[Rituparna Kashyap]

Hello,

As there is no ssh information in the xml file and as you said that your are using ssh tunneling, is that for security reason you have removed the lines from the xml file?

Now, we tried reproducing your case but there was no LOCK WAIT issue. But in another test case we made the source same as the target and we got same issue as yours.

By any chance is yours source and the target database are same, as we saw in your SHOW ENGINE INNODB STATUS of your target during the synchronization process both the locking and locked transaction start at the same time and the process id is same. The same is seen in our case when the source and target are same.

Please find the attach file and comment.

[Rituparna Kashyap]

Hi,

The snapshot of INNODB STATUS, clearly show that there is another transaction going on the source database that is blocking SJA.

Code:

—TRANSACTION 0 411490, ACTIVE 99 sec, process no 4030, OS thread id 1167542592
#
12 LOCK struct(s), heap size 3024
#
MySQL thread id 5, query id 426 localhost 127.0.0.1 deploy
#
Trx READ VIEW will NOT see trx WITH id >= 0 411491, sees < 0 411491

Now as SJA use SELECT …. LOCK IN SHARE MODE, so if there is any other transaction updating(or inserting) or uncommitted update (or insert) the select query will wait to hold the lock on the resource.

Now the interesting point is that both the transaction (one that of SJA and another that of the blocking transaction) are active from same time interval i.e 99 sec.

Code:

—TRANSACTION 0 411494, ACTIVE 99 sec, process no 4030, OS thread id 1167276352 starting index read

Code:

—TRANSACTION 0 411490, ACTIVE 99 sec, process no 4030, OS thread id 1167542592

Can you give me a few details.

1. Result of SHOW OPEN TABLES before executing SJA

2. Result of SHOW OPEN TABLES while in lockstate

** Note : For both 1 & 2 it will be enough to give the output of the scheduled database

3. Result of SHOW ENGINE INNODB STATUS while in lockstate

4. Result of SHOW PROCESSLIST while in lockstate

5. The xml files that you are using to execute SJA.

6. Is there any other application/backup job/replication running which use the user deploy.

[Rituparna Kashyap]

You can install scripts like below in the init.d

Step 1 : Created a script called MONyogd in the /etc/init.d/

Step 2 : Copy the following into the script. ( Change the MONyog installation directory if it is not /usr/local/ , it is defined in INSTALLDIR variable in the script)

#!/bin/bash

INSTALLDIR=”/usr/local”

MONYOGBIN=”$INSTALLDIR/MONyog/bin/MONyog”

case “$1” in

'start')

echo “Starting MONyog: “

if [ $((`pgrep MONyog-bin | wc -l`)) -gt 0 ]; then

echo “MONyog is already running.”

else

# Start MONyog.

$MONYOGBIN start

if [ $((`pgrep MONyog-bin | wc -l`)) -gt 0 ]; then

echo “MONyog started successfully!”

else

echo “Unable to start MONyog.”

exit 0

fi

fi

;;

'stop')

if [ $((`pgrep MONyog-bin | wc -l`)) -gt 0 ]; then

echo -n “Sending MONyog signal to stop: “

pkill -15 -x MONyog-bin

echo -n “Shutting down: “

echo ” “

echo “MONyog stopped successfully!”

else

echo “MONyog is not in memory…”

fi

;;

'reload'|'restart')

echo “Restarting MONyog…”

$0 stop

$0 start

;;

'status')

if [ $((`pgrep MONyog-bin`)) -gt 0 ]; then

echo -n “MONyog is running!”

echo ” “

else

echo -n “MONyog is NOT running!”

echo ” “

fi

;;

*)

echo “Usage: $0 {start|stop|restart|reload|status}”

exit 1

;;

esac

Step 3 : make the script executable by chmod +x /etc/init.d/MONyogd

Step 4 : Use Debian utility update-rc.d to install the script.

update-rc.d MONyogd defaults

This will make MONyog start up in boot time. You can also issue service MONyogd start/stop/restart/status any time to have the respective action done.

[Author]

Posts