<div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div>Hi</div><div><br></div><div>Fron time to time I use to move entries related to deleted instances to shadow tables, using the command:</div><div><br></div><div>nova-manage db archive_deleted_rows<br></div><div><br></div><div>This is now failing [*] for the instance_metadata table because of a 'duplicate entry for the primary key' problem:</div><div><br></div><div><div>DBDuplicateEntry: (pymysql.err.IntegrityError) (1062, u"Duplicate entry '6' for key 'PRIMARY'") [SQL: u'INSERT INTO shadow_instance_metadata (created_at, updated_at, deleted_at, deleted, id, `key`, value, instance_uuid) SELECT instance_metadata.created_at, instance_metadata.updated_at, instance_metadata.deleted_at, instance_metadata.deleted, <a href="http://instance_metadata.id">instance_metadata.id</a>, instance_metadata.`key`, instance_metadata.value, instance_metadata.instance_uuid \nFROM instance_metadata \nWHERE instance_metadata.deleted != %(deleted_1)s ORDER BY <a href="http://instance_metadata.id">instance_metadata.id</a> \n LIMIT %(param_1)s'] [parameters: {u'param_1': 1, u'deleted_1': 0}]</div></div><div><br></div><div><br></div><div>Indeed:</div><div><br></div><div><div>mysql> SELECT instance_metadata.created_at, instance_metadata.updated_at, instance_metadata.deleted_at, instance_metadata.deleted, <a href="http://instance_metadata.id">instance_metadata.id</a>, instance_metadata.`key`, instance_metadata.value, instance_metadata.instance_uuid FROM instance_metadata WHERE instance_metadata.deleted != 0 ORDER BY <a href="http://instance_metadata.id">instance_metadata.id</a> limit 1;</div><div>+---------------------+------------+---------------------+---------+----+-------+-------+--------------------------------------+</div><div>| created_at          | updated_at | deleted_at          | deleted | id | key   | value | instance_uuid                        |</div><div>+---------------------+------------+---------------------+---------+----+-------+-------+--------------------------------------+</div><div>| 2018-09-20 07:40:56 | NULL       | 2018-09-20 07:54:26 |       6 |  6 | group | node  | a9000ff7-2298-454c-bf71-9e3c62ec0f3c |</div><div>+---------------------+------------+---------------------+---------+----+-------+-------+--------------------------------------+</div><div>1 row in set (0.00 sec)</div><div><br></div><div><br></div><div>But there is a 5-years old entry (if I am not wrong we were running Havana at that time) in the shadow table with that id:</div><div><br></div><div>mysql> select * from shadow_instance_metadata where id='6';</div><div>+---------------------+------------+---------------------+----+----------+---------------------+--------------------------------------+---------+</div><div>| created_at          | updated_at | deleted_at          | id | key      | value               | instance_uuid                        | deleted |</div><div>+---------------------+------------+---------------------+----+----------+---------------------+--------------------------------------+---------+</div><div>| 2014-11-04 12:57:10 | NULL       | 2014-11-04 13:06:45 |  6 | director | microbosh-openstack | 5db5b17b-69f2-4f0a-bdd2-efe710268021 |       6 |</div><div>+---------------------+------------+---------------------+----+----------+---------------------+--------------------------------------+---------+</div><div>1 row in set (0.00 sec)</div><div><br></div><div>mysql> </div></div><div><br></div><div><br></div><div>I wonder how could that happen.</div><div><br></div><div>Can I simply remove that entry from the shadow table (I am not really interested to keep it) or are there better (cleaner) way to fix the problem ?</div><div><br></div><div><br></div><div>This Cloud is now running Ocata</div><div><br></div><div>Thanks, Massimo</div><div><br></div><div><br></div><div><br></div><div>[*]</div><div>[root@cld-ctrl-01 ~]# nova-manage db archive_deleted_rows --max_rows 1000 --verbose</div><div>An error has occurred:</div><div>Traceback (most recent call last):</div><div>  File "/usr/lib/python2.7/site-packages/nova/cmd/manage.py", line 1617, in main</div><div>    ret = fn(*fn_args, **fn_kwargs)</div><div>  File "/usr/lib/python2.7/site-packages/nova/cmd/manage.py", line 691, in archive_deleted_rows</div><div>    run = db.archive_deleted_rows(max_rows)</div><div>  File "/usr/lib/python2.7/site-packages/nova/db/api.py", line 2040, in archive_deleted_rows</div><div>    return IMPL.archive_deleted_rows(max_rows=max_rows)</div><div>  File "/usr/lib/python2.7/site-packages/nova/db/sqlalchemy/api.py", line 6564, in archive_deleted_rows</div><div>    tablename, max_rows=max_rows - total_rows_archived)</div><div>  File "/usr/lib/python2.7/site-packages/nova/db/sqlalchemy/api.py", line 6513, in _archive_deleted_rows_for_table</div><div>    conn.execute(insert)</div><div>  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute</div><div>    return meth(self, multiparams, params)</div><div>  File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection</div><div>    return connection._execute_clauseelement(self, multiparams, params)</div><div>  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement</div><div>    compiled_sql, distilled_params</div><div>  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context</div><div>    context)</div><div>  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1337, in _handle_dbapi_exception</div><div>    util.raise_from_cause(newraise, exc_info)</div><div>  File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause</div><div>    reraise(type(exception), exception, tb=exc_tb)</div><div>  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context</div><div>    context)</div><div>  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute</div><div>    cursor.execute(statement, parameters)</div><div>  File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 165, in execute</div><div>    result = self._query(query)</div><div>  File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 321, in _query</div><div>    conn.query(q)</div><div>  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 860, in query</div><div>    self._affected_rows = self._read_query_result(unbuffered=unbuffered)</div><div>  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1061, in _read_query_result</div><div>    result.read()</div><div>  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1349, in read</div><div>    first_packet = self.connection._read_packet()</div><div>  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1018, in _read_packet</div><div>    packet.check_error()</div><div>  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 384, in check_error</div><div>    err.raise_mysql_exception(self._data)</div><div>  File "/usr/lib/python2.7/site-packages/pymysql/err.py", line 107, in raise_mysql_exception</div><div>    raise errorclass(errno, errval)</div><div>DBDuplicateEntry: (pymysql.err.IntegrityError) (1062, u"Duplicate entry '6' for key 'PRIMARY'") [SQL: u'INSERT INTO shadow_instance_metadata (created_at, updated_at, deleted_at, deleted, id, `key`, value, instance_uuid) SELECT instance_metadata.created_at, instance_metadata.updated_at, instance_metadata.deleted_at, instance_metadata.deleted, <a href="http://instance_metadata.id">instance_metadata.id</a>, instance_metadata.`key`, instance_metadata.value, instance_metadata.instance_uuid \nFROM instance_metadata \nWHERE instance_metadata.deleted != %(deleted_1)s ORDER BY <a href="http://instance_metadata.id">instance_metadata.id</a> \n LIMIT %(param_1)s'] [parameters: {u'param_1': 1, u'deleted_1': 0}]</div><div><br></div><div>[root@cld-ctrl-01 ~]# </div><div><br></div></div></div></div></div></div></div></div>