From 98791a441b2e3d26aa7cef9d3e2907fac113e30f Mon Sep 17 00:00:00 2001 From: =?utf8?q?Dani=C3=ABl=20van=20Eeden?= Date: Tue, 22 Jun 2021 09:13:38 +0200 Subject: [PATCH] mysql reflection: Fix TiDB clustered PK comment compatibility On TiDB there is a `clustered_index` feature. This adds a `CLUSTERED` keyword to the PK definition. This is in a TiDB specific comment. However as the comment isn't followed by a space (` `) the regex used by SQL Alchemy fails to decode this line. ``` INFO:sqlalchemy.engine.Engine:SHOW CREATE TABLE `t` INFO:sqlalchemy.engine.Engine:[raw sql] () DEBUG:sqlalchemy.engine.Engine:Col ('Table', 'Create Table') DEBUG:sqlalchemy.engine.Engine:Row ('t', 'CREATE TABLE `t` (\n `id` int(11) NOT NULL,\n PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin') INFO:root:LINE: PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ /home/dvaneeden/dev/sqlalchemy_tidb/./sqlalchemy_tidb.py:26: SAWarning: Unknown schema content: ' PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */' metadata.reflect(engine, only=['t']) ``` ``` mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec) mysql> select tidb_version()\G *************************** 1. row *************************** tidb_version(): Release Version: v5.2.0-alpha-88-ged52601e6 Edition: Community Git Commit Hash: ed52601e6eb560138db8cdccdfa1b5e2d33a11f0 Git Branch: master UTC Build Time: 2021-06-16 13:03:48 GoVersion: go1.16.4 Race Enabled: false TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 Check Table Before Drop: false 1 row in set (0.00 sec) ``` --- lib/sqlalchemy/dialects/mysql/reflection.py | 2 +- test/dialect/mysql/test_reflection.py | 8 ++++++++ 2 files changed, 9 insertions(+), 1 deletion(-) diff --git a/lib/sqlalchemy/dialects/mysql/reflection.py b/lib/sqlalchemy/dialects/mysql/reflection.py index b78034de27..a1ad3adfe0 100644 --- a/lib/sqlalchemy/dialects/mysql/reflection.py +++ b/lib/sqlalchemy/dialects/mysql/reflection.py @@ -416,7 +416,7 @@ class MySQLTableDefinitionParser(object): r"(?: +KEY_BLOCK_SIZE *[ =]? *(?P\S+))?" r"(?: +WITH PARSER +(?P\S+))?" r"(?: +COMMENT +(?P(\x27\x27|\x27([^\x27])*?\x27)+))?" - r"(?: +/\*(?P.+)\*/ +)?" + r"(?: +/\*(?P.+)\*/ *)?" r",?$" % quotes ) diff --git a/test/dialect/mysql/test_reflection.py b/test/dialect/mysql/test_reflection.py index 5b785a7a41..7b04698d95 100644 --- a/test/dialect/mysql/test_reflection.py +++ b/test/dialect/mysql/test_reflection.py @@ -1148,6 +1148,10 @@ class RawReflectionTest(fixtures.TestBase): assert not regex.match( " PRIMARY KEY (`id`) USING BTREE KEY_BLOCK_SIZE = = 16" ) + # TiDB: https://github.com/sqlalchemy/sqlalchemy/issues/6659 + assert regex.match( + " PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */" + ) assert regex.match(" KEY (`id`) USING BTREE COMMENT 'comment'") # `SHOW CREATE TABLE` returns COMMENT '''comment' # after creating table with COMMENT '\'comment' @@ -1164,6 +1168,10 @@ class RawReflectionTest(fixtures.TestBase): "/*!50100 WITH PARSER `ngram` */ " ) + m = regex.match(" PRIMARY KEY (`id`)") + eq_(m.group("type"), "PRIMARY") + eq_(m.group("columns"), "`id`") + def test_key_reflection_columns(self): regex = self.parser._re_key exprs = self.parser._re_keyexprs -- 2.47.2