mysql5.6 Time,datetime,timestamp

"Background"

5.6.4 later time type (Time,datetime,timestamp) supports microseconds

DateTime range: ' 1000-01-01 00:00:00.000000 ' to ' 9999-12-31 23:59:59.999999 '

Timestamp range: Values are ' 1970-01-01 00:00:01.000000 ' to ' 2038-01-19 03:14:07.999999 '

1) 5.6 Supports specifying decimal precision

Use test

CREATE TABLE Fractest (C1 time (2), C2 DATETIME (2), C3 TIMESTAMP (2));
INSERT into Fractest VALUES (' 17:51:04.777 ', ' 2014-09-08 17:51:04.777 ', ' 2014-09-08 17:51:04.777 ');
SELECT * from Fractest;

+-------------+------------------------+ ------------------------+
| C1           | c2                      | c3                      |
+-------------+------------------------ +------------------------+
| 17:51:04.78 | 2014-09-08 17:51:04.78 | 2014-09-08 17:51:04.78 |
+-------------+------------------------ +------------------------+

2) 5.6.4 The data previously inserted supports microseconds, but the data inserted is slightly seconds

Use test

CREATE TABLE Fractest (C1 time, C2 DATETIME, C3 TIMESTAMP);
INSERT into Fractest VALUES (' 17:51:04.777 ', ' 2014-09-08 17:51:04.777 ', ' 2014-09-08 17:51:04.777 ');
SELECT * from Fractest;

| c1        | c2                  | c3                  |
| 17:51:04 | 2014-09-08 17:51:04 | 2014-09-08 17:51:04 |

3) 5.6 Time function (Curtime (), sysdate (), or Utc_timestamp ()) can specify microsecond precision

Mysql> Select Curtime (2);
+-------------+
| Curtime (2) |
+-------------+
| 11:26:56.43 |
+-------------+

4) Storage

5.6.4 Previously, time,datetime,timestamp were fixed to occupy 3,8,4 bytes

Time	3 bytes + Fractional seconds storage
Datetime	5 bytes + Fractional seconds storage
TIMESTAMP	4 bytes + Fractional seconds storage

The relationship between storage length and precision in microseconds is as follows

Fractional Seconds Precision	Storage Required
0	0 bytes
1, 2	1 byte
3, 4	2 bytes
5, 6	3 bytes

For example, C1 time in the example above: 4 bytes, C2 datetime accounted for 6 bytes, TIMESTAMP accounted for 7 bytes, TIMESTAMP occupied 5 bytes

The correlation function can refer to My_datetime_packed_to_binary

5) The performance of new and old time types in the source code

5.6 internal added some new time types

Mysql_type_timestamp2

Mysql_type_datetime2,
Mysql_type_time2,

Used to support microseconds of storage.

And the old time type

Mysql_type_timestamp,
Mysql_type_datetime,
Mysql_type_time

Still retained and supported, thus compatible with old time data

5.6 The New Table Time field defaults to the latest type, as shown in the following code

sql/sql_yacc.yy:6514

| DATETIME type_datetime_precision
{$$= mysql_type_datetime2;}

6) Binlog and new time types

Binlog's Table_map_log_event records the table's metadata information, including libraries, tables, column information, and so on. The Microsecond precision information for the new time type is stored as the metadata for the column (M_field_metadata). The column metadata data for a similar large print column stores the actual length of the large segment (Field_blob::d o_save_field_metadata).

"Problem Recurrence"

1 Master on Execution

Use Zy

CREATE TABLE t1 (id int primary KEY, C1 time, C2 DATETIME, C3 TIMESTAMP);

Set sql_log_bin=0;

ALTER TABLE T1 modify C3 timestamp (4);

Set sql_log_bin=1;

INSERT into T1 VALUES (10, ' 17:51:04.98887 ', ' 2014-09-08 17:51:04.866666 ', ' 2014-09-08 17:51:04.777 ');

2 Slave on Execution

Show Slave Status\g         

last_errno:1677

Last_error:column 3 of table ' Zy.t1 ' cannot is converted from type ' timestamp ' to type ' timestamp '

"Analysis"

1) first try to fix it, Modify slave_type_conversions= ' All_lossy '; parameter slave_type_conversions can refer to  http://dev.mysql.com/doc/refman/5.5/en/ Replication-options-slave.html#sysvar_slave_type_conversions  

Mysql> Show variables like ' slave_type_conversions ';                                                                                          +------------------------+-------+| Variable_name          | Value |+------------------------+-------+| slave_type_conversions |       | +------------------------+-------+1 row in Set (0.00 sec) mysql> set global slave_type_conversions= ' All_lossy ';  Query OK, 0 rows Affected (0.00 sec) show slave status\g  last_errno:1610  last_error:could not execute write_rows Event on table zy.t1; Corrupted replication event was detected, error_code:1610; Handler error No error!; The event ' s master Log mysql-bin.000002, End_log_pos 550

　　

This method repair failed because it found an error parsing binlog row data (Unpack_row) with the table structure information of the standby library.

2) View Source:

Rows_log_event::d o_apply_event table_def::compatible_with can_convert_field_to .... if (field->real_type () = = So      Urce_type)//In this case, the main preparation type is consistent {if (metadata = = 0)//metadata can only is zero if no metadata was provided//this case the main library precision is 4 { /* If There is no metadata, we either has an old event where no metadata were supplied, or a type that Does not require any metadata.       In the either case, the conversion can be do but the no conversion table is necessary.      */Dbug_print ("Debug", ("Base types is identical, but there is no metadata");      *order_var= 0;    Dbug_return (TRUE);    } dbug_print ("Debug", ("Base types is identical, doing field size comparison")); if (field->compatible_field_size (metadata, Rli, Mflags, Order_var)) Dbug_return (IS_CONVERSION_OK (*order_var, Rli)    );  else Dbug_return (false); } else if (metadata = = 0 &&//Here are compatible with the new and old Time types (field->real_type () = = Mysql_type_timestamp2 && Source_type = = Mysql_type_timestamp) | |            (Field->real_type () = = Mysql_type_time2 && Source_type = = mysql_type_time) | |  (Field->real_type () = = Mysql_type_datetime2 && Source_type = = mysql_type_datetime)))      {/* ts-todo:conversion from FSP1>FSP2.    Can do non-lossy conversion from the old time, TIMESTAMP, datetime to New Time (0), TIMESTAMP (0), datetime (0).    */*order_var=-1;  Dbug_return (TRUE);  }

　　The above code makes type compatibility judgments, and this example returns failure at IS_CONVERSION_OK because of inconsistent precision.

mysql5.6 Time,datetime,timestamp