⚝
One Hat Cyber Team
⚝
Your IP:
216.73.216.124
Server IP:
50.28.103.30
Server:
Linux host.jcukjv-lwsites.com 4.18.0-553.22.1.el8_10.x86_64 #1 SMP Tue Sep 24 05:16:59 EDT 2024 x86_64
Server Software:
nginx/1.28.0
PHP Version:
8.3.12
Buat File
|
Buat Folder
Eksekusi
Dir :
~
/
www
/
server
/
mysql
/
mysql-test
/
r
/
View File Name :
type_temporal_upgrade.result
DROP TABLE IF EXISTS t1; SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; Warnings: Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. # # Test with a saved table from 5.5 # SET time_zone='+03:00'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f_time` time DEFAULT NULL, `f_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `f_datetime` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (f_time, f_datetime, f_timestamp) VALUES (9999999, 999999999999999, 999999999999999); Warnings: Warning 1264 Out of range value for column 'f_time' at row 1 Warning 1264 Out of range value for column 'f_datetime' at row 1 Warning 1264 Out of range value for column 'f_timestamp' at row 1 INSERT INTO t1 (f_time, f_datetime, f_timestamp) VALUES (-9999999, -999999999999999, -999999999999999); Warnings: Warning 1264 Out of range value for column 'f_time' at row 1 Warning 1265 Data truncated for column 'f_datetime' at row 1 Warning 1264 Out of range value for column 'f_timestamp' at row 1 INSERT INTO t1 (f_time, f_datetime, f_timestamp) VALUES ('00:00:00', '0000-00-00 00:00:00','0000-00-00 00:00:00'); SELECT * FROM t1; f_time f_timestamp f_datetime 10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10 00:00:00 2001-01-21 18:11:01 2000-01-01 01:01:01 01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01 NULL 2015-11-21 17:11:01 2000-01-01 01:01:01 00:00:00 2011-11-21 17:11:01 NULL 838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00 -838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 SELECT * FROM t1 ORDER BY f_time; f_time f_timestamp f_datetime NULL 2015-11-21 17:11:01 2000-01-01 01:01:01 -838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 2001-01-21 18:11:01 2000-01-01 01:01:01 00:00:00 2011-11-21 17:11:01 NULL 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01 10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10 838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00 SELECT * FROM t1 WHERE f_time > '00:00:00'; f_time f_timestamp f_datetime 10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10 01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01 838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00 SELECT * FROM t1 WHERE f_time > '00:00:00' ORDER BY f_time; f_time f_timestamp f_datetime 01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01 10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10 838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00 SELECT * FROM t1 WHERE f_time = '01:01:10'; f_time f_timestamp f_datetime 01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01 SELECT * FROM t1 WHERE f_time IS NULL; f_time f_timestamp f_datetime NULL 2015-11-21 17:11:01 2000-01-01 01:01:01 SELECT COUNT(*), MIN(f_time) FROM t1 GROUP BY f_time; COUNT(*) MIN(f_time) 1 NULL 1 -838:59:59 3 00:00:00 1 01:01:10 1 10:10:10 1 838:59:59 SELECT * FROM t1 ORDER BY f_timestamp; f_time f_timestamp f_datetime 838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00 -838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01 00:00:00 2001-01-21 18:11:01 2000-01-01 01:01:01 00:00:00 2011-11-21 17:11:01 NULL NULL 2015-11-21 17:11:01 2000-01-01 01:01:01 10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10 SELECT * FROM t1 WHERE f_timestamp > '2011-11-21 17:11:01'; f_time f_timestamp f_datetime 10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10 NULL 2015-11-21 17:11:01 2000-01-01 01:01:01 SELECT * FROM t1 WHERE f_timestamp > '2011-11-21 17:11:01' ORDER BY f_timestamp; f_time f_timestamp f_datetime NULL 2015-11-21 17:11:01 2000-01-01 01:01:01 10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10 SELECT * FROM t1 WHERE f_timestamp = '2015-11-21 17:11:01'; f_time f_timestamp f_datetime NULL 2015-11-21 17:11:01 2000-01-01 01:01:01 SELECT * FROM t1 WHERE f_timestamp IS NULL; f_time f_timestamp f_datetime SELECT COUNT(*), MIN(f_timestamp) FROM t1 GROUP BY f_timestamp; COUNT(*) MIN(f_timestamp) 3 0000-00-00 00:00:00 1 1980-11-21 18:06:01 1 2001-01-21 18:11:01 1 2011-11-21 17:11:01 1 2015-11-21 17:11:01 1 2031-11-21 17:11:01 SELECT UNIX_TIMESTAMP(f_timestamp) FROM t1 ORDER BY f_timestamp; UNIX_TIMESTAMP(f_timestamp) 0 0 0 343667161 980089861 1321884661 1448115061 1953036661 SELECT * FROM t1 ORDER BY f_datetime; f_time f_timestamp f_datetime 00:00:00 2011-11-21 17:11:01 NULL 838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00 -838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 2001-01-21 18:11:01 2000-01-01 01:01:01 NULL 2015-11-21 17:11:01 2000-01-01 01:01:01 10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10 01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01 SELECT * FROM t1 WHERE f_datetime > '2000-01-01 01:01:01'; f_time f_timestamp f_datetime 10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10 01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01 SELECT * FROM t1 WHERE f_datetime > '2000-01-01 01:01:01' ORDER BY f_datetime; f_time f_timestamp f_datetime 10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10 01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01 SELECT * FROM t1 WHERE f_datetime = '2010-10-10 10:10:10'; f_time f_timestamp f_datetime 10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10 SELECT * FROM t1 WHERE f_datetime IS NULL; f_time f_timestamp f_datetime 00:00:00 2011-11-21 17:11:01 NULL SELECT COUNT(*), MIN(f_datetime) FROM t1 GROUP BY f_datetime; COUNT(*) MIN(f_datetime) 1 NULL 3 0000-00-00 00:00:00 2 2000-01-01 01:01:01 1 2010-10-10 10:10:10 1 2020-01-01 01:01:01 # # Checking various val_xxx() methods for the old fields # SELECT CAST(f_time AS CHAR), CAST(f_time AS SIGNED), CAST(f_time AS DECIMAL(23,6)) FROM t1; CAST(f_time AS CHAR) CAST(f_time AS SIGNED) CAST(f_time AS DECIMAL(23,6)) 10:10:10 101010 101010.000000 00:00:00 0 0.000000 01:01:10 10110 10110.000000 NULL NULL NULL 00:00:00 0 0.000000 838:59:59 8385959 8385959.000000 -838:59:59 -8385959 -8385959.000000 00:00:00 0 0.000000 SELECT CAST(f_datetime AS CHAR), CAST(f_datetime AS SIGNED), CAST(f_datetime AS DECIMAL(23,6)) FROM t1; CAST(f_datetime AS CHAR) CAST(f_datetime AS SIGNED) CAST(f_datetime AS DECIMAL(23,6)) 2010-10-10 10:10:10 20101010101010 20101010101010.000000 2000-01-01 01:01:01 20000101010101 20000101010101.000000 2020-01-01 01:01:01 20200101010101 20200101010101.000000 2000-01-01 01:01:01 20000101010101 20000101010101.000000 NULL NULL NULL 0000-00-00 00:00:00 0 0.000000 0000-00-00 00:00:00 0 0.000000 0000-00-00 00:00:00 0 0.000000 SELECT CAST(f_timestamp AS CHAR), CAST(f_timestamp AS SIGNED), CAST(f_timestamp AS DECIMAL(23,6)) FROM t1; CAST(f_timestamp AS CHAR) CAST(f_timestamp AS SIGNED) CAST(f_timestamp AS DECIMAL(23,6)) 2031-11-21 17:11:01 20311121171101 20311121171101.000000 2001-01-21 18:11:01 20010121181101 20010121181101.000000 1980-11-21 18:06:01 19801121180601 19801121180601.000000 2015-11-21 17:11:01 20151121171101 20151121171101.000000 2011-11-21 17:11:01 20111121171101 20111121171101.000000 0000-00-00 00:00:00 0 0.000000 0000-00-00 00:00:00 0 0.000000 0000-00-00 00:00:00 0 0.000000 # # Checking old table # INSERT INTO t1 VALUES ('22:22:22.5','2011-11-21 22:22:22.5','2011-11-21 22:22:22.5'); SELECT f_time, HEX(WEIGHT_STRING(f_time)) FROM t1; f_time HEX(WEIGHT_STRING(f_time)) 10:10:10 818A92 00:00:00 800000 01:01:10 80277E NULL NULL 00:00:00 800000 838:59:59 FFF5A7 -838:59:59 000A59 00:00:00 800000 22:22:23 83640F SELECT f_timestamp, HEX(WEIGHT_STRING(f_timestamp)) FROM t1; f_timestamp HEX(WEIGHT_STRING(f_timestamp)) 2031-11-21 17:11:01 7468F975 2001-01-21 18:11:01 3A6AFC05 1980-11-21 18:06:01 147BF1D9 2015-11-21 17:11:01 56507B75 2011-11-21 17:11:01 4ECA5BF5 0000-00-00 00:00:00 00000000 0000-00-00 00:00:00 00000000 0000-00-00 00:00:00 00000000 2011-11-21 22:22:23 4ECAA4EF SELECT f_datetime, HEX(WEIGHT_STRING(f_datetime)) FROM t1; f_datetime HEX(WEIGHT_STRING(f_datetime)) 2010-10-10 10:10:10 0000124821911312 2000-01-01 01:01:01 00001230A2EA8AB5 2020-01-01 01:01:01 0000125F33D85AB5 2000-01-01 01:01:01 00001230A2EA8AB5 NULL NULL 0000-00-00 00:00:00 0000000000000000 0000-00-00 00:00:00 0000000000000000 0000-00-00 00:00:00 0000000000000000 2011-11-21 22:22:23 0000124A7C3C8A4F # # This ALTER should change old fields to the new fields # Expect new WEIGHT_STRING for the TIME and DATETIME fields # CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22'); ALTER TABLE t2 FORCE; Warnings: Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format. SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)), HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2; f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp)) 22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 816596 998AAB6596 4ECAA4EE DROP TABLE t2; # # This ALTER should change old fields to new fields # Expecting new WEIGHT_STRING the rounding. # ALTER TABLE t1 MODIFY f_time TIME, MODIFY f_timestamp TIMESTAMP, MODIFY f_datetime DATETIME; INSERT INTO t1 VALUES ('22:22:22.5','2011-11-21 22:22:22.5','2011-11-21 22:22:22.5'); SELECT f_time, HEX(WEIGHT_STRING(f_time)) FROM t1; f_time HEX(WEIGHT_STRING(f_time)) 10:10:10 80A28A 00:00:00 800000 01:01:10 80104A NULL NULL 00:00:00 800000 838:59:59 B46EFB -838:59:59 4B9105 00:00:00 800000 22:22:23 816597 22:22:23 816597 SELECT f_timestamp, HEX(WEIGHT_STRING(f_timestamp)) FROM t1; f_timestamp HEX(WEIGHT_STRING(f_timestamp)) 2031-11-21 17:11:01 7468F975 2001-01-21 18:11:01 3A6AFC05 1980-11-21 18:06:01 147BF1D9 2015-11-21 17:11:01 56507B75 2011-11-21 17:11:01 4ECA5BF5 0000-00-00 00:00:00 00000000 0000-00-00 00:00:00 00000000 0000-00-00 00:00:00 00000000 2011-11-21 22:22:23 4ECAA4EF 2011-11-21 22:22:23 4ECAA4EF SELECT f_datetime, HEX(WEIGHT_STRING(f_datetime)) FROM t1; f_datetime HEX(WEIGHT_STRING(f_datetime)) 2010-10-10 10:10:10 998714A28A 2000-01-01 01:01:01 9964421041 2020-01-01 01:01:01 99A5421041 2000-01-01 01:01:01 9964421041 NULL NULL 0000-00-00 00:00:00 8000000000 0000-00-00 00:00:00 8000000000 0000-00-00 00:00:00 8000000000 2011-11-21 22:22:23 998AAB6597 2011-11-21 22:22:23 998AAB6597 DROP TABLE t1; SET time_zone=DEFAULT; SET sql_mode = default; # # BUG#18985579: "CHECK TABLE .. FOR UPGRADE" FAILS TO REPORT OLD # DATETIME FORMAT. # #Setup. CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22'); #Test case with 'avoid_temporal_upgrade' disabled. #Reports an error to repair the table. CHECK TABLE t1 FOR UPGRADE; Table Op Msg_type Msg_text test.t1 check error Table upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! #REPAIR upgrades the old temporal types. SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)), HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t1; f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp)) 10:10:10 2010-10-10 10:10:10 2031-11-21 17:11:01 818A92 0000124821911312 7468F975 00:00:00 2000-01-01 01:01:01 2001-01-21 18:11:01 800000 00001230A2EA8AB5 3A6AFC05 01:01:10 2020-01-01 01:01:01 1980-11-21 18:06:01 80277E 0000125F33D85AB5 147BF1D9 NULL 2000-01-01 01:01:01 2015-11-21 17:11:01 NULL 00001230A2EA8AB5 56507B75 00:00:00 NULL 2011-11-21 17:11:01 800000 NULL 4ECA5BF5 REPAIR TABLE t1; Table Op Msg_type Msg_text test.t1 repair Note TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format. test.t1 repair status OK SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)), HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t1; f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp)) 10:10:10 2010-10-10 10:10:10 2031-11-21 17:11:01 80A28A 998714A28A 7468F975 00:00:00 2000-01-01 01:01:01 2001-01-21 18:11:01 800000 9964421041 3A6AFC05 01:01:10 2020-01-01 01:01:01 1980-11-21 18:06:01 80104A 99A5421041 147BF1D9 NULL 2000-01-01 01:01:01 2015-11-21 17:11:01 NULL 9964421041 56507B75 00:00:00 NULL 2011-11-21 17:11:01 800000 NULL 4ECA5BF5 #Cleanup. DROP TABLE t1; #Test case with 'avoid_temporal_upgrade' enabled. SET @save_avoid_temporal_upgrade= @@global.avoid_temporal_upgrade; Warnings: Warning 1287 '@@avoid_temporal_upgrade' is deprecated and will be removed in a future release. SET GLOBAL avoid_temporal_upgrade= ON; Warnings: Warning 1287 '@@avoid_temporal_upgrade' is deprecated and will be removed in a future release. #Does not report an error to repair the table. CHECK TABLE t2 FOR UPGRADE; Table Op Msg_type Msg_text test.t2 check status OK #REPAIR TABLE does not upgrade the old temporal types. SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)), HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2; f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp)) 22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE REPAIR TABLE t2; Table Op Msg_type Msg_text test.t2 repair status OK SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)), HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2; f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp)) 22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE #Cleanup. DROP TABLE t2; SET @@global.avoid_temporal_upgrade= @save_avoid_temporal_upgrade; Warnings: Warning 1287 '@@avoid_temporal_upgrade' is deprecated and will be removed in a future release.