net::mysql holds onto my query

Discussion in 'Perl Misc' started by Lee, Feb 20, 2007.

  1. Lee

    Lee Guest

    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;
    Lee, Feb 20, 2007
    #1
    1. Advertising

  2. Lee

    Lee Guest

    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.
    Lee, Feb 21, 2007
    #2
    1. Advertising

  3. Lee

    Guest

    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?
    , Feb 21, 2007
    #3
  4. Lee

    J. Gleixner Guest

    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.
    J. Gleixner, Feb 22, 2007
    #4
  5. Lee

    Lee Guest

    > [...]
    > 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?
    Lee, Feb 22, 2007
    #5
  6. 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?



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


    --
    Tad McClellan SGML consulting
    Perl programming
    Fort Worth, Texas
    Tad McClellan, Feb 23, 2007
    #6
  7. Lee

    J. Gleixner Guest

    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.
    J. Gleixner, Feb 23, 2007
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Earl Teigrob
    Replies:
    2
    Views:
    313
    Shintaro
    Feb 21, 2004
  2. =?Utf-8?B?YnJpYW5k?=

    Authentication cookie holds old session id

    =?Utf-8?B?YnJpYW5k?=, Feb 18, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    346
    =?Utf-8?B?YnJpYW5k?=
    Feb 18, 2005
  3. jimjim
    Replies:
    18
    Views:
    7,353
    Default User
    Apr 12, 2004
  4. Rob Richardson
    Replies:
    3
    Views:
    254
    Mark Hammond
    Feb 7, 2012
  5. Love4llamas
    Replies:
    0
    Views:
    799
    Love4llamas
    Oct 13, 2011
Loading...

Share This Page