MySQL – [ERROR] InnoDB: ibdata1 can’t be opened in read-write mode

En ocasiones recibiremos errores en nuestro log del servidor MySql de Xampp.

En este caso os traemos la respuesta para el caso: [ERROR] InnoDB: C:\xampp\mysql\data\ibdata1 can’t be opened in read-write mode.

 
En principio observamos un error en el servicio de MySQL:

Error MySQL Xampp

 
Y tal y como reza el mensaje en color rojo, nos dedicamos a investigar en el archivo de log de MySQL.

ibdata1 can't be opened in read-write mode

 

 
En un caso como este nos podemos encontrar con un log parecido al siguiente:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

22:21:30 25172 [Note] Plugin 'FEDERATED' is disabled. 
22:21:30 3bbc 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.
22:21:30 25172 [Note] InnoDB: Using atomics to ref count buffer pool pages
22:21:30 25172 [Note] InnoDB: The InnoDB memory heap is disabled
22:21:30 25172 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
22:21:30 25172 [Note] InnoDB: Memory barrier is not used
22:21:30 25172 [Note] InnoDB: Compressed tables use zlib 1.2.3
22:21:30 25172 [Note] InnoDB: Not using CPU crc32 instructions
22:21:30 25172 [Note] InnoDB: Initializing buffer pool, size = 16.0M
22:21:30 25172 [Note] InnoDB: Completed initialization of buffer pool
22:21:30 25172 [ERROR] InnoDB: C:\xampp\mysql\data\ibdata1 can't be opened in read-write mode
22:21:30 25172 [ERROR] InnoDB: The system tablespace must be writable!
22:21:30 25172 [ERROR] Plugin 'InnoDB' init function returned error.22:21:30 25172 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
22:21:30 25172 [ERROR] Unknown/unsupported storage engine: InnoDB
22:21:30 25172 [ERROR] Aborting
22:21:30 25172 [Note] Binlog end
22:21:30 25172 [Note] Shutting down plugin 'partition'
22:21:30 25172 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_FT_DELETED'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_METRICS'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_CMPMEM'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_CMP_RESET'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_CMP'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_LOCKS'
22:21:30 25172 [Note] Shutting down plugin 'INNODB_TRX'
22:21:30 25172 [Note] Shutting down plugin 'BLACKHOLE'
22:21:30 25172 [Note] Shutting down plugin 'ARCHIVE'
22:21:30 25172 [Note] Shutting down plugin 'MRG_MYISAM'
22:21:30 25172 [Note] Shutting down plugin 'MyISAM'
22:21:30 25172 [Note] Shutting down plugin 'MEMORY'
22:21:30 25172 [Note] Shutting down plugin 'CSV'
22:21:30 25172 [Note] Shutting down plugin 'sha256_password'
22:21:30 25172 [Note] Shutting down plugin 'mysql_old_password'
22:21:30 25172 [Note] Shutting down plugin 'mysql_native_password'
22:21:30 25172 [Note] Shutting down plugin 'binlog'
22:21:30 25172 [Note] C:\xampp\mysql\bin\mysqld.exe: Shutdown complete

Lo que hemos de hacer es buscar la primera linea de error, tras la cual se desencadenan los demás eventos de error.

Ese primer error, presumiblemente será el problema que debemos solucionar (puede ser que a posteriori encontremos más errores, pero ese ya es otro cantar…).
 

 
En este caso la linea desencadenante de la malfunción de MySQL es :

1

[ERROR] InnoDB: C:\xampp\mysql\data\ibdata1 can't be opened in read-write mode

 

 

La solución de [ERROR] InnoDB: ibdata1 can’t be opened in read-write mode es:

 

    • Parar los servicios Mysql y Apache.
    • Apagar Xampp.
    • Acceder al directorio Xampp/mysql/data y borrar el archivo ibdata y los archivos de ib_logfile.

ibdata - archivos MySQL Xampp

  • Abrir el archivo mysql_error.log, vaciarlo, y guardarlo.
  • Reiniciar Xampp
  • Reiniciar los servicios MySQL y Apache.


 
Hecho esto, ya podremos acceder a PhpMyadmin sin problema.
Si aún así no podemos acceder, debemos seguir el rastro de los errores del log de mysql y apache, hasta acabar de solucionar todos los errores.

 

Artículos Relacionados:

  1. Error: MySQL shutdown unexpectedly
  2. Cómo crear un Servidor Web en casa
  3. Table does not support optimize, doing recreate + analyze instead
  4. Cómo saber la versión de MySQL instalada en Linux