Hi,
When I run multiple insert statements in a query tab (in "Send batch in one go" mode) it misses some inserts. There is no error. But when I query the table, it hasn't some rows.
Table source:
CREATE TABLE `Exp_Paises` (
`IdPais` INT(11) NOT NULL AUTO_INCREMENT,
`Descripcion` VARCHAR(255) NOT NULL,
`Activo` TINYINT(4) NOT NULL,
`IdAfip` INT(11) NOT NULL,
PRIMARY KEY (`IdPais`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
SQL Script:
http://pastebin.com/bcQYduiV
The results:
mysql> select * from Exp_Paises;
[...]
218 rows in set (0.00 sec)
mysql> select * from Exp_Paises where IdPais = 4;
Empty set (0.00 sec)
(There is a IdPais = 4 in the script)
Problem running multiple INSERT in a script
I just tested your script, once with "Send queries one by one" and once with "Send batch in one go". Both times I get 256 rows, veryfied with a SELECT * FROM... to overcome InnoDB row num estimation. In both cases I have the IdPais "4" in that table.
The only noticable thing is that in "Send batch in one go", after each batch query I am disconnected once for some reason, and HeidiSQL reconnects automatically.
The only noticable thing is that in "Send batch in one go", after each batch query I am disconnected once for some reason, and HeidiSQL reconnects automatically.
In "Send queries one by one" mode there is no problem. It only fails in "Send batch in one go"
Here is the log output:
http://pastebin.com/pLPQGEf9
I created another database and table (same ddl) and try to load the smae data, with the same result.
I noticed it was missing again then same record (IdPais 4)
then executed:
/* Affected rows: 0 Found rows: 251 Warnings: 0 Duration for 1 query: 0,172 sec. */
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 1 query: 0,172 sec. */
I see a SHOW max_allowed_packet in the log, this is what it shows my var:
max_allowed_packet 20971520
Server version is: mysql-server-5.1.61-4.el6.x86_64 (Centos 6)
HeidiSQL runs on: Windows vista SP2 (x86)
Here is the log output:
http://pastebin.com/pLPQGEf9
I created another database and table (same ddl) and try to load the smae data, with the same result.
I noticed it was missing again then same record (IdPais 4)
then executed:
select * from Exp_Paises;
/* Affected rows: 0 Found rows: 251 Warnings: 0 Duration for 1 query: 0,172 sec. */
select * from Exp_Paises
where IdPais = 4;
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 1 query: 0,172 sec. */
I see a SHOW max_allowed_packet in the log, this is what it shows my var:
max_allowed_packet 20971520
Server version is: mysql-server-5.1.61-4.el6.x86_64 (Centos 6)
HeidiSQL runs on: Windows vista SP2 (x86)
This is the full table data. Maybe it can help you
"IdPais" "Descripcion" "Activo" "IdAfip"
"1" "BURKINA FASO" "1" "101"
"2" "ARGELIA" "1" "102"
"3" "BOTSWANA" "1" "103"
"5" "CAMERUN" "1" "105"
"6" "REP. CENTROAFRICANA." "1" "107"
"7" "CONGO" "1" "108"
"8" "REP.DEMOCRAT.DEL CONGO EX ZAIRE" "1" "109"
"9" "COSTA DE MARFIL" "1" "110"
"10" "CHAD" "1" "111"
"11" "BENIN" "1" "112"
"12" "EGIPTO" "1" "113"
"13" "GABON" "1" "115"
"14" "GAMBIA" "1" "116"
"15" "GHANA" "1" "117"
"16" "GUINEA" "1" "118"
"17" "GUINEA ECUATORIAL" "1" "119"
"18" "KENYA" "1" "120"
"19" "LESOTHO" "1" "121"
"20" "LIBERIA" "1" "122"
"21" "LIBIA" "1" "123"
"22" "MADAGASCAR" "1" "124"
"23" "MALAWI" "1" "125"
"24" "MALI" "1" "126"
"25" "MARRUECOS" "1" "127"
"26" "MAURICIO,ISLAS" "1" "128"
"27" "MAURITANIA" "1" "129"
"28" "NIGER" "1" "130"
"29" "NIGERIA" "1" "131"
"30" "ZIMBABWE" "1" "132"
"31" "RWANDA" "1" "133"
"32" "SENEGAL" "1" "134"
"33" "SIERRA LEONA" "1" "135"
"34" "SOMALIA" "1" "136"
"35" "SWAZILANDIA" "1" "137"
"36" "SUDAN" "1" "138"
"37" "TANZANIA" "1" "139"
"38" "TOGO" "1" "140"
"39" "TUNEZ" "1" "141"
"40" "UGANDA" "1" "142"
"41" "ZAMBIA" "1" "144"
"42" "TERRIT.VINCULADOS AL R UNIDO" "1" "145"
"43" "TERRIT.VINCULADOS A ESPAÑA" "1" "146"
"44" "TERRIT.VINCULADOS A FRANCIA" "1" "147"
"45" "ANGOLA" "1" "149"
"46" "CABO VERDE" "1" "150"
"47" "MOZAMBIQUE" "1" "151"
"48" "SEYCHELLES" "1" "152"
"49" "DJIBOUTI" "1" "153"
"50" "COMORAS" "1" "155"
"51" "GUINEA BISSAU" "1" "156"
"52" "STO.TOME Y PRINCIPE" "1" "157"
"53" "NAMIBIA" "1" "158"
"55" "ERITREA" "1" "160"
"56" "ETIOPIA" "1" "161"
"57" "RESTO (AFRICA)" "1" "197"
"58" "INDETERMINADO (AFRICA)" "1" "198"
"59" "ARGENTINA" "1" "200"
"60" "BARBADOS" "1" "201"
"61" "BOLIVIA" "1" "202"
"62" "BRASIL" "1" "203"
"63" "CANADA" "1" "204"
"64" "COLOMBIA" "1" "205"
"65" "COSTA RICA" "1" "206"
"66" "CUBA" "1" "207"
"67" "CHILE" "1" "208"
"68" "REPÚBLICA DOMINICANA" "1" "209"
"69" "ECUADOR" "1" "210"
"70" "EL SALVADOR" "1" "211"
"71" "ESTADOS UNIDOS" "1" "212"
"72" "GUATEMALA" "1" "213"
"73" "GUYANA" "1" "214"
"74" "HAITI" "1" "215"
"75" "HONDURAS" "1" "216"
"76" "JAMAICA" "1" "217"
"77" "MEXICO" "1" "218"
"78" "NICARAGUA" "1" "219"
"79" "PANAMA" "1" "220"
"80" "PARAGUAY" "1" "221"
"81" "PERU" "1" "222"
"82" "PUERTO RICO" "1" "223"
"83" "TRINIDAD Y TOBAGO" "1" "224"
"84" "URUGUAY" "1" "225"
"85" "VENEZUELA" "1" "226"
"86" "TERRIT.VINCULADO AL R.UNIDO" "1" "227"
"87" "TER.VINCULADOS A DINAMARCA" "1" "228"
"88" "TERRIT.VINCULADOS A FRANCIA AMERIC." "1" "229"
"89" "TERRIT. HOLANDESES" "1" "230"
"90" "TER.VINCULADOS A ESTADOS UNIDOS" "1" "231"
"91" "SURINAME" "1" "232"
"92" "DOMINICA" "1" "233"
"93" "SANTA LUCIA" "1" "234"
"94" "SAN VICENTE Y LAS GRANADINAS" "1" "235"
"95" "BELICE" "1" "236"
"96" "ANTIGUA Y BARBUDA" "1" "237"
"97" "S.CRISTOBAL Y NEVIS" "1" "238"
"98" "BAHAMAS" "1" "239"
"99" "GRENADA" "1" "240"
"100" "ANTILLAS HOLANDESAS" "1" "241"
"101" "ARUBA" "1" "242"
"102" "AAE Tierra del Fuego - ARGENTINA" "1" "250"
"104" "ZF Justo Daract - ARGENTINA" "1" "252"
"105" "ZF Río Gallegos - ARGENTINA" "1" "253"
"106" "Islas Malvinas - ARGENTINA" "1" "254"
"107" "ZF Tucumán - ARGENTINA" "1" "255"
"108" "ZF Córdoba - ARGENTINA" "1" "256"
"109" "ZF Mendoza - ARGENTINA" "1" "257"
"110" "ZF General Pico - ARGENTINA" "1" "258"
"111" "ZF Comodoro Rivadavia - ARGENTINA" "1" "259"
"112" "ZF Iquique" "1" "260"
"113" "ZF Punta Arenas" "1" "261"
"114" "ZF Salta - ARGENTINA" "1" "262"
"115" "ZF Paso de los Libres - ARGENTINA" "1" "263"
"116" "ZF Puerto Iguazú - ARGENTINA" "1" "264"
"117" "SECTOR ANTARTICO ARG." "1" "265"
"118" "ZF Colón - REPÚBLICA DE PANAMÁ" "1" "270"
"119" "ZF Winner (Sta. C. de la Sierra) - BOLIVIA" "1" "271"
"120" "ZF Colonia - URUGUAY" "1" "280"
"121" "ZF Florida - URUGUAY" "1" "281"
"122" "ZF Libertad - URUGUAY" "1" "282"
"123" "ZF Zonamerica - URUGUAY" "1" "283"
"124" "ZF Nueva Helvecia - URUGUAY" "1" "284"
"125" "ZF Nueva Palmira - URUGUAY" "1" "285"
"126" "ZF Río Negro - URUGUAY" "1" "286"
"127" "ZF Rivera - URUGUAY" "1" "287"
"128" "ZF San José - URUGUAY" "1" "288"
"129" "ZF Manaos - BRASIL" "1" "291"
"130" "MAR ARG ZONA ECO.EX" "1" "295"
"131" "RIOS ARG NAVEG INTER" "1" "296"
"132" "RESTO AMERICA" "1" "297"
"133" "INDETERMINADO (AMERICA)" "1" "298"
"134" "AFGANISTAN" "1" "301"
"135" "ARABIA SAUDITA" "1" "302"
"136" "BAHREIN" "1" "303"
"137" "MYANMAR (EX-BIRMANIA)" "1" "304"
"138" "BUTAN" "1" "305"
"139" "CAMBODYA (EX-KAMPUCHE)" "1" "306"
"140" "SRI LANKA" "1" "307"
"141" "COREA DEMOCRATICA" "1" "308"
"142" "COREA REPUBLICANA" "1" "309"
"143" "CHINA" "1" "310"
"144" "FILIPINAS" "1" "312"
"145" "TAIWAN" "1" "313"
"146" "INDIA" "1" "315"
"147" "INDONESIA" "1" "316"
"148" "IRAK" "1" "317"
"149" "IRAN" "1" "318"
"150" "ISRAEL" "1" "319"
"151" "JAPON" "1" "320"
"152" "JORDANIA" "1" "321"
"153" "QATAR" "1" "322"
"155" "LAOS" "1" "324"
"156" "LIBANO" "1" "325"
"157" "MALASIA" "1" "326"
"158" "MALDIVAS ISLAS" "1" "327"
"159" "OMAN" "1" "328"
"160" "MONGOLIA" "1" "329"
"161" "NEPAL" "1" "330"
"162" "EMIRATOS ARABES UNIDOS" "1" "331"
"163" "PAKISTÁN" "1" "332"
"164" "SINGAPUR" "1" "333"
"165" "SIRIA" "1" "334"
"166" "THAILANDIA" "1" "335"
"167" "VIETNAM" "1" "337"
"168" "HONG KONG" "1" "341"
"169" "MACAO" "1" "344"
"170" "BANGLADESH" "1" "345"
"171" "BRUNEI" "1" "346"
"172" "REPUBLICA DE YEMEN" "1" "348"
"173" "ARMENIA" "1" "349"
"174" "AZERBAIJAN" "1" "350"
"175" "GEORGIA" "1" "351"
"176" "KAZAJSTAN" "1" "352"
"177" "KIRGUIZISTAN" "1" "353"
"178" "TAYIKISTAN" "1" "354"
"179" "TURKMENISTAN" "1" "355"
"180" "UZBEKISTAN" "1" "356"
"181" "TERR. AU. PALESTINOS" "1" "357"
"182" "RESTO DE ASIA" "1" "397"
"183" "INDET.(ASIA)" "1" "398"
"184" "ALBANIA" "1" "401"
"185" "ANDORRA" "1" "404"
"186" "AUSTRIA" "1" "405"
"187" "BELGICA" "1" "406"
"188" "BULGARIA" "1" "407"
"189" "DINAMARCA" "1" "409"
"190" "ESPAÑA" "1" "410"
"191" "FINLANDIA" "1" "411"
"192" "FRANCIA" "1" "412"
"193" "GRECIA" "1" "413"
"194" "HUNGRIA" "1" "414"
"195" "IRLANDA" "1" "415"
"196" "ISLANDIA" "1" "416"
"197" "ITALIA" "1" "417"
"198" "LIECHTENSTEIN" "1" "418"
"199" "LUXEMBURGO" "1" "419"
"200" "MALTA" "1" "420"
"201" "MONACO" "1" "421"
"202" "NORUEGA" "1" "422"
"203" "PAISES BAJOS" "1" "423"
"205" "PORTUGAL" "1" "425"
"206" "REINO UNIDO" "1" "426"
"207" "RUMANIA" "1" "427"
"208" "SAN MARINO" "1" "428"
"209" "SUECIA" "1" "429"
"210" "SUIZA" "1" "430"
"211" "VATICANO(SANTA SEDE)" "1" "431"
"212" "POS.BRIT.(EUROPA)" "1" "433"
"213" "CHIPRE" "1" "435"
"214" "TURQUIA" "1" "436"
"215" "ALEMANIA,REP.FED." "1" "438"
"216" "BIELORRUSIA" "1" "439"
"217" "ESTONIA" "1" "440"
"218" "LETONIA" "1" "441"
"219" "LITUANIA" "1" "442"
"220" "MOLDAVIA" "1" "443"
"221" "RUSIA" "1" "444"
"222" "UCRANIA" "1" "445"
"223" "BOSNIA HERZEGOVINA" "1" "446"
"224" "CROACIA" "1" "447"
"225" "ESLOVAQUIA" "1" "448"
"226" "ESLOVENIA" "1" "449"
"227" "MACEDONIA" "1" "450"
"228" "REP. CHECA" "1" "451"
"229" "MONTENEGRO" "1" "453"
"230" "SERBIA" "1" "454"
"231" "RESTO EUROPA" "1" "497"
"232" "INDET.(EUROPA)" "1" "498"
"233" "AUSTRALIA" "1" "501"
"234" "NAURU" "1" "503"
"235" "NUEVA ZELANDIA" "1" "504"
"236" "VANATU" "1" "505"
"237" "SAMOA OCCIDENTAL" "1" "506"
"238" "TERRITORIO VINCULADOS A AUSTRALIA" "1" "507"
"239" "TERRITORIOS VINCULADOS AL R. UNIDO" "1" "508"
"240" "TERRITORIOS VINCULADOS A FRANCIA" "1" "509"
"241" "TER VINCULADOS A NUEVA. ZELANDA" "1" "510"
"242" "TER. VINCULADOS A ESTADOS UNIDOS" "1" "511"
"243" "FIJI, ISLAS" "1" "512"
"244" "PAPUA NUEVA GUINEA" "1" "513"
"245" "KIRIBATI, ISLAS" "1" "514"
"246" "MICRONESIA,EST.FEDER" "1" "515"
"247" "PALAU" "1" "516"
"248" "TUVALU" "1" "517"
"249" "SALOMON,ISLAS" "1" "518"
"250" "TONGA" "1" "519"
"251" "MARSHALL,ISLAS" "1" "520"
"252" "MARIANAS,ISLAS" "1" "521"
"253" "RESTO OCEANIA" "1" "597"
"254" "INDET.(OCEANIA)" "1" "598"
"255" "RESTO CONTINENTE" "1" "997"
"256" "INDET.(CONTINENTE)" "1" "998"
"IdPais" "Descripcion" "Activo" "IdAfip"
"1" "BURKINA FASO" "1" "101"
"2" "ARGELIA" "1" "102"
"3" "BOTSWANA" "1" "103"
"5" "CAMERUN" "1" "105"
"6" "REP. CENTROAFRICANA." "1" "107"
"7" "CONGO" "1" "108"
"8" "REP.DEMOCRAT.DEL CONGO EX ZAIRE" "1" "109"
"9" "COSTA DE MARFIL" "1" "110"
"10" "CHAD" "1" "111"
"11" "BENIN" "1" "112"
"12" "EGIPTO" "1" "113"
"13" "GABON" "1" "115"
"14" "GAMBIA" "1" "116"
"15" "GHANA" "1" "117"
"16" "GUINEA" "1" "118"
"17" "GUINEA ECUATORIAL" "1" "119"
"18" "KENYA" "1" "120"
"19" "LESOTHO" "1" "121"
"20" "LIBERIA" "1" "122"
"21" "LIBIA" "1" "123"
"22" "MADAGASCAR" "1" "124"
"23" "MALAWI" "1" "125"
"24" "MALI" "1" "126"
"25" "MARRUECOS" "1" "127"
"26" "MAURICIO,ISLAS" "1" "128"
"27" "MAURITANIA" "1" "129"
"28" "NIGER" "1" "130"
"29" "NIGERIA" "1" "131"
"30" "ZIMBABWE" "1" "132"
"31" "RWANDA" "1" "133"
"32" "SENEGAL" "1" "134"
"33" "SIERRA LEONA" "1" "135"
"34" "SOMALIA" "1" "136"
"35" "SWAZILANDIA" "1" "137"
"36" "SUDAN" "1" "138"
"37" "TANZANIA" "1" "139"
"38" "TOGO" "1" "140"
"39" "TUNEZ" "1" "141"
"40" "UGANDA" "1" "142"
"41" "ZAMBIA" "1" "144"
"42" "TERRIT.VINCULADOS AL R UNIDO" "1" "145"
"43" "TERRIT.VINCULADOS A ESPAÑA" "1" "146"
"44" "TERRIT.VINCULADOS A FRANCIA" "1" "147"
"45" "ANGOLA" "1" "149"
"46" "CABO VERDE" "1" "150"
"47" "MOZAMBIQUE" "1" "151"
"48" "SEYCHELLES" "1" "152"
"49" "DJIBOUTI" "1" "153"
"50" "COMORAS" "1" "155"
"51" "GUINEA BISSAU" "1" "156"
"52" "STO.TOME Y PRINCIPE" "1" "157"
"53" "NAMIBIA" "1" "158"
"55" "ERITREA" "1" "160"
"56" "ETIOPIA" "1" "161"
"57" "RESTO (AFRICA)" "1" "197"
"58" "INDETERMINADO (AFRICA)" "1" "198"
"59" "ARGENTINA" "1" "200"
"60" "BARBADOS" "1" "201"
"61" "BOLIVIA" "1" "202"
"62" "BRASIL" "1" "203"
"63" "CANADA" "1" "204"
"64" "COLOMBIA" "1" "205"
"65" "COSTA RICA" "1" "206"
"66" "CUBA" "1" "207"
"67" "CHILE" "1" "208"
"68" "REPÚBLICA DOMINICANA" "1" "209"
"69" "ECUADOR" "1" "210"
"70" "EL SALVADOR" "1" "211"
"71" "ESTADOS UNIDOS" "1" "212"
"72" "GUATEMALA" "1" "213"
"73" "GUYANA" "1" "214"
"74" "HAITI" "1" "215"
"75" "HONDURAS" "1" "216"
"76" "JAMAICA" "1" "217"
"77" "MEXICO" "1" "218"
"78" "NICARAGUA" "1" "219"
"79" "PANAMA" "1" "220"
"80" "PARAGUAY" "1" "221"
"81" "PERU" "1" "222"
"82" "PUERTO RICO" "1" "223"
"83" "TRINIDAD Y TOBAGO" "1" "224"
"84" "URUGUAY" "1" "225"
"85" "VENEZUELA" "1" "226"
"86" "TERRIT.VINCULADO AL R.UNIDO" "1" "227"
"87" "TER.VINCULADOS A DINAMARCA" "1" "228"
"88" "TERRIT.VINCULADOS A FRANCIA AMERIC." "1" "229"
"89" "TERRIT. HOLANDESES" "1" "230"
"90" "TER.VINCULADOS A ESTADOS UNIDOS" "1" "231"
"91" "SURINAME" "1" "232"
"92" "DOMINICA" "1" "233"
"93" "SANTA LUCIA" "1" "234"
"94" "SAN VICENTE Y LAS GRANADINAS" "1" "235"
"95" "BELICE" "1" "236"
"96" "ANTIGUA Y BARBUDA" "1" "237"
"97" "S.CRISTOBAL Y NEVIS" "1" "238"
"98" "BAHAMAS" "1" "239"
"99" "GRENADA" "1" "240"
"100" "ANTILLAS HOLANDESAS" "1" "241"
"101" "ARUBA" "1" "242"
"102" "AAE Tierra del Fuego - ARGENTINA" "1" "250"
"104" "ZF Justo Daract - ARGENTINA" "1" "252"
"105" "ZF Río Gallegos - ARGENTINA" "1" "253"
"106" "Islas Malvinas - ARGENTINA" "1" "254"
"107" "ZF Tucumán - ARGENTINA" "1" "255"
"108" "ZF Córdoba - ARGENTINA" "1" "256"
"109" "ZF Mendoza - ARGENTINA" "1" "257"
"110" "ZF General Pico - ARGENTINA" "1" "258"
"111" "ZF Comodoro Rivadavia - ARGENTINA" "1" "259"
"112" "ZF Iquique" "1" "260"
"113" "ZF Punta Arenas" "1" "261"
"114" "ZF Salta - ARGENTINA" "1" "262"
"115" "ZF Paso de los Libres - ARGENTINA" "1" "263"
"116" "ZF Puerto Iguazú - ARGENTINA" "1" "264"
"117" "SECTOR ANTARTICO ARG." "1" "265"
"118" "ZF Colón - REPÚBLICA DE PANAMÁ" "1" "270"
"119" "ZF Winner (Sta. C. de la Sierra) - BOLIVIA" "1" "271"
"120" "ZF Colonia - URUGUAY" "1" "280"
"121" "ZF Florida - URUGUAY" "1" "281"
"122" "ZF Libertad - URUGUAY" "1" "282"
"123" "ZF Zonamerica - URUGUAY" "1" "283"
"124" "ZF Nueva Helvecia - URUGUAY" "1" "284"
"125" "ZF Nueva Palmira - URUGUAY" "1" "285"
"126" "ZF Río Negro - URUGUAY" "1" "286"
"127" "ZF Rivera - URUGUAY" "1" "287"
"128" "ZF San José - URUGUAY" "1" "288"
"129" "ZF Manaos - BRASIL" "1" "291"
"130" "MAR ARG ZONA ECO.EX" "1" "295"
"131" "RIOS ARG NAVEG INTER" "1" "296"
"132" "RESTO AMERICA" "1" "297"
"133" "INDETERMINADO (AMERICA)" "1" "298"
"134" "AFGANISTAN" "1" "301"
"135" "ARABIA SAUDITA" "1" "302"
"136" "BAHREIN" "1" "303"
"137" "MYANMAR (EX-BIRMANIA)" "1" "304"
"138" "BUTAN" "1" "305"
"139" "CAMBODYA (EX-KAMPUCHE)" "1" "306"
"140" "SRI LANKA" "1" "307"
"141" "COREA DEMOCRATICA" "1" "308"
"142" "COREA REPUBLICANA" "1" "309"
"143" "CHINA" "1" "310"
"144" "FILIPINAS" "1" "312"
"145" "TAIWAN" "1" "313"
"146" "INDIA" "1" "315"
"147" "INDONESIA" "1" "316"
"148" "IRAK" "1" "317"
"149" "IRAN" "1" "318"
"150" "ISRAEL" "1" "319"
"151" "JAPON" "1" "320"
"152" "JORDANIA" "1" "321"
"153" "QATAR" "1" "322"
"155" "LAOS" "1" "324"
"156" "LIBANO" "1" "325"
"157" "MALASIA" "1" "326"
"158" "MALDIVAS ISLAS" "1" "327"
"159" "OMAN" "1" "328"
"160" "MONGOLIA" "1" "329"
"161" "NEPAL" "1" "330"
"162" "EMIRATOS ARABES UNIDOS" "1" "331"
"163" "PAKISTÁN" "1" "332"
"164" "SINGAPUR" "1" "333"
"165" "SIRIA" "1" "334"
"166" "THAILANDIA" "1" "335"
"167" "VIETNAM" "1" "337"
"168" "HONG KONG" "1" "341"
"169" "MACAO" "1" "344"
"170" "BANGLADESH" "1" "345"
"171" "BRUNEI" "1" "346"
"172" "REPUBLICA DE YEMEN" "1" "348"
"173" "ARMENIA" "1" "349"
"174" "AZERBAIJAN" "1" "350"
"175" "GEORGIA" "1" "351"
"176" "KAZAJSTAN" "1" "352"
"177" "KIRGUIZISTAN" "1" "353"
"178" "TAYIKISTAN" "1" "354"
"179" "TURKMENISTAN" "1" "355"
"180" "UZBEKISTAN" "1" "356"
"181" "TERR. AU. PALESTINOS" "1" "357"
"182" "RESTO DE ASIA" "1" "397"
"183" "INDET.(ASIA)" "1" "398"
"184" "ALBANIA" "1" "401"
"185" "ANDORRA" "1" "404"
"186" "AUSTRIA" "1" "405"
"187" "BELGICA" "1" "406"
"188" "BULGARIA" "1" "407"
"189" "DINAMARCA" "1" "409"
"190" "ESPAÑA" "1" "410"
"191" "FINLANDIA" "1" "411"
"192" "FRANCIA" "1" "412"
"193" "GRECIA" "1" "413"
"194" "HUNGRIA" "1" "414"
"195" "IRLANDA" "1" "415"
"196" "ISLANDIA" "1" "416"
"197" "ITALIA" "1" "417"
"198" "LIECHTENSTEIN" "1" "418"
"199" "LUXEMBURGO" "1" "419"
"200" "MALTA" "1" "420"
"201" "MONACO" "1" "421"
"202" "NORUEGA" "1" "422"
"203" "PAISES BAJOS" "1" "423"
"205" "PORTUGAL" "1" "425"
"206" "REINO UNIDO" "1" "426"
"207" "RUMANIA" "1" "427"
"208" "SAN MARINO" "1" "428"
"209" "SUECIA" "1" "429"
"210" "SUIZA" "1" "430"
"211" "VATICANO(SANTA SEDE)" "1" "431"
"212" "POS.BRIT.(EUROPA)" "1" "433"
"213" "CHIPRE" "1" "435"
"214" "TURQUIA" "1" "436"
"215" "ALEMANIA,REP.FED." "1" "438"
"216" "BIELORRUSIA" "1" "439"
"217" "ESTONIA" "1" "440"
"218" "LETONIA" "1" "441"
"219" "LITUANIA" "1" "442"
"220" "MOLDAVIA" "1" "443"
"221" "RUSIA" "1" "444"
"222" "UCRANIA" "1" "445"
"223" "BOSNIA HERZEGOVINA" "1" "446"
"224" "CROACIA" "1" "447"
"225" "ESLOVAQUIA" "1" "448"
"226" "ESLOVENIA" "1" "449"
"227" "MACEDONIA" "1" "450"
"228" "REP. CHECA" "1" "451"
"229" "MONTENEGRO" "1" "453"
"230" "SERBIA" "1" "454"
"231" "RESTO EUROPA" "1" "497"
"232" "INDET.(EUROPA)" "1" "498"
"233" "AUSTRALIA" "1" "501"
"234" "NAURU" "1" "503"
"235" "NUEVA ZELANDIA" "1" "504"
"236" "VANATU" "1" "505"
"237" "SAMOA OCCIDENTAL" "1" "506"
"238" "TERRITORIO VINCULADOS A AUSTRALIA" "1" "507"
"239" "TERRITORIOS VINCULADOS AL R. UNIDO" "1" "508"
"240" "TERRITORIOS VINCULADOS A FRANCIA" "1" "509"
"241" "TER VINCULADOS A NUEVA. ZELANDA" "1" "510"
"242" "TER. VINCULADOS A ESTADOS UNIDOS" "1" "511"
"243" "FIJI, ISLAS" "1" "512"
"244" "PAPUA NUEVA GUINEA" "1" "513"
"245" "KIRIBATI, ISLAS" "1" "514"
"246" "MICRONESIA,EST.FEDER" "1" "515"
"247" "PALAU" "1" "516"
"248" "TUVALU" "1" "517"
"249" "SALOMON,ISLAS" "1" "518"
"250" "TONGA" "1" "519"
"251" "MARSHALL,ISLAS" "1" "520"
"252" "MARIANAS,ISLAS" "1" "521"
"253" "RESTO OCEANIA" "1" "597"
"254" "INDET.(OCEANIA)" "1" "598"
"255" "RESTO CONTINENTE" "1" "997"
"256" "INDET.(CONTINENTE)" "1" "998"
Hm, so 2M. I wonder why your and my batch run into a disconnect. On your side you even get 4 or 5 disconnects, on my side it was only 2 times, which may explain why I get all rows. However, I guess that disconnect is the cause for why you don't get all rows inserted. Feel free to read MySQL documentations about CLIENT_MULTI_STATEMENTS.
Code modification/commit
from ansgar.becker,
12 years ago,
revision 7.0.0.4183
Fix and enhance handling of multiple statements and multiple results:
* TMySQLConnection.Query did not process further results if the first one is a non-result. Instead, it disconnected you as the required mysql_store_result() was never called. Fixed now. Should fix the error described here: http://www.heidisql.com/forum.php?t=11049
* Cache result of SHOW VARIABLES, and introduce a MaxAllowedPacket function, which benefits from that cache
* Do not limit the number of statements in "batch in one
r4183 should fix these disconnection bugs on multiple queries. Please try out and give feedback. You should even only get 1 single packet with all 256 queries in it in batch mode, as I removed the hard number of 50 queries per packet. Also, the SQL log now should report the right sum of affected rows, which previously displayed only the affected rows from the first query in a batch.
Well done anse !!!
Now I have all data in the table. Perfect!
But I have a question:
All the insert statements were sent in one go. This is ok. But it is not possible to use the extended insert sintax here ?
something like:
I see this in the log:
When the script finishes this is the result in the log:
/* Affected rows: 512 Found rows: 0 Warnings: 0 Duration for 257 queries: 0,390 sec. */
what does the "Affected rows: 512" means ?
there is only 256 inserts and one alter table in my script...
Thanks for the fix!!
select * from Exp_Paises where IdPais = 4;
/* Affected rows: 0 Found rows: 1 Warnings: 0 Duration for 1 query: 0,172 sec. */
Now I have all data in the table. Perfect!
But I have a question:
All the insert statements were sent in one go. This is ok. But it is not possible to use the extended insert sintax here ?
something like:
INSERT INTO table (id, desc) VALUES (1, 'desc 1'), (2, 'desc 2'), (3, 'desc 3');
I see this in the log:
INSERT Exp_Paises (IdPais, Descripcion, Activo, IdAfip) VALUES (1, 'BURKINA FASO', 1, 101);INSERT Exp_Paises (IdPais, Descripcion, Activo, IdAfip) VALUES (2, 'ARGELIA', 1, 102);
/* .... */
When the script finishes this is the result in the log:
/* Affected rows: 512 Found rows: 0 Warnings: 0 Duration for 257 queries: 0,390 sec. */
what does the "Affected rows: 512" means ?
there is only 256 inserts and one alter table in my script...
Thanks for the fix!!
> But it is not possible to use the extended insert
> sintax here ?
The query editor cannot modifiy your INSERTs - that's up to you. But I guess you mean the grid export produced these INSERTs and you're asking if that export dialog can do that. The answer is no, as the grid export dialog must process each row as a single item, for various logical reasons. But the "Export database as SQL" can do that. Just check the table in question, select output "Clipboard", and no table creation, data: "INSERT". Alternatively, select output "Database" or even "Server" and export directly into that new table.
> what does the "Affected rows: 512" means ?
> there is only 256 inserts and one alter table in
> my script...
I came across the same question while testing. The ALTER TABLE at the very end touches all rows again, so you have 256 + 256 touched/affected rows.
> sintax here ?
The query editor cannot modifiy your INSERTs - that's up to you. But I guess you mean the grid export produced these INSERTs and you're asking if that export dialog can do that. The answer is no, as the grid export dialog must process each row as a single item, for various logical reasons. But the "Export database as SQL" can do that. Just check the table in question, select output "Clipboard", and no table creation, data: "INSERT". Alternatively, select output "Database" or even "Server" and export directly into that new table.
> what does the "Affected rows: 512" means ?
> there is only 256 inserts and one alter table in
> my script...
I came across the same question while testing. The ALTER TABLE at the very end touches all rows again, so you have 256 + 256 touched/affected rows.
> The query editor cannot modifiy your INSERTs - that's up to you. But I guess
> you mean the grid export produced these INSERTs and you're asking if that
> export dialog can do that. The answer is no, as the grid export dialog must
> process each row as a single item, for various logical reasons. But the
> "Export database as SQL" can do that. Just check the table in question,
> select output "Clipboard", and no table creation, data: "INSERT".
> Alternatively, select output "Database" or even "Server" and export directly
> into that new table.
Yes, I meant the grid export.
Thanks again
> you mean the grid export produced these INSERTs and you're asking if that
> export dialog can do that. The answer is no, as the grid export dialog must
> process each row as a single item, for various logical reasons. But the
> "Export database as SQL" can do that. Just check the table in question,
> select output "Clipboard", and no table creation, data: "INSERT".
> Alternatively, select output "Database" or even "Server" and export directly
> into that new table.
Yes, I meant the grid export.
Thanks again
Please login to leave a reply, or register at first.