TermWeb 3 migration to 4 fails with "Data truncated for column 'api_key' at row 1"
Problem
For very old installations database scheme may be outdated and then update script will fail with message:
Migration V4.0.36__convert_to_utf8mb4.sql failed------------------------------------------------ SQL State : 01000Error Code : 1265Message : Data truncated for column 'api_key' at row 1Location : db/migration/mysql/V4.0.36_convert_to_utf8mb4.sql (/opt/tomcat/webapps/ROOT/WEB-INF/classes/db/migration/mysql/V4.0.36_convert_to_utf8mb4.sql)Line : 94Statement : ALTER TABLE `tw_client` CHANGE `api_key` `api_key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
Or with something similar on another column.
Solution
Scripts may fail when columns has different constraints. For this kind of problems you may find next SQL statements which updates common places with problems:
ALTER TABLE `tw_client`
CHANGE COLUMN `oid` `oid` varchar(14) NOT NULL DEFAULT '',
CHANGE COLUMN `created` `created` datetime NOT NULL,
CHANGE COLUMN `changed` `changed` datetime NOT NULL,
CHANGE COLUMN `created_by` `created_by` varchar(14) NOT NULL DEFAULT '',
CHANGE COLUMN `changed_by` `changed_by` varchar(14) NOT NULL DEFAULT '',
CHANGE COLUMN `name` `name` varchar(255) NOT NULL,
CHANGE COLUMN `login` `login` varchar(255) NOT NULL,
CHANGE COLUMN `max_concurrent_users` `max_concurrent_users` int NOT NULL,
CHANGE COLUMN `max_user_accounts` `max_user_accounts` int NOT NULL,
CHANGE COLUMN `active` `active` tinyint NOT NULL,
CHANGE COLUMN `authkey` `authkey` varchar(255) NOT NULL,
CHANGE COLUMN `api_key` `api_key` varchar(255) NOT NULL;
ALTER TABLE tw_concept CHANGE COLUMN `created` `created` datetime NOT NULL, CHANGE COLUMN `changed` `changed` datetime NOT NULL;
ALTER TABLE tw_dictionaries CHANGE COLUMN `created` `created` datetime NOT NULL, CHANGE COLUMN `changed` `changed` datetime NOT NULL;
ALTER TABLE tw_domain CHANGE COLUMN `created` `created` datetime NOT NULL, CHANGE COLUMN `changed` `changed` datetime NOT NULL;
ALTER TABLE tw_exportsettings CHANGE COLUMN `created` `created` datetime NOT NULL, CHANGE COLUMN `changed` `changed` datetime NOT NULL;
ALTER TABLE tw_field CHANGE COLUMN `created` `created` datetime NOT NULL, CHANGE COLUMN `changed` `changed` datetime NOT NULL;
ALTER TABLE tw_filter CHANGE COLUMN `created` `created` datetime NOT NULL, CHANGE COLUMN `changed` `changed` datetime NOT NULL;
ALTER TABLE tw_groups CHANGE COLUMN `created` `created` datetime NOT NULL, CHANGE COLUMN `changed` `changed` datetime NOT NULL;
ALTER TABLE tw_historyitem CHANGE COLUMN `date` `date` datetime NOT NULL;
ALTER TABLE tw_importsettings CHANGE COLUMN `created` `created` datetime NOT NULL, CHANGE COLUMN `changed` `changed` datetime NOT NULL;
ALTER TABLE tw_scheduledjobresult CHANGE COLUMN `start_date` `start_date` datetime NOT NULL, CHANGE COLUMN `end_date` `end_date` datetime NOT NULL;
ALTER TABLE tw_sections CHANGE COLUMN `created` `created` datetime NOT NULL, CHANGE COLUMN `changed` `changed` datetime NOT NULL;
ALTER TABLE tw_synchronization CHANGE COLUMN `elements_changed` `elements_changed` datetime NOT NULL, CHANGE COLUMN `contents_changed` `contents_changed` datetime NOT NULL;
ALTER TABLE tw_term CHANGE COLUMN `created` `created` datetime NOT NULL, CHANGE COLUMN `changed` `changed` datetime NOT NULL;
ALTER TABLE tw_user CHANGE COLUMN `created` `created` datetime NOT NULL, CHANGE COLUMN `changed` `changed` datetime NOT NULL;
ALTER TABLE tw_userlog CHANGE COLUMN `login` `login` datetime NOT NULL, CHANGE COLUMN `logout` `logout` datetime DEFAULT NULL;
ALTER TABLE tw_view CHANGE COLUMN `created` `created` datetime NOT NULL, CHANGE COLUMN `changed` `changed` datetime NOT NULL;
ALTER TABLE tw_virtualfile CHANGE COLUMN `created` `created` datetime NOT NULL, CHANGE COLUMN `changed` `changed` datetime NOT NULL;
UPDATE tw_exportsettings d
JOIN tw_exportsettings s ON s.oid = d.oid
SET d.file_name = s.name
WHERE d.file_name IS NULL;
UPDATE tw_groups g
SET g.lang_code3 = ''
WHERE g.lang_code3 IS NULL;
UPDATE tw_term t
SET t.config = ''
WHERE t.config IS NULL;
UPDATE tw_term t
SET t.src_term_oid = ''
WHERE t.src_term_oid IS NULL;