#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0

    Arrow MySQLDump conditional comments not getting executed


    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 global 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

    Code:
    /*!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.

    Code:
    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

    Code:
    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 conditional comments from MySQLDUMP?

    -Dave
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0
    Someone suggested I run this test from the command line so I did. And it works from command line. So the Workbench client i am using is stripping the comments out somehow or ignoring them.

IMN logo majestic logo threadwatch logo seochat tools logo