Modify

#11044 closed defect (worksforme)

"OperationalError: no such table: mastertickets" with 1.1.0

Reported by: asdorsey Owned by: anbo
Priority: normal Component: ProjectPlanPlugin
Severity: normal Keywords:
Cc: Trac Release:

Description

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

Attachments (0)

Change History (9)

comment:1 Changed 18 months ago by anbo

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

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

comment:2 Changed 18 months ago by asdorsey

  • Resolution worksforme deleted
  • Status changed from closed to reopened

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.

comment:3 Changed 18 months ago by asdorsey

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

comment:4 Changed 18 months ago by asdorsey

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> 

comment:5 Changed 18 months ago by asdorsey

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.

comment:6 Changed 18 months ago by anbo

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

comment:7 Changed 18 months ago by rjollos

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.

comment:8 Changed 18 months ago by anbo

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

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

comment:9 Changed 18 months ago by anbo

  • Resolution set to worksforme
  • Status changed from reopened to closed

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

Add Comment

Modify Ticket

Action
as 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.