MYSQL error about open-web-analytics HOT 13 CLOSED

I'm getting the exact same error. I'm on windows using mysql and apache. OWA version is 1.5.7.

Query: INSERT into owa_request (...) VALUES (...)
Error is "A MySQL error ocured. Error: (1366) Incorrect integer value: '' for column 'is_new_visitor' at row 1."
Query is INSERT into owa_queue_item (....) VALUES (...)
Error is "A MySQL error ocured. Error: (1366) Incorrect integer value: '' for column 'handled_timestamp' at row 1."

Updating the column values manually and running the query generates the following errors:
Error Code: 1366. Incorrect integer value: '' for column 'is_new_visitor' at row 1
Error Code: 1366. Incorrect integer value: '' for column 'days_since_prior_session' at row 1
Error Code: 1366. Incorrect integer value: '' for column 'ad_id' at row 1
Error Code: 1366. Incorrect integer value: '' for column 'campaign_id' at row 1
Error Code: 1366. Incorrect integer value: '' for column 'inbound_visitor_id' at row 1
Error Code: 1366. Incorrect integer value: '' for column 'inbound_visitor_id' at row 1
Error Code: 1366. Incorrect integer value: '' for column 'inbound_session_id' at row 1
Error Code: 1366. Incorrect integer value: '' for column 'feed_subscription_id' at row 1
Error Code: 1366. Incorrect integer value: '' for column 'prior_document_id' at row 1
Error Code: 1366. Incorrect integer value: '' for column 'prior_document_id' at row 1
Error Code: 1366. Incorrect integer value: '' for column 'prior_document_id' at row 1
Error Code: 1366. Incorrect integer value: '' for column 'is_comment' at row 1
Error Code: 1366. Incorrect integer value: '' for column 'is_entry_page' at row 1
Error Code: 1366. Incorrect integer value: '' for column 'is_robot' at row 1
Error Code: 1366. Incorrect integer value: '' for column 'is_feedreader' at row 1

And then finally it inserts the record. I have no idea how this is all put together since I just downloaded OWA today and tried to get it work.

from open-web-analytics.

turn off strict mode on MySQL and see if that goes away.

-P-

On Oct 10, 2014, at 2:14 PM, jpete2477 [email protected] wrote:

I'm getting the exact same error. I'm on windows using mysql and apache. OWA version is 1.5.7.

Query: INSERT into owa_request (...) VALUES (...)
Error is "A MySQL error ocured. Error: (1366) Incorrect integer value: '' for column 'is_new_visitor' at row 1."
Query is INSERT into owa_queue_item (....) VALUES (...)
Error is "A MySQL error ocured. Error: (1366) Incorrect integer value: '' for column 'handled_timestamp' at row 1."

Running the queries directly in mysql generates: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT into owa_request (id, visitor_id, session_id, site_id, referer_id, ua_id,' at line 4

—
Reply to this email directly or view it on GitHub.

from open-web-analytics.

That seems to have fixed the problem. Thanks for that. Is that something I'll need to do once this is moved to production also? (Production for me is all Linux based.)

from open-web-analytics.

yes. MySQL changed the default of this recently.

On Oct 10, 2014, at 3:40 PM, jpete2477 [email protected] wrote:

That seems to have fixed the problem. Thanks for that. Is that something I'll need to do once this is moved to production also? (Production for me is all Linux based.)

—
Reply to this email directly or view it on GitHub.

from open-web-analytics.

Hey guys - maybe I missed this on the OWA website but if its not on there, could you PLEASE put it on the wiki? I just wasted a couple hours on this. Sadly will probably move on at least until this is fixed in the code as I don't think its appropriate to reduce mysql security.

from open-web-analytics.

Done. thanks. It is possible turn off strict mode for just the OWA tables. I'm going to leave this open as a bug as I'd like to get it fixed.

from open-web-analytics.

Done. thanks. It is possible turn off strict mode for just the OWA tables. I'm going to leave this open as a bug as I'd like to get it fixed.

@padams You can only turn off the strict mode for the current session. Adding the sql command to the connection logic would be the solution.

from open-web-analytics.

Is this already implemented? See owa_db_mysql.php lie 131

// turn off strict mode. needed on mysql 5.7 and lter when it is turned on by default.
$this->query( "SET SESSION sql_mode=''" );

from open-web-analytics.

It looks that way. But people still seem to be reporting the issue. Not sure why.

It would be good to fix this the right way at some point.

Maybe we open an issue ticket for supporting strict mode which would require null setting for empty values on INSERT or UPDATE statements.

from open-web-analytics.

Although maybe that report was from before I implemented that fix... I think we can close this.

from open-web-analytics.

I think the command is wrong. SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION'; should be right. See: https://www.linode.com/community/questions/17070/how-can-i-disable-mysql-strict-mode

from open-web-analytics.

@padams i think this can be closed

from open-web-analytics.

ok.

from open-web-analytics.