Selecting Custom parameters

cbtshare · 02-28-2020, 03:21 PM

Hello All,

I am migrating database from one account to another and need to get the custom parameters of the database. It looks like this

Code:

{
  "ParameterName": "tmpdir",
  "ParameterValue": "/rdsdbdata/tmp/",
  "Description": "The directory used for temporary files and temporary tables",
  "Source": "system",
  "ApplyType": "static",
  "DataType": "string",
  "IsModifiable": false,
  "ApplyMethod": "pending-reboot"
}
{
  "ParameterName": "tmp_table_size",
  "ParameterValue": "67108864",
  "Description": "If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. Increased value can improve perf for many advanced GROUP BY queries.",
  "Source": "user",
  "ApplyType": "dynamic",
  "DataType": "integer",
  "AllowedValues": "1024-18446744073709551615",
  "IsModifiable": true,
  "ApplyMethod": "immediate"
}
{
  "ParameterName": "transaction_alloc_block_size",
  "Description": "The amount in bytes by which to increase a per-transaction memory pool which needs memory.",
  "Source": "engine-default",
  "ApplyType": "dynamic",
  "DataType": "integer",
  "AllowedValues": "1024-18446744073709547520",
  "IsModifiable": true,
  "ApplyMethod": "pending-reboot"
}
{
  "ParameterName": "transaction_prealloc_size",
  "Description": "There is a per-transaction memory pool from which various transaction-related allocations take memory. For every allocation that cannot be satisfied from the pool because it has insufficient memory available, the pool is incremented.",
  "Source": "engine-default",
  "ApplyType": "dynamic",
  "DataType": "integer",
  "AllowedValues": "1024-18446744073709547520",
  "IsModifiable": true,
  "ApplyMethod": "pending-reboot"
}
{
  "ParameterName": "tx_isolation",
  "ParameterValue": "READ-COMMITTED",
  "Description": "Sets the default transaction isolation level.",
  "Source": "user",
  "ApplyType": "dynamic",
  "DataType": "string",
  "AllowedValues": "READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE",
  "IsModifiable": true,
  "ApplyMethod": "immediate"
}

I am only interested in ParameterName that has ParameterValues.

I have tried below but it prints columns without parameterValue, and changing || to && returns nothing

Code:

aws rds describe-db-parameters --db-parameter-group-name -perf  --output json | jq '.[] | .[]' | awk '/ParameterName/ || /ParameterValue/{print $0}'

also

Code:

aws rds describe-db-parameters --db-parameter-group-name perf --profile old --output json | jq '.[] | .[]' | grep -e 'ParameterName' -e 'ParameterValue'

produces similar results

I tried doing it all in jq

Code:

aws rds describe-db-parameters --db-parameter-group-name perf --profile old --output json | jq ".[]|.[] | select((.ParameterName > 0) && .ParameterValue)"

Can I please have some assistance?

Thank you

cbtshare · 02-28-2020, 08:13 PM

Figured out the following works

Code:

aws rds describe-db-parameters --db-parameter-group-name perf --profile old --output json | jq '.[] |.[] |select(.ParameterValue | length >= 1) | select(."IsModifiable"==true)'

Isn't perfect, still would only like only
ParameterName
ParameterValue

Turbocapitalist · 02-28-2020, 11:42 PM

That looks like JSON, which is always rather difficult to work with. You can write a short python or perl script to deal with it. Or you can use jq followed by a sed, awk, or perl script:

Code:

jq '.ParameterName + " = " + .ParameterValue'

MadeInGermany · 02-29-2020, 03:53 AM

If the order is fixed then you can pipe to

Code:

grep -B1 '"ParameterValue":'