Edgewall Software
Modify

Opened 15 years ago

Closed 15 years ago

#7745 closed defect (duplicate)

All Reportviews failed with a SQL syntax error using mysql

Reported by: s.stollfuss@… Owned by:
Priority: normal Milestone:
Component: report system Version: 0.11.1
Severity: blocker Keywords:
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:

Description

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

Attachments (0)

Change History (2)

comment:1 by Remy Blank, 15 years ago

Milestone: 0.11.20.13

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.

comment:2 by Christian Boos, 15 years ago

Milestone: 0.13
Resolution: duplicate
Status: newclosed

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

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The ticket will remain with no owner.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from (none) to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.