Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27757

Database upgrade fails: slow_log table

    XMLWordPrintable

    Details

      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:
            danblack Daniel Black
            Reporter:
            DrP3pp3r Wolfgang Petroschka
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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