I have run into a problem in my web application where I create an entry in the database, then move to another area of the site and list the rows and my created entry is missing. If I use the command line mysql or query-browser I see that the entry was created....So the only thing I can currently guess is that this is caused by the mysql query cache. I'm creating the entry using transactions. I wondered if there is a best practices way of checking/clearing the cache when a new entry has been created.

The following is how the entries are created as well as the list_all attempt to grab the rows. 
#*********************************************************************
+********
# public create
# *************
# Uses:     Saves the given object into the database  
# Params:    HASH with new object attributes
# Return:    The inserted object, or undef if the Object can't be inse
+rted 
#*********************************************************************
+********
sub create {
    
    my $class = shift;
    my ($param) = @_;
    my $dbh = Jnms::Database::Control::DatabaseManager->get_database->
+{dbh};
    
    my $id;
    my $error;
    # check if the required fields are there and insert on the db
    #print STDERR Dumper($class,$param);
    $class->_check_required_create_fields($param);
    eval{
        $id = $class->_insert_into_database($param);
        $dbh->commit;
    };
    if ($@) {
             warn "Transaction aborted because $@";
             $error = $@;
             # now rollback to undo the incomplete changes
             # but do it in an eval{} as it may also fail
             eval { $dbh->rollback };
             die "Transaction aborted because of $error";
             # add other application on-error-clean-up code here
    }    
    #return the inserted object
    return $class->find($id);    
}
#*********************************************************************
+********
# public list_all
# ***************
# Uses:     gets all Objects that matches the child class type from th
+e db  
# Params:    None.
# Return:    an ARRAY of all Objects of the requested type 
#*********************************************************************
+********
sub list_all {
    
    my $class = shift;
    my ($param) = @_;
    
    my $dbh = Jnms::Database::Control::DatabaseManager->get_database->
+{dbh};
    # get the constants specified on the child class
    my $columns = join(', ', $class->TABLE_COLUMNS);
    my $table = $class->TABLE_NAME;
    my $order = $class->LIST_ORDER;
    my @column_values = ();
    
    my $objects = $dbh->selectall_arrayref("SELECT $columns FROM $tabl
+e ORDER BY $order", {Slice=>{}}, @column_values);
    
    bless($_, ref($class) || $class) foreach @$objects;
    
    my $depth = 0;
    if (ref $param eq 'HASH') {
        $depth = $param->{depth};
    }
    elsif($param) {
        $depth = $param;
    }
    unless(defined $depth){ $depth = 0; }
    $objects = $class->_find_foreign_keys($objects, $depth);

    return $objects;
}

[download]

In reply to Mysql query cache and DBI by josh803316

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.