question re import from mysql

[human]

I'm working with a Filemaker database which is imported from a mysql server every night. It's imported, rather than set up as a direct external data source, for reasons of speed - it's just too slow to use otherwise. So up to now I have been doing an "add" import, by adding all the records from the external source, deleting all the omitted records if it succeeds, otherwise on an error, deleting the new imported records, and then showing the omitted ones again. Because of this, I've been careful to run the script after hours, because I worry that connected users may have trouble during this process, or records may be locked by users, and so the script would fail and leave a mess. In addition, the reason for doing an "add" rather than an "update" import, was because there were some join tables in the external data source that did not have primary keys, and I found that an "update" in that case would take way too long to complete, if ever. But now I've corrected that problem on the external data source, so all my imports can be the "update" type, adding new records if there are any, and I am not deleting anything in the event of an error. With this in mind, is it safe to run the script during normal hours? I suppose there still could be locked records, but I can trap for that, and try to commit them or halt the script with an error.

[human]

Another concern I have is this - does an “update” type import delete records that don’t exist in the source anymore (matched by ID)? Or does it just add new ones and leave the old ones untouched?

 

-- NVM, I found the answer to this is yes it deletes

Edited December 18, 2023 by human
found the answer

[comment]

1 hour ago, human said:

I found the answer to this is yes it deletes

No, it doesn't.  It merely omits them. Where do you find your "answers"?

 

[human]

1 hour ago, comment said:

No, it doesn't.  It merely omits them. Where do you find your "answers"?

 

I tested it and I thought that it was deleted - perhaps I didn't see that it was "omitted" - I'll check that now. If it was omitted, then I I should be able to do "show omitted only" and then "delete found records" to zap them - correct?

[human]

33 minutes ago, human said:

I tested it and I thought that it was deleted - perhaps I didn't see that it was "omitted" - I'll check that now. If it was omitted, then I I should be able to do "show omitted only" and then "delete found records" to zap them - correct?

You are correct. they are omitted, not deleted. But my method works I think. Thanks

Edited December 18, 2023 by human

[human]

I would like to re-ask the original question, in simplified form: is it safe to do "update" type imports while users may be accessing the data? The data in FM is read-only, so they can't be editing it while updating it, but there could still be things like find operations going on while the import is running.

Edited December 22, 2023 by human
correction