No suitable driver found for mysql connection pool on Payara 5.193.1

1
votes

I'm attempting to deploy a JavaEE application to a payara server running on a ubuntu server but when the application in run a database exception is thrown. I'd like to note that the same configuration and files run without any issue on my local windows machine. Here is some information on my configuration, if any other details are needed or missed please let me know.

MySQL Server Version 5.7

Database Exception

Local Exception Stack: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.4.payara-p2): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/user_demo?zeroDateTimeBehavior=convertToNull Error Code: 0

Persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
  <persistence-unit name="UserDemoPU" transaction-type="JTA">
    <jta-data-source>userdemodb</jta-data-source>
    <class>com.bspace.userdemo.entity.AccessLink</class>
    <class>com.bspace.userdemo.entity.Answer</class>
    <class>com.bspace.userdemo.entity.Criteria</class>
    <class>com.bspace.userdemo.entity.CriteriaPage</class>
    <class>com.bspace.userdemo.entity.Demo</class>
    <class>com.bspace.userdemo.entity.DemoUser</class>
    <class>com.bspace.userdemo.entity.Faq</class>
    <class>com.bspace.userdemo.entity.LookupTable</class>
    <class>com.bspace.userdemo.entity.Post</class>
    <class>com.bspace.userdemo.entity.Question</class>
    <class>com.bspace.userdemo.entity.QuestionToCriteria</class>
    <class>com.bspace.userdemo.entity.Tag</class>
    <class>com.bspace.userdemo.entity.TagToAnswer</class>
    <class>com.bspace.userdemo.entity.TagToFaq</class>
    <class>com.bspace.userdemo.entity.TagToPost</class>
    <class>com.bspace.userdemo.entity.UserToDemo</class>
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <shared-cache-mode>NONE</shared-cache-mode>
    <properties>
      <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/user_demo?zeroDateTimeBehavior=convertToNull"/>
      <property name="javax.persistence.jdbc.user" value="xxxxxxxxxx"/>
      <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
      <property name="javax.persistence.jdbc.password" value="xxxxxxxxxxxxx"/>
    </properties>
  </persistence-unit>
</persistence>

JDBC Connection Pool

Note most of these properties are automatically generated and removing them don't resolve the issue.

<jdbc-connection-pool datasource-classname="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" name="mysql_user_demo_udemoPool" res-type="javax.sql.DataSource">
      <property name="SelfDestructOnPingSecondsLifetime" value="0"></property>
      <property name="UseUsageAdvisor" value="false"></property>
      <property name="AllowSlaveDownConnections" value="false"></property>
      <property name="LoadBalanceBlacklistTimeout" value="0"></property>
      <property name="QueryTimeoutKillsConnection" value="false"></property>
      <property name="CacheServerConfiguration" value="false"></property>
      <property name="RoundRobinLoadBalance" value="false"></property>
      <property name="UseCursorFetch" value="false"></property>
      <property name="JdbcCompliantTruncation" value="true"></property>
      <property name="UseOnlyServerErrorMessages" value="true"></property>
      <property name="AllowPublicKeyRetrieval" value="false"></property>
      <property name="DefaultAuthenticationPlugin" value="com.mysql.jdbc.authentication.MysqlNativePasswordPlugin"></property>
      <property name="DontTrackOpenResources" value="false"></property>
      <property name="UseInformationSchema" value="false"></property>
      <property name="UseNanosForElapsedTime" value="false"></property>
      <property name="UseCompression" value="false"></property>
      <property name="EnableEscapeProcessing" value="true"></property>
      <property name="PasswordCharacterEncoding" value="UTF-8"></property>
      <property name="AutoDeserialize" value="false"></property>
      <property name="TcpRcvBuf" value="0"></property>
      <property name="CallableStatementCacheSize" value="100"></property>
      <property name="AutoSlowLog" value="true"></property>
      <property name="IgnoreNonTxTables" value="false"></property>
      <property name="UseJDBCCompliantTimezoneShift" value="false"></property>
      <property name="AllowNanAndInf" value="false"></property>
      <property name="TcpSndBuf" value="0"></property>
      <property name="ContinueBatchOnError" value="true"></property>
      <property name="Logger" value="com.mysql.jdbc.log.StandardLogger"></property>
      <property name="MaxAllowedPacket" value="-1"></property>
      <property name="PrepStmtCacheSqlLimit" value="256"></property>
      <property name="AllowMultiQueries" value="false"></property>
      <property name="StrictFloatingPoint" value="false"></property>
      <property name="PreparedStatementCacheSqlLimit" value="256"></property>
      <property name="CachePreparedStatements" value="false"></property>
      <property name="InitialTimeout" value="2"></property>
      <property name="UseUnicode" value="true"></property>
      <property name="AutoClosePStmtStreams" value="false"></property>
      <property name="UseServerPrepStmts" value="false"></property>
      <property name="IncludeThreadNamesAsStatementComment" value="false"></property>
      <property name="PreparedStatementCacheSize" value="25"></property>
      <property name="CreateDatabaseIfNotExist" value="false"></property>
      <property name="RollbackOnPooledClose" value="true"></property>
      <property name="SocketFactoryClassName" value="com.mysql.jdbc.StandardSocketFactory"></property>
      <property name="LoadBalanceValidateConnectionOnSwapServer" value="false"></property>
      <property name="CompensateOnDuplicateKeyUpdateCounts" value="false"></property>
      <property name="EnablePacketDebug" value="false"></property>
      <property name="JdbcCompliantTruncationForReads" value="true"></property>
      <property name="PadCharsWithSpace" value="false"></property>
      <property name="NoAccessToProcedureBodies" value="false"></property>
      <property name="UseTimezone" value="false"></property>
      <property name="ClientCertificateKeyStoreType" value="JKS"></property>
      <property name="Port" value="3306"></property>
      <property name="ClientInfoProvider" value="com.mysql.jdbc.JDBC4CommentClientInfoProvider"></property>
      <property name="IsInteractiveClient" value="false"></property>
      <property name="CachePrepStmts" value="false"></property>
      <property name="ProfileSQL" value="false"></property>
      <property name="ProfileSql" value="false"></property>
      <property name="MaxQuerySizeToLog" value="2048"></property>
      <property name="LoadBalanceHostRemovalGracePeriod" value="15000"></property>
      <property name="TreatUtilDateAsTimestamp" value="true"></property>
      <property name="DynamicCalendars" value="false"></property>
      <property name="UseSSL" value="false"></property>
      <property name="DisconnectOnExpiredPasswords" value="true"></property>
      <property name="DontCheckOnDuplicateKeyUpdateInSQL" value="false"></property>
      <property name="TrustCertificateKeyStoreType" value="JKS"></property>
      <property name="MaxRows" value="-1"></property>
      <property name="SlowQueryThresholdMillis" value="2000"></property>
      <property name="ResultSetSizeThreshold" value="100"></property>
      <property name="UseBlobToStoreUTF8OutsideBMP" value="false"></property>
      <property name="TransformedBitIsBoolean" value="false"></property>
      <property name="Pedantic" value="false"></property>
      <property name="SocksProxyPort" value="1080"></property>
      <property name="UseUltraDevWorkAround" value="false"></property>
      <property name="EnableQueryTimeouts" value="true"></property>
      <property name="LogXaCommands" value="false"></property>
      <property name="PrepStmtCacheSize" value="25"></property>
      <property name="SelfDestructOnPingMaxOperations" value="0"></property>
      <property name="ZeroDateTimeBehavior" value="exception"></property>
      <property name="CallableStmtCacheSize" value="100"></property>
      <property name="SecondsBeforeRetryMaster" value="30"></property>
      <property name="EmulateLocators" value="false"></property>
      <property name="LoginTimeout" value="0"></property>
      <property name="GatherPerfMetrics" value="false"></property>
      <property name="RetriesAllDown" value="120"></property>
      <property name="DetectCustomCollations" value="false"></property>
      <property name="NoDatetimeStringSync" value="false"></property>
      <property name="LoadBalanceStrategy" value="random"></property>
      <property name="UseOldUTF8Behavior" value="false"></property>
      <property name="LoadBalanceAutoCommitStatementThreshold" value="0"></property>
      <property name="UltraDevHack" value="false"></property>
      <property name="UseFastIntParsing" value="true"></property>
      <property name="StrictUpdates" value="true"></property>
      <property name="EmptyStringsConvertToZero" value="true"></property>
      <property name="UseLocalSessionState" value="false"></property>
      <property name="LoggerClassName" value="com.mysql.jdbc.log.StandardLogger"></property>
      <property name="NullNamePatternMatchesAll" value="true"></property>
      <property name="PopulateInsertRowWithDefaultValues" value="false"></property>
      <property name="ProfilerEventHandler" value="com.mysql.jdbc.profiler.LoggingProfilerEventHandler"></property>
      <property name="UseStreamLengthsInPrepStmts" value="true"></property>
      <property name="RunningCTS13" value="false"></property>
      <property name="UseOldAliasMetadataBehavior" value="false"></property>
      <property name="ReadOnlyPropagatesToServer" value="true"></property>
      <property name="SocketFactory" value="com.mysql.jdbc.StandardSocketFactory"></property>
      <property name="MaxReconnects" value="3"></property>
      <property name="ReportMetricsIntervalMillis" value="30000"></property>
      <property name="BlobsAreStrings" value="false"></property>
      <property name="SendFractionalSeconds" value="true"></property>
      <property name="CacheResultSetMetadata" value="false"></property>
      <property name="Paranoid" value="false"></property>
      <property name="AllowUrlInLocalInfile" value="false"></property>
      <property name="NoTimezoneConversionForTimeType" value="false"></property>
      <property name="EmulateUnsupportedPstmts" value="true"></property>
      <property name="ReconnectAtTxEnd" value="false"></property>
      <property name="RequireSSL" value="false"></property>
      <property name="UseHostsInPrivileges" value="true"></property>
      <property name="UseSSPSCompatibleTimezoneShift" value="false"></property>
      <property name="UseReadAheadInput" value="true"></property>
      <property name="ParseInfoCacheFactory" value="com.mysql.jdbc.PerConnectionLRUFactory"></property>
      <property name="DefaultFetchSize" value="0"></property>
      <property name="URL" value="jdbc:mysql://localhost:3306/user_demo?zeroDateTimeBehavior=convertToNull"></property>
      <property name="Url" value="jdbc:mysql://localhost:3306/user_demo?zeroDateTimeBehavior=convertToNull"></property>
      <property name="AllowMasterDownConnections" value="false"></property>
      <property name="CacheDefaultTimezone" value="true"></property>
      <property name="QueriesBeforeRetryMaster" value="50"></property>
      <property name="FunctionsNeverReturnBlobs" value="false"></property>
      <property name="DumpQueriesOnException" value="false"></property>
      <property name="LoadBalanceExceptionChecker" value="com.mysql.jdbc.StandardLoadBalanceExceptionChecker"></property>
      <property name="VerifyServerCertificate" value="true"></property>
      <property name="NetTimeoutForStreamingResults" value="600"></property>
      <property name="ProcessEscapeCodesForPrepStmts" value="true"></property>
      <property name="UseAffectedRows" value="false"></property>
      <property name="GatherPerformanceMetrics" value="false"></property>
      <property name="TinyInt1isBit" value="true"></property>
      <property name="MetadataCacheSize" value="50"></property>
      <property name="RewriteBatchedStatements" value="false"></property>
      <property name="CacheCallableStatements" value="false"></property>
      <property name="GetProceduresReturnsFunctions" value="true"></property>
      <property name="UseGmtMillisForDatetimes" value="false"></property>
      <property name="CapitalizeTypeNames" value="true"></property>
      <property name="ServerConfigCacheFactory" value="com.mysql.jdbc.PerVmServerConfigCacheFactory"></property>
      <property name="NoTimezoneConversionForDateType" value="true"></property>
      <property name="TcpTrafficClass" value="0"></property>
      <property name="AutoGenerateTestcaseScript" value="false"></property>
      <property name="CacheCallableStmts" value="false"></property>
      <property name="FailOverReadOnly" value="true"></property>
      <property name="LoadBalancePingTimeout" value="0"></property>
      <property name="LocatorFetchBufferSize" value="1048576"></property>
      <property name="RelaxAutoCommit" value="false"></property>
      <property name="UseFastDateParsing" value="true"></property>
      <property name="LoadBalanceEnableJMX" value="false"></property>
      <property name="OverrideSupportsIntegrityEnhancementFacility" value="false"></property>
      <property name="HoldResultsOpenOverStatementClose" value="false"></property>
      <property name="InteractiveClient" value="false"></property>
      <property name="UseJvmCharsetConverters" value="false"></property>
      <property name="PortNumber" value="3306"></property>
      <property name="UseDynamicCharsetInfo" value="true"></property>
      <property name="LogSlowQueries" value="false"></property>
      <property name="IncludeThreadDumpInDeadlockExceptions" value="false"></property>
      <property name="ReplicationEnableJMX" value="false"></property>
      <property name="SlowQueryThresholdNanos" value="0"></property>
      <property name="UseDirectRowUnpack" value="true"></property>
      <property name="UseSqlStateCodes" value="true"></property>
      <property name="IncludeInnodbStatusInDeadlockExceptions" value="false"></property>
      <property name="MaintainTimeStats" value="true"></property>
      <property name="PinGlobalTxToPhysicalConnection" value="false"></property>
      <property name="TcpNoDelay" value="true"></property>
      <property name="TraceProtocol" value="false"></property>
      <property name="AlwaysSendSetIsolation" value="true"></property>
      <property name="NullCatalogMeansCurrent" value="true"></property>
      <property name="YearIsDateType" value="true"></property>
      <property name="SocketTimeout" value="0"></property>
      <property name="UseServerPreparedStmts" value="false"></property>
      <property name="UseLocalTransactionState" value="false"></property>
      <property name="GenerateSimpleParameterMetadata" value="false"></property>
      <property name="ExplainSlowQueries" value="false"></property>
      <property name="UseColumnNamesInFindColumn" value="false"></property>
      <property name="ConnectTimeout" value="0"></property>
      <property name="ElideSetAutoCommits" value="false"></property>
      <property name="PacketDebugBufferSize" value="20"></property>
      <property name="RetainStatementAfterResultSetClose" value="false"></property>
      <property name="DumpMetadataOnColumnNotFound" value="false"></property>
      <property name="BlobSendChunkSize" value="1048576"></property>
      <property name="UseLegacyDatetimeCode" value="true"></property>
      <property name="UseUnbufferedInput" value="true"></property>
      <property name="AllowLoadLocalInfile" value="true"></property>
      <property name="ReadFromMasterWhenNoSlaves" value="false"></property>
      <property name="AutoReconnectForPools" value="false"></property>
      <property name="TcpKeepAlive" value="true"></property>
      <property name="ClobberStreamingResults" value="false"></property>
      <property name="serverName" value="localhost"></property>
      <property name="portNumber" value="3306"></property>
      <property name="databaseName" value="user_demo"></property>
      <property name="User" value="xxxxxxxxxxxxxxxx"></property>
      <property name="Password" value="xxxxxxxxxxxxxxxx"></property>
      <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
    </jdbc-connection-pool>

MySQL Connector Files

mysql-connector-java-5.1.48.jar

mysql-connector-java-8.0.17.jar

Both files are in the lib folder within payara/glassfish/lib

1
javamysqlpayara
Are you sure this is the right directory to put the lib files? This sounds like the installation directory?! <payara-home>/glassfish/domains/<domain>/lib directory is the preferred place to put these additional JAR files or the asadmin add-library command can be used to put the files into that directory. - Christoph John
BTW, why are you using two different versions of the mysql-connector JARs? Shouldn't one be enough? - Christoph John
I moved the connector jars to the lib folder for the domain and that seems to have resolved the issue although I don't know why this was a problem on the linux server and not on my local windows machine. If you reply as an answer I can mark it as accepted if you'd like. - Jeff W

1 Answers

0
votes

Libraries should be placed into the <payara-home>/glassfish/domains/<domain>/lib directory.

You could either copy them manually to that location or use the command

asadmin add-library <file>

Side note: the directory <payara-home>/glassfish/domains/<domain>/lib/ext is not supported anymore starting from Payara 5.192. Source: Payara 5.192 release notes