Getting error while importing database of size 1.3 GB in CLI

[iauth]

SYSTEM INFORMATION
OS type and version	Debian 12
Webmin version	2.111
Virtualmin version	7.20.2

I am getting following error while importing database in CLI
ERROR 1114 (HY000) at line 4186622: The table ‘securitygroups_records’ is full

[Joe]

I would assume the virtual server you’re importing into (the domain owner user) has hit their disk quota.

[Stegan]

If so a more friendly system generated error message might have helped.
(a database size of 1.3Gb cannot be that unusually large)

and… would just temporarily increasing the domain owner’s quota be a remedy?

[Ilia]

@Stegan

and… would just temporarily increasing the domain owner’s quota be a remedy?

Probably not, as the database size could be 100GB.

[Stegan]

@Ilia

Probably not, as the database size could be 100GB.

I guess if the importing server has less capacity than the originator then nothing would fit. If it was that big it would be time to think about a dedicated server and probably punning the db.

[Ilia]

@Stegan

I guess if the importing server has less capacity than the originator then nothing would fit.

That’s right!

@Joe

I would assume the virtual server you’re importing into (the domain owner user) has hit their disk quota.

It could also be some other limits … right?

@iauth Try increasing the size of the InnoDB system tablespace data files, by editing the /etc/mysql/mariadb.conf.d/50-server.cnf configuration, and adding the following line under the [mysqld] section, and then restarting the MariaDB service manually:

innodb_data_file_path = ibdata1:10M:autoextend:max:10G

If that alone doesn’t work, also try adding innodb_file_per_table = 1 to enable the file-per-table mode, and/or increase the limits for the tmp_table_size = 256M and max_heap_table_size = 256M options, under the same [mysqld] section.

Then again restart the MariaDB service and check if it works.

[Joe]

@Stegan

If so a more friendly system generated error message might have helped.

That’s a database error, we have no control over that, it’s not our software.

[Stegan]

@iauth

ERROR 1114 (HY000)

Sure I know that (and I am assuming MariaDB/MySQL) so the error should have been elevated in the program that is running MariaDB/MySQL (ie PHP/ NodeJS/whatever) it clearly was for the OP to report it.

A useful link describing how to fix InnoDB table full

though I would be cautious on making such changes and have been covered already by @Ilia

[jimr1]

@Stegan

so the error should have been elevated in the program

The OP was running this via cli rather than exec from some other script/app so nothing to elevate to

[Stegan]

Well the Error was declared in the OP so whatever was managing it (cli or app) must have elevated it.

It also makes me question if this might also be the root cause of those OOM errors that occasionally pop up one here. It is quite an extreme circumstance of having such a large database

[Joe]

@Stegan

Well the Error was declared in the OP so whatever was managing it (cli or app) must have elevated it.

The point is we have no control over it. Mariadb and their CLI is their business, not ours. We have no control over their errors.

And, a 1.3GB database is not extreme at all. That’s a very normal size for a database, kinda small, even, relative to what actually large databases look like. There are Mariadb databases in production out there that are terabytes in size (running on monster hardware, of course).