[Maria-discuss] LOAD DATA LOCAL INFILE
HI All,
Even after enabling --local-infile=1 at mysql user login, I couldn't load the file locally.
mysql -u root --local-infile=1
LOAD DATA LOCAL INFILE
ERROR 1148 (42000): The used command is not allowed with this MariaDB version
show variables like '%local%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | local_infile | OFF | +---------------+-------+
Even though i enable --local-infile=1, the local-infile still shows OFF.
Pleaselet know how can I fix this.
According to https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_... this variable has GLOBAL scope (and I don-t think mariaDB has changed anything here. The SESSION-scope value is always inherited from the GLOBAL setting.
'local-infile' is also considered a security setting and as such it makes sense IMO that a user cannot change for his SESSION as she likes.
Also I get this (on Windows)
C:\Program Files\MariaDB 10.2\bin>mysql -u root -p local-infile=1 Enter password: ******** ERROR 1049 (42000): Unknown database 'local-infile=1'
Is it possible to specify a server variable on the commandline at all? I don't think so. At least I never heard about it.
-- Peter -- Webyog
On Wed, Feb 15, 2017 at 2:43 PM, Karthick Subramanian < ksubramanian@paycommerce.com> wrote:
HI All,
Even after enabling --local-infile=1 at mysql user login, I couldn't load the file locally.
mysql -u root --local-infile=1
LOAD DATA LOCAL INFILE
ERROR 1148 (42000): The used command is not allowed with this MariaDB version
show variables like '%local%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | local_infile | OFF | +---------------+-------+
Even though i enable --local-infile=1, the local-infile still shows OFF.
Pleaselet know how can I fix this.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Typo:
mysql -u root --local-infile=1
I missed the double dashes (--). When you try with --, it will accept, but as you said it doesn't have any effect on session level.
I remember I did local infile successfully some 3 months back, but now its not working. Not sure what version I used 3 months back. But currently its 10.1.16.
On Wed, Feb 15, 2017 at 7:26 PM, Peter Laursen peter_laursen@webyog.com wrote:
According to https://dev.mysql.com/doc/refman/5.7/en/server-system- variables.html#sysvar_local_infile this variable has GLOBAL scope (and I don-t think mariaDB has changed anything here. The SESSION-scope value is always inherited from the GLOBAL setting.
'local-infile' is also considered a security setting and as such it makes sense IMO that a user cannot change for his SESSION as she likes.
Also I get this (on Windows)
C:\Program Files\MariaDB 10.2\bin>mysql -u root -p local-infile=1 Enter password: ******** ERROR 1049 (42000): Unknown database 'local-infile=1'
Is it possible to specify a server variable on the commandline at all? I don't think so. At least I never heard about it.
-- Peter -- Webyog
On Wed, Feb 15, 2017 at 2:43 PM, Karthick Subramanian < ksubramanian@paycommerce.com> wrote:
HI All,
Even after enabling --local-infile=1 at mysql user login, I couldn't load the file locally.
mysql -u root --local-infile=1
LOAD DATA LOCAL INFILE
ERROR 1148 (42000): The used command is not allowed with this MariaDB version
show variables like '%local%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | local_infile | OFF | +---------------+-------+
Even though i enable --local-infile=1, the local-infile still shows OFF.
Pleaselet know how can I fix this.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
local-infile is both a client and a server flag - both must be enabled or you'll get an error. mysql --local-infile=1 only enables the client support for this feature. Your MariaDB server likely has this disabled - perhaps as a security concern.
I would inspect your my.cnf and update the value if you need this feature. If you have sufficient privileges, you can enable this dynamically by running:
mysql -e "SET GLOBAL local_infile = on"
~Andrew
On Wed, Feb 15, 2017 at 8:05 AM, Karthick Subramanian ksubramanian@paycommerce.com wrote:
Typo:
mysql -u root --local-infile=1
I missed the double dashes (--). When you try with --, it will accept, but as you said it doesn't have any effect on session level.
I remember I did local infile successfully some 3 months back, but now its not working. Not sure what version I used 3 months back. But currently its 10.1.16.
On Wed, Feb 15, 2017 at 7:26 PM, Peter Laursen peter_laursen@webyog.com wrote:
According to https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_... this variable has GLOBAL scope (and I don-t think mariaDB has changed anything here. The SESSION-scope value is always inherited from the GLOBAL setting.
'local-infile' is also considered a security setting and as such it makes sense IMO that a user cannot change for his SESSION as she likes.
Also I get this (on Windows)
C:\Program Files\MariaDB 10.2\bin>mysql -u root -p local-infile=1 Enter password: ******** ERROR 1049 (42000): Unknown database 'local-infile=1'
Is it possible to specify a server variable on the commandline at all? I don't think so. At least I never heard about it.
-- Peter -- Webyog
On Wed, Feb 15, 2017 at 2:43 PM, Karthick Subramanian ksubramanian@paycommerce.com wrote:
HI All,
Even after enabling --local-infile=1 at mysql user login, I couldn't load the file locally.
mysql -u root --local-infile=1
LOAD DATA LOCAL INFILE
ERROR 1148 (42000): The used command is not allowed with this MariaDB version
show variables like '%local%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | local_infile | OFF | +---------------+-------+
Even though i enable --local-infile=1, the local-infile still shows OFF.
Pleaselet know how can I fix this.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Thank you Andrew.
I think I shouldn't think too much.
I don't know why I didn't think of setting this GLOBAL and reset back once my load done.
This is actually works: SET GLOBAL local_infile = on
I dd lot of unwanted mess-up in the system by modifying the SELinux context for the file to mysqldb_t etc. I think I have wasted lot of time instead of asking forum upfront. Anyway, lot of learnings.
Thank you Andrew and Peter for your insights.
On Wed, Feb 15, 2017 at 7:57 PM, Andrew Garner muzazzi@gmail.com wrote:
local-infile is both a client and a server flag - both must be enabled or you'll get an error. mysql --local-infile=1 only enables the client support for this feature. Your MariaDB server likely has this disabled - perhaps as a security concern.
I would inspect your my.cnf and update the value if you need this feature. If you have sufficient privileges, you can enable this dynamically by running:
mysql -e "SET GLOBAL local_infile = on"
~Andrew
On Wed, Feb 15, 2017 at 8:05 AM, Karthick Subramanian ksubramanian@paycommerce.com wrote:
Typo:
mysql -u root --local-infile=1
I missed the double dashes (--). When you try with --, it will accept,
but
as you said it doesn't have any effect on session level.
I remember I did local infile successfully some 3 months back, but now
its
not working. Not sure what version I used 3 months back. But currently
its
10.1.16.
On Wed, Feb 15, 2017 at 7:26 PM, Peter Laursen <peter_laursen@webyog.com
wrote:
According to https://dev.mysql.com/doc/refman/5.7/en/server-system-
variables.html#sysvar_local_infile
this variable has GLOBAL scope (and I don-t think mariaDB has changed anything here. The SESSION-scope value is always inherited from the
GLOBAL
setting.
'local-infile' is also considered a security setting and as such it
makes
sense IMO that a user cannot change for his SESSION as she likes.
Also I get this (on Windows)
C:\Program Files\MariaDB 10.2\bin>mysql -u root -p local-infile=1 Enter password: ******** ERROR 1049 (42000): Unknown database 'local-infile=1'
Is it possible to specify a server variable on the commandline at all? I don't think so. At least I never heard about it.
-- Peter -- Webyog
On Wed, Feb 15, 2017 at 2:43 PM, Karthick Subramanian ksubramanian@paycommerce.com wrote:
HI All,
Even after enabling --local-infile=1 at mysql user login, I couldn't
load
the file locally.
mysql -u root --local-infile=1
LOAD DATA LOCAL INFILE
ERROR 1148 (42000): The used command is not allowed with this MariaDB version
show variables like '%local%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | local_infile | OFF | +---------------+-------+
Even though i enable --local-infile=1, the local-infile still shows
OFF.
Pleaselet know how can I fix this.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
Andrew Garner -
Karthick Subramanian -
Peter Laursen