Problems inserting a large string into a mediumtext field

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
visionmaster
Forum Contributor
Posts: 139
Joined: Wed Jul 14, 2004 4:06 am

Problems inserting a large string into a mediumtext field

Post 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!
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Re: Problems inserting a large string into a mediumtext field

Post 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.
visionmaster
Forum Contributor
Posts: 139
Joined: Wed Jul 14, 2004 4:06 am

Re: Problems inserting a large string into a mediumtext field

Post 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...
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Re: Problems inserting a large string into a mediumtext field

Post by bdlang »

Nice. I actually thought about that, as it's mentioned here, but I'm glad you solved the problem.
Post Reply