#At file:///misc/mysql/forest/24679_/50-24679_/
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.
modified:
client/mysqldump.c
mysql-test/r/information_schema.result
mysql-test/r/mysqldump.result
mysql-test/t/mysqldump.test
sql/sql_show.cc
per-file messages:
client/mysqldump.c
If we don't have sufficient privileges to dump table's TRIGGERs,
tell the user rather than fail silently!
mysql-test/r/information_schema.result
Trying to dump triggers when underprivileged now throws a warning.
mysql-test/r/mysqldump.result
Show that we throw a warning when trying to dump (existing!) triggers
when not having proper privileges.
mysql-test/t/mysqldump.test
Show that we throw a warning when trying to dump (existing!) triggers
when not having proper privileges.
sql/sql_show.cc
When triggers exist but user does not have proper permissions to dump them,
throw a warning.
=== modified file 'client/mysqldump.c'
--- a/client/mysqldump.c 2008-09-11 05:46:43 +0000
+++ b/client/mysqldump.c 2008-10-23 04:15:38 +0000
@@ -2183,6 +2183,10 @@ static void dump_triggers_for_table(char
"SHOW TRIGGERS LIKE %s",
quote_for_like(table, name_buff));
+ /*
+ If we lack privileges, we'll get a warning, not an error.
+ We'll handle those later.
+ */
if (mysql_query_with_error_report(mysql, &result, query_buff))
{
if (path)
@@ -2246,6 +2250,20 @@ static void dump_triggers_for_table(char
original value
*/
opt_compatible_mode=old_opt_compatible_mode;
+
+ if (mysql_warning_count(mysql))
+ {
+ if (!mysql_query_with_error_report(mysql, &result, "show warnings"))
+ {
+ if (mysql_num_rows(result))
+ {
+ while ((row= mysql_fetch_row(result)))
+ fprintf(sql_file, "\n-- %s: %s %s - %s\n\n",
+ query_buff, row[0], row[1], row[2]);
+ }
+ mysql_free_result(result);
+ }
+ }
DBUG_VOID_RETURN;
}
=== modified file 'mysql-test/r/information_schema.result'
--- a/mysql-test/r/information_schema.result 2008-03-25 15:44:27 +0000
+++ b/mysql-test/r/information_schema.result 2008-10-23 04:15:38 +0000
@@ -1349,9 +1349,13 @@ column_name
b
show triggers;
Trigger Event Table Statement Timing Created sql_mode Definer
+Warnings:
+Warning 1227 Access denied; you need the SUPER privilege for this operation
select trigger_name from information_schema.triggers
where event_object_table='t1';
trigger_name
+Warnings:
+Warning 1227 Access denied; you need the SUPER privilege for this operation
drop user mysqltest_1@localhost;
drop database mysqltest;
create table t1 (
=== modified file 'mysql-test/r/mysqldump.result'
--- a/mysql-test/r/mysqldump.result 2008-10-01 09:41:13 +0000
+++ b/mysql-test/r/mysqldump.result 2008-10-23 04:15:38 +0000
@@ -3572,5 +3572,85 @@ DROP TABLE t1,t2;
-- Dump completed on DATE
SET @@GLOBAL.CONCURRENT_INSERT = @OLD_CONCURRENT_INSERT;
#
+# Bug#24679: mysqldump does not write create trigger statements
+#
+CREATE DATABASE mysqldump_test_db;
+GRANT ALL PRIVILEGES ON mysqldump_test_db.* TO user1;
+GRANT SUPER ON *.* TO user1;
+GRANT ALL PRIVILEGES ON mysqldump_test_db.* TO user2;
+CREATE TABLE t1(id SERIAL, a INTEGER, b INTEGER);
+CREATE TABLE t2(id SERIAL, a INTEGER, b INTEGER);
+CREATE TRIGGER t1_t
+BEFORE INSERT ON t1
+FOR EACH ROW
+BEGIN
+INSERT INTO t2 SET a=new.a, b=new.b;
+END |
+INSERT INTO t1(a,b) VALUES (2,3);
+
+lacking privileges to dump triggers
+SET @saved_cs_client = @@character_set_client;
+SET character_set_client = utf8;
+CREATE TABLE `t1` (
+ `id` bigint(20) unsigned NOT NULL auto_increment,
+ `a` int(11) default NULL,
+ `b` int(11) default NULL,
+ UNIQUE KEY `id` (`id`)
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
+SET character_set_client = @saved_cs_client;
+INSERT INTO `t1` VALUES (1,2,3);
+
+-- SHOW TRIGGERS LIKE 't1': Warning 1227 - Access denied; you need the SUPER privilege for this operation
+
+SET @saved_cs_client = @@character_set_client;
+SET character_set_client = utf8;
+CREATE TABLE `t2` (
+ `id` bigint(20) unsigned NOT NULL auto_increment,
+ `a` int(11) default NULL,
+ `b` int(11) default NULL,
+ UNIQUE KEY `id` (`id`)
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
+SET character_set_client = @saved_cs_client;
+INSERT INTO `t2` VALUES (1,2,3);
+
+have privileges to dump triggers
+SET @saved_cs_client = @@character_set_client;
+SET character_set_client = utf8;
+CREATE TABLE `t1` (
+ `id` bigint(20) unsigned NOT NULL auto_increment,
+ `a` int(11) default NULL,
+ `b` int(11) default NULL,
+ UNIQUE KEY `id` (`id`)
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
+SET character_set_client = @saved_cs_client;
+INSERT INTO `t1` VALUES (1,2,3);
+
+/*!50003 SET @SAVE_SQL_MODE=@@SQL_MODE*/;
+
+DELIMITER ;;
+/*!50003 SET SESSION SQL_MODE="" */;;
+/*!50003 CREATE */ /*!50017 DEFINER=`user1`@`%` */ /*!50003 TRIGGER `t1_t` BEFORE INSERT ON `t1` FOR EACH ROW BEGIN
+INSERT INTO t2 SET a=new.a, b=new.b;
+END */;;
+
+DELIMITER ;
+/*!50003 SET SESSION SQL_MODE=@SAVE_SQL_MODE*/;
+SET @saved_cs_client = @@character_set_client;
+SET character_set_client = utf8;
+CREATE TABLE `t2` (
+ `id` bigint(20) unsigned NOT NULL auto_increment,
+ `a` int(11) default NULL,
+ `b` int(11) default NULL,
+ UNIQUE KEY `id` (`id`)
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
+SET character_set_client = @saved_cs_client;
+INSERT INTO `t2` VALUES (1,2,3);
+DROP TRIGGER t1_t;
+DROP TABLE t1;
+DROP TABLE t2;
+DROP USER user1;
+DROP USER user2;
+DROP DATABASE mysqldump_test_db;
+#
# End of 5.0 tests
#
=== modified file 'mysql-test/t/mysqldump.test'
--- a/mysql-test/t/mysqldump.test 2008-09-15 19:34:39 +0000
+++ b/mysql-test/t/mysqldump.test 2008-10-23 04:15:38 +0000
@@ -1605,6 +1605,51 @@ DROP TABLE t1,t2;
# We reset concurrent_inserts value to whatever it was at the start of the test
SET @@GLOBAL.CONCURRENT_INSERT = @OLD_CONCURRENT_INSERT;
+--echo #
+--echo # Bug#24679: mysqldump does not write create trigger statements
+--echo #
+
+CREATE DATABASE mysqldump_test_db;
+
+GRANT ALL PRIVILEGES ON mysqldump_test_db.* TO user1;
+GRANT SUPER ON *.* TO user1;
+GRANT ALL PRIVILEGES ON mysqldump_test_db.* TO user2;
+
+connect (user24679,localhost,user1,,mysqldump_test_db,$MASTER_MYPORT,$MASTER_MYSOCK);
+connection user24679;
+
+CREATE TABLE t1(id SERIAL, a INTEGER, b INTEGER);
+CREATE TABLE t2(id SERIAL, a INTEGER, b INTEGER);
+DELIMITER |;
+CREATE TRIGGER t1_t
+ BEFORE INSERT ON t1
+ FOR EACH ROW
+ BEGIN
+ INSERT INTO t2 SET a=new.a, b=new.b;
+ END |
+DELIMITER ;|
+INSERT INTO t1(a,b) VALUES (2,3);
+
+# yes, --triggers is redundant for now.
+--echo
+--echo lacking privileges to dump triggers
+--exec $MYSQL_DUMP -f --compact --user=user2 --password= -h 127.0.0.1 -P $MASTER_MYPORT --triggers mysqldump_test_db
+
+--echo
+--echo have privileges to dump triggers
+--exec $MYSQL_DUMP -f --compact --user=user1 --password= -h 127.0.0.1 -P $MASTER_MYPORT --triggers mysqldump_test_db
+
+DROP TRIGGER t1_t;
+
+DROP TABLE t1;
+DROP TABLE t2;
+
+connection default;
+
+DROP USER user1;
+DROP USER user2;
+
+DROP DATABASE mysqldump_test_db;
--echo #
--echo # End of 5.0 tests
=== modified file 'sql/sql_show.cc'
--- a/sql/sql_show.cc 2008-10-15 10:06:44 +0000
+++ b/sql/sql_show.cc 2008-10-23 04:15:38 +0000
@@ -3402,7 +3402,13 @@ static int get_schema_triggers_record(TH
#ifndef NO_EMBEDDED_ACCESS_CHECKS
if (!(thd->security_ctx->master_access & SUPER_ACL))
+ {
+ push_warning_printf(thd,MYSQL_ERROR::WARN_LEVEL_WARN,
+ ER_SPECIFIC_ACCESS_DENIED_ERROR,
+ ER(ER_SPECIFIC_ACCESS_DENIED_ERROR),
+ "SUPER");
goto ret;
+ }
#endif
for (event= 0; event < (int)TRG_EVENT_MAX; event++)
| Thread |
|---|
| • bzr commit into mysql-5.0-bugteam branch (azundris:2709) Bug#24679 | Tatiana A. Nurnberg | 23 Oct |
