Bug #72487 UUID via JDBC generates not unique IDs
Submitted: 30 Apr 2014 8:12 Modified: 2 Jun 2014 10:49
Reporter: Martin Kirchner Email Updates:
Status: No Feedback Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.18 OS:Any
Assigned to: CPU Architecture:Any

[30 Apr 2014 8:12] Martin Kirchner
Description:
INSERT INTO target (GUID, Value) SELECT UNHEX(REPLACE(UUID(), '-', '')), Value FROM source
results in not unique UUIDs in target when called via JDBC.

How to repeat:
Everything works fine if called on the mysql commandline:

CREATE DATABASE apv;
DROP TABLE IF EXISTS apv.source;
DROP TABLE IF EXISTS apv.target;
CREATE TABLE apv.source (Value INT(10) NOT NULL);
CREATE TABLE apv.target (GUID binary(16) NOT NULL, Value INT(10) NOT NULL);
INSERT INTO apv.source (Value) VALUES (1), (2);
INSERT INTO apv.target (GUID, Value) SELECT UNHEX(REPLACE(UUID(), '-', '')), Value FROM apv.source;
SELECT GUID, COUNT(*) FROM apv.target GROUP BY GUID;
-- result contains two UNIQUE GUIDs

However using JDBC results in two rows with the same GUID:

final Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306", "user",	"password");
final Statement s = c.createStatement();
s.execute("DROP TABLE IF EXISTS apv.source");
s.execute("DROP TABLE IF EXISTS apv.target");
s.execute("CREATE TABLE apv.source (Value INT(10) NOT NULL)");
s.execute("CREATE TABLE apv.target (GUID binary(16) NOT NULL, Value INT(10) NOT NULL)");
s.execute("INSERT INTO apv.source (Value) VALUES (1), (2)");
s.execute("INSERT INTO apv.target (GUID, Value) SELECT UNHEX(REPLACE(UUID(), '-', '')), Value FROM apv.source");
final ResultSet rs = s
	.executeQuery("SELECT GUID, COUNT(*) FROM apv.target GROUP BY GUID");
int numRows = 0;
while (rs.next()) {
	numRows++;
	final byte[] guidBytes = rs.getBytes(1);
	final String guid = UuidTools.toHexString(guidBytes); // util method that converts bytes to HEX
	final int count = rs.getInt(2);
	System.out.println("RowNum: " + numRows + " - GUID: " + guid + " - Count: " + count);
}

assert numRows == 2 : "Two unique GUIDs expected.";

Output: 
RowNum: 1 - GUID: EBA35C69D03D11E38E0EF0DEF1CB5368 - Count: 2
Exception in thread "main" java.lang.AssertionError: Two unique GUIDs expected.
	at de.cas.open.server.addresses.dboperations.v3_7.Egal.main(Egal.java:56)
[30 Apr 2014 18:11] Martin Kirchner
Problem exists with MySQL 5.6.12 and 5.6.17, but not with 5.0.32
[2 May 2014 10:49] Filipe Silva
Hi Martin,

Thank you for this bug report.

I seem to be unable to reproduce this behavior. I'm using your code and I'm getting two distinct records all the time, both from MySQL cli and through Connector/J.

So, for me to be able to figure this out I'll need more details:
- Are you running both cases on same server?
- Are you using any specific settings in server configuration that could be affecting one case and not the other?
- Are you using a single server instance or multi-server architecture?

Also, please also try to narrow this down:
- Use latest Coonnector/J (5.1.30).
- Try without the HEX-BIN-HEX conversion.
- Combine methods: create tables and generate data in MySQL cli - retrieve data using JDBC; and vice-versa.
[3 Jun 2014 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".