Recurring job orders timeing out after about 32 seconds

[jrahma]

Hi,

I am trying to insert recurring (bulk) insert into MySQL table but it's getting timedout.

I increased the connection timeout even to 800 but still having the same problem.

I noticed it's timing out at about 30-32 seconds and only approx 300 records will be inserted.

what could be the problem please?

here is my stored procedure.. try current date as current date and max date as same day next year or after two years.

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_add_new_job_order`(IN param_customer_id int, IN param_cleaner_id int, IN param_job_order_date date, IN param_job_order_date_to date, IN param_start_time time, IN param_end_time time, IN param_job_order_note text, IN param_is_recurring bit, IN param_max_recurring_date date, IN param_is_contract bit, IN param_contract_id int, IN param_total_hours decimal(11, 6), IN param_created_user int, OUT param_record_identity int)
BEGIN
	UPDATE customers SET allow_delete = FALSE WHERE customer_id = param_customer_id;

INSERT INTO job_orders (history_status, status_user, status_date, customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_recurring, max_recurring_date, is_contract, contract_id, total_hours, created_date, created_user) VALUES ('New', param_created_user, NOW(), param_customer_id, param_cleaner_id, param_job_order_date, param_job_order_date_to, param_start_time, param_end_time, param_job_order_note, param_is_recurring, param_max_recurring_date, param_is_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
SET param_record_identity = LAST_INSERT_ID();

IF (param_is_contract = TRUE) THEN
BEGIN
	UPDATE job_orders SET job_order_status = 6 WHERE job_order_id = param_record_identity;
	UPDATE contracts SET remaining_hours = remaining_hours - param_total_hours WHERE contract_id = param_contract_id;
END;
END IF;

IF (param_is_recurring = TRUE) THEN
BEGIN
	REPEAT
		SET param_job_order_date = DATE_ADD(param_job_order_date, INTERVAL 7 DAY);

		-- insert recurring job orders;
		INSERT INTO job_orders (history_status, status_user, status_date, customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_recurring, max_recurring_date, is_contract, contract_id, total_hours, created_date, created_user) VALUES ('New', param_created_user, NOW(), param_customer_id, param_cleaner_id, param_job_order_date, param_job_order_date_to, param_start_time, param_end_time, param_job_order_note, param_is_recurring, param_max_recurring_date, param_is_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
		SET param_record_identity = LAST_INSERT_ID();

		IF (param_is_contract = TRUE) THEN
		BEGIN
			UPDATE job_orders SET job_order_status = 6 WHERE job_order_id = param_record_identity;
			UPDATE contracts SET remaining_hours = remaining_hours - param_total_hours WHERE contract_id = param_contract_id;
		END;
		END IF;

		-- add event log;
		CALL sp_add_event_log("JOBORDER", param_record_identity, param_created_user, "Job order was created.");
		CALL sp_add_event_log("CUSTOMER", param_customer_id, param_created_user, "Job order was created.");
	UNTIL param_job_order_date >= param_max_recurring_date
	END REPEAT;
END;
END IF;
END