#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    2
    Rep Power
    0

    Having trouble with Savepoint and Rollback


    Hey, I'm a new user of SQL. I'm using the command line client for the MySQL 5.6 Server. I'm having a problem with using savepoint and rollback commands; I'm receiving an error saying my savepoint does not exist. Here's the problem verbatim from my screen (any help would be much appreciated):

    mysql> INSERT INTO CUSTOMER_TBL VALUES (615, 'FRED WOLF', '109 MEMORY LANE',
    -> 'PLAINFIELD', 'IN', 46113, '3175555555', NULL);
    Query OK, 1 row affected (1.27 sec)

    mysql> INSERT INTO CUSTOMER_TBL VALUES
    -> (559, 'RITA THOMPSON', '125 PEACHTREE', 'INDIANAPOLIS', 'IN', 46248, '317
    1111111', NULL);
    Query OK, 1 row affected (0.08 sec)

    mysql> INSERT INTO CUSTOMER_TBL VALUES
    -> (715, 'BOB DIGGLER', '1102 HUNTINGTON ST', 'SHELBY', 'IN', 41234, '317222
    2222', NULL);
    Query OK, 1 row affected (0.09 sec)

    mysql> savepoint sp1;
    Query OK, 0 rows affected (0.03 sec)

    mysql> UPDATE CUSTOMER_TBL
    -> SET CUST_NAME = 'FRED WOLF' WHERE CUST_ID = '559';
    Query OK, 1 row affected (0.22 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

    mysql> UPDATE CUSTOMER_TBL
    -> SET CUST_ADDRESS = 'APT C 4556 WATERWAY' WHERE CUST_ID = '615';
    Query OK, 1 row affected (0.08 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

    mysql> UPDATE CUSTOMER_TBL
    -> SET CUST_CITY = 'CHICAGO' WHERE CUST_ID = '715';
    Query OK, 1 row affected (0.06 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

    mysql> ROLLBACK TO SP1;
    ERROR 1305 (42000): SAVEPOINT SP1 does not exist
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    The save point identifier is probably case sensitive.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    2
    Rep Power
    0

    Already Eliminated that Possibility


    Thanks for the reply, but I already tried that theory. It's not a matter of being case-sensitive.

    I'm surprised more people have not encountered this problem.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,436
    Rep Power
    1688
    You are using InnoDB for your table? Rollback and savepoints, etc., do not function with myISAM. The savepoint command will work:
    Code:
    mysql> savepoint sp1;
    Query OK, 0 rows affected (0.00 sec)
    but will not be valid:
    Code:
    mysql> update test set name='after' where id=2;
    Query OK, 1 row affected (0.02 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> rollback to sp1;
    ERROR 1305 (42000): SAVEPOINT sp1 does not exist
    Code:
    CREATE TABLE `test` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(48) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`),
      KEY `name` (`name`)
    ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo