V8-archive: Cannot install: "SQLSTATE[HY000]: General error: 1709 Index column size too large"

[thepurpleblob]

Bug Report

Attempting to install new Directus site but fails at database creation. Used to work on same server. Think this is a bug recently introduced.

Latest commit is, commit eb93d96756de9c7805ed12d249f21eabc94620b6 on May 3

Steps to Reproduce

1. Download latest Directus from Git
2. Install as documented and run /admin
3. Create a new utf8mb4 database in MariaDB
4. Follow install prompts

Expected Behavior

Directus installs.

Actual Behavior

SQL Error.
`[2019-05-09 10:17:39] api[].ERROR: PDOException: SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes. in /home/collection/directus/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:134
Stack trace:

0 /home/collection/directus/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php(134): PDO->exec

1 /home/collection/directus/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/MysqlAdapter.php(294): Phinx\Db\Adapter\PdoAdapter->execute

2 /home/collection/directus/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/AdapterWrapper.php(351): Phinx\Db\Adapter\MysqlAdapter->createTable

3 /home/collection/directus/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/TimedOutputAdapter.php(138): Phinx\Db\Adapter\AdapterWrapper->createTable

4 /home/collection/directus/vendor/robmorgan/phinx/src/Phinx/Db/Table.php(628): Phinx\Db\Adapter\TimedOutputAdapter->createTable

5 /home/collection/directus/migrations/db/schemas/20180220023152_create_collections_presets_table.php(81): Phinx\Db\Table->create

6 /home/collection/directus/vendor/robmorgan/phinx/src/Phinx/Migration/Manager/Environment.php(124): CreateCollectionsPresetsTable->change

7 /home/collection/directus/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(375): Phinx\Migration\Manager\Environment->executeMigration

8 /home/collection/directus/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(351): Phinx\Migration\Manager->executeMigration

9 /home/collection/directus/src/core/Directus/Util/Installation/InstallerUtils.php(145): Phinx\Migration\Manager->migrate

10 /home/collection/directus/src/core/Directus/Util/Installation/InstallerUtils.php(112): Directus\Util\Installation\InstallerUtils::runMigrationAndSeeder

11 /home/collection/directus/src/core/Directus/Services/ProjectService.php(92): Directus\Util\Installation\InstallerUtils::createTables

12 /home/collection/directus/src/endpoints/ProjectsCreate.php(16): Directus\Services\ProjectService->create

13 [internal function]: Directus\Api\Routes\ProjectsCreate->__invoke

14 /home/collection/directus/vendor/slim/slim/Slim/Handlers/Strategies/RequestResponse.php(41): call_user_func

15 /home/collection/directus/vendor/slim/slim/Slim/Route.php(356): Slim\Handlers\Strategies\RequestResponse->__invoke

16 /home/collection/directus/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(117): Slim\Route->__invoke

17 /home/collection/directus/vendor/slim/slim/Slim/Route.php(334): Slim\Route->callMiddlewareStack

18 /home/collection/directus/vendor/slim/slim/Slim/App.php(515): Slim\Route->run

19 /home/collection/directus/src/core/Directus/Application/Http/Middleware/CorsMiddleware.php(66): Slim\App->__invoke

20 [internal function]: Directus\Application\Http\Middleware\CorsMiddleware->__invoke

21 /home/collection/directus/vendor/slim/slim/Slim/DeferredCallable.php(43): call_user_func_array

22 [internal function]: Slim\DeferredCallable->__invoke

23 /home/collection/directus/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func

24 /home/collection/directus/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(117): Slim\App->Slim{closure}

25 /home/collection/directus/vendor/slim/slim/Slim/App.php(405): Slim\App->callMiddlewareStack

26 /home/collection/directus/vendor/slim/slim/Slim/App.php(313): Slim\App->process

27 /home/collection/directus/src/core/Directus/Application/Application.php(161): Slim\App->run

28 /home/collection/directus/public/index.php(5): Directus\Application\Application->run [] []`

Other Context & Screenshots

Technical Details

• Device: Desktop
• OS: MacOS
• Web Server: Apache 2.4
• PHP Version: PHP 7.2.17
• Database: MariaDB 10.1
• Install Method: standard web install

[benhaynes]

Ref: https://github.com/directus/api/issues/906

[alrick]

I have the same problem.
The issue is that MariaDB 10.1 (which is the last version through official Ubuntu repository) is based on MySQL 5.5 which is not supported anymore by Directus.

You have two possibilities :

1. migrate from MariaDB to MySQL
2. upgrade MariaDB via the MariaDB APT repository

[thepurpleblob]

Yep - I've just realised that Ubuntu 18.04 comes with an ancient version of MariaDB for some reason.

I'm just attempting to "upgrade" to MySQL 5.7 but it's all going wrong. I'll report back when I've persuaded MySQL to work properly.

[thepurpleblob]

Yep - installs fine with 5.7.

For the casual reader... Ubuntu 18.04LTS comes with MariaDB 10.1 which is really old and not supported by Directus. If, like me, you have a bunch of existing databases you don't want to loose this turns into hassle. It's not entirely straightforward 'upgrading' to MySQL 5.7.

[alrick]

Yeah, the main problem is that MySQL 5.5 is outdated but MariaDB 10.1 isn't (even if quit old).
But as Directus doesn't officially support MariaDB, we can't blame them ;)

So either use MySQL (probably the best option for casual user) or be prepared to upgrade more often your MariaDB instance from their repository directly.

[alrick]

Just a follow-up to confirm that upgrading MariaDB to version 10.3 fixes this problem, for those who want to keep MariaDB (at your own risk ;) ).

[bjgajjar]

I'm closing this as this issue resolve with MySQL 5.7

[julianlecalvez]

I upgraded to MariaDB 10.4 and it works fine now !
https://computingforgeeks.com/install-mariadb-10-on-ubuntu-18-04-and-centos-7/

[ManniManfred]

Hi, as a workaround you could change the index idx_user_collection_title to only include the 'user' column instead of 'user', 'collection', 'title'.

Change in migrations/install/20180220023152_create_collections_presets_table.php
to (line 58):

        $table->addIndex(['user'], [
            'unique' => true,
            'name' => 'idx_user_collection_title'
        ]);