load data local infile, need to see warnings if insert query is incorrect

I have a script which executes a few mysql queries, and the script also sends me email with any errors in the queries, EXCEPT the insert query. If the contents of the local infile have an incorrect column count for one of the records (I did this as a test), no error is returned. So, my question is, how do I see the errors returned for incorrect column count, field type, etc., on an insert? Thanks.

$sql = array();
$sql[]="DROP TABLE IF EXISTS `products_temp`";
$sql[]="CREATE TABLE `products_temp` SELECT * FROM `products`";
$sql[]="TRUNCATE TABLE `products_temp`";
$sql[]="ALTER TABLE `products_temp` ADD PRIMARY KEY  (`products_id`), ADD KEY `idx_products_date_added` (`products_date_added`), ADD KEY `idx_filters` (`manufacturer`,`special_price`), ADD KEY `stock_status` (`stock_status`), ADD FULLTEXT KEY `ft_products_model` (`products_model`)";
$sql[]="LOAD DATA LOCAL INFILE '/usr/home/folder/commands/sql/products_temp_insert.sql' INTO TABLE products_temp FIELDS TERMINATED BY '|'";
$sql[]="REPAIR TABLE `products_temp` QUICK";
$sql[]="LOCK TABLE `products` WRITE";
$sql[]="DROP TABLE `products`";
$sql[]="RENAME TABLE `products_temp` TO `products`";
$sql[]="UNLOCK TABLE";
pkromerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
adrpoConnect With a Mentor Commented:

Hi,

Ok, let's give it a try....
Please view my code with a very critical eye as my PHP knowledge is a bit fuzzy.
Check the script by trying to run with bad LOAD files and see what is happening.

Cheers,
za-k/

$warningMessage = "";
$warningSubject = "";
 
	for($i=0, $limit = count($sql); $i < $limit; ++$i)
	{
		if( !mysql_query($sql[$i],$link) )
		{	//bummer, we have an error.
			$errorMessage .= "\nFailed Query.";
			$errorMessage .= "\nAttempted Query: " . $sql[$i];
			$errorMessage .= "\nError: " . mysql_errno($link);
			$errorMessage .= "\nDescription: " . mysql_error($link);
			$errorSubject = "DB: Query Problem.";
			
			if($stopOnError==TRUE)
			{	//exit only when admin wants to exit immediately upon error
				break;
			}
		}
                  // additional checking code for LOAD DATA LOCAL 
                  // i don't know how how is string comparison in php, but you can fix that                   
                  if(substr($sql[$i], 0, 4) == "LOAD")                   
                  {
                      // perform additional checks after LOAD                     
                     $warnings = mysql_query("SHOW WARNINGS;", $link);
                     $numberOfWarnings = mysql_query("SHOW COUNT(*) WARNINGS;", $link);
                     $haveWarnings = FALSE;
                     while ($row = mysql_fetch_assoc($warnings)) {
                           //bummer, we have warnings!
                           $warningMessage .= "\nWarning: " . $row['Level'];
                           $warningMessage .= " Code: " . $row['Code']";
                           $warningMessage .= " Message: " . $row['Message'];
                           $haveWarnings = TRUE;
                     }
                     $warningSubject = "DB: Query Warning.";
                     if($haveWarnings and $stopOnWarning==TRUE)
                     { //exit only when admin wants to exit immediately upon Warnings
                       break;
                     }
                  }
	}
 
//check if any errors or warnings were encountered
if($errorMessage!="" or $warningMessage!="" )
{      //notify admin
      reportError($admin_email,
                  $errorSubject . " / " . $warningSubject, 
                  $errorMessage . "\n-----\n" . $warningMessage);
                  $stderr = fopen('php://stderr', 'w');
      fwrite($stderr, $errorMessage . "\n-----\n" . $warningMessage);
      fclose($stderr);
}

Open in new window

0
 
pkromerAuthor Commented:
Here is the complete code..
#!/usr/www/cgi-bin/php4.cgi -q
<?php
$db_name="mydb";
$db_server="localhost";
$db_user="myuser";
$db_password="mypw";
$admin_email="me@mysite.com";
 
//set to true if you wish to stop other commands from executing as soon as one
//of them fails. Otherwise all commands will be run and you will get a report
//of all errors encountered, if any.
$stopOnError = TRUE;
 
$sql = array();
$sql[]="DROP TABLE IF EXISTS `products_temp`";
$sql[]="CREATE TABLE `products_temp` SELECT * FROM `products`";
$sql[]="TRUNCATE TABLE `products_temp`";
$sql[]="ALTER TABLE `products_temp` ADD PRIMARY KEY  (`products_id`), ADD KEY `idx_products_date_added` (`products_date_added`), ADD KEY `idx_filters` (`manufacturer`,`special_price`), ADD KEY `stock_status` (`stock_status`), ADD FULLTEXT KEY `ft_products_model` (`products_model`)";
$sql[]="LOAD DATA LOCAL INFILE '/usr/home/folder/commands/sql/products_temp_insert.sql' INTO TABLE products_temp FIELDS TERMINATED BY '|'";
$sql[]="REPAIR TABLE `products_temp` QUICK";
$sql[]="LOCK TABLE `products` WRITE";
$sql[]="DROP TABLE `products`";
$sql[]="RENAME TABLE `products_temp` TO `products`";
$sql[]="UNLOCK TABLE";
 
$errorMessage="";
$errorSubject="";
 
//attempt to connect to db. If connection fails, no need to attempt further queries! 
if (!($link = mysql_connect($db_server, $db_user, $db_password, $db_database)))
{
	$errorMessage .= "\nUnable to connect to server.";
	$errorMessage .= "\nError: " . mysql_errno($link);
	$errorMessage .= "\nDescription: " . mysql_error($link);
	$errorSubject = "DB:Connection Problem";
}
else if($db_name && !(mysql_select_db($db_name, $link)) )
{
	$errorMessage .= "\nUnable to use DB " . $db_name;
	$errorMessage .= "\nError: " . mysql_errno($link);
	$errorMessage .= "\nDescription: " . mysql_error($link);
	$errorSubject = "DB:Selection Problem";
}
else //we have connection. Let's loop through sql array and attempt to execute each command
{
	for($i=0, $limit = count($sql); $i < $limit; ++$i)
	{
		if( !mysql_query($sql[$i],$link) )
		{	//bummer, we have an error.
			$errorMessage .= "\nFailed Query.";
			$errorMessage .= "\nAttempted Query: " . $sql[$i];
			$errorMessage .= "\nError: " . mysql_errno($link);
			$errorMessage .= "\nDescription: " . mysql_error($link);
			$errorSubject = "DB: Query Problem.";
			
			if($stopOnError==TRUE)
			{	//exit only when admin wants to exit immediately upon error
				break;
			}
		}
	}
	mysql_close($link);
}
 
//check if any errors were encountered
if($errorMessage!="")
{      //notify admin
      reportError($admin_email,$errorSubject,$errorMessage);
                     $stderr = fopen('php://stderr', 'w');
      fwrite($stderr, $errorMessage);
                     fclose($stderr);
}
function reportError($to,$subject,$message)
{
	$headers = 'From: me@mysite.com' . "\r\n" .
    'Reply-To: me@mysite.com' . "\r\n" .
    'Cc: me2@mysite.com';
	mail($to, $subject, $message, $headers);
}
?>

Open in new window

0
 
adrpoCommented:

Hi,

You could use show warnings:
SHOW WARNINGS;
SHOW COUNT(*) WARNINGS;
SELECT @@warning_count;
http://dev.mysql.com/doc/refman/5.0/en/show-warnings.html

Read more here (at the end before the user comments):
http://dev.mysql.com/doc/refman/5.0/en/load-data.html
It seems that just warnings are generated if there are too
many or too less columns. I agree that is not very useful.

Another (I would say better way) would be to load the data
in a temp table and then insert it into the real table.
Then you might get some errors or warnings if there is any
missing data.

Cheers,
za-k/
0
 
pkromerAuthor Commented:
any idea where in my code i would put that?
0
 
pkromerAuthor Commented:
Wow. I was hoping i could just add it to the end of all the other commands but I guess, from how you have written it into the existing code, that it is handled completely different than an error. I will try that and let you know. Thanks.
0
All Courses

From novice to tech pro — start learning today.