One of my Xojo applications connects to a distant MySQL server, and many clients are working with that app, and that’s why many tables get open in MySQL. For some reasons I have to close tables periodically, because they keep on increasing in number, although the connections which opened those tables are closed. So the tables should also be closed, but they remain open.
Now I want to execute a mysqladmin flush-tables on an hourly basis. The engine I use is InnoDB, and autocommit is sometimes on and sometimes off. My question is this:
While the flush tables command forces all tables to get closed, could an Insert statement get blocked and lost? Is it harmful to flush tables while connections are open and operations on databases and tables go on?
Can you help?
P.S. I want to flush tables because on macOS I get a file descriptors exceeded error, since FD_SIZE is set to 1024 on Mac, and exceeding this constant, the server crashes.