The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Table Name Does Not Exist Errors

Discussion in 'Database Discussions' started by Hamza.Iqbal, Jun 14, 2015.

  1. Hamza.Iqbal

    Hamza.Iqbal Member

    Joined:
    Jun 14, 2015
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Pakistan
    cPanel Access Level:
    Website Owner
    Moderators Note: moved from original thread located here:
    https://forums.cpanel.net/threads/innodb-corruption-repair-guide.418722/



    Hi RayanR,
    I have followed your all steps but when I am using mysqldump command, I have got this error:
    mysqldump: Got error: 1146: Table 'db_name.table_name' doesn't exist when using LOCK TABLES.

    Can you please help me,

    Code:
    SQL error log:
    150614 10:24:53 mysqld_safe mysqld from pid file /var/lib/mysql/server-1.serverlin.com.pid ended
    150614 10:29:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    150614 10:29:03 [Note] Plugin 'FEDERATED' is disabled.
    150614 10:29:03 InnoDB: The InnoDB memory heap is disabled
    150614 10:29:03 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    150614 10:29:03 InnoDB: Compressed tables use zlib 1.2.3
    150614 10:29:03 InnoDB: Initializing buffer pool, size = 128.0M
    150614 10:29:03 InnoDB: Completed initialization of buffer pool
    150614 10:29:03 InnoDB: highest supported file format is Barracuda.
    150614 10:29:04 InnoDB: Waiting for the background threads to start
    150614 10:29:05 InnoDB: 5.5.42 started; log sequence number 22239174
    150614 10:29:05 InnoDB: !!! innodb_force_recovery is set to 1 !!!
    150614 10:29:05 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
    150614 10:29:05 [Note] - '0.0.0.0' resolves to '0.0.0.0';
    150614 10:29:05 [Note] Server socket created on IP: '0.0.0.0'.
    150614 10:29:05 [Note] Event Scheduler: Loaded 0 events
    150614 10:29:05 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.5.42-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
    
    
     
    #1 Hamza.Iqbal, Jun 14, 2015
    Last edited by a moderator: Jun 15, 2015
  2. cPanelRyanR

    cPanelRyanR Member
    Staff Member

    Joined:
    Jul 17, 2014
    Messages:
    9
    Likes Received:
    9
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    @Hamza.Iqbal

    What's happening there is that your data dictionary (the metadata stored for each InnoDB table in your databases - this data exists entirely within the ibdata1 file) has no record of that particular table. It does, however, see that at least a .frm file exists in the file structure, so it will report the table's existence superficially (when listing tables, for example), but will allow no table operations to be performed on it, because as far as InnoDB is concerned it doesn't exist.

    Ideally, the mysqldump should proceed beyond this and still provide the SQL dumps for all functional tables, however you might try adding --skip-lock-tables to the mysqldump command to see if it allows you to proceed any further.

    On the other hand, if ALL of the tables in that particular database are reporting "not to exist", then a mysql dump will unfortunately not be of any assistance in that case, because the data will be inaccessible. Your goal with the mysqldump ahead of time is simply to dump any remaining accessible data that still exists. If all of the tables in the database are essentially inaccessible or unusable, then I would simply suggest ensuring that a copy of the MySQL file structure is made (ibdata1, ib_logfile0/1, and the database folder).

    You might try running a simple mysqlcheck on that database - if all of the tables respond with a "table does not exist" error, then the data dictionary has no information stored for those tables and a fresh mysqldump will not be useful.

    What is the recovery situation that you're in? Backups/no backups? All tables down/some tables down? What procedures have you already gone through leading up to this? Did you try re-generating the ibdata1 file, for example? To be clear, re-creating the ibdata1 file is almost always a bad idea, but knowing whether it's been tried helps to understand the situation you may be in. The only time an ibdata1 file should be re-created is if you have no concern for the existing InnoDB data, or if you've got full and complete SQL backups with an understanding that they'll have to be utilized in order to restore functionality.
     
    Hamza.Iqbal and Infopro like this.
  3. Hamza.Iqbal

    Hamza.Iqbal Member

    Joined:
    Jun 14, 2015
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Pakistan
    cPanel Access Level:
    Website Owner
    After trying mysqldump with --skip-lock-tables, Received this error.
    root@server-1 [~]# mysqldump --skip-lock-tables --all-databases > tes.sql
    Error: Couldn't read status information for table wp_maps ()
    mysqldump: Couldn't execute 'show create table `wp_maps`': Table 'bodlaeye_eyecare.wp_maps' doesn't exist (1146)



    After moving Backup of (ibdata1, ib_logfile0/1) and tried to start SQL, received this error:
    Starting MySQL.. ERROR! The server quit without updating PID file (/var/lib/mysql/server-1.serverlin.com.pid).

    And here is the error log:
    150615 10:36:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/ mysql
    150615 10:36:53 [Note] Plugin 'FEDERATED' is disabled.
    150615 10:36:53 InnoDB: The InnoDB memory heap is disabled
    150615 10:36:53 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    150615 10:36:53 InnoDB: Compressed tables use zlib 1.2.3
    150615 10:36:53 InnoDB: Initializing buffer pool, size = 128.0M
    150615 10:36:53 InnoDB: Completed initialization of buffer pool
    150615 10:36:53 InnoDB: Operating system error number 13 in a file operation.
    InnoDB: The error means mysqld does not have the access rights to
    InnoDB: the directory.
    InnoDB: File name ./ibdata1
    InnoDB: File operation call: 'open'.
    InnoDB: Cannot continue operation.
    150615 10:36:53 mysqld_safe mysqld from pid file /var/lib/mysql/server-1.serverl in.com.pid ended

    Only All InnoDBs are not working, MyISAM are working fine.

    MySQL is start only when I delete (ibdata1, ib_logfile0/1) files, But After deleting this files InnoDB are not working correctly.
     
    #3 Hamza.Iqbal, Jun 15, 2015
    Last edited: Jun 15, 2015
  4. cPanelRyanR

    cPanelRyanR Member
    Staff Member

    Joined:
    Jul 17, 2014
    Messages:
    9
    Likes Received:
    9
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    This is the problem, unfortunately. The ibdata1 file contains all of the metadata that InnoDB relies so heavily on, so when you remove that file, you are essentially removing all of your InnoDB tables along with it, giving you a clean slate (this is why many users take this route and see it as "successful" at first - because in an InnoDB corruption scenario, it will allow MySQL to start... but only because it's not loading any of your data). I would go so far as to say that I would never recommend removing the ibdata1 file, in any scenario, however - realistically - it can be performed if you've tried absolutely everything else already, but with the understanding that you'll have to re-create all of your InnoDB tables in order to restore functionality.

    Do you have a backup of the original ibdata1 file (before you initially removed it)? If so, could you restore it back to /var/lib/mysql (with the MySQL service stopped, of course), then rename the existing log files? I understand that MySQL might crash with that ibdata1 file in place, however you will have a much higher likelihood of success troubleshooting the crash than you will of restoring your data on to a fresh ibdata1 file, if you don't have any backups.

    If you could restore the original ibdata1 file, and show me the logs that display the cause of the crash, I may be able to help you troubleshoot it.
     
  5. Hamza.Iqbal

    Hamza.Iqbal Member

    Joined:
    Jun 14, 2015
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Pakistan
    cPanel Access Level:
    Website Owner
    I have deleted new (ibdata1, ib_logfile0/1) files from /var/lib/mysql, And transferred the Old (ibdata1, ib_logfile0/1) files in /var/lib/mysql but After transferring Old (ibdata1, ib_logfile0/1) files in /var/lib/mysql And tried to start MySQL, got this error:
    Starting MySQL.. ERROR! The server quit without updating PID file (/var/lib/mysql/server-1.com.pid).

    And ERROR LOG IS:
    And here is the error log:
    150615 10:36:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/ mysql
    150615 10:36:53 [Note] Plugin 'FEDERATED' is disabled.
    150615 10:36:53 InnoDB: The InnoDB memory heap is disabled
    150615 10:36:53 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    150615 10:36:53 InnoDB: Compressed tables use zlib 1.2.3
    150615 10:36:53 InnoDB: Initializing buffer pool, size = 128.0M
    150615 10:36:53 InnoDB: Completed initialization of buffer pool
    150615 10:36:53 InnoDB: Operating system error number 13 in a file operation.
    InnoDB: The error means mysqld does not have the access rights to
    InnoDB: the directory.
    InnoDB: File name ./ibdata1
    InnoDB: File operation call: 'open'.
    InnoDB: Cannot continue operation.

    150615 10:36:53 mysqld_safe mysqld from pid file /var/lib/mysql/server-1.com.pid ended

    I thing error is: "mysqld does not have the access rights to the directory. File name ./ibdata1"
     
  6. cPanelRyanR

    cPanelRyanR Member
    Staff Member

    Joined:
    Jul 17, 2014
    Messages:
    9
    Likes Received:
    9
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    Could you perform the following commands and let me know what the output is?

    Code:
    ls -l /var/lib/mysql/ib*
    ls -ld /var/lib/mysql
    The ownership should be "mysql:mysql", and the /var/lib/mysql folder permissions should be 0751 ( rwxr-x--x).
     
  7. Hamza.Iqbal

    Hamza.Iqbal Member

    Joined:
    Jun 14, 2015
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Pakistan
    cPanel Access Level:
    Website Owner
    Output of "ls -l /var/lib/mysql/ib*" And "ls -ld /var/lib/mysql":


    Code:
    root@server-1 [~]# ls -l /var/lib/mysql/ib*
    -rw-rw---- 1 mysql mysql 10485760 Jun 15 11:08 /var/lib/mysql/ibdata1
    -rw-r--r-- 1 root  root  35651584 Jun 14 09:57 /var/lib/mysql/ibdata1.bak
    -rw-rw---- 1 mysql mysql  5242880 Jun 15 11:08 /var/lib/mysql/ib_logfile0
    -rw-rw---- 1 mysql mysql  5242880 Jun 15 11:08 /var/lib/mysql/ib_logfile1
    root@server-1 [~]# ls -ld /var/lib/mysql
    drwxr-x--x 83 mysql mysql 12288 Jun 15 11:08 /var/lib/mysql/
     
  8. cPanelRyanR

    cPanelRyanR Member
    Staff Member

    Joined:
    Jul 17, 2014
    Messages:
    9
    Likes Received:
    9
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    @Hamza.Iqbal -

    Would you be able to open up a support ticket on this, and let us know what the ticket number is? Your permissions/ownership look to be correct, so there is something else preventing the use of ibdata1 at that path. It looks like it's specifically unable to actually open the ibdata1 file for use, so my guess is that there is still a mysqld process running, and doing service restarts is actually just trying to start a *new* mysqld process on top of it, but it could be a number of other things as well.
     
  9. Hamza.Iqbal

    Hamza.Iqbal Member

    Joined:
    Jun 14, 2015
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Pakistan
    cPanel Access Level:
    Website Owner
    My ticket id 6676997
     
  10. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    653
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello,

    To update, MySQL now starts well (without the InnoDB databases). I suggest consulting with a qualified system administrator or database expert if you require additional assistance with repairing your InnnoDB databases.

    Thank you.
     
Loading...

Share This Page