Pre-upgrade database check issues Topic is solved

[I-MAG]

Hello,
I ran the database check to make sure all is well before I upgrade. I got this:

Code: Select all

    6 Database Problems Found.
    Other Information

    Database schema version (3.9.10-2019-07-09) does not match CMS version (3.10.7-2022-03-18).
    Table 'xyz_users' does not have index 'idx_username'. (From file 3.9.16-2020-03-04.sql.)
    Table 'xyz_template_styles' does not have column 'inheritable'. (From file 3.10.0-2020-08-10.sql.)
    Table 'xyz_template_styles' does not have column 'parent'. (From file 3.10.0-2020-08-10.sql.)
    Table 'xyz_users' does not have column 'authProvider'. (From file 3.10.7-2022-03-18.sql.)
    The Joomla! Core database tables have not been converted yet to UTF-8 Multibyte (utf8mb4).

When I click fix, I get this and the fixing process does not carry on:

Code: Select all

An error has occurred.

    1062 Duplicate entry 'Zainah' for key 'idx_username' 

My current site fuctions normally. But I'm worried these discrepencies would affect the upgrade.
Any thoughs on what to do?

[I-MAG]

I went to phpmyadmin and ran repair tables, and optimise tables. That did not work.
Any idea on how can I fix this?

[gsmela]

Please post the Forum Post Assistant:

https://forumpostassistant.github.io/docs/

[Per Yngve Berg]

Table 'xyz_users' have User 'Zainah' twice. Delete one of them. It prevents the database from making a unique index.

[I-MAG]

Table 'xyz_users' have User 'Zainah' twice. Delete one of them. It prevents the database from making a unique index.

Many thanks. I did that. But apparenlty there are numerous users that duplicates. Not sure how that happened.
ANyway, is there a way to batch cancel duplicates? Doing that manually is tedious and time consuming.

[Per Yngve Berg]

Perhaps use ID From-To?

[I-MAG]

Perhaps use ID From-To?

Thanks.
I ran this through SQL and it did the trick:

Code: Select all

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

Then I deleted the records.
PS: RFeplace XYZ with the prefix of the database.