[MDEV-18041] Database corruption after renaming a prefix-indexed column Created: 2018-12-20  Updated: 2019-01-08  Resolved: 2018-12-29

Status: Closed
Project: MariaDB Server
Component/s: Server, Storage Engine - InnoDB
Affects Version/s: 10.2.19, 10.2, 10.3, 10.4
Fix Version/s: 10.4.2, 10.2.21, 10.3.12

Type: Bug Priority: Blocker
Reporter: Ralf Haferkamp Assignee: Eugene Kosov (Inactive)
Resolution: Fixed Votes: 0
Labels: regression
Environment:

openSUSE Leap 15, SLES15, SLES12


Issue Links:
Problem/Incident
is caused by MDEV-13671 InnoDB should use case-insensitive co... Closed

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



 Comments   
Comment by Elena Stepanova [ 2018-12-28 ]

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

Comment by Marko Mäkelä [ 2018-12-29 ]

This seems to be a regression caused by the merge of the MDEV-13671 fix.

Comment by Marko Mäkelä [ 2018-12-29 ]

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

Comment by Eugene Kosov (Inactive) [ 2018-12-29 ]

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

Comment by Niko Wittenbeck [ 2019-01-08 ]

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.

Generated at Thu Feb 08 08:41:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.