Enable warnings for 'no default' fields being set to default when they

are not specified in an insert. Most of these changes are actually to
clean up the test suite to either specify defaults to avoid warnings,
or add the warnings to the results. Related to bug #5986.


mysql-test/t/index_merge_ror.test:
  Set default on columns to avoid warnings
mysql-test/t/ctype_many.test:
  Add defaults to avoid warnings
mysql-test/t/ctype_latin1_de.test:
  Add defaults to avoid warnings
mysql-test/t/gis-rtree.test:
  Remove field that isn't used in test
mysql-test/t/limit.test:
  set default values
mysql-test/t/null.test:
  Specify default values
mysql-test/t/order_by.test:
  add default value
mysql-test/t/show_check.test:
  Add defaults
mysql-test/t/sp.test:
  Add default value
mysql-test/t/type_blob.test:
  Set default value
mysql-test/t/type_ranges.test:
  Set default values
mysql-test/r/create.result:
  Update results
mysql-test/r/ctype_latin1_de.result:
  Update results
mysql-test/r/ctype_many.result:
  Update results
mysql-test/r/gis-rtree.result:
  Update results
mysql-test/r/grant.result:
  Update results
mysql-test/r/grant2.result:
  Update results
mysql-test/r/help.result:
  Update results
mysql-test/r/limit.result:
  Update results
mysql-test/r/null.result:
  Update results
mysql-test/r/order_by.result:
  Update results
mysql-test/r/rpl000001.result:
  Update results
mysql-test/r/sp.result:
  Update results
mysql-test/r/strict.result:
  Update results
mysql-test/r/type_blob.result:
  Update results
mysql-test/r/type_ranges.result:
  Update results
mysql-test/r/warnings.result:
  Update results
sql/sql_insert.cc:
  Change check_that_all_fields_are_given_values() to issue warnings for all
  fields, and not only run when thd->abort_on_warning is set and error on
  just the first field.

28 files changed, 93 insertions, 52 deletions

diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index f2e91c36f75..0082feeb67b 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -255,11 +255,15 @@ drop table t1;
 create table t1 (a int not null, b int, primary key (a));
 insert into t1 values (1,1);
 create table if not exists t1 select 2;
+Warnings:
+Warning	1364	Field 'a' doesn't have a default value
 select * from t1;
 a	b
 1	1
 0	2
 create table if not exists t1 select 3 as 'a',4 as 'b';
+Warnings:
+Warning	1364	Field 'a' doesn't have a default value
 create table if not exists t1 select 3 as 'a',3 as 'b';
 ERROR 23000: Duplicate entry '3' for key 1
 select * from t1;
diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result
index f890250e0a3..055b36c0daf 100644
--- a/mysql-test/r/ctype_latin1_de.result
+++ b/mysql-test/r/ctype_latin1_de.result
@@ -220,12 +220,12 @@ select * from t1 where match a against ("te*" in boolean mode)+0;
 a
 test
 drop table t1;
-create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word));
+create table t1 (word varchar(255) not null, word2 varchar(255) not null default '', index(word));
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `word` varchar(255) collate latin1_german2_ci NOT NULL,
-  `word2` varchar(255) collate latin1_german2_ci NOT NULL,
+  `word2` varchar(255) collate latin1_german2_ci NOT NULL default '',
   KEY `word` (`word`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
 insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae');
diff --git a/mysql-test/r/ctype_many.result b/mysql-test/r/ctype_many.result
index 5932bd75787..87b83acfe0f 100644
--- a/mysql-test/r/ctype_many.result
+++ b/mysql-test/r/ctype_many.result
@@ -2,27 +2,27 @@ DROP TABLE IF EXISTS t1;
 SET CHARACTER SET latin1;
 CREATE TABLE t1 (
 comment CHAR(32) ASCII NOT NULL,
-koi8_ru_f CHAR(32) CHARACTER SET koi8r NOT NULL
+koi8_ru_f CHAR(32) CHARACTER SET koi8r NOT NULL default ''
 ) CHARSET=latin5;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `comment` char(32) character set latin1 NOT NULL,
-  `koi8_ru_f` char(32) character set koi8r NOT NULL
+  `koi8_ru_f` char(32) character set koi8r NOT NULL default ''
 ) ENGINE=MyISAM DEFAULT CHARSET=latin5
 ALTER TABLE t1 CHANGE comment comment CHAR(32) CHARACTER SET latin2 NOT NULL;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `comment` char(32) character set latin2 NOT NULL,
-  `koi8_ru_f` char(32) character set koi8r NOT NULL
+  `koi8_ru_f` char(32) character set koi8r NOT NULL default ''
 ) ENGINE=MyISAM DEFAULT CHARSET=latin5
 ALTER TABLE t1 ADD latin5_f CHAR(32) NOT NULL;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `comment` char(32) character set latin2 NOT NULL,
-  `koi8_ru_f` char(32) character set koi8r NOT NULL,
+  `koi8_ru_f` char(32) character set koi8r NOT NULL default '',
   `latin5_f` char(32) NOT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin5
 ALTER TABLE t1 DEFAULT CHARSET=latin2;
@@ -31,7 +31,7 @@ SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `comment` char(32) NOT NULL,
-  `koi8_ru_f` char(32) character set koi8r NOT NULL,
+  `koi8_ru_f` char(32) character set koi8r NOT NULL default '',
   `latin5_f` char(32) character set latin5 NOT NULL,
   `latin2_f` char(32) NOT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin2
@@ -40,7 +40,7 @@ SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `comment` char(32) NOT NULL,
-  `koi8_ru_f` char(32) character set koi8r NOT NULL
+  `koi8_ru_f` char(32) character set koi8r NOT NULL default ''
 ) ENGINE=MyISAM DEFAULT CHARSET=latin2
 INSERT INTO t1 (koi8_ru_f,comment) VALUES ('a','LAT SMALL A');
 INSERT INTO t1 (koi8_ru_f,comment) VALUES ('b','LAT SMALL B');
@@ -219,7 +219,7 @@ z	LAT CAPIT Z	2
 э	CYR CAPIT E	2
 ю	CYR CAPIT YU	2
 я	CYR CAPIT YA	2
-ALTER TABLE t1 ADD utf8_f CHAR(32) CHARACTER SET utf8 NOT NULL;
+ALTER TABLE t1 ADD utf8_f CHAR(32) CHARACTER SET utf8 NOT NULL default '';
 UPDATE t1 SET utf8_f=CONVERT(koi8_ru_f USING utf8);
 SET CHARACTER SET koi8r;
 SELECT * FROM t1;
@@ -340,7 +340,7 @@ CYR CAPIT SOFT SIGN	�	�
 CYR CAPIT E	�	�
 CYR CAPIT YU	�	�
 CYR CAPIT YA	�	�
-ALTER TABLE t1 ADD bin_f CHAR(32) BYTE NOT NULL;
+ALTER TABLE t1 ADD bin_f CHAR(32) BYTE NOT NULL default '';
 UPDATE t1 SET bin_f=koi8_ru_f;
 SELECT COUNT(DISTINCT bin_f),COUNT(DISTINCT koi8_ru_f),COUNT(DISTINCT utf8_f) FROM t1;
 COUNT(DISTINCT bin_f)	COUNT(DISTINCT koi8_ru_f)	COUNT(DISTINCT utf8_f)
diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result
index efd3b0f8e15..0e558e47594 100644
--- a/mysql-test/r/gis-rtree.result
+++ b/mysql-test/r/gis-rtree.result
@@ -758,7 +758,6 @@ SPATIAL KEY(g)
 INSERT INTO t1 (g) VALUES (GeomFromText('LineString(1 2, 2 3)')),(GeomFromText('LineString(1 2, 2 4)'));
 drop table t1;
 CREATE TABLE t1 (
-geoobjid INT NOT NULL,
 line LINESTRING NOT NULL,
 kind ENUM('po', 'pp', 'rr', 'dr', 'rd', 'ts', 'cl') NOT NULL DEFAULT 'po',
 name VARCHAR(32),
diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result
index e5f7f535ae2..2eaf30f802d 100644
--- a/mysql-test/r/grant.result
+++ b/mysql-test/r/grant.result
@@ -157,6 +157,10 @@ select 1;
 1
 1
 insert into mysql.user (host, user) values ('localhost', 'test11');
+Warnings:
+Warning	1364	Field 'ssl_cipher' doesn't have a default value
+Warning	1364	Field 'x509_issuer' doesn't have a default value
+Warning	1364	Field 'x509_subject' doesn't have a default value
 insert into mysql.db (host, db, user, select_priv) values 
 ('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y');
 alter table mysql.db order by db asc;
diff --git a/mysql-test/r/grant2.result b/mysql-test/r/grant2.result
index 0779e2adfa2..dedf5935743 100644
--- a/mysql-test/r/grant2.result
+++ b/mysql-test/r/grant2.result
@@ -225,6 +225,10 @@ host	user	password
 %	mysqltest_2	*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1
 localhost	mysqltest_3	
 insert into mysql.user set host='%', user='mysqltest_B';
+Warnings:
+Warning	1364	Field 'ssl_cipher' doesn't have a default value
+Warning	1364	Field 'x509_issuer' doesn't have a default value
+Warning	1364	Field 'x509_subject' doesn't have a default value
 create user mysqltest_A@'%';
 ERROR 42000: Access denied for user 'mysqltest_3'@'localhost' to database 'mysql'
 rename user mysqltest_B@'%' to mysqltest_C@'%';
diff --git a/mysql-test/r/help.result b/mysql-test/r/help.result
index edf7d0e91cb..85ca832828d 100644
--- a/mysql-test/r/help.result
+++ b/mysql-test/r/help.result
@@ -1,32 +1,48 @@
 insert into mysql.help_category(help_category_id,name)values(1,'impossible_category_1');
+Warnings:
+Warning	1364	Field 'url' doesn't have a default value
 select @category1_id:= 1;
 @category1_id:= 1
 1
 insert into mysql.help_category(help_category_id,name)values(2,'impossible_category_2');
+Warnings:
+Warning	1364	Field 'url' doesn't have a default value
 select @category2_id:= 2;
 @category2_id:= 2
 2
 insert into mysql.help_category(help_category_id,name,parent_category_id)values(3,'impossible_category_3',@category2_id);
+Warnings:
+Warning	1364	Field 'url' doesn't have a default value
 select @category3_id:= 3;
 @category3_id:= 3
 3
 insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(1,'impossible_function_1',@category1_id,'description of \n impossible_function1\n','example of \n impossible_function1');
+Warnings:
+Warning	1364	Field 'url' doesn't have a default value
 select @topic1_id:= 1;
 @topic1_id:= 1
 1
 insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(2,'impossible_function_2',@category1_id,'description of \n impossible_function2\n','example of \n impossible_function2');
+Warnings:
+Warning	1364	Field 'url' doesn't have a default value
 select @topic2_id:= 2;
 @topic2_id:= 2
 2
 insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(3,'impossible_function_3',@category2_id,'description of \n impossible_function3\n','example of \n impossible_function3');
+Warnings:
+Warning	1364	Field 'url' doesn't have a default value
 select @topic3_id:= 3;
 @topic3_id:= 3
 3
 insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(4,'impossible_function_4',@category2_id,'description of \n impossible_function4\n','example of \n impossible_function4');
+Warnings:
+Warning	1364	Field 'url' doesn't have a default value
 select @topic4_id:= 4;
 @topic4_id:= 4
 4
 insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(5,'impossible_function_7',@category3_id,'description of \n impossible_function5\n','example of \n impossible_function7');
+Warnings:
+Warning	1364	Field 'url' doesn't have a default value
 select @topic5_id:= 5;
 @topic5_id:= 5
 5
diff --git a/mysql-test/r/limit.result b/mysql-test/r/limit.result
index c82105e6a49..8d20a191f1c 100644
--- a/mysql-test/r/limit.result
+++ b/mysql-test/r/limit.result
@@ -1,5 +1,5 @@
 drop table if exists t1;
-create table t1 (a int primary key, b int not null);
+create table t1 (a int not null default 0 primary key, b int not null default 0);
 insert into t1 () values ();
 insert into t1 values (1,1),(2,1),(3,1);
 update t1 set a=4 where b=1 limit 1;
diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result
index 9f05703e69e..806e2b6f254 100644
--- a/mysql-test/r/null.result
+++ b/mysql-test/r/null.result
@@ -88,7 +88,7 @@ b	ifnull(t2.b,"this is null")
 NULL	this is null
 NULL	this is null
 drop table t1;
-CREATE TABLE t1 (a varchar(16) NOT NULL, b smallint(6) NOT NULL, c datetime NOT NULL, d smallint(6) NOT NULL);
+CREATE TABLE t1 (a varchar(16) NOT NULL default '', b smallint(6) NOT NULL default '', c datetime NOT NULL default '0000-00-00 00:00:00', d smallint(6) NOT NULL default 0);
 INSERT INTO t1 SET a = "", d= "2003-01-14 03:54:55";
 Warnings:
 Warning	1265	Data truncated for column 'd' at row 1
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 6744ffa889f..0a5efed7a25 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -257,7 +257,7 @@ favo_tijdschrift varchar(50) NOT NULL default '',
 favo_tv varchar(50) NOT NULL default '',
 favo_eten varchar(50) NOT NULL default '',
 favo_muziek varchar(30) NOT NULL default '',
-info text NOT NULL,
+info text NOT NULL default '',
 ipnr varchar(30) NOT NULL default '',
 PRIMARY KEY  (member_id)
 ) ENGINE=MyISAM PACK_KEYS=1;
diff --git a/mysql-test/r/rpl000001.result b/mysql-test/r/rpl000001.result
index eef986d8f8c..434e10160a4 100644
--- a/mysql-test/r/rpl000001.result
+++ b/mysql-test/r/rpl000001.result
@@ -76,6 +76,10 @@ create table t1 (n int);
 insert into t1 values(3456);
 insert into mysql.user (Host, User, Password)
 VALUES ("10.10.10.%", "blafasel2", password("blafasel2"));
+Warnings:
+Warning	1364	Field 'ssl_cipher' doesn't have a default value
+Warning	1364	Field 'x509_issuer' doesn't have a default value
+Warning	1364	Field 'x509_subject' doesn't have a default value
 select select_priv,user from mysql.user where user = _binary'blafasel2';
 select_priv	user
 N	blafasel2
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 1fd519bc729..a887152705b 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -1,7 +1,7 @@
 use test;
 drop table if exists t1;
 create table t1 (
-id   char(16) not null,
+id   char(16) not null default '',
 data int not null
 );
 drop table if exists t2;
@@ -1725,7 +1725,7 @@ tinyint	1	-128	127	0	0	YES	YES	NO	YES	YES	NO	NULL,0	A very small integer
 tinyint unsigned	1	0	255	0	0	YES	YES	YES	YES	YES	NO	NULL,0	A very small integer
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `id` char(16) NOT NULL,
+  `id` char(16) NOT NULL default '',
   `data` int(11) NOT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 Database	Create Database
@@ -1778,7 +1778,7 @@ tinyint	1	-128	127	0	0	YES	YES	NO	YES	YES	NO	NULL,0	A very small integer
 tinyint unsigned	1	0	255	0	0	YES	YES	YES	YES	YES	NO	NULL,0	A very small integer
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `id` char(16) NOT NULL,
+  `id` char(16) NOT NULL default '',
   `data` int(11) NOT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 Database	Create Database
diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result
index 45f7caa0b11..3c007f80d78 100644
--- a/mysql-test/r/strict.result
+++ b/mysql-test/r/strict.result
@@ -912,7 +912,11 @@ Warnings:
 Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'col1' at row 1
 Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'col2' at row 1
 INSERT IGNORE INTO t1 (col1) values (3);
+Warnings:
+Warning	1364	Field 'col2' doesn't have a default value
 INSERT IGNORE INTO t1 () values ();
+Warnings:
+Warning	1364	Field 'col2' doesn't have a default value
 SELECT * FROM t1;
 col1	col2
 1	hello
@@ -942,6 +946,8 @@ DROP TABLE t1;
 SET @@sql_mode = '';
 CREATE TABLE t1 (i int not null);
 INSERT INTO t1 VALUES ();
+Warnings:
+Warning	1364	Field 'i' doesn't have a default value
 INSERT INTO t1 VALUES (DEFAULT);
 Warnings:
 Warning	1364	Field 'i' doesn't have a default value
@@ -949,6 +955,8 @@ INSERT INTO t1 VALUES (DEFAULT(i));
 ERROR HY000: Field 'i' doesn't have a default value
 ALTER TABLE t1 ADD j int;
 INSERT INTO t1 SET j = 1;
+Warnings:
+Warning	1364	Field 'i' doesn't have a default value
 INSERT INTO t1 SET j = 1, i = DEFAULT;
 Warnings:
 Warning	1364	Field 'i' doesn't have a default value
diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result
index 7b9a94ba998..a42dfd53a0a 100644
--- a/mysql-test/r/type_blob.result
+++ b/mysql-test/r/type_blob.result
@@ -502,7 +502,7 @@ select foobar, boggle from t1 where foobar = 'fish' and boggle = 10;
 foobar	boggle
 fish	10
 drop table t1;
-create table t1 (id integer auto_increment unique,imagem LONGBLOB not null);
+create table t1 (id integer auto_increment unique,imagem LONGBLOB not null default '');
 insert into t1 (id) values (1);
 select 
 charset(load_file('../../std_data/words.dat')),
diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result
index 1a66f0d91d2..6433c76571d 100644
--- a/mysql-test/r/type_ranges.result
+++ b/mysql-test/r/type_ranges.result
@@ -21,10 +21,10 @@ time_field time,
 date_time datetime,
 blob_col blob,
 tinyblob_col tinyblob,
-mediumblob_col mediumblob  not null,
-longblob_col longblob  not null,
-options enum('one','two','tree') not null,
-flags set('one','two','tree') not null,
+mediumblob_col mediumblob  not null default '',
+longblob_col longblob  not null default '',
+options enum('one','two','tree') not null ,
+flags set('one','two','tree') not null default '',
 PRIMARY KEY (auto),
 KEY (utiny),
 KEY (tiny),
diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result
index a2e99be62ad..fba5ee02773 100644
--- a/mysql-test/r/warnings.result
+++ b/mysql-test/r/warnings.result
@@ -90,10 +90,12 @@ Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'a' a
 Warning	1265	Data truncated for column 'b' at row 4
 insert into t2(b) values('mysqlab');
 Warnings:
+Warning	1364	Field 'a' doesn't have a default value
 Warning	1265	Data truncated for column 'b' at row 1
 set sql_warnings=1;
 insert into t2(b) values('mysqlab');
 Warnings:
+Warning	1364	Field 'a' doesn't have a default value
 Warning	1265	Data truncated for column 'b' at row 1
 set sql_warnings=0;
 drop table t1, t2;
diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test
index 1c9576c1c56..d091b38c5e8 100644
--- a/mysql-test/t/ctype_latin1_de.test
+++ b/mysql-test/t/ctype_latin1_de.test
@@ -66,7 +66,7 @@ drop table t1;
 #
 # The below checks both binary and character comparisons.
 #
-create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word));
+create table t1 (word varchar(255) not null, word2 varchar(255) not null default '', index(word));
 show create table t1;
 insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae');
 update t1 set word2=word;
diff --git a/mysql-test/t/ctype_many.test b/mysql-test/t/ctype_many.test
index 26057e7c997..454e2c63cbb 100644
--- a/mysql-test/t/ctype_many.test
+++ b/mysql-test/t/ctype_many.test
@@ -8,7 +8,7 @@ SET CHARACTER SET latin1;
 
 CREATE TABLE t1 (
   comment CHAR(32) ASCII NOT NULL,
-  koi8_ru_f CHAR(32) CHARACTER SET koi8r NOT NULL
+  koi8_ru_f CHAR(32) CHARACTER SET koi8r NOT NULL default ''
 ) CHARSET=latin5;
 
 SHOW CREATE TABLE t1;
@@ -142,13 +142,13 @@ INSERT INTO t1 (koi8_ru_f,comment) VALUES (_koi8r'�','CYR CAPIT YA');
 SET CHARACTER SET utf8;
 SELECT koi8_ru_f,MIN(comment),COUNT(*) FROM t1 GROUP BY 1;
 
-ALTER TABLE t1 ADD utf8_f CHAR(32) CHARACTER SET utf8 NOT NULL;
+ALTER TABLE t1 ADD utf8_f CHAR(32) CHARACTER SET utf8 NOT NULL default '';
 UPDATE t1 SET utf8_f=CONVERT(koi8_ru_f USING utf8);
 SET CHARACTER SET koi8r;
 SELECT * FROM t1;
 
 
-ALTER TABLE t1 ADD bin_f CHAR(32) BYTE NOT NULL;
+ALTER TABLE t1 ADD bin_f CHAR(32) BYTE NOT NULL default '';
 UPDATE t1 SET bin_f=koi8_ru_f;
 SELECT COUNT(DISTINCT bin_f),COUNT(DISTINCT koi8_ru_f),COUNT(DISTINCT utf8_f) FROM t1;
 
diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test
index 716dd38a119..da59c6ae5e4 100644
--- a/mysql-test/t/gis-rtree.test
+++ b/mysql-test/t/gis-rtree.test
@@ -120,7 +120,6 @@ INSERT INTO t1 (g) VALUES (GeomFromText('LineString(1 2, 2 3)')),(GeomFromText('
 drop table t1;
 
 CREATE TABLE t1 (
-  geoobjid INT NOT NULL,
   line LINESTRING NOT NULL,
   kind ENUM('po', 'pp', 'rr', 'dr', 'rd', 'ts', 'cl') NOT NULL DEFAULT 'po',
   name VARCHAR(32),
diff --git a/mysql-test/t/index_merge_ror.test b/mysql-test/t/index_merge_ror.test
index 223bd241d96..5375f9d1a31 100644
--- a/mysql-test/t/index_merge_ror.test
+++ b/mysql-test/t/index_merge_ror.test
@@ -8,13 +8,13 @@ drop table if exists  t0,t1,t2;
 create table t1
 (
   /* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
-  st_a int not null,
-  swt1a int not null,
-  swt2a int not null,
+  st_a int not null default 0,
+  swt1a int not null default 0,
+  swt2a int not null default 0,
 
-  st_b int not null,
-  swt1b int not null,
-  swt2b int not null,
+  st_b int not null default 0,
+  swt1b int not null default 0,
+  swt2b int not null default 0,
 
   /* fields/keys for row retrieval tests */
   key1 int,
diff --git a/mysql-test/t/limit.test b/mysql-test/t/limit.test
index 61c57c9b468..05c5c5298aa 100644
--- a/mysql-test/t/limit.test
+++ b/mysql-test/t/limit.test
@@ -6,7 +6,7 @@
 drop table if exists t1;
 --enable_warnings
 
-create table t1 (a int primary key, b int not null);
+create table t1 (a int not null default 0 primary key, b int not null default 0);
 insert into t1 () values ();		-- Testing default values
 insert into t1 values (1,1),(2,1),(3,1);
 update t1 set a=4 where b=1 limit 1;
diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test
index 4bc6a4f051a..934b165f604 100644
--- a/mysql-test/t/null.test
+++ b/mysql-test/t/null.test
@@ -59,7 +59,7 @@ drop table t1;
 #
 # Test inserting and updating with NULL
 #
-CREATE TABLE t1 (a varchar(16) NOT NULL, b smallint(6) NOT NULL, c datetime NOT NULL, d smallint(6) NOT NULL);
+CREATE TABLE t1 (a varchar(16) NOT NULL default '', b smallint(6) NOT NULL default '', c datetime NOT NULL default '0000-00-00 00:00:00', d smallint(6) NOT NULL default 0);
 INSERT INTO t1 SET a = "", d= "2003-01-14 03:54:55";
 UPDATE t1 SET d=1/NULL;
 UPDATE t1 SET d=NULL;
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index dd36cd95969..deb77baeb0e 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -206,7 +206,7 @@ CREATE TABLE t1 (
   favo_tv varchar(50) NOT NULL default '',
   favo_eten varchar(50) NOT NULL default '',
   favo_muziek varchar(30) NOT NULL default '',
-  info text NOT NULL,
+  info text NOT NULL default '',
   ipnr varchar(30) NOT NULL default '',
   PRIMARY KEY  (member_id)
 ) ENGINE=MyISAM PACK_KEYS=1;
diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test
index 6aafbed6b97..dc512030a60 100644
--- a/mysql-test/t/show_check.test
+++ b/mysql-test/t/show_check.test
@@ -102,7 +102,7 @@ drop table t1;
 # Do a create table that tries to cover all types and options
 #
 create table t1 (
-type_bool bool not null,
+type_bool bool not null default false,
 type_tiny tinyint not null auto_increment primary key,
 type_short smallint(3),
 type_mediumint mediumint,
@@ -113,9 +113,9 @@ empty_char char(0),
 type_char char(2),
 type_varchar varchar(10),
 type_timestamp timestamp not null,
-type_date date not null,
-type_time time not null,
-type_datetime datetime not null,
+type_date date not null default '0000-00-00',
+type_time time not null default '00:00:00',
+type_datetime datetime not null default '0000-00-00 00:00:00',
 type_year year,
 type_enum enum ('red', 'green', 'blue'),
 type_set enum ('red', 'green', 'blue'),
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 4f556e34d51..c4dc1b36247 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -11,7 +11,7 @@ use test;
 drop table if exists t1;
 --enable_warnings
 create table t1 (
-	id   char(16) not null,
+	id   char(16) not null default '',
         data int not null
 );
 --disable_warnings
diff --git a/mysql-test/t/type_blob.test b/mysql-test/t/type_blob.test
index 20a501bb5ed..a45bc3c4c47 100644
--- a/mysql-test/t/type_blob.test
+++ b/mysql-test/t/type_blob.test
@@ -301,7 +301,7 @@ drop table t1;
 # Bug when blob is updated
 #
 
-create table t1 (id integer auto_increment unique,imagem LONGBLOB not null);
+create table t1 (id integer auto_increment unique,imagem LONGBLOB not null default '');
 insert into t1 (id) values (1);
 select 
   charset(load_file('../../std_data/words.dat')),
diff --git a/mysql-test/t/type_ranges.test b/mysql-test/t/type_ranges.test
index 5f035921064..1f80c9cf458 100644
--- a/mysql-test/t/type_ranges.test
+++ b/mysql-test/t/type_ranges.test
@@ -28,10 +28,10 @@ CREATE TABLE t1 (
   date_time datetime,
   blob_col blob,
   tinyblob_col tinyblob,
-  mediumblob_col mediumblob  not null,
-  longblob_col longblob  not null,
-  options enum('one','two','tree') not null,
-  flags set('one','two','tree') not null,
+  mediumblob_col mediumblob  not null default '',
+  longblob_col longblob  not null default '',
+  options enum('one','two','tree') not null ,
+  flags set('one','two','tree') not null default '',
   PRIMARY KEY (auto),
   KEY (utiny),
   KEY (tiny),
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index d93010d9915..1032783d991 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -881,19 +881,20 @@ err:
 
 int check_that_all_fields_are_given_values(THD *thd, TABLE *entry)
 {
-  if (!thd->abort_on_warning)                   // No check if not strict mode
-    return 0;
-
+  int err= 0;
   for (Field **field=entry->field ; *field ; field++)
   {
     if ((*field)->query_id != thd->query_id &&
         ((*field)->flags & NO_DEFAULT_VALUE_FLAG))
     {
-      my_error(ER_NO_DEFAULT_FOR_FIELD, MYF(0), (*field)->field_name);
-      return 1;
+      push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+                           ER_NO_DEFAULT_FOR_FIELD,
+                           ER(ER_NO_DEFAULT_FOR_FIELD),
+                           (*field)->field_name);
+      err= 1;
     }
   }
-  return 0;
+  return thd->abort_on_warning ? err : 0;
 }
 
 /*****************************************************************************