[MDEV-19419] ALTER TABLE ... CHANGE COLUMN causing "ERROR 1034 (HY000) at line 41: Index for table 'xyz' is corrupt" with many rows Created: 2019-05-08  Updated: 2019-06-17  Resolved: 2019-06-17

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.2.12
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Adam Hardy Assignee: Elena Stepanova
Resolution: Incomplete Votes: 0
Labels: need_feedback
Environment:

Amazon RDS


 Description   

I have 3 databases: prod, test and dev for this app. The databases are on separate instances.

I run a mysqldump from prod to test or dev, dropping the logical database on the target first.

I then run a script to modify the schema like this:

-- add username to modification via userid
 
ALTER TABLE modification ADD COLUMN username CHAR(30);
 
UPDATE modification m SET m.username = (
 
    SELECT u.name FROM `user` u WHERE u.id = m.userId);
 
ALTER TABLE modification CHANGE COLUMN username username CHAR(30)
 
    NOT NULL DEFAULT '';

The last statement breaks with the error:

"ERROR 1034 (HY000) at line 41: Index for table 'xyz' is corrupt"

There are 100 million+ rows in the table. If I run the SQL on a table with less than 1000 rows, the bug doesn't present itself.

This is what the table schema looks like before the change:

CREATE TABLE `modification` (
 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  `userId` int(10) unsigned NOT NULL,
 
  `epochTime` bigint(20) NOT NULL,
 
  `forecastId` int(10) unsigned NOT NULL,
 
  `description` char(12) COLLATE utf8mb4_unicode_ci NOT NULL,
 
  `auxText` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `auxDate` date DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `ix_modification_query_by_user_and_forecast` 
      (`userId`,`forecastId`,`description`,`auxDate`,`epochTime`),
 
  KEY `ix_modification_last_save_query`
 
      (`forecastId`,`description`,`auxDate`),
 
  CONSTRAINT `fk_modification_forecastId` 
      FOREIGN KEY (`forecastId`) REFERENCES `forecast` (`id`),
 
  CONSTRAINT `fk_modification_userId` 
      FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
 
) ENGINE=InnoDB 
  AUTO_INCREMENT=92397081 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci 
  MAX_ROWS=12345678;

I tried to get help on dba.stackexchange.com, where I had some help defining the issue: https://dba.stackexchange.com/questions/228546/mariadb-error-1034-hy000-at-line-41-index-for-table-xyz-is-corrupt-on-imp

 Comments   
Comment by Elena Stepanova [ 2019-05-16 ]

I cannot reproduce it right away. Please note that 10.2.12 is 12 releases behind and almost 1.5 years old; if the problem is reproducible for you, could you please try the newest 10.2 to see if it's still there?
Please also run checks on the table to make sure it's healthy before performing the problematic ALTER.

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