hawq-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lisa Owen (JIRA)" <j...@apache.org>
Subject [jira] [Created] (HAWQ-1434) pxf jdbc plugin - case problems with mysql on linux
Date Tue, 18 Apr 2017 20:17:41 GMT
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)

Mime
View raw message