【数据库-MySql】MySQL server has gone away & Got a packet bigger than 'max_allowed_packet' bytes

最新推荐文章于 2023-05-28 15:25:03 发布

少莫千华

最新推荐文章于 2023-05-28 15:25:03 发布

阅读量179

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/chenlu5201314/article/details/105067911

版权

数据库专栏收录该内容

63 篇文章 3 订阅

订阅专栏

一、报错

背景

新建数据库，右键“运行SQL文件...”，文件大小[766 MB (803,415,614 字节)]

[Err] 2006 - MySQL server has gone away

[Err] 1153 - Got a packet bigger than 'max_allowed_packet' bytes

报错1

[Err] 2006 - MySQL server has gone away
[Err] insert  into `dispensing_pack_tablet_hist`(`dsps_num`,`pack_num`,`tblt_cd`,`atm_num`,`cst_num`,`alt_cst_num`,`tblt_qtt`,`fsp_num`,`pack_tblt_num`,`rcpt_num`,`cst_pos`,`alt_cst_pos`,`tblt_seq_num`,`drts_cd`,`day_take_cnt`,`utc_num`,`delay_time`,`alt_delay_time`,`envelope_num`,`take_days`,`dsps_tgt_yn`,`tblt_shape`,`tblt_color`,`tblt_spec`,`tblt_note`,`tblt_barcode`,`tblt_lot_num`,`tblt_exp_date`,`user_exp_date`,`dev_id`,`dsps_tblt_qty`,`pwd_num`,`updt_dtm`,`updt_user_id`,`hspt_tblt_lot_num`,`hspt_tblt_exp_date`,`take_time_cd`,`take_tp_cd`,`take_tp_note`,`sup_dtm`,`ocs_order_tblt_key`,`take_tp_dose`,`tblt_note2`,`tblt_note3`,`tblt_note4`,`sig_lines`,`return_tblt_qty`,`rx_no`,`rx_doctor_nm`,`sup_user_id`,`sup_cfrm_dtm`,`sup_cfrm_user_id`,`tblt_maker`) values ('171118104957051',128,'011327',1,'434',NULL,1,0,288,'171118104856663','32F',NULL,5,'中晚餐前5分钟',1,'',700,NULL,0,1,'Y','','','1MG/片','','','','20200228','','434',1,0,'20171118110544','u','','','','0800','','20171115133724','122333461:118','1.0000','','','','',0,'','','','20171115133724','','丹麦诺和诺德公司'),('171118104957051',172,'010895',1,'31',NULL,1,0,394,'171118104856664','62R',NULL,2,'',1,'',850,NULL,0,1,'Y','','','0.5G/片','','','','','','31',1,0,'20171118110713','u','','','#','1600','','20171118105019','122333641:136','1.0000','','','','',0,'','','','20171118105019','','深圳市中联制药有限公司'),('171118104957051',172,'011687',1,'404',NULL,1,0,395,'171118104856664','63F',NULL,10,'',1,'',1000,NULL,0,1,'Y','','','0.1G/粒','','','','20191229','','404',1,0,'20171118110713','u','','','#','1600','','20171118095325','122333641:144','1.0000','','','','',0,'','','','20171118095325','','齐鲁制药有限公司'),('171118104957051',173,'011682',1,'28',NULL,1,0,396,'171118104856664','62L',NULL,8,'',1,'',850,NULL,0,1,'Y','','','5MG/片','','','','20200501','','28',1,0,'20171118110955','u','','','#','2000','','20171114132452','122333641:142','1.0000','','','','',0,'','','','20171114132452','','卫材(中国)药业有限公司'),('171118104957051',173,'011127',1,'429',NULL,1,0,397,'171118104856664','41F',NULL,12,'',1,'',800,NULL,0,1,'Y','','','20MG/片','','','','20200501','','429',1,0,'20171118110955','u','','','#','2000','','20171117133943','122333641:146','1.0000','','','','',0,'','','','20171117133943','','辉瑞制药有限公司'),('171118104957051',174,'010895',1,'31',NULL,1,0,398,'171118104856664','62R',NULL,1,'',1,'',850,NULL,0,1,'Y','','','0.5G/片','','','','','','31',1,0,'20171118110958','u','','','#','0800','','20171118105019','122333641:135','1.0000','','','','',0,'','','','20171118105019','','深圳市中联制药有限公司'),('171118104957051',174,'011749',1,'191',NULL,1,0,399,'171118104856664','33R',NULL,3,'',1,'',550,NULL,0,1,'Y','','','5MG/片','','','1508082','20200901','','191',1,0,'20171118110958','u','','','#','0800','','20171117133822','122333641:137','1.0000','','','','',0,'','','','20171117133822','','阿斯利康制药有限公司'),('171118104957051',174,'012443',1,'445',NULL,1,0,400,'171118104856664','22F',NULL,4,'',1,'',550,NULL,0,1,'Y','','','0.5MG/片','','','','20190308','','445',1,0,'20171118110958','u','','','#','0800','','20171115133741','122333641:138','1.0000','','','','',0,'','','','20171115133741','','卫材(中国)药业有限公司'),('171118104957051',174,'012327',1,'354',NULL,1,0,401,'171118104856664','11R',NULL,5,'',1,'',200,NULL,0,1,'Y','','','5MG/片','','','','','','354',1,0,'20171118110958','u','','','#','0800','','20171114132501','122333641:139','1.0000','','','','',0,'','','','20171114132501','','天津力生制药股份有限公司'),('171118104957051',174,'011074',1,'437',NULL,1,0,402,'171118104856664','31F',NULL,6,'',1,'',700,NULL,0,1,'Y','','','10MG/片','','','AAD8229','20171201','','437',1,0,'20171118110958','u','','','#','0800','','20171114132539','122333641:140','1.0000','','','','',0,'','','','20171114132539','','上海施贵宝公司'),('171118104957051',174,'011663',1,'0',NULL,0.5,0,403,'171118104856664','',NULL,7,'',1,'8',1000,NULL,0,1,'Y','','','20MG/片','','','','','','M',0.5,0,'20171118110959','u','','','#','0800','','','122333641:141','0.5000','','','','',0,'','','','','',''),('171118104957051',174,'011687',1,'404',NULL,1,0,404,'171118104856664','63F',NULL,9,'',1,'',1000,NULL,0,1,'Y','',''
[Err] 2006 - MySQL server has gone away
[Err] insert  into `dispensing_pack_tablet_hist`(`dsps_num`,`pack_num`,`tblt_cd`,`atm_num`,`cst_num`,`alt_cst_num`,`tblt_qtt`,`fsp_num`,`pack_tblt_num`,`rcpt_num`,`cst_pos`,`alt_cst_pos`,`tblt_seq_num`,`drts_cd`,`day_take_cnt`,`utc_num`,`delay_time`,`alt_delay_time`,`envelope_num`,`take_days`,`dsps_tgt_yn`,`tblt_shape`,`tblt_color`,`tblt_spec`,`tblt_note`,`tblt_barcode`,`tblt_lot_num`,`tblt_exp_date`,`user_exp_date`,`dev_id`,`dsps_tblt_qty`,`pwd_num`,`updt_dtm`,`updt_user_id`,`hspt_tblt_lot_num`,`hspt_tblt_exp_date`,`take_time_cd`,`take_tp_cd`,`take_tp_note`,`sup_dtm`,`ocs_order_tblt_key`,`take_tp_dose`,`tblt_note2`,`tblt_note3`,`tblt_note4`,`sig_lines`,`return_tblt_qty`,`rx_no`,`rx_doctor_nm`,`sup_user_id`,`sup_cfrm_dtm`,`sup_cfrm_user_id`,`tblt_maker`) values ('171125140956058',29,'012510',1,'444',NULL,2,0,70,'171125140815780','22F',NULL,4,'',1,'',550,NULL,0,1,'Y','','','20MG/片','','','','22220601','','444',2,0,'20171125141520','u','','','#','0800','','20171125131636','122942200:43','2.0000','','','','',0,'','','','20171125131636','','上海朝晖制药'),('171125140956058',29,'011382',1,'0',NULL,0.75,0,71,'171125140815780','',NULL,12,'',1,'5',1000,NULL,0,1,'Y','','','50ΜG/片','','','','','','M',0.75,0,'20171125141524','u','','','#','0800','','','122942200:51','0.7500','','','','',0,'','','','','',''),('171125140956058',29,'011804',1,'38',NULL,4,0,72,'171125140815780','62R',NULL,14,'',1,'',850,NULL,0,1,'Y','','','0.63G/片','','','','22220601','','38',4,0,'20171125141520','u','','','#','0800','','20171124134554','122942200:53','4.0000','','','','',0,'','','','20171124134554','','北京费森尤斯卡比医药有限公司'),('171125140956058',29,'011104',1,'173',NULL,1,0,73,'171125140815780','41R',NULL,16,'',1,'',650,NULL,0,1,'Y','','','92.5MG/片','','','T7042','20171130','','173',1,0,'20171125141520','u','','','#','0800','','20171120134921','122942200:55','1.0000','','','','',0,'','','','20171120134921','','瑞士诺华制药有限公司Novartis　Pharma'),('171125140956058',29,'012092',1,'422',NULL,1,0,74,'171125140815780','43F',NULL,18,'',1,'',800,NULL,0,1,'Y','','','20MG/片','','','151003','20180516','','422',1,0,'20171125141520','u','','','#','0800','','20171120135116','122942200:57','1.0000','','','','',0,'','','','20171120135116','','江苏吉贝尔药业'),('171125140956058',30,'011804',1,'38',NULL,4,0,75,'171125140815780','62R',NULL,6,'',1,'',850,NULL,0,1,'Y','','','0.63G/片','','','','22220601','','38',4,0,'20171125141524','u','','','#','1200','','20171124134554','122942200:45','4.0000','','','','',0,'','','','20171124134554','','北京费森尤斯卡比医药有限公司'),('171125140956058',30,'012092',1,'422',NULL,1,0,76,'171125140815780','43F',NULL,8,'',1,'',800,NULL,0,1,'Y','','','20MG/片','','','151003','20180516','','422',1,0,'20171125141524','u','','','#','1200','','20171120135116','122942200:47','1.0000','','','','',0,'','','','20171120135116','','江苏吉贝尔药业'),('171125140956058',30,'012510',1,'444',NULL,2,0,77,'171125140815780','22F',NULL,13,'',1,'',550,NULL,0,1,'Y','','','20MG/片','','','','22220601','','444',2,0,'20171125141524','u','','','#','1200','','20171125131636','122942200:52','2.0000','','','','',0,'','','','20171125131636','','上海朝晖制药'),('171125140956058',31,'011315',1,'433',NULL,1,0,78,'171125140815780','33F',NULL,15,'3餐时嚼服',1,'',700,NULL,0,1,'Y','','','50MG/片','','','','20190228','','433',1,0,'20171125141526','u','','','','1600','','20171124134638','122942200:54','1.0000','','','','',0,'','','','20171124134638','','杭州中美华东制药有限公司'),('171125140956058',32,'011315',1,'433',NULL,1,0,79,'171125140815780','33F',NULL,11,'3餐时嚼服',1,'',700,NULL,0,1,'Y','','','50MG/片','','','','20190228','','433',1,0,'20171125141527','u','','','','0800','','20171124134638','122942200:50','1.0000','','','','',0,'','','','20171124134638','','杭州中美华东制药有限公司'),('171125140956058',33,'011315',1,'433',NULL,1,0,80,'171125140815780','33F',NULL,5,'3餐时嚼服',1,'',700,NULL,0,1,'Y','','','50MG/片','','','','20190228','','433',1,0,'20171125141529','u','','','','1200','','20171124134638','122942200:44','1.0000','','','','',0,'','','','20171124134638','','杭州中美华东制药有限公司'),('171125142219059',10,'011774',1,'176',NULL,1,0,17,'171125131051755','41R',NULL,1,''

报错2

[Err] 1153 - Got a packet bigger than 'max_allowed_packet' bytes
[Err] /*
SQLyog Ultimate v8.4 RC
MySQL - 5.1.54-community : Database - atms
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`atms` /*!40100 DEFAULT CHARACTER SET gbk */;

USE `atms`;

/*Table structure for table `alarm_hist` */

DROP TABLE IF EXISTS `alarm_hist`;

CREATE TABLE `alarm_hist` (
  `atm_num` int(11) NOT NULL DEFAULT '0',
  `alarm_dtm` varchar(14) NOT NULL DEFAULT '',
  `alarm_key` varchar(20) NOT NULL DEFAULT '',
  `alarm_style` varchar(10) DEFAULT NULL,
  `alarm_cd` varchar(10) DEFAULT NULL,
  `alarm_msg` varchar(250) DEFAULT NULL,
  `alarm_onoff` varchar(1) DEFAULT NULL,
  `dev_addr` varchar(20) DEFAULT NULL,
  `rcpt_num` varchar(50) DEFAULT NULL,
  `dsps_num` varchar(20) DEFAULT NULL,
  `pack_idx` int(11) DEFAULT NULL,
  `tblt_cd` varchar(50) DEFAULT NULL,
  `mnt_send_yn` varchar(1) DEFAULT 'N',
  `mnt_send_dtm` varchar(14) DEFAULT NULL,
  PRIMARY KEY (`atm_num`,`alarm_dtm`,`alarm_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `alarm_hist` */

二、分析

根据“报错2”可以知道导入的包"*.sql"文件时，由于文件中单个语包的大小大于 “max_allowed_packet”所以报错，因此只需要将max_allowed_packet 这全局变量设置合适就可以了，如果您不知道sql文件中当个语句包的大小，建议您设置的大小大于文件大小即可。

如我的文件大小是 [766 MB (803,415,614 字节)] ，那么就将 max_allowed_packet 设置为800MB，也就是：

$800 (MB) = 1024 \times 1024 \times 800 (bytes)$

注：max_allowed_packet 设置过大可能会导致服务器响应不及时，甚至在网络环境恶劣的情况及其容易发生丢包现象。建议只在导入大批量数据的时候临时设置。

max_allowed_packet默认值为 1MB ,也就是：

1(MB) =1024(KB) =1,048,576 (bytes)

三、解决方案

第一步、查询现在的值大小，并将参数保存到查询窗口或记事本中。

SHOW VARIABLES LIKE '%max_allowed_packet%';

第二步、根据自己的实际情况设置合适的大小。

SET GLOBAL max_allowed_packet = 1024 * 1024 * 800;

第三步、使用第一步的数据，查看是否设置成功。

第四步、运行SQL文件，导入数据。

第五步、使用第二步的语句还原max_allowed_packet。

少莫千华

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录