Merge bk-internal.mysql.com:/home/bk/mysql-5.1

into  bodhi.local:/opt/local/work/mysql-5.1-runtime


include/my_global.h:
  Auto merged
mysql-test/mysql-test-run.pl:
  Auto merged
mysql-test/r/events_scheduling.result:
  Auto merged
BitKeeper/deleted/.del-init_db.sql~a77d572c39d5a1f8:
  Auto merged
BitKeeper/deleted/.del-mysql_create_system_tables.sh:
  Auto merged
mysql-test/r/query_cache.result:
  Auto merged
mysql-test/r/view.result:
  Auto merged
mysql-test/t/events_scheduling.test:
  Auto merged
mysql-test/t/grant_cache.test:
  Auto merged
mysql-test/t/query_cache.test:
  Auto merged
mysql-test/t/view.test:
  Auto merged
scripts/mysql_system_tables_fix.sql:
  Auto merged
sql/event_db_repository.cc:
  Auto merged
sql/event_queue.cc:
  Auto merged
sql/field.cc:
  Auto merged
sql/handler.cc:
  Auto merged
sql/handler.h:
  Auto merged
sql/item.cc:
  Auto merged
sql/item.h:
  Auto merged
sql/item_func.cc:
  Auto merged
sql/item_subselect.cc:
  Auto merged
sql/log_event.cc:
  Auto merged
sql/mysql_priv.h:
  Auto merged
sql/mysqld.cc:
  Auto merged
sql/set_var.cc:
  Auto merged
sql/sp.cc:
  Auto merged
sql/sp_head.cc:
  Auto merged
sql/sp_head.h:
  Auto merged
sql/sql_base.cc:
  Auto merged
sql/sql_class.cc:
  Auto merged
sql/sql_class.h:
  Auto merged
sql/sql_help.cc:
  Auto merged
sql/sql_lex.cc:
  Auto merged
sql/sql_lex.h:
  Auto merged
sql/sql_parse.cc:
  Auto merged
sql/sql_prepare.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/sql_show.cc:
  Auto merged
sql/sql_table.cc:
  Auto merged
sql/sql_yacc.yy:
  Auto merged
sql/table.cc:
  Auto merged
sql/share/errmsg.txt:
  Auto merged
sql/tztime.cc:
  Auto merged
storage/innobase/handler/ha_innodb.cc:
  Auto merged
storage/myisam/ha_myisam.cc:
  Auto merged
storage/myisam/ha_myisam.h:
  Auto merged
mysql-test/r/skip_grants.result:
  Manualmerge.
mysql-test/r/sp.result:
  Manualmerge.
mysql-test/t/skip_grants.test:
  Manualmerge.
mysql-test/t/sp.test:
  Manualmerge.
sql/event_data_objects.cc:
  Manualmerge.

1 files changed, 463 insertions, 0 deletions

diff --git a/mysql-test/t/events_time_zone.test b/mysql-test/t/events_time_zone.test
new file mode 100644
index 00000000000..fff84c7a995
--- /dev/null
+++ b/mysql-test/t/events_time_zone.test
@@ -0,0 +1,463 @@
+# This test case is sensitive to execution timing.  You may control
+# this sensitivity by the parameter below.  Small values will result
+# in fast but more unstable execution, large values will improve
+# stability at the cost of speed.  Basically, N is a number of seconds
+# to wait for operation to complete.  Should be positive.  Test runs
+# about 25*N seconds (it sleeps most of the time, so CPU speed is not
+# relevant).
+let $N = 5;
+
+--source include/big_test.inc
+
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest_db1;
+--enable_warnings
+
+CREATE DATABASE mysqltest_db1;
+
+let $old_db= `SELECT DATABASE()`;
+USE mysqltest_db1;
+
+SET GLOBAL EVENT_SCHEDULER= OFF;
+
+
+#
+# BUG#16420: Events: timestamps become UTC
+# BUG#26429: SHOW CREATE EVENT is incorrect for an event that
+#            STARTS NOW()
+# BUG#26431: Impossible to re-create an event from backup if its
+#            STARTS clause is in the past
+# WL#3698: Events: execution in local time zone
+#
+
+SET @save_time_zone= @@TIME_ZONE;
+
+#----------------------------------------------------------------------
+
+# We will use a separate connection because SET TIMESTAMP will stop
+# the clock in that connection.
+
+connect (conn1, localhost, root, , mysqltest_db1);
+
+SET TIME_ZONE= '+00:00';
+SET TIMESTAMP= UNIX_TIMESTAMP('2005-12-31 23:58:59');
+
+
+# Test when event time zone is updated on ALTER EVENT.
+#
+
+CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1;
+SHOW EVENTS;
+
+# Test storing and updating of the event time zone.
+#
+SET TIME_ZONE= '-01:00';
+ALTER EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2000-01-01 00:00:00';
+SHOW EVENTS;
+
+# This will update event time zone.
+SET TIME_ZONE= '+02:00';
+ALTER EVENT e1 ON SCHEDULE AT '2000-01-02 00:00:00'
+  ON COMPLETION PRESERVE DISABLE;
+SHOW EVENTS;
+
+# This will update event time zone.
+SET TIME_ZONE= '-03:00';
+ALTER EVENT e1 ON SCHEDULE EVERY 1 DAY ENDS '2030-01-03 00:00:00'
+  ON COMPLETION PRESERVE DISABLE;
+SHOW EVENTS;
+
+# This will not update event time zone, as no time is being adjusted.
+SET TIME_ZONE= '+04:00';
+ALTER EVENT e1 DO SELECT 2;
+SHOW EVENTS;
+
+DROP EVENT e1;
+
+#----------------------------------------------------------------------
+
+# Create some events.
+SET TIME_ZONE='+05:00';
+CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO
+  SELECT 1;
+
+SET TIMESTAMP= @@TIMESTAMP + 1;
+
+SET TIME_ZONE='-05:00';
+CREATE EVENT e2 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO
+  SELECT 1;
+
+SET TIMESTAMP= @@TIMESTAMP + 1;
+
+SET TIME_ZONE='+00:00';
+CREATE EVENT e3 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO
+  SELECT 1;
+
+
+# Test INFORMATION_SCHEMA.EVENTS.
+#
+
+SELECT * FROM INFORMATION_SCHEMA.EVENTS;
+
+
+# Test SHOW EVENTS.
+#
+
+SHOW EVENTS;
+
+
+# Test SHOW CREATE EVENT.
+#
+
+SHOW CREATE EVENT e1;
+SHOW CREATE EVENT e2;
+SHOW CREATE EVENT e3;
+
+#----------------------------------------------------------------------
+
+# Test times in the past.
+#
+
+--echo The following should fail, and nothing should be altered.
+
+--error ER_EVENT_CANNOT_ALTER_IN_THE_PAST
+ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
+  ENDS '1999-01-02 00:00:00';
+
+--error ER_EVENT_CANNOT_ALTER_IN_THE_PAST
+ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
+  ENDS '1999-01-02 00:00:00' DISABLE;
+
+--echo The following should give warnings, and nothing should be created.
+
+CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
+  ENDS '1999-01-02 00:00:00'
+DO
+  SELECT 1;
+
+CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
+  ENDS '1999-01-02 00:00:00' DISABLE
+DO
+  SELECT 1;
+
+CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DO
+  SELECT 1;
+
+CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DISABLE
+DO
+  SELECT 1;
+
+SHOW EVENTS;
+
+--echo The following should succeed giving a warning.
+
+ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
+  ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE;
+
+CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
+  ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE
+DO
+  SELECT 1;
+
+CREATE EVENT e5 ON SCHEDULE AT '1999-01-01 00:00:00'
+  ON COMPLETION PRESERVE
+DO
+  SELECT 1;
+
+--echo The following should succeed without warnings.
+
+ALTER EVENT e2 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00';
+
+ALTER EVENT e3 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
+  ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE;
+
+CREATE EVENT e6 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' DO
+  SELECT 1;
+
+CREATE EVENT e7 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
+  ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE
+DO
+  SELECT 1;
+
+CREATE EVENT e8 ON SCHEDULE AT '1999-01-01 00:00:00'
+  ON COMPLETION PRESERVE DISABLE
+DO
+  SELECT 1;
+
+SHOW EVENTS;
+
+
+DROP EVENT e8;
+DROP EVENT e7;
+DROP EVENT e6;
+DROP EVENT e5;
+DROP EVENT e4;
+DROP EVENT e3;
+DROP EVENT e2;
+DROP EVENT e1;
+
+
+disconnect conn1;
+connection default;
+
+#----------------------------------------------------------------------
+
+# Create rounding function.
+
+# Disable query log to hide actual value of $N.
+--disable_query_log
+eval SET @step= $N;
+--enable_query_log
+
+# Since we are working in a separate database, we may use any names we
+# like.
+CREATE TABLE t_step (step INT);
+INSERT INTO t_step VALUES (@step);
+
+# We can't use @variables in function, because it will be called from
+# the event thread, and 'eval' doesn't work for multi-statements, so
+# we can't interpolate $variables either, hence we fetch the step
+# value from the table.
+delimiter //;
+CREATE FUNCTION round_to_step(i INT, n INT) RETURNS INT
+BEGIN
+  DECLARE step INT;
+
+  SELECT * INTO step FROM t_step;
+
+  # We add 0.1 as a protection from inexact division.
+  RETURN FLOOR((i % (step * n) + 0.1) / step);
+END//
+delimiter ;//
+
+
+# Test time computations wrt Daylight Saving Time shifts.  We also
+# test here that the event operates in its time zone (see what NOW()
+# returns).
+#
+
+# Create a fake time zone with time transitions every 3*$N second.
+
+SET @step3= @step * 3;
+SET @step6= @step * 6;
+
+# Disable query log to hide current time.
+--disable_query_log
+SET @unix_time= UNIX_TIMESTAMP() - 1;
+--enable_query_log
+
+SET @unix_time= @unix_time - @unix_time % @step6;
+
+INSERT INTO mysql.time_zone VALUES (NULL, 'N');
+SET @tzid= LAST_INSERT_ID();
+INSERT INTO mysql.time_zone_transition_type
+  VALUES (@tzid, 0, 0, 0, 'b16420_0'); 
+INSERT INTO mysql.time_zone_transition_type
+  VALUES (@tzid, 1, @step3 - @step, 1, 'b16420_1'); 
+
+let $transition_unix_time= `SELECT @unix_time`;
+let $count= 30;
+--disable_query_log
+while ($count)
+{
+  eval INSERT INTO mysql.time_zone_transition
+         VALUES (@tzid, $transition_unix_time,
+                 $transition_unix_time % @step6 = 0);
+  let $transition_unix_time= `SELECT $transition_unix_time + @step3`;
+  dec $count;
+}
+--enable_query_log
+INSERT INTO mysql.time_zone_name VALUES ('bug16420', @tzid);
+
+CREATE TABLE t1 (count INT, unix_time INT, local_time INT, comment CHAR(80));
+CREATE TABLE t2 (count INT);
+INSERT INTO t2 VALUES (1);
+
+delimiter //;
+CREATE FUNCTION f1(comment CHAR(80)) RETURNS INT
+BEGIN
+  DECLARE orig_tz CHAR(64);
+  DECLARE unix_time INT;
+  DECLARE local_now DATETIME;
+  DECLARE utc_now DATETIME;
+  DECLARE local_time INT;
+
+  SET unix_time= UNIX_TIMESTAMP();
+  SET local_now= FROM_UNIXTIME(unix_time);
+  SET orig_tz= @@TIME_ZONE;
+  SET TIME_ZONE = '+00:00';
+  SET utc_now= FROM_UNIXTIME(unix_time);
+  SET TIME_ZONE= orig_tz;
+  SET local_time = unix_time + TIMESTAMPDIFF(SECOND, utc_now, local_now);
+
+  SET unix_time= round_to_step(unix_time, 6);
+  SET local_time= round_to_step(local_time, 6);
+
+  INSERT INTO t1 VALUES ((SELECT count FROM t2),
+                         unix_time, local_time, comment);
+  RETURN 0;
+END//
+delimiter ;//
+
+SET TIME_ZONE= '+00:00';
+CREATE EVENT e1 ON SCHEDULE EVERY @step SECOND
+  STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e1>");
+
+SET TIME_ZONE= 'bug16420';
+CREATE EVENT e2 ON SCHEDULE EVERY @step SECOND
+  STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e2>");
+
+# We want to start at the beginning of the DST cycle, so we wait
+# untill current time divides by @step6.
+let $wait_timeout= `SELECT @step6 + 1`;
+let $wait_condition= SELECT UNIX_TIMESTAMP() % @step6 = @step6 - 1;
+--source include/wait_condition.inc
+# The second wait is needed because after the first wait we may end up
+# on the ending edge of a second.  Second wait will bring us to the
+# beginning edge.
+let $wait_timeout= `SELECT @step + 1`;
+let $wait_condition= SELECT UNIX_TIMESTAMP() % @step6 = 0;
+--source include/wait_condition.inc
+
+# Note that after the scheduler is enabled, the event will be
+# scheduled only for the next second.
+SET GLOBAL EVENT_SCHEDULER= ON;
+
+# We want to run after the events are executed.
+SELECT SLEEP(@step / 2);
+
+let $count= 7;
+--disable_query_log
+--disable_result_log
+while ($count)
+{
+  SELECT SLEEP(@step);
+
+  eval SELECT CASE $count
+       WHEN 5 THEN f1(CONCAT("Second pass after backward -2 step shift,",
+                             " e2 should not be executed"))
+       WHEN 4 THEN f1(CONCAT("Second pass after backward -2 step shift,",
+                             " e2 should not be executed"))
+       WHEN 2 THEN f1(CONCAT("Forward +2 step shift, local 0, 1 are skipped,",
+                             " e2 should be executed"))
+       ELSE f1("e2 should be executed")
+       END;
+  UPDATE t2 SET count= count + 1;
+
+  dec $count;
+}
+--enable_result_log
+--enable_query_log
+
+SET GLOBAL EVENT_SCHEDULER= OFF;
+
+SELECT * FROM t1 ORDER BY count, comment;
+
+SET TIME_ZONE= @save_time_zone;
+
+DROP EVENT e2;
+DROP EVENT e1;
+DROP FUNCTION f1;
+DROP TABLE t1, t2;
+
+DELETE FROM mysql.time_zone_name            WHERE time_zone_id = @tzid;
+DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid;
+DELETE FROM mysql.time_zone_transition      WHERE time_zone_id = @tzid;
+DELETE FROM mysql.time_zone                 WHERE time_zone_id = @tzid;
+
+#----------------------------------------------------------------------
+
+# Test MONTH interval.
+#
+
+SET TIME_ZONE= '+00:00';
+
+CREATE TABLE t1 (event CHAR(2), dt DATE, offset INT);
+
+INSERT INTO mysql.time_zone VALUES (NULL, 'N');
+SET @tzid= LAST_INSERT_ID();
+
+SET @now= UNIX_TIMESTAMP();
+SET @offset_month_01= UNIX_TIMESTAMP('2030-01-31 12:00:00') - @now;
+SET @offset_month_02= UNIX_TIMESTAMP('2030-02-28 12:00:00') - @now - 5*@step;
+SET @offset_month_03= UNIX_TIMESTAMP('2030-03-31 12:00:00') - @now - 5*@step;
+SET @offset_month_04= UNIX_TIMESTAMP('2030-04-30 12:00:00') - @now - 13*@step;
+
+INSERT INTO mysql.time_zone_transition_type
+  VALUES (@tzid, 0, @offset_month_01, 0, 'b16420_0'); 
+INSERT INTO mysql.time_zone_transition_type
+  VALUES (@tzid, 1, @offset_month_02, 1, 'b16420_1'); 
+INSERT INTO mysql.time_zone_transition_type
+  VALUES (@tzid, 2, @offset_month_03, 1, 'b16420_2'); 
+INSERT INTO mysql.time_zone_transition_type
+  VALUES (@tzid, 3, @offset_month_04, 1, 'b16420_3'); 
+INSERT INTO mysql.time_zone_transition
+  VALUES (@tzid, @now, 0);
+INSERT INTO mysql.time_zone_transition
+  VALUES (@tzid, @now + 3 * @step, 1);
+INSERT INTO mysql.time_zone_transition
+  VALUES (@tzid, @now + 7 * @step, 2);
+INSERT INTO mysql.time_zone_transition
+  VALUES (@tzid, @now + 12 * @step, 3);
+# We have to user a new time zone name, because 'bug16420' has been
+# cached already.
+INSERT INTO mysql.time_zone_name VALUES ('bug16420_2', @tzid);
+
+SET TIME_ZONE= 'bug16420_2';
+
+SET GLOBAL EVENT_SCHEDULER= ON;
+
+let $now= `SELECT @now`;
+--disable_query_log
+eval CREATE EVENT e1 ON SCHEDULE EVERY 1 MONTH
+       STARTS FROM_UNIXTIME($now - @step) DO
+         INSERT INTO t1 VALUES
+           ("e1", NOW(), round_to_step(UNIX_TIMESTAMP() - $now, 4) - 1);
+eval CREATE EVENT e2 ON SCHEDULE EVERY 1 MONTH
+       STARTS FROM_UNIXTIME($now + @step) DO
+         INSERT INTO t1 VALUES
+           ("e2", NOW(), round_to_step(UNIX_TIMESTAMP() - $now, 4) - 1);
+--enable_query_log
+
+let $wait_timeout= `SELECT 16 * @step`;
+let $wait_condition= SELECT COUNT(*) = 7 FROM t1;
+--source include/wait_condition.inc
+
+SET GLOBAL EVENT_SCHEDULER= OFF;
+
+--echo Below we should see the following:
+--echo  - On Jan 31 only e2 is executed, because we started later than
+--echo    e1 should have been executed.  Offset of e2 is 0 because of
+--echo    the late start, not 1.
+--echo  - The next execution is on Feb 28 (last day of Feb).  Both events
+--echo    are executed in their times, offsets are -1 and 1.
+--echo  - The next time is Mar 31.  Because the time of event
+--echo    execution was skipped over, events are executed right away,
+--echo    offsets are 2 and 2.
+--echo  - The next time is Apr 30.  Events are again executed in their
+--echo    appointed times, offsets are -1 and 1.
+SELECT * FROM t1 ORDER BY dt, event;
+
+DROP EVENT e2;
+DROP EVENT e1;
+DROP TABLE t1;
+
+SET TIME_ZONE= @save_time_zone;
+
+DELETE FROM mysql.time_zone_name            WHERE time_zone_id = @tzid;
+DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid;
+DELETE FROM mysql.time_zone_transition      WHERE time_zone_id = @tzid;
+DELETE FROM mysql.time_zone                 WHERE time_zone_id = @tzid;
+
+DROP FUNCTION round_to_step;
+DROP TABLE t_step;
+
+
+DROP DATABASE mysqltest_db1;
+--disable_query_log
+eval USE $old_db;
+--enable_query_log
+
+
+--echo End of 5.1 tests.