Bug #24583 FEDERATED TABLE doesn't allow multicolumn UNIQUE INDEX when some column is NULL

• Submitted: 25 Nov 2006 10:22
• Modified: 27 Nov 2006 12:47
• Reporter: Pavel Francírek
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.1.11-beta
• OS: Linux (Linux 2.6.17.4-amd64)
• CPU Architecture: Any
• Tags: federated, INDEX, null, unique

[25 Nov 2006 10:22] Pavel Francírek

Description:
I hope this is not duplicate but I found no such bug already reported.

Unlike MyISAM or Innodb engine (no other tested) FEDERATED engine doesn't allow multicolumn UNIQUE INDEX when some column is NULL.

How to repeat:
server1:
CREATE TABLE `category` (
  `ID` tinyint(3) unsigned NOT NULL default '0',
  `title` varchar(255) NOT NULL default '',
  `url_keyword` varchar(255) NOT NULL default '',
  `parent_ID` tinyint(3) unsigned default NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `parent_title` (`parent_ID`,`title`),
  KEY `parent_ID` (`parent_ID`)
) ENGINE=INNODB DEFAULT CHARSET=cp1250;
Query OK, 0 rows affected (0.01 sec)

server2:
CREATE TABLE `category` (
  `ID` tinyint(3) unsigned NOT NULL default '0',
  `title` varchar(255) NOT NULL default '',
  `url_keyword` varchar(255) NOT NULL default '',
  `parent_ID` tinyint(3) unsigned default NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `parent_title` (`parent_ID`,`title`),
  KEY `parent_ID` (`parent_ID`)
) ENGINE=FEDERATED CONNECTION='mysql://user:pass@server1/db/category';
ERROR 1121: Column 'parent_ID' is used with UNIQUE or INDEX but is not defined as NOT NULL

[27 Nov 2006 12:47] Valeriy Kravchuk

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour described with 5.1.14-BK on Linux:

openxs@suse:~/dbs/5.1> bin/mysql -uroot test --socket=/tmp/mysql51.sock
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.14-beta Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `category` (
    ->   `ID` tinyint(3) unsigned NOT NULL default '0',
    ->   `title` varchar(255) NOT NULL default '',
    ->   `url_keyword` varchar(255) NOT NULL default '',
    ->   `parent_ID` tinyint(3) unsigned default NULL,
    ->   PRIMARY KEY  (`ID`),
    ->   UNIQUE KEY `parent_title` (`parent_ID`,`title`),
    ->   KEY `parent_ID` (`parent_ID`)
    -> ) ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1/test/category'
    -> ;
Query OK, 0 rows affected (0.13 sec)

mysql> show create table category\G
*************************** 1. row ***************************
       Table: category
Create Table: CREATE TABLE `category` (
  `ID` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `title` varchar(255) NOT NULL DEFAULT '',
  `url_keyword` varchar(255) NOT NULL DEFAULT '',
  `parent_ID` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `parent_title` (`parent_ID`,`title`),
  KEY `parent_ID` (`parent_ID`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1/tes
t/category'
1 row in set (0.01 sec)

So, pleae, try to use newer version, 5.1.12, already released, or build from current sources.