Issue with p6spy Logging in Spring Boot Application

[abhishekjoy1]

I’m using p6spy-spring-boot-starter in my Spring Boot application to analyze MySQL queries. I’ve added the following dependencies in my pom.xml:

<dependency>
    <groupId>com.github.gavlyukovskiy</groupId>
    <artifactId>p6spy-spring-boot-starter</artifactId>
    <version>1.9.1</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>

In my application.properties, I’ve included the following property because I don’t want to generate any logs; instead, I want to set up monitoring based on captured metrics.

decorator.datasource.p6spy.enable-logging=false

Additionally, I’ve created a custom JDBC event listener to capture metrics:

public class CustomJdbcEventListener extends SimpleJdbcEventListener {

  private final MetricManager metricManager;

  public CustomJdbcEventListener(MetricManager metricManager) {
    this.metricManager = metricManager;
  }

  @Override
  public void onAfterAnyExecute(StatementInformation statementInformation,
                                long timeElapsedNanos,
                                SQLException e) {
    long timeElapsedMs = TimeUnit.NANOSECONDS.toMillis(timeElapsedNanos);
    String metricName = "SQL_STATEMENT " + statementInformation.getStatementQuery();
    metricManager.registerCustomMetric(metricName);
    metricManager.setCustomMetricsValue(metricName, timeElapsedMs);
  }
}

I’m injecting this listener using the following configuration:

@Configuration
public class CustomJdbcListenerConfiguration {

  @Bean
  public CustomJdbcEventListener getCustomJdbcEventListener(MetricManager metricManager) {
    return new CustomJdbcEventListener(metricManager);
  }
}

However, when I start the application, I get the following error:

java.lang.IllegalStateException: couldn't create PrintStream for spy.log (Permission Denied)

Despite setting decorator.datasource.p6spy.enable-logging=false, the application seems to be trying to create a spy.log file, which I don't want.

Any help would be greatly appreciated!

[gavlyukovskiy]

@abhishekjoy1 this starter sets slf4j logging by default, so the logfile should not be created. Can you please send minimal example that reproduces the issue?

[grove693]

I am also facing a similar issue. I have a sample spring boot app using p6spy-spring-boot-starter with the following configuration in the application.yml file:

  decorator.datasource:
      p6spy:
        enable-logging: false

The spy.log file is generated at every app startup. How can I disable the log file being generated? I am no interested in the logging functionalities of the p6spy library.

[gavlyukovskiy]

@grove693 if you can, please provide some minimal project where this problem can be reproduced. Otherwise nothing comes to my mind that could cause it

[grove693]

@gavlyukovskiy The cause to this behavior was the mismatch between the versions of Spring Boot and p6spy-spring-boot-starter.

I was using Spring Boot v. 2.7.12 and the newest version of p6spy-spring-boot-starter (1.9.2). What I found out is that for some reason, the P6SpyConfiguration bean (where the library properties are processed) was not initialized in the Spring Context.
Whenever I would start the app the spy.log file would be created that logged all the queries
The custom defined SimpleJdbcEventListener worked fine though.

I then switched to the following configuration: Spring Boot v. 2.7.12 and p6spy-spring-boot-starter v 1.8.1 and it worked smoothly (the properties were being read by the library).

Is there a version of p6spy-spring-boot-starter that works well with both Spring Boot v 2.x and v 3.x ? I'm working on a monitoring library that's using p6spy-spring-boot-starter to intercept JDBC calls and would want to avoid the situation where a random log file (the spy.log file) would be generated for the clients who are also using Spring Boot v 2.x

If you still need a sample repository with the minimal config to reproduce this issue let me know .

[grove693]

@gavlyukovskiy
Added the minimal project here:

https://github.com/grove693/decorator-demo

[gavlyukovskiy]

Thanks for doing some research on the issue! From the point of support of 2.7 and 3.x, it is quite difficult because of different java baselines, so I wont be releasing 1.8.x branch, but if you're willing to make an MR to make 1.9.x compatible with speing boot 2.7, then I'd accept that 👍

[grove693]

For anyone having similar issues, I have disabled the logging by configuring the P6Spy framework itself. I created a spy.properties file with the following content

modulelist=com.p6spy.engine.spy.P6SpyFactory

This might impact some functionalities of p6spy-spring-boot-starter, however the JdbcEventListeners still work fine (which is what I need for my use case).