Updating to 7.x fails with a Specified key was too long fatal error in update #7201

After some quick investigation, it appears that the update hook was incorrectly specifying a larger column length than hook_schema. While I'm not sure how the above error message would result, the latest dev release has a fix for this and it should be in the upcoming final release. Thanks.

No. Not even the dev version works. Same error.

Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes: ALTER TABLE {accesslog} CHANGE `sid` `sid` VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'Browser session ID of user that visited page.'; Array ( ) in db_change_field() (line 2888 of /home/httpd/html/website/includes/database/database.inc).

well it is not fixed. I am getting:

The following updates returned messages

statistics module

Update #7000
Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes: ALTER TABLE {accesslog} CHANGE `sid` `sid` VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'Browser session ID of user that visited page.'; Array ( ) in db_change_field() (line 2888 of /var/www/drupal-sites/drupal-7.0/includes/database/database.inc).

I am getting a similar error, but do not get as far as an update number.

Error message

    * Notice: date_default_timezone_set(): Timezone ID '43200' is invalid in drupal_session_initialize() (line 262 of /home/don/public_html/drupal/includes/session.inc).
    * PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes: ALTER TABLE {sessions} ADD PRIMARY KEY (`sid`, `ssid`); Array ( ) in db_add_primary_key() (line 2762 of /home/don/public_html/drupal/includes/database/database.inc).

The website encountered an unexpected error. Please try again later.

Same error here.

I've got this reported as update #7201

The following updates returned messages
votingapi module
Update #7201
Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes: ALTER TABLE {votingapi_cache} CHANGE `content_type` `entity_type` VARCHAR(64) DEFAULT NULL; Array ( ) in db_change_field() (line 2986 of /[MYPATH]/includes/database/database.inc).

I took a look at the indexes in the table, and the longest one is "content_vtype_tag_func" which is made up of content_type (length 20), content_id (length 10), value_type (length 20), tag (length 128), and `function` (length 128). I (somewhat arbitrarily) limited tag and function to 100 characters (in the indexes, not the table field definition) and reran the uipdate, yielding the following:

The following updates returned messages
votingapi module
Update #7201
Failed: DatabaseSchemaObjectDoesNotExistException: Cannot change the definition of field votingapi_vote.content_type: field doesn't exist. in DatabaseSchema_mysql->changeField() (line 449 of /[MYPATH]/includes/database/mysql/schema.inc).

My assumption here is that the field names were actually changed prior to the "key too long" error. Sure enough, votingapi_vote had the specified field names changed. So I changed entity_type back to content_type and entity_type back to content_type in votingapi_vote (votingapi_cache did not have the names changed) and reran. The update ran successfully.

I suggest looking at Matthew Lord's explanation at http://bugs.mysql.com/bug.php?id=6604 and limiting the lengths of the keys used to make indices. I suspect that those long indices are a drag on performance anyhow, but I don't know—they could be necessary or coding standards might contraindicate my suggestion.

This could be a problem, because the tables would have to be changed prior to this update, which means just tacking the change on as update #7202 won't cut the mustard. In any case, my tale here should indicate a workaround for anyone unafraid to manually mess with their db index definitions.

Also, anyone getting "ALTER TABLE {accesslog}" or any table name other than "ALTER TABLE {votingapi_cache}" should look at #1261056: d6 to d7 statistics module update fails 'Specified key was too long' as it's not something related to the votingapi module.

thx smscotten - this was very helpful :)

The problem described in comment #1 is still occurring with votingapi v2.6, wherin the changing of a field in the votingapi_vote or votingapi_cache tables results in a fatal error:

Update #7201
    Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes: ALTER TABLE {votingapi_cache} CHANGE `content_type` `entity_type` VARCHAR(64) DEFAULT NULL; Array ( ) in db_change_field()

The attached patch properly checks for the existence of the old fields before attempting to safely rename them. It does not fix the 1071 Specified key was too long error, however.

At least this patch will allow the true error to surface each and every time the update hook is run instead of masking the real error with another fake error. :) I'm still working on the "key too long" problem, and I need to find a solution quickly, so if anyone has ideas, fire away.

So here's what we had to do in order to get this module to upgrade itself from D6 to D7. Yeah, it's a lot, but at least it works now. :) I recommend not putting this patch into the module as a "fix", but instead addressing the invalid key lengths.

Just attaching this patch to the issue in case someone else needs a solution to get themselves migrated into D7.

(All code by @dankessler)

#14 worked just fine for me. Cheers @Senpai

#14 fixed my problems too.

#14 rocked here too. Thxs.

ok 3 people confirm #14 works. Marking as RTBC

Patch still applies cleaning, minus 1 line of whitespace and offset of 38 lines.

Eventhough no one is upgrading from 6.x to 7.x any more, I still think this is critical.

Attach is the patch #14 minus the whitespace and the update to date 7.x-2.x-dev. This patch passes coder too.

Patch #18 fixes the upgrade for me, too. THX

Could someone please send me a failing D6 database dump? I'd like to give it a closer look than just commiting #14.

Fix commited in 7.x-2.x http://drupalcode.org/project/votingapi.git/commit/60694cb .

Has someone verified with schema module if the schema matches 100% now?

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes: ALTER TABLE {sessions} ADD PRIMARY KEY (`sid`, `ssid`); Array ( ) in db_add_primary_key() (line 2891 of D:\Program Files\Zend\Apache2\htdocs\knmupgrade\includes\database\database.inc).

I don't see how the last error can come from a votingapi update. It seems to deal with the core-session table. Note that drupal assumes that you are using the InnoDB DB-Engine - this error seems to come from a MyISAM table.

Updating from 6 to 7.2.11 gets this error:

Update #7201
Failed: DatabaseSchemaObjectDoesNotExistException: Cannot change the definition of field <em class="placeholder">votingapi_vote</em>.<em class="placeholder">content_type</em>: field doesn't exist. in DatabaseSchema_mysql->changeField() (line 454 of /home/testeo/public_html/includes/database/mysql/schema.inc).

@Rosumanda: Please don't post different cases to already closed issues.

It seems that you've already attempted the upgrade to D7 but it failed for some other reason. If the problem persists please open a new issue.

Sorry about that.
As I read that this issue was related to Update #7201 I thought in reopening this one instead of creating a (maybe, somewhat) duplicate new one.
I´ll post a new issue.
Thanks! :)