[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 made changes - 2016-11-12 16:02

Labels

need_feedback

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