|
Reproducible on empty datadirs as well (create one by mysql_install_db from 5.6.13, start 10.0.4 on it, run mysql_upgrade, try to use, observe the error).
While technically it is a downgrade (10.0.4 contains InnoDB of 5.6.10), still, it should work whenever possible, and this seems to be the case.
Jan,
Could you please take a look at it along with MDEV-5010?
|
|
This is already fixed on more recent version of MariaDB e.g. 10.0.6. Only error I see are:
131120 16:11:10 [ERROR] Missing system table mysql.roles_mapping; please run mysql_upgrade to create it
131120 16:11:10 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position
14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIO
NS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2',
'MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_
ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE'
,'INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','N
O_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_
QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTG
RESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','M
YSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_A
131120 16:11:10 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the
Event Scheduler.
131120 16:11:10 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Ta
ble 'mysql.gtid_slave_pos' doesn't exist
131120 16:11:10 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '10.0.7-MariaDB' socket: '/home/jan/mysql/newdb/mysql.sock2' port: 3306 Source distribution
|
|
Experienced this on a MySQL 5.7.38 to mariadb 10.6.11 migration. Looking at the prtype of innodb_index_stats and innodb_table_stats it seems the prtype differs in MySQL vs MariaDB. mysqlupgrade should prob rebuild these tables to avoid issues.
MySQL 5.7.38 pre upgrade:
mysql> SELECT a.name AS `Table`, b.TABLE_ID,b.NAME,b.POS,b.MTYPE,b.PRTYPE,b.LEN from innodb_sys_tables a, innodb_sys_columns b where a.name like 'mysql/innodb_table_stats' and a.table_id=b.table_id order by b.table_id,b.pos;
|
+--------------------------+----------+--------------------------+-----+-------+---------+-----+
|
| Table | TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN |
|
+--------------------------+----------+--------------------------+-----+-------+---------+-----+
|
.
|
.
|
| mysql/innodb_table_stats | 27 | last_update | 2 | 3 | 525575 | 4 |
|
.
|
.
|
+--------------------------+----------+--------------------------+-----+-------+---------+-----+
|
6 rows in set (0.00 sec)
|
|
mysql> SELECT a.name AS `Table`, b.TABLE_ID,b.NAME,b.POS,b.MTYPE,b.PRTYPE,b.LEN from innodb_sys_tables a, innodb_sys_columns b where a.name like 'mysql/innodb_index_stats' and a.table_id=b.table_id order by b.table_id,b.pos;
|
+--------------------------+----------+------------------+-----+-------+---------+------+
|
| Table | TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN |
|
+--------------------------+----------+------------------+-----+-------+---------+------+
|
.
|
.
|
| mysql/innodb_index_stats | 28 | last_update | 3 | 3 | 525575 | 4 |
|
.
|
.
|
+--------------------------+----------+------------------+-----+-------+---------+------+
|
8 rows in set (0.00 sec)
|
Mariadb post running mysql upgrade, prtype is incorrect leading to the following errs:
2023-02-27 20:32:52 11 [ERROR] InnoDB: Fetch of persistent statistics requested for table `db`.`table` but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
|
2023-02-27 20:32:52 11 [ERROR] InnoDB: Column last_update in table mysql.innodb_table_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL
|
MariaDB [information_schema]> SELECT a.name AS `Table`, b.TABLE_ID,b.NAME,b.POS,b.MTYPE,b.PRTYPE,b.LEN from innodb_sys_tables a, innodb_sys_columns b where a.name like 'mysql/innodb_table_stats' and a.table_id=b.table_id order by b.table_id,b.pos;
|
+--------------------------+----------+--------------------------+-----+-------+---------+-----+
|
| Table | TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN |
|
+--------------------------+----------+--------------------------+-----+-------+---------+-----+
|
.
|
.
|
| mysql/innodb_table_stats | 27 | last_update | 2 | 3 | 525575 | 4 |
|
.
|
.
|
+--------------------------+----------+--------------------------+-----+-------+---------+-----+
|
6 rows in set (0.001 sec)
|
|
MariaDB [information_schema]> SELECT a.name AS `Table`, b.TABLE_ID,b.NAME,b.POS,b.MTYPE,b.PRTYPE,b.LEN from innodb_sys_tables a, innodb_sys_columns b where a.name like 'mysql/innodb_index_stats' and a.table_id=b.table_id order by b.table_id,b.pos;
|
+--------------------------+----------+------------------+-----+-------+---------+------+
|
| Table | TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN |
|
+--------------------------+----------+------------------+-----+-------+---------+------+
|
.
|
.
|
| mysql/innodb_index_stats | 28 | last_update | 3 | 3 | 525575 | 4 |
|
.
|
.
|
+--------------------------+----------+------------------+-----+-------+---------+------+
|
8 rows in set (0.000 sec)
|
Running optimize to rebuild table corrects this, note change in prtype post optimize:
MariaDB [information_schema]> optimize table mysql.innodb_table_stats;
|
+--------------------------+----------+----------+-------------------------------------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+--------------------------+----------+----------+-------------------------------------------------------------------+
|
| mysql.innodb_table_stats | optimize | note | Table does not support optimize, doing recreate + analyze instead |
|
| mysql.innodb_table_stats | optimize | status | OK |
|
+--------------------------+----------+----------+-------------------------------------------------------------------+
|
2 rows in set (0.051 sec)
|
|
MariaDB [information_schema]> optimize table mysql.innodb_index_stats;
|
+--------------------------+----------+----------+-------------------------------------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+--------------------------+----------+----------+-------------------------------------------------------------------+
|
| mysql.innodb_index_stats | optimize | note | Table does not support optimize, doing recreate + analyze instead |
|
| mysql.innodb_index_stats | optimize | status | OK |
|
+--------------------------+----------+----------+-------------------------------------------------------------------+
|
2 rows in set (0.018 sec)
|
|
MariaDB [information_schema]> SELECT a.name AS `Table`, b.TABLE_ID,b.NAME,b.POS,b.MTYPE,b.PRTYPE,b.LEN from innodb_sys_tables a, innodb_sys_columns b where a.name like 'mysql/innodb_table_stats' and a.table_id=b.table_id order by b.table_id,b.pos;
|
+--------------------------+----------+--------------------------+-----+-------+---------+-----+
|
| Table | TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN |
|
+--------------------------+----------+--------------------------+-----+-------+---------+-----+
|
.
|
.
|
| mysql/innodb_table_stats | 100 | last_update | 2 | 3 | 526087 | 4 | <-- Note change in prtype
|
.
|
.
|
+--------------------------+----------+--------------------------+-----+-------+---------+-----+
|
6 rows in set (0.000 sec)
|
|
MariaDB [information_schema]> SELECT a.name AS `Table`, b.TABLE_ID,b.NAME,b.POS,b.MTYPE,b.PRTYPE,b.LEN from innodb_sys_tables a, innodb_sys_columns b where a.name like 'mysql/innodb_index_stats' and a.table_id=b.table_id order by b.table_id,b.pos;
|
+--------------------------+----------+------------------+-----+-------+---------+------+
|
| Table | TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN |
|
+--------------------------+----------+------------------+-----+-------+---------+------+
|
.
|
.
|
| mysql/innodb_index_stats | 101 | last_update | 3 | 3 | 526087 | 4 | <-- Note change in prtype
|
.
|
.
|
.
|
+--------------------------+----------+------------------+-----+-------+---------+------+
|
8 rows in set (0.000 sec)
|
|
mariadb_100004.tar.gz