MySQL database stops working after log enabled

MySQLwindows

I have this strange issue. I have MySql workbench, which I use to write queries for MySQL database (xampp 3.2.1). Everything works fine, but recently I found myself having to profile the queries my app sends, so in the config file (my.ini) I added this this line:

general_log             = 1

After adding this line, my server starts, I can connect to it via workbench, but when I try to get the data from my database

SELECT * FROM applicantsDB.Applicants;

I get

Error Code: 1146. Table 'applicantsDB.Applicants' doesn't exist

While the same query succeeds if the logging is off. I can still get data from my other databases. What might be causing this?

EDIT now it doesn't even work when logging is off (other dbs are fine). Tried creating a new db and importing the schema from applicantsDB. Worked fine until I decided to restart the server. If I do the same without logging on, everything is fine (across restarts too) but when I enable logging, everything breaks.

EDIT V2
sample error log:

2014-04-17 10:20:26 1878  InnoDB: Error: table 'applicantsdb/applicants'
InnoDB: in InnoDB data dictionary has tablespace id 50,
InnoDB: but the tablespace with that id has name applicantsDB/applicants.
InnoDB: Have you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2014-04-17 10:20:26 1878  InnoDB: Error: table 'applicantsdb/files'
InnoDB: in InnoDB data dictionary has tablespace id 52,
InnoDB: but the tablespace with that id has name applicantsDB/files.
InnoDB: Have you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html

EDIT V3
when I create the database with the name in lower case everything works fine. Still, this does not explain why enabling logging can break a database

Best Answer

You certainly have a problem but that does not look related to enabling general logging. Are you sure it's not that those errors have been filling your error log previously and you only just noticed them after an investigation stemming from enabling general_logging?

Enabling general_logging can and cause your server seemingly hang. General logging will log every single query thrown over the fence to mysql (even one resulting in syntax errors).

This can cause the file size to grow a lot more rapidly than expected. Run

show variables like 'general_log_file';

Then check the disk usage for that path from the shell command line

df -h /var/log/mysql/mysql.log

If that reports 0 available and 100% use then it is very much expected mysql would hang.

It's the defined behavior of mysql when any log (general, binary, error) cannot be further written to because space has been exhausted. The idea is it will just hang out until space is made available.

To remedy you want to either more the log to a filesystem with a filesystem with enough space, or just delete it if you don't care. Then either restart w/ the general_log variable pointing to a file system with enough space for your traffic loads or add more space to the existing file system.

Related Question