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.
Attachments
Activity
If you re-run the upgrade it fails in another spot and complains about a missing user.
I don't have access to the target device atm, so I can't reproduce it, but I will do so tomorrow and post the output here.
@Sergei Golubchik:
When I run the upgrade (and it fails as described above), then drop the slow_log table and then try to upgrade again the following error appears:
Processing databases
|
[Some DBs...]
|
mysql
|
mysql.user
|
Error : The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
error : Corrupt
|
[More DBs...]
|
Phase 4/7: Running 'mysql_fix_privilege_tables' |
ERROR 1449 (HY000) at line 32: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 41: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 84: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 93: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 111: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 197: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 206: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 208: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 219: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 224: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 227: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 351: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 354: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 423: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 428: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 430: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 433: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 437: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 442: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 463: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
ERROR 1449 (HY000) at line 471: The user specified as a definer ('mariadb.sys'@'localhost') does not exist |
FATAL ERROR: Upgrade failed
|
Looks pretty much broken beyond repair to me... 
Awesome. Thanks @Daniel Black for fixing this!
Nice software archaeological research.
What do you mean, a database is destroyed beyond repair? It seems you should only remove the offending table and run mariadb-upgrade again to have everything upgraded properly.