Bug Reportmysql_error.log wrote:{YYYY-mm-dd HH:ii:ss} {####} [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace {database name}/{database table name} uses space ID: {#} at filepath: .\{database name}\{database table name}.ibd. Cannot open {database name}/{database table name} which uses space ID: {#} at filepath: .\{database name}\{database table name}.ibd
InnoDB: Error: could not open single-table tablespace file .\{database name}\{database table name}.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
Reproducible TestcaseMaybe not reproducible, but finally got this issue myself:
- fresh XAMPP 1.8.3-1 .zip installation installed under C:\xampp183
- setup_xampp.bat executed to correct paths
- Apache and MySQL started as application from control panel v3.2.1, not as service
- created a new database with phpmyadmin and imported a small valid and successful .sql Database export (contains a mix of InnoDB and MyISAM database tables from an non XAMPP
MySQL 5.1 Server) - added a new user with phpmyadmin and gave him USAGE permissions on that new database
- added a few new entries with phpmyadmin in one of the InnoDB database tables
- could at this time still use a webapplication with this database
==> issue starting here and i don't know what was happening <== - stopped Apache and MySQL in XAMPP control panel
- XAMPP control panel logs has listed, that i stopped MySQL and Apache
- mysql_error.log does not have any shutdown messages in it (which is because the control panel is simply killing the process)
- two days later trying to start MySQL again: it crashes with the known log messages
Assumption: XAMPP control panel uses, as XAMPP components do since years, stop an application by killing the process instead of shutting it down cleanly (clean shutdown needs a valid MySQL user authentification with SHUTDOWN permissions, which might be the reason XAMPP uses the method of killing the processes).
MySQL 5.6.X doesn't seem to like this behavior much.
Affected XAMPP versionsAll XAMPP versions containing MySQL 5.6.X
Solutions / Workarounds- using XAMPP 1.8.2-1 (MySQL 5.5.32)
or - add the setting "innodb_force_recovery = 4" (value 4 is recommend, if this will not work try value 6) in your my.ini mysqld block
If MySQL starts successful and the data could be restored, immediately do a database dump.
http://dev.mysql.com/doc/refman/5.6/en/ ... overy.html
or - deleting the specific .ibd file (data loss of this specific InnoDB table !!!) and reimport a backup of this specific table
or - deleting the \xampp\mysql\data\ibdata1 file (data loss of all InnoDB Databases !!!)
best solution would be to change the stopping methods to a more clean approach in future XAMPP versions. Maybe the control panel should open a XAMPP shell window executing the command
- Code: Select all
mysqladmin shutdown --user=root --password
which forces the user to enter the root password for the reason someone has changed it from empty string to something else)
References