0

Could I set my InnoDB MySQL Server session state for SQL_SAFE_UPDATES and INNODB_FLUSH_LOG_AT_TRX_COMMIT using MySQL Workbench's SQL Query window or MySQL Connection Navigator instead of in a c# Mono, .NET 4.5 MySQLCommand constructor on Ubuntu Linux 15.10?

If not, could I set my InnoDB MySQL Server session state for SQL_SAFE_UPDATES and INNODB_FLUSH_LOG_AT_TRX_COMMIT using the MySQL server startup script? Where is that located on the Ubuntu Linux 15.10, and what mysql command line arguments should I put in the MySQL server startup scripts?

Here is my SQL script which I wish to run in the SQL Query window:

-- Current Database: `scdvr`
--

CREATE DATABASE IF NOT EXISTS `scdvr` 

USE `scdvr`;

SET SQL_SAFE_UPDATES = 0;

SET GLOBAL INNODB_FLUSH_LOG_AT_TRX_COMMIT = 2;
--
-- Table structure for table `ActionSequence`
--

DROP TABLE IF EXISTS `ActionSequence`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ActionSequence` (
  `ASID` varchar(64) NOT NULL,
  `EAID` varchar(64) NOT NULL,
  `SequenceOrder` smallint(6) NOT NULL DEFAULT '0',
  `Enabled` tinyint(1) NOT NULL DEFAULT '1',
  `Toast` tinyint(1) NOT NULL DEFAULT '0',
  `SendToCentral` tinyint(1) NOT NULL DEFAULT '0',
  `LogAsEvent` tinyint(1) NOT NULL DEFAULT '0',
  `CameraGroupID` int(11) DEFAULT NULL,
  `AudioAlertFile` varchar(800) DEFAULT NULL,
  `EmailAddresses` varchar(1500) DEFAULT NULL,
  PRIMARY KEY (`ASID`),
  UNIQUE KEY `ASID` (`ASID`),
  UNIQUE KEY `EAID` (`EAID`),
  KEY `IX_ActionSequence_EAID` (`EAID`),
  KEY `FK_ActionSequence_CameraGroups` (`CameraGroupID`),
  CONSTRAINT `FK_ActionSequence_CameraGroups` FOREIGN KEY (`CameraGroupID`) REFERENCES `CameraGroups` (`CameraGroupID`) ON DELETE SET NULL ON UPDATE NO ACTION,
  CONSTRAINT `FK_ActionSequence_EventAction` FOREIGN KEY (`EAID`) REFERENCES `EventAction` (`EAID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
1

What version are you running? 5.6 doc gives the details:

innodb_flush_log_at_trx_commit can be set (1) on the commandline, (2) in my.cnf, or (3) via SET GLOBAL.

sql_safe_updates can only be set via SET SESSION or SET GLOBAL.

Note that SET GLOBAL does not impact anyone currently connected (including yourself). It will effect anyone connecting later, but before the next shutdown of mysqld.

my.cnf affects those who connect after the next restart.

Your question asks how to set them, but fails to say what the ultimate goal is, so I cannot recommend a particular approach. (Do you care about Workbench? Your C# connection? Others currently connected? Future connections? What?)

I do not know whether Workbench re-connects between actions. It probably does. This implies that SET SESSION may not have any impact.

Update (based on update to Question)

SET GLOBAL INNODB_FLUSH_LOG_AT_TRX_COMMIT = 2;
Note: that SET will affect other users.
Now disconnect and reconnect.
SET SQL_SAFE_UPDATES = 0;
and do the rest of your task.

But... Neither of those settings impact a mere CREATE TABLE. I assume you will proceed to do INSERTs and UPDATEs?

Side notes: These two indexes are redundant and should be removed:

UNIQUE KEY `ASID` (`ASID`),
KEY `IX_ActionSequence_EAID` (`EAID`),

A PRIMARY KEY is a UNIQUE KEY is a KEY.

Why have both ASID and EAID? Both are UNIQUE; both are VARCHAR(64); either could be the PRIMARY KEY.

| improve this answer | |
  • Thank you for your excellent answer. I am running MySQL Server 5.6. I just edited the original question to include my SQL script which I wish to run in the SQL Query window: When does it take effect : before or after the next restart? – Frank Feb 6 '16 at 19:54
  • Thank you for your very useful update. The reason I put SET GLOBAL INNODB_FLUSH_LOG_AT_TRX_COMMIT = 2; SET SQL_SAFE_UPDATES = 0; in the SQL script is for INSERTS and UPDATES. Does your updated answer imply that I have to disconnect and reconnect for it take effect? – Frank Feb 6 '16 at 20:14
  • The disconnect/reconnect is to get the GLOBAL value to be given to your SESSION. (This was implied in my answer, but in a subtle way.) – Rick James Feb 6 '16 at 21:47
  • (I think everything I said here applies to all platforms, not just Linux. There may have been changes over the versions of MySQL; check the page corresponding to the link I gave.) – Rick James Feb 6 '16 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.