Problem running multiple INSERT in a script

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)

Which revision of HeidiSQL are you using? I recall I already fixed a bug like that some time ago.

My version is 7.0.0.4175 (build 403x has the same bug, I dont remember exactly the last number)

4175 is the latest. Ok, then I'll check that here.

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.

You could try out again that script and paste the log output here, or on pastebin.com. So I can check what's different on your side.

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:

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"

What does

SHOW VARIABLES LIKE 'max_allowed_packet';

show you?

20971520

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.

Got it. Disconnects are caused by HeidiSQL's logic in TMySQLConnection.Query. There is a wrong approach in the way multi results are handled in the case when the first query is a non-result query (e.g. INSERT or UPDATE).

Great! when ready I can test the build

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.

Ah, and not to forget: The max_allowed_packet size is now included in the infos which pop up on the status bar's server panel (where you see the server version).

Well done anse !!!

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.

> 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