Bug #73299 DEFAULT value for PRIMARY KEY column depends on the way to declare it PRIMARY

• Submitted: 16 Jul 2014 14:59
• Modified: 18 Jul 2014 9:17
• Reporter: Valeriy Kravchuk
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S2 (Serious)
• Version: 5.6.19, 5.6.20
• OS: Any
• CPU Architecture: Any
• Tags: DDL, DEFAULT, missing manual, primary key

[16 Jul 2014 14:59] Valeriy Kravchuk

Description:
Manual (http://dev.mysql.com/doc/refman/5.6/en/data-type-defaults.html) says:

"If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. Exception: If the column is defined as part of a PRIMARY KEY but not explicitly as NOT NULL, MySQL creates it as a NOT NULL column (because PRIMARY KEY columns must be NOT NULL), but also assigns it a DEFAULT clause using the implicit default value."

But look at this text case:

mysql> create table tnull5 (a int primary key, b int) engine = innodb;
Query OK, 0 rows affected (0.43 sec)

mysql> show create table tnull5\G
*************************** 1. row ***************************
       Table: tnull5
Create Table: CREATE TABLE `tnull5` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.04 sec)

mysql> create table tnull6 (a int, b int, primary key(a)) engine = innodb;
Query OK, 0 rows affected (0.49 sec)

mysql> show create table tnull6\G
*************************** 1. row ***************************
       Table: tnull6
Create Table: CREATE TABLE `tnull6` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.06 sec)

mysql> select @@sql_mode, version()\G
*************************** 1. row ***************************
@@sql_mode:
 version(): 5.6.19-log
1 row in set (0.00 sec)

It seems implicit default value is assigned ONLY when PRIMARY KEY is defined on table level, NOT at column level. Manual says nothing to explain this, and one can not actually "reverse engineer" the way PRIMARY KEY was defined (as you can see from SHOW statements above).

How to repeat:
set session sql_mode='';
create table tnull5 (a int primary key, b int) engine = innodb;
show create table tnull5\G

create table tnull6 (a int, b int, primary key(a)) engine = innodb;
show create table tnull6\G

select @@sql_mode, version()\G

Now, try to explain the results using the manual.

Suggested fix:
Please, do what manual says consistently, no matter how or where PRIMARY KEY constraint is declared.

[16 Jul 2014 18:42] Peter Laursen

I checked and it is the same with MySQL 5.5.38

-- Peter
-- not a MySQL/Oracle person

[18 Jul 2014 9:17] MySQL Verification Team

Hello Valeriy,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

[18 Jul 2014 9:18] MySQL Verification Team

//
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> create table tnull5 (a int primary key, b int) engine = innodb;
Query OK, 0 rows affected (0.37 sec)

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

mysql>
mysql> create table tnull6 (a int, b int, primary key(a)) engine = innodb;
Query OK, 0 rows affected (0.30 sec)

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

mysql>
mysql> select @@sql_mode, version()\G
*************************** 1. row ***************************
@@sql_mode:
 version(): 5.6.20-enterprise-commercial-advanced-log
1 row in set (0.03 sec)

[18 Jul 2014 9:25] MySQL Verification Team

// 5.7.4

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

mysql> create table tnull5 (a int primary key, b int) engine = innodb;
Query OK, 0 rows affected (0.27 sec)

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

mysql>
mysql> create table tnull6 (a int, b int, primary key(a)) engine = innodb;
Query OK, 0 rows affected (0.28 sec)

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

mysql>
mysql> select @@sql_mode, version()\G
*************************** 1. row ***************************
@@sql_mode:
 version(): 5.7.4-m14-log
1 row in set (0.00 sec)