How can I perform multi-table delete in safe mode with binary logging enabled?

Question

Let's pretend, that I have following data structure:

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

Table B might have more than 3000 records: approximately by 600 records, referenced to different rows in table C. There are two settings enabled on my server:

SELECT
    @@SQL_SAFE_UPDATES as `safe mode`, -- result: 1
    @@LOG_BIN as `binary log`;         -- result: 1

Question: How can I efficiently delete all records from table A, which is referenced to table C's records through table B without issuing warnings ?

---

What have I tried:

DELETE
    `A`.*
FROM
    `A` INNER JOIN `B` ON `REF_B` = `ID_B`
WHERE
    `B`.`REF_C` = 1;

DBMS server issues safe_mode error:

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.

I have removed B. alias:

DELETE
    `A`.*
FROM
    `A` INNER JOIN `B` ON `REF_B` = `ID_B`
WHERE
    `REF_C` = 1;

Yeah, it succeeded, but I've got this:

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.

Also, I was trying to force PRIMARY KEY usage:

DELETE
    `A`.*
FROM
    `A` INNER JOIN `B` ON `REF_B` = `ID_B`
WHERE
    `A`.`ID_A` > 0
    AND
    `REF_C` = 1;

But it not helped too. Am I doing something BAD or EVIL to my server? What approach is correct? Am I missing something?

Thanks in advance. Any help will be appreciated.

---

P.S.: I know how to use Google and searchbar. Here is what I've found:

1. https://stackoverflow.com/questions/12275864/multiple-delete-not-working-with-inner-join

2. http://tech-solutions4u.blogspot.ru/2012/09/mysql-multi-delete-issue-in-safe-mode.html

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

---

EDIT:

I know, that there is a workaround to save GROUP_CONCAT(ID_B) in temporary variable and perform delete by it's "scalar" value:

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` > 0;

But it will be approximately 600 * 5 = 3000 characters, so this idea is not prefered too. I mean, it will be the last option, if nothing else is possible.

Answer 1

It seems there are two problems:

1. You have safe updates mode on. Here is a sample about --safe-update from MySQL docs:

   For beginners, a useful startup option is --safe-updates (or --i-am-a-dummy, which has the same effect). It is helpful for cases when you might have issued a DELETE FROM tbl_name statement but forgotten the WHERE clause. Normally, such a statement deletes all rows from the table.

   As you see, this mode implies you know no SQL at all and ready to shoot yourself in the foot at any moment. So this mode fires warnings on each complicated query. If you do not want to switch the mode off, the only option you have is to select the ids of all rows you want to delete, and in a second query explicitly pass the ids to the WHERE clause of the delete statement. At the same time, this approach will solve the second problem.

2. The second warning with error code 1592 is something completely different about logging the changes into the binary log.

   Error: 1592 SQLSTATE: HY000 (ER_BINLOG_UNSAFE_STATEMENT)

   Message: Statement may not be safe to log in statement format.

   The previous command I suggested does have a problem that I did not specify the ORDER BY clause but used a LIMIT. The order in this case is theoretically undefined, so you can either drop the LIMIT clause, or specify the order explicitly:

   DELETE FROM A USING
   table_a as A
   JOIN (
     SELECT a_id
     FROM table_a as A
     JOIN table_b as B ON A.ref_b = A.b_id
     WHERE B.ref_c = 1
     ORDER BY a_id
     LIMIT 1000
   ) a_ids ON A.a_id = a_ids.a_id;