12.5 Upgrade DB Error

[vispa]

Hi,

I am trying to upgrade from 12.0.18 to 12.5.

The upgrade failed on an SQL query. Here is my installation log extract :

ERROR while trying to execute SQL query, the query was: ALTER TABLE `APSLicenseTypes` CHANGE `application_name` `application_name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_b$

Check the error reason(see log file: /var/log/plesk/install/plesk_12.5.30_installation.log), fix and try again
===> Restoring database from backup /var/lib/psa/dumps/mysql.preupgrade.12.0.18-12.5.30.20151024-122955.dump.gz
Trying to start psa...

Stopping psa... Stopping sw-engine-fpm: [FAILED]

done



**** Product prep-install for BASE failed.



***** problem report *****

ERROR while trying to execute SQL query, the query was: ALTER TABLE `APSLicenseTypes` CHANGE `application_name` `application_name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_b$

Check the error reason(see log file: /var/log/plesk/install/plesk_12.5.30_installation.log), fix and try again

STOP Bootstrapper 12.5.30 prep-install for BASE AT Sat Oct 24 12:31:27 BST 2015

Any help would be appreciated as this problem appears to happen on two servers.

 

[UFHH01]

Hi vispa,

there is an error with UTF collation. You could solve the issue with a manual correction, but please be sure, that a Odin-Team - Member confirms the suggestion.

Suggestion:

First of all, please backup your psa and apsc database:

mysqldump -uadmin -p`< /etc/psa/.psa.shadow ` --databases psa apsc | gzip > /root/manual.psa-apsc.dump.sql.gz
​

Second, please make a command list for each database:

SELECT concat( 'ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET `utf8` COLLATE `utf8_bin`;' )
FROM information_schema.tables
WHERE table_schema = 'psa'
AND TABLE_TYPE = 'BASE TABLE'

SELECT concat( 'ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET `utf8` COLLATE `utf8_bin`;' )
FROM information_schema.tables
WHERE table_schema = 'apsc'
AND TABLE_TYPE = 'BASE TABLE'

Both above SQL - queries will show a complete list of possible SQL - commands for "psa" and "apsc" for UTF collation, which you have to execute. It will look like this ( shortened! ):

ALTER TABLE APSApplicationItems CONVERT TO CHARACTER SET `utf8` COLLATE `utf8_bin`;
ALTER TABLE APSCatalogUpdates CONVERT TO CHARACTER SET `utf8` COLLATE `utf8_bin`;
ALTER TABLE APSClientApplicationItems CONVERT TO CHARACTER SET `utf8` COLLATE `utf8_bin`;
ALTER TABLE APSLicenseTypes CONVERT TO CHARACTER SET `utf8` COLLATE `utf8_bin`;
ALTER TABLE APSLicenses CONVERT TO CHARACTER SET `utf8` COLLATE `utf8_bin`;
...

After the execution of all listed SQL - commands, please return use the previous upgrade process and try to upgrade again.​

If you experience any issues with the above suggestion, please restore your "psa" and "apsc" - databases with the command:

zcat /root/manual.psa-apsc.dump.sql.gz | mysql -uadmin -p`< /etc/psa/.psa.shadow `​

 

[Hexanet]

I have deleted my previous answer. I was wrong. The query "ALTER TABLE `APSLicenseTypes` CHANGE `application_name` `application_name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL" is valid. But it triggers a MySQL error #1071.

The given error when running this query is: ERROR #1071 - Specified key was too long; max key length is 1000 bytes.

The problem is described in this StackOverflow thread with a lengthy mysql bug discussion on the matter. The latin to utf8 conversion here is hitting this long standing issue. In my case, the query won't run unless I first convert such table(s) to InnoDB first from MyISAM (my legacy table type).

PS: The plesk team should probably look at converting tables type first or make sure to around this problem for users from perhaps much older versions using MyISAM tables.

 

[Linulex]

There is no need to run any other queries, the tables only needs to be converted to innodb before the upgrade.
I had the same problem, run this at the commandline:

ALTER TABLE APSLicenseTypes ENGINE=InnoDB;

or better still, make psa database completly innodb: this is what i did

mysql -uadmin -p`cat /etc/psa/.psa.shadow` psa -e "show table status where Engine='MyISAM';" | awk 'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}' | mysql -uadmin -p`cat /etc/psa/.psa.shadow` psa

regards
Jan