Author Topic: How to assign contacts to a different user via SQL  (Read 4468 times)

Offline bhy

  • Newbie
  • *
  • Posts: 5
How to assign contacts to a different user via SQL
« on: June 16, 2018, 04:22:53 AM »
Hello,

we changed our server configuration so that users now log in as "user@domain.com" insted of only "user" as it was before.

Now the contacts are missing in mailboxes. When I look at the "roundcube" MySQL database, I can see tables "users" and "contacts" but I don't understand how are they linked to each other. In the "users" table I can only see users without reference to contacts and in the "contacts" table I can only see contacts without reference to users.

How do I reassign contacts from user "user" to user "user@domain.com"?

Thank you very much

bhy

Offline bhy

  • Newbie
  • *
  • Posts: 5
Re: How to assign contacts to a different user via SQL
« Reply #1 on: June 16, 2018, 06:00:56 AM »
Found it!

There is a "user_id" column in tables "contacts" and "contactgroups". I found the "user_id" for the new username "user@domain.com" in the table "users" and changed the "user_id" in the tables "contacts" and "contactgroups" to the new value.

Example:

UPDATE contacts SET user_id='14' WHERE user_id='3';
UPDATE contactgroups SET user_id='14' WHERE user_id='3';

in this case user_id='14' is the new user and user_id='3' the old one.

hope this helps someone.

bhy

Offline bhy

  • Newbie
  • *
  • Posts: 5
Re: How to assign contacts to a different user via SQL
« Reply #2 on: September 02, 2019, 08:49:12 AM »
Hello,

to elaborate on my soliloquy, what's interesting that I had to this again after upgrade of Roundcube from 1.1.7 to 1.3.10.

I was using the standard bin/update.sh script and it apparently changed user_id's in the database for some reason.

So this time, I had to run the following to determine the new user_id:

SELECT user_id FROM users WHERE username='info@bioma.cz';

and two different ID's were shown!

There must be something wrong that causes changes and/or duplications of user_id's in the update script.

Anyway, I chose the higher user_id from the two and ran:

UPDATE contacts SET user_id='22' WHERE user_id='14';
UPDATE contactgroups SET user_id='22' WHERE user_id='14';

and now my contacts are back again.

It would be very frustrating to this for multiple user accounts after each update.

bhy

Offline JohnDoh

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 2,845
Re: How to assign contacts to a different user via SQL
« Reply #3 on: September 03, 2019, 06:19:18 AM »
userid is a combination of the username and the mail_host. So if you change teh value of default_host in your rc config then you need to update the contents of the mail_host in the users table of the database to match the new one or it will create new entries.
Roundcube Plugins: Contextmenu, SpamAssassin Prefs, and more…

Offline bhy

  • Newbie
  • *
  • Posts: 5
Re: How to assign contacts to a different user via SQL
« Reply #4 on: October 17, 2019, 06:07:00 AM »
Quote
userid is a combination of the username and the mail_host. So if you change teh value of default_host in your rc config then you need to update the contents of the mail_host in the users table of the database to match the new one or it will create new entries.
I know, this was the first time. The user_id changed because the username changed. However, the second time the username didn't change and I merely upgraded roundcube using the standard bin/update.sh script and that's what changed the user_id, making my contacts disappear. I think it must be a bug.

Offline JohnDoh

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 2,845
Re: How to assign contacts to a different user via SQL
« Reply #5 on: October 18, 2019, 04:46:29 AM »
The users table of the Roundcube database has a unique key made up of the username and mail_host field. If you have 2 entries in your users table with the same username and mail_host then something has gone wrong in your database.
Roundcube Plugins: Contextmenu, SpamAssassin Prefs, and more…