Tell me more ×
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. It's 100% free, no registration required.
up vote 1 down vote favorite

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):

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

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

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

# InnoDB settings

innodb_buffer_pool_size=384M
innodb_log_file_size=32M
innodb_log_buffer_size=2M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_file_per_table

query_cache_limit       = 0M
#query_cache_size        = 16M

expire_logs_days        = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

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

[isamchk]
key_buffer              = 16M

EDIT

What I did was to follow the steps as described at http://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql

MySQL version - 5.1.61-0+squeeze1-log

Engines available - MyISAM, InnoDB, MRG_MYISAM, BLACKHOLE, CSV, MEMORY, FEDERATED, ARCHIVE

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
1  
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
show 2 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.