2

Surprisingly (at least to me), the following queries will insert records.

CREATE TABLE null_1 (
  id INT NOT NULL,
  text1 VARCHAR(32) NOT NULL,
  text2 VARCHAR(32) NOT NULL DEFAULT 'foo'
);

INSERT INTO null_1 (id) VALUES(1);
INSERT INTO null_1 (text1) VALUES('test');

mysql> SELECT * FROM null_1;
+----+-------+-------+
| id | text1 | text2 |
+----+-------+-------+
|  1 |       | foo   |
|  0 | test  | foo   |
+----+-------+-------+
2 rows in set (0.00 sec)

I couldn't find this behavior described in the official MySQL documentation, but it is described by http://sql-info.de/mysql/gotchas.html#1_1

Here MySQL has inserted an empty string into column text1 on the first row, and zero into column id on the second row, even though each column is defined as NOT NULL with no default value. As no value was provided in the INSERT statements, these can be considered an attempt to insert implicit NULLs into theses columns, which should normally cause the statements to fail.

... If no DEFAULT value is specified for a column, MySQL automatically assigns one, as follows. If the column may take NULL as a value, the default value is NULL. If the column is declared as NOT NULL, the default value depends on the column type: ...

Is it possible to configure MySQL to prevent doing so, and reject the query based on the NOT NULL constraint? My my.cnf file is shown below.

##  _______________________________________________________________________ 
## / Rackspace MySQL 5.5 Terse Configuration File                          \
## |                                                                       |
## | This is a base configuration file containing the most frequently used |
## | settings with reasonably defined default values for configuring and   |
## | tuning MySQL. Note that these settings can likely be further tuned in |
## | order to get optimum performance from MySQL based upon the database   |
## | configuration and hardware platform.                                  |
## |                                                                       |
## | While the settings provided are likely sufficient for most            |
## | situations, an exhaustive list of settings (with descriptions) can be |
## | found at:                                                             |
## | http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html   |
## |                                                                       |
## | Take care to only add/remove/change a setting if you are comfortable  |
## | doing so! For Rackspace customers, if you have any questions or       |
## | concerns, please contact the MySQL Database Services Team. Be aware   |
## | that some work performed by this team can involve additional billable |
## \ fees.                                                                 /
##  ----------------------------------------------------------------------- 
##         \   ^__^
##          \  (oo)\_______
##             (__)\       )\/\
##                 ||----w |
##                 ||     ||

[mysqld]

## General
datadir                         = /var/lib/mysql
tmpdir                          = /var/lib/mysqltmp
socket                          = /var/lib/mysql/mysql.sock
skip-name-resolve
sql-mode                        = NO_ENGINE_SUBSTITUTION
#event-scheduler                = 1

## Cache
thread-cache-size               = 16
table-open-cache                = 4096
table-definition-cache          = 2048
query-cache-size                = 32M 
query-cache-limit               = 1M

## Per-thread Buffers
sort-buffer-size                = 1M
read-buffer-size                = 1M
read-rnd-buffer-size            = 1M
join-buffer-size                = 1M

## Temp Tables
tmp-table-size                  = 32M 
max-heap-table-size             = 64M 

## Networking
back-log                        = 100
#max-connections                = 200
max-connect-errors              = 10000
max-allowed-packet              = 16M
interactive-timeout             = 3600
wait-timeout                    = 600

### Storage Engines
#default-storage-engine         = InnoDB
innodb                          = FORCE

## MyISAM
key-buffer-size                 = 64M 
myisam-sort-buffer-size         = 128M

## InnoDB
#innodb-buffer-pool-size        = 128M
#innodb-log-file-size           = 100M
#innodb-log-buffer-size         = 8M
#innodb-file-per-table          = 1
#innodb-open-files              = 300

## Replication
server-id                       = 1
#log-bin                        = /var/log/mysql/bin-log
#relay-log                      = /var/log/mysql/relay-log
relay-log-space-limit           = 16G
expire-logs-days                = 7
#read-only                      = 1
#sync-binlog                    = 1
#log-slave-updates              = 1
#binlog-format                  = STATEMENT
#auto-increment-offset          = 1
#auto-increment-increment       = 2

## Logging
#log-output                      = FILE
#slow-query-log                  = 1
#slow-query-log-file             = /var/log/mysql/slow-log
#log-slow-slave-statements
#long-query-time                 = 2

#log                              = /home/mysql_log/allqueries.log
general_log = on
general_log_file=/home/mysql_log/allqueries.log

symbolic-links=0

[mysqld_safe]
log-error                       = /var/log/mysqld.log
open-files-limit                = 65535

innodb_flush_log_at_trx_commit = 0
innodb_strict_mode=on
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE

[mysql]
no-auto-rehash
  • Hello downvoter. I am not asking how to perform a query, but asking how to configure a MySQL server so it enforces these constraints. Why the downvote? – user1032531 Jan 10 '15 at 23:15
6

I couldn't find this behavior described in the official MySQL documentation

A few minutes of looking in the MySQL docs found this:

Column values can be given in several ways:

If you are not running in strict SQL mode, any column
not explicitly given a value is set to its default
(explicit or implicit) value. For example, if you specify a
column list that does not name all the columns in the
table, unnamed columns are set to their default values.
Default value assignment is described in Section 11.6,
“Data Type Default Values”. See also Section 1.8.3.3,
“Constraints on Invalid Data”.

If you want an INSERT statement to generate an error unless
you explicitly specify values for all columns that do not
have a default value, you should use strict mode. See
Section 5.1.7, “Server SQL Modes”.
|improve this answer|||||
  • Thanks Mike. Yes, I recently came across dev.mysql.com/doc/refman/5.6/en/…, and found that I need to use STRICT_ALL_TABLES if I wish to throw an error. – user1032531 Jan 10 '15 at 20:59
  • Yes, and that's in your my.cnf. So you're probably not starting as mysqld_safe...? – Mike Jan 10 '15 at 21:03
  • 1
    I had STRICT_TRANS_TABLES, not STRICT_ALL_TABLES in my.cnf. And no, I don't believe I am starting as mysqld_safe, but need to read up on it. – user1032531 Jan 10 '15 at 21:10
1

Use SQL mode STRICT_ALL_TABLES per http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_strict_all_tables.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.