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

            JonBeedle Jonathan Baxter created issue -
            alice Alice Sherepa made changes -
            Field Original Value New Value
            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 )
            alice Alice Sherepa made changes -
            Status Open [ 1 ] Needs Feedback [ 10501 ]

            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.
            alice Alice Sherepa made changes -
            Status Needs Feedback [ 10501 ] Open [ 1 ]
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] Needs Feedback [ 10501 ]
            serg Sergei Golubchik made changes -
            Status Needs Feedback [ 10501 ] Open [ 1 ]
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] Needs Feedback [ 10501 ]

            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
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s N/A [ 14700 ]
            Resolution Incomplete [ 4 ]
            Status Needs Feedback [ 10501 ] Closed [ 6 ]

            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.