MDEV-18428 Memory: If transactional=0 is specified in CREATE TABLE, it is not possible to ALTER TABLE

* be strict in CREATE TABLE, just like in ALTER TABLE, because
  CREATE TABLE, just like ALTER TABLE, can be rolled back for any engine
* but don't auto-convert warnings into errors for engine warnings
  (handler::create) - this matches ALTER TABLE behavior
* and not when creating a default record, these errors are handled
  specially (and replaced with ER_INVALID_DEFAULT)
* always issue a Note when a non-unique key is truncated, because it's
  not a Warning that can be converted to an Error. Before this commit
  it was a Note for blobs and a Warning for all other data types.

23 files changed, 69 insertions, 43 deletions

diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index d0755931bf7..edbceba7ee5 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -2081,3 +2081,11 @@ drop table t1;
 #
 # End of 10.0 tests
 #
+#
+# MDEV-18428 Memory: If transactional=0 is specified in CREATE TABLE, it is not possible to ALTER TABLE
+#
+create table t1 (c int(10) unsigned) engine=memory transactional=0;
+ERROR HY000: Table storage engine 'MEMORY' does not support the create option 'TRANSACTIONAL=0'
+#
+# End of 10.2 tests
+#
diff --git a/mysql-test/r/ctype_utf32.result b/mysql-test/r/ctype_utf32.result
index 7598474e493..584ca12f8c3 100644
--- a/mysql-test/r/ctype_utf32.result
+++ b/mysql-test/r/ctype_utf32.result
@@ -1306,7 +1306,7 @@ create table t1 (a varchar(334) character set utf32 primary key);
 ERROR 42000: Specified key was too long; max key length is 1000 bytes
 create table t1 (a varchar(333) character set utf32, key(a));
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 insert into t1 values (repeat('a',333)), (repeat('b',333));
 flush tables;
 check table t1;
diff --git a/mysql-test/r/ctype_utf8mb4.result b/mysql-test/r/ctype_utf8mb4.result
index bdcc07d590e..2762873b9c7 100644
--- a/mysql-test/r/ctype_utf8mb4.result
+++ b/mysql-test/r/ctype_utf8mb4.result
@@ -1478,7 +1478,7 @@ a varchar(255) NOT NULL default '',
 KEY a (a)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 insert into t1 values (_utf8mb4 0xe880bd);
 insert into t1 values (_utf8mb4 0x5b);
 select hex(a) from t1;
@@ -1526,7 +1526,7 @@ Warnings:
 Note	1051	Unknown table 'test.t1'
 CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
 INSERT INTO t1 VALUES('uu');
 check table t1;
@@ -2726,7 +2726,7 @@ DEFAULT CHARACTER SET utf8,
 MODIFY subject varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 MODIFY p varchar(255) CHARACTER SET utf8;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
diff --git a/mysql-test/r/ctype_utf8mb4_innodb.result b/mysql-test/r/ctype_utf8mb4_innodb.result
index c8fe1233b55..e9e54cb86fc 100644
--- a/mysql-test/r/ctype_utf8mb4_innodb.result
+++ b/mysql-test/r/ctype_utf8mb4_innodb.result
@@ -1438,7 +1438,7 @@ a varchar(255) NOT NULL default '',
 KEY a (a)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 767 bytes
+Note	1071	Specified key was too long; max key length is 767 bytes
 insert into t1 values (_utf8mb4 0xe880bd);
 insert into t1 values (_utf8mb4 0x5b);
 select hex(a) from t1;
@@ -1486,7 +1486,7 @@ Warnings:
 Note	1051	Unknown table 'test.t1'
 CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 767 bytes
+Note	1071	Specified key was too long; max key length is 767 bytes
 INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
 INSERT INTO t1 VALUES('uu');
 check table t1;
diff --git a/mysql-test/r/ctype_utf8mb4_myisam.result b/mysql-test/r/ctype_utf8mb4_myisam.result
index c975d805dfa..408b4f9c68b 100644
--- a/mysql-test/r/ctype_utf8mb4_myisam.result
+++ b/mysql-test/r/ctype_utf8mb4_myisam.result
@@ -1438,7 +1438,7 @@ a varchar(255) NOT NULL default '',
 KEY a (a)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 insert into t1 values (_utf8mb4 0xe880bd);
 insert into t1 values (_utf8mb4 0x5b);
 select hex(a) from t1;
@@ -1486,7 +1486,7 @@ Warnings:
 Note	1051	Unknown table 'test.t1'
 CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
 INSERT INTO t1 VALUES('uu');
 check table t1;
diff --git a/mysql-test/r/mix2_myisam.result b/mysql-test/r/mix2_myisam.result
index 34764466d2a..dbc992ece23 100644
--- a/mysql-test/r/mix2_myisam.result
+++ b/mysql-test/r/mix2_myisam.result
@@ -1989,7 +1989,7 @@ a	b
 drop table t1;
 create table t1 (v varchar(65530), key(v));
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 drop table t1;
 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 create table t1 (v varchar(65536));
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index dc99dc06b6d..1a0fb5f0c04 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -1695,7 +1695,7 @@ a	b
 drop table t1;
 create table t1 (v varchar(65530), key(v));
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 drop table if exists t1;
 set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for
 create table t1 (v varchar(65536));
@@ -1968,7 +1968,7 @@ t1	CREATE TABLE `t1` (
 drop table t1;
 create table t1 (a varchar(2048), key `a` (a));
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -1978,7 +1978,7 @@ t1	CREATE TABLE `t1` (
 drop table t1;
 create table t1 (a varchar(2048), key `a` (a) key_block_size=1024);
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -1988,7 +1988,7 @@ t1	CREATE TABLE `t1` (
 drop table t1;
 create table t1 (a int not null, b varchar(2048), key (a), key(b)) key_block_size=1024;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -2034,7 +2034,7 @@ t1	CREATE TABLE `t1` (
 drop table t1;
 create table t1 (a int not null, b varchar(2048), key (a), key(b)) key_block_size=8192;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -2046,7 +2046,7 @@ t1	CREATE TABLE `t1` (
 drop table t1;
 create table t1 (a int not null, b varchar(2048), key (a) key_block_size=1024, key(b)) key_block_size=8192;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -2076,7 +2076,7 @@ t1	CREATE TABLE `t1` (
 drop table t1;
 create table t1 (a varchar(2048), key `a` (a) key_block_size=1000000000000000000);
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -2322,6 +2322,7 @@ Key Start Len Index   Type
 1   2     30  multip. varchar              
 2   33    30  multip. char NULL            
 DROP TABLE t1;
+set statement sql_mode='' for
 create table t1 (n int not null, c char(1)) transactional=1;
 Warnings:
 Warning	1478	Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1'
diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result
index 6770b64552f..a4405882189 100644
--- a/mysql-test/r/partition_innodb.result
+++ b/mysql-test/r/partition_innodb.result
@@ -853,7 +853,7 @@ PARTITION p0 VALUES IN (1,3,9,null),
 PARTITION p1 VALUES IN (2,4,0)
 );
 Warnings:
-Warning	1071	Specified key was too long; max key length is 767 bytes
+Note	1071	Specified key was too long; max key length is 767 bytes
 INSERT INTO t2 VALUES  
 ('k','s',3,'b','j'),('a','b',NULL,'v','j'),('c','m',9,'t',NULL),
 ('b','l',9,'b',NULL),('i','y',3,'o','w'),('c','m',NULL,'a','m'),  
diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result
index 764a1b2780e..8e4210c06fb 100644
--- a/mysql-test/r/table_elim.result
+++ b/mysql-test/r/table_elim.result
@@ -544,7 +544,7 @@ drop table t0,t1,t2,t3,t4,t5,t6;
 CREATE TABLE t1 (f1 int(11), PRIMARY KEY (f1)) ;
 CREATE TABLE t2 (f4 varchar(1024), KEY (f4)) ;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 INSERT IGNORE INTO t2 VALUES ('xcddwntkbxyorzdv'),
 ('cnxxcddwntkbxyor'),('r'),('r'), ('did'),('I'),('when'),
 ('hczkfqjeggivdvac'),('e'),('okay'),('up');
diff --git a/mysql-test/suite/innodb/r/file_format_defaults.result b/mysql-test/suite/innodb/r/file_format_defaults.result
index 5ea34ceb7b5..2be4e375eb2 100644
--- a/mysql-test/suite/innodb/r/file_format_defaults.result
+++ b/mysql-test/suite/innodb/r/file_format_defaults.result
@@ -12,7 +12,7 @@ Warning	131	Using innodb_large_prefix is deprecated and the parameter may be rem
 SET SQL_MODE=strict_all_tables;
 CREATE TABLE tab0 (c1 VARCHAR(65530), KEY(c1(3073))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 3072 bytes
+Note	1071	Specified key was too long; max key length is 3072 bytes
 SHOW CREATE TABLE tab0;
 Table	Create Table
 tab0	CREATE TABLE `tab0` (
diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result
index 921f9880d47..ace226781c0 100644
--- a/mysql-test/suite/innodb/r/innodb.result
+++ b/mysql-test/suite/innodb/r/innodb.result
@@ -2302,7 +2302,7 @@ Warnings:
 Warning	131	Using innodb_large_prefix is deprecated and the parameter may be removed in future releases. See https://mariadb.com/kb/en/library/xtradbinnodb-file-format/
 create table t1 (v varchar(65530), key(v));
 Warnings:
-Warning	1071	Specified key was too long; max key length is 767 bytes
+Note	1071	Specified key was too long; max key length is 767 bytes
 SET GLOBAL innodb_large_prefix=default;
 Warnings:
 Warning	131	Using innodb_large_prefix is deprecated and the parameter may be removed in future releases. See https://mariadb.com/kb/en/library/xtradbinnodb-file-format/
@@ -2477,11 +2477,11 @@ Warning	131	Using innodb_large_prefix is deprecated and the parameter may be rem
 create table t1 (col1 varchar(768), index(col1))
 character set = latin1 engine = innodb;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 767 bytes
+Note	1071	Specified key was too long; max key length is 767 bytes
 create table t2 (col1 varbinary(768), index(col1))
 character set = latin1 engine = innodb;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 767 bytes
+Note	1071	Specified key was too long; max key length is 767 bytes
 create table t3 (col1 text, index(col1(768)))
 character set = latin1 engine = innodb;
 Warnings:
diff --git a/mysql-test/suite/innodb_zip/r/index_large_prefix.result b/mysql-test/suite/innodb_zip/r/index_large_prefix.result
index fe03586546a..d125df09ae3 100644
--- a/mysql-test/suite/innodb_zip/r/index_large_prefix.result
+++ b/mysql-test/suite/innodb_zip/r/index_large_prefix.result
@@ -407,10 +407,10 @@ ROW_FORMAT=DYNAMIC;
 SET sql_mode='';
 create index idx1 on worklog5743(a2);
 Warnings:
-Warning	1071	Specified key was too long; max key length is 3072 bytes
+Note	1071	Specified key was too long; max key length is 3072 bytes
 create index idx2 on worklog5743(a3);
 Warnings:
-Warning	1071	Specified key was too long; max key length is 3072 bytes
+Note	1071	Specified key was too long; max key length is 3072 bytes
 create index idx3 on worklog5743(a4);
 show warnings;
 Level	Code	Message
@@ -419,7 +419,7 @@ create index idx4 on worklog5743(a1, a2);
 ERROR 42000: Specified key was too long; max key length is 3072 bytes
 show warnings;
 Level	Code	Message
-Error	1071	Specified key was too long; max key length is 3072 bytes
+Note	1071	Specified key was too long; max key length is 3072 bytes
 Error	1071	Specified key was too long; max key length is 3072 bytes
 create index idx5 on worklog5743(a1, a5);
 ERROR 42000: Specified key was too long; max key length is 3072 bytes
diff --git a/mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result b/mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result
index 6cb0b09dee0..a5da38e24db 100644
--- a/mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result
+++ b/mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result
@@ -1221,7 +1221,7 @@ DROP INDEX prefix_idx ON worklog5743;
 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE INDEX prefix_idx ON worklog5743(col_1_varbinary (4000));
 Warnings:
-Warning	1071	Specified key was too long; max key length is 3072 bytes
+Note	1071	Specified key was too long; max key length is 3072 bytes
 SET sql_mode = default;
 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
 SELECT col_1_varbinary = REPEAT("a", 4000) FROM worklog5743;
diff --git a/mysql-test/suite/maria/maria-ucs2.result b/mysql-test/suite/maria/maria-ucs2.result
index 7499b37279b..73c45759373 100644
--- a/mysql-test/suite/maria/maria-ucs2.result
+++ b/mysql-test/suite/maria/maria-ucs2.result
@@ -17,7 +17,7 @@ test.t1	check	status	OK
 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 MODIFY a VARCHAR(800) CHARSET `ucs2`;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 CHECK TABLE t1;
 Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK
@@ -30,7 +30,7 @@ t1	CREATE TABLE `t1` (
 DROP TABLE t1;
 CREATE TABLE t1 (a VARCHAR(800),KEY(a)) ENGINE=Aria CHARACTER SET ucs2;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 INSERT INTO t1 VALUES (REPEAT('abc ',200));
 CHECK TABLE t1;
 Table	Op	Msg_type	Msg_text
diff --git a/mysql-test/suite/maria/maria.result b/mysql-test/suite/maria/maria.result
index 35ec098f7a8..1e85ee8b50c 100644
--- a/mysql-test/suite/maria/maria.result
+++ b/mysql-test/suite/maria/maria.result
@@ -1585,7 +1585,7 @@ a	b
 drop table t1;
 create table t1 (v varchar(65530), key(v));
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 drop table if exists t1;
 set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for
 create table t1 (v varchar(65536));
@@ -1855,7 +1855,7 @@ t1	CREATE TABLE `t1` (
 drop table t1;
 create table t1 (a varchar(2048), key `a` (a));
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -1865,7 +1865,7 @@ t1	CREATE TABLE `t1` (
 drop table t1;
 create table t1 (a varchar(2048), key `a` (a) key_block_size=1024);
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -1875,7 +1875,7 @@ t1	CREATE TABLE `t1` (
 drop table t1;
 create table t1 (a int not null, b varchar(2048), key (a), key(b)) key_block_size=1024;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -1921,7 +1921,7 @@ t1	CREATE TABLE `t1` (
 drop table t1;
 create table t1 (a int not null, b varchar(2048), key (a), key(b)) key_block_size=8192;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -1933,7 +1933,7 @@ t1	CREATE TABLE `t1` (
 drop table t1;
 create table t1 (a int not null, b varchar(2048), key (a) key_block_size=1024, key(b)) key_block_size=8192;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -1963,7 +1963,7 @@ t1	CREATE TABLE `t1` (
 drop table t1;
 create table t1 (a varchar(2048), key `a` (a) key_block_size=1000000000000000000);
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
diff --git a/mysql-test/suite/maria/maria3.result b/mysql-test/suite/maria/maria3.result
index e923039a07f..6005bedf7bb 100644
--- a/mysql-test/suite/maria/maria3.result
+++ b/mysql-test/suite/maria/maria3.result
@@ -17,7 +17,7 @@ t1	CREATE TABLE `t1` (
 drop table t1;
 create table t1 (a varchar(2048), key `a` (a) key_block_size=1000000000000000000);
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
diff --git a/mysql-test/suite/maria/mrr.result b/mysql-test/suite/maria/mrr.result
index 06be64566e5..5f9fcb4f937 100644
--- a/mysql-test/suite/maria/mrr.result
+++ b/mysql-test/suite/maria/mrr.result
@@ -393,7 +393,7 @@ PRIMARY KEY (pk),
 KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key)
 ) ENGINE=Aria;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 INSERT INTO t1 VALUES
 (1,'z'), (2,'abcdefjhjkl'), (3,'in'), (4,'abcdefjhjkl'), (6,'abcdefjhjkl'),
 (11,'zx'), (12,'abcdefjhjm'), (13,'jn'), (14,'abcdefjhjp'), (16,'abcdefjhjr');
@@ -430,7 +430,7 @@ f5 varchar(1024) COLLATE latin1_bin,
 KEY (f5)
 ) ENGINE=Aria TRANSACTIONAL=0 ;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 # Fill the table with some data
 SELECT alias2.* , alias1.f2 
 FROM 
diff --git a/mysql-test/suite/rpl/r/rpl_row_utf32.result b/mysql-test/suite/rpl/r/rpl_row_utf32.result
index af6e709860e..6d177b7cda0 100644
--- a/mysql-test/suite/rpl/r/rpl_row_utf32.result
+++ b/mysql-test/suite/rpl/r/rpl_row_utf32.result
@@ -3,7 +3,7 @@ include/master-slave.inc
 SET SQL_LOG_BIN=0;
 CREATE TABLE t1 (c1 char(255) DEFAULT NULL, KEY c1 (c1)) DEFAULT CHARSET=utf32;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 SET SQL_LOG_BIN=1;
 connection slave;
 SET @saved_slave_type_conversions= @@global.slave_type_conversions;
@@ -13,7 +13,7 @@ include/start_slave.inc
 SET SQL_LOG_BIN=0;
 CREATE TABLE t1 ( c1 varchar(255) DEFAULT NULL, KEY c1 (c1)) DEFAULT CHARSET=utf32;
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1000 bytes
+Note	1071	Specified key was too long; max key length is 1000 bytes
 SET SQL_LOG_BIN=1;
 connection master;
 INSERT INTO t1(c1) VALUES ('insert into t1');
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index 03437320ee6..396de147ec2 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -1930,3 +1930,13 @@ drop table t1;
 --echo #
 --echo # End of 10.0 tests
 --echo #
+
+--echo #
+--echo # MDEV-18428 Memory: If transactional=0 is specified in CREATE TABLE, it is not possible to ALTER TABLE
+--echo #
+--error ER_ILLEGAL_HA_CREATE_OPTION
+create table t1 (c int(10) unsigned) engine=memory transactional=0;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test
index 217789fccb2..d6efdea982f 100644
--- a/mysql-test/t/myisam.test
+++ b/mysql-test/t/myisam.test
@@ -1539,6 +1539,7 @@ DROP TABLE t1;
 # MariaDB: Note that the table will still have 'TRANSACTIONAL=1' attribute.
 # That's the intended behavior atm.
 #
+set statement sql_mode='' for
 create table t1 (n int not null, c char(1)) transactional=1;
 show create table t1;
 drop table t1;
diff --git a/sql/handler.cc b/sql/handler.cc
index 29b01763e8b..87592beb5d3 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -4847,6 +4847,7 @@ int ha_create_table(THD *thd, const char *path,
   char name_buff[FN_REFLEN];
   const char *name;
   TABLE_SHARE share;
+  Abort_on_warning_instant_set old_abort_on_warning(thd, 0);
   bool temp_table __attribute__((unused)) =
     create_info->options & (HA_LEX_CREATE_TMP_TABLE | HA_CREATE_TMP_ALTER);
                                  
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 4b62ccb7d7c..cb28c6adcec 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -3983,8 +3983,7 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
 	    {
 	      /* not a critical problem */
 	      push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
-                                  ER_TOO_LONG_KEY,
-                                  ER_THD(thd, ER_TOO_LONG_KEY),
+                                  ER_TOO_LONG_KEY, ER_THD(thd, ER_TOO_LONG_KEY),
                                   key_part_length);
               /* Align key length to multibyte char boundary */
               key_part_length-= key_part_length % sql_field->charset->mbmaxlen;
@@ -4030,7 +4029,7 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
 	if (key->type == Key::MULTIPLE)
 	{
 	  /* not a critical problem */
-	  push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+	  push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
                               ER_TOO_LONG_KEY, ER_THD(thd, ER_TOO_LONG_KEY),
                               key_part_length);
           /* Align key length to multibyte char boundary */
@@ -5133,6 +5132,9 @@ bool mysql_create_table(THD *thd, TABLE_LIST *create_table,
   if (!opt_explicit_defaults_for_timestamp)
     promote_first_timestamp_column(&alter_info->create_list);
 
+  /* We can abort create table for any table type */
+  thd->abort_on_warning= thd->is_strict_mode();
+
   if (mysql_create_table_no_lock(thd, db, table_name, create_info, alter_info,
                                  &is_trans, create_table_mode) > 0)
   {
@@ -5165,6 +5167,8 @@ bool mysql_create_table(THD *thd, TABLE_LIST *create_table,
   }
 
 err:
+  thd->abort_on_warning= 0;
+
   /* In RBR we don't need to log CREATE TEMPORARY TABLE */
   if (!result && thd->is_current_stmt_binlog_format_row() && create_info->tmp_table())
     DBUG_RETURN(result);
diff --git a/sql/unireg.cc b/sql/unireg.cc
index 9c22527581a..7974255af35 100644
--- a/sql/unireg.cc
+++ b/sql/unireg.cc
@@ -945,6 +945,7 @@ static bool make_empty_rec(THD *thd, uchar *buff, uint table_options,
   TABLE_SHARE share;
   Create_field *field;
   Check_level_instant_set old_count_cuted_fields(thd, CHECK_FIELD_WARN);
+  Abort_on_warning_instant_set old_abort_on_warning(thd, 0);
   DBUG_ENTER("make_empty_rec");
 
   /* We need a table to generate columns for default values */