Unicode upgrade duplicate username error

Unicode upgrade duplicate username error

by Steve Bluck -
Number of replies: 3

Hi,

We are running 3.1.7 & are about to upgrade to 3.3.3+ with approx 9500 users.

 While running the $ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci script in test I've got:

ALTER TABLE mdl_user

                        MODIFY COLUMN username varchar(100)

                        CHARACTER SET utf8mb4

                        COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''

Error code: ddlexecuteerror

* line 485 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown

* line 1000 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()

* line 190 of /admin/cli/mysql_collation.php: call to mysqli_native_moodle_database->change_database_structure()

 

!!! DDL sql execution error !!!

!! Duplicate entry '11-ｋï½ï½šï½•ï½“１' for key 'mdl_user_mneuse_uix'

ALTER TABLE mdl_user

                        MODIFY COLUMN username varchar(100)

                        CHARACTER SET utf8mb4

                        COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''

Error code: ddlexecuteerror !!

!! Stack trace: * line 485 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown

* line 1000 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()

* line 190 of /admin/cli/mysql_collation.php: call to mysqli_native_moodle_database->change_database_structure()

 !!

If I run the query as root directly against MySQL (I'm not a MySQL expert in any form!):

ALTER TABLE mdl_user

MODIFY COLUMN username varchar(100)

CHARACTER SET utf8mb4

COLLATE utf8mb4_unicode_ci NOT NULL;

I get:

Error Code: 1062. Duplicate entry '11-ｋｏｚｕｓ１' for key 'mdl_user_mneuse_uix'

But I cant find a user "11-ｋｏｚｕｓ１" either via MySQL or Moodle admin (including checking for '11% in MySQL'), so where do I go from here?

Cheers

 


Average of ratings: -
In reply to Steve Bluck

Re: Unicode upgrade duplicate username error

by Steve Bluck -

The ‘11’ at the front of the username threw me, searching on kozus gave three entries of which I've deleted two but running the SQL manually still fails with the same error code 1062

Average of ratings: -
In reply to Steve Bluck

Re: Unicode upgrade duplicate username error

by Ken Task -
Picture of Particularly helpful Moodlers

Not a certified MySQL DB admin either but ....

this: mdl_user_mneuse_uix ...  looks to be moodle networking and is an index specifically for 'mnethost' and 'username' fields of the mdl_user table of the DB.

So I'd check the user you have remaining and their username + and *especially* the mnet host value in that users entry.  

All user accounts had a column for mnethost even if you never used Moodle Networking.  Value should be a '1'.  If that users record has a '1' then change mnethost to a '0' - save - .then back again to a '1' and save  Or, if it won't take a '0', change it to '2' - save - then re-edit changing that column value back to a '0' and save.  Am thinking that  by changing it DB server will re-index.

Check out:

https://dev.mysql.com/doc/refman/5.7/en/rebuilding-tables.html

for how to re-index ... or better yet, repair a table (mdl_user)

https://dev.mysql.com/doc/refman/5.7/en/mysqlcheck.html

Fingers Crossed!

'spirit of sharing', Ken





Average of ratings: -
In reply to Steve Bluck

Re: Unicode upgrade duplicate username error

by Steve Bluck -

And sorted -the 's' in kozus was in what appears to be in either wide text or MS Gothic font ,  which seems to have thrown the SQL query (as a guess because it expands beyond the var char (100) when changing to utf8mb4).

And of course it was the last user with this in their username, deleted & the query now runs

Thanks for your input though Ken!  

Average of ratings: -