Description:
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.
© 2025, Oracle Corporation and/or its affiliates
Description: 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.