Bitten problems when upgrading Trac from sqlite to PostgreSQL

Following a routine upgrade of a Trac site from version 0.10 to 0.11, I needed to change the database from sqlite to PostgreSQL, because the former (at a little over 1GB) had been giving the dreaded "database is locked" errors. This meant that continuous builds, handled by the Trac plugin Bitten, were often failing to complete. This post describes how I fixed the couple of problems I encountered.

For the database switch I used sqlite2pg:

./sqlite2pg -e /path/to/tracproject -p 'postgres:///name_of_database

which gave the following error:

Traceback (most recent call last):
 File "./sqlite2pg", line 335, in ?
   sys.exit(main(sys.argv[1:]))
 File "./sqlite2pg", line 331, in main
   Main(opts)
 File "./sqlite2pg", line 244, in Main
   pgenv = getPostgreSQLEnvironment(opts)
 File "./sqlite2pg", line 222, in getPostgreSQLEnvironment
   env.upgrade()
 File "/usr/lib/python2.4/site-packages/Trac-0.11.5rc1-py2.4.egg/trac/env.py",
line 457, in upgrade
   participant.upgrade_environment(db)
 File "/usr/lib/python2.4/site-packages/Bitten-0.6dev_r653-py2.4.egg/bitten/main.py",
line 67, in upgrade_environment
   self.environment_created()
 File "/usr/lib/python2.4/site-packages/Bitten-0.6dev_r653-py2.4.egg/bitten/main.py",
line 51, in environment_created
   os.mkdir(snapshots_dir)
OSError: [Errno 17] File exists: '/path/to/tracproject/snapshots'

So, bitten had already created a snapshots directory (perhaps during 0.10->0.11 upgrade), which was now crashing the database upgrade. Simply removing the (empty) folder fixed the problem, but the code should probably check whether the directory exists before creating it. Pointed the trac environment at the new database, followed by a resync of the repository, and everything worked straight away. Except the Bitten builds...

Fixing the Bitten tables

Now when I restarted any of the build slaves, I got an "Internal server error". Enabling SQL debug logging in trac.ini gave this in the log file:

IntegrityError: duplicate key violates unique constraint "bitten_build_pkey"

So I jumped into the psql interface and attempted the same command:

# insert into bitten_build (config,rev,rev_time,platform,slave,started,stopped,status) VALUES ('Nightly','10991',1247666073, 7, '', 0,0,'P');
ERROR:  duplicate key violates unique constraint "bitten_build_pkey"

So far, so good. The primary key in question is just an incrementing integer - a sequence in PostgreSQL parlance. I found that both this, and the equivalent key in bitten_log (IntegrityError: duplicate key violates unique constraint "bitten_log_pkey") needed to be initialised to the maximum existing value, and then the builds ran happily again. Without the locked database problems. Well worth the time required to upgrade.

It turns out that this sequence problem is a known problem with the upgrade script.

And in case it helps, here's the psql session which fixed the problems.

# select max (id) from  bitten_build;
  max  
-------
 17457
(1 row)
 
# select currval('bitten_build_id_seq');
 currval 
---------
       9
(1 row)
 
select setval('bitten_build_id_seq',17457);
 
# select max(id) from bitten_log;
  max   
--------
 546168
(1 row)
 
# select currval('bitten_log_id_seq');
 currval 
---------
       2
(1 row)
 
# select setval('bitten_log_id_seq',546168);

Update - need to do the same for bitten_platform_id_seq and bitten_report_id_seq too.

Comments

Wow! Thank you so much.

Wow! Thank you so much. This perfectly fixed the error we were encountering after a trac upgrade.

Thanks! We were having a

Thanks! We were having a similar error and this fixed it.