The Blog

Permanently changing MySQL mode Posted on

Permanently changing SQL mode

Then, we use this path to execute the lookup:

$ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

We can see that the first favored configuration file is one in the root of the etc folder. That file, however, did not exist on my system so I opted for the second one.

First, we find out the current sql mode:

mysql -u homestead -psecret -e "select @@sql_mode"

+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+

Then, we copy the current string this query produced and remove everything we don’t like. In my case, I needed to get rid of NO_ZERO_IN_DATE, NO_ZERO_DATE and of course ONLY_FULL_GROUP_BY. The newly formed string then looks like this:

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

We open the configuration file we decided on before (/etc/mysql/my.cnf) and add the following line into the [mysqld] section:

[mysqld]
# ... other stuff will probably be here
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Save, exit, and restart MySQL:

sudo service mysql restart

Voilà, the SQL mode is permanently changed and I can continue developing the legacy project until I need some additional strictness.

This entry was posted in MySQL. Bookmark the permalink.

5 thoughts on “Permanently changing MySQL mode

  1. AlbinaSags says:

    very informative. Either way, great blog and I look forward to seeing it expand over time. I am dealing with many of these issues as well..

  2. Larrybek says:

    [URL=http://sibirki.com – проститутки новосибирска[/URL –
    [URL=https://sexonsk.xxx – проститутки новосибирска[/URL – Настоящая проститутка!!! Работаю от души и для души! … Красивая девочка, одна из лучших в Новосибирске!
    [URL=https://sexonsk.top – проститутки новосибирска[/URL –
    [URL=http://nsexy.ru – проститутки новосибирска[/URL – Вас приветствует команда сайта Сибирки, сайта, на котором размещают анкеты 86% всех проституток города Новосибирска.
    [URL=http://www.siblaguna.ru – проститутки новосибирска[/URL –
    [URL=http://sexonsk.ru – проститутки новосибирска[/URL –

  3. Anthonyassof says:

    Ищете коды киви ваучеров, не нужно!

  4. Aarontooff says:

    You’ll quickly entice RIAA’s attention by employing file-sharing providers like Kazaa and Grokster. The know-how is not unlawful (however), but making use of it to share downloadable new music is at very best a grey location. And make no blunder, RIAA can and will discover you!How do I get Free of charge Downloadable Tunes with no breaking the law?Initial fully grasp the big difference between limited and unrestricted copyrights. Just about all tracks have an artist or document label copyright. Several copyrights prohibit gratis downloads. But several musicians want to share their new music at no charge by way of absolutely free downloads.Why, you could question? Some are unknowns seeking for exposure, but quite a few renowned musicians know it is in their interest to offer free downloads. It inevitably prospects to far more CD, live performance and goods profits. Furthermore it boosts their image and recognition.

    Most tunes download expert services offer you a free of charge trial time period when you can obtain tunes at no cost. Terminate just before the demo finishes and the songs value nothing. Of system they hope you’ll continue to be and fork out the month-to-month membership cost, which in truth is a low-cost way to grow your new music collection.Review the membership conditions as some internet sites only make it possible for use of the downloads whilst you are a member. In addition, a “absolutely free songs” lookup will guide to internet sites – by or for musicians – that present mp3 downloads for cost-free.

    http://musicddl.net

    It is all over the news these days. Down load new music from the Net and you could be sued, fined, and/or go to jail. The fact is totally free new music downloads, carried out accurately, are completely authorized.The Recording Sector Association of The us (RIAA) aggressively prosecutes copyright violators. And effectively they must – it can be piracy simple and basic. But there are thousands and thousands of totally free mp3 downloads legally available. RIAA will not want you to know this and desires you to believe any songs downloads you really don’t spend for are unlawful.

  5. Josephfuh says:

    Согласен, весьма полезная мысль
    cpadollar.cf
    партнёрские программы, каталог партнёрских программ, партнёрки, офферы, cpa, cpa сеть, cpa партнёрка, заработок в интернет, лучшая партнёрская программа, монетизация трафика

Please Post Your Comments & Reviews

Your email address will not be published. Required fields are marked *



CAPTCHA
Change the CAPTCHA codeSpeak the CAPTCHA code