About EasyMorph Tutorials & Examples Web-help

Delete matching database rows

salam,
I have an action to delete the lines from the mysql table which do not correspond in the sql server table but always I have a problem with the error which in attachment.


thanks for your help

Hello @Alilo_Dris and welcome to the community.

Can you please:

  1. Update your EasyMorph Desktop to the latest version from our download page:
    https://easymorph.com/download.html
  2. Run your project again
  3. Send us a screenshot or all the error messages.

We have improved error messages in the “Delete matching DB rows” action and I hope that the updated version will show you the actual source of the problem.

Also, I seem that a temporary database table, created by that action, was not deleted. Is it possible that you have no permission to delete DB tables in the target database?

salam,
i much thank you for your reply I will dowload the latest version and I will inform you if the probeleme is resolved.
Very cordially

i have a latest verion V4.0.6.
i think that the probleme of permission in databae mysql .easymorph create a tomporary table named by :easymorph_faac27f95d3acc2ee71f and I can’t delete it except if I restart the wamp server

Can you please install version 4.1.0.10?

“Une ou plusieurs erreurs se sont produites” part of the error message means that there was some other error (or errors). Version 4.1.0.10 should display all the errors with their details. It’s possible that those errors will tell us why the temporary table can’t be deleted.

you are right here is the error in attachment.
thanks

Can you please resize the error window to make all the errors visible and send us a screenshot of all the error messages?

here is the details:
Error: Fatal error encountered during command execution.
Fatal error encountered attempting to read the resultset.
Reading from the stream has failed.
Impossible de lire les données de la connexion de transport : Une tentative de connexion a échoué car le parti connecté n’a pas répondu convenablement au-delà d’une certaine durée ou une connexion établie a échoué car l’hôte de connexion n’a pas répondu.
Une tentative de connexion a échoué car le parti connecté n’a pas répondu convenablement au-delà d’une certaine durée ou une connexion établie a échoué car l’hôte de connexion n’a pas répondu
Source: action “Delete matching database rows”, table “test 1”

Thank you for the screenshot.

It seems like some kind of connection or\and timeout issue.

Can you please open the properties window of your MySQL connector and press the “Test” button. Will the test succeed?

If yes, please answer the following questions:

  • How many rows does your target DB table has?
  • How many rows and columns in your dataset with you are passing to the “Delete matching DB rows” action?
  • How much time does it take for this action to fail?
  • The number of rows is 118669 rows in the table target
  • The number of all columns table target is 37 and only one column for delete
  • The action take one minute to fail

Can you please open “Advanced” tab of the connector settings, set “SQL command timeout” to 300 seconds and then run your “Delete matching DB rows” action again. Will it fail? And if it will, how much time will it take to fail?

the time take 10 minutes and after :the action is fail

after the execution action the easymorph creates tomporary table in the target database as shown in the attachment and when I can’t open it only after I restart my mysql wampservertables%20%20tempo

@Alilo_Dris, it seems that after the error occurs, DELETE query keeps running on the server. That’s why you can’t delete temporary table, created by EasyMorph.

And it seems that deleting 100 thousand rows from MySQL with this action may took like 1.5-2 hours.

So it’s better to find another approach to your task.

Maybe it’s be possible to use “Delete DB rows by condition” action instead?

but how i can delete the lines that don’t match the lines of table source.
thank you in advance

I see that you are importing data from a database before using “Delete matching DB rows”. Does your “Import from DB” and “Delete matching DB rows” actions work with the same database?

83/5000
same problem when I delete after or before importe
apparently the problem is when creating the temporary table in the target database.

The problem is not in the temporary table itself but in the DELETE query which is executed on the target table and that temporary table. It seems that MySQL doesn’t optimize it good enough and deletes only 15-20 rows per second.

As a workaround you can try to:

  • Export the keys which you are trying to match to a temporary table with the “Export to DB” action.
  • Run a custom DELETE SQL query which will delete required rows. Custom SQL can be executed with the “Custom command” mode of the “Database command” action.

Custom DELETE SQL query may use:

  • JOIN on a target and a temporary table and WHERE condition which will filter rows which have not matches in the temporary table
    or
  • WHERE with NOT IN condition and a subselect on the temporary table.

salam,
I want to update between two different databases bd sqlserver and bd mysql
does easymorph do the differential update between those database? but without executing the following steps:
-Import the lines to update in EasyMorph
-Save them in a temporary .dset file
-Update rows in EasyMorph
-Delete rows from the database table
-Export the updated rows in the database table
-Delete the temporary .dset file
thanks for help.

@Alilo_Dris, there is no separate action which updates database rows in EasyMorph.

To learn more about EasyMorph visit easymorph.com.