search

matomo-org / matomo

Empowering People Ethically with the leading open source alternative to Google Analytics that gives you full control over your data. Matomo lets you easily collect data from websites & apps and visualise this data and extract insights. Privacy is built-in. Liberating Web Analytics. Star us on Github? +1. And we love Pull Requests!
https://matomo.org/
GNU General Public License v3.0
19.63k stars 2.62k forks source link

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

Closed chriscroome closed 2 years ago

chriscroome commented 2 years ago

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

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?

bx80 commented 2 years ago

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 commented 2 years ago

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?

bx80 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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:

MatomoForumNotifications commented 1 year ago

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 commented 1 year ago

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.

masterwishx commented 4 months ago

is it still issue ?

masterwishx commented 4 months ago

have this warning on Matomo original docker + LSIO MariaDB in Oracle free vps , but not in home with bitnami Matomo + LSIO MariaDB . Both DB have same setting .

masterwishx commented 4 months ago

@chriscroome did you fixed it ?

chriscroome commented 4 months ago

No it is not fixed one server still has this issue.

php console diagnostics:run
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. over 100,000 pages per month).
    - OK CREATE TEMPORARY TABLES
    - OK Changing transaction isolation level
1 warnings detected

The web System Check is fine:

Screenshot 2024-04-27 at 17-45-15 System Check - Administration - Matomo

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                                 |
+--------------------------+---------------------------------------+--------------------------------------------------------------------+

As far as I'm aware the console check for the FILE privilege doesn't always work, why it works on some servers but not others is a mystery to me :woman_shrugging:

masterwishx commented 4 months ago

No it is not fixed one server still has this issue.

php console diagnostics:run
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. over 100,000 pages per month).
  - OK CREATE TEMPORARY TABLES
  - OK Changing transaction isolation level
1 warnings detected

The web System Check is fine:

Screenshot 2024-04-27 at 17-45-15 System Check - Administration - Matomo

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                                 |
+--------------------------+---------------------------------------+--------------------------------------------------------------------+

As far as I'm aware the console check for the FILE privilege doesn't always work, why it works on some servers but not others is a mystery to me 🤷‍♀️

Do you using matomo official servers? Strange that on bitnami it's OK and in official I have this warning. Did you added File server privilege for the matomo user?

chriscroome commented 4 months ago

Do you using matomo official servers?

No, this is self hosted.

Did you added File server privilege for the matomo user?

The Matomo MariaDB user does have the File privilege as illustrated by the output of mysql -e "SHOW PRIVILEGES" above -- this command was run using the same user account that Matomo uses.

masterwishx commented 4 months ago

Do you using matomo official servers?

No, this is self hosted.

Did you added File server privilege for the matomo user?

The Matomo MariaDB user does have the File privilege as illustrated by the output of mysql -e "SHOW PRIVILEGES" above -- this command was run using the same user account that Matomo uses.

Sorry I meaned matomo official docker images, Yes I see all privileges, just using Adminer for mariadb

chriscroome commented 4 months ago

I'm not running Matomo in a Docker container.

masterwishx commented 4 months ago

I'm not running Matomo in a Docker container.

OK, got it but strange that issue exist on one of some systems...

chriscroome commented 4 months ago

Indeed...

masterwishx commented 4 months ago

Also forgot to mention, in home in Unraid bitnami Matomo docker image + lsio mariadb I have no File server privelege for user but have no this issue..