Tagged articles
sql-mode
20 articles · Page 1 of 1
This tutorial demonstrates MySQL 8.0 configuration of character sets, collations, time zones, and SQL modes through reproducible experiments, showing how connection settings differ from server defaults, how utf8mb4 handles emojis, why TIMESTAMP converts across time zones while DATETIME does not, and how strict SQL mode rejects out-of-range values.
MySQLcharacter-setcollation
0 likes · 20 min read

This comprehensive guide details 20 critical technical checks for migrating from MySQL 5.7 to 8.0, covering authentication plugins, character sets, reserved words, strict SQL modes, replication setup, backup validation, and cutover procedures with executable SQL and bash examples.
AuthenticationDatabase MigrationMySQL
0 likes · 23 min read

MySQL silently resolves missing column names in subqueries to outer scope columns, turning filters into correlated subqueries that always evaluate true, causing full-table updates or deletes without errors or warnings; no sql_mode prevents this behavior.
Best PracticesMySQLcolumn resolution
0 likes · 10 min read

The article details a MySQL 5.7 bug where rollback SQL generated for a JSON NOT NULL column produces an empty string instead of NULL, explains the underlying binlog behavior, reproduces the issue with sample tables, and provides a practical workaround by converting the JSON column to TEXT before re‑importing the data, while recommending strict SQL_MODE for production.
JSONMySQLdatabase troubleshooting
0 likes · 7 min read

This tutorial walks through preparing the environment, installing and uninstalling MySQL on CentOS 7, configuring firewalls, setting up remote access, handling character sets, adjusting security policies, managing SQL modes, and best practices for case sensitivity and version‑specific password policies.
ConfigurationLinuxMySQL
0 likes · 30 min read

This article explains MySQL's SQL_MODE system variable, its default values for different versions, common configuration options, how it influences data validation and migration, and provides practical code examples for adjusting and testing SQL_MODE settings.
Database MigrationMySQLSQL Settings
0 likes · 13 min read

This article explains the MySQL sql_mode setting, details each of its ten modes—including STRICT_TRANS_TABLES, STRICT_ALL_TABLES, and ONLY_FULL_GROUP_BY—provides a command to view the current mode, and also includes a brief promotion for a PHP training class.
Database ConfigurationDatabase SettingsMySQL
0 likes · 4 min read

This article walks through a step‑by‑step investigation of why incremental backups failed on a DBaaS platform, revealing that the PERCONA_SCHEMA.xtrabackup_history table’s binlog_pos column was too short, and shows how altering its length resolves the problem.
Backup FailureMySQLPercona XtraBackup
0 likes · 10 min read

This article explains how MySQL determines default values for TIMESTAMP columns, the effect of the explicit_defaults_for_timestamp parameter, when zero‑date values are permitted via sql_mode, and demonstrates the behavior through four test scenarios with corresponding SQL statements and results.
MySQLdefault valueexplicit_defaults_for_timestamp
0 likes · 5 min read

This article explains MySQL's SQL Mode concept, lists the default mode values for MySQL 5.7, demonstrates how to view and change the mode with SQL commands, describes the impact of common modes such as STRICT_TRANS_TABLES and ONLY_FULL_GROUP_BY, and provides a complete reference table of all supported mode values and predefined mode combinations.
MySQLSQLdata validation
0 likes · 10 min read

This article outlines a practical method for migrating MySQL tables with millions of rows by exporting data to an outfile, compressing, transferring, and loading it on the target server, while also addressing strict SQL mode errors that can arise during import.
LOAD DATALarge TablesMySQL
0 likes · 2 min read

This comprehensive guide walks through MySQL's advanced concepts—including lock types, transaction control, SQL security, mode settings, regular expressions, common tricks, case sensitivity, foreign‑key support, and a wide range of built‑in functions—providing clear examples and practical commands for each topic.
LocksMySQLTransactions
0 likes · 23 min read

This tutorial walks through MySQL's transaction control, lock statements, autocommit settings, manual commit and rollback, savepoints, differences between transactional and non‑transactional tables, SQL mode configurations, common SQL tricks, regular expressions, and a comprehensive catalog of built‑in functions, all illustrated with step‑by‑step examples and code snippets.
MySQLSavepointTransactions
0 likes · 25 min read


ITPUBJan 26, 2021 · Databases
This guide walks through MySQL's advanced features, covering lock types, transaction control commands, autocommit handling, savepoints, SQL mode configuration, regular expression usage, common SQL tricks, and a comprehensive overview of built‑in functions with practical code examples.
LocksMySQLTransactions
0 likes · 25 min read

This article provides a comprehensive guide to advanced MySQL concepts, covering transaction control, various lock levels, lock and unlock statements, autocommit settings, savepoints, SQL security issues, SQL mode configurations, regular expression usage, and a collection of useful string, numeric, date‑time, and flow functions with practical examples.
MySQLfunctionsregular expressions
0 likes · 23 min read

This article explains how to troubleshoot and fix the MySQL 5.7 ONLY_FULL_GROUP_BY error in a Laravel application by inspecting sql_mode, understanding Laravel's strict mode behavior, and customizing the framework's mode configuration to remove the problematic setting without changing global MySQL settings.
Database ConfigurationLaravelONLY_FULL_GROUP_BY
0 likes · 4 min read

This article explains the SQL standard restriction that only grouped keys, aggregate functions, or constants may appear in the SELECT clause of a GROUP BY query, demonstrates the resulting error in MySQL, and discusses how SQL modes like ONLY_FULL_GROUP_BY enforce this rule.
MySQLSQLaggregation
0 likes · 11 min read

This article explains the SQL standard limits on SELECT columns after GROUP BY, demonstrates how MySQL's ONLY_FULL_GROUP_BY mode enforces those limits, explores various SQL modes, and clarifies why aggregated queries cannot reference non‑grouped columns directly.
MySQLONLY_FULL_GROUP_BYSQL
0 likes · 11 min read

This article outlines the practical experience of upgrading MySQL from version 5.7 to 8.0, detailing version requirements, the specific upgrade tasks, step‑by‑step procedures, and important considerations such as deprecated sql_mode settings, character set handling, and lower_case_table_names consistency.
Best PracticesMySQLUpgrade
0 likes · 7 min read

This article reviews the most important MySQL 5.7 enhancements—including stronger authentication, password expiration, account locking, simplified SSL, safer initialization, updated SQL_MODE, online DDL improvements, CJK full‑text support, temporary‑table optimizations, spatial data types, and NVM‑specific tweaks—providing practical guidance and configuration examples for DBAs.
5.7Database SecurityMySQL
0 likes · 11 min read
