DB Maintenance

Reference Integrity

Clicking the "Reference Integrity" menu item allows you to verify your database reference integrity with an ability to delete redundant data as well as records that refer to non-existent table records.

To illustrate the above, here is what would happen if we deleted the "AOL 7.x" record from the "VALUE" table via executing the DELETE FROM SS_VALUE WHERE VALUE = 'AOL 7.x' query for these browser visits to refer to a non-existent record:

The example features some tables shown as links: SS_MK_RULE (4), SS_VISIT (3) and SS_PARAM (32). If some table from the left column is shown as a link, it means that it has records that aren't referred to by any record in the corresponding tables from the right column. If some table from the right column is displayed as a link, it means that the table has records referring to non-existent records in the corresponding table from the left column. So, it is normal to have links in the left column, but if we have links in the right column - it means that something is going wrong. Actually, if a table is displayed as a link, we really have two links where the first one (table name) allows you to view records (and then delete them or return), and the second one (integer number in parentheses) - to just delete these records. The number represents the records count. If we click the "SS_PARAM" table link, we'll see the following:



As a result, our defined "quote" parameters are displayed (they don't have visits assigned to).

If we click the "SS_VISIT" table link, the situation will be like this:

If we press the "DELETE" button, these records will be deleted, but in this case we may well get broken references in other tables due to not having checked the reference integrity in process of such deletion.

So, after deleting we have got broken references in the "SS_USED_RESOURCE" and "SS_VISIT_PROPERTY" tables. You should delete them as well because all records displayed refer to the records in the "SS_VISIT" table we have just deleted. To delete such records in more than one table with a single click, we can use the "DELETE BROKEN" button. This way the records in the "SS_PARAM" table (and other tables in the left column) will be preserved.



Following this operation we'll get a database with no broken references whatsoever (i.e. the right column free of any links).

Starting from version 2.3.4, the way the 'Reference Integrity' page now behaves depends on the total number of records in the database as well as on how many broken or redundant records there are in the db table. The reason is in the fact that from now on it is possible to set a maximum number of db records for the 'Reference Integrity' page to begin displaying calculated integrity data for just a single table dependence per one query if the above number is exceeded. This helps reduce script execution time and prevent its possible cancellation due to a timeout thanks to the script using less system resources when doing just a part of the entire set of calculations.

Besides, there is an ability to set another limit on the number of broken or redundant links in a table so that references to such useless information are no longer displayed as soon as a predefined value is exceeded. This way the user will not be able to request calculations involving huge amounts of records and face risks of failure due to timeout or insufficient resources.

The mentioned max values are defined via two constants in the cfg/ui_const.cfg.php config file: ALLOW_ALLTABLEINFO_MAXROWS and ALLOW_BROKENDETAILS_MAXROWS.
Let us change the default values for them as follows:

define('ALLOW_ALLTABLEINFO_MAXROWS', '1250');

define('ALLOW_BROKENDETAILS_MAXROWS', '3');

Then again click the 'REFERENCE INTEGRITY' menu for the report to look differently, like on the screenshot below:



Now, because the total db records number (1267) is greater than the value set for ALLOW_ALLTABLEINFO_MAXROWS, the application will display information for just a single line in the report. As to links placed in the left column, they will allow verifying the state of integrity for a selected dependence. So, if, for example, you click the 'SS_PARAM' link, the report will be generated for just the selected dependence:



Please note that there is no link for displaying redundant records in the SS_PARAM table, because the total of such records (32) is greater than the value preset for the ALLOW_BROKENDETAILS_MAXROWS constant (3).

The next thing you can do is delete all the redundant records in the SS_PARAM table by clicking the link being the total number of such records. This will make the report look as follows:



After deletion of the above 32 records the total number of db records has grown less than the maximum set by the ALLOW_ALLTABLEINFO_MAXROWS constant (3), so from that moment on the report will be generated for all the dependences in the table. However, here again you should remember that the limiting option as regards displaying stats for broken/redundant records remains enabled. The example below shows that their display is 'on' for the SS_RESOURCE (1), SS_RF_HOST(2) and SS_VALUE(1) tables, because the number in brackets does not exceed the maximum set in ALLOW_BROKENDETAILS_MAXROWS = 3, while for SS_MK_RULE(4) the display is 'off'.

Also note that the functionality of the 'DELETE ALL' and 'DELETE BROKEN' buttons does not depend on a current report mode. In any case clicking them will make sure that all the broken and redundant records or just broken records respectively in all tables are deleted.

Delete Info

Using this section you can delete statistics information from the database keeping its integrity intact. The information can be removed with the help of one of the three separate forms, each of them allowing deletion of data portions if they satisfy the predefined criteria.

The delete operation can be executed both right upon a form has been confirmed, and later as a delayed scheduled task. The delete mode is managed by the 'Delete Now' checkbox, which is present on all the three forms.

If your database contains large amounts of records, any deletion of information from such db will require much time. With the 'Delete Now' checkbox enabled, it'll be up to the delinfo.php script to perform deletion upon a query having been processed by a web server. However, chances are that such operation will fail due to a timeout. So it's recommended you uncheck 'Delete Now' if dealing with a large sized database. This way, after the form has been confirmed, the application will schedule deletion using parameters defined, while the operation itself will be executed by the jobs.php script launched by the scheduler (see Server Part Installation, item 8).

You can also fine tune the jobs.php script execution parameters to better match the capacity and resources of your server and database by specifying them in the command file (jobs.sh or jobs.cmd), directly responsible for launching jobs.php.

Please note that once the delete operation has been initiated, it is impossible to either start or schedule another deletion with different parameters, nor can you cancel an ongoing or scheduled deletion. At the same time, if a deletion has been scheduled but not yet started, what you can do is re-schedule it with different parameters.

If for some reason the scheduled delete operation fails, further deletion will not be possible as well, because they're only allowed provided the previous operation has been done successfully. To avoid this problem, it's recommended that you specify the time following which further deletions will become possible. This can be done in the cfg/cron.cfg.php file, parameter $stat_cron_params['cleanup']['timeout'].

The "DELETE VISITS INFORMATION" form allows you to delete visits information which includes: visits records, "resource use" records and visit parameter records. The information that becomes unreferred is deleted as well: resources, resource groups, visitors, parameter values, referral hosts. There are three conditions available to determine which information is going to be deleted. These are:

• Hosts (host info to be deleted);
• Sites (site info to be deleted);
• Date (info to be deleted dated before the specified date).

The "DELETE SITE INFORMATION" form allows you to delete all information related to selected sites. The information includes: visits records, "resource use" records, visit parameter records, rules and site records. The information that becomes unreferred is deleted as well.

The above example (form 2) demonstrates an attempt to immediately free the database of all the information on the "Softerra LLC" site (ID=1).

The "DELETE PARAMETER INFORMATION" form allows you to delete all information related to parameters selected or their tracked values. For parameters, the information that can be deleted is: parameters, parameter rules, parameter values and visit parameter records. For tracked values, only visit parameter records are deleted. The information that becomes unreferred is deleted as well.

To delete all information on certain parameters, you should select those first. There are two extra conditions available when trying to delete tracked values info only. They are:

• Sites (site visit info to be deleted);
• Date (info to be deleted dated before the specified date).