0

Running Aurora with mySQL in AWS and accessing from a management machine in the same VPC. If I run the query from the command line (management machine) it finishes in about 2 minutes. If I run the same query on MySQL Workbench it gives this error after a couple minutes:

Error Code: 2013. Lost connection to MySQL server during query

I guess the difference would be that the command line mysql is executed directly on the Aurora instance even though it is issued from the management machine? If this is the case, is there a better visual interface (for Windows) for MySQL?

I've bumped up all the Workbench timeouts but I don't think this is the issue as the query fails well before any of the timeouts are reached.

+-------------------------------------------+----------+
| Variable_name                             | Value    |
+-------------------------------------------+----------+
| aurora_fwd_master_idle_timeout            | 60       |
| aurora_globaldb_rpo_wait_timeout          | 60       |
| aurora_zdr_timeout_on_replica_fall_behind | 60       |
| connect_timeout                           | 10       |
| delayed_insert_timeout                    | 300      |
| have_statement_timeout                    | YES      |
| innodb_flush_log_at_timeout               | 1        |
| innodb_lock_wait_timeout                  | 50       |
| innodb_rollback_on_timeout                | OFF      |
| interactive_timeout                       | 28800    |
| lock_wait_timeout                         | 31536000 |
| net_read_timeout                          | 30       |
| net_write_timeout                         | 60       |
| rpl_stop_slave_timeout                    | 31536000 |
| slave_net_timeout                         | 60       |
| wait_timeout                              | 28800    |
+-------------------------------------------+----------+

mysql> SHOW GLOBAL STATUS LIKE 'aborted%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 4     |
| Aborted_connects | 0     |
+------------------+-------+
2 rows in set (0.00 sec)

mysql>
Steve A
  • 11
  • 3
  • Please login to your AWS Aurora instance and post TEXT results of A) SHOW GLOBAL VARIABLES LIKE '%timeout%'; and B) SHOW GLOBAL STATUS LIKE 'aborted%'; – Wilson Hauck Apr 01 '22 at 00:08
  • I doubt if it is Workbench. More likely it is network lag or some other factor. Show us the query. – Rick James Apr 04 '22 at 03:57
  • It's just a simple query to test performance select sql_no_cache * from airport.flights where Dep_Delay > (select avg(Dep_Delay) from airport.flights) LIMIT 10; After a few hours it started running as quick as the command line. I'll add the output Wilson requested in the original message as the formatting here is terrible. – Steve A Apr 05 '22 at 12:02
  • @SteveA Now that we have the slow query, please post TEXT results of A) EXPLAIN SELECT sql_no_cache * ..........; B) SHOW CREATE TABLE airport.flights; C) SHOW TABLE STATUS WHERE name LIKE 'airport.flights'; for analysis. WHEN you are experiencing VERY LONG query completion times, SHOW FULL PROCESSLIST; during the WAIT can be very helpful in determining the cause of a blockage. Getting a SHOW FULL PROCESSLIST; after the query has completed would not have any clues on why you were waiting 5 minutes ago. – Wilson Hauck Apr 06 '22 at 17:01

0 Answers0