UNLOCK TABLES causes syntax error

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
hunted
Forum Newbie
Posts: 3
Joined: Wed May 19, 2010 2:46 pm

UNLOCK TABLES causes syntax error

Post 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
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: UNLOCK TABLES causes syntax error

Post by AbraCadaver »

You can't execute multiple queries at one time with the mysql extension. You might look at the the mysqli extension.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: UNLOCK TABLES causes syntax error

Post 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.
hunted
Forum Newbie
Posts: 3
Joined: Wed May 19, 2010 2:46 pm

Re: UNLOCK TABLES causes syntax error

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: UNLOCK TABLES causes syntax error

Post by Eran »

probably splitting them by ';' and issuing them one by one
hunted
Forum Newbie
Posts: 3
Joined: Wed May 19, 2010 2:46 pm

Re: UNLOCK TABLES causes syntax error

Post 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!
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: UNLOCK TABLES causes syntax error

Post 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
Post Reply