list of warnings

I often perform mass updates by pasting in over 1,000 queries and running them all at once. This one bunch (all insert queries) gives me the message box that says I have 7 warnings, and says "Warning from last query:" -- and nothing else.

I do not want any warnings or errors; I want to fix them all. But how can I get a list of all warnings when and as they occur? --They’re not in the log file (although the last line says "Warnings: 7...."

Example, one time I had 1,000 warnings, last one being "data truncated..." I figured out that I had a comma and decimal point in an integer field. But these were listed as warnings and should have been errors, IMO (at least for my application). So Now I’m afraid and want to correct all warnings. But my last batch says I have 7 warnings but I can find no way to display them.

Pls, how to display all warnings?

Thanking you all

try the following: immediately after your query has finished executing, and it has told you of warnings, open a new query tab, enter

show warnings

and press F9. AFAIK, this should show the warning messages.

TY jfalch.

Didn’t work.

From the monitor pane, here are the last two queries, and the output:

INSERT INTO a_sto (`dbid`, `catno`, `title`, `slr`, `usd`, `qis`) VALUES ("wh464", "WH464(2010 ed.)", "Walking Meditation - Three Expositions on Walking Meditation by Multi Author ISBN 955 24 0300 2", "50", "1.00", "1142");
/* Affected rows: 1,174 Found rows: 0 Warnings: 7 Duration for 1,174 queries: 1.123 sec. */
SHOW WARNINGS LIMIT 5;
<< popup window appears, no warnings shown. Have to click OK on the popup >>
show warnings; << entered in the other query tab >>
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 1 query: 0.000 sec. */
<< nothing displayed >>

BTW I had two query tabs open, ran 1174 in one, and SHOW WARNINGS; in the other.

And I did it one last time, following your instructions exactly. Nothing in the result pane.

--perplexing...

And I tried adding “show warnings;” to the 1174 queries. From the monitor pane:

INSERT INTO a_sto (`dbid`, `catno`, `title`, `slr`, `usd`, `qis`) VALUES ("wh464", "WH464(2010 ed.)", "Walking Meditation - Three Expositions on Walking Meditation by Multi Author ISBN 955 24 0300 2", "50", "1.00", "1142");
show warnings;
/* Affected rows: 1,174 Found rows: 0 Warnings: 7 Duration for 1,175 queries: 1.074 sec. */

No warnings anywhere...

hm. looks like a job for anse then, to show them inside heidisql...

in the meantime, if you really want to see the warnings, you could Tools / Launch command line in Heidisql´s menu.
(If you do not yet have mysql.exe anywhere, i´m afraid you have to download a .zip file of around 200 MB from here; after unpacking mysql.exe is in the bin\ directory, you can delete all the rest. put mysql.exe into the heidisql directory.)

then, on the mysql.exe command prompt (xxx> ), enter
\W
+enter to switch on warnings, then
\. your-file.sql
+enter to process your file. this should finally show the elusive warnings.

Well, that SHOW WARNINGS query should work, and if not, I cannot display anything else in HeidiSQL. I just tried to reproduce that situation, by firing an INSERT query into a table with a string that's longer than the VARCHAR's length. I get a popup dialog:

Warning
Your query produced 1 warnings.
Warnings from last query:
Warning: Data truncated for column 'name' at row 1
[OK]

Running SHOW WARNINGS afterwards shows me the same warning. Why doesn't that work on your server?

Ah, I did not read your first post properly: You are running several, 1000s or whatever number of queries at once, and some query inbetween generates warnings. Warnings are cleared after running a new query that uses a table. See http://dev.mysql.com/doc/refman/5.0/en/show-warnings.html

HeidiSQL now remembers the number of warnings after each query, from what mysql_warning_count() returns. That's all HeidiSQL can do between queries - the warnings themselves can only be fetched per SHOW WARNINGS *after* the batch of queries. Finally, it turns out that the log details about warnings are useless in batch mode. Well.

My appreciation to fjalch and ansgar for their kind support. I would like to share my solution to the problem.

All the queries come from a Word macro I wrote that converts a spreadsheet (Word table) into a series of insert, update, or delete queries. I need to perform the updates in this way, as the CMS software on my remote e-commerce web site has an SQL query input function; it’s easy to update my local copy AND the remote site with the same batch of queries. Much easier than updating a local table, exporting it, then logging onto the remote side, invoking phpymadmin, and importing the table.

Here’s the new query, which merely imports the original spreadsheet (as a cs file) into a temporary table.

LOAD DATA LOW_PRIORITY LOCAL INFILE 'D:\\bps\\Local_Sites\\database\\updates\\20130724-newlist\\Stores-01.csv'
REPLACE INTO TABLE `bpslk_txp`.`a_sto`
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES (`ID1`, `dbid`, `catno`, `title`, `qis`, `USD`, `SLR`);

Then I get a list of warnings, which I can correct in the original spreadsheet. Once the spreadsheet data is corrected, I can create the queries using the Word macro, and safely run them locally and remotely.

Thanks aqain to all.

I continue this discution in another topic : "SHOW WARNINGS following CREATE TABLE"