Error installing 1.1.0 when importing SQL-queries

[G. de Keijzer]

Installing a second version on the same server in a new directory.

Running version is 1.0.5.

Want to install 1.1.0 in seperate directory. Everything goes fine until:
Populating the database

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

Loading from 2.sql .......

Loading from 3.sql ....

Loading from 4.sql .........

Loading from 6.sql ....

Loading from 7.sql .........

Loading from 8.sql .........

Loading from 9.sql ......

Loading from 20.sql .

An error occured populating the database.

MySQL Error:
Duplicate entry '28' for key 'PRIMARY'

SQL executed:
INSERT INTO `pages` (`name`, `pagegroupID`) VALUES ('help', '2'), ('clock', 2)

Statement number: 110

[Alex Harrington (alexharrington)]

Are you using the same database as the 1.0.5 install or are you creating a new empty database in the installer?

Server OS?
PHP Version?
Which Xibo install file did you download (tar.gz / zip)?

Alex

[Alex Harrington (alexharrington)]

Dan:
As a sidenote looking at 20.sql I notice the two items to be inserted on line 2 aren't formatted consistently. I don't think this is what's causing the problem though:
http://bazaar.launchpad.net/~xibo-maintainers/xibo/encke-lineendings/annotate/head%3A/server/install/database/20.sql#L2

[G. de Keijzer (gerwin-ipict)]

Server: W2003
PHP: 5.2
Apache 2.1
MySQL; uhm... latest
(WAMP server)

Installed from the ZIP file.

The installation of 1.0.5 didn't give any errors. They can run besides each other, can they ?

[G. de Keijzer (gerwin-ipict)]

Installing on new MySQL database

[Alex Harrington (alexharrington)]

As long as you're not trying to install in to a database that has a 1.0.5 install inside it already then it should work. If you want to use your 1.0.5 install database as a base you should clone it to a different database name and then upgrade from 1.1.0 to 1.0.5 manually ammending the database name in the settings.php file.

Can you dump out that database as the installer left it please using mysqladmin or mysqldump and send it to <email address hidden> so we can see where it got to.

Cheers

Alex

[G. de Keijzer (gerwin-ipict)]

Nope... seperate folders, seperate databases and separate DB-users.
SQL-Dump on its way

[Alex Harrington (alexharrington)]

OK - got it. Thank you. I don't see why that line should fail at all on the database you sent.

Dan?

[Alex Harrington (alexharrington)]

Tried 1.1.0 fresh install here and it went on first time - no errors.

[G. de Keijzer (gerwin-ipict)]

Perhaps try to install it to a new database with a completely new name.
In my case i made a new DB called XIBO110 with corresponding user and password

[Alex Harrington (alexharrington)]

I did create a new database, username and password. I'll wait for Dan to look more closely before doing anything else.

[Dan Garner (dangarner)]

Can you try changing 20.sql from:

INSERT INTO `pages` (`name`, `pagegroupID`)
VALUES ('help', '2'), ('clock', 2);

to

INSERT INTO `pages` (`name`, `pagegroupID`)
VALUES ('help', '2');

INSERT INTO `pages` (`name`, `pagegroupID`)
VALUES ('clock', 2);

And running a fresh install from that?

Which version of MySQL are you using?

[G. de Keijzer (gerwin-ipict)]

Mysql 5.1.30

Meanwhile, SOLVED !!

In 9.SQL changed:
=============================
INSERT INTO `pages` (
`pageID` ,
`name` ,
`pagegroupID`
)
VALUES (
28 , 'manual', '2'
);

In to:
==============================
INSERT INTO `pages` (
`name` ,
`pagegroupID`
)
VALUES (
'manual', '2'
);

[G. de Keijzer (gerwin-ipict)]

Perhaps because pageID is an auto_increment field?

[Alex Harrington (alexharrington)]

But the error comes from 20.sql and the database you sent over has
correctly got that line added.

I don't think that was the cause of your problem.

Alex

[G. de Keijzer (gerwin-ipict)]

The error comes from 20.sql while 9.sql already placed a record with a fixed 28 in the pageID field.

I'll try with an other version of MySQL to check wether is re-occurs.
I'll also do these SQL's by hand.

Meanwhile it works, but i'll try to figure out.. why ?!
:)

Regards,

Gerwin

[Dan Garner (dangarner)]

The pages table does have auto increment... but auto increment should still work if you insert a record with a fixed ID...

I.e. when the SQL executed in 20.sql it should have tried to put in ID 29 - not ID 28.

Very odd...

As it happens the 9.sql is the wrong way to do it... the reason it puts in a fixed ID is so that it knows the pageID for the menuitem insert just below... there is a better way do to it, but I didn't think this way would cause any problems either.

I'll fix 9.sql for 1.0.6 to insert that record in another manner.

[Shahid (smalik-albion)]

Just wanted to add that I also experienced this exact issue as well, today while trying to install to a new database from the installer.

Win2k3 serv
MySQL: 5.1.30
PHP: 5.2.8

However, upon updating MySQL to the latest 5.1.42, the install went without error.

Shahid

[Dan Garner (dangarner)]

Hi Shahid - that is very interesting indeed.

It worked for me on 5.1.36 too.

If anyone else is experiencing this problem a MySQL upgrade will fix it. Otherwise 1.0.6 will fix it.