1 Topic by Francesco Ferretti

  • Francesco Ferretti
  • Member
  • Offline
  • Registered: 19-06-2012
  • Posts: 8

Topic: Data deletion

Dear all,
On the last months we asked to the forum how to completely delete records from both the occurrence/sample list and the maps in the website. With the help of Jim Bacon, we successfully deleted our records, which disappeared from both the occurrence/sample lists in the warehouse and the maps in our website.

After some weeks, we have noticed that one of those “bad” records is again present ONLY in the map of my personal observations (but not in the map of all sightings). Furthermore, this sighting is not present in my observation list, but it is only present in the list which appears under the map of my sightings.
This record does not appear in the database (either in the warehouse, or in our website). Furthermore, the record does not appear in the geoserver.

How could it be possible?

Thank you in advance for your help!
Francesco Ferretti

2 Reply by Jim Bacon

  • Jim Bacon
  • Veteran
  • Offline
  • From: BRC
  • Registered: 03-11-2008
  • Posts: 980

Re: Data deletion

Hi Francesco,

I wonder if your record is left in the cache_occurrences table.

This cache is built and maintained by the scheduled_tasks and is updated when a record is changed. However, if you totally delete records from the database then there will be no information available to update that record in the cache.

The cache exists to enable reports to run faster but not all reports use it. That would explain why this record only appears in some places.

If my guess is correct then you should rebuild the cache as described in the documentation for the Cache Builder Module

Jim Bacon.

3 Reply by Francesco Ferretti

  • Francesco Ferretti
  • Member
  • Offline
  • Registered: 19-06-2012
  • Posts: 8

Re: Data deletion

Hi Jim,
Thank you for your reply! I will ask our colleagues computer technicians.
Francesco

4 Reply by Francesco Ferretti

  • Francesco Ferretti
  • Member
  • Offline
  • Registered: 19-06-2012
  • Posts: 8

Re: Data deletion

Dear Jim,

we followed the procedure that you suggested but, unfortunately, the sighting is still present only in the map of my observations, even if it is absent in all the other datasets.
What could you suggest we can do?

Francesco

5 Reply by Jim Bacon

  • Jim Bacon
  • Veteran
  • Offline
  • From: BRC
  • Registered: 03-11-2008
  • Posts: 980

Re: Data deletion

Hi Francesco,

To pinpoint where the record remains in the database I would look at the different reports you are running or perhaps look at the queries they are executing, which will be written to the warehouse log if the log level is set to 

$config['log_threshold'] = 4;

in /application/config/config.php.

Compare the database query that shows the deleted record with those that do not. This should make it clear which table the bad record is coming from.

Another approach that might be helpful is to compare the occurrences table with the cache_occurrences table to see if there are records in the cache that are not present in the occurrence table. E.g.

select co.id from cache_occurrences co
left join occurrences o on o.id = co.id
where o.id is null or o.deleted = true

This should return no records.

Jim Bacon.

6 Reply by Jim Bacon

  • Jim Bacon
  • Veteran
  • Offline
  • From: BRC
  • Registered: 03-11-2008
  • Posts: 980

Re: Data deletion

Hi

Just a note of warning from my own experience with rebuilding the cache. When you do 

www.mysite.com/indicia/index.php/scheduled_tasks?force_cache_rebuild

All of the cache tables are all rebuilt fully, all in one attempt, which included, for me, about 90k updates to cache_occurrences followed by an equal number of "final updates for Nullify recorders" and "final updates for Sample recorder names".

That took about 3.5 minutes and was not successful until my PHP timeout had been increased beyond this.

Moreover, having once issued force_cache_rebuild, the scheduled_tasks could never run to completion, even without forcing the rebuild, until the timeout was extended.

If you run scheduled_tasks in a browser window you will see a list of messages upon successful completion. A blank page indicates failure.

Jim Bacon