Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.4
-
None
-
Windows 10 (22H2) & Windows Server 2022 (21H2)
Description
I have a table with the following create statement (from MariaDB 10.4.11):
CREATE TABLE `errortbl` (
|
`id` INT(10) NOT NULL AUTO_INCREMENT,
|
`FldPK` VARCHAR(50) NOT NULL COLLATE 'utf8_bin',
|
`Fld01` VARCHAR(6) NOT NULL COLLATE 'utf8_bin',
|
`Fld02` VARCHAR(200) NOT NULL COLLATE 'utf8mb4_bin',
|
`Fld03` SMALLINT(6) NULL DEFAULT NULL,
|
`Fld04` VARCHAR(25) NULL DEFAULT NULL COLLATE 'utf8_bin',
|
`Fld05` TINYINT(4) NULL DEFAULT NULL,
|
`Fld06` TINYINT(1) NULL DEFAULT NULL,
|
`Fld07` DATETIME(3) NULL DEFAULT NULL,
|
`Fld08` VARCHAR(15) NULL DEFAULT NULL COLLATE 'utf8_bin',
|
`Fld09` CHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',
|
`Fld10` CHAR(10) NULL DEFAULT NULL COLLATE 'utf8_bin',
|
`Fld11` VARCHAR(15) NULL DEFAULT NULL COLLATE 'utf8_bin',
|
`Fld12` DATETIME(3) NULL DEFAULT NULL,
|
`Fld13` DATETIME(3) NULL DEFAULT NULL,
|
`Fld14` CHAR(3) NOT NULL DEFAULT '8' COLLATE 'utf8_bin',
|
`Fld15` DATETIME(3) NULL DEFAULT NULL,
|
`Fld16` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',
|
`Fld17` INT(11) NULL DEFAULT NULL,
|
PRIMARY KEY (`id`) USING BTREE,
|
INDEX `Idx01` (`Fld01`, `Fld17`, `Fld03`, `Fld05`, `FldPK`) USING BTREE,
|
INDEX `Idx02` (`Fld15`),
|
INDEX `Idx03` (`FldPK`),
|
INDEX `Idx04` (`Fld12`),
|
INDEX `Idx05` (`Fld01`, `Fld15`, `Fld02`) USING BTREE,
|
INDEX `Idx06` (`Fld01`, `Fld02`, `Fld15`) USING BTREE,
|
INDEX `Idx07` (`Fld10`, `Fld09`, `Fld15`) USING BTREE,
|
INDEX `Idx08` (`Fld01`, `Fld06`, `Fld15`, `Fld07`, `Fld02`) USING BTREE,
|
INDEX `Idx09` (`Fld07`),
|
INDEX `Idx10` (`Fld08`, `Fld09`) USING BTREE,
|
INDEX `Idx11` (`Fld15`, `Fld01`, `Fld04`, `Fld02`) USING BTREE
|
)
|
COLLATE='utf8_bin'
|
ENGINE=Aria
|
ROW_FORMAT=
|
AUTO_INCREMENT=13934577
|
;
|
This table was working fine in MariaDB 10.4 and 10.5, but after upgrading to 10.11.3 the table begun to crash regularly, with regular REPAIR TABLE. But after starting to dig into the issue it turned out that a single UPDATE/INSERT/DELETE will crash the index file.
The utf8_bin are changed to utf8mb3_bin when it was moved to version 10.11.3.
Here is how to crash the table:
Database changed
|
MariaDB [mydb]> check table errortbl quick;
|
+-----------------+-------+----------+------------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+-----------------+-------+----------+------------------------------------------+
|
| mydb.errortbl | check | warning | Table is marked as crashed |
|
| mydb.errortbl | check | error | Key in wrong position at page 2400059392 |
|
| mydb.errortbl | check | error | Corrupt |
|
+-----------------+-------+----------+------------------------------------------+
|
3 rows in set (46.750 sec)
|
|
|
MariaDB [mydb]> repair table errortbl quick;
|
+-----------------+--------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+-----------------+--------+----------+----------+
|
| mydb.errortbl | repair | status | OK |
|
+-----------------+--------+----------+----------+
|
1 row in set (1 hour 7 min 57.692 sec)
|
|
|
MariaDB [mydb]> check table errortbl quick;
|
+-----------------+-------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+-----------------+-------+----------+----------+
|
| mydb.errortbl | check | status | OK |
|
+-----------------+-------+----------+----------+
|
1 row in set (1 min 3.214 sec)
|
|
|
MariaDB [mydb]> select count(*) from errortbl;
|
+----------+
|
| count(*) |
|
+----------+
|
| 13913039 |
|
+----------+
|
1 row in set (0.531 sec)
|
|
|
MariaDB [mydb]> update errortbl set Fld15 = now() where id = 13654623 ;
|
ERROR 126 (HY000): Got error '126 "Index is corrupted"' for '.\mydb\errortbl.MAI'
|
|
|
MariaDB [mydb]> check table errortbl quick;
|
+-----------------+-------+----------+------------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+-----------------+-------+----------+------------------------------------------+
|
| mydb.errortbl | check | warning | Table is marked as crashed |
|
| mydb.errortbl | check | error | Key in wrong position at page 2400059392 |
|
| mydb.errortbl | check | error | Corrupt |
|
+-----------------+-------+----------+------------------------------------------+
|
3 rows in set (43.390 sec)
|
I've tried everything I can think of to make the index not to crash.
- Tested the table on two different computers.
- Upgraded MariaDB to 10.11.4.
- Changed all collations to utf8bm4_bin.
- Run REPAIR TABLE EXTENDED.
- Created a new table copy and did INSERT INTO newtable SELECT * FROM oldtable.
- And much more...
The only two things that I could find that are working are:
- Move the crashing table to a server with version 10.4/10.5.
- Alter the table to use InnoDB.
The table currently has 13 million rows and I do not know if there is an issue with one of the rows or index, and each repair takes about an hour (on a virtual test server with really slow disks). So it takes time to find.
The Fld02 has a lot of unusual characters, so it might be a clue.
I cannot find anything in the hostname.err that gives a clue.
I'm currently altered the table to use InnoDB for our production server to not crash the table.
I will try and continue to troubleshoot this and try to find more details and a proper test case. Any hints to what to look for (and how) are appreciated.
Attachments
Issue Links
- relates to
-
-
- Open
-