Tag Archives | mysql

Razuna and MySQL 5.6.x

Just a short head up that the recent MySQL 5.6.x releases do not seam to be compatible with the initial setup script of Razuna.

We are not sure why this is so, as there has been no changes to the scripts in any way. Problem is that the indexes are all of the sudden shortened in the MySQL 5.6.x releases, meaning they can not hold large columns.

For now, we recommend to install or stay on the MySQL 5.5.x releases.

Comments { 0 }

Razuna Virtual Server Image available for different environments

razuna_logo_200As of immediately, Razuna is available as a virtual server image for Amacon EC2, VMWare, Xen, Parallels, KVM (Kernel Virtual Machine) and VirtualIron. With this offering, you can deploy one of the most used open source Digital Asset Management System, within minutes in your environment.

But the Razuna Virtual Server Image is not just a virtual image you can download. We have gone a step further and have teamed up with the fine people at Elastic Server. With their service you are able to build your own Razuna Virtual Server on your choice of Operating System (Ubuntu 8.10, Ubuntu 8.04 and Daisy Linux available for now), add additional applications (MySQL, Apache, etc.) and configure your hardware, like available RAM, Hard Disk size and Network settings. Once done, your individual Razuna Virtual Server Image will be build and made available for download.

Actually, it has never been easier to deploy any Digital Asset Management System before. Try it now, head over to the Razuna download page and configure your own Razuna Virtual Server Image.

About Elastic Server
CohesiveFT (the people behind Elastic Server) is the leader in automated software assembly and a complement to virtualization and cloud computing solutions. The Elastic Server® platform is a web-based “factory” for assembling, testing, and deploying custom stacks and servers to virtual machines or clouds. These custom Elastic Servers can be comprised of open source, third-party or proprietary software components from multiple vendors, saved as templates, updated, augmented, or redeployed in minutes. Made-to-order application stacks mean faster assembly and limitless configurations.

About Razuna
Razuna is a enterprise digital asset management/media asset management with an integrated web content management that delivers and makes management of your digital assets a simple task! By using Razuna you get the benefit of it being free and open source and supported by a professional company.

With Razuna, your information can be collected, consolidated, verified, filtered, mined and always be available and secure. Razuna does all this while requiring less hardware and fewer administrators, for the lowest overall cost of ownership. A powerful solution like Razuna will achieve significant efficiency gains as well as consistency throughout the organization.

Since Razuna is based on open standards (J2EE/CFML/SQL/XML) you can rest assured that Razuna can scale with your business. For international organizations that hold already thousands of assets, Razuna can take up on your existing assets with powerful import and export tools.

Comments { 45 }

Razuna 1.1.3 released – even more power to the open source digital asset management

razuna-logo-400.pngIt gives us much pleasure to bring Razuna in its latest version 1.1.3 to the world. It is our part to say thank you to the growing community around our open source digital asset management and end the year with this great release.

We are very exited about Razuna 1.1.3, since it brings full support for the MySQL database, includes the final release of the underlining application server (OpenBD) and an administration for the server itself. Also, it includes most of the requests and bug fixes that our community has told us about.

Please find below a short summary of the new features and fixes of this release. For those who are already running Razuna 1.1.2 and want to upgrade to Razuna 1.1.3, should read the Upgrade documentation before doing so.

Razuna and MySQL

A lot of users have asked that we add support for the MySQL database. Thus, we are very happy to finally bring it to Razuna. It was not an easy task to do, since we run into a couple of issues with the SQL syntax itself and how MySQL differs from other databases. We already posted about our findings how MySQL is different to Oracle or H2 (in Oracle mode).

In any case, this is all technical mumbo-jumbo and all you should know is that Razuna runs now perfectly with MySQL (as it does with the embedded and the Oracle database). Support for other databases, will come in future versions and will depend on the demand of the community. You can cast your vote, or add a new feature request and bug report, at the public available Issue Tracker of Razuna.

General improvements

Overall improvements have been made to this release. To list each one, is not the purpose of this post, but rather give you a small summary. Some of the improvements are;

Updated the Flash Player to the latest version, we are now running the embedded database in server mode (thus allowing other connections to the same database), uploading and adding assets is now much faster, we moved the general DAM folder to a global position (thus allowing for greater compatibility for future updates) and finally we resized the pop-up windows to better suit users with smaller screens or lower resolutions.

General Fixes

We pride ourselves with how fast we fix any reported bug. Usually our customers receive a bug fix within 24 hours (usually within hours). For Razuna 1.1.3 we could, with the help of the community, squash some bugs. Here is a summary of some of the most important ones fixed in this release;

Users that have spaces in the executables paths would run into errors (actually, you should never have spaces in your executables paths, but Windows allows you to do so), Descriptions and Keywords sometimes failed while adding assets, Adding many assets from Scheduled Uploads failed, WMV and AVI failed to play in the Flash Player (they still don’t play in the Flash Player, but now we re-added how to play them), and finally adding ZIP archives did not extract the folder structure and added assets correctly.

Razuna and 2009

As already outlined in a previous post, we shared some of the new features of a upcoming Razuna version. In 2009 we will take digital asset management and your assets to the cloud, meaning that you will be able to leverage the Amazon S3 and/or Nirvanix data storage services. Also, we will bring review/preview and workflow to Razuna so that you and your team will have a complete review process right within Razuna. Another high demand request will come in 2009, that is you will get version control for all your assets within Razuna.

There has never been a better time to start managing your assets with Razuna, the open source digital asset management alternative!

As always you can get the latest Version directly from Razuna.com. Razuna is available in a Standalone Server download or as a WAR/EAR download to deploy on a existing J2EE server. Customers who already have Razuna deployed will get a notification within the system that a new version is available.

Razuna is a professional digital asset management/media asset management with an integrated web content management that delivers and makes management of your digital assets a simple task. Razuna is licensed under a dual-license (AGPL and commercial) and can be downloaded at http://www.razuna.org. The Wiki is available at http://wiki.sixsigns.com and the public Issues Tracker at http://issues.sixsigns.com.


Comments { 76 }

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;

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> CREATE TABLE sequence (id INT NOT NULL);
    mysql> INSERT INTO sequence VALUES (0);
  2. Use the table to generate sequence numbers like this:
    mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
    mysql> SELECT LAST_INSERT_ID();

The UPDATE statement increments the sequence counter and causes the next call to LAST_INSERT_ID() to return the updated value. The SELECT statement retrieves that value. The mysql_insert_id() C API function can also be used to get the value. See Section 19.7.3.37, “mysql_insert_id().

You can generate sequences without calling LAST_INSERT_ID(), 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 UPDATE statement and get their own sequence value with the SELECT statement (or mysql_insert_id()), 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 mysql_insert_id() is only updated after INSERT and UPDATE statements, so you cannot use the C API function to retrieve the value for LAST_INSERT_ID(expr) after executing other SQL statements like SELECT or SET.

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 }