Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2012
    Posts
    3

    Arrow Unanswered: 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
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I am not sure how this is setup or where you are running the files from but I have run a test with my version 5.5.9 and this is working correctly. If there is more information you can provide about how you are running it and from where?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Sep 2012
    Posts
    3
    I am using MySQL Workbench 5.2.43 CE Rev 9869

    I just go to new query window and run the select commands I tried in my original post.

    So you're thinking it's a server configuration issue?

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I have run the same commands from Linux command line

    mysql -u user -ppassword < script.sql

    And this worked. I am just wondering whether MySQL Workbench is doing something i.e. stripping out comment lines rather than passing them on. Let me run a quick test here to the same database that it did work on.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I am running a version of MySQL Workbench on Mac. Are you running on Windows? Would it be possible to execute the same script but using the command line version to verify that it is not MySQL Workbench that is stripping out the comments?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Sep 2012
    Posts
    3
    I am running on windows 7 enterprise 64 bit.

    I take my select statements and save them to test.sql and then navigate to the workbench folder and find the mysql.exe

    When I run the command as you suggested

    Code:
    C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE>mysql -h hostname -u user -v -ppassword < test.sql
    It returns:

    --------------
    select @@SQL_MODE
    --------------

    @@SQL_MODE
    STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION
    --------------
    select @OLD_SQL_MODE
    --------------

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

    --------------
    select @@SQL_MODE
    --------------

    @@SQL_MODE
    NO_AUTO_VALUE_ON_ZERO
    --------------
    select @OLD_SQL_MODE
    --------------

    @OLD_SQL_MODE
    STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION

    It seems to work fine from the command line. So Workbench is doing something to the comments. That was a good suggestion to rule out the server being the cause.

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    When working on problems like this you have to rule out things one by one. I had not experience problems at all using mysql command line and this is designed to handle conditional comments.

    Anyway good to know that you can move forward with this!!
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •