For loop sql query each row from variable output in shellscripts

Question

The $columns_output inside value like below:

dbname1.tablename
dbname2.tablename
dbname3.tablename
dbname4.tablename

Part of the scripts section

$columns_output=""

for value in "$columns_output"; do

    sql_query2="ANALYZE table '$columns_output';"

    query_result2=$(mysql -h "$db_host" -P "$db_port" -u "$db_user" -p"$db_password" -e "$sql_query2")

    echo -e "Query Analyze Results:\n$query_result2"
done

How to achieve like below result need to be execute the query for each rows? Need some help with the logic and correction.

ANALYZE table dbname1.tablename;
ANALYZE table dbname2.tablename;
ANALYZE table dbname3.tablename;
ANALYZE table dbname4.tablename;

This really looks like you should not be doing it in a shell script. Why are you doing database maintenance in a shell script? It's expect this to happen within a framework where you use a more sensible programming or scripting language to interface with your database, anyways. — Marcus Müller
– Marcus Müller, Commented Oct 7, 2023 at 9:17
Well because we have 50+ db and 1500++ tables in many different environment (DEV1,DEV2,DEV3 so on) whereby I couldn't do manually 1 by 1 so thats how I need to come out a shell script to perform this during no heavy traffic in TiDB server. For the first stage we scan those tables health below 90% threshold, next will be perform analyze those tables health below 90%. — Jose Ace
– Jose Ace, Commented Oct 7, 2023 at 15:50
That sounds like you should have central proper monitoring, not this kind of scripting — Marcus Müller
– Marcus Müller, Commented Oct 7, 2023 at 16:30

Kusalananda · Accepted Answer · 2023-10-07 09:54:45Z

1

Assuming that columns_output is a single string containing newline-delimited table names. We may then create a set of ANALYZE TABLE statements by inserting those words at the start of each line in $columns_output, and a ; at the end, and then feeding them to the database client:

printf '%s\n' "$columns_output" | sed '/.*/ANALYZE TABLE &;/' |
mysql -h "$db_host" -P "$db_port" -u "$db_user" -p"$db_password"

With an array of tables:

tables=(
    dbname1.tablename
    dbname2.tablename
    dbname3.tablename
    dbname4.tablename
)

printf 'ANALYZE TABLE %s;\n' "${tables[@]}" |
mysql -h "$db_host" -P "$db_port" -u "$db_user" -p"$db_password"

Instead of a statement per table, you could also use ANALYZE TABLES (note the S) with a comma-delimited list of tables. Assuming we use an array of table names as above:

(IFS=, ; printf 'ANALYZE TABLES %s\n' "${tables[*]}") |
mysql ...as before...

edited Oct 7, 2023 at 9:54

answered Oct 7, 2023 at 9:35

Kusalananda♦

356k42 gold badges737 silver badges1.1k bronze badges

Not suitable for me to use an array when my $columns_output has 500-600 records to manually writing. Thanks for the suggestion, anyway I used different approach to output as SQL scripts and use AWK awk '{ print "ANALYZE table",$0";" }' As for the last stage will be run the SQL scripts to execute in TiDB Server.

Jose Ace
– Jose Ace

2023-10-07 16:00:12 +00:00
Commented Oct 7, 2023 at 16:00
@JoseAce here too, the first approach doesn't use an array.

terdon
– terdon ♦

2023-10-07 16:01:03 +00:00
Commented Oct 7, 2023 at 16:01
@JoseAce As terdon said, my 1st approach does not use an array, and all three variants produce a single SQL "script" that the mysql client executes. I don't understand what you mean by "manually writing". You don't have to write 500 tabels manually, just read them into an array with readarray -t tables < <(some-command). I just hardcoded the table names because that's what you showed in the question.

Kusalananda
– Kusalananda ♦

2023-10-07 18:56:22 +00:00
Commented Oct 7, 2023 at 18:56
@Kusalananda, apologize for being misunderstood earlier, and thanks to you and Terdon for making my understanding clearer. I appreciate that it works as my expectation.

Jose Ace
– Jose Ace

2023-10-08 00:36:19 +00:00
Commented Oct 8, 2023 at 0:36

Add a comment |

terdon · Accepted Answer · 2023-10-07 09:17:38Z

0

Assuming your $column_output variable is something like this:

$ printf -v columns_output 'dbname1.tablename\ndbname2.tablename\ndbname3.tablename\ndbname4.tablename' 
$ echo "$columns_output" 
dbname1.tablename
dbname2.tablename
dbname3.tablename
dbname4.tablename

You could do:

printf '%s\n' "$columns_output" | while read table; do
  sql_query2="ANALYZE table '$columns_output';"
  query_result2=$(mysql -h "$db_host" -P "$db_port" -u "$db_user" -p"$db_password" -e "$sql_query2")
  printf 'Query Analysis Results:\n%s\n' "$query_result2"
done

If you have full control of your input, it might make more sense to have columns_output as an array instead of a string. If you have a command that produces the list of tables, you can make your array like this:

table_array=( $(command_that_lists_tables) )

Or, if you are writing them out manually, like this:

table_array=("dbname1.tablename" "dbname2.tablename" 
             "dbname3.tablename" "dbname4.tablename")

Next, presumably, you want to know what comes from which table, so instead of the not very informative "Query Analysis Results", just print out the name of the table and then the results. Like this:

for table in "${table_array[@]}"; do 
   printf '---- Analyzing table "%s" ----\n' "$table"
   mysql -h "$db_host" -P "$db_port" -u "$db_user" -p"$db_password" \
      -e "ANALYZE table '$table';"
done

answered Oct 7, 2023 at 9:17

terdon♦

253k69 gold badges481 silver badges719 bronze badges

Not suitable for me to use an array when my $columns_output has 500-600 records to manually writing. Thanks for the suggestion, anyway I used different approach to output as SQL file scripts and use AWK awk '{ print "ANALYZE table",$0";" }' As for the last stage will be run the SQL scripts to execute in TiDB Server.

Jose Ace
– Jose Ace

2023-10-07 15:57:05 +00:00
Commented Oct 7, 2023 at 15:57
@JoseAce there is no need to use an array, it's just cleaner. Presumably, you are populating the variable with the output of a command. So instead of doing columns_output=$(command), just do columns_output=( $(command) ) and you ave an array. In any case, my first approach doesn't use an array.

terdon
– terdon ♦

2023-10-07 16:00:31 +00:00
Commented Oct 7, 2023 at 16:00
Thanks for highlighting and make me understand clearer. However I face this error ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 27 near "'homestay.t1'" Query Analysis Results: How do i get rid of single quote ? The sql_query2="ANALYZE table '$columns_output';" single quote shouldn't be remove as my understanding.

Jose Ace
– Jose Ace

2023-10-07 16:32:31 +00:00
Commented Oct 7, 2023 at 16:32
Sorry, @JoseAce, but I can't help without knowing the full SQL command. You are adding the quote yourself though, since you're using '$columns_output', and I don't see why the quote would be a problem.

terdon
– terdon ♦

2023-10-07 16:36:00 +00:00
Commented Oct 7, 2023 at 16:36
I did some test removed quote was working good. But it only run first line while second or third line it wont continue executed. Code: printf '%s\n' "$columns_output" | while read table; do sql_query2="ANALYZE table $columns_output;" query_result2=$(mysql -h "$db_host" -P "$db_port" -u "$db_user" -p"$db_password" -v -e "$sql_query2") printf 'Query Analysis Results:\n%s\n' "$query_result2" done

Jose Ace
– Jose Ace

2023-10-07 17:26:41 +00:00
Commented Oct 7, 2023 at 17:26

| Show 2 more comments

Stack Exchange Network

For loop sql query each row from variable output in shellscripts

2 Answers 2

You must log in to answer this question.

Hot Network Questions

For loop sql query each row from variable output in shellscripts

2 Answers 2

You must log in to answer this question.

Related

Hot Network Questions