Description:
Whenever I call a procedure from my insert trigger with this structure:
PROCEDURE internal_create_log_entry (IN in_account_main_id int, IN in_base_type_sc smallint, IN in_record_id int, IN in_base_subtype_sc smallint, IN in_log_type_c smallint, IN in_log_action_c smallint, IN in_JSON_detail json, OUT out_result bool)
It ALWAYS returns Error 1292 (State 22007) "Truncated incorrect DOUBLE value: '0A000'."
Regardless of values. I can use:
CALL internal_create_log_entry (1,1,1,1,1,1, JSON_OBJECT(), result_out);
Doesn't matter of values are hardcoded or reference. I've tried every permutation, including global variables. Nothing works. Also verified the SP is never called - it's failing with the attempted call.
Something is very broken.
How to repeat:
Here's the insert trigger. Note all code works fine with no errors until I try to call the above SP. No version of calling the SP with any values works at all.
CREATE
DEFINER = 'root'@'localhost'
TRIGGER empowercrm_sandbox.trigger_workflow_item_i
AFTER INSERT
ON empowercrm_sandbox.workflow_item
FOR EACH ROW
BEGIN
DECLARE result_bool BOOL;
DECLARE ACCOUNT_MAIN INT DEFAULT 1;
DECLARE debug_location text CHARSET utf8mb4 DEFAULT 'initialization';
DECLARE debug_definition json DEFAULT JSON_OBJECT('stored_procedure', 'trigger_workflow_item_i');
DECLARE debug_details json DEFAULT JSON_OBJECT('source', debug_definition);
-- == [ERROR HANDLING] ========================================
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS @sp_error_count = NUMBER;
GET DIAGNOSTICS CONDITION @sp_error_count @sp_error_state = RETURNED_SQLSTATE,
@sp_error_number = MYSQL_ERRNO, @sp_error_message = MESSAGE_TEXT;
CALL internal_error_handler(JSON_OBJECT('source', 'SQL', 'type', 'exit', 'location', debug_location),
NULL, debug_details, @out_result_details);
END;
-- == [TRIGGER CODE] ========================================
SET debug_location = 'trigger code';
CALL trigger_workflow_item_iu
(
null,
JSON_OBJECT
(
'id',NEW.id,
'item_state_c',NEW.item_state_c,
'item_status_c',NEW.item_status_c,
'item_node_id',NEW.item_node_id
)
);
-- == [INDEX TRACKING] ========================================
SET debug_location = 'index tracking';
IF (@workflow_item_array_insert IS NULL) THEN set @workflow_item_array_insert = JSON_ARRAY(); END IF;
SET @workflow_item_array_insert = JSON_ARRAY_APPEND(@workflow_item_array_insert,'$',NEW.id);
-- == [LOGGING] ========================================
SET debug_location = 'logging';
CALL internal_create_log_entry
(
1,
1,
1,
1,
1,
1,
JSON_OBJECT(),
result_bool
);
END
Suggested fix:
I have no idea what's going on here. It's obvious the wrong error is being returned. Can't debug if the proper error isn't shared.
SP works fine outside of trigger.
Description: Whenever I call a procedure from my insert trigger with this structure: PROCEDURE internal_create_log_entry (IN in_account_main_id int, IN in_base_type_sc smallint, IN in_record_id int, IN in_base_subtype_sc smallint, IN in_log_type_c smallint, IN in_log_action_c smallint, IN in_JSON_detail json, OUT out_result bool) It ALWAYS returns Error 1292 (State 22007) "Truncated incorrect DOUBLE value: '0A000'." Regardless of values. I can use: CALL internal_create_log_entry (1,1,1,1,1,1, JSON_OBJECT(), result_out); Doesn't matter of values are hardcoded or reference. I've tried every permutation, including global variables. Nothing works. Also verified the SP is never called - it's failing with the attempted call. Something is very broken. How to repeat: Here's the insert trigger. Note all code works fine with no errors until I try to call the above SP. No version of calling the SP with any values works at all. CREATE DEFINER = 'root'@'localhost' TRIGGER empowercrm_sandbox.trigger_workflow_item_i AFTER INSERT ON empowercrm_sandbox.workflow_item FOR EACH ROW BEGIN DECLARE result_bool BOOL; DECLARE ACCOUNT_MAIN INT DEFAULT 1; DECLARE debug_location text CHARSET utf8mb4 DEFAULT 'initialization'; DECLARE debug_definition json DEFAULT JSON_OBJECT('stored_procedure', 'trigger_workflow_item_i'); DECLARE debug_details json DEFAULT JSON_OBJECT('source', debug_definition); -- == [ERROR HANDLING] ======================================== DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS @sp_error_count = NUMBER; GET DIAGNOSTICS CONDITION @sp_error_count @sp_error_state = RETURNED_SQLSTATE, @sp_error_number = MYSQL_ERRNO, @sp_error_message = MESSAGE_TEXT; CALL internal_error_handler(JSON_OBJECT('source', 'SQL', 'type', 'exit', 'location', debug_location), NULL, debug_details, @out_result_details); END; -- == [TRIGGER CODE] ======================================== SET debug_location = 'trigger code'; CALL trigger_workflow_item_iu ( null, JSON_OBJECT ( 'id',NEW.id, 'item_state_c',NEW.item_state_c, 'item_status_c',NEW.item_status_c, 'item_node_id',NEW.item_node_id ) ); -- == [INDEX TRACKING] ======================================== SET debug_location = 'index tracking'; IF (@workflow_item_array_insert IS NULL) THEN set @workflow_item_array_insert = JSON_ARRAY(); END IF; SET @workflow_item_array_insert = JSON_ARRAY_APPEND(@workflow_item_array_insert,'$',NEW.id); -- == [LOGGING] ======================================== SET debug_location = 'logging'; CALL internal_create_log_entry ( 1, 1, 1, 1, 1, 1, JSON_OBJECT(), result_bool ); END Suggested fix: I have no idea what's going on here. It's obvious the wrong error is being returned. Can't debug if the proper error isn't shared. SP works fine outside of trigger.