Transaction MySQL

Question

How can I start a transaction when there is any error in the SQL statements the system will rollback the changes automatically?

I've the following SQL statement

START TRANSACTION;

BEGIN;

INSERT INTO `users`(id,name,gender,email,age)
    VALUES(11121,'sss',0,'ssss',22);

INSERT INTO `teachers`(`UserId`,`teachingSubject`)
    VALUES(11121,300);

COMMIT;

It doesn't rollback when the second statement counter an error. Why? the 'teachingSubject' is TINYINT(2), the second statement will run out of its range. I want to rollback all statements.

Below is my procedure.

mysql> source d:\s.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

ERROR 1264 (22003): Out of range value for column 'te
Query OK, 0 rows affected (0.09 sec)

mysql> SELECT * FROM teachers
    -> ;
+--------+-----------------+
| UserId | teachingSubject |
+--------+-----------------+
|  11111 |               1 |
|  11112 |               9 |
|  11113 |             100 |
+--------+-----------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM users;
+-------+--------+--------+----------------+------+
| id    | name   | gender | email          | age  |
+-------+--------+--------+----------------+------+
| 11111 | Killer |       | ssss@sss       |   12 |
| 11112 | sss    |        | ssss           |   22 |
| 11113 | sss    |        | ssss           |   22 |
| 11114 | sss    |        | ssss           |   22 |
| 11115 | sss    |        | ssss           |   22 |
| 11116 | sss    |        | ssss           |   22 |
| 11117 | sss    |        | ssss           |   22 |
| 11118 | sss    |        | ssss           |   22 |
| 11119 | sss    |        | ssss           |   22 |
| 11120 | sss    |        | ssss           |   22 |
| 11121 | sss    |        | ssss           |   22 |
| 12345 | Sefler |       | [email protected] |   12 |
+-------+--------+--------+----------------+------+
12 rows in set (0.00 sec)

No record with id=11121 in teachers table but users table does.

Answer 1

MySQL won't throw an error when the int overflows. Instead it'll just truncate it to the highest possible value. In the case of an unsigned tinyint that's 255. But no actual error will be thrown.