Error 1071 on "2020_08_04_111754_drop_joint_permissions_id"

[ArlorNaturalScientific]

Hello
I have that issue :

`C:\Wamp64\www\bookstack|► php artisan migrate:fresh

---

• Application In Production!     *
  

---

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

yes

Dropped all tables successfully.
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated: 2014_10_12_000000_create_users_table (0.08 seconds)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated: 2014_10_12_100000_create_password_resets_table (0.04 seconds)
Migrating: 2015_07_12_114933_create_books_table
Migrated: 2015_07_12_114933_create_books_table (0.01 seconds)
Migrating: 2015_07_12_190027_create_pages_table
Migrated: 2015_07_12_190027_create_pages_table (0.01 seconds)
Migrating: 2015_07_13_172121_create_images_table
Migrated: 2015_07_13_172121_create_images_table (0.01 seconds)
Migrating: 2015_07_27_172342_create_chapters_table
Migrated: 2015_07_27_172342_create_chapters_table (0.01 seconds)
Migrating: 2015_08_08_200447_add_users_to_entities
Migrated: 2015_08_08_200447_add_users_to_entities (0.06 seconds)
Migrating: 2015_08_09_093534_create_page_revisions_table
Migrated: 2015_08_09_093534_create_page_revisions_table (0.01 seconds)
Migrating: 2015_08_16_142133_create_activities_table
Migrated: 2015_08_16_142133_create_activities_table (0.01 seconds)
Migrating: 2015_08_29_105422_add_roles_and_permissions
Migrated: 2015_08_29_105422_add_roles_and_permissions (0.18 seconds)
Migrating: 2015_08_30_125859_create_settings_table
Migrated: 2015_08_30_125859_create_settings_table (0.02 seconds)
Migrating: 2015_08_31_175240_add_search_indexes
Migrated: 2015_08_31_175240_add_search_indexes (0 seconds)
Migrating: 2015_09_04_165821_create_social_accounts_table
Migrated: 2015_09_04_165821_create_social_accounts_table (0.04 seconds)
Migrating: 2015_09_05_164707_add_email_confirmation_table
Migrated: 2015_09_05_164707_add_email_confirmation_table (0.07 seconds)
Migrating: 2015_11_21_145609_create_views_table
Migrated: 2015_11_21_145609_create_views_table (0.01 seconds)
Migrating: 2015_11_26_221857_add_entity_indexes
Migrated: 2015_11_26_221857_add_entity_indexes (0.34 seconds)
Migrating: 2015_12_05_145049_fulltext_weighting
Migrated: 2015_12_05_145049_fulltext_weighting (0 seconds)
Migrating: 2015_12_07_195238_add_image_upload_types
Migrated: 2015_12_07_195238_add_image_upload_types (0.04 seconds)
Migrating: 2015_12_09_195748_add_user_avatars
Migrated: 2015_12_09_195748_add_user_avatars (0.02 seconds)
Migrating: 2016_01_11_210908_add_external_auth_to_users
Migrated: 2016_01_11_210908_add_external_auth_to_users (0.04 seconds)
Migrating: 2016_02_25_184030_add_slug_to_revisions
Migrated: 2016_02_25_184030_add_slug_to_revisions (0.05 seconds)
Migrating: 2016_02_27_120329_update_permissions_and_roles
Migrated: 2016_02_27_120329_update_permissions_and_roles (0.02 seconds)
Migrating: 2016_02_28_084200_add_entity_access_controls
Migrated: 2016_02_28_084200_add_entity_access_controls (0.21 seconds)
Migrating: 2016_03_09_203143_add_page_revision_types
Migrated: 2016_03_09_203143_add_page_revision_types (0.04 seconds)
Migrating: 2016_03_13_082138_add_page_drafts
Migrated: 2016_03_13_082138_add_page_drafts (0.05 seconds)
Migrating: 2016_03_25_123157_add_markdown_support
Migrated: 2016_03_25_123157_add_markdown_support (0.04 seconds)
Migrating: 2016_04_09_100730_add_view_permissions_to_roles
Migrated: 2016_04_09_100730_add_view_permissions_to_roles (0.01 seconds)
Migrating: 2016_04_20_192649_create_joint_permissions_table
Migrated: 2016_04_20_192649_create_joint_permissions_table (0.19 seconds)
Migrating: 2016_05_06_185215_create_tags_table
Migrated: 2016_05_06_185215_create_tags_table (0.08 seconds)
Migrating: 2016_07_07_181521_add_summary_to_page_revisions
Migrated: 2016_07_07_181521_add_summary_to_page_revisions (0.02 seconds)
Migrating: 2016_09_29_101449_remove_hidden_roles
Migrated: 2016_09_29_101449_remove_hidden_roles (0.07 seconds)
Migrating: 2016_10_09_142037_create_attachments_table
Migrated: 2016_10_09_142037_create_attachments_table (0.03 seconds)
Migrating: 2017_01_21_163556_create_cache_table
Migrated: 2017_01_21_163556_create_cache_table (0.02 seconds)
Migrating: 2017_01_21_163602_create_sessions_table
Migrated: 2017_01_21_163602_create_sessions_table (0.02 seconds)
Migrating: 2017_03_19_091553_create_search_index_table
Migrated: 2017_03_19_091553_create_search_index_table (0.1 seconds)
Migrating: 2017_04_20_185112_add_revision_counts
Migrated: 2017_04_20_185112_add_revision_counts (0.05 seconds)
Migrating: 2017_07_02_152834_update_db_encoding_to_ut8mb4
Migrated: 2017_07_02_152834_update_db_encoding_to_ut8mb4 (0 seconds)
Migrating: 2017_08_01_130541_create_comments_table
Migrated: 2017_08_01_130541_create_comments_table (0.05 seconds)
Migrating: 2017_08_29_102650_add_cover_image_display
Migrated: 2017_08_29_102650_add_cover_image_display (0.02 seconds)
Migrating: 2018_07_15_173514_add_role_external_auth_id
Migrated: 2018_07_15_173514_add_role_external_auth_id (0.04 seconds)
Migrating: 2018_08_04_115700_create_bookshelves_table
Migrated: 2018_08_04_115700_create_bookshelves_table (0.27 seconds)
Migrating: 2019_07_07_112515_add_template_support
Migrated: 2019_07_07_112515_add_template_support (0.06 seconds)
Migrating: 2019_08_17_140214_add_user_invites_table
Migrated: 2019_08_17_140214_add_user_invites_table (0.04 seconds)
Migrating: 2019_12_29_120917_add_api_auth
Migrated: 2019_12_29_120917_add_api_auth (0.06 seconds)
Migrating: 2020_08_04_111754_drop_joint_permissions_id

Illuminate\Database\QueryException : SQLSTATE[42000]: Syntax error or access violation: 1071 La clé est trop longue. Longueur maximale: 1000 (SQL: alter table joint_permissions add primary key joint_primary(role_id, entity_type, entity_id, action))

at C:\Wamp64\www\bookstack\vendor\laravel\framework\src\Illuminate\Database\Connection.php:669
665| // If an exception occurs when attempting to run a query, we'll format the error
666| // message to include the bindings with SQL, which will make this exception a
667| // lot more helpful to the developer instead of just the database's errors.
668| catch (Exception $e) {

669| throw new QueryException(
670| $query, $this->prepareBindings($bindings), $e
671| );
672| }
673|

Exception trace:

1 Doctrine\DBAL\Driver\PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1071 La clé est trop longue. Longueur maximale: 1000")
C:\Wamp64\www\bookstack\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\PDOStatement.php:131

2 PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1071 La clé est trop longue. Longueur maximale: 1000")
C:\Wamp64\www\bookstack\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\PDOStatement.php:129

Please use the argument -v to see more details.

C:\Wamp64\www\bookstack|►`

I already converted table to InnoDB, but the issue is always here.

The version of MySQL is : 5.7.31

Thanks a lot.

[ssddanbrown]

Hi @ArlorNaturalScientific,
Sorry to hear you are experiencing issues.

When you say the following:

I already converted table to InnoDB, but the issue is always here.

Have you seen the tables running on a non-InnoDB engine then?
My thinking is that maybe your database is defaulting to MyISAM by default. Although you have attempted converting the table to InnoDB, this would not help if you're running php artisan migrate:fresh since the table will be destroyed then re-created.

It would be good to confirm what engine the database/tables are using right after running the command.

[ArlorNaturalScientific]

Hi ssddanbrown,
Please don't be confused.

You were right, I changed the default engine and install now works correctly :)
Many thanks 👍

[skabbes]

If anyone else find this Github by searching this error, I encountered this error because I had duplicates existing in my joint_permissions table. (thought I am not sure why, however - we have probably close to 100 users creating books, so it is possible a concurrency issue. The reason this fails is because the migration 2020_08_04_111754_drop_joint_permissions_id tried to add a primary key, but couldn't.

I didn't care too much about the permissions in general, so I didn't spend very much time to getting them exactly correct, the query below was sufficient for my needs.

The fix was a bit manual, but I ended up running this directly in MySQL to solve it.

-- create a new table to store the deduplicated data from joint_permissions
create table joint_permissions_copy like joint_permissions;

-- insert the deduplicated data into the copy table
insert into joint_permissions_copy
select
  role_id,
  entity_type,
  entity_id,   
  action,   
  min(has_permission) as has_permission,
  min(has_permission_own) as has_permission_own,
  min(created_by) as created_by
from joint_permissions
group by 1, 2, 3, 4;

delete from joint_permissions;
insert into joint_permissions select * from joint_permissions_copy;
drop table joint_permissions_copy;

-- Since this migration fails when it has partially completed, add the id column back in order to have it successfully
-- drop it next time it runs
alter table joint_permissions add column id int;

Then I ran php artisan migrate one more time, and it was successful.

I hope this helps someone else upgrading to Bookstack v0.31+.

[Tagirijus]

I had a similar problem right now with BookStack v21.08.4 where it told me:

"Doctrine\DBAL\Driver\PDO\Exception::("SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes")"

So I searched the web and found different solutions, which all did not work for me. My provider from my webspace asked me if the tables were in InnoDB .. apparently most of them were MyISAM though. I altered them all manually and tried to re-execute php artisan migrate, which then threw:

Can't DROP COLUMN 'id'

So I added it manually and did it again: php artisan migrate. And then it workd.

My provider told me that they have MyISAM set up as the default and recommended to try to set this in the database.php:

'engine' => 'InnoDB'

But this did not work for me. Only my manually fix like mentioned above.

Now my question is: is there something else I could try to not face such an issue later in case I want to update BookStack?

[bdabelow]

For any one else finding this via web search, here are two hopefully cleaner approaches to solving this issue when the default DB engine cannot be changed (tested on v23.01).

Create additional migration
Create an additional migration that changes the engine for table joint_permissions right before the failing migration. This approach should work for any clean DB (no failed migrations) from pre-2020 versions.

Create a file database/migrations/2020_08_04_111753_joint_permissions_engine_type.php with the following content:

<?php

use Illuminate\Database\Migrations\Migration;

class JointPermissionsEngineType extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement('ALTER TABLE joint_permissions ENGINE = InnoDB');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement('ALTER TABLE joint_permissions ENGINE = MyISAM');
    }
}

Force engine type
As mentioned by @Tagirijus: Configure Laravel to create all tables with engine InnoDB. This only works when starting with an empty DB.

In app/Config/database.php in the 'mysql' array change 'engine' => none, to 'engine' => 'InnoDB',.

Use at your own risk
Both approaches worked for me, but I am not familiar with the application, so cannot foresee every potential side effect.

@ssddanbrown Any interest in making engine in database.php configurable via env? I could try to come up with a PR.

[ssddanbrown]

Any interest in making engine in database.php configurable via env? I could try to come up with a PR.

@ArlorNaturalScientific Not really to be honest, I don't want to introduce a separate concern/option that will need specific guidance, especially since the only scenario this should only occur in is sketchy hosting environments where the user may not be so technical. It'd be an awkward one to provide advice/guidance for in the scenario it'd be a problem.

Really, MyISAM should be obsolete, especially as a default.
There's a potential case for setting the default value to 'InnoDB', but I'd be hesitant with that too due to the unforeseeable consequences with that kind of change.
There's been relatively little report of this as an issue, and it should only ever become lesser of an issue so I'm not sure it's worth actioning and widening configuration support for in 2023.