CODEWITHSUNDEEP
mysqlsqltransactions
benstpierre
benstpierre

Reputation: 33581

Why does start transaction and rollback fail to rollback when running scripts in MySQL 5?

I have the following script...

START TRANSACTION;

INSERT INTO `Image` (`id`,`created`,`deleted`,`fileName`,`used`) VALUES (-114, NULL, NULL, 'c21_1.jpg', NULL);
INSERT INTO `Image` (`id`,`created`,`deleted`,`fileName`,`used`) VALUES (-115, NULL, NULL, 'c21_2.jpg', NULL);
INSERT INTO `Image` (`id`,`created`,`deleted`,`fileName`,`used`) VALUES (-116, NULL, NULL, 'c21_3.jpg', NULL);
INSERT INTO `Image` (`id`,`created`,`deleted`,`fileName`,`used`) VALUES (-117, NULL, NULL, 'c21_4.gif', NULL);
INSERT INTO `Image` (`id`,`created`,`deleted`,`fileName`,`used`) VALUES (-118, NULL, NULL, 'c21_5.gif', NULL);
INSERT INTO `Image` (`id`,`created`,`deleted`,`fileName`,`used`) VALUES (-119, NULL, NULL, 'c21_6.gif', NULL);
INSERT INTO `Image` (`id`,`created`,`deleted`,`fileName`,`used`) VALUES (-120, NULL, NULL, 'c21_7.gif', NULL);
INSERT INTO `Image` (`id`,`created`,`deleted`,`fileName`,`used`) VALUES (-121, NULL, NULL, 'AIRMILES_MIN_RBLUE.png', NULL);
INSERT INTO `Image` (`id`,`created`,`deleted`,`fileName`,`used`) VALUES (-122, NULL, NULL, 'c21_logo_commercial-WHT.png', NULL);
INSERT INTO `Image` (`id`,`created`,`deleted`,`fileName`,`used`) VALUES (-123, NULL, NULL, 'c21_logo_fhe_pms124-WHT.png', NULL);
INSERT INTO `Image` (`id`,`created`,`deleted`,`fileName`,`used`) VALUES (-124, NULL, NULL, 'CENTURY-21-Logo-Century21.png', NULL);
INSERT INTO `Image` (`id`,`created`,`deleted`,`fileName`,`used`) VALUES (-125, NULL, NULL, 'Easter-Seals-Logos-2005-008-1.png', NULL);
INSERT INTO `Image` (`id`,`created`,`deleted`,`fileName`,`used`) VALUES (-126, NULL, NULL, 'Easter-Seals-Logos-2005-008-2.png', NULL);
INSERT INTO `Image` (`id`,`created`,`deleted`,`fileName`,`used`) VALUES (-127, NULL, NULL, 'kids-to-camp-EN.png', NULL);



INSERT INTO `AgencyImage` (`agency_id`,`image_id`) VALUES   (1, -115);
INSERT INTO `AgencyImage` (`agency_id`,`image_id`) VALUES   (1, -116);
INSERT INTO `AgencyImage` (`agency_id`,`image_id`) VALUES   (1, -117);
INSERT INTO `AgencyImage` (`agency_id`,`image_id`) VALUES   (1, -118);
INSERT INTO `AgencyImage` (`agency_id`,`image_id`) VALUES   (1, -119);
INSERT INTO `AgencyImage` (`agency_id`,`image_id`) VALUES   (1, -120);
INSERT INTO `AgencyImage` (`agency_id`,`image_id`) VALUES   (1, -121);
INSERT INTO `AgencyImage` (`agency_id`,`image_id`) VALUES   (1, -122);
INSERT INTO `AgencyImage` (`agency_id`,`image_id`) VALUES   (1, -123);
INSERT INTO `AgencyImage` (`agency_id`,`image_id`) VALUES   (1, -124);
INSERT INTO `AgencyImage` (`agency_id`,`image_id`) VALUES   (1, -125);
INSERT INTO `AgencyImage` (`agency_id`,`image_id`) VALUES   (1, -126);
INSERT INTO `AgencyImage` (`agency_id`,`image_id`) VALUES   (1, -127);


ROLLBACK;

I wanted to validate the script ran before commiting the code. However the first time this ran it did not roll anything back.

What am I doing wrong?

Upvotes: 1

Views: 166

Answers (1)

King Skippus
King Skippus

Reputation: 3826

Is it a MyISAM table? If so, you need to switch to InnoDB if you want to use transactions. If you're running the queries from something like PHP, make sure you have autocommit turned off.

Upvotes: 1

Related Questions