20 August 2013, 12:39

So I just upgraded our Mahara install from 1.4 to 1.7... along with the process, I transitioned accounts that were created using LDAP over to XML-RPC auth which is configured for our Moodle installation. This is so we can use our new Moodle - Mahara portfolio assignment type that we created over the summer.

Before the upgrade, all accounts in the "usr" and "auth_remote_user" tables were set to use LDAP auth. After the upgrade, I manually ran a query to switch these to XML-RPC (I just switched the instance number to the XML-RPC auth). Then, I deleted the LDAP authentication from the institution so that only XML-RPC remained.

The first issue is that whenever a user FIRST attempts to log in to mahara using the moodle Networked Servers block, they get sent to Mahara and get an error. If they try again it works from then on.

The second issue is that brand new users who have never used Mahara are not able to SSO. They can keep trying to use the Networked Servers block in moodle but always hit the error message (unfortunately I'm not sure what it is.)

The error logs on the server look like this when a new user tries to log in.

[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216] [WAR] c7 (lib/errors.php:749) Failed to get a recordset: mysql error: [1452: Cannot add or update a child row: a foreign key constraint fails ("mahara_prod"."usr", CONSTRAINT "usr_aut_fk" FOREIGN KEY ("authinstance") REFERENCES "auth_instance" ("id"))] in adodb_throw(INSERT INTO "usr" ("username", "password", "salt", "passwordchange", "active", "deleted", "expirymailsent", "lastlogin", "inactivemailsent", "staff", "admin", "firstname", "lastname", "studentid", "preferred name", "email", "quota", "quotaused", "authinstance", "ctime", "showhomeinfo", "unread") VALUES ('xxxxxxxx', '', '', 1, 1, 0, 0, '2013-08-19 14:59:03', 0, 0, 0, 'xxxxxxxx', 'xxxxxxxx', '', '', '[email protected]', '104857600', 0, '6', '2013-08-19 14:59:03', 1, 0), ), referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216] [WAR] c7 (lib/errors.php:749) Command was: INSERT INTO "usr" ("username", "password", "salt", "passwordchange", "active", "deleted", "expirymailsent", "lastlogin", "inactivemailsent", "staff", "admin", "firstname", "lastname", "studentid", "preferredname", "email", "quota", "quotaused", "authinstance", "ctime", "showhomeinfo", "unread") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and values was (xxxxxxxx,,,1,1,0,0,2013-08-19 14:59:03,0,0,0,xxxxxxxx,xxxxxxxx,,,[email protected],104857600,0,6,2013-08-19 14:59:03,1,0), referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216] Call stack (most recent first):, referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216]   * log_message("Failed to get a recordset: mysql error: [1452: Can...", 8, true, true) at /var/www/html/mahara/lib/errors.php:109, referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216]   * log_warn("Failed to get a recordset: mysql error: [1452: Can...") at /var/www/html/mahara/lib/errors.php:749, referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216]   * SQLException->__construct("Failed to get a recordset: mysql error: [1452: Can...") at /var/www/html/mahara/lib/dml.php:1068, referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216]   * insert_record("usr", object(stdClass), "id", true) at /var/www/html/mahara/auth/user.php:438, referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216]   * User->commit() at /var/www/html/mahara/lib/user.php:2131, referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216]   * create_user(object(User), array(size 0), "httpslearningupedumoodle", object(AuthXmlrpc), "xxxxxxxx") at /var/www/html/mahara/auth/xmlrpc/lib.php:219, referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216]   * AuthXmlrpc->request_user_authorise("39ce9fd23fb781266e43860f0689a8872dc843e4", "https://learning.up.edu/moodle") at /var/www/html/mahara/auth/xmlrpc/land.php:94, referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216] , referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216] [WAR] c7 (lib/dml.php:1068) Failed to get a recordset: mysql error: [1452: Cannot add or update a child row: a foreign key constraint fails ("mahara_prod"."usr", CONSTRAINT "usr_aut_fk" FOREIGN KEY ("authinstance") REFERENCES "auth_instance" ("id"))] in adodb_throw(INSERT INTO "usr" ("username", "password", "salt", "passwordchange", "active", "deleted", "expirymailsent", "lastlogin", "inactivemailsent", "staff", "admin", "firstname", "lastname", "studentid", "preferredname", "email", "quota", "quotaused", "authinstance", "ctime", "showhomeinfo", "unread") VALUES ('xxxxxxxx', '', '', 1, 1, 0, 0, '2013-08-19 14:59:03', 0, 0, 0, 'xxxxxxxx', 'xxxxxxxx', '', '', '[email protected]', '104857600', 0, '6', '2013-08-19 14:59:03', 1, 0), ), referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216] [WAR] c7 (lib/dml.php:1068) Command was: INSERT INTO "usr" ("username", "password", "salt", "passwordchange", "active", "deleted", "expirymailsent", "lastlogin", "inactivemailsent", "staff", "admin", "firstname", "lastname", "studentid", "preferredname", "email", "quota", "quotaused", "authinstance", "ctime", "showhomeinfo", "unread") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and values was (xxxxxxxx,,,1,1,0,0,2013-08-19 14:59:03,0,0,0,xxxxxxxx,xxxxxxxx,,,[email protected],104857600,0,6,2013-08-19 14:59:03,1,0), referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216] Call stack (most recent first):, referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216]   * insert_record("usr", object(stdClass), "id", true) at /var/www/html/mahara/auth/user.php:438, referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216]   * User->commit() at /var/www/html/mahara/lib/user.php:2131, referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216]   * create_user(object(User), array(size 0), "httpslearningupedumoodle", object(AuthXmlrpc), "xxxxxxxx") at /var/www/html/mahara/auth/xmlrpc/lib.php:219, referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216]   * AuthXmlrpc->request_user_authorise("39ce9fd23fb781266e43860f0689a8872dc843e4", "https://learning.up.edu/moodle") at /var/www/html/mahara/auth/xmlrpc/land.php:94, referer: https://learning.up.edu/moodle/
[Mon Aug 19 14:59:03 2013] [error] [client 10.10.1.216] , referer: https://learning.up.edu/moodle/

Sorry for the error log dump! This log is from one user who's name has been replaced with xxxxxx's of course.

It basically looks like the query can't insert a new row because one of the columns doesn't exist? Arg!

Does anyone with a good understanding of the user creation process/SQL know what I did wrong to break user creations? Thanks for any help!

Tony

20 August 2013, 14:57

Hi Tony,

The error "Cannot add or update a child row: a foreign key constraint fails ("mahara_prod"."usr", CONSTRAINT "usr_aut_fk" FOREIGN KEY ("authinstance") REFERENCES "auth_instance" ("id"))" means that it's trying to set usr.authinstance to ID 6, but there is no corresponding record in auth_instance.id with value 6.

Did you set the LDAP instance as the parent auth method for the XMLRPC auth instance, before deleting it? I believe that would cause exactly this issue. (The way it works, the parent authority is actually the one stored in the usr record; and in this case, the parent authority of ID 6 no longer exists.) If so, just update the XMLRPC auth instance so that "Parent authority" is "None". See http://manual.mahara.org/en/1.7/administration/institutions.html#xml-rpc-mnet-authentication

Cheers,

Aaron

21 August 2013, 5:22

Aaron - thanks so much!

This definitely seems to be the issue, except that I do have the XMLRPC auth instance set to have no parent authority.

Where in the database is it still trying to set the parent auth to the one I deleted (6)? I can't find any reference to it so I'm not sure where it's getting that from. Does the XMLRPC auth require a parent authority? In 'auth_installed' it says it does :-/

-Tony

21 August 2013, 11:18

Hi Tony,

The parent auth instance is stored in the "auth_instance_config" table. Try this:

select
    ai.*, aic.*
from
    auth_instance ai
    inner join auth_instance_config aic
        on ai.id = aic.instance
where
    aic.field='parent'
    and aic.value='6'
;

If you've already removed the parent authority via the UI, then I'm a little stumped. The only thing I can think of is this: If you deleted the old parent authority (LDAP), and then clicked on the XMLRPC auth instance, the dropdown menu will show "None" (and there will be a warning in the PHP logs (lib/pieforms/pieform.php:1212) Invalid value for select "parent"), but it won't actually delete the parent auth setting until you hit "Submit" in the XMLRPC instance config popup the first time. Perhaps that's what's going on?

Cheers,

Aaron

21 August 2013, 11:41

Actually this has inspired me to submit a bug fix that makes it so that when you delete an auth instance, it stops being the parent authority to other auth instances.

https://bugs.launchpad.net/mahara/+bug/1214647

22 August 2013, 6:37

Awesome! I had a feeling there was something buggy going on. I actually did go back in to that page and re-save it so I guess that's why I couldn't find any reference to it in the DB... because I had already fixed it!

Thanks again for all your help.

20 August 2013, 21:29

Hi Tony,

And following on Aaron's suggestion, can you please check that you ticked the box for "We auto-create accounts" in the XML-RPC settings?

Cheers

Kristina

21 August 2013, 5:13

Yep - got it marked!