# Set the environmental variables call mtr.add_suppression("Unable to read tablespace .* page no .* into the buffer pool after 100 attempts"); call mtr.add_suppression("innodb_checksum_algorithm is set to"); SET GLOBAL innodb_file_per_table=on; [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)); ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys ALTER TABLE tab1 ADD KEY (linestring_key(5)); ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys # 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 --default_storage_engine=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 --default_storage_engine=InnoDB 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, ibdata1 and mysql.ibd # restart: --innodb_checksum_algorithm=strict_none --default_storage_engine=InnoDB 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 # SDI 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 # Subsequent Compressed BLOB page # SDI BLOB page # Compressed SDI BLOB page # Other type of page =============================================== Additional information: Undo page type: # insert, # update, # other Undo page state: # active, # cached, # to_free, # to_purge, # prepared, # other [3]: Check the page type summary with longform for tab1.ibd File::tab#.ibd ================PAGE TYPE SUMMARY============== #PAGE_COUNT PAGE_TYPE =============================================== # Index page # SDI 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 # Subsequent Compressed BLOB page # SDI BLOB page # Compressed SDI BLOB page # Other type of page =============================================== Additional information: Undo page type: # insert, # update, # other Undo page state: # active, # cached, # to_free, # to_purge, # prepared, # other [4]: Page type dump for with longform for tab1.ibd # Print the contents stored in dump.txt Filename::MYSQLD_DATADIR/test/tab#.ibd ============================================================================== PAGE_NO | PAGE_TYPE | EXTRA INFO ============================================================================== #::0| File Space Header | - #::#| Insert Buffer Bitmap | - #::#| SDI Index page | index id=#, page level=0, No. of records=#, garbage=0, - #::#| Index page | index id=#, page level=0, No. of records=#, garbage=0, - #::#| Freshly allocated page | - #::#| Freshly allocated page | - [5]: Page type dump for with shortform for tab1.ibd Filename::MYSQLD_DATADIR/test/tab#.ibd ============================================================================== PAGE_NO | PAGE_TYPE | EXTRA INFO ============================================================================== #::0| File Space Header | - #::#| Insert Buffer Bitmap | - #::#| SDI Index page | index id=#, page level=0, No. of records=#, garbage=0, - #::#| Index page | index id=#, page level=0, No. of records=#, garbage=0, - #::#| Freshly allocated page | - #::#| Freshly allocated page | - [6]: check the valid lower bound values for option # allow-mismatches,page,start-page,end-page [7]: check the negative values for option # allow-mismatches,page,start-page,end-page. # They will reset to zero for negative values. # check the invalid lower bound values [8]: check the valid upper bound values for # both short and long options "allow-mismatches" and "end-page" [9]: check the both short and long options "page" and "start-page" when # seek value is larger than file size. Pattern "Error: Unable to seek to necessary offset: Invalid argument" found Pattern "Error: Unable to seek to necessary offset: Invalid argument" found Pattern "Error: Unable to seek to necessary offset: Invalid argument" found Pattern "Error: Unable to seek to necessary offset: Invalid argument" found [34]: check the invalid upper bound values for options, allow-mismatches, end-page, start-page and page. # innochecksum will fail with error code: 1 Pattern "Incorrect unsigned integer value: '18446744073709551616'" found Pattern "Incorrect unsigned integer value: '18446744073709551616'" found Pattern "Incorrect unsigned integer value: '18446744073709551616'" found Pattern "Incorrect unsigned integer value: '18446744073709551616'" found Pattern "Incorrect unsigned integer value: '18446744073709551616'" found Pattern "Incorrect unsigned integer value: '18446744073709551616'" found Pattern "Incorrect unsigned integer value: '18446744073709551616'" found Pattern "Incorrect unsigned integer value: '18446744073709551616'" found # Restart the server # restart: --innodb_checksum_algorithm=strict_none --default_storage_engine=InnoDB DROP TABLE tab1; DROP TABLE tab2; SET GLOBAL innodb_file_per_table=default;