InnoDB tables inaccessible after reboot

Question

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.

Answer 1

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.