1764

Get puppet build to fail when the contained SQL script fails execution

Question:

I am attempting to run a vagrant build which installs Oracle XE in an Ubuntu Virtualbox VM and then runs an SQL script to initialize the Oracle Schema.

The vagrant build is here : <a href="https://github.com/ajorpheus/vagrant-ubuntu-oracle-xe" rel="nofollow">https://github.com/ajorpheus/vagrant-ubuntu-oracle-xe</a> The <a href="https://github.com/ajorpheus/vagrant-ubuntu-oracle-xe/blob/master/modules/oracle/files/setup.sql" rel="nofollow">setup.sql</a> is run as a part of the oracle module's <a href="https://github.com/ajorpheus/vagrant-ubuntu-oracle-xe/blob/master/modules/oracle/manifests/init.pp" rel="nofollow">init.pp</a> (look near the end of that file or search for 'oracle-script').

I was getting an error while running this vagrant build and was just being told that the 'oracle-script' bit was not successful without any more details. To figure out whether it is a problem with the 'oracle-script' bit, I extracted it out into a <a href="https://github.com/ajorpheus/vagrant-ubuntu-oracle-xe/blob/master/test_sql.pp" rel="nofollow">test-sql.pp</a> :

file { '/tmp/setup.sql': ensure => file, source => '/tmp/setup.sql'; } exec { 'oracle-script': path => ['/bin', '/u01/app/oracle/product/11.2.0/xe/bin'], command => 'sqlplus system/manager@xe < /tmp/setup.sql', require => [ File['/tmp/setup.sql']], timeout => '0', }

Now I have another issue (which is what this question is about). The setup.sql creates a table space and on subsequent runs, the tablespace creation should fail and so should the puppet script.

I am running the following command

puppet apply --verbose --debug test-sql.pp

And the output always indicates a successful outcome:

debug: Exec[oracle-script](provider=posix): Executing 'sqlplus system/manager@xe < /tmp/setup.sql' debug: Executing 'sqlplus system/manager@xe < /tmp/setup.sql' notice: /Stage[main]//Exec[oracle-script]/returns: executed successfully

However, if I run the same SQL script manually:<br /><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/gk1Hn.png" data-original="https://i.stack.imgur.com/gk1Hn.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

<hr />

<strong>Questions</strong>

<ol><li>

Why doesn't the Puppet build fail even though the contained SQL script does? Should I be looking at the exit codes of SQL Plus?

</li> <li>

How can I get puppet to emit more verbose debug information? I found <a href="http://www.devco.net/archives/2009/08/19/tips_and_tricks_for_puppet_debugging.php" rel="nofollow">this</a>, and have tried the suggestions there.

</li> <li>

After RTFM ... I see that Puppet's <a href="http://docs.puppetlabs.com/references/latest/type.html#exec" rel="nofollow">exec</a> is required to be idempotent. However, the question remains, if the tablespace already exists, why doesn't the puppet build fail?

</li> </ol>

Thanks!

<strong>Update</strong>

After adding logoutput, as per TheQ's suggestion below, I see the following output:

debug: Executing 'sqlplus system/manager@xe < /tmp/setup.sql' notice: /Stage[main]//Exec[oracle-script]/returns: notice: /Stage[main]//Exec[oracle-script]/returns: SQL*Plus: Release 11.2.0.2.0 Production on Sat Apr 5 16:02:37 2014 notice: /Stage[main]//Exec[oracle-script]/returns: notice: /Stage[main]//Exec[oracle-script]/returns: Copyright (c) 1982, 2011, Oracle. All rights reserved. notice: /Stage[main]//Exec[oracle-script]/returns: notice: /Stage[main]//Exec[oracle-script]/returns: notice: /Stage[main]//Exec[oracle-script]/returns: Connected to: notice: /Stage[main]//Exec[oracle-script]/returns: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production notice: /Stage[main]//Exec[oracle-script]/returns: notice: /Stage[main]//Exec[oracle-script]/returns: create tablespace some_INDEX_50M datafile '~\u01\some_INDEX_50M.dbf' size 50m notice: /Stage[main]//Exec[oracle-script]/returns: * notice: /Stage[main]//Exec[oracle-script]/returns: ERROR at line 1: notice: /Stage[main]//Exec[oracle-script]/returns: ORA-01543: tablespace 'SOME_INDEX_50M' already exists notice: /Stage[main]//Exec[oracle-script]/returns: notice: /Stage[main]//Exec[oracle-script]/returns: notice: /Stage[main]//Exec[oracle-script]/returns: Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production notice: /Stage[main]//Exec[oracle-script]/returns: executed successfully

Answer1:

I believe puppet detects the success of the script based on the return code of the called program. By default, sqlplus returns 0 when you close it, regardless of what may have been ran during the session.

[oracle@bbdb2 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 17 08:47:08 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select asdjkhasd from sadbjaksd; select asdjkhasd from sadbjaksd * ERROR at line 1: ORA-00942: table or view does not exist SQL> quit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@bbdb2 ~]$ echo $? 0

If you want sqlplus to exit with an error status, you can use the whenever command, e.g.

[oracle@bbdb2 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 17 08:48:17 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> whenever sqlerror exit failure; SQL> select bogus from nowhere; select bogus from nowhere * ERROR at line 1: ORA-00942: table or view does not exist Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@bbdb2 ~]$ echo $? 1

Notice the different return code in the latter case. This should be enough to let puppet know the command failed.

Answer2:

This does not answer all part of the question, but if you use <a href="http://docs.puppetlabs.com/references/latest/type.html#exec-attribute-logoutput" rel="nofollow">logoutput</a>, you should see a lot more output from the exec command. By default the output appears only if exec detects a failure, which does not seem to be happening in your case.

Therefore, regarding #2 try adding:

logoutput => 'true',

Recommend

  • Postgres pg_dump cache lookup failed for index
  • ORA-00972 Identifier is too long: while creating tablespace
  • WAMP Server stays offline. MySQL service not starting “could not start the wampmysqld64 service - er
  • drop tablespace if do not exist
  • Granting permissions to Azure Active Directory Web Application automatically
  • Getting unread count in Sent Folder using Google Apps Script - GMail
  • How do I Dispose a HttpResponseMessage in my Web Api Method?
  • What Makes These Two Array Adds Different?
  • Function calls are not supported. Consider replacing the function or lambda with a reference to an e
  • Bigquery event streaming and table creation
  • Django model inheritance, filtering models
  • Cannot upload to OneDrive using the new SDK
  • rspec simple example getting error on request variable in integration test
  • Content-Length header not returned from Pylons response
  • How to attach a node.js readable stream to a Sendgrid email?
  • Exception “firebase.functions() takes … no argument …” when specifying a region for a Cloud Function
  • Change Inet root folder for iis 7
  • Ajax Loaded meta Tags
  • Xamarin Forms - UWP Fonts
  • Display issues when we change from one jquery mobile page to another in firefox
  • Splitting given String into two variables - php
  • Ajax jQuery multiple calls at the same time - long wait for answer and not able to cancel
  • Different response to non-authenticated users and AJAX calls
  • Spray.io: When (not) to use non-blocking route handling?
  • Arrow is showed instead of the material design version hamburger icon. Why doesn't syncState in
  • Hazelcast - OperationTimeoutException
  • How to make Safari send if-modified-since header?
  • 0x202A in filename: Why?
  • Run Powershell script from inside other Powershell script with dynamic redirection to file
  • Arrays break string types in Julia
  • Data Validation Drop Down Box Arrow Disappearing
  • File upload with ng-file-upload throwing error
  • Load html files in TinyMce
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • coudnt use logback because of log4j
  • How does Linux kernel interrupt the application?
  • costura.fody for a dll that references another dll
  • Observable and ngFor in Angular 2
  • UserPrincipal.Current returns apppool on IIS
  • java string with new operator and a literal