MySQL Bugs: #12157: Longtext Problem

Bug #12157	Longtext Problem
Submitted:	25 Jul 2005 16:10	Modified:	28 Jul 2005 18:50
Reporter:	Luciano GOnzalez	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:		OS:	Windows (windows XP Pro SP2)
Assigned to:		CPU Architecture:	Any

Description:
When trying to update a longtext field through php, and when the new data inserted into the cell is grater than 56000 characters (i.e.). The server seems to do nothing. But when de data updated is relatively small (about 100 characters) the update query works fine.-

How to repeat:
1) select longtext data from a table (more than 56000 characters)
2) alter the data selected, p.e. put in lowercase
3) update de table inserting de modified data into de longtext field

Could you please write here value of max_allowed_packet ? You can use for it follow command:
show variables like "max_allowed_packet";

Fine, the size of the max_allowed_packet is 1048576 (bytes i guess)

i´ll show you my php code:

$i = 1;
while ($i < 2) {
	$result = mysql_query("SELECT * from public where idpublic = '1'");
	$public = mysql_fetch_row($result);
	$patron = "/\s+/"; /*Regular Expression*/
	$field_public = preg_replace($patron," ",$publicacion[5]);
             mysql_query("update public set publication = '".$field_public."' where idpublic = '".$i."'");
             $i++;
             }

Maybe i´m doing a select and update so close with big amount of data...
p/d: id i use another regular expression the query don´t work too, but when in the second query (update) put a piece of text shorter than 39950 (+5-) the code works great...

Could you please say me you really use ODBC driver ? I tried to repeat on test case (C) - ODBC driver updates fine long texts. I attached the test case.

Test case of ODBC

Attachment: test.c (text/plain), 3.87 KiB.

Yes i do. I´m usind a odbc driver, the version is 3.51. I have created a user dsn and a system dsn. Did you make the test in "C"? Maybe the problem is PHP. I´m still probing. thanks

I was unable to reproduce this bug using PHP 5 under Linux & WinXP SP2.

Well the problem still persists, i don´t know if is about de odbc connector, mysql or php.

I have a table like this:

+-------------+----------+------+-----+---------+-------+
| Field            | Type      | Null    | Key | Default   | Extra   |
+-------------+----------+------+-----+---------+-------+
| idpublic         | int(11)   | NO    | PRI  | 0           |           |
| publicacion    | longtext | YES   |       | NULL      |           |
+-------------+----------+------+-----+---------+-------+

the Only data is: (1 row) idpublic=1 and publicacion= [longtext greater than 60000 chars]
well, this longtext begins with a particular text (i.e. "Serie de Encuentros") and i want to modify that.
I need to modify de text via web, i´m programming in php.
what I discovered when designing de page is when i get de data from the database table, is all Ok.
When the data is modified and printed by php, all is Ok too.
But when trying to insert the modified data into the table, again, it seems to do nothing, in the database, if the texts lenght is greater than 39950 chars aprox. If it is smaller works great.-

my code in PHP is

$i = 1;
while ($i < 2) {
	$result = mysql_query("SELECT * from public where idpublic = '1'");
	$public = mysql_fetch_row($result);
	$patron = "/(Serie de Encuentros)/"; /*Regular Expression*/
	$field_public = preg_replace($patron," ",$publicacion[1]);
	echo $campo_publicacion; // PRINTING OK!
        mysql_query("update public set publication = '".$field_public."' where idpublic = '".$i."'"); // DO NOTHING!
	//mysql_query("update publicaciones set publicacion = '".substr($campo_publicacion,0,35000)."' where idpublic = 

'1'");	// WORKS GREAT
	//mysql_query("update publicaciones set publicacion = '".substr($campo_publicacion,0,60000)."' where idpublic = 

'1'");	// WORKS BAD!
        $i++;
        }

if the table is MyISAM or InnoDB is the same...

That´s my specific problem...I thank for much your preoccupation, trying generate the problem in C, but what can´t do is to update the longtext field with more than 40000 chars.

thank you very much, in advance and sorry about my poor english.-

Luciano González