database search and collation

Conversation started Aug 26, 2012 at 17:40.

toscho

Aug 26, 2012 17:40

getting strange results from MySQL … this is so annoying.

SELECT SQL_CALC_FOUND_ROWS  mu_4_posts.ID
    FROM mu_4_posts
    WHERE 1=1
    AND (((mu_4_posts.post_title LIKE '%furc%')
    OR (mu_4_posts.post_content LIKE '%furc%')))
    AND mu_4_posts.post_type IN ('post', 'page', 'attachment')
    AND (mu_4_posts.post_status = 'publish')
    ORDER BY mu_4_posts.post_date DESC
    LIMIT 0, 4

Some results do not contain the search string. I don’t understand it. :/

Rarst

seems to work normally for me... are you sure there is no partial match somewhere?

toscho

yes, I checked it multiple times

Rarst

running through wp or directly?

toscho

the string is not even part of the markup

Rarst

(1) try directly (2) reset object cache if using it

toscho

Aug 26, 2012 17:50

Here is one match for this search string: toscho.de/2010/das-falsche-gebot

Rarst

it has "Fürchte" check collation stuff

toscho

no object cache

ah … good idea

Hm, no

define('DB_COLLATE', 'utf8_general_ci');

That should do it.

Rarst

still seems like most likely reason...

toscho

yes

Switched to utf8_unicode_ci which is better anyway – still no progress

Rarst

there is some code in $wpdb that sets up conenction, maybe something goes bonkers there... is there a way to pull opened connection properties? don't know

toscho

Aug 26, 2012 18:12

Ah, the collation stays utf8_general_ci once they are created.

kaiser

Could we please all switch to Letter-Avatars like U & R? Maybe we'd get some nice words out of it ;)

Stephen Harris

WPSE Countdown! (en.wikipedia.org/wiki/Countdown_(game_show))

Rarst

@StephenHarris wat?

Stephen Harris

@Rarst With letter avatars we could play countdown.... you get (I think) 9 letters and 30 seconds to make a word out of it.. longest word wins :)

toscho

ALTER TABLE changed the collation … but not the search results.

Rarst

Aug 26, 2012 18:20

@toscho tried direct query?

toscho

yes

Rarst

try explicit collation in query?

A: Mysql unique index does't work on a certain umlaut

It's because of the collation you are using. Anything that ends with _ci is case-insensitive (and also accent/umlaut insensitive). So yes, MySQL will consider "günni" and "gunni" the same thing, unless you change your collation. Docs: http://dev.mysql.com/doc/refman/5.0/en/charset-table.html

A: MySQL DB selects records with and without umlauts. e.g: '.. where something = FÖÖ'

SELECT * FROM table WHERE some_field LIKE ('%ö%' COLLATE utf8_bin)

toscho

Aha!

SELECT  *
FROM  `mu_4_posts`
WHERE post_content LIKE  '%furc%'
COLLATE utf8_bin
LIMIT 0 , 30

Rarst

I would never make logical jump from case sensitivity to umlaut %)

toscho

But now it isn’t case insensitive anymore, right?

Rarst

Aug 26, 2012 18:27

yep

toscho

dangit

hm, I could change the query before I send it to the db

toscho

Aug 26, 2012 18:42

oh, this is really tricky.

Conversation ended Aug 26, 2012 at 18:42.

all rooms

Aug '1226

The Loop

Where humor doesn’t work.

context of the conversation
join this room
about this room

Participants

toscho 58%
Rarst 33%
Stephen Harris 5%
kaiser 2%

all times are UTC