[RESOLVED] MySQL - Large text field causes ConstraintException

[kebo]

Hi all,
I'm having a strange problem with one of my tables. I've been using this table for quite some time, then the customer gave me a requirement to attach data in an excel file to the table. Since the data in excel table needs to edited in the application, I can't just store a path to the file. Also, the excel file for each row is too different to create a new table just for the excel data. Each excel file has a different number of columns, different column heading, etc. The working solution is to collapse the excel file into a csv string and stuff that into a field of the table. This works(ed) file until the customer gave me a file that had 300+ rows.

The large excel file collapsed into a string that was about 22,000 characters (below the 64K limit of a MySQL text field) and the entire string saves in the DB without issue. It's when I try to fetch the row with the large excel string that I get the Constraint exception. Furthermore, if I ignore the exception, the data loads fine and completely, so although there is an exception raised, I don't know why, and the only thing I've been able to do is ignore it.

Has anyone seen this issue or anything like it? My working solution is to simply catch the Constraint exception and ignore it, but in doing so I am potentially ignoring exceptions that I shouldn't.

Thanks for your time
kevin

edit:
Changing the MySQL field to LONGTEXT results in the same exception being thrown.

Also, the exact number of excel rows causing the problem is 323. If I reduce the number of rows to 300, I don't have a problem, so it seems that in this case, size does matter.

Another tid-bit... I am loading a data table directly using this code...

Dim dT as new datatable
dT.Load(cmd.ExecuteReader)

but when I run the command string directly on the database (outside of the .net environment) it runs fine.

[techgnome]

My guess is that it isn't a problem with MySQL but rather with the code itself. Rather than including the data into the datatable, can you load everything else into the datatable and then only get the LONGTEXT on demand. That way you're only getting the data when you want it, and reduce the over head of also getting the data for all the rows that you may not need.

-tg

[kebo]

tg-
I would completely agree that this is not a MySQL issue. In fact now I know it is not. When a data table is loaded from a data reader, apparently the data columns are added to the table with a max length of 21,845. Is it a coincidence that 21,845 * 3 = (2^16)-1 ? I don't think so, but if I add a column to the table first, the exception is avoided. The following code shows this...

Dim _connString As String = "Server=127.0.0.1;Uid=kevin;Pwd=1234;Database=test"
        Using conn As New MySqlConnection(_connString)
            conn.Open()
            Using cmd As New MySqlCommand()
                cmd.Connection = conn
 
                cmd.CommandText = "DELETE FROM testTable"
                cmd.ExecuteNonQuery()
 
                cmd.CommandText = String.Format("INSERT INTO testTable SET longfield='{0}'", "".PadRight(21846, "X"))
                cmd.ExecuteNonQuery()
 
                cmd.CommandText = "SELECT longfield FROM testTable LIMIT 1"
                Dim dr As MySqlDataReader = cmd.ExecuteReader()
 
                Dim dt As New DataTable
                'adding a column first yields a max length of -1 (or no max) 
                Dim c As New DataColumn("longField", GetType(String))
                dt.Columns.Add(c)
 
                Try
                    dt.Load(dr)
                    Debug.Print("ok")
                Catch ex As Exception
                    Debug.Print("no good")
                End Try
                Debug.Print(dt.Columns(0).MaxLength)
                conn.Close()
 
            End Using
        End Using

Add the column it runs.... don't add the column and it doesn't

I don't know if this is intend behavior or an oversight, but I guess the only solution is to load data into a table with preexisting columns.

This is pretty much resolved with a not-so-elegant solution, but if anyone has any more insight to this, I' like to hear it.

[techgnome]

It's probably by design... when the col is added automatically, it has to set a size to it... since it doesn't quite know how big to make it, an arbitrary size (who needs more than 21k characters anyways, right? ) is used. But when you add the field via code, you can specify the length and avoid the problem.

-tg

[kebo]

Something is amiss with this. Taking a look at the datareader's schema before loading the table (using dr.GetSchemaTable.Columns(0).MaxLength) shows the column has a max length of -1. That makes me think the MySQL datareader is not be doing what it ought to be doing. Seems like it's out gallivanting around using "an arbitrary size" for this and that instead of making a table based on the schema.

[techgnome]

Right... it's -1 because it doesn't know what the length should be... readers only deal with a record at a time... it doesn't see the whole table... so when you start to read the data, how should it know what size to make it? If it bases it off the first row, what if it only has 200 characters in it? And then the second record has 250... so initially it sets it to -1because it doesn't know what size it should be. When it reads the first record, and it sees the data, it updates the internal schema to some arbitrary size (presumably the devs picked that number as a "Reasonable" size)... and that's what you get, unless you explicitly set the size. In other words, what you're getting is the default size set by the provider.

-tg