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

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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Incomplete
    • 11.4.2
    • N/A
    • 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

            alice Alice Sherepa added a comment -

            Could you please try 11.4.3 and check if the problem is resolved there? (probably a duplicate of MDEV-34522)

            alice Alice Sherepa added a comment - Could you please try 11.4.3 and check if the problem is resolved there? (probably a duplicate of MDEV-34522 )

            Difficult for me to do an out-of-band update for mariadb unfortunately. I can update to 11.4.3 when OpenSUSE does. I'll provide feedback then.

            In the meantime I have temporarily switched a couple of tables to myisam for this particular application.

            JonBeedle Jonathan Baxter added a comment - Difficult for me to do an out-of-band update for mariadb unfortunately. I can update to 11.4.3 when OpenSUSE does. I'll provide feedback then. In the meantime I have temporarily switched a couple of tables to myisam for this particular application.

            Did the upgrade help? It seems that openSUSE Tumbleweed now has MariaDB 11.5.2, which should include all fixes of 11.4.3

            serg Sergei Golubchik added a comment - Did the upgrade help? It seems that openSUSE Tumbleweed now has MariaDB 11.5.2, which should include all fixes of 11.4.3

            People

              Unassigned Unassigned
              JonBeedle Jonathan Baxter
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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