ERROR 1146 (42S02): Table 'mysql.general_log' doesn't exist

Question

After mysql upgrade I'm getting this error on my Centos box when I tried to enable general_log. Any idea?

SET GLOBAL general_log = 'ON';

ERROR 1146 (42S02) : Table 'mysql.general_log' doesn't exist

Answer 1

I have created that missing table and worked for me.

Login to mysql console

use mysql;

CREATE TABLE general_log(
event_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
user_host mediumtext NOT NULL,
thread_id int(11) NOT NULL,
server_id int(10) unsigned NOT NULL,
command_type varchar(64) NOT NULL,
argument mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'

Answer 2

When you find yourself in this situation then you problem have done a MySQL upgrade and incorrectly carried over the datadir (e.g. /usr/local/var/mysql) to the new installation.

So the accepted solution above will solve your immediate problem but it also indicates that you might have other problems with your MySQL install as well.

Answer 3

just an Addition to Harikrishnan's answer! I had to alter the fields type to work from me as MYSQL could not write to table so:

1. if general_log is enabled, turn it off SET GLOBAL general_log= 0;

2. Create table USE mysql; CREATE TABLE mysql.general_log( event_time TIMESTAMP(6) NOT NULL DEFAULTCURRENT_TIMESTAMP(6) ON UPDATECURRENT_TIMESTAMP(6), user_host MEDIUMTEXT NOT NULL, thread_id BIGINT(21)UNSIGNED NOT NULL, server_id INT(10) UNSIGNED NOT NULL, command_type VARCHAR(64) NOT NULL, argument MEDIUMBLOB NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';

3. reenable logging SET GLOBAL general_log= 1;

4. view log SELECT * FROM mysql.general_log;