0

Background: I am running a cqpweb, a corpus query interface written in PHP and connected to a mysql database, on an Apache2 webserver under Ubuntu 18.04 LTS. The Ubuntu server was set up from scratch and there are currently no manual configurations to mysql or php.

cqpweb ran happily unless it started throwing out error messages like this one:

 A MySQL query did not run successfully!

 Original query: LOAD DATA LOCAL INFILE '/data2/cqpweb/cach/tab_dist_fycel9oplk' INTO TABLE `db_dist_g2f3ndxf40` FIELDS ESCAPED BY '' /* from User: knappen |
 Function: create_db() | 2019-Apr-25 11:23:01 */

 Error # 0:

This happened overnight, no updates or other changes to the system occurred. Therefore I suspect that some size limit of the database was reached and that I will have to increase it.

Question: What are some size limits for mysql databases and how can I increase them?

Additional information:

php --version
PHP 7.2.17-0ubuntu0.18.04.1 (cli) (built: Apr 18 2019 14:12:38) ( NTS )

mysql --version
mysql  Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper

Size of mysql:

root@corpora:/var/lib/mysql# du -h ./
23M        ./mysql
676K        ./sys
16G        ./cqpweb
1.1M        ./performance_schema
16G        ./

Error log: There are no related messages in /var/log/mysql/error.log.

Running the database query manually from the mysql prompt (under the same database user as cqpweb) succeeds (to my big surprise).

EDIT: Raw disk space is not an issue—there is enough space left on the device.

EDIT2: I tried a brute force approach to the basic problem, did a backup of the data in the database using mysqldump, destroying the database, reinstalling mysql-server, restoring the database from the dump; reducing the size from 16G to 11G, but the original problem persists.

Sir Cornflakes
  • 574

1 Answers1

1

Therefore I suspect that some size limit of the database was reached and that I will have to increase it.

I doubt it. If you reach a limit those get reported (probably /var/log/mysql/error.log, and/or /var/log/mysql.log).

What are some size limits for mysql databases and how can I increase them

These limitation are NOT editable:

  • InnoDB permits up to 4 billion tables.

  • MySQL has hard limit of 4096 columns per table

  • The maximum tablespace size is four billion pages (64TB), which is also the maximum size for a table.

  • MyISAM permits data and index files to grow up to 256TB by default, but this limit can be changed up to the maximum permissible size of 65,536TB (2567 − 1 bytes).

But you are no where near those.

Other limitation can be enforced by editing /etc/my.cnf but if there are you added them.

A MySQL query did not run successfully!

This notice says nothing uselful.

Your problem is with the function create_db and MySQL is throwing an error. In theory it could indeed be a limitation of MySQL but the odds on the query being wrong are a lot higher. Maybe there is a (double)quote or some other character in the data that is not processed correctly. Or there is a JOIN not correct and due to the amount of data MySQL now believes it needs to use another INDEX (a SELECT with an ORDER BY that has a lot of the same values or has NULL values in a field tends to switch INDEX a lot in my experience).

You probably need to find out what the SQL command actually is and examine it or the results of the execution.

Without the actual content of the function and an example of your database there is not a lot to go on.

Rinzwind
  • 299,756