Can I use multiple SQL statements in a single MySQL query?

Grafana MySQL
1

  • What Grafana version and what operating system are you using?

    Grafana v11.4.0 (b58701869e)

  • What are you trying to achieve?

    Before using AES_DECRYPT() , set SET SESSION block_encryption_mode = 'aes-256-ecb';

  • How are you trying to achieve it?

    I wrote two lines of SQL in one query, and it resulted in an error.
    I split them into two queries, but the query order is sometimes reversed, which prevents the AES decryption parameters from being set.

  • What happened?

    Saving error or decryption failure.

  • What did you expect to happen?

    I can directly modify parameters in an SQL query, or use SET and SELECT statements simultaneously in a single query, or set dependencies between multiple queries to affect the query order.

  • Can you copy/paste the configuration(s) that you are having problems with?

    The SQL statement has been de-identified.

    SET SESSION block_encryption_mode = 'aes-256-ecb';
SELECT
  col_a,
  AES_DECRYPT(
    FROM_BASE64(col_b),
    col_c
  ) AS col_x,
  col_d
FROM
  schema_x.table_y
WHERE
  col_a IN (<LIST_1>)
  OR
  col_a IN (<LIST_2>);

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

    db query error: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT col_a, AES_DECRYPT( FROM_BASE64(col_b), col_c ' at line 2

  • Did you follow any online instructions? If so, what is the URL?

    • no
2

Copy that query out of grafana and run in a mysql query tool

What do you see

3
  1. It works correctly in MySQL client tools.
  2. Executing the SQL statements as two separate queries has a chance of successful decryption and a chance of failure. Sometimes refreshing the page can restore functionality.
4

Hi @huangsijun17 ,
You can’t use two SQL lines in one query it will through the error, so the solution is creating a MySQL stored procedure.
Inside the procedure, we run both the session setting and the query together:
For example :-

CREATE PROCEDURE get_users_decrypted()
BEGIN
SET SESSION block_encryption_mode = ‘aes-256-ecb’;

      SELECT 
                 id,
                 username,
                 CAST(AES_DECRYPT(email_enc, 'key') AS CHAR) AS email,
                 dept
       FROM users
       ORDER BY id;

END;

Then in Grafana, the query becomes:

CALL grafana_demo.get_users_decrypted();

Here is the result -

image
image1127×493 33.4 KB

If you need anything else, please let me know.