?
avatar sanek4life
sanek4life
10 Mar 2020

изображение

Error text:

Предупреждение
JInstaller: :Install: Ошибка SQL Duplicate entry 'BGirl' for key 'idx_username'
Обновление файлов: файл ошибок SQL Ошибка базы данных с номером 1062
Duplicate entry 'BGirl' for key 'idx_username'
SQL =

ALTER TABLE #__users ADD UNIQUE INDEX idx_username (username);

Перед обновлением убедитесь, что установленные расширения совместимы с новой версией Joomla!.

System information (as much as possible)

Joomla 3.9.16, PHP 7.2.28, MySQL 5.5.64-MariaDB

Additional comments

изображение

After I pressed button Fix:

изображение

avatar sanek4life sanek4life - open - 10 Mar 2020
avatar joomla-cms-bot joomla-cms-bot - change - 10 Mar 2020
Labels Added: ?
avatar joomla-cms-bot joomla-cms-bot - labeled - 10 Mar 2020
avatar wilsonge
wilsonge - comment - 10 Mar 2020

So here's the fun part. This means your site has two users with the same username :/

avatar HLeithner
HLeithner - comment - 10 Mar 2020

It seams your database has the same username more then once in the table, as part of one of the latest security releases we have to add an unique index to the user table, please check your database #__users table and remove duplicated entries.

avatar sanek4life
sanek4life - comment - 10 Mar 2020

So here's the fun part. This means your site has two users with the same username :/

My site has existed since February 2008 with Joomla 1.5. He was migrated to Joomla 2.5 and now he is on Joomla 3.9.16.

Was it possible earlier that 2 users could have the same username? I can’t understand how this could happen.

avatar mbabker
mbabker - comment - 10 Mar 2020

as part of one of the latest security releases we have to add an unique index to the user table

This should have been highlighted in bold red text in the release announcement and not buried under the security notes in some vague thing. It's a B/C breaking data schema change, and just as this issue shows, can cause an upgrade failure. People need lead time to address this change and not stumble on it by accident then think Joomla's crappy software because the update broke their site.

avatar sanek4life
sanek4life - comment - 10 Mar 2020

It seams your database has the same username more then once in the table, as part of one of the latest security releases we have to add an unique index to the user table, please check your database #__users table and remove duplicated entries.

Is there an easy way to find a duplicate? Over 12 thousand users have been registered on my website over these 12 years.

avatar HLeithner
HLeithner - comment - 10 Mar 2020

open your favorite SQL manage and execute the following query:

SELECT username FROM #__users GROUP BY username HAVING count(*) > 1

replace #__ with your database prefix

and yes should be mentioned on the release page FAQ entry is in progress.

avatar joomdonation
joomdonation - comment - 10 Mar 2020

Happened for our website, too (some accounts from 2009, some accounts from 2012....). The mentioned query should be changed to:

SELECT username FROM #__users GROUP BY username HAVING COUNT(*) > 1
avatar HLeithner
HLeithner - comment - 10 Mar 2020

@joomdonation correct thanks for fixing the query

avatar joomdonation
joomdonation - comment - 10 Mar 2020

Maybe a quick and dirty fix would be running this query to delete all these kind of user records (replace jos_ with table prefix of your database). Remember to backup the table first before running the query (I deleted these user accounts manually via Users Manage and haven't tested this command yet)

DELETE FROM jos_users WHERE username IN (SELECT username FROM jos_users GROUP BY username HAVING COUNT(*) > 1)
avatar wilsonge
wilsonge - comment - 10 Mar 2020

@joomdonation your deleting both user accounts in that scenario. Depends how intended that is.

avatar sanek4life
sanek4life - comment - 10 Mar 2020

I think it will affect a huge number of websites. Maybe you will release something like a plugin to fix this error, because the built-in button Fix, which was supposed to fix this error, does not cope with this task, but this button is for fixing errors in the database, right?

avatar wilsonge
wilsonge - comment - 10 Mar 2020

The button fixes schema issues in the database - it doesn't fix data issues

avatar sanek4life
sanek4life - comment - 10 Mar 2020

The button fixes schema issues in the database - it doesn't fix data issues

Maybe there is a chance that there will be a second button to fix this error?

avatar HLeithner
HLeithner - comment - 10 Mar 2020

It's not possible to fix this error automatically because we have to delete (or rename) one of the 2 users

avatar joomdonation
joomdonation - comment - 10 Mar 2020

@wilsonge Yes. These accounts in this case, use same username are invalid, so I have no problem with deleting it. But others might want to do different (in my case, these accounts seems to be spam accounts, not activated and never logged in)

avatar sanek4life
sanek4life - comment - 10 Mar 2020

It's not possible to fix this error automatically because we have to delete (or rename) one of the 2 users

I think that we need official documentation with steps to fix this error and the news on the main page joomla.org

avatar HLeithner
HLeithner - comment - 10 Mar 2020

I think that we need official documentation with steps to fix this error and the news on the main page joomla.org

We are working on it, main page already shows a information about this behavior.

avatar richard67
richard67 - comment - 10 Mar 2020

@sanek4life You can find duplicates in SQL with following command:
SELECT u3.id, u3.username FROM #__users u3 WHERE u3.username IN (SELECT u2.username FROM (SELECT u1.username, COUNT(*) AS cnt FROM #__users u1 GROUP BY u1.username) u2 WHERE u2.cnt > 1);

(replace "#__" by your table prefix).

Update: Better query for getting the duplicate user names can be found on https://docs.joomla.org/J3.x:Duplicate_usernames_cause_update_issue:

SELECT username FROM #__users GROUP BY username HAVING COUNT(*) > 1

Advantage of my query is that it gives you the IDs of the users with duplicate username.

avatar ReLater
ReLater - comment - 11 Mar 2020

Was it possible earlier that 2 users could have the same username? I can’t understand how this could happen.

In my own interest: Was it possible by Joomla core? As far as I see not in versions 1.5.26 to 3. I don't have older versions installed than 1.5.26.

avatar alikon
alikon - comment - 11 Mar 2020

username must be unique, if we are doing it wrong in the past , then forgive us,
but this is a right move

avatar ReLater
ReLater - comment - 11 Mar 2020

if we are doing it wrong in the past

I just wanted to know if it was possible at any time (early Joomla versions) because i know it since "ever" like this:

username must be unique

avatar Fedik
Fedik - comment - 11 Mar 2020

I just wanted to know if it was possible at any time

it was possible through import directly do database, there was no unique constraint,
but it not means that it was correct thing to do.
if I right

avatar richard67
richard67 - comment - 11 Mar 2020

Our PHP code checks that. But in case if a username is too long, MySQL silently cuts if off at the end, so maybe this is how it happened.

avatar brianteeman
brianteeman - comment - 11 Mar 2020

I discovered on one of my own sites that I had this problem. In my case it was not truncated text as the username field is 150 characters long. My problem was bad code that I wrote to create users from a form and without checking to ensure it was unique before directly inserting in the database

avatar ReLater
ReLater - comment - 11 Mar 2020

Thanks! Now I see. Also a spammer protection that misuse username for longer junk entries.

avatar richard67
richard67 - comment - 11 Mar 2020

@sanek4life Did the instructions on help you? If so, you can close this issue, I think. For reasons stated above there won't be an automatic fix.

avatar richard67
richard67 - comment - 14 Mar 2020

@sanek4life Would be nice if you could answer my previous comment.

avatar sanek4life
sanek4life - comment - 15 Mar 2020

@sanek4life Would be nice if you could answer my previous comment.

I figured out how to do it.

avatar sanek4life
sanek4life - comment - 15 Mar 2020

@sanek4life Did the instructions on help you? If so, you can close this issue, I think. For reasons stated above there won't be an automatic fix.

Wow! I’m shocked how such a thing could have happened !!! At that time, Joomla 1.5 was installed on my website, this is 2009!

изображение

avatar sanek4life
sanek4life - comment - 15 Mar 2020

replace #__ with your database prefix

Please add this description to the FAQ, immediately after the query for the database is written there

here - https://docs.joomla.org/J3.x:Duplicate_usernames_cause_update_issue

avatar sanek4life
sanek4life - comment - 15 Mar 2020

It was probably a spammer. In the same second, two identical users on the site! How is this possible?

изображение

avatar sanek4life
sanek4life - comment - 15 Mar 2020

four users with a few seconds difference!

изображение

avatar sanek4life
sanek4life - comment - 15 Mar 2020

@richard67 Out of 12 thousand users, I found duplicate usernames for only less than 10 values. These were users who tried to re-register (as well as users who registered the exact time accurate to the second when 2 identical accounts were created on the site at once) and only one spammer.

For one user I did not find a duplicate account with the same username and I believe that one account was mistakenly specified, I was able to fix this error by simply changing the username to one letter.

I found that not all users received emails with account activation, and some users could not even activate their accounts on the site. It’s good that Joomla can now resend an account activation email.

avatar ReLater
ReLater - comment - 15 Mar 2020

Please add this description to the FAQ, immediately after the query for the database is written there

Done!
BTW: Anybody can edit the docs. You, too.

avatar ReLater
ReLater - comment - 15 Mar 2020

Can you close this issue, please.

avatar Quy Quy - change - 15 Mar 2020
Status New Closed
Closed_Date 0000-00-00 00:00:00 2020-03-15 22:09:35
Closed_By Quy
avatar joomla-cms-bot joomla-cms-bot - change - 15 Mar 2020
Closed_By Quy joomla-cms-bot
avatar joomla-cms-bot joomla-cms-bot - close - 15 Mar 2020
avatar joomla-cms-bot
joomla-cms-bot - comment - 15 Mar 2020

Set to "closed" on behalf of @Quy by The JTracker Application at issues.joomla.org/joomla-cms/28308

Add a Comment

Login with GitHub to post a comment