All Questions

0
votes
0answers
4 views

MySQL replication process parallel with user process?

I have process which executes query like below on Mysql(InnoDB) slave INSERT ignore INTO user_profile_temp (some_fields) select some_fields from user_profile user_profile is bigtable of 8GB ...
0
votes
0answers
3 views

Prevent read access on primary AG replica, but allow it on the readable secondary

We have a unique situation where we want to allow users to query a readable secondary replica of a database with SSMS for adhoc reporting, but not allow them to ever read from the primary replica. We ...
-1
votes
0answers
5 views

How can I get the max value for each column?

If data of table like this, [co1:varchar][col2:varchar][col3:varchar][col4:timestamp] And, Follow is data example. 224 / 755 / 542 / 2019-05-23 10:12:23 230 / 741 / 530 / 2019-05-23 10:12:23 221 /...
0
votes
0answers
4 views

Understanding MAXDOP behaviour for queries going parallel

For my current server as below 4 Sockets with 10 cores per socket and 20 logical processors per socket. Total 80 per SQL licensing: 4 NUMA nodes with HT enabled I've set CTOP 50 and MAXDOP 8 on ...
0
votes
0answers
4 views

System-wide startup file for Postgres 10 on Ubuntu 18.04

When I log into postgres under my personal shell account, my .psqlrc file runs a series of meta commands to set up what is for me an optimum working environment. I'd like to set that same environment ...
0
votes
1answer
6 views

How to update JSON using logic equivalent to WHERE clause

My question is similar to another (and also another for postgresql) except in the other question the person wants to update values for a specific key. In my case, I want to detect elements having a ...
0
votes
0answers
10 views

I am a newbie and I need a query that will query a certain table/column from all the database

My table name is File Column name is Location..I need the records that is in the column
1
vote
1answer
19 views

How do you retrieve the identity value of a row inserted from the OUTPUT of an UPDATE statement?

How do you retrieve the identity value for an inserted row when that row is inserted from the OUTPUT of an UPDATE statement? Neither @@IDENTITY nor SCOPE_IDENTITY() appears to be set properly. ...
0
votes
0answers
5 views

Join on unnested integer array column and count occurrences in array postgresql

I am trying to join two tables after I have unnested one of them. The tables are: CREATE TABLE teachers (id SERIAL, first_name VARCHAR(60), last_name VARCHAR(60)); CREATE TABLE training_modules (id ...
0
votes
1answer
5 views

PostgreSQL Trigger to override insert on table with inheritance

We have a table documents that is partitioned by time into child tables like documents_2019_01, documents_2019_02. We do this using a BEFORE trigger on the parent documents table to insert into the ...
1
vote
1answer
10 views

SSAS Tabular Model or SQL Relational Database for new Datawarehouse? Is SSAS still a good to learn technology?

I am in the process of creating a data warehouse using SQL Server for my company. I have created a POC with a simple SQL Relational database with a few(about 10) stored Procs for the ETL process. Now ...
0
votes
1answer
17 views

Oracle - large table with large IN

Table students has about 10 million records; ID is indexed. Student grades has 20 million records - student_id is indexed. I am querying about 20,000 students by their ids ids with a join between ...
-1
votes
0answers
12 views

Design schema to handle different data types (avoiding EVA anti-pattern)

So I have a table that stores ML model metadata. I have a table that holds my model. I need to also include hyper parameter information (epochs, batch_size, weight_decay, classes etc.). I don't want ...
1
vote
1answer
14 views

Basic information splitting and table design in a database about people, accounts and transactions

I am having trouble deciding which design would be considered a better practice. Say, for example, I have two tables: TABLE Person pid int primary key name varchar age int bank_account varchar ...
0
votes
0answers
44 views

How to unit test a stored procedure that has a temp table using tSQLt framework

I have stored procedure that creates a temp table #line(can't share the SP because of company privacy). I am using tSQLt framework. I am not sure how to address this situation. Please help tSQLt ...
-1
votes
1answer
16 views

How to check if a start and end date interval is within the range of another start and end date interval

As the title says, I need to check if a start and end date period is within the range of another start and end date period. I don't know if this needs to be done in a trigger or a stored procedure. ...
5
votes
1answer
68 views

Mysterious procedure calls without parameters - but no exceptions generated

I've been tracing a handful of stored procedures with EventClass RPC:Completed that are intermittently called from .Net SqlClient Data Provider without required parameters - just "exec SprocName". ...
0
votes
0answers
17 views

Store large amounts of text is Postgres

I have a large text corpus that I am storing in Postgres. A table articles has all of the metadata for each article, about 19 million rows. It's primary key is articles.doc_id. Another table (texts) ...
0
votes
1answer
26 views

Is it better to install a very large Postgres database (billions of rows) in a real or a virtual machine?

From my common sense, my physical machine hosting a very large Postgres database (for big data) should be real rather than virtual or container. Some people say it is better to install on a virtual ...
1
vote
1answer
28 views

Can the LOG file fill up from index rebuilds where the indexes are not fragmented?

Working on SQL Server 2014. I have noticed that a log file (60GB) on a database has filled up within the past day. To my knowledge, I am the only developer using this database and I have not been ...
1
vote
1answer
22 views

Index on JSONB not improving query speed

table structure Column | Type | Collation | Nullable | Default ------------+--------------------------------+-----------+----------+------------------------...
1
vote
0answers
12 views

Asynchronous availability groups not synchronizing on SQL Server on Ubuntu

I installed SQL Server 2017 on Ubuntu and setup an availability group with cluster type = "None". I set the "Availability" Mode to "Synchronous commit" and tested it. It works fine. All transactions ...
0
votes
1answer
16 views

Correlated Subquery can't access column from outer query

Given the following query which contains a subquery: SELECT COUNT(DISTINCT sc.caseID) AS 'TotalCases', (SELECT COUNT(DISTINCT se1.caseID) FROM scEvent AS se1 INNER JOIN scCase ...
0
votes
1answer
14 views

Disable Oracle substitution parsing

I need to update a string (VARCHAR2) value in an Oracle database which contains Javascript code. The update script contains the following string: c===l&&p(a,b) which when executed from "...
1
vote
1answer
18 views

How to TRUNCATE table if the table exists

I want to run a few TRUNCATE statements. But in between some of the database doesn't exists. But SQL stops the rest of the queries. Is there any way to ignore the error and continue to execute the ...
1
vote
0answers
35 views

How avoid updating unnecessary columns in Update statement?

I have an Update statement which update several columns. Some of those columns are nvarchar(max) type and not always need to be updated. I would like to avoid updating unnecessary columns, so, the ...
2
votes
0answers
11 views

SQL Server Audit File Metrics Information

I'm currently working in an environment that requires we enable SQL Server Audit, enterprise-wide. We generate .sqlaudit files as we have retention and access guidelines, and I'm finding that we need ...
-2
votes
0answers
17 views

Can we create read only service for Oracle primary database [on hold]

We have 1 Oracle primary database 12c without any active standby. Could we cra read only service ?
0
votes
0answers
22 views

SQL Server - CASE for multiple tables

hope I can explain myself right in this question. I have three (3) tables from which I have to build a report, this is the data selection process I need to do in order to correctly output the data to ...
-3
votes
0answers
21 views

Open source database for testing algorithm [on hold]

I have developed algorithm which probably would optimize query executions and accuracy of results. I am seeking for some open source or free database systems where I could run this algorithm. Even ...
0
votes
0answers
3 views

VS 2019 - Schema Compare - “The certificate chain was issued by an authority that is not trusted” when TrustServerCertificate=True

I'm trying to do a schema compare in VS 2019 with Active Directory Password Authentication. The account I'm using works in SSMS, but fails in VS with the following error, even when ...
0
votes
1answer
14 views

Mysql not writing to binlog

Mysql is not writing events to binlog, I have tried change the binlog configurations, but can't solve. Conf: server_id = 2 log-bin = master binlog_format = row replicate-do-db = our_db log-slave-...
0
votes
1answer
32 views

SQL Server 2017 Configuration Problems

On my Development Server I have two instances configured; the default and one for our ERP system testing. I can connect to the ERP instance remotely with no problems. However, I cannot connect to the ...
-1
votes
0answers
20 views

Migrating Data to another database and changing all ID values

I'm currently seeking some advice on a project that the team and I have been asked to deliver on. The project is to migrate the data for one of our customers, from a production DB in the UK, to a ...
3
votes
1answer
52 views

why it is doing index seek

I created a sample table as below CREATE TABLE [dbo].[StatisticsDemo]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NULL ) ON [PRIMARY] Then I inserted below data as below: ...
0
votes
0answers
10 views

Lock wait timeout in error.log

Is there a way to put messages like 'Error Code: 1205. Lock wait timeout exceeded; try restarting transaction' into error.log in MySQL? MySQL: 5.7.26 | log_error | /var/log/mysql/error.lo ...
0
votes
1answer
18 views

In a replication setup, does corrupted data also gets replicated?

Say one of the tables got corrupted, will that corrupted table still get replicated to the other DB? Or, will the replication stop to prevent the other DB from being corrupted too? Note: Question is ...
1
vote
0answers
18 views

Levenshtein distance on MariaDB

is there any official or "de-facto" official implementation of Levenshtein distance on MariaDB 10.2 or 10.3? I found this https://github.com/juanmirocks/Levenshtein-MySQL-UDF, but I want to be sure ...
0
votes
0answers
10 views

R Studio Error '/ not defined for Date Objects '

Bit of a newbie to R. Get this error running a regular report that other guys in my team run ok. Went through and compared librarys were ok ? Customers1$date <- Sys.Date() Customers1$MonthsLive &...
0
votes
0answers
34 views

Alter view after table drop

I want to alter the view for the drop tables. For an example: View: create view View1 as select * from table1 union all select * from table2 union all select * from table3 union all select * from ...
0
votes
0answers
13 views

Undone the table partition for a particular year

I have implemented partitioning for a particular table, in such a way that user can partition min 1 and max upto 52, to a particular table for a particular year. Now, after performing partitioning, I ...
0
votes
1answer
17 views

how to update a column from another database?

I would like to update a field that has been delete from the backup database. how can I update the field from an other database
0
votes
0answers
16 views

[SSISDB].[internal].[cleanup_server_retention_window] Foreign key Violation

I have setup a job to cleanup SSISDB with predefined storeprocedure "[SSISDB].[internal].[cleanup_server_retention_window]" but sometimes i get following error. After that i have to do manual cleanup ...
1
vote
2answers
17 views

postgresql 9.* - user cannot SELECT from VIEW inside a SCHEMA as DEFAULT PRIVILEGE

hope you can help me. I have 3 users First is me, the db admin. I run the GRANT queries, I can do anything. (name doesn't matter now) Second is the data manager user. = manager_u Third is the data ...
0
votes
1answer
28 views

ORA-28040: No matching authentication protocol in Eclipse project

I recently tried to connect my java program to my Sql data base made on SQLDevlopper, but after downloading & installing in my Eclipse project the ojdbc6 driver I still have the "ORA-28040: No ...
1
vote
1answer
28 views

Subquery in where clause forces seq scan instead of using trigram index

I have a query that is supposed to use a bunch of trigram indexes to speed up ILIKE clauses. This works as long as there isn't a subquery in one of the where terms. I've simplified my query and can ...
2
votes
0answers
12 views

Query plan (JSON format) in pgAdmin not showing completely

I would like to use the tool Postgres Explain Viewer (PEV). For it I need to paste the execution plan in json format. If I try to get the execution plan in pgAdmin with this query: EXPLAIN (ANALYZE, ...
0
votes
1answer
19 views

SQL substract values from same column different rows

I have above mysql stock table where both inward and outward data are stored and identified each type by type column (i and o). I want to build a query to retrieve balance quantity(qty) per each ...
0
votes
1answer
23 views

MySQL/innodb trying to lock uncommitted row from parallel transaction, deadlock in result

MySQL 8.0.16, 2 simple RC transactions, each trying to insert and delete rows: CREATE TABLE test1 ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(100), PRIMARY KEY (id) ) ...
0
votes
0answers
15 views

Help to troubleshoot pgadmin4 and /svn/repo 403 error

I installed postgresql db and pgadmin4 3 months ago, configured both on centos7. I used to access pgadmin4 from random remote machines every day by, xxx.xxx.xxx.xxx/pgadmin4 and xxx.xxx.xxx.xxx/svn/...

15 30 50 per page