MySQL Adds XML and XPath Support

I was browsing around the web and ran across this article at xml.com, XML Moves to mySQL. Being a heavy XML user, I had to read the article. It looks like MySQL is expanding the built-in support for XML.

This article isn't very detailed but it links to Using XML in MySQL 5.1 and 6.0 at mySQL.com which is very detailed.

I like the ability that is built in to support loading XML from files. That's a feature I wish Oracle would work on. Even in 11g, that functionality is still limited.

MySQL also adds ExtractValue() and UpdateXML() support. If you're manipulating XML much, you know that these two functions are needed.

From what I know of MySQL and what I read here, MySQL is just getting started with XML support. This article points out some baby steps on the road to XML maturity. You have to start somewhere and I am glad MySQL is adding this.

LewisC

Google Contributes to MySQL

According to this article in ComputerWorld, MySQL to get injection of Google code. Google has signed an agreement to contribute to MySQL (oddly enough called a contributer license agreement) and will contribute source code for a variety of technical items. In a way, this can be considered a very strong endorsement of MySQL by Google. Google even has an engineer dedicated to working with MySQL and the MySQL development team. I didn't realize that Google was such a heavy user of MySQL. Google will contribute some code related to replication and monitoring. Also noted in the article is that MySQL, in the future of course, will support role based security and even Transparent Data Encryption (TDE). TDE is a big selling point for financial companies and other companies heavily regulated by privacy concerns. TDE puts the burden of encryption on the database instead of on the developer. Oracle has had it since 10g. Some good info in the article. Check it out! LewisC

Hiding SQL in a Stored Procedure

I recently wrote a blog entry (on my Postgres blog) about hiding SQL in a stored procedure, Hiding SQL in a Stored Procedure. I decided to see if I could convert that same concept to a MySQL stored procedure.

It doesn't work exactly the same. For one, the syntax is a little different. I expected that and the syntax differences really aren't that bad. Minor tweaks really.

The second issue is the major one. While I could write the proc and return a result set, I am not, as far as I can tell, able to treat the procedure as a table. In Postgres, I created a function with a set output. Unfortunately, MySQL does not allow sets as a function result. You can return a set from a procedure though, as odd as that sounds.

So here is what I found.

My create table command and inserts ran unchanged. I did run into an issue with the timestamp though.

mysql> create table test_data (

-> name text,

-> address text,

-> create_date timestamp );

Query OK, 0 rows affected (0.09 sec)



mysql> insert into test_data values (

-> 'lewis',

-> '123 abc st',

-> timestamp '2001-01-01 10:00:00');

Query OK, 1 row affected (0.02 sec)



mysql> insert into test_data values (

-> 'george',

-> '456 def dr',

-> timestamp '2091-01-01 10:00:00');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>

mysql> select * from test_data;

+--------+------------+---------------------+

| name | address | create_date |

+--------+------------+---------------------+

| lewis | 123 abc st | 2001-01-01 10:00:00 |

| george | 456 def dr | 0000-00-00 00:00:00 |

+--------+------------+---------------------+

2 rows in set (0.00 sec)

Notice the timestamp in the "george" record is all 0s. I figure that's a configurable issue but I don't really care to research it at this moment so I'll just delete it and use a timestamp that's a little closer to NOW.

mysql> delete from test_data where name = 'george';

Query OK, 1 row affected (0.03 sec)

mysql> insert into test_data values (

-> 'george',

-> '456 def dr',

-> timestamp '2021-01-01 10:00:00');

Query OK, 1 row affected (0.02 sec)

mysql> select * from test_data;

+--------+------------+---------------------+

| name | address | create_date |

+--------+------------+---------------------+

| lewis | 123 abc st | 2001-01-01 10:00:00 |

| george | 456 def dr | 2021-01-01 10:00:00 |

+--------+------------+---------------------+

2 rows in set (0.00 sec)

mysql>

Ok. Now I'm ready to go. I look at the proc that I wrote for Postgres:

CREATE OR REPLACE FUNCTION get_data_by_creation(

          timestamp without time zone, 

          timestamp without time zone)

RETURNS SETOF test_data 

AS

$$ 

SELECT name, address, create_date

  FROM test_data

  WHERE create_date >= $1

    AND create_date <= $2;

$$

LANGUAGE 'sql' VOLATILE;

That's obviously not going to work but like I said above, the changes are fairly minor. I need to add a delimiter call and drop the postgres specific stuff:

delimiter //

CREATE PROCEDURE get_data_by_creation(

    IN param1 timestamp, 

    IN param2 timestamp)

BEGIN

  SELECT name, address, create_date

    FROM test_data

    WHERE create_date >= param1

      AND create_date <= param2;

END;

//

That compiles fine. Now for the test. I can't use select so I will do a call. I write three call statements: one to return both records, one to "george" and one to return "lewis".

call get_data_by_creation('2000-01-01 10:00:00','2025-01-01 10:00:00');

call get_data_by_creation('2002-01-01 10:00:00','2025-01-01 10:00:00');

call get_data_by_creation('2000-01-01 10:00:00','2010-01-01 10:00:00');

When I run these, I get the expected results:

mysql> call get_data_by_creation('2000-01-01 10:00:00','2025-01-01 10:00:00');

+--------+------------+---------------------+

| name | address | create_date |

+--------+------------+---------------------+

| lewis | 123 abc st | 2001-01-01 10:00:00 |

| george | 456 def dr | 2021-01-01 10:00:00 |

+--------+------------+---------------------+

2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.05 sec)



mysql> call get_data_by_creation('2002-01-01 10:00:00','2025-01-01 10:00:00');

+--------+------------+---------------------+

| name | address | create_date |

+--------+------------+---------------------+

| george | 456 def dr | 2021-01-01 10:00:00 |

+--------+------------+---------------------+

1 row in set (0.02 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> call get_data_by_creation('2000-01-01 10:00:00','2010-01-01 10:00:00');

+-------+------------+---------------------+

| name | address | create_date |

+-------+------------+---------------------+

| lewis | 123 abc st | 2001-01-01 10:00:00 |

+-------+------------+---------------------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Sweet! This code is actually not all that far from Oracle's PL/SQL. I'll do up an example of that next.

LewisC

Does MySQL GIS Make The Grade?

Bob Zurek of EnterpriseDB posted a blog entry today titled, "We slammed into a brick wall with MySQL". If you read his blog entry, the information he is referencing is in this press release, FortiusOne Migrates GeoCommons Intelligent Mapping Website to EnterpriseDB Advanced Server. If you read that press release, it says:

“We slammed into a brick wall with MySQL,” said Chris Ingrassia, chief technology officer, FortiusOne. “As an example, MySQL’s rather limited and incomplete spatial support dramatically impacted performance. We were looking for an affordable database solution, but we required enterprise-class features and performance that MySQL simply couldn’t deliver. Plus, philosophically we want to support open source-based technologies like EnterpriseDB.”

I'm not at all familiar with the MySQL GIS support and only remotely familiar with PostGIS (PostgreSQL GIS). Is MySQL GIS support lacking or was that particular application of MySQL GIS a bad fit? Anyone familiar with both? I'm curious as to how they compare. LewisC

Tablespaces in MySQL, Oracle and Postgres

If you are not familiar with tablespaces you may be wondering what the big deal about them is. Tablespaces are a logical addition to a database that helps maintenance, and potentially, can improve performance.

In Oracle and MySQL, a tablespace is a logical unit meant to store segments (i.e. tables and indexes). In Postgres, a tablespace is a physical unit. It is a symbolic link to a directory. Postgres does not allow tablespaces on operating systems that do not support symbolic links (such as windows).

The data file is the actual physical storage mechanism in Oracle and MySQL. Postgres stores tables in individual files. Postgres support of tablespaces is minimal. In MySQL and Oracle, performance can be improved by a more granular spread of data across disks. Ease of maintenance is maintained due to the logical grouping of tablespaces.

Oracle syntax for creating a tablespace is much the same as MySQL but with many more options. Oracle also allows a single tablespace to be made up of many data files.

Below is a very simple example of creating and using a tablespace in MySQL:

mysql> create tablespace testts

-> add datafile 'myfirstfile'

-> engine = falcon;

Query OK, 0 rows affected (0.48 sec)

mysql> create table testmyts (

-> abc integer )

-> tablespace testts;

Query OK, 0 rows affected (0.11 sec)

mysql>

The first command creates the tablespace (naming it testts) and assigning it a file name of myfirstfile. Of course, we are using the falcon engine. If you look in your MySQL data directory after running this command, you should a new file named myfirstfile.

The second command creates a table using our new tablespace. If you look at the tablespace now, it should be bigger. If not, insert a bunch of rows and watch it grow.

Oracle syntax, in its simplest form, is very close to MySQL syntax. Here is the same example in Oracle.

SQL> create tablespace testts

2 datafile 'myfirstfile'

3 size 10M;

Tablespace created.

SQL> create table testmyts (

2 abc integer )

3 tablespace testts;

Table created.

SQL> 

In Oracle, we leave off the engine keyword and we need to declare a size. We can create a small data file and use autoextend if we want, but we MUST declare an initial size.

In Oracle, it is possible to create each partition of a partitioned table in its own tablespace. I'm not sure if that's possible with MySQL, but I plan to try it out!

How do I log into MySQL?

I remember the first time I downloaded MySQL. I think I was using Mandrake Linux. Anyway, the install was fairly painless but once it was installed, I had no clue how to run queries.

I was coming from an Oracle background and was used to SQL*Plus. I was also familiar with PostgreSQL and psql. For the life of me, I could not figure out how to get into MySQL.

So, for you developers and brand new users, you can easily start MySQL and start using it. This is not meant for a production installation, just for playing on your laptop or desktop.

Start MySQL by running mysqld (mysqld.exe on Windows). It will be in your MySQL home/bin directory. That gets the server portion of our program running.

The SQL*Plus equivalent is mysql (or mysql.exe). If you are logging in for the first time, you can use root. Once you are in, you can create other users.

To log in and run commands, type:

mysql -u root

That will load the character based MySQL Monitor. At this point you are in and ready to play.

LewisC

Falcon Test

I just downloaded Falcon and was running some tests. I was getting an error:

C:\MySQL\bin>mysqladmin version status proc 
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'ODBC'@'localhost' 
                             (using password: NO)'

To fix this error, just add a -u parameter, i.e.:

C:\MySQL\bin>mysqladmin version status proc -u root
mysqladmin  Ver 8.42 Distrib 6.0.2-alpha, for Win32 on ia32
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free 
software, and you are welcome to modify and redistribute it 
under the GPL license

Server version          6.0.2-alpha-community-nt-debug
Protocol version        10
Connection              localhost via TCP/IP
TCP port                3306
Uptime:                 27 min 43 sec

Threads: 1  Questions: 4  Slow queries: 0  Opens: 15  Flush tables: 1  
Open tables: 8  Queries per second avg: 0.2
Uptime: 1663  Threads: 1  Questions: 4  Slow queries: 0  Opens: 15  
Flush tables: 1  Open tables: 8 
 Queries per second avg: 0.2
+----+------+----------------+----+---------+------+-------+------------------+
| Id | User | Host           | db | Command | Time | State | Info             |
+----+------+----------------+----+---------+------+-------+------------------+
| 5  | root | localhost:4682 |    | Query   | 0    |       | show processlist |
+----+------+----------------+----+---------+------+-------+------------------+

I'm going to be blogging about my attempts to use some of the newer enterprise features of Falcon, as well as general MySQL programming info. LewisC

Free MySQL Magazine

I stumbled across a free MySQL magazine while reading the 64th edition of Pyhthian's Log Buffer at Diamond Notes (a MySQL DBA Blog). The magazine is MySQL Magazine. There are currently two editions, Summer 2007 and Fall 2007. They're in PDF format and, at least for a MySQL beginner like me, are very useful. The Summer 2007 edition has a very good "Securing MySQL Server" article. One improvement I would like to see om the site would be to have a table of contents in the HTML. That way I wouldn't have to load the PDF just to see what's inside. That's a minor nit though. LewisC

Differences Between MySQL and Oracle

Augusto Bott at Pythian recently posted a good entry detailing some differences between Oracle and MySQL from a MySQL DBA's perspective, From MySQL to Oracle: A Few Differences. My viewpoint is exactly the opposite, I know Oracle extremely well but I have a lot of learning to do with MySQL. Augusto ran into a SQL*Plus issue, where it is less than obvious where a SQL syntax error is. He could get around this error by using SQL Developer, Oracle's free SQL Development IDE. SQL Developer can connect to and browse MySQL databases in addition to Oracle and SQL Server. He also noticed some syntactic differences between MySQL and Oracle. That is something I have been running into in my experimentation with Oracle, Postgres, MySQL and DB2. For something touted as a standard, SQL is VERY different from one database to another. This is a good article. I can't wait to see more from Augusto. I like seeing his viewpoint as it might help me avoid some of the pain I would otherwise experience. LewisC

Partitioning in MySQL 5.1, Part 2

Read Partitioning In MySQL, Part 1 before reading this post.

Partition pruning is the most critical performance component about partitioning. Partitioning can ease management of your data (for example the ability to just backup or restore a single partition or the ability to drop a no longer needed partition) but the main reason people add partitions is for performance.

Pruning helps performance by only reading the partitions that need to be read for a query. For example, if you have a table range partitioned by year that has partitions for the years 2000 through 2010. If you write a query selecting data between 2002 and 2005, you don't want to have to scan all 10 partitions.

The optimizer needs to be smart enough to select and scan just those partitions that hold the data. This is called partition pruning. All of the databases that I have used that support partitioning (Oracle, Postgres, EnterpriseDB and DB2) support partition pruning. The better the optimizer, the less often the optimizer will erroneously scan partitions that aren't really required.

As I said above, partition management is important and this is one area that postgres (and enterprisedb) lacks. The management and support functions in MySQL are pretty robust. The way it will dynamically move data when a partition is dropped or added is pretty sweet. This is very advanced logic. I will need to play with it to see how robust and performant it is but in most databases, moving data like this requires manual intervention.

I also like the ability to convert a table to a partitioned table online. That is another feature that requires manual intervention in most databases.

The last slide I will cover from the presentation are the limitations. Right now there are several severe limitations.

Most of the limitations listed here are no big deal. All partitions must use the same storage engine (no biggie) and increased response time for partitioned tables with many partitions (expected). The time you save by partition pruning should outweigh the overhead by a large margin.

The things that bother me are the integer result requirement. I'm just not sure how I would implement range partitioning using a numeric. That really sounds like a list partition to me.

The lack of foreign keys in a partition is also a limitation of postgres and is a show stopper in many cases. If you can't ensure the integrity of your database, do you really need that data? I'm glad the fix for that is on the road map.

What do you think of partitioning in MySQL?

Partitioning in MySQL 5.1, Part 1

I was browsing around the MySQL web site tonight and ran across some free webinars (recordings). Seeing as how I once did a podcast on Oracle partitioning, one webinar that jumped out at me was Partitioning in MySQL 5.1 and onwards.

I didn't even know MySQL did partitioning. Here is a description of the webinar:

In this webinar we will provide further insight into MySQL partitioning, including:

• Introduction to MySQL Partitioning
• Linear Key Partitioning
• Partition Function
• Partition Options
• Information Schema for Partitions
• EXPLAIN PARTITIONS
• Partition Management
• Partition Pruning
• Partitioning and NULL Values
• Partitioning Implementation
• Partitioning for MySQL Cluster
• Future Roadmap for Partitioning
• Partitioning Limitations in 5.1

Sounds good. It's a good presentation but kind of slow. If you're like me and would prefer to move at your own speed, you can download the presentation itself.

For those of you even more impatient than me, I will point out a few highlights. The beginning of the presentation was about what partitioning is and why use it. I'll skip over those parts. If you are interested in those details, post me a note and I will do a blog entry on that topic.

One of the more important pieces of information is the types of partitioning supported.

Oracle supports all of these types of partitioning but some other very good databases do not. For example, PostgreSQL only supports range partitioning (and list becuase of that) but you would have to manually implement a hash partition and any composites are out of scope for now.

I am fairly impressed with this support. You can also mix and match engines (in future versions). That's pretty exciting.

A quick description of the types for those not familiar:

• Range Partition - Things like by year, by month, etc. Used very frequently in data warehousing. You might have a partition for each year. You can drop partitions (old years) and add new parititions (for future years). Range partitions tend to be dynamic (you'll be adding and dropping partitions over time).

• List Partition - A known list of partitioning values. You may partition by state, country, region etc. Each value gets its own partition. You can add partitions but a list tends to be fairly static.

• Hash Partition - A hashing algorithm creates a numeric value. You'll use this on large data sets that don't have a logical partitioning key. The idea is to give you the ability to partition on numeric data like a primary key.

• Composite Partition - A combination of the three partitioning types above.

The partition function is the function that determines which partition a particular row will reside in. The current implementation has some serious limitations but those can be worked around. Basically you write a function, say by comparing dates, and that determines which partitions will hold your record. I'm sure that I could live with two of the current limitations: always an integer value (how do you get that to work with dates?) and the fact that you can only write the function on your primary key.

I'll finish up my coverage of this presentation in part 2. All in all, I am quite impressed with the partitioning function in MySQL 5.1.

LewisC

MySQL 6.0 in Alpha Test

I just found out that MySQL 6.0 with the Falcon Storage Engine is available as Alpha. Falcon adds ACID compliance, tablespaces and performance improvements. It's supposed to also simplify administration but I'm not sure how MySQL can be simplified. It's pretty simple now. I've always thought that the ability to swap out the storage engine is the best feature of MySQL. It reminds me of the replaceable database drivers from my clipper days in the late 80s and early 90s. From my reading about Falcon, it looks like one advantage will be the fact that Falcon has been engineered to take advantage of 64 bit architectures and large memory caches. User data files in Falcon can be up to 100 terabytes. That should pretty much cover most uses. It looks like you only get a single data file per Falcon database. Falcon has what it calles the Falcon serial Log. It's the equivalent of the Redo Log in Oracle or the Write Ahead Log (WAL) in Postgres/EnterpriseDB. This log file is also used for crash recovery (which I believe is completely automated in MySQL 6.0). Falcon uses 4 different memory caches: log, system and index, page and record. It uses a sophisticated algorithm to determine what data remains when the max is hit. Supposedly this algorithm is faster and more complex that the LRU.

Thoughts on MySQL Proxy

I am reading up on MySQL Proxy and find it to be very interesting. It looks like the proxy is a network layer between clients and servers. A client may think it is connecting to a single server but in reality, the client's query could be running against multiple distributed servers. Another nice feature is the ability to monitor and secure data at the query and result level. A client can send a query and the proxy can log that query. When the results come back, the proxy could strip out data (or mangle it) before it goes back to the client. That would be a nice feature for protecting data during development and testing. The developers and testers can run their queries against production and the proxy could mangle sensitive data on the way back out. It's only compatible with various Unix/Linux flavors at this time so no Windows. That's probably not that big of an issue. I don't know anyone running MySQL in Windows in production. It's pretty slick. I need to do some additional reading before I can start playing with it.

My SQL Community Meeting at Google

Cool video from Google about a MySQL user group (I think). I thought it would be appropriate as my first MySQL blog Post. Some good information here. Lot's of storage talk.