04 June 2009, 0:56

Hi Nigel,

 Just for your info, I tried something which might give you more "thoughts."  I confess that's my fault to set the default collation to swedish.. but I guess it's not the core problem upgrading from 1.0.x to 1.1.x...

1) I installed brand new 1.0.9 with latin1_swedish_ci collation on a test server with default records, then upgrade to 1.1.3 = no problem, it went so smooth.

2) I did change all tables from swedish_ci to utf8_unicode_ci by some scripts on the web, it still no good when I try to alter some tables like artefacts

ALTER TABLE `artefact` CHANGE `artefacttype` `artefacttype` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci  NOT NULL; 

And it comes out this error

#1025 - Error on rename of './mahara/#sql-2e19_a7c' to './mahara/artefact' (errno: 150) 

Then, I try to backup the database from phpmyadmin with "default" check boxes, another error

INSERT INTO `backup2`.`usr`
SELECT *
FROM `mahara`.`usr`

MySQL said: Documentation

#1062 - Duplicate entry '1' for key 1

Then, I have to check "Add AUTO_INCREMENT value" and works.

3)  Then, I execute the same ALTER table SQL on backup2...

 ALTER TABLE `artefact` CHANGE `artefacttype` `artefacttype` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci  NOT NULL; 

 It works this time!

 4) So, I replace the "mahara" database by "backup2", then try to upgrade from 1.0.9 to 1.1.3 = fail (I also tried 1.0.9 to 1.0.11 = okay, but 1.0.9 to 1.1.0 = fail)  These are the error from the "core" section:

Could not execute command: ALTER TABLE "artefact" ADD CONSTRAINT "arte_ins_fk" FOREIGN KEY (institution) REFERENCES "institution"(name);

Call stack (most recent first):

• execute_sql("ALTER TABLE {artefact} ADD CONSTRAINT {arte_ins_fk...") at /var/www/mahara/lib/db/upgrade.php:256
• xmldb_core_upgrade("2008040218") at /var/www/mahara/lib/upgrade.php:271
• upgrade_core(object(stdClass)) at /var/www/mahara/admin/upgrade.json.php:71

 With another "reload", these are popping out: 

 Could not execute command: ALTER TABLE "group" ADD grouptype CHARACTER VARYING(20)

Call stack (most recent first):

• execute_sql("ALTER TABLE {group} ADD grouptype CHARACTER VARYIN...") at /var/www/mahara/lib/db/upgrade.php:165
• xmldb_core_upgrade("2008040218") at /var/www/mahara/lib/upgrade.php:271
• upgrade_core(object(stdClass)) at /var/www/mahara/admin/upgrade.json.php:71

So, do you think there are something "miss" placed inside some tables and caused the upgrade problem?

Is there anyway to check the correctless of the database? 

Is there any "SQL" script to do the table upgrade instead of running upgrade.php? 

Thanks for your great help on this.

Wilson. 

 [edited by Nigel to remove reference to image behind password protected server]

04 June 2009, 22:57

Hi, looks like you had a bit of an exploration mission

When you see errors like this:

#1025 - Error on rename of './mahara/#sql-2e19_a7c' to './mahara/artefact' (errno: 150)

Then you should run SHOW ENGINE INNODB STATUS; and have a look at the "LAST FOREIGN KEY ERROR" section, that will give you the exact reason why it failed.

You might even be able to do this when you try your Mahara upgrades. E.g., run the upgrade, and after it crashes, run that SHOW ENGINE command in the mysql shell and see if you get access to the error. That might not work if the errors are shown per database connection though.

 When you have that situation where you upgrade on a clean database and get one error, then future upgrades cause a different error, that is because the first upgrade did half of the upgrade before it crashed (trying to add that foreign key), and MySQL does not roll back schema changes. So that second error where the grouptype column can't be added is because MySQL already added the column and so can't do it again.

The way to get back to the original error is to restore the database from back up each time the upgrade fails.

The only way to upgrade the database is through upgrade.php for now, there's no script for it. The actual commands are in lib/db/upgrade.php. Maybe you would have luck if you commented out the part that adds that foreign key, then after the upgrade you could try running that command manually? It's on line 256 of lib/db/upgrade.php.

05 June 2009, 3:25

Hi Nigel,

 Thanks! And as you suggested,do you have any idea how to fix this?

Wilson.

------------------------
LATEST FOREIGN KEY ERROR
------------------------
090605 16:23:38 Error in foreign key constraint of table eportfolio/#sql-2e19_cd9:
 FOREIGN KEY (institution) REFERENCES "institution"(name):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

 

07 June 2009, 23:40

17 June 2009, 22:27

Hi Nigel,

I got stuck with server mirgration and didn't have time to try to upgrade Mahara until now!  Yes, the upgrade went smooth after comment out line 256 as you suggested.  If anyone has my problem, please make sure your "institution column "collation" in artefact table = "name" in institution table.  Then execute this SQL statment.  My case, I have to force the institution to "latin1_swedish_ci", and it rocks!

ALTER TABLE artefact ADD CONSTRAINT arte_ins_fk FOREIGN KEY (institution) REFERENCES institution(name);

 Thanks Nigel!

Wilson.

17 June 2009, 23:11

Awesome, glad it works for you now!

Hopefully there won't be so many problems in future. I don't think the Mahara 1.2 upgrade is very big, for example.