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.

I have two MySQL servers, running master/slave replication.
At one point i had to write huge general.log on the slave.

I had the glorious idea to write it to the datadir of MySQL. Mock me.
Later I removed that file.
Now MySQL insists on having a table mysql.general_log which never was a table but a logfile. I found out because my cron-mysqldump sends complaints:

#> mysqldump mysql > /home/mysqldump/mysqldump.sql
Error: Couldn't read status information for table general_log ()
mysqldump: Couldn't execute 'show create table `general_log`': Table 'mysql.general_log' doesn't exist (1146)

So I tried this by hand:

mysql> show create table mysql.general_log;
ERROR 1146 (42S02): Table 'mysql.general_log' doesn't exist

and tried to drop the table:

mysql> drop table general_log;
ERROR 1051 (42S02): Unknown table 'general_log'

Also tried:

#> mysqlcheck --repair mysql

to no avail.

following this link i found something about ib* files. So I did

#> service mysqld stop
#> mv mysql/data/ib* mysql.off/data/
#> service mysqld start
#> 

Now mysqldump complained about another missing table. It is just a .frm file without accompaning MYD and MYI. Reverted changes.

result of test alex_sf postetd:

mysql> SET GLOBAL general_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
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';
Query OK, 0 rows affected (0.02 sec)

mysql> DROP TABLE general_log;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@server ~]# mysqldump mysql > /home/mysqldump/mysqldump.sql
Error: Couldn't read status information for table general_log ()
mysqldump: Couldn't execute 'show create table `general_log`': Table 'mysql.general_log' doesn't exist (1146)

I tried to re-initialize the DB and used a dump from the other MySQL instance, which does not show this error:

#> mysql_install_db
#> service mysqld start
#> /usr/bin/mysql_secure_installation
#> mysqldump mysql > /home/mysqldump/mysqldump.sql

Result: some minor error, cant remember.
now i use the "clean" mysqldump again:

#> mysql < /home/mysqldump/mysqldump.master.sql
#> mysqldump mysql > /home/mysqldump/mysqldump.sql
Error: Couldn't read status information for table general_log ()
mysqldump: Couldn't execute 'show create table `general_log`': Table 'mysql.general_log' doesn't exist (1146)

Et voila, the bug is in the mysqldump. grep grep grep ....

Result of alex_sfs second test:

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
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';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@ns235265 mysql]# mysqldump mysql > /home/mysqldump/mysqldump.sql
Error: Couldn't read status information for table slow_log ()
mysqldump: Couldn't execute 'show create table `slow_log`': Table 'mysql.slow_log' doesn't exist (1146)

after that i realized i hadn' dropped the table:

mysql> DROP TABLE general_log;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@server ~]# mysqldump mysql > /home/mysqldump/mysqldump.sql
Error: Couldn't read status information for table general_log ()
mysqldump: Couldn't execute 'show create table `general_log`': Table 'mysql.general_log' doesn't exist (1146)

Im starting to think that DB is done for...

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

There's a bug reported here with mysqldump that could be causing this. Create the table definition, and then try your dump again:

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';
share|improve this answer
    
Can you run tree in your datadir and stick it in a pastebin? –  alex_sf Jun 16 '13 at 17:46
    
What's the output of show create table mysql.general_log;? The frm files without MYD and MYI are normal. FRM files are table definitions for every storage engine; MYD and MYI are specific to MyISAM. The tables with just FRM's and no MYD/MYI are probably Innodb tables. –  alex_sf Jun 16 '13 at 18:50
    
I've updated my answer, give it a shot. –  alex_sf Jun 16 '13 at 18:53
    
I found a bug report for mysqldump that may be causing this. Updated my answer again. –  alex_sf Jun 16 '13 at 19:52
    
Updated my question with result of your second test –  chief_wrench Jun 16 '13 at 20:18
add comment

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.