cloudstack-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Remi Bergsma <RBerg...@schubergphilis.com>
Subject Re: Upgrade 4.4.x to 4.5.x and above fails with sql error
Date Mon, 13 Apr 2015 21:50:01 GMT
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.hoogland@gmail.com> wrote:

> Noem Rohit in de pull request. zijn bug
> 
> On Mon, Apr 13, 2015 at 4:01 PM, Remi Bergsma
> <RBergsma@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 <RBergsma@schubergphilis.com<mailto:RBergsma@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 <wido@widodh.nl<mailto:wido@widodh.nl><mailto:wido@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! <nux@li.nux.ro<mailto:nux@li.nux.ro><mailto:nux@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.hoogland@gmail.com<mailto:daan.hoogland@gmail.com><mailto:daan.hoogland@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


Mime
View raw message