Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-3943

Switching between JSON and BLOB/TEXT columns will increase key_block_size; unclear table full condition

    XMLWordPrintable

    Details

      Description

      Hi dear all,
      The situation is when you are altering the column type from json to blob or vice versa, it will increase the key_block_size continuously.
      Such as:

      5.7.21>show create table sbtest85\G
      *************************** 1. row ***************************
             Table: sbtest85
      Create Table: CREATE TABLE `sbtest85` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `k` int(11) NOT NULL DEFAULT '0',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        `json_column` json DEFAULT NULL,
        `blob_column` blob,
        PRIMARY KEY (`id`)
      ) ENGINE=MEMORY AUTO_INCREMENT=13005 DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=36672
      1 row in set (0.00 sec)
      
      5.7.21>show create table sbtest85\G
      *************************** 1. row ***************************
             Table: sbtest85
      Create Table: CREATE TABLE `sbtest85` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `k` int(11) NOT NULL DEFAULT '0',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        `json_column` json DEFAULT NULL,
        `blob_column` json DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=MEMORY AUTO_INCREMENT=13005 DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=36752
      1 row in set (0.00 sec)
      

      Which is going to be resulted with weird thing:

      5.7.21>show create table sbtest85\G
      *************************** 1. row ***************************
             Table: sbtest85
      Create Table: CREATE TABLE `sbtest85` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `k` int(11) NOT NULL DEFAULT '0',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        `json_column` json DEFAULT NULL,
        `blob_column` blob,
        PRIMARY KEY (`id`)
      ) ENGINE=MEMORY AUTO_INCREMENT=13005 DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=42944
      1 row in set (0.00 sec)
      
      5.7.21>insert into sbtest85(json_column) select json_array(`k`,`c`,`pad`) from sbtest1;
      ERROR 1114 (HY000): The table 'sbtest85' is full
      

      Deleting rows:

      5.7.21>delete from sbtest85;
      Query OK, 388 rows affected (0.04 sec)
      

      Trying to insert single row:

      5.7.21>select * from sbtest85;
      Empty set (0.00 sec)
      
      5.7.21>insert into sbtest85(json_column) select json_array(`k`,`c`,`pad`) from sbtest1 limit 1;
      ERROR 1114 (HY000): The table 'sbtest85' is full
      

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                zsolt.parragi Zsolt Parragi
                Reporter:
                shahriyar.rzayev Shahriyar Rzayev (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 3 minutes
                  3m