Duncan Roe added a comment - 2013-10-18 05:13 The uploaded tar archives were both taken with server stopped. sql_50614 gives no errors in Oracle mysqld 5.6.14. mariadb_100004 has 2 server 10.0.4 sessions in mysql/dwarfstar.err. 2nd session shows the persistent error. Users in database are root/root and dunc/dunc. As root, I used mysql_upgrade -proot

Elena Stepanova added a comment - 2013-10-18 10:52 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 ?

Jan Lindström (Inactive) added a comment - 2013-11-20 16:13 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

Park Jeong Su (Inactive) added a comment - 2014-06-23 06:13 Started mariadb_10.0.12 with mysql_5.6.16 databaste. also, ran mysql_upgade Stopped and re-started server. Following messages still appear: 140623 11:27:05 mysqld_safe Starting mysqld daemon with databases from /home/mariadb/mariadb-data 140623 11:27:06 [Note] InnoDB: Using mutexes to ref count buffer pool pages 140623 11:27:06 [Note] InnoDB: The InnoDB memory heap is disabled 140623 11:27:06 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 140623 11:27:06 [Note] InnoDB: Compressed tables use zlib 1.2.3 140623 11:27:06 [Note] InnoDB: Using CPU crc32 instructions 140623 11:27:06 [Note] InnoDB: Initializing buffer pool, size = 4.0G 140623 11:27:06 [Note] InnoDB: Completed initialization of buffer pool 140623 11:27:06 [Note] InnoDB: Highest supported file format is Barracuda. 140623 11:27:06 [Note] InnoDB: 128 rollback segment(s) are active. 140623 11:27:06 [Note] InnoDB: Waiting for purge to start 140623 11:27:06 [Note] InnoDB: Percona XtraDB ( http://www.percona.com ) 5.6.17-65.0 started; log sequence number 2377750992406 2014-06-23 11:27:06 7fe9ef9f2700 InnoDB: Error: Column last_update in table "mysql"."innodb_table_stats" is BINARY(4) NOT NULL but should be INT NOT NULL (type mismatch). 2014-06-23 11:27:06 7fe9ef9f2700 InnoDB: Error: Fetch of persistent statistics requested for table "mysql"."gtid_slave_pos" 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. 140623 11:27:06 [Note] Server socket created on IP: '0.0.0.0'. 140623 11:27:06 [Note] Event Scheduler: Loaded 0 events 140623 11:27:06 [Note] /home/mariadb/bin/mysqld: ready for connections. Version: '10.0.12-MariaDB-log' socket: '/home/mariadb/mariadb-data/mariadb.socket' port: 3307 Source distribution

Jan Lindström (Inactive) added a comment - 2014-06-23 10:19 - edited "I'm seeing this error, as well as one of our partners, when doing an upgrade from MySQL 5.6.17 to MariaDB 10.0.12"

Michael Widenius added a comment - 2014-06-24 02:34 The following is just a guess where the problem could be: In ha_innodb.cc::get_innobase_type_from_mysql_type() we have in 5.6: case MYSQL_TYPE_TIMESTAMP: switch (field->real_type()) { case MYSQL_TYPE_TIME: case MYSQL_TYPE_DATETIME: case MYSQL_TYPE_TIMESTAMP: return(DATA_INT); default: /* Fall through */ DBUG_ASSERT((ulint)MYSQL_TYPE_DECIMAL < 256); case MYSQL_TYPE_TIME2: case MYSQL_TYPE_DATETIME2: case MYSQL_TYPE_TIMESTAMP2: return(DATA_FIXBINARY); } In MariaDB 10.0 we have: case MYSQL_TYPE_TIMESTAMP: if (field->key_type() == HA_KEYTYPE_BINARY) return(DATA_FIXBINARY); else return(DATA_INT); The likely reason the code is failing in 10.0 is that the field is stored as MYSQL_TYPE_TIMESTAMP2 in the .frm file (as it's exported from 5.6) and either Field_timestampf() doesn't have field->key_type() to return HA_KEYTYPE_BINARY or the field is not read as a Field_timestampf() in MariaDB 10.0. I did a quick code check in 10.0 and the code looks correct. We need to check this in a debugger to see why the current code fails.

Arjen Lentz added a comment - 2014-09-02 05:55 - edited awesome - thanks for fixing this, Serg!

Shivam added a comment - 2015-01-20 19:29 This is not really fixed up on due usage on CPanel/WHM server running Maria DB 10.0.15 22:27:45 server root@5997911 ~]cPs# tail /var/lib/mysql/server.brandyou.in.err 2015-01-20 22:27:25 7fdb46fb6700 InnoDB: Error: Column last_update in table "mysql"."innodb_table_stats" is BINARY(4) NOT NULL but should be INT NOT NULL (type mismatch). 2015-01-20 22:27:25 7fdb46fb6700 InnoDB: Error: Fetch of persistent statistics requested for table "photoz_wp84"."wp_wfVulnScanners" 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. 2015-01-20 22:27:25 7fdb46fb6700 InnoDB: Error: Column last_update in table "mysql"."innodb_table_stats" is BINARY(4) NOT NULL but should be INT NOT NULL (type mismatch). 2015-01-20 22:27:25 7fdb46fb6700 InnoDB: Error: Column last_update in table "mysql"."innodb_table_stats" is BINARY(4) NOT NULL but should be INT NOT NULL (type mismatch). 2015-01-20 22:27:25 7fdb46fb6700 InnoDB: Error: Column last_update in table "mysql"."innodb_table_stats" is BINARY(4) NOT NULL but should be INT NOT NULL (type mismatch). 2015-01-20 22:27:25 7fdb46fb6700 InnoDB: Error: Fetch of persistent statistics requested for table "photoz_wp84"."wp_wfLockedOut" 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. 2015-01-20 22:27:25 7fdb4cbff700 InnoDB: Error: Column last_update in table "mysql"."innodb_table_stats" is BINARY(4) NOT NULL but should be INT NOT NULL (type mismatch). 2015-01-20 22:27:25 7fdb4cbff700 InnoDB: Error: Fetch of persistent statistics requested for table "photoz_wp84"."wp_wfLeechers" 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. 2015-01-20 22:27:25 7fdb4cbff700 InnoDB: Error: Column last_update in table "mysql"."innodb_table_stats" is BINARY(4) NOT NULL but should be INT NOT NULL (type mismatch). 2015-01-20 22:27:25 7fdb4cbff700 InnoDB: Error: Fetch of persistent statistics requested for table "photoz_wp84"."wp_wfHits" 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.

Federico Razzoli added a comment - 2015-10-04 15:44 - edited Does this bug also affect 10.1.7? (note, however, the 1136 error after the other errors) root@ubu1:/usr/local/mysql# ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql Installing MariaDB/MySQL system tables in '/var/lib/mysql' ... 2015-10-04 14:30:34 140696390404032 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.1.7-MariaDB-log) starting as process 3431 ... 2015-10-04 14:30:34 140696390404032 [Note] CONNECT: Version 1.03.0007 July 05, 2015 2015-10-04 14:30:34 140696390404032 [Note] InnoDB: Using mutexes to ref count buffer pool pages 2015-10-04 14:30:34 140696390404032 [Note] InnoDB: The InnoDB memory heap is disabled 2015-10-04 14:30:34 140696390404032 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2015-10-04 14:30:34 140696390404032 [Note] InnoDB: Memory barrier is not used 2015-10-04 14:30:34 140696390404032 [Note] InnoDB: Compressed tables use zlib 1.2.3 2015-10-04 14:30:34 140696390404032 [Note] InnoDB: Using Linux native AIO 2015-10-04 14:30:34 140696390404032 [Note] InnoDB: Using CPU crc32 instructions 2015-10-04 14:30:34 140696390404032 [Note] InnoDB: Initializing buffer pool, size = 256.0M 2015-10-04 14:30:34 140696390404032 [Note] InnoDB: Completed initialization of buffer pool 2015-10-04 14:30:34 140696390404032 [Note] InnoDB: Highest supported file format is Barracuda. 2015-10-04 14:30:34 140696390404032 [Note] InnoDB: 128 rollback segment(s) are active. 2015-10-04 14:30:34 140696390404032 [Note] InnoDB: Waiting for purge to start 2015-10-04 14:30:34 140696390404032 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.25-73.1 started; log sequence number 36477166193 2015-10-04 14:30:34 140695473981184 [Note] InnoDB: Dumping buffer pool(s) not yet started 2015-10-04 14:30:34 7ff66e5d5700 InnoDB: Error: Column last_update in table "mysql"."innodb_table_stats" is INT UNSIGNED NOT NULL but should be BINARY(4) NOT NULL (type mismatch). 2015-10-04 14:30:34 7ff66e5d5700 InnoDB: Error: Fetch of persistent statistics requested for table "mysql"."gtid_slave_pos" 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. ERROR: 1136 Column count doesn't match value count at row 1 2015-10-04 14:30:34 140696390404032 [ERROR] Aborting   2015-10-04 14:30:34 140695457195776 [Note] InnoDB: FTS optimize thread exiting. 2015-10-04 14:30:34 140696390404032 [Note] InnoDB: Starting shutdown... 2015-10-04 14:30:37 140696390404032 [Note] InnoDB: Shutdown completed; log sequence number 36477166203 2015-10-04 14:30:37 140696390404032 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

Duncan Roe added a comment - 2015-10-22 13:07 I can confirm it is fixed at mysqld 10.0.21-MariaDB I am offered a link to 10.1.8 (not 7) - will try that next

Duncan Roe added a comment - 2015-10-22 14:26 10.1.8 fails to build on my 32-bit Slackware 14.1 system. Undefined references to __atomic_fetch_add_8 and __atomic_load_8. These entry points are available in /usr/lib/libatomic.so.1.0.0 Will report as a new bug if I can't fix it easily tomorrow or soon after

Marc Reilly added a comment - 2023-02-27 20:49 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)