I have one master RDS instance of 50 GB storage and created a read replica of the master DB with the same configuration.
I use this read replica only for SELECT operations nothing else. But suddenly I got the storage full problem with the read replica DB. Master Db is working properly, how is it possible if the size is same but replica DB's size is full?
Getting below error when executing the complex query(inner, join, group):-
ERROR 3 (HY000): Error writing file '/rdsdbdata/tmp/MY7U2XRf' (Errcode: 28 - No space left on device)
In AWS Console, The slave DB status is "Storage-full" and an event log message is : The free storage capacity for DB Instance: example-slave is low at 1% of the provisioned storage [Provisioned Storage: 49.07 GB, Free Storage: 527.80 MB]. You may want to increase the provisioned storage to address this issue.
Checked the free size with below command of both Master and replica Db instances, it's almost the same.
MySQL:
SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024/1024,2) "size in GB"
FROM information_schema.tables
GROUP BY 1
ORDER BY 2 DESC;
Master DB's space is used 23.86 GB out of 50 GB and Slave DB's space is used 24 GB out of 50 GB.
