Disable Strict Mode Mysql

[girish]

It seems the way to access the db itself is like this https://github.com/X2Engine/X2CRM/blob/8718c36c5710e19b4155bb00315ffec08209438d/x2engine/protected/models/Fields.php#L795

@saikarthik I am not a PHP dev as such but see https://yii2-framework.readthedocs.io/en/stable/guide/db-dao/ . Specifically, "Tip: If you need to execute a SQL query right after establishing a connection (e.g., to set the timezone or character set), you can do so in the [[yii\db\Connection::EVENT_AFTER_OPEN]] event handler. " .

There's a code sample in the above link.

[girish]

Maybe https://github.com/X2Engine/X2CRM/blob/master/x2engine/protected/config/main.php#L250 is the place to put the on afterOpen event.

[saikarthik]

@girish Thanks so much for your guidance!

Here's the exact fix:
Needed to add the following to the 'db' here (https://github.com/X2Engine/X2CRM/blob/master/x2engine/protected/config/main.php#L250) :

'initSQLs'=>array("SET @@sql_mode = REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY,', '')",),

This is where I found 'initSQLs':
https://github.com/X2Engine/X2CRM/blob/b60fb5c4ae969f59e6d4b9efa2ae72394ed45e60/x2engine/framework/db/CDbConnection.php#L244

[p44]

Dear All, I've to apply this small guide:

https://sendy.co/forum/discussion/7752/campaign-sent-to-0-recipients-ubuntu-php-7/p1

Inside "Sendy.co".

Can you help me? Where I can add code below or something similar?

        $pDO->query("SET sql_mode = ''");

Thank's a lot!

[girish]

@p44 The MySQL database in Cloudron is shared by all the apps. In MySQL 5.7.5+, they changed the behavior to have ONLY_FULL_GROUP_BY to be the default to be more compliant. Disabling this at the server level will cause issues for other apps.

The correct way to fix this is to have the app disable this mode at a session level. Like https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html . Essentially, the app has to call "SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));" after creating a connection.

So, I guess the answer is:

1. Either sendy has to provide a mechanism to do the above.
2. Alternately, you cannot run sendy in Cloudron. You have to create a dedicated server for this and setup MySQL accordingly.

[osobo]

Dear All,
I'm trying to install OpenSIS (https://github.com/OS4ED/openSIS-Classic) on Cloudron's LAMP.
It requires to disable MySQL strict mode.
I'm not a PHP Dev and don't know how to do that. Would a brave soul kindly help me to figure out where to insert SET sql_mode = ''; instruction into PHP code ?
Thanks a lot for the support !
Bertrand

[girish]

@osobo this is specific to the app. So, unless someone knows OpenSIS code base, it's difficult to know this. Maybe you can ask OpenSIS devs if this MySQL strict mode can be disabled at session level ?

[osobo]

@girish Thanks for your prompt reply. Ok I will try to contact them to inquire about this. Will keep you guys updated. Best wishes !

[timconsidine]

@girish said in Disable Strict Mode Mysql:

Essentially, the app has to call "SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));" after creating a connection.

Old thread but current problem (packaging another app)

@girish I thought our app cannot SET GLOBAL due to restricted permissions ? True / false ?

[girish]

@timconsidine said in Disable Strict Mode Mysql:

@girish I thought our app cannot SET GLOBAL due to restricted permissions ? True / false ?

My earlier comment seems to be a typo. I think I meant SET SESSION .

ONLY_FULL_GROUP_BY cannot be disabled globally on Cloudron. It's a deprecated flag in MySQL and any code needing it is deviating from SQL standards.