MySQL Bugs: #72030: Exceptions on SQL Explain

Bug #72030	Exceptions on SQL Explain
Submitted:	13 Mar 2014 7:45	Modified:	5 Nov 2016 9:43
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Enterprise Monitor: Server	Severity:	S3 (Non-critical)
Version:	3.0.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	explain

Description:
In the logs there are messages about: 
Cannot use ref access on index '*field*' due to type or collation conversion on field '*field*'

2014-03-12 11:15:08,014 ERROR [cme.schedule.Scheduler-14:com.mysql.etools.agent.collection.impl.InventoryConcentrator] org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [EXPLAIN FORMAT=JS
ON INSERT INTO *table* (
*REMOVED*
)
SELECT
*REMOVED*
FROM *table*
LEFT JOIN *table*
        ON *col* = *col* AND *col* = @some_var
JOIN *table* ON *col* = *col* AND *col* = @some_var
WHERE *col* = @some_var
AND *col* IS NULL]; SQL state [HY000]; error code [1739]; Cannot use ref access on index '*field*' due to type or collation conversion on field '*field*'; nested exception is java.sql.SQLException: Cannot use ref access on in
dex '*field*' due to type or collation conversion on field '*field*'
org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [EXPLAIN FORMAT=JSON INSERT INTO *query*]; SQL state [HY000]; error code [1739]; Cannot use ref access on index '*field*' due to type or collation conversion on field '*field*'; nested exception is java.sql.SQLException: Cannot use ref access on index '*field*' due to type or collation conversion on field '*field*'
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translateAbstractFallbackSQLExceptionTranslator.java:83)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:406)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:455)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:463)
        at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:471)
        at org.springframework.jdbc.core.JdbcTemplate.queryForMap(JdbcTemplate.java:467)
        at com.mysql.etools.agent.collection.mysql.quan.StatementDigestCollector.getExplain(StatementDigestCollector.java:546)
        at com.mysql.etools.agent.collection.mysql.quan.StatementDigestCollector.access$000(StatementDigestCollector.java:73)
        at com.mysql.etools.agent.collection.mysql.quan.StatementDigestCollector$ExplainAction.execute(StatementDigestCollector.java:296)
        at com.mysql.etools.agent.collection.mysql.quan.StatementDigestCollector$ExplainAction.execute(StatementDigestCollector.java:287)
        at com.mysql.etools.agent.collection.MysqlConnection$State.actionExecute(MysqlConnection.java:528)
        at com.mysql.etools.agent.collection.MysqlConnection$State.utilizeOperationTry(MysqlConnection.java:487)
        at com.mysql.etools.agent.collection.MysqlConnection$State.utilizeOperation(MysqlConnection.java:455)
        at com.mysql.etools.agent.collection.MysqlConnection$OpenState.utilizeImpl(MysqlConnection.java:674)
        at com.mysql.etools.agent.collection.MysqlConnection$OpenState.utilize(MysqlConnection.java:694)
        at com.mysql.etools.agent.collection.MysqlConnection$UtilizeCallable.call(MysqlConnection.java:1062)
        at com.mysql.etools.limits.RateLimit.doWithRateLimit(RateLimit.java:222)
        at com.mysql.etools.agent.collection.MysqlConnectionRateLimit.doWithRateLimit(MysqlConnectionRateLimit.java:81)
        at com.mysql.etools.agent.collection.MysqlConnectionRateLimit.doWithRateLimitNonCore(MysqlConnectionRateLimit.java:66)
        at com.mysql.etools.agent.collection.MysqlConnection.nonCoreUtilizeCall(MysqlConnection.java:1042)
        at com.mysql.etools.agent.collection.MysqlConnection.access$3100(MysqlConnection.java:71)
        at com.mysql.etools.agent.collection.MysqlConnection$4.execute(MysqlConnection.java:1098)
        at com.mysql.etools.agent.collection.PrivilegeFallback.attempt(PrivilegeFallback.java:19)
        at com.mysql.etools.agent.collection.MysqlConnection.utilize(MysqlConnection.java:1102)
        at com.mysql.etools.agent.collection.mysql.quan.StatementDigestCollector.getExplainIfNecessary(StatementDigestCollector.java:280)
        at com.mysql.etools.agent.collection.mysql.quan.StatementDigestCollector.calculateDeltasMaybeExplains(StatementDigestCollector.java:215)
        at com.mysql.etools.agent.collection.mysql.quan.StatementDigestCollector.lockedFetch(StatementDigestCollector.java:155)
        at com.mysql.etools.agent.collection.mysql.quan.StatementDigestCollector.fetch(StatementDigestCollector.java:132)
        at com.mysql.etools.agent.collection.impl.InventoryConcentrator$ProvidersIterator.computeNext(InventoryConcentrator.java:130)
        at com.mysql.etools.agent.collection.impl.InventoryConcentrator$ProvidersIterator.computeNext(InventoryConcentrator.java:106)
        at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:143)
        at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:138)
        at com.google.common.collect.ImmutableList.copyOf(ImmutableList.java:263)
        at com.google.common.collect.ImmutableList.copyOf(ImmutableList.java:223)
        at com.mysql.etools.agent.collection.impl.MetricCollectionConcentrator.doCollect(MetricCollectionConcentrator.java:71)
        at com.mysql.etools.agent.collection.impl.MetricCollectionConcentrator.collect(MetricCollectionConcentrator.java:62)
        at com.mysql.etools.agent.collection.advisor.CollectionManager$ScheduleFamilyTask.run(CoalescedCollectionRunner.java:353)
        at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:51)
        at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)
        at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
        at java.util.concurrent.FutureTask.run(Unknown Source)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(Unknown Source)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Cannot use ref access on index '*field*' due to type or collation conversion on field '*field*'
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:927)
        at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:2006)
        at com.mysql.jdbc.RowDataDynamic.nextRecord(RowDataDynamic.java:404)
        at com.mysql.jdbc.RowDataDynamic.next(RowDataDynamic.java:383)
        at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7012)
        at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:91)
        at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:1)
        at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:445)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:395)
        ... 44 more

How to repeat:
See description.

When I try this explain manualy:
The first try: 
It works, reports a nested_loop with 3 tables.

The second try:
It fails with 'Impossible WHERE after reading const tables
With 4 warnings:
Connot use ref access on index ... due to type or collation conversion on field...

MySQL version of the monitored server: 5.6.16 Enterprise on RHEL6.

I've reported the behaviour in which the explain only works the first time as Bug #72031

Hello Daniel,

Thank you for the bug report.

Thanks,
Umesh

add explain tag

This isn't actually a bug with MEM itself, as noted this is an exception that is (wrongly) getting raised via the MySQL Server itself (Bug#72031).

Closing this bug as !bug, because I believe us logging an exception in these cases is actually the right thing to do anyway.