Closed rob-baker-ar closed 1 year ago
Database dump sent to support@craftcms.com
I've also got a large number of addresses with a null
value for the ownerId
in the database after the upgrade. Some users have an address associated with them but many do not, and I've ended up writing extra code into our platform in the interim to handle the missing addresses. I didn't realise it might have been a bug with the migration so I may not be able to resolve the address issue at this point, but wanted to add that I've seen the same issue that @rob-baker-ar reports.
FTR addresses with a null value in the ownerId
is expected behavior. An address don't have to belong to a user, it can belong to a store. The exception that @rob-baker-ar is getting is a separate issue.
Having tried this a few more times over the last couple of weeks I'm still having the same problem.
A key point I think: The main thrust of this issue is is not null
values for ownerId
, it's the numeric values in that column that seem to be orphaned from the parent elements
table (i.e. no corresponding matching value in that table).
There is a separate issue to do with a first party commerce plugin that is currently preventing me seeing if a garbage collection run will mitigate or fix any of this but I plan to have another go at that this week.
@rob-baker-ar Does the migration issue resolved when Digital Products is disabled before upgrade?
@pdaleramirez With Digital Products disabled, I still get the integrity constraint exception from https://github.com/craftcms/digital-products/issues/75 when trying to run garbage collection before the upgrade (so as an effect the upgrade still runs with ~1.6million customer records).
For the upgrade itself, after doing:
./craft migrate/all
(which completes successfully, taking approximately 5 minutes)./craft commerce/upgrade
(which also completes successfully, taking approximately 15 minutes)I am still left with the headline issue (numeric ownerId
values on the addresses
table pointing to elements that don't exist - i.e. still throwing the same exception).
@rob-baker-ar This issue has been fixed on craftcms/digital-products version 3.1.0. Please open an issue on the craftcms/digital-products if you are still experiencing the issue
@pdaleramirez
With Digital Products disabled, I still get the integrity constraint exception from https://github.com/craftcms/digital-products/issues/75
I am still left with the headline issue (numeric ownerId values on the addresses table pointing to elements that don't exist - i.e. still throwing the same exception).
Perhaps I'm missing something: how would the fix to digital products affect rows on the address table that are effectively detached from the elements table? There are foreign key constraints on the addresses
table but the ownerId
s from the addresses
table point to rows that do not exist on elements
table.
Looking at this from v3.1.0: https://github.com/craftcms/digital-products/commit/3414823f927be87b4e58e3962d90278f8c95976e craft\digitalproducts\Plugin::_registerGarbageCollection()
calls Gc::deletePartialElements()
which only affects the products
, licenses
and elements
tables.
Addresses tables shouldn't be associated with digital product element as owner. Unless it explicitly assigned through custom module or plugin in that case a custom query should be executed to delete the association.
@pdaleramirez
Addresses tables shouldn't be associated with digital product element as owner.
I couldn't agree more. We have no code that changes ownerId
on addresses in our modules nor have we ever. Put another way: if there are some associations between digital products and addresses, they have not been created by our code. This must have happened as a result of a core or commerce plugin related migration at some point over the last 2-3 years that became apparent after the migration that created the addresses table or as an effect of some other functionality built into the plugin or core.
However, if it is safe to do a delete on the addresses
table where the ownerId
column has a numeric value who's corresponding row is not in the elements
table will that fix this?
@rob-baker-ar Deleting the row should be fine if the foreign key doesn’t exist.
What happened?
Starting versions:
Craft:
Craft Pro 3.7.51
Commerce:3.4.16
Description
After the steps described in "Steps to reproduce", running this query directly on the database:
This might be a clue to an issue when running a similar query from a PHP / Craft context:
Exception thrown is:
Steps to reproduce
composer.json
composer upgrade
craft clear-caches/all
craft cache/purge-all
craft migrate/all
craft commerce/upgrade
In the last step, all defaults were selected, it's output:
All the above steps complete successfully - I have their output if needed.
Row counts
commerce_customers
count (beforecommerce/upgrade
): 1643564commerce_addresses
count (beforecommerce/upgrade
): 2765commerce_customers
count (aftercommerce/upgrade
): 1161addresses
count (aftercommerce/upgrade
): 2767As part of the upgrade the number of rows is massively reduced - generally speaking this is probably good - I don't need 1.6m rows in
commerce_customers
as the huge majority are the result of old carts for customers that never completed an order.Expected behavior
All rows in
addresses
table have a valid value forownerId
.Actual behavior
Something is going wrong during
commerce/upgrade
command leaving broken data in the addresses table - there are both rows with anull
value forownerId
as well as rows that have a integerownerId
that no longer point to it's owner (see the value show in in the exception message).Craft CMS version
4.2.1.1
Craft Commerce version
4.1.0
PHP version
8.1.2
Operating system and version
Ubuntu 22.04 (Linux 5.15.0-46-generic)
Database type and version
MySQL 5.5.5 (mariadb Ver 15.1 Distrib 10.6.7-MariaDB)
Image driver and version
No response
Installed plugins and versions