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 INDEXidx_username
(username
);Перед обновлением убедитесь, что установленные расширения совместимы с новой версией Joomla!.
Joomla 3.9.16, PHP 7.2.28, MySQL 5.5.64-MariaDB
After I pressed button Fix
:
Labels |
Added:
?
|
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.
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.
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.
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.
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.
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
@joomdonation correct thanks for fixing the query
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)
@joomdonation your deleting both user accounts in that scenario. Depends how intended that is.
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?
The button fixes schema issues in the database - it doesn't fix data issues
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?
It's not possible to fix this error automatically because we have to delete (or rename) one of the 2 users
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
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.
@wilsonge created a FAQ page https://docs.joomla.org/J3.x:Duplicate_usernames_cause_update_issue
thx
@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.
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.
username must be unique, if we are doing it wrong in the past , then forgive us,
but this is a right move
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
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
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.
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
Thanks! Now I see. Also a spammer protection that misuse username
for longer junk entries.
@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.
@sanek4life Would be nice if you could answer my previous comment.
@sanek4life Would be nice if you could answer my previous comment.
I figured out how to do it.
@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!
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
@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.
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.
Can you close this issue, please.
Status | New | ⇒ | Closed |
Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2020-03-15 22:09:35 |
Closed_By | ⇒ | Quy |
Closed_By | Quy | ⇒ | joomla-cms-bot |
Set to "closed" on behalf of @Quy by The JTracker Application at issues.joomla.org/joomla-cms/28308
So here's the fun part. This means your site has two users with the same username :/