Leonard zhou wrote:
> He Zhenxing 写道:
> > Hi Leonard,
> >
> > Leonard Zhou wrote:
> >> #At file:///home/zhl/mysql/rep/5.0/bug41719/
> >>
> >> 2767 Leonard Zhou 2009-03-05
> >> BUG#41719 delayed INSERT into timestamp col needs set time_zone for
> concurrent binlogging
> >>
> >> When do 'insert delayed' operation, the time_zone info doesn't follow
> with the row info.
> >
> > doesn't follow with => is not saved in
> >
> >> So when we do insert sometime later, time_zone didn't write into
> binlog.
> >> This will cause wrong result for timestamp column in slave.
> >>
> >> Our solution is that adding time_zone info with the delayed-row and
> >> restoring time_zone from row-info when execute that row in the furture
> by another thread.
> >> So we can write correct time_zone info into binlog and got correct
> result in slave.
> >
> > Very good.
> >
> >> modified:
> >> mysql-test/r/rpl_timezone.result
> >> mysql-test/t/rpl_timezone.test
> >> sql/sql_insert.cc
> >>
> >> per-file messages:
> >> mysql-test/r/rpl_timezone.result
> >> Test result
> >> mysql-test/t/rpl_timezone.test
> >> Add test for bug#41719
> >> sql/sql_insert.cc
> >> Add time_zone info in the delayed-row and restore time_zone when execute
> the row in the furture by another thread.
> >> === modified file 'mysql-test/r/rpl_timezone.result'
> >> --- a/mysql-test/r/rpl_timezone.result 2007-12-15 11:50:23 +0000
> >> +++ b/mysql-test/r/rpl_timezone.result 2009-03-04 18:56:35 +0000
> >> @@ -153,4 +153,22 @@ a b
> >> SET @@session.time_zone = default;
> >> DROP TABLE t1;
> >> SET @@session.time_zone = default;
> >> +CREATE TABLE t1 (date timestamp NOT NULL default CURRENT_TIMESTAMP on
> update CURRENT_TIMESTAMP, a int(11) default NULL);
> >> +SET @@session.time_zone='+01:00';
> >> +insert into t1 values('2008-12-23 19:39:39',1);
> >> +SET @@session.time_zone='+02:00';
> >> +insert delayed into t1 values ('2002-12-23 19:39:39',2);
> >> +flush logs;
> >> +select * from t1;
> >> +date a
> >> +2008-12-23 20:39:39 1
> >> +2002-12-23 19:39:39 2
> >> +delete from t1;
> >> +DROP TABLE t1;
> >> +select * from t1;
> >> +date a
> >> +2008-12-23 20:39:39 1
> >> +2002-12-23 19:39:39 2
> >> +DROP TABLE t1;
> >> +SET @@session.time_zone = default;
> >> End of 5.0 tests
> >>
> >> === modified file 'mysql-test/t/rpl_timezone.test'
> >> --- a/mysql-test/t/rpl_timezone.test 2007-08-06 11:57:28 +0000
> >> +++ b/mysql-test/t/rpl_timezone.test 2009-03-04 18:56:35 +0000
> >> @@ -154,5 +154,28 @@ connection master;
> >> DROP TABLE t1;
> >> SET @@session.time_zone = default;
> >>
> >> +# Bug#41719 delayed INSERT into timestamp col needs set time_zone for
> concurrent binlogging
> >> +# To test that time_zone is correctly binloging for 'insert delayed'
> statement
> >> +# Insert 2 values into timestamp col with different time_zone. Check
> result.
> >> +
> >> +--connection master
> >> +CREATE TABLE t1 (date timestamp NOT NULL default CURRENT_TIMESTAMP on
> update CURRENT_TIMESTAMP, a int(11) default NULL);
> >> +
> >> +SET @@session.time_zone='+01:00';
> >> +insert into t1 values('2008-12-23 19:39:39',1);
> >> +
> >> +--connection master1
> >> +SET @@session.time_zone='+02:00';
> >> +insert delayed into t1 values ('2002-12-23 19:39:39',2);
> >
> > use 'FLUSH TABLES t1' to wait for the delay insert finish.
> >
> >> +flush logs;
> >> +select * from t1;
> >> +delete from t1;
> >
> > the delete can be removed
> >
> >> +DROP TABLE t1;
> >> +
> >> +--exec $MYSQL_BINLOG $MYSQLTEST_VARDIR/log/master-bin.000001 | $MYSQL
> >> +--connection master1
> >> +select * from t1;
> >> +DROP TABLE t1;
> >> +SET @@session.time_zone = default;
> >>
> >> --echo End of 5.0 tests
> >>
> >> === modified file 'sql/sql_insert.cc'
> >> --- a/sql/sql_insert.cc 2008-10-15 13:55:52 +0000
> >> +++ b/sql/sql_insert.cc 2009-03-04 18:56:35 +0000
> >> @@ -1605,6 +1605,8 @@ public:
> >> ulong auto_increment_increment;
> >> ulong auto_increment_offset;
> >> timestamp_auto_set_type timestamp_field_type;
> >> + char time_zone_str[MAX_TIME_ZONE_NAME_LENGTH];
> >> + uint time_zone_len;
> >
> > Use
> >
> > Time_zone *time_zone;
> >
> > to replace above two members, so that you do can avoid searching for the
> > timezone object later.
> >
> > Please also change the constructor to initialize this member to 0;
> >
> >> uint query_length;
> >>
> >> delayed_row(enum_duplicates dup_arg, bool ignore_arg, bool
> log_query_arg)
> >> @@ -2062,6 +2064,19 @@ int write_delayed(THD *thd,TABLE *table,
> >> row->last_insert_id= thd->last_insert_id;
> >> row->timestamp_field_type= table->timestamp_field_type;
> >>
> >> + // add session variable timezone
> >
> > Please use /* */ for comments, this is required by the code guidelines
> > for server.
> >
> >> + if (thd->time_zone_used)
> >> + {
> >> + row->time_zone_len=
> thd->variables.time_zone->get_name()->length();
> >> + memcpy(row->time_zone_str,
> >> + thd->variables.time_zone->get_name()->ptr(),
> >> + row->time_zone_len);
> >> + row->time_zone_str[row->time_zone_len+1] = '\0';
> >
> > If you use Time_zone *time_zone, these lines can be:
> > row->time_zone= thd->variables.time_zone
>
> That's wrong.
> Row->time_zone is a pointer, and thd->variable.time_zone will be
> released after the thread finish.
> A new thread will execute the delayed-insert. So if we use a Time_zone
> pointer, we should allocate memory for it.
>
No, the time_zone object will not be freed even switched to another
thread, please check the comment of my_tz_find() function.
> And i don't know if Time_zone have a
> assignment functions. I will check it later.
>
Not necessary, just use the pointer.
> BTW. I searched almost all the cpp files and seems nobody deal with
> Time_zone using a pointer.
>
thd->varable.time_zone is also a pointer.
> >
> >> + }
> >> + else
> >> + {
> >> + row->time_zone_len = 0;
> >> + }
> >
> > and this will be:
> > row->time_zone= 0;
> >
> >> /* The session variable settings can always be copied. */
> >> row->auto_increment_increment=
> thd->variables.auto_increment_increment;
> >> row->auto_increment_offset=
> thd->variables.auto_increment_offset;
> >> @@ -2515,6 +2530,22 @@ bool Delayed_insert::handle_inserts(void
> >> }
> >> if (row->query && row->log_query &&
> using_bin_log)
> >> {
> >> + if (row->time_zone_len)
> >> + {
> >> + thd.time_zone_used = true;
> >> + String tmp(row->time_zone_str, row->time_zone_len,
> &my_charset_bin);
> >> + if (!(thd.variables.time_zone=my_tz_find(&tmp,
> thd.lex->time_zone_tables_used)))
> >> + {
> >> + thd.variables.time_zone= global_system_variables.time_zone;
> >> + sql_print_error("%s",thd.net.last_error);
> >> + DBUG_PRINT("error", ("Unknow time zone"));
> >> + goto err;
> >> + }
> >
> > And you can avoid searching for the time zone object by name here.
> >
> >> + }
> >> + else
> >> + {
> >> + thd.variables.time_zone= global_system_variables.time_zone;
> >
> > Could you explain why you need this? I think this is not necessary.
> >
> >> + }
> >> Query_log_event qinfo(&thd, row->query, row->query_length,
> 0, FALSE);
> >> mysql_bin_log.write(&qinfo);
> >> }
> >>
> >>
> >
> >
>
>
