Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type

November 27, 2018 1.6k views
MySQL CentOS

I cannot restart mysql by using systemctl restart mysql, and /var/log/mysqld.err shows:

181122  4:47:33 [Note] /usr/sbin/mysqld: Normal shutdown

181122  4:47:34  InnoDB: Starting shutdown…
181122  4:47:35  InnoDB: Shutdown completed; log sequence number 1834096
181122  4:47:35 [Note] /usr/sbin/mysqld: Shutdown complete

 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
181127  5:52:21 [Note] Plugin 'FEDERATED' is disabled.
181127  5:52:21 InnoDB: The InnoDB memory heap is disabled
181127  5:52:21 InnoDB: Mutexes and rw_locks use GCC atomic builtins
181127  5:52:21 InnoDB: Compressed tables use zlib 1.2.3
181127  5:52:21 InnoDB: Using Linux native AIO
181127  5:52:21 InnoDB: Initializing buffer pool, size = 128.0M
181127  5:52:21 InnoDB: Completed initialization of buffer pool
181127  5:52:21 InnoDB: highest supported file format is Barracuda.
181127  5:52:21  InnoDB: Waiting for the background threads to start
181127  5:52:22 InnoDB: 5.5.60 started; log sequence number 1834096
181127  5:52:22 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
181127  5:52:22 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
181127  5:52:22 [Note] Server socket created on IP: '0.0.0.0'.
181127  5:52:22 [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
181127  5:52:22 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
181127  5:52:22 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.60'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

Status information:

Current dir: /var/lib/mysql/
Running threads: 0  Stack size: 262144
Current locks:
lock: 0x7f54ec09ad30:

lock: 0x7f54ec098a80:

lock: 0x7f54ec08b250:

lock: 0x7f54ec07b790:

lock: 0x7f54ec076cf0:

lock: 0x7f54ec074a80:

lock: 0x7f54ec045f00:

lock: 0x7f54ec0722b0:

lock: 0x7f54ec067ef0:

lock: 0x7f54ec05ff70:

lock: 0x7f54ec05e090:

lock: 0x7f54ec0560c0:

lock: 0x7f54ec04f710:

lock: 0x7f54ec04c360:

lock: 0x7f54ec048e40:

lock: 0x7f54ec03bc50:

lock: 0x7f54ec038160:

lock: 0x7f54ec0426a0:

lock: 0x7f54ec02e6f0:

lock: 0x7f54ec032b60:

lock: 0x7f54ec01f1d0:

lock: 0x7f54ec005d80:

lock: 0x7f54ec01baa0:

lock: 0x7f54ec0116c0:

lock: 0x1a6cd20:

lock: 0x1a352f0:

lock: 0x1a2a6c0:

lock: 0x1a0f900:

lock: 0x1a1bd40:

lock: 0x1a07020:

lock: 0x1a01d60:

lock: 0x19fb8d0:

lock: 0x19f3280:

lock: 0x1083b80:

lock: 0x10841a0:

lock: 0x1084280:

lock: 0x1084360:

lock: 0x1083c60:

lock: 0x1083d40:

lock: 0x1083aa0:

lock: 0x10838e0:

lock: 0x10839c0:

lock: 0x1084440:

lock: 0x1083e20:

lock: 0x10840c0:

lock: 0x1083fe0:

lock: 0x1083f00:

lock: 0x1083640:

lock: 0x1083720:

lock: 0x1083800:


Key caches:
default
Buffer_size:       8388608
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:           115
not flushed:             0
w_requests:             13
writes:                 11
r_requests:            739
reads:                 115

What should I do to fix this error?

1 Answer

Try this:

mysql_upgrade -u root

If you know your mysql root pass:

mysql_upgrade -u root -p
Have another answer? Share your knowledge.