DataColumn MaxLength issue with MySql.Data.MySqlClient.MySqlCommand.ExecuteReader

Question

I'm trying to migrate my data from SQLite to MySQL database but this strange issue is raising again and again.

I have this table, no relations with other tables:

CREATE TABLE `tableClassesNacionaisH` (
  `id` int(11) DEFAULT NULL,
  `nome` varchar(255) DEFAULT NULL,
  `situacao` varchar(255) DEFAULT NULL,
  `codigoNacional` int(11) DEFAULT NULL,
  `codigoNacionalPai` int(11) DEFAULT NULL,
  `natureza` varchar(255) DEFAULT NULL,
  `dispositivoLegal` varchar(255) DEFAULT NULL,
  `artigo` varchar(255) DEFAULT NULL,
  `sigla` varchar(255) DEFAULT NULL,
  `poloAtivo` varchar(255) DEFAULT NULL,
  `numeracaoPropria` varchar(255) DEFAULT NULL,
  `glossario` text,
  `lft` int(11) DEFAULT NULL,
  `lvl` int(11) DEFAULT NULL,
  `rgt` int(11) DEFAULT NULL,
  `root` int(11) DEFAULT NULL,
  `justEs1Grau` varchar(11) DEFAULT NULL,
  `justEs2Grau` varchar(11) DEFAULT NULL,
  `justEsJuizadoEs` varchar(11) DEFAULT NULL,
  `justEsTurmas` varchar(11) DEFAULT NULL,
  `justEs1GrauMil` varchar(11) DEFAULT NULL,
  `justEs2GrauMil` varchar(11) DEFAULT NULL,
  `justEsJuizadoEsFp` varchar(11) DEFAULT NULL,
  `justTuEsUn` varchar(11) DEFAULT NULL,
  `justFed1Grau` varchar(11) DEFAULT NULL,
  `justFed2Grau` varchar(11) DEFAULT NULL,
  `justFedJuizadoEs` varchar(11) DEFAULT NULL,
  `justFedTurmas` varchar(11) DEFAULT NULL,
  `justFedNacional` varchar(11) DEFAULT NULL,
  `justFedRegional` varchar(11) DEFAULT NULL,
  `justTrab1Grau` varchar(11) DEFAULT NULL,
  `justTrab2Grau` varchar(11) DEFAULT NULL,
  `justTrabTst` varchar(11) DEFAULT NULL,
  `stf` varchar(11) DEFAULT NULL,
  `stj` varchar(11) DEFAULT NULL,
  `cjf` varchar(11) DEFAULT NULL,
  `cnj` varchar(11) DEFAULT NULL,
  `justMilUniao1Grau` varchar(11) DEFAULT NULL,
  `justMilUniaoStm` varchar(11) DEFAULT NULL,
  `justMilEst1Grau` varchar(11) DEFAULT NULL,
  `justMilEstTjm` varchar(11) DEFAULT NULL,
  `justElei1Grau` varchar(11) DEFAULT NULL,
  `justElei2Grau` varchar(11) DEFAULT NULL,
  `justEleiTse` varchar(11) DEFAULT NULL,
  `criadoEm` datetime DEFAULT NULL,
  `atualizadoEm` datetime DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `poloPassivo` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In order to isolate the error, I wrote this test routine:

Private Shared Sub TestSub()
    Dim command_text As String = "SELECT * FROM atena.tableClassesNacionaisH;"
    Dim connection As MySql.Data.MySqlClient.MySqlConnection = AppSet.Atena.Connection
    Dim command As New MySql.Data.MySqlClient.MySqlCommand(command_text, connection)
    Dim table As New DataTable
    Dim dataexception As Exception = Nothing
    Dim rowsinerror As DataRow() = {}
    connection.Open()
    Try
        table.Load(command.ExecuteReader)
    Catch dataexception
    End Try
    connection.Close()
    rowsinerror = table.GetErrors
    If dataexception IsNot Nothing Then
        Debug.Print(dataexception.GetType.ToString)
        Debug.Print(dataexception.Message)
        For Each rowinerror In rowsinerror
            Debug.Print(rowinerror.RowError)
        Next
        Debug.Print(table.Columns("glossario").MaxLength)
    End If
End Sub

These are, in order, exception type, exception message, 1st row error (translation into English is mine), and value for table.Columns("glossario").MaxLength:

System.Data.ConstraintException

Fail activating restrictions. One or more rows contain values that violate non-null, unique or foreign-key restrictions.

Column 'glossario' exceeds MaxLength limit.

21845

Since I don't know how this number (21845) was inferred, I went to MySQL Workbench and ran the following query:

SELECT max(length(glossario)) FROM tableClassesNacionaisH;

The result was 34504.

Now that's the problem:

*Why Column glossario has its MaxLength set to 21845 if the real field contents from that particular query can be as long as 34504 and TEXT DataType has maximum length of 65535 characters?

And how can I prevent/circumvent this issue?*

Thank you very much.

EDIT: A friend pointed to me that 21845 is exactly 1/3 of 65535, which makes me suspect the issue is charset-related. I circumvented it by changing column's datatype to MEDIUMTEXT and thus elevating the ceiling way up to 16,777,215 characters (or bytes?, I'm not sure anymore). But the question remains: MySQL did stored a 34504-long string in a TEXT column without truncating it. CLR type System.String has no such low limit of 21845 or 65535 characters (it could hypothetically contain about 1 billion characters, but I'm good with less...).

Answer 1

In the link you provide, it clearly states that for TEXT fields, "The effective maximum length is less if the value contains multibyte characters."

UTF-8 is a Unicode Charset, and the default for your database. It uses 1-byte representations for the first 128 characters, which match the ASCII set, and 4-byte representations for the rest of the characters.

This means that a TEXT column can have more or less characters stored depending on whether the characters are ASCII or not.

Now, your problem comes when the MySQL driver doesn't handle this properly, and will assume all UTF-8 characters are 4 bytes wide, and will divide the Column Length by the 4 bytes and pass that to the DataTable as the actual Field Length. This means that it is technically possible to have some text in your database that won't fit in the DataTable, as has happened in your case.

The solution (or workaround in this case) to your problem is exactly what you have done for now, which is changing the column to a type that can contain more characters, as MEDIUMTEXT or LONGTEXT.

My guess is this decision was made to simplify the implementation of the MySQL Driver, but I would look at submitting a bug report to Oracle.