diff options
| author | Jacob Mathew <jacob.mathew@mariadb.com> | 2018-07-24 15:57:13 -0700 |
|---|---|---|
| committer | Jacob Mathew <jacob.mathew@mariadb.com> | 2018-07-24 15:57:13 -0700 |
| commit | d6594847cff55bb6d7d094a0311f1fe3d5be789e (patch) | |
| tree | c95ecb3a3c01fe152d683e71ecab3ca083e1d6db /storage/spider | |
| parent | a78d1aaaa349ebbe3400e48f63903b349050b316 (diff) | |
| download | mariadb-git-d6594847cff55bb6d7d094a0311f1fe3d5be789e.tar.gz | |
MDEV-16246: insert timestamp into spider table from mysqldump gets wrong time zone.bb-10.4-MDEV-16246
The problem occurred because the Spider node was incorrectly handling
timestamp values sent to and received from the data nodes.
The problem has been corrected as follows:
- Added logic to set and maintain the UTC time zone on the data nodes.
To prevent timestamp ambiguity, it is necessary for the data nodes to use
a time zone such as UTC which does not have daylight savings time.
- Removed the spider_sync_time_zone configuration variable, which did not
solve the problem and which interfered with the solution.
- Added logic to convert to the UTC time zone all timestamp values sent to
and received from the data nodes. This is done for both unique and
non-unique timestamp columns. It is done for WHERE clauses, applying to
SELECT, UPDATE and DELETE statements, and for UPDATE columns.
- Disabled Spider's use of direct update when any of the columns to update is
a timestamp column. This is necessary to prevent false duplicate key value
errors.
- Added a new test spider.timestamp to thoroughly test Spider's handling of
timestamp values.
Author:
Jacob Mathew.
Reviewer:
Kentoku Shiba.
Merged:
Commit 97cc9d3 on branch bb-10.3-MDEV-16246
Diffstat (limited to 'storage/spider')
| -rw-r--r-- | storage/spider/ha_spider.cc | 94 | ||||
| -rw-r--r-- | storage/spider/ha_spider.h | 34 | ||||
| -rw-r--r-- | storage/spider/mysql-test/spider/r/timestamp.result | 370 | ||||
| -rw-r--r-- | storage/spider/mysql-test/spider/t/timestamp.test | 465 | ||||
| -rw-r--r-- | storage/spider/mysql-test/spider/t/timestamp_deinit.inc | 13 | ||||
| -rw-r--r-- | storage/spider/mysql-test/spider/t/timestamp_init.inc | 45 | ||||
| -rw-r--r-- | storage/spider/spd_conn.cc | 18 | ||||
| -rw-r--r-- | storage/spider/spd_conn.h | 6 | ||||
| -rw-r--r-- | storage/spider/spd_db_conn.cc | 525 | ||||
| -rw-r--r-- | storage/spider/spd_db_conn.h | 14 | ||||
| -rw-r--r-- | storage/spider/spd_db_include.h | 1 | ||||
| -rw-r--r-- | storage/spider/spd_db_mysql.cc | 242 | ||||
| -rw-r--r-- | storage/spider/spd_db_mysql.h | 2 | ||||
| -rw-r--r-- | storage/spider/spd_db_oracle.h | 2 | ||||
| -rw-r--r-- | storage/spider/spd_group_by_handler.cc | 24 | ||||
| -rw-r--r-- | storage/spider/spd_include.h | 2 | ||||
| -rw-r--r-- | storage/spider/spd_param.cc | 21 | ||||
| -rw-r--r-- | storage/spider/spd_param.h | 3 | ||||
| -rw-r--r-- | storage/spider/spd_trx.cc | 5 |
19 files changed, 1573 insertions, 313 deletions
diff --git a/storage/spider/ha_spider.cc b/storage/spider/ha_spider.cc index a2543065891..e6377d566d9 100644 --- a/storage/spider/ha_spider.cc +++ b/storage/spider/ha_spider.cc @@ -10064,13 +10064,11 @@ int ha_spider::update_row( #ifdef HANDLER_HAS_DIRECT_UPDATE_ROWS #ifdef HANDLER_HAS_DIRECT_UPDATE_ROWS_WITH_HS -int ha_spider::direct_update_rows_init( - uint mode, - KEY_MULTI_RANGE *ranges, - uint range_count, - bool sorted, - const uchar *new_data -) { +int ha_spider::direct_update_rows_init(List<Item> *update_fields, uint mode, + KEY_MULTI_RANGE *ranges, + uint range_count, bool sorted, + const uchar *new_data) +{ #if defined(HS_HAS_SQLCOM) && defined(HAVE_HANDLERSOCKET) int error_num; #endif @@ -10098,7 +10096,7 @@ int ha_spider::direct_update_rows_init( DBUG_RETURN(pre_direct_init_result); } DBUG_RETURN(bulk_access_link_exec_tgt->spider->direct_update_rows_init( - mode, ranges, range_count, sorted, new_data)); + update_fields, mode, ranges, range_count, sorted, new_data)); } #endif direct_update_init( @@ -10202,14 +10200,46 @@ int ha_spider::direct_update_rows_init( DBUG_RETURN(HA_ERR_WRONG_COMMAND); } #else -int ha_spider::direct_update_rows_init() +/** + Perform initialization for a direct update request. + + @param update fields Pointer to the list of fields to update. + + @return >0 Error. + 0 Success. +*/ + +int ha_spider::direct_update_rows_init(List<Item> *update_fields) { st_select_lex *select_lex; longlong select_limit; longlong offset_limit; + List_iterator<Item> it(*update_fields); + Item *item; + Field *field; THD *thd = trx->thd; DBUG_ENTER("ha_spider::direct_update_rows_init"); DBUG_PRINT("info",("spider this=%p", this)); + + while ((item = it++)) + { + if (item->type() == Item::FIELD_ITEM) + { + field = ((Item_field *)item)->field; + + if (field->type() == FIELD_TYPE_TIMESTAMP && + field->flags & UNIQUE_KEY_FLAG) + { + /* + Spider cannot perform direct update on unique timestamp fields. + To avoid false duplicate key errors, the table needs to be + updated one row at a time. + */ + DBUG_RETURN(HA_ERR_WRONG_COMMAND); + } + } + } + #ifdef HA_CAN_BULK_ACCESS if ( bulk_access_executing && @@ -10227,7 +10257,8 @@ int ha_spider::direct_update_rows_init() pre_direct_init_result)); DBUG_RETURN(pre_direct_init_result); } - DBUG_RETURN(bulk_access_link_exec_tgt->spider->direct_update_rows_init()); + DBUG_RETURN(bulk_access_link_exec_tgt->spider-> + direct_update_rows_init(List<Item> *update_fields)); } #endif direct_update_init( @@ -10298,31 +10329,41 @@ int ha_spider::direct_update_rows_init() #ifdef HA_CAN_BULK_ACCESS #ifdef HANDLER_HAS_DIRECT_UPDATE_ROWS_WITH_HS -int ha_spider::pre_direct_update_rows_init( - uint mode, - KEY_MULTI_RANGE *ranges, - uint range_count, - bool sorted, - const uchar *new_data -) { +int ha_spider::pre_direct_update_rows_init(List<Item> *update_fields, + uint mode, + KEY_MULTI_RANGE *ranges, + uint range_count, bool sorted, + const uchar *new_data) +{ int error_num; DBUG_ENTER("ha_spider::pre_direct_update_rows_init"); DBUG_PRINT("info",("spider this=%p", this)); if (bulk_access_started) { error_num = bulk_access_link_current->spider-> - pre_direct_update_rows_init( - mode, ranges, range_count, sorted, new_data); + pre_direct_update_rows_init(update_fields, mode, ranges, range_count, + sorted, new_data); bulk_access_link_current->spider->bulk_access_pre_called = TRUE; bulk_access_link_current->called = TRUE; DBUG_RETURN(error_num); } - pre_direct_init_result = direct_update_rows_init( - mode, ranges, range_count, sorted, new_data); + pre_direct_init_result = direct_update_rows_init(update_fields, mode, + ranges, range_count, + sorted, new_data); DBUG_RETURN(pre_direct_init_result); } #else -int ha_spider::pre_direct_update_rows_init() +/** + Do initialization for performing parallel direct update + for a handlersocket update request. + + @param update fields Pointer to the list of fields to update. + + @return >0 Error. + 0 Success. +*/ + +int ha_spider::pre_direct_update_rows_init(List<Item> *update_fields) { int error_num; DBUG_ENTER("ha_spider::pre_direct_update_rows_init"); @@ -10330,12 +10371,12 @@ int ha_spider::pre_direct_update_rows_init() if (bulk_access_started) { error_num = bulk_access_link_current->spider-> - pre_direct_update_rows_init(); + pre_direct_update_rows_init(update_fields); bulk_access_link_current->spider->bulk_access_pre_called = TRUE; bulk_access_link_current->called = TRUE; DBUG_RETURN(error_num); } - pre_direct_init_result = direct_update_rows_init(); + pre_direct_init_result = direct_update_rows_init(update_fields); DBUG_RETURN(pre_direct_init_result); } #endif @@ -15733,8 +15774,9 @@ int ha_spider::print_item_type( dbton_hdl = dbton_handler[dbton_id]; if ( dbton_hdl->first_link_idx >= 0 && - (error_num = spider_db_print_item_type(item, this, str, - alias, alias_length, dbton_id, FALSE, NULL)) + (error_num = spider_db_print_item_type(item, NULL, this, str, + alias, alias_length, dbton_id, + FALSE, NULL)) ) { DBUG_RETURN(error_num); } diff --git a/storage/spider/ha_spider.h b/storage/spider/ha_spider.h index b79e1b89fbf..66e5cf8a452 100644 --- a/storage/spider/ha_spider.h +++ b/storage/spider/ha_spider.h @@ -587,35 +587,29 @@ public: ); #ifdef HANDLER_HAS_DIRECT_UPDATE_ROWS #ifdef HANDLER_HAS_DIRECT_UPDATE_ROWS_WITH_HS - inline int direct_update_rows_init() + inline int direct_update_rows_init(List<Item> *update_fields) { - return direct_update_rows_init(2, NULL, 0, FALSE, NULL); + return direct_update_rows_init(update_fields, 2, NULL, 0, FALSE, NULL); } - int direct_update_rows_init( - uint mode, - KEY_MULTI_RANGE *ranges, - uint range_count, - bool sorted, - const uchar *new_data - ); + int direct_update_rows_init(List<Item> *update_fields, uint mode, + KEY_MULTI_RANGE *ranges, uint range_count, + bool sorted, const uchar *new_data); #else - int direct_update_rows_init(); + int direct_update_rows_init(List<Item> *update_fields); #endif #ifdef HA_CAN_BULK_ACCESS #ifdef HANDLER_HAS_DIRECT_UPDATE_ROWS_WITH_HS - inline int pre_direct_update_rows_init() + inline int pre_direct_update_rows_init(List<Item> *update_fields) { - return pre_direct_update_rows_init(2, NULL, 0, FALSE, NULL); + return pre_direct_update_rows_init(update_fields, + 2, NULL, 0, FALSE, NULL); } - int pre_direct_update_rows_init( - uint mode, - KEY_MULTI_RANGE *ranges, - uint range_count, - bool sorted, - uchar *new_data - ); + int pre_direct_update_rows_init(List<Item> *update_fields, + uint mode, KEY_MULTI_RANGE *ranges, + uint range_count, bool sorted, + uchar *new_data); #else - int pre_direct_update_rows_init(); + int pre_direct_update_rows_init(List<Item> *update_fields); #endif #endif #ifdef HANDLER_HAS_DIRECT_UPDATE_ROWS_WITH_HS diff --git a/storage/spider/mysql-test/spider/r/timestamp.result b/storage/spider/mysql-test/spider/r/timestamp.result new file mode 100644 index 00000000000..df40549cab2 --- /dev/null +++ b/storage/spider/mysql-test/spider/r/timestamp.result @@ -0,0 +1,370 @@ +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +child3_1 +child3_2 +child3_3 + +Initialize Time Zone +connection master_1; +SET GLOBAL time_zone='MET'; +SET time_zone='MET'; +connection child2_1; +SET GLOBAL time_zone='MET'; +SET time_zone='MET'; + +drop and create databases +connection master_1; +DROP DATABASE IF EXISTS ts_test_local; +CREATE DATABASE ts_test_local; +USE ts_test_local; +connection child2_1; +SET @old_log_output = @@global.log_output; +SET GLOBAL log_output = 'TABLE,FILE'; +DROP DATABASE IF EXISTS ts_test_remote; +CREATE DATABASE ts_test_remote; +USE ts_test_remote; + +test select 1 +connection master_1; +SELECT 1; +1 +1 +connection child2_1; +SELECT 1; +1 +1 + +create table +connection child2_1; +CHILD2_1_DROP_TABLES +CHILD2_1_CREATE_TABLES +TRUNCATE TABLE mysql.general_log; +connection master_1; +DROP TABLE IF EXISTS tbl_a; +CREATE TABLE tbl_a ( +col_a INT UNSIGNED NOT NULL AUTO_INCREMENT, +col_dt DATETIME, +col_ts TIMESTAMP NOT NULL +DEFAULT current_timestamp() ON UPDATE current_timestamp(), +PRIMARY KEY(col_a), +) MASTER_1_ENGINE MASTER_1_AUTO_INCREMENT_2_1 MASTER_1_COMMENT_2_1 +SHOW CREATE TABLE tbl_a; +Table Create Table +tbl_a CREATE TABLE `tbl_a` ( + `col_a` int(10) unsigned NOT NULL AUTO_INCREMENT, + `col_dt` datetime DEFAULT NULL, + `col_ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + PRIMARY KEY (`col_a`), + UNIQUE KEY `i_ts` (`col_ts`) +) ENGINE=SPIDER AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COMMENT='database "ts_test_remote", table "tbl_a", srv "s_2_1"' + +Set a different time zone that has DST +SET time_zone='+01:00'; + +Insert Rows +connection master_1; +Min value +SET @@timestamp=1; +INSERT INTO tbl_a VALUES (1, now(), now()); +SET @@timestamp=0; +Ambiguous DST values for MET time zone that result in the +same UTC timestamp +INSERT INTO tbl_a VALUES (2, '2018-03-25 02:00:00', '2018-03-25 02:00:00'); +INSERT INTO tbl_a VALUES (3, '2018-03-25 02:30:00', '2018-03-25 02:30:00'); +Ambiguous DST values for MET time zone in the 2:00 am to 3:00 am hour +that occur twice when transitioning from DST to standard time +SET @@timestamp=1540686600; +INSERT INTO tbl_a VALUES (4, now(), now()); +SET @@timestamp=1540690200; +INSERT INTO tbl_a VALUES (5, now(), now()); +Max value +SET @@timestamp=2147483647; +INSERT INTO tbl_a VALUES (6, now(), now()); +SET @@timestamp=0; + +SELECTs +connection child2_1; +TRUNCATE TABLE mysql.general_log; +connection master_1; +SELECT *, unix_timestamp(col_ts) FROM tbl_a; +col_a col_dt col_ts unix_timestamp(col_ts) +1 1970-01-01 01:00:01 1970-01-01 01:00:01 1 +2 2018-03-25 02:00:00 2018-03-25 02:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 02:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +6 2038-01-19 04:14:07 2038-01-19 04:14:07 2147483647 +connection child2_1; +SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; +argument +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` +SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %' +SELECT col_a, col_dt, col_ts, unix_timestamp(col_ts) FROM tbl_a ORDER BY col_a; +col_a col_dt col_ts unix_timestamp(col_ts) +1 1970-01-01 01:00:01 1970-01-01 01:00:01 1 +2 2018-03-25 02:00:00 2018-03-25 03:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 03:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 02:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +6 2038-01-19 04:14:07 2038-01-19 04:14:07 2147483647 + +DELETEs +connection child2_1; +TRUNCATE TABLE mysql.general_log; +connection master_1; +DELETE FROM tbl_a WHERE col_ts='1970-01-01 01:00:01'; +SELECT *, unix_timestamp(col_ts) FROM tbl_a; +col_a col_dt col_ts unix_timestamp(col_ts) +2 2018-03-25 02:00:00 2018-03-25 02:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 02:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +6 2038-01-19 04:14:07 2038-01-19 04:14:07 2147483647 +connection child2_1; +SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; +argument +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` +SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %' +SELECT col_a, col_dt, col_ts, unix_timestamp(col_ts) FROM tbl_a ORDER BY col_a; +col_a col_dt col_ts unix_timestamp(col_ts) +2 2018-03-25 02:00:00 2018-03-25 03:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 03:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 02:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +6 2038-01-19 04:14:07 2038-01-19 04:14:07 2147483647 +connection child2_1; +TRUNCATE TABLE mysql.general_log; +connection master_1; +SET @@timestamp=1; +INSERT INTO tbl_a VALUES (1, now(), now()); +SET @@timestamp=0; +SELECT *, unix_timestamp(col_ts) FROM tbl_a; +col_a col_dt col_ts unix_timestamp(col_ts) +1 1970-01-01 01:00:01 1970-01-01 01:00:01 1 +2 2018-03-25 02:00:00 2018-03-25 02:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 02:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +6 2038-01-19 04:14:07 2038-01-19 04:14:07 2147483647 +connection child2_1; +SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; +argument +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` +SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %' +SELECT col_a, col_dt, col_ts, unix_timestamp(col_ts) FROM tbl_a ORDER BY col_a; +col_a col_dt col_ts unix_timestamp(col_ts) +1 1970-01-01 01:00:01 1970-01-01 01:00:01 1 +2 2018-03-25 02:00:00 2018-03-25 03:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 03:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 02:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +6 2038-01-19 04:14:07 2038-01-19 04:14:07 2147483647 + +UPDATEs +connection child2_1; +TRUNCATE TABLE mysql.general_log; +connection master_1; +UPDATE tbl_a SET col_ts=col_dt; +SELECT *, unix_timestamp(col_ts) FROM tbl_a; +col_a col_dt col_ts unix_timestamp(col_ts) +1 1970-01-01 01:00:01 1970-01-01 01:00:01 1 +2 2018-03-25 02:00:00 2018-03-25 02:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 02:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +6 2038-01-19 04:14:07 2038-01-19 04:14:07 2147483647 +connection child2_1; +SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; +argument +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` for update +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` +SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %' +SELECT col_a, col_dt, col_ts, unix_timestamp(col_ts) FROM tbl_a ORDER BY col_a; +col_a col_dt col_ts unix_timestamp(col_ts) +1 1970-01-01 01:00:01 1970-01-01 01:00:01 1 +2 2018-03-25 02:00:00 2018-03-25 03:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 03:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 02:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +6 2038-01-19 04:14:07 2038-01-19 04:14:07 2147483647 + +Lookups +connection child2_1; +TRUNCATE TABLE mysql.general_log; +connection master_1; +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > '2018-01-01'; +col_a col_dt col_ts unix_timestamp(col_ts) +2 2018-03-25 02:00:00 2018-03-25 02:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 02:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +6 2038-01-19 04:14:07 2038-01-19 04:14:07 2147483647 +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts < '2018-10-28 02:30:00'; +col_a col_dt col_ts unix_timestamp(col_ts) +1 1970-01-01 01:00:01 1970-01-01 01:00:01 1 +2 2018-03-25 02:00:00 2018-03-25 02:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 02:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE '2018-10-28 02:30:00' > col_ts; +col_a col_dt col_ts unix_timestamp(col_ts) +1 1970-01-01 01:00:01 1970-01-01 01:00:01 1 +2 2018-03-25 02:00:00 2018-03-25 02:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 02:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts BETWEEN '2018-10-28 01:30:00' AND '2018-10-28 02:30:00'; +col_a col_dt col_ts unix_timestamp(col_ts) +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts >= '2018-10-28 01:30:00' AND col_ts <= '2018-10-28 02:30:00'; +col_a col_dt col_ts unix_timestamp(col_ts) +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > 180325020000; +col_a col_dt col_ts unix_timestamp(col_ts) +3 2018-03-25 02:30:00 2018-03-25 02:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +6 2038-01-19 04:14:07 2038-01-19 04:14:07 2147483647 +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > 19700101010001; +col_a col_dt col_ts unix_timestamp(col_ts) +2 2018-03-25 02:00:00 2018-03-25 02:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 02:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +6 2038-01-19 04:14:07 2038-01-19 04:14:07 2147483647 +connection child2_1; +SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; +argument +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` where (`col_ts` > '2017-12-31 23:00:00') order by `col_ts` +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` where (`col_ts` < '2018-10-28 01:30:00') order by `col_ts` +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` where ('2018-10-28 01:30:00' > `col_ts`) order by `col_ts` +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` where `col_ts` >= '2018-10-28 00:30:00' and `col_ts` <= '2018-10-28 01:30:00' and (`col_ts` between '2018-10-28 00:30:00' and '2018-10-28 01:30:00') order by `col_ts` +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` where `col_ts` >= '2018-10-28 00:30:00' and `col_ts` <= '2018-10-28 01:30:00' and ((`col_ts` >= '2018-10-28 00:30:00') and (`col_ts` <= '2018-10-28 01:30:00')) order by `col_ts` +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` where (`col_ts` > '2018-03-25 01:00:00') order by `col_ts` +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` where (`col_ts` > '1970-01-01 00:00:01') order by `col_ts` +SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %' +SELECT col_a, col_dt, col_ts, unix_timestamp(col_ts) FROM tbl_a ORDER BY col_a; +col_a col_dt col_ts unix_timestamp(col_ts) +1 1970-01-01 01:00:01 1970-01-01 01:00:01 1 +2 2018-03-25 02:00:00 2018-03-25 03:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 03:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 02:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +6 2038-01-19 04:14:07 2038-01-19 04:14:07 2147483647 + +Drop the index on the timestamp column +connection child2_1; +DROP INDEX i_ts ON tbl_a; +SHOW CREATE TABLE tbl_a; +Table Create Table +tbl_a CREATE TABLE `tbl_a` ( + `col_a` int(10) unsigned NOT NULL AUTO_INCREMENT, + `col_dt` datetime DEFAULT NULL, + `col_ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + PRIMARY KEY (`col_a`) +) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 +TRUNCATE TABLE mysql.general_log; +connection master_1; +DROP INDEX i_ts ON tbl_a; +SHOW CREATE TABLE tbl_a; +Table Create Table +tbl_a CREATE TABLE `tbl_a` ( + `col_a` int(10) unsigned NOT NULL AUTO_INCREMENT, + `col_dt` datetime DEFAULT NULL, + `col_ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + PRIMARY KEY (`col_a`) +) ENGINE=SPIDER AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COMMENT='database "ts_test_remote", table "tbl_a", srv "s_2_1"' + +Retry lookups on unindexed timestamp column +connection child2_1; +TRUNCATE TABLE mysql.general_log; +connection master_1; +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > '2018-01-01'; +col_a col_dt col_ts unix_timestamp(col_ts) +2 2018-03-25 02:00:00 2018-03-25 02:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 02:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +6 2038-01-19 04:14:07 2038-01-19 04:14:07 2147483647 +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts < '2018-10-28 02:30:00'; +col_a col_dt col_ts unix_timestamp(col_ts) +1 1970-01-01 01:00:01 1970-01-01 01:00:01 1 +2 2018-03-25 02:00:00 2018-03-25 02:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 02:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE '2018-10-28 02:30:00' > col_ts; +col_a col_dt col_ts unix_timestamp(col_ts) +1 1970-01-01 01:00:01 1970-01-01 01:00:01 1 +2 2018-03-25 02:00:00 2018-03-25 02:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 02:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts BETWEEN '2018-10-28 01:30:00' AND '2018-10-28 02:30:00'; +col_a col_dt col_ts unix_timestamp(col_ts) +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts >= '2018-10-28 01:30:00' AND col_ts <= '2018-10-28 02:30:00'; +col_a col_dt col_ts unix_timestamp(col_ts) +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > 180325020000; +col_a col_dt col_ts unix_timestamp(col_ts) +3 2018-03-25 02:30:00 2018-03-25 02:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +6 2038-01-19 04:14:07 2038-01-19 04:14:07 2147483647 +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > 19700101010001; +col_a col_dt col_ts unix_timestamp(col_ts) +2 2018-03-25 02:00:00 2018-03-25 02:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 02:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 01:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +6 2038-01-19 04:14:07 2038-01-19 04:14:07 2147483647 +connection child2_1; +SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; +argument +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` where (`col_ts` > '2017-12-31 23:00:00') +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` where (`col_ts` < '2018-10-28 01:30:00') +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` where ('2018-10-28 01:30:00' > `col_ts`) +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` where (`col_ts` between '2018-10-28 00:30:00' and '2018-10-28 01:30:00') +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` where ((`col_ts` >= '2018-10-28 00:30:00') and (`col_ts` <= '2018-10-28 01:30:00')) +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` where (`col_ts` > '2018-03-25 01:00:00') +select `col_a`,`col_dt`,`col_ts` from `ts_test_remote`.`tbl_a` where (`col_ts` > '1970-01-01 00:00:01') +SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %' +SELECT col_a, col_dt, col_ts, unix_timestamp(col_ts) FROM tbl_a ORDER BY col_a; +col_a col_dt col_ts unix_timestamp(col_ts) +1 1970-01-01 01:00:01 1970-01-01 01:00:01 1 +2 2018-03-25 02:00:00 2018-03-25 03:00:00 1521939600 +3 2018-03-25 02:30:00 2018-03-25 03:30:00 1521941400 +4 2018-10-28 01:30:00 2018-10-28 02:30:00 1540686600 +5 2018-10-28 02:30:00 2018-10-28 02:30:00 1540690200 +6 2038-01-19 04:14:07 2038-01-19 04:14:07 2147483647 + +Restore Time Zone settings +connection master_1; +SET GLOBAL time_zone=DEFAULT; +SET time_zone=DEFAULT; +connection child2_1; +SET GLOBAL time_zone=DEFAULT; +SET time_zone=DEFAULT; + +deinit +connection master_1; +DROP DATABASE IF EXISTS ts_test_local; +connection child2_1; +DROP DATABASE IF EXISTS ts_test_remote; +SET GLOBAL log_output = @old_log_output; +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +child3_1 +child3_2 +child3_3 + +end of test diff --git a/storage/spider/mysql-test/spider/t/timestamp.test b/storage/spider/mysql-test/spider/t/timestamp.test new file mode 100644 index 00000000000..7008638cc4a --- /dev/null +++ b/storage/spider/mysql-test/spider/t/timestamp.test @@ -0,0 +1,465 @@ +--source timestamp_init.inc + +--echo +--echo Initialize Time Zone +--connection master_1 +SET GLOBAL time_zone='MET'; +SET time_zone='MET'; +if ($USE_CHILD_GROUP2) +{ + --connection child2_1 + SET GLOBAL time_zone='MET'; + SET time_zone='MET'; +} + +--echo +--echo drop and create databases +--connection master_1 +--disable_warnings +DROP DATABASE IF EXISTS ts_test_local; +CREATE DATABASE ts_test_local; +USE ts_test_local; +if ($USE_CHILD_GROUP2) +{ + --connection child2_1 + if ($USE_GENERAL_LOG) + { + SET @old_log_output = @@global.log_output; + SET GLOBAL log_output = 'TABLE,FILE'; + } + DROP DATABASE IF EXISTS ts_test_remote; + CREATE DATABASE ts_test_remote; + USE ts_test_remote; +} +--enable_warnings + +--echo +--echo test select 1 +--connection master_1 +SELECT 1; +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + SELECT 1; + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} + +--echo +--echo create table +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + if ($OUTPUT_CHILD_GROUP2) + { + --disable_query_log + echo CHILD2_1_DROP_TABLES; + echo CHILD2_1_CREATE_TABLES; + } + --disable_warnings + eval $CHILD2_1_DROP_TABLES; + --enable_warnings + eval $CHILD2_1_CREATE_TABLES; + if ($OUTPUT_CHILD_GROUP2) + { + --enable_query_log + } + if ($USE_GENERAL_LOG) + { + TRUNCATE TABLE mysql.general_log; + } + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} +--connection master_1 +--disable_warnings +DROP TABLE IF EXISTS tbl_a; +--enable_warnings +--disable_query_log +echo CREATE TABLE tbl_a ( + col_a INT UNSIGNED NOT NULL AUTO_INCREMENT, + col_dt DATETIME, + col_ts TIMESTAMP NOT NULL + DEFAULT current_timestamp() ON UPDATE current_timestamp(), + PRIMARY KEY(col_a), + +) MASTER_1_ENGINE MASTER_1_AUTO_INCREMENT_2_1 MASTER_1_COMMENT_2_1; +eval CREATE TABLE tbl_a ( + col_a INT UNSIGNED NOT NULL AUTO_INCREMENT, + col_dt DATETIME, + col_ts TIMESTAMP NOT NULL + DEFAULT current_timestamp() ON UPDATE current_timestamp(), + PRIMARY KEY(col_a), + UNIQUE INDEX i_ts (col_ts) +) $MASTER_1_ENGINE $MASTER_1_AUTO_INCREMENT_2_1 $MASTER_1_COMMENT_2_1; +--enable_query_log +SHOW CREATE TABLE tbl_a; + +--echo +--echo Set a different time zone that has DST +SET time_zone='+01:00'; + +--echo +--echo Insert Rows +--connection master_1 +--echo Min value +SET @@timestamp=1; +INSERT INTO tbl_a VALUES (1, now(), now()); +SET @@timestamp=0; +--echo Ambiguous DST values for MET time zone that result in the +--echo same UTC timestamp +INSERT INTO tbl_a VALUES (2, '2018-03-25 02:00:00', '2018-03-25 02:00:00'); +INSERT INTO tbl_a VALUES (3, '2018-03-25 02:30:00', '2018-03-25 02:30:00'); +--echo Ambiguous DST values for MET time zone in the 2:00 am to 3:00 am hour +--echo that occur twice when transitioning from DST to standard time +SET @@timestamp=1540686600; +INSERT INTO tbl_a VALUES (4, now(), now()); +SET @@timestamp=1540690200; +INSERT INTO tbl_a VALUES (5, now(), now()); +--echo Max value +SET @@timestamp=2147483647; +INSERT INTO tbl_a VALUES (6, now(), now()); +SET @@timestamp=0; + +--echo +--echo SELECTs +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + if ($USE_GENERAL_LOG) + { + TRUNCATE TABLE mysql.general_log; + } + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} +--connection master_1 +SELECT *, unix_timestamp(col_ts) FROM tbl_a; +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + if ($USE_GENERAL_LOG) + { + eval $CHILD2_1_SELECT_ARGUMENT1; + } + eval $CHILD2_1_SELECT_TABLES; + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} + +--echo +--echo DELETEs +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + if ($USE_GENERAL_LOG) + { + TRUNCATE TABLE mysql.general_log; + } + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} +--connection master_1 +DELETE FROM tbl_a WHERE col_ts='1970-01-01 01:00:01'; +SELECT *, unix_timestamp(col_ts) FROM tbl_a; +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + if ($USE_GENERAL_LOG) + { + eval $CHILD2_1_SELECT_ARGUMENT1; + } + eval $CHILD2_1_SELECT_TABLES; + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + if ($USE_GENERAL_LOG) + { + TRUNCATE TABLE mysql.general_log; + } + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} +--connection master_1 +SET @@timestamp=1; +INSERT INTO tbl_a VALUES (1, now(), now()); +SET @@timestamp=0; +SELECT *, unix_timestamp(col_ts) FROM tbl_a; +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + if ($USE_GENERAL_LOG) + { + eval $CHILD2_1_SELECT_ARGUMENT1; + } + eval $CHILD2_1_SELECT_TABLES; + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} + +--echo +--echo UPDATEs +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + if ($USE_GENERAL_LOG) + { + TRUNCATE TABLE mysql.general_log; + } + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} +--connection master_1 +UPDATE tbl_a SET col_ts=col_dt; +SELECT *, unix_timestamp(col_ts) FROM tbl_a; +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + if ($USE_GENERAL_LOG) + { + eval $CHILD2_1_SELECT_ARGUMENT1; + } + eval $CHILD2_1_SELECT_TABLES; + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} + +--echo +--echo Lookups +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + if ($USE_GENERAL_LOG) + { + TRUNCATE TABLE mysql.general_log; + } + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} +--connection master_1 +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > '2018-01-01'; +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts < '2018-10-28 02:30:00'; +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE '2018-10-28 02:30:00' > col_ts; +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts BETWEEN '2018-10-28 01:30:00' AND '2018-10-28 02:30:00'; +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts >= '2018-10-28 01:30:00' AND col_ts <= '2018-10-28 02:30:00'; +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > 180325020000; +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > 19700101010001; +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + if ($USE_GENERAL_LOG) + { + eval $CHILD2_1_SELECT_ARGUMENT1; + } + eval $CHILD2_1_SELECT_TABLES; + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} + +--echo +--echo Drop the index on the timestamp column +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + eval $CHILD2_1_DROP_INDEX; + eval $CHILD2_1_SHOW_CREATE_TABLE; + if ($OUTPUT_CHILD_GROUP2) + { + --enable_query_log + } + if ($USE_GENERAL_LOG) + { + TRUNCATE TABLE mysql.general_log; + } + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} +--connection master_1 +DROP INDEX i_ts ON tbl_a; +SHOW CREATE TABLE tbl_a; + +--echo +--echo Retry lookups on unindexed timestamp column +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + if ($USE_GENERAL_LOG) + { + TRUNCATE TABLE mysql.general_log; + } + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} +--connection master_1 +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > '2018-01-01'; +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts < '2018-10-28 02:30:00'; +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE '2018-10-28 02:30:00' > col_ts; +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts BETWEEN '2018-10-28 01:30:00' AND '2018-10-28 02:30:00'; +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts >= '2018-10-28 01:30:00' AND col_ts <= '2018-10-28 02:30:00'; +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > 180325020000; +SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > 19700101010001; +if ($USE_CHILD_GROUP2) +{ + if (!$OUTPUT_CHILD_GROUP2) + { + --disable_query_log + --disable_result_log + } + --connection child2_1 + if ($USE_GENERAL_LOG) + { + eval $CHILD2_1_SELECT_ARGUMENT1; + } + eval $CHILD2_1_SELECT_TABLES; + if (!$OUTPUT_CHILD_GROUP2) + { + --enable_query_log + --enable_result_log + } +} + +--echo +--echo Restore Time Zone settings +--connection master_1 +SET GLOBAL time_zone=DEFAULT; +SET time_zone=DEFAULT; +if ($USE_CHILD_GROUP2) +{ + --connection child2_1 + SET GLOBAL time_zone=DEFAULT; + SET time_zone=DEFAULT; +} + +--echo +--echo deinit +--disable_warnings +--connection master_1 +DROP DATABASE IF EXISTS ts_test_local; +if ($USE_CHILD_GROUP2) +{ + --connection child2_1 + DROP DATABASE IF EXISTS ts_test_remote; + if ($USE_GENERAL_LOG) + { + SET GLOBAL log_output = @old_log_output; + } +} +--enable_warnings +--source timestamp_deinit.inc +--echo +--echo end of test diff --git a/storage/spider/mysql-test/spider/t/timestamp_deinit.inc b/storage/spider/mysql-test/spider/t/timestamp_deinit.inc new file mode 100644 index 00000000000..52be67a1d09 --- /dev/null +++ b/storage/spider/mysql-test/spider/t/timestamp_deinit.inc @@ -0,0 +1,13 @@ +--let $MASTER_1_COMMENT_2_1= $MASTER_1_COMMENT_2_1_BACKUP +--let $CHILD2_1_DROP_TABLES= $CHILD2_1_DROP_TABLES_BACKUP +--let $CHILD2_1_CREATE_TABLES= $CHILD2_1_CREATE_TABLES_BACKUP +--let $CHILD2_1_SELECT_TABLES= $CHILD2_1_SELECT_TABLES_BACKUP +--let $OUTPUT_CHILD_GROUP2= $OUTPUT_CHILD_GROUP2_BACKUP +--let $USE_GENERAL_LOG= $USE_GENERAL_LOG_BACKUP +--disable_warnings +--disable_query_log +--disable_result_log +--source ../t/test_deinit.inc +--enable_result_log +--enable_query_log +--enable_warnings diff --git a/storage/spider/mysql-test/spider/t/timestamp_init.inc b/storage/spider/mysql-test/spider/t/timestamp_init.inc new file mode 100644 index 00000000000..12049ab34fa --- /dev/null +++ b/storage/spider/mysql-test/spider/t/timestamp_init.inc @@ -0,0 +1,45 @@ +--disable_warnings +--disable_query_log +--disable_result_log +--source ../t/test_init.inc +--enable_result_log +--enable_query_log +--enable_warnings + +--let $MASTER_1_COMMENT_2_1_BACKUP= $MASTER_1_COMMENT_2_1 +let $MASTER_1_COMMENT_2_1= + COMMENT='database "ts_test_remote", table "tbl_a", srv "s_2_1"'; +let $MASTER_1_AUTO_INCREMENT_2_1= + AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4; +let $MASTER_1_AUTO_INCREMENT1= + AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4; +let $MASTER_1_AUTO_INCREMENT2= + AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4; +let $CHILD2_1_AUTO_INCREMENT= + AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4; +--let $CHILD2_1_DROP_TABLES_BACKUP= $CHILD2_1_DROP_TABLES +let $CHILD2_1_DROP_TABLES= + DROP TABLE IF EXISTS tbl_a; +--let $CHILD2_1_CREATE_TABLES_BACKUP= $CHILD2_1_CREATE_TABLES +let $CHILD2_1_CREATE_TABLES= + CREATE TABLE tbl_a ( + col_a INT UNSIGNED NOT NULL AUTO_INCREMENT, + col_dt DATETIME, + col_ts TIMESTAMP NOT NULL + DEFAULT current_timestamp() ON UPDATE current_timestamp(), + PRIMARY KEY(col_a), + UNIQUE INDEX i_ts (col_ts) + ) $CHILD2_1_ENGINE $CHILD2_1_AUTO_INCREMENT; +--let $CHILD2_1_SELECT_TABLES_BACKUP= $CHILD2_1_SELECT_TABLES +let $CHILD2_1_SELECT_TABLES= + SELECT col_a, col_dt, col_ts, unix_timestamp(col_ts) FROM tbl_a ORDER BY col_a; +let $CHILD2_1_SELECT_ARGUMENT1= + SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; +let $CHILD2_1_DROP_INDEX= + DROP INDEX i_ts ON tbl_a; +let $CHILD2_1_SHOW_CREATE_TABLE= + SHOW CREATE TABLE tbl_a; +--let $OUTPUT_CHILD_GROUP2_BACKUP= $OUTPUT_CHILD_GROUP2 +--let $OUTPUT_CHILD_GROUP2= 1 +--let $USE_GENERAL_LOG_BACKUP= $USE_GENERAL_LOG +--let $USE_GENERAL_LOG= 1 diff --git a/storage/spider/spd_conn.cc b/storage/spider/spd_conn.cc index 0ca7555385c..148ad43d337 100644 --- a/storage/spider/spd_conn.cc +++ b/storage/spider/spd_conn.cc @@ -88,6 +88,9 @@ extern PSI_thread_key spd_key_thd_bg_mon; #endif #endif +/* UTC time zone for timestamp columns */ +extern Time_zone *UTC; + HASH spider_open_connections; uint spider_open_connections_id; HASH spider_ipport_conns; @@ -454,6 +457,13 @@ SPIDER_CONN *spider_create_conn( char *tmp_ssl_cipher, *tmp_ssl_key, *tmp_default_file, *tmp_default_group; DBUG_ENTER("spider_create_conn"); + if (unlikely(!UTC)) + { + /* UTC time zone for timestamp columns */ + String tz_00_name(STRING_WITH_LEN("+00:00"), &my_charset_bin); + UTC = my_tz_find(current_thd, &tz_00_name); + } + #if defined(HS_HAS_SQLCOM) && defined(HAVE_HANDLERSOCKET) if (conn_kind == SPIDER_CONN_KIND_MYSQL) { @@ -1429,6 +1439,14 @@ void spider_conn_queue_time_zone( DBUG_VOID_RETURN; } +void spider_conn_queue_UTC_time_zone(SPIDER_CONN *conn) +{ + DBUG_ENTER("spider_conn_queue_time_zone"); + DBUG_PRINT("info", ("spider conn=%p", conn)); + spider_conn_queue_time_zone(conn, UTC); + DBUG_VOID_RETURN; +} + void spider_conn_queue_start_transaction( SPIDER_CONN *conn ) { diff --git a/storage/spider/spd_conn.h b/storage/spider/spd_conn.h index 48c9d206d97..998658c5353 100644 --- a/storage/spider/spd_conn.h +++ b/storage/spider/spd_conn.h @@ -13,6 +13,8 @@ along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ +#include "tztime.h" + #define SPIDER_LOCK_MODE_NO_LOCK 0 #define SPIDER_LOCK_MODE_SHARED 1 #define SPIDER_LOCK_MODE_EXCLUSIVE 2 @@ -137,6 +139,10 @@ void spider_conn_queue_time_zone( Time_zone *time_zone ); +void spider_conn_queue_UTC_time_zone( + SPIDER_CONN *conn +); + void spider_conn_queue_start_transaction( SPIDER_CONN *conn ); diff --git a/storage/spider/spd_db_conn.cc b/storage/spider/spd_db_conn.cc index ebfea1be138..7ac1b050d76 100644 --- a/storage/spider/spd_db_conn.cc +++ b/storage/spider/spd_db_conn.cc @@ -76,6 +76,9 @@ extern HASH spider_open_connections; pthread_mutex_t spider_open_conn_mutex; const char spider_dig_upper[] = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; +/* UTC time zone for timestamp columns */ +extern Time_zone *UTC; + int spider_db_connect( const SPIDER_SHARE *share, SPIDER_CONN *conn, @@ -191,10 +194,14 @@ int spider_db_connect( } DBUG_RETURN(error_num); } + conn->connect_error = 0; conn->opened_handlers = 0; conn->db_conn->reset_opened_handler(); ++conn->connection_id; + + /* Set the connection's time zone to UTC */ + spider_conn_queue_UTC_time_zone(conn); DBUG_RETURN(0); } @@ -2866,12 +2873,20 @@ int spider_db_fetch_row( my_ptrdiff_t ptr_diff ) { int error_num; + THD *thd = field->table->in_use; + Time_zone *saved_time_zone = thd->variables.time_zone; DBUG_ENTER("spider_db_fetch_row"); DBUG_PRINT("info", ("spider field_name %s", field->field_name.str)); DBUG_PRINT("info", ("spider fieldcharset %s", field->charset()->csname)); + + thd->variables.time_zone = UTC; + field->move_field_offset(ptr_diff); error_num = row->store_to_field(field, share->access_charset); field->move_field_offset(-ptr_diff); + + thd->variables.time_zone = saved_time_zone; + DBUG_RETURN(error_num); } @@ -8449,47 +8464,80 @@ int spider_db_flush_logs( DBUG_RETURN(0); } -int spider_db_print_item_type_default( - Item *item, - ha_spider *spider, - spider_string *str -) { - DBUG_ENTER("spider_db_print_item_type_default"); - THD *thd = spider->trx->thd; - SPIDER_SHARE *share = spider->share; - if (spider_param_skip_default_condition(thd, - share->skip_default_condition)) - DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); - if (str) +/** + Find the field among the items in an expression tree. + + @param item_list List of items of the expression. + @param item_count Number of items in the item list. + @param start_item Index of the first item to consider. + @param str String into which the expression is to be printed. + @param func_name Function or operator name. + @param func_name_length Length of function or operator name. + + @return Pointer to the field in the item list if the list + contains only one field; NULL otherwise. +*/ + +Field *spider_db_find_field_in_item_list(Item **item_list, uint item_count, + uint start_item, spider_string *str, + const char *func_name, + int func_name_length) +{ + uint item_num; + Item *item; + Field *field = NULL; + DBUG_ENTER("spider_db_find_field_in_item_list"); + + if (str && func_name_length) { - if (spider->share->access_charset->cset == system_charset_info->cset) + if (strncasecmp(func_name, ",", 1)) { -#if MYSQL_VERSION_ID < 50500 - item->print(str->get_str(), QT_IS); -#else - item->print(str->get_str(), QT_TO_SYSTEM_CHARSET); -#endif - } else { - item->print(str->get_str(), QT_ORDINARY); + /* A known function or operator */ + for (item_num = start_item; item_num < item_count; item_num++) + { + item = item_list[item_num]; + + if (item->type() == Item::FIELD_ITEM) + { + if (field) + { + /* Field is not relevant if there are multiple fields */ + DBUG_RETURN(NULL); + } + + field = ((Item_field *) item)->field; + } + } } - str->mem_calc(); } - DBUG_RETURN(0); + + DBUG_RETURN(field); } +/** + Print an operand value within a statement generated for an expression. -int spider_db_print_item_type( - Item *item, - ha_spider *spider, - spider_string *str, - const char *alias, - uint alias_length, - uint dbton_id, - bool use_fields, - spider_fields *fields -) { + @param item Operand value to print. + @param field Field related to the operand value. + @param spider Spider. + @param str String into which the value is to be printed. + @param alias Name related to the operand. + @param alias_length Length of the name. + @param dbton_id Spider Db/Table id. + @param use_fields Use fields or exchange fields. + @param fields Array of fields in the expression. + + @return Error code. +*/ + +int spider_db_print_item_type(Item *item, Field *field, ha_spider *spider, + spider_string *str, const char *alias, + uint alias_length, uint dbton_id, + bool use_fields, spider_fields *fields) +{ DBUG_ENTER("spider_db_print_item_type"); DBUG_PRINT("info",("spider COND type=%d", item->type())); + switch (item->type()) { case Item::FUNC_ITEM: @@ -8512,40 +8560,65 @@ int spider_db_print_item_type( case Item::ROW_ITEM: DBUG_RETURN(spider_db_open_item_row((Item_row *) item, spider, str, alias, alias_length, dbton_id, use_fields, fields)); - case Item::CONST_ITEM: - { - switch (item->cmp_type()) { - case ROW_RESULT: - DBUG_ASSERT(0); - // fall through - case TIME_RESULT: - DBUG_RETURN(spider_db_print_item_type_default(item, spider, str)); - case STRING_RESULT: - DBUG_RETURN(spider_db_open_item_string(item, spider, str, - alias, alias_length, dbton_id, use_fields, fields)); - case INT_RESULT: - case REAL_RESULT: - case DECIMAL_RESULT: - DBUG_RETURN(spider_db_open_item_int(item, spider, str, - alias, alias_length, dbton_id, use_fields, fields)); - } - DBUG_ASSERT(0); - } case Item::CACHE_ITEM: - DBUG_RETURN(spider_db_open_item_cache((Item_cache *)item, spider, str, - alias, alias_length, dbton_id, use_fields, fields)); + DBUG_RETURN(spider_db_open_item_cache((Item_cache *) item, field, + spider, str, alias, alias_length, + dbton_id, use_fields, fields)); case Item::INSERT_VALUE_ITEM: - DBUG_RETURN(spider_db_open_item_insert_value((Item_insert_value *)item, - spider, str, alias, alias_length, dbton_id, use_fields, fields)); + DBUG_RETURN(spider_db_open_item_insert_value((Item_insert_value *) item, + field, spider, str, + alias, alias_length, + dbton_id, + use_fields, fields)); case Item::SUBSELECT_ITEM: case Item::TRIGGER_FIELD_ITEM: #ifdef SPIDER_HAS_EXPR_CACHE_ITEM case Item::EXPR_CACHE_ITEM: #endif DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); + case Item::CONST_ITEM: + { + switch (item->cmp_type()) + { + case STRING_RESULT: + case TIME_RESULT: + DBUG_RETURN(spider_db_open_item_string(item, field, spider, str, + alias, alias_length, dbton_id, + use_fields, fields)); + case INT_RESULT: + case REAL_RESULT: + case DECIMAL_RESULT: + DBUG_RETURN(spider_db_open_item_int(item, field, spider, str, + alias, alias_length, dbton_id, + use_fields, fields)); + default: + DBUG_ASSERT(FALSE); + /* Fall through */ + } + } default: - DBUG_RETURN(spider_db_print_item_type_default(item, spider, str)); + THD *thd = spider->trx->thd; + SPIDER_SHARE *share = spider->share; + if (spider_param_skip_default_condition(thd, + share->skip_default_condition)) + DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); + if (str) + { + if (spider->share->access_charset->cset == system_charset_info->cset) + { +#if MYSQL_VERSION_ID < 50500 + item->print(str->get_str(), QT_IS); +#else + item->print(str->get_str(), QT_TO_SYSTEM_CHARSET); +#endif + } else { + item->print(str->get_str(), QT_ORDINARY); + } + str->mem_calc(); + } + break; } + DBUG_RETURN(0); } @@ -8577,8 +8650,9 @@ restart_first: { if (str) restart_pos = str->length(); - if ((error_num = spider_db_print_item_type(item, spider, str, - alias, alias_length, dbton_id, use_fields, fields))) + if ((error_num = spider_db_print_item_type(item, NULL, spider, str, + alias, alias_length, dbton_id, + use_fields, fields))) { if ( str && @@ -8611,8 +8685,9 @@ restart_first: str->q_append(SPIDER_SQL_SPACE_STR, SPIDER_SQL_SPACE_LEN); } - if ((error_num = spider_db_print_item_type(item, spider, str, - alias, alias_length, dbton_id, use_fields, fields))) + if ((error_num = spider_db_print_item_type(item, NULL, spider, str, + alias, alias_length, dbton_id, + use_fields, fields))) { if ( str && @@ -8848,8 +8923,9 @@ int spider_db_open_item_ref( } DBUG_RETURN(0); } - DBUG_RETURN(spider_db_print_item_type(*(item_ref->ref), spider, str, - alias, alias_length, dbton_id, use_fields, fields)); + DBUG_RETURN(spider_db_print_item_type(*(item_ref->ref), NULL, spider, + str, alias, alias_length, dbton_id, + use_fields, fields)); } DBUG_RETURN(spider_db_open_item_ident((Item_ident *) item_ref, spider, str, alias, alias_length, dbton_id, use_fields, fields)); @@ -8878,8 +8954,9 @@ int spider_db_open_item_row( for (roop_count = 0; roop_count < cols; roop_count++) { item = item_row->element_index(roop_count); - if ((error_num = spider_db_print_item_type(item, spider, str, - alias, alias_length, dbton_id, use_fields, fields))) + if ((error_num = spider_db_print_item_type(item, NULL, spider, str, + alias, alias_length, dbton_id, + use_fields, fields))) DBUG_RETURN(error_num); if (str) { @@ -8889,8 +8966,9 @@ int spider_db_open_item_row( } } item = item_row->element_index(roop_count); - if ((error_num = spider_db_print_item_type(item, spider, str, - alias, alias_length, dbton_id, use_fields, fields))) + if ((error_num = spider_db_print_item_type(item, NULL, spider, str, + alias, alias_length, dbton_id, + use_fields, fields))) DBUG_RETURN(error_num); if (str) { @@ -8902,104 +8980,236 @@ int spider_db_open_item_row( DBUG_RETURN(0); } -int spider_db_open_item_string( - Item *item, - ha_spider *spider, - spider_string *str, - const char *alias, - uint alias_length, - uint dbton_id, - bool use_fields, - spider_fields *fields -) { +/** + Print a string value within a generated statement. + + @param item String value to print. + @param field Field related to the string value. + @param spider Spider. + @param str String into which the value is to be printed. + @param alias Name related to the string value. + @param alias_length Length of the name. + @param dbton_id Spider Db/Table id. + @param use_fields Use fields or exchange fields. + @param fields Array of fields in an expression containing + the string value. + + @return Error code. +*/ + +int spider_db_open_item_string(Item *item, Field *field, ha_spider *spider, + spider_string *str, + const char *alias, uint alias_length, + uint dbton_id, + bool use_fields, spider_fields *fields) +{ + int error_num = 0; DBUG_ENTER("spider_db_open_item_string"); + if (str) { + THD *thd = NULL; + TABLE *table; + my_bitmap_map *saved_map; + Time_zone *saved_time_zone; char tmp_buf[MAX_FIELD_WIDTH]; spider_string tmp_str(tmp_buf, MAX_FIELD_WIDTH, str->charset()); String *tmp_str2; + String str_value; tmp_str.init_calc_mem(126); + if (!(tmp_str2 = item->val_str(tmp_str.get_str()))) { if (str->reserve(SPIDER_SQL_NULL_LEN)) - DBUG_RETURN(HA_ERR_OUT_OF_MEM); + { + error_num = HA_ERR_OUT_OF_MEM; + goto error; + } str->q_append(SPIDER_SQL_NULL_STR, SPIDER_SQL_NULL_LEN); - } else { + } + else + { + if (field && field->type() == FIELD_TYPE_TIMESTAMP) + { + /* + Store the string value in the field. This is necessary + when the statement contains more than one value for the + same field. + */ + table = field->table; + thd = table->in_use; + saved_map = dbug_tmp_use_all_columns(table, table->write_set); + item->save_in_field(field, FALSE); + saved_time_zone = thd->variables.time_zone; + thd->variables.time_zone = UTC; + + /* Retrieve the stored value converted to UTC */ + tmp_str2 = field->val_str(&str_value); + + if (!tmp_str2) + { + error_num = HA_ERR_OUT_OF_MEM; + goto error; + } + } + if (str->reserve(SPIDER_SQL_VALUE_QUOTE_LEN * 2 + - tmp_str2->length() * 2)) - DBUG_RETURN(HA_ERR_OUT_OF_MEM); - tmp_str.mem_calc(); + tmp_str2->length() * 2)) + { + error_num = HA_ERR_OUT_OF_MEM; + goto error; + } + if (!thd) + tmp_str.mem_calc(); str->q_append(SPIDER_SQL_VALUE_QUOTE_STR, SPIDER_SQL_VALUE_QUOTE_LEN); str->append_escape_string(tmp_str2->ptr(), tmp_str2->length()); - if ( - str->reserve(SPIDER_SQL_VALUE_QUOTE_LEN) - ) - DBUG_RETURN(HA_ERR_OUT_OF_MEM); + if (str->reserve(SPIDER_SQL_VALUE_QUOTE_LEN)) + { + error_num = HA_ERR_OUT_OF_MEM; + goto error; + } str->q_append(SPIDER_SQL_VALUE_QUOTE_STR, SPIDER_SQL_VALUE_QUOTE_LEN); } + +error: + if (thd) + { + thd->variables.time_zone = saved_time_zone; + dbug_tmp_restore_column_map(table->write_set, saved_map); + } } - DBUG_RETURN(0); + + DBUG_RETURN(error_num); } -int spider_db_open_item_int( - Item *item, - ha_spider *spider, - spider_string *str, - const char *alias, - uint alias_length, - uint dbton_id, - bool use_fields, - spider_fields *fields -) { +/** + Print an integer value within a generated statement. + + @param item Integer value to print. + @param field Field related to the integer value. + @param spider Spider. + @param str String into which the value is to be printed. + @param alias Name related to the integer value. + @param alias_length Length of the name. + @param dbton_id Spider Db/Table id. + @param use_fields Use fields or exchange fields. + @param fields Array of fields in an expression containing + the integer value. + + @return Error code. +*/ + +int spider_db_open_item_int(Item *item, Field *field, ha_spider *spider, + spider_string *str, + const char *alias, uint alias_length, + uint dbton_id, + bool use_fields, spider_fields *fields) +{ + int error_num = 0; DBUG_ENTER("spider_db_open_item_int"); + if (str) { + THD *thd = NULL; + TABLE *table; + my_bitmap_map *saved_map; + Time_zone *saved_time_zone; char tmp_buf[MAX_FIELD_WIDTH]; spider_string tmp_str(tmp_buf, MAX_FIELD_WIDTH, str->charset()); + String str_value; String *tmp_str2; tmp_str.init_calc_mem(127); + if (!(tmp_str2 = item->val_str(tmp_str.get_str()))) - DBUG_RETURN(HA_ERR_OUT_OF_MEM); + { + error_num = HA_ERR_OUT_OF_MEM; + goto error; + } tmp_str.mem_calc(); -#ifdef SPIDER_ITEM_HAS_CMP_TYPE - DBUG_PRINT("info",("spider cmp_type=%u", item->cmp_type())); - if (item->cmp_type() == TIME_RESULT) + + if (field && field->type() == FIELD_TYPE_TIMESTAMP) + { + /* + Store the int value in the field. This is necessary + when the statement contains more than one value for the + same field. + */ + table = field->table; + thd = table->in_use; + saved_map = dbug_tmp_use_all_columns(table, table->write_set); + item->save_in_field(field, FALSE); + saved_time_zone = thd->variables.time_zone; + thd->variables.time_zone = UTC; + } + + if (thd) { + /* Retrieve the stored value converted to UTC */ + tmp_str2 = field->val_str(&str_value); + + if (!tmp_str2) + { + error_num = HA_ERR_OUT_OF_MEM; + goto error; + } + if (str->reserve(SPIDER_SQL_VALUE_QUOTE_LEN * 2 + tmp_str2->length())) DBUG_RETURN(HA_ERR_OUT_OF_MEM); str->q_append(SPIDER_SQL_VALUE_QUOTE_STR, SPIDER_SQL_VALUE_QUOTE_LEN); str->append(*tmp_str2); str->q_append(SPIDER_SQL_VALUE_QUOTE_STR, SPIDER_SQL_VALUE_QUOTE_LEN); - } else { -#endif + } + else + { if (str->append(*tmp_str2)) - DBUG_RETURN(HA_ERR_OUT_OF_MEM); -#ifdef SPIDER_ITEM_HAS_CMP_TYPE + error_num = HA_ERR_OUT_OF_MEM; + } + +error: + if (thd) + { + thd->variables.time_zone = saved_time_zone; + dbug_tmp_restore_column_map(table->write_set, saved_map); } -#endif } - DBUG_RETURN(0); + + DBUG_RETURN(error_num); } -int spider_db_open_item_cache( - Item_cache *item_cache, - ha_spider *spider, - spider_string *str, - const char *alias, - uint alias_length, - uint dbton_id, - bool use_fields, - spider_fields *fields -) { +/** + Print a cached value within a generated statement. + + @param item Cached value to print. + @param field Field related to the cached value. + @param spider Spider. + @param str String into which the value is to be printed. + @param alias Name related to the cached value. + @param alias_length Length of the name. + @param dbton_id Spider Db/Table id. + @param use_fields Use fields or exchange fields. + @param fields Array of fields in the expression containing + the cached value. + + @return Error code. +*/ + +int spider_db_open_item_cache(Item_cache *item_cache, Field *field, + ha_spider *spider, spider_string *str, + const char *alias, uint alias_length, + uint dbton_id, + bool use_fields, spider_fields *fields) +{ DBUG_ENTER("spider_db_open_item_cache"); if (!item_cache->const_item()) DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); DBUG_PRINT("info",("spider result_type=%u", item_cache->result_type())); + switch (item_cache->result_type()) { case STRING_RESULT: - DBUG_RETURN(spider_db_open_item_string(item_cache, spider, str, - alias, alias_length, dbton_id, use_fields, fields)); + DBUG_RETURN(spider_db_open_item_string(item_cache, field, spider, str, + alias, alias_length, dbton_id, + use_fields, fields)); case ROW_RESULT: { int error_num; @@ -9014,9 +9224,11 @@ int spider_db_open_item_cache( for (roop_count = 0; roop_count < item_count; ++roop_count) { if ((error_num = spider_db_open_item_cache( - (Item_cache *) item_cache_row->element_index(roop_count), - spider, str, alias, alias_length, dbton_id, use_fields, fields - ))) { + (Item_cache *) + item_cache_row->element_index(roop_count), + NULL, spider, str, alias, alias_length, + dbton_id, use_fields, fields))) + { DBUG_RETURN(error_num); } if (str) @@ -9027,9 +9239,11 @@ int spider_db_open_item_cache( } } if ((error_num = spider_db_open_item_cache( - (Item_cache *) item_cache_row->element_index(roop_count), - spider, str, alias, alias_length, dbton_id, use_fields, fields - ))) { + (Item_cache *) + item_cache_row->element_index(roop_count), + NULL, spider, str, alias, alias_length, + dbton_id, use_fields, fields))) + { DBUG_RETURN(error_num); } if (str) @@ -9047,22 +9261,38 @@ int spider_db_open_item_cache( default: break; } - DBUG_RETURN(spider_db_open_item_int(item_cache, spider, str, - alias, alias_length, dbton_id, use_fields, fields)); + + DBUG_RETURN(spider_db_open_item_int(item_cache, field, spider, str, + alias, alias_length, dbton_id, + use_fields, fields)); } -int spider_db_open_item_insert_value( - Item_insert_value *item_insert_value, - ha_spider *spider, - spider_string *str, - const char *alias, - uint alias_length, - uint dbton_id, - bool use_fields, - spider_fields *fields -) { +/** + Print an INSERT value within a generated INSERT statement. + + @param item INSERT value to print. + @param field Field related to the INSERT value. + @param spider Spider. + @param str String into which the value is to be printed. + @param alias Name related to the INSERT value. + @param alias_length Length of the name. + @param dbton_id Spider Db/Table id. + @param use_fields Use fields or exchange fields. + @param fields Array of fields in the expression. + + @return Error code. +*/ + +int spider_db_open_item_insert_value(Item_insert_value *item_insert_value, + Field *field, ha_spider *spider, + spider_string *str, + const char *alias, uint alias_length, + uint dbton_id, + bool use_fields, spider_fields *fields) +{ int error_num; DBUG_ENTER("spider_db_open_item_insert_value"); + if (item_insert_value->arg) { if (str) @@ -9072,8 +9302,10 @@ int spider_db_open_item_insert_value( str->q_append(SPIDER_SQL_VALUES_STR, SPIDER_SQL_VALUES_LEN); str->q_append(SPIDER_SQL_OPEN_PAREN_STR, SPIDER_SQL_OPEN_PAREN_LEN); } - if ((error_num = spider_db_print_item_type(item_insert_value->arg, spider, - str, alias, alias_length, dbton_id, use_fields, fields))) + if ((error_num = spider_db_print_item_type(item_insert_value->arg, field, + spider, str, alias, + alias_length, dbton_id, + use_fields, fields))) DBUG_RETURN(error_num); if (str) { @@ -9082,6 +9314,7 @@ int spider_db_open_item_insert_value( str->q_append(SPIDER_SQL_CLOSE_PAREN_STR, SPIDER_SQL_CLOSE_PAREN_LEN); } } + DBUG_RETURN(0); } @@ -9137,9 +9370,10 @@ int spider_db_append_update_columns( while ((field = fi++)) { value = vi++; - if ((error_num = spider_db_print_item_type( - (Item *) field, spider, str, alias, alias_length, dbton_id, - use_fields, fields))) + if ((error_num = spider_db_print_item_type((Item *) field, NULL, spider, + str, alias, alias_length, + dbton_id, + use_fields, fields))) { if ( error_num == ER_SPIDER_COND_SKIP_NUM && @@ -9157,9 +9391,12 @@ int spider_db_append_update_columns( DBUG_RETURN(HA_ERR_OUT_OF_MEM); str->q_append(SPIDER_SQL_EQUAL_STR, SPIDER_SQL_EQUAL_LEN); } - if ((error_num = spider_db_print_item_type( - (Item *) value, spider, str, alias, alias_length, dbton_id, - use_fields, fields))) + if ((error_num = spider_db_print_item_type((Item *) value, + ((Item_field *) field)->field, + spider, str, + alias, alias_length, + dbton_id, + use_fields, fields))) DBUG_RETURN(error_num); if (str) { diff --git a/storage/spider/spd_db_conn.h b/storage/spider/spd_db_conn.h index 7977e61da58..879ab3540c1 100644 --- a/storage/spider/spd_db_conn.h +++ b/storage/spider/spd_db_conn.h @@ -838,8 +838,18 @@ int spider_db_flush_logs( ha_spider *spider ); +Field *spider_db_find_field_in_item_list( + Item **item_list, + uint item_count, + uint start_item, + spider_string *str, + const char *func_name, + int func_name_length +); + int spider_db_print_item_type( Item *item, + Field *field, ha_spider *spider, spider_string *str, const char *alias, @@ -930,6 +940,7 @@ int spider_db_open_item_row( int spider_db_open_item_string( Item *item, + Field *field, ha_spider *spider, spider_string *str, const char *alias, @@ -941,6 +952,7 @@ int spider_db_open_item_string( int spider_db_open_item_int( Item *item, + Field *field, ha_spider *spider, spider_string *str, const char *alias, @@ -952,6 +964,7 @@ int spider_db_open_item_int( int spider_db_open_item_cache( Item_cache *item_cache, + Field *field, ha_spider *spider, spider_string *str, const char *alias, @@ -963,6 +976,7 @@ int spider_db_open_item_cache( int spider_db_open_item_insert_value( Item_insert_value *item_insert_value, + Field *field, ha_spider *spider, spider_string *str, const char *alias, diff --git a/storage/spider/spd_db_include.h b/storage/spider/spd_db_include.h index 2913f911587..639bb6dac2d 100644 --- a/storage/spider/spd_db_include.h +++ b/storage/spider/spd_db_include.h @@ -17,6 +17,7 @@ #if defined(HS_HAS_SQLCOM) && defined(HAVE_HANDLERSOCKET) #include "hstcpcli.hpp" #endif +#include "tztime.h" #define SPIDER_DBTON_SIZE 15 diff --git a/storage/spider/spd_db_mysql.cc b/storage/spider/spd_db_mysql.cc index 38ad180fabf..aec08ae7be1 100644 --- a/storage/spider/spd_db_mysql.cc +++ b/storage/spider/spd_db_mysql.cc @@ -179,6 +179,9 @@ static const char *spider_db_timefunc_interval_str[] = " minute_microsecond", " second_microsecond" }; +/* UTC time zone for timestamp columns */ +Time_zone *UTC = 0; + int spider_mysql_init() { DBUG_ENTER("spider_mysql_init"); @@ -3192,9 +3195,13 @@ int spider_db_mysql_util::append_column_value( spider_string tmp_str(buf, MAX_FIELD_WIDTH, &my_charset_bin); String *ptr; uint length; + THD *thd = field->table->in_use; + Time_zone *saved_time_zone = thd->variables.time_zone; DBUG_ENTER("spider_db_mysql_util::append_column_value"); tmp_str.init_calc_mem(113); + thd->variables.time_zone = UTC; + if (new_ptr) { if ( @@ -3205,7 +3212,8 @@ int spider_db_mysql_util::append_column_value( tmp_str.set_quick((char *) new_ptr + HA_KEY_BLOB_LENGTH, length, &my_charset_bin); ptr = tmp_str.get_str(); - } else if (field->type() == MYSQL_TYPE_GEOMETRY) + } + else if (field->type() == MYSQL_TYPE_GEOMETRY) { /* uint mlength = SIZEOF_STORED_DOUBLE, lcnt; @@ -3282,14 +3290,21 @@ int spider_db_mysql_util::append_column_value( tmp_str.q_append((char *) new_ptr + SIZEOF_STORED_DOUBLE * 3, SIZEOF_STORED_DOUBLE); ptr = tmp_str.get_str(); - } else { + } + else + { ptr = field->val_str(tmp_str.get_str(), new_ptr); tmp_str.mem_calc(); } - } else { + } + else + { ptr = field->val_str(tmp_str.get_str()); tmp_str.mem_calc(); } + + thd->variables.time_zone = saved_time_zone; + DBUG_PRINT("info", ("spider field->type() is %d", field->type())); DBUG_PRINT("info", ("spider ptr->length() is %d", ptr->length())); /* @@ -3335,7 +3350,8 @@ int spider_db_mysql_util::append_column_value( append_escaped_util(str, tmp_str2.get_str()) ) DBUG_RETURN(HA_ERR_OUT_OF_MEM); - } else if (str->append(*ptr)) + } + else if (str->append(*ptr)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); if (str->reserve(SPIDER_SQL_VALUE_QUOTE_LEN)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); @@ -3604,12 +3620,13 @@ int spider_db_mysql_util::open_item_func( uint dbton_id = spider_dbton_mysql.dbton_id; int error_num; Item *item, **item_list = item_func->arguments(); - uint roop_count, item_count = item_func->argument_count(), start_item = 0; + Field *field; + uint loop_count, item_count = item_func->argument_count(), start_item = 0; const char *func_name = SPIDER_SQL_NULL_CHAR_STR, - *separete_str = SPIDER_SQL_NULL_CHAR_STR, + *separator_str = SPIDER_SQL_NULL_CHAR_STR, *last_str = SPIDER_SQL_NULL_CHAR_STR; int func_name_length = SPIDER_SQL_NULL_CHAR_LEN, - separete_str_length = SPIDER_SQL_NULL_CHAR_LEN, + separator_str_length = SPIDER_SQL_NULL_CHAR_LEN, last_str_length = SPIDER_SQL_NULL_CHAR_LEN; int use_pushdown_udf; bool merge_func = FALSE; @@ -3675,8 +3692,9 @@ int spider_db_mysql_util::open_item_func( ) { if (str) str->length(str->length() - SPIDER_SQL_OPEN_PAREN_LEN); - DBUG_RETURN(spider_db_open_item_int(item_func, spider, str, - alias, alias_length, dbton_id, use_fields, fields)); + DBUG_RETURN(spider_db_open_item_int(item_func, NULL, spider, str, + alias, alias_length, dbton_id, + use_fields, fields)); } else if ( !strncasecmp("case", func_name, func_name_length) ) { @@ -3695,8 +3713,8 @@ int spider_db_mysql_util::open_item_func( alias, alias_length, dbton_id, use_fields, fields))) DBUG_RETURN(error_num); } - for (roop_count = 0; roop_count < item_func_case->ncases; - roop_count += 2) + for (loop_count = 0; loop_count < item_func_case->ncases; + loop_count += 2) { if (str) { @@ -3705,7 +3723,7 @@ int spider_db_mysql_util::open_item_func( str->q_append(SPIDER_SQL_WHEN_STR, SPIDER_SQL_WHEN_LEN); } if ((error_num = spider_db_print_item_type( - item_list[roop_count], spider, str, + item_list[loop_count], spider, str, alias, alias_length, dbton_id, use_fields, fields))) DBUG_RETURN(error_num); if (str) @@ -3715,7 +3733,7 @@ int spider_db_mysql_util::open_item_func( str->q_append(SPIDER_SQL_THEN_STR, SPIDER_SQL_THEN_LEN); } if ((error_num = spider_db_print_item_type( - item_list[roop_count + 1], spider, str, + item_list[loop_count + 1], spider, str, alias, alias_length, dbton_id, use_fields, fields))) DBUG_RETURN(error_num); } @@ -3765,8 +3783,10 @@ int spider_db_mysql_util::open_item_func( ) { if (str) str->length(str->length() - SPIDER_SQL_OPEN_PAREN_LEN); - DBUG_RETURN(spider_db_open_item_string(item_func, spider, str, - alias, alias_length, dbton_id, use_fields, fields)); + DBUG_RETURN(spider_db_open_item_string(item_func, NULL, spider, str, + alias, alias_length, + dbton_id, + use_fields, fields)); } else if ( !strncasecmp("convert", func_name, func_name_length) ) { @@ -3790,8 +3810,9 @@ int spider_db_mysql_util::open_item_func( ) { if (str) str->length(str->length() - SPIDER_SQL_OPEN_PAREN_LEN); - DBUG_RETURN(spider_db_open_item_string(item_func, spider, str, - alias, alias_length, dbton_id, use_fields, fields)); + DBUG_RETURN(spider_db_open_item_string(item_func, NULL, spider, str, + alias, alias_length, dbton_id, + use_fields, fields)); } else if (func_name_length == 9 && !strncasecmp("isnottrue", func_name, func_name_length) ) { @@ -3817,8 +3838,8 @@ int spider_db_mysql_util::open_item_func( } func_name = SPIDER_SQL_COMMA_STR; func_name_length = SPIDER_SQL_COMMA_LEN; - separete_str = SPIDER_SQL_COMMA_STR; - separete_str_length = SPIDER_SQL_COMMA_LEN; + separator_str = SPIDER_SQL_COMMA_STR; + separator_str_length = SPIDER_SQL_COMMA_LEN; break; } } else if (func_name_length == 12) @@ -3906,8 +3927,10 @@ int spider_db_mysql_util::open_item_func( { if (str) str->length(str->length() - SPIDER_SQL_OPEN_PAREN_LEN); - DBUG_RETURN(spider_db_open_item_string(item_func, spider, str, - alias, alias_length, dbton_id, use_fields, fields)); + DBUG_RETURN(spider_db_open_item_string(item_func, NULL, spider, str, + alias, alias_length, + dbton_id, + use_fields, fields)); } else if (!strncasecmp("timestampdiff", func_name, func_name_length)) { #ifdef ITEM_FUNC_TIMESTAMPDIFF_ARE_PUBLIC @@ -4232,8 +4255,11 @@ int spider_db_mysql_util::open_item_func( func_name = spider_db_timefunc_interval_str[ item_date_add_interval->int_type]; func_name_length = strlen(func_name); - if ((error_num = spider_db_print_item_type(item_list[0], spider, str, - alias, alias_length, dbton_id, use_fields, fields))) + if ((error_num = spider_db_print_item_type(item_list[0], NULL, + spider, str, + alias, alias_length, + dbton_id, + use_fields, fields))) DBUG_RETURN(error_num); if (str) { @@ -4249,8 +4275,11 @@ int spider_db_mysql_util::open_item_func( str->q_append(SPIDER_SQL_INTERVAL_STR, SPIDER_SQL_INTERVAL_LEN); } } - if ((error_num = spider_db_print_item_type(item_list[1], spider, str, - alias, alias_length, dbton_id, use_fields, fields))) + if ((error_num = spider_db_print_item_type(item_list[1], NULL, + spider, str, + alias, alias_length, + dbton_id, + use_fields, fields))) DBUG_RETURN(error_num); if (str) { @@ -4272,16 +4301,17 @@ int spider_db_mysql_util::open_item_func( } func_name = SPIDER_SQL_COMMA_STR; func_name_length = SPIDER_SQL_COMMA_LEN; - separete_str = SPIDER_SQL_COMMA_STR; - separete_str_length = SPIDER_SQL_COMMA_LEN; + separator_str = SPIDER_SQL_COMMA_STR; + separator_str_length = SPIDER_SQL_COMMA_LEN; last_str = SPIDER_SQL_CLOSE_PAREN_STR; last_str_length = SPIDER_SQL_CLOSE_PAREN_LEN; break; case Item_func::NOW_FUNC: if (str) str->length(str->length() - SPIDER_SQL_OPEN_PAREN_LEN); - DBUG_RETURN(spider_db_open_item_string(item_func, spider, str, - alias, alias_length, dbton_id, use_fields, fields)); + DBUG_RETURN(spider_db_open_item_string(item_func, NULL, spider, str, + alias, alias_length, dbton_id, + use_fields, fields)); case Item_func::CHAR_TYPECAST_FUNC: DBUG_PRINT("info",("spider CHAR_TYPECAST_FUNC")); { @@ -4406,15 +4436,15 @@ int spider_db_mysql_util::open_item_func( { func_name = SPIDER_SQL_NOT_IN_STR; func_name_length = SPIDER_SQL_NOT_IN_LEN; - separete_str = SPIDER_SQL_COMMA_STR; - separete_str_length = SPIDER_SQL_COMMA_LEN; + separator_str = SPIDER_SQL_COMMA_STR; + separator_str_length = SPIDER_SQL_COMMA_LEN; last_str = SPIDER_SQL_CLOSE_PAREN_STR; last_str_length = SPIDER_SQL_CLOSE_PAREN_LEN; } else { func_name = SPIDER_SQL_IN_STR; func_name_length = SPIDER_SQL_IN_LEN; - separete_str = SPIDER_SQL_COMMA_STR; - separete_str_length = SPIDER_SQL_COMMA_LEN; + separator_str = SPIDER_SQL_COMMA_STR; + separator_str_length = SPIDER_SQL_COMMA_LEN; last_str = SPIDER_SQL_CLOSE_PAREN_STR; last_str_length = SPIDER_SQL_CLOSE_PAREN_LEN; } @@ -4424,13 +4454,13 @@ int spider_db_mysql_util::open_item_func( { func_name = SPIDER_SQL_NOT_BETWEEN_STR; func_name_length = SPIDER_SQL_NOT_BETWEEN_LEN; - separete_str = SPIDER_SQL_AND_STR; - separete_str_length = SPIDER_SQL_AND_LEN; + separator_str = SPIDER_SQL_AND_STR; + separator_str_length = SPIDER_SQL_AND_LEN; } else { func_name = (char*) item_func->func_name(); func_name_length = strlen(func_name); - separete_str = SPIDER_SQL_AND_STR; - separete_str_length = SPIDER_SQL_AND_LEN; + separator_str = SPIDER_SQL_AND_STR; + separator_str_length = SPIDER_SQL_AND_LEN; } break; case Item_func::UDF_FUNC: @@ -4451,8 +4481,8 @@ int spider_db_mysql_util::open_item_func( } func_name = SPIDER_SQL_COMMA_STR; func_name_length = SPIDER_SQL_COMMA_LEN; - separete_str = SPIDER_SQL_COMMA_STR; - separete_str_length = SPIDER_SQL_COMMA_LEN; + separator_str = SPIDER_SQL_COMMA_STR; + separator_str_length = SPIDER_SQL_COMMA_LEN; last_str = SPIDER_SQL_CLOSE_PAREN_STR; last_str_length = SPIDER_SQL_CLOSE_PAREN_LEN; break; @@ -4472,11 +4502,13 @@ int spider_db_mysql_util::open_item_func( if (str) str->length(str->length() - SPIDER_SQL_OPEN_PAREN_LEN); if (item_func->result_type() == STRING_RESULT) - DBUG_RETURN(spider_db_open_item_string(item_func, spider, str, - alias, alias_length, dbton_id, use_fields, fields)); + DBUG_RETURN(spider_db_open_item_string(item_func, NULL, spider, str, + alias, alias_length, dbton_id, + use_fields, fields)); else - DBUG_RETURN(spider_db_open_item_int(item_func, spider, str, - alias, alias_length, dbton_id, use_fields, fields)); + DBUG_RETURN(spider_db_open_item_int(item_func, NULL, spider, str, + alias, alias_length, dbton_id, + use_fields, fields)); case Item_func::FT_FUNC: if (spider_db_check_ft_idx(item_func, spider) == MAX_KEY) DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); @@ -4487,8 +4519,8 @@ int spider_db_mysql_util::open_item_func( DBUG_RETURN(HA_ERR_OUT_OF_MEM); str->q_append(SPIDER_SQL_MATCH_STR, SPIDER_SQL_MATCH_LEN); } - separete_str = SPIDER_SQL_COMMA_STR; - separete_str_length = SPIDER_SQL_COMMA_LEN; + separator_str = SPIDER_SQL_COMMA_STR; + separator_str_length = SPIDER_SQL_COMMA_LEN; last_str = SPIDER_SQL_CLOSE_PAREN_STR; last_str_length = SPIDER_SQL_CLOSE_PAREN_LEN; break; @@ -4505,8 +4537,8 @@ int spider_db_mysql_util::open_item_func( } func_name = SPIDER_SQL_COMMA_STR; func_name_length = SPIDER_SQL_COMMA_LEN; - separete_str = SPIDER_SQL_COMMA_STR; - separete_str_length = SPIDER_SQL_COMMA_LEN; + separator_str = SPIDER_SQL_COMMA_STR; + separator_str_length = SPIDER_SQL_COMMA_LEN; last_str = SPIDER_SQL_CLOSE_PAREN_STR; last_str_length = SPIDER_SQL_CLOSE_PAREN_LEN; break; @@ -4537,8 +4569,8 @@ int spider_db_mysql_util::open_item_func( } func_name = SPIDER_SQL_COMMA_STR; func_name_length = SPIDER_SQL_COMMA_LEN; - separete_str = SPIDER_SQL_COMMA_STR; - separete_str_length = SPIDER_SQL_COMMA_LEN; + separator_str = SPIDER_SQL_COMMA_STR; + separator_str_length = SPIDER_SQL_COMMA_LEN; last_str = SPIDER_SQL_CLOSE_PAREN_STR; last_str_length = SPIDER_SQL_CLOSE_PAREN_LEN; break; @@ -4571,23 +4603,39 @@ int spider_db_mysql_util::open_item_func( } DBUG_PRINT("info",("spider func_name = %s", func_name)); DBUG_PRINT("info",("spider func_name_length = %d", func_name_length)); - DBUG_PRINT("info",("spider separete_str = %s", separete_str)); - DBUG_PRINT("info",("spider separete_str_length = %d", separete_str_length)); + DBUG_PRINT("info",("spider separator_str = %s", separator_str)); + DBUG_PRINT("info",("spider separator_str_length = %d", separator_str_length)); DBUG_PRINT("info",("spider last_str = %s", last_str)); DBUG_PRINT("info",("spider last_str_length = %d", last_str_length)); + if (item_count) { + /* Find the field in the list of items of the expression tree */ + field = spider_db_find_field_in_item_list(item_list, + item_count, start_item, + str, + func_name, func_name_length); + item_count--; - for (roop_count = start_item; roop_count < item_count; roop_count++) - { - item = item_list[roop_count]; - if ((error_num = spider_db_print_item_type(item, spider, str, - alias, alias_length, dbton_id, use_fields, fields))) + + /* + Loop through the items of the current function expression to + print its portion of the statement + */ + for (loop_count = start_item; loop_count < item_count; loop_count++) + { + item = item_list[loop_count]; + if ((error_num = spider_db_print_item_type(item, field, spider, str, + alias, alias_length, + dbton_id, + use_fields, fields))) DBUG_RETURN(error_num); - if (roop_count == 1) + + if (loop_count == 1) { - func_name = separete_str; - func_name_length = separete_str_length; + /* Remaining operands need to be preceded by the separator */ + func_name = separator_str; + func_name_length = separator_str_length; } if (str) { @@ -4598,11 +4646,16 @@ int spider_db_mysql_util::open_item_func( str->q_append(SPIDER_SQL_SPACE_STR, SPIDER_SQL_SPACE_LEN); } } - item = item_list[roop_count]; - if ((error_num = spider_db_print_item_type(item, spider, str, - alias, alias_length, dbton_id, use_fields, fields))) + + /* Print the last operand value */ + item = item_list[loop_count]; + if ((error_num = spider_db_print_item_type(item, field, spider, str, + alias, alias_length, + dbton_id, + use_fields, fields))) DBUG_RETURN(error_num); } + if (item_func->functype() == Item_func::FT_FUNC) { Item_func_match *item_func_match = (Item_func_match *)item_func; @@ -4613,8 +4666,9 @@ int spider_db_mysql_util::open_item_func( str->q_append(SPIDER_SQL_AGAINST_STR, SPIDER_SQL_AGAINST_LEN); } item = item_list[0]; - if ((error_num = spider_db_print_item_type(item, spider, str, - alias, alias_length, dbton_id, use_fields, fields))) + if ((error_num = spider_db_print_item_type(item, NULL, spider, str, + alias, alias_length, dbton_id, + use_fields, fields))) DBUG_RETURN(error_num); if (str) { @@ -4701,8 +4755,11 @@ int spider_db_mysql_util::open_item_sum_func( for (roop_count = 0; roop_count < item_count; roop_count++) { item = args[roop_count]; - if ((error_num = spider_db_print_item_type(item, spider, str, - alias, alias_length, dbton_id, use_fields, fields))) + if ((error_num = spider_db_print_item_type(item, NULL, + spider, str, + alias, alias_length, + dbton_id, + use_fields, fields))) DBUG_RETURN(error_num); if (str) { @@ -4712,8 +4769,10 @@ int spider_db_mysql_util::open_item_sum_func( } } item = args[roop_count]; - if ((error_num = spider_db_print_item_type(item, spider, str, - alias, alias_length, dbton_id, use_fields, fields))) + if ((error_num = spider_db_print_item_type(item, NULL, spider, str, + alias, alias_length, + dbton_id, + use_fields, fields))) DBUG_RETURN(error_num); } if (str) @@ -7592,8 +7651,9 @@ int spider_mysql_handler::check_item_type( int error_num; DBUG_ENTER("spider_mysql_handler::check_item_type"); DBUG_PRINT("info",("spider this=%p", this)); - error_num = spider_db_print_item_type(item, spider, NULL, NULL, 0, - spider_dbton_mysql.dbton_id, FALSE, NULL); + error_num = spider_db_print_item_type(item, NULL, spider, NULL, NULL, 0, + spider_dbton_mysql.dbton_id, + FALSE, NULL); DBUG_RETURN(error_num); } @@ -8391,9 +8451,11 @@ int spider_mysql_handler::append_condition( str->q_append(SPIDER_SQL_AND_STR, SPIDER_SQL_AND_LEN); } } - if ((error_num = spider_db_print_item_type( - (Item *) tmp_cond->cond, spider, str, alias, alias_length, - spider_dbton_mysql.dbton_id, FALSE, NULL))) + if ((error_num = spider_db_print_item_type((Item *) tmp_cond->cond, + NULL, spider, str, + alias, alias_length, + spider_dbton_mysql.dbton_id, + FALSE, NULL))) { if (str && error_num == ER_SPIDER_COND_SKIP_NUM) { @@ -8715,8 +8777,10 @@ int spider_mysql_handler::append_group_by( str->q_append(SPIDER_SQL_GROUP_STR, SPIDER_SQL_GROUP_LEN); for (; group; group = group->next) { - if ((error_num = spider_db_print_item_type((*group->item), spider, str, - alias, alias_length, spider_dbton_mysql.dbton_id, FALSE, NULL))) + if ((error_num = spider_db_print_item_type((*group->item), NULL, spider, + str, alias, alias_length, + spider_dbton_mysql.dbton_id, + FALSE, NULL))) { DBUG_RETURN(error_num); } @@ -8908,8 +8972,9 @@ int spider_mysql_handler::append_key_order_for_direct_order_limit_with_alias( order = order->next) { if ((error_num = - spider_db_print_item_type((*order->item), spider, str, alias, - alias_length, spider_dbton_mysql.dbton_id, FALSE, NULL))) + spider_db_print_item_type((*order->item), NULL, spider, str, alias, + alias_length, spider_dbton_mysql.dbton_id, + FALSE, NULL))) { DBUG_PRINT("info",("spider error=%d", error_num)); DBUG_RETURN(error_num); @@ -12768,8 +12833,10 @@ int spider_mysql_handler::append_item_type_part( default: DBUG_RETURN(0); } - error_num = spider_db_print_item_type(item, spider, str, alias, alias_length, - spider_dbton_mysql.dbton_id, use_fields, fields); + error_num = spider_db_print_item_type(item, NULL, spider, str, + alias, alias_length, + spider_dbton_mysql.dbton_id, + use_fields, fields); DBUG_RETURN(error_num); } @@ -12815,8 +12882,9 @@ int spider_mysql_handler::append_list_item_select( DBUG_PRINT("info",("spider this=%p", this)); while ((item = it++)) { - if ((error_num = spider_db_print_item_type(item, spider, str, - alias, alias_length, dbton_id, use_fields, fields))) + if ((error_num = spider_db_print_item_type(item, NULL, spider, str, + alias, alias_length, dbton_id, + use_fields, fields))) { DBUG_RETURN(error_num); } @@ -12883,8 +12951,10 @@ int spider_mysql_handler::append_group_by( str->q_append(SPIDER_SQL_GROUP_STR, SPIDER_SQL_GROUP_LEN); for (; order; order = order->next) { - if ((error_num = spider_db_print_item_type((*order->item), spider, str, - alias, alias_length, dbton_id, use_fields, fields))) + if ((error_num = spider_db_print_item_type((*order->item), NULL, spider, + str, alias, alias_length, + dbton_id, + use_fields, fields))) { DBUG_RETURN(error_num); } @@ -12941,8 +13011,10 @@ int spider_mysql_handler::append_order_by( str->q_append(SPIDER_SQL_ORDER_STR, SPIDER_SQL_ORDER_LEN); for (; order; order = order->next) { - if ((error_num = spider_db_print_item_type((*order->item), spider, str, - alias, alias_length, dbton_id, use_fields, fields))) + if ((error_num = spider_db_print_item_type((*order->item), NULL, spider, + str, alias, alias_length, + dbton_id, + use_fields, fields))) { DBUG_RETURN(error_num); } diff --git a/storage/spider/spd_db_mysql.h b/storage/spider/spd_db_mysql.h index 766c15971ec..25cad01c66c 100644 --- a/storage/spider/spd_db_mysql.h +++ b/storage/spider/spd_db_mysql.h @@ -13,6 +13,8 @@ along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ +#include "tztime.h" + class spider_db_mysql_util: public spider_db_util { public: diff --git a/storage/spider/spd_db_oracle.h b/storage/spider/spd_db_oracle.h index 2e9da5cab84..6962ff4884f 100644 --- a/storage/spider/spd_db_oracle.h +++ b/storage/spider/spd_db_oracle.h @@ -13,6 +13,8 @@ along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ +#include "tztime.h" + class spider_db_oracle; class spider_db_oracle_result; diff --git a/storage/spider/spd_group_by_handler.cc b/storage/spider/spd_group_by_handler.cc index 673248d0530..a5328464bb6 100644 --- a/storage/spider/spd_group_by_handler.cc +++ b/storage/spider/spd_group_by_handler.cc @@ -1727,8 +1727,8 @@ group_by_handler *spider_create_group_by_handler( while ((item = it++)) { DBUG_PRINT("info",("spider select item=%p", item)); - if (spider_db_print_item_type(item, spider, NULL, NULL, 0, - roop_count, TRUE, fields_arg)) + if (spider_db_print_item_type(item, NULL, spider, NULL, NULL, 0, + roop_count, TRUE, fields_arg)) { DBUG_PRINT("info",("spider dbton_id=%d can't create select", roop_count)); spider_clear_bit(dbton_bitmap, roop_count); @@ -1741,8 +1741,9 @@ group_by_handler *spider_create_group_by_handler( DBUG_PRINT("info",("spider query->where=%p", query->where)); if (query->where) { - if (spider_db_print_item_type(query->where, spider, NULL, NULL, 0, - roop_count, TRUE, fields_arg)) + if (spider_db_print_item_type(query->where, NULL, spider, NULL, + NULL, 0, roop_count, + TRUE, fields_arg)) { DBUG_PRINT("info",("spider dbton_id=%d can't create where", roop_count)); spider_clear_bit(dbton_bitmap, roop_count); @@ -1757,8 +1758,9 @@ group_by_handler *spider_create_group_by_handler( { for (order = query->group_by; order; order = order->next) { - if (spider_db_print_item_type((*order->item), spider, NULL, NULL, 0, - roop_count, TRUE, fields_arg)) + if (spider_db_print_item_type((*order->item), NULL, spider, NULL, + NULL, 0, roop_count, + TRUE, fields_arg)) { DBUG_PRINT("info",("spider dbton_id=%d can't create group by", roop_count)); spider_clear_bit(dbton_bitmap, roop_count); @@ -1775,8 +1777,9 @@ group_by_handler *spider_create_group_by_handler( { for (order = query->order_by; order; order = order->next) { - if (spider_db_print_item_type((*order->item), spider, NULL, NULL, 0, - roop_count, TRUE, fields_arg)) + if (spider_db_print_item_type((*order->item), NULL, spider, NULL, + NULL, 0, roop_count, + TRUE, fields_arg)) { DBUG_PRINT("info",("spider dbton_id=%d can't create order by", roop_count)); spider_clear_bit(dbton_bitmap, roop_count); @@ -1791,8 +1794,9 @@ group_by_handler *spider_create_group_by_handler( DBUG_PRINT("info",("spider query->having=%p", query->having)); if (query->having) { - if (spider_db_print_item_type(query->having, spider, NULL, NULL, 0, - roop_count, TRUE, fields_arg)) + if (spider_db_print_item_type(query->having, NULL, spider, NULL, + NULL, 0, roop_count, + TRUE, fields_arg)) { DBUG_PRINT("info",("spider dbton_id=%d can't create having", roop_count)); spider_clear_bit(dbton_bitmap, roop_count); diff --git a/storage/spider/spd_include.h b/storage/spider/spd_include.h index bede60412e2..79e030b0872 100644 --- a/storage/spider/spd_include.h +++ b/storage/spider/spd_include.h @@ -13,6 +13,8 @@ along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ +#include "tztime.h" + #define SPIDER_DETAIL_VERSION "3.3.13" #define SPIDER_HEX_VERSION 0x0303 diff --git a/storage/spider/spd_param.cc b/storage/spider/spd_param.cc index 9a771fe8357..9c7aa4dcdcb 100644 --- a/storage/spider/spd_param.cc +++ b/storage/spider/spd_param.cc @@ -929,26 +929,6 @@ bool spider_param_sync_autocommit( } /* - FALSE: no sync - TRUE: sync - */ -static MYSQL_THDVAR_BOOL( - sync_time_zone, /* name */ - PLUGIN_VAR_OPCMDARG, /* opt */ - "Sync time_zone", /* comment */ - NULL, /* check */ - NULL, /* update */ - FALSE /* def */ -); - -bool spider_param_sync_time_zone( - THD *thd -) { - DBUG_ENTER("spider_param_sync_time_zone"); - DBUG_RETURN(THDVAR(thd, sync_time_zone)); -} - -/* FALSE: not use TRUE: use */ @@ -3332,7 +3312,6 @@ static struct st_mysql_sys_var* spider_system_variables[] = { MYSQL_SYSVAR(block_size), MYSQL_SYSVAR(selupd_lock_mode), MYSQL_SYSVAR(sync_autocommit), - MYSQL_SYSVAR(sync_time_zone), MYSQL_SYSVAR(use_default_database), MYSQL_SYSVAR(internal_sql_log_off), MYSQL_SYSVAR(bulk_size), diff --git a/storage/spider/spd_param.h b/storage/spider/spd_param.h index 9a358c54be5..06df06a3129 100644 --- a/storage/spider/spd_param.h +++ b/storage/spider/spd_param.h @@ -107,9 +107,6 @@ int spider_param_selupd_lock_mode( bool spider_param_sync_autocommit( THD *thd ); -bool spider_param_sync_time_zone( - THD *thd -); bool spider_param_use_default_database( THD *thd ); diff --git a/storage/spider/spd_trx.cc b/storage/spider/spd_trx.cc index cf60a0376bb..e09c7fa6c45 100644 --- a/storage/spider/spd_trx.cc +++ b/storage/spider/spd_trx.cc @@ -26,6 +26,7 @@ #include "sql_class.h" #include "sql_partition.h" #include "records.h" +#include "tztime.h" #endif #include "spd_err.h" #include "spd_param.h" @@ -1840,7 +1841,6 @@ int spider_internal_start_trx( SPIDER_TRX *trx = spider->trx; THD *thd = trx->thd; bool sync_autocommit = spider_param_sync_autocommit(thd); - bool sync_time_zone = spider_param_sync_time_zone(thd); double ping_interval_at_trx_start = spider_param_ping_interval_at_trx_start(thd); bool xa_lock = FALSE; @@ -1867,9 +1867,6 @@ int spider_internal_start_trx( if ( (error_num = spider_check_and_set_sql_log_off(thd, conn, &spider->need_mons[link_idx])) || - (sync_time_zone && - (error_num = spider_check_and_set_time_zone(thd, conn, - &spider->need_mons[link_idx]))) || (sync_autocommit && (error_num = spider_check_and_set_autocommit(thd, conn, &spider->need_mons[link_idx]))) |