Moodle 3.5.3+: admin login failed after update to 3.5.6+

Moodle 3.5.3+: admin login failed after update to 3.5.6+

by Fernando Navarro Páez -
Number of replies: 5
Picture of Testers

Hello!

With Moodle 3.5.3+ (Build: 20181116) I can login using admin user. When I update it to 3.5.6+ (Build: 20190514) Moodle show a database error and I can't login:

Debug info: Duplicate entry '1-adminuser' for key 'mdl_user_mneuse_uix'

INSERT INTO mdl_user (city,auth,username,lang,confirmed,lastip,timecreated,timemodified,mnethostid,calendartype,maildisplay,mailformat,maildigest,autosubscribe,trackforums) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Stack trace:

line 489 of \lib\dml\moodle_database.php: dml_write_exception thrown

line 1329 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end()

line 1375 of \lib\dml\mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()

line 111 of \user\lib.php: call to mysqli_native_moodle_database->insert_record()

line 3903 of \lib\moodlelib.php: call to user_create_user()

line 4393 of \lib\moodlelib.php: call to create_user_record()

line 141 of \login\index.php: call to authenticate_user_login()


My config.php has following $CFG-> dboptions before update:

$CFG->dboptions = array (

  'dbpersist' => 0,

  'dbport' => '',

  'dbsocket' => '',

  'dbcollation' => 'utf8mb4_general_ci',

);

What can I do? 

Thanks in advance

Average of ratings: -
In reply to Fernando Navarro Páez

Re: Moodle 3.5.3+: admin login failed after update to 3.5.6+

by Fernando Navarro Páez -
Picture of Testers
UPDATE:

I have an "Invalid Login Token" message in error_log
I'm using standard Clean Theme
Average of ratings: -
In reply to Fernando Navarro Páez

Re: Moodle 3.5.3+: admin login failed after update to 3.5.6+

by Ken Task -
Picture of Particularly helpful Moodlers

Might change:

'dbcollation' => 'utf8mb4_general_ci',

to utf8_general_ci ... that's IF all your tables have that collation.

There is are a couple of CLI scripts that can help in moodlecode/admin/cli/

mysql_collation.php
mysql_compressed_rows.php
mysql_engine.php

each has help screen to see options if you use:

php mysql_collation.php without any switch/option help screen will show.

php mysql_collation.php -l

(list) should show you at the very end a summary of tables.

All tables must have same collation for the table and for the columns in those tables.

'SoS', Ken

Average of ratings: Useful (2)
In reply to Ken Task

Re: Re: Moodle 3.5.3+: admin login failed after update to 3.5.6+

by Fernando Navarro Páez -
Picture of Testers

Hello Ken. 

Thank you for your answer.

admin/cli/mysql_collation.php -l shows tables with utf8mb4_general_ci collation and others with utf8_general_ci collation.

I have executed admin/cli/mysql_collation.php --collation=utf8mb4_general_ci to convert all tables to utf8_general_ci collation.

Now I can access

Salu2

Fernando

Average of ratings: Useful (1)
In reply to Fernando Navarro Páez

Re: Re: Re: Moodle 3.5.3+: admin login failed after update to 3.5.6+

by Ken Task -
Picture of Particularly helpful Moodlers

Good to hear.  But ... not out of the woods yet!

You've now a decision to make, depending upon DB server and if you can change it's defaults for character set and collation.

Seems the entire DB industry (or at least MySQL and MariaDB) is moving to utf8mb4.  So you've really only delayed what probably needs to be done ... convert your DB to utf8mb4 character set and a collation to match.

Check the environment of your server.  Set pick list to any of  the higher versions of Moodle.  Then look at DB checks and especially the bottom of the environment check.

From personal experience, I resisted at first using other work-arounds like Global commands, etc.  Growing tired of having to remember or do them again on the next version upwards, decided to bite the bullet and just convert the DB's ... as well as defaults in my.cnf for server ... since my servers are usually dedicated to just moodles.

So ... strongly advise researching your hosting and situation, then make the change permanent if you can.

'SoS'. Ken

Average of ratings: Useful (1)
In reply to Ken Task

Re: Re: Re: Re: Moodle 3.5.3+: admin login failed after update to 3.5.6+

by Fernando Navarro Páez -
Picture of Testers

Hello Ken.

I totally agree. Thank you for your advices.

Salu2

Fernando

Average of ratings: -