MySQL 5.4

New Features

MySQL has released a new version of database server – MySQL in version 5.4. Latest release was 5.1 with a really great features. This one brings not so much but also very important features:

• InnoDB storage engine can address more than 4 CPU’s/cores. Thjs helps that MySQL scale much better under huge application workloads.
• Till now, subqueries were well known as performance problematical. In version 5.4 subquery optimization has been improved a lot in a number of various use cases. As MySQL mentioned in the example below, execution time dropped from 12 minutes (9 000 000 reads) on MySQL 5.1 to just 1.8 seconds (153 000 reads) on MySQL 5.4

SELECT COUNT(l_orderkey) FROM lineitem
WHERE l_linenumber=1 AND
      l_orderkey IN
      (SELECT o_orderkey FROM orders
         WHERE o_totalprice > 1000 AND
               o_custkey IN
               (SELECT c_custkey FROM customer
                  WHERE c_address LIKE 'Le%'));

• MySQL 5.4 offers a new much better join algorithm which speed up execution time of multi-way joins like in the following example

SELECT COUNT(*) FROM part, lineitem
       WHERE l_partkey=p_partkey AND p_retailprice>2050
             AND l_discount>0.04;

• Improved error handling – through the implementation of standard SQL (SQL 2003) SIGNAL and RESIGNAL operations in stored procedures, functions and triggers developers are able to signal rule violations during execution.

Must mention here that Oracle and SQL Server not implement this functionality but have their own mechanisms: Oracle through RAISE and SQL Server through RAISEERROR statements.

• INFORMATION_SCHEMA got a new objects PARAMETERS. We wait a lot on this.


• In prior version of MySQL out parameter were not possible in prepared statements. That has been changed now.

This is all from programmers perspective. Have a fun.

Compare SQL Server, Oracle and MySQL database with DBTYP.NET Studio

Table 'mysql.proc' doesn't exist.

Sometimes our support team receive a helpdesk request concerning this error message shown in Schema Comparison of DBTYP.NET 2009 Studio.

The usual problem with such a customers is that almost all of them make recent upgrade of MySQL server. While some releases of MySQL introduce changes in the structure of the system tables in mysql database to add new privileges and support new features, it is necessary to update the system tables as well.

This can be achieved by running mysql_fix_privilege_tables script in MySQL prior to version 5.1.7 or mysql_upgrade in version 5.1.7 or above.

For more details check following links:

• http://dev.mysql.com/doc/refman/5.1/en/mysql-fix-privilege-tables.html
• http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html

See all database differences and dependencies with DBTYP.NET Studio

DBTyP.NET 2008 Release 3 Available

BYPsoft announced the availability of DBTyP.NET 2008 Release 3, the newest version of the cross-database comparison tool that compares SQL Server, MySQL and Oracle databases (schema and data). DBTyP.NET 2008 Release 3 is available for download from http://www.bypsoft.com.

With its rich support for schema and data cross-database comparison, DBTyP.NET takes the mystery out of databases, making their comparison practical and easy for programmers and database administrators everywhere. Over and above its powerful comparison capabilities till now, BYPsoft added to the DBTyP.NET 2008 support for schema comparisons for Oracle databases. Full cross database comparison family includes now support for SQL Server, MySQL and Oracle databases in the latest release of DBTyP.NET 2008 for a fraction of the cost of single-database solution.

Since the DBTyP.NET is under constant development, user interface has been redesigned and rich visual interface and easy-to-use features of DBTyP.NET 2008 allow developers and DBAs to identify and deploy changes quickly on all supported databases and enhance productivity and maximize results. Features like colorfull differences, different objects icons, visual column description, in place colorfull script differences, multiple views and more all save time and ensure accuracy.

In addition to all the new features listed above, in the latest release of DBTyP.NET 2008 BYPsoft includes also the following:

• SQL Server 2008 Ready
• Support for SQL Server ROWGUIDCOL
• Filtered out system objects on SQL Server databases.
• Support SQLServer user defined types.
• Support MySQL ENUM and SET data types.

We know that constant development is the key to progress, therefore we already started working on all new cool features. Stay with us.

List All Functions and Stored Procedures definitions in Oracle schema

A long time I have not published anything. BYPsoft preparing new version of DBTyP.NET with full support for a Oracle schema comparison and therefore didn't had any free time left for this blog.
A very interesting topic came to me out of this experience.
Have you ever tried to get a list of all stored procedures and functions in Oracle schema? Preaty easy,
SELECT * FROM USER_PROCEDURES;
You figured out that it is quite different comparing to SQL Server and MySQL where with the single query
SELECT * FROM INFORMATION_SCHEMA.ROUTINES;
you get a body definition as well.
To achieve this on Oracle is not so simply. First, to get user sources you should query USER_SOURCE view where each function or stored procedure line is stored in one row.
To get a full body, you have to use a powerful hierarchical query clause SYS_CONNECT_BY_PATH.
Here is a query which returns you list of all stored procedures and functions in Oracle schema together with their bodies defined in one column:

SELECT routine_name, y.TYPE, TRANSLATE(LTRIM(x.text1, '/'), '/', ' ') routine_definition
FROM (SELECT name routine_name, LEVEL lvl, sys_connect_by_path(text, '/') text1
FROM USER_SOURCE
CONNECT BY LINE - 1 = PRIOR LINE AND name = PRIOR name) x,
(SELECT name, TYPE, MAX(line) AS maxline
FROM USER_SOURCE
GROUP BY name, TYPE) y
WHERE x.routine_name = y.name AND x.lvl = y.maxline
ORDER BY TYPE, routine_name

Compare database schema and data fast and easy with DBTYP.NET Studio


MySqlDateTime and .NET

In version 2008 our cross database comparison tool DBTyP.NET will support cross data comparison between SQL Server and MySQL DateTime values (date, datetime, smalldatetime). Actually, this is a common problem, between MySql.Data.Types.MySqlDateTime and System.DateTime types in .NET. It is very possible that if you tries to update MySqlDateTime column with DateTime variable you will get SystemArgumentException saying that could not store value ... into ... Expected type is MySqlDateTime. It is possible to format value as "yyyy-MM-dd HH:mm:ss" but in our case we don't want to iterate through whole DataSet and make this formatting.
Very elegant solution is to change a DataColumn type from MySqlDateTime to System.DateTime as

myMySqlDateTimeColum.DataType = typeof(System.DateTime);

After that you should not worry about formatting.

How to check if class has been used in Web or Windows application

After a while, I decided to participate in a newsgroups tonight and found an interesting question. "Is there a programmatic way for the class lib to determine what kind of app is running - web or windows". The worst, guy requested official response from Microsoft support and didn't get an answer. After 3 days his question wasn't answered, he re-posted it. So, is there are something wrong? Looks like not.
The simple answer from my side was, to implement following function somewhere in his shared assembly:
public bool IsWebApp(){
return (HttpContext.Current != null);
}
So, if there is Current HttpContext than function has been called from ASP.NET application and if there is not such an object, function is called from Windows Forms application.
Simple enough.

Handling Miliseconds in .NET and SQL Server

Have you taken a look at number of such a questions in online community? It looks like that a lot of .NET developers have a problems reading and updating milliseconds value from SQL Server databases. So what is a real problem here?
Let's take a look at common scenario. Developers read data from a table which has Datetime column ColDateTime. Let's say they are storing values in some DataTable table1. Suddenly, if you want just to print a Value of that field, with

table1.Rows[0].Cells["ColDateTime"].Value.ToString()

you will get value like '2008/02/26 2:26:53 PM'. But you value in database is actually '2008/02/26 14:26:53.3480'. So milliseconds are missing. The only way to get them is to convert value from database in DateTime variable and then print out that variable:

DateTime dtDbValue = System.Covert.ToDateTime(table1.Rows[0].Cells["ColDateTime"]);
dtDbValue.ToString();

Now, you have a real value: '2008/02/26 14:26:53.3480'

Connected to this is also reverse process, where people lose milliseconds during update. Take a look at following statements:

DateTame dtValue = DateTime.Now;

string sql = "update table1 set ColDateTime = '" + dtValue.ToString() + "'";

-- create DataCommand, assign CommandText and execute.

You will see that your column has been updated with the wrong value. Again milliseconds cut.

People try very often to format ToString method call to looks like:
string sql = "update table1 set ColDateTime = '" + dtValue.ToString("yyyy/MM/dd hh:mm:ss.ffff") + "'";

but this cause an exception on SQL Server side saying that varchar value can not be converted to Datetime type. Strange, and maybe lack of documentation, but the solution is to format your DateTime value with different format - using 3 f and not 4:

string sql = "update table1 set ColDateTime = '" + dtValue.ToString("yyyy/MM/dd hh:mm:ss.fff") + "'";


Compare databases fast, safe, free... DBTyP.NET - in version 2008 supports Oracle besides SQL Server and MySQL. DBTyP.NET 2008 is coming... in 3 weeks.