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;

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