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

Okay, this is a strange one.

I have a perl script that generates a user report for our site by hitting the MySQL database. Normally, I would use straight SQL to generate the report, but that query takes FOREVER because you wind up joining a particular table to itself several times. (For the record, I didn't design this, we're using drupal.)

Now, the report runs fine on the test and staging servers. However, when I move to the production server, there is a problem. At one point, I build a query to his the user profile tables to grab a value for the user:

  # Open cursor
  $imm_qy = "SELECT pv.value FROM profile_values pv, profile_fields pf
+ WHERE pv.uid = $uid and pv.fid = pf.fid and pf.name = 'profile_magaz
+ine'";

print "\$imm_qy is $imm_qy .\n";
  $immh = $dbh->prepare($imm_qy) or die "Cannot prepare magazine query
+: " . $dbh->errstr . "\n";
  # Execute  query
  $immh->execute or die "Cannot execute magazine query: " . $dbh->errs
+tr . "\n";
  $imm = $immh->fetchrow_array;
  $immh->finish;
  print "For uid of $uid, \$imm is $imm .\n";

[download]

Note: the prints are in there to make sure that I'm acutally building these queries correctly, and apparently I am: 

imm_qy is SELECT pv.value FROM profile_values pv, profile_fields pf WH
+ERE pv.uid = 17 and pv.fid = pf.fid and pf.name = 'profile_magazine' 
+ .
For uid of 17, $imm is  .

[download]

This is odd, because $imm should have a value at this point. In fact, if I go into a mysql session and copy-and-paste that command, I get the value of '1'. But for some users, it returns nothing. And here's the kicker: it's always the same users and it is 100% repeatable. So it must be an environment issue between the machines.

The test and staging server are identical, using Perl 5.8.6; MySQL Ver 14.7 Distrib 4.1.20, for redhat-linux-gnu (i386) using readline 4.3; and the same version of Red Hat Linux. Ditto for the production server. The only difference I can find is that the production server is using 1.50 of DBI, while the test and staging servers are using 1.48. Now, does ANYONE know of ANYTHING between 1.48 and 1.50 of DBI that *might* cause something like this? I am absolutely at a loss.

Thanks in advance.

--
tbone1, YAPS (Yet Another Perl Schlub)
And remember, if he succeeds, so what.
- Chick McGee

Replies are listed 'Best First'.
Re: DBI and MySQL issue
by kyle (Abbot) on May 02, 2007 at 20:26 UTC

    I had a look at DBI::Changes, and nothing jumped out at me, but you might have better luck since you know your environment better than I do.

    I notice you're calling fetchrow_array in a scalar context, which the DBI documentation says is a bad idea for various reasons. I'd make that line be: 

    ($imm) = $immh->fetchrow_array;

    [download]

    That said, I don't see why the code you have wouldn't work as it is.

    The only other suggestion I have is to set the RaiseError attribute on your database handle. It'll save you from checking for errors all the time, and you'll know if fetchrow_array (in scalar context) is returning a NULL from the database or undef because it had a problem.

[reply]
[d/l]
[select]
Re: DBI and MySQL issue
by Krambambuli (Curate) on May 02, 2007 at 20:36 UTC
    Just an idea: may it still be an DB issue, not necessarily an environmental one ?

    Is it possible that it is just 'too early' when you're making the SELECT, and that all goes well if you sleep() a while before that ?

    Is it during the same session that the INSERT/SELECT sequence happens ?

    I suspect that it might be possible that the problem is due to MySQL being late for some reason to let you see the just inserted/updated value.

    Hope that helps.
[reply]
      I've been inclining that way. As I said, it is always the same user IDs, 100% repeatable. However, given that I've caught some junk for using Perl to do this, I wanted to get advice from my fellow monks before I start pointing fingers and shouting "J'accuse! at these young punk whippersnappers with their Ruby on Rails and fax machines and hula hoops.

      --
      tbone1, YAPS (Yet Another Perl Schlub)
      And remember, if he succeeds, so what.
      - Chick McGee

[reply]
Re: DBI and MySQL issue
by nedals (Deacon) on May 02, 2007 at 22:58 UTC
    I get concerned when I see... (and I'll guess that you are not using 'strict') 
      $immh->execute .....;
  $imm = $immh->fetchrow_array;

  ## as opposed to 
  $immh->execute() ....;
  $imm = $immh->fetchrow_array();

    [download]
    That said, you might consider using this instead. 
    # Open cursor
$imm_qy = "SELECT pv.value FROM profile_values pv, profile_fields pf
            WHERE pf.name = 'profile_magazine' AND pv.uid = $uid AND p
+v.fid = pf.fid";
print "\$imm_qy is $imm_qy .\n";
$immh = $dbh->selectrow_array($imm_qy) or die "Cannot prepare magazine
+ query: " . $dbh->errstr . "\n";

    [download]
    Note that I changed the order of the where clause that may reduce the number of lines processed and speed up the query. You also may want to index the pf.name column. Using text in a where clause is generally slow.
[reply]
[d/l]
[select]
      I get concerned when I see... (and I'll guess that you are not using 'strict')

      strict has nothing to do with leaving parentheses off of method calls. The method names are not barewords; they're unambiguously method calls.

[reply]
[d/l]
      And while we are speaking about the query design: Avoid cross joins if possible. Cross joins explode the tables first and apply the "where" clauses afterwards. There is no inherent reason for a cross join as far as I can see.
[reply]
        I have to do it because of the database design (like I said, it's a drupal-based web site), and because, well, because it shouldn't be a big deal to do that. The tables aren't big, they're indexed, and it's probably less overhead than multiple queries. Is there something in MySQL, or not in MySQL, that I should know about? I've been doing this in Ingres/Informix/Oracle for longer than I'd care to admit, so I don't see why it's a big deal.

        --
        tbone1, YAPS (Yet Another Perl Schlub)
        And remember, if he succeeds, so what.
        - Chick McGee

[reply]
        Howdy!

        What are you talking about?

        On the face of it, the query looks like a simple join with suitable criteria. There is no cartesian product begin generated.

        How would you rephrase the query to avoid the problem you allege exists?

        yours,
        Michael
[reply]
Re: DBI and MySQL issue
by Moron (Curate) on May 03, 2007 at 13:28 UTC
    Do you get a '0' or still ' ' if you put a count() around the column being selected in the "offending" SQL? (this would confirm if the problem is with the Perl or with the database).
    __________________________________________________________________________________

    ^M Free your mind!

[reply]
      *slaps forehead* Thanks for the idea. I'm actually getting 0 counts. That is just ... strange.

      --
      tbone1, YAPS (Yet Another Perl Schlub)
      And remember, if he succeeds, so what.
      - Chick McGee

[reply]
        A general suggestion: Try doing some other things that you're pretty sure should work, even though you don't see why they should be necessary.

        For example, you could try creating a duplicate table and then try your queries again against the duplicate.

        Since you say the tables aren't terribly large, you could try making multiple copies of the tables, and then do joins between different tables with identical data, instead of trying to get self-joins to work.

        Mysql myiasm tables are capable of much weirdness, so don't just *assume* the problem is on your end...

        And by all means, run with RaiseError on, and use "use strict"... this might flag a problem you're missing.

[reply]
Re: DBI and MySQL issue
by perrin (Chancellor) on May 03, 2007 at 11:45 UTC
    Are you using InnoDB tables or MyISAM?
[reply]

Back to Seekers of Perl Wisdom