Somebody wanted to understand why you can backquote a single wildcard operator (that’s the underscore _ character) in MySQL, but can’t in Oracle. The answer is you can in Oracle when you know that you required an additional clause.

While I prefer using regular expression resolution, the LIKE operator is convenient. Here’s an example of backquoting an underscore in MySQL, where it looks for any string with an underscore anywhere in the string:

SELECT   common_lookup_type
FROM     common_lookup
WHERE    common_lookup_type LIKE '%\_%';

You can gain the same behavior in Oracle by appending the ESCAPE '\' clause, like this:

SELECT   common_lookup_type
FROM     common_lookup
WHERE    common_lookup_type LIKE '%\_%' ESCAPE '\';

The ESCAPE '\' clause is one of those Oracle details that often gets lost. It only works when the SQL*Plus ESCAPE …

You can use this MySQL function to generate names, (e-mail)addresses, phone numbers, urls, bit values, colors, IP address, etc.. As usual, the code is provided in a zip and the code is fully documented.

A student asked, “Can you successfully overload MySQL stored procedures, like PL/SQL does in stored packages?” I said, “MySQL doesn’t formally support overloading stored procedures, and doesn’t support packages like Oracle 11g. You can, however, mimic (or fake) overloading with prepared statements. It requires a careful combination of stored procedures and session variables.”

Unfortunately, they asked for an example. So I wrote this set of code. It uses queries as the dynamic statements to make it simple to test but you can substitute INSERT, UPDATE, or DELETE statements. I didn’t provide type validation in the example, which would be required for dates or date-timestamps.

It’s presented in steps with test programs at each level. If you see an opportunity to improve on any of the example components, leave a comment. As to whether I’d implement this in …

Cloud-powered BLOB type provides ACID guarantees and fast direct access to blobs via Web URLs.

Storing unstructured data

Typically unstructured data (such as pictures, media files, documents)

a) Is either stored on the file system, unlike the related with it relational data which is stored in the database. This is well known, “convenient” practice that allows fast access to files but offers no transactional story and no unified data management (for db and filesystem)

b) Or is stored in BLOBs. This ensures transactional consistency and reduces management complexities, but is really bad for performance and scalability.

We took advantage of the cloud, and came up with an upgrade to the BLOB – a solution that combines the benefits of the two.

Weblob data type

Weblob is a new data type that is supported by the Cloud Storage Engine for MySQL ( …

As a tool author, I really look forward to working with MySQL 5.6. Many of the improvements will make life significantly easier for Percona Toolkit.

One illustration of this is in figuring out what the optimizer is doing when it plans a statement, and how it intends to use indexes. Compound indexes present challenges in some situations. Many of our tools have extensive checks to try to avoid executing queries that have bad execution plans. If the optimizer intends to use only a few of the columns in an index, how will we know?

Of course I have answers for this, but they aren’t as simple as they seem at first glance. The obvious method is to look at key_len in EXPLAIN. But what is the length of the full index? That’s a tricky thing to figure out. The simple case is always easy, but character sets, nullability, and so on make it much harder.

Having a lot more information about the process the planner uses to choose an …

A coworker came to me with a perplexing issue. He wanted to know why these two queries were not returning the same results:

mysql> SELECT COUNT(*) 
    -> FROM parent
    -> WHERE id NOT IN (SELECT parent_id FROM child);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (7.84 sec)

mysql> SELECT COUNT(*)
    -> FROM parent p
    -> WHERE NOT EXISTS(SELECT 1 
    ->                  FROM child c
    ->                  WHERE p.id = c.parent_id);
+----------+
| count(*) |
+----------+
|     5575 |
+----------+
1 row in set (2.95 sec)

At first (and second, and third) glance these two queries look identical. It obviously is an exclusion join and because the MySQL optimizer is what it is, I decided to rewrite it as a LEFT JOIN to see what results came back:

mysql> SELECT …

I’ve been seeing a lot of interest in pt-online-schema-change (nonblocking MySQL schema changes), with a lively discussion on the mailing list (which I think I’m not keeping up with…) and a couple of bug reports filed. I’m really interested whether people have tested it rigorously to ensure that it maintains your data integrity. I have done so, and there is a set of tests for it in the codebase, but nothing replaces real-world testing. If you find any problems or have questions, please address them to the percona-discussion Google Group.

Further Reading:

• Planned change in Maatkit & Aspersa development
• …

Below you will find all code to create the table and the stored procedures to manage hierarchical trees in MySQL. All code is documented and can be downloaded in a zip file.

In (My)SQL, join is a means for combining records from two tables into a single set which can be either returned as is or used in another join. In order to perform the operation a join has to define the relationship between records in either table, as well as the way it will evaluate the relationship. The relationship itself is created through a set of conditions that are part of the join and usually are put inside ON clause. The rest is determined through a join type, which can either be an inner join or an outer join.

The SQL clauses that set the respective join type in a query are [INNER] JOIN and {LEFT | RIGHT} [OUTER] JOIN. As you can see the actual keywords INNER and OUTER are optional and can be omitted, however outer joins require specifying the direction – either left or right (more on that later).

Examples of queries using …

Setting the mode to STRICT_ALL_TABLES when you compile your stored procedure or function can prevent a lot of subtle bugs in your MySQL application. Consider this example: DELIMITER // CREATE FUNCTION test(p_first TINYINT, p_second TINYINT) RETURNS TINYINT BEGIN     DECLARE v_result TINYINT;     SET v_result := p_first + p_second;     RETURN v_result; END// DELIMITER ; compile it and then test the funcion: mysql> [...]