Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > net::mysql holds onto my query

Reply
Thread Tools

net::mysql holds onto my query

 
 
Lee
Guest
Posts: n/a
 
      02-20-2007
Hi,
I am sending this query to Net::MySQL, and it halts. The program
doesn't move forward to tell me if there is an error or to print the
next line after that. This query works in the query browser, and I
can make other queries in the same program. Thus, I know that there
is no general problem with making queries, and I know that there is no
problem with how I make the queries. Can you guys help me figure out
what is wrong with pairing this query with perl? Many thanks!

sub writeCsvFromQuery{
my($query,$newFilename)=@_;
my $time = time();
print "Running query\n$query\n";
$mysql->query($query);
print "Getting result..\n";
if($mysql->is_error()){
print "Cannot run query because " . $mysql-
>get_error_message();

return;
}
my $result=$mysql->create_record_iterator();
my @field=$result->get_field_names();
open(SPREADSHEET,">$newFilename") or die ($!);
print SPREADSHEET join(',',@field) . "\n";
while(my $row=$result->each){
my $line = join(',',@$row) . "\n";
chomp($line);
print SPREADSHEET $line . "\n";
}
close SPREADSHEET;
print "Spreadsheet was written to $newFilename\n";
my $timeTook = time() - $time;
print "Query took " . ($timeTook) . " seconds to complete, or " .
($timeTook/60) . " minutes.\n";
return 1;
}

this query works:
SELECT * FROM network LIMIT 3;

this query does not work (but works in the query browser):
SELECT DISTINCT niNode1,niNode2,niR,maNode1,maNode2,maR FROM(
SELECT net.node1 AS niNode1,net.node2 AS niNode2,net.r AS
niR,cloneid
FROM intersection,
network net
WHERE lineage='NI'
AND r>0.92
AND (net.node1 IN (SELECT cloneid FROM intersection) AND net.node2
IN (SELECT cloneid FROM intersection) )
AND (intersection.cloneid = net.node1 OR intersection.cloneid =
net.node2)
) niEdge,
(
SELECT net.node1 AS maNode1,net.node2 AS maNode2,net.r AS
maR,cloneid
FROM intersection,
network net
WHERE lineage='MA'
AND r>0.92
AND (net.node1 IN (SELECT cloneid FROM intersection) AND net.node2
IN (SELECT cloneid FROM intersection) )
AND (intersection.cloneid = net.node1 OR intersection.cloneid =
net.node2)
) maEdge
WHERE (maNode1=niNode1 AND maNode2=niNode2)
OR (maEdge.maNode2=niEdge.niNode1 AND
maEdge.maNode1=niEdge.niNode2)
ORDER BY niNode1,maNode1;

 
Reply With Quote
 
 
 
 
Lee
Guest
Posts: n/a
 
      02-21-2007
I set debug=true and got this information in case it helps anyone help
me...

Net::MySQL::_execute_command():
7A 03 00 00 03 53 45 4C 45 43 54 20 44 49 53 54 z....SELECT.DIST
49 4E 43 54 20 6E 69 4E 6F 64 65 31 2C 6E 69 4E INCT.niNode1,niN
6F 64 65 32 2C 6E 69 52 2C 6D 61 4E 6F 64 65 31 ode2,niR,maNode1
2C 6D 61 4E 6F 64 65 32 2C 6D 61 52 20 46 52 4F ,maNode2,maR.FRO
4D 28 0A 20 20 53 45 4C 45 43 54 20 6E 65 74 2E M(...SELECT.net.
6E 6F 64 65 31 20 41 53 20 6E 69 4E 6F 64 65 31 node1.AS.niNode1
2C 6E 65 74 2E 6E 6F 64 65 32 20 41 53 20 6E 69 ,net.node2.AS.ni
4E 6F 64 65 32 2C 6E 65 74 2E 72 20 41 53 20 6E Node2,net.r.AS.n
69 52 2C 63 6C 6F 6E 65 69 64 0A 20 20 46 52 4F iR,cloneid...FRO
4D 20 69 6E 74 65 72 73 65 63 74 69 6F 6E 2C 0A M.intersection,.
20 20 6E 65 74 77 6F 72 6B 20 6E 65 74 0A 20 20 ..network.net...
57 48 45 52 45 20 6C 69 6E 65 61 67 65 3D 27 4E WHERE.lineage='N
49 27 0A 20 20 41 4E 44 20 72 3E 30 2E 39 39 0A I'...AND.r.0.99.
20 20 41 4E 44 20 28 6E 65 74 2E 6E 6F 64 65 31 ..AND.(net.node1
20 49 4E 20 28 53 45 4C 45 43 54 20 63 6C 6F 6E .IN.(SELECT.clon
65 69 64 20 46 52 4F 4D 20 69 6E 74 65 72 73 65 eid.FROM.interse
63 74 69 6F 6E 29 20 41 4E 44 20 6E 65 74 2E 6E ction).AND.net.n
6F 64 65 32 20 49 4E 20 28 53 45 4C 45 43 54 20 ode2.IN.(SELECT.
63 6C 6F 6E 65 69 64 20 46 52 4F 4D 20 69 6E 74 cloneid.FROM.int
65 72 73 65 63 74 69 6F 6E 29 20 29 0A 20 20 41 ersection).)...A
4E 44 20 28 69 6E 74 65 72 73 65 63 74 69 6F 6E ND.(intersection
2E 63 6C 6F 6E 65 69 64 20 3D 20 6E 65 74 2E 6E .cloneid.=.net.n
6F 64 65 31 20 4F 52 20 69 6E 74 65 72 73 65 63 ode1.OR.intersec
74 69 6F 6E 2E 63 6C 6F 6E 65 69 64 20 3D 20 6E tion.cloneid.=.n
65 74 2E 6E 6F 64 65 32 29 0A 29 20 6E 69 45 64 et.node2).).niEd
67 65 2C 0A 28 0A 20 20 53 45 4C 45 43 54 20 6E ge,.(...SELECT.n
65 74 2E 6E 6F 64 65 31 20 41 53 20 6D 61 4E 6F et.node1.AS.maNo
64 65 31 2C 6E 65 74 2E 6E 6F 64 65 32 20 41 53 de1,net.node2.AS
20 6D 61 4E 6F 64 65 32 2C 6E 65 74 2E 72 20 41 .maNode2,net.r.A
53 20 6D 61 52 2C 63 6C 6F 6E 65 69 64 0A 20 20 S.maR,cloneid...
46 52 4F 4D 20 69 6E 74 65 72 73 65 63 74 69 6F FROM.intersectio
6E 2C 0A 20 20 6E 65 74 77 6F 72 6B 20 6E 65 74 n,...network.net
0A 20 20 57 48 45 52 45 20 6C 69 6E 65 61 67 65 ...WHERE.lineage
3D 27 4D 41 27 0A 20 20 41 4E 44 20 72 3E 30 2E ='MA'...AND.r.0.
39 39 0A 20 20 41 4E 44 20 28 6E 65 74 2E 6E 6F 99...AND.(net.no
64 65 31 20 49 4E 20 28 53 45 4C 45 43 54 20 63 de1.IN.(SELECT.c
6C 6F 6E 65 69 64 20 46 52 4F 4D 20 69 6E 74 65 loneid.FROM.inte
72 73 65 63 74 69 6F 6E 29 20 41 4E 44 20 6E 65 rsection).AND.ne
74 2E 6E 6F 64 65 32 20 49 4E 20 28 53 45 4C 45 t.node2.IN.(SELE
43 54 20 63 6C 6F 6E 65 69 64 20 46 52 4F 4D 20 CT.cloneid.FROM.
69 6E 74 65 72 73 65 63 74 69 6F 6E 29 20 29 0A intersection).).
20 20 41 4E 44 20 28 69 6E 74 65 72 73 65 63 74 ..AND.(intersect
69 6F 6E 2E 63 6C 6F 6E 65 69 64 20 3D 20 6E 65 ion.cloneid.=.ne
74 2E 6E 6F 64 65 31 20 4F 52 20 69 6E 74 65 72 t.node1.OR.inter
73 65 63 74 69 6F 6E 2E 63 6C 6F 6E 65 69 64 20 section.cloneid.
3D 20 6E 65 74 2E 6E 6F 64 65 32 29 0A 20 29 20 =.net.node2)..).
6D 61 45 64 67 65 0A 20 20 20 20 57 48 45 52 45 maEdge.....WHERE
20 28 6D 61 4E 6F 64 65 31 3D 6E 69 4E 6F 64 65 .(maNode1=niNode
31 20 41 4E 44 20 6D 61 4E 6F 64 65 32 3D 6E 69 1.AND.maNode2=ni
4E 6F 64 65 32 29 0A 20 20 20 20 4F 52 20 28 6D Node2).....OR.(m
61 45 64 67 65 2E 6D 61 4E 6F 64 65 32 3D 6E 69 aEdge.maNode2=ni
45 64 67 65 2E 6E 69 4E 6F 64 65 31 20 41 4E 44 Edge.niNode1.AND
20 6D 61 45 64 67 65 2E 6D 61 4E 6F 64 65 31 3D .maEdge.maNode1=
6E 69 45 64 67 65 2E 6E 69 4E 6F 64 65 32 29 0A niEdge.niNode2).
20 20 20 20 4F 52 44 45 52 20 42 59 20 6E 69 4E ....ORDER.BY.niN
6F 64 65 31 2C 6D 61 4E 6F 64 65 31 3B 0A ode1,maNode1;.
Net::MySQL::_execute_command():
01 00 00 01 06 38 00 00 02 03 64 65 66 0A 6D 69 .....8....def.mi
63 72 6F 61 72 72 61 79 03 6E 65 74 07 6E 65 74 croarray.net.net
77 6F 72 6B 07 6E 69 4E 6F 64 65 31 07 6E 69 4E work.niNode1.niN
6F 64 65 31 0C 21 00 3C 00 00 00 FD 01 10 00 00 ode1............
00 38 00 00 03 03 64 65 66 0A 6D 69 63 72 6F 61 .8....def.microa
72 72 61 79 03 6E 65 74 07 6E 65 74 77 6F 72 6B rray.net.network
07 6E 69 4E 6F 64 65 32 07 6E 69 4E 6F 64 65 32 .niNode2.niNode2
0C 21 00 3C 00 00 00 FD 01 10 00 00 00 30 00 00 .............0..
04 03 64 65 66 0A 6D 69 63 72 6F 61 72 72 61 79 ..def.microarray
03 6E 65 74 07 6E 65 74 77 6F 72 6B 03 6E 69 52 .net.network.niR
03 6E 69 52 0C 3F 00 16 00 00 00 05 01 10 1F 00 .niR.?..........
00 38 00 00 05 03 64 65 66 0A 6D 69 63 72 6F 61 .8....def.microa
72 72 61 79 03 6E 65 74 07 6E 65 74 77 6F 72 6B rray.net.network
07 6D 61 4E 6F 64 65 31 07 6D 61 4E 6F 64 65 31 .maNode1.maNode1
0C 21 00 3C 00 00 00 FD 01 10 00 00 00 38 00 00 .............8..
06 03 64 65 66 0A 6D 69 63 72 6F 61 72 72 61 79 ..def.microarray
03 6E 65 74 07 6E 65 74 77 6F 72 6B 07 6D 61 4E .net.network.maN
6F 64 65 32 07 6D 61 4E 6F 64 65 32 0C 21 00 3C ode2.maNode2....
00 00 00 FD 01 10 00 00 00 30 00 00 07 03 64 65 .........0....de
66 0A 6D 69 63 72 6F 61 72 72 61 79 03 6E 65 74 f.microarray.net
07 6E 65 74 77 6F 72 6B 03 6D 61 52 03 6D 61 52 .network.maR.maR
0C 3F 00 16 00 00 00 05 01 10 1F 00 00 05 00 00 .?..............
08 FE 00 00 32 00 05 00 00 09 FE 00 00 32 00 ....2........2.

.... and it freezes here.

 
Reply With Quote
 
 
 
 
dnikolayev@gmail.com
Guest
Posts: n/a
 
      02-21-2007
Try to use DBD::mysql throught DBI.
Net::Mysql was never the best method to "talk" with mysql..
Also, maybe you have a lot of data in DB and your query takes too long?

 
Reply With Quote
 
J. Gleixner
Guest
Posts: n/a
 
      02-22-2007
Lee wrote:
> I set debug=true and got this information in case it helps anyone help
> me...
>
> Net::MySQL::_execute_command():
> 7A 03 00 00 03 53 45 4C 45 43 54 20 44 49 53 54 z....SELECT.DIST

[...]
How would that help anyone???...

>
> ... and it freezes here.


Maybe it's running your query and waiting for results.

Check your database to see what's running.

Try using DBI.
 
Reply With Quote
 
Lee
Guest
Posts: n/a
 
      02-22-2007
> [...]
> How would that help anyone???...

No clue!

>
> Try using DBI.


So... what is the consensus on what to use? DBD::mysql? or the class
for DBI?

 
Reply With Quote
 
Tad McClellan
Guest
Posts: n/a
 
      02-23-2007
Lee <(E-Mail Removed)> wrote:
>> [...]
>> How would that help anyone???...

> No clue!
>
>>
>> Try using DBI.

>
> So... what is the consensus on what to use? DBD::mysql? or the class
> for DBI?



I am quite sure the consensus is to use DBI.pm.


--
Tad McClellan SGML consulting
http://www.velocityreviews.com/forums/(E-Mail Removed) Perl programming
Fort Worth, Texas
 
Reply With Quote
 
J. Gleixner
Guest
Posts: n/a
 
      02-23-2007
Lee wrote:
>> [...]
>> How would that help anyone???...

> No clue!
>
>> Try using DBI.

>
> So... what is the consensus on what to use? DBD::mysql? or the class
> for DBI?


ahhh.. you'd use/install both. DBI is the interface, DBD is the driver.

You'd 'use' DBI, and when it calls its 'connect' method, you
specify what driver it should use. That way you could write
all of your code to use the DBI methods, then, if your
database changes, you might simply have to change only
the driver you specify and that's it.

Really though, since other queries seem to work, it's
likely that it's running and sending back the results.
Maybe it's waiting until the results are all
sent back. Try doing an EXPLAIN on your SQL, or
maybe add "limit 1" to your SQL. In addition,
you could watch your process, as it's running, to
see if it's using more and more memory (storing
the results) or if it's just sitting there
(waiting for results). And check to see what
the database is doing (show processlist).

As a quick test, you could save your SQL in a
file (file.sql) , and use the mysql client to
run your query.

% mysql -u someuser -psomepassword -h some.host.com some_database <
file.sql > output

If that runs within some expected time, then try DBI,
if the above exhibits the same problem, then your
SQL is at fault and the EXPLAIN should help
figure that out.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
PythonWin debugger holds onto global logging objects too long Rob Richardson Python 3 02-07-2012 11:04 PM
Authentication cookie holds old session id =?Utf-8?B?YnJpYW5k?= ASP .Net 0 02-18-2005 09:29 AM
Photo quality - onto paper or onto CD? Theseeker Digital Photography 8 10-04-2004 08:59 PM
Is Table Width Required for Table that Holds Image? Earl Teigrob ASP .Net 2 02-21-2004 09:28 PM
what file holds registry info in xp Deb Computer Support 2 11-07-2003 12:40 AM



Advertisments