title: How can I perform multi-table delete in safe mode with binary logging enabled?
body: <p>Let's pretend, that I have following data structure:</p>
  
  <pre><code>DROP TABLE IF EXISTS `A`;
  DROP TABLE IF EXISTS `B`;
  DROP TABLE IF EXISTS `C`;
  
  CREATE TABLE IF NOT EXISTS `C` (
      `ID_C`
          INT UNSIGNED
          NOT NULL
          AUTO_INCREMENT,
  
      PRIMARY KEY (`ID_C`)
  )
  ENGINE=InnoDB;
  
  CREATE TABLE IF NOT EXISTS `B` (
      `ID_B`
          INT UNSIGNED
          NOT NULL
          AUTO_INCREMENT,
  
      `REF_C`
          INT UNSIGNED
          NOT NULL,
  
      PRIMARY KEY (`ID_B`),
  
      INDEX `FK_C` (`REF_C` ASC),
  
      CONSTRAINT `FK_C`
          FOREIGN KEY (`REF_C`)
          REFERENCES `C` (`ID_C`)
              ON DELETE CASCADE
              ON UPDATE CASCADE
  ) ENGINE=InnoDB;
  
  CREATE TABLE IF NOT EXISTS `A` (
      `ID_A`
          INT UNSIGNED
          NOT NULL
          AUTO_INCREMENT,
  
      `REF_B`
          INT UNSIGNED
          NOT NULL,
  
      PRIMARY KEY (`ID_A`),
  
      INDEX `FK_B` (`REF_B` ASC),
  
      CONSTRAINT `FK_B`
          FOREIGN KEY (`REF_B`)
          REFERENCES `B` (`ID_B`)
              ON DELETE CASCADE
              ON UPDATE CASCADE
  ) ENGINE=InnoDB;
  
  INSERT INTO `C`
      (`ID_C`)
  VALUES
      (NULL),
      (NULL);
  
  INSERT INTO `B`
      (`ID_B`, `REF_C`)
  VALUES
      (NULL, 1),
      (NULL, 1),
      (NULL, 2),
      (NULL, 2);
  
  INSERT INTO `A`
      (`ID_A`, `REF_B`)
  VALUES
      (NULL, 1),
      (NULL, 2),
      (NULL, 3),
      (NULL, 4);
  </code></pre>
  
  <p>Table <code>B</code> might have more than 3000 records: approximately by 600 records, referenced to different rows in table <code>C</code>. There are two settings enabled on my server:</p>
  
  <pre><code>SELECT
      @@SQL_SAFE_UPDATES as `safe mode`, -- result: 1
      @@LOG_BIN as `binary log`;         -- result: 1
  </code></pre>
  
  <p><strong>Question:</strong> <em>How can I efficiently delete all records from table <code>A</code>, which is referenced to table <code>C</code>'s records through table <code>B</code> without issuing warnings ?</em></p>
  
  <hr>
  
  <p><strong>What have I tried:</strong></p>
  
  <pre><code>DELETE
      `A`.*
  FROM
      `A` INNER JOIN `B` ON `REF_B` = `ID_B`
  WHERE
      `B`.`REF_C` = 1;
  </code></pre>
  
  <p>DBMS server issues <code>safe_mode</code> error:</p>
  
  <blockquote>
    <p>Error Code: 1175. You are using safe update mode and you tried to
    update a table without a WHERE that uses a KEY column To disable safe
    mode, toggle the option in Preferences -> SQL Queries and reconnect.</p>
  </blockquote>
  
  <p>I have removed <code>B.</code> alias:</p>
  
  <pre><code>DELETE
      `A`.*
  FROM
      `A` INNER JOIN `B` ON `REF_B` = `ID_B`
  WHERE
      `REF_C` = 1;
  </code></pre>
  
  <p>Yeah, it succeeded, but I've got this:</p>
  
  <blockquote>
    <p>2 row(s) affected, 1 warning(s): 1592 Unsafe statement written to the
    binary log using statement format since BINLOG_FORMAT = STATEMENT.
    Statements writing to a table with an auto-increment column after
    selecting from another table are unsafe because the order in which
    rows are retrieved determines what (if any) rows will be written. This
    order cannot be predicted and may differ on master and the slave.</p>
  </blockquote>
  
  <p>Also, I was trying to force PRIMARY KEY usage:</p>
  
  <pre><code>DELETE
      `A`.*
  FROM
      `A` INNER JOIN `B` ON `REF_B` = `ID_B`
  WHERE
      `A`.`ID_A` &gt; 0
      AND
      `REF_C` = 1;
  </code></pre>
  
  <p>But it not helped too. Am I doing something BAD or EVIL to my server? What approach is correct? Am I missing something?</p>
  
  <p>Thanks in advance. Any help will be appreciated.</p>
  
  <hr>
  
  <p><strong>P.S.:</strong> I know how to use Google and searchbar. Here is what I've found:</p>
  
  <ol>
  <li><p><a href="https://stackoverflow.com/questions/12275864/multiple-delete-not-working-with-inner-join">https://stackoverflow.com/questions/12275864/multiple-delete-not-working-with-inner-join</a></p></li>
  <li><p><a href="http://tech-solutions4u.blogspot.ru/2012/09/mysql-multi-delete-issue-in-safe-mode.html" rel="nofollow noreferrer">http://tech-solutions4u.blogspot.ru/2012/09/mysql-multi-delete-issue-in-safe-mode.html</a></p></li>
  </ol>
  
  <p>And so on. I've tried, but, in the end, I do not like the idea of disabling server  features (which was set not by me) even "for a while...".</p>
  
  <hr>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I know, that there is a workaround to save <code>GROUP_CONCAT(ID_B)</code> in temporary variable and perform delete by it's "scalar" value:</p>
  
  <pre><code>SELECT GROUP_CONCAT(`ID_B`) INTO @tmp FROM `B` WHERE `REF_C` = 1;
  
  DELETE FROM
      `A`
  WHERE
      FIND_IN_SET(`REF_B`, @tmp)
      AND
      `ID_A` &gt; 0;
  </code></pre>
  
  <p>But it will be approximately <code>600 * 5 = 3000</code> characters, so this idea is not prefered too.
  I mean, it will be the last option, if nothing else is possible.</p>