0

I have hosted a MySQL DB on AWS EC2 instance(t2.micro, ubuntu, free tier). I am able to connect to it and store data. But most of the time, I am unable to connect to it via workbench or my app. But my configurations are correct since I am able to connect to it sometime and store values. The workbench throws the following error: Lost connection to MySQL server at 'reading initial communication packet', system error: 104.

The app throws an ETIMEDOUT error.

What's wrong here? I have checked Cloudwatch metrics also, but there is no warning on CPU out of credits also. This issue persists continuously for days now(between I am able to connect to the DB randomly). and no useful info about it anywhere.

[edit]: I am trying to connect via my local machine. And the app is also not hosted in EC2, it is on another server.

SELECT @@version; results:

8.0.30-0ubuntu0.22.04.1

SHOW GLOBAL STATUS LIKE 'aborted%'; results:

Aborted_clients | 516 | | Aborted_connects | 357

SHOW GLOBAL STATUS LIKE '%connect%'; results:

Aborted_connects | 357 | | Connection_errors_accept | 0 | | Connection_errors_internal | 0 | | Connection_errors_max_connections | 0 | | Connection_errors_peer_address | 0 | | Connection_errors_select | 0 | | Connection_errors_tcpwrap | 0 | | Connections | 895 | | Global_connection_memory | 0 | | Locked_connects | 0 | | Max_used_connections | 13 | | Max_used_connections_time | 2022-09-08 10:19:10 | | Mysqlx_connection_accept_errors | 0 | | Mysqlx_connection_errors | 0 | | Mysqlx_connections_accepted | 0 | | Mysqlx_connections_closed | 0 | | Mysqlx_connections_rejected | 0 | | Performance_schema_session_connect_attrs_longest_seen | 117 | | Performance_schema_session_connect_attrs_lost | 0 | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_finished_connects | 0 | | Threads_connected | 2

SHOW GLOBAL VARIABLES LIKE '%timeo%'; results:

| 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 | | mysqlx_connect_timeout | 30 | | mysqlx_idle_worker_thread_timeout | 60 | | mysqlx_interactive_timeout | 28800 | | mysqlx_port_open_timeout | 0 | | mysqlx_read_timeout | 30 | | mysqlx_wait_timeout | 28800 | | mysqlx_write_timeout | 60 | | net_read_timeout | 30 | | net_write_timeout | 60 | | replica_net_timeout | 60 | | rpl_stop_replica_timeout | 31536000 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 60 | | ssl_session_cache_timeout | 300 | | wait_timeout | 28800

SHOW GLOBAL VARIABLES LIKE 'max_allowed%'; results: max_allowed_packet | 67108864

Nithur
  • 111
  • 2
  • 10
  • Firewall problem at amazon. Ask on https://serverfault.com . – O. Jones Sep 10 '22 at 11:06
  • 1
    would it be the case even if I am able to connect to it sometimes? – Nithur Sep 10 '22 at 11:07
  • Seems like network issue. Did you try to use different computer or network? Does this happen when you connect from inside AWS, outside AWS? Sadly your question lacks details and is unclear. – Marcin Sep 10 '22 at 11:08
  • I cannot tell from your question whether your connections fail from code running inside AWS or from your development machine. It has to be said, that a micro instance might not have enough RAM to run MySQL, though. It might be crashing. Check your mysql log with `journalctl -u mysql.service`. Or try a bigger instance to see if you can achieve stability. – O. Jones Sep 10 '22 at 11:13
  • 1
    @Marcin I am able to connect to it inside the instance, this happens when I try to connect from outside. – Nithur Sep 10 '22 at 11:15
  • 1
    @O.Jones there is nothing in MySQL logs except starting and stopping logs. The error happens when I try to connect from my local machine. (MySQL workbench) – Nithur Sep 10 '22 at 11:19
  • From each MySQL server involved, please post TEXT results of A) SELECT @@version; B) SHOW GLOBAL STATUS LIKE 'aborted%'; C) SHOW GLOBAL STATUS LIKE '%connect%'; D) SHOW GLOBAL VARIABLES LIKE '%timeo%; E) SHOW GLOBAL VARIABLES LIKE 'max_allowed%'; , please. – Wilson Hauck Sep 10 '22 at 12:05
  • @Nithur This URL may help you resolve your ETIMEDOUT situation, https://github.com/mysqljs/mysql/issues/1388 – Wilson Hauck Sep 10 '22 at 12:17
  • 1
    @WilsonHauck, my resource is not RDS. It is just an EC2 instance. – Nithur Sep 10 '22 at 15:24
  • 1
    @WilsonHauck I've updated the question with the results from the above queries. Please check it out. – Nithur Sep 10 '22 at 15:35
  • @Nithur Which server are these values from? A) AWS EC2 or B) workbench server or C) your app server? Need all three, if possible. Thanks – Wilson Hauck Sep 10 '22 at 23:44
  • 1
    @WilsonHauck, aws EC2. Can you please tell me how it helps? – Nithur Sep 11 '22 at 13:57
  • @Nithur When we have 'aborted_clients' and 'aborted_connects' counts, it is confirmation of the difference between connecting problems and trouble later, sometime after connecting. – Wilson Hauck Sep 22 '22 at 13:41

1 Answers1

0

With the limited information provided from your EC2 server,

Observations, Connections were 895 Aborted_connects were 357 for your uptime of the instance.

To attempt to reduce connect failures, consider for your EC2 Parameters Group,

net_read_timeout=60  # from 30 seconds for more patience to complete task
connect_timeout=30  # from 10 seconds for more patience to complete task
mysqlx_read_timeout=60  # from 30 seconds just in case.

Things we do not know that might influence your timing, the availability zone where your EC2 is running and what is your physical zone? If a REPLICA is involved, where is the availability zone for the REPLICA?

These suggestions are for your EC2 instance.

Good luck, for additional assistance view profile for contact info and free Utility Scripts to improve server query completion speed.

Wilson Hauck
  • 2,094
  • 1
  • 11
  • 19
  • is there any way to see the reason/logs associated with this issue? – Nithur Sep 15 '22 at 05:44
  • [edit]: I just found that the instant reachability check is failing. This has happened before also. – Nithur Sep 15 '22 at 05:53
  • I just found that the reason is CPU utilization is above 99% and credit balance is 0. I guess this is the issue. How to make it auto scale? – Nithur Sep 15 '22 at 07:08
  • There are ways with EC2 to prevent a credit balance of 0. Your AWS contact can probably help you get this done. Errorlogs are wonderful, I can not tell with the data provided if the error log is on or off. For further assistance view contact info and get in touch. – Wilson Hauck Sep 15 '22 at 19:46
  • I changed the instance type to something with more memory, and vCPUs. It is now fine. Thanks for the help. Leaving this here incase if anybody faces the same issue in the future. – Nithur Sep 16 '22 at 05:15
  • Hi, this issue still persists. Maybe it is due to the number of allowed connections?? because I have a lambda function that updates the DB with a connection pool. So, it may create so many connections and refuse to connect to other origins. – Nithur Sep 16 '22 at 07:21
  • 1
    Also, where to add the above mentioned configurations?? – Nithur Sep 16 '22 at 09:16
  • @Nithur From the suggestions, consider for your EC2 Parameters Group, And get in touch, if you need my assistance, please. – Wilson Hauck Sep 16 '22 at 13:55
  • 1
    yeah, where to find parameters group? – Nithur Sep 17 '22 at 05:16
  • @Nithur This URL should help you. https://aws.amazon.com/premiumsupport/knowledge-center/rds-modify-parameter-group-values/ Welcome to SO. – Wilson Hauck Sep 17 '22 at 12:07
  • 1
    I check the error log in MySQL and saw this error: `IP address couldn't be resolved. Temporary failure in name resolution.` How to resolve this? – Nithur Sep 20 '22 at 06:23
  • @Nithur Have the 3 previous timeo* suggestions been applied? When were the suggestions applied? Approximate date/time applied? Can you post the last 40 lines of your error log? – Wilson Hauck Sep 20 '22 at 13:43
  • 1
    No, I didn't apply them. There is no explanation on the error log. It prints the same line repeatedly every time I try to connect to the server. – Nithur Sep 22 '22 at 07:38
  • @Nithur Do you have flexibility to use Skype Talk in English? I will be available in 90 minutes, if you have 10 minutes available to talk. My Skype ID is wlhauck@aol.com – Wilson Hauck Sep 22 '22 at 13:06
  • Please consider trying the suggestions, and let us know the results. You could find these three changes resolves primary causes. – Wilson Hauck Sep 23 '22 at 19:41