Opened 21 months ago

Closed 21 months ago

Last modified 21 months ago

#3421 closed undecided (fixed)

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

Reported by: rutsky Owned by: dustin
Priority: major Milestone: undecided
Version: master Keywords:
Cc:

Description

$ 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.

Change History (7)

comment:1 Changed 21 months ago by 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.

comment:2 Changed 21 months ago by 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.

comment:3 Changed 21 months ago by dustin

  • Owner set to dustin
  • Status changed from new to assigned

comment:4 Changed 21 months ago by dustin

  • master/buildbot/db/enginestrategy.py

    diff --git a/master/buildbot/db/enginestrategy.py b/master/buildbot/db/enginestrategy.py
    index 0bd22af..ee0cc42 100644
    a b class BuildbotEngineStrategy(strategies.ThreadLocalEngineStrategy): 
    129129        charset to be True and 'utf8', failing if they were set to
    130130        anything else."""
    131131
    132132        kwargs['pool_recycle'] = int(u.query.pop('max_idle', 3600))
    133133
    134134        # default to the MyISAM storage engine; InnoDB is not supported
    135135        storage_engine = u.query.pop('storage_engine', 'MyISAM')
    136136        kwargs['connect_args'] = {
    137             'init_command': 'SET storage_engine=%s' % storage_engine,
     137            'init_command': 'SET default_storage_engine=%s' % storage_engine,
    138138        }
    139139
    140140        if 'use_unicode' in u.query:
    141141            if u.query['use_unicode'] != "True":
    142142                raise TypeError("Buildbot requires use_unicode=True " +
    143143                                "(and adds it automatically)")
    144144        else:
    145145            u.query['use_unicode'] = True

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

Last edited 21 months ago by dustin (previous) (diff)

comment:6 Changed 21 months ago by dustin

  • Resolution set to fixed
  • Status changed from assigned to closed

comment:7 Changed 21 months ago by rutsky

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

Note: See TracTickets for help on using tickets.