Migration failed from 0.11.7 - IntegrityError: UNIQUE constraint failed

[nicolasa@…]

I have an existing Trac instance :

Trac:	0.11.7
Python:	2.6.5 (r265:79063, Feb 27 2014, 19:56:00) [GCC 4.4.3]
setuptools:	0.6
SQLite:	3.6.22
pysqlite:	2.4.1
Genshi:	0.5.1
mod_wsgi:	2.8 (WSGIProcessGroup WSGIApplicationGroup %{GLOBAL})
Pygments:	1.2.2
Subversion:	1.6.6 (r40053)
RPC:	1.0.6
jQuery:	1.3.2

I duplicate the Trac repository to a Ubuntu 14.04 LTS.

I have to upgrade the Trac repository using "trac-admin /path/to/repo upgrade".

I have this following error (without stack):

IntegrityError: UNIQUE constraint failed: ticket_change.ticket, ticket_change.time, ticket_change.field

I install in a userspace Trac 0.12 and try to upgrade with is trac-admin: exactly the same error.

I try to export / import the trac.db using sqlite3, with no error, but the trac migration still failed.

I search for duplicate entries on ticket, time and field from ticket_change using:

SELECT ticket, time, field, COUNT(*) FROM ticket_change GROUP BY ticket, time, field HAVING COUNT(*) > 1;

With no result, of course …

I also apply "PRAGMA integrity_check" and "VACUUM" without error and change.

I have 4200 tickets for this instance and I won't drop it for a fresh repository !

Bug in trac-admin from 0.12 ? Corrupted db by trac 0.11.7 ?

Any idea to successfully upgrade my repository ?

[Ryan J Ollos]

Replying to nicolasa@…:

I have this following error (without stack):

Is there no stack trace, or have you just not posted it to this ticket?

I have 4200 tickets for this instance and I won't drop it for a fresh repository !

Yes, definitely don't drop it! We will try to help you resolve the issue, and if there is a defect in Trac we want to fix it.

Trac 0.11.7 was db_version = 21: tags/trac-0.11.7/trac/db_default.py@:20​. It's unclear if you are upgrading to Trac 0.12, 0.12.5, or some intermediate version. Well regardless, the db_version will be 26: tags/trac-0.12/trac/db_default.py@:20​, tags/trac-0.12.5/trac/db_default.py@:20​.

The ticket_change table is migrated in db25.py: tags/trac-0.12/trac/upgrades/db25.py@:12​, so I guess it must be failing on that step. Does the database of the failed migration report database_version|24 for SELECT name, value FROM system WHERE name='database_version'?

[nicolasa@…]

Replying to rjollos:

Is there no stack trace, or have you just not posted it to this ticket?

No stack at all.

Yes, definitely don't drop it! We will try to help you resolve the issue, and if there is a defect in Trac we want to fix it.

Thank you!

The ticket_change table is migrated in db25.py: tags/trac-0.12/trac/upgrades/db25.py@:12​, so I guess it must be failing on that step. Does the database of the failed migration report database_version|24 for SELECT name, value FROM system WHERE name='database_version'?

You right :) The db on the working instance returns 'database_version|21' and on the broken db returns 'database_version|24'.

This is the type change of time from int to int64 that cause the problem ?

Or the 'Convert timestamps to microseconds' ?

Tell me if I can try something (and how to run migration from source, not egg).

[Ryan J Ollos]

Replying to nicolasa@…:

This is the type change of time from int to int64 that cause the problem ?

Or the 'Convert timestamps to microseconds' ?

I'm not sure, but we've possible narrowed the problem. I'm wondering if we can trap the exception when do_upgrade is executed in upgrade_environment and report a better error message and/or a stack trace. I will try to find some time to experiment tomorrow or the following day.

You might try comparing the data between the original and failed upgrade databases, though I imagine that the transaction would have been rolled back and therefore the ticket_change tables will be the same.

Tell me if I can try something (and how to run migration from source, not egg).

You could install in development mode and try to step through the upgrade steps with a debugger to try and find where it is failing. More info on setting up a development environment can be found here: TracDev/DevelopmentEnvironmentSetup.

[nicolasa@…]

Ok, I checkout the trunk of Trac and I try to see where is the problem in db25.py → the exception is raised by:

cursor.execute("UPDATE %s SET %s" % (table,
                            ', '.join("%s=%s*1000000" % (column, column)
                                      for column in columns)))

This is for table = 'ticket_change'.

Can we temporary disable the UNIQUE constraint on 'time' during this update ?

I try this request, with no result:

SELECT * FROM ticket_change t1, ticket_change t2 WHERE t1.time = t2.time*1000000;

[Remy Blank]

Heh, I never thought of that case. Indeed, if one row has a time value of 1, and the next a value of 1000000, then converting the first row will fail due to the UNIQUE constraint. Of course, this will only happen if you have weird values as times (e.g. close to 1970), but it's still possible. To handle this properly would require using a temporary table.

But since your query above doesn't return any results, you don't seem to be affected by this particular case. I would suggest looking for unexpected values in the time column of the ticket_change table, like NULL, floating-point or string values. SQLite column types are advisory only, and it's possible to store a string in an INTEGER column. Maybe multiplying two different such values with 1000000 gives the same result.

sqlite> SELECT "a" * 1000000, "b" * 1000000;
0|0

Yep, two different string values, therefore satisfying the UNIQUE constraint, are reduced to the same value when multiplied by 1000000. I would start looking for strings in the time column and fix them before attempting another upgrade.

[Nicolas Albert <nicolasa@…>]

Database repaired !

I try this request:

SELECT t1.ticket, t1.time, t2.time FROM ticket_change t1, ticket_change t2 WHERE t1.time*10 = t2.time*10 AND t1.ticket = t2.ticket AND t1.field = t2.field AND t1.time != t2.time;

It return the ticket "29550" ! The latest ticket is the "4025" :-/

For time value, I have many filenames. So I do a:

sqlite> SELECT * FROM ticket_change WHERE ticket = 29550;
29550|pathToFile1|F|E|pathToFile1|29501
29550|pathToFile2|F|E|pathToFile2|28379
29550|pathToFile3|F|E|pathToFile3|28379
29550|pathToFile4|F|E|pathToFile4|29451

I change the real path file by pathToFileN. 29550 is a 2 years old changeset and I don't know why this entry is here, but I delete it!

After that, the trac-admin congrats to finish the upgrade and the trac server finaly launch.

Thank you for your help :)

[Remy Blank]

Glad it works. I don't think the edge case described in comment:5 is worth handling, so there's nothing else to do here.