Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-34809

Aria table fails to add secondary index. Works for myisam table.

    XMLWordPrintable

Details

    • Bug
    • Status: Needs Feedback (View Workflow)
    • Major
    • Resolution: Unresolved
    • 11.4.2
    • None
    • Storage Engine - Aria
    • 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

          Activity

            People

              Unassigned Unassigned
              JonBeedle Jonathan Baxter
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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