- Composite PK table on InnoDB where the "autoincrement" column

isn't first will emit an explicit "KEY" phrase within
CREATE TABLE thereby avoiding errors, [ticket:1496]

2 files changed, 36 insertions, 12 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 686e3da62..4a4648076 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1269,6 +1269,26 @@ class MySQLCompiler(compiler.SQLCompiler):
 #       creation of foreign key constraints fails."
 
 class MySQLDDLCompiler(compiler.DDLCompiler):
+    def create_table_constraints(self, table):
+        """Get table constraints."""
+        constraint_string = super(MySQLDDLCompiler, self).create_table_constraints(table)
+        
+        is_innodb = table.kwargs.has_key('mysql_engine') and \
+                    table.kwargs['mysql_engine'].lower() == 'innodb'
+
+        auto_inc_column = table._autoincrement_column
+
+        if is_innodb and \
+                auto_inc_column is not None and \
+                auto_inc_column is not list(table.primary_key)[0]:
+            if constraint_string:
+                constraint_string += ", \n\t"
+            constraint_string += "KEY `idx_autoinc_%s`(`%s`)" % (auto_inc_column.name, \
+                            self.preparer.format_column(auto_inc_column))
+        
+        return constraint_string
+
+
     def get_column_specification(self, column, **kw):
         """Builds column DDL."""
 
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 187b4d26f..32aa2a992 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1004,17 +1004,26 @@ class DDLCompiler(engine.Compiled):
             if const:
                 text += " " + const
 
+        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):
+        
         # On some DB order is significant: visit PK first, then the
         # other constraints (engine.ReflectionTest.testbasic failed on FB2)
+        constraints = []
         if table.primary_key:
-            pk = self.process(table.primary_key)
-            if pk:
-                text += ", \n\t" + pk
+            constraints.append(table.primary_key)
+            
+        constraints.extend([c for c in table.constraints if c is not table.primary_key])
         
-        const = ", \n\t".join(p for p in 
-                        (self.process(constraint) for constraint in table.constraints 
-                        if constraint is not table.primary_key
-                        and (
+        return ", \n\t".join(p for p in
+                        (self.process(constraint) for constraint in constraints 
+                        if (
                             constraint._create_rule is None or
                             constraint._create_rule(self))
                         and (
@@ -1022,11 +1031,6 @@ class DDLCompiler(engine.Compiled):
                             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)