Upgrade fails from 1.6.0 to 1.7.0

[Mondlicht81]

Hi,

Unfortunately also my upgrade fails. I tried to upgrade from version 1.6.0 directly to 1.7.0

Statement number: 80. Error Message = [SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '9' for key 1]

Please tell me what you need to analyze further.

Thanks!

[dasgarner]

Can you try reverting to 1.6 and then patching this file: https://github.com/dasgarner/xibo-cms/blob/develop/install/database/80.sql ?

[dasgarner]

To stem the tide of these errors I have released some fresh archives on the release page which fix the issue inserting resolutions (duplicate key 9) and fixes the subsequent issues of duplicate column render_as.

[Mondlicht81]

Hi Dan,

Yes, exactly that was the problem. I just found it: we inserted some own display resolution, with index 9.

mysql> select * from resolution -> ; +--------------+----------------------+-------+--------+----------------+-----------------+---------+---------+ | resolutionID | resolution | width | height | intended_width | intended_height | version | enabled | +--------------+----------------------+-------+--------+----------------+-----------------+---------+---------+ | 1 | 4:3 Monitor | 800 | 600 | 1024 | 768 | 1 | 0 | | 2 | 3:2 Tv | 720 | 480 | 1440 | 960 | 1 | 0 | | 3 | 16:10 Widescreen Mon | 800 | 500 | 1680 | 1050 | 1 | 0 | | 4 | 16:9 HD Widescreen | 800 | 450 | 1920 | 1080 | 1 | 0 | | 5 | 3:4 Monitor | 600 | 800 | 768 | 1024 | 1 | 0 | | 6 | 2:3 Tv | 480 | 720 | 960 | 1440 | 1 | 0 | | 7 | 10:16 Widescreen | 500 | 800 | 1050 | 1680 | 1 | 0 | | 8 | 9:16 HD Widescreen | 450 | 800 | 1080 | 1920 | 1 | 0 | | 9 | CustomerSupportTV | 800 | 500 | 1280 | 800 | 1 | 0 | +--------------+----------------------+-------+--------+----------------+-----------------+---------+---------+ 9 rows in set (0.00 sec)

And this is why mySQL complains about the ID 9.

INSERT INTO `resolution` (`resolutionID`, `resolution`, `width`, `height`, `intended_width`, `intended_height`, `version`, `enabled`) VALUES
    -> (9, '1080p HD Landscape', 800, 450, 1920, 1080, 2, 1),
    -> (10, '720p HD Landscape', 800, 450, 1280, 720, 2, 1),
    -> (11, '1080p HD Portrait', 450, 800, 1080, 1920, 2, 1),
    -> (12, '720p HD Portrait', 450, 800, 720, 1280, 2, 1),
    -> (13, '4k', 800, 450, 4096, 2304, 2, 1),
    -> (14, 'Common PC Monitor 4:3', 800, 600, 1024, 768, 2, 1);
ERROR 1062 (23000): Duplicate entry '9' for key 1

[dasgarner]

Yup exactly so - I have fixed the 80.sql install file so that it reads:

INSERT INTO `resolution` (`resolution`, `width`, `height`, `intended_width`, `intended_height`, `version`, `enabled`) VALUES
    -> ('1080p HD Landscape', 800, 450, 1920, 1080, 2, 1),
    -> ('720p HD Landscape', 800, 450, 1280, 720, 2, 1),
    -> ('1080p HD Portrait', 450, 800, 1080, 1920, 2, 1),
    -> ('720p HD Portrait', 450, 800, 720, 1280, 2, 1),
    -> ('4k', 800, 450, 4096, 2304, 2, 1),
    -> ('Common PC Monitor 4:3', 800, 600, 1024, 768, 2, 1);

Which should fix the issue - the problem now will be that there is a partial upgrade and you will need to revert back to your backup and run it all again or delete everything from 80.sql that is before that INSERT statement.

[Mondlicht81]

Yes, thanks. This works now. (but now I seem to hang on the utf32 issue...)

[dasgarner]

utf32 can be changed to utf8

The new release files contain that change too

[Mondlicht81]

Something else: since I have to run the upgrade several times, I just re-insert the backup sql into my database. but this doesn't delete the newly created tables.

could you please add some DROP statements for all tables that get inserted data with the key?

e.g. in sql80.xml: DROP TABLE bandwidthtype; CREATE TABLE IF NOT EXISTS bandwidthtype ( bandwidthtypeid int(11) NOT NULL AUTO_INCREMENT, name varchar(25) NOT NULL, PRIMARY KEY (bandwidthtypeid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

sql82.xml:

DROP TABLE tag; CREATE TABLE IF NOT EXISTS tag ( tagId int(11) NOT NULL AUTO_INCREMENT, tag varchar(50) NOT NULL, PRIMARY KEY (tagId) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

[dasgarner]

That would generate a bunch of errors for dropping non-existent tables - besides you should always restore backups into an empty DB.

[Mondlicht81]

Ok, accepted.