[MXS-3586] Maxscale tries to execute write statement on slave Created: 2021-06-01  Updated: 2021-06-03  Resolved: 2021-06-02

Status: Closed
Project: MariaDB MaxScale
Component/s: qc_sqlite
Affects Version/s: 2.5.11
Fix Version/s: 2.5.13

Type: Bug Priority: Major
Reporter: Alex Assignee: Johan Wikman
Resolution: Fixed Votes: 0
Labels: None
Environment:

Debian duster, backend 10.4.19, 10.5.10

Issue Links:
Problem/Incident
is caused by MXS-3589 qc_sqlite handles current_timestamp e... Closed

 Description   

From the log:

2021-06-01 16:13:51   warning: (4637894) [readwritesplit] (Read-Write-Service) Slave server 
cannot execute this statement) differs from master's response (OK) to COM_QUERY: `SET STATEMENT max_statement_time = 60 FOR 
      INSERT INTO maillog5
 
      ( 
        to_email,
 
        c_d,
 
        sent,
 
        text,
 
        html,
 
        urgent,
 
        comment,
 
        subject,
 
        thread,
 
        from_name,
 
        from_domain,
 
        source,
 
        next_try ......

Config: https://jira.mariadb.org/browse/MXS-3578?focusedCommentId=190701&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-190701

 Comments   
Comment by markus makela [ 2021-06-02 ]

Can you give an example SQL statement that reproduces this? I was able to reproduce this but it was with a command with syntax errors in it.

Comment by Alex [ 2021-06-02 ] 

CREATE TABLE `maillog5` (
 
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,                                                                                                                                 `to_email` varchar(255) DEFAULT NULL,
 
  `l_c_d` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),                                                                                             `c_d` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `sent_date` timestamp NULL DEFAULT NULL,                                                                                                                                          `sent` char(1) NOT NULL DEFAULT 'N',
 
  `text` mediumtext DEFAULT NULL,
 
  `html` mediumtext DEFAULT NULL,
 
  `urgent` char(1) NOT NULL DEFAULT 'N',
 
  `comment` varchar(255) DEFAULT NULL,
 
  `subject` varchar(255) DEFAULT NULL,
 
  `fio` varchar(255) DEFAULT NULL,
 
  `from_domain` varchar(255) DEFAULT NULL,
 
  `from_name` varchar(255) DEFAULT NULL,
 
  `tried_did_not_work` smallint(5) unsigned NOT NULL DEFAULT 0,
 
  `next_try` int(10) unsigned DEFAULT NULL,
 
  `priority` enum('1','2','3') NOT NULL DEFAULT '1',
 
  `thread` tinyint(3) unsigned NOT NULL DEFAULT 0,
 
  `error_info` varchar(255) NOT NULL DEFAULT '',
 
  `reply_to` varchar(255) NOT NULL DEFAULT '',
 
  `reply_to_name` varchar(255) NOT NULL DEFAULT '',
 
  `source` tinyint(3) unsigned NOT NULL DEFAULT 0,
 
  PRIMARY KEY (`id`,`urgent`,`sent`),
 
  KEY `c_d_s_i` (`c_d`,`sent`),
 
  KEY `e_i` (`to_email`),
 
  KEY `com_i` (`comment`(4)),
 
  KEY `thread_priority_c_d_i` (`thread`,`priority`,`c_d`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
 PARTITION BY KEY (`urgent`,`sent`)
 
PARTITIONS 6


SET STATEMENT max_statement_time = 60 FOR 
      INSERT INTO maillog5
 
      ( 
        to_email,
 
        c_d,
 
        sent,
 
        text,
 
        html,
 
        urgent,
 
        comment,
 
        subject,
 
        thread,
 
        from_name,
 
        from_domain,
 
        source,
 
        next_try
 
      ) VALUES (
 
        '********@gmail.com',
 
        current_timestamp(),
 
        'N',
 
        'Добрый день, Andrei821\n\nSerg.9 <https://meshok.net/info/570389> сделал ставку в 462 \nна лото Набор марок СССР 1957-58 гг.Революция.Состояние идеальное.Редкость.Читайте описание! <https://meshok.net/item/2222222>\n\nПосмотреть: https://meshok.net/item/111111111\n\nВсего наилучшего,\nИнтернет-аукцион Мешок <https://meshok.net>.\n\nP.S.: Не от
 
вечайте на это письмо. Оно создано автоматической системой рассылки уведомлений.\nВаш ответ не дойдет до нас. Если у Вас есть вопросы относительно содержания этого\nписьма - воспользуйтесь, пожалуйста, формой обращения в службу поддержки <https://meshok.net/faq.php>.',
 
        '<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Strict//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd\">\n<html xmlns=\"http://www.w3.org/1999/xhtml\">\n\n<head>\n  <meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />\n  <meta name=\"viewport\" content=\"width=device-width, initial-scale=1, minimum-scale=1, maximum-scale=1\" />\n  <!--[if !mso]><!-->\n  <meta http-equiv=\"X-UA-Compatible\" content=\"IE=Edge\" />\n  <!--<![endif]-->\n  <!--[if (gte mso 9)|(IE)]>\n      <xml>\n        <o:OfficeDocumentSettings>\n          <o:AllowPNG />\n          <o:PixelsPerInch>96</o:PixelsPerInch>\n        </o:OfficeDocumentSettings>\n      </xml>\n    <![endif]-->\n  <!--[if (gte mso 9)|(IE)]>\n      <style type=\"text/css\">\n        body {\n          width: 600px;\n          margin: 0 auto;\n        }\n        table {\n          border-collapse: collapse;\n        }\n        table,\n        td {\n          mso-table-lspace: 0pt;\n          mso-table-rspace: 0pt;\n        }\n        img {\n          -ms-interpolation-mode: bicubic;\n        }\n      </style>\n    <![endif]-->\n\n  <style type=\"text/css\">\n    body,\n    p,\n    div {\n      font-family: arial;\n      font-size: 14px;\n    }\n\n    body {\n      color: #000000;\n    }\n\n    body a {\n      color: #004B8E;\n      text-decoration: none;\n    }\n\n    body a:hover {\n      color: #b71c1c;\n      text-decoration: underline;\n    }\n\n    img.logo {\n      margin-bottom: 2em;\n    }\n\n    p {\n      margin: 0;\n      padding: 0;\n    }\n\n    .center {\n      text-align: center;\n    }\n\n    .warning {\n      color: #973c00;\n    }\n\n    table.wrapper {\n      width: 100% !important;\n      table-layout: fixed;\n      -webkit-font-smoothing: antialiased;\n      -webkit-text-size-adjust: 100%;\n      -moz-text-size-adjust: 100%;\n      -ms-text-size-adjust: 100%;\n    }\n\n    .column.of-2 {\n      width: 50%;\n    }\n\n    .column.of-3 {\n      width: 33.333%;\n    }\n\n    .column.of-4 {\n      width: 25%;\n    }\n\n    @media screen and (max-width: 480px) {\n\n      .preheader .rightColumnContent,\n      
.footer .rightColumnContent {\n        text-align: left !important;\n      }\n\n      .preheader .rightColumnContent div,\n      .preheader .rightColumnContent span,\n      .foot
 
er .rightColumnContent div,\n      .footer .rightColumnContent span {\n        text-align: left !important;\n      }\n\n      .preheader .rightColumnContent,\n      .preheader .l
 
eftColumnContent {\n        font-size: 80% !important;\n        padding: 5px 0;\n      }\n\n      table.wrapper-mobile {\n        width: 100% !important;\n        table-layout: fixed;\n      }\n\n      img.max-width {\n        height: auto !important;\n        max-width: 480px !important;\n      }\n\n      a.bulletproof-button {\n        display: block !
 
important;\n        width: auto !important;\n        font-size: 80%;\n        padding-left: 0 !important;\n        padding-right: 0 !important;\n      }\n\n      .columns {\n    
    width: 100% !important;\n      }\n\n      .column {\n        display: block !important;\n        width: 100% !important;\n        padding-left: 0 !important;\n        padding-right: 0 !important;\n        margin-left: 0 !important;\n        margin-right: 0 !important;\n      }\n    }\n  </style>\n  <!--user entered Head Start-->\n\n  <style>\n    tab
 
le.passwd {\n      border: 0;\n      background: rgb(238, 238, 238);\n      width: 100%;\n      border-collapse: collapse;\n    }\n\n    table.passwd td {\n      border: 1px soli
 
d #ccc;\n    }\n\n    p {\n      margin-bottom: 1.5em;\n      line-height: 160%;\n    }\n\n    .no-margin {\n      margin: 0 !important;\n    }\n\n    table.general-table {\n
 
 border-collapse: collapse;\n      margin-bottom: 1.5em;\n    }\n\n    table.general-table tr {\n      border-top: 1px solid #eee;\n    }\n\n    table.general-table tr:first-chi
 
ld {\n      border-top: 0px;\n    }\n\n    table.general-table td {\n      padding: 12px;\n      vertical-align: middle;\n    }\n\n    table.general-table td:first-child {\n      padding-left: 0;\n    }\n  </style>\n\n  <!--End Head user entered-->\n</head>\n\n<body>\n  <center class=\"wrapper\" data-link-color=\"#1188E6\"\n    data-body-style=\"font-size: 14px; font-family: arial; color: #000000; background-color: #ffffff;\">\n    <div class=\"webkit\">\n      <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" class=\"wrapper\" bgcolor=\"#ffffff\">\n        <tr>\n          <td valign=\"top\" bgcolor=\"#ffffff\" width=\"100%\">\n            <table width=\"100%\" role=\"content-contai
 
ner\" class=\"outer\" align=\"center\" cellpadding=\"0\" cellspacing=\"0\"\n              border=\"0\">\n              <tr>\n                <td width=\"100%\">\n                  <table width=\"100%\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n                    <tr>\n                      <td>\n                        <!--[if mso]>\n                          <center>\n                          <table><tr><td width=\"600\">\n                          <![endif]-->\n                        <table width=\"100%\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\"\n                          style=\"width: 100%; max-width:600px;\">\n                          <tr>\n                            <td role=\"modules-container\"\n                              style=\"padding: 0px 0px 0px 0px; color: #000000; text-align: left;\" bgcolor=\"#ffffff\"\n                              width=\"100%\" align=\"left\">\n                              <table class=\"module preheader preheader-hide\" role=\"module\" data-type=\"preheader\"\n                                border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"100%\"\n                                style=\"display: none !important; mso-hide: all; visibility: hidden; opacity: 0
 
; color: transparent; height: 0; width: 0;\">\n                                <tr>\n                                  <td role=\"module-content\">\n                                    <p></p>\n                                  </td>\n                                </tr>\n                              </table>\n\n                              <table style=\"display:none\" class=\"wrapper\" role=\"module\" data-type=\"image\" border=\"0\" cellpadding=\"0\"\n                                cellspacing=\"0\" width=\"100%\" style=\"table-layout: fixed;\">\n                                <tr>\n                                  <td style=\"font-size:6px;line-height:10px;padding:0px 0px 0px 0px;\" valign=\"top\"\n>',
 
        'Y',
 
        'V2',
 
        'Ставка',
 
        '55',
 
        'Мешок',
 
        'meshok.net',
 
        '1',
 
        unix_timestamp()
 
      )

Comment by markus makela [ 2021-06-02 ]

Thanks, I was able to fully reproduce the problem with the SQL.

Comment by Johan Wikman [ 2021-06-02 ]

alexcool This actually seems to be related to the use of current_timestamp().

The problem will be sorted out, but meanwhile you can work around this if you change current_timestamp() to now(). They are synonyms so there will be no functional difference.

Comment by Alex [ 2021-06-02 ]

OK, will do

Comment by Johan Wikman [ 2021-06-02 ]

Fixed by MXS-3589

Generated at Thu Feb 08 04:22:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.