MySQL Forums
Forum List  »  Backup

MySQLDump conditional comments not working
Posted by: D S
Date: September 27, 2012 10:19AM

I am new to mysql coming from heavy microsoft sql server use. A vendor is sending us mysql files generated by MysqlDump 10.13

We set up a mysql installation so we can take their dumped files and restore the db so we have a copy of their db after the process is over.

But I am getting errors on some of the files using MySQL Workbench to execute them. Researching the errors it turns out that our installation has STRICT_TRANS_TABLES set in the SQL_MODE paramater and some of the data fails validation on the table insert statements.

Well, the MySQLDump software auto generates a bunch of conditional comments that modify session variables one of which temporarily turns off strict_trans_tables to allow the insert and then turns strict mode back on when the inserts are done.

Here is the comment

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

As I understand it, this will work on any versions higher than 40101 and we are using 5.5.27 so it should execute the code within the comment buts its not executing it.

I tested it like this.

select @@SQL_MODE;
select @OLD_SQL_MODE;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
select @@SQL_MODE;
select @OLD_SQL_MODE;

Values before conditional comment:

@@SQL_MODE = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
@OLD_SQL_MODE returns NULL

Values after conditional comment supposedly executed:

@@SQL_MODE = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
@OLD_SQL_MODE returns NULL

Nothing changed!

Now if I take the code directly out of the conditional comment like this

select @@SQL_MODE;
select @OLD_SQL_MODE;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
select @@SQL_MODE;
select @OLD_SQL_MODE;


Values before set statement:

@@SQL_MODE = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
@OLD_SQL_MODE returns NULL

Values after set statement:

@@SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"
@OLD_SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

It works!

I have 600 .sql files to import and editing the files to change the comments is not really an option so how can I get my installation of MySQL to actually recognize and execute the auto generated comments from MySQLDUMP?

-Dave

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQLDump conditional comments not working
3823
D S
September 27, 2012 10:19AM
1637
D S
September 28, 2012 02:46PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.