tag2 issue when merging

[stuartlynne]

We ran an event last weekend that involved pulling in some license holder data with tag2 set so we could support using their existing tags.

Merging duplicate license holders today I'm getting this if I select the newer record. Merging to the older record works.

Request Method: GET Request URL: http://racedb.wimsey.online/RaceDB/LicenseHolders/LicenseHoldersManageDuplicates/LicenseHoldersMergeDuplicatesOK/6108/10621,6108/

Django Version: 5.1.1 Python Version: 3.10.15 Installed Applications: ('django.contrib.admin', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.messages', 'django.contrib.staticfiles', 'django_extensions', 'crispy_forms', 'crispy_bootstrap3', 'core') Installed Middleware: ['django.middleware.security.SecurityMiddleware', 'django.contrib.sessions.middleware.SessionMiddleware', 'django.middleware.common.CommonMiddleware', 'django.middleware.csrf.CsrfViewMiddleware', 'django.contrib.auth.middleware.AuthenticationMiddleware', 'django.contrib.messages.middleware.MessageMiddleware', 'django.middleware.clickjacking.XFrameOptionsMiddleware']

Traceback (most recent call last): File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params)

The above exception (duplicate key value violates unique constraint "core_licenseholder_existing_tag2_key" DETAIL: Key (existing_tag2)=(30187E7C3CA35DE138AF8E2A) already exists. ) was the direct cause of the following exception: File "/usr/local/lib/python3.10/site-packages/django/core/handlers/exception.py", line 55, in inner response = get_response(request) File "/usr/local/lib/python3.10/site-packages/django/core/handlers/base.py", line 197, in _get_response response = wrapped_callback(request, *callback_args, *callback_kwargs) File "/RaceDB/core/views.py", line 801, in LicenseHoldersMergeDuplicatesOK license_holder_merge_duplicates( license_holder_merge, duplicates ) File "/RaceDB/core/models.py", line 5794, in license_holder_merge_duplicates p.save() File "/RaceDB/core/models.py", line 3924, in save license_holder.save() File "/RaceDB/core/models.py", line 2547, in save super().save( args, **kwargs ) File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 891, in save self.save_base( File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 997, in save_base updated = self._save_table( File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 1129, in _save_table updated = self._do_update( File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 1194, in _do_update return filtered._update(values) > 0 File "/usr/local/lib/python3.10/site-packages/django/db/models/query.py", line 1278, in _update return query.get_compiler(self.db).execute_sql(CURSOR) File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 2003, in execute_sql cursor = super().execute_sql(result_type) File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1574, in execute_sql cursor.execute(sql, params) File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 122, in execute return super().execute(sql, params) File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 79, in execute return self._execute_with_wrappers( File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers return executor(sql, params, many, context) File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 100, in _execute with self.db.wrap_database_errors: File "/usr/local/lib/python3.10/site-packages/django/db/utils.py", line 91, in exit raise dj_exc_value.with_traceback(traceback) from exc_value File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params)

Exception Type: IntegrityError at /RaceDB/LicenseHolders/LicenseHoldersManageDuplicates/LicenseHoldersMergeDuplicatesOK/6108/10621,6108/ Exception Value: duplicate key value violates unique constraint "core_licenseholder_existing_tag2_key" DETAIL: Key (existing_tag2)=(30187E7C3CA35DE138AF8E2A) already exists.

[esitarski]

There is a database constraint that says that all tag2 fields by competition and category must be unique. Otherwise, two riders could get the same tag. That would be BAD, as it would totally mess up the results.

Are you reusing tags rather than creating new unique ones?

Obviously, having an error message is not a good way of handling this, but the issue and the constraint is valid.

I have three suggestions:

1) Always use unique tags, don't reuse tags that other riders already have. Problem solved.

2) Automatically set any duplicate tags to None to avoid the "duplicate key unique constraint" problem. This assumes that the merge records are the "correct" ones, and that you are reusing tags and don't actually have any duplicates. If you have duplicates, then you will never know, and the results will potentially be messed up if any of those riders use the duplicate tags.

3) Reject merging tags that cause duplicate tag issues, and report that record that is causing the conflict. This could involve a lot of issue to chase down.

On Tue, Oct 8, 2024 at 5:05 PM Stuart Lynne @.***> wrote:

We ran an event last weekend that involved pulling in some license holder data with tag2 set so we could support using their existing tags.

Merging duplicate license holders today I'm getting this if I select the newer record. Merging to the older record works.

Request Method: GET Request URL: http://racedb.wimsey.online/RaceDB/LicenseHolders/LicenseHoldersManageDuplicates/LicenseHoldersMergeDuplicatesOK/6108/10621,6108/

Django Version: 5.1.1 Python Version: 3.10.15 Installed Applications: ('django.contrib.admin', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.messages', 'django.contrib.staticfiles', 'django_extensions', 'crispy_forms', 'crispy_bootstrap3', 'core') Installed Middleware: ['django.middleware.security.SecurityMiddleware', 'django.contrib.sessions.middleware.SessionMiddleware', 'django.middleware.common.CommonMiddleware', 'django.middleware.csrf.CsrfViewMiddleware', 'django.contrib.auth.middleware.AuthenticationMiddleware', 'django.contrib.messages.middleware.MessageMiddleware', 'django.middleware.clickjacking.XFrameOptionsMiddleware']

Traceback (most recent call last): File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params)

The above exception (duplicate key value violates unique constraint "core_licenseholder_existing_tag2_key" DETAIL: Key (existing_tag2)=(30187E7C3CA35DE138AF8E2A) already exists. ) was the direct cause of the following exception: File "/usr/local/lib/python3.10/site-packages/django/core/handlers/exception.py", line 55, in inner response = get_response(request) File "/usr/local/lib/python3.10/site-packages/django/core/handlers/base.py", line 197, in _get_response response = wrapped_callback(request, *callback_args, *callback_kwargs) File "/RaceDB/core/views.py", line 801, in LicenseHoldersMergeDuplicatesOK license_holder_merge_duplicates( license_holder_merge, duplicates ) File "/RaceDB/core/models.py", line 5794, in license_holder_merge_duplicates p.save() File "/RaceDB/core/models.py", line 3924, in save license_holder.save() File "/RaceDB/core/models.py", line 2547, in save super().save( args, *kwargs ) File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 891, in save self.save_base( File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 997, in save_base updated = self._save_table( File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 1129, in _save_table updated = self._do_update( File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 1194, in _do_update return filtered._update(values) > 0 File "/usr/local/lib/python3.10/site-packages/django/db/models/query.py", line 1278, in _update return query.get_compiler(self.db).execute_sql(CURSOR) File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 2003, in execute_sql cursor = super().execute_sql(result_type) File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1574, in execute_sql cursor.execute(sql, params) File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 122, in execute return super().execute(sql, params) File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 79, in execute return self._execute_with_wrappers( File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers return executor(sql, params, many, context) File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 100, in _execute with self.db.wrap_database_errors: File "/usr/local/lib/python3.10/site-packages/django/db/utils.py", line 91, in exit* raise dj_exc_value.with_traceback(traceback) from exc_value File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params)

Exception Type: IntegrityError at /RaceDB/LicenseHolders/LicenseHoldersManageDuplicates/LicenseHoldersMergeDuplicatesOK/6108/10621,6108/ Exception Value: duplicate key value violates unique constraint "core_licenseholder_existing_tag2_key" DETAIL: Key (existing_tag2)=(30187E7C3CA35DE138AF8E2A) already exists.

— Reply to this email directly, view it on GitHub https://github.com/esitarski/RaceDB/issues/86, or unsubscribe https://github.com/notifications/unsubscribe-auth/AABGXKK4G43IWTQ7T7P4LPDZ2RCJHAVCNFSM6AAAAABPTD3CQ6VHI2DSMVQWIX3LMV43ASLTON2WKOZSGU3TIMRRGA4TGMQ . You are receiving this because you are subscribed to this thread.Message ID: @.***>

--

Edward Sitarski

[stuartlynne]

We have some people showing up with tags from 7 or 8 years ago :-)

I'm trying to understand the constraint.

As far as I know what we have is a few dozen people that we updated with a tag2, and then were added to the event last Saturday.

That apparently worked well.

Let me look at the backup copy of the Database to see actual tag information for the records.

Ok, first person I found, two records, one with no tag, and one with valid tag2.

The version with tag2 was used in the event on Saturday. The merge to use that license holder record to merge to is what fails.

The error shows the tag2 data for that record. And searching for that tag does not find any license holder record:

duplicate key value violates unique constraint "core_licenseholder_existing_tag2_key"

DETAIL: Key (existing_tag2)=(30187E7C3CA35DE138AF8E2A) already exists.

On Tue, Oct 8, 2024 at 3:25 PM Edward Sitarski @.***> wrote:

There is a database constraint that says that all tag2 fields by competition and category must be unique. Otherwise, two riders could get the same tag. That would be BAD, as it would totally mess up the results.

Are you reusing tags rather than creating new unique ones?

Obviously, having an error message is not a good way of handling this, but the issue and the constraint is valid.

I have three suggestions:

1) Always use unique tags, don't reuse tags that other riders already have. Problem solved.

2) Automatically set any duplicate tags to None to avoid the "duplicate key unique constraint" problem. This assumes that the merge records are the "correct" ones, and that you are reusing tags and don't actually have any duplicates. If you have duplicates, then you will never know, and the results will potentially be messed up if any of those riders use the duplicate tags.

3) Reject merging tags that cause duplicate tag issues, and report that record that is causing the conflict. This could involve a lot of issue to chase down.

On Tue, Oct 8, 2024 at 5:05 PM Stuart Lynne @.***> wrote:

We ran an event last weekend that involved pulling in some license holder data with tag2 set so we could support using their existing tags.

Merging duplicate license holders today I'm getting this if I select the newer record. Merging to the older record works.

Request Method: GET Request URL:

http://racedb.wimsey.online/RaceDB/LicenseHolders/LicenseHoldersManageDuplicates/LicenseHoldersMergeDuplicatesOK/6108/10621,6108/

Django Version: 5.1.1 Python Version: 3.10.15 Installed Applications: ('django.contrib.admin', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.messages', 'django.contrib.staticfiles', 'django_extensions', 'crispy_forms', 'crispy_bootstrap3', 'core') Installed Middleware: ['django.middleware.security.SecurityMiddleware', 'django.contrib.sessions.middleware.SessionMiddleware', 'django.middleware.common.CommonMiddleware', 'django.middleware.csrf.CsrfViewMiddleware', 'django.contrib.auth.middleware.AuthenticationMiddleware', 'django.contrib.messages.middleware.MessageMiddleware', 'django.middleware.clickjacking.XFrameOptionsMiddleware']

Traceback (most recent call last): File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params)

The above exception (duplicate key value violates unique constraint "core_licenseholder_existing_tag2_key" DETAIL: Key (existing_tag2)=(30187E7C3CA35DE138AF8E2A) already exists. ) was the direct cause of the following exception: File

"/usr/local/lib/python3.10/site-packages/django/core/handlers/exception.py",

line 55, in inner response = get_response(request) File "/usr/local/lib/python3.10/site-packages/django/core/handlers/base.py", line 197, in _get_response response = wrapped_callback(request, *callback_args, *callback_kwargs) File "/RaceDB/core/views.py", line 801, in LicenseHoldersMergeDuplicatesOK license_holder_merge_duplicates( license_holder_merge, duplicates ) File "/RaceDB/core/models.py", line 5794, in license_holder_merge_duplicates p.save() File "/RaceDB/core/models.py", line 3924, in save license_holder.save() File "/RaceDB/core/models.py", line 2547, in save super().save( args, **kwargs ) File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 891, in save self.save_base( File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 997, in save_base updated = self._save_table( File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 1129, in _save_table updated = self._do_update( File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 1194, in _do_update return filtered._update(values) > 0 File "/usr/local/lib/python3.10/site-packages/django/db/models/query.py", line 1278, in _update return query.get_compiler(self.db).execute_sql(CURSOR) File

"/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 2003, in execute_sql cursor = super().execute_sql(result_type) File

"/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1574, in execute_sql cursor.execute(sql, params) File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 122, in execute return super().execute(sql, params) File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 79, in execute return self._execute_with_wrappers( File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers return executor(sql, params, many, context) File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 100, in _execute with self.db.wrap_database_errors: File "/usr/local/lib/python3.10/site-packages/django/db/utils.py", line 91, in exit raise dj_exc_value.with_traceback(traceback) from exc_value File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params)

Exception Type: IntegrityError at

/RaceDB/LicenseHolders/LicenseHoldersManageDuplicates/LicenseHoldersMergeDuplicatesOK/6108/10621,6108/

Exception Value: duplicate key value violates unique constraint "core_licenseholder_existing_tag2_key" DETAIL: Key (existing_tag2)=(30187E7C3CA35DE138AF8E2A) already exists.

— Reply to this email directly, view it on GitHub https://github.com/esitarski/RaceDB/issues/86, or unsubscribe < https://github.com/notifications/unsubscribe-auth/AABGXKK4G43IWTQ7T7P4LPDZ2RCJHAVCNFSM6AAAAABPTD3CQ6VHI2DSMVQWIX3LMV43ASLTON2WKOZSGU3TIMRRGA4TGMQ>

. You are receiving this because you are subscribed to this thread.Message ID: @.***>

--

Edward Sitarski

— Reply to this email directly, view it on GitHub https://github.com/esitarski/RaceDB/issues/86#issuecomment-2400925598, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACIUWOVR4THKZRIGOBXRA3Z2RLXFAVCNFSM6AAAAABPTD3CQ6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMBQHEZDKNJZHA . You are receiving this because you authored the thread.Message ID: @.***>

-- __O____ -\<,____ ____()/()___

---

@.***>__604-518-1749(m)604-461-7532(h)

[esitarski]

Here are RaceDB's database constraints on the Participant.

unique_together = ( ('competition', 'category', 'license_holder'), ('competition', 'category', 'bib'), ('competition', 'category', 'tag'), ('competition', 'category', 'tag2'), )

License_holders must be unique to the competition and category (no duplicate entries in the same race). Bib numbers must be unique for each competition and category (no duplicate bib numbers in the same race). Tag and Tag2 must be unique for each competition and category (no duplicate RFID tags in the same race).

Seems legit.

Agree that throwing an exception is not an acceptable response for duplicates in the merge (there is a bunch of code that handles this in the rest of the application).

This is more than a database issue. There needs to be some "tag management" to prevent old duplicate tags from ending up on two different riders in the same race, whether RaceDB knows about it or not.

Here is what I propose:

The safest thing for RaceDB to do when it encounters a duplicate tag from a Participant in the same Competition and Category is to NULL-out tag and tag2 for the riders involved. That prevents duplicate tags from ever getting to CrossMgr.

At race check-in, the rider's tag and tag2 would be empty. Self-serve check-in would not work, and the rider would be referred to reg. The check-in screen would then show missing tag/tag2 tags. The riders would get new, unique tags then (possibly turning in the old tags for re-programming).

Any other scheme that preserves the duplicate tags could cause problems if the riders don't remove the old tags. We don't want duplicate tags in a race ever.

Make sense?

On Tue, Oct 8, 2024 at 9:02 PM Stuart Lynne @.***> wrote:

We have some people showing up with tags from 7 or 8 years ago :-)

I'm trying to understand the constraint.

As far as I know what we have is a few dozen people that we updated with a tag2, and then were added to the event last Saturday.

That apparently worked well.

Let me look at the backup copy of the Database to see actual tag information for the records.

Ok, first person I found, two records, one with no tag, and one with valid tag2.

The version with tag2 was used in the event on Saturday. The merge to use that license holder record to merge to is what fails.

The error shows the tag2 data for that record. And searching for that tag does not find any license holder record:

duplicate key value violates unique constraint "core_licenseholder_existing_tag2_key"

DETAIL: Key (existing_tag2)=(30187E7C3CA35DE138AF8E2A) already exists.

On Tue, Oct 8, 2024 at 3:25 PM Edward Sitarski @.***> wrote:

There is a database constraint that says that all tag2 fields by competition and category must be unique. Otherwise, two riders could get the same tag. That would be BAD, as it would totally mess up the results.

Are you reusing tags rather than creating new unique ones?

Obviously, having an error message is not a good way of handling this, but the issue and the constraint is valid.

I have three suggestions:

1) Always use unique tags, don't reuse tags that other riders already have. Problem solved.

2) Automatically set any duplicate tags to None to avoid the "duplicate key unique constraint" problem. This assumes that the merge records are the "correct" ones, and that you are reusing tags and don't actually have any duplicates. If you have duplicates, then you will never know, and the results will potentially be messed up if any of those riders use the duplicate tags.

3) Reject merging tags that cause duplicate tag issues, and report that record that is causing the conflict. This could involve a lot of issue to chase down.

On Tue, Oct 8, 2024 at 5:05 PM Stuart Lynne @.***> wrote:

We ran an event last weekend that involved pulling in some license holder data with tag2 set so we could support using their existing tags.

Merging duplicate license holders today I'm getting this if I select the newer record. Merging to the older record works.

Request Method: GET Request URL:

http://racedb.wimsey.online/RaceDB/LicenseHolders/LicenseHoldersManageDuplicates/LicenseHoldersMergeDuplicatesOK/6108/10621,6108/

Django Version: 5.1.1 Python Version: 3.10.15 Installed Applications: ('django.contrib.admin', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.messages', 'django.contrib.staticfiles', 'django_extensions', 'crispy_forms', 'crispy_bootstrap3', 'core') Installed Middleware: ['django.middleware.security.SecurityMiddleware', 'django.contrib.sessions.middleware.SessionMiddleware', 'django.middleware.common.CommonMiddleware', 'django.middleware.csrf.CsrfViewMiddleware', 'django.contrib.auth.middleware.AuthenticationMiddleware', 'django.contrib.messages.middleware.MessageMiddleware', 'django.middleware.clickjacking.XFrameOptionsMiddleware']

Traceback (most recent call last): File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params)

The above exception (duplicate key value violates unique constraint "core_licenseholder_existing_tag2_key" DETAIL: Key (existing_tag2)=(30187E7C3CA35DE138AF8E2A) already exists. ) was the direct cause of the following exception: File

"/usr/local/lib/python3.10/site-packages/django/core/handlers/exception.py",

line 55, in inner response = get_response(request) File

"/usr/local/lib/python3.10/site-packages/django/core/handlers/base.py", line 197, in _get_response response = wrapped_callback(request, *callback_args, *callback_kwargs) File "/RaceDB/core/views.py", line 801, in LicenseHoldersMergeDuplicatesOK license_holder_merge_duplicates( license_holder_merge, duplicates ) File "/RaceDB/core/models.py", line 5794, in license_holder_merge_duplicates p.save() File "/RaceDB/core/models.py", line 3924, in save license_holder.save() File "/RaceDB/core/models.py", line 2547, in save super().save( args, **kwargs ) File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 891, in save self.save_base( File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 997, in save_base updated = self._save_table( File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 1129, in _save_table updated = self._do_update( File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 1194, in _do_update return filtered._update(values) > 0 File "/usr/local/lib/python3.10/site-packages/django/db/models/query.py", line 1278, in _update return query.get_compiler(self.db).execute_sql(CURSOR) File

"/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py",

line 2003, in execute_sql cursor = super().execute_sql(result_type) File

"/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py",

line 1574, in execute_sql cursor.execute(sql, params) File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 122, in execute return super().execute(sql, params) File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 79, in execute return self._execute_with_wrappers( File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers return executor(sql, params, many, context) File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 100, in _execute with self.db.wrap_database_errors: File "/usr/local/lib/python3.10/site-packages/django/db/utils.py", line 91, in exit raise dj_exc_value.with_traceback(traceback) from exc_value File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params)

Exception Type: IntegrityError at

/RaceDB/LicenseHolders/LicenseHoldersManageDuplicates/LicenseHoldersMergeDuplicatesOK/6108/10621,6108/

Exception Value: duplicate key value violates unique constraint "core_licenseholder_existing_tag2_key" DETAIL: Key (existing_tag2)=(30187E7C3CA35DE138AF8E2A) already exists.

— Reply to this email directly, view it on GitHub https://github.com/esitarski/RaceDB/issues/86, or unsubscribe <

https://github.com/notifications/unsubscribe-auth/AABGXKK4G43IWTQ7T7P4LPDZ2RCJHAVCNFSM6AAAAABPTD3CQ6VHI2DSMVQWIX3LMV43ASLTON2WKOZSGU3TIMRRGA4TGMQ>

. You are receiving this because you are subscribed to this thread.Message ID: @.***>

--

Edward Sitarski

— Reply to this email directly, view it on GitHub https://github.com/esitarski/RaceDB/issues/86#issuecomment-2400925598,

or unsubscribe < https://github.com/notifications/unsubscribe-auth/AACIUWOVR4THKZRIGOBXRA3Z2RLXFAVCNFSM6AAAAABPTD3CQ6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMBQHEZDKNJZHA>

. You are receiving this because you authored the thread.Message ID: @.***>

-- __O____ -\<,____ ____()/()___

---

@.***>__604-518-1749(m)604-461-7532(h)

— Reply to this email directly, view it on GitHub https://github.com/esitarski/RaceDB/issues/86#issuecomment-2401070224, or unsubscribe https://github.com/notifications/unsubscribe-auth/AABGXKJW4GZWKIPZJEGMLH3Z2R6CJAVCNFSM6AAAAABPTD3CQ6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMBRGA3TAMRSGQ . You are receiving this because you commented.Message ID: @.***>

--

Edward Sitarski

[stuartlynne]

Yes, I agree on principle.

What happens now when we merge to licenceholder records that have different tags (presumably if they both have tags they will be different.)

Specifically, does RaceDB remember the (now discarded) tag for the races that used the tag?

If the CrossMgr file was downloaded for that event would it have the new or old tag?

In rare occasions we do have to do that, for example to update a license or team name. And then regenerate the results.

If there is a different tag will CrossMgr now forget the reads from the previous tag (that was actually used in the race)?

I know that updating the CrossMgr file mid-race with the correct tag (original CrossMgr file had the rider with no tag) will find the reads.

Would it be worthwhile to change from having tag and tag2 as part of licenseholder record, and have a separate table for tags, pointing to the licenseholder record.

The tag(s) exported as part of CrossMgr file would be the ones scanned at registration or mostly recently written/used if nothing was scanned.

On Wed, Oct 9, 2024 at 7:29 AM Edward Sitarski @.***> wrote:

Here are RaceDB's database constraints on the Participant.

unique_together = ( ('competition', 'category', 'license_holder'), ('competition', 'category', 'bib'), ('competition', 'category', 'tag'), ('competition', 'category', 'tag2'), )

License_holders must be unique to the competition and category (no duplicate entries in the same race). Bib numbers must be unique for each competition and category (no duplicate bib numbers in the same race). Tag and Tag2 must be unique for each competition and category (no duplicate RFID tags in the same race).

Seems legit.

Agree that throwing an exception is not an acceptable response for duplicates in the merge (there is a bunch of code that handles this in the rest of the application).

This is more than a database issue. There needs to be some "tag management" to prevent old duplicate tags from ending up on two different riders in the same race, whether RaceDB knows about it or not.

Here is what I propose:

The safest thing for RaceDB to do when it encounters a duplicate tag from a Participant in the same Competition and Category is to NULL-out tag and tag2 for the riders involved. That prevents duplicate tags from ever getting to CrossMgr.

At race check-in, the rider's tag and tag2 would be empty. Self-serve check-in would not work, and the rider would be referred to reg. The check-in screen would then show missing tag/tag2 tags. The riders would get new, unique tags then (possibly turning in the old tags for re-programming).

Any other scheme that preserves the duplicate tags could cause problems if the riders don't remove the old tags. We don't want duplicate tags in a race ever.

Make sense?

On Tue, Oct 8, 2024 at 9:02 PM Stuart Lynne @.***> wrote:

We have some people showing up with tags from 7 or 8 years ago :-)

I'm trying to understand the constraint.

As far as I know what we have is a few dozen people that we updated with a tag2, and then were added to the event last Saturday.

That apparently worked well.

Let me look at the backup copy of the Database to see actual tag information for the records.

Ok, first person I found, two records, one with no tag, and one with valid tag2.

The version with tag2 was used in the event on Saturday. The merge to use that license holder record to merge to is what fails.

The error shows the tag2 data for that record. And searching for that tag does not find any license holder record:

duplicate key value violates unique constraint "core_licenseholder_existing_tag2_key"

DETAIL: Key (existing_tag2)=(30187E7C3CA35DE138AF8E2A) already exists.

On Tue, Oct 8, 2024 at 3:25 PM Edward Sitarski @.***> wrote:

There is a database constraint that says that all tag2 fields by competition and category must be unique. Otherwise, two riders could get the same tag. That would be BAD, as it would totally mess up the results.

Are you reusing tags rather than creating new unique ones?

Obviously, having an error message is not a good way of handling this, but the issue and the constraint is valid.

I have three suggestions:

1) Always use unique tags, don't reuse tags that other riders already have. Problem solved.

2) Automatically set any duplicate tags to None to avoid the "duplicate key unique constraint" problem. This assumes that the merge records are the "correct" ones, and that you are reusing tags and don't actually have any duplicates. If you have duplicates, then you will never know, and the results will potentially be messed up if any of those riders use the duplicate tags.

3) Reject merging tags that cause duplicate tag issues, and report that record that is causing the conflict. This could involve a lot of issue to chase down.

On Tue, Oct 8, 2024 at 5:05 PM Stuart Lynne @.***> wrote:

We ran an event last weekend that involved pulling in some license holder data with tag2 set so we could support using their existing tags.

Merging duplicate license holders today I'm getting this if I select the newer record. Merging to the older record works.

Request Method: GET Request URL:

http://racedb.wimsey.online/RaceDB/LicenseHolders/LicenseHoldersManageDuplicates/LicenseHoldersMergeDuplicatesOK/6108/10621,6108/

Django Version: 5.1.1 Python Version: 3.10.15 Installed Applications: ('django.contrib.admin', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.messages', 'django.contrib.staticfiles', 'django_extensions', 'crispy_forms', 'crispy_bootstrap3', 'core') Installed Middleware: ['django.middleware.security.SecurityMiddleware', 'django.contrib.sessions.middleware.SessionMiddleware', 'django.middleware.common.CommonMiddleware', 'django.middleware.csrf.CsrfViewMiddleware', 'django.contrib.auth.middleware.AuthenticationMiddleware', 'django.contrib.messages.middleware.MessageMiddleware', 'django.middleware.clickjacking.XFrameOptionsMiddleware']

Traceback (most recent call last): File

"/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params)

The above exception (duplicate key value violates unique constraint "core_licenseholder_existing_tag2_key" DETAIL: Key (existing_tag2)=(30187E7C3CA35DE138AF8E2A) already exists. ) was the direct cause of the following exception: File

"/usr/local/lib/python3.10/site-packages/django/core/handlers/exception.py",

line 55, in inner response = get_response(request) File

"/usr/local/lib/python3.10/site-packages/django/core/handlers/base.py", line 197, in _get_response response = wrapped_callback(request, *callback_args, *callback_kwargs) File "/RaceDB/core/views.py", line 801, in LicenseHoldersMergeDuplicatesOK license_holder_merge_duplicates( license_holder_merge, duplicates ) File "/RaceDB/core/models.py", line 5794, in license_holder_merge_duplicates p.save() File "/RaceDB/core/models.py", line 3924, in save license_holder.save() File "/RaceDB/core/models.py", line 2547, in save super().save( args, **kwargs ) File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 891, in save self.save_base( File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 997, in save_base updated = self._save_table( File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 1129, in _save_table updated = self._do_update( File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 1194, in _do_update return filtered._update(values) > 0 File "/usr/local/lib/python3.10/site-packages/django/db/models/query.py", line 1278, in _update return query.get_compiler(self.db).execute_sql(CURSOR) File

"/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py",

line 2003, in execute_sql cursor = super().execute_sql(result_type) File

"/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py",

line 1574, in execute_sql cursor.execute(sql, params) File

"/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 122, in execute return super().execute(sql, params) File

"/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 79, in execute return self._execute_with_wrappers( File

"/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers return executor(sql, params, many, context) File

"/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 100, in _execute with self.db.wrap_database_errors: File "/usr/local/lib/python3.10/site-packages/django/db/utils.py", line 91, in exit raise dj_exc_value.with_traceback(traceback) from exc_value File

"/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params)

Exception Type: IntegrityError at

/RaceDB/LicenseHolders/LicenseHoldersManageDuplicates/LicenseHoldersMergeDuplicatesOK/6108/10621,6108/

Exception Value: duplicate key value violates unique constraint "core_licenseholder_existing_tag2_key" DETAIL: Key (existing_tag2)=(30187E7C3CA35DE138AF8E2A) already exists.

— Reply to this email directly, view it on GitHub https://github.com/esitarski/RaceDB/issues/86, or unsubscribe <

https://github.com/notifications/unsubscribe-auth/AABGXKK4G43IWTQ7T7P4LPDZ2RCJHAVCNFSM6AAAAABPTD3CQ6VHI2DSMVQWIX3LMV43ASLTON2WKOZSGU3TIMRRGA4TGMQ>

. You are receiving this because you are subscribed to this thread.Message ID: @.***>

--

Edward Sitarski

— Reply to this email directly, view it on GitHub https://github.com/esitarski/RaceDB/issues/86#issuecomment-2400925598,

or unsubscribe <

https://github.com/notifications/unsubscribe-auth/AACIUWOVR4THKZRIGOBXRA3Z2RLXFAVCNFSM6AAAAABPTD3CQ6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMBQHEZDKNJZHA>

. You are receiving this because you authored the thread.Message ID: @.***>

-- __O____ -\<,____ ____()/()___

---

@.***>__604-518-1749(m)604-461-7532(h)

— Reply to this email directly, view it on GitHub https://github.com/esitarski/RaceDB/issues/86#issuecomment-2401070224,

or unsubscribe < https://github.com/notifications/unsubscribe-auth/AABGXKJW4GZWKIPZJEGMLH3Z2R6CJAVCNFSM6AAAAABPTD3CQ6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMBRGA3TAMRSGQ>

. You are receiving this because you commented.Message ID: @.***>

--

Edward Sitarski

— Reply to this email directly, view it on GitHub https://github.com/esitarski/RaceDB/issues/86#issuecomment-2402509671, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACIUWOIHG5OCS5T2F5XIYTZ2U4WFAVCNFSM6AAAAABPTD3CQ6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMBSGUYDSNRXGE . You are receiving this because you authored the thread.Message ID: @.***>

-- __O____ -\<,____ ____()/()___

---

@.***>__604-518-1749(m)604-461-7532(h)