UserGroup user causes Unique constraint violation

[GoogleCodeExporter]

An error occurs during installation: After entering the admin data and clicking 
upon the Install button, the message attached appears.

FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint 
»usergroups_group_pkey« 
-> translation: Error: duplicate key value violates unique constraint 
»usergroups_group_pkey«

I took a look into the source and found out, that the "user" usergroup is the 
problem. When I comment line 289, it works.

Original issue reported on code.google.com by matthias...@gmail.com on 18 Apr 2011 at 9:10

Attachments:

• install.htm

[GoogleCodeExporter]

I forgot to mention: I'm using postgres. It seems that the ID is not 
incremented.

Original comment by matthias...@gmail.com on 18 Apr 2011 at 9:18

[GoogleCodeExporter]

I don't know if this is a consecutive error:

When I login, I click upon "Root tools" / "User List" but nothing happens. When 
I open the links seperately in a tab, I get the attached error.

CDbCommand failed to execute the SQL statement: SQLSTATE[42P01]: Undefined 
table: 7 FEHLER: fehlender Eintrag in FROM-Klausel für Tabelle 
»relusergroupsgroup«

-> translation: Error: missing entry in FROM block for table relusergroupsgroup

Well, I don't have any table called "relusergroupsgroup" in my database and I 
could not find any piece of code, that creates this table.

Original comment by matthias...@gmail.com on 18 Apr 2011 at 9:39

Attachments:

• index.htm

[GoogleCodeExporter]

still have to truly test this bug under postgres.

does the first error occur every time you try installation? even if you destroy 
the created tables before running the installation again?

Original comment by nic...@creationgears.com on 18 Apr 2011 at 9:54

• Changed state: Accepted

[GoogleCodeExporter]

Yes, the first bug does occur always during installation, but as far as I can 
see, it's just a minor bug and a I found the simple solution:

just put this in line 288 in the InstallationController.php
$group_model->id = UserGroupsUser::ROOT + 1;

It does not seem that the other bug is related to that but it has to the use of 
postgres ;). So installation works now fine.
but the tabs Root tools" / "User List" still don't work, but this can be caused 
by the use of yii 1.1.6 (damn, I'm stupid! I didn't see your information that 
one should use 1.1.7!). I will try this first...

Original comment by matthias...@gmail.com on 19 Apr 2011 at 6:47

[GoogleCodeExporter]

Mh... it does not work also for 1.1.7, the same error occurs, but the error 
output is a bit different.

Thank you very much btw for your fast response!

Original comment by matthias...@gmail.com on 19 Apr 2011 at 7:55

Attachments:

• admin.htm

[GoogleCodeExporter]

are you still using tablePrefix?

because i think that's what may be causing the error... the query that is 
trying to execute is looking for default name tables without prefix

Original comment by nic...@creationgears.com on 19 Apr 2011 at 8:04

• Changed state: Started

[GoogleCodeExporter]

I am not using any table prefix.

But I think I got it:
The last "relUserGroupsGroup" was without quotation!

SELECT COUNT(DISTINCT "t"."id")
FROM "usergroups_user" "t"
LEFT OUTER JOIN "usergroups_group" "relUserGroupsGroup"
    ON ("t"."group_id"="relUserGroupsGroup"."id") WHERE ("relUserGroupsGroup".level <=:ycp1)

instead of 

(relUserGroupsGroup.level <=:ycp1)

:D

Original comment by matthias...@gmail.com on 19 Apr 2011 at 8:28

[GoogleCodeExporter]

Mh... is this possibly a yii bug? The stacktrace looks like it... Absolutely no 
clue where to start looking for this.

Original comment by matthias...@gmail.com on 19 Apr 2011 at 8:37

[GoogleCodeExporter]

i was thinking about the same... after all yii is generating the query and i 
don't see a way to force the quotes unless writing the sql statement hardcoded

Original comment by nic...@creationgears.com on 19 Apr 2011 at 8:41

[GoogleCodeExporter]

Ok, so I will report this to yii.
Thanks for your help!

Original comment by matthias...@gmail.com on 19 Apr 2011 at 8:45

[GoogleCodeExporter]

thank you for the feedbacks and bug reports!

Original comment by nic...@creationgears.com on 19 Apr 2011 at 8:48

• Changed state: WontFix

[GoogleCodeExporter]

Mh... it seems they are of a different opinion:
http://code.google.com/p/yii/issues/detail?id=2347

Any clue where you put the WHERE clause?

Original comment by matthias...@gmail.com on 19 Apr 2011 at 11:55

[GoogleCodeExporter]

i'm doing some checks around to see how to solve the problem ^^

but i think i spotted it

Original comment by nic...@creationgears.com on 19 Apr 2011 at 12:07

[GoogleCodeExporter]

ok, we have a problem here... if i add those quotes on mysql i get this error XD

CDbCommand ha riportato un errore nell'esecuzione della query SQL: 
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in 
your SQL syntax; check the manual that corresponds to your MySQL server version 
for the right syntax to use near '.level <='99')' at line 1. The SQL statement 
executed was: SELECT COUNT(DISTINCT `t`.`id`) FROM `usergroups_user` `t` LEFT 
OUTER JOIN `usergroups_group` `relUserGroupsGroup` ON 
(`t`.`group_id`=`relUserGroupsGroup`.`id`) WHERE ("relUserGroupsGroup".level 
<=:ycp7) 

so it seems like that the error is either on one or the other rdbms...

this is the line in which i define the where condition btw:

$criteria->compare('relUserGroupsGroup.level <',Yii::app()->user->level 
-1,false);

this is the query generated by the system naturally (and that doesn't give me 
any error on mysql.

Querying SQL: SELECT COUNT(DISTINCT `t`.`id`) FROM `usergroups_user` `t` LEFT 
OUTER JOIN `usergroups_group` `relUserGroupsGroup` ON 
(`t`.`group_id`=`relUserGroupsGroup`.`id`) WHERE (relUserGroupsGroup.level 
<=:ycp7)

if you don't mind helping me out, try changing the where condition line i 
showed you, is located inside the UserGroupsUser model in the search method

Original comment by nic...@creationgears.com on 19 Apr 2011 at 12:19

• Changed state: Started

[GoogleCodeExporter]

Awesome! It works now! All the greatness of your extension finally uncovered! :D

Well, it is stupid, that postgres does not go along with case sensitive names 
when unquoted.^^

#337: $criteria->order='"relUserGroupsGroup".level DESC, 
"relUserGroupsGroup".groupname';
#343: 
$criteria->compare('"relUserGroupsGroup".groupname',$this->group_name,true);
#344: $criteria->compare('"relUserGroupsGroup".level <',Yii::app()->user->level 
-1,false);

Thanks for your help!

Original comment by matthias...@gmail.com on 19 Apr 2011 at 12:39

[GoogleCodeExporter]

thank you!

now i'll have to understand what's the clean way to make it work on both mysql 
and postgres XD

Original comment by nic...@creationgears.com on 19 Apr 2011 at 12:49

[GoogleCodeExporter]

solved!

Original comment by nic...@creationgears.com on 20 Apr 2011 at 1:59

• Changed state: Fixed

[GoogleCodeExporter]

I have the 1.8 versions and had this error with my postgresql database. I 
resolved the issue by commenting out the following line in 
InstallController.php:

    $group_model->id = UserGroupsUser::ROOT;

This line is stomping over the sequence. Leaving this line out still assigns 
ID=1 to root because it is the first record INSERTED into the table.

Original comment by wats...@gmail.com on 19 Jan 2012 at 11:25