SQL Error 1406 Data is too long for a column

Question

I try to execute the query below in MySQL, but each time with a SQL Server 1406 error column error too much. The column data type is longtext. Any ideas?

UPDATE `my_db`.`my_table` SET `content` = '<div id="primaryContent"><div id="offices_map"></div><!-- #offices_map --><div id="offices_mapControlPanel" class="cf"><ul id="offices_continentLinkList"><li><a href="#" rel="Africa">AFRIQUE</a></li><li><a href="#" rel="Asia">ASIE</a></li><li><a href="#" rel="Australasia">AUSTRALASIE</a></li><li><a href="#" rel="Europe" id="offices_europeLink" class="current">EUROPE</a></li><li><a href="#" rel="NorthAmerica">AMERIQUE DU NORD</a></li><li class="last"><a href="#" rel="SouthAmerica">AMERIQUE DU SUD</a></li></ul><ul id="offices_mapLegend"><li id="offices_mapLegendRedPointer">Bureaux Panavision</li><li id="offices_mapLegendYellowPointer">Agents Panavision</li></ul></div><!-- #offices_mapLegend --><div id="offices_ownedOfficesContactDetails" class="cf"><h2>Bureaux Panavision</h2><ul class="cf"><li class="first"><strong>Panavision Greenford</strong> - pour l''Europe et l''Afrique - <a href="#" id="offices_linkPanavisionGreenford">D&#233;tails</a></li><li>Panavision Prague - <a href="#" id="offices_linkPanavisionPrague">D&#233;tails</a></li><li>Panavision Manchester - <a href="#" id="offices_linkPanavisionManchester">D&#233;tails</a></li><li>Panavision Alga &amp; Cinecam - <a href="#" id="offices_linkPanavisionAlga">D&#233;tails</a></li><li>Panavision Rh&ocirc;ne-Alpes - <a href="#" id="offices_linkPanavisionRhoneAlpes">D&#233;tails</a></li><li>Panavision Marseille - <a href="#" id="offices_linkPanavisionMarseille">D&#233;tails</a></li><li>Panavision Pologne - <a href="#" id="offices_linkPanavisionPoland">D&#233;tails</a></li><li>Panavision Dublin - <a href="#" id="offices_linkPanavisionDublin">D&#233;tails</a></li><li>Panavision Belgique - <a href="#" id="offices_linkPanavisionBelgium">D&#233;tails</a></li></ul></div><!-- #offices_ownedOfficesContactDetails --></div><!-- #offices_mapControlPanel --><div class="cf"></div>' WHERE `my_table`.`id` = 27; 

Thanks, here is the result from SHOW CREATE TABLE

  CREATE TABLE `my_table` ( `content` longtext NOT NULL, `cat` text NOT NULL, `starter` int(1) NOT NULL, `at` int(11) DEFAULT '0', `table` varchar(60) DEFAULT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=84 DEFAULT CHARSET=latin1 | 

Just tried this shorter request that does not contain special / escaped characters, but I still get the error.

 UPDATE `my_db`.`my_table` SET `contenu` = '<div id="primaryContent"><div id="offices_map"></div><!-- #offices_map --><div id="offices_mapControlPanel" class="cf"><ul id="offices_continentLinkList"><li><a href="#" rel="Africa">AFRIQUE</a></li><li><a href="#" rel="Asia">ASIE</a></li><li><a href="#" rel="Australasia">AUSTRALASIE</a></li><li><a href="#" rel="Europe" id="offices_europeLink" class="current">EUROPE</a></li><li><a href="#" rel="NorthAmerica">AMERIQUE DU NORD</a></li><li class="last"><a href="#" rel="SouthAmerica">AMERIQUE DU SUD</a></li></ul><ul id="offices_mapLegend"><li id="offices_mapLegendRedPointer">Bureaux Panavision</li><li id="offices_mapLegendYellowPointer">Agents Panavision</li></ul></div><!-- #offices_mapLegend -->' WHERE `my_table`.`id` = 27; 

[SOLVED] Thanks for all your help, I deleted the HTML comments and worked.

Answer 1

Besides the obvious possibility of getting an error for another column (your error message does not seem to mention the exact column, which is strange), here that the manual should indicate LONGTEXT :

TEXT column with a maximum length of 4,294,967,295 or 4 GB (232 - 1) characters. The effective maximum length is less if the value contains multibyte characters. Effective maximum length LONGTEXT columns also depend on the configured maximum packet size per client / server and available memory . Each LONGTEXT value is equally stored using a four-byte prefix that indicates the number of bytes in the value.

You can check the maximum packet size with this query:

 SELECT @@max_allowed_packet 

If it will be small, see this answer to this question for more information.

Answer 2

There must be a problem with the column data type.

The line you insert (through the update) is 1.875, which is nowhere near the limit of long text.

Take a look at this "pour l '' Europe et l '' Afrique" in your insert line. This may cause problems; try pasting data without the "."

Could you try inserting a string of 1875 characters, perhaps "aaaaaaa ....". this eliminates the problem with the contents of the row you are inserting.

Answer 3

I think you are making the content served as a varchar type. but to store long text you create a type of text . this type does not require length