# Node hang after INSERT IGNORE INTO  or DELETE FROM .. WHERE 1=1

**URL:** <https://forums.percona.com/t/node-hang-after-insert-ignore-into-or-delete-from-where-1-1/6240>\
**Category:** Percona XtraDB Cluster 5.x\
**Created:** [March 8, 2018, 11:01pm UTC](https://forums.percona.com/t/node-hang-after-insert-ignore-into-or-delete-from-where-1-1/6240 "2018-03-08T23:01:48Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![tmdmmtk4](https://avatars.discourse-cdn.com/v4/letter/t/f9ae1b/32.png) [@tmdmmtk4](https://forums.percona.com/u/tmdmmtk4)\
**Post date:** [March 8, 2018, 11:01pm UTC](https://forums.percona.com/t/node-hang-after-insert-ignore-into-or-delete-from-where-1-1/6240/1 "2018-03-08T23:01:48Z")

</div>

Hi

I built a percona xtradb cluster with three node (5.7.19). Cluster work properly except after i use INSERT IGNORE INTO or DELETE FROM … WHERE 1=1

Here is my query

```auto

[I]INSERT IGNORE INTO catalog_product_entity_group_price_location_level[/I]
[I] ([/I]
[I] SELECT pr.*[/I]
[I] FROM catalog_product_entity_group_price_level pr[/I]
[I] INNER JOIN[/I]
[I] ([/I]
[I] SELECT entity_id, level, event, max(location_level) as max_location_level, customer_group_id, warehouse_id, is_promotion, value[/I]
[I] FROM catalog_product_entity_group_price_level[/I]
[I] WHERE 1 = 1[/I]

[I] GROUP BY entity_id, customer_group_id, warehouse_id, is_promotion ORDER BY NULL[/I]
[I] ) tmp_table[/I]
[I] ON[/I]
[I] pr.entity_id = tmp_table.entity_id[/I]
[I] AND pr.customer_group_id = tmp_table.customer_group_id[/I]
[I] AND pr.warehouse_id = tmp_table.warehouse_id[/I]
[I] AND pr.location_level = tmp_table.max_location_level[/I]
[I] AND pr.is_promotion = tmp_table.is_promotion[/I]
[I] GROUP BY pr.entity_id, pr.warehouse_id, pr.customer_group_id, pr.is_promotion, pr.event, pr.level, pr.location_level[/I]
[I] ORDER BY NULL[/I]
[I] )[/I]

```

- When a node runs this query it still works normally. But after query runs completely, i can read but cannot write any table on this node.
- The result of query is about 10 milion rows
- Node just cannot write for about 1 hour. After that it work normally. I think node sync to other nodes in this time
- Why does it block writing on databases. Please help me

Thanks

---

<div class="post-metadata">

**Author:** ![Kenn\_Takara](https://avatars.discourse-cdn.com/v4/letter/k/b2d939/32.png) [@Kenn\_Takara](https://forums.percona.com/u/Kenn_Takara)\
**Post date:** [March 13, 2018, 7:07pm UTC](https://forums.percona.com/t/node-hang-after-insert-ignore-into-or-delete-from-where-1-1/6240/2 "2018-03-13T19:07:01Z")

</div>

What is your value for wsrep\_slave\_threads? If it hasn’t been changed, the default is 1, which means that only a single thread is handling writes.  
You can check the value by executing "show variables like ‘wsrep\_slave\_threads’; ".

[url][Redirecting](https://www.percona.com/doc/percona-xtradb-cluster/LATEST/wsrep-system-index.html%5B/url%5D)

---

<div class="post-metadata">

**Author:** ![tmdmmtk4](https://avatars.discourse-cdn.com/v4/letter/t/f9ae1b/32.png) [@tmdmmtk4](https://forums.percona.com/u/tmdmmtk4)\
**Post date:** [March 16, 2018, 3:20am UTC](https://forums.percona.com/t/node-hang-after-insert-ignore-into-or-delete-from-where-1-1/6240/3 "2018-03-16T03:20:45Z")

</div>

Hi Kenn Takara  
My nproc shows 16 and wsrep\_slave\_threads have set to 8 before.

---

<div class="post-metadata">

**Author:** ![tmdmmtk4](https://avatars.discourse-cdn.com/v4/letter/t/f9ae1b/32.png) [@tmdmmtk4](https://forums.percona.com/u/tmdmmtk4)\
**Post date:** [March 16, 2018, 3:28am UTC](https://forums.percona.com/t/node-hang-after-insert-ignore-into-or-delete-from-where-1-1/6240/4 "2018-03-16T03:28:50Z")

</div>

Hi Kenn Takara

My value for wsrep\_slave\_threads is 8 and nproc show 16.  
Here is my config for wsrep :

```auto
[mysqld]

wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.20,192.168.1.21,192.168.1.23

binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# Slave thread to use
wsrep_slave_threads= 8

wsrep_log_conflicts

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node IP address
wsrep_node_address=192.168.1.23
# Cluster name
wsrep_cluster_name='OmniCluster'

#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=percona-node4

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING

# SST method
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="xxx"

```

---

<div class="post-metadata">

**Author:** ![krunalbauskar](https://avatars.discourse-cdn.com/v4/letter/k/439d5e/32.png) [@krunalbauskar](https://forums.percona.com/u/krunalbauskar)\
**Post date:** [March 19, 2018, 1:51am UTC](https://forums.percona.com/t/node-hang-after-insert-ignore-into-or-delete-from-where-1-1/6240/5 "2018-03-19T01:51:45Z")

</div>

Just to re-understand the problem:

Say you have 2 node cluster

- Node-1 is where you run the said long-running insert.
- Node-2 is replicating this insert through galera replication.

Now when the insert/delete is completed on node-1 if you execute another insert on NODE-1 … DOES IT HANGS?  
OR  
Do you mean when the insert/delete is completed on node-1 if you execute another insert on NODE-2 … IT HANGS

Latter is not an issue. Node-2 is busy applying the existing replicated write-set. It can’t allow followup write-set to commit till the replicate write-set is committed.

Former looks to be a problem.  
If you are hitting a former case then can please share the reproducible test-case.