MySQL: reflect on PARTITIONs is noisy

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)