Take the 2-minute tour ×
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.

After a fresh install of XAMPP and import from my linux live db that is working to the windows Dev-stage, I started encountering problems with INSERT. Everything else seems to work well.

The errors I get in the mysql_error.log are:

2013-08-15 12:44:49 16c0 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-08-15 12:44:49 16c0 InnoDB: Recalculation of persistent statistics requested for table "sizaradb"."pages" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
2013-08-15 12:44:59 16c0 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-08-15 12:44:59 16c0 InnoDB: Recalculation of persistent statistics requested for table "sizaradb"."translations" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.

I cannot open table mysql/innodb_index_stats from the internal data dictionary of InnoDB though the .frm file for the table exists

I tried to fix it by googling the errors and trying to see fixes of other people, but it is still not working. I have been working on this for 2 days now.

Please help me solve it.

share|improve this question
    
This is a problem I quickly learned. +1 for introducing this situation to the DBA StackExchange. –  RolandoMySQLDBA Aug 15 '13 at 15:07
add comment

2 Answers

up vote 5 down vote accepted

PROBLEM

In MySQL 5.6, ibdata1 includes 5 InnoDB in the mysql schema.

mysql> select table_name from information_schema.tables
    -> where table_schema='mysql' and engine='InnoDB';
+----------------------+
| table_name           |
+----------------------+
| innodb_index_stats   |
| innodb_table_stats   |
| slave_master_info    |
| slave_relay_log_info |
| slave_worker_info    |
+----------------------+
5 rows in set (0.00 sec)

mysql>

In MySQL versions before 5.6, if you shutdown mysql, delete ibdata1, and start mysql back up, ibdata1 gets recreated. If you do this with MySQL 5.6, these 5 tables are not recreated. Even if your deleted ibdata1, the following 10 files are still in /var/lib/mysql/mysql:

  • innodb_index_stats.frm
  • innodb_index_stats.ibd
  • innodb_table_stats.frm
  • innodb_table_stats.ibd
  • slave_master_info.frm
  • slave_master_info.ibd
  • slave_relay_log_info.frm
  • slave_relay_log_info.ibd
  • slave_worker_info.frm
  • slave_worker_info.ibd

I learned this early on. With a newly created or damaged ibdata1, there are no corresponding data dictionary entries for those 5 tables.

In your particular case, I suspect you copied ibdata1 from a Linux server to the Windows server. MySQL/Windows would be expecting the data dictionary to have the DOS Filename Format. Moving that ibdata1 to Windows, MySQL/Windows cannot relate to the Linux filename and path for pointing to the .frm and .ibd files.

SOLUTION

STEP 01 : Install MySQL on another DB Server

STEP 02 : mysqldump only those 5 tables

TABLELIST="innodb_index_stats"
TABLELIST="${TABLELIST} innodb_table_stats"
TABLELIST="${TABLELIST} slave_master_info"
TABLELIST="${TABLELIST} slave_relay_log_info"
TABLELIST="${TABLELIST} slave_worker_info"
mysqldump -uroot -p mysql ${TABLELIST} > mysql_innodb_tables.sql

STEP 03 : Copy mysql_innodb_tables.sql to the DB Server with bad tables.

STEP 04 : Execute mysql_innodb_tables.sql

STEP 05 : Run FLUSH TABLES; (Optional)

Give it a Try !!!

share|improve this answer
    
I will give it a try and report back in a few minutes. the DB copy direction was opposit than what you said: linux to windows (says so in the first sentence of the question). –  user1725378 Aug 15 '13 at 15:15
    
My mistake. I updated my answer, reversing the order of data movement. –  RolandoMySQLDBA Aug 15 '13 at 15:23
    
Worked. Thank you :) –  user1725378 Aug 16 '13 at 14:06
add comment

We hit the same problem on deploying 5.6 as part of our standards we always install from .rpm and relocate the data dir after the first set up . 5.5 and lower we would change our settings in the /etc/my.cnf stop mysql and then tar up the database directories in the default data dir and untar to our new one . As we change all our innodb settings we then got a nice new clean innoddb datafile.

So now before we stop our server we run the following on the mysql database

ALTER TABLE innodb_index_stats engine=MYISAM;
ALTER TABLE innodb_table_stats engine=MYISAM;
ALTER TABLE slave_master_info engine=MYISAM;
ALTER TABLE slave_relay_log_info engine=MYISAM;
ALTER TABLE slave_worker_info engine=MYISAM;

We then move all the data to the new location and restart mysql followed by another set of alters thus

ALTER TABLE innodb_index_stats engine=INNODB;
ALTER TABLE innodb_table_stats engine=INNODB;
ALTER TABLE slave_master_info engine=INNODB;
ALTER TABLE slave_relay_log_info engine=INNODB;
ALTER TABLE slave_worker_info engine=INNODB;

This seems to work fine for us and is quick to do

share|improve this answer
    
I also added the create table for the 5 tables back in December (dba.stackexchange.com/questions/54608/…). Nevertheless, this is very good answer provided one remembers four(4) things: 1) the 5 InnoDB tables are inside MySQL 5.6 ibdata1 2), the .ibd exist in the mysql schema, 3) MySQL 5.5 has no upgrade path to MySQL 5.6 to make them, 4) your steps will only work with an ibdata1 generated by MySQL 5.6. +1 !!! –  RolandoMySQLDBA Apr 10 at 15:32
    
BTW, Welcome to the DBA StackExchange !!! –  RolandoMySQLDBA Apr 10 at 15:33
    
Thanks for the welcome - Good points to remember - The solution I'm using is for new clean installs not an upgrade. If upgrading to 5.6 I would probably have a new install and migrate the data to that . –  David Alistair Clough Apr 14 at 9:51
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.