External database authentication validates, but user profile is empty

External database authentication validates, but user profile is empty

by Danny H -
Number of replies: 10

Using a Maria database (same SQL server as Moodle) to authenticate users from an external database.  We don't want the user to be added to Moodle until they login, so we are NOT using cron to sync.

Moddle is authenticating the user, but the user is presented immediately with a blank profile form (user/edit.php). Isn't this form supposed to be pre-filled with data from the external database?

DEBUGGING that I've done:

In login/index.php, I added print_r($user) after 'if ($user)' and see values set for [username], [calendartype], and [language].  However most fields, such as [firstname], [lastname], [department], [country],... are empty.

In auth/db/auth.php, this code correctly returns the moodle field name and the mapped field name:

foreach ($userfields as $field) {
            if (!empty($this->config->{"field_map_$field"})) {
                $moodleattributes[$field] = $this->config->{"field_map_$field"};
            }
        }

Am I missing a setting or something that correctly populates $user with the elements from the external database?


Average of ratings: -
In reply to Danny H

Re: External database authentication validates, but user profile is empty

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

The user's profile form is supposed to be pre-filled with data from the external database for any fields specified under the Data mapping heading on the configuration page for External Database authentication. Users only see the profile form if any of the mandatory fields — first name, surname and email address — are blank, if these are populated they're taken straight to the home page.

So the basic checks are 1) that the mapped fields in Moodle are the names of the corresponding columns in the external database and 2) that these columns in the external database aren't blank.

If my external database has the following:

SELECT username, password, firstname, lastname, email FROM users;
+----------+---------------+-----------+----------+----------------------+
| username | password      | firstname | lastname | email                |
+----------+---------------+-----------+----------+----------------------+
| george   | $2y$10$05J... | George    | Jetson   | gjetson@example.com  |
| jane     | $2y$10$orQ... | Jane      | Jetson   | jjetson@example.com  |
+----------+---------------+-----------+----------+----------------------+

and my configuration has:

Data mapping for First name, Surname and Email address

when jane or george log in these three fields are transferred from the external database into their Moodle profile.

You've probably already check this but if you could shared the data mapping and example database values so we can compare them, but be sure to obscure or remove any personal data before sharing.

Average of ratings: Useful (1)
In reply to Leon Stringer

Re: Re: External database authentication validates, but user profile is empty

by Danny H -
Thanks for your help.  It confirms my understanding of the process.

Here is a screen capture for the external database 'Structure'.  Note that all are varchar(). For every entry in the "Data Mapping" I have confirmed the corresponding field name is entered (except the two phonetic entries which are blank).  "On every login", "Never" and "Locked" are selected (if you really want to see this, I'll capture and post).

To make further tests, I unlocked the firstname, lastname and email fields so I could manually enter them on the blank screen, and when I do, the 'cannotupdateprofile' message appears (from user/edit.php).  Could this be related?

Obviously, the database is being read, since authentication happens.  But, the user form is still blank.  Since we can create courses, there seems to be no "write" issue to the Moodle database.

These fields are left empty in the external database:  description, url, idnumber, phone1, phone2, alternativename.
These fields are always the same: country='US', language='EN', institution='[Our Company Name]'.

Attachment authen_database.jpg
Average of ratings: -
In reply to Danny H

Re: Re: Re: External database authentication validates, but user profile is empty

by Iñaki Arenaza -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Do you have any errors in your PHP error logs? "cannotupdateprofile" in user/edit.php is only triggered if the auth/db plugin can't update the user details, using the user_update method. And having a look at auth/db/auth.php, user_update method, it looks like it is probably failing in this part:

       $curruser = $this->get_userinfo($olduser->username);
        if (empty($curruser)) {
            error_log("ERROR:User $olduser->username found in ext db");
            return false;
        }

It tries to get the user info using get_userinfo (which is the same method used to get all the user details when it logs in). And if it fails, it logs an error message and returns false (which triggers the "cannotupdateprofile" error. To confirm that this is indeed the root of your problem, we would need to know if the "ERROR:User $olduser->username found in ext db" error message appears in your PHP error logs.

You might need to set the debug messages level to "DEVELOPER" in the Development settings.

Saludos. Iñaki

Average of ratings: -
In reply to Iñaki Arenaza

Re: Re: Re: Re: External database authentication validates, but user profile is empty

by Danny H -
I cant' get php.ini setting of error_log = /var/log/httpd/php_error.log to work, even after restart httpd. But here's what the web page returns:

Error updating user record
More information about this error
Debug info:
Error code: cannotupdateprofile
Stack trace:
line 494 of /lib/setuplib.php: moodle_exception thrown
line 226 of /user/edit.php: call to print_error()

If this helps any:
function core_login_get_return_url()
if (user_not_fully_set_up($USER, true)) {
$USER is incomplete (like I described above).
Average of ratings: -
In reply to Danny H

Re: Re: Re: Re: Re: External database authentication validates, but user profile is empty

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

A potential cause of this (although it's a little obscure) is that the MariaDB user account being used to access the external database only has access to the username and password columns. In a test this causes the behaviour you've described. Could that be the issue? Either check the access this user has, or connect to MariaDB using this user's credentials and see if you can select all the columns from the external database table.

Average of ratings: -
In reply to Danny H

Re: Re: Re: Re: Re: External database authentication validates, but user profile is empty

by Danny H -
I think I've found the location of the error:

function get_userinfo
"$sql =" is set to the correct query
SELECT firstname AS F0, lastname AS F1, email AS F2, city AS F3, country AS F4, language AS F5, description AS F6, url AS F7, idnumber AS F8, institutuion AS F9, department AS F10, phone1 AS F11, phone2 AS F12, address AS F13, middlename AS F14, alternativename AS F15, fielduser AS F16 FROM users WHERE fielduser = 'THE_USER_NAME'

LINE 230 fails:
if ($rs = $authdb->Execute($sql)) {
which is calling
lid/adodb/adodb.inc.php
which gets to
$ret = $this->_Execute($sql,false);
where $ret is set to empty.

The above _Execute function:
where $sql = "SET NAMES 'utf-8'"
goes through the line (unchanged)
$sql = ADODB_str_replace( '_ADODB_COUNT', '', $sql );
not in debug, so goes through the line
where: $inputarr is false
$this->_queryID = @$this->_query($sql,$inputarr);
returns false

I believe it's the next lines that retrieve the data from the external database?
Average of ratings: -
In reply to Danny H

Re: Re: Re: Re: Re: Re: External database authentication validates, but user profile is empty

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

Check that you can run that query manually from the web server as the database user specified in the External Database settings.

If the MariaDB database is on the same server as the web server you should be able to do run mysql -u DB_user -p DB_name where DB_user and DB_name are the DB user and DB name from the External Database settings page in Moodle. Then copy and paste the query and see if it works. If the database is on a different server you'll need mysql -u DB_user -p -h Host DB_name with Host again taken from the settings page.

Average of ratings: -
In reply to Leon Stringer

Re: Re: Re: Re: Re: Re: Re: External database authentication validates, but user profile is empty

by Danny H -
That's what I needed to find the bug. I had misspelled 'institution', and evidently that was enough to mess up the response?
Average of ratings: Useful (1)
In reply to Danny H

Re: Re: Re: Re: Re: Re: Re: Re: External database authentication validates, but user profile is empty

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers
The initial login step just gets the username and password values from the external database, only once that authentication occurs does it perform the SELECT for fields being mapped. When I tried it here with Debug messages set to "DEVELOPER" (under Site administration → Development → Debugging), then the cannotupdateprofile page does include "1054: Unknown column" in the output so it does get reported.
Average of ratings: Useful (1)
In reply to Leon Stringer

Re: Re: Re: Re: Re: Re: Re: Re: Re: External database authentication validates, but user profile is empty

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

Just to correct my previous reply: you need both Debug messages set to "DEVELOPER" and Debug ADOdb set to "Yes" (in the External Database authentication plugin settings) to get Moodle to report the "Unknown column" error message from the external database.

Average of ratings: -