Importing Unicode Data into MySQL

Phew! I have finally solved a big unicode problem for importing chinese characters into MySQL. The problem is like this: My client has a huge Access database containing English and Chinese data which I need to migrate into MySQL.

In the past, I used MySQL Migration Toolkit to import from Access into MySQL. However, this time, it doesn't work because the toolkit turns all the Chinese characters into ???. Yup, exactly that! Question marks.

So I tried to export the Access table into CSV. I opened it with Wordpad and it looks great. So after playing around with the CSV to turn it into SQL statements, I am ready to use command line import style to import the data in. The idea is to go to MySQL command prompt and type:

mysql -u root -p database_name < data.csv

This ought to do the job... but it doesn't. It has some very very funny MySQL Syntax error which is like some funny characters. I find it extremely funny so I opened up the file using MySQL Query Analyser instead. What I discovered is that the data is suddenly shortened significantly to just 3 funny characters. My best guess is that wordpad actually compresses the data when you saved it.

Never mind! Chill! I rework the data in MySQL Query Analyser and save it properly. It is neat to know that MySQL Query Analyser will save your sql file in proper UTF encoding. With the proper UTF file, I tried the command line import again.

There shouldn't be any more problem... but there is. Now MySQL told me "Error 2006: SQL Server has gone away". What? Gone away? For a smoking break? OMG! After some searching on the net, I realize that the problem could be with the MySQL server setting to automatically close a connection when there is a large transfer. So I fired up MySQL Administrator GUI, go to "Startup Variables", under "Advanced Networking" tab, I checked and set "Max Packet Size" and "Max Buffer Length" to 5M each.

Back to the command line import, YUP it works! FINALLY! I am writing this out to throw it in the net hoping that it will just help some poor soul who is searching for such a solution.

Joomla Access Control

One of the greatest shortcoming of Joomla is that it does not have an editable access control feature. That means you can't really create new access groups nor can you change what each group can access.

The only to edit Joomla's access groups is to programmically hack it. In this posting, I will outline how to edit the access permissions of the existing backend access groups in Joomla.

All the access roles are defined in /includes/gacl.class.php and in the function gacl (line 97 of the file). You will see many statements like this:

$this->_mos_add_acl( 'action', 'edit', 'users', 'manager', 'content', 'all' );

To add an access rule, simply monkey see monkey do:

$this->_mos_add_acl( {Action}, {Action Value}, 'users', {Access Group}, {The Component Name}, {The Access} );

Not much point in explaining the variables in this statement which you will see why in the next step. We have defined the rule but that is not enough. The component/program has to honor it. Hence, in the component which is to impose this rule, you will need to add something like this:

global $acl, $my;

if (!($acl->acl_check( 'action', 'edit', 'users', $my->usertype, 'content', 'all' ))) {
mosRedirect( 'index2.php', _NOT_AUTH );
}

You just do a checking and if it is not true, redirect to a "Not Authorized" web page. You may notice that the number of arguments in the function acl_check is the same as the function _mos_add_acl. Yup! The thing is this: the function acl_check simply checks if you have added a rule through _mos_add_acl that has the exact arguments and values as the acl_check function! So if the acl_check is called with the same arguments as the _mos_add_acl, then it will return true. ACL is quite simple in Joomla right? =)

With that, you should now understand that the arguments for _mos_add_acl is not very important but more for readability. Just good programming practice, it is always good to give meaningful names.