CSV Import fails with "user_status" cannot be null
Posted: Thu Apr 24, 2025 12:58 pm
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:
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)
We saw that when the subquery gives multiple results, the INSERT INTO fails.
Maybe if 2 or more following inserts happen, the import crashes?
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:
Where "foo@example.com" is a update and "bar@example.com" is a insert. This works.
With this csv, it fails:
Anyone of you guys have any idea?
Thanks!
- Pascal
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 nullMy 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
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
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
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
Thanks!
- Pascal