Description of problem: When importing a system CSV file, if a row contains an error Beaker does not roll back the database transaction and so changes to system_status_duration are committed. This leaves the system_status_duration table in an inconsistent state (no row with NULL finish_time) which subsequently causes exceptions like this when changing the system status: cherrypy.msg INFO HTTP: Traceback (most recent call last): File "/usr/lib/python2.6/site-packages/CherryPy-2.3.0-py2.6.egg/cherrypy/_cphttptools.py", line 121, in _run self.main() ... File "/usr/lib/python2.6/site-packages/bkr/server/controllers.py", line 1341, in save system.status = kw['status'] File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/attributes.py", line 159, in __set__ instance_dict(instance), value, None) File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/attributes.py", line 466, in set value, old, initiator) File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/attributes.py", line 472, in fire_replace_event value = ext.set(state, value, previous, initiator or self) File "/usr/lib/python2.6/site-packages/bkr/server/model/inventory.py", line 192, in set assert open_sd.finish_time is None AssertionError: <bound method Root.save of <bkr.server.controllers.Root object at 0x7f7c2364a710>> Version-Release number of selected component (if applicable): 0.16.1 How reproducible: always Steps to Reproduce: 1. Upload a CSV file which changes system status and also contains an error (for example 'secret' column with no value): csv_type,id,status,secret system,1,Automated, 2. CSV fails to import with "Invalid secret None" (this badly worded message means that the 'secret' column does not accept empty string) 3. Check system_status_duration for this system Actual results: mysql> select * from system_status_duration where system_id = 1; +----+-----------+-----------+---------------------+---------------------+ | id | system_id | status | start_time | finish_time | +----+-----------+-----------+---------------------+---------------------+ | 1 | 1 | Automated | 2014-04-08 01:40:40 | 2014-04-08 01:40:45 | +----+-----------+-----------+---------------------+---------------------+ 1 row in set (0.00 sec) Expected results: mysql> select * from system_status_duration where system_id = 1; +----+-----------+-----------+---------------------+-------------+ | id | system_id | status | start_time | finish_time | +----+-----------+-----------+---------------------+-------------+ | 1 | 1 | Automated | 2014-04-08 01:42:13 | NULL | +----+-----------+-----------+---------------------+-------------+ 1 row in set (0.00 sec) Additional info: As far as I can see this is not a regression, it has been broken since system_status_duration was introduced in Beaker 0.6.8. The reason we are seeing it now is because of bug 1085047.
On Gerrit: http://gerrit.beaker-project.org/3003
As far as I can tell the only side-effect of the failure to roll back is the corrupted system_status_duration rows, which causes an exception like the one in comment 0 when changing system status. To find systems with corrupted system_status_duration rows you can use this query: SELECT fqdn FROM system WHERE NOT EXISTS ( SELECT 1 FROM system_status_duration WHERE finish_time IS NULL AND system_id = system.id); To fix the bad rows you can clear the finish_time from the latest row: UPDATE system_status_duration INNER JOIN (SELECT MAX(id) max_id FROM system_status_duration GROUP BY system_id) x ON system_status_duration.id = max_id SET finish_time = NULL;
Verify: 1. Secret=1 (1）Before importing: mysql> select id,fqdn from system where fqdn="test1"; +----+-------+ | id | fqdn | +----+-------+ | 35 | test1 | +----+-------+ 1 row in set (0.00 sec) mysql> select * from system_status_duration where id=35; +----+-----------+--------+---------------------+---------------------+ | id | system_id | status | start_time | finish_time | +----+-----------+--------+---------------------+---------------------+ | 35 | 14 | Broken | 2013-04-02 19:28:58 | 2013-04-02 19:28:58 | +----+-----------+--------+---------------------+---------------------+ 1 row in set (0.00 sec) (2)After importing mysql> select * from system_status_duration where system_id = 35 -> ; +-----+-----------+-----------+---------------------+---------------------+ | id | system_id | status | start_time | finish_time | +-----+-----------+-----------+---------------------+---------------------+ | 195 | 35 | Automated | 2013-10-17 08:36:14 | 2013-11-12 12:05:13 | | 259 | 35 | Broken | 2013-11-12 12:05:13 | 2014-02-13 03:16:40 | | 327 | 35 | Automated | 2014-02-13 03:16:40 | 2014-02-13 03:17:32 | | 328 | 35 | Broken | 2014-02-13 03:17:32 | 2014-04-15 05:32:22 | | 372 | 35 | Automated | 2014-04-15 05:32:22 | NULL | +-----+-----------+-----------+---------------------+---------------------+ 5 rows in set (0.00 sec) 2.secret=None [jason@dhcp-65-193 Downloads]$ cat system.csv csv_type,fqdn,deleted,lender,location,mac_address,memory,model,serial,vendor,arch,lab_controller,owner,secret,status,type,cc system,test1,False,test1,,1:1:1:2:2,,,,,x86_64,lab-devel-03.rhts.eng.bos.redhat.com,jinzhang,,Automated,Machine, (1）Before importing: mysql> select id,fqdn from system where fqdn="test1"; +----+-------+ | id | fqdn | +----+-------+ | 35 | test1 | +----+-------+ 1 row in set (0.00 sec) mysql> select * from system_status_duration where system_id = 35; +-----+-----------+-----------+---------------------+---------------------+ | id | system_id | status | start_time | finish_time | +-----+-----------+-----------+---------------------+---------------------+ | 195 | 35 | Automated | 2013-10-17 08:36:14 | 2013-11-12 12:05:13 | | 259 | 35 | Broken | 2013-11-12 12:05:13 | 2014-02-13 03:16:40 | | 327 | 35 | Automated | 2014-02-13 03:16:40 | 2014-02-13 03:17:32 | | 328 | 35 | Broken | 2014-02-13 03:17:32 | 2014-04-15 05:32:22 | | 372 | 35 | Automated | 2014-04-15 05:32:22 | 2014-04-15 05:44:43 | | 373 | 35 | Broken | 2014-04-15 05:44:43 | NULL | +-----+-----------+-----------+---------------------+---------------------+ (2)After importing: Show error message on Importing Page: Error Log Error importing line 2: test1: Invalid secret None mysql> select * from system_status_duration where system_id = 35; +-----+-----------+-----------+---------------------+---------------------+ | id | system_id | status | start_time | finish_time | +-----+-----------+-----------+---------------------+---------------------+ | 195 | 35 | Automated | 2013-10-17 08:36:14 | 2013-11-12 12:05:13 | | 259 | 35 | Broken | 2013-11-12 12:05:13 | 2014-02-13 03:16:40 | | 327 | 35 | Automated | 2014-02-13 03:16:40 | 2014-02-13 03:17:32 | | 328 | 35 | Broken | 2014-02-13 03:17:32 | 2014-04-15 05:32:22 | | 372 | 35 | Automated | 2014-04-15 05:32:22 | 2014-04-15 05:44:43 | | 373 | 35 | Broken | 2014-04-15 05:44:43 | NULL | +-----+-----------+-----------+---------------------+---------------------+ So move this bug to "VERIFIED"
Beaker 0.16.2 has been released.