Audit Profile Data Store and Forward errors: com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated

Ignition
1

I’m trying to work out why I’m getting truncation errors in some audit log inserts as reported by S+F, but the exported quarantine XML of the errors is all encrypted. How can I find out exactly what queries are failing so that I can have a chance at fixing it?

It’s not particularly sensitive, so here’s an example:

<?xml version="1.0"?>
<cachedata sourceStore="SQLServer" creationTime="Mon Mar 21 09:05:51 ACDT 2022">
   <data flavor="__datasourcedata__" subtype="">
      <base64>H4sIAAAAAAAAAJVTTW7TQBSethQ1LULQHRISJiDRotSO04qIFKlyY7c1dew0niRUVRUNydSdyvGk
9nN+NqAegAUnYMuCa3AGxCHgDIydhEQVG2TZeu+b931v5nueb7/QchSivTbvyizoxG1gfUpi4F0C
jAcy8wKWBh4BOiAjmcQdBrKWfKshv2A+1QmQly/ws9+rTz4toiULraU1NdrmYQfQG0toK//QVqba
ykRbSXmKNmPvCjEWRDSEk5iGI0Dr1hXpE8Ungae4ELLAEyWZHglpABb3rtEHtDjsiQNp/3kgnV6Q
2Ie53k9/fv2S+Vy8WUQZE63zkHksEBKBV+YB0CGYaDUCAnFU5h1qobukncimO7DQvXGGSehRmGBr
Y6xB/JhOoGUB8XCSZNLkiEdTwsO5pu4oAtqdVgLrUtG82wP0YOxIDMxXxCTo7rCHELojXgRo+Xps
2w/Tdo0alkwbO1KHQNlnwrB6RDzqijNE0oYwL2FjIZyT6sJxmyTRuNCsTqNkd0G60hTm8kGVwGVO
EmQ86tFNqaFZdcOVNs6atCMd0PdSYVtSC6V8sbRTlLSyjqVCvlA4z531GR3wwB+JUM3LO69ltSir
6o5Im8eurhaKJ/ltkVQICyadIsXp0zDhCVxLp3q+CWhBBbQyVQO0OqMDKkWjqFStOfqWORn6llZv
7ltuXn21dVBzbGzYeknphfyKtqGEm0ZrP/Sb0aUViD/ofuKrnPgqJ85cXt0sHJ/CoyWEhr3BCkIL
H5+XlLdDQN/nzdXquolbRsOwsfAhmwYtbFYMF2uVajYnZZ2aeWjaGjbtw5Z76mKjchstJzt7hxNY
K2On9jdoHTnuFDYdexa1sFY7NOaWWukkklz0xXVXaOpGdjagvdytR1j5eHITxHWOeBy26fwl/wM8
cKRIKAQAAA==</base64>

Converting from base64 to ascii resulted in garbarge so I assume it’s got some other measures applied as well

2

That quarantine data unfortunately is encoded so Ignition is the only thing that can meaningfully deserialize it, and there is no easy way to get it into human readable format. Following the steps Paul outlines here may be your best bet to see what that data is.

Audit profile insert data and a few other operations (notably system.db.runSF functions) do not have their own exporters/importers yet for quarantined items, which is why you are seeing that data in base64 form. At this point, those are slated to be worked on in 8.2.

However, if the error is always that string or binary data would be truncated message, then that piece at least should be a relatively easy fix. You should just need to update the problem ‘string’ columns in your AUDIT_EVENTS table to use larger data types, something like varchar(1000) or varchar(MAX). For the future, to prevent this from happening with new tables, you would need to update your Translator settings for your MSSQL translator, and set the String Data Type Mapping to a similar varchar(MAX) value.

2 Likes
3

One amendment: Kindling has some ability to load S+F cache data; ideally by decoding it directly from Ignition’s packed format, but if that fails it can decode the raw Java serialization payload into its representative parts.

1 Like