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.

ms-extract-umeta-clean.sql

-- 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: https://gist.github.com/robrecord/e54e0ce141aaa9870a1e

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

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). :)

Hi,

Getting multiple errors. See attached.
Ideas?

Thanks

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

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