Xibo Open Source Digital Signage

Error installing 1.1.0 when importing SQL-queries

Reported by G. de Keijzer on 2010-01-03
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Xibo
Medium
Dan Garner

Bug Description

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

Related branches

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

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

Changed in xibo:
status: New → Incomplete
G. de Keijzer (gerwin-ipict) wrote :

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) wrote :

Installing on new MySQL database

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) wrote :

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

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

Dan?

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

G. de Keijzer (gerwin-ipict) wrote :

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

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) wrote :

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) wrote :

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) wrote :

Perhaps because pageID is an auto_increment field?

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) wrote :

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) wrote :

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.

Changed in xibo:
milestone: none → 1.0.6
assignee: nobody → Dan Garner (dangarner)
status: Incomplete → Confirmed
Changed in xibo:
importance: Undecided → Medium
Shahid (smalik-albion) wrote :

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) wrote :

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.

Dan Garner (dangarner) on 2010-01-16
Changed in xibo:
status: Confirmed → Fix Committed
Changed in xibo:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers