15

I've installed Drupal on my local XAMPP Server. It worked all fine, no problems with including and working with the database/site till i restarted XAMPP. Since then I get the following at my logfile:

2013-09-02 16:18:46 2544 [Note] Plugin 'FEDERATED' is disabled.

2013-09-02 16:18:46 3e8 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.

2013-09-02 16:18:46 2544 [Note] InnoDB: The InnoDB memory heap is disabled

2013-09-02 16:18:46 2544 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions

2013-09-02 16:18:46 2544 [Note] InnoDB: Compressed tables use zlib 1.2.3

2013-09-02 16:18:46 2544 [Note] InnoDB: Not using CPU crc32 instructions

2013-09-02 16:18:46 2544 [Note] InnoDB: Initializing buffer pool, size = 16.0M

2013-09-02 16:18:46 2544 [Note] InnoDB: Completed initialization of buffer pool

2013-09-02 16:18:46 2544 [Note] InnoDB: Highest supported file format is Barracuda.

2013-09-02 16:18:47 2544 [Note] InnoDB: The log sequence numbers 1600614 and 1600614 in ibdata files do not match the log sequence number 1600644 in the ib_logfiles!

2013-09-02 16:18:47 2544 [Note] InnoDB: Database was not shutdown normally!

2013-09-02 16:18:47 2544 [Note] InnoDB: Starting crash recovery.

2013-09-02 16:18:47 2544 [Note] InnoDB: Reading tablespace information from the .ibd files...

2013-09-02 16:18:47 2544 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace drupal/variable uses space ID: 2 at filepath: .\drupal\variable.ibd. Cannot open tablespace mysql/innodb_index_stats which uses space ID: 2 at filepath: .\mysql\innodb_index_stats.ibd

InnoDB: Error: could not open single-table tablespace file .\mysql\innodb_index_stats.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.

I looked for a solution via google but it seems to be a problem just with the drupal database because it's able to connect with MySQL if I remove the database.

I hope someone could help me :(.

Community
  • 1
  • 1
flumingo
  • 513
  • 2
  • 7
  • 24
  • Ok, i think i got it. It was a problem with the user privilegs for the database. The user had the rights but after shutting down MySQL, the changes my user did, were all cancelled. Now my user has limited privileges only at this database and - behold - it works :). – flumingo Sep 03 '13 at 11:19

4 Answers4

34

Move (DON'T DELETE) those files, into another folder:

innodb_index_stats.frm
innodb_table_stats.frm
slave_master_info.frm
slave_relay_log_info.frm
slave_worker_info.frm

and .ibd files with the same filename:

innodb_index_stats.ibd
innodb_table_stats.ibd
slave_master_info.ibd
slave_relay_log_info.ibd
slave_worker_info.ibd

Try start MySQL.

PiTheNumber
  • 22,828
  • 17
  • 107
  • 180
Panos Angelopoulos
  • 525
  • 1
  • 6
  • 14
  • I tried this before but I got the same problem again and again. Just changing the privileges of my database user worked fine :) For some reason all changes my user did were overwritten with 0 after shutting the system down , but thanks for your reply! – flumingo Oct 30 '13 at 16:18
  • Hi , i have the same problem .. how can i change the privileges of my database ? – Abdou Tahiri Dec 18 '13 at 11:10
  • Take a look here (http://serverfault.com/questions/115950/how-do-i-change-the-privileges-for-mysql-user-that-is-already-created) and here (http://stackoverflow.com/questions/5016505/mysql-grant-all-privileges-on-database). – Panos Angelopoulos Dec 28 '13 at 17:57
  • Hey Abdou Tahiri, that's contingent to the database you're using. AFAIK you're not able to change the privileges via drupal. In my case, i just had to go to phpMyAdmin, choose the database and change the rights (I'm using XAMPP!). Maybe you have to change the rights via sql querys... but you have to be logged in as database admin. Just look for the possibilities of your database. – flumingo Jan 16 '14 at 10:26
  • Removing those tables fixes issues with conflicting table ids, but you may need to re-create them to get replication working - see Kay Nelson's answer at http://stackoverflow.com/questions/15767652/mysql-error-table-mysql-innodb-table-stats-not-found which has a script to re-create those tables once you've deleted them. – Jason Jan 21 '14 at 01:03
21

You can solve this problem by adding a line in your mysql config file: my.cnf or my.ini (depends on your distro)

just under [mysqld] add this line: innodb_force_recovery = 1 

..
[mysqld]
innodb_force_recovery = 1 
..

Then restart your MySql Server. You could have lost some data but you'll get the server working again with your data.

Regards,

Charles P.
  • 1,947
  • 16
  • 13
  • This fixed it for me although it warned me that it could cause issues. So make sure you create a backup before doing this. I'm guessing it could force itself to recover breaking the db in the process – Crecket Jul 31 '15 at 19:17
  • Charles P. After doing this my tables have only read-only permission. Unable to insert/update any idea? – dev_khan Apr 25 '16 at 20:07
  • Hi, dev_khan. It didn't happen to me, but try to add to the configuration file this line: skip-grant-tables Then restart mysql and try again. – Charles P. Apr 30 '16 at 14:16
18

dev_khan, try restarting MySQL in Read-Only mode with the innodb_force_recovery option enabled:

  1. Edit my.cnf - find the line: # innodb_force_recovery = 2
  2. Comment the line in (remove the #)
  3. Restart MySQL to let the MySQL engine repair itself.
  4. Comment the innodb_force_recovery line in again (add #)
  5. Restart MySQL again and you have full access again without a Read-Only-Restriction.

Greetings from Germany

Nik
  • 2,902
  • 2
  • 16
  • 11
Janis Benda
  • 196
  • 1
  • 2
0

This happens with Wordpress too. It only seems to happen with the latest version as I've rolled back to previous versions of AMPPS and it works fine without throwing up this innodb issue.

mandrill
  • 91
  • 1
  • 8