MySQL Bugs: #69571: Please make table creation stricter and not make changes silently

Bug #69571	Please make table creation stricter and not make changes silently
Submitted:	25 Jun 2013 11:15	Modified:	26 Jun 2013 5:38
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.6.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I guess this should not surprise me but it does.

mysql> create table t3 ( id int not null, a varchar(20) not null, b varchar(20) , primary key (id,a,b) ) engine = innodb default charset latin1;
Query OK, 0 rows affected (2.04 sec)
mysql> show create table t3\G
*************************** 1. row ***************************
      Table: t3
Create Table: CREATE TABLE `t3` (
 `id` int(11) NOT NULL,
 `a` varchar(20) NOT NULL,
 `b` varchar(20) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`,`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.03 sec)
and why does it also add a default.
?
mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.12-log |
+------------+
1 row in set (0.00 sec)

Why:
1. silently add a NOT NULL
2. silently add a DEFAULT VALUE
3. not even both to tell me that my "intended SQL" is wrong or is going to be changed

How to repeat:
See above.

Suggested fix:
I'd prefer:
1. if the syntax is not correct for MySQL to generate an error.
2. if you want to have a "do the right thing" mode.  That's fine, but make that optional, but not the default behaviour.
3. There's no need to add a default value either, as that's a slightly less intrusive change.

Perhaps this behaviour is really considered normal but in the end what it does is make you lazy and possibly think you have configured the table one way when in fact you have it configured differently.

Hello Simon,

Thank you for the report.

Imho this is not a bug but a documented behavior as manual clearly states that "A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently)" Please see http://dev.mysql.com/doc/refman/5.6/en/create-table.html

mysql> create table t3 ( id int not null, a varchar(20) not null, b varchar(20) , primary key (id,a,b) ) engine = innodb default charset latin1;
Query OK, 0 rows affected (0.07 sec)

mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `a` varchar(20) NOT NULL,
  `b` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> 

I think it is a feature request to show warning if user try ..   b VARCHAR(20) NULL, PRIMARY KEY (... b)  because that would mean user wanted nullable PK, which is not allowed, so it was set to not null implicitly (and silently).

Thanks,
Umesh

Also, see - http://dev.mysql.com/doc/refman/5.6/en/silent-column-changes.html

I agree that this is documented but many other RDBMSes give errors when things are not defined correctly.  The problem with this is that people get used to it and often do not worry about the implications of what they are doing and in many cases when a table starts to grow performance and storage characteristics may change because of these 'silent' changes.

So I agree this is documented but so is "silent truncation" of data which can often get missed. That does not mean that it is good. When MySQL was being used in smaller environments I can imagine that the developers appreciated these "annoyances" going away. I am not  so sure that this is good as you scale up and want to have more control or be more aware of "mistakes" and the only way to do that is to be stricter in what you accept.

So please consider this stricter behaviour for the future.

Related: Bug #69223 (warning/error if no primary key provided)