WFA 4 SQL INSERT INTO NOT WORKING IN COMMAND RESERVATION

[ninja]

I have a mysql query in the reservations tab of a command which is not doing anything against the database. When I run the same query against the playground database with the same exact tables it works and updates the table.

The query is an INSERT INTO and I have also tried an UPDATE.

The table that i'm updating is a customer table that I created under cm_storage.

Need help

Is there some place where I can see logs where it errored for the sql command?

The volume_move_job is a custom table in WFA that I built.

INSERT
INTO
cm_storage.volume_move_job
SELECT
'103' AS id,
'something' AS name,
volume.id AS volume_id,
'-' AS uuid,
'-' AS resource_key,
'892' AS job_identifier,
NULL AS start_time,
NULL AS completion_time,
'CUTOVER_HARD_DEFERRED' AS phase,
'cutover_hard_deferred' AS phase_raw,
'ALERT' AS state,
'alert' AS state_raw,
'somethin' AS details,
NULL AS percent_complete,
NULL AS estimated_completion_time,
'0' AS inactive,
'0' AS create_time,
'0' AS update_time,
'aggrname01' AS destination_aggregate_name,
aggregate.name AS source_aggregate_name,
vserver.name AS vserver_name,
cluster.name AS cluster_name
FROM
cm_storage.volume volume,
cm_storage.aggregate aggregate,
cm_storage.vserver vserver,
cm_storage.cluster cluster
WHERE
cluster.name = 'clustername'
AND vserver.name = 'vservername'
AND volume.name = 'volumename01'
AND aggregate.id = volume.aggregate_id
AND 'wait' = 'wait';

[sinhaa]

@ninja

You need to understand how reservation works. The SQL INSERT/UPDATE statements executed in reservations tab are not committed on the mysql Database. They are maintained in separate reservation tables handled by WFA.

Hence you can't see the impact of those SQL queries by looking into DB. You can only see them through WFA.

So how do you see that your reservations got executed the way you wanted to?

1. There will be an entry in Execution->Reservation table for the execution job. If you have properly written Reservation representation string, you can see the values of the variables.

2. The 2nd and better way is to use User-Input of type query to your service. So have a 'Test Reservation workflow' to help you.

Add a workflow and put a 'Search or Define command'. Or you can put any command you wish.

Don't select any dictionary, just press Ok

In constants tab, have a constant named; QUERY , value: $query

Now you have a user-input to your workflow.

Chnage its type from string to 'Query'.

Now when you write your query,  and do TEST you'll have option check-box to  use reservation data.

SO now the rows will show : data in DB + Data due to reservation

You can uncheck it to see the difference your reservation is making.

Save your workflow for all your future Reservation tests.

sinhaa

[ninja]

Thanks. That's it. My original query uses user-input and was correct. I was just looking for something that doesn't exist.

Another question to add to this. Since the reservation doesn't actually execute and save anything in the WFA DB what does the verification tab run against or what is it doing?

[sinhaa]

@ninja 

Verification is used for command that create or remove new objects. The SQL query in 'Verification' tab is used for 'Element Existance validation'. You see the 2 check-boxes in the Workflow 'Details' tab. (1) Consider Reserved Elements (2) Enable element existance

So take this example of create a volume command. You want to create a volume name vol001 on aggr1 Vserver1 Cluster1.

If this volume is already present then the query in the 'Validation' script will retrun SUCCESS or a row based on how the SQL query was written. So basically an non-empty row. 

When this happens, WFA planning will fail saying that the element you are trying to create already exists.