Plesk Question posted in
The official documentation for Plesk Obsidian can be found here.

mysqldump: Couldn't execute 'show create table `xxx.yyy`': Table 'yyy' doesn't exist in engine (1932) – Plesk

I had to redo my server (Debian 9) in Proxmox. I updated a package (libc6) and I broke the dependencies (I didn’t snapshot it…). One of the affected daemons was MySQL / MariaDB, I could not make a backup from PhpMyAdmin because the daemon does not work and I could not connect to the database.

Now I have installed Debian 10, but I am having problems recovering the database from the other machine.

mysqldump: Couldn't execute 'show create table `xxx.yyy`': Table 'yyy' doesn't exist in engine (1932)

The following errors are prompted when I tried the following solutions:

root@debian:~# mysqldump -u root -p --all-databases > all_databases.sql
Enter password:
mysqldump: Got error: 1932: "Table 'mysql.gtid_slave_pos' doesn't exist in engine" when using LOCK TABLES
root@debian:~# mysqldump --skip-lock-tables -u root -p --all-databases > all_databases.sql
Enter password:
mysqldump: Couldn't execute 'show create table `gtid_slave_pos`': Table 'mysql.gtid_slave_pos' doesn't exist in engine (1932)

Source: https://support.plesk.com/hc/en-us/articles/213931725-Dump-of-the-MySQL-database-hosted-on-the-Plesk-server-fails-mysqldump-table-doesn-t-exist-when-using-LOCK-TABLES

root@debian:~# mysqlfrm --server=xxx:yyy@localhost:3306 test.frm --port=3310
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ...
Usage: mysqlfrm --server=[user[:<pass>]@host[:<port>][:<socket>]|<login-path>[:<port>][:<socket>]] [pathtbl1.frm|db:tbl.frm]

mysqlfrm: error: Can't connect to MySQL server on 'localhost:3306' (111 Connection refused)

Source: https://dba.stackexchange.com/a/127813

MariaDB [(none)]> ALTER TABLE mysql.gtid_slave_pos DISCARD TABLESPACE;
ERROR 1932 (42S02): Table 'mysql.gtid_slave_pos' doesn't exist in engine

Source: Restore MySQL database using only .frm and .ibd files

Is there any option left?

EDIT:

I’ll answer some questions danblack asked me.

  • How did I get into this state?

I don’t exactly know it but I think that I broke MySQL after I tried to restart several times mysqld/mariadb service while I tried several solutions downgrading libc6 or trying to make mariadb to run again.

  • What was the MySQL/MariaDB version in Debian 9?

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

  • What is the version in Debian 10?

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

  • Did I copy the entire datadir from one to another?

No, I didn’t try that… But I didn’t find anything useful on stackoverflow sites nor google.

  • Did I run mysql_upgrade?

No, I didn’t try that maybe by updating mariadb in Debian 9 could be a possible solution. But reviewing packages for Debian 9, I didn’t found the mariadb 10.3 version for Debian 9 (the one that clib6 2.30-10 requires, I was on 2.14).

Can you take a mysqldump of other databases in the server (ie. not the mysql named one)?

Yes, I did backups for some databases but I couldn’t backup all of them (only 3 or 4 from 10).

Tags:

2

Answers


  1. 0

    The syntax is

    `dbname`.`tablename`

    not

    `dbname.tablename`
    Login or Signup to reply.
  2. 0

    You have at least 2 options:

    Option #1: Fix it in-place.

    1. Create a snapshot and/or backup of the server before trying to fix anything.
    2. Remove mariadb.
    3. Install mariadb…which should fix any software issues preventing it from starting up.
    4. Once it is working, create a backup of your databases using mysqldump.

    Option #2: Create a new server.

    1. Spin up another virtual machine just like it (install the same OS and MariaDB version).
    2. Stop the new mariadb service and copy the database files from the old server to the new. The default location on Ubuntu is /var/lib/mysql/
    3. Once you overwrite the files, start the mariadb service and jump into the mysql prompt to verify that the databases show up. (e.g. show databases;)
    4. Once it is working, create a backup of your databases using mysqldump.

    This is how I install MariaDB onto a server and setup automated backups: https://hammondslegacy.com/forum/viewtopic.php?f=40&t=266

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search