#11664 closed defect (duplicate)
MySQL to PostgreSQL migration fails with codereviewer table
Reported by: | Quince | Owned by: | Rob Guttman |
---|---|---|---|
Priority: | high | Component: | CodeReviewerPlugin |
Severity: | major | Keywords: | |
Cc: | Ryan J Ollos | Trac Release: | 1.0 |
Description
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.
Attachments (0)
Change History (12)
comment:1 Changed 10 years ago by
comment:2 Changed 10 years ago by
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).
comment:3 Changed 10 years ago by
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.
comment:4 Changed 10 years ago by
#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.
comment:5 Changed 10 years ago by
So would switching these to bigint and replacing the time format as per comment:1 be sufficient?
comment:6 Changed 10 years ago by
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.
comment:7 Changed 10 years ago by
How? I don't see int64 in PostgreSQL: http://www.postgresql.org/docs/9.3/static/datatype-numeric.html
comment:8 Changed 10 years ago by
Use the Trac database API. For example: browser:/tags/trac-1.0.1/trac/db_default.py@:80,96#L75.
comment:9 Changed 10 years ago by
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.
comment:10 Changed 10 years ago by
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:
Index: coderev/model.py =================================================================== --- coderev/model.py (revision 13803) +++ coderev/model.py (working copy) @@ -1,9 +1,10 @@ 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,7 +81,8 @@ 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,7 +202,7 @@ 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, Index: coderev/upgrades/db1.py =================================================================== --- coderev/upgrades/db1.py (revision 13803) +++ coderev/upgrades/db1.py (working copy) @@ -9,7 +9,7 @@ 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']), ], ] Index: coderev/upgrades/db2.py =================================================================== --- coderev/upgrades/db2.py (revision 13803) +++ coderev/upgrades/db2.py (working copy) @@ -7,7 +7,7 @@ Column('repo', type='text'), Column('changeset', type='text'), Column('ticket', type='text'), - Column('time', type='integer'), + Column('time', type='int64'), ], ] Index: coderev/util/sync.py =================================================================== --- coderev/util/sync.py (revision 13803) +++ coderev/util/sync.py (working copy) @@ -8,6 +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,7 +34,7 @@ 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:
comment:11 Changed 10 years ago by
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.
comment:12 Changed 9 years ago by
Resolution: | → duplicate |
---|---|
Status: | new → closed |
Closing as a duplicate of #10716.
It might be related to #10806. The
time
column should probably beint64
rather thaninteger
.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 wheretrac.util.datefmt
should be used.