[PDOException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

[TribuneX]

For Bug Reports

• BookStack Version: v0.17.0
• PHP Version: PHP 7.1.6
• MySQL Version: Ver 15.1 Distrib 10.0.30-MariaDB

While trying to upgrade my bookstack installation, I got the following error:

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length
is 767 bytes

This error appears after:

Do you really wish to run this command? (yes/no) [no]:
yes

Any idea whats the issue here?

[TheFiZi]

I just got the exact same thing upgrading from v0.16.3 to v0.17.0

• CentOS 6 64-bit
• PHP 7.0.20
• MariaDB 10.1.24
• Apache 2.2.15

-bash-4.1$ php artisan migrate
**************************************
*     Application In Production!     *
**************************************

 Do you really wish to run this command? (yes/no) [no]:
 > yes

  [PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

When I login to the instance of Bookstack and click 'Settings' it shows 0.17.0 in the bottom right corner.

[sanderdw]

Same here upgrading from v0.16.3 to v0.17.0:

• PHP 7.1.6
• 10.1.24-MariaDB

[PDOException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t oo long; max key length is 1000 bytes

[domainzero]

I ran into this issue as well.
Looks like this is a known issue you'll hit if you're using a MariaDB instance that's older than 10.2.2.
Unfortunately upgrading to 10.2.x isn't really a possibility right now for me.

Updating with my information:

• DB Version: MariaDB 10.1.24
• OS: Fedora 25
• DB Engine: InnoDB
• Row Format: Compact

Some more details here.

Laravel uses the utf8mb4 character set by default, which includes support for storing "emojis" in the database. If you are running a version of MySQL older than the 5.7.7 release or MariaDB older than the 10.2.2 release, you may need to manually configure the default string length generated by migrations in order for MySQL to create indexes for them. You may configure this by calling the Schema::defaultStringLength method within your AppServiceProvider:

[TheFiZi]

Confirming @domainzero's post. I upgraded to MariaDB 10.2.6 and I am now able to run php artisan migrate successfully.

-bash-4.1$ php artisan migrate
**************************************
*     Application In Production!     *
**************************************

 Do you really wish to run this command? (yes/no) [no]:
 > yes

Migrated: 2017_07_02_152834_update_db_encoding_to_ut8mb4

[Aarup]

Can confirm - had this problem too, but upgrading to MariaDB 10.2.6 fixed the problem

[marcvef]

Same problem here. Unfortunately, I can also not upgrade to mariadb 10.2.x at this time. Is there an alternative solution?

[ssddanbrown]

Interestingly I also ran into this issue on MySQL 5.6.33 on ubuntu 14.04. I'll have to have a dive into the exact versions of MySQL/Mariadb this effects and find a work around to fix this sometime soon.

In the meantime, The migration is not essential, It just means you can't store emoji in your content for now. You could manually convert your tables to the new encoding or just wait until this is fixed. Nothing should break using BookStack v0.17.0 without this migration unless emoji are vital.

Thanks everyone for reporting your DB versions, Will be very useful.

My Info:

• DB Version: MySQL 5.6.33
• OS: Ubuntu 14.04
• DB Engine: InnoDB
• Row Format: Compact

[marcvef]

Update: Providing some information of the system
PHP Version: 7.1.6
MySQL Version: mysql Ver 15.1 Distrib 10.1.24-MariaDB, for Linux (x86_64) using readline 5.1
OS: Arch Linux

Sidenote: MariaDB 10.2.x is not yet available (i.e., declared stable) on Arch Linux or Gentoo which usually have new versions earlier than other distros.

[Ethanb00]

I had the exact same experience as other folks here.

[domainzero]

Was able to get patched up to MariaDB 10.2.6 and the issue is resolved.

This issue will affect:

• MySQL databases at versions earlier than 5.7.7
• MariaDB databases at versions earlier than 10.2.2
  according to the Laravel docs.

[ssddanbrown]

Okay, After some investigation it seems like there's not going to be an ideal solution for this. Really strange as running the commands manually seems to work fine.

I'm thinking that the best option is to empty out the migration to prevent issues for others. Then add a command to generate out the sql commands as a helper to those that do want to upgrade to utf8mb4.

New instances should be relatively unaffected I think since it seems to be more of an issue when you have existing data.

[AbijeetP]

New instances should be relatively unaffected I think since it seems to be more of an issue when you have existing data.

Had this when developing on BookStack with a database with no books created. Don't think its related to the data you have in the database.

[ssddanbrown]

Had this when developing on BookStack with a database with no books created. Don't think its related to the data you have in the database.

Strange... I initially used docker containers with old versions of MySQL and mariadb and could into get the issue to pop up unless i migrated to BookStack v0.16, Seeded data, then migrated to v0.17. This issue is very finicky.

[pingram3030]

So, is the mitigation to rm -f database/migrations/2017_07_02_152834_update_db_encoding_to_ut8mb4.php? @ssddanbrown, could you please provide guidance of a suitable work around for this issue, we are completely incapable of upgrading BS because of this. Upgrading to v0.16.3 works, so we are pinning ourselves here.

This doesn't work, but seemed like a good start:

DATABASE=bookstack
mysql -e "ALTER DATABASE ${DATABASE} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
for table in $(mysql -BNe 'SHOW TABLES;' ${DATABASE}); do
    echo ${table}
    mysql -e "ALTER TABLE ${table} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" ${DATABASE}
done

activities
books
chapters
email_confirmations
entity_permissions
images
joint_permissions
migrations
page_revisions
pages
password_resets
permission_role
role_permissions
ERROR 1071 (42000) at line 1: Specified key was too long; max key length is 767 bytes
role_user
roles
ERROR 1071 (42000) at line 1: Specified key was too long; max key length is 767 bytes
settings
ERROR 1071 (42000) at line 1: Specified key was too long; max key length is 767 bytes
social_accounts
tags
users
ERROR 1071 (42000) at line 1: Specified key was too long; max key length is 767 bytes
views

This is the latest version of MariaDB available on AWS RDS:

$ aws rds describe-db-instances --db-instance-identifier REDACTED --query "DBInstances[].[Engine,EngineVersion]" --output table
------------------------
|  DescribeDBInstances |
+----------+-----------+
|  mariadb |  10.1.23  |
+----------+-----------+

The following is the latest supported version available on CentOS released by the author, MariaDB. It may be poignant to be mindful of those that use enterprise class software and/or who prefer older and stabler versions of software for its LTS. Heck, even Fedora 26, released days ago, only has 10.1.21 available; which is incompatible out of the box.

$ repoquery --info MariaDB-server.x86_64 | grep Source
Source      : MariaDB-server-10.1.25-1.el7.centos.src.rpm

[TheFiZi]

@pingram3030 FWIW 10.2 is considered stable on CentOS 6 and 7 (I've got it running on 6)

See: https://downloads.mariadb.org/mariadb/repositories/#mirror=globotech&distro=CentOS&distro_release=centos7-amd64--centos7&version=10.2

[darkmoon2]

So I manually installed on Ubuntu 16.04.2 with MySQL 5.7.18 and did not have an issue, but when I tried to perform the same exact steps of upgrade on openSUSE Leap 42.2 with MariaDB 10.0.30 I experienced the same error 1071. Attempted Fix Update 2 of scorer's post (https://stackoverflow.com/questions/23786359/laravel-migration-unique-key-is-too-long-even-if-specified), which did not work, however after deleting all the tables in my database, then I was able to perform the installation.

[ssddanbrown]

As of BookStack v0.17.2 (Just released) this upgrade has been taken out of migrations and a helper command has been created. Details of this can be found here.