ctools_object_cache.name is not big enough

I encountered the same problem while panelizing paragraphs. The name is too short and I solved it on my system by increasing the size of the name field from varchar(128) to varchar(192).

I would prefer to not shorten / cut paragraph machine names and field / panelizer display names because of readability and maintainability.

The question is, does such a change have a negative impact on the ctools module?

I have faced same issue. If you use Field collection item within field collection item - you will get such error.

Increasing size of name column from 128 to 192 solved issue for me.

So any objections to just updating name field to 255? Attaching patch...

Adjusted comment

Hi Darren, quick review. A couple of issues, nothing major.

1. +++ b/ctools.install
   @@ -62,7 +62,7 @@ function ctools_schema_2() {
      // update the 'name' field to be 128 bytes long:
   

   Comment: 128 needs changing.

2. +++ b/ctools.install
   @@ -263,3 +263,14 @@ function ctools_update_7001() {
   + */ ¶
   

   Trailing space on line: wil fail to commit.

OK updated

Good to go :-)

Had to refactor the patch a little to work with the ctools schema stuff. but otherwise, looks good. Committed.

When updating (on OVH hosting provider), I got the following error:

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 ( ) dans db_change_field() (ligne 3076 dans /home/theologi/www/includes/database/database.inc).

This causes another issue for me too: I tend prefer to use MyISAM tables for my local development installations. This increases the performance for me by factor 2-3. This change breaks compatibility with MyISAM.

Anyway a module using ctools_object_cache should make sure that the names it uses are not longer than the column size in this table. That’s something panels should take care of in this case.

Increasing size of the key field alone shouldnt break compatibility of MyISAM, neither should it degrade performance by 200-300%.
While it is true that key size could have an influence on performance, this should (from my understanding) only apply if a table has too many indizes or the index for the table has not been refreshed / recreated properly.

http://web.synametrics.com/top10performancetips.htm:

When an index is created, the data for indexed columns is stored on the disk. When new rows of data are inserted into the table or the values in the indexed columns are changed, the database may have to reorganize the storage of the data to make room for the new rows. This reorganization can take additional toll on DML queries. However, if you expect new rows on a regular basis in any table, you can specify the expected growth for an index.

Related issue: https://www.drupal.org/project/ctools/issues/2941920 with the aim to replace three primary key fields by one aggregated primary key.

@Rudi: MyISAM performes 2-3 faster for Drupal, if you don’t need transactions. For live sites you definitely want transactions but for development it’s viable to use MyISAM to decrease the time for needed for running tests (and increases your efficiency).

So no the increased index size doesn’t deteriorate performance on its own, but it makes the primary key bigger than the 1000Bytes MyISAM can deal with. Thereby making it impossible to use MyISAM for development purposes. Thereby decreasing performance for development work.

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. …

https://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html

… also increasing the column to 255chars does not actually fix the original problem.

Ah ok, thanks for the clarification. :)

In the related issue they changed the content of the name primary key from "name" to "md5(name)", so it will probably solve the myisam issues that surfaced from this issue as well. The problem is, that there are still some update issues with the latest suggested patches, but I think a solution should just be a matter of days.