List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:December 27 1999 2:10pm
Subject:INSERT DELAY broken for timestamp fields
View as plain text  
Hi!

>>>>> "sisk" == sisk  <sisk@stripped> writes:

>> Description:
sisk> The behavior of "INSERT" and "INSERT DELAYED" is different when
sisk> specifying two timestamp fields.  "INSERT" works as expected, both
sisk> fields get the specified values, however, "INSERT DELAYED" only
sisk> sets the last one, the first field gets set to NOW() regardless
sisk> of what you specify.

>> How-To-Repeat:
	
sisk> $ mysqladmin create delaybug
sisk> Database "delaybug" created.
sisk> $ mysql delaybug
sisk> Welcome to the MySQL monitor.  Commands end with ; or \g.
sisk> Your MySQL connection id is 84 to server version: 3.23.6-alpha
 
sisk> Type 'help' for help.
 
mysql> create table t (dt timestamp, ct timestamp);
sisk> Query OK, 0 rows affected (0.02 sec)
 
mysql> insert into t (dt,ct) values ('19991124163225','19991124164725');
sisk> Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t;
sisk> +----------------+----------------+
sisk> | dt             | ct             |
sisk> +----------------+----------------+
sisk> | 19991124163225 | 19991124164725 |
sisk> +----------------+----------------+
sisk> 1 row in set (0.00 sec)
 
mysql> insert delayed into t (dt,ct) values ('19991124163225','19991124164725');
 
sisk> Query OK, 1 row affected (0.01 sec)
 
mysql> select * from t;
sisk> +----------------+----------------+
sisk> | dt             | ct             |
sisk> +----------------+----------------+
sisk> | 19991124163225 | 19991124164725 |
sisk> | 19991124172221 | 19991124164725 |
sisk> +----------------+----------------+
sisk> 2 rows in set (0.00 sec)
 
mysql> 
>> Fix:
	
sisk> Don't Know!

Hi!

I was scanning old mails to check if we have any unsolved problems and
come access the above.

Here is a patch for this (it will be included in MySQL 3.23.8):

*** /my/monty/master/mysql-3.23.7-alpha/sql/sql_insert.cc	Wed Dec  8 14:24:55 1999
--- ./sql_insert.cc	Mon Dec 27 15:50:34 1999
***************
*** 392,397 ****
--- 391,397 ----
    time_t start_time;
    bool query_start_used,last_insert_id_used,insert_id_used;
    ulonglong last_insert_id;
+   ulong time_stamp;
  
    delayed_row(enum_duplicates dup_arg) :record(0),query(0),dup(dup_arg) {}
    ~delayed_row()
***************
*** 648,653 ****
--- 648,662 ----
      (*field)->move_field(adjust_ptrs);		// Point at copy->record[0]
    }
    *field=0;
+ 
+   /* Adjust timestamp */
+   if (table->timestamp_field)
+   {
+     /* Restore offset as this may have been reset in handle_inserts */
+     copy->time_stamp=table->timestamp_field->offset()+1;
+     copy->timestamp_field=
+       (Field_timestamp*) copy->field[table->timestamp_field_offset];
+   }
    return copy;
  
    /* Got fatal error */
***************
*** 692,697 ****
--- 701,707 ----
    row->last_insert_id_used=	thd->last_insert_id_used;
    row->insert_id_used=		thd->insert_id_used;
    row->last_insert_id=		thd->last_insert_id;
+   row->time_stamp=		table->time_stamp;
  
    di->rows.push_back(row);
    di->stacked_inserts++;
***************
*** 1001,1006 ****
--- 1014,1020 ----
      thd.last_insert_id=row->last_insert_id;
      thd.last_insert_id_used=row->last_insert_id_used;
      thd.insert_id_used=row->insert_id_used;
+     table->time_stamp=row->time_stamp;
  
      info.handle_duplicates= row->dup;
      if (write_record(table,&info))
*** /my/monty/master/mysql-3.23.7-alpha/sql/table.cc	Sun Nov 28 14:14:08 1999
--- ./table.cc	Mon Dec 27 15:43:16 1999
***************
*** 977,982 ****
--- 978,984 ----
      goto err;
    diff= PTR_BYTE_DIFF(outparam->record[0],table->record[0]);
    outparam->timestamp_field=0;			// Will be set later
+   outparam->timestamp_field_offset=0;
    outparam->found_next_number_field=0;
  
    for (uint fieldnr=0 ; fieldnr < outparam->fields ; fieldnr++)
***************
*** 990,995 ****
--- 992,999 ----
        outparam->found_next_number_field=field;
      if (use_hash)
        (void) hash_insert(&outparam->name_hash,(byte*) field);
+     if (outparam->timestamp_field == field)
+       outparam->timestamp_field_offset=fieldnr;
    }
  
    /* copy keyinfo */
*** /my/monty/master/mysql-3.23.7-alpha/sql/table.h	Wed Nov 10 14:38:15 1999
--- ./table.h	Mon Dec 27 15:40:16 1999
***************
*** 69,75 ****
    uint db_stat;				/* mode of file as in handler.h */
    uint status;				/* Used by postfix.. */
    uint system;				/* Set if system record */
!   uint time_stamp;			/* Set to offset+1 of record */
    uint next_number_index;
    uint blob_ptr_size;			/* 4 or 8 */
    uint next_number_key_offset;
--- 69,76 ----
    uint db_stat;				/* mode of file as in handler.h */
    uint status;				/* Used by postfix.. */
    uint system;				/* Set if system record */
!   ulong time_stamp;			/* Set to offset+1 of record */
!   uint timestamp_field_offset;
    uint next_number_index;
    uint blob_ptr_size;			/* 4 or 8 */
    uint next_number_key_offset;


Regards,
Monty

PS: Starting of next year we will create a new mailing list:
    bugs@stripped ;  To this we will only accept bug reports,
    posted with mysqlbug and with a repeatable example. All MySQL
    developers will subscribe and read to this list and ensure that
    that all posted bugs will get solved quickly!
Thread
INSERT DELAY broken for timestamp fieldssisk25 Nov
  • INSERT DELAY broken for timestamp fieldsMichael Widenius27 Dec