-
-
Save aprea/484539cfea18e253bc2a to your computer and use it in GitHub Desktop.
-- 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). :)
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);
I get the same error as @mkormendy.
till 2 month the code was correct