Modify

Opened 5 years ago

Closed 4 years ago

#5844 closed defect (invalid)

Migrating fails with psycopg2.ProgrammingError and .DataError

Reported by: titer Owned by: pacopablo
Priority: normal Component: SqliteToPgScript
Severity: normal Keywords:
Cc: Trac Release: 0.11

Description

On Debian Lenny with:

  • Trac: 0.11.5
  • Python: 2.5.2
  • SQLite: 3.5.9
  • pysqlite: 2.3.2
  • psycopg2: 2.0.7

Migrating to a custom schema fails with:

% ./sqlite2pg -e TransmissionTest -p 'postgres://transmission:XXX@localhost/transmission?schema=trac'
Traceback (most recent call last):
  File "./sqlite2pg", line 335, in <module>
    sys.exit(main(sys.argv[1:]))
  File "./sqlite2pg", line 331, in main
    Main(opts)
  File "./sqlite2pg", line 244, in Main
    pgenv = getPostgreSQLEnvironment(opts)
  File "./sqlite2pg", line 220, in getPostgreSQLEnvironment
    x.environment_created()        
  File "/usr/local/lib/python2.5/site-packages/Trac-0.11.5-py2.5.egg/trac/env.py", line 495, in environment_created
    vals)
  File "/usr/local/lib/python2.5/site-packages/Trac-0.11.5-py2.5.egg/trac/db/util.py", line 75, in executemany
    return self.cursor.executemany(sql_escape_percent(sql), args)
  File "/usr/local/lib/python2.5/site-packages/Trac-0.11.5-py2.5.egg/trac/db/util.py", line 75, in executemany
    return self.cursor.executemany(sql_escape_percent(sql), args)
psycopg2.ProgrammingError: relation "component" does not exist

Migrating to the public schema fails differently:

% ./sqlite2pg -e TransmissionTest -p 'postgres://transmission:XXX@localhost/transmission'
Traceback (most recent call last):
  File "./sqlite2pg", line 335, in <module>
    sys.exit(main(sys.argv[1:]))
  File "./sqlite2pg", line 331, in main
    Main(opts)
  File "./sqlite2pg", line 250, in Main
    rc = tmigration.migrateTable(tname) or rc
  File "./sqlite2pg", line 72, in migrateTable
    rc = self.default_copy(table)
  File "./sqlite2pg", line 93, in default_copy
    pgcur.execute(insert_into, row)
  File "/usr/local/lib/python2.5/site-packages/Trac-0.11.5-py2.5.egg/trac/db/util.py", line 59, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/local/lib/python2.5/site-packages/Trac-0.11.5-py2.5.egg/trac/db/util.py", line 59, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
psycopg2.DataError: invalid input syntax for integer: "query_tickets"

I had wiped the database before each attempt, in each case the script created the tables and sequences, though the tables were empty.

I can reproduce it, and can make the Trac environment available or arrange ssh access if that helps.

Attachments (0)

Change History (2)

comment:1 Changed 4 years ago by anonymous

Work-around:

  1. Create an empty Trac environment trac-admin tmp-env initenv using your Postgres connection.
  2. Run sqlite2pg as you did above

The script is not properly setting up the tables but, since it clears them out before populating them, this works.

comment:2 Changed 4 years ago by titer

  • Resolution set to invalid
  • Status changed from new to closed

The second error came from bogus entries in the original sqlite database - some strings where you'd expect integers, not sure how that happened. PostgreSQL was rightfully choking since it does enforce the data to be of the correct type.

I removed the entries manually and was able to migrate successfully, things have been working well since then (that was months ago, I just realized that ticket was still open).

Add Comment

Modify Ticket

Action
as closed .
as The resolution will be set. Next status will be 'closed'.
to The owner will be changed from pacopablo. Next status will be 'closed'.
The resolution will be deleted. Next status will be 'reopened'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.