ERROR 1932 on a table with recently added VIRTUAL column
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.)
Answer Answered by Ian Gilfillan in this comment.
Virtual columns should not be unstable - they've been present since MariaDB 5.2. You are using MariaDB 10.1.0 though, which was an alpha version, and definitely not recommended for production use. The latest 10.1 release is currently 10.1.6 (in beta), and the latest stable release is 10.0.20, so try upgrading to a more stable release. If the problem persists, you can report it as a bug.
MySQL does not have the virtual columns feature in its stable releases, although it's recently added it to its milestone release.