MySQL Bugs: #64160: page size 1024 but the only supported page size in this release is=16384

Bug #64160	page size 1024 but the only supported page size in this release is=16384
Submitted:	30 Jan 2012 3:13	Modified:	30 Mar 2012 19:42
Reporter:	Emre Gilbert	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.5.20	OS:	Windows (2003)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:

I have upgraded from 5.1.59 to 5.5.20
Using InnoDB tables and converted some of them compression with block size 8.
After restart the server I am getting error below.
I can open with 5.1.59 however I can not see compress tables.
If I delete ibdata\ibdata2 and ibdata1 + iblogs I can not see innodb tables.

I have all ibd files.
Is there a way to fix this?

120130  4:59:57 [Note] Plugin 'FEDERATED' is disabled.
120130  4:59:57 InnoDB: The InnoDB memory heap is disabled
120130  4:59:57 InnoDB: Mutexes and rw_locks use Windows interlocked functions
120130  4:59:57 InnoDB: Compressed tables use zlib 1.2.3
120130  4:59:57 InnoDB: Initializing buffer pool, size = 1.0G
120130  4:59:57 InnoDB: Completed initialization of buffer pool
120130  4:59:57 InnoDB: Error: data file C:\Program Files\Parallels\Plesk\Databases\MySQL\Data\ibdata\ibdata2 uses page size 1024,
120130  4:59:57 InnoDB: but the only supported page size in this release is=16384
120130  4:59:57 InnoDB: Could not open or create data files.
120130  4:59:57 InnoDB: If you tried to add new data files, and it failed here,
120130  4:59:57 InnoDB: you should now edit innodb_data_file_path in my.cnf back
120130  4:59:57 InnoDB: to what it was, and remove the new ibdata files InnoDB created
120130  4:59:57 InnoDB: in this failed attempt. InnoDB only wrote those files full of
120130  4:59:57 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
120130  4:59:57 InnoDB: remove old data files which contain your precious data!
120130  4:59:57 [ERROR] Plugin 'InnoDB' init function returned error.
120130  4:59:57 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
120130  4:59:57 [ERROR] Unknown/unsupported storage engine: INNODB
120130  4:59:57 [ERROR] Aborting

----
my.ini
----
[mysqld]
port=3306

back_log = 50 #default
max_connect_errors = 10 #default
max_connections = 200 #300-200

basedir=C:\Program Files\\Parallels\\Plesk\\Databases\\MySQL
datadir=C:\Program Files\\Parallels\\Plesk\\Databases\\MySQL\\Data

default-storage-engine=INNODB
innodb_file_format=barracuda
innodb_file_per_table=1

key_buffer_size = 64M #128-80-64

query_cache_type = 1 # query cache
query_cache_limit = 4M #2-4
query_cache_size = 256M #48->64->80->96->128->160
#query_cache_min_res_unit=1K #1K-4K

long_query_time=10 #2
slow_query_log_file = slow-queries.log
binlog_cache_size = 1M

#sort_buffer_size = 128K
#read_buffer_size = 256K#512K-1M
#read_rnd_buffer_size = 128K#256K512K

sort_buffer_size = 4M #2->4
read_buffer_size = 2M
read_rnd_buffer_size = 10M #8->10

join_buffer_size = 4MB
myisam_sort_buffer_size = 16M #32-64M

max_allowed_packet=32M # 64->32 mysqlhotcopy icin
wait_timeout=150 # mysqlhotcopy icin
connect_timeout=150 #120
thread_concurrency = 8
table_open_cache = 2048 #1536-2048
thread_cache_size=20 #20-16-14-10-8-7

max_heap_table_size=48M #48->645
tmp_table_size=48M
max_tmp_tables=64
innodb_data_home_dir = C:\\Program Files\\Parallels\\Plesk\\Databases\\MySQL\\Data\\ibdata
innodb_data_file_path = ibdata1:732M;ibdata2:10M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory
innodb_buffer_pool_size=1024M #1024->1280
innodb_additional_mem_pool_size=16M #4->16
#
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=512M #256->512
innodb_log_buffer_size=16M #8->16
#
innodb_max_dirty_pages_pct = 90
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=50 #120
innodb_support_xa = 0
innodb_thread_concurrency=8 #4->8
#innodb_autoextend_increment=4

[client]
port=3306

Regards,
Emre

How to repeat:
mysql service is not starting.

Please, provide exact CREATE TABLE for compressed table you had created in 5.5.20.

Hello Valeriy,

Our tables' engine type are already InnoDB before upgrade

So I did not create any tables after upgrade but convert them compressed.
I am giving one of table's create sql statement that I compressed after upgrade
CREATE TABLE smf_attachments (
  id_attach int(10) unsigned NOT NULL auto_increment,
  id_thumb int(10) unsigned NOT NULL default '0',
  id_msg int(10) unsigned NOT NULL default '0',
  id_member mediumint(8) unsigned NOT NULL default '0',
  id_folder tinyint(3) NOT NULL default '1',
  attachment_type tinyint(3) unsigned NOT NULL default '0',
  filename varchar(255) NOT NULL default '',
  file_hash varchar(40) NOT NULL default '',
  fileext varchar(8) NOT NULL default '',
  size int(10) unsigned NOT NULL default '0',
  downloads mediumint(8) unsigned NOT NULL default '0',
  width mediumint(8) unsigned NOT NULL default '0',
  height mediumint(8) unsigned NOT NULL default '0',
  mime_type varchar(20) NOT NULL default '',
  approved tinyint(3) NOT NULL default '1',
  PRIMARY KEY (id_attach),
  UNIQUE id_member (id_member, id_attach),
  KEY id_msg (id_msg),
  KEY attachment_type (attachment_type)
) ENGINE=InnoDB;

Do you need all of create statements that are conventered to compress? or all tables are using myisam engine.
I use mostly below
alter table smf_attachments ENGINE=MyISAM ROW_FORMAT=COMPRESSED;

For one or two below
ALTER TABLE table_name smf_attachments ENGINE=MyISAM ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

Regards,
Emre

I discovered that I miswritten alter stament engine was innodb.
ALTER TABLE table_name name_of_innodb_tables ENGINE=INNODB ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;

ALTER TABLE table_name name_of_innodb_tables ENGINE=INNODB ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;

If I try to open without ibdata and logs, it is opening but I can only select myisam tables
desc smf_attachments;
ERROR 1146 (42S02): Table 'gilbaz_forumdb.smf_attachments' doesn't exist

Thank you for the report.

I can not repeat described behavior. Have you compiled 5.1 server yourself? Please send us output of SHOW STATUS LIKE 'Innodb_page_size' from both 5.1 and 5.5 servers.

I also found you had not specified --innodb_file_per_table and innodb_file_format=barracuda in the configuration provided. Have you missed these options required for KEY_BLOCK_SIZE and ROW_FORMAT=COMPRESSED?

InnoDDB in MySQL version 5.5.20 contains a check for a new filespace flag added to version 5.6.4.  This was intended to protect again using a tablespace in 5.5.20 created by 5.6.4 with the newly supported page sizes of 4096 and 8192.

However, this bug seems to indicate that some old ibdata tablespaces contain some unexpected bits set in the non-used portion of FSP_SPACE_FLAGS in the header page.

I think a possible solution is to only check for the wrong page size on non-system tablespaces that end in .ibd.  These kind of tablespaces were added after this field was assured to be fully initialized.

Hi Emre,

Thank you for your report!  This issue has been verified and will be addressed in an upcoming release.  

In the meantime, you should have no issue using 5.5.19.  This issue is due to a regression in 5.5.20.

Best Regards,

Matt

Hi Matthew, Kevin and Sveta,
Thanks for your feedbacks.
I understand that if I try to open with version 5.5.19 I'll open my database without problem.
I restored database from backup (before upgrade state)
Regards,

Hello,

Today i tried to upgrade my mysql DB from version 5.5.10 to 5.5.20 (MySQL-server-5.5.20-1.rhel5.x86_64.rpm) . On my test environment the upgrade went smooth without any problem.
On my production servers, the upgrade failed.
The mysql daemon did not started, with the below errors messages:
120214 18:19:18 InnoDB: Error: data file /var/lib/mysql/ibdata2 uses page size 131072,
120214 18:19:18 InnoDB: but the only supported page size in this release is=16384
120214 18:19:18 InnoDB: Could not open or create data files.
120214 18:19:18 InnoDB: If you tried to add new data files, and it failed here,
120214 18:19:18 InnoDB: you should now edit innodb_data_file_path in my.cnf back
120214 18:19:18 InnoDB: to what it was, and remove the new ibdata files InnoDB created
120214 18:19:18 InnoDB: in this failed attempt. InnoDB only wrote those files full of
120214 18:19:18 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
120214 18:19:18 InnoDB: remove old data files which contain your precious data!
120214 18:19:18 [ERROR] Plugin 'InnoDB' init function returned error.
120214 18:19:18 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
120214 18:19:18 [ERROR] Unknown/unsupported storage engine: InnoDB
120214 18:19:18 [ERROR] Aborting

The part from my.cnf regarding innodb is:
---------------------------------
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:26M;ibdata2:100M:autoextend
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 128M
---------------------------------

After downgrading to 5.5.19, the problem disappared.

Hello Antonis,
I'm glad to see someone handle that problem before it hurts too much like me :) anyway, downgrading to 5.5.19 indicates to solve that problem.
I hope to see mysql team will fix that problem in next release.
Regards,
Emre

Patch for Bug 64160, Oracle Bug 13698765

Attachment: 13698765.1st.patch (application/octet-stream, text), 545 bytes.

The patch attached here fixes this problem.

The problem was introduced in 5.5.20 where it tries to protect against downgrading from a version 5.6.4 database that was created with a page size other than 16k.  Version 5.6.4 supports page sizes 4k and 8k and stamps that page size into the header page of each tablespace file.  Version 5.5.20 attempts to read that page size in the file header.

But it turns out that only the first system tablespace file has a reliable flags field in the header. So only ibdata1 can be or needs to be tested for another page size. Extra system tablespace files like ibdata2, ibdata3, etc do not and should not be tested since the flags field is unreliable.  

This patch is basically a one line change that prevents the flags field of ibdata2, ibdata3, etc from being tested for a different page size created by a 5.6.4 engine.

i got the same error when i am doing a experiment of adding data files for system tablespace. 
before i add data file,i hava a data file in system tablespace(it is created by the innodb when i am first start mysqld, default is ibdata1).
then,i do some INSERT operations.you know the data file must be bigger.

now, i start the operation of add datafile to tablepace.
first, i calculate the orginal data files's size and round it to MB.
then i modify the my.cnf, set the ibdata1 to it's real size,
and then add the new datafile, then i start the mysqld successfully.
later, i restart mysqld, then an error occurs:
just like follow:

120307 20:52:59 InnoDB: Error: data file /styx/home/hzzhangbosen/mysql-5.5/data/ibdata2 uses page size 1024,
120307 20:52:59 InnoDB: but the only supported page size in this release is=16384
120307 20:52:59 InnoDB: Could not open or create data files.

Added to changelog for 5.5.22, 5.6.5: 

After using an ALTER TABLE statement to change the KEY_BLOCK_SIZE
property for an InnoDB table, for example when switching from an
uncompressed to a compressed table, subsequent server restarts could
fail with a message like:

InnoDB: Error: data file path/ibdata2 uses page size 1024,
InnoDB: but the only supported page size in this release is=16384

Added to changelog for 5.5.22, 5.6.5: 

After using an ALTER TABLE statement to change the KEY_BLOCK_SIZE
property for an InnoDB table, for example when switching from an
uncompressed to a compressed table, subsequent server restarts could
fail with a message like:

InnoDB: Error: data file path/ibdata2 uses page size 1024,
InnoDB: but the only supported page size in this release is=16384

http://bugs.mysql.com/bug.php?id=67979 marked as duplicate of this one.