[
https://issues.apache.org/jira/browse/HIVE-10659?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Hari Sankar Sivarama Subramaniyan updated HIVE-10659:
-----------------------------------------------------
Description:
Consider a scenario where beeline is used to connect to a mysql server. The
commands executed via beeline can include stored procedures. For e.g. the
following stored procedure can be included as a command :
{code}
CREATE PROCEDURE RM_TLBS_LINKID() BEGIN IF EXISTS (SELECT * FROM
`INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_NAME` = 'TBLS' AND `COLUMN_NAME` =
'LINK_TARGET_ID') THEN ALTER TABLE `TBLS` DROP FOREIGN KEY `TBLS_FK3` ; ALTER
TABLE `TBLS` DROP KEY `TBLS_N51` ; ALTER TABLE `TBLS` DROP COLUMN
`LINK_TARGET_ID` ; END IF; END
{code}
MySQL stored procedures have semi-colon ( ; ) as the statement terminator.
Since this coincides with beeline's only available command terminator,
semi-colon, beeline will not able to execute the above command successfully
since it tries to execute the partial below command instead of the complete
original command shown above.
{code}
CREATE PROCEDURE RM_TLBS_LINKID() BEGIN IF EXISTS (SELECT * FROM
`INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_NAME` = 'TBLS' AND `COLUMN_NAME` =
'LINK_TARGET_ID') THEN ALTER TABLE `TBLS` DROP FOREIGN KEY `TBLS_FK3` ;
{code}
The above situation can actually happen within Hive when Hive SchemaTool is
used to upgrade a mysql metastore db and the scripts used for the upgrade
process contain stored procedure(as the one introduced initially by HIVE-7018).
As of now, we cannot have any stored procedures as part of MySQL metastore db
upgrade scripts because schemaTool uses beeline to connect to MySQL and beeline
fails to execute any "create procedure" command. This is a serious limitation
and it needs to be fixed by allowing the end user to provide an option to
beeline to not use ";" as the command delimiter and instead use new line
character as the command delimiter.
was:
Consider a scenario where beeline is used to connect to a mysql server. The
commands executed via beeline can include stored procedures. For e.g. the
following stored procedure can be included as a command :
{code}
CREATE PROCEDURE RM_TLBS_LINKID() BEGIN IF EXISTS (SELECT * FROM
`INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_NAME` = 'TBLS' AND `COLUMN_NAME` =
'LINK_TARGET_ID') THEN ALTER TABLE `TBLS` DROP FOREIGN KEY `TBLS_FK3` ; ALTER
TABLE `TBLS` DROP KEY `TBLS_N51` ; ALTER TABLE `TBLS` DROP COLUMN
`LINK_TARGET_ID` ; END IF; END
{code}
MySQL stored procedures have semi-colon(;) as the statement terminator. Since
this coincides with beeline's only available command terminator, semi-colon,
beeline will not able to execute the above command successfully since it tries
to execute the partial below command instead of the complete original command
shown above.
{code}
CREATE PROCEDURE RM_TLBS_LINKID() BEGIN IF EXISTS (SELECT * FROM
`INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_NAME` = 'TBLS' AND `COLUMN_NAME` =
'LINK_TARGET_ID') THEN ALTER TABLE `TBLS` DROP FOREIGN KEY `TBLS_FK3` ;
{code}
The above situation can actually happen within Hive when Hive SchemaTool is
used to upgrade a mysql metastore db and the scripts used for the upgrade
process contain stored procedure(as the one introduced initially by HIVE-7018).
As of now, we cannot have any stored procedures as part of MySQL metastore db
upgrade scripts because schemaTool uses beeline to connect to MySQL and beeline
fails to execute any "create procedure" command. This is a serious limitation
and it needs to be fixed by allowing the end user to provide an option to
beeline to not use ";" as the command delimiter and instead use new line
character as the command delimiter.
> Beeline command which contains semi-colon as a non-command terminator will
> fail
> -------------------------------------------------------------------------------
>
> Key: HIVE-10659
> URL: https://issues.apache.org/jira/browse/HIVE-10659
> Project: Hive
> Issue Type: Bug
> Reporter: Hari Sankar Sivarama Subramaniyan
> Assignee: Hari Sankar Sivarama Subramaniyan
> Attachments: HIVE-10659.1.patch
>
>
> Consider a scenario where beeline is used to connect to a mysql server. The
> commands executed via beeline can include stored procedures. For e.g. the
> following stored procedure can be included as a command :
> {code}
> CREATE PROCEDURE RM_TLBS_LINKID() BEGIN IF EXISTS (SELECT * FROM
> `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_NAME` = 'TBLS' AND `COLUMN_NAME`
> = 'LINK_TARGET_ID') THEN ALTER TABLE `TBLS` DROP FOREIGN KEY `TBLS_FK3` ;
> ALTER TABLE `TBLS` DROP KEY `TBLS_N51` ; ALTER TABLE `TBLS` DROP COLUMN
> `LINK_TARGET_ID` ; END IF; END
> {code}
> MySQL stored procedures have semi-colon ( ; ) as the statement terminator.
> Since this coincides with beeline's only available command terminator,
> semi-colon, beeline will not able to execute the above command successfully
> since it tries to execute the partial below command instead of the complete
> original command shown above.
> {code}
> CREATE PROCEDURE RM_TLBS_LINKID() BEGIN IF EXISTS (SELECT * FROM
> `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_NAME` = 'TBLS' AND `COLUMN_NAME`
> = 'LINK_TARGET_ID') THEN ALTER TABLE `TBLS` DROP FOREIGN KEY `TBLS_FK3` ;
> {code}
> The above situation can actually happen within Hive when Hive SchemaTool is
> used to upgrade a mysql metastore db and the scripts used for the upgrade
> process contain stored procedure(as the one introduced initially by
> HIVE-7018). As of now, we cannot have any stored procedures as part of MySQL
> metastore db upgrade scripts because schemaTool uses beeline to connect to
> MySQL and beeline fails to execute any "create procedure" command. This is a
> serious limitation and it needs to be fixed by allowing the end user to
> provide an option to beeline to not use ";" as the command delimiter and
> instead use new line character as the command delimiter.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
