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

Doing a very simple SELECT query and I'm not sure whats going wrong. It works fine when I do the WHERE statement pointing at one of the columns other than the primary key (weight). But as soon as I try and do a SELECT on the primary key the query hangs. Also this SELECT query works fine from the MySQL Administration program but always hangs through perl. 
my $query_submitted = "SELECT * FROM rates WHERE weight = 2"; # Hangs

my $query_submitted = "SELECT * FROM rates WHERE zone2 = '4.67'"; # Wo
+rks fine

[download]
Here is the table information from a "DESCRIBE rates";
Field Type Null Key Default Extra
weight int(11) NO PRI   auto_increment
zone2 varchar(45) NO      


I've tried using placeholders, placing quotes around the number, leaving quotes out. It always seems to just hang. Any thoughts on what I'm missing here? 
my $weight = 4; 

my $query_submitted = 'SELECT * FROM rates WHERE weight = ?';

# This executes the query
my $sth_submitted = $dbh->prepare($query_submitted) or die "Error prep
+are";
$sth_submitted->execute($weight) or die 'Error';

[download]
Any help is appreciated. As always, thank you in advance.

EDIT: This is also on a Windows Server 2003 box. FYI.


UPDATE: Gave up. Switched to an ODBC connection and it works like intended...

Replies are listed 'Best First'.
Re: MySQL 5.1.11 Select Query
by pc88mxer (Vicar) on Aug 05, 2008 at 15:36 UTC
    My guess is that primary key index is corrupted. Try REPAIR TABLE rates (if it's a MYISAM table.)

    Another thing to try is to re-create the table, and see if you still have the problem.

[reply]
[d/l]
[select]
      Did a REPAIR rates and it gave me a successfully repaired return value.

      Wouldn't the query not work from the Administration tool if it was the index was corrupted?

      EDIT: Still same problem. And it is a MYISAM table.
[reply]
[d/l]
Re: MySQL 5.1.11 Select Query
by Illuminatus (Curate) on Aug 05, 2008 at 16:46 UTC
    What do you mean by 'hang'? Does this this mean it executes the die operation? Have you tried "SELECT weight from rates". Have you tried the query from the SQL prompt?
[reply]
      I'm pulling the results through a web browser. When I try the WHERE pointed at weights, it hangs. Doesn't load a page.

      SELECT weight FROM rates works and returns the correct 70 rows in order.

      Running the query pointed at weights from the SQL Prompt executes perfectly. It returns the rows correctly. Only when I try doing it in perl do I get the problem.

      EDIT: I also went in and ran the process from the command line and it hung out there also. Should of output'd my html to the command prompt. So it isn't IIS or anything that causing this.

      Works great from the SQL Prompt, but perl DBI won't produce any results...
[reply]
[d/l]
[select]
Re: MySQL 5.1.11 Select Query
by perrin (Chancellor) on Aug 05, 2008 at 16:49 UTC
    Do a SHOW PROCESSLIST while the query is hanging and see what state it's in. If the result set is large, it may just be taking a while to load it all into memory.
[reply]
      I ran a SHOW PROCESSLIST while it was hanging and it does show a sleep being ran: 
      mysql> show processlist\g
+-----+-----------+----------------+---------------+---------+------+-
+------+---
---------------+
| Id  | User      | Host           | db            | Command | Time | 
+State | In
fo             |
+-----+-----------+----------------+---------------+---------+------+-
+------+---
---------------+
|  84 | root      | localhost:1332 | rate_calc     | Sleep   |    1 | 
+      | NU
LL             |
|  92 | WebClient | localhost:1346 | sch_oxitest08 | Sleep   |  957 | 
+      | NU
LL             |
| 100 | root      | localhost:1356 | rate_calc     | Query   |    0 | 
+NULL  | sh
ow processlist |
| 106 | root      | localhost:1364 | rate_calc     | Sleep   |   18 | 
+      | NU
LL             |
+-----+-----------+----------------+---------------+---------+------+-
+------+---
---------------+
4 rows in set (0.00 sec)

      [download]
[reply]
[d/l]
        Are you sure one of those sleeping connections is the one you ran the query on? Another thing you might try is DBI_TRACE. That shows you what DBI is doing.
[reply]
Re: MySQL 5.1.11 Select Query
by jethro (Monsignor) on Aug 05, 2008 at 16:35 UTC
    You might enable the logfiles of mysql to see what is happening on the mysql side. From mysql manual: "To enable the general query log, start mysqld with the --log[=file_name] or -l [file_name] option.". You might also check the error log.
[reply]
[d/l]
[select]
      Logfile shows: 
      080805 12:49:17          
5 Connect     root@localhost on rate_calc
5 Query       select * from rates where weight = '4'

      [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom