Servoy mysql issue

Classic Servoy
1

Hi all,

Servoy version: 3.5.10
OS Platform: Windows

We have a report that is generated by mysql data.

When we run it on small sets of data report runs fine. But on large sets of data we got the following error.
com.servoy.j2db.persistence.RepositoryException: java.util.NoSuchElementException: Timeout waiting for idle object Timeout waiting for idle object

On doing some research http://forum.servoy.com/viewtopic.php?f=3&t=11743&p=58795 on the forum we managed to resolve it. It was basically due to minimum number of active connection we had the above error.

However we bumped up the maximum connections setting from 10 to 50 and the report completed without any errors. But we noticed the active connections value not dropping lower even when logging out of the solution from our 3.5.10 version of the servoy server. Every time we ran a report, the active connection creeped up until it reached the maximum and then the solution crashed. The real active connection between the servoy server and the mysql server does not reach 50 viewed from the mysql database.

We are not seeing this happen on our 3.5.3 server. We run a report, the active connection goes up, the report finishes, the active connection goes back down. We have two servers at 3.5.10 (development and the new production server) and one server at 3.5.3 (the old production server).

Is there a setting that needs to be enabled?

Regards
James Dcunha

2

Do you have, by any chance, queries involving compound keys in your report? I experienced a similar, though hard to reproduce, behaviour when asking for 2 columns acting as keys with a query.

3

Hi Riccardino,

I’m working with James on this problem. None of our tables use compound keys, however as part of this report we are querying a View. The view is constructed from more than one table but only has a single primary key.

Thanks,
-Jeremy

4

James,

We have found and fixed a connection leak in Servoy.
The fix is included in Servoy 4.1.4 and Servoy 5 (currently in RC status).

Rob

5

Thanks Rob,

We will test it out and get back to you. Appreciate your help on this.

Regards
James Dcunha

6

Hi, I have the same problem here, but on MS SQL Server
Servoy 3.5.10
OS: Windows Server 2003

My function have to call _myJSFoundSet.loadRecords() 2 times
and when it call loadRecords(‘select pk1, pk2, pk3 from tbl1’), this make active connection goes up and never down.

I removed this the active connection doesn’t up, I changed to load another table with 1 pk, active connection’s not increase.
and changed to load from table with 2 pk, it’s up.

According to Rob’s post, Is this a bug too?

Regards
Rerng Rak

7

Rerng,

Yes, that is the same issue.
These are 2 situations where Servoy needs a temporary table to perform the query:

  • a pk select with more than 200 values, instead of doing ‘where pk in (?, ?, …, ?)’, Servoy saves the values in a temp table to join with
  • a custom query on pk select with composite pk, you cannot say ‘where (pk1, pk2) in (select x, y from tab)’ so Servoy also needs a temporary table.
    The connection leak was related to the cleanup of the temporary table.

Rob

8

Hi Rob,
Thank you for your answer. :)

btw, Is there any best practice to avoid this bug?
I should change my table structure or there’s has another way to load record from table with composite key

9

We’re still having this issue (that ends up in killing the db connection). The query, used with a DB2 database, is the following:

"select lineitems_id from lineitems where offer_id IN (select offer_id from offers where dossier_id = ?) and flag != ?"
```.
The problem remains after upgrading to 4.1.4: do you have any suggestion?
10

Hi Ric,

Not an answer to your question but why use a subselect when you can use a join ?
Joins are in general much faster.

SELECT lineitems_id FROM lineitems WHERE offer_id IN (SELECT offer_id FROM offers WHERE dossier_id = ?) AND flag != ?

Could be rewritten to

SELECT lineitems_id FROM lineitems a JOIN offers b ON a.offer_id = b.offer_id WHERE b.dossier_id = ? AND a.flag != ?

Hope this helps.

11

Riccardino:
The problem remains after upgrading to 4.1.4: do you have any suggestion?

Riccardino,

The connection leak issue was actually fixed in 4.1.5, not 4.1.4.

Rob