Bitten by MariaDB 10.2 Incompatibile Change

Since MariaDB first claimed to be a “drop-in replacement for MySQL”, I knew that claim wouldn’t age well.  It hasn’t – the list of incompatibilities between various MySQL and MariaDB verions grows larger and larger over time, now requiring a dedicated page to compare two specific recent releases.  Despite that, I largely operated under the assumption that basic, general use cases would see no difference.

I was wrong.

This past week, I was “helping” with a Support issue where installation of Cloudera Manager failed on MariaDB 10.2.  Like many products, the schema for Cloudera Manager has evolved over releases, and new installs apply both the original schema and version-specific updates to bring the schema to a current version.  When it failed, I reviewed the scripts and found nothing that should cause problems.  A fantastic Support colleague (thanks Michalis!) dug in deeper, and – contradicting my assumptions – found that the root problem was an incompatible change made in MariaDB 10.2.8.  This causes ALTER TABLE … DROP COLUMN commands to fail in MariaDB 10.2.8, which do not fail in any MySQL version, nor in any previous MariaDB version – including 10.2.7.

When dropping a column that’s part of a composite unique constraint, MariaDB throws error 1072.  Here’s an example:

MariaDB [test]> SHOW CREATE TABLE uc_test\G
*************************** 1. row ***************************
Table: uc_test
Create Table: CREATE TABLE `uc_test` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
UNIQUE KEY `b` (`b`,`c`),
UNIQUE KEY `d` (`d`)
1 row in set (0.00 sec)

MariaDB [test]> ALTER TABLE uc_test DROP COLUMN c;
ERROR 1072 (42000): Key column ‘c’ doesn’t exist in table
MariaDB [test]> ALTER TABLE uc_test DROP COLUMN b;
ERROR 1072 (42000): Key column ‘b’ doesn’t exist in table

This differs from previous version behavior, as well as MySQL, which does one of two things:

  1. Narrows the UNIQUE constraint to the remaining columns (surely not ideal, Bug#17098).
  2. Rejects the change if the resulting narrowed UNIQUE constraint is violated.

While narrowing the existing UNIQUE constraint seems inappropriate, at least MySQL provides a useful and appropriate error message as a result:

mysql> INSERT INto uc_test values (1,1,1,1),(2,1,2,2);
Query OK, 2 rows affected (0.17 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> alter table uc_test DROP COLUMN c;
ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘b’

I agree with the suggestion in the MySQL bug report that a more appropriate action would be to drop the entire UNIQUE constraint in which the dropped column appeared.

MariaDB elected to go a different route. This behavior change was triggered by the work done in MDEV-11114, which explicitly identified a defect in MariaDB’s CHECK CONSTRAINT feature.  As the CHECK CONSTRAINT feature is not found upstream, neither was this defect, which failed to drop constraints declared independent of the column definition when the related column was dropped.  Rather than restrict the fix to the problematic CHECK CONSTRAINT code, MariaDB developers elected to impose new restrictions on all CONSTRAINTS – despite a very clearly-articulated comment from Mike Bayer documenting the incompatibilities this would trigger.  MariaDB also elected to make this change  in a maintenance release instead of a minor version upgrade.  As a result, SQL scripts – like that used by Cloudera Manager – fail using MariaDB 10.2.8, while they did not in 10.2.7.

Note that I have not tested this behavior in a replication stream – I presume it breaks replication from any MySQL or MariaDB version to 10.2.8+.  That’s an even bigger issue for many deployments.

MariaDB has an opportunity – and I’d argue an obligation – to avoid introducing such incompatibilities in maintenance releases.  As somebody supporting enterprise products built to use relational databases such as MariaDB, we need to have confidence that maintenance release upgrades won’t break products relying on those services.  Ignoring the promise of being a “drop in replacement”, the consequences of this change were known in advance, yet MariaDB chose to pursue it in a maintenance release.  There are two real, underlying bugs – the CHECK CONSTRAINT defect introduced by MariaDB, which should have been fixed without impact to other functionality, and the long-standing MySQL defect that triggers constraint narrowing.  SQL standards compliance is a viable reason for behavior changes, but such should be done at minor releases only.

I also object to MariaDB’s decision to repurpose an existing MySQL error code for this purpose.  I agree with Elena’s comment that more meaningful error messages are required.  This is new error-generating behavior, and should have a unique, new error code and message to accompany it for troubleshooting purposes.  This is particularly important when the behavior deviates from established historical behavior.

I’m all for MariaDB developing their own value-add features for MySQL Server.  Defects found in such features should be fixed without impacting common behavior, though.


14 thoughts on “Bitten by MariaDB 10.2 Incompatibile Change

    1. I commented on an existing bug, MDEV-13613, where the decision was made to address this as a documentation defect. The earlier referenced bug report (MDEV-11114) has a pretty comprehensive explanation of the impacts this change will have, and a decision was made to implement the change, anyhow.

  1. Despite Mike Bayer articulated his comment very clearly, like you said, suprisingly you managed to miss his point. It was, in his words, about “constraint that relies solely upon a single column in a table.” A *single* column, and this works. Your example uses multiple columns.

    1. Hi Vlad,

      I disagree that I miss his point. The specifics cited were in regards to single columns, but the larger concern was about both appropriateness and incompatibility of the exact error condition triggered by the eventual “fix”. I fully expect MariaDB to fully consider both concerns when making changes, especially when they are raised in such a direct manner. That includes projecting from specific examples on single-column indexes to composite indexes. This should have been clear when corresponding upstream MySQL test cases had to be revised to accommodate the new behavior. You seem to want to make the argument that this should not be expected of MariaDB.

  2. Hi Todd,
    Mike specifically mentions single column. His use cases all make sense.

    Silently converting a multicolumn unique constraint to a single column unique constrain does not make sense, according to me. Is this documented anywhere, except now in your blog?

    1. Vlad,

      I feel like you are confusing two things. You ask whether silent conversion of a composite unique constraint to narrowed unique constraint upon dropping one column referenced in that constraint makes sense. I said I don’t think it does, and referenced MySQL Bug#17098 where this is tracked. So, yes – it is documented.

      Also documented are incompatibility concerns of generating a new error due to constraints when dropping a column from a table (MDEV-11114) and the impacts on composite constraints (MDEV-13625, which required test case changes, and MDEV-13613, which – correctly, IMHO – initially labels this as a regression). It’s not clear what more you are asking for.

      Maybe you could state your position clearly – are you arguing that MariaDB didn’t know what the implications were of the change they made in a maintenance release for standards compliance purposes?

      1. Can I state my position clearly, yes I can.
        Todd, of you quote someone else, please be accurate. Do not give additional meaning to someones else’s words.

        This is a remark, and I did not ask any questions, thus I cannot answer “It’s not clear what more you are asking for.”

        1. Vlad,

          I did not quote anybody. I referenced – and linked, so people could read directly – the comments made, which I believe explicitly call out the incompatibility concern introducing this new error behavior would trigger. If you believe I have poorly characterized that comment based on the fact that the example and references were for single-column indexes only, I disagree. I’m not saying that multiple-column indexes were explicitly called out (it’s MariaDB’s job to assess the impacts of such code changes in full), but that incompatible behavior concerns were explicitly raised.

          These exact incompatibilities are what impacted production customers later, who rely on MariaDB to not make such arbitrary behavioral changes in maintenance releases. Regardless of where you stand on the specific comment you reference, I’m glad to hear you are not suggesting that this change was made without awareness of the compatibility concerns. Since we appear to agree on that, further debate on that specific comment seems largely academic.

  3. Todd, what can we do now about it? Reverting this behavior would introduce another incompatibility in the stable version, right?

    MariaDB has an @@old_mode variable that allows to revert to the old behavior sometimes. I can make it to revert MDEV-11114. Would that help with your compatibility issues?

    1. Thanks for asking, Sergei. From a Cloudera Manager perspective, we’ll address this by modifying the upgrade scripts to first drop the UNIQUE constraint, then the column. From a general compatibility perspective, I’d recommend reverting the change. It’s relatively new behavior, and I’d argue that nobody is likely dependent upon it yet. Enabling the behavior by optional configuration (perhaps the opposite of @@old_mode, not sure) seems ideal.

  4. What about introducing a strict mode that would not allow to drop any column that is part of any index? This could possibly be an annoyance for someone who relies on bad practices, but that’s all. Any other behaviour – especially if not consistent to all versions of all MySQL flavours – is extremely dangerous for every single user. The more changes are done with a wrong ALTER TABLE, the more dangerous the version is.

    1. I’d be fine with a strict mode. If somebody (Oracle, MariaDB) gets under the hood to implement different behavior, it seems better to trigger it off of expanded SQL syntax (CASCADE, etc.) instead of a configuration option, though.

    1. If I see that happen, I will update this post. You may want to subscribe to both MDEV-11114 and MDEV-13625, as I hope any code changes made would be reflected in at least one of those.

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.