Update 7002 Specified key was too long

Essaye un drush cc all avant de faire drush updb

Bizarre un peu parce qu'on change le champ de 128 à 255 varchar
? Ça fait pas de sens

Peut être une faille dans MySql 5.6.38

I ran into the same problem running. If I remove name from the PK I can change the column.
Might be that the problem is related to:
https://stackoverflow.com/questions/8746207/1071-specified-key-was-too-l...

Same problem:
The following updates returned messages
ctools module
Update #7002

Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes: ALTER TABLE {ctools_object_cache} CHANGE `name` `name` VARCHAR(255) NOT NULL; Array ( ) i db_change_field() (linje 3076 af /home2/ordblind/public_html/includes/database/database.inc).

Same here

Update:

ctools module
Update #7002

Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes: ALTER TABLE {ctools_object_cache} CHANGE `name` `name` VARCHAR(255) NOT NULL; Array ( ) in db_change_field() (line 3076 of /home/ra261/richadventure.com/includes/database/database.inc).

Try cache clear then updb again? Does that help?
It's just a cache table.
Maybe truncate table ctools_object_cache
The updb, however cache clear should do it.

From reading this issue I suspect that the error is caused because my database engine is set to MyISAM. Years ago I changed all my tables to be MyISAM to fix a compatibility issue with my host, MediaTemple.

Via phpMyAdmin I ran the following SQL command:

ALTER TABLE `ctools_object_cache` ENGINE = InnoDB;

Then ran the cTools update and no longer got the error. Tested on two of my sites and seemed to work in both.

NOTE: I am a noob who normally interacts with Drupal via the Web UI, so please let me know if this is an incorrect approach. I know little to nothing about MySQL and backend-ish stuff.

makes sense, yes you should normally be using InnoDB these days for most (or all) Drupal tables when using MySQL except if your database administrator says otherwise (without getting into the details of why you might want to run MyISAM in some cases).

RTBC #9 , for those having this issue when using MySQL (or MariaDB or Persona), you'll want to make sure this table is using the InnoDB engine and not the MyISAM. Switch it as described in #2941920-9: Update 7002 Specified key was too long

To improve this, we could write a patch and modify the ctools.install file ctools_requirements checking that if a mysql driver is in use to perform a check to ensure that the InnoDB engine is enabled for the ctools_object_cache table making sure that MyISAM is not enabled for this table.

With all that said, I wonder if there's a way to make this work for those that are stuck on MyISAM due to hosting limitations (some service providers only allow MyISAM and not InnoDB).

My table storage engine is InnoDB and I've cleared my drupal caches but still gets this problem!
Any other solution?!

There is no patch so setting the status back to Active and against the 1.x-dev branch.

#9 worked for me. Thx

#9 worked for me.

The solution in #9 is definitely the way to go.

This issue has shown up in a bunch of other modules. Seems to be because it's a key and large keys. So please change the table to InnoDB.

#9 worked for me as well. Thank you!

#9 worked, thank you very much.

#9 worked for me too. Thanx!

#9 is the answer, Thank you

Didn't worked for me!
My table was already InnoDB but still have this problem!
Any other solutions?

@seenafallah , please refer to this instruction, it will most likely solve your issue. #2941951: MyISAM tables throw PDOException on updating ctools module 7.x-1.13

@josepholstad Both my system and ctools.cache Storage Engine are InnoDB but I can't do the update!

@seenafallah are you getting the same error message then?

We'll need more information to help you.

Please let us know what version of MySQL you are running?

Is it UTF8 or UTF8MB4 encoding or something else?

Is innodb_large_prefix enabled?
SHOW GLOBAL VARIABLES LIKE 'innodb_large%';

@joelpittet The version of MySQL is 5.1.73. the tables charset is utf8mb4 and innodb_large_perfix is off!

How did you get utf8mb4 on MySQL 5.1? I thought that didn’t get added till 5.5.

https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-3.html

Would be worth trying to get large prefixes enabled but I don’t feel qualified to give you how to do that.

I’d really hope not to revert this because it has a possibility to truncate data.

@seenafellah the other solution is not the same as #9, the reported solution in the other issue has a couple queries to find all myisam tables not just one and update to innodb.
#2941951: MyISAM tables throw PDOException on updating ctools module 7.x-1.13

I'm one of those who must use MyISAM due to hosting limitations.

Of course, I'd far sooner see you change CTools to be able to use either MyISAM or InnoDB as you've left me (and no doubt many others) with the option of either not doing the update or moving sites to another provider.

Not knowing much about things like this, why is it suddenly necessary to cause significant upset to people by creating this limitation?

@tschewe which host doesn't allow Innodb out of curiosity.
Here's the original bug that we fixed that caused this issue btw.

#2575673: ctools_object_cache.name is not big enough

@josepholstad All my tables are InnoDB!

@joelpittet So should I try the solution in that link or its the risk?!
If I turn on large_prefix wouldn't be okay?!

I had same issue updating to 7.x.1.13 from 7.x.1.12 after updating database. I'm using Mysql.

@joelpittet I'm on MediaTemple's managed host, Gridserver. To be clear, they allow Innodb, but Drupal takes a huge performance hit and throws PDO Exceptions all over the place when certain tables aren't MyISAM. I don't know why this is the case. For some background, this thread is where I found out I needed to convert my database engine to MyISAM: https://www.drupal.org/node/1041298#comment-5188198

This was years ago, so MediaTemple might have fixed some of these problems? This ctools update was the first time in all these years that I encountered a conflict because of MyISAM tables.

MySql version: 5.7.21-0ubuntu0.16.04.1
utf8_general_ci
MyISAM
latin1_swedish_ci

Hi, Changing DBEngine was not possible in my case and what i did was to change the code in install File as following:

function ctools_update_7002() {
db_change_field('ctools_object_cache', 'name', 'name', array(
'type' => 'varchar',
'length' => '100',
'not null' => TRUE,
));
}
and it solved my problem.

cheers!

Any reasons why it shouldnt just use Text instead of varchar?

#9 seems to fix the issue.

@duckzland the only reason this is an issue is because it’s used in an index.

We could remove the index but that could have some performance issues.

@seenafallah with enough backups it’s fine to change, it’s also the default for newer versions of MySQL. I just don’t want to claim expertise (IANADBA)

@joelpittet I have Drupal 7 sites hosted with Shaw Business which turns out to be a reseller for megawebservers.com. They do not provide InnoDB and have no plans to add it in the near future.

Unfortunately, I seem to find out about host limitations after something goes wrong like this. No way of knowing the requirement up front as it changed in mid-stream and it's a nuisance to move.

I guess it all comes down to balance. I can't use my sites fully now without a move, and others have to limit file name size if it doesn't change. Which choice causes the least damage?

@tscheweDo you know what version of MySQL they are running out of curiosity?

I think I have a "make everybody happy" solution, so here's a patch for people to test.

1. Removes the schema change.
2. Hash's the $name argument so that it doesn't break when it's too long.

This is a great start! Two things I'd probably want get answers on:

1) If I already updated, we need a way to revert back the change. So we'd need another update hook to change the schema key back.
2) The cache should probably be cleared on the update, otherwise you have to wait for cron to execute to get the old cache entries cleared.

1) I was thinking it wasn't a big deal to leave it, but here's an update hook to move it back.
2) The cost of leaving the cache there is likely super minimal for a week but I through in a db_delete() for good measure.

Patch #50 works for me.

Patch #50 worked for me as well.

If it's going to run md5() on every value going into the 'name' column, why not just change it to varchar(32)?

OTOH the 'cid' key field in the core cache tables is 255, so maybe this can be solved another way?

Ah, I checked the schema again and saw it uses a compound primary key with three fields. Ick.

Are the extra parts of the key really needed or could it change to just 'sid'?

Ah, 'sid' is the session ID, so no, that can't be a solitary primary key.

Would it blow people's minds if we added a new primary key to replace the three separate ones?

Food for thought. I haven't tested it yet ;-)

Oh for crying out loud, Damien.

Testbot finally worked. Awesome.

Server version: 5.5.58-MariaDB

Thank you very much for reconsidering. I'm sure that you have saved many headaches for Chaos Tools users.

To save me mucking about in an area I'm not that familiar with, instead of applying the patch, can I just wait and apply the next update and accomplish the same thing?

@tschewe: Yes, I'm hoping we'll have some agreement in a day or two and then a new release can be made.

The "ctools-n2941920-59.patch" did not work for me.
It created db errors that I noticed when i tried to login to the site, and the site broke with "The site encountered an error".

Server: 10.1.26-MariaDB-0+deb9u1

I applied the ctools-n2941920-59.patch, however I am still getting the same SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes when I run drush updb.

I think because the patch doesn't remove ctools_update_7002. Probably needs to be removed like I did in #50

PERFECT; Thanks joelpittet, #50 took care of that!

Here's the patch from #59 with the changes needed from #50 to avoid the error for those who get it.

I don't see the benefit of reverting the 'name' field back if it's no longer part of the primary index, it's just going to cause the problem that lead to it being increased in the first place.

Oh, and in #59 I forgot to delete the two unnecessary fields.

(and I still need to actually manually test the patch... duh Damien)

@DamienMcKenna the failure most people seem to be having is the ctools_update_7002 update like I mentioned. So without removing that change in the previous update it won't get past that.

Oh now I get it. Duh Damien.

Yep, I concur Doctor.

ya it gets a bit tricky when it comes to fixing .install files. sorry I haven't had a chance to review any of these patches but some great work being done here, I like Damiens approach as well having peeked at it and followed the thread a bit. I have nothing against MyISAM , it is a very fast engine and I can understand why some people would want to use it. For speed, my servers are using brute force for speed, I've got high performance SSD on a raid array and I use InnoDB, that is why I didn't have this issue others are not so lucky , I spent a fortune on my servers.

The schema functions don't need to change, just update 7002.

After applying patch #76 am now getting total site outage with error:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'c.obj' in 'where clause': SELECT s.uid, c.updated FROM {ctools_object_cache} c INNER JOIN {sessions} s ON c.sid = s.sid WHERE s.sid <> :session_id AND c.obj = :obj AND c.name = :name ORDER BY c.updated ASC; Array ( [:session_id] => TySAGxTgW82KHlLvMyNGqSIP34kj0U-0VJnUZ6zwx9U [:obj] => view [:name] => panoptic_server_get_sites ) in ctools_object_cache_test() (line 146 of /home/panoptic/public_html/sites/all/modules/ctools/includes/object-cache.inc).

Should patch #76 work by itself, or is there another patch I need to apply?

@Stephen Ollman: Thanks for the review. Patch #76 should work on its own. The error you saw indicates that the patch needs work.

I wonder if we should just go back to #50? *sigh* The 'name' column could then be made smaller as it'd only need to deal with 32 characters.

WARNING!!! patch #76

After applying patch #76 I was unable to access my site and had to literally roll back the code and database in order to get it to work again, as the patch updates the database removing exiting columns and as such rolling back the code fails just as bad.

I guess this probably needs to be said(maybe a few times):

*Don't test this out in production.*

It's dealing with the database schema, though it's only a cache table... so make sure you take a dump of it and it's create schema and just re-import it after you revert the patch.

The idea @DamienMcKenna has is much cleaner than my md5 solution so I was pulling for that one even though more things to check. In this case the sid that is getting dropped is also used as a sessions foreign key in ctools_object_cache_test() and ctools_object_cache_test_objects() which would have to be changed but I think the join may become much less efficient, thought @DamienMcKenna?

And I very much appreciate his effort and all of yours for testing this to help out the rest of the community who need and/or wants to keep their engine.

wondering if a hook_requirements would be appropriate in this case.
So add a hook_requirements phase that checks to see if mysql driver is in use, if it is, check the table engine for ctools_object_cache_test_objects() , if it is myisam, provide helpful information such as instructions on changing the table engine to innodb.

the same code could be called from the ctools_update_7200 , prevent the upgrade if myisam is in use.

this way, we avoid the complication of supporting myisam, while at the same time providing a helpful instruction on changing the engine to innodb?

or go the patch 50 way. Not sure, maybe just looking for lazy solution.

FYI: this issue was discussed by the core maintainers back in 2010:
#821768: hook_requirements() should warn if no transaction support, provide INNODB upgrade script

but they never actually did anything about it.

Hello I had the same issue, #50 helped.

Okay, #9 worked for me - was doing update from Version: 7.x-1.12 --> Version: 7.x-1.13. Thank you! Don't call Chaos Tools for nothing... ;)

#50 works for me!

#9 seemed to work for me

I like the look of #76 a lot more than #50, just needs a bit of ironing to get it over the line.

for Damiens patch #2941920-76: Update 7002 Specified key was too long
perhaps to deal with #2941920-78: Update 7002 Specified key was too long add a simple cache clear on the first line of:

function ctools_update_7003() {
  //flush caches before blowing away ctools table
  drupal_flush_all_caches();
  /*
   * snipped
   */
  //then clear again after new table 
  drupal_flush_all_caches();
}

@joseph.olstad: What would the benefit be of clearing all caches?

Looking at the two approaches, I think #50 should work for both those who've successfully updated and those who have seen the failures in this issue. Lets put #50 in.

In order to unblock this and another issue around tests not working for PHP 5.3. I'm going to commit #50 and if people are interested they can tackle alternative and maybe better solutions in a follow-up.

Thanks for everybody on the patch for contributing! I've pushed this to -dev and plan to release this afternoon.

Woohoo! Thanks everyone!

Standing on the shoulders of giants, thanks everyone!

I just performed the 7.14 update without problem.

Thank you very much for making the change to continue to support MyISAM tables! I am sure that many will join me in expressing that sentiment as it has saved us no end of trouble.

Ok, coming into this a year later, I updated ctools module to 7.x-1.15. I had no trouble update the db on local, and a staging site, but the production site update.php fails with this message:
An AJAX HTTP request terminated abnormally. Debugging information follows. Path: https://mysite.org/update.php?op=selection&token=hashtoken&op=do StatusText: ResponseText: ReadyState: 4

Watchdog reports no error. I had figured any issues related to this would be resolved. The staging db is an older clone of the production site.

Found this issue, but this table is running InnoDb. Parsing through the responses it appears that it is resolved, but it stops all other updates.
Looking at the ctools.install I see this:

function ctools_update_7002() {
  // Removed due to alternative database configuration issues.
  // @see https://www.drupal.org/project/ctools/issues/2941920 which is this page.
}

This may not be a real issue per se with ctools or perhaps more with the update.php file not understanding how to deal with empty updates?
At least it seems that ctools_update_7003() resets the name back to 128 characters and my site shouldn't crash? Just that subsequent db_updates on production are not run yet.