1

this is a follow-up on MySQL "Zombie"-table, cannot delete where I asked abot a rogue table in schema mysql.

Long story short:

  • I accidently wrote a file general.log to the MySQL datadir
  • Removed it later
  • mysqldump starts complaining about missing information about table general_log
  • cant drop the table
  • cant recreate the table and delete it,

Now i found a line in information_schema:

mysql> select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'general_log';
+---------------+--------------+-------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME  | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+-------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------------+
| NULL          | mysql        | general_log | BASE TABLE | CSV    |      10 | Dynamic    |          2 |              0 |           0 |               0 |            0 |         0 |           NULL | NULL        | NULL        | NULL       | utf8_general_ci |     NULL |                | General log   |
+---------------+--------------+-------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------------+
1 row in set (0.00 sec)

I want to delete that line.

Would that be safe?
Is there more information in other tables related to this entry?

2
  • I honestly wouldn't touch anything in the information_schema manually.
    – Nathan C
    Jun 17, 2013 at 12:37
  • Even if i know there is no table tied to this? I can do that in a test environment. Jun 17, 2013 at 12:40

1 Answer 1

1

That is not a zombie table as you call it.

When mysql was installed, the table mysql.general_log was included.

It uses the CSV storage engine.

The two files that represent it are

  • /var/lib/mysql/mysql/general_log.frm
  • /var/lib/mysql/mysql/general_log.CSV

You have the option to enable general logging in that table. Personally, I would never use it as a CSV table. I would convert it to MyISAM and index it. Then, activate it so that I can query the table by datetime and locate SQL commands and users in time ranges.

I have many posts in the DBA StackExchange on how to set it up and use it. Here are a few:

FOR EMPHASIS : That is not a phantom table. It's real. Simply run in the mysql client:

SHOW CREATE TTABLE mysql.general_log\G

or this in the OS

ls -l /var/lib/mysql/mysql/general_log.*

Now, it mysqldump is complaining about that table, chances are you must have delete the /var/lib/mysql/mysql/general_log.CSV file and left the other file /var/lib/mysql/mysql/general_log.frm alone. There are two solutions:

SOLUTION #1 : Create the CSV file again

echo -n > /var/lib/mysql/mysql/general_log.CSV
chown mysql:mysql /var/lib/mysql/mysql/general_log.CSV

SOLUTION #2 : Delete the .frm file

rm -f /var/lib/mysql/mysql/general_log.frm

CAVEAT: Once you delete /var/lib/mysql/mysql/general_log.frm, you cannot make a general log table until you do this:

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';

If you never intend to use the general log as table, then deleting it should not be a problem.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .