470,851 Members | 1,125 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,851 developers. It's quick & easy.

mysqldump wraps all trigger code in comments.

I was trying to compare a particular trigger on multiple servers.

First I tried phpMyAdmin to script the trigger code, which unfortunately only worked on one server that has newer version of phpMyAdmin...

Then I used mysqldump, which scripted trigger code on all the servers, bur with comments around all the trigger related code:

/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `t_dobj_ins` BEFORE INSERT ON `mytable` FOR EACH ROW BEGIN
phpMyAdmine scripted trigger code without comments. Why are those comments there?

I searched thru documents tion on MySQL.com website and found nothing.

Searching for "mysqldump trigger comment" on google triggered lots of hits on bugs.mysql.com, but I found no explanation there. Bugs were about "DEFINER" clause missing, or no space between "ROW" and "BEGIN", etc. There were code samples with those funny comments, but no explanation why they are there and no questions about them.

I tried all the mysqldup options, and I couldn't get rid of comments. How does phpMyAdmin manage to get rid of those comments?

And can I learn more about those comments, such as why are they there in the first place? Is there any documentation anywhere about them?
Sep 17 '09 #1
6 11449
code green
1,726 Expert 1GB
I think they are there for version control.
Not sure what I mean by that but building a MySQL DB in older versions
from mysqldump fails because of the triggers.

Trouble is the commenting out causes failure also.
I update a older version database from MySQL dump and had to write a script that removed the comments and the DELIMITER words also
Sep 18 '09 #2
I just found this in the reference manual:

This page explains, as you said, that it's related to the version.

I'm still wondering how to get rid of those comments. We have no intentions of going back to 4.* or even 3.*, and the comments make the dump hard to read.
And even if we ever want to go back, I'd prefer to get the error messages, so I could redesign the parts that stop working.

The documentation on mysqldump makes no reference of those comments at all.

So, is there maybe an undocumented option that suppresses them?

Or do I have to write a script that will remove them??? In that case, has anyone already written a script like that so I don't reinvent the wheel?
Sep 18 '09 #3
code green
1,726 Expert 1GB
You can have this chunk of code.
It is by no means a refined function.
It just does the job need
Expand|Select|Wrap|Line Numbers
  1. ///////////////////////////////////////////////////////////////////////
  2.     print '<br>Now remove oneandone version problems ';
  3.     $remove = '/\/\\*[\\s\\S]*?\\*\//';
  4.     $find = 'DELIMITER';
  5.      //Different attempts to remove multiple line comments
  6.     #'(/\*[\w|\W][^\*/]*\*/)';
  7.     #'/\/\*(.|\s)*?\*\//';
  8.     #'/\/\*(?:\w|\s|\.|\n)+\*\//';
  10.     if($handle = fopen($dirtyFile,'r'))
  11.     {
  12.         print '<br>Re-opened file';
  13.         $cleanFile = $backupPath.'C'.$filename;
  14.         if($newfile = fopen($cleanFile,'w'))
  15.         {
  16.             print '<br>Opened new file';
  17.             while(!feof($handle))
  18.             {
  19.                 $line = fgets($handle);
  20.                 if(strpos($line,$find)!==false){
  21.                     continue; # Will jump out of loop
  22.                 }
  23.                 $line = preg_replace($remove,'',$line);
  24.                 if(!strcmp(trim($line),';;')){
  25.                     continue; # Will jump out of loop
  26.                }    
  27.                 if(!strcmp(trim($line),';')){
  28.                     continue; # Will jump out of loop
  29.                 }
  30.                 fwrite($newfile,$line);
  31.             }
  32.             print '<br>Copied clean text across';
  34.             if(!fclose($handle)){
  35.                 errormessage('Could not close '.$dirtyFile);
  36.                 }    
  37.             if(!fclose($newfile)){
  38.                 errormessage('Could not close '.$cleanFile);
  39.                }    
  40.             if(!unlink($dirtyFile)) #Delete the previous backup
  41.             {
  42.                 errormessage('Could not delete dirtyfile.sql');        
  43.             }        
  44.             if(!rename($cleanFile,$dirtyFile)) 
  45.             {
  46.                 errormessage('Could not rename '.$cleanFile);
  47.             }        
  48.         }
  49.     }
  50.     ///////////////////////////////////////////////////////////////////////
Sep 21 '09 #4
@code green
Thanks for the code. Though it's not what I need. Your code removes the comments completely, while I'd just like to remove the comment marks and keep the code iside comment. For example:
Expand|Select|Wrap|Line Numbers
  1. /*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */
  2. /*!50003 TRIGGER `t_dobj_ins`
  3. BEFORE INSERT ON `mytable` /* insert trigger for mytable */
  4. FOR EACH ROW BEGIN .../*more code*/ */
should be:
Expand|Select|Wrap|Line Numbers
  1. CREATE DEFINER=`root`@`localhost` TRIGGER `t_dobj_ins`
  2. BEFORE INSERT ON `mytable` /* insert trigger for mytable */
  3. FOR EACH ROW BEGIN .../*more code*/ 
This takes a little more work than just a regular expression. I wrote a simple parser in PHP which has 163 lines, and does what I want, but only if comments aren't nested. I need to dig out and dust off my C/Lex/Yacc and do the real thing...

In any case, it seems funny I have to put extra work to remove something that needed extra work to be inserted...

I have tried all the options and none of them seems to have any effect on the /*!ddddd ... */ comments.
Sep 22 '09 #5
code green
1,726 Expert 1GB
Just check that the backup/rebuild works with the comments removed.
I have had further problems from dumps with triggers where DROP TRIGGER IF EXISTS was not present.
Maybe only DROP TRIGGER which caused the query to fail if it did not already exist.or nothing so the query failed because the trigger already existed.

I have been tempted to write my own dump routine
Sep 22 '09 #6
These are MySQL-specific code comments. Comments with numbers indicate the minimal version of the MySQL server that knows to handle the commented section.

For example, stored procedures did not exist before version 5.0.3 . So all of the stored procedure will be enclosed with comments that look like

/*!50003 */
Nov 28 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by William F. O'Neill | last post: by
13 posts views Thread by Christopher Benson-Manica | last post: by
1 post views Thread by Tomk | last post: by
2 posts views Thread by Ole Olsen | last post: by
1 post views Thread by Russell Mangel | last post: by
2 posts views Thread by Mythran | last post: by
6 posts views Thread by planetthoughtful | last post: by
4 posts views Thread by Elioth | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.