All Questions
2,050 questions
-1
votes
1
answer
7k
views
Stored procedure to Insert data into table with select statement and argument parameter [duplicate]
I am creating a stored procedure which will populate my table with data passed from procedure argument and from other table.
I have a table with three columns (cart_id, product, sold_quantity). Now, I ...
- 239
-1
votes
1
answer
101
views
Design database for history data
I want to create a database with history data score. For example in June 2011 the keyword had score 102. In July 2020 the same word had score 203. What would be the best database design for this. I ...
- 115
-1
votes
1
answer
724
views
Nested query is throwing Error Code: 1054 (unknown column)
SELECT
booking.batch_id,
(SELECT
COALESCE(JSON_MERGE_PRESERVE('[]',
CONCAT('[',
GROUP_CONCAT(JSON_ARRAY(v0)...
- 99
-1
votes
1
answer
78
views
AWS RDS snapshot restore from west to east
have query related to snapshot restore .
my rds snasphot are copied everday from west to east region everyday after automated daily rds backups task gets completed. However
however last one month ...
CharlesDeeZee
-1
votes
1
answer
2k
views
Match closest date from a table to a table with just dates
I am trying to feel some gaps in table with values from a previous date.
I have created a date table which looks like this:
date
2021-04-13
2021-04-14
2021-04-15
2021-04-16
2021-04-17
2021-04-...
- 101
-1
votes
1
answer
2k
views
why is mysql not using the composite index on my query?
Here is my query.
select user_id, sum(user_points.points) overallpts, username
from user
inner join user_points on user.user_id = user_points.user_id
where user_points.date >= '2020-01-01 00:00:00'
...
- 149
-1
votes
1
answer
693
views
Schedule SQL Agent Job on RDS SQL Instance to restore latest backup weekly from S3
Scenario: I want to schedule an SQL Agent job weekly on RDS SQL instance to restore database which should access the latest backup file from S3. I'm using sp msdb.dbo.rds_restore_database to restore ...
- 11
-1
votes
2
answers
403
views
RDS MySQL top CPU queries
I'd like to know if there is any way to get the top queries in my instance per CPU. I would see which query has more impact in my instance based on CPU usage.
Anyone have any idea if it possible in ...
- 121
-1
votes
1
answer
39
views
Alternative for creating Database and inside Database having schemes like Oracle in MySQL
In oracle we have database inside database we have schemes and inside schemas we have tables.
For Example consider the following scenario
I have 2 databases like Database1 and Database2.
In ...
- 41
-1
votes
1
answer
3k
views
Incomplete dumps using mysqldump
I am running Mysql server with master and slave replication. From past few days i found that backups on my master server are incomplete.
When i ran manually on master it's returning the below error
...
- 1
-1
votes
1
answer
152
views
DATE_SUB with configurable value
In MySQL you can use the DATE_SUB function to get a new date based on the values passed in. For example I can use the following to get the a date of 30 days ago from when I run the function
SELECT ...
- 1,049
-1
votes
1
answer
169
views
mysql trigger not allowing to select from another table
I have the following snippet in a trigger on a table named bugs:
SELECT
uat_reopened_stats.release_name
(uat_reopened_stats.rate_bugs + qa_reopened_stats.rate_bugs + ...
- 221
-1
votes
2
answers
529
views
Best mysql config for my server
I have dedicated server, my CPU usage is too high (80%-100%) and memory only use 9GB.
RAM 32 gb and CPU Dual Xeon E5-2670(8core and 16 thread)
I have 25000 users that use my android application
My ...
- 1
-1
votes
1
answer
64
views
Create calculated column min value in mysql 5.7
How to compute a column: calculation is to have 1 or 0
If it is minimum value then 1 should be shown else 0
-1
votes
1
answer
777
views
Allocation of disk space to new created databases in production [closed]
Our current production database has total disk space of 2 TB. (SQL Serve 2012 standard RTM version.)
Currently we have database size allocation as attached. Apart from FUNDSDB and DBAdmin, remaining ...
- 103
-1
votes
1
answer
94
views
Server settings with 36GB handling app+data
I'm setting up a server to handle a multi-site configuration on a beta application for a partner in a new business venture.
Trying to assess sane values for MySQL.
Currently I have set:
...
- 1
-1
votes
1
answer
426
views
Error while declaring procedure
When I run this query
it returns me a count of null values of the column country
(select
sum(case
when (country IS NULL) then 1
else 0 END)
as 'null_flag'
from city)
whereas ...
-1
votes
2
answers
2k
views
Amazon RDS MySQL high CPU issues
I am hoping for a bit of help or direction on how best to troubleshoot this issue. We have recently increased our application load (more processes interacting with the DB) and the DB CPU is constantly ...
- 9
-1
votes
1
answer
33
views
What are the steps involved in updating Mysql db 5.7.30 to 5.7.35
I need to patch update MySql db from 5.7.30 to 5.7.35
What are the steps involved?
Can I take mysqldump and just run yum update on below mysql packages
mysql-community-client.x86_64
mysql-community-...
- 27
-1
votes
1
answer
468
views
How to temporarily disable foreign keys in Amazon RDS Aurora PostgreSQL? [duplicate]
Hi I am using DMS to load data from physical server to AWS Aurora PostgreSQL, but I am getting error for foreign key constraint.
I want to disable using any sp / function. Could you please help me?
-1
votes
1
answer
963
views
InnoDB: Assertion failure on executing select Query - MySQL 5.7.31
I am using pt-archiver for daily archiving of tables, but while selecting data from one tables I am getting following error and it restart mysql instance
2021-07-07 13:21:17 0x7fe0dffdc700 InnoDB: ...
- 149
-1
votes
1
answer
262
views
Difference between rows of a table - MySQL 5.7
I try to decrease the rows of a table by its previous value when it exists. For the first row element, consider 0. Could you help me? Create the table below as example.
CREATE TABLE IF NOT EXISTS t1 (
...
- 117
-1
votes
1
answer
802
views
How to checkdb automatically in AWS RDS?
I'm pretty new to Amazon AWS and RDS. I have migrated my SQL Database to RDS and I have it up and running. I'm now looking to automate maintenance tasks. Anyone with experience with RDS can help me ...
- 103
-1
votes
1
answer
244
views
MySQL Master and Replica different size of files
Recently I noticed that my replica and master storage is quite different. To be exact replica takes 250GB more.
Both are dedicated servers, both are exact specs, both have exact disk drives and ...
-1
votes
1
answer
662
views
what's the best way to execute fast aggregation queries based on YEAR and MONTH filters in addition to dynamic filters
My SQL table is going to have millions of rows. Currently, with only 2M rows, my aggregation queries take anywhere between 20 seconds to a minute.
The filters in WHERE clause for aggregation queries ...
- 169
-1
votes
1
answer
628
views
-bash: PURGE: command not found
I want to delete binary log files
following are my binary log files
binary2.000001
binary2.000002
i want to delete binary2.000002
mysql> PURGE BINARY LOGS TO 'binary2.000002';
It shows me:...
-1
votes
1
answer
68
views
Can't get this query to work properly
I've been trying to get the following query working for days but I can't seem to solve my issue. I'm using the MySQL version 5.7.9
I have the following database:
CREATE TABLE user (
id INT NOT ...
- 3
-1
votes
1
answer
721
views
MYSQL Trigger: Update before insert
I want to increment the order of every video by one every time a video gets inserted so that the new video takes its place
UPDATE services SET orders = orders + 1
This doesn't work, I get this error,...
- 159
-1
votes
1
answer
208
views
Mysql Partitioning Query
i have created partitions on pricing table. below is the alter statement.
ALTER TABLE `price_tbl`
PARTITION BY HASH(man_code)
PARTITIONS 87;
one partition consists of 435510 records. total records ...
- 49
-1
votes
1
answer
52
views
Impact of changing swappiness
On one of my aws ec2 m4.10xlarge instance running Ubuntu 16.04 LTS with 160 GB memory, I want to change the swappiness from the default 60 to 10. Does changing this at runtime using
sysctl -w vm....
- 115
-1
votes
1
answer
257
views
MySQL only, Multiple queries at once
MySQL 5.7,
Core 16,
Memory 64GB
My query runs about every hour. In that query i change the table structure of 5 tables.
The question is not why i'm doing this, i know there are better ways.
No the ...
- 11
-1
votes
2
answers
1k
views
AWS KMS Encryption enable for SQL Server Columns?
Is it possible MSSQL on EC2 with a key in AWS KMS to encrypt columns
- 19
-1
votes
1
answer
365
views
Loss of 20+Gb of space and cannot find out why (amazon mysql rds)
I am trying to figure out why one of my MYSQL RDS DB instances lost 20+GB of storage space and I cannot figure out why. I have 2 RDS instances and am using some queries to compare data + free data ...
- 701
-1
votes
1
answer
2k
views
MySQL 5.7 UPDATE slow due to not using index
We have an issue similar to this. Only, the solution there won't work for us as:
We don't use multi-table JOINS, but UPDATE … WHERE `userid` IN (<list of 10k ids>);
We currently "only" have the ...
- 5
-1
votes
1
answer
150
views
MySQL Huge DELETE
I'm trying to delete 10000 rows from my database on MySQL 5.7 but it's been stuck for 7 hours.
Query:
DELETE FROM article WHERE id in (52109548,52109558,52109560,52109564,....).
This are my buffer ...
-1
votes
2
answers
2k
views
MySQL query optimization not using index
I'm running into an issue with my MySQL database and I would like to know if any of you could please shed some light.
Background:
I currently have entire schema configured with InnoDB. This database ...
-1
votes
1
answer
125
views
Mysql crashes on fresh installation
I try to run mysqld and here's the dump I get:
https://pastebin.com/TCk2YWQN
I tried re-installing mysql, but the same thing happens. Just running mysql returns ERROR 2002 (HY000): Can't connect to ...
- 161
-1
votes
1
answer
518
views
Move data chunk from table with 200+million rows to a new table MySQL
Our DB is hosted in AWS MySQL RDS. The data is such that each company that signs up gets its own set of tables which then receive enormous amount of data in them.
Currently we want to move a chunk of ...
- 1
-1
votes
1
answer
166
views
MySQL Performance Problems
Started having issues with our central production MySQL Database Server today.
We have 1 central production MySQL 5.7.11-log server which is bare metal with 32GB mem and 12 core CPU with 2 SSDs in ...
- 201
-1
votes
1
answer
1k
views
HELP - impdp from expdp - do i need to get the .dmp file to the RDS DATA_PUMP_DIR
I've been beating my head against a stubborn wall trying to get a expdp of an oracle schema (on premises db) loaded to my new Oracle RDS instance.
I saw this comment from you and it sounds exactly ...
- 1
-1
votes
1
answer
3k
views
Got error while reverting mysql 5.7 to mysql 5.6
Got error while doing mysql service restart. Googled a lot, but nothing fix my issue.
[ERROR] Fatal error: mysql.user table is damaged or in unsupported 3.20 format.
2016-04-23 11:10:38 22185 [...
- 11
-1
votes
1
answer
176
views
Connecting to RDS MySQL from command line sometimes fails -- why?
I have used RDS for many years, and MySQL for even longer, so I am very familiar with both -- yet, I am running into an odd thing that I cannot figure out. Wondering if someone can help.
I have 2 ...
-1
votes
2
answers
758
views
MySql Not Logging Slow Queries
I've set up slow query logging for MySQL 5.7:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query_log.log
long_query_time = 10
log_queries_not_using_indexes = 0
Then to test ...
- 385
-2
votes
4
answers
1k
views
Should I spread many connections over a period of time, or hammer the database at same time?
I currently have a fairly small (micro, but can upgrade if necessary) Postgres db instance on Amazon RDS. I think I have a max connections of about 85 (checked through max_connections value in console)...
- 109
-2
votes
2
answers
101
views
MySQL Upgrade Procedure in a production environment [closed]
Am new to the role and employers have asked me to come up with a plan to upgrade the MySQL 5.6 on the production server to 5.7. It has partitions and MASTER-MASTER replication setup and has around ...
- 21
-2
votes
2
answers
678
views
Why the order of indexed columns in ORDER BY affect performance?
I'm trying to improve performance of the following query which takes a 1+ minutes to execute:
SELECT *
FROM test
WHERE ( created_at < '2023-3-31 06:10:20.871' )
AND ( ( id > '2a95048f' ...
- 101
-2
votes
1
answer
45
views
Strange COUNT(*) issue
I have the following SQL statement:
SELECT COUNT(J.id) as total,
(3959 * acos (cos ( radians(J.lat) )* cos( radians( UserM.userLat ) )* cos( radians( UserM.userLng ) - radians(j.lng) )+ sin ( ...
- 29
-2
votes
1
answer
106
views
AWS TO OCI GOLDENGATE REPLICATION
I want to use oracle Goldengate to my Multi cloud setup database, I'm just wondering if Im required to down my primary database to avoid data discrepancy? or goldengate can sync the data even if my ...
-2
votes
1
answer
22
views
Can't manage to work with comparing days
In a table with persons and their birthdays, the ones whose birthday is in less than 7 days need to be displayed.
I found some answers on the Internet but they all are at least 5 years ago with ...
- 101
-4
votes
3
answers
84
views
Not able to create this table [closed]
I am trying to create this table in my database with no luck at all. All my tables look the same, but this one is giving me trouble.
I am using Notepad to type, then copying the code to the MySQL 5.7 ...
