Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 7

Displaying posts with tag: prepared statements (reset)

Re-factoring some internals of prepared statements in 5.7
Employee_Team +5 Vote Up -0Vote Down

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 outer joins are involved).
  • optimization: finding the best way to read tables: the best order of tables, and for each
  [Read more...]
MySQL Performance Schema : Prepared Statements Instrumentation
Employee_Team +3 Vote Up -0Vote Down

MySQL 5.7.4 has a new Performance Schema feature, namely instrumentation for prepared statements. This instrumentation gives details of PREPARE and EXECUTE statistics for a prepared statement.

New Table
New table added to display run time statistics of Prepared Statements is named as prepared_statements_instances.

mysql> describe performance_schema.prepared_statements_instances;
+———————–+——————————————————
| Field                        | Type
+———————–+——————————————————
|




  [Read more...]
Notes on ALTER USER … PASSWORD EXPIRE
Employee +3 Vote Up -0Vote Down

I’ve been looking at the new ALTER USER … PASSWORD EXPIRE command as I try to implement a comprehensive password policy for MySQL 5.6.  There’s a few aspects of this feature that I found interesting, and thought others might benefit from what I’ve learned.  Here’s a quick summary:

You can use ALTER USER … PASSWORD EXPIRE in prepared statements as of 5.6.8-rc

This is important because there’s no other way to dynamically bind ALTER USER statements to a user name and host, which is necessary if you are trying to automate anything related to password policies.  This wasn’t the case with earlier 5.6 releases, but was fixed in 5.6.8-rc:

mysql> SELECT password_expired
-> FROM mysql.user
-> WHERE user = 'root' AND host = 'localhost';
+------------------+
| password_expired |
  [Read more...]
Who’s leaking prepared statements?
Employee +2 Vote Up -0Vote Down

In my last post, I described a specific problem with prepared statements into which PERFORMANCE_SCHEMA can give visibility.  That made me wonder whether PERFORMANCE_SCHEMA can also be used to identify other areas where prepared statements run into problems.  The most significant problem tends to be leakage of prepared statements.  This can inflate memory usage, both on the server and application side, and it’s not uncommon to find applications which fail to close prepared statements.

So the question is, what can PERFORMANCE_SCHEMA tell us about how connections close (or more importantly, fail to close) prepared statements?

At the most basic level, one can check the number of PREPARE statements executed compared to DEALLOCATE PREPARE, and you can do that using global status variables.  You’re shooting for general equality between

  [Read more...]
Prepared statement peculiarities (P_S to the rescue)
Employee +3 Vote Up -0Vote Down

Prepared statements have been with MySQL since version 4.1, including the protocol plumbing that helps support it.  What I didn’t realize – until a recent expedition through a general query log – is that the mysql command-line interface doesn’t implement the protocol commands that support this explicitly.  I came to this realization after observing a byproduct of this behavior.

The initial observation that triggered this exploration was noting that PREPARE and EXECUTE statements, when issued from the mysql command-line interface, result in two entries per command in the general query log:

6 Query    PREPARE stmt FROM 'SELECT RAND()'
6 Prepare    SELECT RAND()
6 Query    EXECUTE stmt
6 Execute    SELECT RAND()

Contrast this behavior with what is seen when a client sends COM_PREPARE and

  [Read more...]
On stored routines and dynamic statements
+4 Vote Up -0Vote Down

I very much enjoyed reading Overloading Procedures by Michael McLaughlin: good stuff!

I'm dealing with similar issues in common_schema/QueryScript, where I implement a whole new scripting language within MySQL, interpreted by stored routines. I am now finalizing the next version of common_schema/QueryScript, with a major addition to the scripting language to put yet even more power at the hands of the programmer/DBA using simple, clean syntax.

Still hush hush, the development of that feature touched at the very same issues described in Michael's post. Present in current release, these issues are intensified by the use and complexity of the new development. Here are a

  [Read more...]
Why You Want to Switch to MySQL 5.1
+0 Vote Up -0Vote Down

In two words: online operations. In a paragraph: Forget partitioning, row-based replication and events. The big reasons most people are going to salivate over 5.1, and probably start plans to upgrade now, are the online operations:

  • online ALTER TABLE for column rename, column default value change, and adding values to the end of an ENUM/SET
  • Online, table-based logging. No more need to restart your server to enable or change the general or slow query logs. You can have the standard file-based output or choose a table format…which you can query.
  • (more…)

Showing entries 1 to 7

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.