mysqldump wraps all trigger code in comments.

[Marjeta]

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 @SAVE_SQL_MODE= @@SQL_MODE*/;

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

[code green]

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

[Marjeta]

I just found this in the reference manual:
http://dev.mysql.com/doc/refman/5.0/en/comments.html

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?

[code green]

You can have this chunk of code.
It is by no means a refined function.
It just does the job need

///////////////////////////////////////////////////////////////////////
    print '<br>Now remove oneandone version problems ';
    $remove = '/\/\\*[\\s\\S]*?\\*\//';
    $find = 'DELIMITER';
     //Different attempts to remove multiple line comments
    #'(/\*[\w|\W][^\*/]*\*/)';
    #'/\/\*(.|\s)*?\*\//';
    #'/\/\*(?:\w|\s|\.|\n)+\*\//';
 
    if($handle = fopen($dirtyFile,'r'))
    {
        print '<br>Re-opened file';
        $cleanFile = $backupPath.'C'.$filename;
        if($newfile = fopen($cleanFile,'w'))
        {
            print '<br>Opened new file';
            while(!feof($handle))
            {
                $line = fgets($handle);
                if(strpos($line,$find)!==false){
                    continue; # Will jump out of loop
                }
                $line = preg_replace($remove,'',$line);
                if(!strcmp(trim($line),';;')){
                    continue; # Will jump out of loop
               }    
                if(!strcmp(trim($line),';')){
                    continue; # Will jump out of loop
                }
                fwrite($newfile,$line);
            }
            print '<br>Copied clean text across';
 
            if(!fclose($handle)){
                errormessage('Could not close '.$dirtyFile);
                }    
            if(!fclose($newfile)){
                errormessage('Could not close '.$cleanFile);
               }    
            if(!unlink($dirtyFile)) #Delete the previous backup
            {
                errormessage('Could not delete dirtyfile.sql');        
            }        
            if(!rename($cleanFile,$dirtyFile)) 
            {
                errormessage('Could not rename '.$cleanFile);
            }        
        }
    }
    ///////////////////////////////////////////////////////////////////////

[Marjeta]

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

/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */
/*!50003 TRIGGER `t_dobj_ins`
BEFORE INSERT ON `mytable` /* insert trigger for mytable */
FOR EACH ROW BEGIN .../*more code*/ */

should be:

CREATE DEFINER=`root`@`localhost` TRIGGER `t_dobj_ins`
BEFORE INSERT ON `mytable` /* insert trigger for mytable */
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.

[code green]

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

[MosheElisha]

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 */