Wednesday, January 28, 2009

Perl Mysql DBI get deleted row count

Trying to capture the number of deleted rows when executing a delete statement was returning a "0E0". See below for the script details. What I found was that it was DBI's way of saying zero rows were deleted. The thing to know about DBI is that do() needs to return distinct values to distinguish the occurrence of an error from the case that no rows were affected, so it uses “undef” and “0E0”. The value “undef” indicates an error; it evaluates to false in a Boolean context. The string “0E0” indicates that no rows were affected; it evaluates to true in Boolean contexts but is treated as zero in numeric contexts. If do() returned 0 to indicate no rows were affected, that evaluates to false in a Booelan context and would be difficult from an error. You can do the following to display the row count correctly

Error:

my $deleted = $conn->do("delete from employee where year between 1901 and 1910");
print "deleted $deleted rows.\n";

The above would print "deleted 0E0 rows"


Solution:

 
my $deleted = $conn->do("delete from employee where year between 1901 and 1910");
printf "deleted %d rows.\n", $deleted;

(OR)

my $deleted += $conn->do("delete from employee where year between 1901 and 1910");
print "deleted $deleted rows.\n";

Monday, January 26, 2009

Adcenter Error Code 1514 : The Negative keywords requires partial match bid

Got this error when dealing with a keyword update through the Adcenter API. Its a shame that Microsoft has no documentation on this error and none the less anywhere on the internet, hence the drive to blog this. Here's a brief background on Adcenter Negative Keywords:

http://msdn.microsoft.com/en-us/library/bb545038.aspx

So what the error basically means is that "Negative keywords are only allowed on keywords that have a broad or phrase match bid specified". If you try to add/update a keyword with "Not Keywords" and only exact bid specified then you would get this error. In my case this was being caused by a different scenario. We use Apache Axis for our webservice calls and Axis treats a blank string as a valid content string when compared to a null string. So even if the "Not Keywords" was set to blank it was generating the "Negative Keyword" soap element. See below for SOAP source. In the soap below the broad and phrase bid is specified as 0's and the "NegativeKeywords" element is constructed making MSN think that there are negative keywords associated with this keyword and hence the error. I resolved this error by setting the string to NULL instead of a blank ("''").

<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soapenv:Header><ns1:ApplicationToken soapenv:actor="http://schemas.xmlsoap.org/soap/actor/next" soapenv:mustUnderstand="0" xmlns:ns1="https://adcenter.microsoft.com/api/advertiser/v5"/><ns2:DeveloperToken soapenv:actor="http://schemas.xmlsoap.org/soap/actor/next" soapenv:mustUnderstand="0" xmlns:ns2="https://adcenter.microsoft.com/api/advertiser/v5"><ns2:Value>xxxx</ns2:Value></ns2:DeveloperToken><ns3:UserCredentials soapenv:actor="http://schemas.xmlsoap.org/soap/actor/next" soapenv:mustUnderstand="0" xmlns:ns3="https://adcenter.microsoft.com/api/advertiser/v5"><ns3:Password>xxxxxx</ns3:Password><ns3:Username>xxxxx</ns3:Username></ns3:UserCredentials></soapenv:Header>
<soapenv:Body>
<UpdateKeywordsRequest xmlns="https://adcenter.microsoft.com/api/advertiser/v5"><AdGroupId>xxxxx</AdGroupId><Keywords>
<Keyword><BroadMatchBid>0.0</BroadMatchBid><ExactMatchBid>0.5</ExactMatchBid><Id>xxxxx</Id><NegativeKeywords><ns4:string xmlns:ns4="http://schemas.microsoft.com/2003/10/Serialization/Arrays"></ns4:string></NegativeKeywords><Param1>xxxx</Param1><Param2></Param2><Param3></Param3><PhraseMatchBid>0.0</PhraseMatchBid></Keyword>
<Keyword><BroadMatchBid>0.0</BroadMatchBid><ExactMatchBid>0.5</ExactMatchBid><Id>xxxxxx</Id><NegativeKeywords><ns5:string xmlns:ns5="http://schemas.microsoft.com/2003/10/Serialization/Arrays"></ns5:string></NegativeKeywords><Param1>xxxxxxx</Param1><Param2></Param2><Param3></Param3><PhraseMatchBid>0.0</PhraseMatchBid></Keyword>
</Keywords>
</UpdateKeywordsRequest>
</soapenv:Body>
</soapenv:Envelope>


<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Body><s:Fault><faultcode>s:Client</faultcode><faultstring xml:lang="en-US">Invalid client data. Check the SOAP fault details for more information</faultstring>
<detail>
<EditorialApiFaultDetail xmlns="https://adcenter.microsoft.com/api/advertiser/v5" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<BatchErrors>
<BatchError><Code>1514</Code><Details i:nil="true"/><Index>0</Index><Message>The Negative keywords requires partial match bid.</Message></BatchError>
<BatchError><Code>1514</Code><Details i:nil="true"/><Index>1</Index><Message>The Negative keywords requires partial match bid.</Message></BatchError>
</BatchErrors>
<OperationErrors/>
<TrackingId>xxxxxxx</TrackingId>
<EditorialErrors/></EditorialApiFaultDetail>
</detail>
</s:Fault></s:Body>
</s:Envelope>

Tuesday, January 20, 2009

Mysql (errno: 13) can't find file frm

Recently got this error when I was trying to copy a database on to another server. I basically copied the mysql data directory for the corresponding database on to the new server and logged into mysql client.


mysql> select count(*) from placement;
Can't find file: './main/placement.frm (errno: 13)


Checked the mysql log and this was being reported for the all frm files for all tables in the db. Looks like this is a permission issue on the data directory. Changed to the following permissions and it seemed to work.


server> sudo chmod -R 660 main
server> sudo chmod 775 main

Thursday, January 15, 2009

DBD::mysql::db do failed: The used command is not allowed with this MySQL version

Our nightly etl process loads a whole lot of data from local files. The recent upgrade of mysql from 5.0.45 --> 5.0.67 was throwing this error

"DBD::mysql::db do failed: The used command is not allowed with this MySQL version"

when using the mysql 'LOAD LOCAL FILE' command. We are not entirely sure if this is an issue caused by the mysql upgrade or an outdated perl DBD:Mysql module. MySQL server has the local_infile set to ON via a show variables command (see below) and user has the FILE permission to the local file. A reinstall of the DBD:Mysql module also did not resolve the issue.


mysql> show variables like 'local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+

Looking online we came through various links that suggested we needed to specify a option "mysql_local_infile" while connecting to mysql via the DBD:Mysql module. This seemed to resolve our issue.


use strict;
use DBI;
my $dsn = "DBI:mysql:mydb;mysql_local_infile=1";
my $user = "me";
my $password = "secret";
my $dbh = DBI->connect($dsn,$user,$password);


References:
http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html
http://www.perlmonks.org/?node_id=728472
http://www.bigresource.com/MYSQL-LOAD-DATA-INFILE-command-to-import-a-text-f
ile-rODHiNex.html

DBD:Mysql warning statements "Use of uninitialized value in concatenation (.) or string"

We recently upgraded to the latest and greatest version of the DBD:Mysql perl module and have been seeing these warning statements in our logs a lot (believe me a whole lot to the point of annoyance for developers skimming through logs for debug help).

Use of uninitialized value in concatenation (.) or string at /usr/local/perl/lib/site_perl/5.8.8/x86_64-linux-thread-multi/DBI.pm line 929.

For now we went ahead and commented the line no. 929 in the /usr/local/perl/lib/site_perl/5.8.8/x86_64-linux-thread-multi/DBI.pm file and have seen no side effects of that. A fairly weak solution but works for now.

Wednesday, January 07, 2009

setting up a home server

I am a software guy by profession and have been lately delving into some system ops at work. Been playing around with a home server for the last month or so mostly for learning purposes. Lots of questions and searching on the internet. Came across this book that is quite useful to understand some of the basics around setting up a home network. "Home Wireless Networking in a Snap (Sams Teach Yourself) (Paperback)" (http://www.amazon.com/gp/product/0672327023). I am a linux guy and although this book is based on setting a windows server, still the router related stuff applies everywhere. Came across this book when I was trying to resolve as to why my home server won't respond to ping although ssh and ftp works. DUH!!! the router is the public interface for the home network and the settings on the router was configured to not respond to ping requests. The WAN setup page on my netgear router has a setting "Allow Pinging of Router Internet Interface" that needs to be enabled. There are some other cool stuff that can be set on the WAN setup page. See description and picture below:

Enable/Disable Firewall

Select the appropriate check box or option button to turn on your router's firewall. Some routers (such as my Netgear router) have the firewall enabled by default, and the configuration page does not provide an enable option; it only allows you to disable the firewall by selecting Disable SPI Firewall. Disabling the firewall opens up your network to the possibility of outside attack. There is actually no good reason to disable the firewall, even if access to gaming or other services is a problem, because all connectivity issues can be resolved with port triggering and port forwarding settings

Specify DMZ Server IP Address

If you want to operate a computer or a server (such as a computer that is acting as a web server or a gaming server) outside the firewall, you can have the router place that computer in the DMZ. This means that the network is still protected from attack but that the DMZ computer could potentially be attacked. To place a computer on your network in the DMZ using a Netgear router, select the Default DMZ Server check box and then enter the IP address of the computer that will be placed in the DMZ. The DMZ isn't really a place; it is a virtual location configured by your WiFi router's firewall. The DMZ is a virtual place that resides between your protected internal network and the public Internet. Placing a computer in the DMZ allows it to communicate with the Internet without the router's firewall inspecting the data flowing to and from the computer. It is not uncommon for computers offering certain services to be placed in the DMZ. Even large corporations sometimes place communication servers in the DMZ so that they do not have to open ports on the firewall to allow access to the server.


Allow Pinging of Router Internet Interface


By default, most WiFi routers are configured so that the router's interface or connection to the Internet cannot be pinged. The Internet interface for you router is actually assigned its IP address by your Internet service provider. So the Internet interface on the router is really its public interface. Allowing the public interface to be pinged can open the router up to attack since it can be "pinged to death." A malicious individual on the Internet could send a barrage of ping packets or oversized ping packets that would actually bring down the router's public interface. This kind of attack is called the "Ping of Death." Enable the router's Internet interface for pinging only if your Internet service provider (or you) needs to ping that interface to determine whether there is a connectivity problem. For my Netgear router, I select the Respond to Ping on Internet Port option to turn on this feature. When you have determined that the interface can be reached by a ping (from you or the ISP technician), I suggest that you disable the feature.

Set MTU Size

The Maximum Transmit Unit (MTU) value for Ethernet networks such as your WiFi network is 1500 bytes. Leave the MTU setting at the default unless your Internet service provider requires that a different setting be used. If you're unsure about the MTU value, contact your ISP. To change the MTU on my Netgear router, I click in the MTU text box and type a different value. Each router provides a slightly different configuration screen for setting the MTU. Your Internet service provider determines the optimal MTU for the network it services by trial and error. The only way you might perceive that you don't have the correct MTU setting for your ISP connection would be a slight slowing of the connection to the Internet—and this would only be in situations where your MTU is set higher than the ISP's and your data packets have to be broken into smaller chunks for transmission. So, bottom line, call your ISP and see whether it uses a special MTU setting.