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

I have a MySql database with a table(s) containing dated records. I use a PERL cgi to allow visitors to my site to search the database according to various parameters and then output a results list. That works fine. However at the beginning of the program I want to open the database and delete records that are older than 30 days in the target table. I've tried every piece of code from every tutorial that I can find on the Internet such as DELETE FROM table WHERE DATE (NOW (), date) >30 or DELETE FROM table WHERE DATE < DATE_SUB(NOW(), INTERVAL 30 DAY) and many others. NONE work. I continually get some kind of error message.

Can anyone help?

Thanks in advance for any guidance that is provided!

Replies are listed 'Best First'.
Re: Deleting Old MySql Records With PERL
by perlfan (Parson) on Jul 13, 2016 at 21:02 UTC
    If your the column with your date in it is really called date, you're gonna have a bad time. Try putting the column name date in back ticks:
    (untested, but exemplifies what I am saying): 
    DELETE FROM table WHERE `date` < DATE_SUB(NOW(), INTERVAL 30 DAY);

    [download]
    Furthermore, make sure that column `date` is of the right column type (DATE or DATETIME). Finally, I test my SQL using the mysql client; if it succeeds there, it should succeed in your Perl script.
[reply]
[d/l]
[select]

      I tried the suggested code in my program and it didn't work. I have changed the Date column name to Posted and am using this code

      my $dbh = DBI->connect('dbi:mysql:owner_db','searcher','searcherpasswd
+')
or die "Connection Error: $DBI::errstr\n";
$sth = $dbh->prepare ("DELETE FROM my table WHERE POSTED < DATE_SUB(NO
+W(), INTERVAL 30 DAY)");
$sth->execute ();

      [download]

      I have also tried the same code with the mysql client. In both cases the 30 DAY interval is ignored and everything is deleted.

      Any suggestions?

[reply]
[d/l]

        What is the data type of the Posted field ?

        poj
[reply]
Re: Deleting Old MySql Records With PERL
by neilwatson (Priest) on Jul 13, 2016 at 19:46 UTC

    One. that's an SQL question not a perl question. Two, please provide actual error messages and repeatable code.

    Neil Watson
    watson-wilson.ca

[reply]

      Here's some actual code from the cgi.

      #/usr/bin/perl -w
use DBI; 
use CGI ':standard'; 
print "Content-type: text/html\n\n";

my $dbh = DBI->connect('dbi:mysql:my_db','searcher','searcherpasswd')
or die "Connection Error: $DBI::errstr\n";
##DELETE FROM my table WHERE DATE(NOW(), date) >30;
DELETE FROM my table where now()-d >interval '30days';

      [download]

      Here are some error messages. The first is when using the 'Commented Out' piece of code. The others are when using the other piece of code. 

      Undefined subroutine &main::NOW called at e:\owner\website \cgi-bin\se
+arch_ specialty.pl line 13

[Wed Jul 13 15:26:07 2016] [error] [client 72.208.178.63] String found
+ where operator expected at e:\owner\website \cgi-bin\search_ special
+ty.pl line 14, near "interval '30days'"

[Wed Jul 13 15:26:07 2016] [error] [client xx.xxx.xxx.xx]     (Do you 
+need to predeclare interval?)

Syntax error at e:\owner\website \cgi-bin\search_ specialty.pl line 14
+, near ")-d "

Execution of e:\pinnacle\website\cgi-bin\search_specialty.pl aborted d
+ue to compilation errors.

      [download]

      According to advise from several tech sites the code should work. But, it doesn't.

[reply]
[d/l]
[select]
        In addition to what perlfan said about SQL syntax, you also have a problem with perl syntax.

        The DELETE statement is SQL, not perl. As such, it needs to be quoted, and passed to $dbh->do("SQL here");

        Be careful to properly escape quotes, when passing SQL to do().

                There is no time like the present for postponing what you ought to be doing.

[reply]

        Your code doesn't pass any queries to the database to be executed.

        It's unnecessarily difficult to read that post as it stands. Please edit it and enclose both the code and the error messages in <code> tags. Thank you.

[reply]
[d/l]
Re: Deleting Old MySql Records With PERL
by chacham (Prior) on Jul 14, 2016 at 14:39 UTC

    DELETE FROM table WHERE DATE (NOW (), date) >30

    That's a terrible approach; it evaluates the expression for each record.

    DELETE FROM table WHERE DATE < DATE_SUB(NOW(), INTERVAL 30 DAY)

    That is the correct approach. Evaluate the expression just once, and do a simple comparison.

    Run the statement from the console to determine if it is working. If it is not, break it apart and figure out why. Obviously, "DATE_SUB(NOW(), INTERVAL 30 DAY)" should be checked to work. Look at the documentation if you do not understand DATE_SUB()

    After you are clear that it should work, try it within your script, where the issues would mainly be connectivity or rights related, having already solved any syntactical or type issues.

[reply]

      Thanks for all the feedback. I believe I now have it figured out.

      I added a column to the table and named it DateAdded with Type - TIMESTAMP, NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. The comparison you suggested now seems to work in that it does not delete rows that were placed in the table today. After a couple of days I will change the INTERVAL to 2 or 3 days and see what happens when I try the query in the Mysql client. I still have a column named 'Posted' which contains a date in simple English, i.e. Month Day, Year.

[reply]

Back to Seekers of Perl Wisdom