BLOB/TEXT column used in key specification without a key length

[Former user]

The following field/index produces an error in SA 0.4.2 / MySQL 5:

table = sa.Table('resource_path', self.db_metadata,
            sa.Column('id',             sa.Integer,     primary_key = True),
            sa.Column('path',           sa.Binary(255), index = True),
            mysql_engine='INNODB'
        )
table.create(checkfirst = True)

The complete error message is:

Traceback (most recent call last):
  File "Guard/DBTest.py", line 43, in setUp
    self.assert_(self.db.install())
  File "/home/sam/code/spiff_guard/tests/Guard/../../src/Guard/DB.py",
line 35, in install
    table.create(checkfirst = True)
  File "/var/lib/python-support/python2.5/sqlalchemy/schema.py", line 300, in create
    self.metadata.create_all(bind=bind, checkfirst=checkfirst,
tables=[self](self))
  File "/var/lib/python-support/python2.5/sqlalchemy/schema.py", line 1215, in create_all
    bind.create(self, checkfirst=checkfirst, tables=tables)
  File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py", line 1131, in create
    self._run_visitor(self.dialect.schemagenerator, entity, connection=connection, **kwargs)
  File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py", line 1160, in _run_visitor
    visitorcallable(self.dialect, conn, **kwargs).traverse(element)
  File "/var/lib/python-support/python2.5/sqlalchemy/sql/visitors.py", line 76, in traverse
    meth(target)
  File "/var/lib/python-support/python2.5/sqlalchemy/sql/compiler.py", line 760, in visit_metadata
    self.traverse_single(table)
  File "/var/lib/python-support/python2.5/sqlalchemy/sql/visitors.py", line 30, in traverse_single
    return meth(obj, **kwargs)
  File "/var/lib/python-support/python2.5/sqlalchemy/sql/compiler.py", line 796, in visit_table
    self.traverse_single(index)
  File "/var/lib/python-support/python2.5/sqlalchemy/sql/visitors.py", line 30, in traverse_single
    return meth(obj, **kwargs)
  File "/var/lib/python-support/python2.5/sqlalchemy/sql/compiler.py", line 881, in visit_index
    self.execute()
  File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py", line 1760, in execute
    return self.connection.execute(self.buffer.getvalue())
  File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py", line 844, in execute
    return Connection.executors[c](c)(self, object, multiparams, params)
  File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py", line 854, in _execute_text
    self.__execute_raw(context)
  File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py", line 916, in __execute_raw
    self._cursor_execute(context.cursor, context.statement, context.parameters[0](0), context=context)
  File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py", line 953, in _cursor_execute
    self._handle_dbapi_exception(e, statement, parameters, cursor)
  File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py", line 935, in _handle_dbapi_exception
    raise exceptions.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect)
OperationalError: (OperationalError) (1170, "BLOB/TEXT column 'path' used in key specification without a key length") 'CREATE INDEX
ix_guard_resource_path_path ON guard_resource_path (path)' {}

The same thing happens when using Index() explicitly. The same table specification works in SA 0.3.

[Mike Bayer]

I get the exact same error in 0.3 as in 0.4, this case is not yet supported (dupe of #695). As a workaround, use a schema.DDL() construct for now with the exact MySQL syntax desired.

[Former user]

I hit the same problem, because Binary() is using BLOB() in MySQL instead of BINARY()

The way I solved it is equivalent to:

table = sa.Table('resource_path', self.db_metadata,

from sqlalchemy.databases.mysql import MSBinary
sa.Column('id', sa.Integer, primary_key = True), sa.Column('path', MSBinary(255), index = True), mysql_engine='INNODB'

)

Why doesn't SQLAlchemy use BINARY()?

[jek]

Why doesn't SQLAlchemy use BINARY()

The general mixed-case types like Binary are a request for SA's choice of backing data type. BINARY is a fixed-width type with padding, and VARBINARY drops trailing whitespace on SELECT on older servers. BLOB is the best all-purpose type available and is able to retrieve the same data that was stored. Binary(255) should emit BLOB(255) which in turn should be interpreted as TINYBLOB.

As you've mentioned, the full range of db-native types are available in the dialect module.

[Mike Bayer]

Its not clear to me what behavioral change we'd like to see here.

[Mike Bayer]

this is essentially a dupe of #1664.

[Mike Bayer]

Removing milestone: 0.6.xx (automated comment)