Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Fixed
-
None
-
None
Description
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.
Attachments
Issue Links
- links to
