Win a copy of Spring in Action (5th edition) this week in the Spring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

Possible to use SLFJ4/Log4j2 to check connection to database?  RSS feed

 
Ranch Hand
Posts: 430
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have tried to check my connectivity problem but am not sure the right way of doing things so hope to get some advice here.

Most of the codes are the same old codes that are working ok (including connectivity) before  I made the app conforming to Maven standard structure.

Basically, I can get a ping successfully to MYSQL 8 but when I run my webApp, the console will say ERROR: Unable to Connect to Database.

I'd like to know if it is possible to use SLFJ4 to check what cause the connectivity problem and is there any tutorial out there.  

Most of the tutorial I found are on Spring or hibernate.

 
tangara goh
Ranch Hand
Posts: 430
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think I found something that can help me in :

https://stackoverflow.com/questions/13719787/send-log4j-info-to-database-and-debug-to-console

But, I need someone to tell me what is logger name referring to ?  Is it the class that we need to use Log4j2 for debugging ?

<logger name="com.mycompany">
       <level value="INFO" />
       <appender-ref ref="DBAppender" />
   </logger>

So, for my case, I will use the class where I have my connectivity code to put under logger name ?
 
tangara goh
Ranch Hand
Posts: 430
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have another question ; How do I make the below INFO onto console and not into database ?

<param name="sql"
           value="INSERT INTO logs(user_id, dated,logger,lev,message) VALUES('%t', '%d{yyyy-MM-dd HH:mm:ss}','%-50c{3}','%p','%m')" />
 
Saloon Keeper
Posts: 2153
278
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

tangara goh wrote:I can get a ping successfully to MYSQL 8


How did you do that?

tangara goh wrote:but when I run my webApp, the console will say ERROR: Unable to Connect to Database.


Where is that message coming from?

How are you connecting to the database - using a DataSource or the DriverManager?

 
tangara goh
Ranch Hand
Posts: 430
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Ron McLeod wrote:

tangara goh wrote:I can get a ping successfully to MYSQL 8


How did you do that?

Using Data Explorer in Eclipse where you input all the connections parameters.

tangara goh wrote:but when I run my webApp, the console will say ERROR: Unable to Connect to Database.


Where is that message coming from?

The message is printed to console I think it is from TOMCAT

How are you connecting to the database - using a DataSource or the DriverManager?



I am using DriverManager
 
Bartender
Posts: 19984
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Don't use DriverManager in a webapp. It's crude and inefficient. Use a DataSource:

https://www.journaldev.com/2513/tomcat-datasource-jndi-example-java

If you use a DataSource, it becomes Tomcat's job to ensure connection to the database and Tomcat has very good diagnostics.
 
tangara goh
Ranch Hand
Posts: 430
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:Don't use DriverManager in a webapp. It's crude and inefficient. Use a DataSource:

https://www.journaldev.com/2513/tomcat-datasource-jndi-example-java

If you use a DataSource, it becomes Tomcat's job to ensure connection to the database and Tomcat has very good diagnostics.



Tks for the tutorial.  I will try to change it but I need to tackle a harder problem so I am changing that later.

I got the cause of mysql connection not working properly.

Could you let me know what is wrong cos I can't decipher what is wrong.


23:05:48.732 [http-nio-8013-exec-2] ERROR daoMySql.tutorDAOImpl - Failed to obtain JDBC connection
java.sql.SQLNonTransientConnectionException: Cannot load connection class because of underlying exception: com.mysql.cj.exceptions.WrongArgumentException: Malformed database URL, failed to parse the connection string near ';autoReconnect=true&useSSL=false; &logger=com.mysql.cj.jdbc.log.Slf4JLogger&profileSQL=true'.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110) ~[mysql-connector-java-8.0.12.jar:8.0.12]



And here's my connection :

 
Ron McLeod
Saloon Keeper
Posts: 2153
278
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This looks suspicious:
 
tangara goh
Ranch Hand
Posts: 430
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Ron McLeod wrote:This looks suspicious:



OK Ron,  I corrected but now I kept getting this error and no matter what I do, it still continues giving me this error message :


SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/C:/Users/K/.m2/repository/org/apache/logging/log4j/log4j-slf4j-impl/2.11.1/log4j-slf4j-impl-2.11.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/C:/Users/K/.m2/repository/org/slf4j/slf4j-nop/1.7.25/slf4j-nop-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
[Fatal Error] :17:116: The reference to entity "useSSL" must end with the ';' delimiter.
ERROR StatusLogger Error parsing C:\Users\K\eclipse-workspace\webApp5\target\classes\log4j2.xml org.xml.sax.SAXParseException; lineNumber: 17; columnNumber: 116; The reference to entity "useSSL" must end with the ';' delimiter.



And here's my modified String URL :


<param name="URL" value="jdbc:mysql://localhost/project?serverTimezone=UTC&autoReconnect=true&useSSL=False;"/>
     



I am really lost.......
 
Rancher
Posts: 3753
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There should not be a semi-colon at the end there, and (I believe) False should be false.
 
Tim Holloway
Bartender
Posts: 19984
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

tangara goh's log wrote:
[Fatal Error] :17:116: The reference to entity "useSSL" must end with the ';' delimiter.
ERROR StatusLogger Error parsing C:\Users\K\eclipse-workspace\webApp5\target\classes\log4j2.xml org.xml.sax.SAXParseException; lineNumber: 17; columnNumber: 116; The reference to entity "useSSL" must end with the ';' delimiter.



However, since I'm pretty certain that useSSL's normal value is false, that option could be omitted.
 
Ron McLeod
Saloon Keeper
Posts: 2153
278
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

tangara goh wrote:I corrected but now I kept getting this error and no matter what I do, it still continues giving me this error message :
And here's my modified String URL :


It seems like the original issue that you had has been resolved, and you are chasing a different problem now, and that this new problem seems to be related configuring log4j to log to a database -- is that correct?

 
Tim Holloway
Bartender
Posts: 19984
95
Android Eclipse IDE Linux
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think I see.

You're being nuked by XML, it appears.

Try this syntax, instead:



The ampersand is one of the 5 "magic characters" in XML. It indicates that everything up to a terminating ";" should be considered as a name or value of an XML entity.

Except that the XML parser doesn't see the ';' before it sees the "=" and it chokes. Using the "&amp;" entity name escapes that behaviour.
 
tangara goh
Ranch Hand
Posts: 430
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:I think I see.

You're being nuked by XML, it appears.

Try this syntax, instead:



The ampersand is one of the 5 "magic characters" in XML. It indicates that everything up to a terminating ";" should be considered as a name or value of an XML entity.

Except that the XML parser doesn't see the ';' before it sees the "=" and it chokes. Using the "&amp;" entity name escapes that behaviour.



Tks Tim,

But now I got another unresolvable error message as follows :

<quote>
The content of element type "appender" must match "(errorHandler?,param*,rollingPolicy?,triggeringPolicy?,connectionSource?,layout?,filter*,appender-ref*)".
</quote>

Here's the log4j2.xml :


<!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/xml/doc-files/log4j.dtd">


<log4j:configuration debug="false" <br />    xmlns:log4j="http://jakarta.apache.org/log4j/">
   <!-- APPENDERS LIST -->
   <!-- show log entries on console -->

   <appender name="ConsoleAppender" class="org.apache.log4j.ConsoleAppender">
       <layout class="org.apache.log4j.PatternLayout">
           <param name="ConversionPattern" value="%d{ISO8601} [%-5p][%-16.16t][%32.32c] - %m%n" />
        <param name="Threshold" value="info"/>
       </layout>
   </appender>

   <appender name="ConsoleAppender" class="org.apache.log4j.jdbc.JDBCAppender">
       <param name="URL" value="jdbc:mysql://localhost/project?serverTimezone=UTC&autoReconnect=true&useSSL=False;"/>
       <param name="driver" value="com.mysql.cj.jdbc.Driver" />
       <param name="user" value="user" />
       <param name="password" value="password" />
       <Column name="LOGGER" pattern="%C" />
       <Column name="Level" pattern="%level" />
       <Column name="Message" pattern="%m" />
       <Column name="UserName" pattern="%x" />
       <Column name="Priority" pattern="%p" />
       <param name="sql" <br />            value="INSERT INTO logs(user_id, dated,logger,lev,message) VALUES('%t', '%d{yyyy-MM-dd HH:mm:ss}','%-50c{3}','%p','%m')" />
   </appender>

   <root>
       <priority value="ERROR" />
       <appender-ref ref="ConsoleAppender" />
   </root>

</log4j:configuration>




 
tangara goh
Ranch Hand
Posts: 430
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I just want to add that the log4j2.xml is working fine but the old error still appears.

com.mysql.cj.exceptions.WrongArgumentException: Malformed database URL, failed to parse the connection string near ';useSSL=FALSE&logger=com.mysql.cj.jdbc.log.Slf4JLogger&profileSQL=true'.

What is the problem in this case ?
 
Dave Tolls
Rancher
Posts: 3753
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's different from the original error, so what does your connection string look like now?
 
tangara goh
Ranch Hand
Posts: 430
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:That's different from the original error, so what does your connection string look like now?



Hi Dave,

Here's the latest URL value.



May I know what the error is about cos I have been trying very hard but to no avail.

 
Dave Tolls
Rancher
Posts: 3753
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, that one doesn't seem to match the error posted earlier.
Are you sure that ULR caused that error?
 
tangara goh
Ranch Hand
Posts: 430
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Well, that one doesn't seem to match the error posted earlier.
Are you sure that ULR caused that error?



Hi Dave,

Yes.  I double check and ran the jsp again and this is exactly what I got.

Here's the URL again:






 
Dave Tolls
Rancher
Posts: 3753
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you show the bit of code around here, including any relevant static values used?
daoMySql.tutorDAOImpl.openConnection(tutorDAOImpl.java:50)
 
tangara goh
Ranch Hand
Posts: 430
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Dave,

Basically, all my connection URL are static and I stored them in a singleton class.

So, I just use the tutorImpl to get the connection from there.

And the error points out that it comes from that URL string....

 
Sheriff
Posts: 5446
147
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The &amp;s have turned back into just &.
 
Dave Tolls
Rancher
Posts: 3753
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

tangara goh wrote:Hi Dave,

Basically, all my connection URL are static and I stored them in a singleton class.

So, I just use the tutorImpl to get the connection from there.

And the error points out that it comes from that URL string....



So what were these other ones that were in what I assume is an XML file then?

An '&' is fine if it's hard coded into your Java code, but you will hit issues with it in an XML file (as states earlier).

We need to know exactly where these values come from, and that will involve posting the relevant code here.
 
tangara goh
Ranch Hand
Posts: 430
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Dave,

There is indeed a log4j.xml file where I put in the required URL.

<Appenders>
   <Console name="Console" target="SYSTEM_OUT">
     <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n"/>
   </Console>
    <param name="URL" value="jdbc:mysql://localhost/project?serverTimezone=UTC&autoReconnect=true&useSSL=False;"/>
       <param name="driver" value="com.mysql.cj.jdbc.Driver" />

Should I do the URL differently; meaning the syntax is not the same in the java code ?

Please advise me how it should be in the xml.

Tks.

 
Knute Snortum
Sheriff
Posts: 5446
147
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Change all the & to &amp;
 
tangara goh
Ranch Hand
Posts: 430
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Knute Snortum wrote:Change all the & to &amp;



Hi Knute,

It is still giving me the same error

java.sql.SQLNonTransientConnectionException: Cannot load connection class because of underlying exception: com.mysql.cj.exceptions.WrongArgumentException: Malformed database URL, failed to parse the connection string near ';useSSL=FALSE&logger=com.mysql.cj.jdbc.log.Slf4JLogger&profileSQL=true'.

 
Dave Tolls
Rancher
Posts: 3753
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you show us the code that is throwing that exception?
Including where the URL value is declared.

That URL that it is complaining about is not the one you are currently showing us.
 
Tim Holloway
Bartender
Posts: 19984
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Knute Snortum wrote:The &amp;s have turned back into just &.



Note for the confused: The JavaRanch message display system also evaluates the '&amp;' entity, so make sure when you type or copy an example to preview the output. If it's getting mangled, use the escapes to make it display as you are actually seeing it on your machine.
 
tangara goh
Ranch Hand
Posts: 430
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:

Knute Snortum wrote:The &amp;s have turned back into just &.



Note for the confused: The JavaRanch message display system also evaluates the '&amp;' entity, so make sure when you type or copy an example to preview the output. If it's getting mangled, use the escapes to make it display as you are actually seeing it on your machine.



Hi Tim,

I have tried to put '&' and '&" but still getting the below error.

It seems like this problem cannot be resolved.  What are the alternatives for me in this case?

ERROR: Unable to Connect to Database.
16:12:51.800 [http-nio-8013-exec-3] ERROR daoMySql.tutorDAOImpl - Failed to obtain JDBC connection
java.sql.SQLNonTransientConnectionException: Cannot load connection class because of underlying exception: com.mysql.cj.exceptions.WrongArgumentException: Malformed database URL, failed to parse the connection string near ';UseLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=False'.

I also tried with a small public static void main(String[] args) {

and when I use just & without ;, everything works ok.

So, I am really stuck and it's been so long already.

What do you suggest ?
 
tangara goh
Ranch Hand
Posts: 430
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When I just used "amp&" without ; on the small test programme, it works fine.
 
Knute Snortum
Sheriff
Posts: 5446
147
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you really mean "amp&" and not "&amp"?
 
tangara goh
Ranch Hand
Posts: 430
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Knute Snortum wrote:Do you really mean "amp&" and not "&amp"?



Sorry I meant "& without ;"


 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!