How to set SQL session variables globally?

[tntramsey]

The issue I am facing is that I am trying to install Magento 2 with a Digital Ocean Managed Database(DO). The issue is that DO does not allow tables to be created without a primary key(via a global sql_require_primary_key=1). They do allow this to be set locally via SET @@local.sql_require_primary_key = 0; but I cannot think of a way to set this before any/all table manipulations. I was able to install Magento via a SQL import and setting the session variable, but now even extensions that create tables are erroring out with :

SQLSTATE[HY000]: General error: 3750 Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set

 Turning it off outside of the session is not possible, and I really didn't want to manage my own database nodes.

The only thing I can think of is to customize a preference that could possibly override something like the following, but I may not be understanding correctly.  Any ideas?

startSetup(){
    $this->rawQuery("SET @@local.sql_require_primary_key = 0");
    return $this;
}

[Nishu Jindal]

Hi @tntramsey 

Can you please check below link for setting up session variables.

http://fibrevillage.com/database/330-how-to-change-mysql-system-parameters-variables

Hope this helps you!

Problem Solved! Click Kudos & Accept as Solution!

[tntramsey]

It is a digital ocean managed database, and they can only be set in the connection scripts(and not inside any configuration file).  I need Magento to set it, just like it does in the MySql adapter interface, but not sure how to go about doing it.

In the startSetup() method they are already doing this.

public function startSetup()
    {
        $this->rawQuery("SET SQL_MODE=''");
        $this->rawQuery("SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0");
        $this->rawQuery("SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'");

        return $this;
    }

[tntramsey · Accepted solution]

Come to find out the /app/etc/env.php file has an initStatements setting where these can be set. My specific need was added like so

'db' => [
    'connection' => [
        'default' => [
            'initStatements' => 'SET NAMES utf8; SET @@local.sql_require_primary_key = OFF;'
        ]
    ]
]

[centrable_admin]

Legend.

Fixed it for me.