[Maria-discuss] New Question: ERROR 1932 on a table with recently added VIRTUAL column

16 Jul 2015

      Hello,

A new question has been asked in "MariaDB Community" by Bytesmiths. Please answer it at http://mariadb.com/kb/en/error-1932-on-a-table-with-recently-added-virtual-c... as the person asking the question may not be subscribed to the mailing list.

--------------------------------
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.)
--------------------------------

To view or answer this question please visit: http://mariadb.com/kb/en/error-1932-on-a-table-with-recently-added-virtual-c...

[Maria-discuss] New Question: ERROR 1932 on a table with recently added VIRTUAL column

AskMonty KB