The MU forums have moved to

categories wp_1_terms.term_id auto-increment BROKEN after migration! (12 posts)

  1. rcain
    Posted 14 years ago #


    I have just migrated customer blog data from a (flat) WP v2.6.3 site to a WPMu v2.7 site (blog) and observed the following problems/symptoms:

    1) i migrated all WP data to the new (almost empty)WPMu environment using mysql phpmyadmin. this is a task i have performed successfully many times before, and although a painful exersize, has always worked ok. it seemed so this time, at first, EXCEPT:

    2) i logged into wpmu blog admin, posts categories - although all top level categories displayed ok, NONE of the child categories were displaying!!

    3) as a test, i added a new category 'testcat1' and a new subcategory testcat1.1 using the wpmu admin screens - worked ok, AND, suddenly ALL my migrated subcategories suddenly appear in wpmu categories admin screen!!

    4) i log in to mysql/phpmyadmin and look at the tables. I find that wp_1_terms.term_id for my new testcat1 category has been set to 2755 and testcat1.1 category has term_id set to 5291!! - it should have been 26 and 27 respectively (the next numbers in autoincrement sequence)!!

    5) wp_1_terms NextAutoIndex is now set to 5292!!

    6) wp_site_categories table has new entries for testcat1 and testcat1.1, in addition to the existing default install categories Uncategorised and Blogroll. NOTE, i did not add my imported categories to this table since the standard wp site database does not use such a table. i did however, import data into wp_1_terms, wp_1_term_taxonomy and wp_1_term_relationships.

    This appears to be a prety scary problem/undocumented feature of wpmu!

    it suggests that autoincrement is being overridden in wpmu core code somewhere and also suggests that it will possibly grow exponentially/randomly instead of incrementally under mysql control!

    I see several related/identical problems on this forum, going back a year or more, these are: - specifically the post by beaulebens on page 2 (and re5et on page 3) comes nearest to explaining it, suggesting that the cause of the problem is the fact that wp_sitecategories table is a legacy table and that wpmu is attempting (unsuccessfully) to maintain two sets of category index, one of which is redundant - something i know from personal coding experience is generally a BAD idea just waiting to blow up! - same problem from 2 years ago - same problem from 3 years ago, but this time experienced on wp (not wpmu)

    i cant find any refence to this precise problem being fixed or addressed by developers - forgive me if i have missed something/please could you point me there?

    can anyone shed some light on how to fix/avoid this AND how to ensure it doesnt blow up at some point in the future (/reassure me that it wont)?

    Thanks for any feedback.

  2. andrea_r
    Posted 14 years ago #

    Well, all I can say is to leave the cats like that and no, it won't blow up.

    Just a number really.

  3. rcain
    Posted 14 years ago #

    Hi andrea

    Thanks for your reply. I know you to be a regular and knowledgable contributor here (& am thankful for many of your previous posts :)), so I am 'somewhat' reassured when you assert 'it wont blow up'.

    however, i would still like to know by what method the wp_1_terms.term_id index is being maintained here.

    i did a couple more add-categories through admin and the indexes now take the sequence

    nextautoindex=10244 - though i expect the code may well override this (mysql value) again next time around.

    the differences between these indexes therefore currently look like:

    1...1 (for 1st 25 (imported) records, then)
    (for the ones added through admin)

    so, its not growing (actually) exponentially thank goodness, though neither does it seem to be converging in any linear way, just jumping in blocks of APPROXIMATELY 1500 (potentially reducing the maximum number of categories WPMu can support by that same factor - reducing from 1E20 down to around 1E6).

    its the 'APPROXIMATELY' bit i really dont like the look of.

    i note also the wpmu plugin sync-taxonomy:

    which seems to be addressing some very similar (upgrade/migration) issue back in 10/04/07. the code references the (legacy?) $global_id variable which would seem to have something to do with it.

    as for 'just a number' - well i know what you mean, EXCEPT that the database is definitely configured to autoincrement this value in steps of 1. therefore anything that overrides this is definately suspicious (else it shouldnt be defined as autoincrement). that, plus the existance of the wp_sitecategories table makes at least TWO POSSIBLE AMBIGUOUS paths through the code.

    my strategy at present is therefore as follows:
    a) (as you say) leave it alone and just accept the situation of uncertainty
    b) be prepared to trace the core WPMu core code in detail
    c) be prepared to write my own (post-)category recovery/rebuild script should the worst eventualy happen.

    any other clues or explanations lurking out there would still be really welcome at this stage.

    thanks again
    very best regards

  4. revg
    Posted 14 years ago #


    I'm also having this exact issue after taking a standard WP 2.6.3 blog and transitioning it to WPMU 2.7. I've started tracing the code, but if you've come up with anything significant it'd be great to hear about it.



  5. elebail
    Posted 13 years ago #


    I have the same problem with WPMU 2.8.6

    I tracked back the error to the function:

    File : wp-include/taxonomy.php
    function : wp_insert_term

    the code is:
    $maxterm = $wpdb->get_var( "SELECT max(term_id) FROM {$wpdb->terms}" );
    $term_id = mt_rand( $maxterm+100, $maxterm+4000 );

    I don't undestand why this code is done.

    I would do something like
    $term_id = $maxterm +1;


  6. elebail
    Posted 13 years ago #


    I found What's going on:

    See topic for details.

    The solution is to add a step into the migration:

    Something like:
    in sql, my old base is "wordpress", the new one is "wordpressmu"
    #Delete from wordpressmu.wp_sitecategories;
    #insert into wordpressmu.wp_sitecategories
    # term_id,
    # name,
    # slug,
    # now()
    #from wordpress.wp_terms;

    I whish this can help you.

    Eric Le Bail

  7. dailynewszw
    Posted 13 years ago #

    Could you please elaborate on this response for a not so savvy php user. I am having a slightly different problem where, after migrating data from wordpress to wordpressmu, each time I try to add or modify a category on the WPMU admin dashboard, the category is created with a differen name than what I would have given it. The names seem to be picked out from the wp_X_terms table. I have to go into that table in myphpadmin, and change the name to the correct name I intend to use, in order for it to stick on the WPMU dashboard. Attempting to correct the category name on the dashboard only leads to the next term in the wp_x_terms table being picked out instead, to replace whatever was there before, and what I type in is not taken. Please provide response and help in as layman terms as possible, so I know how to implement solution. Thanks,

  8. elebail
    Posted 13 years ago #


    What I say is that during your migration,
    you need to copy the data from the old table wp_terms into the new table wp_X_terms but also into the table wp_sitecategories (that is a table specific to WPMU).

    This copy is done with the folowing fields:
    - table "wp_terms" field "term_id" goes to table "wp_sitecategories" field "cat_ID"
    - table "wp_terms" field "name" goes to table "wp_sitecategories" field "cat_name"
    - table "wp_terms" field "slug" goes to table "wp_sitecategories" field "category_nicename"
    - and you put the current date ("now()") into table "wp_sitecategories" field "last_updated".

    I whish this new description is easier to understand.

    Eric Le Bail

  9. dailynewszw
    Posted 13 years ago #

    Thanks, this is starting to make sense. However, just to be completely clear, so I get my wp_posts, comments, post meta, comments meta, taxonomy, terms tables exported from my regular wordpress as sql. Then from what I am understanding, I need to write into the sql, the script you gave above, is that correct? Could you email me an example sql file perhaps, also, showing how to do this??

  10. gilek7
    Posted 13 years ago #

    Thank you so much, merci beaucoup, I have the same problem, this is of great help. It would be great if someone close to would indicate how to resolve this cleanly, or how to migrate from WP to WPMU without running into this problem.

    Can I also use the above solution after I've already migrated and used the site?

    BTW, why does the search _MU_ forums link redirect to ??

  11. gilek7
    Posted 13 years ago #

    I forgot to say that exporting and re-importing is not so good for me because I have 500 categories and 10000 posts.

  12. miklb
    Posted 13 years ago #

    what if you failed to bring those individual sites over into the sitecatgories table?

About this Topic