Defect #1759

Migration from trac/postgresql fails with SQL error (probably important - postgres 8.3)

Added by Marcin Kasperski over 13 years ago. Updated almost 9 years ago.

Status:Needs feedbackStart date:2008-08-08
Priority:NormalDue date:
Assignee:Jean-Philippe Lang% Done:

0%

Category:Importers
Target version:-
Resolution:Fixed Affected version:0.7.3

Description

Migration from trac (with data in postgres database) ends with:

Migrating components...
Migrating milestones...
Migrating custom fields
Migrating tickets.rake aborted!
PGError: ERROR:  operator does not exist: text = integer
LINE 1: ...CT * FROM "attachment"     WHERE ("attachment".id = 1 AND (a...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT * FROM "attachment"     WHERE ("attachment".id = 1 AND (attachment.type = 'ticket')) 

The same SQL fails when issued from psql, to have it working one must quote this identifier - WHERE ("attachment".id = "1" ....)

PS Postgres 8.3 is restrictive on datatypes....

Associated revisions

Revision 1728
Added by Jean-Philippe Lang over 13 years ago

Quote ids for attachment association since Trac's attachment.id is varchar (#1759).

History

#1 Updated by Marcin Kasperski over 13 years ago

... and it seems this id is indeed expected to be textual. When I tried to workaround this by renaming the table attachment in trac database and recreating new one with id modified to be integer, I got:

Migrating components...
Migrating milestones...
Migrating custom fields
Migrating tickets..........................................................................
Migrating wiki.rake aborted!
PGError: ERROR:  invalid input syntax for integer: "Reconfiguration" 
: SELECT * FROM "attachment"     WHERE ("attachment".id = E'Reconfiguration' AND (attachment.type = 'wiki')) 

('Reconfiguration' is a name of some wiki page).

#2 Updated by Jean-Philippe Lang over 13 years ago

  • Category set to Importers
  • Status changed from New to Resolved
  • Affected version (unused) set to 0.7.3
  • Resolution set to Fixed
  • Affected version set to 0.7.3

It should be fixed by r1728. Please confirm.

#3 Updated by Marcin Kasperski over 13 years ago

The first problem is indeed fixed, I succesfully migrated all tickets.

But it turned out, that the second problem is not related to my hacks, but still present. Here is what I am getting now:

Migrating components...
Migrating milestones...
Migrating custom fields
Migrating tickets..........................................................................
Migrating wiki.rake aborted!
PGError: ERROR:  invalid input syntax for integer: "Reconfiguration" 
: SELECT DISTINCT attachment.* FROM attachment WHERE attachment.type = 'wiki' AND attachment.id = 'Reconfiguration'

(See full trace by running task with --trace)

As I already said, 'Reconfiguration' is the name of some wiki page I had in trac.

(I don't really care as I didn't have any wiki attachments, so everything I need is already migrated. But a bug is a bug)

Note: my trac was fairly old so I can't guarantee whether their current db schema is identical.

#4 Updated by Jean-Philippe Lang over 13 years ago

  • Status changed from Resolved to New

I'll try to move a sqlite trac database to pgsql to debug this problem.

#5 Updated by Daniel Felix almost 9 years ago

  • Assignee set to Jean-Philippe Lang

Jean-Philippe Lang wrote:

I'll try to move a sqlite trac database to pgsql to debug this problem.

Hi Jean-Philippe,

are there any news on this or is this issue closeable? :-)

Best regards,
Daniel

#6 Updated by Daniel Felix almost 9 years ago

  • Status changed from New to Needs feedback

Jean-Philippe Lang wrote:

I'll try to move a sqlite trac database to pgsql to debug this problem.

Hi Jean-Philippe,

are there any news on this or is this issue closeable? :-)

Best regards,
Daniel

Also available in: Atom PDF