diff options
Diffstat (limited to 'storage/spider/scripts/install_spider.sql')
| -rw-r--r-- | storage/spider/scripts/install_spider.sql | 543 |
1 files changed, 1 insertions, 542 deletions
diff --git a/storage/spider/scripts/install_spider.sql b/storage/spider/scripts/install_spider.sql index f52c78e496d..403bd99fd68 100644 --- a/storage/spider/scripts/install_spider.sql +++ b/storage/spider/scripts/install_spider.sql @@ -1,4 +1,4 @@ -# Copyright (C) 2010-2018 Kentoku Shiba +# Copyright (C) 2010-2019 Kentoku Shiba # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by @@ -13,482 +13,6 @@ # along with this program; if not, write to the Free Software # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA -# This SQL script creates system tables for SPIDER -# or fixes incompatibilities if ones already exist. - --- Create system tables if not exist -create table if not exists mysql.spider_xa( - format_id int not null default 0, - gtrid_length int not null default 0, - bqual_length int not null default 0, - data char(128) charset binary not null default '', - status char(8) not null default '', - primary key (data, format_id, gtrid_length), - key idx1 (status) -) engine=MyISAM default charset=utf8 collate=utf8_bin; -create table if not exists mysql.spider_xa_member( - format_id int not null default 0, - gtrid_length int not null default 0, - bqual_length int not null default 0, - data char(128) charset binary not null default '', - scheme char(64) not null default '', - host char(64) not null default '', - port char(5) not null default '', - socket text not null, - username char(64) not null default '', - password char(64) not null default '', - ssl_ca text, - ssl_capath text, - ssl_cert text, - ssl_cipher char(64) default null, - ssl_key text, - ssl_verify_server_cert tinyint not null default 0, - default_file text, - default_group char(64) default null, - key idx1 (data, format_id, gtrid_length, host) -) engine=MyISAM default charset=utf8 collate=utf8_bin; -create table if not exists mysql.spider_xa_failed_log( - format_id int not null default 0, - gtrid_length int not null default 0, - bqual_length int not null default 0, - data char(128) charset binary not null default '', - scheme char(64) not null default '', - host char(64) not null default '', - port char(5) not null default '', - socket text not null, - username char(64) not null default '', - password char(64) not null default '', - ssl_ca text, - ssl_capath text, - ssl_cert text, - ssl_cipher char(64) default null, - ssl_key text, - ssl_verify_server_cert tinyint not null default 0, - default_file text, - default_group char(64) default null, - thread_id int default null, - status char(8) not null default '', - failed_time timestamp not null default current_timestamp, - key idx1 (data, format_id, gtrid_length, host) -) engine=MyISAM default charset=utf8 collate=utf8_bin; -create table if not exists mysql.spider_tables( - db_name char(64) not null default '', - table_name char(199) not null default '', - link_id int not null default 0, - priority bigint not null default 0, - server char(64) default null, - scheme char(64) default null, - host char(64) default null, - port char(5) default null, - socket text, - username char(64) default null, - password char(64) default null, - ssl_ca text, - ssl_capath text, - ssl_cert text, - ssl_cipher char(64) default null, - ssl_key text, - ssl_verify_server_cert tinyint not null default 0, - monitoring_binlog_pos_at_failing tinyint not null default 0, - default_file text, - default_group char(64) default null, - tgt_db_name char(64) default null, - tgt_table_name char(64) default null, - link_status tinyint not null default 1, - block_status tinyint not null default 0, - static_link_id char(64) default null, - primary key (db_name, table_name, link_id), - key idx1 (priority), - unique key uidx1 (db_name, table_name, static_link_id) -) engine=MyISAM default charset=utf8 collate=utf8_bin; -create table if not exists mysql.spider_link_mon_servers( - db_name char(64) not null default '', - table_name char(199) not null default '', - link_id char(64) not null default '', - sid int unsigned not null default 0, - server char(64) default null, - scheme char(64) default null, - host char(64) default null, - port char(5) default null, - socket text, - username char(64) default null, - password char(64) default null, - ssl_ca text, - ssl_capath text, - ssl_cert text, - ssl_cipher char(64) default null, - ssl_key text, - ssl_verify_server_cert tinyint not null default 0, - default_file text, - default_group char(64) default null, - primary key (db_name, table_name, link_id, sid) -) engine=MyISAM default charset=utf8 collate=utf8_bin; -create table if not exists mysql.spider_link_failed_log( - db_name char(64) not null default '', - table_name char(199) not null default '', - link_id char(64) not null default '', - failed_time timestamp not null default current_timestamp -) engine=MyISAM default charset=utf8 collate=utf8_bin; -create table if not exists mysql.spider_table_position_for_recovery( - db_name char(64) not null default '', - table_name char(199) not null default '', - failed_link_id int not null default 0, - source_link_id int not null default 0, - file text, - position text, - gtid text, - primary key (db_name, table_name, failed_link_id, source_link_id) -) engine=MyISAM default charset=utf8 collate=utf8_bin; -create table if not exists mysql.spider_table_sts( - db_name char(64) not null default '', - table_name char(199) not null default '', - data_file_length bigint unsigned not null default 0, - max_data_file_length bigint unsigned not null default 0, - index_file_length bigint unsigned not null default 0, - records bigint unsigned not null default 0, - mean_rec_length bigint unsigned not null default 0, - check_time datetime not null default '0000-00-00 00:00:00', - create_time datetime not null default '0000-00-00 00:00:00', - update_time datetime not null default '0000-00-00 00:00:00', - primary key (db_name, table_name) -) engine=MyISAM default charset=utf8 collate=utf8_bin; -create table if not exists mysql.spider_table_crd( - db_name char(64) not null default '', - table_name char(199) not null default '', - key_seq int unsigned not null default 0, - cardinality bigint not null default 0, - primary key (db_name, table_name, key_seq) -) engine=MyISAM default charset=utf8 collate=utf8_bin; - --- If tables already exist and their definition differ from the latest ones, --- we fix them here. -drop procedure if exists mysql.spider_fix_one_table; -drop procedure if exists mysql.spider_fix_system_tables; -delimiter // -create procedure mysql.spider_fix_one_table - (tab_name char(255) charset utf8 collate utf8_bin, - test_col_name char(255) charset utf8 collate utf8_bin, - _sql text charset utf8 collate utf8_bin) -begin - set @col_exists := 0; - select 1 into @col_exists from INFORMATION_SCHEMA.COLUMNS - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = tab_name - AND COLUMN_NAME = test_col_name; - if @col_exists = 0 then - select @stmt := _sql; - prepare sp_stmt1 from @stmt; - execute sp_stmt1; - end if; -end;// - -create procedure mysql.spider_fix_system_tables() -begin - select substring_index(substring_index(version(), '-', 2), '-', -1) - into @server_name; - select substring_index(version(), '.', 1) - into @server_major_version; - select substring_index(substring_index(version(), '.', 2), '.', -1) - into @server_minor_version; - - -- Fix for 0.5 - call mysql.spider_fix_one_table('spider_tables', 'server', - 'alter table mysql.spider_tables - add server char(64) default null, - add scheme char(64) default null, - add host char(64) default null, - add port char(5) default null, - add socket char(64) default null, - add username char(64) default null, - add password char(64) default null, - add tgt_db_name char(64) default null, - add tgt_table_name char(64) default null'); - - -- Fix for version 0.17 - select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_xa' - AND COLUMN_NAME = 'data'; - if @col_type != 'binary(128)' then - alter table mysql.spider_xa modify data binary(128) not null default ''; - end if; - select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_xa_member' - AND COLUMN_NAME = 'data'; - if @col_type != 'binary(128)' then - alter table mysql.spider_xa_member modify data binary(128) not null default ''; - end if; - - -- Fix for version 2.7 - call mysql.spider_fix_one_table('spider_tables', 'link_id', - 'alter table mysql.spider_tables - add column link_id int not null default 0 after table_name, - drop primary key, - add primary key (db_name, table_name, link_id)'); - - -- Fix for version 2.8 - call mysql.spider_fix_one_table('spider_tables', 'link_status', - 'alter table mysql.spider_tables - add column link_status tinyint not null default 1'); - - -- Fix for version 2.10 - call mysql.spider_fix_one_table('spider_xa_member', 'ssl_ca', - 'alter table mysql.spider_xa_member - add column ssl_ca char(64) default null after password, - add column ssl_capath char(64) default null after ssl_ca, - add column ssl_cert char(64) default null after ssl_capath, - add column ssl_cipher char(64) default null after ssl_cert, - add column ssl_key char(64) default null after ssl_cipher, - add column ssl_verify_server_cert tinyint not null default 0 after ssl_key, - add column default_file char(64) default null after ssl_verify_server_cert, - add column default_group char(64) default null after default_file'); - call mysql.spider_fix_one_table('spider_tables', 'ssl_ca', - 'alter table mysql.spider_tables - add column ssl_ca char(64) default null after password, - add column ssl_capath char(64) default null after ssl_ca, - add column ssl_cert char(64) default null after ssl_capath, - add column ssl_cipher char(64) default null after ssl_cert, - add column ssl_key char(64) default null after ssl_cipher, - add column ssl_verify_server_cert tinyint not null default 0 after ssl_key, - add column default_file char(64) default null after ssl_verify_server_cert, - add column default_group char(64) default null after default_file'); - call mysql.spider_fix_one_table('spider_link_mon_servers', 'ssl_ca', - 'alter table mysql.spider_link_mon_servers - add column ssl_ca char(64) default null after password, - add column ssl_capath char(64) default null after ssl_ca, - add column ssl_cert char(64) default null after ssl_capath, - add column ssl_cipher char(64) default null after ssl_cert, - add column ssl_key char(64) default null after ssl_cipher, - add column ssl_verify_server_cert tinyint not null default 0 after ssl_key, - add column default_file char(64) default null after ssl_verify_server_cert, - add column default_group char(64) default null after default_file'); - - -- Fix for version 2.25 - -- select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS - -- where TABLE_SCHEMA = 'mysql' - -- AND TABLE_NAME = 'spider_link_mon_servers' - -- AND COLUMN_NAME = 'link_id'; - -- if @col_type != 'char(5)' then - -- alter table mysql.spider_link_mon_servers - -- modify link_id char(5) not null default ''; - -- end if; - - -- Fix for version 2.28 - select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_link_mon_servers' - AND COLUMN_NAME = 'sid'; - if @col_type != 'int(10) unsigned' then - alter table mysql.spider_link_mon_servers - modify sid int unsigned not null default 0; - end if; - - -- Fix for version 3.1 - select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_xa_member' - AND COLUMN_NAME = 'socket'; - if @col_type = 'char(64)' then - alter table mysql.spider_xa_member - drop primary key, - add index idx1 (data, format_id, gtrid_length, host), - modify socket text not null, - modify ssl_ca text, - modify ssl_capath text, - modify ssl_cert text, - modify ssl_key text, - modify default_file text; - end if; - select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_tables' - AND COLUMN_NAME = 'socket'; - if @col_type = 'char(64)' then - alter table mysql.spider_tables - modify socket text, - modify ssl_ca text, - modify ssl_capath text, - modify ssl_cert text, - modify ssl_key text, - modify default_file text; - end if; - select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_link_mon_servers' - AND COLUMN_NAME = 'socket'; - if @col_type = 'char(64)' then - alter table mysql.spider_link_mon_servers - modify socket text, - modify ssl_ca text, - modify ssl_capath text, - modify ssl_cert text, - modify ssl_key text, - modify default_file text; - end if; - - -- Fix for version 3.3.0 - call mysql.spider_fix_one_table('spider_tables', - 'monitoring_binlog_pos_at_failing', - 'alter table mysql.spider_tables - add monitoring_binlog_pos_at_failing tinyint not null default 0 after ssl_verify_server_cert'); - - -- Fix for version 3.3.6 - call mysql.spider_fix_one_table('spider_tables', 'block_status', - 'alter table mysql.spider_tables - add column block_status tinyint not null default 0 after link_status'); - call mysql.spider_fix_one_table('spider_tables', 'static_link_id', - 'alter table mysql.spider_tables - add column static_link_id char(64) default null after block_status, - add unique index uidx1 (db_name, table_name, static_link_id)'); - select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_link_mon_servers' - AND COLUMN_NAME = 'link_id'; - if @col_type != 'char(64)' then - alter table mysql.spider_link_mon_servers - modify link_id char(64) not null default ''; - end if; - select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_link_failed_log' - AND COLUMN_NAME = 'link_id'; - if @col_type != 'char(64)' then - alter table mysql.spider_link_failed_log - modify link_id char(64) not null default ''; - end if; - - -- Fix for version 3.3.10 - select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_tables' - AND COLUMN_NAME = 'table_name'; - if @col_type != 'char(199)' then - alter table mysql.spider_tables - modify table_name char(199) not null default ''; - end if; - select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_link_mon_servers' - AND COLUMN_NAME = 'table_name'; - if @col_type != 'char(199)' then - alter table mysql.spider_link_mon_servers - modify table_name char(199) not null default ''; - end if; - select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_link_failed_log' - AND COLUMN_NAME = 'table_name'; - if @col_type != 'char(199)' then - alter table mysql.spider_link_failed_log - modify table_name char(199) not null default ''; - end if; - select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_table_position_for_recovery' - AND COLUMN_NAME = 'table_name'; - if @col_type != 'char(199)' then - alter table mysql.spider_table_position_for_recovery - modify table_name char(199) not null default ''; - end if; - select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_table_sts' - AND COLUMN_NAME = 'table_name'; - if @col_type != 'char(199)' then - alter table mysql.spider_table_sts - modify table_name char(199) not null default ''; - end if; - select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_table_crd' - AND COLUMN_NAME = 'table_name'; - if @col_type != 'char(199)' then - alter table mysql.spider_table_crd - modify table_name char(199) not null default ''; - end if; - - -- Fix for MariaDB 10.4: Crash-Safe system tables - if @server_name = 'MariaDB' and - ( - @server_major_version > 10 or - ( - @server_major_version = 10 and - @server_minor_version >= 4 - ) - ) - then - select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_link_failed_log'; - if @engine_name != 'Aria' then - alter table mysql.spider_link_failed_log - engine=Aria transactional=1; - end if; - select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_link_mon_servers'; - if @engine_name != 'Aria' then - alter table mysql.spider_link_mon_servers - engine=Aria transactional=1; - end if; - select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_table_crd'; - if @engine_name != 'Aria' then - alter table mysql.spider_table_crd - engine=Aria transactional=1; - end if; - select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_table_position_for_recovery'; - if @engine_name != 'Aria' then - alter table mysql.spider_table_position_for_recovery - engine=Aria transactional=1; - end if; - select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_table_sts'; - if @engine_name != 'Aria' then - alter table mysql.spider_table_sts - engine=Aria transactional=1; - end if; - select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_tables'; - if @engine_name != 'Aria' then - alter table mysql.spider_tables - engine=Aria transactional=1; - end if; - select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_xa'; - if @engine_name != 'Aria' then - alter table mysql.spider_xa - engine=Aria transactional=1; - end if; - select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_xa_failed_log'; - if @engine_name != 'Aria' then - alter table mysql.spider_xa_failed_log - engine=Aria transactional=1; - end if; - select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES - where TABLE_SCHEMA = 'mysql' - AND TABLE_NAME = 'spider_xa_member'; - if @engine_name != 'Aria' then - alter table mysql.spider_xa_member - engine=Aria transactional=1; - end if; - end if; -end;// -delimiter ; -call mysql.spider_fix_system_tables; -drop procedure mysql.spider_fix_one_table; -drop procedure mysql.spider_fix_system_tables; - --- Install a plugin and UDFs drop procedure if exists mysql.spider_plugin_installer; delimiter // create procedure mysql.spider_plugin_installer() @@ -512,71 +36,6 @@ begin install plugin spider soname 'ha_spider.dll'; end if; end if; - set @have_spider_i_s_alloc_mem_plugin := 0; - select @have_spider_i_s_alloc_mem_plugin := 1 from INFORMATION_SCHEMA.plugins where PLUGIN_NAME = 'SPIDER_ALLOC_MEM'; - set @have_spider_alloc_mem_plugin := 0; - select @have_spider_alloc_mem_plugin := 1 from mysql.plugin where name = 'spider_alloc_mem'; - if @have_spider_i_s_alloc_mem_plugin = 0 then - if @have_spider_alloc_mem_plugin = 1 then - -- spider_alloc_mem plugin is present in mysql.plugin but not in - -- information_schema.plugins. Remove spider_alloc_mem plugin entry - -- in mysql.plugin first. - delete from mysql.plugin where name = 'spider_alloc_mem'; - end if; - -- Install spider_alloc_mem plugin - if @win_plugin = 0 then - install plugin spider_alloc_mem soname 'ha_spider.so'; - else - install plugin spider_alloc_mem soname 'ha_spider.dll'; - end if; - end if; - set @have_spider_direct_sql_udf := 0; - select @have_spider_direct_sql_udf := 1 from mysql.func where name = 'spider_direct_sql'; - if @have_spider_direct_sql_udf = 0 then - if @win_plugin = 0 then - create function spider_direct_sql returns int soname 'ha_spider.so'; - else - create function spider_direct_sql returns int soname 'ha_spider.dll'; - end if; - end if; - set @have_spider_bg_direct_sql_udf := 0; - select @have_spider_bg_direct_sql_udf := 1 from mysql.func where name = 'spider_bg_direct_sql'; - if @have_spider_bg_direct_sql_udf = 0 then - if @win_plugin = 0 then - create aggregate function spider_bg_direct_sql returns int soname 'ha_spider.so'; - else - create aggregate function spider_bg_direct_sql returns int soname 'ha_spider.dll'; - end if; - end if; - set @have_spider_ping_table_udf := 0; - select @have_spider_ping_table_udf := 1 from mysql.func where name = 'spider_ping_table'; - if @have_spider_ping_table_udf = 0 then - if @win_plugin = 0 then - create function spider_ping_table returns int soname 'ha_spider.so'; - else - create function spider_ping_table returns int soname 'ha_spider.dll'; - end if; - end if; - set @have_spider_copy_tables_udf := 0; - select @have_spider_copy_tables_udf := 1 from mysql.func where name = 'spider_copy_tables'; - if @have_spider_copy_tables_udf = 0 then - if @win_plugin = 0 then - create function spider_copy_tables returns int soname 'ha_spider.so'; - else - create function spider_copy_tables returns int soname 'ha_spider.dll'; - end if; - end if; - - set @have_spider_flush_table_mon_cache_udf := 0; - select @have_spider_flush_table_mon_cache_udf := 1 from mysql.func where name = 'spider_flush_table_mon_cache'; - if @have_spider_flush_table_mon_cache_udf = 0 then - if @win_plugin = 0 then - create function spider_flush_table_mon_cache returns int soname 'ha_spider.so'; - else - create function spider_flush_table_mon_cache returns int soname 'ha_spider.dll'; - end if; - end if; - end;// delimiter ; call mysql.spider_plugin_installer; |