MySQL Bugs: #72487: UUID via JDBC generates not unique IDs

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

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)

Problem exists with MySQL 5.6.12 and 5.6.17, but not with 5.0.32

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.

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".