Bug #62580 Allow insert null

• Submitted: 30 Sep 2011 6:43
• Modified: 30 Sep 2011 17:47
• Reporter: Duy Anh K. Dang
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.5.8
• OS: Any
• CPU Architecture: Any
• Tags: Insert NULL

[30 Sep 2011 6:43] Duy Anh K. Dang

Description:
MySQL didn't care my constraint that the column can't be null???

How to repeat:
I create table like this:

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

val is NOT NULL but if i run query like:

INSERT INTO test (val) VALUES (''), (NULL), ('abcd');

It runs successful! (it is not my expectation!!!)

But if I run query like

INSERT INTO test (val) VALUES (NULL);

It shows warning note (it is my expectation)

Suggested fix:
If a record input is null, it stop execute (except for IGNORE) and show warning

[30 Sep 2011 9:33] Peter Laursen

Not reproducible for me with neither MySQL 5.1.59 or 5.5.16:

SET SQL_MODE = '';

USE somedatabase;

DROP TABLE IF EXISTS test;

CREATE TABLE IF NOT EXISTS `test` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `val` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO test (val) VALUES (''), (NULL), ('abcd');

SHOW WARNINGS;
-- returns "Column 'val' cannot be null"

SELECT * FROM test;
/* returns

    id  val   
------  ------
     1        
     2        
     3  abcd  

.. because in non-strict mode NULL is substitued by '' (empty string)
*/

Peter
(not a MySQL person)

[30 Sep 2011 17:47] Valeriy Kravchuk

This is NOT a bug. By default you get warning, and in strict SQL mode you get error:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.5.17-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table test;
Query OK, 0 rows affected (0.80 sec)

mysql> CREATE TABLE IF NOT EXISTS `test` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `val` varchar(50) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.46 sec)

mysql> INSERT INTO test (val) VALUES (''), (NULL), ('abcd');
Query OK, 3 rows affected, 1 warning (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1048
Message: Column 'val' cannot be null
1 row in set (0.00 sec)

mysql> set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO test (val) VALUES (''), (NULL), ('abcd');
ERROR 1048 (23000): Column 'val' cannot be null