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

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

        Issue Links

          Activity

            alice Alice Sherepa added a comment -

            krilbe Is it possible for you to try 10.11.9 (which includes MDEV-34522, that seems to be the same problem)

            alice Alice Sherepa added a comment - krilbe Is it possible for you to try 10.11.9 (which includes MDEV-34522 , that seems to be the same problem)
            krilbe Kjell Rilbe added a comment -

            @Alice Sherepa, The problem is on a production server... I suppose I could test on my dev machine and clone the database in question there, but it would be lot of work.

            Can you give some kind of confidence indication for the bug to actually be fixed?

            Eve so, it will probably take a few weeks before I can find time off hours to do the test (or work on my dev machine which would also require me to first test with the faulty version to make sure I can reproduce the error there).

            krilbe Kjell Rilbe added a comment - @Alice Sherepa, The problem is on a production server... I suppose I could test on my dev machine and clone the database in question there, but it would be lot of work. Can you give some kind of confidence indication for the bug to actually be fixed? Eve so, it will probably take a few weeks before I can find time off hours to do the test (or work on my dev machine which would also require me to first test with the faulty version to make sure I can reproduce the error there).
            alice Alice Sherepa added a comment -

            I haven't repeated it exactly with your case, so could not be 100% sure.

            MDEV-34522 is fixed, a regression in 10.11.8, affecting Aria tables with ROW_FORMAT=PAGE where the data file is over 4G with more than one index.
            The effect is that ALTER TABLE, OPTIMIZE TABLE or REPAIR TABLE will fail on these tables.

            Your MyTable description has row_format=fixed, but then some columns are of text type, so fixed could not be used there, so Aria switched to row_format=page

            90% chance it is the same bug as yours, but of course, there is a small possibility that there is some slight variation of that, which is still unfixed, and in that case, it will need further investigation.

            alice Alice Sherepa added a comment - I haven't repeated it exactly with your case, so could not be 100% sure. MDEV-34522 is fixed, a regression in 10.11.8, affecting Aria tables with ROW_FORMAT=PAGE where the data file is over 4G with more than one index. The effect is that ALTER TABLE, OPTIMIZE TABLE or REPAIR TABLE will fail on these tables. Your MyTable description has row_format=fixed, but then some columns are of text type, so fixed could not be used there, so Aria switched to row_format=page 90% chance it is the same bug as yours, but of course, there is a small possibility that there is some slight variation of that, which is still unfixed, and in that case, it will need further investigation.
            krilbe Kjell Rilbe added a comment - - edited

            @alice OK, makes sense. I will try to test it eventuelly, but don't hold your breath. Sorry I can't be more helpful than that. :-I

            krilbe Kjell Rilbe added a comment - - edited @alice OK, makes sense. I will try to test it eventuelly, but don't hold your breath. Sorry I can't be more helpful than that. :-I
            alice Alice Sherepa added a comment -

            I'll close the report then, in case the bug will still be reproducible - please comment here and it will be reopened and investigated.

            alice Alice Sherepa added a comment - I'll close the report then, in case the bug will still be reproducible - please comment here and it will be reopened and investigated.

            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.