The DB API is inconsistent about returning sub-second resolution timestamps

[Rushi Agrawal]

While creating volume, the return value says that volume creation time is dd-mm-yyThh:mm:ss.sssss, but while listing volumes at a later point of time, it changes to dd-mm-yyThh:mm:ss.00000

Doesn't look like a big issue though, but nevertheless shouldn't happen. I'd say the simplest solution should be to return dd-mm-yyThh:mm:ss.00000 at the create-time itself.

[Juan Manuel Ollé (juan-m-olle)]

This bug seams to be a MySQL Bug,

Bug in MySQL: http://bugs.mysql.com/bug.php?id=8523

The same impacting Keystone, for more reference:

https://bugs.launchpad.net/keystone/+bug/1246383

[Leandro Ignacio Costantino (leandro-i-costantino)]

I think this belong to Cinder actually.
Nova is just using cinder client to retrieve the output.

The incorrect format is due to timeutils.utc_now() getting subseconds, while the DB is not saving it in the same format.

[Openstack Gerrit (openstack-gerrit)]

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

[Mark McLoughlin (markmc)]

This is a fairly broad problem which isn't just limited to the EC2 API

See:

  http://lists.openstack.org/pipermail/openstack-dev/2014-April/033971.html

  - Whether we store sub-second resolution timestamps in the database
    appears to be database specific. In my quick tests, we store that
    information in sqlite but not MySQL.

  - However, timestamps added by SQLAlchemy when you do e.g. save() do
    include sub-second information, so some DB API calls may return
    sub-second timestamps even when that information isn't stored in
    the database.

If we are to address this, I think we should do it some sort of global way.

e.g. detect whether the database will correctly store and return sub-second timestamps and, if not, strip the sub-second information from all timestamps returned by the DB layer

[OpenStack Infra (hudson-openstack)]

Change abandoned by Mark McLoughlin (<email address hidden>) on branch: master
Review: https://review.openstack.org/92322
Reason: Looks like work on this has stalled. Feel free to restore the change if and when there's further progress. Thanks!

[Andrey Pavlov (apavlov-e)]

this is not ec2 bug. it's cinder's bug -

(0):~/$ cinder create 1
+---------------------+--------------------------------------+
| Property | Value |
+---------------------+--------------------------------------+
| attachments | [] |
| availability_zone | nova |
| bootable | false |
| created_at | 2015-04-17T13:26:40.625216 |
| display_description | None |
| display_name | None |
| encrypted | False |
| id | 123bcee1-8fdf-4b8b-b967-d4e444c50bfc |
| metadata | {} |
| multiattach | false |
| size | 1 |
| snapshot_id | None |
| source_volid | None |
| status | creating |
| volume_type | lvmdriver-1 |
+---------------------+--------------------------------------+
(0):~/$ cinder show 123bcee1-8fdf-4b8b-b967-d4e444c50bfc
+---------------------------------------+--------------------------------------+
| Property | Value |
+---------------------------------------+--------------------------------------+
| attachments | [] |
| availability_zone | nova |
| bootable | false |
| created_at | 2015-04-17T13:26:40.000000 |
| display_description | None |
| display_name | None |
| encrypted | False |
| id | 123bcee1-8fdf-4b8b-b967-d4e444c50bfc |
| metadata | {} |
| multiattach | false |
| os-vol-host-attr:host | grizzly@lvmdriver-1#lvmdriver-1 |
| os-vol-mig-status-attr:migstat | None |
| os-vol-mig-status-attr:name_id | None |
| os-vol-tenant-attr:tenant_id | 936ac6b47d33432b9f698e5dc82c32be |
| os-volume-replication:driver_data | None |
| os-volume-replication:extended_status | None |
| size | 1 |
| snapshot_id | None |
| source_volid | None |
| status | available |
| vo...