Incorrect definition of table mysql.db

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

I’m seing these messages on one my 2 nodes but there’s no binary logs on that node.

2015-04-27 16:49:07 11211 [ERROR] Incorrect definition of table mysql.db: expected column ‘User’ at position 2 to have type char(16), found type char(80).
2015-04-27 16:49:07 11211 [ERROR] Incorrect definition of table mysql.event: expected column ‘definer’ at position 3 to have type char(77), found type char(141).
2015-04-27 16:49:07 11211 [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’,‘NOT_USED’,‘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’,‘NO_ENGINE_SUBSTITUTION’,‘PAD_CHAR_TO_FULL_LENGTH’), found type set(‘REAL_AS_FLOAT’,‘PIPES_AS_CONCAT’,‘ANSI_QUOTES’,‘IGNORE_SPACE’,‘IGNORE_BAD_TABLE_OPTIONS’,‘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_A
2015-04-27 16:49:07 11211 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.

My database was restored from mysqldump --all-database of MariaDB 10.0

percona-xtradb-cluster-5.6
Version: 5.6.22-25.8-978.trusty

2

when I tried to restore db & event table for database mysql (mysqldump from a percona xtradb cluster node with same version), below messages appeared on another node where restoration did not take place

2015-04-27 17:30:03 18831 [ERROR] Slave SQL: Error ‘Table ‘mysql.db’ doesn’t exist’ on query. Default database: ‘mysql’. Query: ‘/*!40000 ALTER TABLE db DISABLE KEYS /', Error_code: 1146
2015-04-27 17:30:03 18831 [Warning] WSREP: RBR event 1 Query apply warning: 1, 46360
2015-04-27 17:30:03 18831 [Warning] WSREP: Ignoring error for TO isolated action: source: 8df5a19c-ecbc-11e4-9d1f-0be6ae4e1b84 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 2145 trx_id: -1 seqnos (l: 2350, g: 46360, s: 46358, d: 46359, ts: 538828340331030)
2015-04-27 17:30:03 18831 [ERROR] Slave SQL: Error ‘Table ‘mysql.db’ doesn’t exist’ on query. Default database: ‘mysql’. Query: '/!40000 ALTER TABLE db ENABLE KEYS /', Error_code: 1146
2015-04-27 17:30:03 18831 [Warning] WSREP: RBR event 1 Query apply warning: 1, 46361
2015-04-27 17:30:03 18831 [Warning] WSREP: Ignoring error for TO isolated action: source: 8df5a19c-ecbc-11e4-9d1f-0be6ae4e1b84 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 2145 trx_id: -1 seqnos (l: 2351, g: 46361, s: 46360, d: 46360, ts: 538828384858347)
2015-04-27 17:30:10 18831 [ERROR] Slave SQL: Error ‘Table ‘mysql.event’ doesn’t exist’ on query. Default database: ‘mysql’. Query: '/!40000 ALTER TABLE event DISABLE KEYS /', Error_code: 1146
2015-04-27 17:30:10 18831 [Warning] WSREP: RBR event 1 Query apply warning: 1, 46370
2015-04-27 17:30:10 18831 [Warning] WSREP: Ignoring error for TO isolated action: source: 8df5a19c-ecbc-11e4-9d1f-0be6ae4e1b84 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 2145 trx_id: -1 seqnos (l: 2360, g: 46370, s: 46369, d: 46369, ts: 538835476300921)
2015-04-27 17:30:10 18831 [ERROR] Slave SQL: Error ‘Table ‘mysql.event’ doesn’t exist’ on query. Default database: ‘mysql’. Query: '/!40000 ALTER TABLE event ENABLE KEYS */’, Error_code: 1146
2015-04-27 17:30:10 18831 [Warning] WSREP: RBR event 1 Query apply warning: 1, 46371
2015-04-27 17:30:10 18831 [Warning] WSREP: Ignoring error for TO isolated action: source: 8df5a19c-ecbc-11e4-9d1f-0be6ae4e1b84 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 2145 trx_id: -1 seqnos (l: 2361, g: 46371, s: 46370, d: 46370, ts: 538835497820819)

3

Make sure you’re always using the same version of Xtrabackup and XtraDB Cluster components.

1-) There are many differences between Percona Server and MariaDB and this is what the MySQL is now complaining about. Are you able to restore the db and event tables from another Percona Server? Take care about the data it contains…

2-) MySQL is complaining at the end that the tables db and event do not exists. Can you check on the file system if tables db and event files are there? Remember that all the mysql system database tables are MyISAM (3 files, frm, myi, myd).

Cheers,

4

  1. post #2 are output during restore of mysql db table & mysql event table. Just realized it shutsdown after restoring those tables

  2. on node 2

mysql> show tables;
±--------------------------+
| Tables_in_mysql |
±--------------------------+
| audit_log |
| column_stats |
| columns_priv |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
±--------------------------+

on node 1

mysql> use mysql;
ERROR 1047 (08S01): Unknown command

it seems on both nodes I installed percona-xtrabackup 2.2.10-1.trusty & percona-xtradb-cluster-galera-3.x 3.9.3494.trusty

5

after purged and re-installed of node 1, I bootstrap it, then let node 2 join the cluster, there’s no more db & event table not exist error.

FYI, I’m using rsync sst method.