| Bug #24679 | mysqldump does not write create trigger statements | ||
|---|---|---|---|
| Submitted: | 29 Nov 2006 8:42 | Modified: | 5 Dec 2008 14:15 |
| Reporter: | Kai Sautter (Basic Quality Contributor) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: mysqldump Command-line Client | Severity: | S2 (Serious) |
| Version: | 5.0.22 | OS: | Any |
| Assigned to: | Tatiana Azundris Nuernberg | CPU Architecture: | Any |
| Tags: | qc | ||
[29 Nov 2006 8:42]
Kai Sautter
[29 Nov 2006 9:02]
Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newr version, 5.0.27, and infor about the results. With 5.0.32-BK on Linux I've got: openxs@suse:~/dbs/5.0> bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.0.32-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database bug24679; Query OK, 1 row affected (0.01 sec) mysql> use bug24679; Database changed mysql> create table t1 (c1 int); Query OK, 0 rows affected (0.01 sec) mysql> create trigger t_t1 before insert on t1 for each row select 1 into @a; Query OK, 0 rows affected (0.01 sec) mysql> exit openxs@suse:~/dbs/5.0> bin/mysqldump -uroot bug24679 -- MySQL dump 10.11 -- -- Host: localhost Database: bug24679 -- ------------------------------------------------------ -- Server version 5.0.32-debug /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `t1` -- DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `c1` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `t1` -- LOCK TABLES `t1` WRITE; /*!40000 ALTER TABLE `t1` DISABLE KEYS */; /*!40000 ALTER TABLE `t1` ENABLE KEYS */; UNLOCK TABLES; /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; DELIMITER ;; /*!50003 SET SESSION SQL_MODE="" */;; /*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `t_t1 ` BEFORE INSERT ON `t1` FOR EACH ROW select 1 into @a */;; DELIMITER ; /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2006-11-28 21:27:22 So, trigger is dumped by default.
[30 Dec 2006 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[18 Mar 2008 18:29]
Craig Ward
I have run into an issue with mysqldump and the -triggers switch where the triggers are not always included in the dump. If the mysqldump command is used with an account that may define triggers, it includes them. If the mysqldump command is used with an account that cannot define triggers (can't create tables, views, procedures, etc.) it does not include triggers and issues no error message. (This case with --routines resulted in an error message about missing privileges.) What privilege or access would an account need in order to dump a trigger definition for another definer? Perhaps this bug should be changed to "-triggers doesn't report errors when failing." mysqldump : 10.11 Server : 5.0.51a OS : Mac OS X 10.4.11
[20 Mar 2008 12:37]
Susanne Ebrecht
Verified as described. mysql> select version(); +--------------+ | version() | +--------------+ | 5.0.60-debug | +--------------+ mysql> create table t(id serial, a integer, b integer); mysql> create table t2(id serial, a integer, b integer); mysql> delimiter // mysql> create trigger t_t before insert on t for each row begin insert into t2 set a=new.a, b=new.b; end; // mysql> delimiter ; mysql> insert into t(a,b) values (2,3); Query OK, 1 row affected (0.00 sec) mysql> grant all on mydb.* to testuser@'localhost'; mysql> grant all on mydb.* to testuser@'%'; mysql> /q $ ./bin/mysqldump -u myuser mydb ... DELIMITER ;; /*!50003 SET SESSION SQL_MODE="" */;; /*!50003 CREATE */ /*!50017 DEFINER=`miracee`@`localhost` */ /*!50003 TRIGGER `t_t` BEFORE INSERT ON `t` FOR EACH ROW begin insert into t2 set a=new.a, b=new.b; end */;; DELIMITER ; ... $ ./bin/mysqldump -u testuser mydb the CREATE TRIGGER is missing.
[23 Oct 2008 3:27]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/56858 2709 Tatiana A. Nurnberg 2008-10-23 Bug#24679: mysqldump does not write create trigger statements mysqldump actually does write TRIGGER dumps, but only when permissions suffice. It now prints a warning when they don't to make things clearer.
[23 Oct 2008 4:20]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/56859 2709 Tatiana A. Nurnberg 2008-10-23 Bug#24679: mysqldump does not write create trigger statements mysqldump actually does write TRIGGER dumps, but only when permissions suffice. It now prints a warning when they don't to make things clearer.
[23 Oct 2008 4:36]
Tatiana Azundris Nuernberg
Reviewers: http://dev.mysql.com/doc/refman/5.1/en/information-schema.html "Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA.ROUTINES table), users who have insufficient privileges will see NULL." This more or less coincides with current server behaviour (if we don't have the permissions to list the triggers, we'll see none). This, as we've seen, is not desirable; anything that proofs dumping is a plus; getting correct dumps is imperative. Two patches attached: Patch #1 throws a full error (in mysqld) for SHOW TRIGGERS with insufficient privileges, same as SHOW TABLE would (consistency with other SHOW). Patch #2 throw a warning (in mysqld) and gives an empty list (thus conforming with INFORMATION SCHEMA docs). After initially favouring the clear cut #1, I now gravitate towards the "best of both worlds" #2; #2 is my suggestion. If you really have strong feelings and want #1 instead though, go ahead and approve that. Thanks!
[5 Dec 2008 14:15]
Tatiana Azundris Nuernberg
PeterG also in favour of current behaviour; I consider myself outnumbered. Closing as "not a bug, cf docs." Thanks everyone!
