MySQL Bugs: #35441: Cannot change PAGE

Bug #35441	Cannot change PAGE_CHECKSUM table option
Submitted:	19 Mar 2008 16:37	Modified:	20 Mar 2008 16:40
Reporter:	Marc Delisle	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Maria storage engine	Severity:	S3 (Non-critical)
Version:	5.1.23a-maria	OS:	Linux
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
With ALTER TABLE I am not able to set this option to 0.

How to repeat:
mysql> 
use test
create table test1 (f1 int) engine=maria;

show create table test1;
(it reports that PAGE_CHECKSUM is 1)

alter table test1 page_checksum=0;

show create table test1;
(it is still 1)

Thank you for the bug report.

[miguel@mira dbs]$ maria/bin/mysqladmin -uroot create db5
[miguel@mira dbs]$ maria/bin/mysql -uroot db5
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.24-maria-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test1 (f1 int) engine=maria;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `f1` int(11) DEFAULT NULL
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
1 row in set (0.01 sec)

mysql> alter table test1 PAGE_CHECKSUM=0;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `f1` int(11) DEFAULT NULL
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
1 row in set (0.00 sec)

mysql> create table test2 (f1 int) engine=maria PAGE_CHECKSUM=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table test2\G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `f1` int(11) DEFAULT NULL
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0
1 row in set (0.00 sec)

mysql> alter table test2 PAGE_CHECKSUM=1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test2\G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `f1` int(11) DEFAULT NULL
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
1 row in set (0.00 sec)

mysql>

Here are more complete results. Buggy lines marked with BUG:
create table t1(a int) engine=maria;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
alter table t1 page_checksum=0;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
BUG^
drop table t1;
create table t1(a int) engine=maria page_checksum=1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
alter table t1 page_checksum=0;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
BUG^
drop table t1;
create table t1(a int) engine=maria page_checksum=0;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0
alter table t1 page_checksum=1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
drop table t1;
select @@global.maria_page_checksum;
@@global.maria_page_checksum
1
create table t1(a int) engine=maria;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
set global maria_page_checksum= 0;
alter table t1 engine=maria;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
drop table t1;
create table t1(a int) engine=maria;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MARIA DEFAULT CHARSET=latin1
set global maria_page_checksum= 1;
alter table t1 engine=maria;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
BUG^ (ENGINE=MARIA is for reconstruction, like after upgrade, shouldn't change
page_checksum)
drop table t1;

Regarding the first of the three BUG lines:
1) "alter table" thinks it does not have to recreate the table but only fix the frm, compare_tables() should be fixed like this:
use ALTER_TABLE_DATA_CHANGED if
      create_info->used_fields & HA_CREATE_USED_PAGE_CHECKSUM
2) after doing a fix for 1), this piece of ha_maria::update_create_info() looks guilty: it replaces the HA_CHOICE_NO of the "alter table" by the HA_CHOICE_YES of the original table:
  /*
    Show always page checksums, as this can be forced with
    maria_page_checksums variable
  */
  if (file->s->options & HA_OPTION_PAGE_CHECKSUM)
    create_info->page_checksum= HA_CHOICE_YES;

note that update_create_info() serves both in ALTER TABLE and SHOW CREATE TABLE, which complicates things.

Another bug:

set global maria_page_checksum = 0 ;
create table t1(a int) engine=maria  ;
show create table t1;
set global maria_page_checksum = 0 ;
alter table t1 PAGE_CHECKSUM=1 ;
show create table t1;
The last SHOW CREATE TABLE above says table has page checksum, but maria_chk -dv says it has not.

I am attaching an exhaustive test file, and its result. If you look at this result file it shows the following problems:
error lineno 56 : expected PAGE_CHECKSUM=1, got Page checksums are not used
error lineno 73 : expected PAGE_CHECKSUM=0, got ) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
error lineno 110 : expected PAGE_CHECKSUM=1, got Page checksums are not used
error lineno 164 : expected PAGE_CHECKSUM=1, got Page checksums are not used
error lineno 181 : expected PAGE_CHECKSUM=0, got ) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
error lineno 218 : expected PAGE_CHECKSUM=1, got Page checksums are not used
error lineno 253 : expected PAGE_CHECKSUM=0, got ) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
error lineno 307 : expected PAGE_CHECKSUM=0, got ) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
error lineno 361 : expected PAGE_CHECKSUM=0, got ) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
error lineno 415 : expected PAGE_CHECKSUM=0, got ) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
error lineno 488 : expected PAGE_CHECKSUM=1, got Page checksums are not used
error lineno 505 : expected PAGE_CHECKSUM=0, got ) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
error lineno 542 : expected PAGE_CHECKSUM=1, got Page checksums are not used
error lineno 577 : expected PAGE_CHECKSUM=0, got ) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
error lineno 631 : expected PAGE_CHECKSUM=0, got ) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1

which can be grouped in three types:
- mismatch between SHOW CREATE TABLE and real engine's structure (SHOW CREATE TABLE says PAGE_CHECKSUM=1, maria_chk -dv does not)
- ALTER TABLE PAGE_CHECKSUM=0 without apparent effect
- ALTER TABLE ENGINE=MARIA influenced by maria_page_checksums when altering an existing Maria table, whereas existing table's checksum mode (yes/no) should be preserved, as ALTER TABLE ENGINE=MARIA can be used for upgrades or defragmentation.

test file for the 3 bugs

Attachment: bug3 (application/octet-stream, text), 0 bytes.

test file for the 3 bugs

Attachment: bug3.test (application/octet-stream, text), 37.27 KiB.

Result file showing 3 bugs

Attachment: bug3.reject (application/octet-stream, text), 20.74 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44291

ChangeSet@1.2619, 2008-03-20 16:05:23+01:00, guilhem@gbichot4.local +8 -0
  Fix for BUG#35441 "Cannot change PAGE_CHECKSUM table option".
  A big test was written and is committed, which found 3 bugs in total:
  - ALTER TABLE PAGE_CHECKSUM=0 sometimes had no effect
  - ALTER TABLE ENGINE=MARIA sometimes changed page checksumming in
  the table
  - SHOW CREATE TABLE and 'maria_chk -dv' disagreed on the presence
  of page checksumming.
  They are all fixed here. Side-effect is that SHOW CREATE TABLE now
  always prints a PAGE_CHECKSUM clause for Maria tables.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Nice work! thanks.