MySQL: Comment with "type" in it causes creation failure

[Former user]

CREATE TABLE test ( id int(11) unsigned NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='types'

Create the above table, then reflect the table and attempt to create it again (I used a second engine and the table.create(bind=e2) call). There will be a 'TYPE=s' added that causes failure (since there isn't an engine named s):

CREATE TABLE test ( id int(11) unsigned NOT NULL, PRIMARY KEY (id) ) COLLATE=utf8_bin ENGINE=InnoDB TYPE=s DEFAULT CHARSET=utf8

This was against MySQL 5.

Contact me at russg at rnstech.com if more information needed.

[Mike Bayer]

seems like its the below code, which is l2508 of mysql.py in 0.5:

    def _add_option_string(self, directive):
        regex = (r'(?P<directive>%s\s*(?:=\s*)?)'
                 r'(?:\x27.(?P<val>.*?)\x27(?!\x27)\x27)' %
                 re.escape(directive))
        self._pr_options.append(
            _pr_compile(regex, lambda v: v.replace("''", "'")))

assuming we can reproduce, this would have to not match "'types'" as TYPE, so perhaps needs somehting like \b(?P<directive>%s\b or similar

[Former user]

\btype\b is insufficient. Fields like {{{comment}}} have quoted strings, and it's not good to be looking for other options inside those strings.

My proposed patch looks for string-based options first, and removes options as they're detected in the string so they can't be re-parsed.

[Former user]

1634.patch

[Michael Trier]

This patch looks pretty good. Because of the complexity and the amount of changes I would like zzzeek to look at it.

[Mike Bayer]

I'm too fuzzy to understand it fully but it works, 0f4770775fd91f43a7243965eb15b090d4c34a88.

[Mike Bayer]

Removing milestone: 0.6.0 (automated comment)