Three problems with mysql...

[Raghavan Chockalingam]

1. You can see the error coming up when I try to delete the column which is a primary key.

mysql> describe student_tbl;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     |      | PRI | NULL    | auto_increment |
| name  | varchar(35) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
 

mysql> alter table
    -> student_tbl
    -> drop column
    -> id;
ERROR 1025 (HY000): Error on rename of '.\test\#sql-68c_1fd' to '.\test\student_tbl' (err
no: 150)
 

2. I am not able to add foreign key after creating table

mysql> describe studentscores_tbl;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(11)          |      | PRI | NULL    | auto_increment |
| studentid | int(11)          |      | MUL | 0       |                |
| subjectid | int(10) unsigned |      |     | 0       |                |
+-----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
 

mysql> describe subject_tbl;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned |      | PRI | NULL    | auto_increment |
| name  | varchar(25)         | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)
 

 
mysql>  alter table
    ->  studentscores_tbl
    ->  add
    ->  foreign key (subjectid) references subject_tbl(id);
ERROR 1005 (HY000): Can't create table '.\test\#sql-68c_1fe.frm' (errno: 150)
 

I am posting all the problems in a single topic because all are related
Problem 3: Unable to drop a foreign key on a table

mysql> show keys from studentscores_tbl;
+-------------------+------------+-----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+
| Table             | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | C
ardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------------+------------+-----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+
| studentscores_tbl |          0 | PRIMARY   |            1 | id          | A         |
         1 |     NULL | NULL   |      | BTREE      |         |
| studentscores_tbl |          1 | studentid |            1 | studentid   | A         |
         1 |     NULL | NULL   |      | BTREE      |         |
+-------------------+------------+-----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
 
mysql> alter table studentscores_tbl
    -> drop
    -> foreign key studentid
    -> ;
ERROR 1025 (HY000): Error on rename of '.\test\studentscores_tbl' to '.\test\#sql2-68c-1f
e' (errno: 152)

mysql> show create table studentscores_tbl;
| studentscores_tbl | CREATE TABLE `studentscores_tbl` (
  `id` int(11) NOT NULL auto_increment,
  `studentid` int(11) NOT NULL default '0',
  `subjectid` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `studentid` (`studentid`),
  CONSTRAINT `studentscores_tbl_ibfk_1` FOREIGN KEY (`studentid`) REFERENCES `student_tbl
` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)
 
mysql> alter table studentscores_tbl
    -> drop
    -> foreign key
    -> student_tbl_ibfk_1;
ERROR 1025 (HY000): Error on rename of '.\test\studentscores_tbl' to '.\test\#sql2-68c-1f
e' (errno: 152)
 

[Jeanne Boyarsky]

Raghavan,
It looks like something with the tablespace. Check if you have any locks on the tables and that there is enough memory on the machine.

[Raghavan Chockalingam]

I have run queries at the end of the output to show you that no locks exist. I want to know how to do two things...
1. Find and list the entries in the table where indexes are stored
2. How to list all the tables created by an user?

mysql> show columns from studentscores_tbl;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(11)          |      | PRI | NULL    | auto_increment |
| studentid | int(11)          |      | MUL | 0       |                |
| subjectid | int(10) unsigned |      |     | 0       |                |
+-----------+------------------+------+-----+---------+----------------+
 
mysql> show index from studentscores_tbl;
+-------------------+------------+-----------+--------------+-------------+-----------+-------------+---
-------+--------+------+------------+---------+
| Table             | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Su
b_part | Packed | Null | Index_type | Comment |
+-------------------+------------+-----------+--------------+-------------+-----------+-------------+---
-------+--------+------+------------+---------+
| studentscores_tbl |          0 | PRIMARY   |            1 | id          | A         |           1 |
  NULL | NULL   |      | BTREE      |         |
| studentscores_tbl |          1 | studentid |            1 | studentid   | A         |           1 |
  NULL | NULL   |      | BTREE      |         |
+-------------------+------------+-----------+--------------+-------------+-----------+-------------+---
-------+--------+------+------------+---------+
2 rows in set (0.02 sec)
 
mysql>  alter table
    ->  studentscores_tbl
    ->  drop
    ->  foreign key
    ->  student_tbl_ibfk_1;
ERROR 1025 (HY000): Error on rename of '.\test\studentscores_tbl' to '.\test\#sql2-68c-217' (errno: 152)
 
mysql> show errors;
+-------+------+--------------------------------------------------------------------------------------+
| Level | Code | Message                                                                              |
+-------+------+--------------------------------------------------------------------------------------+
| Error | 1025 | Error on rename of '.\test\studentscores_tbl' to '.\test\#sql2-68c-217' (errno: 152) |
+-------+------+--------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
 

#### From dev.mysql.com
Error: 1025 SQLSTATE: HY000 (ER_ERROR_ON_RENAME)
 
Message: Error on rename of '%s' to '%s' (errno: %d)
###
 
/*** This should be adequate to prove you that no locks exist
 
mysql> select * from studentscores_tbl;
+----+-----------+-----------+
| id | studentid | subjectid |
+----+-----------+-----------+
|  1 |         3 |         0 |
+----+-----------+-----------+
1 row in set (0.03 sec)
 
mysql>  insert into studentscores_tbl
    ->  values( null, 3, 1);
Query OK, 1 row affected (0.03 sec)
 

mysql> update student_tbl
    -> set name  ='murali'
    -> where name = 'logesh';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
***/

[Ben Ethridge]

In MySql, I had the same problem as this thread starter. What solved it for me was including the database name, i.e, for example:

alter table myDb.studentscores_tbl drop foreign key studentid;

A clue is in the:

"Error on rename of '.\test\studentscores_tbl' to '.\test\#sql2-68c-1fe' (errno: 152)"

There is no database name ahead of the "."'s.

Hope that solves your problem as well.

Ben

[OddThomas]

Regarding the third point, I was getting the same issue (errno 152) attempting to drop a foreign key using the MySQL Query Browser, even adding the schema at the front of the table. When I re-connected without specifying a default schema to connect to, the ALTER TABLE <schema>.<tablename> DROP FOREIGN KEY <fk> command worked fine.

Hope this is useful.

[ February 08, 2006: Message edited by: OddThomas ]
[ February 08, 2006: Message edited by: OddThomas ]

[Ulf Dittmer]

Hello "OddThomas"-

Welcome to JavaRanch.

On your way in you may have missed that JavaRanch has a policy on display names, and yours does not comply with it - please adjust it accordingly, which you can do right here. Thanks for your prompt attention to this matter.

Enyoy your time here.