The article recounts a MySQL production incident where an INT auto‑increment column reached its maximum value, causing insert failures, and walks through analysis, three remediation options, a stored‑procedure cleanup, a conversion to BIGINT, performance monitoring, and lessons on concurrency and schema design.
This article recounts a real‑world MySQL incident where the auto‑increment INT overflowed after billions of rows, explores why the ID limit was reached, evaluates three remediation strategies—including switching to BIGINT, distributed IDs, and sharding—and shares practical SQL scripts and performance insights.
This tutorial demonstrates how to use variables and loop constructs in MySQL and PostgreSQL stored procedures to iterate over a users table, filter records by age, and simulate sending a system welcome message, while comparing syntax differences between the two databases.
This tutorial explains why stored procedure parameters are essential, details the differences between IN, OUT, and INOUT parameters, and provides step‑by‑step MySQL examples—including environment setup, code snippets, execution results, common FAQs, and an advanced age‑checking exercise—to help developers write flexible, reusable procedures.
Learn how to create, execute, and troubleshoot a simple MySQL stored procedure using MySQL 8.0, covering delimiter changes, basic SQL syntax, result verification, common beginner mistakes, and extending the concept with parameterized examples.
This article explains how Java developers can replace thousands of individual UPDATE statements with a single PostgreSQL stored procedure call, covering the motivation, procedure creation, Java invocation, scheduling with shell scripts, performance benchmarks, and common pitfalls.
This article provides a comprehensive technical analysis of MSSQL attack vectors—including vulnerable stored procedures, COM automation, CLR exploitation, sandbox bypass, trigger abuse, proxy jobs, Kerberoasting, and linked servers—detailing prerequisites, step‑by‑step T‑SQL examples, mitigation recommendations, and overall impact on database security.
This article demonstrates how to create a test table, bulk‑insert two million rows via a stored procedure, convert the table to the MEMORY engine, and compare query performance against the original InnoDB table, highlighting speed gains, configuration tweaks, and practical usage considerations.
This article explains how to troubleshoot a slow OceanBase stored procedure by locating its trace_id, analyzing audit logs to identify non‑cached UPDATE statements, revealing that preceding TRUNCATE operations invalidate the plan cache, and finally recommending replacing TRUNCATE with DELETE to restore performance.
This article provides a comprehensive guide to MySQL stored procedures, functions, custom functions, variables, control‑flow statements, cursors, and condition/handler definitions, illustrating creation, invocation, inspection, modification, and deletion with concrete SQL examples.
This article explains how to design a SaaS system that isolates each subsidiary's data by adding a tenant_id column to a shared MySQL table, converting the table to LIST‑partitioned storage, and enforcing tenant‑level access through stored procedures and a MyBatis interceptor, complete with code examples and testing steps.
This article explains how to locate, trace, and resolve a syntax error that occurs when a stored procedure migrated from Oracle to OceanBase fails, detailing the use of trace IDs, SQL reconstruction, cursor analysis, and the necessary adjustments to primary‑key handling.
This guide explains MySQL stored procedure flow‑control constructs—including IF, CASE, LOOP, WHILE, REPEAT, LEAVE and ITERATE—detailing their syntax, usage patterns, and practical examples such as salary adjustments and conditional logic, helping developers implement complex database operations efficiently.
This article demonstrates how to convert a single‑tenant MySQL application into a multi‑tenant SaaS solution by adding a tenant_id column, using shared‑database partitioning, creating tenant tables, writing stored procedures for dynamic partition management, and implementing a MyBatis interceptor for hierarchical data permissions.
This article walks through converting a single‑tenant MySQL application into a SaaS solution by adding a tenant_id column, using shared‑database table partitioning, creating tenant metadata tables, writing stored procedures to add columns and partitions, and implementing a MyBatis interceptor for automatic data‑permission enforcement.
This guide explains MySQL stored procedures, covering their definition, benefits, parameter types, creation syntax, delimiter handling, multiple practical examples, calling conventions, debugging tips, and hands‑on exercises, enabling developers to efficiently write, test, and manage reusable SQL routines.
This article details a practical approach to handling massive billing data by dynamically creating monthly tables in MySQL via stored procedures, integrating them with a SpringBoot‑MyBatis backend, and providing APIs for insertion, serial‑number lookup, and time‑range queries, complete with code examples and operational tips.
This article investigates why Oracle and OceanBase return different results when querying the USER_TAB_COLUMNS view in stored procedures, demonstrates reproducible tests, analyzes system view behavior, and proposes workarounds such as using ALL_TAB_COLUMNS, creating synonyms, intermediate tables, or materialized views to ensure consistent table name retrieval.
This article presents a hands‑on walkthrough of implementing in‑database horizontal partitioning by month for a high‑volume billing table, covering the business background, design decisions, stored‑procedure table creation, dynamic table selection in MyBatis, and query strategies such as serial‑number lookup and date‑range retrieval.
This article presents a practical guide to implementing monthly sharding for high‑volume billing data using MySQL stored procedures, dynamic table creation, and SpringBoot‑MyBatis integration, covering schema design, code examples, and query strategies for inserting, retrieving, and managing partitioned tables.
This guide explains how to use MySQL stored procedures, events (timers), and triggers—including their creation, variable handling, control structures, parameter passing, loops, scheduling options, and practical examples—to automate reporting, data monitoring, and business logic directly at the database layer.
An in-depth case study describes how to dramatically improve the performance of a massive MES reporting query—reducing execution from over half an hour to seconds—by analyzing inefficient SQL, adding proper indexes, avoiding full table scans, redesigning with stored procedures, temporary tables, and selective use of NOLOCK.
This article explains how to replace shell‑script based lock‑wait monitoring with MySQL Event and Stored Procedure mechanisms, showing step‑by‑step creation of a monitoring database, procedure, scheduled event, and how to interpret the generated lock‑wait log table for troubleshooting.
This article explains how to prevent duplicate order numbers in ERP systems under high concurrency by creating a dedicated table and a MySQL stored procedure that generates, validates, inserts, and returns a uniquely formatted order identifier.
This article explains how MySQL 8.0’s JSON data type enables range‑based traversal of JSON arrays, demonstrates extracting array elements with path expressions, and provides stored‑procedure examples—both a traditional loop version and an optimized version using the new "$[m to n]" syntax.
The article details how a half‑hour SSRS report query on a massive MES database was transformed into a sub‑second operation by analyzing the original SQL, eliminating full table scans, adding proper indexes, using temporary tables and a well‑designed stored procedure, while also discussing common pitfalls and best‑practice tips for high‑performance database querying.
The article describes how a massive MES reporting query that took over half an hour to run was analyzed, re‑engineered with proper indexing, partitioning considerations, and a comprehensive stored‑procedure redesign, resulting in sub‑second response times for both backend analysis and front‑end B/S applications.
This article details how to transform a painfully slow SSRS report that scanned billions of rows into a high‑performance solution by analyzing the original SQL, fixing indexing and partitioning mistakes, and rewriting the logic as a flexible stored procedure that runs in seconds.
The author describes how they transformed a sluggish SSRS report that took over thirty minutes to run into a fast, sub‑second query by analyzing the original SQL, adding missing indexes, avoiding full table scans, and rewriting the logic as a flexible stored procedure, complete with code examples and performance tips.
This article provides a concise overview of relational database fundamentals, covering key terminology, the structure of SQL statements, essential DDL/DML/TCL/DCL commands, query operators, joins, subqueries, functions, indexing, constraints, transactions, permissions, stored procedures, cursors, and triggers, with practical code examples throughout.
This article explains how DBLE's hint mechanism can be used to specify database routing, classify different hint types, and demonstrate practical operations such as invoking stored procedures and executing custom SQL statements on selected nodes.
This comprehensive guide covers fundamental SQL Server operations such as creating databases, tables, indexes, and views, advanced techniques like set operators, subqueries, and linked servers, plus step‑by‑step instructions for replication, backup, compression, and automated synchronization using stored procedures and SQL Agent jobs.
A detailed case study shows how a DELETE statement executed inside a MySQL stored procedure can switch binlog format to ROW, causing billions of bitmap operations on a partitioned table and resulting in severe master‑slave replication delay, and presents three practical solutions.
This article demonstrates how to use MySQL's stacked diagnostics and a custom stored procedure to automatically capture and log errors from bulk INSERT statements during Oracle‑to‑MySQL migration, enabling quick identification and correction of problematic rows without manual inspection.
This article details a multi‑stage, file‑less attack that leveraged weak SQL Server credentials, Transact‑SQL stored procedures, and WMI to download and execute a downloader (cabs.exe) which fetched multiple botnet components, and explains the forensic steps and remediation measures taken to eradicate the threat.
This guide walks through downloading and installing Redis, adding Java dependencies, creating a Redis DAO with Protostuff serialization, configuring Spring beans, caching seckill data, implementing a MySQL stored procedure for atomic execution, mapping it with MyBatis, updating service and controller layers, and testing the entire workflow.
A large‑scale delete operation in a MySQL PXC cluster caused transaction rollback, table locks, and flow control, leading to service slowdown, and was resolved by redesigning the cleanup mechanism with partitioned tables, stored procedures, and scheduled events.
This article explains why a large‑transaction update caused a binlog overflow and slave I/O failure in a remote MySQL master‑slave setup, and provides step‑by‑step commands and a stored‑procedure solution to restore replication safely.
A .NET MVC warehouse system’s MySQL view was taking over 30 seconds to fetch 70,000 inventory records, but by adding a composite index on the dictionary table, removing a UNION ALL, and adjusting view algorithms, the query time dropped to a few seconds, with a stored‑procedure alternative for flexible use.
This article analyzes a poorly performing MES reporting SQL query that scans billions of rows, identifies indexing and partitioning shortcomings, and proposes a redesign using stored procedures, temporary tables, and query‑writing best practices to achieve sub‑second response times.
This article explains a practical PL/SQL stored‑procedure technique for bulk‑deleting large tables by batching rows, using rowid partitioning, off‑peak execution, and short pauses to minimize impact on Oracle database performance.
This guide explains why a numeric column turned null, compares using the volatile v$sql view with a permanent logging solution, and details step‑by‑step how to create a tablespace, logging table, stored procedure, and scheduled job to capture all Oracle SQL statements.
This article explains practical methods for importing various external data formats—such as CSV, plain text, XML, and JSON—into MySQL, covering command-line utilities, LOAD DATA statements, stored procedures, third‑party tools like mysqljsonimport, and using MySQL Workbench, while addressing configuration and security considerations.
This guide demonstrates how to create a linked server to Active Directory in SQL Server, query user and group information via T‑SQL, and store the results in database tables for further analysis with TFS data.
