Mysql connection pool error

JDBC connection pool for MYSQL Database is not working #2747

Comments

I am just at the beginning of configuring Payara server, for my Java EE 8 application to run probably on it.

First is simple I just wanted to create a JDBC connection pool, that connects to MySQL server on my local machine, after creation and save the connection pool. trying to ping the server it gives this error:

An error has occurred Ping Connection Pool failed for MySqlPool. Connection could not be allocated because: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. Please check the server.log for more details.

And this what appears on the admin console.

Expected Outcome

Connection pool should be created successfully and able to ping the database successfully in order to create, JDBC resource correctly.

Current Outcome

Server log reports the following error:

Steps to reproduce (Only for bug reports)

  1. Copy MySQL driver to domain lib folder.
  2. Start the domain

./asadmin start-domain domain1

Configure JDBC connection pool in the admin console for MySQL database.

Save and ping to will give you this error.

Context (Optional)

This is a production project for our company WebCentric, that will serve 10 million transactions per day for clients from around the world as well as our internal system as it is the data lake project for the company, that will connect to MySQL Cluster, and uses Java EE 8 technologies.

Environment

  • Payara Version: 5.181
  • Edition: Full
  • JDK Version: 8 u172 — Oracle
  • Operating System: Windows 10 / macOS Siera
  • Database: MySQL Server 8.0.11

The text was updated successfully, but these errors were encountered:

The error message is coming from the MySQL driver. Are you sure you configured your datasource properties correctly for your MySQL Server?

Thanks for the reply, I already connect to the database from main application and using JPA local persistence unit connected to the database and it succeeds, with the following properties with are the same when trying to create a MySQL connection pool using Payara:

Resource Type: java.sql.DataSource
Datasource Classname: com.mysql.cj.jdbc.MysqlDataSource
Aditional properties:
portNumber: 3306
user:root
password:pass
serverName: localhost or 127.0.0.1
databaseName: taman

Those are the configuration I use, if you able to make the connection just give me the correct parameters.

I tried these configurations:

user:root
password:pass
url: jdbc:mysql://127.0.0.1:3306/taman or jdbc:mysql://localhost:3306/taman

It doesn’t work too.

tried these configurations as per provided URL (@dmatej) for connector/j v8.0.11:
Configuration Properties

user:root
password:pass
localSocketAddress: localhost or 127.0.0.1
useSSL: false
socksProxyPort: 3306
socksProxyHost: localhost or 127.0.0.1

It doesn’t work too the same error.

I think that «shotgun debugging» does not help too much. You have to know the database configuration for connection. I’m not sure with this database version, I still use MySql 5.7.22 with jdbc driver version 8.
The most trivial connection to mysql database is something like this (copy from my asadmin command updated with your properties):
create-jdbc-connection-pool —ping —restype javax.sql.DataSource —datasourceclassname com.mysql.cj.jdbc.MysqlDataSource —property user=root:password=pass:DatabaseName=taman:ServerName=127.0.0.1:port=3306:useSSL=false:zeroDateTimeBehavior=CONVERT_TO_NULL:useUnicode=true:serverTimezone=UTC:characterEncoding=UTF-8:useInformationSchema=true:nullCatalogMeansCurrent=true:nullNamePatternMatchesAll=false MySqlPool
When I look again on your experiments, I would try the original properties and added useSSL=false. I don’t think you use proxy .

Читайте также:  The rdp protocol component x 224 detected an error in the protocol stream

Did you try telnet? Server should respond; if it hangs before Connected to localhost, it is network problem. But if you can successfuly connect from the application, this is not probably the problem.

And one final thing — in which directory did you put the JDBC driver?

  1. I have pinged the server and telnet it, and it responds correctly.
  2. To add the driver to Payara I have used this command from asadmin :
    asadmin> add-library ‘C:\Program Files (x86)\MySQL\Connector J 8.0\mysql-connector-java-8.0.11.jar’
  3. And I checked the driver location and it was added to the following location:
    C:\Work\Utils\Servers\Payara\5.181\full\glassfish\domains\domain1\lib\mysql-connector-java-8.0.11.jar
  4. I am using the JUnit to test my business from JPA local environment and it connects successfully and test cases for all CRUD operations successfully run.

Look dear @dmatej I have tried this command from asadmin :

create-jdbc-connection-pool —ping —restype javax.sql.DataSource —datasourceclassname com.mysql.cj.jdbc.MysqlDataSource —property user=root:password=pass:DatabaseName=taman:ServerName=127.0.0.1:port=3306:useSSL=false:zeroDateTimeBehavior=CONVERT_TO_NULL:useUnicode=true:serverTimezone=UTC:characterEncoding=UTF-8:useInformationSchema=true:nullCatalogMeansCurrent=true:nullNamePatternMatchesAll=false MySqlPool

And it is created successfully and I have used the following command
asadmin> ping-connection-pool MySqlPool
and it runs successfully too which wasn’t before when I created the database connection from GUI.

All I have seen here is different configurations I will try to add them to my old connection to see if it will work or really there is a problem in GUI when created the connection, and will let you know.

Also be careful with other my properties (unicode, timezone, characterEncoding, etc.) — these correlate to the database configuration. So if you have iso-8859-1 database, UTF-8 in jdbc driver may cause data corruption or exceptions (not sure now).
As @smillidge wrote, I don’t think the problem is with Payara, so I believe you can close this issue. I configured the pool from GUI and my command here is based on my Arquillian+JUnit test configuration.

I have copped the all the configurations from the newly created pool to my old one and it is work successfully.

So you are right there is no problem with Payara at all.

Thanks, @smillidge, and @dmatej really for your help and thanks for configurations that solve the problem.

Источник

Need Help Error Occurred — Connect Timeout expired. All pooled connections are in use. The operation was canceled. #815

Comments

We are using the mysql connector library v0.65, (sync approach), we have just saw our logs that there are several errors, and this is the error message:

Connect Timeout expired. All pooled connections are in use. The operation was canceled.

MySql.Data.MySqlClient.MySqlException:
at MySql.Data.MySqlClient.MySqlConnection+d__109.MoveNext (MySqlConnector, Version=0.65.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92MySqlConnector, Version=0.65.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92: //src/MySqlConnector/MySql.Data.MySqlClient/MySqlConnection.csMySqlConnector, Version=0.65.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92: 670)
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at MySql.Data.MySqlClient.MySqlConnection+d__25.MoveNext (MySqlConnector, Version=0.65.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92MySqlConnector, Version=0.65.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92: /
/src/MySqlConnector/MySql.Data.MySqlClient/MySqlConnection.csMySqlConnector, Version=0.65.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92: 327)
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at MySql.Data.MySqlClient.MySqlConnection.Open (MySqlConnector, Version=0.65.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92MySqlConnector, Version=0.65.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92: //src/MySqlConnector/MySql.Data.MySqlClient/MySqlConnection.csMySqlConnector, Version=0.65.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92: 285)
at Investagrams.Data.SqlFactory.SqlParent.ExecuteQuery (Investagrams.Data, Version=1.1.0.0, Culture=neutral, PublicKeyToken=nullInvestagrams.Data, Version=1.1.0.0, Culture=neutral, PublicKeyToken=null: C:\Projects\Investagrams Web\Investagrams\Investagrams.Data\SqlFactory\CommonFactory\SqlParent.csInvestagrams.Data, Version=1.1.0.0, Culture=neutral, PublicKeyToken=null: 205)
Inner exception System.OperationCanceledException handled at MySql.Data.MySqlClient.MySqlConnection+d__109.MoveNext:
at System.Threading.SemaphoreSlim.Wait (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at System.Threading.SemaphoreSlim.Wait (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at MySqlConnector.Core.ConnectionPool+d__10.MoveNext (MySqlConnector, Version=0.65.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92MySqlConnector, Version=0.65.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92: /
/src/MySqlConnector/Core/ConnectionPool.csMySqlConnector, Version=0.65.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92: 44)
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at System.Threading.Tasks.ValueTask`1.get_Result (System.Threading.Tasks.Extensions, Version=4.2.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51)
at MySql.Data.MySqlClient.MySqlConnection+d__109.MoveNext (MySqlConnector, Version=0.65.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92MySqlConnector, Version=0.65.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92: /_/src/MySqlConnector/MySql.Data.MySqlClient/MySqlConnection.csMySqlConnector, Version=0.65.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92: 652)

Читайте также:  Query failed error syntax error at or near

looks like it happened on when calling the open connection.

It happened so many times for about 2 mins.

This never happened before so far, we are closing our connections properly, all of our data / DB calls are wrap in a try catch finally just like this:

This is our connection string: SERVER=xxxxxx;DATABASE=xxxx;USERID=xxxxx;PASSWORD=xxxxxx;POOLING=true;DEFAULT COMMAND TIMEOUT=10;Connection Timeout=5;charset=utf8mb4;

Any suggestions and recommendations on how to fix and prevent this kind of errors? We are using from .net framework 4.7.2 web api

The text was updated successfully, but these errors were encountered:

Источник

Throw error on connection failed in pool #708

Comments

When I var pool = mysql.createPool(options); to a wrong host (ip address), I catch nothing in pool.on(‘error’, function(error) <>); .

Instead, not until I do a pool.getConnection() + connection.query() and sit out the full (default) timeout of two minutes or something do I receive an ETIMEDOUT error.

Is there (or should there be) a way to find out immediately if the connection failed?

In contrast, if I (purposely) specify a wrong username, I immediately catch an ER_DBACCESS_DENIED_ERROR error. But a wrong host will just do nothing until the connection times out.

The text was updated successfully, but these errors were encountered:

The poll doesn’t even make a connection to the database until a pool.getConnection() call, so that would be the earliest point to know.

That makes sense, but then I should get an error that a connection could not be made. But it waits until a timeout is thrown in stead.

So, just like ER_DBACCESS_DENIED_ERROR is thrown when the user/pass is wrong, can’t I ‘catch’ a wrong host instead of just having the query time out?

This is only when host is an ip (e.g. 1.1.1.1), when host is a name e.g. ‘localhostiness’ (deliberate typo) I get an error thrown immediately.

Testcase

Connecting to a non-existing host will not throw an error, instead it will wait forever (for timeout).
I’ve wrapped the query inside a timeout of two seconds, otherwise this demonstration would take a few minutes.

If you fill in correct database credentials you will see that any other mistake will throw an error immediately (once the query is executed).

The query is not what is timing out, it is the call to pool.getConnection that times out, because it calls connection.connect() before passing you the connection. This means you only have to wait for a pool.getConnection() call to timeout; you cannot even make the connection.query() call.

I am not sure I am following. Because using proper credentials, the connection and the query are established/queried without a problem.

q is just a library for flattening those (annoying) nested callbacks that are the side-effect of asynchronous code.
If err is defined, all .then() will be skipped and .fail() executed in stead.

But since your example throws immediately, there must be something wrong on my end. I’m just not seeing the problem. I will experiment with this.

When you use Q.timeout , it will timeout on the entire chain, so even though you put the timeout call after the query call, the timeout is still encompassing the pool.getConnection call, as that is the part that is timing out, not the query as your original post was saying.

This is true, but one would expect .fail() to be executed immediately, because getConnection() calls back with err (and that doesn’t take 2+ seconds).

Читайте также:  Parse error there is problem parsing the package

OK. Does the code I posted call back as fast as you are looking for? If so, then it seems like your issue would be with your use of the Q library (which I don’t know much about), which is why I was posting in the standard callback-style.

I will get back to you on that. 👍

Sorry for the delay.

Does the code I posted call back as fast as you are looking for?

No it does not. The callback never gets executed in your code either.
If you chance host to an existing one, e.g. 127.0.0.1 , you’ll get an ER_ACCESS_DENIED_ERROR error immediately, just like with my code.

Looks like my initial report was valid. There is no error thrown when you are trying to connect to a non-existing host. So we need to guess based on a lack of response before a certain timeout.

@Redsandro can you try using the code from PR #726 and specifying connectTimeout in your connection/pool configuration and seeing if this causes a timeout in your situation?

@dougwilson Sorry for the late response, I was on the toilet.

Indeed I get the a timeout error when specifying this connectTimeout .

Set this to a value higher than 10 seconds and a different hardcoded timeout wins:

It makes sense, because it should not take that long to connect. When I opened this bug I didn’t understand why a timeout was needed at all, but it makes sense. We cannot know that a connection was failed if we do not get a reply from the (non-existing) server. If we connect to an existing server with bad credentials, we get ER_ACCESS_DENIED_ERROR immediately, because the server tells us.

I’m satisfied and I forgot to let you know. 👍

So don’t change anything on my account.

With that said, I do have the feeling that setting connectTimeout should change the time before a Handshake inactivity timeout occurs in stead of adding a separate timeout with a separate connect ETIMEDOUT error on top of that.

Hi @Redsandro , the inactivity timeout is not hard-coded in any way. That Handshake inactivity timeout is a different timeout tracking a different aspect of the connection, and you control that using the acquireTimeout option in your pool options (all pool options are documented at https://github.com/felixge/node-mysql#pool-options).

With that said, I do have the feeling that setting connectTimeout should change the time before a Handshake inactivity timeout occurs in stead of adding a separate timeout with a separate connect ETIMEDOUT error on top of that.

These are two independent timeouts and track different aspects of the connection lifecycle. They are additive, because they are separate. The connectTimeout is a timeout when establishing the underlying TCP connection to your MySQL server. Once this succeeds and you have a TCP channel, then the timeout is destroyed. After that, this module will send a handshake packet to the MySQL server. The acquireTimeout will time this aspect of establishing a MySQL application session and the login of your user.

These are separate timeouts because they are very different in nature — one is sensitive to your networking infrastructure and the other is sensitive to your MySQL application/authentication infrastructure. Typically you want to have a very small connectTimeout and a longer acquireTimeout (we default to 10s + 10s, which is 20s total).

Источник

Smartadm.ru
Adblock
detector