Directus: not able to install database: ./directus install:database SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

[Marcnuth]

Error Info:

> ./directus install:database
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

Env:

mysql: 5.7.18
php: 7.1
directus-suite: 190503A

[Marcnuth]

fixed by set innodb_large_prefix=ON for MySQL

[dev7ch]

could be useful for the docs

[danyright]

Hey guys,
No sure this issue is actually closed...
Because I for one, have not been able to set this MySQL setting on my Plesk server I use for directus.
Not sure if it's because of the way Plesk manages MySQL but I can't seem to set the right user privileges to set innodb_large_prefix=ON via phpMyAdmin.

Again, maybe my deep hatred for MySQL isn't helping, but I can't imagine that anyone with an account on a shared hosting could manage to setup this properly.
Please tell me if I'm wrong, and if so, please let me know if there's a simple solution to this.

By the way, why was it necessary to make this change? I did not run into these issues with earlier versions of directus 7.

Thanks for your help,
D

PS: sorry but I got a bit annoyed after spending over 1.5 hour trying to solve this (without success) instead of the 10 minutes I planned for installing directus from scratch...

[rijkvanzanten]

Hey @danyright

What versions are you running? MySQL / PHP / Plesk / Apache

[danyright]

Hi,
CentOS 7.5, PHP 7.2.11, mysql Ver 15.1 Distrib 5.5.60-MariaDB, Apache/2.4.6 (CentOS), Plesk 17.8.11.

[dev7ch]

have you changed from utf8mb4 to utf8 in configs?
or maybe setting the options in configs, maybe helpfull ...

https://stackoverflow.com/questions/35847015/mysql-change-innodb-large-prefix?answertab=active#tab-top

https://docs.nextcloud.com/server/16/admin_manual/configuration_database/mysql_4byte_support.html

[danyright]

No I have not done that (change the DB's encoding).
Thanks for the StackOverflow link - got there as well, but the only way to do that (on my server at least) seems to access mysql as admin/root or change the my.cnf file.

I was about to go change that file, in desperation, but again, it seems to me that this is a serious limitation given that many people do not have root access to their server.
_And I honestly don't think you should need root access to install a web app on a server!_
Event to install nextCloud, I never had to do such tinkering...

But thanks for your help :-)
I'll check the encoding solution later on and let you know.

Cheers,
D

[rijkvanzanten]

And I honestly don't think you should need root access to install a web app on a server!

True! To be fair, I've tested and used Directus on many standard LAMP stacks using DigitalOcean's one click setup which didn't require any tinkering. There must be something causing this in the specific server setup you're using.

I do notice you're using a non-supported version of MySQL (5.5) vs the required 5.7+. Maybe that causes the problems?

[danyright]

I do notice you're using a non-supported version of MySQL (5.5) vs the required 5.7+. Maybe that causes the problems?

Indeed, it might. CentOS's official repo are sometimes quite late at the party... 🙃
I will try to get MySQL to 5.7.

Though again, this might be difficult to achieve for someone with less control on their hosting. But if it's in the requirements...

Thanks for the help.
D

[danyright]

So, I did update mysql - resp. updated MariaDB to v10.2 (10.2.24 to be exact) using this guide on my CentOS server.

Just tried installing directus again and it did work without a glitch.

So I guess I will just have to blame CentOS then...
I hope this helps someone !

Thank you all for your help,
D

[benhaynes]

Haha, well that's good news! Sorry about the weirdness in the previous install... difficult for our small team to test on so many possible stacks. Glad it's working! 👍

[vatologic]

An older version ( < 10.2.1 ) of MariaDB is the culprit - together with 3rd party library Phinx used by Directus. Since the global variable 'innodb_default_row_format' doesn't exist yet in this version of MariaDB, every CREATE statement using larger indices should include 'ROW_FORMAT=DYNAMIC'.

A quick and rather dirty workaround is adding one extra line to Phinx in the file below, just around line 252.
/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/MysqlAdapter.php

// process table collation
if (isset($options['collation'])) {
    $charset = explode('_', $options['collation']);
    $optionsStr .= sprintf(' CHARACTER SET %s', $charset[0]);
    $optionsStr .= sprintf(' COLLATE %s', $options['collation']);
}

// Very quick, dirty and rather unadvisable MariaDB < 10.2.1 hack
$optionsStr .= ' ROW_FORMAT=DYNAMIC';

// set the table comment
if (isset($options['comment'])) {
    $optionsStr .= sprintf(" COMMENT=%s ", $this->getConnection()->quote($options['comment']));
}

[staybox001]

https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740

$ sudo mysql -u root -p
MariaDB [(none)]> use nextcloud;
MariaDB [nextcloud]> set global innodb_large_prefix=on;
MariaDB [nextcloud]> set global innodb_file_format=Barracuda;
$ sudo -u www-data php occ maintenance:repair

[sajjad-shirazy]

In case of no root access to MySQL, fixes by changing charset in the config file:

'charset' => 'utf8',

and commenting these lines out:
https://github.com/directus/directus/blob/d67cfc65bf8c5b011c45319ea928c3a43cd436f8/migrations/install/20180220023152_create_collections_presets_table.php#L58-L61