• Michael Widenius's avatar
    Added some fixes that should make MyISAM & Aria REPAIR work with more than 4G records · 53d44ad1
    Michael Widenius authored
    - If one specifies --force twice to myisamchk and aria_chk, then we will try to finnish the repair even if sort_buffer would be too small.
      This was done by dynamically allocate buffer handler objects as long as memory lasts.
    - New option for myisamchk and aria_chk: create-missing-keys
    - Changed default size of myisam_sort_buffer_size from 8M to 128M.
    - Changed default size of sort_buffer_size in aria_chk from 128M to 256M.
    - Increased information in error message about 'sort_buffer_size' beeing to small.
    - Print also to 'show warnings' if repair was retried.
    - Increased size of internal sort-buffer-readers from 16K to 128K
    - Changed printing of 'number of records' to use %ll instead of casting to long
    - Changed buffer sizes for myisam and aria to use MY_ALIGN_DOWN() to get same number of bytes allocated on different machines.
    
    
    
    
    
    include/my_global.h:
      Added MY_ALIGN_DOWN() to get previous alignment (for big memory areas)
    include/myisam.h:
      Increased size of types to be able to handle more records
    include/myisamchk.h:
      Increased size of types to be able to handle more records
      Added T_FORCE_SORT_MEMORY to force repair to work even if sort_buffer would not be big enough
    mysql-test/r/myisam.result:
      Updated result
    mysql-test/r/mysqld--help.result:
      Updated result
    mysql-test/r/repair.result:
      Updated result
    mysql-test/suite/maria/maria.result:
      Added test cases for some fixed bugs in MyISAM to verify that Aria doesn't have them too.
    mysql-test/suite/maria/maria.test:
      Added test cases for some fixed bugs in MyISAM to verify that Aria doesn't have them too.
    mysql-test/suite/maria/maria3.result:
      Updated result after sort buffer size increase
    mysql-test/suite/sys_vars/r/aria_sort_buffer_size_basic.result:
      Updated result after sort buffer size increase
    mysql-test/suite/sys_vars/r/myisam_sort_buffer_size_basic.result:
      Updated result after sort buffer size increase
    mysql-test/suite/sys_vars/t/myisam_sort_buffer_size_basic.test:
      Updated result after sort buffer size increase
    mysql-test/t/myisam.test:
      Fixed error messages to not print system specific data
    mysql-test/t/repair.test:
      Fixed error messages to not print system specific data
    storage/maria/ha_maria.cc:
      Print also to 'show warnings' if repair was retried
      Changed default size of sort_buffer_size from 128M to 256M (same as in mysqld)
    storage/maria/ma_check.c:
      Renamed USE_BUFFER_INIT -> PAGE_BUFFER_INIT
    storage/maria/ma_sort.c:
      Increased size of internal sort-buffer-readers from 16K to 128K
      Increased size of types to be able to handle more records
      Added support for T_FORCE_SORT_MEMORY
      Don't allocate too many extra BUFFPEK at a time (they are probably not needed)
      Improved error message for "sort_buffer_size is too small"
      Changed printing of 'number of records' to use %ll instead of casting to long
      Fixed bug where maria_update_key_parts() was called too early.
      Fixed bug in detecting result from read_to_buffer().
      Added 'out of memory' checking when calling 'alloc_dynamic()'.
    storage/maria/maria_chk.c:
      Added --create-missing-keys
      If one specifies --force twice then we will try to finnish the repair even if sort_buffer would be too small.
      check_param.sort_buffer_length varialble was used with wrong type.
    storage/maria/maria_def.h:
      Increased size of types to be able to handle more records
      Use MY_ALIGN_DOWN() to get same number of bytes allocated on different machines
      Renamed USE_BUFFER_INIT -> PAGE_BUFFER_INIT
    storage/maria/maria_ftdump.c:
      Renamed USE_BUFFER_INIT -> PAGE_BUFFER_INIT
    storage/maria/maria_read_log.c:
      Use PAGE_BUFFER_INIT for page cache
    storage/myisam/ha_myisam.cc:
      Changed default size of myisam_sort_buffer_size from 8M to 128M
    storage/myisam/mi_check.c:
      Renamed USE_BUFFER_INIT -> KEY_BUFFER_INIT
    storage/myisam/myisam_ftdump.c:
      Renamed USE_BUFFER_INIT -> KEY_BUFFER_INIT
    storage/myisam/myisamchk.c:
      Added --create-missing-keys
      If one specifies --force twice then we will try to finnish the repair even if sort_buffer would be too small.
      check_param.sort_buffer_length varialble was used with wrong type.
      Renamed USE_BUFFER_INIT -> KEY_BUFFER_INIT
    storage/myisam/myisamdef.h:
      Increased SORT_BUFFER_INIT to 64M
      (speeds up repair a lot and most machines have nowadays a lot of memory)
      Use MY_ALIGN_DOWN() to get same number of bytes allocated on different machines
      Renamed USE_BUFFER_INIT -> KEY_BUFFER_INIT
    storage/myisam/sort.c:
      Increased size of internal sort-buffer-readers from 16K to 128K
      Increased size of types to be able to handle more records
      Added support for T_FORCE_SORT_MEMORY
      Don't allocate too many extra BUFFPEK at a time (they are probably not needed)
      Improved error message for "sort_buffer_size is too small"
      Changed printing of 'number of records' to use %ll instead of casting to long
      Fixed bug in detecting result from read_to_buffer().
      Added 'out of memory' checking when calling 'alloc_dynamic()'.
    53d44ad1
repair.result 7.03 KB
drop table if exists t1;
create table t1 SELECT 1,"table 1";
repair table t1 use_frm;
Table	Op	Msg_type	Msg_text
test.t1	repair	warning	Number of rows changed from 0 to 1
test.t1	repair	status	OK
alter table t1 ENGINE=HEAP;
repair table t1 use_frm;
Table	Op	Msg_type	Msg_text
test.t1	repair	note	The storage engine for the table doesn't support repair
drop table t1;
create table t1(id int PRIMARY KEY, st varchar(10), KEY st_key(st));
insert into t1 values(1, "One");
alter table t1 disable keys;
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t1	0	PRIMARY	1	id	A	1	NULL	NULL		BTREE		
t1	1	st_key	1	st	A	NULL	NULL	NULL	YES	BTREE	disabled	
repair table t1 extended;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t1	0	PRIMARY	1	id	A	1	NULL	NULL		BTREE		
t1	1	st_key	1	st	A	NULL	NULL	NULL	YES	BTREE	disabled	
drop table t1;
repair table t1 use_frm;
Table	Op	Msg_type	Msg_text
test.t1	repair	Error	Table 'test.t1' doesn't exist
test.t1	repair	status	Operation failed
create table t1 engine=myisam SELECT 1,"table 1";
flush tables;
repair table t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	Error	Incorrect file format 't1'
test.t1	repair	error	Corrupt
repair table t1 use_frm;
Table	Op	Msg_type	Msg_text
test.t1	repair	warning	Number of rows changed from 0 to 1
test.t1	repair	status	OK
drop table t1;
CREATE TABLE t1(a INT, KEY(a));
INSERT INTO t1 VALUES(1),(2),(3),(4),(5);
SET myisam_repair_threads=2;
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
SHOW INDEX FROM t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t1	1	a	1	a	A	5	NULL	NULL	YES	BTREE		
SET myisam_repair_threads=@@global.myisam_repair_threads;
DROP TABLE t1;
CREATE TABLE t1(a INT);
USE mysql;
REPAIR TABLE test.t1 USE_FRM;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
USE test;
DROP TABLE t1;
CREATE TABLE t1(a CHAR(255), KEY(a));
SET myisam_sort_buffer_size=4096;
INSERT INTO t1 VALUES
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0');
Warnings:
Error	1034	myisam_sort_buffer_size is too small. X
Error	1034	Number of rows changed from 0 to 157
SET myisam_repair_threads=2;
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	error	myisam_sort_buffer_size is too small. X
test.t1	repair	warning	Number of rows changed from 0 to 157
test.t1	repair	status	OK
SET myisam_repair_threads=@@global.myisam_repair_threads;
SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size;
DROP TABLE t1;
CREATE TABLE t1(a CHAR(255), KEY(a));
SET myisam_sort_buffer_size=4496;
INSERT INTO t1 VALUES
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0');
SET myisam_repair_threads=2;
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
SET myisam_repair_threads=@@global.myisam_repair_threads;
SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size;
DROP TABLE t1;
End of 4.1 tests
# Test with a saved table from 4.1
SHOW TABLE STATUS LIKE 't1';
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
t1	MyISAM	9	Fixed	2	5	10	21474836479	1024	0	NULL	#	#	NULL	latin1_swedish_ci	NULL		
SELECT * FROM t1;
id
1
2
# Run CHECK TABLE, it should indicate table need a REPAIR TABLE
CHECK TABLE t1 FOR UPGRADE;
Table	Op	Msg_type	Msg_text
test.t1	check	error	Table upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it!
# REPAIR old table USE_FRM should fail
REPAIR TABLE t1 USE_FRM;
Table	Op	Msg_type	Msg_text
t1	repair	error	Failed repairing incompatible .frm file
# Run REPAIR TABLE to upgrade .frm file
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
SHOW TABLE STATUS LIKE 't1';
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
t1	MyISAM	10	Fixed	2	7	14	1970324836974591	1024	0	NULL	#	#	NULL	latin1_swedish_ci	NULL		
SELECT * FROM t1;
id
1
2
REPAIR TABLE t1 USE_FRM;
Table	Op	Msg_type	Msg_text
test.t1	repair	warning	Number of rows changed from 0 to 2
test.t1	repair	status	OK
SELECT * FROM t1;
id
1
2
DROP TABLE t1;
DROP TABLE IF EXISTS tt1;
CREATE TEMPORARY TABLE tt1 (c1 INT);
REPAIR TABLE tt1 USE_FRM;
Table	Op	Msg_type	Msg_text
tt1	repair	error	Cannot repair temporary table from .frm file
DROP TABLE tt1;
#
# Bug #48248 assert in MDL_ticket::upgrade_shared_lock_to_exclusive
#
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INT);
LOCK TABLES t1 READ;
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	Error	Table 't1' was locked with a READ lock and can't be updated
test.t1	repair	status	Operation failed
UNLOCK TABLES;
DROP TABLE t1;
#
# Test for bug #50784 "MDL: Assertion `m_tickets.is_empty() ||
#                      m_tickets.front() == m_trans_sentinel'"
#
drop tables if exists t1, t2;
create table t1 (i int);
create table t2 (j int);
set @@autocommit= 0;
repair table t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
test.t2	repair	status	OK
set @@autocommit= default;
drop tables t1, t2;