20 July 2016, 1:09

Command line upgrade is failing with the following....

This is using MySQL...

 

[DBG] 92 (lib/db/upgrade.php:4374) block_instance move id = 164110 -1 * -2
[DBG] 92 (lib/db/upgrade.php:4374) block_instance move id = 164109 -1 * -1
[DBG] 92 (lib/db/upgrade.php:4374) block_instance move id = 164108 -1 * 0
[DBG] 92 (lib/db/upgrade.php:4379) block_instance true order id = 164110 +1 0
[DBG] 92 (lib/dml.php:157) mysqli error: [1062: Duplicate entry '34914-1-1-1' for key 'blocinst_vierowcolord_uk'] in EXECUTE("UPDATE "block_instance" SET "order" = 1 WHERE id = '164110'")
[DBG] 92 (lib/dml.php:157) Command was: UPDATE "block_instance" SET "order" = ? WHERE id = ?
[WAR] 92 (lib/errors.php:796) Could not execute command: UPDATE "block_instance" SET "order" = ? WHERE id = ?
Call stack (most recent first):
 * log_message("Could not execute command: UPDATE "block_instance"...", 8, true, true) at /var/www/mahara16/htdocs/lib/errors.php:95
 * log_warn("Could not execute command: UPDATE "block_instance"...") at /var/www/mahara16/htdocs/lib/errors.php:796
 * SQLException->__construct("Could not execute command: UPDATE "block_instance"...") at /var/www/mahara16/htdocs/lib/dml.php:158
 * execute_sql("UPDATE "block_instance" SET "order" = ? WHERE id =...", array(size 2)) at /var/www/mahara16/htdocs/lib/db/upgrade.php:4380
 * xmldb_core_upgrade("2015030421") at /var/www/mahara16/htdocs/lib/upgrade.php:342
 * upgrade_core(object(stdClass)) at /var/www/mahara16/htdocs/lib/mahara.php:265
 * upgrade_mahara(array(size 11)) at /var/www/mahara16/htdocs/admin/cli/upgrade.php:50

[WAR] 92 (lib/dml.php:158) Could not execute command: UPDATE "block_instance" SET "order" = ? WHERE id = ?
Call stack (most recent first):
 * execute_sql("UPDATE "block_instance" SET "order" = ? WHERE id =...", array(size 2)) at /var/www/mahara16/htdocs/lib/db/upgrade.php:4380
 * xmldb_core_upgrade("2015030421") at /var/www/mahara16/htdocs/lib/upgrade.php:342
 * upgrade_core(object(stdClass)) at /var/www/mahara16/htdocs/lib/mahara.php:265
 * upgrade_mahara(array(size 11)) at /var/www/mahara16/htdocs/admin/cli/upgrade.php:50

The additional debugging lines are my own.

This refers to the bit of upgrade code (lib/db/upgrade.php) entitled 'Sorting out block_instance sort order drift'. I'm not convinced it's working but I'm not sure why. 

The 'move' debug line just before the error was this...

 foreach ($blocks as $key => $block) {
 // First move them out of the way to avoid uniqueness clash
 log_debug('block_instance move id = ' . $block['id'] . ' -1 * ' . $block['order']);
 execute_sql('UPDATE {block_instance} SET "order" = ? WHERE id = ?', array(($block['order'] * -1), $block['id']));
 }

I am guessing that the idea is to make the column value negative before changing it to the correct value. However, the log line implies that $block['order'] was *already* negative for ids 164109 and 164110, thus making them positive hence the uniqueness clash at the next stage. 

At the point of failure, the relevant database records for this are (note the positive column values that - I think - should be at their temporary negative values)...

select * from block_instance where view=34914;
+--------+--------------+--------------------------------------+---------------------------------------+-------+--------+-------+-----+
| id | blocktype | title | configdata | view | column | order | row |
+--------+--------------+--------------------------------------+---------------------------------------+-------+--------+-------+-----+
| 164108 | textbox | Brief Description | a:1:{s:10:"artefactid";s:6:"215516";} | 34914 | 1 | 0 | 1 |
| 164109 | textbox | Intended Learning Outcomes Satisfied | a:1:{s:10:"artefactid";i:215517;} | 34914 | 1 | 1 | 1 |
| 164110 | textbox | Reflective Commentary | a:1:{s:10:"artefactid";i:215518;} | 34914 | 1 | 2 | 1 |
| 164107 | textbox | | a:1:{s:10:"artefactid";i:215515;} | 34914 | 2 | 0 | 1 |
| 164523 | filedownload | File(s) to download | a:0:{} | 34914 | 2 | 1 | 1 |
| 164522 | folder | | a:0:{} | 34914 | 2 | 2 | 1 |
| 164521 | folder | | a:0:{} | 34914 | 2 | 3 | 1 |
| 164111 | textbox | | a:1:{s:10:"artefactid";s:6:"215519";} | 34914 | 2 | 5 | 1 |
+--------+--------------+--------------------------------------+---------------------------------------+-------+--------+-------+-----+

The failure is caused by the upgrade process trying to modify id=164110 with view-34914, column=1, oder=1, row=1 which already exists in id=164109. This causes a fatal error due to the unique key constraining these fields. 

I'm probably doing something stupid, but running the bit of SQL this routine uses by hand...

SELECT b.id, b.view, b.row, b.column, b.order, maxorder, countorder

    FROM block_instance b

    JOIN (SELECT view AS sview, "row" AS srow, "column" AS scol, COUNT("order") AS countorder, MAX("order") AS maxorder

    FROM block_instance GROUP BY view, "row", "column") AS myview

    ON myview.sview = b.VIEW AND myview.srow = b.row AND myview.scol = b.column

    WHERE maxorder != countorder

    ORDER BY b.view, b.row, b.column, b.order

 

...always returns an empty set. 

Any insight appreciated. 

20 July 2016, 2:07

Just discovered in the *before* version of the database (obvious when you think about it)...

select id,view,`column`,`order`,`row` from block_instance where `order`<0;
+--------+-------+--------+-------+-----+
| id | view | column | order | row |
+--------+-------+--------+-------+-----+
| 164110 | 34914 | 1 | -2 | 1 |
| 164109 | 34914 | 1 | -1 | 1 |
+--------+-------+--------+-------+-----+

So, those 'order' columns were negative to start with. That wouldn't help. I have no idea how that came about, but it might be worth updating that code to check just in case I am not unique in this respect.

21 July 2016, 13:02

Hi Howard,

I've seen this before with another client who is using mysql. I thought they had some some weird and wacky stuff with their data.

We customised their upgrade script (/htdocs/lib/db/upgrade.php  from line 4215) -  basically dropped the index and reworked the order/sorting and then recreated the index.

It's a bit dangerous because we're dropping and recreating the index. But, it worked for their data.

You can give it a try:

    if ($oldversion < 2015092915) {
        log_debug('Sorting out block_instance sort order drift');
        // There was an issue with the sorting of blocks (Bug #1523719) that existed since
        // Sept 2007, commit 02fb5d96 where the max order number does not equal the number
        // of blocks in the cell
        set_time_limit(120);
        if ($results = get_records_sql_array('SELECT b.id, b.view, b.row, b.column, b.order, maxorder, countorder
                                              FROM {block_instance} b
                                              JOIN (SELECT view AS sview, "row" AS srow, "column" AS scol, COUNT("order") AS countorder, MAX("order") AS maxorder
                                                  FROM {block_instance} GROUP BY view, "row", "column") AS myview
                                                ON myview.sview = b.VIEW AND myview.srow = b.row AND myview.scol = b.column
                                              WHERE maxorder != countorder
                                              ORDER BY b.view, b.row, b.column, b.order', array())) {
            // Structure the info into a more usable format
            $updates = array();
            foreach ($results as $r) {
                $updates[$r->view][$r->row][$r->column][] = array('order' => $r->order, 'id' => $r->id);
            }
            // customisation: start.
            if (is_mysql()) {
                // Drop index to faciliate the ordering.
                execute_sql("alter table {block_instance} drop index {blocinst_vierowcolord_uk}");
            }
            // customisation: end.

            // Now deal with the results
            foreach ($updates as $view => $grid) {
                foreach ($grid as $row => $columns) {
                    foreach ($columns as $column => $blocks) {
                        // First move them out of the way to avoid uniqueness clash
                        // customisation: start.
                        execute_sql('UPDATE {block_instance} SET "order" = "order" * -1 WHERE view = ?', array($view));
                        $counter = 0;
                        foreach ($blocks as $key => $block) {
                            // Then update them with true order
                            execute_sql('UPDATE {block_instance} SET "order" = ? WHERE id = ?', array($counter, $block['id']));
                            $counter++;
                        }
                        // customisation: end.
                    }
                }
                set_time_limit(30);
            }
            // customisation: start.
            if (is_mysql()) {
                // Now recreating index again.
                $table = new XMLDBTable('block_instance');
                $key = new XMLDBKey('viewrowcolumnorderuk');
                $key->setAttributes(XMLDB_KEY_UNIQUE, array('view', 'row', 'column', 'order'));
                add_key($table, $key);
            }
            // customisation: end.
        }
    }

 

Cheers,

Ghada

 

21 July 2016, 20:45

Thanks for that (and for letting me know I'm not the only one)

As it was only two records in my case, I just corrected them and the upgrade completed.

 

Now to fix all the schema differences (between the upgraded version and a clean install). Running Mahara on MySQL feels like a constant battle sometimes but our server people don't support PostgreSQL :(

21 July 2016, 22:07

Hi Howard,

Technically, there shouldn't be a difference between postges and mysql in Mahara - in a  perfect world.

But we know better ;-)

If you do find discrepancies, please let us know by filling out a bug. And if you're so inclined, submit a fix upstream. We would be very grateful!

Cheers,
Ghada