Solved

How do I write updates from a table to a text log file in MySQL 5.5 after an insert or update?

Posted on 2011-02-27
5
328 Views
Last Modified: 2012-06-27
I was wondering if anyone had a solution on how to set up a process for writing a new row to a text file once a table has been updated in MySQL 5.5?

I've researched using mysqldump, but I don't think this can be called within MySQL?

Looking at a second option, the  INTO OUTFILE can't overwrite existing files.

Basically I have a table and as it is updated, I would like to log a subset of the information in that table to a text log file. The text file should be updated immediately once the insert or update has happened. Preferrably the new row would be concatenated to the existing text file....

Any ideas would be greatly appreciated.

Jake
0
Comment
Question by:seedcoit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 9

Expert Comment

by:AnilKumarSharma
ID: 34994468
Before MySQL 5.0.10, triggers cannot contain direct references to tables by name. Beginning with MySQL 5.0.10, you can write triggers.
Check out this..
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

For sample check this forum thread (and follow it for more info),

http://forums.mysql.com/read.php?99,68167,68333#msg-68333
0
 

Author Comment

by:seedcoit
ID: 34999289
I created a trigger, but I don't think it can call mysqldump from there.

And as I mentioned earlier, the INTO OUTFILE statement can't overwrite existing text files. I need just one text file, updated after every insert or update.
 

0
 
LVL 1

Accepted Solution

by:
RealRaven earned 500 total points
ID: 35017935
The key here is the text file.

I see two options. Either log to a table and then dump i manually (or with cron) every now and then

or

Write a custom UDF to do this, there is a couple here http://www.mysqludf.org/lib_mysqludf_sys/index.php that might work, I haven't tried them myself. A call to there sys_exec inside the trigger would do the job.

0
 
LVL 1

Assisted Solution

by:RealRaven
RealRaven earned 500 total points
ID: 35018169
I just thought of another solution. I don't know if it fits you or not but it's worth a try

There is an engine called CVS that keeps all information in comma separated list on disk. The file is located next to the .frm file and is called .CVS

I guess it has some performance issues but depending on your application it might work.

Put a trigger on the original table that puts a row in the table with the CVS-engine and then just read the CVS-file.
0
 

Author Closing Comment

by:seedcoit
ID: 35690914
Both solutions worked.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question