No longer able to delete security group rules in kilo

[Andrew Bogott]

Security groups and rules worked fine in Juno, but ever since my upgrade to Kilo I'm unable to delete rules.

andrew@labcontrol1001:~$ nova secgroup-delete-rule default tcp 666 666 10.0.0.0/8
+-------------+-----------+---------+------------+--------------+
| IP Protocol | From Port | To Port | IP Range | Source Group |
+-------------+-----------+---------+------------+--------------+
| tcp | 666 | 666 | 10.0.0.0/8 | |
+-------------+-----------+---------+------------+--------------+
ERROR (ClientException): The server has either erred or is incapable of performing the requested operation. (HTTP 500)

Here's the interesting bit of the stack trace:

2015-09-16 21:34:31.105 3179 TRACE nova.api.openstack File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
2015-09-16 21:34:31.105 3179 TRACE nova.api.openstack context)
2015-09-16 21:34:31.105 3179 TRACE nova.api.openstack File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 436, in do_execute
2015-09-16 21:34:31.105 3179 TRACE nova.api.openstack cursor.execute(statement, parameters)
2015-09-16 21:34:31.105 3179 TRACE nova.api.openstack File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
2015-09-16 21:34:31.105 3179 TRACE nova.api.openstack self.errorhandler(self, exc, value)
2015-09-16 21:34:31.105 3179 TRACE nova.api.openstack File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
2015-09-16 21:34:31.105 3179 TRACE nova.api.openstack raise errorclass, errorvalue
2015-09-16 21:34:31.105 3179 TRACE nova.api.openstack DBError: (DataError) (1264, "Out of range value for column 'deleted' at row 1") 'UPDATE security_group_rules SET updated_at=updated_at, deleted_at=%s, deleted=id WHERE security_group_rules.deleted = %s AND security_group_rules.id = %s' (datetime.datetime(2015, 9, 16, 21, 34, 31, 99896), 0, 2769L)

I've no doubt that this feature works in a fresh install of Kilo, and that my db schema is messed up due to a faulty upgrade script.

[Matt Riedemann (mriedem)]

This is the full trace:

http://paste.openstack.org/show/473769/

[Matt Riedemann (mriedem)]

This is the row from the DB:

https://dpaste.de/xvff

An id of 2769 should be fine, I don't understand why you'd get a 1264 error from mysql here.

Can you dump the schema for your security_group_rules table?

[Matt Riedemann (mriedem)]

This is Andrew's table schema:

https://dpaste.de/F7CT

We can see that the id column is int and the deleted column is tinyint, so that's why we're seeing the 1264 error.

[Matt Riedemann (mriedem)]

As we can see here:

https://dev.mysql.com/doc/refman/5.0/en/integer-types.html

A value of 2769 isn't going to fit into a tinyint column.

It's interesting that you still have tables with deleted defined as tinyint, since that would probably predate the soft-deleted stuff that's been in the data model for a long time.

In fact, it was db migration 152 back in grizzly that converted the type on the deleted column:

https://github.com/openstack/nova/commit/28191baa2b1a65fb86bbbbfc0974495059db6d42

[Matt Riedemann (mriedem)]

Marked as invalid. There could have been a bug in the 152 database migration which failed to convert the type on this schema from tinyint to int, but given that's been rolled into the 216 migration in icehouse and this looks correct:

https://github.com/openstack/nova/blob/icehouse-eol/nova/db/sqlalchemy/migrate_repo/versions/216_havana.py#L883

I don't think there is anything we can actually fix in the code.