Home » RADICORE » How To » How to deal with multiple inserts in ADD1 pattern
| How to deal with multiple inserts in ADD1 pattern [message #5882] |
Mon, 19 September 2016 05:33  |
kong
Messages: 90
Registered: December 2011
|
Member |
|
|
When using ADD1 to create a new record for a table that has an auto-increment field as primary key, sometimes duplicated records are inserted in the table when user accidentally double or triple clicks the submit button. What would be the best way to avoid or deal with such potential duplicated submissions?
[Updated on: Mon, 19 September 2016 05:47] Report message to a moderator |
|
|
|
| Re: How to deal with multiple inserts in ADD1 pattern [message #5883 is a reply to message #5882] |
Mon, 19 September 2016 12:39  |
kong
Messages: 90
Registered: December 2011
|
Member |
|
|
Tried to work around this problem by using unique id rather than auto increment as primary key, in that way any duplicate inserts should have been caught by Radicore built-in checks as described here: http:// www.tonymarston.net/php-mysql/functions-and-variables.html#n otes._dml_insertrecord.
However, the odd thing is that those Radicore's built-in checks for duplicate primary key don't always work, cause I ended up with fatal errors when triple clicking the ADD1 submit button:
Fatal Error: MySQL: Duplicate entry 'abc' for key 'PRIMARY' (# 1062)
I can see in the sql log that the insert operation happened successfully once, while the framework's duplicate key check happened multiple times:
SELECT count(*) FROM acc_cash_transfer WHERE acc_cash_transfer_id='ABC123'=>Count=0
Here is the full log:
***** \accounting\acc_cash_transfer(add1).php 2016-09-19 23:39:24
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
SELECT SQL_CALC_FOUND_ROWS * FROM php_session WHERE session_id='043u6pg4q6rvjrc03dkpavo3t0' =>Count=1
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
START TRANSACTION (SET AUTOCOMMIT=0)
SELECT count(*) FROM acc_cash_transfer WHERE acc_cash_transfer_id='ABC123'=>Count=0
INSERT INTO acc_cash_transfer SET `acc_cash_transfer_id`='ABC123', `transfer_date`=NULL, `user_id_from`='1', `user_id_to`='2', `cash_amount`=NULL, `comment`=NULL, `sender_confirmed`='0', `receiver_confirmed`='0', `created_date`='2016-09-19 23:39:24', `created_user`='EKL'
SELECT max(tran_seq_no) FROM audit_trn WHERE session_id='583'=>Count=1
***** \accounting\acc_cash_transfer(add1).php 2016-09-19 23:39:25
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
INSERT INTO audit_trn SET `session_id`='583', `tran_seq_no`='2', `task_id`='acc_cash_transfer(add1)', `trn_datetime`='2016-09-19 23:39:24'
SELECT max(table_seq_no) FROM audit_tbl WHERE session_id='583' AND tran_seq_no=2
SELECT SQL_CALC_FOUND_ROWS * FROM php_session WHERE session_id='043u6pg4q6rvjrc03dkpavo3t0' =>Count=1
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
START TRANSACTION (SET AUTOCOMMIT=0)
SELECT count(*) FROM acc_cash_transfer WHERE acc_cash_transfer_id='ABC123'=>Count=0
INSERT INTO audit_tbl SET `session_id`='583', `tran_seq_no`='2', `table_seq_no`='1', `base_name`='dev_accounting', `table_name`='acc_cash_transfer', `pkey`='acc_cash_transfer_id=\'ABC123\''
SELECT count(*) FROM audit_fld WHERE session_id='583' AND tran_seq_no='2' AND table_seq_no='1' AND field_id='acc_cash_transfer_id'=>Count=0
INSERT INTO audit_fld SET `session_id`='583', `tran_seq_no`='2', `table_seq_no`='1', `field_id`='acc_cash_transfer_id', `old_value`=NULL, `new_value`='ABC123'
SELECT count(*) FROM audit_fld WHERE session_id='583' AND tran_seq_no='2' AND table_seq_no='1' AND field_id='user_id_from'=>Count=0
***** \accounting\acc_cash_transfer(add1).php 2016-09-19 23:39:25
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
INSERT INTO audit_fld SET `session_id`='583', `tran_seq_no`='2', `table_seq_no`='1', `field_id`='user_id_from', `old_value`=NULL, `new_value`='1'
SELECT count(*) FROM audit_fld WHERE session_id='583' AND tran_seq_no='2' AND table_seq_no='1' AND field_id='user_id_to'=>Count=0
SELECT SQL_CALC_FOUND_ROWS * FROM php_session WHERE session_id='043u6pg4q6rvjrc03dkpavo3t0' =>Count=1
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
START TRANSACTION (SET AUTOCOMMIT=0)
SELECT count(*) FROM acc_cash_transfer WHERE acc_cash_transfer_id='ABC123'=>Count=0
INSERT INTO audit_fld SET `session_id`='583', `tran_seq_no`='2', `table_seq_no`='1', `field_id`='user_id_to', `old_value`=NULL, `new_value`='2'
SELECT count(*) FROM audit_fld WHERE session_id='583' AND tran_seq_no='2' AND table_seq_no='1' AND field_id='sender_confirmed'=>Count=0
INSERT INTO audit_fld SET `session_id`='583', `tran_seq_no`='2', `table_seq_no`='1', `field_id`='sender_confirmed', `old_value`=NULL, `new_value`='0'
SELECT count(*) FROM audit_fld WHERE session_id='583' AND tran_seq_no='2' AND table_seq_no='1' AND field_id='receiver_confirmed'=>Count=0
INSERT INTO audit_fld SET `session_id`='583', `tran_seq_no`='2', `table_seq_no`='1', `field_id`='receiver_confirmed', `old_value`=NULL, `new_value`='0'
SELECT count(*) FROM audit_fld WHERE session_id='583' AND tran_seq_no='2' AND table_seq_no='1' AND field_id='created_date'=>Count=0
INSERT INTO audit_fld SET `session_id`='583', `tran_seq_no`='2', `table_seq_no`='1', `field_id`='created_date', `old_value`=NULL, `new_value`='2016-09-19 23:39:24'
SELECT count(*) FROM audit_fld WHERE session_id='583' AND tran_seq_no='2' AND table_seq_no='1' AND field_id='created_user'=>Count=0
INSERT INTO audit_fld SET `session_id`='583', `tran_seq_no`='2', `table_seq_no`='1', `field_id`='created_user', `old_value`=NULL, `new_value`='EKL'
SELECT SQL_CALC_FOUND_ROWS workflow_id, workflow_name FROM wf_workflow WHERE start_task_id='acc_cash_transfer(add1)' AND is_valid='Y' AND start_date<='2016-09-19' AND end_date>='2016-09-19' ORDER BY workflow_id asc LOCK IN SHARE MODE=>Count=0
COMMIT; UNLOCK TABLES; SET AUTOCOMMIT=1
SELECT SQL_CALC_FOUND_ROWS * FROM php_session WHERE session_id='043u6pg4q6rvjrc03dkpavo3t0' LIMIT 1=>Count=1
UPDATE php_session SET `last_updated`=now(), `session_data`='...;' WHERE session_id='043u6pg4q6rvjrc03dkpavo3t0'=>Count=1
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
DELETE FROM php_session WHERE last_updated < DATE_SUB(NOW(), INTERVAL 14400 SECOND)=>Count=0
ROLLBACK; UNLOCK TABLES
ROLLBACK; UNLOCK TABLES
SELECT SQL_CALC_FOUND_ROWS * FROM mnu_user WHERE user_id='EKL' =>Count=1
SELECT SQL_CALC_FOUND_ROWS * FROM mnu_user WHERE user_id='EKL' =>Count=1
UPDATE mnu_user SET `in_use`='N', `revised_date`='2016-09-19 23:39:26', `revised_user`='EKL' WHERE user_id='EKL'=>Count=1
SELECT max(tran_seq_no) FROM audit_trn WHERE session_id='583'=>Count=2
UPDATE mnu_user SET `in_use`='N', `revised_date`='2016-09-19 23:39:26', `revised_user`='EKL' WHERE user_id='EKL'=>Count=0
SELECT max(tran_seq_no) FROM audit_trn WHERE session_id='583'=>Count=2
INSERT INTO audit_trn SET `session_id`='583', `tran_seq_no`='3', `task_id`='logoff', `trn_datetime`='2016-09-19 23:39:26'
SELECT max(table_seq_no) FROM audit_tbl WHERE session_id='583' AND tran_seq_no=3
INSERT INTO audit_trn SET `session_id`='583', `tran_seq_no`='4', `task_id`='logoff', `trn_datetime`='2016-09-19 23:39:26'
SELECT max(table_seq_no) FROM audit_tbl WHERE session_id='583' AND tran_seq_no=4
INSERT INTO audit_tbl SET `session_id`='583', `tran_seq_no`='3', `table_seq_no`='1', `base_name`='dev_menu', `table_name`='mnu_user', `pkey`='user_id=\'EKL\''
SELECT count(*) FROM audit_fld WHERE session_id='583' AND tran_seq_no='3' AND table_seq_no='1' AND field_id='in_use'=>Count=0
INSERT INTO audit_tbl SET `session_id`='583', `tran_seq_no`='4', `table_seq_no`='1', `base_name`='dev_menu', `table_name`='mnu_user', `pkey`='user_id=\'EKL\''
SELECT count(*) FROM audit_fld WHERE session_id='583' AND tran_seq_no='4' AND table_seq_no='1' AND field_id='in_use'=>Count=0
INSERT INTO audit_fld SET `session_id`='583', `tran_seq_no`='3', `table_seq_no`='1', `field_id`='in_use', `old_value`='Y', `new_value`='N'
SELECT count(*) FROM audit_fld WHERE session_id='583' AND tran_seq_no='3' AND table_seq_no='1' AND field_id='revised_date'=>Count=0
INSERT INTO audit_fld SET `session_id`='583', `tran_seq_no`='4', `table_seq_no`='1', `field_id`='in_use', `old_value`='Y', `new_value`='N'
SELECT count(*) FROM audit_fld WHERE session_id='583' AND tran_seq_no='4' AND table_seq_no='1' AND field_id='revised_date'=>Count=0
INSERT INTO audit_fld SET `session_id`='583', `tran_seq_no`='3', `table_seq_no`='1', `field_id`='revised_date', `old_value`='2016-09-19 22:51:10', `new_value`='2016-09-19 23:39:26'
SELECT count(*) FROM audit_fld WHERE session_id='583' AND tran_seq_no='3' AND table_seq_no='1' AND field_id='revised_user'=>Count=0
INSERT INTO audit_fld SET `session_id`='583', `tran_seq_no`='4', `table_seq_no`='1', `field_id`='revised_date', `old_value`='2016-09-19 22:51:10', `new_value`='2016-09-19 23:39:26'
SELECT count(*) FROM audit_fld WHERE session_id='583' AND tran_seq_no='4' AND table_seq_no='1' AND field_id='revised_user'=>Count=0
INSERT INTO audit_fld SET `session_id`='583', `tran_seq_no`='3', `table_seq_no`='1', `field_id`='revised_user', `old_value`='EKL', `new_value`='EKL'
INSERT INTO audit_fld SET `session_id`='583', `tran_seq_no`='4', `table_seq_no`='1', `field_id`='revised_user', `old_value`='EKL', `new_value`='EKL'
DELETE FROM php_session WHERE session_id='043u6pg4q6rvjrc03dkpavo3t0'=>Count=0
DELETE FROM php_session WHERE last_updated < DATE_SUB(NOW(), INTERVAL 14400 SECOND)=>Count=0
DELETE FROM php_session WHERE session_id='043u6pg4q6rvjrc03dkpavo3t0'=>Count=1
DELETE FROM php_session WHERE last_updated < DATE_SUB(NOW(), INTERVAL 14400 SECOND)=>Count=0
So, then I tried replicate this problem by multi-clicking submit button when creating a new subsystem, which turned out the Radicore built-in duplicate checks did work and a validation error message was returned correctly:A record already exists with this ID.
The sql log in this case showed SELECT count(*) FROM mnu_subsystem WHERE subsys_id='ABC123'=>Count=0 SELECT count(*) FROM mnu_subsystem WHERE subsys_id='ABC123'=>Count=1
Following is the full log:
***** \menu\mnu_subsystem(add1).php 2016-09-19 23:52:01
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
SELECT SQL_CALC_FOUND_ROWS * FROM php_session WHERE session_id='1rticv7i69gercel9jl61iifj6' =>Count=1
START TRANSACTION (SET AUTOCOMMIT=0)
SELECT count(*) FROM mnu_subsystem WHERE subsys_id='ABC123'=>Count=0
INSERT INTO mnu_subsystem SET `subsys_id`='ABC123', `subsys_desc`='123', `subsys_dir`='123', `task_prefix`=NULL, `created_date`='2016-09-19 23:52:01', `created_user`='EKL'
SELECT max(tran_seq_no) FROM audit_trn WHERE session_id='578'=>Count=14
INSERT INTO audit_trn SET `session_id`='578', `tran_seq_no`='15', `task_id`='mnu_subsystem(add1)', `trn_datetime`='2016-09-19 23:52:01'
SELECT max(table_seq_no) FROM audit_tbl WHERE session_id='578' AND tran_seq_no=15
INSERT INTO audit_tbl SET `session_id`='578', `tran_seq_no`='15', `table_seq_no`='1', `base_name`='dev_menu', `table_name`='mnu_subsystem', `pkey`='subsys_id=\'ABC123\''
SELECT count(*) FROM audit_fld WHERE session_id='578' AND tran_seq_no='15' AND table_seq_no='1' AND field_id='subsys_id'=>Count=0
INSERT INTO audit_fld SET `session_id`='578', `tran_seq_no`='15', `table_seq_no`='1', `field_id`='subsys_id', `old_value`=NULL, `new_value`='ABC123'
SELECT count(*) FROM audit_fld WHERE session_id='578' AND tran_seq_no='15' AND table_seq_no='1' AND field_id='subsys_desc'=>Count=0
INSERT INTO audit_fld SET `session_id`='578', `tran_seq_no`='15', `table_seq_no`='1', `field_id`='subsys_desc', `old_value`=NULL, `new_value`='123'
SELECT count(*) FROM audit_fld WHERE session_id='578' AND tran_seq_no='15' AND table_seq_no='1' AND field_id='subsys_dir'=>Count=0
INSERT INTO audit_fld SET `session_id`='578', `tran_seq_no`='15', `table_seq_no`='1', `field_id`='subsys_dir', `old_value`=NULL, `new_value`='123'
SELECT count(*) FROM audit_fld WHERE session_id='578' AND tran_seq_no='15' AND table_seq_no='1' AND field_id='created_date'=>Count=0
INSERT INTO audit_fld SET `session_id`='578', `tran_seq_no`='15', `table_seq_no`='1', `field_id`='created_date', `old_value`=NULL, `new_value`='2016-09-19 23:52:01'
SELECT count(*) FROM audit_fld WHERE session_id='578' AND tran_seq_no='15' AND table_seq_no='1' AND field_id='created_user'=>Count=0
INSERT INTO audit_fld SET `session_id`='578', `tran_seq_no`='15', `table_seq_no`='1', `field_id`='created_user', `old_value`=NULL, `new_value`='EKL'
***** \menu\mnu_subsystem(add1).php 2016-09-19 23:52:02
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
SELECT SQL_CALC_FOUND_ROWS * FROM php_session WHERE session_id='1rticv7i69gercel9jl61iifj6' =>Count=1
START TRANSACTION (SET AUTOCOMMIT=0)
SELECT SQL_CALC_FOUND_ROWS workflow_id, workflow_name FROM wf_workflow WHERE start_task_id='mnu_subsystem(add1)' AND is_valid='Y' AND start_date<='2016-09-19' AND end_date>='2016-09-19' ORDER BY workflow_id asc LOCK IN SHARE MODE=>Count=0
SELECT count(*) FROM mnu_subsystem WHERE subsys_id='ABC123'=>Count=1
ROLLBACK; UNLOCK TABLES
SELECT SQL_CALC_FOUND_ROWS mnu_role.role_id, global_access, (SELECT GROUP_CONCAT(role_id ORDER BY role_id SEPARATOR ',') FROM mnu_user_role WHERE user_id='EKL') AS role_list FROM mnu_role
LEFT JOIN mnu_user_role AS mur ON (mur.user_id='EKL' AND mur.is_primary='Y') WHERE mnu_role.role_id=mur.role_id ORDER BY mnu_role.role_id asc =>Count=1
COMMIT; UNLOCK TABLES; SET AUTOCOMMIT=1
SELECT SQL_CALC_FOUND_ROWS * FROM php_session WHERE session_id='1rticv7i69gercel9jl61iifj6' LIMIT 1=>Count=1
SELECT SQL_CALC_FOUND_ROWS x.task_id, x.task_desc, x.script_id, x.pattern_id, x.is_disabled, mnu_nav_button.button_text, mnu_nav_button.context_preselect, mnu_nav_button.sort_seq FROM mnu_nav_button LEFT JOIN mnu_task ON (mnu_task.task_id = mnu_nav_button.task_id_snr) LEFT JOIN mnu_task AS x ON (x.task_id = mnu_nav_button.task_id_jnr) WHERE mnu_task.task_id='mnu_subsystem(add1)' HAVING x.is_disabled='N' ORDER BY mnu_nav_button.sort_seq asc =>Count=0
***** \menu\mnu_subsystem(add1).php 2016-09-19 23:52:02
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
SELECT SQL_CALC_FOUND_ROWS * FROM php_session WHERE session_id='1rticv7i69gercel9jl61iifj6' =>Count=1
SELECT SQL_CALC_FOUND_ROWS * FROM php_session WHERE session_id='1rticv7i69gercel9jl61iifj6' LIMIT 1=>Count=1
UPDATE php_session SET `last_updated`=now(), `session_data`='...;' WHERE session_id='1rticv7i69gercel9jl61iifj6'=>Count=1
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
DELETE FROM php_session WHERE last_updated < DATE_SUB(NOW(), INTERVAL 14400 SECOND)=>Count=0
UPDATE php_session SET `last_updated`=now(), `session_data`='...;' WHERE session_id='1rticv7i69gercel9jl61iifj6'=>Count=1
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
DELETE FROM php_session WHERE last_updated < DATE_SUB(NOW(), INTERVAL 14400 SECOND)=>Count=0
SELECT SQL_CALC_FOUND_ROWS mnu_task.task_id, mnu_task.task_desc, mnu_task.button_text, mnu_task.task_type, mnu_task.script_id, mnu_task.is_disabled, mnu_task.pattern_id, mnu_task.subsys_id, mnu_task.initial_passthru, mnu_task.selection_fixed, mnu_task.selection_temp, mnu_task.settings, mnu_task.order_by, mnu_task.keep_data, mnu_task.log_sql_query, mnu_task.screen_refresh, mnu_task.use_https, mnu_task.max_execution_time, mnu_task.task_id_run_at_end, mnu_task.task_id_run_at_cancel, mnu_task.created_date, mnu_task.created_user, mnu_task.revised_date, mnu_task.revised_user, mnu_pattern.pattern_desc, mnu_subsystem.subsys_desc, mnu_task_passthru.task_desc AS task_desc_passthru, mnu_task_run_at_end.task_desc AS task_desc_run_at_end, mnu_task_run_at_cancel.task_desc AS task_desc_run_at_cancel FROM mnu_task LEFT JOIN mnu_pattern ON (mnu_pattern.pattern_id=mnu_task.pattern_id) LEFT JOIN mnu_subsystem ON (mnu_subsystem.subsys_id=mnu_task.subsys_id) LEFT JOIN mnu_task AS mnu_task_passthru ON (mnu_task_passthru.task_id=mnu_task.initial_passthru) LEFT JOIN mnu_task AS mnu_task_run_at_end ON (mnu_task_run_at_end.task_id=mnu_task.task_id_run_at_end) LEFT JOIN mnu_task AS mnu_task_run_at_cancel ON (mnu_task_run_at_cancel.task_id=mnu_task.task_id_run_at_cancel) WHERE mnu_task.task_id='mnu_subsystem(add1)' ORDER BY mnu_task.task_id asc =>Count=1
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
SELECT SQL_CALC_FOUND_ROWS * FROM php_session WHERE session_id='1rticv7i69gercel9jl61iifj6' LIMIT 1=>Count=1
UPDATE php_session SET `last_updated`=now(), `session_data`='...;' WHERE session_id='1rticv7i69gercel9jl61iifj6'=>Count=1
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
DELETE FROM php_session WHERE last_updated < DATE_SUB(NOW(), INTERVAL 14400 SECOND)=>Count=0
***** \menu\mnu_subsystem(add1).php 2016-09-19 23:52:03
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
SELECT SQL_CALC_FOUND_ROWS * FROM php_session WHERE session_id='1rticv7i69gercel9jl61iifj6' =>Count=1
START TRANSACTION (SET AUTOCOMMIT=0)
SELECT count(*) FROM mnu_subsystem WHERE subsys_id='ABC123'=>Count=1
ROLLBACK; UNLOCK TABLES
SELECT SQL_CALC_FOUND_ROWS mnu_role.role_id, global_access, (SELECT GROUP_CONCAT(role_id ORDER BY role_id SEPARATOR ',') FROM mnu_user_role WHERE user_id='EKL') AS role_list FROM mnu_role
LEFT JOIN mnu_user_role AS mur ON (mur.user_id='EKL' AND mur.is_primary='Y') WHERE mnu_role.role_id=mur.role_id ORDER BY mnu_role.role_id asc =>Count=1
SELECT SQL_CALC_FOUND_ROWS x.task_id, x.task_desc, x.script_id, x.pattern_id, x.is_disabled, mnu_nav_button.button_text, mnu_nav_button.context_preselect, mnu_nav_button.sort_seq FROM mnu_nav_button LEFT JOIN mnu_task ON (mnu_task.task_id = mnu_nav_button.task_id_snr) LEFT JOIN mnu_task AS x ON (x.task_id = mnu_nav_button.task_id_jnr) WHERE mnu_task.task_id='mnu_subsystem(add1)' HAVING x.is_disabled='N' ORDER BY mnu_nav_button.sort_seq asc =>Count=0
SELECT SQL_CALC_FOUND_ROWS mnu_task.task_id, mnu_task.task_desc, mnu_task.button_text, mnu_task.task_type, mnu_task.script_id, mnu_task.is_disabled, mnu_task.pattern_id, mnu_task.subsys_id, mnu_task.initial_passthru, mnu_task.selection_fixed, mnu_task.selection_temp, mnu_task.settings, mnu_task.order_by, mnu_task.keep_data, mnu_task.log_sql_query, mnu_task.screen_refresh, mnu_task.use_https, mnu_task.max_execution_time, mnu_task.task_id_run_at_end, mnu_task.task_id_run_at_cancel, mnu_task.created_date, mnu_task.created_user, mnu_task.revised_date, mnu_task.revised_user, mnu_pattern.pattern_desc, mnu_subsystem.subsys_desc, mnu_task_passthru.task_desc AS task_desc_passthru, mnu_task_run_at_end.task_desc AS task_desc_run_at_end, mnu_task_run_at_cancel.task_desc AS task_desc_run_at_cancel FROM mnu_task LEFT JOIN mnu_pattern ON (mnu_pattern.pattern_id=mnu_task.pattern_id) LEFT JOIN mnu_subsystem ON (mnu_subsystem.subsys_id=mnu_task.subsys_id) LEFT JOIN mnu_task AS mnu_task_passthru ON (mnu_task_passthru.task_id=mnu_task.initial_passthru) LEFT JOIN mnu_task AS mnu_task_run_at_end ON (mnu_task_run_at_end.task_id=mnu_task.task_id_run_at_end) LEFT JOIN mnu_task AS mnu_task_run_at_cancel ON (mnu_task_run_at_cancel.task_id=mnu_task.task_id_run_at_cancel) WHERE mnu_task.task_id='mnu_subsystem(add1)' ORDER BY mnu_task.task_id asc =>Count=1
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
SELECT SQL_CALC_FOUND_ROWS * FROM php_session WHERE session_id='1rticv7i69gercel9jl61iifj6' LIMIT 1=>Count=1
UPDATE php_session SET `last_updated`=now(), `session_data`='...;' WHERE session_id='1rticv7i69gercel9jl61iifj6'=>Count=1
SET names 'UTF8'
SET time_zone = '+08:00'
SET SESSION sql_mode=CONCAT('ANSI_QUOTES,',@@sql_mode)
DELETE FROM php_session WHERE last_updated < DATE_SUB(NOW(), INTERVAL 14400 SECOND)=>Count=0
I explored some more and noticed that especially in newly created subsystems with just 1 simple table, this bug could be replicated more readily than in complicated subsystems such as the MENU subsystem. Maybe this is because complicated subsystems have to serialize much more session data than simple subsystems and somehow this gives the first Insert statement enough time to be committed before the next duplicate key check happens. While for the simple subsystems, none of the Insert statements have been committed before the duplicate key checks happen, so all duplicate key checks return 0 resulting in multiple duplicate Insert statements to be committed afterwards and the database returning fatal error crashing the application.
[Updated on: Mon, 19 September 2016 13:31] Report message to a moderator |
|
|
|
| Re: How to deal with multiple inserts in ADD1 pattern [message #5884 is a reply to message #5882] |
Mon, 19 September 2016 14:17 |
AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
According to the MySQL manual it is simply not possible for multiple inserts into a table which has an auto-increment primary key to fail with a duplicate key error. Each insert will increment the number, so it is not possible for the same number to be returned for different inserts. I cannot duplicate this supposed bug in my system.
the logs you have provided show a primary key with the value "ABC123" so as it is not pure numeric it cannot be an auto-increment column.
If using an ADD1 pattern the user presses the SUBMIT button more than once then the insert will be attempted more than once. If the primary key is a user-supplied value then this will result in a "duplicate key" error.
If different users try to insert a record with the same primary key at the same time then the first insert will succeed and all others will fail. This is expected behaviour. Each INSERT operation is surrounded by a START TRANSACTION and a COMMIT/ROLLBACK during which the database is locked. Perhaps you need to examine your locking strategy.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: How to deal with multiple inserts in ADD1 pattern [message #5885 is a reply to message #5884] |
Mon, 19 September 2016 15:07 |
kong
Messages: 90
Registered: December 2011
|
Member |
|
|
My first post is about using the auto-increment, which will just produce multiple duplicated records with incremented primary key as expected.
In my second post I tried to avoid getting these duplicated records with just incremented primary keys by changing the table to use varchar(13) as primary key instead of auto-increment. The hope was that then only one record should be generated and subsequent inserts should have resulted in "duplicate error", just as you said. It is in my second post (using unique key ABC123) that I discovered the bug that shows the system does not always result in "duplicate error", but will sometimes throw a fatal error by MySQL crashing the application. I am using only standard transaction pattern components without any customization, and have not made any changes or customization in locking strategy between the components.
The content of this topic has digressed from the title. So, I have raised a new topic here: http:// radicore.org/fud/index.php?t=msg&goto=5886&#msg_5886
[Updated on: Tue, 20 September 2016 01:35] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 13:31:43 EST 2024
Total time taken to generate the page: 0.01138 seconds
|
Radicore Forum
Search
Help
Members
Register
Login
Home
