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

TribuneX commented

2017-07-03 21:26:04 +02:00

(Migrated from github.com)

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?

### 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 commented

2017-07-04 08:10:34 +02:00

(Migrated from github.com)

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.

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 commented

2017-07-04 14:47:40 +02:00

(Migrated from github.com)

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

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 commented

2017-07-05 01:58:54 +02:00

(Migrated from github.com)

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:

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](https://laravel.com/docs/master/migrations#creating-indexes). >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:

👍 1

TheFiZi commented

2017-07-05 03:47:28 +02:00

(Migrated from github.com)

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

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 commented

2017-07-05 09:36:23 +02:00

(Migrated from github.com)

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

marcvef commented

2017-07-05 17:57:02 +02:00

(Migrated from github.com)

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

ssddanbrown commented

2017-07-05 19:55:02 +02:00

(Migrated from github.com)

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

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 commented

2017-07-05 20:10:59 +02:00

(Migrated from github.com)

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.

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 commented

2017-07-07 15:20:12 +02:00

(Migrated from github.com)

I had the exact same experience as other folks here.

domainzero commented

2017-07-08 07:16:32 +02:00

(Migrated from github.com)

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.

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 commented

2017-07-10 21:50:16 +02:00

(Migrated from github.com)

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.

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 commented

2017-07-12 08:14:08 +02:00

(Migrated from github.com)

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.

> 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 commented

2017-07-12 09:40:36 +02:00

(Migrated from github.com)

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.

> 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 commented

2017-07-19 02:28:49 +02:00

(Migrated from github.com)

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

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 commented

2017-07-19 04:13:00 +02:00

(Migrated from github.com)

@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

@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 commented

2017-07-20 20:14:27 +02:00

(Migrated from github.com)

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 commented

2017-07-22 18:32:10 +02:00

(Migrated from github.com)

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.

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](https://www.bookstackapp.com/docs/admin/ut8mb4-support/).

Hsins commented

2025-03-21 05:47:09 +01:00

(Migrated from github.com)

Encounter this issue with MariaDB 10.6.20:

In Connection.php line 825:
                                                                                                                                                                                                               
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes (Connection: mysql, SQL: alter table `joint_permissions` add primary key (`role_id`, `enti  
  ty_type`, `entity_id`, `action`))                                                                                                                                                                            
                                                                                                                                                                                                               

In Connection.php line 571:
                                                                                                                    
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes

Just for reference for someone got into trouble with the same isseu. My solution is editng the /app/Config/database.php file:

- 'engine'         => 'null',
+ 'engine'         => 'InnoDB ROW_FORMAT=DYNAMIC',

And run the migration again:

$ php artisan db:wipe
$ php artisan migrate

Encounter this issue with MariaDB 10.6.20: ``` In Connection.php line 825: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes (Connection: mysql, SQL: alter table `joint_permissions` add primary key (`role_id`, `enti ty_type`, `entity_id`, `action`)) In Connection.php line 571: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes ``` Just for reference for someone got into trouble with the same isseu. My solution is editng the `/app/Config/database.php` file: ```diff - 'engine' => 'null', + 'engine' => 'InnoDB ROW_FORMAT=DYNAMIC', ``` And run the migration again: ``` bash $ php artisan db:wipe $ php artisan migrate ```

👍 2

Rows
Columns

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

For Bug Reports