Page 1 of 1

UNLOCK TABLES causes syntax error

Posted: Wed May 19, 2010 3:10 pm
by hunted
Hello,

I am having a odd issue, where UNLOCK TABLES causes a syntax error (1064) when used from my code. If it is used through phpmyadmin or the mysql client it works fine. I am have mysql version 5.0.77 and PHP version 5.3.2.

I am doing a query with 1 select, followed by 2 updates and 1 insert, and I am locking the table first and unlocking it afterwords. Note I have tried it with both a user with LOCK TABLES privileges as well as root, and again, it works for both of these cases in phpmyadmin and in mysql client.

I isolated the error to being just the UNLOCK TABLES; command.
code that fails:

Code: Select all

<?php

mysql_connect('localhost', 'username', 'password');
mysql_select_db('database');

$query = "LOCK TABLE nav WRITE; UNLOCK TABLES;";
$result = mysql_query($query) or die(mysql_error());

print '<br><br>';
print mysql_num_rows($result) . ' Rows returned<br><br>';
print mysql_affected_rows() . ' Rows Affected<br>';

?>
Output is: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNLOCK TABLES' at line 1

I have read the mysql docs for LOCK TABLES and have found no reason it doesn't work.
Anyone have any idea whats going on here?
Thanks

Re: UNLOCK TABLES causes syntax error

Posted: Wed May 19, 2010 3:11 pm
by AbraCadaver
You can't execute multiple queries at one time with the mysql extension. You might look at the the mysqli extension.

Re: UNLOCK TABLES causes syntax error

Posted: Wed May 19, 2010 3:13 pm
by Eran
You can't issue multiple queries with mysql_query. You can do that using mysqli's multi_query()
I don't see the point though of issuing a lock and releasing it immediately after without doing anything in between.

Re: UNLOCK TABLES causes syntax error

Posted: Wed May 19, 2010 3:21 pm
by hunted
Oh, I didn't realize I couldn't do multiple queries with msyql_query.
That was just an example, I am actually issuing [even] more queries. I will switch to mysqli.

Another question then, when phpmyadmin says its using the msyql extension, how is it issuing multiple queries?

Re: UNLOCK TABLES causes syntax error

Posted: Wed May 19, 2010 3:22 pm
by Eran
probably splitting them by ';' and issuing them one by one

Re: UNLOCK TABLES causes syntax error

Posted: Wed May 19, 2010 3:27 pm
by hunted
Well, now I feel dumb, It's been forever since I actually read the docs for mysql_query. I guess this is the first time I've ran across running multiple queries
Thanks though!

Re: UNLOCK TABLES causes syntax error

Posted: Wed May 19, 2010 3:31 pm
by mikosiko
+1 with Pytrin comments about why are you using LOCK/UNLOCK with nothing in between.... :?:

for a small example of multi-query usage look here viewtopic.php?f=1&p=607218#p607218