You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hawq.apache.org by "Oleksandr Diachenko (JIRA)" <ji...@apache.org> on 2017/05/09 18:20:04 UTC
[jira] [Commented] (HAWQ-1434) pxf jdbc plugin - case problems with
mysql on linux
[ 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)