MySQL Forums
Forum List  »  InnoDB

Question: Create table fails after switch from 4.0.18-4.1.10
Posted by: Noah White
Date: February 21, 2005 05:27PM

The following create table statement worked fine under 4.0.18 w/Java connector 3.0.7 stable but now gives a errorno 121 under 4.1.10 with Java connector 3.1.7 under WinXP. Perror says this is "Duplicate key on write or update". I've isolated the issue down to the following bit from the vir_label_set_fields create statement. If I take it out the statement runs fine.

CONSTRAINT vir_field_type_fk
FOREIGN KEY (field_type)
REFERENCES vir_valid_field_types(field_type),
CONSTRAINT vir_field_input_fk
FOREIGN KEY (input_method)
REFERENCES vir_valid_input_methods(input_method),

Any thoughts on this problem? TIA -

Full relavent create tables below -

CREATE TABLE vir_label_set_fields
(
index_name VARCHAR(128) NOT NULL,
label_set_name VARCHAR(128) NOT NULL,
field_name VARCHAR(128) NOT NULL,
label_type VARCHAR(16) NOT NULL,
field_order INTEGER NOT NULL,
field_type VARCHAR(8) NOT NULL,
display_size INTEGER NOT NULL,
input_method VARCHAR(16) NOT NULL,
display_name VARCHAR(255),
description TEXT,
field_required CHAR(1) DEFAULT 'N' ,
is_hidden CHAR(1) DEFAULT 'N' ,
is_sortable CHAR(1) DEFAULT 'N' ,
INDEX vir_label_set_fields_idx_pk (index_name, label_set_name, field_name, label_type),
INDEX vir_label_set_fields_idx_inln (index_name, label_set_name),
INDEX vir_label_set_fields_idx_lt (label_type),
INDEX vir_label_set_fields_idx_ft (field_type),
INDEX vir_label_set_fields_idx_im (input_method),
CONSTRAINT vir_label_set_fields_pk
PRIMARY KEY (index_name, label_set_name, field_name, label_type),
CONSTRAINT vir_lsf_ls_idx_fk
FOREIGN KEY(index_name, label_set_name)
REFERENCES vir_label_sets(index_name, name) ON DELETE CASCADE,
CONSTRAINT vir_label_set_type_fk
FOREIGN KEY (label_type)
REFERENCES vir_valid_label_types(label_type),
CONSTRAINT vir_field_type_fk
FOREIGN KEY (field_type)
REFERENCES vir_valid_field_types(field_type),
CONSTRAINT vir_field_input_fk
FOREIGN KEY (input_method)
REFERENCES vir_valid_input_methods(input_method),
CONSTRAINT vir_lsf_check_required
CHECK( field_required IN( 'Y', 'N') ),
CONSTRAINT vir_lsf_check_hidden
CHECK( is_hidden IN( 'Y', 'N') ),
CONSTRAINT vir_lsf_check_sortable
CHECK( is_sortable IN( 'Y', 'N') )
)
TYPE=INNODB
;

CREATE TABLE vir_valid_field_types
(
field_type VARCHAR(8) NOT NULL,
description VARCHAR(200),
INDEX vir_valid_field_types_idx (field_type),
CONSTRAINT vir_valid_field_types_pk PRIMARY KEY(field_type)
)
TYPE=INNODB
;

INSERT INTO vir_valid_field_types VALUES ('STR', 'Indicates default virage string field');
INSERT INTO vir_valid_field_types VALUES ('INT', 'Indicates default virage int field');
INSERT INTO vir_valid_field_types VALUES ('FLOAT', 'Indicates default virage float field');
INSERT INTO vir_valid_field_types VALUES ('DATE', 'Indicates default virage date field');

CREATE TABLE vir_valid_input_methods
(
input_method VARCHAR(16) NOT NULL,
description VARCHAR(200),
INDEX vir_valid_input_methods_idx (input_method),
CONSTRAINT vir_valid_input_methods_pk
PRIMARY KEY(input_method)
)
TYPE=INNODB
;

INSERT INTO vir_valid_input_methods VALUES ('ENTRY', 'User can enter supply any value they choose');
INSERT INTO vir_valid_input_methods VALUES ('SELECT', 'User selects from a list of acceptable values');
INSERT INTO vir_valid_input_methods VALUES ('EDIT_SELECT', 'User can supply any value, but are provided with a list of VALUES to choose from');
INSERT INTO vir_valid_input_methods VALUES ('MULTI_SELECT', 'User can select multiple VALUES from a list of acceptable values');

Options: ReplyQuote


Subject
Views
Written By
Posted
Question: Create table fails after switch from 4.0.18-4.1.10
2863
February 21, 2005 05:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.