Discussion :

[ulspider]

Bonjour,

J'ai eu un soucis avec une ancienne base de données sous Wamp.

J'ai décidé de refaire une installation propre avec la dernière version de wamp 32bits.

J'ai recrée la BD de 0, puis j'ai recréer les structures des tables INNODB. Je tente maintenant de récupérer les données.

J'ai appliqué la procédure suivante :

Step 2: Recreate the table in a new database

In a new database, create the new table with the script generated at the step 1. This script will create 2 files in the database data folder :

mytable.frm
mytable.idb

Step 3: Remove the new idb file

To remove the new idb file, execute the sql command :

ALTER TABLE mytable DISCARD TABLESPACE;

This command removes the link between the table and the tablespace, and removes the idb file.

Step 4: Copy the old idb file

The idb file recovered from the old server must be copied in place of the idb file deleted at the step 3.

Step 5: Reactivate the table

The link broken at the step 3 is restored with the command :

ALTER TABLE mytable IMPORT TABLESPACE;

No worry about the warnings you will receive.

That’s it !

En reprenant les anciens fichiers idb et en appliquant la procédure ci-dessus, j'ai pu récupérer les données de la plupart des tables.

Néanmoins sur une table, dont le fichier .idb fait 4.2 Go (les autres fichiers idb ne dépassent pas 150mo), PhpMyAdmin plante au bout de
30 secondes précisément avec la requete d'import du tablespace.

Requête SQL :

ALTER TABLE ANNONCE_LEBONCOIN_VERSION IMPORT TABLESPACE

MySQL a répondu : Documentation
#2006 - MySQL server has gone away

Et le contenu de la log MySQL :

2018-04-07T15:12:21.991588Z 62 [Warning] InnoDB: Cannot open table proxilum/annonce_leboncoin_version from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/...eshooting.html for how to resolve the issue.
2018-04-07T15:12:24.931592Z 66 [ERROR] InnoDB: Table `proxilum`.`annonce_leboncoin_version` does not exist in the InnoDB internal data dictionary though MySQL is trying to drop it. Have you copied the .frm file of the table to the MySQL database directory from another database? Please refer to http://dev.mysql.com/doc/refman/5.7/...eshooting.html for how to resolve the issue.
2018-04-07 17:15:27 0x1cb4 InnoDB: Assertion failure in thread 7348 in file os0file.cc line 1697
InnoDB: Failing assertion: offset > 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/...-recovery.html
InnoDB: about forcing recovery.
15:15:27 UTC - mysqld got exception 0x80000003 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=268435456
read_buffer_size=2097152
max_used_connections=2
max_threads=151
thread_count=2
connection_count=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 881993 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x21a54748
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
19629f0 mysqld.exe!my_sigabrt_handler()[my_thr_init.c:448]
6b0d4aae MSVCR120.dll!raise()
6b0d764e MSVCR120.dll!abort()
19f1f93 mysqld.exe!os_file_io_complete()[os0file.cc:1697]
19f1f3e mysqld.exe!os_file_io()[os0file.cc:5453]
19f23bd mysqld.exe!os_file_pread()[os0file.cc:5612]
19f269a mysqld.exe!os_file_read_page()[os0file.cc:5651]
19f25fe mysqld.exe!os_file_read_func()[os0file.cc:6044]
1a7586b mysqld.exe!fil_iterate()[fil0fil.cc:6375]
1a7bd64 mysqld.exe!fil_tablespace_iterate()[fil0fil.cc:6597]
1af6ae4 mysqld.exe!row_import_for_mysql()[row0import.cc:3669]
19a50d3 mysqld.exe!ha_innobase::discard_or_import_tablespace()[ha_innodb.cc:12328]
1373658 mysqld.exe!handler::ha_discard_or_import_tablespace()[handler.cc:4793]
14002d9 mysqld.exe!mysql_discard_or_import_tablespace()[sql_table.cc:6053]
1507495 mysqld.exe!Sql_cmd_discard_import_tablespace::execute()[sql_alter.cc:369]
13930e8 mysqld.exe!mysql_execute_command()[sql_parse.cc:3566]
139592f mysqld.exe!mysql_parse()[sql_parse.cc:5582]
138f011 mysqld.exe!dispatch_command()[sql_parse.cc:1458]
138fd04 mysqld.exe!do_command()[sql_parse.cc:1001]
1343406 mysqld.exe!handle_connection()[connection_handler_per_thread.cc:300]
1bef391 mysqld.exe!pfs_spawn_thread()[pfs.cc:2190]
19628a7 mysqld.exe!win_thread_start()[my_thread.c:36]
6b05c01d MSVCR120.dll!__get_tlsindex()
6b05c001 MSVCR120.dll!__get_tlsindex()
753f343d kernel32.dll!BaseThreadInitThunk()
76fa9832 ntdll.dll!RtlInitializeExceptionChain()
76fa9805 ntdll.dll!RtlInitializeExceptionChain()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (21a1c998): ALTER TABLE ANNONCE_LEBONCOIN_VERSION IMPORT TABLESPACE
Connection ID (thread ID): 104
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

J'ai tenté de modifier le fichier my.ini avec les variables liées aux buffers, aux timeout... mais rien y fait. Quelqu'un a une idée ? Un timeout caché ?

Edit :
En modifiant les paramètres de MySql dans le fichier my.init, l'erreur survient maintenant après une minute. C'est mieux mais ça plante toujours... Franchement je tatonne en augmentant les valeurs...

[rawsrc]

Salut,

rajoute dans my.ini :

innodb_force_recovery = 2

[ulspider]

Salut,

1m40 avant plantage xD

Qui dit mieux ?

[rawsrc]

J'ai pas de soluce toute cuite à te filer, t'as vu on a gagné 40 secondes, génial non ?
Cela devrait prendre plus de temps mais refait un dump intégral de ta base et réimporte dans le nouvel tablespace.

[ulspider]

Lol oui, pas encore ça ^^

bah l'ancienne base du jour au lendemain la plupart des tables étaient non chargeables, le dump complet est donc impossible.

Je vais faire quelques tests mais ça fait ch...

[rawsrc]

ça sent pas bon du tout.
T'as fait une analyse disque approfondie ? Qu'est ce te dit le rapport S.M.A.R.T. ?