Bug #1009 Wrong dropping of primary keys without any error message
Submitted: 7 Aug 2003 4:46 Modified: 7 Aug 2003 6:46
Reporter: Sinisa Milivojevic Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.13 OS:Any (ALL)
Assigned to: CPU Architecture:Any

[7 Aug 2003 4:46] Sinisa Milivojevic
Description:
Dropping primary key on InnoDB, when there is unique key, drops unique key..

MyISAM allows auto_incremnt as UNIQUE in CREATE, but not in ALTER.

How to repeat:
The problem(s) occured on Redhat 8 or 9 and Mysql server-max 4.0.13 (tar
format) from mysql homepage. I had to reinstall a machine to reproduce the
problem,this is why it took so long.

---- First the original problem
Redhat 9 + Mysql server-max 4.0.13 (tar format from mysql.com homepage)

create table uksample2 (id int unique not null ,name char(30),tel
char(20))type=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table uksample2 add primary key (tel);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc uksample2;
+-------+----------+------+-----+---------+-------+

| Field | Type     | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| id    | int(11)  |      | UNI | 0       |       |
| name  | char(30) | YES  |     | NULL    |       |
| tel   | char(20) |      | PRI |         |       |

+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table uksample2 drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc uksample2;
+-------+----------+------+-----+---------+-------+

| Field | Type     | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| id    | int(11)  |      |     | 0       |       |
| name  | char(30) | YES  |     | NULL    |       |
| tel   | char(20) |      | PRI |         |       |

+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Unique key was dropped. Why not Primary key, why no error message???

------
2nd  example where it complains about that no two primary keys are
 allowed,but lets me create them first ;-) This time on Redhat 8.
Redhat8 and 4.0.13 server-max (tar format from mysql.com homepage)

create table uksample2 (id int unique not null auto_increment,name
char(30),tel char(20));
Query OK, 0 rows affected (0.01 sec)

mysql> desc uksample2;
+-------+----------+------+-----+---------+----------------+

| Field | Type     | Null | Key | Default | Extra          |

+-------+----------+------+-----+---------+----------------+

| id    | int(11)  |      | PRI | NULL    | auto_increment |
| name  | char(30) | YES  |     | NULL    |                |
| tel   | char(20) | YES  |     | NULL    |                |

+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> alter table uksample2 add primary key (tel);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc uksample2;
+-------+----------+------+-----+---------+----------------+

| Field | Type     | Null | Key | Default | Extra          |

+-------+----------+------+-----+---------+----------------+

| id    | int(11)  |      | UNI | NULL    | auto_increment |
| name  | char(30) | YES  |     | NULL    |                |
| tel   | char(20) |      | PRI |         |                |

+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> alter table uksample2 drop primary key ;
ERROR 1075: Incorrect table definition; There can only be one auto column and
it must be defined as a key
mysql> alter table uksample2 drop primary key (id);

Note that also "alter table uksample2 drop index id;" will give me the same
 error.

-----

On a Suse Linux system 8.1 with mysql server max 4.1 -alpha (rpm format from
mysql.com homepage) I get the below message, which I believe is correct.

mysql> create table uksample2 (id int unique not null auto_increment,name
char(30),tel char(20));
Query OK, 0 rows affected (0.07 sec)

mysql> alter table uksample2 add primary key (tel);
ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL;  If you need NULL in
a key, use UNIQUE instead
mysql> drop table uksample2;
Query OK, 0 rows affected (0.00 sec)
[7 Aug 2003 6:46] Sergei Golubchik
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

fixed in 4.0.15