Perl DBI on MySQL issue... select statement not...

Discussion in 'Programming' started by marty, Mar 17, 2006.

0
  1. #1
    Hey Everyone,

    I maintain a legacy Perl on MySQL intranet application and for some reason last night it suddenly quit returning all of the results from a simple Select statement.

    The statement is something like:

    "Select brand_id, brand_name from brands order by brand_name"


    It's supposed to get about 2000 records back, but it suddenly started just getting around 400 records back, and it stops on the same record every time.

    Here's what I've done to troubleshoot so far:

    1. Checked the DB and all of the records are still stored... the DB table has not been corrupted.
    2. Tried different browsers (IE, Opera, and FF) and all stopped on the same record... so this doesn't appear to be a browser memory issue.

    What's really strange is that when I change the SQL to:

    "Select brand_id, brand_name from brands order by brand_name desc"

    it returns all 2000 records.

    Since I haven't changed any of the Perl libraries and since the code hasn't changed, it seems like it must be some kind of problem in MySQL. I could code around this problem, but I am concerned that this might be an early indication of some kind of meta data corruption.

    Anyone have any thoughts about this?
     
    marty, Mar 17, 2006 IP
  2. clancey

    clancey Peon

    Messages:
    1,099
    Likes Received:
    63
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Have you tried running it from the command line with the -w switch?

    This generates lots of error messages, some of which may be helpful in tracking down the problem. Have you examined the data entries for that record?

    I have been doing the opposite, creating a MySql database from an old xbase system, and I ran into a similar situation with the program dieing on certain records. One was caused by empty data and the other by unescaped characters.
     
    clancey, Mar 17, 2006 IP
  3. marty

    marty Peon

    Messages:
    154
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thanks clancey, the wierd thing is that the data started returning the complete recordset the next day (after additional records we inserted). I think it had to have been some kind of meta data issue that resolved itself when another record was inserted.
     
    marty, Mar 22, 2006 IP
  4. rainborick

    rainborick Well-Known Member

    Messages:
    424
    Likes Received:
    33
    Best Answers:
    0
    Trophy Points:
    120
    #4
    My advice would be to try eliminate or at least reduce the possibility that this is a problem with MySQL or the database. Run the same query through phpMyAdmin or a simple script in your language of choice other than Perl, and check the results against what you're seeing in Perl. If the non-Perl tool shows the expected results, then you can go back to looking at the Perl script or the DBI/MySQL modules for errors.
     
    rainborick, Apr 1, 2006 IP
  5. mad4

    mad4 Peon

    Messages:
    6,986
    Likes Received:
    493
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Maybe your host was making some changes? Upgrading MySQL can alter the way things work quite a lot.
     
    mad4, Apr 2, 2006 IP
  6. marty

    marty Peon

    Messages:
    154
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Hey mad4 & rainborick,

    mad4 - This MySQL db is running locally on my wife's laptop. I'm 99.9999% sure that it wasn't being changed. But that's a good thought... thanks.

    rainborick - thanks for the idea. Unfortunately (or maybe fortunately) I changed the query so that my wife could continue working and then when I went back to the store the next day the problem resolved itself. I used the MySQL Query Browser tool and the query returned the proper data. I think it might have started working again when my wife inserted a new record. I think some meta data in MySQL got corrupt and then fixed itself when a new record was inserted.

    Thanks again to both of you :)

    Marty
     
    marty, Apr 3, 2006 IP