Load data local infile Not working now

[bamawebx]

Something das changed. After a few years with no errors, this load is failing with the following msg:

Please help!


SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MariaDB version

1. in Descargas.php line 236
2. at PDO->exec('LOAD DATA LOCAL INFILE

 

[munimadx]

It looks like LOAD DATA LOCAL INFILE is no longer supported.
Is it possible to have it enabled again?
Is there an alternative to load a csv file programmatically? (e.g. without LOCAL)

Thank you

 

[Anna]

I'm not familiar with using that way of importing data, I did some digging around and this may be of help for you:
https://mariadb.com/kb/en/importing-data-into-mariadb/

Apparently the local option is deemed unsafe "In releases after MariaDB 5.5, LOAD DATA INFILE is unsafe for statement-based replication."

This may be the reason it is configured without that option turned on, for that I'll have to check with server techs though, but if you can make it work without the local that will be the safer option in any way.

 

[munimadx]

I have tried without LOCAL keyword, putting the file to load in tmp directory as the article says, but does not work:

#1045 - Access denied for user: 'da_sso_SOuN83xN9'@'localhost' (Usando clave: SI)

Is there any folder we can use to load these files? Any chance of keeping LOCAL until a new way of importing files is found?
Our site is supported on this feature to work

Thank you!

I'm not familiar with using that way of importing data, I did some digging around and this may be of help for you:
https://mariadb.com/kb/en/importing-data-into-mariadb/

Apparently the local option is deemed unsafe "In releases after MariaDB 5.5, LOAD DATA INFILE is unsafe for statement-based replication."

This may be the reason it is configured without that option turned on, for that I'll have to check with server techs though, but if you can make it work without the local that will be the safer option in any way.

 

[Anna]

Any database user created within cpanel would typically have your hosting username as prefix, like munimadx_databaseuser

This may be why the user in your example gets access denied, it is not the correct username, from what I can see no such user exist for your database.

 

[munimadx]

You are right, that user appeared when using phpadmin.
But unfortunatly, using my user in the app does not work either.

SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user 'munimadx_<myuser>'@'localhost' (using password: YES)

I have tried:
LOAD DATA INFILE \'/tmp/myfile.csv\' and also
LOAD DATA INFILE \'/home/munimadx/domains/munimad.x10host.com/public_html/files/myfile.csv'

Is there a chance to have FILE permission? Shoud that fix it?

GRANT FILE ON *.* TO 'munimadx_<myuser>'@'localhost';

 

[spacresx]

Although im not familiar with the error myself,
but have you tried looking up the error that you get?
i did a quick search and there are a few topics on the error.
like here or here were just a couple i seen.
maybe one of those would be helpful.

 

[munimadx]

Yes, although the solution in both links relies in

1. SET GLOBAL local_infile = 1;

a global parameter, and need super user privileges.
Yes, I have already spent many hours searching for a way. Thank you!

SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Although im not familiar with the error myself,
but have you tried looking up the error that you get?
i did a quick search and there are a few topics on the error.
like here or here were just a couple i seen.
maybe one of those would be helpful.

 

[spacresx]

I found this while searching granting access to the db user.
would something like that be useful just changing the database user?

 

[munimadx]

That would be nice, but I cannot grant myself the required SUPER privilege

 

[spacresx]

That would be nice, but I cannot grant myself the required SUPER privilege

true, i just thought that might help to get past the original issue.

SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user 'munimadx_<myuser>'@'localhost' (using password: YES)

 

[munimadx]

The problem is more or less like this:

- Using Load Data Local Infile -> works for 3 years. Stops working (disabled for security reasons)

Alternative? -> Removing "Local"

- Does not work because:

> variable local_infile is set to false. Global variable, can be only modified by SUPER user
OR
> FILE permission should be granted

I think that is the scenario

 

[munimadx]

I need help, I have run out of ideas...

 

[spacresx]

just putting this out there,
since i dont know exactly what this is for, i can only suggest.
what about trying to reinstall whatever it is that uses the database.

 

[munimadx]

Thank you for your intentions

It's simple, I just want to load csv files in mariadb.

 

[spacresx]

Thank you for your intentions

It's simple, I just want to load csv files in mariadb.

this sounds like an option for that using mysqlimport

 

[munimadx]

Hmm I don't think we can call that from PHP here, can we?

 

[spacresx]

i dont know for sure, just throwing options out there.