MySQL table definition parser warns about valid key definition

[Lele Long]

I am using mysql 5.6.16-log, and SA warns as following:

/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/reflection.py:56: SAWarning: Unknown schema content: u"  KEY `tbl_idx_pk` (`zip_code`,`shop_code`) USING BTREE COMMENT '(null)',"
  util.warn("Unknown schema content: %r" % line)

Code below demonstrates the problem, I have also found no such test case in mysql/test_reflection.py RawReflectionTest test_key_reflection

>>> from sqlalchemy.dialects.mysql import reflection
>>> from sqlalchemy.dialects.mysql import base as mysql
>>> dialect = mysql.dialect()
>>> parser = reflection.MySQLTableDefinitionParser(dialect, dialect.identifier_preparer)
>>> regex = parser._re_key
>>> assert regex.match("  PRIMARY KEY (`id`) USING BTREE KEY_BLOCK_SIZE 16,")
>>> assert regex.match("  PRIMARY KEY (`id`) USING BTREE COMMENT '(null)',")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
AssertionError
>>> 

I would suggest adding comment to the regexp for key definition(_re_key) .

[Lele Long]

I doubt (P in the comment capture group r'(?: +COMMENT +(P<comment>(?:\x27\x27|[^\x27])+))?'. Should it be (?P ?

[Mike Bayer]

alrighty, this is the same as #3829. Same question as there. May I please have a CREATE TABLE statement to reproduce so that I may be able to test this? else I'll have to close.

[Lele Long]

CREATE TABLE `shop_test` (
  `pkey` varchar(255) NOT NULL,
  `zip_code` varchar(16) NOT NULL COMMENT 'postcode for location',
  `shop_code` varchar(16) NOT NULL COMMENT 'shop code for retailer',
  PRIMARY KEY (`pkey`),
  KEY `tbl_idx_pk` (`zip_code`,`shop_code`) USING BTREE COMMENT '(null)',
  KEY `tbl_idx_pk2` (`pkey`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I suggest addding a comment capturing(or non-capturing) group to _re_key, and I also suggest fixing the existed comment capturing group in _re_column at this line.

I will try to submit one patch to https://gerrit.sqlalchemy.org/. I have never used gerrit before, or I will just make a pr on github.

[Lele Long]

@zzzeek I have submitted the pr and the test result here: https://github.com/zzzeek/sqlalchemy/pull/324 . I will try to fix comment re in the _re_column if this pr sounds good enough.

[Mike Bayer]

I would note we have full comment reflection support for 1.2 almost ready to go at https://gerrit.sqlalchemy.org/#/c/111/. A warning here is only interim, but since we have your patch I'll put it in.

[Mike Bayer]

Parse (but don't record) COMMENT portion of MySQL table key

The MySQL dialect now will not warn when a reflected column has a "COMMENT" keyword on it, but note however the comment is not yet reflected; this is on the roadmap for a future release. Pull request courtesy Lele Long.

Fixes: #3867 Pull-request: https://github.com/zzzeek/sqlalchemy/pull/324 Change-Id: I869e29aba6766d0eda1e59af09a3e8e3748a3942

→ <<cset bd6ba3ac8274>>