upgrade Plesk 12.0.18 to Plesk 12.5.30 fails SQL query

[johnrdorazio]

I am trying to update my Plesk installation to the latest version, but it is tripping up on the PREP actions before actually proceeding to update. Here is the error that is repeated a few times:

ERROR while trying to execute SQL query, the query was: ALTER TABLE `avstatistics` ADD KEY `date_type` (type,`date`)

I might add that my MySQL is also updated to the latest version 5.7. Perhaps there was some incompatible change between MySQL 5.6 and MySQL 5.7?

 

[IgorG]

Note, according to Plesk 12.5.30 Release Notes, MySQL 5.7 version is not supported - http://docs.plesk.com/release-notes/12.5/software-requirements/#s4-2-1

 

[Jens]

Hi IgorG,

same error; with

• Server version: 5.5.47-cll-lve - MySQL Community Server (GPL) by Atomicorp

 

[IgorG]

Do you have any related error messages in mysql log? Looks like database is corrupted. Try to fix it with help of standard mysql methods.

 

[Linulex]

I had this problem because some tables in the psa database where myisam.

this will update all myisam to innodb in the psa database. run at the commandline, one line.

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

hope this helps
regards
Jan

 

[Jens]

Thanks Linulex,

but i got a new error

***** problem report *****
ERROR while trying to execute SQL query, the query was: ALTER TABLE `APSCatalogUpdates` CHANGE `received` `received` DATETIME DEFAULT '0000-00-00 00:00:00'
Check the error reason(see log file: /var/log/plesk/install/plesk_12.5.30_installation.log), fix and try again

that is the part of plesk i hate, i don't know if this part of database is necessary for plesk i wish i could delete this table; i guess plesk will never work very well.

to solve this; i really need to disable the strict mode in mysql.

 

[IgorG]

Thanks Linulex,

but i got a new error

***** problem report *****
ERROR while trying to execute SQL query, the query was: ALTER TABLE `APSCatalogUpdates` CHANGE `received` `received` DATETIME DEFAULT '0000-00-00 00:00:00'
Check the error reason(see log file: /var/log/plesk/install/plesk_12.5.30_installation.log), fix and try again

that is the part of plesk i hate, i don't know if this part of database is necessary for plesk i wish i could delete this table; i guess plesk will never work very well.

Looks like table APSCatalogUpdates is corrupted. I'd suggest you drop this table and recreate with following schema:

Create Table: CREATE TABLE `APSCatalogUpdates` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`package_name` varchar(255) DEFAULT NULL,
`package_version` varchar(30) DEFAULT NULL,
`package_release` varchar(30) DEFAULT NULL,
`update_name` varchar(255) DEFAULT NULL,
`update_version` varchar(30) DEFAULT NULL,
`update_release` varchar(30) DEFAULT NULL,
`update_vendor` varchar(255) DEFAULT NULL,
`update_packager` varchar(255) DEFAULT NULL,
`received` datetime DEFAULT '0000-00-00 00:00:00',
`update_type` enum('patch','upgrade') NOT NULL DEFAULT 'patch',
`recommended` enum('false','true') NOT NULL DEFAULT 'false',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

[Jens]

Looks like table APSCatalogUpdates is corrupted.

to solve this; i really need to disable the strict mode in mysql.

Thanks IgorG; but i just had disable the strict mode from MySQL to solve problem above; the update (upgrade) was now successfully.

After that; i could not use the "Let's Encrypt" Plugin, because i had edited the nginx.conf for more SSL Security but they was now in conflict with Plesk Odin settings, to solve this i just edit the nginx.conf again and exclude the conf.d folder.