[MGNLCE-284] Installation from scratch of Magnolia is not working with the last version of Mysql driver (8.0.19) Created: 14/Feb/20  Updated: 23/Jun/22  Resolved: 17/Dec/21

Status: Closed
Project: Community Edition
Component/s: None
Affects Version/s: 6.2.11
Fix Version/s: 6.2.15

Type: Bug Priority: Neutral
Reporter: Mercedes Iruela Assignee: Jaroslav Simak
Resolution: Fixed Votes: 0
Labels: maintenance
Remaining Estimate: Not Specified
Time Spent: 1h
Original Estimate: Not Specified

Attachments: File catalina.out     XML File jackrabbit-pool-mysql8-search.xml     PNG File mysql8-java11.png     PNG File mysql8.png     PNG File password-change.png     PNG File qa.png    
Issue Links:
Problem/Incident
Relates
relates to DOCU-2247 Repository configuration detailed guide Closed
documentation
to be documented by MGNLCE-293 DOC: Installation from scratch of Mag... Closed
relation
supersession
is superseded by MGNLCE-323 Swap default Mysql repo config to Con... Closed
Template:
Acceptance criteria:
Empty
Task DoD:
[X]* Doc/release notes changes? Comment present?
[X]* Downstream builds green?
[X]* Solution information and context easily available?
[X]* Tests
[X]* FixVersion filled and not yet released
[ ]  Architecture Decision Record (ADR)
Bug DoR:
[X]* Steps to reproduce, expected, and actual results filled
[X]* Affected version filled
Documentation update required:
Yes
Date of First Response:
Epic Link: Support
Sprint: Global Maintenance 1, Global Maintenance 3
Story Points: 2

 Description   

Jackrabbit is not able to generate the database structure due to this error:

2020-02-14 14:35:07,538 ERROR org.apache.jackrabbit.core.RepositoryImpl         : failed to start Repository: org.apache.jackrabbit.core.state.ItemStateException: failed to read bundle (stacktrace on DEBUG log level): deadbeef-face-babe-cafe-babecafebabe: java.sql.SQLSyntaxErrorException: Table 'magnoliaauthor.version_bundle' doesn't exist
javax.jcr.RepositoryException: org.apache.jackrabbit.core.state.ItemStateException: failed to read bundle (stacktrace on DEBUG log level): deadbeef-face-babe-cafe-babecafebabe: java.sql.SQLSyntaxErrorException: Table 'magnoliaauthor.version_bundle' doesn't exist
 at org.apache.jackrabbit.core.version.InternalVersionManagerImpl.<init>(InternalVersionManagerImpl.java:208) ~[jackrabbit-core-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.RepositoryImpl.createVersionManager(RepositoryImpl.java:526) ~[jackrabbit-core-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.RepositoryImpl.<init>(RepositoryImpl.java:314) [jackrabbit-core-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.RepositoryImpl.create(RepositoryImpl.java:616) [jackrabbit-core-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.jndi.BindableRepository.createRepository(BindableRepository.java:141) [jackrabbit-core-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.jndi.BindableRepository.init(BindableRepository.java:117) [jackrabbit-core-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.jndi.BindableRepository.<init>(BindableRepository.java:106) [jackrabbit-core-2.18.4.jar:2.18.4
 at org.apache.jackrabbit.core.jndi.BindableRepositoryFactory.getObjectInstance(BindableRepositoryFactory.java:52) [jackrabbit-core-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.jndi.RegistryHelper.registerRepository(RegistryHelper.java:74) [jackrabbit-core-2.18.4.jar:2.18.4]
 at info.magnolia.jackrabbit.ProviderImpl.init(ProviderImpl.java:240) [magnolia-core-6.1.5-SNAPSHOT.jar:?]
 at info.magnolia.repository.DefaultRepositoryManager.loadRepository(DefaultRepositoryManager.java:259) [magnolia-core-6.1.5-SNAPSHOT.jar:?]
 at info.magnolia.repository.DefaultRepositoryManager.loadRepositories(DefaultRepositoryManager.java:222) [magnolia-core-6.1.5-SNAPSHOT.jar:?]
 at info.magnolia.repository.DefaultRepositoryManager.init(DefaultRepositoryManager.java:121) [magnolia-core-6.1.5-SNAPSHOT.jar:?]
 at info.magnolia.cms.beans.config.ConfigLoader.load(ConfigLoader.java:136) [magnolia-core-6.1.5-SNAPSHOT.jar:?]
 at info.magnolia.init.MagnoliaServletContextListener$1.doExec(MagnoliaServletContextListener.java:259) [magnolia-core-6.1.5-SNAPSHOT.jar:?]
 at info.magnolia.context.MgnlContext$VoidOp.exec(MgnlContext.java:407) [magnolia-core-6.1.5-SNAPSHOT.jar:?]
 at info.magnolia.context.MgnlContext$VoidOp.exec(MgnlContext.java:404) [magnolia-core-6.1.5-SNAPSHOT.jar:?]
 at info.magnolia.context.MgnlContext.doInSystemContext(MgnlContext.java:378) [magnolia-core-6.1.5-SNAPSHOT.jar:?]
 at info.magnolia.init.MagnoliaServletContextListener.startServer(MagnoliaServletContextListener.java:256) [magnolia-core-6.1.5-SNAPSHOT.jar:?]
 at info.magnolia.init.MagnoliaServletContextListener.contextInitialized(MagnoliaServletContextListener.java:182) [magnolia-core-6.1.5-SNAPSHOT.jar:?]
 at info.magnolia.init.MagnoliaServletContextListener.contextInitialized(MagnoliaServletContextListener.java:128) [magnolia-core-6.1.5-SNAPSHOT.jar:?]
 at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4643) [catalina.jar:9.0.10]
 at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5109) [catalina.jar:9.0.10]
 at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183) [catalina.jar:9.0.10]
 at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:742) [catalina.jar:9.0.10]
 at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:718) [catalina.jar:9.0.10]
 at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:703) [catalina.jar:9.0.10]
 at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1141) [catalina.jar:9.0.10]
 at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1876) [catalina.jar:9.0.10]
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:1.8.0_71]
 at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_71]
 at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75) [tomcat-util.jar:9.0.10]
 at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:112) [?:1.8.0_71]
 at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:1053) [catalina.jar:9.0.10]
 at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:428) [catalina.jar:9.0.10]
 at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1585) [catalina.jar:9.0.10]
 at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:308) [catalina.jar:9.0.10]
 at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:123) [catalina.jar:9.0.10]
 at org.apache.catalina.util.LifecycleBase.setStateInternal(LifecycleBase.java:424) [catalina.jar:9.0.10]
 at org.apache.catalina.util.LifecycleBase.setState(LifecycleBase.java:367) [catalina.jar:9.0.10]
 at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:966) [catalina.jar:9.0.10]
 at org.apache.catalina.core.StandardHost.startInternal(StandardHost.java:839) [catalina.jar:9.0.10]
 at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183) [catalina.jar:9.0.10]
 at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1427) [catalina.jar:9.0.10]
 at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1417) [catalina.jar:9.0.10]
 at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_71]
 at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75) [tomcat-util.jar:9.0.10]
 at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:134) [?:1.8.0_71]
 at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:943) [catalina.jar:9.0.10]
 at org.apache.catalina.core.StandardEngine.startInternal(StandardEngine.java:258) [catalina.jar:9.0.10]
 at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183) [catalina.jar:9.0.10]
 at org.apache.catalina.core.StandardService.startInternal(StandardService.java:422) [catalina.jar:9.0.10]
 at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183) [catalina.jar:9.0.10]
 at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:770) [catalina.jar:9.0.10]
 at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183) [catalina.jar:9.0.10]
 at org.apache.catalina.startup.Catalina.start(Catalina.java:682) [catalina.jar:9.0.10]
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_71]
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_71]
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_71]
 at java.lang.reflect.Method.invoke(Method.java:497) ~[?:1.8.0_71]
 at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:350) [bootstrap.jar:9.0.10]
 at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:492) [bootstrap.jar:9.0.10]
Caused by: org.apache.jackrabbit.core.state.ItemStateException: failed to read bundle (stacktrace on DEBUG log level): deadbeef-face-babe-cafe-babecafebabe: java.sql.SQLSyntaxErrorException: Table 'magnoliaauthor.version_bundle' doesn't exist
 at org.apache.jackrabbit.core.persistence.pool.BundleDbPersistenceManager.loadBundle(BundleDbPersistenceManager.java:902) ~[jackrabbit-core-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.persistence.bundle.AbstractBundlePersistenceManager.getBundleCacheMiss(AbstractBundlePersistenceManager.java:766) ~[jackrabbit-core-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.persistence.bundle.AbstractBundlePersistenceManager.getBundle(AbstractBundlePersistenceManager.java:750) ~[jackrabbit-core-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.persistence.bundle.AbstractBundlePersistenceManager.exists(AbstractBundlePersistenceManager.java:563) ~[jackrabbit-core-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.version.InternalVersionManagerImpl.<init>(InternalVersionManagerImpl.java:162) ~[jackrabbit-core-2.18.4.jar:2.18.4]
 ... 61 more
Caused by: java.sql.SQLSyntaxErrorException: Table 'magnoliaauthor.version_bundle' doesn't exist
 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.19.jar:8.0.19]
 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.19.jar:8.0.19]
 at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.19.jar:8.0.19]
 at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.19.jar:8.0.19]
 at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370) ~[mysql-connector-java-8.0.19.jar:8.0.19]
 at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) ~[commons-dbcp-1.4.jar:1.4]
 at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) ~[commons-dbcp-1.4.jar:1.4]
 at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) ~[commons-dbcp-1.4.jar:1.4]
 at org.apache.jackrabbit.core.util.db.ConnectionHelper.execute(ConnectionHelper.java:524) ~[jackrabbit-data-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.util.db.ConnectionHelper.reallyExec(ConnectionHelper.java:411) ~[jackrabbit-data-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.util.db.ConnectionHelper$3.call(ConnectionHelper.java:385) ~[jackrabbit-data-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.util.db.ConnectionHelper$3.call(ConnectionHelper.java:381) ~[jackrabbit-data-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.util.db.ConnectionHelper$RetryManager.doTry(ConnectionHelper.java:552) ~[jackrabbit-data-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.util.db.ConnectionHelper.exec(ConnectionHelper.java:388) ~[jackrabbit-data-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.persistence.pool.BundleDbPersistenceManager.loadBundle(BundleDbPersistenceManager.java:886) ~[jackrabbit-core-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.persistence.bundle.AbstractBundlePersistenceManager.getBundleCacheMiss(AbstractBundlePersistenceManager.java:766) ~[jackrabbit-core-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.persistence.bundle.AbstractBundlePersistenceManager.getBundle(AbstractBundlePersistenceManager.java:750) ~[jackrabbit-core-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.persistence.bundle.AbstractBundlePersistenceManager.exists(AbstractBundlePersistenceManager.java:563) ~[jackrabbit-core-2.18.4.jar:2.18.4]
 at org.apache.jackrabbit.core.version.InternalVersionManagerImpl.<init>(InternalVersionManagerImpl.java:162) ~[jackrabbit-core-2.18.4.jar:2.18.4]
 ... 61 more


 Comments   
Comment by Mercedes Iruela [ 14/Feb/20 ]

Workaround: mysql drivers 5.1.* works as expected (tested v5.1.47)

Comment by Richard Gange [ 28/Apr/20 ]

I was also using MySQL version 8.0.11. So both the driver and the database are at version 8. Perhaps you are trying to use an old database version with a newer driver?

Comment by Mercedes Iruela [ 28/Apr/20 ]

I used mysql 8.0.16. Jonathan uses 8.0.11

Comment by Richard Gange [ 28/Apr/20 ]

Then there must be something else causing it. Here is the screenshot of the details.

Comment by Richard Gange [ 28/Apr/20 ]

Also:

Comment by Mercedes Iruela [ 28/Apr/20 ]

Was this instance created from scratch? The issue is just related to a new installations.

Comment by Richard Gange [ 28/Apr/20 ]

Yes, I only had the database defined. 

It seems that you could easily workaround the issue by creating the table(s) manually. In any case, it seems to be an issue with JR and MySQL not Magnolia. Maybe take a look and see if you can find any tickets there. There is probably a chance others have hit this problem. 

Comment by Richard Gange [ 28/Apr/20 ]

Here is my startup log: catalina.out

Comment by Richard Gange [ 06/Jul/21 ]

We probably need to leave this as open until we have a solid answer about this. I think customers are ok with the workaround but at some point we need a solution.

Comment by Marcello Romani [ 22/Sep/21 ]

Hi Richard,

just wanted to mention we experienced this very bug, but the odd thing is we had the exact same Magnolia installation (exact same version numbers for JDBC driver, mgnl, mysql db etc.) work flawlessly for a number of times. By work flawlessly I mean the tables were created at startup in an empty database with no issue.

 

I'm testing https://documentation.bloomreach.com/14/library/deployment/configuring/configuring-hippo-for-mysql.html

 

When using mysql-connector 8.x with a MySQL 5.x database, jackrabbit tries to fetch the metadata of the database, checks whether jackrabbit tables have been created. Because of a change in mysql-connector 8.x library, connector fetchs metadata of all authorized server data regardless of the selected database. When another non-empty jackrabbit database exists in MySQL 5.x server, because of the wrong metadata which is provided by the connector, jackrabbit assumes that the tables have already been created and skips table generation. After that, when repository tries to query to database, query fails since jackrabbit tables don't exist in the selected database.
To solve this problem, parameter nullDatabaseMeansCurrent (nullCatalogMeansCurrent for mysql-connector versions older than 8.0.17) should be used.
jdbc:mysql://DBHOST:DBPORT/DBNAME?characterEncoding=utf88&useSSL=false&nullDatabaseMeansCurrent=true

and it seems like it's working!

Comment by Richard Gange [ 22/Sep/21 ]

Hello Marcello-

Thanks for the input. So I'm thinking maybe we should be providing an updated version of the mysql repo config for version 8. I propose jackrabbit-pool-mysql8-search.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE Repository PUBLIC "-//The Apache Software Foundation//DTD Jackrabbit 2.0//EN" "http://jackrabbit.apache.org/dtd/repository-2.0.dtd">
<Repository>
  <DataSources>
    <DataSource name="magnolia">
      <param name="driver" value="com.mysql.cj.jdbc.Driver" />
      <param name="url" value="jdbc:mysql://localhost:3306/magnolia?serverTimezone=UTC&useSSL=false&nullDatabaseMeansCurrent=true" />
      <param name="user" value="root" />
      <param name="password" value="password" />
      <param name="databaseType" value="mysql"/>
      <param name="validationQuery" value="select 1"/>
    </DataSource>
  </DataSources>
  ...
  ...
Comment by Marcello Romani [ 22/Sep/21 ]

FWIW that looks good to me, it's what fixed our issue.

Comment by Mikaël Geljić [ 24/Sep/21 ]

rgange Let's move to MGNLCE and create a pull-request (into empty-webapp, where we maintain those repo-confs).

I'm considering whether we should make mysql driver 8 the default, and leave commented-out parameters for older versions.
On the one hand, nullDatabaseMeansCurrent most likely doesn't bother if we're dealing with an older mysql driver that would otherwise work; on the other hand, the driver class itself changed in Connector/J 8.0.

Rather spare this for a major, let's do as you suggested on 6.2.

Comment by Jaroslav Simak [ 30/Nov/21 ]

Cannot reproduce with following setup:

Magnolia DX Core 6.2.14-SNAPSHOT

Server version: 5.7.36 MySQL Community Server (GPL) 

mysql-connector-java-8.0.19.jar

mysql-connector-java-8.0.27.jar (latest version) works as well

All tables are created without any change to jackrabbit-bundle-mysql-search.xml.

 

Edit: Changing the jdbc driver to com.mysql.cj.jdbc.Driver does not make any difference – all tables created.

Comment by Mikaël Geljić [ 07/Dec/21 ]

As far as I see, it depends on mysql-connector-java version. On 8.0.x, the old driver class is still around, should display a harmless error message

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

while the new Driver is automatically loaded, as per NonRegisteringDriver javadoc.

The DriverManager will try to load as many drivers as it can find and then for any given connection request, it will ask each driver in turn to try to connect to the target URL.

If we have customers still using mysql-connector-java:5.1.x, this might cause issues (easy to workaround, by keeping a copy of the jackrabbit config file instead of inheriting it). Let's see if we know more about current mysql connector usage.

Comment by Richard Gange [ 07/Dec/21 ]

For the record, it was not always reproducible. On my machine I never had a problem.

Comment by Dai Ha [ 20/Dec/21 ]

Verified with below configuration

Generated at Mon Feb 12 00:07:54 CET 2024 using Jira 9.4.2#940002-sha1:46d1a51de284217efdcb32434eab47a99af2938b.