Hi, Rafael. The first procedure creation
CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_COLUMN` (
IN in_table_name VARCHAR(200)
, IN in_column_name VARCHAR(200)
, IN in_column_definition VARCHAR(1000)
)
BEGIN
DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl =
CONCAT('ALTER TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ',
'ADD COLUMN') ; SET @ddl = CONCAT(@ddl, ' ', in_column_name); SET @ddl
= CONCAT(@ddl, ' ', in_column_definition); PREPARE stmt FROM @ddl;
EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
Doesn't work in freshly installed mysql, I suppose other similar
expressions fail too without custom delimiter specification, at least I see
that in native mysql console. I found that during upgrade from 4.10 to
4.11.1 the tables weren't altered (take a look at the first e-mail), so I
started to find where that alteration happens and found the file
https://github.com/apache/cloudstack/blob/9187392ff9cd33b53d5b251076fee22b02743477/engine/schema/src/main/resources/META-INF/db/schema-41000to41100.sql
where that stored procedure is created, it seems that a migration process
failed to run that file content during the migration, otherwise, I don't
catch how it could happen, that whole file or its part skipped during 4.10
to 4.11 upgrade.
May be the migration code includes the delimiter, but whole file is skipped
during db upgrade by the unknown reason, I haven't looked to the migration
code, so don't have other details, but I know for sure that migration took
place because it was failing until I uploaded new system vm template and
"looks-like-succeeded" without errors after that.
сб, 26 мая 2018 г., 19:03 Rafael Weingärtner <rafaelweingart...@gmail.com>:
> What line are you talking about?
>
> On Sat, May 26, 2018 at 3:54 AM, Ivan Kudryavtsev <
> kudryavtsev...@bw-sw.com>
> wrote:
>
> > After I manually set delimiter ENDTHAT in the beginning of the file and
> in
> > the end, expressions have been correctly processed and upgrade path
> > completed well. I was able to start 4.11.1 and it functions well.
> >
> > 2018-05-26 12:52 GMT+07:00 Ivan Kudryavtsev <kudryavtsev...@bw-sw.com>:
> >
> > > The problem is with that file:
> > >
> > >
> https://github.com/apache/cloudstack/blob/9187392ff9cd33b53d5b251076fee2
> > > 2b02743477/engine/schema/src/main/resources/META-INF/db/
> > > schema-41000to41100.sql
> > >
> > > The expressions don't run on mysql and maria as well without the
> > delimiter.
> > >
> > > 2018-05-26 12:27 GMT+07:00 Ivan Kudryavtsev <kudryavtsev...@bw-sw.com
> >:
> > >
> > >> Hello, I found that db upgrade routing is incompatible at least with
> > >> MariaDB 10.2.10
> > >>
> > >> 2018-05-26 12:03 GMT+07:00 Ivan Kudryavtsev <kudryavtsev...@bw-sw.com
> >:
> > >>
> > >>> Hello, Devs. During upgrade I got an error about missing column:
> > >>>
> > >>> SELECT network_offerings.id, network_offerings.name,
> > >>> network_offerings.unique_name, network_offerings.display_text,
> > >>> network_offerings.nw_rate, network_offerings.mc_rate,
> > >>> network_offerings.traffic_type, network_offerings.specify_vlan,
> > >>> network_offerings.system_only, network_offerings.service_offering_id,
> > >>> network_offerings.tags, network_offerings.default,
> > >>> network_offerings.availability, network_offerings.state,
> > >>> network_offerings.removed, network_offerings.created,
> > >>> network_offerings.guest_type, network_offerings.dedicated_lb_service,
> > >>> network_offerings.shared_source_nat_service,
> > >>> network_offerings.specify_ip_ranges, network_offerings.sort_key,
> > >>> network_offerings.uuid, network_offerings.redundant_router_service,
> > >>> network_offerings.conserve_mode,
> network_offerings.elastic_ip_service,
> > >>> network_offerings.eip_associate_public_ip,
> > >>> network_offerings.elastic_lb_service, network_offerings.inline,
> > >>> network_offerings.is_persistent, network_offerings.for_vpc,
> > >>> network_offerings.egress_default_policy,
> network_offerings.concurrent_
> > connections,
> > >>> network_offerings.keep_alive_enabled, network_offerings.supports_
> > streched_l2,
> > >>> network_offerings.supports_public_access,
> > >>> network_offerings.internal_lb, network_offerings.public_lb,
> > >>> network_offerings.service_package_id FROM network_offerings WHERE
> > >>> network_offerings.unique_name = _binary'QuickCloudNoServices' AND
> > >>> network_offerings.removed IS NULL ORDER BY RAND() LIMIT 1;
> > >>> ERROR 1054 (42S22): Unknown column 'network_offerings.for_vpc' in
> > 'field
> > >>> list'
> > >>>
> > >>> I have read
> > >>> https://markmail.org/message/f42kqr3mx4r4hgih
> > >>>
> > >>> But it seems nothing connected to the error in proposed SQL
> expressions
> > >>> fixes.
> > >>>
> > >>> --
> > >>> With best regards, Ivan Kudryavtsev
> > >>> Bitworks Software, Ltd.
> > >>> Cell: +7-923-414-1515
> > >>> WWW: http://bitworks.software/ <http://bw-sw.com/>
> > >>>
> > >>>
> > >>
> > >>
> > >> --
> > >> With best regards, Ivan Kudryavtsev
> > >> Bitworks Software, Ltd.
> > >> Cell: +7-923-414-1515
> > >> WWW: http://bitworks.software/ <http://bw-sw.com/>
> > >>
> > >>
> > >
> > >
> > > --
> > > With best regards, Ivan Kudryavtsev
> > > Bitworks Software, Ltd.
> > > Cell: +7-923-414-1515
> > > WWW: http://bitworks.software/ <http://bw-sw.com/>
> > >
> > >
> >
> >
> > --
> > With best regards, Ivan Kudryavtsev
> > Bitworks Software, Ltd.
> > Cell: +7-923-414-1515
> > WWW: http://bitworks.software/ <http://bw-sw.com/>
> >
>
>
>
> --
> Rafael Weingärtner
>
