Hi, Did some more digging:
The foreign keys are to these tables: +-----------------------------+ | TABLE_NAME | +-----------------------------+ | global_load_balancing_rules | | portable_ip_address | | portable_ip_range | +-----------------------------+ 3 rows in set (0.04 sec) I see two ways to solve it: Option 1: Remove the foreign keys, do the alter, then add them again. Like this: # Remove ALTER TABLE global_load_balancing_rules DROP FOREIGN KEY fk_global_load_balancing_rules_region_id; ALTER TABLE portable_ip_address DROP FOREIGN KEY fk_portable_ip_address__region_id; ALTER TABLE portable_ip_range DROP FOREIGN KEY fk_portableip__region_id; # Alter ALTER TABLE `cloud`.`region` MODIFY `id` int unsigned AUTO_INCREMENT; # Add ALTER TABLE global_load_balancing_rules ADD CONSTRAINT fk_global_load_balancing_rules_region_id FOREIGN KEY (region_id) references region(id); ALTER TABLE portable_ip_address ADD CONSTRAINT fk_portable_ip_address__region_id FOREIGN KEY (region_id) references region(id); ALTER TABLE portable_ip_range ADD CONSTRAINT fk_portableip__region_id FOREIGN KEY (region_id) references region(id); Option 2: Temporarily disable foreign key checks: set foreign_key_checks=0; ALTER TABLE `cloud`.`region` MODIFY `id` int unsigned AUTO_INCREMENT; set foreign_key_checks=1; I have tested both and they work. Let's discuss how you want to solve it and I can create a PR if needed. Regards, Remi On 13 Apr 2015, at 15:26, Remi Bergsma <rberg...@schubergphilis.com<mailto:rberg...@schubergphilis.com>> wrote: Hi Daan, This is probably something we want to fix in 4.4.3? If I try an upgrade from 4.4.x to 4.5 and above, then there's a SQL upgrade command failing: ERROR [c.c.u.d.ScriptRunner] (main:null) Error executing: ALTER TABLE `cloud`.`region` MODIFY `id` int unsigned AUTO_INCREMENT UNIQUE NOT NULL ERROR [c.c.u.d.ScriptRunner] (main:null) java.sql.SQLException: Cannot change column 'id': used in a foreign key constraint 'fk_global_load_balancing_rules_region_id' of table 'cloud.global_load_balancing_rules' ERROR [c.c.u.DatabaseUpgradeChecker] (main:null) Unable to execute upgrade script: /Users/rbergsma/git/remibergsma/cloudstack/client/target/utilities/scripts/db/db/schema-442to450.sql java.sql.SQLException: Cannot change column 'id': used in a foreign key constraint 'fk_global_load_balancing_rules_region_id' of table 'cloud.global_load_balancing_rules' I discovered it first when I was on the to-be-released 4.4.3 branch, but 4.4.2 and 4.4.1 have the same issue if upgrading to 4.5/4.6. Testing is easy: just deploy a given version and try to run this query: ALTER TABLE `cloud`.`region` MODIFY `id` int unsigned AUTO_INCREMENT UNIQUE NOT NULL; Let's have a look how to fix this. Also see the details below. Regards, Remi 4.4.3: mysql> select * from version; +----+---------+---------------------+----------+ | id | version | updated | step | +----+---------+---------------------+----------+ | 1 | 4.0.0 | 2015-04-13 14:24:31 | Complete | | 2 | 4.1.0 | 2015-04-13 19:24:49 | Complete | | 3 | 4.2.0 | 2015-04-13 19:24:49 | Complete | | 4 | 4.2.1 | 2015-04-13 19:24:49 | Complete | | 5 | 4.3.0 | 2015-04-13 19:24:49 | Complete | | 6 | 4.4.0 | 2015-04-13 19:24:49 | Complete | | 7 | 4.4.1 | 2015-04-13 19:24:49 | Complete | | 8 | 4.4.2 | 2015-04-13 19:24:49 | Complete | | 9 | 4.4.3 | 2015-04-13 19:24:49 | Complete | +----+---------+---------------------+----------+ 9 rows in set (0.00 sec) mysql> select * from region; +----+-------+-------------------------------+----------------------------+----------------------+ | id | name | end_point | portableip_service_enabled | gslb_service_enabled | +----+-------+-------------------------------+----------------------------+----------------------+ | 1 | Local | http://localhost:8080/client/ | 0 | 1 | +----+-------+-------------------------------+----------------------------+----------------------+ 1 row in set (0.00 sec) mysql> select * from global_load_balancing_rules; Empty set (0.00 sec) mysql> ALTER TABLE `cloud`.`region` MODIFY `id` int unsigned AUTO_INCREMENT UNIQUE NOT NULL; ERROR 1833 (HY000): Cannot change column 'id': used in a foreign key constraint 'fk_global_load_balancing_rules_region_id' of table 'cloud.global_load_balancing_rules' mysql> It also appears in 4.4.2: git checkout upstream/GA-4.4.2 mysql> select * from version; +----+---------+---------------------+----------+ | id | version | updated | step | +----+---------+---------------------+----------+ | 1 | 4.0.0 | 2015-04-13 14:31:31 | Complete | | 2 | 4.1.0 | 2015-04-13 19:31:48 | Complete | | 3 | 4.2.0 | 2015-04-13 19:31:48 | Complete | | 4 | 4.2.1 | 2015-04-13 19:31:48 | Complete | | 5 | 4.3.0 | 2015-04-13 19:31:48 | Complete | | 6 | 4.4.0 | 2015-04-13 19:31:48 | Complete | | 7 | 4.4.1 | 2015-04-13 19:31:48 | Complete | | 8 | 4.4.2 | 2015-04-13 19:31:48 | Complete | +----+---------+---------------------+----------+ 8 rows in set (0.00 sec) mysql> describe region; +----------------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------------+---------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | NULL | | | name | varchar(255) | NO | UNI | NULL | | | end_point | varchar(255) | NO | | NULL | | | portableip_service_enabled | tinyint(1) unsigned | NO | | 0 | | | gslb_service_enabled | tinyint(1) unsigned | NO | | 1 | | +----------------------------+---------------------+------+-----+---------+-------+ 5 rows in set (0.01 sec) mysql> ALTER TABLE `cloud`.`region` MODIFY `id` int unsigned AUTO_INCREMENT UNIQUE NOT NULL; ERROR 1833 (HY000): Cannot change column 'id': used in a foreign key constraint 'fk_global_load_balancing_rules_region_id' of table 'cloud.global_load_balancing_rules' mysql> git checkout upstream/GA-4.4.1 mysql> select * from version; +----+---------+---------------------+----------+ | id | version | updated | step | +----+---------+---------------------+----------+ | 1 | 4.0.0 | 2015-04-13 14:42:18 | Complete | | 2 | 4.1.0 | 2015-04-13 19:42:33 | Complete | | 3 | 4.2.0 | 2015-04-13 19:42:33 | Complete | | 4 | 4.2.1 | 2015-04-13 19:42:33 | Complete | | 5 | 4.3.0 | 2015-04-13 19:42:33 | Complete | | 6 | 4.4.0 | 2015-04-13 19:42:33 | Complete | | 7 | 4.4.1 | 2015-04-13 19:42:33 | Complete | +----+---------+---------------------+----------+ 7 rows in set (0.00 sec) mysql> describe region; +----------------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------------+---------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | NULL | | | name | varchar(255) | NO | UNI | NULL | | | end_point | varchar(255) | NO | | NULL | | | portableip_service_enabled | tinyint(1) unsigned | NO | | 0 | | | gslb_service_enabled | tinyint(1) unsigned | NO | | 1 | | +----------------------------+---------------------+------+-----+---------+-------+ 5 rows in set (0.02 sec) ALTER TABLE `cloud`.`region` MODIFY `id` int unsigned AUTO_INCREMENT UNIQUE NOT NULL; ERROR 1833 (HY000): Cannot change column 'id': used in a foreign key constraint 'fk_global_load_balancing_rules_region_id' of table 'cloud.global_load_balancing_rules' mysql> As you might expect, greenfield 4.5 and 4.6 has this field set already. 4.5: mysql> describe region; +----------------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | UNI | NULL | | | end_point | varchar(255) | NO | | NULL | | | portableip_service_enabled | tinyint(1) unsigned | NO | | 0 | | | gslb_service_enabled | tinyint(1) unsigned | NO | | 1 | | +----------------------------+---------------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec) 4.6: mysql> describe region; +----------------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | UNI | NULL | | | end_point | varchar(255) | NO | | NULL | | | portableip_service_enabled | tinyint(1) unsigned | NO | | 0 | | | gslb_service_enabled | tinyint(1) unsigned | NO | | 1 | | +----------------------------+---------------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec) On 09 Apr 2015, at 06:00, Wido den Hollander <w...@widodh.nl<mailto:w...@widodh.nl><mailto:w...@widodh.nl>> wrote: -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 04/09/2015 12:26 PM, Remi Bergsma wrote: Hi Daan, If your doing another RC, consider including the fixes to the kvmheartbeat script. There’s a final PR I just submitted to make it work reliably. We’ve tested it in our employee cloud and it works fine. +1 Is it in the 4.4 branch already? Regards, Remi On 09 Apr 2015, at 11:27, Nux! <n...@li.nux.ro<mailto:n...@li.nux.ro><mailto:n...@li.nux.ro>> wrote: Daan, "33744ed658bbf29924bf79c0a651f88abcf793f9: appliance: build systemvm template for KVM compatible with older qemu" this seems important as System VMs will not be able to run on older qemu (namely CentOS 6). Since we've been taking our time with it, I wouldn't mind another quick RC, at least release something good. -- Sent from the Delta quadrant using Borg technology! Nux! www.nux.ro<http://www.nux.ro><http://www.nux.ro> ----- Original Message ----- From: "Daan Hoogland" <daan.hoogl...@gmail.com<mailto:daan.hoogl...@gmail.com><mailto:daan.hoogl...@gmail.com>> To: "dev" <dev@cloudstack.apache.org<mailto:dev@cloudstack.apache.org><mailto:dev@cloudstack.apache.org>> Sent: Thursday, 9 April, 2015 09:33:07 Subject: [DISCUSS] 4.4.3 rc H, I made a 4.4.3 rc a couple of weeks ago and it has tachnically enough votes to be released atm. There where some fixes in the last two weeks that are not in there however and it is no trouble to create a new rc. Should I/Can I? Or do we all feel the present state should be released: 33744ed658bbf29924bf79c0a651f88abcf793f9: appliance: build systemvm template for KVM compatible with older qemu 244c361b50c7de8b7b4299e8aabcad01efc12e35: appliance: remove any stale raw.img file 9a730d469c7d0baa51034761934791536260396e: CLOUDSTACK-6353: Fix list to have correct LB IP for each rule a79fd8816e1fb91f2df78332380f23ea47463481: Add CONTRIBUTING instructions These don't seem like heavy weighing issues but I still want to give opportunity to champion for any of them. -- Daan -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBAgAGBQJVJlvOAAoJEAGbWC3bPspCKiAP+QEZEZ0ulBKDP5xT9bBOIRy4 sJ8S6r2fAKP9/d1Y2uwLwAGVdehIAG9uEzxQI7C2ICpokvm2tZCzyHuRlpA18Xkb 3CEbk84SUFiq+riXmr9U0P8A7ukCEAwu6jpZVdwF3cjqPm1racNdzJRrAnNoOS8y TyK+5cJC76KQH4SbYgaOFB4qRZC5pUE3SmwwtDUwY6BBuQcgr6j3TBNHI+kX8ylk h6BpC7tzv0O+MoF9N7y09ITU5ViInbcIwtxAtPc7HEr58md4TnIkJegudPBvDV1Y 90HtQ4jfzTT7q3gQ5aCd0ezp+xbwCxAM+3bsFu6T3z9xEMj6cADOqxSDfxwGcvzT 1mnvRqtuEU2YRxhHm7RCNQilnh5PVrEr175jPOiNTo5srueEQp5BMBWPaQPm4uBK ei0wm4n0Igk6Qp43HbNRDYX9A28gMxtILf1cTavrIIzZVJIMW5FLpR076eDx8UZn tSQS3maHRZdOKAQMGjdUTsfic20WvwTYk8s6k4w4559TV9Wvx6zOFff1NociuFx/ Zww4RoAEzgo6xCyz/BeLMU9W2YnY70clt9YnpLqRLsghWmTXuLmjHwZLez4zYOaY dKjdcorFgUQt7qngV6FTwYLds2Ka+cTYkIPGJ4ivShQHa8hItMcFStyu/VFtfUY2 v7nLvATOEsE96hJqFw0O =qcKu -----END PGP SIGNATURE-----
