CSV Import fails with "user_status" cannot be null

Use this forum for questions regarding installation, upgrading and configuration of OpenEMM

Moderator: moderator

Post Reply
pquotto
Posts: 1
Joined: Wed Apr 23, 2025 4:38 pm

CSV Import fails with "user_status" cannot be null

Post by pquotto »

Hello all,

on a test system we've upgraded from 20.04.x to the now latest 24.10.x.
Upon finalizing the test system, we've tried the CSV Import feature, which is heavily in use, which worked good enough on 20.04.x.

On some import state, we receive the following error:

Code: Select all

2025-04-23 19:36:59,511: ERROR [WorkerExecutorService-emm-thread-#6] org.agnitas.dao.impl.ImportRecipientsDaoImpl - Error: PreparedStatementCallback; SQL [INSERT INTO customer_1_binding_tbl (customer_id, user_type, user_status, user_remark, timestamp, creation_date, exit_mailing_id, mailinglist_id, mediatype) (SELECT DISTINCT customer_id, 'W', ?, 'CSV File Upload', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, ?, ? FROM tmp_imp1_42 temp WHERE (customer_id != 0 AND customer_id IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM customer_1_binding_tbl bind WHERE temp.customer_id = bind.customer_id AND ? = bind.mailinglist_id AND ? = bind.mediatype))]; (conn=220) Column 'user_status' cannot be null
SQL: INSERT INTO customer_1_binding_tbl (customer_id, user_type, user_status, user_remark, timestamp, creation_date, exit_mailing_id, mailinglist_id, mediatype) (SELECT DISTINCT customer_id, 'W', ?, 'CSV File Upload', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, ?, ? FROM tmp_imp1_42 temp WHERE (customer_id != 0 AND customer_id IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM customer_1_binding_tbl bind WHERE temp.customer_id = bind.customer_id AND ? = bind.mailinglist_id AND ? = bind.mediatype))
Parameter: 1, 3, 0, 3, 0
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO customer_1_binding_tbl (customer_id, user_type, user_status, user_remark, timestamp, creation_date, exit_mailing_id, mailinglist_id, mediatype) (SELECT DISTINCT customer_id, 'W', ?, 'CSV File Upload', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, ?, ? FROM tmp_imp1_42 temp WHERE (customer_id != 0 AND customer_id IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM customer_1_binding_tbl bind WHERE temp.customer_id = bind.customer_id AND ? = bind.mailinglist_id AND ? = bind.mediatype))]; (conn=220) Column 'user_status' cannot be null
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:97) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1548) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:677) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:970) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1014) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1024) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.agnitas.dao.impl.BaseDaoImpl.update(BaseDaoImpl.java:472) ~[classes/:?]
	at org.agnitas.dao.impl.RetryUpdateBaseDaoImpl.retryableUpdate(RetryUpdateBaseDaoImpl.java:44) ~[classes/:?]
	at org.agnitas.dao.impl.ImportRecipientsDaoImpl.assignExistingCustomerWithoutBindingToMailingList(ImportRecipientsDaoImpl.java:454) ~[classes/:?]
	at org.agnitas.util.importvalues.ImportModeAddAndUpdateHandler.handlePostProcessing(ImportModeAddAndUpdateHandler.java:207) ~[classes/:?]
	at org.agnitas.service.ProfileImportWorker.importData(ProfileImportWorker.java:750) ~[classes/:?]
	at org.agnitas.service.ProfileImportWorker.call(ProfileImportWorker.java:458) ~[classes/:?]
	at com.agnitas.emm.core.imports.web.RecipientImportController.lambda$execute$0(RecipientImportController.java:318) ~[classes/:?]
	at com.agnitas.service.impl.PollingServiceImpl$CallablePollable.call(PollingServiceImpl.java:90) [classes/:?]
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) [?:?]
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
	at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304) [?:?]
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
	at java.base/java.lang.Thread.run(Thread.java:840) [?:?]
Caused by: java.sql.SQLIntegrityConstraintViolationException: (conn=220) Column 'user_status' cannot be null


My testing workflow has been like this:

- Create CSV with 2 entries. 1 new entry, 1 update entry. Import works.
- Add 1 new entry. Get 2 updates, 1 new entry. Import works.
- Use full CSV, which works on 20.04 (we still have this system, as its used in production as of right now): import breaks with the above stacktrace.
- Find a way where this CSV broke, by halving it everytime and retrying an import: breaks.
- Start new CSV. Use the 3 entries from before (all updates). Use 1 single entry from the CSV (which works on 20.04): Import works.
- Add 2 or more new entries, and it fails to import.

Debugging the query (example)

Code: Select all

INSERT INTO customer_1_binding_tbl (customer_id, user_type, user_status, user_remark, timestamp, creation_date, exit_mailing_id, mailinglist_id, mediatype) (
        SELECT customer_id, 'W', ?, 'CSV File Upload', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, ?, ? FROM customer_1_tbl cust WHERE datasource_id = ? AND NOT EXISTS (
                SELECT 1 FROM customer_1_binding_tbl bind WHERE cust.customer_id = bind.customer_id AND ? = bind.mailinglist_id AND ? = bind.mediatype))

Parameter: 1, 3, 0, 683, 3, 0
We saw that when the subquery gives multiple results, the INSERT INTO fails.
Maybe if 2 or more following inserts happen, the import crashes?

Code: Select all

+-------------+---+---+-----------------+---------------------+---------------------+---+---+---+
| customer_id | W | 1 | CSV File Upload | CURRENT_TIMESTAMP   | CURRENT_TIMESTAMP   | 0 | 3 | 0 |
+-------------+---+---+-----------------+---------------------+---------------------+---+---+---+
|     1224462 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224463 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224464 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224465 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224466 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224467 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224468 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224469 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224470 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224471 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224472 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224473 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224474 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224475 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224476 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224477 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224478 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224479 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224480 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224481 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224482 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224483 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224484 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224485 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224486 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224487 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224488 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224489 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224490 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224491 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224492 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224493 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224494 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224495 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224496 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224497 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224498 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224499 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224500 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224501 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224502 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224503 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224504 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224505 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224506 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224507 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224508 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224509 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224510 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224511 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224512 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224513 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224514 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224515 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
|     1224516 | W | 1 | CSV File Upload | 2025-04-23 16:07:26 | 2025-04-23 16:07:26 | 0 | 3 | 0 |
+-------------+---+---+-----------------+---------------------+---------------------+---+---+---+
55 rows in set (26.682 sec)

Code: Select all

2025-04-23 18:45:47,993: ERROR [WorkerExecutorService-emm-thread-#1] org.agnitas.dao.impl.ImportRecipientsDaoImpl - Error: PreparedStatementCallback; SQL [INSERT INTO customer_1_binding_tbl (customer_id, user_type, user_status, user_remark, timestamp, creation_date, exit_mailing_id, mailinglist_id, mediatype) (SELECT DISTINCT customer_id, 'W', ?, 'CSV File Upload', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, ?, ? FROM tmp_imp1_18 temp WHERE (customer_id != 0 AND customer_id IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM customer_1_binding_tbl bind WHERE temp.customer_id = bind.customer_id AND ? = bind.mailinglist_id AND ? = bind.mediatype))]; (conn=220) Column 'user_status' cannot be null
SQL: INSERT INTO customer_1_binding_tbl (customer_id, user_type, user_status, user_remark, timestamp, creation_date, exit_mailing_id, mailinglist_id, mediatype) (SELECT DISTINCT customer_id, 'W', ?, 'CSV File Upload', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, ?, ? FROM tmp_imp1_18 temp WHERE (customer_id != 0 AND customer_id IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM customer_1_binding_tbl bind WHERE temp.customer_id = bind.customer_id AND ? = bind.mailinglist_id AND ? = bind.mediatype))
Parameter: 1, 3, 0, 3, 0
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO customer_1_binding_tbl (customer_id, user_type, user_status, user_remark, timestamp, creation_date, exit_mailing_id, mailinglist_id, mediatype) (SELECT DISTINCT customer_id, 'W', ?, 'CSV File Upload', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, ?, ? FROM tmp_imp1_18 temp WHERE (customer_id != 0 AND customer_id IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM customer_1_binding_tbl bind WHERE temp.customer_id = bind.customer_id AND ? = bind.mailinglist_id AND ? = bind.mediatype))]; (conn=220) Column 'user_status' cannot be null
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:97) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1548) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:677) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:970) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1014) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1024) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.agnitas.dao.impl.BaseDaoImpl.update(BaseDaoImpl.java:472) ~[classes/:?]
	at org.agnitas.dao.impl.RetryUpdateBaseDaoImpl.retryableUpdate(RetryUpdateBaseDaoImpl.java:44) ~[classes/:?]
	at org.agnitas.dao.impl.ImportRecipientsDaoImpl.assignExistingCustomerWithoutBindingToMailingList(ImportRecipientsDaoImpl.java:454) ~[classes/:?]
	at org.agnitas.util.importvalues.ImportModeAddAndUpdateHandler.handlePostProcessing(ImportModeAddAndUpdateHandler.java:207) ~[classes/:?]
	at org.agnitas.service.ProfileImportWorker.importData(ProfileImportWorker.java:750) ~[classes/:?]
	at org.agnitas.service.ProfileImportWorker.call(ProfileImportWorker.java:458) ~[classes/:?]
	at com.agnitas.emm.core.imports.web.RecipientImportController.lambda$execute$0(RecipientImportController.java:318) ~[classes/:?]
	at com.agnitas.service.impl.PollingServiceImpl$CallablePollable.call(PollingServiceImpl.java:90) [classes/:?]
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) [?:?]
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
	at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304) [?:?]
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
	at java.base/java.lang.Thread.run(Thread.java:840) [?:?]
Caused by: java.sql.SQLIntegrityConstraintViolationException: (conn=220) Column 'user_status' cannot be null
	at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:290) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:370) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:137) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:840) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:779) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:698) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:641) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:95) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:334) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:311) ~[mariadb-java-client-3.0.8.jar:?]
	at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:137) ~[tomcat-dbcp.jar:10.1.39]
	at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:137) ~[tomcat-dbcp.jar:10.1.39]
	at org.springframework.jdbc.core.JdbcTemplate.lambda$update$2(JdbcTemplate.java:975) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	... 18 more
2025-04-23 18:45:48,172: ERROR [WorkerExecutorService-emm-thread-#1] org.agnitas.service.ProfileImportWorker - Error during profile importData: PreparedStatementCallback; SQL [INSERT INTO customer_1_binding_tbl (customer_id, user_type, user_status, user_remark, timestamp, creation_date, exit_mailing_id, mailinglist_id, mediatype) (SELECT DISTINCT customer_id, 'W', ?, 'CSV File Upload', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, ?, ? FROM tmp_imp1_18 temp WHERE (customer_id != 0 AND customer_id IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM customer_1_binding_tbl bind WHERE temp.customer_id = bind.customer_id AND ? = bind.mailinglist_id AND ? = bind.mediatype))]; (conn=220) Column 'user_status' cannot be null
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO customer_1_binding_tbl (customer_id, user_type, user_status, user_remark, timestamp, creation_date, exit_mailing_id, mailinglist_id, mediatype) (SELECT DISTINCT customer_id, 'W', ?, 'CSV File Upload', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, ?, ? FROM tmp_imp1_18 temp WHERE (customer_id != 0 AND customer_id IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM customer_1_binding_tbl bind WHERE temp.customer_id = bind.customer_id AND ? = bind.mailinglist_id AND ? = bind.mediatype))]; (conn=220) Column 'user_status' cannot be null
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:97) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1548) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:677) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:970) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1014) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1024) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.agnitas.dao.impl.BaseDaoImpl.update(BaseDaoImpl.java:472) ~[classes/:?]
	at org.agnitas.dao.impl.RetryUpdateBaseDaoImpl.retryableUpdate(RetryUpdateBaseDaoImpl.java:44) ~[classes/:?]
	at org.agnitas.dao.impl.ImportRecipientsDaoImpl.assignExistingCustomerWithoutBindingToMailingList(ImportRecipientsDaoImpl.java:454) ~[classes/:?]
	at org.agnitas.util.importvalues.ImportModeAddAndUpdateHandler.handlePostProcessing(ImportModeAddAndUpdateHandler.java:207) ~[classes/:?]
	at org.agnitas.service.ProfileImportWorker.importData(ProfileImportWorker.java:750) ~[classes/:?]
	at org.agnitas.service.ProfileImportWorker.call(ProfileImportWorker.java:458) ~[classes/:?]
	at com.agnitas.emm.core.imports.web.RecipientImportController.lambda$execute$0(RecipientImportController.java:318) ~[classes/:?]
	at com.agnitas.service.impl.PollingServiceImpl$CallablePollable.call(PollingServiceImpl.java:90) [classes/:?]
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) [?:?]
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
	at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304) [?:?]
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
	at java.base/java.lang.Thread.run(Thread.java:840) [?:?]
Caused by: java.sql.SQLIntegrityConstraintViolationException: (conn=220) Column 'user_status' cannot be null
	at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:290) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:370) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:137) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:840) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:779) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:698) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:641) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:95) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:334) ~[mariadb-java-client-3.0.8.jar:?]
	at org.mariadb.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:311) ~[mariadb-java-client-3.0.8.jar:?]
	at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:137) ~[tomcat-dbcp.jar:10.1.39]
	at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:137) ~[tomcat-dbcp.jar:10.1.39]
	at org.springframework.jdbc.core.JdbcTemplate.lambda$update$2(JdbcTemplate.java:975) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658) ~[spring-jdbc-6.1.14.migrated.jar:6.1.14-migrated-1.0.0]
	... 18 more
2025-04-23 18:45:48,214: ERROR [WorkerExecutorService-emm-thread-#1] com.agnitas.emm.core.JavaMailServiceImpl - Error sending email via localhost: Couldn't connect to host, port: localhost, 25; timeout -1 
emailSubject: Import-ERROR: Recipients import report:  "Testimport" (Client: EMM-Master) 
emailContent: Import-ERROR:
Recipients import report "Testimport":

License ID: 0
Client: "EMM-Master" (ID: 1)
Import destination: Recipients
Import profile: "Testimport" (ID: 2)
	Character set: UTF-8
	Headings in first row: true
	Zipped file: false
	Zip password: false
	Automatic mapping: false
	Separator: ,
	Text recognition character: NONE
	Decimal separator: .
	Date format: dd.MM.yyyy HH:mm
	HtmlTagCheck: no
	AllowSimpleHtmlTags: yes
	Mode: Add new recipients and update existing recipients
	Duplicate recipients: COMPLETE
	Empty values in import file: OVERWRITE
	Recipient mailing type (Default): HTML
	Media type: E-mail
	Datatype: CSV
	Update all duplicates (not only first found): true
	E-mail address(es) for reports: snip
	E-mail address(es) for errors: snip
	Gender settings: NONE
	Mapping: 
		email = Email
		firstname = Firstname
		lastname = Surname
		storecode = StoreCode
		store_name = StoreName
		birthday_coupon_code = GutscheinCode
		unsubscribelink = UnsubscribeLink
	Key column: email
Start time: 4/23/2025 18:45
End time: 4/23/2025 18:45
File name: nl-2025-04-23-page-3.csv

Fatal error: Internal db error
Errors in e-mail addresses: 0
Items with invalid null values: 0
Addresses on blocklist: 0
Duplicate records in the import data file: 0
Error in numeric values: 0
Error in mailing type values: 0
Error in gender field: 0
Errors in date fields: 0
Errors in line structure: 0
Encryption errors: 0
Value is too large for destination column: 0
Number is too large for destination column: 0
Invalid format: 0
Please enter the missing mandatory value.: 0
Error in actions: 0
File size: 123.72 KiByte
CSV lines: 586 (+1 Headings in first row)
Recipients from import data file already in database: 0
New recipients: 0
Updated recipients: 585
Blocklisted e-mail addresses: 0
Generated DATASOURCE_ID: 18
But when it only has 1 result, the INSERT INTO works. The INSERT INTO query also works, if the take a single result (by hand) from the SELECT subquery and use its value for the INSERT INTO.

We saw this issue in `assignExistingCustomerWithoutBindingToMailingList` and in `assignNewCustomerToMailingList` (although here is a slightly different query used, the take still stands).

We've tested this against Mariadb 10.6.21 and 10.11.11.

Looking at the code for the methods that create those queries, we can only see small changes and didnt see anything that should cause this.
Maybe we are too blind to see it.

Example csv:

Code: Select all

Email,Firstname,Surname,StoreCode,StoreName,GutscheinCode,UnsubscribeLink
foo@example.com,F,OO,test,test,AA-BB-CC,https://example.com
bar@example.com,F,BAR,test,test,DD-EE-FF,https://example.com
Where "foo@example.com" is a update and "bar@example.com" is a insert. This works.

With this csv, it fails:

Code: Select all

Email,Firstname,Surname,StoreCode,StoreName,GutscheinCode,UnsubscribeLink
foo@example.com,F,OO,test,test,AA-BB-CC,https://example.com
bar@example.com,F,BAR,test,test,DD-EE-FF,https://example.com
baz@example.com,F,BAZ,test,test,123-44-6F,https://example.com
foobar@example.com,F,BARB,test,test,113233-44-6F,https://example.com
Anyone of you guys have any idea?

Thanks!
- Pascal
Top
Post Reply

Return to “Installation & Upgrades”