Table Name Does Not Exist Errors

June 14, 2015 20:22

@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.

0

Hamza.Iqbal

June 15, 2015 11:01

however you might try adding --skip-lock-tables to the mysqldump command to see if it allows you to proceed any further.

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

All tables down/some tables down?

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.

0

cPanelRyanR

June 15, 2015 12:06

MySQL is start only when I delete (ibdata1, ib_logfile0/1) files, But After deleting this files InnoDB are not working correctly.

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.

0

Hamza.Iqbal

June 15, 2015 12:21

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"

0

cPanelRyanR

June 15, 2015 12:53

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"

Could you perform the following commands and let me know what the output is?

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).

0

Hamza.Iqbal

June 15, 2015 13:49

Output of "ls -l /var/lib/mysql/ib*" And "ls -ld /var/lib/mysql":

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/

0

cPanelRyanR

June 15, 2015 14:49

@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.

0

Hamza.Iqbal

June 15, 2015 15:23

My ticket id 6676997

0

cPanelMichael

June 17, 2015 12:58

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.

0

Table Name Does Not Exist Errors

Comments

Didn't find what you were looking for?