Bitten problems when upgrading Trac from sqlite to PostgreSQL
Update: I use Jenkins now. Some people commented that this helped them, so I’ve left it.
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 a PostgreSQL sequence. 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.