java.sql.SQLException: Incorrect string value:

[hmbrannen]

This is for our Mirth Connect instance. We are consistently getting this error with many different EMRs. This is on tcp listeners. It is because they are sending a non-standard char. These are "weird" versions of dashes, apostrephes, or even spaces. At first, we thought it was the senders problem, but with the frequency of this issue, I am thinking that this is due to our system not being able to handle enough. It's a massive headache to resolve these as we have to identify the wrong char(by having our client send us the raw message), let our client know, and then they have to edit it. All while our interface with them is halted. Any suggestions on how to get past this where the database can handle it?

This is the base of the error that we are seeing. Also note that we can only see this error in our logs. We can't see the message itself. It doesn't even show up in our channel. Any suggestions on how you've solved this issue would be greatly appreciated. Note that our MySQL db is character set latin1 and collation is latin1_swedish_ci.

at com.mirth.connect.donkey.server.data.jdbc.JdbcDao.insertContent(JdbcDao.java:296)
at com.mirth.connect.donkey.server.data.jdbc.JdbcDao.insertMessageContent(JdbcDao.java:208)
at com.mirth.connect.donkey.server.data.buffered.BufferedDao.executeTasks(BufferedDao.java:119)
at com.mirth.connect.donkey.server.data.buffered.BufferedDao.commit(BufferedDao.java:93)
at com.mirth.connect.donkey.server.data.buffered.BufferedDao.commit(BufferedDao.java:80)
at com.mirth.connect.donkey.server.channel.Channel.dispatchRawMessage(Channel.java:1295)
... 8 moreCaused by: java.sql.SQLException: Incorrect string value: '\xEF\xBF\xBD ED...' for column 'CONTENT' at row 1

[pacmano1]

Example message? What is the encoding on the inbound TCP listener? You omitted that. That seems like UTF-8 which is I think is the default on every engine I have worked on - but I also defer to international users here.

[hmbrannen]

@pacmano1 The encoding of the TCP listner is Default, which in this case is UTF-8.

I don't have an example message, but one of the chars that we have identified that breaks it is C2 A0 - Non-Breaking Space

[pacmano1]

I'll duck here, not a mysql user (postgres and en_US.UTF-8). Hopefully someone else will chime one.

[kirbykn2]

From Chat GPT https://chatgpt.com/g/g-XG1Dzv32o-interop-guru I have no idea if this is correct, but I hope it helps. The issue you’re encountering with non-standard characters likely stems from the character encoding limitations of your MySQL database setup ( latin1_swedish_ci). Here’s how to address this effectively in Mirth Connect and your database: 1. *Database Character Encoding*: Switch your MySQL database character set and collation to utf8mb4 (character set) and utf8mb4_unicode_ci (collation). This configuration is more robust for handling extended Unicode characters, which are often the source of these encoding errors. - You can update your database’s character set with the following SQL commands: sql Copy code ALTER DATABASE your_database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; - Make sure to adjust each table as needed. 2. *Mirth Connect Channel Modification*: - Within Mirth Connect, ensure that your channel correctly handles character encoding by setting the source and destination connectors to use UTF-8. - In the source connector (TCP Listener), you might add a transformation step to sanitize or replace problematic characters. JavaScript code in a transformer could help: javascript Copy code var sanitizedMessage = msg.replace(/[\u0096\u0097\u2019\u2013]/g, ''); // Example: replace problematic dashes, apostrophes, etc.return sanitizedMessage; 3. *Error Handling*: - Implement error handling in your channel scripts to detect and log such encoding issues directly. Logging can help in isolating and resolving specific character issues without full database errors. 4. *Considerations for Future Character Input*: - If clients regularly send messages with problematic characters, setting clear guidelines on acceptable characters or sanitizing them programmatically within Mirth Connect is a proactive approach. These steps should mitigate or resolve the non-standard character handling issues and reduce the need to halt the interface.

…

On Mon, Nov 11, 2024 at 2:14 PM pacmano1 ***@***.***> wrote: I'll duck here, not a mysql user (postgres and en_US.UTF-8). Hopefully someone else will chime one. — Reply to this email directly, view it on GitHub <#6347 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/APRXWD23QM5XDAY5QC5PKY32AD6X7AVCNFSM6AAAAABRSBUQTSVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTCMRRHAYTGNI> . You are receiving this because you are subscribed to this thread.Message ID: <nextgenhealthcare/connect/repo-discussions/6347/comments/11218135@ github.com>

-- Best, Kirby Knight | 231.735.4650 | ***@***.***