Cloudron makes it easy to run web apps like WordPress, Nextcloud, GitLab on your server. Find out more or install now.


Navigation

    Cloudron Forum

    • Register
    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular

    SOLVED Disable Strict Mode Mysql

    LAMP
    lamp sqlmode
    5
    15
    143
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • nebulon
      nebulon Staff last edited by

      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?

      1 Reply Last reply Reply Quote 0
      • girish
        girish Staff last edited by

        @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

        S 1 Reply Last reply Reply Quote 1
        • M
          makemrproper last edited by 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!

          1 Reply Last reply Reply Quote 1
          • girish
            girish Staff last edited by

            @makemrproper Thanks for getting back with the solution!

            1 Reply Last reply Reply Quote 1
            • M
              makemrproper last edited by

              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 = '';");*
              
              1 Reply Last reply Reply Quote 0
              • S
                saikarthik @girish last edited by

                @girish I am trying to do the same, edit the sql_mode, for the LAMP stack with multidatabase enabled. How can I set it in this case?

                1 Reply Last reply Reply Quote 0
                • girish
                  girish Staff last edited by

                  @saikarthik You have to set it in your LAMP stack code at the point where you connect to the database(s). Is that what you are asking?

                  S 1 Reply Last reply Reply Quote 0
                  • S
                    saikarthik @girish last edited by saikarthik

                    @girish Yes. I'm trying to get more details on how to do exactly that. This is not a PHP app I coded. So where do I begin to look for where it makes the database connection? Could there be more than 1 place where I need to add SET sql_mode = '';??

                    The PHP directories look like the attached.
                    Screen Shot 2020-10-04 at 11.42.43 PM.png

                    S 1 Reply Last reply Reply Quote 0
                    • S
                      saikarthik @saikarthik last edited by

                      This post is deleted!
                      1 Reply Last reply Reply Quote 0
                      • girish
                        girish Staff last edited by

                        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.

                        1 Reply Last reply Reply Quote 0
                        • girish
                          girish Staff last edited by

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

                          1 Reply Last reply Reply Quote 0
                          • S
                            saikarthik last edited by

                            @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

                            1 Reply Last reply Reply Quote 2
                            • P
                              p44 last edited by

                              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!

                              1 Reply Last reply Reply Quote 0
                              • girish
                                girish Staff last edited by

                                @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.
                                1 Reply Last reply Reply Quote 0
                                • First post
                                  Last post