Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Database seeding not working #7

Open
Aupajo opened this issue Sep 25, 2012 · 2 comments
Open

Database seeding not working #7

Aupajo opened this issue Sep 25, 2012 · 2 comments

Comments

@Aupajo
Copy link
Contributor

Aupajo commented Sep 25, 2012

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
Copy link
Owner

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
Copy link
Contributor Author

Aupajo commented Sep 30, 2012

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@Aupajo @bcampbell