Avoiding the NO STRICT_TRANS_TABLES requirement?

Forum for questions and support relating to the 1.29.x releases only.
Locked
bodom
Posts: 15
Joined: Thu Feb 15, 2018 4:52 am

Avoiding the NO STRICT_TRANS_TABLES requirement?

Post by bodom »

Hi there!

I have installed this software and it looks very interesting; anyway, I can't use it because I am facing the error described in this post: viewtopic.php?t=24943

Unfortunately, I cannot change the global sql_mode of the server, because other apps are also running on it.

Since setting the SQL mode after connection is just a matter of running a single query, I suppose there is another way to have zm set its own SQL mode at runtime. How can I achieve that?

Thank you!
Last edited by bodom on Thu Feb 15, 2018 10:34 pm, edited 1 time in total.
bbunge
Posts: 2930
Joined: Mon Mar 26, 2012 11:40 am
Location: Pennsylvania

Re: Aviding the NO STRICT_TRANS_TABLES requirement?

Post by bbunge »

Actually you need to set

sql_mode=NO_ENGINE_SUBSTITUTION
bodom
Posts: 15
Joined: Thu Feb 15, 2018 4:52 am

Re: Avoiding the NO STRICT_TRANS_TABLES requirement?

Post by bodom »

I can't.

Any different solution?
bbunge
Posts: 2930
Joined: Mon Mar 26, 2012 11:40 am
Location: Pennsylvania

Re: Avoiding the NO STRICT_TRANS_TABLES requirement?

Post by bbunge »

Switch to Mariadb. Or go back to an earlier MySQL, 5.5 I think.
bodom
Posts: 15
Joined: Thu Feb 15, 2018 4:52 am

Re: Avoiding the NO STRICT_TRANS_TABLES requirement?

Post by bodom »

I can't change the DBMS or its globals settings, because many other apps are running on it.

Is there any solution that involves changing ZoneMinder configuration instead?

This seems to be a quite common scenario: I don't think everyone runs ZoneMinder on a dedicated server.
bbunge
Posts: 2930
Joined: Mon Mar 26, 2012 11:40 am
Location: Pennsylvania

Re: Avoiding the NO STRICT_TRANS_TABLES requirement?

Post by bbunge »

Virtual box?
While I am not an advocate for it it may be possible to use Docker. I seem to remember the database also runs in Docker.
User avatar
knight-of-ni
Posts: 2404
Joined: Thu Oct 18, 2007 1:55 pm
Location: Shiloh, IL

Re: Avoiding the NO STRICT_TRANS_TABLES requirement?

Post by knight-of-ni »

Refer to the sql documentation to set sql_mode per session:
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

This shows you how to set the variable at the start of each session transparently:
https://dba.stackexchange.com/questions ... given-user

My friend Google is really smart! :D
Visit my blog for ZoneMinder related projects using the Raspberry Pi, Orange Pi, Odroid, and the ESP8266
All of these can be found at https://zoneminder.blogspot.com/
bodom
Posts: 15
Joined: Thu Feb 15, 2018 4:52 am

Re: Avoiding the NO STRICT_TRANS_TABLES requirement?

Post by bodom »

Does your smart Google friend also explains how to apply that to ZoneMinder's session?

Because setting the variable for my own console session seems quite useless to me...
User avatar
knight-of-ni
Posts: 2404
Joined: Thu Oct 18, 2007 1:55 pm
Location: Shiloh, IL

Re: Avoiding the NO STRICT_TRANS_TABLES requirement?

Post by knight-of-ni »

bodom wrote: Fri Feb 16, 2018 2:45 pm Does your smart Google friend also explains how to apply that to ZoneMinder's session?

Because setting the variable for my own console session seems quite useless to me...
Try reading what I linked for you and you will find your answer.
Visit my blog for ZoneMinder related projects using the Raspberry Pi, Orange Pi, Odroid, and the ESP8266
All of these can be found at https://zoneminder.blogspot.com/
bodom
Posts: 15
Joined: Thu Feb 15, 2018 4:52 am

Re: Avoiding the NO STRICT_TRANS_TABLES requirement?

Post by bodom »

So, for others who may be unable to quickly understand the answer, the solution involves hacking mysql's login script.

Edit your mysqld.conf and add this inside the [mysqld] section:

Code: Select all

init_connect = 'SET @@sql_mode = CASE CURRENT_USER() WHEN \'zmuser@localhost\' THEN \'NO_ENGINE_SUBSTITUTION\' ELSE @@sql_mode END;'
(adjust it accordingly to you zoneminder username and merge any previous script, if any)

That's weird indeed, but it works.

Thank you. Solved.
Locked