| Bug #12153 | LOCK TABLES with mysql_query() works wrong | ||
|---|---|---|---|
| Submitted: | 25 Jul 2005 14:10 | Modified: | 2 Aug 2005 15:34 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.0.12,4.0.13,4.0.14 | OS: | Linux (Linux Fedora Core 4) |
| Assigned to: | CPU Architecture: | Any | |
[31 Jul 2005 19:50]
Jorge del Conde
I was able to reproduce this w/4.0.25:
jorge-jorge/mysql_code> mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.25
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database test_db;
Query OK, 1 row affected (0.02 sec)
mysql> use test_db;
Database changed
mysql> create table test_table_1 (a int);
Query OK, 0 rows affected (0.00 sec)
mysql> create table test_table_2 (a int);
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
jorge-jorge/mysql_code> ./main
Connected with the Server: 4.0.25
3-ERROR: Table 'test_table_2' was not locked with LOCK TABLES
-- main.c:
int main()
{
MYSQL *MySQL;
MYSQL_RES * res ;
int i;
int NUM = 1000000;
char qry[100];
if ((MySQL = mysql_init((MYSQL*) 0)) &&
mysql_real_connect( MySQL,"localhost","root", NULL, "test_db", 3306, NULL,0))
printf("Connected with the Server: %s\n", mysql_get_server_info(MySQL));
else
{
printf("Failed to connect with the server\n");
mysql_close( MySQL );
return;
}
if (mysql_query(MySQL, "LOCK TABLES `test_table_1` WRITE") != 0)
printf ("1-ERROR: %s\n", mysql_error(MySQL));
if (mysql_query(MySQL, "INSERT INTO `test_table_1` (`a`) VALUES (1)") != 0)
printf ("2-ERROR: %s\n", mysql_error(MySQL));
if (mysql_query(MySQL, "INSERT INTO `test_table_2` (`a`) VALUES (1)") != 0)
printf ("3-ERROR: %s\n", mysql_error(MySQL));
sleep(100);
if (mysql_query(MySQL, "UNLOCK TABLES") != 0)
printf ("4-ERROR: %s\n", mysql_error(MySQL));
mysql_close( MySQL );
return 0;
}
[1 Aug 2005 14:00]
[ name withheld ]
Yes, you can reproduce it. And it must work so, you cannot use tables without lock, during lock session. It's rule of MySQL locking. But my problem, that in real time from PHP env., during this LOCK I can access this tables, from another threads for READ/WRITE actions. So I cannot LOCK tables. It works ALL like it must, but during lock i can access locked table from other threads.
[2 Aug 2005 10:41]
Andrey Hristov
Tried with PHP 5.1.0-b3 but because this relies on the mysql extension the result should not be different with 5.0.x . MySQL 5.0.11-beta. Changed the table to have datetime field and put now(). one can see that after lock table xxx write the second script waits and even when trying to do a SELECT from mysql cmdline client - the lock is respected.
[2 Aug 2005 10:51]
Andrey Hristov
Can't reproduce also with MySQL 4.1.12
Description: When I'am locking MySQL ISAM table from PHP env, all other threads still can use this table for READ/WRITE actions. But I didn't get from mysql_error() any message, and even cannot access other tables, untill they not locked too. So it's seems like PHP really thinks that MySQL locked. From MySQL console I can lock tables succesfuly, and after this lock even PHP cannot access this table. How to repeat: mysql_pconnect("localhost","root",""); mysql_select_db("test_db"); mysql_query("LOCK TABLES `test_table_1` WRITE"); mysql_query("INSERT INTO `test_table_1` (`a`) VALUES (1)"); // returns OK mysql_query("INSERT INTO `test_table_2` (`a`) VALUES (1)"); // Table was not locked with LOCK TABLES sleep(100); mysql_query("UNLOCK TABLES"); Suggested fix: with this code I want to show you that all works fine, from side of thread what lock MySQL table. But really nothing is locked, during this 100 seconds I can edit/read test_table_1, without any problems from other threads. If I will even remove UNLOCK TABLES command and will use presistent connection, I will not hang up system. Because nothing is locked. I tried it on PHP v 4.4.0/5.0.3/snapshots, CLI mode/Apache 2.0.54 compiled as worker/prefork, mysql v 4.1.12,13,14. And everywhere the same picture. I tried to compile PHP in different ways, from --disable-all + some only important keys, to full configs. Also tried to use budle MySQL libraries and what install on system. But all the same. Hope on your fast bug_fix.