ON DELETE CASCADE not working
You declared the FK constraints "inline" and you fell into a mysql trap. You can check with SHOW CREATE TABLE followers that there is no FK now! This known behaviour is mentioned in the mysql documentation:


  MySQL does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. 


It should really say that the syntax is recognized as valid SQL but is ignored and the foreign key constraints are not created.



To solve the issue, the CREATE TABLE statement should have been:

CREATE TABLE followers (
    id_follower INT NOT NULL,
    id_following INT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    PRIMARY KEY (id_follower, id_following),
    CONSTRAINT follower_fk
        FOREIGN KEY (id_follower) 
        REFERENCES users (id) ON DELETE CASCADE, 
    CONSTRAINT following_fk
        FOREIGN KEY (id_following) 
        REFERENCES users (id) ON DELETE CASCADE
);


If you already have data in the table, you can add the constraints with an ALTER TABLE statement:

ALTER TABLE followers (
    ADD CONSTRAINT follower_fk
        FOREIGN KEY (id_follower) 
        REFERENCES users (id) ON DELETE CASCADE, 
    ADD CONSTRAINT following_fk
        FOREIGN KEY (id_following) 
        REFERENCES users (id) ON DELETE CASCADE
);