frontera with mysql backend, error (create table, utf8, 'key was too long')

[Alexey Lemeshevski]

Hello!

Sorry for my bad english.

in:

frontera/contrib/backends/sqlalchemy/models.py

    __table_args__ = (
        {
            'mysql_charset': 'utf8',
            'mysql_engine': 'InnoDB',
            'mysql_row_format': 'DYNAMIC',
        },
    )

and:

    key = Column(String(256), primary_key=True, nullable=False)

But, when utf8 is utf8mb4 (4 bytes), we have only "191" for key length.

So, we have error:

Traceback (most recent call last):
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1071, 'Specified key was too long; max key length is 767 bytes')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/lib/python3.5/runpy.py", line 193, in _run_module_as_main
    "__main__", mod_spec)
  File "/usr/lib/python3.5/runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/frontera/utils/add_seeds.py", line 44, in <module>
    run_add_seeds(settings, args.seeds_file)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/frontera/utils/add_seeds.py", line 19, in run_add_seeds
    manager = LocalFrontierManager.from_settings(settings)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/frontera/core/manager.py", line 402, in from_settings
    canonicalsolver=manager_settings.CANONICAL_SOLVER)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/frontera/core/manager.py", line 362, in __init__
    db_worker=False, strategy_worker=False)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/frontera/core/manager.py", line 157, in __init__
    super(StrategyComponentsPipelineMixin, self).__init__(backend, **kwargs)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/frontera/core/manager.py", line 87, in __init__
    BackendMixin.__init__(self, backend, db_worker, strategy_worker)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/frontera/core/manager.py", line 20, in __init__
    self._backend = self._load_backend(backend, db_worker, strategy_worker)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/frontera/core/manager.py", line 32, in _load_backend
    return cls.local(self)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/frontera/contrib/backends/sqlalchemy/__init__.py", line 79, in local
    b._init_strategy_worker(manager)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/frontera/contrib/backends/sqlalchemy/__init__.py", line 47, in _init_strategy_worker
    self.check_and_create_tables(drop_all_tables, clear_content, (model_states, model_dm))
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/frontera/contrib/backends/sqlalchemy/__init__.py", line 35, in check_and_create_tables
    model.__table__.create(bind=self.engine)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 925, in create
    bind._run_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2095, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1656, in _run_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 145, in traverse_single
    return meth(obj, **kw)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 832, in visit_table
    include_foreign_key_constraints,  # noqa
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1073, in _execute_ddl
    compiled,
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1317, in _execute_context
    e, statement, parameters, cursor, context
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1511, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/sa_run/dev-venv/lib/python3.5/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1071, 'Specified key was too long; max key length is 767 bytes')
[SQL:
CREATE TABLE domain_metadata (
        `key` VARCHAR(256) NOT NULL,
        value BLOB,
        PRIMARY KEY (`key`)
)ROW_FORMAT=DYNAMIC ENGINE=InnoDB CHARSET=utf8

]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

p.s.

I'am rewrite string as: key = Column(String(191), primary_key=True, nullable=False) ...but, may be need to fix this in code on github.