In this blog series, I will share my experiences as I migrate commercial databases (i.e., Microsoft SQL or Oracle) to open source (MySQL). More specifically, we will look at how you can migrate from MS SQL Server to MySQL.

For this first blog post I’ve chosen Jira database as an example, and used three different tools to migrate Jira database in Microsoft SQL Server to MySQL:

1. MySQL Workbench (opensource)
2. Amazon DMS (cloud tool)
3. Ispirer MnMTK 2015 (commercial tool)

When I started my research, I was under the impression that Jira database would be easy to migrate (no stored procedures, no triggers, etc.). It turned out that there were some problems that I was able to fix.

One of the reasons I chose Jira as opposed to some standard MS SQL database (such as AdventureWorks2014) is that it is a non-standard …

Recently, someone emailed me:I have a requirement in MYSQL as follows:
we have a table EMP and we have to restrict the users not delete employees with DEPT_ID = 10. If user executes a DELETE statement without giving any WHERE condition all the rows should be deleted except those with DEPT_ID = 10.

We are trying to write a BEFORE DELETE trigger but we are not able to get this functionality.

I have seen your blog where you explained about Using an UDF to Raise Errors from inside MySQL Procedures and/or Triggers. Will it helps me to get this functionality? Could you suggest if we have any other alternatives to do this as well?Frankly, I usually refer people that write me these things to a public forum, but this time I felt like …

Yesterday, I wrote about how I think this year's MySQL conference will differ from prior editions. I also wrote that I will attend and that I will be speaking on MQL-to-SQL.

I promised I would explain a little bit more background about my talk, so here's the first installment.

Abstract: MQL is a JSON-based database query language that has some very interesting features as compared to SQL, especially for modern ( …

From a functional perspective, the core SQL support in all major and minor RDBMS-es is reasonably similar. In this light, it's sometimes quite disturbing to find how some very basic things work so differently across different products. Consider this simple statement:

SELECT  'a' /* this is a comment */ 'b'
FROM    onerow

What should the result be? (You can assume that onerow is an existing table that contains one row)

It turns out popular RDBMS-es mostly disagree with one another.

In Oracle XE, we get this:

SELECT  'a' /* comment */ 'b'
                         *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

PostgreSQL 8.4 also treats it as a syntax error, and thus seems compatible with Oracle's behavior:

ERROR:  syntax error at or near "'b'"
LINE 1: SELECT  'a' /* this is a comment */ 'b'

…

Recently, MySQL had an article comparing MySQL and SQL Server at  http://dev.mysql.com/tech-resources/articles/move_from_microsoft_SQL_Server.html

There is one clarification I would like to make to this article.  The article states that MS SQL Server has row locking, and while this is true, MS SQL Server doesn’t always use row locking and often will resort to page locking.  A page is 8k of data, so, in effect, many rows are locked at the same time even if only one row in that page is being updated.  At high data throughputs, this can lead to serious lock contention and to dead locks, even though the two processes with the contention or dead  lock are updating different rows.   Stated differently, no matter how well you order your multi-row update processes in MS SQL Server you should expect …

Yes, if compared to code in Java or C#. For example, this overly simple code took 284 seconds.

CREATE PROCEDURE CountTest()
begin
    declare counter int default 0;
    select now();
    repeat
        set counter = counter + 1;
    until counter > 120000000
    end repeat;
    select counter;
    select now();
end

Ignoring my off by one error, here is equivalent code in C# (the language I’m currently learning).  It took 419 milliseconds, or MySQL took 677 times longer to execute. From my experience, Java isn’t going to be any slower.

 int counter = 0;
while (counter < 120000000)
{
    counter++;
}

Slow stored procedure …

Ok - this has been stewing for some time now, and I think now is the right time to announce that I am working together with Jos van Dongen from Tholis Consulting to create a book for Wiley with the tentative title "Building Pentaho Solutions".

My personal aim is to make this book the primary point of reference for DBAs and Application Developers that are familiar with Open Source products like MySQL and PostgreSQL but have no prior BI skills, as well as BI professionals that are familiar with closed source BI products like Microsoft BI and Business Objects that want to learn how to get things done with Pentaho.

The book will cover all distinct components and sub-products that make up the …