Maria DB high volume of insert errors

amigan_99
amigan_99 used Ask the Experts™
on
When application observium is trying to write to mariadb there is a recurring error:

[2019/01/25 13:24:19 -0800] poller.php(142737): Failed dbQuery (#1114 - The table 'ports_cbqos' is full),
Query: INSERT INTO `ports_cbqos` (`device_id`,`port_id`,`policy_index`,`object_index`,`direction`,`PrePolicyPkt`,
`PrePolicyByte`,`PostPolicyByte`,`DropPkt`,`DropByte`,`NoBufDropPkt`)  
VALUES ('132','11977','301990276','301990277','output','0','0','0','0','0','')

This adds up to some 10GB of logging per day. This is running on centos. Any thoughts on how I could
fix this error? There appears to be plenty of disk space.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
The message The table 'ports_cbqos' is full suggests a disk is full or disk quota for user or database subsystem has been hit.

Take a look at /var/lib/mysql + look at ports_cbqos related file sizes, if that's the only table throwing the error.
Fractional CTO
Distinguished Expert 2018
Commented:
Just realized you're running CentOS... so you're a thrillseeker...

This may be related to an old version of CentOS running... with poorly configured ext4 filesystem.

Or might also be (more likely) your my.cnf file fails to enable innodb_file_per_table.

If you don't have innodb_file_per_table enabled, then best check the MariaDB docs for version your running about how to fix this.

I'd likely do this...

1) service mysql stop

2) mkdir /var/lib/mysql.bak

3) rsync -av /var/lib/mysql/. /var/lib/mysql.bak/.

4) Add innodb_file_per_table to your /etc/my.cnf file or equivalent.

5) service mysql start

Then track your /var/log/syslog or equivalent or /var/log/mysql/mariadb-error.log might be the file.

Track the daemon startup + see if the MariaDB version you're running is smart enough to auto split all files out of a single file to a file per table. I'm unsure if this actually works. If everything circles the drain, then you'll have to stop mysql + sync your /var/lib/mysql.bak directory over /var/lib/mysql again to revert.

Then do a mysqldump of all tables.

Destroy all your tables. Restart mysql with innodb_file_per_table enabled + reload all your databases.
amigan_99Network Engineer

Author

Commented:
Thank you again David.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial