0

I'm running a task every 5 minutes on MS SQL Server 2008 that links to MySQL on another server in the network. It works probably 80% of the time. The remaining 20% results in MySQL hanging, and a service mysql restart is required.

Here's some info on the query that runs every 5 minutes:

  • ~32,000 rows and 25 columns
  • Engine = MyISAM
  • query (ran from SQL Server 2008) = INSERT INTO MYSQLINTRANET...segments_temp SELECT * FROM ProjectTracking.dbo.segments;
  • Row failure appears to be random
  • Error logs are empty

Is it possible that the ProjectTracking.dbo.segments could be locked resulting in a hang?

Without any reported errors in the error log, where can I go from here?

1

I'd have left this as a comment but I want to quote some code, so I'm doing it here instead. I'd start with trying to track down where the lock is happening. The next time you've got a lock, gather some data. On the MS SQL server run the following:

USE master
select text,wait_time,blocking_session_id AS "Block",
percent_complete, * from sys.dm_exec_requests 
CROSS APPLY sys.dm_exec_sql_text(sql_handle)  AS s2 order by start_time asc

Analyze the return and see what the status is. Is it blocked? Is it waiting on data to return from remote?

On the mySQL server run:

show full processlist;

As well as:

SHOW ENGINE INNODB STATUS

The first will list the running processes, what is the status of your query? What does it say it's waiting on. The second will help you find if you're having a deadlock issue.

  • Thanks for the response - I'll run those tonight when everyone leaves – visevo Feb 26 '15 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.