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.
SHOW TABLE STATUS;
ibdata1
file in /Applications/.../xamppfiles/var/mysqlmysql --version: mysql Ver 15.1 Distrib 10.1.29-MariaDB, for osx10.6 (i386) using EditLine wrapper
. I've updated XAMPP, but I am not sure if the error started happening at the same time as XAPP was updated.SELECT support FROM information_schema.engines WHERE engine='InnoDB';
. What is the result ???