load data LOCAL bug

Hi,

here's a bug I found that is either in the software or in the
documentation (or both):

It's about the LOCAL option of LOAD DATA. Apparently, this part of the
manual is wrong: http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html

It says you can enable the LOCAL option (though this is not secure, I
know) with --local-infile=1 or even --local-infile. This doesn't work,
though. I can start the server with this option and/or I can start the
mysql client with this option, it doesn't accept LOCAL.

How-to-repeat:

C:\mysql\bin>mysql --local-infile=1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20 to server version: 4.0.10-gamma-max-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW VARIABLES LIKE '%local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set (0.05 sec)

mysql> SELECT VERSION();
+-------------------------+
| VERSION() |
+-------------------------+
| 4.0.10-gamma-max-nt-log |
+-------------------------+
1 row in set (0.00 sec)

mysql> LOAD DATA LOCAL INFILE 'c:/test.txt' INTO TABLE test.t;
ERROR 1148: The used command is not allowed with this MySQL version

OS is Win2K with SP2, but this has been reported on the list with a
number of other OS's like Linux. Personally, I encountered this
problem beginning with MySQL version 4.0.3 (as far as I remember).

A lot of people on the main list have been asking exactly the same
question like I did, without getting an answer like "you can't use
LOCAL, it's disabled, and you can only enable it by compiling MySQL
yourself" (this, indeed, seems to work).

Today, I got two personal mails from list members asking if I can tell
them what to do, or whether this is a bug (in the software, or in the
documentation). I don't know, so this is why I am sending this to you.

Regards,
--
Stefan Hinz
iConnect GmbH
Heesestr. 6, 12169 Berlin (Germany)
Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3v


------------------------------------------------------------ ---------
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-thread13829@lists.mysql.com
To unsubscribe, e-mail

Re: load data LOCAL bug

Stefan Hinz writes:
> Hi,
>
> here's a bug I found that is either in the software or in the
> documentation (or both):
>
> It's about the LOCAL option of LOAD DATA. Apparently, this part of the
> manual is wrong: http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html
>
> It says you can enable the LOCAL option (though this is not secure, I
> know) with --local-infile=1 or even --local-infile. This doesn't work,
> though. I can start the server with this option and/or I can start the
> mysql client with this option, it doesn't accept LOCAL.
>
> How-to-repeat:
>
> C:\mysql\bin>mysql --local-infile=1
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 20 to server version: 4.0.10-gamma-max-nt-log
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> SHOW VARIABLES LIKE '%local%';
> +---------------+-------+
> | Variable_name | Value |
> +---------------+-------+
> | local_infile | ON |
> +---------------+-------+
> 1 row in set (0.05 sec)
>
> mysql> SELECT VERSION();
> +-------------------------+
> | VERSION() |
> +-------------------------+
> | 4.0.10-gamma-max-nt-log |
> +-------------------------+
> 1 row in set (0.00 sec)
>

> Regards,
> --
> Stefan Hinz
> iConnect GmbH
> Heesestr. 6, 12169 Berlin (Germany)
> Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3v
>

Hi!

It is quite possible that our Windows binary have not been built with
local infile enabled.

We shall check it out with our Windows team.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
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-thread13831@lists.mysql.com
To unsubscribe, e-mail

Re: load data LOCAL bug

Stefan,

Stefan Hinz wrote:
> Hi,
>
> here's a bug I found that is either in the software or in the
> documentation (or both):


> How-to-repeat:
>
> C:\mysql\bin>mysql --local-infile=1


Under Linux I wasn't able to reproduce it.
I tried with both mysql compiled from source (bk tree) and from binary install.

However it looks that mysql does not respect --local-infile at all :(
Looks that on my machine somehow it is always turned ON and --local-infile changes
nothing.

I am able to alter this setting by using SET GLOBAL:

# mysql-4 --local-infile=false
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.9-gamma-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like "%local%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set (0.02 sec)

mysql> set global local_infile=0;
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like "%local%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+

But it should be processed as a command line or my.cnf setting as well.
I will do some more tests to see how exactly it behaves and will enter it into
Bugs database.



--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com





------------------------------------------------------------ ---------
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-thread13832@lists.mysql.com
To unsubscribe, e-mail

Re[2]: load data LOCAL bug

Stefan,

> > here's a bug I found that is either in the software or in the
> > documentation (or both):
> > C:\mysql\bin>mysql --local-infile=1

> Under Linux I wasn't able to reproduce it.
> I tried with both mysql compiled from source (bk tree) and from binary install.

As Sinisa said, it's probably not enabled in the Windows binaries.

mysql> SET GLOBAL local_infile=0;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW VARIABLES LIKE 'local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
1 row in set (0.00 sec)

Works for me as well. As one can see, --local-infile doesn't actually
change anything:

C:\mysql\bin>mysql --local-infile=0
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22 to server version: 4.0.10-gamma-max-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW VARIABLES LIKE 'local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL local_infile=0;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW VARIABLES LIKE 'local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL local_infile=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set (0.00 sec)

> However it looks that mysql does not respect --local-infile at all :(
> Looks that on my machine somehow it is always turned ON and --local-infile changes
> nothing.

Thanks for telling me about this SET GLOBAL local_infile thing,
although this doesn't change anything with my 4.0.10 on Win2K either.

Regards,
--
Stefan Hinz
iConnect GmbH
Heesestr. 6, 12169 Berlin (Germany)
Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3


------------------------------------------------------------ ---------
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-thread13833@lists.mysql.com
To unsubscribe, e-mail

Re: load data LOCAL bug

At 15:52 +0200 2/25/03, Alexander Keremidarski wrote:
>Stefan,
>
>Stefan Hinz wrote:
>> Hi,
>>
>> here's a bug I found that is either in the software or in the
>> documentation (or both):
>
>
>> How-to-repeat:
>>
>> C:\mysql\bin>mysql --local-infile=1
>
>
>Under Linux I wasn't able to reproduce it.

But that has nothing to do with it. The bug report is Windows-specific.
I get the same (broken) behavior on Windows, though my Linux builds
work as I want. (But on Linux, that happens because I use
--enable-local-infile).

>I tried with both mysql compiled from source (bk tree) and from
>binary install.
>
>However it looks that mysql does not respect --local-infile at all :(
>Looks that on my machine somehow it is always turned ON and
>--local-infile changes
>nothing.
>
>I am able to alter this setting by using SET GLOBAL:
>
># mysql-4 --local-infile=false
>Welcome to the MySQL monitor. Commands end with ; or \g.
>Your MySQL connection id is 1 to server version: 4.0.9-gamma-debug-log
>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
>mysql> show variables like "%local%";
>+---------------+-------+
>| Variable_name | Value |
>+---------------+-------+
>| local_infile | ON |
>+---------------+-------+
>1 row in set (0.02 sec)
>
>mysql> set global local_infile=0;
>Query OK, 0 rows affected (0.02 sec)
>
>mysql> show variables like "%local%";
>+---------------+-------+
>| Variable_name | Value |
>+---------------+-------+
>| local_infile | OFF |
>+---------------+-------+
>
>But it should be processed as a command line or my.cnf setting as well.
>I will do some more tests to see how exactly it behaves and will
>enter it into Bugs database.
>
>
>
>--
> MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
> For technical support contracts, visit https://order.mysql.com/?ref=msal
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
> <___/ www.mysql.com
>
>
>
>
>
>----------------------------------------------------------- ----------
>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-thread13832@lists.mysql.com
>To unsubscribe, e-mail


------------------------------------------------------------ ---------
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-thread13834@lists.mysql.com
To unsubscribe, e-mail

Re: Re[2]: load data bug

Stefan Hinz writes:
> Stefan,
>
> > > here's a bug I found that is either in the software or in the
> > > documentation (or both):
> > > C:\mysql\bin>mysql --local-infile=1
>
> > Under Linux I wasn't able to reproduce it.
> > I tried with both mysql compiled from source (bk tree) and from binary install.
>
>
> Thanks for telling me about this SET GLOBAL local_infile thing,
> although this doesn't change anything with my 4.0.10 on Win2K either.
>
> Regards,
> --
> Stefan Hinz
> iConnect GmbH
> Heesestr. 6, 12169 Berlin (Germany)
> Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3
>

This is just to inform you that I was not able to reproduce your
TRUNCATE bug on Linux.

This is a test case I used:

drop table if exists personnel;
CREATE TABLE `personnel` (
`pid` smallint(5) unsigned NOT NULL auto_increment,
`unit` tinyint(3) unsigned default NULL,
`grade` tinyint(3) unsigned default NULL,
PRIMARY KEY (`pid`)
) TYPE=InnoDB;
LOAD DATA INFILE '/tmp/xx'INTO TABLE personnel FIELDS TERMINATED BY ';' (unit, grade);
TRUNCATE TABLE personnel;
LOAD DATA INFILE '/tmp/xx'INTO TABLE personnel FIELDS TERMINATED BY ';' (unit, grade);
SELECT * FROM personnel;
ALTER TABLE personnel TYPE=MyISAM;
SELECT * FROM personnel;
drop table if exists personnel;
CREATE TABLE `personnel` (
`pid` smallint(5) unsigned NOT NULL auto_increment,
`unit` tinyint(3) unsigned default NULL,
`grade` tinyint(3) unsigned default NULL,
PRIMARY KEY (`pid`)
) TYPE=MyISAM;
LOAD DATA INFILE '/tmp/xx'INTO TABLE personnel FIELDS TERMINATED BY ';' (unit, grade);
TRUNCATE TABLE personnel;
LOAD DATA INFILE '/tmp/xx'INTO TABLE personnel FIELDS TERMINATED BY ';' (unit, grade);
SELECT * FROM personnel;
ALTER TABLE personnel TYPE=InnoDB;
SELECT * FROM personnel;
drop table if exists personnel;


It has 4 SELECT's and the output is:

pid unit grade
1 23 42
2 23 53
3 23 123
4 23 142
5 23 198
6 23 248
7 23 255
8 42 110
9 42 255
pid unit grade
1 23 42
2 23 53
3 23 123
4 23 142
5 23 198
6 23 248
7 23 255
8 42 110
9 42 255
pid unit grade
1 23 42
2 23 53
3 23 123
4 23 142
5 23 198
6 23 248
7 23 255
8 42 110
9 42 255
pid unit grade
1 23 42
2 23 53
3 23 123
4 23 142
5 23 198
6 23 248
7 23 255
8 42 110
9 42 255


So, it could be another Windows build issue. That is why I asked
our Windows team to check it out.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
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-thread13836@lists.mysql.com
To unsubscribe, e-mail

Re[4]: load data bug

Sinisa,

> This is just to inform you that I was not able to reproduce your
> TRUNCATE bug on Linux.

> This is a test case I used:

Looks pretty much the same, indeed.

> So, it could be another Windows build issue. That is why I asked
> our Windows team to check it out.

Okay. Thanks for informing me.

Regards,
--
Stefan Hinz
iConnect GmbH
Heesestr. 6, 12169 Berlin (Germany)
Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3


------------------------------------------------------------ ---------
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-thread13838@lists.mysql.com
To unsubscribe, e-mail