MYSQL-TOOLS

Comparison of graphical tools for MySQL

Home Data Validation in MySQL

Data Validation in MySQL

In this lesson of the MySQL tutorial, you will learn...
  1. Different type of MySQL programs and executables.
  2. Managing SQL Modes.
  3. Important SQL Modes.
  4. Strict checking using SQL Mode.


Data Validation

Print PDF

No one wants their data to be invalid, but under certain circumstances, we may not have the luxury to ignore or avoid all invalid data. This lesson and text outlines the MySQL data validation rules and mechanisms. It stands to reason that validation only applies when data is added or altered.

Though most validation of data is generally controlled by applications, there are still ample situations where data is manipulated directly on the database. This lesson provides a foundation for managing validation of data changes at MySQL level.

Both INSERT and UPDATE statements can be run with the IGNORE option to allow for certain type of validation errors to go through.

MySQL SQL Modes

Server SQL modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers. The MySQL Server apply these modes individually to different clients. Each mode can be independently switched on and off.

The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients. This capability enables each application to tailor the server's operating mode to its own requirements.

The mode values is a list of different modes separated by comma and the default value is empty (no modes set) or by default, no special server SQL modes are enabled during installation.

Setting SQL mode

Here are ways to manage SQL mode, globally or for specific to a session:

  • Set the default SQL mode when you start the server:
    root> mysqld --sql-mode
  • Set it from within MySQL connection (local or global):
    SET [SESSION|GLOBAL] sql_mode='modes';
  • Set modes via configuration file (my.ini or my.cnf):
    sql-mode=modes
  • Each mode can be independently switched on and off.
  • Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that point onwards.
  • Setting the SESSION variable affects only the current client.
  • Any client can change its own session sql_mode value at any time.
  • A SQL mode is not linked to a particular database.

To retrieve the current global or session sql_mode, use:

Code Sample:

SELECT @@global.sql_mode, @@session.sql_mode;

Some important SQL Modes

The following list shows several important values for sql_mode with an explanation and its effect:

ANSI_QUOTES

Treat a double-quote " as an identifier quote character similar to the backtick `, and not as a string quote character. You can still use backtick ` to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotes to quote literal strings, because those strings will be interpreted as identifiers.

We have seen the use of this mode in the Identifiers lesson.

IGNORE_SPACE

Allow (ignore) spaces between a function name and the left parenthesis <( character>. This mode allows for built-in function names to be treated as reserved words. Identifiers that are the same as function names must be quoted. For example, as there is a COUNT() function, the use of count as a table name in the following statement causes an error:

Code Sample:

DROP TABLE IF EXISTS count;

CREATE TEMPORARY TABLE count (i INT);
INSERT INTO count VALUES (123);
SELECT * FROM COUNT;
DROP TABLE IF EXISTS count;

SET sql_mode='IGNORE_SPACE';
CREATE TEMPORARY TABLE count (i INT);
INSERT INTO count VALUES (456);
SELECT * FROM COUNT;
DROP TABLE IF EXISTS count;

CREATE TEMPORARY TABLE `count` (i INT);
INSERT INTO count VALUES (789);
SELECT * FROM COUNT;
DROP TABLE IF EXISTS count;

SET sql_mode='';
Code Explanation

The first creation succeeds and second create fails after setting IGNORE_SPACE.

The third create succeeds due to quoting of the reserved name, even though IGNORE_SPACE is still set.

Note: The IGNORE_SPACE SQL mode applies to built-in functions, not to user-defined functions or stored functions, as spaces are allowed after a UDF or stored function name, regardless of IGNORE_SPACE.

ONLY_FULL_GROUP_BY

Do not allow queries for which the SELECT list refers to non-aggregated columns that are not specified in the GROUP BY clause.

The following query is invalid with this mode enabled as title is specified in the GROUP BY clause:

Code Sample:

SET sql_mode='';
SELECT store_id, last_name, first_name, count(1) from customer group by store_id;
SET sql_mode='ONLY_FULL_GROUP_BY';
SELECT store_id, last_name, first_name, count(1) from customer group by store_id;
SET sql_mode='';
Code Explanation

The first SELECT returns customers grouped by store_id, showing a customer name as well.

The second SELECT fails after setting ONLY_FULL_GROUP_BY mode.

ERROR 1055 (42000): 'sakila.customer.last_name' isn't in GROUP BY

Note: This mode also restricts references to non-aggregated columns in the HAVING clause that are not named in the GROUP BY clause.

PIPES_AS_CONCAT

Treat || as a string concatenation operator, same as CONCAT()). Normally, || is a synonym for OR.

Code Sample:

SET sql_mode='PIPES_AS_CONCAT';
SELECT last_name || ' ' || first_name from customer limit 3;
SET sql_mode='';
SELECT last_name || ' ' || first_name from customer limit 3;
Code Explanation

The first SELECT returns concatenated names from customer.

The second SELECT returns 0 (false) for all values, treating pipes as an OR operator.

REAL_AS_FLOAT

Treat REAL data type as a synonym for FLOAT. By default, MySQL treats REAL as a synonym for DOUBLE.

Strict SQL Mode

Strict SQL mode enforces correct usage of default values and data types when adding inserting and updating records in a database. It is best to have development teams to run under this stricter SQL mode to ensure that all statements are coded to correctness and will not cause unwanted data to creep into the databases, with some serious consequences at a later stage.

  • STRICT_TRANS_TABLES:

    If a value could not be inserted as given into a transactional table, abort the statement. For a non-transactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement.

  • TRADITIONAL:

    Make MySQL behave more traditional as a SQL database system. One aspect of this mode is for MySQL to be strict - throw an error instead of a warning when inserting a bad value into a column.

    In this mode, any INSERT/UPDATE statements abort as soon as the error is encountered. This may not be what you want if you are using a non-transactional storage engine, because data changes made prior to the error may not be rolled back, resulting in a partial update.

  • STRICT_ALL_TABLES:

    Enable strict mode for all storage engines. Invalid data values are rejected.

  • STRICT_TRANS_TABLES:

    Enable strict mode for transactional storage engines, and when possible for non-transactional storage engines. The strict modes are discussed in detail in the next section.

When we refer to strict mode, we mean a mode where at least one of the modes TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES is enabled. Options can be combined, so you can add additional restrictions to a mode.

Strict mode may impact performance. The intensive validation of input data that some settings requires more time than if the validation is not done. If you do validation in your application, then MySQL gives you the option of leaving strict mode disabled.

  1. To check our current SQL mode:
    SELECT @@global.sql_mode, @@sql_mode;
  2. To set the SQL mode as strict:
    SET @@sql_mode = STRICT_ALL_TABLES;

Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might fail a constraint or be out of range.

A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.)

For transactional tables, an error occurs for invalid or missing values in a statement when either of the STRICT_ALL_TABLES or STRICT_TRANS_TABLES modes are enabled. The statement is aborted and rolled back.

For non-transactional tables, the behavior is the same for either mode, if the bad value occurs in the first row to be inserted or updated. The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict option is enabled:

  • For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. The earlier rows still have been inserted or updated leading to an unintended Partial Update. To avoid this, it's best to use single-row statements because these 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 insert 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. You can find implicit defaults in MySQL documentation.

If not using strict mode, MySQL inserts adjusted values for invalid or missing values and produces warnings. In strict mode, you can produce this behavior by using INSERT IGNORE or UPDATE IGNORE.

Strict Mode in Default Values

When operating under strict mode MySQL will generate an error if no value is supplied for a field for which no explicitly defined default value is provided.

We create a table and INSERT a few rows:

Code Sample:

DROP TABLE test_strict_mode;

CREATE TEMPORARY TABLE test_strict_mode (
id bigint NOT NULL AUTO_INCREMENT,
value varchar(255) NOT NULL,
PRIMARY KEY (id)
);

SELECT @@global.sql_mode, @@sql_mode;
INSERT INTO `test_strict_mode` ( `id` , `value` ) VALUES ('1', 'Test Value 1');
INSERT INTO `test_strict_mode` ( `id` ) VALUES ('2');
SET @@sql_mode = STRICT_ALL_TABLES;
INSERT INTO `test_strict_mode` ( `id` ) VALUES ('3');

Code Explanation

The first and second INSERTs will work but the third INSERT will fail with the error: #1364 - Field ‘value’ doesn’t have a default value.

This error occurs as the table definition does not having a default for value column and none were supplied in the INSERT statement while the STRICT is mode on. This is actually an INSTALL option as well.

Special Combination Modes

Some special modes are provided as shorthand for combinations of mode values from the preceding list. For example, ANSI is Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE. Before MySQL 5.0.3, ANSI also includes ONLY_FULL_GROUP_BY.

Code Sample:

CREATE TEMPORARY TABLE IF NOT EXISTS valid_int (a TINYINT UNSIGNED NOT NULL) ENGINE=MyISAM;

SET sql_mode='MYSQL40'; -- No strict checking (short hand mode)
INSERT INTO valid_int VALUES(1); -- valid value
SELECT * FROM valid_int;
INSERT INTO valid_int VALUES(-10); -- out of range (signed)
SELECT * FROM valid_int;
INSERT INTO valid_int VALUES(NULL); -- Null not allowed
SELECT * FROM valid_int;
INSERT INTO valid_int VALUES(300); -- out of range > 255
SELECT * FROM valid_int;

DELETE FROM valid_int;

SET sql_mode='STRICT_ALL_TABLES'; -- Strict checking, aborts on error
INSERT INTO valid_int VALUES(2);
SELECT * FROM valid_int;
INSERT INTO valid_int VALUES(-10);
SELECT * FROM valid_int;
INSERT INTO valid_int VALUES(NULL);
SELECT * FROM valid_int;
INSERT INTO valid_int VALUES(300);
SELECT * FROM valid_int;

SELECT * FROM valid_int;

SET @sql_mode='';
Code Explanation

A sql_mode MYSQL40 is used to disable strict checking.

Some of the INSERTS will fail and cause warnings or errors depending on the failure. Some errors are adjusted, others are critical, and each INSERT is followed by a SELECT to show the effects.

Refer to MySQL documentation to find other shorthands.

Data Validation for Temporal Types

As seen earlier, date validation in MySQL follows somewhat interesting rules. A 0 is allowed in the month and day values to allow for storing incomplete or unknown data), and a DATE type column can store 0000-00-00. Table below lists the summary of the sql_mode values that are relevant for time validation.

Strict mode disallows invalid date values such as '2008-04-31'. It does not disallow dates with zero month or day parts such as '2008-04-00' or zero dates. To disallow these zero values as well, enable the NO_ZERO_IN_DATE and NO_ZERO_DATE SQL modes in addition to strict mode.

Setting Meaning
NO_ZERO_DATE In strict mode, don't allow '0000-00-00' as a valid date, though we can still insert zero dates with the IGNORE option. When not in strict mode, the date is accepted but a warning is generated.
NO_ZERO_IN_DATE 0 will not be allowed in month or day components of date. Though years can be legally 0 as in 2000. So this mode will not allow '2008-04-00' or '2008-00-12' as a valid date.
ALLOW_INVALID_DATES Don't perform full checking of dates so incorrect dates such as 2007-04-31 are allowed.

Though we may check that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. This is very convenient for Web applications where you obtain year, month, and day in three different fields and you want to retain user inputs without whole-date validation. This mode applies to DATE and DATETIME columns. It does not apply TIMESTAMP columns, which always require a valid date.

MySQL server requires that month and day values be legal as per date rules, and not just in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2008-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error.

Code Sample:

SET sql_mode='';
CREATE TEMPORARY TABLE invalid_date ( date1 DATETIME );
INSERT INTO invalid_date VALUES ('20070431' );
SET sql_mode='ALLOW_INVALID_DATES';
INSERT INTO invalid_date VALUES ('20070431' );
Code Explanation

Without the SQL Mode, the invalid date inserted was converted to all zeroes.

Code Sample:

USE test;
CREATE TEMPORARY TABLE test_date (
coldate DATE,
colyear YEAR,
coldatetime DATETIME,
lastupdate TIMESTAMP
);

SELECT @@global.sql_mode;
SET @@global.sql_mode = "STRICT_ALL_TABLES";

INSERT INTO test_date(coldate) VALUES(0);
SET sql_mode=NO_ZERO_DATE;
INSERT INTO test_date(coldate) VALUES(0);
SHOW WARNINGS;

INSERT INTO test_date(coldate) VALUES('2001-00-00');
SET sql_mode=NO_ZERO_IN_DATE;
INSERT INTO test_date(coldate) VALUES('2001-00-00');
SHOW WARNINGS;

INSERT INTO test_date(coldate) VALUES('2001-04-31');
SET sql_mode=ALLOW_INVALID_DATES;
INSERT INTO test_date(coldate) VALUES('2001-04-31');

SET sql_mode=@@global.sql_mode;
SET sql_mode=NO_ZERO_DATE;
INSERT INTO test_date(coldate) VALUES(0);
SET sql_mode=NO_ZERO_IN_DATE;
INSERT INTO test_date(coldate) VALUES('2001-00-00');
INSERT INTO test_date(coldate) VALUES('2001-04-31');
Code Explanation

The script shows several examples of date validation.

SHOW WARNINGS

SHOW WARNINGS shows the error, warning, and note messages that resulted from the last statement that generated messages. SHOW ERRORS, shows only the errors.

SHOW WARNINGS [LIMIT [offset,] row_count]
SHOW COUNT(*) WARNINGS
SELECT @@warning_count;

The MySQL server sends back the total number of errors, warnings, and notes resulting from the last statement. Warnings are generated for statements such as LOAD DATA INFILE and DML statements such as INSERT, UPDATE, CREATE TABLE, and ALTER TABLE.

The following DROP TABLE statement causes in a note:

Code Sample:

DROP TABLE IF EXISTS missing_table;
SELECT @@warning_count;
SHOW WARNINGS;

DROP TABLE missing_table;
SELECT @@warning_count;
SHOW ERRORS; -- Empty, already shown!

Here is another example that shows a syntax warning for CREATE TABLE and conversion warnings for INSERT:

Code Sample:

SET @old_sql_mode=@@sql_mode; -- Save original mode

DROP TEMPORARY TABLE IF EXISTS t1;

CREATE TEMPORARY TABLE t1 (a TINYINT NOT NULL, b CHAR(4)) TYPE=MyISAM;
SHOW WARNINGS;

SET sql_mode='MYSQL40'; -- No strict checking
INSERT INTO t1 VALUES(10,'mysql'),(NULL,'test'),(300,'Open Source');
SHOW WARNINGS;

SET sql_mode='STRICT_ALL_TABLES'; -- Strict checking, aborts on first error
INSERT INTO t1 VALUES(10,'mysql'),(NULL,'test'),(300,'Open Source');
SHOW WARNINGS;

SET sql_mode=@old_sql_mode; -- Revert to original mode

The maximum number of error, warning, and note messages to store is controlled by the max_error_count system variable. By default, its value is 64. To disable warnings, set max_error_count to 0. warning_count will still tell how many warnings occurred, but the messages will NOT be stored.

We can set the SQL_NOTES session variable to 0 to stop Note-level warnings from being recorded.

Here is another example for validation ENUM data type:

Code Sample:

SET @old_sql_mode=@@sql_mode; -- Save original mode

DROP TEMPORARY TABLE IF EXISTS tenum1;

CREATE TEMPORARY TABLE tenum1 (c1 enum('advanced','basic','certified'));
SHOW WARNINGS;

SET sql_mode='MYSQL40'; -- No strict checking
INSERT INTO tenum1 VALUES('adv'),('basic');
SHOW WARNINGS;

SELECT * from tenum1;

SET sql_mode='STRICT_ALL_TABLES'; -- Strict checking, aborts on first error
INSERT INTO tenum1 VALUES('amazon'),('basic');
SHOW WARNINGS;
SELECT * from tenum1;

SET sql_mode=@old_sql_mode; -- Revert to original mode

Data Validation in MySQL Conclusion

This lesson introduced you to data validation aspects of MySQL.

To continue to learn MySQL go to the top of this page and click on the next lesson in this MySQL Tutorial's Table of Contents.
 


mysql tools

HTTP-tunnel - Ability to manage MySQL database via the Internet. More...

'mysql.proc' doesn't exist fixed problem

_

Table 'mysql.proc' doesn't exist. Fix error 1146 Table doesn't exist here...