需要一个工作策略来在 Jenkins 管道脚本中执行 SQL 脚本 [英] Need a working strategy to execute SQL scripts in Jenkins pipeline script

问题描述

作为 Jenkins 流水线脚本的一部分，我需要执行一些 SQL 脚本来初始化数据库 (mariadb).这项工作在我拥有的 Jenkins 奴隶上运行.我维护 Jenkins 主实例，但不维护它运行的主机.

As part of a Jenkins pipeline script, I need to execute some SQL scripts to initialize a database (mariadb). This job runs on a Jenkins slave that I own. I maintain the Jenkins master instance, but not the host it runs on.

我的第一次尝试只是在脚本中添加内联代码来调用Sql.newInstance(...)"和sql.execute(...)".这会失败，因为 mariadb jar 不在类路径中.

My first try was simply having inline code in the script to call "Sql.newInstance(...)" and "sql.execute(...)". This fails because the mariadb jar is not in the classpath.

然后我尝试为从站设置 CLASSPATH 环境变量，将 mariadb 客户端 jar 添加到其中，然后断开并重新连接从站.这似乎没有效果.

I then tried setting the CLASSPATH env var for the slave, to add the mariadb client jar to it, then disconnecting and reconnecting the slave. This appeared to have no effect.

然后我尝试了@GrapeConfig/@Grab 方法.我使用@GrapeConfig 设置systemClassLoader=true".这会因无法找到合适的类加载器"而失败.所以，然后我尝试删除@GrapeConfig，但失败并显示RuntimeException:无法创建类 javax.xml.parsers.SAXParserFactory 的提供程序".

I then tried the @GrapeConfig/@Grab approach. I used @GrapeConfig to set "systemClassLoader=true". This fails with "Unable to find suitable classloader". So, I then tried removing the @GrapeConfig, and this fails with "RuntimeException: Provider for class javax.xml.parsers.SAXParserFactory cannot be created".

接下来，我想我将尝试从sh"命令直接运行mysql"，并通过管道输入我的 sql 脚本的内容.这似乎是合理的，但我不确定这是否可行.

Next, I guess I'm going to try to directly run "mysql" from a "sh" command and pipe in the contents of my sql scripts. This seems plausible, but I'm not sure if this will work.

我看到过关于这样做的各种尝试的笔记，但我从未听说有人成功地做到了这一点.

I've seen notes talking about various attempts to do this, but I've never heard of someone successfully doing this.

推荐答案

虽然用java 方式"实现这个看起来合乎逻辑，但我发现简单地运行 'sh("mysql ... '.它避免了所有棘手的类路径问题.它确实要求将数据库客户端安装在从机上，并且不允许数据库独立(并不是那么重要).实际上，我实际上所做的是在容器中运行 mariadb，因此生成的命令行更像docker exec -i container mysql ... < file.sql"(注意-i"，而不是通常的-it"，因为如果在文件中进行管道传输，这将不起作用).

Although it seems logical to implement this in a "java way", I found that it was more straightforward to simply run 'sh("mysql ... < file.sql")'. It avoids all the gnarly classpath problems. It does require that the database client be installed on the slave box, and it doesn't allow for database independence (not really that important). In reality, what I actually did was run mariadb in a container, so the resulting command line was more like "docker exec -i container mysql ... < file.sql" (notice the "-i", not the usual "-it", as that won't work if piping in a file).

更新:

从 macg33zr 开始，我发现将任务添加到我现有的 Gradle 构建脚本以操作数据库非常容易.我还没有完全实现我需要的东西，我只是验证了它可以使用一个简单的选择"语句.

From the elbow from macg33zr, I found it was pretty easy to add tasks to my existing Gradle build script to manipulate the database. I haven't fully implemented what I need to with it, I just verified that it could work with a simple "select" statement.

我已经在单独的 Gradle 配置中拥有 JDBC 驱动程序 jar，因为我需要将这些工件存储在容器的 lib 目录中.

I already had the JDBC driver jars in a separate Gradle configuration, as I needed to store those artifacts in the container's lib directory.

以下是所需部分的一个很好的总结:https://discuss.gradle.org/t/jdbc-driver-class-cannot-be-loaded-with-gradle-2-0-but-work-with-1-12/2277 .关键的微妙之处是将 JDBC 驱动程序 jar 添加到类加载器中.

The following is a good summary of the required pieces: https://discuss.gradle.org/t/jdbc-driver-class-cannot-be-loaded-with-gradle-2-0-but-worked-with-1-12/2277 . The key subtle thing is adding the JDBC driver jars to the classloader.

这篇关于需要一个工作策略来在 Jenkins 管道脚本中执行 SQL 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！