Hiera does not return Array of first elements - only "first first" element

[cschoell]

When calling hiera-mysql only the the first entry from a single select is returned.

My Debug output is:

DEBUG: mysql_backend initialized
DEBUG: mysql_backend invoked lookup
DEBUG: resolution type is priority
DEBUG: Executing SQL Query: SELECT value FROM configdata WHERE var='lamp_example_params::clientips' AND fqdn='lamp-test-ubuntu-db.novalocal'
DEBUG: Mysql Query returned 2 rows
DEBUG: Mysql value : 172.18.0.19
DEBUG: Mysql value : 172.18.0.28 
172.18.0.19

For the last line I would expect:
172.18.0.19 172.18.0.28

My Create Table Statement is:

CREATE TABLE `configdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `var` varchar(255) NOT NULL,
  `value` text,
  `env` varchar(255) DEFAULT NULL,
  `fqdn` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

And my hiera.yaml:

:backends:
  - mysql
:mysql:
 :host: localhost
 :user: root
 :pass: *****
 :database: hieradata

 :query:
  - SELECT value FROM configdata WHERE var='%{key}' AND fqdn='%{fqdn}'
  - SELECT value FROM configdata WHERE var='%{key}' AND env='%{environment}'
  - SELECT value FROM configdata WHERE var='%{key}' AND env='common'

[cschoell]

I have implemented a fix which works for me at least:

# Class Mysql_backend
# Description: MySQL back end to Hiera.
# Author: Craig Dunn <craig@craigdunn.org>
#
class Hiera
    module Backend
        class Mysql_backend
            def initialize
                begin
                  require 'mysql'
                rescue LoadError
                  require 'rubygems'
                  require 'mysql'
                end

                Hiera.debug("mysql_backend initialized")
            end
            def lookup(key, scope, order_override, resolution_type)

                Hiera.debug("mysql_backend invoked lookup")
                Hiera.debug("resolution type is #{resolution_type}")

                answer = nil

                # Parse the mysql query from the config, we also pass in key
                # to extra_data so this can be interpreted into the query 
                # string
                #
                queries = [ Config[:mysql][:query] ].flatten
                queries.map! { |q| Backend.parse_string(q, scope, {"key" => key}) }

                queries.each do |mysql_query|

                  results = query(mysql_query)

                  unless results.empty?
                    case resolution_type
                      when :array
                        answer ||= []
                        results.each do |ritem|
                          answer << Backend.parse_answer(ritem, scope)
                        end
                      else
                        #return single element if only one result
                        if results.size == 1
                          answer = Backend.parse_answer(results[0], scope)
                        else
                         #return all results as array - this is required to get all rows for a single query
                         answer ||= []
                          results.each do |ritem| 
                         answer << Backend.parse_answer(ritem, scope)
                        end
                       end
                       #break for resolution_type which is not an array / stop on first find for priority type
                       break
                    end
                  end

                end
              answer
            end

            def query (sql)
                Hiera.debug("Executing SQL Query: #{sql}")

                data=[]
                mysql_host=Config[:mysql][:host]
                mysql_user=Config[:mysql][:user]
                mysql_pass=Config[:mysql][:pass]
                mysql_database=Config[:mysql][:database]

                dbh = Mysql.new(mysql_host, mysql_user, mysql_pass, mysql_database)
                dbh.reconnect = true

                res = dbh.query(sql)
                Hiera.debug("Mysql Query returned #{res.num_rows} rows")


                # Currently we'll just return the first element of each row, a future
                # enhancement would be to make this easily support hashes so you can do
                # select foo,bar from table
                #
                if res.num_fields < 2
                  res.each do |row|
                    Hiera.debug("Mysql value : #{row[0]}")
                    data << row[0]
                  end

                else
                  res.each_hash do |row|
                    data << row
                  end
                end

                Hiera.debug("Returning Data : #{data}")
                return data
            end
        end
    end
end

[ghost]

You should commit this, it works for me at least.

[chestarss]

use hiera_array(key)

[mikehurn]

Hi Craig,

Thank you for writing hiera-mysql.
Is there any way it could return an array when required?

Hi Christof,

Thank you for your updated version. It works for me unless I only want one entry in an array.
Do you have any ideas?

I am testing with NTP 'https://forge.puppetlabs.com/puppetlabs/ntp'.
From the command line and via a puppet run.

hiera -d ntp::servers

puppet agent -t

See the entry for servers in my common.yaml. And my MySQL test table.

ntp::servers:

• 0.us.pool.ntp.org
• 1.us.common.ntp.org
• 2.us.pool.ntp.org
• 3.us.pool.ntp.org

mysql> select * from data;
+----+--------------+-------------------+
| id | var | val |
+----+--------------+-------------------+
| 1 | ntp::servers | 0.ca.pool.ntp.org |
| 2 | ntp::servers | 1.ca.pool.ntp.org |
| 3 | ntp::servers | 2.ca.pool.ntp.org |
+----+--------------+-------------------+

In my hiera.yaml I have:

---

:backends:

• mysql
• yaml

:mysql:
:query: SELECT val FROM data WHERE var='%{key}'

Every test is perfect unless I only have one entry in the DB. The MySQL Puppet run fails as it gets a string when it is expecting an array! Yet the yaml tests all work even when there is only one ntp server.

At the moment I have a version of hiera-mysql that works for my requirements. The following are a suggestion to give some ideas. For the moment I am steadily improving my knowledge on Puppet, Hiera & Ruby.

I do not know if it would be possible use a table like the one below. Where 'type' = 'string'|'array'|'hash'.

Then automatically add 'type' to the queries, Query the DB then extract the 'type' from the results to determine how to return the data.

SELECT type,val FROM data WHERE var='%{key}'

CREATE TABLE data (
id int(11) NOT NULL AUTO_INCREMENT,
type varchar(255) NOT NULL,
var1 varchar(255) NOT NULL,
val1 text,
var2 varchar(255) DEFAULT NULL,
val2 text,
environment varchar(255) DEFAULT NULL,
fqdn varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
)

Please get in touch if I can help…