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 <#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 > <#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 <#86 (comment)>, 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 > > <#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 > <#86 (comment)>, > 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 <#86 (comment)>, 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 > > > <#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 > > <#86 (comment)>, > > > 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 > <#86 (comment)>, > 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 <#86 (comment)>, 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)