MySQL 通过 UDF 生成 key 环导致 SQLException

Question

这个问题我也在DBA Stackexchange中提出过。我将在这里逐字发布:

我正在尝试使用 MySQL 建议的 key 环插件来加密我的表。在我的/etc/my.cnf 文件中，我设置了早期插件标志以及 key 环文件的位置。它看起来像这样:

[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/usr/local/mysql/mysql-keyring/keyring

作为 root 用户，我还安装了 keyring_udf.so 并创建了一些 UDF，如下所示:

INSTALL PLUGIN keyring_udf SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_generate RETURNS INTEGER SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_fetch RETURNS STRING SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_length_fetch RETURNS INTEGER SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_type_fetch RETURNS STRING SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_store RETURNS INTEGER SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_remove RETURNS INTEGER SONAME 'keyring_udf.so';

从这里开始，我尝试像这样定义一个键:

SELECT keyring_key_generate('MyKey', 'AES', 32);

这会导致控制台显示以下消息:

[2018-06-15 15:11:38] Streaming result set com.mysql.jdbc.RowDataDynamic@15d7e44 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
[2018-06-15 15:11:38] java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@15d7e44 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:868)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:864)
    at com.mysql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:3211)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2443)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
    at com.mysql.jdbc.ConnectionImpl.setSessionMaxRows(ConnectionImpl.java:5432)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1365)
    at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:704)
    at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:656)
    at com.mysql.jdbc.StatementImpl.getWarnings(StatementImpl.java:2145)
    at com.intellij.database.remote.jdbc.impl.RemoteStatementImpl.getWarnings(RemoteStatementImpl.java:86)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:324)
    at sun.rmi.transport.Transport$1.run(Transport.java:200)
    at sun.rmi.transport.Transport$1.run(Transport.java:197)
    at java.security.AccessController.doPrivileged(Native Method)
    at sun.rmi.transport.Transport.serviceCall(Transport.java:196)
    at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:568)
    at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:826)
    at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$0(TCPTransport.java:683)
    at java.security.AccessController.doPrivileged(Native Method)
    at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:682)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
    at sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(StreamRemoteCall.java:276)
    at sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java:253)
    at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:162)
    at java.rmi.server.RemoteObjectInvocationHandler.invokeRemoteMethod(RemoteObjectInvocationHandler.java:227)
    at java.rmi.server.RemoteObjectInvocationHandler.invoke(RemoteObjectInvocationHandler.java:179)
    at com.sun.proxy.$Proxy121.getWarnings(Unknown Source)
    at sun.reflect.GeneratedMethodAccessor360.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.intellij.execution.rmi.RemoteUtil.invokeRemote(RemoteUtil.java:169)
    at com.intellij.execution.rmi.RemoteUtil.access$300(RemoteUtil.java:36)
    at com.intellij.execution.rmi.RemoteUtil$RemoteInvocationHandler.invoke(RemoteUtil.java:274)
    at com.sun.proxy.$Proxy122.getWarnings(Unknown Source)
    at com.intellij.database.console.JdbcEngine.b(JdbcEngine.java:444)
    at com.intellij.database.console.JdbcEngine.a(JdbcEngine.java:397)
    at com.intellij.database.console.JdbcEngine.b(JdbcEngine.java:224)
    at com.intellij.database.console.AbstractEngine.a(AbstractEngine.java:171)
    at com.intellij.database.console.AbstractEngine.a(AbstractEngine.java:148)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)

[2018-06-15 15:11:38] [HY000][3188] Function 'keyring_key_generate' failed because underlying keyring service returned an error. Please check if a keyring plugin is installed and that provided arguments are valid for the keyring you are using.

我还尝试过重置 mysql 服务器:systemctl restart mysqld。

环境是CentOS服务器。

经过几个小时的努力，我决定举白旗并向你们寻求帮助。感谢您的阅读/帮助!

编辑:

我尝试在 Windows 下执行相同的命令。 keyring_udf.dll 文件仍然存在同样的问题。我想知道这是否是插件中的错误？或者我可能缺少一个关键的 MySQL 设置。

Answer 1

我遇到了同样的错误，并且错误日志中还包含以下内容:

[ERROR] Plugin keyring_file reported: 'File '/usr/local/mysql/mysql-keyring/keyring' not found (Errcode: 13 - Permission denied)'

就我而言(我正在运行 ubuntu)，是 apparmor 限制了 mysql 的目录访问(无论目录或文件权限如何)，因此 mysql 无法读取 key 环目录或文件.

也许 CentOS 也在使用 apparmor 或类似的东西。

mysql 和 Ubuntu 的 Apparmor 配置文件已允许访问 /var/lib/mysql-keyring/。所以你应该可以直接使用这个目录。我的工作配置:

[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring

否则，您可以在 /etc/apparmor.d/usr.sbin.mysqld 中更改或添加 apparmor 中的目录。

希望这有帮助。