Docker version 17.06.0-ce, build 02c1d87 running on OSX 10.12.4 (16E195)
Docker image SHA: 801ba73f78da191339d1cf2acdb98f2ea6fe2d38974e838a4a5f1535563e4ed7

Yes, it is a result of a bug fix. Dropping a column in such a case was a bug. Because it did not only drop a column, it also added an additional constraint to the table — that all values in the column B are unique. It also contradicted the SQL standard. In the standard the unique constraint could have been dropped as a whole. Or there could've been an error. But under no circumstances it could've been automatically "adjusted" to mean something else.

Now a column cannot be dropped, if it's part of a multi-column UNIQUE constraint.

To reiterate — this logic only applies to multi-column and unique indexes. If an index is not unique, or it only covers one single column, the column will be dropped as before.

Exactly the same restriction applies to CHECK constraints too — a column cannot be dropped, if it's part of a multi-column CHECK constraint.

We need to document this change better, though

On a related note, ALTER TABLE…ALGORITHM=INPLACE in InnoDB in MariaDB 10.2.2 contains code to skip the sorting when the previous ordering of the primary key can be preserved. This is being covered in a test of MySQL 5.7 which is currently missing from MariaDB, innodb.innodb-index-debug:

#  BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP

#                LAST COLUMN OF OLD PK

#

SET debug="+d,innodb_alter_table_pk_assert_no_sort";

create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;

insert into t1 values('abd'), ('acd');

alter table t1 drop primary key, add primary key(o1(3)), lock=none;

drop table t1;

We should import this test to MariaDB. And we should also have a test that demonstrates that with explicit DROP, ADD clauses for PRIMARY KEY or UNIQUE KEY, the DROP COLUMN will be accepted.

In the provided example, the sorting will be necessary in

ALTER TABLE X DROP COLUMN A, DROP PRIMARY KEY, ADD PRIMARY KEY(B);

but it can be skipped in

ALTER TABLE X DROP COLUMN A, DROP PRIMARY KEY, ADD PRIMARY KEY(A);

because the old PRIMARY KEY(A,B) would be sorted in the same way as the new PRIMARY KEY(A).

I wouldn't dispute this being a bug, it's certainly something I can entirely understand fixing. I think it's worth triaging these kind of bugfixes to a larger release though. This behaviour has been in

• 5.5
• 10.0.10+
• 10.1.8 +
• 10.2.6 - 10.2.7

I feel as though fixing it in a minor/major version could have been more appropriate given it's a larger departure from how things have been in the past.

I have documented this on https://mariadb.com/kb/en/the-mariadb-library/alter-table/#drop-column-if-exists-col_name-cascaderestrict but "Key column 'A' doesn't exist in table" is not the best error message in this case.

pathompson, right. That's why I've done it in only 10.2, which is not as widely used as 5.5–10.1. But the actual trigger was MDEV-11114 — for CHECK constraint we have to disallow DROP COLUMN (there's no way to "automatically adjust the CHECK constraint"). And UNIQUE constraint was fixed to have a consistent behavior for all constraint types.

As part of MDEV-13625 I imported the test innodb.innodb-table-online into MariaDB Server 10.0. In the merge to 10.2, I had to adjust it for the changed behaviour: DROP COLUMN of a primary key column would require explicit DROP PRIMARY KEY, ADD PRIMARY KEY in order to be accepted.

Documentation clarified - the misleading error message can be opened as a separate issue

As part of MDEV-13626, I merged and adapted the MySQL 5.7 changes to the tests innodb.innodb-index and innodb.innodb-index-online.
As expected, some ALTER TABLE…DROP COLUMN statements were rejected, and only accepted if augmented with explicit DROP/ADD KEY.
It is worth noting that if all columns of a PRIMARY or UNIQUE KEY are dropped, then dropping the key will be implied:

--source include/have_innodb.inc

create table t1(f1 int not null, f2 int not null, f3 int,

        primary key (f1), unique key(f1, f2))engine=innodb;

insert into t1(f1,f2) values(1,3), (2,2);

--error ER_KEY_COLUMN_DOES_NOT_EXITS

alter table t1 drop f1;

alter table t1 drop f1, drop f2;

select * from t1;

drop table t1;

Please clarify exactly how this is "fixed" (per JIRA resolution status). Enhanced documentation? The behavior itself does not appear to be changed, despite the fact that this introduced breaking incompatibility with both past versions, upstream MySQL and in a maintenance release.

Yes, enhanced documentation.

The changed behavior was a result of the bug fix, namely, old behavior was considered a bug.
Of course, one can argue that any bug fix is a "change in behavior" and "introduces incompatibility", even if it fixes a crash.

Maybe the documentation can be linked, and the JIRA component updated to show that what was "fixed" was just documentation? Right now, it references "Data Definition - Alter Table", which - when coupled with the "Fixed" resolution - suggests behavior was changed.

When your bug fix for CHECK CONSTRAINT code changes behavior that triggers schema preparation scripts to fail - as it did for Cloudera Manager - in a maintenance release, when CHECK CONSTRAINTS aren't in use, I will argue that it's most certainly an introduced incompatibility.

Thanks - you may also consider adding a note in the 10.2 incompatibilities documentation.

You're right. Sorry for confusion.

"Component" is now Documentation.
The clarification was added to https://mariadb.com/kb/en/library/alter-table/#drop-column

The bug was that DROP COLUMN X in some cases implicitly meant "add new UNIQUE constraint to the column Y". Now if you want all values in the column Y be unique, you need to add a unique constraint explicitly, it won't automagically appear because of some unrelated operation.

Thank you again.

I understand you are referencing MySQL Bug#17098. I agree with the assertion that the historical behavior is inappropriate and non-compliant with standards. I disagree with the decision to make such a change in a maintenance release. I'd argue that behavioral changes such as this should only be made in maintenance releases when the consequences of the defect are severe (data loss, service availability,etc,), and that standards compliance enhancements be reserved for major/minor releases.