starred/BookStack
1
0
Fork 0
mirror of https://github.com/BookStackApp/BookStack.git synced 2026-02-05 00:29:48 +03:00
Code Issues 305 Packages Projects Releases 10 Wiki Activity

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

New Issue
Closed
opened 2026-02-04 19:03:40 +03:00 by OVERLORD · 18 comments
OVERLORD commented 2026-02-04 19:03:40 +03:00
Owner
Copy Link

Originally created by @TribuneX on GitHub (Jul 3, 2017).

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?

Originally created by @TribuneX on GitHub (Jul 3, 2017). ### 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?
OVERLORD added the 🐛 Bug📖 Docs Update labels 2026-02-04 19:03:40 +03:00
OVERLORD commented 2026-02-04 19:03:59 +03:00
Author
Owner
Copy Link

@TheFiZi commented on GitHub (Jul 4, 2017):

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.

@TheFiZi commented on GitHub (Jul 4, 2017): 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.
OVERLORD commented 2026-02-04 19:04:03 +03:00
Author
Owner
Copy Link

@sanderdw commented on GitHub (Jul 4, 2017):

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

@sanderdw commented on GitHub (Jul 4, 2017): 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 `
OVERLORD commented 2026-02-04 19:04:05 +03:00
Author
Owner
Copy Link

@domainzero commented on GitHub (Jul 5, 2017):

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:

@domainzero commented on GitHub (Jul 5, 2017): 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:
OVERLORD commented 2026-02-04 19:04:14 +03:00
Author
Owner
Copy Link

@TheFiZi commented on GitHub (Jul 5, 2017):

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
@TheFiZi commented on GitHub (Jul 5, 2017): 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 ```
OVERLORD commented 2026-02-04 19:04:36 +03:00
Author
Owner
Copy Link

@Aarup commented on GitHub (Jul 5, 2017):

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

@Aarup commented on GitHub (Jul 5, 2017): Can confirm - had this problem too, but upgrading to MariaDB 10.2.6 fixed the problem
OVERLORD commented 2026-02-04 19:04:48 +03:00
Author
Owner
Copy Link

@marcvef commented on GitHub (Jul 5, 2017):

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

@marcvef commented on GitHub (Jul 5, 2017): Same problem here. Unfortunately, I can also not upgrade to mariadb 10.2.x at this time. Is there an alternative solution?
OVERLORD commented 2026-02-04 19:04:56 +03:00
Author
Owner
Copy Link

@ssddanbrown commented on GitHub (Jul 5, 2017):

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
@ssddanbrown commented on GitHub (Jul 5, 2017): 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
OVERLORD commented 2026-02-04 19:05:05 +03:00
Author
Owner
Copy Link

@marcvef commented on GitHub (Jul 5, 2017):

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.

@marcvef commented on GitHub (Jul 5, 2017): 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.
OVERLORD commented 2026-02-04 19:05:19 +03:00
Author
Owner
Copy Link

@Ethanb00 commented on GitHub (Jul 7, 2017):

I had the exact same experience as other folks here.

@Ethanb00 commented on GitHub (Jul 7, 2017): I had the exact same experience as other folks here.
OVERLORD commented 2026-02-04 19:05:28 +03:00
Author
Owner
Copy Link

@domainzero commented on GitHub (Jul 8, 2017):

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.
@domainzero commented on GitHub (Jul 8, 2017): 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.
OVERLORD commented 2026-02-04 19:05:37 +03:00
Author
Owner
Copy Link

@ssddanbrown commented on GitHub (Jul 10, 2017):

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.

@ssddanbrown commented on GitHub (Jul 10, 2017): 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.
OVERLORD commented 2026-02-04 19:05:43 +03:00
Author
Owner
Copy Link

@AbijeetP commented on GitHub (Jul 12, 2017):

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.

@AbijeetP commented on GitHub (Jul 12, 2017): > 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.
OVERLORD commented 2026-02-04 19:05:49 +03:00
Author
Owner
Copy Link

@ssddanbrown commented on GitHub (Jul 12, 2017):

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.

@ssddanbrown commented on GitHub (Jul 12, 2017): > 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.
OVERLORD commented 2026-02-04 19:05:52 +03:00
Author
Owner
Copy Link

@pingram3030 commented on GitHub (Jul 19, 2017):

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
@pingram3030 commented on GitHub (Jul 19, 2017): 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 ```
OVERLORD commented 2026-02-04 19:06:00 +03:00
Author
Owner
Copy Link

@TheFiZi commented on GitHub (Jul 19, 2017):

@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

@TheFiZi commented on GitHub (Jul 19, 2017): @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
OVERLORD commented 2026-02-04 19:06:12 +03:00
Author
Owner
Copy Link

@darkmoon2 commented on GitHub (Jul 20, 2017):

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.

@darkmoon2 commented on GitHub (Jul 20, 2017): 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.
OVERLORD commented 2026-02-04 19:06:18 +03:00
Author
Owner
Copy Link

@ssddanbrown commented on GitHub (Jul 22, 2017):

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.

@ssddanbrown commented on GitHub (Jul 22, 2017): 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/).
OVERLORD commented 2026-02-04 19:06:29 +03:00
Author
Owner
Copy Link

@Hsins commented on GitHub (Mar 21, 2025):

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
@Hsins commented on GitHub (Mar 21, 2025): 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 ```
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
🎨 Design
📖 Docs Update
🐛 Bug
🐛 Bug
:cat2:🐈 Possible duplicate
💿 Database
☕ Open to discussion
💻 Front-End
🐕 Support
🚪 Authentication
🌍 Translations
🔌 API Task
🏭 Back-End
⛲ Upstream
🔨 Feature Request
🛠️ Enhancement
🛠️ Enhancement
🛠️ Enhancement
❤️ Happy feedback
🔒 Security
🔍 Pending Validation
💆 UX
📝 WYSIWYG Editor
🌔 Out of scope
🔩 API Request
:octocat: Admin/Meta
🖌️ View Customization
❓ Question
🚀 Priority
🛡️ Blocked
🚚 Export System
♿ A11y
🔧 Maintenance
> Markdown Editor
No Label 📖 Docs Update 🐛 Bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
OVERLORD
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/BookStack#364
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?