Bug #89552 setCatalog with cachePrepStmts and useCursorFetch might get the wrong resutls

• Submitted: 6 Feb 2018 9:37
• Modified: 8 Feb 2018 16:27
• Reporter: Yiftach Kaplan
• Status: Duplicate
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 5.1.37
• OS: Any
• CPU Architecture: Any

[6 Feb 2018 9:37] Yiftach Kaplan

Description:
When I open a connection with cachePrepStmts and useCursorFetch and send a request to one database, the results is cashed and I'm getting the same results if I send the same results to a different database.

How to repeat:
When the instance installed on local host, with root user and no password and run this code:

import java.sql.*;

public class TestMySqlSetCataloug
{
    public static void main(String[] args) throws Exception
    {
        try(Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/?cachePrepStmts=true&useCursorFetch=true&useSSL=false", "root", "");
                Statement createTables = connection.createStatement();)
        {
            createTables.executeUpdate("DROP DATABASE IF EXISTS db_one");
            createTables.executeUpdate("DROP DATABASE IF EXISTS db_two");
            createTables.executeUpdate("CREATE DATABASE IF NOT EXISTS db_one");
            createTables.executeUpdate("CREATE DATABASE IF NOT EXISTS db_two");

            createTables.executeUpdate("DROP TABLE IF EXISTS db_one.aaa");
            createTables.executeUpdate("CREATE TABLE IF NOT EXISTS db_one.aaa(a0 INT)");

            createTables.executeUpdate("DROP TABLE IF EXISTS db_two.aaa");
            createTables.executeUpdate("CREATE TABLE IF NOT EXISTS db_two.aaa(a0 INT)");
            createTables.executeUpdate("INSERT INTO db_two.aaa VALUES (0),(1),(2)");

            
            connection.setCatalog("db_one");
            try(PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM aaa");
                    ResultSet results = statement.executeQuery())
            {
                while(results.next())
                {
                    System.out.println("got " + results.getLong(1));
                }
            }
            
            connection.setCatalog("db_two");
            try(PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM aaa");
                    ResultSet results = statement.executeQuery())
            {
                while(results.next())
                {
                    System.out.println("got " + results.getLong(1));
                }
            }
        }
        
    }
}

Suggested fix:
setCatalog should revoke the cache.

[6 Feb 2018 12:25] Chiranjeevi Battula

Hello Yiftach Kaplan,

Thank you for the bug report testcase.
I could not repeat the issue at our end using with Connector / J  5.1.45, MySQL 5.7.21 version.

Thanks,
Chiranjeevi.

[6 Feb 2018 12:26] Chiranjeevi Battula

Screenshot

Attachment: Bug_89552.PNG (image/png, text), 69.84 KiB.

[7 Feb 2018 14:31] Yiftach Kaplan

I can reproduce it with version 5.1.37 and not with 5.1.45

Was it fixed somewhere in between?

[8 Feb 2018 16:27] Filipe Silva

This was fixed in Connector/J 5.1.42, Bug#66430.