Share via

How do I configure the CLI to return more than 30 rows in a resultset (query flexible-server mysql)

Thomas Spoelstra | OptimaData BV 21 Reputation points
2025-04-09T07:32:32.5066667+00:00

We execute a query to retrieve the binary log names using the following statement: 

"az mysql flexible-server execute --name $servername --admin-user "$MySQL_User" --admin-password "$MySQL_pwd" --querytext "$query"

Unfortunately, the default settings for the execute is that it returns only the first 30 rows of the resultset, see below:

Successfully connected to <redacted>. Ran Database Query: 'SHOW BINARY LOGS;' Retrieving first 30 rows of query output, if applicable. Closed the connection to <redacted>

This causes issues for us and we would like advice as to how to configure the CLI to return ALL the rows in a resultset, and not limit to the default of 30 rows.

Azure Database for MySQL
Azure Database for MySQL
An Azure managed MySQL database service for app development and deployment.
949 questions
Accepted answer
  1. Mahesh Kurva 3,820 Reputation points Microsoft External Staff
    2025-05-02T04:57:47.6633333+00:00

    Hi Thomas Spoelstra | OptimaData BV,

    I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.

    Issue:

    We execute a query to retrieve the binary log names using the following statement: 

    "az mysql flexible-server execute --name $servername --admin-user "$MySQL_User" --admin-password "$MySQL_pwd" --querytext "$query"

    Unfortunately, the default settings for the execute is that it returns only the first 30 rows of the resultset, see below:

    Successfully connected to <redacted>. Ran Database Query: 'SHOW BINARY LOGS;' Retrieving first 30 rows of query output, if applicable. Closed the connection to <redacted>

    This causes issues for us and we would like advice as to how to configure the CLI to return ALL the rows in a resultset, and not limit to the default of 30 rows.

    Solution:

    I resolved the issue by NOT using the "azure mysql flexible server execute ..." but used a native MySQL client which allowed me to get all rows, code snippet below. This is most likely not the most elegant solution but it worked for me:

    $query="SHOW BINARY LOGS;"
        $myxml=$(mysql -h $serverFQDN -P 3306 -u $MySQL_User -p"$MySQL_pwd"  -N --silent --xml  -e "$query")
        Clear-Content -Path $tempXMLFile
        Add-Content -Path $tempXMLFile -Value $myxml
        [xml]$xml = get-content -Path $tempXMLFile
        $logNamesArray = @()    
        foreach ($row in $xml.resultset.row) {
            $logName = $row.field | Where-Object { $_.name -eq "Log_name" }
            $logNamesArray += $logName.'#text'
        }

    If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

    Hope this helps. Do let us know if you have any further queries.

    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mahesh Kurva 3,820 Reputation points Microsoft External Staff
    2025-04-09T10:18:52.8133333+00:00

    Hi Thomas Spoelstra | OptimaData BV,

    Greetings!

    To configure the Azure CLI to return all rows in a result set, you can modify the query or use additional parameters to control the output. Unfortunately, the Azure CLI for MySQL flexible server does not have a direct parameter to change the row limit for query results.

    Work around

    By using pagination or modifying your query to fetch all rows.

    Here are a few approaches you can consider:

    • If the CLI supports pagination, you can fetch the results in chunks and then combine them. This approach involves running the query multiple times with different offsets.
    • You can modify your query to ensure it fetches all rows. For example, if you are using SHOW BINARY LOGS;, you might need to ensure that the query itself is designed to fetch all rows without limitation.
    • You can write a script to handle the query execution and fetch all rows. This script can use the Azure CLI to execute the query and handle the pagination or row fetching logic.

    Here is an example of how you might write a script to fetch all rows using the Azure CLI:

    1.Save the above script to a file, e.g., fetch_all_rows.sh.

    2.Run chmod +x fetch_all_rows.sh to make the script executable.

    3.Run ./fetch_all_rows.sh to execute the script and fetch all rows.

    servername=""
MySQL_User=""
MySQL_pwd=""
query="SHOW BINARY LOGS;"
# Function to execute the query and fetch results
fetch_all_rows() {
    local offset=0
    local limit=30
    local results=()
    while true; do
        local query_with_limit="$query LIMIT $limit OFFSET $offset"
        local output=$(az mysql flexible-server execute --name $servername --admin-user "$MySQL_User" --admin-password "$MySQL_pwd" --querytext "$query_with_limit")
        if [[ -z "$output" ]]; then
            break
        fi
        results+=("$output")
        offset=$((offset + limit))
    done
    echo "${results[@]}"
}
# Fetch all rows
fetch_all_rows

    Hope this helps. Do let us know if you any further queries.

    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.