[MDEV-32745] config upgrade helper tool Created: 2023-11-09  Updated: 2024-02-02

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Major
Reporter: Sergei Golubchik Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-26923 MariaDB will abort server startup if ... In Review

 Description   

A tool that helps to adjust config files after MariaDB is upgraded.

It can list all options that are no longer recognized by the server, suggest replacements, if any.
It could also edit files in place (when requested), removing, commenting out, or adding loose- prefix to such options.

Also it could check for invalid values of enum/set variables.

To parse all files it could use my_print_defaults, the list of supported options could be hard-coded or extracted from mariadbd --help and such.

perl/python or C/C++ ? C or C++ is probably more portable for windows.

For code how to parse options like !include, check mysys/my_defaults.c

Suggested options:

  • No options: List unknown server options on stdout (those that would prevent the server from starting).
  • --update
    • Update the my.cnf file in place (and all files included with !include or !includedir)
  • --backup
    • Store modified files as backup (for example my.cnf-backup)
  • --current-version=... (for example --current-version=mysqld-5.7)
    • Section to use for unknown options, see --mode
      • The purpose of --current-version is to enable the tool to create a config file that will work for both MySQL and MariaDB at
        the same time. In this case, options that works for both MySQL and MariaDB should use the section [mysqld]. MySQL unique options should be under [mysqld-5.7] and MariaDB specific options
        would be under [mariadbd].
  • --edit=...
    • Select what to do with unknown options. List of suggested mode options:
      • remove Remove unoption
      • comment Add # before option
      • inline-old-version Add ['current-version'] before the option and [mysqld] afterwards. 'curent-version' is the tag specified with --current-version=...
      • last-old-version Move all unknown version last in the file with [old-version] before.
  • --print
    • Print converted file on stdout. In this case all !include and !includedir files should be included.

We need both inline- and last- as some options may depend on their place in the config file.

The tool should also be prepared for special handling of some specific options.

Note that MariaDB don't have the audit_log plugin (Percona Server).
Instead MariaDB has a server_audit plugin which takes different options.
If the tool notice that 'audit_plugin' is used, it should suggest one to use the server_audit plugin instead.

--plugin-load=QUERY_RESPONSE_TIME_READ=query_response_time.so;QUERY_RESPONSE_TIME_WRITE=query_response_time.so
This is only supported in MariaDB 10.11 and above. If used with an earlier MariaDB version it would be good to get a note that this is supported in 10.11.6 and above.

As a reference, here are some common config options found in Percona server that is not in MariaDB:
[mysqld-5.7]
--binlog-ignore-db=information_schema
--innodb_log_files_in_group=2
--innodb_buffer_pool_instances=3
--max_binlog_files=14
--log_slow_rate_type=query
--slow_query_log_always_write_time=0.5
--slow_query_log_use_global_control=all
--plugin-load=audit_log.so

 Comments   
Comment by Daniel Black [ 2023-11-14 ]

First concept to show incorrect options:

my_convert.sh

#!/bin/bash
 
 
 
declare -a opts
 
declare -a maybebad
 
declare -a isbad
 
 
 
readarray -t opts  < <( my_print_defaults --mysqld )
 
 
 
while ! mariadbd --no-defaults "${opts[@]}" --help --verbose > /dev/null 2>&1 
do
 
	maybebad+=( "${opts[-1]}" )
 
	unset "opts[-1]"
 
done
 
 
 
while [ ${#maybebad[@]} -gt 0 ]
 
do
 
	opts+=( "${maybebad[-1]}" )
 
	unset "maybebad[-1]"
 
	if ! mariadbd --no-defaults "${opts[@]}" --help --verbose > /dev/null 2>&1 
	then
 
		isbad+=( "${opts[-1]}" )
 
		unset "opts[-1]"
 
	fi
 
done
 
 
 
echo Good options: "${opts[@]}"
 
echo Bad options: "${isbad[@]}"

Example:

 ./my_convert.sh 
Good options: --table_open_cache=17384 --max_connections=150 --datadir=/home/dan/mariadb-datadir --slow_query_log_file=/home/tom/x.slow
 
Bad options: --bababa=foo

Comment by Michael Widenius [ 2023-11-14 ]

Great first version.
Some comments:

  • This will probably not work on windows (bash)
  • It will not work for anyone wanting to create an automatic script that will migratate MySQL 5.7 to MariaDB.

What I would like to see in the final script/tool:

  • Option to list 'bad options' (like above)
  • Be able to run it on an existing config files and print the resulting config file.
  • In this case we should move 'bad' options' in the 'server' or 'mysqld' section to [mysqld-5.7] (user specified) to the end of the script or add a #not-in-mariadb comment before them. The benefit of using [mysqld-5.7] is that the config files would be usable both by MariaDB and MySQL.
  • Be able to run it on all config file and fix them inplace.
  • When pointing to a config file for automatic conversion, support the '!include" and "!includedir" options.

When it comes to fixing things inplace, check out the replace tool. It does this and also has optimization to not change files that does not need conversion.

Comment by Daniel Black [ 2023-12-14 ]

note MDEV-26923 Check all invalid config options #2935

Comment by Vladislav Vaintroub [ 2023-12-21 ]

mysql_upgrade_service.exe (windows upgrade tool) has edited my.ini to remove invalid options for a while now, itself. Without suggestions, it removed known outdated options. It did not support "!include" , but neither our nor MySQL installer use any includes on Windows

Generated at Thu Feb 08 10:33:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.