Lasso Cleanup Script

The tip of the week for April 27, 2007 includes a script which can be used to periodically clean a number of tables in Lasso's internal databases. These include the error log table, email queue, and other tables which can grow over time.

Introduction

Lasso's internal databases include a number of tables which collect log entries and can grow over time. When these databases are very large they can impact the performance of Lasso. The Lasso_Cleanup.Lasso script can be used to clean these tables so they contain only the most recent entries.

The options map at the top of the script can be used to modify the number of messages which will be kept from each table.

Lasso Errors - The "errors" table (or "_errors" table) in the Lasso internal database stores logged error messages. In recent versions of Lasso this table is automatically truncated when Lasso starts up so only the latest 1,000 messages are stored.

In Lasso 8.5 two additional tables, both in the "site" database, store error messages. Errors which are generated by the event scheduler are stored in a "schedule_error" table errors whic are generated by the email sending system are stored in an "smtp_error" table.

The Cleanup Script will automatically truncate the main errors table in any version of Lasso and also the schedule and email errors tables in Lasso 8.5.

The "errors_max" option controls how many records will be kept from each errors table. By default the script is set to keep the most recent 1,000 messages. If the option is set to 0 then no messages will be kept. If the option is set to -1 then all messages will be kept (none will be deleted).

Email Queue - The email queue table is used to store messages which are waiting to be sent. After a message send has been attempted several times the message is marked as an error. Normally, the email queue will contain only queued messages and messages which have errored out. The email preferences can also be set to keep a record of all sent messages.

Over time the email queue table can collect quite a number of error messages and sent messages. In extreme cases this can impact the performance of Lasso. If you notice it taking more than a few milliseconds to queue email messages then it could be because the email queue table has grown very large.

In Lasso 8.5 there are actually three tables. The "smtp_queue" table holds queued messages. The "smtp_stage" table holds messages parts. And, the "smtp_error" table holds error messages from message send attempts. Lasso 8.0 includes only the "smtp_queue" table. Earlier versions of Lasso used an "_outgoingemail" table.

The Cleanup Script will automatically truncate the email queue table in any version of Lasso and also the additional email tables in Lasso 8.5.

The "email_sent_max" and "email_error_max" options controls how many records will be kept from the email queue table. By default the script is set to keep the most recent 1,000 messages of each type. If either option is set to 0 then no messages of that type will be kept. If either option is set to -1 then all messages of that type will be kept (none will be deleted).

Download

You can download the Lasso_Cleanup.Lasso script from the following URL. The page can be placed into your Web server root and loaded through a Web browser. Or, the page can be placed in LassoStartup within the Lasso application folder so that it will run each time Lasso starts up.

Implementation

The implementation of the script can be seen in the script itself. The script includes different code blocks for Lasso 8.5, earlier versions of Lasso 8, and Lasso 6/7. Each code block is tuned for the table schemas of that version of Lasso.

The goal of the code for each table is to delete all messages except for the most recent 1,000 (by default). This is done by finding all records (using criteria if necessary to identify only records of a particular type) and then using LIMIT to skip the first 1,000 ordered by the "id" field in descending order.

 SELECT id FROM error ORDER BY id DESC LIMIT 1000,1

The first 1,000 records in this order are the ones we want to keep so the "id" returned by this statement identifies the first record we want to delete. Then, we simply delete all records which have an "id" less than or equal to this field value.

  DELETE FROM schedule_error WHERE id <= [field: 'id']

The code for the email tables is similar except a WHERE clause is used to check the status of the messages and to only delete "sent" or "error" messages.

The code for the email tables in Lasso 8.5 is a little different. Instead of simply deleting all the records with lower values in their "id" field, those values are put into an array. This allows the related records from the stage and error tables to be deleted before the main records in the queue table.

More Information

More information about all of the tags used in the downloadable script can be found in the Lasso 8.5 Language Guide or in the online Lasso Reference.

Author: Fletcher Sandbeck
Created: 27 Apr 2007
Last Modified: 16 Mar 2011