Modify Database in Multi-Site wp_usermeta table

Question

My name is Antonio and i'm from Ecuador, my english is not good but here we go.

I already have my Multi-site installation working, but the "wp_2_" site have some error in the functionality, because i change manually all the meta_key from wp_usermeta table to have "wp_" in all users meta_key.

Im working in a project that have 3 wordpress subdomain wordpress site 2 3 and 4 that should to work with all users in the same way.

The problem and the solution that i think to much and i'm 100% sure that is the solution is the database from wp_2_, wp_3_ and wp_4_ should work with the right wp_usermeta ->(meta_key)

The Meta Key:

this 2 are the problem and you can found it in wp_usermeta table

wp_capabilities wp_user_level

the solution that i think is simple but need a few modification is the database

First let me upload the image for a better explanation

Ok, in the above image you can see the inside of wp_usermeta table

the 2 importants data are located in meta_key column and are the last one of the image: wp_capabilities and wp_user_level.

That's what i want to do x = 2, 3, 4

I need to add a new Row for "umeta_id" with the same "user_id" and in meta_key the both that are necessary (wp_x_capabilities and wp_x_user_level) something like "wp_2_user_level" and "wp_2_capabilities" with his own meta_value field, and i like the same meta_value.

Now i show you an example that i want:

umeta_id ---- user_id ------ meta_key --------------- meta_value

1             1        wp_capabilities      a:1:{s:13:"administrator";b:1;}
2             1        wp_user_level               10
3             1        wp_2_capabilities    a:1:{s:13:"administrator";b:1;}
4             1        wp_2_user_level             10
5             1        wp_3_user_level             10
6             1        wp_4_user_level             10
7             1        wp_3_capabilities    a:1:{s:13:"administrator";b:1;}
8             1        wp_4_capabilities    a:1:{s:13:"administrator";b:1;}

I already have a lot users with wp_user_level and wp_capabilities in my db but the final point is to:

Create and add new meta_id, putting the same user_id change the variable key wp_2_, wp_3_, wp_4_ for the both meta_key and all they have to come with the same meta_value in relation with the ID

I have not much idea on how i can add these new meta_key by modify the database for do that.

and how i can do it with the new users? i think on cron_job?

PD: i use cpanel, mysql(phpmyadmin), and wordpress

anyone can help me? plz

Thanks a lot for read!

Answer 1

This Should Work?

ALTER TABLE wp_usermeta
ADD COLUMN old_user_id bigint(20) unsigned;

INSERT INTO wp_usermeta (old_user_id)
SELECT u.id
FROM wp_users AS u;

INSERT INTO wp_usermeta (user_id, meta_key, meta_value)
SELECT u.id, 'wp_2_capabilities', meta_value
JOIN wp_users AS u ON u.user_id = u.old_user_id;

ALTER TABLE wp_users
DROP COLUMN old_user_id;