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:01:30 GMT
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-----



Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message