October: Database utf8mb4 Key was too long

[jurchiks]

Open /config/database.php, set 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci' under mysql, and execute php artisan october:up.
Result:

[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table `sessions` add unique sessions_id_unique(`id`))

[LukeTowers]

Closing as it has been over a month since any activity on this occurred.

[jurchiks]

This should be fixed, not closed. It's clearly a bug, and utf8mb4 is increasingly common these days.

[LukeTowers]

I'm going on a closing spree @jurchiks to spur people into taking action on important issues. Otherwise your issue is just going to sit on the bottom of the queue for all eternity.

Please update to the latest dev build of October and check if this issue is still present. Thanks!

[jurchiks]

Yeeeah, I haven't used this software since I posted the issue...

[LukeTowers]

@jurchiks well I'd recommend that you give it a try if you still have a business use case for a CMS. We've upgraded to using Laravel 5.5 which is where I'm assuming the issue originated from so I'm going to assume this has been fixed until I am otherwise proved wrong.

[jurchiks]

Nah, working on entirely different projects now, completely custom, this would not be flexible enough.

[LukeTowers]

Whatever works best for you. Best of luck with your future projects!

[ghost]

We have same issue !

[LukeTowers]

@ibsvn then open a new issue and provide detailed replication instructions and as much information as possible.

[jurchiks]

Why not just reopen this same issue if it's identical?

[LukeTowers]

@jurchiks because it's probably not.

[tylerssn]

@ibsvn - this is resolved by updating ./config/database.php's mysql settings from:

'charset'   => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',

to:

'charset'   => 'utf8',
'collation' => 'utf8_unicode_ci',

The migration and seeding works with php artisan october:up after making these changes, though I have not tested the entire application to ensure that this does not cause any other issues.

Aside, this could be related to MariaDB?

[LukeTowers]

@tylerssn the issue mentioned would be different as we only recently switched to defaulting to utf8mb4.

[LukeTowers]

The issue is when you are running an older version of MySQL or MariaDB. Upgrade those and the issue will go away. https://laravel-news.com/laravel-5-4-key-too-long-error

[wintstar]

The issue is when you are running an older version of MySQL or MariaDB. Upgrade those and the issue will go away.

And how about isnt´t you're with a hoster who doesn't update the database immediately?
Isn't there a solution for the meantime?

To change the hoster, I and others certainly don't think it's a good solution.

[LukeTowers]

@wintstar then change database.php back to using just utf8 instead of utf8mb4

[Verbat]

Is there also a way fixing this when using the installer?
I'm trying to set up a new octobercms installation but i cannot get it up and running as installation always stops with an error.

edit:
i was able to run the installer.
After the error message i logged in via ftp. Changed the database.php. Dropped deferred_bindings table with mysql admin and rerun the installer.

[LukeTowers]

If someone wants to add a PR to the install repo to specify the DB collation for MySQL installs that would be a good solution for older hosts.

[gerrymcdonnell]

Same issues on a fresh install i know this issue is common with Laravel but was able to get the install to work using the method above;
config/database.php

'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

[ghost]

@LukeTowers @bennothommo @daftspunk @Samuell1

Today I have this error and I have been reading through all this repo with regards to this error and the Laravel doc's and I think the commits are wrong!

Please can we sort this out properly and add a Priority: High label to this issue!

This issue is caused by someone being stupid at Laravel and not thinking of backwards compatibility!

How to fix this issue

1. If you are on Mysql < 5.7 or MariaDB < 10.2 then enable innodb_large_prefix this will let you have 255 fields on your database you won't need to touch any code.

2. If you are running MySQL greater than 5.6 you should use this:

In Laravel config/database.php define:

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',

1. If you are running MySQL less than 5.7 you should use this:

In Laravel config/database.php define:

'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',

1. Add the: Schema::defaultStringLength(191); which is already done - so no action needed.

What October CMS should do to fix this probelm

1. It should check the MySQL version of the server when a user installs October first the first time. October CMS should have the logic to decide what is the correct setup according to the user's server setup and then the users doesn't need to see this issue and go correct the problem by adding backwards compatibility!

2. Need to add engine option in the database config file.

Some links for extra infomation:

https://stackoverflow.com/questions/23786359/laravel-migration-unique-key-is-too-long-even-if-specified/39750202#39750202

https://stackoverflow.com/questions/42244541/laravel-migration-error-syntax-error-or-access-violation-1071-specified-key-wa

[LukeTowers]

@ayumihamsaki I don't think we support MySQL < 5.7

[ghost]

@LukeTowers

That's going to cause a lot of issues to many people. Most hosting companies are still using MySQL 5.6

For example my hosting company is using:

Server version: 5.6.40-84.0-log - Percona Server (GPL).

Link: https://www.percona.com/doc/percona-server/5.6/release-notes/Percona-Server-5.6.40-84.0.html

Note the release date is: May 30, 2018 for V5.6

I really think October should support MySQL 5.6 and upwards.

[jurchiks]

MySQL 5.7 was released in 2015, and so was Percona 5.7 - https://www.percona.com/doc/percona-server/5.7/release-notes/Percona-Server-5.7.10-1.html
Either you haven't checked for the 5.7 version or your hosting company is shit.

[ghost]

@jurchiks it's not that straight forward.

MySQL 5.6.45 - release date: 2019-07-22, see here: https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-45.html

MySQL 5.7.27 - release date: 2019-07-22, see here: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-27.html

[jurchiks]

It IS that straightforward. There is a newer stable version of MySQL available for 4 years already, and any respectable hosting service should be supporting it. No excuses.

[ghost]

There's a good reason why many hosting companies still use MySQL 5.6

MySQL 5.7 may have more features but it so much slower than MySQL 5.6

MySQL 5.6, performs in 0.04 seconds - see test results: https://codepen.io/Petce/full/JNKEpy/

MySQL 5.7, performs in 1.952 seconds - see test results: https://codepen.io/Petce/full/gWMgKZ/

End of the day October should support both versions and users should be able to decide in the installer, as both versions are currently being fully development right now.

[jurchiks]

Ok, now you're just making excuses for your hosting provider.

[Samuell1]

@ayumihamsaki thats some weird benchmarks looking trough newest 5.7 benchmarks performs a lot better then 5.6

For example this shows 5.7 is a lot better
http://funwithmysql.blogspot.com/2018/01/mysql-57-vs-56-vs-55-sysbench-benchmark.html

[ghost]

@Samuell1 interesting thanks for the share.

@jurchiks

Ok, now you're just making excuses for your hosting provider.

Don't get me wrong! I'm not agreeing with the hosting companies, I would prefer if they all gave a free choice of 5.6, 5.7 or 8.0 - but they don't.

October CMS supports IE11, though I disagree with that decision and think people should use an evergreen browser.

Because MySQL 5.6 and 5.7 are being development at the exact same time and the security patches are being aplied to them both at the exact same time.

I have to say October CMS should support both versions. If MySQL dropped support for 5.6 like Windows is going to drop support for Windows 7 at the end of this year, then I would say yah support 5.7 or higher. But they are being fully developed right now! So that's my reason behind it.

It makes no sense to say to people you can only use October CMS with MySQL 5.7 and stop many people not being able to use it with many major hosting companies.

That's my reasons - I don't like it but that's what it is.

[ghost]

My suggestion is on this screen to have an option to select which MySQL version a user is using from a dropdown menu:

[bennothommo]

@ayumihamsaki We are asking people to provide a PR to add an option for the database charset and collation types, not to select the version of MySQL running on the server.

I don't believe this is a High Priority issue, as there's workarounds readily available in either changing the charset and collation type manually, upgrading MySQL, or switching to MariaDB.

[ghost]

@bennothommo Sorry, yes exactly what you said is correct.

• Not a High Priority issue.
• Add an option for charset and collation types.

And any other useful / needed config to use the new MySQL 8.0 releases (to keep October future-proof).

[Samuell1]

@ayumihamsaki i think that will be more supported with new laravel update

[ghost]

@Samuell1 I was wondering about that last night with Luke's Lavarel 5.9 updates (or in the laravel 6) - if it contains anything related to MySQL 8.0 or not?

• I just don't want an update of Laravel to October, to create another issue like this one - which seems to be from the Laravel 5.4 updates - https://laravel-news.com/laravel-5-4-key-too-long-error

[LukeTowers]

@ayumihamsaki reading back over this issue, why doesn't https://github.com/octobercms/october/commit/bd9f88587f5adad66866294d22a598aa195cff4b solve the problem for you?

[ghost]

@LukeTowers Here's all the background info on my situation with one of my clients.

Simply put one of my clients asked me to re-install all of their october cms installs as I created 86 of their websites between 2015 - 2016 and some files are not the same as now even after a re-complie. So I said I would manually do fresh installs on all their websites and uploaded their plugins, themes and settings into the new installs.

I quickly found out that their hosting company Siteground is using the latest version of MySQL 5.6 series and came across this issue. Not in the installation of October CMS but the issue came up in several plugins the websites were using on certain migration code lines.

Two factors came from this:

1. I contacted Siteground and they said they are in the process of updating all their servers from 5.6 to 5.7 but as my client was using Shared hosting packages, they said to him to buy dedicated severs for all 86 websites. Of course my client said no due to the huge extra costs.

2. Many plugins in the marketplace broke down and not worked using utf8mb4_unicode_ci. So because of that my answer to my client was to use utf8_unicode_ci and wait for the hosting company to finish their updates. They are in the process of updating their admin area as well. https://www.siteground.com/blog/new-client-area-and-site-tools/

why doesn't bd9f885 solve the problem for you?

Marketplace plugin issues.

[LukeTowers]

Marketplace plugin issues.

What plugins and what issues? There shouldn't be any issues unless a plugin migration is manually specifying a string with more characters than 191.

[ghost]

I was seeing a huge range of different errors from roughly 30 different plugins (out of close to 100 plugins installed on each website). I wished you asked me that question yesterday - I had them all written down. Now I've deleted all those notes.

The errors were all coming from creating database tables and seeder files from what I could tell.

[LukeTowers]

Well without specifics I can't help much.

[ghost]

Sorry I can't be more helpful, all I can say it's a pain to manually set the collation types in mysql, so if October can help out with that it would be great for the plugins and cms core code.

[daftspunk]

FEI - introduced varcharmax to the mysql database driver configuration. This should allow more granular control over all the possible architectural variants

See https://github.com/octobercms/october/commit/160ae441ff29dfde81e7efd42ac3f1e26f5c7985 for more detail

Note that we will eventually change this default from 191 back to 255 and sunset the support for older database versions