Catégories: "Bases de Données"

Charsets in MySQL 4.1

Once you start messing with charsets in MySQL you eventually get to a situation where the default charset for your database is UTF-8 but you want to import old backups in their own charset, for instance ISO-LATIN-1.

But when you import it, MySQL thinks it's UTF-8 and all your specials chars get messed up.

Simple solution, add this at the end of your CREATE TABLE statements:

DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci

More in the MySQL Manual.

Note: this does not convert your data, this does tell MySQL that those particular tables are in another charset. I think, if you want to convert, you need to change the charset of your connection at the time you do the import (using the --default-character-set switch for example). Gotta re-check that though... but I think it can be overridden like this:

SET NAMES latin1

Hébergement PHP & MySQL: misère...

Le journal du net publie une sorte de comparatif sommaire des hébergeurs PHP/MySQL soit gratuits, soit low cost…

L’info a retenir, c’est que globalement tout le monde est à la traine. PHP est ultra stable en version 4.4 (ou au moins 4.3.10) et MySQL est utra stable en version 4.1 … et pourtant, quasiment aucun hébergeur ne propose ces versions à jour.

En gros, si vous voulez faire tourner une application critique qui nécessite une fiabilité élevée, il vous faut un serveur dédié… et encore, pas n’importe lequel, parce que sur les serveurs dédiés mid-cost – ceux qui sont fournis avec panneau d’administration – il est quasiment impossible d’upgrader PHP & MySQL sans tout casser…

Médiocrité, médiocrité… XX(

Crypter les fichiers sur un serveur XP

Attributs avancés

Crypter les fichiers sur le disque dur, ça a pour unique but que si on vous vole la machine on ne puisse pas lire vos données (mots de passe ou autre) en analysant le disque dur. Windows XP intègre un système pour celà: propriétés du fichier > attributs > avancé... > cochez la case "crypter". C'est gratuit et ça marche. Pensez-y avant de partir en vacances! :P (Pensez aussi à faire un backup! ;D)

Le problème c'est que ça ralentit aussi l'accès aux fichiers puisque Windows doit en permamence crypter/décrypter à la volée. Un peu le même problème qu'avec les antivirus qui scannent tout en permanence...

Alors pourquoi faire ça sur un serveur plutôt que de mettre le serveur sous clef... et pui surtout déjà: pourquoi avoir un serveur sous Windows XP!? :!::?:

C'est simple: ici (au bureau) on a des serveurs web (Apache/PHP/MySQL) sur les machines de développement, et on s'en sert pour tester les modifications en temps réel. Mais évidemment, ces machines de développement sont plus exposées au vol que les serveurs. (Et je vous parle pas des portables...)

Et on ne voudrait pas donner nos codes sources à n'importe qui, n'est-ce pas? :roll:

A partir de là, crypter les bases de données et les fichiers PHP pose deux problèmes...

Tout d'abord, une fois les fichiers cryptés, les services Apache et MySQL ne peuvent plus y accéder! Solution: changer la configuration des services en question pour les faire tourner sous le nom de l'utilisateur qui à crypté les fichiers. Moi, ou vous, en l'occurrence! :P [ Service > Propriétés > Connexion ].

Ensuite, il y a un problème de performances assez ahurissant. Crypter le fichier InnoDB de MySQL semble avoir un impact limité. En revanche, crypter l'ensemble des fichiers PHP peut (par le jeu des include) multiplier le temps de génération des pages web par 6 ! :(

La seule solution que j'ai trouvée à ce problème pour l'instant, c'est de ne crypter que des fichiers choisis. Les fichiers de config en particulier (ceux qui contiennent des mots de passe en premier lieu) ainsi que d'autres fichiers stratégiquement choisis pour qu'on ne puisse pas faire grand chose sans eux.

Une solution qui serait peut être plus intéressante serait d'avoir un script qui crypte l'ensemble des codes sources le soir en partant et les redécrypte le matin en arrivant... en partant du principe que les PCs ont moins de risques de vol aux heures de bureau qu'en dehors. Je me demande s'il existe des solutions élégantes pour ça... :?:

Pour finir, une petite astuce pour avoir l'option Crypter/Décrypter directement dans le menu contextuel du fichier: avec RegEdit, il faut aller dans HKEY_LOCAL_MACHINE\ SOFTWARE\ Microsoft\ Windows\ CurrentVersion \Explorer\ Advanced puis créer une Valeur DWORD nommée EncryptionContextMenu et lui assigner la valeur 1.

Quelques ressources supplémentaires:

The illusion of DataBase Abstraction Layers or Classes

Many people think that DB abstraction is cool. I get emails about this topic all the time. People suggesting we add a DB abstraction layer to b2evolution, or offering to do so themselves.

So why haven't we added one yet? Well, simply because DB abstraction doesn't work! :!:

DB abstraction layers will mostly hide the specific interface semantics between the application and the database. Thus you don't have to worry about the specific system call to connect to each particular DB. OK nice. So what? That's only the easiest part in porting an app from a DBMS to another... (BTW, we use a class (/evocore/db.class) in b2evolution for this too. You just have to slighlty alter this class if you want to connect to another DBMS than MySQL).

More sophisticated layers will also translate between different datatypes from one DBMS to another. But since datatypes tend to standardize among all popular DBMSes this is getting less of an issue as time passes by...

Finally, we get to the point where it hurts: the SQL syntax! There are vitually no two DBMSes that share the exact same SQL syntax, except for the most basic SELECT, INSERT, UPDATE and DELETE statements! >:-[ And it doesn't seem to be standardizing really... some still won't support even the minimalistic SQL 92 standard! >:XX

Just check it out with your 2 favorite DBMSes:

  • How do you perform a LEFT OUTER JOIN?
  • How do you concatenate columns into a single result string?
  • How do you limit results in the WHERE clause to a regular expression?
  • How do you handle the fact that when you INSERT and the primary key already exists, you want to UPDATE instead? (MySQL's REPLACE INTO syntax...)?

These are just a few common examples, but they're probably already enough to show you why you DB abstraction layer won't magically translate from you first favorite DBMS to your second... You'll have to rewrite many queries too... and sometimes you won't find equivalent functionnality (subqueries?) and you'll have to write extra application code...

Not to mention triggers and stored procedures where you wouldn't even dare to dream about some kind of compatibility.

The most advanced DB abstraction I have ever worked with was ODBC. Yes that thing actually did SQL syntax translation! But it depended on specific drivers to implement translation against a standardized syntax defined by Microsoft.

So today when I see some open source library pretending to perform DB abstraction and I can't find anything closely related to SQL syntax translation, I won't even consider it... It's useless to me. The real difficulty with handling multiple DBs is rewriting the SQL queries, triggers and stored procedures... it's not connecting to yet another fancy DBMS and SELECT 'hello'... |-|

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! :>>