473,378 Members | 1,439 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

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

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

Similar topics

by: William F. O'Neill | last post by:
Am using SQL Server 2000 on WINXP Pro. Have a requirement to change some Oracle triggers to SQL 2000. I have modied this one Insert Trigger, but get an error when I attempt to compile: CREATE...
by: Christopher Benson-Manica | last post by:
This is intended to be a simple version of the Unix "head" command, i.e. a utility that displays the first n lines of a file. Comments welcomed... #include <cstdlib> #include <iostream>...
by: Tomk | last post by:
I'm using VB.DOC & NDoc for now until Whidbey is released and I'm looking for a way to extract and compile my XML comments for private methods. These tools and I beleive the existing C# doc...
by: Ole Olsen | last post by:
Hi When writing code comments (to e.g. methods) in an Interface, this comments does not seem to be transferred to the implementing class? When ever instantiating a class implementing a...
by: Russell Mangel | last post by:
Is it possible to create code comments in VC++ 2003.NET? I have created a Mixed-mode .dll which will be called from C#.NET or VB.NET. When I add a reference to this .net .dll file using C# or...
by: Mythran | last post by:
I have asked this question here before but was given an answer I didn't like (stubborn I am!). I believe the way my question was interpreted was not as I had hoped...so I am trying again. In...
by: Steve | last post by:
Hi all, I have just installed the latest version of VBCommenter, but from what I can make out it seems it is not useable in VB.net standard. According to the docs you will only get comments...
by: planetthoughtful | last post by:
Hi All, I've written my first piece of practical Python code (included below), and would appreciate some comments. My situation was that I had a directory with a number of subdirectories that...
by: Elioth | last post by:
I am creating my own library (.DLL), I need to know the code to put a comments or Tooltip to my library. I want a Tooltip when I choose any function of my library when I working with, like the...
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.