For the past few weeks, I’ve been trying to clean-up and normalize my blog content using Markdown. As you can imagine, this is not an easy process; but, I thought it was going quite smoothly. That is, until last night when I noticed that one of my posts was being truncated at 64,000 characters:

64,000 characters is the default “Long Text Buffer” size in Adobe ColdFusion’s database drivers (at least the MySQL one). You can insert more than 64K characters under such settings; however, if you try to read that data out of the database, it gets truncated. And, since my data clean-up script was querying for data, cleaning it up, and then re-saving it, it becomes clear that the clean-up was taking place on arbitrarily truncated data (which was then be re-saved over the original data - poo!).

Luckily, I only had a few posts that were longer than 64,000 characters; and, I was able to locate some original copies. So, disaster averted. But, it got me thinking about what Lucee CFML is doing by default.

To test this, I used CommandBox to spin up a fresh Lucee CFML 5.3.5.92 server and a fresh Adobe ColdFusion 2018 (2018.0.09.318650) server. Then, I went into each administrative panel and I created a MySQL datasource with the default settings.

NOTE: _I added _[_allowMultiQueries=true_](https://www.bennadel.com/blog/1209-turning-on-multiple-statements-in-coldfusion-8-mysql-4-5-datasource.htm)_ to the connection string_ in order to run the compound SQL statements in my test.

Once I had the data-source in place, I create this ColdFusion script (compatible with both Adobe ColdFusion and Lucee CFML). The goal of the test is to insert two values: one smaller than 64,000 characters and one larger than 64,000 characters. And then, to see how data goes into and comes out of the database:

<!---
	Let's generate two values - one short one and one LONG ONE that is designed to use up
	more than 64,000 characters (which is the default truncation length in Adobe
	ColdFusion's data-source configuration).
--->
<cfset shortValue = " Hello world ".repeatString( 10 ) />
<cfset longValue = " Hello world ".repeatString( 70000 ) />

<!--- Let's try to insert some data! --->
<cfquery name="dbInsert" datasource="length_test">
	TRUNCATE TABLE
		length_test
	;

	INSERT INTO
		length_test
	SET
		id = 1,
		value = <cfqueryparam value="#shortValue#" cfsqltype="cf_sql_longvarchar" />
	;

	INSERT INTO
		length_test
	SET
		id = 2,
		value = <cfqueryparam value="#longValue#" cfsqltype="cf_sql_longvarchar" />
	;

	-- Now, let's see how much data was actually inserted into the database.
	SELECT
		id,
		value,
		LENGTH( value ) AS valueLength
	FROM
		length_test
	;
</cfquery>

<cfoutput>

	<ul>
		<li>
			<strong>Short Value ( #dbInsert.id[ 1 ]## )</strong>
			<ul>
				<li>Inserted Length: #numberFormat( dbInsert.valueLength[ 1 ] )#</li>
				<li>Returned Length: #numberFormat( dbInsert.value[ 1 ].len() )#</li>
			</ul>
		</li>
		<li>
			<strong>Short Value ( #dbInsert.id[ 2 ]## )</strong>
			<ul>
				<li>Inserted Length: #numberFormat( dbInsert.valueLength[ 2 ] )#</li>
				<li>Returned Length: #numberFormat( dbInsert.value[ 2 ].len() )#</li>
			</ul>
		</li>
	</ul>	

</cfoutput>

#coldfusion