Come together with the global Drupal community in Rotterdam, 28 Sept – 1 Oct 2026. Sessions, contribution, connection, and Early Bird savings until 8 June.Hello, I tried to set up a multisite login with shared users.
Login does work, but creating users on different sites of the same same network does create errors:
user warning: Duplicate entry '2' for key 1 query: INSERT INTO myname_user.users (name, mail, pass, status, init, uid, created, access) VALUES ('Dan', 'dan@zooot.net', 'XXXXXXXXXXXXXXXXXXXX', 1, 'dan@zooot.net', 2, 1234439173, 1234439173) in /home/myname/public_html/includes/database.mysql.inc on line 174.
I have the following setup:
- sites/all (contains all the modules)
- sites/default (contains the master site "zooot.net")
- sites/zooot.at (files, settings.php, tmp, themes)
- sites/zooot.ch (files, settings.php, tmp, themes)
- sites/zooot.co.uk (files, settings.php, tmp, themes)
- sites/zooot.eu (files, settings.php, tmp, themes)
- sites/zooot.us (files, settings.php, tmp, themes)
The sites all share the same modules. But each site uses its own SQL data base:
- myname_zoootnet
- myname_zoootat
- myname_zoootch
- myname_zoootcouk
- myname_zoootus
- myname_zoooteu
For the user data I use this data base:
- myname_users
The settings.php contains the following lines:
$db_url = 'mysql://myname_paul:XXXXXXXXXXXXXXXXXXXX@localhost/myname_zoootnet';
$db_prefix = array(
'default' => '',
'authmap' => 'myname_user.',
'role' => 'myname_user.',
'sessions' => 'myname_user.',
'users' => 'myname_user.',
'users_roles' => 'myname_user.',
I am also using the advanced profile kit. But at the time I am not sharing it because I don't know how and I have not set all fields yet.
Now where did I go wrong? Why do I get the "Duplicate entry errors?
Do I have to delete the fields that I share in the users db from the other dbs?
Questions upon questions.
Paul.
Comments
Comment #1
dalinI'm pretty sure that this has nothing to do with the Multisite Login module. I suggest disabling Multisite Login and Advanced Profile and then try to create a new user and see what happens.
I'm a bit unclear as to when you are getting this error. I assume the error happens when you create a completely new user that doesn't exist on any of the sites. If that is the case then the problem _could_ be that you have somehow got a corrupted user table. You _may_ need to delete the DB and start from scratch.
If on the other hand you create a user successfully on one site, then attempt to create the same user on another site, then the problem would be with one or more of your settings.php files. Since the user table is shared between all sites, then once a user is created on one site, the user would then be able to simply login to any of the sites. If you attempted to create the same user on another site Drupal should give you a nice warning that the user already exists, not a MySQL error.
Comment #2
paul_constantine commentedThe error appears right after I hit the "submit button" to enter a new user. I tried it on several of the sites.
Sometimes it work, sometimes it does not. Sometimes If I enter the same user again after I get that error it works and I find that user on the shared user db.
Would it be wise to delete the tables on my site data bases that I have on the shared data base?
By the way, the multisite login does not work after all. Now I am not so sure I got the user sharing right. Or could this be a conflict with the fields of the advanced user profiles? I noticed that on my master site all the failed attempts to create users resultet in filled out namefields (sometimes in triplicate) for the users I had to enter more that one time.
Paul.
Comment #3
dalinAs I already mentioned. Try disabling both Advanced Profile and Multisite Login for all of your sites and see if you still have the error.
Comment #4
paul_constantine commentedFound the solution.
I did not share the "sequences" table. That way the six websites were not synchronized when assigning UIDs for new users. Each was using its own version of the sequences table and therefore assigning UIDs that in some cases were already taken.
By sharing sequences all the websites are now using the same table to assign UIDs to users.
Thanks for your time.
Paul.
Comment #5
dalin