Coldfusion 8 / MySQL 5 - Problem

[Angin]

Hi,
I've been working for this all day long and it seems to go nowhere.
Datatype longtext did not appear.For example address.
I use mysql 5 and coldfusion 8.
I already check CLOB at the Data Sources.

Pls Help 😞

[Newsgroup_User]

hmm... confused...
what exactly do you mean by 'did not appear'???
appear where? when? how are you trying to make it 'appear'?

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Angin]

This is how i retrieve address info from table user_detail:
<textarea name="address" cols="50" rows="6">#qry_detail.address#</textarea>
The datatype is longtext.

When i change the datatype to mediumtext or varchar,
It works.

I've check my query and nothing wrong with it.
This is the query:
<cfquery name="qry_detail" datasource="#odbcdsn#" dbtype="#appdbtype#">
SELECT * FROM user_detail
WHERE user_id = #url.user_id#
</cfquery>

[Newsgroup_User]

why do you need it to be longtext?
longtext has a max string length of 4,294,967,295 characters - do you
really need that much for an address field???


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Angin]

Sorry Azadi. Thats the simple example i can give.
There are other field in the database to store something long such as letter that i need to make it longtext.
The same problem occurs.

Is there other solution besides changing the datatype to medium text or varchar?
Because this problem occurs on many project when i upgrade to coldfusion 8 and mysql 5.
I dont know wherether the problem is on mysql 5 or coldfusion 8
😞

[Newsgroup_User]

which exact version of mysq and which version of mysql jdbc driver are
you using?

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Angin]

Mysql Version : MYSQL 5.1.32 -community via TCP/IP
Mysql JDBC Driver : mysql-connector-odbc-5.1.5

[Newsgroup_User]

ps: what you need to check is BLOB, not CLOB...
BLOB check box and value in BLOB buffer size for you dsn, to be precise...


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[coldfusion]

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