使用sequelize的时候,mysql自增长Id总是 Duplicate entry '0' for key 'PRIMARY错误
生成的语句是:
INSERT INTO `apps` (`id`,`name`,`uid`,`created_at`,`updated_at`) VALUES (DEFAULT,\'yahue\',1,\'2018-03-28 12:22:20\',\'2018-03-28 12:22:20\');
sequelize自动增长id为 DEFAULT
还是会有 Duplicate entry ‘0’ for key 'PRIMARY 错误
我是要设置 mysql的什么属性吗?
有哪位大神遇到过这个问题?
11 回复
default是什么值?自增长的话不需要对id设值啊
来自酷炫的 CNodeMD
@zhangmingfeng 但是 sequelize 默认会把自增长的主键设置 default的呀
apps表的id是auto_increment的吗?
define里面也要把autoIncrement设置为true
主要还是要看表的主键是不是自增的
@sjnho 这样的
@burning0xb 这样设置是自增长的吧?
好吧 是数据库的问题
原来mysql的配置 my.cnf是
# CLIENT #
[mysql]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld]
# GENERAL #
# user = mysql
server-id = 1
default-storage-engine = InnoDB
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysql.pid
# MyISAM #
key-buffer-size = 32M
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sysdate-is-now = 1
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
explicit_defaults_for_timestamp=true
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 128
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 64M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 160M
# LOGGING #
#log-error = /var/lib/mysql/mysql-error.log
#log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
改成了
# Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Community Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
default-character-set = utf8mb4
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
skip-name-resolve
back_log = 500
max_connections = 2000
max_connect_errors = 9999999
table_open_cache = 512
max_allowed_packet = 16M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 64
innodb_thread_concurrency = 4
query_cache_size =128M
query_cache_limit = 2M
default-storage-engine = InnoDB
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake
init_connect='SET NAMES utf8mb4'
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
table_open_cache = 2048
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp
# *** log config***
slow_query_log
long_query_time = 2
# log-slow-queries = /var/log/mysql/mysql-slow.log
slow-query-log-file = /var/log/mysql/mysql-slow.log # 5.6+
log-queries-not-using_indexes
# *** myisam Specific options ***
key_buffer_size = 256M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover_options
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
[mysqldump]
quick
max_allowed_packet = 16M
log-error = /var/log/mysql/error.log
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysql]
no-auto-rehash
default-character-set = utf8mb4
# Only allow UPDATEs and DELETEs that use keys.
#safe-updates
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links =0
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
open-files-limit = 60000
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
就好了,但是我还是不知道为什么就好了–!
原因是 数据库设置了 sqlmode 为 NO_AUTO_VALUE_ON_ZERO
@jinceon 的确是这样的 当时找了好久没有这方面资料,多谢了