All Questions
2,050 questions
0
votes
1
answer
17
views
Unable to create root@localhost mysql 5.7 or 8.0.31
I have received a mysql environment to work on and currently Audits are being performed.
What I see in mysql.user is 'root'@'%' created everywhere and it is required that access to root is to be ...
- 226
0
votes
2
answers
32
views
Disable MySQL ONLY_FULL_GROUP_BY while logging violations
We have attempted to upgrade a legacy database from MySQL 5.6 to 5.7 in the hopes of bringing it up to current, but quickly experienced queries that failed with this is incompatible with sql_mode=...
- 195
0
votes
0
answers
26
views
Switch to master auto position from master_log_file and master_log_pos MySQL 5.7.44
MySQL Server - 5.7.44
I have a master and three slaves running on MySQL 5.7.44.
The GTID is enabled and I am using gtid_enforce_consistency.
I took a backup from a slave and built a fourth slave using ...
- 226
0
votes
1
answer
32
views
Where can I find more detailed MySQL deadlock logs?
We have a MySQL database in AWS RDS that was migrated recently from 5.7 to 8. One of our microservices running in ECS executes bootstrapping code at launch to iterate through a large list of table ...
GS-Scooter
0
votes
0
answers
11
views
Can you downgrade provisioned iops on gp3 for postgresql RDS?
Doing an occasional analysis on a postgreSQL RDS instance with gp3 storage.
Running out of provisioned IOPS.
Is it possible to temporarily raise the provisioned IOPS limit? e.g. just for a day or ...
- 347
0
votes
0
answers
109
views
How can I install MySQL 5.7 on Ubuntu 24.04? [closed]
The company I am working uses an older stack, and I need to install MySQL 5.7 on my Ubuntu 24.04 to work on some projects locally. Does anyone know how to do this? I just can't find anything besides ...
0
votes
0
answers
15
views
Postrgresql Logical replication causing very high buildup of log files even though replication slots are active [ AWS RDS postgresql 16.1]
I have two rds instances and have set up logical replication between them [ publisher subscriber]. for the 3-4 days everything was working fine wal files were getting cleared but suddenly when the ...
- 1
0
votes
0
answers
17
views
Using pt-osc(online schema change) in Multi-Master(Active/Passive) environment
MySQL version : Percona Server for MySQL 5.7.40-43-log
We have Master-Master(Active-Passive) Replication setup in our cluster. Only one Master(Active) is receiving the requests for writes while other ...
- 21
0
votes
1
answer
31
views
Amazon Aurora RDS - invalid undo record
Context
Aurora RDS, MySQL 8.0.mysql_aurora.3.05.2
Problem
The writer instance is constantly cycling. In the AWS RDS console, under Logs & events for the writer, I see
Attempting to restart the ...
- 121
0
votes
1
answer
41
views
Setting Synchronous_commit to off in Postgres in AWS RDS
0
We are experiencing significant delays in our PostgreSQL database due to LWLock:WALWrite and LWLock:WALSync waits, which are causing commits to pile up.
To address this, we have undertaken several ...
- 1
0
votes
0
answers
20
views
pt-archiver lost rows when cancelled in the middle and reinitiated
Lost some 430 rows from the original table.
I was archiving 1 year old data (1st Apr 2022 to 31st Mar 2023) from one of my servers, (Server version: 5.7.44-log MySQL Community Server (GPL)) using pt-...
- 226
0
votes
2
answers
21
views
MySQL UPDATE IGNORE opposite?
I would like to update some records. There's a chance it'll create a UNIQUE key error. In this case I would like the update to win over the previous record. How would I do this?
- 427
0
votes
1
answer
26
views
Analyzing sql query performance issues in mysql
I am new to mysql, I have few questions in regards to query performance tuning. (Note- its version 8.0.32)
I understand we use "explain analyze" to see the execution plan and it shows the ...
- 11
0
votes
0
answers
22
views
Granting Permissions To as User in Mysql
I created user-a that has the following grant privileges
SHOW GRANTS FOR 'user-a'@'%';
'GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, ...
0
votes
0
answers
15
views
MySQL 8.0 connecting to AWS RDS MySQL 5.7 with SSL/TLS CA authentication fails weirdly (Error code MY-002026)
When trying to establish TLS encrypted replication between a replication source living in AWS RDS on version 5.7.44, and a replica living in a VM on-premise on version 8.0.39, I keep getting an error:
...
- 1
0
votes
0
answers
94
views
How does RDS Postgres OldestReplicationSlotLag metric correlate to replication slot Log Sequence Number lag?
We run a Postgres 14.9 database via RDS Aurora Serverless. We have a logical replication slot consumed by a service (Fivetran) every 15 minutes.
We noticed this behavior where the reported ...
- 51
0
votes
1
answer
32
views
Mysql : how to join 2 row different colum using same table?
I try to get output from 2 row in single row for attendance report, but i dont know how. please help me
Table : attendance
no
id
punch_in
punch_out
1
1
2024-10-04 08:00
2
2
2024-10-05 08:10
3
2
...
- 1
1
vote
1
answer
48
views
Collation difference in PostgreSQL on AWS RDS and local machine
I have an issue with collation in PostgreSQL when sorting a result set. We use AWS RDS PostgreSQL, and we want to sort query results in a specific way. Locally, everything works perfectly on my laptop ...
- 119
2
votes
1
answer
24
views
Primary Key data type suggestion for MySQL for an application that depends in a external system. unsigned bigint vs varchar data type in primary keys
I'll give you some context and then ask my question.
I'm building a database model for an ecommerce application. This application needs to communicate to another system (ERP system) to get product, ...
0
votes
0
answers
41
views
How can I tell if SQL Server is RDS, RDS Custom, or installed manually on a VM (EC2)?
I only have direct access to the SQL Server. How can I check if it's RDS, RDS Custom (which allow to connect to the VM), or installed manually on a VM (EC2)?
- 801
0
votes
1
answer
85
views
How can high severity alerts be configured in Amazon RDS SQL Server?
Per the RDS documentation,
SQL Server Agent alerts and operators aren't supported.
But on any sensible SQL Server, you really want these for the high severity alerts.
So, I'm here to ask: How can ...
- 801
1
vote
0
answers
45
views
How can I improve the performance of a "cold" (first) run of this seemingly simple query?
I'm working on a specific query which has been slowing down an application for some time. The table in question has about 55 million rows.
A "cold" (first) run of this query usually takes ...
- 193
0
votes
2
answers
64
views
How can I safely write an UPDATE statement to update missing values from TABLE2?
I am in the middle of fix some bad data and I am looking a safer way to update a given column when the value does not exists anymore in it is related table.
Take a look to the following example (...
- 1,888
0
votes
1
answer
153
views
RDS MySQL not using NVMe instance storage - no benefit of "Optimised Reads"
I am load testing various RDS instances trying to optimise db price/performance
I had seen this doc https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-optimized-reads.html and it sounded like ...
- 528
0
votes
0
answers
22
views
RDS Mysql replica cdc performance
I have a RDS Mysql which has high read/write volume. ~10k IOPS on an average and peaks up to 16k IOPS.
I have another RDS mysql replica of the main. We are using this replica to capture cdc logs and ...
0
votes
0
answers
46
views
How can I find what's eating so much space in Postgres in AWS RDS Aurora?
Essentially when I run
SELECT
pg_size_pretty(SUM(pg_total_relation_size(relid))) as total_size,
pg_size_pretty(SUM(pg_indexes_size(relid))) as index_size,
pg_size_pretty(SUM(pg_relation_size(...
- 101
0
votes
0
answers
192
views
How to grant SYSTEM_VARIABLES_ADMIN permission to master MySQL user or rds_superuser_role from MySQL Workbench hosted on RDS?
Using MySQL 8.0.39 on an AWS RDS insteance, for troubleshooting purposes we want to be able to Enable/Disable the global general_log setting , being able to turn it on and off easily and quickly from ...
- 9
1
vote
2
answers
62
views
Does regular truncate & insert hurt performance?
We have a large (5 TB) PostgreSQL 15 RDS that acts as a data warehouse, data comes in through regular pipelines and then ~20 users run analytic queries on it.
Our infrastructure people have access to ...
- 559
0
votes
0
answers
34
views
Best way to aggregate data while applying filters
In many RESTful services that I have built, I often find the need to return a resource with nested resources while applying filters. These are typically retrieved in JSON format.
I have created a ...
- 9
2
votes
1
answer
68
views
MySQL 5.7: Is it relevant/risk to have external hosts in the mysql.Hosts column if "skip-networking" is enabled?
NOTE: I am well aware our MySQL is out of date and I am pushing on this but we can't update at this time.
A couple of small questions I can't find clear responses to:
I have found that our MySQL v5....
- 247
0
votes
0
answers
14
views
Understanding MySQL's Deadlock for un-related tables
I see the following Deadlock in my show engine innodb status's output:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-08-22 09:35:46 0x7f70f2d2d700
*** (1) TRANSACTION:...
- 21
0
votes
0
answers
11
views
How to see number of storage volumes on AWS RDS MySQL?
According to https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html, for "400–65,536 GiB" storage MySQL RDS will use "Number of volumes provisioned = 4".
I have ...
1
vote
2
answers
101
views
How to work around "row is too big" for role definitions without recreating the database?
I am running into the row is too big (ERROR: 54000: row is too big: size 9976, maximum size 8160) error in Postgres related to role permissions on a couple of my instances. I understand that after ...
- 11
1
vote
1
answer
32
views
Why free storage space dip on MySQL replica?
I have AWS RDS MySQL 8.0.36 primary-replica setup. I see this storage free space dip on the replica only. Any ideas why it happened?
2
votes
0
answers
76
views
AWS RDS PostgreSQL -
We have an AWS RDS PostgreSQL (v14.10) instance running on a db.t3.micro instance. Randomly throughout the day (some days more than others), our .NET Core application starts failing with the following ...
- 121
8
votes
2
answers
1k
views
Is an invalid date considered the same as a NULL value?
I am using MySQL 5.7 and I have notice something which I cannot explain to myself with my current knowledge so here it goes.
Is an "invalid" date 0000-00-00 considered same as NULL for a ...
- 1,888
0
votes
0
answers
31
views
MSSQL on AWS: remote user unable to establish connection via SSMS
Newly created MSSQL 2017 SE instance, using SQL authentication. I am able to connect to it with no issues via my corporate VPN. Overseas user on VPN is unable to connect, getting this error from SSMS:
...
- 143
0
votes
0
answers
41
views
How can I avoid downtime for AWS RDS SSL/TLS certificate rotation?
I have some production databases in RDS for which I have active connections and writes every minute. The current server certificates are expiring on 22nd August. I tried to update one, it takes 5 ...
- 101
0
votes
0
answers
23
views
Failed to prune sessions: no pg_hba.conf entry for host, although connection works fine
I am using an AWS RDS Postgres database and Typeorm to connect to the database. I have also configured the RDS database to require a SSL connection. I pass the CA certificate to the database like this:...
- 101
1
vote
1
answer
37
views
Decrease high response time when reading large table in MySQL
I have a table with around 80~ million rows called parts.
I have a simple query like this
SELECT line_code, part_number, compressed_part_number, ...[Others columns]
FROM parts
WHERE (@partNumber = ...
- 111
0
votes
1
answer
71
views
How to run a query against AWS RDS "locally"?
Our company has an RDS instance of a Postgres database in AWS. I don't have much experience with AWS.
I'd like to run a very lengthy query against the database (VACUUM FULL) that may take many hours ...
- 4,690
0
votes
2
answers
239
views
How to down size the storage of AWS RDS MySQL instance?
I have an RDS MySQL instance db.m5.4xlarge with 8.3TB of data, and I want to shrink it to 4TB to save costs because it actually has 4TB of data. What are the viable options?
I've tried several methods,...
- 11
0
votes
0
answers
53
views
Intermittent AWS RDS Connection Spikes Causing Production DB Outages
I'm new to AWS, and we're using a db.r7g.2xlarge instance running MySQL with default configurations. Typically, CPU usage and database connections are normal until "something" causes the DB ...
- 101
2
votes
1
answer
115
views
How can I get all results concatenated from a column group by UID?
I need to build a SQL query, for MySQL 5.7, if it is possible, to "GROUP BY" uid and title and concat the permissions column and separate with a |? I am saying GROUP BY but I do not know if ...
- 1,888
0
votes
1
answer
63
views
AWS RDS taking snapshots outside the backup window
I administer an AWS RDS SQL Server instance, with a backup window of 22:00-22:30 UTC and a retention period of 35 days.
I have noticed some peculiarities:
There are 55 automatic snapshots currently ...
- 101
0
votes
0
answers
62
views
I have created a blue green deployment for my AWS RDS MySQL Database as I need to update the version. But I can't connect to the green instance?
I have an old MySQL database on AWS RDS that I need to upgrade to a newer version. I have created a blue green deployment, in order to test out the upgraded version of MySQL before switching over. But ...
- 1
0
votes
0
answers
89
views
When to provision higher iops or throughput on gp3
We have an AWS PostgreSQL RDS instance. Not a lot of users, high loads in the overnight batch jobs.
Current instance type is m6g.large (2CPU 8GiB RAM) with 1300 GB Storage on gp3 SSD with 12000 IOPS ...
- 347
0
votes
1
answer
191
views
high cpu spikes on postgresql (rds)
I have a PG 15(.5) RDS instance, which is experiencing intermittent random(?) periods of high cpu; that do not seem to, afaict, correlate with an obvious cause:
Here is one example. The queries being ...
- 101
0
votes
1
answer
132
views
Removing partitions from MySQL table
I have table containing 3.2TB data in partition named default_partition, & all writes/read goes to this partition, as we have defined partitions on table like below :
PARTITION BY RANGE (to_days(...
- 101
0
votes
0
answers
27
views
Accessing RDS transaction logs
Greetings to everyone from a newcomer to this forum!
There is an RDS MySQL 5.7 database the transaction logs of which I would like to inspect: more specifically, upon attempting to add foreign keys to ...
- 101