14 January 2014, 19:42

Hi 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

21 January 2014, 12:33

Hi Aaron,

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

22 January 2014, 11:08

Hi Jacqui,

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

28 January 2014, 15:47

Thanks 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”:

• artefact_access_role
• artefact_access_usr
• grouptype_roles
• view_autocreate_grouptype

Kind Regards,

Jacqui.

29 January 2014, 17:02

Hi Jacqui,

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

23 January 2014, 17:05

Hi 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?

27 January 2014, 11:48

Hi 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

28 January 2014, 12:16

Thanks Aaron, that's a relief! 

We'll keep working on it here, and post back any resolution we find...

Cheers,

Steve.

30 January 2014, 10:48

Hi Aaron

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?