Server Installation problem : Mysql Error 150

Hi Sinus

What version of MySQL is installed?

Alex

Hi again

I've made a small modification to the installer which should give us a bit more information about exactly which statement is failing.

Please click on the following link which will take you to a modified version of the "install.php" file. At the top of the page you'll see a "download file" link. Click that and use the install.php you download to replace the one in the release files and try again.

http://bazaar.launchpad.net/~alexharrington/xibo/installer-sql-errors-more-verbose/annotate/head%3A/server/install.php

Extra output should be generated - specifically the statement number and the SQL statement that's failing.

If you could paste the output in here that would be great.

Cheers

Alex

Hi Alex ,

thanks for your quick answer.

1- I use : - PHP version 5.2.9 and MySQL version 5.1.30

2- Using the new install.php, here is the error message :

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Xibo Installer

Populating the database

Loading from 1.sql .......................................

An error occured populating the database.

MySQL Error:
Error on rename of './example_xibo7/#sql-5b33_6ffdcd' to './example_xibo7/pages' (errno: 150)

SQL executed:
ALTER TABLE `pages` ADD CONSTRAINT `pages_ibfk_1` FOREIGN KEY (`pagegroupID`) REFERENCES `pages` (`pagegroupID`)

Statement number: 39
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thanks

Sorry for the delay in replying... I've been trying all sorts to recreate this problem (without any success).

One thing I wonder... what was the command you used to create the empty database?

Hi Dan,

I created the empty database and new user using the Cpanel.

~~~
Users in xibo7
example_xibo7 (Privileges: ALL PRIVILEGES)
~~~

Thanks

It seems that some of the KEYS are being added but it is failing on a particular key... I'm having a hard time explaining this!

This might seem crude - but I am out of ideas...

Could you edit "install/database/1.sql" and remove lines 574, 575 (the statements you delete should match the one in your error message). Then could you try to run the install again on a clean database.

It it runs OK it would suggest that we have a problem with that specific statement - if not then it would suggest that something about your MySQL install is different to what we expect.

We can always add that KEY manually afterwards...

Cheers,
Dan

I tried to install the server on 3 different hosting companies using MySQL version 5.1.30 and i had the same error.

The 4th installation on a different Mysql version worked fine.

Glad you got it sorted. The problem is to do with key constraints and we've been unable to replicate it. It must just be something to do with the way that hosting company have setup MySQL.

Cheers

Alex

I been trying to install this script on my hosting account for two days now with no success. I receive the same errors as stated above. Keep in mind that I work at HostGator.com and have a little deeper insight as our sever technologies and yet have absolutely no clue what could be causing this LOL.

Eventually I decided to set up a virtual machine in my personal computer, installed LAMP to it. And finally attempted the install once again. The installation went fine. I used the create a new database method since i have root access to that vm server.

Once the script was installed. I decided to move the files over to my hosting account. I figured it would be only a matter of updating the settings file, manually creating a db and db user on that server. And importing the sql from the installation that actually worked.

During the sql import however the issue was once again present.

I decided to drop all tables and manually import the sql bit by bit.

Everything imported fine. except for this little chunk....
=========================================================================
ALTER TABLE `pages`
  ADD CONSTRAINT `pages_ibfk_1` FOREIGN KEY (`pagegroupID`) REFERENCES `pages` (`pagegroupID`);
=========================================================================

The error it gives out is this...
================================================================================

Error

SQL query:

ALTER TABLE `pages` ADD CONSTRAINT `pages_ibfk_1` FOREIGN KEY ( `pagegroupID` ) REFERENCES `pages` ( `pagegroupID` )

MySQL said: Documentation
#1025 - Error on rename of './quetzal_quetzal/#sql-6602_c976a' to './quetzal_quetzal/pages' (errno: 150)

===================================================================================

If you would like access to a cpanel account on my server so you can replicate this yourself let me know. Just drop me a line at "toontitan at gmail dot com" and i'll set it up for you.

Hi

I'd guess it's almost certainly the version of MySQL you're using.

Alex

--- original message ---
From: "toontitan" <email address hidden>
Subject: Re: [Question #74266]: Server Installation problem : Mysql Error 150
Date: 24th January 2010
Time: 5:09:20 am

Question #74266 on Xibo changed:
https://answers.launchpad.net/xibo/+question/74266

toontitan posted a new comment:
I been trying to install this script on my hosting account for two days
now with no success. I receive the same errors as stated above. Keep in
mind that I work at HostGator.com and have a little deeper insight as
our sever technologies and yet have absolutely no clue what could be
causing this LOL.

Eventually I decided to set up a virtual machine in my personal
computer, installed LAMP to it. And finally attempted the install once
again. The installation went fine. I used the create a new database
method since i have root access to that vm server.

Once the script was installed. I decided to move the files over to my
hosting account. I figured it would be only a matter of updating the
settings file, manually creating a db and db user on that server. And
importing the sql from the installation that actually worked.

During the sql import however the issue was once again present.

I decided to drop all tables and manually import the sql bit by bit.

Everything imported fine. except for this little chunk....
=========================================================================
ALTER TABLE `pages`
  ADD CONSTRAINT `pages_ibfk_1` FOREIGN KEY (`pagegroupID`) REFERENCES `pages` (`pagegroupID`);
=========================================================================

The error it gives out is this...
================================================================================

Error

SQL query:

ALTER TABLE `pages` ADD CONSTRAINT `pages_ibfk_1` FOREIGN KEY (
`pagegroupID` ) REFERENCES `pages` ( `pagegroupID` )

MySQL said: Documentation
#1025 - Error on rename of './quetzal_quetzal/#sql-6602_c976a' to './quetzal_quetzal/pages' (errno: 150)

===================================================================================

If you would like access to a cpanel account on my server so you can
replicate this yourself let me know. Just drop me a line at "toontitan
at gmail dot com" and i'll set it up for you.

--
You received this question notification because you are a member of Xibo
Developers, which is an answer contact for Xibo.

This email carries a disclaimer, a copy of which may be read at http://learning.longhill.org.uk/disclaimer

Well the funny thing is that everything else worked fine including all the
other ALTER TABLE queries which tells me that theres oviously something
different about the pagegroupID field that were not quite seeing.

The weird thing is I don't even know how important that really is since my
installation appears to be working just fine simply by leaving that out of
the sql import.

On Sun, Jan 24, 2010 at 7:54 AM, Alex Harrington <
<email address hidden>> wrote:

> Question #74266 on Xibo changed:
> https://answers.launchpad.net/xibo/+question/74266
>
> Alex Harrington posted a new comment:
> Hi
>
> I'd guess it's almost certainly the version of MySQL you're using.
>
> Alex
>
> --- original message ---
> From: "toontitan" <email address hidden>
> Subject: Re: [Question #74266]: Server Installation problem : Mysql Error
> 150
> Date: 24th January 2010
> Time: 5:09:20 am
>
> Question #74266 on Xibo changed:
> https://answers.launchpad.net/xibo/+question/74266
>
> toontitan posted a new comment:
> I been trying to install this script on my hosting account for two days
> now with no success. I receive the same errors as stated above. Keep in
> mind that I work at HostGator.com and have a little deeper insight as
> our sever technologies and yet have absolutely no clue what could be
> causing this LOL.
>
> Eventually I decided to set up a virtual machine in my personal
> computer, installed LAMP to it. And finally attempted the install once
> again. The installation went fine. I used the create a new database
> method since i have root access to that vm server.
>
> Once the script was installed. I decided to move the files over to my
> hosting account. I figured it would be only a matter of updating the
> settings file, manually creating a db and db user on that server. And
> importing the sql from the installation that actually worked.
>
> During the sql import however the issue was once again present.
>
> I decided to drop all tables and manually import the sql bit by bit.
>
> Everything imported fine. except for this little chunk....
> =========================================================================
> ALTER TABLE `pages`
> ADD CONSTRAINT `pages_ibfk_1` FOREIGN KEY (`pagegroupID`) REFERENCES
> `pages` (`pagegroupID`);
> =========================================================================
>
>
> The error it gives out is this...
>
> ================================================================================
>
> Error
>
> SQL query:
>
> ALTER TABLE `pages` ADD CONSTRAINT `pages_ibfk_1` FOREIGN KEY (
> `pagegroupID` ) REFERENCES `pages` ( `pagegroupID` )
>
> MySQL said: Documentation
> #1025 - Error on rename of './quetzal_quetzal/#sql-6602_c976a' to
> './quetzal_quetzal/pages' (errno: 150)
>
>
> ===================================================================================
>
> If you would like access to a cpanel account on my server so you can
> replicate this yourself let me know. Just drop me a line at "toontitan
> at gmail dot com" and i'll set it up for you.
>
> --
> You received this question notification because you are a member of Xibo
> Developers, which is an answer contact for Xibo.
>
> This email carries a disclaimer, a copy of which may be read at
> http://learning.longhill.org.uk/disclaimer
>
> You received this question notification because you are a direct
> subscriber of the question.
>

--
Ibis Fernandez
Cinestar Filmworks
Tel: 832.867.0842
http://cinestarfilmworks.com
http://onesourcebt.com

There is actually an error in that statement which seems to cause problems on some MySQL installs. It has been fixed for the next release.

You are quite safe to leave that out.

If the statement is the one from the original question this refers to it's in 1.sql and therefore isn't that issue?

Alex

--- original message ---
From: "Dan Garner" <email address hidden>
Subject: Re: [Question #74266]: Server Installation problem : Mysql Error 1 50
Date: 24th January 2010
Time: 2:15:39 pm

Question #74266 on Xibo changed:
https://answers.launchpad.net/xibo/+question/74266

Dan Garner posted a new comment:
There is actually an error in that statement which seems to cause
problems on some MySQL installs. It has been fixed for the next release.

You are quite safe to leave that out.

--
You received this question notification because you are a member of Xibo
Developers, which is an answer contact for Xibo.

This email carries a disclaimer, a copy of which may be read at http://learning.longhill.org.uk/disclaimer

I should have said "a similar issue". I noticed this incorrect statement some time ago but forgot to create a bug report for it - created one now and filed against 1.0.6.

That is awesome guys. I'm glad its been fixed in the next release :)

I was downloading the 1.1 version of Xibo server. I have the same problem and my hosting service is Hostgator. Was this fixed in this version?

This problem will be fixed in 1.1.1 but is not fixed in the released 1.1.0.

As a workaround please install 1.0.6 and then put 1.1.0 over the top (the
upgrade between the two works without issue)

Thanks,
Dan