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

Question

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

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

+1 for not saying "My coworker executed `DROP TABLE`" – FlipMcF Jul 19 '13 at 01:13 — FlipMcF, Jul 19 '13 at 01:13

score 19 · Accepted Answer · edited Sep 03 '12 at 12:54

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;

score 5 · Answer 2 · answered Sep 03 '12 at 11:39

5

For 1.:

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
)
COMMENT='General log'
COLLATE='utf8_general_ci'
ENGINE=CSV;

For 2.:

TRUNCATE mysql.general_log;

answered Sep 03 '12 at 11:39

m4573r

992
7
17

Thanks for your answer. Unfortunately, only one answer can be accepted altough the answers almost all the same. I accepted first answer. – mmdemirbas Sep 03 '12 at 12:52

score 5 · Answer 3 · answered Jan 28 '15 at 12:39

5

This is the updated version, the 2012 answers do not work anymore:

CREATE TABLE mysql.general_log (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `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` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'

answered Jan 28 '15 at 12:39

glasspill

1,290
4
21
36

1

The difference being ... the size of the `thread_id` column. You should have mentioned that. – user207421 May 07 '15 at 02:26

score 5 · Answer 4 · edited Oct 31 '18 at 05:24

MySQL 5.7.18

I have dropped the mysql.general_log table too, but the other answers did not help me.

Then, I noticed errors about the table structure cause it's a bit different on MySQL 5.7.18, so I have changed some field and now it works.

In case anybody needs it:

CREATE TABLE `general_log` (
  `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_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
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'

score 3 · Answer 5 · answered Feb 12 '16 at 20:52

Ok, having first hand experience, this is what worked for me, if your table gets corrupted by any reason whatsoever, works with MySQL 5.6.11

use mysql;
SET GLOBAL general_log = 'OFF';

DROP TABLE general_log;

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` bigint(21) unsigned NOT NULL,  -- Be careful with this one.
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
);

SET GLOBAL general_log = 'ON';

SET GLOBAL log_output = 'TABLE';

select * from mysql.general_log
order by event_time desc;

score 0 · Answer 6 · answered Nov 14 '20 at 09:04

Recreate table in MySQL 8.0.17:

use mysql;
CREATE TABLE general_log(
event_time timestamp(6) NOT NULL ,
user_host mediumtext NOT NULL,
thread_id bigint(21) unsigned NOT NULL,
server_id int unsigned NOT NULL,
command_type varchar(64) NOT NULL,
argument mediumblob NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';