Souvenirs d’une très bonne soirĂ©e…
1 May 2008 par pĂ©bĂ©En regardant le blog de Colin, je ne peux m’empecher de penser Ă ces très bons moments passĂ©s Ă Heidelberg…
VoilĂ la photo:

En regardant le blog de Colin, je ne peux m’empecher de penser Ă ces très bons moments passĂ©s Ă Heidelberg…
VoilĂ la photo:

3 keynotes ce matin:
C’est la 1er fois que j’assiste Ă une prĂ©sentation de Mr Schwartz et je dois dire que j’ai bien apprĂ©ciĂ©… C’est officiellement parti…
les sessions commencent!
ET C’EST PARTI pour l’Ă©dition 2008 de la MySQL UC. Quel plaisir de retrouver les membres de la communautĂ©! Le premier jour est consacrĂ© aux tutoriaux. J’ai choisi de suivre:
Nous allons parler de Datamarts toute la matinĂ©e, pour l’instant nous en sommes Ă la partie technique… j’attends la partie consacrĂ©e Ă MySQL. ![]()
Many things in the current IT world are based around hard facts, solid experience and studied techniques. Unfortunately this tends not to be the case when it comes to application developers making database decisions. This is not a criticism of application developers per se, their expertise lies in the app technology, but more a problem with development process and a misunderstanding of the role of the database as the underpinnings of data oriented (as opposed to object oriented) application architecture.The general process of modern agile application development proceeds along fairly set lines of iterative feature based and hopefully test driven development. This approach of getting something working with a suite of tests around it which enable rapid refactoring and rapid development run counter to most Big Design Up Front (BDUF) methodologies, most notably the much maligned waterfall model and the general approach taken to most database driven methods. To truly be an agile database developer in this brave new development world implies a level of clairvoyance beyond most of us, and requires an understanding of future application direction and projected data growth which is beyond that which can be expected of application developers and their product managers. To ignore this difference in requirements of the agile developer and the data modeller/DBA will invariably lead to scalability and performance issues as the project moves forward through its multiple iterations.
We are not saying here that the focus and philosophy of the agile development team and that of the database designer/administrator are incompatible, more that there is a difference in the needs and aims of the two groups and that this difference needs to be recognised as such. This is not always the case in the necessary drive to shift development paradigms. The larger the project, the more apparent this becomes. Changing code is not the same as changing data. A database at the core of a complex multi tier application will usually be supporting many different access paths, from the OLTP requirements of a running user driven application to the reporting requirements of management and maintenance as well as a suite of custom administration interfaces, data feeds in and out as well as the requirements for failover and disaster recovery, and refactoring, recoding and changing requirements is not as simple as that of single parts of the overall codebase.
While there are database methodologies to support agile development, generally the current processes have database design/deployment/administration as worrying separate, and the realms of all the pieces of getting a product from the back of a restaurant napkin to global server deployment need to incorporate the whole iterative development process. We should not be in the position of giving the DBAs the code to deploy when the iterative cycle ends. This is simply unacceptable from an architecture and process point of view. What we have here is a series of failures in using the best people for the job.
As mentioned above, application developers are not generally the best people to be structuring and modelling the data. Data structures, no matter how well abstracted, are fundamentally tied to the underlying technology. Sensible design and architectural decisions can only happen if the data specialists are incorporated into the agile development teams themselves. We can, and need, separate database administration teams, but also, these teams must be part of, or have significant input into the development process, else all roads lead to project misery, to poor use of software/hardware and the continuation of the ignorance loop, as mistakes in data structure and design are rarely fed back up to the original designers. Adding features to code and refactoring as we go works for code, it rarely works in practice for data. It is not so much the new features that is the problem, it the short-termism which the process unconsciously encourages which lies at the heart of the problem. Large data volumes require a degree of foward planning that is often lacking from our short Sprint focused design decisions.
Ignoring data modelling disasters, most projects start off with a fairly well understood and structurally sensible data structure (bear with me here) for the first few iterations of the system. These first set of requirements generally have the data model supporting them happily. The issues only usually really arise as the codebase and feature list grows, along with the growing datasets. Data structures that support smaller volumes of data do not necessarily scale linearly and a lack of understanding of the changing nature of the system will cause problems for the future of the application due to information not flowing back from the DBAs about the current state of the system, as well as the new demands being placed on it from the continuous feature development of the agile processes. Changing requirements are all part of the agile world, and are part of the power of this type of development, and additional features pose less of a problem than feature modifications, but the focus of short iterative steps can easily lead to a loss of focus on the greater need of the ability of the database and the datamodel itself to support the changing application.
In conclusion, for our agile data development processes to succeed, we need to use the skills where they are best suited. Database abstraction layers need to be tested by the database specialists, not in isolation, but within the application context itself, and any fundamental design decisions should be made by the people who understand the systems involved, at all levels.
Voici mon problème du jour : Comment lancer un script (de maintenance par exemple) qui fait appel Ă mysql, sans stocker en dur le nom de l’utilisateur et le mot de passe (ce qui est mal, très très mal). Le but est que seul un utilisateur privilĂ©giĂ© (je n’ai pas forcĂ©ment dit root ! je pense plutĂ´t Ă un compte système comme mysql par exemple) puisse lancer ce script.
C’est plutĂ´t facile, et je fournis trois solutions pour la peine :
[client]
host = localhost
user = votre_user
password = votre_mot_de_passe
socket = /var/run/mysqld/mysqld.sock
$ my_print_defaults -c /etc/mysql/maconf.cnf client
--host=localhost
--user=votre_user
--password=votre_mot_de_passe
--socket=/var/run/mysqld/mysqld.sock
Le sĂ©jour n’est pas encore fini mais je peux dĂ©jĂ vous livrer quelques sentiments de cette immersion dans le monde des dĂ©veloppeurs MySQL. L’ambiance y est excellente, tout le monde est heureux d’ĂŞtre prĂ©sent. NĂ©anmoins ce ne sont pas des vacances pour autant. Le petit dĂ©jeunĂ© est pris en 7:30 et 8:15 et est suivi par de nombreux team meetings. L’après midi se concentre plus sur des sessions universitĂ© MySQL.. Ces sessions Ă©tant vraiment orientĂ© dĂ©veloppeur je dois dire que j’ai vu en quelques jours plus de code C++ que je n’en ai vu en des annĂ©es… Enfin c’est un vrai plaisir de voir les nouveautĂ©s prĂ©sentĂ©es par leur auteurs. Cet Ă©vĂ©nement rĂ©uni plus d’une centaine de “MySQLers”, principalement dĂ©veloppeurs mais aussi quelques formateurs, QA,… et une dizaine d’invitĂ©s externes…
Les différences avec la conférence utilisateurs:
En parlant de User ConfĂ©rence, la prochaine est dĂ©jĂ prĂ©vue du 15-18 Avril 2008, toujours Ă Santa Clara et j’y serai Ă©videment. Je dois trouver ce que je pourrai y prĂ©senter avant le 30 octobre…
MySQL AB est une compagnie mondiale. La plupart des employĂ©s sont de part le monde et travaillent de chez eux. Les Ă©changes se font le plus souvent par emails, blog, tĂ©lĂ©phone, IM et IRC. Une fois par an, ils se retrouvent pour un “MySQL Developer Meeting”. Cette annĂ©e encore, cette rencontre a lieu Ă Heidelberg en Allemagne et dĂ©bute aujourd’hui 19 Septembre 2007 pour finir dimanche. Suite au billet de Kaj qui a rendu la nouvelle officielle, nous y avons appris que certaines personnes de la communautĂ© sont invitĂ©s Ă y participer. J’y serai Ă partir de ce soir et essayerai de vous tenir au courant des nouveautĂ©s qui ne sont pas confidentielles. En effet j’ai du signer un accord de confidentialitĂ©.
Je viens de lire rĂ©cemment SĂ©curitĂ© PHP 5 et MySQL, Ă©crit par Damien Seguy et Philippe Gamache. J’ai Ă©videment commencĂ© par les parties que me tiennent Ă coeur, MySQL et en particulier:
Je vous le conseille, vous trouverez un condensĂ© de tout ce qu’il faut savoir Ă ce sujet pour protĂ©ger vos bases de donnĂ©es. Merci messieurs!
Je vais m’attaquer Ă la partie PHP ![]()
J’ai eu un petit problème sympa Ă rĂ©soudre. En rĂ©sumĂ©, nous avons 2 tables t1 et t2.
mysql> select * from t1;
+——+——+
| id | name |
+——+——+
| 1 | a |
| 2 | b |
| 3 | ab |
| 4 | bc |
+——+——+
mysql> select * from t2;
+————–+———+
| dependent_id | base_id |
+————–+———+
| 1 | 1 |
| 3 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 3 | 2 |
| 4 | 2 |
+————–+———+
Nous ouvrons une transaction (T1) et nous exécutons la requête suivante.
SELECT * FROM t1 JOIN t2 WHERE t1.id=t2.dependent_id AND t2.base_id=1 FOR UPDATE;
+——+——+————–+———+
| id | name | dependent_id | base_id |
+——+——+————–+———+
| 1 | a | 1 | 1 |
| 3 | ab | 3 | 1 |
+——+——+————–+———+
Nous ouvrons une transaction (T2) et nous exécutons la requête suivante.
SELECT * FROM roles WHERE id=2 FOR UPDATE;
Et alors que les 2 requêtes ne sont pas sensées travailler sur les même lignes, T2 attend la fin de T1? Comment est ce possible?
Pour comprendre vous devez analyser le plan d’exĂ©cution.
mysql> explain SELECT * FROM t1 JOIN t2 WHERE t1.id=t2.dependent_id AND t2.base_id=1 FOR UPDATE;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
| 1 | SIMPLE | t1 | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where |
Voila l’erreur, MySQL fait un full table scan (type ALL) pour exĂ©cuter cette requĂŞte et ainsi bloque toutes les lignes. T2 est ainsi obliger d’attendre la fin de T1. Pourquoi?
Ayant seulement 4 entrĂ©es dans cette table, l’optimiseur de MySQL prĂ©fère un faire un full table scan plutot qu’utiliser un index. De la j’ ai insĂ©rĂ© d’autre entrĂ©e dans la table t1, et le plan d’exĂ©cution a changĂ©.
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 217 | Using where |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t2.dependent_id | 1 | |
MySQL bloque ainsi seulement la PK et fait bien du ‘row level locking’. T2 peut aboutir sans problème. CQFD!
Kaj rĂ©agit aux discussions d’hier. Mais qu’est ce que cela va changer pour le user landa? Va-t-il se tourner vers la version enterprise? Sachant que j’ai encore des serveurs qui tournent MySQL du 3.23, du 4.0 et du 4.1, cette restructuration aura-t-elle un impact pour moi? Non je ne crois. Pour des grosses sociĂ©tĂ©s, tous les bugs sont trouvĂ©s en phase de test… si la version courante est buggĂ©, je prendrais la prĂ©cĂ©dente.