Re: LOAD DATA, /N, and auto_increment

Hi.

On Sun 2002-08-18 at 21:58:04 +0200, benjamin-mysql@pflugmann.de wrote:
> Hello.
>
> On Sun 2002-08-18 at 18:53:46 +0200, arjones@simultan.dyndns.org wrote:
> > To Whom it May Concern,
> > I sincerely doubt it's relevant, but i'm running MySQL 3.23.52 on
> > Linux 2.4.18 (RedHat build 5). Client and server are on the same machine
> > and communicate via a Unix domain socket.
> > Perhaps it is a conscious decision, but it would seem an odd one:
> > When using LOAD DATA [LOCAL] INFILE, if the table has a column that is
> > BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY and the infile uses \N
> ^^^^^^^^
>
> \N means NULL with LOAD DATA. So you try to insert NULL values into a
> NOT NULL column. I find warnings about this quite reasonable.

Argh! Ignore that. I overlooked the AUTO_INCREMENT portion.

Considering this, I think you have a point.

> > for that column, a warning is generated for every line inserted. Replacing
> > \N with numbers in the infile gets rid of all warnings on import. Seems
> > to be a matter of adding one little check to Field_longlong::store() in
> > field.cc, but i could be wrong. I strongly suspect that it is
> > independant of integer size (e.g TINYINT, INT, BIGINT probably all
> > behave the same way).
> > Please send all replies to my personal address as well, as i am not
> > subscribed to the list.

I just tested this with 3.23.49 and can reprocude this for BIGINT, INT
and TINYINT (did not test others). Interestingly, the values are
correctly inserted (i.e. incremented).

CC'ed to bugs list, because I can reproduce it.

How-to-repeat:

shell> cat >/tmp/in < 1
2
\N
\N
10
\N
EOF
shell> chmod a+r /tmp/in

mysql> CREATE TABLE ntest2 ( a INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY );
mysql> LOAD DATA INFILE '/tmp/in' INTO TABLE ntest2;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 3
mysql> SELECT * FROM ntest2;
+----+
| a |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 10 |
| 11 |
+----+
6 rows in set (0.00 sec)

As said above, the shown output differs from the expected in the
"Warnings: 3" message. There should be 0 warnings.

Regards,

Benjamin.

--
benjamin-mysql@pflugmann.de

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12362@lists.mysql.com
To unsubscribe, e-mail