Thanks again for your help, and a big thanks to Robert Lyon for pointing us in the right direction.
I don't know how we ended up with different table types in our database, but would it be useful to incorporate some checking in upgrade scripts for this sort of thing?
]]>Yes, all the db tables will need to be of the engine type 'InnoDB' as InnoDB allows for foreign keys and relationship contraints.
Cheers
Robert
]]>I got one of our DBA's to have a look at this, and apparently our grouptype table was originally “MyISAM”, both tables needed to be of engine type “InnoDB”.
By changing the engine type the grou_gro_fk foreign key has now been created on the group table.
A question from our DBA is - do the following tables also need to be type “InnoDB”:
Kind Regards,
Jacqui.
]]>
We'll keep working on it here, and post back any resolution we find...
Cheers,
Steve.
]]>Actually that's a different error. Jacqui got an errno 150, and you've got an errno 121. Google tells me that's an error you get in MySQL when trying to create a key that's a duplicate of an existing key, and that makes sense in this scenario. If you did a clean install of 1.8.0, you'll already have the grou_gro_fk foreign key, from installation time. You should only be missing it if you originally installed Mahara as version 1.0.
Cheers,
Aaron
]]>I installed a fresh version of 1.8.0 on MAMP (MySQL 5.5.29) and got the same error (slightly different syntax) when running the ALTER TABLE as Jacqui stated.
Any ideas?
]]>It does sound like a problem with a primary key. I am assuming that you are using a Mysql database.
I've tried to replicate your problem but cannot.
I suggest working through the list here
http://stackoverflow.com/a/9018753/2453407
to see what we can eliminate as possible causes of this problem.
Cheers
Robert
]]>I tried running the query manually as you suggested and got the following error:
Error Code: 1005. Can't create table 'eportfolio.#sql-904_114' (errno: 150)
is this a problem with a primary key?
Thanks,
Jacqui
]]>Well, another thing you could try is just running the ALTER TABLE query manually. That will probably give you a more specific error message about what the problem is.
ALTER TABLE `group` ADD CONSTRAINT grou_gro_fk FOREIGN KEY (grouptype) REFERENCES grouptype (name);
Cheers,
Aaron
]]>]]>
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.
]]>
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
]]>1. MySQL version is 5.1.61
2. We've had our current Mahara since version 1.0
]]>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.
]]>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
]]>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.
]]>
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
]]>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?
]]>