Fix for Mysql-Dialect when using autoincremented columns with InnoDB

[Former user]

When working with innodb and using combined primary keys where not the first column is autoincremented, the DDL generated by the MySQL-Dialect is broken. (see http://groups.google.com/group/sqlalchemy/browse_thread/thread/1983d33265e0f45f)

The DDL generated looks like this:

CREATE TABLE table_a ( assigned_id INTEGER NOT NULL, id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (assigned_id, id) )ENGINE=InnoDB

What's missing here is the explicit key for the autoincremented column. The correct code has to look like this:

CREATE TABLE table_a ( assigned_id INTEGER NOT NULL, id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (assigned_id, id), KEY key_autoinc(id) )ENGINE=InnoDB

Example code to reproduce the error is attached. Working solution: patched the MySQLDDLCompiler class and overwrote visit_create_table method to add the missing KEY-Part. The modified class is attached as well.

[Former user]

Simple file to reproduce the problem

[Former user]

Patched mysql dialect base.py (dialects/mysql/base.py)

[Mike Bayer]

this patch puts the creation of constraints into a separate step:

Index: lib/sqlalchemy/sql/compiler.py
===================================================================
--- lib/sqlalchemy/sql/compiler.py      (revision 6795)
+++ lib/sqlalchemy/sql/compiler.py      (working copy)
@@ -1011,7 +1011,15 @@
             if pk:
                 text += ", \n\t" + pk

-        const = ", \n\t".join(p for p in 
+        const = self.create_table_constraints(table)
+        if const:
+            text += ", \n\t" + const
+        
+        text += "\n)%s\n\n" % self.post_create_table(table)
+        return text
+    
+    def create_table_constraints(self, table):
+        return ", \n\t".join(p for p in 
                         (self.process(constraint) for constraint in table.constraints 
                         if constraint is not table.primary_key
                         and (
@@ -1022,11 +1030,7 @@
                             not getattr(constraint, 'use_alter', False)
                         )) if p is not None
                 )
-        if const:
-            text += ", \n\t" + const

-        text += "\n)%s\n\n" % self.post_create_table(table)
-        return text

     def visit_drop_table(self, drop):
         ret = "\nDROP TABLE " + self.preparer.format_table(drop.element)

The mysql patch could override this method, and take a look at table._autoincrement_column + the "innodb" flag, and add its "KEY" clause during the create_table_constraints step, so that we don't have to re-implement visit_table completely.

[Former user]

patch for compiler.py, base.py, and test_mysql.py

[Mike Bayer]

still some more changes, 4460f998e3b856e94e870c08cb4ed357027ced00.

[Mike Bayer]

Removing milestone: 0.6.0 (automated comment)