Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[db_sync] 存在一条记录code冲突会导致所有记录单独更新也失败 #926

Open
wklken opened this issue Feb 21, 2023 · 0 comments
Open

[db_sync] 存在一条记录code冲突会导致所有记录单独更新也失败 #926

wklken opened this issue Feb 21, 2023 · 0 comments
Assignees
@nannan00
Labels
Layer: api Api module related Module: LDAP/MAD ldap/mad related Priority: Middlum Middlum priority Type: bug Something isn't working

Comments

@wklken
Copy link
Collaborator

wklken commented Feb 21, 2023 •
edited
Loading

  1. 有一条记录冲突, 导致批量插入失败
  2. 所有记录save one by one也失败
  3. 打印的异常日志也有问题

代码

https://github.com/wklken/bk-user/blob/02aafeeba0e96fb13b48d12c069cb2c8645e1b5d/src/api/bkuser_core/common/db_sync.py#L218-L255

日志

INFO [2023-02-20 14:45:41] 265 disable_profiles_before_sync 29280 140551249217344 
Going to mark profiles(and relations)(all: 7955) in category<2> as deleted, skipping 0 profiles 

INFO [2023-02-20 14:45:41] 212 _sync 29280 140551249217344 
======== Going to bulk_create(count: 236) for Profile ========= 

INFO [2023-02-20 14:45:41] 225 _sync 29280 140551249217344 
======== Syncing part of Profile(1/1) current: 0 + 236 ========= 

WARNING [2023-02-20 14:45:42] 239 _sync 29280 140551249217344 
Profile bulk_create failed, count=236, extra_params={}, will try to sync one by one 

ERROR [2023-02-20 14:45:42] 252 _sync 29280 140551249217344 
Profile bulk_create: save one by one fail, item=71470-abcdefg-NORMAL, will not be updated, detail={} 
Traceback (most recent call last):
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.IntegrityError: (1062, "Duplicate entry 'f94236485e704bef717341c34d6c81c0d20cc7ad12c916b8cd9960c712db142e' for key 'code'")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/data/bkce/usermgr/api/bkuser_core/common/db_sync.py", line 232, in _sync
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/query.py", line 503, in bulk_create
    objs_with_pk, fields, batch_size, ignore_conflicts=ignore_conflicts,
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/query.py", line 1293, in _batched_insert
    self._insert(item, fields=fields, using=self.db, ignore_conflicts=ignore_conflicts)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/query.py", line 1270, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1416, in execute_sql
    cursor.execute(sql, params)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
django.db.utils.IntegrityError: (1062, "Duplicate entry 'f94236485e704bef717341c34d6c81c0d20cc7ad12c916b8cd9960c712db142e' for key 'code'")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/data/bkce/usermgr/api/bkuser_core/common/db_sync.py", line 243, in _sync
  File "/data/bkce/usermgr/api/bkuser_core/profiles/models.py", line 166, in save
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/base.py", line 740, in save
    force_update=force_update, update_fields=update_fields)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/base.py", line 778, in save_base
    force_update, using, update_fields,
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/base.py", line 859, in _save_table
    forced_update)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/base.py", line 912, in _do_update
    return filtered._update(values) > 0
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/query.py", line 802, in _update
    return query.get_compiler(self.db).execute_sql(CURSOR)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1559, in execute_sql
    cursor = super().execute_sql(result_type)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1175, in execute_sql
    cursor.execute(sql, params)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/utils.py", line 78, in _execute
    self.db.validate_no_broken_transaction()
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/base/base.py", line 448, in validate_no_broken_transaction
    "An error occurred in the current transaction. You can't "
django.db.transaction.TransactionManagementError: An error occurred in the current transaction. You can't execute queries until the end of the 'atomic' block.
ERROR [2023-02-20 14:45:42] 252 _sync 29280 140551249217344 
Profile bulk_create: save one by one fail, item=71471-aabbcc-NORMAL, will not be updated, detail={} 
Traceback (most recent call last):
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.IntegrityError: (1062, "Duplicate entry 'f94236485e704bef717341c34d6c81c0d20cc7ad12c916b8cd9960c712db142e' for key 'code'")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/data/bkce/usermgr/api/bkuser_core/common/db_sync.py", line 232, in _sync
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/query.py", line 503, in bulk_create
    objs_with_pk, fields, batch_size, ignore_conflicts=ignore_conflicts,
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/query.py", line 1293, in _batched_insert
    self._insert(item, fields=fields, using=self.db, ignore_conflicts=ignore_conflicts)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/query.py", line 1270, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1416, in execute_sql
    cursor.execute(sql, params)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
django.db.utils.IntegrityError: (1062, "Duplicate entry 'f94236485e704bef717341c34d6c81c0d20cc7ad12c916b8cd9960c712db142e' for key 'code'")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/data/bkce/usermgr/api/bkuser_core/common/db_sync.py", line 243, in _sync
  File "/data/bkce/usermgr/api/bkuser_core/profiles/models.py", line 166, in save
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/base.py", line 740, in save
    force_update=force_update, update_fields=update_fields)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/base.py", line 778, in save_base
    force_update, using, update_fields,
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/base.py", line 859, in _save_table
    forced_update)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/base.py", line 912, in _do_update
    return filtered._update(values) > 0
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/query.py", line 802, in _update
    return query.get_compiler(self.db).execute_sql(CURSOR)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1559, in execute_sql
    cursor = super().execute_sql(result_type)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1175, in execute_sql
    cursor.execute(sql, params)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/utils.py", line 78, in _execute
    self.db.validate_no_broken_transaction()
  File "/data/bkce/.envs/usermgr-api/lib/python3.6/site-packages/django/db/backends/base/base.py", line 448, in validate_no_broken_transaction
    "An error occurred in the current transaction. You can't "
django.db.transaction.TransactionManagementError: An error occurred in the current transaction. You can't execute queries until the end of the 'atomic' block.
@wklken wklken added Type: bug Something isn't working Priority: Middlum Middlum priority Layer: api Api module related Module: LDAP/MAD ldap/mad related todo 进入开发排期的状态，纳入了最近的迭代 labels Feb 21, 2023
@neronkl neronkl mentioned this issue May 15, 2023
Closed
@nannan00 nannan00 removed the todo 进入开发排期的状态，纳入了最近的迭代 label Jul 21, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@nannan00 nannan00

Labels
Layer: api Api module related Module: LDAP/MAD ldap/mad related Priority: Middlum Middlum priority Type: bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@wklken @nannan00