texts: Does specifying STRICT on a PostgreSQL trigger procedure do anything?

texts:
Does specifying CALLED ON NULL INPUT, RETURNS NULL ON NULL INPUT or STRICT (https://www.postgresql.org/docs/current/static/sql-createfunction.html) on a trigger procedure in PostgreSQL do anything?

Since a trigger procedure must be declared as a function with no arguments (https://www.postgresql.org/docs/current/static/plpgsql-trigger.html), I doubt it has any effect but I cannot find a definite answer on this one.


texts:
I want to start mongo on terminal but it gives connection error like:

MongoDB shell version: 2.4.5
connecting to: test
Sat Jan  4 10:18:16.779 JavaScript execution failed: Error: couldn't connect to server 127.0.0.1:27017 at src/mongo/shell/mongo.js:L112
exception: connect failed

I run the following commands:

rm /var/lib/mongodb/mongod.lock
mongod --dbpath /var/data/mongodb --repair
restart the mongodb

But unable to start mongo.
When I run the following command:

mongod --profile=1 --slowms=20 --dbpath /var/data/mongodb --fork --logpath /var/log/mongodb/mongodb.log\

It works fine but log is not created inside logger.

Please help me to fix this issue this is so wierd issue for me.


texts:
Trying to query AD on a linked server error message is 7321 error occurred while preparing the query.  Any suggestions?

Select Name, EmployeeID, Mail, SamAccountName
FROM OPENQUERY(ADSI, 'SELECT Name, EmployeeID, Mail
FROM ''LDAP://DC=mydomain,DC=com''
WHERE  objectClass=''user'' AND EmployeeID > 0 and SamAccountName = ''f.bower''')


texts: How to make SSMS Properties Window coincide with object selected in Object Explorer

texts:
The Properties Window in SSMS seems lame to me. All it shows is the current connection properties. I would like to be able to click on an object in the Object Explorer (e.g. a table or a column) and instantly see the corresponding properties displayed in the Properties Window. Is there some setting in Options that would make this possible?

Now, if I switch to a database diagram and select a box (Table) or field (Column), the Properties Window updates accordingly. But I rarely interact with DB diagrams. I want this functionality while I am working in the Query Designer. Is there anyway to do this?


texts: For a non-binary tree what is the optimal database design?

texts:
For a non-binary tree what is the optimal database design ? There is no limit on the depth of the tree.

I know I can go recursive but it is not optimal because I need to query the database many times.


texts: forcing Oracle to use hash join for a subquery

texts:
I have a query that looks like

SELECT *
FROM table0
WHERE id IN (SELECT id FROM table1 JOIN table2)

Oracle is choosing to join table0 with the result of (table1 x table2) using nested loops and takes hours.  I'm trying to figure out whether I can hint it to use HASH instead, but don't understand which hint and where to use.  I tried sticking HASH_SJ and HASH_AJ in various places and it didn't help...


texts: Restore Database w Master Key and storing password in plain text

texts:
We have a (SQL Agent)job that backs up our production db nightly(and restores to a test db server) and we're adding encryption to some of the columns in our tables.  I created a master key and bound it to the Service Master Key:

Create MASTER KEY Encryption By Password = 'MyReallyStrongPW'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY CLOSE MASTER KEY

I also created my certificate:

CREATE CERTIFICATE [My_Cert] With Subject = 'My Certificate'
CREATE SYMMETRIC KEY My_Symm_Key WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE [MY_Cert];

The same account

  domain\MyBigSqlAccount

is running both instances of sql (prod & test).

The only way to be able to see the data(on test) is to run this:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MyReallyStringPW'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY CLOSE MASTER KEY

and then this would work:

OPEN SYMMETRIC KEY My_Symm_Key DECRYPTION BY CERTIFICATE [My_Cert]

Select Convert(varchar, DecryptByKey(EncryptedColumn)) as DecryptedCol From SomeTable

The only way I've been able to get this to work is to put the password into plain text as a step in the sql job

  step 1:  restore DB, step 2:  use password to bind Master Key to Service Master Key on test server.

Which doesn't seem too wise to store password in plain text(or password to decrypt files which contains the passwords).

Sql Server 2012 for both.

Related (Q1b)Simple Implementation of SQL Server 2008 Encryption

The question is how do I get around storing my passwords in plain text and still use encryption for both prod & text?


texts: A database of all combinations of 39 numbers taken 5 at a time, total combinations = 575757

texts:
I want to create a numerical database of all combinations of 39 integers(1 Through 39) taken 5 at a time. I know the total possible combinations = 575757. This would be the size of the database.

I would then like to query the database for various criteria, e.g. number of combinations that are made up of all even numbers, all odd numbers, 1 even, 1 odd, 2 even, 2 odd, etc,  and then query those results for other criteria such as restricted sums of all integers, and range distribution.

How would I do this? Obviously this is a Lottery oriented idea and I expect nothing but ridicule and disdain.


texts: Remove a period if it is the first character in a varchar column

texts: I am migrating data from an old system into a new system I am building and have found someone is doing a bad practice of adding a period to the start of some people's names to make them appear first in some drop down lists. I am trying to figure out how I can remove these periods when they are the first character of the column with out removing periods later in the name (like for Middle Initials). This change is being made during and INSERT INTO SELECT command.


texts:
Our primary OLTP workload is tempdb heavy (both reads & writes) based on virtual_file_stats DMV, and based on SPs all of which heavily use #tmp tables.
The server has 400GB of memory, more than enough to fit all tables in databases within in.

Tempdb was previously on a local SSD.

We recently bought and installed a Micron PCI-E card, and placed SQL (2016 Ent) tempdb data and log files on it.

However, I did not see a great improvement in performance, for some workloads it was even worse. The vendor specs lists 4K read-write speeds, whereas SQL uses 64KB (right?).

Before I ask the SAN team to run benchmarks on the existing SSD vs new PCI-E disks, would it make sense to format the PCI-E disk as 4KB block size, instead of 64KB.. or does it not make difference for tempdb ?


texts: I need to select the OS user during trigger execution to insert into a table. How can I select the OS User as it may be different from the MySQL user?


texts:
On MySQL 5.0 on CentOS 5.11, MySQL service has crashed and fails to start. Adding innodb_force_recovery=3 in /etc/my.cnf, creates the following error, which repeats with all table names of database DBNAME:

160511 10:10:54  InnoDB: Error: table 'DBNAMEnew/tablename1'
InnoDB: in InnoDB data dictionary has tablespace id ....,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.

Following MySQL troubleshooting, part "Tablespace Does Not Exist", it asks to perform 2 steps:

Create a matching .frm file in some other database directory and copy it to the database directory where the orphan table is located.
Issue DROP TABLE for the original table. That should successfully drop the table and InnoDB should print a warning to the error log that the .ibd file was missing.

/var/lib/mysql/ has DBNAME directory. I've created empty .frm files for all the tables appearing in the error, in DBNAMEnew directory and tried to start the service, which fails, similarly.
Also, since the mysqld wouldn't start, how can I "DROP TABLE" as instructed in the second step?

innodb settnigs in my.cnf

#skip-innodb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2400M
innodb_file_per_table
#innodb_data_home_dir = <directory>
innodb_file_io_threads = 4
innodb_force_recovery=3
#innodb_force_recovery=4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
#innodb_fast_shutdown
innodb_log_buffer_size = 4M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct = 90
#innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 120

I've already backed up all /var/lib/mysql. Do you know how to recover from this crashing state?

Question UPDATE:
I've copied all, .frm files from DBNAME directory to DBNAMEnew. To have matching .frm file for this orphan table. Still getting the following

160512 09:10:52  InnoDB: Error: table 'DBNAMEnew/tablename1'
InnoDB: in InnoDB data dictionary has tablespace id ....,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.

QUESTION UPDATE
I've created a database with the same schema, and copied over the .frm files to the failing database. Now, getting the following errors:

160515 10:54:11  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 3602 935861248
160515 10:55:39  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 160515 10:55:41 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

Since MySQL fails to start, I'm unable to proceed. Is there any way to recover when InnoDB internal data dictionary is in such a state?


texts: How to do Mysql migration from 5.0 (320bit windows) to 5.6 (64 bit windows)?

texts:
I want migrate My Mysql 5.0 windows 32 bit to Mysql 5.6 Windows 64 bit?

Please suggest me the best way to do migration, i have around 10gb of data present in Mysql 5.0.


texts: Why am I seeing poor performance on SQL server 2019 and asynchronous AlwaysOn

texts:
I have recently migrated a 2008 sql dB that’s about 170 GB to 2019 with asynchronous always on.
The application that uses this dB hits it pretty hard and I’m seeing an increase of about 2 ms on every call to the dB, read, inserts and stored procs.( compared to 2008).
This is having an huge impact on application performance.
Same app server, same app.
Same vlan and data centre as before. SQL VMware VM has much better specs.
Gone from 8core 32GB memory. To 16 core 64GB. 55 GB allocated to SQL
We still have 2008 compatibility enabled.
Parallelism is set the same as previous instance.
I’m all out of ideas and looking for suggestions.


texts:
I have used two methods to get around parameter sniffing issues in the past:

1) Use WITH RECOMPILE
 2) Reassign parameter values to local variables and use those instead of the parameters

From what I understand, the end result of both of these is the same -  a new execution plan that is optimized for the current query/params is created and used.

If this is true, are there any differences between these two methods or are they essentially the same?  Is one preferable over the other?


texts: Why does my query search datetime not match?

texts:
select *
from A
where posted_date >= '2015-07-27 00:00:00.000'
  and posted_date  <= '2015-07-27 23:59:59.999'

But the result contains a record that has posted_date today: 2015-07-28.
My database server is not in my country.
What is the problem ?


texts: Does ISOLATION LEVEL READ UNCOMMITTED affect Deletes?

texts:
I have an exceptional circumstance that made me use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.  (I am using sp_getapplock to ensure single process access to a given row.)

I am trying my best to avoid any kind of lock escalation. I know clearly what ISOLATION LEVEL READ UNCOMMITTED does for reads, but I am not sure about deletes.

I saw some example code that was doing a delete using ROWLOCK and READPAST, along with ISOLATION LEVEL READ UNCOMMITTED but I was not clear on what that would do, so I have not been using it for now.

Is that needed to prevent lock escalation of my deletes or is ISOLATION LEVEL READ UNCOMMITTED sufficient?

Note:
In case there is interest, here and here are the relevant sprocs for this question.


texts: Database structure for product attributes on orders

texts:
I'm making a POS and currently kind of stuck on the order proccess due to the product attributes.

As of now the DB looks something like this:

tbl_products

tbl_attributes (attr id, name, extra price)

tbl_sales ( where i store the time, total price etc)

tbl_salelines ( where i store what items have being order for a certain sale).

Problem is storing the attributes for an item order. There isn't a fix amount of attributes one item can have. It could go from 1 to 10-15 therefor i don't think making some columns in the tbl_salelines with attr_1 attr_2 etc etc would solve this.

How should i approach this?


texts: How to find out unused port on Linux server

texts:
We are trying to use port 5444 for our new PostgreSQL instance and we are not sure whether 5444 is used or unused.

Could you please help us to find the answers to the below questions.

We want to find out all the used and unused ports report on PROD Linux servers (RHEL 7.2).
Also find out the application port even the application was shutdown.

Is there any file on Linux servers which holds all the ports (or sockets) info for helping with the above query?


texts: Indexing a SHA hash in place of a VARCHAR

texts:
In Is it a good idea/approach to index a VARCHAR column? the concept was using VARCHAR as the column.

I was wondering, if we store the SHA-1 hash or SHA-256 hash (if I am paranoid) as a BINARY(20) column and index on that column.

Performing a SHA-1 computation on a short string is fast enough on the application side and we just query by the SHA value instead.

Likely I am thinking the length of the VARCHAR would hover around 10 to 30 characters with some going longer but lower probability.


texts: How to replace (SIMILAR TO + regular expression) with LIKE or ~ in PostgreSQL?

texts:
I have the following PostgreSQL function which checks if p_text1 contains the word/phrase p_text2 within it:

CREATE OR REPLACE FUNCTION public."Contains"(
p_text1 character varying,
p_text2 character varying)
RETURNS boolean
LANGUAGE 'plpgsql'

COST 100
IMMUTABLE
AS $BODY$

BEGIN

 perform 1
 where   ( p_text1 similar to '((% )|(%-)|(%\())?'||p_text2||'(( %)|(-%)|(\)%))?' ) or
    ( replace(p_text1,'-',' ') similar to '((% )|(%\())?'||replace(p_text2,'-',' ')||'(( %)|(\)%))?' ) or
    ( replace(p_text1,'-','') similar to '((% )|(%\())?'||replace(p_text2,'-','')||'(( %)|(\)%))?' );

 return found;

END;
$BODY$;

p_text2 is considered a word/phrase if it is preceded/followed by a dash, space, parenthesis or nothing.

Examples:

select public."Contains"('data mining' , 'mining') --> true

select public."Contains"('information retrieval (ir) system' , 'ir') --> true

select public."Contains"('semantic (information retrieval)' , 'semantic information') --> false

select public."Contains"('ontology-based queries' , 'ontology') --> true

select public."Contains"('ontology-based queries' , 'ontology based') --> true

The function will be called like this:

select * from my_table
where public."Contains"( text_column , some_text_variable) = true;

Given that my_table contains about 15,000 rows.

I read many advises to avoid using SIMILAR TO and to replace it with simple LIKE for performance issues... I just don't know how to rewrite such a query with LIKE, and I don't know if that would yield better performance.
Any help is appreciated. Thanks in advance


texts:
I had an SQL Server table that was locked for almost an entire day: nothing was able to insert into it or delete from it.

Eventually, I figured out what was going on by looking at Activity Monitor.
There was a SELECT session from a Microsoft Office client that was blocking all the other sessions from modifying the table. I killed that session, and the inserts immediately began to occur.

I tracked down the person who had made the blocking query, and it turned out not to be the monstrous query that I was expecting: it only returned 935 rows. I have been unable to reproduce this strange locking behavior. I ran the same query using the same client (Microsoft Query) and it returned in less than a second every time.

The query was like

SELECT * FROM MyTable WHERE ID = '09100010090361017'

MyTable has an index on ID, an index on a timestamp column, and indexes on two other columns.

I am thinking the blocking might have occurred because the client left the transaction open. But even if the client had left the transaction open, that would not completely explain this behavior, because it was filtering the data on  ID, which is indexed. This should not have kept new rows from being inserted, or distant records from being deleted (which was the case). Lock escalation must have happened in order for the whole table to be locked - but how? The query only returned 935 rows! Lock escalation isn't supposed to happen until 5,000 transactions have been made. 935 rows + 935 index records to be updated is way
less than 5000.

Does anyone have an idea what was going on? Could it be a bug in Microsoft Query that sometimes fails to commit transactions?

Has anyone seen this before?

======================================================================

EDIT:

As @Erik Darling pointed out, my question might not be answerable as I have put it above. So perhaps a better question would be:
How can I gather more information next time this happens, to aid the troubleshooting process?

@clifton_h mentioned a "plan cache". This issue occurred ~3 weeks ago, so I doubt the plan cache retains any relevant info. But would a good approach be to wait until this problem happens again, query the plan cache to get more info on the blocking query, and go from there?


texts: How can I copy data from one MySQL server to another based on a SELECT statement (then delete the data from the original)?

texts:
I have a very large log table from which I want to copy rows, putting them into the same table structure on a new server.  I don't want to copy everything, only old rows, so that the table on the main server stays small-ish.  So I have to SELECT the data I want and only move (and delete) that.

Keep in mind that there is a lot of data, and I don't want to copy it all with a mysqldump if I don't need to, especially if it's going to lock the table while it's being copied.

The best I've come up with is a PHP script, which I will post as a tentative answer, although I'm certain it's not the best option.


texts: Unquoting JSON strings; print JSON strings without quotes

texts:
SELECT json_array_elements('["one", "two"]'::json)

gives result

| json_array_elements |
| :------------------ |
| "one"               |
| "two"               |

I would like to have the same but without the quotes:

one
two

Looks like I can't use ->> here because I don't have field names in the JSON. It's just an array of strings.

Postgres version:
PostgreSQL 10.0 on x86_64-apple-darwin, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2336.11.00), 64-bit


texts: How to delete and create user remotely on SQL Server 2000

texts:
My boss wants to make sure that no one runs a job accidentally so we are setting up lots of safeguards to ensure that no one sleepwalks, log into the system and run a job. We are updating the subscription at corporate in a merge replication so that it will populate back to the publisher which are spread out across 9 states. That process works great but if it is during business hours, the publisher will override some of the changes that we made to the database.

So to make sure that no one makes any changes to the database, we have decided to delete the user account at the remote locations so that the users can not make changes at 3 am, many, many hours after business hours but some of our managers have been known to come rather early.

I am running into security issues when I try to delete and then after the job is finished, create the user back again.   Using a fully qualified name, it will delete the user but throw an error but won't even think about creating a user.

EXEC [server].databaseName.dbo.sp_revokedbaccess N'<user>'
GO

results in "User has been dropped from current database.

  Msg 0, Level 11, State 0, Line 0
  A severe error occurred on the
  current command.  The results, if any, should be discarded.

but the user really has been dropped.

My create user statement and sp_addrolemember just throws up an error and doesn't run at all.

Any ideas on how I can accomplish this? All of our SQL Server 2008R2 don't have any issues and this is not required but we still have just over 100 SQL Server 2000 machines in the wild that I need to do this to.


texts: mysqldbcompare - ERROR: The list of objects differs among database

texts:
I'm running:

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)
# rpm -q mysql-server
mysql-server-5.1.73-3.el6_5.x86_64
#

and

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)

mysql>

I'm trying to run mysqldbcompare against two mysql server and running into following error:

$ mysqldbcompare -vvv --server1=XXX:XXX@XXX:3306 --server2=XXX:XXX@XXX:3306 pressflow6:pressflow6
# server1 on XXX: ... connected.
# server2 on XXX: ... connected.
# Checking databases pressflow6 on server1 and pressflow6 on server2
#
ERROR: The list of objects differs among database pressflow6 and pressflow6.
$

I just ran mysqldump to export it on server1 and import it to server2 and I'm able to run it against other database without any issues.

Any ideas why I'm unable to run mysqldbcompare successfully?


texts: Full text search in PostgreSQL with search term including unrelated hyphen and ending

texts:
I've a problem in my DB (PGSQL 9.6) that I've not been able to resolve and I don't really even understand what options I might have (if any). Simplified I have a DB of multi-language news articles where I've created full text search index (tsvector) on headlines and text. I need to search through the DB to find articles with a certain game name.

Example of an index that would match what I do:

SELECT to_tsvector('simple', 'Yakumo Shionome featured in new Code Vein-trailer');

This results in the following full text search index:

  'code':6 'features':3 'in':4 'new':5 'shionome':2 'trailer':9 'vein':8
  'vein-trailer':7 'yakumo':1

The challenge come when I want to search for "Code Vein" as game title. In this case I would normally use "phraseto_tsquery". A raw test query could be similar to this:

SELECT to_tsvector('simple','Yakumo Shionome featured in new Code Vein-trailer')
  @@ phraseto_tsquery('simple','Code Vein');

This would result in FALSE in this case. I would have to search for "Code Vein-Trailer" to get a TRUE match. So the "hyphen" followed by "some word" in essence is causing me trouble and I don't really get what options I might have as far as creating a way around this (there can be many different ending words, many I also might not know ahead of time).

Normally the fact that my Boolean searches are word boundary based is good as it avoid potential false positives, but this seems to be a challenging drawback. I use "simple" due to multiple languages and that game names and elements I sometime search for included stop words - which I felt ended up being problematic or more prune to issues if not taking this approach. I also do require exact matches and not ranked results.

Any thoughts and help welcome.


texts:
I have following situation:
One server with Oracle has to be backed up and recovered on other Oracle server.
Second Oracle DB has the same user/schemas, but with a different older data.

I intend to update the data on it by backup from first DB and restoring it on the secondary DB.

I've ran a backup with following rman command:

connect target
run {
allocate channel ch1 device type disk format '\\192.168.1.40\Archive\ARC%u_%c;
backup database ;
release channel ch1;
}

Now I have two files created on a network share.

On the other server I intended to run following command:

RMAN> run {
2>   allocate channel ch1 type disk format '\\192.168.1.40\Archive\ARC%u_%c';
4>   restore database;
5>   recover database;
6>   release channel t1;
7> }

But it fails with error

RMAN-03002: failure of restore command at 02/06/2014 14:24:45
ORA-01180: can not create datafile 1
ORA-01110: data file 1: 'C:\ORACLE\MUR\DATA1\SYSTEM_1\SYSTEM.DATA1'

Can you suggest what should I do in this case? How the restoration is done on Oracle?

Answer Found:

I found the answer.
Backup location should be populated.
I copied backup to a local drive F: and run following command in RMAN:

rman> catalog start with 'F:\backup\';

It has responded with

searching for all files that match the pattern F:\

List of Files Unknown to the Database
=====================================

File Name: F:\backup\ARC0AOVT8H9_1
File Name: F:\backup\ARC0BOVTGN3_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: F:\backup\ARC0AOVT8H9_1
File Name: F:\backup\ARC0BOVTGN3_1

After that I run

RMAN> run {
2>   allocate channel t1 type disk format 'F:\backup\ARC%u_%c';
3>   restore database;
4>   recover database;
5>   release channel t1;
6> }

And recovery started


texts: How do I validate that my purge hasn't deleted the wrong data?

texts:
I've just inherited a 17 year old database which has 481 tables, terrible referential integrity and upwards of 800GB of data at some customer sites.

We have a process which goes through and identifies all parents older than x days, then identifies all the children, grandchildren, great-grandchildren etc. of those parents and deletes from the lowest level back up to the parent. We have a QA process for determining that we are deleting the right quantity of data (using table row counts), but when pressed by the CTO, I couldn't commit to saying that we are sure we are deleting the right data

The purge scripts comprise of over 15,000 lines of SQL spread across 50+ SPROCs, so this isn't something that can be solved by a code review or unit testing.

I'm looking for any advice on processes/practices to utilise as part of our QA to validate that we have not deleted the wrong data. The complexity of our application prevents the answer from being as part of application QA and given there is millions of dollars riding on this data, the margin for error is immeasurably small


texts: Update Local Table With Data From Linked Server Using Open Query

texts:
This is the syntax I am trying to use, but I get an error of

  Incorrect syntax near #

I have also tried it with an actual table and not a temp table and still not succesful.  Here is my syntax, what should I do in order to be able to update a table on my SQL Server 2008 installation with data from a linked connection to postgresql?

Create Table #Test (field1 varchar(100), field2 varchar(100))

DECLARE @salesname nvarchar(500) = N'''Sam Wise''';
DECLARE @d1 nvarchar(100) = N'05/01/2016';
DECLARE @d2 nvarchar(100) = N'05/31/2016';
DECLARE @sql nvarchar(max);

SET @sql = N'Select *
FROM OPENQUERY(LinkedServer,
''UPDATE #Test
SET field1 = a.salesname SalesName
,field2 = b.salesTotal TotalSales
from sales a
inner join empinfo b
on a.empid = b.empid
WHERE a.salesname IN ('+ REPLACE(@salesname, '''', '''''') + N')
AND iv.jbinvoicedate BETWEEN ('+ CHAR(39) + CHAR(39)
+ CAST(@d1 AS NVARCHAR(500)) + CHAR(39) + CHAR(39) + N')
AND (' + CHAR(39) + CHAR(39) + CAST(@d2 AS NVARCHAR(500)) + CHAR(39)
+ CHAR(39) + N')''
);
';

EXEC sp_executesql @sql;


texts: is it possible to trim the table output for a specific job?

texts:
I have a routine to save the logins and apply them on a different server , and I run this routine from a job that I have scheduled to run everyday.

On that job I have specified these 3 things:

Log to table
Append output to existing entry in table
Include step output in history

as can be seen on the picture below:

I am happy with that, however, I was wondering if there is a way to trim the table output because when I open it I see records from over 4 months ago, which are no longer relevant now, as shown on the picture below:

when I click View:

I get:

and the table has over 61 thousand lines.

Every time the job runs it adds about 600 new lines to the bottom of the table.

Can I keep the last let's say 10 thousand lines only,getting rid of the older lines?


texts: MySQL 8.0 - Could not acquire management access - Administration issue

texts:
I am using MySQL Workbench for many months, it was working fine. Today, when I tried to open Server Status in the MANAGEMENT tab, below error occurred.

MYSQL80 and Windows Management Instrumentation services are running.
I saw some posts regarding this issue, but the solution they have given was for older version.
Everything works fine. Even Test Connection is successful. But I am not able to access management related activities even though I am using "root" user.
How to resolve this issue? If it's administration access issue, how to check and change administration access privilege's?


texts:
SQL Server 2012. Sample query at the bottom of this post.
I'm trying to create a simple report for when a given database was last backed up.
When executing the sample query with output to text in SSMS, the DB_NAME column is formatted to be the max possible size for data (same issue exists in DB2, btw). So, I've got a column that contains data that is never more than, say, 12 characters, but it's stored in a varchar(128), I get 128 characters of data no matter what. RTRIM has no effect on the output.
Is there an elegant way that you know of to make the formatted column length be the max size of actual data there, rather than the max potential size of data?
I guess there exists an xp_sprintf() function, but I'm not familiar with it, and it doesn't look terribly robust.
I've tried casting it like this:
DECLARE @Servername_Length int;
SELECT  @Servername_Length =        LEN(    CAST(   SERVERPROPERTY('Servername') AS VARCHAR(MAX)    )   ) ;

...
SELECT
   CONVERT(CHAR(@Servername_Length), SERVERPROPERTY('Servername')) AS Server,
...

But then SQL Server won't let me use the variable @database_name_Length in my varchar definition when casting.  SQL Server, apparently, demands a literal number when declaring the char or varchar variable.
I'm down to building the statement in a string and using something like sp_executesql, or building a temp table with the actual column lengths I need, both of which are really a bit more trouble than I was hoping to go to just to NOT get 100 spaces in my output on a 128 character column.
Have searched the interwebs and found bupkus.
Maybe I'm searching for the wrong thing, or Google is cross with me.
It seems that SSMS will format the column to be the maximum size allowed, even if the actual data is much smaller. I was hoping for an elegant way to "fix" this without jumping through hoops. I'm using SSMS 2012.
If I go to Results To Grid and then to Excel or something similar, the trailing space is eliminated. I was hoping to basically create a report that I email, though.
Sample query
--------------------------------------------------------------------------
QUERY:
--------------------------------------------------------------------------
SELECT
   CONVERT(CHAR(32), SERVERPROPERTY('Servername')) AS Server,
   '''' + msdb.dbo.backupset.database_name + '''',
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  msdb..backupset.type = 'D'
GROUP BY
   msdb.dbo.backupset.database_name
ORDER BY
   msdb.dbo.backupset.database_name


texts: restore multiple .trn logs in SQL 2008 R2

texts:
I am using SQL server 2008 R2 and need to restore 36 .trn logs to a database. I have restored the first 4 using the restore transaction log functionality in SSMS (right click database > tasks > restore > transaction log). Is there a way to restore them all in order using this tool or with a script? The .trn files are in a folder on a remote server. I do not not need to restore all the files in the folder, only ones between specific dates.

Thanks in advance


texts: Search NVARCHAR(MAX) string in other string

texts:
I want to find the index of a string in other string. I try to use CHARINDEX and PATINDEX but nothing worked because the search string is NVARCHAR(MAX) and these functions are limited to 8000 chars only.

I am getting the following error:

Msg 8152, Level 16, State 10, Line 13
String or binary data would be truncated.

Are there any alternatives?

The goal is to replace the find string with other, but REPLACE is not working with NVARCHAR(MAX) either. That's why I am trying to use .WRITE clause but need to calculate the offset.

This is sample code. Basically, I am trying to replace the source of img HTML tags from BASE64 to relative url.

DECLARE @DataSource TABLE
(
    [Content] NVARCHAR(MAX)
)

DECLARE @defaultStartScreenOld nvarchar(max) = N'a NVARCHAR(MAX) big image in BASE64 format';
DECLARE @defaultStartScreenNew nvarchar(2000) = N'images/surveo/presetImages/Thumb_PointerStart.jpg';

INSERT INTO @DataSource ([Content])
SELECT CONCAT('<RUSLAN src="', @defaultStartScreenOld, '"/>')

SELECT [Content]
FROM @DataSource

UPDATE @DataSource
--SET [Content].WRITE(@defaultStartScreenNew, 10, LEN(@defaultStartScreenNew))
SET [Content] = REPLACE([Content], @defaultStartScreenOld, @defaultStartScreenNew)

SELECT [Content]
FROM @DataSource


texts: Duplicate index - Same key, one with INCLUDE columns, which one to drop?

texts:
I have 2 indexes that have the same key, but one also has INCLUDE columns. Which one can I drop?

They are the same, except IndexInclude has 6 INCLUDE columns.

Both have some reads (IndexNoInclude has much higher seeks).

I believe the IndexInclude will satisfy anything that would've used IndexNoInclude right? Since the keys are exactly the same.

Thanks for all the help in advance.

Output from sp_Blitz. Sorry for the bad format

Details: db_schema.table.index(indexid)
Definition: [Property] ColumnName {datatype maxbytes}   Secret Columns  Fillfactor  Usage Stats Op Stats    Size

IndexINCLUDE [1 KEY] EXECUTION_TIME {datetime 8} [6 INCLUDES]
Reads: 101,913 (101,913 seek) Writes:2,381,661,369
0 singleton lookups; 225 scans/seeks; 0 deletes; 0 updates;     263,358,683 rows; 5.4GB

IndexNoInclude   [1 KEY] EXECUTION_TIME {datetime 8}
Reads: 1,181,595 (1,181,595 seek) Writes:2,380,483,678
0 singleton lookups; 15 scans/seeks; 0 deletes; 0 updates;  263,358,683 rows; 2.9GB


texts: Oracle export not importing correctly (missing more than half of the tables)

texts:
I have an export of a 75gb database broken up into 2gb files. When I run imp utility after creating the tablespaces and users to match the original database, I don't think all tables are created. I think this because the table names (alphabetically) go from A-D. Each of the existing table names have a matching sequence. However, the sequences go from A-W. So there "should" be about 2x more tables that start with letters C-W.

So my first question is, is the entire schema for the database stored in the first dmp file (when it is split into multiple files with exp)? Or can it be split up into multiple. Looking through the first dmp file, I can not see any indication of the missing tables.

Alternatively, is this just a bad export?


texts: CROSS JOIN with temp table and CASE statement

texts:
I'm trying to CROSS JOIN a table with a view. Here is my code:

 SELECT *
 FROM   ( SELECT    * ,
                    CASE TypeId
                      WHEN 1 THEN InGs828S
                      WHEN 2 THEN InGS828H
                      WHEN 3 THEN InGS828L
                      WHEN 4 THEN InEasyFlowWD
                    END AS InDevice
          FROM      dbo.Ports
        ) tempTable
        CROSS JOIN dbo.vwDevices
WHERE InDevice=1

I get Msg 207, Level 16, State 1, Line 4 Invalid column name 'TypeId'.  TypeId is a column in view vwDevices.

My question is why can't I use any columns from vwDevices in CASE *ColName*, while I can use columns from table Ports?


texts:
Alright, I'm trying to make a query for a library that will show which students have never borrowed a book. For this, I have done the following:

SELECT
    LEERLINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    LEERLINGEN.KLAS,
    COUNT(*) AS AANTAL
FROM
    UITLENINGEN
    INNER JOIN LEERLINGEN ON UITLENINGEN.LLNR = LEERLINGEN.LLNR
GROUP BY
    LEERLINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    LEERLINGEN.KLAS
HAVING
    COUNT(*) = 0;

This doesn't seem to work for some reason, as all it does is create an empty table when I click Execute.

What did I do wrong here?


texts: Active/current (1-5%) and historical records in same table: index or partitioning?

texts:
I am trying to discover the correct architecture for the following situation. An application stores 'source data' in multiple tables. Each of these 'source data' tables contains actual, current, active records, as well as old, historical records. There will be a maximum of about a 100 historical 'sets' of data (older data will be cleaned up). Data is loaded in the 'source data tables' once per day or less. The data is otherwise never updated (no OLTP), only read.

95% of the time, queries are executed only on the active records. These queries must be executed almost as fast as if they were the only records in the table. It's ok if queries on the old/historical records are a bit slower (say, a cost increased with a factor 5-10 max).

I currently implemented this as follows:

All 'source data' tables contain a column SRC_CURRENT with value 0 or 1. This column contains a bitmap index (but I've tried with a regular index too).
All 'source data' tables contain a column SRC_DATE with the (truncated) date of the set of data. This column contains a regular index.
All queries on all of these 'source data' tables always include either SRC_CURRENT = 1, or SRC_CURRENT = 0 AND SRC_DATE = <date>
The SRC_CURRENT flag is set correctly each time a new set of data is inserted.

But the performance isn't what I expected. What I'm looking for is a way to let the CBO know that the SRC_CURRENT column only contains a value of 1 in about 1-5% of the rows, and that in that case he can limit the query to only this small subset. If a value of 0 is supplied (95-99% of rows), then he can, of course, just ignore this index and rely on the SRC_DATE index instead to select only the appropriate subset.

Can this be solved using only indexes, do I need partitioning, or is what I'm trying to do simply impossible and should I for instance resort to 2 separate source tables of each type (a table containing the active rows, and a table and a _HIS table) and duplicate all queries, with the application selecting the correct query ?

I'm using Oracle 9i, but if there's a solution to this problem available only in later versions, feel free to mention it too, for future reference.

EDIT

I did a test with partitioning one of the tables on the SRC_CURRENT column. This does seem to fix it, both when targeting SRC_CURRENT = 1 as well as when targeting SRC_CURRENT = 0 AND SRC_DATE = <date>. Is this the proper approach ?


texts: Repeatedly emptying a table, keeping the table itself

texts: I have been using "delete from table_xyz" to delete everything in the table. Would it be better to instead use truncate? What difference does it make?


texts: How to list Extended Properties for all databases

texts:
I am currently working on task where we use Central Management Server  and Server Group for easy access to our SQL server boxes.

I am trying to query the extended properties for all databases on multiple SQL Servers as can be seen below:

  Is there a way that can be done using TSQL script?

I am using the code snippet from this social.msdn post that is pretty close to what I want to achieve, but my powershell skills are zero to none: Please find the script below :

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "computer\instance"

$dbs = $s.Databases

$query = @"
SELECT objtype,
        objname,
        name,
        value
FROM fn_listextendedproperty(default, default, default, default, default, default, default);
"@

foreach($db in $dbs) {
    $ds = $db.ExecuteWithResults($query)

    $table = $ds.Tables[0];

    foreach($row in $table) {

        Write-Host $row.Item("objtype") `
            $row.Item("objname") `
            $row.Item("name") `
            $row.Item("value");
    }
}

To add to the complication I am using the above Powershell script  within in Jenkins.


texts: Changing Database Owner Removed User Mapping

texts:
I ran an ALTER AUTHORIZATION to change a database owner from an AD account to sa and it seemed to cause the user mapping for a specific database to be removed. I’m not sure why this happened.

Is this normal and expected behaviour?


texts: How to enable windows authentication on server that is not part of domain

texts:
We have an SQL server instance running on a standalone server (not part of the organizations domain network). Is there any way that we can use windows authentication to logon to sql server without having to add the remote server to the domain. I hope my question is clear, what is want is to be able to logon to sql server using windows authentication from my client, but I am not able to add windows users in sql server because the server is not part of the domain and so cannot identify the domain users that I am trying to add. Is there any way to get around this?

Thanks in advance.


texts: SQL Server AG databases went into 'Synchronizing/In recovery' after upgrading to 2014

texts:
We have 3 replica AG setup:

2 replicas are in sync/automatic failover
the other(DR Server, different subnet) in asynchronous/manual mode
All these replicas were on SQL Server 2012.

Recently we upgraded the DR server to SQL Server 2014. Since then we've had a problem with the AG databases in the 2014 instance going into ‘Synchronizing/ in recovery’ state.

The SQL Server error log has the message:

  The recovery couldn’t start for the database ‘XYZ’

We tried to create a new database and add it to AG, which it works for the other two 2012 replicas, but on 2014 we see the same issue.

Is there any workaround to fix it, or is upgrading other replicas to 2014 the only available solution?


texts: SQL Server Enable filestream without having SQL Server Configuration Manager(so via command line only)

texts:
I have SQL server 2017 on a Windows Server 2019 Core installation, no GUI.  One of our users wants to use Filestream.  I tried to follow this setup and this however, since it requires SSCM I can't complete that part.  I can enable in SSMS however, cannot set file location etc, so when executing the stored procedure command on the setup page I get this error:

  FILESTREAM feature could not be initialized. The operating system
  Administrator must enable FILESTREAM on the instance using
  Configuration Manager.

When I connect to SSCM remotely through the computer management window and try to open the services none will display.  I can access the networking options but not the services so maybe those won't work remotely?  The firewall is off between the Core install and the GUI version I am trying to use.

Anybody have a way to remotely enable Filestream and set the needed parameters like you would through SSCM?

Thanks in advance.


texts:
How can I group (by) two joined tables (in an Oracle database) like this:

cant:
id      CANT_VAL    COD_VAL
18059   18.56       R12
18060   11          R10
18061   15          R11

col:
FK_ID_COL_DMA   CANT_VAL    COD_VAL
18059           1134        R10
18059           1234        R3
18061           1111        R5

to look like that:

id      CANT_VAL    COD_VAL
18059   18.56       R12
18059   1134        R10
18059   1234        R3
18060   11          R10
18061   15          R11
18061   1111        R5

Thanks!


texts: Show full detail error message on the console

texts:
I try to get the error from this call:

$ psql --dbname=synapse -c "SELECT     (json::JSONB->>'content')::JSONB->>'name' AS channel_name,     json::JSONB->>'room_id' AS room_id,     json::JSONB->>'sender' as sender FROM event_json WHERE json::JSONB->>'room_id' LIKE '%matrix.org%';";
Pager usage is off.
FEHLER:  nicht unterstützte Unicode-Escape-Sequenz
DETAIL:  \u0000 kann nicht in »text« umgewandelt werden.
CONTEXT:  JSON-Daten, Zeile 1: ...": {"age_ts": 1535735391873}, "content": {"body":...

but the error message is cut off, how do I get the full error message?


texts:
I need to create a database for data that looks like this:

I am planning to use this table structure:

CREATE TABLE IF NOT EXISTS `AUDCAD` (
  `date` BIGINT NOT NULL,
  `open` decimal(7,5) DEFAULT NULL,
  `high` decimal(7,5) DEFAULT NULL,
  `low` decimal(7,5) DEFAULT NULL,
  `close` decimal(7,5) DEFAULT NULL,
  `volume` INT DEFAULT NULL,
  PRIMARY KEY(date),
  UNIQUE (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

date column is minute difference timestamps. It will be unique and if same timestamp insert/update came, can be ignored or override with no problem.

This is my first time planning table structure. Does that sound have problem? Do you have a suggestion over this?


texts: check if a user-defined type exists before dropping it

texts:
The sp_droptype procedure can be used to drop a User Defined Type.

So I have a number of such sp_droptype calls in my "clean" script which I invoke using sqsh -i. However it is not possible to conditionally invoke sp_droptype only if the type does in fact exist or to instruct it to be silent if the type doesn't exist.

Is there a way to test if a type exists or not and only invoke sp_droptype if it actually exists? This would result in cleaner output.


texts: Is it possible for a MySQL database to have duplicate tables with the same name?

texts:
As in the question title - is it possible for a MySQL database to have duplicate tables with the same name? Is there a directive that can be used for a database to allow this?

Normally, when trying to add duplicate tables, MySQL should return an error #1050 - Table 'tablename' already exists, but recently, while working on a project, I noticed that almost every table in the database is duplicated (only the structure, new tables are empty). While I know that some table-creating scripts could be executed, MySQL shouldn't allow this, right?

How to even deal with such an issue? The program causing this is a Magento - a PHP-based eCommerce platform.

There are parts of a dump from this database:

--
-- Table structure for table `adminnotification_inbox`
--

CREATE TABLE IF NOT EXISTS `adminnotification_inbox` (
  `notification_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Notification id',
  `severity` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Problem type',
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Create date',
  `title` varchar(255) NOT NULL COMMENT 'Title',
  `description` text COMMENT 'Description',
  `url` varchar(255) DEFAULT NULL COMMENT 'Url',
  `is_read` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Flag if notification read',
  `is_remove` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Flag if notification might be removed',
  PRIMARY KEY (`notification_id`),
  KEY `IDX_ADMINNOTIFICATION_INBOX_SEVERITY` (`severity`),
  KEY `IDX_ADMINNOTIFICATION_INBOX_IS_READ` (`is_read`),
  KEY `IDX_ADMINNOTIFICATION_INBOX_IS_REMOVE` (`is_remove`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Adminnotification Inbox' AUTO_INCREMENT=26 ;

--
-- Dumping data for table `adminnotification_inbox`
--
-- there was some data from this table, not relevant here

-- --------------------------------------------------------

--
-- Table structure for table `
                adminnotification_inbox`
--
-- in use(#1146 - Table 'dev_testproject_1_12.
                adminnotification_inbox' doesn't exist)
-- in use (#1146 - Table 'dev_testproject_1_12.
                adminnotification_inbox' doesn't exist)

-- --------------------------------------------------------

I don't think this dump would even run without errors.


texts: Appropriate language to write a Script that Kills Long running query MongoDB

texts:
I want to create a script that kills queries when they reach  specific time threshold, I will be using db.currentOp() method to get information about currently running queries, and use db.killOp to kill the query, My organization mostly uses shell scripts but I find them confusing and not good enough to handle exceptions and errors, Is shell script good enough to get this done? or should I be using python which I am much more comfortable with
MongoDB 3.6.13


texts: What are some ways to implement a many-to-many relationship in a data warehouse?

texts:
The dominant topologies of Data Warehouse modelling (Star, Snowflake) are designed with one-to-many relationships in mind. Query readability, performance, and structure degrades severely when faced with a many-to-many relationship in these modelling schemes.

What are some ways to implement a many-to-many relationship between dimensions or between the fact table and a dimension in a data warehouse and what compromises do they inflict with regards to necessary granularity and query performance?


texts: What does SQL Server use to do a count(*) on a table?

texts: Internally, how does SQL Server determine the result for a select(*)?  Is it different between a heap and a clustered index?  Is it able to do smart things with the clustered index to not have to load the whole table?


texts: Concurrent backups in MS SQLServer 2008 R2

texts:
Can anyone please point me to a piece of documentation that suggest how many concurrent backups initiated by different BACKUP statements are possible in Microsoft SQL server 2008?

The documentation suggests that you can specify up to 64 different backup devices in one statement, but I have noticed today that SQL server was not treating my BACKUP statement while the one initiated by SQL Server Agent was running.

I have been closely observing the Performance Monitor and could tell that at one time only one .bak file was being written to.

Another proof of that would be a screenshot I have with SQL Management Studio query running time being 15 minutes, and the actual query result stating:

  BACKUP DATABASE successfully processed 3422697 pages in 389.656
  seconds (68.624 MB/sec).

So, how many simultaneous backups is SQLServer meant to handle (if any)?


texts: "copy to tmp table" for 24 hours, then timeout; when ALTERing to add PK & AI

texts:
I downloaded the Wiktionary XML dump, and ran mwdumper.jar to insert the data into MySQL.

Following the advice in the manual, I commented out the PRIMARY KEY AUTO_INCREMENT part of the CREATE TABLE statements, for page, revision and text (which have millions of rows in each, and very large BLOB's); in order to aid in a much faster import time.

After the data had been inserted, I ran the CREATE INDEX statements, and these executed fine (in ~30 seconds each).

My problem is, I'm now trying to add the PK's in, and just on the first line, it ran for 86,400 seconds (24 hours), then timed out!

SHOW FULL PROCESSLIST shows State: copy to tmp table.

Here are the queries I'm trying to run - it's stuck on the first one:

ALTER TABLE revision MODIFY COLUMN rev_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE text MODIFY COLUMN old_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE page MODIFY COLUMN page_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT;

When I ran SHOW GLOBAL STATUS while the query was running, the Handler_read_rnd_next variable was increasing, and there were no other connected users or applications running on the db, to otherwise influence that var.

How can I speed this query up? It's running on a quad-core 3.5GHz development machine with 16GB of RAM, but it's barely using any of this power.

The only options that have been changed from the default install are:

max_allowed_packet = 1G
innodb_log_file_size = 100M
innodb_log_buffer_size = 100M

And in MySQL Workbench:

Any advice is appreciated!


texts: SQL Server 2014 View any Live execution plan in Activity Monitor

texts:
I have a SQL Server 2014 Build(12.0.5205.0) running here.

Would It be possible to view any Live execution plan (from queries being executed by other users) in Activity Monitor ?

My problem is:

When I open the Activity Monitor - > Active Expensive Queries, I can see all expensive queries, but clicking right button the "Show Live execution Plan" is only enabled for queries that my user is running, for the any other user this menu is disabled:

An example:

I heard about Trace flags, enable a global trace flag using DBCC traceon(7412,-1).

So, my questions is:

Enabling This flag above (7412), considering the build version of my Sql Server, will it work? Or only in SQL Server 2016?
If it works, I don't have the sa rights. What kind of permissions do I have to ask for?


texts: Backup cleanup Ola Hallengren on AG fails after failover

texts:
We run an Always On Availability Group in SQL Server 2017 Standard Edition.
Backup is managed with Ola Hallengren Maintenance Solution with the following settings:
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = '\\10.90.93.30\backup',
@BackupType = 'FULL',
@Verify = 'N',
@CleanupTime = 721,
@Compress = 'Y',
@CheckSum = 'N',
@LogToTable = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'Backup_Encryption_Certificate_2020'

Same job on both servers in the group scheduled to run once a day. Log backups are scheduled every hour and the job looks like this:
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = '\\10.90.93.30\backup',
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = 168,
@CheckSum = 'Y',
@LogToTable = 'Y'

As you see backups are made to a network share from both servers. However if the Availability Group fails over the cleanup stops working (for the full backups, logs are still being cleaned up). System databases are also backed up to the same network share and cleanup works for them as well for both servers.
I have tried to google for a solution without result, only found similar issues but where backups have been stored locally on each server. Grateful for any help on this.
My first question here so please also give advise if something in the question itself could be improved for next time.
ADDITION 2021-01-20:
Both servers are identical in terms of Backup preference (Primary) and SQL Agent service account (a domain user).
Checking CommandLog table created by Maintenance Solution I find that this command has been executed:
DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'\\PathToNetworkShare\dbcluster$AG_Name\DatabaseName\FULL', 'bak', '2020-12-21T01:52:30' IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1)

with ErrorNumber = 0
Running it in Management Studio on Primary server gives a "Commands completed successfully" response. But no files are deleted. Running the exact same command with the exact same user on the secondary server gives the same response but also deletes the files as expected.
This similar command for deleting log backups
DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'\\PathToNetworkShare\dbcluster$AG_Name\DatabaseName\LOG', 'trn', '2021-01-13T02:00:10' IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1)

is working perfectly on both servers.
The server has three user databases. All three have the same problem (from that one server), full backups are not deleted but logfiles are. System database backups are also deleted as expected from both servers.
I have even tried setting Full control to Everyone on the directories in question with no result.


texts: enable log_bin_use_v1_row_events in mysql 5.6

texts:
I have a MYSQL master with two slaves also running MYSQL 5.6. I want to create another slave and that needs to be MYSQL 5.5. I was wondering if replication will work smoothly.
I have  log_bin_use_v1_row_events switched off in MYSQL 5.6 and I am using mixed format replication.

Can anyone give me advise on doing above.


texts: Php PDO Get all column names from table in specific database

texts:
I currently have the following query. Which fetches all column names for a table. But when there are 2 tables with the same name (in different databases) it fetches those column names as well.

In the following query $table of course means the requested table. In this case for example customer.

select column_name from information_schema.columns
where lower(table_name)=lower(\''.$table.'\')

So my question is: How do I specify which database the query should look into.


texts: What effect does serializable isolation level have on DDL-statements?

texts:
I'm using Red Gate SQL Compare to create a release script based on differences between SVN and a database. This results in a script containing a bunch of table- and procedure-changes and it works fine. However, one thing puzzles me, it's using transaction isolation level serializable.

I know what it does to dml-statements, but I'm not sure what it means for ddl. Can someone enlighten me, perhaps with an example?


texts: MySQL partition by date doesn't pruning the query

texts:
I have this table:

CREATE TABLE `location` (
  `id` int(11) NOT NULL,
  `lat` DOUBLE NOT NULL,
   #...
  `date` datetime NOT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT '0',
  KEY `pk` (`id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (MONTH(date))
(PARTITION part0 VALUES LESS THAN (2),
 PARTITION part1 VALUES LESS THAN (3),
 PARTITION part2 VALUES LESS THAN (4),
 PARTITION part3 VALUES LESS THAN (5),
 PARTITION part4 VALUES LESS THAN (6),
 PARTITION part5 VALUES LESS THAN (7),
 PARTITION part6 VALUES LESS THAN (8),
 PARTITION part7 VALUES LESS THAN (9),
 PARTITION part8 VALUES LESS THAN (10),
 PARTITION part9 VALUES LESS THAN (11),
 PARTITION part10 VALUES LESS THAN (12),
 PARTITION part11 VALUES LESS THAN MAXVALUE);

I use partition by Month function to speed up the queries because I have a huge amount of data  in this table.

But when I tried a query like this:

Explain SELECT * FROM test.new_table where date between '2017-02-02' and '2017-04-25';

I got this result:

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
1 | SIMPLE  | new_table | part0,part1,part2,part3,part4,part5,part6,part7,part8,part9,part10,part11  | ALL      |           8   | 12.50 |   Using where

I see that I can't benefit from Pruning feature of MySQL.

What I can do else to enhance incoming queries ?

Edit

The most used case to get data is Between 2 dates.

Examples of queries:

SELECT
    l.*,
    u.name
FROM
    locations l
    LEFT JOIN `user` `u` ON `l`.`user` = `u`.`id`
WHERE
    l.`date` BETWEEN `u`.`date_start` AND `u`.`date_end`
 OR (ISNULL(`u`.`date_end`) AND (`l`.`date` >= `u`.`date_start`))

2

SELECT
    l.*,
    u.name
FROM
    locations l
    LEFT JOIN `user` `u` ON `l`.`user` = `u`.`id`
WHERE
    l.id in (...)


texts:
I have a test environment for Oracle single instance on ASM 11.2.0.3.

I have restored database from tape and during the recovery, the system crashed by some I/O error.

I restarted the server, mounted the database and run validate on archivelog(thinking that the blocks in this archivelog may be corrupted)

RMAN> validate archivelog sequence=2994;

RMAN-03009: failure of validate command on ORA_DISK_1 channel at 02/25/2014 12:24:13
ORA-19501: read error on file "+DATA01/crimesys/archivelog/2014_02_24/thread_1_seq_2994.294.8
ORA-15081: failed to submit an I/O operation to a disk
ORA-15081: failed to submit an I/O operation to a disk

And the system crushed again.

Note:  Other validates are successful just this archivelog causes the system crash.

Details:

--Asm disk

ls -la /dev/oracleasm/asm_disk1
brw-rw---- 1 grid asmdba 253, 2 Feb 25 13:13 /dev/oracleasm/asm_disk1

--Trace info

more /u01/app/oracle/diag/rdbms/crimesys/crimesys/trace/crimesys_ora_2696.

WARNING: Read Failed. group:1 disk:0 AU:114682 offset:512 size:1048064
path:/dev/oracleasm/asm_disk1
         incarnation:0xe968a075 asynchronous result:'I/O error'
         subsys:System iop:0x7fd93c64b680 bufp:0x7fd93b64e000 osderr:0x0 osderr1:0x0
WARNING: failed to read mirror side 1 of virtual extent 13 logical extent 0 of file 294 in gr
oup [1.353915012] from disk DATA01_0000  allocation unit 114682 reason error; if possible, wi
ll try another mirror side

--Also when I check the disk of ASM it is successful.

SQL> alter diskgroup data01 check

NOTE: starting check of diskgroup DATA01
Tue Feb 25 13:36:59 2014
GMON checking disk 0 for group 1 at 5 for pid 18, osid 2181
SUCCESS: check of diskgroup DATA01 found no errors
SUCCESS: alter diskgroup data01 check

How to solve? Is the virtual block corrupted? How to check?
Please help.


texts: Getting exception on running sh.status() command on MongoDB sharded cluster

texts:
I am running sh.status() command on my mongos instance of sharded Mongo cluster and I am getting below exception.

mongos> sh.status()
assert: command failed: {
    "ok" : 0,
    "errmsg" : "error processing query: ns=config.mongosTree: ping $gt new Date(1542173873192)\nSort: {}\nProj: {}\n No query solutions",
    "code" : 2
} : aggregate failed
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:290:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1312:5
printShardingStatus@src/mongo/shell/utils_sh.js:611:13
sh.status@src/mongo/shell/utils_sh.js:78:5
@(shell):1:1

2018-11-14T05:38:55.904+0000 E QUERY    [thread1] Error: command failed: {
    "ok" : 0,
    "errmsg" : "error processing query: ns=config.mongosTree: ping $gt new Date(1542173873192)\nSort: {}\nProj: {}\n No query solutions",
    "code" : 2
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:290:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1312:5
printShardingStatus@src/mongo/shell/utils_sh.js:611:13
sh.status@src/mongo/shell/utils_sh.js:78:5
@(shell):1:1

I am running Mongo 3.2 on ubuntu 16.04 platform. It is working fine on MongoDB version 3.0 on ubuntu 14.04 platform.
I have reinstalled everything and tried this on new machines too, everything is working fine except sh.status() command.

Thanks.


texts: Are there any reasons to avoid using sp_rename for renaming objects?

texts:
In the documentation it is clearly said:

  Changing any part of an object name can break scripts and stored
  procedures. We recommend you do not use this statement to rename
  stored procedures, triggers, user-defined functions, or views;
  instead, drop the object and re-create it with the new name.

What about renaming:

primary keys
unique keys
foreign keys
default constraints
indexes

I am going to rename all of the above objects in order to match our internal naming conventions but I want to write an automatic script for doing this (as there are thousands of constraints).

The only issue I can think off is that if index is used with a hint in statement it will be broken.

Could you tell any other reasons not to use the sp_rename for such purpose?


texts: Return row with most matching parameters

texts:
I'm trying to make a stored procedure who will return me the RowID with most matching parameters. Let's say we have a table:

+-------+------------+------------+-----------+
| RowID | DocumentID | EmployeeID | CompanyID |
+-------+------------+------------+-----------+
|     1 |         36 | 1          | 4         |
|     2 |         36 | null       | 2         |
|     3 |         36 | 4          | null      |
+-------+------------+------------+-----------+

And I send let's say values DocumentID = 36 ,EmployeeID = 5, ComanyID = 2,
it should return me second row since DocumentID and CompanyID exist.

Some other situation would be, if I send DocumentID = 36, EmployeeID = 5 and CompanyID=10, it should return me first in the table.

If there is too little information about this problem, or it's not clear, feel free to ask for more detail.


texts: How to use variables in file paths in MySQL statements?

texts:
I have now a problem that I have to set paths through variables in queries like

load data infile  '/my/file/path/to/file.txt'
into  table  database1.table2
fields terminated by ''
lines terminated by '\r\n'
( id, name_en, name_de, value_en, value_de);

The part "/my/file/path/to/" should be set with a variable, but the only way I have found is to use prepared statements, which is not possible with this type of queries.

Is there any other option to keep the path in a variable?


texts: Maintenance plan backup and reload the same database

texts:
I want in my maintenance plan every night to copy the production database to a backup, and reload the newly-created backup into a test database. By reading here:

Restore database - maintenance plan task

I see that it is possible to do it using first the Back Up Database Task and then the Execute T-SQL Statement Task.
The problem is, the backup file has every time a different name, like

  SIAM_backup_2014_08_14_221501_2611766.bak

If I don't find a better solution, I will have to save the database backup always on the same filename, overwriting it every time.
Is it possible to pass a parameter between the two tasks, with the name of the backup file, or is there a T-SQL statement that means "Reload the latest saved full backup", or do you have other ideas?


texts: What's the difference between EXECUTE AS USER and EXECUTE AS LOGIN that makes the second one behave unexpectedly?

texts:
Recently I've been using EXECUTE AS LOGIN to check if a certain person could or couldn't use a table function in one of our databases. The tests showed that he could but he repeatedly reported failure.

The person uses login 'WEB' to connect to the database and he has the user 'WEB' in that database associated with login 'WEB' so I tried the following scripts:

-- part 1
EXECUTE AS USER = 'WEB'
GO
SELECT
    USER_NAME() AS 'user_name'
    ,SUSER_NAME() AS 'suser_name'
    ,SUSER_SNAME() AS 'suser_sname'
    ,SYSTEM_USER AS 'system_user'
GO
REVERT
GO

and

-- part 2
EXECUTE AS LOGIN = 'WEB'
GO
SELECT
    USER_NAME() AS 'user_name'
    ,SUSER_NAME() AS 'suser_name'
    ,SUSER_SNAME() AS 'suser_sname'
    ,SYSTEM_USER AS 'system_user'
GO
REVERT
GO

The first part was fine with the result of:

  WEB | WEB | WEB | WEB

But the second result was a bit confusing:

  dbo | WEB | WEB | WEB

What's the difference between EXECUTE AS USER and EXECUTE AS LOGIN that makes the second one fail? Besides, of course, the first one being database level and the second one being server level impersonation, of what I'm aware of and does not explain the situation here.


texts:
We have created a dump file (.sql) using the command mysqldump in mysql5.5.
Now we want to restore the same. We are using the command :-

-u[username] -p[pwd] [dbname] < [.sql file]

But we are not able to restore.
We are using Win7 SP1 32 bit OS.


texts:
Can we use regular expression to select the item from database?

The table item is like below

Table column|name|
10.01.02    | a  |
100.2.03    | b  |
1021.10.04  | c  |

Now my problem is that i need to select the code and get the substring like below

Table column|name|
10.01       | a  |
100.2       | b  |
1021.10     | c  |

any suggestion with regular expression or substring?


texts: MySQL asynchronous multi master replication conflict resolution

texts:
I have setup Multi Master replication, following this blog thread:

http://0wned.it/geek-bits/guides/mysql-multi-master-replication/

And now it synchronizes perfectly, up until the moment the connection between the servers is lost. When I have no connection both servers still update fields. This results in the following problem:

connection is lost.
server1 updates a field to the value "bike".
server2 updates the same field a few seconds later to the value "car"
connection restored
server1 gets the value "car" from server2 and updates the field
server2 gets the value "bike" from server 1 and updates the field.

Is there a way to check if the value has already has been changed and neglect the update based on time of update? In the field on both servers the value "car" should be the current value.


texts: Mariadb clustering Index design question

texts:
Here are the columns of concern for a table describing tasks:

Org_ID
Task_ID
Task_property_columns

The only requirement for Task_ID is that it is unique per every Org_ID, however I would prefer not handle that programatically and that it be auto incremented.

The requirement for the Org_ID is that I would like to run metrics and analytics using sort operations with the table organised by Org_ID and ported nationally some of the task properties. The table being clustered on Task_ID would be useless,  but would be very beneficial to generate on Org_ID.

From reviewing the documentation, it seems that clustering is picked for you in MariaDB where it will use the primary key, a unique non-nullable index, or a background auto generated index in that order.

I am pretty  new to MariaDB and was hoping for some help in picking the right solution. From what I understand I can't find a good way to auto increment and cluster how I want it to as well.


texts: Storing files with extension in database as BLOB

texts:
Recently during development process I've faced file storage issue. I'm not sure which solution is the best one that's why I decided to write this question. I don't have a lot experience of database administration that's why any tip can be really useful for me.

In my application project I have several types of files that have relationship with specific tables, for example:

Invoice has one scan
User has one document
Transaction has one receipt
Building has many photos

... etc.

All those files: scan | document | receipt | photo have different extensions(pdf/jpg/png etc.).

I decided that files corresponding for each table will be stored as BLOB datatype. Because of BLOB usage there is need to store file extension somewhere in database.

I was considering few possible solutions:
(For demonstration purposes I will attach simple tables visualization)

user | invoice | building_photo | transaction have require file stored as BLOB or MEDIUMBLOB, but extension of files is missing. What would be the best way of storing that? as VARCHAR column in each table or as separate table?

Example of extension storage in each table:

This solution is the easiest one to implement, extension as additional VARCHAR is handy when I'm inserting new data, but I'm repeating this information since I will have two files with the same extension.

Example of extension storage in separate table:

This solution seems to be more optimized but little bit harder to manage while inserting new data. I would be grateful for any suggestion about problem described above.

Another implementation that I've considered is to not store all files in separate tables but instead create one table called file that will contains file BLOB and then invoice | user | transaction | building etc.
will have relationship to that table. Visualization below:

In this case again I'm keeping extension in separate table, but file data as well. All files are in the same place but I'll end up with large table and a lot one to one relationships. If such solution makes sense also I would be grateful for option, or maybe there is another way of doing such things?


texts:
I have just cut 'n' paste some SQL code into SQL developer (4.0.2.15). I have a right margin set and selected CTRL+F7 to format it. However I have two problems

The code is wider that the right margin I have set
The keywords are in capitals and the coding standard here is lowercase.

I have been unable to find how to change this or is this possible?


texts: Executing delayed inserts with MySQL event scheduler

texts:
A table that I'm working on, has two triggers; one is after insert and the other is before delete. These triggers carry out calculations and copy over some rows to a backup table. This backup table is to be used in the application for showing data, so data can be delayed, but this table should be available in the db for JOINs etc.

Now if a large quantity of insert's/delete's are carried out on the primary table, the triggers slow the entire application down. I've thought about creating a sql-queue table, and using the triggers to dump any SQL query that needs to run, in the backup table.

Then, I'd create an MySQL event that checks this sql-queue table every 'x' seconds for queries to execute. At least in this manner, the user using the application is not blocked waiting for the triggers to execute and insert data into the backup table.

Is this approach recommended?


texts: Why does FROM multiple tables default to cartesian product?

texts:
What was the idea behind doing a cartesian product (cross join) when i did a query like -
SELECT * FROM agents, orders

I would think that they would concatenate (like pandas). It feels more natural to add tables instead of multiplying them.
Just curious and did not find on the internet the rationale behind defaulting to cartesian product. I am assuming that syntax FROM table1, table2 according to SQL would probably be more correct with cross join but why?


texts: Changing the Replication Distributor Server

texts:
Scenario:

We have Server A, a current distributor server that also does some publishing (but not the exclusive publisher servers).  We have servers C, D, E, F and G.  All of these are subscribers to the publishers.  We have a Server B that we want to exclusively use for distributing, no publishing.  We want all replication to use Server B as the distributor and not Server A, meaning that I can either slowly switch publication at a time, or change the configuration, relative to what option is available (unfortunately, this is not as clear as I had hoped in MS articles).

Questions:

Can a publisher server have two distributor, such as in the above example, can Server A and Server B both be distributors, where Server B distributes for some of Server A's publishers and Server A also distributes for some of its own publishers as well?  In configurations, the below code would return two values for distributor - Server A and Server B:

USE master
GO

EXEC sp_helpdistributor
Assuming the question for option 1 is not possible, if I were to remove Server A as the distributor and replace it with Server B, what's the offline time for all the publications in existence on Server A, meaning is replication set up to where a simple configuration change doesn't disrupt the process (like a mirror aware connection string), or will this cause a massive outage?

Thanks!


texts: Setting up a Database for Multiple User Access

texts:
I am working on revamping the database at the company I work at.  I've been making decent progress getting various parts set up, but something occurred to me today that I hadn't really thought to consider as a variable for my setup.

With this database, there is the potential for there to be multiple users accessing it at the same time.  Now it isn't an insane amount, like 50 different users all going at it at once.  Rather the situation is closer to their being 2 or 3 users that can be assumed to be accessing the database at the same time.

Now each of these users would potentially be Updating, Adding, or Deleting records within the database.  As such I am concerned about potential problems arising from a situation like the following:

Employee A begins editing a record
Employee B deletes the record Employee A is current editing
Employee A goes to commit changes, but the record no longer exists

So what are some ways I can set up the database to get around issues like this?  Each person will be logged in to the database on an account, so I can track who is online and potentially making changes.  What is some recommended reading any of you would have for a situation like this?


texts: DB2 importing/replacing fields from a file that contains XML

texts:
In two tables I have a column called table1.column_xml and table2.column_xml_2 defined as CLOB(1000000). The fields in the column contain XML formatted information like below

column_xml
--------------------------------------------------------------------------------
<values>
 <comment>test test test</comment>
 <rights></rights>
 <title>TEST MAN</title>
</values>
--------------------------------------------------------------------------------

What I would like to do is import/or replace any existing values in table2.column_xml_2 with the values from table1.column_xml.

So in simpler terms I want to export the values from column_xml into a file then import/replace into column_xml_2


texts: Low performance with dynamic queries and cursors

texts:
Doing some legacy work, I found the following procedure that used to take between 1.5 to 6 hours, totally not expected when dealing with less than 2 million records. After the change it takes less than 3 minutes.

Why this script was taking too much time?

  The script extracts only one value from one field, between 20 and 40
  different sources, each source has from 100 to 100,000's records. Total records at the destination table is up to from half million to 2 millions.
  The table SOURCE_TABLE_FIELDS contains the table names, fields and criteria to build the dynamic queries.

DECLARE source_c CURSOR FOR   --cursor starts here
SELECT table_name, field_name, condition
FROM SOURCE_TABLE_FIELDS

OPEN source_c

FETCH NEXT FROM source_c
INTO @table, @field, @condition;

WHILE @@FETCH_STATUS = 0
    BEGIN

        PRINT 'DEBUG CHECKPOINT: START LOOP'

        declare @sql as nvarchar (1000)
        declare @sql_source as nvarchar(1000)

        set @sql_source = 'select ' + @field + ' as SOURCE_FIELD from ['  + @table + '] A '
                        + ' WHERE 1=1 ' +
                        + @condition +
        'EXCEPT SELECT DISTINCT DEST_FIELD FROM ' + @DESTINATION;
        set @sql = 'INSERT INTO ' + @DESTINATION + '(DEST_FIELD) ' + @sql_source;

    EXEC SP_EXECUTESQL @SQL
    PRINT 'DEBUG CHECKPOINT: END LOOP'
    FETCH NEXT FROM source_c INTO @table, @fild, @condition;
    END
CLOSE source_c;
DEALLOCATE source_c;

  Look at the PRINT 'DEBUG' marks; sometimes the last line printed was from the end, sometimes the one at the start. It could take up to an hour to jump. (I checked this by printing timestamps and everything).

By sometimes I mean, usually after the first 10 loops, randomly. No matter if I sorted out the sources with small records at first or at last.

Important remarks:

I don't have permission to access the SQL-Profiler
I don't have access to sys.dm_exec_query_stats or any other high level stuff.
I'm not a DBA, only a developer but with tons of SSIS work lately.


texts:
I need to automatically synchronize changes made in an Azure SQL database to our DB2 system. Whenever a change is made in Azure, DB2 should also be modified.
Azure DB creation is not  started and table schema will be the same except for new modification details columns.
This activity is done as part of mainframe to cloud migration and there are other applications(not part of migration) that use the same tables. Because of this  some tables need Azure SQL to DB2 sync and others need DB2 to Azure sync .
The organization has previously used Attunity replicate for DB2 to azure sync. This cannot be used for Azure to DB2 sync because of its limitations with db2 as a target.
My current options are :

Batch program used with triggers in azure DB
Azure data factory

Which is my best option? The answer does not have to be limited to the above options.


texts: SSIS data flow task failed for no reason

texts:
An SSIS package keeps failing for no reason. There are only three lookup transform components in the package (besides the source and output component), two running in partial cache mode and the other in full cache mode. Each lookup will need to find records among tens of millions of entries.

When the full-cached component have cached around 10M records the data flow will fail. The only error message is "Data flow task failed" (no error code provided). At that time the server has 15% free memory, and SSIS uses up about 3GB. There is no warning about memory space during execution. Windows Server and SQL Server are 64 bit, but I'm not sure about Visual Studio BIDS.


texts:
I am seeing a timeout to the Pricincipel SQL server (2008 R2)

The exact error I am seeing is this:

  Timeout expired. The timeout period elapsed prior to completion of the
  operation or the server is not responding. This failure occured while
  attempting to connect to the Principle server.

  System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The
  timeout period elapsed prior to completion of the operation or the
  server is not responding. This failure occured while attempting to
  connect to the Principle server. --->
  System.ComponentModel.Win32Exception (0x80004005): The wait operation
  timed out at XXXX

What I am trying to understand is is this a standard timeout, ie. the DB was unable to return the required results within the allowed time? Or does the "Principle Server" wording signify another issue?

I have not been able to find much about this specific error and need to know if its a performance issue that tuning the indexes and procs will help resolve or is there another issue I need to look at instead.


texts: RavenDB - Disaster Recovery on a Replicated Instance

texts:
EDIT: I'm encountering precisely the same issue.

Assume that I have two RavenDB instances.
Instance 1 contains one document: users/1.
Instance 2 contains three documents: users/2, users/3, and users/4.

_

Assume that I configure and enable replication for both instances.

That is, Instance 1 will replicate to Instance 2 and Instance 2 will replicate to Instance 1.

After replication, both instances will have all four documents.

But both instances will agree that users/1 's Raven-Replication-Source is Instance 1, and users/2, users/3, and users/4 will have their Raven-Replication-Source set to Instance 2.

_

Assume that Instance 1 suffers a catastrophic failure and I need to reinstall RavenDB.

_

My testing shows that in this scenario: users/2, users/3, and users/4 will again be replicated to Instance 1.
However, users/1 will not be replicated to Instance 1 because its Raven-Replication-Source is Instance 1.

My Question:

How can I force Instance 2 to replicate users/1 back to Instance 1, even though users/1 was originally created on Instance 1 and its Raven-Replication-Source is Instance 1?


texts: PostgreSQL fsync off for a single database

texts: I have a write-intensive database stored in PostgreSQL v9.4.4 and it causes IO on my device. I'd like to switch fsync off for only a single database, not PostgreSQL server wide. (I accept that it can cause corruption when unexpected shutdown occurs). Help me find out how to make it.


texts:
I'm a bit confused by terms latency and throughput in case of databases.

My understanding is that latency represents time needed to perform a single request (e.g. insert or select), while the throughput represents the number of such operations during a certain amount of time (e.g. how many inserts per second).

If my understanding is correct, shouldn't it always be the cases that the two are correlated? If latency decreases throughput should increase and vice versa? So a database having low latency should have high throughput?

What comes to my mind is that many requests can be performed in parallel, so that could lead to the fact that even a single request is slow (latency high) in general the database can perform well (can process large number of requests in parallel, especially if we are talking about scalable distributed database). Is that the point?

I started thinking about this by reading about Cassandra:

  Cassandra also places a high value on performance. In 2012, University of Toronto researchers studying NoSQL systems concluded that "In terms of scalability, there is a clear winner throughout our experiments. Cassandra achieves the highest throughput for the maximum number of nodes in all experiments" although "this comes at the price of high write and read latencies."

Is it the case that read and write are slow, but it achieves high throughput because it is distributed?


texts: What is an efficient storage method to store Graph Data from GraphX (Spark)?

texts:
I generate graph data using Spark's GraphX library and I need an efficient way to store graph data. I have access to Apache Cassandra and ideally I want to store the graph data in there but the issue is I don't know how to efficiently store the graph structure in Cassandra. I have looked at Titan's backend storage in Cassandra but I could not find a detailed explanation of how the data (edges and properties) is formatted and stored. I am giving Titan as an example, any existing alternative or methodology is welcome. The persistence layer doesn't even have to be Cassandra necessarily. I mostly need an efficient way to store the graph that also enables me to do filtering on the backend. I.e. if I store on HDFS, I have to load the whole DB content on Spark to analyze it and perform transformations, and for big data that is unfeasible so I need a kind of storage that enables me to filter server-side what I need before loading it onto my memory.

Edit: My requirements is that this is a distributed graph database that can handle Big Data, is scalable and has to have a Spark connector of sorts (or a way to connect with Spark functionalities).


texts: PostgreSQL 9.3 Amazon RDS gives error while using UUID-OSSP extension for generating uuid

texts:
I have created DB instance on RDS and used below function. It gave error while with the same PostgreSQL version on a local development machine it works fine.

create or replace function uuid() returns uuid as 'uuid-ossp', 'uuid_generate_v1' volatile strict language C;

here is the error log:

ERROR:  permission denied for language c
********** Error **********

ERROR: permission denied for language c
SQL state: 42501

I'm using this function in ruby on rails 3.2 for generating uuid for my schema.


texts: How to convert data type from varchar to number in SQL Server?

texts:
I want to convert a field currently in varchar data type to number data type in the Views of SQL Server say (dbo.CUSTOMER.STORE_NBR) from Varchar to number.
How to do that ?
Will CONVERT function will do the necessity?
If yes, can you help us with syntax?


texts: InnoDB tables in an 8GB RAM server: Are these settings optimized?

texts:
I've been reading about how to optimize my database (MySQL 5.6.51) settings, and I've found an article suggesting to increase the key_buffer as much as possible.
Here's my my.cnf file:
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1
performance-schema=0
max_allowed_packet=32M
open_files_limit=10000
innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
innodb_file_per_table=1
key_buffer_size = 32M
default-storage-engine=MyISAM

query_cache_type=2
query_cache_size=128M
query_cache_limit=2M

Should I keep these settings as they are? I'm administrating my own website database, but I'm not a proper Database Admin.
Thanks for your help.
EDIT: Sorry, by mistake I indicated my MySQL version as being 7.0.33, but that is my PHP version! (very silly mistake, I know). I corrected that in the question.


texts:
I'm building a Web/Web service application based on a number of (MySQL) databases. The databases are all built from externally derived data from dozens to hundreds of sources. My general plan is to have completely separate test, staging and production databases. The typical workflow is:

Check to see if any of the data distributions have been updated. The update frequency varies from daily to yearly, and everything in between.
If so, download the data, perform whatever transformations are required to turn it into SQL, and build a test database
Run unit tests with the new data
If everything passes, load the new data to a staging database
Run tests again on the staging database (because I'm paranoid)
Once a day, switch the production database to the staging database

All of the databases are read-only, once they're built. I very much like the idea of multiple versions of the database(s), but implementing this has me confused. Specifically:

Because several of the databases have foreign key constraints, I can't just drop each table in turn and recreate it. I don't see any choice but to delete the whole database and rebuild it, but then I'd need to recreate all of the grants, and I don't want to have to run something with database admin privileges like this on a daily basis.
I don't know how to swap the staging and production databases in a way that won't cost down-time.

I've looked for best practice in building databases within this general model, and come up dry. So, two questions:

Am I on the right track with this general design, and
What do I need to implement it?


texts: PostgreSQL extremely slow after upgrade from 9.3 to 9.4

texts:
We recently upgraded an Amazon RDS instance from PostgreSQL 9.3 to 9.4. There were no problems during the upgrade, and afterwards we ran VACUUM ANALYZE. Performance is now very slow across the board. Here's an example of a query that took a fraction of a second in 9.3:

EXPLAIN ANALYZE SELECT room_name, id FROM common_activityinstance WHERE started_by_id = 1370408 AND room_name IN ('robcv28', 'foobartest', 'noroster', 'jscode', 'cv28', 'johansencouple', 'lv426', 'johansenfamily', 'johansen') AND end_time IS NULL;
                                                                              QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on common_activityinstance  (cost=55.63..59.65 rows=1 width=13) (actual time=1.082..1.082 rows=0 loops=1)
   Recheck Cond: ((started_by_id = 1370408) AND (room_name = ANY ('{robcv28,foobartest,noroster,jscode,cv28,johansencouple,lv426,johansenfamily,johansen}'::text[])))
   Filter: (end_time IS NULL)
   Rows Removed by Filter: 925
   ->  BitmapAnd  (cost=55.63..55.63 rows=1 width=0) (actual time=0.349..0.349 rows=0 loops=1)
         ->  Bitmap Index Scan on common_activityinstance_started_by_id  (cost=0.00..5.53 rows=146 width=0) (actual time=0.122..0.122 rows=927 loops=1)
               Index Cond: (started_by_id = 1370408)
         ->  Bitmap Index Scan on common_activityinstance_room_name_c1f9997a_like  (cost=0.00..49.85 rows=1171 width=0) (actual time=0.171..0.171 rows=926 loops=1)
               Index Cond: (room_name = ANY ('{robcv28,foobartest,noroster,jscode,cv28,johansencouple,lv426,johansenfamily,johansen}'::text[]))
 Total runtime: 1.116 ms
(10 rows)

This same query is now much slower in 9.4:

EXPLAIN ANALYZE SELECT room_name, id FROM common_activityinstance WHERE started_by_id = 1370408 AND room_name IN ('robcv28', 'foobartest', 'noroster', 'jscode', 'cv28', 'johansencouple', 'lv426', 'johansenfamily', 'johansen') AND end_time IS NULL;
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on common_activityinstance  (cost=10157.60..14038.97 rows=45 width=36) (actual time=3923.011..3923.011 rows=0 loops=1)
   Recheck Cond: ((started_by_id = 1370408) AND (end_time IS NULL))
   Rows Removed by Index Recheck: 698
   Filter: (room_name = ANY ('{robcv28,foobartest,noroster,jscode,cv28,johansencouple,lv426,johansenfamily,johansen}'::text[]))
   Heap Blocks: exact=634
   ->  BitmapAnd  (cost=10157.60..10157.60 rows=991 width=0) (actual time=3917.278..3917.278 rows=0 loops=1)
         ->  Bitmap Index Scan on common_activityinstance_started_by_id  (cost=0.00..3282.60 rows=198155 width=0) (actual time=0.245..0.245 rows=927 loops=1)
               Index Cond: (started_by_id = 1370408)
         ->  Bitmap Index Scan on end_time_id  (cost=0.00..6874.73 rows=198155 width=0) (actual time=3915.565..3915.565 rows=1674860 loops=1)
               Index Cond: (end_time IS NULL)
 Planning time: 2.457 ms
 Execution time: 3923.065 ms
(12 rows)

I don't understand why the query plans are different. And this is just one example—another query was running for 15 minutes before I just gave up and stopped it.

I thought maybe there was a problem with the indexes, so I also did REINDEX DATABASE but it didn't help.

Any ideas?


texts: Flush Host Connection Blocked, but some connections allowed

texts:
We are running Percona MySQL 5.6.30.  We've had an issue where a PHP script on a different host was reporting an issue connecting to the database:

  Database may be down : SQLSTATE[HY000] [1129] Host 'x.x.x.101' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"

We did the flush hosts, and it resolved the issue.

When we went back to check the Percona Audit Logs for the same time, we see a number of connections at that same time, from that same host ('x.x.x.101').

I'm trying to understand how a host could be blocked, and not blocked, at the same time. :)  Any ideas?


texts: Why can't I use a CASE statement to see if a column exists and not SELECT from it?

texts:
Why does something like this not work?

SELECT
CASE
WHEN NULLIF(COL_LENGTH('Customers', 'Somecol'), '') IS NULL THEN NULL
ELSE Somecol
END AS MyTest
FROM Customers;

I am just checking if the column exists, however, SQL Server complains about Somecol not existing.  Is there an alternative to this in a single statement?


texts: Optimize INSERT performance with a UNIQUE constraint on md5(JSONB_COLUMN::text)

texts:
I have the following table and indices defined:

CREATE TABLE IF NOT EXISTS data (
    id serial,
    job_id bigint NOT NULL,
    payload jsonb NOT NULL,
    tags jsonb NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_data_content on data (md5(payload::text));
CREATE INDEX IF NOT EXISTS idx_data_tags ON data USING GIN (tags jsonb_path_ops);
CREATE INDEX IF NOT EXISTS idx_data_jid ON data USING btree (job_id);

In that table, I store many JSON blobs, which are associated with a job_id (also available in payload->>'jobId') and a time stamp (unix epoch) stored in payload->>'__time'. I want to avoid storing duplicate JSON blobs, so I'm using this method as a UNIQUE constraint. After a few hundred thousand rows in the DB, however, INSERT performance starts to decline, which does not happen without the UNIQUE constraint.

It is highly unlikely but possible to have two different JSON blobs with the same job_id and payload->>'__time'. Is there any way I can make use of that, for example, by defining a UNIQUE constraint that first checks the job_id, then payload->>'__time', and only if both match actually checks the md5(payload::text) index?

(Note, that the keys in my JSON blobs are always in the same order for the same documents.)


texts: How to set foreign key in table in one database that references a table in another database

texts:
Msg 1768, Level 16, State 0, Line 1
  Foreign key 'FK_id' references object 'dbo.student' which is not a user table.

  Msg 1750, Level 16, State 0, Line 1
  Could not create constraint. See previous errors.


texts: Partial (filtered) replication in PostgreSQL

texts:
My setup looks something like this: I have a primary server (Postgres 9.6) that continually scrapes a bunch of data from the web. I'd like to be able to stream a subset of tables or databases from that primary into a secondary instance so I can read the data from a web app. The web app needs to do standard web app things with its database (read/write tables for user accounts, perform authentication, etc.) and also be able to run queries on the replicated data.

Streaming replication with a hot standby looked like it would be up my alley, but a Reddit response from a few years ago seems to suggest that you can't filter for specific tables or write to the replicated cluster at all. From poking around on the Internet, my impression is that most people solve this problem with third-party tools like Bucardo and Slony – I'm open to that, but I'm looking for the simplest possible solution and adding a dedicated replication system to my stack seems unnecessarily intense. This SO question from last year had a promising lead using logical decoding, which makes me wonder if there is any new work in that direction. I'm also wondering if it would work to just run two instances of Postgres and bind them to different ports.

Are there any solutions here that don't require installing third-party tools? If a third-party tool is absolutely necessary, which one would have the lowest overhead for my purposes, from your experience?

Thanks in advance for the help, y'all. First question here and I'm thrilled to have access to your expertise.