[CONJ-28] native query returns strange object instead of String Created: 2013-03-07  Updated: 2013-03-08  Resolved: 2013-03-08

Status: Closed
Project: MariaDB Connector/J
Component/s: None
Affects Version/s: 1.1.0
Fix Version/s: 1.1.2

Type: Bug Priority: Major
Reporter: Tomas Gustavsson Assignee: Vladislav Vaintroub
Resolution: Fixed Votes: 0
Labels: None
Environment:

Ubuntu 12.04, JBoss Application Server EAP 5.1.2, MySQL-Galera 5.5.29, OpenJDK 1.6.0_27


Attachments: File mariadb-java-client-1.1.1.jar    

 Description   

MariaDB Java connector fails where MySQL connector, Postgres, Oracle etc works fine.


I have a JPA bean with a simple method:

public static CertificateInfo findFirstCertificateInfo(EntityManager entityManager, String issuerDN, String serialNumber) {
CertificateInfo ret = null;
final Query query = entityManager
.createNativeQuery(
"SELECT a.fingerprint, a.subjectDN, a.cAFingerprint, a.status, a.type, a.serialNumber, a.expireDate, a.revocationDate, a.revocationReason, "
+ "a.username, a.tag, a.certificateProfileId, a.updateTime FROM CertificateData a WHERE a.issuerDN=:issuerDN AND a.serialNumber=:serialNumber",
"CertificateInfoSubset2");
query.setParameter("issuerDN", issuerDN);
query.setParameter("serialNumber", serialNumber);
query.setMaxResults(1);
@SuppressWarnings("unchecked")
final List<Object[]> resultList = (List<Object[]>) query.getResultList();
if (!resultList.isEmpty()) {
Object[] fields = resultList.get(0);
// The order of the results are defined by the SqlResultSetMapping annotation
System.out.println("object: "+fields[0]);
System.out.println("type: "+fields[0].getClass().getName());

String fingerprint = (String) fields[0];
String subjectDN = (String) fields[1];
String cafp = (String) fields[2];
int status = ValueExtractor.extractIntValue(fields[3]);
int type = ValueExtractor.extractIntValue(fields[4]);
long expireDate = ValueExtractor.extractLongValue(fields[5]);
long revocationDate = ValueExtractor.extractLongValue(fields[6]);
int revocationReason = ValueExtractor.extractIntValue(fields[7]);
String username = (String) fields[8];
String tag = (String) fields[9];
int cProfId = ValueExtractor.extractIntValue(fields[10]);
long updateTime;
if (fields[11] == null)

{ updateTime = 0; // Might be null in an upgraded installation }

else

{ updateTime = ValueExtractor.extractLongValue(fields[11]); }

ret = new CertificateInfo(fingerprint, cafp, serialNumber, issuerDN, subjectDN, status, type, expireDate, revocationDate,
revocationReason, username, tag, cProfId, updateTime);
}
return ret;
}


@SqlResultSetMapping(name = "CertificateInfoSubset2", columns =

{ @ColumnResult(name = "fingerprint"), @ColumnResult(name = "subjectDN"), @ColumnResult(name = "cAFingerprint"), @ColumnResult(name = "status"), @ColumnResult(name = "type"), @ColumnResult(name = "expireDate"), @ColumnResult(name = "revocationDate"), @ColumnResult(name = "revocationReason"), @ColumnResult(name = "username"), @ColumnResult(name = "tag"), @ColumnResult(name = "certificateProfileId"), @ColumnResult(name = "updateTime") }

),


The table looks like:

CREATE TABLE CertificateData (
fingerprint VARCHAR(250) BINARY NOT NULL,
base64Cert LONGTEXT,
cAFingerprint VARCHAR(250) BINARY,
certificateProfileId INT(11) NOT NULL,
expireDate BIGINT(20) NOT NULL,
issuerDN VARCHAR(250) BINARY NOT NULL,
revocationDate BIGINT(20) NOT NULL,
revocationReason INT(11) NOT NULL,
rowProtection LONGTEXT,
rowVersion INT(11) NOT NULL,
serialNumber VARCHAR(250) BINARY NOT NULL,
status INT(11) NOT NULL,
subjectDN VARCHAR(250) BINARY NOT NULL,
subjectKeyId VARCHAR(250) BINARY,
tag VARCHAR(250) BINARY,
type INT(11) NOT NULL,
updateTime BIGINT(20) NOT NULL,
username VARCHAR(250) BINARY,
PRIMARY KEY (fingerprint)
);


Running the query I get:

java.lang.ClassCastException: [B cannot be cast to java.lang.String
at org.cesecore.certificates.certificate.CertificateData.findFirstCertificateInfo(CertificateData.java:760)
at org.cesecore.certificates.certificate.CertificateStoreSessionBean.findFirstCertificateInfo(CertificateStoreSessionBean.java:387)


CertificateData.java:760 is the line from above:
String fingerprint = (String) fields[0];

The System.out.println debug output prints strange things:
[STDOUT] (http-0.0.0.0-8443-1) object: [B@25073d81
[STDOUT] (http-0.0.0.0-8443-1) type: [B



 Comments   
Comment by Vladislav Vaintroub [ 2013-03-07 ]

Would it be correct to say that you expect ResultSet.getObject() to return String for varbinary datatype? (cAFingerprint is VARCHAR(250) BINARY, thus I ask)

Comment by Tomas Gustavsson [ 2013-03-07 ]

Yes that is correct. It is a String value in java code and Hibernate maps that to a VARCHAR(250) BINARY by default in MySQL (a TEXT in Psql and a VARCHAR2(255 byte) in Oracle)

Comment by Vladislav Vaintroub [ 2013-03-07 ]

Also, can you please paste STDOUT of your test when you use MySQL ConnectorJ? Thanks!

Comment by Tomas Gustavsson [ 2013-03-07 ]

Or it might be an old JBoss default mapping from ages ago...not 100% sure about hibernates default actually. Anyhow that is the mapping we use for String and it has worked quite well so far

Comment by Tomas Gustavsson [ 2013-03-07 ]

STDOUT output using mysql-connector-java-5.1.18-bin.jar

16:39:09,584 INFO [STDOUT] object: fa1db241cf20aee34a42a3ea887ebba422aa22e7
16:39:09,584 INFO [STDOUT] type: java.lang.String

Comment by Vladislav Vaintroub [ 2013-03-07 ]

perhaps you can try 1.1.1 .There was a change related to consistency with ConnectorJ wrt binary types/metadata/getObject(). https://mariadb.atlassian.net/browse/CONJ-20. I'm not sure that helps, but just so we're on the same page

Generally, "VARCHAR BINARY" for String does seem strange, it seems great for byte array. If "BINARY" were ommited , yes, VARCHAR is good for string.

Comment by Tomas Gustavsson [ 2013-03-07 ]

Tested with 1.1.1, same result.

Comment by Tomas Gustavsson [ 2013-03-07 ]

Sounds like string in the MySQL docs:
"the BINARY attribute does not cause the column to be treated as a binary string column"

According to http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html


The BINARY and VARBINARY data types are distinct from the CHAR BINARY and VARCHAR BINARY data types. For the latter types, the BINARY attribute does not cause the column to be treated as a binary string column. Instead, it causes the binary collation for the column character set to be used, and the column itself contains nonbinary character strings rather than binary byte strings. For example, CHAR(5) BINARY is treated as CHAR(5) CHARACTER SET latin1 COLLATE latin1_bin, assuming that the default character set is latin1. This differs from BINARY(5), which stores 5-bytes binary strings that have no character set or collation. For information about differences between nonbinary string binary collations and binary strings, see Section 10.1.7.6, “The _bin and binary Collations”.

Comment by Vladislav Vaintroub [ 2013-03-07 ]

http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-type-conversions.html
Sound like binary

Table 22.25. MySQL Types to Java Types for ResultSet.getObject()

VARCHAR(M) [BINARY] VARCHAR java.lang.String (unless the character set for the column is BINARY, then byte[] is returned.

Comment by Tomas Gustavsson [ 2013-03-07 ]

hehe.

Actually my show create table gives:

CertificateData CREATE TABLE `CertificateData` (
`fingerprint` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`base64Cert` longtext,
`cAFingerprint` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
`certificateProfileId` int(11) NOT NULL,
`expireDate` bigint(20) NOT NULL,
`issuerDN` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`revocationDate` bigint(20) NOT NULL,
`revocationReason` int(11) NOT NULL,
`rowProtection` longtext,
`rowVersion` int(11) NOT NULL,
`serialNumber` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`status` int(11) NOT NULL,
`subjectDN` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`subjectKeyId` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
`tag` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
`type` int(11) NOT NULL,
`updateTime` bigint(20) NOT NULL,
`username` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
PRIMARY KEY (`fingerprint`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Looks like string?

Comment by Vladislav Vaintroub [ 2013-03-07 ]

I'll run some experiments and tell my findings.

Comment by Vladislav Vaintroub [ 2013-03-07 ]

Oh I see. MariaDB JDBC handles "varbinary" and "varchar binary" as same type. ConnectorJ handles them differently (though documentation is misleading here)

Comment by Tomas Gustavsson [ 2013-03-08 ]

I'll be more than happy to test a snapshot.

Comment by Vladislav Vaintroub [ 2013-03-08 ]

If you are unable to build yourself (using bzr to get the sources, and maven to build,as described here https://kb.askmonty.org/en/about-the-mariadb-java-client/) , in this case you can used attached file, build by myself.

Comment by Tomas Gustavsson [ 2013-03-08 ]

Tested the new version. It seems to work just fine.

Awesome! Thanks, nice to be able to move all Maria.

Generated at Thu Feb 08 03:12:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.