Incorrect definition of table mysql.proc

I recently upgraded my server from Debian 8 to Debian 9, I also upgraded MySQL5.5 (Percona)  to MariaDB 10, everything seemed fine until I noticed the database was down, and i just couldn’t bring it up, it just kept dying, after some research into the logs I found the next error:

[ERROR] Incorrect definition of table mysql.proc:
 expected column 'sql_mode' at position 14 to have 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_ALL_TABLES',
'NO_ZERO_IN_DATE','NO_ZERO_DATE','INVA

 

2017-07-18 11:38:42 140335330596608 [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_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_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','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_A
ERROR: 1136  Column count doesn't match value count at row 1
2017-07-18 11:38:42 140335331021376 [ERROR] Aborting

 

In order to fix this issue first I had to start the MySQL Daemon with –skip-grant option:

/usr/sbin/mysqld --skip-grant --general-log &

 

–general-log: enables the error logging in case you don’t have any logs.
&: sends the process to the background.

Then I modify the tables that where affected:

ALTER TABLE mysql.proc MODIFY sql_mode 
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_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');

 

ALTER TABLE mysql.event MODIFY sql_mode 
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_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');

 

flush privileges;

 

Then bring the process to the foreground and end it, after that you can start the daemon in the usual way and you won’t have anymore errors.

Hope it helps.

This entry was posted in Databases, Debian, Linux, MySQL, Systemd, Unix. Bookmark the permalink.

4 Responses to Incorrect definition of table mysql.proc

  1. Niels says:

    Thanks for the info, I seem to have the same problem. But I’m kind of a noob, so can you give some additional info?

    How do I stop the daemon? Do I just kill it?

    • virgil says:

      If you didn’t disowned the process you can just bring it to the foreground with fg and press ctrl + c, you can also stop it by killing it with kill -15 or pkill mysqld, hope it helps.

  2. Niels says:

    Thanks! This resolved my error too.

  3. Ckurtz says:

    Running mysql_upgrade seems to fix this.

Leave a Reply

Your email address will not be published. Required fields are marked *

*