Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes: ALTER TABLE {wysiwyg_user} DROP INDEX `uid`; Array ( ) in db_drop_index()
Comment | File | Size | Author |
---|---|---|---|
#22 | wysiwyg-database_update_error-2840699-22.patch | 1.19 KB | TonyT |
#18 | wysiwyg-database_update_error-2840699-18.patch | 428 bytes | heilop |
Comments
Comment #2
TwoDThat query should not be able to produce that error. Seems information is missing here and it's more likely the last query in update 7203 which is causing problems when trying to create the primary index.
Please provide more information here.
Which Wysiwyg version did you upgrade from?
Which database and which version of it are you using? Could it be MySQL 5.6? Perhaps this is relevant: https://bugs.mysql.com/bug.php?id=68453
I'm assuming the database is using UTF-8?
Comment #3
Code Rader CreditAttribution: Code Rader as a volunteer commentedYes, it is on update 7203.
I was upgrading from 7.x-2.2.
Yes, I do have MySQL 5.6
I reduced the length of format to 128 from 255 and it worked.
Comment #4
TwoDDo you get errors when installing Drupal from scratch too?
It seems at least the filter module should trigger the same error as it has a primary key combining the format field (255 chars) with the name field (32 chars), for a total of 287 chars (867 bytes on UTF-8 if they're all 3 chars long).
Comment #5
Code Rader CreditAttribution: Code Rader as a volunteer commentedNo I don't. The interesting thing is the error is triggered on trying to drop the uid index.
I actually had to drop the uid index manually and then run my modified 7203 update.
Comment #6
TwoDStrange that it errors out when running Drupal's query, but not when running it manually. Did you run the exact same query to drop the original uid index or something else?
Comment #7
Code Rader CreditAttribution: Code Rader as a volunteer commentedI dropped it from phpMyAdmin.
Comment #8
TwoDWhat happens if you set the wysiwyg_user uid field length back to 255 and then try to create the primary key from within phpmyadmin?
Comment #9
Code Rader CreditAttribution: Code Rader as a volunteer commentedNo. The highest I seem to be able to set is 160
Comment #10
TwoDI can't reproduce this with the database versions I've got around so I'm not sure what to do here.
The format field size is determined by the filter module and if Wysiwyg is to remain 100% compatible it should use the same field size, same with the uid field of course. I think it's unlikely the format name would actually be that long. But like I said before, other modules create primary keys including 255 long fields so this error should pop up everywhere.
Comment #11
akshitggrwl CreditAttribution: akshitggrwl commentedGot the same error but in db_change_field().
Database version: 5.5.5-10.0.23-MariaDB-log
Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes: ALTER TABLE {wysiwyg_user} CHANGE `format` `format` VARCHAR(255) NOT NULL, ADD PRIMARY KEY (`uid`, `format`), ADD INDEX `uid` (`uid`); Array ( ) in db_change_field()
Found the work around.
`format` uses collation 'utf8mb4_general_ci', i.e. 4 bytes per character.
so total 255 * 4 > 767
Following commands did the trick:
1. set global innodb_file_format = BARRACUDA;
2. set global innodb_large_prefix = ON;
Comment #12
akshitggrwl CreditAttribution: akshitggrwl commentedSyntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes: ALTER TABLE {wysiwyg_user} DROP INDEX `uid`; Array ( ) in db_drop_index()
Comment #13
TwoDStill can't reproduce this so I'm shooting in the dark.
What if you modify
wysiwyg_update_7203()
in wysiwyg.install to read:Comment #14
gumanov CreditAttribution: gumanov commentedI just experienced the same issue.
I'm in the process of testing an upgrade from Drupal 6.x to 7.x
My WYSIWYG 6.x version was 6.x-2.4+54-dev, should I upgrade to 6.x-2.5 before attempting going to 7.x?
The exact error was:
Don't know if this is related, but in my status report page, Drupal says:
Database 4 byte UTF-8 support Disabled
Comment #15
TwoD@gumanov, yes, that would be best. The D6 and D7 versions are very similar, but Drupal will likely also run through all the updates hooks in the D7 version when you upgrade from D6. This should be fine however, since each hook checks to make sure its changes have not already been applied. Just make sure you have a backup of the current DB in case something goes wrong.
I would also recommend you go directly to 7.x-2.x-dev as it includes some fixes in the update hooks, if the next release is not out by the time you do it.
Comment #16
TonyT CreditAttribution: TonyT commentedI was upgrading from wysiwyg-6.x-2.5 to wysiwyg-7.x-2.4. update.php gave the error:
Rerunning update.php gave the same error.
I am using MySQL 5.6.35. The database being used has utf8_general_ci collation.
Before running the update the update the format field of the wysiwyg_user table was int(11). MySQL Workbench showed that the primary key was (uid, format).
From the above comments I managed to fix it by using MySQL Workbench to manually remove the primary key for uid and format from the wysiwyg_user table, then reran the unmodified update.php successfully.
After this the format field of the wysiwyg_user table was varchar(255) and the primary key was (uid, format).
Comment #17
TwoD@TonyT update 7000 should have changed the format field type to varchar way before 7203 runs. If that didn't happen there's something weird going on. I have honestly not tested a D6->D7 upgrade in a long time so I don't know off the top of my head what could have happened. Would probably have to run it with a debugger to see why the field didn't change (or if that update didn't run at all), and before that set up a new D6 site. :)
Good you managed to work around it though.
Comment #18
heilop CreditAttribution: heilop as a volunteer commentedIn my opinion, in the
wysiwyg_update_7203
, before to add new primary keys, is necessary to remove existing ones.Here a little patch to fix that.
Comment #19
heilop CreditAttribution: heilop as a volunteer commentedComment #21
TwoDReverted to the original summary, lost in #12.
@heilop, Thanks for the patch! I don't think that field would have had a primary key prior to that update hook being added, but attempting to remove one should not hurt so I'll accept the patch. I'm not sure it fixes the original issue, which apparently happened earlier when dropping the 'uid' index but it should fix any possible issues with adding the new primary key.
These errors look fairly rare and are difficult for me to reproduce so I'm more or less going to give up on trying more fixes for this in code.
If anyone still has the original error (too long key when the uid index is dropped) and can offer some insight, please re-open this issue and provide as much details as possible (steps to reproduce and/or possible patches/solutions).
Comment #22
TonyT CreditAttribution: TonyT commentedReopening this as I had another example of this error and ran the updates in the debugger to see what was happening. Here is what I found:
Was updating from wysiwyg 6.x-2.5. The wysiwyg_user table already existed with the following structure:
Update 7000 is dropping the index for the wysiwyg table, not the wysiwyg_user table.
Update 7200 is adding the wysiwyg_user table if it does not exist. But if it does already exist it changes the format field to varchar(255). But it is not dropping the indexes that already exist. From wysiwyg 6.x-2.5 the indexes are not the same as being created if the table does not exist. In fact the primary index may become too big when the format field is changed to be varchar(255). In my case no error was reported. Seems this update needs to cater for the possible indexes that may exist by removing both the possible primary key and the uid key. Then set the uid and format indexes. If this is not done update 7203 may fail.
So when update 7203 executes the wysiwyg_user table may still have a primary key uid, format. Dropping the uid and format keys does not affect the primary key.
The attached patch is an attempt to fix the issue. It works for me, but I am not sure if this is the right way:-)
Comment #24
TwoDOk, that makes sense. Thank you to everyone involved in tracking down this bug, could not have done it without you!