Go Gorm Sqlite3 CreateInBatches 报错：too many SQL variable 排查与解决

时间：2022-12-22 07:50:32

Go：1.17.7

Gorm：gorm.io/gorm v1.22.3

Gorm-Sqlite3-driver: gorm.io/driver/sqlite v1.2.4

mattn-Sqlite3: github.com/mattn/go-sqlite3 v2.0.1+incompatible

Sqlite3：3.40.0

如果是老手，看到这里 ，应该就可以知道问题在哪了，不过这个解决方法也因为我是菜鸟，后面一步一步才发现的。

场景

使用gorm CreateInBatches 功能，批量插入100个对象 A ，对象 A 有 17 个字段

// BatchCreate is 批量新增
func (a *A) BatchCreate(tx db.Client, batch []*A) error {
   return tx.Table(a.TableName()).CreateInBatches(batch, len(batch)).Error
}

执行过后，报错：too many SQL variable。

如果想直接知道解决方案的，可以往后跳，下面的一些细节是我的排查思路

排查

之前碰到 too many SQL variable 的错误，是因为 gorm.where(“a=? and b=?”, a,b,c) 。类似这样子，原本需要 2 个参数就可以，但是传了 3 个的情况，会报 too many SQL variable。

顺着这个思路，就开始排查是不是 CreateInBatches 的参数有问题，导致了 SQL 执行出错。

不过，很遗憾，拿着打印的 SQL 去执行，发现没有报错，能直接批量写入 100 条数据，holy shit。那只能去问度娘了。

搜：

  • gorm 批量写入 too many SQL variable
  • gorm CreateInBatches 的坑
  • gorm 批量写入上限
  • …

找了很多，发现都没找到点上，或者根本不想关，那只能回归源码。

too many SQL variable 会是谁的报错？

  • gorm
  • sqlite-driver
  • mattn-sqlite
  • sdk/database

我们知道 go 访问数据库，都是通过驱动去访问，最后执行的结果也还是数据库返回的，gorm 这一层只是封装了一下调用方式，让我们更好用。所以，焦点就回归到，是不是 sqlite3 数据库本身对批量写入的 sql 语句有限制？顺藤摸瓜，找到了答案。

https://sqlite.org/limits.html#max_variable_number

A host parameter is a place-holder in an SQL statement that is filled in using one of the sqlite3_bind_XXXX() interfaces. Many SQL programmers are familiar with using a question mark (“?”) as a host parameter. SQLite also supports named host parameters prefaced by “:”, “$”, or “@” and numbered host parameters of the form “?123”.

Each host parameter in an SQLite statement is assigned a number. The numbers normally begin with 1 and increase by one with each new parameter. However, when the “?123” form is used, the host parameter number is the number that follows the question mark.

SQLite allocates space to hold all host parameters between 1 and the largest host parameter number used. Hence, an SQL statement that contains a host parameter like ?1000000000 would require gigabytes of storage. This could easily overwhelm the resources of the host machine. To prevent excessive memory allocations, the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999 for SQLite versions prior to 3.32.0 (2020-05-22) or 32766 for SQLite versions after 3.32.0.

The maximum host parameter number can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_VARIABLE_NUMBER,size) interface.

这个讲的是 sqlite3 parameter 最大限制数量，也就是在 SQL 语句中的占位符 ? (还有别的)。文中说，因为怕 SQL 语句里的 parameter 太多，例如到 1000000000 个，那会需要用到 G 为单位的存储，那会是灾难。为了防止分配如此巨大的内存空间，SQL 用 SQLITE_MAX_VARIABLE_NUMBER 这个配置来限制。重点来了：

  • 版本在 2020-05-22 号前的 3.32.0 默认 999
  • 版本在 3.32.0 后的默认 32766
验证一

注意，gorm 中的 CreateInBatches的第二个参数，是每次批量写入的数据长度，不是你要批量写入的数据的长度。例如你要批量写入 100 条，第二个参数写 50，那么就是分 2 次去 insert，是在一个事务里面。

查到这个信息后，当然要验证一下。验证的方式就是调整每次批量写入的数量，999 / 17 = 58.7(999 个 parameter，对象 A insert 需要 17 个 value)，所有就测试了一下 58 和 59 分别的执行情况。

  • 58 ：success
  • 59 ：too many SQL variable
解决方案一

调整每次 CreateInBatches 的 batchSize，改为 50，合适的范围。

// 最后调整成，最多 50
func (a *A) BatchCreate(tx db.Client, batch []*A) error {
   return tx.Table(a.TableName()).CreateInBatches(batch, 50).Error
}

接着排查

可能你看到这里会有点奇怪，为什么解决了还要接着排查。不知道，你注意到没有，上线写着的是 3.32.0 前后有区别，我的 Sqlite3 版本是 3.40.0，那正常来说应该可以批量写入的呀，这是为什么呢？

Sqlite3 版本没问题，那会不会是 gorm sqlite 相关的包，版本兼容问题呢？

去看了 github 上 gorm、sqlite3-driver、mattn-sqlite3 的首页，发现都有新的版本。

因为我用的 gorm 和 sqlite3-driver 包比较旧，就改成了：

Gorm: gorm.io/gorm v1.24.2

Gorm-Sqlite3-driver: : gorm.io/driver/sqlite v1.4.3

mattn-Sqlite3: github.com/mattn/go-sqlite3 v1.14.15

因为这个时候，我是用一个新的 project 测试，不是在原有的项目上面。

验证二

切换完版本，根本不用改 batchSize，一条过，OMG

解决方案二

更新到最新版本的 gorm，自动会更新 sqlite3-driver 的包

此时我还没有注意到其中一个细节

验证三

如果是最新版本问题，那么是不是在 gorm 中间版本的时候，说明了某个版本只能兼容到 sqlite3 3.32.0。于是我就翻了 gorm 的 release 发现 v1.3.2 版本写着 update sqlite3 driver。以为我发现了新大陆，被我找到了。

于是我把 gorm 版本改成了 v1.3.2，发现确实可以。正当我以为解决完问题的时候，我在我新的项目下改成 v1.2.4，也就是开头的版本，验证一下，就是因为版本太旧，不能兼容 Sqlite3 3.32.0 之后的版本的时候。我去，它竟然也可以。

这是为什么？这是为什么？比对了一下 go mod，看下两边的包版本是不是哪里不一样。最终发现罪魁祸首：

mattn-Sqlite3: github.com/mattn/go-sqlite3 v2.0.1+incompatible

mattn-Sqlite3: github.com/mattn/go-sqlite3 v1.14.15

就是因为这两个包的不同。

验证四

旧项目不能更新到最新版本的 gorm，所以就用 replace 把包替换一下，很真的可以

解决方案三

多看看这些包的官方文档，在 https://github.com/mattn/go-sqlite3 写着：

NOTE: The increase to v2 was an accident. There were no major changes or features.

明确地写着 v2 不能用呀~~

这也是为什么我在前面说的，老手，一眼就可以看出来

引申问题

go mod，为什么会去拉取到 v2 的包，我还没有分析出来

总结

虽然兜兜转转，最后竟然是因为包的缘故，导致了 too many SQL variable，不过还是学到了很多东西。也让我注意了批量写入时，是需要注意批量写入的大小的。也不是看到默认值是 32766，那就意味着我可以一次性干到最大，这个想法是不对的。在 https://github.com/mattn/go-sqlite3/issues/704 这个 issue 有提到类似的问题，最下面有一个写法，是用 Prepare ，然后每次都批量写入一些，然后完成你最后需要批量写入的数据。再看了下 gorm 的 CreateInBatches，是一样的效果。

解决方案

  • 限制批量写入的数量，太大性能可能就会下降
  • 删掉旧的 go mod，拉取最新的包，在够用的前提下，不见得最新就是最好的
  • 多学习，多看源码

启发文章

https://blog.csdn.net/lovelyelfpop/article/details/51064664

标签：

相关文章