I have some fields on one table and others on different tables. I then created a view containing the data needed for authentication and authorization. Does moodle recognize connecting through database view? it seems not connecting.
Yes, in fact if your fields are across different tables then you must use a database view.
You can use the Test settings link under Site administration ▸ Plugins ▸ Authentication ▸ Manage authentication. What do you get when you try this?
If you still need help please tell us more details: the Moodle version, the PHP version and the database type containing the view (such as MySQL or SQL Server).
Thank you @leon. I was able to do the connection successfully. 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. But the major error I get after users logged in, the connection is not fetching in their detail. All the fields are Empty . we use mysqli, moodle version 3.10
i think you need to re-check yout field configuration in Exernal Database Authentication plugin. and maybe you can try with a table (not a view) because view will take a lot of resources when many users login at the same time. and at some point it will fail to provide the data needed especially when the view is complex.
Both view and table act the same way. can someone please help me with this issue?
With the followingview:
SELECT * FROM user_vw;
+--------+--------+--------+----------------------+--------------+
| userid | name1 | name2 | mail | password |
+--------+--------+--------+----------------------+--------------+
| george | George | Jetson | gjetson@example.com | $2y$10$05Jfl |
| jane | Jane | Jetson | jjetson@example.com | $2y$10$orQ8c |
+--------+--------+--------+----------------------+--------------+
Then I can map the fields with:
Update local is set to "On every login" for each field. If this was set to "On creation" then fields would not be mapped if the user already existed in Moodle without the mapped fields.
You can also try connecting to the external database from the Moodle server using the settings from Moodle and check the values are as expected. For example if the settings were :
- Host (auth_db | host): 192.168.0.24
- DB name (auth_db | name): students
- DB user (auth_db | user): moodle_access
- Password (auth_db | pass): mypassword
- Table (auth_db | table): users_view
then connect with mysql -u moodle_access -pmypassword -h 192.168.0.24 students. Then try SELECT * FROM users_view; as see if the mapped fields have the expected values.
If it's still not working please share details of the field mapping settings from Moodle, the column names in the view and the results of the above test.
I have done it as stated but still not working. i have been using the plugin for over 6 years, but don't know why is returning empty for this installation.
this is the database structure
Are you using matric_number as the Moodle username? In which case Moodle will run the following query to read the mapped fields:
SELECT firstname AS F0, surname AS F1, email AS F2, matric_number AS F3
FROM user_view
WHERE matric_number = 'A001'
So again, I suggest you run the above query from the Moodle server as the DB user, changing ' A001' to a valid user and user_view to whatever your view is called. Because this will test:
- the connection from the Moodle server to the database;
- that the MySQL credentials work:
- at all, and,
- from the Moodle server because MySQL users include a network address for the account, for example: 'dbuser'@'localhost' or 'dbuser'@'192.168.%';
- that the MySQL credentials can read those columns: firstname, surname and email.
- that the view is returning the expected values for those columns.
You could also enable debugging in case any messages get logged to the Moodle web server's error log.
Everything seems fine from the image below.
The View contains the fields above, when user login, it authenticate and also enroll him in the courses. Only that name and email is not fetching.
I enabled debugging and this is the error it displays.
- line 706 of /lib/outputlib.php: call to debugging()
- line 7530 of /lib/moodlelib.php: call to theme_config::load()
- line 705 of /lib/classes/user.php: call to get_list_of_themes()
- line 927 of /lib/classes/user.php: call to core_user::fill_properties_cache()
- line 300 of /user/editlib.php: call to core_user::get_property_default()
- line 77 of /user/edit_form.php: call to useredit_shared_definition()
- line 214 of /lib/formslib.php: call to user_edit_form->definition()
- line 175 of /user/edit.php: call to moodleform->__construct()
Have you actually tried running the query on the Moodle server? The Test Settings page shows that the columns can be seen but it doesn't mean all columns contain data. If the Moodle database is MySQL (or MariaDB) then the mysql client is probably installed so this is an easy test.
I don't think the error "line 706 of /lib/outputlib.php: call to debugging()" is causing this but it should be ruled out as the cause. Try switching to the default Boost theme temporarily and see if 1) this error message and 2) the problem fetching user details from the database still occur.
If it's still not working I think the next step would be to add debugging messages to the Moodle source code. The mapped user fields are read in function get_userinfo() in auth/db/auth.php. If you back up that file (to auth.php.original) then insert the following error_log() call on line 240:
238 $result[$localname] = core_text::convert($value, $this->config->extencoding, 'utf-8');
239 }
240 error_log(__FUNCTION__ . "() " . print_r($result, true));
241 }
242 $rs->Close();
This will send the user fields read from the external database to the web server's error log, for example:
[10-Mar-2021 12:28:30 Europe/London] get_userinfo() Array
(
[firstname] => DBUser
[lastname] => Three
[email] => dbuser3@example.com
[username] => dbuser3
)
These values are then set in lib/moodlelib.php. Again back up this file if you change it. For existing users changes from the external database are updated by update_user_record_by_id() so you could insert line 4146:
4145 $newuser['timemodified'] = time();
4146 error_log(__FUNCTION__ . "() " . print_r($newuser, true));
4147 user_update_user((object) $newuser, false, false);
For new users the mapped fields are set in create_user_record() so you could insert the line shown at 4062:
4061 // Save user profile data.
4062 error_log(__FUNCTION__ . "() " . print_r($newuser, true));
4063 profile_save_data($newuser);
This should output the user's fields to the error log, for example:
[10-Mar-2021 12:54:33 Europe/London] create_user_record() stdClass Object
(
[firstname] => DBUser
[lastname] => Ten
[email] => dbuser10@example.com
⋮
)
Hopefully this will help identify whether the problem is obtaining the values from the external database or setting them in Moodle.
Thank you very much.
I have solved the problem by mapping all the fields that are in the database. I know is only surname, first name, and email are required fields, but may there is a bug related to any of the fields. Thank you once again
I have solved the problem by mapping all the fields that are in the database. I know is only surname, first name, and email are required fields, but may there is a bug related to any of the fields. Thank you once again