Unexpected Behavior: Need to Run Query Twice to Get Results

[habr]

Hello,

I am encountering an unexpected behavior when trying to query my MySQL database using the mysql1 Dart package. When I execute a query to show all tables in the database, it seems like I need to run the query twice to get the expected results. The first query doesn't return any result, but the second query returns the expected result. Here's a simplified version of my code to demonstrate the issue:

import 'dart:async';
import 'package:mysql1/mysql1.dart';

Future main() async {
  // Open a connection (testdb should already exist)
  final conn = await MySqlConnection.connect(ConnectionSettings(
    host: '127.0.0.1',
    port: 3306,
    user: 'username',
    password: 'password',
    db: 'database_name',
  ),);

  // Show tables
  var results2 = await conn.query('SHOW TABLES');  // First query
  var results = await conn.query('SHOW TABLES');   // Second query

  for (var row in results) {
    print('Table: ${row[0]}');
  }

  // Finally, close the connection
  await conn.close();
}

In the code above, results2 ends up being empty, but results contains the list of tables as expected. I have verified the connection settings and the database state, and everything seems to be in order. I have also checked the MySQL logs and found that both queries are indeed reaching the MySQL server.

Is this a known issue, or am I missing something in my implementation? Any help or guidance would be greatly appreciated.

Thank you in advance!

[vlaadoo]

I've found a solution to this problem in this issue report
After connection, a small delay should be used for further work with the base
Example (from my code):

final conn = await MySqlConnection.connect(...);
await Future.delayed(Duration(milliseconds: 1));
await conn.query(...);

Even 1 millisecond is enough to execute the query correctly

[clxicecream]

Although I have solved the problem, I still want to know why the problem occurs and why it can be solved after delaying？ Please Answer ╰(°▽°)╯