| Bug #73769 | Advisor for non compressed table with compression options | ||
|---|---|---|---|
| Submitted: | 29 Aug 2014 10:40 | Modified: | 1 Sep 2014 12:43 |
| Reporter: | Daniël van Eeden (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Enterprise Monitor: Advisors/Rules | Severity: | S3 (Non-critical) |
| Version: | 3.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[1 Sep 2014 12:43]
MySQL Verification Team
Hello Daniël, Thank you for the report and contribution. Thanks, Umesh
[1 Sep 2014 12:44]
MySQL Verification Team
//
mysql> show variables like 'innodb_file%';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
+--------------------------+----------+
4 rows in set (0.00 sec)
mysql> create table `test` (`a` int primary key) engine=innodb row_format=compressed;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |
+---------+------+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
1 row in set (0.00 sec)
mysql> show table status like 'test'\G
*************************** 1. row ***************************
Name: test
Engine: InnoDB
Version: 10
Row_format: Compact <=========
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2014-09-03 09:07:52
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: row_format=COMPRESSED <====
Comment:
1 row in set (0.00 sec)
Description: When a table is created with innodb_file_format=Antelope and row_format=compressed the table is not actually compressed as the Antelope format doesn't support compression. How to repeat: See description Suggested fix: Create advisor (pseudo cod): if mysql:tablestatus:Row_format != compressed: if mysql:tablestatus:Create_options like '%row_format=compressed%': error "Table with failed compression found"