MySQL to PostgreSQL migration fails with codereviewer table

[Quince]

fatt:/tmp/m # ./trac-migrate.py --in-place /srv/trac postgres://tracuser:xxxxxxxxx@127.0.0.1/trac?schema=trac

Copying tables:
  attachment table... 37 records.
  auth_cookie table... 4 records.
  cache table... 8 records.
  codereviewer table... Traceback (most recent call last):
  File "./trac-migrate.py", line 55, in <module>
    sys.exit(main(sys.argv[1:]) or 0)
  File "./trac-migrate.py", line 51, in main
    return TracMigrationCommand(env)._do_migrate(dest, dburi)
  File "/tmp/m/tracmigrate/admin.py", line 29, in _do_migrate
    return self._do_migrate_inplace(dburi)
  File "/tmp/m/tracmigrate/admin.py", line 63, in _do_migrate_inplace
    self._copy_tables(src_db, dst_db, src_dburi, dburi, inplace=True)
  File "/tmp/m/tracmigrate/admin.py", line 108, in _copy_tables
    @self._with_transaction(dst_db)
  File "/tmp/m/tracmigrate/admin.py", line 197, in wrapper
    fn(db)
  File "/tmp/m/tracmigrate/admin.py", line 143, in copy
    cursor.executemany(query, rows)
  File "/usr/lib/python2.7/site-packages/trac/db/util.py", line 85, in executemany
    return self.cursor.executemany(sql_escape_percent(sql), args)
psycopg2.DataError: integer out of range

No problem if I don't have CodeReviewerPlugin enabled, but if I have it disabled and enable it after the switch, it's no longer present in the interface and I can't set reviews even on new tickets.

[Ryan J Ollos]

It might be related to #10806. The time column should probably be int64 rather than integer.

We can also cleanup the code and use to_utimestamp(datetime.now(utc)) rather than: codereviewerplugin/0.12/coderev/model.py@13697:83#L81​, and fix other places where trac.util.datefmt should be used.

[Quince]

Actually, I'm the "Bubba" of #10806; I had forgotten my old username. What would be a workaround I could use at this time? I actually don't care if previous reviews are lost; I just need to be able to create new ones, and I don't know why the plugin GUI elements don't show up if I disable it and then reenable it post-migration (trac-admin upgrade seems to not recreate the plugin table, saying nothing's changed, even if I move the plugin egg out of the plugins directory before migration and then back in).

[Ryan J Ollos]

There a row in the system table with name = coderev. You'll need to delete that entry so that trac-admin knows that the database needs to be upgraded.

[Jun Omae]

#11663 was closed as a duplicate.

Root cause is that the plugin uses integer type for codereviewer.time and codereviewer_map.time.

• codereviewerplugin/0.12/coderev/upgrades/db1.py@13697:12​
• codereviewerplugin/0.12/coderev/upgrades/db2.py@13697:10​

The range of integer type in PostgreSQL is -2147483648 to +2147483647, see ​http://www.postgresql.org/docs/9.1/static/datatype-numeric.html.

[Quince]

So would switching these to bigint and replacing the time format as per comment:1 be sufficient?

[Ryan J Ollos]

Technically you'll want to use int64 for the Trac database API, but yes, if you don't care about migrating your old data, that should work. In fixing this for the plugin we'll need to take care to migrate data.

[anonymous]

How? I don't see int4 in PostgreSQL: ​http://www.postgresql.org/docs/9.3/static/datatype-numeric.html

[Ryan J Ollos]

Use the Trac database API. For example: ​browser:/tags/trac-1.0.1/trac/db_default.py@:80,96#L75.

[Quince]

If I change to the to_utimestamp(datetime.now(utc)) mentioned above, what do I change the pretty printing to codereviewerplugin/0.12/coderev/model.py@13697:200-203#L200​?

Do I just need to change 203 to pretty_when += ' (%s ago)' % pretty_timedelta(from_utimestamp(when)) and the previous three lines are OK as is?

[Edit:] Or, actually, shouldn't need to change even 203? I can't tell because I'm not sure which ones are supposed to be in localtime and which in UTC. Also the other location, codereviewerplugin/0.12/coderev/util/reviewer.py@13697:94#L94​ not clear if needs to be changed.

[Quince]

This seems to work, giving correct dates, as well as the migration. If someone wants to double-check it, perhaps you could merge it in the plugin:

coderev/model.py

@@ -1 +1 @@
 import re
 import time
 
+from datetime import datetime
 from trac.mimeview import Context
 from trac.resource import ResourceNotFound
-from trac.util.datefmt import pretty_timedelta
+from trac.util.datefmt import pretty_timedelta, utc, from_utimestamp, to_utimestamp
 from trac.wiki.formatter import format_to_html
 from trac.ticket.model import Ticket
 
@@ -80 +81 @@
 
     def save(self, status, reviewer, summary, **kw):
         status = self.encode(status)
-        when = int(time.time() * self.EPOCH_MULTIPLIER)
+        when = to_utimestamp(datetime.now(utc))
+
         if status == self.status and self._when != 0: # initial is special
             status = '' # only save status when changed
         if not status and not summary:
@@ -200 +202 @@
             pretty_when = time.strftime('%Y-%m-%d %H:%M',
                                         time.localtime(long(when) /
                                                        self.EPOCH_MULTIPLIER))
-            pretty_when += ' (%s ago)' % pretty_timedelta(when)
+            pretty_when += ' (%s ago)' % pretty_timedelta(from_utimestamp(when))
             summaries.append({
                 'repo': self.repo,
                 'changeset': self.changeset,

coderev/upgrades/db1.py

@@ -9 +9 @@
             Column('status', type='text'),
             Column('reviewer', type='text'),
             Column('summary', type='text'),
-            Column('time', type='integer'),
+            Column('time', type='int64'),
             Index(columns=['repo', 'changeset', 'time']),
         ],
     ]

coderev/upgrades/db2.py

@@ -7 +7 @@
             Column('repo', type='text'),
             Column('changeset', type='text'),
             Column('ticket', type='text'),
-            Column('time', type='integer'),
+            Column('time', type='int64'),
         ],
     ]
 

coderev/util/sync.py

@@ -8 +8 @@
 import re
 import sys
 import sqlite3
+from datetime import datetime
+from trac.util.datefmt import utc, to_utimestamp
 from subprocess import Popen, STDOUT, PIPE
 
 EPOCH_MULTIPLIER = 1000000.0
@@ -32 +34 @@
     for changeset_line in changeset_lines:
         print '.',
         rev,when,msg = changeset_line.split('|',2)
-        when = long(when) * EPOCH_MULTIPLIER
+        when = to_utimestamp(datetime.now(utc))
         ticket_re = re.compile('#([0-9]+)')
         for ticket in ticket_re.findall(msg):
             try:

[Ryan J Ollos]

comment:10 looks like a good start. We need to created a new db3.py rather than modifying db1.py and db2.py so that existing installations are upgraded from db version 2 to db version 3.

[Ryan J Ollos]

Closing as a duplicate of #10716.