You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cloudstack.apache.org by Rohit Yadav <bh...@apache.org> on 2013/02/16 13:19:26 UTC
Watchout: Recent DB deployment changes
Hi, I recently fixed some db stuff for 4.1 release but on master,
mainly to support rolling release
(4.2 goal: https://issues.apache.org/jira/browse/CLOUDSTACK-1019)
What was done:
- Remove and merge 4.1-new-db-schema.sql to schema-40to410.sql
- Fix jetty's classpath so the db files for rolling updates are
readable: ${project.build.directory}/utilities/scripts/db/
- I've moved changes since 4.0 to: schema-40to410-cleanup.sql
schema-40to410.sql
- Add stubs for Upgrade410to420, schema-410to420.sql and
schema-410to420-cleanup.sql
- Fix building and deploy database
- Refactor new change in create-schema/premium to schema40-410.sql:
- Move changes since 4.0 to schema upgrade path (schema40-410.sql)
- Comment out some table names where we're trying to copy uuid from id, they
don't exists (which don't exist in 4.0 for example)
- We don't run above step for tables which are newly created for
410 and don't
exist in 4.0 for example autoscale related ones, code is
commented and not removed
- Drop indexes which are removed before dropping the column
- Comment out insertion, as for default region we're inserting the same in
code, in ConfigurationServerImpl:createDefaultRegion(), fix same
in premium
Latest difference between 4.0 and 4.1.0 schema (create-schema-view.sql
not included):
http://people.apache.org/~bhaisaab/diff40-410.diff
Any 4.2 db schema changes should go to:
schema-410to420-cleanup.sql schema-410to420.sql
And upgrade paths must be fixed in the class Upgrade410to420.
4.1 TODOs:
- Fix cleaning up path and schema-40to410-cleanup.sql
- In sql files, use full names like `cloud`.`table names` (Hugo comment?)
- Fix syntax, in some places it's lowercase, some cases it's uppercase
Our aim to make sure the schema is same as 4.0s' this means
http://people.apache.org/~bhaisaab/diff40-410.diff should be almost
None.
Edison, I got a conflict in schema40-410.sql (use the one in create-schema.sql):
@@ -171,7 +172,7 @@ CREATE TABLE `cloud`.`template_s3_ref` (
CONSTRAINT `uc_template_s3_ref__template_id` UNIQUE (`template_id`),
CONSTRAINT `fk_template_s3_ref__s3_id` FOREIGN KEY
`fk_template_s3_ref__s3_id` (`s3_id`) REFERENCES `s3` (`id`) ON DELETE
CASCADE,
CONSTRAINT `fk_template_s3_ref__template_id` FOREIGN KEY
`fk_template_s3_ref__template_id` (`template_id`) REFERENCES
`vm_template` (`id`),
- INDEX `i_template_s3_ref__swift_id`(`s3_id`),
+ INDEX `i_template_s3_ref__s3_id`(`s3_id`),
INDEX `i_template_s3_ref__template_id`(`template_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Also, I saw user_vm_temp, what is this table used for, I could not
find a creating definition, commented it out:
@@ -253,7 +262,7 @@ UPDATE `cloud`.`swift` set uuid=id WHERE uuid is NULL;
UPDATE `cloud`.`upload` set uuid=id WHERE uuid is NULL;
UPDATE `cloud`.`user` set uuid=id WHERE uuid is NULL;
UPDATE `cloud`.`user_ip_address` set uuid=id WHERE uuid is NULL;
-UPDATE `cloud`.`user_vm_temp` set uuid=id WHERE uuid is NULL;
+-- UPDATE `cloud`.`user_vm_temp` set uuid=id WHERE uuid is NULL;
UPDATE `cloud`.`virtual_router_providers` set uuid=id WHERE uuid is NULL;
Is it okay, if not pl. advise/fix it?
Sheng, can you identify and move any of the ipv6/4.2 stuff to
schema-410to420-cleanup.sql schema-410to420.sql as applicable.
Alex, rolling update works, tested for 4.0 to 4.1.0 :)
-INSERT INTO `version` (`version`, `updated`, `step`) VALUES('4.1.0',
now(), 'Complete');
+INSERT INTO `version` (`version`, `updated`, `step`) VALUES('4.0.0',
now(), 'Complete');
Kishan, for your regions changes, I commented out the default region
as this in persisted upon running mgmt server in
ConfigurationServerImpl:createDefaultRegion():
In schema-40to410.sql:
+-- INSERT INTO `cloud`.`region` values
('1','Local','http://localhost:8080/client/api','','');
+ALTER TABLE `cloud`.`account` ADD COLUMN `region_id` int unsigned NOT
NULL DEFAULT '1';
Pl. check?
Vijay, for your autoscale changes, I had to comment out in
schema-410to420.sql as these tables did not exist in 4.0, so we don't
need to fill uuid column with id.
@@ -264,23 +273,258 @@ UPDATE `cloud`.`vpc_gateways` set uuid=id WHERE
uuid is NULL;
UPDATE `cloud`.`vpc_offerings` set uuid=id WHERE uuid is NULL;
UPDATE `cloud`.`vpn_users` set uuid=id WHERE uuid is NULL;
UPDATE `cloud`.`volumes` set uuid=id WHERE uuid is NULL;
-UPDATE `cloud`.`autoscale_vmgroups` set uuid=id WHERE uuid is NULL;
-UPDATE `cloud`.`autoscale_vmprofiles` set uuid=id WHERE uuid is NULL;
-UPDATE `cloud`.`autoscale_policies` set uuid=id WHERE uuid is NULL;
-UPDATE `cloud`.`counter` set uuid=id WHERE uuid is NULL;
-UPDATE `cloud`.`conditions` set uuid=id WHERE uuid is NULL;
+-- UPDATE `cloud`.`autoscale_vmgroups` set uuid=id WHERE uuid is NULL;
+-- UPDATE `cloud`.`autoscale_vmprofiles` set uuid=id WHERE uuid is NULL;
+-- UPDATE `cloud`.`autoscale_policies` set uuid=id WHERE uuid is NULL;
+-- UPDATE `cloud`.`counter` set uuid=id WHERE uuid is NULL;
+-- UPDATE `cloud`.`conditions` set uuid=id WHERE uuid is NULL;
Hugo, I moved 4.0 post changes for ex. nicira_nvp_router_map to
schema-40to410.sql See if it's okay.
Min, I check both create-schema-view and schema-40to410.sql they were
in sync so we can just remove create-schema-view. I moved the create
view related definitions at the end of the schema-40to410.sql file
as it depends on some other tables being created for example counters
etc.
Changes we need to cherry pick on 4.1 branch if it's verified to work
fine; 87b668b71b34c93e9ba85d4708a1c04f4020f6bf..16e81130cca78d2a10ff47856e374d92fa4f3ecc
(except 0e354473f799fd3a387747c8fdb85d65ecac8fea)
Regards.
Re: Watchout: Recent DB deployment changes
Posted by Rohit Yadav <bh...@apache.org>.
Thanks Sheng.
I've fixed all the diversions I found, on 4.1 branch I was able to
deploydb, mgmt server did a rolling upgrade to 4.1.0, I was able to
deploy a basic zone and run basic vm life cycle smoke tests.
But if anything breaks you know this thread was on the ML that someone
failed to see :)
Regards.
On Wed, Feb 20, 2013 at 1:41 AM, Sheng Yang <sh...@yasker.org> wrote:
> Db upgrade path for ipv6 on 4.1/master done.
>
> --Sheng
>
> On Tue, Feb 19, 2013 at 3:07 AM, Rohit Yadav <bh...@apache.org> wrote:
>> Following people please reply:
>> Edison, Sheng, Kishan, Hugo Vijay.
>>
>> Koushik, please reply and fix the max host per cluster changes in
>> http://people.apache.org/~bhaisaab/diff40-410.diff
>>
>> Regards.
>>
>>
>> On Sat, Feb 16, 2013 at 5:49 PM, Rohit Yadav <bh...@apache.org> wrote:
>>> Hi, I recently fixed some db stuff for 4.1 release but on master,
>>> mainly to support rolling release
>>> (4.2 goal: https://issues.apache.org/jira/browse/CLOUDSTACK-1019)
>>>
>>> What was done:
>>> - Remove and merge 4.1-new-db-schema.sql to schema-40to410.sql
>>> - Fix jetty's classpath so the db files for rolling updates are
>>> readable: ${project.build.directory}/utilities/scripts/db/
>>> - I've moved changes since 4.0 to: schema-40to410-cleanup.sql
>>> schema-40to410.sql
>>> - Add stubs for Upgrade410to420, schema-410to420.sql and
>>> schema-410to420-cleanup.sql
>>> - Fix building and deploy database
>>> - Refactor new change in create-schema/premium to schema40-410.sql:
>>> - Move changes since 4.0 to schema upgrade path (schema40-410.sql)
>>> - Comment out some table names where we're trying to copy uuid from id, they
>>> don't exists (which don't exist in 4.0 for example)
>>> - We don't run above step for tables which are newly created for
>>> 410 and don't
>>> exist in 4.0 for example autoscale related ones, code is
>>> commented and not removed
>>> - Drop indexes which are removed before dropping the column
>>> - Comment out insertion, as for default region we're inserting the same in
>>> code, in ConfigurationServerImpl:createDefaultRegion(), fix same
>>> in premium
>>>
>>> Latest difference between 4.0 and 4.1.0 schema (create-schema-view.sql
>>> not included):
>>> http://people.apache.org/~bhaisaab/diff40-410.diff
>>>
>>> Any 4.2 db schema changes should go to:
>>> schema-410to420-cleanup.sql schema-410to420.sql
>>>
>>> And upgrade paths must be fixed in the class Upgrade410to420.
>>>
>>> 4.1 TODOs:
>>> - Fix cleaning up path and schema-40to410-cleanup.sql
>>> - In sql files, use full names like `cloud`.`table names` (Hugo comment?)
>>> - Fix syntax, in some places it's lowercase, some cases it's uppercase
>>>
>>> Our aim to make sure the schema is same as 4.0s' this means
>>> http://people.apache.org/~bhaisaab/diff40-410.diff should be almost
>>> None.
>>>
>>> Edison, I got a conflict in schema40-410.sql (use the one in create-schema.sql):
>>>
>>> @@ -171,7 +172,7 @@ CREATE TABLE `cloud`.`template_s3_ref` (
>>> CONSTRAINT `uc_template_s3_ref__template_id` UNIQUE (`template_id`),
>>> CONSTRAINT `fk_template_s3_ref__s3_id` FOREIGN KEY
>>> `fk_template_s3_ref__s3_id` (`s3_id`) REFERENCES `s3` (`id`) ON DELETE
>>> CASCADE,
>>> CONSTRAINT `fk_template_s3_ref__template_id` FOREIGN KEY
>>> `fk_template_s3_ref__template_id` (`template_id`) REFERENCES
>>> `vm_template` (`id`),
>>> - INDEX `i_template_s3_ref__swift_id`(`s3_id`),
>>> + INDEX `i_template_s3_ref__s3_id`(`s3_id`),
>>> INDEX `i_template_s3_ref__template_id`(`template_id`)
>>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>>>
>>> Also, I saw user_vm_temp, what is this table used for, I could not
>>> find a creating definition, commented it out:
>>>
>>> @@ -253,7 +262,7 @@ UPDATE `cloud`.`swift` set uuid=id WHERE uuid is NULL;
>>> UPDATE `cloud`.`upload` set uuid=id WHERE uuid is NULL;
>>> UPDATE `cloud`.`user` set uuid=id WHERE uuid is NULL;
>>> UPDATE `cloud`.`user_ip_address` set uuid=id WHERE uuid is NULL;
>>> -UPDATE `cloud`.`user_vm_temp` set uuid=id WHERE uuid is NULL;
>>> +-- UPDATE `cloud`.`user_vm_temp` set uuid=id WHERE uuid is NULL;
>>> UPDATE `cloud`.`virtual_router_providers` set uuid=id WHERE uuid is NULL;
>>>
>>> Is it okay, if not pl. advise/fix it?
>>>
>>> Sheng, can you identify and move any of the ipv6/4.2 stuff to
>>> schema-410to420-cleanup.sql schema-410to420.sql as applicable.
>>>
>>> Alex, rolling update works, tested for 4.0 to 4.1.0 :)
>>> -INSERT INTO `version` (`version`, `updated`, `step`) VALUES('4.1.0',
>>> now(), 'Complete');
>>> +INSERT INTO `version` (`version`, `updated`, `step`) VALUES('4.0.0',
>>> now(), 'Complete');
>>>
>>> Kishan, for your regions changes, I commented out the default region
>>> as this in persisted upon running mgmt server in
>>> ConfigurationServerImpl:createDefaultRegion():
>>>
>>> In schema-40to410.sql:
>>> +-- INSERT INTO `cloud`.`region` values
>>> ('1','Local','http://localhost:8080/client/api','','');
>>> +ALTER TABLE `cloud`.`account` ADD COLUMN `region_id` int unsigned NOT
>>> NULL DEFAULT '1';
>>>
>>> Pl. check?
>>>
>>> Vijay, for your autoscale changes, I had to comment out in
>>> schema-410to420.sql as these tables did not exist in 4.0, so we don't
>>> need to fill uuid column with id.
>>>
>>> @@ -264,23 +273,258 @@ UPDATE `cloud`.`vpc_gateways` set uuid=id WHERE
>>> uuid is NULL;
>>> UPDATE `cloud`.`vpc_offerings` set uuid=id WHERE uuid is NULL;
>>> UPDATE `cloud`.`vpn_users` set uuid=id WHERE uuid is NULL;
>>> UPDATE `cloud`.`volumes` set uuid=id WHERE uuid is NULL;
>>> -UPDATE `cloud`.`autoscale_vmgroups` set uuid=id WHERE uuid is NULL;
>>> -UPDATE `cloud`.`autoscale_vmprofiles` set uuid=id WHERE uuid is NULL;
>>> -UPDATE `cloud`.`autoscale_policies` set uuid=id WHERE uuid is NULL;
>>> -UPDATE `cloud`.`counter` set uuid=id WHERE uuid is NULL;
>>> -UPDATE `cloud`.`conditions` set uuid=id WHERE uuid is NULL;
>>> +-- UPDATE `cloud`.`autoscale_vmgroups` set uuid=id WHERE uuid is NULL;
>>> +-- UPDATE `cloud`.`autoscale_vmprofiles` set uuid=id WHERE uuid is NULL;
>>> +-- UPDATE `cloud`.`autoscale_policies` set uuid=id WHERE uuid is NULL;
>>> +-- UPDATE `cloud`.`counter` set uuid=id WHERE uuid is NULL;
>>> +-- UPDATE `cloud`.`conditions` set uuid=id WHERE uuid is NULL;
>>>
>>> Hugo, I moved 4.0 post changes for ex. nicira_nvp_router_map to
>>> schema-40to410.sql See if it's okay.
>>>
>>> Min, I check both create-schema-view and schema-40to410.sql they were
>>> in sync so we can just remove create-schema-view. I moved the create
>>> view related definitions at the end of the schema-40to410.sql file
>>> as it depends on some other tables being created for example counters
>>> etc.
>>>
>>> Changes we need to cherry pick on 4.1 branch if it's verified to work
>>> fine; 87b668b71b34c93e9ba85d4708a1c04f4020f6bf..16e81130cca78d2a10ff47856e374d92fa4f3ecc
>>> (except 0e354473f799fd3a387747c8fdb85d65ecac8fea)
>>>
>>>
>>> Regards.
Re: Watchout: Recent DB deployment changes
Posted by Sheng Yang <sh...@yasker.org>.
Db upgrade path for ipv6 on 4.1/master done.
--Sheng
On Tue, Feb 19, 2013 at 3:07 AM, Rohit Yadav <bh...@apache.org> wrote:
> Following people please reply:
> Edison, Sheng, Kishan, Hugo Vijay.
>
> Koushik, please reply and fix the max host per cluster changes in
> http://people.apache.org/~bhaisaab/diff40-410.diff
>
> Regards.
>
>
> On Sat, Feb 16, 2013 at 5:49 PM, Rohit Yadav <bh...@apache.org> wrote:
>> Hi, I recently fixed some db stuff for 4.1 release but on master,
>> mainly to support rolling release
>> (4.2 goal: https://issues.apache.org/jira/browse/CLOUDSTACK-1019)
>>
>> What was done:
>> - Remove and merge 4.1-new-db-schema.sql to schema-40to410.sql
>> - Fix jetty's classpath so the db files for rolling updates are
>> readable: ${project.build.directory}/utilities/scripts/db/
>> - I've moved changes since 4.0 to: schema-40to410-cleanup.sql
>> schema-40to410.sql
>> - Add stubs for Upgrade410to420, schema-410to420.sql and
>> schema-410to420-cleanup.sql
>> - Fix building and deploy database
>> - Refactor new change in create-schema/premium to schema40-410.sql:
>> - Move changes since 4.0 to schema upgrade path (schema40-410.sql)
>> - Comment out some table names where we're trying to copy uuid from id, they
>> don't exists (which don't exist in 4.0 for example)
>> - We don't run above step for tables which are newly created for
>> 410 and don't
>> exist in 4.0 for example autoscale related ones, code is
>> commented and not removed
>> - Drop indexes which are removed before dropping the column
>> - Comment out insertion, as for default region we're inserting the same in
>> code, in ConfigurationServerImpl:createDefaultRegion(), fix same
>> in premium
>>
>> Latest difference between 4.0 and 4.1.0 schema (create-schema-view.sql
>> not included):
>> http://people.apache.org/~bhaisaab/diff40-410.diff
>>
>> Any 4.2 db schema changes should go to:
>> schema-410to420-cleanup.sql schema-410to420.sql
>>
>> And upgrade paths must be fixed in the class Upgrade410to420.
>>
>> 4.1 TODOs:
>> - Fix cleaning up path and schema-40to410-cleanup.sql
>> - In sql files, use full names like `cloud`.`table names` (Hugo comment?)
>> - Fix syntax, in some places it's lowercase, some cases it's uppercase
>>
>> Our aim to make sure the schema is same as 4.0s' this means
>> http://people.apache.org/~bhaisaab/diff40-410.diff should be almost
>> None.
>>
>> Edison, I got a conflict in schema40-410.sql (use the one in create-schema.sql):
>>
>> @@ -171,7 +172,7 @@ CREATE TABLE `cloud`.`template_s3_ref` (
>> CONSTRAINT `uc_template_s3_ref__template_id` UNIQUE (`template_id`),
>> CONSTRAINT `fk_template_s3_ref__s3_id` FOREIGN KEY
>> `fk_template_s3_ref__s3_id` (`s3_id`) REFERENCES `s3` (`id`) ON DELETE
>> CASCADE,
>> CONSTRAINT `fk_template_s3_ref__template_id` FOREIGN KEY
>> `fk_template_s3_ref__template_id` (`template_id`) REFERENCES
>> `vm_template` (`id`),
>> - INDEX `i_template_s3_ref__swift_id`(`s3_id`),
>> + INDEX `i_template_s3_ref__s3_id`(`s3_id`),
>> INDEX `i_template_s3_ref__template_id`(`template_id`)
>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>>
>> Also, I saw user_vm_temp, what is this table used for, I could not
>> find a creating definition, commented it out:
>>
>> @@ -253,7 +262,7 @@ UPDATE `cloud`.`swift` set uuid=id WHERE uuid is NULL;
>> UPDATE `cloud`.`upload` set uuid=id WHERE uuid is NULL;
>> UPDATE `cloud`.`user` set uuid=id WHERE uuid is NULL;
>> UPDATE `cloud`.`user_ip_address` set uuid=id WHERE uuid is NULL;
>> -UPDATE `cloud`.`user_vm_temp` set uuid=id WHERE uuid is NULL;
>> +-- UPDATE `cloud`.`user_vm_temp` set uuid=id WHERE uuid is NULL;
>> UPDATE `cloud`.`virtual_router_providers` set uuid=id WHERE uuid is NULL;
>>
>> Is it okay, if not pl. advise/fix it?
>>
>> Sheng, can you identify and move any of the ipv6/4.2 stuff to
>> schema-410to420-cleanup.sql schema-410to420.sql as applicable.
>>
>> Alex, rolling update works, tested for 4.0 to 4.1.0 :)
>> -INSERT INTO `version` (`version`, `updated`, `step`) VALUES('4.1.0',
>> now(), 'Complete');
>> +INSERT INTO `version` (`version`, `updated`, `step`) VALUES('4.0.0',
>> now(), 'Complete');
>>
>> Kishan, for your regions changes, I commented out the default region
>> as this in persisted upon running mgmt server in
>> ConfigurationServerImpl:createDefaultRegion():
>>
>> In schema-40to410.sql:
>> +-- INSERT INTO `cloud`.`region` values
>> ('1','Local','http://localhost:8080/client/api','','');
>> +ALTER TABLE `cloud`.`account` ADD COLUMN `region_id` int unsigned NOT
>> NULL DEFAULT '1';
>>
>> Pl. check?
>>
>> Vijay, for your autoscale changes, I had to comment out in
>> schema-410to420.sql as these tables did not exist in 4.0, so we don't
>> need to fill uuid column with id.
>>
>> @@ -264,23 +273,258 @@ UPDATE `cloud`.`vpc_gateways` set uuid=id WHERE
>> uuid is NULL;
>> UPDATE `cloud`.`vpc_offerings` set uuid=id WHERE uuid is NULL;
>> UPDATE `cloud`.`vpn_users` set uuid=id WHERE uuid is NULL;
>> UPDATE `cloud`.`volumes` set uuid=id WHERE uuid is NULL;
>> -UPDATE `cloud`.`autoscale_vmgroups` set uuid=id WHERE uuid is NULL;
>> -UPDATE `cloud`.`autoscale_vmprofiles` set uuid=id WHERE uuid is NULL;
>> -UPDATE `cloud`.`autoscale_policies` set uuid=id WHERE uuid is NULL;
>> -UPDATE `cloud`.`counter` set uuid=id WHERE uuid is NULL;
>> -UPDATE `cloud`.`conditions` set uuid=id WHERE uuid is NULL;
>> +-- UPDATE `cloud`.`autoscale_vmgroups` set uuid=id WHERE uuid is NULL;
>> +-- UPDATE `cloud`.`autoscale_vmprofiles` set uuid=id WHERE uuid is NULL;
>> +-- UPDATE `cloud`.`autoscale_policies` set uuid=id WHERE uuid is NULL;
>> +-- UPDATE `cloud`.`counter` set uuid=id WHERE uuid is NULL;
>> +-- UPDATE `cloud`.`conditions` set uuid=id WHERE uuid is NULL;
>>
>> Hugo, I moved 4.0 post changes for ex. nicira_nvp_router_map to
>> schema-40to410.sql See if it's okay.
>>
>> Min, I check both create-schema-view and schema-40to410.sql they were
>> in sync so we can just remove create-schema-view. I moved the create
>> view related definitions at the end of the schema-40to410.sql file
>> as it depends on some other tables being created for example counters
>> etc.
>>
>> Changes we need to cherry pick on 4.1 branch if it's verified to work
>> fine; 87b668b71b34c93e9ba85d4708a1c04f4020f6bf..16e81130cca78d2a10ff47856e374d92fa4f3ecc
>> (except 0e354473f799fd3a387747c8fdb85d65ecac8fea)
>>
>>
>> Regards.
Re: Watchout: Recent DB deployment changes
Posted by Rohit Yadav <bh...@apache.org>.
Following people please reply:
Edison, Sheng, Kishan, Hugo Vijay.
Koushik, please reply and fix the max host per cluster changes in
http://people.apache.org/~bhaisaab/diff40-410.diff
Regards.
On Sat, Feb 16, 2013 at 5:49 PM, Rohit Yadav <bh...@apache.org> wrote:
> Hi, I recently fixed some db stuff for 4.1 release but on master,
> mainly to support rolling release
> (4.2 goal: https://issues.apache.org/jira/browse/CLOUDSTACK-1019)
>
> What was done:
> - Remove and merge 4.1-new-db-schema.sql to schema-40to410.sql
> - Fix jetty's classpath so the db files for rolling updates are
> readable: ${project.build.directory}/utilities/scripts/db/
> - I've moved changes since 4.0 to: schema-40to410-cleanup.sql
> schema-40to410.sql
> - Add stubs for Upgrade410to420, schema-410to420.sql and
> schema-410to420-cleanup.sql
> - Fix building and deploy database
> - Refactor new change in create-schema/premium to schema40-410.sql:
> - Move changes since 4.0 to schema upgrade path (schema40-410.sql)
> - Comment out some table names where we're trying to copy uuid from id, they
> don't exists (which don't exist in 4.0 for example)
> - We don't run above step for tables which are newly created for
> 410 and don't
> exist in 4.0 for example autoscale related ones, code is
> commented and not removed
> - Drop indexes which are removed before dropping the column
> - Comment out insertion, as for default region we're inserting the same in
> code, in ConfigurationServerImpl:createDefaultRegion(), fix same
> in premium
>
> Latest difference between 4.0 and 4.1.0 schema (create-schema-view.sql
> not included):
> http://people.apache.org/~bhaisaab/diff40-410.diff
>
> Any 4.2 db schema changes should go to:
> schema-410to420-cleanup.sql schema-410to420.sql
>
> And upgrade paths must be fixed in the class Upgrade410to420.
>
> 4.1 TODOs:
> - Fix cleaning up path and schema-40to410-cleanup.sql
> - In sql files, use full names like `cloud`.`table names` (Hugo comment?)
> - Fix syntax, in some places it's lowercase, some cases it's uppercase
>
> Our aim to make sure the schema is same as 4.0s' this means
> http://people.apache.org/~bhaisaab/diff40-410.diff should be almost
> None.
>
> Edison, I got a conflict in schema40-410.sql (use the one in create-schema.sql):
>
> @@ -171,7 +172,7 @@ CREATE TABLE `cloud`.`template_s3_ref` (
> CONSTRAINT `uc_template_s3_ref__template_id` UNIQUE (`template_id`),
> CONSTRAINT `fk_template_s3_ref__s3_id` FOREIGN KEY
> `fk_template_s3_ref__s3_id` (`s3_id`) REFERENCES `s3` (`id`) ON DELETE
> CASCADE,
> CONSTRAINT `fk_template_s3_ref__template_id` FOREIGN KEY
> `fk_template_s3_ref__template_id` (`template_id`) REFERENCES
> `vm_template` (`id`),
> - INDEX `i_template_s3_ref__swift_id`(`s3_id`),
> + INDEX `i_template_s3_ref__s3_id`(`s3_id`),
> INDEX `i_template_s3_ref__template_id`(`template_id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
> Also, I saw user_vm_temp, what is this table used for, I could not
> find a creating definition, commented it out:
>
> @@ -253,7 +262,7 @@ UPDATE `cloud`.`swift` set uuid=id WHERE uuid is NULL;
> UPDATE `cloud`.`upload` set uuid=id WHERE uuid is NULL;
> UPDATE `cloud`.`user` set uuid=id WHERE uuid is NULL;
> UPDATE `cloud`.`user_ip_address` set uuid=id WHERE uuid is NULL;
> -UPDATE `cloud`.`user_vm_temp` set uuid=id WHERE uuid is NULL;
> +-- UPDATE `cloud`.`user_vm_temp` set uuid=id WHERE uuid is NULL;
> UPDATE `cloud`.`virtual_router_providers` set uuid=id WHERE uuid is NULL;
>
> Is it okay, if not pl. advise/fix it?
>
> Sheng, can you identify and move any of the ipv6/4.2 stuff to
> schema-410to420-cleanup.sql schema-410to420.sql as applicable.
>
> Alex, rolling update works, tested for 4.0 to 4.1.0 :)
> -INSERT INTO `version` (`version`, `updated`, `step`) VALUES('4.1.0',
> now(), 'Complete');
> +INSERT INTO `version` (`version`, `updated`, `step`) VALUES('4.0.0',
> now(), 'Complete');
>
> Kishan, for your regions changes, I commented out the default region
> as this in persisted upon running mgmt server in
> ConfigurationServerImpl:createDefaultRegion():
>
> In schema-40to410.sql:
> +-- INSERT INTO `cloud`.`region` values
> ('1','Local','http://localhost:8080/client/api','','');
> +ALTER TABLE `cloud`.`account` ADD COLUMN `region_id` int unsigned NOT
> NULL DEFAULT '1';
>
> Pl. check?
>
> Vijay, for your autoscale changes, I had to comment out in
> schema-410to420.sql as these tables did not exist in 4.0, so we don't
> need to fill uuid column with id.
>
> @@ -264,23 +273,258 @@ UPDATE `cloud`.`vpc_gateways` set uuid=id WHERE
> uuid is NULL;
> UPDATE `cloud`.`vpc_offerings` set uuid=id WHERE uuid is NULL;
> UPDATE `cloud`.`vpn_users` set uuid=id WHERE uuid is NULL;
> UPDATE `cloud`.`volumes` set uuid=id WHERE uuid is NULL;
> -UPDATE `cloud`.`autoscale_vmgroups` set uuid=id WHERE uuid is NULL;
> -UPDATE `cloud`.`autoscale_vmprofiles` set uuid=id WHERE uuid is NULL;
> -UPDATE `cloud`.`autoscale_policies` set uuid=id WHERE uuid is NULL;
> -UPDATE `cloud`.`counter` set uuid=id WHERE uuid is NULL;
> -UPDATE `cloud`.`conditions` set uuid=id WHERE uuid is NULL;
> +-- UPDATE `cloud`.`autoscale_vmgroups` set uuid=id WHERE uuid is NULL;
> +-- UPDATE `cloud`.`autoscale_vmprofiles` set uuid=id WHERE uuid is NULL;
> +-- UPDATE `cloud`.`autoscale_policies` set uuid=id WHERE uuid is NULL;
> +-- UPDATE `cloud`.`counter` set uuid=id WHERE uuid is NULL;
> +-- UPDATE `cloud`.`conditions` set uuid=id WHERE uuid is NULL;
>
> Hugo, I moved 4.0 post changes for ex. nicira_nvp_router_map to
> schema-40to410.sql See if it's okay.
>
> Min, I check both create-schema-view and schema-40to410.sql they were
> in sync so we can just remove create-schema-view. I moved the create
> view related definitions at the end of the schema-40to410.sql file
> as it depends on some other tables being created for example counters
> etc.
>
> Changes we need to cherry pick on 4.1 branch if it's verified to work
> fine; 87b668b71b34c93e9ba85d4708a1c04f4020f6bf..16e81130cca78d2a10ff47856e374d92fa4f3ecc
> (except 0e354473f799fd3a387747c8fdb85d65ecac8fea)
>
>
> Regards.
Re: Watchout: Recent DB deployment changes
Posted by Rohit Yadav <bh...@apache.org>.
Thanks Min, fixed on master with 31d6f03308aa10a170d5a37a3d6f8d572cf9e7c7
Will backport all useful db changes for 4.1 as soon as other hackers
can help verify the changes.
Regards.
On Sun, Feb 17, 2013 at 1:55 AM, Min Chen <mi...@citrix.com> wrote:
> Your fix for create-schema-view is correct, just remove that SQL since I kept them in upgrade script as well.
>
> Thanks
> -min
>
> Sent from my iPhone
>
> On Feb 16, 2013, at 4:20 AM, "Rohit Yadav" <bh...@apache.org> wrote:
>
>> Hi, I recently fixed some db stuff for 4.1 release but on master,
>> mainly to support rolling release
>> (4.2 goal: https://issues.apache.org/jira/browse/CLOUDSTACK-1019)
>>
>> What was done:
>> - Remove and merge 4.1-new-db-schema.sql to schema-40to410.sql
>> - Fix jetty's classpath so the db files for rolling updates are
>> readable: ${project.build.directory}/utilities/scripts/db/
>> - I've moved changes since 4.0 to: schema-40to410-cleanup.sql
>> schema-40to410.sql
>> - Add stubs for Upgrade410to420, schema-410to420.sql and
>> schema-410to420-cleanup.sql
>> - Fix building and deploy database
>> - Refactor new change in create-schema/premium to schema40-410.sql:
>> - Move changes since 4.0 to schema upgrade path (schema40-410.sql)
>> - Comment out some table names where we're trying to copy uuid from id, they
>> don't exists (which don't exist in 4.0 for example)
>> - We don't run above step for tables which are newly created for
>> 410 and don't
>> exist in 4.0 for example autoscale related ones, code is
>> commented and not removed
>> - Drop indexes which are removed before dropping the column
>> - Comment out insertion, as for default region we're inserting the same in
>> code, in ConfigurationServerImpl:createDefaultRegion(), fix same
>> in premium
>>
>> Latest difference between 4.0 and 4.1.0 schema (create-schema-view.sql
>> not included):
>> http://people.apache.org/~bhaisaab/diff40-410.diff
>>
>> Any 4.2 db schema changes should go to:
>> schema-410to420-cleanup.sql schema-410to420.sql
>>
>> And upgrade paths must be fixed in the class Upgrade410to420.
>>
>> 4.1 TODOs:
>> - Fix cleaning up path and schema-40to410-cleanup.sql
>> - In sql files, use full names like `cloud`.`table names` (Hugo comment?)
>> - Fix syntax, in some places it's lowercase, some cases it's uppercase
>>
>> Our aim to make sure the schema is same as 4.0s' this means
>> http://people.apache.org/~bhaisaab/diff40-410.diff should be almost
>> None.
>>
>> Edison, I got a conflict in schema40-410.sql (use the one in create-schema.sql):
>>
>> @@ -171,7 +172,7 @@ CREATE TABLE `cloud`.`template_s3_ref` (
>> CONSTRAINT `uc_template_s3_ref__template_id` UNIQUE (`template_id`),
>> CONSTRAINT `fk_template_s3_ref__s3_id` FOREIGN KEY
>> `fk_template_s3_ref__s3_id` (`s3_id`) REFERENCES `s3` (`id`) ON DELETE
>> CASCADE,
>> CONSTRAINT `fk_template_s3_ref__template_id` FOREIGN KEY
>> `fk_template_s3_ref__template_id` (`template_id`) REFERENCES
>> `vm_template` (`id`),
>> - INDEX `i_template_s3_ref__swift_id`(`s3_id`),
>> + INDEX `i_template_s3_ref__s3_id`(`s3_id`),
>> INDEX `i_template_s3_ref__template_id`(`template_id`)
>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>>
>> Also, I saw user_vm_temp, what is this table used for, I could not
>> find a creating definition, commented it out:
>>
>> @@ -253,7 +262,7 @@ UPDATE `cloud`.`swift` set uuid=id WHERE uuid is NULL;
>> UPDATE `cloud`.`upload` set uuid=id WHERE uuid is NULL;
>> UPDATE `cloud`.`user` set uuid=id WHERE uuid is NULL;
>> UPDATE `cloud`.`user_ip_address` set uuid=id WHERE uuid is NULL;
>> -UPDATE `cloud`.`user_vm_temp` set uuid=id WHERE uuid is NULL;
>> +-- UPDATE `cloud`.`user_vm_temp` set uuid=id WHERE uuid is NULL;
>> UPDATE `cloud`.`virtual_router_providers` set uuid=id WHERE uuid is NULL;
>>
>> Is it okay, if not pl. advise/fix it?
>>
>> Sheng, can you identify and move any of the ipv6/4.2 stuff to
>> schema-410to420-cleanup.sql schema-410to420.sql as applicable.
>>
>> Alex, rolling update works, tested for 4.0 to 4.1.0 :)
>> -INSERT INTO `version` (`version`, `updated`, `step`) VALUES('4.1.0',
>> now(), 'Complete');
>> +INSERT INTO `version` (`version`, `updated`, `step`) VALUES('4.0.0',
>> now(), 'Complete');
>>
>> Kishan, for your regions changes, I commented out the default region
>> as this in persisted upon running mgmt server in
>> ConfigurationServerImpl:createDefaultRegion():
>>
>> In schema-40to410.sql:
>> +-- INSERT INTO `cloud`.`region` values
>> ('1','Local','http://localhost:8080/client/api','','');
>> +ALTER TABLE `cloud`.`account` ADD COLUMN `region_id` int unsigned NOT
>> NULL DEFAULT '1';
>>
>> Pl. check?
>>
>> Vijay, for your autoscale changes, I had to comment out in
>> schema-410to420.sql as these tables did not exist in 4.0, so we don't
>> need to fill uuid column with id.
>>
>> @@ -264,23 +273,258 @@ UPDATE `cloud`.`vpc_gateways` set uuid=id WHERE
>> uuid is NULL;
>> UPDATE `cloud`.`vpc_offerings` set uuid=id WHERE uuid is NULL;
>> UPDATE `cloud`.`vpn_users` set uuid=id WHERE uuid is NULL;
>> UPDATE `cloud`.`volumes` set uuid=id WHERE uuid is NULL;
>> -UPDATE `cloud`.`autoscale_vmgroups` set uuid=id WHERE uuid is NULL;
>> -UPDATE `cloud`.`autoscale_vmprofiles` set uuid=id WHERE uuid is NULL;
>> -UPDATE `cloud`.`autoscale_policies` set uuid=id WHERE uuid is NULL;
>> -UPDATE `cloud`.`counter` set uuid=id WHERE uuid is NULL;
>> -UPDATE `cloud`.`conditions` set uuid=id WHERE uuid is NULL;
>> +-- UPDATE `cloud`.`autoscale_vmgroups` set uuid=id WHERE uuid is NULL;
>> +-- UPDATE `cloud`.`autoscale_vmprofiles` set uuid=id WHERE uuid is NULL;
>> +-- UPDATE `cloud`.`autoscale_policies` set uuid=id WHERE uuid is NULL;
>> +-- UPDATE `cloud`.`counter` set uuid=id WHERE uuid is NULL;
>> +-- UPDATE `cloud`.`conditions` set uuid=id WHERE uuid is NULL;
>>
>> Hugo, I moved 4.0 post changes for ex. nicira_nvp_router_map to
>> schema-40to410.sql See if it's okay.
>>
>> Min, I check both create-schema-view and schema-40to410.sql they were
>> in sync so we can just remove create-schema-view. I moved the create
>> view related definitions at the end of the schema-40to410.sql file
>> as it depends on some other tables being created for example counters
>> etc.
>>
>> Changes we need to cherry pick on 4.1 branch if it's verified to work
>> fine; 87b668b71b34c93e9ba85d4708a1c04f4020f6bf..16e81130cca78d2a10ff47856e374d92fa4f3ecc
>> (except 0e354473f799fd3a387747c8fdb85d65ecac8fea)
>>
>>
>> Regards.
Re: Watchout: Recent DB deployment changes
Posted by Min Chen <mi...@citrix.com>.
Your fix for create-schema-view is correct, just remove that SQL since I kept them in upgrade script as well.
Thanks
-min
Sent from my iPhone
On Feb 16, 2013, at 4:20 AM, "Rohit Yadav" <bh...@apache.org> wrote:
> Hi, I recently fixed some db stuff for 4.1 release but on master,
> mainly to support rolling release
> (4.2 goal: https://issues.apache.org/jira/browse/CLOUDSTACK-1019)
>
> What was done:
> - Remove and merge 4.1-new-db-schema.sql to schema-40to410.sql
> - Fix jetty's classpath so the db files for rolling updates are
> readable: ${project.build.directory}/utilities/scripts/db/
> - I've moved changes since 4.0 to: schema-40to410-cleanup.sql
> schema-40to410.sql
> - Add stubs for Upgrade410to420, schema-410to420.sql and
> schema-410to420-cleanup.sql
> - Fix building and deploy database
> - Refactor new change in create-schema/premium to schema40-410.sql:
> - Move changes since 4.0 to schema upgrade path (schema40-410.sql)
> - Comment out some table names where we're trying to copy uuid from id, they
> don't exists (which don't exist in 4.0 for example)
> - We don't run above step for tables which are newly created for
> 410 and don't
> exist in 4.0 for example autoscale related ones, code is
> commented and not removed
> - Drop indexes which are removed before dropping the column
> - Comment out insertion, as for default region we're inserting the same in
> code, in ConfigurationServerImpl:createDefaultRegion(), fix same
> in premium
>
> Latest difference between 4.0 and 4.1.0 schema (create-schema-view.sql
> not included):
> http://people.apache.org/~bhaisaab/diff40-410.diff
>
> Any 4.2 db schema changes should go to:
> schema-410to420-cleanup.sql schema-410to420.sql
>
> And upgrade paths must be fixed in the class Upgrade410to420.
>
> 4.1 TODOs:
> - Fix cleaning up path and schema-40to410-cleanup.sql
> - In sql files, use full names like `cloud`.`table names` (Hugo comment?)
> - Fix syntax, in some places it's lowercase, some cases it's uppercase
>
> Our aim to make sure the schema is same as 4.0s' this means
> http://people.apache.org/~bhaisaab/diff40-410.diff should be almost
> None.
>
> Edison, I got a conflict in schema40-410.sql (use the one in create-schema.sql):
>
> @@ -171,7 +172,7 @@ CREATE TABLE `cloud`.`template_s3_ref` (
> CONSTRAINT `uc_template_s3_ref__template_id` UNIQUE (`template_id`),
> CONSTRAINT `fk_template_s3_ref__s3_id` FOREIGN KEY
> `fk_template_s3_ref__s3_id` (`s3_id`) REFERENCES `s3` (`id`) ON DELETE
> CASCADE,
> CONSTRAINT `fk_template_s3_ref__template_id` FOREIGN KEY
> `fk_template_s3_ref__template_id` (`template_id`) REFERENCES
> `vm_template` (`id`),
> - INDEX `i_template_s3_ref__swift_id`(`s3_id`),
> + INDEX `i_template_s3_ref__s3_id`(`s3_id`),
> INDEX `i_template_s3_ref__template_id`(`template_id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
> Also, I saw user_vm_temp, what is this table used for, I could not
> find a creating definition, commented it out:
>
> @@ -253,7 +262,7 @@ UPDATE `cloud`.`swift` set uuid=id WHERE uuid is NULL;
> UPDATE `cloud`.`upload` set uuid=id WHERE uuid is NULL;
> UPDATE `cloud`.`user` set uuid=id WHERE uuid is NULL;
> UPDATE `cloud`.`user_ip_address` set uuid=id WHERE uuid is NULL;
> -UPDATE `cloud`.`user_vm_temp` set uuid=id WHERE uuid is NULL;
> +-- UPDATE `cloud`.`user_vm_temp` set uuid=id WHERE uuid is NULL;
> UPDATE `cloud`.`virtual_router_providers` set uuid=id WHERE uuid is NULL;
>
> Is it okay, if not pl. advise/fix it?
>
> Sheng, can you identify and move any of the ipv6/4.2 stuff to
> schema-410to420-cleanup.sql schema-410to420.sql as applicable.
>
> Alex, rolling update works, tested for 4.0 to 4.1.0 :)
> -INSERT INTO `version` (`version`, `updated`, `step`) VALUES('4.1.0',
> now(), 'Complete');
> +INSERT INTO `version` (`version`, `updated`, `step`) VALUES('4.0.0',
> now(), 'Complete');
>
> Kishan, for your regions changes, I commented out the default region
> as this in persisted upon running mgmt server in
> ConfigurationServerImpl:createDefaultRegion():
>
> In schema-40to410.sql:
> +-- INSERT INTO `cloud`.`region` values
> ('1','Local','http://localhost:8080/client/api','','');
> +ALTER TABLE `cloud`.`account` ADD COLUMN `region_id` int unsigned NOT
> NULL DEFAULT '1';
>
> Pl. check?
>
> Vijay, for your autoscale changes, I had to comment out in
> schema-410to420.sql as these tables did not exist in 4.0, so we don't
> need to fill uuid column with id.
>
> @@ -264,23 +273,258 @@ UPDATE `cloud`.`vpc_gateways` set uuid=id WHERE
> uuid is NULL;
> UPDATE `cloud`.`vpc_offerings` set uuid=id WHERE uuid is NULL;
> UPDATE `cloud`.`vpn_users` set uuid=id WHERE uuid is NULL;
> UPDATE `cloud`.`volumes` set uuid=id WHERE uuid is NULL;
> -UPDATE `cloud`.`autoscale_vmgroups` set uuid=id WHERE uuid is NULL;
> -UPDATE `cloud`.`autoscale_vmprofiles` set uuid=id WHERE uuid is NULL;
> -UPDATE `cloud`.`autoscale_policies` set uuid=id WHERE uuid is NULL;
> -UPDATE `cloud`.`counter` set uuid=id WHERE uuid is NULL;
> -UPDATE `cloud`.`conditions` set uuid=id WHERE uuid is NULL;
> +-- UPDATE `cloud`.`autoscale_vmgroups` set uuid=id WHERE uuid is NULL;
> +-- UPDATE `cloud`.`autoscale_vmprofiles` set uuid=id WHERE uuid is NULL;
> +-- UPDATE `cloud`.`autoscale_policies` set uuid=id WHERE uuid is NULL;
> +-- UPDATE `cloud`.`counter` set uuid=id WHERE uuid is NULL;
> +-- UPDATE `cloud`.`conditions` set uuid=id WHERE uuid is NULL;
>
> Hugo, I moved 4.0 post changes for ex. nicira_nvp_router_map to
> schema-40to410.sql See if it's okay.
>
> Min, I check both create-schema-view and schema-40to410.sql they were
> in sync so we can just remove create-schema-view. I moved the create
> view related definitions at the end of the schema-40to410.sql file
> as it depends on some other tables being created for example counters
> etc.
>
> Changes we need to cherry pick on 4.1 branch if it's verified to work
> fine; 87b668b71b34c93e9ba85d4708a1c04f4020f6bf..16e81130cca78d2a10ff47856e374d92fa4f3ecc
> (except 0e354473f799fd3a387747c8fdb85d65ecac8fea)
>
>
> Regards.