Recover MySQL tables when only the .frm files and. ibd are available

How to solve “Mysql Error #1146 -Table doesn't exist” caused by moving .IDB files (InnoDB) directly between directories (tablespace).

logo_mysql

Problem

Many of the problems we have are problems we created. I had just set up a Site using WordPress and thought: How about seeing how this works in PHP 7? OK, as my WampServer used PHP 5.6, so I had to “happy” idea to upgrade the WampServer 2.5 (Apache-2.4.9, MySQL-5.6.17, PHP 5.5.12) for WampServer 3.0.6 (Apache 2.4.23, MySQL 5.7.14, 5.6.25 PHP and Php 7.0.10).

Of course, started by backup, but my great slip was choose not to export SQL from such Site, Instead, just made a copy of the MySQL files (MySQL/data) and the application (www/site). I was confident it would work, like the other times.

After installing the new WampServer with PHP 7, put the backup files and the application back, only when I went to test by the browser, didn't show up the Site, but the screen of WordPress installation as if it were a new Site. Froze up! Open PHPMyAdmin and check to see if the database was Ok and I saw that, Despite being listed in the database, When you clicked on the top of the table to view the data the following error message:

  MySQL Error #1146 -Table <TABLE_NAME> doesn ' t exist

I have been searching and I just found out that you can only copy or move the files directly to another MySQL MySQL directory if files are MyISAM type, those with frm file extension. MYD. MYI. I don't know what happened, but my database appeared as InnoDB (.frm and. idb). Unfortunately, only after the fact that MySQL alerted documentation:

  You cannot freely move .IBD files between database directories as you can with MyISAM table files. 
  The table definition stored in the InnoDB shared tablespace includes the database name. 
  The transaction IDs and log sequence numbers stored in the tablespace files also differ between databases.

Solution

Cheguei a ver algumas soluções simples, mas vou descrever aqui a única que realmente funcionou para mim, apesar de parecer bem mais complexa, encontrada no Post “Restoring table(with) in MySQL database when frm or ibd files are available only“.

Antes, porém, a título de conhecimento, vale lembrar que em relação ao tipo InnoDB, MySQL your guard structure in files and data in .frm. ibd. Enough theory, lets get down to!

Step 1: Re-create the structure of the .frm files

As said, the frm contains your table structure. First we need to retrieve this structure to then populate the data. You can do this in 3 different methods described below, but before, Let me tell you something about the “Mysql Utilities”.

Mysql Utilities | mysqlfrm

If you do not intend to use the first method proposed (Online) so you need to use the tool “mysqlfrm” provided by the “Mysql Utilities“.

This tool will extract the data structure and generate the table creation SQL script. For this, Download “Mysql Utilities” (something like mysql-utilities-1.6.4-winx64.msi) and install it.

Open the console (Command / Of the) and use the CD command to move the default installation directory (something like C:\C:program FilesMySQLMySQL Utilities 1.6\). Note that within this directory there is the executable mysqlfrm.exe.

  CD "C:\C:Program FilesMySQLMySQL Utilities 1.6"

Choose only one of the methods below to extract your table structure contained in the .frm files:

1º Method | Online

1. Go to the website HTTPS://recovery.twindb.com/;
2. Click on the menu “Recover Structure” and then in “frm file from.“;
3. Click on the button “Browser…” and select your frm file; and
4. Click on the button “Upload” and the table creation script will be displayed on the screen.

It is possible to send multiple files frm once generating a single script with all tables. Just to let you create a .zip with all its .frm before “Browser/Upload”.

2º Method | Using –diagnostic

/* Generic example */
   mysqlfrm-diagnostic "<source/path>/mytable.frm" > "<destination/path/recovered_mytable.sql>"
 
/* Example in practice with the wp_posts.frm table of the WordPress */
   mysqlfrm --diagnostic "C:\wampbinmysqlmysql5.6.17datameubancowp_posts.frm" > "C:\recovered_wp_posts.sql"

3º Method | Using –Server

/* Generic example */
  mysqlfrm-server=root:mypassword@localhost-port=3311 "<source/path>/mytable.frm" > "<destination/path>/recovered_mytable.sql"
 
/* Example in practice with the wp_posts.frm table of the WordPress */
  mysqlfrm --Server=root@localhost --port=3307 ""(C):\wampbinMySQL\MySQL5.6.17\datameubancowp_posts.frm" > "(C):\recovered_wp_posts.SQL"

Do not use the MySQL port! Choose any other available. Note that the command redirects the output to a .SQL file that is saved in the specified path (where, (C):).

Step 2: Re-create the table in your database

In a new database, create a table with the generated script in step 1. I personally installed a clean version of WampServer and used PHPMyAdmin (http://localhost/phpmyadmin/) to create the database and import the SQL script created in step 1 (recovered_wp_posts.sql). This script will create 2 files in the directory of your MySQL database:

  SUA_TABELA.frm SUA_TABELA.IDB

In my case, These files were created in (C):\wamp64binmysqlmysql5.7.14data<my seat>. IE, look for your MySQL installation and note that in the directory date There will be your database and the two files mentioned.

Step 3: Remove the new .IDB file

To remove the new .IDB file, run the following SQL command:

/* Generic example */
  ALTER TABLE mytable DISCARD TABLESPACE;
 
/* Exemplo na prática com a tabela wp_posts do WordPress */
  ALTER TABLE wp_posts DISCARD TABLESPACE;

This command removes the link between the table and the tablespace (Where are physically stored the database files), and remove the .IDB file.

Step 4: Copy the old .IDB file

The original .IDB file (the that contains the data) needs to be copied to the place that got deleted in the .IDB Step 3. Use Ctrl + C and Ctrl + V, or kill yourself by using the command line.

Step 5: Reactivate the table

The broken link in step 3 needs to be restored with the following command:

/* Generic example */
  ALTER TABLE mytable IMPORT TABLESPACE;
 
/* Exemplo na prática com a tabela wp_posts do WordPress */
  ALTER TABLE wp_posts IMPORT TABLESPACE;

Don't worry if you receive some warnings, porém, It might be general malfunction appearing the following error:

  #1808 - Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .IBD file has ROW_TYPE_COMPACT row format.)

In this case, This happened due to differently than mysql 5.7.14 (NEW) creates a default table compared to Mysql-5.6.17 (OLD), without the ROW_FORMAT = compact.

So, If you are using MySQL > = mysql 5.7.14, all you need to do is add ROW_FORMAT = compact in the definition of table creation.

  CREATE TABLE ' wp_posts ' (. . .) ENGINE=Innodb ROW_FORMAT=Compact

Other errors

Just for the record, before obtaining success in extracting data from. frm, I had some errors:

  # Source on localhost: ... connected.
  # Starting the spawned server on port 3304 ... ERROR Attempting to stop failed spawned server.  Process id = 6648.
  ERROR: Spawn server operation failed. Clone server error: Unable to communicate with new instance. Process id = 6648.. To diagnose, run the utility again and use the --verbosity option to view the messages from the spawned server and correct any errors presented then run the utility again.
  SUCCESS: The process with PID 6648 (child process of PID 7264) has been terminated.
Traceback (most recent call last):
  File "G:\adebuildsb_0-19921351-1470074463.97Python-2.7.6-windows-x86-64bitlibsite-packagescx_FreezeinitscriptsConsole.py", line 27, in <module>
  File "scripts\mysqlfrm.py", line 422, in <module>
  File ".\mysql\utilities\command\read_frm.py", line 439, in read_frm_files
  File ".\mysql\utilities\command\read_frm.py", line 166, in _spawn_server
  File ".\mysql\utilities\command\serverclone.py", line 180, in clone_server
  File ".\mysql\utilities\common\tools.py", line 273, in get_mysqld_version IOError: [Errno 13] Permission denied: 'version_check'

In the specific case of WordPress, you need to remove the script that generated the SQL generated under. frm, all entries ' DEFAULT ‘0000-00-00 00:00:00’, otherwise, will catch the error:

  #1067 - Default value (default) not valid for ' comment_date '

That's it!

Fonts

http://www.voxteneo.com/restoring-tables-mysql-database-frm-ibd-files-available/

https://medium.com/@alexquick/transporting-mysql-tablespaces-from-5-6-to-5-7-517c01345fbb#.72zermd8b

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

https://lanmenezesbr.wordpress.com/2014/10/21/mysql-erro-1146-table-doesnt-exist/

http://stackoverflow.com/questions/7759170/mysql-table-doesnt-exist-but-it-does-or-it-should

Total hits: 14913

10 comments on “Recover MySQL tables when only the .frm files and. ibd are available

  1. Rodrigo said:

    What to do when you see the following error message: #1815 – Internal error. Cannot reset LSNs in table “my seat”.”myTable”: Data structure corruption.
    Can I run the inserts of data manually? Como fazer isso?
    Desde já te agradeço.

  2. Cristian U said:

    Hi Taylor,

    How did you solve this error:
    ERROR: Spawn server operation failed. Clone server error: Unable to communicate with new instance. Process id = 6648.. To diagnose, run the utility again and use the –verbosity option to view the messages from the spawned server and correct any errors presented then run the utility again.
    SUCCESS: The process with PID 6648 (child process of PID 7264) has been terminated.

    I keep receiving the same message and I don´t know how to solve it.

    Thanks

Leave a reply

The your email address will not be published. Required fields are marked with *