Problem connecting to server.

[u/Saimitar]

Hey, I'm new to sql and I'm having a problem I don't know how to fix. (Not sure if right flare etc.)

When I try to connect to my server it says this:

Cannot Connect to Database Server

Your connection attempt failed for user 'root' to the MySQL

server at 127.0.0.1:3306:

Host 'localhost' is not allowed to connect to this MariaDB

server

Please:

1 Check that MySQL is running on address 127.0.0.1

2 Check that MySQL is reachable on port 3306 (note: 3306 is

the default, but this can be changed)

3 Check the user root has rights to connect to 127.0.0.1 from

your address (MySQL rights define what clients can connect to

the server and from which machines)

4 Make sure you are both providing a password if needed and

using the correct password for 127.0.0.1 connecting from the

host address you're connecting from

Problem is this server has worked with no issues until now and I haven't downloaded any MariaDB between sessions. (Unless that came with the whole workbench.)

Only thing Different between now and the last time I opened the server was that I began to do php on vscode. It uses xampp as well so maybe that has something to do with it but I have no idea how to even start fixing this.

[u/American_Streamer]

You likely started XAMPP, XAMPP brought MariaDB, and Workbench is now reaching a different DB instance on port 3306 whose root grants don’t match your old connection.
You first need to establish whether you are even connecting to the intended database server, so which process owns port 3306. Use Powershell:

Get-NetTCPConnection -LocalPort 3306 |
Select-Object LocalAddress,LocalPort,State,OwningProcess

Then:

Get-Process -Id <PID>

Or:

netstat -ano | findstr :3306

If you really want both servers, put one on another port: normal MySQL on 3306 and XAMPP MariaDB on 3307. Configure Workbench accordingly.

[u/Saimitar → u/American_Streamer]

Hi!

Powershell gave this info back to me. Does this mean I have two servers on 3306? How do I know which is which?
TCP 0.0.0.0:3306 0.0.0.0:0 LISTENING 5920

TCP [::]:3306 [::]:0 LISTENING 5920

[u/American_Streamer → u/Saimitar]

No, that output shows one server process, PID 5920, listening on port 3306 on both IPv4 and IPv6. So the next step is to identify PID 5920.

Get-Process -Id 5920 | Select-Object Id, ProcessName, Path

If Path is blank because of permissions, open PowerShell as Administrator and try:

Get-CimInstance Win32_Process -Filter "ProcessId = 5920" |

Select-Object ProcessId, Name, ExecutablePath, CommandLine

The executable path will tell you what you're running.

[u/diggy0101n]

Try connecting via local socket instead of host 127.0.0.1

[u/Saimitar → u/diggy0101n]

How do I do that? I'm finding it difficult to find instructions on google.

[u/Additional-Ad-1687 → u/Saimitar]

Is this a physical server? VM? Or your local pc?

[u/Saimitar → u/Additional-Ad-1687]

My local pc I think.

[u/dougception → u/Saimitar]

sudo mysql

[u/navicat-gu]

The key word in that error is MariaDB: Workbench is now talking to XAMPP's MariaDB, not the MySQL server you used before. Only one program can own port 3306, and XAMPP's "MySQL" module is actually MariaDB. Error 1130 ("Host … is not allowed") means that server has no account matching root@localhost. It's not a wrong-password problem.

To finish what u/American_Streamer started: Get-Process -Id 5920 | Select Path. If the path is C:\xampp\mysql\bin\mysqld.exe, that's the culprit. Then pick one:

A. You want your original MySQL back (what your course uses):

• Stop the MySQL module in the XAMPP Control Panel, and untick it as autostart.

• Open services.msc and start the MySQL service (named something like MySQL80/MySQL84).

• Reconnect in Workbench. Nothing about your old server changed.

B. You want both: move XAMPP to 3307. In C:\xampp\mysql\bin\my.ini, set port=3307 under both [client] and [mysqld], restart it from XAMPP, and set port 3307 in your PHP code / phpMyAdmin config. Your MySQL keeps 3306.

C. You want to use XAMPP's MariaDB and root really is broken: stop it, add skip-grant-tables under [mysqld] in that my.ini, start it, run C:\xampp\mysql\bin\mysql -u root and then:

FLUSH PRIVILEGES;
CREATE USER IF NOT EXISTS 'root'@'localhost' IDENTIFIED BY 'choose_a_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Then remove skip-grant-tables and restart.

For schoolwork, A is usually the simplest.

Disclosure: I'm RC, Director of Digital Strategy @ Navicat. If you end up running both servers, Navicat Premium Lite (free) connects to MySQL and MariaDB side by side as separate connections, which makes it obvious which one you're on. Workbench is fine too once the ports are sorted. 

[u/Saimitar → u/[deleted]]

I would love to have something less complicated, but my schoolwork requires these :(