Env Mgr - Changing Database Connection - MySQL Driver Issue

I want to change the database settings in the Environment Manager so that the event data coming from Virtualize is written to a MySQL database. I have done this in the past and it worked fine, however we are using newer MySQL drivers now and the CTP is giving me an error.

I have placed mysql-connector-java-8.0.11.jar in the /WEB-INF/lib/ folder on the application server as instructed. When I configure for my database settings and test the connection I get the following error:

The JDBC driver class com.mysql.jdbc.Driver was not found.
Add the JDBC connector jar to the WEB-INF/lib/ folder on the application server.

I can see that the error message references the OLD **MySQL driver. The **NEW driver class it should reference for this version should be com.mysql.cj.jdbc.Driver

How do I update the CTP so that it looks for the correct driver class? Downgrading to the previous driver is not an option.

Thanks,

JT

Comments

  • Matt LoveMatt Love Posts: 63 admin

    Hi JT,

    CTP should already check for both the old com.mysql.jdbc.Driver and new com.mysql.cj.jdbc.Driver classes. It's just the error message in the web UI that only shows the old version. Please restart Tomcat so that it will load the new jar file from the WEB-INF/lib/ folder. If a Tomcat restart does not help, then check the Tomcat catalina log file for more detailed information.

    Cheers,
    Matt

  • jefftuckerbofajefftuckerbofa Posts: 116

    Hi Matt,

    I had the admin on that server check the logs and it looks like it is only looking for the old driver. He has confirmed that the jar I mentioned was installed and that Tomcat had been restarted.

    19-Oct-2021 14:32:22.795 WARNING [http-nio-9000-exec-8] com.parasoft.concerto.em.web.controllers.EMDatabaseController.testConnection Cannot load JDBC driver class 'com.mysql.jdbc.Driver'
    org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.mysql.jdbc.Driver'

    Thanks,

    JT

  • jefftuckerbofajefftuckerbofa Posts: 116

    On the Database Configuration page in the Database Connection Settings section, how is the Type select list populated? Does CTP dynamically list all of the databases for which it finds drivers? Or, is there some configuration needed to set these up?

    Thanks,

    JT

  • Matt LoveMatt Love Posts: 63 admin

    Hi JT,

    Double check that the mysql-connector-java-8.0.11.jar was placed in the apache-tomcat/webapps/em/WEB-INF/lib folder. Also check that the file was not corrupted during file transfer.

    Regarding the database type select list in CTP, these are the database types that CTP officially supports, not what it detects.

    Best regards,
    Matt

  • jefftuckerbofajefftuckerbofa Posts: 116

    Ok. I think we're making progress. It's finding the driver now, however I am getting a connection error:

    Could not establish connection with provided parameters.

    I know from recent experience that we have had to tweak our connections to force the use of TLSv1.2. Is there a configuration for this somewhere in CTP?

    Thanks,

    JT

  • jefftuckerbofajefftuckerbofa Posts: 116

    Seeing this in the logs:

    org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Cannot load connection class because of underlying exception: com.mysql.cj.exceptions.WrongArgumentException: Malformed database URL, failed to parse the connection string near '=NO_BACKSLASH_ESCAPES&serverTimezone=America/New_York'.)

    JT

  • jefftuckerbofajefftuckerbofa Posts: 116

    Is it possible to edit the JDBC URL params used for the MySQL connection?

    JT

  • Matt LoveMatt Love Posts: 63 admin
    edited October 20

    You can try editing the connection url in apache-tomcat/webapps/em/config/db_config.xml
    By default for MySQL that file will look like this:

    <?xml version="1.0" encoding="UTF-8"?> <configuration> <db_config> <connection> <url>jdbc:mysql://localhost:3306/em?useUnicode=true&amp;characterEncoding=UTF-8&amp;sessionVariables=sql_mode=NO_BACKSLASH_ESCAPES&amp;useSSL=false&amp;allowPublicKeyRetrieval=true</url> <username>em</username> <password>em</password> </connection> </db_config> </configuration>

  • jefftuckerbofajefftuckerbofa Posts: 116

    Ok. We're trying a custom version of this config XML. Based on the exception I saw in our logs, it looks like the new driver we're using does not like this portion of the JDBC URL: sessionVariables=sql_mode=NO_BACKSLASH_ESCAPES

    It doesn't appear to be well-formed with those double equals

    JT

  • jefftuckerbofajefftuckerbofa Posts: 116

    We tried temporarily updating the db_config.xml with our MySQL parameters. When I navigated to the Database Configuration page I did see our server, port, schema, user, pass in the form fields. However, the connection still failed with:

    Malformed database URL, failed to parse the connection string near '=NO_BACKSLASH_ESCAPES&serverTimezone=America/New_York'

    Those params must be added by the CTP application because they were not in our JDBC URL.

    This is a blocker for us.

    JT

  • Matt LoveMatt Love Posts: 63 admin

    It sounds like you will need a product change for your specific use case. Please contact [email protected] to create a ticket.

  • jefftuckerbofajefftuckerbofa Posts: 116

    I did a bit more research and it looks like this is actually caused by a bug in the MySQL driver that was resolved in this version: mysql-connector-java-8.0.13.jar. We will test this next week. Thanks for your help. JT

    Here's a snippet from their changelog:

    Added the following entry to the Connector/J 8.0.13 changelog:

    "When a connection property's value contained an equal sign (???=???) in
    itself, an exception (???WrongArgumentException: Malformed database URL???)
    was thrown. This was due to an error in the parser for the connection URL,
    which has been corrected by this fix."

Sign In or Register to comment.