MySQL DSN-Less connection closing files

questions
#1

I’m updating MySQL database from my TPS based app - have a browse on tps file and with a button calli (in the same thread) a window that updates MySQL table with a button. The Window procedure opens MySQL table and updates or adds a record. It all works when I open the app, MySQL records are being updated.

After some time of keeping the browse open ( browse itself does not open any MySQL tables ) calling the window updating MySQL and opening the file causes an error 37 - file not open.

Similar thing happens when I created a browse on MySQL table. It displays the table content, but when I leave it open and idle for about 10 minutes it throws an error not being able to fetch records.

Closing and reopenig the browse fixes the issue, I don’t even have to close the app itself.

Initially I thought it’s a connection_timeou issue. This MySQL database is used by a web application in php so the timeout is default, but thired to in increase it to 8 hrs, but still the same behaviour. This is my current driver string:
Driver={MySQL ODBC 5.2 ANSI Driver};Server=10.1.1.10;Database=mytest;User=mytest;Password=MyPass;Option=3 ;Connection Timeout= 28800

Looks like it only happens when connection is idle, as there is another process checking MySQL table starting every 5 minutes from another app in the tray and I get no such issues there .

#2

MySQL is very aggressive when it comes to dropping “idle connections”. Just “do something” that results in db activity on a timer (say) every 30 seconds and you may be in a better place.

#3

I could use that approach when browsing MySQL tables, however in this case the procedure (Window) updating MySQL table is called from a browse on TPS table which does not need MySQL connection. Only a few users of that app connect to MySQL table - other stations do not need MySQL connections.
In my case, the connection to the database is established only when I call a specific procedure, but is not terminated on exit.

In the window updating MySQL I am calling Relate:MySQLTable.Open() at ThisWindow.Init and then relate:MySQLTable.Close() in ThisWindow.Kill
Despite closing the table the connection shows on the server and is only terminated when I close the whole app. Relation manager close method does not close the connection and open method does not try to reopen.

However it looks like it’s Clarion app that looses connection with the server, not the server closing connection, as when error 37 is thrown I can still see the connection on the server side and closing Clarion app does not terminate it. When I close and reopen the app in this scenario a new connection is created so I end up with multiple connections from the same workstation.

Another interesting observation is that when I call a browse on MySQL table1 and leave it open I get the same issue when I try reopening the same MySQL table1 but opening another browse procedure on a different MySQL table2 re-establishes connection and I can open browse on table1 without errors.

Any idea how I can force re-establishing MySQL connection when it’s lost by clarion app ? Any thing I can add to driver string ?

#4

In ThisWindow.Kill after your .Close(), try adding:

MySQLTable{PROP:Disconnect}

This will close the connection. Needs to be used carefully because it will close connections for all threads but in this case sounds like that’s OK…

#5

Thanks, that seems to do the trick. MySQL connection is closed when updating windows closes and reopened every time user clicks on the button opening the window.

#6

Cool. Be wary of using PROP:Disconnect in more complex apps but in this case smelt fine.