LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 07-17-2003, 08:21 AM   #1
Satriani
Member
 
Registered: Mar 2003
Location: The Netherlands
Distribution: Red Hat 7.3, Red Hat 9, Solaris8, Slackware 10, Slax on USB, AIX, FreeBSD, WinXP, AIX, Ubuntu
Posts: 418

Rep: Reputation: 30
PLEASE!!! Help with mySQL needed!!!


I made an error... (Stupid)
I wanted to update a record in a database, so in mysql i ran:

update table_name set column_name='my new data';

But, as you can see, i forgot the 'WHERE' clause, and now every row contains this data.

Is there some kind of trick to get the data back? (Some kind of Undo)

I tried 'rollback' but that generated an error. It looks like i did not activate rollback functionality. (autocommit)

Is there some logfile where I get the data back, or is there at least some logfile of the outpu of all commands I ran? (I saw a tee option, but never used it. Is there somewhere a default file with this info in it?)

Any help would be grately appriciated.

A very stupid and thus sad person.....
 
Old 07-17-2003, 09:34 AM   #2
Rumblefish
Member
 
Registered: Jun 2003
Location: Delaware
Distribution: Redhat 7.0, 7.2, 8.0, 9.0, FreeBSD 4.6.2
Posts: 51

Rep: Reputation: 15
Unless you back up your data...

...you're pretty well up the creek. Backups are always highly recommended.

If this is a business system and you aren't ghosting your entire system, you should at least back up mission-critical data on a daily basis. This data includes your MySQL databases and possibly your entire /usr partition (in nearly every distro, this will also include your /home tree, because /home is usually a symlink to /usr/home).

I recommend, if you are using the box in question to maintain business-related data, that you invest in at least a 10GB tape drive. They aren't cheap, but they most certainly will give you a rollback of sorts in case this happens again. Also, turn off auto-commit as well. Much safer without it.

If you want information on backing up your MySQL tables, go to the documentation at http://www.mysql.com and search for both "mysqldump" and "backup" in the manual. I can't remember the section numbers offhand, but I believe they are 4.8.x. These tools will be invaluable, especially if set up with cron to automate the process.
 
Old 07-17-2003, 11:26 AM   #3
Satriani
Member
 
Registered: Mar 2003
Location: The Netherlands
Distribution: Red Hat 7.3, Red Hat 9, Solaris8, Slackware 10, Slax on USB, AIX, FreeBSD, WinXP, AIX, Ubuntu
Posts: 418

Original Poster
Rep: Reputation: 30
Quote:
Backups are always highly recommended.
Yup.... I know that... However, it wasn't a business critical system, so no real harm is caused. It was a table containing some kind of "weblog cq guestbook" for the users of my site.

But, one always finds out too late that one should have made a backup.....

Before i did the update statement, I did a select * statement.

So some records I could see the data of, so manually, i can put that in again. But my screen wasn't big enough to hold all records. I was hoping mysql (or even linux) kept some kind of logfile.

If there's someway of telling wich data was in it, before I ran the update statement, i would be helped alot. Then it's only going to take some time to get all the data back in, but at least I know what data there was....

Isn't there some logfile somewhere???? I'd settle with anything, as long as i can read the data that was in there. Getting it back into the database isn't the problem.....
 
Old 07-17-2003, 05:08 PM   #4
Satriani
Member
 
Registered: Mar 2003
Location: The Netherlands
Distribution: Red Hat 7.3, Red Hat 9, Solaris8, Slackware 10, Slax on USB, AIX, FreeBSD, WinXP, AIX, Ubuntu
Posts: 418

Original Poster
Rep: Reputation: 30
Does anyone know if its possible to find the data back? Or at least find out what used to be in there, so i can reinsert it???
 
Old 07-18-2003, 01:49 AM   #5
gabriele_101
Member
 
Registered: Oct 2001
Location: CAMBRIDGE, MA USA
Distribution: RH9 Kernel 2.4.20-18.9
Posts: 69

Rep: Reputation: 15
Unhappy

Alas, shy of shipping your hard-drive off to a specialty lab, there is no real way of getting your data back.

Additionally, autocommit can only be turned off on InnoDB and BDB tables. See http://www.mysql.com/documentation/m...ce.html#COMMIT

As for log files, there is a history file associated with mysql the client: ~/.mysql_history . It won't have the output from your select * SQL, however.

If your data was generated via GET commands through your website, then you could whip up a perl script to recreate the data from your weblogs. If it was via POST, then unless your CGI/PHP/JSP, etc pages log information, that info is lost.

My 0.063 cents....

-G
 
Old 07-18-2003, 03:35 AM   #6
Satriani
Member
 
Registered: Mar 2003
Location: The Netherlands
Distribution: Red Hat 7.3, Red Hat 9, Solaris8, Slackware 10, Slax on USB, AIX, FreeBSD, WinXP, AIX, Ubuntu
Posts: 418

Original Poster
Rep: Reputation: 30
Thanks all... It seems I just have to start over....
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL Help needed Ook Slackware 2 10-16-2004 11:49 AM
mysql via shell help needed tommytomato Linux - Newbie 3 12-01-2003 01:03 PM
OS X PHP mysql help needed iJasonT Linux - Software 1 05-04-2003 10:32 PM
MySQL Help needed muhammade Linux - General 9 01-28-2003 08:25 PM
MYSQL advice needed. d3funct General 1 12-12-2001 01:48 PM


All times are GMT -5. The time now is 06:38 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration