Instantly share code, notes, and snippets.

Embed
What would you like to do?
Per https://deliciousbrains.com/wp-migrate-db-pro/doc/extracting-subsite-multisite-create-new-single-site-install/ clean up wp_usermeta after a simplistic export of a sub-site from multisite.
-- delete any usermeta specific to the other subsites
delete from wp_usermeta where meta_key regexp '^wp_([0-9]+)_';
-- duplicate the wp_usermeta structure in a working data table,
-- but add a unique index for filtering out duplicates
create table _fix_usermeta like wp_usermeta;
alter table _fix_usermeta add unique(user_id, meta_key);
-- copy the site-specific usermeta, keeping only the last of each duplicate
insert into _fix_usermeta
select * from wp_usermeta
where meta_key like 'wp\_%'
order by user_id, meta_key, umeta_id
on duplicate key update umeta_id=values(umeta_id), meta_value=values(meta_value);
-- remove the first of each duplicate
delete from wp_usermeta
where meta_key like 'wp\_%'
and not exists (select * from _fix_usermeta where umeta_id = wp_usermeta.umeta_id);
-- remove that working data table
drop table _fix_usermeta;
@robrecord

This comment has been minimized.

Show comment
Hide comment
@robrecord

robrecord Sep 25, 2015

I get Index column size too large. The maximum column size is 767 bytes. on query 3 with InnoDB (and a similar error with MyISAM).

Fix for InnoDB here: https://gist.github.com/robrecord/e54e0ce141aaa9870a1e

robrecord commented Sep 25, 2015

I get Index column size too large. The maximum column size is 767 bytes. on query 3 with InnoDB (and a similar error with MyISAM).

Fix for InnoDB here: https://gist.github.com/robrecord/e54e0ce141aaa9870a1e

@mkormendy

This comment has been minimized.

Show comment
Hide comment
@mkormendy

mkormendy Oct 18, 2015

I get Specified key was too long; max key length is 1000 bytes on query 3. What's the fix for that?

mkormendy commented Oct 18, 2015

I get Specified key was too long; max key length is 1000 bytes on query 3. What's the fix for that?

@oscarfroberg

This comment has been minimized.

Show comment
Hide comment
@oscarfroberg

oscarfroberg Oct 28, 2015

I get the same error as @mkormendy.

oscarfroberg commented Oct 28, 2015

I get the same error as @mkormendy.

@NicolasGlatz

This comment has been minimized.

Show comment
Hide comment
@NicolasGlatz

NicolasGlatz Dec 9, 2015

I get the same error as @mkormendy.

till 2 month the code was correct

NicolasGlatz commented Dec 9, 2015

I get the same error as @mkormendy.

till 2 month the code was correct

@RichwebInc

This comment has been minimized.

Show comment
Hide comment
@RichwebInc

RichwebInc Feb 28, 2018

For anyone that's getting the byte size restrictions, (767 bytes or 1000 bytes), start the entire process over (drop your temp table first). Run this, instead (fully tested and working):

-- delete any usermeta specific to the other subsites
delete from wp_usermeta where meta_key regexp '^wp_([0-9]+)_';

-- duplicate the wp_usermeta structure in a working data table,
-- but add a unique index for filtering out duplicates
create table _fix_usermeta like wp_usermeta;
ALTER TABLE _fix_usermeta CHANGE user_id user_id INT(20) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE _fix_usermeta CHANGE meta_key meta_key VARCHAR(191) NULL DEFAULT NULL;
alter table _fix_usermeta add unique(user_id, meta_key);

-- copy the site-specific usermeta, keeping only the last of each duplicate
insert into _fix_usermeta
select * from wp_usermeta
where meta_key like 'wp_%'
order by user_id, meta_key, umeta_id
on duplicate key update umeta_id=values(umeta_id), meta_value=values(meta_value);

-- remove the first of each duplicate
delete from wp_usermeta
where meta_key like 'wp_%'
and not exists (select * from _fix_usermeta where umeta_id = wp_usermeta.umeta_id);

-- remove that working data table
drop table _fix_usermeta;

Tagging @robrecord @mkormendy @oscarfroberg @NicolasGlatz for the fixes (even though it's almost three years later). :)

RichwebInc commented Feb 28, 2018

For anyone that's getting the byte size restrictions, (767 bytes or 1000 bytes), start the entire process over (drop your temp table first). Run this, instead (fully tested and working):

-- delete any usermeta specific to the other subsites
delete from wp_usermeta where meta_key regexp '^wp_([0-9]+)_';

-- duplicate the wp_usermeta structure in a working data table,
-- but add a unique index for filtering out duplicates
create table _fix_usermeta like wp_usermeta;
ALTER TABLE _fix_usermeta CHANGE user_id user_id INT(20) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE _fix_usermeta CHANGE meta_key meta_key VARCHAR(191) NULL DEFAULT NULL;
alter table _fix_usermeta add unique(user_id, meta_key);

-- copy the site-specific usermeta, keeping only the last of each duplicate
insert into _fix_usermeta
select * from wp_usermeta
where meta_key like 'wp_%'
order by user_id, meta_key, umeta_id
on duplicate key update umeta_id=values(umeta_id), meta_value=values(meta_value);

-- remove the first of each duplicate
delete from wp_usermeta
where meta_key like 'wp_%'
and not exists (select * from _fix_usermeta where umeta_id = wp_usermeta.umeta_id);

-- remove that working data table
drop table _fix_usermeta;

Tagging @robrecord @mkormendy @oscarfroberg @NicolasGlatz for the fixes (even though it's almost three years later). :)

@theret

This comment has been minimized.

Show comment
Hide comment
@theret

theret Jul 30, 2018

Hi,

Getting multiple errors. See attached.
Ideas?

Thanks

screen shot 2018-07-30 at 13 09 03

theret commented Jul 30, 2018

Hi,

Getting multiple errors. See attached.
Ideas?

Thanks

screen shot 2018-07-30 at 13 09 03

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment