srfrogster has asked for the wisdom of the Perl Monks concerning the following question:

Hello all,
I have a problem with queries from perl. This is a simply code I have write to test mysql:
!#/usr/bin/perl

use Net::MySQL;

my $host = 'localhost';
my $db = 'MyDB';
my $db_user = 'mysql';
my $db_password = 'mysql';


my $mySQL = Net::MySQL->new(
    hostname => $host,
    database => $db,
    user     => $db_user,
    password => $db_password);

$mySQL->query("SELECT * FROM Users WHERE CSL = 'hsanche'");

my $record_set = $mySQL->create_record_iterator;
while (my $record = $record_set->each)
{
    print $record->[0]."\n";
}
$mySQL->close;

exit 0;

[download]

This script hangs up when executing $mySQL->query(...), and doesn't terminate never.

Both, the Database and table exist, and the mysqld is up and running well. Also, I have run a php script accessing to that MySQL table and retrieving its data, and it works ok.

I'm using perl 5.8.4 with mysql 5.0.45, and I have DBI, DBD::mysql and Net::MySQL modules up to date.

Greetings

Replies are listed 'Best First'.
Re: MySQL doesn't work with perl
by dragonchild (Archbishop) on Jan 09, 2008 at 16:15 UTC
    Don't use Net::MySQL. Just use DBI. 
    use DBI;
my $dbh = DBI->new(
    "dbi:mysql:host=$host;database=$database", $db_user, $db_password,
);

my $sth = $dbh->prepare( 'select * from users where csl = ?' );
$sth->execute( 'hasanche' );
while ( my $row = $sth->fetch ) {
    print "$row->{whatever_col1_is_called}\n";
}
$sth->finish;

    [download]
    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
[reply]
[d/l]
      I would agree with dragonchild, but for a different reason.

      The doc for Net::MySQL reads:

      This module implements network protocol between server and client of MySQL, thus you don't need external MySQL client library like libmysqlclient for this module to work. It means this module enables you to connect to MySQL server from some operation systems which MySQL is not ported. How nifty!
      But if DBI is available, because it is more "usual", the principle of least surprise says to use that.

      I can't comment on why your Net::MySQL code doesn't work as expected. It certainly seems okay. You might try logging in the mysql client and trying 

      mysql> show full processlist;
+----+-------+----------------+------+---------+------+-------+-------
+----------------+
| Id | User  | Host           | db   | Command | Time | State | Info  
+    |
+----+-------+----------------+------+---------+------+-------+-------
+----------------+
|  1 | woody | localhost:2244 | to9  | Query   |    0 | NULL | show fu
+ll processlist |
+----+-------+----------------+------+---------+------+-------+-------
+----------------+
1 row in set (0.10 sec)

      [download]
      to see what the server is doing when it hangs. Also, I'd add a bunch of or die $dbh->errstr() to the $dbh->prepare and $sth->execute statements, just to get a feel for what could be going wrong.
[reply]
[d/l]
[select]
        Ok,
        I have tested with DBI and it works great.
        Anyway, I have checked with the mysql command "show full processlist;" what is doing the server when it hangs on with Net::MySQL, and this is the result:
        mysql> show full processlist;
+----+-------+-----------+--------+---------+------+-------+----------
+-------------+
| Id | User  | Host      | db     | Command | Time | State | Info     
+             |
+----+-------+-----------+--------+---------+------+-------+----------
+-------------+
| 56 | root  | localhost | NULL   | Query   |    0 | NULL  | show full
+ processlist | 
| 57 | mysql | localhost | Weblib | Sleep   |  137 |       | NULL     
+             | 
+----+-------+-----------+--------+---------+------+-------+----------
+-------------+

        [download]

        Also, when running a "truss" command on UNIX, it says something similar:
        send(4, 0x081B6F68, 46, 0)                      = 46
   *\0\0\003 S E L E C T   *   F R O M   U s e r s   W H E R E   C
   S L   =   ' h s a n c h e '
recvfrom(4, 0x081B70B0, 1460, 0, 0x08047530, 0x0804796C) = 144
  01\0\00102 ,\0\00203 d e f06 W e b l i b05 U s e r s05 U s e r s
  03 C S L03 C S L\f !\018\0\0\0FD\0\0\0\0\0 6\0\00303 d e f06 W e
   b l i b05 U s e r s05 U s e r s\b L a n g C o d e\b L a n g C o
   d e\f !\006\0\0\0FE\0\0\0\0\005\0\004FE\0\002\0\v\0\00507 h s a
   n c h e02 e s05\0\006FE\0\002\0
recvfrom(4, 0x081F7520, 1460, 0, 0x08047530, 0x0804796C) (sleeping...)

        [download]

        I don't know why is so lazy this process and want to sleep always! :)
        So, I'm going to use the script with DBI
        Thanks to you 2 for your advice, guys
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom