All Questions
101,914
questions
0
votes
1
answer
27
views
Possible options for SQL and NoSQL combination for social media data
I have searched this forum for an answer but could not find anything relevant. We are designing database schemas for a social media app with posts, groups, users and chats etc and it looks like we ...
- 101
0
votes
0
answers
42
views
how to solve the issue of missing datafile and tablespace
how to solve the issue datafile mistakenly deleted something
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/prod/system01.dbf'
...
Dilip Kumar
1
vote
1
answer
7
views
OrientDB won't start after upgrade. Cannot create user guest: role guest does not exist
When upgrading Orient DB server from 3.0 to 3.2, the server won't start after running ./server.sh due the following error:
com.orientechnologies.orient.core.exception.OCommandExecutionException: ...
jamie
0
votes
0
answers
19
views
Restoring partitioned table data causes error in PostgreSQL
I have partitioned tables and dumped it with pg_dump
pg_dump \
--dbname "${DBNAME}" \
--table 'log_*' \
--format "directory" \
--file ./backup \
--jobs "${...
- 101
0
votes
0
answers
13
views
aurora mysql index creation challenges on large tables
I have a table of yearly data [size 500GB = ~1 billion rows] in aurora mysql 3.02.2. requirement is to migrate this table to another aurora mysql [PROD] using DMS. we initiated the DMS task with data ...
0
votes
0
answers
16
views
SSMS Syntax Error Highlight
In SSMS, a syntax error is highlighted with a red squiggly line. So is the Invalid Object Name error. In my mind, they’re two different things — the second can sometimes be rectified with Refresh ...
- 2,759
1
vote
2
answers
79
views
coalesce more than 1 column in sql server
I have the following query where I search 2 tables for the item price
select productSerial, productName
coalesce(
(select top 1 price
from productPrice pp
where pp.id = wi.id
order by pp.date),
(...
- 119
0
votes
1
answer
36
views
How do you correlate SQL Database Audit spec's log column transaction_id with a LSN? from trans log or CDC capture table?
How do you correlate SQL Database Audit log column "transaction_id" with a LSN either from the trans log or a CDC capture table?
I am trying to do this because cdc only captures the data ...
- 1
0
votes
1
answer
13
views
How do I find missing sub-partitions of an index to make main index complete?
I have a table with many partitions that I wanted to add a new index to. Following the instructions from Postgres I created the new index on the main table using ON ONLY and then proceeded to create ...
- 111
0
votes
0
answers
14
views
Failing to connect PySpark Session to Snowflake. DATA SOUCE NOT FOUND error
I am running this code on jupyter notebook through Ubuntu. I'm pretty sure the main issue is probably a compatibility with the JAR files but I am not sure.
spark = SparkSession.builder \
....
0
votes
1
answer
48
views
SQL Server Extended Event Session for Attention is missing field sql_text
I have an extended event session set up to track Attention events, to monitor procedures that timed out. This is the create script:
CREATE EVENT SESSION [ApplicationTimeout3] ON SERVER
ADD EVENT ...
- 191
0
votes
2
answers
64
views
What is a difference in failover time for SQL Server FCI when database is in Full vs Simple recovery mode
I have 2-node SQL Server 2019 FCI cluster, which takes up to 4 minutes to failover from one node to another.
The cluster has 3 user databases, migrated from the older SQL server versions:
2 databases ...
- 103
0
votes
0
answers
20
views
Which query reproduces \dconfig+ command output in postgresql? [duplicate]
Which query reproduce the exact output of \dconfig+? (List of non-default configuration parameters)
I've tried
SELECT name, setting, source, sourcefile,pending_restart
FROM pg_settings WHERE source !=...
- 630
2
votes
1
answer
51
views
How to locate a procedure in the body of a trigger
I'd like to perform a validation by capturing all my triggers that call procedures.To do the search, I'm looking for the words "Call" and "Execute" in the trigger body
-- I'm using ...
0
votes
0
answers
44
views
Reporting only copy for a production database
I need a read-only copy of my production SQL Database so our reporting department can query it and not bring down production because of it being so resource intensive. Would replication still be the ...
- 427
1
vote
0
answers
25
views
Advice on postgreSQL table logging
I am thinking I can turn off logging for certain tables in my PostgreSQL analytic data warehouse. There are a core set of very large tables (between 50 and 500Gb) that are completely rebuilt at the ...
- 21
1
vote
1
answer
55
views
What indexes should I put on this table, given this illustration of its contents and the query I will be running on it?
I am working with a table (which I control) that is being populated by another application (which I don't control), each time a record of a particular type in an external system is created or updated. ...
1
vote
1
answer
43
views
DB2 Parallel Insert
I have something like this to insert via application into DB2 LUW,
insert into MY_TABLE (CREATION_DATE, MESSAGE, IDENTIFIER) values
(CURRENT_TIMESTAMP, '0', '1b0d7cfb-66ee-4372-913b-2283c6e3f0b5')
,(...
- 11
1
vote
1
answer
45
views
Postgres sorting order not respecting default C collation?
I have a postgres DB running in an AL2023 docker image:
postgres=# select version();
select version();
version ...
- 133
0
votes
0
answers
38
views
Balancing Legacy CRM Needs and Security in Multiple Databases
I have a set of databases on a server.
Database_alpha
Database_beta
Database_gamma
Database_delta
Database_alpha houses a legacy CRM system which has an inbuilt dependency on the sa account to ...
- 129
0
votes
1
answer
51
views
What could create block in that case when 'blocking' procedure/spid not do any select / almost nothing?
I had situation like this today- sorry that this is screenshot but it is easier way to show what i need to understand
As on screen - many processes was blocked by SPID 91
This SPID 91 querry was as ...
- 101
2
votes
2
answers
152
views
Understanding the Impact of ORDER BY in Window Functions on other Partitions in PostgreSQL
I have a simple table odd_nums with columns num and odd:
create table odd_nums
(
num numeric,
odd numeric
);
INSERT INTO odd_nums VALUES (0, 0);
INSERT INTO odd_nums VALUES (1, 1);
INSERT ...
- 23
0
votes
0
answers
35
views
Mounting mysql /var/lib/mysql on Azure File Share
With Azure Container Apps, I'm trying to mount mysql on Azure file share, and the container shows running status but as soon as I enable ingress on TCP 3306, the container fails.
InnoDB ...
user1930027
2
votes
2
answers
602
views
Why are these columns specified in the include clause of an index rather than as key columns
For the below query, I'm trying to figure out why the index suggested by SQL Server on the sysjobhistory table, which is also the index that results in a seek, was created on the Job_Id column with ...
- 47
0
votes
0
answers
12
views
on-prem sybase to aws aurora
I want to migrate from on-Prem Sybase to AWS Aurora Postgres database. I came across tools like AWS SCT and AWS DMS, but I am not sure , if the source Sybase database should be in AWS cloud as a pre-...
0
votes
0
answers
24
views
sqlserver alwayson audit setting
I am running SQLSERVER ALWAYS ON 2019 ENT VERSION.
I'm trying to STOCK an audit at the database level in SQLSERVER.
In the master, it was confirmed that the log audit was left at DATABASE LEVEL.
...
dante
1
vote
2
answers
53
views
How to configure the location of the Oracle password file?
When I start an Oracle instance with sqlplus the paths to the datafiles are stored in the control files, the control file name is stored in the control_files initialization parameter. The ...
- 7,664
0
votes
0
answers
24
views
Write SQL query to create output dataset with quarterly exchange rate based on input table with exchange rate by monthly
from_currency month year exchange_rate
GBP 1 2023 0.7952
GBP 2 2023 0.7738
GBP 3 2023 0.7934
GBP 1 2024 0....
- 1
0
votes
1
answer
47
views
How should I design my databas for online voting web app?
Currently I'm trying to build web app for online voting where registered user can create their own Election.
For the database, every time user create an election should the system create new required ...
- 1
0
votes
2
answers
55
views
how sorting nullable number and ones is last
my list something like this
Id
Value
ِDate
1
null
2020
2
0
2022
3
1
2021
4
1
2023
5
100
2024
2
250
2018
I want to sort by column [Value] so that ones are at the end
The result should be as ...
- 11
2
votes
1
answer
18
views
Using pg_repack to remove OIDs from tables in PostgreSQL
I'm prepping a legacy PostgreSQL 9.5 database for upgrade.
I need to remove OIDs from tables without locking the tables for long periods of time. I have developed a strategy that seems to work, but I ...
- 123
0
votes
1
answer
15
views
MySQL Drop Federated Table When Remote Server is Down
We have a production database (MySQL 8.0) that has a federated table in it. The remote server had an IP address update, and whenever a client opens this DB, it takes forever (~5 minutes) since table ...
- 101
0
votes
0
answers
31
views
mysqld got signal 6 - MySQL server crashes every 2-3 minutes
MySQL server crashes every 2-3 minutes due to the issue below, how do I understand where exactly does the error occur and how to stop this from occurring? Any help with how to properly read this to ...
- 101
0
votes
2
answers
53
views
Duplication records with INNER JOIN
I’m validating records after a partition’s rotation.When I run the SELECT COUNT for each table I get 114,817,959 records.
SELECT COUNT(*) FROM SMS.dbo.MessagesPerf WITH (NOLOCK) WHERE
DateEntered &...
- 37
0
votes
1
answer
67
views
Windows Power Plans for SQL Server
Is the High Performance power plan in Windows Server still relevant in SQL Server setups on modern hardware/virtualization? Searching for articles from my usual trusted sources brought up nothing more ...
0
votes
1
answer
22
views
Oracle Redaction and Definer's Rights Stored Procedures
0
I created a data Redaction policy for a field EMAIL in a Person Table in an ORACLE Data Base 12c.
In the expression statment of the Redaction Policy is the exception for the Data base owner user: ...
0
votes
0
answers
22
views
RDS MySQL Cluster: Cannot Grant SET_USER_ID
On an RDS MySQL 8.0.33 cluster, I'm trying to grant the SET_USER_ID privilege to a migrations@% User so that that account can change the DEFINER on some views. I'm using the Master account, root, ...
- 101
0
votes
0
answers
11
views
Trying to speed up production build using Gradle with Azure Devops
https://docs.microsoft.com/azure/devops/pipelines/ecosystems/android
pool:
vmImage: 'macos-latest
steps:
-task: Bash@3
- displayName: Check for skippable builds
inputs:
targetType: 'inline'
...
- 1
0
votes
2
answers
44
views
PostgreSQL's unaccent function is unable to remove accents (diacritic signs) from Japanese characters like 'ド'
The postgresql out of box unaccent function is unable to remove accents (diacritic signs) with more then one diacritic in a character. The character 'ド' which is failing for normalisation have 2 ...
0
votes
1
answer
33
views
cpulimit/iolimit per mysql/mariadb user
I can set cpulimit, iolimit, etc for mysql process by moving its pid into cgroup.procs file of a cgroup.
But single mysql process handles all the queries(threads) and I cant set cpulimit to the entire ...
- 1
0
votes
2
answers
77
views
formating date colums causing performance issues
I have a query(written by my previous co-worker) with date columns returned as output but formatted, which s causing performance degradation (for below query returning 10k records, performance ...
- 25
0
votes
1
answer
14
views
High Availability postgres db cluster with Patroni and Etcd
I am building a HA Postgres db cluster with one master and multiple slaves. Please look at the below points and correct me if I am wrong:
For each slave node, I need to use pg_basebackup to sync and ...
1
vote
1
answer
66
views
Using table valued function as a table in sql server join
I have a scenario where SQL server using table valued function with joins to many other tables. The function returns many columns, which are not always desirable output for every report, causing lots ...
- 25
1
vote
1
answer
44
views
Aggregate SCD Type 2 data "as of" each day
Problem
When working with SCD type 2 data it's easy to see the state of a table "as of" a given point in time by using your date columns, eg: valid_from and valid_to. For example:
select * ...
- 133
0
votes
0
answers
24
views
resume search with full text index
I have a database structure where resume stored in document column in WHDocument table in database for each person. During skilled person search in application the persons skill is also searched for ...
- 25
0
votes
2
answers
53
views
PostgreSQL: Pgbouncer authentication
We are using authentication file mode in pgbouncer. The authentication part of Pgbouncer is so confusing. Would be helpful if anybody can throw some light on my below queries.
Are Pgbouncer ...
- 13
0
votes
0
answers
38
views
upgrade data dir from mysql 5.7 to mysql 8
Seems like mysql_upgrade is deprecated but how to upgrade the database then?
When starting mysql 8 with datadir from mysql 5.7 the server can not start
2023-11-27T22:45:15.292059Z 0 [System] [MY-...
- 147
0
votes
2
answers
119
views
What database to store 20~ billions rows
I plan on archiving messages, and I'm not sure which I should use.
It should be able to hold a maximum of 20 billions rows (that's what I expect the total number to tend to)
Each row will have three ...
- 167
4
votes
1
answer
236
views
Catch Block Inside of a Trigger Does Not Get Hit When a SP With Dynamic SQL Errors Out - Causing the Larger Transaction to Fail
I am not a fan of triggers and dynamic sql, however, what I am working with requires both.
CREATE TRIGGER [dbo].[GenerateDynamicFormItemViews] ON [dbo].[tblFormItems] AFTER INSERT, UPDATE
AS
BEGIN
...
- 505
1
vote
1
answer
29
views
how to speed up and optimize mysql search with indexes
I need to optimize performance on a search with lots of strings like Z1719121.
my table structure:
codfabrica => varchar 191 => nullable
codoriginal => varchar 191 => nullable
aplicacao ...
- 113