{"id":1020,"date":"2017-05-16T20:22:44","date_gmt":"2017-05-17T01:22:44","guid":{"rendered":"http:\/\/xfloyd.net\/blog\/?p=1020"},"modified":"2017-05-16T20:24:00","modified_gmt":"2017-05-17T01:24:00","slug":"permanently-changing-mysql-mode","status":"publish","type":"post","link":"http:\/\/xfloyd.net\/blog\/?p=1020","title":{"rendered":"Permanently changing MySQL mode"},"content":{"rendered":"<h2 id=\"permanently-changing-sql-mode\">Permanently changing SQL mode<\/h2>\n<p><!--more--><\/p>\n<p>Then, we use this path to execute the lookup:<\/p>\n<pre class=\" language-undefined\"><code class=\" language-undefined\">$ \/usr\/sbin\/mysqld --verbose --help | grep -A 1 \"Default options\"\r\n\r\nDefault options are read from the following files in the given order:\r\n\/etc\/my.cnf \/etc\/mysql\/my.cnf ~\/.my.cnf\r\n<\/code><\/pre>\n<p>We can see that the first favored configuration file is one in the root of the <code class=\" language-undefined\">etc<\/code> folder. That file, however, did not exist on my system so I opted for the second one.<\/p>\n<p>First, we find out the current sql mode:<\/p>\n<pre class=\" language-undefined\"><code class=\" language-undefined\">mysql -u homestead -psecret -e \"select @@sql_mode\"\r\n\r\n+-------------------------------------------------------------------------------------------------------------------------------------------+\r\n| @@sql_mode                                                                                                                                |\r\n+-------------------------------------------------------------------------------------------------------------------------------------------+\r\n| 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 |\r\n+-------------------------------------------------------------------------------------------------------------------------------------------+\r\n<\/code><\/pre>\n<p>Then, we copy the current string this query produced and remove everything we don\u2019t like. In my case, I needed to get rid of <code class=\" language-undefined\">NO_ZERO_IN_DATE<\/code>, <code class=\" language-undefined\">NO_ZERO_DATE<\/code> and of course <code class=\" language-undefined\">ONLY_FULL_GROUP_BY<\/code>. The newly formed string then looks like this:<\/p>\n<pre class=\" language-undefined\"><code class=\" language-undefined\">STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION\r\n<\/code><\/pre>\n<p>We open the configuration file we decided on before (<code class=\" language-undefined\">\/etc\/mysql\/my.cnf<\/code>) and add the following line into the <code class=\" language-undefined\">[mysqld]<\/code> section:<\/p>\n<pre class=\" language-undefined\"><code class=\" language-undefined\">[mysqld]\r\n# ... other stuff will probably be here\r\nsql_mode = \"STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION\"\r\n<\/code><\/pre>\n<p>Save, exit, and restart MySQL:<\/p>\n<pre class=\" language-undefined\"><code class=\" language-undefined\">sudo service mysql restart\r\n<\/code><\/pre>\n<p>Voil\u00e0, the SQL mode is permanently changed and I can continue developing the legacy project until I need some additional strictness.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Permanently changing SQL mode<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"_links":{"self":[{"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1020"}],"collection":[{"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1020"}],"version-history":[{"count":3,"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1020\/revisions"}],"predecessor-version":[{"id":1023,"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1020\/revisions\/1023"}],"wp:attachment":[{"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1020"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1020"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1020"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}