Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL my.cnf sql_mode schema modifications     - Ubuntu system grub repair method (Linux)

- CentOS yum source deployment (Linux)

- About DataGuard three protected mode test (Database)

- Elaborate .NET Multithreading: Concepts (Programming)

- CentOS 6.5 can not connect to the network under VMware (Linux)

- AngularJS achieve picture upload feature (Programming)

- To create a secure network firewall with iptables Under Linux (Linux)

- Ubuntu install Liferea news subscription software (Linux)

- About MongoDB query method according to fuzzy field (Database)

- Using nmcli commands to manage network in RedHat / CentOS 7.x (Linux)

- HA-Federation-HDFS + Yarn cluster deployment (Server)

- In-depth summary of the PHP core of object-oriented (Programming)

- OpenJDK 7 compiled under Ubuntu 14.04.3 64-bit (Linux)

- Linux tar compressed exclude a folder (Linux)

- Upgrading to MySQL 5.7 partitioning problem solving (Database)

- Use HttpClient remote interface testing (Programming)

- Easy to get hidden administrator account (Linux)

- Use LKM change the default linux security level (Linux)

- CentOS7 install MySQL 5.5 (Database)

- ThinkPad X201i installation notes Debian 7.5 (Linux)

 
         
  MySQL my.cnf sql_mode schema modifications
     
  Add Date : 2016-04-24      
         
         
         
  1. Sql_mode mode
The mysql database has an environment variable sql_mode, defined mysql should support sql syntax, data verification and so on! We can see sql_mode current database used in the following ways:

 mysql> select @@ sql_mode;
+ ------------------------------------------------- --------------- +
| @@ Sql_mode |
+ ------------------------------------------------- --------------- +
| STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION |
+ ------------------------------------------------- --------------- +
Or by looking at the way the system variables:
mysql> show variables like 'sql_mode%' \ G;
*************************** 1. row ******************** *******
Variable_name: sql_mode
        Value: STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION


mysql5.0 sql_mode later supports three modes: ANSI, TRADITIONAL and STRICT_TRANS_TABLES.
ANSI modes: loose mode, insert data validation, if you do not meet the definition of the type or length, adjust the data type or truncated saved messages warning warning.
TRADITIONAL mode: Strict mode, when inserting data into mysql database, data strict verification, to ensure that incorrect data can not be inserted, the error message error. When applied to things, things will be rolled back.
STRICT_TRANS_TABLES mode: Strict mode, strict parity data, incorrect data can not be inserted, the error message error.

1.2 ANSI mode

 mysql> set @@ sql_mode = ANSI;
Query OK, 0 rows affected (0.00 sec)
  
mysql> create table test (name varchar (4), pass varchar (4));
Query OK, 0 rows affected (0.03 sec)
  
mysql> insert into test values ​​( 'aaaaa', 'aaaaa'), ( 'bbbb', 'bbbb');
Query OK, 2 rows affected, 2 warnings (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 2
  
mysql> show warnings;
+ --------- + ------ + -------------------------------- ----------- +
| Level | Code | Message |
+ --------- + ------ + -------------------------------- ----------- +
| Warning | 1265 | Data truncated for column 'name' at row 1 |
| Warning | 1265 | Data truncated for column 'pass' at row 1 |
+ --------- + ------ + -------------------------------- ----------- +
2 rows in set (0.00 sec)
  
mysql> select * from test;
+ ------ + ------ +
| Name | pass |
+ ------ + ------ +
| Aaaa | aaaa |
| Bbbb | bbbb |
+ ------ + ------ +
2 rows in set (0.00 sec)


We can see that in ANSI mode, when we insert data, when the length of the column does not meet the requirements, the data will be inserted into the same success, but beyond the column length field truncation, while warning report warns.

1.3 STRICT_TRANS_TABLES mode

 mysql> set @@ sql_mode = STRICT_TRANS_TABLES;
Query OK, 0 rows affected (0.00 sec)
  
mysql> create table test (name varchar (4), pass varchar (4));
Query OK, 0 rows affected (0.02 sec)
  
mysql> insert into test values ​​( 'aaaaa', 'aaaaa'), ( 'bbbb', 'bbbb');
ERROR 1406 (22001): Data too long for column 'name' at row 1
  
mysql> show errors;
+ ------- + ------ + ---------------------------------- -------- +
| Level | Code | Message |
+ ------- + ------ + ---------------------------------- -------- +
| Error | 1406 | Data too long for column 'name' at row 1 |
+ ------- + ------ + ---------------------------------- -------- +
1 row in set (0.00 sec)
  
mysql> select * from test;
Empty set (0.00 sec)


We can see that in STRICT_TRANS_TABLES mode, when we insert data, mysql will strictly verify the data, when inserted into the column value is not found to meet the requirements, direct reports error error to ensure that incorrect data can not be inserted into the database.

1.3 TRADITIONAL mode

 mysql> set @@ sql_mode = TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)
  
mysql> create table test (name varchar (4), pass varchar (4));
Query OK, 0 rows affected (0.02 sec)
  
mysql> insert into test values ​​( 'aaaaa', 'aaaaa'), ( 'bbbb', 'bbbb');
ERROR 1406 (22001): Data too long for column 'name' at row 1
  
mysql> show errors;
+ ------- + ------ + ---------------------------------- -------- +
| Level | Code | Message |
+ ------- + ------ + ---------------------------------- -------- +
| Error | 1406 | Data too long for column 'name' at row 1 |
+ ------- + ------ + ---------------------------------- -------- +
1 row in set (0.00 sec)
  
mysql> select * from test;
Empty set (0.00 sec)


TRADITIONAL mode and Results STRICT_TRANS_TABLES execution mode, in this case the same.

 mysql> select @@ sql_mode \ G;
*************************** 1. row ******************** *******
@@ Sql_mode: STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, E
RROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, NO_AUTO_CREATE_USER
1 row in set (0.00 sec)


Look TRADITIONAL mode, we found that in TRADITIONAL mode, all transactional storage engine, non-transactional storage engine checks, date type month and day portion can not contain 0, 0 can not have such a date (0000-00-00) the data can not divide by zero, prohibiting grant automatically create new users and some checking.


Note: sql_mode we set here is a session-level. Alternatively, you can directly modify the my.ini file, find sql_mode, then set the new model can be!
E.g:
    vi /etc/my.cnf
In the [mysqld] add the following as the following:
sql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES
     
         
         
         
  More:      
 
- Linux SU command security Suggestions (Linux)
- C # / iOS / Android Universal Encryption and decryption (Programming)
- Linux shell script debugging (Linux)
- Linux security settings Basics (Linux)
- Linux Getting Started tutorial: Experience QEMU virtual machine articles (Linux)
- How open source code libraries hosted on Github (Linux)
- Shell array: Define Shell array, the array length (Programming)
- MySQL related statements (CRUD) (SQLyog software) (Database)
- Linux cut Command Study Notes (Linux)
- To repair Shell script by default open in Ubuntu (Linux)
- 10 Nginx safety tips (Linux)
- CentOS7 Kubernetes used on container management (Server)
- Struts2 configure a static resource files without Struts processing (regular match) (Programming)
- Ubuntu and derivative users to install the system launcher SimDock 1.3 (Linux)
- xCAT install and update software (Linux)
- MySQL tmpdir parameter modification (Database)
- Nagios (centreon) monitoring LVS (Server)
- HBase table data processing tab (Database)
- C # Future: Method Contract (Programming)
- Ubuntu 14.10 / 14.04 how to install Quick Start tool Mutate 2.2 (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.