2
votes
1answer
56 views

Reading from subscriber during Transactional Replication

When using Transaction Replication, are there any restrictions around selecting data from the subscriber whilst transactions are being applied to it? i.e. can selects be made against tables to which ...
1
vote
0answers
160 views

PostgreSQL 9.3 slaves don't recognize hstore as a type

I am using the postgres built in replication with two slave nodes and a single master. On the master node (as well as the slaves) the hstore extension is installed. But whenever a query using hstore ...
2
votes
3answers
2k views

How to do a one-time log shrink on a database with transactional replication

I have inherited a 10 GB database with an 80 GB log [only 3% in use according to DBCC SQLPERF(logspace)]. It seems safe to assume the extreme log growth was due to problems from long before I was ...
0
votes
4answers
136 views

Move MySQL database to another server

I need to move 20 Gb MySQL database from on Debian server to another. The problem is that there is no way to create its backup since there are almost no free HDD space on the source server. What would ...
1
vote
1answer
204 views

Why does IN (subquery) perform bad when = (subquery) is blazing fast?

Have a look at these two queries: Simple: mysql> SELECT * FROM omgbatch JOIN omgoutput ON (idomgbatch=omgbatch) WHERE idomgoutput = (SELECT DISTINCT lastoutput FROM omgenvelope WHERE ...
4
votes
1answer
3k views

How to remove known elements from a JSON[] array in PostgreSQL?

I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
2
votes
3answers
2k views

postgreSQL 9.3 streaming replication delay

I'm running a fairly simple WAL streaming replication setup between two postgreSQL 9.3 servers that are on a private 1Gb LAN, but recently I've noticed that there is an increasing amount of ...
1
vote
1answer
62 views

restore database using bak file - resorting to old date?

Last night I made a full backup of my DB in SQL server management, when I restore the DB instead of being complete - it is missing all its data from the last few month. The full backup was taken ...
-1
votes
4answers
7k views

Host is not allowed to connect to MySQL

I have already uploaded some files in the free web host I chose and created a database there using phpMyAdmin v2.11.4 Also, I have Googled first this question before posting this here but I don't ...
0
votes
1answer
95 views

Apply DB2 logs from production to DR

Is it possible to Apply DB2 9.5 logs files from Production site to DR site? Or do I have to do Backup/Restore and rollforward ...? I copied log files from production site to DR and issued the ...
-1
votes
1answer
352 views

Lost connection to mysql?

We're receiving the following error on every 1 hour. Lost connection to MySQL server at 'reading initial communication packet', system error: 115 "Operation now in progress" Any clue ?
1
vote
0answers
35 views

Host is not allowed to connect to MySQL [duplicate]

First of all I'm acknowledging you that I am a completely newbie in this field. The field I'm referring to is the summation of these things: Accessing files on different computers or over the ...
0
votes
1answer
9k views

Trigger to update a date field on one table when another table is updated

I want to be able to update a date filed in one table when another table is updated. I have the following two tables: Transdetails, Customers. In the Customers table I have a field called ...
12
votes
3answers
839 views

Get streak count and streak type from win-loss-tie data

I made a SQL Fiddle for this question if that makes things easier for anyone. I have a fantasy sports database of sorts and what I'm trying to figure out is how to come up with "current streak" data ...
1
vote
3answers
1k views

MySQL: How to avoid case sensitivity in SQL queries when migrate from Windows to Linux?

I have migrated my Database from Windows to Linux server. In many places, query has been written without focusing their cases (uppercase / lowercase). So, these queries are not getting executed and ...
0
votes
1answer
8k views

How to calculate time difference in sql

I have one table named test. In that table I have a column named test_time of type DATE. I want to calculate the time difference between test_time and sysdate in minutes. select 1440 * ...
2
votes
2answers
325 views

Count where previous record exists in either of two tables

I'm trying to COUNT records on one table where both the counted records and their "previous" records exist either in the same table or on another. The table that I'm sampling has a composite PRIMARY ...
2
votes
1answer
531 views

MySQL Seconds behind Master fluctuation

I have two servers set up in Master-Master with one additional Slave connected to the first master. Lately I've started getting alerts from monyog that Master 1 has a seconds_behind_master value of ...
2
votes
0answers
132 views

Postgres: missing disk space after truncate and re-insert

We've got a IP address range dataset stored in a table that was truncated and then re-populated with a slightly modified version of the dataset (almost identical in size). However, after truncating ...
6
votes
1answer
767 views

high background flush avg mongodb

similar question: High Global Lock % on Mongodb Overview We have a production setup replica set in v2.4.8 mongodb running on five 4-core, 28gb RAM VMs with standard azure datadisk HDDs running on ...
0
votes
1answer
299 views

SQL Select query to include each row a number of times

This is probably easy to do (I hope), but my experiments with OUTER JOIN and CROSS JOIN etc do not give the results I am after, so here's the question: I need to do do a projection on fields in a ...
3
votes
1answer
328 views

Multi-Site clustering on Windows 2012 for SQL Server 2012

I am hoping someone can point me in the right direction here. We are trying to do a proof of concept for a 3 node multi-site cluster on Windows Server 2012 R2 for our SQL Server environment. There ...
1
vote
1answer
717 views

Upgrading using pg_upgrade on windows fails

When upgrading from postgres 9.2 to 9.3 by following the upgrade procedure found in PostgreSQL 9.3.4 documentation about pg_upgrade, the following command fails: RUNAS /USER:postgres "CMD.EXE" ...
1
vote
4answers
368 views

Mass data upload from Excel - technology

I am a Developer seeking help in terms of DB technology for a specific requirement. I have done some research but can not conclude with list. Requirement: There will be a web application which will ...
2
votes
3answers
691 views

Moved Model Database and now my SQL Server doesn't work

I moved my Model data and log files: ALTER DATABASE Model MODIFY FILE (NAME = 'modeldev', FILENAME = 'D:\Data\model.mdf') ALTER DATABASE Model MODIFY FILE (NAME = 'modellog', FILENAME = ...
8
votes
2answers
2k views

Alternative to EAV for dynamic fields in a star schema data warehouse

I need to support dynamic fields and values in a big datawarehouse for storing API requests log, my user case is that I need to store all API requests query string and able to perform query against ...
1
vote
1answer
130 views

Rerunning query in the same connection takes significantly longer

I am having a weird issue when a running a query that joins some semi-large tables (one table is about 2 million rows and the other about 21 million) together. What happens is the query does not run ...
1
vote
1answer
311 views

Consolidated realtime mysql backup with master/slave setup or binlog

I have 20+ mysql servers and their have low write requirements. So, I want to see if any way I can replicate the data/binlog from these servers to a single slave so realtime backup purpose. If I ...
1
vote
1answer
453 views

Why does innodb_flush_method = O_DSYNC provide such a performance boost with SAN-backed storage?

I've seen some people suggest that O_DSYNC should be used with a SAN. The MySQL docs have this to say about O_DSYNC in general: In some versions of GNU/Linux and Unix, flushing files to disk with ...
0
votes
1answer
1k views

Export, based on query, into INSERT text file

I need to export some data into a file. The data are selected with a query that will often be made of multiple joins. I want the import operation to be as easy as possible. I was thinking of building ...
1
vote
1answer
40 views

Can an index refer to only one or a subset of possible values?

I am using Postgres. The table looks something like this: tran_id SERIAL PRIMARY KEY, account_id integer NOT NULL CONSTRAINT fk_account_id REFERENCES base.account(account_id) ON DELETE CASCADE, ...
0
votes
1answer
125 views

Replace String within Serialized data

I am moving my forum from one domain to another and need to replace all instances of the previous domain with the new one. In looking at the mysql data, many of the matches are going to be in the ...
3
votes
1answer
290 views

How to Substitute Variables in PostgreSQL

The given Oracle query inserts data into table by substituting variables. How I achieve the same in PostgreSQL? INSERT INTO control_threshold ( threshold_id, group_name, description, sql, ...
0
votes
2answers
2k views

Difficulty with Postgis extension on Ubuntu 14.04

I have a updated Ubuntu 14.04 running postgresql 9.3 (installed using the tutorial from https://wiki.postgresql.org/wiki/Apt). When I access my database through pgadmin and go to the console and ...
0
votes
0answers
78 views

Sorting the view on which the stored procedure runs

I want to sort the view on a particular column before my stored procedure runs. Is there a way to do this through the stored procedure? Can anyone show me a MySQL example? This is what the stored ...
1
vote
1answer
44 views

InnoDB buffer pool active pages

I'd like to optimize (lower) the InnoDB buffer pool size. How can one find out how many distinct pages are active in the InnoDB buffer pool? A page could be made young twice in a while (see SHOW ...
3
votes
2answers
377 views

PL/pgSQL fuction to find circular references

I have two tables ID Task 1 1 2 2 3 3 4 4 Col1 depend 2 3 2 4 3 1 4 2 ID and Col1 are related through FK constraint. I want to find out all circular references. Here ID ...
0
votes
1answer
2k views

Keyword Search a single SQL Server table for multiple search terms and create a new table from results?

I am not a database administrator. Most of my experience is with MySQL. However I think what I want to do is in the realm of possibility. I want to text query a single large table in a SQL Server ...
2
votes
1answer
2k views

Use limit with where clause in in mysql [closed]

I know where clause search and display the records as per criteria comes true, and limit is use to fetch the number of records from a table. But please help me when I am trying to use them both ...
5
votes
1answer
172 views

Placing an ASSERT in a SQL Server query

As part of a MERGE query that I want to run I'd like to assert at runtime that a certain condition holds. When a MERGE-match is found I'd like to update a certain column and have the following logic ...
1
vote
1answer
2k views

Does SQL Server allow INSERTs into tables and indexes to be written in parallel?

If I have a table that has many indexes, and I run a statement that inserts rows into the table, will SQL Server insert rows one-at-a-time, or will the rows be inserted using parallelism?
0
votes
1answer
659 views

Assign result of regexp_split_to_array() to Pl/pgSQL array variable

How do I create an array and then put a result of a function into it? I'm doing it like this: array text[]; select regexp_split_to_array('whatever this is it splits into array with spaces', E'\\s+') ...
2
votes
1answer
414 views

MariaDB Cluster - data consistency for updates and auto_increment

I have two different MariaDB servers and plan to merge data into one database and create MariaDB Cluster with 3 nodes. I read about Galera and multi-master replication - which MariaDB Cluster use and ...
0
votes
1answer
163 views

How to avoid/prevent Aborted Connections in mysql

We are using MySQL Version 5.1.73. Database is getting huge number of connections around 600 at one time. I observed Aborted Connections are getting increased from Global Status variables. So I ...
-2
votes
1answer
511 views

How to create a user via GUI in SQL server 2008 R2 with read only access on specific databases? [closed]

I am a newbie in SQL server 2008 and slowly learning it. Today I got a task of creating a user with read only access for a specific database in a server. So what should I specify under the tab- ...
0
votes
2answers
225 views

Is it possible to speed up this PARTITION BY query?

I've got a timeserie table. Each datapoint has also a statut field which indicates its providing source. I'd like to request last available datapoint as of date T but there could be several results, ...
0
votes
2answers
405 views

How to create a versioning DDL/DML change script for SQL Server?

I have an application which has a database, and some versions of the application introduce schema changes. To make installation easier (?), I figured I want to create a meta table which contains the ...
0
votes
0answers
48 views

Normalization to BCNF help needed

I have a table with 5 columns as A, B, C, D, and E. The functional dependencies are: AB -> C,D,E BCD -> A BCE -> A,D BD -> E I think AB is the candidate key. I can't find any other candidates. ...
0
votes
0answers
362 views

SQL Server: mirroring several tables in different databases

I want to keep several tables in two different databases mirrored. Both databases are within the same server, and both are SQL Server dbs. This mirroring only affects the data they contain, not the ...
0
votes
1answer
273 views

NTEXT,Indexing, and lob logical reads

We have an query that would benefit from a covering index but one of the columns that would be returned is a NTEXT data type. I know this can't be added to the index and I also know it would be good ...

15 30 50 per page