Hello,
First drop the table in mysql database and then create the table.
mysql> use mysql;
database changed
mysql> CREATE TABLE `innodb_index_stats` (
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`index_name` varchar(64) COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
`stat_value` bigint(20) unsigned NOT NULL,
`sample_size` bigint(20) unsigned DEFAULT NULL,
`stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
This will create «innodb_index_stats» table.
——————————
Martin N
Linux Support Engineer.
Nixtree Solutions
Managed Full Server Backups
https://www.nixtree.com/managed-backups.php
Follow us on : https://twitter.com/nixtree
Using version «10.1.0-MariaDB-log — Source distribution» on MacOS X Server 10.6.8 via phpMyAdmin 4.1.8 using raw SQL, I recently used ALTER TABLE to add a VIRTUAL column to a table that was the simple difference between two columns: «ALTER TABLE s_vehicle_log ADD COLUMN v_distance INT(5) AS (odometer — begin) AFTER odometer».
This first appeared to work; I could see the new column in phpMyAdmin and it appeared to have the proper values. But within an hour, I could no longer access that table.
Going into the mysql CLI, executing «SELECT count(*) FROM s_vehicle_log;» yields «ERROR 1932 (42S02): Table ‘EcoReality.s_vehicle_log’ doesn’t exist in engine» (I do the same on other tables in the same database successfully.)
Going into the shell (bash), I can see the proper files, and they have the same perms as other tables that work properly:
# ls -l s_vehicle_* -rw-rw---- 1 _mysql _mysql 1264 Feb 12 18:40 s_vehicle_cost.frm -rw-rw---- 1 _mysql _mysql 98304 Feb 12 18:40 s_vehicle_cost.ibd -rw-rw---- 1 _mysql _mysql 3796 Jul 15 15:56 s_vehicle_log.frm -rw-rw---- 1 _mysql _mysql 688128 Jul 15 15:56 s_vehicle_log.ibd -rw-rw---- 1 _mysql _mysql 2583 Feb 12 18:40 s_vehicle_monthly_query.frm
It is interesting that, though ERROR 1932 says the table doesn’t exist, phpMyAdmin shows it in the list of tables, showing «in use» as its table type:
s_vehicle_cost Browse Browse Structure Structure Search Search Insert Insert Empty Empty Drop Drop ~11 InnoDB latin1_swedish_ci 16 KiB -
s_vehicle_log Browse Browse Structure Structure Search Search Insert Insert Empty Empty Drop Drop in use
mysql SHOW TABLE STATUS isn't very helpful, either:
MariaDB [EcoReality]> show table status like 's_vehicle_%'
-> ;
+-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+----------------------------------------------------------+
| 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 |
+-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+----------------------------------------------------------+
| s_vehicle_cost | InnoDB | 10 | Compact | 11 | 1489 | 16384 | 0 | 0 | 0 | NULL | 2015-02-12 18:40:32 | NULL | NULL | latin1_swedish_ci | NULL | | Cost per km of vehicles over time. |
| s_vehicle_log | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Table 'EcoReality.s_vehicle_log' doesn't exist in engine |
| s_vehicle_monthly_query | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Table 'EcoReality.s_vehicle_log' doesn't exist in engine |
+-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+----------------------------------------------------------+
3 rows in set, 2 warnings (0.00 sec)
Looking at the most recent file that seems to be a binlog reveals nothing of interest; just phpMyAdmin logging queries about the damaged table:
# mysqlbinlog ../mysql-bin.000127 | fgrep s_vehicle_log
REPLACE INTO `phpmyadmin`.`pma__table_uiprefs` VALUES ('root', 'EcoReality', 's_vehicle_log', '[]', NULL)
REPLACE INTO `phpmyadmin`.`pma__recent` (`username`, `tables`) VALUES ('root', '[{"db":"EcoReality","table":"s_where_heard"},{"db":"EcoReality","table":"s_product_sales_log"},{"db":"EcoReality","table":"s_animal_notes"},{"db":"EcoReality","table":"s_product_harvest"},{"db":"EcoReality","table":"s_timelog"},{"db":"EcoReality","table":"s_project"},{"db":"EcoReality","table":"s_product"},{"db":"Personal","table":"Weight"},{"db":"EcoReality","table":"s_vehicle_log"},{"db":"EcoReality","table":"s_shares"}]')
REPLACE INTO `phpmyadmin`.`pma__recent` (`username`, `tables`) VALUES ('root', '[{"db":"EcoReality","table":"s_timelog"},{"db":"EcoReality","table":"s_where_heard"},{"db":"EcoReality","table":"s_product_sales_log"},{"db":"EcoReality","table":"s_animal_notes"},{"db":"EcoReality","table":"s_product_harvest"},{"db":"EcoReality","table":"s_project"},{"db":"EcoReality","table":"s_product"},{"db":"Personal","table":"Weight"},{"db":"EcoReality","table":"s_vehicle_log"},{"db":"EcoReality","table":"s_shares"}]')
REPLACE INTO `phpmyadmin`.`pma__recent` (`username`, `tables`) VALUES ('root', '[{"db":"EcoReality","table":"s_product_sales_log"},{"db":"EcoReality","table":"s_timelog"},{"db":"EcoReality","table":"s_where_heard"},{"db":"EcoReality","table":"s_animal_notes"},{"db":"EcoReality","table":"s_product_harvest"},{"db":"EcoReality","table":"s_project"},{"db":"EcoReality","table":"s_product"},{"db":"Personal","table":"Weight"},{"db":"EcoReality","table":"s_vehicle_log"},{"db":"EcoReality","table":"s_shares"}]')
repair table s_vehicle_log
REPLACE INTO `phpmyadmin`.`pma__recent` (`username`, `tables`) VALUES ('root', '[{"db":"EcoReality","table":"s_timelog"},{"db":"EcoReality","table":"s_product_sales_log"},{"db":"EcoReality","table":"s_where_heard"},{"db":"EcoReality","table":"s_animal_notes"},{"db":"EcoReality","table":"s_product_harvest"},{"db":"EcoReality","table":"s_project"},{"db":"EcoReality","table":"s_product"},{"db":"Personal","table":"Weight"},{"db":"EcoReality","table":"s_vehicle_log"},{"db":"EcoReality","table":"s_shares"}]')
REPLACE INTO `phpmyadmin`.`pma__recent` (`username`, `tables`) VALUES ('root', '[{"db":"EcoReality","table":"s_vehicle"},{"db":"EcoReality","table":"s_timelog"},{"db":"EcoReality","table":"s_product_sales_log"},{"db":"EcoReality","table":"s_where_heard"},{"db":"EcoReality","table":"s_animal_notes"},{"db":"EcoReality","table":"s_product_harvest"},{"db":"EcoReality","table":"s_project"},{"db":"EcoReality","table":"s_product"},{"db":"Personal","table":"Weight"},{"db":"EcoReality","table":"s_vehicle_log"}]')
----------------
Anyone know what’s going on, and what to do about it? (Besides «restore from backup,» which is a bit old.)
Question: When attempting a mysqldump on one of the databases on a MariaDB server with this command I received the error message mysqldump: Got error: 1932: «Table ‘schema1.myTable’ doesn’t exist in engine» when using LOCK TABLES
mysqldump -u myun -p database_name > /mariadb/backups/mydb1171117.sql
Description: Backup of [~myDB1~] MySQL database failed. The error message [~mysqldump: Couldn’t execute ‘show create table `my_code`’: Table ‘mydb1.my_code’ doesn’t exist in engine (1932) ~] is received during backup.
Source: myserver1, Process: MySqlBackupChil
How can I fix? In summary — some files were moved around and this has caused the issue
Answer:I’ve found this error message appears when files have been moved around incorrectly.
Test 1 — Try a native MariaDB backup and see if the same error occurs
mysqldump -u myun -p database_name > /mariadb/backups/mydb1171117.sql
mysqldump: Got error: 1932: «Table ‘myschema.mytable’ doesn’t exist in engine» when using LOCK TABLES
Test 2 — check to see if table exists
use db;
show tables;
Yes it appears in list . The reason «show tables;» works is because mysqld will scan the database directory for .frm files only. As long as they exist, it sees a table definition.
Test 3 — Tried --skip-lock-tables parameter
Try to use --skip-lock-tables parameter with mysqldump to skip lock tables, like in the example below:
mysqldump --skip-lock-tables -u myun -p database_name > /mariadb/backups/mydb1171117.sql
mysqldump: Couldn’t execute ‘show create table `mytable`’: Table ‘myschema.mytable’ doesn’t exist in engine (1932)
Test 4 — Check permissions — it should be mysql both group and owner
grep datadir /etc/my.cnf
ls -la /my_data_dir/mysql/data
Test 5 — If problem persists — try a TABLE REPAIR
mysqlcheck —repair mydb -uroot -p
status : Operation failed
myschema.myTable
Error : Table ‘myschema.myTable’ doesn’t exist in engine
The tests above are a range of tests to establish some basic checkouts. Mainly this error occurs when these situations exist
1) InnoDB tablespace was deleted and recreated but related InnoDB table.frm files from the db directory were not removed.Another option is .frm files were moved to a different database
2)Incorrect permissions and ownership on table’s files in MySQL data directory — check Test 4 above
3) MariaDB table data is corrupted
!
Author: Rambler (http://www.dba-ninja.com)
Share: