By using STRAIGHT_JOIN instead of “regular” inner join I made my sql query optimized again:

STRAIGHT_JOIN is an inner join where MySQL optimizer will not change the order of tables when joining them. It will always read the left table first and then the right table.

In general, you should  put to the left  the table which would give smaller result set in the final result.

Since premature optimization is the root of all evil, you should use “regular” inner join, monitor you application performance and check MySQL slow log. And if the sql query “suddenly” become slow – now you know how to fix it.

My mate who was working with me on our initial implementation – Ante ‘Ivoks’ Karamatić (Chief executive at Init) got drunk with Kurt von Finck (Chief Community and Communications Officer for Monty Program Ab) in Dallas last week, who passed a good word to Michael (“Monty”) Widenius (MySQL’s original author and co-founder of MySQL AB) to listen our cries for help. Monty convinced Alexander Barkov (Lead software developer at Sun Microsystems working on MySQL) to give us little help on whole Croatian ordering issue. As a result, utf8_croatian_ci and ucs2_croatian_ci collations were created and added to MySQL 6.

After a pleasant chat with Monty and Bar, they were good enough to help us with a MySQL 5.1 patch which implements full Croatian ordering in utf8_croatian_ci and ucs2_croatian_ci collations. Woohoo! :)

But the bad news is that it will take fair amount of time before MySQL-5.6 (or 6.0 for that matter) will go GA, so one have to wait before it will be possible to download a production version of MySQL with “real” Croatian support.

If you really need Croatian support, you can try patching MySQL server as we did.

More details about the patch can be found here:

• Bar’s eplanation: http://www.collation-charts.org/articles/croatian.htm
• Bar’s patch for MySQL-5.1: http://www.collation-charts.org/articles/utf8_croatian_ci.diff

Since Alexander Barkov was so kind and provided a patch for MySQL 5.1, Ante created packages for Ubuntu. He also slightly (needs further testing) modified that patch so it works with MySQL 5.0. If you need this feature, go add this PPA to your sources.list: https://edge.launchpad.net/~ivoks/+archive/mysql-hr/.

After you apply the patch, you can try it out using my test database dump. If everything went ok “use croatian; SET NAMES ‘utf8’ COLLATE ‘utf8_croatian_ci’; select rijec from test_croatian order by rijec;”, should produce output like this (switch browser view to utf8).

Any feedback from the Croatian MySQL community is greatly welcomed. Please write your comments to <Alexander.Barkov[at]Sun.COM>. Thanks!

Proof of conecpet:

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.0.51a-hr1-log | 
+-----------------+
mysql> use croatian; SET NAMES 'utf8' COLLATE 'utf8_croatian_ci'; select rijec from test_croatian order by rijec;
+--------------+
|  rijec       |
+--------------+
| Aboriđin     |
| Aboriđini    |
| Ante         |
| Branimir     |
| Cipela       |
| Čazma        |
| Ćevapčići    |
| Džak         |
| džak         |
| Džamija      |
| džamija      |
| Đak          |
| đak          |
| Đevđelija    |
| Inat         |
| Init         |
| Inozemstvo   |
| Interes      |
| Injekcija    |
| Ipsilon      |
| Kutina       |
| Livno        |
| Lovor        |
| Ljubav       |
| Ljubljana    |
| Neven        |
| Nivas        |
| Nosorog      |
| Njivice      |
| Onomatopeja  |
| Šišmiš       |
| Zagreb       |
| Žaba         |
+--------------+

To repeat – the stopword list is a list of most used words in english language. Common words such as “some”, “little”, “let” or “then” are stopwords and do not match if present in the search string. Basically, fulltext searching for any of the stopwords would return (almost) all the entries, so MySQL ignores those words to reduce result pollution and for efficiency. If the word starts with stopword, that word is returned, however if your word IS stopword, you are out of luck. Here is a list of build in stopwords in MySQL 5.0/5.1. Yeah, well this is so very exciting, but whole universe DOES NOT speak and write just english.

Last time we had problems with stopwords, there was no way to disable them without recompiling the mysql engine from the source. On production servers where you need to stay away from recompiling custom builds for maximum compatibility that was no go.

Today we again encountered stopword problem. Client complained that he cannot find video from artist “Let 3” or artist “Little Boots” (Victoria Hesket). To my surprise, in no time – I’ve found out a prefectly legal and easy to apply solution. Just add this to your .cnf file, restart mysql engine and rebuild indexes:

ft_stopword_file = ""

I addition to that, if you haven’t allready, lower the min word to 3 (ft_min_word_len=3) to be able to search for 3 letter words.

Happy searching! :)

This is old and confirmed bug #25162. The problem was in incompatible syntax for key definition in CREATE TABLE. MySQL 5.0 supports only this syntax:
{INDEX|KEY} [index_name] [index_type] (index_col_name,...)
while MySQL 5.1 preferrs this one :
{INDEX|KEY} [index_name] (index_col_name,...) [index_type]
..which lead to incompatible dump.

We have this situation – workstations run MySQL v5.1.33 and our development and production servers are still on 5.0.51a-3ubuntu5.4 (Ubuntu LTS) version. We can’t upgrade MySQL on servers that easily so I first tried updating my workstation to latest windows version 5.1.37. mysqldump which comes with it is v10.13 Distrib 5.1.37, and it produced exactly the same incompatible output. I even tried x86 and x64 versions – with same incompatible results. I submited a bug report to MySQL because it seems that this bug was fixed in Windows installer version but not in without-installer version which I used to upgrade my local installation (bug #46598).

This was going nowhere so I downgraded my workstation to MySQL 5.0.84. I was then able to do proper export from my workstation and import on our development server. I have no idea why mysqldump in 5.1 version didn’t get –compatible=50 switch option. If somebody knows a workaround for this, do share!

If you cant’ upgrade, try exporting from 5.1 with –compatible=mysql40 switch but doing so will heavily downgrade your dump so beware.

We tried implementing it on our own for couple of times, but without any luck. The problem lies in fact that Croatian language (Serbian and Bosnian too) have digraph characters (single characters consisted of two characters – lj, nj and dž). And without proper support for those, we will never be able to sort things right (a-b-c-č-ć-d-dž-đ-…i-j-k-l-lj-m-n-nj-…u-v-z-ž)

What does it take to implement Croatian utf8 collation? It takes modifying source code beyond our knowledge (we tried creating new collation with Vietnamese as a base for digraphs as a pair of basic latin letter + accented Latin letter).

AFAIK the countries which would benefit from the same implementation (alongside Croatia) are: Bosnia, Serbia (for latin charset) and Monte Negro (for latin charset). So please, if you can – spread the word! I think that support for this would be appreciated by thousands of MySQL developers in our region who are now forced to use hacks from ’90 to get correct sort order. :)

I’ve submitted S4 feature request to MySQL – http://bugs.mysql.com/44523 and
I’ve posted a feature request/proposition on official MySQL dev forum, so we will see what happens. It certainly wouldn’t harm if you would sign in to bugs.mysql.com and MySQL dev forum and reply to my feature request and topic with “Yes please” or something similar. It’s free, and it can make difference. :)

On 26 February 2008, Sun acquired MySQL AB. Today, Oracle Corporation announced acquisition of Sun Microsystems. All that acquiring is nice, but what’s in it for us – MySQL developers? Well, we will have to wait and see. I wouldn’t have anything against switching to Oracle. :) Oracle was my first real database I used back in ’90s (Oracle 7), and after reading 2 meters of Oracle books, I was pretty happy with PL/SQL, triggers, views and stored procedures. Switching to MySQL was hard. :)

What is certain – Oracle does not plan to shutdown MySQL. According to Oracle FAQ about the acquisition: “MySQL will be an addition to Oracle’s existing suite of database products, which already includes Oracle Database 11g, TimesTen, Berkeley DB open source database, and the open source transactional storage engine, InnoDB,”.

What do you think will happen to MySQL?

Unfortunately for U2 fans, online ticket sales maybe did start, but they sure did end pretty fast. But not because web shop run out of tickets, but because web shop and organizers of the concert ran out of professionalism.

Look guys, if you decided to run so strong advertising campaign across couple of countries which loudly and clearly says: “Ticket sales start online on Friday at 00:01”, I suppose one who does all that, would also build a website which can sustain such number of customers?

Well, I guess not.

Exactly the same thing happened some time ago when Croatian Football Association launched online reservation of world cup tickets (if I can recall correctly). I am not big sports fan but many of my friends are and they were very pissed.

Servers are so dirty cheap, and downtime costs a lot of money (nerves, reputation…), Especially if downtime happens a minute you launch your service. My personal preference/advice regarding server configuration in time of special events or service launch is – tend to over engineer your network! EC2, EBS, S3 (Amazon Web services) and other cloud computing solutions do their job pretty good in situations like this.

Otherwise you get this beauty…

Thanks to everybody for such a nice feedback:

• tportal.hr – Centrala hrvatskih medija
• net.hr – Ručno izabrane vijesti
• totalportal.hr – Centrala.hr – novi informativni portal
• kroativ.com
• 3kolone.com – Sto mu gromova! Centrala.hr
• Plan B
• webmajstori.net

We are redesigning one large website and naturally, some content from old website has to be migrated to new site. Since we don’t have access to their database, we have to manually copy content from old website’s HTML source. Client’s old site is in UTF-8. Imagine following entry in old HTML source:
[ftf w=”480″ h=”105″]Ðakovo…
[/ftf]
This is a select tag with list of cities in Croatia, the city in example is Đakovo. So as I said, we are copy-ing large amounts of data from old HTML’s, and we do not care why old site displays this city as “&#208;akovo” and not as “Đakovo”. After we pasted “Ðakovo” into our engine, we momentarily started to get all sorts of different errors. The most bizare one was comming from our utf8_strtolower function which had to return string “đakovo”. Instead it was returning string “ðakovo”. Wicked!

After some digging, we found out that there was nothing wrong with our utf8 php methods nor our mysql database. We found out that the letter Ð (hex d0 00) we pasted from HTML was just not the letter Đ (hex 10 01) we needed. Somebody (or something) very brilliant, used letter Eth instead of standard Croatian letter D with stroke.

Eth (Ð, ð; also spelled edh or eð) is a letter used in Old English, Icelandic, Faroese (in which it is called edd). In the Unicode universal character encoding standard, upper and lower case eth are represented by U+00D0 and U+00F0. These code points are inherited from the older ISO 8859-1 standard. In HTML, eth is represented by the Latin character entities Ð and ð.

Đ (lowercase đ) is a letter of the Latin alphabet, formed from D with the addition of a bar or stroke through the letter. This is the same modification that was used to create eth (ð), but eth is based on an insular variant of d while đ is based on its usual upright shape. In Unicode the letter is represented as U+0110 LATIN CAPITAL LETTER D WITH STROKE and U+0111 LATIN SMALL LETTER D WITH STROKE.

So there you have it. No matter the letters look the same, beware what you copy/paste. :)

MySQL Cluster is currently not supported on Microsoft Windows, you’ll have to install it somewhere else. Once you have it running, it’s time to import some data. Database schema of a my test dump mostly used MyIsam storage engine and few InnoDB tables. Before the import, change Engine=NDB in the dump and be aware, import takes ages even on a really fast servers (200 kb dump = cca 1 min ??).

My best guess is, you will see a lot of HY00 errors when you start your import. Be patient, check one by one. Most of them are caused by non supported features of NDB but can be avoided.

Here is a brief coverage of reasons for the problems which I encountered:

• NDB does not support foreign keys or FULLTEXT indexes or indexes on text columns (indexes on char/varchar are ok).
• Attribute names are automatically truncated to 31 characters. Database names and table names can total a maximum of 122 characters – the maximum length for an NDB table name is 122 characters, less the number of characters in the name of the database of which that table is a part.
• The maximum number columns and indexes per table is limited to 128.
• Temporary tables are not supported.
• Every table using the NDBCluster storage engine requires a primary key; if no primary key is defined by the user, then a “hidden” primary key will be created by NDB. This hidden primary key consumes 31-35 bytes per table record.
• The maximum permitted size of any one row is 8KB. Note that each BLOB or TEXT column adds 256 + 8 = 264 bytes towards this total.

The last error is really nasty one and will cause you to rethink your database schema and make a lot of changes to your application):

ERROR 1118 (42000) at line 794: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8052. You have to change some columns to TEXT or BLOBs


Some help:

• The lack of foreign keys, can be resolved by using triggers (Enforcing Foreign Keys Programmatically in MySQL).
• You need a lot of RAM in your severs because all data is in the RAM, unless you are using the ‘DATA ON DISK’ feature of MySQL 5.1.x. In that case you can have the non-indexed data on disk.
• When calculating Cluster memory requirements, very useful is ndb_size.pl utility. This Perl script connects to a current MySQL (non-Cluster) database and creates a report on how much space that database would require if it used the NDBCluster storage engine. When you run the script, compare the suggested parameter values reported for your database to the default values for each shown here. If the default is higher than the recommended value from ndb_size.pl do not adjust the value. If the recommended vales are lower than the defaults use slightly larger numbers.

NDBCluster is something I’ll definitively have my four eyes on and watch it grow. Bugtracker on mysql is crawling with bugs, but this is something to be expected. I’ve tested 5.0, and there is a lot of stuff allready fixed in 5.1. My biggest disappointment was the fact that new nodes can’t be added to the cluster without shutting it down first. My conclusion is, that It is not yet ready for prime time. When it will be? when 5.1 is released into production I guess. Until then… We are on our own. :)

Looking at the mysqldump of a database I was a bit surprised to see Croatian letters screwed up beyond recognition. For example letter “š” was represented as “ÄąÄO„” (4 bytes), “č” was “čO” (two bytes)… etc. OMG! All our tables are “CHARSET=utf8”, we force “AddDefaultCharset utf-8” in Apache and all of our markup uses “charset=utf-8” for content type encoding. BUT, we unintentionally left one very bad call in our config file which got triggered if site was running on development server – “SET NAMES ‘latin2’ COLLATE ‘latin2_croatian_ci”. The production config had SET NAMES utf8, but development didn’t. Oh boy.

Ok so now I had a real mess – utf8 tables with utf8 data in them stored as re-encoded latin2 but in utf8!? I tried converting the dump from ISO8859-2 to UTF8 but that just made things worse, some of characters now used 6 bytes which isn’t good. The solution was pretty straight forward. I converted the dump from UTF8 to ISO-8859-2 and I got real UTF8 again. I imported the converted dump and changed for good config files to “SET NAMES ‘utf8’ COLLATE ‘utf8_general_ci”.
iconv to the rescue:

[ftf w=”400″ h=”150″]
mysqldump -u root -p db > weirdo-dump.sql
iconv -f UTF-8 -t ISO_8859-2//TRANSLIT weirdo-dump.sql > dump-latin2-aka-realUTF8.sql
mysql -u root -p db < dump-latin2-aka-realUTF8.sql[/ftf]

• http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html
• http://mysqldba.blogspot.com/2006/07/rebuilding-40-data-into-41-data.html
• http://textsnippets.com/posts/show/84
• http://www.oreillynet.com/onlamp/blog/2006/01/turning_mysql_data_in_utf8_t.html
• http://gentoo-wiki.com/TIP_Convert_utf8_to_UTF-8_in_MySQL
• http://www.phpwact.org/php/i18n/utf-8/mysql

my.cnf

[mysqld]
back_log = 75
skip-innodb
max_connections = 500
key_buffer = 384M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 7200
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 1000
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size =128M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = MyISAM
[mysqld_safe]
nice = -5
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

For years I’ we been degrading in ways of database usage, only working on my left and right joins. For many years I was prisoner of adjacency list model of my data hierarchy. Well, neither the projects I worked were that database creative. On our last project (www.smsoglasi.com) I had to do a slightly more complex category structure so I decided to use the long lost and forgot – preorder tree traversal algorithm. Joe Celko is by far the most respected author on the subject and there are better places on the net to read more on the subject.

Must read:

Database wizard Joe Celko’s SQL for Smarties (Worship Joe Celkoooo!)

Managing Hierarchical Data in MySQL

Storing Hierarchical Data in a Database