19 December 2013, 14:27

Hi

We have been able to successfully upgrade from 1.6.3 to 1.8rc1 on a LAMP server.

However, now that we are ready to put 1.8.1 into production, we've run into problems. First we tried upgrading 1.6.3 to 1.8.1 and it failed. So after rolling back to 1.6.3 we went successfully to 1.8.0 and then tried upgrading to 1.8.1. This was the result:

(http://s9.postimg.org/4sv56vlsv/mahara181_upgrade_error.jpg)

Any ideas out there? 

20 December 2013, 13:14

Hi Steve,

The error you're seeing is in a piece of code that is meant to add indexes and keys that may be missing from older Mahara sites due to past bugs in the upgrade process. These indexes and keys are not essential to Mahara's operation, but the site will be more robust with them.

Would you mind answering a couple of questions for me, which may help to determine the cause of this bug?

1. What version of MySQL are you using?

2. What version of Mahara was your site originally installed as?

Anyway, the statement you're erroring out on, is for the creation of a foreign key.  Unfortunately your error message doesn't say exactly why it's erroring out (just "Could not execute command") so I can't say for certain how to make this statement work. But since the site will continue to run even without the foreign key being present, you could resolve the problem by simply commenting out this statement -- lines 2691 through 2695 in lib/db/upgrade.php:

        $key = new XMLDBKey('grouptypefk');
        $key->setAttributes(XMLDB_KEY_FOREIGN, array('grouptype'), 'grouptype', array('name'));
        if (!db_key_exists($table, $key)) {
            add_key($table, $key);
        }

Actually... I suppose the most likely reason why you'd be having a failure to add a foreign key is because the foreign key relationship between the two tables is invalid. Would you mind running this SQL query for me, in your database? If the result returns any rows, it would indicate this is the problem.

SELECT g.grouptype, count(*) FROM `group` g WHERE NOT EXISTS (SELECT 1 FROM `grouptype` gt WHERE g.grouptype = gt.name) GROUP BY g.grouptype;

Cheers,

Aaron

23 December 2013, 14:22

Thank you Aaron.

I will have to wait for our analyst to look into this and get back to you, regarding your questions and proposed solution...probably in the new year.

We have been using Mahara since 0.9 days, and this current issue is most likely related to this one:

https://mahara.org/interaction/forum/topic.php?id=5770

Cheers,

Steve.

24 December 2013, 14:02

Hi Steve,

It's related to the issue you linked to, in that they both have to do with "schema drift" between older Mahara sites and newer ones.

I've put together a patch to make it so that the upgrade script won't error out if the missing constraint can't be added. It's still in review at the time I'm writing this: https://reviews.mahara.org/#/c/2833/

Cheers,

Aaron

14 January 2014, 13:08

(duplicate response)

14 January 2014, 13:10

Hi Aaron

Our analyst/programmer (Jacqui) ran the query and it came back with zero rows.

We'll try the upgrade again now that your fix has been merged.

Thanks again.

Steve.

14 January 2014, 14:12

P.S.

1. MySQL version is 5.1.61

2. We've had our current Mahara since version 1.0

14 January 2014, 14:24

Hi steve,

The patch actually had only been merged into the branch for 1.9dev so far. I've just this minute backported it to 1.8_STABLE as well. So, if you pull the code from our git repository you'll have the updated version of it, otherwise you'll need to wait for the 1.8.2 release.

On the other hand, since that query came back with zero rows, that suggests I may have been incorrect about the cause of your problem, in which case this patch won't help anyhow.

Cheers,

Aaron

14 January 2014, 14:41

I noticed that mysql can fail on execution of an adding foreign key if the two columns being connected are not exactly the same type

eg if one column is int(11) and the other is int(10) then the adding of a foreign key will fail

Check to make sure that the two columns you are trying to connect are of the same type, it will at least eliminate one possible problem.

14 January 2014, 18:39

Thanks Robert, but I've had a look and both of the columns are varchar(20), eliminating this as a possible cause.