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