Categories: "Databases"

MySQL features by version breakdown

I could not find anything like this on the net, so I thought I'd make my own chart and share it... ;)

MySQL version 3.23.58
4.0.22 4.1.7 5.0
Release date 11-sept-03 27-oct-04 23-oct-04 not stable yet
Row level locking yes yes yes yes
Transactions yes yes yes yes
Foreign keys yes yes yes yes
UNION no yes yes yes
Subqueries (derived tables) no no yes yes
Multiple table DELETEs no yes yes yes
Unicode UTF-8 support yes yes yes yes
Column level charset support no no yes yes
Timezone handling no no yes yes
Full text indexing (MyISAM only) no UTF-8 no UTF-8 yes yes
Stored procedures no no no yes
Rudimentary triggers no no no yes
Updatatable views no no no yes
Server side cursors no no no yes
Master/slave one way replication yes yes yes yes
Replication over SSL no no yes yes
Clustering (not available on Windows) no no yes yes

Note: I have not included MySQL 3.23 without InnoDB because that version can do vitually nothing interesting. It's a pure joke. Unfortunately, it is the kind of joke hosting providers like to try on you when they claim they offer you a "DBMS"... yeah right... call it an electronic rollodex, no more :>>

I'm a little bit too lazy for styling this right now, sorry.
If you notice an error, please be so nice and report it. Thanks ;)

Exporting MySQL databases

PhpMyAdmin does a pretty decent job at exporting MySql database with their structure and/or their data to a plain SQL file. One thing bugged me though: it encloses every table/column/whatever name in backquotes like in `addr_city` varchar(50). Who needs this? Are there really people who put spaces, commas, quotes and special chars in their database object names?? |-|

Today, I finally fixed this. You need to open and change this line:

$cfg['Export']['sql_backquotes'] = FALSE;

Life is good! :D

Another lousy PHP/MySQL /charset issue...

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

...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! ;)

Using PHP 4.3.8 with MySQL 4.1

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');

XML vs. Relational Databases

Lately, I've seen quite a few people claiming XML to be the definitive way to store data, because it's supposed to be the most flexible format...

Others claim RDBMSes have been providing an efficient solution to data storage for years or decades and there was no need for something new.

This argument just doesn't make sense! XML and RDBMSes are two different solutions, both efficient... but solving two different problems!

Think about it. One tries to structure documents. The other one tries to structure relational but basicly tabular data.

Asking which of XML or an RDBMS is best, is like asking which of Word or Excel is best! :!: Best for what? :?:

I guess the whole confusion started when people started to use XML to exchange tabular data in the context of webservices. Don't get fooled: tabular data can temporatily be converted to a document (XML) when it must be transfered or exchanged, but there is still no reason to store it that way at one end or the other of that transmission...