There's many weird things happenning around global categories, i thought i'd open a thread to discuss it and get some feedback from others before maybe opening bugs in trac.
ok, the code for global categories reads:
...
function global_categories( $cat_ID ) {
global $wpdb;
$cat_ID = intval( $cat_ID );
$c = $wpdb->get_row( "SELECT * FROM $wpdb->categories WHERE cat_ID = '$cat_ID'" );
$global_category = $wpdb->get_row( "SELECT * FROM $wpdb->sitecategories WHERE category_nicename = '" . $wpdb->escape( $c->category_nicename ) . "'" );
if ( $global_category ) {
$global_id = $global_category->cat_ID;
} else {
$wpdb->query( "INSERT INTO $wpdb->sitecategories ( cat_name, category_nicename ) VALUES ( '" . $wpdb->escape( $c->cat_name ) . "', '" . $wpdb->escape( $c->category_nicename ) . "' )" );
$global_id = $wpdb->insert_id;
}
$wpdb->query( "UPDATE $wpdb->categories SET cat_ID = '$global_id' WHERE cat_id = '$cat_ID'" );
$wpdb->query( "UPDATE $wpdb->categories SET category_parent = '$global_id' WHERE category_parent = '$cat_ID'" );
etc.
So basically, when i create a new cat, there's a lookup on the global cats table, and my local new cat gets assigned the global ID.
Only the lookup is made on category_nicename.
There are several issues with this, one being you can't create multiple different categories with the same nicename.
OK this i could live with, but something stranger seems to happen : in my global categories table, i have many categories with the same nicename. Ex:
mysql> select cat_ID, cat_name, category_nicename from wp_sitecategories where category_nicename='musique';
+--------+--------------------------------------+-------------------+
| cat_ID | cat_name | category_nicename |
+--------+--------------------------------------+-------------------+
| 27 | Musique | musique |
| 853 | Chant Choral | musique |
| 1173 | paroles des chansons | musique |
| 2676 | musique... | musique |
| 3240 | version piano | musique |
| 3241 | instrumental | musique |
| 8200 | musique !! | musique |
| 13248 | j'ai écouté | musique |
| 16118 | 1 2 3 ...musique | musique |
| 16445 | Musique Pop-Rock-New-wave | musique |
| 16617 | Actu Music | musique |
| 17162 | MUSIQUE. | musique |
| 17283 | Tous les artistes | musique |
| 19033 | La Music | musique |
| 19043 | Move your body ! | musique |
| 19376 | --------Musique-------- | musique |
| 21838 | Musique, concerts, groupes et albums | musique |
| 21839 | Musique; concerts, groupes et albums | musique |
| 22354 | MUSIQUE !!! | musique |
| 23077 | Musique/POP-ROCK/New-wave | musique |
| 23078 | Musique/POP-ROCK-New-wave | musique |
| 24732 | musique/Musique live | musique |
+--------+--------------------------------------+-------------------+
22 rows in set (0.00 sec)
How can this happen ? First the nicename isn't what it should for those cats, and then even if it was the category should have been given the ID of the first one in the list at creation time right ? At first I thought it'd be when you modify a category name that maybe it didn't reflect on the nicename. But I've just tried updating a category name, and while the cat keeps its global ID, the global cat isn't updated, so I have no clue how these arrived like this in the global cat table.
What's clear is that as soon as you use the modify name thing for categories, the link between the local cat and the global one is broken, making global cats virtually useless (well if anyone wanted to use them).
Also, I haven't yet found the exact sequence of events that lead to it, but it happens from time to time on my platform that a category is given its own ID as parent_id. That results in ugly infinite loops, big CPU spikes and slowdowns, and the only way i've found to fix it is to script something that checks all local category tables.
This is all using MU1.1.
Anyway, I wanted to know if someone else was experiencing this kind of problem or had an explanation for the phenomenon.
First, to make sure it's not something to do with my version, could someone with a large install (several hundreds blogs minimum, or you may just be "lucky" that the problem never occurred yet) run this query:
select cat_name, category_nicename, count(*) as thec from wp_sitecategories group by category_nicename order by thec desc limit 0,5;
and let me know if they too have multiple global cats with the same nicename ? This already I can't explain how it happens.
If anyone has any feedback / comments they're really welcome.
