mysqldump:在写入时出现errno 32:“突然之间"仍有足够的空间... Drupal 6安装 [英] mysqldump: Got errno 32 on write: 'all of a sudden' plenty of room still...Drupal 6 installation

问题描述

在开发服务器上，我尝试运行已经使用了将近一年的相同脚本，最后得到了:mysqldump:写入时出现errno 32

上周，IT系统管理员刚刚将虚拟服务器还原到备份前几天，并且一切正常.

Drupal安装很好，实时服务器也很好(开发服务器的副本)...我们大约有30台左右的虚拟服务器都在同一个盒子上，IT SysAdmin已经分配了很多资源. /p>

这是我在开发人员上使用df -h所得到的:

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        18G  6.1G   12G  36% /
udev           1000M  4.0K 1000M   1% /dev
tmpfs           403M  228K  403M   1% /run
none            5.0M     0  5.0M   0% /run/lock
none           1007M     0 1007M   0% /run/shm
/dev/sdb1       100G  8.1G   87G   9% /data
xxxx@dev1:~$ 

以及在命令行中运行脚本后的基本输出:

第5416行的错误1005(HY000):无法创建表 'content_type_ses_job_postings'(errno:28)mysqldump:在errno 32上 写入错误2003(HY000):无法连接到MySQL服务器 '198.xx.xx.xx'(111)LDAP服务器IP已更新.

请注意，我得到的最后一个ERROR 2003关于即使在一切正常也不会连接到MySQL服务器，尽管这不应该发生，但我认为这是更多的用户问题，因为数据库备份，保存然后导入到保持"数据库，我在更新内容时将其切换到该数据库，所以也许是这样，但这从来不是一个特定的问题.

如果error 32与空间有关，那么空间问题在哪里?如果与权限有关，权限问题将放在哪个文件夹上?但是，我不知道如何或如何动态改变权限，就像我之前所说的那样，我已经运行这些脚本大约8个月了，没问题吗?

开发服务器的基础

• MySQL 5.5.24
• Ubuntu0.12.04.1
• PHP 5.3

解决方案

似乎很奇怪

[root@*****]# perror 28
OS error code  28:  No space left on device
[root@*****]# perror 32
OS error code  32:  Broken pipe

由于mysqldump总是在随机位置中断，它与空间有关，并且没有磁盘已满的情况，所以我会怀疑更深层的问题:MySQL数据包.什么是MySQL封包?

根据本书的第99页

以下1-3段对此进行了解释:

MySQL网络通讯代码原为 根据以下假设编写 查询总是相当短， 因此可以发送给和 由服务器一整块地处理， 在MySQL中称为 packet 术语.服务器分配 用于存储临时缓冲区的内存 数据包，它请求足够 完全适合.这个架构 需要采取预防措施以避免 服务器内存不足---上限 在数据包的大小上，这 选项完成.

与之相关的感兴趣的代码 该选项位于 sql/net_serv.cc .看一下 my_net_read()，然后按照电话呼叫 my_real_read()并付款 特别注意 net_realloc().

此变量还限制了长度 许多字符串函数的结果. 请参见 sql/field.cc 和 sql/intem_strfunc.cc 了解详情.

给出此解释，使批量INSERT可以相当快地加载/卸载MySQL数据包.当max_allowed_pa​​cket对于给定的数据负载过小时，尤其如此.

我之前曾写过这本书: MySQL服务器已经消失，阻止了大型转储的导入

尝试将将mysqldump的max_allowed_pa​​cket提升到如下:

mysqldump --max-allowed-packet=1073741824 ...

并尝试mysqldump.

如果这样做不行，请执行以下操作:

将此添加到my.cnf

[mysqld]
max_allowed_packet = 1G

然后，以root@localhost登录到MySQL并运行它

mysql> SET GLOBAL max_allowed_packet = 1024 * 1024 * 1024;

并尝试mysqldump.

尝试一下！

On the dev server, I tried to run the same script I've been using for almost a year and at the end got the : mysqldump: Got errno 32 on write

Last week, the IT sysadmin just restored the virtual server to a few days before backup and it all worked.

The Drupal install is fine and the live server is fine (a duplicate of the dev server)...we have about 30 or so virtual servers all on the same box and the IT SysAdmin has allocated quite a few resources.

Here's what I get with df -h on the dev:

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        18G  6.1G   12G  36% /
udev           1000M  4.0K 1000M   1% /dev
tmpfs           403M  228K  403M   1% /run
none            5.0M     0  5.0M   0% /run/lock
none           1007M     0 1007M   0% /run/shm
/dev/sdb1       100G  8.1G   87G   9% /data
xxxx@dev1:~$ 

and the basic output after running my script in the command line:

ERROR 1005 (HY000) at line 5416: Can't create table 'content_type_ses_job_postings' (errno: 28) mysqldump: Got errno 32 on write ERROR 2003 (HY000): Can't connect to MySQL server on '198.xx.xx.xx' (111) LDAP server IP updated.

Note that I get that last ERROR 2003 about not connecting to the MySQL server even when it all works so though that shouldn't happen, I think that's more of the user problem as the databases backup, save, and then import to the 'holding' db which I then switch to when updating content so perhaps that's something, but it's never been a specific problem.

If error 32 is related to space, where could the space problem be? If it's related to permissions, which folder would the permissions problem be on? However, I don't know how or what could have dynamically changed the permissions anywhere as...I stated before, I've been running these scripts for about 8 months with no problem?

The basics of the dev server

• MySQL 5.5.24
• Ubuntu0.12.04.1
• PHP 5.3

解决方案

Seems strange

[root@*****]# perror 28
OS error code  28:  No space left on device
[root@*****]# perror 32
OS error code  32:  Broken pipe

Since the mysqldump keeps breaking at random places, it is space-related, and no disk full condition, I would suspect the problem at a deeper layer : the MySQL Packet. What is a MySQL Packet?

According to the page 99 of the Book

here are paragraphs 1-3 explaining it:

MySQL network communication code was written under the assumption that queries are always reasonably short, and therefore can be sent to and processed by the server in one chunk, which is called a packet in MySQL terminology. The server allocates the memory for a temporary buffer to store the packet, and it requests enough to fit it entirely. This architecture requires a precaution to avoid having the server run out of memory---a cap on the size of the packet, which this option accomplishes.

The code of interest in relation to this option is found in sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay particular attention to net_realloc().

This variable also limits the length of a result of many string functons. See sql/field.cc and sql/intem_strfunc.cc for details.

Given this explanation, making bulk INSERTs will load/unload a MySQL Packet rather quickly. This is especially true when max_allowed_packet is too small for the given load of data coming at it.

I wrote about this before : MySQL server has gone away obstructing import of large dumps

Try raising max_allowed_packet for the mysqldump to 1G as follows:

mysqldump --max-allowed-packet=1073741824 ...

and try the mysqldump.

If this does not do it, then do this:

Added this to my.cnf

[mysqld]
max_allowed_packet = 1G

Then, login to MySQL as root@localhost and run this

mysql> SET GLOBAL max_allowed_packet = 1024 * 1024 * 1024;

and try the mysqldump.

Give it a Try !!!

这篇关于mysqldump:在写入时出现errno 32:“突然之间"仍有足够的空间... Drupal 6安装的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！