[CWB] CQPWeb Create minimal metadata table crashes

Mon Sep 19 20:54:08 CEST 2022

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
> <http://corptedig-glif.upf.edu/phpmyadmin/index.php?route=/sql&db=cqp&table=text_metadata_for_bnc>"
> 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
>>
>> 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%7C16bf14713f86434f897408da9a651849%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992056230955405%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=f1gas%2FDaOlbHAFlkSpkVtZOGmCwbsDugcPgksfv7grw%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%7C16bf14713f86434f897408da9a651849%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992056230955405%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=z4cjvLYN1nV%2BDTVVYGGFODk2YnYrPiDcDj%2BSISFW9eY%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%7C16bf14713f86434f897408da9a651849%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992056230955405%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=EDbQfkwDgwIcsQoIWC5r%2BlJeCeRXhOdb3MVSD6qC%2FhM%3D&reserved=0>
>>> <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2Fchandianet&data=05%7C01%7Chardiea%40live.lancs.ac.uk%7C16bf14713f86434f897408da9a651849%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992056230955405%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=d6LlZcQ%2BsP%2ByitqThYGrpFw7Fu4LL71RpWL5ioa3KIE%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%7C16bf14713f86434f897408da9a651849%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992056230955405%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=nC1692p4LUhjaXvF2MibniCgANDlj3JSFen0BkXcx%2B8%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%7C16bf14713f86434f897408da9a651849%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992056230955405%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=nC1692p4LUhjaXvF2MibniCgANDlj3JSFen0BkXcx%2B8%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%7C16bf14713f86434f897408da9a651849%7C9c9bcd11977a4e9ca9a0bc734090164a%7C0%7C0%7C637992056230955405%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=nC1692p4LUhjaXvF2MibniCgANDlj3JSFen0BkXcx%2B8%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/20220919/b3578c1e/attachment-0001.html>