Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Done
-
None
Description
Using CONNECT JDBC with a Postgres database that has UUID seems impossible right now.
I've tried mapping them to VARCHAR(36) and BINARY(16) but can't seem to get any valid data back. The JDBC driver sees this a type OTHER data_type number 1111.
Any ideas? Unfortunately we have a customer where the entire DB is setup around UUIDs.
Attachments
Activity
Can you please post that to the thread listed above for postgres jdbc driver bugs?
Well, I tried simply declaring the UUID as char(36) or varchar(36) and then setting the postgresl url parameter to stringType=unspecified but I am still getting blank UUIDs and no error message.
I would be happy at this stage if CONNECT could read uuid if I manually specify them as char(36) or varchar(36). Is this reasonable without a driver change, or is it too cumbersome?
If I understood their last post correctly, it seems like connect would need no change to read a UUID column...so I guess I don't understnd.
Maybe a hack is for CONNECT to just see if driver type = postgresql and type=OTHER (i.e. 1111) and remote type=uuid then that would be a very specific indication that getObject() and convert the uuid to string. They are mapping other stuff to 1111 so checking the remote type (or whatever it is called ... shows in the returned column info).
Strangely enough, when I call the same JDBCInterface functions from a Java test client, at least reading UUID with getString works allright. But when the same functions is called from CONNECT via JNI, it returns a null string.
Anyhow, I have made a fix in CONNECT that I will publish by the time I thoroughly test it.
UUID will be translated to CHAR(36) when column definitions are set using discovery. Locally a PostgreSQL UUID column will be handled like a CHAR or VARCHAR column. Example:
Using the PostgreSQL table testuuid in the text database:
Table « public.testuuid »
|
Colonne | Type | Collationnement | NULL-able | Par défaut
|
---------+------+-----------------+-----------+--------------------
|
id | uuid | | | uuid_generate_v4()
|
msg | text | | |
|
Its column definitions can by queried by:
create or replace table juuidcol engine=connect table_type=JDBC tabname=testuuid catfunc=columns
|
connection='jdbc:postgresql:test?user=postgres&password=pwd';
|
select table_name "Table", column_name "Column", data_type "Type", type_name "Name", column_size "Size" from juuidcol;
|
This returns:
| Table | Column | Type | Name | Size |
|---|---|---|---|---|
| testuuid | id | 1111 | uuid | 2147483647 |
| testuuid | msg | 12 | text | 2147483647 |
Note: PostgreSQL, when a column size is undefined, returns 2147483647, which is not acceptable for MariaDB. CONNECT change it to the value of the connect_conv_size session variable. Also, for TEXT columns the data type returned is 12 (SQL_VARCHAR) instead of -1 the SQL_TEXT value.
Accessing this table via JDBC by:
CREATE TABLE juuid ENGINE=connect TABLE_TYPE=JDBC TABNAME=testuuid
|
CONNECTION='jdbc:postgresql:test?user=postgres&password=pwd';
|
it will be created as:
CREATE TABLE `juuid` (
|
`id` char(36) DEFAULT NULL,
|
`msg` varchar(8192) DEFAULT NULL
|
) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:postgresql:test?user=postgres&password=pwd' `TABLE_TYPE`='JDBC' `TABNAME`='testuuid';
|
Note: 8192 being here the connect_conv_size value.
Let's populate it:
insert into juuid(msg) values('First');
|
insert into juuid(msg) values('Second');
|
select * from juuid;
|
| id | msg |
|---|---|
| 5e6d260e-29ff-4ac2-85fc-fc163cac48ce | First |
| 374d280e-a2b4-44c2-bb9e-de9d1943f0a1 | Second |
Here the id column values come from the DEFAULT of the PostgreSQL column that was specified as uuid_generate_v4().
It can be set from MariaDB. For instance:
insert into juuid values('2f835fb8-73b0-42f3-a1d3-8a532b38feca','inserted');
|
insert into juuid values(NULL,'null');
|
insert into juuid values('','random');
|
select * from juuid;
|
| id | msg |
|---|---|
| 5e6d260e-29ff-4ac2-85fc-fc163cac48ce | First |
| 374d280e-a2b4-44c2-bb9e-de9d1943f0a1 | Second |
| 2f835fb8-73b0-42f3-a1d3-8a532b38feca | inserted |
| NULL | null |
| 0104dba9-09eb-4fb4-a653-4e0e5981987e | random |
The first insert specifies a valid UUID character representation. The second one set it to NULL. The third one (a void string) generates a Java random UUID.
UPDATE command obeys the same specification.
These commands both work:
select * from juuid where id = '2f835fb8-73b0-42f3-a1d3-8a532b38feca';
|
delete from juuid where id = '2f835fb8-73b0-42f3-a1d3-8a532b38feca';
|
However, this one fails:
select * from juuid where id like '%4f%';
|
Saying:
1296: Got error 174 'ExecuteQuery: org.postgresql.util.PSQLException:
|
ERROR: operator does not exist : uuid ~~ unknown
|
hint: no operator correspond to the data name and to the argument types.
|
This because CONNECT cond_push feature added the WHERE clause to the query sent to PostgreSQL:
SELECT id, msg FROM testuuid WHERE id LIKE '%4f%'
|
and the LIKE operator does not apply to UUID in PostgreSQL.
A new session variable was added to CONNECT: connect_cond_push. It permit to specify if cond_push is enable or not for CONNECT and defaults to 1 (enabled). In this case, you can execute:
set connect_cond_push=0;
|
Doing so, the where clause will be executed by MariaDB only and the query will not fail anymore.
I can confirm that using the PostgreSQL JDBC driver 42.2.1 getString returns null for a UUID column and that trying to set it by setString fails with a message saying something like:
org.postgresql.util.PSQLException: ERROR:column "id" is of type uuid but the expression is of type character varyinghint: You should rewrite the expression or apply to it a transformation of type.(translated from French)
This is bad because, even it is easy to get the UUID value by getObject and toString and to set it by setObject of a UUID Java object, it requires CONNECT to be updated.
Indeed, when reading, the remote column type can be retrieved and when it is 1111 a special function must be called. However, the local column type being CHAR(36) or VARCHAR(36), CONNECT has to get the type of the remote column to know if it must call a specialized function. This is not trivial and could be avoided if the JDBC driver was fixed.
Perhaps you should report this on the PostgreSQL JDBC driver site so this could be fixed. If so, just specying the column as CHAR(36) or VARCHAR(36) will work, even with older MariaDB version (just discovery won't, unless SKIP is specified)