[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

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