Description:
I have one table called 'validation' that is defined as follows:
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| storage_id | int(10) unsigned | NO | PRI | NULL | |
| status | char(1) | NO | | N | |
| reason | varchar(255) | YES | | NULL | |
+------------+------------------+------+-----+---------+-------+
A second table called 'storage', defined as follows:
+-------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+----------------+
| storage_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| user_id | smallint(5) unsigned | NO | MUL | NULL | |
| app_id | smallint(5) unsigned | NO | MUL | NULL | |
| source_id | smallint(5) unsigned | NO | MUL | NULL | |
| event_date | date | NO | MUL | NULL | |
| start_tm | time | NO | | NULL | |
| end_tm | time | NO | | NULL | |
| identifier | varchar(20) | NO | | NULL | |
| description | varchar(255) | NO | | NULL | |
+-------------+----------------------+------+-----+---------+----------------+
Both table engines is InnoDB.
Table 'validation' has FK from storage_id to storage.storage_id.
Table 'storage' has two triggers, defined as follows:
CREATE TRIGGER storage_to_validation_insert AFTER INSERT ON storage
FOR EACH ROW
BEGIN
INSERT INTO validation VALUES (NEW.storage_id, CAST('N' AS CHAR), '');
END//
CREATE TRIGGER storage_to_validation_update AFTER UPDATE ON storage
FOR EACH ROW
BEGIN
UPDATE validation V SET status = 'N' WHERE V.storage_id = NEW.storage_id;
END//
When the 'storage' table is updated (via the .NET interface - I'm using Visual Studio
2008, VB, .NET Platform 3.5), the trigger is CORRECTLY run and the validation table
receives the UPDATE to 'N' on the status field. So, since this is run on the server side,
there is no issue on the server. Updating the 'validation' table through the command-line
also works fine.
However, when the 'validation' table (status field) is updated (e.g., to 'A') via the GUI
(thus through the connector), I receive the following error message: "Truncated incorrect
DOUBLE value: 'A'"
How to repeat:
1. Create both tables:
CREATE TABLE IF NOT EXISTS `storage` (
`storage_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`user_id` SMALLINT UNSIGNED NOT NULL ,
`app_id` SMALLINT UNSIGNED NOT NULL ,
`source_id` SMALLINT UNSIGNED NOT NULL ,
`event_date` DATE NOT NULL ,
`start_tm` TIME NOT NULL ,
`end_tm` TIME NOT NULL ,
`identifier` VARCHAR(20) NOT NULL ,
`description` VARCHAR(255) NOT NULL ,
PRIMARY KEY (`storage_id`) ,
INDEX `fk_storage_users_id` (`user_id` ASC) ,
INDEX `fk_storage_source_id` (`source_id` ASC) ,
INDEX `fk_storage_apps_id` (`app_id` ASC) ,
INDEX `ik_storage_eventdate` (`event_date` ASC) ,
INDEX `ik_storage_appid` (`app_id` ASC) ,
INDEX `ik_storage_userid` (`user_id` ASC) ,
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `validation` (
`storage_id` INT UNSIGNED NOT NULL ,
`status` CHAR(1) NOT NULL,
`reason` VARCHAR(255) NULL ,
PRIMARY KEY (`storage_id`) ,
INDEX `fk_rejected_storage_id` (`storage_id` ASC) ,
CONSTRAINT `fk_rejected_storage_id`
FOREIGN KEY (`storage_id` )
REFERENCES storage` (`storage_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
2. Create the triggers:
DELIMITER //
CREATE TRIGGER storage_to_validation_insert AFTER INSERT ON storage
FOR EACH ROW
BEGIN
INSERT INTO validation VALUES (NEW.storage_id, 'N', '');
END//
CREATE TRIGGER storage_to_validation_update AFTER UPDATE ON storage
FOR EACH ROW
BEGIN
UPDATE validation V SET STATUS = CAST('N' AS CHAR) WHERE V.storage_id =
NEW.storage_id;
END//
DELIMITER ;
3. Add some records to 'storage':
INSERT INTO storage VALUES (1,1,1,1,'2009-07-07','07:00','09:00','Test','Bogus Test 1');
INSERT INTO storage VALUES (2,1,1,1,'2009-07-07','07:00','09:00','Test','Bogus Test 2');
INSERT INTO storage VALUES (3,1,1,1,'2009-07-07','07:00','09:00','Test','Bogus Test 3');
INSERT INTO storage VALUES (4,1,1,1,'2009-07-07','07:00','09:00','Test','Bogus Test 4');
4. Check 'validation'. There should be four records there, all with the status field set
to 'N'.
5. Using the .NET Connector 6.0.4, run the following statement:
"UPDATE validation V SET V.status = 'A' AND V.reason = '' WHERE V.storage_id=1; "
6. You should receive the error message "Truncated incorrect DOUBLE value: 'A'"
Note: if you force the CAST, the value is inserted as '0':
7. Using the .NET Connector 6.0.4, run the following statement:
"UPDATE validation V SET V.status = CAST('A' AS CHAR) AND V.reason = '' WHERE
V.storage_id=1; "
8. Check the 'validation' table records. The row with 'storage_id' = 1 should have its
'status' field set to '0'.
Suggested fix:
Is the Connector converting CHAR(1) fields to another Datatype? DOUBLE or BIT, perhaps?
Description: I have one table called 'validation' that is defined as follows: +------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+-------+ | storage_id | int(10) unsigned | NO | PRI | NULL | | | status | char(1) | NO | | N | | | reason | varchar(255) | YES | | NULL | | +------------+------------------+------+-----+---------+-------+ A second table called 'storage', defined as follows: +-------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+---------+----------------+ | storage_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | user_id | smallint(5) unsigned | NO | MUL | NULL | | | app_id | smallint(5) unsigned | NO | MUL | NULL | | | source_id | smallint(5) unsigned | NO | MUL | NULL | | | event_date | date | NO | MUL | NULL | | | start_tm | time | NO | | NULL | | | end_tm | time | NO | | NULL | | | identifier | varchar(20) | NO | | NULL | | | description | varchar(255) | NO | | NULL | | +-------------+----------------------+------+-----+---------+----------------+ Both table engines is InnoDB. Table 'validation' has FK from storage_id to storage.storage_id. Table 'storage' has two triggers, defined as follows: CREATE TRIGGER storage_to_validation_insert AFTER INSERT ON storage FOR EACH ROW BEGIN INSERT INTO validation VALUES (NEW.storage_id, CAST('N' AS CHAR), ''); END// CREATE TRIGGER storage_to_validation_update AFTER UPDATE ON storage FOR EACH ROW BEGIN UPDATE validation V SET status = 'N' WHERE V.storage_id = NEW.storage_id; END// When the 'storage' table is updated (via the .NET interface - I'm using Visual Studio 2008, VB, .NET Platform 3.5), the trigger is CORRECTLY run and the validation table receives the UPDATE to 'N' on the status field. So, since this is run on the server side, there is no issue on the server. Updating the 'validation' table through the command-line also works fine. However, when the 'validation' table (status field) is updated (e.g., to 'A') via the GUI (thus through the connector), I receive the following error message: "Truncated incorrect DOUBLE value: 'A'" How to repeat: 1. Create both tables: CREATE TABLE IF NOT EXISTS `storage` ( `storage_id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `user_id` SMALLINT UNSIGNED NOT NULL , `app_id` SMALLINT UNSIGNED NOT NULL , `source_id` SMALLINT UNSIGNED NOT NULL , `event_date` DATE NOT NULL , `start_tm` TIME NOT NULL , `end_tm` TIME NOT NULL , `identifier` VARCHAR(20) NOT NULL , `description` VARCHAR(255) NOT NULL , PRIMARY KEY (`storage_id`) , INDEX `fk_storage_users_id` (`user_id` ASC) , INDEX `fk_storage_source_id` (`source_id` ASC) , INDEX `fk_storage_apps_id` (`app_id` ASC) , INDEX `ik_storage_eventdate` (`event_date` ASC) , INDEX `ik_storage_appid` (`app_id` ASC) , INDEX `ik_storage_userid` (`user_id` ASC) , ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `validation` ( `storage_id` INT UNSIGNED NOT NULL , `status` CHAR(1) NOT NULL, `reason` VARCHAR(255) NULL , PRIMARY KEY (`storage_id`) , INDEX `fk_rejected_storage_id` (`storage_id` ASC) , CONSTRAINT `fk_rejected_storage_id` FOREIGN KEY (`storage_id` ) REFERENCES storage` (`storage_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; 2. Create the triggers: DELIMITER // CREATE TRIGGER storage_to_validation_insert AFTER INSERT ON storage FOR EACH ROW BEGIN INSERT INTO validation VALUES (NEW.storage_id, 'N', ''); END// CREATE TRIGGER storage_to_validation_update AFTER UPDATE ON storage FOR EACH ROW BEGIN UPDATE validation V SET STATUS = CAST('N' AS CHAR) WHERE V.storage_id = NEW.storage_id; END// DELIMITER ; 3. Add some records to 'storage': INSERT INTO storage VALUES (1,1,1,1,'2009-07-07','07:00','09:00','Test','Bogus Test 1'); INSERT INTO storage VALUES (2,1,1,1,'2009-07-07','07:00','09:00','Test','Bogus Test 2'); INSERT INTO storage VALUES (3,1,1,1,'2009-07-07','07:00','09:00','Test','Bogus Test 3'); INSERT INTO storage VALUES (4,1,1,1,'2009-07-07','07:00','09:00','Test','Bogus Test 4'); 4. Check 'validation'. There should be four records there, all with the status field set to 'N'. 5. Using the .NET Connector 6.0.4, run the following statement: "UPDATE validation V SET V.status = 'A' AND V.reason = '' WHERE V.storage_id=1; " 6. You should receive the error message "Truncated incorrect DOUBLE value: 'A'" Note: if you force the CAST, the value is inserted as '0': 7. Using the .NET Connector 6.0.4, run the following statement: "UPDATE validation V SET V.status = CAST('A' AS CHAR) AND V.reason = '' WHERE V.storage_id=1; " 8. Check the 'validation' table records. The row with 'storage_id' = 1 should have its 'status' field set to '0'. Suggested fix: Is the Connector converting CHAR(1) fields to another Datatype? DOUBLE or BIT, perhaps?