soledad penadés

Following the discussion in the previous post (Reasons for using UTF-8) I thought it could be interesting to gather a series of steps needed to get a UTF-8 friendly environment.

I’m going to focus on php and mysql, because using mysql and ruby/rails and utf8 tends to be kind of easier (specially since newer Rails versions suggest mysql which charset to use when connecting automagically for you), but the advice can be applied to both platforms in any case.

In your preferred editor

Make sure your editor is set to use UTF-8, specially when editing templates and any other file which is used for building output content. If you include any non-ASCII content (for example, accented words) and they are mixed with more UTF-8 content (from other templates or sources), things will get messed up.

In your html/xml code

Make sure the document’s charset is specified.

In HTML documents you would accomplish this with the content/type meta tag, which you should place in the header:

In XML documents this is done in the XML declaration, which needs to be placed at the immediate beginning of the document:

I have heard recommendations for placing the page title immediately after the Content-type has been declared, so that browsers can switch to the right charset immediately if the page title includes any non-ASCII code, but it sounds a little urban myth to me.

I have also seen people recommending to specify the encoding in every form you have in your site, but I haven’t found any difference between doing it or not.

In Apache

Make sure the content is being served as UTF-8. A good AddDefaultCharset utf-8 should do. You don’t need to change all your hosted content into UTF-8 if you don’t want to, charsets can be configured per virtual hosts as well.

In the database

Make sure mysql is configured to use UTF-8 at server level.

Or if you can’t modify my.cnf (shared servers anyone?), before issuing any other query, and right after you’ve connected to the server, execute

It seems otherwise mysql will not recognize properly the character set that the client is using (!!) and will return bad data.

Of course, when you create the tables, make sure that every column which can include text data (this includes TEXT, VARCHAR, etc) is using utf8 as well.

In php

I rarely need to send a header specifying the content-type, but if things are not working totally fine you could also try to manually specify the content type with the header:

Or application/xml, whatever you need!

In Flash (or Flex)

Do not use systemCodepage. Flash assumes XML is UTF-8 so if everything else has been properly set up, Flash will be served UTF-8 content, which is what it expects, and we’ll be happy :-)

One more thing

Although generally it’s a good idea to run a validator, it’s even a better idea if you’re doing utf-8 stuff and want to make sure you’re not outputting bad stuff inadvertently. Validators are picky (even more if we speak about XML validators) and will cheekily reveal your inappropriate characters.

Missing something? Anything wrong?

I’m not an expert and I may easily have forgotten something so if you think something is missing or plainly wrong you know what to do ;-)

And before you ask – I don’t have any experience with Pylons, Django, TurboGear, Zope or whatever your favourite Python framework is, so feel free to share with us your experience.

I was trying to run bake script (for cakephp!) and it started complaining about not finding /var/mysql/mysql.sock – but why this path? I already had problems with mysql socket and ruby on rails. In that time, I was using xampp for apache, php and mysql, so the mysql socket was inside xampp folder and I could solve it thanks to ccm (see the post if you feel curious).

But past week I decided I was fed up with xampp and not knowing where are the things, and more specially, not having a working version of Apache with mod_rewrite, so I went for the hard way and compiled and installed all from scratch (apache, php, mysql). Then what happened is that mysql socket is now in /tmp/mysql.sock but for some reason cake (and obviously php) is looking for the socket in /var/mysql/mysql.sock. Why, I don’t know – since the application I’m developing works perfectly (I presume that’s because it’s running in a virtual host and thus php doesn’t try to connect with localhost but with http, as it believes that it’s not localhost actually).

In any case, it’s just bake which fails.

Well, it was just bake which failed, since I decided to solve it all quickly. Did it want a socket in /var/mysql/mysql.sock?

There you go! Open a terminal and…

cd /var
sudo mkdir mysql (if a mysql directory doesn’t exist there)
sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

bye bye error! now enjoy bake!

This trick is maybe dirty but I’m fed up of running ./configure and friends. If you know why it failed before, you can leave a comment… and I’ll appreciate it much :)

I was retaking my self taught course of RoR yesterday and I started getting this error when accessing the database, each time I wanted to generate the scaffolding of one model:

Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’

Oh well, I thought, aggghh. When I tried to install php, ror, mysql, etc… I did a great mess here, and although I hadn’t had any problem with php (once I installed xampp), seems like I have a separate mysql installation (the remaining of the first, separated one, I tried first).

After trying to find out what was the problem with that socket, specially because it shouldn’t look at that file, but at one under xampp files, I found a quicker solution:

In the config/database.yml file, I replaced all appearances of localhost with 127.0.0.1.

And voila, it started working. Although it’s not very efficient, as it makes every connection to go through TCP, and not via a socket, as it would do if the host was localhost.

I think I’ll have to take my time once and clean this horrible mess, but by the moment it works.

You probably know there’s a quick way of dumping your whole database to a file, with mysqldump. This generates a .sql file containing all the data you would need to restore your database when moving to another server, or when doing a backup.

Anyway, if you have ever tried to restore a database which contained foreign keys it’s very probable that you had problems with it, as the dump just outputs all the tables in alphabetical order, not taking into account whether one depends on another and so on. So you are likely to get an error like this: “Cannot delete or update a parent row: a foreign key constraint fails”.

Scaring, isn’t it?

First solution could be reordering manually the dump, putting the tables in a reasonable order in a way that they can be created without breaking any constraint. But this is a pain-you-know-where!!

There’s a better way of doing this. In your sql dump, add this statement at the beginning:

and this other at the end:

and voila! run that dump with your favourite method – may it be phpmyadmin or mysql command line client itself (this is the fastest one and the recommended method if you have a very big database, otherwise phpmyadmin can time out and you’ll end with an incomplete database).

Now this method is not only avoiding problems with foreign keys, but is also faster, as it does not check things that shouldbe ok – as they come from a supposedly ok database.

Isn’t it cool? Enjoy it! Now you don’t have excuse for not doing backups of your database!

It seems like some people is not aware yet of the fact that with mysql you can have not only transactions but also foreign key constrains, apart from other features which were usually found only on expensive products like Oracle, MSSQL server, etc…

The problem is that as these features weren’t available until some months/years ago, there’s a vast amount of php software which does not use them, and hence php developers [which read/use other developers code] do not know about. So all majority of code examples and real-life applications do not take care of adding constraints or transactions in the SQL data model declaration. Instead, they do all the checkings and validations in the php code. And that’s also what I do.

Is it right? I wouldn’t say yes, but I wouldn’t say no. My workmate is completely obsessed with foreign keys, he can’t stop adding those constraints to every pair of tables that are linked… and he also can’t stop complaining about me not setting those constraints when declaring the tables. Why do I do that?

Mainly for convenience. In this world of undecided clients and blurred targets, we are going to possibly mess up with the database tables lots of times, at least at the beginning of the project when the data model tends to not to be very well defined. Also, for having foreign keys and transactions and all that funky stuff, your tables need to be InnoDB, which are said to be slower than MyIsam (which are the default ones that mysql uses when you do not indicate any table type during table creation), mainly when accecsing data in read only mode, not updating or inserting it – and that’s how websites works, don’t they?

Sure, we all have been taught that a good data model must be consistent and preserve data integrity as one of the main goals (apart from storing data in itself, of course!). One could argue that foreign keys are data in themselves, as they mean something; you can’t simply remove them without losing information somehow. But as I said before, during development I think it’s going to be harder if you are playing with the tables and the data inside them. Lots of integrity constraint failed errors, lots of lost time because of that.

I am thinking of a compromise solution: not using foreign keys during development but adding them when everything is almost definitive (where everything means the data model, etc…). As we also make checks in our business layer (I hate speaking with that terminology!), we can’t lose any functionality. And if there was an error, it will show!
I would love to listen any suggestion/opinion/truth/whatever. Please take into account that I am approaching the foreign key issue from a practical point of view; I would prefer to avoid metaphilosophical discussions about the database designs. Or at least I’ll avoid those which are not human readable. Thanks!