[CWB] CQPWeb Create minimal metadata table crashes
Stephanie Evert
stefanML at collocations.de
Mon Oct 10 09:03:42 CEST 2022
My own installation of CQPweb v3.2 has had this patch for many years:
Index: lib/sql-lib.php
===================================================================
--- lib/sql-lib.php (revision 1651)
+++ lib/sql-lib.php (working copy)
@@ -343,7 +343,8 @@
$filepath = escape_sql(realpath($filepath)); /* because "infile" requries an absolute path when non-LOCAL */
$sql = "{$Config->mysql_LOAD_DATA_INFILE_command} '$filepath' INTO TABLE `$table`";
-
+
+ $sql .= " CHARACTER SET utf8mb4"; /* PATCH to handle characters outside BMP */
if ($no_escapes)
$sql .= ' FIELDS ESCAPED BY \'\'';
Otherwise it'll die on the first Twitter corpus you try to install. :-)
Best,
Stephanie
> On 9 Oct 2022, at 21:04, Andrés Chandía <andres.chandia at upf.edu> wrote:
>
> I could finally fix it,
> apt install --reinstall php-gd php-intl php-json php-mbstring php-mysqli php-phar php-cgi php-cli php-bz2 php-zip php-pclzip
>
> After that, when checking Internal PHP settings relevant to CQPweb in the Admin Control Panel, I realised that php.ini file is no longer apache2/php.ini but /etc/php/8.1/fpm/php.ini. Then, I applied settings for upload_max_filesize post_max_size memory_limit max_execution_time and mysqli.allow_local_infile. Then restarted apache and all wen almost as expected, except that I get this error now:
>
> [proxy_fcgi:error] [pid 3342] [client x.xx.xx.x:21205] AH01071: Got error 'PHP message: PHP Fatal error: Uncaught mysqli_sql_exception: Invalid utf8mb4 character string: 'A history of the world in 10' in /cqpweb/lib/sql-lib.php:211\nStack trace:\n#0 /cqpweb/lib/sql-lib.php(211): mysqli_query()\n#1 /cqpweb/lib/sql-lib.php(350): do_sql_query()\n#2 /cqpweb/lib/metadata-lib.php(984): do_sql_infile_query()\n#3 /cqpweb/lib/metadata-lib.php(814): create_text_metadata_from_file()\n#4 /cqpweb/lib/metadata-act.php(179): create_text_metadata_from_xml()\n#5 /cqpweb/exe/metadata-act.php(1): require('...')\n#6 {main}\n thrown in /cqpweb/lib/sql-lib.php on line 211', referer: http://corptedig-glif.upf.edu/cqpweb/bnc/index.php?ui=manageMetadata&createMetadataFromXml=1
>
> Thanks!
>
> ... Andrés Chandía
>
>
> Missatge de Hardie, Andrew <a.hardie at lancaster.ac.uk> del dia dt., 20 de set. 2022 a les 22:57:
> Some bug reports on the web suggest that adding
>
>
>
> loose-local-infile=1
>
>
>
> in the [client] section of your mysql configuration may help?
>
>
>
> Other possibilities:
>
>
>
> · Check that cqpusr has the “FILE” privilege on the sql server.
>
>
>
> · Check that the input file is readable by system user www-data.
>
>
>
> · Check that no other system (like apparmor) is causing problems by limiting what folders the webserver / mysql client can access.
>
>
>
>
>
>
>
>
>
> From: cwb-bounces at sslmit.unibo.it <cwb-bounces at sslmit.unibo.it> On Behalf Of Andrés Chandía
> Sent: 20 September 2022 17:31
> To: Open source development of the Corpus WorkBench <cwb at sslmit.unibo.it>
> Subject: Re: [CWB] CQPWeb Create minimal metadata table crashes
>
>
>
> 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.
>
>
>
> <image002.jpg>
>
>
>
> 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" 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
>
>
>
> 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
>
>
>
> /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
>
>
>
> mail:andres at chandia.net
>
>
>
> _______________________________________________
> CWB mailing list
> CWB at sslmit.unibo.it
> http://liste.sslmit.unibo.it/mailman/listinfo/cwb
>
> _______________________________________________
> CWB mailing list
> CWB at sslmit.unibo.it
> http://liste.sslmit.unibo.it/mailman/listinfo/cwb
>
> _______________________________________________
> CWB mailing list
> CWB at sslmit.unibo.it
> http://liste.sslmit.unibo.it/mailman/listinfo/cwb
>
> _______________________________________________
> CWB mailing list
> CWB at sslmit.unibo.it
> http://liste.sslmit.unibo.it/mailman/listinfo/cwb
>
> _______________________________________________
> CWB mailing list
> CWB at sslmit.unibo.it
> http://liste.sslmit.unibo.it/mailman/listinfo/cwb
>
> _______________________________________________
> CWB mailing list
> CWB at sslmit.unibo.it
> http://liste.sslmit.unibo.it/mailman/listinfo/cwb
> _______________________________________________
> CWB mailing list
> CWB at sslmit.unibo.it
> http://liste.sslmit.unibo.it/mailman/listinfo/cwb
More information about the CWB
mailing list