How to send email from MySQL 5 1

Question

I m trying to send an email to several recipients when a new row is inserted into a table  The list of recipients varies   I would like to be able to set this list using a select statement   I also have installed Navicat which allows me to send email notifications but only to a predetermined set of people   Thanks

User · Accepted Answer

I would be very concerned about putting the load of sending e-mails on my database server  small though it may be   I might suggest one of these alternatives    Have application logic detect the need to send an e-mail and send it  Have a MySQL trigger populate a table that queues up the e-mails to be sent and have a process monitor that table and send the e-mails

User · Answer

I agree with Jim Blizard   The database is not the part of your technology stack that should send emails   For example  what if you send an email but then roll back the change that triggered that email   You can t take the email back   It s better to send the email in your application code layer  after your app has confirmed that the SQL change was made successfully and committed

User · Answer

I agree with Jim Blizard   The database is not the part of your technology stack that should send emails   For example  what if you send an email but then roll back the change that triggered that email   You can t take the email back   It s better to send the email in your application code layer  after your app has confirmed that the SQL change was made successfully and committed

User · Answer

If you have an SMTP service running  you can outfile to the drop directory   If you have high volume  you may result with duplicate file names  but there are ways to avoid that     Otherwise  you will need to create a UDF   Here s a sample trigger solution   CREATE TRIGGER test autosendfromdrop BEFORE INSERT ON test emaildrop FOR EACH ROW BEGIN          START THE WRITING OF THE EMAIL FILE HERE               SELECT  concat  To    NEW To                 concat  From    NEW From                 concat  Subject    NEW Subject                 NEW Body           INTO OUTFILE                      C   inetpub  mailroot  pickup  mail txt                 FIELDS TERMINATED by   r n  ESCAPED BY                 END    To markup the message body you will need something like this     CREATE FUNCTION  HTMLBody  Msg varchar 8192        RETURNS varchar 17408  CHARSET latin1 DETERMINISTIC BEGIN   declare tmpMsg varchar 17408     set tmpMsg   cast concat         Date    date format NOW     e  b  Y  H  i  S -0600     r n          MIME-Version  1 0    r n          Content-Type  multipart alternative     r n           boundary   ----  NextPart 000 0000 01CA4B3F 8C263EE0      r n          Content-Class  urn content-classes message    r n          Importance  normal    r n          Priority  normal    r n       r n       r n          This is a multi-part message in MIME format     r n       r n          ------  NextPart 000 0000 01CA4B3F 8C263EE0    r n          Content-Type  text plain     r n            charset   iso-8859-1      r n          Content-Transfer-Encoding  7bit    r n       r n       r n         Msg          r n       r n       r n          ------  NextPart 000 0000 01CA4B3F 8C263EE0    r n          Content-Type  text html    r n          Content-Transfer-Encoding  7bit    r n       r n         Msg          r n   ------  NextPart 000 0000 01CA4B3F 8C263EE0--          as char     RETURN tmpMsg  END

User · Answer

If you have vps or dedicated server  You can code your own module using C programming    para h         File    para h    Author  rahul       Created on 10 February  2016  11 24 AM       ifndef PARA H  define  PARA H   ifdef    cplusplus extern  C     endif    define From   lt dgdfg dgfdgd com gt    define To   lt dfgdfg dfg gmail com gt     define From header  Rahul lt fdgdfg dfgdfg com gt       define TO header  Mini lt dfgdffefg gmail com gt       define UID  smtp server account ID   define PWD  smtp server account PWD   define domain  dfgdfgdfg com     ifdef    cplusplus    endif   endif      PARA H      main c         File    main c    Author  rahul       Created on 10 February  2016  10 29 AM      include  lt my global h gt   include  lt mysql h gt   include  lt string h gt   include  lt ctype h gt   include  lt sys socket h gt   include  lt netinet in h gt   include  lt netdb h gt   include  lt arpa inet h gt   include  lt unistd h gt   include  time h   include  para h               my bool SendEmail init UDF INIT  initid UDF ARGS  arg char  message   void SendEmail deinit UDF INIT  initid   attribute    unused     char  SendEmail UDF INIT  initid  UDF ARGS  arg char  result unsigned long  length  char  is null char  error          base64     int Base64encode len int len   int Base64encode char   coded dst  const char  plain src int len plain src    int Base64decode len const char   coded src   int Base64decode char   plain dst  const char  coded src       aaaack but it s fast and const should make it shared text page     static const unsigned char pr2six 256             ASCII table        64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  62  64  64  64  63      52  53  54  55  56  57  58  59  60  61  64  64  64  64  64  64      64   0   1   2   3   4   5   6   7   8   9  10  11  12  13  14      15  16  17  18  19  20  21  22  23  24  25  64  64  64  64  64      64  26  27  28  29  30  31  32  33  34  35  36  37  38  39  40      41  42  43  44  45  46  47  48  49  50  51  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64     int Base64decode len const char  bufcoded        int nbytesdecoded      register const unsigned char  bufin      register int nprbytes       bufin    const unsigned char    bufcoded      while  pr2six   bufin      lt   63        nprbytes    bufin -  const unsigned char    bufcoded  - 1      nbytesdecoded     nprbytes   3    4    3       return nbytesdecoded   1     int Base64decode char  bufplain  const char  bufcoded        int nbytesdecoded      register const unsigned char  bufin      register unsigned char  bufout      register int nprbytes       bufin    const unsigned char    bufcoded      while  pr2six   bufin      lt   63       nprbytes    bufin -  const unsigned char    bufcoded  - 1      nbytesdecoded     nprbytes   3    4    3       bufout    unsigned char    bufplain      bufin    const unsigned char    bufcoded       while  nprbytes  gt  4          bufout               unsigned char   pr2six  bufin   lt  lt  2   pr2six bufin 1    gt  gt  4         bufout               unsigned char   pr2six bufin 1    lt  lt  4   pr2six bufin 2    gt  gt  2         bufout               unsigned char   pr2six bufin 2    lt  lt  6   pr2six bufin 3         bufin    4      nprbytes -  4                Note   nprbytes    1  would be an error  so just ingore that case        if  nprbytes  gt  1          bufout               unsigned char   pr2six  bufin   lt  lt  2   pr2six bufin 1    gt  gt  4             if  nprbytes  gt  2          bufout               unsigned char   pr2six bufin 1    lt  lt  4   pr2six bufin 2    gt  gt  2             if  nprbytes  gt  3          bufout               unsigned char   pr2six bufin 2    lt  lt  6   pr2six bufin 3                  bufout        0       nbytesdecoded -   4 - nprbytes   amp  3      return nbytesdecoded     static const char basis 64          ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789      int Base64encode len int len        return   len   2    3   4    1     int Base64encode char  encoded  const char  string  int len        int i      char  p       p   encoded      for  i   0  i  lt  len - 2  i    3         p     basis 64  string i   gt  gt  2   amp  0x3F        p     basis 64   string i   amp  0x3   lt  lt  4                          int   string i   1   amp  0xF0   gt  gt  4         p     basis 64   string i   1   amp  0xF   lt  lt  2                          int   string i   2   amp  0xC0   gt  gt  6         p     basis 64 string i   2   amp  0x3F             if  i  lt  len         p     basis 64  string i   gt  gt  2   amp  0x3F       if  i     len - 1              p     basis 64   string i   amp  0x3   lt  lt  4             p                    else            p     basis 64   string i   amp  0x3   lt  lt  4                              int   string i   1   amp  0xF0   gt  gt  4             p     basis 64   string i   1   amp  0xF   lt  lt  2               p                      p       0       return p - encoded         end of base64      const char  GetIPAddress const char  target domain        const char  target ip      struct in addr  host address      struct hostent  raw list   gethostbyname target domain       int i   0      for  i  raw list- gt h addr list i     0  i              host address   raw list- gt h addr list i           target ip   inet ntoa  host address             return target ip     char   MailHeader const char  from  const char  to  const char  subject  const char  mime type  const char  charset         time t now      time  amp now       char  app brand    Codevlog Test APP       char  mail header   NULL      char date buff 26       char Branding 6   strlen date buff    2   10   strlen app brand    1   1       char Sender 6   strlen from    1   1       char Recip 4   strlen to    1   1       char Subject 8   1   strlen subject    1   1       char mime data 13   1   3   1   1   13   1   strlen mime type    1   1   8   strlen charset    1   1   2        strftime date buff   33     a    d  b  Y  H  M  S   localtime  amp now         sprintf Branding   DATE   s r nX-Mailer   s r n   date buff  app brand       sprintf Sender   FROM   s r n   from       sprintf Recip   To   s r n   to       sprintf Subject   Subject   s r n   subject       sprintf mime data   MIME-Version  1 0 r nContent-type   s  charset  s r n r n   mime type  charset        int mail header length   strlen Branding    strlen Sender    strlen Recip    strlen Subject    strlen mime data    10       mail header    char   malloc mail header length        memcpy  amp mail header 0    amp Branding  strlen Branding        memcpy  amp mail header 0   strlen Branding     amp Sender  strlen Sender        memcpy  amp mail header 0   strlen Branding    strlen Sender     amp Recip  strlen Recip        memcpy  amp mail header 0   strlen Branding    strlen Sender    strlen Recip     amp Subject  strlen Subject        memcpy  amp mail header 0   strlen Branding    strlen Sender    strlen Recip    strlen Subject     amp mime data  strlen mime data        return mail header     my bool SendEmail init UDF INIT  initid UDF ARGS  arg char  message        if    arg- gt arg count    2             strcpy message   Expected two arguments            return 1             arg- gt arg type 0    STRING RESULT    smtp server address     arg- gt arg type 1    STRING RESULT    email body     initid- gt ptr    char   malloc 2050   sizeof  char        memset initid- gt ptr    0   sizeof  initid- gt ptr        return 0     void SendEmail deinit UDF INIT  initid   attribute    unused         if  initid- gt ptr            free initid- gt ptr            char  SendEmail UDF INIT  initid  UDF ARGS  arg char  result unsigned long  length  char  is null char  error      char  header   MailHeader From header  TO header   Hello Its a test Mail from Codevlog    text plain    US-ASCII        int connected fd   socket AF INET  SOCK STREAM  IPPROTO IP       struct sockaddr in addr      memset  amp addr  0  sizeof  addr        addr sin family   AF INET      addr sin port   htons 25       if  inet pton AF INET  GetIPAddress arg- gt args 0     amp addr sin addr     1            connect connected fd   struct sockaddr    amp addr  sizeof  addr              if  connected fd    -1            int recvd   0          const char recv buff 4768           int sdsd          sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0           recvd    sdsd           char buff 1000           strcpy buff   EHLO        EHLO sdfsdfsdf com r n          strcat buff  domain           strcat buff    r n            send connected fd  buff  strlen buff   0           sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0           recvd    sdsd           char  cmd2 1000           strcpy  cmd2   AUTH LOGIN r n            int dfdf   send connected fd   cmd2  strlen  cmd2   0           sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0           recvd    sdsd           char  cmd3 1000           Base64encode  amp  cmd3  UID  strlen UID            strcat  cmd3    r n            send connected fd   cmd3  strlen  cmd3   0           sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0           recvd    sdsd           char  cmd4 1000           Base64encode  amp  cmd4  PWD  strlen PWD            strcat  cmd4    r n            send connected fd   cmd4  strlen  cmd4   0           sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0           recvd    sdsd           char  cmd5 1000           strcpy  cmd5   MAIL FROM              strcat  cmd5  From           strcat  cmd5    r n            send connected fd   cmd5  strlen  cmd5   0           char skip 1000           sdsd   recv connected fd  skip  sizeof  skip   0            char  cmd6 1000           strcpy  cmd6   RCPT TO              strcat  cmd6  To              strcat  cmd6    r n            send connected fd   cmd6  strlen  cmd6   0           sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0           recvd    sdsd           char  cmd7 1000           strcpy  cmd7   DATA r n            send connected fd   cmd7  strlen  cmd7   0           sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0           recvd    sdsd           send connected fd  header  strlen header   0           send connected fd  arg- gt args 1   strlen arg- gt args 1    0           char  cmd9 1000           strcpy  cmd9    r n  r n             send connected fd   cmd9  sizeof   cmd9   0           sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0           recvd    sdsd           char  cmd10 1000           strcpy  cmd10   QUIT r n            send connected fd   cmd10  sizeof   cmd10   0           sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0            memcpy initid- gt ptr  recv buff  strlen recv buff             length   recvd            free header       close connected fd       return initid- gt ptr      To configure your project go through this video  https   www youtube com watch v Zm2pKTW5z98  Send Email from MySQL on Linux  It will work for any mysql version  5 5  5 6  5 7   I will resolve if any error appear in above code  Just Inform in comment

User · Answer

I agree with Jim Blizard   The database is not the part of your technology stack that should send emails   For example  what if you send an email but then roll back the change that triggered that email   You can t take the email back   It s better to send the email in your application code layer  after your app has confirmed that the SQL change was made successfully and committed

User · Answer

If you have vps or dedicated server  You can code your own module using C programming    para h         File    para h    Author  rahul       Created on 10 February  2016  11 24 AM       ifndef PARA H  define  PARA H   ifdef    cplusplus extern  C     endif    define From   lt dgdfg dgfdgd com gt    define To   lt dfgdfg dfg gmail com gt     define From header  Rahul lt fdgdfg dfgdfg com gt       define TO header  Mini lt dfgdffefg gmail com gt       define UID  smtp server account ID   define PWD  smtp server account PWD   define domain  dfgdfgdfg com     ifdef    cplusplus    endif   endif      PARA H      main c         File    main c    Author  rahul       Created on 10 February  2016  10 29 AM      include  lt my global h gt   include  lt mysql h gt   include  lt string h gt   include  lt ctype h gt   include  lt sys socket h gt   include  lt netinet in h gt   include  lt netdb h gt   include  lt arpa inet h gt   include  lt unistd h gt   include  time h   include  para h               my bool SendEmail init UDF INIT  initid UDF ARGS  arg char  message   void SendEmail deinit UDF INIT  initid   attribute    unused     char  SendEmail UDF INIT  initid  UDF ARGS  arg char  result unsigned long  length  char  is null char  error          base64     int Base64encode len int len   int Base64encode char   coded dst  const char  plain src int len plain src    int Base64decode len const char   coded src   int Base64decode char   plain dst  const char  coded src       aaaack but it s fast and const should make it shared text page     static const unsigned char pr2six 256             ASCII table        64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  62  64  64  64  63      52  53  54  55  56  57  58  59  60  61  64  64  64  64  64  64      64   0   1   2   3   4   5   6   7   8   9  10  11  12  13  14      15  16  17  18  19  20  21  22  23  24  25  64  64  64  64  64      64  26  27  28  29  30  31  32  33  34  35  36  37  38  39  40      41  42  43  44  45  46  47  48  49  50  51  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64      64  64  64  64  64  64  64  64  64  64  64  64  64  64  64  64     int Base64decode len const char  bufcoded        int nbytesdecoded      register const unsigned char  bufin      register int nprbytes       bufin    const unsigned char    bufcoded      while  pr2six   bufin      lt   63        nprbytes    bufin -  const unsigned char    bufcoded  - 1      nbytesdecoded     nprbytes   3    4    3       return nbytesdecoded   1     int Base64decode char  bufplain  const char  bufcoded        int nbytesdecoded      register const unsigned char  bufin      register unsigned char  bufout      register int nprbytes       bufin    const unsigned char    bufcoded      while  pr2six   bufin      lt   63       nprbytes    bufin -  const unsigned char    bufcoded  - 1      nbytesdecoded     nprbytes   3    4    3       bufout    unsigned char    bufplain      bufin    const unsigned char    bufcoded       while  nprbytes  gt  4          bufout               unsigned char   pr2six  bufin   lt  lt  2   pr2six bufin 1    gt  gt  4         bufout               unsigned char   pr2six bufin 1    lt  lt  4   pr2six bufin 2    gt  gt  2         bufout               unsigned char   pr2six bufin 2    lt  lt  6   pr2six bufin 3         bufin    4      nprbytes -  4                Note   nprbytes    1  would be an error  so just ingore that case        if  nprbytes  gt  1          bufout               unsigned char   pr2six  bufin   lt  lt  2   pr2six bufin 1    gt  gt  4             if  nprbytes  gt  2          bufout               unsigned char   pr2six bufin 1    lt  lt  4   pr2six bufin 2    gt  gt  2             if  nprbytes  gt  3          bufout               unsigned char   pr2six bufin 2    lt  lt  6   pr2six bufin 3                  bufout        0       nbytesdecoded -   4 - nprbytes   amp  3      return nbytesdecoded     static const char basis 64          ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789      int Base64encode len int len        return   len   2    3   4    1     int Base64encode char  encoded  const char  string  int len        int i      char  p       p   encoded      for  i   0  i  lt  len - 2  i    3         p     basis 64  string i   gt  gt  2   amp  0x3F        p     basis 64   string i   amp  0x3   lt  lt  4                          int   string i   1   amp  0xF0   gt  gt  4         p     basis 64   string i   1   amp  0xF   lt  lt  2                          int   string i   2   amp  0xC0   gt  gt  6         p     basis 64 string i   2   amp  0x3F             if  i  lt  len         p     basis 64  string i   gt  gt  2   amp  0x3F       if  i     len - 1              p     basis 64   string i   amp  0x3   lt  lt  4             p                    else            p     basis 64   string i   amp  0x3   lt  lt  4                              int   string i   1   amp  0xF0   gt  gt  4             p     basis 64   string i   1   amp  0xF   lt  lt  2               p                      p       0       return p - encoded         end of base64      const char  GetIPAddress const char  target domain        const char  target ip      struct in addr  host address      struct hostent  raw list   gethostbyname target domain       int i   0      for  i  raw list- gt h addr list i     0  i              host address   raw list- gt h addr list i           target ip   inet ntoa  host address             return target ip     char   MailHeader const char  from  const char  to  const char  subject  const char  mime type  const char  charset         time t now      time  amp now       char  app brand    Codevlog Test APP       char  mail header   NULL      char date buff 26       char Branding 6   strlen date buff    2   10   strlen app brand    1   1       char Sender 6   strlen from    1   1       char Recip 4   strlen to    1   1       char Subject 8   1   strlen subject    1   1       char mime data 13   1   3   1   1   13   1   strlen mime type    1   1   8   strlen charset    1   1   2        strftime date buff   33     a    d  b  Y  H  M  S   localtime  amp now         sprintf Branding   DATE   s r nX-Mailer   s r n   date buff  app brand       sprintf Sender   FROM   s r n   from       sprintf Recip   To   s r n   to       sprintf Subject   Subject   s r n   subject       sprintf mime data   MIME-Version  1 0 r nContent-type   s  charset  s r n r n   mime type  charset        int mail header length   strlen Branding    strlen Sender    strlen Recip    strlen Subject    strlen mime data    10       mail header    char   malloc mail header length        memcpy  amp mail header 0    amp Branding  strlen Branding        memcpy  amp mail header 0   strlen Branding     amp Sender  strlen Sender        memcpy  amp mail header 0   strlen Branding    strlen Sender     amp Recip  strlen Recip        memcpy  amp mail header 0   strlen Branding    strlen Sender    strlen Recip     amp Subject  strlen Subject        memcpy  amp mail header 0   strlen Branding    strlen Sender    strlen Recip    strlen Subject     amp mime data  strlen mime data        return mail header     my bool SendEmail init UDF INIT  initid UDF ARGS  arg char  message        if    arg- gt arg count    2             strcpy message   Expected two arguments            return 1             arg- gt arg type 0    STRING RESULT    smtp server address     arg- gt arg type 1    STRING RESULT    email body     initid- gt ptr    char   malloc 2050   sizeof  char        memset initid- gt ptr    0   sizeof  initid- gt ptr        return 0     void SendEmail deinit UDF INIT  initid   attribute    unused         if  initid- gt ptr            free initid- gt ptr            char  SendEmail UDF INIT  initid  UDF ARGS  arg char  result unsigned long  length  char  is null char  error      char  header   MailHeader From header  TO header   Hello Its a test Mail from Codevlog    text plain    US-ASCII        int connected fd   socket AF INET  SOCK STREAM  IPPROTO IP       struct sockaddr in addr      memset  amp addr  0  sizeof  addr        addr sin family   AF INET      addr sin port   htons 25       if  inet pton AF INET  GetIPAddress arg- gt args 0     amp addr sin addr     1            connect connected fd   struct sockaddr    amp addr  sizeof  addr              if  connected fd    -1            int recvd   0          const char recv buff 4768           int sdsd          sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0           recvd    sdsd           char buff 1000           strcpy buff   EHLO        EHLO sdfsdfsdf com r n          strcat buff  domain           strcat buff    r n            send connected fd  buff  strlen buff   0           sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0           recvd    sdsd           char  cmd2 1000           strcpy  cmd2   AUTH LOGIN r n            int dfdf   send connected fd   cmd2  strlen  cmd2   0           sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0           recvd    sdsd           char  cmd3 1000           Base64encode  amp  cmd3  UID  strlen UID            strcat  cmd3    r n            send connected fd   cmd3  strlen  cmd3   0           sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0           recvd    sdsd           char  cmd4 1000           Base64encode  amp  cmd4  PWD  strlen PWD            strcat  cmd4    r n            send connected fd   cmd4  strlen  cmd4   0           sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0           recvd    sdsd           char  cmd5 1000           strcpy  cmd5   MAIL FROM              strcat  cmd5  From           strcat  cmd5    r n            send connected fd   cmd5  strlen  cmd5   0           char skip 1000           sdsd   recv connected fd  skip  sizeof  skip   0            char  cmd6 1000           strcpy  cmd6   RCPT TO              strcat  cmd6  To              strcat  cmd6    r n            send connected fd   cmd6  strlen  cmd6   0           sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0           recvd    sdsd           char  cmd7 1000           strcpy  cmd7   DATA r n            send connected fd   cmd7  strlen  cmd7   0           sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0           recvd    sdsd           send connected fd  header  strlen header   0           send connected fd  arg- gt args 1   strlen arg- gt args 1    0           char  cmd9 1000           strcpy  cmd9    r n  r n             send connected fd   cmd9  sizeof   cmd9   0           sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0           recvd    sdsd           char  cmd10 1000           strcpy  cmd10   QUIT r n            send connected fd   cmd10  sizeof   cmd10   0           sdsd   recv connected fd  recv buff   recvd  sizeof  recv buff  - recvd  0            memcpy initid- gt ptr  recv buff  strlen recv buff             length   recvd            free header       close connected fd       return initid- gt ptr      To configure your project go through this video  https   www youtube com watch v Zm2pKTW5z98  Send Email from MySQL on Linux  It will work for any mysql version  5 5  5 6  5 7   I will resolve if any error appear in above code  Just Inform in comment

User · Answer

If you have an SMTP service running  you can outfile to the drop directory   If you have high volume  you may result with duplicate file names  but there are ways to avoid that     Otherwise  you will need to create a UDF   Here s a sample trigger solution   CREATE TRIGGER test autosendfromdrop BEFORE INSERT ON test emaildrop FOR EACH ROW BEGIN          START THE WRITING OF THE EMAIL FILE HERE               SELECT  concat  To    NEW To                 concat  From    NEW From                 concat  Subject    NEW Subject                 NEW Body           INTO OUTFILE                      C   inetpub  mailroot  pickup  mail txt                 FIELDS TERMINATED by   r n  ESCAPED BY                 END    To markup the message body you will need something like this     CREATE FUNCTION  HTMLBody  Msg varchar 8192        RETURNS varchar 17408  CHARSET latin1 DETERMINISTIC BEGIN   declare tmpMsg varchar 17408     set tmpMsg   cast concat         Date    date format NOW     e  b  Y  H  i  S -0600     r n          MIME-Version  1 0    r n          Content-Type  multipart alternative     r n           boundary   ----  NextPart 000 0000 01CA4B3F 8C263EE0      r n          Content-Class  urn content-classes message    r n          Importance  normal    r n          Priority  normal    r n       r n       r n          This is a multi-part message in MIME format     r n       r n          ------  NextPart 000 0000 01CA4B3F 8C263EE0    r n          Content-Type  text plain     r n            charset   iso-8859-1      r n          Content-Transfer-Encoding  7bit    r n       r n       r n         Msg          r n       r n       r n          ------  NextPart 000 0000 01CA4B3F 8C263EE0    r n          Content-Type  text html    r n          Content-Transfer-Encoding  7bit    r n       r n         Msg          r n   ------  NextPart 000 0000 01CA4B3F 8C263EE0--          as char     RETURN tmpMsg  END

User · Answer

I agree with Jim Blizard   The database is not the part of your technology stack that should send emails   For example  what if you send an email but then roll back the change that triggered that email   You can t take the email back   It s better to send the email in your application code layer  after your app has confirmed that the SQL change was made successfully and committed

[mysql] How to send email from MySQL 5.1

Examples related to mysql

Examples related to email

Examples related to notifications

Examples related to navicat