Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Closed
chriscroome opened this issue May 25, 2022 · 7 comments
Closed

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

chriscroome opened this issue May 25, 2022 · 7 comments
Labels
Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. wontfix If you can reproduce this issue, please reopen the issue or create a new one describing it.

Comments

@chriscroome
Copy link

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:

Database abilities✓ UTF8mb4 charset
✓ LOAD DATA INFILE
✓ CREATE TEMPORARY TABLES
✓ Changing transaction isolation level

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 @@read_only=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?
@chriscroome chriscroome added the Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. label May 25, 2022
@bx80
Copy link
Contributor

bx80 commented May 29, 2022

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?

@chriscroome
Copy link
Author

chriscroome commented Jun 2, 2022 •
edited

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?

@chriscroome chriscroome reopened this Jun 2, 2022
@bx80
Copy link
Contributor

bx80 commented Jun 6, 2022

Hi @chriscroome,

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

  1. The MySQL setting returned by SELECT @@secure_file_priv, 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

@chriscroome
Copy link
Author

Thanks @bx80 the server with this issue doesn't have @@secure_file_priv set, but neither do the servers that doesn't have this issue, I get the same result on all of them:

mysql -e "SELECT @@secure_file_priv"
+--------------------+
| @@secure_file_priv |
+--------------------+
| 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.

@bx80
Copy link
Contributor

bx80 commented Jun 13, 2022

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 👍

@bx80 bx80 closed this as completed Jun 13, 2022
@MatomoForumNotifications
Copy link

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

@cronventis
Copy link

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.

@justinvelluppillai justinvelluppillai added the wontfix If you can reproduce this issue, please reopen the issue or create a new one describing it. label Sep 29, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. wontfix If you can reproduce this issue, please reopen the issue or create a new one describing it.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@justinvelluppillai @chriscroome @cronventis @bx80 @MatomoForumNotifications