Bug #80389 Irrelevant warning while altering tablespace of compression='none' table

• Submitted: 16 Feb 2016 9:29
• Modified: 31 Mar 2016 5:14
• Reporter: Shahriyar Rzayev
• Status: Duplicate
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.7.11
• OS: CentOS (7)
• CPU Architecture: Any

[16 Feb 2016 9:29] Shahriyar Rzayev

Description:
Here is a table: 

CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `pad_c` int(11) GENERATED ALWAYS AS ((char_length(`pad`) + char_length(`c`))) STORED,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB AUTO_INCREMENT=1843230 DEFAULT CHARSET=latin1 COMPRESSION='none' ENCRYPTION='N'

Creating general tablespace:

mysql> create tablespace sbtest1 add datafile '/var/lib/mysql_tablespaces/sbtest1.ibd' engine=innodb;
Query OK, 0 rows affected (0,01 sec)

mysql> alter table sbtest1 tablespace=sbtest1;
Query OK, 1843229 rows affected, 1 warning (17,39 sec)
Records: 1843229  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning |  138 | InnoDB: Cannot compress pages of shared tablespaces |
+---------+------+-----------------------------------------------------+
1 row in set (0,00 sec)

So as general idea if there is no compression enabled then, there must be no warning.

How to repeat:
See description

Suggested fix:
do not show warning if there is no compression enabled.

[17 Feb 2016 9:12] MySQL Verification Team

Hello Shahriyar,

Thank you for the report and test case.

Thanks,
Umesh

[17 Feb 2016 9:13] MySQL Verification Team

-- 5.7.11

mysql> CREATE TABLE `sbtest1` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `k` int(10) unsigned NOT NULL DEFAULT '0',
    ->   `c` char(120) NOT NULL DEFAULT '',
    ->   `pad` char(60) NOT NULL DEFAULT '',
    ->   `pad_c` int(11) GENERATED ALWAYS AS ((char_length(`pad`) + char_length(`c`))) STORED,
    ->   PRIMARY KEY (`id`),
    ->   KEY `k_1` (`k`)
    -> ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB AUTO_INCREMENT=1843230 DEFAULT CHARSET=latin1 COMPRESSION='none' ENCRYPTION='N';
Query OK, 0 rows affected (0.01 sec)

mysql> create tablespace sbtest1 add datafile '/tmp/sbtest1.ibd' engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table sbtest1 tablespace=sbtest1;
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning |  138 | InnoDB: Cannot compress pages of shared tablespaces |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

[31 Mar 2016 5:14] Erlend Dahl

Fixed as a duplicate of

Bug#80182 Unsupported extension error for new features while 'create table like'