For many years the debconf-set-selections utility has been used by Debian/Ubuntu sysadmins to input a password for the MySQL root user before APT begins the actual package installation (e.g. apt install mysql-server).
However, if you install MySQL without this utility, the root user will have a blank password field in the mysql.users table, meaning you can sudo mysql -u root in the command line without entering a password.
If this works, why doesn't it work within shell scripts?
For example:
## this works fine in shell scripts (MySQL root user will have a blank password) ##
apt update
apt install mysql-server-8.0
## this only works when typed into the shell (not within a script) ##
mysql --user=root -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'ABC123';"
mysql --user=root -e "FLUSH PRIVILEGES;"
P.S. I'm aware of mysql_secure_installation wizard and manual queries (alternative), but I'm wondering why a blank password results in failure of MySQL access when used in Bash scripts, etc. It does not appear to be related to common issues like user, bind-address, protocol, host, socket, or general permissions.
Is there a solution here besides debconf-set-selections and janky expect guess-and-check?