[MDEV-19805] Changing aria block size to 1024 will break install_system_tables Created: 2019-06-19  Updated: 2022-04-14  Resolved: 2019-06-27

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Aria, Storage Engine - Spider
Affects Version/s: 10.4
Fix Version/s: 10.4.7

Type: Bug Priority: Major
Reporter: VAROQUI Stephane Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-28311 aria_block_size minimum size 8192 Open
relates to MDEV-26688 Can no longer modify aria_block_size Confirmed

 Description   

To reproduce uncomment #aria_block_size=1024
I use docker lasted 10.4.5 

[mariadb]
 
 
 
aria_pagecache_buffer_size = 256M
 
aria_group_commit = hard
 
#aria_used_for_temp_tables = 1
 
aria_pagecache_file_hash_size = 1024
 
#aria_block_size=1024


mysql_upgrade
 
Phase 4/7: Running 'mysql_fix_privilege_tables'
 
ERROR 1071 (42000) at line 26: Specified key was too long; max key length is 209 bytes
 
ERROR 1071 (42000) at line 45: Specified key was too long; max key length is 209 bytes
 
ERROR 1071 (42000) at line 80: Specified key was too long; max key length is 209 bytes
 
ERROR 1071 (42000) at line 110: Specified key was too long; max key length is 209 bytes
 
ERROR 1071 (42000) at line 201: Specified key was too long; max key length is 209 bytes
 
ERROR 1071 (42000) at line 463: Specified key was too long; max key length is 209 bytes
 
ERROR 1071 (42000) at line 464: Specified key was too long; max key length is 209 bytes
 
ERROR 1071 (42000) at line 466: Specified key was too long; max key length is 209 bytes
 
ERROR 1071 (42000) at line 467: Specified key was too long; max key length is 209 bytes
 
ERROR 1071 (42000) at line 468: Specified key was too long; max key length is 209 bytes
 
ERROR 1904 (HY000) at line 469: Key/Index cannot be defined on a virtual generated column
 
ERROR 1071 (42000) at line 477: Specified key was too long; max key length is 209 bytes
 
ERROR 1071 (42000) at line 478: Specified key was too long; max key length is 209 bytes
 
ERROR 1071 (42000) at line 479: Specified key was too long; max key length is 209 bytes
 
ERROR 1071 (42000) at line 484: Specified key was too long; max key length is 209 bytes
 
ERROR 1071 (42000) at line 485: Specified key was too long; max key length is 209 bytes
 
ERROR 1071 (42000) at line 486: Specified key was too long; max key length is 209 bytes
 
ERROR 1071 (42000) at line 488: Specified key was too long; max key length is 209 bytes
 
ERROR 1071 (42000) at line 549: Specified key was too long; max key length is 209 bytes
 
FATAL ERROR: Upgrade failed

There is a more generic question raised by Aria replacing MyISAM for temporary tables
ARIA should at minimum support the same dictionary limits as MyISAM if not will it break many workload?

I'd like to be wrong but if would probably means mean block size of 16K and that would be a pure no go for temporary table usage.

Any advice to come back to MyISAM temp table in such case ?

 Comments   
Comment by Alice Sherepa [ 2019-06-19 ]

The same error with aria_block_size=2048. Works with aria_block_size=4096 (I tried 10.4.5)

Comment by Michael Widenius [ 2019-06-24 ]

One can't anymore have a block size of 1K for Aria tables. I have changed the minimum allowed size to 4096 to not allow people to create a setup that will break their installation. There should not be any notable performance differences between 1K and 4K, especially for temporary tables. In most cases using bigger blocks is also faster (with the possible exception of doing key reads of not fixed length keys).

Aria has been default for temporary tables since 5.5 and the default for block size has been 8K. I haven't heard of anyone actually changing this so I don't think there is a big problem in fixing the size for temporary tables.

One option, if this becomes a problem, would be to have a separate page cache for temporary tables. This page cache could have totally different limits compared to the normal page cache.

Comment by VAROQUI Stephane [ 2019-06-24 ]

Thanks for this quick answer, i pick such practice from MDEV-9079 that is now fixed, so ll update my practices based on your last comment.

Comment by Michael Widenius [ 2019-06-27 ]

Fix pushed

Generated at Thu Feb 08 08:54:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.