Can't create table (errno: 121)

When Mysql can't create the table is usually due to FK.

1- make sure you have exact same date types between PK and FK (if you're using unsigned in PK, FK must be unsigned).

2- Check you're not already using same constraint name in the schema. Use info_schema to double check

select table_name, referenced_table_name, constraint_name  from information_schema.referential_constraints where constraint_schema='your_db'

Backup Stored Procedures Only

If you want to backup Stored Procedures only, use the following command:

mysqldump -uusername -p -hhostname DBname --routines --no-create-info --no-data > DBName_SP.sql

Mysql error : Can't create table (errno: 121)

Mysql Error: can't create table (errno 121).

This error occurs when you are trying to create referential integrity between InnoDB engine tables and one of the key (primary key or foreign key) has not "unsigned" defined.

Make sure your table create statement includes "unsigned" for each PK and FK you created.

Can't update table 'tbl' in stored function/trigger because it is already used by statement

You'll probably get this error if you try to execute an Insert/Update/Delete statement inside a trigger. 

The following will fail !!

create trigger DB.Trg_Upd_Sample

before update ON SDB.Test 

FOR EACH ROW

BEGIN

If NEW.rowstatus = 0 Then

Update Test set new.rowstatus =1 where TestId = new.TestId;

End If ;

End ;

You need to use "Set" to update the actual columns.

Insert/Update/Delete statement can be used inside a trigger if they targer another table.

create trigger DB.Trg_Upd_Sample

before update ON SDB.Test 

FOR EACH ROW

BEGIN

If NEW.rowstatus = 0 Then

Set New.rowstatus = 2 ;

End If ;

End ;

(from Mysql 5 Ref Manual)

The OLD and NEW keywords enable you to access columns in the rows affected by a trigger. (OLD and NEW are not case sensitive.) In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated. 

Franck Leveneur

GreenLit Design

http://www.greenlitdesign.com