Recurring job orders timeing out after about 32 seconds

Jassim Rahma

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\_recurri

Richard Deeming

Jassim Rahma wrote:

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

The ConnectionTimeout[^] property specifies how long to wait while trying to establish the initial connection to the database. Your code isn't timing out waiting to establish a connection to the database; it's timing out trying to execute the command. That means you need to change the CommandTimeout[^] property.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Jassim Rahma

Thanks... Now I am able to insert more than 1000 rows and it's running without timing out can I ask one more question please regarding this article: http://www.codeproject.com/Messages/4799058/MySQL-A-connection-attempt-failed-because-the-conn.aspx[^] is it because of the same issue?

Technology News @ www.JassimRahma.com

Richard Deeming

No, that's a different error message.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Jassim Rahma

appreciate if you update that post if you have a solution, Thank you so much

Technology News @ www.JassimRahma.com