Modify

Opened 4 years ago

Closed 4 years ago

#7433 closed defect (fixed)

MySQL support is partly functional

Reported by: buci@… Owned by: martin_s
Priority: normal Component: ListOfWikiPagesMacro
Severity: normal Keywords:
Cc: Trac Release: 0.11

Description

When using MySQL as backend, ListChangesBy doesn't work at all:

Error: Macro LastChangesBy(None) failed

1064You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''5'' at line 5

Also, the exclude option doesn't work with the ListOfWikiPages

Error: Macro ListOfWikiPages(format=long, from=14d, headline="Wiki pages modified in the last 14 days", exclude="RecentlyUpdated") failed

1064You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RecentlyUpdated']') AND version=(SELECT MAX(version) FROM wiki AS w2 WHERE w1.na' at line 1

Thank you!

Attachments (0)

Change History (7)

comment:1 follow-up: Changed 4 years ago by bucienator

Sorry, version information was missing:

MySQL:
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2

Trac, debian packaged:
0.11.7-3~bpo50+1

ListOfWikiPagesMacro:
Last Changed Rev: 7996

Also, is there a way, to hide my email address in this bug? Or replace by my newly registered user name: bucienator.

Thanks!

comment:2 Changed 4 years ago by martin_s

  • Status changed from new to assigned

Thanks for reporting this. My SQL-Fu is not very strong and it is a little difficult to test all database backends. I will have a look into it.

comment:3 in reply to: ↑ 1 Changed 4 years ago by martin_s

Replying to bucienator:

Also, is there a way, to hide my email address in this bug? Or replace by my newly registered user name: bucienator.

I don't have admin rights for that. You would have to ask the site administrator to do this.

comment:4 Changed 4 years ago by martin_s

I can't see what's wrong by looking on the SQL code only. Some log data would be very helpful.
I enabled SQL logging in [8309]. Could you do me the favor and install it, enable the debug mode in trac.ini and post the debug information from the log file here. Thanks!

comment:5 Changed 4 years ago by bucienator

Sure:

SQL for the exclude=... option:

2010-07-29 14:09:24,144 Trac[util] DEBUG: SQL: "SELECT name,time,author,version,comment FROM wiki AS w1 WHERE  time BETWEEN 1279195764 AND 1280405364 AND author NOT IN ('trac')  AND name NOT IN ('[u'RecentlyUpdated']') AND version=(SELECT MAX(version) FROM wiki AS w2 WHERE w1.name=w2.name) ORDER BY time DESC"

And for the LastChangesBy

2010-07-29 14:11:40,062 Trac[formatter] DEBUG: Executing Wiki macro LastChangesBy by provider <traclistofwikipages.macro.ListOfWikiPagesComponent object at 0x9b2958c>
2010-07-29 14:11:40,062 Trac[util] DEBUG: SQL: '\n              SELECT name,time,version,comment\n              FROM wiki AS w1 WHERE author = %s \n              AND version=(SELECT MAX(version) FROM wiki AS w2 WHERE w1.name=w2.name)\n              ORDER BY time\n           DESC LIMIT 0,%s '
2010-07-29 14:11:40,062 Trac[util] DEBUG: args: ('PeterBolla', '5')
2010-07-29 14:11:40,063 Trac[util] DEBUG: execute exception: ProgrammingError(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 ''5'' at line 5")

comment:6 Changed 4 years ago by martin_s

Thanks so much for the debug data.

The first issue is unrelated to MySQL, i.e. also affects other backends. I forgot to join a list correctly.

The second issue is a cast issue, '5' (string) instead of 5 (integer). The SQLite backend is very flexible in this matter, which allows this form of issues to sneak in. Also the Trac frontend interface keeps confusing me. I made now changes which should make it work for all backends.

Have a look at [8311] and tell me please if both issues disappeared.
Thanks,
Martin

comment:7 Changed 4 years ago by bucienator

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

Looks like it's working now!

Thanks a lot!

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.