php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #25473 Updating single row in table causing all rows in table to be updated.
Submitted: 2003-09-10 13:28 UTC Modified: 2003-09-16 12:35 UTC
From: jim at bluedojo dot com Assigned:
Status: Not a bug Package: MySQL related
PHP Version: 4.3.3 OS: WinXP
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: jim at bluedojo dot com
New email:
PHP Version: OS:

 

 [2003-09-10 13:28 UTC] jim at bluedojo dot com
Description:
------------
I have a table with ~2.2 million rows.  Sometimes when I do an update to one single row it will instead update ALL of the rows using the same update, making the database totally useless.  This happens every one in about 500,000 updates to a single row.

I am running mysql 4.0.14 in WinXP.  I am using PHP 4.3.3 to do the update.

I updated a column of a row of type "text" with the following data (The string is quite long):

$buf =  "KLUC 98.5, Las Vegas' #1 Hit Music Station 			  local weather  KLUC Privacy Policy Terms of Use Agreement Employment EEO Policy EEO Public File Report  As the school year just gets started, 98.5 KLUC, Las Vegas' #1 Hit Music Station asks you this.. Are you ready to PARTY!!! It's the return of the High School Elimination . It's your opportunity to show your school spirit and try to survive from being voted off. If you do, your school will win a free party at Gameworks, and you never know what KLUC has up it's sleeves. Can VoTech repeat as champions or will the rookies, Liberty or Shadow Ridge be the ones to take it all. It's High School Elimination 2 from the only station with all the school spirit, 98.5 KLUC, Las Vegas' #1 Hit Music Station. Click here to register and vote! 98.5 KLUC, Las Vegas' #1 Hit Music Station is bringing yet another huge KLUC concert to the Vegas Valley. Mack 10 live on-stage Thursday September 18 at the House Of Blues. Keep it locked onto KLUC for you shot to win. It's Mack 10 brought to you by Michelob, House Of Blues, and Las Vegas' #1 Hit Music Station, 98.5 KLUC. Every week the 98.5 KLUC High School Football Patrol pads up with live updates from the best games of the week. Check out the football patrol in the stands for the Bishop Gorman at Liberty and Canyon View at Shadow Ridge matchups. High School Football Patrol only from Las Vegas' #1 Hit Music Station, 98.5 KLUC. Every Monday - Friday, KLUC midday man Tim Stevens plays all your favorite old school music during the Old School Lunch Hour at noon. Call in with your requests and you may win lunch for (2) from Aloha Kitchen. 98.5 KLUC, Las Vegas' #1 Hit Music Station is teaming up with Aquafina to spot you. Listen to KLUC to find out where the Party Patrol will be with ice cold Aquafina and KLUC prizes throughout the valley. If you pick an Aquafina bottle with an X on it, you'll have the chance to win CD's, DVD's, PS2, Xbox, and Gamecube games, and tons of other prizes. It's the Aquafina Get Spotted contest with Aquafina and Las Vegas' #1 Hit Music Station, 98.5 KLUC. Training with Trejo Listen to the Morning Zoo M-F 5a-9a for a daily update on Trejo's progress! The 98.5 KLUC Party Patrol is back for the Summer of Jamz! Every Saturday night, Rayne and the KLUC Party Patrol load up the Urban Suburban and will broadcast LIVE fron your living room with awesome KLUC prizes like movie tickets, CD's, concert tickets, Wet N Wild tickets, food and drinks and more and they could crash your crib! BEST OF ALL, IT'S FREE! Click here to fill out the entry form and get ready, because Saturday night, the Party Patrol could be knockin' on your front door! From Las Vegas' #1 Hit Music Station, 98.5 KLUC! upcoming events Tue. September 9 11:00pm Tim Stevens @ Studio 54 Wed. September 10 11:00pm JB @ Baby's Thu. September 11 11:00pm Bo Jaxon @ Club Rio kluc's top 8@8 Results From :: 9.8.2003 1. Suga Suga Baby Bash 2. Get Low Lil Jon 3. Can't Nobody Hold Us Down Christina/Lil Kim 4. PIMP(remix) 50Cent 5. Right Thurrr Chingy 6. Shake Yo Tailfeather Nelly, P-Diddy 7. Thoing Thoing R.Kelly 8. The Way You Move Outkast  morning zoo poll The Bennifer (Ben Affleck & J-Lo) are to be married this weekend. How long do you think this marriage will last? 1 year 3 years 5 years Until J-Lo gets bored For Eternity current results earlier poll results 288 people have taken this poll ? 2003 KLUC / Infinity Radio Corp."


I am thinking something in the variable '$buf' is causing mysql to do this.  Some character (like single quote or weird character) in the update is causing all the rows to be updated when only one row is supposed to be updated.  


Reproduce code:
---------------
Here is a sample SQL (cleaned up for easy reading): 

$buf ="A long string";
$buf = addslashes($buf);
mysql_query("UPDATE page SET page_content = '$buf' WHERE page_id = 10");


Expected result:
----------------
It should only update a single row.

Actual result:
--------------
It updates all the rows in the database.  Not good. :(

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2003-09-10 13:31 UTC] jim at bluedojo dot com
I wanted to say that I pulled out that $buf string from my corrupted database (which has the same rows).
 [2003-09-15 01:37 UTC] jim at bluedojo dot com
Ok, I did what you said.  The error occurs very, very rarely so I need to debug it still and it is very hard to debug.  (Since it takes about 5 days to completely update the database).

Basically I get two different errors.

1) One error produces a mysql 145 (which means the table becomes corrupted and needs to be repaired.  And when I do repair the table then it becomes a 144 error which means the table is gone forever and it cannot be repaired).  This has happened to me about 5-10 times already and it is very frustrating since the database is so huge (29 GB) and it takes a long time to update, copy, move, etc.

2) The other error just makes all of the columns in a table the same update statement.  This error seems to be fixed after using mysql-real-escape-string function instead of addslashes.  I am not completely sure of this.  It's possible the error could be the same.

I will let you know what I find.
 [2003-09-16 12:35 UTC] sniper@php.net
1. is hardly PHP bug. And as using correct function fixed the problem you reported in the first place -> bogus.


 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Dec 27 01:01:28 2024 UTC