As of January 2008, the Pipelines will no longer publish monthly tips. However, we welcome and will post user-submitted tips on this page. Please send your tips to webmaster@quest-pipelines.com.

Our archive of monthly tips will be maintained, so feel free to browse them below.

2006 Tips
2005 Tips
2004 Tips

January's Tip of the Month

Connecting to MySQL From Python
By Edmon Begoli, Reprinted from IT Toolbox Blogs
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=50701

This Python routine demonstrates how to connect to MySQL from Python. I use it for semantic web applications.

Prerequisites:

• Python installation
   
• MySQLdb library - you can find it at: http://sourceforge.net/projects/mysql-python
   
• I also use Eclipse as an IDE with pydev plug in

Routine: connects to the local MySQL on the default port 3306. For other ports you would need to specify the port="XXXX" in the connection string.

For testing purposes it select all locations from the onco schema, and

prints first two columns:

#!/usr/bin/python
# e begoli, python connector for mysql
# import MySQL module
import MySQLdb

# connect
db = MySQLdb.connect(host="localhost", user="appuser", passwd="",
db="onco")

# create a database cursor
cursor = db.cursor()

# execute SQL select statement
cursor.execute("SELECT * FROM LOCATION")

# get the number of rows in the resultset
numrows = int(cursor.rowcount)

# get and display one row at a time
for x in range(0,numrows):
row = cursor.fetchone()
print row[0], "-->", row[1]

February's Tip of the Month

Triggers and Mutating Table Errors in MySQL
By Scott Noyes, Reprinted from SearchOpenSource.com
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=50702

Since triggers are still in the early stages of development for MySQL, users may find the number of limitations restrictive.

What are the differences between statement triggers and row triggers?

Statement triggers "fire" once for each statement no matter how many rows that statement affects. Row triggers act for each row affected. A statement like "UPDATE myTable SET myField = 1" would fire a statement trigger only once. That same statement would cause a row trigger to fire as many times as there are rows in the table. MySQL currently only supports row triggers.

What are mutating table errors?

Mutating table errors occur when a trigger attempts to access a "mutating" table, like the table from which the trigger has been called. In MySQL, triggers are not initiated by cascading foreign keys, and they cannot modify the table from which they are called, so neither of those issues can cause a mutating table error. However, foreign keys appear to operate before 'AFTER' triggers, so it is possible to create a trigger that will cause a statement to fail.

Which supports the most triggers, Microsoft SQL Server, Oracle or MySQL? How should that affect my decision in regards to choosing a database?

Triggers are fairly new to MySQL and are, therefore, still in an early stage of development. There are a number of limitations you might find too restrictive if you rely heavily on triggers for your application. All three databases support row triggers before and after insert, update and delete statements. Oracle and Microsoft SQL Server also offer statement triggers.

MySQL allows, at most, one trigger per table per event time and type -- a table can only have one BEFORE INSERT trigger, for example. Both Orable and SQL Server permit multiple triggers of each type on a table. The order in which they execute may not be defined.

MySQL requires each of the six possible triggers to be defined separately. If you want to perform the exact same action on INSERT or on DELETE, you must define two separate triggers. Oracle and SQL Server allow trigger definitions to include multiple event types, such as BEFORE INSERT, and DELETE.

MySQL does not permit dynamic SQL in a trigger, or in a stored procedure called by a trigger. There is no such restriction in Oracle.

Both MySQL and Oracle attempt to prevent mutating table errors where, for example, a trigger attempts to modify the table which called it. MySQL and Oracle try to stop this by preventing such triggers from being created. It is still possible to create such errors, using cascading foreign keys or by other means. SQL Server allows for recursive triggers, and so must be coded carefully to prevent endless recursion.

How does dual licensing affect the use of MySQL in enterprise IT environments? Is there anything I need to be aware of?

MySQL Community edition is free to use, whether you are a home user or a billion-dollar corporation. You can store your data in it, serve Web pages from it and build reports that rely on it, all without paying a dime. However, if you plan to sell a product that requires MySQL to function, or a product with built-in functionality to use MySQL, you must buy a commercial license. Of course, if you want official support, you'll need to pay for that too.

March's Tip of the Month

MySQL Error Control Changes
By Peter Zaitsev, Reprinted from Planet MySQL
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=50703

In MySQL 5.0, error control was mostly improved. For example, strict mode was added to change the infamous MySQL behavior of cutting overly large strings and numbers and allowing you to use dates such as February 31st.

In one case, however, there was a reversal - in regards to storage engine initialization. Previously, if you started MySQL and the Innodb storage engine failed to initialize (i.e. you resized the log file but forgot to delete old ones) the MySQL Server simply would not start. In MySQL 5.0, however, it will continue loading and simply have the Innodb storage engine disabled.

This can cause numerous problems especially if you got used to the old behavior and do not check MySQL logs but simply check that it is started. It especially hurts if you have only some tables in Innodb so you might not notice that part of your application does not function. Also monitoring often monitors that MySQL is up and running and will not query that all tables are accessible, thus not catching such errors. If MySQL is started with Innodb tables disabled, accesses to Innodb tables will simply result in errors.

The other way I've seen this beating up people is performing Innodb conversion. By default MySQL substitutes storage engines so if Innodb is disabled MyISAM is used, so I've seen people thinking they have converted things to Innodb while they really did not because it was substituted back to MyISAM.

April's Tip of the Month

Testing Queries in MySQL
By Peter J. Farrell, Reprinted from Shuttle.Space
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=50704

Let's say you want to test your query, but don't actually want it to change your data? Then use the LIMIT clause available in MySQL and set it O.

UPDATE admins SET
l_name = 'Farrellll'
WHERE l_name = 'Farrell'
LIMIT 0

The above code would run -- so if there were any problems with it it would fail, but it wouldn't actually update anything because I've told MySQL to limit the total number of affected records to 0. I learned this little trick from the LIMIT optimization page in the MySQL documentation. Thought I would blog it since I never heard using the LIMIT clause in this way and I think it might very useful for some people.

May's Tip of the Month

Using the EXPLAIN Command
By Justin Silverton, Reprinted from http://www.whenpenguinsattack.com
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=50705

The explain command can tell you which indexes are used with the specified query and many other pieces of useful information that can help you choose a better index or query.

Example of usage:

explain select * from table

Explanation of row output:

• table - The name of the table.
   
• type - The join type, of which there are several.
   
• possible_keys - This column indicates which indexes MySQL could use to find the rows in this table. If the result is NULL, no indexes would help with this query. You should then take a look at your table structure and see whether there are any indexes that you could create that would increase the performance of this query.
   
• key - The key actually used in this query, or NULL if no index was used.
   
• key_len - The length of the key used, if any.
   
• ref - Any columns used with the key to retrieve a result.
   
• rows - The number of rows MySQL must examine to execute the query.
   
• extra - Additional information regarding how MySQL will execute the query. There are several options, such as Using index (an index was used) and Where (a WHERE clause was used).

June's Tip of the Month

Connecting MySQL to ASP Pages
By Scott Noyes, Reprinted from SearchOpenSource.com
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=50706

You can connect ASP pages to MySQL using ODBC. Download the ODBC drivers. Once you have MyODBC configured, you can connect to MySQL with or without DSN.

Using DSN:
<%
set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN=mysql_dsn"
%>

Without DSN:
<%
set conn = createobject("ADODB.Connection") 
 conn.open = "DRIVER={MySQL ODBC 3.51 Driver};" & _
 "SERVER=yourHostName;" & _
 "DATABASE=yourDBName;" & _
 "UID=yourUserName;PWD=yourPassword; OPTION=35;"
%>

Check for values for the OPTION parameter.

July's Tip of the Month

Tips for MySQL Using InnoDB Table Engine
By Peter J. Farrell, Reprinted from Shuttle.Space
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=50707

I was doing some research on MySQL found a great video from Google EngEDU in which Jay Pipes from MySQL gives a great Best Practices presentation. Here are a couple of small things I didn't know about MySQL using the InnoDB table engine.

Redundant Indexes

InnoDB automatically "appends" the primary key value on all indexes that are not the primary key index. For example, if you have an index for the product_name field and product_id (primary key), InnoDB internally "appends" the primary key. So essentially, you end up with an index that looks a bit like this:

• product_name
   
• product_id
   
• product_id (used internally by the indexing components)

As you can see, in InnoDB this is wasteful as a column is repeated in your index. This is something you don't see unless you know that InnoDB does this internally.

Don't Use SELECT COUNT(*)

Due to the implementation of the page files used for transaction safe tables, counting rows means you have to table scan the entire table. This in unlike MyISAM tables where it can just use the index. Instead of using COUNT(*) for getting that metric, Jay recommends you use a counter table that you update by incrementing by 1 for all inserts and decrease by 1 if you delete a row. You could even use a trigger for this.

Use Joins Instead of Sub-Queries

Not that I use sub-queries often, but they are costly operations. For instance, you if you wanted to get the MAX() selling prices for all your products, you could do this with a sub-query. However, if you have 1000 products you will perform 10000 sub-queries + the 1 query to get the product data. Use a join instead.

Turn the Query Cache

If you have a read intensive applications, turn on the query cache for MySQL. This isn't an InnoDB thing only, but by default the caching pool is 0MB (thus off).

innodb_buffer_pool_size

50-80% of the total memory of your box should be devoted to the InnoDB buffer. I love the quote from Jay, "MySQL just loves to use RAM. It eats it up just like PacMan. RAM is cheap, don't ask your boss...just throw another stick of RAM in that baby." However, innodb_buffer_pool_size != key_buffer_size. Even if you don't use MyISAM table engine, don't set the key_buffer_size to 0 because the internal DB that MySQL uses is implemented in MyISAM. If that DB isn't happy, your server isn't going to be happy.

August's Tip of the Month

Resolving MySQL Error 106
Reprinted from http://www.goitexpert.com
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=50708

MySQL database is an excellent option for storing data of any sort and size. However, it does have its little quirks and bugs that seem to pop up here and there. One of them is the infamous MySQL Error 106. Many database administrators have had this error occur for them, but few knew how to fix it or what causes it. It is a Windows-Only error.

This error usually occurs if you are upgrading one version of MySQL to the next one up, (i.e. MySQL 4.1 to MySQL 5.0). The reason for it is that the two MySQL services are conflicting with one another and the newer one is not able to start itself up. One option is to completely remove all of the instances. However, this option is only viable if you have no important data in your database. But an easier option would be to follow the instructions outlined below.

1. Step 1: Under your installation path for MySQL or the place where you told it to store data, open the file called mysql.err. This file can also be named youmachinename.err, depending on the installation options and the version of MySQL you had installed. Here is the path to this file on my system:

   C:\Program Files\MySQL\MySQL Server 5.0\data

   The log file should have a message similar to the following one:

   070501 9:45:44 InnoDB: Starting shutdown…
   070501 9:45:44 InnoDB: Shutdown completed; log sequence number 0 2235 
   070501 9:45:44 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: 
                         Shutdown complete

   This error message identifies this specific problem. Follow the instructions for the next step in order to remedy this error.

2. Step 2: An easy step to perform, just remove two files, ib_logfile0 and ib_logfile1, which are located in the same folder.

3. Step 3: Restart MySQL Service.

The above steps should have fixed the Error 106.

September's Tip of the Month

SQL Helper Function
Reprinted from http://www.thescripts.com
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=50709

Here is a class that I call a server because it stores the connection and cursor, handles errors and sends info (last query, etc) to its master's write() command. called with sys.stdout as the master, my dbServer prints to stdout. (The code is python.)

from MySQLdb import *
from time import time
 
class DBServer:
    def __init__(self, master):
        self.master = master
 
    def Login(self, servername, username, password, database=""):
        try:
        self.dbconnect = connect(host=servername, user=username, passwd=password, db=database)
        except (DatabaseError, OperationalError):
            self.dbconnect = None
            self.master.write('Couldn\'t connect to the database named %s on host %s'
                              %(repr(database), repr(servername)))
            return
        self.dbcursor = self.dbconnect.cursor()
        self.Execute('SET autocommit=1')
        return self.dbconnect
 
    def DBError(self):
        """Remove the current message from the cursor
           and display it."""
        try:
            (error, message) = self.dbcursor.messages.pop()
        except AttributeError:
            (error, message) = self.dbconnect.messages.pop()
        self.master.write('%s #%d:  %s' %(str(error).split('.')[-1],
                                          message[0], message[1]))
 
    def Execute(self, query):
        try:
            cursor = self.dbcursor
            now = time()
            cursor.execute(query)
            nRows = cursor.rowcount
            self.master.write(query)
            self.master.write("%d rows affected: %.2f sec." %(nRows, time() - now))
        except (DatabaseError, OperationalError):
            self.DBError()
            return
        return cursor
 
    def DBExists(self, database):
        """Return True if database exists"""
        resultset = self.Execute("show databases").fetchall()
        ## print resultset
        return (database,) in resultset
 
    def close(self):
        self.dbconnect.close()

October's Tip of the Month

Best Practices for Securing Tables, Triggers and Privileges
By MiMi Yeh, Reprinted from http://searchenterpriselinux.techtarget.com
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=50710

Although there is no way to limit table access on a per-use basis in MySQL, you can use stored procedures to limit user access to data. This is one of the many security workarounds that Scott Noyes, SearchEnterpriseLinux.com's MySQL expert, suggests, in addition to explaining the differences between user and local variables and preventing privilege pass-alongs by users.

Noyes is a MySQL Core-, Developer- and DBA-certified engineer. He is also the senior Web applications developer for Bookit.com.

How can you prevent users from passing on privileges granted to them on certain tables?

Scott Noyes: When issuing the GRANT statement, you have the option of appending WITH GRANT OPTION to the end. If that option is set, then users will be allowed to issue their own GRANT statements to other users. If you do not include that option, any attempt to issue a grant statement will be met with an access denied error.

Is there a way to limit access a table via a per-use basis in MySQL using the GRANT statement?

Noyes: The GRANT statement provides the following access limitations on a user:

• The number of queries per hour
   
• The number of updates per hour
   
• The number of connections to the server per hour
   
• The number of simultaneous connections to the server

There is no built-in method to limit the number of times a user may access a particular table outside of the per hour limits.

You can use stored procedures to limit user access to data. Construct a stored procedure that logs user access to the table (referencing the CURRENT_USER() function as needed), and exits early if the table has already been accessed. Give your users access only to the stored procedure, and not directly to the table itself.

What is the difference between user variables and local variables?

Noyes: Local variables refer to variables declared within a stored routine. Their scope is limited to the BEGIN and END block in which they are declared.

User variables are named with the @ symbol, and have global scope within the connection (you can set them inside a stored procedure, and still access them after the procedure has finished, for example).

How do you create a trigger for my USERS table that means if one user is removed, that user is also taken out of the USERS_DIR table?

Noyes: If you use the InnoDB storage engine, there is no need for a trigger. You can achieve the same effect using a foreign key with an appropriate cascading action:

ALTER TABLE users_dir ADD FOREIGN KEY (userId) REFERENCES users (userId) ON DELETE CASCADE;

If you use MyISAM or one of the other engines that does not yet enforce foreign key constraints, you can use a trigger:

CREATE TRIGGER users_AD AFTER DELETE ON users FOR EACH ROW DELETE FROM
users_dir WHERE userId = OLD.userId;

Note that the foreign key is defined on the users_dir table, but the trigger is defined on the users table.

The examples shown here for both foreign keys and triggers only address behavior if the user is deleted. You will need to decide what action to take if the user's id is updated to a different value instead.

Where should you put a NOT EXIST subquery, named as a column, in a statement with several joins ahead of it?

Noyes: Aliases defined in the select list, whether aliases of simple columns, results of expressions or subqueries, should not appear in the where clause. To use a subquery in the where clause, you should redefine it.

Using NOT EXISTS is one way to find rows which appear in one table but do not appear in another:

SELECT
  t1.*
FROM
  t1
WHERE
  NOT EXISTS (SELECT * FROM t2 WHERE t1.id = t2.id)

Such correlated subqueries can often be rewritten as joins:

SELECT
  t1.*
FROM
  t1
  LEFT JOIN t2 ON (t1.id = t2.id)
WHERE
  t2.id IS NULL;

Test both approaches on your data. A different index or table size can make one approach faster than the other in different situations. Of course, if you're on a hosted server that still hasn't upgrade from 3.23 or 4.0, subqueries aren't available anyway, so you'll have to use the join form.

November's Tip of the Month

Sequence Number Differences Between Oracle and MySQL
By Nigel McFarlane, Reprinted from http://searchenterpriselinux.techtarget.com
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=50711

If you're working with relational data, the day will soon come when you need to generate unique key values for your data, otherwise known as sequence numbers. In this tip I'll contrast the Oracle way with the MySQL way.

Since sequence numbers aren't part of the entry-level conformance of the SQL92 standard, their use creates portability problems for your SQL scripts straight away.

Way back when, there were no sequence number features in any relational database. In those days, if you wanted to generate unique numbers (like invoice numbers), you had to store them separately in a whole table, and increment them yourself:

CREATE TABLE my_sequence (id INT NOT NULL);

INSERT INTO my_sequence VALUES (0);
UPDATE my_sequence SET id = id + 1;
INSERT
INTO my_table

SELECT id, "some data"
FROM my_sequence;

That approach was rather clunky to say the least, but at least it is standard SQL.

In modern Oracle databases, all you need to do is create a sequence object, and then select new numbers from it:

CREATE SEQUENCE my_sequence;

INSERT
INTO my_table
SELECT my_sequence.nextval, "some data"
FROM dual;

The special table DUAL contains exactly one row and one column and is used mostly to report back a single piece of data - in this case derived from a sequence object. In all my life I've never been able to understand why a dummy table with one row and one column is called DUAL. Doesn't DUAL mean two-part or two-use? What's dual about a single row? Oh well.

By comparison, MySQL has no DUAL table. Instead, it allows you to put an extra detail on any numeric column of any table, provided it's an indexed key column. Not only can the column be NULLable, it can also be AUTO_INCREMENTable. Here's an example:

CREATE TABLE my_table (
id INT NOT NULL AUTO_INCREMENT,
data CHAR(30),
PRIMARY_KEY(id)
);

INSERT INTO my_table (null, "some data");

The INSERT statement looks highly suspect. After all, doesn't a primary key normally require a concrete value? True, and in this case MySQL calculates that for you. By passing in NULL to the column with the AUTO_INCREMENT property, you're telling MySQL to use the next free number not yet used for that column. If this INSERT statement were to be applied three times, each time the ID value would be one larger.

For the first three rows inserted, that number would merely be 1, then 2, then 3.

The fact that the sequence number is tied to a specific table in MySQL is a little restrictive, but there are various techniques that can be used to work around it. For example, MySQL provides a LAST_INSERT_ID() that can be used to dig the generated number out for other purposes.

December's Tip of the Month

Queries - Group the Resultset with GROUP BY
Excerpt from Knowledge Xpert for MySQL
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=50712

The principal reason for returning data in groups as well as sorting it is to apply an aggregating function, which provides information about each group rather than about individual rows. To explain this, consider an example table called child_products:

mysql> SELECT * FROM child_products;
+-----+--------------+------+-------+
| id  | name         | age  | price |
+-----+--------------+------+-------+
| 101 | Toy train    | 3-5  | 12.99 |
| 102 | Racing car   | 3-5  |  9.99 |
| 103 | Spinning top | 3-5  |  7.50 |
| 104 | Teddy bear   | 0-2  | 12.50 |
| 105 | Kitchen      | 2-3  | 24.99 |
+-----+--------------+------+-------+

Each item has a unique id number, a name, the age range of its intended customers, and a price. The GROUP BY clause can be used to group items by age, like this:

mysql> SELECT * FROM child_products GROUP BY age;
+-----+------------+------+-------+
| id  | name       | age  | price |
+-----+------------+------+-------+
| 104 | Teddy bear | 0-2  | 12.50 |
| 105 | Kitchen    | 2-3  | 24.99 |
| 101 | Toy train  | 3-5  | 12.99 |
+-----+------------+------+-------+

But this is not very useful! It has made the age column distinct, but the row it chooses to display with each distinct age range has effectively been chosen at random. Using GROUP BY in this way and retrieving several columns is seldom helpful.

Now consider this:

mysql> SELECT age FROM child_products GROUP BY age;
+------+
| age  |
+------+
| 0-2  |
| 2-3  |
| 3-5  |
+------+

This time products are grouped by age, but only age has been displayed in the result. In other words, the resultset contains only group level data, and not the misleading row-level data from within each group. To sort the groups in descending order:

mysql> SELECT age FROM child_products GROUP BY age DESC;
+------+
| age  |
+------+
| 3-5  |
| 2-3  |
| 0-2  |
+------+

The most common use of GROUP BY is in conjunction with aggregating functions. These perform some operation on the values retrieved within each group (even though the individual values do not appear in the resultset). SUM() and COUNT() are commonly used aggregating functions, which add up items by group and count the number of items in each group, respectively.

Here's an example of GROUP BY with SUM(). It adds up the total cost of items in each age range:

mysql> SELECT age, SUM(price) FROM child_products GROUP BY age;
+------+------------+
| age  | SUM(price) |
+------+------------+
| 0-2  |      12.50 |
| 2-3  |      24.99 |
| 3-5  |      30.48 |
+------+------------+

COUNT() can be used in much the same way:

mysql> SELECT age, COUNT(*) FROM child_products GROUP BY age;
+------+----------+
| age  | COUNT(*) |
+------+----------+
| 0-2  |        1 |
| 2-3  |        1 |
| 3-5  |        3 |
+------+----------+

The asterisk in COUNT(*) tells the query to look for any row that exists and count it.

You can name a column, COUNT(price) for example, which would only count items where price is not NULL or empty. * is used here just in case any product has no price; this query would still count it.

As well as SUM() and COUNT(), the expressions that may be used with GROUP BY include the following:

• AVG() Return the mean, or average, value of data in the group
   
• MAX() The maximum value
   
• MIN() The minimum value
   
• STD() The standard deviation

Caution: Don't name columns in your GROUP BY clause that are also used by your aggregating function. It defeats the object of grouping!

Here's a slightly more complex example, combining the averaging function AVG(), GROUP BY, and ORDER BY, and using an alias:

mysql> SELECT age, AVG(price) AS avg_p FROM child_products
    -> GROUP BY age
    -> ORDER BY avg_p;
+------+-----------+
| age  | avg_p     |
+------+-----------+
| 3-5  | 10.160000 |
| 0-2  | 12.500000 |
| 2-3  | 24.990000 |
+------+-----------+

This query finds the average price of items in each age group and then sorts by the average price (the alias avg_p).

For questions or comments on this site: webmaster@quest-pipelines.com
All content Copyright © Quest Software, Inc. All rights reserved.