db connection not released to pool in certain case.

[sven....@gmail.com]

Hi,

There seems to be a case where a db connection is not released back to the pool, leading to db connection exhaustion within SonarQube.
SonarQube version: 4.5.2
Database: MySQL

db properties:
sonar.jdbc.maxActive=20
sonar.jdbc.maxIdle=5
sonar.jdbc.minIdle=2
sonar.jdbc.maxWait=5000
sonar.jdbc.minEvictableIdleTimeMillis=600000
sonar.jdbc.timeBetweenEvictionRunsMillis=30000

From above, I would conclude that connections can be sleeping for max 629 seconds until they are closed.
Mysql shows connections are sleeping longer than that, hence I conclude connections get lost.
+-----+-------+-----------------+-------+---------+------+-------+-----------------+
| Id  | User  | Host            | db    | Command | Time | State | Info    |
+-----+-------+-----------------+-------+---------+------+-------+-----------------+
|   4 | sonar | localhost:56977 | sonar | Sleep   | 1386 |       | NULL    |
|   6 | sonar | localhost:56979 | sonar | Sleep   | 3408 |       | NULL    |
|   7 | sonar | localhost:56980 | sonar | Sleep   | 3200 |       | NULL    |
|   8 | sonar | localhost:56981 | sonar | Sleep   | 3068 |       | NULL    |
| 114 | sonar | localhost:57603 | sonar | Sleep   | 1774 |       | NULL    |
| 115 | sonar | localhost:57629 | sonar | Sleep   | 1602 |       | NULL    |
| 116 | sonar | localhost:57634 | sonar | Sleep   | 1669 |       | NULL    |
| 117 | sonar | localhost:57703 | sonar | Sleep   | 1500 |       | NULL    |
| 118 | sonar | localhost:57735 | sonar | Sleep   | 1417 |       | NULL    |
| 119 | sonar | localhost:57818 | sonar | Sleep   |  545 |       | NULL    |
| 120 | sonar | localhost:57842 | sonar | Sleep   |  807 |       | NULL    |
| 121 | sonar | localhost:57848 | sonar | Sleep   | 1043 |       | NULL    |
| 122 | sonar | localhost:57959 | sonar | Sleep   | 1012 |       | NULL    |
| 123 | sonar | localhost:57968 | sonar | Sleep   |  982 |       | NULL    |
| 124 | sonar | localhost:58035 | sonar | Sleep   |  742 |       | NULL    |
| 125 | sonar | localhost:58036 | sonar | Sleep   |  771 |       | NULL    |
| 126 | sonar | localhost:58051 | sonar | Sleep   |   34 |       | NULL    |
| 127 | sonar | localhost:58052 | sonar | Sleep   |  713 |       | NULL    |
| 128 | sonar | localhost:58059 | sonar | Sleep   |  668 |       | NULL    |
| 136 | sonar | localhost:58119 | sonar | Sleep   |  409 |       | NULL    |
+-----+-------+-----------------+-------+---------+------+-------+-------------

For all of the lost connections, this is the last query executed (with id being variable):
Query    SELECT * FROM `projects` WHERE (`projects`.`id` = 93653)  LIMIT 1

So wherever this happens, I think the connection is not closed, thereby not returning the connection to the pool.

I haven't found a related JIRA incident, nor find a fix for the above in the release history of newer versions.
This topic on SO might be the same issue or related:

http://stackoverflow.com/questions/25690194/sonarqube-4-3-2-not-releasing-oracle-db-connections

If needed, I can provide all the queries executed on the connections to trace where it happens.
I suspect somewhere in a flow where a user deletes a project through the web interface.

[Sven Bovens]

Attaching activity for lost connections.

--
You received this message because you are subscribed to a topic in the Google Groups "SonarQube" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sonarqube/J8ZlDpD9wZE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sonarqube+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sonarqube/014acba5-e2ec-4794-84ca-c4822d05da6c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[dfl...@objectif.paris]

Hi,

We had the same problem (with bulk deletion) and a workaround exists :

https://jira.sonarsource.com/browse/SONAR-2784

"A workaround is to add those two properties to the sonar.properties file :

sonar.jdbc.removeAbandoned : true
sonar.jdbc.removeAbandonedTimeout : 20"

Regards,

Denis

[sven....@gmail.com]

Thanks for your quick reply Denis!

I can't verify if the properties effectively work though.
I'm assuming the timeout is expressed in seconds.
Google only knows about these properties from the jira you mentioned.

It would be good if one of the following could happen:

• a new jira is created to properly fix this issue (In the assumption I provided enough info to trace where it goes wrong)
• the workaround properties are documented in the SonarQube documentation and are added to the delivered sonar.properties

Cheers,

Sven

[Julien Lancelot]

Hi Sven,

Is this timeout appears during an analysis, or on server side ?

Thanks.

Regards,

--
You received this message because you are subscribed to the Google Groups "SonarQube" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sonarqube+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sonarqube/0e8c0b59-381e-4344-ad2f-a92f65398657%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Julien LANCELOT | SonarSource

http://sonarsource.com

[dfl...@objectif.paris]

Hi,

We had the same problem and the use case is the following (SonarQube 4.5.5):
We deleted some projects on server side (with the bulk delete page or directly with the project deletion page).
Later (a few minutes later), some analysis are executed, and among these analysis, one or two stopped amid the timeout exception. The other analysis were completed successfully.
And so on, until we restarted the server to reset the JDBC connection pool (first workaround).
Since we added the 2 properties (sonar.jdbc.removeAbandoned, sonar.jdbc.removeAbandonedTimeout) into the sonar.properties file, it works fine. no more timeout exception after project deletion.

Regards,

Denis