#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 11 years ago by
comment:2 Changed 11 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 11 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 11 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 11 years ago by
So would switching these to bigint and replacing the time format as per comment:1 be sufficient?
comment:6 Changed 11 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 11 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 11 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 11 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 11 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:
-
coderev/model.py
1 1 import re 2 2 import time 3 3 4 from datetime import datetime 4 5 from trac.mimeview import Context 5 6 from trac.resource import ResourceNotFound 6 from trac.util.datefmt import pretty_timedelta 7 from trac.util.datefmt import pretty_timedelta, utc, from_utimestamp, to_utimestamp 7 8 from trac.wiki.formatter import format_to_html 8 9 from trac.ticket.model import Ticket 9 10 … … 80 81 81 82 def save(self, status, reviewer, summary, **kw): 82 83 status = self.encode(status) 83 when = int(time.time() * self.EPOCH_MULTIPLIER) 84 when = to_utimestamp(datetime.now(utc)) 85 84 86 if status == self.status and self._when != 0: # initial is special 85 87 status = '' # only save status when changed 86 88 if not status and not summary: … … 200 202 pretty_when = time.strftime('%Y-%m-%d %H:%M', 201 203 time.localtime(long(when) / 202 204 self.EPOCH_MULTIPLIER)) 203 pretty_when += ' (%s ago)' % pretty_timedelta( when)205 pretty_when += ' (%s ago)' % pretty_timedelta(from_utimestamp(when)) 204 206 summaries.append({ 205 207 'repo': self.repo, 206 208 'changeset': self.changeset, -
coderev/upgrades/db1.py
9 9 Column('status', type='text'), 10 10 Column('reviewer', type='text'), 11 11 Column('summary', type='text'), 12 Column('time', type='int eger'),12 Column('time', type='int64'), 13 13 Index(columns=['repo', 'changeset', 'time']), 14 14 ], 15 15 ] -
coderev/upgrades/db2.py
7 7 Column('repo', type='text'), 8 8 Column('changeset', type='text'), 9 9 Column('ticket', type='text'), 10 Column('time', type='int eger'),10 Column('time', type='int64'), 11 11 ], 12 12 ] 13 13 -
coderev/util/sync.py
8 8 import re 9 9 import sys 10 10 import sqlite3 11 from datetime import datetime 12 from trac.util.datefmt import utc, to_utimestamp 11 13 from subprocess import Popen, STDOUT, PIPE 12 14 13 15 EPOCH_MULTIPLIER = 1000000.0 … … 32 34 for changeset_line in changeset_lines: 33 35 print '.', 34 36 rev,when,msg = changeset_line.split('|',2) 35 when = long(when) * EPOCH_MULTIPLIER37 when = to_utimestamp(datetime.now(utc)) 36 38 ticket_re = re.compile('#([0-9]+)') 37 39 for ticket in ticket_re.findall(msg): 38 40 try:
comment:11 Changed 11 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 10 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.