1

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

  • Can you check for errors in the error log? – dkasipovic Apr 17 '14 at 8:10
  • @DKasipovic see updated answer – Mario Stoilov Apr 17 '14 at 8:18
  • Have you by any chance copied the mysql data directory from a *nix like system? – dkasipovic Apr 17 '14 at 11:33
  • @DKasipovic no, I haven't – Mario Stoilov Apr 17 '14 at 11:54
  • Problem is obviously that in InnoDB data dictionary your table is stored as applicantsdb/files but your database is named applicantsDB. However, I am not sure how you got to creating different table names in vocabulary and mysql itself. What I can suggest, is to use lowercase database names. – dkasipovic Apr 17 '14 at 11:56
1

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.

|improve this answer|||||
  • Aren't these linux shell commands? I'm pretty sure I tagged windows on this question.... still, the info is helpful – Mario Stoilov Apr 22 '14 at 6:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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