External feed has duplicate rows causing problems

Bug #1239461 reported by Robert Lyon on 2013-10-13
This bug affects 1 person
Affects Status Importance Assigned to Milestone

Bug Description

Older sites can end up with multiple rows in the blocktype_externalfeed_data due to an earlier (now fixed) problem

This causes problem when one tries to add a new rss feed block to a page if there is more than one row in the db of the same feed url.

[WAR] bc (lib/errors.php:752) get_record_sql found more than one row. If you meant to retrieve more than one record, use get_records_*, otherwise check your code or database for inconsistencies
Call stack (most recent first):

    log_message("get_record_sql found more than one row. If you mea...", 8, true, true) at /var/www/myportfolio-school2-testing-mahara/releases/20131010191940/lib/errors.php:109
    log_warn("get_record_sql found more than one row. If you mea...") at /var/www/myportfolio-school2-testing-mahara/releases/20131010191940/lib/errors.php:752
    SQLException->__construct("get_record_sql found more than one row. If you mea...") at /var/www/myportfolio-school2-testing-mahara/releases/20131010191940/lib/dml.php:347
    get_record_sql("SELECT * FROM "blocktype_externalfeed_data" WHERE ...", array(size 3)) at /var/www/myportfolio-school2-testing-mahara/releases/20131010191940/lib/dml.php:365
    get_record_select("blocktype_externalfeed_data", ""url" = ? AND "authuser" = ? AND "authpassword" ...", array(size 3)) at /var/www/myportfolio-school2-testing-mahara/releases/20131010191940/lib/dml.php:1143
    ensure_record_exists("blocktype_externalfeed_data", array(size 3), object(stdClass), "id", true) at /var/www/myportfolio-school2-testing-mahara/releases/20131010191940/blocktype/externalfeed/lib.php:274
    PluginBlocktypeExternalfeed::instance_config_save(array(size 9), object(BlockInstance)) at Unknown:0

To sort this out we are going to need a script in the the lib/db/upgrade.php to handle the clearing out of the duplicate rows and making sure the block instance configdata now points to the 1 row left in the db.

Robert Lyon (robertl-9) wrote :

Thoughts on sorting this out - will need a script to do:

Step 1: Find the duplicate feed urls: (1 db call)

SELECT COUNT(url), url, authuser, authpassword FROM {blocktype_externalfeed_data} GROUP BY url, authuser, authpassword HAVING COUNT(url) > 1 ORDER BY COUNT(url);

step 2: get the feed ids and use the 1st one to be the feed id for the block instances: (many db calls depending on how many rows from step 1 have duplicates)

SELECT id FROM {blocktype_externalfeed_data} WHERE url = ?, authuser = ?, authpassword = ?;

- add the ids to an $array[$duplicatefeed]['ids'][] and use 'ids'[0] as the 'true' id

step 3: Find the block instances using external feeds. (1 db call). Check to see if they are not using the 'true' id and update them accordingly:

SELECT id FROM {block_instance} WHERE blocktype = 'externalfeed';

- get block_instance object and get $blockfeedid from object
$blockinstance = new BlockInstance($blockid);
$configdata = $blockinstance->get('configdata');
if (!empty($configdata['feedid'])) {
    foreach ($array as $duplicatefeed => $ids) {
        foreach ($ids as $key => $id) {
            if ($id == $configdata['feedid'] && $key != '0') {
         // not using the first instance id of this feed
                $configdata['feedid'] = $ids[0];
  $blockinstance->set('dirty') = true;
                $blockinstance->commit(); (possibly very many db calls)

step 4: delete the now orphaned feed id(s) rows

foreach ($array as $duplicatefeed => $ids) {
    foreach ($ids as $key => $id) {
        if ($key != '0') {
            DELETE FROM {blocktype_externalfeed_data} WHERE id = ? (possibly very many db calls)

Currently I'm thinking that this approach may use too many db calls. But not sure how to get feedid from configdata column in an easier way. Maybe a preg_match on the serialized data?

Robert Lyon (robertl-9) wrote :
Changed in mahara:
status: New → In Progress
Aaron Wells (u-aaronw) on 2013-12-17
Changed in mahara:
milestone: 1.8.1 → 1.8.2

Reviewed: https://reviews.mahara.org/2760
Committed: http://gitorious.org/mahara/mahara/commit/887d5deb3cde9fb5b19a1cf4acc1f1df689b0f7d
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit 887d5deb3cde9fb5b19a1cf4acc1f1df689b0f7d
Author: Robert Lyon <email address hidden>
Date: Mon Dec 2 13:53:18 2013 +1300

Removing duplicate feed url rows (bug #1239461)

Older sites can end up with duplicate rows in the
blocktype_externalfeed_data for the same url. This causes problems if
the site is using latest mahara as it stops one being able to add
/edit a feed if there are duplicate rows for that feed's url.

Change-Id: I47ee3952dbe75b5643adf55105a9219a3e3e8cd9
Signed-off-by: Robert Lyon <email address hidden>
Signed-off-by: Aaron Wells <email address hidden>

Robert Lyon (robertl-9) on 2014-04-01
Changed in mahara:
status: In Progress → Fix Committed
Robert Lyon (robertl-9) on 2014-04-03
Changed in mahara:
status: Fix Committed → Fix Released
Aaron Wells (u-aaronw) on 2014-04-15
Changed in mahara:
status: Fix Released → Fix Committed
milestone: 1.8.2 → 1.9.0
Robert Lyon (robertl-9) on 2014-04-22
Changed in mahara:
status: Fix Committed → Fix Released
Aaron Wells (u-aaronw) wrote :

Another patch for this one which accidentally got pushed directly to the 1.9_STABLE branch (back in April) during the 1.9.0 release process. Consequently it didn't get into gerrit, and didn't get forward-ported to master.

Aaron Wells (u-aaronw) wrote :

Commit: 4b39ed5d22df0c461d6919f24a29f475cdcc2e0b

Reviewed: https://reviews.mahara.org/3438
Committed: http://gitorious.org/mahara/mahara/commit/4adb421b011ee4b43deb5957feaff3d7ca0ee6e0
Submitter: Aaron Wells (<email address hidden>)
Branch: master

commit 4adb421b011ee4b43deb5957feaff3d7ca0ee6e0
Author: Aaron Wells <email address hidden>
Date: Tue Apr 15 16:50:23 2014 +1200

External feed block upgrade should be in the block's upgrade script

Bug 1239461

Change-Id: Ib574072c06aec2c12bb113f2c2429bd733aebcc6

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers