[EP-tech] Re: mysqldump problem with cachetables

[Tim Brody]

mysqldump does a "SHOW TABLES" then goes through each table to dump it. Of
course, a table can be dropped in the mean-time which will confuse it.

So either you have to modify mysqldump to obtain a global lock (not so good
for users) or force it to skip-over lost cache tables.

The better long-term solution is to use InnoDB tables and a file-system
level backup, but that can come with its own problems.

--
All the best,
Tim.

On Mon, 22 Apr 2013 13:40:50 +1000, Mark Gregson <mark.gregson at qut.edu.au>
wrote:
> If you are running myisam table engine, you could try myisamchk
> (http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html) to check for
> underlying table problems.
> 
> Is it possible that EPrints is cleaning up (dropping) cache tables while
> mysqldump is running?
> 
> Mark
> 
> Mark Gregson | Applications and Development Team Leader
> Library eServices | Queensland University of Technology
> Level 3 | R Block | Kelvin Grove Campus | GPO Box 2434 | Brisbane 4001
> Phone: +61 7 3138 3782 | Web: http://eprints.qut.edu.au/
> ABN: 83 791 724 622
> CRICOS No: 00213J
> 
> 
> 
> -----Original Message-----
> From: eprints-tech-bounces at ecs.soton.ac.uk
> [mailto:eprints-tech-bounces at ecs.soton.ac.uk] On Behalf Of Malcolm Bodger
> Sent: Saturday, 20 April 2013 12:19 AM
> To: paolo.tealdi at polito.it
> Cc: <eprints-tech at ecs.soton.ac.uk>
> Subject: [EP-tech] Re: mysqldump problem with cachetables
> 
> Hi Paolo,
> 
> I'm running mysqldump with the force option and I have 15 lines of error
> mesages, like this one:
> 
> Error: Couldn't read status information for table cache1813265 ()
> mysqldump: Couldn't execute 'show create table `cache1813265`': Table
> 'eprints.cache1813265' doesn't exist (1146)
> 
> Can I ignore these errors?
> 
> Thanks.
> Regards,
> Malcolm.
> ________________________________________
> From: Malcolm Bodger
> Sent: 19 April 2013 15:07
> To: paolo.tealdi at polito.it
> Cc: <eprints-tech at ecs.soton.ac.uk>
> Subject: RE: [EP-tech]  mysqldump problem with cachetables
> 
> Hi Paolo,
> 
> Thanks for this information, it's very helpful, and I'll go with the
> 'force' option.
> 
> Regards,
> Malcolm.
> ________________________________________
> From: Paolo Tealdi [paolo.tealdi at polito.it]
> Sent: 19 April 2013 14:23
> To: Malcolm Bodger
> Cc: <eprints-tech at ecs.soton.ac.uk>
> Subject: Re: [EP-tech]  mysqldump problem with cachetables
> 
> Il 19/04/2013 15:01, Malcolm Bodger ha scritto:
>> Hi Paolo,
>>
>> I run the cleanup_cachemaps, but it only removed one orphaned table,
>> leaving me with 942.
>> Would you know why I have so many cache tables?
>>
>> Thanks.
>> Regards,
>> Malcolm.
>>
> 
> Hi Malcom,
> This is normal.
> When you make a web search on your eprints web interface, the system
saves
> the result records in a cache table, avoiding to repeat a (possibly) cpu
> intensive operation (for example to present the next search page).
> The server saves the recordset result in a table named "cache$number" and
> adds a line also in "cachemap" table.
> The epadmin command i wrote matches the records in "cache" table with the
> cache"number" table,  cleaning orphaned table (NOT all cache table on the
> orphaned one).
> The server has a setting for the max number of cache table active, when
you
> go beyond that number, automatically the server drops the oldest table
> "table$number" and deletes its related record in "cachemap" table.
> The error you find in the mysqldump is due to this automatic cache
cleaning
> by eprints: mysqldump tries to dump a table that eprints has already
> deleted.
> I think it's not a problem if you put --force in the mysqldump
parameters.
> 
> 
> Best regards,
> Paolo Tealdi
> 
> 
> --
> Ing. Paolo Tealdi         Area IT - Politecnico Torino
> Telefono/Phone : +39-011-0906714 , FAX : +39-011-0906799
Indirizzo/Address
> : C.so Duca degli Abruzzi,  24 - 10129 Torino - ITALY Skype :
tealdi.paolo
> Please consider your environmental responsibility before printing this
> e-mail
> 
> The University of Westminster is a charity and a company limited by
> guarantee. Registration number: 977818 England. Registered Office: 309
> Regent Street, London W1B 2UW.
> 
> 
> *** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
> *** Archive: http://www.eprints.org/tech.php/
> *** EPrints community wiki: http://wiki.eprints.org/
> 
> *** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
> *** Archive: http://www.eprints.org/tech.php/
> *** EPrints community wiki: http://wiki.eprints.org/

-- 
All the best,
Tim.