CSV Import fails with "user_status" cannot be null

[maschoff]

Wow! I will check the JSON file if it is really clean. It may contain some artifacts too.

[pquotto]

I still didnt manage to import multiple CSVs on any version past 22.10.
This is error logs from 24.10.000.167.

I've activated debug logging in mysql, but its not very clear to me how it shows me the error:

Code: Select all

SET GLOBAL log_warnings = 2;
SET GLOBAL general_log = ON;
SET GLOBAL log_output = 'TABLE';

Code: Select all

SELECT event_time, argument FROM mysql.general_log WHERE argument LIKE 'INSERT INTO customer_1_binding_tbl%' ORDER BY event_time DESC \G;
*************************** 1. row ***************************
event_time: 2025-12-17 17:09:12.827769
  argument: 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', 1, 'CSV File Upload', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, 3, 0 FROM tmp_imp1_19 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 3 = bind.mailinglist_id AND 0 = bind.mediatype))
*************************** 2. row ***************************
event_time: 2025-12-17 17:09:12.773758
  argument: 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', 1, 'CSV File Upload', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, 3, 0 FROM customer_1_tbl cust WHERE datasource_id = 19 AND NOT EXISTS (SELECT 1 FROM customer_1_binding_tbl bind WHERE cust.customer_id = bind.customer_id AND 3 = bind.mailinglist_id AND 0 = bind.mediatype))
*************************** 3. row ***************************

Could the migration process have a little bug in any version after 22.10 and if so, how would I identify it? The SCHEMA of the table looks identical for the "user_status" column, so what else could it be?

[maschoff]

This log does not help. Please post the stacktrace from Tomcat's log catalina.out.

[pquotto]

It should be the one from the first page.

This is the latest:

Code: Select all

[ WARN] (WorkerExecutorService-emm-thread-#2) Error: 1048-23000: Column 'user_status' cannot be null
2025-12-17 17:09:12,828: ERROR [WorkerExecutorService-emm-thread-#2] 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_19 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=5232) 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_19 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_19 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=5232) 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=5232) 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-12-17 17:09:13,214: ERROR [WorkerExecutorService-emm-thread-#2] 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_19 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=5232) 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_19 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=5232) 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=5232) 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

[maschoff]

Root cause is "Column 'user_status' cannot be null". Please share the settings of your import profile.

[pquotto]

Here is the test import profile.

Code: Select all

Import profile: "Test" (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: None
	E-mail address(es) for errors: None
	Gender settings: NONE
	Mapping: 
		email = Email
		firstname = Firstname
		lastname = Surname
		storecode = StoreCode
		store_name = StoreName
		coupon_code = GutscheinCode
		unsubscribelink = UnsubscribeLink
	Key column: email

This exact profile works on the 25.04 OVA version (I havent tried the other versions which I have in .ova yet).

I think either
a) I messed up the migration from 22.10 to beyond (3 times)
b) migration of 22.10 to beyond is borked (can this be? Theres only a very little chance this can be true.. else you would have the same error I think)
c) ? nothing else comes to mind

Thanks

[maschoff]

The import profile looks OK. Does the mapping contain all fields which are present in the import file?

Which exact version of OpenEMM do you use?

[pquotto]

Does the mapping contain all fields which are present in the import file?

Yes.
https://imgur.com/a/pgQ9bXU

The logs are from this system:

Code: Select all

=================================================
= OpenEMM Maintenance Tool (OMT) v24.10.000.036 =
=================================================
Hostname: -
Free diskspace: 89.1 % (of 49.98 GiB)
OpenEMM License: OpenEMM (ID: 0)
OpenEMM Runtime Version: 24.10.000.036
OpenEMM Version: 24.10.000.167
OpenEMM Manual Version: 24.10.001
System-Url: http://192.168.23.234:8080

Current menu: Show OpenEMM status

OpenEMM status:
Operating System (OS): Alma 8
Python version: 3.8.17
Optional system package 'galera' for MariaDB clusters is not installed
DBCFG: local OK
Postfix Version: 3.5.8
Database client version: /usr/bin/mariadb  Ver 15.1 Distrib 10.6.21-MariaDB, for Linux (x86_64) using readline 5.1
Database Connection: OK
System-Time: 2025-12-18 13:50:24
System-Time-DB: 2025-12-18 13:50:24
Database version: MariaDB 10.6.21-MariaDB
OpenEMM database structure exists (Version 24.07.535)
Database table emm_db_errorlog_tbl: OK
BirtURL configvalue: OK
Mailaddresses: ERROR (feature_support not set)
Jobqueue status: 15 errorneous jobs (BirtReports, LoginTrackTableCleaner, CalendarCommentMailingService, ...)
DKIM keys available for domains: None
Java version: 17.0.14 (Oracle)
Tomcat version: 10.1.39.0
Wkhtml version: wkhtmltopdf 0.12.6 (with patched qt)
OpenEMM Application is running (Tomcat ProcessID: 143675, started at: Dec17 4:17)
OpenEMM Backend ok: once
OpenEMM Backend running: mailout, update, trigger, generate, pickdist, slrtscn, direct-path, bav-update, bavd, bav
Frontend application integrity: OK
Statistics application integrity: OK
Webservices application integrity: OK

This system has been upgrades from 20.04.x all the way to here now. And the CSV Import worked until 22.10.x.

What I can do is, to do more upgrades from 22.10 - to make sure I did everything correct.

I documented all my steps and did the upgrade process 3 times (on two different systems, meaning different hosts. And the data set was different. 1 system (the logs are from the testing instance) does not contain actual customer data, but only testing data))

Thanks

[maschoff]

Let's check the schema of your customer_1_binding_tbl:

Code: Select all

# mysql -u root -p
> use openemm
> DESC customer_1_binding_tbl;

What do you get? (edited)