# Set the environmental variables call mtr.add_suppression("InnoDB: Unable to read tablespace .* page no .* into the buffer pool after 100 attempts"); call mtr.add_suppression("InnoDB: innodb_checksum_algorithm is set to.*"); [1]: Further Test are for rewrite checksum (innodb|crc32|none) for all ibd file & start the server. CREATE TABLE tab1 (pk INTEGER NOT NULL PRIMARY KEY, linestring_key GEOMETRY NOT NULL, linestring_nokey GEOMETRY NOT NULL) ENGINE=InnoDB ; INSERT INTO tab1 (pk, linestring_key, linestring_nokey) VALUES (1, ST_GeomFromText('POINT(10 10) '), ST_GeomFromText('POINT(10 10) ')); CREATE INDEX linestring_index ON tab1(linestring_nokey(5)); ALTER TABLE tab1 ADD KEY (linestring_key(5)); # create a compressed table CREATE TABLE tab2(col_1 CHAR (255) , col_2 VARCHAR (255), col_3 longtext, col_4 longtext,col_5 longtext, col_6 longtext , col_7 int ) engine = innodb row_format=compressed key_block_size=4; CREATE INDEX idx1 ON tab2(col_3(10)); CREATE INDEX idx2 ON tab2(col_4(10)); CREATE INDEX idx3 ON tab2(col_5(10)); SET @col_1 = repeat('a', 5); SET @col_2 = repeat('b', 20); SET @col_3 = repeat('c', 100); SET @col_4 = repeat('d', 100); SET @col_5 = repeat('e', 100); SET @col_6 = repeat('f', 100); INSERT INTO tab2(col_1,col_2,col_3,col_4,col_5,col_6,col_7) VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,5); INSERT INTO tab2(col_1,col_2,col_3,col_4,col_5,col_6,col_7) VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,4); INSERT INTO tab2(col_1,col_2,col_3,col_4,col_5,col_6,col_7) VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,3); INSERT INTO tab2(col_1,col_2,col_3,col_4,col_5,col_6,col_7) VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,2); INSERT INTO tab2(col_1,col_2,col_3,col_4,col_5,col_6,col_7) VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,1); SELECT * FROM tab2 ORDER BY col_7; # stop the server [1(a)]: Rewrite into new checksum=InnoDB for all *.ibd file and ibdata1 : start the server with innodb_checksum_algorithm=strict_innodb # restart: --innodb_checksum_algorithm=strict_innodb INSERT INTO tab1 (pk, linestring_key, linestring_nokey) VALUES (2, ST_GeomFromText('LINESTRING(10 10,20 20,30 30)'), ST_GeomFromText('LINESTRING(10 10,20 20,30 30)')); SET @col_1 = repeat('a', 5); SET @col_2 = repeat('b', 20); SET @col_3 = repeat('c', 100); SET @col_4 = repeat('d', 100); SET @col_5 = repeat('e', 100); SET @col_6 = repeat('f', 100); INSERT INTO tab2(col_1,col_2,col_3,col_4,col_5,col_6,col_7) VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,6); SELECT pk,ST_AsText(linestring_key),ST_AsText(linestring_nokey) FROM tab1 ORDER BY pk; SELECT * FROM tab2 ORDER BY col_7; # stop the server [1(b)]: Rewrite into new checksum=crc32 for all *.ibd file and ibdata1 # start the server with innodb_checksum_algorithm=strict_crc32 # restart: --innodb_checksum_algorithm=strict_crc32 INSERT INTO tab1 (pk, linestring_key, linestring_nokey) VALUES (3, ST_GeomFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))'), ST_GeomFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))')); SET @col_1 = repeat('g', 5); SET @col_2 = repeat('h', 20); SET @col_3 = repeat('i', 100); SET @col_4 = repeat('j', 100); SET @col_5 = repeat('k', 100); SET @col_6 = repeat('l', 100); INSERT INTO tab2(col_1,col_2,col_3,col_4,col_5,col_6,col_7) VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,7); SELECT pk,ST_AsText(linestring_key),ST_AsText(linestring_nokey) FROM tab1 ORDER BY pk; SELECT * FROM tab2 ORDER BY col_7; # stop the server [1(c)]: Rewrite into new checksum=none for all *.ibd file and ibdata1 # restart: --innodb_checksum_algorithm=strict_none INSERT INTO tab1 (pk, linestring_key, linestring_nokey) VALUES (4, ST_GeomFromText('MULTIPOINT(0 0,5 5,10 10,20 20) '), ST_GeomFromText('MULTIPOINT(0 0,5 5,10 10,20 20) ')); SET @col_1 = repeat('m', 5); SET @col_2 = repeat('n', 20); SET @col_3 = repeat('o', 100); SET @col_4 = repeat('p', 100); SET @col_5 = repeat('q', 100); SET @col_6 = repeat('r', 100); INSERT INTO tab2(col_1,col_2,col_3,col_4,col_5,col_6,col_7) VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,8); SELECT pk,ST_AsText(linestring_key),ST_AsText(linestring_nokey) FROM tab1 ORDER BY pk; SELECT * FROM tab2 ORDER BY col_7; # stop the server [2]: Check the page type summary with shortform for tab1.ibd File::tab#.ibd ================PAGE TYPE SUMMARY============== #PAGE_COUNT PAGE_TYPE =============================================== # Index page # Undo log page # Inode page # Insert buffer free list page # Freshly allocated page # Insert buffer bitmap # System page # Transaction system page # File Space Header # Extent descriptor page # BLOB page # Compressed BLOB page # Page compressed page # Page compressed encrypted page # Other type of page =============================================== Additional information: Undo page type: # Undo page state: # active, # cached, # to_purge, # prepared, # other index_id #pages #leaf_pages #recs_per_page #bytes_per_page # # # # # # # # # # # # # # # index_id page_data_bytes_histgram(empty,...,oversized) # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # [3]: Check the page type summary with longform for tab1.ibd File::tab#.ibd ================PAGE TYPE SUMMARY============== #PAGE_COUNT PAGE_TYPE =============================================== # Index page # Undo log page # Inode page # Insert buffer free list page # Freshly allocated page # Insert buffer bitmap # System page # Transaction system page # File Space Header # Extent descriptor page # BLOB page # Compressed BLOB page # Page compressed page # Page compressed encrypted page # Other type of page =============================================== Additional information: Undo page type: # Undo page state: # active, # cached, # to_purge, # prepared, # other index_id #pages #leaf_pages #recs_per_page #bytes_per_page # # # # # # # # # # # # # # # index_id page_data_bytes_histgram(empty,...,oversized) # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # [4]: Page type dump for with longform for tab1.ibd # Print the contents stored in dump.txt Filename::tab#.ibd ============================================================================== PAGE_NO | PAGE_TYPE | EXTRA INFO ============================================================================== #::# | File Space Header | - #::# | Insert Buffer Bitmap | - #::# | Inode page | - #::# | Index page | index id=#, page level=#, No. of records=#, garbage=#, - #::# | Index page | index id=#, page level=#, No. of records=#, garbage=#, - #::# | Index page | index id=#, page level=#, No. of records=#, garbage=#, - #::# | Freshly allocated page | - #::# | Freshly allocated page | - # Variables used by page type dump for ibdata1 Variables (--variable-name=value) and boolean options {FALSE|TRUE} Value (after reading options) --------------------------------- ---------------------------------------- verbose TRUE count FALSE start-page 0 end-page 0 page 0 strict-check crc32 no-check FALSE allow-mismatches 0 write crc32 page-type-summary FALSE page-type-dump MYSQLTEST_VARDIR/tmp/dump.txt per-page-details FALSE log (No default value) leaf FALSE merge 0 [5]: Page type dump for with shortform for tab1.ibd Filename::tab#.ibd ============================================================================== PAGE_NO | PAGE_TYPE | EXTRA INFO ============================================================================== #::# | File Space Header | - #::# | Insert Buffer Bitmap | - #::# | Inode page | - #::# | Index page | index id=#, page level=#, No. of records=#, garbage=#, - #::# | Index page | index id=#, page level=#, No. of records=#, garbage=#, - #::# | Index page | index id=#, page level=#, No. of records=#, garbage=#, - #::# | Freshly allocated page | - #::# | Freshly allocated page | - [6]: check the valid lower bound values for option # allow-mismatches,page,start-page,end-page [9]: check the both short and long options "page" and "start-page" when # seek value is larger than file size. FOUND 1 /Error: Unable to seek to necessary offset/ in my_restart.err FOUND 1 /Error: Unable to seek to necessary offset/ in my_restart.err FOUND 1 /Error: Unable to seek to necessary offset/ in my_restart.err FOUND 1 /Error: Unable to seek to necessary offset/ in my_restart.err [34]: check the invalid upper bound values for options, allow-mismatches, end-page, start-page and page. # innochecksum will fail with error code: 1 NOT FOUND /Incorrect unsigned integer value: '18446744073709551616'/ in my_restart.err NOT FOUND /Incorrect unsigned integer value: '18446744073709551616'/ in my_restart.err NOT FOUND /Incorrect unsigned integer value: '18446744073709551616'/ in my_restart.err NOT FOUND /Incorrect unsigned integer value: '18446744073709551616'/ in my_restart.err NOT FOUND /Incorrect unsigned integer value: '18446744073709551616'/ in my_restart.err NOT FOUND /Incorrect unsigned integer value: '18446744073709551616'/ in my_restart.err NOT FOUND /Incorrect unsigned integer value: '18446744073709551616'/ in my_restart.err NOT FOUND /Incorrect unsigned integer value: '18446744073709551616'/ in my_restart.err # restart DROP TABLE tab1,tab2;