All Questions
99,466
questions
0
votes
1
answer
6
views
Query with smaller subset to scan taking exponentially longer?
I have a large table, extrinsics, almost 90GB in size, containing data from multiple blockchains.
I have a query:
select * from public.extrinsics where chain_id = 1 ORDER BY "extrinsics".&...
- 163k
0
votes
1
answer
6
views
merge or delete then insert or delete then append when the database is unstable
What are the best practices when it comes to modifying a huge (more than 500 M records) table in Azure SQL server in deployment.
By modification I mean the table gets updated for past few days (let's ...
- 36.7k
0
votes
1
answer
674
views
How to configure MySQL Router on two application servers?
I have three MySQL servers running with group replication and inside a InnoDB Cluster. I have two app servers that will be connecting to the cluster and I need to configure mysql router on both app ...
CommunityBot
- 1
1
vote
1
answer
899
views
SQL Server Linked Servers not connecting
I have configured linked servers with "connections will be made using the login's current security context", but can only make use of them when logged on to the server, not from another machine...
...
CommunityBot
- 1
3
votes
1
answer
418
views
Does ordinal position of columns matter for SQL Server replication
I am setting up replication on SQL Server 2017. Transaction replication with push subscriptions such that many smaller database are replicated into a single larger database for reporting.
The ...
CommunityBot
- 1
-1
votes
0
answers
12
views
What permissions are needed to prevent ERROR: permission denied for tablespace pg_global in RDS?
I'm looking for some help identifying a permission I need to grant to prevent an error in a proprietary app. The App is loading data into an AWS RDS postgresql database. Loading data is working fine....
0
votes
0
answers
7
views
Best database solution for highly interconnected entities?
I'm building a service (or rather a set of microservices) to serve as the backend for a social-network-like website. In short that means the following for my data:
millions of entities
with dozens of ...
- 1
0
votes
4
answers
15k
views
How to take SQL Server databases backup using .bat script file with time stamp
I am using SQL Server Express edition, so there is no features for backup plan, I want to take databases backup using bat script, and create a scheduler to be run everyday. I have created .bat file ...
- 483
1
vote
2
answers
1k
views
Are certain environment variables required for mysql login-path?
I created a bash script that backs up my database, db_backup.sh
It basically just runs these commands:
DB=/home/linuxuser/backups/backupfilename.sql.gz
mysqldump --login-path=local db_name | gzip &...
0
votes
3
answers
596
views
Do I have to add accounts to "pg_hba.conf" to allow the account to use the database?
I was following a tutorial to use PostgreSQL with a server application (both are running on the same computer). The tutorial was using SQL commands, but I used pgAdmin. I added a new user 'gitea' with ...
CommunityBot
- 1
2
votes
2
answers
51
views
Find out students max no of times not enrolled consecutively in a subject
This is my sample data set. Looking for code in MySQL
Desired output
Student
Times
abc
3 (Student abc didn't enroll for any subject consecutively for T3,T4 in 2021 and then for T1 in 2022)
xyz
4 (...
- 22.3k
0
votes
1
answer
15
views
Unnest is not preserving nulls or empty values while inserting the data
I have a Table Student where I have an XML as input
inputXML=
<Students><student><id>1</id><Name>SAM</Name></student>
<student><id>1</id>&...
- 77.2k
0
votes
1
answer
23
views
T-SQL How to retrieve metadata from SQL proc containing temp tables
I've a T-SQL stored proc running in a Microsoft SQL Server 2019 environment. The proc contains temp tables, dynamic SQL and is 2000+ lines. The proc's output has been verified and tested for ...
- 161
0
votes
1
answer
227
views
Postgres notify if connection timeout is occured
I want to set up a separate connection for every user of my app.
I have some data structure for this to store connections.
Obviously, I should delete the expired connections somehow.
There are ...
CommunityBot
- 1
0
votes
1
answer
309
views
auto_explain does not log
looks like i do it wrong. I turned auto_explain on with adding next valued to postgresql.conf:
shared_preload_libraries = 'pg_stat_statements,pg_wait_sampling,pg_stat_kcache,auto_explain'
...
CommunityBot
- 1
-1
votes
0
answers
18
views
Postgresql won't start after moving data directory
My use case: I am running Linux Mint 21. I have the OS on a 500 GB drive. I have a mounted 8 TB drive to contain data for use in machine learning. Some projects can conveniently work with .csv files ...
- 99
0
votes
1
answer
1k
views
SQL Install Fail - 2012 on Win11 [duplicate]
Cannot use file 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Template Data\master.mdf' because it was originally formatted with sector size 4096 and is now on a volume with sector ...
- 78.7k
0
votes
2
answers
29
views
Sql Server Differential restore
Our network team is going to restore a server (ie the entire vm) with sql 2014 on it from a backup taken on Monday. This would include all database files. Out last full DB backup was taken the ...
0
votes
1
answer
385
views
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one.
Do you think that indexes going to reduce query run time?
Any ideas how ...
CommunityBot
- 1
1
vote
1
answer
251
views
Consistency of data between two databases on different server
I have to guarantee consistency of data stored in two databases installed on different servers.
The first one is a MySQL database and the second is a SQL Server database.
Data properties are not the ...
- 23.8k
0
votes
1
answer
21
views
Secondary indexes vs Using elastic search
When does it make sense to put data in elastic search vs creating secondary indexing on Primary datastore?
Elastic search with another primary store
Pros:
Primary datastore can be optimised for read ...
- 30.5k
4
votes
3
answers
484
views
Regretting an identity: Is there a way to force inserts to specify the identity column?
To prevent an X-Y problem here's the actual problem we're trying to solve:
The Problem:
We have a bunch of lookup tables that were unfortunately created with an identity column on the Primary Key, ...
- 78.7k
1
vote
1
answer
6k
views
Error 1062: Duplicate entry 'mydb/regimes' for key 'name'
I am not an expert on databases but I'm modeling a database on mysqlworkbench 8.0.13 and when I try to run the script it breaks at this table(regimes). I can't figure out what KEY 'NAME' it is ...
CommunityBot
- 1
1
vote
1
answer
2k
views
SQL Server is terminating this session. c0000005 EXCEPTION_ACCESS_VIOLATION
I Got Error:
A user request from the session with SPID 183 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log ...
CommunityBot
- 1
0
votes
1
answer
605
views
.Net Core connect to MS SQL Server Database using AD user creds
I'm trying to connect a .Net Core application running in a Linux container to a SQL Server database. Our DBA's require us to use AD users to connect to SQL Server. Another team is using NodeJS and ...
CommunityBot
- 1
0
votes
1
answer
13
views
SQL query using MAX with multiple table joins
I have a working SQL script to show results that join multiple tables. However, I want to limit the results of the join to show only one record per a given "Chart Number" value in one of ...
- 973
0
votes
1
answer
882
views
Does a read replica help if the problem are heavy queries?
The application that our team is responsible for is having ever more often DB performance problems. The application querying it doesn't throw lots of concurrent queries, the problem we have are rather ...
CommunityBot
- 1
0
votes
1
answer
242
views
Postgres Repeatable Read vs Selecting rows that are changed by another ongoing transaction
Let's say I have a set of select statements that query important field values in a for-loop.
The goal is to make sure that the rows are not updated by any other transaction so that this set of selects ...
CommunityBot
- 1
0
votes
0
answers
14
views
+50
MySQLTuner vs cPanel SQL recommendations are different / contradictory
I've used WHM's (cPanel) to optimize the mysql configuration on my server and it suggested making the following changes which I made
innodb_buffer_pool_size=3328M
key_buffer_size=55M
...
- 51
5
votes
1
answer
583
views
Reinitialize Table Values in SQL SSDT Unit Testing
I am creating SQL Server Unit Tests. We are testing various stored procedures.
In Unit testing principles, it is good practice to setup a small database table, populate values, and tear down (...
CommunityBot
- 1
0
votes
1
answer
323
views
error writing input to commandERROR: Invalid username and/or password
This is the error I have been getting on my RMAN notifcations on my backup jobs for the last day or so. The title is exactly how the message is formatted.
~~~End Step Output/Error Log~~~
Error Log:...
CommunityBot
- 1
0
votes
1
answer
2k
views
How to create a database or role in pgAdmin4?
I want to create a database in pgAdmin4 but a corresponding item in the menu is grayed out.
I also tried to create a role, thinking that I might be lacking permissions to create database in first ...
0
votes
1
answer
346
views
How to pass, specific day month and year in the below query?
I have registration according to the data in a table and I want to show the whole month, I found the easy solution with below query but I do not know how can I pass specific day, month and year in ...
CommunityBot
- 1
1
vote
1
answer
267
views
How to force Redshift/Postgres to aggregate nth_value?
I'm trying to retrieve the 2nd, 3rd, 4th order/transaction from an orders table using a query that looks like this:
select
customer_email,
nth_value(created_at, 1) over (partition by ...
CommunityBot
- 1
1
vote
1
answer
567
views
Replicate data between MySQL servers with a different structure
As part of a system upgrade I need to sync a database between 2 different servers (master-master). The catch is that the structure is slightly different between the 2.
Most of the differences are:-
...
CommunityBot
- 1
0
votes
2
answers
327
views
Parameterized Query without Prepared Statements and PgBouncer
my app is written in Go and I use PgBouncer as a connection pool to my Postgres Database. One downside of PgBouncer, in transaction mode, is that I can't use prepared statements.
So I have 2 options, ...
CommunityBot
- 1
0
votes
1
answer
1k
views
ORA-31626: job does not exist during schema export
I'm exporting schema with following command -
expdp system/system@xxx.xxx.xxx.xxx:1521/orcl schemas=schema-name directory=test_dir dumpfile=Schema.dmp logfile=SchemaLog.log
but it results into ...
CommunityBot
- 1
0
votes
1
answer
257
views
MariaDB 10.2.22 Galera Cluster Using Bad Indexes After Analyze
I'm using Drupal on a MariaDB Galera 10.2.22 3 node cluster and I'm having some problems with one query. I have a table (InnoDB) called field_data_field_department which has a bunch of indexes. If I ...
CommunityBot
- 1
1
vote
3
answers
19k
views
How to update complex jsonb column?
I have a table with following definition:
create table json_test (
filter_data jsonb);
and i insert to it values like this:
'{"task_packets": [
{
"state": "PROCEEDING",
"...
- 163k
1
vote
1
answer
898
views
Remove the deeper jsonb element in Postgres [duplicate]
[
{
"type":"test.added",
"values":[
{
"field":"field1",
"value":"fieldValue1"
...
- 163k
1
vote
1
answer
647
views
MariaDB Read-Only user can't see database, why?
I have 2 users, one with ALL grants, another with SELECT only.
User 1 can see the database, not user 2, why?
Read-only user:
MariaDB [(none)]> SHOW GRANTS FOR 'readonly'@'%';
+----------------------...
CommunityBot
- 1
0
votes
0
answers
1k
views
Maxing out the shared pool's memory by executing many UPDATE statements (ORA-04031)
I was executing a bunch of UPDATE statement. Around ~32.8K got executed and committed, then got the error:
cx_Oracle.DatabaseError: ORA-04031: unable to allocate 3896 bytes of shared memory ("...
- 78.7k
0
votes
0
answers
836
views
Column order in a DynamoDB table browsed through RazorSQL
I use RazorSQL to navigate in a DynamoDB database. What's the rationale behind the column order?
I am asking as the import tool uses the same order (which means if I import a CSV file it needs to be ...
- 78.7k
0
votes
0
answers
20
views
Postgresql swapping even there is plenty free memory
Let me ask a question about my problem im dealing with in our production database. We have postgresql 13 database which which using swap even when there is enough RAM and i dont understand it at all. ...
Ivo Mičánek
-1
votes
1
answer
121
views
History of the provisioned write capacity in DynamoDB beyond the last two weeks
Is there any way to see the history of the provisioned write capacity for a DynamoDB table beyond the last two weeks? When I go to the Amazon AWS console, select a DynamoDB table and look at the ...
- 1,993
0
votes
1
answer
297
views
MySQL query - Passing variable into subquery's derived table
Is there a way to pass a variable into a subquery, and use that variable in a derived table in the subquery?
I am trying identify "active" organizations, and there are multiple actions that ...
CommunityBot
- 1
0
votes
1
answer
230
views
How to select maximum row value using optimal way
I have a large table:
+----+-----------+------------+--------+--------+--------+
| Id | lClientId | datAccess | lProp1 | lProp2 | lProp3 |
+----+-----------+------------+--------+--------+--------+
| ...
CommunityBot
- 1
0
votes
3
answers
7
views
How do you retrieve connect strings for Oracle database links if not in TNS
I am DBA for an Oracle 19C database which has a number of database links in it. Some of these links are local, to other instances within our firewall, and others are remote, linking to databases ...
- 3
0
votes
1
answer
16
views
Avoid nested loops
I've the following query:
Select GAD.OWNREF, GAD.ADCODE, GAD.CORNAM, GAD.NOMCUR1, GAD.OPNCUR1,
GAD.OPNAMT1, GAD.EXPDAT, GAD.CONFLG, GAD.GACTYP, GAD.OWNNAM, GAD.GACRUL,
GAD.CORREF, GAD.GACFRM, ...
1
vote
1
answer
353
views
master-master replication or master slave replication?
We have right now one server with a database.
A device and a website are accessing that database causing load
What I want is to create two servers master-slave where master databases are replicated ...
CommunityBot
- 1