mysql 5.6 to MariaDB 10.3.29 - sql error logging issues

[competitions]

My webhost is migrating clients including me to a new server and as part of that I have discovered that they have taken us from mysql 5.6 to MariaDB 10.3.29

Seems MariaDB sql error logging is a plugin [SQL Error Log Plugin] - assuming " The SQL_ERROR_LOG plugin collects errors sent to clients in a log file" means collecting issues such as for example an sql statement attempting to update a table record with a null which is no longer allowed.

SHOW PLUGINS revealed it wasn't enabled so, my.cnf was added to with

[mariadb]
# sql errors plugin from SQL Error Log Plugin
plugin_load_add = sql_errlog
sql_error_log_filename = /var/lib/mysql/mysql-err.log

and sql server restarted, then

SHOW PLUGINS reveals it worked:

SQL_ERROR_LOG

ACTIVE

AUDIT

sql_errlog.so

GPL

However a CAT of that mysql-err.log file reveals:

2021-06-10 13:30:01 [Cpanel::MysqlUtils::Unprivileged] @ localhost [] ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MariaDB client : (null)
2021-06-10 13:30:01 [Cpanel::MysqlUtils::Unprivileged] @ localhost [] ERROR 1160: Got an error writing communication packets : (null)
2021-06-10 13:48:51 [Cpanel::MysqlUtils::Unprivileged] @ localhost [] ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MariaDB client : (null)
2021-06-10 13:48:51 [Cpanel::MysqlUtils::Unprivileged] @ localhost [] ERROR 1160: Got an error writing communication packets : (null)
2021-06-10 13:48:51 [Cpanel::MysqlUtils::Unprivileged] @ localhost [] ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MariaDB client : (null)
2021-06-10 13:48:51 [Cpanel::MysqlUtils::Unprivileged] @ localhost [] ERROR 1160: Got an error writing communication packets : (null)


I need this sql error file working to help identify sql issues that will/have arisen from the transition from mysql 5.6 to MariaDB 10.3.29 - any advice appreciated.

 

[cPJustinD]

Hello competitions! Do you happen to know if you used the mysql_native_password plugin while on the previous server running MySQL 5.6? Also, does your new server happen to be running Node?

Please provide us with the output of the following command as well, as it may help provide us with a better understanding of what is occurring:

grep max_allowed_packet /etc/my.cnf

We look forward to hearing back from you!

 

[competitions]

Hello competitions! Do you happen to know if you used the mysql_native_password plugin while on the previous server running MySQL 5.6? Also, does your new server happen to be running Node?

Please provide us with the output of the following command as well, as it may help provide us with a better understanding of what is occurring:

grep max_allowed_packet /etc/my.cnf

We look forward to hearing back from you!

The old server [VPS] shows the following plugins are active, not sure that is the definitive answer you are after. Is there a way to tell if mysql_native_password is/was used?

mysql_native_password	ACTIVE	AUTHENTICATION	NULL	GPL
mysql_old_password	ACTIVE	AUTHENTICATION	NULL	GPL
sha256_password	ACTIVE	AUTHENTICATION	NULL	GPL

New VPS:

max_allowed_packet = 16M

On new VPS, by Node, do you mean
WHM -> Link Server Nodes ? None listed there.
or perhaps node.js?
WHM -> EasyApache - > Currently Installed Packages - Contains Apache 2.4, PHP 7.3, PHP 7.4, and PHP 8.0

 

[ffeingol]

The issue with the null inserts sounds like strict mode issues.

 

[cPJustinD]

Yes, I was referring to NodeJS. And it does look like mysql_native_password was used in the previous environment.

I did also find the following information from StackOverflow that may help:

What's going on?
Let's first make it clear what's going on.MySQL 8 has supports pluggable authentication methods. By default, one of them named caching_sha2_password is used rather than our good old mysql_native_password (source). It should be obvious that using a crypto algorithm with several handshakes is more secure than plain password passing that has been there for 24 years!Now, the problem is mysqljs in Node (the package you install with npm i mysql and use it in your Node code) doesn't support this new default authentication method of MySQL 8, yet. The issue is in here: https://github.com/mysqljs/mysql/issues/1507 and is still open, after 3 years, as of July 2019.

Source: MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

If this doesn't help you resolve the issue, however, I think it would be best to open a support ticket so that our analysts can review the issue more thoroughly and determine what exactly is occurring. You can submit a support request using the "Submit a ticket" link in my signature below.

Please be sure to link this thread when opening the ticket and provide the ticket number here so that we can track the issue appropriately. If possible, please post the resolution on this thread as it may help other community members with similar issues.

 

[competitions]

Thank you. Have submitted a support request, number 94338806.

 

[cPRex]

Thanks for submitting a ticket to our team. It looks like our support team was not able to replicate the steps so they asked for some additional details. I'm following along with the ticket on my end now so I'll be sure to keep this thread updated with more information.