Login Form

Another Christmas had come and gone, another opportunity lost for most of us to do the right thing.

Families reunions are probably the worst thing ever, unless there is a real family as solid ground.

As some knows my nick name is “The Grinch” from many years now. But I have to say that while we were in Canada, and we were focusing the Holydays on us only, I had start to enjoy it again. We were US, with our feeling and joy to be together, simple things … few presents, and a lot of games, talk and “just” stay together.

This year we had it in Italy … with our large family. We were looking to be together again, given some had gone, some just join. Instead I had a mix of feelings, spacing from pure disappointment, embarrassment, to a bit of rage and very few moments of serenity.

Well not really the feeling you should have in this period of the year.

The question I was asking myself was “WHY”?

Reviewing the situations, I had noted few things I feel terrible wrong, things that do not really match with what the time of the year should suggest.

In my view this is a good moment to “think” about how we can be better as person, how to improve us be compassionate, respectful, humble and more balanced in our every day life.

Family should be the place where we can research, training and exercise all the above, having the one we love (and that should love us), helping each other without fear and without looking for our own interest.

Instead, we were in a crazy carousel of “things”, food, superficial discussions, sometime argument because old (very old) resentments. An apotheosis of superficiality focuses on consumerism bound to fake religious feeling.

Of course, some “good” feeling(s) was there, still it was not the central element, it was a secondary or tertiary element.

We had kids receiving so many presents/toys that they cannot even play with them, adults focusing on unwrapping presents as the most important thing to do, more than talk (for real).

I felt as I was in a different space/time, looking at something as an external viewer, not able to interact, not able to move, without any control and/or ability to modify what was going on.

And I realized that I was becoming The Grinch again, but not with my heart 3 sizes less. Who had lost 3 sizes were all the people around me.

Looking around I was searching for “my” family, and in some way, I saw similar disconcert in their eyes.

This is when I felt the (bit of) rage surging, I am not Catholic, but I am a religious person, I believe in “doing the right thing” above the immediate pleasure or satisfaction, I believe we have a responsibility as human being in supporting each other, in sharing and participate …and more. I felt we were betraying the basic rules, that we miss the point losing time and wasting a good opportunity to be right.

In that moment I decided “this is it” no more stupidity, we must be different, we must act in some way. Not stealing Christmas, no… but still I must act and start to push around me, to see things going in a different way.

Starting with using the resources for others not for stupid presents, and ask the other to talk … for real, feeling, themselves, us as part of a community, stop to be selfish, stop to be so egocentric and superficial, stop to be consumed by time.

Last Updated on Tuesday, 25 December 2018 19:15

04

Dec

2018

MySQL 8 and the FRM drop.

Written by Marco Tusa

(What I should keep in mind in case of disaster)

Retrieve and maintain in SQL format the tables definition of all tables in a database, is one of the best practices that we all should adopt.

To have that under versioning is also another BP to keep in mind. Doing that may seems redundant, but it become a life saver in several situations. From the need to review what had historically change in that table, know who change what and why, to when you need to recover your data and have your loved MySQL instance not able to start.

But let us be honest, just few do the right thing, and even fewer keep that information up to date. Given that, what can we do when we have the need to discover/recover the table structure? From the beginning, MySQL had used some external files to describe the internal structure. For instance, if I have a schema named windmills and a table named wmillAUTOINC1, on file system I will see:

The ibd file contains the data, while frm file contains the structure information.Keeping aside ANY discussion about if this is safe, if it transactional and more… when we had some major crash and data corruption this approach had being helpful. Being able to read from this file was the easiest way to get the information we need. Simple tools like DBSake was making the task quite trivial, also allowing us to script it when in need to run long, complex tedious data recovery:

Of course, if the frm file was also corrupted we could try to get the information from the ibdata dictionary. If that is corrupted as well (trust me I saw all these) … well last resource was hoping customer has a recent table definition store somewhere, but as mentioned before, we are not so diligent, are we? NOW in MySQL8 we do not have FRM files, the were drop. Even more interesting is the fact that we do not have the same dictionary, most of the things that we knew had change, including the dictionary location, so what can be done?

Well Oracle had moved the FRM information and more, to what is call Serialized Dictionary Information (SDI), the SDI is wrote INSIDE the ibd file, and represent the redundant copy of the information contain in the data dictionary.

The SDI is update/modified by DDL operations on tables that reside in that tablespace.

This is it, if you have file per table normally you will have in that file ONLY the SDI for that table, but if you have multiple table on a tablespace, the SDI information will refer to ALL the tables.

To extract this information out from IBD files, Oracle provide an utility call ibd2sdi.

The application parses the SDI information and report a JSON file that can be easily manipulate to extract/build the table definition. One exception is represented by Partitioned tables, the SDI information is contained ONLY in the first partition, and in case you drop it, is moved to the next one, will show that later.

Let us see now how it works. In the next examples I will look for Tables name, attribute and datatype starting from the dictionary tables.To take the info I will do this:

As you cannot see because I cut the output for brevity, but you can if you run the above command by yourself, what I will get retrieve ALL the tables information, residing in the IBD. The other thing I hope you have noticed, is that I am NOT parsing ibdata, but mysql.ibd, why? Because the dictionary was moved out from ibdata and is now in mysql.ibd. Look what happens if I try to parse ibdata:

That is a lot more details than what we had in the FRM and is quite relevant and interesting information as well.Once extracted the SDI any JSON parser tool script can generate the information for the SQL DDL.I mention Partitions, let us cover it a second a bit more, given they can be tricky.As mentioned the SDI information is present ONLY in the first partition. All the others will have ONLY the tablespace information. Given that the first thing to do is to identify which Partition is the first… OR simple try to access all and when you are able to get the details just extract them.The process is the same:

As also mention before the SDI is update at each DDL, and here we go, I will have all the information on the NOW FIRST partition. Please note the created attribute between the first time I query the Other partition and the one I had from now: