| Bug #112011 | RESOURCE GROUP hint fails when used within a prepared statement | ||
|---|---|---|---|
| Submitted: | 9 Aug 2023 12:06 | Modified: | 10 Aug 2023 13:19 |
| Reporter: | Iwo P | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Prepared statements | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[9 Aug 2023 13:04]
MySQL Verification Team
Hi Mr. P, Thank you for your bug report. However, you have set your OS to "Any", while resource groups are supported only on Linux. You have also set the "Category" to "MySQL Server: Prepared statements". However, in SQL your test case works just fine in SQL. What does not work are prepared statements in our Connector/J. Hence, we have to ask you do still think that this is a report that is applicable to all operating systems and that it is a bug in the server. We are waiting on your response.
[10 Aug 2023 13:08]
Dmitry Lenev
Hello! Actually, it is easy to reproduce this problem on Linux without involving Connector and Java at all. Here is the simple test case for MTR framework which demostrates the problem using MySQL's PREPARE syntax. I am sure the same can be reproduced using MySQL C API. CREATE RESOURCE GROUP r1 TYPE=USER VCPU=0,1; --echo # The below query should see itself as executed in 'r1' resource group in the P_S.THREADS table SELECT /*+ RESOURCE_GROUP(r1) */ processlist_info, resource_group FROM performance_schema.threads WHERE processlist_id = connection_id(); # Returns: # processlist_info resource_group # SELECT /*+ RESOURCE_GROUP(r1) */ processlist_info, resource_group FROM performance_schema.threads WHERE processlist_id = connection_id() r1 --echo # Make prepared statement from the same query. PREPARE stmt1 FROM 'SELECT /*+ RESOURCE_GROUP(r1) */ processlist_info, resource_group FROM performance_schema.threads WHERE processlist_id = connection_id()'; --echo # The expectation is that execution of prepared statement will use 'r1' resource group as well. But it is not!!! EXECUTE stmt1; # Returns: # processlist_info resource_group # EXECUTE stmt1 USR_default DEALLOCATE PREPARE stmt1;
[10 Aug 2023 13:19]
MySQL Verification Team
Thank you Mr. Lenev, We were able to repeat your test case. Hence, this is now a verified bug report.
[16 Aug 2023 17:29]
Dmitry Lenev
Hello! Here is a straightforward patch against 8.0.34 version of MySQL server which solves the problem.
[16 Aug 2023 17:29]
Dmitry Lenev
Straightfoward fix against MySQL Server 8.0.34 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: ps-8871-bug112011-contrib.patch (text/x-patch), 14.08 KiB.
[17 Aug 2023 12:21]
MySQL Verification Team
Thank you, Mr. Lenev, This is very kind of you. We will welcome any future contribution from you, since we have a feeling that you know some parts of MySQL source code.
Description: RESOURCE GROUP hint when used within prepared statements is not working correctly. How to repeat: 01) Create a resource group ``` CREATE RESOURCE GROUP rg1 TYPE = USER VCPU = 0 THREAD_PRIORITY = 19; ``` 02) Run a query: ``` mysql> select /*+ RESOURCE_GROUP(rg1) */ sleep(10); +-----------+ | sleep(10) | +-----------+ | 0 | +-----------+ 1 row in set (10,00 sec) ``` That will work correctly (pfs.threads): ```SELECT * FROM performance_schema.threads: … THREAD_ID: 48 NAME: thread/sql/one_connection TYPE: FOREGROUND PROCESSLIST_ID: 9 PROCESSLIST_USER: msandbox PROCESSLIST_HOST: localhost PROCESSLIST_DB: NULL PROCESSLIST_COMMAND: Query PROCESSLIST_TIME: 5 PROCESSLIST_STATE: User sleep PROCESSLIST_INFO: select /*+ RESOURCE_GROUP(rg1) */ sleep(10) PARENT_THREAD_ID: NULL ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: Socket THREAD_OS_ID: 113423 RESOURCE_GROUP: rg1 … ``` 03) Same, from a prepared statement: ``` PREPARE s1 FROM 'SELECT /*+ RESOURCE_GROUP(rg1) */ SLEEP(10)'; EXECUTE s1 … THREAD_ID: 49 NAME: thread/sql/one_connection TYPE: FOREGROUND PROCESSLIST_ID: 10 PROCESSLIST_USER: msandbox PROCESSLIST_HOST: localhost PROCESSLIST_DB: performance_schema PROCESSLIST_COMMAND: Query PROCESSLIST_TIME: 1 PROCESSLIST_STATE: User sleep PROCESSLIST_INFO: EXECUTE s1 PARENT_THREAD_ID: 1 ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: Socket THREAD_OS_ID: 113820 RESOURCE_GROUP: USR_default EXECUTION_ENGINE: PRIMARY CONTROLLED_MEMORY: 29200 MAX_CONTROLLED_MEMORY: 647280 TOTAL_MEMORY: 2170116 MAX_TOTAL_MEMORY: 2194784 TELEMETRY_ACTIVE: NO … ``` 04) Moreover, the following java code reproduces the problem (the resource group is missing after the first iteration): ``` /* ConnectorTest.java */ import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Date; public class ConnectorTest { private Connection connect = null; private Statement statement = null; private ResultSet resultSet = null; public void doit() { try { Class.forName("com.mysql.cj.jdbc.Driver"); connect = DriverManager.getConnection("jdbc:mysql://127.0.0.1:8032/test?user=msandbox&password=msandbox&useServerPrepStmts=true&useCursorFetch=false"); statement = connect.createStatement(); PreparedStatement stmt = connect.prepareStatement("SELECT /*+ RESOURCE_GROUP(rg1) */ 1 FROM DUAL WHERE SLEEP(5)"); for (int i = 0; i < 1000; i++) { stmt.execute(); } } catch (Exception ex) { System.out.println("Exception while cleaning up resources: " + ex.toString()); } } public static void main(String[] argsa) { System.out.println("Starting..."); ConnectorTest x = new ConnectorTest(); System.out.println("x: " + x.toString()); x.doit(); System.out.println("Done..."); } } ```