craftcms / stripe

Sync and extend Stripe products and subscriptions.
MIT License
5 stars 2 forks source link

Broken migration, STORED NULL is not valid on mariadb 10.4+ #51

Closed okolvik-avento closed 2 weeks ago

okolvik-avento commented 3 weeks ago

The latest migration src/migrations/m240819_121818_loosen_aux_data_uniq.php does not successfully complete on mariadb 10.4+ (10.4.34 to be specific). This also affects the new Install.php script.

This is beacuse mariadb does not support the STORED NULL syntax.

execute SQL: ALTER TABLE {{%stripe_subscriptiondata}} ADD COLUMN [[latestInvoiceId]] VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.\"latest_invoice\"'))) STORED NULL ...Exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL' at line 1
The SQL being executed was: ALTER TABLE `craft_stripe_subscriptiondata` ADD COLUMN `latestInvoiceId` VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.\"latest_invoice\"'))) STORED NULL (/var/www/html/vendor/yiisoft/yii2/db/Schema.php:676)

Valid syntax for mysql:

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

Valid syntax for mariadb:

<type>  [GENERATED ALWAYS]  AS   ( <expression> )
[VIRTUAL | PERSISTENT | STORED]  [UNIQUE] [UNIQUE KEY] [COMMENT <text>]

Note that STORED does not require NULL, as NULL is implied when not specified on generated columns.

CustomCodeIT commented 3 weeks ago

I am experiencing this issue too.

I just tried a fresh install on:

execute SQL: ALTER TABLE {{%stripe_subscriptiondata}} ADD COLUMNlatestInvoiceIdVARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.\"latest_invoice\"'))) STORED NULL; ...Exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL' at line 1

i-just commented 3 weeks ago

Hi, thanks for reporting! I raised a PR for this.

okolvik-avento commented 2 weeks ago

Any chance the fix could be released soon?

i-just commented 2 weeks ago

Stripe 1.3.0 is out with a fix for that. Thanks again!