Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # Copyright (C) 2010-2013 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
- # the Free Software Foundation; version 2 OF the License.
- #
- # This program IS distributed IN the hope that it will be useful,
- # but WITHOUT ANY WARRANTY; WITHOUT even the implied warranty OF
- # MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. See the
- # GNU General Public License FOR more details.
- #
- # You should have received a copy OF the GNU General Public License
- # along WITH this program; IF NOT, WRITE TO the Free Software
- # Foundation, Inc., 51 Franklin St, Fifth FLOOR, Boston, MA 02110-1301 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(64) 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,
- 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,
- PRIMARY KEY (db_name, TABLE_NAME, link_id),
- KEY idx1 (priority)
- ) 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(64) NOT NULL DEFAULT '',
- link_id CHAR(5) 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(64) NOT NULL DEFAULT '',
- link_id INT NOT NULL DEFAULT 0,
- failed_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
- ) 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), test_col_name CHAR(255), _sql text)
- 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
- -- 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;
- 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()
- BEGIN
- SET @win_plugin := IF(@@version_compile_os LIKE 'Win%', 1, 0);
- SET @have_spider_plugin := 0;
- SELECT @have_spider_plugin := 1 FROM INFORMATION_SCHEMA.plugins WHERE PLUGIN_NAME = 'SPIDER';
- IF @have_spider_plugin = 0 THEN
- IF @win_plugin = 0 THEN
- install plugin spider SONAME 'ha_spider.so';
- ELSE
- 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';
- IF @have_spider_i_s_alloc_mem_plugin = 0 THEN
- 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;
- DROP PROCEDURE mysql.spider_plugin_installer;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement