30 May 2012, 13:51

I'm getting the following error when upgrading the database:

Could not execute command: ALTER TABLE `group` ADD CONSTRAINT grou_ins_fk FOREIGN KEY (institution) REFERENCES institution (name)

This occurs when updating the core.  I'm not really a systems programmer so don't know where to start on this one.

I am using the config file from my prior running version (1.4.1)

Any ideas on how I should proceed?

30 May 2012, 14:31

Actually, after reloading the old database into mysql I ran the update again and recalled that my first error message was:

A nonrecoverable error occured. This probably means you have encountered a bug in the system

I get the prior reported message when I try to run the update again.  So I think my problem is deeper than what I originally thought.

Any thoughts on how to proceed?

30 May 2012, 16:26

Hi Bart,

Restore your old database, run the upgrade again, and as soon as you see "A nonrecoverable error occurred...", have a look in your webserver log, it should have some useful information about the source of the error.

R.

31 May 2012, 8:59

Hi richard,

I have the same issue on a test environment and its coming up with

Invalid (old?) table or database name '#sql-68c_64'

in the MySQL Error log which is strange. 

and its coming up in the web browser straight to:

Could not execute command: ALTER TABLE `group` ADD CONSTRAINT grou_ins_fk FOREIGN KEY (institution) REFERENCES institution (name)

31 May 2012, 17:13

Hi Richard,

Is that the very first error you see after restoring your old database and running a fresh upgrade?  Bart's comment implies that the SQL error you are getting is on re-running the upgrade after an initial failure.

01 June 2012, 1:15

It says upgrade failed on mine and then when I try and run it again it comes up with that and the message above is the only thing that appears in the MySQL error log. I have tried running it from the client too with a fresh table from the 1.4.2 build and it then says you have encountered a bug in the system.

I only have a problem with an upgrade as a clean install of 1.5.1 works without any problems.

I am using UTF8 general ci

Thanks

Richard

18 June 2012, 11:06

Hi I realised that because there was no errors in the MySQL error log that it must have been timing out. I went into lib/db/upgrade.php

and changed the variables to this:

    ini_set('max_execution_time', 2500); // Let's be safe
    raise_memory_limit('1024M');

Then the upgrade ran smoothly.

20 June 2012, 9:58

Hi Bart
Did you ever get a resolution to this? I also have exactly the same error messages upgrading Mahara 1.4.1 to 1.5.1.

1st - - A nonrecoverable error occured. This probably means you have encountered a bug in the system

then -

Could not execute command: ALTER TABLE `group` ADD CONSTRAINT grou_ins_fk FOREIGN KEY (institution) REFERENCES institution (name)

Our Current PHP version is  5.3.14
MYSQL  5.1.43

We are running Mahara on Windows Server 2008 the when the upgrade fails the following event is logged.
Invalid (old?) table or database name '#sql-684_33' 

I have tried increasing the timeouts and memory limits in upgrade.php.
Current database collation already using UTF8_general_ci.

Spend all day trying to resolve this..now reverting back to 1.4.1

Any advise gratetfully appreciated.
Regards

Kevin 

20 June 2012, 21:43

Hi Kev, the real cause of the problem will be in the error log of your webserver at the same time you see "A nonrecoverable error occurred" on-screen.

The second error "Could not execute command: ALTER TABLE..." is probably caused by trying to run an upgrade on a failed, half-upgraded MySQL database, and is not useful when tracking down the cause of the error.

If for some reason you can't get to your error log to find out more information on your first error, try temporarily adding the following lines into config.php:

$cfg->log_dbg_targets     = LOG_TARGET_SCREEN | LOG_TARGET_ERRORLOG;
$cfg->log_info_targets    = LOG_TARGET_SCREEN | LOG_TARGET_ERRORLOG;
$cfg->log_warn_targets    = LOG_TARGET_SCREEN | LOG_TARGET_ERRORLOG;
$cfg->log_environ_targets = LOG_TARGET_SCREEN | LOG_TARGET_ERRORLOG;

Then restore your old database, and try the upgrade again.

21 June 2012, 9:15

Hi Richard

Many Thanks for the pointers. I have restored database, and added your suggested logging into config.php. I have cleared the Windows eventlogs and run the upgrade again without any success..still exactly the same errors.

I have subsequently upgraded MySLQ to 5.1.59, restored db etc - -Problem Still the same.

According to MYSQL http://dev.mysql.com/doc/refman/5.1/en/error-log.html
On Windows machines "You cannot disable writing information to the Windows Event Log."

But after the failed upgrade the eventlog remains empty which suggests that SQL is not in an error state?? 

I am struggling to get a handle on where the problem actually lies? Is there any logging done within Mahara itself?

Kind Regards

Kev