Reviewed: https://review.openstack.org/278870 Committed: https://git.openstack.org/cgit/openstack/glance/commit/?id=79c602507c93fab04fc77fcc4ec329e6527331ad Submitter: Jenkins Branch: master
commit 79c602507c93fab04fc77fcc4ec329e6527331ad Author: Abhishek Kekane <[email protected]> Date: Mon Feb 8 05:33:18 2016 -0800 glance-manage db purge failure for limit MySQL does not support LIMIT in subqueries for certain subquery operators. Ref :- https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.7/en/subquery-restrictions.html Using nested select statements to resolve this issue. NOTE: Added order by clause on 'deleted_at' column so that old records will be deleted first. Closes-bug: #1543092 Change-Id: Id90a6686bed60dea74644057c3929643a4c1d201 ** Changed in: glance Status: In Progress => Fix Released -- You received this bug notification because you are a member of Yahoo! Engineering Team, which is subscribed to Glance. https://bugs.launchpad.net/bugs/1543092 Title: glance-manage db purge failure Status in Glance: Fix Released Bug description: While running glance-manage db purge command, it fail with error "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'" $ glance-manage db purge 1 2016-02-05 01:46:01.902 DEBUG oslo_db.sqlalchemy.engines [req-1cfabb0d-e775-44ea-b253-d134b0abb303 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=26959) _check_effective_sql_mode /usr/local/lib/python2.7/dist-packages/oslo_db/sqlalchemy/engines.py:256 2016-02-05 01:46:02.340 INFO glance.db.sqlalchemy.api [req-1cfabb0d-e775-44ea-b253-d134b0abb303 None None] Purging deleted rows older than %(age_in_days)d day(s) from table %(tbl)s 2016-02-05 01:46:02.417 ERROR oslo_db.sqlalchemy.exc_filters [req-1cfabb0d-e775-44ea-b253-d134b0abb303 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'DELETE FROM image_tags WHERE image_tags.id IN (SELECT image_tags.id \nFROM image_tags \nWHERE image_tags.deleted_at < %(deleted_at_1)s \n LIMIT %(param_1)s)'] [parameters: {u'deleted_at_1': datetime.datetime(2016, 2, 4, 9, 46, 1, 906022), u'param_1': 100}] 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters Traceback (most recent call last): 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters context) 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters cursor.execute(statement, parameters) 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 146, in execute 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters result = self._query(query) 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 296, in _query 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters conn.query(q) 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 819, in query 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters self._affected_rows = self._read_query_result(unbuffered=unbuffered) 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1001, in _read_query_result 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters result.read() 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1285, in read 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters first_packet = self.connection._read_packet() 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 965, in _read_packet 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters packet.check_error() 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 394, in check_error 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters err.raise_mysql_exception(self._data) 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 120, in raise_mysql_exception 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters _check_mysql_exception(errinfo) 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 112, in _check_mysql_exception 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters raise errorclass(errno, errorvalue) 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters NotSupportedError: (1235, u"This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'") 2016-02-05 01:46:02.417 TRACE oslo_db.sqlalchemy.exc_filters 2016-02-05 01:46:02.468 CRITICAL glance [req-1cfabb0d-e775-44ea-b253-d134b0abb303 None None] DBError: (pymysql.err.NotSupportedError) (1235, u"This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'") [SQL: u'DELETE FROM image_tags WHERE image_tags.id IN (SELECT image_tags.id \nFROM image_tags \nWHERE image_tags.deleted_at < %(deleted_at_1)s \n LIMIT %(param_1)s)'] [parameters: {u'deleted_at_1': datetime.datetime(2016, 2, 4, 9, 46, 1, 906022), u'param_1': 100}] 2016-02-05 01:46:02.468 TRACE glance Traceback (most recent call last): 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/bin/glance-manage", line 10, in <module> 2016-02-05 01:46:02.468 TRACE glance sys.exit(main()) 2016-02-05 01:46:02.468 TRACE glance File "/opt/stack/glance/glance/cmd/manage.py", line 347, in main 2016-02-05 01:46:02.468 TRACE glance return CONF.command.action_fn(*func_args, **func_kwargs) 2016-02-05 01:46:02.468 TRACE glance File "/opt/stack/glance/glance/cmd/manage.py", line 168, in purge 2016-02-05 01:46:02.468 TRACE glance db_api.purge_deleted_rows(ctx, age_in_days, max_rows) 2016-02-05 01:46:02.468 TRACE glance File "/opt/stack/glance/glance/db/sqlalchemy/api.py", line 1290, in purge_deleted_rows 2016-02-05 01:46:02.468 TRACE glance ).limit(max_rows) 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 1046, in execute 2016-02-05 01:46:02.468 TRACE glance bind, close_with_result=True).execute(clause, params or {}) 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 914, in execute 2016-02-05 01:46:02.468 TRACE glance return meth(self, multiparams, params) 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection 2016-02-05 01:46:02.468 TRACE glance return connection._execute_clauseelement(self, multiparams, params) 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement 2016-02-05 01:46:02.468 TRACE glance compiled_sql, distilled_params 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context 2016-02-05 01:46:02.468 TRACE glance context) 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1337, in _handle_dbapi_exception 2016-02-05 01:46:02.468 TRACE glance util.raise_from_cause(newraise, exc_info) 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause 2016-02-05 01:46:02.468 TRACE glance reraise(type(exception), exception, tb=exc_tb) 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context 2016-02-05 01:46:02.468 TRACE glance context) 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute 2016-02-05 01:46:02.468 TRACE glance cursor.execute(statement, parameters) 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 146, in execute 2016-02-05 01:46:02.468 TRACE glance result = self._query(query) 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 296, in _query 2016-02-05 01:46:02.468 TRACE glance conn.query(q) 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 819, in query 2016-02-05 01:46:02.468 TRACE glance self._affected_rows = self._read_query_result(unbuffered=unbuffered) 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1001, in _read_query_result 2016-02-05 01:46:02.468 TRACE glance result.read() 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1285, in read 2016-02-05 01:46:02.468 TRACE glance first_packet = self.connection._read_packet() 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 965, in _read_packet 2016-02-05 01:46:02.468 TRACE glance packet.check_error() 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 394, in check_error 2016-02-05 01:46:02.468 TRACE glance err.raise_mysql_exception(self._data) 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 120, in raise_mysql_exception 2016-02-05 01:46:02.468 TRACE glance _check_mysql_exception(errinfo) 2016-02-05 01:46:02.468 TRACE glance File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 112, in _check_mysql_exception 2016-02-05 01:46:02.468 TRACE glance raise errorclass(errno, errorvalue) 2016-02-05 01:46:02.468 TRACE glance DBError: (pymysql.err.NotSupportedError) (1235, u"This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'") [SQL: u'DELETE FROM image_tags WHERE image_tags.id IN (SELECT image_tags.id \nFROM image_tags \nWHERE image_tags.deleted_at < %(deleted_at_1)s \n LIMIT %(param_1)s)'] [parameters: {u'deleted_at_1': datetime.datetime(2016, 2, 4, 9, 46, 1, 906022), u'param_1': 100}] 2016-02-05 01:46:02.468 TRACE glance To manage notifications about this bug go to: https://bugs.launchpad.net/glance/+bug/1543092/+subscriptions -- Mailing list: https://launchpad.net/~yahoo-eng-team Post to : [email protected] Unsubscribe : https://launchpad.net/~yahoo-eng-team More help : https://help.launchpad.net/ListHelp
