Tag Archives | Database

Differences in databases or from Oracle to MySQL

In the last couple of weeks we have integrated MySQL support for Razuna 1.1.3 and learned that MySQL and Oracle have sometimes different syntaxes and approaches to the same SQL language. We thought that some of you might be interested in these and have gathered the ones we stumbled upon.

This should serve as a reference and for some coding problems you might run into. Some parts of this post is taken directly from the MySQL documentation, because it fits the current context and some is our own.

Our setup was a MySQL Server 5.0.45 and all tables are using the InnoDB storage engine. On the Oracle database side we use the 10g R2 release.

We did not want to change any global MySQL settings and wanted to keep it by the default installation. In this regard you might read a topic that could have been solved by adding a global setting parameter.

1. In MySQL tables names are case-sensitive on Linux, but case-insensitive on Windows

We were very surprised to find out that MySQL is case-sensitive for table names (on Linux). Honestly, I never thought that MySQL would have done something like that. We know that there is a global setting to change this, but as a neutral product we wanted Razuna to support MySQL independently of the server settings.

2. Substitute NULL value

Oracle’s nvl() Function
In Oracle/PLSQL, the NVL function lets you substitute a value when a NULL value is encountered.

The syntax for the NVL function is:

NVL( string1, replace_with )

string1 is the string to test for a null value.

replace_with is the value returned if string1 is null.

For example:

select nvl(supplier_city, 'n/a') from suppliers<strong>;</strong>

Mysql’s ifnull() Function
In MySQL, you can use IFNULL() in your SELECT statement is make the NULL any value you wish.

The syntax for the IFNULL function is:

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2.

For example:
select ifnull(supplier_city, ‘n/a’) from suppliers;

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:

  1. Create a table to hold the sequence counter and initialize it:
    mysql&gt; <strong class="userinput">[[code]]czo0MDpcIkNSRUFURSBUQUJMRSBzZXF1ZW5jZSAoaWQgSU5UIE5PVCBOVUxMKTtcIjt7WyYqJl19[[/code]]</strong>
    mysql&gt; <strong class="userinput">[[code]]czozMjpcIklOU0VSVCBJTlRPIHNlcXVlbmNlIFZBTFVFUyAoMCk7XCI7e1smKiZdfQ==[[/code]]</strong>
  2. Use the table to generate sequence numbers like this:
    mysql&gt; <strong class="userinput">[[code]]czo0NDpcIlVQREFURSBzZXF1ZW5jZSBTRVQgaWQ9TEFTVF9JTlNFUlRfSUQoaWQrMSk7XCI7e1smKiZdfQ==[[/code]]</strong>
    mysql&gt; <strong class="userinput">[[code]]czoyNDpcIlNFTEVDVCBMQVNUX0lOU0VSVF9JRCgpO1wiO3tbJiomXX0=[[/code]]</strong>

The [code class="literal"]]czo2OlwiVVBEQVRFXCI7e1smKiZdfQ==[[/code] statement increments the sequence counter and causes the next call to [code class="literal"]]czoxNjpcIkxBU1RfSU5TRVJUX0lEKClcIjt7WyYqJl19[[/code] to return the updated value. The [code class="literal"]]czo2OlwiU0VMRUNUXCI7e1smKiZdfQ==[[/code] statement retrieves that value. The [code class="literal"]]czoxNzpcIm15c3FsX2luc2VydF9pZCgpXCI7e1smKiZdfQ==[[/code] C API function can also be used to get the value. See Section 19.7.3.37, “[code class="literal"]]czoxNzpcIm15c3FsX2luc2VydF9pZCgpXCI7e1smKiZdfQ==[[/code]”.

You can generate sequences without calling [code class="literal"]]czoxNjpcIkxBU1RfSU5TRVJUX0lEKClcIjt7WyYqJl19[[/code], but the utility of using the function this way is that the ID value is maintained in the server as the last automatically generated value. It is multi-user safe because multiple clients can issue the [code class="literal"]]czo2OlwiVVBEQVRFXCI7e1smKiZdfQ==[[/code] statement and get their own sequence value with the [code class="literal"]]czo2OlwiU0VMRUNUXCI7e1smKiZdfQ==[[/code] statement (or [code class="literal"]]czoxNzpcIm15c3FsX2luc2VydF9pZCgpXCI7e1smKiZdfQ==[[/code]), without affecting or being affected by other clients that generate their own sequence values.

3. ERROR 1170 (42000): BLOB/TEXT column ‘field_name’ used in key specification without a key length

The error happens because MySQL can index only the first N chars of a BLOB or TEXT column. So, the error mainly happen when there is a field/column type of TEXT or BLOB or those belongs to TEXT or BLOB types such as TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, and LONGTEXT that you try to make as primary key or index. With full BLOB or TEXT without the length value, MySQL is unable to guarantee the uniqueness of the column as it’s of variable and dynamic size. So, when using BLOB or TEXT types as index, the value of N must be supplied so that MySQL can determine the key length. However, MySQL doesn’t support limit on TEXT or BLOB. TEXT(88) simply won’t work.

The error will also pop up when you try to convert a table column from non-TEXT and non-BLOB type such as VARCHAR and ENUM into TEXT or BLOB type, with the column already been defined as unique constraints or index. The Alter Table SQL command will fail.

The solution to the problem is to remove the TEXT or BLOB column from the index or unique constraint, or set another field as primary key. If you can’t do that, and wanting to place a limit on the TEXT or BLOB column, try to use VARCHAR type and place a limit of length on it. By default, VARCHAR is limited to a maximum of 255 characters and its limit must be specified implicitly within a bracket right after its declaration, i.e VARCHAR(200) will limit it to 200 characters long only.

Sometimes, even though you don’t use TEXT or BLOB related type in your table, the Error 1170 may also appear. It happens in situation such as when you specify VARCHAR column as primary key, but wrongly set its length or characters size. VARCHAR can only accepts up to 256 characters, so anything such as VARCHAR(512) will force MySQL to auto-convert the VARCHAR(512) to a SMALLTEXT datatype, which subsequently fail with error 1170 on key length if the column is used as primary key or unique or non-unique index. To solve this problem, specify a figure less than 256 as the size for VARCHAR field.

Note that [code class="literal"]]czoxNzpcIm15c3FsX2luc2VydF9pZCgpXCI7e1smKiZdfQ==[[/code] is only updated after [code class="literal"]]czo2OlwiSU5TRVJUXCI7e1smKiZdfQ==[[/code] and [code class="literal"]]czo2OlwiVVBEQVRFXCI7e1smKiZdfQ==[[/code] statements, so you cannot use the C API function to retrieve the value for [code class="literal"]]czo0OTpcIkxBU1RfSU5TRVJUX0lEKDxlbSBjbGFzcz1cInJlcGxhY2VhYmxlXCI+PGNvZGU+ZXhwclwiO3tbJiomXX0=[[/code]) after executing other SQL statements like [code class="literal"]]czo2OlwiU0VMRUNUXCI7e1smKiZdfQ==[[/code] or [code class="literal"]]czozOlwiU0VUXCI7e1smKiZdfQ==[[/code].

Not only that, but by default windows mysql converts table names to lower case on sql dump restores. Watch out for that gotcha.

4. Nested Selects need an alias

Select field
from (
select test
from test
) ALIAS

Oracle does not need the alias, but MySQL will throw you a error if you don't have an alias set!

5. ROWNUM in MySQL

The ROWNUM is badly missed in MySQL. But we have found something similar.

Oracle:
select rownum, foo from bar where rownum<5;

MySQL:
select @n:=@n+1, foo from bar where @N<5;

or (a simpler solution):

select foo from bar LIMIT 0,20;

6. Delete from with exists

One thing that hit us hard was that within Oracle you can use the same table within a subquery when doing a delete. When does this matter, you might ask? Look at the following code example:

DELETE FROM t1
WHERE id = 1
AND EXISTS ( SELECT * FROM t2, t1 WHERE ... )

This is a valid syntax with Oracle, but this will throw a error with MySQL saying "You can't specify target table 't1' for update in FROM clause". Of course, you could argue which one is right, but that is not the matter of this post.

The workaround for the above is to either get your syntax correct (by NOT using the same table again within the subquery) or then use INNER JOIN. Another workaround (as by the comment in the MySQL documentation) is to use temporary tables.

7. Constraints

Now the CONSTRAINTS hit us quite hard (again). As one could easily create constraints with Oracle, we were forced to have INNODB tables and each related key has to have a index to work! There are more requirements that have to be meet with MySQL for constraints to work. Best is to read it up at their Foreign Key Constraint documentation site.

Also, when you try to DROP a table then watch out for this "behaviour";

InnoDB does not allow you to drop a table that is referenced by a FOREIGN KEY constraint, unless you do "SET foreign_key_checks = 0". When you drop a table, the constraints that were defined in its create statement are also dropped.

8. Cast Types

Oracle allows VARCHAR(n) within a Cast. MySQL does not and you need to replace VARCHAR(n) with CHAR(n). MySQL documentation for CAST.

9. Concat differences

Concat is another syntax that Oracle handles completely different then MySQL. Look at this Oracle code:

SELECT NVL(t1.fieldid, 'myfield' || CAST(t2.order AS VARCHAR(1000))) as char(1000))) AS fieldid
FROM .....

Given this is a more complex example, but it shows you how you can "concat" and use cast at the same time to combine a field and assign this to an alias.

Now with MySQL the above will have to be like;

SELECT IFNULL(t1.fieldid, CAST(CONCAT('myfield',t2.order) as CHAR(1000))) AS fieldid
FROM .....

So, this is our list of the things we run into while adding support for MySQL to our open source Digital Asset Management Razuna. Did we forget something or have you encountered other things you would like to share? Don't hesitate let us know in the comment section.

Comments { 43 }

MySQL Landscape and other databases

Since we have just recently finished the MySQL integration for Razuna (coming in the imminent 1.1.3 release) we have learned our fair share of the MySQL Server and how it differs from other databases (we will post a separate post about our experiences adding support for MySQL in Razuna and how it differs from Oracle soon).

In any way, I just found this very informative blog post entitled “The New MySQL Landscape” which talks about the different options you got for deploying a MySQL Server. Ever heard of OurDelta or Drizzle?

Especially, the Drizzle project looks interesting since it is a re-making of MySQL started primarily by Brian Aker, who worked as MySQL’s Director of Architecture for years. Brian is now at Sun and, along with a handful of others at Sun and elsewhere, is ripping out a lot of the stuff in a fork of MySQL that doesn’t get used much, needlessly complicated the code, or is simply no longer needed.

Personally, we think the H2 database is a very reliable and powerful alternative to the MySQL database. H2 can be run as a embedded database, in-memory, clustered or as a server itself. H2 has even the capability to run in a specific database mode so that you wont have to change most of your SQL code to make it work with H2. The H2 database is built on Java and available under a open source license.

Comments { 11 }

Razuna open source Digital Asset Management and the future

It is the middle of December 2008 and it’s time to reflect a bit back to the last couple of months. Especially in regard to our release of Razuna – our open source Digital Asset Management (DAM). First off, I have to say that the download numbers are overwhelming and the feedback we have gotten from you is simply amazing.

Thus we want to share with you what the future of Razuna will bring to you and your organization.

Razuna and the Cloud
There is a lot of buzz around Cloud computing and putting your stuff in the cloud. Be it in moving your current server infrastructure to a virtual platform (Amazon EC2, Flexiscale comes to mind) or by using one of the many storage networks (Amazon S3, Nirvanix, etc.). Thus we are very happy to be able to announce that one of the new things that you will see in a future version of Razuna is the ability to store your assets with one of the storage providers like Amazon or Nirvanix.
In other words, Razuna will be the first Digital Asset Management that allows you to not only leverage your local hardware infrastructure but also use your current Amazons S3 or Nirvanix account. You can imagine that we are very exited about this and can’t wait until you get your hands on it. As mentioned we can’t say in which version this will be released, but it won’t be too long.

Razuna Workflow
A high demand on our feature list is the ability to have a certain set of assets be ready for a preview/review proccess. Version 1.1.4 of Razuna will be all about Workflow, Review and Preview of your asset collection. You will be able to publish collections with their own unique URLs, protect them with groups and users, have your reviewers comment of each asset and have a full log of your workflow ready at hand.

Razuna Version Control
Also, high on our feature list and wanted from customers is the ability to version assets. That means, that you will be able to upload different versions of the same asset and have it versioned. Thus being able to “move back” in time to a certain version. This will most probably make it into Version 1.1.4 or Version 1.1.5.

Razuna Database support
Currently, Razuna comes with high performance embedded database and with Oracle support. In version 1.1.3 there will be MySQL support built in as well. After that we are planning on bringing support for MS SQL and PostGreSQL. If there is a demand for any other database, please let us know.

Razuna GUI
Currently, we are in the middle of a light design overhaul of the whole Razuna experience. We trust that the new GUI will make it easier for our customers to manage their assets. The new design will most probably be incorporated in Version 1.1.4 of Razuna.

I hope you like some of the new features as much as we do making them. We encourage everyone to either leave a comment on this page or enter a feature request at our public issues system. Let us know what you think and let us make Razuna your Digital Asset Management of choice.

In any case, Razuna is all about openness and we welcome any party that wants to work with us on building the next generation Digital Asset Management or individuals who want to support us in our endevours.

Comments { 2 }

What the Open Source Barometer tells us

Alfresco, a company that publishes a open source ECM, does also feature a Open Source Barometer over at http://www.opensourcebarometer.org. According to their own saying; “The Alfresco Open Source Barometer survey is the largest open source enterprise infrastructure/stack survey. The third global survey covers data provided by over 25,000 of Alfresco’s 74,000 community members during the period of April to September 2008. “.

Since the Barometer shows us a door into the Enterprise open source usage what can we learn from it?

For Operating System we see that RedHat and Ubuntu are the clear leaders for Linux deployment. Look at how much Ubuntu has caught up. Ubuntu is not as long in the business as RedHat and I hear a lot of people taking on the Ubuntu Server. Looks like RedHat has to be on the look out. A obvious thing is that most will develop and test on Windows, but will deploy on Linux.

We see that Tomcat is still the most used Application Server, as it is our choice of server with Razuna, our very own open source Digital Asset Management, with over 67% compared to JBoss with 17%. The choice of database is with most the famous MySQL, followed by Oracle.

Thought that with the recent 10 million download for OpenOffice we see that MS Office is still the most used business application and despite the growing numbers of XEN marketing VMware is still the choice for most businesses to deploy Virtualization on.

One thing that really stood out for me is that Open Source is really at the “Long Tail”, that most businesses will go with a mixed stack (Open Source and proprietary software) and that Microsoft will have a impact on the Enterprise world and the Open Source community with their aggressive partnership. One thing I can tell from my own experience at a recent BizSpark introduction event is that MS will make inroads with a lot of businesses and startups.

A big thing for RIA and Adobe Flex is that 24% of the 25000 questioned community members take to Flex, that is second after 52% on Ajax and way ahead of the 8% of MS Silverlight.

Open Source Barometer III (Nov08)

View SlideShare presentation or Upload your own. (tags: alfresco open)
Comments { 7 }