Dec 29

MySQL: How to solve “[MY-013360] mysql_native_password is deprecated”

By Iain Development, Linux No Comments »

Have you ever looked through your server logs and seen something like this?

[MY-013360] [Server] Plugin mysql_native_password reported: ”mysql_native_password’ is deprecated and will be removed in a future release. Please use caching_sha2_password instead’

This is because, as you can see in the MySQL 8.1.0 release notes, the plugin is now deprecated.

Luckily, the warning also tells you the solution – switch to caching_sha2_password instead. However it doesn’t tell you how to do that.

This needs to be done on a per-user basis, and you also need to know the password of the user. To find out which users to update, run the following SQL:

SELECT user, host, plugin FROM mysql.user;

(Note, you can enter a SQL console on the host machine with something like mysql -u root -p)

This will give you an output something like this:

mysql> SELECT user, host, plugin FROM mysql.user ORDER BY user;
+------------------+--------------+-----------------------+
| user             | host         | plugin                |
+------------------+--------------+-----------------------+
| debian-sys-maint | localhost    | mysql_native_password |
| example_user     | localhost    | mysql_native_password |
| mysql.infoschema | localhost    | caching_sha2_password |
| mysql.session    | localhost    | mysql_native_password |
| mysql.sys        | localhost    | caching_sha2_password |
| mysqld_exporter  | localhost    | mysql_native_password |
| root             | localhost    | mysql_native_password |
+------------------+--------------+-----------------------+

As you can see, some users are configured with the mysql_native_password plugin. All we need to do is alter the users and update them to caching_sha2_password.

Here’s an example:

ALTER USER 'mysqld_exporter'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'StrongPassword';

You might want to FLUSH PRIVILEGES; once you’re done.

Tagged with:
Feb 18

From SVN to Google Cloud Source Repositories

By Iain Development, Linux, Mac OS X No Comments »

I have been maintaining my own SVN server for many years, but in order to share code and make use of managed services, it was time to migrate some of my repositories to Git.

There are many tutorials for svn-git migration, here’s a customised script which works for me: migrate-svn-to-git.sh. Note, it requires an authors.txt file – there any plenty of adequate resources out there for how to create one.

There is nothing specific about this script for Google Source Repos, that is just my choice for private code so it can make use of Google Cloud Build and gcr.io.

#!/usr/bin/env bash

svnUri="$1"
gitUri="$2"

if [[ -z "${svnUri}" || -z "${gitUri}" ]]; then
    echo "[ERROR] Usage migrate-svn-to-git.sh <svn_uri> <git_uri>" 1>&2
    exit 1
fi

if [[ ! -f authors.txt ]]; then
    echo "[ERROR] authors.txt is missing" 1>&2
    exit 1
fi

echo "[INFO] Cloning from SVN: ${svnUri}"
git svn --authors-file=authors.txt clone -s ${svnUri} --prefix svn/ migration
cd migration

echo "[INFO] Creating Git tags"
for t in $(git for-each-ref --format='%(refname:short)' refs/remotes/svn/tags); do git tag $(echo ${t} | sed 's^svn/tags/^^') ${t} && git branch -D -r ${t}; done
echo "[INFO] Creating Git branches"
for b in $(git for-each-ref --format='%(refname:short)' refs/remotes/svn | grep -v trunk); do git branch $(echo ${b} | sed 's^svn/^^') refs/remotes/${b} && git branch -D -r ${b}; done

echo "[INFO] Creating .gitignore file"
git svn show-ignore > .gitignore
git add .gitignore
git commit -m "Added .gitignore"

echo "[INFO] Pushing to Git: ${gitUri}"
git remote add google ${gitUri}
git push --force google --tags
git push --force google --all

cd -

echo "[INFO] Removing migration directory"
rm -rf migration

Once you have created the Git repo, you need to switch your working copy of a project from SVN to Git. Here’s a script: switch-svn-to-git.sh

#!/usr/bin/env bash

gitUri="$1"

if [[ -z "${gitUri}" ]]; then
    echo "[ERROR] Usage switch-svn-to-git.sh <git_uri>" 1>&2
    exit 1
fi

if [[ ! -d .svn ]]; then
    echo "[ERROR] Not a SVN project" 1>&2
    exit 1
fi

echo "[INFO] Finding current branch"
svnBranch=$(svn info | grep '^URL:' | egrep -o '(tags|branches)/[^/]+|trunk' | egrep -o '[^/]+$')
echo "[DEBUG] ${svnBranch}"

echo "[INFO] Cleaning SVN directory"
rm -rf .svn

echo "[INFO] Initialising Git from: ${gitUri}"
git init
git remote add origin ${gitUri}
git fetch

echo "[INFO] Saving working copy"
git show origin/master:.gitignore > .gitignore
git checkout -b tmp
git add .
git commit -m "local changes"

echo "[INFO] Checking out branch"
gitBranch="master"
if [[ ${svnBranch} != "trunk" ]]; then
    gitBranch="${svnBranch}"
fi
git checkout ${gitBranch}

echo "[INFO] Merging working copy"
git merge --allow-unrelated-histories -X theirs --squash tmp
git branch --delete --force tmp

echo "[INFO] Deleting IntelliJ reference to SVN (if exists)"
test -f .idea/vcs.xml && rm .idea/vcs.xml

echo "[INFO] Done - you may want to set your name / email with: git config user.email <email>"
Dec 03

PHP PDO: How to solve “Cannot execute queries while other unbuffered queries are active”

By Iain Development No Comments »

While migrating one of my hobby projects from the PHP mysql extension to PDO, I came across this error:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.'

A quick search on the web suggested this happens when you don’t fetch all rows from a query. I knew this wasn’t the case and didn’t want to just enable the buffered query attribute as I felt something else was wrong.

Turns out this problem came about as I was trying to migrate my MySQL connection properties, previously defined with:

[code lang=”php”]define(‘UTC_OFFSET’, date(‘P’));
mysql_query(“SET time_zone='” . UTC_OFFSET . “‘;”);
mysql_query(“SET NAMES ‘utf8’ COLLATE ‘utf8_general_ci;'”);[/code]

The natural change was to add these two statements to PDO::MYSQL_ATTR_INIT_COMMAND (separated by a semicolon). However, that’s where the problem is. The SET command allows both to be specified at once, hence the right way of doing it is:

[code lang=”php”]PDO::MYSQL_ATTR_INIT_COMMAND => “SET NAMES ‘utf8’ COLLATE ‘utf8_general_ci’, time_zone = ‘” . UTC_OFFSET . “‘”[/code]

Credit: Stack Overflow

Bonus: Setting the timezone with a UTC offset allows you to use a zone that PHP knows about, but the MySQL server doesn’t. That way it can be set with the ini setting date.timezone or date_default_timezone_set and doesn’t need to be modified in two places if it needs to be changed.

preload preload preload