MYSQL: added support for drop check/constraint

Added support for DROP CHECK constraint which is required by MySQL 8.0.16
to drop a CHECK constraint; MariaDB supports plain DROP CONSTRAINT.  The
logic distinguishes between the two syntaxes by checking the server version
string for MariaDB presence.    Alembic migrations has already worked
around this issue by implementing its own DROP for MySQL / MariaDB CHECK
constraints, however this change implements it straight in Core so that its
available for general use.   Pull request courtesy Hannes Hansen.

Fixes: #4650
Closes: #4659
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4659
Pull-request-sha: 5b654a55e23c2ca498ca3b1cae4f53859e93e8f7

Change-Id: I967710f890722f11cf1f40406adbb17464d16194

3 files changed, 51 insertions, 2 deletions

diff --git a/doc/build/changelog/unreleased_13/4650.rst b/doc/build/changelog/unreleased_13/4650.rst
new file mode 100644
index 000000000..9c6623e17
--- /dev/null
+++ b/doc/build/changelog/unreleased_13/4650.rst
@@ -0,0 +1,11 @@
+.. change::
+    :tags: mysql, bug
+    :tickets: 4650
+
+    Added support for DROP CHECK constraint which is required by MySQL 8.0.16
+    to drop a CHECK constraint; MariaDB supports plain DROP CONSTRAINT.  The
+    logic distinguishes between the two syntaxes by checking the server version
+    string for MariaDB presence.    Alembic migrations has already worked
+    around this issue by implementing its own DROP for MySQL / MariaDB CHECK
+    constraints, however this change implements it straight in Core so that its
+    available for general use.   Pull request courtesy Hannes Hansen.
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 9cae3c689..ad5ab288c 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1703,6 +1703,12 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
         elif isinstance(constraint, sa_schema.UniqueConstraint):
             qual = "INDEX "
             const = self.preparer.format_constraint(constraint)
+        elif isinstance(constraint, sa_schema.CheckConstraint):
+            if self.dialect._is_mariadb:
+                qual = "CONSTRAINT "
+            else:
+                qual = "CHECK "
+            const = self.preparer.format_constraint(constraint)
         else:
             qual = ""
             const = self.preparer.format_constraint(constraint)
@@ -2397,11 +2403,13 @@ class MySQLDialect(default.DefaultDialect):
 
     @property
     def _is_mariadb(self):
-        return "MariaDB" in self.server_version_info
+        return (
+            self.server_version_info and "MariaDB" in self.server_version_info
+        )
 
     @property
     def _is_mysql(self):
-        return "MariaDB" not in self.server_version_info
+        return not self._is_mariadb
 
     @property
     def _is_mariadb_102(self):
diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py
index 54767a913..60e11ca29 100644
--- a/test/dialect/mysql/test_compiler.py
+++ b/test/dialect/mysql/test_compiler.py
@@ -5,6 +5,7 @@ from sqlalchemy import BOOLEAN
 from sqlalchemy import Boolean
 from sqlalchemy import cast
 from sqlalchemy import CHAR
+from sqlalchemy import CheckConstraint
 from sqlalchemy import CLOB
 from sqlalchemy import Column
 from sqlalchemy import DATE
@@ -128,6 +129,35 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "CREATE INDEX test_idx2 ON testtbl (data(5))",
         )
 
+    def test_drop_constraint_mysql(self):
+        m = MetaData()
+        table_name = "testtbl"
+        constraint_name = "constraint"
+        constraint = CheckConstraint("data IS NOT NULL", name=constraint_name)
+        tbl = Table(table_name, m, Column("data", String(255)), constraint)
+        dialect = mysql.dialect()
+        self.assert_compile(
+            schema.DropConstraint(constraint),
+            "ALTER TABLE %s DROP CHECK `%s`"
+            % (table_name, constraint_name),
+            dialect=dialect
+        )
+
+    def test_drop_constraint_mariadb(self):
+        m = MetaData()
+        table_name = "testtbl"
+        constraint_name = "constraint"
+        constraint = CheckConstraint("data IS NOT NULL", name=constraint_name)
+        tbl = Table(table_name, m, Column("data", String(255)), constraint)
+        dialect = mysql.dialect()
+        dialect.server_version_info = (10, 1, 1, "MariaDB")
+        self.assert_compile(
+            schema.DropConstraint(constraint),
+            "ALTER TABLE %s DROP CONSTRAINT `%s`"
+            % (table_name, constraint_name),
+            dialect=dialect
+        )
+
     def test_create_index_with_length_quoted(self):
         m = MetaData()
         tbl = Table(