Details
-
Epic
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
1.1.0, 1.2.0
-
None
-
Use server prepareStatement
-
Sprint connector/j 1.3.0
Description
To enable prepared statements on the server side, MySQL Connector/J provides useServerPrepStmts parameter.
It doesn't work for MariaDB client. If there is a way to get prepared statements work through MariaDB client, I haven't yet found it.
The provided test case checks the global value of Prepared_stmt_count status variable, then attempts to prepare a statement on a connection with useServerPrepStmts, then checks the variable value again.
Output with MariaDB client library 1.1.0:
Prepared_stmt_count before prepare: 0
|
Prepared_stmt_count after prepare: 0
|
With MySQL Connector/J 5.1.23:
Prepared_stmt_count before prepare: 0
|
Prepared_stmt_count after prepare: 1
|
Test case:
import java.sql.DriverManager; |
import java.sql.ResultSet; |
import java.sql.Statement; |
import java.sql.PreparedStatement; |
|
|
|
|
public class BugPreparedStatement |
{
|
public static void main (String argv[]) |
{
|
try { |
|
|
Statement status = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","").createStatement(); |
|
|
ResultSet rs = status.executeQuery("show global status like 'Prepared_stmt_count'"); |
if (rs.first()) { |
System.out.println("Prepared_stmt_count before prepare: " + rs.getInt(2)); |
}
|
|
|
PreparedStatement pst = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useServerPrepStmts=true","root","").prepareStatement("select 1"); |
|
|
rs = status.executeQuery("show global status like 'Prepared_stmt_count'"); |
if (rs.first()) { |
System.out.println("Prepared_stmt_count after prepare: " + rs.getInt(2)); |
}
|
}
|
catch (Exception e) |
{
|
System.out.println("Exception: " + e + "\n"); |
e.printStackTrace();
|
}
|
}
|
}
|
|
|
Attachments
Issue Links
Activity
Hi,
I check again and this work.
Can you elaborate on the problem you have ?
some tests case i've run:
@Test |
public void serverExecutionTest() throws SQLException { |
createTable("ServerPrepareStatementTestt", "id int not null primary key auto_increment, test boolean"); |
try (Connection connection = setConnection()){ |
Statement statement = connection.createStatement();
|
ResultSet rs = statement.executeQuery("show global status like 'Prepared_stmt_count'"); |
assertTrue(rs.next());
|
final int nbStatementCount = rs.getInt(2); |
|
|
PreparedStatement ps = connection.prepareStatement(
|
"INSERT INTO ServerPrepareStatementTestt (test) VALUES (?)"); |
ps.setBoolean(1, true); |
ps.addBatch();
|
ps.execute();
|
|
|
rs = statement.executeQuery("show global status like 'Prepared_stmt_count'"); |
assertTrue(rs.next());
|
assertTrue(rs.getInt(2) == nbStatementCount + 1); |
}
|
}
|
@Test |
public void testCache() throws SQLException { |
createTable("ServerPrepareStatementPrepareCache", "id int not null primary key auto_increment, test varchar(20)"); |
try (Connection connection = setConnection()) { |
final String QUERY = "INSERT INTO ServerPrepareStatementPrepareCache(test) VALUES (?)"; |
final long startTime = System.nanoTime(); |
PreparedStatement pstmt = connection.prepareStatement(QUERY);
|
pstmt.setString(1, "test1"); |
pstmt.execute();
|
final long executionTime = System.nanoTime() - startTime; |
|
|
final long startTimeSecond = System.nanoTime(); |
PreparedStatement pstmt2 = connection.prepareStatement(QUERY);
|
pstmt2.setString(1, "test2"); |
pstmt2.execute();
|
final long executionTimeSecond = System.nanoTime() - startTimeSecond; |
|
|
System.out.println("total time : " + (executionTimeSecond) + " first : " + executionTime); |
Assert.assertTrue(executionTimeSecond * 10 < executionTime); |
|
|
ResultSet resultSet = connection.createStatement().executeQuery("SELECT * FROM ServerPrepareStatementPrepareCache"); |
if (resultSet.next()) { |
Assert.assertEquals("test1", resultSet.getString(2)); |
if (resultSet.next()) { |
Assert.assertEquals("test2", resultSet.getString(2)); |
} else { |
Assert.fail("Must have a result"); |
}
|
} else { |
Assert.fail("Must have a result"); |
}
|
}
|
}
|
console result is :
second query is total time : 2788435 first : 59721444
I can reproduce your test cases: they work as intended. So I went back to my case which doesn't work. After some analyses I found this:
the caching does work if you use a statement with a bind, like this:
"select count(1) from ServerPrepareStatementTestt where id=?".
However, the caching does not work if you don't use a bind, like this:
"select count(1) from ServerPrepareStatementTestt where 1=1".
When I switch to the mysql driver, the caching works in both cases.
Were you able to reproduce my results (using literals instead of binds)? Showing that the issue is still open?
I verified this with version 1.3.3, and it seems that the useServerPrepStmts parameter still does not work.
I suggest to reopen the ticket.