Not the answer you need?
Register and ask your own question!

Incorrect definition of table mysql.db

umarzukiumarzuki ContributorCurrent User Role Beginner
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

Comments

  • umarzukiumarzuki Contributor Current User Role Beginner
    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)
  • wagnerbianchiwagnerbianchi Remote DBA Current User Role Patron
    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,
  • umarzukiumarzuki Contributor Current User Role Beginner
    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
  • umarzukiumarzuki Contributor Current User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.