WooCommerce clean Orphaned variants

Find the orphaned variants

SELECT * FROM `wp_posts` o
LEFT OUTER JOIN `wp_posts` r
ON o.post_parent = r.ID
WHERE r.id IS null AND o.post_type = 'product_variation'

And delete them

DELETE o FROM `wp_posts` o
LEFT OUTER JOIN `wp_posts` r
ON o.post_parent = r.ID
WHERE r.id IS null AND o.post_type = 'product_variation'

WooCommerce incorrect out of stock error on checkout

It took me a few hours, to debug the cause of an out of stock error for a Woocommerce site.

The product variation had stock available. When performing a checkout it cause an error that the product didn't have enough stock.

I finally figured out what causes this problem:

Woocommerce tries to reserve a product via the function
'reserve_stock_for_product' (woocommerce/src/Checkout/Helpers/ReserveStock.php)

This method joins several tables: wp_wc_reserved_stock and wp_post_meta.

In my case the tables had different storage engines. wc_reserved_stock had a INNODB storage engine. wp_post_meta was MyISAM.

This fails with this particular INSERT query. Because it cannot joins these tables in a single transaction.

So don't mix table engines!!

(Which happen when you migrate an old site to a new server with a different default storage enginge)

SQL Script to generate collation change sql statements

Nice answer from https://stackoverflow.com/questions/10859966/how-to-convert-all-tables-in-database-to-one-collation

I adjusted it, so you can specify an OLD collation and new collation.
This way staying away from other collations

-- set your database and new charsets/collations here
SET @MY_SCHEMA = "schema_name";
SET @MY_CHARSET = "utf8mb4";
SET @MY_COLLATION = "utf8mb4_unicode_ci";
SET @OLD_CHARSET = "utf8";
SET @OLD_COLLATION = "utf8_general_ci";

-- tables
SELECT DISTINCT
    CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET ", @MY_CHARSET,  " COLLATE ", @MY_COLLATION) as queries
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=@MY_SCHEMA
  AND TABLE_TYPE="BASE TABLE"
  AND TABLE_COLLATION=@OLD_COLLATION

UNION

-- table columns
SELECT DISTINCT
    CONCAT("ALTER TABLE ", C.TABLE_NAME, " CHANGE ", C.COLUMN_NAME, " ", C.COLUMN_NAME, " ", C.COLUMN_TYPE, " CHARACTER SET ", @MY_CHARSET, " COLLATE ",  @MY_COLLATION) as queries
FROM INFORMATION_SCHEMA.COLUMNS as C
    LEFT JOIN INFORMATION_SCHEMA.TABLES as T
        ON C.TABLE_NAME = T.TABLE_NAME
WHERE C.COLLATION_NAME is not null
    AND C.TABLE_SCHEMA=@MY_SCHEMA
    AND T.TABLE_TYPE="BASE TABLE"
    AND C.CHARACTER_SET_NAME = @OLD_CHARSET
    AND C.COLLATION_NAME  = @OLD_COLLATION

UNION

-- views
SELECT DISTINCT
    CONCAT("CREATE OR REPLACE VIEW ", V.TABLE_NAME, " AS ", V.VIEW_DEFINITION, ";") as queries
FROM INFORMATION_SCHEMA.VIEWS as V
    LEFT JOIN INFORMATION_SCHEMA.TABLES as T
        ON V.TABLE_NAME = T.TABLE_NAME
WHERE V.TABLE_SCHEMA=@MY_SCHEMA
    AND T.TABLE_TYPE="VIEW";