The size of a table does not match the records in MySQL, AWS RDS

Asked Jul 26 '19 at 06:18

Active Jul 31 '19 at 07:49

Viewed 99 times

I have a problem with a MySQL table, the table has only 385 records that if I export them via Workbench the file is 894 KB in size. The problem is that the Data length is 10 GB when I try to inspect the table.

Note: In the table when making query only show 385 records.

The only solution to restore the size of the table that I have used is to drop the table and import it again.

I hope and you can help me to verify if it is a MySQL error or it is an attack or I don't know why the behavior is due.

The MySQL server is in an AWS RDS.

I have read the next article: https://docs.aws.amazon.com/es_es/AmazonRDS/latest/UserGuide/MySQL.KnownIssuesAndLimitations.html

CREATE TABLE `logs` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
    `file` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'yes',
    PRIMARY KEY (`id`),
    UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=130204 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

edited Jul 31 '19 at 07:49

asked Jul 26 '19 at 06:18

Gustavo Martínez

Image NOT AVAILABLE – James Jul 26 '19 at 06:44
Try to add the image which SO provides – James Jul 26 '19 at 06:59
Ready the image is in the post, waited for your comments. – Gustavo Martínez Jul 26 '19 at 07:02
1

As per your Image it's 13 MB and records are much more than what you have mentioned in your question. – James Jul 26 '19 at 07:09
No, in the table when making query only show 385 records. – Gustavo Martínez Jul 26 '19 at 07:18
**Data length is 10 GB when I try to inspect the table.**? – James Jul 26 '19 at 07:53
2

try`Analyze table table_name` – James Jul 26 '19 at 07:56
1

See [MySQL InnoDB not releasing disk space after deleting data rows from table](https://stackoverflow.com/q/1270944) – Solarflare Jul 26 '19 at 07:57
@James you're thinking of `OPTIMIZE TABLE`. `ANALYZE` just rebuilds the index statistics. – Michael - sqlbot Jul 26 '19 at 22:55
Yeah exactly .... Optimize ...my bad – James Jul 27 '19 at 04:06
I used ANALYZE TABLE and work with normality. But my question is how often do you recommend that you do this process with a cron. – Gustavo Martínez Jul 28 '19 at 03:39

The size of a table does not match the records in MySQL, AWS RDS

0 Answers0