mysql：无法将 max_allowed_package 设置为大于 16MB
我不确定这是否是发布此类问题的正确位置，如果不是，请（礼貌地）让我知道...:-)
我需要在 mysql 数据库上保存大于 16MB 的文件php 站点...
我已经更改了 c:\xampp\mysql\bin\my.cnf
并将 max_allowed_packet 设置为 16 MB，一切正常，
然后我将其设置为 32 MB，但我无法处理大于 16 MB 的文件
我收到以下错误：
“MySQL 服务器已消失”
（与 max_allowed_packet 设置为 1MB 时出现的错误相同）
必须有其他一些设置不允许我处理大于 16MB 的文件
也许是 php 客户端，我猜，但我不知道在哪里编辑它，
我运行的代码
这是当 file.txt 小于 16.776.192 字节长时
，它工作正常，但如果 file.txt 有 16.777 .216 字节我收到上述错误
哦，并且字段 download.content 是一个 longblob...
$file = 'file.txt';
$file_handle = fopen( $file, 'r' );
$content = fread( $file_handle, filesize( $file ) );
fclose( $file_handle );
db_execute( 'truncate table download', true );
$sql =
"insert into download(
code, title, name, description, original_name,
mime_type, size, content,
user_insert_id, date_insert, user_update_id, date_update )
values (
'new file', 'new file', 'sas.jpg', 'new file', '$file',
'mime', " . filesize( $file ) . ", '" . addslashes( $content ) . "',
0, " . db_char_to_sql( now_char(), 'datetime' ) . ", 0, " . db_char_to_sql( now_char(), 'datetime' ) . " )";
db_execute( $sql, true );
（db_execute 函数只是打开连接并执行 sql 内容）
在 Windows XP sp2 上运行 服务器版本：5.0.67-社区 PHP 版本 4.4.9 mysql 客户端 API 版本：3.23.49
使用：ApacheFriends XAMPP (Basispaket) 版本 1.6.8 附带 + 阿帕奇 2.2.9 + MySQL 5.0.67（社区服务器） + PHP 5.2.6 + PHP 4.4.9 + 梨 + phpMyAdmin 2.11.9.2 ...
这是内容的一部分 c:\xampp\mysql\bin\my.cnf
# The MySQL server [mysqld] port= 3306 socket= "C:/xampp/mysql/mysql.sock" basedir="C:/xampp/mysql" tmpdir="C:/xampp/tmp" datadir="C:/xampp/mysql/data" skip-locking key_buffer = 16M # max_allowed_packet = 1M max_allowed_packet = 32M table_cache = 128 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M
I'm not sure if this is the right place to post these kind of questions, if it's not so, please (politely) let me know... :-)
I need to save files greater than 16MB on a mysql database from a php site...
I've already changed the c:\xampp\mysql\bin\my.cnf
and set max_allowed_packet to 16 MB, and everything worked fine
then I set it to 32 MB but there´s no way I can handle a file bigger than 16 MB
I get the following error:
'MySQL server has gone away'
(the same error I had when max_allowed_packet was set to 1MB)
there must be some other setting that doesn´t allow me to handle files bigger than 16MB
maybe the php client, I guess, but I don't know where to edit it
this is the code I'm running
when file.txt is smaller than 16.776.192 bytes long, it works fine, but
if file.txt has 16.777.216 bytes i get the aforementioned error
oh, and the field download.content is a longblob...
$file = 'file.txt';
$file_handle = fopen( $file, 'r' );
$content = fread( $file_handle, filesize( $file ) );
fclose( $file_handle );
db_execute( 'truncate table download', true );
$sql =
"insert into download(
code, title, name, description, original_name,
mime_type, size, content,
user_insert_id, date_insert, user_update_id, date_update )
values (
'new file', 'new file', 'sas.jpg', 'new file', '$file',
'mime', " . filesize( $file ) . ", '" . addslashes( $content ) . "',
0, " . db_char_to_sql( now_char(), 'datetime' ) . ", 0, " . db_char_to_sql( now_char(), 'datetime' ) . " )";
db_execute( $sql, true );
(the db_execute funcion just opens the connections and executes the sql stuff)
running on windows XP sp2
server version: 5.0.67-community
PHP Version 4.4.9
mysql client API version: 3.23.49
using: ApacheFriends XAMPP (Basispaket) version 1.6.8
that comes with
+ Apache 2.2.9
+ MySQL 5.0.67 (Community Server)
+ PHP 5.2.6 + PHP 4.4.9 + PEAR
+ phpMyAdmin 2.11.9.2
...
this is part of the content of
c:\xampp\mysql\bin\my.cnf
# The MySQL server [mysqld] port= 3306 socket= "C:/xampp/mysql/mysql.sock" basedir="C:/xampp/mysql" tmpdir="C:/xampp/tmp" datadir="C:/xampp/mysql/data" skip-locking key_buffer = 16M # max_allowed_packet = 1M max_allowed_packet = 32M table_cache = 128 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
编辑：我原来的答案是不正确的。
链接的 mysql 客户端库 php 的版本（明显）比 mysql 服务器版本旧，这使得我写的内容看起来是正确的； 但现在看来情况并非如此。 当我针对正确的 mysql 客户端库重新编译 php 时，只需要更新服务器端变量即可。
我将在这里留下回复，因为其他人可能会发现问题，并且它很可能与客户端库有关（就像我的一样）。 它也很可能是原始问题的答案（鉴于原始问题指出 v3 库和 v4 服务器）。
运行
并在 mysql 部分查找“Client API version”以检查版本。
--来自这里的旧答案
问题是，虽然有一个服务器变量“max_allowed_packet”，但客户端中也有一个设置。 进行设置
您可以在 mysql 客户端中使用或
在 my.cnf 的 [client] 部分中
。不幸的是，php 不读取 my.cnf 并且也不允许您设置此客户端变量。 因此，您受到 php 源代码中的编译时间限制的困扰：
如果您在修改了此限制的情况下重新编译 php 模块，它应该可以解决您的问题，但正如其他人指出的那样，您确实应该以不同的方式做事。
Edit: my original answer is incorrect.
The version of the mysql client library php was linked against was (significantly) older than the mysql server version, which made it appear that what I wrote was true; however it now seems not to be the case. When I recompile php against the correct mysql client libraries updating the server-side variable is all that's required.
I'll leave the response here because someone else may find the problem and it could well be related (as was mine) to client libraries. It's also (given the original question states v3 library and v4 server) quite possibly the answer to the original question.
Run
and look for "Client API version" in the mysql section to check the version.
-- old answer from here
The problem is that while there is a server variable "max_allowed_packet", there is also one set in the client. You can set this in the mysql client using
or by using
in the [client] section of the my.cnf
Unfortunately php doesn't read my.cnf and doesn't allow you to set this client-side variable either. You are therefore stuck with the compile-time limit in the php source:
If you recompile the php module with this limit modified it should fix your problem but as others have pointed out, you should really be doing things differently.
我过去在使用 PHP 和 MySQL 时遇到过这个问题。
我记得我的解决方案将二进制文件分解成块并在数组上循环。 首先它会 INSERT 一个新行，然后它会使用 CONCAT 语句更新该行。
这很糟糕，但它成功了。
实际上，最后，我们将二进制内容存储在文件中，并在数据库中保留指向该文件的指针。 效率要高得多。
I have come across this issue in the past with PHP and MySQL.
I remember that my solution broke the binary up into chunks and looped over the array. First it would INSERT a new row, then it would UPDATE the row using the CONCAT statement.
It was nasty, but it did the trick.
Actually, in the end, we stored the binary contents on file and kept a pointer to the file in the database. It was much more efficient.
为此原因扩展 max_allowed_packet 是糟糕实现的结果。 我建议阅读这篇文件存储的文章/实现。 您可以存储任何大小的文件。
Extending the max_allowed_packet for this reason is the result of a bad implementation. I suggest reading this article/implementation for file storage. You can store files of any size.
php.ini 文件中还有最大允许上传限制。 它没有直接连接到 MySQL，但如果您使用 php 发送数据，这可能是您的问题。
There is also max alowed upload limit in php.ini file. It's not directly connected to MySQL but if you are using php to send data it could be your problem.