I’ve been trying out WooCommerce recently, an e-store solution built on top of WordPress and designed for small to large-sized online merchants.
Among many things, WooCommerce offers a few order statuses out of the box. Successful orders are set to processing and can be subsequently updated, ultimately resulting in the completed state.
To flag orders that are pending local pickup or ready for shipping, I turned to Custom Order Status for WooCommerce, by Tyche Softwares. This third-party plugin allows the shop manager to create and manage additional order statuses.
I created a status for pending pickup and this worked fine. I created an additional status for ready for shipping, and even though this status appeared in the order management options, setting the order to this status did nothing.
Investigation
A curious bug. This plugin is free with a paid premium upgrade to unlock additional features, so I didn’t expect the issue to be a long-standing bug. I checked the plugin issues page and while I didn’t see any recent threads that seemed like my issue, I saw a few mentions of an incompatibility with the new plugin version and PHP 7.4.
A promising lead, but the PHP error logs were clean. I pulled the source and traced how the plugin works. Very effectively, it uses custom post types to manage custom statuses and injects them into WooCommerce’s status list organically.
Nothing fancy here, but I know that WooCommerce leverages WordPress’ post status, as orders are also custom post types. I connected to the database to see specifically how statuses are persisted on existing orders, and then I saw a strong clue! The post status is stored as a varchar(20) column in the posts table. It turns out that the internal serialized value of my shipping status is 21 characters long.
MySQL strict mode!!
When traditional/non-strict mode is enabled, MySQL will truncate char fields on insert or update and produces a warning. However, when strict mode is enabled an error is produced and the query fails instead. My production database runs in strict mode. This is it! This must mean that when I was attempting to update the order status, the update was failing because the new status was too long to fit.
Follow this link for more information on MySQL modes.
Resolution
Fortunately it wasn’t a bug within the third-party plugin itself and making things work was as easy as changing the slug for my shipping status to be shorter (the name can be longer as it is stored as the custom post name).
This is an unseeming oversight however, and it could potentially leave many Shop Managers rather confused. I recommend that the plugin maintainer take this internal limit into consideration and either warn the user with text labels, or truncate the internal status slug when a new status is created.
