Corrupt category indices?

Bloke · 2007-12-21 15:37:15

Anyone ever seen a category list (images in this case) that as soon as you edit/add/delete a category in the list, some of them don’t show up on the Content->Categories screen any more? Or they move about under other subcats?

I’m refactoring a site, moving pics about and want to remove/rename loads of old categories (I’ve got well over a hundred, all told, and its getting out of hand) but as soon as I do, some of the sub-cats get mashed.

Doing a ‘before’ and ‘after’ comparison of the txp_category table shows that the category I operated on has changed and it looks like rebuildTree() has rebalanced the list because some of the indices are different. But some of the subcats just don’t show up in the category screen any more. It doesn’t affect any other lists – the article, link and file lists are fine and I can operate on those to my heart’s content. But as soon as I touch an image category it breaks.

It’s repeatable: if I delete a particular category it’ll always “remove” a chunk of subcats from a main category and move one of those subcats under another main category. If I restore the DB (all hail rss_db_admin_manager!) then delete a different category it might remove an entire subtree from one of the categories; but it will always do that time after time I remove that category.

My tentative prognosis is that somehow the indices aren’t pointing where they should and when rebuildTree() is called, the shuffling causes chaos. But if the indices are wrong why do they show up in the correct tree structure in the first place and then not show up after an edit? I had a similar thing before (non-TXP) when I was testing a binary tree algorithm and I’d added more than 127 keys to the table that was only assigned tinyint(3) to the index. But TXP’s index is int(6) so there’s plenty of space.

Follow-on question: is there anything I can do to a) track down what’s going wrong, b) fix it without having to clear the table, re-input the categories in their exact same locations and reassign all 270 of my images to the correct categories? I assume truncating, then reloading the table and/or reindexing it will lose or re-assign the categories that point from the other tables? Anything I can do to shuffle cats around and keep the tables in sync?

If I just delete categories willy nilly from the database in MySQL, the DB integrity will fall apart when I go to the category screen and do anything, right? ‘Coz rebuildTree() will probably barf. So how do I delete categories safely if they don’t show up in the admin screen?

As a long shot I tried a repair but that did nothing. I’m stumped.

Last edited by Bloke (2007-12-21 15:38:05)

ruud · 2007-12-21 22:23:54

Deleting categories that don’t show up on the category list should work without side effects. Those categories probably have incorrect lft/rgt values set. Since the categories in the images/articles/links/files table are not based on ID, but on the category name, changing txp_category (without changing category names) does not cause inconsistencies with the other tables.

If you can reproduce this problem, I’d love to see a before and after txp_category table (and what actions you took to get to the latter. I though all of these category delete problems were fixed in 4.0.4 and if not, then I’d like to know what’s causing it.

Bloke · 2007-12-22 19:39:53

Thanks for the info Ruud. Was thinking about it last night and I guess there’s no harm — once I’ve added/deleted a category and rebuildTree() has done its stuff — in just seeing what the damage is and re-assigning the categories to my images. I think most of them are still there, it’s just the structure that’s messed up a bit.

As you say, I should think from that point on, everything will behave and the fact that other tables reference the name instead of the ID means I can delete the offending entries manually from the DB, add them again from the TXP interface, assign them their correct subcat location and the other tables should be none the wiser.

However, yes, it would be nice to know what is going on and perhaps why. So I’ve performed a series of tests and posted the results (a dump from MySQL) as follows. Between each test I restored the database to the state it was in, as shown in the ‘Before’ state:

Category list before and the corresponding jpg of the category screen
Category list after adding a new cat ‘International Anthem’ and the corresponding jpg of the category screen
Category list after deleting the category ‘dB’ and the corresponding jpg of the category screen
Category list after deleting the sub-category ‘Lotoya > Gallery’ and the corresponding jpg of the category screen
Category list after deleting the sub-category ‘Riotakt > Studio’ and the corresponding jpg of the category screen

If any of that helps, then cool. If you want anything else off me, just let me know what it is. Or if you want direct acess to the TXP install I can let you have a login too, just ask [it’s a live site though, so if you do poke around, just restore the database to the most recent one please! :-) ]

Thanks for any insight. Can’t think of anything I’ve done to cause this; I do everything through the TXP admin interface.

Last edited by Bloke (2007-12-22 19:42:26)

ruud · 2007-12-22 20:29:36

Which TXP version or SVN revision are you using?

Bloke · 2007-12-22 21:15:28

ruud wrote:

Which TXP version or SVN revision are you using?

Vanilla 4.0.5

Textpattern CMS

Textpattern CMS support forum

#1 2007-12-21 15:37:15

Corrupt category indices?

#2 2007-12-21 22:23:54

Re: Corrupt category indices?

#3 2007-12-22 19:39:53

Re: Corrupt category indices?

#4 2007-12-22 20:29:36

Re: Corrupt category indices?

#5 2007-12-22 21:15:28

Re: Corrupt category indices?

Board footer