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