php console diagnostics:run sometimes displays incorrect LOAD DATA INFILE warning #19267

I have one Matomo instance for which the command line diagnostics displays a LOAD DATA INFILE warning:

php console diagnostics:run
INFO [2022-05-25 07:30:02] 1738607  Unable to test if mod_pagespeed is enabled: the request to http://unknown/console?module=Installation&action=getEmptyPageForSystemCheck failed
Database abilities:
        - OK UTF8mb4 charset
        - WARNING LOAD DATA INFILE
Hint: Using LOAD DATA INFILE by updating your PHP and MySQL software and ensuring your database user has the FILE privilege speeds up Matomo's archiving process a lot.
You should fix this problem if your Matomo server tracks high-traffic websites (e.g. > 100,000 pages per month).
        - OK CREATE TEMPORARY TABLES
        - OK Changing transaction isolation level
1 warnings detected

However the System Check web page displays:

And checking directly with MariaDB:

mysql -e "SHOW PRIVILEGES"                       
+--------------------------+---------------------------------------+--------------------------------------------------------------------+
| Privilege                | Context                               | Comment                                                            |
+--------------------------+---------------------------------------+--------------------------------------------------------------------+
| Alter                    | Tables                                | To alter the table                                                 |
| Alter routine            | Functions,Procedures                  | To alter or drop stored functions/procedures                       |
| Create                   | Databases,Tables,Indexes              | To create new databases and tables                                 |
| Create routine           | Databases                             | To use CREATE FUNCTION/PROCEDURE                                   |
| Create temporary tables  | Databases                             | To use CREATE TEMPORARY TABLE                                      |
| Create view              | Tables                                | To create new views                                                |
| Create user              | Server Admin                          | To create new users                                                |
| Delete                   | Tables                                | To delete existing rows                                            |
| Delete history           | Tables                                | To delete versioning table historical rows                         |
| Drop                     | Databases,Tables                      | To drop databases, tables, and views                               |
| Event                    | Server Admin                          | To create, alter, drop and execute events                          |
| Execute                  | Functions,Procedures                  | To execute stored routines                                         |
| File                     | File access on server                 | To read and write files on the server                              |
| Grant option             | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess                |
| Index                    | Tables                                | To create or drop indexes                                          |
| Insert                   | Tables                                | To insert data into tables                                         |
| Lock tables              | Databases                             | To use LOCK TABLES (together with SELECT privilege)                |
| Process                  | Server Admin                          | To view the plain text of currently executing queries              |
| Proxy                    | Server Admin                          | To make proxy user possible                                        |
| References               | Databases,Tables                      | To have references on tables                                       |
| Reload                   | Server Admin                          | To reload or refresh tables, logs and privileges                   |
| Binlog admin             | Server                                | To purge binary logs                                               |
| Binlog monitor           | Server                                | To use SHOW BINLOG STATUS and SHOW BINARY LOG                      |
| Binlog replay            | Server                                | To use BINLOG (generated by mariadb-binlog)                        |
| Replication master admin | Server                                | To monitor connected slaves                                        |
| Replication slave admin  | Server                                | To start/stop slave and apply binlog events                        |
| Slave monitor            | Server                                | To use SHOW SLAVE STATUS and SHOW RELAYLOG EVENTS                  |
| Replication slave        | Server Admin                          | To read binary log events from the master                          |
| Select                   | Tables                                | To retrieve rows from table                                        |
| Show databases           | Server Admin                          | To see all databases with SHOW DATABASES                           |
| Show view                | Tables                                | To see views with SHOW CREATE VIEW                                 |
| Shutdown                 | Server Admin                          | To shut down the server                                            |
| Super                    | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.                |
| Trigger                  | Tables                                | To use triggers                                                    |
| Create tablespace        | Server Admin                          | To create/alter/drop tablespaces                                   |
| Update                   | Tables                                | To update existing rows                                            |
| Set user                 | Server                                | To create views and stored routines with a different definer       |
| Federated admin          | Server                                | To execute the CREATE SERVER, ALTER SERVER, DROP SERVER statements |
| Connection admin         | Server                                | To bypass connection limits and kill other users' connections      |
| Read_only admin          | Server                                | To perform write operations even if @<a class='mention' href='https://github.com/read_only'>@read_only</a>=ON                 |
| Usage                    | Server Admin                          | No privileges - allow connect only                                 |
+--------------------------+---------------------------------------+--------------------------------------------------------------------+

This is with

• Matomo Version: 4.10.1
• PHP Version: PHP-FPM 7.4.28
• Server Operating System: Debian Buster

However on around a dozen other sites all running the same versions of Matomo, MariaDB and PHP this issue doesn't occur, which is odd?

Hi @chriscroome, thanks for reporting this.

When testing database capabilities the diagnostics check will create a temporary .csv file on disk and then attempt to load it into MySQL/MariaDB using LOAD DATA INFILE.

One possibility here could be that the command line diagnostics check is being run as a user without access to write the temporary file which then causes the check to fail.

Could you try running the command line diagnostics check as the web server user (eg. su apache) and see if that resolves the issue?

Thanks @bx80 I am running the command as the same user that PHP-FPM runs as.

I thought the issue could be that the PHP sys_temp_dir variable was set when the system check was accessed using a web browser but not on the command line, so I added the following to ~/.bash_aliases:

alias php="php --php-ini ~/.php.ini

And the following to ~/.php.ini:

sys_temp_dir = "/home/matomo/tmp"

Where /home/matomo/tmp is the $TMPDIR for this user and it is owned by this user but this doesn't appear to have made a difference, I still get:

php console --no-ansi -n diagnostics:run
INFO [2022-06-02 15:03:39] 487207  Unable to test if mod_pagespeed is enabled: the request to http://unknown/console?module=Installation&action=getEmptyPageForSystemCheck failed
Database abilities:
        - OK UTF8mb4 charset
        - WARNING LOAD DATA INFILE
Hint: Using LOAD DATA INFILE by updating your PHP and MySQL software and ensuring your database user has the FILE privilege speeds up Matomo's archiving process a lot.
You should fix this problem if your Matomo server tracks high-traffic websites (e.g. > 100,000 pages per month).
        - OK CREATE TEMPORARY TABLES
        - OK Changing transaction isolation level
1 warnings detected

Where is the temporary .csv file written?

Hi @chriscroome,

It looks like Matomo will choose the load infile tmp path in the following sequence:

1) The MySQL setting returned by SELECT @<a class='mention' href='https://github.com/secure_file_priv'>@secure_file_priv</a>, this option must be set and the specified location must exist and be writable for it to be used.
2) Thetmp_path setting from config/config.ini.php if it is set
3) The subdirectory tmp under the matomo root installation directory (eg. /home/matomo/tmp)

If your MySQL server does not have the secure_file_priv setting configured and you haven't set a tmp_path option in the config file then the temporary .csv file should be written to /home/matomo/tmp

Thanks @bx80 the server with this issue doesn't have @<a class='mention' href='https://github.com/secure_file_priv'>@secure_file_priv</a> set, but neither do the servers that doesn't have this issue, I get the same result on all of them:

mysql -e "SELECT @<a class='mention' href='https://github.com/secure_file_priv'>@secure_file_priv</a>"
+--------------------+
| @<a class='mention' href='https://github.com/secure_file_priv'>@secure_file_priv</a> |
+--------------------+
| NULL               |
+--------------------+

There isn't a problem writing to the temp directories as far as I can see.

I think I'll just have to accept that this one server can't run php console --no-ansi -n diagnostics:run without an inexplicable error -- I'll simply have to work around this.

Sorry we couldn't get to the bottom of this one @chriscroome, I'll close the issue but do feel free to reopen if anything else comes to light :+1:

This issue has been mentioned on Matomo forums. There might be relevant details there:

https://forum.matomo.org/t/matomo-missing-privileges-in-mariadb/47379/2

This issue has been mentioned on Matomo forums. There might be relevant details there:

https://forum.matomo.org/t/matomo-missing-privileges-in-mariadb/47379/2

This was me.. And its not only commandline, its also on matomo itself (web)
I checked the tmp folders and its all looking good so far, there is also no error in matomo check, all folders seem to be setup correctly.