[MDEV-18041] Database corruption after renaming a prefix-indexed column - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 10.2.19, 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.4.2, 10.2.21, 10.3.12
Component/s: Server, Storage Engine - InnoDB
Labels:
- regression
Environment:
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

MDEV-13671 InnoDB should use case-insensitive column name comparisons like the rest of the server

Closed

Activity

Ascending order - Click to sort in descending order

Ralf Haferkamp created issue - 2018-12-20 09:33

Elena Stepanova added a comment - 2018-12-28 19:55

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

Elena Stepanova added a comment - 2018-12-28 19:55 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

Elena Stepanova made changes - 2018-12-28 19:55

Field	Original Value	New Value
Component/s		Storage Engine - InnoDB [ 10129 ]
Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]
Assignee		Marko Mäkelä [ marko ]
Labels		regression
Priority	Major [ 3 ]	Blocker [ 1 ]

Elena Stepanova made changes - 2018-12-28 19:55

Status

Open [ 1 ]

Confirmed [ 10101 ]

Marko Mäkelä added a comment - 2018-12-29 09:39

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

Marko Mäkelä added a comment - 2018-12-29 09:39 This seems to be a regression caused by the merge of the MDEV-13671 fix.

Marko Mäkelä made changes - 2018-12-29 09:39

Link

This issue is caused by ~~MDEV-13671~~ [ ~~MDEV-13671~~ ]

Marko Mäkelä added a comment - 2018-12-29 09:49

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

Marko Mäkelä added a comment - 2018-12-29 09:49 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.)

Marko Mäkelä made changes - 2018-12-29 09:49

Assignee

Marko Mäkelä [ marko ]

Eugene Kosov [ kevg ]

Eugene Kosov (Inactive) added a comment - 2018-12-29 17:53

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

Eugene Kosov (Inactive) added a comment - 2018-12-29 17:53 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

Eugene Kosov (Inactive) made changes - 2018-12-29 17:54

Assignee	Eugene Kosov [ kevg ]	Marko Mäkelä [ marko ]
Status	Confirmed [ 10101 ]	In Review [ 10002 ]

Marko Mäkelä made changes - 2018-12-29 20:56

Summary

Database corruption after altering column name

Database corruption after renaming a prefix-indexed column

Marko Mäkelä made changes - 2018-12-29 21:30

issue.field.resolutiondate

2018-12-29 21:30:32.0

2018-12-29 21:30:32.367

Marko Mäkelä made changes - 2018-12-29 21:30

Fix Version/s		10.4.2 [ 23229 ]
Fix Version/s		10.2.21 [ 23213 ]
Fix Version/s		10.3.12 [ 23214 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Assignee	Marko Mäkelä [ marko ]	Eugene Kosov [ kevg ]
Resolution		Fixed [ 1 ]
Status	In Review [ 10002 ]	Closed [ 6 ]

Niko Wittenbeck added a comment - 2019-01-08 14:58

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.

Niko Wittenbeck added a comment - 2019-01-08 14:58 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.

Sergei Golubchik made changes - 2021-12-06 21:48

Workflow

MariaDB v3 [ 91327 ]

MariaDB v4 [ 155383 ]

People

Assignee:: Eugene Kosov (Inactive)

Reporter:: Ralf Haferkamp

Votes:: 0 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2018-12-20 09:33

Updated:: 2019-01-08 14:58

Resolved:: 2018-12-29 21:30

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server