Bobcares

mysqldump error 1146 table doesn’t exist – How to fix it

by | May 26, 2021

We may come across the mysqldump error 1146 table doesn’t exist while we perform the database dump.

As part of our Server Management Services, we assist our customers with several MySQL queries.

Today, let us see how to fix this error in Plesk and Directadmin.

 

mysqldump: Got error: 1146: Table doesn‘t exist

Recently, while performing the database dump, some of our users noticed the error:

mysqldump: Got error: Table ‘myDatabase.table’ doesn‘t exist when using LOCK TABLES

In order to check, we go to MySQL:

 mysql -u admin -p

Then we query for the tables:

show tables;

Here, we can find the table. However, when we query for that particular table:

select * from table

We get the same error:

ERROR 1146 (42S02): Table 'myDatabase.table' doesn't exist

mysqldump: Got error: 1146: Table doesn‘t existWe can try to repair it via:

mysqlcheck -u admin -p --auto-repair --check --all-databases

However, the error may prevail:

Error : Table 'myDatabase.table' doesn't exist

The major causes of this can be:

  • InnoDB tablespace might have been deleted and recreated but corresponding .frm files of InnoDB tables from the database directory were not removed, or .frm files were moved to another database
  • Incorrect permissions and ownership on table files in the MySQL data directory
  • A corrupt table data.

 

How to fix it?

Moving ahead, let us see how our Support Techs fix this error in both Plesk and DirectAdmin.

Solution: Plesk

  1. Initially, we tried to connect to the server using SSH.
  2. Then we try to use --skip-lock-tables parameter with mysqldump to skip lock tables.
    For example,

    #mysqldump –skip-lock-tables -u<db_user> -p<db_user_password> database_name > /root/database_dump.sql
  3. If it does not help, we check permissions and ownership on the table’s files in the MySQL data directory for the database that fails to dump. It should be mysql for both owner and group:
    • Find data dir location:
      RHEL/CentOS

      #grep datadir /etc/my.cnf
      datadir=/var/lib/mysql

      Debian/Ubuntu

      #grep -iR datadir /etc/mysql*
      /etc/mysql/mysql.conf.d/mysqld.cnf:datadir = /var/lib/mysql
    • Check permissions:
      # ls -la /var/lib/mysql/example_db/
    • Fix permissions:
      # chown -R mysql:mysql /var/lib/mysql/example_db/
  4. If it is still not possible, we try to repair the table in the error using the native MySQL repair tool:
    # plesk db
    
    mysql> use example_db;
    mysql> REPAIR TABLE <TABLENAME>;

    Note: We need to replace the <TABLENAME> with the table name in the error message.

  5. If the issue still persists, most probably ibdata* file does not have the info about the table. However, the orphaned .frm files still persist on the file system. We remove it:
    • To verify whether the table is corrupt or not, we run:
      # plesk db
      
      mysql> use database example_db;
      mysql> desc <TABLENAME>;

      If this command fails with the error, it means that ibdata* does not have the information about the table and we need to remove the .frm file.

    • To do so, we browse to the database directory /var/lib/mysql/example_db/ and move .frm file:
      cd /var/lib/mysql/example_db/
      # mv <TABLENAME>.frm /root/<TABLENAME>.frm
  6. If these options fail and we have no valid backups to restore, the only available option to save the database is to dump it with the innodb_force_recovery option

Solution: DirectAdmin

Suppose, we get the error for the User database and Table:

mysqldump error output: mysqldump: Got error: 1146: Table ‘user_db.table‘ doesn’t exist when using LOCK TABLES
  1. In this case, we check to see if there are any other data files, or if it’s just the .frm file:
    cd /var/lib/mysql/user_db
    ls -la table.*

    If it’s just the table.frm file, then the rest of the data is likely lost. However, we may be able to rebuild the table.

  2. To do so, we need to read the .frm file. We need the mysqlfrm tool for that, eg: yum install mysql-utilities. Once we install it, we check if it can be read:
    mysqlfrm –diagnostic table.frm

    This can output the full CREATE TABLE syntax.  We save this somewhere, until the end of the last ; character.

    We can either delete the “CHARACTER SET “,  or change it to the correct charset.

  3. Then, we have to remove the broken table.  To do so, we login to /phpMyAdmin and run the query:
    DROP TABLE user_db.table
  4. Finally, we run the CREATE TABLE query from above, to rebuild the table.

[Finding it hard to fix? We’d be happy to assist you]

 

Conclusion

In short, we saw how our Support Techs fix the MySQL error for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Never again lose customers to poor
server speed! Let us help you.

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie
_clck, _clsk, CLID, ANONCHK, MR, MUID, SM

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

_reb2bgeo - The visitor's geographical location

_reb2bloaded - Whether or not the script loaded for the visitor

_reb2bref - The referring URL for the visit

_reb2bsessionID - The visitor's RB2B session ID

_reb2buid - The visitor's RB2B user ID

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid
_reb2bgeo, _reb2bloaded, _reb2bref, _reb2bsessionID, _reb2buid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF