krummas / drizzlejdbc Goto Github PK

drizzle-jdbc drops fractions within timestamps of a MySql database. The tables were created to support fractional seconds.

We changed the listed methods as stated below. It worked for us, but we tested it only against MySQL Server (not Drizzle Server):

org.drizzle.jdbc.internal.common.AbstractValueObject:

public Timestamp getTimestamp() throws ParseException {
    if (rawBytes == null) {
        return null;
    }
    String rawValue = getString();
    SimpleDateFormat sdf;

    if (rawValue.length() > 20) {
        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
    } else if (rawValue.length() >= 19) {
        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    } else {
        sdf = new SimpleDateFormat("yyyy-MM-dd");
    }
    sdf.setLenient(false);
    final java.util.Date utilTime = sdf.parse(rawValue);
    return new Timestamp(utilTime.getTime());
}

org.drizzle.jdbc.internal.common.query.parameters.TimeParameter:

public TimeParameter(final long timestamp) {
    SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss.SSS");
    byteRepresentation = ("'" + sdf.format(new Date(timestamp)) + "'").getBytes();
}

org.drizzle.jdbc.internal.common.query.parameters.TimestampParameter:

public TimestampParameter(final long timestamp) {
    final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
    byteRepresentation = String.valueOf("'" + sdf.format(new Date(timestamp)) + "'").getBytes();
}

public TimestampParameter(final long timestamp, final Calendar cal) {
    final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
    sdf.setCalendar(cal);
    byteRepresentation = String.valueOf("'" + sdf.format(new Date(timestamp)) + "'").getBytes();

}

org.drizzle.jdbc.internal.mysql.MySQLValueObject:

@Override
public Time getTime() throws ParseException {
    if (getBytes() == null) {
        return null;
    }
    final String rawValue = getString();
    final SimpleDateFormat sdf;
    if (rawValue.length() > 8) {
        sdf = new SimpleDateFormat("HH:mm:ss.SSS");

    } else {
        sdf = new SimpleDateFormat("HH:mm:ss");
    }
    final java.util.Date utilTime = sdf.parse(rawValue);
    return new Time(utilTime.getTime());
}

In a simplistic benchmark that I run (consisting of "do 1" and "select 1" queries, running in a loop against local server), NetBeans profiler (CPU, instrumentation, all classes) says this line in jdbc\internal\common\packet\commands\StreamedQueryPacket.java takes takes about half of the CPU time

log.finest("Sending : " + MySQLProtocol.hexdump(byteHeader, 0));

Indeed, even if hexdump is not going to be output, MySQLProtocol.hexdump is still called, and it creates a hex string (in a rather expensive manner), and also concatenated this string with "Sending :". But the "Sending : hex" string is not going to be used, unless log level is set to FINEST. I'm able to increase the QPS by wide margin, just by removing this string, or by placing it under if (log.getLevel() == java.util.logging.Level.FINEST) condition.

JDBC connection problem in drizzle database?
Ubentu s, and jdbc 12. jar.

I am trying to connect to drizzle data through jdbc driver.it will trough below error.

java.sql.SQLNonTransientConnectionException: Could not connect: Connection refused: connect
at org.drizzle.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:97)
at org.drizzle.jdbc.DrizzleDriver.connect(DrizzleDriver.java:97)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at test.doGet(test.java:48)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1008)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: org.drizzle.jdbc.internal.common.QueryException: Could not connect: Connection refused: connect
at org.drizzle.jdbc.internal.mysql.MySQLProtocol.(MySQLProtocol.java:145)
at org.drizzle.jdbc.DrizzleDriver.connect(DrizzleDriver.java:88)
... 21 more
Caused by: java.net.ConnectException: Connection refused: connect
at java.net.DualStackPlainSocketImpl.connect0(Native Method)
at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.SocksSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at org.drizzle.jdbc.internal.mysql.MySQLProtocol.(MySQLProtocol.java:142)
... 22 more

I have a java application which I use MySQL database as back-end and eclipse as front end. My Db contains more than one tables. From my java application Iam trying to connect MySQL by using MySQL-connector-java-5.1.12 as earlier. I have a check box in the application, when checked/unchecked, some datas fetched from the database based on my select query (query contains join statement of more than two tables). The select query is different in chech/uncheck. It works properly and got result what I expect.

My problem is now I change the connector of mysql to drizzle-jdbc1.2, select query doesn't work properly based on the query I send to the DB. Initially the check box is cleared, this time result is perfect. When I check it, my expectation is some additional rows are coming, but the result contains 0's only instead of additional rows. Why?

Note : The same queries are succesfully returns the correct result in the console on the in MySQLQueryBrowser. So I think this issue is related to the drizzle-jdbc itself only.

The current implementation in DrizzleStatement and MySQLProtocol explicitly declares the input field (unnecessarily) as FileInputStream when there is no file based logic in the class. We need to wrap our inputs with custom filter stream wrappers to decrypt data on the fly. We need the declared type of the field and related methods to change to InputStream. If I used Maven or Git I'd include a patch, but I don't, and this is a trivial change, just using the proper parent class instead of the too specific subclass.

I've had a BufferUnderflowException kill a DB Thread in a high performance application, with the following stack trace ('Caused by:' because this is after an explicit catch and log in the application):

Caused by: java.nio.BufferUnderflowException
      at java.nio.Buffer.nextGetIndex(Buffer.java:497)
      at java.nio.HeapByteBuffer.getInt(HeapByteBuffer.java:355)
      at org.drizzle.jdbc.internal.common.packet.buffer.Reader.readInt(Reader.java:86)
      at org.drizzle.jdbc.internal.mysql.packet.MySQLGreetingReadPacket.<init>(MySQLGreetingReadPacket.java:39)
      at org.drizzle.jdbc.internal.mysql.MySQLProtocol.<init>(MySQLProtocol.java:153)
      at org.drizzle.jdbc.DrizzleDriver.connect(DrizzleDriver.java:73 

This was caused by MySQL blocking the host.

I'm guessing that the easiest way to solve it would be to catch the BufferUnderflowException at some point (Reader?) and re-throw it wraped in an IOException (so that that gets wrapped in a QueryException in MySQLProtocol).

But, that first packet isn't required to be a MySQLGreetingReadPacket:

It starts with the client connect()ing to the server which may send a ERR packet and finish the handshake or send a Initial Handshake Packet which the client answers with a Handshake Response Packet

So a proper fix would presumably require determining the type of packet first, and acting accordingly in the MySQLProtocol constructor.

I am using MariaDB 5.5 with Drizzle JDBC .Sometimes I got the following exception in Connection.isValid() .When I got this exception datas are not populated in the GUI.

java.sql.SQLNonTransientConnectionException: Could not ping: Incomplete read! Expected 4, got -1
at org.drizzle.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:97)
at org.drizzle.jdbc.DrizzleConnection.isValid(DrizzleConnection.java:950)

How I can solve this issue?

https://bugs.launchpad.net/drizzle/+bug/904203

According to jdbc spec: "Calling the method close on a Connection object that is already closed is a no-op."
http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#close()

This simple code throws an error:

    Class.forName("org.drizzle.jdbc.DrizzleDriver").newInstance();
    Connection conn = null;
    {
        conn = DriverManager.getConnection("jdbc:mysql:thin://" + HOST + ":"
                + PORT + "/" + DB, USER, PASS);
    }
    conn.close();
    conn.close();

Proposed fix in DrizzleConnection:

    public void close() throws SQLException {
        if (isClosed())
            return;
        ...

Note: I was unable to tell whether the timeoutExecutor could be running while protocol.isClosed() is true. It might be worth trying to this.timeoutExecutor.shutdown(); in any case

Add support for useAffectedRows property in MySQL connections. From the MySQL 5.1 jdbc driver source, this appears to be enabled by default, and seems to be required for JDBC compliance (see http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html). I would suggest adding the following to the constructor in org/drizzle/jdbc/internal/mysql/MySQLProtocol.java:

if(!"true".equals(info.getProperty("useAffectedRows"))) {
capabilities.add(MySQLServerCapabilities.FOUND_ROWS);
}

When connecting to a MySQL server that has reached too many connections, you'll get a ugly BufferUnderflowException:

java.nio.BufferUnderflowException at java.nio.Buffer.nextGetIndex(Buffer.java:498) at java.nio.HeapByteBuffer.getInt(HeapByteBuffer.java:355) at org.drizzle.jdbc.internal.common.packet.buffer.Reader.readInt(Reader.java:80) at org.drizzle.jdbc.internal.mysql.packet.MySQLGreetingReadPacket.<init>(MySQLGreetingReadPacket.java:54) at org.drizzle.jdbc.internal.mysql.MySQLProtocol.<init>(MySQLProtocol.java:155) at org.drizzle.jdbc.DrizzleDriver.connect(DrizzleDriver.java:89)

Looking at the code, greeting packet reading doesn't handle the case where an error packet is sent by the server in place of the greeting packet

there is no implementation if we want to use xadatasource?

According to javadoc,
There are no more results when the following is true:
((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))

The following code will throw a Null Pointer Exception :
boolean isRS= stmt.execute(sql);
int updateCount = -1;
while (isRS || (updateCount = stmt.getUpdateCount()) > -1)
{
if (isRS)
{
ResultSet rs = null;
try
{
rs = stmt.getResultSet();
// Process resultset
}
finally
{
if (rs != null)
{
rs.close();
rs = null;
}
}
}
else
{
// an update count
System.out.println("Result is " + stmt.getUpdateCount());
}
isRS = stmt.getMoreResults();
}

Error message is :
java.lang.NullPointerException
at org.drizzle.jdbc.DrizzleStatement.getUpdateCount(DrizzleStatement.java:740)

if the string value is larger than Integer.MAX_VALUE or smaller than Integer.MIN_VALUE, Drizzle JDBC throws a NumberFormatException. Connector/J returns the appropriate MAX/MIN int value, unless the string value truly isn't a number.

Yes, this is less than ideal, as the caller should know to ask for a long or double, but among other things Hibernate expects all columns to be sized in an integer range, but MySQL reports 4GB in bytes for the size of a LONG_TEXT column defined as UTF8, among other things. This breaks Hibernate's automatic schema update functionality.

Hi,

I have recently started testing Drizzle-JDBC using IPv6 addresses and this results in an exception in the parse(String url) method of org.drizzle.jdbc.JDBCUrl. This is caused by the fact that IPv6 addresses contain ":" characters.

I have prepared and tested a fix for this issue. As I do not have GIT configured on my machine and the change is relatively small, it seemed simpler to post the suggested code change directly here.

The code change allows IPv6 addresses when placed between square brackets (this matches general URL formatting standards for IPv6 addresses):
jdbc:drizzle://[IPv6Address]:3306/database_name

For example, with the IPv6 localhost address, this would look like this:
jdbc:drizzle://[::1]:3306/database_name

Here is the source code related to the issue before and after the suggested change:

Old code:
int hostPortDividerIndex = url.indexOf(":");
if (hostPortDividerIndex == -1) {
int slashIndex = url.indexOf("/");
hostname = url.substring(0, slashIndex);
url = url.substring(slashIndex+1);
} else {
hostname = url.substring(0, hostPortDividerIndex);
url = url.substring(hostPortDividerIndex+1);
int slashIndex = url.indexOf("/");
port = Integer.parseInt(url.substring(0, slashIndex));
url = url.substring(slashIndex+1);
}
int slashIndex = url.indexOf("/");
if (slashIndex == -1) {
database = url;
} else {
database = url.substring(0, slashIndex);
}

New code:
int slashIndex = url.indexOf("/");
String hostPortCombo = url.substring(0, slashIndex);
url = url.substring(slashIndex + 1);
int ipv6StartIndex = hostPortCombo.indexOf("[");
int ipv6EndIndex = hostPortCombo.indexOf("]");
if (ipv6StartIndex >= 0 && ipv6EndIndex > ipv6StartIndex) {
hostname = hostPortCombo.substring(ipv6StartIndex + 1, ipv6EndIndex);
int hostPortDividerIndex = hostPortCombo.indexOf(":", ipv6EndIndex + 1);
if (hostPortDividerIndex != -1) {
port = Integer.parseInt(hostPortCombo.substring(hostPortDividerIndex + 1));
}
} else {
int hostPortDividerIndex = hostPortCombo.indexOf(":");
if (hostPortDividerIndex == -1) {
hostname = hostPortCombo;
} else {
hostname = hostPortCombo.substring(0, hostPortDividerIndex);
port = Integer.parseInt(hostPortCombo.substring(hostPortDividerIndex + 1));
}
}
slashIndex = url.indexOf("/");
if (slashIndex == -1) {
database = url;
} else {
database = url.substring(0, slashIndex);
}

Calling setCatalog() in the drizzle jdbc driver leads to a no-op. Comments in the code state that drizzle doesn't support catalogs.
However, MySQL sort of supports catalogs by switching schemas
Thus, when connected to a MySQL server, the driver should transform setCatalog into "use " and return the connected database for getCatalog

see flyway/flyway#2332

is this project alive and willing to look into this problem?

is anyone with commit privilege (and willingness to release) even read this, and would accept a patch?

I have an java application which uses mariadb server. Drizzle jdbc is used for connecting the mariadb server from java side. Sometimes, I got an error, broken pipe.

Iam using ubuntu12.04, latest drizzle jdbc driver(1.2) and mariadb 5.5.29.

Please see the error log :

java.sql.SQLNonTransientConnectionException: Could not ping: Broken pipe at org.drizzle.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:97) at org.drizzle.jdbc.DrizzleConnection.isValid(DrizzleConnection.java:950) at

.............................................

org.eclipse.cdt.dsf.concurrent.RequestMonitor$2.run(RequestMonitor.java:298) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) at java.util.concurrent.FutureTask.run(FutureTask.java:166) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101(ScheduledThreadPoolExecutor.java:165) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) at java.lang.Thread.run(Thread.java:679) Caused by: org.drizzle.jdbc.internal.common.QueryException: Could not ping: Broken pipe at org.drizzle.jdbc.internal.mysql.MySQLProtocol.ping(MySQLProtocol.java:424) at org.drizzle.jdbc.DrizzleConnection.isValid(DrizzleConnection.java:948) ... 14 more Caused by: java.net.SocketException: Broken pipe at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109) at java.net.SocketOutputStream.write(SocketOutputStream.java:153) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) at org.drizzle.jdbc.internal.mysql.packet.commands.MySQLPingPacket.send(MySQLPingPacket.java:47) at org.drizzle.jdbc.internal.mysql.MySQLProtocol.ping(MySQLProtocol.java:419) ... 15 more

With a table defined as :
CREATE TABLE question ( a? int(11) NOT NULL AUTO_INCREMENT, b? char(80) DEFAULT NULL, PRIMARY KEY (a?));

Inserting data with a prepared statement ( INSERT INTO question ( a? , b? ) VALUES ( ? , ?) ) would fail with :

org.drizzle.jdbc.internal.common.QueryException: You need to set exactly 4 parameters on the prepared statement
at org.drizzle.jdbc.internal.common.query.DrizzleParameterizedQuery.length(DrizzleParameterizedQuery.java:88)
at org.drizzle.jdbc.internal.common.packet.commands.StreamedQueryPacket.send(StreamedQueryPacket.java:67)
at org.drizzle.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:439)
at org.drizzle.jdbc.DrizzlePreparedStatement.executeUpdate(DrizzlePreparedStatement.java:126)

This is due to the fact that the parameterized query does not handle '`', but only single and double quote

I am testing the Drizzle driver with a MySQL server and noticed a difference in behavior regarding DateTime types. I am using the jdbc:mysql:thin: scheme in this case.

It looks like the MySQLType class maps the DATETIME type into a java.sql.Date class rather than a java.sql.TimeStamp I would expect. While both of these extend java.util.Date it appears the sql Date class drops the time data which makes the data incorrect / unusable.

Is there a known way to work around this when using the driver so that I get either a complete (with time) java.util.Date or java.sql.TimeStamp for datetime fields?

Thanks in advance,
Jody

Hello,

I'm using the drizzle-jdbc driver to interface with MySQL. Everything works well on MySQL 5.1.x, but on 5.0.x I receive an error :

2013-02-12 16:36:03,234 INFO [STDOUT] Feb 12, 2013 4:36:03 PM org.drizzle.jdbc.internal.mysql.MySQLProtocol executeQuery
WARNING: Could not execute query org.drizzle.jdbc.internal.common.query.DrizzleQuery@67adf2d7: Unknown table 'referential_constraints' in information_schema
2013-02-12 16:36:03,243 WARN [org.hibernate.util.JDBCExceptionReporter] SQL Error: 1109, SQLState: 42S02
2013-02-12 16:36:03,244 ERROR [org.hibernate.util.JDBCExceptionReporter] Unknown table 'referential_constraints' in information_schema

information_schema.referential_constraints is indeed available from MySQL 5.1.x on. I did some Googling on this issue, but I can't seem to find a solution for it. Is upgrading to 5.1.x the only possibility, or can this be fixed somehow?

Kind Regards.
Tim.

change jdbc.CommonDatabaseMetaData.dataTypeClause:
protected final String dataTypeClause =
" CASE data_type" +
" WHEN 'int' THEN " + Types.INTEGER +
" WHEN 'mediumint' THEN "+Types.INTEGER +
" WHEN 'varchar' THEN " + Types.VARCHAR +
" WHEN 'datetime' THEN " + Types.TIMESTAMP +
" WHEN 'date' THEN " + Types.DATE +
" WHEN 'time' THEN " + Types.TIME +
" WHEN 'text' THEN " + Types.VARCHAR +
" WHEN 'bigint' THEN " + Types.BIGINT +
" WHEN 'varbinary' THEN " + Types.VARBINARY +
" WHEN 'timestamp' THEN " + Types.TIMESTAMP +
" WHEN 'double' THEN " + Types.DOUBLE +
" WHEN 'bit' THEN " + Types.BIT +

" WHEN 'tinyint' THEN " + Types.TINYINT + <<<<<<
" WHEN 'char' THEN " + Types.CHAR+ <<<<<<
" END";

I am using MariaDB 5.5, I tested the auto reconnect feature of the server by setting wait_timeout parameter in the my.ini file. From my code, I created a connection string with autoreconnect option. It is worked fine with mysql jdbc connector. But using drizzle jdbc, it shows an error.

Error log is

SQLException information
Error msg: Could not ping: Incomplete read! Expected 4, got -1
SQLSTATE: 08
Error code: -1
java.sql.SQLNonTransientConnectionException: Could not ping: Incomplete read! Expected 4, got -1
at org.drizzle.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:97)
at org.drizzle.jdbc.DrizzleConnection.isValid(DrizzleConnection.java:950)

When using a long running connection using a lot of different prepared statements (eventually, these prepared statements being big statements), these prepared statements get cached.

This can lead to OOM errors, as this cache is unbounded.

This can be solved by 2 or 3 different ways :

1. provide an option to disable the cache completely. This will avoid such OOM conditions.
2. cache only statements within some size threshold
3. use a better cache implementation (LRU for example)

The currently published drizzle-jdbc is 1.2, which is very old.

It would be nice if a new version would be available as many fixes are compatibility fixes.

to be connector/j-compatible, we need to be able to get columns using

Running the Oracle JDK 1.6.0, MySQLProtocol.close() gets an unhandled UnspportedOperationException when trying to call socket.shutdownInput() when socket is an SSLSocket. This is per the SSL/TLS spec, and can be seen in the OpenJDK source.

Looks like this may have changed since previous JDK versions? The current close() code looks like it is supposed to handle this case, but only by catching the checked exception, not this runtime one.