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)
