Solución de problemas de MySQL 8

No se pudo crear una conexión con el servidor de base de datos

Puede haber varias razones por las que la aplicación no consigue establecer el enlace con la base de datos; consulte el fichero de log, que podría dar pistas sobre lo que está ocurriendo.

Strong Password Encryption for Authentication

El último asistente de instalación de MySQL sugiere usar “Strong Password Encryption for Authentication” como se muestra en la siguiente captura:

Cuando la opción “Strong Password Encryption for Authentication” está activada en lugar de “Legacy Authentication Method”, está activando una conexión SSL por defecto. Esto significa que podría tener que leer algo de documentación y aplicar cambios a su configuración de conexión o a su server.xml para conectar con la base de datos. Normalmente, en este escenario, obtendrá un error como el que se muestra a continuación:

java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)
  at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2303) ~[tomcat-dbcp.jar:8.5.14]
  at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2043) ~[tomcat-dbcp.jar:8.5.14]
  at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1543) ~[tomcat-dbcp.jar:8.5.14]
  at org.hibernate.connection.DatasourceConnectionProvider.getConnection(DatasourceConnectionProvider.java:92) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
  at org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:113) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
  at org.hibernate.cfg.Configuration.buildSettingsInternal(Configuration.java:2863) [hibernate-core-3.6.10.Final.jar:3.6.10.Final]
  at org.hibernate.cfg.Configuration.buildSettings(Configuration.java:2859) [hibernate-core-3.6.10.Final.jar:3.6.10.Final]
  at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1870) [hibernate-core-3.6.10.Final.jar:3.6.10.Final]
  at com.openkm.dao.HibernateUtil.getSessionFactory(HibernateUtil.java:367) [classes/:6.4.43-DEV]
  at com.openkm.dao.HibernateUtil.getSessionFactory(HibernateUtil.java:182) [classes/:6.4.43-DEV]
  at com.openkm.servlet.RepositoryStartupServlet.init(RepositoryStartupServlet.java:99) [classes/:6.4.43-DEV]
  at javax.servlet.GenericServlet.init(GenericServlet.java:158) [servlet-api.jar:3.1.FR]
  at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1183) [catalina.jar:8.5.14]
  at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1099) [catalina.jar:8.5.14]
  at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:989) [catalina.jar:8.5.14]
  at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4931) [catalina.jar:8.5.14]
  at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5241) [catalina.jar:8.5.14]
  at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150) [catalina.jar:8.5.14]
  at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:752) [catalina.jar:8.5.14]
  at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:728) [catalina.jar:8.5.14]
  at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:734) [catalina.jar:8.5.14]
  at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:952) [catalina.jar:8.5.14]
  at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1823) [catalina.jar:8.5.14]
  at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_171]
  at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_171]
  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_171]
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_171]
  at java.lang.Thread.run(Thread.java:748) [na:1.8.0_171]
Caused by: java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:108) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:87) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:61) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:71) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:932) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:857) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:444) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:230) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:226) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at org.apache.tomcat.dbcp.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38) ~[tomcat-dbcp.jar:8.5.14]
  at org.apache.tomcat.dbcp.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:257) ~[tomcat-dbcp.jar:8.5.14]
  at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2313) ~[tomcat-dbcp.jar:8.5.14]
  at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2299) ~[tomcat-dbcp.jar:8.5.14]
  ... 27 common frames omitted
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: 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.
  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_171]
  at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_171]
  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_171]
  at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_171]
  at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:59) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:103) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:149) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:165) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.protocol.a.NativeProtocol.negotiateSSLConnection(NativeProtocol.java:355) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.protocol.a.NativeAuthenticationProvider.negotiateSSLConnection(NativeAuthenticationProvider.java:789) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.protocol.a.NativeAuthenticationProvider.proceedHandshakeWithPluggableAuthentication(NativeAuthenticationProvider.java:499) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.protocol.a.NativeAuthenticationProvider.connect(NativeAuthenticationProvider.java:217) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.protocol.a.NativeProtocol.connect(NativeProtocol.java:1411) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.NativeSession.connect(NativeSession.java:165) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:876) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  ... 35 common frames omitted
Caused by: javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateException: java.security.cert.CertPathValidatorException: Path does not chain with any of the trust anchors
  at sun.security.ssl.Alerts.getSSLException(Alerts.java:192) ~[na:1.8.0_171]
  at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1964) ~[na:1.8.0_171]
  at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:328) ~[na:1.8.0_171]
  at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:322) ~[na:1.8.0_171]
  at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1614) ~[na:1.8.0_171]
  at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:216) ~[na:1.8.0_171]
  at sun.security.ssl.Handshaker.processLoop(Handshaker.java:1052) ~[na:1.8.0_171]
  at sun.security.ssl.Handshaker.process_record(Handshaker.java:987) ~[na:1.8.0_171]
  at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1072) ~[na:1.8.0_171]
  at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1385) ~[na:1.8.0_171]
  at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1413) ~[na:1.8.0_171]
  at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1397) ~[na:1.8.0_171]
  at com.mysql.cj.protocol.ExportControlled.performTlsHandshake(ExportControlled.java:213) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.protocol.StandardSocketFactory.performTlsHandshake(StandardSocketFactory.java:206) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.protocol.a.NativeSocketConnection.performTlsHandshake(NativeSocketConnection.java:99) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.protocol.a.NativeProtocol.negotiateSSLConnection(NativeProtocol.java:350) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  ... 41 common frames omitted
Caused by: java.security.cert.CertificateException: java.security.cert.CertPathValidatorException: Path does not chain with any of the trust anchors
  at com.mysql.cj.protocol.ExportControlled$X509TrustManagerWrapper.checkServerTrusted(ExportControlled.java:280) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at sun.security.ssl.AbstractTrustManagerWrapper.checkServerTrusted(SSLContextImpl.java:985) ~[na:1.8.0_171]
  at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1596) ~[na:1.8.0_171]
  ... 52 common frames omitted
Caused by: java.security.cert.CertPathValidatorException: Path does not chain with any of the trust anchors
  at sun.security.provider.certpath.PKIXCertPathValidator.validate(PKIXCertPathValidator.java:154) ~[na:1.8.0_171]
  at sun.security.provider.certpath.PKIXCertPathValidator.engineValidate(PKIXCertPathValidator.java:80) ~[na:1.8.0_171]
  at java.security.cert.CertPathValidator.validate(CertPathValidator.java:292) ~[na:1.8.0_171]
  at com.mysql.cj.protocol.ExportControlled$X509TrustManagerWrapper.checkServerTrusted(ExportControlled.java:273) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  ... 54 common frames omitted

Tiene dos opciones:

• Configurar SSL para la conexión MySQL.
• Cambiar de “Strong Password Encryption for Authentication” a “Legacy Authentication Method.”

Nota

Algunas URLs que podrían serle útiles si decide continuar con la configuración SSL:

• https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-using-ssl.html
• https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-rsa-files-using-mysql.html

En Windows, es relativamente fácil activar el “Legacy Authentication Method”:

• Ejecute el instalador.
• Haga clic en “Reconfigure” junto a MySQL Server.
• Siga las pantallas del asistente y elija “Legacy Authentication Method.”

Driver JDBC antiguo

Si su fichero de log muestra el error de abajo:

Caused by: java.lang.NullPointerException: null
  at com.mysql.jdbc.ConnectionImpl.getServerCharset(ConnectionImpl.java:3005) ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
  at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1916) ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
  at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1845) ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
  at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1215) ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
  at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2255) ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
  at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2106) ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
  ... 41 common frames omitted

Está usando la versión incorrecta del driver para conectar con MySQL Server 8; debería hacer los siguientes cambios:

• Sustituya el MySQL-connector-java-5.1.38-bin.jar de su $TOMCAT_HOME/libs por el último driver JDBC para MySQL Server 8. Puede obtener el driver más reciente en https://dev.mysql.com/downloads/connector/j/.
• Cambie la configuración de conexión MySQL en su $TOMCAT_HOME/conf/server.xml; debería quedar algo así:

Precaución

En la última versión del driver JDBC de MySQL, el nombre de la clase com.mysql.jdbc.Driver ha cambiado a com.mysql.cj.jdbc.Driver.

<Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
            maxTotal="100" maxIdle="30" maxWaitMillis="10000" validationQuery="select 1"
            username="openkm" password="openkm" driverClassName="com.mysql.cj.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/okmdb?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8>

El valor de la zona horaria del servidor no se reconoce.

Si su fichero de log muestra el error de abajo o uno similar:

Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'CEST' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_171]
  at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_171]
  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_171]
  at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_171]
  at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:59) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:83) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:128) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.protocol.a.NativeProtocol.configureTimezone(NativeProtocol.java:2201) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.protocol.a.NativeProtocol.initServerSession(NativeProtocol.java:2225) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:1391) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:895) ~[mysql-connector-java-8.0.11.jar:8.0.11]
  ... 45 common frames omitted

Parece que el último driver JDBC de MySQL tiene un problema con la configuración de zona horaria de su sistema operativo; una solución rápida es indicar la zona horaria del servidor, por ejemplo, serverTimezone=UTC:

<Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
            maxTotal="100" maxIdle="30" maxWaitMillis="10000" validationQuery="select 1"
            username="openkm" password="openkm" driverClassName="com.mysql.cj.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/okmdb_test?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8&amp;serverTimezone=UTC"/>

Problema al conectar con su base de datos

Si obtiene un error como este:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Public Key Retrieval is not allowed

Necesita modificar su fichero conf/server.xml y añadir lo siguiente a la URL JDBC: allowPublicKeyRetrieval=true.

<Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
            maxTotal="100" maxIdle="30" maxWaitMillis="10000" validationQuery="select 1"
            username="openkm" password="openkm" driverClassName="com.mysql.cj.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/okmdb_test?allowPublicKeyRetrieval=true&autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8&amp;serverTimezone=UTC"/>