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

Error 1034 "number of rows changed from x to y" on modify column type

    XMLWordPrintable

Details

    Description

      I create a table with 576 columns (don't ask...):

      create table `MyTable` (
        `Id` int not null,
        -- Rest of table def...
        primary key (`Id`)
      ) engine=Aria default charset=utf8mb4 collate=utf8mb4_bin row_format=fixed;
      

      Then modify:

      alter table `MyTable`
      modify `Id` varchar(9) not null;
      

      Then load data from two files, where the second one updates a small subset of the rows of the first one:

      -- 1837285 rows.
      load data infile 'MyData1.csv'
      into table `MyTable`
      character set utf8
      fields terminated by '\t'
      enclosed by ''
      lines terminated by '\r\n';
       
      -- 433 rows.
      load data infile 'MyData2.csv'
      replace into table `MyTable`
      character set utf8
      fields terminated by '\t'
      enclosed by ''
      lines terminated by '\r\n';
      

      Finally, I modify the Id column back to int:

      update `MyTable`
      set `Id` = substring(`Id` from 2); -- Remove non-numeric prefix
       
      alter table `MyTable`
      modify `Id` int not null;
      

      At the end of the last alter, I get error 1034 with message "Number of rows changed from 1837285 to 153094".

      With the current data files I can consistently reproduce this error.

      I've been running this script monthly for years without problem, but a couple of days ago I upgraded MariaDB from 10.11.6 to 10.11.8, so I suspect that this is caused by some bug introduced between those two versions.

      I tested the script with last month's csv files, which were successfully imported with MariaDB version 10.11.6. This gives the same error (but with slightly different rows counts, of course). This seems to further indicate that it's a bug, and not the data itself that has a problem, since those old files were successfully imported in MariaDB version 10.11.6.

      For legal reasons I am unable to share the data files, but I would be able to run the operations with extra debug info or whatever might help you pinpoint what goes wrong.

      I tried the operation with just the Id column, but then it all worked just fine.

      Attachments

        1. DataSample.csv
          3 kB
          Kjell Rilbe
        2. MyTable.sql
          18 kB
          Kjell Rilbe

        Issue Links

          Activity

            People

              Unassigned Unassigned
              krilbe Kjell Rilbe
              Votes:
              1 Vote for this issue
              Watchers:
              4 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.