[APP][PRO] MySQL-Client

Apps
, ,
81

The flow card you are using is doing all in once:

  • connect
  • execute query
  • end connection

If the query errors somehow (thrown an error), the process is aborted and the flow card throws an error (error output). In this case, the “end connection” is not done.
If you get an EHOSTUNREACH error, it should be already happen on connect try. Then it doesn’t make sense to try to end the connection if it couldn’t be established yet.

Perhaps the connect already opens the connection at the server but your Homey doesn’t get a response. This could cause an open connection. But the question would be: why does the server accept/open the connection without responding.

82

Thank you so much for quick response RonnyW - I appreciate it!
So, we both agree on that EHOSTUNREACH should not invoke a OPEN (thus no need to close). And EHOSTUNREACH are the only errors I got (all other are checked and correct).

I guess (and hope) that one instance are isolated, so many different flows does not interfere or mix up OPEN/CLOSE, right?

Ex:
Flow 1: … open, query, close
Flow 2: … open, query, close
Flow 3: … open, query, close
Flow 4: … open, query, close

Thanks!

83

Well, if the connection is opened at the server even if you get a connect error, then all connect attempts could leave an open connection.

84

Looking at the code, there are a few possibilities that can result in dangling connections:

await connection.connect();
await connection.ping();     
await connection.end();

If ping() fails, for whatever reason, the connection isn’t closed.

await connection.connect();
let databases = await connection.query("SHOW DATABASES");  
await connection.end();

If query() fails, for whatever reason, the connection isn’t closed.

It might be better to always end the connection in a finally:

let connection;

try {
  connection = await mysql.createConnection({
      host     : settings.host,
      port     : settings.port,
      user     : settings.user,
      password : settings.pw
  });

  await connection.connect();
  await connection.ping();     
  await connection.end();   
  return true;
} catch (error) {
  throw error;
} finally {
  if (connection) connection.end();
}

(I’m not sure if you also need to check if connection is actually connected before you call .end() and I’m too lazy to RTFM :wink: )

85

Thanks, Robert. I also saw the 3 calls in a line. I just thought on query I would get another error.
Using finally is a good point.

86

Thanks guys for looking into this.

As I haven’t recently changed any flows etc and the app hasn’t been changed but my Internet seems flaky, my feeling is that this hopefully (?) is an edge-case where my bad Internet stress-testes both the app and Homey in particular, in an unfair way.

It happened with an calendar app last fall, for some weeks, and sometimes Zigbee will fail for days and Z-wave will stop work for new inclusions.

I will disable the MySQL app for now, in order to get my web site back and hopefully my Internet and Homey will magically be cured again in hopefully not to long time!

Thanks again!

87

New live version 1.0.5:

  • Force close connection on error.

@Pat_M FYI and to try:

88

Since this version, I have been encountering issues with the MySQL server, specifically an overflow of existing connections. It seems that old connections are no longer being closed. I quickly receive the message: “Too many connections.” I will disable the MySQL communication flows for now. Hopefully, you can look into this issue and release an update.

I have already restarted the Homey MySQL app, the MySQL service, and even the entire server. I also tried increasing the max_connections variable, but that does not seem to be the issue.

89

Thanks for the hint. You are right. I missed to adapt a small part.

New version 1.0.6 should fix the issue.

1 Like
90

Thanks, Ronny. I see that everything worked fine with the new version. Many thanks for the very quick fix!

91

Very good news, I will try this on Monday when my ISP is active. Thanks RonnyW for quick fix, super hopeful this will work!!

92

After 1.0.5 - 1.0.6, the app still show installed, but not running, when try to restart it say app not installed, tried to reinstall + reboot in several combination, but end result same, app show installed, but can not add client, and when trying to restart, it says app not installed :frowning: love the app :slight_smile:
b1d0b559-296a-43c7-a948-8a1abce1d8be

93

It seems you are also affected by this issue…

Are you using a HP19? The try the new FW:

94

Hi, No its a homey pro 2023, but agree it looks like the same problem, will try a full backup/ restore tonight, if not working, I will see if I can install V1.0.4 again :slight_smile:

95

Thank you very much RonnyW (and sorry for late reply, life+longer testing).
This seem to work very well now. I have ran it for more than a week now, no complaints from my ISP so far.
Also I have started logging active connections to my MySQL database (Threads_connected) and I’m monitoring this via variables and Insights. Average is 20 and max was 56 one day. I think the cap is 100.

Thank you again, really appreciate your work so I can have all my database-queries running again!

1 Like