MYSQL Strict Mode

Question

For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, because the earlier rows have been inserted or updated, the result is a partial update. To avoid this, use single-row statements, which can be aborted without changing the table.

For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and inserts the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement. Implicit defaults are described in Section 11.6, “Data Type Default Values”.

I tried to set STRICT_TRANS_TABLE and tried inserting a invalid record without a transaction. Despite as documented it showed a error.

 mysql> show create table mydemo;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                    |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| mydemo | CREATE TABLE `mydemo` (
  `pk` int(20) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM mydemo;
+----+------+
| pk | name |
+----+------+
|  1 | Test |
+----+------+
1 row in set (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into mydemo values (2, "abcdefghijklmn");
ERROR 1406 (22001): Data too long for column 'name' at row 1

mysql> select @@SESSION.autocommit;
+----------------------+
| @@SESSION.autocommit |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

1. What do they mean by Non transaction table ?
2. In the above, why do it shows error instead of warning ?
3. What if both the modes are set down ?

Answer 1

Your table type is innodb, which is a transactional table type (supports transactions), therefore te paragraph before the two you quoted applies:

For transactional tables, an error occurs for invalid or missing values in a data-change statement when either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled. The statement is aborted and rolled back.

The two paragraphs you quoted applies to non-transactional tables, such as myisam.