Showing entries 1 to 2
Displaying posts with tag: prepared statement (reset)
Handy stored procedure for regular DBA tasks

As a stored procedures fan, I use MySQL stored procedures to get some of my DBA tasks accomplished. To make it simple, I have a template stored procedure that can be customized for several purposes.
The template syntax contains cursor, continue handler, loop, if condition and prepared statement. Thought it may be useful for others – at least, who are searching for the MySQL Stored Procedure syntax – so I’m publishing this post!

Use case: Reorganize tables partition:

In MySQL Cluster, it is required to reorganize all NDB tables’ partitions after adding new data nodes to rebalance the data across all nodes. Also the tables need to be optimized afterwards to reclaim the memory space. For this task I use the following procedure:

DROP PROCEDURE IF EXISTS reorganize_tables;
DELIMITER //
CREATE PROCEDURE reorganize_tables (IN db_name VARCHAR(50))
BEGIN

[Read more]
Re-factoring some internals of prepared statements in 5.7

[ this is a re-posting of what I published on the MySQL server team blog a few days ago ]
 
When the MySQL server receives a SELECT query, the query goes through several consecutive phases:

  • parsing: SQL words are recognized, the query is split into different parts following the SQL grammar rules: a list of selected expressions, a list of tables to read, a WHERE condition, …
  • resolution: the output of the parsing stage contains names of columns and names of tables. Resolution is about making sense out of this. For example, in “WHERE foo=3“, “foo” is a column name without a table name; by applying SQL name resolution rules, we discover the table who contains “foo” (it can be complicated if subqueries or …
[Read more]
Showing entries 1 to 2