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

Hello,

I posted this in the INNODB forum as well but since my J-connector version has changed as well I thought it might be relevant to this forum too.

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),

I dropped the database and checked that its data directory was removed from the mySQL Data directory, which it was, so there wouldn't be an issue of an existing .frm file.

I re-ran the table creation statements and got the same error. When I checked the appropriate data directory I saw the .frm files for the tables which had been made but the table is question did not have one there.

Looking at the log file (.err file in the Data directory) did not shed any light either. I've attached the log file below. As you can see there's nothing in there which looks relevant.

The only exception I get is through the driver:

2005-02-22 14:51:00,534 [TP-Processor2] ERROR com.wonka.setup.MySqlServerSetup - processSqlFile: SQL error:
java.sql.SQLException: Can't create table '.\test_vs\vir_label_set_fields.frm' (errno: 121)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2847)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622)

Looking in test_vs's data directory I do not see the vir_label_set_fields.frm file so there's nothing in the way or being created. There are .frm files for the other two tables which this one references. So those are in place beforehand which is good.

Again this all worked fine under 4.0.18 w/J-connector 3.0.7 stable.

Any other thoughts to try? Thanks,

-Noah

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
Written By
Posted
Question: Create table fails after switch from 4.0.18-4.1.10
February 22, 2005 02: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.