All Questions
96,363
questions
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
...
- 71.2k
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 ...
CommunityBot
- 1
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 ...
- 226
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/...
CommunityBot
- 1
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, ...
- 1,277
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 |
| ...
- 133
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 =...
- 71.2k
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 ...
CommunityBot
- 1
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 "...
- 32.1k
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 ...
CommunityBot
- 1
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:...
- 71.2k
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 ...
CommunityBot
- 1
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 ...
- 2,119
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 ...
- 2,119
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....
CommunityBot
- 1
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 ...
- 2,119
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,387
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
...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
- 152k
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 ...
- 23k
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 ...
CommunityBot
- 1
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 ...
- 23k
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 | ...
- 161
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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:
...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
- 33.6k
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
...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 -...
CommunityBot
- 1
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 ...
- 173
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),'&...
- 9,871
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 ...
- 68.9k
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 ...
- 9,871
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 ...
CommunityBot
- 1
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 ...
- 101
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 ...
CommunityBot
- 1
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 ...
- 131