com.mysql.jdbc.PacketTooBigException

com.mysql.jdbc.PacketTooBigException

提问by Fahim Parkar

I am storing images in MYSQL.

我在 MYSQL 中存储图像。

I have table as

我有桌子

CREATE TABLE myTable (id INT, myImage BLOB);

When I am trying to insert 4.7MBfile, I am getting exception as

当我尝试插入4.7MB文件时，出现异常

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (4996552 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.

I believe this is related to image size only. Is there any other variable type that I can use?

我相信这仅与图像大小有关。我可以使用任何其他变量类型吗？

---

---

Update 1

更新 1

As per older SO question, I also tried with MEDIUMBLOBbut still I am getting same error.

根据较旧的 SO 问题，我也尝试过，MEDIUMBLOB但仍然遇到相同的错误。

Adding Image to a database in Java

用Java将图像添加到数据库

---

---

Update 2

更新 2

At the start of the project, I execute below query and everything is working now

在项目开始时，我执行以下查询，现在一切正常

SET GLOBAL max_allowed_packet = 1024*1024*14;

回答by eggyal

As the error says, it has nothing to do with variable type but rather the max_allowed_packetvariable:

正如错误所说，它与变量类型无关，而是与max_allowed_packet变量有关：

You must increase this value if you are using large BLOBcolumns or long strings. It should be as big as the largest BLOByou want to use. The protocol limit for max_allowed_packetis 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

如果您使用大BLOB列或长字符串，则必须增加此值。它应该和BLOB你想使用的最大一样大。的协议限制为max_allowed_packet1GB。该值应为 1024 的倍数；非倍数向下舍入到最接近的倍数。

But, generally speaking, don't store files in your database - store them in your filesystem and record the path to the file in the database.

但是，一般来说，不要将文件存储在您的数据库中 - 将它们存储在您的文件系统中并在数据库中记录文件的路径。

回答by Peter Perhá?

For Windows users:

对于 Windows 用户：

mysql_home points to your mysql/mariadb installation folder.

mysql_home 指向您的 mysql/mariadb 安装文件夹。

open cmd, cd to %mysql_home%\bin and run mysqladmin > temp.txtThis will spit out a lot of information about the usage of the tool. Somewhere among all that output you will find this information:

打开cmd，cd到%mysql_home%\bin，运行，mysqladmin > temp.txt会吐出很多工具使用的信息。在所有输出中的某个地方，您会找到以下信息：

Default options are read from the following files in the given order: C:\windows\my.ini C:\windows\my.cnf C:\my.ini C:\my.cnf c:\mariadb-5.5.29-w inx64\my.ini c:\mariadb-5.5.29-winx64\my.cnf

默认选项按给定顺序从以下文件中读取： C:\windows\my.ini C:\windows\my.cnf C:\my.ini C:\my.cnf c:\mariadb-5.5.29- w inx64\my.ini c:\mariadb-5.5.29-winx64\my.cnf

This shows that you couldhave, if you don't have it already, a file called my.inior my.conf in the %mysql_home% directory.

这表明您可以my.ini在 %mysql_home% 目录中拥有一个名为or my.conf的文件（如果您还没有）。

create my.ini and add the lines:

创建 my.ini 并添加以下行：

[mysqld]
#allow larger BLOBs to be stored
max_allowed_packet = 10M

make sure to include the settings groupwhich is [mysqld]otherwise it will fail to start (and for me it ended up hanging in limbo).

确保包含设置组，[mysqld]否则它将无法启动（对我来说它最终陷入困境）。

You will now need to restart the MySQL daemon, this is done either by killing and starting the currently running mysqld process or by restarting the MySQL service (run services.msc, locate MySQL, press the restart button; or from cmd, net stop MySQLfollowed by net start MySQL).

您现在需要重新启动 MySQL 守护进程，这可以通过终止并启动当前运行的 mysqld 进程或通过重新启动 MySQL 服务来完成（运行 services.msc，找到 MySQL，按重新启动按钮；或从 cmd，net stop MySQL然后是net start MySQL） .

回答by Farm

Following worked for me

以下为我工作

edit my.cnf file ( mine was in /etc/mysql )

编辑 my.cnf 文件（我的在 /etc/mysql 中）

Then modify the max_allowed_packet value I set it to max_allowed_packet=200M

然后修改max_allowed_pa​​cket值我设置为max_allowed_pa​​cket=200M

Make sure you restart MySQL for change to take effect

确保重新启动 MySQL 以使更改生效

回答by Dinu Mathai

If working with AWS RDS, max_allowed_packet can be modified using DB Parameter Groups

如果使用 AWS RDS，可以使用数据库参数组修改 max_allowed_pa​​cket