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

["Lisa Owen (JIRA)" <ji...@apache.org>]

Lisa Owen created HAWQ-1434:
-------------------------------

             Summary: 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)