MySQL Duplicate entry when syncing user about redmine_ldap_sync HOT 16 CLOSED

We can try changing line "https://github.com/thorin/redmine_ldap_sync/blob/redmine-1.4.x/lib/redmine_ldap_sync/redmine_ext/auth_source_ldap_patch.rb#L16".

Later, I'll create a fork with the change for you to try.

from redmine_ldap_sync.

I'd be very glad.
Thank you in advance!

from redmine_ldap_sync.

Can you try branch redmine-1.4.x-55?

If it fixes your problem I'll then merge it to the redmine-1.4.x branch.

from redmine_ldap_sync.

Notice that I've done two commits:

• f520515
• 65e3ca5

from redmine_ldap_sync.

I tried branch .1.4.x-55 and the error is still the same.
I also noticed, that users who had signed off, can't login anymore due to same error. They get internal server error 500.

This is error in production log:

Processing AccountController#login (for 172.18.20.170 at 2013-01-10 08:23:46) [GET]
Parameters: {"action"=>"login", "back_url"=>"https://xxxxxxxxxi/redmine/projects/hd-01/issues/new", "controller"=>"account"}
Rendering template within layouts/base
Rendering account/login
Completed in 0ms (View: 0, DB: 0) | 200 OK [https://xxxxxxxx/redmine/login?back_url=https%3A%2F%2Fxxxxxxxxxx%2Fredmine%2Fprojects%2Fhd-01%2Fissues%2Fnew]

Processing AccountController#login (for 172.18.20.170 at 2013-01-10 08:23:50) [POST]
Parameters: {"action"=>"login", "username"=>"klancarj", "password"=>"[FILTERED]", "authenticity_token"=>"yWPJC6iguyRcInmyyQejKdcet2sXh0+bzGokxkUuf8w=", "login"=>"Prijavi se »", "back_url"=>"https%3A%2F%2Fhxxxxxxxx%2Fredmine%2Fprojects%2Fhd-01%2Fissues%2Fnew", "controller"=>"account"}
Could not create group 'Informatika-Oddelek_Podpore-Certifikati_RW': "Ime je predolg"
Could not create group 'Informatika-Oddelek_Podpore-Osnovna_Sredstva_RW': "Ime je predolg"
Could not create group 'Informatika-Oddelek_Podpore-Osnovna_Sredstva_RO': "Ime je predolg"
Could not create group 'Informatika-Oddelek_Podpore-Syslog_RO': "Ime je predolg"
Could not create group 'Sophos PureMessage Administrators': "Ime je predolg"
Could not create group 'Informatika-Oddelek_Podpore-Ghost_PC_RW': "Ime je predolg"
Could not create group 'Informatika-Oddelek_Podpore-Proxy_Backup_RW': "Ime je predolg"
Could not create group 'GPO_Windows 7 GPO_Informatika_Extra': "Ime je predolg"
Could not create group 'Software Deploy - Dodaj SHARP Tiskalnik Novo mesto': "Ime je predolg"
Could not create group 'SP_Oddelki_Skupno_ITObrazci_DodeljevanjePravicIT': "Ime je predolg"
Could not create group 'Projekti_DostopnePravice_DostopnePravice_RW': "Ime je predolg"
Could not create group 'Informatika-Oddelek_Podpore-ESA_Backup_RW': "Ime je predolg"
Could not create group 'Oskrba_Narocilnice_NarocilaIT_RW': "Ime je predolg"

ActiveRecord::StatementInvalid (Mysql::Error: Duplicate entry '6-4' for key 1: INSERT INTO groups_users (user_id, group_id) VALUES (4, 6)):
app/controllers/account_controller.rb:143:in password_authentication' app/controllers/account_controller.rb:138:inauthenticate_user'
app/controllers/account_controller.rb:30:in login' thin (1.3.1) lib/thin/connection.rb:80:inpre_process'
thin (1.3.1) lib/thin/connection.rb:78:in catch' thin (1.3.1) lib/thin/connection.rb:78:inpre_process'
thin (1.3.1) lib/thin/connection.rb:53:in process' thin (1.3.1) lib/thin/connection.rb:38:inreceive_data'
C:/Program Files/BitNami Redmine Stack/ruby/lib/ruby/gems/1.8/gems/eventmachine-1.0.0.rc.4-x86-mingw32/lib/eventmachine.rb:187:in run_machine' C:/Program Files/BitNami Redmine Stack/ruby/lib/ruby/gems/1.8/gems/eventmachine-1.0.0.rc.4-x86-mingw32/lib/eventmachine.rb:187:inrun'
thin (1.3.1) lib/thin/backends/base.rb:61:in start' thin (1.3.1) lib/thin/server.rb:159:instart'
thin (1.3.1) lib/thin/controllers/controller.rb:86:in start' thin (1.3.1) lib/thin/runner.rb:185:insend'
thin (1.3.1) lib/thin/runner.rb:185:in run_command' thin (1.3.1) lib/thin/runner.rb:151:inrun!'
thin (1.3.1) bin/thin:6
bin/thin:16:in `load'
bin/thin:16

Rendering C:/Program Files/BitNami Redmine Stack/apps/redmine/htdocs/public/500.html (500 Internal Server Error)

from redmine_ldap_sync.

Have you recently updated redmine or rails?

from redmine_ldap_sync.

Nope, nothing.
What will happen if I just uninstall plugin? Will uninstallation delete table groups_users? Is it possible, that there is something wrong with this table?

from redmine_ldap_sync.

it's safe to just delete/move the plugin folder out of the redmine directory.

I'm also considering that hypothesis. Maybe loss of an unique key.
The thing is that somehow, rails is not detecting that the user already belongs to one group.
And is trying to add it again.

You can search the "users" database for duplicate ids.

select count(id)
from users
where type = 'Group'
group by id
order by count(id) desc;

It's supposed to exist an unique key on (id, type) preventing this to happen.

from redmine_ldap_sync.

Tried your query and it shows for all 773 users, count 1. Which I suppose is correct. Each account has one uniqe id.

I moved folder with plugin & restarted application. Now users can login. Your plugin is not in plugin list anymore.

If there is something wrong with table groups_users, can you give me advice how to recreate it?

from redmine_ldap_sync.

I believe there is no problem with that table.

Redmine stores both the groups and users on the users table.
That table associates users with groups.

Can you try to create a new group manually to see if it gives you any error?
It might be trying to create a new group with an existing id and then trying to associate it to an user.

This might happen if somehow rails is failing to get a valid new id.

If it doesn't give you an error, can you then check on the database if it created the group with a duplicate id?

from redmine_ldap_sync.

Thank you for explaining.
I created a new internal group, succsesfully, the new group ID is unique.

BTW, I also noticed that there were 8 groups with name $DUPLICATE-14xx where xx is different number. I deleted them, but it didn't wipe my problem ;-)

from redmine_ldap_sync.

Sorry for the long delay on my answer.

=) If those groups exist on the AD they will be created again.
I don't think they were generated by redmine.

So far we know that ldap_sync is not correctly detecting to wich groups the user already belongs to.
We have to thin this down.

• Are you running other plugins besides ldap_sync?
  There might be other plugins trying to add the user to groups and conflicting with ldap_sync.
  If you give me a list of the plugins you have I can have a look at them.
  Or you can try disabling all the plugins but ldap_sync and see if the rake task works properly.
• I wish the stack trace would be more detailed.
  Could you run the following, with the plugin enabled:

rake redmine:plugins:redmine_ldap_sync:sync_users --trace RAILS_ENV=production

and send me at least the first 10 lines of the error message?

PS: I hope we haven't hit on a activerecord-mysql-adapter bug.

from redmine_ldap_sync.

I've managed to replicate the error. But it happened to me right after creating a new user.

Is it the same situation for you?

from redmine_ldap_sync.

I get an error when syncing users from AD. I have cca. 95% AD users and 5% Redmine internal users. When creating internal user, everything is OK.
I have two connections to two different AD-s.

In first I have approx. 450 users (from both domains) and 1400 groups. Maybe there is problem in group number.
Second AD is smaller.

My original setting for Groups base DN was DC=domain,DC=local (so all groups in AD were included).
One of users (first to sync) is member of 51 AD groups. And syncing stuck with this user.

So I narrowed down Groups base DN with OU=REDMINE skupine,OU=blablabla,OU=groups,DC=domain,DC=local
and voila, now everything is ok. Now I get no more errors.

So, I guess I'll do a little bit of reorganizing of my AD groups to sync just groups, which are important for redmine usage.

Thorin, you've been very helpful and I really appretiate your help.

from redmine_ldap_sync.

Ok, I've just tried doing one last change.
To prevent importing groups with duplicate names I'm now using sets.

I've tried adding the same group twice and it does replicates your error.

 rails console
 > g = Group.find_by_lastname('audit.team')
 > User.find_by_login('admin').groups << [g, g]
ActiveRecord::RecordNotUnique: PG::Error: ERROR:  duplicate key value violates unique constraint "groups_users_ids"
DETAIL:  Key (group_id, user_id)=(92, 1) already exists.
: INSERT INTO "groups_users" ("user_id", "group_id") VALUES (1, 92)
        from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.10/lib/active_record/connection_adapters/postgresql_adapter.rb:1153:in `async_exec'

I've commited the change to branch redmine-1.4.x-55 and later I'll merge it to branch 1.4 and master.

I believe that you might have in your AD groups with replicated names.

from redmine_ldap_sync.

That is correct due to two LDAP connections. As mentioned before, narrowing goups base solved my problem, and I also deleted all unnecesary groups from redmine.

Thanks again.

from redmine_ldap_sync.