WordPress MU

Hi,
I've just moved a database from one host to another. Both are using mysql5.

I'm getting errors when I create new blogs, and visually comparing table structures I note that the old database had its autoincrement flags turned on whereas the new one doesn't.

It's trying to insert values of '0' in autoincrement columns, these are being taken literally instead of being allocated the next number.

I was getting errors:
ALTER TABLE wp_blogs CHANGE blog_id blog_id BIGINT( 20 ) NOT NULL DEFAULT '0' AUTO_INCREMENT MySQL said: #1067 - Invalid default value for 'blog_id'

To test my theory I changed the structure:
ALTER TABLE wp_blogs CHANGE blog_id blog_id BIGINT( 20 ) NOT NULL DEFAULT NULL AUTO_INCREMENT

This worked, but it would be risky to do this manually with every table.

Is there a tool or mode that can check the structure of the database?

I've run the wp-admin/wpmu-upgrade-site.php tool already.

If not, is there a process I can use on the old install to export every blog user so that I can restore it through the interface rather than through mysql?

Advice much appreciated,
Martin.

I've just experienced the same thing. The auto_increment flags were ignored on the import of a database from the backup. I think the cause was the fact that I had to use the --skip-opts flag on import as the database is too big to import without that.

Anyway, I've been doing some research, and found some clues as to the correct table structure in wp-admin/includes/schema.php. This tells me which tables need the auto increment flag.

First of all there are all the main wp_ tables.

ALTER TABLE wp_blogs CHANGE blog_id blog_id BIGINT( 20 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_users CHANGE ID ID BIGINT( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_usermeta CHANGE umeta_id umeta_id BIGINT( 20 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_registration_log CHANGE ID ID BIGINT( 20 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_site CHANGE id id BIGINT( 20 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_sitecategories CHANGE cat_ID cat_ID BIGINT( 20 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_sitemeta CHANGE meta_id meta_id BIGINT( 20 ) NOT NULL AUTO_INCREMENT;

Then you have to loop through each blog changing the value of xx from 1 to however many blogs you have and doing the following.

ALTER TABLE wp_xx_postmeta CHANGE meta_id meta_id BIGINT( 20 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_xx_options CHANGE option_id option_id BIGINT( 20 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_xx_links CHANGE link_id link_id BIGINT( 20 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_xx_comments CHANGE comment_ID comment_ID BIGINT( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_xx_term_taxonomy CHANGE term_taxonomy_id term_taxonomy_id BIGINT( 20 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_xx_posts CHANGE ID ID BIGINT( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_xx_terms CHANGE term_id term_id BIGINT( 20 ) NOT NULL AUTO_INCREMENT;

That seems to work. If I was expecting to do this a lot of times, I'd probably write a small script to do this, but for now I just got a list of the tables using SHOW TABLES in mysql commandline client.

Hope this helps someone. It took me a while to figure out.

One further thing ... if these queries don't work, I see that posting them here has removed all the backticks around the tablenames. You may need to put them back in. But it should work without them.

Oh I remember having this happen to me when I was doing a migration from MT Grid Service to one of their dedicated virtuals because their migration software was having issues. Had to do the same thing, look at schema.php! Thanks for sharing.