It seems the stand Mysql select ... into outfile does not work.
We are using ICE 3.1 for Win32.

Hi !

What means “does not work” ? Could you provide some details?

I am not sure but it may be necessary to “set @bh_dataformat = ‘txt_variable’ ” before export, though recent ICE versions have this by default as I remember.

“set @bh_dataformat = ‘txt_variable’
is necessary for ICE 3.1 RC1 at least, but I dont believe this has changed with 3.1 GA

mysql> select * into outfile ‘D:\\test.data1’ from test;
ERROR 5 (HY000): The query includes syntax that is not supported by the Infobrig
ht Optimizer. Either restructure the query with supported syntax, or enable the
MySQL Query Path in the brighthouse.ini file to execute the query with reduced p
erformance.

When I set AllowMySQLQueryPath = 1 and restart Infobright it really works.

And did you “set @bh_dataformat = ‘txt_variable’  ” before export?
Have a look at http://www.infobright.org/Forums/viewthread/209/ as well, there are examples of the full export syntax.

No, I didn’t.

As Chris has clarified, “set @bh_dataformat = ‘txt_variable’  ” is a mandatory step. I was wrong in supposing that this is a default setting and I was right to mention that this can matter.

Hi Amber,

Amber - 07 March 2009 02:37 AM

When I set AllowMySQLQueryPath = 1 and restart Infobright it really works.

It works, but through MySQL optimizer. For longer queries the difference in performance may be huge. That’s why setting ‘txt_variable’ does matter.
BTW, for some queries producing large results (like millions of rows), exporting the result to file (using ‘txt_variable’) may be much faster than displaying it on a client application, including mysql command-line interface.

Regards,

I am also simile issue

I am trying to execute the below query

SELECT COUNT(1),LEFT(a.DATETIME,10),a.shortcode,a.retry_count,b.operator_name
FROM
SMSGW_V3_BAK.smsgw_esme_log_2011_07 AS a, smsgw_config_v3.smsgw_esme AS b
WHERE a.esme_id=b.esme_id AND a.state=512 AND origin IN (‘88222’,’82344’) AND a.DATETIME < ‘2011-08-01’ GROUP BY 2,3,4,5;

I am getting error like

ERROR 5 (HY000): The query includes syntax that is not supported by the Infobright Optimizer. Either restructure the query with supported syntax, or enable the MySQL Query Path in the brighthouse.ini file to execute the query with reduced performance.

I am unable to update AllowMySQLQueryPath variable

mysql> set AllowMySQLQueryPath = 1;
ERROR 1193 (HY000): Unknown system variable ‘AllowMySQLQueryPath’

after executing the below query also I am getting same error

set @bh_dataformat = ‘txt_variable’;

Some one please help me on how to enable AllowMySQLQueryPath ?

Server version: 5.1.40 build number (revision)=IB_3.5.2_r11794_12393(ice)

Hey Buddoda,

You’ll get poor performance if you allow it to use the MySQL Engine.  I’d try to find a way to execute without requiring MySQL.  From your syntax, though, I’m not sure where it’s needing to go to MySQL.  Perhaps someone else can find the point where it’s crossing over?

If you must allow MySQL Query Path, then open the brighthouse.ini file located in your data directory.  At the very bottom, change AllowMySQLQueryPath=1.

Thanks,

Jeff