show warnings not working on mysql 4

webnick

Hi, I'm running a LOAD DATA INFILE query to import into our database daily. The query runs from the command line as a pipe in. Is there any way to get more detailed reporting about warnings with this query? Supposedly only half the records are coming in.

MYSQL says "Query OK. 275,000 records, 0 deleted, 0 skipped, 260,000 warnings."
When I try 'SHOW WARNINGS' from the command prompt, I get an error. show_warnings(); in a PHP script also calls an error.

How can I display warning for a query? I'd probably write them into a table if I can get them displayed.

sneakyimp

it is difficult to tell if you are having problems with your php code, if you are getting errors from the command line using mysql's client, or if you are trying to use something like phpmyadmin.

try to be more specific about what exactly you are doing.

webnick

I'm running MYSQL 4.0.12-nt-log. Documentation seems to say SHOW WARNINGS is supported through the command line and the PHP api? I can run other show statements like SHOW STATUS or VERSION.

The SQL are piped in like mysql.exe < sql.txt

Another problem I've had is trying to specify the user running mysql from the command prompt. Z:>mysql\bin\mysq.exe --user=foo --password=bar

Any ideas on why mysql won't connect like this?

Thank you

sneakyimp

i'm still not sure whether you have php code that is buggy or whether you are trying to connect to mysql using the command line client.

If you are trying to run a SHOW WARNINGS query, you might check out the SHOW WARNINGS documentation:
http://dev.mysql.com/doc/refman/4.1/en/show-warnings.html

If you read the documentation, you will notice that this will return nothing if the last statement didn't cause an error. you might have 260,000 warnings importing a file but if the last one worked ok that sql will return nothing.

If you are wondering how to get errors in php when a query doesn't work, you should check out mysql_error():
http://php.net/mysql_error

if you are wondering what those 260,000 errors might be i'm not sure what to tell you....maybe try RTFM? Perhaps those errors get written into an error log.

webnick

Thanks for your help. Essentially, I want to get more detailed error reporting. So SHOW WARNINGS is only supported after 4.1 and we're on version 4.0.

It seems odd though that I'm still not getting errors to show up in the log files, if they're generated at the mysql> prompt or in a php script. The mysql configuration file has error logging on-

log-error=Z:/mysql/logs/error.log
log-slow-queries=Z:/mysql/logs/slow.log

errors.log has some general information-
010228 11:23:05  Aborted connection 19 to db: 'test' user: 'foo' host: `11.11.111.71' (Got an error reading communication packets)

Another option would be to tee the command prompt session to some file but this doesn't seem to work if you pipe in commands. In other words, I can

mysq.exe --user=foo --tee=log.txt
mysql> SHOW VARIABLES;

And they all show up in the file. If I try

mysq.exe --user=foo --tee=log.txt < showvariables.sql

nothing shows up.

Has anyone else run into problems with this logging sql with errors?