Tag history string length

Using version 8.1.2
I started with internal tag history which worked great with long JSON strings.
I added a push to remote server using MariaDB connection with regular tag historian db.
I updated the Table definition as varchar(10000) but still getting error message.
java.sql.BatchUpdateException: Data truncation: Data too long for column ‘stringvalue’ at row 1

I ran into this same issue with a MySQL database, and figured out a work around. It's not super clean, but seems to be working. Dragging this thread back from the dead to give a solution for anyone else who comes across it.

If I change the stringValue column datatype from VARCHAR(255) to VARCHAR(1000) the records are able to be stored (both saving new entries and Store and Forward 'Retry' in the web interface). It's interesting that didn't work for you. I'm using 8.1.19, so maybe there is a change there. I was getting the record too long error in the StoreAndForward status page on the Gateway web interface.

While updating the column datatype worked, when a new partition table is created the problem comes back up. Tech Support suggested making a new database Driver/Translator and setting the default string type to varchar(1000). I tried this but got a bunch of errors immediately. I didn't dig too deep, since I had already figured out another work around.

It feels really 'hacky' but seems to be working well. There is also a lot of room left to clean it up/make it more robust, but it met our immediate need.

I setup a Gateway tag change event on the historian StoreAndForward Quarantined tag ([System]Gateway/StoreAndForward/historianName/Quarantined). If there are records quarantined, it checks the MySQL data types, updates any as needed, and then triggers a StoreAndForward retry. Currently lots of hard coded values, but it should be pretty straightforward to make it more generalized.

"""
Check the historian database table data types, the default stringvalue type is too short.
Increase the DB field size and then attempt to store the data again

Called by a Gateway tag change event on the StoreAndForward Quarantined tag.
Must be run in Gateway scope.
"""

#import Java classes, needed for the transfer retry at the end
from com.inductiveautomation.ignition.gateway import IgnitionGateway
from java.lang import Long 

#TODO this is not robust, the database name is hardcoded and both Ignition DB name and MySQL schema must be entered.
#set Ignition database name for the historian, and the associated MySQL schema name
historianDBName = "HistorianDBName"
historianSchema = "historianSchemaName"

#set new stringValue varchar length
newLength = 1000

logger = system.util.getLogger("HistotianDBUpdate")

#check if there are quarantined records.
#assumes these are because of column datatype errors, needs error handling incase this doesn't fix it.
if newValue.getValue() > 0:
	
	logger.info("{} has {} quaratined records. Attempting to fix.".format(historianDBName, newValue.getValue()))
	
	#get partition table names
	query = "SELECT pname FROM sqlth_partitions;"
	tableNames = system.db.runPrepQuery(query, [], historianDBName)
	
	if len(tableNames) > 0:
		#check the datatype of the stringvalue column in each table
		for table in tableNames:
			#get stringValue datatype length
			query = "SELECT CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.columns where COLUMN_NAME = 'stringvalue' and table_schema = ? and table_name = ?;"
			varLength = system.db.runPrepQuery(query, [historianSchema, table[0]], historianDBName)
			
			#update datatype length if needed
			if varLength[0][0] < newLength:
				logger.info("Updating table {} in {}".format(table[0]), historianDBName)
				query = "ALTER TABLE " + table[0] + " MODIFY COLUMN `stringvalue` VARCHAR(?);"
				system.db.runPrepUpdate(query, [newLength], historianDBName, skipAudit = True)
		
		#trigger store and forward retry after datatypes have been updated
		logger.info("Triggering StoreAndForward retry")
		gateway = IgnitionGateway.get()	
		histMngr = gateway.getHistoryManager()
			
		stores = histMngr.getStores(None)	 #none = getAll
		for store in stores:
			qdataInfo = histMngr.getQuarantinedDataInfo(store)
			ids = [Long(x.getId()) for x in qdataInfo]		
			if ids:
				histMngr.unquarantineData(store,ids)

Big gaps are there is no error checking that updating the datatype fixed the problem (should check the quarantine tag value after running and make sure it's zero) and if there is more than 1 quarantined value there is a good chance that this script will trigger it's self during the StoreAndForward retry.