federated_user table failed db unit test if db engine is MyISAM

[Guang Yee]

094_add_federated_user_table.py failed functional test if the default db engine is MyISAM for MySQL. We need to follow the established pattern of adding the following

mysql_engine='InnoDB',
mysql_charset='utf8'

to the script during table creation.

Here's an example of one of the failures.

keystone.tests.unit.test_sql_upgrade.MySQLOpportunisticUpgradeTestCase.test_migration_96_constraint_exists
----------------------------------------------------------------------------------------------------------

Captured traceback:
~~~~~~~~~~~~~~~~~~~
    Traceback (most recent call last):
      File "keystone/tests/unit/test_sql_upgrade.py", line 1113, in test_migration_96_constraint_exists
        self.upgrade(95)
      File "keystone/tests/unit/test_sql_upgrade.py", line 262, in upgrade
        self._migrate(*args, **kwargs)
      File "keystone/tests/unit/test_sql_upgrade.py", line 276, in _migrate
        self.schema_.runchange(ver, change, changeset.step)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/migrate/versioning/schema.py", line 93, in runchange
        change.run(self.engine, step)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/migrate/versioning/script/py.py", line 148, in run
        script_func(engine)
      File "/home/gyee/projects/keystone/keystone/common/sql/migrate_repo/versions/094_add_federated_user_table.py", line 39, in upgrade
        federated_table.create(migrate_engine, checkfirst=True)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 725, in create
        checkfirst=checkfirst)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1856, in _run_visitor
        conn._run_visitor(visitorcallable, element, **kwargs)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1481, in _run_visitor
        **kwargs).traverse_single(element)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
        return meth(obj, **kw)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 764, in visit_table
        include_foreign_key_constraints=include_foreign_key_constraints
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
        return meth(self, multiparams, params)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
        return connection._execute_ddl(self, multiparams, params)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 968, in _execute_ddl
        compiled
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
        context)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1337, in _handle_dbapi_exception
        util.raise_from_cause(newraise, exc_info)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
        reraise(type(exception), exception, tb=exc_tb, cause=cause)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
        context)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
        cursor.execute(statement, parameters)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/pymysql/cursors.py", line 161, in execute
        result = self._query(query)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/pymysql/cursors.py", line 317, in _query
        conn.query(q)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/pymysql/connections.py", line 835, in query
        self._affected_rows = self._read_query_result(unbuffered=unbuffered)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/pymysql/connections.py", line 1019, in _read_query_result
        result.read()
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/pymysql/connections.py", line 1302, in read
        first_packet = self.connection._read_packet()
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/pymysql/connections.py", line 981, in _read_packet
        packet.check_error()
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/pymysql/connections.py", line 393, in check_error
        err.raise_mysql_exception(self._data)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/pymysql/err.py", line 120, in raise_mysql_exception
        _check_mysql_exception(errinfo)
      File "/home/gyee/projects/keystone/.tox/py27/local/lib/python2.7/site-packages/pymysql/err.py", line 115, in _check_mysql_exception
        raise InternalError(errno, errorvalue)
    oslo_db.exception.DBError: (pymysql.err.InternalError) (1071, u'Specified key was too long; max key length is 1000 bytes') [SQL: u'\nCREATE TABLE federated_user (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tuser_id VARCHAR(64) NOT NULL, \n\tidp_id VARCHAR(64) NOT NULL, \n\tprotocol_id VARCHAR(64) NOT NULL, \n\tunique_id VARCHAR(255) NOT NULL, \n\tdisplay_name VARCHAR(255), \n\tPRIMARY KEY (id), \n\tUNIQUE (idp_id, protocol_id, unique_id), \n\tFOREIGN KEY(user_id) REFERENCES user (id) ON DELETE CASCADE, \n\tFOREIGN KEY(idp_id) REFERENCES identity_provider (id) ON DELETE CASCADE\n)\n\n']

[Guang Yee (guang-yee)]

Steps to reproduce the failure on Ubuntu 14.04.

1) install mysql
2) create 'openstack_citest' user and grant the appropriate permissions

mysql> create user 'openstack_citest'@'localhost' identified by 'openstack_citest';
mysql> grant all on keystone.* to 'openstack_citest';

3) check to see if default-store-engine is set to "MyISAM" in /etc/mysql/my.conf

[mysqld]
default-storage-engine = MyISAM

4) run the MySQLOpportunisticUpgradeTestCase test suite to see the failures

tox -e py27 keystone.tests.unit.test_sql_upgrade.MySQLOpportunisticUpgradeTestCase

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/331872

[Steve Martinelli (stevemar)]

this isnt a functional test

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/331872
Committed: https://git.openstack.org/cgit/openstack/keystone/commit/?id=48ccf751ac726638db7aeb8288c457388e870ea9
Submitter: Jenkins
Branch: master

commit 48ccf751ac726638db7aeb8288c457388e870ea9
Author: guang-yee <email address hidden>
Date: Mon Jun 20 15:20:53 2016 -0700

    Make sure to use InnoDB as the DB engine

    For certain Fedora based systems the MySQL default DB engine is MyISAM.
    Therefore, the MySQLOpportunisticUpgradeTestCase functional test suite
    will fail because of MyISAM's known limitations. We've already established
    a pattern to mitigate this problem, which is to simply force the upgrade
    scripts to use InnoDB engine explicitly.

    Change-Id: Idd20ea7f0f0a41c01d62b8730c7bd4ed6ecd6486
    Closes-Bug: 1594592

[Davanum Srinivas (DIMS) (dims-v)]

This issue was fixed in the openstack/keystone 10.0.0.0b2 development milestone.