Why MySQL implicitly convert no data to empty string?

Question

Lets create table

CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `data1` varchar(64) NOT NULL, `data2` varchar(64) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

and paste some data

INSERT INTO `test` (`data1`) VALUES ('111');

We did not specify data2 but row have been inserted. Value of field data2 is empty string. Why insertion didn't fail? Is it a bug?

mysql Ver 14.14 Distrib 5.6.19, for debian-linux-gnu (x86_64)

what is your sql_mode is set to? you can find that by checking this show variables like 'sql_mode' your sql_mode probably is set to empty which allows that. but you would get a warning. — Jaylen
– Jaylen, Commented Nov 25, 2014 at 0:14
sql_mode in my system is NO_ENGINE_SUBSTITUTION. Only one variable found. It's default config - I have not changed anything since the installation — Victor Mezrin
– Victor Mezrin, Commented Nov 25, 2014 at 0:25

Jaylen · Accepted Answer · 2014-11-25 00:43:04Z

2

Most likely your sql_mode does not contains STRICT_TRANS_TABLES which will allow the insert to be executed and you will get a warning instead of an error..

You can check your sql_mode variable like so

SHOW VARIABLES LIKE 'sql_mode';

If you don't want that behavior you can change your sql_mode like so

-- change sql_mode only for the current session
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

and if you want this change to affect your entire server then

-- change the sql_mode for the entire server.
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

If you want to change it for the entire server. Then you also need to add update your my.ini file

look for the line that starts with sql-mode =

and replace it with

sql-mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Updating my.ini file will prevent the server from reverting back after a reboot.

edited Nov 25, 2014 at 0:43

answered Nov 25, 2014 at 0:20

Jaylen

40.5k43 gold badges140 silver badges228 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Victor Mezrin Over a year ago

sql_mode on my system is NO_ENGINE_SUBSTITUTION. there is only one value. It's default mysql config

Jaylen Over a year ago

Okay change it like I mentioned above and this will give you the desired outcome.

Jaylen Over a year ago

cool, I just updated the line to look for in your my.ini file if you want to change the mode for the entire server.

Collectives™ on Stack Overflow

Why MySQL implicitly convert no data to empty string?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related