Glance

glance-manage db purge failure

Bug #1543092 reported by Abhishek Kekane
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Glance
Fix Released
Undecided
Abhishek Kekane

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

See original description
Abhishek Kekane (abhishek-kekane)
Changed in glance:
assignee: nobody → Abhishek Kekane (abhishek-kekane)
Abhishek Kekane (abhishek-kekane)
description: updated
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to glance (master)

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

Changed in glance:
status: New → In Progress
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to glance (master)

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 address hidden>
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
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.