mysql 5.6 – UPDATE Trigger Causes Deadlock

MySQL version: 5.6

I have a trigger for a table that has a number of conditions. However, the trigger does a few selections above. I get deadlocks on some.

DELIMITER //
DROP TRIGGER IF EXISTS pieces_after_update;

CREATE TRIGGER pieces_after_update
AFTER UPDATE
   ON pieces FOR EACH ROW
BEGIN
    SET @COMPANYID = (SELECT company_id FROM skus where id = NEW.sku_id);
    SET @CLOSINGSTOCK = (SELECT closing_stock FROM skus_warehouses WHERE sku_id=NEW.sku_id AND warehouse_id=NEW.warehouse_id ORDER BY ID DESC LIMIT 1);
    SET @CLOSINGPENDINGSTOCK = (SELECT closing_pending_stock FROM skus_warehouses WHERE sku_id=NEW.sku_id AND warehouse_id=NEW.warehouse_id ORDER BY ID DESC LIMIT 1);
    IF (@CLOSINGSTOCK IS NULL)
    THEN
        SET @CLOSINGSTOCK=0;
    END IF;

    ##movement from putaway_pending to putaway_done for good stock
    IF (NEW.stock_status_id=4 AND NEW.is_bad_stock=0 AND OLD.stock_status_id=3) 
    THEN        
        INSERT INTO skus_warehouses(sku_id, warehouse_id, company_id, comment, stock_changed_by, closing_stock, closing_pending_stock)
        VALUES(NEW.sku_id, NEW.warehouse_id, @COMPANYID, CONCAT_WS(' - ','Stock Added by putaway of serial no ', NEW.id), 1, @CLOSINGSTOCK+1, @CLOSINGPENDINGSTOCK); 
    END IF; 

    ##movement from putaway_done to blocked for an order/outward entity
    IF(NEW.stock_status_id=5  AND NEW.is_bad_stock=0 AND OLD.stock_status_id=4)
    THEN
        INSERT INTO skus_warehouses(sku_id, warehouse_id, company_id, comment, stock_changed_by, closing_stock, closing_pending_stock)
        VALUES(NEW.sku_id, NEW.warehouse_id, @COMPANYID, CONCAT_WS(' - ','Stock blocked for serial no ', NEW.id, ' for ', NEW.outward_entity_type, ' id ', NEW.outward_entity_id), -1, @CLOSINGSTOCK-1, @CLOSINGPENDINGSTOCK); 
    END  IF;

    ##movement from BLOCKED to PUTAWAY_DONE for an order/outward entity. Unblocking case, needs adjustment entry in SkuW
    IF(NEW.stock_status_id=4  AND OLD.stock_status_id=5 AND NEW.is_bad_stock=0)
    THEN        
        INSERT INTO skus_warehouses(sku_id, warehouse_id, company_id, comment, stock_changed_by, closing_stock, closing_pending_stock)
        VALUES(NEW.sku_id, NEW.warehouse_id, @COMPANYID, CONCAT_WS(' - ','Stock UNblocked for serial no ', NEW.id, ' for ', NEW.outward_entity_type, ' id ', NEW.outward_entity_id), 1, @CLOSINGSTOCK+1, @CLOSINGPENDINGSTOCK); 
    END  IF;  

    ##movement from PUTAWAY_DONE to PICKED (missed BLOCKED) for an order/outward entity
    IF(NEW.stock_status_id=6  AND NEW.is_bad_stock=0 AND OLD.stock_status_id=4)
    THEN
        INSERT INTO skus_warehouses(sku_id, warehouse_id, company_id, comment, stock_changed_by, closing_stock, closing_pending_stock)
        VALUES(NEW.sku_id, NEW.warehouse_id, @COMPANYID, CONCAT_WS(' - ','Stock blocked & picked for serial no ', NEW.id, ' for ', NEW.outward_entity_type, ' id ', NEW.outward_entity_id), -1, @CLOSINGSTOCK-1, @CLOSINGPENDINGSTOCK); 
    END  IF;

    ##movement from PICKED to PUTAWAY_DONE for an order/outward entity. Unpicking case, needs adjustment entry in SkuW
    IF(NEW.stock_status_id=4  AND OLD.stock_status_id=6 AND NEW.is_bad_stock=0)
    THEN        
        INSERT INTO skus_warehouses(sku_id, warehouse_id, company_id, comment, stock_changed_by, closing_stock, closing_pending_stock)
        VALUES(NEW.sku_id, NEW.warehouse_id, @COMPANYID, CONCAT_WS(' - ','Stock Unpicked & Unblocked for serial no ', NEW.id, ' for ', OLD.outward_entity_type, ' id ', OLD.outward_entity_id), 1, @CLOSINGSTOCK+1, @CLOSINGPENDINGSTOCK); 
    END  IF; 

    ##movement from PUTAWAY_DONE/good_stock to PUTAWAY_PENDING/bad_stock
    IF(NEW.is_bad_stock=1 AND OLD.stock_status_id=4 AND NEW.stock_status_id=3)
    THEN
        INSERT INTO skus_warehouses(sku_id, warehouse_id, company_id, comment, stock_changed_by, closing_stock, closing_pending_stock)
        VALUES(NEW.sku_id, NEW.warehouse_id, @COMPANYID, CONCAT_WS(' - ','Stock marked as BADSTOCK for serial no ', NEW.id), -1,@CLOSINGSTOCK-1, @CLOSINGPENDINGSTOCK);
    END IF;

    ##movement from PUTAWAY_DONE to INVALIDATED
    IF(OLD.stock_status_id=4 AND NEW.stock_status_id=10)
    THEN
        INSERT INTO skus_warehouses(sku_id, warehouse_id, company_id, comment, stock_changed_by, closing_stock, closing_pending_stock)
        VALUES(NEW.sku_id, NEW.warehouse_id, @COMPANYID, CONCAT_WS(' - ','Invalidated serial no ', NEW.id), -1,@CLOSINGSTOCK-1, @CLOSINGPENDINGSTOCK);
    END IF;

END; //

I know that I get deadlocks on the trigger because I found this SHOW INNODB ENGINE STATUS

*** (1) TRANSACTION:
TRANSACTION 635520869854, ACTIVE 0 sec fetching rows
mysql tables in use 5, locked 3
LOCK WAIT 1625 lock struct(s), heap size 177704, 7983 row lock(s), undo log entries 1
MySQL thread id 2085967891, OS thread handle 0x2b5e2d4d1700, query id 503500721288 10.0.10.59 primary_db_user Creating sort index
SET @CLOSINGSTOCK = (SELECT closing_stock FROM skus_warehouses WHERE sku_id=NEW.sku_id AND warehouse_id=NEW.warehouse_id ORDER BY ID DESC LIMIT 1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 139169 page no 7248658 n bits 216 index `PRIMARY` of table `primary_db`.`skus_warehouses` trx id 635520869854 lock_mode X locks rec but not gap waiting
Record lock, heap no 121 PHYSICAL RECORD: n_fields 14; compact format; info bits 0

The table skus_warehouses has over 100 million records but good indexing. All subsequent queries (which appear to be blocking) are completed in 0.5 to 1 second.

SET @CLOSINGSTOCK = (SELECT closing_stock FROM skus_warehouses WHERE sku_id=NEW.sku_id AND warehouse_id=NEW.warehouse_id ORDER BY ID DESC LIMIT 1)

Is there an external trigger with AF-ON and light measuring button?

I use a D7100 case with some lightweight lenses (Nikkor 50mm and Nikkor 55-300mm) with the backspace key as AF-ON, pressing the shutter button the first time you press in the light metering mode and the shutter button the second time you press the shutter button. Nothing special.

Recently I bought a Sigma 60-600mm and I would like to use it freehand with both hands on the lens. That's why I also bought a small external trigger, which I connected to the Arcaswiss / tripod mounting system.

It works fine, I can easily lift the lens and the camera body and I can zoom in and out very easily.

The only problem is that I can not perform the light metering without pressing a button on the camera body because my two hands are on the lens.

I was wondering if an external trigger supports the AF-ON, light metering and trigger functions. If not, is it possible to do it yourself using the GPS port to which the actual external trigger is connected?

How to automatically create a trigger when I open my spreadsheet (Google App script)

I'm trying to automatically create a time-based trigger when a file is opened. The function works fine if I run it manually, but not when I open the file.

function onOpen(e){

  var Triggers = ScriptApp.getProjectTriggers();
  Logger.log(Triggers);
  for (var i in Triggers){
    var trigger = Triggers(i);
    var func = trigger.getHandlerFunction(); 
    if (func == "Copy"){
      var copy = "True"
      }
  }
  if (copy !== "True"){
    ScriptApp.newTrigger("Copy")
    .timeBased()
    .atHour(2)
    .everyDays(1) // Frequency is required if you are using atHour() or nearMinute()
    .create();
  }

dnd 3.5e – Does dropping out of a threatened field trigger a casual attack?

Falling is an extremely cumbersome, underdetermined part of the rules. However, the basic rule for triggering a casual attack is pretty simple:

To move

Leaving a threatened field usually provokes a casual attack by the threatening opponent. There are two common ways to avoid such an attack: the 5-foot step and the withdrawal action.

Leaving a threatened place provokes normally. As the warblade leaves the threatened field, it leaves it and will provoke.

It could be argued that "move" refers to certain intentional actions performed by creatures to move themselves, and that falling does not count. In the rules, the word "move" or "move" is used to refer to any type of movement, whether or not it is a movement action. I think this is a weak argument. For example, a creature pushed out of a threatened field with a bull rush provokes it explicitly. So does a creature that is (at least probably) carried by a mount from a threatened field. So should fall.

But no part of the rules deals directly with this situation that I can find.

c ++ – What is the most efficient way to trigger an action based on proximity?

I'm developing a 3rd-person platformer that includes a claw hook system.

This gripper system is based on a series of gripping points.

I want these dots to shine from time to time.

These points light up when:

  • In the field of view of the player cone AND
  • At a certain distance from the player.

This & gris; & grasp & # 39; class contains a method ~ & # 39; Shine () # to trigger the effect.


Initial research

My initial research was based on using a cone track from the player for these "gripping objects", which were then found in each and called this shine method.

I could then adjust the angle and height of the cone to better visualize the cone.


issues

By default, Unreal has no conical track, and I'm not sure if using a multibox track followed by an angle check is the most efficient solution.

Flash – Godox X2T-C trigger transmitter does not communicate with the X1R-C receiver

The X2T-C trigger is attached to the Canon 6D and the Canon EX600 RT II to the X1R-C receiver. The channels on the trigger and the receiver are all set to 1 and the groups are set to A. The EX600 RT II flash is set to Manual and the wireless function is disabled. When I press the camera shutter button or the test button (flash) on the X2T-C shutter, the EX600 RT II flash does not extinguish. Does anyone have any ideas?

postgresql – Postgres with powershell scripts – where do trigger messages go?

I have a Windows (10 Pro) Powershell script that reads messages from an external server and inserts them into a Postges (10.5) database table.

The table defines an AFTER INSERT trigger that contains some RAISE NOTICE statements for generating simple messages. When I manually insert a single record using pgAdmin, the message RAISE appears on the Messages tab.

Where do the trigger messages go if they are put into the same table from a powershell script?
How can I display the trigger messages in the Powershell console window?

MySQL syntax error when trying to create a database trigger

Attempting to create the following trigger displays a syntax error. It looks good to me and I have a terrible time figuring it out.

  DELIMITER $$
    CREATE TRIGGER trg_pic_hours
    AFTER INSERT ON CREW
    FOR EACH ROW
    BEGIN
    IF (NEW.CREW_JOB = 'Pilot') THEN
    UPDATE PILOT
    SET PIL_PIC_HRS = TEMP_PIL.PIL_PIC_HRS + CHARTER.CHAR_HOURS_FLOWN
    WHERE CHARTER.CHAR_TRIP = NEW.CHAR_TRIP
    AND PILOT.EMP_NUM = NEW.EMP_NUM;
    ENDIF;
    END$$
    DELIMITER;

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; END' at line 10