Page 1 of 1

Problems inserting a large string into a mediumtext field

Posted: Sat Apr 12, 2008 3:25 am
by visionmaster
Hello together,

I'm having extreme difficulties inserting a large string of 1243746 characters into a mediumtext field produkte_volltext . MysQL Server Version: 5.0.51a-log. Table is a MyISAM, collation utf8_general_ci.

Here my code-snippet

Code: Select all

[...]
 
#echo mb_strlen($pipe_separated); # = 1243746
$pipe_separated = implode("|", $arrProdukte); # $arrProdukte holds the products in an array
#$pipe_separated = substr($pipe_separated,0,1088277); #error, mediumtext field produkte_volltext is empty!
$pipe_separated = substr($pipe_separated,0,1000000); #ok, mediumtext field holds exactly, 1000000 characters
 
$strSQL = "UPDATE firmendaten SET produkte_volltext = '".mysql_escape_string($pipe_separated)."' WHERE idfirmendaten = ".$intFirmenId;
mysql_query($strSQL);       
[...]
 

After running the script, I always checked how many characters where actualy in the mediumtext field produkte_volltext with following SQL-statement:
SELECT CHAR_LENGTH( produkte_volltext ) AS zeichen FROM `firmendaten` WHERE `idfirmendaten` =438073

I couldn't find out the exact number of characters, by which MySQL actually fails to insert the string, leaving the mediumtext field produkte_volltext empty.

=> Is there some limit of characters, whicht causes the UPDATE statement to fail inserting a large string?

Thank you for your help!

Re: Problems inserting a large string into a mediumtext field

Posted: Sat Apr 12, 2008 9:50 am
by bdlang
You should consult the manual.

Look at what character set you're using and calculate how many bytes that set takes to store data vs. let's say ascii for example.

Use mysql_error() to trap any query errors, or retrieve the warning or errors given on a particular statement with by issuing a 'SHOW WARNINGS;' or 'SHOW ERRORS;' statement.

Re: Problems inserting a large string into a mediumtext field

Posted: Sat Apr 12, 2008 10:34 am
by visionmaster
@bdlang, thank you for your reply.

I actually found the solution for the problem.
There is a MysQL system variable max allowed packet, which was set to 1MB. I changed its value to 2MB and look here, the import of such a large string worked out fine!

At first I found this webpage: http://pudge.net/glob/2005/02/useperl-23012.html

=> "Problem: max_allowed_packet doesn't merely prevent you from sending data of a certain size: it also governs the size of an "expanded" query. So if data is 1MB and $chunk is 512K, you still hit a 1MB max_allowed_packet limit with the CONCAT(...).
Solution: increase max_allowed_packet."

Then following webpage:
http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html

=> "String-valued functions return NULL if the length of the result would be greater than the value of the max_allowed_packet system variable. See Section 7.5.2, “Tuning Server Parameters”."


After importing the file (1243746 characters), I made the following check:
SELECT CHAR_LENGTH( produkte=_volltext ) AS zeichen FROM `firmendaten` WHERE `idfirmendaten` =438073

=> This query exactly returned 1243746. Which means the large string was successfully inserted into the field and that the system variable max_allowed_packet was actually the cause...

Re: Problems inserting a large string into a mediumtext field

Posted: Sat Apr 12, 2008 1:03 pm
by bdlang
Nice. I actually thought about that, as it's mentioned here, but I'm glad you solved the problem.