Indexes not working?

[Former user]

Hello! I ran into the following issues when creating columns with both {{{index=True}}} and {{{unique=True}}}, e.g.

Column('name', String(255), unique=True, index=True),

I experienced that: * Indexes were not created at all * {{{index=true}}} killed {{{unique=true}}} * explicitly adding {{{Index()}}} constructs didn't help either

My setup is * SQL alchemy 0.5.5 * mysql Ver 14.12 Distrib 5.0.70

Below is my case to reproduce.

Alchemy code input

  Table('index', metadata,
      Column('id', Integer, primary_key=True, autoincrement=True),
      Column('name', String(255), index=True),
  )
  Table('unique', metadata,
      Column('id', Integer, primary_key=True, autoincrement=True),
      Column('name', String(255), unique=True),
  )
  Table('unique_index', metadata,
      Column('id', Integer, primary_key=True, autoincrement=True),
      Column('name', String(255), unique=True, index=True),
  )

SQL shown in logging output:

  CREATE TABLE `index` (
          id INTEGER NOT NULL AUTO_INCREMENT,
          name VARCHAR(255),
          PRIMARY KEY (id)
  )
  CREATE TABLE `unique` (
          id INTEGER NOT NULL AUTO_INCREMENT,
          name VARCHAR(255),
          PRIMARY KEY (id),
           UNIQUE (name)
  )
  CREATE TABLE unique_index (
          id INTEGER NOT NULL AUTO_INCREMENT,
          name VARCHAR(255),
          PRIMARY KEY (id)
  )

Please contact me if you need further information from me.

Thanks, Sebastian

[Former user]

ping. any news?

[Mike Bayer]

no problem here:

from sqlalchemy import *
from StringIO import StringIO

buf = StringIO()

e = create_engine('mysql://', strategy='mock', executor=lambda s, p='': buf.write(s + p))
metadata = MetaData()

Table('index', metadata,
  Column('id', Integer, primary_key=True, autoincrement=True),
  Column('name', String(255), index=True),
)
Table('unique', metadata,
  Column('id', Integer, primary_key=True, autoincrement=True),
  Column('name', String(255), unique=True),
)
Table('unique_index', metadata,
  Column('id', Integer, primary_key=True, autoincrement=True),
  Column('name', String(255), unique=True, index=True),
)

metadata.create_all(e)
print buf.getvalue()

output:

zzzeek-2:sa05 classic$ python test.py

CREATE TABLE `unique` (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    name VARCHAR(255), 
    PRIMARY KEY (id), 
     UNIQUE (name)
)


CREATE TABLE `index` (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    name VARCHAR(255), 
    PRIMARY KEY (id)
)

CREATE INDEX ix_index_name ON `index` (name)
CREATE TABLE unique_index (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    name VARCHAR(255), 
    PRIMARY KEY (id)
)

CREATE UNIQUE INDEX ix_unique_index_name ON unique_index (name)

[Former user]

True, with {{{strategy='mock'}}} and the executor you mentioned. However, when you replace both with plain {{{echo=True}}} all traces of indexes disappear.

PS: Is there documentation on the mock strategy and custom executors available?

[Mike Bayer]

Replying to guest:

"mock" was for convenience since I did not have MySQL locally installed. But that's not a problem now. However there is still no issue.

SHOW VARIABLES LIKE 'sql_mode'
()
DESCRIBE `unique`
()
ROLLBACK
DESCRIBE `index`
()
ROLLBACK
DESCRIBE `unique_index`
()
ROLLBACK

CREATE TABLE `unique` (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    name VARCHAR(255), 
    PRIMARY KEY (id), 
     UNIQUE (name)
)


()
COMMIT

CREATE TABLE `index` (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    name VARCHAR(255), 
    PRIMARY KEY (id)
)


()
COMMIT
CREATE INDEX ix_index_name ON `index` (name)
()
COMMIT

CREATE TABLE unique_index (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    name VARCHAR(255), 
    PRIMARY KEY (id)
)


()
COMMIT
CREATE UNIQUE INDEX ix_unique_index_name ON unique_index (name)
()
COMMIT

PS: Is there documentation on the mock strategy and custom executors available?

"mock" is in the FAQ.