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.

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.

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.

oscarfroberg commented Oct 28, 2015

I get the same error as @mkormendy.

@NicolasGlatz

This comment has been minimized.

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.

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.

theret commented Jul 30, 2018

Hi,

Getting multiple errors. See attached.
Ideas?

Thanks

screen shot 2018-07-30 at 13 09 03

@nicolasflorth

This comment has been minimized.

nicolasflorth commented Nov 24, 2018

using ON DUPLICATE KEY UPDATE in capital fixed the same errors I had

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);

Hi,

Getting multiple errors. See attached.
Ideas?

Thanks

screen shot 2018-07-30 at 13 09 03

using ON DUPLICATE KEY UPDATE in capital fixed the same errors I had

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);

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