Putting the MySQL information_schema to Use
The MySQL information schema database was introduced in the September issue of the newsletter. We’ve seen that the information schema allows us to access metadata in a much more flexible way than the MySQL specific SHOW commands. We owe this to the fact that the information schema allows us to query and format metadata using arbitrary SELECT statements. Let’s explore this feature, and try to put that to good use
MySQL :: MySQL Enterprise for Amazon EC2:
Amazon EC2 is a web service that allows organizations to right size their computing capacity on demand using Amazon’s proven computing environment. Using MySQL Enterprise for Amazon EC2, developers can cost-effectively deliver web-scale database applications in the “cloud”, fully backed by the database experts at MySQL. Amazon EC2 and MySQL are a great fit for organizations that want to reduce the capital expenditures and operating costs required to build out and run their IT infrastructure. The benefits of MySQL Enterprise for Amazon for EC2 include:
Welcome to Tip #1 in MySQL Replication Series. In this tip we will go over what to do when you only want to replicate certain data to slaves. Most general way to tell what is replicated to which slave is to include following configuration directive in my.cnf file depending on your environment and your goals. We will start with slave server side options since you have more flexibility on slave on what to replicate and what not to.
For a given value of “natively”… | Carsten’s Random Ramblings
A quick (but somewhat simplistic) way to test performance of different methodologies is to turn off the query cache and use the BENCHMARK function. BENCHMARK loops over an expression a given number of times, and always returns 0, which is in itself quite uninteresting. But we can then look at the execution time to see how long things took.
I love it when the verbose expressions turn out wicked fast.
Colin Charles Agenda » Blog Archive » Compiling MySQL UDFs on Mac OS X
Compiling and installing a User Defined Function for MySQL on Mac OS X seems tricky. There are installation notes, but they seem to be sparse on OS X (the comments are clues, though).
[The Unexpected SQL Injection] Web Security Articles – Web Application Security Consortium
Abstract:
We will look at several scenarios under which SQL injection may occur, even though mysql_real_escape_string() has been used. There are two major steps at writing SQL injection resistant code: correct validation and escaping of input and proper use of the SQL syntax. Failure to comply with any of them may lead to compromise. Many of the specific issues are already known, but no single document mentions them all.
Although the examples are built on PHP/MySQL, the same principles apply to ASP/MSSQL and other combinations of languages and databases.
How to notify event listeners in MySQL at Xaprb
A high-performance application that has producers and consumers of some resource, such as a queue of messages, needs an efficient way to notify the consumers when the producer has inserted into the queue. Polling the queue for changes is not a good option. MySQL’s GET_LOCK() and RELEASE_LOCK() functions can provide both mutual exclusivity and notifications.
The Data Charmer: MySQL 5 general purpose routine library – II: Using arrays
Implementation
Arrays, as part of the MySQL General Purpose Stored Routines Library are implemented as datasets, all belonging to one table, and identified by a unique name. The details of the implementation are hiddenby the interface, and you should not worry a lot about it.
General concepts about the array library
Whenever possible, each routine is implemented both as a procedure and as a function. Only when a function returns an array value or an array property, then there is no corresponding procedure. This is a design choice, so that you are free of calling the routine in the most convenient way. When using a procedure that has a function synonym, a user variable with the same name of the procedure is set with the function return value. For instance, calling function array_create() you get the array ID.
How to implement a queue in SQL at Xaprb
This article explains how to create a fixed-size FIFO (first-in, first-out) queue in SQL, where rows added after a threshold will cause the oldest row to be deleted. There are several ways to do this, but MERGE on Oracle and DB2, and MySQL’s non-standard extensions to SQL, make an elegant solution easy.
Update a PostgreSQL blogger pointed out the obvious method I missed: triggers! There’s also a really neat PG-specific feature that allows it to work even more elegantly on that platform. Well worth a read. I sometimes wish I worked at a PostgreSQL shop so I could have time to learn as much about it as I’ve learned about MySQL.
Technical Notes and Articles of Interest » MySQL Backup & Recovery – Part 1
In order to copy your database, there are two different ways. The first is to shutdown your database first then copy your data files. Be sure to also copy your my.cnf file, this is important in a recovery process. The second is to use FLUSH command. For example.