VARCHAR requires a length when rendered on MySQL

Question

I have a buildout instance with pas.plugins.sqlalchemy. It appears in installation list but when installing it results in an error.

Here's the ZCML definition:

<configure xmlns="http://namespaces.zope.org/zope" xmlns:db="http://namespaces.zope.org/db">
<include package="z3c.saconfig" file="meta.zcml" />
<db:engine xmlns="http://namespaces.zope.org/db" name="pas" url="mysql://webadmin:password@rcs-mysql-dev/estep" />
<db:session xmlns="http://namespaces.zope.org/db" name="pas.plugins.sqlalchemy" engine="pas" />
</configure>

Traceback is:

Traceback (innermost last):
  Module ZPublisher.Publish, line 127, in publish
  Module ZPublisher.mapply, line 77, in mapply
  Module ZPublisher.Publish, line 47, in call_object
  Module Products.CMFQuickInstallerTool.QuickInstallerTool, line 575, in installProducts
  Module Products.CMFQuickInstallerTool.QuickInstallerTool, line 512, in installProduct
   - __traceback_info__: ('pas.plugins.sqlalchemy',)
  Module Products.GenericSetup.tool, line 330, in runAllImportStepsFromProfile
   - __traceback_info__: profile-pas.plugins.sqlalchemy:install
  Module Products.GenericSetup.tool, line 1085, in _runImportStepsFromContext
  Module Products.GenericSetup.tool, line 999, in _doRunImportStep
   - __traceback_info__: pas.plugins.sqlalchemy.install
  Module pas.plugins.sqlalchemy.setuphandlers, line 46, in install
  Module sqlalchemy.schema, line 2148, in create_all
  Module sqlalchemy.engine.base, line 1698, in create
  Module sqlalchemy.engine.base, line 1740, in _run_visitor
  Module sqlalchemy.sql.visitors, line 83, in traverse_single
  Module sqlalchemy.engine.ddl, line 42, in visit_metadata
  Module sqlalchemy.sql.visitors, line 83, in traverse_single
  Module sqlalchemy.engine.ddl, line 58, in visit_table
  Module sqlalchemy.engine.base, line 1191, in execute
  Module sqlalchemy.engine.base, line 1241, in _execute_ddl
  Module sqlalchemy.sql.expression, line 1413, in compile
  Module sqlalchemy.engine.base, line 702, in compile
  Module sqlalchemy.engine.base, line 715, in process
  Module sqlalchemy.sql.visitors, line 54, in _compiler_dispatch
  Module sqlalchemy.sql.compiler, line 1152, in visit_create_table
  Module sqlalchemy.dialects.mysql.base, line 1282, in get_column_specification
  Module sqlalchemy.engine.base, line 761, in process
  Module sqlalchemy.sql.visitors, line 54, in _compiler_dispatch
  Module sqlalchemy.sql.compiler, line 1450, in visit_string
  Module sqlalchemy.dialects.mysql.base, line 1520, in visit_VARCHAR
InvalidRequestError: VARCHAR requires a length when rendered on MySQL

Answer 1

you need to specify lengths for all Strings. That means: String(n) instead of simply String. So, in pas.plugins.sqlalchemy.model.User

 login = Column(String, unique=True)

becomes

 login = Column(String(100), unique=True)

etc...

Answer 2

I don't have a MySQL installation, and pas.plugins.sqlalchemy works fine on postgresql for me, but it would seem that the authors have made an assumption about varchars. Assuming it's not something that SQLAlchemy should be handling itself (it would be really nice if the MySQL dialect for SQLalchemy would select an appropriate maximum size for unbounded varchars), I'll see if I can commit a fix this evening.

A quick glance at the code shows that all "String" (treated as varchar by the database) fields have maximum lengths except Login, name and password in the User table and name in the Group table, and there's no good reason why these should be different.

Update: Check out https://svn.plone.org/svn/collective/PASPlugins/pas.plugins.sqlalchemy/branches/auspex from subversion. It's my version of pas.plugins.sqlalchemy with support for the IGroupCapability interface (lets users be added to and removed from groups that are also stored in the rdb), and I've also added lengths to all unbounded String fields.

If you don't know how to use subversion checkouts in buildout, see: http://pypi.python.org/pypi/mr.developer/