This time, no more 'Auth Error, Down' but all nodes 'Down'. Actually all nodes were up and running. What's wrong? Here attached are my.cnf, server.cnf and maxscale.cnf configuration files from all the nodes for your reference.
If 'user'@'%' (either user=maxscale or user=monitor) is no longer correct, may you update the documentation on web and maxscale.cnf ? Or if there is bug, may you help to fix so 'user'@'%' alone will work again?
Test Jetco
added a comment - If 'user'@'%' (either user=maxscale or user=monitor) is no longer correct, may you update the documentation on web and maxscale.cnf ? Or if there is bug, may you help to fix so 'user'@'%' alone will work again?
Ah, I think I figured out why it fails. I didn't see that you had an anonymous user for the ekycdb1 host:
root
localhost
*698D9A9B433F616F4C603D16C6BFB85B89D05A06
mysql
localhost
invalid
localhost
ekycdb1
mariadb.sys
localhost
monitor
ekycdb1
*6691484EA6B50DDDE1926A220DA01FA9E575C18A
maxscale
ekycdb1
*6691484EA6B50DDDE1926A220DA01FA9E575C18A
monitor
ekycdb2
*6691484EA6B50DDDE1926A220DA01FA9E575C18A
maxscale
ekycdb2
*6691484EA6B50DDDE1926A220DA01FA9E575C18A
monitor
ekycdb3
*6691484EA6B50DDDE1926A220DA01FA9E575C18A
maxscale
ekycdb3
*6691484EA6B50DDDE1926A220DA01FA9E575C18A
Since connections from this host will match the anonymous user first before matching the wildcard user, it ended up causing authentication failures from that host. For whatever reason, all hosts had an anonymous user for that host instead of their local hosts. Once you drop that ''@'ekycdb1' user from all nodes, you should be able to use a wildcard user again. Normally you don't have anonymous users in production environments as they are dropped by the mysql_secure_installation command.
markus makela
added a comment - Ah, I think I figured out why it fails. I didn't see that you had an anonymous user for the ekycdb1 host:
root
localhost
*698D9A9B433F616F4C603D16C6BFB85B89D05A06
mysql
localhost
invalid
localhost
ekycdb1
mariadb.sys
localhost
monitor
ekycdb1
*6691484EA6B50DDDE1926A220DA01FA9E575C18A
maxscale
ekycdb1
*6691484EA6B50DDDE1926A220DA01FA9E575C18A
monitor
ekycdb2
*6691484EA6B50DDDE1926A220DA01FA9E575C18A
maxscale
ekycdb2
*6691484EA6B50DDDE1926A220DA01FA9E575C18A
monitor
ekycdb3
*6691484EA6B50DDDE1926A220DA01FA9E575C18A
maxscale
ekycdb3
*6691484EA6B50DDDE1926A220DA01FA9E575C18A
Since connections from this host will match the anonymous user first before matching the wildcard user, it ended up causing authentication failures from that host. For whatever reason, all hosts had an anonymous user for that host instead of their local hosts. Once you drop that ''@'ekycdb1' user from all nodes, you should be able to use a wildcard user again. Normally you don't have anonymous users in production environments as they are dropped by the mysql_secure_installation command.
It depends on you whether it is better to document this on maxscale.cnf or other documentation relating to maxscale. Please close this case. Thank you.
Test Jetco
added a comment - It depends on you whether it is better to document this on maxscale.cnf or other documentation relating to maxscale. Please close this case. Thank you.
People
markus makela
Test Jetco
Votes:
0Vote for this issue
Watchers:
2Start watching this issue
Dates
Created:
Updated:
Resolved:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
{"report":{"fcp":1368.5,"ttfb":463.3999996185303,"pageVisibility":"visible","entityId":117040,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"0b8379e6-2a6a-4899-bb60-84fd6aa29968","navigationType":0,"readyForUser":1496.8999996185303,"redirectCount":0,"resourceLoadedEnd":1109.5,"resourceLoadedStart":498.0999994277954,"resourceTiming":[{"duration":161.10000038146973,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":498.0999994277954,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":498.0999994277954,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":659.1999998092651,"responseStart":0,"secureConnectionStart":0},{"duration":161.10000038146973,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":498.3999996185303,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":498.3999996185303,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":659.5,"responseStart":0,"secureConnectionStart":0},{"duration":358.0999994277954,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":498.5,"connectEnd":498.5,"connectStart":498.5,"domainLookupEnd":498.5,"domainLookupStart":498.5,"fetchStart":498.5,"redirectEnd":0,"redirectStart":0,"requestStart":664.5999994277954,"responseEnd":856.5999994277954,"responseStart":677,"secureConnectionStart":498.5},{"duration":498.4000005722046,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true&whisper-enabled=true","startTime":498.79999923706055,"connectEnd":498.79999923706055,"connectStart":498.79999923706055,"domainLookupEnd":498.79999923706055,"domainLookupStart":498.79999923706055,"fetchStart":498.79999923706055,"redirectEnd":0,"redirectStart":0,"requestStart":664.6999998092651,"responseEnd":997.1999998092651,"responseStart":679.7999992370605,"secureConnectionStart":498.79999923706055},{"duration":179.29999923706055,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":499,"connectEnd":499,"connectStart":499,"domainLookupEnd":499,"domainLookupStart":499,"fetchStart":499,"redirectEnd":0,"redirectStart":0,"requestStart":665.0999994277954,"responseEnd":678.2999992370605,"responseStart":676.0999994277954,"secureConnectionStart":499},{"duration":181.80000019073486,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":499.19999980926514,"connectEnd":499.19999980926514,"connectStart":499.19999980926514,"domainLookupEnd":499.19999980926514,"domainLookupStart":499.19999980926514,"fetchStart":499.19999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":665.6999998092651,"responseEnd":681,"responseStart":679.1999998092651,"secureConnectionStart":499.19999980926514},{"duration":184.60000038146973,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":499.3999996185303,"connectEnd":499.3999996185303,"connectStart":499.3999996185303,"domainLookupEnd":499.3999996185303,"domainLookupStart":499.3999996185303,"fetchStart":499.3999996185303,"redirectEnd":0,"redirectStart":0,"requestStart":666,"responseEnd":684,"responseStart":682.7999992370605,"secureConnectionStart":499.3999996185303},{"duration":166.30000019073486,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":499.5999994277954,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":499.5999994277954,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":665.8999996185303,"responseStart":0,"secureConnectionStart":0},{"duration":185.70000076293945,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":499.79999923706055,"connectEnd":499.79999923706055,"connectStart":499.79999923706055,"domainLookupEnd":499.79999923706055,"domainLookupStart":499.79999923706055,"fetchStart":499.79999923706055,"redirectEnd":0,"redirectStart":0,"requestStart":668.5999994277954,"responseEnd":685.5,"responseStart":684.5,"secureConnectionStart":499.79999923706055},{"duration":168.5999994277954,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":500,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":500,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":668.5999994277954,"responseStart":0,"secureConnectionStart":0},{"duration":192.19999980926514,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":500.0999994277954,"connectEnd":500.0999994277954,"connectStart":500.0999994277954,"domainLookupEnd":500.0999994277954,"domainLookupStart":500.0999994277954,"fetchStart":500.0999994277954,"redirectEnd":0,"redirectStart":0,"requestStart":675.3999996185303,"responseEnd":692.2999992370605,"responseStart":691,"secureConnectionStart":500.0999994277954},{"duration":585,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":524,"connectEnd":524,"connectStart":524,"domainLookupEnd":524,"domainLookupStart":524,"fetchStart":524,"redirectEnd":0,"redirectStart":0,"requestStart":1096.3999996185303,"responseEnd":1109,"responseStart":1107.1999998092651,"secureConnectionStart":524},{"duration":539.2000007629395,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":570.2999992370605,"connectEnd":570.2999992370605,"connectStart":570.2999992370605,"domainLookupEnd":570.2999992370605,"domainLookupStart":570.2999992370605,"fetchStart":570.2999992370605,"redirectEnd":0,"redirectStart":0,"requestStart":1096.5999994277954,"responseEnd":1109.5,"responseStart":1107.6999998092651,"secureConnectionStart":570.2999992370605},{"duration":113.69999980926514,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1019.1999998092651,"connectEnd":1019.1999998092651,"connectStart":1019.1999998092651,"domainLookupEnd":1019.1999998092651,"domainLookupStart":1019.1999998092651,"fetchStart":1019.1999998092651,"redirectEnd":0,"redirectStart":0,"requestStart":1099.7999992370605,"responseEnd":1132.8999996185303,"responseStart":1132.1999998092651,"secureConnectionStart":1019.1999998092651},{"duration":192.69999980926514,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1261.1999998092651,"connectEnd":1261.1999998092651,"connectStart":1261.1999998092651,"domainLookupEnd":1261.1999998092651,"domainLookupStart":1261.1999998092651,"fetchStart":1261.1999998092651,"redirectEnd":0,"redirectStart":0,"requestStart":1418.1999998092651,"responseEnd":1453.8999996185303,"responseStart":1452.8999996185303,"secureConnectionStart":1261.1999998092651}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":262,"responseStart":464,"responseEnd":564,"domLoading":480,"domInteractive":1599,"domContentLoadedEventStart":1599,"domContentLoadedEventEnd":1655,"domComplete":2109,"loadEventStart":2109,"loadEventEnd":2110,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1566.7999992370605},{"name":"bigPipe.sidebar-id.end","time":1567.5999994277954},{"name":"bigPipe.activity-panel-pipe-id.start","time":1567.7999992370605},{"name":"bigPipe.activity-panel-pipe-id.end","time":1570.3999996185303},{"name":"activityTabFullyLoaded","time":1684.5}],"measures":[],"correlationId":"24fbb8e3fb6d71","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":109,"dbReadsTimeInMs":10,"dbConnsTimeInMs":17,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
I dropped the db user, 'maxscale'@'%' and created the following db users with appropriate rights granted respectively:
node 1
-------
'maxscale'@'ekycdb1'
node 2
-------
'maxscale'@'ekycdb2'
node 3
-------
'maxscale'@'ekycdb3'
Final user table was as follows:
MariaDB [mysql]> select user,host,password from user;
------------
---------------------------------------------------------------
---------------------------------------------------------------
---------------------------------------------------11 rows in set (0.001 sec)
I stopped/started maxscale on all the nodes again. No more 'access denial' messages on mariadb.err. Maxscale status was correct on all the nodes.
=========
Node 1/2/3
=========
> maxctrl list servers;
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴────────────────┘
> maxctrl list services;
┌────────────────────┬────────────────┬─────────────┬───────────────────┬───────────────────────────┐
│ Service │ Router │ Connections │ Total Connections │ Targets │
├────────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────┤
│ Read-Write-Service │ readwritesplit │ 0 │ 0 │ server1, server2, server3 │
└────────────────────┴────────────────┴─────────────┴───────────────────┴───────────────────────────┘
> maxctrl list listeners Read-Write-Service
┌─────────────────────┬──────┬─────────┬─────────┬────────────────────┐
│ Name │ Port │ Host │ State │ Service │
├─────────────────────┼──────┼─────────┼─────────┼────────────────────┤
│ Read-Write-Listener │ 4006 │ 0.0.0.0 │ Running │ Read-Write-Service │
└─────────────────────┴──────┴─────────┴─────────┴────────────────────┘