在 Django 中迁移时，我收到错误完整性错误 1364，"Field 'id' doesn't have a default value"

Question

这是在保存和恢复MariaDB数据库的几次迭代之后出现的东西。无论更改多么简单，或者我正在更改什么，它都会执行迁移，然后抛出此错误。

File "/home/.virtualenvs/venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 85, in _execute
return self.cursor.execute(sql, params)
File "/home/.virtualenvs/venv/lib/python3.7/site-packages/django/db/backends/mysql/base.py", line 71, in execute
return self.cursor.execute(query, args)
File "/home/.virtualenvs/venv/lib/python3.7/site-packages/MySQLdb/cursors.py", line 250, in execute
self.errorhandler(self, exc, value)
File "/home/.virtualenvs/venv/lib/python3.7/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
raise errorvalue
File "/home/.virtualenvs/venv/lib/python3.7/site-packages/MySQLdb/cursors.py", line 247, in execute
res = self._query(query)
File "/home/.virtualenvs/venv/lib/python3.7/site-packages/MySQLdb/cursors.py", line 412, in _query
rowcount = self._do_query(q)
File "/home/.virtualenvs/venv/lib/python3.7/site-packages/MySQLdb/cursors.py", line 375, in _do_query
db.query(q)
File "/home/.virtualenvs/venv/lib/python3.7/site-packages/MySQLdb/connections.py", line 276, in query
_mysql.connection.query(self, query)
_mysql_exceptions.IntegrityError: (1364, "Field 'id' doesn't have a default value")

这是我可以通过忽略它来解决的问题，但它非常烦人和分散注意力。我该如何解决？

Answer 1

这多年来一直困扰着我。在保存和恢复的某个地方，大多数 django 管理表的 id 字段失去了它们的主键和自动增量属性，并开始用零填充。我使用以下 Python 3.6 脚本更正了这一点：

import MySQLdb
dbspec = {
'HOST': 'localhost',
'USER': 'user',
'PASSWORD': 'badpassword',
'NAME': 'databasename'
}
def reserialize1(cnx, tablename, truekey, makekey='id'):
sql = f"select max({makekey}) from {tablename}"
cur = cnx.cursor()
cur.execute(sql)
response = cur.fetchall()
maxid = response[0][0]
counter = maxid + 1
sql = f"select {makekey}, {truekey} from {tablename}"
cur = cnx.cursor()
cur.execute(sql)
response = cur.fetchall()
cur = cnx.cursor()
for itr in response:
if itr[0] == 0:
sql = f"update {tablename} set {makekey}={counter} where {truekey}='{itr[1]}'"
print(f"running {sql}")
cur.execute(sql)
counter += 1
cnx.commit()

def reserialize2(cnx, tablename, key1, key2, makekey='id'):
sql = f"select max({makekey}) from {tablename}"
cur = cnx.cursor()
cur.execute(sql)
response = cur.fetchall()
maxid = response[0][0]
counter = maxid + 1
sql = f"select {makekey}, {key1}, {key2} from {tablename}"
cur = cnx.cursor()
cur.execute(sql)
response = cur.fetchall()
cur = cnx.cursor()
for itr in response:
if itr[0] == 0:
sql = f"update {tablename} set {makekey}={counter} " 
f"where {key1}='{itr[1]}' and {key2}='{itr[2]}'"
print(f"running {sql}")
cur.execute(sql)
counter += 1
cnx.commit()

if __name__ == '__main__':
cnx = MySQLdb.connect(host=dbspec['HOST'],
user=dbspec['USER'],
passwd=dbspec['PASSWORD'],
db=dbspec['NAME'])
reserialize1(cnx, 'auth_permission', 'codename')
reserialize2(cnx, 'auth_user_groups', 'user_id', 'group_id')
reserialize1(cnx, 'django_admin_log', 'action_time')
reserialize2(cnx, 'django_content_type', 'app_label', 'model')
reserialize1(cnx, 'django_migrations', 'applied')
cur = cnx.cursor()
cur.execute("ALTER TABLE auth_group MODIFY COLUMN id INT primary key auto_increment;")
cur.execute("ALTER TABLE auth_group_permissions MODIFY COLUMN id INT primary key auto_increment;")
cur.execute("ALTER TABLE auth_permission MODIFY COLUMN id INT primary key auto_increment;")
cur.execute("ALTER TABLE auth_user_groups MODIFY COLUMN id INT primary key auto_increment;")
cur.execute("ALTER TABLE auth_user_user_permissions MODIFY COLUMN id INT primary key auto_increment;")
cur.execute("ALTER TABLE django_admin_log MODIFY COLUMN id INT primary key auto_increment;")
cur.execute("ALTER TABLE django_content_type MODIFY COLUMN id INT primary key auto_increment;")
cur.execute("ALTER TABLE django_migrations MODIFY COLUMN id INT primary key auto_increment;")
cnx.commit()