This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-16. For the latest, interactive version please visit https://mariadb.com/kb/.

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.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.