MySQL support is partly functional

[Peter Bolla]

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!

[Peter Bolla]

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!

[Martin Scharrer]

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.

[Martin Scharrer]

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.

[Martin Scharrer]

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!

[Peter Bolla]

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

[Martin Scharrer]

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

[Peter Bolla]

Looks like it's working now!

Thanks a lot!