mysql> show processlist; +----+------+----------------+--------------------+---------+------+-------+---- --------------+ | Id | User | Host | db | Command | Time | State | Inf o | +----+------+----------------+--------------------+---------+------+-------+---- --------------+ | 2 | root | localhost:2284 | gamerecord-chn1030 | Sleep | 188 | | NUL L | | 3 | root | localhost:2285 | NULL | Sleep | 568 | | NUL L | | 6 | root | localhost:2290 | gamerecord-chn1030 | Sleep | 514 | | NUL L | | 7 | root | localhost:2572 | NULL | Query | 0 | NULL | sho w processlist | +----+------+----------------+--------------------+---------+------+-------+----
my.ini文件里面加了max_allowed_packet=600Mshow variables like 'max_allowed_packet'用编辑器看看。sql的内容是什么
show variables like 'max_allowed_packet' +--------------------+-----------+ | Variable_name | Value | +--------------------+-----------+ | max_allowed_packet | 629145600 | +--------------------+-----------+ 1 row in set (0.00 sec)已经生效了,但是为什么就是不行呢,sql文件的内容就是一个insert语句,批量插入数据,数据比较多
我已经执行完成了 在日志文件里面看到error日志提示是这样的 110415 9:45:36 [ERROR] E:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Out of memory (Needed 1992016 bytes) 110415 9:45:36 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
打开另一个MYSQL连接,看看当前的processlist
mysql> show processlist;
+----+------+----------------+--------------------+---------+------+-------+----
--------------+
| Id | User | Host | db | Command | Time | State | Inf
o |
+----+------+----------------+--------------------+---------+------+-------+----
--------------+
| 2 | root | localhost:2284 | gamerecord-chn1030 | Sleep | 188 | | NUL
L |
| 3 | root | localhost:2285 | NULL | Sleep | 568 | | NUL
L |
| 6 | root | localhost:2290 | gamerecord-chn1030 | Sleep | 514 | | NUL
L |
| 7 | root | localhost:2572 | NULL | Query | 0 | NULL | sho
w processlist |
+----+------+----------------+--------------------+---------+------+-------+----
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| max_allowed_packet | 629145600 |
+--------------------+-----------+
1 row in set (0.00 sec)已经生效了,但是为什么就是不行呢,sql文件的内容就是一个insert语句,批量插入数据,数据比较多
insert into itemrecord_5(Time,PlayerA,Type,ScriptID,MapA,MapAx,MapAy,PlayerLevel,GetExp,PlayerExp,PlayerMaxExp,NpcTypeID) values
("2011-3-29 17:54:40",8631790509,5,5,352,1028,1127,125,6194,1181892640,1350000000,4435),
("2011-3-29 15:2:54",47507717618,5,5,12,12312,3228,86,63,53101780,64000000,4140),
("2011-3-29 14:48:37",60449502696,5,5,22,5080,6055,104,2300,94621002,230000000,4304),
("2011-3-29 22:17:35",4307438554,5,5,12,11360,11533,78,42,6954105,34000000,4126),
("2011-3-29 9:16:55",86421393336,5,5,13,5326,5237,90,70,71029148,82000000,4226),
("2011-3-29 23:57:56",4307247342,5,5,12,5064,8705,84,52,2924052,56000000,4127),
("2011-3-29 7:51:9",4307438554,5,5,12,11547,11670,78,42,5763594,34000000,4126),
("2011-3-29 22:56:13",69080810480,5,5,26,2167,1088,126,4332,1242395430,1470000000,4383),
("2011-3-29 3:51:28",4307247342,5,5,12,5064,8812,84,52,1088456,56000000,4127),
("2011-3-29 22:50:40",69080810480,5,5,26,2251,1117,126,4332,1241592621,1470000000,4383),
("2011-3-29 11:42:55",56147847144,5,5,14,3331,1277,78,500,24827096,34000000,4228),
("2011-3-29 17:15:44",47507720342,5,5,12,12243,3450,86,63,51651660,64000000,4132),
("2011-3-29 14:43:26",4295032890,5,5,13,5480,3348,88,60,58418940,72000000,4226),
("2011-3-29 17:41:5",86421378305,5,5,14,1485,2941,92,70,20697541,94000000,4225),
("2011-3-29 13:48:50",47507717618,5,5,12,12136,3356,86,63,52927422,64000000,4132),
("2011-3-29 17:31:3",86421378305,5,5,14,1076,3019,92,82,20681303,94000000,4225),
("2011-3-29 21:27:21",99374730180,5,5,26,5829,2538,127,3932,1284453278,1590000000,4383),
("2011-3-29 20:6:37",4295013659,5,5,13,4920,3796,88,70,68598067,72000000,4226),
("2011-3-29 22:35:57",86421378305,5,5,14,1252,2968,92,414,21210040,94000000,4225),
("2011-3-29 11:50:39",108062031492,5,5,17,8798,3137,93,300,31264527,100000000,4235),
("2011-3-29 8:38:39",4295032890,5,5,13,4997,3523,88,70,57863817,72000000,4226),
("2011-3-29 18:37:55",86422143767,5,5,13,4986,4895,90,60,56839521,82000000,4226),
("2011-3-29 3:4:22",17237246542,5,5,308,1040,1077,114,3230,262705176,580000000,4377),
("2011-3-29 13:2:20",4295019008,5,5,13,4684,3438,88,60,46498406,72000000,4226),
("2011-3-29 2:9:50",99374730180,5,5,26,5136,2220,127,3932,1275949796,1590000000,4383),
("2011-3-29 9:16:58",47507720342,5,5,12,12394,3365,86,63,50558914,64000000,4132),
("2011-3-29 15:17:28",4295267863,5,5,12,10491,879,88,63,22698116,72000000,4132),
("2011-3-29 20:51:42",103726644678,5,5,13,5599,5033,90,60,27045128,82000000,4226),
("2011-3-29 0:20:2",4307437863,5,5,12,11704,11518,83,42,13933708,52000000,4126),
("2011-3-29 9:24:49",86421393336,5,5,13,5800,5396,90,87,71042135,82000000,4226),
("2011-3-29 6:54:7",4307249738,5,5,12,5064,8652,83,52,23891086,52000000,4138),
("2011-3-29 3:1:50",86421393931,5,5,13,5336,5496,90,70,61775766,82000000,4226),
("2011-3-29 0:3:46",47504635734,5,5,13,9136,3377,88,62,64719617,72000000,4227),
("2011-3-29 14:15:49",51815203938,5,5,24,4746,5276,119,1769,562078656,830000000,4336),
("2011-3-29 19:35:40",4307248544,5,5,12,4981,8897,83,52,37743769,52000000,4138),
("2011-3-29 23:56:47",56128945985,5,5,19,8812,2238,91,474,52555963,88000000,4271),
("2011-3-29 10:33:15",56139983951,5,5,18,5133,4127,83,656,45015586,52000000,4236),
("2011-3-29 19:27:24",4307345229,5,5,12,11709,11737,83,52,13743393,52000000,4126),
("2011-3-29 9:45:11",4307247342,5,5,12,4861,8870,84,52,1557830,56000000,4127),
("2011-3-29 14:34:50",47504626603,5,5,14,3189,3743,91,70,52393055,88000000,4225),
("2011-3-29 0:33:20",112368188870,5,5,352,4229,1848,126,4129,498408392,1470000000,4435),
("2011-3-29 9:40:53",4307345229,5,5,12,11725,11267,83,42,12929404,52000000,4126),
("2011-3-29 17:39:9",17237246542,5,5,23,6262,7858,114,2860,287169730,580000000,4333),
("2011-3-29 14:51:37",99374730180,5,5,26,5241,2362,127,3932,1278688966,1590000000,4383),
("2011-3-29 16:11:44",4295267863,5,5,12,10894,877,88,63,22800654,72000000,4132),
("2011-3-29 8:31:38",47504628791,5,5,13,8181,3229,91,62,34983481,88000000,4227),
("2011-3-29 19:11:0",108067999319,5,5,13,11367,4353,90,62,37290280,82000000,4227),
("2011-3-29 9:45:13",108063303476,5,5,13,11209,4119,91,62,87407924,88000000,4227),
sql文件应该是没有问题的,我是用批处理生成的这个sql文件,文件小的时候是可以导进去的,几十M的都可以导进去,不知道这个一百多M就导不进去了,一直停在那里没动
| 6 | root | localhost:2290 | gamerecord-chn1030 | Sleep |……
是这个进程
说明一个语句都没有提交。 最大的可能是你的文件中的语句没有结束。 比如 引号的不匹配等等原因。 你不能指望我能猜出准确的问题。建议你打开这个SQL文件后,一句一句直接贴到MYSQL中执行,然后就可以看到什么问题了。use gamerecord-chn1030;这句本身就有问题,你自己试一下。
sql文件中的语句是绝对没有问题的,语句已经结束了,我把文件的后半部分删除,只留前半部分,大概有六十多M是可以插入进去的,这插入数据的过程是不是先要把文件读到内存中然后再插入呢?应该是某个设置给限制了读不了那么大的问题,我个人认为!
`Id` int(11) NOT NULL auto_increment,
`Time` datetime NOT NULL default '1900-01-01 00:00:00',
`PlayerA` bigint(20) NOT NULL default '0',
`Type` int(1) NOT NULL default '0',
`ScriptID` int(11) default NULL,
`MapA` int(11) default NULL,
`MapAx` int(11) default NULL,
`MapAy` int(11) default NULL,
`PlayerLevel` int(11) default NULL,
`GetExp` bigint(20) default NULL,
`PlayerExp` bigint(20) default NULL,
`PlayerMaxExp` bigint(20) default NULL,
`NpcTypeID` int(11) default NULL,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=143035528 DEFAULT CHARSET=utf8;
这是表的格式,你可以建一个把我上面发上来的内容复制到一百M试试
我已经执行完成了 在日志文件里面看到error日志提示是这样的
110415 9:45:36 [ERROR] E:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Out of memory (Needed 1992016 bytes)
110415 9:45:36 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
INSERT INTO tab (....)
VALUES
(line1....),
(line2....),
(line3....),
......
(line9999999....);
啊,建议分成每100个一句insert
就1个SQL写入数据数据,当然大了
对的,我就是这样插入数据的,下面数据太多了 每一百行写一个insert?这样就可以?
用64位os+64位mysql导入,然后再把表文件复制出来。
把你的sql文件压缩了,也就30多M,传给我们这些dba,让他们用公司服务器帮你干活~~也就几分钟。
全都是最简单直接能解决问题的方案啊~~~
set names 'xxx' ; //你文件的编码source /xxx/xx.sql;我的2G多,都没有问题!有问题会给你提示消息
我是用脚本导入,脚本用不了mysql命令,只能用mysql命令导入sql文件
有什么不同吗?我上面的是直接中登录mysql后在命令行的
你也可以写在程序代码中
phpmysql_query('xxxx');
mysql_query('ssxxxx');一样执行
批处理用source这命令怎么用?
我现在用的就是用mysql -uroot -p123456<***\***.sql
我用批处理只会这么导,还有没有别的更好的办法,
你400M是一句SQL,程序需要一口气全部读入内存,并解析,开销太大,所以分成100行一个INSERT。
这就好比你搬家,你现在的家当太多,如果一次性搬的话,你需要一个集装箱,可是你的小区根本进不了那么大的车子,而且你的财力也只够雇个2吨的小卡车,那你只能分批一批批来