Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
This is a potential, unverified bug based on a verified critical bug of MySQL 8:
The SQL query
select * from performance_schema.data_locks |
will crash the MySQL server if there are data locks on the "fts_" table (internal table created by the Full-text Search feature) when the query is executed.
MariaDB does not have performance_schema.data_locks table. However, some versions of MariaDB have information_schema.innodb_locks table.
On MariaDB the SQL query
select * from information_schema.innodb_locks |
might crash the server if this SELECT attempts to print columns of the internal "fts_" table created by the Full-text Search feature.
For Mysql 8 the root cause of the crash is this: The Full-Text Search feature (FTS) creates internal InnoDB tables, such as the "fts_" table. This internal "fts_" contains a VARCHAR field "key" with precise type 0. If this field is printed by MySQL code, the "key" field with precise type 0 will cause a segmentation fault because 0 is not a valid precise type for a VARCHAR field.
How to repeat
Create the following 4 bash scripts:
1) "select":
mysql="mariadb -uroot -h0 -P19000 test " |
for ((i = 0 ; i < 10000 ; i++ )); do $mysql -e"select * from information_schema.INNODB_LOCKS"; done |
2) "schema":
drop table if exists ads_archive; |
CREATE TABLE `ads_archive` ( |
`advert_id` varchar(8) NOT NULL, |
`geo_id` varchar(16) NOT NULL, |
`region` varchar(255) DEFAULT NULL, |
`category_id` bigint NOT NULL, |
`shop_category_id` int DEFAULT NULL, |
`source` varchar(50) NOT NULL, |
`origin` varchar(25) NOT NULL, |
`last_modified_origin` varchar(25) NOT NULL, |
`subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, |
`body` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, |
`coordinates` point NOT NULL, |
`media_name` varchar(255) DEFAULT NULL, |
`ip_address` varchar(40) DEFAULT NULL, |
PRIMARY KEY (`advert_id`) USING BTREE, |
FULLTEXT KEY `idx_arch_region` (`region`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; |
3) "insert":
insert into test.ads_archive(advert_id,geo_id,category_id,source,origin,last_modified_origin,subject,body,coordinates) values ('aa','bb',1,'source','WEB','DESKTOP_WEB','subjekti','boody', point(1,1)); |
insert into test.ads_archive(advert_id,geo_id,category_id,source,origin,last_modified_origin,subject,body,coordinates) values ('bb','bc',1,'source','WEB','MOBILE_WEB','subjekti','boody', point(1,1)); |
insert into test.ads_archive(advert_id,geo_id,category_id,source,origin,last_modified_origin,subject,body,coordinates) values ('cc','bd',1,'source','WEB','MOBILE_WEB','subjekti','boody', point(1,1)); |
insert into test.ads_archive(advert_id,geo_id,category_id,source,origin,last_modified_origin,subject,body,coordinates) values ('dd','be',1,'source','WEB','MOBILE_WEB','subjekti','boody', point(1,1)); |
insert into test.ads_archive(advert_id,geo_id,category_id,source,origin,last_modified_origin,subject,body,coordinates) values ('ee','bf',1,'source','WEB','MOBILE_WEB','subjekti','boody', point(1,1)); |
insert into test.ads_archive(advert_id,geo_id,category_id,source,origin,last_modified_origin,subject,body,coordinates) values ('ff','bg',1,'source','WEB','MOBILE_WEB','subjekti','boody', point(1,1)); |
insert into test.ads_archive(advert_id,geo_id,category_id,source,origin,last_modified_origin,subject,body,coordinates) values ('gg','cc',1,'source','WEB','MOBILE_WEB','subjekti','boody', point(1,1)); |
insert into test.ads_archive(advert_id,geo_id,category_id,source,origin,last_modified_origin,subject,body,coordinates) values ('hh','dd',1,'source','WEB','MOBILE_WEB','subjekti','boody', point(1,1)); |
insert into test.ads_archive(advert_id,geo_id,category_id,source,origin,last_modified_origin,subject,body,coordinates) values ('ii','eee',1,'source','WEB','MOBILE_WEB','subjekti','boody', point(1,1)); |
insert into test.ads_archive(advert_id,geo_id,category_id,source,origin,last_modified_origin,subject,body,coordinates) values ('jj','fff',1,'source','WEB','MOBILE_WEB','subjekti','boody', point(1,1)); |
insert into test.ads_archive(advert_id,geo_id,category_id,source,origin,last_modified_origin,subject,body,coordinates) values ('kk','ggg',1,'source','WEB','MOBILE_WEB','subjekti','boody', point(1,1)); |
4) "run"
mysql -uroot -h0 -P19000 test -e "source schema" |
mysql -uroot -h0 -P19000 test -e "source insert" |
pt-online-schema-change --execute --progress time,30 --statistics --no-version-check --critical-load "Threads_running=1500" --max-load "Threads_running=500" --analyze-before-swap --drop-new-table --host localhost --user pscu --alter "MODIFY COLUMN origin ENUM('WEB','DESKTOP_WEB','MOBILE_WEB','ANDROID_APP','IOS_APP','CSV_UPLOAD','ANZEIGENCHEF','WEBSERVICE','IMPORT','ADMIN','ANDROID_TWA','IOS_TWA') NOT NULL, MODIFY COLUMN last_modified_origin ENUM('WEB','DESKTOP_WEB','MOBILE_WEB','ANDROID_APP','IOS_APP','CSV_UPLOAD','ANZEIGENCHEF','WEBSERVICE','IMPORT','ADMIN','ANDROID_TWA','IOS_TWA') NOT NULL, DROP COLUMN shop_category_id, DROP COLUMN media_name, ADD COLUMN ip VARBINARY(16) AFTER ip_address, ADD INDEX idx_arch_ip (ip)" D=test,t=ads_archive,u=root,h=0,P=19000 |
mysql -uroot -h0 -P19000 -e"drop table test.ads_archive" |
Then do the following:
- Install Percona's toolkit to get the pt-online-schema-change script (PT-OSC).
- Start mysqld by mtr:
./mtr --suite=innodb --start-and-exit
- Start the loop of selects from performance schema:
bash select
- Through anther session, execute the run script:
bash run
The run script will create and populate the test tables, and finally call for PT-OSC.
Crash may happen with the first attempt, but you may have to execute the run script several times.