Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.2.12
-
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