All Questions

0
votes
0answers
3 views

InnoDB: does `redo log` contain initial value and final value? or just final value?

start transaction; update users set age = 34 where user_id = 3 // update age from 30 to 34 years old. .... .... when I am updating the age of this user, InnDB will write redo log. What's the ...
0
votes
0answers
5 views

MySQL: why undo log is required in the processes of database recovery?

Let's imagine that an incident happens when you updates. start transaction; update users set gender = 1 where id = 3; Undo log space is allocated. Previous values are copied to undo log Record of ...
0
votes
0answers
8 views

Very high (99%) insertion cost in estimated execution plan

I have four tables with 500,000 to 4,000,000 rows. The script joins them and insert into a temporary table with some where clause. It is taking long. The execution plan shows 99% cost only for the ...
1
vote
0answers
17 views

Same query hash for totally different queries

Query_hash definition from sys.dm_exec_query_stats is: query_hash: Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to ...
0
votes
0answers
9 views

Suddently slow SELECT

I noticed that one of my Hibernate JPA SELECTs against my Heroku PG 10.4 instance is taking a loooog time to complete as this EXPLAIN (ANALYZE, BUFFERS) shows. The database is 591MB running in PG 10....
0
votes
1answer
5 views

How do I export a multi table database with exceptions?

I am working out of phpmyadmin to export data using an sql. I have an 88 table database. I would like to export all data to a .csv from 85 of those tables where I am only keeping the rows in which my ...
1
vote
0answers
6 views

No shardIdentity document - MongoDB

Having a sharded cluster in production which has been running for a good year now without issues. Everything is on 4.0, classic 3-3-3(config) + 2 mongos. While revisiting our back-up strategy i ...
-2
votes
0answers
17 views

Applying 3NF to a table with one primary key

If I'm not mistaken, in 3NF every column needs to directly depend on all primary keys. In my table I have one key so that doesn't cause too many problems. I have a few columns which are used in ...
0
votes
1answer
17 views

Select rows with same Name and their posible ID (could be Null)

So I have this For every username there is at most one unique associated not null col2 value and potentially some null col2 values. +----------+-------+ | username | col2 | +----------+-------+ | a ...
-2
votes
1answer
20 views

Get Average of a column excluding the current month [on hold]

I would like the average to be average excluding the current month counts. I.e for November the average would calculated based on Jan-September values. How do I do this using SQL Server 2008?
-1
votes
0answers
30 views

Linked server values cached

I have a small problem. I have a warehouse, and our Inventory table tracks Stock. The warehouse is fed by services on it and the other servers we have, largely tracked by Store_Id. Here are the ...
1
vote
0answers
9 views

pgbench - 20-30% variation in benchmark results (non-repeatable benchmarks)

Problem I'm trying to use pgbench to help me tune Postgres for my hardware, but I'm seeing a very strange behaviour. I cannot seem to get a stable number for TPS in consecutive runs of pgbench. Since ...
1
vote
1answer
26 views

alternatives to passing temp table as a parameter to a stored procedure

some time ago I was developing a simple procedure to get the database sizes on a particular server. Here it is the procedure: USE MASTER GO ALTER PROCEDURE dbo.sp_getDBSpace @Radhe sysname=null, ...
0
votes
0answers
9 views

MySQL converting column of type TEXT to TINYTEXT truncates value at non-intuitive position, why?

I recently ran into a problem where a table had a column of type TEXT inadvertently converted to type TINYTEXT (MySQL version 5.6.24). We were able to restore any truncated data, but the confusing ...
1
vote
0answers
40 views

Database I/O issues on SQL Server and Extremely slow backups

We are running into a significant issue on our SQL server where the nightly backups (using Olla Hellengren's solution) has jumped from 2 hours to 10 hours. I have been unable to determine the root ...
-1
votes
1answer
23 views

Postgresql - LEFT JOIN error

Apologies ahead of time for the novice question as I am working through building my first database. I've come to joining my tables (I have multiple), but have decided to test one first. I am trying to ...
-1
votes
0answers
5 views

#1005 - Can't create table `canteen_system`.`main` (errno: 150 “Foreign key constraint is incorrectly formed”)

CREATE TABLE Main ( Customer_Id INT(5), Customer_Name varchar(50), StallName INT(5), State INT(5), Worker_ID INT(5), Position INT(5), Feedback INT(5), PRIMARY KEY (Customer_Id), ...
0
votes
0answers
3 views

Postgresql streaming replication sticky read queries

I have setup 1 master node and 1 slave node with postgresql streaming replication, is it possible to configure certain read queries to be executed in slave node only instead of routing to both nodes?
0
votes
1answer
23 views

Use NEW in FROM clause in Postgres trigger?

I am trying to write a Postgres trigger to unnest an array field before inserting or updating a new row. E.g. SELECT unnest(something) FROM NEW However, this seems to lead to an error: relation '...
2
votes
2answers
42 views

Initial Database Size Too Big

I have a clustered SQL 2016 standard database. It is set up in a virtual environment with a total of 250 GB of storage. However, my developer set the initial size of the databases to be 100 GB. There ...
0
votes
1answer
7 views

The merge process could not connect to the Publisher ' '. Check to ensure that the server is running. (

I have 2 servers with 2 kinds of replications between them. One is Transactional and the other is merge. The Transactional replication is working fine but in the merge replication, I'm getting error ...
1
vote
1answer
7 views

SQL Server 2016 Standard or Enterprise for SharePoint 2016

I know that for SharePoint 2016 version we can go for either SQL server 2016 standard or enterprise. But just wanted to know are there any disadvantages of going for SQL server standard edition ...
0
votes
0answers
10 views

Count query shows varying performance

The query appears to take anywhere from milliseconds to a couple minutes. There are about 1 million receptacles and 1 million assignments rows. While I attempt the query, there are writes going on ...
0
votes
1answer
26 views

Trigger to log Users Assigned to website

Is it possible to create a trigger so that works with the following scenarios: User types valid login info to the web application A trigger is activated and login name is inserted to the audit ...
0
votes
0answers
9 views

Oracle 10g Database Locking Session Issue

I have a problem with locking and not timing out in one of my Oracle databases. This session locks occur on certain transactions that come from Oracle Forms users. For instance, when some user ...
0
votes
0answers
9 views

importing data from different data sources to a centralized databese [on hold]

I have data stored on different databases (Oracle, SQL server, mySql...etc) located on different servers. Now am searching for open source tools to import all data from all my databases to centralized ...
0
votes
0answers
17 views

Disk recovery for Azure VM to host SQL Server

As per the Azure VM Creation of the storage pool best practice document, Simple Recovery option is what Microsoft recommend. For recovery purpose shouldn't this be at least Mirror. What is ...
0
votes
0answers
39 views

Accidentally drop table in database that has no backup [duplicate]

One Table is accidentally dropped in my database. I had not taken backup of the database since it is in development environment. I want to restore the table. Kindly please provide any solution. The ...
10
votes
1answer
756 views

How to “self audit” all of the SQL Server licenses at an organization?

So I have started a new job with a nice non-profit organization, which has been around for a few decades. They have a wide variety of SQL Server editions and versions (ex: 2005 Express to 2016 SP2 ...
-2
votes
1answer
16 views

Error in stored procedure on Phpmyadmin [on hold]

I have created this simple StoredProcedure in the Phpmyadmin SQL console but I keep getting syntax errors. I have looked at similar created StoredProcedures but can't seem to figure out what the error ...
0
votes
1answer
28 views

Slow Sum/Group By over large amount of rows

Please can you help with a slow query that we have with a large amount of data (~500K rows). We're on Postgres 10. SELECT mike_romeo, SUM(hotel) as counts FROM whiskey_lima WHERE yankee = 'kilo' ...
0
votes
0answers
8 views

How to know if a sql dump has been already imported?

I am writting a Dockerfile where the entrypoint script checks if any *.sql file present inside initdb.d dir then it import that *.sql file. However as this import mechanism is a part of entrypoint ...
0
votes
1answer
23 views

How to query data of changes in a column in MySQL

I want to write a complex query for me. My example records: | id | seed | run_date | value | |-----------------------------------------------------| | 1 | 12 | 2019-...
0
votes
1answer
10 views

Error parsing DDL for schema.table in mysql workbench

I have production dbs using Mysql 5.5.31-0ubuntu0.12.04-1 (I know its old, will be upgraded soon). I used a too advanced version of mysql workbench which allowed me to add a comment to key. (Now ...
0
votes
1answer
16 views

Joining three tables with intermediate nullable option and filter condition

Sorry if this seems like a dumb question, but I've read through tens of answers and tried many different options, yet my query results were either overly inclusive or exclusive. The basic scenario is,...
5
votes
2answers
437 views

Nested Loop has very low estimates due to skewed data

On SQL Server 2016 SP2 we have a query that has a very low estimate on the nested loop operator. Due to the low estimate this query also spills to tempdb. If I'm correct SQL Server 2014+ uses Coarse ...
1
vote
1answer
9 views

Report-builder: How can I show the labels in a List when the dataset is empty?

An equivalent question was answered for a tablix, but I would like to do this for a list: "how to keep the structure of a tablix where there is no data". I am showing about half a dozen fields in the ...
2
votes
1answer
44 views

Extreme performance loss due to moving from Mysql 5.7.23 to Mysql 8.0 (status, config included)

Been sitting on this for 12 hours straight (it's noon now, so I will read/respond when I woke up again). I have made the big fault of recommending a database upgrade of our productive environment to ...
0
votes
1answer
18 views

Logical Replication to different schema name

We have PostgreSQL 11 database with structure one database one schema (public). dbname : production_one schema : public dbname : production_two schema : public I would like to set up a logical ...
0
votes
1answer
14 views

Ops Manager Install in Windows and connect to mongod instance started in linux

Can we install ops manager in windows and connect to mongodb instance started in Linux operating system? Does ops manager installation on windows require separate installation of mongoDB instance on ...
0
votes
2answers
22 views

Select first and last row based on each user id mysql

I want to select every first and last event of every user id, if no first event exists,then just the last event instead . tried using partition over , but i am getting first 2 events instead. Input: ...
0
votes
0answers
23 views

call store procedure from sql devloper

How to call store procedure from sql developer which is using two out parameter 1. Number 2. SYS_REFCURSOR i have tried this way in sql developer but giving error, the same i can run from sql ...
0
votes
1answer
18 views

MongoDB - Database vs Collection memory-wise

Lets say for example i need to have 5 collections, each collection is about 10GB. What is the difference in performance, with emphasis on memory usage, between assigning each said collection to a ...
0
votes
0answers
16 views

Sum multiple rows and return sorted result

I have a database that holds players wins on the game, but as it has multiple arenas, there are multiple win variables too, I tried the following: SELECT winsdm + winsodm + winsrace + winsshooter AS ...
0
votes
0answers
11 views

Error Code: 1845. ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE

I upgraded to MySQL 8.0.13 and tried to add a new field using ALGORITHM=INSTANT but got the following error: Error Code: 1845. ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=...
1
vote
1answer
23 views

LEFT JOIN based on content of a field in matched rows

Generically, I'm attempting to take a table and join it with another table but only on rows that have a specific value in one of the fields. Specifically, I have a users table and a contact table. ...
0
votes
1answer
28 views

How to use JSON path wildcards in JSON_QUERY?

For a minimal working example say I have a column storing JSON with a structure similar to the following. DECLARE @json NVARCHAR(4000) = '{"Dims": [ {"Name":"Apple", "Baking": ["Pie",...
0
votes
0answers
15 views

Integration Services Catalog folder permissions changed

Question: Do any of the SQL Server systems tables in either SSISDB or MSDB contain information that would allow me to discovered which users are making changes to folder permissions in the Integration ...
0
votes
0answers
16 views

MYSQL - Database check

We just downloaded and installed Mysql 8.0 Community edition. what I am trying to do is check all the tables in the database for errors. I only want output if there are errors. It's like DBCC('...
0
votes
1answer
31 views

Converting MySQL 5.5 datetime binary to MySQL 5.6 (math problem)

I received a database "backup" of binary files (.frm and .ibd) that were copied from a MySQL 5.5 install. Unfortunately, I was unable to get these files to import into a MySQL 5.5 vm that I created. ...

15 30 50 per page