Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.2.19, 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
openSUSE Leap 15, SLES15, SLES12
Description
It seems that under certain conditions a simple "alter table" statement to rename a column can cause the database to go bad. This is what I do to reproduce the issue:
(Basically creating a table with the primary key on two columns, after that renaming one of the columns)
-------------------
create database test42;
use test42;
create table `test` ( `test_old` varchar(255) NOT NULL,
`other` varchar(255) NOT NULL,
PRIMARY KEY (`test_old`,`other`) );
alter table `test` CHANGE COLUMN `test_old` `test_new` varchar(255) NOT NULL;
select * from test;
-------------------
Up to here it works fine. Now after I restart the server (systemctl restart mariadb). I get this after rerunning the select statement.
---------------------
select * from test;
ERROR 1932 (42S02): Table 'test42.test' doesn't exist in engine
---------------------
So something went wrong with that "test" table.
BTW, the above is just a minimized test case, we orginally noticed this issue when trying to use 10.2.19 with openstack-neutron (which needs to run schemamigrations similar to the above).
Attachments
Issue Links
- is caused by
-
-
- Closed
-
Activity
Yes it is a regression after MDEV-13671. And yes it's related to updating of INFORMATION_SCHEMA.INNODB_SYS_FIELDS
My fix is here https://github.com/MariaDB/server/pull/1063
The problem seems to be that both PRIMARY KEY columns (not only test_old, but also other) will be renamed to test_new in INFORMATION_SCHEMA.INNODB_SYS_FIELDS. This causes the following message to be emitted into the error log:
2018-12-29 11:41:17 140736989312768 [ERROR] InnoDB: No matching column for `test_new` in index `PRIMARY` of table `test`.`test`
I do not immediately see why this would not happen in 10.0 or 10.1. (There have not been any 10.0 or 10.1 releases with this change yet.)
This seems to be a regression caused by the merge of the MDEV-13671 fix.
Thanks for the report and test case, reproducible as described.
Here is an MTR version of the same:
--source include/have_innodb.inc
|
|
|
create table `test` ( |
`test_old` varchar(255) NOT NULL, |
`other` varchar(255) NOT NULL, |
PRIMARY KEY (`test_old`,`other`) |
) ENGINE=InnoDB;
|
|
|
alter table `test` CHANGE COLUMN `test_old` `test_new` varchar(255) NOT NULL; |
show create table `test`; |
select * from test; |
|
|
--source include/restart_mysqld.inc
|
|
|
select * from test; |
|
|
# Cleanup
|
drop table `test`; |
Reproducible on 10.2+, not reproducible on 10.1. However, it seems to have appeared in 10.2 tree with this merge:
commit 32062cc61cd00e4cd3b7939c8a09f9c3ac34ec76
|
Merge: af9649c bae21bf
|
Author: Marko Mäkelä <marko.makela@mariadb.com>
|
Date: Tue Nov 6 08:41:48 2018 +0200
|
|
|
Merge 10.1 into 10.2
|
Is there a way to directly update the INNODB_SYS_FIELDS table and to fix the columns manually (root lacks access)? Fixing that manually with table/db recreation is quite time-consuming.