Monday, 12 February 2007

MySQL vs Sybase / MS SQL Server – a comparison

(Migrated from old website)

As I database consultant that has been working with Sybase and MS SQL Server for a long time I have been interested to see the rapid growth of MySQL. I decided to evaluate MySQL from the point of view of porting a database to it from Sybase/Ms SQL Server.

For those of you who don’t know, the two (Syb & MS) are still about 90% the same, at least from a Transact SQL point of view. They also both still come with that old friend – the pubs database (pubs2 & 3 on Sybase, pubs on MS SQL Server). Therefore what better to use for my sample database as pubs ! Note that I will not provide my converted script due to copyright issues, but if you are a Sybase of SQL Server user feel free to do the same. This is a very good test as pubs has been purpose build to try and use many of the different features available. Here is the results of my test starting from the point of my download of MySQL 5.0 alpha (the newest version available when I started this) :

Steps

1. Install MySQL, client tools & documentation.

2. RTFM for about 10 minutes.

3. set up my server using the winmysqladmin program. It gives me a working server ! Connect to the server: from a command prompt, type ‘mysql’ (no options).

I'm in a 'test' database. Follow a basic tutorial from the manual.

Learn a few basic commands like "show databases;" "select now();", & "select user();" - This seems pretty easy !

4. Decided to create a database. I'm going to create pubs2. Following the tutorial I find the syntax:

"create database pubs2;" Fails. Go back to RTFM.

5. Find out that there is a "test" user that I am connected as with no privileges. Find out I should be using the 'root' login. connect as root with no password:

mysql -uroot mysql. Follow recommendations to set a root password:

SET PASSWORD FOR root@localhost=PASSWORD('new_password');

Log out and login with the password:

mysql -uroot mysql -pnew_password

I'm in. Now try again:

create database pubs2; - Success !

Run "show databases;" - its there.

6. type "use pubs2"; - It works !

7. Cut and paste create table authors... from instpubs2. Get an error:

ERROR 1064 (42000): You have an error in your SQL syntax. Check the manual that

corresponds to your MySQL server version for the right syntax to use near 'id not null,

au_lname varchar(40) not null,

au_fname varchar(20) not null,

ph' at line 2

Sounds like a simple error, as id is a user define data type. Don't know how

to create those yet, so I'll replace it with the underlying type - varchar(11).

8. Try again - Success ! Now for Rest:

Publishers - ok

royshed - replaces tid with varchar(6), then ok.

sales - ok

-- Decided to work ou how to create udfs so I don't have to keep editing the scripts.

Spent about 1 hour earching the web and manuals. It doesn’t support them. Ill continue the way I was.

salesdetail - ok

titlesauthor - ok

titles - Error:

ERROR 1064 (42000): You have an error in your SQL syntax. Check the manual that

corresponds to your MySQL server version for the right syntax to use near 'money null,

advance money null,

total_sales int null,

notes varchar(200) null' at line 6

Thats right - no money datatype ! That wouldn't be a big deal if we had UDFs, but we don't. More re-writing ! (I’ll use decimal) and I am going to try replace image with the blob type. I don't know what the difference really is, but this is just a basic test. It works.

9.

After getting through the tables. I get to constraints. Primary key constraints seem ok, but foreign keys can’t be created. After looking on the manual I find I need to create indexes before foreign keys. I do this, and can then progress. At this point however alarm bells are ringing about how it handles constraints. The manual is warning me that when using not null and default constraints that behaviour will not be as I would expect. For example if you try to insert a null into a not null column, MySQL will replace the null with a '0' or a '' (empty string) rather than rejecting the insert. Is sort of defeats the purpose and I would avoid using these features until they 'fix' them. The manual says you can recompile MySQL to change this, but doesn't make

it clear on what the new behaviour will be.

10.

We need to skip some default clauses here. This requires editing the table creation scripts and manually placing the defaults on the relevant columns, or editing the inserts to include this. I have chosen to edit the data. Note I also had to replace

some occurrences of "" with a single ' as I am not sure how MySQL handles double quotes inside double quotes.

11. Indexes. MySQL doesn't seem to support the clustered/non clusters index types. I created the indexes without specifying the type and it works fine. The manual says that all MySQL indexes are b-trees with some compression.

12. Rules. Rules are not supported. I am going to ignore them as I think the only way to work around them would be to put the functionality in the app (not desirable) or in triggers.

13. Triggers. The manual says triggers are scheduled for 5.1. I can confirm they don't work in 5.0. I am having to push a lot of functionality back to the app (it theory – I’m not actually building an app).

14. Views. Not yet supported. Should be in 5.1 Derived tables (unnamed views) are supported, but this doesn’t help here.

15. Stored procedures. Apparently they are supported in 5.0 but I need to recompile the server. My C skills are probably not us to this, and it obviously isn't ready for prime time is it isn't in the server, so I'll forget them.

16. Grants. Grant all succeeded. Grant create table etc... didn’t. Give the number of things I have had to leave out, I don’t think I’ll worry about fixing this, but the manual suggest you can grant these things at the user level.

Other notes:

  • Temporary tables have limited functionality and cannot be used in the same way as a real table. For example the following doesn’t work:
SELECT * FROM temporary_table, temporary_table AS t2;
  • BCP like functionality is provided by the load data command. I only did some basic testing, but is seemed very fast (faster than bcp).
  • The security model seems quite basic; groups and roles seem absent, so granting seems to need to be done on each user.

So what do I think of MySQL ?

This product has great potential. By the time version 5.1 becomes a stable production release it will be usable for a lot of applications. By the time 6.x comes out I would expect they have caught up in the important areas of data and domain integrity to the point where is will become a feasible option to companies out their. It wasn't that long ago that people tried to write off Apache & Linux. I can see MySQL being the next big thing in Open source, if it isn't already.

Having said that, as it stands now (5.0) I would not be willing to use MySQL for anything more critical than a reporting database. The lack of system wide rules, defaults and the problems with constraints mean that you would have to build all

of your referential and domain integrity into the app. That’s a DBA's worst nightmare!

It is worth noting here that there is an offshoot of MySQL called MaxDB (formally SAPDB). This has been developed by MySQL and SAP and has been developed to try and address these enterprise limitations. I have yet to look at it, but if I have time I will also try and conduct the same exercise against this.

2 comments:

Paul Robinson said...

Didn't you know? Microsoft started SQL Server by buying it from Sybase. They have probably changed it dramatically but the original code came from Sybase.

Paul Robinson's Blog

Rosdan Teh said...

Dear Paul,
He was talking abount MySql, not M$Sql