texts: Does joining to the same table twice have a predictable performance impact

texts:
I have a simple dimension, for example a date dimension, which is static and unchanging over time. In the fact table, I have several dates.

Is it different from a performance perspective to join to one date dimension twice vs. having a second copy of that date dimension and joining to it for the second date and so on?

If I have a fact table that looks like this:

FactId   FactNumber  FactDate  FactOtherDate
1        10          1/1/2016  12/31/2015

...and I want to produce a query like this:

Select Sum(FactNumber), DateDim.Month, OtherMonth = DateDim2.Month
From Fact
Inner Join DateDim On DateDim.Date = FactDate
Inner Join DateDim DateDim2 ON DateDim2.Date = FactOtherDate
Group By DateDim.Month, DateDim2.Month

...would it be better to make a second actual table and join to that, rather than joining to the same table twice?

I ask because indexed views won't allow such a query as a view, and that made me think that SQL Server handles it differently and possibly less efficiently. I'm not looking to use an indexed view, just wondering if that restriction provides any insight into this question.


texts:
I'm part of a small company so as usual covering a number of different roles. The latest of which is procuring a dedicated SQL Server box for our .NET web app. We've been quoted on a dual Xeon E5-2620 (six core) 2.00 GHz CPU configuration (12 cores in total), with 32 GB of RAM. This has left us with a limited budget for the disk array, which would essentially consist of two 2.5" SAS 300 GB drives (15k RPM) in a RAID 1 config.

I know that the disk setup is sub-optimal for SQL Server and I'd really like to push for RAID 10 so we can put the database, log files and tempdb on their own drives. In order to make this compatible with our budget should I consider reducing the number of CPU cores? or would I get better bank for buck keeping the cores and using fewer drives, perhaps 4 in a dual RAID 1 setup?

Here are some additional stats

The SQL Server database is tilted towards high numbers of reads to writes, probably 80% vs 20% respectively. The current DB size is around 10 GB 26 GB at present, growing at rate of 250 MB per month.
Currently running on SQL Server 2008 R2 Standard on a single quad core Xeon box shared with the web-server (12 GB Ram, 2 x 10k 300GB SAS drives in RAID 1), looking to move to SQL Server 2012 Standard.
Database serves approx 100-150 concurrent users with some background scheduling tasks thrown in. Reading this, I'm thinking that 12 cores is serious overkill!

I deployed the whole application to an Azure cloud service (2 small instances) linked to an SQL Azure DB. Although performance was reasonable when testing (almost zero load) I lost the courage to use in production due to the unpredictability I'd read so much about. It may work better with a scale-out approach, but with just a 10 GB database I can probably get away with scaling up right now and save some cash.

I initially overlooked the licensing costs and didn't realise that SQL Server 2012 licensing is based on the number of cores. I have a BizSpark MSDN subscription with a SQL Server 2012 Standard licence so I'd need to read up on how many cores this would utilise out of the box.


texts: Calculate hours based on a course schedule in mysql


texts: ORA-01466: unable to read data - table definition has changed

texts:
What happen when is done a import from a export with this erro?
I have a table that every day is altered the structure during the consistent export (flashback_time=systimestamp) and I always receive this erro. If I to do a import using this export, it will work well?


texts: Execution Plan is different when Constant Variable is Null

texts:
I am working on a scenario in which I am providing a variable a value. When I pass Null value, the Query Engine didn't scan the Join tables. As per Logical Query Processing, first FROM Clause Executes, then ON and JOIN executes. But in this Case the Query Engine direct go to Where Clause. Could anyone please explain the Behavior Query Engine when there is a NULL value of a Variable. I am using SQL Server 2016.

When the Value is changed then


texts: Optimize query: Filter Values Contained in Multiple Ranges

texts:
Background

In the fiddle below, I have two tables. The first table contains a column with a numeric value (e.g. int). The second table contains rows of numeric ranges (stored in two columns that hold the “low” and “high” limits of the range).

The Problem

The query in the fiddle works, using a FULL OUTER JOIN to produce the expected results. I can add indices to speed queries, but would appreciate thoughts on how to generate SQL to achieve a more optimal query plan than that provided in the fiddle.

Example

DB Fiddle here

Other Notes

Solutions need not be portable (so long as they work in PostgreSQL), but no third-party solutions, please.

System

PostgreSQL v11.2

Fedora 29


texts:
I own a jewelry retail store alongside an online store, and am in the midst of building a completely custom system to track all of my inventory. I also have a number of stockists (other stores) that sell my items on consignment, so I want to keep track of stock levels there too.

I primarily sell rings, and have thousands of them in storage. I keep one of each size/style in stock at my physical store. They come in a variety of sizes and styles. I want to avoid the EAV approach completely and am happy to have sparse fields, there aren't many attributes.

The main product table, I included a number of attributes in here because they don't vary for each product. Size varies, so I have addressed that using other tables.

  Product Table
  - id
  - name
  - categoryID
  - description
  - finish (polished, brushed, satin etc)
  - width
  - thickness

Each ring comes in 6 sizes, so rather than creating 6 duplicate entries in the product table I created the productVariation table which references the productID. At the moment this is only used for storing the different sizes. Each different sized ring will have a unique SKU and barcode, so I have put that in here rather than the main product table.

  ProductVariation Table
  - id
  - productID
  - sizeID
  - sku

Then I have to keep quantity levels for several locations, so I made the productQuantity table to hold that information.

  ProductQuantity Table
  - id
  - productVariationID
  - locationID
  - quantity

Am I on the right track? One thing I haven't figured out yet is how to add a product that has no variations, for example a watch that only comes in one style and has no size options. It would have to be added to the productVariation table with a blank sizeID since that's where the SKU is stored. Any ideas to improve this design?


texts: Mysql : user with privileges to create databases, users and grant all privileges on those dbs to users

texts: Is it possible in mysql to create user who is able to create database, user and grant all privileges on this database to that user?I dont want to use root user in script


texts: Performance issues with inherited tables and indices

texts:
I have a PostgreSQL database with a master table and 2 child tables.
My master table:

CREATE TABLE test (
    id serial PRIMARY KEY,
    date timestamp without time zone
);
CREATE INDEX ON test(date);

My child tables:

CREATE TABLE test_20150812 (
    CHECK ( date >= DATE '2015-08-12' AND date < DATE '2015-08-13' )
) INHERITS (test);

CREATE TABLE test_20150811 (
    CHECK ( date >= DATE '2015-08-11' AND date < DATE '2015-08-12' )
) INHERITS (test);

CREATE INDEX ON test_20150812(date);
CREATE INDEX ON test_20150811(date);

When I execute query like:

select * from test_20150812 where date > '2015-08-12' order by date desc;

It returns very quickly (20-30 miliseconds). EXPLAIN output:

 Limit  (cost=0.00..2.69 rows=50 width=212)
   ->  Index Scan Backward using test_20150812_date_idx on test_20150812  (cost=0.00..149538.92 rows=2782286 width=212)
         Index Cond: (date > '2015-08-12 00:00:00'::timestamp without time zone)

However if I execute query like:

select * from test where date > '2015-08-12' order by date desc;

It takes a long time (10-15 seconds). EXPLAIN output:

 Limit  (cost=196687.06..196687.19 rows=50 width=212)
   ->  Sort  (cost=196687.06..203617.51 rows=2772180 width=212)
         Sort Key: public.test.date
         ->  Result  (cost=0.00..104597.24 rows=2772180 width=212)
               ->  Append  (cost=0.00..104597.24 rows=2772180 width=212)
                     ->  Seq Scan on test  (cost=0.00..0.00 rows=1 width=1857)
                           Filter: (date > '2015-08-12 00:00:00'::timestamp without time zone)
                     ->  Seq Scan on test_20150812 test  (cost=0.00..104597.24 rows=2772179 width=212)
                           Filter: (date > '2015-08-12 00:00:00'::timestamp without time zone)

constraint_exclusion is set to ON in my postgresql.conf. Therefore it should only be executed on test_20150812.

I see that, if a query is executed on master table, indices are never used. How can I improve it? I want to make all my queries on my master table. When querying for a specific date I expect no performance difference between querying on the master table or the child table.


texts:
I want to add a check constraint on a column if entered value for column is greater than 3 than it should be saved as 1 else entered value. For that scenario I am using below query but on insertion of data it shows an error message.

create table tblTestCheckConstraint
(
id int,
NewColumn int
)

alter table tblTestCheckConstraint
add constraint chk_tblTestCheckConstraint_NewColumn1 CHECK
(
    CASE
        WHEN NewColumn >4 THEN 1
        ELSE NewColumn
    END = 1
)

Insert Statement:

insert into tblTestCheckConstraint values ( 1,5)
insert into tblTestCheckConstraint values ( 1,2)

Error Message:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "chk_tblTestCheckConstraint_NewColumn1". The conflict occurred in database "DBName"
, table "dbo.tblTestCheckConstraint", column 'NewColumn'.
The statement has been terminated.


texts:
I am running a Oracle Big Data Appliance platform that has Cloudera EDH 5.9.x running under the hood. My users are mainly using beeswax, which is the Hive query editor app within Hue.

When a user logs into Hue and then opens beeswax, the default Hive database "default" is preselected. I want to change this so that the first database they see is their own sandbox database. Currently, the user has to manually select the database from beeswax or run the use DATABASE command in the editor.

Is there a configuration item I can change within any of the CDH software modules that will help me do this? Or is there a concept of a Hive startup script where I can run the use DATABASE command?


texts: Cannot connect to MySQL sever with MySQL Workbench

texts:
I am using MySQL 5.6.26 Community Server on a Mac OSX 10.10.4.

The server is running, according to the preference pane utility. Also I can connect to the server using the command line tool with the common login:

./mysql -h localhost -u root -p

However, using the same credentials in MySQL Workbench gives me this error:

Can't connect to MySQL server on '127.0.0.1' (61)

I tested replacing localhost with 127.0.0.1 in the command line login wich gives the same error:

./mysql -h 127.0.0.1 -u root -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (61)

I think there is an issue here with resolving localhost to 127.0.0.1, but I need help with further investigation.


texts: Altering db schema in production environment

texts:
I have some questions on db schema changes in production environment without taking the database server and the webapp offline.

Q1- I want to be able to make changes to database schema (adding, removing columns, normalizing tables etc) in my production environment without taking the db server and my webapp offline. What are some of the best practices to achieve this? Also are there any free tools that deal with this problem. I am using PostgresSQL.

Q2- While the db is being upgraded, will the app servers in production work against the old db or the new db. Or is there usually some sort of a hybrid using perhaps a strategy pattern in the app? What are some of the best practises for this? When would it be safe to point the appserver to the upgraded db. Will there be some sort of downtime involved or can that be avoided altogether somehow?

Thanks.


texts: Storing multiple tags on analytics database

texts:
I would like to store user purchase custom tags on each transaction, example if user bought  shoes then tags are "SPORTS", "NIKE", SHOES, COLOUR_BLACK, SIZE_12,..

These tags are that seller interested in querying back to understand the sales.

My idea is when ever new tag comes in create new code(something like hashcode but sequential) for that tag, and code starts from "a-z" 26 letters then "aa, ab, ac...zz" goes on. Now keep all the tags given for in one transaction in the one column called tag (varchar) by separating with "|".

Let us assume mapping is (at application level)

"SPORTS" = a
"TENNIS" = b
"CRICKET" = c
...
...
"NIKE"  = z        //Brands company
"ADIDAS" = aa
"WOODLAND" = ab
...
...
SHOES   = ay
...
...
COLOUR_BLACK = bc
COLOUR_RED = bd
COLOUR_BLUE = be
...
SIZE_12 = cq
...

So storing the above purchase transaction, tag will be like tag="|a|z|ay|bc|cq|" And now allowing seller to search number of SHOES sold by adding WHERE condition tag LIKE %|ay|%. Now the problem is i cannot use index (sort key in redshift db) for "LIKE starts with %". So how to solve this issue, since i might have 100 millions of records? dont want full table scan..

any solution to fix this?

Update_1:
I have not followed bridge table concept (cross-reference table) since I want to perform group by on the results after searching the specified tags. My solution will give only one row when two tags matched in a single transaction, but bridge table will give me two rows? then my sum() will be doubled.

I got suggestion like below

  EXISTS (SELECT 1 FROM transaction_tag WHERE tag_id = 'zz' and trans_id
  = tr.trans_id) in the WHERE clause once for each tag (note: assumes tr is an alias to the transaction table in the surrounding query)

I have not followed this; since i have to perform AND and OR condition on the tags, example ("SPORTS" AND "ADIDAS") ----  "SHOE" AND ("NIKE"  OR  "ADIDAS")

Update_2:
I have not followed bitfield, since dont know redshift has this support also  I assuming if my system will be going to have minimum of 3500 tags, and allocating one bit for each; which results in 437 bytes for each transaction, though there will be only max of 5 tags can be given for a transaction. Any optimisation here?

Solution_1:

I have thought of adding min (SMALL_INT) and max value (SMALL_INT) along with tags column, and apply index on that.

so something like this

"SPORTS" = a = 1
"TENNIS" = b = 2
"CRICKET" = c = 3
...
...
"NIKE"  = z  = 26
"ADIDAS" = aa = 27

So my column values are

`tag="|a|z|ay|bc|cq|"` //sorted?
`minTag=1`
`maxTag=95` //for cq

And query for searching shoe(ay=51) is

maxTag <= 51 AND tag LIKE %|ay|%

And query for searching shoe(ay=51) AND SIZE_12 (cq=95)  is

minTag >= 51 AND maxTag <= 95 AND tag LIKE %|ay|%|cq|%

Will this give any benefit? Kindly suggest any alternatives.


texts: Generating Error when data size is larger then max text repl size

texts:
I recently ran into an issue with where an application tried to do an insert with data over the max text repl size.  this time the failed insert was captured by the application log, and I was made aware.  I was a little disturbed though to find out the error wasn't captured in the sql server error log, and I don't see it captured any place else.

Any advise on how to capture, and Ideally generate alerts based on the 'Length of LOB data (XXXXXX) to be replicated exceeds configured maximum " would be appreciated.


texts: Unable to create index on InnoDB table (5.5.37)

texts:
I've met with the strange trouble with one InnoDB table.

Index creation on any column fails with error:

Incorrect key file for table 'table', try to repair it

Despite this error, this table is queryable and there's enough space on disks and RAM.

What reasons can cause this trouble?

SHOW TABLE STATUS output:

Name    some_datamart
Engine  InnoDB
Version 10
Row_format  Compact
Rows    364738
Avg_row_length  182
Data_length 66699264
Max_data_length 0
Index_length    0
Data_free   5242880
Auto_increment  null
Update_time null
Check_time  null
Collation   utf8_general_ci
Checksum    null
Create_options  no options


texts: Preferred Methods of Indexing MS SQL Server Audit Data to Splunk

texts:
What is your preferred method for indexing MS SQL Server for Splunk? I am collecting audit data in various environments by various means (profiler, app logs, and extended events) but I hope to consolidate them all into extended events moving forward.
However, one or two environments have Splunk connected and we want to ensure all security logs are collected and sent to the Splunk server. I have seen a few ways of doing this but I'm not sure which would be preferred or the 'best practice'. I haven't seen much on the Splunk community on this since really it's up to us to get it in Splunk however we can, so I figured I'd ask us here.

Requirements:
1. Make sure Splunk can index the darn thing.
Prefer minimal user scripting to accomplish it, if possible. Basically, make it a text or CSV file.
2. Minimal risk of 'fudging' the audit logs before Splunk gets them.
I want them to be written to Splunk or the text file that is indexed as fast as possible. This is to reduce 'man in the middle' attacks where audit logs are fudged 'in between' pulls and syncs. Also, we might be exposed to duplicate entries in certain scenarios.
3. Minimize DBA access.
I understand the DBAs with SA rights for the enterprise will always be able to get around issues, but we could vastly minimize our access to it. I am thinking of a solution so that security really manages this, because sometimes, even DBAs go bad.

Solutions:
1. Collect profiler data and use the .Net class to write a mini-app so it exports to a text file for Splunk.
I haven't done this before but it seems like it would resolve 'having data read immediately'. It requires a server side trace but in 2012 you can start it up without having to enable server start up stored procs which would go against the security standard.
Positives:

Profiler is easy and everyone knows how to use it in and out; won't be dependent on me.

Easy .Net integration

Negatives:

Profiler sucks compared to extended events.

Will have to ensure it is always collecting data. In 2012 it's easy but in 2008 you will have issues if you don't use start up stored procs, and will have to get creative with jobs. Even then, you might miss some logging.

It is being deprecated.

2. Replace profiler with Extended Events.
Positives:

Will always start up in 2008 and up without any special parameters or start up stored procs

Very light weight

Preferred new method

Negatives:

How the heck do you get the file saved in a text or CSV format so Splunk can easily access it? I haven't seen any way to do that and my PluralSight sub ran out :/

3. Log the data to a locked-down table and have Splunk read that table. Put proper permissions in place where a DBA/sec admin can modify the trigger/service broker app that logs it and alert SecOps/DBAs if someone changes anything in that table.
Problem is I have only found Splunk to be able to query MySQL and not MS SQL Server. Perhaps I could run a PowerShell script to continually query that table or get creative with a trigger that starts a PowerShell session (have never done that before) to reduce man in the middle editing the files.
Positives:

Meets the requirements

Reduces man in the middle attack if we can have it update the file immediately.

Negatives:

It's perhaps more complicated than it needs to be.

4. Use this beta product.
Positives:

Security team will manage it which is preferred since this is a security concern, and part of it IMO is protecting the data from DBAs as well.

Minimal overhead on the DBA team.

Negatives

It's beta.

No real documentation.

Edit:
5. Use Splunk DB Collect.
This seems like the best solution. We just have to ensure that not too many people have access to the table or trigger. I will check to see how 'real time' it is.


texts: Not able to delete transaction log backup file

texts:
I have a big SQL Server database (~1.5 TB) and i didn't take log backup of that database for last two weeks for some reason.
Today i found that the .ldf file of that database became huge around 1.5 TB.
So i took the transaction log backup and then shrinked the .ldf file to fix the space issue.
I used the sql job(from maintenance plan) for taking transaction log backup and the transaction log backup taken after two weeks came up to 2TB.
Now that the space issue is solved,i tried to delete the log backup(~2TB) but
it says that i dont have permission to delete.
I took another log backup which is like 200MB and i am able to delete those.
But the first huge log backup i am not able to delete.
I checked the permission,and the domain account using which i logged into the server belongs to admin group.

I even used the sql job to scrub old backup,but even that fails.

Only this huge log backup has the security part as shown in the screenshot.

All the other log backup taken after that huge lock backup has the security tabs as shown in the second screenshot below.

Any idea what is going on.?

If i stop the sql service/sql agent service ,then can i delete this .trn file?
I wanna try that sometime when no one is using the database.

I was able to delete the 2TB log backup file after i restart the server.
Not sure why such a lock happened.


texts:
I have a table like this:

CREATE TABLE employees
(employee_id NUMBER(4) NOT NULL,
 last_name VARCHAR2(10),
 department_id NUMBER(2))
PARTITION BY RANGE (department_id)
(PARTITION employees_part1 VALUES LESS THAN (11) ,
 PARTITION employees_part2 VALUES LESS THAN (21) ,
 PARTITION employees_part3 VALUES LESS THAN (31) );

Are these statements the same?

CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL;

CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL
(
 PARTITION employees_part1,
 PARTITION employees_part2,
 PARTITION employees_part3
)

Thanks,


texts: t-sql or powershell to return currently-configured snapshot folder for transactional replication SQL 2012

texts:
I'm working on a script to iterate through all the snapshot folders and do some basic find and replace of the ANSI_PADDING OFF bug and replace it with SET ANSI_PADDING ON. I'd like to be able to dynamically point to any publisher and get a list of currently configured snapshot folders by publication. I can then filter and dedupe if needed.

I know I can query the distribution database for the default folder but how do I return the currently configured one, if say, someone changed the snapshot folder via the Replication Monitor GUI?


texts: How do i set up an Alert that tells me when a replication of data failed?

texts: I've got Database Mail setup and tested fine.  Alerts do work when I stop and start the replication agent job, but I cannot get it to throw any alerts when data moves from a publisher db to a subscriber db successfully or unsuccessfully.  Ultimate goal is to notify me if something doesn't go right with a data replication.


texts: FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional


texts:
I need to persist on Oracle Db the follow check box table :

The simple thing that I can imagine is to create a Table  with 15 boolean fields, but since that the rows and the columns could increase I would to know if there is a better way to persist this table without to provide a table of Number of rows *  Number of Columns.

Thank You in advance for the replies

p.s: the meaning the table is to mean:

is the north Mountainous? true o false?
is the north Hilly? true o false?
is the north Flat? true o false?
is the Middle Mountainous? true o false?
etc.


texts:
One of our Oracle servers went down rather badly last night, and we have been unable to bring it online since. Attempting to startup each instance results in:

SQL> Connected to an idle instance.
SQL> ORA-00449: background process 'MMAN' unexpectedly terminated with error 7446
ORA-07446: sdnfy: bad value '' for parameter .
SQL> Disconnected

After running this, none of the instances are online. The listener reports that is running, at least.

The first instance (asdb) in the order has a number of (mostly repetitive sections) in its alert.log, but nothing in there seems particularly likely. The other instances have blank (but writeable) alert.log files.

Where should I investigate to try and get this back online?


texts: Count of distinct values of a table using sql

texts:
name  number
=============
lin |   8
lin |   8
lin |   8
rob |   8
rob |   8
rob |   8
lin |   8
lin |   41
lin |   41
rob |   41

and I want the result like

name  |   Count of 8 |   count of 41
====================================
lin   |      4       |        2
rob   |      3       |       1

Can anyone help me please?


texts: How to disable all password requirements in Oracle?

texts:
When I try to create a new database using DBCA it stops me because my password isn't complex enough. I am working with Oracle in a VirtualBox VM on my own laptop, so I don't need the password complexity restrictions Oracle imposes out of the box. Personally I don't care if my passwords in this environment are "password" or even a space. Unfortunately I can't seem to find a straightforward answer on how to simply disable password checks. What command do I need to run / switch do I need to flip to turn it off?

I'm on a Windows laptop with Windows 2012 in the VM if that is relevant. Thanks for any tips.


texts: I know that psql is an option, but I'm looking for an easier text-based interface for PostgreSQL. Something that's similar to what 'tig' is to 'git', or 'phpmyadmin' is to 'mysql' (but using the command line). does any one know something like this?


texts:
Table detail has a ForeignKey to table master.

How to call the back relation?

  master has ??? to table detail

I could not find a canonical word in wikipedia or other resources.


texts: Choosing sort and distkeys for redshift with multiple joins

texts:
I have a relatively large Redshift cluster which I am attempting to optimize via the selection of sort and dist keys. I have a recurring situation where multiple tables are joined together via an intermediary join. The join is relatively stable among all queries. I know that you're supposed to distribute on the field that you join to and sort on the conditions in the where clause. However, in this situation there is no where clause and it is joined twice. For example I have three tables:

users -> accounts -> subscriptions. These are in a 1:1:1 relationship. The joins are as follows:

users.id = accounts.user_id
accounts.id = subscriptions.accounts_id.

What is the optimal way to set the sort and dist keys on the accounts table in this situation?


texts:
I want to show all the categories and the items inside that each category which should store in items_this_category.

Note, I have created an associative table item_categories because one item can be listed in more than one category.

Here is a screen of NaviCat with structure and the query


texts: Query Performance When Dropping Multiple Columns

texts:
In PostgreSQL, is there any performance gain when using a single ALTER TABLE statement to drop multiple columns, instead of using a separate statement to drop each column?

ALTER TABLE table DROP COLUMN col1, DROP COLUMN col2, DROP COLUMN 3;

// vs

ALTER TABLE table DROP COLUMN col1;
ALTER TABLE table DROP COLUMN col2;
ALTER TABLE table DROP COLUMN col3;


texts: How to convert a Microsoft SQL Server CE file (.sdf) to Postgres 9.6 /pgAdmin 4?

texts: I have a database file data.sdf that I want to import into PgAdmin 4. I've considered using pgloader but the file format does not seem to be accepted. Does anybody have any hints on how I might go about making this work?


texts: enforcing function/constant names length in Oracle 18 to be 30 characters

texts: in Oracle 18 is there a way to enforce the max length of function names (and constant-names) to be 30 chars only (as it is in Oracle 11 and priors)?


texts: SQL Server Management Studio backup not shown when restoring

texts:
I am trying to restore a database from a backup taken on another server. I am doing this by using SQL Server 2012 Management Studio.

After I have taken the backup on server 1, I go on to select the database on server 2, choose restore, and I select the database on server 1 as source.

After that, the only backup available is an old one taken by another user. It is from the correct database, but not the backup I just took. The newly created backup is not showing in the list.

The backup I took is copy-only, but the old backup that shows up is copy-only as well. It was another user who took the backup that shows up, but I have administrator rights at both server and I don't think that's the problem (but I can't rule it out). I was able to take the backup on server 1 when using Windows integrated security, so it seems strange if I suddenly don't have rights to view the backup anymore. However, it might be that SSMS is run in a such manner that it doesn't have necessary rights. I am not sure.

I am probably missing something here, but right now I can't see what. What can be possible sources of error to why I can't see my newly taken backup in the list of available backups?


texts: MongoDB: Is it possible add SECONDARY member with version 3.0 to a replica set where members have version 2.6?

texts:
I have a replica set with 1 secondary and 1 arbiter running version 2.6. and I would like to introduce a new member with version 3.0 so that I can upgrade the whole replica set to 3.0. I wonder if this is a way to to a "rolling upgrade" to MongoDB version 3.0?

The upgrade process I'm thinking would be:

Introduce new member with version 3.0 and let it sync. With priority 0 so that it is not elected at any point.
Once new SECONDARY has caught up, increase priority to make it eligible and remove old SECONDARY server.
Step down PRIMARY (new member with version 3.0 becomes PRIMARY).
Repeat steps 1 and 2 to remove what used to be the original PRIMARY member.

Any other upgrade process suggestions are appreciated.

Thanks


texts: Oracle DB unix/linux autostart - oracle user no login shell

texts:
I have to setup an autostart for a Oracle DB 12c on a RHEL 6.8 OS. The user oracle has no login shell e.g.

sudo su - oracle -s /bin/bash

I followed a guide for a RHEL setup. The init.d script Looks as follows:

#!/bin/bash
# chkconfig: 35 99 10
# description: Starts and stops Oracle processes

ORA_HOME=/opt/oracle/product/12cR1/db
ORA_OWNER=oracle

PATH=${PATH}:$ORACLE_HOME/bin
HOST=`hostname`
PLATFORM=`uname`
export ORACLE_HOME PATH
case $1 in
'start')
        su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
        su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart"
        ;;
'stop')
        su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
        su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut"
        ;;
*)
        echo "usage: $0 {start|stop}"
        exit 1
esac
#
exit

After os reboot.  Oracle DB does not start. What could be wrong? Are there other logs beside $ORACLE_HOME/startup.log?


texts: Very poor insert performance MySQL / Percona Server

texts:
I'm running a Percona-Server instance off an Ubuntu server install.  I'm using an application that needs to access this database, and its having VERY poor performance.  Once the database has been established, the application goes in (upon install) and creates the schema.  It defaults everything to MyISAM, however I have converted the table engines back to InnoDB.  The issue that I'm having is VERY poor insert performance.  This application is very write-heavy, and it seems as though its writing each row 1 at a time to disk, not using any sort of buffers, however I'm unsure how to check or verify this.  Even a select(*) from one of the tables will take 2.4 seconds, and there is only 163,000 rows.  I'm kind of at a loss as to what else I can do.

Server has 8GB of ram, and CPU is almost entirely idle while this is occurring.

my.cnf:

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/run/mysqld/mysqld.sock

[mysqld]

# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = /var/run/mysqld/mysqld.sock
pid_file                       = /var/run/mysqld/mysqld.pid

# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve
sql_mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate_is_now                 = 1
innodb                         = FORCE
innodb_strict_mode             = 1

# DATA STORAGE #
datadir                        = /mnt/Storage/mysql/

# BINARY LOGGING #
log_bin                        = /mnt/Storage/mysql/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 1024
table_open_cache               = 2048

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 512M
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 6144M
innodb_buffer_pool_instances   = 1
innodb_read_io_threads         = 64
innodb_write_io_threads        = 64
innodb_io_capacity             = 10000

# LOGGING #
log_error                      = /mnt/Storage/mysql/mysql-error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /mnt/Storage/mysql/mysql-slow.log

desc of one table that is having poor insert performance:

mysql> desc parts;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| ID         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| binaryID   | int(11) unsigned    | NO   | MUL | 0       |                |
| messageID  | varchar(255)        | NO   |     |         |                |
| number     | bigint(20) unsigned | NO   | MUL | 0       |                |
| partnumber | int(10) unsigned    | NO   |     | 0       |                |
| size       | bigint(20) unsigned | NO   |     | 0       |                |
| dateadded  | datetime            | YES  | MUL | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)


texts: Selecting Multiple Rows in One Query with Multiple Conditions

texts:
I can select multiple rows with one condition by using something like:

SELECT `Col`, `Col2` FROM `Table` WHERE `Col3` IN (?, ?, ?, ?, ?);
# This selects 5 rows

How can this be done if there are multiple conditions (all integer equals operations)?

There are three conditions that the query needs to check against and all three of these make up the composite primary key.

A single query will select from 10 to 100 rows (though most of the time it'll be only 10)- it has to be fast in terms of performance. This is why using multiple queries isn't a good idea.

The CREATE TABLE statement is:

CREATE TABLE `Table Name` (
  `X` smallint(6) unsigned NOT NULL,
  `Y` smallint(6) unsigned NOT NULL,
  `Z` smallint(6) unsigned NOT NULL,
  `Data` varchar(2048) NOT NULL DEFAULT '',
  PRIMARY KEY (`X`,`Y`,`Z`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

With multiple queries, it could be done like this:

SELECT `One`, `Two` FROM `Table` WHERE `X` = ? AND `Y` = ? AND `Z` = ?;
SELECT `One`, `Two` FROM `Table` WHERE `X` = ? AND `Y` = ? AND `Z` = ?;
SELECT `One`, `Two` FROM `Table` WHERE `X` = ? AND `Y` = ? AND `Z` = ?;
# This selects 3 rows but I don't want to make 3 calls to the database server for that


texts: Disk usage spikes: good, bad or indifferent?

texts:
I'm working on a project where we're receiving data from multiple sources, that needs to be saved into various tables in our database.

Fast.

I've played with various methods, and the fastest I've found so far is using a collection of TableValue parameters, filling them up and periodically sending them to the database via a corresponding collection of stored procedures, inside a transaction.

The results are quite satisfying. However, looking at disk usage (% Idle Time in Perfmon), I can see that the disk is getting periodically 'thrashed' (a 'spike' down to 0% every 13-18 seconds), whilst in between the %Idle time is around 90%. I've tried varying the 'batch' size, but it doesn't have an enormous influence.

Is this 'normal'? Given that the overall average throughput is ok, I could just leave it, but I'm a bit worried about exploding buffers.
Should I be able to get better throughput by (somehow) avoiding the spikes while decreasing the overall idle time

Other details:

The database is in Simple recovery mode, and pre-sized to 'big', the log file is not growing
The ldf and mdf files are on different disks (the mdf on a striped array, ldf on a 'normal' disk, all I had available)


texts: What is the most efficient method of performing the FIZZBUZZ test in SQL Server?

texts:
This may not be completely on-topic, however it's a slow day here.

Is there a more efficient method of obtaining a list of numbers from 1 to 49 with a column containing the words FIZZ when the number can be evenly divided by 3, BUZZ when the number can be evenly divided by 5, and FIZZBUZZ when the number can be evenly divided by both 3 and 5?

My attempts are (CAUTION, this will empty your procedure cache, so DON'T RUN ON a PRODUCTION BOX):

DBCC FREEPROCCACHE
GO
/*VARIANT1*/
;WITH t AS (
    SELECT RowNum = ROW_NUMBER() OVER (ORDER BY o.object_id)
    FROM sys.objects o
)
SELECT t.RowNum
    , CASE WHEN ((t.RowNum % 3) + (t.RowNum % 5)) = 0  THEN 'FIZZBUZZ'
    ELSE
        CASE WHEN t.RowNum % 3 = 0 THEN 'FIZZ'
        ELSE
            CASE WHEN t.RowNum % 5 = 0 THEN 'BUZZ'
            ELSE ''
            END
        END
    END
FROM t
WHERE t.RowNum < 50;
GO 100

/*VARIANT2*/
DECLARE @t TABLE
(
    Num INT NOT NULL PRIMARY KEY CLUSTERED
);
INSERT INTO @t (Num)
SELECT ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o;

SELECT t.Num
    , CASE WHEN ((t.Num % 3) + (t.Num % 5)) = 0  THEN 'FIZZBUZZ'
    ELSE
        CASE WHEN t.Num % 3 = 0 THEN 'FIZZ'
        ELSE
            CASE WHEN t.Num % 5 = 0 THEN 'BUZZ'
            ELSE ''
            END
        END
    END
FROM @t t
WHERE t.Num < 50;
GO 100

SELECT CASE WHEN dest.text LIKE '%/*VARIANT1*/%' THEN 'VARIANT1' ELSE 'VARIANT2' END
    , MAX(deqs.execution_count)
    , SUM(deqs.total_worker_time)
    , AvgWorkerTime = SUM(deqs.total_worker_time) / MAX(deqs.execution_count)
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) dest
WHERE (dest.text LIKE '%/*VARIANT1*/%'
    OR dest.text LIKE '%/*VARIANT2*/%')
    AND dest.text NOT LIKE '%/*NOT_ME!*/%'
GROUP BY CASE WHEN dest.text LIKE '%/*VARIANT1*/%' THEN 'VARIANT1' ELSE 'VARIANT2' END
ORDER BY CASE WHEN dest.text LIKE '%/*VARIANT1*/%' THEN 'VARIANT1' ELSE 'VARIANT2' END
/*NOT_ME!*/;

As advised by @AaronBertrand, I've modified my attempts to run each set of statements 100 times each, then show the times recorded by SQL Server through sys.dm_exec_query_stats.

The results:

            Runs    total_time      average time
VARIANT1    100     42533           425
VARIANT2    100     138677          1386


texts:
I am developing a database for the recording of Volleyball results.  I have a fixture database that tells me who plays who in each round.

Teams Table, say IDs 1,2,3,4
Fixtures Table, Round No.1, Team 1 (Team.ID) v Team 2 (Team.ID)

Results is harder and I am stuck on the schema.  Each Volleyball game is best of 5 sets, so it can be 3, 4 or 5 sets long.  I need to record Fixtures.ID, Team1.Set1, Team1.Set2, Team1.Set3, Team1.Set4 and Team1.Set5 (and same for Team 2).  Is this the best way of doing it, or should some normalisation process have an entry like
Result.ID, Fixture.ID, Result.TeamID, Result.SetNo, Result.SetScore


texts:
Disclaimer: I've never worked with replication before, but I'm doing all my research. Not really having a bunch of in-depth knowledge about MySQL makes a lot of the documentation hard to understand.

We have a remote server, and many 'onsite' servers. This app is mission-critical, and because of the unreliable internet connections on site, the onsite machines have fully functional instances of the application. Onsite machines are behind various firewalls and generally are not able to access each other. We can set up SSH tunnels to allow the cloud DB to communicate with the onsite DBs.

Here's a simple diagram:

Async Multi-Master replication seems to be the best bet for us, but most of the documentation seems to imply that multi-source replication only works for slaves and/or multi-master replication only works circularly and a single master (in this case, the cloud server) can not replicate directly to multiple other masters.

Am I interpreting this incorrectly (merely unable to find documentation on how to do this), or is this the case? If it's the case, are there are 3rd party packages like Tungsten or Galera we can use? We would prefer to use as vanilla MySQL as possible, though.

Edit for clarification: Here's a my.cnf section I found describing a 2-master setup. I'd like to set N number of these on the cloud server, and just one each (the cloud server) on the onsite servers. We'd like to do everything through my.cnf and avoid using/scripting mysql shell commands.

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306


texts: PostgreSQL 9.1 runs into inconsistent data, but pg_dump corrects the problem?

texts:
We are running a PostgreSQL 9.1.20 with a database of about 50GB.

All things works fine.
After some days strange unexpected values appear in numeric columns,  like negative or bigger values in unexpected places.
The strange part: if we run a pg_dump of the same "corrupted" database, and then restore it (in the same server), the data becomes all ok. Now the same queries return different values!

Why does the database dump have different values when we are taking the dump exactly from the "corrupted" database? (The dump has corrected values from our perception!)

Can this be related to some kind of broken transaction or hard drive trouble? Note that restarting the database does not solve the problem.

What can be the cause behind this problem?

As requested in the comments some examples of data:

    #psql
    \c base
    select * from salXXXX where sal_id=2323;
    sal_id | sal_XXXX | XXXXXX | XXXXXX | XXXXX | XXXXX | XXXXX
--------+-----------+---------------+--------------------+------------+-----------------------+------------
   2323 |     -30.43 | 42586501      |                  6 |         13 | f                     |

    \quit
    pg_dump -h 127.0.0.1 -p 5432 --user=postgres >backup.sql
    psql
    drop database base;
    create database base;
    \c base
    \i backup.sql
    (no errors found here)
    select * from salXXXX where sal_id=2323;
    sal_id | sal_XXXX | XXXXXX | XXXXXX | XXXXX | XXXXX | XXXXX
--------+-----------+---------------+--------------------+------------+-----------------------+------------
   2323 |    245.43 | 42586501      |                  6 |         13 | f                     |

It is hard to emulate it, rarely occurs, but when it occurs we just do a dump, it is a ridiculous solution, but it works.

This is just an example, many more wrong values start to appear, even if we reprocess the data. But if the database is dumped and restored, values are in the expected range again.


texts: How can I get individual rowcounts like SSMS?

texts:
I have a client c# program that is executing stored procedures via ExectueNonQuery, including catching the PRINT and Error output with InfoMessage events.  It works fine, but I have noticed something odd.

When I execute a stored procedure from SSMS, it displays rowcounts for each individual SQL statement that is executed in the Messages tab (as though it were coming from the InfoMessages).  However my program never sees these messages, though it does catch all of the same other output.  Instead, it just returns the rows affected in the ExecuteNonQuery function result that is the sum of all of the individual rowcounts (which is kind of useless).

For example, this procedure:

use [tempdb]
go

SELECT  *
INTO    MyCols
FROM    sys.columns
go

CREATE PROC foo As

    UPDATE  MyCols
    SET     name = name + N''
-- SSMS shows (662 row(s) affected)

    UPDATE  MyCols
    SET     name = name + N''
    WHERE   name like '%x%'
-- SSMS shows (59 row(s) affected)

PRINT 'bar'
-- both SSMS and ExecuteNonQuery get this

-- ExecuteNonQuery returns 721 rows affected
GO

When the foo proc is run, SSMS displays rowcounts of 662 and 59, but ExecuteNonQuery only returns the total of 721.

So, how can I get the same information that SSMS is getting?

Just to be clear here: I am not interested in how to change the stored procedures to add PRINT @@ROWCOUNTs after every SQL statement.  I know how to do that and it's not an option most of the time for a variety of reasons.

I am asking how to do what SSMS is doing here.  I can change the client code all I want at this point (for right now, anyway) and I would like to do it right.


texts: How does the alter command work internally when adding a column?

texts:
Can anyone explain what the internal operations are that takes place when an alter command is issued? How is column modified? Does it use created_tmp_tables or created_tmp_files?

As per my test, I created a table and with the alter command I added a column. After checking the created_tmp_file status variable, it increased by 3.

Can someone explain how this alter command works internally?


texts: Log Shipping from Synchronous Automatic Failover AlwaysOn Availability Group AAG

texts:
I have an AlwaysOn Availability Group which is running with Synchronous Commit & Automatic Failover. For example, connected to as SQL01 with nodes SQL01A or SQL01B.

I want to make this data available for Warehousing on another server SQL02, due to high numbers of changes and high numbers of reads we cannot use AAG Asynchronous Commit as it gets behind and causes high waits.

Because of this I have decided to logship to SQL02 once daily. However with the AAG failing over between the nodes SQL01A & SQL01B I am not sure on best practices around this.

I had set up LSBackup,Copy,Restores from SQL01A. Then failed over to SQL01B and set up. However then my jobs error on the copy and restore, so I can add an additional step onto each job to check if it is the primary copy or restore job and exit out if not, but this just seems messy and I feel I am missing something?

Any advice would be greatly appreciated.


texts: Serial fields at 1 after logical replication

texts:
I'm migrating my database from one server to another.

So far I've done pg_dump -s and I was able to successfully transfer the schema to my new server. As for the data I went with logical replication, all of the tuples are where they should be, but trying to insert another row leads to an error saying Key (id) = (1) already exists. Is replication the wrong tool for the job, or is there a way to get the tables up to date?


texts:
Is it possible to figure out which tables contribute the most to read / writes operations if all of them in same file?

I been looking at the following DMV

SELECT *
FROM sys.dm_io_virtual_file_stats

but it only shows statistics for the whole file and not sure how to split into individual tables. Trying to do this in order to identify tables that should be places in their own files on different disk.


texts: How to count more than one time with different conditions?

texts:
Having a table like this:

I want to get a result like this:

Something like:

SELECT ParentId, COUNT(SomeValue = 1) [CountMin], COUNT(SomeValue > 1) [CountRest]
FROM MyTable
GROUP BY ParentId


texts: MySQL replication for non-standard port (3306)

texts:
I want to do MySQL replication using MySQL 5.6.14 under MS Windows 2008 R2, the port for Master and Slave should be 1000 (master database on PC1, slave database installed on PC2).

I added the following parameters for master My.ini

[mysqld]
server-id=1
log-bin=black-bin.log
datadir=d:\mysql\master\
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Then I edited slave My.ini adding the following parameters:

[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
datadir=d:\mysql\slave

After that I logged in into Master database using root user and executed the following commands

CREATE USER repl_user@slave_ip;
GRANT REPLICATION SLAVE ON *.* TO repl_user@slave_ip IDENTIFIED BY 'password';

To Initialize Replication I logged in into slave database and executed the following commands:

CHANGE MASTER TO MASTER_HOST='maset_ip',
 -> MASTER_USER='repl_user',
 -> MASTER_PASSWORD='password';
start slave;

The replication works fine if the port for the master is 3306 but if the port is changed then it does not work anymore. My question is: how to make slave connect to port 1000? I tried to add the following parameter to slave my.ini  master-port=1000 then restarted the slave SID but it did not work (service did not start, error message: unknown parameter master-port). Please advise, thanks.


texts: Best way to cast array (text[]) to record-type?

texts:
PostgreSQL has the ability to create record types. A lot of operations like regexp_match return text[]. What is the most efficent way to go from text[] to a custom record type?

Currently, I'm doing it quite explicitly,

SELECT (a[1],a[2],a[3],a[4],a[5],a[6])::census.series_id
FROM census.alldata
CROSS JOIN LATERAL regexp_matches(series_id, '^(.{2})(.)(.*?)(.{4})(.{2})(.)$') AS t(a);

And, I'm wondering if there is a nicer way to do this than that. Ideally, I would just be able to cast text[] into any record type, and the coercion would take place under the hood. When I try this I get an error though,

SELECT a::census.series_id
FROM census.alldata
CROSS JOIN LATERAL regexp_matches(series_id, '^(.{2})(.)(.*?)(.{4})(.{2})(.)$') AS t(a);

ERROR:  cannot cast type text[] to census.series_id
LINE 1: SELECT a::census.series_id


texts: SSIS Script Component errors sporadically with the same code

texts:
I have a SSIS Data Flow Task that takes CSV output (from a flat file connection) and passes that to a Script Component. When I run the task I get the following error:

Script Component has encountered an exception in user code
Project name: blahblahbla
String was not recognized as valid DateTime

at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
at ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
at UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
at UserComponent.ProcessInput(Int32 InputID, String InputName, PipelineBuffer Buffer, OutputNameMap OutputMap)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.ProcessInput(Int32 InputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Which is interesting because I don't think there is a date column in the CSV, and I'm not using DateTime in this code at all.
When I go into the script and debug it, it runs smoothly. Then I take away the break point and it errors again. This seems to be some kind of race condition, which makes me worried about my other script components. This component was created via a copy paste - could that effect anything?
My user code from that function is here:
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    // Create a new row
    DataRow glRow = _data.NewRow();

    glRow["xx"] = Row.xx;
    glRow["xx"] = Row.xx;
    glRow["xx"] = Row.xx;
    glRow["xx"] = Row.xx;
    glRow["xx"] = ConvertToBool(Row.xx);
    glRow["xx"] = ConvertToBool(Row.xx);

    // Add row to _data
    _data.Rows.Add(glRow);

}

And the function is:
private object ConvertToBool(string obj)
{
    if (obj.Replace(" ", "") == "") return DBNull.Value;
    return (obj == "1") ? true : false;
}


texts: After enabling SSL in Oracle user must log in twice

texts:
I've enabled SSL for my Oracle 12c DB. The lsnr starts up fine and I can access it via a sqlplus / as sysdba (no password auth there, it's OS local). However, if I attempt to connect as a user which requires password authentication, then it fails the first time with the error ORA-28864: SSL connection closed gracefully and then prompts again for username/password. If I enter my username and password again, it works!

Why would this fail the first time but work the second? That behavior is so odd and really what I want to try to figure out.


texts: Is it possible through only T-SQL to physically move the data and log files of a database from one drive to another drive (on the same server)?

texts: I know you can alter the database to update their data and log file paths in the database properties, but is there also a way to script out the actual physical move of the files themselves?...maybe through a database command?


texts: Where does Postgres store all statistics?

texts:
I recently ran into a weird problem while migrating from PG 9.4 => 11

I replicated the production server from PG 9.4 to PG 11 using pglogical. Everything worked out well, except we discovered that a few complex queries were taking tens of seconds on the PG 11 server, while there were taking under one second on the 9.4 server.

It turns out that running ANALYZE on the new server fixed the slow queries. So, I checked $PGDATA/pg_stat and $PGDATA/pg_stat_tmp and found them to be empty on both the servers!

Where does PG store statistics? (Follow-up question -- doesn't pglogical collect statistics while it is replicating?)

Edit / follow-up:

Output of query on pg_stat_user_tables on the new PG 11 server -

select analyze_count,autoanalyze_count, count(*) from pg_stat_user_tables group by 1,2;
 analyze_count | autoanalyze_count | count
---------------+-------------------+-------
             1 |                11 |     1
             4 |                 7 |     1
             1 |                 0 |    14
             4 |                 0 |   168
(4 rows)

Same query on old PG 9.4 server (current production server):

 analyze_count | autoanalyze_count | count
---------------+-------------------+-------
             0 |                13 |     3
             0 |               226 |     1
             0 |                 1 |    31
             0 |                 5 |     6
             0 |                21 |     2
             0 |                16 |     2
             0 |               601 |     1
             0 |                 8 |     1
             0 |                20 |     1
             0 |                 4 |     2
             0 |                 9 |     2
             0 |                10 |     2
             0 |                12 |     1
             0 |                 7 |     2
             0 |                41 |     1
             0 |                 2 |    11
             0 |                 6 |     1
             0 |                 3 |     8
             0 |               731 |     1
             0 |                39 |     1
             0 |                11 |     1
             0 |                 0 |    99
             0 |                50 |     1
             0 |                66 |     1
             0 |               241 |     1
             0 |                14 |     2


texts:
We have an AWS Aurora (MySQL engine, r3.large) we are using to test a migration.

We have noticed a couple of queries that simply don't appear to run.  When I run

show engine innodb status;

it reports that there is a single table locked:

2017-06-21 18:20:49 2aea8928a700 Transaction:
TRANSACTION 6093969, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
16 lock struct(s), heap size 376, 6164 row lock(s), undo log entries 1
MySQL thread id 12219, OS thread handle 0x2aea8928a700, query id 8842803 10.127.0.24 root updating
delete from <tablename> where id=<id>
Foreign key constraint fails for table `mydb`.`<otherTable>`:

This is a java application.  I have stopped the tomcat instances, and verified that there are no active connections, but the lock does not clear up.

I looked at a couple of tables in information_schema (INNODB_LOCKS, INNODB_LOCK_WAITS, and INNODB_TRX) but there are 0 records.

Is this an aurora problem?  Any solution other than AWS Support?


texts:
Today I created a local database on my computer.
Then I installed SQL Developer. But I can't connect.

It gives me this error:

  "Status : Failure -Test failed: Locale not recognized"

My database version: 11.2.0.1.0

Now I have only one  instance (2 databases deleted before)


texts: MySQL - Arrange a table so that it shrinks as much as possible?

texts:
I'm a newbie and I have +10 million articles(200 to 1000 words) in a InnoDB table. I use this to select them:

SELECT article,title,other_fields from table where id=123;

There is no full-text search or any updates on article field.

How can I arrange the table so that the table size shrinks as much as possible. I'm happy with a little performance tradeoff. I'm sorry for the vague question but I couldn't find any answers online.

PS: There are inserts and updates on table, should I put article and id to a seperate table?


texts:
I have wriiten the following SQL script to execute using powershell.

    cls
foreach ($svr in get-content "demo.txt")
{
  $con = "server=MC1-PQ10X.RF.LILLY.COM\SQL01;database=mylilly_WSS_Content_INCTSK0014840;Integrated Security=sspi"

  $cmd = "SELECT
Docs.DirName + '/' + Docs.LeafName AS 'Item Name',
DocVersions.UIVersion,
(DocVersions.UIVersion/512) as Version_Label, DocVersions.Level, DocVersions.TimeCreated
FROM DocVersions FULL OUTER JOIN Docs ON Docs.Id = DocVersions.Id
--   INNER JOIN Webs On Docs.WebId = Webs.Id
--INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE (DirName LIKE '%globalcontentrepository%')
AND (IsCurrentVersion = '0')
AND (DocVersions.Id IN ('$svr'))"

  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

  $dt = new-object System.Data.DataTable

  $da.fill($dt) |out-null

  $dt | Export-Csv music.csv -Encoding ascii -NoTypeInformation

 }

The problem I'm facing with the above code is regarding the output. For every $svr, this code is creating a new csv file. The input file is containing around 1000 inputs.
My requirement is that all the output should get stored in the file rather than creating new file.

Please look into this. Kindly give your suggestions.
Thanks in advance


texts: `"C:\xampp\mysql\bin"' is not recognized as an internal or external command,operable program or batch file

texts:
I am trying to access to mysql from windows command prompt by this command:

"C:\xampp\mysql\bin" mysql -u root - p

But it sayas:

  `"C:\xampp\mysql\bin"' is not recognized as an internal or external
  command, operable program or batch file.

But when I do these, it works:

cd C:\xampp\mysql\bin
mysql -u root - p


texts: How do I add read/write permissions on a directory for an SQL Server account

texts:
I have created an account in SQL Server that I use in a connection string for a c# application.

I am trying to export data from my c# database programmatically,

INSERT INTO OpenRowSet(
'Microsoft.ACE.OLEDB.12.0'
, 'Excel 12.0;Database=C:\Temp\TestExcel.xlsx;'
, 'SELECT * FROM [categoryData$]'
)
SELECT * FROM categoryData;

When i do this I receive an error

  Cannot initialize the data source object of OLE DB provider
  "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

So I did some research and came across the following guide:

https://visakhm.blogspot.co.uk/2013/12/how-to-solve-microsoftaceoledb120-error.html

I'm on step 2 where I have to add permissions to the Temp folder to the account accessing the database.

  This is needed because the provider uses the temp folder while
  retrieving the data. The folder can be one of the below based on
  whether you use a local system account or network domain account.

  For network accounts, folder is

  :\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp

Here is my connection string (wrapped for readability):

SqlConnection con = new SqlConnection("Data Source=x.x.x.x,1433; Network Library=DBMSSOCN;
Initial Catalog=footfall; User ID=access; Password=pw;connection timeout=0; ");

Am I correct in assuming that the user account I need to add permissions for is "access" - Which is a login I setup myself.  If so, how to I add this account as I can't find it in the security dialog.

Edit: Using answer below from Max Vernon I completed STEP 2 of the guide but STEP 3 I cannot as I am using a 64 bit version of SQL Server.  Does anyone else know how to get round this error as I would really like to be able to export my data to excel.

  Cannot initialize the data source object of OLE DB provider
  "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

EDIT: I managed to get this working yesterday by adding a domain admin account as the log in account for the sql server service, then outputting the file to a network drive.  I still cant get it to work locally.  I can get it to write rows into the spreadsheet by manually editing the column headings to match those in the table.  I've written some new code to create the spreadsheet using System.Runtime.InteropServices; &
Microsoft.Office.Interop.Excel; Then I can export the data.


texts:
I am running into a problem where I have to use an alias (Ordernum) in a group by. I have found that this could be done somehow with subquery but I don't have any success with that and also those are not so familiar. Any kind of advice at the moment would be a major help.
SELECT a.ma   as  Document
     , a.ca  as  type
     , SUM(a.num) as Value1
     , SUM(a.num2)as Value2
     , coalesce(a.ba, MAX(a.ba) OVER (partition by a.ca)) as OrderNum  <-- Required in group by
     , a.ti as Year
FROM Orders a
WHERE ti = 2020
GROUP BY a.ma
       , b.ca
       , a.ti


texts:
Playing around with SQL Server, I came across a semi-cryptic error. Essentially, I only need to log into DAC. Is there any general advice for when DAC is needed? How do I know I need it to access sys.sysobjvalues without googling for the error message?

What all does DAC provide access to?


texts: Postgres tracking count of recent items in count table or by selecting count of recent records from history table

texts:
Which would generally be more efficient, or does it not matter?
Option 1 is incrementing a count in a table that stores counts by the unique id, and resetting the count to zero when enough time has passed for that unique id.
Option 2 is not storing a count and fetching the count of records from a history table, by the records' unique ids and within the past so many time units.
I would add an index on the id and timestamp fields for option 2.
The history table is in the low millions of records and doesn't grow that fast.
Is the periodic vacuuming of the counts table likely to be much worse than selecting the count from the history table using indexed fields?
It seems like it would require less operations, no insert or update with Option 2.


texts: Delete an oracle instance, the right way

texts:
I want to delete an oracle instance (oracle 10.2.0.4.0) created in the AIX 6.0 OS.
I know i can delete all the dbf and ctl files in the Terminal but i thinks that's no the best way to do it. I think must be a cleaner way to do that.

Thanks in advance.


texts: Easy Postgresql master/slave replication with automatic failover on Linux?

texts:
I need a reliable SQL DB for our application and have been looking at Postgresql's streaming replication support. It seems that it's not hard to setup master/slave replication and you can use the slave as a hot standby for read-only queries. All good so far. However how do I automatically promote a slave to master when the master fails (or the slave thinks the master has failed)? I know I can write a file on the slave to make it become master but am looking for scripts et al to do that (and make sure the master stays down etc.).

Anyone know of a nice tutorial on how to do this on Ubuntu 12.04? Hosted at Hetzner (for bonus points)? Chef recipes or something?


texts: How to sort the result based on a certain column when using GROUP BY in sql statement

texts:
I am new to this, so please be patient...

I have a table "tFruit".
The fields inside tFruit are "ID", "Buyer", "Seller", "Time", and "FruitName".
"ID" is the key field, the other 4 fiends has non-unique data.

When I use this SQL statement I will get all sellers sorted by Time. But an individual seller appears multiple times because they sold fruits not only once.

SELECT Seller
FROM tFruit
WHERE Buyer = 'email@address.com'
ORDER BY Time DESC

What I want is to get the list of seller, non-duplicate, sorted by Time. Thus I use this statement:

SELECT Seller
FROM tFruit
WHERE Buyer = 'email@address.com'
GROUP BY Seller
ORDER BY Time DESC

Now there is no duplicate Seller, but they are not sorted by Time.

Any ideas how to do this?

Thanks!


texts:
I have table named 'car'

              Table "db145004db.car"
  Column  |  Type  | Collation | Nullable | Default
----------+--------+-----------+----------+---------
 car_no   | bigint |           | not null |
 car_name | text   |           |          |
Indexes:
    "car_pkey" PRIMARY KEY, btree (car_no)

Table Content:

 car_no |  car_name
--------+------------
      1 | first_car
      2 | second_car
      3 | third_car

I ran this following two transaction,

Transaction 1:

begin ;
select * from car where car_no=2 for update;

Transaction 2:

begin ;
select * from car where car_no=2 for update; (waiting for first transaction to release lock)

My 'pg_locks' table looks like:

  locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |        mode         | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------
 relation      |   103777 |   143022 |      |       |            |               |         |       |          | 4/75               | 11043 | AccessShareLock     | t       | t
 relation      |   103777 |   143016 |      |       |            |               |         |       |          | 4/75               | 11043 | RowShareLock        | t       | t
 virtualxid    |          |          |      |       | 4/75       |               |         |       |          | 4/75               | 11043 | ExclusiveLock       | t       | t
 relation      |   103777 |   143022 |      |       |            |               |         |       |          | 5/144              | 11150 | AccessShareLock     | t       | t
 relation      |   103777 |   143016 |      |       |            |               |         |       |          | 5/144              | 11150 | RowShareLock        | t       | t
 virtualxid    |          |          |      |       | 5/144      |               |         |       |          | 5/144              | 11150 | ExclusiveLock       | t       | t
 relation      |   103777 |    11577 |      |       |            |               |         |       |          | 3/141              | 11263 | AccessShareLock     | t       | t
 virtualxid    |          |          |      |       | 3/141      |               |         |       |          | 3/141              | 11263 | ExclusiveLock       | t       | t
 transactionid |          |          |      |       |            |         10204 |         |       |          | 5/144              | 11150 | ShareLock           | f       | f
 tuple         |   103777 |   143016 |    0 |     2 |            |               |         |       |          | 5/144              | 11150 | AccessExclusiveLock | t       | f
 transactionid |          |          |      |       |            |         10204 |         |       |          | 4/75               | 11043 | ExclusiveLock       | t       | f

I can't see any locks related to rows/tuples (car_no=2). Also, locktype and mode are little confusing(I can't understand what they tend to convey). As a newbie to psql, I am totally confused. Can anyone help interpreting 'pg_locks', with respect to row-level locking, with respect to above scenario.


texts: Numeric vs Integer for a column - size and performance

texts:
I have an application which uses a PostgreSQL table. The table is very big (billions of rows) and has a column which is an integer.

The integer can be up to 6 digits, i.e. 0-999,999 , no negatives.

I thought about changing it to be numeric(6,0).

Would this be a good idea? Would numeric(6,0) take fewer bytes? How about the performance (this table is being queried a lot)?


texts: What should we do when same column fields occurring multiple time in many tables?

texts:
I have more than 20 tables in my database. There are some columns which occur in almost all of the tables. In that case what should I do? Do I put all those columns in every table or is there a better way to solve this problem?

Country Table Example

CountryID, CountryName, AddedBy, AddedTime, LastUpdatedBy, LastUpdateDTime, DeletedBy, DeletedTime, DeleteFlag

As we can see in the table structure above there are 9 columns. Except for CountryID and CountrynName all occur in most tables. I don't want to keep above repeating columns in all tables. So if there is any solution then please let me know.


texts:
I run into a problem with a customer which had no proper backup.

His last backup (meaning exp-dump) is from 1st Aug 2011.

However he lost due to batch DELETE statements all data.

My plan was now to get the redo-sql from the logs, import the dump and replay all sql from 1st Aug up to 2nd Sept (just before the batch DELETE happened).

However I am stuck because V$LOG_HISTORY shows me logs back to February 2011 but no V$ARCHIVED_LOG entries to know which logs to include into LogMiner.

So: How can i load this V$LOG_HISTORY logs into the LogMiner to get the redo-sql or how else can I get the redo-sql or replay it based on the dump-file fromn 1st Aug?

Side notes: This is a single alone (like an orphant) oracle 9.2 installation on a linux server.


texts: I am using SQL Server 2008 R2 Standard Edition. I am trying to implement log shipping, but the log shipping option is not showing in the database properties window. The database recovery option is bulk logged.


texts:
Can I take a log backup manually in Primary replica when all my databases are synchronized with secondary replicas in AOAG?

Will it break my Log sequence for Synchronization?

In AlwaysON AGs do we take T-Log backups for Synchronization manually or do they happen over the wire (End points 5022)? Can some one please simplify this process for my understanding?

I would like to understand will it affect my AOAG with LSN Mismatch?


texts: Storing unicode text into varchar column in SQL Server

texts:
I have mistakenly stored unicode text into a varchar column in a table and now it is shown as question marks (???).

Is it possible to get our main information back from the column instead of question marks???


texts: Which MySQL engine should i use where most operations are read operations

texts:
I want to develop an application for which i will need a database to store data. I am using MySQL as backend. My question is Which MySQL engine should i use?

Well, i have mentioned some important characteristics of operations that will be performed on db:

99.99% read operations.
I don't need foreign-keys.
I don't need transactions.
Simple read operations and nothing else.

I know that InnoDB is latest default engine in MySQL. But, as far as i can conclude, i don't need most of the features which are main reasons to use InnoDB. So, now i am left with MyISAM.

I am thinking of using MyISAM as i just want to read data from database. I have refered the official documentation for storage engines and also googled it. But, still may be that my knowledge-base is lacking some important updates. So, i am seeking help from community.

Is my choice of using MyISAM storage engine for specified scenario proper?

Thanking Community in anticipation.

I thank ypercube to help me narrow-down my choice.


texts:
What is the most sample and practical way to know the MOM (middle of month) and EOM (end of month) in a stored procedure in SQL Server 2005? (without doing a select case / switch)

EOM is the last day of the month, and MOM is the last day of the fortnight. It seems to be 15 for every month except on February.

Also I need to take care the leap-year, Holidays, business day option, etc. Like linux.


texts: Switching alias in subquery equality leads to different result

texts:
Trying to learn sql queries recently and  as the title says I stumbled upon some "behavior" i didn't understand.

This query returns the managers from the database.

SELECT *
FROM employees e
WHERE EXISTS (SELECT * FROM employees f WHERE e.employee_id = f.manager_id)

However if I switch the aliases in the equality it returns all the employees.

SELECT *
FROM employees e
WHERE EXISTS (SELECT * FROM employees f WHERE f.employee_id = e.manager_id)

Isn't employees e and employees f the same table but with different names? If so, why is then the alias order important?


texts: mysql general_log only shows a hex number instead of SQL?

texts:
When I enable sql logging to table like this:

set global log_output = 'table'
set global general_log = 'on'

Do some queries then look at the log table like this:

select * from mysql.general_log
order by event_time desc

I dont see my sql, I see hex numbers like this in the argument field or blank ('') and no SQL at all:

0x53484F5720435245415445205441424C4520606D7973716C602E6067656E6572616C5F6C6F6760

What is going wrong?


texts: Mapping between each bit in a bitmap index and tuple-pointers

texts:
Bitmap Indexes are described here, but I don't understand some part of it.

  A plain indexscan fetches one tuple-pointer at a time from the index,
  and immediately visits that tuple in the table.  A bitmap scan fetches
  all the tuple-pointers from the index in one go, sorts them using an
  in-memory "bitmap" data structure, and then visits the table tuples in
  physical tuple-location order.  The bitmap scan improves locality of
  reference to the table at the cost of more bookkeeping overhead to
  manage the "bitmap" data structure --- and at the cost that the data
  is no longer retrieved in index order, which doesn't matter for your
  query but would matter if you said ORDER BY.

To visit the table tuples, you need to know the tuple-pointers. Then there must be a mapping between each bit in a bitmap and tuple-pointers. How is this mapping maintained?

I also don't understand what he meant by "sorts them using an in-memory "bitmap" data structure".


texts: Availability Groups and Partition Swapping

texts: Is it possible to use SQL 2012 Availability Groups over two machines and perform partition swapping on the primary machine ?


texts: Slow SSMS connecting to AWS EC2 instance

texts:
I have an AlwaysOn availability group in AWS EC2. 2 nodes, multisubnet, with 1 DC in one subnet, the other DC in a separate (3rd) subnet. All in the same VPC.

When anyone connects to either of the two AG nodes, either by IP address or by the listener IP address, the result is the same: the object explorer is extremely slow to load, SQL Agent node takes about 7 seconds to load, the connection itself takes a few seconds at least.

Odd thing is that pinging the ip addresses of either AG node is very responsive <44ms.

Using RDP to connect to a server on a different subnet or entirely separate VPC yields a very responsive, snappy SSMS experience.

This is bugging me because it forces people to RDP to do any SSMS work and unfortunately some folks use the primary AG node...


texts: RMAN-06054: media recovery requesting unknown archived log, Where is SCN kept?

texts:
I have the following situation:

Backups of DB1(located on srv1 server) database are taken everyday at 1:00 AM.
I took backed up files(datafile,archivelog,controlfile,spfile) of 24/10/2011 and restored them on the server srv2.
During recovering Oracle required log file:

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 228 and starting SCN of 26651733
I searched that logfile on the server srv1 and copied it to the server srv2. Registered it with the database and run recover database command again. Still the same error, but with other sequence number and SCN.

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 229 and starting SCN of 26654944

These archivelogs are generated after 24/10/2011 so backup of this date should not know about newer archivelogs. I can set recovery until that sequence but I just wonder to know where is that SCN stored? Why does my Oracle requires newer archivelogs?

Server srv1 and srv2 are not contacting with each other.

Thank you a lot.


texts: Performance of rare SELECT vs. frequent INSERT in timeseries data

texts:
I have a simple timeseries table

movement_history (
    data_id serial,
    item_id character varying (8),
    event_time timestamp without timezone,
    location_id character varying (7),
    area_id character varying (2)
);

My frontend developer is telling me that the cost is too high if he wants to know where an item is at a given timestamp because he has to sort the table. He wants me to add another timestamp field for the next event so he doesn't have to sort. Yet that is going to more than double the cost of my code to insert a new movement as I will need to query for the previous entry for the item, update that and then insert the new data.

My inserts of course far outnumber his queries in frequency. And I have never seen a timeseries table which included an entry for the time of the next event. He's telling me my table is broken because his infrequent query requires a sort. Any suggestions?

I don't know what query he is using but I would be doing this:

select * from movement_history
where event_time <= '1-15-2015'::timestamp
and item_id = 'H665AYG3'
order by event_time desc limit 1;

We currently have about 15K items they are at most entered into the database once a day. However we will soon have 50K of items with sensor data that is updated every 1 to 5 minutes.

I do not see his query being performed very often but another query to get the current status of the pallets will be.

select distinct on (item_id) *
from movement_history
order by item_id, event_time desc;

This server is currently running 9.3 but it could be running on 9.4 if it needs to.


texts: How to optimized mysql query having large dataset

texts:
I have two tables in MySql with the following schema,

CREATE TABLE `open_log` (
  `delivery_id` varchar(30) DEFAULT NULL,
  `email_id` varchar(50) DEFAULT NULL,
  `email_activity` varchar(30) DEFAULT NULL,
  `click_url` text,
  `email_code` varchar(30) DEFAULT NULL,
  `on_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sent_log` (
  `email_id` varchar(50) DEFAULT NULL,
  `delivery_id` varchar(50) DEFAULT NULL,
  `email_code` varchar(50) DEFAULT NULL,
  `delivery_status` varchar(50) DEFAULT NULL,
  `tries` int(11) DEFAULT NULL,
  `creation_ts` varchar(50) DEFAULT NULL,
  `creation_dt` varchar(50) DEFAULT NULL,
  `on_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

The email_id and delivery_id columns in both tables make up a unique key.

The open_log table have 2.5 million records where as sent_log table has 0.25 million records.

I want to filter out the records from open log table based on the unique key (email_id and delivery_id).

I'm writing the following query.

SELECT * FROM open_log
WHERE CONCAT(email_id,'^',delivery_id)
IN (
SELECT DISTINCT CONCAT(email_id,'^',delivery_id) FROM sent_log
)

The problem is the query is taking too much time to execute. I've waited for an hour for the query completion but didn't succeed.

I've tried to make the email_id and delivery_id as composite key but that didn't help.

Kindly, suggest what I can do to make it fast since, I have the big data size in the tables.

Thanks,
Faisal Nasir


texts: Full backup of Simple recovery model - how much log is backed up if there is an open transaction

texts:
I have a situation right now.
There is a database in SIMPLE recovery model.

Note:
 1. Compression is not enabled which will be enabled pretty soon.
 2. SQL Server 2012 SP3

BackupSize: 8GB (Usually)<br/>
Last Saturday BackupSize: 73GB (yes, 73 !!)

This is because of an application that left 2 transactions open.
Right now the LOG file is 154GB.

Transaction: So far it inserted 90M records to a not so large table)

Question:

In a FULL backup, how much LOG information is recorded if there is an open transaction?
When the FULL backup is restored, does it ROLLBACK the actions by the OPEN transaction?
If so, why does FULL backup even bother to backup the LOG for an OPEN transaction when it is still open after the backup is finished (on a SIMPLE recovery model though).
If I kill the spid from SQL Server, does it need more LOG space to ROLLBACK? We are already out of space here !!


texts: pgAdmin and PostgreSQL compatibility matrix

texts:
Has anyone ever put together a version compatibility matrix for pgAdmin versions and PostgreSQL versions?

https://www.pgadmin.org/download/windows.php

https://www.postgresql.org/download/windows/


texts: Why is FORCE JOIN option slowing down query and forcing execution plan to sort prior to joining tables?

texts:
I'm linking Orders data with the Results data for those orders. Here's the SQL query for joining and selecting the
; with orders as (
    select o.*, otr.*, oo.*
    from Order o
    inner join OrderTransitionRecord otr
        on otr.Order_oid = o.ObjectID
    inner join OccurrenceOrder oo
        on oo.Order_oid = o.ObjectID
), results as (
    select ir.*, irsi.*
    from InvestigationResult ir
    left outer join InvestigationResultSuppInfo irsi
        on irsi.ObjectID = ir.ResultSuppInfo_oid
), anchor as (
    select orders.*, results.*
    from orders
    inner join results
        on results.Occurrence_oid = orders.OO_oid
        and results.IR_Patient_oid = orders.O_Patient_oid
        and results.IR_PatientVisit_oid = orders.O_PatientVisit_oid
)
select top 100000 *
from anchor

This takes ~35 seconds and follows this execution plan. If you look at the execution plan, you'll see that it first joins (OccurrenceOrder, OrderTransitionRecord) with InvestigationResult...and then joins that result with Order. My question is why doesn't it first join all three tables (Order, OccurrenceOrder, OrderTransitionRecord) that are inside the orders CTE prior to joining with the tables (InvestigationResult, InvestigationResultSuppInfo) that are in the results CTE?
I tried forcing the order of the joins by adding the OPTION(FORCE JOIN) option at the end of the select query. However, it slowed the query down  by a factor of 2 (~75 seconds). Here's the execution plan for when I force the join order. If you take a look at it, you'll see that the join order is forced correctly, but it has to sort the results of clustered index scans on all 3 Order tables before joining them. Why does it need to do that?
What can I do to speed up my query? I've already made sure that the tables have indices for all the columns that are needed in all the joins. Any and all help would be much appreciated. Thanks
Original

Force Order


texts: Stored procedure to delete the 4 large table records in small chunks

texts:
As I am new to procedures, I request your help on below pls.
There is a requirement to delete the records from a large table in small chunks like 10 Million records at a time.
For example, if you have 1000 days records in a table then you need to delete in small chunks like 10M at once. first will select 100 days records if it is below 10 Million then it will delete or else it will split into 50-50 days, like the same way I need to reduce from 1000 days to 50 days.
BEGIN
    DECLARE @ROWCOUNT INT;
    SELECT
        @ROWCOUNT = count(*)
    FROM
        table
    WHERE
        timestamp>=GETDATE()-1000 and timestamp<=GETDATE()-900;
    SELECT @ROWCOUNT;
    IF @ROWCOUNT < 10000000
    BEGIN
        DELETE FROM table where timestamp>=GETDATE()-1000 and timestamp<=GETDATE()-900;
    END
    ELSE
    BEGIN
        PRINT 'The data is more than the 10M records for the selected 100 days, you need to split into 50-50days';
    END
END

Thanks in advance!


texts: In terms of a mainframe database, what does a regularly scheduled database reorg mean?

texts:
I see this internal message floating around to remind support people in my company about a high volume high traffic mainframe database that it needs to go down for several hours for a "Bi-Monthly Database Re-organization".

I am not sure what this term means?  Does this mean they have to rebuild indices? Is it as simple as saying they have to degragment the disk storage?  I never hear DBA's having to do this for your typical vendor supplied relational databases like Oracle or SQL Server.


texts: What is exactly a uniform server in a galera cluster setup?

texts:
In many manuals about performance tuning and avoid Galera's MySQL cluster limitations they write the below suggestion.

  It is possible for a slow/overloaded master node to affect performance
  of the Galera Replication cluster, therefore it is recommended to have
  uniform servers across the cluster.

I cannot find any information about what a uniform server is? Has anybody an idea?


texts: SQL Server Management Send Mail as Part of a Job

texts:
I have a SQL Job that I would like to send an email through, but for some reason I can't get any details on why the email is not sending. This code works as expected when executed in the query window, and SQL Server Agent will also send error emails to me, so my email profile seems to be set up correctly. The code:
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MailProfile',
@recipients = 'myaccount@emailcom',
@subject = 'Bad Data Report',
@query = N' USE Database;
   DECLARE @yesterday date = getdate() - 1;  select * from Timecards WHERE Date = @yesterday AND (DailyHours > 24.1 OR DailyHours < 0 ) ',
@exclude_query_output = 1,
@attach_query_result_as_file = 1,
@query_result_header = 1,
@query_attachment_filename = 'BadDataQuery.txt'
END

Following some advice from similar issues on this site, I tried looking through sysmail_allitems and sysmail_faileditems, but neither log any attempts to send. I also created a stored procedure that I had the job call as a roundabout way to send the email, but that also did not send an email nor leave an error. Any ideas what might be going on?


texts: Encryption of Data, Log and Backup files using Always Encrypted in SQL Server 2016

texts:
When we implement the Always Encrypted feature in SQL Server 2016, do the .mdf files, .ldf files and .bak files get encrypted as well?

I would like to know if the data/log/backup files gets encrypted or not.


texts: Execute a script after transactional replication setup completes

texts:
We successfully set up transactional replication with 15 articles.
After successful of article changes Done in subscriber, we need to execute ".SQL File"  at subscriber database. Please help on this.

Create the below table in Publisher database:

Create Table DwDimTable (
Ino int Primary Key
,Item Varchar(10)
,Usage numeric(16,2)
,StartDate date
,EndDate date)

Create the below table and run the query in the Subscriber database:

Create table DwFactTable(
Ino int,
Item    varchar(15),
PerDayUsage numeric(16,5),
[Day]   int,
[Month] int,
[Year]  int,
[Date]  date)
Go
SELECT TOP (1000) IDENTITY(INT,0,1) AS N
INTO dbo.Multi
FROM sys.columns c1, sys.columns c2

Insert the data in the Publisher with below query:

insert into DwDimTable
values
    (1,'Gas Bill','9500.00',cast(GETDATE() as date),
    cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) as date))

Set up transactional replication for the article ‘DwDimTable’. After successful of transaction replication.

Here, I need assistance to execute/run automatically below .SQL file/Query
automatically at subscriber once publication is done:

insert into DwFactTable
SELECT DISTINCT
Ino
,Item
,sum(Usage)/(DATEDIFF(DD,StartDate,EndDate)+1) [PerDayUsage]
,DD as [Day]
,MM as [Month]
,YY as [Year]
,CAST(CAST(MM AS VARCHAR)+'/'+CAST(DD AS VARCHAR)+'/'+CAST(YY AS VARCHAR) AS DATE) AS [Date]
FROM   DwDimTable
CROSS APPLY (SELECT DAY(DAYDT) AS DD, MONTH(DAYDT) AS MM, YEAR(DAYDT) AS YY
             FROM (SELECT TOP (DATEDIFF(DAY,StartDate,EndDate) + 1)
                          DATEADD(DAY, N, StartDate) DAYDT
                   FROM DBO.Multi) AS R
            ) F
GROUP BY Ino,Item,Usage ,DD,MM,YY,StartDate,EndDate,
CAST(CAST(MM AS VARCHAR)+'/'+CAST(DD AS VARCHAR)+'/'+CAST(YY AS VARCHAR) AS DATETIME)


texts:
I'm designing a database with multiple lookup tables containing possible attributes of the main entities.  I'm thinking of using a 4 or 5-character key to identify these lookup values rather than an auto-incrementing integer so that when I store these attribute IDs on the main tables I'll see meaningful values rather than just random numbers.

What are the performance implications of using a character field as a primary key rather than an integer?

I'm using MySQL if that matters.

[Edit]
These lookup tables have new records added infrequently.  They are manually maintained, and the character-based keys are manually created as well.  Here's an example:

      CUISINES
 ID      Description
-----  --------------
CHNSE  Chinese
ITALN  Italian
MXICN  Mexican


texts: How Large Table before Using Clustered Columnstore Index

texts:
At what size should we consider applying Columnstore Index on a table? Is there a SQL Server storage or design guidance?

Our Data Warehouse is 5TB total. Additionally, we have tables which are 100GB to 1TB which compose the database. Just curious if SQL Server has guidance on how large should table before a columnstore index is applied?

For Fact Tables: Eg, should we have columnstore index on table that is 300MB, 1GB, 1TB. Or should we have on all tables, and size does not matter?

For Dimension Tables: Additionally, should we have columnstore indexes on lookup tables? These are small, generally 2-50 MB

This article never stated

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance?view=sql-server-2017


texts: Why isn't INFORMATION_SCHEMA not displayed in the MySQL Workbench UI by default?

texts:
I am running MySQL Workbench 6.3 CE.  The following is the default look of the UI:

I can query the INFORMATION_SCHEMA database, but it is not shown by default under the SCHEMAS section in the navigator panel on the left.  Why is this, and how can I get this database to show up on the said panel?


texts: How to properly index hstore tags column to faster search for keys

texts:
I imported a large area of OpenStreetMap's planet.osm file into a postgresql database. The database contains a table called nodes. Each node has a geometry column called geom and a hstore column called tags.
I need to extract nodes along a line that have certain keys in the tags column. To do that I use the following query:

SELECT id, tags
FROM nodes
WHERE ST_DWithin(nodes.geom, ST_MakeLine('{$geom1}', '{$geom2}'), 0.001)
AND tags ? '{$type}';

$geom1 and $geom2 are geometries for start and end points of my line.
The $type variable contains the key I want to search for. Now, it can have one of the following values: 'historic' or 'tourist'.

The query given above works but it is too slow. I guess searching for a key in tags column takes too much time. I read about GIN and GIST indexes and I generated  a GIN index using the following query:

CREATE INDEX nodes_tags_idx ON nodes USING GIN(tags);

After creating the index I searched again for nodes using the same first query but there is no change in performance.

How can I properly use GIN and GIST to index tags column so I can faster search for nodes that have a certain key in tags column?

Thank you


texts: Update Postgres Table with Array of JSON

texts:
I am using Postgres  9.5, and I am trying to figure out how to update a postgres table using an array of JSON. I want each object in the array to coorespond to a new row, with each key cooresponding to a column, and each value is the data to be inserted into the column. I am trying to do so with a function. Here the the data format:

[
    { col1: a, col2: 5, col3: 1, col4: one},
    { col1: b, col2: 6, col3: 2, col4: two},
    { col1: c, col2: 7, col3: 3, col4: three},
    { col1: d, col2: 8, col3: 4, col4: four},
]

Here is my expected output:

 col1   (varchar)| col2 (integer) |   col3 (integer)   |   col4 (varchar)
-----------------+----------------+--------------------+------------------
    a            |  5             |     1              |    one
    b            |  6             |     2              |    two
    c            |  7             |     3              |    three
    d            |  8             |     4              |    four

Is there a built in postgres JSON function or operator that can do this for me? Or do I have to loop through the array and pull out each value, and pass it as an input? I know function below is wrong, but my goal is for the function to act similar to the following:

CREATE OR REPLACE FUNCTION UPDATE_TABLE_FUNC (
arrayOfValues TEXT[]
)
RETURN VOID AS $$
BEGIN
UPDATE table SET (col1, col2, col3, col4) = ($1)
END;
$$ LANGUAGE plpgsql;


texts: Group multiple rows with different column values to a single row

texts:
I have 3 records with 3 column values for the same key.
PFB sample data.

CREATE TABLE student
(
    student varchar(100),
    maths int,
    physics int,
    chemistry int
)
PRIMARY INDEX (student)
ON COMMIT PRESERVE ROWS;

INSERT INTO student('Student_100', 90,   ,   );
INSERT INTO student('Student_100',   , 80,   );
INSERT INTO student('Student_100',   ,   , 70);

Now, for student_100, there are 3 records with 3 column values.
I want to get output like below

StudentID   | Maths | Physics | Chemistry
------------+-------+---------+----------
Student_100 |  90   |   80    |    70

A single record with all column values. I don't want to use SUM function, because I have columns with Date type as well. So I am looking for a solution that fits all datatypes.


texts: timeout while dropping database - MongoDB

texts:
Getting time out issue while dropping database from mongoDB. Database size is around 200GB.

   {
          "code" : 46,
          "ok" : 0,
          "errmsg" : "timed out waiting for DBNAME.Collectionname"
   }


texts: MySQL connection fails with one database and not the other

texts:
I have 2 databases on mysql. ge and ge_sc001.

I can access both of these through my asp.net mvc application locally.

Web.config

 <connectionStrings>
<add name="GEContext_sc001" connectionString="server=localhost;port=3306;uid=root;pwd=***;database=ge_sc001" providerName="MySql.Data.MySqlClient" />
<add name="GEContext_sc002" connectionString="server=localhost;port=3306;uid=root;pwd=***;database=ge" providerName="MySql.Data.MySqlClient" />
<add name="GEContext" connectionString="server=localhost;port=3306;uid=root;pwd=***;database=ge" providerName="MySql.Data.MySqlClient" />

Later on when i deployed my application on the web server, and tried to access them from a remote machine. I changed server=localhost to my machine's external-facing IP address. Now I can access only one ge. when i try to access the second it gives me error.

  Access denied for user 'root'@'' to database 'ge_sc001'

Notice there is no localhost above i.e. 'root'@'localhost'

SHOW GRANTS

'GRANT ALL PRIVILEGES ON . TO \'root\'@\'localhost\' IDENTIFIED BY PASSWORD \'*xxx\' WITH GRANT OPTION' 'GRANT PROXY ON \'\'@\'\' TO \'root\'@\'localhost\' WITH GRANT OPTION'

UPDATE

I ran

GRANT ALL ON ge_sc001.* TO 'root'@'<external_ip>';

Now the error has change to

  The provider did not return a ProviderManifestToken string.

This is now happening on both of my schemas


texts: How does the disk and file share witness work?

texts: Can somebody provide an in depth explanation to how the disk witness and file share witness assign a vote to a node?


texts: How to create an aggregate to find min and max value of an integer column and return output in text format?

texts:
I have a function where I am specifying the table and column but I require an aggregate that can work for any table and column for similar output
create function min_max_val()
returns text
 LANGUAGE plpgsql
 AS $$
  declare
  mmv text;
  begin
  select format('%s - %s',min(id),max(id) )min_max_vals into mmv from towns;
  return mmv;
 end;
$$ ;

    min_max_vals
---------------------
 1000001 - 11000000


texts:
I am trying to search for the ? character in a string, but across all dbs on a server. Since the ? mark is a placeholder for the database name, how may I format the query so it knows I mean to search for ? and not replace it with the database name?

The WHERE clause at this time is:

WHERE DirectoryPath LIKE ''%?%''

This renders to:

WHERE DirectoryPath LIKE '%DATABASENAME%'

I need it to render as:

WHERE DirectoryPath LIKE '%?%'


texts: How can I view the number of locks held by MySQL InnoDB, when the total is larger than 10?

texts:
We have a problem where our ORM is locking more tables than I'd expect. This is causing deadlock on our production system which I'm struggling to diagnose.

I can see, using either SHOW ENGINE INNODB STATUS or by using the innotop tool the top 10 locks, but I'm unable to see the rest.

Here's the head and tail of an example output from SHOW ENGINE INNODB STATUS:

--TRANSACTION 1832883, ACTIVE 1728 sec
29 lock struct(s), heap size 3520, 14 row lock(s), undo log entries 10

...

10 LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS

How can I view the locks beyond the first 10?

Perconda has the innodb_show_locks_held variable - is there an alternative for MySQL? I'm using MySQL 5.7 in development if that's relevant.

Thanks,
Ed