MYSQL :: Audit Trail Of Updates And Deletes

Clean Up Tables After Deletes
I noticed that in phpMyAdmin there is a way to cleanup and compact
tables that have had lots of deletes done to them. Can anyone tell me
how to do that manually? I find phpMyAdmin to be a pain so I don't want
to use it but I would like to compact some tables that got bloated with
testing and are now much smaller than before. The main reason I want to
clean them up is that new records go into slots previously used and
deleted instead of going at the end, where they naturally belong
(chronologically).

Multiple Inserts And Deletes
I'm using PHP/mySQL couple :) to insert and delete some informations
based on some criterium and I don't like to look at my code full of
FOR and IF's.

1)
Example: In a list of ID's 1 4 5 7 8 9 20 34 56 is possible to send an
UNIQUE command to mysql to delete the Id's 5, 7, 20, etc? Need I put a
ugly query like "Delete ... WHERE id=5 AND id=7 AND id=20" ad
infinitum?

2) How can I check without loops if a given value exist in the table?
Example: id's 1 3 5 7 8 must be inserted ONLY IF 3 or 5 doesn't exist.

Finally, It's very hard to do some search for examples to know how to
use the keyword "IN" , any hint please?

Store Inserts/deletes
I need help writing a program that stores all the inserts, deletes and updates to my mysql database.

Large Tables, Very Slow Deletes.
I've been using MySQL for a while now and are starting to run into limitations. Either my own, or something else.

I have this really large table, it stores images, it has a mediumblob field, an unsigned id integer field and a timestamp field. This table contains up to a few million rows and is constantly filled real time. It grows up to sizes between 50 and 100 gb and in the future probably even larger.

This data is not supposed to be stored for all time, therefor when a certain treshhold is reached, the oldest 5000 rows are deleted to make room for newer rows. This is where my problem kicks in. Whenever I try to delete those rows, it can take for ever to complete. 500 rows I can delete without problem, 2500 becomes slow, 5000 rows takes several seconds and 10.000 rows or more makes it looks like things are frozen.

Now the temporary solution I have is to delete 500 rows at time, but this means I must do that every 10 seconds just to maintain status quo. I would much rather check every 5 minutes or so, and if needed delete 15.000 rows in one go.

I use MyISAM tables and tried setting the key size to 128M, no luck. I run this on a dual Opteron system with a GB of memory and WinXP-SP2 Proffesional.

Mysql_affected_rows And Recursive Cascading Deletes Possible
I have 5.1.17 and one table 'connected' to itself via a foreign key (see below)
When I delete a parent post with children they all get deleted BUT a subsequent call to mysql_affected_rows always returns 1.

I would expect/hope that it returns the total number of deleted posts(including any cascading ones) Am I wrong??

Updates
How do I look for/download and apply updates to the Windows 2000 server.

Updates
if there's a query that could help me 2 update my table fields as follows.
It needs to 2 tables.

Withing table2 i needs to update the same field twice 2 different records e.g. set name='1', name='2' where id='1' and id='2'.
I've tried it like that but all it does is to update both records with 2.
What i wanted 2 to was to update "name=1 where id=1" then "name=2 where id=2".
Can anyone help me on this?

SQL Updates
This is so weird, I'm writing a little poll thing for my website and I've run into a problem..
$update = 'UPDATE mith_poll SET ans7N = 3 WHERE id = 1';
mysql_query($update);
When I vote on an answer it will go up +1 but when I pick a different answer after the last field has been updated the same field gets updated again, I then have to vote again to make the second choice go up + 1..
Am I making sense?
I'll try a bit harder for those who don't understand..
I select answer A
Answer A gets updated
I select answer B
Answer A gets updated
I select answer B,C,D,E and which ever I picked gets updated
I select answer F and the last answer gets updated

Updates Not Consistent
I got a simple application in C# where I have a text box and the userinsert the serial number then I will check if its status in thedatabase is 'y' then I am able to select it and set a new status as's'. The problem here if I do it 2 for different serial number oneafter another for ex. first I select 00123 and then 00124. First the00123 is set to 's' but then the moment I select 00124 the status of00123 gets back to 'y'. I am using the same set of code for both 00123and 00124. There is no place where I run a query which sets the statusto 'y' and I dont understand why the status of 00123 can become to 'y'when it shows in the first place it has succeffully updated to 's'.Below is my code. I have commented the places what I do exactly.

MySQL Updates
We have a new application that will be using MySQL i haven't used it before so have some time put aside to get the hang.

I downloaded mysql-essential-5.0.45-win32.msi and installed it i have the app up and running ok but i'd like to know how i find out what MySQL patches are available and if they need applying. Is there a live update style facility?

Updates And UDF Statements
I have a particular UDF function called pcd which returns the first 1
or 2 letters of a postcode (postcode area), it works fine in selects
but when i use it in a update the column being updated ends up with
the first column in the table, 10 spaces, then the 2nd column in the
table, instead of whatever the UDF function returns.

Has anybody else experienced similar problems, im losing my faith in
mysql a bit, im trying to use it for serious business applications but
might have to find an alternative. I prefer linux servers to windows,
any advice, like Max DB?

Automatic Updates
I'm not sure if this is possible or not, but is there a way to automatically insert data and delete data in a database at specific times during the day?

Is there a script or something out there that would allow me to do this?

Substrings In Updates
I have a column called full_name that contains name like 'amar ram patel'.
I want to update the same table by breaking the name in 3 different values using space as delimiter and add it to the respective columns viz. name, middle_name and surname.
I guess substring_index function can be used for the same.

Trigger Updates
If a certain table is updated I want all other tables which contain some of its attributes as a foreign key to be updated as it is updated.
However the tables that I want to trigger updates from is ISAM
and the other tables are INNODB.

Table Updates
There is a field in a MyISAM table that gets updated each time a PHP page is reloaded, providing an indicator of continued presence on a web site. There is also an app on the server that watches this field for 'logged in' users and logs them out if the timestamp doesn't update for several minutes. Mostly it works, but sometimes it seems that the value in the database table isn't being updated. I.e. the web page refreshes, but the server-side app continues to get the same timestamp.

I'm pretty new to this environment, so I didn't attempt to layer session logic (transactions) over the top of the basic 'update table' and 'select' commands.
Is there something I can put in the php file, or a configuration option on the database or server that would insure the data is committed to disk? (and, since the docs say that happens when the php script terminates..., is there something else I might not be seeing in this situation?)

Multiple Updates.
UPDATE site_settings SET
value = '$title' WHERE setting_name = 'site_name'
value = '$keywords' WHERE setting_name = 'keywords',
value = '$description' WHERE setting_name = 'description'
How can I perform this in ONE Query?

I Have Way Too Many Inserts/updates In My Forum
when someone posts a thread theres 2 count queries
2 fetch queries
3 insert queries
and 2 update queries

so my question is, is there a way to do a sort of join query for inserts and updates?

also, has anyone got some mysql musts?
i dont see a mysql tips thread
i think there should be one

Optimizing Multiple UPDATEs
Basically what I want to do comes down to this:

UPDATE players
SET ranking = 1
WHERE id = 45;

UPDATE players
SET ranking = 2
WHERE id = 30;

UPDATE players
SET ranking = 3
WHERE id = 8;

UPDATE players
SET ranking = 4
WHERE id = 97;

UPDATE players
SET ranking = 5
WHERE id = 12;
I do have a php array containing each player ID with its (new) ranking. However looping through all the players to update their rankings (as shown above) does not seem like a scalable solution to me.

Finding Out Table Updates Since...
I want to synchronize 2 sets of tables and need to be able to tell wich updates the offline set has had since the last time it sychronized the online table. Then i want to run these queries against the online table so their both in sync again.

Is there a MySQL way to do this or will i have to store the SQL queries ran agains the offline table manualy?

Updates Not Visible To Client
I have a Java application (app A) which "polls" a MySQL database table
for records with a certain column set to 0. It runs continuously,
utilising the same commection repeatedly.

The other day, I had a support call raised, whereby there were clearly
suitable records in the database, but my application was not selecting
them.

I wrote a standalone test app (app B) to read the same table and list
the records with the column=0. Sure enough, this app listed records as
requested.

So, app A retrieved zero records whilst app B retrieved many. Both
apps *are* using the same select statement.

I think it might be something to do with the rules governing when
updates become visible to clients.

The application that writes records to the database is not mine so I
don't know all the details. It's a php webapp that seems pretty simple
and just does plain inserts into the table with no attempt to do
anything non-default wrt locking afaik.

Updates To Multiple Tables
can i insert multiple records into multiple tables using MySQL:

statement.executeUpdate("INSERT INTO members, members2, members3, members4, members5 SET
La_Na='"+lana+"', Fi_Na='"+fina+"', NRIC='"+nric+"', CCode='"+ccode+"'");

is this possible?

Safe-updates From All Clients
This is version 5.0.24a on a Windows based machine.

If safe-updates is specified in the [mysql] section, if using the MySQL command line client, safe-updates does work! However, if using another client, or a web page for example to access the database, safe-updates does not work.

Is it possible to have safe-updates work in all clients, such as through connector/ODBC?

safe-updates in the [mysqld] section does not work (the server will not even start with it there).

So the question is: Is it possible to specify safe-updates somewhere so that it is global to every connection that accesses the database no matter what the source is for that connection?

User Updates On MySQL With PHP
I am trying to do is to have a login form in html or php that when a user enters a user name and password that they are taken to a page where their editable information in a mysql database is displayed for them to change.

Mass Updates To A Table
I've got a web-based form that will allow people to update a database
that is displayed as a table. Let's say that there are several columns
across titled Red, Blue, Green... and several rows titled 01, 02, 03...
In each cell, they can fill in some simple text data.

The only way that I can think of to update the database with this
information is using two imbedded loops in Perl:

pseudocode:
for($color=1;$color<=10;++$color) {
for($number=1;$number<=10;++$number) {
update table xyz set value=$value[$color][$number]
where color=$color and number=$number;

This would mean that it would have to execute 100 update statements.
Perhaps this is no big deal and I shouldn't worry about it, but since
I'm relatively new to sql, I'm guessing that there's probably a more
streamlined method to do this.

BTW - I've seen a lot of people asking questions that were vaguely
similar to mine in this group, and they've been told to go to the php
group, even though I didn't see any php-specific code in their
questions. I don't use php and don't have it installed on my server.

Multi-Table UPDATEs
on my old server I developed an application in MySQL 4+. Now I am trying to run it on a different server that only has MySQL 3.

Previously, I used these queries:

CREATE TEMPORARY TABLE x
SELECT *, 0 AS flag
FROM user
WHERE active = 1
ORDER BY id
LIMIT 0,25

UPDATE x, othertable
SET x.flag=1
WHERE x.id = othertable.userid AND othertable.type = 8

Is there any way to do this same sort of thing - update data in one table based on data in another table in MySQL 3?

Detect And Track Updates
I'm trying a develop a connector (or synchronizer) between MySQL and OpenLDAP(very similar to Metadirectory connector). The connector has to detect any updates made to the mysql database and should replicate that in OpenLDAP. The first part of it, is how to detect changes in MySQL? I thought of the option of using a Timestamp variable as an additional column in my DB table. But it won't work for 'delete' detection. Please let me know how to do that or point me to any source regarding that. Please reply soon as this is a part of my project.

Newbie: Formating Data In Updates
My POS products table is formatted totally different from that of the Online products table. No problem, I've got FileMaker Pro and it is really good at changing and formating data how I want it. I can output the product records for my website one way, the output the records for Froogle in another format.

The problem is that it takes and extremely long time with 70k+ records. My current scenario is outgrowing itself and I need to come up with a better solution. I need to find a quicker solution, possibly one I can automate down the road.

Any advice for resources? Or examples?

Unwanted Updates Follow A Select
I update a field after a very banal query (call it the WANTED update).

After that, this crazy thing happens: if I load a page of statistics to see the newly updated values after the mentioned update query has run, it results updates by 3 numbers rather than increased just by 1.
I am absolutely, psoitively sure the WANTED update increased only by 1: I printed the result all in the page, no weird addition ensued.

If I reload again the statistics (that is, I deliver one more select), it doesn't happen.

Morale, an update, followed by a select, updates (this is not wanted!) the selected fields of more than 2 or 3 digits. If another select follows on the same table, no unwanted updates occurs.

Best Way To Issue Hundreds Of Inserts/updates?
What is the best way to execute several (hundreds of) inserts and updates?
Rather than issuing tons of individual inserts and updates, can I send the
strings to a text file and then have mysql do them all??

IE :
query.txt

insert into table1 (col1,col2) values (1,2);
insert into table1 (col1,col2) values (9,4);
update table1 set col2=0 where col1=9;
insert into table1 (col1,col2) values (5,2);
insert into table1 (col1,col2) values (6,2);
insert into table1 (col1,col2) values (4,2);

Whats the best/fastest way to execute a few hundred of these?
Should I lock the table and then somehow use mysql.exe to run all the
commands in the txt file??

Updates Invisible - Windows Only (Linux OK)
I have one Java app writing records to a database (App A) and another,
a Java web application running under Tomcat, displaying the records.
Call this App B.

When I first log into App B and get a connection, all records in the
database at that point get displayed OK.

But after that, any new records inserted by App A do not get displayed
when I refresh (running the query again).

If I log out of App B and log in again, getting a new conection, I see
all the new records.

This behaviour only occurs if I'm running on Windows. If I run the
database on my Linux box with the same application code, new records
inserted by App B are immediately visible to App B with no need to
close the connection and open a new one.

Syncronizing Updates From File To Mysql
Is it possible to upload just the changes made to file since last time
it was imported/updated to the database.

For Eg:

1. File A has String "One"
2. This get uploaded to mysql table
3. File A is updated and now it has
"One"
"Two"
4. Only "Two" gets uploaded to mysql.

ALTER TABLE And Logging Updates During Process
The documentation states that the ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, and then the original table is deleted and the new one is renamed. While ALTER TABLE is executing, the original table is readable by other clients.

So that makes complete sense and is logical. But it then goes on to say,

"Updates and writes to the table are stalled until the new table is ready, and then are automatically redirected to the new table without any failed updates."

I'm hoping somebody can shed some light on '...are stalled until the new table is ready...' I assume MySQL is storing these writes somewhere, either in a memory queue or perhaps on disk but I'm not sure. Actually Where it stores the information in this Gap?

Multiple Updates Per Row In A Multi-table Update
I'm wondering how exactly a multi-table update works. I'm using the following queries.
I expect t to contain (1, 2) and (2, 0), but it contains (1, 1) and (2, 0).
Why? .....

Forwarding Insert/Updates From Slave To Master
Is there a way within MySQL to forward all Inserts/Update requests from the
Slave to the Master, or much I rearchitect my application to carry two
connection; one connection to the Master for updates and the other
connection to the slave for Selects?

Updating Column1 Updates Also Defaultet Column2
I'm using 5.1.11beta and tried to update column1 of a row, which resulted also in an inherent update of column2 (TIMESTAMP(14) default current_timestamp).

To my understanding, an update of one column of a row, should not end up in updating also some other column(s). Even if i use the column2 as predicate in the where clause of my update-query, it ends up changed after the update.

Building A Stored Proc That Updates Using An If Test
I need some help building a sp with an if statement (really need to get a book but hoping someone can help me out right now)

I have a table with the following fields:
User varchar(25)
UserName varchar(50)
PUID varchar(25)
TS TimeStamp

Now what I want to do is make an SP that checks the Timestamp with a timestamp of a in paramater and if they are the same update the record with changes so I will have parameters for the other fields too and if the timestamp in the table is more recent than the parameter have a flag var returned by the sp. Problem is I have no idea how to do this in mysql can someone help me out.

Changed MyISAM To InnoDB - Getting Really Slow UPDATES
I've recently got all inspired and purchased "High Performance SQL" - an O'Reilly's book. Using a slow query log I'd already identified two tables that were causing problems. Both had an equal number of SELECT and UPDATE/DELETE statements being run against them.

This was leading to table-locking problems and after reading about the advantages (and disadvantages) of InnoDB tables versus MyISAM tables I thought that it would be good to convert both tables to InnoDB.

The conversion was carried out using:
ALTER TABLE `table_name` TYPE = INNODB

Scheduling Replication To Avoid Bottle-neck Updates
We have a circular master-slave setup where any one of the 2 servers
can become master at any time (by human decision). The two servers are
placed at geographically different sites. The servers contain à number

of databases which are all replicated both ways.

When we have full usage of one master ~500 inserts/updates per second,
the bandwidth between our sites becomes a significant bottle-neck. This

we can accept at database level not on server level, ie
- if database A on site B has a lag of 30min because of important
activity on database A on site A, it is acceptable.
- if database B on site B has a lag of 30min because of important
activity on database A on site A, it is not acceptable.

Is there a work-around? We never have updates concerning 2 databases in
the same query.

Creating multiple mysql servers at each site could be one, but that
means some 10-50 servers on every physical computer. What side-effects
does that create?

LOAD DATA INFILE, Updates All Other Fields With Nulls.
I am using the following command to update three fields ONLY in the destination table. The infile that I am using was created using:
..........................................................
select code, desc2 , qty_on_hand from plu_tmp
into outfile "c:mp.txt"

..........................................................
LOAD DATA INFILE "c:mp.txt"
REPLACE INTO TABLE `plu` (code, desc2 , qty_on_hand)

I expect to update only the three fields, without touching the other fields.
What happens is that the specified fields are updated, and all the rest of the fields are reset to nulls as they have not been specified.

Is there any way I can use the load inifile to insert / Update just the
specified fields.

Mysql User Profile Update Problem With Country Dropdown Menu Updates
I have trying to resolve this myself for a few days, but despite referencing two books and falling short on finding sample code on the web for this, I would wellcome your assistance to help me resolve this.

All I'm trying to do is to have an edit account form where the user is able to change their country_id field
in the user table, through a dropdown of country names that is stored in a country table with an (id, name, iso) fields. So far I have been able to do this:

1) register a new user and store their country_id field,
2) display the user's country name with all other user info on the "summary info" page,
3) and I have managed to display on the edit page in a single dropdown the users country 'selected' and all other countries.

The only thing that is not working now is when I try to change the country from the dropdown menu on the edit profile page and click update, nothing happens, the users old country selection remains. I have the value="country_id" for both options as you can see in the dropdown, but its not working. I suspect the variables are getting mixed up somehwere, or I'm not calling them in the right way, but I feel like my head is going to explode soon I have included below the code. I look forward to hearing your advice. T

Miro

<?php
if (isset($_POST['submit']) && $_POST['submit'] == "Update") {
$query_update = "UPDATE user SET " .
"first_name = '" . $_POST['first_name'] . "', " .
"last_name = '" . $_POST['last_name'] . "', " .
"nick_name = '" . $_POST['nick_name'] . "', " .
"alt_email = '" . $_POST['alt_email'] . "', " .
"mobile = '" . $_POST['mobile'] . "', " .
"country_id = '" . $_POST['country_id'] . "' " .
"WHERE username = '" . $_SESSION['user_logged'] . "' " .
"AND password = (PASSWORD('" .
$_SESSION['user_password'] . "'))";
$result_update = mysql_query($query_update)
or die(mysql_error());

$query = "SELECT * FROM user " .
"WHERE ............................

Audit Trail Of Updates And Deletes

In my application I need to keep track of all the changes, especially update, and delete changes each record. I need to keep track of who did what changes. Is there any audit trail feature in MySQL 4.1? Or is there a simple data schema, which can accomplish that.

In my application I need to keep track of all the changes, especially
update,

and delete changes each record. I need to keep track of who did
what changes. Is there any audit trail feature in MySQL 4.1? Or is
there a simple data schema, which can accomplish that.