8 Replies Latest reply on Apr 7, 2009 3:19 PM by coldfusion@logicmountain

    Coldfusion 8 / MySQL 5 - Problem

      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 :(
        • 1. Re: Coldfusion 8 / MySQL 5 - Problem
          Level 7
          hmm... confused...
          what exactly do you mean by 'did not appear'???
          appear where? when? how are you trying to make it 'appear'?

          Azadi Saryev
          • 2. Re: Coldfusion 8 / MySQL 5 - Problem
            Angin Level 1
            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#
            • 3. Re: Coldfusion 8 / MySQL 5 - Problem
              Level 7
              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
              • 4. Re: Coldfusion 8 / MySQL 5 - Problem
                Level 7
                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
                • 5. Re: Coldfusion 8 / MySQL 5 - Problem
                  Angin Level 1
                  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
                  • 6. Re: Coldfusion 8 / MySQL 5 - Problem
                    Level 7
                    which exact version of mysq and which version of mysql jdbc driver are
                    you using?

                    Azadi Saryev
                    • 7. Re: Coldfusion 8 / MySQL 5 - Problem
                      Angin Level 1
                      Mysql Version : MYSQL 5.1.32 -community via TCP/IP
                      Mysql JDBC Driver : mysql-connector-odbc-5.1.5
                      • 8. Re: 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