Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I recently made changes to optimize performance for InnoDB engine-based DB and soon after when restarting I encounter into the following strange behavior:

  1. I have some tables with data in them
  2. I made a change to my.cnf - increased innodb_buffer_pool_size
  3. I restart MySQL server
  4. I do 'use database' and perform 'show tables' > all tables are shown
  5. I do 'select * from some_table_shown_in_the_list' and I get 'Table does not exist'

Seriously... what the hell? I've no idea what's going on...

My my.cnf settings (I am using only InnoDB engine for my tables):

port            = 3306
socket          = /var/run/mysqld/mysqld.sock

socket          = /var/run/mysqld/mysqld.sock
nice            = 0

user            = mysql
pid-file        = /var/run/mysqld/
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english

key_buffer              = 1M
max_allowed_packet      = 512K
thread_stack            = 128K
thread_cache_size       = 50

# InnoDB settings


query_cache_limit       = 0M
#query_cache_size        = 16M

expire_logs_days        = 10
max_binlog_size         = 100M

max_allowed_packet      = 16M

#no-auto-rehash # faster start of mysql but no tab completition

key_buffer              = 16M


What I did was to follow the steps as described at

MySQL version - 5.1.61-0+squeeze1-log


share|improve this question
It's possible that your real datadir exists on a partition that failed to mount. It's also possible that your /etc/my.cnf datadir changed. When you do an ls -lah of your datadir, what do you see? I noticed you have innodb_file_per_table set. You should see a folder for each database/schema and under that the data files. – randy melder May 25 '12 at 21:25
yes, this is exactly what I see. For every table now I have frm and ibd files. I made this to disable growing huge ibdata1 file. All I did was to restart MySQL and what I got was 'tables not found' – Martin Asenov May 25 '12 at 21:26
Please run SHOW ENGINES; right now and post the output. – RolandoMySQLDBA May 25 '12 at 22:46
Please tell the following: 1. What version of MySQL you are running (SELECT VERSION();)? 2. How much RAM do you have on the DB Server? 3. Did you copy a previous version of ibdata1 over the current one ? 4.Any other changes have you made that you did not mention? – RolandoMySQLDBA May 25 '12 at 22:51
The answer is likely to be in the file mysqld.err . Often it is caused by a change to the size of the innodb log files. – Rick James May 29 '12 at 21:23

Your Answer


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

Browse other questions tagged or ask your own question.