I’ve just sent a PR to fix this issue in master. We do need to port it to 4.4 and 4.5 though.
Regards, Remi On 13 Apr 2015, at 16:11, Daan Hoogland <daan.hoogl...@gmail.com> wrote: > Noem Rohit in de pull request. zijn bug > > On Mon, Apr 13, 2015 at 4:01 PM, Remi Bergsma > <rberg...@schubergphilis.com> wrote: >> 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----- >> >> > > > > -- > Daan
