creating CachedRowSet to populate the ResultSet returned by sharding-proxy fails

[ppourali]

todo list edit by tuohai666:

• [x] Question: creating CachedRowSet to populate the ResultSet returned by sharding-proxy fails.
• [x] Question: CachedRowSet populate FAIL when running inside the Sharding Proxy code.
• [x] Question: For PostgreSQL database connection: The rs.first() and rs.previous() both FAIL outside the sharding proxy (raw blackbox). This sounds really weird to me. org.postgresql.util.PSQLException: Operation requires a scrollable ResultSet, but this ResultSet is FORWARD_ONLY.
• [x] Question: For PostgreSQL database connection: The rs.updateString(column, value) FAIL outside the sharding proxy (raw blackbox). org.postgresql.util.PSQLException: ResultSets with concurrency CONCUR_READ_ONLY cannot be updated.

Hi,

Apparently, when I get the returned resultset from the sharding-proxy, I can only scroll forward to get the rows. For instance, I cannot do rs.previous(). So, to fix this, I am trying to the get the resultset, and use this method to convert the resultset to a cachedrowset (to be able to scroll the rows freely backward and forward). ` public static CachedRowSet convertToCachedRowSet(ResultSet rs) {

    RowSetFactory factory;
    CachedRowSet cachedRowSet = null;
    try {
        factory = RowSetProvider.newFactory();
        cachedRowSet = factory.createCachedRowSet();

        if (rs == null || rs.getMetaData() == null)
            return cachedRowSet;
        else 
            cachedRowSet.populate(rs);

    } catch (SQLException e) {
        e.printStackTrace();
    }
    return cachedRowSet;
}

`

The above method, populates the resultset into a cached row set. It is working fine for PostgreSQL but throwing error for MySQL. The error says:

java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@6c7bd190 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:869) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:865) at com.mysql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:3217) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2453) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1242) at com.mysql.jdbc.StatementImpl.createResultSetUsingServerFetch(StatementImpl.java:663) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1343) at com.mysql.jdbc.Field.getCollation(Field.java:446) at com.mysql.jdbc.ResultSetMetaData.isCaseSensitive(ResultSetMetaData.java:552) at com.sun.rowset.CachedRowSetImpl.initMetaData(Unknown Source) at com.sun.rowset.CachedRowSetImpl.populate(Unknown Source)

After googling I found that this is a limitation with MySQL whether it should stream the data or fetch. Do you guys have any idea how to solve this? should I modify any parts of the Sharding-Proxy code? I use the following connection string which disables streaming but still get the error. jdbc:mysql://localhost:3306/mydatabase?useSSL=false&useCursorFetch=true&defaultFetchSize=10000&UseStreamingResults=false

Any help would be appreciated.

Thanks, Parsa

[ppourali]

--UPDATE So, for now I changed the value of the fetch size to 1; public final class ProxyJDBCExecutePrepareCallback implements SQLExecutePrepareCallback {
private static final Integer MYSQL_MEMORY_FETCH_ONE_ROW_A_TIME = 1;

It is working better apparently with the updated fetch size but when I use sql command: "use mydatabase;", it throws the following exception, but it connects to the database. Unknown table 'TABLE_NAMES' in information_schema. So there is definitely an issue with the resultset returned by sharding proxy for mysql and populating it to a cachedrowset.

Thanks, Parsa

[tuohai666]

Hi @ppourali , thanks for you report. We don't test rs.previous() yet, maybe there have problem.

I am a little confused. I am not sure your description and codes are inside sharding-proxy or outside.

Can you just describe the problem stand for the user outside sharding-proxy.(logs or Exceptions) And then use another section to anylyze inside the sharding-proxy separately.

Fill in the bug report will help.

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details. If no response more than 7 days and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

Which project did you use? Sharding-JDBC or Sharding-Proxy?

Expected behavior

Actual behavior

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Example codes for reproduce this issue (such as a github link).

[ppourali]

Hi @tuohai666 , Thanks for the reply.

Problem: When running executeQuery, I am not able to move forward and backward on the ResultSet (maybe it is defined as forward_only). Therefore, I clone the resultset into a cachedrowset to be able to change the cursor position back and forth. This works for PostgreSQL, but fails for MySql. The error I get is:

java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@6c7bd190 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:869) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:865) at com.mysql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:3217) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2453) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1242) at com.mysql.jdbc.StatementImpl.createResultSetUsingServerFetch(StatementImpl.java:663) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1343) at com.mysql.jdbc.Field.getCollation(Field.java:446) at com.mysql.jdbc.ResultSetMetaData.isCaseSensitive(ResultSetMetaData.java:552) at com.sun.rowset.CachedRowSetImpl.initMetaData(Unknown Source) at com.sun.rowset.CachedRowSetImpl.populate(Unknown Source)

I call: cachedRowSet.populate(rs); and this is where it fails. I traced the code of populate() and found that the populate method calls another statement on the connection to get more info about the resultset (see https://github.com/spullara/mysql-connector-java/blob/cc5922f6712c491d0cc46e846ae0dc674c9a5844/src/main/java/com/mysql/jdbc/Field.java#L481 ). And because in MySQL streaming mode we cannot run another statement on the active connection, it throws the above error.

I also investigated further in Sharding-Proxy's code to see if the resultset is created for streaming, and the answer was Yes! According to the MySQL documentation: "The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row". And this is what is set in the Sharding-Proxy.

To resolve, I changed the MYSQL_MEMORY_FETCH_ONE_ROW_A_TIME attribute in the ProxyJDBCExecutePrepareCallback class from Integer.MIN_VALUE to 1 to disable the stream mode resultset (or in fact change connection mode from Memory_Stricktly to Connection_Stricktly ), and rebuilt the project. Now, the previous error is gone, and it works better. However, when in the mysql command line, I use "USE mydatabase;" to switch between databases, it throws the following error:

_com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'TABLE_NAMES' in informationschema at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1242) at com.mysql.jdbc.StatementImpl.createResultSetUsingServerFetch(StatementImpl.java:663) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1343) at com.mysql.jdbc.Field.getCollation(Field.java:446) at com.mysql.jdbc.ResultSetMetaData.isCaseSensitive(ResultSetMetaData.java:552) at com.sun.rowset.CachedRowSetImpl.initMetaData(Unknown Source) at com.sun.rowset.CachedRowSetImpl.populate(Unknown Source) at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.execute.callback.ProxySQLExecuteCallback.executeSQL(ProxySQLExecuteCallback.java:90) at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.execute.callback.ProxySQLExecuteCallback.executeSQL(ProxySQLExecuteCallback.java:77) at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.execute.callback.ProxySQLExecuteCallback.executeSQL(ProxySQLExecuteCallback.java:1) at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:82) at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:58) at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.syncExecute(ExecutorEngine.java:97) at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.parallelExecute(ExecutorEngine.java:93) at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.execute(ExecutorEngine.java:76) at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:68) at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.execute.JDBCExecuteEngine.execute(JDBCExecuteEngine.java:78) at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:98) at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:84) at org.apache.shardingsphere.shardingproxy.backend.text.admin.UnicastBackendHandler.execute(UnicastBackendHandler.java:55) at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:73) at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:90) at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:70) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source)

[ERROR] 08:32:33.275 [pool-4-thread-1] o.a.s.s.f.c.CommandExecutorTask - Exception occur: java.lang.NullPointerException: null at org.apache.shardingsphere.sql.parser.relation.segment.select.projection.DerivedColumn.isDerivedColumn(DerivedColumn.java:76) at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.removeDerivedColumns(JDBCDatabaseCommunicationEngine.java:162) at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.handleColumnsForQueryHeader(JDBCDatabaseCommunicationEngine.java:153) at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.merge(JDBCDatabaseCommunicationEngine.java:126) at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:102) at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:84) at org.apache.shardingsphere.shardingproxy.backend.text.admin.UnicastBackendHandler.execute(UnicastBackendHandler.java:55) at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:73) at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:90) at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:70) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source)

Which version of ShardingSphere did you use? Version 5.0.0-RC1

Which project did you use? Sharding-JDBC or Sharding-Proxy? Sharding-Proxy

Expected behavior Being able to create/populate a cached rowset from the resultset.

Actual behavior Throws exception

Reason analyze (If you can) Analysis is given above.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc Config.yaml" schemaName: mysqlaw dataSource: url: jdbc:mysql://ip:3306/adventureworks?useSSL=false&useCursorFetch=true&defaultFetchSize=10000&UseStreamingResults=false username: username password: 12345678 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50

Steps and Example codes for reproduce this issue (such as a github link). To reproduce, in the sharding-proxy-backend project:

1. Open the ProxySQLExecuteCallback class.
2. Go to the method: private ExecuteResponse executeSQL(final Statement statement, final String sql, final ConnectionMode connectionMode, final boolean withMetadata)
3. Modify the method's body to:

backendConnection.add(statement);
(jdbcExecutorWrapper.executeSQL(statement, sql, isReturnGeneratedKeys)) {
resultSet = statement.getResultSet();

//here try to populate a cached rowset from the resultset
RowSetFactory factory;
CachedRowSet cachedRowSet = null;
try {
    factory = RowSetProvider.newFactory();
    cachedRowSet = factory.createCachedRowSet();
    if (rs == null || rs.getMetaData() == null)
        return cachedRowSet;
    else {          
        cachedRowSet.populate(rs);
    }
} catch (SQLException e) {
    e.printStackTrace();
}

backendConnection.add(resultSet);
return new ExecuteQueryResponse(withMetadata ? getQueryHeaders(resultSet.getMetaData()) : null, createQueryResult(resultSet, connectionMode));

3. Open MySQL command line and make a query, for example:
   USE mysqlaw; SELECT * FROM employee; And you can see that the cachedrowset populate will fail because the resultset is generated in stream mode.

Thanks, Parsa

[tuohai666]

@ppourali , thanks for re-edit, it's more clear now.

I wonder what is the Exception and logs for " I am not able to move forward and backward on the ResultSet".

Actually, move forward and backward is complete a client(JDBC in this case) behavior, that means sharding-proxy can't impact this behavior. Let's treat sharding-proxy a black box for now and further discussion, and not trying to modify codes for it.

A resultset in the wire is like is:

When the result row data reach the JDBC client, it's only a row of data, can not indicate it's a stream resultset or not. Whether it's a stream resultset depends on JDBC client not sharding-proxy.

I have tested sharding-proxy-5.0.0-RC1, and resultSet.previous() works well.

I guess the problem you occurred is due to your JDBC client had been set stream resultset by some way you didn't realize.

[ppourali]

Hi @tuohai666 ,

Thanks a lot for the reply. Here is my output. Actually, you are right, the next and previous work as expected. But I can't still populate a cachedrowset from the resultset. Could you please test this too?

Configuration

schemaName: mysqlaw
dataSource:
 url: jdbc:mysql://localhost:3306/adventureworks?useSSL=false&useCursorFetch=true&defaultFetchSize=5000&UseStreamingResults=false
  username: someuser
  password: 12345678
  connectionTimeoutMilliseconds: 30000
  idleTimeoutMilliseconds: 60000
  maxLifetimeMilliseconds: 1800000
  maxPoolSize: 50

My Java Code:

public class MySqlCon {
    public static void main(String args[]) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3307/mysqlaw?useSSL=false&useCursorFetch=true&defaultFetchSize=5000&UseStreamingResults=false", "postgres", "12345678");
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("select * from employee");

            // stmt.executeQuery("select * from employees".toString());

            RowSetFactory factory = RowSetProvider.newFactory();
            CachedRowSet cachedRowSet = factory.createCachedRowSet();

            // here is the issue
            cachedRowSet.populate(rs);

            while (rs.next())
                System.out.println(rs.getInt(1));
            rs.previous();
            System.out.println(rs.getInt(1));

            con.close();
        } catch (SQLException e) {
            System.out.println(e);

        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

Output I get when I run: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'mysqlaw.employee' doesn't exist

So, it seems that it is not possible to create CachedRowSet from a ResultSet.

Any advice would be appreciated. Thanks, Parsa

[tuohai666]

This exception is different from previous one and not related to stream resultset any more.

The config contains no rules, so proxy works on transparent mode. I suggest change the schemaName to adventureworks to resolve this problem. In transparent mode you can set only one datasource, so the it's better to set the two names identical.

[ppourali]

Hi @tuohai666 ,

Thanks for the reply. It is true that this exception is different, but it is thrown on the same line as the previous exception (with in the populate method of cached rowset). I will change the database name to adventureworks and will update you about the result. Thanks a lot for the advice.

Parsa

[ppourali]

Hi again @tuohai666 ,

So based on your advise the issue is now less sever. Let me explain the scenario and maybe you can have a better thought to make the scenario work.

Let's assume that a client connects to the MySql database server through Sharding-Proxy (client <--> ShardingProxy <--> MySqlServer). Now, we want to intercept the resultset that is going back to the client and manipulate the contents of the resultset. For this, I implemented a few lines of code in the ProxySQLExecuteCallback class and modified the body of the executeSQL method as below:

private ExecuteResponse executeSQL(final Statement statement, final String sql, final ConnectionMode connectionMode,
            final boolean withMetadata) throws SQLException {
        backendConnection.add(statement);

        if (jdbcExecutorWrapper.executeSQL(statement, sql, isReturnGeneratedKeys)) {
            ResultSet resultSet = statement.getResultSet();

            if (sql.toLowerCase().trim().startsWith("select")) {
                ResultSet modifiedResultSet;
                try {
                    modifiedResultSet= Modifier.performModification(resultSet);
                } catch (Exception e) {
                    e.printStackTrace();
                    modifiedResultSet= resultSet;
                }
                resultSet = modifiedResultSet;
            }
            backendConnection.add(resultSet);
            return new ExecuteQueryResponse(withMetadata ? getQueryHeaders(resultSet.getMetaData()) : null,
                    createQueryResult(resultSet, connectionMode));
        }
        return new ExecuteUpdateResponse(statement.getUpdateCount(),
                isReturnGeneratedKeys ? getGeneratedKey(statement) : 0L);

    }

And below is the body of Modifier.performModification:

public ResultSet performAnonymization(ResultSet resultSet) throws SQLException {
        if (resultSet == null)
            return resultSet;

        RowSetFactory factory = RowSetProvider.newFactory();
        CachedRowSet cachedRowSet = factory.createCachedRowSet();

        // here is the issue
        cachedRowSet.populate(rs);

        while (cachedRowSet.next()) {
            cachedRowSet.updateString(1, "some thing");
        }
        return cachedRowset;
    }

What I do above works well for PostgreSQL but not for MySql. On cachedRowSet.populate(), it throws an error saying that the connection is still active, which is usually thrown when a resultset is in streaming mode.

What I found so far: I noticed that MySql says that "The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row". And this is what is happening in the above scenario. I found that in the ProxyJDBCExecutePrepareCallback class, MYSQL_MEMORY_FETCH_ONE_ROW_A_TIME = Integer.MIN_VALUE is used in a situation where connectionMode.equals(ConnectionMode.MEMORY_STRICTLY).

So, when I change MYSQL_MEMORY_FETCH_ONE_ROW_A_TIME = 1, the problem is gone. But I want to know the correct way instead of hardcoding the value. Now a few questions:

1) Is the executeSQL method in the ProxySQLExecuteCallback class the right place to intercept a resultset before being sent to the client? Is it possible that this issue arises because maybe I am working on the resultset too early in ProxySQLExecuteCallback class (e.g., in the preparation mode)? Should I get the returning resultset from somewhere else (maybe later in the process) to modify its content?

2) How does Sharding-Proxy know if the connection is MEMORY_STRICTLY or not (I think the other option is CONNECTION_STRICTLY)? Is there anything in the configuration that I can set to avoid this? Otherwise, I have to hardcode the MYSQL_MEMORY_FETCH_ONE_ROW_A_TIME equal to 1 to make it work. I tried changing the max.connections.size.per.query: 1000 to see if it goes to the Connection Strictly mode, but it did not help.

I appreciate your help. Thanks, Parsa

[tuohai666]

@ppourali I am a little confused now. To make the question clear, I suggest we don't look inside the sharding-proxy just as I said before(Let's treat sharding-proxy as a black box for now and further discussion).

I can't find out why you have to modify the codes until you tell me the exact errors for exceptions appear in the MySQL client(JDBC, CLI, GUI..) which connected to a raw sharding-proxy without modification.

Answer a few questions can make things clear:

1. Please response straightforwardly for " I will change the database name to adventureworks and will update you about the result. " I found your response may be "the issue is now less sever" that is a little ambiguous. Is the issue resolved or part resolved? If part resolved, what left?

2. Your previous reply:

Hi @tuohai666 , Thanks for the reply.

Problem: When running executeQuery, I am not able to move forward and backward on the ResultSet (maybe it is defined as forward_only). Therefore, I clone the resultset into a cachedrowset to be able to change the cursor position back and forth. This works for PostgreSQL, but fails for MySql. The error I get is:

java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@6c7bd190 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.

Is the "Problem:" found outside the sharding-proxy? That means your MySQL client(JDBC) which connected to a raw sharding-proxy throws this exception.

3. What problems you met(errors or exceptions you see from the MySQL client) when using a raw sharding-proxy?

4. Is this issue(#5224) contains multiple topics in addition to the title mentioned?

5. Is this issue use to report a bug or intend to create a pull request of 'CachedRowSet' relative feature?

[ppourali]

Hi @tuohai666 , Thanks again for the reply.

@ppourali I am a little confused now. To make the question clear, I suggest we don't look inside the sharding-proxy just as I said before(Let's treat sharding-proxy as a black box for now and further discussion). I can't find out why you have to modify the codes until you tell me the exact errors for exceptions appear in the MySQL client(JDBC, CLI, GUI..) which connected to a raw sharding-proxy without modification.

Answer a few questions can make things clear:

1. Please response straightforwardly for " I will change the database name to adventureworks and will update you about the result. " I found your response may be "the issue is now less sever" that is a little ambiguous. Is the issue resolved or part resolved? If part resolved, what left?

I changed the database name to adventureworks based as you suggested. The problem related to "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'mysqlaw.employee' doesn't exist" is fixed now. So, this side-issue is fixed by changing my datasource name from mysqlaw to adventrueworks in my configuration. Thanks.

2. Your previous reply:

Hi @tuohai666 , Thanks for the reply. Problem: When running executeQuery, I am not able to move forward and backward on the ResultSet (maybe it is defined as forward_only). Therefore, I clone the resultset into a cachedrowset to be able to change the cursor position back and forth. This works for PostgreSQL, but fails for MySql. The error I get is: java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@6c7bd190 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.

Is the "Problem:" found outside the sharding-proxy? That means your MySQL client(JDBC) which connected to a raw sharding-proxy throws this exception.

1. What problems you met(errors or exceptions you see from the MySQL client) when using a raw sharding-proxy?

OK; multiple things: a) For MySQL database connection: The rs.first() and rs.previous() and CachedRowSet all WORK outside the sharding proxy (raw blackbox). No issue with this.

b) THIS IS NOT ABOUT SHARDING PROXY RAW * For MySQL database connection: CachedRowSet populate FAIL** when running inside the Sharding Proxy code (which is my actual issue now with the stream mode). As mentioned, my scenario is to modify the data before presenting it to the client. I assume I attempt to modify the resultset too early in the sharding proxy because the populate method works outside the sharding proxy well.

c) For PostgreSQL database connection: The rs.first() and rs.previous() both FAIL outside the sharding proxy (raw blackbox). This sounds really weird to me. _org.postgresql.util.PSQLException: Operation requires a scrollable ResultSet, but this ResultSet is FORWARDONLY.

d) For PostgreSQL database connection: The rs.updateString(column, value) FAIL outside the sharding proxy (raw blackbox). _org.postgresql.util.PSQLException: ResultSets with concurrency CONCUR_READONLY cannot be updated.

e) For PostgreSQL database connection: CachedRowSet populate WORKS when running inside the Sharding Proxy code as well as outside the raw sharding proxy. So there is no issue for PostgreSQL and cached row set be it inside the Sharding Proxy or outside.

2. Is this issue(#5224) contains multiple topics in addition to the title mentioned? Yes, probably.

3. Is this issue use to report a bug or intend to create a pull request of 'CachedRowSet' relative feature?

   • I think cases c and d are bugs.
   • For case b, I just need your advice on a possible place to grab the resultset and modify it within the Sharding Proxy (I am writing a data masker kind of thing, so that is why I need to modify the data inside Sharding proxy). I am currently intercepting the resultset in the ProxySQLExecuteCallback class but I get the error of case b mentioned above. java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@6c7bd190 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.

I hope I made things more clear now. So, my main issue is Case b which is NOT really with the raw sharding proxy. The reason of why I keep asking over and over about how to modify the Sharding Proxy is that my scenario is to mask data before sending it to the client.

Thanks again, I know this is becoming very complicated now.

Best Regards, Parsa

[tuohai666]

I have to keep asking whether there's problem as the title mentioned in sharding-proxy until you tell me there's no problem. Now I learn you modify sharding-proxy is because you want to mask data not because there's problems in sharding-proxy.

The following is my advice for case b: The sharding-proxy in this scenario (transparent mode with out sharding rules) always using a stream resultset. Why must it be a stream resultset? Please refer to the doc: https://shardingsphere.apache.org/document/current/en/features/sharding/principle/execute/ Instead of change the logic of ExecuteEngine, your solution is better. In my opinion, you already change the codes in sharding-proxy, and change one more line MYSQL_MEMORY_FETCH_ONE_ROW_A_TIME = 1 is acceptable. Is this advice helpful?

Usually, there's one topic in an issue. It's also OK that we talk about only one case at a time without any others in this issue. I'll create a todo list to be resolved one by one.

[ppourali]

Thanks @tuohai666 ,

Sorry I had to clarify earlier about this. So based on your advice, I will just change MYSQL_MEMORY_FETCH_ONE_ROW_A_TIME = 1 to make it work.

Now only remains cases c and d. Will you create topics on how to fix cases c and d mentioned above? Or should I create two other topics for the issues of c and d. These two cases may be bugs though.

Thanks, Parsa

[tuohai666]

@ppourali I'm glad that the main question is resolved, you can go ahead now.

Fow case c and d, can you please create two separate issues? Because bug report is needed, I can't do that instead of you.

I'd like to ask some questions before the issues are created, therefore you can answer there.

For case c: The ResultSet is FORWARD_ONLY. It seems like client behavior. Just like we met before. Can you check your client usage and find out why ResultSet is FORWARD_ONLY? Meanwhile, does it work when your client connect directly to a PostgreSQL server?

For case d: Same with case c. All these two exceptions are client behavior.

[ppourali]

Hi @tuohai666 ,

You are indeed right. These are client behaviours and nothing to do with the Sharding Proxy. Is there any plan (TODO) to enable the CachedRowSet mechanism in the team's schedule? I think it is kind of important because in many cases you want the resultset to be disconnected from the datasource and CachedRowSet is a good option for this.

Thanks again for your help and support. I think this issue thread can be close now. Parsa

[tuohai666]

Hi @ppourali , Yes, you are right, CachedRowSet is good, I have studied it before. I did put it in sharding-proxy about two years ago at the beginning of the sharding-proxy development. But not for long, our team find there's side-effect. I can't remeber the details of what the side-effect it is, but I can make sure we can't use it anymore from then.

So, there's no plan to enable the CachedRowSet mechanism for the moment. But don't worry, maybe you are right, we can restart the discussion about it if you want. You can create an issue about it anytime. Thanks.