Shulou(Shulou.com)06/01 Report--

The topic description describes in detail the risk of data errors that may occur when SQL_MODE is in strict mode. Question 1: if the default date is' 0000-00-0000: 0000: 00, an error will be reported directly when querying.

Caused by: java.sql.SQLException: Value '0000-00-0000: 00 can not be represented as java.sql.Timestamp

The following parameter needs to be added to URL: zeroDateTimeBehavior=convertToNull. Due to the different parameter configuration of the UAT environment and the performance test environment, the 2OMS database results in the application SQL execution error in the performance test environment, and the related parameters are STRICT_TRANS_TABLES and explicit_defaults_for_timestamp. Data risk 1 integer type data value truncation risk

Focus on TINYINT types

Other integer types have a low probability of overflow at the initial stage of launch

With 'payment_display_ mode` tinyint (4)

DEFAULT NULL COMMENT 'payment due date display method:

11-the latest remittance date 12-maturity date', "as an example.

Example: in non-strict mode, the application is

Field payment_display_mode sets a value

Less than-128 or greater than 127

The excess will be truncated (if it exceeds-128, it will be stored as-128, for example,-200 will become-128 by default; if it exceeds 127, it will be stored as 127); for example, 300 will become 127 by default.

2 floating point type truncation

With `longitude` DECIMAL (19pm 5) NULL

DEFAULT '0.00000' COMMENT' longitude coordinates'"

As an example.

Example: in non-strict mode, the application is

Field longitude sets a value

More than 5 decimal places or integer more than 14 digits

The excess will be truncated.

3 string type risk

With `MODIFIER_ user` VARCHAR (30) NUL

L DEFAULT NULL COMMENT 'UPDAT user account',

As an example.

Example: in non-strict mode

The application is the field MODIFIER_USER

If you set a value that exceeds 30 characters, the excess will be truncated.

4 time type risk because the JAVA code thinks that 0000-00-0000: 00:00 time is an illegal time, and parameters are provided in JDBC to control its processing, it can be converted to null by adding zeroDateTimeBehavior=convertToNull.

Sample connection string:

Jdbc:mysql://10.202.198.201:3319/oms6?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull5NOT NULL type risk

In non-strict mode, no constraints are defined for fields.

Assign or give a null value to the NOT NULL field

In the case of no defined constraints and no default values: data value type

The field value defaults to 0 and the field value of string type defaults to the empty string''. From the analysis of SQL statements and stored procedure processing, it is compatible in most cases, but the R & D team is still asked to evaluate it carefully.

The cause of the problem database SQL_MODE is set to strict mode. Solution 1 suggests that the DBA team should set SQL_MODE to strict mode, run the system for testing, and modify the program according to the data hidden dangers of error location. 2 set the server SQL_MODE to strict mode. Knowledge point 1

The database SQL_MODE is set to strict mode to divide the problems into the following categories

1. The date defaults to '0000-00-0000: 00: 00: 00'. An error will be reported directly when querying.

2. When the field type is set to NOT NULL, inserting a null value will report an error.

3. When inserting values that are outside the range of numeric field types, an error is reported directly

2STRICT_TRANS_TABLES interpretation:

Links to official documents:

Http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict

Official document explanation: 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 main purpose of using this parameter is to make the database tend to report errors in the face of incorrect data and operations, rather than to give warnings. It is easy to find the problem in advance and standardize the operation of the database. three

The system tends to warn when the database SQL_MODE is set to non-strict mode

. The system takes the maximum value when inserting a field value beyond the numeric type range.

The inserted character type field value is truncated when the value exceeds the length range.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.