Category Archives: PHP / SQL

MySQL SELECT query with LIKE case sensitive ?

Today at work I helped an intern with an interesting problem I would like to share.
he was doing this kind of query on a MySQL server :

SELECT description FROM service WHERE description LIKE '%cloud%';

It returned these lines :

cloud customer 1
cloud customer 2

but does not return these two lines he was expecting :

new Cloud infra
Cloud customer 2

LIKE should be case insensitive … What was wrong ?

Continue reading “MySQL SELECT query with LIKE case sensitive ?” »

Migrating a MySQL database to another server

A customer asked me to copy a whole database from one mysql server to another.
A few years ago, I would go with the classic mysqldump + import solution, but it is very slow, especially the import part (because MySQL insert buffer is monothread). One can also use mysqlimport (LOAD DATA INFILE), but it is still quite slow… When using a standard SQL dump, I measured a speed of 1MB/sec for reimport … Quite long if you have gigabytes of data !

So I tested xtrabackup for this. This tool is already in heavy tests internally but to my mind, is not quite ready for production. But let’s try this for this specific task of migrating a database.

First, you need to install xtrabackup and all other binaries included (especially xbstream). You’ll also need at least version 5.5.25 on remote host (you’ll see why). And last but not least, InnoDB must run with

innodb_file_per_table=1

In this blog post, I’ll name “server A” the source, and “server B” the destination.
My SQL data is stored in /srv/mysql.
On server B, create a destination folder, for example /tmp/test
This is because you need to import data to a temporary directory, where there is .
On server A, launch the following command :

time innobackupex --export --databases 'mydb' --no-lock \
--stream=xbstream --tmpdir=/tmp --use-memory=128MB \
--user=backup --password=XXXXX --parallel=4 /srv/mysql \
| ssh root@10.0.0.224 "xbstream --directory=/tmp/test -x"

A few explanations :

  • –export : add specific data that would be useful for reimport
  • –databases : specify database to copy.
  • –no-lock : by default, a FLUSH TABLES WITH READ LOCK is emitted, to ensure the whole backup is consistant. I chose not to use it, as I do not care about binary log position of the backup (used in replication).
  • –stream=xbstream : use xbstream as streaming software (more powerful than tar)
  • –use-memory : memory that can be used for several tasks
  • –parallel=4 : dump 4 tables in parallel
  • I pipe stream to ssh and execute xbstream on the remote host.
  • I do not use –compress as network is not a limit in my case.

Dump is complete ! I achieved a rate of 11.3 MB/sec with this : far better than mysqldump / mysqlimport ! I’m sure we can be faster by tuning a few things.
Take care of file owner : copy was done with root, but I’m sure your mysql data is owned by someone else (or it should be !).

Then, prepare data for export :

xtrabackup_55 --prepare --export --target-dir=/tmp/test

This will “prepare” data (applying innodb log …), and especially will create .exp files that contains table metadata. This is mandatory for import in the next step !

We can see xtrabackup working :

[...]
xtrabackup: export option is specified.
xtrabackup: export metadata of table 'mydb/performer' to file `./mydb/performer.exp` (1 indexes)
xtrabackup: name=PRIMARY, id.low=443, page=3
[...]

Ok, now we need to create the new database, and import scheme.
On server B, do :

CREATE DATABASE mydb;

We also need a temporary database (you’ll see why later …)

CREATE DATABASE test;

Then, we have two choices :

  1. create all tables, discard all tablespaces, and import all
  2. do the same operation, but for each table

I first thought that there were a bug with method 1 (https://bugs.launchpad.net/percona-server/+bug/1052960) but it appears this bug is also hitting if you do method 2. Nevertheless, I’ve created a script that handle method 2 all by itself.

NOTE THAT YOU NEED Percona Server 5.5.25 v27.1 if you don’t want to hit the bug I was talking about. This bug crashes MySQL, and leaves it in a state where it cannot start again … You’ve been warned.
First, let’s dump all tables schemas. On server A, do :

mysqldump --routines --triggers --single-transaction --no-data \
--host=serverA --user=root --password=xxx \
--result-file=schemas.sql mydb;

And import it ON THE TEMPORARY DATABASE :

cat schemas.sql |mysql -uroot -hSERVER_B -p test

Execute following statement in MySQL (server B) to tell MySQL that we will import data :

SET GLOBAL innodb_import_table_from_xtrabackup = 1;
/** If you have a version < 5.5.10, despite what I just
 * said about minimum version required, query is : **/
SET GLOBAL innodb_expand_import = 1;

Then, let’s use following bash script : http://www.olivierdoucet.info/blog/wp-content/uploads/2012/09/expand_import.sh.txt

A few explanations :

For each table, we do the following :

ALTER TABLE xxx DISCARD TABLESPACE;
mv xxx.ibd xxx.exp /srv/mysql/mydb;
ALTER TABLE xxx IMPORT TABLESPACE;

This bash script is using .my.cnf file in your user dir for credentials (or other default values). Please ensure you have access to the destination database with these credentials.
ALL STEPS ARE REQUIRED. If you miss something (the set global, chown, chmod …) you’ll probably get an error (like ‘got error -1 from storage engine’). At this point, you’d better start the process over (and drop the destination database, which is incomplete).

If import works, you can see lines like this in log :

[...]
InnoDB: Import: The extended import of mydb/mytable is being started.
InnoDB: Import: 2 indexes have been detected.
InnoDB: Progress in %: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 done.
[...]

Note that when importing huge InnoDB tables, there is (was) a lock on the dictionary while scanning the ibd file (the data file), so it may lock the whole server … If operation took too long, server crashed. This is bug https://bugs.launchpad.net/percona-server/+bug/684829
This bug has been fixed since, and as you need at least version 5.5.25, you should not hit this problem.

 

Conclusion

This method is 10 times faster than mysqldump / mysqlimport. But As you can see, there are huge risks and still bugs remaining. The dump part is really safe, so I would recommend you to first test the import on a dev server before doing this in production.

Xtrabackup is really an amazing tool, but it still suffers somme nasty bugs. I’m sure I’ll use it in production in a few months, when it will be perfectly stable for all tasks.

 

Migrer de PHP 5.3 à PHP 5.4

Les développeurs de PHP ont choisis de se focaliser sur les performances plutôt que sur les nouvelles fonctionnalités sur la version 5.4 ; mais cette version est également un grand saut sur beaucoup de fonctions dépréciées, qui sont maintenant totalement supprimées.

La migration de la version 5.3 vers la 5.4 est donc une étape délicate (bien plus que de 5.2 vers 5.3), et de nombreux points sont à vérifier avant de se lancer dans l’opération.

Continue reading “Migrer de PHP 5.3 à PHP 5.4” »

Migrer de PHP 5.2 à PHP 5.3

PHP 5.2 n’est plus maintenu depuis janvier 2011, il devient donc tant de migrer sur la version 5.3 (surtout que dorénavant, la 5.4 est sortie en stable …).

Mais une migration doit toujours se préparer, afin qu’elle se passe le mieux possible sans grosse interruption de service. Voici donc une présentation des incompatibilités entre ces deux versions.

Continue reading “Migrer de PHP 5.2 à PHP 5.3” »

Customer case : finding an unusual cause of max_user_connections

The last few days were very busy dealing with a problem on the MySQL server of a customer. My company is offering fully managed hosting services, so it was up to us to investigate the troubles. I’ll try to explain some of the checks I’ve done ; maybe this can give you some ideas when you also deal with mysql troubleshooting.

Continue reading “Customer case : finding an unusual cause of max_user_connections” »

“Certains de mes champs POST sont ignorés par PHP !”

J’ai eu ce genre de message plusieurs fois de la part de clients ces derniers temps, et je pense que cela peut concerner beaucoup de monde donc je vous donne les explications.

Le symptôme est simple : “Les X premiers champs envoyés en POST sont bien lus par PHP, mais à partir d’une certaine limite ceux-ci ne sont plus disponibles.” Ou formulé autrement : “il me manque une partie des champs POST”.

Le plus curieux dans cette histoire, c’est que votre code PHP fonctionnait très bien il y a quelques semaines, et là d’un seul coup, plus rien. Que s’est-il passé ?

Vous n’êtes pas en tord, et pourtant il va vous falloir faire des modifications sur la config de PHP 🙂 Le coupable ? Un trou de sécurité dans PHP assez sérieux, qu’il a fallu corriger rapidement et donc … de manière un peu abrupte. Les curieux pourront lire le rapport de sécurité ici :  http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2011-4885

 Pour résumer en quelques mots : il s’agit d’une attaque par amplification. Une personne avec de mauvaises intentions peux envoyer un formulaire avec un certain formatage à votre serveur web, et PHP va mettre plusieurs minutes à la traiter … Multipliez ça par quelques centaines, et voilà votre hébergement dans les choux pour plusieurs heures. Je ne vous expliquerais pas dans cet article comment exploiter cette faille 😉

Pour limiter le champs de l’attaque, l’équipe de développement PHP a mis en place une limite simple : A partir de 1000 champs POST (d’ailleurs, c’est la même limite sur des cookies ou GET), PHP ignore tous les champs suivants. Donc si vous avez un très grand formulaire avec des milliers de champs, il va vous en manquer un bout.

Normalement, PHP vous prévient qu’il a dû tailler dans le vif avec ce message d’erreur : Input variables exceeded 1000. To increase the limit change max_input_vars in php.ini.

Cela vous explicite donc le correctif : il suffit de modifier (ou plutôt ajouter, car je doute que vous l’ayez déjà) la variable max_input_vars dans votre fichier de configuration php.ini (qui se trouve généralement dans /etc sous Linux) et de lui mettre une grande valeur qui permettra à votre formulaire de fonctionner.

Et c’est tout ?

Oui, mais prenez garde : cette limitation était une limite de sécurité. Donc augmenter la limite vous expose à l’attaque initiale. D’expérience, vous pouvez monter à 5000 ou 10000 si vous avez des configurations relativement récentes. Au delà, c’est à vos risques et périls.

in_array et le mode strict

[La fonction in_array|http://fr.php.net/in_array|fr] peut avoir un comportement tout à fait étrange lorsqu’on utilise la syntaxe par défaut, qui fait une vérification non stricte : @@var_dump(in_array(‘mouhahaha’,array(0,1,2))); // bool(true)@@%%% Et oui, mon texte est dans ce tableau d’après PHP. Etonnant, non ? Tout simplement parce que PHP va essayer de convertir (cast) les types avant de les comparer. Ma chaîne de caractère, à un moment, vaut ‘true’ et mon 1 vaut ‘true’ aussi, d’où l’égalité.%%% Par contre, si on met les nombres entre quotes comme ceci : @@var_dump(in_array(‘mouhahaha’,array(‘0′,’1′,’2’))); // bool(false)@@%%% Car les chaines étant de même type (string), aucun cast n’a lieu. Pour éviter les effets de bords et autres mauvaises surprises, utilisez le mode strict qui va également comparer le type de la variable. Pour cela, ajouter un 3e argument à in_array comme ceci : %%% @@var_dump(in_array(‘mouhahaha’,array(0,1,2), true)); // bool(false)@@

Stockage de nombre à virgules dans MySQL

Pour stocker un nombre à virgule (un flottant, ou float en anglais) dans MySQL, il existe plusieurs types de colonnes. Mais attention : ils ne sont pas tous identiques. Petite démonstration simple : %%% * Prenons une table ‘test’, avec entre autre un champ de type FLOAT(8,2). * Executez la requete suivante : %%% @@INSERT INTO `test` (id, flottant) VALUES(4,’446351.74′);@@ %%% * Puis relisez la ligne : @@SELECT * FROM `test` WHERE id=4@@ Voici le résultat:%%% @@446351.75@@ Comment ?? ,75 et non ,74 comme je l’ai demandé ? Et oui, normal vu la méthode de stockage qu’utilise MySQL.
Continue reading “Stockage de nombre à virgules dans MySQL” »