External feed has duplicate rows causing problems

[Robert Lyon]

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)]

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)
                break;
            }
        }
    }
}

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)]

https://reviews.mahara.org/#/c/2760/

[Mahara Bot (dev-mahara)]

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>

[Mahara Bot (dev-mahara)]

Patch for "master" branch: https://reviews.mahara.org/3438

[Aaron Wells (u-aaronw)]

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)]

Commit: 4b39ed5d22df0c461d6919f24a29f475cdcc2e0b

[Mahara Bot (dev-mahara)]

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