phpMyAdmin: Moved Database Files, Then
Getting “Table Doesn't Exist In Engine”

Posted:

I recently migrated my development system to a new server.  Instead of exporting and importing some very large SQL dumps, I thought I'd save some time by just moving my development database files from my old virtual machine to my new one.  I had a bugger of a time getting the database to work properly, as trying to read the tables gave me the error “Table doesn't exist in engine.”  Here was the solution (using MariaDB on Debian 10, interfacing via phpMyAdmin).

On the new system, stop the mySQL service, for example:

> /etc/init.d/mysql stop

Back up the new system's database files so that if you screw up you can always start over, for example:

> mv /var/lib/mysql /var/lib/mysql.backup

Copy your old database files to the new system at /var/lib/mysql/.

Change ownership of the database files to mysql:

> chown -R mysql:mysql /var/lib/mysql/

MySQL likes permissions to look like -rw-rw-???.  Most likely you will only need to add group-write permission:

> chmod -R g+w /var/lib/mysql/

Restart the mysql service, e.g.

> /etc/init.d/mysql start

Log into mysql from the command line as root.

Create a database called phpmyadmin if it doesn't already exist.

Create a user called pma and set the “host” to the hostname or IP address of your web server (or localhost if applicable), make a note of the password, and grant the new user full control over the phpmyadmin database.  It is recommended that this user does not have access to anything other than this database.  Example query:

GRANT ALL PRIVILEGES ON phpmyadmin.* TO 'pma'@'localhost';

Go to the phpMyAdmin installation directory and find sql/create_tables.sql, most likely located at /usr/share/phpMyAdmin/sql/create_tables.sql.  (On some installations, you may have to extract create_tables.sql from /usr/share/phpMyAdmin/sql/create_tables.sql.gz.)  Open create_tables.sql in a text editor and copy all text.

In phpMyAdmin, select the phpmyadmin database and click on the “SQL” tab.  Paste the entire text from create_tables.sql into the text box, and run the query.

Open the config.inc.php file in the phpMyAdmin install directory (e.g. /usr/share/phpMyAdmin/config.inc.php), and make sure the following lines are present and configured accordingly:

/* User used to manipulate with storage */
$cfg['Servers'][$i]['controlhost'] = 'localhost';
//$cfg['Servers'][$i]['controlport'] = ''; // Uncomment and fill in if needed
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = '[Your database password for user `pma`]';

/* Storage database and tables */
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
// Uncomment the following lines
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
$cfg['Servers'][$i]['tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['recent'] = 'pma__recent';
$cfg['Servers'][$i]['favorite'] = 'pma__favorite';
$cfg['Servers'][$i]['users'] = 'pma__users';
$cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
$cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';
$cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches';
$cfg['Servers'][$i]['central_columns'] = 'pma__central_columns';
$cfg['Servers'][$i]['designer_settings'] = 'pma__designer_settings';
$cfg['Servers'][$i]['export_templates'] = 'pma__export_templates';

Save the file.

Restart the mysql service, e.g.

> /etc/init.d/mysql restart

Log out and back into phpmyadmin to ensure changes take effect.  (For good measure, it's not a bad idea to clear the server's cookies.)

You should now be able to read tables in your migrated databases without any issues.