Mysql – Table doesn’t exist in engine

MySQL

I am getting a weird error in my mysql database:

SELECT * FROM mytable

returns:

ERROR 1932 (42S02): Table 'mydb.mytable' doesn't exist in engine

I can confirm that the tables do indeed exist on local file system and show tables; does indeed return the tables I am interested in.

show tables;

+-----------------+
| Tables_in_mydb  |
+-----------------+
| presets         |
| mytable         |
+-----------------+

And the select:

select * from mytable;

Table 'mydb.mytable' doesn't exist in engine.

Error makes absolute no sense and I've been working with this tables a couple of days ago without any problems. I haven't moved the tables. What should I do?

Before that error above showed up I was getting an error:

Access denied for user 'root'@'localhost' (using password: YES),
but I successfully updated the password. I have no idea what triggered this error because I haven't played with passwords.

However, when I tried to fix this error I have set permissions on /Applications/XAMPP/xamppfiles/var/mysql to read/write to everyone.

I have physical access to .frm and .ibd files of the database. I believe I use InnoDB storage engine.

UPDATE:

show table status gives back a really weird outout:

+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------------------------------------+
| Name            | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment                                              |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------------------------------------+
| preset          | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL        | NULL       | NULL              |     NULL | NULL           | Table 'mydb.preset' doesn't exist in engine          |
| mytable         | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL        | NULL       | NULL              |     NULL | NULL           | Table 'mydb.mytable' doesn't exist in engine         |

UPDATE 2
ls -lh /Applications/XAMPP/xamppfiles/var/mysql/ returns

-rwxrwxr--   1 _mysql  706   135K Jan  4  2017 My-MBP.err
-rwxrwxr--   1 _mysql  706   897K Jan 27 00:46 My-MacBook-Pro.local.err
drwxrwxr-x  33 _mysql  706   1.1K Feb 20  2017 abraham
drwxrwxr-x   7 _mysql  706   238B May 27  2016 accounts
-rwxrwxr--   1 _mysql  706    16K Jan 27 00:46 aria_log.00000001
-rwxrwxr--   1 _mysql  706    52B Jan 27 00:46 aria_log_control
drwxrwxr-x  23 _mysql  706   782B Jun 11  2017 ch7
drwxrwxr-x  31 _mysql  706   1.0K Jun 19  2017 coronel_2
drwxrwxr-x   5 _mysql  706   170B May 29  2017 emails
-rwxrwxr--   1 _mysql  706   5.0M Jan 27 00:46 ib_logfile0
-rwxrwxr--   1 _mysql  706   5.0M Nov 24  2015 ib_logfile1
-rwxrwxr--   1 _mysql  706    74M Jan 27 00:46 ibdata1
drwxrwxr-x   5 _mysql  706   170B Aug 28 17:44 jsonTest
-rwxrwxr--   1 _mysql  706     0B Nov 24  2015 multi-master.info
drwxrwxrwx   9 _mysql  706   306B Jan 26 11:40 mydb
drwxrwxr-x  89 _mysql  706   3.0K Jan 15 11:29 mysql
drwxrwxr-x  13 _mysql  706   442B May 30  2017 payments_ipndb
drwxrwxr-x   3 _mysql  706   102B Jan 15 11:29 performance_schema
drwxrwxr-x  41 _mysql  706   1.4K Jan 15 11:29 phpmyadmin
drwxrwxr-x   2 _mysql  706    68B Jan 15 11:28 test
drwxrwxr-x   5 _mysql  706   170B Jun 11  2017 tracking
drwxrwxr-x   9 _mysql  706   306B May 17  2017 tutorial
drwxrwxr-x   9 _mysql  706   306B Feb 21  2017 users

and ls -lh /Applications/XAMPP/xamppfiles/var/mysql/mydb/ returns

-rw-rw-rw-  1 _mysql  706    65B Sep  4 17:57 db.opt
-rw-rw-rw-  1 _mysql  706   1.8K Sep  8 18:20 preset.frm
-rw-rw-rw-  1 _mysql  706    96K Sep  9 20:53 preset.ibd
-rw-rw-rw-  1 _mysql  706   1.0K Jan 11 16:15 mytable.frm
-rw-rw-rw-  1 _mysql  706   128K Jan 11 21:02 mytable.ibd

.err file contains these messages:

[Warning] InnoDB: Cannot open table mydb/mytable from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

Even with that link, I am still unsure of what to do.

UPDATE:

I can confirm that the error comes from reinstalling XAMPP. I've created a new table, and I could use it normally. Than I reinstalled the xampp, and the newly created table is not accessible anymore, returning the same error as all the other tables: Table doesn't exist in engine.

I also get the same error in error logs:
[Warning] InnoDB: Cannot open table mydb/newlycreatedtable from the internal data dictionary of InnoDB though the .frm file for the table exists.
I still don't know how to solve it.

Best Answer

Disclaimer: Please read the whole answer to the end before you perform any modifications. Please backup everything before you change the permissions and/or owners. You are changing permissions at your own risk

Possible Root Cause

Changing the permissions on the .../var/mysql directory isn't the best idea.

However, when I tried to fix this error I have set permissions on /Applications/XAMPP/xamppfiles/var/mysql to read/write to everyone.

What MySQL Prefers

MySQL prefers the following directory permissions:

0700 (-RWX------)

MySQL prefers the following file permissions:

0660 (-RW-RW----)

By default, MySQL creates database directories with an access permission value of 0700.

...and...

The default UMASK and UMASK_DIR values are 0660 and 0700, respectively.

Reference: B.5.3.1 Problems with File Permissions

Fixing Permissions

To alter the directory and file permissions you would have to set off two commands like this:

Directory Permissions

bash> find /Applications/XAMPP/xamppfiles/var/mysql -type d -exec chmod 0700 {} \;

In the directory /Applications/XAMPP/xamppfiles/var/mysql find all directories and modify the directory permissions to -rwx------

File Permissions

bash> find /Applications/XAMPP/xamppfiles/var/mysql -type f -exec chmod 0660 {} \;

This translates to: In the directory /Applications/XAMPP/xamppfiles/var/mysql find all files and modify the directory permissions to -rw-rw---

Reference: How do I set chmod for a folder and all of its subfolders and files in Linux Ubuntu Terminal? [clsoed]

Owner?

You should have reset everything the way MySQL prefers it, unless you modified the owner of the .../var/mysql sub-directories and files, which should normally belong to the mysql linux user and equally to the mysql group.

In the case that the files and directories no longer belong to mysql mysql then you might have to reset the owner and group using:

bash> chown -R mysql mysql /Applications/XAMPP/xamppfiles/var/mysql

Individual File and Directory

You might want to consider looking at the permissions of that one file.

Final Observations

Looking at your directory listing I noticed that the user is set to _mysql (with underscore) and the group is set to 706. This might be because the linux group mysql has been deleted and the user _mysql changed.

Is the group mysql displayed if you issue the following command:

bash> cat /etc/group

Possible output:

mysql:x:117:

Does the user mysql exist when you type the following command:

bash> cat /etc/passwd

Possible output:

mysql:x:109:117:MySQL Server,,,:/home/mysql:/bin/bash

Note that the mysql user (109) belongs to the mysql group (117), which matches the ID from the cat /etc/group command.

Please Note:
Running the CHMOD and CHOWN commands will only reset the permissions correctly if the user and group exist. If the user and group exist, then you might be able to reset the permissions and owners to the correct values.

Last Resort

If all else fails you might want to consider re-installing your whole MySQL environment.

Last Resort 2

After reading the newest comments and updates to question there is a possible other solution

Reinstalling the XAMPP stack apparently does not remove all previous *.frm files containing your previously created tables.

However, reinstalling the XAMPPP stack will not allow you to access the previously accessible objects. This is because the files/tables are no longer referenced in the system catalogues. This is why you are receiving an error message when you try to select from your tables.

You might achieve a partial restore of the data and/or table definitions with the MySQL Utilities 1.5 which contain the following tool:

5.10 mysqlfrm — File reader for .frm files.

The tools is documented as follows:

...
A diagnostic mode is available by using the --diagnostic option. This switches the utility to read the .frm files byte-by-byte to recover as much information as possible. The diagnostic mode has additional limitations in that it cannot decipher character set or collation values without using an existing server installation specified with either the --server or --basedir option. This can also affect the size of the columns if the table uses multibyte characters. Use this mode when the default mode cannot read the file, or if a MySQL server is not installed on the host.
...

This might be your last resort to retrieving the information from your *.frm files in your broken MySQL instance.

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.

Mysql – InnoDB tables inaccessible after reboot

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.