[jira] [Commented] (HIVE-17830) dbnotification fails to work with rdbms other than postgres

[anishek (JIRA)]

    [ 
https://issues.apache.org/jira/browse/HIVE-17830?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16214723#comment-16214723
 ] 

anishek commented on HIVE-17830:
--------------------------------

Thanks [~daijy] for this patch. A Quick question. 

on looking at the code which sets the ANSI_QUOTE its in 
*MetastoreDirectSql.java*
{code}


public void prepareTxn() throws MetaException {
    if (dbType != DatabaseProduct.MYSQL) return;
    try {
      assert pm.currentTransaction().isActive(); // must be inside tx together 
with queries
      executeNoResult("SET @@session.sql_mode=ANSI_QUOTES");
    } catch (SQLException sqlEx) {
      throw new MetaException("Error setting ansi quotes: " + 
sqlEx.getMessage());
    }
  }

{code}


here we are setting the sql_mode only for the *session* and not *global*. I 
just ran the below on a mysql server without modifying the sql_mode

{code}
mysql> select "NEXT_EVENT_ID" from NOTIFICATION_SEQUENCE;
+---------------+
| NEXT_EVENT_ID |
+---------------+
| NEXT_EVENT_ID |
+---------------+
1 row in set (0.00 sec)
{code}

since we use connection pooling depending on which connection is used to 
execute the above statement we will get different results, wont we. May be i am 
missing something here. 

cc [~thejas]


> dbnotification fails to work with rdbms other than postgres
> -----------------------------------------------------------
>
>                 Key: HIVE-17830
>                 URL: https://issues.apache.org/jira/browse/HIVE-17830
>             Project: Hive
>          Issue Type: Bug
>          Components: HiveServer2
>            Reporter: anishek
>            Assignee: Daniel Dai
>            Priority: Critical
>             Fix For: 3.0.0
>
>         Attachments: HIVE-17830.0.patch, HIVE-17830.1.patch
>
>
> as part of HIVE-17721 we had changed the direct sql to acquire the lock for 
> postgres as
> {code}
> select "NEXT_EVENT_ID" from "NOTIFICATION_SEQUENCE" for update;
> {code}
> however this breaks other databases and we have to use different sql 
> statements for different databases 
> for postgres use
> {code}
> select "NEXT_EVENT_ID" from "NOTIFICATION_SEQUENCE" for update;
> {code}
> for SQLServer 
> {code}
> select "NEXT_EVENT_ID" from "NOTIFICATION_SEQUENCE" with (updlock);
> {code}
> for other databases 
> {code}
> select NEXT_EVENT_ID from NOTIFICATION_SEQUENCE for update;
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)