Personal blog of Yzmir Ramirez

I’ll be presenting at MySQL Meetup in Raleigh,NC February 21,2012. The talk with be about Optimizing MySQL Configuration which I believe is a great topic for my first talk at this meetup group as it covers something every MySQL user has to deal with, also being something both beginner and advanced MySQL Users can learn a lot from. I’ll also bring High Performance MySQL 2nd edition for a drawing as well as exclusive discount codes for MySQL Conference and Expo 2012 in Santa Clara. See you there.

This week, time, logger and Doctrine collectors were tweaked and refactored. Meanwhile, the Form component fixed lots of bugs, specially with the improvements of ChoiceListInterface and its implementations.

Development mailing list

• Call for volunteers for Netbeans Sf2 plugin development

Symfony2 development highlights

Master branch:

• 3b260d2:
  refactored the collector to separate the loggers per connection
• e37783f:
  [DoctrineBridge] refactored the query sanitization in the collector
• 2a998e0:
  [FrameworkBundle] handle disabled csrf protection in the PHP templating form helper
• 3a1699a:
  handle disabled csrf protection in the Twig FormExtension
• 87b16e7:
  [Form] greatly improved ChoiceListInterface and all of its implementations (EntityChoiceList was adapted, the methods getEntities(), getEntitiesByByKeys(), getIdentifier() and getIdentifierValues() were removed/made private. Instead of the first two you can use getChoices() and getChoicesByValues(), for the latter two no replacement exists. ArrayChoiceList was replaced by SimpleChoiceList. PaddedChoiceList, MonthChoiceList and TimezoneChoiceList were removed. Their functionality was merged into DateType, TimeType and TimezoneType)
• e1fc5a5:
  [Form] restricted form names to specific characters to fix generation of HTML IDs and to avoid problems with property paths
• f533ef0:
  [Form] added ChoiceView class for passing choice-related data to the view
• 43e0db5:
  [DomCrawler] add support for multivalued form fields
• 399af27:
  [Form] implemented checks to assert that values and indices generated in choice lists match their requirements
• ed9c348:
  [Security] Authentication(Success|Failure)Handler can now return null
• 2dd4bf1, c3f637b:
  [Form] support for PATCH method in forms
• d4300b9, b879397:
  [WebProfilerBundle] tweak the time view
• a52c675:
  [WebProfilerBundle] improved the logger panel

2.0.x branch:

• 7f96c8a:
  [HttpKernel] prevent php script execution in cached ESI pages using HttpCache
• c3f0ec7:
  [DoctrineBundle] made DoctrineBundle fowards compatible with Doctrine 2.2

Repository summary: 3,830 watchers (#1 in PHP, #27 overall) and 983 forks (#1 in PHP, #11 overall).

Updated plugins

• dcReloadedFormExtra:
  • added a uniquenes checker widget that checks the uniqueness of a value using an Ajax callback
• sfSyncContent:
  • created default parameters for sync content to be frontend dev FROM prod@production
• apostrophe:
  • added an app.yml option to return email links in a different format that allows apostrophe.unobfuscateEmailInline to be called outside of the a_js spool
  • all unlink() and rmdir() calls in the product now invoke aFiles::unlink() and aFiles::rmdir(), providing a capability to override these methods
  • the aFiles class has been refactored into a BaseaFiles? class and an empty aFiles subclass, allowing convenient overrides
  • a_button helper now accepts a single array of options as an alternative to the full list of parameters
  • slideshows are allowed to have the crossfade transition now even if they have no height specified
  • fixed event bug in menutoggle script and added jQuery 1.7 to the plugin
  • annotation of multiple media items works again
• apostropheBlog:
  • added a ‘first’ class to the first pager in the blog index

They talked about us

• deSymfony 2012 inicia su call for papers
• Create your own framework … on top of the Symfony2 Components (part 11)
• Une semaine symfonique #264 – du 16 au 22 janvier 2012
• symfony forward through LAN
• Use virtuals forms with Symfony2
• Create your own framework … on top of the Symfony2 Components (part 12)
• sqlite w ?rodowisku developerskim w symfony i symfony2
• Load fixtures with Symfony2 and YAML files
• Onde encontrar Bundles para o Symfony2
• Notes: Symfony 2 (?)
• Nazwa.pl, PostgreSQL i Symfony
• Recording ‘Last Activity’ for Users in Symfony2 + FOSUserBundle
• Get started with Symfony in Amazon Cloud
• symfony schema.yml ???
• Symfony YAML MySQL Workbench Plugin
• Symfony 1.4 Admin Generator – Aktuelles Objekt in Partial
• Uso de sfWidgetFormJQueryDate (datepicker) en Symfony
• Symfony meets APC (Alternative PHP Cache)
• ???? ?? ????? – ???????? ????????????????? ?????? ??? Symfony

We’ve released an updated version of the MySQL Configuration Wizard we announced at the end of last year. If you don’t remember that announcement, here’s the short version: this is a tool to help you generate my.cnf files based on your server’s hardware and other characteristics.

We’ve gotten really good feedback on this tool, including this nice mention on Stack Exchange:

Percona just built a tool to do just that called the Configuration Wizard. I tested it out once just to see what it would return and the results were pretty darn close to what we were using on our servers, whose cnf’s were put together by highly trained mysql certified dba’s.

So what’s changed in the new version of the Configuration Wizard? Quite a few things. We’ve rolled out the first iteration of the account and profile features. Now you get a homepage with your configuration files, so you can manage them and return to them anytime you like.

From this page (click on the image for a fullsize view) you can do things like sharing configuration files and emailing them to yourself. The new release also adds features like downloading the configuration files so you don’t have to copy-paste them.

If you share a configuration file, then the URL can be loaded by anyone, even if they’re not logged in. It’s kind of like sending someone a link to a pastebin or something like that. Screenshot:

Another new feature is something I’ve wanted for a long time: the ability to generate a more strict, safer configuration file. There’s a new page in the Wizard that lets you set a lot of sanity/safety options to prevent common problems MySQL users run into because of too-permissive MySQL behaviors. These are the kinds of things that Drizzle fixes — and should be fixed by default in MySQL — but never will be because they might break applications that rely on the default behaviors. If you’re building an application from the ground up, now you can prevent bad things from getting a nose under the tent. Here’s a screenshot:

In addition to these things, we have added a number of other features you might not notice, which I won’t spend much time on — they’re things like an integrated feedback form at the left of the page and so on.

What’s next? Well, next I think we’re going to turn our attention to adding new tools, rather than improving this one. I have a list of tools that people have requested or suggested: a SQL formatter, a visual EXPLAIN tool, a configuration advisor, a query analysis tool, a way to register a server’s essential characteristics and then get advice when there’s a new release that might be beneficial for you, and so on. I have selected the next priorities, but I don’t want to spoil the surprise or promise something if it turns out to be harder than I think it will be. What ideas do you have? Let me know by leaving your feedback in the comments.

We hope this suite of free browser-based tools helps you become a more productive MySQL user and administrator!

Sometimes we need to restore only some tables from a full backup maybe because your data loss affect a small number of your tables. In this particular scenario is faster to recover single tables than a full backup. This is easy with MyISAM but if your tables are InnoDB the process is a little bit different story.

With Oracle’s stock MySQL you cannot move your ibd files freely from one server to another or from one database to another. The reason is that the table definition is stored in the InnoDB shared tablespace (ibdata) and the transaction IDs and log sequence numbers that are stored in the tablespace files also differ between servers. Therefore our example will be very straightforward: we’ll delete some rows from a table in order to recover the table later.

Most of these limitations are solved on Percona Server . More info about this in the conclusion section of this post. This post will be focus on how to recover a single tablespace using stock MySQL server.

First, you must meet certain prerequisites to be able to restore a ibd tablespace:

• The ibd file must be from a consistent backup with all insert buffer entries merged and have no uncommitted transactions in order to not be dependent of the shared tablespace ibdata. That is, shutting down with innodb_fast_shutdown=0. We’ll use XtraBackup to avoid the server shutdown.
• You must not drop, truncate or alter the schema of the table after the backup has been taken.
• The variable innodb_file_per_table must be enabled.

Then, our first step is to get a consistent backup.

First we need to copy all the data to an output directory:

The –export option is the magic trick that will help us to get a consistent backup with complete independent ibd files without shutting down the service. In the second step the use of –export option runs a recovery process on the backup with innodb_fast_shutdown=0 and therefore merging all the insert buffers.

# innobackupex --defaults-file=/etc/my.cnf --export /tmp/

Then apply the logs to get a consistent backup:

# innobackupex --defaults-file=/etc/my.cnf --apply-log --export /tmp/2012-01-22_14-13-20/

Now we’re going to delete some data from one table. In this case we’re going to delete the salary information from the user 10008:

mysql> SELECT * FROM salaries WHERE emp_no=10008;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10008 | 46671 | 1998-03-11 | 1999-03-11 |
| 10008 | 48584 | 1999-03-11 | 2000-03-10 |
| 10008 | 52668 | 2000-03-10 | 2000-07-31 |
+--------+--------+------------+------------+

mysql> DELETE FROM salaries WHERE emp_no=10008;

The next step is where we are going to save a lot of time and some headaches Instead of recovering all the InnoDB data we are going to recover only the “salaries” table:

• Discard the tablespace of the salaries table:


mysql> set FOREIGN_KEY_CHECKS=0;
mysql> ALTER TABLE salaries DISCARD TABLESPACE;

• Copy the salaries.ibd files from the backup to the database data directory:

# cp /tmp/2012-01-22_14-13-20/employees/salaries.ibd /var/lib/mysql/data/employees/

• Import the new tablespace:


mysql> set FOREIGN_KEY_CHECKS=0;
mysql> ALTER TABLE salaries IMPORT TABLESPACE;
mysql> set FOREIGN_KEY_CHECKS=1;
mysql> SELECT * FROM salaries WHERE emp_no=10008;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10008 | 46671 | 1998-03-11 | 1999-03-11 |
| 10008 | 48584 | 1999-03-11 | 2000-03-10 |
| 10008 | 52668 | 2000-03-10 | 2000-07-31 |
+--------+--------+------------+------------+

The salary history from the user is back again!

Conclusion:

As we learned , you can also recover single InnoDB table as with MyISAM but knowing in advance that there are some prerequisites to comply.

Percona Server relaxes a lot of limitations and is able to import tables from different Server instance, when table was altered or truncated in the meanwhile. Though this only works if table was
“exported” with Xtrabackup as this exports essential information from main tablespace which is not stored in .ibd file. innodb_import_table_from_xtrabackup=1 should be enabled for such advanced import process to work. You can read more about this feature in Percona Server Documentation

In the next blog post I’ll explain how to do recovery using Percona Data Recovery toolkit.

I am pleased to announce the schedule for Percona Live: MySQL Conference And Expo 2012 is now published. This is truly great selection of talks with something for MySQL Developers, DBAs, Managers, people just starting to use MySQL as well as looking for advanced topics. We have talks about running MySQL on extremely large scale in a Web as well as running MySQL In the Enterprise Environments. Some speakers have spent over decade pushing MySQL to its limits, others have in depth experience working on MySQL Code.

We have many talks which are covering Oracle MySQL, and forks such as MariaDB, Drizzle and Percona Server are well covered too. You will also have a chance to learn about commercial MySQL alternatives such as Clustrix and SchoonerSQL from our sponsors.

At the same time this is the conference for MySQL Community. We’re talking about other database systems only as it comes to migration to MySQL and about NoSQL technologies such as Memcached,Redis,Sphinx which are commonly used to supplement MySQL.

The space was very tight this year and competition was very tough. We had over 300 proposals for approximately 60 slots. As results committee had to make a lot of very tough choices and many great talks could not be accommodated.

We have a great Conference Committee this year who has done a great job getting the schedule together. I can’t thank them enough !

See you in April in Santa Clara for MySQL Conference and lets make this event an amazing success !

I spent last week at linux.conf.au in Ballarat, Victoria (that’s the Victoria in Australia, not wherever else there may be one) which is only a pleasant two hour drive from my home town of Melbourne (Australia, not Florida). I sent an email internally to our experts detailing bits of the conference that may interest them – and I thought that it may also interest our wider readers who are interested in all levels of the software stack.

For those that don’t know: linux.conf.au is one of (if not the) most awesome technical conference in the free software space. It consistently attracts a wide variety of very knowledgable speakers and a large number of attendees.

Every year it is put together by a (different) set of volunteers, and this means it also tours around the country (and sometimes even New Zealand). This year it was in Ballarat – a regional city a couple of hours drive out of Melbourne. One of the great things about LCA is that you are not always at the same hotel, in the same city stuck with the same two restaurants.

This year had a bit of an increased focus on privacy, security and basic freedoms and human rights. This is no doubt a reaction to the increased attacks on freedom of speech and the internet that have been going on in recent months.

That being said, there were a huge number of great talks on a variety of topics – everything from filesystem performance to open hardware, to repurposing existing hardware to upcoming challenges for the kernel to howto be a better sysadmin. In fact… for those who weren’t there and spend any of their life helping people admin machines – go and watch those talks.

linux.conf.au (for me) is one of the cannot-miss events in the year. It’s an opportunity to learn things that directly apply to my work, may apply in the future and most certainly will never apply but are rather cool anyway.

All the video from the conference are already up! This is an amazing effort from the (volunteer) AV team. I’ve included links to a selection of talks below that I especially think are worth watching:

Watch no matter what:

• Keynote – Karen Sandler
  This keynote was amazing. Go watch it. The organisers did a truly excellent job at selecting keynotes this year.
• Keynote – Jacob Appelbaum
  This is best described as a tour of internet freedom, the attacks on it and a tour of the modern surveillance state.
• UEFI and Linux: The future is here and it’s awful
  You will be depressed at some point in this talk – the news is not great for the future of even being able to easily boot free software on machines.
• Paul Fenwick’s Keynote
  A good quick introduction to hacking your brain. I’m sure many of you (like me) are interested in ways to hack our brains and our bodies to better serve us. This talk is merely an introduction. I also suggest you check out Anki if you want to improve your ability to remember things.
• Torturing OpenSSL
  This was certainly one of the most amazing talks I saw. A whole new interesting way of attacking SSL. Vary CPU voltage, extract private SSL keys! Wheee!
• The Kernel Report – Jonathan Corbet
  You can skip this only if you read every single mail on LKML, run your own analysis on the kernel source tree and publish an (at least) weekly publication on Linux.
  This is one of the few (err… only) talks that is repeatedly accepted into linux.conf.au. Why? Because Jon manages to compress a whole year of activity in the Linux world int oa single session that is incredibly informative.
• I Can’t Believe This Is Butter! A tour of BTRFS
  This is going to be the default filesystem in a number of places over the next few years, time to start learning! While it’s unlikely to be suitable for database workloads anywhere in the near future, I suspect we’ll see BTRFS as the root filesystem and XFS as the filesystem for the database server in the not too distant future.
• Mistakes Were Made
  This session explores a number of rather indispensable things for those in operations – but also leaks over into development. Learning from our mistakes can only make us better at doing our jobs.
• Hack Everything: repurposing everyday devices
  This was great fun – reuse existing hardware to do awesome things!

Talks that could be quite interesting for you, depending on your interests:

• XFS: Recent and Future Adventures
  This talk covered a lot of the recent improvements in XFS. Especially interesting for those with metadata heavy workloads.
• Bloat: How and Why UNIX Grew Up (and Out) – Rusty Russell,Matt Evans
  This is a pretty neat examination of where bloat comes from taking V6 UNIX utilities as the baseline. It would be interesting to have a similar study on the database servers we all love and use.
• Migrating to PHP 5.4
  Probably quite useful if you’re dealing with PHP (although I wasn’t in this session).
• Finding vulnerabilities in PHP code (via static code analysis)
  This one is for our web developers. It’s only a 20minute talk, so you should really all go and watch it. Various static analysis tools are part of the normal toolkit for C and C++ development these days, it’s great to see people working on them for other languages.

Talks I shall be watching the videos of as I was in another talk at the time:

• Smashing a square peg into a round hole
• Codec2: Open Source Speech Codec
• Desktop Home Hacks
• This Old Code, or Renovating Dusty Old Open Source For Fun and Profit
  This is especially interesting as I both know Greg and have talked to him about this work *and* because of my experience with the Drizzle project – we certainly have encountered more than our fair share of dusty old code during our time working on Drizzle.
• Mentoring: we’re Doing It Wrong

My Talk:

• Multi-tenancy, multi-master, sharding, scaling and analytics with Drizzle
  An update as to what we’ve been working on getting going in Drizzle.

All the videos are going up at:

• http://www.youtube.com/user/linuxconfau2012/videos

On the 25th of January at 10 am PST, I will present a webinar on preventing MySQL emergencies titled “Preventing Downtime in Production MySQL Servers”. The material I will present is based on in-depth research done by Percona across many production servers.  We analyzed more than 150 emergency cases and categorized our findings to help you learn ways to avoid production downtimes. Join us to learn more about why emergencies happen (it may be different than what you think) and what you can do to avoid them.

Sign up for the webinar now by visiting our webinar page [http://www.percona.com/webinars/2012-01-25-preventing-downtime-in-production-mysql-servers/].

Sometimes you might hear people talk about full backups, and differential backups versus incremental backups. What is the difference?

A full backup is pretty self-explanatory. It makes a copy of all of your MySQL data.

A differential backup, on the other hand, simply records the differences since your last full backup. The advantage of taking a differential backup is usually the space savings. Most databases have a lot of data that does not change from one backup to the next. Not copying this data into your backups can result in significantly smaller backups. In addition, depending on the backup tool used, a differential backup can be less labor-intensive for the server. If a differential backup does not have to scan all of the data to determine what has changed, the differential backup process can be significantly more efficient.

An incremental backup is a modification of a differential backup. An incremental backup records the changes since the last backup of any type, be it a differential or full backup. The advantages of incremental backups are similar to those of differential backups.

Here is an example that might explain this more clearly. Suppose that you take a full backup on Sunday. On Monday, you make a backup of all the changes since Sunday. This is a differential backup.

On Tuesday is when you begin to see the differences between the backup strategies. If you back up the changes since Sunday, then you have made a differential backup. If you back up the changes since Monday, it is an incremental backup.

Why would you choose an incremental versus a differential backup? That is a little bit out of scope for this blog post, because there are a lot of subtleties to consider. However, perhaps the biggest difference is in the way that you would restore a backup. Suppose that you need to restore your database on Friday. If you have taken differential backups all week long, you only need to restore Sunday, and then apply the changes that have happened since Sunday. If you have taken incremental backups, you must restore Sunday’s backup, and then apply changes repeatedly until you reach Friday. This can be more labor intensive, error-prone, and risky. It can also take longer.

Percona XtraBackup is capable of taking incremental backups, and when you specify the base backup as the previous incremental or full backup, you can easily make differential backups.

This week, Form and Validator were the most active components on the Symfony2 master branch. First, the CollectionValidator introduced new Optional and Required constraints. Second, the validation of form children was made configurable, meaning that child forms now aren’t validated anymore by default (bc-break change).

Symfony2 development highlights

Master branch:

• 6641f3e, bf59018, 509c7bf, e6e3da5:
  [Validator] added constraints Optional and Required for the CollectionValidator
• 0c70a41, 43293f4:
  [Form] made validation of form children configurable. Set the option cascade_validation to true if you need it
• 0c18324:
  [HttpKernel] added extra information when collecting data from an object if that object implements a __toString()
• 693d1ec:
  [Form] do not render default ID attribute on empty form name
• 15c666b, d2a0c74, 9e55cda:
  added a recover-timeout option to allow recovering messages that have taken too long to send
• 0b7e2e0:
  support for DELETE method in forms
• 0513eb1:
  [Form] pass translation domain to the sub-forms when choice list is expanded

2.0.x branch:

• cacc880:
  [Locale] fixed incomplete Locale data loading (sublocales returned only incomplete results for getDisplayCountries(), getDisplayLanguages() and getDisplayLocales())
• 959614b:
  use reflection to determine the correct path for component validation.xml file
• a1317c3:
  [Locale] fixed breaking tests if ‘intl’ extension is not installed
• 1f733e1:
  updated Serbian translation for validators

Repository summary: 3,787 watchers (#1 in PHP, #26 overall) and 973 forks (#1 in PHP, #11 overall).

New plugins

• asJqueryTreePropelManager: (no description)
• sfCpGrid: is a javascript based backend for Symfony. It loads the recordset from the db via ajax, can sort it on the fly, it checks periodically if there is something new, it allows filtering and search, and to have custom buttons and related actions.

Updated plugins

• sfGearman:
  • allow to namespaces function for mutualized gearmand
• apostrophe:
  • slight change in linkage on aImage placeholders
  • fixed bug with placeholder clicker box that looks for the choose button url in the correct place for both slots in areas and singleton slots
  • smart slideshow passes new ‘clickToSelect => false’ option to placeholder, which prevents a 404 if you click the placeholder area on a smart slideshow
  • factored out the slideshow slot code that relinks the related media items into a separate method so it can be easily called from overrides
• apostropheBlog:
  • slot help text doesn’t need to get output to logged out visitors
  • changed the check to editor privileges

They talked about us

• Create your own framework … on top of the Symfony2 Components (part 8)
• ??????? ??????????? ????????? ?? ?????? Symfony2. (????? 2)
• Une semaine symfonique #263 – du 9 au 15 janvier 2012
• ??????? ??????????? ????????? ?? ?????? Symfony2. (????? 3)
• Se publica Symfony 2.0.9
• Create your own framework … on top of the Symfony2 Components (part 9)
• Paris PHPCR meetup
• Symfony 2 – Veritabani Icin Baslangic Verileri Olusturmak – Doctrine Fixtures
• Deploying SF2 Projects on Plesk VPS.
• ??????? ??????????? ????????? ?? ?????? Symfony2. (????? 4)
• Create your own framework … on top of the Symfony2 Components (part 10)
• Les services, théorie et création
• ?????? ????????? Symfony2
• Intégrer facilement un blog à votre application Symfony – WordPress, Drupal, Joomla …
• Sortie de Symfony 2.0.9
• ???????????????? ????????? ? SonataAdminBundle
• Documentação do Symfony 2 em português
• Git and first Symfony2 project in NetBeans
• Symfony: short commands in terminal
• [symfony] javascript???????(use_javascript ? include_javascripts)
• Les services, utilisation poussée
• Drupal8 und Symfony2 gehen gemeinsame Wege!
• Symfony 1.4, 1.3 ?rlapok és ajax validáció
• Symfony2 & Mercurial
• Symfony2, best IDE support?
• Symfony meets APC (Alternative PHP Cache)
• rails 3.1 vs Symfony2 – my first comparison