Update_post_meta is case insensitive on meta_key, but get_post_meta is NOT

[anmari]

In WordPress 3.3-aortic-dissection and 3.2.1

get_post_meta is case sensitive on the meta-key

BUT

update_post_meta is NOT case sensitive

Thus If there is a pre-existing meta record with a key in say UPPERCASE, then one can issue an update for a lowercase key that one can not then fetch as it does not exist - only the uppercase key exists.

Example Code

	$meta = get_post_meta ($post->ID, '_allday');
	if ($meta ) { echo '<br />got lower: '; var_dump($meta);
	}
	$meta = get_post_meta ($post->ID, '_ALLDAY');
	if ($meta ) { echo '<br />got upper: '; var_dump($meta);
	}
	update_post_meta (21, '_allday','Tried to update lowercase');
	$meta = get_post_meta ($post->ID, '_allday');
	if ($meta ) { echo '<br />got lower: '; var_dump($meta);
	}
	else { echo '<br />Tried to get lower but no go';
	}
	$meta = get_post_meta ($post->ID, '_ALLDAY');
	if ($meta ) { echo '<br />Still have upper: '; var_dump($meta);
	}

Output of above:

got upper: array(1) { [0]=> string(14) "tis the upper." }
Tried to get lower but no go
Still have upper: array(1) { [0]=> string(25) "Tried to update lowercase" }

[anmari]

Further..

delete_post_meta also appears to be case_insensitive.

IE: one can delete issue

delete_post_meta(21, '_ALLDAY');

and any lowercase entries will be deleted too.

[anmari]

get_posts also NOT case sensitive:

	$args = array(
	'posts_per_page' => '-1',
	'post_type'=>'any',
	'meta_key'=> $key  // update to your key
);
	$allpostwith = get_posts( $args );

will return a MIX of lower and uppercase.

Is it caused by a mysql 'feature' perhaps? Similar behaviour in phpmyadmin? by why get_post_meta works ie is case sensitive ?

[xknown]

Yes, it is due to the collation used by default in WP (utf8_general_ci), which is case insensitive. There are more cases than case insensivity, for example the following query returns true:

select ('_allday' COLLATE utf8_general_ci) = ('_Älldây        ' COLLATE utf8_general_ci)

The reason why get_post_meta does not work for you is because the meta data are first cached in PHP using an array with key/value pairs and as you know, the string comparison in PHP is case sensitive.

[anmari]

I just very quickly learnt a whole heap more about collation and the sorting and matching issues especially for foreign languages.

I gather the _ci (case insenstive) collations are faster than the _cs (case sensitive) and different collations define different orders of search results. Eg: _bin the binary ones do not sort the capital 'A' with the lower 'a'.

It seems weird to give up accuracy for speed, and it seems that in mysql one can assign a collation (if available) at query time. Is there a case that functions that work together should do the 'same' thing. Eg: maybe use a binary collation where 'matching' keys is important?

Are there some 'best practises' around appropriate way to handle this that could be communicated. So far I've added a couple of edits to the get_post_meta and update_post_meta codex to highlight the risks in mixing case, or changing case of one's meta key.

For others who may read, these are useful
​http://dev.mysql.com/doc/refman/5.0/en/charset-collations.html
​http://hakre.wordpress.com/2010/12/26/wordpress-database-charset-and-collation-configuration/

[kdoole]

I don't know of / couldn't find best practices around this.

The collation can be changed in the query statement, so one option would be adding COLLATE utf8_general_cs when adding / updating / deleting meta. Would it be worthwhile to benchmark the performance difference between ci and cs?

Another option would be allowing users to set this themselves with a parameter when using the various get/update/insert/delete post meta fns. Seems like overkill though. Is it at all useful to let people make meta fields case sensitive?

Perhaps it's simply a matter of updating get_post_meta to be case INsensitive (and noting in the codex that post meta keys are case insensitive) so that everything works consistently.

[kdoole]

Added tests for the cases @anmari describes

[webaware]

I ran into a related problem with user meta. A client used a CSV importer and accidentally capitalised the first letter of a meta key. Because case insensitivity in MySQL, but case sensitivity in the WP user meta PHP code, the billing last name would never load "for some users". Fixed with this query, and a flush of the object cache:

update wp_usermeta
set meta_key = 'billing_last_name'
where meta_key collate utf8mb4_bin = 'Billing_last_name'

FWIW, I ran the query below on one of my testbed sites with over 200 plugins (multisite, lots of compatibility testing) and didn't find any examples of plugins having user meta keys with capital letters.

select distinct meta_key
from wp_usermeta
where meta_key collate utf8mb4_bin regexp '[A-Z]'

Post meta is a different story, however; meta keys are sometimes directly used on the front end, and thus may legitimately contain capital letters.

[jrchamp]

Rather than specifying the collation, you'll have better luck using the BINARY keyword. Example:

SELECT meta_key, meta_value
FROM wp_usermeta
WHERE meta_key = BINARY 'Billing_last_name'

Note that it's also important from a performance perspective to place the BINARY keyword on the value side of the equals or regexp (otherwise, you may not be able to benefit from the existing index).

[webaware]

Replying to jrchamp:

Rather than specifying the collation, you'll have better luck using the BINARY keyword.
...
Note that it's also important from a performance perspective to place the BINARY keyword on the value side of the equals or regexp (otherwise, you may not be able to benefit from the existing index).

Thanks for the tips, will try to remember for next time I bump into this one :)

[pbearne]

I have run the test provided and this seems to be fixed
So closing ticket as invalid