19 September 2010, 20:42

Hi,

I get the following error on first upgrade attempt:

Could not execute command: ALTER TABLE notification_internal_activity ADD CONSTRAINT notiinteacti_fro_fk FOREIGN KEY (`from`) REFERENCES usr (id)

Call stack (most recent first):

• execute_sql() at /124testup/13/lib/dml.php:1406
• execute_sql_arr() at /124testup/13/lib/ddl.php:972
• add_key() at /13/lib/db/upgrade.php:1702
• xmldb_core_upgrade("ALTER TABLE notification_internal_activity ADD CON...", true) at /124testup/13/lib/upgrade.php:301
• upgrade_core(array(size 2), true, true) at /124testup/13/admin/upgrade.json.php:93

Something wrong at my end or in the upgrade code?

20 September 2010, 0:05

It could be something wrong in the upgrade code.

If you could retrieve a MySQL error code out of your error log, that might give me a better idea of why your MySQL doesn't want to create that foreign key.

Alternatively, just try pasting that ALTER TABLE command into your mysql client and see what it says.

20 September 2010, 0:14

Steve,

It would be good to know which version of MySQL you're using and maybe also the output from "SHOW VARIABLES".  I haven't been able to reproduce this error on 5.0.32.

20 September 2010, 0:43

I installed a fresh 1.2.4 and got the same error.

#1072 - Key column 'from' doesn't exist in table

Server version 5.1.50

Output from SHOW VARIABLES: http://pastie.org/1169472

21 September 2010, 0:23

Nothing springs out at me I'm afraid Steve.  I did try the 1.2.4 -> 1.3.1 upgrade on a newer version (the MySQL 5.1.49-1 in Debian lenny) but couldn't reproduce the problem there either.

If you're interested in trying to debug this at all, it all happens at line 1702 of lib/db/upgrade.php, but it's complicated by the fact that the add_field and add_key functions do all the SQL generation, so you'd need to go into those functions if you want to see what commands are being run.  For me, it's doing this:

ALTER TABLE notification_internal_activity ADD parent BIGINT(10) DEFAULT NULL
ALTER TABLE notification_internal_activity ADD CONSTRAINT notiinteacti_par_fk FOREIGN KEY (parent) REFERENCES notification_internal_activity (id)
CREATE INDEX notiinteacti_par_ix ON notification_internal_activity (parent)

ALTER TABLE notification_internal_activity ADD `from` BIGINT(10) DEFAULT NULL
ALTER TABLE notification_internal_activity ADD CONSTRAINT notiinteacti_fro_fk FOREIGN KEY (`from`) REFERENCES usr (id)
CREATE INDEX notiinteacti_fro_ix ON notification_internal_activity (`from`)

So I guess it'd be interesting to know what happens if you try to run those commands in your MySQL client on your 1.2.4 db.  For me they all succeed, but in your case it seems that the 5th one is failing, and claiming there is no 'from' column on the table when that column should have been created by the 4th command.

21 September 2010, 19:15

Thanks for following up, Richard.

I've been able to complete a successful test upgrade by emptying the contents of notification_internal_activity, so there must be some data corrupting it. Not sure where to start looking for it though, as there are over 1 million records in it. I wasn't previously aware that corrupt data could interfere with alterations to the structure of a table.

Interestingly, on my last failed upgrade attempt, the failure was at ADD 'from' BIGINT(10) DEFAULT NULL

21 September 2010, 20:56

Right, that error on your last attempt does make more sense considering the earlier one you reported was complaining that the from column didn't exist.

Have you got an error number and message for this latest one?

21 September 2010, 23:19

When I run the ADD query by itself it executes successfully, but takes 70+ seconds to complete. So I haven't got an error number/message to give you.

Is it possible that the size of my notification_internal_activity table is so big that it causes some sort of time-out when the upgrade script is run?

22 September 2010, 17:40

Yeah I think that's definitely possible, but it's a bit weird that you're getting the SQL error in the log complaining about the missing column.  That makes me think that increasing the php script timeout won't help, because the next command is getting run anyway.  It's worth trying though - just edit lib/db/upgrade.php, and increase the max_execution_time at the very top.  If that doesn't help, try upping the memory limit on the next line.

I do also wonder what happens if a Mysql timeout gets exceeded too - it could be that these aren't being returned properly to php or more likely that they aren't being caught & displayed properly by mahara.

We'll also have to look at pruning that table a bit by default I think - it's probably mostly junk that we don't need to keep around.  We can certainly wipe out the old forum post notifications because everything in them is reachable from elsewhere on the site.  Some of the other message types like feedback, watchlist, institution message are also pretty useless once they've got a bit old.