Fix commerce_product_limits_update_9001() to support other storage engines

Try updating to 1.0.3 and please report back if the issue is fixed, but that does seem to indicate a problem with your installation.

I'm supposed to be checking if the table exists... So not sure what else I can do there, I tested the update hook on my install prior to committing it, and I didn't encounter issue... Is the error message similar?

Did you check the DB to see if that table exists?
Worst case scenario you can stay on 1.0.1 for now... just hardcode the version in composer.json until you figure out what is wrong with your install.

Encounter same issue in DB update.
1.0.1 - no issues.
1.0.2 - DB update error.

$ composer update drupal/commerce_product_limits -W
Gathering patches for root package.
Loading composer repositories with package information
Updating dependencies
Lock file operations: 0 installs, 1 update, 0 removals
  - Upgrading drupal/commerce_product_limits (1.0.1 => 1.0.2)
Writing lock file
Installing dependencies from lock file (including require-dev)
Package operations: 0 installs, 1 update, 0 removals
  - Downloading drupal/commerce_product_limits (1.0.2)
Gathering patches for root package.
Gathering patches for dependencies. This might take a minute.
  - Upgrading drupal/commerce_product_limits (1.0.1 => 1.0.2): Extracting archive
Generating optimized autoload files
53 packages you are using are looking for funding.
Use the `composer fund` command to find out more!
Scaffolding files for pantheon-systems/drupal-integrations:
  - Skip [web-root]/sites/default/settings.php because it already exists and overwrite is false.
> DrupalProject\composer\ScriptHandler::createRequiredFiles
No security vulnerability advisories found.

$ drush updb -y                                    
 ------------------------- ----------- --------------- ------------------------------------ 
  Module                    Update ID   Type            Description                         
 ------------------------- ----------- --------------- ------------------------------------ 
  commerce_product_limits   9001        hook_update_n   9001 - Implements hook_update_N().  
 ------------------------- ----------- --------------- ------------------------------------ 


 // Do you wish to run the specified pending updates?: yes.                                                             

>  [notice] Update started: commerce_product_limits_update_9001
>  [error]  SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'maximum_order_quantity_value' at row 5: ALTER TABLE commerce_product_variation__a7288e9c31 MODIFY maximum_order_quantity_value INT(10) UNSIGNED NOT NULL; Array
> (
> )
>  
>  [error]  Update failed: commerce_product_limits_update_9001 
 [error]  Update aborted by: commerce_product_limits_update_9001 
 [error]  Finished performing updates. 
Failed to execute command drush updb -y: exit status 1

The DB table table

maximum_order_quantity_value	tinyint(4)

There are negative values inside the maximum_order_quantity_value column. So UNSIGNED is causing the problem.

Hm... What is the usecase for a negative allowed quantity? How is that supposed to work?

Good question, let me run that through client.

Looks like there shouldn't be any negative value here, changed to zero will allow the DB update.

My issue was caused by PostgreSQL-specific syntax, which was incompatible with other database types. To resolve this, I refactored the code to use Drupal's Database API, ensuring compatibility across multiple database servers. After making this change, the update ran successfully without any further issues.

Here's the patch

@rokkay: Thank you for this, slightly adapted your changes and committed them! Marking this as fixed...