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
Tuesday, December 25, 2007
MySQL Adds XML and XPath Support
Saturday, October 27, 2007
Google Contributes to MySQL
Saturday, October 6, 2007
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
Wednesday, October 3, 2007
Does MySQL GIS Make The Grade?
“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
Sunday, September 30, 2007
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!
Saturday, September 29, 2007
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
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
