[EP-tech] Trying to understand a disaster after a database update

John Salter J.Salter at leeds.ac.uk
Mon Nov 16 14:51:53 GMT 2020


CAUTION: This e-mail originated outside the University of Southampton.

I rarely go near the database in this way now.

For this type of issue my approach would be to:-
- work out the logic to identify and correct records
- if this is an ongoing activity (sounds like there are humans involved - so it probably is!) create triggers/validations/warnings as David suggested
- write a bin script that searches for records that need correcting, and either calls the above triggers, or updates/commits records as appropriate
- include a 'dry-run' option in the script - so you can check it will all work as expected
- if necessary, export results from the script showing what's been updated (useful to sent to Repo manager)

The benefits of this approach over direct MySQL action are:
- the script will create a revision - the actor will be the name of the script
- all additional processes (re-indexing changed fields) will still be carried out.

If you make a change in a record directly in MySQL, the revision history for that item will only change next time EPrints commits the record - which can make things seem very strange.

Having written all that - I've got no idea what happened to all your EPrints.
My only offering would be that some issue with the eprints__* (two underscores) tables - but adding fields via the configuration should make all that happen by default.

If you wanted to share the mysql and command-line history off-list, I'd happily take a look - but still might not be able to see what happened!

Cheers,
John


-----Original Message-----
From: eprints-tech-bounces at ecs.soton.ac.uk [mailto:eprints-tech-bounces at ecs.soton.ac.uk] On Behalf Of Yuri via Eprints-tech
Sent: 16 November 2020 13:27
To: eprints-tech at ecs.soton.ac.uk
Subject: Re: [EP-tech] Trying to understand a disaster after a database update

CAUTION: This e-mail originated outside the University of Southampton.

I would check the apache logs, maybe there's some hint or error that can
help you on finding the cause of eprint removal. Also check the mysql
history, maybe you made some mistake and deleted the eprint table contents.

If anything works, just recover from a recent backup and follow David
advice on moving fields. Eprints recommit accept a list of ids, you
should have tested your sql changes on few elements.

epadmin recommit repository_id dataset_id [eprint_id eprint_id ...]

Il 16/11/20 12:09, David R Newman via Eprints-tech ha scritto:
>
> Hi Laurent,
>
> So to confirm: Are there are no records in the eprint database table
> or you just don't see any records when you look at the Manage Deposits
> page in the repository?  When you ran recommit at the end of your set
> of instructions.  How long did this take to run and how many record
> did you have before you started this whole process?  If it took quite
> a long time then it suggests some how the recommit process caused you
> problem.  However, if it ran quickly this suggests you eprint records
> were already deleted or broken so could not be displayed in the Manage
> Deposits page.
>
> As a general comment on your solution.  I would have not dumped out
> and reimported database tables.  I would have created a rule (block of
> code) in your archive's cfg/cfg.d/eprint_fields_automatic.pl to map
> (probably just copy) the old field to new field.  You would have still
> had to have the old field defined in eprint_fields.pl but as long as
> you had removed the old field from the eprint workflow and any other
> config files (except eprint_fields_automatic.pl and eprint_fields.pl)
> it should not be editable, or visible to non-logged in users.  You
> would then need to run epadmin recommit like you did in your original
> instructions and once this completed you could remove (probably best
> to comment out) references to the old field and the rule to map the
> old field to the new field.
>
> Regards
>
> David Newman
>
> On 16/11/2020 10:49, Laurent Cloarec via Eprints-tech wrote:
>> *CAUTION:* This e-mail originated outside the University of Southampton.
>> Hello everyone
>>
>> The initial problem : many (about 1,000) metadata values entered by
>> archive editors in a non appropriate field (used in parallel for
>> another purpose by another import mechanism), let's say "infoX". Into
>> the database, this information was stored in a table looking like
>> "eprint_creators_infoX(eprintid,pos,creators_infoX)"
>>
>> The solution employed :
>>
>>  1. create a new metadata/field (let's say "infoY") among the
>>     creators metadata (with "eprints_fields.pl") and update the
>>     database structure in consequence => creation of a new table
>>     "eprint_creators_infoY(eprintid,pos,creators_infoY)"
>>  2. backup (SQL values dump) the important values previously entered
>>     and stored into the "eprint_creators_infoX" table;
>>  3. from this backup, create a new SQL file where the "infoX" column
>>     name is replaced by "infoY";
>>  4. import these values into the new table ""eprint_creators_infoY";
>>  5. delete the previously entered value from "eprint_creators_infoX"
>>     table (/there was an objective criteria to distinguish the
>>     entered from the imported values/);
>>  6. run "bin/epdamin recommit <archivename> eprint"
>>
>> The disaster/issue that happened : all the records from "eprint" main
>> table have been deleted, and the archive/repository consequently
>> appears as empty!!!
>>
>> Could someone explain this???
>>
>> Best regards
>> --
>> Laurent Cloarec
>> Service Commun de la Documentation - Service du Numérique Documentaire
>> Université Toulouse 1 Capitole
>>
>> *** Options:http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
>> *** Archive:https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.eprints.org%2Ftech.php%2F&amp;data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C0dccb203a66842566f3708d88a3f2ad2%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637411351185817527%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=pRs8ZhgNsvEUGBv0gcaE1kDw1JvM7qhAiw0valzUmk8%3D&amp;reserved=0
>> *** EPrints community wiki:https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwiki.eprints.org%2F&amp;data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C0dccb203a66842566f3708d88a3f2ad2%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637411351185817527%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=z6%2BS%2FhET8X%2F%2BqjX6abyqk3VMO7mPA4%2BmSXx85g2MmUo%3D&amp;reserved=0
>
> <https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.avg.com%2Femail-signature%3Futm_medium%3Demail%26utm_source%3Dlink%26utm_campaign%3Dsig-email%26utm_content%3Demailclient&amp;data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C0dccb203a66842566f3708d88a3f2ad2%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637411351185817527%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=GIhsroL9EMPt%2BEwunSOS6UPMcXxXqmfs%2FoNXHUcYpgE%3D&amp;reserved=0>
>       Virus-free. https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.avg.com%2F&amp;data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C0dccb203a66842566f3708d88a3f2ad2%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637411351185827483%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=tHxFuSaIZtCNC572AwgSqjXvtCCyOznX5lbPVQTaH4k%3D&amp;reserved=0
> <https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.avg.com%2Femail-signature%3Futm_medium%3Demail%26utm_source%3Dlink%26utm_campaign%3Dsig-email%26utm_content%3Demailclient&amp;data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C0dccb203a66842566f3708d88a3f2ad2%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637411351185827483%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=43wXllnI6SbogqrQZM8nzqcl4f29200ztsfUiBAdgIs%3D&amp;reserved=0>
>
>
> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
> *** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
> *** Archive: https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.eprints.org%2Ftech.php%2F&amp;data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C0dccb203a66842566f3708d88a3f2ad2%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637411351185827483%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=mP6m4njkPbIPOc5zxJz27L2vBD0oWNdQj8OeNdAKWZA%3D&amp;reserved=0
> *** EPrints community wiki: https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwiki.eprints.org%2F&amp;data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C0dccb203a66842566f3708d88a3f2ad2%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637411351185827483%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=mTxdnlbpSoCInoDKnk4do6kIEoHXIu5OTolZSLGs88E%3D&amp;reserved=0

*** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
*** Archive: https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.eprints.org%2Ftech.php%2F&amp;data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C0dccb203a66842566f3708d88a3f2ad2%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637411351185827483%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=mP6m4njkPbIPOc5zxJz27L2vBD0oWNdQj8OeNdAKWZA%3D&amp;reserved=0
*** EPrints community wiki: https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwiki.eprints.org%2F&amp;data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C0dccb203a66842566f3708d88a3f2ad2%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637411351185827483%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=mTxdnlbpSoCInoDKnk4do6kIEoHXIu5OTolZSLGs88E%3D&amp;reserved=0



More information about the Eprints-tech mailing list