Bug #111953 The JDBC program gets stuck during execution after connecting to the database

• Submitted: 3 Aug 2023 8:36
• Modified: 20 Oct 2023 15:30
• Reporter: peng a
• Status: Won't fix
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 8.0.22,8.0.33, 8.1.0
• OS: Linux
• CPU Architecture: Any

[3 Aug 2023 8:36] peng a

Description:
In the case of Order By followed by a placeholder, the JDBC program is stuck when connecting to the database using the prepare protocol.

How to repeat:
public class Java_Test{

    public static void main(String []args){
        try{
            Connection con = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            con = GetConnection("...", "...");
        } catch (Exception e){
            e.printStackTrace();
        }

    };
    public static Connection GetConnection(String username, String passwd) {
        String driver = "com.mysql.jdbc.Driver";
        String sourceURL = "jdbc:mysql://....../test?useSSL=false&useServerPrepStmts=true&allowPublicKeyRetrieval=true";
        Connection conn = null;
        try {
            System.out.println("start connect!");
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(sourceURL, username, passwd);
            System.out.println("connection succeed!");
            String sql = "select c2 from test.t1 order by c2";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
              System.out.println(rs.getString(1));
            }
            String sql_1 = "select c2 from test.t1 order by ?";

            //--------Where the problem occurs----------------
            PreparedStatement ps_1 = conn.prepareStatement(sql_1);
            //--------At this point, the program gets stuck and no longer advances-------

            System.out.println("test point!");
            ps_1.setString(1,"c2");
            ResultSet rs_1=ps_1.executeQuery();
            System.out.println("continue!");
            while(rs_1.next()){  
              System.out.println(rs_1.getString(1));  
            }  
        } catch (Exception e) { 
            e.printStackTrace();
            return null;
        }    
        return conn;
    };

[11 Aug 2023 2:11] peng a

This behavior occurred in both versions 8.0.22 and 8.0.33, and the jar package was replaced accordingly.
In addition, a simple table structure and data used for the test are attached. 

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                              |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int NOT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    3 |
|  2 |    1 |
|  3 |    2 |
+----+------+
3 rows in set (0.00 sec)

[11 Aug 2023 10:30] MySQL Verification Team

Hello!

Thank you for the report and feedback.

regards,
Umesh

[20 Oct 2023 15:30] Filipe Silva

Must be fixed in the MySQL server. Please follow up Bug#101614.