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!!

Answer 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.

Answer 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);

Answer 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).