Specified key was too long; max key length is 1000 bytes

[cheesegrits]

I ship getid3 with a Joomla extension, and have found that where users have a multi-byte default character set like UTF8 in MySQL, the create_table() in extension.cache.mysql.php blows up with "Specified key was too long; max key length is 1000 bytes", because the VARCHAR(500) filename field index winds up being too long, as each character is 3 bytes.

I've kind of hacked around it by adding "CHARACTER SET latin1" to the CREATE TABLE, although I'm not sure if this is a valid fix, as I'm really not sure what different OS's do with multibyte characters in filenames.

[JamesHeinrich]

The other workaround is to reduce the length of the filename field to 330 characters, which should still be sufficient for most cases. I have made that change in https://github.com/JamesHeinrich/getID3/commit/3e4b8260d55a3869d38abfcec59956fc0016c49a

[cheesegrits]

Thanks.

I went for the character set solution as a couple of my users actually have crazy long path names. If it turns into a problem, I'll PR something to check for the length of $filename in analyze() and "miss" if it's longer than 330.

If I get really enthusiastic, I'll PR a change to mysqli or PDO, so avoid those deprecated warnings. :)

Anyway - thanks for the fix.

[JamesHeinrich]

With a latin1 filename field there's no reason the length couldn't be increased from 500 up to 990 if you like.

I have quickly thrown together a clone of getid3.extension.cache.mysql.php to getid3.extension.cache.mysqli.php and changed the MySQL calls to MySQLi. I haven't really tested it however, so if you want to see how well it works for you I would appreciate that: https://github.com/JamesHeinrich/getID3/commit/adfd32a4ac2ed0ddfd111cadd0b46152373de373

[cheesegrits]

I submitted a PR with fixes for the new code.

I suspect you may need to fix that CHARSET usage in the original code as well.

[cheesegrits]

Just to be clear about that CHARSET fix, I don't know if it's a MySQL version thing, but all the versions I use, it's CHARACTER SET not CHARSET ...

http://dev.mysql.com/doc/refman/5.7/en/charset-database.html

[JamesHeinrich]

I got CHARSET from show create table and in the two installations I have at hand (5.6 and 5.1) they both show CHARSET there, but do accept CHARACTER SET. I have copied it to the mysql version.

[Aybee]

This line did not work for me https://github.com/JamesHeinrich/getID3/blob/v1.9.17/getid3/extension.cache.mysqli.php#L211 (edit: changed the link from master to v1.9.17)

Table gets not created. Error Message Specified key was too long; max key length is 767 bytes

It's a MaraiDB with a DB of collation latin1_swedish_ci. I have to set this value to 767.

If I set the DB to collation utf8_general_ci then the error also occurs and its not possible to create the table even with 767.

I think its necessary to improve the table create process so it will fit to more situations.

[StudioMaX]

As a workaround we can create a new column like hash VARCHAR(32) NOT NULL DEFAULT '', use it in primary key instead of previous filename and search for md5($filename) instead of $filename. But this will be a BC-break for all previously created tables.

[JamesHeinrich]

I have made a tentative change in https://github.com/JamesHeinrich/getID3/commit/5e911573d5f06f94345e563959bd9f8f066070a4 to just the MySQLi version. If it seems like a reasonable change then I can migrate that to the other versions of the cache extension as well.