texts: MongoDB automatic failover between 2 data centers

texts:
If I deploy a MongoDb replica set across only 2 data centers, if one entire data center fails, can there be automatic failover to the other data center?

Consider the situation where 1 data center has a primary, secondary and an arbiter, and the second data center has 2 secondary members. Essentially, a 5 member replica set.

If data center 2 goes down, there will still be 3 members in the replica set in data center 1. That is great.

In reverse, if data center 2 remains up and data center 1 goes down, there will only be 2 members left out of the 5 member replica set. That is not enough to have either secondary vote themselves to become primary.

Is that correct? If not, what am I doing wrong.

If this isn't possible, please let me know so I can explore other options.

Adding a third data center to house the arbiter is not an option.


texts: Update my local db based on scripts executed in my server database

texts:
I need to create an solution that update my local db version based on changes that was made on my server database.
First, I need to check the differences that has exists between these databases and so, based on a number of control,
and I will be able to know whom scripts i need to execute in my local db. I will create an table to do  control of what was executed and not.
But I need an 'insight' on how i will store the script that was executed on my server db to be able to execute it again in my local database to make the update,
and make both of them stay in the same 'version'. This need be done just by click on a button on my application, in other words, I pretend to automate this task.

P.S.: Sorry for my English :-)  this is the first time that I make an question on a English forum.


texts: Why does a many to one merge join change the sort order of the data set?

texts:
I have a query that calculates row_Number(). The table has a clustered index on the same columns (and order) as the partitioning and ordering for the row number.

When using a merge join (many to one), a sort is required, even though the clustered index is in the correct order. Removing the join also removes the sort operation.

The clustered index that should power the row_number calculation:

create clustered index [ClusteredIndex_e060df3fbf464a8eb9b6ea5d46a9a5f5] on [dbo].[log1]
(
    [client] asc,
    [orderId] asc,
    [campaign] asc,
    [id] asc,
    [DateStamp] asc
)

create clustered index [ClusteredIndex_dd0ee53e050d436cba2cab7c678a39e5] on [dbo].[LiveReference]
(
    [client] asc,
    [orderId] asc,
    [campaign] asc
)

The query:

    with cr as
(
    select distinct client, orderId,campaign
    from LiveReference
)

select e.[DateStamp]
  ,e.[campaign]
  ,e.[client]
  ,e.[orderId]
  ,e.[ad]
  ,e.[id]
  ,e.[source]
,row_number() over (partition by e.[client] ,
                        e.[orderId] ,
                        e.[campaign] ,
                        e.[id]
                    order by e.[DateStamp]) as num
from [dbo].[log1] e
inner join cr on
                        e.client = cr.client
                        and e.campaign = cr.campaign
                        and e.orderId =  cr.orderId

which gives the below plan:

Removing the join also removes the sort:

select e.[DateStamp]
  ,e.[campaign]
  ,e.[client]
  ,e.[orderId]
  ,e.[ad]
  ,e.[id]
  ,e.[source]
,row_number() over (partition by e.[client] ,
                        e.[orderId] ,
                        e.[campaign] ,
                        e.[id]
                    order by e.[DateStamp]) as num
from [dbo].[log1] e

(I know this also removes the filtering performed by the join, but this doesn't explain why excluding those rows changes the order)

Why would the results of a sorted join then not be in the correct order?


texts: Implementing a Record Keeper/Rollback System for a MySQL Database

texts:
What's the best way to create a record keeping table for all actions in my database?

I have a number of tables inside one database and I want to create another that records all movements in every other table - adds, removals, delete and edits, which can then be reviewed by a user with top-level administrative privileges and reversed if necessary at the click of a button.

Is there a tried and tested method for this or a native feature of (InnoDB) MySQL useful for this?

If possible, can you point me to resources specific to this please. I'm working on my first database so I apologise if this has been asked here before, but I couldn't find it.


texts:
Can someone please let me know the view name to see all backed-up transaction logs, their sizes in SQLServer ?

In Oracle, it is v$archived_logs. Similar to this, I want to know the view for sqlserver.


texts: What steps to take to resize an existing MYSQL NDB Cluster?

texts:
I have an existing NDB Cluster. It's grown larger than I expected and I need to make it bigger. When I ask the management console for the ALL REPORT MEMORY, it looks like the datamemory is 65% full. However, I get page faults, swap, and memory related issues.

How do I find what my correct settings for data-memory, index-memory and any other related parameters should be?

Is ndb_size.pl the correct script to run?


texts: How to properly restart a MySQL Master server?

texts:
We are running on Azure VM wherein you can not expand the attached disk while the VM is running. So we need to stop the VM first before doing anything on the disk size/performance.
We just have 1 slave server for this replication.
I've read some procedures online in doing this, please confirm if this is appropriate.
On Slave Server
STOP SLAVE;

On Master Server
sudo service mysql stop

Once stopped, update the VM settings (expand disk). Then start the VM.
On Slave Server
START SLAVE;


texts:
I have a question regarding database table design.

I have a product master table which has all the details of a product.

User would be adding notes against each product (product number) and for each product there would be multiple notes.

Proposed columns for notes against product.

Subject
Description
Entered BY
Updated By
Entered Date
Updated Date.

Should I create two tables, one to keep the product no and another table to keep the notes and the other columns or one table to keep the product no and other columns?


texts: Why does searching for LIKE N'%�%' match any Unicode character and = N'�' match many?

texts:
DECLARE @T TABLE(
  Col NCHAR(1));

INSERT INTO @T
VALUES      (N'A'),
            (N'B'),
            (N'C'),
            (N'Ƕ'),
            (N'Ƿ'),
            (N'Ǹ');

SELECT *
FROM   @T
WHERE  Col LIKE N'%�%'

Returns

Col
A
B
C
Ƕ
Ƿ
Ǹ

SELECT *
FROM   @T
WHERE  Col = N'�'

Returns

Col
Ƕ
Ƿ
Ǹ

Generating every possible double byte "character" with the below shows that the = version matches 21,229 of them and the LIKE N'%�%' version all of them (I've tried a few non binary collations with the same result).

WITH T(I, N)
AS
(
SELECT TOP 65536 ROW_NUMBER() OVER (ORDER BY @@SPID),
                 NCHAR(ROW_NUMBER() OVER (ORDER BY @@SPID))
FROM master..spt_values v1,
     master..spt_values v2
)
SELECT I, N
FROM T
WHERE N = N'�'

Anyone able to shed any light as to what is going on here?

Using COLLATE Latin1_General_BIN then matches the single character NCHAR(65533) - but the question is to understand what rules it uses in the other case. What's special about those 21,229 characters that match the = and why does everything match the wildcard like? I presume there is some reason behind it that I am missing.

nchar(65534) [and 21k others] work just as well as nchar(65533). The question could have been phrased using nchar(502)  equally as � - it behaves the same both as LIKE N'%Ƕ%' (matches everything) and in the = case. That's probably quite a big clue.

Changing the SELECT in the last query to SELECT I, N, RANK() OVER(ORDER BY N) shows that SQL Server can't rank the characters. It seems that any character not handled by the collation is considered equivalent.

A database with a Latin1_General_100_CS_AS collation produces 5840 matches. Latin1_General_100_CS_AS cuts down the = matches quite considerably, but doesn't change the LIKE behaviour. It does seem like there is a pot of characters that has got smaller in later collations that all compare equal and are ignored in wildcard LIKE searches then.

I am using SQL Server 2016. The symbol � is the Unicode replacement character, but the only invalid characters in the UCS-2 encoding are 55296 - 57343 AFAIK and it is clearly matching perfectly valid code points such as N'Ԛ' that are not in this range.

All these characters behave like the empty string for LIKE and =. They even evaluate as equivalent. N'' = N'�' is true, and you can drop it in a LIKE comparison of single spaces LIKE '_' + nchar(65533) + '_' with no effect. LEN comparisons yield different results though, so it's probably only certain string functions.

I think the LIKE behaviour is correct for this case; it behaves like an unknown value (which could be anything). It happens also for these other characters:

nchar(11217) (Uncertainty Sign)
nchar(65532) (Object Replacement Character)
nchar(65533) (Replacement Character)
nchar(65534) (Not a Character)

So if I want to find all the chars that represent uncertainty with the equal sign I would use a collation that supports supplementary characters like Latin1_General_100_CI_AS_SC.

I guess these are the group of "non weighted characters" mentioned in the documentation, Collation and Unicode Support.


texts: Have to run same queries against many servers (30+), use CURSOR or LOOP

texts:
I have to do the following 30 times
connect to server with unique IP using TSQL
download a table to temp table
do some query on the temp table (same query 30times)
at end union all the tables for a final solution
Would loop or cursor or some other approach be a better approach?  I don't want to copy paste similar SQL 30 times but that is also an option.
I am currently using linked server to connect to each server


texts:
This is my table:

- user_id INT (foreign key on `user` with the reference `id`)
- question_id INT (foreign key on `question` with the reference `id`)
- option_id INT (foreign key on `option` with the reference `id`)
- exam_id INT (foreign key on `exam` with the reference `id`)
- order INT

My Index is: user_id, exam_id

My Query:

select * from `user_answer` where `user_id` = '48' and `exam_id` = '1' and `order` > '10' order by `order` desc limit 1;

I think it should use my index but this is the result of EXPLAIN:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'user_answer', 'range', 'user_answer_exam_id_foreign,user_answer_user_id_exam_id_index', 'user_answer_exam_id_foreign', '4', NULL, '10', 'Using where; Using filesort'

Apperently, It is not using my index. When I use FORCE INDEX:

select * from `user_answer` force index (user_answer_user_id_exam_id_index) where `user_id` = '48' and `exam_id` = '1' and `order` > '10' order by `order` desc limit 1;

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'user_answer', 'ref', 'user_answer_user_id_exam_id_index', 'user_answer_user_id_exam_id_index', '8', 'const,const', '10', 'Using where; Using filesort'

Any idea what's wrong with me or MySQL ?


texts:
I am new to SQL Server and I have a problem.

I created a database to store dates in an nvarchar field but now I need to convert that field to datetime. I searched the internet and couldn't find a proper solution.

One thing I found was to create a new datetime column, copy string value to that, then rename the new column to original one.

My string value format is 21/11/2014. I need to keep the same format. If anyone could give me a SQL Server query that can do that, I'd highly appreciate. I'm using SQL Server Management Studio 2014.


texts: How to resolve red invalid icon for Informatica mapping

texts: I'm having a difficult time resolving a red "invalid" icon within Informatica.  I've done all possible scenarios for checking in / checking out this object (mapping).  It currently does not have any dependencies.  How can I fix this?


texts: Do you need to include all foreing keys of a parent table in the child table?

texts:
Suppose you have a table

my_table
-----------
id
name
foreign_id

And you are creating another table that will reference my_table. I think that this would be enough:

other_table
-----------
id
name
my_table_id

However, when creating a diagram on MySQL workbench, if I create a relationship, whether identifying or non-identifying, 1 to 1, or 1 to many, I get this table:

other_table
-----------
id
name
my_table_id
foreign_id

As you can see, both, the id of my_table, and the foreign_key of my_table get added to the new table. Is that really necessary? I would say that other_table.foreign_id is not necessary.

I first noticed this when I was working on schemabank.com (which seems to be gone now, such a shame). And now I noticed it too on MySQL workbench.


texts:
i have one large database (more than 1,500 tables and total db size of 56GB) and i would like to ask for tips and guidance regarding the maintenance of such large database!

Will the size be a problem anytime? For example when i reach 4000 or 5000 tables, i am going to have an issue? At the moment i have a "problem" with the backup/restore of it. A complete backup takes around 5 hours to finish, so you can imagine how long it takes the restore!

As you can understand this is not a proper solution, because in case of a db crash, it will take me at least 12 hours to be back online! Any suggestion?


texts: SQL show only the oldest date with same number

texts:
I'm writing some code but there are some errors. If the numbers are the same, to show only one record of the oldest date. Thanks for help.

The errors: SQL command not properly ended and not a single-group group function

My code in the following

    select
       DIVISION,
       DEPT_CODE,
       NO,
       min(START_DATE),
    row_number() over (order by
       DIVISION,
       DEPT_CODE
    ) as "NEW_NO"
    FROM INFO
    Group by NO
    where (TITLE IS NULL
      OR (upper(TITLE) NOT LIKE '%RESEARCH%'
      AND upper(TITLE) NOT LIKE '%POSTDOCTORAL FELLOW%'
      AND upper(TITLE) NOT LIKE '%PROJECT%'
      )
    )


texts:
MySQL.

Let there is a table with fields startdate and kind.

Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is UNIQUE would be also interesting.

Let a date d is specified. I want to retrieve the data of given kind which corresponds to the latest startdate which is before d.

As far as I understand this cannot be efficiently done with views.

So, should it be done with a stored procedure? I am a novice in the topic of stored procedures. Your code would be welcome.


texts: Database Consolidation / AlwaysOn Availability Groups

texts:
How can I calculate the potential savings in RAM, Storage and CPU by consolidating a certain number of servers into single server?
How can I go about consolidating the BI servers' and the OLTP servers' databases. Do I have to separate the consolidation of the OLTP servers' databases from that of the BI servers' databases?

The end goal is to put all the databases in one availablility group to create redundancy but adding a replica.

Any suggestions or materials will be highly appreciated.


texts:
My boss would like me to find which (of approx 400+) stored procedures are not being used by our website any more. I'm found and reworked a script to give me the details of most recent executions, however it only goes back as far as 10:17am September 1st.

My boss assures me that (although he likes to tinker with the server in his spare time) the server has been up since May 5th.

I'm pretty new to this, so explain it like I'm a particularly simple house pet, but is there are reason why my results table only goes back 1 month, instead of the full 6? I would assume that uptime is reset if the server is restarted, but I'm not sure why I wouldn't have any history that far back.

Does viewing the procedure by using right-click -> modify count as an execution?

SQL Query

SELECT qt.text AS 'SP Name',
qt.dbid as 'SP DB',
qs.execution_count AS 'Execution Count',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_physical_reads AS 'PhysicalReads',
qs.last_execution_time 'Last Executed'
FROM sys.dm_exec_procedure_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
where qs.database_id = 5
ORDER BY qs.last_execution_time asc

My question relates more to the time frame of the results. I'm finding the information I need in the month of results I have but I'm worried there are 6 months of results that aren't being found found.

This returns May 5th:

select create_date from sys.databases where name = N'tempdb';

The following returns "2015-05-05 08:27:42.080":

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

Finally, this one returns "2013-09-15 19:34:34.667":

SELECT top 1 [rs].[destination_database_name], [rs].[restore_date]
FROM msdb..restorehistory rs
where destination_database_name = '%ID%'
ORDER BY [rs].[restore_date] DESC;


texts:
I have been using the REPLACE function to remove characters from a text value in a table. It has just grown over time and now the query is quite messy.

What can I do to make this more efficient? Is there a Regex example that I could implement?

Query:

SELECT
        REPLACE(
           REPLACE(
              REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(dbo.[RemoveNumericCharacters](Filename), '.xlsx', '')
                                        , '.xls', '')
                                        ,'csv', '')
                                        ,'---_pdf', '')
                                        ,'.', '')
                                        ,'-Jun-', '')
                                        ,'-Jul-', '')
                                        ,'Jul', '')
                                        ,'__', '_')
           as 'ConvertedFilename'
FROM @Files

Function:

ALTER Function [dbo].[RemoveNumericCharacters](@Temp nvarchar(1000))
Returns nvarchar(1000)
AS
Begin

    Declare @NumRange as varchar(50) = '%[0-9]%'
    While PatIndex(@NumRange, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1, '')

    Return @Temp
End


texts: Parsing a section of a pipe-delimited string

texts:
I have a pipe-delimited string that ends with a |. I need the information between the second last | and the last |.

Example:

a|b|c|d|e|f|g|h|ijk|

I need to return ijk. The string will always have 9 pipes if that helps.


texts: New users not working on MySQL 5.6 in Amazon RDS

texts:
I'm using the CREATE USER and GRANT commands as mentioned here http://dev.n0ise.net/2012/09/amazon-rds-mysql-add-additional-users-with-limited-previledges/

When I try to connect I get this in MySQL Quer Browser:

Could not connect to host '...sa-east-1.rds.amazonaws.com'.
MySQL Error Nr. 0

Output in MySQL Workbench:

Your connection attempt failed for user 'site' from your host to server at ...sa-east-1.rds.amazonaws.com:3306:
  MySQL server has gone away

Some details:

MySQL version: 5.6.13
Credentials are typed correctly. When I miss them I get the standard 1045 access denied error.
The user created with the instance and another limited user I created some weeks ago are working fine
Same commands worked fine on a non RDS server

Any ideas?

Thanks in advance.


texts:
Any idea, why the first of the following queries returns a row with -1, NULL, although it has a WHERE 1=2?

Only when I put the query in another subquery it works correct (and returns an empty result set).

Tested on Microsoft SQL Server 2014 and 2016

DECLARE @i INT = 1

SELECT @i i, MAX(sub.id) mid
  FROM (SELECT TOP(@i) x.id
          FROM (VALUES(1), (2), (3), (4)) x(id)
         WHERE x.id > 2 + @i
         ORDER BY x.id) sub
WHERE 1 = 2

SELECT s1.i, s1.mid
  FROM (
        SELECT @i i, MAX(sub.id) mid
          FROM (SELECT TOP(@i) x.id
                  FROM (VALUES(1), (2), (3), (4)) x(id)
                 WHERE x.id > 2 + @i
                 ORDER BY x.id) sub
       ) s1
WHERE 1 = 2


texts: How can I detect the use of SQL Server replication on a server (all versions) preferably using PS or CMD

texts:
I am trying to find a way to detect if a customer has SQL Server replication on. This helps decide whether they can use Express (as they frequently request) and this is one of the blocking factors.

Preferred responses would use a Powershell or command line script and would not require anything additional to be installed as this would need to work on many different server configurations. Also, the version of SQL Server would vary.

I do not need to know status or do monitoring, just check whether it is in use.

Windows/MSSQL are not my strong suit, so if there are any incorrect assumptions in this question, please feel free to point them out.

The goal is to login via RDP, open PS or CMD, paste in a command and know right away whether customer has replication and will not be able to utilize SQL Server Express (I know there are other factors, but this one is the first I am tackling).

As a quick note, I did do a lot of research on this before I came to ask. I found tons of information on monitoring replication, but nothing to just check if it was there. I wanted to put this to make sure nobody thinks I skipped the "rtfm" or "google it" steps.


texts:
I am researching MySQL cluster. For the purpose, I do a demo with 4 virtual machines. I have some problems in practice.

Topology

My topology contains four nodes
Node 1: management node, ip address 192.168.56.205
Node 2: sql node, ip address 192.168.56.206
Node 3: data node 1, ip address 192.168.56.207
Node 4: data node 2, ip address 192.168.56.208

Other information

Linux OS: Centos 6.4, 32 bits
Linux kernel: 2.6.32-358.el6.i686
MySQL Cluster version: MySQL-Cluster-gpl-7.3.6-2.el6.i686.rpm-bundle.tar
RAM 128MB
Turned off firewall iptables
Disabled selinux

Installation

On all four nodes, I install MySQL-Cluster-server-gpl-7.3.6-2.el6.i686.rpm and MySQL-Cluster-shared-compat-gpl-7.3.6-2.el6.i686.rpm
Except Node 2 - sql node, I install more MySQL-Cluster-client-gpl-7.3.6-2.el6.i686.rpm

Configuartion

On node 1 - management node

Make directory /var/lib/mysql-cluster, assign owner:group is mysql:mysql
Make /var/lib/mysql-cluster/config.ini:

[ndbd default]
NoOfReplicas=2
DataMemory=80M
IndexMemory=18M

[tcp default]
portnumber=2202

[ndb_mgmd]
hostname=192.168.56.205
datadir=/var/lib/mysql-cluster

[ndbd]
hostname=192.168.56.207
datadir=/usr/local/mysql/data

[ndbd]
hostname=192.168.56.208
datadir=/usr/local/mysql/data

[mysqld]
hostname=192.168.56.206

On node 2 - sql node

[mysqld]
datadir = /var/lib/mysql
port = 3306
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
ndbcluster

[mysql_cluster]
ndb-connectstring=192.168.56.205

On node 3 and node 4 - data nodes

Make directory /usr/local/mysql/data, assign owner:group is mysql:mysql
Config /etc/my.cnf:

[mysqld]
ndbcluster

[mysql_cluster]
ndb-connectstring=192.168.56.205

Start up

On management node, I run:
    ndb_mgmd -f /var/lib/mysql-cluster/config.ini
On data node 1 và 2, I run:
    ndbd
Finally, on sql node, I run:
    mysqld_safe &

Result

ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2 (not connected, accepting connect from 192.168.56.207)
id=3    @192.168.56.208  (mysql-5.6.19 ndb-7.3.6, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.56.205  (mysql-5.6.19 ndb-7.3.6)

[mysqld(API)]   1 node(s)
id=4 (not connected, accepting connect from any host)

Error:

Node 2: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 11.

There is ndb_2_error.log in /usr/local/mysql/data of node 2

The content of ndb_2_error.log:

Time: Monday 28 July 2014 - 09:45:01
Status: Temporary error, restart node
Message: WatchDog terminate, internal error or massive overload on the machine running this node (Internal error, programming error or missing error message, please report a bug)
Error: 6050
Error data: Job Handling
Error object: /export/home/pb2/build/sb_0-12598553-1404293345.9/rpm/BUILD/mysql-cluster-gpl-7.3.6/mysql-cluster-gpl-7.3.6/storage/ndb/src/kernel/vm/WatchDog.cpp
Program: ndbd
Pid: 1709
Version: mysql-5.6.19

Warning for three nodes: node 2, node 3 and node 4

2014-07-28 09:47:23 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 192.168.56.208. Returned error: 'No free node id found for mysqld(API).'
2014-07-28 09:47:23 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 192.168.56.207. Returned error: 'No free node id found for mysqld(API).'
2014-07-28 09:47:23 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 192.168.56.206. Returned error: 'No free node id found for mysqld(API).'

Thanks for your time.


texts:
Everytime I restart my server, the database is always in recovery mode, and it takes about 20 minutes for it to behave as normal.  This always and only happens when I restart the server, so I have a few questions...

I was told this could be caused by a large log file? Could that be correct? If not then what could be the other causes?
I need to lower the space of the log file to prevent recoveries.  What is better: shrinking or truncating?
How can I shrink or truncate a log file / database to lower the size? What is the syntax?

I am currently using Microsoft SQL Server 2008.


texts:
SQL Server 2008, connecting via SQL Server Authentication.

I have a stored procedure in DatabaseA which calls sp_send_dbmail in msdb to send an email with a file attachment. The file is on the db server, not on a remote fileshare.

The SQL Server account being used is not sysadmin, but does belong to the DatabaseMailUserRole in msdb.

Sending an email without an attachment is fine, but when an attachment is present I get an error:

  The client connection security context could not be impersonated. Attaching files require an integrated client login

There are a few articles/posts about this out there, but some seem to say conflicting things. I've been looking into impersonation, and one thing that does work is in the stored procedure in DatabaseA, to do the following:

EXECUTE AS LOGIN = 'sa' -- or any account with sysadmin privileges
EXECUTE msdb..sp_send_dbmail ....
REVERT

I wasn't expecting this to work as I thought to send attachments, you needed to use Windows Authentication. However it does work, but it means the lower privileged SQL Server account needs to be granted permission to IMPERSONATE the sa (or other sysadmin account).

Doing my due diligence as a dev before unleashing a DBA's nightmare into the wild...

My question is: what is a good/safe way of allowing a user authenticated via SQL Server (non sysadmin) to send email attachments from the local db server disk without opening up a security hole?

Update:

Re: Credentials

I've created a new Windows Login, created credentials for that account via SSMS, mapped those credentials to my limited privileges SQL Server account. I get the error:

  Msg 22051, Level 16, State 1, Line 0
  The client connection security context could not be impersonated.
  Attaching files require an integrated client login

I must be missing something!


texts: Index Seek scanning whole table dependent on parameter value

texts:
I have a query:

SELECT  Id,
        ColumnA,
        ColumnB
FROM    MyTable
WHERE   ColumnA = @varA OR
        ColumnB = @varB

The table is defined as

CREATE TABLE MyTable
(
    Id INT IDENTITY(-2147483648,1) PRIMARY KEY,
    ColumnA VARCHAR(22)
    ColumnB VARCAHR(22)
)

and there is a non clustered index on the table

CREATE INDEX IX_MyIndex ON MyTable
(
    ColumnA
)

When I run the query with the parameters below:

DECLARE @varA nvarchar(4000) = ''
DECLARE @varB  nvarchar(8) = '10140730'

The execution plan shows an index seek on IX_MyIndex, however it shows number of rows read as 17million rows but actual number of rows as 0 (There are 0 rows in MyTable.ColumnA with the value '')
If I turn SET STATISTICS IO ON I can see the full table is being read

This makes sense as per: this article in the section "Here’s a “bad” index seek"

However, when I run the same query with the parameters:

DECLARE @varA nvarchar(8) = 'a'
DECLARE @varB  nvarchar(8) = '10140730'

The seek operator doesn't have a "number of rows read" property (there are 0 rows MyTable.ColumnA in with value 'a') and SET STATISTICS IO reports single figure logical reads

Incidentally, the plan has an implicit convert warning and the issue goes away when I change the query like so:

SELECT  Id,
        ColumnA,
        ColumnB
FROM    MyTable
WHERE   ColumnA = CONVERT(VARCHAR(22),@varA) OR
        ColumnB = CONVERT(VARCHAR(22),@varB)

Or change the underlying column to NVARCHAR

However, I am curious as to why the behaviour of the index seek with the two different values for @varA is different even though both of them return the same number of records in the table (0)


texts: List all the MS SQL Server Objects ->LinkedServers->Providers using SQL

texts:
How to List all MS SQL Server Providers List available using SQL.
like
 Microsoft.ACE.OLEDB.12.0
 Microsoft.Jet.OLEDB.4.0
 SQLXMLOLEDB.4.0
 SQLXMLOLEDB
 SQLOLEDB


texts: SQL Server : which role grants permission to execute all stored procedures?

texts:
Which database role membership grants permission to execute all existing stored procedures in SQL Server 2012?

I tried adding a user to each of them and am still unable to execute a stored procedure. I don't want to grant EXECUTE for each stored procedure separately, I want to add the user to a role and he be able to execute any of them.


texts: Trouble adding foreign key on lots of data in Postgres

texts:
Working with PostgreSQL 9.0.8 on linux. Can't add foreign key.
I have two tables: Table1 and Table2.
Table1 has 10 columns and 200 000 rows filled with some data, where first column is 'id' (primary key).
Table2 has 0 rows and 2 columns: id (primary key) and table1_id.
Adding foreign key from Table2.table1_id to Table1.id blocks database (can't select any data - got "FATAL:  remaining connection slots are reserved for non-replication superuser connections" error). I tried to wait for 6 hours, but nothing happened. Then I restarted database and this restored normal database work, but foreign key wasn't added anyway.
Any advice?


texts: Is using DISTINCT as a hint in subqueries useful?

texts:
Does adding DISTINCT in the following example have any impact on the query running time?
Is it wise to use it as a hint sometimes?

SELECT *
FROM   A
WHERE  A.SomeColumn IN (SELECT DISTINCT B.SomeColumn FROM B)


texts: Last few rows from a non-indexed table in Oracle

texts:
I'm trying to run a query on an Oracle DB through SQL Server [dont' ask, it's a requirement]
The Oracle table in question [lets call it hugeTable] has over 200 million rows and no index. I'm not allowed to alter the structure in any way.
I need to fetch some rows from hugeTable for a inner join with another Oracle table from the same DB [not as large, can retrieve data easily].

The data in question [based on the criteria I have], is towards the end and as such takes forever to be pulled. This causes a timeout on the Oracle side of things and it returns no rows.
Running the query directly on Oracle DB returns the data but only if I set the timeout to 0.

My question is:
Is there a way to fetch the rows I need from hugeTable in this current scenario?

My query looks a bit like this:

SELECT
TABLE1.COL1, TABLE1.COL2, TABLE2.COL1, ETC..
FROM TABLE1
INNER JOIN TABLE2
ON [A COUPLE OF COLUMNS]
INNER JOIN hugeTable
ON [A COUPLE OF COLUMNS]
GROUP BY
a bunch of stuff
HAVING
hugeTable.Column1 = 'something'
and TABLE1.COLUMN3 = 'SOMETHING'


texts:
The software I am installing requires SQL Server 2008. I have SQL Server 2008 R2 Express Version. I am having problem in the setup and finally I am arriving on the conclusion that my SQL Express can be the problem. However I am highly skeptical.

I have asked the vendors and they we cannot confirm if their software works with the Express version.

Have you ever run into a problem where a product will work with full version (of Database) and not a free version?

Here is the Limitation of SQL Server 2008 R2


texts: Do not have permission to use 'dbo' when creating a view

texts:
I want to create a user with minimal required access to two databases within a server.  The user will be used by someone from a third party integrating a Business Intelligence tool into our database.   They have specified that they will only need to create views.

So I created a new user on the server,  then used the database properties on each database to grant them the SELECT and CREATE VIEW privileges on that database.

I then logged in on SSMS using that user, and tried to create a view but I got a message saying that

  'dbo' either does not exist , or I do not have permission to use it.

I tried to figure this problem out myself by trial and error but I just don't understand user permissions on sql server.   What am I doing wrong and what extra/different steps do I need to take?

Remember, I don't want them to have more control than the minimum they need.


texts: Where should I look for details about 'access denied' errors?

texts:
This question derives from Deploying a new database on a new server, an msbuild ssdt rookie issue.

Since I have a permission denied error, I expect to find which user is failing to access so I can grant it.

Is there a place in the Event Viewer (Windows 7) where I can look for it?


texts: Is it possible to get a status from a concluded operation?

texts:
In MongoDB, you have the command "db.currentOp()", that gives you statistics about the current operation which is running on the database.

Here is the output of the command:

> db.currentOp()
{
    "inprog" : [
        {
            "desc" : "conn1",
            "threadId" : "140534427899648",
            "connectionId" : 1,
            "client" : "127.0.0.1:37394",
            "appName" : "MongoDB Shell",
            "active" : true,
            "opid" : 10120364,
            "secs_running" : 0,
            "microsecs_running" : NumberLong(34),
            "op" : "command",
            "ns" : "admin.$cmd",
            "query" : {
                "currentOp" : 1
            },
            "numYields" : 0,
            "locks" : {

            },
            "waitingForLock" : false,
            "lockStats" : {

            }
        }
    ],

    "ok" : 1

}

Since that there is no operation running on MongoDB, the command gives a status about itself, and among the keys above, there is the key "opid" which stands for Operation ID (obviously) and a number: 10120364

Is it possible to obtain data from a previous operation 10120363 in MongoDB?


texts: How to count number of rows inside Oracle dump file?

texts:
I have a huge (38Gb after bzip2) Oracle EXP dump file. It contains one table.
Such big dumps usually end up with an error, but in fact contain the full table.

Earlier I was importing these tables back into DB to check that they are ok.
But it takes significant amount of time and resources.

Easy way to check that the file is fine - is to see how many rows in it. To compare with database (this table for example has 4 464 761 311 rows).
But because export ended with an error, EXP did not show how many rows it dumped.

So how is it possible to see how many rows inside dump file without importing it?

Export was done with such parameters: exp direct=y recordlength=65535 and there are no visible sql commands in it, just the data.


texts: Create a read-write role for a specific schema with read-only for all other schemas" on Postgres 9.6 on RDS

texts:
I am planning to implement 3 types of roles on PostgreSQL 9.6.  We do not need users in our case.
Below are the 3 roles I am trying to implement:

Full Admin access role
READ-WRITE to ignore schema and SELECT on all schemas/tables (existing or future created tables, with select nextval capability).
READ-ONLY role on all schemas/tables, select nextval on seqs.

1 and 3 worked fine.  However, I am unable to create a role that can have all access on a specific schema but read-only on all other schemas.  I've tried many answers from Stack Overflow, but nothing has helped so far.
This is what I've tried:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

DO
$do$
BEGIN

IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'rds_dba') THEN
    CREATE ROLE rds_dba WITH LOGIN PASSWORD 'XXXXXX' ;
END IF;

END;
$do$
;

COMMIT;

GRANT CONNECT ON DATABASE test TO rds_dba;

GRANT USAGE on SCHEMA public TO rds_dba;
GRANT USAGE on SCHEMA reporting TO rds_dba;
GRANT USAGE,CREATE on SCHEMA ignore TO rds_dba;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO rds_dba;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO rds_dba;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO rds_dba;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA reporting TO rds_dba;

GRANT ALL on ALL TABLES in SCHEMA ignore to rds_dba;
GRANT SELECT ON ALL TABLES IN SCHEMA ignore TO rds_dba ;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA ignore TO rds_dba ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA ignore TO rds_dba ;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO rds_dba;
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting GRANT SELECT ON TABLES TO rds_dba;

ALTER DEFAULT PRIVILEGES IN SCHEMA ignore GRANT ALL ON TABLES TO rds_dba;

ALTER DEFAULT PRIVILEGES IN SCHEMA ignore GRANT REFERENCES,UPDATE, INSERT, SELECT, DELETE, TRIGGER ON TABLES TO rds_dba;
ALTER DEFAULT PRIVILEGES IN SCHEMA ignore GRANT SELECT ON TABLES TO rds_dba;

grant rds_iam to rds_dba;


texts: How to sum of column by depending other column in my sql?

texts:
how to calculate / SUM of column by the latest data of updateid column ?

I try this :

SELECT * FROM yourTable;

SELECT
ANY_VALUE(code) AS code,
COUNT(*) AS total_rows,
SUM(data->'$.chart."21"') AS total_a
FROM yourTable
GROUP BY code
ORDER BY code;

From above query it sums all rows, it should be sum the latest update id

anyone can solve this problem ?

here is the fiddle : https://www.db-fiddle.com/f/qHnaHNQtEhDasLiSB4Jhv5/0


texts:
I'm trying to debug a MS SQL Server error 242:

  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The error is originated by this statement:

CREATE TABLE db.schema.test (
    Column1 datetime NULL
) GO

INSERT INTO db.schema.test (Column1)
VALUES (convert(varchar,convert(datetime,{D '2019-06-30'}),102));

This is my server version:

Microsoft SQL Server 2017 (RTM-CU15) (KB4498951) - 14.0.3162.1 (X64)
    May 15 2019 19:14:30
    Copyright (C) 2017 Microsoft Corporation
    Standard Edition (64-bit) on Linux (Debian GNU/Linux 9 (stretch))

And the language @@LANGUAGE is Italiano.

The same statements works on

Microsoft SQL Server 2017 (RTM-CU10) (KB4342123) - 14.0.3037.1 (X64)
   Jul 27 2018 09:40:27
   Copyright (C) 2017 Microsoft Corporation
   Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

With language: us_english

Without without making explicit convert style it works

INSERT INTO db.schema.test (Column1)
VALUES (convert(varchar,convert(datetime,{D '2019-06-30'})));

Convert style 102 is ANSI yyyy.mm.dd, shouldn't be recognized by both English and Italian SQL Server?


texts: How to optimize a query with select, row number and a lot of LIKE

texts:
I try to find search rank of a product in today's product history records. For this I use 6 ILIKE and 2 inner join. I have indexed all columns but still so slow. It takes approximately 10 sec. to execute.   How can I increase the speed.  I'm currently using postgresql 10. Any advice would be grateful. SqlFiddle and sql is below.

http://sqlfiddle.com/#!17/718b5/1

select
   search_rank
from
   (
      select
         row_number() OVER (
      order by
         marketplace_rank) as search_rank,
         cp.cr_product_id as cr_product_id
      from
         cr_products cp
         INNER JOIN
            cr_product_histories cph
            on cph.cr_product_id = cp.cr_product_id
            and cph.created_at > now()::date
         INNER JOIN
            cr_product_names cpn
            on cpn.cr_product_name_id = cph.cr_product_name_id
      where
         cpn.cr_product_name ILIKE '%dsa%'
         and cpn.cr_product_name ILIKE '%ghfg%'
         and cpn.cr_product_name ILIKE '%gfhgf%'
         and cpn.cr_product_name ILIKE '%terttre%'
         and cpn.cr_product_name ILIKE '%trter%'
         and cpn.cr_product_name ILIKE '%sfjkkjh%'
      order by
         marketplace_rank asc
   )
   xyz
where
   cr_product_id = 1 limit 1


texts: Is it possible to deploy Microsoft PDW on Azure?

texts:
I've read about it and want to play around with it for a bit.

I think it requires a minimum of 4-5 machines to run, which is feasible with Azure IaaS, but how do I actually deploy it there?


texts:
I have the following sample table.

drop table if exists fruit;

create table fruit
    (
    id int not null auto_increment primary key,
    tag varchar(100) not null
    );

insert into fruit (tag) values
('apples, oranges, apples'),('apples, pears, pears'),('figs, oranges, figs, oranges');

So the table looks like

> select tag from fruit;
+--------------------------------+
| tag                            |
+--------------------------------+
| apples, oranges, apples        |
| apples, pears, pears           |
| figs, oranges, figs, oranges   |
+-----------------+
3 rows in set (0.00 sec)

How can I make the rows have unique fruits? For example, I would like to have the following resulting table.

        tag

| apples, oranges |
| apples, pears   |
| figs, oranges   |

Any suggestions would be greatly appreciated.


texts: Preserving tablespace and databases from previous install?

texts:
I goofed and destroyed my postgresql 9.2/postgis 2.1 installation under freebsd 9.2 by trying to install mapnik which, for my inconvenience, overwrote it with 9.0.

I have, on another spindle, a tablespace and 2 databases declared.  One of the databases is filled with openstreetmap data that took me 15 hours to create, so I'd rather preserve it when I reinstall 9.2, if I can.  Is that possible?  Under mariadb/mysql it would be trivially easy, but the pgsql docs don't seem to have much to say about the problem (that I could find).

Many thanks in advance for any appropriate advice.


texts:
I need to periodically import significant number of rows from a CSV file. I'd like to be able to open up the least amount of access to the user (automation script) for this job.

Is it possible to limit LOAD DATA LOCAL to a specific table and user? If not, I'm thinking of creating a "staging database", load-data-local into it then INSERT-SELECT across two databases under the same MySQL instance.

Any other suggestions?


texts:
I have a domains table and each domain belongs to a contact. Additionally you can assign 3 extra contacts with different roles admin, tech and billing ,those are not mandatory.

My first thought was to add three extra fields in my domains table.

but then I thought it was better to create an intersection table.

Any suggestions?


texts: How to select status variables into temp variables in mysql

texts:
I have selected global variables into temporary local variables like below.

mysql> select @@long_query_time into @a;
Query OK, 1 row affected (0.00 sec)

mysql> select @a;

+------+
| @a   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

But when i am trying to do the same for status variables like below , i am getting error.

mysql> select @@qcache_hits;
ERROR 1193 (HY000): Unknown system variable 'qcache_hits'

I know we can select status variables into temp variables using information_schema tables like below.

SELECT @a := variable_value from information_schema.global_status where variable_name='com_select';

But I want to know if there is any way to select server status variables into temp variables without using information_schema tables.

Thanks in advance.


texts: How can I improve performance of the stored procedure?

texts:
Stored Procedure

CREATE     procedure [dbo].[ImproveProcedure] (@port varchar(50), @portdate datetime)
as

Declare @intdate datetime
select @intdate = max(rate_date) from Interestrate where rate_type = 'Zero'
and rate_date <= @portdate

Update transactiontable set NonDiscount = null, Discount = null, NonDiscountTcurr = null, DiscountTcurr = null,
NonDiscountNew = null, DiscountNew = null
where
port = @port and portdate = @portdate

Update tr set NonDiscount = (case sss_ind when 'P' then -exposure else exposure end) *
      dbo.Foo(ccd, 'USD', @portdate,
case when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) < 1 then 1
 when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) > 48 then 48
 else  datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature)))
end),
NonDiscountTcurr = (case sss_ind when 'P' then -exposure else exposure end)
from
Phy p1
where
port = @port and portdate = @portdate
and
tr.trans = p1.trans
and
p1.Sub <> 'Option'

Update tr set NonDiscount = (case when buysell in ('A', 'S') then -vol * markprice else vol * markprice end) *
      dbo.Foo(ccd, 'USD', @portdate,
case when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) < 1 then 1
 when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) > 48 then 48
 else  datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature)))
end),
NonDiscountTcurr = (case when buysell in ('A', 'S') then -vol * markprice else vol * markprice end)
from
Phy p1
where
port = @port and portfolio = @portfolio
and
tr.trans = p1.trans
and
p1.Sub = 'Option'

CREATE   function [dbo].[Foo]
(@currency1 varchar(10),
@currency2 varchar(10),
@portdate datetime, @month int) returns float

As
BEGIN
Declare
@CurrentRate float,
@Ratedate datetime

select @Ratedate = max(rate_date) from fx where
(
currency1 = @currency1 and currency2 = @currency2 and forward_month = @month
or
currency2 = @currency1 and currency1 = @currency2 and forward_month = @month
)
and
@portdate >= Rate_date

IF exists ( select * from fx where currency1 = @currency1
and currency2 = @currency2  and rate_date = @Ratedate and forward_month = @month)

  SELECT @CurrentRate =  Rate from fx where currency1 = @currency1 and
  currency2 = @currency2 and rate_date = @Ratedate and forward_month = @month

ELSE

  IF exists ( select * from fx where currency1 =
  @currency2 and currency2 = @currency1 and rate_date = @Ratedate and forward_month = @month)

  select @CurrentRate = 1/Rate  from fx  where currency1 = @currency2
  and currency2 = @currency1 and rate_date = @Ratedate and forward_month = @month

  ELSE
   select @CurrentRate = 1

return (@CurrRate )
END

I understand that scalar function calls can limit the performance of a query. Can someone see a way to remove the function calls, replacing them with a JOIN or CROSS APPLY or something instead, to see if this helps performance? I've tried, but haven't been able to find a way.

More info about current performance:  A single run of the procedure, updating ~25,000 rows, takes 20-25 minutes to run. Looking at the execution plan, I see a lot of reads tied up in the function, and I'm thinking that with a JOIN solution (or some such), that number of reads would drop dramatically, and the performance may improve. Plus, as noted above, I've heard scalar function calls are (in general) "bad" in queries.


texts: What Triggers sp_recompile in database without triggers, stored procedures, etc?

texts:
In a database with only tables and indexes (ie no stored procs, functions, triggers, etc). We get a call to sp_recompile that blocks table selects / updates randomly (ie once a month but in a highly transactional system. Catching this via trace has been a challenge. The local DBA seems to only capture the statement in a blocking lock tree. It is my understanding that SQL Server uses some other method (ie. not a direct call to sp_recompile) to trigger the internal recompiles when needed for plan cache / object updates / aging.

I see from books online that you can call sp_recompile with a table object, which triggers a cascading flag change to recompile any objects related the next time it executes, but this seems to be directed toward other DB objects, not tables and indexes. So does anyone know if SQL Server calls / triggers sp_recompile directly?


texts: Ola Hallengren Index maintenance on servers with Availability Groups

texts:
I'm configuring the Ola Hallengren scripts for Index maintenance on all servers in may Availability Group cluster which has several AG's and databases which are not in AG's.

If I select the "ALL_DATABASES" option, is the script intelligent enough to exclude the read only secondary replicas?


texts:
As the title suggests I need some help getting a running total in T-SQL. The problem is that the sum I need to do is the sum of a count:

sum(count (distinct (customers)))

Say if I ran the count alone, the result would be:

Day | CountCustomers
----------------------
5/1  |      1
5/2  |      0
5/3  |      5

I need output with the sum to be :

Day | RunningTotalCustomers
----------------------
5/1  |      1
5/2  |      1
5/3  |      6

I've done running totals before using the coalesce method, but never with a count. I'm not sure how to do it now that I have the count.


texts: As far as I know, DOMAINS cannot be created in MySQL. Is there any disadvantage for that? Is there something to be used instead of using CREATE DOMAIN?


texts: sp_BlitzCache Fails When writing to Table

texts:
I'm attempting to run sp_blitzcache version 5.8  on sql server 2012 SP3 with following parameters:

EXEC sp_BlitzCache @SortOrder = 'CPU',
                   @Top = 10,
                   @ExpertMode = 1,
                   @OutputDatabaseName = 'Admin',
           @OutputSchemaName = 'dbo',
           @OutputTableName = 'BlitzCache_History'

This is throwing an error when inserting to table output:

Msg 121, Level 15, State 1, Line 1
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

Has anyone seen this behavior before?


texts: Get another table row on the basis of first table

texts:
Scenario,

I have three table like ,
Table - 1

table_1_id    status
   1          pending
   2          progress
   3

Table - 2

  table_2_id   status     table_1_id
     1         pending      1
     2         progress     1
     3         pending      2
     4         completed    3

Table -3

   table_3_id  status      table_2_id
        1      pending        1
        2      pending        2
        3      pending        1
        4      progress       2
        5      completed      3
        6      pending        4

All the table in one to many relation

I want to write query which cover following scenario

   1. Run the select statement on table_1 , with 'pending' status and given primary key of table_1.

     1.1 If row any found - then run the second select on table_2, with 'pending' status and the table_1 primary key (which is foreign key in table_2)

       1.1.1 If any row found - then run the third select on table_3 ,
           with pending status and table_2 primary key (which is foreign key in
           table_3), then return the primary key means table_3_id.

       1.1.2 If no row found- then return the primary key of table 2 means table_id_2.

     1.2 If no row found - then return the primary key of first table means table_1_id.

 2. If no row found - then return null


texts: Difference between SID and Database name

texts:
all.

I've searched this topic in Google and there are several information, but it makes me confused more..

At first, I thought SID is the unique key for "database" not "instance", and must be matched with "DB_NAME".

However, In a post, it says SID doesn't need to match DB NAME, global name, unique name, and instance name.. with some practical configuration. In another post, it says SID is combination of database name + instance number..

In my short knowledge,

Instance is server ORACLE processes are running, which contains SGA.

Database is just storage that user applications' data is stored. When instance runs, it access to DB, mount the control files, open the data files and redo logs.

If the above comment is right, I think SID must be Database, and.. could be DB_NAME..

Now I'm confusing Database, Instance, SID, and DB_NAME.

Thank you for reading..


texts:
We're running sql server on vmware. Our san is pretty new but still I think I/O is pretty crappy even though I'm supposed to be on separate spindels. Server speeds are:

My client machine:

Is our san slow? Can I get some comparative values from other users? If so please let me know if you're on a vm or a physical machine. It looks like it's doing pretty ok writing but reading is really terrible...


texts: Converting old SQL Server outer join syntax

texts:
I'm a Power Builder (PB) developer. I've migrated PB from 10 .5 to 12.5, and now I need to migrate the stored procedures from SQL Server 2005 to 2012 as well.

I get a invalid expression error when I run the procedures in Power Builder. We have previously been using a lower database compatibility model in 2005, which allowed the procedures to work there. I have learned that =* is the old way to write right outer joins.

For example:

select  *
from    A
right outer join
        B
on      A.bid = B.id

...is written in the old style like:

select  *
from    A,
        B
where   A.bid =* B.id

Here is a part of my procedure that uses *=:

SELECT  DISTINCT
   a_stmt.cyc_dte,
   a_carr.carr_nm,
   a_stmt.amt_du_ic
 FROM  a_stmt,
   s_dataccs,
   a_carr,
   a_icc
 WHERE ( a_stmt.ic_cntct_id *= a_icc.ic_cntct_id ) and  -- HERE
   ( a_icc.usg_ind = 'S' ) and
   ( a_carr.acna = a_stmt.acna ) and
   ( s_dataccs.acna = a_stmt.acna ) and ( s_dataccs.user_id = @user_id ) and
   ( ( a_stmt.acna = @acna ) or ( @acna = '' ) ) AND
   ( ( a_stmt.juris_id = @juris_id ) or ( @juris_id = '' ) ) AND
   ( a_stmt.jrnl_mo_yr = @jrnl_mo_yr ) AND
   ( a_stmt.amt_du_ic < 0 )

I have converted that to:

SELECT  DISTINCT
   a_stmt.cyc_dte,
   a_carr.carr_nm,
   a_stmt.amt_du_ic
from
 a_stmt left outer join a_icc on a_stmt.ic_cntct_id = a_icc.ic_cntct_id
 join  a_carr on a_stmt.acna = a_carr.acna join s_dataccs on a_stmt.acna = s_dataccs.acna and
   ( a_icc.usg_ind = 'S' ) and
   ( s_dataccs.user_id = @user_id ) and
   ( ( a_stmt.acna = @acna ) or ( @acna = '' ) ) AND
   ( ( a_stmt.juris_id = @juris_id ) or ( @juris_id = '' ) ) AND
   ( a_stmt.jrnl_mo_yr = @jrnl_mo_yr ) AND
   ( a_stmt.amt_du_ic < 0 )

Is my approach correct, or do I need to add a WHERE condition to it? I don't have access to the production database to check. The development database is in SQL Server 2012 too, sO I will not be able to run the old version there to check. It would be really kind, if someone could help me out with this!


texts: Want to create an SQL function that removes table row duplicates

texts:
I'd be following the procedure outlined here (unless of course someone has a better way to do it), and I'm wondering if I could just have some help being pointed in the right direction on how to start.

Basically I need help first on HOW to create functions, and general tips on making it adjustable for varying number of columns etc. This may be a very complicated task, as I have no previous experience making SQL functions, so please let me know if this is a difficult task for an SQL noobie working with MS SQL 2005.


texts: Tiered permissions to generate conditional data access

texts:
I am working to improve the confidentiality and access level of the information presented within a program interface over top of a MySQL database. My goal is to create a two tier system: one based on access-level, and the other based on physical location.

For instance, an engineer will have greater complexity and more access than someone within HR. While at the same time users in China will be able to review information specific to them, and will not be able to see information for Australia. I thought about creating a permissions table, with all the possible combinations stored in it, and each unique combination would have a number associated with it, but I would like administrators to have the ability to add new permissions at will and in a manner that is fairly painless. I have also toyed with the idea of a single character for company and another for group-level, but this would give a limit of 62 companies (assuming for simplicity case-sensitive alphanumeric values) and 62 regions. Is there an obvious solution that I'm just not seeing?


texts: How can Azure PostgreSQL authenticate against Azure AD with username & password instead of a token?

texts: I have used the documentation at https://docs.microsoft.com/en-us/azure/postgresql/howto-configure-sign-in-aad-authentication to enable users in Azure AD to authenticate to an Azure PostgreSQL instance using a token retrieved using their password. I would like for users to be able to authenticate using just their username and password and not have to go through getting a token. This would allow for non-technical users to access a PostgreSQL database using standard SQL client tooling. Is there some plugin or other mechanism I can use with Azure PostgreSQL to make this possible?


texts: Alternative to sequence and timestamp: uniquely ordering records in time

texts:
Oracle 11gR2 Exadata

I'm required to uniquely identify when records are created in time. Sequence caching means I cannot use a sequence-based ID and batching inserts means that all records inserted in one batch will have the same timestamp value (even using TIMESTAMP(9)).  Akin to Twitter's since_id concept.

The best alternatives I've ideated so far

creating an additional sequence used for each unique timestamp
not batching inserts of the records to force a unique timestamp for each
record
not caching the sequence, although there has been some
discussion that this won't solve the problem under Exadata

Here's my requirement: I have an API that allows users to supply a sequence as a marker and request all records since that time. For example, they request 1000 records with a marker of 7 and they'll get 1000 records from my table with an ID greater than 1007. As an example let's say the numerically greatest ID of the returned 1000 records is 2045 so we return 2045 as the marker Later the clients request 1000 records with a marker of 2045 expecting to get the next batch of 1000 and a new marker.

Pretty straightforward way to allow them to get all of the records in whatever size works for them without missing any. However, due to sequence caching across multiple Exadata nodes, at the time the client requests 1000 records with a marker of 1007, a record with an ID of 2020 may not have been created. Therefore, when they do the next request using the marker of 2045, they will have missed record 2020 forever. Using the ID to get the timestamp of the associated record solves this, but then I must make sure to always insert records into the table individually to guarantee unique timestamps.

Assumptions:

Not a way to get separate timestamps for individual records on a bulk/batch insert
Multiple nodes may cause insertion of records to be non-time-sequential even with NOCACHE on the sequence (e.g., a record with sequence value 180 could be written with a systimestamp greater than that of a record with sequence value 179)

Hopefully I just haven't hit on the correct terms to search for existing answers. I feel that this is a problem that should have been solved by some pattern(s) for years.  I think Twitter has solved it...


texts: Export entire SQL Server Database into platform independent data files

texts:
On a quarterly basis I need to provide our clients a copy of all their data.  We need to provide this data in a database independent format (in other words I cannot simply send them a backup file).  The typical client has roughly 1GB of data.  This includes BLOB fields with files up to 10mb in size.  These files are a mix of Office documents, PDFs, and images.

I’ve looked into both the BCP and the XML query capabilities, and from my understanding both only seem to work at the table level which would mean I would have to write a query per table.  The schema size of this database makes those options prohibitively time consuming, not to mention future maintenance of this process seems costly.

The database independent format can be anything.  XML and scripts (scripts have to support both Oracle and SQL) are less desirable due to the storage size that these typically require.


texts: Postgres Slow Like Query with unicode string

texts:
I have 20 million Record in table its Schema is like below
FieldName   Datatype
id          bigint(Auto Inc,Primarykey)
name        varchar(255)
phone       varchar(255)
deleted_at  timestamp
created_at  timestamp
updated_at  timestamp

It has index on name and phone column
Column  Index type
name    GIN trgm index
phone   btree index, GIN trgm index

Created index using the following commands
CREATE INDEX btree_idx ON contacts USING btree (phone);
CREATE INDEX trgm_idx ON contacts USING GIN (phone gin_trgm_ops);
CREATE INDEX trgm_idx_name ON contacts USING GIN (name gin_trgm_ops);

I am running the below query
 select * from contacts where name like '%خالد.%' limit 15;

I am doing contains query on name. The above query takes more than 80 second to get a result. Let me provide the explain statement of this.
   explain (analyse,BUFFERS) select * from contacts where name like '%ش عمر%' limit 15;

Limit  (cost=1450.71..1506.42 rows=15 width=65) (actual time=200.998..1833.925 rows=15 loops=1)
  Buffers: shared hit=2072 read=4998
  I/O Timings: read=1647.539
  ->  Bitmap Heap Scan on contacts  (cost=1450.71..10416.39 rows=2414 width=65) (actual time=200.996..1833.909 rows=15 loops=1)
        Recheck Cond: ((name)::text ~~ '%ش عمر%'::text)
        Rows Removed by Index Recheck: 7368
        Heap Blocks: exact=5045
        Buffers: shared hit=2072 read=4998
        I/O Timings: read=1647.539
        ->  Bitmap Index Scan on trgm_idx_name  (cost=0.00..1450.11 rows=2414 width=0) (actual time=174.616..174.616 rows=234828 loops=1)
              Index Cond: ((name)::text ~~ '%ش عمر%'::text)
              Buffers: shared hit=1837 read=188
              I/O Timings: read=57.733
Planning Time: 0.121 ms
Execution Time: 1835.259 ms

Here what can I do to optimize my query? and bring the result under 5 sec would be optimal


texts: Restrict Source Access to SQL Server Service Account

texts:
I have an Audit requirement to ensure that the service accounts (that connect to SQL Server) are not used by anyone but the by the APPLICATION SERVER itself.

All other connections attempts must be refused including access via SSMS. I also wanted to avoid doing it via Network such as Proxy, etc.

Can you provide me with some clues?


texts: SQL Server Query Optimizer Time Spent and Numbers

texts:
In Microsoft SQL Server, is it possible to find how much time the query optimizer spent to generate N numbers of execution plans? (Let's say for a specific stored procedure).

Also, is it possible to know how many execution plans the query optimizer generated and which execution plan it has selected for that specific stored procedure?

Or does the query optimizer completely works like a sealed box?

Thanks!


texts: Create new function by code if it doesn't exist

texts:
I want to create new function by script in my database.
The script code is below:

IF Exists(Select * From sys.sysobjects A Where A.name =N'fn_myfunc' and xtype=N'FN') return;

CREATE FUNCTION fn_myfunc ()
returns varchar(10)
AS Begin
...
End

But when I execute the above script, SQL Server returns an error:

'CREATE FUNCTION' must be the first statement in a query batch.


texts: restoring Postgres database: pg_restore -vs- just using psql

texts:
I am dumping my Postgres database using pg_dump (in plain-text format) and then restore it simply by using psql (with the -f option).

Which begs the question: am I missing anything by not using pg_restore which seems like a specialized restore tool (compared to the generic psql)?

I can control options like disabling of triggers and such by using pg_dump parameters. What, then, is pg_restore used for? non-plain-text dump formats ?


texts: MATCH() AGAINST() in boolean mode: order of patterns

texts:
Does the order of the patterns under against affect something?
Here is a table I have:

create table df_test
(
  t1 text,
  t2 text,
  FULLTEXT df_test_idx (t1 ASC, t2 ASC)
);

Here are the rows I have:

t1                    |  t2
--------------------------------------------
1word2 1sec71 5word5  |  word2 1sec8 6word6
word1 sec8 5word5     |  word2 sec7 word6
word1 sec8 5word5     |  word2 1sec17 word6
word1 sec7 5word5     |  word2 1sec8 6word6
word1 sec7 5word5     |  word2 sec8 word6
word1 sec7 5word5     |  word2 sec7 word6
sometrash9            |  sometrash10
sometrash7            |  sometrash8
sometrash5            |  sometrash6
sometrash3            |  sometrash4
sometrash13           |  sometrash14
sometrash11           |  sometrash12
sometrash1            |  sometrash2

The query select * from df_test where match(t1, t2) against('6word*' IN BOOLEAN MODE) returns 2 rows, which is expected:

t1                    | t2
-------------------------------------------
1word2 1sec71 5word5  | word2 1sec8 6word6
word1 sec7 5word5     | word2 1sec8 6word6

The query select * from df_test where match(t1, t2) against('"word1 sec8"' IN BOOLEAN MODE) also returns 2 rows, which is also expected:

t1                    | t2
-------------------------------------------
word1 sec8 5word5     | word2 sec7 word6
word1 sec8 5word5     | word2 1sec17 word6

The query select * from df_test where match(t1, t2) against('"word1 sec8" 6word*' IN BOOLEAN MODE) is expected to merge the results but it returns the same 2 rows as above:

t1                    | t2
-------------------------------------------
word1 sec8 5word5     | word2 sec7 word6
word1 sec8 5word5     | word2 1sec17 word6

Nevertheless, if I change the sequence of patterns, it returns as expected. The query select * from df_test where match(t1, t2) against('6word*"word1 sec8"' IN BOOLEAN MODE). The result:

t1                    | t2
-------------------------------------------
1word2 1sec71 5word5  | word2 1sec8 6word6
word1 sec7 5word5     | word2 1sec8 6word6
word1 sec8 5word5     | word2 sec7 word6
word1 sec8 5word5     | word2 1sec17 word6

Could someone please explain what I am missing?

UPDATE#1:
The table is InnoDB

SQL to fill the table in:

INSERT INTO df_test
VALUES ('1word2 1sec71 5word5', 'word2 1sec8 6word6'),
       ('word1 sec8 5word5', 'word2 sec7 word6'),
       ('word1 sec8 5word5', 'word2 1sec17 word6'),
       ('word1 sec7 5word5', 'word2 1sec8 6word6'),
       ('word1 sec7 5word5', 'word2 sec8 word6'),
       ('word1 sec7 5word5', 'word2 sec7 word6'),
       ('sometrash9', 'sometrash10'),
       ('sometrash7', 'sometrash8'),
       ('sometrash5', 'sometrash6'),
       ('sometrash3', 'sometrash4'),
       ('sometrash13', 'sometrash14'),
       ('sometrash11', 'sometrash12'),
       ('sometrash1', 'sometrash2')

UPDATE#2:
Here is the link to the bug opened with MySQL:
https://bugs.mysql.com/bug.php?id=95434


texts: What permissions are needed to view dependencies?

texts:
My company is moving to a much more restrictive security model in the near future.

As part of that we are changing specific servers to the new model and testing all our existing processes.  One of the processes that I use a lot utilizes the SMO framework to script out database objects in dependency order, so we can run those scripts on another server.

The dependency order piece is very important as we have a lot of nested objects (think views that reference other views).

With the new security model in place, this script stopped working correctly.

I tested in SSMS as well (SSMS 2012 against a 2008r2 instance) and using View Dependencies on a view that references another view in the same DB doesn't show the referenced view under Objects on which [this view] depends.

Even more troubling, if I run sp_depends on the view in question I do get an accurate list of dependencies.

I did a bit of research and couldn't find a definitive answer, so I'm hoping someone can help me out:

What specific permissions are needed for a user to accurately view dependencies in SQL Server 2008r2 and/or SQL Server 2012 (we are upgrading soon).


texts: non cumulative differential backup for SQL Server

texts:
SQL Server natively do not support non-cumulative differential backup type.
I wonder why independent backup software providers like from RedGate, Idera or Quest do not support it either?
It looks easy to implement: just store LSN from last non-cumulative backup and put in the backup files only pages changed from this LSN and could be useful for log shipping databases in simply recovery mode.
Is there any caveat I am missing?


texts: Retrieving the top 5 users with the most views on their posts in total

texts:
I have 3 tables:

users
posts
visits

Each time a post gets visited, a new visit will be inserted in the following format:
| id | post_id

My post table has:
| id | text | user_id ...

I'm trying to retrieve the TOP 5 users with the most views on their posts in total.
For example:

User1 -> 4
User2 -> 2

SELECT count(v.id) AS count FROM visits AS v RIGHT JOIN pastes AS p ON v.post_id = p.id RIGHT JOIN users AS u ON id = p.id

But all I get is an array with "count: 12".


texts: Delete shared memory segments left over by an Oracle instance

texts:
We're using Oracle Enterprise 11rR2 running on Solaris.

How can I delete/remove allocated shared memory segments using ipcrm?

I'm getting this error:

 ORA-01041: internal error. hostdef extension doesn't exist


texts: MongoDB Update specific entries in an array

texts:
{
    "_id" : "abcd",
    "arrayItems" : [
        {
            "name" : "Peter",
            "age" : 23,
            "profession" : "Student"
        },
        {
            "name" : "Paul",
            "age" : 80
            "profession" : "Student"
        },
        {
            "name" : "Mary",
            "age" : 45
            "profession" : "CEO"
        }
    ]
}

Is it possible to update in a single step?
Peter-> "age" : 27 and Paul -> "age" : 23

In single steps I could do something like this

db.students.update({'_id':'abcd', 'arrayItems.name': 'Peter'}, {$set:{'arrayItems.$.age': 27}});


texts: Need advises for a new MySQL architecture

texts:
I need to deploy a MySQL-based application with the following requirements and would love to receive some guidance (any) as to how I could set things up:

Scale of deployment

5 servers with a latency of 200-400ms between them.
Database content/structure

Running InnoDB. 1GB of data split across 50 tables which are categorized as follows:

-user tables: about 200MB across 10 tables which store information on users (e.g. user, user_favorites, user_votes...).
-content tables: about 800MB across 40 tables which store information about everything else (these tables don't have any relationships with the user tables).
Content creation/access

-user tables: each server should have READ-WRITE access to these tables. The frequency of writes depends on users' activity on the website (e.g. people creating account, updating it, voting and/or putting items in favorites...).

-content tables: each server should have READ ONLY access to these tables which would be updated once a week when the webmaster updates the website's content (and on some occasions maybe on a daily basis to perform urgent content updates).

And here is the setup I've come up with so far:

each server would have its own local copy of the database for READ-ONLY access.
WRITE queries for user tables would be forwarded to 1 MASTER server using MySQL splitting techniques.
WRITE queries for content tables would be performed directly on the MASTER server.
The MASTER server would be replicated to the other servers operating as SLAVES.

And here are my questions:

Q1: Do you see anything fundamentally wrong with my setup / can you recommend something better?

Q2: What tool would you use to split READ and WRITE queries?

Q3: WRITE queries for user tables are going to be delayed because of the latency of writing to the MASTER: is there anything that can be done to mitigate that?


texts:
I have created a SQL Server agent job called TestJob which executes an SSIS package called CommunityPackage.dtsx.

I have also configured "SQL Server Logging" to sysssislog table for the SSIS package, however when I execute the job nothing is being logged in the sysssislog table?

Is there anything I can do to configure so that when I execute the SQL Server agent job which in turn executes the SSIS package to capture the log in the sysssislog table?


texts: SQL queries to get the duplicate records based on trim

texts:
I have a table that has got the below set of records.

UNMASK  MASK
123 897609
00123   896509
0000456 5632789
456 32567889
5678    5632789

From the above table I need to select only the below records.

  UNMASK
123
00123
0000456
456

(I.E) Whatever values in UNMASK has any number of '0' before the '1-9' and also the row that doesn't have 0s before that 1-9.

Like 123 and there can be 000000123, 456 and 00456, I need such records(only Prefixed with 0s and the original value) in the output using SQL query in Oracle.

I tried the below query but it doesn't work the way I want.

Select UNMASK,TRIM (LEADING '0' FROM UNMASK) from HSA.TEST_TABLE group by UNMASK having count(TRIM(LEADING '0' FROM UNMASK)) > 1;


texts: Add newest values until current date with the year and month separated as 2 INT columns

texts:
So I have this query to insert data into a table (year and month are when the row was created)

INSERT INTO Table#1 (ID, col2, Year, Month)

SELECT B.ID, B.col2, A.InvoiceYear, A.InvoiceMonth

FROM Table#2 AS A

INNER JOIN Table#3 AS B ON B.ID = A.ID

ORDER BY A.InvoiceYear DESC, A.InvoiceMonth DESC

The thing is that everytime i run that query, it add all the data all over again, and i want only the newest data from the last update.
So the thing i wanted to do is adding:

WHERE A.InvoiceYear >= MAX(table#1.Year)
AND A.InvoiceMonth > MAX(table#1.Month)

The problem with it, first of all, it didnt work (error 1111: Invalid use of group function), and second, the maximum value for month its going to be 12, because i have data from 4 years. Is there a way to add data that the year and month is more recent that the oldest value added?

Thanks for the help, if something wasnt clear please ask me, it's not my native language


texts:
I am attempting to run a dbcc shrinkfile in chunks of 1GB against a database where 95% of the data has been archived and deleted. I'm leaft with a 235GB file where 9GB is data/indexes. I want to shrink this down to 50GB. I know that shrinking database files is bad, it causes fragmentation etc. As part of the data purge/shrink we also have a rebuild idnex script.

When I run my dbcc shrinkfile script against the database on my own workstation (quad core, 12GB RAM, 2 x SATA drives), the shrink takes around 8-10 minutes.

When running the identical code against an identical copy of the database post data purge, in our testing envrionment, (80+ cores, 128GB RAM, SSD SAN), it takes 70 minutes. To note, there is little activity on this server at the time of the shrink file running. It has been run 4 times with identical results.

I then took a different approach, of moving the remaining 9GB to a different filegroup and physical file. Running dbcc shrinkfile on the empty 230GB file to shrink it down to 50GB, on my own workstation takes < 1 minute.

With this same approach, on the testing environment, it again takes 70+ minutes.

I have taken a snapshot of waitstats before and after as per Brent Ozar's script during the 70 minute run on the test environemnt, and the waittypes returned showing nothing to be conerned about. Top 3 rows below:

Second Sample Time Sample Duration in Seconds  wait_type   Wait Time (Seconds) Number of Waits Avg ms Per Wait
2013-05-28 11:24:22.893 3600    WRITELOG    160.8   143066  1.1
2013-05-28 11:24:22.893 3600    CXPACKET    20.9    13915   1.5
2013-05-28 11:24:22.893 3600    PAGELATCH_EX    11.1    443038  0.0

Windows event log shows nothing unusual. I am heading scratching at this point, why it is taking so long on the ninja hardware, compared to my standalone workstation.


texts: How many backup devices is recommended to improve backup performance?

texts:
In order to improve performance of our SQL Server backups we backup to multiple backup files.

On most blog posts about this topic they say to use multiple backup
files but, is there a point where you can have too many files that
instead of improving performance you get a worse performance due to
the big amount of threads waiting?
Is there any general recommendation based on, for example, number of
cores in the server? To be more specific, one of our server shows
BACKUPIO and BACKUPBUFFER as top 2 offenders wait stats.

We're backing up a big DB (several TBs) using 10 backup files, but I noticed server has only 4 cores and 32 GBs of RAM. I have changed the number of backup files to use 4 backup files instead. I'll see how it goes next week in the next backup cycle, but meanwhile I was trying to find any recommendations on how many backup files to use depending on the server specifications.


texts: Failover Cluster Instance work without quorum

texts:
I have a environment with three nodes with installed SQL Server on them. All three nodes are parts of FCI and quorum setting is Node Majority.

In start, first node is role owner, and all three nodes is online. When I intentionally shut down first node, then second node takes a role. But when I check votes, I saw that third node is voting with 0, from some reason.

What I expect is that all environment is shutted down because quorum is not achieved (I have only one node online), but that is not happening. In fact, everything worked as on start.

What am I missing?


texts:
I have a list of 50 columns in a database that are likely to change or be removed within the next few months. I have a task to identify any SQL scripts that reference these tables. Up until now, I have been doing this by eye however, I have come across some huge scripts that will take forever to use this method. I'm also getting a little sick and tired of doing it :)

I would like to be able to search through a script with the 50 table.column names and if there is a hit, I can do further, manual investigation.

Can anyone recommend a product? Freeware would be best as I don't really want to use my own money to pay for the solution.


texts: Large table random select with some peculiarity

texts:
I have a table with 800k rows and I need to select 20 random rows that not are in other 2 tables (tickets already purchased).

I already tried a SQL with NOT IN syntax, but it's still (sometimes 1s in development machine).

Actual SQL:

SELECT id, description
FROM ticket
WHERE id NOT IN ( SELECT id
                  FROM cart_item
                  WHERE DATE(added) = CURDATE() )
  AND id NOT IN ( SELECT id
                  FROM valid_ticket )
  AND id BETWEEN 1 AND 200000
ORDER BY RAND() LIMIT 20

I need some faster. Because grabbing all table and select 20 rand rows is very slow. That function is executed is all pages of a website and I'm thinking how to optimize and cache that query.

Thanks all for support


texts: Changing SQL Server service account passwords enterprise-wide

texts: In the event of a DBA leaving an organization, what options can the surviving team members pursue to change passwords for service accounts portfolio-wide? While using Configuration Manager appears to be the de-facto method, can PowerShell or another scripting or batch language be used to reset them in bulk? I figure if you're changing the login name for the service accounts, Configuration Manager is essential as registry permissions and the like need to be propagated. For just a password change to the existing account however, is a scriptable method OK as long as it's performed during approved maintenance windows for the service restarts?


texts: How can I SELECT an 'entry' that spans adjacent months?

texts:
I have an Event table (used in a calendar application I am working on) that holds the following columns:

EventName
StartDate
EndDate

The events stored in said table get shown one month at a time, and that is working great, except that it does not return any events that span over into an adjacent month. For example, an event that runs from 2/25 - 3/05.

I will only get events for the current month, provided the EndDate is also within the month under consideration.

I know that this has to do with the statements I am writing. This is what I currently have:

  SELECT *
    FROM Events
   WHERE StartDate >= '2/1/2011'
     AND EndDate   <  '3/1/2011'
ORDER BY StartDate

The dates above are dynamic.


texts: Extended Events to find Blocking and Deadlocking

texts:
Came across another great article from Jeremiah Peschka called Finding Blocked Processes and Deadlocks using SQL Server Extended Events and set this up on my local MS SQL 2014 Developer edition and created blocking and deadlocking events and was able to run his provided query which generate XML for both events.

I took the same process up to a production server MS SQL 2012 Enterprise edition and although I can query some captured deadlock events, the report column is blank, contains no XML for any captured event.  Thoughts?

Also, I am currently running the production server with deadlock trace flags
-T1204 & -T1222 and getting deadlocks logged to the SQL log but although this extended event query generated many rows of REPORT TYPE=Deadlock, no corresponding deadlock was written to the SQL Log, at least around the same time, I have many many more at other times in the SQL log, hence my work in this environment.  What am I missing?

If I open the .xel in SSMS I get a column with the XML string and can double click to have it parse/format and display it in a new window. I'm just not sure why the hyperlink doesn't display in the original query.


texts: postgres: index to SELECT strings like '%foo%';

texts:
I have an expensive, periodic PostgreSQL 9.3 query of the form:

SELECT * from mytable where name NOT LIKE '%foo%';

foo is actually a constant here that never changes.  The query is expensive because it requires a table scan.  I expect that only a few rows will actually match the query, so I want to use a partial index to speed up the query.

CREATE INDEX foo_idx ON mytable ((name NOT LIKE '%foo%')) WHERE name NOT LIKE '%foo%';

However, when I do an EXPLAIN on my query, it still is using a sequential scan instead of relying on the new index.  What am I doing wrong?

I have tried several different expressions inside the parenthesized expression clause of the CREATE INDEX statement, but nothing helps:

CREATE INDEX foo_idx ON mytable (name) WHERE name NOT LIKE '%foo%';
CREATE INDEX foo_idx ON mytable ((name LIKE '%foo%')) WHERE name NOT LIKE '%foo%';

I have also tried removing the WHERE clause for the index, and that doesn't help either.


texts:
I am using Oracle 11g R2 on Linux, and I have multiple tables in my database that has got columns that needs to be masked.

I have tried the OEM to mask the data as mentioned in the below hyperlink, it worked fine.
    http://www.oracle.com/webfolder/technetwork/tutorials/obe/em/emgc10205/data_masking/datamask.htm

But I couldn't find any blog that gives manual ways to mask data in oracle, like executing packages, functions and so on via SQLPLUS prompt.

Could someone please suggest me how else can this be achieved


texts: On which filegroup will constraints be created by default when adding them to a table on a filegroup and how should they be separeated?

texts:
We have multiple filegroups, that look something like this:

PRIMARY (default)
app_data
app_index

When I create a table on filegroup app_data

CREATE TABLE Person
(
    [PersonID] INT NOT NULL IDENTITY,
    [Identifier] NVARCHAR(64) NOT NULL,
    [SupervisorID] INT NULL
) ON app_data

and in the current setup (I haven't made this) constraints will get added in 3 different ways

-- Primary keys are added to app_data
ALTER TABLE Person ADD CONSTRAINT PK_Person
    PRIMARY KEY (PersonID) ON app_data

-- Foreign keys are added to "nothing"
ALTER TABLE Person ADD CONSTRAINT FK_Person_PERSON
    FOREIGN KEY (SupervisorID) REFERENCES Person(PersonID)

-- Unique Indexes are added on app_index
-- Sometimes also with constraint notation (but these are the same in background anyway)
CREATE INDEX UX_Person_Identifier
    ON Person(PersonID) ON app_index

On what filegroup will the foreign key be created? app_data or PRIMARY?

Should all constraints be created on app_index? Or should only "custom" indexes be created on this filegroup?


texts: Very Slow MySQL Queries, even with indexes

texts:
I have a relatively large 4-deep relational data setup like this:

client_applications: (potentially 1,000's of records)
   - ...
   - account_id
   - deleted_at

client_application_versions: (potentially 10,000's of records)
   - ...
   - client_application_id
   - deleted_at

cloud_logs: (potentially 1,000,000's of records)
   - ...
   - client_application_version_id
   - deleted_at

logs: (potentially 1,000,000,000's of records)
   - ...
   - cloud_log_id
   - time_stamp
   - deleted_at

I am still in development so the structure and setup is not set in stone, but I think it is setup ok. Using Rails 3.2.11 and InnoDB MySQL. The database is filled with a small (compared to the eventual db size) set of data (logs only has 700,000 rows) I have 4 queries, 3 of which are problematic, to retrieve logs.

Grab first page of logs, ordered by timestamp, limited by account_id, client_application_id, client_application_version_id (Over 100 seconds)
Grab first page of logs, ordered by timestamp, limited by account_id, client_application_id (Over 100 seconds)
Grab first page of logs, ordered by timestamp, limited by account_id (Over 100 seconds)
Grab first page of logs, ordered by timestamp (~2 seconds)

Here are the EXPLAIN statements. I have indexes on all applicable fields already. Would it be better to duplicate the various ...id fields on the logs table to prevent joins? Or is there some magic sauce I am missing when making these queries? I have never dealt with this quantity of data before so perhaps my standard way of approaching the setup and queries just doesn't scale? How can I alter my setup or statements to make these queries return in a reasonable time?

UPDATE
I have added a few combined indexes on the Logs table and shaved a tiny bit off of the time. Here are the explains for that. I have concluded that the ORDER method is the source of the delay. Removing the order by timestamp desc causes the query to return in a second or so. So the new question is why, when indexed on timestamp, does it still take over a minute to run this query?

UPDATE 2
Using a subquery to fetch the 100 id's increased performance to 14 seconds, but that is still far too long. The optimizations I have tried so far have all shortened the time a bit, but I feel that they are not getting at the root of the problem. Here is the EXPLAIN for the subquery approach.


texts:
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1' : file not found.
pugazhl@pugazhl-LIFEBOOK-LH532:~$ # sqlcmd -S localhost -U SA
pugazhl@pugazhl-LIFEBOOK-LH532:~$ # sqlcmd -S localhost -U SA
pugazhl@pugazhl-LIFEBOOK-LH532:~$  sqlcmd -S localhost -U SA
Password:
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1' : file not found.


texts: Large table load slow mysql php high resources usage

texts:
in this situation i want to tell you guy's about one weird think.
Maybe its already answered for this but seriously i can't find answer.
I have table who have 130k records. 3 columns (id, product_id, picture_url).
When im creating SELECT picture_url FROM table WHERE id='1', server cpu,ram and ssd is going to 100% but when im selecting all SELECT * FROM table; Its loads fast and without any resourses usage. Where could be a problem for this weird situation?


texts: Query that fetchs average values over timestamp range of time, on a certain value of another pair of fields

texts:
This is the situation, over the time our table reached around 800 millions rows. And it is going to get bigger and bigger. It is a service that collects metrics of energy (voltage, power, current, etc) from devices in factories and warehouses. Each row has:

a timestamp value, which is a BIGINT
a deviceid, which is a varchar (I know, this is not good)
a metricid, which is a varchar too
a recvalue, which is a float (this is what we want)

Other fields not important, that need to be removed.

The table is so big that performing a query with dates in a range of one month, retrieves more than 900000 records in the result set.

select recvalue
from devicesmetrics
where deviceid = 'adpackca2_t_axm'
  and metricid = 'EP_TOTAL_kWh'
  and timestamp between 1532571651000 and 1537509599000;

Of course that takes a lot! Several minutes. Even though we already created an index like this:

create index dm_deviceid_metricid_timestamp
    on devicesmetrics (deviceid, metricid, timestamp)

And also, the explain plan says that the query is using it.

We expected that. The solution we found was to retrieve averages of recvalue, retrieving only a certain number of points over the time. So, for example, for a month, we decided to use a query like this:

select max("timestamp") as time_metric, metricid, deviceid, avg(cast(recvalue as float)), avg(cast(calcvalue as float))
from devicesmetrics where deviceid = 'adpackca2_t_axm'
and metricid = 'EP_TOTAL_kWh'
and "timestamp" between 1532571651000 and 1537509599000
group by deviceid, metricid, "timestamp"/((1537509599000-1532571651000)/1000)

This is still taking a lot, unfortunately, around 2 minutes. Even though the number of records retrieve is lower than 500.

Suggestions on how to get a better solutions for this?

It is a standard-4 Heroku-PostgreSQL instance (~30GB RAM and 500 connections).
As another detail, I can show how the explain (analize, buffers) looks like, for that last query:

GroupAggregate  (cost=75179.55..75561.41 rows=38182 width=62) (actual time=108265.296..108445.322 rows=802 loops=1)
  Group Key: deviceid, metricid, (("timestamp" / '4937948'::bigint))
  Buffers: shared hit=8765 read=228254
  I/O Timings: read=107118.279
  ->  Sort  (cost=75179.55..75198.64 rows=38189 width=58) (actual time=108265.172..108277.890 rows=237853 loops=1)
        Sort Key: (("timestamp" / '4937948'::bigint))
        Sort Method: quicksort  Memory: 39593kB
        Buffers: shared hit=8765 read=228254
        I/O Timings: read=107118.279
        ->  Index Scan using dm_deviceid_metricid_timestamp on devicesmetrics  (cost=0.14..74598.28 rows=38189 width=58) (actual time=2.677..108082.026 rows=237853 loops=1)
              Index Cond: (((deviceid)::text = 'adpackca2_t_axm'::text) AND ((metricid)::text = 'EP_TOTAL_kWh'::text) AND ("timestamp" >= '1532571651000'::bigint) AND ("timestamp" <= '1537509599000'::bigint))
              Buffers: shared hit=8765 read=228254
              I/O Timings: read=107118.279
Planning time: 0.220 ms
Execution time: 108448.051 ms


texts:
I tried to recover from a sudden shutdown of mongo, but I am experiencing some problems. Here is the log file

***** SERVER RESTARTED *****
Thu Apr 25 01:09:17 [initandlisten] MongoDB starting : pid=19996 port=27017 dbpath=/var/lib/mongodb 64-bit host=Vault
Thu Apr 25 01:09:17 [initandlisten] db version v2.2.3, pdfile version 4.5
Thu Apr 25 01:09:17 [initandlisten] git version: f570771a5d8a3846eb7586eaffcf4c2f4a96bf08
Thu Apr 25 01:09:17 [initandlisten] build info: Linux ip-10-2-29-40 2.6.21.7-2.ec2.v1.2.fc8xen #1 SMP Fri Nov 20 17:48:28 EST 2009 x86_64 BOOST_LIB_VERSION=1_49
Thu Apr 25 01:09:17 [initandlisten] options: { config: "/etc/mongodb.conf", dbpath: "/var/lib/mongodb", logappend: "true", logpath: "/var/log/mongodb/mongodb.log", nohttpinterface: "true" }
Thu Apr 25 01:09:17 [initandlisten] journal dir=/var/lib/mongodb/journal
Thu Apr 25 01:09:17 [initandlisten] recover begin
Thu Apr 25 01:09:17 [initandlisten] recover lsn: 3778163987
Thu Apr 25 01:09:17 [initandlisten] recover /var/lib/mongodb/journal/j._0
Thu Apr 25 01:09:17 [initandlisten] dbexception during recovery: 13537 journal file header invalid. This could indicate corruption in a journal file, or perhaps a crash where sectors in file header were in flight written out of order at time of crash (unlikely but possible).
Thu Apr 25 01:09:17 [initandlisten] exception in initAndListen: 13537 journal file header invalid. This could indicate corruption in a journal file, or perhaps a crash where sectors in file header were in flight written out of order at time of crash (unlikely but possible)., terminating
Thu Apr 25 01:09:17 dbexit:
Thu Apr 25 01:09:17 [initandlisten] shutdown: going to close listening sockets...
Thu Apr 25 01:09:17 [initandlisten] shutdown: going to flush diaglog...
Thu Apr 25 01:09:17 [initandlisten] shutdown: going to close sockets...
Thu Apr 25 01:09:17 [initandlisten] shutdown: waiting for fs preallocator...
Thu Apr 25 01:09:17 [initandlisten] shutdown: lock for final commit...
Thu Apr 25 01:09:17 [initandlisten] shutdown: final commit...
Thu Apr 25 01:09:17 [initandlisten] shutdown: closing all files...
Thu Apr 25 01:09:17 [initandlisten] closeAllFiles() finished
Thu Apr 25 01:09:17 [initandlisten] shutdown: removing fs lock...
Thu Apr 25 01:09:17 dbexit: really exiting now


texts:
Quick dscription of a problem

first of all I am still learning so please be understanding. Recently I've come across a post where someone was proposing nice schema for storing recurring events. I found it interesting and decided to expand that for learning purposes. But I endded up with a "circular" schema that really freaks me out. Let's look at that through the prism of appointment system (let's say to a doctor).

Presentation of my approach

Company - a private clinic that employs doctors and let patients make appointemnts.

Frame - a range in time when an event occurs. F.e. an appointemnt in a company calendar might be an event, that is represented by a row in the frame table. It's possible to create a repetitive frame that happens f.e. every monday.

Doctor - obviously a person that can be assigned to a given frame (appointment) and company.

A company can have a calendar. A calendar can have "appointments"/"events". Each event occurs within a given time frame. Each such an "event" is held as a "range in time" in a frame table (along with meta info). Thus one
company can have many "appointments"/"events" or many "frame"s.
A frame can be in many companies (many calendars). Because a frame,
is basically just a start_date and an end_date. F.e. frame
2018-01-01 08:00:00 - 2018-01-01 09:00:00 can be defined in
calendar of a "companyA" and a "companyB".
Doctor can be a member (an employee) of many comapnies (clinics). that is obvious. A company (clinic) can have many doctors, that's obvious too.
But an employee of a clinic, can assign a doctor to a frame (make an appointemnt on behalf of a patient). Let's assume that, one frame can have assigned many doctors (f.e. difficult DDX, like in a tv show "Dr. House"). Thus one evetn/appointement/frame can have many doctors.
A dcotor can be in many frames, be assigned to many appointments. That's obvious.

My questions

Is my schema acceptable ?
Is "circular square of tables" acceptable in that case ?

Update:

When company A and company B both are assigned to a frame (2018-03-26 09:00 - 2018-03-26 12:00), that means that company A has an event in the frame, and company B has a completely distinct event in that same frame.

Company A
An appointment between Ms.Alice and Dr.Frank at 5 o'clock (frame: 2018-01-01 05:00:00 PM)

Company B
An appointment between Mr.Bob and Dr.Smith at 5 o'clock (frame: 2018-01-01 05:00:00 PM)

Two different companies, two different calendars, two different events, one (time) frame.

Corrected design (according to review in an answer):


texts:
I'm trying to create insert and update triggers with SELECT GROUP_CONCAT request on one of the fields;

This select query perfectly works, when I trying it in console, but trigger saves only first line of GROUP_CONCAT and ignores concatination at all...
How can I solve this?

UPD trigger code:

CREATE TRIGGER `insert` AFTER INSERT ON `adp_films`
FOR EACH ROW
INSERT INTO adp_films_search (`id`, `code_title`, `title`, `channel`, `cycle`, `annotations`, `author`, `rights`, `tags`)
VALUES(
    NEW.`id`,
    NEW.`code_title`,
    NEW.`title`,
    (SELECT GROUP_CONCAT(distinct(`adp_channels`.`channel`)) as channel FROM `adp_cycles` LEFT JOIN `adp_channels` on `adp_cycles`.`channel` = `adp_channels`.`id` WHERE `adp_cycles`.`id` in (NEW.cycle)),
    (SELECT group_concat(`cycle`) as cycle FROM `adp_cycles` WHERE id in (NEW.cycle)),
    NEW.`annotations`,
    (SELECT `author` FROM `adp_authors` WHERE `adp_authors`.id = NEW.`author`),
    (SELECT concat(`territory`, " ", `license`, " ", `sold_to`) FROM `adp_rights` WHERE `adp_rights`.`id` in (NEW.`rights`)),
    (SELECT GROUP_CONCAT(`tag`) FROM `adp_tags` WHERE `adp_tags`.`id` in (NEW.`tags`))
);