Message processing works only on 2nd attempt

[tom08zehn]

Running on Mirth 3.5.2
I have a File Reader
Receiving a new CSV file every day

In the preprocessor I'm validating the CSV file. I simply read the first line and split it using the semi-colon (;) as delimiter.
As a 1st check, I'm counting the number of columns.
As a 2nd check, I'm checking the names of the columns (must match with an array).

Every day the message processing fails (my validation in the preprocessor script fails).
When I reprocess it manually without any modifications then it works.

Any idea why?

The only strange thing I noticed: for some reason the provided CSV file starts with a question mark (so it's the first character of the first header column).
But still, why does reprocessing work like a charm if neither the inbound message nor the script is different?

Here's the script:

// Modify the message variable below to pre process data

// new line character
const NEWLINE = '\n';
// field delimiter
const DELIM = ';';

// expected columns and headers
// in case you wonder: for some reason the provided CSV file starts with a question mark
var COLS = '?foo;bar;spam;eggs'.split(DELIM);

try {
	// get 1st line = header
	var header = message.toString().split(NEWLINE)[0];
	// get columns
	var fields = header.split(DELIM);
	
	// validate number of columns
	if(fields.length != COLS.length) {
		throw new Error('Number of CSV file columns: ' + fields.length.toString() + ' (expected: ' + COLS.length.toString() + ')');
	}
	
	// validate fields names in header line
	fields.forEach(function(item, i) {
		logger.debug("i: " + i + " / COLS[i]: " + COLS[i].trim().toLowerCase() + " / item: " + item.trim().toLowerCase());
		if(COLS[i].trim().toLowerCase() != item.trim().toLowerCase()) throw new Error('Invalid CSV file header (field names).');
	});
}
catch(e) {
	throw new Error('Error validating CSV file: ' + e.message);
}

return message;

[jonbartels]

Can you post a sample valid CSV please?

When your validation fails the first time, which error is thrown and what is the debugging output?

for some reason the provided CSV file starts with a question mark

Are you sure its a question mark and not a non-printable character? Open the raw file in a text editor or hex editor and make sure.

[tom08zehn]

Sorry, it took a while to reduce channel and CSV file... cannot share the original CSV because it contains PII data.

Note: My question is not about finding another solution or workaround (see below, I can fix it myself). I'd like to understand why the 1st message processing fails while the 2nd (manual) attempt works fine while nothing has changed. Also I'd like to know where this strange question mark comes from (1st character in header row of inbound message, see 1st screenshot below).

CSV file:
test.csv

Channel causing the error:
test_csv_validation1-error.zip

Errors:

Preprocessor Script error
ERROR MESSAGE: Error running preprocessor scripts
com.mirth.connect.server.MirthJavascriptTransformerException: 
SOURCE CODE:	
1643: 		logger.debug("i: " + i + " / COLS[i]: " + COLS[i].trim().toLowerCase() + " / item: " + item.trim().toLowerCase());
1644: 		if(COLS[i].trim().toLowerCase() != item.trim().toLowerCase()) throw new Error('Invalid CSV file header (field names).');
1645: 	});
1646: }
1647: catch(e) {
1648: 	throw new Error('Error validating CSV file: ' + e.message);
1649: }
1650: 
1651: return message; 
1652: }
LINE NUMBER:	1648
DETAILS:	Error: Error validating CSV file: Invalid CSV file header (field names).
	at ef067568-3b78-458d-837e-9d125d26f393_Preprocessor:1648 (doScript)
	at ef067568-3b78-458d-837e-9d125d26f393_Preprocessor:1653
	at com.mirth.connect.server.util.javascript.JavaScriptUtil.executeScript(JavaScriptUtil.java:547)
	at com.mirth.connect.server.util.javascript.JavaScriptUtil.executePreprocessorScripts(JavaScriptUtil.java:216)
	at com.mirth.connect.server.transformers.JavaScriptPreprocessor$JavaScriptPreProcessorTask.doCall(JavaScriptPreprocessor.java:103)
	at com.mirth.connect.server.util.javascript.JavaScriptTask.call(JavaScriptTask.java:113)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)

Screenshots:

Same channel but slightly modified that succeeds:
test_csv_validation2-works.zip

I just changed the logic from string to array:

[jonbartels]

Here is your sample CSV in a hex editor:

You're seeing the ? because MC can't print those characters (or they're non-printable in the first place). The hex codes are efbb bf, that is a UTF-8 Byte Order Mark (BOM).

I'm heading into morning meetings but I'll try to load your test channel and see how it can be fixed. I think the message works on the second try because either MC or the backing database are stripping out the non-ASCII characters on the reprocess attempt, but I am not sure. One of the first things to check is the file encoding in the file reader.

There might be some workarounds or an explanation of the reprocessing behaviors on old forum threads .

[tom08zehn]

Ah, thanks, now I guess I understood what you mean: 1st processing is tried on-the-fly from memory before (or in parallel of) storing the message to the DB. 2nd attempt is reading the message from the DB and then processes it.

I'm using MySQL.

Well, still, Mirth could be designed in the way that it always first stores and then reads from DB... because it has to store the message anyway. But from a developer's perspective I also see why you wouldn't like do it... too much I/O would result in poor performance.

[jonbartels]

I see where you're coming from @tom08zehn . If MC got a UTF-8 BOM on the way in, then you reprocess, that UTF-8 BOM should still be there on reprocessing. Preserving that raw message is crucial for accurate testing.

The approximate workflow is:

1. MC gets message with UTF-8 BOM
2. MC errors
3. MC writes raw, transformed, and encoded messages to database
4. DB charset and encoding rules persist the message, potentially stripping characters
5. User tells MC to reprocess message
6. MC reads message from DB and reprocesses it, this DB read could strip characters either from the DB or based on how MC handles the result set when it loads the message
7. MC handles the message because the BOM characters got stripped

[pacmano1]

Fair enough.

[tonygermano]

I don't think the BOM is actually crucial, as it is not technically part of the data. If you are reading a file in binary mode, then the BOM will certainly be retained, but it would be a nice feature if mirth stripped it for you in text mode, because once the data has been converted to a string rather than a bytestream, the BOM should no longer be there.

[tom08zehn]

Exactly. I'm reading the CSV file in text mode so my expectation would be that Mirth strips the BOM before processing the data.

Worth a feature request?

BTW: For now, my solution is to replace the BOM in the preprocessor:

message = message.replace(/^/g, '');

[pacmano1]

This discussion was answered in the thread of messages, however no particular item was tagged as the exact answer by the orginal poster.