OperationalError "Unknown system variable 'storage_engine'" when using MySQL 5.7

[rutsky]

$ pip install MySQL-python
$ sudo docker run --name bb-test-mysql -e MYSQL_ROOT_PASSWORD=password \
       -p 127.0.0.1:13306:3306 -d mysql:5.7
$ BUILDBOT_TEST_DB_URL=mysql+mysqldb://root:password@127.0.0.1:13306/mysql trial --reporter=text -x buildbot.test.integration.test_upgrade.TestWeirdChanges.testUpgradeChangeNoRevision
E
===============================================================================
[ERROR]
Traceback (most recent call last):
  File "/home/bob/stuff/buildbot/env-master/local/lib/python2.7/site-packages/twisted/python/threadpool.py", line 246, in inContext
    result = inContext.theWork()
  File "/home/bob/stuff/buildbot/env-master/local/lib/python2.7/site-packages/twisted/python/threadpool.py", line 262, in <lambda>
    inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
  File "/home/bob/stuff/buildbot/env-master/local/lib/python2.7/site-packages/twisted/python/context.py", line 118, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/home/bob/stuff/buildbot/env-master/local/lib/python2.7/site-packages/twisted/python/context.py", line 81, in callWithContext
    return func(*args,**kw)
  File "/home/bob/stuff/buildbot/buildbot-master/master/buildbot/db/pool.py", line 186, in __thd
    arg = self.engine.contextual_connect()
  File "/home/bob/stuff/buildbot/env-master/local/lib/python2.7/site-packages/sqlalchemy/engine/threadlocal.py", line 55, in contextual_connect
    connection = self._tl_connection_cls(self, self.pool.connect(), **kw)
  File "/home/bob/stuff/buildbot/env-master/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 236, in connect
    return _ConnectionFairy(self).checkout()
  File "/home/bob/stuff/buildbot/env-master/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 401, in __init__
    rec = self._connection_record = pool._do_get()
  File "/home/bob/stuff/buildbot/env-master/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 746, in _do_get
    con = self._create_connection()
  File "/home/bob/stuff/buildbot/env-master/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 189, in _create_connection
    return _ConnectionRecord(self)
  File "/home/bob/stuff/buildbot/env-master/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 282, in __init__
    self.connection = self.__connect()
  File "/home/bob/stuff/buildbot/env-master/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 344, in __connect
    connection = self.__pool._creator()
  File "/home/bob/stuff/buildbot/env-master/local/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 80, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/bob/stuff/buildbot/env-master/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 281, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/bob/stuff/buildbot/env-master/local/lib/python2.7/site-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/home/bob/stuff/buildbot/env-master/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 193, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (OperationalError) (1193, "Unknown system variable 'storage_engine'") None None

buildbot.test.integration.test_upgrade.TestWeirdChanges.testUpgradeChangeNoRevision
-------------------------------------------------------------------------------
Ran 1 tests in 0.015s

FAILED (errors=1)

I tested with MySQL 5.7 image from Docker.

IMO it's either SQLAlchemy doesn't play nicely with MySQL 5.7, or default MySQL image from Docker requires additional configuration (however with MySQL 5.5 all works fine). Needs further investigation.

[dustin]

I saw this too. The "storage_engine" bit is something Buildbot tries to set (since it's not compatible with InnoDB). Apparently MySQL removed the option to set that option on a per-connection basis.

[tardyp]

This is related to this bug:

​http://trac.buildbot.net/ticket/2634

also, google found confluence has similar bug: ​https://confluence.atlassian.com/display/CONFKB/Confluence+fails+to+start+with+error+'Unknown+system+variable+'storage_engine''+using+MySQL+5.7.x

Cause MySQL 5.7.x uses InnoDB by default, and has removed the storage_engine system variable.

[dustin]

master/buildbot/db/enginestrategy.py

@@ -129 +129 @@
         charset to be True and 'utf8', failing if they were set to
         anything else."""
 
         kwargs['pool_recycle'] = int(u.query.pop('max_idle', 3600))
 
         # default to the MyISAM storage engine; InnoDB is not supported
         storage_engine = u.query.pop('storage_engine', 'MyISAM')
         kwargs['connect_args'] = {
-            'init_command': 'SET storage_engine=%s' % storage_engine,
+            'init_command': 'SET default_storage_engine=%s' % storage_engine,
         }
 
         if 'use_unicode' in u.query:
             if u.query['use_unicode'] != "True":
                 raise TypeError("Buildbot requires use_unicode=True " +
                                 "(and adds it automatically)")
         else:
             u.query['use_unicode'] = True

seems to help, but the warnings about blob columns having default values are now errors :(

[dustin]

​https://github.com/buildbot/buildbot/pull/1968

[rutsky]

I can confirm that now tests are passing on MySQL 5.7.