Centos 7

wonko, can you please provide the table schema? Preferrably, CREATE TABLE statement, then ALTER TABLE to add the columns. You can obfuscate the table and column names, but it has to be valid SQL. That would greatly help us reproduce the problem. Thanks!

I only have the current create statement (`show create table`), I don't have the exact statement used to add the column which was added through the instant-add method. But we didn't do anything special, just an "alter table add column ... ". So I would say the original table was what is below, without the instant-added-field, and the instant-added-field is added later.

CREATE TABLE `table1` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `field1_id` int(11) DEFAULT NULL,

  `field2_id` int(11) DEFAULT NULL,

  `field3_id` int(11) DEFAULT NULL,

  `field4_id` int(11) DEFAULT NULL,

  `field5` text COLLATE utf8_unicode_ci DEFAULT NULL,

  `field6` int(11) DEFAULT NULL,

  `field7` int(11) DEFAULT NULL,

  `field8` longtext COLLATE utf8_unicode_ci DEFAULT NULL,

  `field9` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

  `field10` int(11) DEFAULT NULL,

  `field11` int(11) NOT NULL,

  `field12` int(11) NOT NULL,

  `field13` datetime DEFAULT NULL,

  `field14` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

  `instant_added_field1` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `IDX_F84AA10D613FECDF` (`field1_id`),

  KEY `IDX_F84AA10D5DA1941` (`field2_id`),

  KEY `IDX_F84AA10DCCFA12B8` (`field4_id`),

  KEY `IDX_F84AA10DF025383A` (`field3_id`),

  CONSTRAINT `FK_F84AA10DCCFA12B8` FOREIGN KEY (`field4_id`) REFERENCES `table_4` (`id`),

  CONSTRAINT `_FK_F84AA10D5DA1941` FOREIGN KEY (`field2_id`) REFERENCES `table_2` (`id`),

  CONSTRAINT `_FK_F84AA10DF025383A` FOREIGN KEY (`field3_id`) REFERENCES `table_3` (`id`),

  CONSTRAINT `__FK_F84AA10D613FECDF` FOREIGN KEY (`field1_id`) REFERENCES `table_1` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1751637 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Btw, if it might help debugging the case, I can provide that frm/ibd with the corruption, but I can't just post it here (there is customer data in there).

wonko, I am very sorry for the delay, due other assignments, and holidays. The .ibd file would help a lot, if you still have it. You can upload it to ftp://ftp.mariadb.com/uploads and let me know the file name.

This case looks very similar to MDEV-18519, and for that one I should have the table schema and some recent ADD COLUMN operations, but no .ibd file. With that information, I might be able to reproduce the problem, but an affected .ibd file would be much easier to work with. I suspect that the problem can only be repeated with a suitable sequence of DML operations. Also the timing of purge operations could affect reproducibility.

Uploaded the two ibd/frm files which caused the issues. Filename is MDEV-19783_data_2.tgz (there is also a MDEV-19783_data.tgz but you can remove that one, upload failed halfway). The column which should be NULL but had random data from the table itself is `uuid` (last column). This column was added through the instant method a couple of weeks before the issues started to happen.

You can clearly see the problem when you do a simple select (with a limit 10), and you'll notice the uuid field has data which actually maps to the other fields.

There is customer data in these files, please discard of them when they are no longer needed.

wonko, thank you.

It looks like for this table, something has wrongly reset the FIL_PAGE_TYPE of the root page from FIL_PAGE_TYPE_INSTANT to FIL_PAGE_INDEX. This corruption would not take effect until the table definition is being re-loaded (after server restart, or after the table had been evicted from the InnoDB data dictionary cache).

This is a very nasty bug that should affect all usage of instant ADD COLUMN (in 10.4, also instant DROP COLUMN and changing the order of columns). To work around this, the tables should be rebuilt to the canonical format:

ALTER TABLE instantly_altered_table FORCE;

ALTER TABLE t ADD COLUMN …, FORCE;

For both .ibd files that are contained in the archive MDEV-19783_data_2.tgz, the clustered index root page (page 3) is in the old "not instant ALTER TABLE" format. We can see it from the FIL_PAGE_TYPE being 0x45bf.
For the first file (with the 5-digit number in the directory name), the clustered index root page has PAGE_LEVEL=1, and the leftmost leaf page is page number 8.
In that page, the first record is a metadata record, because it carries the info_bits=0x10.

For the second file (with 3-digit number in the directory name), we likewise have FIL_PAGE_TYPE=FIL_PAGE_INDEX and PAGE_LEVEL=1 for the clustered index root page., and again the leftmost leaf page number is 8, and again we have the hidden metadata record at the logical start of that page.

We fixed this by disallowing the instant-add logic in our framework which keeps our schemas in a good shape; and did an optimize on all tables. So we no longer have this corruption.
Main question is, how did we get in that situation, and how to avoid this in the future? We must have hit a bug somewhere which caused this... We would like to be able to use the INSTANT functionality again, but I'm not feeling to confident currently...

wonko, there must be a glitch somewhere in the InnoDB B-tree logic that allows this to happen. It should not be in the root page split logic, because in both your tables, it looked like the instant ADD COLUMN was executed after the root page had already been split. The root page could have been reorganized, but on reorganize we should preserve the FIL_PAGE_TYPE.

The next step is that I will add debug instrumentation that allows our Random Query Generator (RQG) based tests to evict tables from the InnoDB table more frequently, or at each transaction commit. With that, it should be possible to repeat the bug using RQG, and then apply the grammar simplifier to end up with a simple test case that I can use for repeating the bug.

Until we have found and fixed this bug, it is not safe to use the instant ADD COLUMN feature.

Thanks for the confirmation. Is there a general flag in the config we can set to disable all the `INSTANT` stuff at the server leve (or change the default behaviour)l?

wonko, there is only the crude old_alter_table=1 or alter_algorithm=copy, which will cause even the simplest ALTER TABLE to use ALGORITHM=COPY (copy the data record by record), like it was before the InnoDB Plugin in MySQL 5.1 introduced "fast index creation". You might not want that.

elenst observed that MDEV-19743 and this MDEV-19783 were reported against version 10.3.14. I checked the InnoDB changes between 10.3.13 and 10.3.14, and the best candidate for introducing the regression would be MDEV-19022. But, the change at the end of btr_discard_page() does appear to preserve the FIL_PAGE_TYPE on the root page. And MDEV-18519, which I believe to be the same issue, was filed already against version 10.3.7.

mleich, please test 10.3 with innodb_evict_tables_on_commit_debug.patch and set the debug parameter during tests with Random Query Generator. I believe that the minimal scenario should be like the following:

CREATE TABLE t … ENGINE=InnoDB;

INSERT INTO t …; -- at least one row, most likely several, because the data files that I analyzed had multiple pages in the clustered index

-- optionally needed: UPDATE or DELETE

ALTER TABLE t ADD COLUMN … /* ALGORITHM=INSTANT */;

-- INSERT, UPDATE, DELETE workload until we crash

A single client should be sufficient, and possibly necessary. With a single client connection, --innodb-evict-tables-on-commit-debug should have much better chances to evict table definitions during transaction commit.

ok

Hi,
The issue looks like critical. One of the core feature can leads to a server crash and data corruption.
Wouldn't you like to mention it in release notes or provide workaround without the necessity of changing applications like setting default ALTER algorithm via a config file?
As far I understand, the only one workaround at the moment is explicitly set ALGORITHM in an ALTER statement.

Hi kompastver,

Wouldn't you like to mention it in release notes or provide workaround without the necessity of changing applications like setting default ALTER algorithm via a config file?

We do have some documentation here:

https://mariadb.com/kb/en/library/innodb-online-ddl-operations-with-algorithminstant/#known-bugs

I believe the only configuration option that could transparently prevent this is to set the following in a configuration file:

[mariadb]

...

alter_algorithm=COPY

Setting this to COPY would likely cause terrible performance for most users. Setting this to INPLACE wouldn't be a sufficient workaround, because InnoDB can still use the INSTANT algorithm if the algorithm is set to any value other than COPY. This is due to the hierarchical nature of MariaDB's alter algorithms. See here:

When a user specifies an alter algorithm for a DDL operation, MariaDB does not necessarily use that specific algorithm for the operation. It interprets the choice in the following way:

• If the user specifies COPY, then InnoDB uses the COPY algorithm.
• If the user specifies any other algorithm, then InnoDB interprets that choice as the least efficient algorithm that the user is willing to accept. This means that if the user specifies INPLACE, then InnoDB will use the most efficient algorithm supported by the specific operation from the set (INPLACE, NOCOPY, INSTANT). Likewise, if the user specifies NOCOPY, then InnoDB will use the most efficient algorithm supported by the specific operation from the set (NOCOPY, INSTANT).

https://mariadb.com/kb/en/library/innodb-online-ddl-overview/#specifying-an-alter-algorithm

As far I understand, the only one workaround at the moment is explicitly set ALGORITHM in an ALTER statement.

If you set the algorithm to INPLACE, then you would also have to add the FORCE option. e.g.:

SET SESSION alter_algorithm='INPLACE';

ALTER TABLE tab ADD COLUMN c varchar(50), FORCE;

Geoff, thanks for explanations!

Hi kompastver,

No problem!

I know that marko was considering whether a better workaround is needed to avoid known bugs like this. I created MDEV-20590 to track one potential idea that we previously discussed. I'm not sure if something like that will actually get implemented though.

We haven't been able to reproduce this issue yet, so if you happen to find a way to reproduce it, please let us know. Luckily, the bug seems relatively rare.

We should ensure that CHECK TABLE will report corruption if:

• !table->is_instant() holds and we find a metadata record
• we encounter any other leaf-page where REC_INFO_MIN_REC_FLAG is set

Note: REC_INFO_MIN_REC_FLAG did not occur in any leaf-page records until MDEV-11369 introduced it to distinguish the hidden metadata record. Such records are always at the start of the leftmost non-leaf pages (with FIL_PAGE_PREV containing FIL_NULL and PAGE_LEVEL not being 0). The flag causes the key of the record to be ignored, and makes the record smaller than any other key.

It would also help if normal SQL operations noticed these forms of corruption.
Such an instrumented build should be used in an attempt to reproduce the problem. Tests with the recently introduced debug parameter

SET GLOBAL innodb_evict_tables_on_commit_debug=ON;

were not successful yet. I think that in order to be able to reproduce this bug, we must try to evict and reload table definitions as often as possible. If the table remains in the InnoDB dict_sys cache, any changes to the FIL_PAGE_TYPE of the clustered index root page should not matter at all.

Please review CHECK TABLE improvements.

I pushed the CHECK TABLE improvements and improved checks to a staging tree of 10.2 after testing it WITH_INNODB_EXTRA_DEBUG enabled. I don’t think that it hurts to have better checks in 10.2 already.

I believe that I may have found an explanation why mleich failed to repeat this corruption with recent 10.3 or 10.4.
This error could be fully explained by MDEV-19916, which was fixed in MariaDB Server 10.3.17 and 10.4.7. Notably, "affected versions" do not list anything after those versions.

I think that this report duplicates MDEV-19916, which was fixed in MariaDB 10.3.17 and 10.4.7.