during LV execution, how to verify if database server is still connected and disconnected at LV program's different stages

[astroboy]

Hi I am having sorts of problems with the connection and disconnections to database.

I am using labview 7.1, LabSQL and Mysql

I have 4 running labview programs that access the same database server.

I noticed that sometimes whenever I logged on to database server by means of command prompt.

Occasionally I would see error too many connections on my attempt to connect to server database.

I am not sure which one of my program has not closed database connection properly, after each use.

I would like to create a program that detects the opening and closing of the database connection  at the various stage of  LV programs, in the same PC.

How do I do that?

Pls advise me

thabks

[JohnCS]

To find out what's causing the too many connections error, you can query SELECT USER(), SHOW PROCESSLIST, SHOW STATUS or SHOW TABLE STATUS. These will return information about the connected users, open connections and threads. Since the problem could arise from the db connection, the selected table or an active query, check the MySQL manual to see which of these queries will generate the exact information you need to find out which of your LV programs is leaving connections open. You may have to make each one of your LV programs a separate user to identify the exact source. You'll also need SUPER or PROCESS privileges to see system wide.

Once you identify the culprit, you can use one of the FLUSH, RESET or KILL queries to terminate the problem connections. You could build a LabSQL VI to do this every time before opening and closing a session or use this procedure to respond to an error message from the db but you should examine your programs to find out why you are running out of connections. Multiple calls to the same table without a corresponding close, especially within loops are the first and most obvious place to look.

Another possibility is that your programs are genuinely busy querying the db and using up all the available connections. If this is the case, you can increase the number of allowed simultaneous connections to the server. Again, you should track down why this is happening. This could be a LabVIEW, a database design or MySQL issue. The MySQL Manual has some good information on optimizing the database server and queries.

[astroboy]

Hi John CS

thanks alot. I will look into your advices.

Clement

[astroboy]

Hi John CS.

I am not sure how to read the result after the show processlist. I am not DB administrator but I am the one who created database and LV program.

See the pic below.

Many different IDs access the same db from the same host, using only one LV program. Is this normal? I am sure it is not normal, right?  And  most of the time  they sleep for more than 5 hrs! I am not well versed in DB but know few enough to create, insert and remove.

Please advise the right way to open and close the connections so that there won't be any IDs that are still sleeping. Is there a way to obtain IDs after opening, then close and kill the former ID so that there won't be too many connections? is that right method?

If I were to create, open and close, and so on at different stages of the programs, will that increase the number of connections, instead of decrementing connections based on the number of closed connections.

I have come across ways to reuse the connection, instead of making new connection? Correct ?

Please advise

Clement

[astroboy]

Hi Here are three scnarios,

Pls see the pic attached.

Should I use shift register to continue the same reference?

Should I use destroy connection.vi after the close connection.vi?

Please advise. thanks in advance.

Clement

[JohnCS]

MySQL will return a new ID for each new connection. If you open connections without a corresponding close for each one, the behaviour you observed is normal. The Connector doesn't care who asked or how often, only how many open connections there are.  The thread command SLEEP means that the thread is waiting for the client to send a new statement to it. The connection is still open and needs to be closed in order to release the thread.

The behaviour you are experiencing is caused by the nested layers of control that exist between your VI and the MySQL database and back. Queries go through LabVIEW, ADODB, ODBC and MySQL and back to process a transaction. Sometimes the interactions are not so obvious. For example, when using the ADODB Close Method on a database opened with ODBC drivers, the thread doesn't terminate because the ODBC database connection process keeps running. To close the connection successfully, you must terminate the connection from the calling application.  You may also want to investigate using

In our case, LabVIEW further complicates things because it keeps refnums open until they are explicitly closed by the Close Reference function or when the VI terminates normally. So if you use the LabSQL Close Connection.vi to close the connection,  ODBC will keep the reference open as long as the VI runs. The LabSQL Destroy.VI will take care of this. There are timeouts in ADODB, ODBC and MySQL that can also terminate the connection, but not every time.  If ADODB detects a pending transaction, it will keep the connection open it is resolved.  You may also want to investigate using LabSQL Recordset Move Last.vi to force a close.

Each component tries to simplify the process by hiding some of these activities from the user. For both the ADODB and ODBC components, Microsoft recommends that you keep the connection time as short as necessary and to explicitly close the connection when you are done with it. With this in mind, I think that all three of the scenarios meet this in the circumstances you present.

The safest thing to do is to always have a Destroy Connection.vi matched to each Open Connection.vi.

In your examples, a shift register is not necessary for the refnums. The error cluster, on the other hand, should be wired to a shift register to pass any error from one iteration to the next. Checking for an error in each iteration and stopping the loop on an error is a good idea here.

[astroboy]

Hi JohnCS,]

Thanks

Clement