Coupon code: mysql14
Hurry up, offer expires soon!
Blog

BUMMER

USE mysql;

SHOW FULL TABLES WHERE table_type = ‘base table’;
/* returns

innodb_index_stats BASE TABLE
innodb_table_stats BASE TABLE

*/

SELECT * from innodb_table_stats;
/* returns
Error CODE: 1146
TABLE ‘mysql.innodb_table_stats’ doesn’t exist
*/

SHOW CREATE TABLE innodb_table_stats;
/* returns
Error CODE: 1146
TABLE ‘mysql.innodb_table_stats’ doesn’t exist
*/

Well .. the table exists and also does not exist at the same time. There is a free choice from the shelf! But what is happening?  I am not sure yet.  It was reported to this bug report by a Linux user, and it is reproducible for me on Windows. Miguel (the MySQL supporter, who is on it) cannot reproduce  - not yet at least.

First of all this is just one more example of how a .frm file in MySQL may cause havoc if its content is inconsistent with (existense/accessibility, lack of same or content of) data files (of any storage engine).

I am also not in doubt it is related to ‘innodb_file_per_table’ set to “1″ (MySQL 5.6.7 default).  My 5.6.7 server cannot access the .ibd  files in the /datadir/mysql folder (existent there due to this configuration parameter setting). I have no problem with other file types in same folder and also no problems with .ibd files anywhere else on the file system.  It is also not a file system permissions issue.

I am not aware of any such report with same setting of  ‘innodb_file_per_table’ on earlier MySQL versions – so I think (but cannot be sure) that this is introduced in 5.6.7. Could it be a ‘side-effect’ of another configuration parameter? Or just a packaging issue? I don’t know yet.  If anybody has a clue, a comment (here or in the bug report) will be appreciated.

 

14 thoughts on “BUMMER

  1. After a complete wipe of the db and re-install I got it working. It may be something crazy going on in mysql_install_db

  2. @Justin .. do you think that it is OK that SHOW TABLES returns tables that are inaccessible – even if it could be solved by setting ‘persistent stats’ ON?

    I think that the tables that are inaccessible due to a specific configuration setting should be completely hidden (should not cause any errors recorded in error log, and SHOW TABLES should ignore them).

  3. I didn’t say I think it is okay or not a bug, I just pointed out that they are probably accessible if you turn on persistent stats.

  4. I am also facing the same problem.
    InnoDB: Cannot open table mysql/innodb_table_stats from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
    ERROR 1146 (42S02): Table ‘mysql.innodb_table_stats’ doesn’t exist

    As Partha mentioned, is that the only solution? (wiping dbs and rerunning mysql_install_db)

  5. Hello,

    Somehow InnoDB cannot read the .ibd file under DATADIR/mysql/innodb_table_stats.ibd, apart from checking the obvious reasons – whether the file is there (observing case sensitivity!) and has the correct permissions you could try to drop the two tables and recreate them:

    DROP TABLE mysql.innodb_index_stats;
    DROP TABLE mysql.innodb_table_stats;

    the create definitions you will find in scripts/mysql_system_tables.sql in the source directory of mysql. See http://bazaar.launchpad.net/~mysql/mysql-server/5.6/view/head:/scripts/mysql_system_tables.sql#L103 between lines 103 and 127.

    Hope this helps!

  6. Even that is not possible:

    DROP TABLE `mysql`.`innodb_index_stats`;
    – Error Code: 1051
    – Unknown table ‘mysql.innodb_index_stats’

    Should I try to stop MySQL, delete the .frm and ibd files for the two tables and next CREATE them from the script?

  7. It did the trick! Stopped MySQL, deleted files for the two tables and created them from the script on launchpad.

    – after deleting files and restarting mySQL
    SHOW TABLES LIKE ‘%stats’; — empty set

    CREATE TABLE IF NOT EXISTS innodb_table_stats (
    database_name VARCHAR(64) NOT NULL,
    table_name VARCHAR(64) NOT NULL,
    last_update TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    n_rows BIGINT UNSIGNED NOT NULL,
    clustered_index_size BIGINT UNSIGNED NOT NULL,
    sum_of_other_index_sizes BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (database_name, table_name)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
    – success

    CREATE TABLE IF NOT EXISTS innodb_index_stats (
    database_name VARCHAR(64) NOT NULL,
    table_name VARCHAR(64) NOT NULL,
    index_name VARCHAR(64) NOT NULL,
    last_update TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    /* there are at least:
    stat_name=’size’
    stat_name=’n_leaf_pages’
    stat_name=’n_diff_pfx%’ */
    stat_name VARCHAR(64) NOT NULL,
    stat_value BIGINT UNSIGNED NOT NULL,
    sample_size BIGINT UNSIGNED,
    stat_description VARCHAR(1024) NOT NULL,
    PRIMARY KEY (database_name, table_name, index_name, stat_name)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
    – success

    SHOW TABLES LIKE ‘%stats’; — the tables are listed (as before)

    SHOW CREATE TABLE `innodb_index_stats`;
    /* now returns something :-)

    CREATE TABLE `innodb_index_stats` (
    `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `stat_value` bigint(20) unsigned NOT NULL,
    `sample_size` bigint(20) unsigned DEFAULT NULL,
    `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0
    */

    Thanks! But this should of course not be necessary after a fresh install.

  8. But other InnoDB tables in the `mysql` database are still affected of course:

    SHOW CREATE TABLE `slave_master_info`;
    – Error Code: 1146
    – Table ‘mysql.slave_master_info’ doesn’t exist

    .. and mysqldump aborts when such an error is encountered.

  9. Yeah, re-creating the tables is just working around the outcome from the problem, not fixing the problem itself.

    I am still struggling to understand how did you end up in such a situation, unable to reproduce this locally.

    • I have managed to reproduce this locally many times.

      Fresh install (not upgrade) of 5.6.7 on Windows 7 64 Bit, using official packages.

      Its as easy as that….

      Good luck!

      //Steve

  10. @Vasil. Try exactly what I did!

    1) Start with system with no MySQL 5.5 and 5.6 (5.1 and older may be there). not a single file or registry key should be left from any previous 5.5 or 5.6 installation.
    2) Install 5.5.28 (using ‘standalone’ .msi installer) and configure it using the configuration wizard and start teh server.
    3) Install 5.6.7 (also using ‘standalone’ .msi installer). This does not ship with the config wizard. You may configure the instance with the my.ini I posted in the bug report, Now start it.
    4) USE `mysql`; SHOW CREATE TABLE some_innodb_table;

    I don’t know, but it could be related to having a 5.5.28 running while installing 5.6.7.

  11. Same problem here, with 5.6.7 on Windows 7, using the “no install” (mysql-5.6.7-rc-winx64.zip) method. Pretty desperate for a RC version… tried all the tricks to overcome without success. Will keep trying and hope they solve it ASAP. Must go back to 5.5, but would really prefer the UGUID thing for replication.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>