From 765478888c0d14acd4969352e6c73f8724ad93fb Mon Sep 17 00:00:00 2001
From: Gord Thompson <gord@gordthompson.com>
Date: Thu, 6 Aug 2020 17:19:52 -0600
Subject: [PATCH] Fix mysql CREATE TABLE / COLLATE issue

Fixes: #5411
Change-Id: Ib0c53f5ed3f9d3ff0586580c9a9cce73b4b870f4
(cherry picked from commit 32e0b128f6c077017673f7049e2b667bf96f53e3)
---
 doc/build/changelog/unreleased_13/5411.rst |  6 +++++
 lib/sqlalchemy/dialects/mysql/base.py      |  2 ++
 test/dialect/mysql/test_compiler.py        | 28 ++++++++++++++++++++++
 3 files changed, 36 insertions(+)
 create mode 100644 doc/build/changelog/unreleased_13/5411.rst

diff --git a/doc/build/changelog/unreleased_13/5411.rst b/doc/build/changelog/unreleased_13/5411.rst
new file mode 100644
index 0000000000..8389278e27
--- /dev/null
+++ b/doc/build/changelog/unreleased_13/5411.rst
@@ -0,0 +1,6 @@
+.. change::
+    :tags: bug, mysql
+    :tickets: 5411
+
+    Fixed an issue where CREATE TABLE statements were not specifying the
+    COLLATE keyword correctly.
\ No newline at end of file
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index c652693246..b3bcb11417 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1772,6 +1772,8 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
             [
                 ("DEFAULT_CHARSET", "COLLATE"),
                 ("DEFAULT_CHARACTER_SET", "COLLATE"),
+                ("CHARSET", "COLLATE"),
+                ("CHARACTER_SET", "COLLATE"),
             ],
             nonpart_options,
         ):
diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py
index ec3c8bc139..ad556902dd 100644
--- a/test/dialect/mysql/test_compiler.py
+++ b/test/dialect/mysql/test_compiler.py
@@ -864,6 +864,34 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
             ")STATS_SAMPLE_PAGES=2 PARTITION BY HASH(other_id) PARTITIONS 2",
         )
 
+    def test_create_table_with_collate(self):
+        # issue #5411
+        t1 = Table(
+            "testtable",
+            MetaData(),
+            Column("id", Integer(), primary_key=True, autoincrement=True),
+            mysql_engine="InnoDB",
+            mysql_collate="utf8_icelandic_ci",
+            mysql_charset="utf8",
+        )
+        first_part = (
+            "CREATE TABLE testtable ("
+            "id INTEGER NOT NULL AUTO_INCREMENT, "
+            "PRIMARY KEY (id))"
+        )
+        try:
+            self.assert_compile(
+                schema.CreateTable(t1),
+                first_part
+                + "ENGINE=InnoDB CHARSET=utf8 COLLATE utf8_icelandic_ci",
+            )
+        except AssertionError:
+            self.assert_compile(
+                schema.CreateTable(t1),
+                first_part
+                + "CHARSET=utf8 ENGINE=InnoDB COLLATE utf8_icelandic_ci",
+            )
+
     def test_inner_join(self):
         t1 = table("t1", column("x"))
         t2 = table("t2", column("y"))
-- 
2.47.3