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-column/
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-column/
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
