Details
-
Bug
-
Status: Needs Feedback (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4.2
-
None
-
None
-
openSUSE Tumbleweed 20240705
Linux 6.9.7-1-default #1 SMP PREEMPT_DYNAMIC x86_64
Description
Cannot create a secondary index on an aria table via alter table. Works if I change the engine to myisam.
Broken aria index creation via alter table:
MariaDB> create table t1 (c1 bigint not null, c2 bigint not null, c3 int, c4 varchar(255), primary key(c1,c2)) engine=aria;
Query OK, 0 rows affected (0.055 sec)
MariaDB> insert into t1 select * from <redacted>
Query OK, 53442668 rows affected (3 min 22.054 sec)
Records: 53442668 Duplicates: 0 Warnings: 0
MariaDB> alter table t1 add index(c3);
ERROR 1034 (HY000): Key 2 - Found too many records; Can't continue
Working myisam index creation via alter table:
MariaDB> create table t2 (c1 bigint not null, c2 bigint not null, c3 int, c4 varchar(255), primary key(c1,c2)) engine=myisam;
Query OK, 0 rows affected (0.030 sec)
MariaDB> insert into t2 select * from <redacted>;
Query OK, 53442668 rows affected (2 min 18.984 sec)
Records: 53442668 Duplicates: 0 Warnings: 0
MariaDB> alter table t2 add index(c3);
Query OK, 53442668 rows affected (3 min 0.335 sec)
Records: 53442668 Duplicates: 0 Warnings: 0
Inconsistent aria table state if created with index.
If I create the table as aria with the secondary index, and then insert the data, the insert succeeds but the table is left in a very odd and inconsistent state:
MariaDB> create table t3 (c1 bigint not null, c2 bigint not null, c3 int, c4 varchar(255), primary key(c1,c2), index(c3)) engine=aria;
Query OK, 0 rows affected (0.071 sec)
MariaDB> insert into t3 select * from t2;
Query OK, 53442668 rows affected, 1 warning (3 min 43.108 sec)
Records: 53442668 Duplicates: 0 Warnings: 1
MariaDB> show warnings;
+--------+----+------------------------------------------------+
| Level | Code | Message |
+--------+----+------------------------------------------------+
| Warning | 1034 | Number of rows changed from 53442668 to 5651550 |
+--------+----+------------------------------------------------+
MariaDB> select count from t3;
+----------+
| count |
+----------+
| 5651550 |
+----------+
So, there's only 5,651,550 rows in t3. Or is there?
MariaDB> select count(distinct(c1)) from t3;
+---------------------+
| count(distinct(c1)) |
+---------------------+
| 11673399 |
+---------------------+
There can't be 11,673,399 distinct values of c1 if there is only 5,651,550 rows in the table. In fact, if I count the rows in common between t1 and t3 (based on primary key), they're the same: 53,442,668
Attachments
Issue Links
- duplicates
-
MDEV-34522 Index for (specific) Aria table is created as corrupted
- Closed