使用sequelize的时候，mysql自增长Id总是 Duplicate entry '0' for key 'PRIMARY错误

问答

Yyahue发布于 8 年前最后回复 8 年前

1168550

生成的语句是：

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的什么属性吗？有哪位大神遇到过这个问题？

查看回复

yahue#118 年前

引用 jinceon原因是数据库设置了 sqlmode 为 NO AUTO VALUE ON ZERO

@jinceon 的确是这样的当时找了好久没有这方面资料，多谢了

jinceon#108 年前

原因是数据库设置了 sqlmode 为 NO_AUTO_VALUE_ON_ZERO

yahue#98 年前

原来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/

就好了，但是我还是不知道为什么就好了--！

yahue#88 年前

好吧是数据库的问题

yahue#78 年前

引用 burning0xb主要还是要看表的主键是不是自增的

@burning0xb TIM截图20180329100630.png 这样设置是自增长的吧？