Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

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

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.