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
