MySQL: reflect on PARTITIONs is noisy

Issue #2376 resolved
George V. Reilly created an issue

Reflecting on a partitioned MySQL table yields a lot of noisy warnings.

Create a table using the attached file partition_table.sql, which creates a table per http://dev.mysql.com/doc/refman/5.1/en/partitioning-range.html

Run the attached Python script, mysql_partition_reflection.py. It should print output like

$ python mysql_partition_reflection.py 
sqlalchemy/dialects/mysql/base.py:2078: SAWarning: Unknown schema content: u'/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(actual_action_time_utc))'
  return parser.parse(sql, charset)
sqlalchemy/dialects/mysql/base.py:2078: SAWarning: Unknown schema content: u'(PARTITION p201112 VALUES LESS THAN (1325404800) ENGINE = InnoDB,'
  return parser.parse(sql, charset)
sqlalchemy/dialects/mysql/base.py:2078: SAWarning: Unknown schema content: u' PARTITION p201201 VALUES LESS THAN (1328083200) ENGINE = InnoDB,'
  return parser.parse(sql, charset)
sqlalchemy/dialects/mysql/base.py:2078: SAWarning: Unknown schema content: u' PARTITION p201202 VALUES LESS THAN (1330588800) ENGINE = InnoDB,'
  return parser.parse(sql, charset)
sqlalchemy/dialects/mysql/base.py:2078: SAWarning: Unknown schema content: u' PARTITION p209912 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */'
  return parser.parse(sql, charset)

Reproduced with MySQL 5.5.10.

The following patch "fixes" it, since the code "punts" on reflecting over partitions:

diff -r 7026ffe57c76821af3f1d5d01721e4035dc82de9 lib/sqlalchemy/dialects/mysql/base.py
--- a/lib/sqlalchemy/dialects/mysql/base.py Thu Jan 12 00:49:02 2012 -0500
+++ b/lib/sqlalchemy/dialects/mysql/base.py Tue Jan 17 14:20:01 2012 -0800
@@ -2560,8 +2560,7 @@
         #
         # punt!
         self._re_partition = _re_compile(
-            r'  '
-            r'(?:SUB)?PARTITION')
+            r'(?:.*)(?:SUB)?PARTITION(?:.*)')

         # Table-level options (COLLATE, ENGINE, etc.)
         # Do the string options first, since they have quoted strings we need to get rid of.

Comments (5)

  1. Mike Bayer repo owner
    • changed component to mysql
    • changed milestone to 0.7.5

    looks good, seems like that regexp already intended to do this, but in this case needs to be widened. reflection tests still pass for mysql.

  2. Log in to comment