Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Modeling » Epsilon » jdbc emc problem: "Too many connections"(MySql error reply using jdbc emc)
jdbc emc problem: "Too many connections" [message #1772183] Wed, 06 September 2017 12:28 Go to next message
Gianluigi Proserpio is currently offline Gianluigi ProserpioFriend
Messages: 15
Registered: November 2015
Junior Member
Hi all,
I'm using ETL code and MySQL emc driver in order to transform from a MySQL source to a RDF file target
After some correct record trasformation I get this error from the MySQL server: "message from server: "Too many connections"".

This is the ETL code:

rule servicelocation
transform s : MySQL!servicelocation
to t : adms!ConformLoad
{
System.out.println('Rule servicelocation: ' + s.name );
t.UUID = s.mRID;
t.name = s.name;
t.aliasName = s.aliasName;
t.Terminals.add(CreateTerminal(s.Terminal1));
}

operation CreateTerminal(Terminal_ID: String ) {
System.out.println("CreateTerminal " + Terminal_ID);
var MyTerminal;
var TerminalSource = MySQL!terminal.allInstances. // Select the terminal in MySQL input db
selectOne(o|o.mRID = Terminal_ID);
if (TerminalSource.isUndefined()) {
System.out.println("Terminal " + Terminal_ID + " doesn't exist!");
} else { // Se esiste, ne crea uno equivalente in output
System.out.println("Terminal found: " + TerminalSource);
MyTerminal = new adms!Terminal;
MyTerminal.UUID = TerminalSource.mRID;
MyTerminal.name = TerminalSource.name;
// look for the Connectivity Node related to the terminal
//MyTerminal.ConnectivityNode = Connect2CN(TerminalSource.ConnectivityNode);
}
return MyTerminal;
}

I'm also attaching the full console output.

Any suggestion is appreciated, thanks,
Gianluigi
Re: jdbc emc problem: "Too many connections" [message #1772212 is a reply to message #1772183] Wed, 06 September 2017 18:36 Go to previous messageGo to next message
Horacio Hoyos is currently offline Horacio HoyosFriend
Messages: 197
Registered: October 2009
Location: UK
Senior Member

Hello Gianluigi,

My first guess is that the MySQL!terminal.allInstances is attempting to connect again to the database, so for each s : MySQL!servicelocation you get a new connection and eventually there are too many. But I have little experience with the MySQL driver. Can you share your DB (or a part of it) as part of a MWE[1] so we can take a closer look?

Thanks,

[1] https://www.eclipse.org/epsilon/doc/articles/minimal-examples/


Horacio Hoyos Rodriguez
Epsilon Committer
Research Assistant
University Of York
Re: jdbc emc problem: "Too many connections" [message #1772213 is a reply to message #1772183] Wed, 06 September 2017 18:36 Go to previous messageGo to next message
Horacio Hoyos is currently offline Horacio HoyosFriend
Messages: 197
Registered: October 2009
Location: UK
Senior Member

DBL POST


Horacio Hoyos Rodriguez
Epsilon Committer
Research Assistant
University Of York

[Updated on: Wed, 06 September 2017 18:36]

Report message to a moderator

Re: jdbc emc problem: "Too many connections" [message #1772641 is a reply to message #1772213] Wed, 13 September 2017 13:18 Go to previous messageGo to next message
Horacio Hoyos is currently offline Horacio HoyosFriend
Messages: 197
Registered: October 2009
Location: UK
Senior Member

Hello Gianluigi,

After looking at the driver I can confirm that it uses at most two connections to process the Epsilon data queries. Can you provide further information about your setup? At this point I can only imagine that you are executing multiple transformations at the same time, which eventually open too many connections. This might be unintended, but the connections are dropped only after the ETL execution finishes, so it might be the case that you are starting new executions while the others are finishing.

Another possible cause is that your MySQL configuration has a max. connections settings that is small and hence the error.

Please provides us any further information if you still need help,

Cheers,


Horacio Hoyos Rodriguez
Epsilon Committer
Research Assistant
University Of York
Re: jdbc emc problem: "Too many connections" [message #1772643 is a reply to message #1772641] Wed, 13 September 2017 13:51 Go to previous messageGo to next message
Gianluigi Proserpio is currently offline Gianluigi ProserpioFriend
Messages: 15
Registered: November 2015
Junior Member
Hi Horacio,
thanks for your answer.

I limited the max_connection to 5 instead of the original 151 on the server side and I get the same error but early, more or less after 5 select SQL (see attached log).
I also increase the max_connection from the original 151 to 200, and also the error will appear later on. The log file increase from 700 to 921 rows.

It could be that every "select ..." open a new connection ?

Gianluigi
Re: jdbc emc problem: "Too many connections" [message #1772881 is a reply to message #1772643] Mon, 18 September 2017 09:57 Go to previous messageGo to next message
Horacio Hoyos is currently offline Horacio HoyosFriend
Messages: 197
Registered: October 2009
Location: UK
Senior Member

Hi Gianluigi,

As stated before the driver only uses two connections... I need to investigate this further. In the mean time, can you pull your DB logs? Maybe we can get some more information on when are the connections created and by whom.

Also (is not related) you can change your "System.out" statements by EOL's native "print" and "println" methods:
...
s.name.println('Rule servicelocation: ');
t.UUID = s.mRID;
...


Can you provide a copy one your console output? Maybe it will provide further insight into how many times the rule executes before it fails.


Horacio Hoyos Rodriguez
Epsilon Committer
Research Assistant
University Of York
Re: jdbc emc problem: "Too many connections" [message #1778519 is a reply to message #1772881] Fri, 15 December 2017 15:27 Go to previous message
Horacio Hoyos is currently offline Horacio HoyosFriend
Messages: 197
Registered: October 2009
Location: UK
Senior Member

Hi,

For future reference, the JDBC driver uses ResourceSets (each of which uses a connection) to provide dynamic collections that are not cached in Java memory but instead accessed directly in the DB. It is good for memory management so the complete DB is not loaded into memory but has the down side that instantiating many collections from the JDBC model will result in "too many connections" errors. As a workaround be sure that in your EOL, ETL, EGL, etc scripts you keep the number of JDBC element access under control. In the OP's case, there was an allInstances access in a transformation rule (pseudo-code):
rule JDBC2SOmething
  transform r : Row
  to x: X {
      x.name = JDBC!Tables.all.select(....);
  }

So for each Row in the JDBC model, the "all" operation was invoked. Since each "all" method call created a new connection, then it will result in the mentioned error. If the select operation is static, you could move this to a "pre" block so it is called only once:
 
pre {
  var tables = JDBC!Tables.all.select(....);
}
rule JDBC2SOmething
  transform r : Row
  to x: X {
      x.name = tables.select(...);
  }


Horacio Hoyos Rodriguez
Epsilon Committer
Research Assistant
University Of York
Previous Topic:Basic EGL Practice
Next Topic: EWL: How to run a Rename wizard Eclipse Neon3
Goto Forum:
  


Current Time: Thu Sep 20 15:00:51 GMT 2018

Powered by FUDForum. Page generated in 0.01933 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top