34125

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:

<pre class="lang-sql prettyprint-override">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:

<pre class="lang-vb prettyprint-override">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:

<blockquote>

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

</blockquote>

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

<pre class="lang-sql prettyprint-override">SELECT max(length(glossario)) FROM tableClassesNacionaisH;

The result was 34504.

Now that's the problem:

*Why Column glossario has its MaxLength set to <strong>21845</strong> if the real field contents <strong>from that particular query</strong> can be as long as <strong>34504</strong> and <a href="https://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html" rel="nofollow">TEXT DataType has maximum length of <strong>65535</strong> characters</a>?

And how can I prevent/circumvent this issue?*

Thank you very much.

<strong>EDIT:</strong> 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 <em>did</em> 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...).

Answer1:

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

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 <strong>will assume all UTF-8 characters are 4 bytes wide</strong>, 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.

Recommend

  • What is the runtime complexity of Python's deepcopy()?
  • Why VBA goes to error handling code when there is no error?
  • python: geometric brownian motion simulation [closed]
  • R shiny - last clicked button id
  • Fatal error on Windows XP when compiled with XP Targeting in Visual Studio 2015
  • Xamarin PCLCrypto SHA256 give different hash
  • Certain Arabic text gets incorrectly shown while other Arabic text gets showed normally?
  • How to extract text from a PDF and decode characters?
  • .NET video play library which allows to change the playback rate?
  • Dart - Isolate Cross Window Communication
  • In matplotlib, how do you change the fontsize of a single figure?
  • How can I include If-None-Match header in HttpRequestMessage
  • Detecting null parameter in preprocessor macro
  • jQuery .attr() and value
  • Breeze - Deleted Items nav properties bug
  • Highlight one bar in a series in highcharts?
  • Display issues when we change from one jquery mobile page to another in firefox
  • Deselecting radio buttons while keeping the View Model in synch
  • Encrypt data by using a public key in c# and decrypt data by using a private key in php
  • Getting last autonumber in access
  • javaw.exe and eclipse startup problems
  • How to check if every primary key value is being referenced as foreign key in another table
  • MySQL WHERE-condition in procedure ignored
  • Javascript convert timezone issue
  • JSON with duplicate key names losing information when parsed
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • Do create extension work in single-user mode in postgres?
  • Jquery - Jquery Wysiwyg return html as a string
  • How to get next/previous record number?
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • Hits per day in Google Big Query
  • How do you join a server to an Active Directory (domain)?
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • need help with bizarre java.net.HttpURLConnection behavior
  • How does Linux kernel interrupt the application?
  • Linking SubReports Without LinkChild/LinkMaster
  • apache spark aggregate function using min value
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • Sorting a 2D array using the second column C++