To sum up very simply before going into detail the problem is as follows. I have a method in which I run ALL my updates in batch on different tables in different databases. However on 3 tables coming from two different databases (and only there!), my queries are not executed on all my batch but only part of it. Let's move on to the real examples and the code.
First some basics informations. This is my pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns=".0.0" xmlns:xsi=";
xsi:schemaLocation=".0.0 .0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.11</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>fr.xxxxxxx.data.rgpd</groupId>
<artifactId>xxxxxxxAnonymizeData</artifactId>
<version>2.0.2</version>
<name>xxxxxxxAnonymizeData</name>
<description>RGPD-Compliance : anonymisation des donnees personnelles</description>
<properties>
<java.version>17</java.version>
</properties>
<dependencies>
<dependency>
<groupId>javax.xml.bind</groupId>
<artifactId>jaxb-api</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-mail</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>fr.xxxxxxx.tools</groupId>
<artifactId>xxxxxxxcommons</artifactId>
<version>1.16</version>
</dependency>
<!-- data access -->
<dependency>
<groupId>org.mongodb</groupId>
<artifactId>mongo-java-driver</artifactId>
<version>3.12.10</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-mongodb</artifactId>
</dependency>
<!-- Test -->
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>de.flapdoodle.embed</groupId>
<artifactId>de.flapdoodle.embed.mongo</artifactId>
<version>3.4.3</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<artifactId>maven-surefire-plugin</artifactId>
<configuration>
<argLine>
--add-opens java.base/java.lang=ALL-UNNAMED
</argLine>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-jar-plugin</artifactId>
<configuration>
<excludes>
<exclude>**/logback.xml</exclude>
</excludes>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-resources-plugin</artifactId>
<version>3.1.0</version>
</plugin>
</plugins>
</build>
<distributionManagement>
<repository>
<id>xxxxxxx-release</id>
<uniqueVersion>true</uniqueVersion>
<name>Nexus xxxxxxx release</name>
<url>/</url>
<layout>default</layout>
</repository>
<snapshotRepository>
<id>xxxxxxx-snapshot</id>
<uniqueVersion>true</uniqueVersion>
<name>Nexus xxxxxxx snapshot</name>
<url>/</url>
<layout>default</layout>
</snapshotRepository>
</distributionManagement>
<repositories>
<repository>
<id>xxxxxxx-release</id>
<url>/</url>
</repository>
<repository>
<id>maven-public</id>
<url>/</url>
</repository>
</repositories>
</project>
Here is my portion of code that I use to make batch updates (with some debug message all along to find my problem)
public @NonNull List<ErrorUpdateClient> queryFactoryUpdate(Set<ClientGlobalActivity> clientSet, Base base, Environnement env, String sql, String nomRequete){
if(clientSet == null || clientSet.isEmpty()){
return new ArrayList<>();
}
else if (clientSet.size() == 1 ){
return queryFactoryUpdate(clientSet.stream().findFirst().get(), base, env, sql, nomRequete);
}
else{
return queryFactoryBatchUpdate(clientSet, base, env, sql, nomRequete);
}
}
public @NonNull List<ErrorUpdateClient> queryFactoryBatchUpdate(Set<ClientGlobalActivity> clientSet, Base base, Environnement env, String sql, String nomRequete) {
logger.info("DEBUT - DAO - appel de la méthode [ " + nomRequete + " ]");
LocalDateTime debut = LocalDateTime.now();
List<ErrorUpdateClient> updateClientResults = new ArrayList<>();
try {
final JdbcTemplate jdbcTemplate = getJdbcTemplate(base, env);
jdbcTemplate.execute(new ConnectionCallback<Object>() {
@Override
public Object doInConnection(Connection con) throws SQLException, DataAccessException {
PreparedStatement ps = null;
int affectedRows = 0;
try{
String sqlStr = sqlTranslator.translate(sql);
logger.debug("[FIX-DEBUG] : " + sqlStr);
ps = con.prepareStatement(sqlStr);
Iterator<ClientGlobalActivity> it = clientSet.iterator();
int runningQueries = 0;
int totalQueries = clientSet.size();
Set<ClientGlobalActivity> chunkClient = new HashSet<>();
while(it.hasNext()){
ClientGlobalActivity c = it.next();
runningQueries++;
ps.setString(1, c.getIdClient());
logger.debug("[DEBUG-SQL] Preparing to add to batch: " + sqlStr + " | Param: xid_client = " + c.getIdClient());
ps.addBatch();
chunkClient.add(c);
if(runningQueries % app.getCommitSize() == 0 || runningQueries == totalQueries){
try{
int[] batchResults = ps.executeBatch();
for (int i = 0; i < batchResults.length; i++) {
logger.debug("[DEBUG-SQL] Batch result for query " + (i + 1) + ": " + batchResults[i]);
}
//affectedRows += IntStream.of(ps.executeBatch()).sum();
logger.debug("Execution batch - nombre de client traites : " + runningQueries + " - cumul de lignes impactees : " + affectedRows);
}
catch(Exception e){
// Erreur lors de l'execution de la requete pour un client => Erreur non bloquante
logger.debug("Erreur modification des données impossible pour la requête : [ " + nomRequete + " ] ERREUR / " + e);
// On stocke dans un nouvel objet le client, la base, la requete ainsi que l'exception
// Comme on insere en batch, on considère que tous les clients des chunk courant sont en erreur (pour ne pas passer à côté d'une erreur)
chunkClient.forEach(clientInChunk -> {
updateClientResults.add(new ErrorUpdateClient(
clientInChunk.getIdClient(),
base,
env,
sql,
e)
);}
);
}
finally {
chunkClient.clear();
}
}
}
}
catch (Exception e) {
JdbcUtils.closeStatement(ps);
ps = null;
DataSourceUtils.releaseConnection(con, jdbcTemplate.getDataSource());
con = null;
// Erreur de création de l'objet JdbcTemplate (acces à la base,...) => Erreur bloquante pour tout le set de client
logger.debug("Initialisation de la connexion impossible pour la base : [ " + base.name()
+ " ] de l'env [" + env.name() + "] ERREUR / " + e);
throw e;
} finally {
ps.clearBatch();
JdbcUtils.closeStatement(ps);
ps = null;
DataSourceUtils.releaseConnection(con, jdbcTemplate.getDataSource());
con = null;
}
return null;
}
});
}
catch (Exception e) {
// Erreur de création de l'objet JdbcTemplate (acces à la base,...) => Erreur bloquante pour tout le set de client
logger.debug("Initialisation de la connexion impossible pour la base : [ " + base.name()
+ " ] de l'env [" + env.name() + "] ERREUR / " + e);
throw e;
} finally {
LocalDateTime fin = LocalDateTime.now();
logger.debug("FIN - DAO - exécution requête : [ " + nomRequete + " ] en [ "
+ ChronoUnit.SECONDS.between(debut, fin) +
" ] secondes, pour [ " + clientSet.size() + " ] clients. Sur l'environnement [ " + env + " ] et sur la BDD [ "
+ base + " ].");
}
return updateClientResults;
}
And this my class with different methods to update some tables on a SQL server database
@Repository
public class CampaignCrmJdbcRepository extends JdbcGenericDao {
private final AppProperties app;
public CampaignCrmJdbcRepository(AppProperties app) {
this.app = app;
}
public List<ErrorUpdateClient> deleteOptinsInMarketingCampaigns(Set<ClientGlobalActivity> clientSet) {
String sqlDataOptin = "UPDATE dbo.DATA_OPTIN_CRM " +
"SET TELEPHONE_FIXE = 0, " +
"MOBILE = 0, " +
"EMAIL = 0, " +
"COURRIER = 0, " +
"TELEPHONE_FIXE_PARTENAIRE = 0, " +
"MOBILE_PARTENAIRE = 0, " +
"EMAIL_PARTENAIRE = 0, " +
"COURRIER_PARTENAIRE = 0, " +
"CANAL_FAVORI = '', " +
"MODIFIED_DT = GETUTCDATE() " +
"FROM dbo.DATA_OPTIN_CRM doc " +
"INNER JOIN dbo.USERS_CONTACTS_CRM uc ON doc.USERID = uc.ID " +
"WHERE uc.ID_CLIENT = ?";
String sqlUsersContats = "UPDATE dbo.USERS_CONTACTS_CRM " +
"SET OFFRE_INSTORE_UNIQUEMENT = 0, " +
"MODIFIED_DT = GETUTCDATE() " +
"WHERE ID_CLIENT = ?";
List<ErrorUpdateClient> errorUpdateClients = new ArrayList<>();
errorUpdateClients.addAll(queryFactoryUpdate(clientSet, Base.CAMPAIGN, app.getEnvironnement(), sqlDataOptin, "delete optins DATA_OPTIN_CRM"));
errorUpdateClients.addAll(queryFactoryUpdate(clientSet, Base.CAMPAIGN, app.getEnvironnement(), sqlUsersContats, "delete optins USERS_CONTACTS_CRM"));
return errorUpdateClients;
}
public List<ErrorUpdateClient> anonymizationClientCoordonneesFromCampaign(Set<ClientGlobalActivity> clientGlobalActivities) {
String sql = "UPDATE dbo.USERS_CONTACTS_CRM " +
"SET MAIL = NULL, " +
"MOBILE_COMPLET = NULL " +
"where ID_CLIENT = ? ";
return queryFactoryUpdate(clientGlobalActivities, Base.CAMPAIGN, app.getEnvironnement(), sql,"anonymizationClientCoordonneesFromCampaign");
}
public List<ErrorUpdateClient> anonymizationClientIdentityFromCampaign(Set<ClientGlobalActivity> clientGlobalActivities) {
String sql = "UPDATE dbo.USERS_CONTACTS_CRM " +
"SET NAME = '" + AnonymizationRules.NAME_REPLACE_VALUE + "', " +
"PRENOM = '" + AnonymizationRules.FIRSTNAME_REPLACE_VALUE + "' " +
"where ID_CLIENT = ? ";
return queryFactoryUpdate(clientGlobalActivities, Base.CAMPAIGN, app.getEnvironnement(), sql, "anonymizationClientIdentityFromCampaign");
}
}
All the methods here update the client set I give them. All except deleteOptinsInMarketingCampaigns() ! The set passed to this method is more or less updated. Depending on the mood of the application, I suppose... In fact, you only need to check the client set passed as a parameter in the database to see that only part of the set has been updated using the MODIFIED_DT column.
But when I look at the logs, I can see that every update line has passed and no exception has been raised.
The logs are visible here --> /?d3dc662d98214a77#89iNypF4755Y4woLPLCWjfX3R2RaHHSZTX9LiuJMKQbe because otherwise I ran out of space on this post
And for those wondering, the second SQL query in the deleteOptinsInMarketingCampaigns() method, which should update the same set of clients in another table, gives me the same problem and doesn't update the whole set, but not necessarily the same clients as before.
And I'd rather say it again, but this batch backup method is used everywhere else in my program for many other tables in Oracle and SQL server databases and everything works fine. Even more bizarrely, I update other tables in the same database using the same queryFactoryBatchUpdate() method with no problem.
I've don't know how to debug the problem. If some kind soul wants to help me, I'm ready to hear it.