Resolved I can't enable load data infile permanently (losing changes after reboot)

[javibus]

Server operating system version

Ubuntu 18.04.5 LTS

Plesk version and microupdate number

Plesk Obsidian

Hello all!

I'm going crazy. I need to enable this feature to get Matomo software working smoothly with a high traffic volume. I have configured the db user to get FILE permissions and it works only when I enter into mysql from SSH and run this instruction:

SET GLOBAL local_infile = 1;

But, after a reboot, I lose the changes and the "local_infile" global var goes to "OFF" again.

I have added this lines to the my.cnf too:

[client]
local-infile = 1

[client-mariadb]
local-infile = 1

[mysql]
local-infile = 1

[mariadb]
local-infile = 1

[mariadb-10.1]
local-infile = 1

Nothing works. It only works when I enter into mysql from SSH and run "SET GLOBAL local_infile = 1;", but after a reboot ("service mysql restart"), the global local-inline resets to "OFF".

Please, can anyone figure out how to get this config permanently on a Plesk-Ubuntu server?

Thanks!

 

[Maarten]

Have you tried to use SET PERSIST instead of SET GLOBAL ?

SET PERSIST local_infile = 1;
SET @@PERSIST.local_infile = 1;

Have a look at:

MySQL :: MySQL 8.0 Reference Manual :: 5.1.9 Using System Variables

dev.mysql.com

MySQL :: MySQL 8.0 Reference Manual :: 13.7.6.1 SET Syntax for Variable Assignment

dev.mysql.com

 

[javibus]

Hello,

Thanks for your response. But it throws an error.

MariaDB [(none)]> SET PERSIST local_infile = 1;
ERROR 1193 (HY000): Unknown system variable 'PERSIST'
MariaDB [(none)]> SET @@PERSIST.local_infile = 1;
ERROR 1272 (HY000): Variable 'local_infile' is not a variable component (can't be used as XXXX.variable_name)

I don't know why is Plesk omitting the my.cnf configuration. This file includes this other files:

/etc/mysql/mariadb.conf.d/50-client.cnf
/etc/mysql/mariadb.conf.d/50-mysql-clients.cnf
/etc/mysql/mariadb.conf.d/50-mysqld_safe.cnf
/etc/mysql/mariadb.conf.d/50-server.cnf

I have changed other parameters in the 50-server.cnf and it persists the reboot, but for some reason ignores the "local-infile = 1" directive.

 

[javibus]

Solved!!

I have created a sql file with a sentence that enables the local infile feature:

echo "SET GLOBAL local_infile = 'ON';" > /var/lib/mysql/init_file.sql
chown mysql:mysql /var/lib/mysql/init_file.sql

And I have configured the mysql service to load this file when it starts by editing the my.cnf file and adding the following under "[mysqld]":

[mysqld]
init-file=/var/lib/mysql/init_file.sql

In my case, the config file is /etc/mysql/mariadb.conf.d/50-server.cnf

Hope this helps someone in the future.