Error running query involving lots of data

Support
1

One of my web application gets below error.

“Syntax error or access violation: 1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay.”

Apparently it runs a query which requires processing large amount of data. I checked in mysql MAX_JOIN_SIZE is 5,000,000 instead of default value which is 18446744073709551615.

I tried to increase value from phpmyadmin, but it gives error I need at least one super priviledges. I don’t understand how can I fix this.

Can you please explain how can I fix this?

Thanks

2

This was addressed over community chat, but for those searching there’s a 5 million record limit to prevent resource monopolization by a busted query. Rationale being 5,000,000 x 100 byte records coming together in a join isn’t encountered very often and symptomatic of a bad query.

You can disable this safety check by adding the following to /root/apnscp-vars-runtime.yml then running upcp -sb mysql/install

mysql_custom_config:
  max_join_size: -1

Or to override it manually, add to any file lexicographically higher than apnscp.cnf in /etc/my.cnf.d

[server]
max_join_size=-1

Then restart the service, systemctl restart mysql

Be sure to assign a category to posts so they get relayed to the chat!