0

This is the query that I have performed.

select *
from samples
left join ETL_freemark on samples.lab_number_code=ETL_freemark.Lab_Number;

Both tables have only about 1,000,000 rows in it. 'Samples' table is able to left join easily with other tables.

It is only when it is left joined to 'ETL_freemark' that is returning with the

Error Code: 2013. Lost connection to MySQL server during query 30.016 sec

After increasing DBMS connection keep-alive interval/read timeout/connection timeout to 3000 seconds and making indexes for samples.lab_number_code and for ETL_freemark_Lab_number this error comes out instead

Error Code: 1105. The last transaction was aborted due to Seamless Scaling. Please retry. 44.266 sec"

And these 3 queries work fine.

select *
from samples

and

select *
from ETL_freemark

and

select *
from samples
inner join ETL_freemark on samples.lab_number_code=ETL_freemark.lab_number

Using explain select results are these:

explain select *
        from samples
        left join ETL_freemark on samples.lab_number_code=ETL_freemark.Lab_Number;

1   SIMPLE  samples             ALL                 132440  100.00  
1   SIMPLE  ETL_freemark        ALL                 179093  100.00  Using where; Using join buffer (Block Nested Loop)

Any ideas on why my left join is not working?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
ReinAtreides
  • 19
  • 3
  • Do a `explain select * from samples left join ETL_freemark on samples.lab_number_code=ETL_freemark.Lab_Number;` and post the results here – Raymond Nijland Feb 09 '22 at 10:21
  • Did you read all of https://aws.amazon.com/blogs/database/best-practices-for-working-with-amazon-aurora-serverless/ – RiggsFolly Feb 09 '22 at 10:29
  • I've read the article but how does it explain why I'm able to do an inner join at lightning quick speed but not a left join? I don't think it's about resources.. – ReinAtreides Feb 09 '22 at 11:09
  • Welcome to Stack Overflow. With error code 1105 AWS Aurora tells you, explicitly, that your problem happens because your "autoscaling" server ran short of resources, your belief to the contrary notwithstanding. What happens when you try again? – O. Jones Feb 09 '22 at 11:41
  • As for Block Nested Loop in your explain output, please read this. https://stackoverflow.com/questions/60059084/what-does-using-join-buffer-block-nested-loop-mean-with-explain-mysql-command Block Nested Loop queries require large quantities of RAM to be satisfied, so Aurora must scale up. – O. Jones Feb 09 '22 at 11:41

0 Answers0