Modify

Opened 8 years ago

Last modified 5 weeks ago

#1150 reopened defect

multiple errors on running mantis2trac.py with Mantis 1.0.1 and Trac 0.10

Reported by: pottingerhj@… Owned by: codeFiend
Priority: normal Component: MantisImportScript
Severity: major Keywords:
Cc: Trac Release: 0.10

Description

1) a minor annoyance: an extra slash is required at the end of the trac env path, which isn't standard practice; the example given of /path/to/trac/env is wrong.

2) after fixing 1 above, running the script gets me all the way to step 2:

2. import components...
sql: SELECT category, user_id as owner FROM mantis_project_category_table WHERE project_id = '1' or project_id = '3' or project_id = '4' or project_id = '5' or project_id = '6' or project_id = '7' or project_id = '8' or project_id = '9' or project_id = '10'
Adding user hardy to sessions table
Traceback (most recent call last):
  File "./mantis2trac.py", line 926, in ?
    main()
  File "./mantis2trac.py", line 923, in main
    convert(MANTIS_DB, MANTIS_HOST, MANTIS_USER, MANTIS_PASSWORD, TRAC_ENV, TRAC_CLEAN)
  File "./mantis2trac.py", line 578, in convert
    component['owner'] = trac.getLoginName(mysql_cur, component['owner'])
  File "./mantis2trac.py", line 467, in getLoginName
    print 'could not insert %s into sessions table: sql error %s ' % loginName, self.db().error()

After some debugging, I discover that:

AttributeError: 'pysqlite2.dbapi2.Connection' object has no attribute 'error'

So I drop the fancy error reporting and just see what error Python will throw:

pysqlite2.dbapi2.OperationalError: near "IGNORE": syntax error

According the SQLite page, that is indeed incorrect syntax. Lines 462 and 470 should read "INSERT OR IGNORE" not "INSERT IGNORE".

So, after correcting these lines, I receive this error:

2. import components...
sql: SELECT category, user_id as owner FROM mantis_project_category_table WHERE project_id = '1' or project_id = '3' or project_id = '4' or project_id = '5' or project_id = '6' or project_id = '7' or project_id = '8' or project_id = '9' or project_id = '10'
Adding user hardy to sessions table
Traceback (most recent call last):
  File "./mantis2trac.py", line 926, in ?
    main()
  File "./mantis2trac.py", line 923, in main
    convert(MANTIS_DB, MANTIS_HOST, MANTIS_USER, MANTIS_PASSWORD, TRAC_ENV, TRAC_CLEAN)
  File "./mantis2trac.py", line 578, in convert
    component['owner'] = trac.getLoginName(mysql_cur, component['owner'])
  File "./mantis2trac.py", line 474, in getLoginName
    (result[0]['username'].encode('utf-8'), '1', 'name', result[0]['realname'].encode('utf-8'),
  File "/usr/lib/python2.3/site-packages/trac/db/util.py", line 47, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.3/site-packages/trac/db/sqlite_backend.py", line 56, in execute
    args or [])
  File "/usr/lib/python2.3/site-packages/trac/db/sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
pysqlite2.dbapi2.OperationalError: near ",": syntax error

And, unfortunately, my debug abilities have hit a wall. I'm not sure what might be the problem, so I'm throwing this out there, to see if anyone might be able to help.

Attachments (0)

Change History (19)

comment:1 Changed 7 years ago by anonymous

Had the exact same problems as you did...they seem to have been introduced in the latest version [1724] . I got the previous version [1721] from the following link and it worked great:

http://trac-hacks.org/browser/mantisimportscript/mantis2trac.py?rev=1721&format=raw

comment:2 follow-up: Changed 7 years ago by codeFiend

  • Status changed from new to assigned

It's complaining because sqlite doesn't support mutliple insert statements like sql does. Try the new revision (r1927) (I split up the session attribute insert into separate queries and removed the 'IGNORE' part (not sure how to cleanly make alternate sql syntax depending on whether the trac is running a MySQL or SQLite backend.

comment:3 in reply to: ↑ 2 Changed 7 years ago by anonymous

Tried the new revision (r1927). No luck, here's the error:

2. import components...
sql: SELECT category, user_id as owner FROM mantis_project_category_table WHERE project_id = '1' or project_id = '3' or project_id = '4' or project_id = '5' or project_id = '6' or project_id = '7' or project_id = '8' or project_id = '9' or project_id = '10'
Adding user hardy to sessions table
Traceback (most recent call last):
  File "./mantis2trac.py", line 936, in ?
    main()
  File "./mantis2trac.py", line 933, in main
    convert(MANTIS_DB, MANTIS_HOST, MANTIS_USER, MANTIS_PASSWORD, TRAC_ENV, TRAC_CLEAN)
  File "./mantis2trac.py", line 588, in convert
    component['owner'] = trac.getLoginName(mysql_cur, component['owner'])
  File "./mantis2trac.py", line 467, in getLoginName
    print 'could not insert %s into sessions table: sql error %s ' % loginName, self.db().error()
TypeError: not enough arguments for format string

comment:4 Changed 7 years ago by codeFiend

  • Resolution set to fixed
  • Status changed from assigned to closed

(In [1929]) MantisImportScript: second try to fix #1150

comment:5 Changed 7 years ago by codeFiend

Try now. I didn't actually test my commit last night (yeah, I know, bad bad...) Tested the fix now, should be working correctly. Thanks!

comment:6 Changed 7 years ago by anonymous

  • Resolution fixed deleted
  • Status changed from closed to reopened

still no luck with the new revision (r1929):

2. import components...
sql: SELECT category, user_id as owner FROM mantis_project_category_table WHERE project_id = '1' or project_id = '3' or project_id = '4' or project_id = '5' or project_id = '6' or project_id = '7' or project_id = '8' or project_id = '9' or project_id = '10'
Adding user foo to sessions table
Adding user bar to sessions table
failed executing sql:
INSERT INTO session
                            (sid, authenticated, last_visit)
                        VALUES  ('bar', '1', '1161118853')
Traceback (most recent call last):
  File "./mantis2trac_new.py", line 943, in ?
    main()
  File "./mantis2trac_new.py", line 940, in main
    convert(MANTIS_DB, MANTIS_HOST, MANTIS_USER, MANTIS_PASSWORD, TRAC_ENV, TRAC_CLEAN)
  File "./mantis2trac_new.py", line 595, in convert
    component['owner'] = trac.getLoginName(mysql_cur, component['owner'])
  File "./mantis2trac_new.py", line 476, in getLoginName
    print 'could not insert %s into sessions table: sql error %s ' % (loginName, self.db().error())
  File "/usr/lib/python2.3/site-packages/trac/db/util.py", line 69, in __getattr__
    return getattr(self.cnx, name)
  File "/usr/lib/python2.3/site-packages/trac/db/util.py", line 69, in __getattr__
    return getattr(self.cnx, name)
AttributeError: 'pysqlite2.dbapi2.Connection' object has no attribute 'error'

comment:7 Changed 7 years ago by codeFiend

What the... looks like the sqlite wrapper doesn't provide us with an error() function... grrr.

How large is your mantis db dump? Can you attach it to this ticket so I can test it?

Alternatively, can you go into sqlite3 prompt and run the sql query that it's erroring on, to see if you can get a more intelligible error statement?

INSERT INTO session
 (sid, authenticated, last_visit)
VALUES  ('bar', '1', '1161118853');

comment:8 Changed 7 years ago by codeFiend

Er, that should be

INSERT INTO session
    (sid, authenticated, last_visit)
VALUES  ('bar', '1', '1161118853')

comment:9 Changed 7 years ago by anonymous

I'm not comfortable posting my db dump.

However, I did follow the advice of comment 1, and reverted back to r1721. Needed to also turn off the TIME_ADJUSTMENT_HACK and set the defaultencoding to latin-1, but the script ran fine. Looking forward to seeing what I've got in my trac db now.

I'd be willing to share a dump file of my mantis MySQL db via some other method, if you'd like to tinker. I just don't want it available to the world.

comment:10 Changed 7 years ago by codeFiend


No problem. It's really a minor part of the script that's causing all this trouble; more for convenience than anything else. If you like, email me your mantis dump at aeontech at gmail dot com, I'll poke it with a stick, see what I can turn up. The script is running fine against trac on a mysql backend, not sure why it's having so much trouble with sqlite.

comment:11 Changed 7 years ago by david

I'm seeing pretty much the same thing here (though I should admit up front that I'm working on Windows, which may or may not make any difference.)

Mantis database is from 0.19.x, imported into MySQL 5.0.37 no problem; using trac 0.10 with pysqlite-2.3.3.win32-py2.4.exe, set up the trac environment ok, ie database connection is the default sqlite:db/trac.db.

Using latest mantis2trac r1929 (with TRAC_CLEAN option disabled cos windows doesn't understand 'rm' though I was regularly nuking the trac installation to make sure things were clean), I get pretty much the same as anonymous on 9 Feb:

7. import bugs and bug activity...
Adding user jason to sessions table
failed executing sql:
INSERT INTO session
                            (sid, authenticated, last_visit)
                        VALUES  ('jason', '1', '')
Traceback (most recent call last):
  File "C:\Documents and Settings\dec\Desktop\Trac on windows\mantis2trac.1929.py", line 943, in ?
    main()
  File "C:\Documents and Settings\dec\Desktop\Trac on windows\mantis2trac.1929.py", line 940, in main
    convert(MANTIS_DB, MANTIS_HOST, MANTIS_USER, MANTIS_PASSWORD, TRAC_ENV, TRAC_CLEAN)
  File "C:\Documents and Settings\dec\Desktop\Trac on windows\mantis2trac.1929.py", line 648, in convert
    ticket['reporter'] = trac.getLoginName(mysql_cur, bug['reporter_id'])
  File "C:\Documents and Settings\dec\Desktop\Trac on windows\mantis2trac.1929.py", line 476, in getLoginName
    print 'could not insert %s into sessions table: sql error %s ' % (loginName,
 self.db().error())
  File "C:\Python24\lib\site-packages\trac\db\util.py", line 69, in __getattr__
    return getattr(self.cnx, name)
  File "C:\Python24\lib\site-packages\trac\db\util.py", line 69, in __getattr__
    return getattr(self.cnx, name)
AttributeError: 'pysqlite2.dbapi2.Connection' object has no attribute 'error'

However, I had no luck with the suggested comment 1 approach of reverting to r1721 etc, although it did get further:

inserting ticket 9 -- "autogenerate jnlp file from build.paths"
 * adding comment "Take a look at the ant tasks here http:/..."
 * adding comment "its been moved to here http://sourceforg..."
Traceback (most recent call last):
  File "C:\Documents and Settings\dec\Desktop\Trac on windows\mantis2trac.1721.p
y", line 905, in ?
    main()
  File "C:\Documents and Settings\dec\Desktop\Trac on windows\mantis2trac.1721.p
y", line 902, in main
    convert(MANTIS_DB, MANTIS_HOST, MANTIS_USER, MANTIS_PASSWORD, TRAC_ENV, TRAC
_CLEAN)
  File "C:\Documents and Settings\dec\Desktop\Trac on windows\mantis2trac.1721.p
y", line 651, in convert
    trac.addTicketComment(bugid, note['date_submitted'], trac.getLoginName(mysql
_cur, note['reporter_id']), note['note'])
  File "C:\Documents and Settings\dec\Desktop\Trac on windows\mantis2trac.1721.p
y", line 427, in addTicketComment
    (ticket, time.strftime('%s'), author, 'comment', '', comment,))
  File "C:\Python24\lib\site-packages\trac\db\util.py", line 47, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "C:\Python24\lib\site-packages\trac\db\sqlite_backend.py", line 56, in ex
ecute
    args or [])
  File "C:\Python24\lib\site-packages\trac\db\sqlite_backend.py", line 48, in _r
ollback_on_error
    return function(self, *args, **kwargs)
pysqlite2.dbapi2.IntegrityError: columns ticket, time, field are not unique

... which made me think briefly that the TIME_ADJUSTMENT_HACK would save me, but it didn't.

So, seeing the remarks about pysqlite cf mysql, I followed http://trac.edgewall.org/wiki/MySqlDb and I created the trac database within MySQL. Reran the trac environment initialisation with the database connection string set to: mysql://root:password@localhost/trac

Tried again with r1929 and got essentially the same as with pysqlite:

7. import bugs and bug activity...
Adding user jason to sessions table
failed executing sql:
INSERT INTO session
                            (sid, authenticated, last_visit)
                        VALUES  ('jason', '1', '')
could not insert jason into sessions table: sql error
Traceback (most recent call last):
  File "C:\Documents and Settings\dec\Desktop\Trac on windows\mantis2trac.1929.py", line 943, in ?
    main()
  File "C:\Documents and Settings\dec\Desktop\Trac on windows\mantis2trac.1929.py", line 940, in main
    convert(MANTIS_DB, MANTIS_HOST, MANTIS_USER, MANTIS_PASSWORD, TRAC_ENV, TRAC_CLEAN)
  File "C:\Documents and Settings\dec\Desktop\Trac on windows\mantis2trac.1929.py", line 648, in convert
    ticket['reporter'] = trac.getLoginName(mysql_cur, bug['reporter_id'])
  File "C:\Documents and Settings\dec\Desktop\Trac on windows\mantis2trac.1929.py", line 480, in getLoginName
    c.execute(
  File "C:\Python24\lib\site-packages\trac\db\util.py", line 47, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "C:\Python24\lib\site-packages\trac\db\util.py", line 47, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "C:\Python24\lib\site-packages\mysql_python-1.2.2-py2.4-win32.egg\MySQLdb\cursors.py", 
line 151, in execute
TypeError: not all arguments converted during string formatting

For completeness, tried the r1721 approach too:

7. import bugs and bug activity...
inserting ticket 1 -- "Sort Ranges by multiple rows/columns"
Traceback (most recent call last):
  File "C:\Documents and Settings\dec\Desktop\Trac on windows\mantis2trac.1721.p
y", line 905, in ?
    main()
  File "C:\Documents and Settings\dec\Desktop\Trac on windows\mantis2trac.1721.p
y", line 902, in main
    convert(MANTIS_DB, MANTIS_HOST, MANTIS_USER, MANTIS_PASSWORD, TRAC_ENV, TRAC
_CLEAN)
  File "C:\Documents and Settings\dec\Desktop\Trac on windows\mantis2trac.1721.p
y", line 642, in convert
    trac.addTicket(**ticket)
  File "C:\Documents and Settings\dec\Desktop\Trac on windows\mantis2trac.1721.p
y", line 400, in addTicket
    (id, time.strftime('%s'), changetime.strftime('%s'), component.encode('utf-8
'),
  File "C:\Python24\lib\site-packages\trac\db\util.py", line 47, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "C:\Python24\lib\site-packages\trac\db\util.py", line 47, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "C:\Python24\lib\site-packages\mysql_python-1.2.2-py2.4-win32.egg\MySQLdb
\cursors.py", line 166, in execute
  File "C:\Python24\lib\site-packages\mysql_python-1.2.2-py2.4-win32.egg\MySQLdb
\connections.py", line 35, in defaulterrorhandler
_mysql_exceptions.OperationalError: (1366, "Incorrect integer value: '' for colu
mn 'time' at row 1")

So I'm now stuck. I've tried all the combinations I can think of and have reached the limit of my understanding of what's going on. Hope this helps?!

comment:12 Changed 7 years ago by sngautam@…

I get this error:

  1. import components...

sql: SELECT category, user_id as owner FROM mantis_project_category_table
warning: unknown mantis userid 0, recording as anonymous
inserting component ' Marketing Request ', owner anonymous
inserting component ' Bugs ', owner anonymous
inserting component ' Customer Support ', owner anonymous
inserting component ' Change Request(Tech) ', owner anonymous
inserting component ' Change Request(Design and content) ', owner anonymous
inserting component ' New Feature ', owner anonymous
inserting component ' Hiring ', owner anonymous
inserting component ' Monitoring ', owner anonymous
inserting component ' Hiring ', owner anonymous

Traceback (most recent call last):
  File "./mantis2trac.py", line 943, in <module>
    main()
  File "./mantis2trac.py", line 940, in main
    convert(MANTIS_DB, MANTIS_HOST, MANTIS_USER, MANTIS_PASSWORD, TRAC_ENV, TRAC_CLEAN)
  File "./mantis2trac.py", line 596, in convert
    trac.setComponentList(components, 'category')
  File "./mantis2trac.py", line 353, in setComponentList
    (comp[key].encode('utf-8'), comp['owner'].encode('utf-8'),))
  File "/usr/lib/python2.5/site-packages/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.5/site-packages/trac/db/sqlite_backend.py", line 56, in execute
    args or [])
  File "/usr/lib/python2.5/site-packages/trac/db/sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
pysqlite2.dbapi2.IntegrityError: column name is not unique

comment:13 Changed 7 years ago by The Fixer

  • Severity changed from normal to major

Hi,
I also had have this problem.

all places with

INSERT INTO ticket_change

should be replaced with

INSERT OR IGNORE INTO ticket_change

and then the script will work.

(BTW: Furtermore, I had to replace all 'utf-8' with 'latin1' to make it work with all my used German umlauts. Maybe this should be made configurable as script command line argument...)

comment:14 Changed 7 years ago by The Fixer

Hmm...now with that 'INSERT OR IGNORE ticket_change' it converts well but I get a runtime-error:

Traceback (most recent call last):
  File "C:\Program Files\Python24\Lib\site-packages\trac\web\main.py", line 406, in dispatch_request
    dispatcher.dispatch(req)
  File "C:\Program Files\Python24\Lib\site-packages\trac\web\main.py", line 237, in dispatch
    resp = chosen_handler.process_request(req)
  File "C:\Program Files\Python24\Lib\site-packages\trac\ticket\report.py", line 88, in process_request
    resp = self._render_view(req, db, id)
  File "C:\Program Files\Python24\Lib\site-packages\trac\ticket\report.py", line 346, in _render_view
    value['date'] = format_date(cell)
  File "C:\Program Files\Python24\Lib\site-packages\trac\util\datefmt.py", line 68, in format_date
    return format_datetime(t, format, gmt)
  File "C:\Program Files\Python24\Lib\site-packages\trac\util\datefmt.py", line 58, in format_datetime
    t = time.localtime(float(t))
ValueError: empty string for float()

What can I do now? *sigh*

(Hmm...and, please, forget the comment about using of 'latin1' because 'utf-8' was correct. Before the mantis2trac script call I had to convert my database to utf-8. Because trac can only handle utf-8.)

comment:15 Changed 7 years ago by The Fixer

The whole thing seems to be related to ticket #5278

comment:16 Changed 7 years ago by The Fixer

comment:17 Changed 7 years ago by The Fixer

I tried this and converting and web-gui to the tickets run fine now. Thanks to licho@…! :-) I think this ticket can be closed now.

comment:18 Changed 7 years ago by sngautam@…

I'm still facing the same problem. I used the patched script given in #5855

++++
pysqlite2.dbapi2.IntegrityError?: column name is not unique
++++

This is my error message.
Same as posted above.

comment:19 Changed 2 years ago by codeFiend

The Fixer is right, it's due to the Trac ticket #5278. I don't have a test database to test with right now, but the latest code is now on github, please feel free to submit patches.

Add Comment

Modify Ticket

Action
as reopened .
Author


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

 
Note: See TracTickets for help on using tickets.