when hive-metastore started to occur query syntax error

[#1]

I used configuration hive-site.xml what I already setted. than I reinstalled hadoop, hive and started hive-metastore. But I could not connect MariaDB with hive-client. Metastore log showed me MariaDB syntax error. I know hive support only MySQL. But I'm always using MariaDB and I've never gotten any problem.

I guess it is wrong espace query. I searched what is query. I thought hive ran this query. SELECT “THIS”.”NAME” AS NUCORDER0 FROM “DBS” “THIS” WHERE (LOWER(“THIS”.”NAME”) LIKE ? ESCAPE ‘\\' ) ORDER BY NUCORDER0 

But I think this query using only one backslash character. so I though there is escape issue. Can I fix or avoid this problem?

This is log of exception.

NestedThrowablesStackTrace:
java.sql.SQLSyntaxErrorException: (conn=31) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''\' ORDER BY NUCORDER0' at line 1
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:242)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:171)
at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:238)
at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:230)
at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:157)
at org.mariadb.jdbc.ClientSidePreparedStatement.executeQuery(ClientSidePreparedStatement.java:172)
at com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:174)
at org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeQuery(ParamLoggingPreparedStatement.java:381)
at org.datanucleus.store.rdbms.SQLController.executeStatementQuery(SQLController.java:504)
at org.datanucleus.store.rdbms.query.JDOQLQuery.performExecute(JDOQLQuery.java:651)
at org.datanucleus.store.query.Query.executeQuery(Query.java:1786)
at org.datanucleus.store.query.Query.executeWithArray(Query.java:1672)
at org.datanucleus.api.jdo.JDOQuery.executeWithArray(JDOQuery.java:312)
at org.apache.hadoop.hive.metastore.ObjectStore.getTables(ObjectStore.java:1054)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:103)
at com.sun.proxy.$Proxy5.getTables(Unknown Source)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_tables(HiveMetaStore.java:3895)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:140)
at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:99)
at com.sun.proxy.$Proxy7.get_tables(Unknown Source)
at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_tables.getResult(ThriftHiveMetastore.java:10056)
at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_tables.getResult(ThriftHiveMetastore.java:10040)
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:110)
at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:106)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1924)
at org.apache.hadoop.hive.metastore.TUGIBasedProcessor.process(TUGIBasedProcessor.java:118)
at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:286)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''\' ORDER BY NUCORDER0' at line 1
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1594)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1453)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1415)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:288)
at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:221)
... 37 more

This is hive version.

hive-metastore-1.1.0+cdh5.16.2+1450-1.cdh5.16.2.p0.28.el7.noarch

[#2]

@Jeongtaek

Your Query seems to be partially correct. Except the LIKE ? ESCAPE '\\'' part.

MariaDB [hive]> SELECT this.name AS NUCORDER0 FROM DBS this WHERE ( LOWER(this.name) LIKE 'default' ) ORDER BY NUCORDER0 ;
+-----------+
| NUCORDER0 |
+-----------+
| default |
+-----------+

- Can you please let us know what do you want to match in the LIKE condition? 
- Can you please share some example of DBS names that you want to filter using LIKE clause?

[#3 · Accepted solution]

@Jeongtaek 
Please ignore my previous comment.   
I reread your query and looks like you are not running the query explicitly rather the hive process is executing such query on MariaDB and failing.

I was reading something similar for PostgreSQL where it provides a special property to avoid such issues standard_conforming_strings = off in postgresql.conf

Similar discussion for PostgreSQL: https://grokbase.com/t/hive/user/131qf335q5/problem-with-using-postgres-as-hive-meta-store-db

So i think that in MariaDB also there might be some setting to avoid similar failures with string literals with escape chars.
I will do bit more research on MariaDB ... 

[#4]

@jsensharma 

There is a option like postgresql on MariaDB what you said. https://mariadb.com/kb/en/library/sql-mode/#sql_mode-values

So, I setted sql_mode option in my.cnf like this and restarted MariaDB, hive-metastore and hive-server2.

[mysqld]
...
sql_mode=NO_BACKSLASH_ESCAPES
...

Than it showed me different exception again. I was sure query what hive ran was fine when I watched run query. Because this query already conciderated backslash escape character. So I removed sql_mode option and restarted MariaDB, hive-metastore and hive-server2 again. Than it didn't show any exception anymore. I don't have any idea what is different. Anyway this problem was gone and my hive-client has connected my MariaDB well.

Caused by: java.sql.SQLException: Incorrect arguments to ESCAPE
Query is: SELECT A0.TBL_NAME AS NUCORDER0 FROM TBLS A0 LEFT OUTER JOIN DBS B0 ON A0.DB_ID = B0.DB_ID WHERE B0.`NAME` = ? AND LOWER(A0.TBL_NAME) LIKE '_%' ESCAPE '\\' ORDER BY NUCORDER0, parameters ['default']
java thread: pool-5-thread-2
at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:163)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:280)
at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:223)
... 37 more