[MDEV-11235] LOAD DATA does not honor strict modes - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Not a Bug
Affects Version/s: 10.2.2
Fix Version/s: N/A
Component/s: OTHER
Labels:
- need_feedback

Description

Zero date could be inserted using LOAD DATA even under 'NO_ZERO_DATE' mode.

MariaDB [test]> set sql_mode='';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> create table t1(c1 date default '0000-00-00');

Query OK, 0 rows affected (0.08 sec)

MariaDB [test]> \! echo "" > /tmp/data

MariaDB [test]> insert into t1 values ();

Query OK, 1 row affected (0.00 sec)

MariaDB [test]> load data local infile '/tmp/data' into table t1;

Query OK, 1 row affected, 1 warning (0.01 sec)

Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

MariaDB [test]> select * from t1;

+------------+

| c1         |

+------------+

| 0000-00-00 |

| 0000-00-00 |

+------------+

2 rows in set (0.01 sec)

MariaDB [test]> set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into t1 values ();

ERROR 1978 (22007): Incorrect default value '0000-00-00' for column 'c1'

MariaDB [test]> load data local infile '/tmp/data' into table t1;

Query OK, 1 row affected, 1 warning (0.04 sec)

Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

MariaDB [test]> show warnings;

+---------+------+-----------------------------------------+

| Level   | Code | Message                                 |

+---------+------+-----------------------------------------+

| Warning | 1265 | Data truncated for column 'c1' at row 1 |

+---------+------+-----------------------------------------+

1 row in set (0.00 sec)

MariaDB [test]> select * from t1;

+------------+

| c1         |

+------------+

| 0000-00-00 |

| 0000-00-00 |

| 0000-00-00 |

+------------+

3 rows in set (0.00 sec)

MariaDB [test]> show create table t1;

+-------+---------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                |

+-------+---------------------------------------------------------------------------------------------+

| t1    | CREATE TABLE `t1` (

  `c1` date DEFAULT '0000-00-00'

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+---------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

Attachments

Issue Links

relates to

MDEV-7635 update defaults and simplify mysqld config parameters

Closed

Activity

Ascending order - Click to sort in descending order

Nirbhay Choubey (Inactive) created issue - 2016-11-04 15:24

Nirbhay Choubey (Inactive) made changes - 2016-11-04 15:24

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-7635~~ [ ~~MDEV-7635~~ ]

Nirbhay Choubey (Inactive) made changes - 2016-11-04 15:25

Description

Zero date could be inserted using LOAD DATA even under 'NO_ZERO_DATE' mode.

{code}
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.3-MariaDB-debug Source distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]>
MariaDB [test]>
MariaDB [test]>
MariaDB [test]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> create table t1(c1 date default '0000-00-00');
Query OK, 0 rows affected (0.08 sec)

MariaDB [test]> \! echo "" > /tmp/data
MariaDB [test]> insert into t1 values ();
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> load data local infile '/tmp/data' into table t1;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1

MariaDB [test]> select * from t1;
+------------+
| c1 |
+------------+
| 0000-00-00 |
| 0000-00-00 |
+------------+
2 rows in set (0.01 sec)

MariaDB [test]> set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into t1 values ();
ERROR 1978 (22007): Incorrect default value '0000-00-00' for column 'c1'
MariaDB [test]> load data local infile '/tmp/data' into table t1;
Query OK, 1 row affected, 1 warning (0.04 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1

MariaDB [test]> show warnings;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'c1' at row 1 |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)
MariaDB [test]> select * from t1;
+------------+
| c1 |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
+------------+
3 rows in set (0.00 sec)

MariaDB [test]> show create table t1;
+-------+---------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c1` date DEFAULT '0000-00-00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
{code}

Zero date could be inserted using LOAD DATA even under 'NO_ZERO_DATE' mode.

{code}
MariaDB [test]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> create table t1(c1 date default '0000-00-00');
Query OK, 0 rows affected (0.08 sec)

MariaDB [test]> \! echo "" > /tmp/data

MariaDB [test]> insert into t1 values ();
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> load data local infile '/tmp/data' into table t1;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1

MariaDB [test]> select * from t1;
+------------+
| c1 |
+------------+
| 0000-00-00 |
| 0000-00-00 |
+------------+
2 rows in set (0.01 sec)

MariaDB [test]> set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into t1 values ();
ERROR 1978 (22007): Incorrect default value '0000-00-00' for column 'c1'

MariaDB [test]> load data local infile '/tmp/data' into table t1;
Query OK, 1 row affected, 1 warning (0.04 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1

MariaDB [test]> show warnings;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'c1' at row 1 |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select * from t1;
+------------+
| c1 |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
+------------+
3 rows in set (0.00 sec)

MariaDB [test]> show create table t1;
+-------+---------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c1` date DEFAULT '0000-00-00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
{code}

Elena Stepanova added a comment - 2016-11-04 16:02

This section talks about duplicate keys, but I think the same logic applies:
http://dev.mysql.com/doc/refman/5.7/en/load-data.html

If you specify IGNORE, rows that duplicate an existing row on a unique key value are discarded. For more information, see Comparison of the IGNORE Keyword and Strict SQL Mode.

If you do not specify either option, the behavior depends on whether the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation.

Elena Stepanova added a comment - 2016-11-04 16:02 This section talks about duplicate keys, but I think the same logic applies: http://dev.mysql.com/doc/refman/5.7/en/load-data.html If you specify IGNORE, rows that duplicate an existing row on a unique key value are discarded. For more information, see Comparison of the IGNORE Keyword and Strict SQL Mode. If you do not specify either option, the behavior depends on whether the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation.

Elena Stepanova added a comment - 2016-11-12 16:02

nirbhay_c,
I am inclined to close it as not a bug, unless you disagree with the above.

Elena Stepanova added a comment - 2016-11-12 16:02 nirbhay_c , I am inclined to close it as not a bug, unless you disagree with the above.

Elena Stepanova made changes - 2016-11-12 16:02

Labels

need_feedback

Nirbhay Choubey (Inactive) added a comment - 2016-11-13 23:09

elenst I agree with the documented behavior. Thanks for pointing.

Nirbhay Choubey (Inactive) added a comment - 2016-11-13 23:09 elenst I agree with the documented behavior. Thanks for pointing.

Nirbhay Choubey (Inactive) made changes - 2016-11-13 23:09

Fix Version/s		N/A [ 14700 ]
Resolution		Not a Bug [ 6 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:43

Workflow

MariaDB v3 [ 78209 ]

MariaDB v4 [ 151188 ]

People

Assignee:: Unassigned

Reporter:: Nirbhay Choubey (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2016-11-04 15:24

Updated:: 2016-11-13 23:09

Resolved:: 2016-11-13 23:09

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration