All Questions

Filter by
Sorted by
Tagged with
0 votes
2 answers
349 views

Postgres: How to automatically fix date/time field value out of range inside the query

How one can make Postgres treat a little stupid about the date which actually does not occur on the Gregorian calendar? SELCET date, value FROM table WHERE date BETWEEN 2022-01-01 AND 2022-02-31 ...
0 votes
1 answer
462 views

MySQL column that auto generates data, longer than UUID

My friend has the problem as shown below. He asked at StackOverFlow and there is no reply yet till now so I wish to help him to try the luck here. Thanks for everyone that helps. "I would like to ask ...
1 vote
0 answers
15 views

Why is this `pgbench` benchmark faster on postgresql 11 than on postgresql 14?

To check postgresql performance, I ran the following benchmark with varying postgresql versions and filesystems: pgbench --initialize --scale=50 bench # Run for 3 minutes postgres pgbench --client=10 -...
1 vote
0 answers
5 views

Postgres 14 and pg_trgm index on primary key column make join operations super slow

I've just upgraded my postgres instance from v11 to v14. There was an interesting problem because we have a trigram index on order_id column. This new feature makes our simple join query on that ...
0 votes
1 answer
607 views

Mariadb Galera cluster empty set

I am playing around with a MariaDB Galera Master Master Cluster and I have some issues which I do not understand. [mysqld] bind-address=0.0.0.0 [galera] wsrep_on=ON wsrep_provider=/usr/lib64/galera/...
0 votes
0 answers
6 views

Delete all data of all tables of all user databases in MariaDB

In our development environment, we want to have a DeleteData.sql query that developers can use to delete all of the data of all tables of all databases of their MariaDB docker instance. For example, ...
0 votes
1 answer
27 views

Postgres: Delete but keep x amount of rows per input

Let's say I have the following table, with data: +----+------+--------+------+ | Id | User | Item | Time | +----+------+--------+------+ | 1 | 1 | Banana | D+1 | | 2 | 1 | Banana | D+2 | | ...
10 votes
1 answer
903 views

Simplifying Function with Recursive CTE and/or Window Function

I'm trying to come up with a Recursive CTE and/or Window Function to create a function. After days, I've boiled the function down to (pseudocode) where I have N and B, and need to generate E: En =...
0 votes
1 answer
863 views

Create triggers on slave only in mysql replication with a slave with different schema

I have a mysql replication setup with one master reporting to one slave. The schema on the slave is the same than the master but for each table I have an additional synchronized column only on the ...
0 votes
1 answer
19 views

any suspicious result in that explain analyze?

I have the following query : SELECT "message"."id" AS "message_id", "message"."conversation_id" AS "message_conversation_id" FROM "...
0 votes
2 answers
624 views

Add database to existing MySQL master-master replication which uses binlog_do_db?

We have 2 Mysql 5.7 servers running in master-master replication mode. And there is a database replicating between these 2 servers: binlog_do_db = database1 Now we need to add a new database into ...
1 vote
1 answer
55 views

Percentage based scan on a timeseries data

I have 20 GB csv file with 650 million rows. The data looks like this: trade_time,price 2020-01-01 00:00:01.481,7189.42 2020-01-01 00:00:01.708,7189.42 2020-01-01 00:00:06.290,7189.5 2020-01-01 00:00:...
1 vote
1 answer
754 views

MariaDB Inner join is slow

Here is my SQL (with explain): EXPLAIN SELECT * FROM GBI gbi INNER JOIN VF_KRED kred ON gbi.vendor = kred.vendor; Giving below the outcome: Now, the select SQL takes ~10 seconds to produce the ...
15 votes
5 answers
47k views

How to detect any changes to a database (DDL and DML)

There are a lot of databases on my client's SQL server. These databases are under development, so developers can design, refactor, do data modifications and so on. There are some databases that change ...
3 votes
2 answers
11k views

Shared and IX locks causing deadlock (Sql server)

In my environment, I have 2 tables - Issue and IssuePropertyValues (which has foreign key reference to the Issue table). Now, we insert values into these 2 tables using the below 2 queries: INSERT ...
0 votes
1 answer
429 views

Insert into table select - Replication lag - Percona Server 5.6

I have two MySQL instances (Percona server 5.6.31) in Master-Slave replication setup. I have set the below configuration: ROW based replication is set. Transaction Isolation is set to read-committed....
17 votes
1 answer
5k views

Why is Clustered Index Scan Number of Executions so high?

I have two similar queries that generate the same query plan, except that one query plan executes a Clustered Index Scan 1316 times, while the other executes it 1 time. The only difference between ...
1 vote
2 answers
79 views

db<>fiddle (Oracle): Query works in 18c, but not 21c

I have a Oracle query that I use to analyze the vertices of SDO_GEOMETRY polylines: WITH lines AS (SELECT SDO_UTIL.FROM_WKTGEOMETRY('MULTILINESTRING ((0 5 0, 10 10 10, 30 0 33.54),(50 10 33.54, 60 10 ...
1 vote
1 answer
247 views

Ensure same tenant in a Many to Many relationship

I am trying to draft a very simple data model for a multi-tenant database. So far I have only 3 tables: company: pretty much my tenant account: is part of one company and zero or more groups ...
2 votes
1 answer
233 views

Replicating into a Percona XtraDB Cluster

We have a 2 node (plus an arbiter) Percona XtraDB cluster. It is used for data indexing/archiving. It has it's own database called archive but it is also the replication slave for a database called ...
1 vote
1 answer
327 views

MySQL: Point in Time query using InnoDB log sequence number?

Is it possible to do a point in time query based on a log sequence number? For example, I would like to do something similar to Oracle's "as of" query select foo from bar as of scn 123923455; which ...
1 vote
1 answer
16 views

EXCLUDE CURRENT ROW cripples performance of window functions

I'm working with a customer's database where there are rows representing instances of Users, Activities (table named ActivityRecords), and ActivityUsers in correspondingly named tables, where ...
0 votes
0 answers
35 views

Order by string with numbers

I need to order the query using a pattern that mixes text and numbers. This is the default ORDER BY which is "correct" if not considering the entire numbers. But as you can see, at the end ...
0 votes
1 answer
813 views

How to gracefully handle MySQL deadlocks involving SAVEPOINT?

I have a concurrency problem where 2 (or more) processes are making inserts at the same time and are all locking the same index page (I guess) among with a on a SELECT FOR UPDATE clause Here is a ...
0 votes
1 answer
75 views

Index Optimization for tables that are truncated and charged everyday

I'm a recently recruited Data Engineer, I have made a bunch of ETL pipelines planned to run daily, the destination tables are truncated and charged again. The DBMS is on-prem SQL Server. When I ...
1 vote
1 answer
2k views

SSRS 2016 report is failing with 'EXCELOPENXML' render format error

Few reports are failing with 'ExcelOpenXML' render format error after rs migration from 2014 to 2016. I made following change to the RS config file so I can use old excel format however some of the ...
1 vote
3 answers
136 views

why is granting ALL permissions and privileges to a new role/user for a new database difficult with postgresql?

With mysql and am sure countless other database softwares, it is pretty easy to create a new database, create a new user and grant that user to own and be able to do everything on that database this ...
0 votes
3 answers
3k views

MySQL thinks Master & Slave have same server-id, but they don't

Trying to setup MySQL to run one-way replication. I have the master set to server_id = 1, a replication user setup, binary logging enabled, the Slave server_id = 2 and is connected and waiting for an ...
1 vote
1 answer
3k views

Oracle 12c Database swapping after set up huge pages

I need to setup a Oracle Database 12c with 64GB RAM on Linux RedHet. I decided to use huge pages. Something must have went wrong. Altertlog: WARNING: Heavy swapping observed on system in last 5 ...
0 votes
1 answer
297 views

How speed up mysql dump without data?

There are a lot of questions (1,2,3) about speed up MySql restoring. But all of them speed up data import. Is there a way to speed up schema tables creation? For example dump iundepended table in ...
1 vote
2 answers
2k views

SQL Server 2012: flatten hierachy to dynamic number of columns

In MS SQL Server SQL 2012, I have a hierarchy table with a dynamic number of levels. How can I transform the following table: CREATE TABLE #Hierarchy (AccountId INT, ParentId INT, Name VARCHAR(50)) ...
0 votes
0 answers
19 views

Return Multiple Stored Procedures Outputs into one table

I have a handful of stored procedures which have a SELECT COUNT(*) and then return the number that is that count I want to write a new SP that has all of the values in a table something like: Sp1 | ...
1 vote
2 answers
1k views

SQl query to create a report based on multiple relational tables

I have an e-commerce website and I'm in the process of creating a sales report. These reports need to be divided by year and month. Each sale made on the website will generate a relational link ...
0 votes
1 answer
468 views

Unable to start mysql slave due to the error: Unknown table engine 'InnoDB'

I had to install my mysql slave server due to storage issue. I have synced my master disk to the slave disk. Now i'm not able to start my slave server (4GB RAM) due to the error: Unknown table engine ...
0 votes
1 answer
807 views

How to create a MongoDB index with isodate?

I've create and index for a query and its running is less then 1 second to retrieve millions of rows, but as soon as I add greater than ISODATE, it doesnt use indexes anymore. the query is this one: ...
5 votes
3 answers
16k views

Disabling certain steps in a job

I have a project coming this weekend where all the jobs has to be stopped but only 1 step in a particular job has to be run. for eg: i have a a 2 step job and the second step must run and the first ...
0 votes
1 answer
1k views

Repair by Sort not working

I am trying to import a .sql file which has MyISAM engine, about 51 million rows with 18 columns. The file has 1 primary and 1 unique key. I have after lurking around this site and reading mysql ...
0 votes
1 answer
86 views

SQL Server stored procedure taking too long sometimes even though indexes have been added with memory grant issues

I have a stored procedure that takes too long sometimes even though I have added all suggested indexes. I keep getting the Query had to wait for memory grant The query plan can be found here: Query ...
0 votes
1 answer
2k views

Append JSON array to an object

I'm trying to transform a table that has a rows with the some attributes that are the same, but others that differ like the table below to a specific JSON structure. id name language attr1 attr2 ...
0 votes
1 answer
708 views

Finding all possible minimal covers

I have a relation schema R = {A, B, C} and the following functional dependencies: A → B A → C B → A B → C C → A C → B How many different minimal covers can I derive from this relation schema? I have ...
0 votes
1 answer
256 views

Change Storage Engine on Referenced Tables

i need to change storage engine from 'InnoDB' to 'MyISAM' on some tables of different databases. So i made a script file to do so: #!/bin/bash DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -...
0 votes
0 answers
17 views

Oracle: why does this DDL yield a different result in production (reordering of columns)?

Problem description This week we had an issue in production, where an SQL script that was executed ended up with a different result than what we had tested before locally, in Docker and on our ...
1 vote
1 answer
16 views

Mysql update json if-else exist

I want to update my raw with if-else condition. Here is my sql code; update posts set like = if(json_contains(like,2,'$'), json_remove(like,replace(json_search(like,'one',2),'&...
0 votes
1 answer
21 views

Using having after grouping by a rand is not returnig expected results

AFAIK having statement is executed after group by (if present, otherwise it works like a simple where), so it can be used to filter grouped rows, but i'm facing a strange behavior issue I can't ...
1 vote
1 answer
30 views

How to disable MYSQL Replication?

I have 2 databases which are replicating each other with no issues. I required a test database server and to avoid installing OS etc, I started a new image of one of the servers (with different ...
1 vote
1 answer
933 views

DB Design - Assigning users to multiple sites with different roles

I am working on a DB design that lets me manage roles and permissions for each user per site. These are my business rules: A user can manage multiple sites A user can only have one role per site A ...
5 votes
2 answers
6k views

In Memory Database Remove File and FileGroup

I have a SQL Server 2016 database and I configured in-memory table. I want to drop this configuration, then I drop that table. When I want to delete that file and filegroup, I got following error. ...
0 votes
0 answers
7 views

Can I mix Galera-3 and Galera-4 nodes?

Say I have three servers node1 192.168.100.201 node2 192.168.100.202 node3 192.168.100.203 on node1 and node2 is running mariadb 10.5 and on node3 still 10.1 and I need that version there still, but ...
0 votes
1 answer
1k views

How to remove NULL values in multidimensional array?

SELECT dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid ,array_agg(array[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]) as ...
0 votes
0 answers
13 views

Different time sysdate and current_date when connect to database since application server

I have a problem with the time that Oracle gives me when working with applications. I currently connect from an application server (apx1) to a server containing Oracle 19c (bdx1) for database ...

15 30 50 per page
1
2 3 4 5
1928