I've simple transaction here;
START TRANSACTION;
INSERT INTO user_photos(id, user_id, meta, upload_timestamp) VALUES (1211, 1, '{"a"}',
from_unixtime(unix_timestamp()));
UPDATE user_photos set user_id = '{"test"}' WHERE user_id = 1;
COMMIT;
The INSERT statement will work but the second statement, the UPDATE statement, will fail as the user_id column has type of INTEGER.
I would like to have a transaction which will rollback if any statement(inside transaction) fails. This transaction doesn't behave like that.
I use INNODB engine. Why is this transaction not working?
MYSQL has no "autorollback" features at transaction level.
If some statement has failed, this statement is rolled back but the transaction is not rolled back. If you run COMMIT, MySQL commits all non rolled back statements of the current transaction: that is what happened. if you run ROLLBACK, all statements are rolled back whether they failed or succeeded.
If you want a different behaviour you need to code it in your application code. This can be done with a stored procedure: see mysql transaction - roll back on any exception.