Database seeding not working

[Aupajo]

Getting there. I tried seeding the database using db_tool, which failed with "Specified key was too long; max key length is 767 bytes".

Any tips would be appreciated :)

2012-09-25 22:43:20,910 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2012-09-25 22:43:20,910 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,912 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'character_set%%'
2012-09-25 22:43:20,912 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,913 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2012-09-25 22:43:20,913 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,914 INFO sqlalchemy.engine.base.Engine SHOW COLLATION
2012-09-25 22:43:20,914 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,918 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2012-09-25 22:43:20,918 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,920 INFO sqlalchemy.engine.base.Engine DESCRIBE `tag`
2012-09-25 22:43:20,920 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,922 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-09-25 22:43:20,922 INFO sqlalchemy.engine.base.Engine DESCRIBE `article`
2012-09-25 22:43:20,922 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,923 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-09-25 22:43:20,923 INFO sqlalchemy.engine.base.Engine DESCRIBE `useraccount`
2012-09-25 22:43:20,923 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,924 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-09-25 22:43:20,924 INFO sqlalchemy.engine.base.Engine DESCRIBE `lookup`
2012-09-25 22:43:20,924 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,926 INFO sqlalchemy.engine.base.Engine DESCRIBE `uploaded_file`
2012-09-25 22:43:20,926 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,927 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-09-25 22:43:20,927 INFO sqlalchemy.engine.base.Engine DESCRIBE `label`
2012-09-25 22:43:20,927 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,928 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-09-25 22:43:20,928 INFO sqlalchemy.engine.base.Engine DESCRIBE `token`
2012-09-25 22:43:20,928 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,929 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-09-25 22:43:20,930 INFO sqlalchemy.engine.base.Engine DESCRIBE `article_label`
2012-09-25 22:43:20,930 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,931 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-09-25 22:43:20,931 INFO sqlalchemy.engine.base.Engine DESCRIBE `comment`
2012-09-25 22:43:20,931 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,932 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-09-25 22:43:20,932 INFO sqlalchemy.engine.base.Engine DESCRIBE `help_req`
2012-09-25 22:43:20,932 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,933 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-09-25 22:43:20,934 INFO sqlalchemy.engine.base.Engine DESCRIBE `twitter_access_token`
2012-09-25 22:43:20,934 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,935 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-09-25 22:43:20,935 INFO sqlalchemy.engine.base.Engine DESCRIBE `article_url`
2012-09-25 22:43:20,935 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,936 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-09-25 22:43:20,936 INFO sqlalchemy.engine.base.Engine DESCRIBE `article_tag`
2012-09-25 22:43:20,936 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,937 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-09-25 22:43:20,937 INFO sqlalchemy.engine.base.Engine DESCRIBE `source`
2012-09-25 22:43:20,937 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,938 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-09-25 22:43:20,939 INFO sqlalchemy.engine.base.Engine DESCRIBE `comment_user_map`
2012-09-25 22:43:20,939 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,940 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-09-25 22:43:20,940 INFO sqlalchemy.engine.base.Engine DESCRIBE `action`
2012-09-25 22:43:20,940 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,941 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-09-25 22:43:20,942 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE tag (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    name VARCHAR(32) NOT NULL, 
    description VARCHAR(256) NOT NULL, 
    kind INTEGER NOT NULL, 
    icon VARCHAR(32) NOT NULL, 
    PRIMARY KEY (id)
)


2012-09-25 22:43:20,942 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,961 INFO sqlalchemy.engine.base.Engine COMMIT
2012-09-25 22:43:20,962 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE article (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    headline VARCHAR(512) NOT NULL, 
    permalink VARCHAR(512) NOT NULL, 
    pubdate DATETIME, 
    added DATETIME NOT NULL, 
    needs_sourcing BOOL NOT NULL, 
    PRIMARY KEY (id), 
    CHECK (needs_sourcing IN (0, 1))
)


2012-09-25 22:43:20,962 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:20,978 INFO sqlalchemy.engine.base.Engine COMMIT
2012-09-25 22:43:20,979 INFO sqlalchemy.engine.base.Engine CREATE INDEX ix_article_pubdate ON article (pubdate)
2012-09-25 22:43:20,979 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:21,000 INFO sqlalchemy.engine.base.Engine COMMIT
2012-09-25 22:43:21,001 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE useraccount (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    email VARCHAR(256) NOT NULL, 
    username VARCHAR(64) NOT NULL, 
    prettyname VARCHAR(256) NOT NULL, 
    hashed_password VARCHAR(128), 
    created DATETIME NOT NULL, 
    last_seen DATETIME, 
    auth_supplier VARCHAR(16) NOT NULL, 
    auth_uid VARCHAR(1024) NOT NULL, 
    photo_id INTEGER, 
    PRIMARY KEY (id)
)


2012-09-25 22:43:21,002 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:21,013 INFO sqlalchemy.engine.base.Engine COMMIT
2012-09-25 22:43:21,014 INFO sqlalchemy.engine.base.Engine CREATE INDEX ix_useraccount_email ON useraccount (email)
2012-09-25 22:43:21,014 INFO sqlalchemy.engine.base.Engine ()
/Users/pete/Workspace/forkful/bcampbell/unsourced/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/default.py:331: Warning: Specified key was too long; max key length is 767 bytes
  cursor.execute(statement, parameters)
2012-09-25 22:43:21,033 INFO sqlalchemy.engine.base.Engine COMMIT
2012-09-25 22:43:21,033 INFO sqlalchemy.engine.base.Engine CREATE UNIQUE INDEX ix_useraccount_username ON useraccount (username)
2012-09-25 22:43:21,033 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:21,049 INFO sqlalchemy.engine.base.Engine COMMIT
2012-09-25 22:43:21,050 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE uploaded_file (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    filename VARCHAR(256) NOT NULL, 
    content_type VARCHAR(128) NOT NULL, 
    uploaded DATETIME NOT NULL, 
    is_img BOOL NOT NULL, 
    width INTEGER, 
    height INTEGER, 
    PRIMARY KEY (id), 
    UNIQUE (filename), 
    CHECK (is_img IN (0, 1))
)


2012-09-25 22:43:21,051 INFO sqlalchemy.engine.base.Engine ()
2012-09-25 22:43:21,051 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "./db_tool", line 36, in <module>
    main()
  File "./db_tool", line 29, in main
    Base.metadata.create_all(engine)
  File "/Users/pete/Workspace/forkful/bcampbell/unsourced/pyenv/lib/python2.7/site-packages/sqlalchemy/schema.py", line 2564, in create_all
    tables=tables)
  File "/Users/pete/Workspace/forkful/bcampbell/unsourced/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2303, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/Users/pete/Workspace/forkful/bcampbell/unsourced/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1973, in _run_visitor
    **kwargs).traverse_single(element)
  File "/Users/pete/Workspace/forkful/bcampbell/unsourced/pyenv/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 106, in traverse_single
    return meth(obj, **kw)
  File "/Users/pete/Workspace/forkful/bcampbell/unsourced/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/ddl.py", line 67, in visit_metadata
    self.traverse_single(table, create_ok=True)
  File "/Users/pete/Workspace/forkful/bcampbell/unsourced/pyenv/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 106, in traverse_single
    return meth(obj, **kw)
  File "/Users/pete/Workspace/forkful/bcampbell/unsourced/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/ddl.py", line 86, in visit_table
    self.connection.execute(schema.CreateTable(table))
  File "/Users/pete/Workspace/forkful/bcampbell/unsourced/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1449, in execute
    params)
  File "/Users/pete/Workspace/forkful/bcampbell/unsourced/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1542, in _execute_ddl
    compiled
  File "/Users/pete/Workspace/forkful/bcampbell/unsourced/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1698, in _execute_context
    context)
  File "/Users/pete/Workspace/forkful/bcampbell/unsourced/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1691, in _execute_context
    context)
  File "/Users/pete/Workspace/forkful/bcampbell/unsourced/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 331, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/pete/Workspace/forkful/bcampbell/unsourced/pyenv/lib/python2.7/site-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/Users/pete/Workspace/forkful/bcampbell/unsourced/pyenv/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
sqlalchemy.exc.OperationalError: (OperationalError) (1071, 'Specified key was too long; max key length is 767 bytes') '\nCREATE TABLE uploaded_file (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tfilename VARCHAR(256) NOT NULL, \n\tcontent_type VARCHAR(128) NOT NULL, \n\tuploaded DATETIME NOT NULL, \n\tis_img BOOL NOT NULL, \n\twidth INTEGER, \n\theight INTEGER, \n\tPRIMARY KEY (id), \n\tUNIQUE (filename), \n\tCHECK (is_img IN (0, 1))\n)\n\n' ()

[bcampbell]

Ahh... It works fine for me, but I think I know what the problem is:
The uploaded_files.filename field is 256 chars, and has a UNIQUE constraint. I think my database is using utf8, so the field will probably take up 256 char max, as long as nobody uploads too many filenames in Chinese :-)
However, it sounds like mysql 5.5 defaults to utf8mb4, which I'd guess is a wider encoding, so the size of the field is pushing past some limit on key sizes. Sigh.
Some clues here:
http://code.google.com/p/sqlalchemy-migrate/issues/detail?id=145

possible cheesy hacks:

• create database with a different encoding
• try reducing the filename size in the UploadedFile class in unsourced/Models.py
  I'd go for the latter.
  This'll probably bite me in future as I upgrade MySQL installs, so I guess the thing is for me to shrink the field down a bit. Let me know if it works and I'll apply it and generate a migration for it.

[Aupajo]

I changed the UploadedFile filename column to an arbitrary 200 chars, and db_tool ran without errors.

I'd push a change, but I don't know what would make a good default, or if there are any side-effects of introducing that change.