Oakies Blog Aggregator

In the previous note I posted about Join Cardinality I described a method for calculating the figure that the optimizer would give for the special case where you had a query that:

• joined two tables
• used a single-column to join on equality
• had no nulls in the join columns
• had a perfect frequency histogram on the columns at the two ends of the join
• had no filter predicates associated with either table

The method simply said: “Match up rows from the two frequency histograms, multiply the corresponding frequencies” and I supplied a simple SQL statement that would read and report the two sets of histogram data, doing the arithmetic and reporting the final cardinality for you. In an update I also added an adjustment needed in 11g (or, you might say, removed in 12c) where gaps in the histograms were replaced by “ghost rows” with a frequency that was half the lowest frequency in the histogram.

This is a nice place to start as the idea is very simple, and it’s likely that extensions of the basic idea will be used in all the other cases we have to consider. There are 25 possibilities that could need separate testing – though only 16 of them ought to be relevant from 12c onwards. Oracle allows for four kinds of histograms – in order of how precisely they describe the data they are:

• Frequency – with a perfect description of the data
• Top-N (a.k.a. Top-Frequency) – which describes all but a tiny fraction (ca. one bucket’s worth) of data perfectly
• Hybrid – which can (but doesn’t usually, by default) describe up to 2,048 popular values perfectly and gives an approximate distribution for the rest
• Height-balanced – which can (but doesn’t usually, by default) describe at most 1,024 popular values with some scope for misinformation.

Finally, of course, we have the general case of no histogram, using only 4 numbers (low value, high value, number of rows, number of distinct values) to give a rough picture of the data – and the need for histograms appears, of course, when the data doesn’t look anything like an even distribution of values between the low and high with close to “number of rows”/”number of distinct values” for each value.

So there are 5 possible statistical descriptions for the data in a column – which means there are 5 * 5 = 25 possible options to consider when we join two columns, or 4 * 4 = 16 if we label height-balanced histograms as obsolete and ignore them (which would be a pity because Chinar has done some very nice work explaining them).

Of course, once we’ve worked out a single-column equijoin between two tables there are plenty more options to consider:  multi-column joins, joins involving range-based predicates, joins involving more than 2 tables, and queries which (as so often happens) have predicates which aren’t involved in the joins.

For the moment I’m going to stick to the simplest case – two tables, one column, equality – and comment on the effects of filter predicates. It seems to be very straightforward as I’ll demonstrate with a new model

rem
rem     Script:         freq_hist_join_03.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Oct 2018
rem

execute dbms_random.seed(0)

create table t1(
        id      number(8,0),
        n0040   number(4,0),
        n0090   number(4,0),
        n0190   number(4,0),
        n0990   number(4,0),
        n1      number(4,0)
)
;

create table t2(
        id      number(8,0),
        n0050   number(4,0),
        n0110   number(4,0),
        n0230   number(4,0),
        n1150   number(4,0),
        n1      number(4,0)
)
;

insert into t1
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        rownum                                  id,
        mod(rownum,   40) + 1                   n0040,
        mod(rownum,   90) + 1                   n0090,
        mod(rownum,  190) + 1                   n0190,
        mod(rownum,  990) + 1                   n0990,
        trunc(30 * abs(dbms_random.normal))     n1
from
        generator       v1,
        generator       v2
where
        rownum <= 1e5 -- > comment to avoid WordPress format issue
;

insert into t2
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        rownum                                  id,
        mod(rownum,   50) + 1                   n0050,
        mod(rownum,  110) + 1                   n0110,
        mod(rownum,  230) + 1                   n0230,
        mod(rownum, 1150) + 1                   n1150,
        trunc(30 * abs(dbms_random.normal))     n1
from
        generator       v1,
        generator       v2
where
        rownum <= 1e5 -- > comment to avoid WordPress format issue
;

begin
        dbms_stats.gather_table_stats(
                ownname => null,
                tabname     => 'T1',
                method_opt  => 'for all columns size 1 for columns n1 size 254'
        );
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'T2',
                method_opt  => 'for all columns size 1 for columns n1 size 254'
        );
end;
/

You’ll notice that in this script I’ve created empty tables and then populated them. This is because of an anomaly that appeared in 18.3 when I used “create as select”, and should allow the results from 18.3 be an exact match for 12c. You don’t need to pay much attention to the Nxxx columns, they were there so I could experiment with a few variations in the selectivity of filter predicates.

Given the purpose of the demonstration I’ve gathered histograms on the column I’m going to use to join the tables (called n1 in this case), and here are the summary results:

TABLE_NAME           COLUMN_NAME          HISTOGRAM       NUM_DISTINCT NUM_BUCKETS
-------------------- -------------------- --------------- ------------ -----------
T1                   N1                   FREQUENCY                119         119
T2                   N1                   FREQUENCY                124         124

     VALUE  FREQUENCY  FREQUENCY      PRODUCT
---------- ---------- ---------- ------------
         0       2488       2619    6,516,072
         1       2693       2599    6,999,107
         2       2635       2685    7,074,975
         3       2636       2654    6,995,944
...
       113          1          3            3
       115          1          2            2
       116          4          3           12
       117          1          1            1
       120          1          2            2
                                 ------------
sum                               188,114,543

We’ve got frequencyy histograms, and we can see that they don’t have a perfect overlap. I haven’t printed every single line from the cardinality query, just enough to show you the extreme skew, a few gaps, and the total. So here are three queries with execution plans:

set serveroutput off

alter session set statistics_level = all;
alter session set events '10053 trace name context forever';

select
        count(*)
from
        t1, t2
where
        t1.n1 = t2.n1
;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

select
        count(*)
from
        t1, t2
where
        t1.n1 = t2.n1
and     t1.n0990 = 20
;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));


select
        count(*)
from
        t1, t2
where
        t1.n1 = t2.n1
and     t1.n0990 = 20
and     t2.n1150 = 25
;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

I’ve queried the pure join – the count was exactly the 188,114,543 predicted by the cardinality query, of course – then I’ve applied a filter to one table, then to both tables. The first filter n0990 = 20 will (given the mod(,990)) definition identify one row in 990 from the original 100,000 in t1; the second filter n1150 = 25 will identify one row in 1150 from t2. That’s filtering down to 101 rows and 87 rows respectively from the two tables. So what do we see in the plans:

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:23.47 |     748 |       |       |          |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:23.47 |     748 |       |       |          |
|*  2 |   HASH JOIN         |      |      1 |    188M|    188M|00:00:23.36 |     748 |  6556K|  3619K| 8839K (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |    100K|    100K|00:00:00.01 |     374 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |     374 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."N1"="T2"."N1")



-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.02 |     748 |       |       |          |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:00.02 |     748 |       |       |          |
|*  2 |   HASH JOIN         |      |      1 |    190K|    200K|00:00:00.02 |     748 |  2715K|  2715K| 1647K (0)|
|*  3 |    TABLE ACCESS FULL| T1   |      1 |    101 |    101 |00:00:00.01 |     374 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |     374 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."N1"="T2"."N1")
   3 - filter("T1"."N0990"=20)



-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.01 |     748 |       |       |          |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:00.01 |     748 |       |       |          |
|*  2 |   HASH JOIN         |      |      1 |    165 |    165 |00:00:00.01 |     748 |  2715K|  2715K| 1678K (0)|
|*  3 |    TABLE ACCESS FULL| T2   |      1 |     87 |     87 |00:00:00.01 |     374 |       |       |          |
|*  4 |    TABLE ACCESS FULL| T1   |      1 |    101 |    101 |00:00:00.01 |     374 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."N1"="T2"."N1")
   3 - filter("T2"."N1150"=25)
   4 - filter("T1"."N0990"=20)

The first execution plan shows an estimate of 188M rows – but we’ll have to check the trace file to confirm whether that’s only an approximate match to our calculation, or whether it’s an exact match. So here’s the relevant pair of lines:

Join Card:  188114543.000000 = outer (100000.000000) * inner (100000.000000) * sel (0.018811)
Join Card - Rounded: 188114543 Computed: 188114543.000000

Yes, the cardinality calculation and the execution plan estimates match perfectly. But there are a couple of interesting things to note. First, Oracle seems to be deriving the cardinality by multiplying the individual cardinalities of the two tables with a figure it calls “sel” – the thing that Chinar Aliyev has labelled J_sel the “Join Selectivity”. Secondly, Oracle can’t do arithmetic (or, removing tongue from cheek) the value it’s reported for the join selectivity is reported at only 6 decimal places, but stored to far more. What is the Join Selectivity, though ? It’s the figure we derive from the cardinality SQL divided by the cardinality of the cartesian join of the two tables – i.e. 188,114,543 / (100,000 * 100,000).

With the clue from the first trace file, can we work out why the second and third plans show 190K and 165 rows respectively. How about this – multiply the filtered cardinalities of the two separate tables, then multiply the result by the join selectivity:

• 1a)   n0990 = 20: gives us 1 row in every 990.    100,000 / 990 = 101.010101…    (echoing the rounded execution plan estimate).
• 1b)   100,000 * (100,000/990) * 0.0188114543 = 190,014.69898989…    (which is in the ballpark of the plan and needs confirmation from the trace file).

• 2a)   n1150 = 25: gives us 1 row in every 1,150.    100,000 / 1,150 = 86.9565217…    (echoing the rounded execution plan estimate)
• 2b)   (100,000/990) * (100,000/1,150) * 0.0188114543 = 165.2301651..    (echoing the rounded execution plan estimate).

Cross-checking against extracts from the 10053 trace files:

Join Card:  190014.689899 = outer (101.010101) * inner (100000.000000) * sel (0.018811)
Join Card - Rounded: 190015 Computed: 190014.689899

Join Card:  165.230165 = outer (86.956522) * inner (101.010101) * sel (0.018811)
Join Card - Rounded: 165 Computed: 165.230165

Conclusion.

Remembering that we’re still looking at very simple examples with perfect frequency histograms: it looks as if we can work out a “Join Selectivity” (J_sel) – the selectivity of a “pure” unfiltered join of the two tables – by querying the histogram data then use the resulting value to calculate cardinalities for simple two-table equi-joins by multiplying together the individual (filtered) table cardinality estimates and scaling by the Join Selectivity.

Acknowledgements

Most of this work is based on a document written by Chinar Aliyev in 2016 and presented at the Hotsos Symposium the same year. I am most grateful to him for responding to a recent post of mine and getting me interested in spending some time to get re-acquainted with the topic. His original document is a 35 page pdf file, so there’s plenty more material to work through, experiment with, and write about.

Now everyone knows how I like to automate everything and for those that have known me since I started sharing content, I pretty much cried a thousand tears when the personalized news source, Prism disappeared.

I’ve been working with RSS feeds aggregators to send me content each day to read, but I get frustrated with having to go find them sent to my spam folder or not being able to get to the links, so I wanted to try something new.

Microsoft Flow

I automated a lot of tasks at my previous jobs with IFTTT, (If this, then that) recipes and after arriving at Microsoft, was thrilled when I saw my first demo of Microsoft Flow.  It’s a growing product that has connectors to a ton of applications, features and has a custom API to build out automated workflows for whatever your heart desires.  What I’m about to do isn’t going to require anything THAT custom, so no worries.

Change Flow

With my time in the Analytics and AI space, my content source is going to change, so I have a few new blogs that I want to keep up to date with.  For our example, we’ll use one of my recent favorites, Chris Webb’s BI blog.  As we eat our own dogfood here at Microsoft, I’m would like to have emails notify me via email of when he has a new blog post and provide it to “my personal team of one” in Microsoft Teams, (I found Teams excellent as a self-project management program, like Trello, Outlook Calendar and Slack all in one.) You’ll need a Microsoft Team account to do one of the steps I’ve added, but it’s not necessary if you want to just email the notification of a new post to yourself.

After logging into my Microsoft Flow account, I choose a new template and type in the Search bar, “RSS”.  I double click on the RSS Feed Notification and it displays the following page:

https://i0.wp.com/dbakevlar.com/wp-content/uploads/2018/10/flow7.jpg?res... 300w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2018/10/flow7.jpg?res... 768w" sizes="(max-width: 387px) 100vw, 387px" data-recalc-dims="1" />

Feed the URL

If you don’t know how to get the feed URL from a blog you like, Click on the browser settings and choose either “Developer Tools” or “Source Code”, (or it will be something similar, depending on the browser..)  Do a search in the code for “RSS” and you’ll quickly come across the URL for the RSS Feed.  By default, it’s commonly the URL/feed/, but it’s always better to check.

https://i1.wp.com/dbakevlar.com/wp-content/uploads/2018/10/flow2.jpg?res... 300w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2018/10/flow2.jpg?res... 768w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2018/10/flow2.jpg?w=1... 1400w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2018/10/flow2.jpg?w=2... 2100w" sizes="(max-width: 501px) 100vw, 501px" data-recalc-dims="1" />

The rest of the information is just my Team name, which shows in the drop down list.  I could send this to any of the Teams I’m part of, but this is for me, so my own little team of one will do.  I dynamically populate the blog summary and the blog post URL to make it easy to go from Teams to the blog post.

https://i2.wp.com/dbakevlar.com/wp-content/uploads/2018/10/flow3.jpg?res... 300w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2018/10/flow3.jpg?res... 768w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2018/10/flow3.jpg?w=1... 1400w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2018/10/flow3.jpg?w=2... 2100w" sizes="(max-width: 504px) 100vw, 504px" data-recalc-dims="1" />

Add Email

After filling out the fields, I can save, but instead I click Next Step to add the email to Outlook step.  If you don’t have Outlook, there are connectors for Gmail and event Mailchimp!  As I stated, I’m eating me own dogfood, so I have Outlook and just need to fill in the pertinent information as requested.  I dynamically populate some of the fields for the Blog Summary and URL link so I can easily connect to the site when the email arrives.

https://i0.wp.com/dbakevlar.com/wp-content/uploads/2018/10/flow4.jpg?res... 300w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2018/10/flow4.jpg?res... 768w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2018/10/flow4.jpg?w=1... 1400w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2018/10/flow4.jpg?w=2... 2100w" sizes="(max-width: 503px) 100vw, 503px" data-recalc-dims="1" />

Test and Savor

Now I click on the Test up in the upper right hand corner.  It tests all the steps and the connections, just to verify all the steps have been filled out correctly and then once that’s completed, you can then exit, the workflow creation is complete.

https://i1.wp.com/dbakevlar.com/wp-content/uploads/2018/10/flow5.jpg?res... 300w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2018/10/flow5.jpg?res... 768w" sizes="(max-width: 203px) 100vw, 203px" data-recalc-dims="1" />

Now, when there’s a new blog post from Chris, I’ll know about it, both via email and Teams!  I may have lost Prism, but that doesn’t mean I can’t get the information that’s important to me without seeing all the rubbish that’s put out on most sites!  Yes, I’m adding a ton more workflows to automate more of my day, including adding contacts from coworker emails, automating refreshes on datasets, etc.  Don’t let the preview status scare you on the product-  this is how the product comes up to speed fast is with people working with it and using it to its full potential.

Tags:  Automation, Blogs, Microsoft Flow, News

Del.icio.us

Facebook

TweetThis

Digg

StumbleUpon

Exasol doesn’t need much administration but getting distribution right matters

Exasol uses a clustered shared-nothing architecture with many sophisticated internal mechanisms to deliver outstanding performance without requiring much administration. Getting the distribution of rows between cluster nodes right is one of the few critical tasks left, though. To explain this, let’s say we have two tables t1 and t2:

https://uhesse.files.wordpress.com/2018/10/tablest1t2.png?w=150&h=137 150w, https://uhesse.files.wordpress.com/2018/10/tablest1t2.png 497w" sizes="(max-width: 300px) 100vw, 300px" />

The two tables are joined on the column JoinCol, while WHERE conditions for filtering are done with the column WhereCol. Other columns are not shown to keep the sketches small and simple. Now say these two tables are stored on a three-node cluster. Again, for simplicity only active nodes are on the sketch – no reserve nodes or license nodes. We also ignore the fact that small tables will be replicated across all active nodes.

Distribution will be random if no distribution key is specified

Without specifying a distribution key, the rows of the tables are distributed randomly across the nodes like this:

https://uhesse.files.wordpress.com/2018/10/randomdistribution.png?w=150&... 150w, https://uhesse.files.wordpress.com/2018/10/randomdistribution.png?w=300&... 300w, https://uhesse.files.wordpress.com/2018/10/randomdistribution.png?w=768&... 768w, https://uhesse.files.wordpress.com/2018/10/randomdistribution.png 888w" sizes="(max-width: 620px) 100vw, 620px" />

Absence of proper distribution keys: global joins

The two tables are then joined:

SELECT  FROM t1 JOIN t2 ON t1.JoinCol = t2.JoinCol;

Internally, this is processed as a global join which means network communication between the nodes on behalf of the join is required. This is the case because some rows do not find local join partners on the same node:

https://uhesse.files.wordpress.com/2018/10/globaljoin.png?w=150&h=58 150w, https://uhesse.files.wordpress.com/2018/10/globaljoin.png?w=300&h=117 300w, https://uhesse.files.wordpress.com/2018/10/globaljoin.png?w=768&h=299 768w, https://uhesse.files.wordpress.com/2018/10/globaljoin.png 889w" sizes="(max-width: 620px) 100vw, 620px" />

Distribution on join columns: local joins

If the two tables were distributed on their join columns with statements like these

ALTER TABLE t1 DISTRIBUTE BY JoinCol;

ALTER TABLE t2 DISTRIBUTE BY JoinCol;

then the same query can be processed internally as a local join:

https://uhesse.files.wordpress.com/2018/10/localjoin.png?w=150&h=59 150w, https://uhesse.files.wordpress.com/2018/10/localjoin.png?w=300&h=119 300w, https://uhesse.files.wordpress.com/2018/10/localjoin.png?w=768&h=304 768w, https://uhesse.files.wordpress.com/2018/10/localjoin.png 891w" sizes="(max-width: 620px) 100vw, 620px" />

Here every row finds a local join partner on the same node so no network communication between the nodes on behalf of the join is required. The performance with this local join is much better than with the global join although it’s the same statement as before.

Why you shouldn’t distribute on WHERE-columns

While it’s generally a good idea to distribute on JOIN-columns, it’s by contrast a bad idea to distribute on columns that are used for filtering with WHERE conditions. If both tables would have been distributed on the WhereCol columns, it would look like this:

https://uhesse.files.wordpress.com/2018/10/wherecols.png?w=150&h=59 150w, https://uhesse.files.wordpress.com/2018/10/wherecols.png?w=300&h=119 300w, https://uhesse.files.wordpress.com/2018/10/wherecols.png?w=768&h=304 768w, https://uhesse.files.wordpress.com/2018/10/wherecols.png 883w" sizes="(max-width: 620px) 100vw, 620px" />

This distribution is actually worse than the initial random distribution! Not only does this cause global joins between the two tables as already explained, statements like e.g.

 WHERE t2.WhereCol='A';

will utilize only one node (the first with this WHERE condition) and that effectively disables one of Exasol’s best strengths, the Massive Parallel Processing (MPP) functionality. This distribution leads to poor performance because all other nodes in the cluster have to stand by being idle while one node has to do all the work alone.

Examine existing distribution with iproc()

The function iproc() helps investigating the existing distribution of rows across cluster nodes. This statement shows the distribution of the table t1:

SELECT iproc(),COUNT(*) FROM t1 GROUP BY 1 ORDER BY 1;

Evaluate the effect of distribution keys with value2proc()

The function value2proc() can be used to display the effect that a (new) distribution key would have:

SELECT home_node,COUNT(*) FROM (SELECT value2proc(JoinCol) AS home_node FROM t1) GROUP BY 1 ORDER BY 1;

Conclusion

Distribution on JOIN-columns leads to local joins which perform better than global joins: Do that!

Distribution on WHERE-columns leads to global joins and disables the MPP functionality, both causing poor performance: Don’t do that!

When running ansible scripts, occasionally you wonder why a given task has failed. I found out more than once that it’s commonly a problem with the script, not the engine ;) Finding out exactly where in the script I made the mistake can be more of a challenge.

With the default ansible settings, output can be a bit hard to read. Consider this example: I do quite a bit of patching in my lab, and this almost always requires an upgrade of OPatch (d’oh!). So instead of connecting to each of my hosts and performing the same unzip command over and over again, I thought of using something else. Why not use ansible for this task? It won’t get tired copying/unzipping OPatch to all the destinations I indicate in my configuration. And it won’t introduce a mistake when dealing with the fifth ORACLE_HOME on the third server…

Before replacing $ORACLE_HOME/OPatch with the new version, I want to take a backup of the current OPatch just in case. I don’t want to keep more than 1 backup around in this particular lab environment, so I decided to check for an existing backup first, before creating a new one. If one exists, I remove it. Or at least, that’s the plan.

So I was happily coding away and in my usual trial-and-error approach was ready to test the script I wrote for the first time. Here’s the result (as shown in my 80×24 terminal):

[martin@controller environment]$ ansible-playbook -i inventory.yml broken.yml

PLAY [blogpost] ****************************************************************

TASK [Gathering Facts] *********************************************************
ok: [server1]

TASK [check if there is an old backup] *****************************************
ok: [server1]

TASK [remove old OPatch backup] ************************************************
fatal: [server1]: FAILED! => {"msg": "The conditional check 'backup_present.exis
ts' failed. The error was: error while evaluating conditional (backup_present.ex
ists): 'dict object' has no attribute 'exists'\n\nThe error appears to have been
 in '/home/martin/ansible/blogpost/environment/broken.yml': line 20, column 11, 
but may\nbe elsewhere in the file depending on the exact syntax problem.\n\nThe 
offending line appears to be:\n\n\n        - name: remove old OPatch backup\n  
        ^ here\n"}

PLAY RECAP *********************************************************************
server1                    : ok=2    changed=0    unreachable=0    failed=1   

[martin@controller environment]$ 

It doesn’t really matter what I was trying to do here, what matters though is the somewhat illegible formatting of the output. The listing above really shows how the error displayed in my terminal. I haven’t quite understood yet why there are linebreaks (\n) in the output that don’t result in a carriage return on screen.

So I did a little bit of digging around and found a global setting named stdout_callback. This is usually defined in /etc/ansible/ansible.cfg which would be bad news for developers if we couldn’t override it. Thankfully you can – using $HOME/.ansible.cfg or even an ansible.cfg file in your project directory. Setting stdout_callback to “debug” reveals a much more readable version of the error:

TASK [remove old OPatch backup] ************************************************
fatal: [server1]: FAILED! => {}

MSG:

The conditional check 'backup_present.exists' failed. The error was: error while
 evaluating conditional (backup_present.exists): 'dict object' has no attribute 
'exists'

The error appears to have been in '/home/martin/ansible/blogpost/environment/bro
ken.yml': line 20, column 11, but may
be elsewhere in the file depending on the exact syntax problem.

The offending line appears to be:


        - name: remove old OPatch backup
          ^ here


PLAY RECAP *********************************************************************
server1                    : ok=2    changed=0    unreachable=0    failed=1

I find this much easier to read, and by setting stdout_callback to a non-default value in my project directory I don’t break anything inadvertently. It also immediately revealed I wasn’t checking backup_exists.stat.exists, I used backup_exists.exists. Pretty-printing the output helped me debug the mistake much quicker. Later on, when your script is ready to be deployed it’s probably a good idea not to use the debug callback ;)

The ansible version in this post is 2.6.4 by the way.

Happy scripting!