Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Per 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;

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:

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

I get the same error as @mkormendy.

I get the same error as @mkormendy.

till 2 month the code was correct

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