mysql bug 之 “on duplicate key update”

最新推荐文章于 2024-11-18 10:07:56 发布
最新推荐文章于 2024-11-18 10:07:56 发布 · 668 阅读 · 0
文章标签：

#mysql #Parameter index out of range

本文详细分析了一个SQL语句问题导致的异常情况，主要聚焦于'mysqljdbcjar'版本过低引发的SQL解析错误。通过深入探讨，最终定位到错误在于SQL语句中'onduplicate keyupdate'关键字的不当使用，特别是其中的换行符导致的解析失败。文章提供了正确的语法修正建议，并强调了更新JDBC版本以避免类似问题的重要性。

首先发现一个异常，徘徊好久不能解决！error log如下：

 

Caused by: java.sql.SQLException: Parameter index out of range (35 > number of parameters, which is 34).
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
	at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3729)
	at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3713)
	at com.mysql.jdbc.PreparedStatement.setBytesNoEscapeNoQuotes(PreparedStatement.java:3463)
	at com.mysql.jdbc.PreparedStatement.setOneBatchedParameterSet(PreparedStatement.java:4264)
	at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1766)
	at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1440)
	at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
	at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
	at com.ibatis.sqlmap.engine.execution.SqlExecutor$Batch.executeBatch(SqlExecutor.java:611)
	at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeBatch(SqlExecutor.java:118)
	at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.executeBatch(SqlMapExecutorDelegate.java:759)
	at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.executeBatch(SqlMapSessionImpl.java:184)

 

mysql jdbc jar 版本：5.1.18

 

 

初步定位：sql语句问题！

 

<insert id="insert" parameterClass="***">
	insert into ****** ( 
	gmt_created,
	nick
	)
	values (
	#gmtCreated#,
	#nick#
	) on duplicate
	 key update 
		gmt_modified=#gmtModified#,
		nick=#nick#
</insert>
 

 

最终定位：

 

 on duplicate
	 key update 

应该为：

on duplicate key update

 

问题原因：

    “ on duplicate key update ” 中如果有换行，sql解析就会失败！

 

注意：

mysql jdbc jar 版本5.1.8以下都存在一定程度的sql解析问题。最好能升级到最新版本！

如果sql解析异常，大多异常信息为：

Caused by: java.sql.SQLException: Parameter index out of range

 

 

 

收藏夹吃灰系列(十一):插入更新sql语句 ON DUPLICATE KEY UPDATE 使用详解 | 超级详细，建议收藏！
**My Coding Family**
09-25 956
一：使用背景 在项目中，你可能遇到这么一种场景，由于业务需求，需要先根据某一字段值查询数据库中是否有记录，有则更新，没有则插入。 对于这种需求，我想先看看大家是怎么玩的，在想听到大家怎么实现该需求之前，我先给介绍一下，我前同事的实现逻辑，后续就是该代码被弃用，进而被优化。 基本就是如下进行逻辑并合先进行查询再进行新增或更新操作。 if not exists (select task_name from task where task_name =...
duplicate mysql_Mysql死锁如何排查：insert on duplicate死锁一次排查分析过程
weixin_36477752的博客
01-18 529
前言遇到Mysql死锁问题，我们应该怎么排查分析呢？之前线上出现一个insert on duplicate死锁问题，本文将基于这个死锁问题，分享排查分析过程，希望对大家有帮助。死锁案发还原表结构：CREATE TABLE `song_rank` (`id` int(11) NOT NULL AUTO_INCREMENT,`songId` int(11) NOT NULL,`weight` int(...
记录mysql ON DUPLICATE KEY UPDATE 时的小坑以及其底层处理逻辑
liaguaya的博客
09-08 1098
ON DUPLICATE KEY UPDATE
关于on duplicate key update导致主键不连续自增的问题
m0_59849460的博客
04-06 4832
最近项目上需要实现这么一个功能:从服务器数据库获取一个json字符串，通过解析获得一个10万量的数据，存入本本地数据库，最开始采用先更新后新增的逻辑进行操作，通过测试逐条更新执行完，整个代码是在10分钟左右，效率低下，所以采用了如下代码 <insert id="insertPackWmsOperator" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id"> insert into pack_
[MySQL]使用on duplicate key update引起主键不连续自增
新一的技术笔记
01-22 1258
因为在定时任务里面，把mysql的一个表当做缓存来用； 所以就需要update + insert的操作； 如果不需要主键还好，但是因为公司要求最好是有主键的（自增id） 当然用自增id也是有很多好处的，这边就不做过多赘述 现象呢： 当然，这边有个bug，导致我某些记录频繁的进行更新； 所以就成这样子了； 所以解决方式是 先更新，看看影响的行数，如果大于等于1，那么更新成功，否则就进行插入操作； 之前写法： <insert id="upsert" parameterTyp..
数据库｜一文教你解决on duplicate key update引发的索引数据不一致问题
在数字化道路无限探索
01-24 2200
使用insert into ... on duplicate key update操作时遇到的小问题
MYSQL的REPLACE和INSERT ... ON DUPLICATE KEY UPDATE
ms_X0828的专栏
05-29 1071
在对看看的后台进行排序的时候,遇到了一个像这样的需求,在电影表中有ID(主键自增)和orderby(排序字段) ,假设有十条数据id分别从1-10之间,对应的orderby也是从1-10之间,我现在想把id=9的数据移动到第三的位置(id=3)的这个位置,并且保证之前的数据排列顺序(即id=3的orderby=4,id=4的orderby=5…id=8的orderby=9),这样如果用循环的形
Caused by: java.sql.SQLException: sql injection violation, dbType mysql, , druid-version 1.2.8, syntax error: syntax error, expect ')', pos 256, line 8, column 10, token ON : INSERT INTO mcr_crs_task ( task_id, mcr_instance_id, crs_rule_id, crs_rule_name, task_type, task_start_time, task_end_time, use_time, harbor_task_id, sync_status, create_time, created_by ) VALUES ON DUPLICATE KEY UPDATE task_id = VALUES(task_id), mcr_instance_id = VALUES(mcr_instance_id), crs_rule_id = VALUES(crs_rule_id), crs_rule_name = VALUES(crs_rule_name), task_type = VALUES(task_type), task_start_time = VALUES(task_start_time), task_end_time = VALUES(task_end_time), use_time = VALUES(use_time), harbor_task_id = VALUES(harbor_task_id), sync_status = VALUES(sync_status), update_time = VALUES(update_time), updated_by = VALUES(updated_by), delete_time = VALUES(delete_time), deleted_by = VALUES(deleted_by), deleted = VALUES(deleted) 哪里有异常
最新发布
10-25
- 引用[1]: 讨论了MySQL的ON DUPLICATE KEY UPDATE影响行数异常排查，包括Connector/J版本差异。 - 引用[2]: 展示了使用INSERT ... ON DUPLICATE KEY UPDATE的示例代码。 用户上次的问题是关于这个错误信息：...
深入分析Mybatis 使用useGeneratedKeys获取自增主键
方丈的寺院
09-07 2655
摘要 我们经常使用useGenerateKeys来返回自增主键，避免多一次查询。也会经常使用on duplicate key update，来进行insertOrUpdate，来避免先query 在insert/update。用起来很爽，但是经常踩坑，还不知为何。本篇就是深入分析获取自增主键的原理。 问题 首先摘两段我司一些老代码的bug 批量插入用户收藏 for (tries = 0; trie...
笔记（上）：mysql-DuplicateUpdate和java的threadpool的“死锁“
m0_57781768的博客
12-03 481
今天给大家讲讲最近2个有意思的issue，分享一下我学到的mysql DuplicateUpdate的用法要注意的点有个issue说遇到了一个这样的问题，这个朋友使用我开源的job调度框架 https://github.com/yuzd/Hangfire.HttpJob存储用的是mysql，采用的实现是 https://github.com/arnoldasgudas/Hangfire.MySqlStorageset表的id是自增主键，正常理解 都是慢慢自增上去的，但是发现是大幅度跳跃式的自增， 真相是什么
on duplicate key update的坑
hehehehao1的博客
11-18 500
ON DUPLICATE KEY UPDATE坑
MySQL唯一值重复报错DuplicateKeyException最好解决方法ON DUPLICATE KEY UPDATE
weixin_46114677的博客
09-11 4886
公司业务,表设置了唯一值,一般业务代码里先查一遍数据库,存在的话就直接返回货修改,不存在才插入,测试没什么问题,但是上到线上高并发下疯狂报DuplicateKeyException…查阅两个多小时才发现MySQL还有ON DUPLICATE KEY UPDATE这样的用法 场景： 比如，有一张表，专门记录业务里的唯一数据记录，这张表里如果存在此唯一数据的记录就更新此行数据的某个字段，如果此唯一数据不存在，那么就添加一条最新数据。 一贯操作：如果不知道mysql有 ON DUPLICATE KEY UPDAT
ON DUPLICATE KEY UPDATE
qq_39056877的博客
07-20 477
ON DUPLICATE KEY UPDATE ON DUPLICATE KEY UPDATE 语法的目的是为了解决重复性，当数据库中存在某个记录时，执行这条语句会更新它，而不存在这条记录时，会插入它。 注意点1：我们使用该语句的时候注意这条语句判断该条记录是否存在的标准是什么？由于同一个值是可以同时出现在多个记录中的，所以必须有个字段是唯一不能重复的。 注意点2：规则如果你插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复，那么就会认为该条记录存在，则执行update语句而不是i
Mysql ON DUPLICATE KEY Update
qq_28313291的博客
03-26 339
深入理解和优雅使用ON DUPLICATE KEY Update 解决存在则更新，不存在则新增的问题 先看语法INSERT INTO table(id,a,……) values(1,‘xh’，……) ON DUPLICATE KEY Update a=‘value’，……; 举个例子 1，先创建表结构如下 DROP TABLE IF EXISTS `t_ware_duplicate`; CREA...
on duplicate key update 处理
滇沛流漓的博客
05-09 377
mysql 批量数据存在即更新 不存在新增 insert into `user` ( id, name, gender, birthday, address ) values( 1, '张三', 1, '2021-02-14', '黑龙江' ) on duplicate key update name =values(name), gender =values(gender), birthday =values(birthday), address =values(address) ...
MySql中 on duplicate key update的使用
【折翼天使不孤单】的博客
12-20 1073
开发中，经常会遇到向数据库中插入数据，如果数据库中执行存在这条记录就更update操作，如果不存在就执行insert操作。当操作一条数据的时候，实现比较简单，比较笨的方法（小编之前自己就是用这种方法）就是先根据主键或者唯一键进行查询，如果查到数据库总有记录，就执行update，如果没有查到记录，就执行insert。Mysql有一个自己特有的关键词-on duplicate key update，很...
INSERT ... ON DUPLICATE KEY UPDATE产生death lock 原因
彩色天的博客
08-03 1114
线上收到了一个报警，主要内容如下： 异常：Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:， 原因：最终锁定是和一个插入或者更新的语句有关，INSERT ... ON DUPLICATE KEY UPDATE 结果：这个语句可能导致death ...
on duplicate key update简单使用
@龙猫的博客
07-26 6558
在实际应用中，经常碰到导入数据的功能，当导入的数据不存在时则进行添加，有修改时则进行更新， 在刚碰到的时候，第一反应是将其实现分为两块，分别是判断增加，判断更新，后来发现在mysql中有 ON DUPLICATE KEY UPDATE一步就可以完成，感觉实在是太方便了。 但是ON DUPLICATE KEY UPDATE为Mysql特有语法，这是个坑 语句的作用，当insert已经存在的记录时，执...
on duplicate key update的使用
weixin_45326523的博客
04-30 437
插入或更新
Duplicate key 96000 不是更新和插入，只有查询
08-29
<think>我们正在处理一个看似矛盾的情况：用户报告在只有查询操作（SELECT）的情况下出现了“Duplicate key 96000”错误。通常，重复键错误（Duplicate key）发生在插入（INSERT）或更新（UPDATE）操作违反唯一性约束时。因此，我们需要探究在仅执行查询操作时为何会出现此错误。 ### 原因分析 1. **错误报告不准确**： - 用户可能误认为当前操作是查询，但实际上有后台进程或应用逻辑在执行写入操作。 - 应用框架或ORM可能自动触发了写入操作（如Hibernate的自动脏检查）。 2. **数据库触发器（Triggers）**： - 在查询操作期间，可能触发了某个触发器，该触发器内部执行了INSERT或UPDATE操作，导致违反唯一约束。 3. **数据库复制或同步机制**： - 如果数据库处于复制环境（如MySQL主从复制），可能在从库上执行了写入操作（例如修复数据时），而该写入操作在从库上造成冲突。 4. **查询语句的副作用**： - 某些SQL扩展允许查询有副作用（如SQL Server的`SELECT INTO`），但标准SELECT不应有写入行为。 5. **中间件行为**： - 数据库连接池或代理可能在执行某些维护操作（如刷新缓存）时触发了写入。 6. **并发操作影响**： - 其他并发的写入操作（由其他会话执行）可能导致唯一索引的临时状态，但通常不会在SELECT时报错。 ### 诊断步骤 1. **检查完整错误日志**： - 获取完整的错误堆栈信息，确认错误发生的具体上下文（如SQL语句、时间戳、会话ID）。 2. **审查数据库活动**： - 在错误发生时，捕获数据库的活动会话： ```sql -- MySQL SHOW FULL PROCESSLIST; -- PostgreSQL SELECT * FROM pg_stat_activity; ``` 3. **审计触发器**： - 检查相关表上是否定义了触发器： ```sql -- MySQL SHOW TRIGGERS LIKE 'your_table'; -- PostgreSQL SELECT tgname FROM pg_trigger WHERE tgrelid = 'your_table'::regclass; ``` 4. **分析应用代码**： - 检查执行查询的代码路径，确认是否有隐藏的写入操作（如使用Spring Data JPA的`@Query`注解可能隐含修改操作）。 5. **监控复制状态**： - 如果使用复制，检查复制状态和错误日志： ```sql SHOW SLAVE STATUS; -- MySQL ``` ### 解决方案 1. **修复触发器逻辑**： - 如果触发器是罪魁祸首，修改其逻辑以避免重复键写入： ```sql CREATE TRIGGER example_trigger BEFORE INSERT ON your_table FOR EACH ROW BEGIN -- 添加防重复逻辑 IF (EXISTS(SELECT 1 FROM your_table WHERE unique_col = NEW.unique_col)) THEN SET NEW.unique_col = NULL; -- 或跳过插入 END IF; END; ``` 2. **隔离查询操作**： - 将查询操作路由到只读副本（若有），避免触发任何写入逻辑。 3. **审查数据库复制**： - 修复主从复制中的不一致： ```bash # MySQL 主从数据校验 pt-table-checksum --databases=your_db pt-table-sync --execute ``` 4. **应用层修正**： - 在代码中添加防护逻辑，确保查询操作不会意外触发写入： ```java @Transactional(readOnly = true) // Spring的只读事务 public List<Record> safeQuery() { return repository.findRecords(); } ``` ### 特殊情况：查询计划生成中的错误 - **数据库引擎缺陷**：极少数情况下，数据库优化器在生成查询计划时可能触发内部错误（如MySQL的Bug #NNNNN），但通常不会报告为“Duplicate key”。 ### 验证方法 1. 在测试环境模拟相同查询，使用相同数据集。 2. 逐步剥离应用层（如直接使用SQL客户端执行查询），确认错误是否重现。 > **关键提示**：真正的查询操作不会导致重复键错误。因此，必须深入挖掘背后的隐藏操作。[^1][^2] --- ### 相关问题 1. 如何检测数据库中的隐藏触发器？ 2. MySQL主从复制冲突的常见解决方案有哪些？ 3. 在Spring框架中如何强制事务为只读模式？ 4. 数据库查询操作可能产生哪些副作用？ 5. 如何审计数据库中的所有写入操作来源？</think>### 在只有查询操作时出现 "Duplicate key 96000" 错误的原因及解决方案 #### 一、问题本质分析 虽然"Duplicate key"错误通常与写入操作相关，但在**纯查询场景**中出现该错误可能有以下特殊原因： 1. **数据库连接池污染** - 应用层未正确释放数据库连接，导致前一个会话的写入操作错误（如Duplicate key）被传递给新查询会话 - 表现为：查询操作意外抛出写入错误（错误代码96000） 2. **数据库复制/同步机制故障** - 主从同步过程中出现数据不一致（如binlog重放错误） - 从库在响应查询时报告底层存储引擎的唯一键冲突 3. **查询优化器缺陷** - 特定数据库版本（如MySQL 5.x）的查询优化器在处理复杂查询时可能错误触发约束检查 - 典型场景：包含子查询的SELECT语句涉及唯一索引表 4. **内存数据结构损坏** - InnoDB缓冲池或MyISAM索引缓存发生位翻转 - 错误代码：`ER_DUP_KEY (96000)` #### 二、诊断步骤 ```sql -- 1. 检查错误日志定位源头 SHOW ENGINE INNODB STATUS; -- 查看最新错误上下文 -- 2. 验证表结构完整性（MySQL示例） CHECK TABLE your_table QUICK; -- 3. 检测复制一致性（主从环境） pt-table-checksum --databases=your_db ``` #### 三、针对性解决方案 1. **连接池污染修复** ```java // Java示例：强制重置污染连接 dataSource.getConnection().close(); // 废弃当前连接 Connection cleanConn = dataSource.getConnection(); // 获取新连接 ``` - 配置连接验证参数（JDBC URL添加）： ``` ?testOnBorrow=true&validationQuery=SELECT 1 ``` 2. **主从同步修复** ```bash # 重新同步从库数据 mysql> STOP SLAVE; mysql> RESET SLAVE ALL; $ mysqldump --master-data=2 -u root -p master_db | mysql -u root -p slave_db ``` 3. **查询优化器规避** - 方案1：拆分复杂查询 ```sql -- 原始查询 SELECT * FROM tableA WHERE id IN (SELECT DISTINCT id FROM tableB); -- 改为分步执行 CREATE TEMPORARY TABLE tmp_ids SELECT DISTINCT id FROM tableB; SELECT * FROM tableA JOIN tmp_ids USING(id); ``` - 方案2：强制索引提示 ```sql SELECT /*+ INDEX(tableA) */ * FROM tableA WHERE ... ``` 4. **内存损坏恢复** ```sql -- InnoDB引擎修复 ALTER TABLE your_table ENGINE=INNODB; -- 重建表结构 -- MyISAM引擎修复 REPAIR TABLE your_table; ``` #### 四、预防措施 1. **连接池监控** ```mermaid graph LR A[应用启动] --> B[初始化连接池] B --> C{执行查询} C -->|成功| D[归还连接] C -->|失败| E[销毁连接] E --> F[创建新连接] ``` 2. **定期一致性检查** ```bash # 每周自动检查（crontab） 0 3 * * 1 /usr/bin/pt-table-checksum --quiet ``` 3. **数据库参数优化** ```ini # my.cnf 关键配置 [mysqld] innodb_buffer_pool_validate_on_resore=ON query_cache_type=0 # 避免复杂查询缓存 ``` > **关键结论**：纯查询操作报错通常指示底层基础设施问题而非SQL本身，需系统级排查[^1][^2] --- ### 相关问题 1. 如何检测MySQL主从数据不一致？ 2. 数据库连接池的最佳配置参数有哪些？ 3. InnoDB缓冲池损坏的常见症状是什么？ 4. 复杂SQL查询优化的基本原则？ 5. 如何监控数据库连接的生命周期？
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值