All Questions
103,251
questions
4
votes
2
answers
5k
views
Efficient database design for multiple price based on quantity and attribute
I have a data sample for products with different prices depending on quantity and attributes. The price might be updated occasionally (not too frequently).
PID | Name | Type | Colour ...
- 141
0
votes
1
answer
3k
views
Building an bridge table from existing one with the use of the SELECT INTO
I have one big source table in which attributes are mixed with measures together. I want to make a bridge table from them to allow analysis of the data.
Problem (in my case N is actually 22, ...
- 55
2
votes
2
answers
127
views
Correct organization of the database structure and selection
Sorry for newbie question.
For example, there is some kind of abstract device, we will call the Device class and the tasks for it: Task1, Task2, Task3.
For concreteness, I will describe everything in ...
- 23
4
votes
3
answers
24k
views
Grouping data into 5 minute intervals within a time range
I am trying to prepare data for a candle stick chart. I have some records on MySQL database as shown below:
| Rate | Date |
|----------|---------------------|
| 0.001111 | 2017-11-...
0
votes
2
answers
909
views
Will NVME SSD speed up heavy queries, e.g. selects at large offsets / table scans etc?
If the database data will be on NVME SSD which let's assume is 4x faster than an ordinary SSD will queries like SELECT something OFFSET 100000 and the like typically also be much faster (of course ...
- 237
3
votes
2
answers
8k
views
How to create function with Return Type as (TABLE student_name string)
I am creating function using pgAdmin4 and the CODE Basically something like
BEGIN
RETURN QUERY
SELECT * FROM teest."students";
END
I am just wondering, how do I set the TABLE ReturnType as
...
- 31
0
votes
1
answer
145
views
MySQL table partition truncation not to propagate on a slave
I have a table now. It has 7 seven partitions, and each day I truncate a partition(not delete, just empty the partition).
I have a use case where I would like to have a full backup of the table as ...
- 101
1
vote
5
answers
505
views
Database Normalization Causing Inefficiency
Note: I am relatively new to managing an SQL database. I have done my fair share of reading, research, and experimentation, but it is possible that I am missing some fairly basic knowledge. I'm also ...
- 135
0
votes
1
answer
944
views
mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)
I need to dump a database from a server that uses the old password hash. It's a shared server and I am unable to change the user's password to the new hash.
Attempts with MySQLWorkbench fail with the ...
- 101
0
votes
1
answer
76
views
SQL Server incremental Log backups
We are taking COPY_ONLY (non-truncating) transaction log backups every 2 hours. Our requirement is to take the incremental log backup every 2 hours and not cumulative. I did not find an option in ...
- 1
4
votes
2
answers
7k
views
checking availability of rooms
I have 3 table in my sql database:
Table Room
roomnumber roomtype
1 DeluxeRoom
2 DeluxeRoom
3 DeluxeRoom
100 StandardRoom
...
- 41
12
votes
6
answers
12k
views
High CPU system time usage on MySQL server
A little backstory, some time ago we started to experience high CPU system time on one of our MySQL databases. This database was also suffering from high disk utilization so we figured that those ...
- 119
-5
votes
1
answer
190
views
What does `QUOTED_IDENTIFIERS` do, exactly?
There is a really obnoxious feature in SQL Server called QUOTED_IDENTIFIERS and I wanted to know more about it. In short, with the option set to ON, which is not the default with the Linux client, ...
- 63.1k
-1
votes
2
answers
78
views
Distinct Examples from one Table SQL Server [closed]
I have a item file with one item per record and a detail file with many customers with many descriptions. Each customer can have many items. Each item can have many customers.
File 1
...
- 195
1
vote
1
answer
485
views
Migrating from MySQL RDS to Aurora RDS with CNAME record
I understand migrating to Aurora is simply a matter of creating read replica from MySQL source and then promoting when caught up and stopping writes on MySQL RDS previous master.
I use Route 53 DNS ...
- 701
0
votes
1
answer
211
views
Amazon Aurora MySQL conditional comment queries
How does Amazon Aurora handle conditional queries based on MySQL version? I was unable to find any documentation on this.
For example, the code below inserts into a database for MySQL 5.6.4 or newer. ...
- 701
2
votes
1
answer
916
views
Reading decimal mark comma with an external table
SQL Server 2016, SQL Server Management Studio (SSMS). Creating external tables with Transact-SQL and Polybase. How can we read decimals with a decimal mark, comma instead of dot?
When using float and ...
- 139
4
votes
1
answer
16k
views
Inner join returns duplicate results
I need to select data from 2 tables based on 1 filed UID and display some from both tables.
Data from first table "a.symbol, a.order_type, a.price_open, a.time_open, a.size", and data from second ...
- 143
1
vote
0
answers
90
views
EMC Recoverpoint works for database?
As a solution for replication, to protect VM, LUNs and recovery including database, EMC Recoverpoint was presented by our vendor.
But in EMC paper's, the only one database related example ilustrates ...
- 441
4
votes
1
answer
8k
views
how to resolve "InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table" in mysql
I am continuous getting above error in mysqld log. Please tell me what is this error about and how to solve it. I am using mysql 5.7 (not mariadb)
- 41
2
votes
2
answers
4k
views
How do I full text search on a datetime column?
I created is a DATETIME column. I also tried to change all Collation's of that table to the same utf8 type but it wouldn't stick. Is it possible to do a FULLTEXT search on a DATETIME column? (%like% ...
- 147
0
votes
1
answer
440
views
How to speed up group by
I am trying to speed up the following query, which currently takes 14 seconds:
SELECT
COUNT(*) AS employee_count, e.jurisdiction_id, e.year
FROM
employee_employee e
GROUP BY
e.jurisdiction_id, ...
- 125
0
votes
1
answer
577
views
Assign object owner to a role other than the creator automatically on creation for a database
Background:
I'll start by saying I'm fairly new to postgres, I generally work with web apps and I know my way around SQL but I'm new to both postgres and database administration. I have however read ...
- 335
-4
votes
1
answer
210
views
How do I ensure a relationship is commutative and unique? [closed]
In PostgreSQL, we can ensure the relations stored inside a table a commutative-ly (or bidirectional etc) unique. For instance,
CREATE TABLE foo (
a int,
b int,
UNIQUE ( greatest(a,b), least(a,b)...
- 63.1k
1
vote
2
answers
604
views
RHEL 7.4, heavy workload(s) and SWAP usage
This might not be the correct board for this, but since I'm a DBA and I'll ask here.
I have several RHEL 7.4 servers, running a mix of MariaDB (10.1, 10.2) and MongoDB (3.4). The problem was ...
- 11
1
vote
2
answers
2k
views
What is the formal definition of a Primary Index and Primary Key?
The below is from Fundamentals of Database Systems (7th Edition)
17.1 Types of Single-Level Ordered Indexes
There are several types of ordered indexes. A primary index is specified on the ordering ...
- 63.1k
0
votes
0
answers
76
views
Queries running fast most of the time but >100x slower other times
I know this is a very generic question, but if someone can point me to what else I should investigate/check I would greatly appreciate it.
We have a view which runs around 15 seconds most of the time ...
- 11
0
votes
2
answers
85
views
Retrieve Latest (max) date
Using SQL Server 2008R2, I am trying to pull the latest assessment date for a patient. I have it pulling the correct data I need, but it is pulling all dates. For instance if a patient has an ...
- 3
0
votes
1
answer
2k
views
How to dynamically add/remove role members from SSAS 2016 Tabular model
I am developing a number of tabular models, which currently have a limited number of users assigned to roles within each. At the moment, with the exception of 1 or 2 cases, the majority of the users ...
- 382
2
votes
1
answer
64
views
It is possible to extract Extented Properties using SQL Developer Data Modeler?
I'm using SQL Developer Data Modeler in a oracle server. No problem, I can connect to SQL Server, list tables and generate a really good report. But what I need is to extract the extended properties ...
- 7,386
1
vote
3
answers
87
views
ZipCode to ZipCode Storage [closed]
I'm working on a project that requires me to allow users to store/edit properties of what they call "path". A path can be any Zip Code to any other Zip Code.
The system they have now, utilizes one ...
- 13
3
votes
1
answer
422
views
Why does an index scan read more pages than exist in an index?
I've been trying out running some queries for a testing scenario and I noticed that when doing a full-scan of a nonclustered index, SQL Server reads more pages than actually are in the index.
As you ...
- 943
2
votes
2
answers
2k
views
MySQL InnoDB .ibd file growing extreme
We have a database with a few larger tables were one (yes only one) table grows extremely. It contains only 50 gigabytes of data while the .ibd file (we use innodb_file_per_table) takes up about 1.2 ...
- 21
0
votes
0
answers
67
views
Suggested Min/Max SQL Server memory [duplicate]
I've inherited multiple servers, each containing one database and one instance of SQL Server 2008 R2 SP2. Each server is set to use 12GB min/max memory and has Lock Pages in Memory enabled. The ...
- 135
3
votes
3
answers
4k
views
SQL Server 2016 maximum server memory
Does max server memory for SQL Server 2016 contain buffer pool plus in-memory columnstore plus in-memory OLTP, etc. cache?
0
votes
1
answer
45
views
Mysql Update Query is not Giving Desired Result
In My Php Code I Have Query As
SELECT COUNT(id) AS todayleads
FROM `lead_data`
where `date` = '$todate' AND lead_status = 1
AND uid = '$userid' OR assign_to = '$userid'
When I echo Query it ...
- 15
-1
votes
2
answers
531
views
PostgreSQL insert not occurring serially [duplicate]
I have come across a problem where PostgreSQL does not insert records at the end of the table (according to id, which is the primary key)
So if, for example I have ids like 1521, 1522, 1527, the id ...
- 99
0
votes
0
answers
53
views
Error starting mysql service
I'm having trouble on starting MariaDB instance. Here is the error I got: https://pastebin.com/RsdMxYRY. Any help is appreciated. Thanks.
- 113
1
vote
0
answers
46
views
how to show row values in column?
i have a database sample that looks like this:-
as you can see my student master id is repeated. i want student master id to appear only once (in one row) can corresponding roll numbers to appear in ...
- 111
0
votes
1
answer
318
views
How do I start over? (newbie goof: now I need to start fresh with MySQL) [duplicate]
I'm just starting with MySQL (version 5.7.20 on Xubuntu 16.04), and in playing around with it I changed the root password. Now I can't log in as root -- I either forgot the password or didn't set it ...
- 101
1
vote
2
answers
6k
views
postgres user is not superuser
Im having some problems with my postgresql server. In particular, some databases cannot be dropped.
On further inspection, I see that the postgres user is not a SuperUser.
There are two other users ...
- 111
1
vote
1
answer
1k
views
Append timestamp to powershell
I am trying to capture some data on CPU activity on a SQL instance.
Get-Process | Sort CPU -descending | Select -first 10 -Property ID,ProcessName,CPU
The PowerShell above give me the following ...
0
votes
1
answer
125
views
Move local SQL Server DB to hosted SQL Server
I have a SQL Server database here that contains the info for the Gemini Issue Tracking software. It is running on a Windows XP system and I have SQL Server Management Studio (SSMS) on that system. I ...
- 103
2
votes
2
answers
6k
views
DROP DATABASE fails due to foreign key constraints
This is weird and I haven't ever encountered this.
I'm simply dropping a whole database.
$ mysql -u $RDS_USER -p$RDS_PASS -h $RDS_HOST -e "DROP DATABASE dev_test"
And yet it fails with this...
...
- 123
0
votes
1
answer
88
views
InnoDB: Is a covering index in the form of (PK, col1, col2, col3) redundant?
Table structure for config:
property (primary key)
value
description
Query:
SELECT property, value FROM config
If I put a covering index on (property, value) the optimizer still chooses the ...
- 235
1
vote
1
answer
4k
views
Issue installing SQL Server 2012 SP4 in a cluster - missing ‘vc_red.msi’
Installed SQL Sever 2012 SP4 (from SP3) on one node of a two-node SQL Server cluster without issue. On the other node it is exiting out of the install with the following message:
The required MSI ...
- 1,784
3
votes
1
answer
9k
views
Granting [NT AUTHORITY\SYSTEM] sysadmin on SQL server - Compliance/Best Practice
New to security in general. We have a third party service which runs on SQL server host and under Local System. Since it is running under Local System, they have always given sysadmin privilege to [...
- 31
1
vote
1
answer
2k
views
MySQL for loop to show grouped results
I'm trying to show results for 12 months using this query:
SELECT
COUNT(DISTINCT user_id) as customers,
SUM(amount)*.01 as amount,
SUM(amount_refunded)*.01 as refunded,
MONTH(MAX(...
- 379
2
votes
3
answers
7k
views
Preparing a cluster installation fails at Setup Support Rules Step (Not Clustered or the cluster service is up and online)
Today I was trying to install a SQL Server 2008 R2 Cluster environment on Windows Server 2012 R2 servers and after doing all the necessary steps to prepare the cluster (like Creating an iSCSI Virtual ...
- 773
2
votes
2
answers
2k
views
What's the best way to use a gist index on tsrange to check if a time occurs after the range?
table (simplified)
Table "public.events"
Column | Type | Modifiers
-------------...
- 817