[
https://issues.apache.org/jira/browse/HAWQ-1434?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16003229#comment-16003229
]
Oleksandr Diachenko commented on HAWQ-1434:
-------------------------------------------
Merged to master.
> 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
> Fix For: 2.3.0.0-incubating
>
>
> 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)
