key length errors (jobs table)

[ixcat]

While performing some DB maintenance testing, I encountered an error:

pymysql.err.InternalError: (1071, 'Specified key was too long; max key length is 767 bytes')

in Ubuntu 16.04/MariaDB 10.0 -

Digging further, it appears that the jobs table key length & default database config for this setup result in a key longer than 767 bytes - default character set is utf8mb4 which results in (255+32)*4 for the key.

Various posts/documentation pertaining to the InnoDB related parameters are below -
On this particular setup, enabling:

innodb_file_format=Barracuda
innodb_large_prefix=on

to my.cnf and adding ROW_FORMAT=DYNAMIC to the query allowed the table to be created manually. Table creation (datajoint 0.10.0) with the single addition of the row format argument is as follows:

CREATE TABLE IF NOT EXISTS `pipline`.`~jobs` (
`table_name` varchar(255) NOT NULL COMMENT "className of the table",
`key_hash` char(32) NOT NULL COMMENT "key hash",
`status` enum('reserved','error','ignore') NOT NULL COMMENT "if tuple is missing, the job is available",
`key` blob DEFAULT NULL COMMENT "structure containing the key",
`error_message` varchar(4096) NOT NULL DEFAULT "" COMMENT "error message returned if failed",
`error_stack` blob DEFAULT NULL COMMENT "error stack if failed",
`user` varchar(255) NOT NULL DEFAULT "" COMMENT "database user",
`host` varchar(255) NOT NULL DEFAULT "" COMMENT "system hostname",
`pid` int unsigned NOT NULL DEFAULT "0" COMMENT "system process id",
`connection_id` bigint unsigned NOT NULL DEFAULT "0" COMMENT "connection_id()",
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT "automatic timestamp",
PRIMARY KEY (`table_name`,`key_hash`)
) ENGINE=InnoDB, ROW_FORMAT=DYNAMIC, COMMENT "job reservation table for `pipline`"

This issue should not apparently occur on current MySQL and MariaDB InnoDB configurations since they default to DYNAMIC row configurations; That said adding the ROW_FORMAT=DYNAMIC tweak still might be useful in some situations. If nothing else, it can be noted here or perhaps documented more officially.

Various links:

gogs/gogs#4894 - details provided about similar issue
https://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html - see issues concerning innodb restrictions
https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-specification.html - misc on row formats
https://mariadb.com/kb/en/library/xtradbinnodb-storage-formats/ - mariadb specific information

[ixcat]

setup can be reproduced via the following Vagrantfile: (install vagrant, vagrant up, vagrant ssh mysql1)
Vagrantfile.txt

[ixcat]

also, changing character set should also resolve the issue (similarly might be useful in CREATE DATABASE statements)

[dimitri-yatsenko]

I see the problem. yes, the character set should be just latin1 and we should set that globally. Aslovarchar(255) is excessive for table names. varchar(80) should be enough.

[dimitri-yatsenko]

The root cause of the problem is that the default character set has changed from latin1 to UTF8mb4, causing quadrupling of char and varchar storage requirements.

I think for now, DataJoint should default to latin1. I will create a separate issue raising about controlling the encoding.

Solution:

• Change the table_name field type to varchar(90)
• Set the encoding to latin1 in all table declarations.

[ixcat]

Also note, this could be mostly a Ubuntu/Debian specific issue as far as defaults go - just found this in the default Ubuntu 16.04 server configuration file:

# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

[dimitri-yatsenko]

Since #454, we explicitly set the table character set to latin1 overriding all system defaults.

The discussion for supporting UTF8 goes into #455.

This particular issue is already closed.