精选文章 如何更改max_allowed_pa​​cket大小

如何更改max_allowed_pa​​cket大小

作者:CHCH998 时间: 2020-08-05 10:36:55
CHCH998 2020-08-05 10:36:55

本文翻译自:How to change max_allowed_packet size

I am having a problem with BLOB fields in my MySQL database - when uploading files larger than approx 1MB I get an error Packets larger than max_allowed_packet are not allowed. 我的MySQL数据库中的BLOB字段存在问题-上传大于1MB的文件时出现错误, Packets larger than max_allowed_packet are not allowed.

Here is what i've tried: 这是我尝试过的:

In MySQL Query Browser I ran a show variables like 'max_allowed_packet' which gave me 1048576. 在MySQL查询浏览器中,我运行了一个show variables like 'max_allowed_packet' ,它给了我1048576。

Then I execute the query set global max_allowed_packet=33554432 followed by show variables like 'max_allowed_packet' - it gives me 33554432 as expected. 然后,我执行查询set global max_allowed_packet=33554432然后执行show variables like 'max_allowed_packet'如预期的那样,它给了我33554432。

But when I restart the MySQL server it magically goes back to 1048576. What am I doing wrong here? 但是,当我重新启动MySQL服务器时,它神奇地回到了1048576。在这里我做错了什么?

Bonus question, is it possible to compress a BLOB field? 额外的问题,是否可以压缩BLOB字段?


#1楼

参考:https://stackoom.com/question/xPqg/如何更改max-allowed-pa-cket大小


#2楼

I think some would also want to know how to find the my.ini file on your PC. 我认为有些人也想知道如何在您的PC上找到my.ini文件。 For windows users, I think the best way is as follows: 对于Windows用户,我认为最好的方法如下:

  1. Win+R (shortcut for 'run'), type services.msc , Enter Win + R (“运行”的快捷方式),键入services.msc ,输入
  2. You could find an entry like 'MySQL56', right click on it, select properties 您可以找到类似“ MySQL56”的条目,右键单击它,选择属性
  3. You could see sth like "D:/Program Files/MySQL/MySQL Server 5.6/bin\\mysqld" --defaults-file="D:\\ProgramData\\MySQL\\MySQL Server 5.6\\my.ini" MySQL56 您可能会看到类似“ D:/ Program Files / MySQL / MySQL Server 5.6 / bin \\ mysqld” --defaults-file =“ D:\\ ProgramData \\ MySQL \\ MySQL Server 5.6 \\ my.ini” MySQL56

I got this answer from http://bugs.mysql.com/bug.php?id=68516 我从http://bugs.mysql.com/bug.php?id=68516得到了这个答案


#3楼

One of my junior developers was having a problem modifying this for me so I thought I would expand this in greater detail for linux users: 我的一名初级开发人员在为我修改此文件时遇到了问题,因此我想我将为Linux用户更详细地介绍这一点:

1) open terminal 1)打开终端

2) ssh root@YOURIP 2)ssh root @您的IP地址

3) enter root password 3)输入root密码

4) nano /etc/mysql/my.cnf (if command is not recognized do this first or try vi then repeat: yum install nano ) 4)nano /etc/mysql/my.cnf(如果无法识别命令,请先执行此操作,或者先尝试vi然后再重复:yum install nano)

5) add the line: max_allowed_packet=256M (obviously adjust size for whatever you need) under the [MYSQLD] section. 5)在[MYSQLD]部分下添加以下行:max_allowed_pa​​cket = 256M(显然可以根据需要调整大小)。 He made a mistake of putting it at the bottom of the file first so it did not work. 他犯了一个错误,将其首先放在文件的底部,因此它不起作用。

如何更改max_allowed_pa​​cket大小1

6) Control + O (save) then ENTER (confirm) then Control + X (exit file) 6)Control + O(保存),然后按ENTER(确认),然后按Control + X(退出文件)

7) service mysqld restart 7)服务mysqld重启

8) You can check the change in the variables section on phpmyadmin 8)您可以在phpmyadmin的变量部分中检查更改


#4楼

If getting this error while performing a backup, max_allowed_packet can be set in the my.cnf particularly for mysqldump . 如果在执行备份时出现此错误,则可以在my.cnf设置max_allowed_packet ,特别是对于mysqldump

[mysqldump]
max_allowed_packet=512M

I kept getting this error while performing a mysqldump and I did not understand because I had this set in my.cnf under the [mysqld] section. 我在执行mysqldump时一直收到此错误,但我不明白,因为我在[mysqld]部分下的my.cnf对此进行了设置。 Once I figured out I could set it for [mysqldump] and I set the value, my backups completed without issue. 一旦确定可以为[mysqldump]设置它并设置该值,我的备份就可以顺利完成了。


#5楼

If you want upload big size image or data in database. 如果要上载较大尺寸的图像或数据库中的数据。 Just change the data type to 'BIG BLOB' . 只需将数据类型更改为'BIG BLOB'


#6楼

For those running wamp mysql server 对于那些运行wamp mysql服务器的人

Wamp tray Icon -> MySql -> my.ini Wamp tray图标-> MySql-> my.ini

[wampmysqld]
port        = 3306
socket      = /tmp/mysql.sock
key_buffer_size = 16M
max_allowed_packet = 16M        // --> changing this wont solve
sort_buffer_size = 512K

Scroll down to the end until u find 向下滚动到最后直到找到

[mysqld]
port=3306
explicit_defaults_for_timestamp = TRUE

Add the line of packet_size in between 在之间添加packet_size

[mysqld]
port=3306
max_allowed_packet = 16M
explicit_defaults_for_timestamp = TRUE

Check whether it worked with this query 检查它是否适用于此查询

Select @@global.max_allowed_packet;

相关文章

勿删,copyright占位
分享文章到微博
分享文章到朋友圈

上一篇:公众号榜单 | 2020·7月公众号行业排行榜重磅发布

下一篇:(1)PCIE接口应用领域(学无止境)

您可能感兴趣

华为云40多款云服务产品0元试用活动

免费套餐,马上领取!
CSDN

CSDN

中国开发者社区CSDN (Chinese Software Developer Network) 创立于1999年,致力为中国开发者提供知识传播、在线学习、职业发展等全生命周期服务。