[CWB] CQPWeb Create minimal metadata table crashes

Tue Sep 20 18:31:20 CEST 2022

Sorry Andrew for keeping going on with this, but I find no solution...
I turn back to false $mysql_has_file_access
and, as it says in the manual:
default value of secure file priv has changed. In this case, instead of
changing the value of
secure file priv, to fix the problem you need to add a line secure file
priv="" to your
my.cnf file to restore what was previously the default state.
It’s unknown whether this issue affects MariaDB as well as MySQL.

first, I have tried adding
secure_file_priv = ""
to /etc/mysql/mysql.conf.d/mysqld.cnf

mysql> show variables like "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0,01 sec)

results in:
Got error 'PHP message: PHP Fatal error:  Uncaught mysqli_sql_exception:
LOAD DATA LOCAL INFILE is forbidden

second, I have tried adding:
secure_file_priv = /corpora/cqp/tmp/

mysql> show variables like "secure_file_priv";
+------------------+---------------------------+
| Variable_name    | Value                   |
+------------------+---------------------------+
| secure_file_priv | /corpora/cqp/tmp/ |
+------------------+--------------------------+
1 row in set (0,01 sec)

where
ls -la /corpora/cqp/
drwxrwxr-x 2 www-data cqpweb       217088 sep 19 17:46 tmp

and
grep '^cqpweb:.*$' /etc/group | cut -d: -f4
mysql,www-data

but the same error comes up
Got error 'PHP message: PHP Fatal error:  Uncaught mysqli_sql_exception:
LOAD DATA LOCAL INFILE is forbidden

*... Andrés Chandía*

Missatge de Hardie, Andrew <a.hardie at lancaster.ac.uk> del dia dt., 20 de
set. 2022 a les 1:26:

> This exact situation is covered in the section of the manual I mentioned
> before. Pls reread. Thank you.
>
>
>
>
>
> *From:* cwb-bounces at sslmit.unibo.it cwb-bounces at sslmit.unibo.it *On
> Behalf Of *Andrés Chandía
> *Sent:* 19 September 2022 19:54
> *To:* Open source development of the Corpus WorkBench <cwb at sslmit.unibo.it
> >
> *Subject:* Re: [CWB] CQPWeb Create minimal metadata table crashes
>
>
>
> I have done:
>
> mysql> show variables like "secure_file_priv";
> +------------------+-----------------------+
> | Variable_name    | Value                 |
> +------------------+-----------------------+
> | secure_file_priv | /var/lib/mysql-files/ |
> +------------------+-----------------------+
> 1 row in set (0,01 sec)
>
>
>
> but I look for that variable and nothing is found:
>
>
>
> grep -r "secure_file_priv" /etc/mysql/
>
>
>
>
>
>
>
> *... Andrés Chandía*
>
>
>
>
>
> Missatge de Andrés Chandía <andres.chandia at upf.edu> del dia dl., 19 de
> set. 2022 a les 20:33:
>
> By the way,  the table "text_metadata_for_bnc
> <https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcorptedig-glif.upf.edu%2Fphpmyadmin%2Findex.php%3Froute%3D%2Fsql%26db%3Dcqp%26table%3Dtext_metadata_for_bnc&data=05%7C01%7Chardiea%40live.lancs.ac.uk%7C4849e79a37294542f1e708da9a7064ea%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992105380998817%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000%7C%7C%7C&sdata=8CWzkImIMISC7cZa5Wob6n6vaaoB6RUCwQRPxJRhREo%3D&reserved=0>"
> gets created, but empty.
>
> *... Andrés Chandía*
>
>
>
>
>
> Missatge de Andrés Chandía <andres.chandia at upf.edu> del dia dl., 19 de
> set. 2022 a les 20:21:
>
> I've searched for *secure_file_priv* in
> /etc/mysql/mysql.conf.d/mysqld.cnf and there is no instance of it, just in
> case I also searched in /etc/php/8.1/apache2/php.ini and nothing too...
>
> I've changed from false to
>
> $mysql_has_file_access = true;
>
> in /lib/defaults.inc.php and /lib/defaults.php:
>
> and now the error has changed to something more weird:
>
>
>
> Got error 'PHP message: PHP Fatal error:  Uncaught mysqli_sql_exception:
> Access denied for user 'cqpusr'@'localhost' (using password: YES) in
> /corptedig-glif/cqpweb/lib/sql-lib.php:211\nStack trace:\n#0
> /corptedig-glif/cqpweb/lib/sql-lib.php(211): mysqli_query()\n#1
> /corptedig-glif/cqpweb/lib/sql-lib.php(350): do_sql_query()\n#2
> /corptedig-glif/cqpweb/lib/metadata-lib.php(1050):
> do_sql_infile_query()\n#3 /corptedig-glif/cqpweb/lib/metadata-act.php(191):
> create_text_metadata_minimalist()\n#4
> /corptedig-glif/cqpweb/exe/metadata-act.php(1): require('...')\n#5 {main}\n
>  thrown in /corptedig-glif/cqpweb/lib/sql-lib.php on line 211', referer:
> http://corptedig-glif.upf.edu/cqpweb/bnc/index.php?ui=manageMetadata
> <https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcorptedig-glif.upf.edu%2Fcqpweb%2Fbnc%2Findex.php%3Fui%3DmanageMetadata&data=05%7C01%7Chardiea%40live.lancs.ac.uk%7C4849e79a37294542f1e708da9a7064ea%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992105380998817%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000%7C%7C%7C&sdata=TArNe9KGfzdOtKNsLKR4NlxHPCqiyqhMsdnieBUfnII%3D&reserved=0>
>
>
>
> I looked for the database configuration in lib/config.inc.php
>
> and could enter mysql with the data stored there, I mean the mysqluser and
> the mysqlpassword for the cqpwebdb. Just in case I granted again the
> database to the same user, but I still get that same last error.
>
>
>
>
>
> *... Andrés Chandía*
>
>
>
>
>
> Missatge de Hardie, Andrew <a.hardie at lancaster.ac.uk> del dia dl., 19 de
> set. 2022 a les 19:51:
>
> That clarifies what the problem is. It’s not in PHP. It’s in MySQL. Even
> though local infile is switched on, something else is blocking it.
>
>
>
> I hesitate to ask, but you *did* check the setting of *secure_file_priv*,
> right? It’s listed in the manual as one of the things to check if infile
> doesn’t work.
>
>
>
> Section “Known “gotchas” in SQL DB setup”; paragraph “File privilege with
> restricted scope”
>
>
>
> (you could also try just setting $mysql_has_file_access in cqpweb, so that
> LOCAL isn’t needed)
>
>
>
> best
>
>
>
> Andrew.
>
>
>
> *From:* cwb-bounces at sslmit.unibo.it <cwb-bounces at sslmit.unibo.it> *On
> Behalf Of *Andrés Chandía
> *Sent:* 19 September 2022 18:33
> *To:* Open source development of the Corpus WorkBench <cwb at sslmit.unibo.it
> >
> *Subject:* Re: [CWB] CQPWeb Create minimal metadata table crashes
>
>
>
> mysql> show variables like "local_infile";
> +---------------+-------+
> | Variable_name | Value |
> +---------------+-------+
> | local_infile  | ON    |
> +---------------+-------+
> 1 row in set (0,02 sec)
>
> mysql> use cqp;
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
>
> Database changed
> mysql> LOAD DATA LOCAL INFILE
> '/mnt/vmdata/corptedig-glif/corpora/cqp/tmp/___install_temp_metadata_bnc'
> INTO TABLE `text_metadata_for_bnc` FIELDS ESCAPED BY '';
> ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to
> restrictions on access.
> mysql> quit
>
>
>
> -rw-r--r-- 1 www-data www-data 16196 sep 19 17:44
> /mnt/vmdata/corptedig-glif/corpora/cqp/tmp/___install_temp_metadata_bnc
>
>
>
> so I though that adding user mysql to www-data group would help, but
> nothing...
>
>
>
> and just if....
>
> chmod 777
> /mnt/vmdata/corptedig-glif/corpora/cqp/tmp/___install_temp_metadata_bnc
>
>
>
> and nothing again.... I mean, all the time: ERROR 2068 (HY000): LOAD DATA
> LOCAL INFILE file request rejected due to restrictions on access.
>
>
>
> file /etc/php/8.1/apache2/php.ini
>
> mysqli.allow_local_infile = On
>
> ;mysqli.local_infile_directory = On (commente out)
>
>
>
>
>
>
>
> *... Andrés Chandía*
>
>
>
>
>
> Missatge de Hardie, Andrew <a.hardie at lancaster.ac.uk> del dia dl., 19 de
> set. 2022 a les 19:14:
>
> There’s no alternative to you breaking out the mysql client and having a
> poke around to work out what is causing the problem.
>
>
>
> EG to find out whether the mysqld configuration is taking effect, you need
> to run
>
>
>
> > show variables like "local_infile";
>
>
>
> In order to get
>
>
>
> +---------------+-------+
>
> | Variable_name | Value |
>
> +---------------+-------+
>
> | local_infile  | ON    |
>
> +---------------+-------+
>
>
>
> If it’s ON, that suggests the issue is in PHP (or, some other security
> setting). If it’s OFF, the issue is that your mysql config file is not
> taking effect.
>
>
>
> Also, running the load data command , ie
>
>
>
> LOAD DATA LOCAL INFILE
> '/mnt/vmdata/corptedig-glif/corpora/cqp/tmp/___install_temp_metadata_bnc'
> INTO TABLE `text_metadata_for_bnc` FIELDS ESCAPED BY ''
>
>
>
> manually in the client might result in a more informative error message
> (also might not, mysql err messages are not noted for their helpfulness)
>
>
>
> best
>
>
>
> Andrew.
>
>
>
> *From:* cwb-bounces at sslmit.unibo.it <cwb-bounces at sslmit.unibo.it> *On
> Behalf Of *Andrés Chandía
> *Sent:* 19 September 2022 16:45
> *To:* Open source development of the Corpus WorkBench <cwb at sslmit.unibo.it
> >
> *Subject:* [External] Re: [CWB] CQPWeb Create minimal metadata table
> crashes
>
>
>
> In /etc/mysql/mysql.conf.d/mysqld.cnf i tried
>
> local_infile = true
>
> local_infile = 1
>
> local_infile = On
>
>
>
> but no improvement
>
> The error is not shown in the web interface when $print_debug_messages =
> true; but in the apache error log
>
>
>
> Last thing showing in the web interface with debug activated is:
>
>
>
> About to run SQL:
>
>         LOAD DATA LOCAL INFILE '/mnt/vmdata/corptedig-glif/corpora/cqp/tmp/___install_temp_metadata_bnc' INTO TABLE `text_metadata_for_bnc` FIELDS ESCAPED BY ''
>
>         /* from User: admin | Function: create_text_metadata_minimalist() | 2022-Sep-19 17:43 */
>
>
>
>
>
>
>
> *... Andrés Chandía*
>
>
>
>
>
> Missatge de Hardie, Andrew <a.hardie at lancaster.ac.uk> del dia dl., 19 de
> set. 2022 a les 17:22:
>
> The other possibility is “local_infile = true” as the culprit. I am not
> sure the mysql ini file parser recognises “true”. You might try “On” or “1”
> instead.
>
> If that fails, you’ll have to move to digging with mysql client to see
> what the server settings actually are in practice and where they are being
> set.
>
> best
>
>
>
> Andrew.
>
>
>
> *From:* cwb-bounces at sslmit.unibo.it <cwb-bounces at sslmit.unibo.it> *On
> Behalf Of *Andrés Chandía
> *Sent:* 19 September 2022 16:07
> *To:* Open source development of the Corpus WorkBench <cwb at sslmit.unibo.it
> >
> *Subject:* Re: [CWB] CQPWeb Create minimal metadata table crashes
>
>
>
> Thanks Andrew for your answer, but I'm sorry to say, that when I detected
> the problem i had
>
> ;mysqli.local_infile_directory =
>
>
>
> then, as suggested by the error log, I activate it, but nothing changed, I
> have commented out already...
>
>
>
> thanks again...
>
>
>
>
>
>
>
> *... Andrés Chandía*
>
>
>
>
>
> Missatge de Hardie, Andrew <a.hardie at lancaster.ac.uk> del dia dl., 19 de
> set. 2022 a les 17:03:
>
> My guess: You’ve caused it by setting local-infile-directory.
>
>
>
> That’s a string setting, which if set limits LOCAL INFILE to a particular
> directory. You’ve set it to Boolean true, which translates to string “1”.
> Obviously this will block infile from working anywhere.
>
>
>
>
> https://www.php.net/manual/en/mysqli.configuration.php#ini.mysqli.local-infile-directory
> <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.php.net%2Fmanual%2Fen%2Fmysqli.configuration.php%23ini.mysqli.local-infile-directory&data=05%7C01%7Chardiea%40live.lancs.ac.uk%7C4849e79a37294542f1e708da9a7064ea%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992105380998817%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000%7C%7C%7C&sdata=Hiw6n0efCTBotue4y8gFsZuCLa1dPcyHBlx9ayHL1w8%3D&reserved=0>
>
>
>
> I suggest leaving it unset.
>
>
>
> best
>
>
>
> Andrew.
>
>
>
> *From:* cwb-bounces at sslmit.unibo.it <cwb-bounces at sslmit.unibo.it> *On
> Behalf Of *Andrés Chandía
> *Sent:* 18 September 2022 16:03
> *To:* Open source development of the Corpus WorkBench <cwb at sslmit.unibo.it
> >
> *Subject:* [CWB] CQPWeb Create minimal metadata table crashes
>
>
>
> CQPWeb: v3.2.43
>
> Php 8.1
>
> mysql  Ver 8.0.30-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))
>
>
>
> PHP message: PHP Fatal error:  Uncaught mysqli_sql_exception: LOAD DATA
> LOCAL INFILE is forbidden, check related settings like
> mysqli.allow_local_infile|mysqli.local_infile_directory or
> PDO::MYSQL_ATTR_LOCAL_INFILE|PDO::MYSQL_ATTR_LOCAL_INFILE_DIRECTORY in
> /corptedig-glif/cqpweb/lib/sql-lib.php:211\nStack trace:\n#0
> /corptedig-glif/cqpweb/lib/sql-lib.php(211): mysqli_query()\n#1
> /corptedig-glif/cqpweb/lib/sql-lib.php(350): do_sql_query()\n#2
> /corptedig-glif/cqpweb/lib/metadata-lib.php(1050):
> do_sql_infile_query()\n#3 /corptedig-glif/cqpweb/lib/metadata-act.php(191):
> create_text_metadata_minimalist()\n#4
> /corptedig-glif/cqpweb/exe/metadata-act.php(1): require('...')\n#5 {main}\n
>  thrown in /corptedig-glif/cqpweb/lib/sql-lib.php on line 211', referer:
> http://corptedig-glif.upf.edu/cqpweb/bnc/index.php?ui=manageMetadata
> <https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcorptedig-glif.upf.edu%2Fcqpweb%2Fbnc%2Findex.php%3Fui%3DmanageMetadata&data=05%7C01%7Chardiea%40live.lancs.ac.uk%7C4849e79a37294542f1e708da9a7064ea%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992105380998817%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000%7C%7C%7C&sdata=TArNe9KGfzdOtKNsLKR4NlxHPCqiyqhMsdnieBUfnII%3D&reserved=0>
>
>
>
> /etc/php/8.1/apache2/php.ini
>
> mysqli.allow_local_infile = On
>
> mysqli.local_infile_directory = On
>
>
>
> /etc/mysql/mysql.conf.d/mysqld.cnf
>
> local_infile = true
>
>
>
> Thanks
>
> *Andrés Chandía*
> Unitat de Traducció i Ciències del Llenguatge
> Roc Boronat 138, C. P.: 08018, Barcelona
> Tel.: 935 055 722 - mail:andres.chandia at upf.edu <andres.chandia at upf.edu>
>
> [image: chandia.net]
> <https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.chandia.net%2F&data=05%7C01%7Chardiea%40live.lancs.ac.uk%7C4849e79a37294542f1e708da9a7064ea%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992105380998817%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000%7C%7C%7C&sdata=UJ07ZYiUITwDkaeZKrIsj0B4tkZpFcin1IO4Ms5K%2B74%3D&reserved=0>
> <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2Fchandianet&data=05%7C01%7Chardiea%40live.lancs.ac.uk%7C4849e79a37294542f1e708da9a7064ea%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992105381155038%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000%7C%7C%7C&sdata=S4iCyaAtztNenNh12iHKjsnd2pOmmZF0OItlTP5zHTY%3D&reserved=0>
>
> mail:andres at chandia.net <andres at chandia.net>
> ------------------------------
>
> _______________________________________________
> CWB mailing list
> CWB at sslmit.unibo.it
> http://liste.sslmit.unibo.it/mailman/listinfo/cwb
> <https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fliste.sslmit.unibo.it%2Fmailman%2Flistinfo%2Fcwb&data=05%7C01%7Chardiea%40live.lancs.ac.uk%7C4849e79a37294542f1e708da9a7064ea%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992105381155038%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000%7C%7C%7C&sdata=CtelZB0Wb%2BiZ3i2wjuptCwRoXPiGShR5w%2FYKxMh47nQ%3D&reserved=0>
>
> _______________________________________________
> CWB mailing list
> CWB at sslmit.unibo.it
> http://liste.sslmit.unibo.it/mailman/listinfo/cwb
> <https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fliste.sslmit.unibo.it%2Fmailman%2Flistinfo%2Fcwb&data=05%7C01%7Chardiea%40live.lancs.ac.uk%7C4849e79a37294542f1e708da9a7064ea%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992105381155038%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000%7C%7C%7C&sdata=CtelZB0Wb%2BiZ3i2wjuptCwRoXPiGShR5w%2FYKxMh47nQ%3D&reserved=0>
>
> _______________________________________________
> CWB mailing list
> CWB at sslmit.unibo.it
> http://liste.sslmit.unibo.it/mailman/listinfo/cwb
> <https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fliste.sslmit.unibo.it%2Fmailman%2Flistinfo%2Fcwb&data=05%7C01%7Chardiea%40live.lancs.ac.uk%7C4849e79a37294542f1e708da9a7064ea%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992105381155038%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000%7C%7C%7C&sdata=CtelZB0Wb%2BiZ3i2wjuptCwRoXPiGShR5w%2FYKxMh47nQ%3D&reserved=0>
>
> _______________________________________________
> CWB mailing list
> CWB at sslmit.unibo.it
> http://liste.sslmit.unibo.it/mailman/listinfo/cwb
> <https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fliste.sslmit.unibo.it%2Fmailman%2Flistinfo%2Fcwb&data=05%7C01%7Chardiea%40live.lancs.ac.uk%7C4849e79a37294542f1e708da9a7064ea%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992105381155038%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000%7C%7C%7C&sdata=CtelZB0Wb%2BiZ3i2wjuptCwRoXPiGShR5w%2FYKxMh47nQ%3D&reserved=0>
>
> _______________________________________________
> CWB mailing list
> CWB at sslmit.unibo.it
> http://liste.sslmit.unibo.it/mailman/listinfo/cwb
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://liste.sslmit.unibo.it/pipermail/cwb/attachments/20220920/888fffca/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 322426 bytes
Desc: not available
URL: <http://liste.sslmit.unibo.it/pipermail/cwb/attachments/20220920/888fffca/attachment-0001.png>