Disable Strict Mode Mysql

[makemrproper]

Hi

How do I disable Strict Mode on the Mysql database instance that my LAMP container connects to?

I have now tried "everything" without any success.

At least for the LAMP container in question, which will be hosting Vanillaforums, I need to disable Mysql strict mode.

From inside the container, and accessing the Mysql command interpreter:

SHOW VARIABLES LIKE 'sql_mode';

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

Question
How do I change the sql_mode both runtime and as a permanent setting on each reboot - for my LAMP container?
I guess I need root access to the mysql for the database engine running for the container, but from where do I access it and how do I reset it?

Documentation:

Although this document is very nicely written and contains a lot of useful information in general cases, it didn't help me unfortunately: https://cloudron.io/documentation/apps/lamp/

I have tried to change it in the mysql server running in the regular Ubuntu system, but that didn't work. I later understood that's only used for internal purposes, so I guess I shouldn't have messed with it. It would be good if Cloudron internals were documented explicitly somewhere, such as the internal Mysql database.

Thanks. I'm really enjoying my subscription.

[nebulon]

That is correct, Cloudron comes with two mysql databases. One for the system itself, which is the one on the ubuntu host system, you should not touch that one, similar to basically everything on the host system. Any changes may either break your Cloudron, has side-effects or will simply be overwritten during an update.
The second instance is running as a docker container and provides databases for the apps as an addon. This is shared across all apps using mysql to safe resources.

Regarding the actual strict mode question, I think we had this topic already some time in the past, but I can't find the info right now. From what I remember we were not able to enable strict mode on that mysql instance, since some apps would break when enabled Also as far as I understand this is a setting across all databases within that mysql instance, so it is impossible to enable that for a single app only. Is it maybe possible for your application in question to configure it to work even without that mode?

[girish]

@makemrproper said in Disable Strict Mode Mysql:

How do I change the sql_mode both runtime and as a permanent setting on each reboot - for my LAMP container?

The global default sql_mode is strict mode. sql_mode can, however, be set per session. In your PHP code, where you create the database connection, just do this:

    SET sql_mode = '';

See https://stackoverflow.com/questions/5273726/how-to-change-sql-mode-at-runtime and https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

[makemrproper]

Thanks for your answers.

Trying to solve it in the code:
The problem is; Modern PHP code does not allow direct SQL queries anymore. Its now modern to use prepared statements.
I could never be bothered to dive further into PHP than that, so I guess the joke is on me. But I can't just easily run
SET sql_mode = ''; because of this, and the vanillaforums code is modern enough to be way to convoluted to figure out how to deal with it.

EDIT:
Problem solved!
I was successful at adding this SQL code as a prepared statement.

SET sql_mode = '';

I managed to do my first prepared statement today, maybe its not a real prepared statement, but at least half way.

For the record, I got Vanillaforums to work by adding the following code after line 156 (but this can change), rather than line number it was added to the function newPDO in file $vanillaforums-root/library/database/class.database.php.

The entire function looks like this now:

     *
     *
     * @param $dsn
     * @param $user
     * @param $password
     * @return PDO
     * @throws Exception
     */
    protected function newPDO($dsn, $user, $password) {
        try {
            $pDO = new PDO(strtolower($this->Engine).':'.$dsn, $user, $password, $this->ConnectionOptions);
            $pDO->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);
            $pDO->query("set time_zone = '+0:0'");
            $pDO->query("SET sql_mode = ''");

Thanks for your support!

[girish]

@makemrproper Thanks for getting back with the solution!

[makemrproper]

The devs of Vanilla provided me with this solution

*If you create a file /conf/bootstrap.after.php and put that into this file, it will be executed on each request:

<?php
Gdn::database()->query("SET sql_mode = '';");*