Issues creating tables with older MySQL/MariaDB versions

[ladowny]

Issues creating tables

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Reason: MySQL has a prefix limitation of 767 bytes in InnoDB, and 1000 bytes in MYISAM.

Solution: add
CHARACTER SET utf8 COLLATE utf8_general_ci
after
AUTO_INCREMENT=1
in CREATE TABLE report SQL query

Working query
CREATE TABLE report(serialint(10) unsigned NOT NULL AUTO_INCREMENT,mindatetimestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),maxdatetimestamp NULL DEFAULT NULL,domainvarchar(255) NOT NULL,orgvarchar(255) NOT NULL,reportidvarchar(255) NOT NULL,emailvarchar(255) DEFAULT NULL,extra_contact_infovarchar(255) DEFAULT NULL,policy_adkimvarchar(20) DEFAULT NULL,policy_aspfvarchar(20) DEFAULT NULL,policy_pvarchar(20) DEFAULT NULL,policy_spvarchar(20) DEFAULT NULL,policy_pcttinyint(3) unsigned DEFAULT NULL,raw_xml mediumtext DEFAULT NULL, PRIMARY KEY (serial), UNIQUE KEY domain (domain,reportid), KEY maxdate (maxdate) ) AUTO_INCREMENT=1 CHARACTER SET utf8 COLLATE utf8_general_ci;