MySQL Bugs: #22909: Using CREATE ... LIKE is possible to create field with invalid default value

Bug #22909	Using CREATE ... LIKE is possible to create field with invalid default value
Submitted:	2 Oct 2006 21:22	Modified:	22 Jun 2010 13:37
Reporter:	Sveta Smirnova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.12	OS:	Linux (Linux)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
When I try to create table with invalid DATETIME default value I get error:

mysql> CREATE TABLE foo(marketHour DATETIME NOT NULL PRIMARY KEY DEFAULT '0000-01-01 00:00:00');
ERROR 1067 (42000): Invalid default value for 'marketHour'

But If I create such table using 5.1.11 installation and then use .MYD, .MYI, .FRM files from the 5.1.11 installation and 5.1.12 server, I can create new table with invalid default value.

How to repeat:
Use MySQL 5.1.11:

mysql> CREATE TABLE foo(marketHour DATETIME NOT NULL PRIMARY KEY DEFAULT '0000-01-01 00:00:00');
Query OK, 0 rows affected (0.18 sec)

Install MySQL 5.1.12 and start mysqld with --datadir=/path/to/5.1.11/datadir option, then type:

mysql> CREATE TABLE foo2 LIKE foo;
Query OK, 0 rows affected (0.00 sec)

and then:

SHOW CREATE TABLE foo2;

Suggested fix:
Do not allow to create tables with invalid default values using CREATE ... LIKE syntax.

Even simpler:

drop database bug22369_12;
create database bug22369_12;
use bug22369_12;
DROP TABLE IF EXISTS `Notes`;
DROP TABLE IF EXISTS `BrokerNotes`;
CREATE TABLE `BrokerNotes`(
`BrokerID` BIGINT(20) UNSIGNED NOT NULL,
PRIMARY KEY(`BrokerNoteID`),
INDEX(`BrokerID`) )ENGINE=InnoDB;

INSERT INTO `BrokerNotes` (`BrokerID`) VALUES (1);

ALTER TABLE BrokerNotes CHANGE BrokerID GenericUserID bigint unsigned not null, RENAME Notes;

The problem is because the frm gets renamed, as it should be to Notes.frm, and then BrokerNotes is attempted to be opened, as table_list is the old one. Of course this fails and the situation is in the middle of nothing. Most probably the table wasn't renamed in InnoDB's DDIC. Continuing investigation.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14500

ChangeSet@1.2325, 2006-10-27 20:16:41+02:00, andrey@example.com +1 -0
  Fix for bug#22909
  Using CREATE ... LIKE is possible to create field with invalid default value
  
  Some values become disallowed in newer versions of the server. CREATE TABLE
  (..) with such default values is not possible, but it was possible to do
  CREATE TABLE ... LIKE , when the like_table was created by previous MySQL
  version.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51617

2682 Dmitry Lenev	2008-08-14
      Tentative patch for the 7th milestone of WL#148 "Foreign keys"
      ("DDL checks and changes CREATE, CREATE TABLE SELECT, CREATE
      TABLE LIKE") implementing necessary changes in CREATE TABLE LIKE.
      
      Per LLD CREATE TABLE LIKE statement should not copy foreign keys
      from source table to the new table.
      
      Since we store information about foreign keys in .FRM files
      and there is no simple way to modify this information without
      recreating .FRM file we have to change our implementation of
      CREATE TABLE LIKE. Instead of directly copying .FRMs file we
      now use the same code as simple CREATE TABLE.
      I.e. we generate structures describing table being created
      from source table and the pass these structures after minor
      tweaks to the mysql_create_table_no_lock() function.
      
      A side effect of this change is that CREATE TABLE LIKE now
      follows the same rules as CREATE TABLE and thus bug#22909
      should be solved.
      
      Questions for reviewer are marked by QQ.

Hello!

The following patch http://lists.mysql.com/commits/51937, which among others fixes this problem, was pushed into mysql-6.1-fk tree. Eventually this tree is going to be merged into one of 6.x releases. Please let me know if there is a need for fixing this bug in one of earlier versions so this patch could be back-ported.

This bug was originally fixed in the 6.1-fk tree. The fix was later backported as part of the patch for Bug#42546 "Backup: RESTORE fails, thinking it finds an existing table".

This bug was therefore fixed in Ver 5.5.3, so I'm setting this bug to Documenting.

Test coverage (originally not backported) has now been backported in http://lists.mysql.com/commits/110795 and pushed to mysql-trunk-runtime (Ver 5.5.5).

Noted in 5.5.3 changelog.

CREATE TABLE ... LIKE did not always produce an error is the source
table column defaults were illegal for the current version of MySQL.
(This could occur if the table was created using an older server that
was less restrictive about legal default values.)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100622130139-u05awgya93zvbsop) (version source revid:marko.makela@oracle.com-20100603095032-v5ptkkzt1bhz0m1d) (merge vers: 5.1.48) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100622130623-r7yhm89fz9n5t9nb) (version source revid:alik@sun.com-20100622130528-187gd949sa9b6pa6) (pib:16)