All Questions
99,461
questions
0
votes
1
answer
11
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 ...
- 971
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
241
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
10
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
10
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 ...
- 3,645
1
vote
1
answer
248
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's properties are not the ...
CommunityBot
- 1
0
votes
2
answers
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
345
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
votes
0
answers
14
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
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
835
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
17
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
120
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
296
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
228
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
15
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
1
vote
3
answers
24
views
Seq scan on OR condition
I'm using PostgreSQL 11. I have a table with ~500к records and its size is around 2GB. If I'm running this query:
SELECT DISTINCT point.id
FROM "buildings_point" as point
LEFT OUTER JOIN ...
- 163k
0
votes
1
answer
16
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
0
votes
2
answers
257
views
Backup on Distributed AG setup
I have setup a distibruted availability group and trying to understand behaviour of backups on it. While I can take backup from primary replica of Primary AG, I can not take backup from primary ...
CommunityBot
- 1
1
vote
1
answer
20
views
Alter command showing syntax error in MySQL 8.0
Below is my Table schema
create table employee(
emp_id numeric(4),
fname varchar(10),
lname varchar(10),
mgr_id numeric(4),
hire_date date,
job_id numeric(2),
dept_id numeric(3)
);
Now I want to use ...
- 3,029
0
votes
1
answer
21
views
Is UPSERT feature complete?
Here, a lot of energy seems spent on Coalesce - I think it is to avoid the case where a null value in the update record could overwrite a non-null value.
Say we have a table of businesses and some ...
- 163k
28
votes
2
answers
25k
views
PostgreSQL UPSERT issue with NULL values
I'm having an issue with using the new UPSERT feature in Postgres 9.5
I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can ...
- 163k
2
votes
1
answer
2k
views
Database design - Members & companies represented by members
I'm struggling in database design problem and it seems I can't get it out properly.
I need some help on this. Maybe it's a dumb case but I'd like to validate or correct my scheme and then go ahead.
...
CommunityBot
- 1
1
vote
4
answers
788
views
COUNTING results per row in self referenced table
I have a table called Clients. And at this table, I have an idFather to know who added this row to the table.
Sample:
id | user | idFather | createdAt
---+---------+----------+-----------
01 | ...
- 101
0
votes
0
answers
10
views
Does pg_listen blocks a thread on the database server?
As stated in the title. If yes, is this blocking per listener?
- 21
1
vote
1
answer
773
views
MySQLdump Exclude master-data
Is it possible to exclude the master data from a mysql dump? From the manual it seems like the only options are commented out or present. I don't need the data at all because it is going to a static ...
CommunityBot
- 1
0
votes
1
answer
483
views
PostgreSQL server running very slow at minimal work load
Postgres server running very slow. DB has two tables and when I run explain analyze for below query, taking more than 10min and query running forever.
Tables DDL, db<>fiddle.
User query:
...
CommunityBot
- 1
0
votes
0
answers
3
views
Is it possible to change data in 'select' request which contains join comands in pgadmin?
When I'm getting data from a table, pgadmin tools allow me to edit, add and delete data. However, if join command is added to the request, these pgadmin tools become unavailable.
Is there any way to ...
- 1
0
votes
0
answers
17
views
How can i get the count of millions of records from a view in PostgreSQL as fast as possible?
I created a view that hold millions of records,
I want to get count of all rows,
I used this query but it takes time alot to run
select count(*) from my_view
is there any way to get faster?
- 30.5k
0
votes
0
answers
6
views
How/Why are in-flight transactions lost in synchronous replication in contrast to semi-synchronous
I'm reading through an article on why SQL databases are hard to scale and problems with ACID.
Here's the link to the short article: Link
Today's solution is usually post-write replication, where each ...
- 101
1
vote
1
answer
839
views
I'm getting an error when I ran mysql_secure_installation
I keep getting the following error on running sudo mysql_secure_installation
... Failed! Error: SET PASSWORD has no significance for user
'root'@'localhost' as the authentication method used doesn't ...
CommunityBot
- 1
0
votes
1
answer
10
views
Mariadb upgrade problem
I upgraded mariadb on a Centos 7 box from 5.5 to 10.5 and after updating the tables, mariadb will no longer start. In /var/log/messages, I'm getting the following errors. Any help would be much ...
- 6,081
3
votes
2
answers
914
views
SQL Audit and AlwaysOn Replicas
We would like to enable SQL Audit on a SQL Server 2014 database. This database is replicated to a read replica via AlwaysOn. We don't need, or want, the replica audited. If we enable audit on the main ...
0
votes
1
answer
442
views
Filter bad words in multiple tables using triggers
I have for example two tables blog_posts and image and each record in these tables have a text field, that may contain a bad word in it.
so the idea is, I want to check these records and change these ...
CommunityBot
- 1
5
votes
2
answers
4k
views
SQL Server database forum design sample
I am doing a design for a database using SQL Server 2008 R2, basically it is a simple database design for a forum (just like games fans forums). Fortunately I am struggling with it as I didn't find ...
CommunityBot
- 1
0
votes
1
answer
9
views
How to silent the login.sql output
[nir@dba etl]$ cat login.sql
col TAB# new_value TAB NOPRINT
select chr(9) TAB# from dual;
set markup csv on delimiter "&TAB" quote off
set heading off termout off echo off feedback off ...
- 39.2k
2
votes
1
answer
3k
views
Understanding INSERT ... ON DUPLICATE KEY UPDATE deadlock scenario
I'm trying to understand a scenario where an INSERT ... ON DUPLICATE KEY UPDATE statement is causing deadlocks under high concurrency.
The two tables in question:
hosts:
"CREATE TABLE `...
CommunityBot
- 1
24
votes
7
answers
31k
views
How do I force one record to have a true value for a boolean column, and all others a false value?
I want to enforce that only one record in a table is considered the "default" value for other queries or views that might access that table.
Basically, I want to guarantee that this query will always ...
