Calling an url from a trigger in mysql

Question

I know it's highly unrecommended,

I know that it's an issue with performance, speed, etc, but it's for an integration, and they only are doing their updates via mysql (I know it's crazy to do that too but I can't change what they do, and they are making a ton of sales so they don't want to change anything).

I only need to post to a URL (it can be as simple as http://www.google.com?id=skuid)

I read this blogs and stacks, but they are 2+ years old, would like to know if there are alternatives to using an udf:

http://open-bi.blogspot.pe/2012/11/call-restful-web-services-from-mysql.html

http://www.mooreds.com/wordpress/archives/1497

Calling a php file by using mysql trigger

Thanks a lot for everything!!

Solution 1

To trigger an external action, you have to use a UDF - it's the only way for mysql to tell something to the "outside world". The only alternative is an external agent polling the DB constantly - which is an inferior solution.

As for the choice of a UDF,

• to minimize load on the DB, it should probably be something that finishes quickly (note that UDFs run synchronously).
• So, unless the installation is sufficiently small-scale, it's going to merely notify an external agent of the event. This also minimizes error handling at the DB side.
  • Otherwise, if you don't (yet) care, you can e.g. just spawn curl for all it's worth.

Ways that come to mind:

• spawn a small program - e.g. touch some file which the agent watches. There's an existing sys_exec that uses system() (with all due considerations).
• IPC (signal is the simplest; with others, you can pass additional information but it requires more setup)

As the sys_exec's source shows, it's not so hard to write a UDF, so you aren't really limited to what's already available (this may explain why lib_mysqludf_sys is so limited: if you need something better, it's sufficiently easy to write a task-specific function). The current docs are at 26.4.2 Adding a New User-Defined Function - MySQL 5.7 Reference Manual.

Solution 2

Here's a solution for a MySQL server 5.6 64bit(!) on Windows platform. I tested it under Win10 64bit. I needed a 64bit .dll version of a plugin which gives you functionality to run a command in a shell, a working one I found here: http://winadmin.blogspot.nl/2011/06/mysql-sysexec-udf-for-64-bit-windows.html

You could also compile it yourself on Windows see: http://rpbouman.blogspot.nl/2007/09/creating-mysql-udfs-with-microsoft.html

For MySQL 5.1+ you have to put the plugin/dll in a subdir of your MySQL installation root for example C:\wamp\bin\mysql\mysql5.6.17\lib\plugin Or else you get an error:

Can not open shared library dll – errorcode 193

You also need curl.exe which is called by sys_eval. You need to download the correct one here (be sure to copy both(!) files .exe and .crt to a reachable path from your PATH env. var), I used c:\windows\system32 : https://winampplugins.co.uk/curl/

Then only code you need is:

--one time setup. run inside your database
CREATE FUNCTION sys_eval RETURNS STRING SONAME ‘lib_mysqludf_sys.dll’;

--example call to an URL
select CONVERT(sys_eval(CONCAT(‘curl https://randomuser.me/api?results=1‘)) USING UTF8MB4);

Solution 3

You can execute external script via "sys_exec" command from your trigger. The trick is to write that script the non-blocking way, so it spawns background process that do the work asynchronously, and the main process finishes right away.

For example something like this:

#!/bin/sh
nohup curl(or wget) http://www.example.com ...other_post_parameters... &

You need to make sure though, that you don't create too many simultaneous processes. That could be done in the trigger (for example it may write last execution time to some table, and then check if some amount of time has passed), or in shell script (it can create/delete some flag file that would indicate running proceess).

Comments

[ivan_pozdeev]

If you just need to POST/whatever, regarless of method, you can do that through some asynchronous proxy, with the trigger using a less detrimental UDF like sys_exec().

[Solarflare]

You have to use an UDF for this. If you don't need a result (or if you can it add it later), you should use an asynchronous method. E.g. in the trigger, just add the url to a todo-table and let an event handle it later (or call an udf directly, that returns immediately, not after execution) - otherwise your trigger has to include errorhandling and/or maybe wait for a timeout if the connection is down - all while potentionally locking your data.

[e4c5]

The fact that the first link is from 2012 makes no difference. Nothing has changed over the last 15 years. Use it.

[Saikios]

@ivan_pozdeev I need to post a curl, so yes, I thought of doing a udf with a sys_exec + idtriggered, but as I mentioned, would love to know if there is another workaround any open source, or somehting that somene thought about this outside the box maybe? (thanks a lot for your answer)

[Saikios]

@Solarflare no I don't need a result at all, and I'm thinking that I can even loose one query every once and then, this is to sync products and stocks, but we are thinking on adding a +-10% stock to avoid problems

[Saikios]

@e4c5 someimes there are updates, just wanted to be sure because I never liked the idea of calling a sys from inside a trigger, and I don't beleive I'm the only one :S (maybe I'm :(

[Solarflare]

@Saikios synchronizing stock between two different systems (that is: not replication) is basically never done by calling a udf in a trigger. Apart from maybe directly accessing the other database (in mysql that would be federated tables), it is usually done at a level before the db (usually some kind of api/php/webservice): e.g. if you update the stocks in db 1 only by php requests, send a url there to db 2; same for "buy" button for customers. 2nd method: use a cron job that will check for changes (e.g. written in php) (in your trigger, add a line to a log or just query for update-times).

[Saikios]

@Solarflare would love to be able to do that :(, but some of the companies that we work with are working directly on their db, and we can't change how they work.

[Saikios]

I will probably choose your answer because it's greatly explained and useful, will leave it open only for one more day to see if somebody else has an out of the box idea but I don't think so, thanks a lot this will really help me to justify a lot of decisions on a very close future.

[Gili]

Polling a table of outstanding events is underrated. If you want to trigger asynchronous events, this is a reasonable way to do.

[Brian Leishman]

Shameless plug for my own repo that makes an http request and ignores the response github.com/StirlingMarketingGroup/mysql-http