I have two VPS's running right now. One, which I will refer to as the "old" server, runs on Debian 7 and gives this output for mysql --version:

mysql Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.2

The other, which I will refer to as the "new" server, is running on a brand new installation of Debian 8, and gives this output:

mysql Ver 15.1 Distrib 10.0.20-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

I am trying to move my databases & tables from the old server to the new server. I thought that I could do this by simply copying the /var/lib/mysql/ directory from the old server and overwriting the same directory on the new server. However after doing this, I now receive this error in phpMyAdmin on the new server when I click on any of the tables or databases:

`

1932 - Table 'phpmyadmin.pma__tracking' doesn't exist in engine`

However I can clearly see that it exists:

Is there a solution to this? I'm not sure what I did wrong. Thanks!

asked Aug 20, 2015 at 2:19

Mitch TalmadgeMitch Talmadge

1601 gold badge1 silver badge8 bronze badges

2

You can copy files from one server to the next to move your database. To do this, you must make sure that both instances of your DB are stopped. I also recommend using rsync to copy your database files. In addition to making sure that both database servers are stopped before moving the files, you will most likely need to change the ownership of your files. sudo chown --recursive mysql:mysql /var/lib/mysql before starting your new instance.

There are other options available to move your database. If your database isn't terribly large, mysqldump is probably the most simple. I have attached the documentation for mysqldump below as provided by MariaDB, as you are using MariaDB.

https://mariadb.com/kb/en/mariadb/mysqldump/

Another option to consider, a bit more complex but a little more flexible for size, would be Percona's xtrabackup.

https://www.percona.com/software/mysql-database/percona-xtrabackup

answered Aug 20, 2015 at 3:01

7

If you are just moving your DB from one folder to another folder, but your InnoDB tables no longer work, because they can't be found and the error message "Table does not exist in the engine" is displayed, then these pair of SQL statements can be as quick as helpful:

ALTER TABLE __yourtable_name__ DISCARD TABLESPACE ;
ALTER TABLE __yourtable_name__ IMPORT TABLESPACE ;

In pratice, the

LATEST FOREIGN KEY ERROR

2019-07-23 10:43:05 0x7f68a4255700 Error in foreign key constraint of table lhprovedorinfo/NOTAFISCAL:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:

0 file is detached from

LATEST FOREIGN KEY ERROR

2019-07-23 10:43:05 0x7f68a4255700 Error in foreign key constraint of table lhprovedorinfo/NOTAFISCAL:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:

1 file and then bound again, so that table contents turn into readable.

It worked in my case. Hope yours too.

John K. N.

17.6k12 gold badges51 silver badges110 bronze badges

answered Sep 14, 2018 at 7:24

I solved the problem by following this answer: https://stackoverflow.com/a/11506495/2364405

Except with these changes:

• On Step 5, I did not find `

  ---

  LATEST FOREIGN KEY ERROR

  ---

  2019-07-23 10:43:05 0x7f68a4255700 Error in foreign key constraint of table lhprovedorinfo/NOTAFISCAL: there is no index in the table which would contain the columns as the first columns, or the data types in the table do not match the ones in the referenced table or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:

  2, but I found

  ---

  LATEST FOREIGN KEY ERROR

  ---

  2019-07-23 10:43:05 0x7f68a4255700 Error in foreign key constraint of table lhprovedorinfo/NOTAFISCAL: there is no index in the table which would contain the columns as the first columns, or the data types in the table do not match the ones in the referenced table or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:

  ` 3 instead. I used WinSCP and downloaded this file, then imported it into phpMyAdmin (This takes care of steps 6 and 7).
• On Step 8, I only changed the username/password. My tables have TWO underscores ( `

  ---

  LATEST FOREIGN KEY ERROR

  ---

  2019-07-23 10:43:05 0x7f68a4255700 Error in foreign key constraint of table lhprovedorinfo/NOTAFISCAL: there is no index in the table which would contain the columns as the first columns, or the data types in the table do not match the ones in the referenced table or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:

  ` 4 for example), so I did not change them to one underscore.

Somehow this allowed me to use phpMyAdmin again with no errors. :)

answered Aug 20, 2015 at 5:27

Mitch TalmadgeMitch Talmadge

1601 gold badge1 silver badge8 bronze badges

I was getting this same error after moving a mysqldump backup to a new machine, and doing some updates on the database I've deleted a unique index which had a key for an foreign constraint.

I did realize It was a Foreign Key Error using: show engine innodb status.

LATEST FOREIGN KEY ERROR

2019-07-23 10:43:05 0x7f68a4255700 Error in foreign key constraint of table lhprovedorinfo/NOTAFISCAL:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:

After that, I'v disabled the Foreign Key Checks with: set FOREIGN_KEY_CHECKS=0; Then dropped the foreign key and recreated it, in order to mysql create the necessary indexes. And the my table went back to life.

Hope it helps someone.

Erik Darling

40.1k14 gold badges129 silver badges452 bronze badges

answered Jul 23, 2019 at 14:47

I did the same - but moved from docker hosted on Windows 10 to a docker on Debian 10.9 - both having the /var/lib/mysql docker-mapped to a host directory.

If I move from docker on Windows to docker on another Windows machine there is no error.

I therefore suspected the different ways of handling case sensitivity on Windows and Linux. Inspecting the files in /var/lib/mysql I noticed that my 'File' table had a File.frm and a file.idb file. I tried to rename file.ibd to File.ibd to no avail but renaming both to lowercase makes the table readable again !!

In Windows docker defaults are MariaDB [(none)]> show variables where Variable_name like 'lower%';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 2     |
+------------------------+-------+

but in Linux docker the defaults are

MariaDB [(none)]> show variables where Variable_name like 'lower%';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+

which kind of explains :-)

If you had different settings for lower case on your systems it could explain.

answered Jun 9, 2021 at 13:19

ClausClaus

11 bronze badge

In my case the following worked:

• I removed stray table.frm,table.idb files from /var/lib/mysql/database
• drop table
• create table (structure taken from a healthy table - show create table table)
• if necessary re-import table from backup

answered Oct 21, 2022 at 13:45

Had the same problem. I had installed a new OS on my machine. So I copied my Xampp folder then after installing the OS and copying back the files I got the "can;t find table in engine problem" Deleting all the contents in the mysql folder then copying everything in the mysql folder from the old xampp worked like charm.