问题：
升级过程中报如图所示错：sql执行错误，错误信息：deploy script error --isAll:false--DBRouteKey:basedata -- error。。。。。。。。
Caused by: java.sql.SQLException: PROCEDURE sys.P_ALTERPK does not exist
原因：
是由于数据库中，苍穹的所有分库都缺少了存储过程导致无法升级。
解决：
请在苍穹每一个分库中执行如下脚本添加下存储过程后重新执行升级。
-- p_AlterPK
DROP PROCEDURE IF EXISTS p_AlterPK;
delimiter //
CREATE PROCEDURE p_AlterPK ( PkName VARCHAR ( 50 ), TableName VARCHAR ( 50 ), FieldName VARCHAR ( 2000 ), IsClustered INT )
SQL SECURITY INVOKER
BEGIN
DECLARE
oldPkName VARCHAR ( 30 );
IF
EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = SCHEMA ( ) AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = TableName ) THEN
SET @sqlcounts = concat( 'ALTER TABLE ', TableName, ' DROP primary key' );
PREPARE stmt
FROM
@sqlcounts;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
IF
FieldName IS NOT NULL
AND FieldName <> 'NULL'
AND FieldName <> '' THEN
SET @sqlcounts = concat( 'ALTER TABLE ', TableName, ' ADD primary key(', FieldName, ')' );
PREPARE stmt
FROM
@sqlcounts;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END
//
delimiter ;
-- p_AlterColumn
DROP PROCEDURE IF EXISTS p_AlterColumn;
delimiter //
CREATE PROCEDURE p_AlterColumn (
TableName VARCHAR ( 50 ),
ColumnName VARCHAR ( 50 ),
ColumnType VARCHAR ( 50 ),
ColumnIsNull VARCHAR ( 10 ),
ModifyFlag VARCHAR ( 10 ),
DefaultValue VARCHAR ( 255 )
)
SQL SECURITY INVOKER
BEGIN
DECLARE
v_defaultvalue VARCHAR ( 500 );
DECLARE
v_defaultsql VARCHAR ( 500 );
DECLARE
v_ColumnType VARCHAR ( 500 );
IF
ColumnType = 'IMAGE' THEN
SET v_ColumnType = 'LONGBLOB';
ELSEIF ColumnType = 'NCLOB' THEN
SET v_ColumnType = 'LONGTEXT';
ELSEIF ColumnType = 'XMLTYPE' THEN
SET v_ColumnType = 'LONGTEXT';
ELSE
SET v_ColumnType = ColumnType;
END IF;
SET v_defaultsql = '';
IF
DefaultValue IS NOT NULL
AND LENGTH( DefaultValue ) > 0
AND DefaultValue <> 'NULL' THEN
IF
DefaultValue = 'GETDATE()' THEN
SET v_defaultvalue = ' current_timestamp ';
ELSE
IF
INSTR( DefaultValue, '{ts' ) = 1 THEN
SET v_defaultvalue = REPLACE ( REPLACE ( DefaultValue, '{ts', '' ), '}', '' );
ELSE
SET v_defaultvalue = DefaultValue;
END IF;
END IF;
SET v_defaultsql = concat( ' DEFAULT ', v_defaultvalue );
END IF;
IF
SUBSTR( ModifyFlag, 1, 1 ) = '1'
AND ColumnType = 'NULL' THEN
SET @ifSql = concat( 'SELECT count(1) into @existcol FROM information_schema.columns WHERE TABLE_SCHEMA = SCHEMA() AND table_name = ''', TableName, ''' AND column_name = ''', ColumnName, ''' ' );
PREPARE stmt
FROM
@ifSql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF
@existcol = 1 THEN
SET @sqlcounts = concat( ' ALTER TABLE ', TableName, ' drop column ', ColumnName );
PREPARE stmt
FROM
@sqlcounts;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
ELSE
IF
SUBSTR( ModifyFlag, 3, 1 ) = '1'
AND ColumnIsNull = 'NOT NULL'
AND ( v_defaultvalue IS NOT NULL AND LENGTH( v_defaultvalue ) > 0 ) THEN
SET @sqlcounts1 = concat( 'UPDATE ', TableName, ' SET ', ColumnName, ' = ', v_defaultvalue, ' WHERE ', ColumnName, ' IS NULL ' );
PREPARE stmt1
FROM
@sqlcounts1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
SET @sqlcounts = concat( 'ALTER TABLE ', TableName, ' modify ', ColumnName, ' ', v_ColumnType, ' ', ColumnIsNull, v_defaultsql, '' );
PREPARE stmt
FROM
@sqlcounts;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END
//
delimiter ;
-- p_ModifyObjectName
DROP PROCEDURE IF EXISTS p_ModifyObjectName;
delimiter //
CREATE PROCEDURE p_ModifyObjectName (
TableName VARCHAR ( 50 ),
ObjectName VARCHAR ( 50 ),
NewObjectName VARCHAR ( 50 ),
ObjectType VARCHAR ( 10 ),
FieldType VARCHAR ( 50 )
)
SQL SECURITY INVOKER
BEGIN
IF
ObjectType = 'OBJECT'
AND EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = SCHEMA ( ) AND table_name = ObjectName ) THEN
SET @sqlcounts = concat( 'ALTER TABLE ', ObjectName, ' RENAME TO ', NewObjectName );
PREPARE stmt
FROM
@sqlcounts;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
ELSE
IF
ObjectType = 'COLUMN'
AND EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = SCHEMA ( ) AND table_name = TableName AND COLUMN_NAME = ObjectName ) THEN
SET @sqlcounts = concat( 'ALTER TABLE ', TableName, ' CHANGE COLUMN ', ObjectName, ' ', NewObjectName, ' ', FieldType );
PREPARE stmt
FROM
@sqlcounts;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END IF;
END
//
delimiter ;
-- p_DropIdx
DROP PROCEDURE IF EXISTS p_DropIdx;
delimiter //
CREATE PROCEDURE p_DropIdx( IdxName VARCHAR ( 50 ), TableName VARCHAR ( 50 ))
SQL SECURITY INVOKER
BEGIN
DECLARE
oldIdxName VARCHAR ( 30 );
IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = SCHEMA ( ) AND INDEX_NAME = IdxName) THEN
SET @sqlcounts = concat( 'DROP INDEX ', IdxName, ' ON ', TableName);
PREPARE stmt
FROM
@sqlcounts;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END
//
delimiter ;
-- update mysql.proc
-- update mysql.proc set SECURITY_type='INVOKER' where db not in ('sys','information_schema','mysql','PERFORMANCE_SCHEMA');
mc_procedure_mysql库脚本.zip(2.09KB)
proc_pg库脚本.zip(4.19KB)
