Error during upgrading from 11.0.9 #54 to 11.5.29

[Flachzange]

[SOLVED] Error during upgrading from 11.0.9 #54 to 11.5.29

Plesk offered me a upgrade which I just tried. But I get an error in early phases during upgrade and Google did not help at all:

===> Upgrading database

 Trying to start service mysql... /usr/sbin/mysqld (pid 7951) is running...
done
 Trying to establish test connection... connected
done
 Trying to find psa database... version is 011009
 Trying to backup MySQL database... done
 MySQL databases are dumped to /private-backup/mysql.preupgrade.11.0.9-11.5.29.20130614-182913.dump.gz
 Trying to upgrade APS controller database...
===> Cumulative APS controller database (apsc) upgrade has been started.
 Trying to backup MySQL database... done
 MySQL databases are dumped to /private-backup/mysql.preupgrade.apsc.11.0.9-11.5.29.20130614-182918.dump.gz
ERROR 1005 (HY000) at line 9: Can't create table 'apsc.aps_resource_adjacency_list' (errno: 150)
InnoDB

LATEST FOREIGN KEY ERROR
------------------------
130614 18:29:18 Error in foreign key constraint of table apsc/aps_resource_adjacency_list:
 FOREIGN KEY (`base_resource_id`) REFERENCES `aps_resource` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_aps_resource_adjacency_list_dependent` FOREIGN KEY (`dependent_resource_id`) REFERENCES `aps_resource` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8:
Cannot resolve table name close to:
 (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_aps_resource_adjacency_list_dependent` FOREIGN KEY (`dependent_resource_id`) REFERENCES `aps_resource` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Here you find the log (from web update, but also tried command line).

Any help is appreciated.

 

[Nikolay.]

I suppose you have MyISAM engine for aps_resource table and others in apsc DB. You can either manually change engine for all tables in this DB to InnoDB and retry upgrade, or wait while an update fixing this issue is released (which will be soon-ish, I guess).

 

[Flachzange]

Thanks. This did the trick.

 

[MichaelPD]

Same issue here with 11.5.30, no fix as it seems....

I tried to alter the apsc tables to InnDB with:
alter table aps_resource ENGINE=InnoDB;

But i get:
ERROR 1146 (42S02): Table 'apsc.aps_resource' doesn't exist

The table is there (show tables), and i can also use the "tab" button during typing the command, to select the db. but it still gives me the error.

any idea?
Michael

 

[IgorG]

Are you sure that you created this table in apsc database but not in psa database?

 

[MichaelPD]

Yes, i see the table in the apsc database:


mysql> use apsc;
mysql> show tables;
+------------------------------------+
| Tables_in_apsc |
+------------------------------------+
| aps_application |
| aps_application_backup |
| aps_application_resource |
| aps_package |
| aps_package_configuration |
| aps_package_global_setting |
| aps_package_resource_configuration |
| aps_package_resource_setting |
| aps_package_series |
| aps_package_service |
| aps_registry_object |
| aps_registry_object_setting |
| aps_registry_object_tag |
| aps_resource |
| aps_resource_backup |
| aps_resource_requirement |
| aps_resource_requirement_backup |
| aps_settings_sequenses |
| meta_info |
+------------------------------------+
19 rows in set (0.00 sec)

 

[IgorG]

Do you have the same structure for this table:

# mysql -uadmin -p`cat /etc/psa/.psa.shadow` -Dapsc -e "desc aps_resource"
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| registry_object_id | int(10) unsigned | NO | MUL | NULL | |
| parent_resource_id | int(10) unsigned | YES | | NULL | |
| service_id | varchar(64) | NO | | NULL | |
| resource_type | varchar(256) | NO | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+

 

[MichaelPD]

Same problem, i see:

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -Dapsc -e "desc aps_resource"
ERROR 1146 (42S02) at line 1: Table 'apsc.aps_resource' doesn't exist

 

[IgorG]

Try to delete already existing aps_resource table and create it again with using following structure of this table:

mysql> show create table aps_resource\G
*************************** 1. row ***************************
Table: aps_resource
Create Table: CREATE TABLE `aps_resource` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`registry_object_id` int(10) unsigned NOT NULL,
`parent_resource_id` int(10) unsigned DEFAULT NULL,
`service_id` varchar(64) NOT NULL,
`resource_type` varchar(256) NOT NULL,
`app_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_aps_resource_aps_registry_object` (`registry_object_id`),
KEY `FK_aps_resource_aps_application` (`app_id`),
CONSTRAINT `FK_aps_resource_aps_application` FOREIGN KEY (`app_id`) REFERENCES `aps_application` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_aps_resource_aps_registry_object` FOREIGN KEY (`registry_object_id`) REFERENCES `aps_registry_object` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

 

[MichaelPD]

Doesnt work!

mysql> drop table aps_resource;
ERROR 1051 (42S02): Unknown table 'aps_resource'

If i try the create table command, i get:
ERROR 1005 (HY000): Can't create table 'apsc.aps_resource' (errno: 150)

 

[IgorG]

Try to repair table with mysqlcheck

 

[MichaelPD]

sorry, drop table was succesful!

but create table command wasnt.
ERROR 1005 (HY000): Can't create table 'apsc.aps_resource' (errno: 150)

 

[MichaelPD]

error 150 looks like a foreign key issue. Probably because i have the same issue with all other tables in the apsc database as well:

mysql> alter table aps_package ENGINE=InnoDB;
ERROR 1146 (42S02): Table 'apsc.aps_package' doesn't exist

 

[MichaelPD]

Does anyone have an idea?

Michael

 

[IgorG]

Something is broken in your MySQL server. Contact support team for investigation and fixing directly on your server.