Updating my.ini causes mysql service to stop

PowerShell Help
1

I’m new to powershell and have created a script to update a my.ini file with the required settings, however once I run the script and update the file the service will not start.

I have narrowed it down to the updates causing the file to save using the UFT-8 format instead of ANSI. Is there a way I can prevent this or an option I can add to the script?

2

Hi and welcome to the forum. While not always true, it’s often the case that in order for us to help we need to see example code.
When posting code, make sure to use the “Preformatted text” button so that the code is formatted correctly.
It would help do know how you’re updating the contents of the file. I.e. Set-Content, Add-Content, Out-File, etc.
All of the ones I just mentioned have an -Encoding parameter you should check out.

3

Sorry I should have posted this earlier to help explain how I have set up the function to make the changes to the ini file. I had looked at setting the -Encoding for the Set-Content statement but it didn’t seem to help, perhaps I did it incorrectly.

function  iniConfig{
# Set the full path to the mySQL ini file
$mySQLPath = "C:\ProgramData\MySQL\MySQL Server 8.0"

# Set the path to the my.ini file
$mySQLIniPath =  Join-Path -Path $mySQLPath -ChildPath "my.ini"

Write-Host "-----------------------------------------------------------------" -ForegroundColor Yellow
Write-Host "-----------          my.INI Updater                -------------" -ForegroundColor Yellow
Write-Host "-----------------------------------------------------------------" -ForegroundColor Yellow

Write-Host ""
Write-Host "Checking my.ini has been installed in the correct location."
Write-Host ""

$variables = @(
	
	@{
        Name = "innodb_log_file_size"
        DefaultSetting = "48M"
		Recommended = "96M"
    },	
    @{
        Name = "max_connections"
        DefaultSetting = "151"
		Recommended = "500"
    },
	@{
        Name = "innodb_buffer_pool_size"
        DefaultSetting = "128M"
		Recommended = "8G"
    }
	
)

Write-Host "-----------------------------------------------------------------" -ForegroundColor Yellow
Write-Host "-----------          Checking MY.INI file        ---------------" -ForegroundColor Yellow
Write-Host "-----------------------------------------------------------------" -ForegroundColor Yellow
Write-Host ""

Write-Host "Looking at file: $mySQLIniPath"
Write-Host ""

# Read the contents of the my.ini file
$mySQLIni = Get-Content $mySQLIniPath

# Create a variable to store the changes made
$changes = @()


# Loop through each variable and output its name, value, description, and recommended value (if any)
foreach ($variable in $variables) {
    $description = $variable.Description
    $name = $variable.Name
	$default = $variable.DefaultSetting
    $recommended = $variable.Recommended
    $pattern = "^" + $name + "\s*=\s*(.*)"
    $value = ($mySQLIni | Where-Object { $_ -match $pattern } | ForEach-Object { $Matches[1] })

    if ($value -eq $recommended) {
        Write-Host ""
		Write-Host "-----------------------------------------------------------------"
		Write-Host "$name = $value"
		Write-Host "Default Setting = $default"
		Write-Host "Recommended = $recommended"
		Write-Host "# $description"
		Write-Host "# Matches recommended setting" -ForegroundColor Green
		
    } else {
		
        Write-Host ""
		Write-Host "-----------------------------------------------------------------"
		Write-Host "$name = $value"
		Write-Host "Recommended = $recommended"
		Write-Host "# $description"
		
        if ($recommended) { Write-Host "# Does not match recommended setting" -ForegroundColor Red}

        # Prompt the user to update the variable to its recommended value
        Write-Host -ForegroundColor Yellow "`nDo you want to update this variable from $value to its recommended value - $recommended ? (y/n)"

		# Read the user's input
		$updateVariable = Read-Host

        if ($updateVariable -eq 'y') {
            # Update the variable in the my.ini file
			# Add the change to the $changes array
			$changes += "$name has been updated from $value to its recommended value: $recommended `n `n"
			
            (Get-Content $MySQLIniPath) -replace "^$name\s*=.*", "$name=$recommended" | Set-Content $mySQLIniPath
            Write-Host "`n$name has been updated from $value to its recommended value: $recommended" -ForegroundColor Green
			
			
        }
    }
}

}
4

thanks for sharing your code.
I see the line near the end where you do a Get-Content then a -Replace statement and pipe that out to Set-Content.
This may be less of a Powershell issue and more of an issue with mysql. If you have a copy of Notepad++ can you open the ini file and look in the bottom right hand corner where it identifies the file encoding? Can you do this for a “working” file and a “non-working” file and compare?

You should be able to modify your Set-Content line to include -Encoding but I’m not seeing that in your example. If you tried this, please verify the file encoding separately with something like Notepad++, and even though it’s not Powershell, maybe provide the error from the service?

5

Thanks for your help with this as I have now found the solution.

The actual problem was the value I was setting for innodb_buffer_pool_size was too high for the system I was testing on, once I reduced the value it works correctly. I may now try to build in a check to prevent me from making the same mistake when it is rolled out to other servers.

1 Like
6

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.