Catégories: "Développement"

MySQL Data Integrity Enforcement Caveats

Okay, I desperately lack time to write full articles posts lately, so I'm going to make this quick! :P

Background: Suppose you have a table named Songs and a table named Genres. Genres only contains Genre IDs and Genre Names. Songs contains all sorts of data, but at some point there is one field (let's call it Song_Genre_ID) that points to the Genre ID. Get the picture? I'm sorry, I don't have the time for an actual picture of this. That "pointer" is called a Foreign Key. The Genre_ID, being the Primary key.

Data Integrity Enforcement means that MySQL is going to prevent you from putting any crappy value into the Foreign Key (FK) that would not exist in the refered Primary Key (PK). It will also prevent you from deleting a Genre if there are songs pointing to it. The bottom line is: if you're serious about your DB, you can't do without!

Now how would you enforce the integrity? Basically with something like this (but you may want to check the MySQL manual for details, right? :P):

ALTER TABLE Songs
ADD CONSTRAINT FK_any_name
FOREIGN KEY (Song_Genre_ID)
REFERENCES Genres (Genre_ID)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

Now, here's my point: from my experience I have found that a hell lot of things can go wrong when you try to add that constraint, and it can be a nightmare to find out what's going wrong! As a matter of fact, you'll find out MySQL's error messages aren't very helpful >:XX...

So here's a checklist to follow in order to find out what's been going wrong:

  • If the error message is even less helpful than you could possibly imagine, check your version of MySQL. Versions 4.1+ definitely have better messages than previous versions.
  • Check that both of your tables are of type InnoDB. Don't even try it otherwise.
  • Check that the PK you are refering to is actually defined as the Primary Key for its table.
  • On versions < 4.1, check that the the FK is being properly INDEXed before creating the foreign key.
  • Check the data types of the FK and the PK. They should be exactly the same. And I really mean *exactly*! Check the "UNSIGNED" attribute also. If it's signed on one side and unsigned on the other, it won't work! (I recommend using INT UNSIGNED for most of your FKs and PKs).
  • Check that any existing values in the PK column have matches in the FK column at the time you try to create the constraint.
  • Check that the DEFAULT value for your FK also has a match in the FK column at the time you try to create the constraint.

Okay I hope I didn't forget anything. Of course, if you find another reason for the constraint creation to fail, I'd love to hear about it! ;)

Good luck! :>>

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
+InnoDB
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 config.inc.php 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
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! ;)

PowerDesigner/AMC est en train de me pourrir mon aprem :(

J'utilise le module Physical Architect ("Développeur SQL" en VF) pour générer une base MySQL 4.0. Je mets plein de références dans tous les sens et ensuite je génère le script de création de la base. PowerDesigner fait ça très bien, il crée bien toutes les contraintes de clef étrangère etc.

Mais, car il y a un mais: il semble vérifier poru chaque clef étrangère, si le champ référençant fait partie d'un index. Si c'est le cas, tout va bien. Si ce n'est pas le cas... PowerDesigner insiste pour créer un index sur ce champ alors que je ne lui ai rien demandé à ce bougre!!!

Et le pire de tout, c'est qu'il le crée avec un erreur de syntaxe! (CREATE index_name au lieu de CREATE index_name ON table_name). Résultat, mon script de création est pourri de création d'index aussi gênantes qu'inutiles.

Si vous avez compris ce que je viens de dire, vous êtes très fort. Si en plus, vous savez comment reconfigurer la défition mySQL de PowerDesigner pour qu'il arrête de déconner, alors vous êtes un Dieu!

Bon, voilà, j'ai râlé un ptit coup, je peux retourner me prendre la tête :P