"OperationalError: no such table: mastertickets" with 1.1.0

[Adam Dorsey - NOAA Affiliate]

When I attempt to upgrade to ProjectPlanPlugin 1.1.0, built from trunk, I get the following error when accessing a ticket with a dependency:

Traceback (most recent call last):
  File "/usr/lib/python2.6/site-packages/Trac-1.0.1-py2.6.egg/trac/web/api.py", line 514, in send_error
    data, 'text/html')
  File "/usr/lib/python2.6/site-packages/Trac-1.0.1-py2.6.egg/trac/web/chrome.py", line 983, in render_template
    stream |= self._filter_stream(req, method, filename, stream, data)
  File "/usr/lib/python2.6/site-packages/Genshi-0.6-py2.6.egg/genshi/core.py", line 132, in __or__
    return Stream(_ensure(function(self)), serializer=self.serializer)
  File "/usr/lib/python2.6/site-packages/Trac-1.0.1-py2.6.egg/trac/web/chrome.py", line 1162, in inner
    data)
  File "build/bdist.linux-x86_64/egg/projectplan/ppticketviewtweak.py", line 65, in filter_stream
    self.lazy_init(req.authname)
  File "build/bdist.linux-x86_64/egg/projectplan/ppticketviewtweak.py", line 31, in lazy_init
    self.field = PPConfiguration( self.env ).get('custom_dependency_field')
  File "build/bdist.linux-x86_64/egg/projectplan/ppenv.py", line 647, in __init__
    self.load()
  File "build/bdist.linux-x86_64/egg/projectplan/ppenv.py", line 856, in load
    """, ppenv=self)
  File "build/bdist.linux-x86_64/egg/projectplan/ppenv.py", line 378, in __init__
    cursor.execute(sql)
  File "/usr/lib/python2.6/site-packages/Trac-1.0.1-py2.6.egg/trac/db/util.py", line 66, in execute
    return self.cursor.execute(sql)
  File "/usr/lib/python2.6/site-packages/Trac-1.0.1-py2.6.egg/trac/db/sqlite_backend.py", line 78, in execute
    result = PyFormatCursor.execute(self, *args)
  File "/usr/lib/python2.6/site-packages/Trac-1.0.1-py2.6.egg/trac/db/sqlite_backend.py", line 56, in execute
    args or [])
  File "/usr/lib/python2.6/site-packages/Trac-1.0.1-py2.6.egg/trac/db/sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: no such table: mastertickets

[Andreas]

If you do not use the Mastertickets plugin already, you have to creating a new table.

Please use the script marked for ProjectPlanPlugin version 1.0.0 provided via SVN to update the database: source:projectplanplugin/0.11/trunk/projectplan/sql_import

[Adam Dorsey - NOAA Affiliate]

OK, I applied the projectplan_1.0.0.sql file with a few issues:

• The file supplied was for MySQL, so I had to edit the file a bit to get it to work with SQLite. The modified version is:

  -- 
  -- If you do not use the mastertickets plugin, you need to use this create, 
  --   s.t., the corresponding table is created.
  -- This table is need at the projectplan plugin version 1.0.0 or higher.
  --
  -- Table structure for table `mastertickets`
  --
  
  CREATE TABLE `mastertickets` (
    `source` int(11) NOT NULL DEFAULT '0',
    `dest` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`source`,`dest`)
  ) ;
  

• After applying that SQL file, tickets with dependencies load, but the dependency mouseover links are missing. The Dependencies field under Modify Ticket is also empty; it should be populated with the dependency ticket number by default.

[Adam Dorsey - NOAA Affiliate]

I found an option regarding mastertickets compatibility, I selected "convert dependencies" hoping it would convert my existing data and straighten everything out. However, after I set that option and clicked "Apply Changes", Trac had an internal error. The following is log output from that error.

2013-05-02 13:42:49,960 Trac[ppenv] DEBUG: convertDependenciesToMastertickets: SELECT: SELECT ticket as oldsource,value as olddest FROM ticket_custom LEFT JOIN mastertickets ON ticket=source WHERE name = 'dependencies' AND source IS NULL
2013-05-02 13:42:49,971 Trac[ppenv] WARNING: convertDependenciesToMastertickets: INSERT INTO mastertickets (source,dest) VALUES (355,48),(256,56),(32,63),(518,75),(201,98),(113,115),(148,121),(348,153),(349,153),(41,171),(228,192),(432,199),(243,220),(242,220),(54,229),(412,234),(260,249),(412,253),(579,260),(201,297),(159,317),(525,320),(314,324),(92,326),(294,327),(460,346),(351,352),(48,355),(417,409),(411,412),(256,415),(355,422),(436,426),(336,428),(439,428),(426,436),(543,451),(428,453),(329,453),(404,456),(468,498),(544,503),(545,503),(75,518),(412,552),(632,555),(578,555),(567,555),(584,557),(296,572),(525,572),(234,573),(555,573),(609,573),(567,573),(584,574),(633,579),(260,579),(459,582),(572,583),(594,595),(584,600),(669,622),(716,630),(476,651),(672,651),(651,672),(686,688)
2013-05-02 13:42:49,971 Trac[ppenv] WARNING: convertDependenciesToMastertickets: try to convert 68 ticket dependencies from ticket_custom to mastertickets.
2013-05-02 13:42:49,971 Trac[chrome] DEBUG: Prepare chrome data for request
2013-05-02 13:42:49,999 Trac[main] ERROR: Internal Server Error: 
Traceback (most recent call last):
  File "/usr/lib/python2.6/site-packages/Trac-1.0.1-py2.6.egg/trac/web/main.py", line 497, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.6/site-packages/Trac-1.0.1-py2.6.egg/trac/web/main.py", line 214, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib/python2.6/site-packages/Trac-1.0.1-py2.6.egg/trac/admin/web_ui.py", line 125, in process_request
    path_info)
  File "build/bdist.linux-x86_64/egg/projectplan/projectplan.py", line 66, in render_admin_panel
    macroenv.convertDependenciesToMastertickets()
  File "build/bdist.linux-x86_64/egg/projectplan/ppenv.py", line 1154, in convertDependenciesToMastertickets
    cursor.execute(sql)
  File "/usr/lib/python2.6/site-packages/Trac-1.0.1-py2.6.egg/trac/db/util.py", line 66, in execute
    return self.cursor.execute(sql)
  File "/usr/lib/python2.6/site-packages/Trac-1.0.1-py2.6.egg/trac/db/sqlite_backend.py", line 78, in execute
    result = PyFormatCursor.execute(self, *args)
  File "/usr/lib/python2.6/site-packages/Trac-1.0.1-py2.6.egg/trac/db/sqlite_backend.py", line 56, in execute
    args or [])
  File "/usr/lib/python2.6/site-packages/Trac-1.0.1-py2.6.egg/trac/db/sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: near ",": syntax error

[Adam Dorsey - NOAA Affiliate]

It looks like the above error is SQLite related. I tried running that SQL manually on the database and got the following:

[root@p-014.p db] # sqlite3 trac.db
SQLite version 3.3.6
Enter ".help" for instructions
sqlite> INSERT INTO mastertickets (source,dest) VALUES (355,48),(256,56),(32,63),(518,75),(201,98),(113,115),(148,121),(348,153),(349,153),(41,171),(228,192),(432,199),(243,220),(242,220),(54,229),(412,234),(260,249),(412,253),(579,260),(201,297),(159,317),(525,320),(314,324),(92,326),(294,327),(460,346),(351,352),(48,355),(417,409),(411,412),(256,415),(355,422),(436,426),(336,428),(439,428),(426,436),(543,451),(428,453),(329,453),(404,456),(468,498),(544,503),(545,503),(75,518),(412,552),(632,555),(578,555),(567,555),(584,557),(296,572),(525,572),(234,573),(555,573),(609,573),(567,573),(584,574),(633,579),(260,579),(459,582),(572,583),(594,595),(584,600),(669,622),(716,630),(476,651),(672,651),(651,672),(686,688);
SQL error: near ",": syntax error
sqlite> 

[Adam Dorsey - NOAA Affiliate]

According to ​http://stackoverflow.com/questions/7958029/why-sqlite-complains-to-this-code SQLite doesn't support inserting multiple rows per insert statement. A couple solutions are given on that page; unfortunately, I'm not very good at SQL. I'll try to see what I can do for a workaround.

[Andreas]

Thanks for the information. I will provide a patch soon to workaround the SQL flaws of sqlite.

[Ryan J Ollos]

Most likely this would be resolved by properly utilizing the Trac Database API as described in ​t:TracDev/DatabaseApi (i.e. not preparing SQL statements by string formatting). The Trac database API will handle SQL inconsistencies across DB backends for you, but you have to prepare the SQL and pass the parameters correctly.

[Andreas]

(In [13070]) v1.1.3, patch release

converting tickets to Mastertickets table should work at SQLite too, refs #11044

[Andreas]

Thanks again for the feedback. If you observe any other issues, please report it.