Details
Major Description
When trying to upgrade a MySQL 5.1.14 database to MariaDB 10.6.4 mariadb-upgrade fails:
Phase 4/7: Running 'mysql_fix_privilege_tables' |
ERROR 1178 (42000) at line 174: The storage engine for the table doesn't support nullable columns |
FATAL ERROR: Upgrade failed
|
After this error, the database is destroyed beyond repair.
After studying the source code of mariadb-upgrade and mariadb-check for a while, I found out that the following SQL statement from mysql_fix_privilege_tables_sql.c is causing the problem:
"ALTER TABLE slow_log\n"
|
" ADD COLUMN thread_id BIGINT(21) UNSIGNED NOT NULL AFTER sql_text;\n", |
If I drop the slow_log table before the upgrade everything works smoothly and the slow_log table is recreated.
Output from MySQL 5.1.14:
mysql> desc slow_log;
|
+----------------+--------------+------+-----+-------------------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+----------------+--------------+------+-----+-------------------+-------+
|
| start_time | timestamp | NO | | CURRENT_TIMESTAMP | |
|
| user_host | mediumtext | NO | | | |
|
| query_time | time | NO | | | | |
| lock_time | time | NO | | | | |
| rows_sent | int(11) | NO | | | |
|
| rows_examined | int(11) | NO | | | |
|
| db | varchar(512) | YES | | NULL | |
|
| last_insert_id | int(11) | YES | | NULL | |
|
| insert_id | int(11) | YES | | NULL | |
|
| server_id | int(11) | YES | | NULL | |
|
| sql_text | mediumtext | NO | | | |
|
+----------------+--------------+------+-----+-------------------+-------+
|
11 rows in set (0.00 sec) |
mysql> show create table mysql.slow_log;
|
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| slow_log | CREATE TABLE `slow_log` (
|
`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
`user_host` mediumtext NOT NULL,
|
`query_time` time NOT NULL, |
`lock_time` time NOT NULL, |
`rows_sent` int(11) NOT NULL,
|
`rows_examined` int(11) NOT NULL,
|
`db` varchar(512) DEFAULT NULL,
|
`last_insert_id` int(11) DEFAULT NULL,
|
`insert_id` int(11) DEFAULT NULL,
|
`server_id` int(11) DEFAULT NULL,
|
`sql_text` mediumtext NOT NULL
|
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' | |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec) |
So, the table is already using the CSV storage engine and has nullable columns in MySQL.