Avoiding the NO STRICT_TRANS_TABLES requirement?

[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!

[bbunge]

Actually you need to set

sql_mode=NO_ENGINE_SUBSTITUTION

[bodom]

I can't.

Any different solution?

[bbunge]

Switch to Mariadb. Or go back to an earlier MySQL, 5.5 I think.

[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]

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.

[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!

[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...

[knight-of-ni]

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.

[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.