2 posts tagged

mysql

Un-cancel Magento Order

If you want to restore canceled order in Magento and Magento 2 use the following SQL query. Order will be restored to “Pending” status.

SET @ORDERID = "41970";
UPDATE `sales_order`
SET `state` = 'new',
`status` = 'pending',
`base_discount_canceled` = '0',
`base_shipping_canceled` = '0',
`base_subtotal_canceled` = '0',
`base_tax_canceled` = '0',
`base_total_canceled` = '0',
`discount_canceled` = '0',
`shipping_canceled` = '0',
`subtotal_canceled` = '0',
`tax_canceled` = '0',
`total_canceled` = '0'
WHERE `entity_id` = @ORDERID;
UPDATE `sales_order_item`
SET `qty_canceled` = '0',
`tax_canceled` = '0',
`discount_tax_compensation_canceled` = '0'
WHERE (`order_id` = @ORDERID)
;
Aug 20   mysql   order   sql

Get locked transactions in MySQL

SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY
FROM INNODB_TRX
WHERE TRX_STATE = 'LOCK WAIT';
SELECT
  waiting_trx_id,
  waiting_pid,
  waiting_query,
  blocking_trx_id,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;
SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;
2018   mysql