How to troubleshoot: ERROR 1296 (HY000): Got error 122 'Cannot retrieve error message' from CONNECT

Self resolved.

In summary, realized I tried using connect_xtrace=1023, but set it as a global variable & did not set it as a session variable, hence its effect was not immediate. After setting it as a session variable, was able to trace it to memory allocation failure.

After using CONNECT engine JDBC tables to retrieve data for a day or 2, the error when selecting from a CONNECT storage engine JDBC external table was: "ERROR 1296 (HY000): Got error 122 'Cannot retrieve error message' from CONNECT" ... and error when trying to create a CONNECT storage engine JDBC external table was: "ERROR 1030 (HY000): Got error 122 "Internal (unspecified) error in handler" from storage engine CONNECT"

I just recently noticed multiple of below lines appeared in mysqld.log too: Work area: Memory allocation failed: malloc returned Null

So tried to troubleshoot again...

Maybe I had previously used set global connect_xtrace=1023; which only changes settings for global but not current session. Hence this time tried set session connect_xtrace=1023 instead and relevant log entries below started appearing.

So key was to set connect_xtrace=1023 for the session. My bad.

Anyway, mysqld.log entries had more info now: ... New CONNECT 0x7fc46403de80, table: mssql_CURRENCY_RATE open: name=./_TMP_D/mssql_CURRENCY_RATE mode=2 test=18 PlugInit: Language='Null' SareaAlloc: Memory allocation failed: malloc returned Null Delete CONNECT 0x7fc46403de80, table: mssql_CURRENCY_RATE, xp=(nil) count=0 ... PlugInit: Language='Null' SareaAlloc: Memory allocation failed: malloc returned Null New CONNECT 0x7fc46403de80, table: mssql_CURRENCY_RATE open: name=./_TMP_D/mssql_CURRENCY_RATE mode=2 test=18 ...

So tried to set a much lower connect_work_size value: 64MB (default), and things worked again! No more errors.

So conclusion it would seem, is, CONNECT engine stopped being able to allocate based on connect_work_size after some time as mysqld & OS used more & more memory over time.

Then tried setting connect_work_size to 1GB & tried a select again, still works. Increased another 1GB & select again, repeating a few more times. Started to notice from mysqld.log that memory set for connect_work_size stops being followed after some time, & last successful value is used. (this seems to be as per documentation).

Since I had not come across a more detailed guide on how much to set connect_work_size to, made a stored procedure that I will call to set connect_work_size just before using CONNECT engine, try the size I want, then try CONNECT engine, if fail, try smaller size and try CONNECT engine, if fail, repeat until things work.

Would be great if: a) connect_work_size were implemented in similar manner to: innodb_buffer_pool_instances, innodb_buffer_pool_chunk_size, innodb_buffer_pool_size b) might be good to have something like a connect_work_size_guaranteed, which could be an amt of memory allocated but never released, to have a minimum guarantee of memory for the plugin. c) have more detailed documentation; ie: if approx table row size is xxx & rows queried in memory at any one point in time is yyy, then connect_work_size must be ( xxx * yyy ) * 1.1.