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
[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