• Daniel Black's avatar
    MDEV-27757 Database upgrade fails from 5.1: slow_log table · bf5c2512
    Daniel Black authored
    The table structure from MySQL-5.1.14 is:
    
    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'
    
    Even as far back as MySQL-5.5.40 this table could be created as NULLs
    where not permitted in the CSV table time, but it seems they
    where allowed sometime.
    
    As the first part of mariadb-upgrade adds the column thread_id without
    correcting the 'NULL'able status of existing columns it fails.
    
    We reorder the sql statements in the ugprade as follows:
    
    ALTER TABLE slow_log MODIFY {columns} {new types} NOT NULL,....
    
    As thread_id doesn't exist in the above statement it was removed from
    the first ALTER TABLE statement to prevent failure.
    
    Previous ALTER TABLE slow_log where moved later appending thread_id
    and rows_affected, and also enforces the type of thread_id if it
    was incorrectly like the now first ALTER STATEMENT slow_log used
    to do.
    bf5c2512
mysql_system_tables_fix.sql 39.8 KB