[MDEV-27757] Database upgrade fails: slow_log table - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
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
Component/s: Upgrades
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.

Attachments

Activity

Ascending order - Click to sort in descending order

Wolfgang Petroschka created issue - 2022-02-07 10:14

Wolfgang Petroschka made changes - 2022-02-07 10:15

Field	Original Value	New Value
Description	When trying to upgrade a MySQL 5.1.14 to MariaDB 10.6.4 mariadb-upgrade fails: {code:bash} 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 {code} 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: {code:c} "ALTER TABLE slow_log\n" " ADD COLUMN thread_id BIGINT(21) UNSIGNED NOT NULL AFTER sql_text;\n", {code} 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: {code:bash} 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) {code} {code:bash} 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) {code} So, the table is already using the CSV storage engine and has nullable columns in MySQL.	When trying to upgrade a MySQL 5.1.14 database to MariaDB 10.6.4 mariadb-upgrade fails: {code:bash} 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 {code} 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: {code:c} "ALTER TABLE slow_log\n" " ADD COLUMN thread_id BIGINT(21) UNSIGNED NOT NULL AFTER sql_text;\n", {code} 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: {code:bash} 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) {code} {code:bash} 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) {code} So, the table is already using the CSV storage engine and has nullable columns in MySQL.

Sergei Golubchik made changes - 2022-02-07 18:26

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.7 [ 24805 ]

Sergei Golubchik added a comment - 2022-02-07 18:29

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.

Sergei Golubchik added a comment - 2022-02-07 18:29 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.

Sergei Golubchik made changes - 2022-02-07 18:29

Status

Open [ 1 ]

Needs Feedback [ 10501 ]

Wolfgang Petroschka added a comment - 2022-02-07 18:46

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.

Wolfgang Petroschka added a comment - 2022-02-07 18:46 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.

Wolfgang Petroschka added a comment - 2022-02-08 12:55 - edited

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

Wolfgang Petroschka added a comment - 2022-02-08 12:55 - edited @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...

Wolfgang Petroschka made changes - 2022-02-08 13:01

Status

Needs Feedback [ 10501 ]

Open [ 1 ]

Wolfgang Petroschka made changes - 2022-02-08 13:01

Comment

[ See previous comment... ]

Sergei Golubchik made changes - 2022-02-11 14:15

Assignee

Daniel Black [ danblack ]

Ralf Gebhardt made changes - 2022-08-04 08:45

Fix Version/s

10.2 [ 14601 ]

Julien Fritsch made changes - 2023-03-03 17:27

Fix Version/s

10.7 [ 24805 ]

Julien Fritsch made changes - 2023-04-27 13:51

Fix Version/s

10.3 [ 22126 ]

Daniel Black made changes - 2023-09-19 03:55

Status

Open [ 1 ]

In Progress [ 3 ]

Daniel Black made changes - 2023-09-19 03:55

Assignee	Daniel Black [ danblack ]	Andrew Hutchings [ JIRAUSER52179 ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Daniel Black added a comment - 2023-09-19 22:53

Thanks DrP3pp3r, finally got to this.

Daniel Black added a comment - 2023-09-19 22:53 Thanks DrP3pp3r , finally got to this.

Daniel Black made changes - 2023-09-19 22:53

Fix Version/s		10.4.32 [ 29300 ]
Fix Version/s		10.5.23 [ 29012 ]
Fix Version/s		10.6.16 [ 29014 ]
Fix Version/s		10.10.7 [ 29018 ]
Fix Version/s		10.11.6 [ 29020 ]
Fix Version/s		11.0.4 [ 29021 ]
Fix Version/s		11.1.3 [ 29023 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Assignee	Andrew Hutchings [ JIRAUSER52179 ]	Daniel Black [ danblack ]
Resolution		Fixed [ 1 ]
Status	In Review [ 10002 ]	Closed [ 6 ]

Wolfgang Petroschka added a comment - 2023-09-20 05:44

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

Wolfgang Petroschka added a comment - 2023-09-20 05:44 Awesome. Thanks @Daniel Black for fixing this! Nice software archaeological research.

People

Assignee:: Daniel Black

Reporter:: Wolfgang Petroschka

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2022-02-07 10:14

Updated:: 2023-09-25 03:20

Resolved:: 2023-09-19 22:53

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration