EE4 - can't create a db table with mixed collation when installing an add-on
Developer Preview
7 years ago
This is roughly my SQL for creating a Stash table on installation:
CREATE TABLE `exp_stash` (
`id` int(11) unsigned NOT NULL auto_increment,
`site_id` int(4) unsigned NOT NULL default '1',
`session_id` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci default NULL,
`bundle_id` int(11) unsigned NOT NULL default '1',
`key_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`key_label` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci default NULL,
`created` int(10) unsigned NOT NULL,
`expire` int(10) unsigned NOT NULL default '0',
`parameters` MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`id`),
UNIQUE KEY `cache_key` (`key_name`,`bundle_id`,`site_id`,`session_id`),
KEY `bundle_id` (`bundle_id`),
KEY `site_id` (`site_id`),
KEY `expire` (`expire`)
) ENGINE=InnoDBBut when processed by ee()->db->query() every instance of this…
CHARACTER SET utf8 COLLATE utf8_general_ci…is automatically changed to…
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ciWhich throws an exception:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci default NULL, `bundle' at line 4:
CREATE TABLE `exp_stash` ( `id` int(11) unsigned NOT NULL auto_increment, `site_id` int(4) unsigned NOT NULL default '1', `session_id` varchar(40) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci default NULL, `bundle_id` int(11) unsigned NOT NULL default '1', `key_name` varchar(255) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `key_label` varchar(255) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci default NULL, `created` int(10) unsigned NOT NULL, `expire` int(10) unsigned NOT NULL default '0', `parameters` MEDIUMTEXT DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`id`), UNIQUE KEY `cache_key` (`key_name`,`bundle_id`,`site_id`,`session_id`), KEY `bundle_id` (`bundle_id`), KEY `site_id` (`site_id`), KEY `expire` (`expire`) ) ENGINE=InnoDB
7 years ago
Ah, it looks like we’re changing it to DEFAULT CHARACTER SET on column definitions, which is the actual issue (can’t DEFAULT on a column). Thanks Mark!