25
Dec
2018
Happy holidays
Written by Marco Tusa   

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

-rw-r-----. 1 mysql mysql     8838 Mar 14 2018 wmillAUTOINC1.frm
-rw-r-----. 1 mysql mysql   131072 Mar 14 2018 wmillAUTOINC1.ibd


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:

[root@master1 windmills]# /opt/tools/dbsake frmdump wmillAUTOINC1.frm
--
-- Table structure for table `wmillAUTOINC1`
-- Created with MySQL Version 5.7.20
--
 
CREATE TABLE `wmillAUTOINC1` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `uuid` char(36) COLLATE utf8_bin NOT NULL,
  `millid` smallint(6) NOT NULL,
  `kwatts_s` int(11) NOT NULL,
  `date` date NOT NULL,
  `location` varchar(50) COLLATE utf8_bin NOT NULL,
  `active` tinyint(2) NOT NULL DEFAULT '1',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `strrecordtype` char(3) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_millid` (`millid`,`active`),
  KEY `IDX_active` (`id`,`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC

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:

/opt/mysql_templates/mysql-8P/bin/./ibd2sdi   /opt/mysql_instances/master8/data/mysql.ibd |\
jq '.[]?|.[]?|.dd_object?| \
("------------------------------------"?,"TABLE NAME = ",.name?,"****",(.columns?|.[]?|(.name?,.column_type_utf8?)))'
The result will be something like:
"------------------------------------"
"TABLE NAME = "
"tables"
"****"
"id"
"bigint(20) unsigned"
"schema_id"
"bigint(20) unsigned"
"name"
"varchar(64)"
"type"
"enum('BASE TABLE','VIEW','SYSTEM VIEW')"
"engine"
"varchar(64)"
"mysql_version_id"
"int(10) unsigned"
"row_format"
"enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged')"
"collation_id"
"bigint(20) unsigned"
"comment"
"varchar(2048)"
<snip>
"------------------------------------"
"TABLE NAME = "
"tablespaces"
"****"
"id"
"bigint(20) unsigned"
"name"
"varchar(259)"
"options"
"mediumtext"
"se_private_data"
"mediumtext"
"comment"
"varchar(2048)"
"engine"
"varchar(64)"
"DB_TRX_ID"
""
"DB_ROLL_PTR"
""

 

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:

[root@master1 ~]# /opt/mysql_templates/mysql-8P/bin/./ibd2sdi   /opt/mysql_instances/master8/data/ibdata1 |jq '.'
[INFO] ibd2sdi: SDI is empty.

Be very careful in not messing up with your mysql.ibd file. Now what I can do to take information about my wmillAUTOINC1 table in MySQL8? That is quite simple:

/opt/mysql_templates/mysql-8P/bin/./ibd2sdi   /opt/mysql_instances/master8/data/windmills/wmillAUTOINC.ibd |jq '.'
[
  "ibd2sdi",
  {
    "type": 1,
    "id": 1068,
    "object": {
      "mysqld_version_id": 80013,
      "dd_version": 80013,
      "sdi_version": 1,
      "dd_object_type": "Table",
      "dd_object": {
        "name": "wmillAUTOINC",
        "mysql_version_id": 80011,
        "created": 20180925095853,
        "last_altered": 20180925095853,
        "hidden": 1,
        "options": "avg_row_length=0;key_block_size=0;
keys_disabled=0;pack_record=1;row_type=2;stats_auto_recalc=0;stats_sample_pages=0;", "columns": [ { "name": "id", "type": 9, "is_nullable": false, "is_zerofill": false, "is_unsigned": false, "is_auto_increment": true, "is_virtual": false, "hidden": 1, "ordinal_position": 1, "char_length": 11, "numeric_precision": 19, "numeric_scale": 0, "numeric_scale_null": false, "datetime_precision": 0, "datetime_precision_null": 1, "has_no_default": false, "default_value_null": false, "srs_id_null": true, "srs_id": 0, "default_value": "AAAAAAAAAAA=", "default_value_utf8_null": true, "default_value_utf8": "", "default_option": "", "update_option": "", "comment": "", "generation_expression": "", "generation_expression_utf8": "", "options": "interval_count=0;", "se_private_data": "table_id=1838;", "column_key": 2, "column_type_utf8": "bigint(11)", "elements": [], "collation_id": 83, "is_explicit_collation": false }, <SNIP> "indexes": [ { "name": "PRIMARY", "hidden": false, "is_generated": false, "ordinal_position": 1, "comment": "", "options": "flags=0;", "se_private_data": "id=2261;root=4;space_id=775;table_id=1838;trx_id=6585972;", "type": 1, "algorithm": 2, "is_algorithm_explicit": false, "is_visible": true, "engine": "InnoDB", <Snip> ], "foreign_keys": [], "partitions": [], "collation_id": 83 } } }, { "type": 2, "id": 780, "object": { "mysqld_version_id": 80011, "dd_version": 80011, "sdi_version": 1, "dd_object_type": "Tablespace", "dd_object": { "name": "windmills/wmillAUTOINC", "comment": "", "options": "", "se_private_data": "flags=16417;id=775;server_version=80011;space_version=1;", "engine": "InnoDB", "files": [ { "ordinal_position": 1, "filename": "./windmills/wmillAUTOINC.ibd", "se_private_data": "id=775;" } ] } } } ]

The JSON will contains:

  • A section describing the DB object at high level
  • Array of columns and related information
  • Array of indexes
  • Partition information (not here but in the next example)
  • Table space information

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:

[root@master1 ~]# /opt/mysql_templates/mysql-8P/bin/./ibd2sdi  \
 /opt/mysql_instances/master8/data/windmills/wmillAUTOINCPART#P#PT20170301.ibd |jq '.'
[
  "ibd2sdi",
  {
    "type": 1,
    "id": 1460,
    "object": {
      "mysqld_version_id": 80013,
      "dd_version": 80013,
      "sdi_version": 1,
      "dd_object_type": "Table",
      "dd_object": {
        "name": "wmillAUTOINCPART",
        "mysql_version_id": 80013,
        "created": 20181125110300,
        "last_altered": 20181125110300,
        "hidden": 1,
        "options": "avg_row_length=0;
key_block_size=0;keys_disabled=0;pack_record=1;row_type=2;stats_auto_recalc=0;stats_sample_pages=0;",
        "columns": [<snip>
          "schema_ref": "windmills",
        "se_private_id": 18446744073709552000,
        "engine": "InnoDB",
        "last_checked_for_upgrade_version_id": 80013,
        "comment": "",
        "se_private_data": "autoinc=31080;version=2;",
        "row_format": 2,
        "partition_type": 7,
        "partition_expression": "to_days(`date`)",
        "partition_expression_utf8": "to_days(`date`)",
        "default_partitioning": 1,
        "subpartition_type": 0,
        "subpartition_expression": "",
        "subpartition_expression_utf8": "",
        "default_subpartitioning": 0,
       ],
<snip>
        "foreign_keys": [],
        "partitions": [
          {
            "name": "PT20170301",
            "parent_partition_id": 18446744073709552000,
            "number": 0,
            "se_private_id": 1847,
            "description_utf8": "736754",
            "engine": "InnoDB",
            "comment": "",
            "options": "",
            "se_private_data": "autoinc=0;version=0;",
            "values": [
              {
                "max_value": false,
                "null_value": false,
                "list_num": 0,
                "column_num": 0,
                "value_utf8": "736754"
              }
            ],

The difference as you can see is the section related to Partitions and Sub Partition that will be filled with all the details you may need to recreate the partitions. We will have:

  • Partition type
  • Partition expression
  • Partition values
  • …more

Same for sub partitions. Now again see what happens if I parse the second Partition:

[root@master1 ~]# /opt/mysql_templates/mysql-8P/bin/./ibd2sdi  \
 /opt/mysql_instances/master8/data/windmills/wmillAUTOINCPART#P#PT20170401.ibd |jq '.'
[
  "ibd2sdi",
  {
    "type": 2,
    "id": 790,
    "object": {
      "mysqld_version_id": 80011,
      "dd_version": 80011,
      "sdi_version": 1,
      "dd_object_type": "Tablespace",
      "dd_object": {
        "name": "windmills/wmillAUTOINCPART#P#PT20170401",
        "comment": "",
        "options": "",
        "se_private_data": "flags=16417;id=785;server_version=80011;space_version=1;",
        "engine": "InnoDB",
        "files": [
          {
            "ordinal_position": 1,
            "filename": "./windmills/wmillAUTOINCPART#P#PT20170401.ibd",
            "se_private_data": "id=785;"
          }
        ]
      }
    }
  }
]

I will get only the information about the Tablespace but not the table. As promise let me show you now what happen if I delete the first partition, and the second one will become the first:

(root@localhost) [windmills]>alter table wmillAUTOINCPART drop partition PT20170301;
Query OK, 0 rows affected (1.84 sec)
Records: 0  Duplicates: 0  Warnings: 0

[root@master1 ~]# /opt/mysql_templates/mysql-8P/bin/./ibd2sdi \
  /opt/mysql_instances/master8/data/windmills/wmillAUTOINCPART#P#PT20170401.ibd |jq '.'|more
[
  "ibd2sdi",
  {
    "type": 1,
    "id": 1461,
    "object": {
      "mysqld_version_id": 80013,
      "dd_version": 80013,
      "sdi_version": 1,
      "dd_object_type": "Table",
      "dd_object": {
        "name": "wmillAUTOINCPART",
        "mysql_version_id": 80013,
        "created": 20181129130834,
        "last_altered": 20181129130834,
        "hidden": 1,
        "options": "avg_row_length=0;
key_block_size=0;keys_disabled=0;pack_record=1;row_type=2;stats_auto_recalc=0;stats_sample_pages=0;", "columns": [ { "name": "id", "type": 9, "is_nullable": false, "is_zerofill": false, "is_unsigned": false, "is_auto_increment": true, "is_virtual": false, "hidden": 1, "ordinal_position": 1,

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:

/opt/mysql_instances/master8/data/windmills/wmillAUTOINCPART#P#PT20170301.ibd
       "created": 20181125110300,

/opt/mysql_instances/master8/data/windmills/wmillAUTOINCPART#P#PT20170401.ibd
       "created": 20181129130834,

  To be clear the second created is NOW (PT20170401) when I did the drop on the other partition (PT20170301).

 

Conclusions

At the end this solution is definitely more powerful of the FRM files.
It will allow us to parse the file and identify the table definition more easily, get much more details and information.

The problems will raise when and IF the IBD file will become corrupt.

As for manual For InnoDB, an SDI record requires a single index page, which is 16KB in size by default. However, SDI data is compressed to reduce the storage footprint.

Which it means that for each Table I have a page, if I associate record=table.

Which means that in case of IBD corruption I should (likely) be able to read those pages, unless bad (very bad) luck.

Still wonder how the dimension of an IBD affects the SDI retrieval, given I had not tried it yet but will let you know.

On another side I am working on a script to facilitate the SQL generation, still not ready but you can find it here

Last note but keep this in mind! It is state in the manual but in a hidden place small letters: DDL operations take longer due to writing to storage, undo logs, and redo logs instead of .frm files.

 

References

https://stedolan.github.io/jq/

https://dev.mysql.com/doc/refman/8.0/en/ibd2sdi.html

https://dev.mysql.com/doc/refman/8.0/en/serialized-dictionary-information.html

https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-limitations.html

Last Updated on Tuesday, 25 December 2018 19:18
 
More Articles...
«StartPrev12345678910NextEnd»

Page 1 of 30
 

Who's Online

We have 39 guests online