WampServer

Hello,

I am trying to use the latest php8.3.10 and MySQL 9.0.1 and I get this message:
Fatal error: Uncaught mysqli_sql_exception: Plugin 'mysql_native_password' is not loaded in ...

The tray icon is green, all services running, but I cannot connect to the database. How can I fix this?

Also, how to I change the "root" with no password account to anything else? (latest Apache 2.4.62)

Ciprian M.



Edited 1 time(s). Last edit at 08/13/2024 06:34AM by ciprianmp.

Hi,

This problem does not exist after installing the MySQL 9.0.1 addon. I've just tried a fresh installation of MySQL 9.0.1 under Apache 2.4.62 and PHP 8.3.10.
The connection to the MySQL console (Left-click -> MySQL -> MySQL Console) is carried out without any problem with the user root without a password.

--- Using the MySQL console

- How to set a password for root (MySQL 9+)

# MySQL 9 - Password and Privileges for `root`
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'new_password' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'localhost' DEFAULT ROLE ALL;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION; 
CREATE USER IF NOT EXISTS 'root'@'127.0.0.1' IDENTIFIED WITH caching_sha2_password BY 'new_password' PASSWORD EXPIRE NEVER;
ALTER USER IF EXISTS 'root'@'127.0.0.1' DEFAULT ROLE ALL;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;
CREATE USER IF NOT EXISTS 'root'@'::1' IDENTIFIED WITH caching_sha2_password BY 'new_password' PASSWORD EXPIRE NEVER;
ALTER USER IF EXISTS 'root'@'::1' DEFAULT ROLE ALL; 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' WITH GRANT OPTION;
#

- How to set a new user

# Password and Privileges for `new_user`@`127.0.0.1`
CREATE USER 'new_user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'user_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
# User privileges on all databases
GRANT USAGE ON *.* TO 'new_user'@'localhost';
# Possibly restricted use on certain databases
GRANT SELECT, INSERT, UPDATE, DELETE ON `database_number_1`.* TO 'new_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `database_number_2`.* TO 'new_user'@'localhost';
# To be repeated for 127.0.0.1 and ::1
CREATE USER 'new_user'@'127.0.0.1' IDENTIFIED WITH caching_sha2_password BY 'user_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT USAGE ON *.* TO 'new_user'@'127.0.0.1';
GRANT SELECT, INSERT, UPDATE, DELETE ON `database_number_1`.* TO 'new_user'@'127.0.0.1';
GRANT SELECT, INSERT, UPDATE, DELETE ON `database_number_2`.* TO 'new_user'@'127.0.0.1';
CREATE USER 'new_user'@'::1' IDENTIFIED WITH caching_sha2_password BY 'user_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT USAGE ON *.* TO 'new_user'@'::1';
GRANT SELECT, INSERT, UPDATE, DELETE ON `database_number_1`.* TO 'new_user'@'::1';
GRANT SELECT, INSERT, UPDATE, DELETE ON `database_number_2`.* TO 'new_user'@'::1';
#

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

Thank you for the answer Otomatic, but it's not that straight forward.
I do have Mysql 9.0.1 running fine but with the same interdiction to connect.
Because I have root with no password (I've always been that way since I first installed wampserver), I can never use MySQL command prompt because it asks for credentials and it doesn't accept an "empty" password for root. As soon as I hit enter in the console (with typing no password), the console screen closes and disappears.

Here is from MySQL error log:

2024-08-01T22:27:38.885780Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-08-01T22:27:39.398883Z 0 [System] [MY-010116] [Server] d:\wamp\bin\mysql\mysql9.0.1\bin\mysqld.exe (mysqld 9.0.1) starting as process 9440
2024-08-01T22:27:39.755821Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-08-01T22:27:48.718471Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-08-01T22:27:51.640089Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-08-01T22:27:51.640435Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-08-01T22:27:51.715206Z 0 [Warning] [MY-010312] [Server] The plugin 'mysql_native_password' used to authenticate user 'mysql.session'@'localhost' is not loaded. Nobody can currently login using this account.
2024-08-01T22:27:51.715848Z 0 [Warning] [MY-010312] [Server] The plugin 'mysql_native_password' used to authenticate user 'root'@'localhost' is not loaded. Nobody can currently login using this account.
2024-08-01T22:27:51.742657Z 0 [System] [MY-010931] [Server] d:\wamp\bin\mysql\mysql9.0.1\bin\mysqld.exe: ready for connections. Version: '9.0.1'  socket: ''  port: 3306  MySQL Community Server - GPL.

I am seriously stuck at no way to use mysql anymore. Please help!

Ciprian M.



Edited 1 time(s). Last edit at 08/13/2024 06:36AM by ciprianmp.

Hi,

I don't understand!

The "mysql_native_password" plugin no longer exists with MySQL 9.0+ and nothing in the MySQL 9.0.1 installation under Apache 2.4.62 and PHP 8.3.10 asks for it to be used.
As I've written before, a "fresh" installation of the MySQL 9.0.1 addon works perfectly, even accessing the console without a password.

Revert to a previous version of MySQL, e.g. 8.4.2, while you work out the problem.

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

I never uninstalled older versions, I keep one of each subversions released. The issue appeared when I switched back and forth with 8.2.0 (8.2.0 and 9.0.1 are the only versions that have the tray icon green, the other versions can't start the mysql64 service).
The issue might come from the common database folder I am using for all mysql versions.

PS: I wanted to attach a screenshot to this reply but this forum doesn't provide the advanced formatting toolbar. I remember I could attach/post urls and images. Did something change?

Ciprian M.

Hi,

> The issue might come from the common database folder I am using for all mysql versions.

The users in this common folder were originally created with "mysql_native_password" which is no longer supported with MySQL 8.4.2 or 9.0.1.

You need to recreate the users as described above.

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

Hi,

I would add that you should not use the same data folder for different MySQL branch versions, a branch being 8.4.x or 9.0.x for example.

See item :
- 9 - MySQL/MariaDB - Moving Databases - MySQL upgrade
of Wampserver 3 - Some explanations

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

I have always used the same database folder for the last 5 years, no matter the MySQL version or branch. Never had an issue.
MariaDB is a different story, I always used import/export into new versions.

I understand the issue now, but I cannot use the MySQL Console to run the user commands above. What files should I delete from the database folder to be able to run MySQL8.2.0 and get to over permissions with root/no password and to be able to export/import?

Ciprian M.



Edited 1 time(s). Last edit at 08/14/2024 11:54PM by ciprianmp.

Hi,

There is a way to access the MySQL console even if you do not know the root password or if it is not accepted.

See item 10 ofWampserver 3 - Some explanations
- 10 - MySQL/MariaDB - Re-create root user deleted by mistake
and/or Reset a forgotten root password
and/or Reassign all privileges to root

However, I would advise you to make a backup of your shared folder beforehand.

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

Perfect, Otomatic!
That did the trick, skipping grant tables, modifying the root user and then commenting out skip-grant-tables.

I can run my app and connect to MySQL with root and the new password, flawlessly.

Thank you!

Ciprian M.

Otomatic Wrote:
-------------------------------------------------------
> Hi,
>
> There is a way to access the MySQL console even if
> you do not know the root password or if it is not
> accepted.
>
> See item 10
> of[url=http://forum.wampserver.com/read.php?2,1610
> 50]Wampserver 3 - Some explanations[/url]
> - 10 - MySQL/MariaDB - Re-create root user deleted
> by mistake
> and/or Reset a forgotten root password
> and/or Reassign all privileges to root
>
> However, I would advise you to make a backup of
> your shared folder beforehand.

Hi,

Thanks for the feedback, so I can see that it works somewhere other than on my systems.

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons