4

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:

enter image description here

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

3

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

  • Also note, before trying the first option, go ahead and clear out the mysql directory on your new server before moving the files again. – Nathan Aug 20 '15 at 3:02
  • rsync made no difference :( I cleared it out and made sure mysql was stopped on both servers first. I'll try mysqldump – Mitch Talmadge Aug 20 '15 at 3:24
  • Did you change the ownership of the files before starting on the second server? Being that you replied in 23 minutes, I'd bet that mysqldump will go rather quickly for you. try something like this. mysqldump -u <user> -p --routines <database name> > dbdump.sql copy that to your new server restore with mysql -u <user> -p < dbdump.sql – Nathan Aug 20 '15 at 3:38
  • I did make sure they had the proper permissions before starting mysql back up. mysqldump didn't work either. The original way that I moved the sql files over was by backing them up into a tar file with permissions, sending it to the new server, unpacking, and using cp -av to copy them over with permissions. Also my databases are pretty tiny :P – Mitch Talmadge Aug 20 '15 at 3:48
  • An idea I had was to upgrade the old server to use the same version of MariaDB, then transfer the databases over. Would that do any good? Is there any incompatibility with MySQL and MariaDB? – Mitch Talmadge Aug 20 '15 at 3:50
1

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 .IDB file is detached from .FRM file and then bound again, so that table contents turn into readable.

It worked in my case. Hope yours too.

0

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 create_tables.sql, but I found create_tables.sql.gz 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 (pma__bookmark for example), so I did not change them to one underscore.

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

0

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.

-1

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.

-2

Check that the copied files does have the proper user:group (e.g. mysql:mysql).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.