Sort aborted: Table storage engine for doesn't have this option
Hi,
I've got the following error and hope someone can shed some light:
/usr/sbin/mysqld: Sort aborted: Table storage engine for '/tmp/#sql_8be_1' doesn't have this option
My query:
SELECT a_id, descr, u_id, val
FROM v_ua
WHERE u_id = ?
ORDER BY u_id
My view:
CREATE VIEW v_ua (u_id,a_id,val,desc, c_id, c_time, m_id, m_time) as
SELECT u_id, a_id, val, descr, c_id, c_time, m_id, m_time
FROM u_a
UNION
SELECT DISTINCT u_id, a_id, val, descr, c_id, c_time, m_id, m_time
FROM g_a, u, u_p
WHERE g_a.g_id = u_p.g_id
AND u.u_id = u_p.u_id
AND u.def_p_id = u_p.p_id;
Trial and Error results:
Query through client is OK, only causes the error through ODBC.
Removing the UNION and it works
Removing the ORDER BY works
Some further trial and error from the coding point of view:
Removing the parameter for u_id and put the number into the query text works too.
SOLVED (or better worked around):
The BindParameter causes the entire view to be loaded first which is simply a question of tuning the temporary table sizes. I assume the error comes from MyISAM somehow when a temporary table gets converted from MEMORY. Doesn't explain the actual error.
Edited 2 time(s). Last edit at 03/04/2014 11:29PM by Robert Jacob.