Resolució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 pueda establecer el enlace con la base de datos; consulte el fichero de log, que puede 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á habilitada en vez de “Legacy Authentication Method”, está habilitando una conexión SSL por defecto. Esto significa que puede que necesite consultar documentación y aplicar cambios a su configuración de conexión o a su server.xml para conectar con la base de datos. Habitualmente, 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 pueden serle útiles si decide seguir adelante 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 habilitar 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 una versión de driver incorrecta para conectar con MySQL Server 8; debería hacer los siguientes cambios:

• Sustituya MySQL-connector-java-5.1.38-bin.jar, en su $TOMCAT_HOME/libs, por el driver JDBC más reciente para MySQL Server 8. Puede obtener el driver más nuevo 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 ser 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 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 driver JDBC más reciente 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"/>