文档章节

MySQL存储过程

-启曙-
 -启曙-
发布于 2015/12/16 10:19
字数 1338
阅读 81
收藏 3

项目要求根据天进行分表,但是DBA要求建表要控制权限。表结构固定,因此要求不直接给建表权限,只能通过存储过程来建表。

思路:将建表的语句做成存储过程,然后在需要建表是,调用存储过程创建分表。

创建存储过程

-- procedure.sql
DELIMITER $$

DROP PROCEDURE IF EXISTS `new_dove_table`;$$
CREATE DEFINER=`root`@`%` PROCEDURE `new_dove_table`(IN tname varchar(200), OUT res int)
BEGIN
SET @sqlcmd = CONCAT('CREATE TABLE IF NOT EXISTS `', tname, '` (`id` bigint(20) NOT NULL, `content` mediumtext COLLATE utf8mb4_unicode_ci, `time` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `time_idx` (`time`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci');
PREPARE stmt FROM @sqlcmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET res=1;
END$$

DELIMITER ; 

tname是传递的表名称,IN代表是传入的参数,类型为字符串。 res是返回的应答,OUT代表返回的结构,类型为数字。

EXECUTE stmt; 执行建表SQL

登录mysql: mysql -h127.0.0.1 -P3306 -uroot -p

msyql> source procedure.sql

java调用存储过程

调用前,先创建test的账户,用户测试 mysql -uroot -S tmp/mysql.sokc

mysql> grant select,insert,update,delete,execute on test.* to 'test'@'%' identified by '123456';
mysql> FLUSH PRIVILEGES;

使用JDBC进行调用,可以使用C3P0库。

public class ProcedureTest {
    static Connection conn = null;  

    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //建立JDBC连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","test","123456");   
        } catch (Exception e) {  
            System.out.println("数据库异常!");  
            e.printStackTrace();  
        }
        
        callProcedure();
    } 
            
    public static void callProcedure() {
        String sqlx = "{CALL `new_dove_table`(?,?)}";  
        try {  
            //创建一个JDBC声明
            CallableStatement stmt = conn.prepareCall(sqlx);
            //设置传入的参数
            stmt.setString(1, "test_table"); 
            //注册执行结果
            stmt.registerOutParameter(2, Types.INTEGER);
            //执行存储过程
            stmt.executeUpdate(); 
            
            System.out.println(" test mysql procedure : " + stmt.getInt(2));
        } catch (SQLException e) {  
            e.printStackTrace();  
        } finally {  
            //预防性关闭连接(避免异常发生时在try语句块关闭连接没有执行)  
            try {  
                if (conn != null) conn.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
    }
}

可以执行程序后,查看表是否创建成功。

遇到的问题

在实际过程中遇到以下报错:

java.sql.SQLException: The user specified as a definer ('root'@'%') does not exist
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2111)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2407)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2325)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2310)
	at com.mysql.jdbc.CallableStatement.executeUpdate(CallableStatement.java:973)
	at jdbc.mysql.ProcedureTest.callProcedure(ProcedureTest.java:37)
	at jdbc.mysql.ProcedureTest.main(ProcedureTest.java:24)

分析可能是权限问题,查看下procedure信息

mysql> show procedure status\G;
*************************** 1. row ***************************
                  Db: test
                Name: new_dove_table
                Type: PROCEDURE
             Definer: root@%
            Modified: 2015-12-15 20:23:30
             Created: 2015-12-15 20:11:49
       Security_type: DEFINER
             Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
6 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> 

security_type为definer,也即只有创建者才有权限。修改权限

mysql> ALTER PROCEDURE `new_dove_table` SQL SECURITY INVOKER;
Query OK, 0 rows affected (0.00 sec)

mysql> 

执行完毕后,可以正常调用和创建表。

上线问题

上线后,遇到一个报错,导致无法创建表

[2015-12-15 17:24:57] ERROR ~ checkQueueExist exception: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "no
AccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.
java.sql.SQLException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to h
ave driver generate parameters that represent INOUT strings irregardless of actual parameter types.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
        at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1630)
        at com.mysql.jdbc.DatabaseMetaData.getProcedureOrFunctionColumns(DatabaseMetaData.java:4246)
        at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:4087)
        at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:845)
        at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:626)
        at com.mysql.jdbc.JDBC4CallableStatement.<init>(JDBC4CallableStatement.java:47)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
        at com.mysql.jdbc.CallableStatement.getInstance(CallableStatement.java:522)
        at com.mysql.jdbc.ConnectionImpl.parseCallableStatement(ConnectionImpl.java:4008)
        at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:4092)
        at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:4066)
        at helper.mysql.MysqlHelper.executeNewQueueTableProcedure(MysqlHelper.java:53)
        at storage.impl.mysql.MySQLDataStorage.createQueueTable(MySQLDataStorage.java:193)
        at storage.impl.db.DBDataStorage.checkQueueExist(DBDataStorage.java:49)
        at server.SubscriberManager.checkQueueExist(SubscriberManager.java:142)
        at command.impl.PullCommand.run(PullCommand.java:103)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)

通过分析异常还是权限问题,但是为何两次的权限问题不一样? 我们就在线上mysql url添加了 noAccessToProcedureBodies=true 参数,但是依然存在此报错。 虽然执行了 ALTER PROCEDURE new_dove_table SQL SECURITY INVOKER; 报错依然存在。

mysql> show procedure status\G;
*************************** 1. row ***************************
                  Db: test
                Name: new_dove_table
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2015-12-15 20:23:30
             Created: 2015-12-15 20:11:49
       Security_type: DEFINER
             Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
6 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> 

对比测试环境和线上环境,发现 definer有差异,一个是root@%,一个是root@localhost。 DBA在线上执行:GRANT SELECT ON mysql.proc TO 'test'@'localhost'; 后,问题得到解决

总结:

  • 如果definer是root@localhost,需要执行 GRANT SELECT ON mysql.proc TO 'test'@'%';
  • 如果definer是root@%,需要执行 ALTER PROCEDURE new_dove_table SQL SECURITY INVOKER;

原因: 存储过程的执行流程: 1、查找存储过程 存储过程就在mysql.proc中,查询时需要根据Security_type的类型进行权限检查。 如果Security_type值为INVOKER,则已经具有查询mysql.proc的权限,因此不需要GRANT SELECT ON mysql.proc TO 'user'@'%';赋权限如果如果Security_type值为DEFINER,则需要看调用者是否具有存储过程的权限,默认没有查询权限,因此需要GRANT SELECT ON mysql.proc TO 'user'@'%';赋权限

2、解析存储过程的参数 解析存储过程传递参数和存储过程定义是否符合。

3、执行存储过程 检查执行者的权限,执行者由Security_type决定。如果是INVOKER则,检查存储过程调用者的权限和存储过程内要求的权限是否匹配; 如果是DEFINER,检查存储过程的DEFINER权限和存储过程内要求的权限是否匹配。

扩展信息

存储过程的执行权限

mysql> grant execute on procedure test.new_dove_table to 'test'@'%';

mysql> revoke execute procedure test.new_dove_table from test;

JDBC连接执行MySQL存储过程报权限错误

© 著作权归作者所有

-启曙-
粉丝 9
博文 20
码字总数 14950
作品 0
广州
程序员
私信 提问

暂无文章

parseint和isNaN用法

本文转载于:专业的前端网站➭parseint和isNaN用法 <!doctype html><html><head><meta charset="utf-8"><title>无标题文档</title></head><body><script> var a='12'; alert......

前端老手
43分钟前
7
0
Kylin 精确去重在用户行为分析中的妙用

作者:史少锋,Apache Kylin committer & PMC,2019/10/11 在上次文章《如何在 1 秒内做到大数据精准去重》中,我们介绍了 Apache Kylin 为什么要支持大数据集上的精确去重,以及基于 Bitmap...

ApacheKylin
54分钟前
5
0
学习记录(二) es6基本语法(rest参数,模板化,axios模块,拦截器)

日常学习记录 模块化:把一个大文件分成多个小文件,按照一定规范进行拼接 es5写法: 导出:module.exports = 数据 导入:require("路径") /路径未添加后缀名时 //默认添加.js //把路径作为文件名...

Pole丶逐
58分钟前
4
0
以程序员的角度怎么购买一台「性价比高的电视」

前俩天有小伙伴在我的文章下留言,说能否把 【国内电视机都介绍一下】,今天我已在TV端开发多年的程序员的角度。谈谈已程序员的角度如何购买一台性价比高的电视。 国内大的电视机品牌介绍 长...

我们都很努力着
今天
4
0
PhotoShop 色调:理解直方图/RGB通道信息

一、直方图:图表的形式,展示图像像素分布的情况 1.平均值:表示平均亮度 2.标准偏差值:表示亮度值范围内的中间值 3.像素: 表示用于计算直方图的像素总数 4.色阶:显示指针下面的区域亮度...

东方墨天
今天
7
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部