Mysql – Problem with (not) Locking Tables during Restore

MySQLmysqldumpPHP

First time here in DBA. I'm working to create a simple and strong database backup plan. My solution that is already working to create a gzipped, encrypted backup is:

$string = "mysqldump -u$db_user -p$db_password $database $table | gzip | openssl enc -aes-256-cbc -salt -pass pass:$ePass > " . $path . $table . '_' . $d . "_en.sql.gz";
$result = shell_exec ( $string );

I am trying to test the dump and my programming (head to toe) by importing it into another another table. The table User_test_import below doesn't exist in the operational and live site.
The dump is creating as an example (after modification to test import):

-- MySQL dump 10.11
--
-- Host: localhost    Database: accoun3_flifo
<omitted>
--
-- Table structure for table `User`
--

DROP TABLE IF EXISTS `User_test_import`;
CREATE TABLE `User_test_import` (
   <omitted>
);
--
-- Dumping data for table `User`
--

<Line in question>
LOCK TABLES `User_test_import` WRITE;               /*  Line 59  */
/*!40000 ALTER TABLE `User` DISABLE KEYS */;
INSERT INTO `User_test_import` VALUES ( ... <omitted> ...
);

The third line up had to be 'commented' in order for the import to work using:

$import = 'User_5.sql.gz';
$string = "gunzip < $path$import 2>&1 | mysql -u$db_user -p$db_password $database 2>&1";

From the command line the above reports ERROR 1100 (HY000) at line 60: Table 'User' was not locked with LOCK TABLES User is an existing table. Line 59 is the

The question then is, how do I use the dump with it's LOCK TABLES statement? Thanks.

Edit Question:
Why is TABLE User was not locked with LOCK TABLES occurring? How can it be prevented?

Best Answer

The reason the server is complaining that 'User' was not locked is because of this:

/*!40000 ALTER TABLE `User` DISABLE KEYS */;

That looks like a comment, but it isn't a comment. MySQL has a special case for comment parsing for backward-compatibility and cross-compatibility.

Comments in this style are processed as if they weren't comments at all, if the server's version is greater than or equal to the number shown, so in this case, any version of MySQL Server 4.0.0 or greater will execute it and any prior version won't.

So, the restore is asking the server to ALTER a table that isn't locked. You can only do that when your session has no tables locked at all.

If you change that table name to your test table name, the restore should work.

In similar style, if there's an exclamation point in the first position followed by a space, it will be parsed as if not a comment by all versions of MySQL.

Related Question