- discuss - lists.mariadb.org

Re: "Access denied for user" on AWS RDS MariaDB Instance
by serg＠mariadb.org 04 Jan '24

04 Jan '24

Hi, Rhys.Campbell, First, please reply to (or cc:) the list, if you don't mind. On Jan 04, Rhys.Campbell(a)swisscom.com wrote: > Hello Sergei, > > SHOW PLUGINS; - List three authentication plugins... > > +----------------------------------+--------+--------------------+---------------+-------------+ > | Name | Status | Type | Library | License | > +----------------------------------+--------+--------------------+---------------+-------------+ > | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | > | mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | > | unix_socket | ACTIVE | AUTHENTICATION | NULL | GPL | > +----------------------------------+--------+--------------------+---------------+-------------+ Okay. It shows what your user cannot don't use. But what they do use? Can you check whether they all - or at least those that get errors - only use IDENTIFIED WITH mysql_old_password USING 'xxxxx' ? I'm not asking to send SHOW CREATE USER to the list, you can just take a look yourself and reply "yes" /Sergei > -----Ursprüngliche Nachricht----- > Von: Sergei Golubchik <serg(a)mariadb.org> > Gesendet: Donnerstag, 4. Januar 2024 14:03 > An: Campbell Rhys, B2C-TSP-SRE <Rhys.Campbell(a)swisscom.com> > Cc: discuss(a)lists.mariadb.org > Betreff: Re: [MariaDB discuss] "Access denied for user" on AWS RDS MariaDB Instance > > Hi, Rhys.Campbell, > > How is authentication configured server-side? > Just the conventional mysql_native_password plugin? > > Regards, > Sergei > Chief Architect, MariaDB Server > and security(a)mariadb.org > > On Jan 04, Rhys.Campbell via discuss wrote: > > Hello All, > > > > I have a funny issue that is bothering me on a few MariaDB > > instances. We're running 10.6.14 on AWS RDS with very little changed > > in the default configuration. We have eight instances of a vendor > > application running on EC2 Instances that use these MariaDB > > Instances. This application is deployed and configured via > > TF/Ansible and we don't really manually login to these instance to > > change or configure anything. Very occasionally I get an issue with > > logging into the database. The vendor application is occasionally > > logging the following errors... > > > > Connect failed to database (db): Access denied for user > > 'user'@'X.X.X.X' (using password: YES) - waiting for 125 seconds > > before retry Connect failed to database (db): Access denied for user > > 'user'@'X.X.X.X' (using password: YES) - waiting for 25 seconds before > > retry Connect failed to database (db): Access denied for user > > 'user'@'X.X.X.X' (using password: YES) - waiting for 5 seconds before > > retry Connect failed to database (db): Access denied for user > > 'user'@'X.X.X.X' (using password: YES) - waiting for 1 seconds before > > retry > > > > Of course I recognized these as the classic "wrong password" login > > error. There are naturally corresponding error log entries in the > > MariaDB Log... > > > > Access denied for user 'user'@'X.X.X.X' (using password: YES) > > > > The vendor is a bit dismissive of this, blaming the MariaDB > > Instance. I'm not certain that is the case. We see perhaps 1 > > instance of this per day although somedays it doesn't happen and a > > small number of days we see two instances. It does not appear to > > happen on a predictable schedule. The MariaDB Instances are working > > fine at all other times. CPU utilization is very low and we only > > serve a small number of connections. There is nothing else in the > > error log of interest. Credentials are set, via Ansible, in > > configuration files. Details are correct and 99.9% of the time the > > vendor application has no issues logging into the MariaDB Instance. > > I can't actually tie these errors to something not working... but > > we're running very low traffic at the moment, and I'm concerned > > about it becoming more of an issue later on. > > > > Two possibilities exist in my mind... > > > > > > 1. Bug in vendor application is occasionally munging the password > > and creating the error we see logged. > > 2. Something else as yet unknown, i.e. rogue cronjob configured to > > use false vendor app credentials that creates the above error. I > > don't believe this to be the case but I will not 100% exclude it as > > a possibility. > > > > Is anyone aware of any logging/auditing that I could activate on the > > MariaDB RDS Instances to get a little more information about this? > > I'm thinking about setting up an strace on the vendor process but > > want to see if there are any better options first. > > > > Cheers, > > Rhys

2 1

"Access denied for user" on AWS RDS MariaDB Instance
by Rhys.Campbell＠swisscom.com 04 Jan '24

04 Jan '24

Hello All, I have a funny issue that is bothering me on a few MariaDB instances. We're running 10.6.14 on AWS RDS with very little changed in the default configuration. We have eight instances of a vendor application running on EC2 Instances that use these MariaDB Instances. This application is deployed and configured via TF/Ansible and we don't really manually login to these instance to change or configure anything. Very occasionally I get an issue with logging into the database. The vendor application is occasionally logging the following errors... Connect failed to database (db): Access denied for user 'user'@'X.X.X.X' (using password: YES) - waiting for 125 seconds before retry Connect failed to database (db): Access denied for user 'user'@'X.X.X.X' (using password: YES) - waiting for 25 seconds before retry Connect failed to database (db): Access denied for user 'user'@'X.X.X.X' (using password: YES) - waiting for 5 seconds before retry Connect failed to database (db): Access denied for user 'user'@'X.X.X.X' (using password: YES) - waiting for 1 seconds before retry Of course I recognized these as the classic "wrong password" login error. There are naturally corresponding error log entries in the MariaDB Log... Access denied for user 'user'@'X.X.X.X' (using password: YES) The vendor is a bit dismissive of this, blaming the MariaDB Instance. I'm not certain that is the case. We see perhaps 1 instance of this per day although somedays it doesn't happen and a small number of days we see two instances. It does not appear to happen on a predictable schedule. The MariaDB Instances are working fine at all other times. CPU utilization is very low and we only serve a small number of connections. There is nothing else in the error log of interest. Credentials are set, via Ansible, in configuration files. Details are correct and 99.9% of the time the vendor application has no issues logging into the MariaDB Instance. I can't actually tie these errors to something not working... but we're running very low traffic at the moment, and I'm concerned about it becoming more of an issue later on. Two possibilities exist in my mind... 1. Bug in vendor application is occasionally munging the password and creating the error we see logged. 2. Something else as yet unknown, i.e. rogue cronjob configured to use false vendor app credentials that creates the above error. I don't believe this to be the case but I will not 100% exclude it as a possibility. Is anyone aware of any logging/auditing that I could activate on the MariaDB RDS Instances to get a little more information about this? I'm thinking about setting up an strace on the vendor process but want to see if there are any better options first. Cheers, Rhys

2 1

Creating Replica With Rsync (But It Fails)
by Michael Caplan 30 Dec '23

30 Dec '23

Hi there, I am attempting to create a replica from a MariaDB 10.3.31 master. The replicate is running MariaDB 10.3.39 on the same OS (Ubuntu 20.04) and like hardware with identical MariaDB configuration. The strategy I'm using to create the replica is one that I have done before using earlier version of MariaDB and MySQL. But this time it is failing. It roughly looks like this: 1. rsync /var/lib/mysql from master to replica 2. On master: FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; 3. On master: Shutdown 4. rerun rsync from master to replica; then restart master 5. Start mariadb on replica 6. CHANGE MASTER on replica 7. Celebrate and have a nap When starting the replica which now has all the master data rsynced over, I am getting the following error repeated over and over again: 2023-12-26 22:14:54 0 [ERROR] InnoDB: Page [page id: space=43077, page number=3] log sequence number 19989139444854 is in the future! Current system log sequence number 19246539202516. 2023-12-26 22:14:54 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. Then I get the following errors for many (but not all) tables: 2023-12-26 22:16:32 0 [ERROR] InnoDB: Expected tablespace id 3986547 but found 3986540 in the file ./ddx_practice_91804/alerts.ibd 2023-12-26 22:16:32 0 [Note] InnoDB: trying to read page [page id: space=3986547, page number=0] in the background in a non-existing or being-dropped tablespace The replica is totally useless, even though it is an exact copy, running on like hardware, same OS and similar major version of MariaDB. I've in the past used innodb_fast_shutdown = 0 on the master before final rsync, but I was told by MariaDB engineers that should not matter. I'm unclear why I'm seeing this corruption. Any ideas? Thanks, Mike Confidentiality Notice: The information contained in this electronic message and any attachments to this message are intended only for the individual(s) addressed in the message and may contain proprietary and confidential information. If you are not the intended recipient, you should not disseminate, distribute, or copy this e-mail. Please notify the sender and destroy this message. WARNING: Computer viruses can be transmitted via email. The recipient should scan this email before opening it. The company accepts no liability for any damage caused by any virus transmitted by this email.

2 2

Query Cache: Does a SELECT query tagged with SQL_NO_CACHE really touch the QC mutex?
by hellomaria-letmetrythisfirst＠mailinator.com 23 Dec '23

23 Dec '23

Hello, I found the query cache in MariaDB to be super useful and especially powerful when used in DEMAND mode. So I'd like to learn more about the performance characteristics. The documentation says that for the query cache, "the mutex is hit even for SQL_NO_CACHE": https://mariadb.com/kb/en/mariadb-memory-allocation/#query-cache Is there some more information about this somewhere? Why would a SELECT query that uses this directive dabble with the mutex at all?

2 3

Scheduled jobs recommendations
by ngtech1ltd＠gmail.com 19 Dec '23

19 Dec '23

Hey Everyone, I am currently using MariaDB 10.5.x from almalinux 9.3 and am testing latest 10.11 on Debian 12. I recently encountered an issue on 10.5 which probably didn’t run the stored procedure it should have. I have verified that the DB is configured properly ie: SET GLOBAL event_scheduler = ON; And also in the cnf files is default to ON and after a restart of the service the scheduler is on. I want to create a way to debug the stored procedure and the event scheduler. When I program a software I can log messages into somewhere but I don’t know how to do that in MariaDB and I would like to get a recommendation on how to do that this kind of logging in the when the even is being triggered and also after the call to the stored procedure ends. With this I can start to see if the event is being triggered and when. I am using it to cleanup a table and only leave the last 2 hours of data but it won’t run for at-least couple month now so I am calling the stored procedure from a cronjob. Thanks for any advice, Eliezer

2 1

Some questions restarting mariadb and importing the dumped databases
by Jogchum Reitsma 13 Dec '23

13 Dec '23

Hi all, On 3th of januari I posted the following topic, on the previous list address, maria-discuss(a)lists.launchpad.net, and gave it a reminder on the 19th of januari. I did not get an answer yet, maybe also because the list address changed in the mean time. So I take the liberty to pose the question again here, hoping this time someone is able to answer the the questions asked. Would be much appreciated! (I didn't mention it in the OP, but I've read https://mariadb.com/kb/en/mariadb-dump/, which does not seem to answer the questions) regards, Jogchum Hi, 12th of October I started the topic" Maria-db refuses to start". With the help offered in the conversation following I could access my data by manually starting an older version of Mariadb. This was on the previous list address, maria-discuss(a)lists.launchpad.net, so I then dumped my tables with mysqldump, one by one, successfully. I also dumped the mysql table. Final advice was to purge all reminiscences of the older versions, and make a fresh start by installing the version of my OS (OpenSuse Tumbleweed). In that new environment, import the dumped data. A few questions remain: - should I, after the install process of the OS version (at the moment 10.10.2-1.1) take further actions to get a fully functioning system? - should I import the dumped "mysql" table? - if yes, should I do that before or after importing the user data? Or doesn't that matter? Again, thanks for your answers! kind regards, Jogchum

2 2

10.7/10.8 removed from Debian repository: unable to upgrade Galera clusters
by William Edwards 30 Nov '23

30 Nov '23

Hi, For Debian, versions 10.7 and 10.8 were removed from the repositories. This is understandable, as they're not LTS versions, but... this makes it impossible to upgrade Galera clusters on 10.6 (LTS, supported). According to the documentation, when upgrading Galera clusters, skipping major versions is not supported. Could the project either bring these versions back, or decide that skipping major versions is supported for Galera and update the documentation? Met vriendelijke groeten, William Edwards

4 3

Text Columns encoding
by Ruben Safir 23 Nov '23

23 Nov '23

Years ago I dumped the text of emails directly into the database into a text column message | text | YES | | NULL I pulled it out and displayed it on the website with a simple select like select message from postings where id = 553671 using DBI and DBD Much of the email now ends up like binary junk like this one: http://www.mrbrklyn.com/docs/file.txt I've tried decoding it with pearl with no success. Obvously it is textwidth set someout and has \ inserted in the output. Is there a fix to this? -- So many immigrant groups have swept through our town that Brooklyn, like Atlantis, reaches mythological proportions in the mind of the world - RI Safir 1998 http://www.mrbrklyn.com DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002 http://www.nylxs.com - Leadership Development in Free Software http://www2.mrbrklyn.com/resources - Unpublished Archive http://www.coinhangout.com - coins! http://www.brooklyn-living.com Being so tracked is for FARM ANIMALS and extermination camps, but incompatible with living as a free human being. -RI Safir 2013

2 1

Redo log and tablespace flushing
by Gordan Bobic 20 Nov '23

20 Nov '23

I have a question that may seem somewhat obscure, but what I really want to know is how the disk flushing and crash safety work. Do tablespace commits get explicitly flushed during normal runtime operation? If we have a write that successfully commits to the redo log and to the binlog, but the tablespace loses, say, 5 seconds worth of commits in an unclean shutdown, would crash recovery deal with it? Is replaying the redo log followed by binlog based recovery sufficient to put the tablespace(s) into a consistent state even if the redo+binary logs are in terms of on-disk state a few seconds ahead of the tablespaces? On other words, provided that write ordering is preserved (ordering as guided by flush calls), can I do the equivalent of LD_PRELOAD=libeatmydata on the tablespace operations safely as long as the redo and binary logs are fsync()-ed reliably?

3 10

centos7 mariadb5.5 -> MariaDB10.3
by Marc 17 Nov '23

17 Nov '23

Ever since I switched from the default included centos mariadb to your repo. The web applications are getting ~1 once a week connection refused or so. I have already scheduled a cron job to restart your server. But still that is not enough. In the previous 10 years or so I never had such issue nor do I have such issues with postgresql. Nothing is in the log file, only the restart. Should such things not be logged by default? How to configure that issues resulting in refusing access or being unable to give access are logged?

2 7