Moodle

Workout why we are getting foreign key violations on mnet_host.applicationid

Details

  • Type: Sub-task Sub-task
  • Status: Closed Closed
  • Priority: Minor Minor
  • Resolution: Fixed
  • Affects Version/s: 1.9.3
  • Fix Version/s: 1.9.8, 2.0
  • Component/s: MNet
  • Labels:
    None
  • Difficulty:
    Moderate
  • Affected Branches:
    MOODLE_19_STABLE
  • Fixed Branches:
    MOODLE_19_STABLE, MOODLE_20_STABLE

Description

From Dan's data:

Moodles Affected Average Percentage rows affected Table Key Total Violated Rows Total Rows
141 99.8 mnet_host applicationid 282 283

Work out where these are coming from, and see if we can devise an automated clean-up on upgrade.

Issue Links

This issue will help resolve:
MDL-21260 General open bugs Minor Open
This issue has a non-specific relationship to:
MDL-21277 audit all hosts mnet feature now that we have multiple application types Minor Open

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Penny Leach added a comment -

Dan can we get some more info on what data is in here ?

Show
Penny Leach added a comment - Dan can we get some more info on what data is in here ?
Hide
Permalink
Dan Poltawski added a comment -

Yus, I need to to get back up to speed about how I generated this data

Show
Dan Poltawski added a comment - Yus, I need to to get back up to speed about how I generated this data
Hide
Permalink
Dan Poltawski added a comment - - edited

Looks to me like complete rubbish in there (including the hosts own key?!)

This is from the moodle host vle.cleo.net.uk (note the first entry wwwroot is that, and second is blank with 'All Hosts' name):

 
mysql> SELECT id, deleted, wwwroot, ip_address , name, transport, portno, last_connect_time, applicationid from mnet_host;
+----+---------+---------------------------+----------------+-------------+-----------+--------+-------------------+---------------+
| id | deleted | wwwroot                   | ip_address     | name        | transport | portno | last_connect_time | applicationid |
+----+---------+---------------------------+----------------+-------------+-----------+--------+-------------------+---------------+
|  1 |       0 | http://vle.cleo.net.uk    | 194.66.222.132 |             |         0 |      0 |                 0 |             1 | 
| 11 |       0 |                           |                | All Hosts   |         0 |      0 |                 0 |             1 | 
| 13 |       0 | http://mahara.cleo.net.uk | 194.66.222.132 | CLEO Mahara |         0 |      0 |        1260807874 |            11 | 
+----+---------+---------------------------+----------------+-------------+-----------+--------+-------------------+---------------+
3 rows in set (0.02 sec)

mysql> select * from mnet_application;
+----+--------+--------------+-------------------------+-----------------------+
| id | name   | display_name | xmlrpc_server_url       | sso_land_url          |
+----+--------+--------------+-------------------------+-----------------------+
|  1 | moodle | Moodle       | /mnet/xmlrpc/server.php | /auth/mnet/land.php   | 
| 11 | mahara | Mahara       | /api/xmlrpc/server.php  | /auth/xmlrpc/land.php | 
+----+--------+--------------+-------------------------+-----------------------+
2 rows in set (0.00 sec)
Show
Dan Poltawski added a comment - - edited Looks to me like complete rubbish in there (including the hosts own key?!) This is from the moodle host vle.cleo.net.uk (note the first entry wwwroot is that, and second is blank with 'All Hosts' name): 
 
mysql> SELECT id, deleted, wwwroot, ip_address , name, transport, portno, last_connect_time, applicationid from mnet_host;
+----+---------+---------------------------+----------------+-------------+-----------+--------+-------------------+---------------+
| id | deleted | wwwroot                   | ip_address     | name        | transport | portno | last_connect_time | applicationid |
+----+---------+---------------------------+----------------+-------------+-----------+--------+-------------------+---------------+
|  1 |       0 | http://vle.cleo.net.uk    | 194.66.222.132 |             |         0 |      0 |                 0 |             1 | 
| 11 |       0 |                           |                | All Hosts   |         0 |      0 |                 0 |             1 | 
| 13 |       0 | http://mahara.cleo.net.uk | 194.66.222.132 | CLEO Mahara |         0 |      0 |        1260807874 |            11 | 
+----+---------+---------------------------+----------------+-------------+-----------+--------+-------------------+---------------+
3 rows in set (0.02 sec)

mysql> select * from mnet_application;
+----+--------+--------------+-------------------------+-----------------------+
| id | name   | display_name | xmlrpc_server_url       | sso_land_url          |
+----+--------+--------------+-------------------------+-----------------------+
|  1 | moodle | Moodle       | /mnet/xmlrpc/server.php | /auth/mnet/land.php   | 
| 11 | mahara | Mahara       | /api/xmlrpc/server.php  | /auth/xmlrpc/land.php | 
+----+--------+--------------+-------------------------+-----------------------+
2 rows in set (0.00 sec)
Hide
Permalink
Dan Poltawski added a comment -

But this is a different moodle (where the moodle application id is not 1, and also I think is basically never used)

mysql> select * from mnet_application;
+----+--------+--------------+-------------------------+-----------------------+
| id | name   | display_name | xmlrpc_server_url       | sso_land_url          |
+----+--------+--------------+-------------------------+-----------------------+
|  2 | moodle | Moodle       | /mnet/xmlrpc/server.php | /auth/mnet/land.php   | 
| 12 | mahara | Mahara       | /api/xmlrpc/server.php  | /auth/xmlrpc/land.php | 
+----+--------+--------------+-------------------------+-----------------------+
2 rows in set (0.00 sec)

mysql> SELECT id, deleted, wwwroot, ip_address , name, transport, portno, last_connect_time, applicationid from mnet_host;
+----+---------+------------------------------------+-------------+-----------+-----------+--------+-------------------+---------------+
| id | deleted | wwwroot                            | ip_address  | name      | transport | portno | last_connect_time | applicationid |
+----+---------+------------------------------------+-------------+-----------+-----------+--------+-------------------+---------------+
|  2 |       0 | http://redacted.sch.uk | 172.28.8.21 |           |         0 |      0 |                 0 |             1 | 
| 12 |       0 |                                    |             | All Hosts |         0 |      0 |                 0 |             1 | 
+----+---------+------------------------------------+-------------+-----------+-----------+--------+-------------------+---------------+
2 rows in set (0.28 sec)
Show
Dan Poltawski added a comment - But this is a different moodle (where the moodle application id is not 1, and also I think is basically never used) 
mysql> select * from mnet_application;
+----+--------+--------------+-------------------------+-----------------------+
| id | name   | display_name | xmlrpc_server_url       | sso_land_url          |
+----+--------+--------------+-------------------------+-----------------------+
|  2 | moodle | Moodle       | /mnet/xmlrpc/server.php | /auth/mnet/land.php   | 
| 12 | mahara | Mahara       | /api/xmlrpc/server.php  | /auth/xmlrpc/land.php | 
+----+--------+--------------+-------------------------+-----------------------+
2 rows in set (0.00 sec)

mysql> SELECT id, deleted, wwwroot, ip_address , name, transport, portno, last_connect_time, applicationid from mnet_host;
+----+---------+------------------------------------+-------------+-----------+-----------+--------+-------------------+---------------+
| id | deleted | wwwroot                            | ip_address  | name      | transport | portno | last_connect_time | applicationid |
+----+---------+------------------------------------+-------------+-----------+-----------+--------+-------------------+---------------+
|  2 |       0 | http://redacted.sch.uk | 172.28.8.21 |           |         0 |      0 |                 0 |             1 | 
| 12 |       0 |                                    |             | All Hosts |         0 |      0 |                 0 |             1 | 
+----+---------+------------------------------------+-------------+-----------+-----------+--------+-------------------+---------------+
2 rows in set (0.28 sec)
Hide
Permalink
Penny Leach added a comment -

ok, I'm confused...

The first one - I think the first entry is localhost eh - does id 1 match $CFG->mnetlocalhostid or whatever is called?
The second entry is the pseudo "all hosts" host, which I don't know anything about, and the third one is obviously mahara.

The second moodle is bizarre though.

Show
Penny Leach added a comment - ok, I'm confused... The first one - I think the first entry is localhost eh - does id 1 match $CFG->mnetlocalhostid or whatever is called? The second entry is the pseudo "all hosts" host, which I don't know anything about, and the third one is obviously mahara. The second moodle is bizarre though.
Hide
Permalink
Penny Leach added a comment -

Is one of these a 1.9 fresh install and the other was upgraded from earlier ? Maybe the problem is the upgrade that creates the application table and migrates the host records to it

Show
Penny Leach added a comment - Is one of these a 1.9 fresh install and the other was upgraded from earlier ? Maybe the problem is the upgrade that creates the application table and migrates the host records to it
Hide
Permalink
Dan Poltawski added a comment -

No.

They are probably both upgraded. We use non-linear autoincrementing ids though (mysql, dont ask), so my guess is that the first id is hardcoded to 1 but no in db?

Show
Dan Poltawski added a comment - No. They are probably both upgraded. We use non-linear autoincrementing ids though (mysql, dont ask), so my guess is that the first id is hardcoded to 1 but no in db?
Hide
Permalink
Penny Leach added a comment -

Dan and I dug into this further, and we found the following:

  • That field has a default of 1 in the database
  • There was an upgrade at some point that inserted records with no value in that field:

commit 02caf1e79996aa91b186e3b88e60593a8a61391e
Author: stronk7 <stronk7>
Date: Tue May 12 00:21:28 2009 +0000

MDL-16879 fix mnet hosts - upgrade_fix_incorrect_mnethostids() merged to HEAD

Show
Penny Leach added a comment - Dan and I dug into this further, and we found the following:
  • That field has a default of 1 in the database
  • There was an upgrade at some point that inserted records with no value in that field:
commit 02caf1e79996aa91b186e3b88e60593a8a61391e Author: stronk7 <stronk7> Date: Tue May 12 00:21:28 2009 +0000 MDL-16879 fix mnet hosts - upgrade_fix_incorrect_mnethostids() merged to HEAD
Hide
Permalink
Penny Leach added a comment -

I guess maybe the solution is to

  • fix upgrade_fix_incorrect_mnethostids
  • add a new block in there to set 'moodle' as the application for both local and all hosts records
  • write a new block in ugrade.php to call this function again

and er ... now that we're thinking about it "all hosts" isn't going to work with the fact that we now have different applications ...

Show
Penny Leach added a comment - I guess maybe the solution is to
  • fix upgrade_fix_incorrect_mnethostids
  • add a new block in there to set 'moodle' as the application for both local and all hosts records
  • write a new block in ugrade.php to call this function again
and er ... now that we're thinking about it "all hosts" isn't going to work with the fact that we now have different applications ...
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

Uhm... crap. That function was added because of MDL-16879. I think I discussed about that field and the default was good enough or so, grrr.

I any case we need to:

1) Change upgrade_fix_incorrect_mnethostids() so, it will get the application->id with name='moodle' and use that id in the inserts
2) Add one more step in upgrade so both localhost and allhosts records get fixed if they aren't pointing to the correct application->id

I hope that record name='moodle' will always exist correct? Always!

You, me? Ciao

Show
Eloy Lafuente (stronk7) added a comment - Uhm... crap. That function was added because of MDL-16879. I think I discussed about that field and the default was good enough or so, grrr. I any case we need to: 1) Change upgrade_fix_incorrect_mnethostids() so, it will get the application->id with name='moodle' and use that id in the inserts 2) Add one more step in upgrade so both localhost and allhosts records get fixed if they aren't pointing to the correct application->id I hope that record name='moodle' will always exist correct? Always! You, me? Ciao
Hide
Permalink
Penny Leach added a comment -

I think we're agreeing on what needs to happen to fix it, yes?

I'm happy to do it. I guess we need to do it in 1.9 and 2.0?

Show
Penny Leach added a comment - I think we're agreeing on what needs to happen to fix it, yes? I'm happy to do it. I guess we need to do it in 1.9 and 2.0?
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

yes and yes. Thanks!

Show
Eloy Lafuente (stronk7) added a comment - yes and yes. Thanks!
Hide
Permalink
Penny Leach added a comment -

dan, can you try this patch against the tip of 1.9?

it runs clean for me but i haven't got a broken db really to test it on (other than randomly breaking data)

Show
Penny Leach added a comment - dan, can you try this patch against the tip of 1.9? it runs clean for me but i haven't got a broken db really to test it on (other than randomly breaking data)
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

looks perfect IMO (the only point is the error_log() line... sent always?)

Ciao

Show
Eloy Lafuente (stronk7) added a comment - looks perfect IMO (the only point is the error_log() line... sent always?) Ciao
Hide
Permalink
Penny Leach added a comment -

yeah I removed that already on my branch but forgot to regenerate the diff. Thanks Eloy!

Show
Penny Leach added a comment - yeah I removed that already on my branch but forgot to regenerate the diff. Thanks Eloy!
Hide
Permalink
Penny Leach added a comment -

eloy please qa & close

Show
Penny Leach added a comment - eloy please qa & close

People

Vote (0)
Watch (3)

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.5#665-sha1:422aead) | Report a problem
Powered by a free Atlassian JIRA open source license for Moodle. Try JIRA - bug tracking software for your team.