This saved my bacon last week when my Plex Home went on a permanent walk-about. Adding some of the commands I used in case they might help others in the same boat.
1. Get a valid Plex authentication token
curl -s -H 'x-plex-client-identifier: curl' --data-urlencode 'user[login]={YOUR-PLEX-EMAIL}' --data-urlencode 'user[password]={YOUR-PLEX-PASSWORD}' https://my.plexapp.com/users/sign_in.xml
Your {PLEX_TOKEN} is the value of the authentication-token element.
2. Get list of current managed home user account id values
curl -s -H 'x-plex-client-identifier: curl' https://plex.tv/api/home/users?X-Plex-Token={PLEX_TOKEN}`
Within each User element record the value of id and title as these are the user account id and user name respectively.
3. Get list of old user account values
Within the com.plexapp.plugins.library.db database you want the id and name values from the accounts table.
4. Associate new user account id’s with previous user play history and on-deck data
In the SQL below adjust the “INSERT INTO ua (new,old) VALUES” statement to match your environment. The value new is the id value recorded in step 2 while the old value would be the corresponding id value you recorded from step 3. Delete or add to match however many user accounts you need to recover.
Then execute it against the com.plexapp.plugins.library.db database.
CREATE TEMP TABLE ua (new INTEGER, old INTEGER);
INSERT INTO ua (new, old) VALUES
(2######5, 6#####8), --User1
(2######5, 6#####5), --User2
(2######0, 6#####3), --User3
(2######3, 6#####1), --User4
(2######7, 6#####4), --User5
(2######3, 2######8), --User6
(2######0, 2######5); --User7
SELECT * FROM ua;
UPDATE view_settings SET account_id = (SELECT ua.new from ua WHERE view_settings.account_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE view_settings.account_id = ua.old);
UPDATE statistics_media SET account_id = (SELECT ua.new from ua WHERE statistics_media.account_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE statistics_media.account_id = ua.old);
UPDATE statistics_bandwidth SET account_id = (SELECT ua.new from ua WHERE statistics_bandwidth.account_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE statistics_bandwidth.account_id = ua.old);
UPDATE plugin_permissions SET account_id = (SELECT ua.new from ua WHERE plugin_permissions.account_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE plugin_permissions.account_id = ua.old);
UPDATE play_queues SET account_id = (SELECT ua.new from ua WHERE play_queues.account_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE play_queues.account_id = ua.old);
UPDATE metadata_item_views SET account_id = (SELECT ua.new from ua WHERE metadata_item_views.account_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE metadata_item_views.account_id = ua.old);
UPDATE metadata_item_settings SET account_id = (SELECT ua.new from ua WHERE metadata_item_settings.account_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE metadata_item_settings.account_id = ua.old);
UPDATE metadata_item_accounts SET account_id = (SELECT ua.new from ua WHERE metadata_item_accounts.account_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE metadata_item_accounts.account_id = ua.old);
UPDATE media_stream_settings SET account_id = (SELECT ua.new from ua WHERE media_stream_settings.account_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE media_stream_settings.account_id = ua.old);
UPDATE media_part_settings SET account_id = (SELECT ua.new from ua WHERE media_part_settings.account_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE media_part_settings.account_id = ua.old);
UPDATE media_item_settings SET account_id = (SELECT ua.new from ua WHERE media_item_settings.account_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE media_item_settings.account_id = ua.old);
UPDATE library_section_permissions SET account_id = (SELECT ua.new from ua WHERE library_section_permissions.account_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE library_section_permissions.account_id = ua.old);
UPDATE accounts SET id = (SELECT ua.new from ua WHERE accounts.id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE accounts.id = ua.old);
5. Associate Tautulli session play history to new user accounts
In the SQL below adjust the “INSERT INTO ua (new,old) VALUES” statement to match your environment. The value new is the id value recorded in step 2 while the old value would be the corresponding id value you recorded from step 3. Delete or add to match however many user accounts you need to recover.
Then execute it against the tautulli.db database.
CREATE TEMP TABLE ua (new INTEGER, old INTEGER);
INSERT INTO ua (new, old) VALUES
(2######5, 6#####8), --User1
(2######5, 6#####5), --User2
(2######0, 6#####3), --User3
(2######3, 6#####1), --User4
(2######7, 6#####4), --User5
(2######3, 2######8), --User6
(2######0, 2######5); --User7
SELECT * FROM ua;
UPDATE notify_log SET user_id = (SELECT ua.new from ua WHERE notify_log.user_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE notify_log.user_id = ua.old);
UPDATE session_history SET user_id = (SELECT ua.new from ua WHERE session_history.user_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE session_history.user_id = ua.old);
UPDATE sessions SET user_id = (SELECT ua.new from ua WHERE sessions.user_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE sessions.user_id = ua.old);
UPDATE user_login SET user_id = (SELECT ua.new from ua WHERE user_login.user_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE user_login.user_id = ua.old);
UPDATE users SET user_id = (SELECT ua.new from ua WHERE users.user_id = ua.old) WHERE EXISTS (SELECT * FROM ua WHERE users.user_id = ua.old);
