mysql innodb table with inconsistent row_format

Question

I have a strange issue with MySQL (5.5.59):

I have a logs database (where I store raw data of supplier requests). This table is compressed:

CREATE TABLE `logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `idLogType` tinyint(3) unsigned NOT NULL,
  `idAccount` mediumint(8) unsigned NOT NULL,
  (...)
  `message` text NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `LOGTYPE` (`idLogType`),
  KEY `ACCOUNT` (`idAccount`),
) ENGINE=InnoDB AUTO_INCREMENT=(...) DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

My goal was to clean this table by removing old records and rebuilding the table. Since it is a big table, I used pt-online-schema-change and oak-chunk-update to do the job.

I removed all old last years records with https://shlomi-noach.github.io/openarkkit/oak-chunk-update.html

Then I perform a rebuild of the table to release free space (innodb_file_per_table is enabled

pt-online-schema-change 
    --alter "ENGINE=InnoDB" 
    --nocheck-replication-filters --execute --statistics --progress=percentage,1 
    --set-vars='lock_wait_timeout=60' --check-alter 
    --no-swap-tables --no-drop-triggers --no-drop-old-table --no-drop-new-table 
    --chunk-time=1 --chunk-size=20 --new-table-name='__new_logs'         
    h=**HOST_#########**,D=DB_#########,t=logs,u=root --ask-pass

(the important point is the --alter statement)

So now, I have 2 tables:

• logs (the original one)
• __new_logs the new one (optimized)

but they are not indentical in structure:

SELECT TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
FROM information_schema.tables  
WHERE 
    ENGINE = 'innodb' AND TABLE_NAME LIKE '%logs'

returns this result:

'TABLE_NAME'         'ENGINE'         'ROW_FORMAT'         'CREATE_OPTIONS',
'__new_logs'         'InnoDB'         'Compact'         'row_format=COMPRESSED KEY_BLOCK_SIZE=8',
'logs'         'InnoDB'         'Compressed'         'row_format=COMPRESSED KEY_BLOCK_SIZE=8',

Why the table __new_logs is flagged "compact" and not compressed, but still has "create options" set to row_format=COMPRESSED KEY_BLOCK_SIZE=8

A show create table of the __new_logs table shows:

CREATE TABLE `__new_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `idLogType` tinyint(3) unsigned NOT NULL,
  `idAccount` mediumint(8) unsigned NOT NULL,
  (...)
  `message` text NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `LOGTYPE` (`idLogType`),
  KEY `ACCOUNT` (`idAccount`),
) ENGINE=InnoDB AUTO_INCREMENT=(...) DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

So it is still marked as compressed.

Last strange thing, the table __new_logs is bigger than the original logs table... I feel like the compression is not really done on this new table...

Answer 1

I think I found the solution...

https://www.percona.com/blog/2014/01/14/innodb-file-formats-here-is-one-pitfall-to-avoid/

SHOW VARIABLES LIKE 'innodb_file_format'

=> Antelope.

Compression is only available with barracuda.

So my table logs which is already compressed, must have been compressed with innodb_file_format=Barracuda, but the variable certainly was reverted to Antelope...

Si I need to recreate the table... but this time with the good file format.