All Questions
99,466
questions
0
votes
0
answers
6
views
Portgresql Insert from CSV to table1 when exists in table 2 (Foreign Key) else Insert value in table 2 and then inser in table1
I want to insert data in table 1 imported from csv performing the following steps.
Copy values from csv_depo to T1_depo only if they exist in T2_depo. If not, first copy the values from csv that ...
0
votes
1
answer
1k
views
MongoDB Aggregate Poor Index Usage
I've been trying to understand the MongoDB Aggregate process so I can better optimize my queries and I'm confused by usage and $match and $sort together.
Sample DB has only one collection people
[{
...
CommunityBot
- 1
0
votes
1
answer
392
views
Oracle 12c create manual script for automatic startup on RHEL7
I need to create a script to start and shut down a 12c database in rhel7 automatically when the operating system starts and shuts down, it turns out that I have tried the common options with dbstart ...
CommunityBot
- 1
1
vote
1
answer
30
views
Need a PL/SQL code to drop many partition in 1 single SQL statements
I have a requirement where we need to drop historical partitions which are not dropped .
Requirement is dropping multiple partitions in one step instead of dropping one by one.
like
ALTER TABLE sales ...
- 39.2k
0
votes
0
answers
10
views
backend_xmin or backend_xid go back to NULL in running transaction
I have monitoring on pg_stat_activity that alerts me when a transaction that has (backend_xmin IS NOT NULL OR backend_xid IS NOT null) gets older than 35 minutes (judging by xact_start). Sometimes, ...
- 126
0
votes
1
answer
282
views
MySQL multiple table joins and multiple data levels - best practice
I have multiple tables i wish to join up to produce one big query with specific columns summed.
I was having a problem getting the correct summed columns in the final query
(i believe this was ...
CommunityBot
- 1
0
votes
0
answers
20
views
Can't log into Oracle 19c database running inside a container from the host
I have a server running Oracle 19c inside a Docker container, and I would like the ability to log into the database from the host using sqlplus.
Inside the container, I am able to run this command ...
- 101
0
votes
1
answer
326
views
Why does mysql binlog use different isolation level?
Default isolation level:
mysql> SELECT @@TX_ISOLATION;
+-----------------+
| @@TX_ISOLATION |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
Binlog:
...
CommunityBot
- 1
0
votes
0
answers
13
views
What are the time complexity of MongoDB operations? (Or where I can find it)
I'm newbie in MongoDB, I'm deciding how to make the schema. And I found that people usually don't separate models in mongo, join everything in the same record. So in order to decide if I will or not ...
- 101
1
vote
1
answer
49
views
Mismatch between the total number of rows in a sql join
When I join entries from the points table (n = 34, 436) to the mid_pts table (n=1.98 million) the total number of rows of the table_joined is n = 26,016. However, when I add the unjoined_pts table of ...
- 163k
3
votes
2
answers
123
views
psql can't connect to existing db
i have problem with connecting to database via psql. I get this error:
postgres@server:/var/lib/pgsql # psql -d aisgdmp2
psql: error: FATAL: database "aisgdmp2" does not exist
DETAIL: The ...
- 2,062
0
votes
2
answers
295
views
MySQL replication - independent data update on slave
I have an unusual situation where I am migrating websites and their corresponding databases from one server to another.
I have a database that is hosted on one server, A, where new records are ...
CommunityBot
- 1
0
votes
1
answer
33
views
Does Sqlserver store number of relevant decimals in a float field
I have a table in sql server containing a float field. This field displays a different number of decimals from row to row.
I know for a fact that a float field CAN NOT contain the exact value 3173.77, ...
- 139
1
vote
0
answers
774
views
MySQL Workbench Crash on Execute (Mac)
I'm currently using MySQL version 8.0.28 and MySQL Workbench 8.0.29 on Macbook Pro 2017. When on the most recent version of MySQL Workbench (8.0.31), Workbench started crashing whenever I went to ...
- 1
1
vote
1
answer
916
views
Oracle 18 on Windows with long hostnames doesn't talk to the local listener?
Note: Initially this was about installing Oracle 18c XE on a Windows machine with a long (> 15 chars) hostname. During our further tests we then noticed that Oracle 18c XE does not work properly at ...
0
votes
0
answers
12
views
PostgreSQL trigger to allow update of only one specific column
Is it possible to create such trigger function, without specifying by name the other columns that shouldn't be affected?
Example:
Create table test(id integer, name text, amount numeric);
I'd like to ...
- 119
1
vote
0
answers
36
views
How can I reset the Change Tracking Version of a SQL Server database to 0?
Is there a documented or undocumented way to reset the change tracking version in a SQL Server database to 0 ?
Can someone share in which internal table or structure is the change tracking version ...
user20130987
2
votes
1
answer
5k
views
SQL Server - allocation unit size in Windows 2019
I'm about to install a SQL Server 2019 instance on a Windows 2019 machine and found out that there are new NTFS allocation unit sizes available - up to 2M.
I used to format partitions using 64k unit ...
CommunityBot
- 1
7
votes
1
answer
95
views
SQL Server Primary Key Column Statistics Histogram Suggests Duplicate Values
I have a statistic on a Primary Key column in a table. When I update the statistic with the default options:
UPDATE STATISTICS dbo.MyTable PK__MyTable__CB394B3946083350
I get a histogram as follows (...
- 78.7k
1
vote
4
answers
3k
views
Return sample row if result set is empty
What would be the most practical way to write a query, that returns single row in case of empty result set.
Something like (pseudosql)
(SELECT 1 AS c1, 2 AS c2 FROM ... WHERE ...) OR_ELSE(SELECT 0 ...
- 101
1
vote
1
answer
716
views
pgAdmin - "cannot open logfile!" (WINDOWS 10; Postgres 9.4)
Our Windows 10 clients have both PostgreSQL 9.4 and pgAdmin3 (v1.22) installed.
I keep receiving the FATAL: "Cannot open the logfile!" error by invoking pgAdmin3 in either
of the following ...
CommunityBot
- 1
0
votes
0
answers
13
views
How I can aggregate a final store schedule in postgresql?
I have the following table that I keep for each day of week when a store is open:
store
---
id PK
name VARCHAR
weekly_store_open_schecule
---
id PK UNSIGNED SERIAL
store_id INTEGER not null ...
1
vote
1
answer
630
views
Disabling auto compaction in CouchDB
I would like to disable auto compaction on my CouchDB instance - so that I can get a full history of all the database updates.
Is this possible? If so, how do I go about it? Is there a reason why I ...
CommunityBot
- 1
-1
votes
0
answers
25
views
Script for IF EXITCODE ms sql
I'm trying to find a script that does a check at the end of a piece of code in the stored procedure, that script needs to check after all the query execution that everything was successful and ...
- 21
0
votes
0
answers
62
views
Unable to install SQL Server 2019 on Windows 11
****I would really appreciate if someone can help me to sort this issue, really frustrating.
Action required:
Use the following information to resolve the error, and then try the setup process again.
...
- 1,459
2
votes
1
answer
661
views
Filter by list of values pairs
I have Postgresql 9.4 database and products table. For each product there must be list of pairs:
price; quantity for which this price is acutal
Products table may contain millions and billions of ...
- 973
2
votes
1
answer
91
views
Improving performance in a query with median
I have the following query that I would like to speed up, I ran it through the Tuner but it didnt suggest any new indexes so I am looking for some help to improve it. (using sql server 2019)
Actual ...
- 1,333
2
votes
1
answer
203
views
Problem with IF NOT EXISTS query in ms sql
I want to script query to validate if not have existing column test to add it after check condition. But in those two ways it is not working.
Way 1:
IF [dbo].[TEST].TEST NOT EXISTS
BEGIN
ALTER TABLE [...
- 21
1
vote
2
answers
263
views
How to get sum of 3 sub count queries and group them using an attribute
I have 3 queries as follows:
For the date 2019-12-02, I am calculating 3 type of values. The total value for 2019-12-02 must be the sum of these 3 queries.
Q1
SELECT count(*)
FROM ...
CommunityBot
- 1
-1
votes
0
answers
40
views
Delete query slow
I have a somewhat strange situation that I cannot figure out.
I do have a msSql 2019 server, with a table that has 5 columns, the first being ID.
It has about 1 million rows. There is a little .net ...
13
votes
3
answers
15k
views
How to properly monitor the number of PostgreSQL database connections?
I tried to use a Nagios script for monitoring the number of database connections on a Postgres database and I reached this problem: these are counted as currently open-connections and measured every 5 ...
- 172
-1
votes
0
answers
25
views
Double encoding in Postgresql log file
I have server Debian 9 with Postgresql 11.7. When I read a log file (like less), some messages are in UTF-8, others are in CP1251. How to bring the log file to one encoding?
List of databases
...
0
votes
1
answer
353
views
Parameter innodb_flush_log_at_trx_commit not available in AWS Aurora 5.7
I'm trying to modify the parameter innodb_flush_log_at_trx_commit on a RDS Aurora 5.7 instance from the AWS RDS Web Console and when I look through the parameters in the database I can not find it.
...
CommunityBot
- 1
0
votes
1
answer
21
views
Can Postgresql FDW avoid n+1 when planning joins?
Does the PostgreSQL executor / planner have the ability to avoid n+1 querying of FDW tables? If so, what conditions have to be in place for this to happen, e.g. does the FDW need to emit a specific ...
- 649
0
votes
1
answer
21
views
SSRS - Backup Encryption Key from SSMS
I have a SQL Server with SSRS installed.
Note that SQL runs as NT Service\MSSQLSERVER
SSRS runs as NT SERVICE\SQLServerReportingServices
Version: SQL Server 2017
I am trying to automate the backing up ...
- 30.5k
0
votes
1
answer
278
views
Is there a way to synchronize mysql databases that are missing a few days worth of data
I have a primary-primary MySQL server setup that is connected to a load balancer. At one point, a failure occurred to where the replication wasn't happening and data was being written to one server ...
CommunityBot
- 1
0
votes
1
answer
20
views
Fetching 88 records via DISTINCT from a table of 1.5 million rows is taking 3 seconds
There is a table which are populated from an online service and updated every week.
This goes on a separate (second) database.
The table is like 300MB in size (1.5 million rows) processed by a cron ...
- 30.5k
0
votes
1
answer
4k
views
Internal MariaDB error code: 1927 ('Connection was killed')
Edit: filed a bug report at https://jira.mariadb.org/browse/MDEV-14493
Mariadb nodes freeze occasionally with the following error in the logs, any clues?
(drupal app, db freezes on clearing ...
CommunityBot
- 1
2
votes
1
answer
43
views
multiple percentile_cont with where clause
I'm using percentile_cont to find 50th percentile of product that is sold and product that is open to sell using where clause. Sale is a big table and it is creating performance issues. Is it possible ...
- 1,333
0
votes
1
answer
386
views
How to Design Name and Address with Multiple Addresses
I have a names and addresses table:
Name
Address
AddressType
ID
CustID
Sample:
Bob Smith, 123 Main St, Corporate, 1, 1
Bob Smith, 123 Main St, Home, 2, 1
null, P.O. Box 123, Warehouse, 3, 1
...
CommunityBot
- 1
0
votes
1
answer
1k
views
Executing a query with a variable in a loop
I have two select, how can I do the same using a loop and a variable in PL SQL Developer.
I have:
select 1 as nm from dual;
select 2 as nm from dual;
I need something similar:
DECLARE x NUMBER;
BEGIN
...
CommunityBot
- 1
0
votes
1
answer
429
views
Renaming multiple databases on a MySQL slave
I have 4 mysql databases that I'm replicating from a master to a slave. In the past, I have renamed one of them because the slave is also a dev server where it has a dev version of that schema with ...
CommunityBot
- 1
0
votes
1
answer
61
views
Corrupt empty row found in unfiltered partition
I have a Cassandra cluster consisting of 15 nodes.
The Cassandra version is 3.7.0
I am constantly getting an error message in the log messages of almost all nodes.
The recurring error message is ...
- 1,984
2
votes
1
answer
482
views
how wsrep_local_index is defined or could be changed?
I have a cluster of 3 nodes using Galera with MariaDB, every time I want to backup I check the node with the highest wsrep_local_index and then do:
SET GLOBAL wsrep_desync = ON;
Take the backup, ...
CommunityBot
- 1
1
vote
1
answer
575
views
lock-ddl-per-table option not working as expected in Xtrabackup
I've recently been getting the following errors on some of my Percona XtraBackup backups:
InnoDB: An optimized (without redo logging) DDL operation has been performed. All modified pages may not have ...
CommunityBot
- 1
12
votes
2
answers
5k
views
Is the system column "ctid" legitimate for identifying rows to delete?
I have a table with hundreds of millions of rows that I need to delete data from.
The existing indexes are the most efficient.
I can however use the existing indexes to find the rows to delete by ...
- 163k
0
votes
1
answer
1k
views
Unable to connect to an Oracle 12c Docker container
I'm working on a project with Oracle, and using a Oracle XE 11 container for testing. But recent developments have ended up with me running up against a brick wall: I need larger table names. ...
CommunityBot
- 1
2
votes
2
answers
318
views
SQL trigger that outputs incremental numbers from zero whenever the month changes
I have the following trigger that outputs a number in the format such as 170200005 where 17 is the year and 02 is the month (February) and changes as months changes e.g March 03 then the last digits ...
CommunityBot
- 1
2
votes
1
answer
27
views
Why is the MySQL server-id necessary for replication when the server-uuid exists?
We're trying to setup some basic MySQL Master/Slave replication at our company, and we're confused regarding the unique server_id requirement on replica servers. Quoting the mysql docs:
On the source ...
- 178k
4
votes
1
answer
371
views
Pagination - Text comparison with greater than and less than with DESC
I am implementing a seek method for pagination and am wondering about how to best query on a text column with DESC. The queries for this seek approach use a less than or greater than depending on if ...
CommunityBot
- 1