SQL recovery model for ADFS deployment We have "AdfsConfiguration" and "AdfsArtifactStore" databases for our ADFS deployment.  I keep running into disk space issues due to the full and transaction log backups being taken on the server.

Right now, these two databases are in Full recovery model - but I wonder if I can just switch to Simple.  Are transaction log backups going to be important in these two databases?

I have searched a lot but can't really understand whether these DBs need actually need the trans log backups or if just a nightly full backup is good enough.

Also, I understand the general rules of thumb for deciding between the two models (SIMPLE or FULL recovery model for databases?) for I just can't come to and understand on what these ADFS databases are really doing to make the decision.

This is a screenshot of the "restore" GUI screen. it shows how I have log backups taken every 30 min:

This basically means our RPO is 30 min?  And then the full backup ensures ALL previous transactions are recoverable if they occurred before it was taken (in other words... I don't need to have log backup files from before the point in time in which the full backup was taken, right?)

I am not using compression (only available in Enterprise Edition right?)

I have thought about daily DIFFs and weekly FULLs. I honestly just seem to read online of a lot of people doing nightly FULLs. All instances I inherited were already set up to do nightly FULLs and I haven't seen any that do daily DIFFs (not that this means I shouldn't make a change of course).

Honestly I'm one of those "accidental" DBAs (am more of a developer). It's been overwhelming to actually try to get up to speed on everything I need to understand and put into practice.

When ADFS is installed using the built-in Windows Information Database (which runs an embedded SQL Server on the AD DC), these databases are installed in SIMPLE recovery model. You could possibly take that as an indication that it would be OK to run them in SIMPLE model in your own environment, but that is up to you.

In general, if you're taking a full SQL backup daily, you should be OK discarding any historical transaction log backups prior to the full backup to save on disk space. But this depends on your need to be able to restore the database to any point in time in the past. If your policy requires you to be able to restore the database to a specific point in time, then you'll likely need to hang onto all those full, diff, and tlog backups that comprise your RPO.


MySQL Cluster setup, receiving error "Specify node id" I am attempting to set up a MySQL Cluster.
I have the following setup:

One management node
Two data nodes
One SQL node

Below is the config.ini file:
[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=1     # Number of replicas

DataMemory=256M    # How much memory to allocate for data storage

IndexMemory=128M   # How much memory to allocate for index storage

#Directory for Data Node
DataDir=/var/lib/mysql-cluster

[ndb_mgmd]

# Management process options:
HostName=x.x.x.21

datadir=/var/lib/mysql-cluster

# Storage Nodes
[NDBD]

HostName=x.x.x.188
DataDir= /var/lib/mysql-cluster

[NDBD]

HostName=x.x.x.189
DataDir= /var/lib/mysql-cluster

# Setup node IDs for MySQL API-servers (clients of the cluster)
[mysqld]
# SQL node options:
hostname=x.x.x.22

Below is my.cnf file
[mysqld]

ndbcluster # run NDB storage engine

ndb-connectstring=X.X.X.21 # location of management server

[mysql_cluster]

ndb-connectstring=X.X.X.21 # location of management server

But when I start command ndb_mgmd -f /etc/config.ini --initial
I am getting the following error:

MySQL Cluster Management Server mysql-5.6.28 ndb-7.4.10
2018-05-09 14:29:08 [MgmtSrvr] ERROR    -- Could not determine which nodeid to use for this node. Specify it with --ndb-nodeid= on command line

Can anyone please help?

(Disclaimer: I'm no expert on NDB Cluster)

A few options to try:

Have you tried ndb_mgmd with the --no-nodeid-checks option?

Description on the ndb_mgmd documentation page

As the error message suggests, maybe try ndb_mgmd with the --ndb-nodeid= option? I'm guessing any small number might be valid. (Note: guessing!)

Description on the Common NDB Options documentation page


Need help figuring out a daisy chain issue We have a goofy process (don't we all?) that daisy chains (or cascades) on a table that needs flattening out for our data warehouse.

The field "alt_key" is an identifier field. It ends up being the parent to other alt_key and alt_key2 data.

What I have below is an example of how the data looks. This table is named MEMBER

The record where alt_key = 05526396 is our "master" record, and the other records daisy chain down alternating between the alt_key and alt_key2 fields as shown in the picture below:

[![Record Daisy Chain][1]][1]

In the end, the records need to look like this. alt_key will be named primary_pmi and alt_key2 will be named pmi. if the row is a parent,  alt_key and alt_key2 will be the same as will the primary_member_nbr and member_nbr fields. pmichain_id and pmi_order can be ignored:

[![Final Daisy Chain Transformation][2]][2]

Not sure how I can get to the end dataset. I've tried a recursive CTE but I think I have too many records to process. How else can I go about this? Records can have many children

A simple way to approach this would be to use a loop.

Start by identifying your records where alt_key does not show up in any alt_key2 values. Load those into a staging table with pmi_order 1, and mark the records as processed in the source table (add a bit field to track this). These are your parent records.

Now start your loop with an index (n) of 2, and keep going up by one for each loop. For each staging record with pmi_order = (n-1), find the unprocessed main table record whose alt_key value matches the staging alt_key2 value. This becomes your pmi_order 2 (i.e. current value of n) record for that primary_member_nbr. (Note, you will alternate matching staging.alt_key2 to main.alt_key and staging.alt_key to main.alt_key2 for each execution of the loop.)

After each loop completes, check to see if any unprocessed records remain in the main table. Once you have exhausted the main table, you are done.


Install and Configure Oracle 11g using c# program I need to create a windows application program using c# which will allow me to install oracle client 11g and also configure ODBC automatically with just one click, as my customers have limitations in technology and hence the installation and configuration is very difficult.

Is it possible and also if you could help with some code reference?

For Oracle client you can use Installation using Response Files.
Your C# program can generate response file and run installer with particular command line options.


SIZE is no valid input at this position error in MySQL 5.7 I want to create user-defined function including SELECT. I tried below SQL script.

DELIMITER //

CREATE FUNCTION SEARCH(TEXT CHAR(250))
    RETURNS VARCHAR(SIZE) # => ERROR: SIZE is no valid input at this position...
    NOT DETERMINISTIC
    BEGIN
        DECLARE result VARCHAR(SIZE);
            SET result = (
            select * from wp_posts
                where post_type='post'
                    AND post_status='publish'
                    AND (post_author='1' OR post_author='2')
                    AND NOT (post_author='3')
                    AND post_title REGEXP TEXT
                    )
                        ;
        RETURN result;
    END //

DELIMITER ;

SEARCH("python") ;

I do not why occur an error "SIZE is no valid input at this position". I missing something?

Function need a static definition of the returning type. Size of VARCHAR should be declared explicitly like VARCHAR(50).


MongoDB performance limits of mongo There are 100 data files, each with 60 fields and over 4 million records. There is a perl program that inserts the records or updates them based on an userdefined _id field. There is also a History collection that stores all values ever written for three fields. A replica set with two servers and an arbiter has been set up. Initially the files were loading into the MongoDB at 45 minutes per file. After around 20 files the speed has dropped considerably. The speed at this time is 20 hours per file. The servers have started slowing down badly. I am unable to use the logout command even quickly.

I have built indexes on the _id field with hashed indexing and for the History collection I have built indexes with id and date field. The number of records at this time in the collections are 4 million for the actual data collection and around 100 million for the History collection. I have two 17 GB RAM processor, of which only around 3.5 gigs are used as per the Mongostat res command. However since the data records are to be inserted date wise sequentially, I cannot exploit parallelism either.

The limits of mongo for the specific scenario have been reached? Is this slowdown to be expected? I have fsynced manually every now and then to ensure files are being written to disk. Is there some other diagnostics that I can run to better explain the situation? Is there a solution to this?

Thanks

Community wiki answer generated from comments on the question by Markus W Mahlberg:

You might want to use bulk operations during insert. In case not all your RAM is used, it is safe to assume that either the disks or the inserting program is the limiting factor. With bulk ops, you speed up things for both limiting factors. There is something seriously wrong. Unless your fields are very big, 200k inserts/h are a mere joke. You want to have your code peer reviewed and make sure that IO operations are efficient. See the production notes for further details on this.

There are bulk upserts: bulk.find({...}).update({...},{upsert:true}). Furthermore, you can do var bulk=db.collection.initializeOrderedBulkOp().


Mysql:Need to upload data to database, get duplicate key error - how can dup keys be ignored I'm uploading data to a db (mysql 5.1.42) from a dump file using mysql command line and get duplicate key error.

This is the format I use:

  /mnt/temp# mysql -u username -p -h url_of_db_master -D farm2  <
  db_dump_part_file_a

Hoe can I tell mysql to ignore duplicate keys?

Thanks !

You'll have to decide what you want to happen when a duplicate is found, because you can:

ignore it (insert ignore ...)
do something (insert ... on duplicate key update)

Since mysqldump usually dumps each table in a single insert statement, you can easily just add those keywords in the dump file.

See this SO post for a more in-depth discussion on the pros and cons of the two possiblities.


Find a date range from rows with consecutive dates and identical column values A similar question has been asked before but I've been having trouble adapting those answers to my question.

I want to find rows on consecutive dates, where another column has the same value, and return that as a single date range.

I have tried using OVER and PARTITION BY along with ROW_NUMBER() to get the results I need, but I'm not familiar enough with those concepts to figure out the correct output.

The system is Microsoft SQL Server 2014.

Given:

CREATE TABLE EXCEPTIONS
(
  ID    NUMERIC(18) NOT NULL,
  DATE  DATE        NOT NULL,
  TYPE  VARCHAR(20) NOT NULL,
  VALUE VARCHAR(20),
);

and the data:

INSERT INTO EXCEPTIONS VALUES
(17482, '2016-08-24', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-08-25', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-08-26', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-09-04', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-09-05', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-09-16', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-17', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-23', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-24', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-25', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-27', 'VOLUME_EXCEPTION', 'Low'),
(17482, '2016-09-28', 'VOLUME_EXCEPTION', 'Low')

The result of this query should be something like

StartDate  | EndDate    | Type                 | Value
2016-08-24 | 2016-08-26 | 'PRESSURE_EXCEPTION' | 'Over'
2016-09-04 | 2016-09-05 | 'PRESSURE_EXCEPTION' | 'Over'
2016-09-16 | 2016-09-17 | 'PRESSURE_EXCEPTION' | 'Under'
2016-09-23 | 2016-09-25 | 'PRESSURE_EXCEPTION' | 'Under'
2016-09-27 | 2016-09-28 | 'VOLUME_EXCEPTION'   | 'Low'

SQLFiddle available with more data

This is a 'Gaps and Islands' problem.   I took this example and tweaked it for your situation.

DECLARE @TestData TABLE (
    ID NUMERIC(18) NOT NULL
    ,[DATE] DATE NOT NULL
    ,[TYPE] VARCHAR(20) NOT NULL
    ,[VALUE] VARCHAR(20)
    )
INSERT INTO @TestData VALUES
(17482, '2016-08-24', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-08-25', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-08-26', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-09-04', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-09-05', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-09-16', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-17', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-23', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-24', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-25', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-27', 'VOLUME_EXCEPTION', 'Low'),
(17482, '2016-09-28', 'VOLUME_EXCEPTION', 'Low')
;

WITH mycte
AS (
    SELECT *
        ,DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY [Value] ORDER BY [Date]
                ), [Date]) AS grp
    FROM @TestData
    )
--select * from mycte       --Uncomment to see the data from mycte
SELECT min([Date]) AS [From]
    ,max([Date]) AS [To]
    ,[Type]
    ,[value]
FROM mycte
GROUP BY [Type]
    ,[value]
    ,grp
ORDER BY [From];

| From       | To         | Type               | value |
|------------|------------|--------------------|-------|
| 2016-08-24 | 2016-08-26 | PRESSURE_EXCEPTION | Over  |
| 2016-09-04 | 2016-09-05 | PRESSURE_EXCEPTION | Over  |
| 2016-09-16 | 2016-09-17 | PRESSURE_EXCEPTION | Under |
| 2016-09-23 | 2016-09-25 | PRESSURE_EXCEPTION | Under |
| 2016-09-27 | 2016-09-28 | VOLUME_EXCEPTION   | Low   |

The OVER/PARTITION logic is basically assigning each row to a 'group' so you can pick out the MIN and MAX from each group.  If you un-comment the
select * from mycte (I added an order by date) and run the script from that part to the top, you'll see that each row gets assigned to a group.

Look at the first group of rows assigned to '2016-08-23'.  The ROW_NUMBER for date '2016-08-24' is 1, so the DATEADD subtracts 1 from '2016-08-24' to put that row in group '2016-08-23'.  The ROW_NUMBER for date '2016-08-25' is 2, so the DATEADD subtracts 2 from '2016-08-25' to put it in the same group as row number 1 and so on.

| ID    | DATE       | TYPE               | VALUE | grp        |
|-------|------------|--------------------|-------|------------|
| 17482 | 2016-08-24 | PRESSURE_EXCEPTION | Over  | 2016-08-23 |
| 17482 | 2016-08-25 | PRESSURE_EXCEPTION | Over  | 2016-08-23 |
| 17482 | 2016-08-26 | PRESSURE_EXCEPTION | Over  | 2016-08-23 |
| 17482 | 2016-09-04 | PRESSURE_EXCEPTION | Over  | 2016-08-31 |
| 17482 | 2016-09-05 | PRESSURE_EXCEPTION | Over  | 2016-08-31 |
| 17482 | 2016-09-16 | PRESSURE_EXCEPTION | Under | 2016-09-15 |
| 17482 | 2016-09-17 | PRESSURE_EXCEPTION | Under | 2016-09-15 |
| 17482 | 2016-09-23 | PRESSURE_EXCEPTION | Under | 2016-09-20 |
| 17482 | 2016-09-24 | PRESSURE_EXCEPTION | Under | 2016-09-20 |
| 17482 | 2016-09-25 | PRESSURE_EXCEPTION | Under | 2016-09-20 |
| 17482 | 2016-09-27 | VOLUME_EXCEPTION   | Low   | 2016-09-26 |
| 17482 | 2016-09-28 | VOLUME_EXCEPTION   | Low   | 2016-09-26 |

Now, it's just a matter of pulling the MIN and MAX from each group.


What is the Difference between IN and Not IN performance wise? I am new to database.I am having doubt that what is the difference between IN and NOT IN performance wise.

When I am using IN, takes less time for the same query(Logically same query) when I use NOT IN.


What is wrong with this a MySQL 5.6 procedure definition? Use a variable for table name Just can't understand why it's throwing this error?

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

drop procedure if exists dividir_tabla_por_usuario;

delimiter //
create procedure dividir_tabla_por_usuario(tabla varchar(100), columna_usuario varchar(100), columna_fecha varchar(100))
begin
  set @i = 0;
  set @q1 = concat('select count(*) from (select distinct ', columna_usuario ,' from ', tabla , ' order by ', columna_usuario ,' asc) as t1 into @c');
  prepare p1 from @q;
  execute p1;
while @i <= @c do
  set @r = 0;
  set @q2 = concat('select * from ', tabla , ' where ', columna_usuario ,' = (select ', columna_usuario ,' from (select @r:=@r +1 as ord, ', columna_usuario ,' from (select distinct ', columna_usuario ,' from ', tabla , 'order by ', columna_usuario ,' asc) as t2) as t3 where ord = ', @i ,' ) order by ', columna_fecha ,' asc limit 30000');
  prepare p2 from @q2;
  execute p2;
  set @i := @i + 1;
end while;
end//
delimiter ;

call dividir_tabla_por_usuario('mytable', 'mycolumn_1', 'mycolumn_2');

Typos, always typos.

Here is the final working code:

drop procedure if exists dividir_tabla_por_usuario;

delimiter //
create procedure dividir_tabla_por_usuario(tabla varchar(100), columna_usuario varchar(100), columna_fecha varchar(100))
begin
  set @i = 0;
  set @q1 = concat('select count(*) from (select distinct ', columna_usuario ,' from ', tabla , ' order by ', columna_usuario ,' asc) as t1 into @c');
  prepare p1 from @q1;
  execute p1;
while @i <= @c do
  set @r = 0;
  set @q2 = concat('(select distinct ', columna_usuario ,' from ', tabla , ' order by ', columna_usuario ,' asc) as t2)');
  set @q3 = concat('(select @r:=@r +1 as ord, ', columna_usuario ,' from ',@q2, ' as t3 where ord = ', @i ,' )');
  set @q4 = concat('(select ', columna_usuario ,' from ', @q3);
  set @q5 = concat('select * from ', tabla , ' where ', columna_usuario ,' = ', @q4 ,' order by ', columna_fecha ,' asc limit 30000');
  prepare p2 from @q5;
  execute p2;
  set @i := @i + 1;
end while;
end//
delimiter ;

call dividir_tabla_por_usuario('mytable', 'mycolumn1', 'mycolumn2');


Always On Availability Group Failover Issue I'm testing a PowerShell Disaster Recovery script and trying to fail over to an off-site AG DR (asynch) replica with this command

ALTER AVAILABILITY GROUP [MyAG] FORCE_FAILOVER_ALLOW_DATA_LOSS

On several clusters this works fine but on a couple of other ones it usually fails (but not always) with the error:

  Failed to move a Windows Server Failover Clustering (WSFC) group to
  the local node (Error code 5023).  The WSFC service may not be running
  or may not be accessible in its current state, or the specified
  cluster group or node handle is invalid.  For information about this
  error code, see "System Error Codes" in the Windows Development
  documentation.

  Failed to designate the local availability replica of
  availability group 'MyAG' as the primary replica.  The operation
  encountered SQL Server error 41018 and has been terminated.  Check the
  preceding error and the SQL Server error log for more details about
  the error and corrective actions.

We have to force the quorum simulating the situation where the async DR replica can't communicate with the other replicas: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/force-a-wsfc-cluster-to-start-without-a-quorum?view=sql-server-2017

A comparison of the clusters that work vs. those that don't with Get-Cluster and Get-ClusterGroup doesn't reveal any major differences.

This failover has worked successfully on some clusters but fails 99% of the time on two of the clusters. It has worked on these two clusters, but rarely. One of the common issues I found from a Google search is permissions for NT Authority\System (see https://dataginger.com/2014/10/28/sql-server-failed-to-bring-availability-group-availability-group-name-online/), which I confirmed it has.

I ran a trace and see the same errors and no other clues in the trace.

If nothing turns up I may have to open a Microsoft ticket to engage their support.

Any thoughts?

Thanks for your help!

This error may happen if your SQL Server service is running under user who does not have the needed permission on your servers or the account is locked. try to use another account and make sure account is unlocked.

If that did not fix the issue then you need to edit your regisery (make sure you take a backup first)

open start menu,write regedit then press ok
navigate to HKEY_LOCAL_MACHINE\Cluster\Resources\<GUID>\Parameters
Create the following registry values in the

Parameters registry key:

For a default instance of SQL Server:
InstanceName
Value Name: InstanceName
Value Type: REG_SZ

Value Data: MSSQLSERVER

VirtualServerName

Value Name: VirtualServerName
Value Type: REG_SZ

Value Data:
For a named instance of SQL Server:

Value Name: InstanceName
Value Type: REG_SZ

Value Data:
VirtualServerName

Value Name: VirtualServerName
Value Type: REG_SZ
Value Data:

4- quit registery editor


How to optimize a query that's running slow on Nested Loops (Inner Join) TL;DR

Since this question keeps getting views, I'll summarize it here so newcomers don't have to suffer the history:

JOIN table t ON t.member = @value1 OR t.member = @value2 -- this is slow as hell
JOIN table t ON t.member = COALESCE(@value1, @value2)    -- this is blazing fast
-- Note that here if @value1 has a value, @value2 is NULL, and vice versa

I realize this might not be everyone's problem, but by highlighting the sensitivity of the ON clauses, it might help you look in the right direction. In any case the original text is here for future anthropologists:

Original text

Consider the following simple query (only 3 tables involved)

    SELECT

        l.sku_id AS ProductId,
        l.is_primary AS IsPrimary,
        v1.category_name AS Category1,
        v2.category_name AS Category2,
        v3.category_name AS Category3,
        v4.category_name AS Category4,
        v5.category_name AS Category5

    FROM category c4
    JOIN category_voc v4 ON v4.category_id = c4.category_id and v4.language_code = 'en'

    JOIN category c3 ON c3.category_id = c4.parent_category_id
    JOIN category_voc v3 ON v3.category_id = c3.category_id and v3.language_code = 'en'

    JOIN category c2 ON c2.category_id = c3.category_id
    JOIN category_voc v2 ON v2.category_id = c2.category_id and v2.language_code = 'en'

    JOIN category c1 ON c1.category_id = c2.parent_category_id
    JOIN category_voc v1 ON v1.category_id = c1.category_id and v1.language_code = 'en'

    LEFT OUTER JOIN category c5 ON c5.parent_category_id = c4.category_id
    LEFT OUTER JOIN category_voc v5 ON v5.category_id = c5.category_id and v5.language_code = @lang

    JOIN category_link l on l.sku_id IN (SELECT value FROM #Ids) AND
    (
        l.category_id = c4.category_id OR
        l.category_id = c5.category_id
    )

    WHERE c4.[level] = 4 AND c4.version_id = 5

This is a pretty simple query, the only confusing part is the last category join, it's this way because category level 5 might or might not exist. At the end of the query I am looking for category info per product ID (SKU ID), and the that's where the very large table category_link comes in. Finally, the table #Ids is just a temp table containing 10'000 Ids.

When executed, I get the following actual execution plan:

As you can see, almost 90% of the time is spent in the Nested Loops (Inner Join). Here's extra information on those Nested Loops:

Note that the table names don't match exactly because I edited the query table names for readability, but it's pretty easy to match (ads_alt_category = category). Is there any way to optimize this query? Also note that in production, the temp table #Ids doesn't exist, it's a Table Valued Parameter of the same 10'000 Ids passed on to the Stored Procedure.

Additional info:

category indices on category_id and parent_category_id
category_voc index on category_id, language_code
category_link index on sku_id, category_id

Edit (solved)

As pointed out by the accepted answer, the problem was the OR clause in the category_link JOIN. However, the code suggested in the accepted answer is very slow, slower even than the original code. A much faster and also much cleaner solution is simply to replace the current JOIN condition with the following:

JOIN category_link l on l.sku_id IN (SELECT value FROM @p1) AND l.category_id = COALESCE(c5.category_id, c4.category_id)

This minute tweak is the fastest solution, tested against the double join from the accepted answer and also tested against the CROSS APPLY as suggested by valverij.

The problem appears to be in this part of the code:

JOIN category_link l on l.sku_id IN (SELECT value FROM #Ids) AND
(
    l.category_id = c4.category_id OR
    l.category_id = c5.category_id
)

or in join conditions is always suspicious.  One suggestion is to split this into two joins:

JOIN category_link l1 on l1.sku_id in (SELECT value FROM #Ids) and l1.category_id = cr.category_id
left outer join
category_link l1 on l2.sku_id in (SELECT value FROM #Ids) and l2.category_id = cr.category_id

You then have to modify the rest of the query to handle this . . . coalesce(l1.sku_id, l2.sku_id) for instance in the select clause.


Why is it bad practice to install SQL Server to the root of a drive When I install SQL Server into the root of my drive, for example D:\, I get an error when I run a database deployer from a third party piece of software.

But when I move my SQL Server instance to D:\SQL\ (move it to a folder within the drive) then installation works perfectly.

My question doesn't regard the database deployer but more into the reason why SQL would have an issue with being installed into the root of a drive. Is this bad practice? Why shouldn't I install SQL Server to the root of my drive?

Yes SQl server installation is likely to fail if you are trying to install it on root of a drive which is never advisable and has security implications. Actually it is related to permission setting on specific folders. As per this connect item

  Based on further investigation this issue is due to the fact that SQL tightens the ACL for default data/log directories to exclude broad access for all authenticated users. If the drive root is chosen as the default data/log location then SQL will tighten the root ACL and thus effectively locking down the whole volume. The recommended course of action is to always use a subdirectory to store SQL data/log files.

So there seems to be problem with ACL's when installing on root of a drive in your case.


MS SQL Server 2012 reusing numbers I have a very high transaction database
I have multiple sequences to unique generate numbers for different outlets.

One of the outlets started receiving errors because i have a unique constraint on a table to prevent duplicates.
The sequence that generates the numbers rolled back more than 10 numbers.

I can afford for this to happen again because it stops sales and I currently have over 70 sequences and going to added more as the months go by.

Has anyone ever experienced this before and how can i stop this from happening in the future ?

any help would be appreciated .. thanks

There is a known issue in some circumstances.

As far as I can see, you need to use NO CACHE so numbers cannot be reused


Upgrade SQL Server 2008 R2 to SQL Server 2012 and then set up data replication to SQL Server 2016 Currently I have a Windows 2008 server with SQL Server 2008 R2 and I want set up a data replication to a Windows 2016 Server with SQL Server 2016.

I am aware that the aforementioned is not possible because replication is only supported if each SQL Server endpoint is within two major versions of the current version of SQL Server.

If I upgrade SQL Server 2008 R2 to SQL Server 2012 on the Windows 2008 server then will I be able to set up replication to the Windows 2016 Server with SQL Server 2016?

Please help me on this.

Yes, you'll be able to configure Transactional Replication with 2012 as the Publisher and 2016 as the Subscriber.

However, there are some changes made in replication procedures (that get created at subscribers to replicate transactions like INS, UPD & DEL) in 2016 ,that you'll have to correct manually. I have done it earlier but now migrated to AlwaysOn according to requirement.


Where clause over multiple rows, GROUP BY I have the following table structure

AccountID   Property            Value
123456      Status              Active
123456      City                Los Angeles
123456      RegistrationDate    2018-05-11 11:30:14.000
543210      Status              Active
543210      City                Las Vegas
543210      RegistrationDate    2018-05-11 11:13:14.000
888888      Status              Inactive
888888      City                Toronto
888888      RegistrationDate    2015-05-12 11:13:14.000

I want to be able to Select all rows where Property == Status and Value == Active And Property == RegistrationDate and Value == 2018-05-11 11:30:14.000

Im figuring I need to do some kind of GROUP BY and then select on that aggregation but I can't get my head around it(my sql is really rusty).

I want the following output for the above "query"

AccountID    Status    RegistrationDate
123456       Active    2018-05-11 11:30:14.000
543210       Active    2018-05-11 11:30:14.000

If all the columns had existed on the same row I would just write something like this:

SELECT AccountID FROM Property
WHERE Status = 'Active'
AND RegistrationDate = '2018-05-11 11:30:14.000'

The design pattern is called EAV (entity-attribute-value). It's usual to need either GROUP BY or queries with multiple joins with this design:

SELECT
      a.AccountID
FROM
      tbl AS a
      JOIN tbl AS b
      ON a.AccountID = b.AccountID
WHERE
      a.Property = 'Status'
  AND a.Value    = 'Active'

  AND b.Property = 'RegistrationDate'
  AND b.Value    = '2018-05-11 11:30:14.000' ;


How to filter multiple many to many relationship based on multiple tags? I've a scheme of two main tables: problem and tag and a relation (which is a many to many connector) table: problem_tags which excerpt of them is like:

Problem table:

----+------------------------------------+--------
 id |                name                | rating
----+------------------------------------+--------
  1 | Special Permutation                |   1600
  2 | Binary String Reconstruction       |   1500
  3 | Special Elements                   |   1500
  4 | Alice, Bob and Candies             |   1300
  5 | K-th Not Divisible by n            |   1200
  6 | Same Parity Summands               |   1200
  7 | Sum of Round Numbers               |    800
  8 | Skier                              |   1400
  9 | Square?                            |    900

Tag table:

 id |           name
----+---------------------------
  1 | constructive algorithms
  2 | dfs and similar
  3 | math
  4 | brute force
  5 | implementation
  6 | two pointers
  7 | binary search
  8 | data structures

Problem tags table:

 problem_id | tag_id
------------+--------
          1 |      1
          2 |      1
          2 |      2
          2 |      3
          3 |      4
          3 |      5
          3 |      6
          4 |      5
          5 |      3
          5 |      7

My question is how can I filter out problems based on multiple tags, i.e. all problems that are tagged math and binary search and brute force; or all problems that are tagged math but not constructive algorithms; or for a more complex one all problems that are only tagged with math and implementation and nothing else?

Currently I've come up with something like this:

Find all problem's ids that are tagged math (joining tag and problem_tags table)
Find all problem's ids that are tagged binary search
Find all problem's ids that are tagged brute force
Get intersection of all above ids
Select problems where their ids is in the above intersection

But my solution lacks when it reaches the second example (only tagged with selected tags) and I think it's not the most optimal and SQL-ish way for doing it.

I had just asked that on a different forum.  The best solution for a 3rd Normal Form schema design was to select all that is needed in the linking table and compare the Cardinality (count) of the matching results.

The other forum was for Oracle.  The answer you seek is the PostgreSQL version of this post: https://community.oracle.com/message/15613817#15613817

-- copy paste from original post
-- Oracle version
select *
from my_recipes rp
where rp.recipe_id  in
(
    select tg.recipe_id
    from my_recipe_tags tg
    where tg.tag_id in    (
                            -- expands string '1:2:3' into a table w/ 3 row
                            select to_number(regexp_substr(:p_search_tags,'[^:]+', 1, level) )
                            from  dual
                            connect by regexp_substr(:p_search_tags, '[^:]+', 1, level) is not null
                        )
    group by tg.recipe_id
                      -- counts cardinality of Tags in search
    having count(*) = regexp_count(:p_search_tags, '[^:]+')
) ;

Untested (needs to be fixed)

select *
from PROBLEMS p
where p.id in (
    select pt.problem_id
    from PROBLEM_TAGS pt
        join TAGS t on pt.tag_id = t.tag_id
    where t.name in ('math','binary search', 'brute force') -- should be from a CTE
    group by pt.problem_id
    having count(*) = 3 -- count(*) from CTE
)

For the 2nd version, add AND p.id NOT IN clause.

For the 3rd version add a filter that ensures pt.problem_id has the exact cardinality of the queried tags.

and p.id in (
    select pt.problem_id
    from PROBLEM_TAGS pt
    group by pt.problem_id
    having count(*) = 2 -- you have two tags for 3rd one
                        -- you should pull Cardinality from CTE also
)


Query All Databases Listed In Table I have been using a cursor to get my results, but it is taking an extended period of time to return my result set.  I am hoping to find a way to QUICKLY get the results returned (if at all possible).  Here is my data structure with garbage DDL.

1st problem is each employee has there own table.  I understand this is a major design flaw, and we are quickly working to remedy this, but I need an alternative until all elements have been updated.  All 3 tables geoffrey, jorge, mitch all have the same structure, and I have attached sample DDL for geoffrey, & jorge and below is a sample query that shows what I need to query from each of the two tables.

Greatly appreciate any and all help on how to achieve this result!

Create Table #ReviewDB
(
  [TableName] varchar(500)
  ,[ServerDBTable] varchar(MAX)
)
Insert Into #ReviewDB VALUES
('geoffrey', 'beans.franks.dbo.geoffrey')
,('jorge', 'smallpox.virus.dbo.jorge')
,('mitch', 'mosquito.insect.dbo.mitch')
Create Table #geoffrey
(
  recordID int IDENTITY(1,1) PRIMARY KEY
  ,firstname varchar(500)
  ,lastname varchar(500)
  ,address varchar(500)
  ,city varchar(500)
  ,state varchar(500)
  ,zip varchar(500)
  ,phone varchar(500)
  ,itemsold varchar(500)
  ,saledate datetime
  ,managerapproved varchar(500)
)
Insert Into #geoffrey VALUES
('manny', 'ramirez', '1111111 aoaswdfrljkasdf ', 'topaz', 'mn', '1111', '9995552222', 'sofa', '01/21/2016', '')
,('hi', 'ho', '2323 aoaswdfrljkasdf ', 'topaz', 'mn', '1111', '6662229888', 'chair', '02/21/2016', '')
,('ee', 'aa', '4646 aoaswdfrljkasdf ', 'topaz', 'mn', '1111', '3332221919', 'ottoman', '01/21/2016', '')
Create Table #jorge
(
  recordID int IDENTITY(1,1) PRIMARY KEY
  ,firstname varchar(500)
  ,lastname varchar(500)
  ,address varchar(500)
  ,city varchar(500)
  ,state varchar(500)
  ,zip varchar(500)
  ,phone varchar(500)
  ,itemsold varchar(500)
  ,saledate datetime
  ,managerapproved varchar(500)
)
Insert Into #jorge VALUES
('aa', 'bb', '1111111 c street ', 'holt', 'tn', '2222', '1113334444', 'sofa', '01/21/2016', '')
,('cc', 'ddo', '2323 b street ', 'holt', 'tn', '2222', '8889997788', 'chair', '02/21/2016', '')
,('mm', 'rr', '4646 e street ', 'holt', 'tn', '2222', '8889998877', 'ottoman', '03/21/2016', '')
Create Table #managerrevieweddata
(
  ID int IDENTITY(1,1) PRIMARY KEY
  ,recordID int
  ,dbname varchar(500)
  ,managernotes text
  ,regionalmanager varchar(500)
  ,districtmanager varchar(500)
  ,incentiveitem varchar(500)
)
Insert Into #managerrevieweddata VALUES
('1', 'geoffrey', 'Valid sale, remind to offer upsell next time.', 'Regional Manager', 'District Manager', 'No')
,('2', 'jorge', 'Review with salesman', 'Regional Manager', 'District Manager', 'NO')

And here is a query that shows what I want my desired output to be.

  I need a way to query each serverdbtable in #ReviewDB and return the below select statement

Declare @saledate datetime
Set @saledate = '03/21/2016'

Select a.firstname, a.lastname, a.address, a.city,
a.state, a.zip, a.phone,   a.itemsold, a.saledate,
b.dbname, b.managernotes, b.regionalmanager,
b.districtmanager, b.incentiveitem
FROM #ReviewDB.ServerDBTable a
INNER JOIN #managerrevieweddata b
ON a.recordID = b.recordID
and a.saledate = @saledate

It is unlikely that the cursor you are using is the cause of the delay in returning results.  Most likely, the delay is due to running many select statements over possibly a large amount of data.  Having said that, I likely wouldn't use the DECLARE CURSOR cur FOR ... construct to solve this issue.

I generally approach this class of problem by generating a single dynamic SQL statement, then execute that statement using either sp_executesql() or EXEC (), as in:

IF COALESCE(OBJECT_ID('dbo.TableList'), 0) <> 0
DROP TABLE dbo.TableList;

CREATE TABLE dbo.TableList
(
    DatabaseName SYSNAME NOT NULL
    , SchemaName SYSNAME NOT NULL
    , TableName SYSNAME NOT NULL
);

INSERT INTO dbo.TableList(DatabaseName, SchemaName, TableName)
VALUES ('Database1', 'Schema1', 'Table1')
    ,  ('Database2', 'Schema2', 'Table2');

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = '';

SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) END
    + 'SELECT ''' + tl.DatabaseName
        + '.' + tl.SchemaName
        + '.' + tl.TableName
        + ''', * '
    + 'FROM ' + QUOTENAME(tl.DatabaseName)
        + '.' + QUOTENAME(tl.SchemaName)
        + '.' + QUOTENAME(tl.TableName) + ';'
FROM dbo.TableList tl;

PRINT (@cmd);
EXEC sp_executesql @cmd;

This constructs statements required from the TableList table, appending each statement into a variable.  The variable contents are then executed using the sp_executesql system stored procedure.  This methodology can be used for a multitude of problems where you need to generate T-SQL statements based on the results of some query.

The output will be a single result set for each row in TableList - you probably don't want to do this for more than about 50 or 100 tables since the output might become unmanageable.  If you have a lot of tables, you may want to consider inserting the output into a temporary table, then running a single select statement against that table.

If the structure of the tables listed in the TableName column are identical, you can use a UNION ALL to join the output into a single resultset:

SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10)
    + 'UNION ALL' + CHAR(13) + CHAR(10) END
    + 'SELECT ''' + tl.DatabaseName
        + '.' + tl.SchemaName
        + '.' + tl.TableName
        + ''', * '
    + 'FROM ' + QUOTENAME(tl.DatabaseName)
        + '.' + QUOTENAME(tl.SchemaName)
        + '.' + QUOTENAME(tl.TableName) + ';'
FROM dbo.TableList tl;

Be aware that UNION ALL is generally much faster than UNION since the ALL option doesn't need to run a DISTINCT operator over the data.


Why mysql not using the index here? I created a test table to try and test of course if index is working with a help of EXPLAIN EXTENDED command, here is the table:

id | first_id | second_id | details

and have 7 rows and data given are:

id | first_id | second_id | details
1  | 1        |           | hello
2  | 2        |           | hello2
3  | 1        |           | hello3
4  |          | 1         | hello4
5  |          | 2         | hello5
6  | 1        |           | hello6
7  |          | 1         | hello7

And my index is ALTER TABLE test ADD INDEX test (``first_id``) (Don't know how to add the back tick properly)

Then I tried to run this query:

EXPLAIN EXTENDED SELECT * FROM `test` WHERE first_id = 1

And got this result:

From what I understand here, How can I tell if an index is being used to sort in MySQL?, mysql does not using the index.

Here is what I think the reason why mysql does not used the index.

The rows are too short
I made a mistake

If #1 is true, then how many rows do I need until mysql use the index? And if #2 is true, where I go wrong?

The percentage varies based on a lot of factors; I say "about 20%".

For tiny tables, you would be hard pressed to see any performance difference.

Usually the Optimizer "does the right thing" in deciding between using the index (and bouncing between the index's BTree and the data BTree) versus simply scanning the entire table (and throwing out unwanted rows).

Bottom line:  Don't worry about it.


Connecting to ReadOnly Secondary Replica of SQL Server via the Listener by Default We have the ApplicationIntent=ReadOnly properly routing our users to the Secondary ReadOnly replica.  Is there a way to make this the default behavior?

Apparently the default behavior seems to be ReadWrite, and if you don't specify an ApplicationIntent it will redirect to the Primary server.

The Availability Group Listener is (per the documentation), simply "a Domain Name System (DNS) listener name, listener port designation, and one or more IP addresses." It becomes a resource in the Windows Server Failover Cluster (WSFC), and is used for routing traffic to the appropriate Availability Group (AG) node. However, it is ultimately just a DNS Name and IP.

When you connect to the Listener, it will always route to the primary (read/write) replica. The only way to have traffic routed to a secondary (read-only) replica is to explicitly specify ApplicationIntent=ReadOnly. There is no way to change the default behavior on the Listener to route traffic to a read-only secondary without specifying ApplicationIntent.

If you want users to connect to a read-only replica without explicitly specifying the ApplicationIntent, then those users would need to connect directly to the secondary server.
Note that if the user connects directly to the secondary server, a failover could result in that server becoming primary, and thus, no longer read-only.

In your scenario, if you have automatic failover between DB01 & DB02, and DB03 will "never" be primary, and you also want users to always connect to DB03, then you should simply direct users to connect directly to DB03.

However, if you want to take advantage of Read-Only routing so that the server names are obscured, and the specific secondary node to which a user is connected is transparent to them, then your only choice is to connect to the AG Listener, and specify ApplicationIntent=ReadOnly.


Post Mortem Analysis: PostgreSQL replication failed We have a PostgreSQL 9.4.9 production server which was replicating to a slave instance, but today I discovered that the instance is out of sync!

The obvious actions would be to recreate the slave, setup metrics and proper alarms for the replication activity, so we can effectively monitor the sync status between master and slave nodes.

But, as the sync failed, I'd like to first diagnose the problem and try to identify the root cause of it, as this would be the second time this happens in about 6 months.

Question: How to diagnose what failed in the replication process so it can be done in a better way this time?

Version specifics:

PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

From slave node, at /var/log/postgresql/postgresql-9.4-main.log I can see:

2017-07-18 19:43:55 UTC [12816-1] LOG:  started streaming WAL from primary at 125D/68000000 on timeline 1
2017-07-18 19:43:55 UTC [12816-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000125D00000068 has already been removed

2017-07-18 19:44:00 UTC [12817-1] LOG:  started streaming WAL from primary at 125D/68000000 on timeline 1
2017-07-18 19:44:00 UTC [12817-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000125D00000068 has already been removed

2017-07-18 19:44:05 UTC [12821-1] LOG:  started streaming WAL from primary at 125D/68000000 on timeline 1
2017-07-18 19:44:05 UTC [12821-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000125D00000068 has already been removed

2017-07-18 19:44:10 UTC [12825-1] LOG:  started streaming WAL from primary at 125D/68000000 on timeline 1
2017-07-18 19:44:10 UTC [12825-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000125D00000068 has already been removed

2017-07-18 19:44:15 UTC [12826-1] LOG:  started streaming WAL from primary at 125D/68000000 on timeline 1
2017-07-18 19:44:15 UTC [12826-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000125D00000068 has already been removed

New Question: How can I see backward on where the actual problem appeared?

Master postgresql.conf: https://pastebin.com/NJX5ku6m

Slave postgresql.conf: https://pastebin.com/CUZcyazC

Slave recovery.conf:

standby_mode = on
primary_conninfo = 'host=10.1.1.65 port=5432 user=replicador password=replicador'

Based on this I'd say you didn't have enough wal_keep_segments on the master, weren't using a replication slot, and either had hot_standby_feedback off or had the connection drop for long enough for the master to remove needed WAL.

And you are presumably not using WAL archiving (archive_command on master, restore_command on replica) as fallback.

So the master removed transaction logs the standby needed.

You'll need to re-create the standby. Then either:

Set the standby to use a replication slot and enable hot_standby_feedback; or
enable archive_command and restore_command


INSERT inside PL/pgSQL function gives different result In PostgreSQL 9.4.1 I have created a custom type my_test_type and have found that doing a basic INSERT with NULL::my_test_type gives an expected result, but with NULL::my_test_type as a function argument and using the exact same query, I get an unexpected result:

CREATE TYPE my_test_type as (part1 text, part2 text);
CREATE TABLE my_test_table (id serial, test_col my_test_type);
CREATE FUNCTION my_test_table_insert (test_arg my_test_type DEFAULT NULL)
  RETURNS VOID LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO my_test_table (test_col) VALUES (test_arg);
END; $$;

-- gives null as expected
SELECT NULL::my_test_type;

-- test_col value will be null as expected
INSERT INTO my_test_table (test_col) VALUES (NULL);

-- this performs the exact same query above but in a function,
-- but test_col value shows '(,)', which is NOT expected
SELECT my_test_table_insert(NULL);

-- this is what seems to happening in the function, but I don't understand why
INSERT INTO my_test_table(test_col) VALUES (ROW(NULL, NULL)::my_test_type);

\pset null 'NULL'
SELECT *, (test_col).part1, (test_col).part2, test_col IS NULL AS is_null FROM my_test_table;
┌────┬──────────┬───────┬───────┬─────────┐
│ id │ test_col │ part1 │ part2 │ is_null │
├────┼──────────┼───────┼───────┼─────────┤
│  1 │ NULL     │ NULL  │ NULL  │ t       │
│  2 │ (,)      │ NULL  │ NULL  │ t       │
│  3 │ (,)      │ NULL  │ NULL  │ t       │
└────┴──────────┴───────┴───────┴─────────┘
(3 rows)

Even though everything tests as null, why does the result from the INSERT inside the function show differently in the table?

Edit:
The problem can be simplified:

VALUES (NULL::my_test_type), (ROW(NULL, NULL)::my_test_type);
┌─────────┐
│ column1 │
├─────────┤
│ NULL    │
│ (,)     │
└─────────┘
(2 rows)

Merlin Moncure from the PostgreSQL General mailing list has an answer:

  The SQL standard mandates that rows containing all null
  values satisfy 'IS NULL = true'.  However, postgres internally has
  nullibitily of container types that is distinct from their contents.
  I personally find this to be a good thing for various reasons but the
  facts are that postgres has some historical baggage in this area that
  crashed into the standard.


Synchronize SQL server databases We have the following scenario:

We have several SQL serves instances in distributed geographic areas (distributed buildings) (in a city or maybe globally)

All the SQL servers run one snapshot of a database

The databases should be synchronized (maybe with peer to peer replication or other alternatives)

All computers in a building connected to the SQL server that is located in the building

Connection between buildings maybe lost (for a day for example) and after servers reconnected data should be synced and all the SQL servers should have same data (and we should taking care of conflicts absolutely)

How we can handle this scenario?

(I am not a SQL server expert so it would be nice of you to explain it in more details)

Sounds to me like Merge Replication, considering the offline aspect. You generally have a "master" which the "clients" sync to. https://docs.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication?view=sql-server-2017
I would question the need to distribute the database in the first place. Makes things soooo much more complicated, so carefully weigh the gain you get from distributing the database to the headaches you get from it.


How is calculated fill-factor percentage related to size of database? I have rebuilt all indexes in my database setting fill-factor of 95 (5% free space) using a maintenance plan. After the reindex the database is almost doubled in size - reported free space is 42%.

How is calculated fill-factor related to the size of the database?

Maybe something is wrong with reindex; what causes so much growth of size?

Some database info after reindex:

Size (MB):            164 983.625
Data Space Used (KB):      82 907 896
Index Space Used (KB):     14 073 320
Space Available (KB):      71 879 024

Generated T-SQL for maintenance plan for one table:

ALTER INDEX [Table1_Index1] ON [dbo].[Table1] REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
   IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
   ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95)

Additional informations:

Result of sp_spaceused 'dbo.BigTable'

name        rows        reserved    data        index_size  unused
BigTable    58028080    72824296 KB 68393936 KB 4424000 KB  6360 KB

What you see is correct.

When you rebuild an index first the new index is built and only then the old index is dropped. And if you don't use

SORT_IN_TEMPDB = ON

the space that server needs to make a sort is allocated within your db

Your db has now empty space, it's not space reserved to any object, it's just free space
(space that was freed after temp objects needed to do sort were dropped)

So your indexes are not empty on 42%, it's your db that is empty on 42% and if you do ashrink of data file (I don't recommend it) your db will occupy only 82Gb


Remote harden of transaction 'CREATE INDEX' - when moving an index to a different filegroup Remote harden of transaction 'CREATE INDEX' (ID 0x0000000082a3e3fc
  0000:8d8f7911) started at Aug 14 2018  6:14PM in database 'cola' at
  LSN (2599205:10500:111) failed. The statement has been terminated. Msg
  596, Level 21, State 1, Line 4 Cannot continue the execution because
  the session is in the kill state. Msg 0, Level 20, State 0, Line 4 A
  severe error occurred on the current command.  The results, if any,
  should be discarded.

While moving some indexes to a different filegroup, and at the same time applying compression= PAGE, I get the error message above.

This is an example of one of my statements:

        CREATE NONCLUSTERED INDEX idx_applicationID_activityDate
    ON [dbo].[tbl_applicant_activity]
    (  [applicationID] ASC  , [activityDate] ASC  )
    INCLUDE ( [activityTypeID] , [applicantID])
    WITH (  PAD_INDEX = OFF, FILLFACTOR = 100,
 SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF,
    DROP_EXISTING = ON,DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [NONCLUSTERED_INDEXES]

It is too late in the day today, but for tomorrow, out of working hours my plan is to try to run these statements in batches and find the culprit, if any.

The database is part of an AG.

QUESTION:

What does this message really mean, and how does it interfere in my index creation?

As Kin implied in the comments, this is a problem with communication between the primary and secondary nodes of your sync-mode availability group.

There are a number of different causes for this.  It's possible that the availability group failed over in the middle of the operation (which would cause the transaction to be rolled back and then that error occurs).

More generally, the error message:

  Remote harden of transaction 'CREATE INDEX' (ID 0x0000000082a3e3fc 0000:8d8f7911) started at Aug 14 2018 6:14PM in database 'cola' at LSN (2599205:10500:111) failed

Means that the transaction log record associated with log sequence number 2599205:10500:111 either

couldn't be sent to the secondary, or
wasn't acknowledged by the secondary

You should be able to find clues regarding what is causing this communication failure in

the cluster log (see here for how to get that),
the SQL Server error log (look for messages about availability group role changes), and
the AlwaysOn_health extended event session.


How to create a SQL Server user that can restore databases to a database server? I want to create a new user that can create new databases on my database server.

I've tried:

CREATE LOGIN [my_db_restore_user] WITH PASSWORD = 'passwordhere';
GRANT ALTER ON ROLE::[dbcreator] TO [my_db_restore_user] WITH GRANT OPTION

Which results in: Cannot find the object 'dbcreator', because it does not exist or you do not have permission.

I've also tried:

ALTER SERVER ROLE [dbcreator] ADD MEMBER [my_db_restore_user];

GRANT ALTER ON SERVER ROLE::dbcreator TO my_db_restore_user ;

but all result in the same error.

How can I create a server level user that can create databases on the server for restoring purposes. The user will also need to create users on the new databases too.

Any help on this issue would be greatly appreciated.

Whilst you can use the dbcreator role, I wouldn't personally. It can drop existing databases, and alter them.

In fact I tend to stay away from fixed server roles completely, in favour of designing my own server roles with appropriate rights.

I would do the following to grant the specific, granular right you need:

GRANT CREATE DATABASE TO [my_db_restore_user];

This should allow them to create databases only. Obviously it's worth thinking about the fact that this login then becomes the owner of that database and can subsequently drop it.


MSDB - Backup information is not updating I'am having a problem in my database

My backup job is working correctly, but the tables of msdb is not updating the information.

Here is my case, if you see, My last transaction log backup was in 2020/05/23 05:00:00 in my job history:

But in the msdb tables like msdb.dbo.backupset and msdb.dbo.backupediafamily is not updating.. the last backup was in 2020/05/22 01:00:00:

How can I resolve it?

As you are running SQL Server 2012 it might be this bug.
Check also this article: "Could not insert a backup or restore history/detail record in the msdb database" While restoring msdb


how to make levenshtein distance faster for mysql I am trying to make a similar query faster:

SELECT levenshtein(substring(keywords, 1,5),'texas') as distance
FROM stuff ORDER BY distance ASC

Unfortunately and as expected, the query takes an eternity. How can I accomplish the same thing, but make it fast? The table is 5 million rows.

Functions are slow and it is a complex function. Try optimizing the function.

Or select down to like .NET and do it there.

If you get a lot of exact matches you can make that separate.

SELECT o as distance
FROM stuff ORDER BY
where substring(keywords, 1,5) = 'texas')

union

SELECT levenshtein(substring(keywords, 1,5),'texas')
from FROM stuff
where substring(keywords, 1,5) <> 'texas')
FROM stuff ORDER BY distance ASC


Unexpected Seq Scan when doing query against boolean with value NULL I have a database column called auto_review where column type is boolean.
There is an index for that field, created using the ActiveRecord ORM.

CREATE INDEX index_table_on_auto_renew ON table USING btree (auto_renew);

When I query the field for a boolean value, PG uses the index as expected.

EXPLAIN for: SELECT "table".* FROM "table"  WHERE "table"."auto_renew" = 'f'
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Bitmap Heap Scan on table  (cost=51.65..826.50 rows=28039 width=186)
   Filter: (NOT auto_renew)
   ->  Bitmap Index Scan on index_domains_on_auto_renew  (cost=0.00..44.64 rows=2185 width=0)
         Index Cond: (auto_renew = false)
(4 rows)

When the value is NULL, a sequential scan is used.

EXPLAIN for: SELECT "table".* FROM "table"  WHERE "table"."auto_renew" IS NULL
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on table  (cost=0.00..1094.01 rows=25854 width=186)
   Filter: (auto_renew IS NULL)
(2 rows)

I'm curious to know the reason behind this choice.

Generally, col IS NULL is a possible candidate for a (default) b-tree index search. The manual:

  Also, an IS NULL or IS NOT NULL condition on an index column can be
  used with a B-tree index.

To get proof, disable sequential scans (in a test session only!):

SET enable_seqscan = OFF;

I quote the manual here:

  enable_seqscan (boolean)

  Enables or disables the query planner's use of sequential scan plan types.
  It is impossible to suppress sequential scans entirely,
  but turning this variable off discourages the planner from using one
  if there are other methods available. The default is on.

Then try again:

EXPLAIN ANALYZE SELECT * FROM tbl WHERE auto_renew IS NULL;

This will probably result in a bitmap index scan that is slower than a sequential scan on the table.

Reset, or close the session (the setting is session-local).

RESET enable_seqscan;

Indexes on boolean columns are only useful in certain cases. The planner only uses an index if it expects that to be faster. Calculations are based on your cost settings and the statistics gathered by ANALYZE. If a sizable portion of the table matches your condition (around 5% or more, it depends), it is typically faster to do a full table scan instead.

This leaves the rare value in a boolean column as the only useful candidate for a plain index. And it is typically more efficient to create a (more specialized) partial index instead for this - which is cheaper to maintain, smaller, faster and used more readily if query condition match.

If you have lots of queries looking for rows with auto_renew IS NULL and the NULL case is not very common (and / or you need a certain sort order) then this index would help to find / sort these rows quickly:

CREATE INDEX index_tbl_tbl_id_auto_renew_null ON tbl (tbl_id)
WHERE auto_renew IS NULL;

The condition of the partial index has to be repeated in the WHERE clause of a query more or less exactly to make the query planner realize the index is applicable.

The indexed column (tbl_id) is an arbitrary pick. The important part is the WHERE clause. This particular index would be most effective for queries with ORDER BY tbl_id or an additional filter or join on tbl_id. You could make it a multicolumn index. Boolean columns are often more useful in combination with others.

Aside: ORMs are crutches that regularly fail to get the full potential out of your RDBMS.


why there is no performance differences if I use where clause inside of inner join? Basically I have two different types of query in BQ.
the first one:

select q2.name, q1.* , q2.val1 from table1 as q1
inner join
(select name,val1, val2 from table2) as q2
on q1.name = q2.name

and second one is:

select q2.name, q1.* , q2.val1 from table1 as q1
inner join
(select name,val1, val2 from table2  where val1 = "X") as q2
on q1.name = q2.name

so as you can see there, the only difference is filtering X value. When I filter the X value inside of table2, size of table2 is being almost half. So my expectation is seeing a reduction of bytes of data, when I apply the "where" clause. However when I run --dry_run in the BQ cli. I got exactly the same bytes of data.

Query successfully validated. Assuming the tables are not modified, running this query will process 6958332498714 bytes of data.

So can somebody explain why I got exactly the same processed data size?

BigQuery reduces how much data is scanned in a couple scenarios:

You have a partitioned table, and you filter on the partitioning column.
You have a clustered table, and you filter on one of the columns used for clustering.

For other types of filters, however, such as if you don't partition or cluster a table, you will incur the cost of scanning the entire table. See the query pricing documentation for more information.


MySQL transaction and replication between two datacenters I'm using a master MySQL in an east coast data center and want to setup a replication server on a west coast data center. The app server on the west coast may at times need to write to the master.

Since the data centers can't setup a private link between them using the internal interfaces, I have to use a public interface. Does MySQL use any encryption when sending data over the public network? How can I secure or increase security between the two sites?

Let's assume site-to-site VPN is a last resort option.

You can setup replication using SSL.

With an unencrypted connection between the MySQL client and the server, someone with access to the network could watch all your traffic and look at the data being sent or received, or even change the data while it is in transit between client and server.
When you must move information over a network in a secure fashion, an unencrypted connection is unacceptable. Encryption is the way to make any kind of data unreadable. Encryption algorithms must include security elements to resist many kinds of known attacks such as changing the order of encrypted messages or replaying data twice.
MySQL supports secure (encrypted) connections between clients and the server using the Secure Sockets Layer (SSL) protocol. SSL uses encryption algorithms to ensure that data received over a public network can be trusted. It has mechanisms to detect any data change, loss, or replay. SSL also incorporates algorithms that provide identity verification using the X509 standard.

Source: https://dev.mysql.com/doc/refman/5.6/en/ssl-connections.html


FLUSH TABLES stays in the processlist even after execution Here is my code from Stored Procedures in Mysql

FLUSH TABLES;
TRUNCATE TABLE tbl_cdrload;
TRUNCATE TABLE tbl_cdrdetails_temp;

Then i execute a query to load a file in to database as below

LOAD DATA LOCAL INFILE filename INTO TABLE tbl_cdrload
FIELDS TERMINATED BY LINES TERMINATED BY '\n';

I executed this set of code one after the other several times using a Windows service. After some time, the Windows service stops automatically. When I open MySQL Administrator, I could see the FLUSH TABLES still running in the processlist. Can anyone explain why so and if this is the reason for the service to stop?

I am very iffy when it comes to TRUNCATE TABLE.

FLUSH TABLES actually closes the file handles on all open tables and reopens them with new file handles.

It could be the two commands colliding into one another, especially if there are a lot of open file handles in mysqld.

SUGGESTION #1

Please replace

FLUSH TABLES;
TRUNCATE TABLE tbl_cdrload;
TRUNCATE TABLE tbl_cdrdetails_temp;

with

CREATE TABLE tbl_cdrload_new LIKE tbl_cdrload;
ALTER TABLE tbl_cdrload RENAME tbl_cdrload_old;
ALTER TABLE tbl_cdrload_new RENAME tbl_cdrload;
CREATE TABLE tbl_cdrdetails_temp_new LIKE tbl_cdrdetails_temp;
ALTER TABLE tbl_cdrdetails_temp RENAME tbl_cdrdetails_temp_old;
ALTER TABLE tbl_cdrdetails_temp_new RENAME tbl_cdrdetails_temp;
DROP TABLE tbl_cdrload_old;
DROP TABLE tbl_cdrdetails_temp_old;

SUGGESTION #2

Please add bulk_insert_buffer_size to my.ini

[mysqld]
bulk_insert_buffer_size=256M

and restart mysql

C:\> net stop mysql
C:\> net start mysql

If you cannot restart mysql at this time, please run this command

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 256;

UPDATE 2012-07-31 18:29 EDT

As for the Windows service stopping, you will have to look into the MySQL error log. It will reveal what kind of shutdown (or crash scenario) was executed. In the Linux version of mysql, the mysqld process is controlled by a startup script called mysqld_safe. It has an infinite loop that does this:

LOOP

Execute mysqld
When mysqld terminates, get return code
If ReturnCode is 0, Exit Loop
If ReturnCode > 0 and Severity OK, Loop or Terminate
If ReturnCode > 0 and Severity Bad, Terminate

END LOOP

This mechanism does not exist in MySQL for Windows. If the Windows service for MySQL crashes, that's it. You must check the error log.


If then else case PL/SQL if text is in list I have a column that will return something like this:

01
05
Z7, Z4, 01
09,08

I then have two static lists:
list a = [01, 02, 03, 04]
list b = [Z7, Z2, 05]

and our case statement returns one of the following:

group a
group b
mixed
none

I need to write a CASE statement to first breakdown each row using a comma delimiter, then loop through each value to see if the codes are part of list a exclusively, list b exclusively, a mix of both, or neither.

To put this into practice, we'd look at the first item, 01.

It exists in list a, but not in list b, so the case would return group a

The second result, 05, exists in list b, but not in list a, so it would return group b.

The third result, Z7, Z2, 01 contains multiple codes.  Because at least ONE of these codes is in list b, and at least ONE of these codes exists in list a, we the case would return mixed.  The fact that it also contains a code that doesn't fit into either group doesn't matter because at least one code exists in each list.

The fourth result, 09,08 would return none because neither 09 nor 08 exist in either list.

Is this possible to do in PL/SQL?

For starters, you could do the following: stick all list items - together with the group names - into a table (Oracle 12.1):

create table grouplists ( item_, group_ )
as
select '01', 'a' from dual union all
select '02', 'a' from dual union all
select '03', 'a' from dual union all
select '04', 'a' from dual union all
select 'Z7', 'b' from dual union all
select 'Z2', 'b' from dual union all
select '05', 'b' from dual ;

You already have the table containing the samples.  For testing here:

create table samples ( value_ )
as
select '01' from dual union all
select '05' from dual union all
select 'Z7, Z4, 01' from dual union all
select '09, 08' from dual ;

The following function will accept a "sample" ie a comma-separated value, which gets split up into "items".  Each item is used in a SELECT ... INTO ... statement, picking up a "count" from the GROUPLISTS table.  The function returns a string, containing the group membership ie (group a, group b, mixed, or none).
The only aspect that is (maybe) not straightforward (in this function's code) is the recursive SELECT ... CONNECT BY.  There are examples of this on SO eg this one.

create or replace function findgroup (
  sample varchar2
) return varchar2
is
  ila pls_integer := 0 ; -- increase if item is found in list a
  ilb pls_integer := 0 ; -- increase if item is found in list b
  ccount pls_integer := 0 ;  -- intermediate count
  retval varchar2( 4000 ) := '' ;
begin
  for s in (
    select unique -- split the sample into items
       trim( regexp_substr( sample, '[^,]+', 1, level ) ) item_
    from ( select sample from dual )
    connect by instr( sample, ',', 1, level - 1 ) > 0
  ) loop
--
    select count(*) into ccount
    from grouplists where item_ = s.item_ and group_ = 'a' ;
    ila := ila + ccount ;
--
    select count(*) into ccount
    from grouplists where item_ = s.item_ and group_ = 'b' ;
    ilb := ilb + ccount ;
--
  end loop;
  retval := case
    when ila > 0 and ilb > 0 then 'mixed'
    when ila > 0 and ilb = 0 then 'group a'
    when ilb > 0 and ila = 0 then 'group b'
    else 'none'
  end ;
  return retval ;
end findgroup ;
/

Testing:

select value_, findgroup( value_ ) from samples ;

-- result
VALUE_      FINDGROUP(VALUE_)
01          a
05          b
Z7, Z4, 01  mixed
09, 08      none

Solution in SQL

Maybe you don't need a function (or the function is not fast enough for you).  Here's another approach, written in SQL.  Ideas: {1} break down the values stored in the SAMPLES table via a view, and {2} use this view in a query that JOINs it to the GROUPLISTS, and applies the CASE expressions. ( You can - of course - also code everything into one query, just write the view's  code instead of the name SAMPLES_EXPANDED - see the "Alternative" query )

{1} view

create or replace view samples_expanded
as
select unique
  value_
, trim( regexp_substr( value_, '[^,]+', 1, level ) ) item_
from samples
connect by instr( value_, ',', 1, level - 1 ) > 0 ;

-- testing the view
SQL> select * from samples_expanded order by value_;
VALUE_      ITEM_
01          01
05          05
09, 08      08
09, 08      09
Z7, Z4, 01  01
Z7, Z4, 01  Z4
Z7, Z4, 01  Z7

7 rows selected.

{2} query

select
  value_
, case
    when sum( in_a ) > 0 and sum( in_b ) > 0 then 'mixed'
    when sum( in_a ) > 0 and sum( in_b ) = 0 then 'group a'
    when sum( in_a ) = 0 and sum( in_b ) > 0 then 'group b'
    else 'none'
  end as groupmembership
from (
  select value_, E.item_, group_
  , case when group_ = 'a' then 1 else 0 end in_a
  , case when group_ = 'b' then 1 else 0 end in_b
  from samples_expanded E
    left join grouplists G on E.item_ = G.item_
)
group by value_
order by value_
;

-- result
VALUE_      GROUPMEMBERSHIP
01          group a
05          group b
09, 08      none
Z7, Z4, 01  mixed

Alternative ( query with inline view )

select
  value_
, case
    when sum( in_a ) > 0 and sum( in_b ) > 0 then 'mixed'
    when sum( in_a ) > 0 and sum( in_b ) = 0 then 'group a'
    when sum( in_a ) = 0 and sum( in_b ) > 0 then 'group b'
    else 'none'
  end as groupmembership
from (
  select value_, E.item_, group_
  , case when group_ = 'a' then 1 else 0 end in_a
  , case when group_ = 'b' then 1 else 0 end in_b
  from (
    select unique
      value_
    , trim( regexp_substr( value_, '[^,]+', 1, level ) ) item_
    from samples
    connect by instr( value_, ',', 1, level - 1 ) > 0
  ) E
    left join grouplists G on E.item_ = G.item_
)
group by value_
order by value_
;

Dbfiddle here.


How to get last values in SQL Server 2008 I need to make a program that calculates money for water bills. Here are table definitions that I created:

CusAdd_Table
    Id,
    Name,
    Phone,
    FamilyNo,
    City,
    SubscribeDate

Calu_Table
    NowData,
    PerviousData,
    Diff,
    MonthtBill,
    money,
    id

The Diff column is computed as NowData-PerviousData. The id column is a reference to CusAdd_Table. The Money column is calculated with some differentiation. The MonthtBill column is a dataTimepicker used for ordering.

My table structure is able to easily add new subscribers to the database. When I add a new bill for a month I insert a row into the Calu_Table table. For example, I would specify the values for the NowData,PerviousData,[NowData-PerviousData] as Diff columns when inserting data for Jan. I would need to insert additional rows to insert data for Feb and Mar.

I want to print last month's data but the code needs to work without knowing the number of subscribers upfront. I tried the below code and it did not satisfy my requirements because I want every Id with its last value.

SELECT
CusAdd.CusID AS Expr1,
CusAdd.CusName,
CusAdd.CusPhone,
CusAdd.CusFamily,
CusAdd.CusCity,
CusAdd.CusSubscribe,
Calculte_Table.Months,
Calculte_Table.NowRead,
Calculte_Table.ExRead,
Calculte_Table.Esthlak,
Calculte_Table.WhatHePay,
Calculte_Table.Dis_Money,
Calculte_Table.MonthtBill
FROM Calculte_Table
INNER JOIN CusAdd ON Calculte_Table.CusID = CusAdd.CusID
ORDER BY Calculte_Table.MonthtBill DESC;

Here is the result:

You can use a CTE (or subquery) to determine which row is the latest per customer, then filter for those in the outer query. Consider using more legible formatting (tabs and carriage returns are your friends), table aliases, and always use the schema prefix.

;WITH x AS
(
  SELECT
    c.CusID,
    rn = ROW_NUMBER() OVER (PARTITION BY c.CusID ORDER BY ct.MonthBill DESC)
    c.CusName,
      ... all those other columns ...
    ct.MonthtBill
  FROM
    dbo.Calculte_Table AS ct
  INNER JOIN
    dbo.CusAdd AS c
    ON ct.CusID = c.CusID
)
SELECT CusID, CusName, ... all those other columns ...
FROM x
WHERE rn = 1
ORDER BY MonthtBill DESC;


What design should I use? I am creating a program that will monitor several servers and computers.

A user can create an account and have multiple computers in his account that will be monitored when the user installs the client on it.

When the client is running for the first time, the user can input their account info and it will be saved encrypted for automatic login. After that the program will send important data to the database that only the user can read within his account.

As I am using Mysql.net connector in my vb.net project I would still want to use MySQL as my database.

At the moment I have one DB with the current tables: accounts, computers.

The problem that I am running into is the following:

At this moment the design is very flat. User IDaccount is connected with IDcomputer. But this works only for 1 computer per account. I need a way to link multiple computers to one user account. How should I go about this?

I was thinking of using a separate table that holds the computer IDs with the correct user account. Am I going in the right way?

The accounts table will hold the following info:

IDAccounts
Name
Email
Username
Passwordhash
Salt

computers table will hold the following:

IDComputers
MachineID
Hostname
WANIP
LocalIP
OS
CPUPercentage
RAMFree
RamUsed
Appversion
FreeSpaceC

I'm learning on the job. I just need to know how I can link the user account with multiple computers. I will figure out the rest, I just need a pointing in the right direction.

If you want to have a computer be assigned to more than one user, you're going to need a junction table between the two to store the relationships. This was not stated in the requirements but it seems like a logical approach given the project scope.  1. Account 2. Computers 3. Account_Computer_Assignments.  The third one is your junction table that stores the relationship between the other two.  It's primary key would consist of IDAccounts and IDComputers using your naming convention.  A foreign key relating back to their parent tables would also be advisable.
This structure will also allow for multiple users to link to the same computer.  I don't know if that is the intended behavior but it will allow for that with this design.
If that is not intended, and a single computer can only be monitored by one and only one user, then a single field on the Computer table for the AccountID is all that is needed.  No junction table.


Clustered Index Creation I have been trying to create a clustered index on a table which is 1.4 TB in size.

This is my query:

CREATE CLUSTERED INDEX [C_IDX_Impressions]
ON [dbo].[Impressions] ( [User_ID] ASC )
WITH (PAD_INDEX = OFF,
      STATISTICS_NORECOMPUTE = OFF,
      SORT_IN_TEMPDB = OFF,
      DROP_EXISTING = OFF,
      ONLINE = OFF,
      ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON)
ON [Impressions]
GO

The [Impressions] filegroup has 1TB of free space. But the query is failing with the following message:

  Could not allocate space for object 'dbo.SORT temporary run storage: 140740546723840' in database 'HULU' because the 'Impressions' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Am I missing something here?

The Tempdb should not come in the picture. Is it correct?

Any input will be appreciated.

When you add a clustered index to an existing table, SQL Server needs space to build the new index structure, in essence the entire table.

If space is an issue, you could create a new table with the clustered index, then migrate the data in chunks, drop the original table and rename the new one. It goes without saying that moving 1.4 TB if data like this is really only realistic if you're in SIMPLE recovery model, since you'd fill the log with that data otherwise.

You could probably use partition switching to avoid renaming, as renames carry some risks. That would involve truncating the original table, adding the clustered index to it, and finally using ALTER TABLE ... SWITCH.

Note that this operation will interrupt users, and you'll also have to be mindful of foreign key constraints and other schemabound objects. However, creating a clustered index on a TB+ table will also block the table for a long time, so it's your call.

I would probably just ask my SAN admin for more disk space if that's an option.


enable query log for single database I am having many database schema in mysql 5.6 server, now the problem over here is that I want to catch the queries for one schema only.

I can not enable query log for entire server as one of my schema is highly loaded and it will impact the server.

Is their any way, any tool through which I could only log the queries by single schema.

I found benchmarking graph which shows the impact on transactions/second when query log is enabled.

Interesting question and a +1. I was interested by this because I can see several use cases for this fuctionality.

Unfortunately, for your case where you cannot switch on general logging, there is only one, rather inadequate, workaround.

That is to use the SQL_LOG_OFF variable to disable logging for a given connection. An ideal solution would have been to have an "SQL_LOG_ON" variable as one can do in Oracle (equivalent) - maybe you could try and switch logging off for all but the connection(s) of interest?

Furthermore, and regretably, this requires the SUPER privilege. Again, this may not (even probably not) be possible in your case.

Depending on the severity of your problem, working hours and server load at given times, you may be able to find a use for Percona's pt-query-digest which can help with log analysis. Small comfort, but as usual PostgreSQL is streets ahead of MySQL (1, 2).

If you'd care to file a feature request, I'd be happy to follow up with a "me-too" if you post the link back here.


How to prevent Dropping of tables but allow Creating of them, within a Schema Is it possible to, within a specific Schema, allow a user to create a table, and select from it, but not allow them to Delete or update rows after initial creation, and also prevent them from dropping it ?

Create Table as Select requires select permissions for the Select, however, the Create Table requires Create table and Alter Schema on the Schema.

The Alter Schema permission also includes permissions to drop the table, which we don't want.

We are using Azure SQL Data Warehouse, so can't use Triggers..

Expanding on the suggestion of @Dominique Boucher.

You can do it by making a procedure that acts as proxy for the create statement and specifying an execute as clause in it. If you then only allow execute permissions to the users that you want, they can only create tables, not edit them. This way don't become owners (the execute as user becomes owner) and they can't edit the procedure itself.

GRANT EXECUTE ON [dbo].[TableBuilder] TO [Plebby]

Below an example, with some checking/error handling, but you would need to expand on it to suit your needs. You would create this procedure on a highly priviliged user.

CREATE PROCEDURE TableBuilder
@schemaName sysname,
@tableName sysname,
@tableScript nvarchar(max)
WITH EXECUTE AS 'dbo'
AS
BEGIN
DECLARE @ErrorMessage NVARCHAR(4000);

IF SCHEMA_ID(@schemaName) IS NULL
BEGIN
    SET @ErrorMessage = 'Schema with name ' + @schemaName + ' does not exist.';
    RAISERROR(@ErrorMessage, 16, 0);
    RETURN
END

IF OBJECT_ID(@schemaName + '.' + @tableName, 'U') IS NOT NULL
BEGIN
    SET @ErrorMessage = 'Defined table ' + @schemaName + '.' + @tableName + ' already exists';
    RAISERROR(@ErrorMessage, 16, 0)
    RETURN
END

IF (@schemaName + '.' + @tableName <> (SELECT SUBSTRING(@tableScript, (CHARINDEX('TABLE', @tableScript) + 6), (LEN(@schemaName) + LEN(@tableName) + 1))))
BEGIN
    SET @ErrorMessage = 'Schema and/or tablenames in creation script do not match with ' + @schemaName + '.' + @tableName;
    RAISERROR(@ErrorMessage, 16, 0)
    RETURN
END

EXEC(@tableScript)

RETURN 0
END

A user with execution rights on the above procedure can use it to execute a valid CREATE TABLE statement. And you can add more checks/constraints (i.e. the allowed schemas). And should do so since the execution is dynamic SQL. Depending on the user rights of your:

  WITH EXECUTE AS 'user'

You can get some nasty rights escalation if you don't control your input.
I've tested the above procedure on SQL Server 2008, 2012 and 2017. I don't have an AzureDB instance available for testing though.


SQL Server FullText searching on text that is slightly obfuscated before insert Our app slightly obfuscates text before insertion to the database. This is done by the application (C#) and not the database. (This is not encryption but just to stop people who are looking at the data in Management Studio; not really preventing shoulder surfing, more like "can't read text even with correct permissions")

Here's a simplified example.

In the website I input HELLO into a textbox and click save.
The save method then replaces each character with the next in the
alphabet, so HELLO becomes IFMMP.
The save/update command is then called and IFMMP is saved into the
database.
When the data is read back from the database, each character is
replaced with the one before, so IFMMP becomes HELLO and gets
displayed in the textbox.

IFMMP won't be much good for fulltext searching. We have hundreds of existing sprocs & triggers which we don't want to update for this.

The question is, how can I "un-obfuscate" the data before it gets added to the Fulltext catalogue?

Update: Looks like we need a custom WordBreaker or IFilter. Will update if get to a solution.

The full text catalog will only reflect the contents of a table's data.

Since it sounds like you're not using full text search yet, and you just want to start, you basically have 2 ways to do this:

Option 1: add columns with the de-obfuscated data, and use full text search on
those. Say you have a column dbo.Customers.SavedText that is obfuscated - you add a dbo.Customers.SavedTextClear column with the original text. You can populate it and keep it up to date with triggers on insert and update. (Triggers usually have a bad reputation - rightfully so, you wanna minimize the work performed in them - but you asked for a solution that didn't require modifying the original queries inserting/updating into the table, and this is what it looks like. When you tie my hands, my artwork isn't quite as good, ha ha ho ho.)

Option 2: do full text search in something like Elasticsearch. Build an ETL process that exports the fields you want to search, de-obfuscates them, and puts them in a database that's tuned for full text search, like Elastic. You can read about how Stack Overflow approached this problem, although when you read it, I bet modifying your original queries is probably going to sound like a better solution. The only reason I mention it is that if you really need to scale out, this can be a less expensive approach long term.


Oracle 12c: textual search over all PL/SQL code I want to do a text search over all PL/SQL functions/procedures in my DB to see which code is calling a particular function. Is there a tool in SQL Developer for that, or a meta-table under the system schema storing function code? A textual dump of the schema would help as well.

Provided that the following is true:

1) The function is NOT in a package
2) The function call is NOT dynamic SQL

Then you can simply see what its dependencies are from dba_dependencies:

SELECT *
  FROM dba_dependencies
 WHERE referenced_owner = 'MYOWNER' AND referenced_name = 'MYOBJECT';

Otherwise, you can check dba_source:

SELECT *
  FROM dba_source
 WHERE upper(text) like '%MYOWNER.MYOBJECT%';

To view the source for all objects for a schema:

SELECT *
  FROM dba_source
 WHERE owner = 'MYOWNER'
 ORDER BY type, name, line;


Oracle Datapump between different database releases I need to transfer schemas from a Oracle 12.1.0.2 Database to a Oracle 11.2 XE Database. Accodring to

Oracle Docs:

  On a Data Pump export, if you specify a database version that is older
  than the current database version, then a dump file set is created
  that you can import into that older version of the database. For
  example, if you are running Oracle Database 12c Release 1 (12.1.0.2)
  and specify VERSION=11.2 on an export, then the dump file set that is
  created can be imported into an Oracle 11.2 database

.

I used the following expdp command:

expdp system/XXXX directory=name dumpfile=dump_24.8.2018.dp consistent=y version=11.2 logfile=dump_24.8.2018.log schemas=name1,name2

The impdp failes with errors that the dump has version 12. Any ideas whats wrong?

Be sure to use the version 12 impdp to import into the Oracle 11.2 XE Database.


Saving into a VARCHAR(MAX) throwing error in some scenarios We are noticing an error like this:

InterfaceError: ('HY104', 0, '[Microsoft][SQL Server Native Client 11.0]
Invalid precision value', 10226)

It happens when we insert a certain length of characters along with Unicode content like \u3000 (Ideographic Space). This happens only we are using the Windows MSSQL Driver and the query is parametrized.

The column is a VARCHAR(MAX). So it should support 2GB.

What could be the reason for this?
Why is it that length matters?

If I just insert this Unicode character alone, it won't cause an issue!

I was trying to see this from a theoretical point of view. Switching to NVARCHAR is not an option for us currently, but removing Unicode is.

Here are some more details as per the questions asked by Solomon Rutzky:

Column Collation is SQL_Latin1_General_CP1_CI_AS
String length where I start seeing the error is 4019.
sqlalchemy.connectors.mxodbc, line 86, in error_handler is where I get the initial error. This is from the traceback.
It works fine with single Unicode character.
A 20000 character string without Unicode works fine.
It makes it ASCII from when I try this from this software (Web App). I tested in Dbeaver by exporting the data.

It would greatly help to know:

the current Collation of the VARCHAR(MAX) column
the exact string length (in bytes) at which you start seeing the error
What software is reporting this error?
Does the error occur if only a single Unicode character is present?
Does a 10,000 character string with no Unicode characters get inserted correctly?
For a short-enough string that gets inserted without error yet contains one of these Unicode characters, what value exactly is in the table? You can check by converting the column to VARBINARY(MAX). Did the Unicode character get translated or something else?

It is possible that the string that errors is just over a 4000 or 8000 character limit that then requires using a MAX type and the driver is initially guessing (or being told) that it is one size and then finding out that it is another. The error message says "invalid precision value" which has to be referring to how the column is being configured (i.e. similar to setting up a SqlParameter in .NET and declaring the max size, such as new SqlParameter("@name", SqlDbType.VarChar, 8000)). So this type of error should only really happen if the software is attempting to set up the column as VARCHAR of over 8000 or NVARCHAR of over 4000, as either of those conditions would be an invalid "precision". Now, in order to get such a number I suspect that some code in there is counting the number of bytes (i.e. DATALENGTH ) of the string, which will be larger than the number of characters. Of course, this would be true of any character in a .NET string or C++ wchar, so I am suspecting that the difference with having some Unicode characters vs not having any is that without any Unicode characters it can convert the string to an 8-bit encoding (i.e. single byte for use with VARCHAR), but somehow having a Unicode character prevents that. It's a long-shot (until there is more info provided), but it is pretty clear where the error is occurring.

  If I just insert the character alone it won't cause an issue.

How are you inserting the character? Through the same software that is generating the error, or in SSMS? If you are doing this manually via an INSERT statement, then that isn't a good test as SQL Server converts U+3000 into a regular space, U+0020 (one byte), instead of two questions marks (still 2 bytes, as would happen if there was no equivalent character):

SELECT N'-' + NCHAR(0x3000) + N'-',
       CONVERT(VARCHAR(MAX), N'-' + NCHAR(0x3000) + N'-'),
       CONVERT(VARBINARY(MAX), CONVERT(VARCHAR(MAX), N'-' + NCHAR(0x3000) + N'-'));

returns:

-　-    - -    0x2D202D

If possible, trying passing in both of the strings generated by the following code via the software, not via SSMS:

DECLARE @String4k NVARCHAR(MAX);
SET @String4k = REPLICATE(N'a', 3999) + NCHAR(0x3000);
SELECT LEN(@String4k);

DECLARE @String8k NVARCHAR(MAX);
SET @String8k = REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 7999);
SET @String8k += NCHAR(0x3000);
SELECT LEN(@String8k);

The @String4k string is 3999 characters that can convert cleanly into an 8-bit encoding (i.e. VARCHAR) plus the U+3000 character that will likely remain as 2 bytes. So maybe this comes across, through that software, as 4001 characters. I doubt that this is the issue, but can't hurt to test.

The @String8k string is 7999 characters that can convert cleanly into an 8-bit encoding (i.e. VARCHAR) plus the U+3000 character that will likely remain as 2 bytes. So maybe this comes across, through that software, as 8001 characters.