Nerd Life

We had this quite interesting situation at work today: I export an UTF-8 MySQL database into an UTF_8 SQL file. I *binary* FTP the file over to him. He plays the SQL in PHPmyAdmin all configured for UTF-8. He then checks the data in PHPmyAdmin: special chars display right under an UTF-8 page encoding. He checks under the command line: the DB contents seem to be UTF-8 encoded too...

Now he goes to our app, pulls out some data... and gets a special chars mess... although the html page is displayed as UTF-8. If we force the display to Latin-1, the special chars display correctly again.

The reason for this is that there is actuallly a charset translation taking place between the mysql client (which is PHP here) and the mysql server.

We found we could fix this by editing my.cnf and specifying the following:

# The default character set that will be used when a new schema or table is
# created and no character set is defined
default-character-set=utf8

...instead of the latin-1 he had there for some obscure reason.

However, there must be a more explicit way to have PHP retrieve the mysql data as UTF-8, even though we could not find that in mysql_connect() or somethig alike.

Guess, we'll have to investigate the PhpMyAdmin code and see how those guys do it. Btw, this reminds me of a conference by Rasmus Lerdorf where he said the code for PhpMyAdmin was pretty clean and should be studied. Yep, we definitely gotta do that! ;)

Running an app under PHP 4.3.8 and trying to connect to a MySQL 4.1 database can be pretty frustrating. By default it gives you a message like this:

"Client does not support authentication protocol requested by server; consider upgrading MySQL client".

The problem is that by default PHP's MySQL module is compiled with an older MySQL client library. MySQL has a manual page about this.

I use the OLD_PASSWORD method. I connect to MySQL under root with MySQL Query Browser and I issue the following command:

set password for 'demouser' = old_password('demopass');

(via Cédric) Jim Gray [Links gone]:

"Larry Ellison announced that Oracle is now running entirely on Linux. But he didn't say, "Incidentally we're going to run all of Oracle on MySQL on Linux." If you just connected the dots, that would be the next sentence in the paragraph. But he didn't say that, so I believe that Larry actually thinks Oracle will have a lot more value than MySQL has. I do not understand why he thinks the Linux problems are fixable and the MySQL problems are not."

MySQL offers pretty useful functions when you want to manipulate days:

• You can add a time interval to a date value with ADDDATE() or DATE_ADD()
• You can subtract a time interval from a date value witf DATE_SUB()
• You can find the interval between two dates with  DATEDIFF()

It's often easier to compute this stuff directly in MySQL rather than in PHP.

For all date functions see the MySQL Manual.

mySQL has been planing to implement stored procedures and triggers in mySQL 5 for some time now...

But lately, it looks like it's just gonna get better than expected...

Via Simon Willison (great blog!), from chromatic's wrap-up of OSCON day 3:

Brian Aker, fresh on his new job as Senior Architect at MySQL, shocked the world (or, at least, me) when he announced that he'd embedded Perl in MySQL and was using it for stored procedures a couple of years ago. Of course, it did segfault rather often. Fortunately, it's highly mature now. In his talk on "Making MySQL Do More", Brian showed the embedded function API. You can write new functions for MySQL in Perl, Python, PHP, and Java. (Keep asking him about Ruby.) You can link to C libraries; he's used Image Magick and zlib. I'm excited about how easily you can modify queries — SELECT DIFF(foo) ... anyone?

However, if mySQL alone gets that interesting, I wonder what are they going to do with SAP DB ? Weren't they planing on merging both databases somehow?

Anyway, the real question is: how long is it going to take again for hosting companies to offer mySQL 5 as a standard feature on their hosting plans... :?: Most of them still run the ridiculously dumb 3.23 version! Even worse, most customers seem to be happy with it!

Isn't this ironic, on one side databases are becoming a commodity and on the other side, people think being able to INSERT / UPDATE / SELECT (if not just SELECT) is all you need to call something a DBMS...