mysqlmysql-error-1146

I dropped general_log table, how do I create it again?


Logging enabled

I enabled logging using:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

All executed queries was logging to mysql.general_log table. It is fine.

Attempt to clear the table

Then the table became large and I wanted to delete all records from the table. I executed:

DELETE FROM general_log

But this caused to an error that says I cannot lock log tables. So, I dropped the table after disabling logging:

SET GLOBAL general_log = 'OFF';
DROP TABLE general_log;

I hope that enabling logging again will create the table, but I couldn't enable it. When I execute this:

SET GLOBAL general_log = 'ON';

It gives this error:

Table 'mysql.general_log' doesn't exist

Questions

  1. How to create mysql.general_log again?
  2. How to clear mysql.general_log safely and in a painless way?

Solution

  • Recreate:

    USE mysql;
    
    CREATE TABLE IF NOT EXISTS `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';
    

    Clear table:

    TRUNCATE table mysql.general_log;