Facebook Twitter Instagram
    TecAdmin
    • Home
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    You are at:Home»Databases»MySQL»How to Disable Strict SQL Mode in MySQL

    How to Disable Strict SQL Mode in MySQL

    By RahulNovember 27, 20171 Min ReadUpdated:June 30, 2020

    Question – How to Disable Strict SQL Mode in MySQL? How do I disable MySQL strict mode? How to set sql_mode option globally to disable strict mode my MySQL server?

    Advertisement

    Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. This can be for any reason like if strict mode is enabled, division by zero produces an error. Date as ‘0000-00-00’ is not permitted and produce an error. This tutorial will help you for disabling the strict SQL mode in MySQL server 5.7 or later versions.

    Disable Strict SQL Mode

    You can do it by editing mysqld.cnf on your server. But for this tutorial, I will create a separate configuration file to disable strict SQL mode in MySQL server.

    sudo vim /etc/mysql/conf.d/disable_mysql_strict_mode.cnf
    

    Add the below content and save file.

    [mysqld] 
    sql_mode="IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 
    

    Now restart MySQL service to apply changes.

    sudo systemctl restart mysql.service
    

    Verify SQL Mode

    Login to the MySQL console and execute below query to find the values of sql_mode variables.

    1
    mysql> SELECT @@sql_mode;

    You will find results like below screenshot. Make sure ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES are not there in the result.

    Disable Strict SQL Mode

    MySQL
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp

    Related Posts

    (Resolved) MySQL connection error: certificate verify failed

    How to Connect Python with MySQL Database

    Installing MySQL 8.0 on Amazon Linux 2

    How To Install MySQL 8 on Amazon Linux 2

    View 1 Comment

    1 Comment

    1. Ann on May 16, 2018 3:54 pm

      Thanks for sharing your knowledge, it was useful for me.

      Reply

    Leave A Reply Cancel Reply

    Advertisement
    Recent Posts
    • Error: EACCES: permission denied, scandir (Resolved)
    • How To Install Python 3.11 on Ubuntu 22.04 / 20.04
    • How to Install Python 3.11 on Amazon Linux 2
    • An Introduction to the “./configure” Command: Compiling Source Code in Linux
    • How to Install PHP 8.x on Pop!_OS
    Facebook Twitter Instagram Pinterest
    © 2023 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.