Mysql – InnoDB tables inaccessible after reboot

innodblinuxMySQL

After reboot each time, I can't see my InnoDB table. However, when use command mode I can see my tables, but cannot access to it.

mysql> show tables;
+----------------------+
| Tables_in_xpl_ticket |
+----------------------+
| active_list          |
| bill_block           |
| bill_block_list      |
| block                |
| block_list           |
| box                  |
| cisco_switch         |
| mac_add              |
| mac_change           |
| month                |
| new_connect          |
| new_user             |
| open                 |
| package_change       |
| paid_list            |
| pay                  |
| problem              |
| re_open              |
| refund               |
|  ticket              |
| user                 |
| user_log             |
+----------------------+
22 rows in set (0.00 sec)

But when I want to access a table it says table doesn't exist;

mysql> select * from active_list;
ERROR 1146 (42S02): Table 'xpl_ticket.active_list' doesn't exist

Edit by RolandoMySQLDBA

Please run the following in MySQL:

SELECT VERSION();
SELECT CONCAT('[',table_name,']') tbl_name
FROM information_schema.tables WHERE table_schema='xpl_ticket';

Please run the following in the OS:

cd /var/lib/mysql/xpl_ticket
ls -l

Hi RolandoMySQLDBA,

Thank you for your quick reply. Here is all those outputs.

mysql> SELECT VERSION();
+-------------------------+
| VERSION()               |
+-------------------------+
| 5.5.31-0ubuntu0.13.04.1 |
+-------------------------+
1 row in set (0.00 sec)


mysql> SELECT CONCAT('[',table_name,']') tbl_name
-> FROM information_schema.tables WHERE table_schema='xpl_ticket';
+-------------------+
| tbl_name          |
+-------------------+
| [active_list]     |
| [bill_block]      |
| [bill_block_list] |
| [block]           |
| [block_list]      |
| [box]             |
| [cisco_switch]    |
| [mac_add]         |
| [mac_change]      |
| [month]           |
| [new_connect]     |
| [new_user]        |
| [open]            |
| [package_change]  |
| [paid_list]       |
| [pay]             |
| [problem]         |
| [re_open]         |
| [refund]          |
| [ticket]          |
| [user]            |
| [user_log]        |
+-------------------+
22 rows in set (0.03 sec)

My data directory is not in "/var/lib/mysql" path. I have been changed it to "/var/www/xampp/mysql/data" path. So I am going to execute the following code.

Lurid / # cd /var/www/xampp/mysql/data/xpl_ticket/
Lurid xpl_ticket # ls -l
total 265
-rwxrwxrwx 1 root root 9272 Jun  6 12:48 active_list.frm
-rwxrwxrwx 1 root root 8654 Jun  6 12:48 bill_block.frm
-rwxrwxrwx 1 root root 9272 Jun  6 12:48 bill_block_list.frm
-rwxrwxrwx 1 root root 8654 Jun  6 12:48 block.frm
-rwxrwxrwx 1 root root 9272 Jun  6 12:48 block_list.frm
-rwxrwxrwx 1 root root 8802 Jun  6 12:48 box.frm
-rwxrwxrwx 1 root root 8648 Jun  6 12:48 cisco_switch.frm
-rwxrwxrwx 1 root root   65 Jun  6 12:48 db.opt
-rwxrwxrwx 1 root root 8690 Jun  6 12:48 mac_add.frm
-rwxrwxrwx 1 root root 8698 Jun  6 12:48 mac_change.frm
-rwxrwxrwx 1 root root 8618 Jun  6 12:48 month.frm
-rwxrwxrwx 1 root root 8716 Jun  6 12:48 new_connect.frm
-rwxrwxrwx 1 root root 9002 Jun  6 12:48 new_user.frm
-rwxrwxrwx 1 root root 8680 Jun  6 12:48 open.frm
-rwxrwxrwx 1 root root 8724 Jun  6 12:48 package_change.frm
-rwxrwxrwx 1 root root 8692 Jun  6 12:48 paid_list.frm
-rwxrwxrwx 1 root root 8592 Jun  6 12:48 pay.frm
-rwxrwxrwx 1 root root 8802 Jun  6 12:48 problem.frm
-rwxrwxrwx 1 root root 8670 Jun  6 12:48 refund.frm
-rwxrwxrwx 1 root root 8714 Jun  6 12:48 re_open.frm
-rwxrwxrwx 1 root root 8900 Jun  6 12:48 ticket.frm
-rwxrwxrwx 1 root root 8704 Jun  6 12:48 user.frm
-rwxrwxrwx 1 root root 8808 Jun  6 12:48 user_log.frm

Hi Mannoj

These tables are created in these same database.

I have some errors in my "/var/log/mysql/error.log". I don't understand what are those. Here are they ….

130611 12:41:28 [Note] /usr/sbin/mysqld: Normal shutdown

130611 12:41:29 [Note] Event Scheduler: Purging the queue. 0 events
130611 12:41:30  InnoDB: Starting shutdown...
130611 12:41:31  InnoDB: Shutdown completed; log sequence number 1595675
130611 12:41:31 [Note] /usr/sbin/mysqld: Shutdown complete

130611 12:43:15 [Note] Plugin 'FEDERATED' is disabled.
130611 12:43:15 InnoDB: The InnoDB memory heap is disabled
130611 12:43:15 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130611 12:43:15 InnoDB: Compressed tables use zlib 1.2.7
130611 12:43:15 InnoDB: Using Linux native AIO
130611 12:43:15 InnoDB: Initializing buffer pool, size = 128.0M
130611 12:43:15 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
130611 12:43:15  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
130611 12:43:15  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
130611 12:43:16  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new

Thanks to both of you.

Best Answer

Look at the .frm files. They are owned by root root.

They are supposed to be owned by mysql mysql

My guess is that you copied the data rather than use MySQL techniques to import.

Perhaps you should change the ownership of the entire data folder.

Please run the following commands:

cd /var/www/xampp/mysql/data
chown -R mysql:mysql *

Then restart mysql.

As for the error messages, the InnoDB System Tablespace (ibdata1) and the Redo Log Files (ib_logfile0,ib_logfile1) are being created.

From the above messages, my guess is that you did this something like this

You had shutdown mysql
You copied /var/lib/mysql to /var/www/xampp/mysql/data
You started mysql
mysqld did not see InnoDB Infrastructure files, so they were created

If you have an all-MyISAM database (every table uses the InnoDB storage engine) you can easily get away with copying data folders. You would still have to change file ownership to mysql mysql. If there are any InnoDB tables, you must copy the entire data folder, not just a database within the data folder.

CAVEAT

Please note that running SHOW TABLES; simply walks across the current database telling you table names by looking at the .frm files without checking ownership or table validity. Once you actually perform some SQL again the table that all metadata checks against the table are done.

I have very bad news for you.

You should not have deleted the ibdata1 file. Here is why:

ibdata1 contains four type of information:

table metadata
MVCC data
data pages (with innodb_file_per_table enabled)
index pages (with innodb_file_per_table enabled)

Each InnoDB table created has a numercial id assigned to it via some auto increment metadata feature to each ibd file. That internal tablespace id (ITSID) is embedded in the .ibd file. That number is checked against the list of ITSIDs maintained, guess where, ... ibdata1.

I also have very good news for you along with some bad news.

It is possible to reconstruct ibdata1 to have the correct ITSIDs but it takes work to do it. While I personally have not done procedure alone, I assisted a client at my employer's web hosting to do this. We figured this out together but since the client hosed ibdata1, I let him do most of the work (30 InnoDB tables).

Anyway, here a past post I made in the DBA StackExchange. I answered another question whose root cause was the mixing up of ITSIDs.

To cut right to the chase, here is the article explaining what to do with reference to ITSID and how to massage ibdata1 into acknowledging the presence of the ITSID contained within the .ibd file.

I am sorry there is no quick-and-dirty method for recovering the .ibd file other than playing games with ITSIDs.

UPDATE 2011-10-17 06:19 EDT

Here is your original innodb configuration from your question:

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000

Please notice that innodb_log_file_size is there twice. Look carefully...

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G <----
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M <----
innodb_lock_wait_timeout = 18000

The last setting of innodb_log_file_size takes precedence. MySQL expected to start up with the log files being 5M. Your ib_logfile0 and ib_logfile1 were 1G when you tried to start up mysqld. It saw a size conflict and took the path of least resistance, which was to disable InnoDB. That's why InnoDB was missing from show engines;. Mystery solved !!!

UPDATE 2011-10-17 11:07 EDT

The error message was deceptive because innodb_log_file_size was smaller than the log files (ib_logfile0 and ib_logfile1), which were 1G at the time. What's interesting is this: Corruption was reported because the file was expected to be 5M and the files were bigger. If the situation were reversed and the innodb log files were smaller than the declared size in my.cnf you should get something like this in the error log:

110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

In this example, the log files were already existing as 5M and the setting for innodb_log_file_size was bigger (in this case, 32M).

For this particular question, I blame MySQL (eh Oracle [still hate saying it]) for the inconsistent error message protocol.

Thesql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

The two answers given from @RickJames and @drogart are essentially the remedies. (+1 for each).

Right from the error log you present, the last two lines say:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728 bytes

InnoDB: than specified in the .cnf file 0 268435456 bytes! `

At that point, it was evident that you set innodb_log_file_size to 256M (268435456) in my.cnf while the InnoDB Transaction Logs (ib_logfile0,ib_logfile1) were respectively 128M (134217728) each. Looking back at the link to my StackOverflow answer in your question, you had to do the following:

Step 01) Add this to my.cnf:

[mysqld]
innodb_buffer_pool_size=4G
innodb_log_file_size=1G

Step 02) Run these command in the OS

mysql -u... -p... -e"SET GLOBAL innodb_fast_shutdown = 1"
service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start

So as to have confidence in what is happening, run tail -f against the error log. You will see message telling you when each innodb log file is being created.