Simple mysql database lock

[smidgen11]

Hi,

 

Im trying write a simple command to lock my database but Im having some trouble.  Im using

 

[code=php:0]
include("sqlconnect.php");

mysql_query("FLUSH TABLES WITH READ LOCK");
echo "db is locked";

[/code]

 

This is not working however. I can run "FLUSH TABLES WITH READ LOCK" from the mysql cli directly and get it to work but not from a form action html that calls my file.

 

Just to test my syntax I threw

mysql_query("FLUSH TABLES WITH READ LOCK");

in front of one of my insert mysql queries and the database DOES lock. I cant figure out why I cant get this to work on its own from a single file. Any ideas?

 

[hakimserwa]

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

[smidgen11]

I did see that site and have followed the correct syntax to get it to work from inside my other php sql files. I cant figure out why FLUSH TABLES WITH READ LOCK will not work on its own. Does mysql_query("FLUSH TABLES WITH READ LOCK") prevent proceeding mysql queries from running or something? 

[DavidAM]

Are you saying that the only thing in the PHP script is the Lock query? If so, then this quote from the page linked by hakimserwa applies:

 

If the connection for a client session terminates, whether normally or abnormally, the server implicitly releases all table locks held by the session

 

When the script ends, all database connections are closed. So the locks are released.

 

Locks belong to the session/connection that acquires them. If you could lock the tables and go away, then how would they ever get unlocked?

[smidgen11]

Thanks that is exactly what is happening. I was hoping it just plain unlocks the table for good until unlocked. To unlock the table I was going to write a script with an html form action button to simply run the UNLOCK TABLES query. Is there anyway to do a simple lock the database/unlock the database that does not get affected but sessions?

 

If not, Im trying to think of another way to simply stop my php mysql scripts from executing if a user attempts to run them when the lock button is pushed. Maybe changing to password variable in my sqlconnect file or something.

[Christian F.]

Why not make it simple, and have the "lock" script make a file on the server. Then rewrite your database interface to check for this file, and refuse all (unwanted) operations if it exists?

[smidgen11]

my work around was to run a bash script to execute instead of generating a new connection id to mysql via php. Im using flush tables with read lock and putting mysql to sleep so that nothing can write only read. NOTE: i have this at a global level because i am not using the box for anything else. all other tables and databases will lock up as well. 

 

here is what I ended up doing.

 

lock button:

exec("bash /var/www/html/path/mysqllock.sh");

 

bash for mysqllock:

#! /bin/bash
/usr/bin/mysql -u root -pX -e "FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)"

 

Unlock:

  include("sqlconnect.php");
  
  //Query the mysql processlist and kill all process ids found including the sleep process id
  $result = mysql_query("SHOW FULL PROCESSLIST");
  while ($row=mysql_fetch_array($result)) 
    {
      $process_id=$row["Id"];
      if ($row["Time"] > 2 ) 
    {
          $sql="KILL $process_id";
          mysql_query($sql);
        }
  echo "<meta http-equiv=\"refresh\" content=\"0;URL=index.php\">";
    }

 

Im not saying this is the proper way to do it, but this is a very simple way to accomplish what I needed, which was to prevent users from writing to the db when i hit lock and allow when i hit unlock.