export DB or tables with triggers?
pweil
Rowan Beentje
The SQL export should include triggers - I've just confirmed that with a test export. If you open up the exported SQL file, can you confirm you're not seeing any block like:
DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `foo` ... */;;
DELIMITER ;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
Thanks,
Rowan
On 22 Nov 2011, at 16:36, pweil wrote:
> Doing an SQL export/dump leaves out table triggers. Is there any way to include triggers in an export or dump?
>
> --
> You received this message because you are subscribed to the Google Groups "Sequel Pro" group.
> To view this discussion on the web visit https://groups.google.com/d/msg/sequel-pro/-/WQ9r7b2AcgAJ.
> To post to this group, send email to seque...@googlegroups.com.
> To unsubscribe from this group, send email to sequel-pro+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sequel-pro?hl=en.
pweil
and the trigger was missing. What's the explanation for it's being
commented out in the dump? (I see other stuff that is also commented
out, such as
/*!40000 ALTER TABLE `projects_users` DISABLE KEYS */;
Thanks.
On Nov 22, 10:46 am, Rowan Beentje <ro...@beent.je> wrote:
> Hi pweil,
>
> The SQL export should include triggers - I've just confirmed that with a test export. If you open up the exported SQL file, can you confirm you're not seeing any block like:
>
> DELIMITER ;;
> /*!50003 SET SESSION SQL_MODE="" */;;
> /*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `foo` ... */;;
> DELIMITER ;
> /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
>
> Thanks,
> Rowan
>
> On 22 Nov 2011, at 16:36, pweil wrote:
>
>
>
>
>
>
>
> > Doing an SQL export/dump leaves out table triggers. Is there any way to include triggers in an export or dump?
>
> > --
> > You received this message because you are subscribed to the Google Groups "Sequel Pro" group.
> > To view this discussion on the web visithttps://groups.google.com/d/msg/sequel-pro/-/WQ9r7b2AcgAJ.
Rowan Beentje
That specific comment syntax - with the exclamation mark and number - is a special MySQL comment syntax which means "only execute this on MySQL x.y.z or later". So the trigger statements check for MySQL 5.0.3, which is the first version supporting triggers, and the DISABLE KEYS statement requires 4.0.0.
This allows exports to be transferred across MySQL versions without throwing errors.
Were you importing to a MySQL 4 server? If not, we'll have to check this further :)
Rowan
Jean-Yves
issues wirth triggers not surviving the round trip out of and back
into MySQL via SequelPro.
In the end, I resorted to recreating the disappearing triggers in
MySQL Workbench, dumping the DB form there and only then reimporting
into SequelPro. After which point all seems to have resolved itself -
I can export and reimport my db (which has ~20 triggers, 5 stored
functions and ~80 stored procedures) all without problem.
Mind you, I don't think that I've actually changed any triggers since
then (stored procs and functions certainly, on a daily basis, and this
was about 3 weeks ago).
So, it's just possible that pweil is getting the same problem I had.
Unfortunately, I cannot supply the code as it is commercially
sensitive and my client would not appreciate me sharing it with the
world ;-)
SP 0.9.9.1, MySQL 5.1.57-log (from MacPorts), OS X 10.6.8
Rowan Beentje
That's very interesting information. So you initially created the triggers in Sequel Pro, after which they couldn't be exported and imported correctly; you then recreated them in MySQL Workbench, exported from there, re-imported, and now Sequel Pro both exports and imports them correctly as well?
I appreciate the commercial sensitivity, but it would be great if you still have one of the old (broken) SQL dumps, could check it for a trigger statement, find the same statement in the working dumps, and let us know the differences :)
If not, I'll add this as a bug to our tracker for investigation.
Rowan
pweil
MySQL 5x server.
Jean-Yves
I'll see what I can dig out. It should all be in SVN so I will have
the code. Actually from memory the triggers that kept disappearing
were quite simple, I think they just updated a DATETIME column, so
that code won't be sensitive. I probably can't get anything out to
you until next week though, as am in the middle of major deploy to our
staging server.
One thing I noticed:
Just today I wanted another of these simple update timestamp before
insert triggers for a new table. I went to the def of an existing one
and just copied the code, then cancelled out of the dialogue.
I went to the new table, clicked + to add the trigger, gave it a new
name and pasted the trigger code.
The new trigger was created, but the one I had copied the code from
was gone!
Now I don't kow whether it was really gone or whether SP just was not
displaying it (I forgot to go to Workbench to check). I simply
recreated it and all was fine.
Not sure what this proves, if anything, but maybe something to try
yourself and it may be connected?
UPDATE: I can confirm that I can recreate this. I think it is because
if you open another trigger first, the trigger details are stored in
memory so when you go to create the new one all is in place (name +
code). When you change the name, I guess what the underlying code is
doing is droppiung the trigger with the old stored name and replacing
it with the one with the new stored name, since it is correctly
assuming that as MySQL can only have 1 trigger of each type per table,
what you really want is a drop and create, not a create (ie it is not
checking what table the trigger is being based on).
Hope this helps to some extent.
Jean-Yves
Here's a sample of the very basic triggers that were causing the
issues:
DELIMITER ;;
CREATE DEFINER=`mysqladmin`@`localhost` TRIGGER
`feed_snapshot_before_insert` BEFORE INSERT ON `feed_snapshot` FOR
EACH ROW BEGIN
-- Set the creation date
SET NEW.created = NOW();
END ;;
DELIMITER ;
However, I doubt whether the actual trigger code is the problem, as it
really is basic.
Is it possible that the not-saving of triggers may have been as a
result of the bug I mention in the previous post, and that Sequel
Pro's internal triggers collection was simply out of synch somehow
when exporting?
