[opencms-dev] how many MySQL connections does the import phase of the install need?

[kylekc]

I think I'm about one tweak away from getting OpenCMS working in ISP-land,
and not just on my PC.

[I has posted a couple of days ago, having problems getting the import phase
of the OpenCMS 5.0.0 install to run at all at one ISP. So I switched to
another. The import runs much better at the new ISP -- it runs!]

At the new ISP -- during the import phase of the install, while it's a good
sign that the import runs and generates output, I'm getting java exceptions
during connection attempts to MySQL, due to not enough MySQL connections
allowd for my account.

How many MySQL connections do I need to ask my ISP for, to allow the import
phase of the OpenCMS install to complete? I can ask my ISP to "up" my MySQL
connection limit (says their FAQ) but I need to know how many I need.

For example (repeats of this exception comprise most of the import log):

3637:  at com.opencms.boot.CmsSetupThread.run(CmsSetupThread.java:83)
3636:  at com.opencms.boot.CmsMain.startSetup(CmsMain.java:120)
3635:  at com.opencms.boot.CmsMain.begin(CmsMain.java:164)
3634:  at java.lang.reflect.Method.invoke(Method.java:324)
3633:  at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
.java:25)
3632:  at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
)
3631:  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
3630:  at com.opencms.core.CmsShell.commands(CmsShell.java:197)
3629:  at com.opencms.core.CmsShell.call(CmsShell.java:156)
3628:  at java.lang.reflect.Method.invoke(Method.java:324)
3627:  at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
.java:25)
3626:  at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
)
3625:  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
3624:  at
com.opencms.core.CmsShellCommands.importModuleFromDefault(CmsShellCommands.j
ava:1929)
3623:  at com.opencms.file.CmsRegistry.importModule(CmsRegistry.java:1894)
3622:  at com.opencms.file.CmsImport.importResources(CmsImport.java:221)
3621:  at com.opencms.file.CmsImport.importAllResources(CmsImport.java:781)
3620:  at
com.opencms.file.CmsImport.createPropertydefinition(CmsImport.java:303)
3619:  at
com.opencms.file.CmsObject.createPropertydefinition(CmsObject.java:1145)
3618:  at
com.opencms.file.genericSql.CmsResourceBroker.createPropertydefinition(CmsRe
sourceBroker.java:2216)
3617:  at
com.opencms.file.genericSql.CmsDbAccess.createPropertydefinition(CmsDbAccess
.java:1698)
3616: root cause was java.sql.SQLException: Communication failure during
handshake. Message from server: "User xxxxxxxxxxxx has already more than
'max_user_connections' active connections"
3615: com.opencms.core.CmsException: 4 Sql exception. Detailed error:
[com.opencms.file.mySql.CmsDbAccess] Communication failure during handshake.
Message from server: "User xxxxxxxxxxxx has already more than
'max_user_connections' active connections".

Meanwhile, my ISP has a FAQ that asserts:

<snip>
2.4. "User xyz has already more than 'max_user_connections'." error
This happens if your web application opens database connections but fails to
explicitly close them. Often the problem goes unnoticed during testing,
because garbage collection and MySQL engine will eventually time out the
forgotten connections; but once web site traffic grows new connections might
be opened at faster pace than they expire which leads to this error message.

The only possible solution is to fix your web application, to ensure that db
resources are always released as soon as they become unneccessary. We have
set per-account database connection limit high enough to ensure that
bug-free web application can comfortably serve over 100Gb/mo web traffic. If
your domain experiences higher traffic please contact us to get the limit
increased.

If you do not use database connection pooling and open database
connection(s) at the beginning of JSP/Servlets, make sure that the
connection(s) are always explicitly closed at the end of corresponding
JSP/Servlet (even if page processing exits prematurely). *NEVER* store
database connection objects in session attibutes, static variables, and
other places where database connections can hang unused for hours.

If you do use database connection pooling (which is not recommended at
shared hosting servers, but quite possible), we advice configuring your
database connection pooling library to keep at most 2 database connections
while idle, and at most 10 while there is recent activity. Do not forget to
specify autoReconnect=true parameter of JDBC URL, because most database
connection pooling libraries do not correctly handle timed out database
connections on their own.
</snip>