Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You can trust the database to takes locks where necessary. Only in exceptional circumstances would you override that. –  Andomar Feb 16 '13 at 19:04
    
@Andomar Right. I trust and I also want to run the restore without commenting LOCK TABLES. See edited question. –  David Feb 16 '13 at 19:42
    
Just noted your error message says LOCK TABLE User failed ... shouldn't it be saying LOCK TABLE User_test_importfailed ? Might the dump be trying to do something to the User table? –  Andomar Feb 16 '13 at 19:47
    
@Andomar I too thought it should be User_test_import failed It is actually ERROR 1100 (HY000) at line 60: Table 'User' was not locked with LOCK TABLES I don't understand why the User table must be locked. ?? –  David Feb 16 '13 at 21:14

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
    
Wow. That was easy! Thank you for clarifying and for including the link about comments. –  David Feb 17 '13 at 10:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.