SQL Error 1406 Data too long for column

Question

I am trying to execute the query below in MySQL but get the SQL error 1406 Data too long for column error every time. 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's 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 query which contains no special/escaped chars 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 your help everyone, I deleted the HTML comments and it worked.

Diastasis

Would you mind pasting a result of SHOW CREATE TABLE my_db.my_table? That could help in solving the issue. – Tiber

Answer 1

Apart from the obvious possibility of getting an error for a different column (your error message does not seem to mention the exact column, which is weird), here's what the manual has to say about LONGTEXT:

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

You can verify maximum packet size with this query:

SELECT @@max_allowed_packet

If it happens to be a small value, see this related answer for further info.

Ductile

Thanks, max_allowed_packet is set to 1M (1048576) but this query must only amount to about 2Kb right? I'll get the value changed and give it a try anyway. – Diastasis

Answer 2

There must be an issue with your column datatype.

The string you are inserting (via update) is 1,875 which is nowhere near the limit of longtext.

Take a look at this "pour l''Europe et l''Afrique" in your insert string. It may be causing you issues, try to insert the data without the "'" character.

could you try to insert a string of 1,875 characters, perhaps "aaaaaaa....". this would rule out a problem with the contents of the string you are inserting.

Comeon

@AnthomnyBlake this string successfully saved in the Text type filed – Veinstone

thanks for the table def - I have updated my answer, please give it a try – Comeon

Thanks, I've just run another query [above] and got the same result. This time the string has no escaped chars. – Diastasis

is that the exact query you used? the field name has changed from "content" to "contenu", just a typo? – Comeon

I am still suspicious of the characters in the string, I would try a simple insert of 'abcdefg....' etc just to be certain – Comeon

so it was the contents of your string? – Comeon

Answer 3

I think you make the content filed as the varchar type. but for store the long text you have make it the text type. this type not required any length

Veinstone

Note that the current datatype is LONGTEXT which has a 4GB max length – Tiber