1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
|
SET GLOBAL innodb_log_file_size=4194304;
SHOW VARIABLES LIKE 'innodb_log_file_size';
Variable_name Value
innodb_log_file_size 4194304
SELECT global_value FROM information_schema.system_variables
WHERE variable_name = 'innodb_log_file_size';
global_value
4194304
CREATE TABLE t (
a INT PRIMARY KEY AUTO_INCREMENT,
b CHAR(255) NOT NULL)
ENGINE=INNODB;
INSERT INTO t SELECT NULL, REPEAT('a', 255) FROM seq_1_to_20000;
# restart: --innodb-log-file-size=4194304
SELECT COUNT(*) FROM t;
COUNT(*)
20000
SHOW VARIABLES LIKE 'innodb_log_file_size';
Variable_name Value
innodb_log_file_size 4194304
FOUND 1 /InnoDB: Resized log to 4\.000MiB/ in mysqld.1.err
SET @save=@@global.innodb_log_file_buffering;
SET GLOBAL innodb_log_file_buffering=OFF;
SET GLOBAL innodb_log_file_buffering=ON;
SET GLOBAL innodb_log_file_buffering=@save;
SET GLOBAL innodb_log_file_mmap=OFF;
Got one of the listed errors
connect con1,localhost,root;
SET GLOBAL innodb_log_file_size=7340032;
connection default;
KILL QUERY @id;
connection con1;
connection default;
SET GLOBAL innodb_log_file_size=5242880;
connection con1;
UPDATE t SET b='' WHERE a<10;
connection default;
SHOW VARIABLES LIKE 'innodb_log_file_size';
Variable_name Value
innodb_log_file_size 5242880
SELECT global_value FROM information_schema.system_variables
WHERE variable_name = 'innodb_log_file_size';
global_value
5242880
connection con1;
disconnect con1;
connection default;
# restart
SELECT * FROM t WHERE a<10;
a b
1
2
3
4
5
6
7
8
9
SELECT COUNT(*),LENGTH(b) FROM t GROUP BY b;
COUNT(*) LENGTH(b)
9 0
19991 255
SHOW VARIABLES LIKE 'innodb_log_file_size';
Variable_name Value
innodb_log_file_size 5242880
SET GLOBAL innodb_log_file_size=6291456;
SHOW VARIABLES LIKE 'innodb_log_file_size';
Variable_name Value
innodb_log_file_size 6291456
SET GLOBAL innodb_log_file_size=5242880;
SHOW VARIABLES LIKE 'innodb_log_file_size';
Variable_name Value
innodb_log_file_size 5242880
FOUND 1 /InnoDB: Resized log to 6\.000MiB/ in mysqld.1.err
DROP TABLE t;
|