NotSupportedError when running online_data_migrations on MySQL 5.7

[Michal Dulko]

vagrant@vagrant-ubuntu-trusty-64:~$ cinder-manage db online_data_migrations;
Running batches of 50 until complete.
2017-01-23 12:14:08.695 DEBUG oslo_db.sqlalchemy.engines [req-39f139c1-ee52-4fc8-8782-74f9e1a0662f None None] MySQL server mode set to STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION from (pid=29840) _check_effective_sql_mode /usr/local/lib/python2.7/dist-packages/oslo_db/sqlalchemy/engines.py:261
2017-01-23 12:14:08.878 ERROR oslo_db.sqlalchemy.exc_filters [req-39f139c1-ee52-4fc8-8782-74f9e1a0662f None None] DBAPIError exception wrapped from (pymysql.err.NotSupportedError) (1235, u"This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'") [SQL: u'UPDATE messages SET updated_at=%(updated_at)s, event_id=(concat(%(event_id_1)s, messages.event_id)) WHERE messages.deleted = false AND messages.id IN (SELECT messages.id \nFROM messages \nWHERE messages.deleted = false AND messages.event_id NOT LIKE %(event_id_2)s \n LIMIT %(param_1)s)'] [parameters: {u'param_1': 50, u'event_id_2': 'VOLUME_%', u'event_id_1': 'VOLUME_', 'updated_at': datetime.datetime(2017, 1, 23, 12, 14, 8, 877845)}]
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters Traceback (most recent call last):
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters context)
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters cursor.execute(statement, parameters)
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 166, in execute
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters result = self._query(query)
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 322, in _query
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters conn.query(q)
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 835, in query
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters self._affected_rows = self._read_query_result(unbuffered=unbuffered)
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1019, in _read_query_result
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters result.read()
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1302, in read
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters first_packet = self.connection._read_packet()
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 981, in _read_packet
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters packet.check_error()
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 393, in check_error
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters err.raise_mysql_exception(self._data)
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 107, in raise_mysql_exception
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters raise errorclass(errno, errval)
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters NotSupportedError: (1235, u"This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'")
2017-01-23 12:14:08.878 TRACE oslo_db.sqlalchemy.exc_filters
Error attempting to run migrate_add_message_prefix

This is because the migration query is using both LIMIT and _IN, which isn't supported in MySQL 5.7. It should be modified to an alternative query.

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/424103
Committed: https://git.openstack.org/cgit/openstack/cinder/commit/?id=f0f46e5003a90fd9c79a3b70f917e32da6c3765c
Submitter: Jenkins
Branch: master

commit f0f46e5003a90fd9c79a3b70f917e32da6c3765c
Author: Michał Dulko <email address hidden>
Date: Mon Jan 23 13:53:27 2017 +0100

    Break migrate_add_message_prefix into two queries

    Turns out MySQL 5.7 (version installed in the gate) does not support
    subqueries with LIMIT clause used to populate IN predicate in outer
    query. This manifests through NotSupportedError exception.

    This commit works around the problem by splitting the migration into two
    separate queries (one to get IDs, and second to do the update).

    Change-Id: I3bd749de0486eb44b68d6d59323141a60338f1df
    Closes-Bug: 1658667

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/cinder 10.0.0.0b3 development milestone.