yahoo-eng-team team mailing list archive

Thread
Date
[Bug 1543092] Re: glance-manage db purge failure

To: yahoo-eng-team@xxxxxxxxxxxxxxxxxxx
From: OpenStack Infra <1543092@xxxxxxxxxxxxxxxxxx>
Date: Tue, 15 Mar 2016 20:50:51 -0000
Reply-to: Bug 1543092 <1543092@xxxxxxxxxxxxxxxxxx>
Sender: bounces@xxxxxxxxxxxxx
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 <abhishek.kekane@xxxxxxxxxxx>
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
References

[Bug 1543092] [NEW] glance-manage db purge failure
From: Abhishek Kekane, 2016-02-08