[MDEV-27757] Database upgrade fails: slow_log table Created: 2022-02-07  Updated: 2023-09-25  Resolved: 2023-09-19

Status: Closed
Project: MariaDB Server
Component/s: Upgrades
Affects Version/s: 10.6.4
Fix Version/s: 10.4.32, 10.5.23, 10.6.16, 10.10.7, 10.11.6, 11.0.4, 11.1.3

Type: Bug Priority: Major
Reporter: Wolfgang Petroschka Assignee: Daniel Black
Resolution: Fixed Votes: 0
Labels: None
Environment:

Embedded Linux


 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.

 Comments   
Comment by Sergei Golubchik [ 2022-02-07 ]

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.

Comment by Wolfgang Petroschka [ 2022-02-07 ]

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.

Comment by Wolfgang Petroschka [ 2022-02-08 ]

@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...

Comment by Daniel Black [ 2023-09-19 ]

Thanks DrP3pp3r, finally got to this.

Comment by Wolfgang Petroschka [ 2023-09-20 ]

Awesome. Thanks @Daniel Black for fixing this!
Nice software archaeological research.

Generated at Thu Feb 08 09:55:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.