queries are not fully executing

[lorenw]

Hi All,

I have a problem with a high traffic (mysql) script page.
What the script does is archive users that have expired according to time used up. The "sessionaccounting" table can have 1 to thousands of enties (24 columns) for a single user.
What's happening is that sometimes when records are moved the script bails out and doesnt move data from all of the tables.

I suspect that this is caused by concourant hits to the script ie.. the script is archiving a lot of records and the in the middle of archiving the script gets called again.

Here is the code

$q1= "INSERT into asessionaccounting  select * FROM sessionaccounting where UserName = '" . $row['UserName'] . "' ";
$update1 = mysql_query($q1) or die(mysql_error());

// there are seven more similar inser/select queries here
//then the delete (seven more of these)

$qd1= "DELETE FROM sessionaccounting WHERE UserName = '" . $row['UserName'] . "' ";
$updated1 = mysql_query($qd1) or die(mysql_error());

The problem is that sometimes a complete archive does not happen.
Does anyone have any ideas as to what may be happening and more importantly how can it be fixed.

Many Thanks
Loren

[Dan Grossman]

If you want to prevent the tables from changing until your query finishes, then LOCK them

[lorenw]

Thanks Dan,
would it be something like,

$query = "LOCK TABLES sessionaccounting WRITE";
if (!(@ mysql_query ($query, $connection)))
    or die(mysql_error());


$q1= "INSERT into asessionaccounting  select * FROM sessionaccounting where UserName = '" . $row['UserName'] . "' ";
$update1 = mysql_query($q1) 
or die(mysql_error());

$query = "UNLOCK TABLES";
if (!(@ mysql_query ($query, $connection)))
or die(mysql_error());

I have never used LOCK before and a quick G gave me this.

Edit:


Also found this for locking multiple tables which I think is needed.
LOCK TABLES sessionaccounting WRITE, sessionusers WRITE

These are being locked from WRITE, will that also lock them from deleting?
Thanks

[r937]

if "archive" means insert into a different table and then delete from the originating table, why not just flag the appropriate rows as inactive, and don't move them

[lorenw]

Rudy, That is what I am doing.
hmm need to think about that, it is a free radius database. This means that freeradius.so in the OS writes and reads to the database. I do have access to the queries that freeradius.so uses. The problem is that the system generates soo many reports and accounting from the radius database that I can see the entire system breaking without a huge dose of changes.

That is a good idea though and am really considering biting the bullet.

Meanwhile back at the ranch, I did put the LOCK on all radius tables before the while loop
and UNLOCK TABLES after the loop. Everything works and no errors

I hope I am doing it right.

[SpacePhoenix]

What storage engine is the sessionaccounting table using?

[lorenw]

It is MyISAM.

[SpacePhoenix]

You might want to investigate using the InnoDB storage engine for the sessionaccounting table. MyISAM uses table level locking whilst InnoDB uses row level locking. If the table is getting lots of hits, the table level locking of MyISAM is more then likely causing a major bottleneck.

[lorenw]

I did a search for MyISAM vs InnoDB, and sort of understood it. What I found did not mention locking and I will make the changes to InnoDB with the locks and see what happens in the next months. Sounds better than drawing pentagrams on the ground and throwing chiken bones at it

Still need an answer to: do I lock all of the tables before the while loop or lock each table within the loop?

My thought is all but waiting input. This will be a live environment.

Thanks (really)