Pentaho MySQL and SQL_BIG_SELECTS

Have you ever recognized that a specific query runs into the Error that you need SQL_BIG_SELECTS=1.

The problem you will run into is that this is a session variable, so no chance to set it on the DB Server.

After spending multiple days with Pentaho Enterprise Support we had a chat with MySQL, they told us to set a Parameter in the connect string.

Which means the Database URL should be something like:

jdbc:mysql://DBServer:3306/datawh?defaultFetchSize=500&CursorFetch=true&sessionVariables=@@SQL_BIG_SELECTS=1

Replace DBServer with the Hostname.

The parameters DefaultFetchSize=500, CursorFetch=true are best practice parameters we have set around MySQL and Pentaho.