2

I'm getting this error while trying to execute query in MySQL database:

SQL Error (1231): Variable 'optimizer_switch' can't be set to the value of 'derived_merge=OFF'

Query:

use <dbname>;
set global optimizer_switch='derived_merge=off';

I want to execute query to solve other issue I have that I'm getting while updating the edmx and I got that query from answer here.

I'm using: MySQL: 5.6.26, EF: 6.1.4

Community
  • 1
  • 1
Pratik J
  • 109
  • 2
  • 9
  • Assuming EF schema uses DB first, have you tried to check using either `SELECT @@GLOBAL.optimizer_switch` or `SELECT @@optimizer_switch`? If the double `@` works, try `SET @@optimizer_switch='derived_merge=OFF' ` instead. – Tetsuya Yamamoto Mar 29 '17 at 06:24
  • SELECT @@GLOBAL.optimizer_switch; does work but I cannot use: SET @@optimizer_switch='derived_merge=OFF', giving same error, however I've noticed one thing that, 'derived_merge' is not present when I select optimizer_switch. – Pratik J Mar 29 '17 at 08:16
  • AFAIK from MySQL KB the `derived_merge` option is not available on versions 5.6.x or earlier: https://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html (compare with 5.7.x in same topic: https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html). Check some details for derived tables in 5.6.x: https://dev.mysql.com/doc/refman/5.6/en/derived-table-optimization.html. – Tetsuya Yamamoto Mar 29 '17 at 09:10

1 Answers1

0

What you are trying to do does not work in MySQL 5.6. I had experienced the same issue. You can upgrade your MySQL version to get this working, it's supported in 5.7.x.

The Gilbert Arenas Dagger
  • 12,071
  • 13
  • 66
  • 80