Migrating fails with psycopg2.ProgrammingError and .DataError

[Eric Petit]

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.

[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.

[Eric Petit]

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).