[jira] [Commented] (HAWQ-1434) pxf jdbc plugin - case problems with mysql on linux

["Michael Andre Pearce (IG) (JIRA)" <ji...@apache.org>]

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

Michael Andre Pearce (IG) commented on HAWQ-1434:
-------------------------------------------------

Hi [~lisakowen],

I have raised a PR for addressing this, it seems there was a force uppercasing of the table name in JDBC plugin.
If someone with merge rights can do the honours? Seems something is up with the pr jenkins builds btw.

Cheers
Mike

> pxf jdbc plugin - case problems with mysql on linux
> ---------------------------------------------------
>
>                 Key: HAWQ-1434
>                 URL: https://issues.apache.org/jira/browse/HAWQ-1434
>             Project: Apache HAWQ
>          Issue Type: Bug
>          Components: PXF
>            Reporter: Lisa Owen
>            Assignee: Ed Espino
>
> i was trying out the pxf jdbc plug-in and ran into a problem.  mysql on linux is case sensitive, and the jdbc plug-in appears to change the case of dbname.tablename in LOCATION URI to uppercase.  if the db/table were created with lowercase names, the query fails.
> to reproduce:
> login to mysql as root user and create 2 databases:
> $ mysql --user=root mysql -p
> create database mtestdb1;
> create database CAPDB1;
> grant all on mtestdb1.* to 'hawquser1'@'localhost' identified by 'hawquser1';
> grant all on CAPDB1.* to 'hawquser1'@'localhost' identified by 'hawquser1';
> log in to mysql as hawquser1 and create some tables:
> mysql -h localhost -u hawquser1 -p
> use mtestdb1;
> create table mysql_table1( id int );
> insert into mysql_table1 values (1);
> insert into mysql_table1 values (2);
> insert into mysql_table1 values (3);
> use CAPDB1;
> create table CAPTABLE( id int );
> insert into CAPTABLE values (1);
> insert into CAPTABLE values (2);
> insert into CAPTABLE values (3);
> create pxf external tables using jdbc plug-in and try to select from them:
> psql -d testdb
> CREATE EXTERNAL TABLE pxf_jdbc_mysql_table1(id int)
>             LOCATION ('pxf://c6401.ambari.apache.org:51200/mtestdb1.mysql_table1?PROFILE=JDBC&JDBC_DRIVER=com.mysql.jdbc.Driver&DB_URL=jdbc:mysql://localhost:3306/mtestdb1&USER=hawquser1&PASS=hawquser1')
> FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
> select * from pxf_jdbc_mysql_table1;
> ERROR:  remote component error (500) from '192.168.64.101:51200':  type  Exception report   message   SELECT command denied to user 'hawquser1'@'localhost' for table 'MYSQL_TABLE1'    description   The server encountered an internal error that prevented it from fulfilling this request.    exception   java.io.IOException: SELECT command denied to user 'hawquser1'@'localhost' for table 'MYSQL_TABLE1' (libchurl.c:897)  (seg5 c6401.ambari.apache.org:40000 pid=635675) (dispatcher.c:1801)
> DETAIL:  External table pxf_jdbc_mysql_table1
> CREATE EXTERNAL TABLE pxf_jdbc_mysql_caps(id int)
>             LOCATION ('pxf://c6401.ambari.apache.org:51200/CAPDB1.CAPTABLE?PROFILE=JDBC&JDBC_DRIVER=com.mysql.jdbc.Driver&DB_URL=jdbc:mysql://localhost:3306/CAPDB1&USER=hawquser1&PASS=hawquser1')
> FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
> select * from pxf_jdbc_mysql_caps;
>  id 
> ----
>   1
>   2
>   3
> (3 rows)
> CREATE EXTERNAL TABLE pxf_jdbc_mysql_caps2(id int)
>             LOCATION ('pxf://c6401.ambari.apache.org:51200/capdb1.captable?PROFILE=JDBC&JDBC_DRIVER=com.mysql.jdbc.Driver&DB_URL=jdbc:mysql://localhost:3306/CAPDB1&USER=hawquser1&PASS=hawquser1')
> FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
> CREATE EXTERNAL TABLE
> testdb=# select * from pxf_jdbc_mysql_caps2;
>  id 
> ----
>   1
>   2
>   3
> (3 rows)
> in this case, the jdbc plugin changes lowercase capdb1.captable to uppercase, the case the table was originally created in in mysql, and the query works.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)