All Reportviews failed with a SQL syntax error using mysql

[s.stollfuss@…]

Hi@ll

I'm using the Trac 0.11.1 on a gentoo system. I opened a first test Ticket and want to see it in the reports and I became this error message.

Click on "My Tickets"

Report execution failed: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int), milestone, t.type, time\r\n) AS tab' at line 11")

Click on "Active Tickets"

Report execution failed: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int), milestone, t.type, time\r\n) AS tab' at line 11")

and soon….

The debug trac.log say the following for that:

My Tickets

2008-10-22 16:33:26,440 Trac[report] DEBUG: Executing report with SQL "
SELECT p.value AS __color__,
   (CASE status WHEN 'accepted' THEN 'Accepted' ELSE 'Owned' END) AS __group__,
   id AS ticket, summary, component, version, milestone,
   t.type AS type, priority, time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE t.status <> 'closed' AND owner = %s
  ORDER BY (status = 'accepted') DESC, CAST(p.value AS int), milestone, t.type, time
"
2008-10-22 16:33:26,440 Trac[report] DEBUG: Request args: {'id': u'7'}
2008-10-22 16:33:26,442 Trac[chrome] DEBUG: Prepare chrome data for request
2008-10-22 16:33:26,448 Trac[session] DEBUG: Retrieving session for ID u'steffen'
2008-10-22 16:33:26,518 Trac[main] DEBUG: 402 unreachable objects found.

Active Tickets

2008-10-22 16:35:17,276 Trac[report] DEBUG: Executing report with SQL "
SELECT p.value AS __color__,
   id AS ticket, summary, component, version, milestone, t.type AS type,
   owner, status,
   time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE status <> 'closed'
  ORDER BY CAST(p.value AS int), milestone, t.type, time
"
2008-10-22 16:35:17,276 Trac[report] DEBUG: Request args: {'id': u'1'}
2008-10-22 16:35:17,277 Trac[chrome] DEBUG: Prepare chrome data for request
2008-10-22 16:35:17,284 Trac[session] DEBUG: Retrieving session for ID u'steffen'
2008-10-22 16:35:17,358 Trac[main] DEBUG: 402 unreachable objects found.

The System:

• Apache/2.2.9 (Gentoo)
• mod_ssl/2.2.9
• OpenSSL/0.9.8i
• mod_python/3.3.1
• Python/2.5.2
• PHP/5.3.0-pl0-gentoo
• MySQL-5.0.60-log
• Trac 0.11.1
  • TracMasterTickets 2.1.3
  • TracProgressMeterMacro 0.1
  • TracWysiwyg 0.2
  • graphviz 0.7.4

Does anyone knows whats going on here ??? I search for a while in the internet without a solution for me.

regards Steffen

[Remy Blank]

There are some subtle differences in SQL syntax between the different databases, most notably with CAST(), concatenation and LIKE. The default reports provided on installation have been written for SQLite.

I don't have an installation with MySQL, but you might want to try to change CAST(... AS int) into CAST(... AS signed), as that's what Trac uses in the MySQL backend.

Please note also that MySQL support is very experimental at this point. You might be better served with SQLite, or Postgres if you need better concurrency.

[Christian Boos]

Well, our MySQL support is not to blame here, rather the fact that (on purpose) custom reports are not changed during an upgrade, so he had some old or different SQL than the one he would have got for a fresh installation.

But as you said, here the fix is to replace int by signed.

I looked at my own My Tickets SQL, and I indeed see:

SELECT p.value AS __color__,
   (CASE status WHEN 'accepted' THEN 'Accepted' ELSE 'Owned' END) AS __group__,
   id AS ticket, summary, component, version, milestone,
   t.type AS type, priority, time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE t.status <> 'closed' AND owner = $USER
  ORDER BY (status = 'accepted') DESC, CAST(p.value AS signed), milestone, t.type, time

The above report was created with a 0.11.1 code base, so there's no problem for new installations. It's indeed a duplicate of #5543, because since we're using db_cast, we must also have signed instead of int automatically (since r3716).