Query failed in serendipity_event_freetag

[vwm]

Hi,

I apologise in advance in case I post this in the wrong forum. However I'd like to announce some bug I encountered with the plug-in serendipity_event_freetag and the workaround that fixed it for me.

This report is about serendipity_event_freetag Version 3.23 on Serendipity 1.5.3 (I know it's not current. That's another thing on my todo list) with PHP 5.3.6. Database is MySQL.

== Bug Description: ==

Tags containing special Characters (in my case German umlauts and the sz ligature) cause Database Errors that are passed through to the User.

Example:

Code: Select all

http://www.example.com/index.php?/plugin/tag/Fachkr%E4fte
or
http://www.example.com/index.php?/plugin/tag/Fachkräfte

Returns:

Code: Select all

Query failed:

SELECT 
[... Server rejects my html attachment with the comped Error message. Drop me a note, if you need it]
/ Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

Expected Behaviour

• Query should not fail
• Query should at least fail graceful (i.e. not shout SQL at the user, and possibly reveal innards of the server configuration)

== Workaround ==

I changed line 996 in serendipity_event_freetag.php
from

Code: Select all

$cond = "entrytags.tag = '$showtag' ";

to

Code: Select all

$cond = "entrytags.tag = '$showtag' COLLATE utf8_unicode_ci";

Now .../plugin/tag/Fachkräfte will return the correct results. /plugin/tag/Fachkr%E4fte (that is what serendipity_plugin_freetag produces) does not work, still. However it will return an empty set and not die with an SQL Error.

That works for me -- however I did not conduct a thorough testing (other database, other character sets, etc.). I just checked that my regular tags still work and that I can no longer reproduce Database errors. However, while

Maybe my workaround helps somebody else as well. I'd be happy to hear about more elegant solutions.

Regards

Vincent

PS: I Know that this can probably be solved by tuning the database, however I do not have full privileges for database that is hosted by a provider.

[LazyBadger]

I do not have full privileges for database that is hosted by a provider

AFAIK, all good ISP will give the ability to create DB with correct charset and collation order
From my POV - it's not Serendepity job to build workaround for misconfigured platforms... and after all Serendipity have now "Use SET NAMES", which solve (?) such issues

[garvinhicking]

Hi!

Vincent is right basically. When s9y creates the tables, it uses a compatible way for all databases. This means, we do not use MySQL specific charset features, and so s9y uses the default setting of the mysql server. That still is latin1_swedish_ci on many, many servers and not UTF-8.

It's a shame that MySQL is so picky about the charset lookup that it throws a fatal error.

Sadly it is also virtually impossible to detect if you are running UTF-8 and someone submitted ISO only to you, because distinguising UTF-8 from ISO is technically not really possible.

Long story short: I'll commit vincents patch with the minor addition that the COLLATION feature will only be added for MySQL(i) database types.

Thanks for bringing this forward!

Regards,
Garvin

[MarioH]

Hi,

version 3.24 of the plugin gives me the following error when klicking a tag-link:

Code: Select all

Query failed:

SELECT 
                    ep_sticky.value AS orderkey,

                    e.id,
                    e.title,
                    e.timestamp,
                    e.comments,
                    e.exflag,
                    e.authorid,
                    e.trackbacks,
                    e.isdraft,
                    e.allow_comments,
                    e.last_modified,

                    a.realname AS author,
                    a.username AS loginname,
                    a.email
                     , e.body, e.extended
                     
                FROM
                    serendipity_entries AS e
                    LEFT JOIN serendipity_authors a
                        ON e.authorid = a.authorid
                    LEFT JOIN serendipity_entrycat ec
                        ON e.id = ec.entryid
                    LEFT JOIN serendipity_category c
                        ON ec.categoryid = c.categoryid
                    INNER JOIN serendipity_entrytags AS entrytags ON (e.id = entrytags.entryid)  LEFT OUTER JOIN serendipity_entryproperties ep_access
                                              ON (e.id = ep_access.entryid AND ep_access.property = 'ep_access')
 LEFT JOIN serendipity_entryproperties ep_sticky
                                            ON (e.id = ep_sticky.entryid AND ep_sticky.property = 'ep_is_sticky') LEFT JOIN serendipity_authorgroups AS acl_a
                                   ON acl_a.authorid = 1
                            LEFT JOIN serendipity_access AS acl_acc
                                   ON (    acl_acc.artifact_mode = 'read'
                                       AND acl_acc.artifact_type = 'category'
                                       AND acl_acc.artifact_id   = c.categoryid
                                      )
                    WHERE isdraft = 'false' AND e.timestamp <= 1308692400 AND entrytags.tag = 'banking' COLLATE utf8_unicode_ci  AND  (ep_access.property IS NULL OR ep_access.value = 'member' OR ep_access.value = 'public' OR (ep_access.value = 'private' AND e.authorid = 1))   AND     (
                                 c.categoryid IS NULL
                                 OR ( acl_acc.groupid = acl_a.groupid OR acl_acc.groupid = 0)
                                 OR ( acl_acc.artifact_id IS NULL
                                      
                                    )
                               )
                     GROUP BY e.id
                     
                     ORDER BY orderkey DESC, timestamp DESC
                      LIMIT 10

/ 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 'COLLATE utf8_unicode_ci AND (ep_access.property IS NULL OR ep

The database is a V4 MySQL.

Greets
Mario

[yellowled]

version 3.24 of the plugin gives me the following error when klicking a tag-link:

Smells like the one reported in the German forum?

YL

[MarioH]

Hi Yellowled,

no, the error at the end of the qouted code is not the same. The 3.24 has the collate-workaround from this thread implemented and i think a part of this new code causes the error.

Greets
Mario

[Timbalu]

Could you try and move the COLLATE utf8_unicode.ci down, behind the whole WHERE string, just before the GROUP BY statement and try the SQL with PhpMyAdmin?

[Timbalu]

Well, moving the COLLATE string is not the answer!

The answer is, you use an old Mysql Database!
You really should try to switch to Mysql 5, which will be better in general and procures more fun with some plugins!

[garvinhicking]

Hi!

COLLATE should be supported in Mysql4 properly too, though.

I've jut committed a new bugfix to version 3.25 of the plugin, adding a new typecast and hope to fix the issue with that...

Regards,
Garvin

[MarioH]

Hi!

@Timbalu
I know about the old mysql, i hope i will find the time to update to the new version in near future.

@garvin
The new version does'nt fix the problem.

Code: Select all

Query failed:

SELECT 
                    ep_sticky.value AS orderkey,

                    e.id,
                    e.title,
                    e.timestamp,
                    e.comments,
                    e.exflag,
                    e.authorid,
                    e.trackbacks,
                    e.isdraft,
                    e.allow_comments,
                    e.last_modified,

                    a.realname AS author,
                    a.username AS loginname,
                    a.email
                     , e.body, e.extended
                     
                FROM
                    serendipity_entries AS e
                    LEFT JOIN serendipity_authors a
                        ON e.authorid = a.authorid
                    LEFT JOIN serendipity_entrycat ec
                        ON e.id = ec.entryid
                    LEFT JOIN serendipity_category c
                        ON ec.categoryid = c.categoryid
                    INNER JOIN serendipity_entrytags AS entrytags ON (e.id = entrytags.entryid)  LEFT OUTER JOIN serendipity_entryproperties ep_access
                                              ON (e.id = ep_access.entryid AND ep_access.property = 'ep_access')
 LEFT JOIN serendipity_entryproperties ep_sticky
                                            ON (e.id = ep_sticky.entryid AND ep_sticky.property = 'ep_is_sticky') LEFT JOIN serendipity_authorgroups AS acl_a
                                   ON acl_a.authorid = 0
                            LEFT JOIN serendipity_access AS acl_acc
                                   ON (    acl_acc.artifact_mode = 'read'
                                       AND acl_acc.artifact_type = 'category'
                                       AND acl_acc.artifact_id   = c.categoryid
                                      )
                    WHERE isdraft = 'false' AND e.timestamp <= 1308815100 AND entrytags.tag = _utf8  'Administration' COLLATE utf8_general_ci  AND  (ep_access.property IS NULL OR ep_access.value = 'public')  AND     (
                                 c.categoryid IS NULL
                                 OR ( acl_acc.groupid = 0)
                                 OR ( acl_acc.artifact_id IS NULL
                                      
                                    )
                               )
                     GROUP BY e.id
                     
                     ORDER BY orderkey DESC, timestamp DESC
                      LIMIT 10

/ 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 ''Administration' COLLATE utf8_general_ci AND (ep_access.prope

Is there a missing ')' after 'c.categoryid IS NULL'? Is the '_utf8' after 'entrytags.tags =' ok?

Greets
Mario

[MarioH]

Just tried in PHPMyAdmin:

When i delete '_utf8' and 'COLLATE utf8_general_ci' from the select, it works properly.

Greets
Mario

[Timbalu]

I just tested you query and it seems ok here. (MySql 5!)

[MarioH]

Ok, i tested the 3.25 on another S9Y-Blog, where i have a mysql 5 database and it works properly.

So i think it is definitely a problem with the mysql 4 database.

Greets
Mario

[MarioH]

Hello again,

now i updated my database to mysql 5 and everything works fine.

Thanks to everyone.

Mario

[garvinhicking]

Hi!

I've justed committed a new patch to the plugin (3.27). This adds a new config option to the plugin (autodetected by default) that contains the CURRENT collation type of the "tag" column, so that the appropriate query can be performed. It will only be utf-8typecast now if the column is NOT set to a utf-8 column, so the collation feature should be disabled properly if everything matches UTF-8.

Can those of you who are/were still having trouble check the new plugin, and maybe also those people for whom it now works, to see if it still works?

Thanks,
Garvin