Skip to content

Commit 431e84d

Browse files
GlebShchepaGlebShchepa
committed
WL#11460: Deprecate some SQL compatibility modes in 5.7
This patch is for 5.7. This WL forces deprecation warnings on setting a few compatibility modes: * DB2 * MAXDB * MSSQL * MYSQL323 * MYSQL40 * ORACLE * POSTGRESQL * NO_FIELD_OPTIONS * NO_KEY_OPTIONS * NO_TABLE_OPTIONS Also in warns on the creation of TIMESTAMP columns if the MAXDB SQL mode is "on".
1 parent 78be5ef commit 431e84d

16 files changed

+119
-1
lines changed

‎mysql-test/r/ansi.result

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@ drop table if exists t1;
22
set sql_mode="MySQL40";
33
Warnings:
44
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
5+
Warning 3090 Changing sql mode 'MYSQL40' is deprecated. It will be removed in a future release.
56
select @@sql_mode;
67
@@sql_mode
78
MYSQL40,HIGH_NOT_PRECEDENCE
@@ -27,20 +28,26 @@ t1 CREATE TABLE `t1` (
2728
PRIMARY KEY (`i`)
2829
) ENGINE=ENGINE DEFAULT CHARSET=latin1
2930
SET @@SQL_MODE="MYSQL323";
31+
Warnings:
32+
Warning 3090 Changing sql mode 'MYSQL323' is deprecated. It will be removed in a future release.
3033
SHOW CREATE TABLE t1;
3134
Table Create Table
3235
t1 CREATE TABLE `t1` (
3336
`i` int(11) NOT NULL AUTO_INCREMENT,
3437
PRIMARY KEY (`i`)
3538
) TYPE=ENGINE
3639
SET @@SQL_MODE="MYSQL40";
40+
Warnings:
41+
Warning 3090 Changing sql mode 'MYSQL40' is deprecated. It will be removed in a future release.
3742
SHOW CREATE TABLE t1;
3843
Table Create Table
3944
t1 CREATE TABLE `t1` (
4045
`i` int(11) NOT NULL AUTO_INCREMENT,
4146
PRIMARY KEY (`i`)
4247
) TYPE=ENGINE
4348
SET @@SQL_MODE="NO_FIELD_OPTIONS";
49+
Warnings:
50+
Warning 3090 Changing sql mode 'NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
4451
SHOW CREATE TABLE t1;
4552
Table Create Table
4653
t1 CREATE TABLE `t1` (

‎mysql-test/r/events_bugs.result

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -749,6 +749,8 @@ DROP USER mysqltest_u1@localhost;
749749
drop procedure if exists p;
750750
set @old_mode= @@sql_mode;
751751
set @@sql_mode= cast(pow(2,32)-1 as unsigned integer);
752+
Warnings:
753+
Warning 3090 Changing sql mode 'POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40' is deprecated. It will be removed in a future release.
752754
create event e1 on schedule every 1 day do select 1;
753755
select @@sql_mode into @full_mode;
754756
set @@sql_mode= @old_mode;

‎mysql-test/r/grant2.result

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -163,6 +163,7 @@ flush privileges;
163163
set sql_mode='maxdb';
164164
Warnings:
165165
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
166+
Warning 3090 Changing sql mode 'MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
166167
drop table if exists t1, t2;
167168
create table t1(c1 int);
168169
create table t2(c1 int, c2 int);

‎mysql-test/r/partition.result

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2132,6 +2132,7 @@ Warning 1287 The partition engine, used by table 'test.t1', is deprecated and wi
21322132
set session sql_mode='no_table_options';
21332133
Warnings:
21342134
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
2135+
Warning 3090 Changing sql mode 'NO_TABLE_OPTIONS' is deprecated. It will be removed in a future release.
21352136
show create table t1;
21362137
Table Create Table
21372138
t1 CREATE TABLE `t1` (

‎mysql-test/r/query_cache.result

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -869,6 +869,8 @@ select '1' || '3' from t1;
869869
1
870870
1
871871
set SQL_MODE=oracle;
872+
Warnings:
873+
Warning 3090 Changing sql mode 'ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
872874
select '1' || '3' from t1;
873875
'1' || '3'
874876
13

‎mysql-test/r/sp.result

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6907,6 +6907,7 @@ set @old_mode= @@sql_mode;
69076907
set @@sql_mode= cast(pow(2,32)-1 as unsigned integer);
69086908
Warnings:
69096909
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
6910+
Warning 3090 Changing sql mode 'POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40' is deprecated. It will be removed in a future release.
69106911
select @@sql_mode into @full_mode;
69116912
create procedure p() begin end;
69126913
call p();

‎mysql-test/r/sql_mode.result

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,8 @@ t1 CREATE TABLE "t1" (
3636
UNIQUE KEY "email" ("email") USING BTREE
3737
) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
3838
set @@sql_mode="no_table_options";
39+
Warnings:
40+
Warning 3090 Changing sql mode 'NO_TABLE_OPTIONS' is deprecated. It will be removed in a future release.
3941
show variables like 'sql_mode';
4042
Variable_name Value
4143
sql_mode NO_TABLE_OPTIONS
@@ -49,6 +51,8 @@ t1 CREATE TABLE `t1` (
4951
UNIQUE KEY `email` (`email`) USING BTREE
5052
)
5153
set @@sql_mode="no_key_options";
54+
Warnings:
55+
Warning 3090 Changing sql mode 'NO_KEY_OPTIONS' is deprecated. It will be removed in a future release.
5256
show variables like 'sql_mode';
5357
Variable_name Value
5458
sql_mode NO_KEY_OPTIONS
@@ -62,6 +66,8 @@ t1 CREATE TABLE `t1` (
6266
UNIQUE KEY `email` (`email`)
6367
) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
6468
set @@sql_mode="no_field_options,mysql323,mysql40";
69+
Warnings:
70+
Warning 3090 Changing sql mode 'NO_FIELD_OPTIONS,MYSQL323,MYSQL40' is deprecated. It will be removed in a future release.
6571
show variables like 'sql_mode';
6672
Variable_name Value
6773
sql_mode NO_FIELD_OPTIONS,MYSQL323,MYSQL40,HIGH_NOT_PRECEDENCE
@@ -77,6 +83,7 @@ t1 CREATE TABLE `t1` (
7783
set sql_mode="postgresql,oracle,mssql,db2,maxdb";
7884
Warnings:
7985
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
86+
Warning 3090 Changing sql mode 'POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
8087
select @@sql_mode;
8188
@@sql_mode
8289
PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
@@ -106,6 +113,8 @@ t1 CREATE TABLE `t1` (
106113
`c` binary(10) DEFAULT NULL
107114
) ENGINE=ENGINE DEFAULT CHARSET=latin1
108115
set @@sql_mode="mysql323";
116+
Warnings:
117+
Warning 3090 Changing sql mode 'MYSQL323' is deprecated. It will be removed in a future release.
109118
show create table t1;
110119
Table Create Table
111120
t1 CREATE TABLE `t1` (
@@ -114,6 +123,8 @@ t1 CREATE TABLE `t1` (
114123
`c` binary(10) DEFAULT NULL
115124
) TYPE=ENGINE
116125
set @@sql_mode="mysql40";
126+
Warnings:
127+
Warning 3090 Changing sql mode 'MYSQL40' is deprecated. It will be removed in a future release.
117128
show create table t1;
118129
Table Create Table
119130
t1 CREATE TABLE `t1` (
@@ -159,6 +170,8 @@ t1 CREATE TABLE "t1" (
159170
PRIMARY KEY ("f1")
160171
)
161172
set session sql_mode=no_field_options;
173+
Warnings:
174+
Warning 3090 Changing sql mode 'NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
162175
show create table t1;
163176
Table Create Table
164177
t1 CREATE TABLE `t1` (
@@ -482,6 +495,8 @@ select @@sql_mode;
482495
@@sql_mode
483496
STRICT_ALL_TABLES
484497
set sql_mode=16384+(65536*4);
498+
Warnings:
499+
Warning 3090 Changing sql mode 'NO_TABLE_OPTIONS' is deprecated. It will be removed in a future release.
485500
select @@sql_mode;
486501
@@sql_mode
487502
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,NO_TABLE_OPTIONS,ANSI
@@ -506,6 +521,8 @@ xb x
506521
xcx
507522
drop table t1;
508523
SET @@SQL_MODE=@OLD_SQL_MODE;
524+
Warnings:
525+
Warning 3090 Changing sql mode 'NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
509526
create user mysqltest_32753@localhost;
510527
set @OLD_SQL_MODE=@@SESSION.SQL_MODE;
511528
set session sql_mode='PAD_CHAR_TO_FULL_LENGTH';
@@ -514,6 +531,8 @@ select current_user();
514531
current_user()
515532
mysqltest_32753@localhost
516533
set session sql_mode=@OLD_SQL_MODE;
534+
Warnings:
535+
Warning 3090 Changing sql mode 'NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
517536
flush privileges;
518537
drop user mysqltest_32753@localhost;
519538
SET @org_mode=@@sql_mode;
@@ -526,6 +545,7 @@ SELECT @@sql_mode LIKE '%NO_ENGINE_SUBSTITUTION%';
526545
SET sql_mode=@org_mode;
527546
Warnings:
528547
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
548+
Warning 3090 Changing sql mode 'NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
529549
DROP TABLE IF EXISTS t1,t2;
530550
CREATE USER 'user_PCTFL'@'localhost' identified by 'PWD';
531551
CREATE USER 'user_no_PCTFL'@'localhost' identified by 'PWD';
@@ -537,6 +557,8 @@ SET @OLD_SQL_MODE = @@SESSION.SQL_MODE;
537557
SET SESSION SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH';
538558
DROP USER 'user_PCTFL'@'localhost';
539559
SET SESSION SQL_MODE = @OLD_SQL_MODE;
560+
Warnings:
561+
Warning 3090 Changing sql mode 'NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
540562
DROP USER 'user_no_PCTFL'@'localhost';
541563
FLUSH PRIVILEGES;
542564
SELECT * FROM mysql.db WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL';
@@ -749,6 +771,8 @@ wxyz\_ef
749771
DROP TABLE test_table;
750772
DROP FUNCTION test_function;
751773
SET @@sql_mode= @org_mode;
774+
Warnings:
775+
Warning 3090 Changing sql mode 'NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
752776

753777
#End of Test for Bug#12601974
754778
#
@@ -781,6 +805,8 @@ ERROR_FOR_DIVISION_BY_ZERO
781805
# Restore sql mode
782806
#
783807
SET sql_mode= @org_mode;
808+
Warnings:
809+
Warning 3090 Changing sql mode 'NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
784810
#
785811
# Check that NO_ZERO_DATE, NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO modes
786812
# are removed in triggers after upgrading to 5.7.

‎mysql-test/r/type_timestamp.result

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -407,7 +407,11 @@ drop table t1;
407407
set sql_mode='maxdb';
408408
Warnings:
409409
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
410+
Warning 3090 Changing sql mode 'MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
410411
create table t1 (a timestamp, b timestamp);
412+
Warnings:
413+
Warning 3226 With the MAXDB SQL mode enabled, TIMESTAMP is identical with DATETIME. The MAXDB SQL mode is deprecated and will be removed in a future release. Please disable the MAXDB SQL mode and use DATETIME instead.
414+
Warning 3226 With the MAXDB SQL mode enabled, TIMESTAMP is identical with DATETIME. The MAXDB SQL mode is deprecated and will be removed in a future release. Please disable the MAXDB SQL mode and use DATETIME instead.
411415
show create table t1;
412416
Table Create Table
413417
t1 CREATE TABLE "t1" (

‎mysql-test/r/type_timestamp_explicit.result

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -405,7 +405,11 @@ drop table t1;
405405
set sql_mode='maxdb';
406406
Warnings:
407407
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
408+
Warning 3090 Changing sql mode 'MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
408409
create table t1 (a timestamp, b timestamp);
410+
Warnings:
411+
Warning 3226 With the MAXDB SQL mode enabled, TIMESTAMP is identical with DATETIME. The MAXDB SQL mode is deprecated and will be removed in a future release. Please disable the MAXDB SQL mode and use DATETIME instead.
412+
Warning 3226 With the MAXDB SQL mode enabled, TIMESTAMP is identical with DATETIME. The MAXDB SQL mode is deprecated and will be removed in a future release. Please disable the MAXDB SQL mode and use DATETIME instead.
409413
show create table t1;
410414
Table Create Table
411415
t1 CREATE TABLE "t1" (

‎mysql-test/suite/innodb/r/optimizer_temporary_table.result

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4130,6 +4130,8 @@ select count(*) from t0;
41304130
count(*)
41314131
0
41324132
set @@session.sql_mode = MAXDB;
4133+
Warnings:
4134+
Warning 3090 Changing sql mode 'MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
41334135
set @@session.tmp_table_size = 0;
41344136
Warnings:
41354137
Warning 1292 Truncated incorrect tmp_table_size value: '0'

‎mysql-test/suite/rpl/r/rpl_loaddatalocal.result

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -85,6 +85,7 @@ LOAD/*!99999 special comments that do not expand */DATA/*!99999 code from the fu
8585
SET sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,TRADITIONAL,NO_AUTO_CREATE_USER';
8686
Warnings:
8787
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
88+
Warning 3090 Changing sql mode 'NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
8889
LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1;
8990
[slave]
9091
include/sync_slave_sql_with_master.inc

‎mysql-test/suite/rpl/r/rpl_variables.result

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -146,6 +146,7 @@ Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be r
146146
SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS';
147147
Warnings:
148148
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
149+
Warning 3090 Changing sql mode 'NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
149150
INSERT INTO tstmt(text) VALUES (@@global.sql_mode);
150151
INSERT INTO tstmt(text) VALUES (@@local.sql_mode);
151152
---- user variables ----
@@ -543,6 +544,7 @@ Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be r
543544
EXECUTE p54;
544545
Warnings:
545546
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
547+
Warning 3090 Changing sql mode 'NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
546548
EXECUTE p55;
547549
EXECUTE p56;
548550
EXECUTE p57;

0 commit comments

Comments
 (0)