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:
- 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); - 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( after executing other SQL statements like expr)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.
