Bug 11945 - Cronjob delete_expired_opac_registrations.pl fails with error in SQL syntax
Summary: Cronjob delete_expired_opac_registrations.pl fails with error in SQL syntax
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Command-line Utilities (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Marcel de Rooy
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-03-14 17:40 UTC by Larry Baerveldt
Modified: 2015-12-03 22:11 UTC (History)
5 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 11945: SQL syntax error in delete_expired_opac_registrations.pl (3.18 KB, patch)
2014-10-09 09:41 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 11945: SQL syntax error in delete_expired_opac_registrations.pl (3.19 KB, patch)
2014-10-09 15:05 UTC, Chris Cormack
Details | Diff | Splinter Review
Bug 11945: SQL syntax error in delete_expired_opac_registrations.pl (3.26 KB, patch)
2014-10-11 20:45 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 11945: The script should die if the pref are not filled (1.09 KB, patch)
2014-10-11 20:45 UTC, Jonathan Druart
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Larry Baerveldt 2014-03-14 17:40:26 UTC
When running the cronjob, delete_expired_opac_registrations.pl on a 3.14 site, I get the following error message:

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 6 at /usr/share/koha/bin/cronjobs/delete_expired_opac_registrations.pl line 76.

Checking the script, I find this SQL code:

my $query = "
    SELECT borrowernumber
    FROM borrowers
    WHERE
        categorycode = ?
      AND
        DATEDIFF( DATE( NOW() ), DATE(dateenrolled) ) = ? )

If I run that SQL statement in the database manually, I get the same error message, e.g.:
mysql> select borrowernumber from borrowers where categorycode='SELF' AND DATEDIFF( DATE( NOW() ), DATE(dateenrolled) ) = 7 );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

It would appear there's some problem with the DATEDIFF piece of the SELECT statement.

- Larry
Comment 1 Marcel de Rooy 2014-10-09 09:41:20 UTC Comment hidden (obsolete)
Comment 2 Chris Cormack 2014-10-09 15:05:14 UTC Comment hidden (obsolete)
Comment 3 Jonathan Druart 2014-10-11 20:45:02 UTC
Created attachment 32273 [details] [review]
Bug 11945: SQL syntax error in delete_expired_opac_registrations.pl

This patch corrects a syntax error, adds some comments to the usage
instructions and adds a count of the removed borrowers (with verbose
flag on).

Note that this is a dangerous script. It will delete your patrons
in patron category PatronSelfRegistrationDefaultCategory.
If you do not use this as a temporary category, you should NOT run this
script.

Test plan:
Check PatronSelfRegistrationDefaultCategory.
Check PatronSelfRegistrationExpireTemporaryAccountsDelay.
Based on these two settings, check the number of patrons to be deleted (date
enrolled should be before NOW minus the delay).
Backup your data and run the script.
Check the number of deleted borrowers.

Signed-off-by: Chris Cormack <chris@bigballofwax.co.nz>

Signed-off-by: Jonathan Druart <jonathan.druart@biblibre.com>
Comment 4 Jonathan Druart 2014-10-11 20:45:08 UTC
Created attachment 32274 [details] [review]
Bug 11945: The script should die if the pref are not filled

If the 2 prefs are not defined, the script should die.

Signed-off-by: Jonathan Druart <jonathan.druart@biblibre.com>
Comment 5 Tomás Cohen Arazi 2014-10-24 16:27:12 UTC
Patches pushed to master.

Thanks Marcel and Jonathan!