Bug #71325 stats_sample_pages is limited to 65535
Submitted: 9 Jan 2014 12:38
Reporter: Arnaud Adant Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.15 OS:Any
Assigned to: CPU Architecture:Any

[9 Jan 2014 12:38] Arnaud Adant
If you specify stats_sample_pages > 65535, manually on a table, you get this error :

mysql> alter table test.t stats_sample_pages = 65536;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '65536' at line 1

mysql> alter table test.t stats_sample_pages = 65535;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

The code documents this restriction :

 /* From user point of view STATS_SAMPLE_PAGES can be specified as             STATS_SAMPLE_PAGES=N (where 0<N<=65535, it does not make sense to             scan 0 pages) or STATS_SAMPLE_PAGES=default. Internally we record             =default as 0. See create_frm() in sql/table.cc, we use only two             bytes for stats_sample_pages and this is why we do not allow             larger values. 65535 pages, 16kb each means to sample 1GB, which             is impractical. If at some point this needs to be extended, then             we can store the higher bits from stats_sample_pages in .frm too. */             if ($3 == 0 || $3 > 0xffff)             {               my_parse_error(ER(ER_SYNTAX_ERROR));               MYSQL_YYABORT;             }

How to repeat:
create table t(id int) engine=InnoDB;
alter table t stats_sample_pages = 65536;
alter table t stats_sample_pages = 65535;

Suggested fix:
Either :

1. print a clearer message or a warning instead. Similar to setting global variables.

2. lift this restriction since innodb_page_size can be changed and higher values can be specified using innodb_stats_persistent_sample_pages.

mysql> set global innodb_stats_persistent_sample_pages = 70000;
Query OK, 0 rows affected (0.00 sec)

It think  2) is more user friendly.