{"id":8239,"date":"2024-09-20T23:32:02","date_gmt":"2024-09-20T18:02:02","guid":{"rendered":"https:\/\/pheonixsolutions.com\/blog\/?p=8239"},"modified":"2024-09-20T23:32:54","modified_gmt":"2024-09-20T18:02:54","slug":"how-to-disable-strict_trans_tables-in-mysql","status":"publish","type":"post","link":"https:\/\/pheonixsolutions.com\/blog\/how-to-disable-strict_trans_tables-in-mysql\/","title":{"rendered":"How to Disable STRICT_TRANS_TABLES in MySQL"},"content":{"rendered":"\n

Introduction<\/strong><\/p>\n\n\n\n

STRICT_TRANS_TABLES is one of the SQL modes in MySQL that enforces strict data validation rules. When STRICT_TRANS_TABLES is enabled, MySQL rejects invalid values and generates an error, rather than converting them. If a value is missing or invalid, the entire INSERT or UPDATE query is aborted, and no data is inserted. If STRICT_TRANS_TABLES is disabled, MySQL attempts to adjust invalid values to the closest valid ones or uses default values for missing data, allowing the query to succeed but potentially inserting incorrect or unintended values.<\/p>\n\n\n\n

Prerequisites<\/strong><\/p>\n\n\n\n

    \n
  1. A sudo privileged SSH user credential<\/li>\n\n\n\n
  2. Mysql “root” or any admin-privileged credential<\/li>\n<\/ol>\n\n\n\n

    Implementation<\/strong><\/p>\n\n\n\n

    Step 1: Log in to the server via SSH<\/p>\n\n\n\n

    $ ssh username@IP<\/p>\n\n\n\n

    Step 2: Connect to MySQL via root user or any admin-privileged<\/p>\n\n\n\n

    $ mysql -u <username> -p<\/p>\n\n\n\n

    Step 3: List the active SQL modes and check whether the “STRICT_TRANS_TABLES” in included or not. If it included then we can consider that it is enabled<\/p>\n\n\n\n

    \n

    >SELECT @@GLOBAL.sql_mode;<\/p>\n\n\n\n

    \"\"<\/a><\/figure>\n<\/blockquote>\n\n\n\n

    Step 4: To disable STRICT_TRANS_TABLES<\/code> temporarily by updating the SQL mode for the current session or globally until the server restarts<\/p>\n\n\n\n

    (I) For the current session<\/p>\n\n\n\n

    >SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, ‘STRICT_TRANS_TABLES’, ”);<\/p>\n\n\n\n

    (II) For the global session (until restart)<\/p>\n\n\n\n

    >SET GLOBAL sql_mode = REPLACE(@@GLOBAL.sql_mode, ‘STRICT_TRANS_TABLES’, ”);<\/p>\n\n\n\n

    Step 5: To permanently disable the “STRICT_TRANS_TABLES<\/code>“, edit the MySQL configuration. Navigate to the [mysqld]<\/code> section and remove STRICT_TRANS_TABLES<\/code> from the list of SQL modes. <\/p>\n\n\n\n

    $ vi \/etc\/mysql\/mysql.conf.d<\/code><\/p>\n\n\n\n

    \"\"<\/a><\/figure>\n\n\n\n

    Step 6: Restart the MySQL service<\/p>\n\n\n\n

    $ systemctl restart mysql<\/p>\n\n\n\n

    Conclusion<\/strong><\/p>\n\n\n\n

    Disabling STRICT_TRANS_TABLES<\/code> can help resolve strict data handling issues for applications requiring more flexible data insertion rules.<\/p>\n\n\n\n

    <\/p>\n","protected":false},"excerpt":{"rendered":"

    Introduction STRICT_TRANS_TABLES is one of the SQL modes in MySQL that enforces strict data validation rules. When STRICT_TRANS_TABLES is enabled, MySQL rejects invalid values and generates an error, rather than converting them. If a value is missing or invalid, the entire INSERT or UPDATE query is aborted, and no data… Continue Reading How to Disable STRICT_TRANS_TABLES in MySQL<\/span><\/i><\/a><\/p>\n","protected":false},"author":495,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[387],"tags":[988],"class_list":{"0":"post-8239","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"hentry","6":"category-mysql-database","7":"tag-strict_trans_tables-in-mysql","8":"h-entry","10":"h-as-article"},"yoast_head":"\nPheonix Solutions - We Empower Your Business Growth<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/pheonixsolutions.com\/blog\/how-to-disable-strict_trans_tables-in-mysql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Pheonix Solutions - We Empower Your Business Growth\" \/>\n<meta property=\"og:description\" content=\"Introduction STRICT_TRANS_TABLES is one of the SQL modes in MySQL that enforces strict data validation rules. When STRICT_TRANS_TABLES is enabled, MySQL rejects invalid values and generates an error, rather than converting them. If a value is missing or invalid, the entire INSERT or UPDATE query is aborted, and no data… Continue Reading How to Disable STRICT_TRANS_TABLES in MySQL\" \/>\n<meta property=\"og:url\" content=\"https:\/\/pheonixsolutions.com\/blog\/how-to-disable-strict_trans_tables-in-mysql\/\" \/>\n<meta property=\"og:site_name\" content=\"PHEONIXSOLUTIONS\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/PheonixSolutions-209942982759387\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-09-20T18:02:02+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-09-20T18:02:54+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/pheonixsolutions.com\/blog\/wp-content\/uploads\/2016\/09\/PX2.png\" \/>\n\t<meta property=\"og:image:width\" content=\"3837\" \/>\n\t<meta property=\"og:image:height\" content=\"2540\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"meenakshi k\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@pheonixsolution\" \/>\n<meta name=\"twitter:site\" content=\"@pheonixsolution\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"meenakshi k\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/how-to-disable-strict_trans_tables-in-mysql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/how-to-disable-strict_trans_tables-in-mysql\\\/\"},\"author\":{\"name\":\"meenakshi k\",\"@id\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/#\\\/schema\\\/person\\\/d573de3a2c02cbeca49e90581a88b98f\"},\"headline\":\"How to Disable STRICT_TRANS_TABLES in MySQL\",\"datePublished\":\"2024-09-20T18:02:02+00:00\",\"dateModified\":\"2024-09-20T18:02:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/how-to-disable-strict_trans_tables-in-mysql\\\/\"},\"wordCount\":278,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/#organization\"},\"keywords\":[\"STRICT_TRANS_TABLES in MySQL\"],\"articleSection\":[\"Mysql\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/how-to-disable-strict_trans_tables-in-mysql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/how-to-disable-strict_trans_tables-in-mysql\\\/\",\"url\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/how-to-disable-strict_trans_tables-in-mysql\\\/\",\"name\":\"Pheonix Solutions - We Empower Your Business Growth\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/#website\"},\"datePublished\":\"2024-09-20T18:02:02+00:00\",\"dateModified\":\"2024-09-20T18:02:54+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/how-to-disable-strict_trans_tables-in-mysql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/how-to-disable-strict_trans_tables-in-mysql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/how-to-disable-strict_trans_tables-in-mysql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Disable STRICT_TRANS_TABLES in MySQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/\",\"name\":\"Pheonix Solutions\",\"description\":\"We Empower Your Business Growth\",\"publisher\":{\"@id\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/#organization\",\"name\":\"PheonixSolutions\",\"url\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/wp-content\\\/uploads\\\/2016\\\/12\\\/logo.png\",\"contentUrl\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/wp-content\\\/uploads\\\/2016\\\/12\\\/logo.png\",\"width\":454,\"height\":300,\"caption\":\"PheonixSolutions\"},\"image\":{\"@id\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/PheonixSolutions-209942982759387\\\/\",\"https:\\\/\\\/x.com\\\/pheonixsolution\"]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/#\\\/schema\\\/person\\\/d573de3a2c02cbeca49e90581a88b98f\",\"name\":\"meenakshi k\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/17c0a3923b9a31a41d8de2fdc928e514efe879fe17b6e5d2814b7f81ed2356e5?s=96&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/17c0a3923b9a31a41d8de2fdc928e514efe879fe17b6e5d2814b7f81ed2356e5?s=96&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/17c0a3923b9a31a41d8de2fdc928e514efe879fe17b6e5d2814b7f81ed2356e5?s=96&r=g\",\"caption\":\"meenakshi k\"},\"url\":\"https:\\\/\\\/pheonixsolutions.com\\\/blog\\\/author\\\/meenakshi\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Pheonix Solutions - We Empower Your Business Growth","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/pheonixsolutions.com\/blog\/how-to-disable-strict_trans_tables-in-mysql\/","og_locale":"en_US","og_type":"article","og_title":"Pheonix Solutions - We Empower Your Business Growth","og_description":"Introduction STRICT_TRANS_TABLES is one of the SQL modes in MySQL that enforces strict data validation rules. When STRICT_TRANS_TABLES is enabled, MySQL rejects invalid values and generates an error, rather than converting them. If a value is missing or invalid, the entire INSERT or UPDATE query is aborted, and no data… Continue Reading How to Disable STRICT_TRANS_TABLES in MySQL","og_url":"https:\/\/pheonixsolutions.com\/blog\/how-to-disable-strict_trans_tables-in-mysql\/","og_site_name":"PHEONIXSOLUTIONS","article_publisher":"https:\/\/www.facebook.com\/PheonixSolutions-209942982759387\/","article_published_time":"2024-09-20T18:02:02+00:00","article_modified_time":"2024-09-20T18:02:54+00:00","og_image":[{"width":3837,"height":2540,"url":"https:\/\/pheonixsolutions.com\/blog\/wp-content\/uploads\/2016\/09\/PX2.png","type":"image\/png"}],"author":"meenakshi k","twitter_card":"summary_large_image","twitter_creator":"@pheonixsolution","twitter_site":"@pheonixsolution","twitter_misc":{"Written by":"meenakshi k","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/pheonixsolutions.com\/blog\/how-to-disable-strict_trans_tables-in-mysql\/#article","isPartOf":{"@id":"https:\/\/pheonixsolutions.com\/blog\/how-to-disable-strict_trans_tables-in-mysql\/"},"author":{"name":"meenakshi k","@id":"https:\/\/pheonixsolutions.com\/blog\/#\/schema\/person\/d573de3a2c02cbeca49e90581a88b98f"},"headline":"How to Disable STRICT_TRANS_TABLES in MySQL","datePublished":"2024-09-20T18:02:02+00:00","dateModified":"2024-09-20T18:02:54+00:00","mainEntityOfPage":{"@id":"https:\/\/pheonixsolutions.com\/blog\/how-to-disable-strict_trans_tables-in-mysql\/"},"wordCount":278,"commentCount":0,"publisher":{"@id":"https:\/\/pheonixsolutions.com\/blog\/#organization"},"keywords":["STRICT_TRANS_TABLES in MySQL"],"articleSection":["Mysql"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/pheonixsolutions.com\/blog\/how-to-disable-strict_trans_tables-in-mysql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/pheonixsolutions.com\/blog\/how-to-disable-strict_trans_tables-in-mysql\/","url":"https:\/\/pheonixsolutions.com\/blog\/how-to-disable-strict_trans_tables-in-mysql\/","name":"Pheonix Solutions - We Empower Your Business Growth","isPartOf":{"@id":"https:\/\/pheonixsolutions.com\/blog\/#website"},"datePublished":"2024-09-20T18:02:02+00:00","dateModified":"2024-09-20T18:02:54+00:00","breadcrumb":{"@id":"https:\/\/pheonixsolutions.com\/blog\/how-to-disable-strict_trans_tables-in-mysql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/pheonixsolutions.com\/blog\/how-to-disable-strict_trans_tables-in-mysql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/pheonixsolutions.com\/blog\/how-to-disable-strict_trans_tables-in-mysql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/pheonixsolutions.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to Disable STRICT_TRANS_TABLES in MySQL"}]},{"@type":"WebSite","@id":"https:\/\/pheonixsolutions.com\/blog\/#website","url":"https:\/\/pheonixsolutions.com\/blog\/","name":"Pheonix Solutions","description":"We Empower Your Business Growth","publisher":{"@id":"https:\/\/pheonixsolutions.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/pheonixsolutions.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/pheonixsolutions.com\/blog\/#organization","name":"PheonixSolutions","url":"https:\/\/pheonixsolutions.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/pheonixsolutions.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/pheonixsolutions.com\/blog\/wp-content\/uploads\/2016\/12\/logo.png","contentUrl":"https:\/\/pheonixsolutions.com\/blog\/wp-content\/uploads\/2016\/12\/logo.png","width":454,"height":300,"caption":"PheonixSolutions"},"image":{"@id":"https:\/\/pheonixsolutions.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/PheonixSolutions-209942982759387\/","https:\/\/x.com\/pheonixsolution"]},{"@type":"Person","@id":"https:\/\/pheonixsolutions.com\/blog\/#\/schema\/person\/d573de3a2c02cbeca49e90581a88b98f","name":"meenakshi k","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/17c0a3923b9a31a41d8de2fdc928e514efe879fe17b6e5d2814b7f81ed2356e5?s=96&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/17c0a3923b9a31a41d8de2fdc928e514efe879fe17b6e5d2814b7f81ed2356e5?s=96&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/17c0a3923b9a31a41d8de2fdc928e514efe879fe17b6e5d2814b7f81ed2356e5?s=96&r=g","caption":"meenakshi k"},"url":"https:\/\/pheonixsolutions.com\/blog\/author\/meenakshi\/"}]}},"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7F4uM-28T","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/8239","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/users\/495"}],"replies":[{"embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/comments?post=8239"}],"version-history":[{"count":0,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/8239\/revisions"}],"wp:attachment":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/media?parent=8239"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/categories?post=8239"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/tags?post=8239"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}