Coldfusion 8 / MySQL 5 - Problem

Hi Angin, I can help to some extent with this.

I am having the exact same problem.  Data that was stored as longtext will not show when you try to display through Coldfusion.  In some cases I get nothing, in some cases I get junk characters.  The data looks fine in the MEMO field when you browse it.  Only happening in one database.  Happens on 2 different servers (corruption comes over as part of a backup/restore)

Switch the datatype to mediumtext and it displays fine.

You can also, in your query, do a "CONCAT" and add a space to the end of the longtext row, and the result of that will display (i.e.:

SELECT CONTACT( myLongTextColumn, " ") AS displaythisversion

and in your CFOUTPUT #displaythisversion# will work.

Now to what is wrong.

It's not a ColdFusion or MySQL version issue.  I know because I'm running the exact same code, exact same datatype  for another site and it works perfectly fine.  I have the problem on this particular site in ColdFusion 5 and ColdFusion 8, and on 2 different operating systems.  And I *don't* have this problem with the exact same code pointing to a different database (using the exact same tables and datatypes.)  What I'm saying is that longtext DOES WORK in all my other databases except this particular one.

So... the problem seems to be some kind of corruption in the particular MySQL database you are using.  For me, the problem crosses over to all tables in this database, so somehow the MySQL server has figured out how to hose just the longtext datatype one database and let it work just fine in all the other databases.

How to fix it?  No idea, I've tried everything I can think of so far.  Repair Table doesn't work.  Restoring previous backups doesn't work.  My guess is this - typically with BLOB and CLOB types the database internally creates a seperate area to store this data.  Perhaps MySQL creates a seperate area for each database, and then within each database creates a seperate area for each blob-type datatype.

So I'm imagining there is an area set aside for the longtext data for each database, and that little piece of the puzzle is somehow hosed just enough to make Coldfusion puke.

(LOL excuse my overly technical terms like hosed and puked, I just spent 4 hours on this mess and I didn't feel like facing the Sphynx today.)  If anyone has any suggestions (besides why are you using longtext) would love to hear them, this is a weird problem and would love to be able to fix!

Thanks for any help and hope this gives ya some perspective Angin!

-- Bill