Trac updrade fail with bitten 0.6dev r816

[miniwark]

I am ugrading from bitten r653 to r816 with an sqlite database.

When upgrading the database with trac-admin upgrade i have the following traceback :

Adds a unique index on (config, platform, rev) to the bitten_build             
table. Also drops the old index on bitten_build that serves no real            
purpose anymore.                                                               
Traceback (most recent call last):                                             
  File "/usr/local/bin/trac-admin", line 8, in <module>                        
    load_entry_point('Trac==0.11.7rc1', 'console_scripts', 'trac-admin')()
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/admin/console.py", line 1321, in run
    return admin.onecmd(command)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/admin/console.py", line 138, in onecmd
    rv = cmd.Cmd.onecmd(self, line) or 0
  File "/usr/lib64/python2.5/cmd.py", line 219, in onecmd
    return func(arg)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/admin/console.py", line 1159, in do_upgrade
    self.__env.upgrade(backup=do_backup)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/env.py", line 464, in upgrade
    participant.upgrade_environment(db)
  File "/usr/local/lib64/python2.5/site-packages/Bitten-0.6dev_r816-py2.5.egg/bitten/main.py", line 74, in upgrade_environment
    function(self.env, db)
  File "/usr/local/lib64/python2.5/site-packages/Bitten-0.6dev_r816-py2.5.egg/bitten/upgrades.py", line 538, in add_config_platform_rev_index_to_build
    cursor.execute("CREATE UNIQUE INDEX bitten_build_config_rev_platform_idx ON bitten_build (config,rev,platform)")
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/db/sqlite_backend.py", line 80, in execute
    result = PyFormatCursor.execute(self, *args)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/db/sqlite_backend.py", line 59, in execute
    args or [])
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/db/sqlite_backend.py", line 51, in _rollback_on_error
    return function(self, *args, **kwargs)
sqlite3.OperationalError: index bitten_build_config_rev_platform_idx already exists

Alternalyif i drop all the bitten tables before to have a clean database i have the following error :

Adds a unique index on (config, platform, rev) to the bitten_build                                   
table. Also drops the old index on bitten_build that serves no real                                  
purpose anymore.                                                                                     
Traceback (most recent call last):                                                                   
  File "/usr/local/bin/trac-admin", line 8, in <module>                                              
    load_entry_point('Trac==0.11.7rc1', 'console_scripts', 'trac-admin')()
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/admin/console.py", line 1321, in run
    return admin.onecmd(command)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/admin/console.py", line 138, in onecmd
    rv = cmd.Cmd.onecmd(self, line) or 0
  File "/usr/lib64/python2.5/cmd.py", line 219, in onecmd
    return func(arg)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/admin/console.py", line 1159, in do_upgrade
    self.__env.upgrade(backup=do_backup)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/env.py", line 464, in upgrade
    participant.upgrade_environment(db)
  File "/usr/local/lib64/python2.5/site-packages/Bitten-0.6dev_r816-py2.5.egg/bitten/main.py", line 74, in upgrade_environment
    function(self.env, db)
  File "/usr/local/lib64/python2.5/site-packages/Bitten-0.6dev_r816-py2.5.egg/bitten/upgrades.py", line 506, in add_config_platform_rev_index_to_build
    duplicates_cursor.execute("SELECT config, rev, platform FROM bitten_build GROUP BY config, rev, platform HAVING COUNT(config) > 1")
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/db/sqlite_backend.py", line 80, in execute
    result = PyFormatCursor.execute(self, *args)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/db/sqlite_backend.py", line 59, in execute
    args or [])
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/db/sqlite_backend.py", line 51, in _rollback_on_error
    return function(self, *args, **kwargs)
sqlite3.OperationalError: no such table: bitten_build

For the first errors maybe i have to upgrade to an intermediate revision first ?

[osimons]

I wonder why the index already exists... Shouldn't really, and need to look into the revision number for when things were added. Feels like the upgrade has been run already, then failed leaving the index committed but the full upgrade step not quite done?

As for the second traceback, Bitten reads bitten_version from system table - you have deleted all tables, but the marker for database version still reports it as almost-latest. If you have deleted the Bitten tables and indexes (and thereby whatever Bitten data you had), then you should also delete this one row from the system table (not the table, just the one row...). Without that, Bitten won't create it's database layout (tables + indexes).

[hodgestar]

Agreed with osimons that the mostly like reasons the index exists is that an upgrade was attempted and failed midway through.

@miniwark: Did you attempt any other upgrades before you got this error? If so, I don't suppose you remember what errors they raised?

[miniwark]

@ osimons: thanks for the system table info, i will probably do it this way, as i can dump my build datas and do build agains latter. Basicaly i just need to save recipes.

@hodgestar: I will retest it for you, as i has able to roolback to a working Bitten at revision r653 to be sure than it occur directly from r653 to r816. I think, i have try to update to the dowloable 0.6b2 before but it was falling too. I will check this also en send traceback latter.

[miniwark]

I have just finish the fist test :

r653 -> r816 fail directly because of bitten_build_config_rev_platform_idx

I can see in my saved database than bitten_build_config_rev_platform_idx realy exist already . But now i am not so sure of my saved database. Maybe i have mess it during the upgrade attemp.

I have a old database save older than r653 (without this index), i will roolback to r653 and updrade this save to this version to see if the index is created.

[miniwark]

Ok, so using the old save :

- i has able to use it directly with r653 (no upgrade needed)

- the bitten_build_config_rev_platform_idx index did not exist before upgrade attempt to r816

- direct upgrade from r653 -> r816 still fail because of this index but this time, because he did not exist :

Adds a unique index on (config, platform, rev) to the bitten_build
table. Also drops the old index on bitten_build that serves no real
purpose anymore.                                                   
Traceback (most recent call last):                                 
  File "/usr/local/bin/trac-admin", line 8, in <module>            
    load_entry_point('Trac==0.11.7rc1', 'console_scripts', 'trac-admin')()
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/admin/console.py", line 1321, in run
    return admin.onecmd(command)                                                                                   
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/admin/console.py", line 138, in onecmd
    rv = cmd.Cmd.onecmd(self, line) or 0
  File "/usr/lib64/python2.5/cmd.py", line 219, in onecmd
    return func(arg)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/admin/console.py", line 1159, in do_upgrade
    self.__env.upgrade(backup=do_backup)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/env.py", line 464, in upgrade
    participant.upgrade_environment(db)
  File "/usr/local/lib64/python2.5/site-packages/Bitten-0.6dev_r816-py2.5.egg/bitten/main.py", line 74, in upgrade_environment
    function(self.env, db)
  File "/usr/local/lib64/python2.5/site-packages/Bitten-0.6dev_r816-py2.5.egg/bitten/upgrades.py", line 539, in add_config_platform_rev_index_to_build
    drop_index(env, db, 'bitten_build', 'bitten_build_config_rev_slave_idx')
  File "/usr/local/lib64/python2.5/site-packages/Bitten-0.6dev_r816-py2.5.egg/bitten/upgrades.py", line 53, in drop_index
    cursor.execute("DROP INDEX %s" % (idx,))
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/db/sqlite_backend.py", line 80, in execute
    result = PyFormatCursor.execute(self, *args)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/db/sqlite_backend.py", line 59, in execute
    args or [])
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.7rc1-py2.5.egg/trac/db/sqlite_backend.py", line 51, in _rollback_on_error
    return function(self, *args, **kwargs)
sqlite3.OperationalError: no such index: bitten_build_config_rev_slave_idx

- It's the second updrade attempt who fail like the fist traceback post.

[hodgestar]

Note that the index the upgrade is creating and the index it's trying to remove are different indexes. The first is an index on (config, platform, rev) the second is an index on (config, rev, slave). I'm tempted to say that the (config, rev, slave) index really should exist in the database being upgrade but it's obviously hard to rule out completely that possibility that there are ways for Bitten to end up without it.

[anatoly techtonik <techtonik@…>]

Can we work out a script that checks for the presence of this index and announce it in Bitten ML for people to try on their existing installations? Maybe a script that validates the DB structure at whole and reports Trac/Bitten?/SQLite version combination when structure is broken?

[osimons]

#613 closed as duplicate.

[hodgestar]

I'm tempted to close this ticket without a patch on the grounds that:

• It's not quite clear to me how miniwark's database got into the state it did or that the state was Bitten's fault.
• I can't see an easy clean way to check for the existence of indexes.
• Ignoring errors when dropping or creating indexes risks masking important errors.

If there aren't any objections in a day or two I'll close the ticket.

[anatoly techtonik <techtonik@…>]

There is a way to check for existence of the indexes with gerald. Attached is the dump of DB structure for Trac with installed Bitten 0.6b2 on Debian. .zip archive contains patched version of gerald together with sqlite_dump_db.py script for making dumps out of SQLite databases (this is not ready to be integrated into gerald itself).

[anatoly techtonik <techtonik@…>]

0.6b2 DB structure dump

[anatoly techtonik <techtonik@…>]

patched gerald with SQLite DB dump script

[hodgestar]

The db schema dump you attach appears to omit information about index type and uniqueness (which are rather important). If you examine Gerald's implementation you will notice:

• Each database has an entirely different query for examining indexes.
• The results of these queries look nothing alike.
• In the case of PostgreSQL this requires parsing SQL index definition statements.
• Gerald fails to parse out anything close to full definitions of SQL indexes.

My point is not that we couldn't write a hundred line function to handle simple cases for the three databases Trac currently supports but rather that this is a bad idea. I don't really wish to included and maintain a full database introspection toolkit in Bitten (I have already done this before for another project for two years and I know where it leads).

[osimons]

Sometimes bad things just happen. We can put endless amounts of time into checking, verifying, and introspecting all aspects of all part of Bitten at all times - but there would always be situations that could arise that we would have missed.

We need to put effort into making a solid foundation, solid upgrade scripts and working, unit-tested code. Bugs and mistakes happen, that is just the way of things. I'd rather work on nice, useful features than internal integrity code. And, Bitten is after all just a straight continuous integration system - it is not monitoring a nuclear program...

+1 on closing.

[anatoly techtonik <techtonik@…>]

I'd rather not inflate the issue, but limit the scope to SQLite-only. I don't think we should close it as-is if we can help users troubleshoot their DB issues after using unstable Bitten snapshots that _we_ might ask them to try.

hodgestat, what index type do you expect from SQLite DB? Do you really need the uniqueness info for this specific issue?

I want this tool just to make sure that 0.6b3 upgrade will be smooth, and be prepared if it won't. No need to integrate it into Bitten, but a Wiki link will be helpful. In any case I want 0.6b3 to be released ASAP, so if you feel that closing this issue is critical to make this happen - go ahead.

[hodgestar]

@techtonik: I'm hoping to put together an upgrades page for the Bitten documentation. I'm happy if we make closing this ticket dependent on including something on indexes in the upgrade documentation.

[anatoly techtonik <techtonik@…>]

I don't know what else can be in this "something" except a standard advice to open another bugreport with a reference to the attachment in ticket #550 for troubleshooting. The ticket reference is only needed until my fixes are integrated into gerald.

[anatoly techtonik <techtonik@…>]

I see that the bitten_build_config_rev_platform_idx index first appeared in r724, touched in r730 and in r786. There is no way it could be present in r653 unless there already was an attempt to upgrade that failed in the middle after the index was created.

[hodgestar]

I've added a section on reporting upgrade issues in r936.

[hodgestar]

Upgrade documentation backported to 0.6.x in r946 and available online at wiki:Documentation/upgrade.html.