René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"key length errors (jobs table)","articleBody":"While performing some DB maintenance testing, I encountered an error:\r\n\r\n```\r\npymysql.err.InternalError: (1071, 'Specified key was too long; max key length is 767 bytes')\r\n```\r\n\r\nin Ubuntu 16.04/MariaDB 10.0 -\r\n\r\nDigging further, it appears that the jobs table key length \u0026 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.\r\n\r\nVarious posts/documentation pertaining to the InnoDB related parameters are below -\r\nOn this particular setup, enabling:\r\n\r\n```\r\ninnodb_file_format=Barracuda\r\ninnodb_large_prefix=on\r\n```\r\nto ```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:\r\n\r\n```\r\nCREATE TABLE IF NOT EXISTS `pipline`.`~jobs` (\r\n`table_name` varchar(255) NOT NULL COMMENT \"className of the table\",\r\n`key_hash` char(32) NOT NULL COMMENT \"key hash\",\r\n`status` enum('reserved','error','ignore') NOT NULL COMMENT \"if tuple is missing, the job is available\",\r\n`key` blob DEFAULT NULL COMMENT \"structure containing the key\",\r\n`error_message` varchar(4096) NOT NULL DEFAULT \"\" COMMENT \"error message returned if failed\",\r\n`error_stack` blob DEFAULT NULL COMMENT \"error stack if failed\",\r\n`user` varchar(255) NOT NULL DEFAULT \"\" COMMENT \"database user\",\r\n`host` varchar(255) NOT NULL DEFAULT \"\" COMMENT \"system hostname\",\r\n`pid` int unsigned NOT NULL DEFAULT \"0\" COMMENT \"system process id\",\r\n`connection_id` bigint unsigned NOT NULL DEFAULT \"0\" COMMENT \"connection_id()\",\r\n`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT \"automatic timestamp\",\r\nPRIMARY KEY (`table_name`,`key_hash`)\r\n) ENGINE=InnoDB, ROW_FORMAT=DYNAMIC, COMMENT \"job reservation table for `pipline`\"\r\n```\r\n\r\nThis 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.\r\n\r\nVarious links:\r\n\r\nhttps://github.com/gogits/gogs/issues/4894 - details provided about similar issue\r\nhttps://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html - see issues concerning innodb restrictions\r\nhttps://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-specification.html - misc on row formats\r\nhttps://mariadb.com/kb/en/library/xtradbinnodb-storage-formats/ - mariadb specific information\r\n\r\n","author":{"url":"https://github.com/ixcat","@type":"Person","name":"ixcat"},"datePublished":"2018-03-01T23:55:48.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":6},"url":"https://github.com/453/datajoint-python/issues/453"}