Duplicate usernames cause update issue

From Joomla! Documentation

Other languages:
Deutsch • ‎English • ‎Nederlands • ‎Türkçe • ‎español • ‎français • ‎italiano • ‎Ελληνικά

In some websites, updating to Joomla 3.9.16 results in a database error due to duplicated usernames. If this happens do not panic - the upgrade has been mostly completed without issue and is operating as usual, the remaining database change can be applied after following the instructions below.

Errors reported[edit]

Users receive an update error regarding a failed database query due to a duplicated key in their site's #__users table when upgrading from Joomla 3.9.15 or older.

Joomla 3.9.16 Upgrade Error

Versions affected[edit]

General Information

This pertains only to Joomla! version(s): 3.9.16

What is the cause[edit]

Your website has more than one user in the #__users table with the same username, which is a security concern in that the incorrect account may be accessed by the wrong account owner.

How to fix[edit]

You will need to review your #__users table in your database and fix any duplicated usernames by either removing old user accounts or changing the username until all usernames are unique. Once this is finished, if you have already upgraded to 3.9.16 run the database schema fixer tool to finish the upgrade (this step is not required if you do this review prior to upgrading).

Joomla is unable to automatically determine the correct action to take and as a result is unable to perform this step for you - it has to be fixed by you as the site owner.

The following SQL Query can be run on your database to view which usernames are duplicated. Replace the placeholder #__ with the table prefix of your Joomla database before you run it:

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

One possible way to make resolving this simpler might be to remove all users who have never visited the site. Though this should be done with care, and ensure to backup the table first. After running this query, you can check again how many duplicate users you have.

DELETE FROM #__users WHERE lastvisitDate = "0000-00-00 00:00:00"