I have written a windows OleDB provider for MySQL using the MariaDB connector-C framework.
I just installed a new wireless router (MediaBridge) and have enabled DHCP. I used to assign IP addresses statically, so I believe that the error I am now receiving is related to the change to DHCP from static IP address assignment.
When I am NOT connected to the network the error does NOT occur.
But now when I try to establish a MySQL connection and am connected to the network, I get an error stating that fe80::21c9:b9ab:fac3:a1de%2 is not allowed to connect to MariaDB -- That is a paraphrase of the actual language which I don't have in front of me right now.
The IPv6 address is the one that is found when issuing the command promot command, ipconfig /all called Link-local IPv6 Address.
The connection values I pass are the computer name (Windows 10), the database name and the user credentials.
Again this works when not connected to a network. The IPv4 address is valid and was obtained from the router via DHCP.
To fix this, I hacked the code to change the machine name, when it is that of the regnant computer to localhost. Obviously this is a bad hack as it won't solve the problem if I try to connect to a mysql server on another computer in the network.
Any ideas on how to get mysql to not object to this IPv6 address which is being assigned via the autoconfig feature of windows?
Thank you. Charles..
And a little later: I have more information on this problem.
If I set the options so that only IPv4 addresses are used, then I get the same error for the IPv4 address of the localhost machine.
Here's the new information: I don't get this error if I statically assign IP addresses. The error occurs only when the IP addresses are assigned using DHCP.
So MySQL returns error 1130 when the IP address of the local machine was assigned via DHCP. How odd!
So MySQL and DHCP appear to be a toxic mix...
Given that the internet is filled with people experiencing the 1130 error over DECADES, I am a bit surprised that no one has made this connection between the 1130 error and DHCP being enabled!
I am not saying that all 1130 errors are caused by DHCP, but given most networking environments use DHCP, I think it's a good bet that a high percentage of the 1130 error instances are due to the DHCP and MySQL toxicity.
As there is no reason why such a problem with DCPH and MySQL should exist after decades of users experiencing error 1130, it seems that someone (everyone) is asleep at the switch!
MySQL should work the same in both static address assignment and DHCP environments, OBVIOUSLY!!!!!
Answer Answered by Charles Cotton in this comment.
The fix to this problem is to create users with the syntax:
CREATE USER 'root'@'%' IDENTIFIED BY 'password'
Or for windows authentication accounts:
CREATE USER 'user'@'%' IDENTIFIED VIA gssapi AS 'MACHINE_NAME\\user';
----------------------- Being totally inexperienced with MySQL, I don't understand why it is necessary to create several user records for the same user. For example:
CREATE USER 'root'@'localhost' IDENTIFIED BY 'password' CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'password' CREATE USER 'root'@'HostName' IDENTIFIED BY 'password' CREATE USER 'root'@'::1 IDENTIFIED BY 'password'
Why would anyone want this when one record with the '%' host does the same think?