SQL Server - MERGE duplicate SOURCE rows validation is only evaluated on UPDATE and DELETE clauses I want SQL Server to throw an error at me if my SOURCE table contains duplicates as per the ON clause in every case, including INSERT, but it only does for the UPDATE and DELETE cases. Why doesn't it?

For example, it takes two executions of this statement to get any sort of validation error. First, let's define the table:

--assume this is a permanent table that starts empty:
CREATE TABLE #MyTable(KeyID1 INT, KeyID2 INT, SomeValue REAL)--no primary key on purpose for example

And the MERGE statement I wish would throw the error at me on the first execution:

MERGE #MyTable AS TARGET
USING
(
    SELECT
         KeyID1 = 1
        ,KeyID2 = 1
        ,SomeValue = 1
    UNION ALL SELECT
         KeyID1 = 1
        ,KeyID2 = 1
        ,SomeValue = 2
) AS SOURCE ON
    TARGET.KeyID1 = SOURCE.KeyID1
    AND TARGET.KeyID2 = SOURCE.KeyID2
WHEN MATCHED THEN UPDATE SET
    TARGET.SomeValue = SOURCE.SomeValue
WHEN NOT MATCHED BY TARGET THEN INSERT
    (
         KeyID1
        ,KeyID2
        ,SomeValue
    )
    VALUES
    (
         SOURCE.KeyID1
        ,SOURCE.KeyID2
        ,SOURCE.SomeValue
    );

After the first execution, the contents of #MyTable are

The error message that only comes on the second and subsequent executions:

  Msg 8672, Level 16, State 1, Line 4
  The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

The wording of this error message seems to imply my desired validation should be occurring already.

Of course, one obvious thing to do is make the primary key on the TARGET table reject the INSERT, but that is unrelated to my question.

P.S. I came across this list of MERGE bugs that doesn't seem to apply here.

On the first run, there are no matches in the conditional check. That is, there are no rows in #MyTable that match the input rows.

Note that the matching in a MERGE does not consider rows inserted during the MERGE itself. If those rows could cause duplication, it is the responsibility of the developer to ensure that there are no potential conflicts in the source data. Since this is the case, and there are no constraints such as a Primary Key to conflict with the data being added, this insert succeeds for both rows.

Of course, on the second run, there are two matching rows, and the MERGE fails with the error you showed.

This is by design, and does speed the processing, as if the MERGE had to validate each row individually as they were considered (as opposed to set based validation performed by the MERGE internally), then the process would be more like using a cursor to handle each row one at a time.

How To Handle Source Duplicates

Using the simple case you have above, I would modify it like this to handle source duplication:

CREATE TABLE #MySource (        -- This represents the actual source of the data in the database
    KeyID1 int,
    KeyId2 int,
    SomeValue bigint
    )
INSERT INTO #MySource

    SELECT
         KeyID1 = 1
        ,KeyID2 = 1
        ,SomeValue = 1
    UNION ALL
    SELECT
         1
        , 1
        , 2

-- Find the duplicates, and set up an error condition / report for them.

    SELECT
        KeyID1,
        KeyId2,
        Count(*) as ct
    INTO #Duplicates
    FROM #MySource
    GROUP BY KeyID1, KeyId2
    HAVING COUNT(*) > 1

    IF EXISTS (Select * FROM #Duplicates)
    BEGIN
        -- handle error here. Perform a Raiserror or THROW, or SELECT
        -- the items from the duplicates and stop processing, or...
        Print 'Error - Duplicates!'
        SELECT *
        FROM #Duplicates
    END

    -- No duplicates, so continue
MERGE #MyTable AS TARGET
USING (
    SELECT *
    FROM #MySource m
    )
) AS SOURCE ON
    TARGET.KeyID1 = SOURCE.KeyID1
    AND TARGET.KeyID2 = SOURCE.KeyID2
WHEN MATCHED THEN UPDATE SET
    TARGET.SomeValue = SOURCE.SomeValue
WHEN NOT MATCHED BY TARGET THEN INSERT
    (
         KeyID1
        ,KeyID2
        ,SomeValue
    )
    VALUES
    (
         SOURCE.KeyID1
        ,SOURCE.KeyID2
        ,SOURCE.SomeValue
    );


Define a proper index for a table I have a table with millions of rows and something like 20 columns. This table have an unique identifier as primary key, but the most likely query is based on three varchar columns (col1 and col2 of type nvarchar(35) and col3 of type nvarchar(14)). For a given {col1, col2, col3} correspond at maximum 1 row. col3 is the less fragmented column. In the vast majority of cases, the table will be stressed with query like this:

SELECT * FROM table
    WHERE col1 = "xxx0" AND col2 = "yyy0" AND col3 = "zzz0"
    OR col1 = "xxx1" AND col2 = "yyy1" AND col3 = "zzz1"
    ...
    OR col1 = "xxxn" AND col2 = "yyyn" AND col3 = "zzzn"

Given this situation? What will be a good indexing strategy? Multi-column with col3 as first column? Three separated indexes?

A composite index with any order of col1,col2,col3 would work here. The query you have shown in the question would be able to perform three seeks into it for the three or-ed predicates.

To determine the best order out of the 6 possible permutations I would first look at other queries, or alternatively you could look at the missing index DMVs. Quite likely you will find that the choices you make optimising this one query can help optimise others too.

If you have queries that perform equality seeks using just two of the columns then put them first so the same index supports those queries. And similarly if one of those columns is often used on its own in an equality or range predicate then make that the leading one.

Though if any of the columns is not particularly selective you would need to verify that the index is actually used for the query identified by the process above and consider whether it is worth adding included columns to make it covering if not (or considering a different index order that would benefit a different query).


Is PostgreSQL function parallel safety intended for functions which update data? I am having some trouble understanding function parallel safety. I can understand what it does - which is to specify whether the function will be executed by the parallel master node or whether the query will totally disable parallelism.

But I am having a hard time understand WHEN to use it. Let me give an example. Say I have a function which only creates tables and does not update any data or insert any rows. What should the parallel safety of this be marked as? I would think to mark it as parallel safe, but then I cannot understand why the manual would list the following as parallel restricted:

Scans of common table expressions (CTEs).
Scans of temporary tables.

Since both points do not necessarily involve any data update/insert (race condition)?

The case for CTE is particularly strange. I am understanding that if I add a parallel safe function to a CTE call, it will not be executed as parallel safe any longer? And, outside of function parallel safety, I am understanding that all CTEs are executed as parallel restricted? Is there a difference between materialized and not materialized CTEs here?

The reason is that both CTEs and temporary tables are private to the backend process that created them, and parallel worker processes are different processes that do not have access to the private resources of the leader process.

To make CTE scans parallel safe, CTEs would have to be materialized in shared memory.

I would be careful marking a function that creates a table as parallel safe, since that creates locks in the database. Can you guarantee that there can never be any conflicts so that your worker processes lock each other?


Partition scheme filegroup metadata When you create a partition scheme against a particular partition function, you specify a series of filegroups.  I cannot find the metadata store where this filegroup list can be found & queried.

For example, I execute

CREATE PARTITION FUNCTION [ABPF](int) AS RANGE LEFT FOR VALUES (10);
CREATE PARTITION SCHEME [ABPS] AS PARTITION [ABPF] TO ([PS1], [PS2], [PS3]);

I would like to then execute a query:

select * from <table(s)>

And return, PS1,PS2,PS3 in a table, preferably with an indicator that PS3 is the "next used" filegroup.

I've explored the following and cannot seem to see or find the answer:

sys.partition_functions
sys.partition_schemes
sys.partition_parameters
sys.partition_range_values
sys.partitions
sys.dm_db_partition_stats
sys.filegroups
sys.sysfiles
sys.database_files
sys.data_spaces
sys.allocation_units

This script that I have pulled from my script library (probably a Paul Randal script) gives you the file group information.

It also shows you the ranges of values between each boundary so all you would have to do to see which partition is next would be to get the MAX of your partitioning column (Probably the PK).

With this you could see which is the active partition and depending on your function being a left of value or right you could easily see which partition is next. At least I think that works in all situations.

SELECT      ps.name AS PartitionScheme
            , fg.name AS [FileGroup]
            , prv.*
            , LAG(prv.Value) OVER (PARTITION BY ps.name ORDER BY ps.name, boundary_id) AS PreviousBoundaryValue

FROM        sys.partition_schemes ps
INNER JOIN  sys.destination_data_spaces dds
            ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN  sys.filegroups fg
            ON dds.data_space_id = fg.data_space_id
INNER JOIN  sys.partition_functions f
            ON f.function_id = ps.function_id
INNER JOIN  sys.partition_range_values prv
            ON f.function_id = prv.function_id
            AND dds.destination_id = prv.boundary_id


Msg 15404, Level 16, State 10, Procedure xp_logininfo - missing permissions I have a server based in the USA, where I am not a domain admin, and my permissions are limited to sql server only - where I am sysadmin.

after a migration of a few databases to a new server, I noticed that I needed to go back to the old server to get one of my logins and permissions related.

when I run the following command:

EXEC xp_logininfo 'gplondon';

I get:

  Msg 15404, Level 16, State 10, Procedure xp_logininfo, Line 62 [Batch
  Start Line 6] Could not obtain information about Windows NT group/user
  'gplondon', error code 0xffff0002.

I understand I have all the permissions inside sql server to run xp_logininfo

But what other permissions are missing that have disabled me (or the sql server service on that server) to interrogate the active directory?

in other words, everything was working fine before, what permissions are missing now?

after checking the error log

xp_readerrorlog 0, 1, "error" , NULL, NULL, NULL, N'desc'

finding no errors related to the above

then checking the error message:

BEGIN TRY
EXEC xp_logininfo 'gplondon';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
          ,ERROR_MESSAGE()
END CATCH;

  15404
  Could not obtain information about Windows NT group/user
  'gplondon',  error code 0xffff0002.

investigate:
15404 with Active Directory

with a quick select on sys.server_principals

select * from sys.server_principals
where name = 'gplondon'

I found something obvious: gplondon does not exist in the active directory, because it is a sql login

from the remarks on the documentation of xp_logininfo

  If account_name is a valid Windows user or group that is not
  associated with a SQL Server login, an empty result set is returned.
  If account_name cannot be identified as a valid Windows user or group,
  an error message is returned.


Can Oracle databases start automatically when Windows machine is booted/rebooted? In one of my client machines every time Windows starts we have to start the Oracle database manually.
Is there a solution/ command to start the database automatically when the machine is started/restarted?

on Windows system you have to use oradim.exe to admnister the Oracle instance behavior.
This is the documentation:
Using ORADIM to Administer an Oracle Database Instance

You have to check:

if the Windows service exists (if not create it with oradim);
how it is configured (using oradim)


What data gets passed between mirroring principal and secondary? We are trying to figure out which of the below statements is correct when talking about SQL Server mirroring:

The master database server executes the query, and takes the resulting transactions and ships them over to the secondary server, which "rolls in" the transactions into its database.
The master database server executes the query and simultaneously writes the query to the log. The log contents are sent to the slave and the queries are executed as they were on the primary.

Update: I unfortunately couldn't modify the two statements to avoid tampering. I think because the word "simultaneously" is used in the second statement it leads people away from the focus of question.

The main difference between the two statements is that in Statement 1, the transaction log that is sent to the secondary includes every row change action (insert/update/delete) that RESULTED from the query. In statement 2, the transaction log is actually only passing the query, and then executing it on the secondary. The key difference is, if Statement 2 is correct and you're updating millions of rows, it has little impact on how much data is passed (only the original query). However, if Statement 1 is correct, then updating millions of rows will require millions of rows of data to be sent to the secondary.

As noted it is based on the mode mirroring is configured to, but overall the concept of mirroring you are probably closer with statement one.

However, as noted in the documentation for mirroring:

  Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.

So I would necessarily say it rolls in the transactions to the database on the secondary but it applies them to the secondary. Then depending on which mode you are in would be when the transaction is committed to the database on the primary (synchronous or asynchronous).

So I would probably state it as:

  The primary server reads the transaction log of the mirrored database for a insert/update/delete occurs, that transaction is sent to the secondary and applied to the mirroring database. Based on synchronous or asynchronous the transaction on the primary (or mirrored database) is then committed.


How do I interpret the log when I run DBCC TRACEON (3502, 3504, 3605, -1) I have been using DBCC Traceon (3502, 3504, 3605, -1) because it was recommended in a blog for discovering performance issues related to I/O.  I'm running MS SQL Server 2008 R2 SP1

Results in my SQL Log file look something like this (numbers fudged a little):

  about to log checkpoint end

  last target outstanding 2, avgWriteLatency 40ms

  Average Throughput: 0.67 MB/sec, I/O Saturation: 79, Context Switches
  201

  FlushCache: cleaned up 125 Bufs with 69 writes, in 1447ms (avoided 0
  new dirty bufs)

  Ckpt dbid 9 phase 1 ended (8)

  about to log checkpoint begin.

I don't really know how to read this, or break it down in a way that I get get anything truly meaningful out of it.

What does 'last target outstanding mean?"

Does the average write latency mean the overhead time it takes per write?  or the time between writes?  40ms seems high, the physical drive is a 1TB, and it's RAID5 configured.

What is I/O saturation?

What does it have to do with the Context Switches.  I'm assuming Context switches have something to do with multi-tasking.  Changing between jobs/writes.

FlushCache.  I realize this has to do with clearing out the cache.  What are the Bufs? Are these pages of data that needed to be written?  What are the dirty Bufs?  Why would they be avoided?

A detailed breakdown would be appreciated.

The trace flags that you have turned on will tell you what a checkpoint is doing behind the scenes.

3502: writes to the error log when a checkpoint starts and finishes
3504: writes to the error log information about what is written to disk
3605: allows trace prints to go to the error log

Refer to Paul Randall's blog post for more details on the above. Also, Fine Tuning for Optimal Performance has an excellent info - especially In Search of Spikes section.

Some really internals reading :

How It Works: When is the FlushCache message added to SQL Server Error Log?
How It Works: SQL Server Checkpoint (FlushCache) Outstanding I/O Target
SQL Server checkpoint problems

Instead of concentrating directly on checkpoint behaviour, I would suggest you to look at DMVs and Perfmon (disk related) -

sys.dm_io_virtual_file_stats
Physical Disk Object: Avg. Disk Queue Length
Avg. Disk Sec/Read
Avg. Disk Sec/Write
Physical Disk: %Disk Time
Avg. Disk Reads/Sec
Avg. Disk Writes/Sec

You can refer to Investigating I/O bottlenecks


Postgres fetch exact value when satisfied all conditions Here are the table structure and sample data.

CREATE TABLE public.product (
                id serial NOT NULL,
                opid int4 NULL,
                opvalue int4 NULL,
                info varchar NULL,
                CONSTRAINT product_pkey PRIMARY KEY (id)
            );

INSERT INTO product (id,opid,opvalue,info) VALUES
            (1,1,1,'s1')
            ,(2,1,1,'s2')
            ,(3,2,1,'s2')
            ,(4,1,1,'s3')
            ,(6,3,2,'s3')
            ,(5,2,1,'s3')
            ;

Now I want query if info value satisfied all it's condition value then it will be returned.
Ex.If I pass opid=1 and opvalue=1 then only s1 should be returned not s2 because s2 have it's different condition. s1 should be returned when I pass (opid=1 and opvalue =1) and (opid=2 and opvalue =1)

I think this does what you want:

select *
from product p
where opid = 1 and opvalue = 1
and not exists (
  select *
  from product x
  where x.info = p.info
  and (x.opid != p.opid or x.opvalue != p.opvalue)
);

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1efcb7a125d52c27b2c386307d37da5b


Active Directory authentication with SQL Server on Linux containers I have  a problem and hope someone can help me. I have SQL Server as docker container. I want our developers able to login in SSMS (SQL Server Management Studio) using their Active Directory users. I followed this procedures which was Microsoft Doc page but it didn't work. The log file in container shows about error:

Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [CLIENT: 192.168.1.52]
Error: 17806, Severity: 20, State: 14.
SSPI handshake failed with error code 0x80090304, state 14 while establishing a connection with integrated security; the connection has been closed.
Reason: AcceptSecurityContext failed. The operating system error code indicates the cause of failure. The Local Security Authority cannot be contacted [CLIENT: 192.168.1.52]

My container run successfully and I can connect to it using sa user but, I can't login using Windows Authentication.
This is my docker run command:
# docker run -itd -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<pass>" -p 1433:1433 --name msdb \
--user root \
-v /root/mssql:/var/opt/mssql/ \
-v /root/mssql/krb5.conf:/etc/krb5.conf \
--dns-search <full domain> \
--dns 192.168.1.49 \
--add-host <dc.mydomain>:192.168.1.49 \
--add-host <mydomain>:192.168.1.49 \
--add-host <my domain.com>:192.168.1.49 mcr.microsoft.com/mssql/server

Also, I checked this checklists but the problem didn't resolve and I got the same error again.
I hope some one can help me how can I resolve it.
Thanks

@Martin Cairry, Yes, client is windows 10 and its joined the domain, ssms verion is 18.5. all users have domain admin privilege. Also when I login to ssms with sa user, I can add my domain user in login section but it can't login using windows authentication.


How to avoid server crash during backup of dabase using a crontab I'm on shared hosting, so I want to avoid using too much system resources.
I have a crontab that schedules a MySQL database backup. The database is about 2gb before compression. When that crontab runs, the system seems to crash, because the http server stops for a minute or 2.

Is there anything that I can add or edit to my command that would run the db backup job gradually or in smaller steps to avoid server crashes?

Here is the command that causes the problem:

mysqldump --add-drop-table --user=xxxxxxxxx --password='xxxxxxxxxxxx' db_name | gzip >/path/to/my/backup_directory/db_backup.dmp.gz

Any tips please? or should I ask my host to change any settings in my account?

Try using the -q option of mysqldump to see if it could help. Otherwise, try using multiple mysqldumps specifying individual tables.


Sanitizing a form's database input while retaining basic HTML markup Is there a way to sanitize database input (say, for a comment form), which also allows for the end user to use basic HTML styling markup? As in, if a user wraps their text in <i>/<em> or <strong>/<bold>, the text gets styled, all while preventing users from entering malicious PHP and SQL commands?

There have been a few great discussions of this at Stack Overflow.  This one covers methods and processes, but don't try to create a catch-all function as a replacement for understanding your data flows.

For your particular problem, it looks like HTML Purifier might be a good fit.  That way, you could whitelist very specific HTML functionality (e.g., strong, ol, em) to prevent XSS.  In addition, you can parameterize your queries (check out Bobby Tables for PHP specifics) to prevent SQL injection.

I wouldn't think of these two as one process, though, because they're really useful at different points in your application lifecycle:  parameterizing your SQL queries is important when you're about to make a SQL call, but XSS protection is important when displaying the saved data in a Javascript-equipped browser (as opposed to saving text to the database).


How can I create a stored procedure using xp_fixeddrives? I need a stored procedure to check the free space in 2 linked servers .(the servers are linked manually) .I have comed up with a query like this and using sql server 2008

ALTER PROCEDURE freespace
AS
BEGIN
    CREATE Table #freespace(drive VARCHAR(50),freedrivespace VARCHAR(50));
    DECLARE @servername VARCHAR(50);
    DECLARE @sql VARCHAR(100);
    DECLARE @space  CURSOR;
    SET @space= CURSOR FOR
    SELECT Name FROM SERVERNAME;
    OPEN @space;
    FETCH NEXT
    FROM @space INTO @servername;
    WHILE @@FETCH_STATUS=0
    BEGIN

    SET @sql = '['+@servername+']...xp_fixeddrives';
    INSERT INTO #freespace
    EXEC(@sql);
    FETCH NEXT FROM @space INTO @servername;
    END
    CLOSE @space;
    DEALLOCATE @space;
END

The servername table consist of servername of linked servers like server1, server2.

When I run this query an error like this is displayed:

Msg 7202, Level 11, State 2, Line 1
Could not find server 'server1' in sys.servers. Verify that the correct server name
was specified. If necessary, execute the stored procedure sp_addlinkedserver to add
the server to sys.servers.

 (0 row(s) affected)

OLE DB provider "SQLNCLI10" for linked server "server1" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked
server "server1" was unable to begin a distributed transaction.

I think the problem is with the dynamic SQL.  I've tried many changes to this query, but still I get this error.

Update

now I need to include the servername also into the table for that if i created an extra column 'servername' and used this query for inserting values

SET @sql = insert into #freespace values(@servername,['+@servername+']...xp_fixeddrives');

EXEC(@sql);

But it shows an error ,please help

I've recreated your scenario on my test server that has 2 linked servers that were created manually.

CREATE TABLE SERVERNAME
(
    [Name] nvarchar(255)
);

INSERT INTO SERVERNAME ([Name]) SELECT name from SYS.SERVERS;
GO

CREATE PROCEDURE freespace
AS
BEGIN
    CREATE TABLE #freespace
    (
        drive VARCHAR(50)
        , freedrivespace VARCHAR(50)
    );
    DECLARE @servername VARCHAR(50);
    DECLARE @sql VARCHAR(100);
    DECLARE curspace CURSOR LOCAL FORWARD_ONLY FOR
    SELECT [Name] FROM SERVERNAME;
    OPEN curspace;
    FETCH NEXT FROM curspace INTO @servername;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = '[' + @servername + ']...xp_fixeddrives';
        INSERT INTO #freespace
        EXEC @sql;
        FETCH NEXT FROM curspace INTO @servername;
    END
    CLOSE curspace;
    DEALLOCATE curspace;
    SELECT * FROM #freespace;
END
GO

EXEC FREESPACE;

The results for my server are:

drive   freedrivespace
C       75305
I       277458
K       861878
C       24378
D       90630
F       7575392
G       972578
H       768495
I       50952
J       7150657
C       13799
D       25162
F       6745607
G       982818
H       580167
I       50881
J       7224593

Granted the results are not exactly usable since the name of the server is not shown, however it does work.  This tells me the problem is not in your dynamic SQL; the problem lies with your linked server configuration.

Are you able to manually run a simple query such as:

SELECT * FROM [server1].master.sys.tables;

This should either return results, or most likely the same error you are seeing above.


Postgres 9.6 Physical and Logical replication I am working with Postgres9.6 with a Master/Slave cluster replication using Streaming replication (physical).

I would like to add a new Slave server database but this database with logical replication.

I tried with some configurations but it was not possible  :(
https://github.com/guedim/postgres-streaming-replication

Here is the image of what is in my mind:

I would suggest having the same wal_level on all servers, as as it defines the amount of information you will have when WAL is created (on the primary, these files are then streamed to the standbys).

The available wal_level settings (with each setting writing more information to each WAL, as well as all that of lower levels) from low to high are:

minimal
replica (older values archive and hot_standby are mapped to this)
logical

In this case I would set wal_level = logical for all. As WAL is only created in one place this won't hurt, and it has the added benefit of keeping working if you ever promote a standby to a master.

You mention hot_standby in the diagram, this has now been renamed replica (although both still work).

Other than that this topology should be fine, we use something like this - although you have logical replication from the standby, you'll need to move this to coming from the master. There was a rejected patch to allow this to come from a standby in PostgreSQL 10, hopefully this comes through in PostgreSQL 11.


Manually deciding locking mode in MSSQL In MSSQL, when a lock is acquired with a statement, the locking mode is decided by the engine not by the programmer. Is there a way to force the engine to use a specified Locking Mode (i.e. Row Lock)?

Sure, there are plenty of table hints, like:

PAGLOCK
ROWLOCK
TABLOCK
TABLOCKX

However, you should use these with extreme caution. There are very few cases where the average programmer knows better than the engine, and forcing hints can cause more problems than they solve.


Extract all rows until and including I would like to extract all the rows from a table until and including check_type = 'schedule' and checked_at < '2016-09-26', in this situation I don't have a lower bound for checked_at, the lower bound could be the checked_at value of the first row which has the type scheduled, but I don't see how I can get that.

CREATE TABLE project_check_history (
  id serial PRIMARY KEY,
  project_id int4,
  check_type varchar,
  checked_at timestamp
);

INSERT INTO project_check_history(project_id, check_type, checked_at)
  VALUES
      (1, 'instant', '2016-09-26'),
      (1, 'instant', '2016-09-25'),
      (1, 'scheduled', '2016-09-24'),
      (1, 'instant', '2016-09-23'),
      (1, 'scheduled', '2016-09-23');
      (1, 'instant', '2016-09-22'),
      (1, 'scheduled', '2016-09-21');

This is part of a bigger question here, but I'm really stuck, and don't see how I can do it.

Expected output it will be:

checked_at
2016-09-26
2016-09-25
2016-09-24

This will do what you want. If there is no row with check_type = 'scheduled' in the specified interval, it will return no rows at all. If you want all the rows instead, it will have to be slightly modified:

WITH find_schedule AS
  ( SELECT created_at
    FROM project_check_history
    WHERE check_type = 'scheduled'
      AND checked_at <= '2016-09-26'
    ORDER BY checked_at DESC
    LIMIT 1
  )
SELECT  -- DISTINCT
    created_at
FROM project_check_history
WHERE checked_at <= '2016-09-26'
  AND checked_at >= (TABLE first_schedule) ;


PHP MySQLi Lock Tables Query Sometimes Does Not Complete I am attempting to lock some tables while running some code and unlocking the tables when I am done.

Process:

Run Lock Tables MySQL Query.
Run some PHP Code.
Run Unlock Tables MySQL Query.

While running this process, 9 times out of 10 everything runs perfectly. Sometimes, when I run my query no response comes back from MySQL and PHP just waits for a response. Since no response happens, I never get to steps 2 or 3 and the table stays locked indefinitely. I am running the exact same Lock Tables Query in every single attempt.

After some research, I found that the problem happens when a Second Server writes to the database and then the First Server tries to do a "LOCK TABLES" query. It appears that the Second Server's write does a "metadata lock". I believe this is due to MySQL having the "autocommit" feature enabled and the transaction is not yet completed. So when I attempt to lock the tables, MySQL never responds and the "show processlist;" has "Waiting for table metadata lock | LOCK TABLES ..." listed in it until I manually kill the process.

I am locking Numerous tables. Below is a similar example to my actual table query. I am using the same table multiple times with different aliases based on the queries I am attempting to prevent from accessing the tables.

$sql = "LOCK TABLES table1 as t1 WRITE
                    , table2 as t2 WRITE
                    , table3 WRITE
                    , table2 WRITE
                    , table4 WRITE
                    , table1 WRITE
                    , table5 WRITE
                    , table5 as t5 WRITE
                    , table6 as t6 WRITE
                    , table7 as t7 WRITE
                    , table7 WRITE
                    , table8 as t8 WRITE
                    , table9 t9 WRITE
                    , table10 t10 WRITE
                    , table11 t11 WRITE
                    , table12 WRITE;";

$this->mysqli = new mysqli(
    $this->credentials->server
    , $this->credentials->user
    , $this->credentials->password
    , $this->credentials->database
);

try{
    error_log('before first attempt lock tables '.$sql);
    $this->mysqli->query($sql);
    error_log('after first attempt lock tables');
} catch (Exception $e){
    error_log('Error locking tables: '.$e->getMessage());
}

error_log('After try catch.');
if($this->mysqli->error){
    error_log('lock table error: '.$this->mysqli->error);
}

When the process fails, I see "before first attempt lock tables" in my PHP  Error log. I do not see any of the other error_log() calls. After some checking, I determined it is because PHP has not received a response from MySQL.

I never get into the Catch Exception, since MySQL is not returning an error. MySQL is not returning anything unless I manually kill the MySQL Lock Tables Process.

If I don't kill the process, the PHP code never stops waiting for a response from mysql.

I am confused why the "LOCK TABLES" query is not gracefully waiting for the "metadata lock" to complete and then running. It was my understanding that more than one server was allowed to write to a database and the lock table features would work together instead of one hanging indefinitely.

Gag!  LOCK TABLES is essentially unnecessary if you are using ENGINE=InnoDB.  If you are still using MyISAM, convert!

After converting, you will need to understand how to put things in "transactions" (BEGIN...COMMIT) and use SELECT ... FOR UPDATE.


Building a custom database in C with/without SQL? Provide link or tutorials to build a custom database management in C. I am looking for Full Text Search options of about 10GB DB.

You many want to look into using the Sphinx Search tools.

It allows you to create a MySQL instance that runs on port 9036 and only stores indexing info along with keys into real MySQL databases/tables.

There is a third party MySQL Storage Engine called SphinxSE.

Theoretically, the keys can you store in Sphinx do not have to be for MySQL. They could be for any DBMS. Sphinx is just a good key store that is built for FULLTEXT indexing.

Now, if you need to access the Sphinx DB using C/C++, then you need CodeBase.


Mongodb Cross-region failover replication aws I have deployed 3 MongoDB nodes(node-1, node-2, node-3) in AWS. The first two nodes (node-1, node-2) are deployed in Virginia region and the third node(node-3) is deployed in Oregon region and enabled VPC peering and the replication is working fine.

Current Scenario ::

Virginia : Node-1 (Primary) and Node-2 (Secondary)

Oregon: Node-3 (Secondary)

Failover Scenario::

if the Virginia region goes down,

1. Both the Node-1 and Node-2 will also be down.
2. Third node (Node-3) has to be Primary

And the Virginia region comes back,

1. Node-1 and Node-2 has to be secondary
2. Node-3 has to continue being primary

But, Now if the Virginia goes down the third node(node-3) is still in Secondary. I have to manually make the node as Primary. Is there any automated way or alternate solution to do this.

I have deployed 3 MongoDB nodes(node-1, node-2, node-3) in AWS. The
  first two nodes (node-1, node-2) are deployed in Virginia region and
  the third node(node-3) is deployed in Oregon region and enabled VPC
  peering and the replication is working fine . But, Now if the Virginia
  goes down the third node(node-3) is still in Secondary. I have to
  manually make the node as Primary. Is there any automated way or
  alternate solution to do this.

Yes, As per MongoDB Blog documentation here MongoDB Atlas ensures high availability with a fault-tolerant and self-healing architecture. Replica set members are distributed across availability zones in a customer’s selected region; should a node fail, the election and failover process happen automatically without intervention. You can also choose from 41 regions across AWS, Azure, GCP, ensuring that your managed databases are in close proximity to your application servers for minimal geographic latency.

You can deploy across multiple regions for better availability guarantees, meaning that replica set members in additional regions will participate in the election and automated failover process should your primary fail. Note that you can also select your preferred region; this is the region where reads and writes will default to assuming that there are no active failure or failover conditions.

You can also deploy read-only replica set members. Read-only replica set members allow you to optimize for local reads (reduce read latency) across different geographic regions using a single MongoDB deployment. As the name suggests, these replica set members will not participate in the election and failover process and can never be elected to a primary replica set member.

For further your ref here and here


How do I check if a certain SQL statement is being run? Using SQL Server Management Studio, how can check or see who is running a certain SQL statement?

In SQL Server Management Studio you can use Activity Monitor. Right click on the root server item in Object Explorer or from the top panel. The Processes section contains info about the callers.


10.4.10-MariaDB Column 'authentication_string' is not updatable So I just installed mariadb in a new server as always, but it seems none of my commands I used to set root password and allow remote root logins work anymore and googling isn't helping me much.

mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES;"

mysql -u root -e "use mysql;update user set authentication_string=password('password') where user='root';FLUSH PRIVILEGES;"

mysql -u root -e "use mysql;UPDATE user SET plugin='mysql_native_password' WHERE User='root';FLUSH PRIVILEGES;"

This is what I used on my ssh terminal to allow remote access to root using a password. How can I do this in this new version?

You are suppose to run mysql_upgrade command for Mariadb instance. Command is as following :

mysql_upgrade [--force] [--user=# --password
  --host=hostname --port=# --socket=#
  --protocol=tcp|socket|pipe|memory
  --verbose]


Setting connection properties (application_name) for Postgres connections I want to attach an application name to my connection like PgAdmin does here:

(Query in text form: select pid,query,wait_event,state,* from pg_stat_activity;)

My connections are created this way (here in VBA):

Dim cnDB As New ADODB.Connection
cnDB.Open "PostgreSQLDsnHere"

Where that DSN is created by:

for 32 bit: go here: %WINDIR%\SysWOW64\odbcad32.exe
for 64 bit: go here: %windir%\system32\odbcad32.exe
Go to the System DSN tab and enter the information (Data Source,
Database,Server IP, User Name, Password, Port)

I could not figure out how to add the application_name in this context/connection setup (using a DSN).

I tried adding this in various places in my VBA code, but it didn't work:

cnDB.Properties("ApplicationName") = "testing"

Where do I enter/set the application_name?

I know I can set the setting if I use a standard plain text connection string, but I cannot do that for security reasons. I have to use a DSN.

Non-VBA answers very welcome!

If you can't configure it via the DSN itself, you can always have your connection establishing subroutine set the application name by executing a SQL command on the connection before it hands that connection back to the caller:

set application_name to 'my application name';

In VBScript, this looks like

dim cn
set cn = CreateObject("ADODB.Connection")
cn.ConnectionString = "DSN=PostgreSQL35WDsnHere"
cn.open
cn.execute("set application_name to 'my_application_name';")


Biztalk in alwayson SQL 2019 Good morning all
I am working on a Biztalk migration project On an alwayson SQL environment
Who has an idea if BIztalk is compatible with ALWAYSON if YES it is from Which version?
another question for the activation of the MSDTSC function should you activate this option in Always on
thank you for your help

Biztalk is compatible with ALWAYS ON from SQL Sever 2016: https://docs.microsoft.com/en-us/biztalk/core/high-availability-using-sql-server-always-on-availability-groups
You have here a brief introduction and a setup of Microsoft Distributed Transaction (MS DTS). Microsoft says that is available starting from SQL Server 2016 Service Pack 2.
I found a link that is talking about best practice and is mentioning Biztalk which is exaclty your scenario but.... it doesn't give any example about how to setup MS DTS with Bitztalk.


SQLBulkCopy & InMemory tables helping out on a project where the data loading is done using C# & SQLBulkCopy to normal rowstore tables - does anyone know if there are any issues or gotchas with continuing with SQLBulkCopy but swapping the target table to be an InMemory table ? Is this even possible ?
Have tried searching but going around in circles

It is possible to use an In-Memory Table with the SQLBulkCopy class. I'm not aware of any additional gotchas but did come across this Git Issue in terms of ensuring you properly configure the options of it (depending on the size of your table).
You guys should more importantly understand the differences between an In-Memory Table vs a regular Table, specifically with regards to locks and retry logic for In-Memory Tables being different, which may be important to consider and cause you to make additional changes to your application.
Additional resources for In-Memory Tables:

Microsoft Docs - Memory Optimized Tables

Microsoft Docs - Transactions with Memory Optimized Tables


Order by reduces performance of a grouped result set I'm trying to optimize a query which uses a combination of GROUP BY and ORDER BY which together cause a big performance hit (~x15). If I remove either of the conditions, the query runs pretty fast. I tried different indexing strategies and query structures, but have come up empty so far.

The original query looks like this:

SELECT `academies_calendar`.`day`, `academies_calendar`.`start`, `academies_calendar`.`duration`,
 `academies_calendar`.`title`, `academies_calendar`.`id`, TIME_TO_SEC(TIMEDIFF('00:16:00', academies_calendar.start)) AS `diff`,
 IF('00:16' BETWEEN academies_calendar.start - INTERVAL 60 MINUTE AND academies_calendar.start + INTERVAL (academies_calendar.duration + 60) MINUTE,1,0) AS `active`,
 IF('00:16' BETWEEN academies_calendar.start - INTERVAL 60 MINUTE AND academies_calendar.start + INTERVAL (academies_calendar.duration) MINUTE,1,0) AS `starting`,
 `academies_calendar`.`instructor_id`, CONCAT_WS(' ',academies_instructors.first_name,academies_instructors.last_name) AS `instructor`,
 COUNT(members_attendance.id) AS `attending`, GROUP_CONCAT( CONCAT_WS('>|<',members.first_name,members.last_name,members.academy_id,members.photo) SEPARATOR '<^>') AS `members`
FROM `academies_calendar`
LEFT JOIN `academies_instructors` ON academies_instructors.id=academies_calendar.instructor_id
LEFT JOIN `members_attendance` ON members_attendance.event_id=academies_calendar.id AND (DATE(members_attendance.checked_in)=CURDATE())
LEFT JOIN `members` ON members.id=members_attendance.member_id
WHERE (academies_calendar.academy_id=3)
AND academies_calendar.day=2
AND academies_calendar.start > CAST('00:16' as time) - INTERVAL (academies_calendar.duration + 60) MINUTE
GROUP BY `academies_calendar`.`id`
ORDER BY ISNULL(academies_calendar.start),academies_calendar.start ASC

It's pulling a bit of data and organizing it, but due to the filters that hit indexes, it's usually working on small set of data - 1-6 entries in the academies_calendar table.

The EXPLAIN results for that query:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  academies_calendar  ref     PRIMARY,academy_id  academy_id  6   const,const     5   Using where; Using temporary; Using filesort
1   SIMPLE  academies_instructors   eq_ref  PRIMARY     PRIMARY     4   maonrails.academies_calendar.instructor_id  1   NULL
1   SIMPLE  members_attendance  ref     event_id    event_id    5   maonrails.academies_calendar.id     5   Using where
1   SIMPLE  members     eq_ref  PRIMARY     PRIMARY     4   maonrails.members_attendance.member_id  1   Using where

The key used for the main table (academy_id) is a composite key over (academy_id,day,start). The rest are hitting indexes or primary keys. Only 5 rows are found for this specific query, and it complete in 0.015s

If I remove either the ORDER BY clause, or the GROUP_CONCAT() call, the query runs at around 0.0009s.

I'm not sure I understand this behavior - isn't the ORDER BY clause supposed to be applied after the WHERE clauses and GROUP clauses have been applied? why does sorting 5 rows increase query time by more than an order of magnitude? I get the same results if the query returns just 1 result, by the way.

What seems to be happening, is that the grouping multiplies the amount of time it needs to sort the result set by the amount of rows joined to it from the other tables. I tried to resolve that by replacing those joins with a subquery that groups the data and returns one result per row, but it did not have a significant impact on query run time.

Any ideas on how to optimize this query would be much appreciated!

Remove LEFT if you don't need it.
The ORDER BY has ISNULL(start), yet the WHERE clause will fail if start isNULL`.  Seems like something could be simplified.
Will the table(s) become so big that they cannot be cached in RAM?  If so, we can talk about the choice of PRIMARY KEY.  (I'll need to see SHOW CREATE TABLE for this discussion.)
The query, as it stands, is destined to take some time...

It needs to look at all index rows with academy_id=3 and day=2, then filter on start.  That is currently about 5 rows; will that increase when there is more data?  Will you end up with a LIMIT?

Will the test on start be a significant factor in the filtering?  If so we can discuss a 'lazy eval' technique.

Here's one thing that may help:  Remove the LEFT JOIN members and replace the GROUP_CONCAT... with:

( SELECT GROUP_CONCAT(...) FROM members AS m
      WHERE m.id = members_attendance.member_id ) AS members,

Also, change

(DATE(members_attendance.checked_in)=CURDATE())

to

members_attendance.checked_in >= CURDATE() AND
members_attendance.checked_in  < CURDATE() + INTERVAL 1 DAY

and add

INDEX(event_id, checked_in)  -- in that order

(unless that table has PRIMARY KEY(event_id) -- it would help if you showed us SHOW CREATE TABLE)

Explanation

Some of the question deals with the slowdown due to ORDER BY.  Let me walk you through some of the steps:

fetch things from the first table, filtering based on the WHERE
fetch things from JOINed tables, again filtering where appropriate
GROUP BY id -- Since the joins created an explosion of rows in a tmp table, but you wanted only one row per id, this shrinks things back down.  It may require a temp table and a sort.  (Note:  My suggestion on GROUP_CONCAT avoids some of the explosion.)
ORDER BY something_else -- If the ORDER BY had been on the same value(s), it could be subsumed into the GROUP BY.  But, since it is not, there needs to be another temp table and sort.

Caveat: "temp table" may or may not be entirely in RAM.  "sort" may or may not be in RAM.  If either of these hits disk, the query will be slower.

Caveat:  If any of the SELECTed columns are TEXT or BLOB, the temp table will be headed for disk.  (The whole story is more complex; let's see SHOW CREATE TABLE.)

It may be possible to get rid of all JOINs, such as my suggestion above.  If so, then you can get rid of the GROUP BY, thereby speeding things up.

Do what you can in the directions I suggest, then let's make another pass at it.  (There may need to be further index changes.)

<my-rant>
    3rd party software tries to help with DB abstraction,
    but that usually gets in the way of performance.
</my-rant>


Monitoring real-time statistics on PostgreSQL I need to monitor the DB performance on a distributed system with multiple databases. Some of the database servers use pgbouncer to pool connections and in those servers I can connect to pgbouncer and execute a SHOW STATS; command to retrieve certain statistics, I'm interested in the following:

 avg_req | avg_recv | avg_sent | avg_query
------------------------------------------
     563 |  1080453 |  2060908 |     57718

The columns means:

avg_req
Average requests per second in last stat period.
avg_recv
Average received (from clients) bytes per second.
avg_sent
Average sent (to clients) bytes per second.
avg_query
Average query duration in microseconds.

Now my question is, is there a way of gathering this information without using pgbouncer on a postgresql server?

I believe the answer here is "not exactly". Those stats are coming from pgbouncer itself, so that don't map directly to the postgres backends. That said, you can track connection, data, and query statistics in postgres, but the information won't align exactly, and will probably be formatted differently (ie. you're more likely to get a counter than a gauge, so you'll need to do some tracking/math yourself)


Update tag range on MongoDB range shard Let say I set a min and max key for shards using range tags. Later I find out that the tag range I defined will not balance. Then I have to change the tag range value.

I have searched MongoDB's documentation, which only mentions add and remove tags, nothing about updating or changing the tag range.

Then I experimented by updating the range values in the config database tag collection.

mongos> db.tags.find()
{ "_id" : { "ns" : "test.lab.range", "min" : { "_id" : 1 } }, "ns" : "test.lab.range", "min" : { "_id" : 1 }, "max" : { "_id" : 100 }, "tag" : "rangeTime1" }
{ "_id" : { "ns" : "test.lab.range", "min" : { "_id" : 100 } }, "ns" : "test.lab.range", "min" : { "_id" : 100 }, "max" : { "_id" : 200 }, "tag" : "rangeTime2" }
mongos> db.tags.update({_id:{ "ns" : "test.lab.range", "min" : { "_id" : 100 } }},{$set:{min : {_id : 150}}})
mongos> db.tags.update({_id:{ "ns" : "test.lab.range", "min" : { "_id" : 1 } }},{$set:{max : {_id : 150}}})
mongos> db.tags.find()
{ "_id" : { "ns" : "test.lab.range", "min" : { "_id" : 1 } }, "ns" : "test.lab.range", "min" : { "_id" : 1 }, "max" : { "_id" : 150 }, "tag" : "rangeTime1" }
{ "_id" : { "ns" : "test.lab.range", "min" : { "_id" : 100 } }, "max" : { "_id" : 200 }, "min" : { "_id" : 150 }, "ns" : "test.lab.range", "tag" : "rangeTime2" }

That worked well, but the tags id wasn't updated. Notice that even when min is updated to 150 at rangeTime2, id is not updated, which makes the tags collection seem inconsistent.

It is kind of dirty but sadly it is works.

Is this the right way to update tag range? Is there a better way?

New to MongoDB, but this is how I learned how to do the same thing following the MongoDB 202 class. There are other ways to balance traffic, such as moving, splitting and merging chunks. I haven't read anywhere that directly updating the tags collection in the config database was dirty or incorrect.

The code below is untested, and you will have to replace certain values like nameofShard and the minimum, maximum values. You will notice that I stop the balancer before modifying the tag ranges

// stop the balancer
sh.stopBalancer()

// remove the tags from the shards
sh.removeShardTag("nameofshardN", "rangeTime1")
sh.removeShardTag("nameofshardN+1", "rangeTime2")

// remove the existing tags
db.tags.remove({ "_id" : { "ns" : "testlab.range", "min" : { "_id" : 1 } }, "ns" : "testlab.range", "min" : { "_id" : 1 }, "max" : { "_id" : 100 }, "tag" : "rangeTime1" })
db.tags.remove({ "_id" : { "ns" : "testlab.range", "min" : { "_id" : 100 } }, "ns" : "testlab.range", "min" : { "_id" : 100 }, "max" : { "_id" : 200 }, "tag" : "rangeTime2" })

// Add new tags
sh.addTagRange("testlab.range", {_id : new_minimum_value}, {_id : new_maximum_value}, "rangeTime1");

sh.addTagRange("testlab.range", {_id : new_minimum_value}, {_id : new_maximum_value}, "rangeTime2");

// Add shardTag
sh.addShardTag("nameofshardN", "rangeTime1")
sh.addShardTag("nameofshardN+1", "RangeTime2")

// start balancer
sh.startBalancer()

sh.status() or mongostat --port 27017 --discover to verify balancing

test.lab is not a valid database name, so I changed my database name to testlab.

One additional remark about the _id field not being updated. When I query the tags collection like so

mongos> db.tags.find({}, {_id:1})
{ "_id" : { "ns" : "testlab.range", "min" : { "_id" : 1 } } }
{ "_id" : { "ns" : "testlab.range", "min" : { "_id" : 100 } } }

It shows that the _id field contains the min value or the tag range. If you are updating the tags collection and modifying the tags min field value, then wouldn't it also be reasonable to update the _id field's min value as well?


Unable to mirror a database SQL Server 2012 When trying to mirror a database using the following command

ALTER AVAILABILITY GROUP SQLAlwaysonGroup ADD DATABASE test0916aj8CJ

I get the following error

  Msg 1475, Level 16, State 105, Line 1
  Database "test0916aj8CJ" might contain bulk logged changes that have not been backed up. Take a log backup on the principal database or primary database. Then restore this backup either on the mirror database to enable database mirroring or on every secondary database to enable you to join it to the availability group.

Can this be done without backing the database? Or should I backup and then discard the backup. It is for a newly created db, so I do not need the backup anyway at this point.

I have tried the following...

BACKUP
DATABASE [test0916aj8CJ] TO DISK = N’NUL’
WITH COPY_ONLY, NOFORMAT, INIT,
NAME = N’test-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD
GO

but the above method did not work either.

Thanks

Its easy to repro the error that you got

Create database in full recovery mode on Primary.
Create database in full recovery mode in Secondary.
Launch GUI and try to configure mirroring between Primary and Secondary.

Below is the error you will get :

  Database "test_mirroring_kin" might contain bulk logged changes that have not been backed up. Take a log backup on the principal database or primary database. Then restore this backup either on the mirror database to enable database mirroring or on every secondary database to enable you to join it to the availability group. (Microsoft SQL Server, Error: 1475)

Lets understand what that error is :

You configured your database in FULL recovery mode and think that the database is indeed in FULL recovery mode.

The above is not true. After creating the database, if you don't do a FULL backup, even though the database is in FULL recovery mode, it is in pseudo-SIMPLE recovery

You can easily verify it using dbcc dbinfo --> dbi_dbbackupLSN having value of 0:0:0(0x00000000:00000000:0000) or using Paul Randal's script

dbcc traceon (3604)
go
dbcc dbinfo('test_mirroring_kin') with tableresults
go
dbcc traceoff (3604)

Edit: Even taking a first full backup with COPY_ONLY option does not establish a backup chain as well

backup database test_mirroring_kin
to disk = 'D:\test_mirroring_kin_FULL.bak'
with init, stats=10, COPY_ONLY

dbcc dbinfo --> dbi_dbbackupLSN is still having value of 0:0:0(0x00000000:00000000:0000). This means the database is still in pseudo-simple recovery mode.

What you need to do to resolve the above error ?

You need to take a full backup + one transaction log backup on primary and then restore it on secondary database with norecovery and then join the database in AG group or Mirroring.

As a side note and for completeness, for your script telling backup to NUL, read this blog post by Gail Shaw.


Huge gap in autoincrement primary key I Have normal InnoDB table with autoincremental primary key. There Is about 100 thousands records. Accidentally was saved record with ID 10 million. Other record continue in this row. Is any possible And safe solution how:

A) change some IDS (row started from 10 millions) to max ID from less row? Off course Is necessary to change foreign IDS too.
B) start to fill gap between 100 thousand to 10 million?

I would want it because of better readability.

Locate all tables with IDs in them (FOREIGN KEYs, etc).  Figure out the exact numbers for below (I took your 10M and 100K as exact.)
Take your application "down".
Disable FOREIGN KEYS
Run UPDATE tbl SET x_id = x_id - 9900000 WHERE x_id >= 10000000; for each such table.
Enable FOREIGN KEYS
Do ALTER TABLE ... AUTO_INCREMENT = ... (or otherwise make sure it gets lowered)
Bring your app back up.

It would be wise to experiment before launching into the entire task.  If you have a test platform do it all there.  If not...

Limit the damage (for testing):  WHERE x_id >= 10000000 AND x_id < 10000010 LIMIT 11 -- then check that the number of rows changed was no more than 10.  And verify that all actions on those users are still OK.  You still must go through all 6 steps, but if something breaks, you will have less damage to clean up.  And, in doing this small test, you will (hopefully) figure out all the UPDATEs that are needed.

To be more daring,...  Don't disable/enable the FKs.  Instead, figure out what order to do the UPDATEs in so that the FKs won't complain.  Verfiy that the CASCADEs will do what you need.  This also gives you more confidence that the values will be correctly fixed; that is, no dangling messed up FKs.

(A caveat on AUTO_INCREMENT - The handling of such has changed with 8.0; my steps may be changed a bit.)

As a Postlog, ask yourself whether FKs have been more a benefit or more a pain.


Change collation in easy way I'm a developer and sometime I face with restoring a DB with a different collation than the server.
Our application uses the tempdb too and as result some joins fail due of the different collation.
So my question is : does exist a tool or a standard approach to convert the DB without doing the entire process manually?
Often the collation is very similar changes by a different default on a newer edition of Sql Server such as SQL_Latin1_General_CP1_CI_AS vs Latin1_General_CP1_CI_AS, in these cases the process of export/import data should not be necessary.

Thanks

You should first attempt executing:

ALTER DATABASE [{db_name}] COLLATE {new_collation_name};

If you get errors, are they something simple to remove, then change the collation, then re-create, such as a small number of functions? If so, then do that.

If the errors related to items that cannot be easily changed, or are far too many things to change, then the next easiest way is the undocumented sqlservr.exe -q method. That does a simple meta-data change of the collation_id of each relevant column (relevant = column that can be changed; some system columns cannot be changed). This can lead to data corruption in VARCHAR data if the code page changes, but in your case the code page is not changing. I have documented what this command does here:

Changing the Collation of the SQL Server Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

If the instance has several databases on it that will not be changing, and/or if the instance-level collation itself will not be changing, then you can:

Create a temporary instance of the same version of SQL Server (be sure to specify a collation that is not the collation of the target instance where this database will ultimately be restored to).
Restore this one database to that temporary instance
Use the sqlservr.exe -q method to update this temporary instance to the instance being used by the target instance.
Backup / Detach this database from the temporary instance.
Restore / Attach this database into the target instance.
Assuming everything works as desired, stop and uninstall the temporary instance.


Alternatives to joins using Linked Server and OPENQUERY In my environment, we have a server that stores sales data and another server that stores our HR data. For some reports, we need to combine sales data with employee data. Our sales server contains various tables with millions of rows. The employee table on the HR server contains about 12,000 rows.

On the sales server we have the HR server setup as a Linked Server. For employee sales reporting, we run the main queries on the sales server. Typically this involves joining multiple tables on this server and a single join across to the HR server to the employee table.

As you probably suspect, we are having performance issues. I've tried using both four dot nation  and OPENQUERY.

I haven't seen a major difference in performance between the two. Both are bad. If the query optimizer picks a plan where the remote table is queried once, performance is acceptable, even if all 12,000 rows are returned. Unfortunately, I have seen execution plans where the remote table was hit thousands of times in a single query.

I've considered simply creating a local table and running a SQL Agent job to refresh it from the HR employee table every hour or so. Although not ideal, this would be acceptable because the HR employee table doesn't change all that often.

Are there any other approaches I should be considering to solve my performance issue?

You can perhaps play with EXECUTE AT vs. OPENQUERY, but what I see as critical is:

  If the query optimizer picks a plan where the remote table is queried once, performance is acceptable, even if all 12,000 rows are returned.

If this is both true now, and will be true after a few years of growth, and you've already restricted the amount of data returned as much as possible by doing as many joins and WHERE clause restrictions and even RTRIM if you have CHAR columns with trailing spaces in your OPENQUERY statement as you can, then at the beginning of your stored procedure/query batch, create a #temp table for the results you need and fill it with the OPENQUERY results.  Then use it in your actual queries and joins!

Use Profiler (reads, writes, cpu, duration columns) to check on overall performance with SQL:BatchCompleted and RPC:Completed events; in particular, see if various ways of indexing the #temp table yield a net gain.

Alternately, consider that instead of an agent job refreshing a permanent table willy-nilly, is there a guaranteed way of telling if the remote server's table has been changed, perhaps a rowcount to find deletes combined with a MAX(updatedate) or a MAX(column that's  INT IDENTITY and always autopopulates) that'll catch changes, inserts, and inserts equal in quantity to the deletes, or another way?

If so, then in your stored procedure/batch, instead of fetching all the data, just check to see if it's changed.  If it's changed, update your permanent local table.  If not, use your permanent local table as-is.  WARNING: be very careful not to deadlock yourself with this code if it's run by several people at close to the same time (i.e. "run at the same time" in layman's terms).

Also alternatively, put a trigger on the remote table that simply sets a flag table with a single row and BIT column (call it YourDataIsOutOfDate) to 1 on every DML statement on that remote table.  Then do the above "check for updates" technique, except that you need to lock the remote table, update your local table, update the YourDataIsOutOfDate remote table to be 0 again, and then unlock  (finish your transaction) the remote table.  Again, beware of deadlocking yourself, and this may run into remote transaction intricacies that could be a headache.  I don't recommend this, but include it for completeness.


How to conditionally stop a psql script (based on a variable value)? Let's consider the following example (from the start of a psql script):

\c :db_to_run_on

TRUNCATE the_most_important_table;
-- tried to avoid similarities to anything that exists out there

Now if it is run this by the command

psql [connection details] -v db_to_run_on=\'dev_database\'

then it just runs and the user is happy.  But what if (s)he decides to specify -v db_to_run_on=production_database?  (Let's assume that this can happen, just like people run rm -rf / # don't try this at home!!! ocassionally.)  Hopefully there is a fresh backup of that table...

So the question arises: how to check the variables passed to a script and stop further processing based on their value?

There is an option in psql which stops executing commands on error, this is ON_ERROR_STOP.  If we could raise an error somehow, this would do what we want.

The problem is that we have to test the variable and produce an error somehow.  Since one can't use control structures in psql (because there are none)*, my only idea was to use SQL for testing.  Well, producing an error conditionally is something which pl/pgsql is quite good at, so I wrote a function which would generate an error. I can now call this function from a simple CASE structure.  A simple example:

-- let's assume for clarity that there is no function with this name in the database
CREATE OR REPLACE FUNCTION error_generator()
RETURNS boolean AS
$body$
BEGIN
    RAISE 'Meaningful error message here';
    RETURN FALSE; -- just for aesthetical purposes
END;
$body$
LANGUAGE plpgsql;

\set ON_ERROR_STOP on

BEGIN;

-- test for the variable value
-- notice that if :var is not set, it fails as well (with a syntax error)
SELECT CASE WHEN 1 = :var THEN error_generator() ELSE TRUE END;

INSERT INTO test_table (integer_value, text_value)
VALUES (:var, 'something');

COMMIT;

*: You can use any shell commands after \! and conditionals of the shell, but since \! opens a new shell, executing anything there does not have any effect for the current psql script.


How to free up disk space? which logs/directories to clean? I want to free up the disk space on my Linux machine. I've drill down the space usage and found that the following directories have a big size

/u01/app/11.2.0/grid/cv/log
/u01/app/11.2.0/grid/log/diag/tnslsnr/r1n1/listener_scan2/alert (Contains xml files)
/u01/app/11.2.0/grid/rdbms/audit(Contains .aud files)
/home/oracle/oradiag_oracle/diag/clients/user_oracle/host_XXXXXXXXXX/alert(Contains xml files)
/u01/app/oracle/diag/rdbms/crimesys/crimesys1/alert (Contains xml files)

Can I delete contents from these directories? Note: I mean contents and not directories.

You can change the default retention policy of oracle log and trace files.

Source: https://streetkiter.wordpress.com/2011/04/06/do-you-really-need-one-year-old-logs-and-traces-for-your-oracle-database/

Relevant extract:
There are two parameters: SHORTP_POLICY (default 720 hours) and LONGP_POLICY (default 8720 hours). so SHORTP_POLICY is 30 days while LONGP_POLICY is 365 days.

From the commandline run "adrci". In adrci, use the following commands.

adrci> show homes
....(homes are listed)...
adrci> set home diag/....(the location)
adrci> show control
.... (current settings are listed) ....
adrci> set control (SHORTP_POLICY = 168)
adrci> set control (LONGP_POLICY = 720)

And now the traces are dropped after one week (SHORTP) or one month (LONGP).


Multi-Tenant (SaaS) Database Schema with Sharing Guys I just need your opinion which is the best approach to design a schema of a database for a SaaS application. I want it all on a single database,

At first I think I can simply put a context_id to all the tables and table values. This context_id is the id of the SaaS subscriber. And all my queries will be including a where clause to the context_id.

However I also want it flexible which I can provide sharing between subscribers. Say my application is a clinic management system. One practitioner is subscribed then he/she is actually collaborating to another subscriber in which they are sharing their records.

So bottomline is what can be the best design with the schema? Thank you so much

Often in a multi-tenant system there is little or no sharing (in fact it is often desirable that other clients can be completely unaware of the others' existence) in which case the simple context_id is sufficient. It is generally sufficient for a hierarchical tenant structure (companies within groups, resellers, etc.) too.

For simple sharing this might work:

ThingTable                                   AccessControl
============                                 =================
ThingID (PK)  <----------------------------  ThingID  (PK, FK)
Owner   (FK)  --.                       .--  TenantID (PK, FK)
Property 1      |     Tenants           |    AccessLevel
Property 2      |     =============     |
  ...           `-->  TenantID (PK)  <--'
Property N            Name
                      etc...

Where AccessLevel could be a simple boolean representing read-only/read+write, or a more complicated collection of properties. You could even remove the "Owner" column from your tables unless you want it as a convenience and include ownership as part of the data in the access control table, or if you have no concept of an owner the objects you are modelling you can do away with it completely (it is usual for the "owner" of an object to have greater control, such as being the only user/group able to chance the access control for other users/groups, but this can be implemented in your access control table without needing a specific owner column).

If you are tracking multiple types of Thing this way, you will need to abstract out the concept so that you have a single entity to point the foreign key in AccessControl at, like so:

ThingTable1                                          AccessControl
================                                     =================
ThingID (PK, FK)  --.                           .--  ThingID  (PK, FK)
Property 1          |                           |    TenantID (PK, FK)  --.
Property N          |                           |    AccessLevel          |
                    |                           |                         |
                    |                           |                         |
ThingTable2         |     Things                |                         |
================    |     =================     |                         |     Tenants
ThingID (PK, FK)  --+-->  ThingID      (PK)  <--'                         |     =============
Property 1          |     Owner        (FK)  -----------------------------+-->  TenantID (PK)
Property N          |     Shared Property 1                                     Name
                    |                                                           etc...
                    |
ThingTable3         |
================    |
ThingID (PK, FK)  --'
Property 1
Property N

Any properties common to all the "Thing" tables would go in the general "Thing" table along with the owner (if yuo are are noting the owner that way, of course).

You can get much more flexible than this: allowing for groups of tenants (from simple groups as used in Unix-a-like filesystems through to supporting hierarchical relationships), having control of which tenants or groups can see each other, ans so forth - but from your description I'm assuming such extra features would be overkill for your current needs so there would be little point complicating the design to include them.


How do I rescue SQL Agent jobs from a set of detached master, model, mdb files? I had a MSSQL (MSSQL 2008 R2) cluster that failed in a way that I couldn't bring it back up (long story, servers ended up in BSOD loops, I'm blaming Broadcom and myself).

I've SQL backups, and even better, I've got the detached MDF and LDF files as they were on the SAN before everything went wrong. I've now got the task of restoring these back to a newly built server.

The databases themselves, logins and SSIS packages aren't a problem, as I've got all these. The thing that's bothering me is the SQL Agent jobs that were defined on the previous server. I'm dimly aware that these are stored in MSDB somewhere, but what steps should I go through to retreive these from the MDF/LDF files I've already got and load them on the new server?

I'm not going to be restoring the modal, msdb, master and temp databases on the new server, so should I be looking to attach the old files under different names and pull job definitions out of them, or is there some other (better) way?

You will have to attach the MSDB DB under a different name like 'jobsdb' and pull the jobs out of jobsdb. The jobs will be stored under jobsdb.dbo.sysjobs and the job steps will be under jobsdb.dbo.sysjobsteps. They can be joined by the job_id column.

The schedules can be found under jobsdb.dbo.sysjobschedules joined with jobsdb.dbo.sysschedules on schedule_id.

The rows can be added into your current msdb using a insert-select, or by using the stored procedures: sp_add_job, sp_add_jobstep, sp_add_jobschedule, sp_add_jobserver.


Shreded system_health ExEvent not returning all node information SQL Server is not returning all node information when I shred the deadlock XML from the 2012 system_health Extended event.

When I run the following query

SELECT  CAST(event_data.value('(event/data/value)[1]','varchar(max)') AS XML) AS DeadlockGraph
FROM    (
        SELECT  XEvent.query('.') AS event_data
        FROM    (    -- Cast the target_data to XML
                    SELECT    CAST(target_data AS XML) AS TargetData
                    FROM      sys.dm_xe_session_targets st
                    INNER JOIN
                            sys.dm_xe_sessions s
                            ON s.address = st.event_session_address
                    WHERE   name = 'system_health'
                    AND     target_name = 'ring_buffer'
                ) AS Data -- Split out the Event Nodes
        CROSS APPLY
                TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData ( XEvent )
        ) AS tab ( event_data )

at our Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 instance all node information is returned along with descriptions i.e.

<deadlock>
  <victim-list>
    <victimProcess id="process10e26bc8" />
  </victim-list>
<process-list>
<inputbuf>
Proc [Database Id = 26 Object Id = 1641213047]
</inputbuf>
</process-list>
  ...
...
...
</deadlock>

However when I run it against our Microsoft SQL Server 2012 - 11.0.5548.0 instance all node information and descriptions are stripped out and only the text is returned i.e

Proc [Database Id = 26 Object Id = 1641213047]

What might cause this? The full XML hierarchy is outputted at our 2012 instance if I remove the SELECT  CAST(event_data.value('(event/data/value)[1]','varchar(max)') part from the query.

According to http://www.red-gate.com/community/books/accidental-dba [Troubleshooting SQL Server: A Guide for the Accidental DBA] this looks to be by design.

Changes to Extended Events in SQL Server Denali
At the time of the
final edits of this chapter, SQL Server Denali CTP3 was released, with
changes associated to how the Extended Events targets store XML data
inside of the value element of the Event XML  output. Listing 7.5
shows the use of the .value() method from XML in SQL Server, but in
Denali CTP3, a .query() method has to be used to retrieve the
deadlock graph from the Event XML output.

Additional information : Extended Events Changes in SQL Server 2012 – Event XML for XML data elements


problem with service broker in always-on one of my clients has a database that works with service broker, last week we tried to move the database to an always on environment without knowing the application uses the service broker.
after there were complaints that it didn't work(scheduled meetings were not added to the calendar and did not close when old ones were done (its a clinic application)) we found Microsoft reference to service broker with always on and tried to use it:
 https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/service-broker-with-always-on-availability-groups-sql-server?view=sql-server-2017

but it still didnt work.
at first we tried

alter database [databasename] set enable_broker with rollback immediate

alter database [databasename] set new_broker with rollback immediate

and we received an error

  Msg 1468, Level 16, State 1, Line 2 The operation cannot be performed on database "dbname" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group. Msg 5069, Level 16, State 1

we also tried these commands:

 CREATE ENDPOINT [SSBEndpoint]

    STATE = STARTED

    AS TCP  (LISTENER_PORT = 4022, LISTENER_IP = ALL )

    FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS)

     GRANT CONNECT ON ENDPOINT::[SSBEndpoint] TO [PUBLIC]

ALTER ROUTE AutoCreatedLocal

   WITH ADDRESS = 'TCP://[server]:4022'  ;

at the end we didn't have a choice and we took it out of the always on group and after executing this command it worked:

   ALTER ROUTE AutoCreatedLocal
   WITH ADDRESS = 'LOCAL'  ;

does anyone have advise of what we are missing here on the configuration?
thanks.

I'm sure this is sorted now, but you have to enable service broker before creating the availability group.  This message suggests that you created the AG, then tried to configure service broker afterwards.


Get single records when duplicate records exist in 2 table I have 2 tables. TableA is the sales and TableB is the leads table. I'm trying to see if the people in the leads TableB made a purchase and their name appears
on the sales TableA. If they do I only want the name of people who appears
on both tables.

TableA (Sales)

 customer   school            email     address    state    phone   Sales
 john doe   hight middle    111@hu.z   111 updown   wi     2222222  $500
 john doe   hight middle    111@hu.z   111 updown   wi     2222222  $100
 zow hi     hight middle    222@hop.c  111 updown   wi     2222222  $200
 calispo    dwight elem     s@dw.co.za 34 jimm elis CA     35450456 $1,000
 Jordan     clift high       jp@hy.zut  1 unversity GA     11111111 $100

TableB (Leads)

 customer       school           email       address    state
 john doe      hight middle    111@hu.z    111 updown     wi
 Kawi          hight middle     k@nop.c     111 updown    wi
 calispo       dwight elem    s@dw.co.za   34 jimm elis   CA

What I want to see:

 customer   school           email       address    state
 john doe   hight middle    111@hu.z   111 updown   wi     2222222   $500
 alispo     dwight elem     s@dw.co.za 34 jimm elis CA     35450456 $1,000

I try this query but it is returning duplicate of the info that I want

 SELECT a.email as [sales_school]
  ,b.email[leadds_insti]
  ,a.*
  ,b.*
  FROM [dbo].[Sales] a
  right JOIN leads b ON a.email= b.email

You can try to find highest sale for each email and then join it with leads:

WITH maxsales AS
(SELECT
sales.*,
ROW_NUMBER() (PARTITION BY sales.email ORDER BY Sales.Sales DESC) AS rn
FROM [dbo].[Sales]
)
SELECT maxsales.email as [sales_school]
  ,leads.email[leadds_insti]
  ,maxsales .*
  ,leads.*
FROM maxsales
INNER JOIN leads ON leads.email= maxsales.email
WHERE maxsales.rn = 1


Benefits of reorging a new table with loaded data? Given the following scenario:
DB1:
EXPORT TO T.IXF OF IXF ... modified by codepage=... SELECT * FROM T;

DB2:
CREATE TABLE T (...) COMPRESS YES ADAPTIVE ORGANIZE BY ROW;
CREATE INDEX ... ON T (...) COMPRESS YES;
...
LOAD FROM T.IXF OF IXF INSERT INTO T (...);

Indexes are the same in DB1 and DB2, besides being compressed in DB2, and cluster ratio seems ok in DB1 (not sure if that matters). Code page differs between DB1 och DB2
Is there any point in doing a reorg after loading? I've found some articles from Z that seem to suggest that the way data is exported is of importance (since it where Z I did not read that carefully though so I don't know if that is relevant anymore on Z and/or LUW).
I tried on a fairly large number of tables, and did a REORGCHK after loading and everything seems fine. Comparing CALL GET_DBSIZE_INFO(?, ?, ?, 0) before and after REORG shows a slight decrease in size (<1%), but I don't know whether that is a coincidence.
Is there any particular situation where a REORG would be beneficial after loading into an empty table?

Db2 for LUW
REORG of compressed tables with RESETDICTIONARY might be beneficial after the initial load, because during the load the table-level compression dictionary is built using a sample of incoming data, which means that if your data patterns are unevenly distributed across the input file the dictionary might be suboptimal. REORG will use a larger sample (if not all of the data) to build the dictionary.
Other than that, REORG after LOAD is unneccesary.
Db2 for z/OS is different because in it every table would have a clustering index (implicitly defined if not declared explicitly), and if your input file is not ordered according to the target table's clustering index, clustering factor after the load will be poor.


Optimizing postgresql maintenance statements on psql In a reddit-like app (called Links) I maintain, users post hyperlinks and others can then publicly reply under the said links. The former is saved in a postgresql 9.3.10 table called links_link, the latter in a table called links_publicreply.

My task is to:

Delete all link objects created in a 30 day time window.
Delete all publicreply objects associated with objects in (1), and created within a 45 day time window.

Here's how I just did that for one time-window from 2016:

begin;
  delete from links_publicreply where submitted_on >= timestamp'2016-07-23 01:01:00' and submitted_on < timestamp'2016-09-07 01:00:00' and answer_to_id in (select id from links_link where submitted_on >=timestamp'2016-07-23 01:00:00' and submitted_on < timestamp'2016-08-23 01:00:00');
  delete from links_link where submitted_on >= timestamp'2016-07-23 01:00:00' and submitted_on < timestamp'2016-08-23 01:00:00';
commit;

Notice I'm querying links_link exactly the same way twice. I.e. once each in both delete statements. Now in Django ORM (of which I'm a native), I would have optimized this via first getting all the required links_link object ids separately, and then using them in all proceeding statements. How do I optimize that in the psql command line?

The most pertinent advice I've seen is in this SO answer, but it seems the with clause has to be inside the SQL statement it's being called in.

In other words, the following wouldn't work would it?

BEGIN;
  DELETE FROM links_publicreply
    WHERE submitted_on >= timestamp '2016-07-23 01:01:00'
        AND submitted_on < timestamp '2016-09-07 01:00:00'
        AND answer_to_id in (
            SELECT id
            FROM links_link
            WHERE submitted_on >= timestamp '2016-07-23 01:00:00'
                AND submitted_on < timestamp '2016-08-23 01:00:00'
    );
    DELETE FROM links_link
    WHERE submitted_on >= timestamp'2016-07-23 01:00:00'
        AND submitted_on < timestamp'2016-08-23 01:00:00';
COMMIT;

The most pertinent advice I've seen is in this SO answer, but it seems the with clause has to be inside the SQL statement it's being called in.

Yes, you can use a modifying WITH, i.e. a CTE that deletes from one table and returns the id values to the rest of the query, so they can be used to delete from the second table:

WITH
  links_link_deleted AS
    ( DELETE FROM links_link
        WHERE submitted_on >= timestamp'2016-07-23 01:00:00'
          AND submitted_on < timestamp'2016-08-23 01:00:00'
      RETURNING id
    )
DELETE FROM links_publicreply
  WHERE submitted_on >= timestamp '2016-07-23 01:01:00'
    AND submitted_on < timestamp '2016-09-07 01:00:00'
    AND answer_to_id IN
        ( SELECT id
          FROM links_link_deleted
        )
 ;

  In other words, the following wouldn't work would it?

That would work but it would be two statements.


Previous 5 Years Data based on a Specific Year I have a table like this:

ID|Date    |Name|Score
1 |1/1/2014|Erin|20.2
2 |6/3/2014|Erin|21.2
3 |7/2/2015|Emil|34.2
4 |1/1/2016|Erin|20.2
5 |2/2/2011|Paul|20.2
6 |1/1/2012|John|54.2
7 |1/1/2017|John|54.2
8 |12/31/2016|Erin|23.2

I want the data back 5 years based on the year I provide.
I am using this:

select * from tblStdnt where date > DATEADD(Year,DATEDIFF(year,0, '2016')- 5, 0)

But this gives me data starting from 2017. How do I fix this? any ideas?

It should give me:

  all data from 2011 to 2016 (the 5 year period, regardless of the month or day)

UPDATE
DBFiddle link


How to index WHERE (start_date >= '2013-12-15') I have a table named items, and inside the table there is a some_date column, with date as the datatype.

I was wondering how can I index the some_date column with PostgreSQL

SELECT "items".* FROM "items" WHERE (some_date >= '2013-12-15')

Bitmap Heap Scan on items  (cost=126.48..4671.78 rows=6459 width=384) (actual time=0.799..2.035 rows=7511 loops=1)
  Recheck Cond: (some_date >= '2014-03-30'::date)
  ->  Bitmap Index Scan on index_items_on_some_date_and_end_date  (cost=0.00..124.86 rows=6459 width=0) (actual time=0.744..0.744 rows=8777 loops=1)
        Index Cond: (some_date >= '2014-03-30'::date)
Total runtime: 2.439 ms

I've thought about partial index, but some_date is flexible in terms of the value used.

You just need to add a B-tree index to the some_date DB field. Partial indexes work, only if you know your query parameters and want to exclude / include particular ranges (http://www.postgresql.org/docs/current/static/indexes-partial.html).

Another alternative is to use table partitioning (http://www.postgresql.org/docs/current/static/ddl-partitioning.html) for the some_date DB field per day or month, so you may exclude huge number of rows easily.


Index on a partitioned key we have a list partitioned table. Let partitioned column be c1. We have an index on that column(c1). We are planning to drop the index. Our doubt is that if we drop the index, does it affect the performance? Suppose we are querying one of the partition. Does the oracle optimizer directly use the partition segment or rely on index(on the partitioned column) to choose the correct partition or does it perform a full table scan (going through all the segments) if we delete the index?

First, you can try to monitor index usage.
You will take the answer used index or not used.
Index monitoring can be switched on/off several times to get more information.

For example, here: https://oracle-base.com/articles/9i/performance-enhancements-9i#IndexMonitoring

Also, you can look into v$sql_plan view to see what recent queries used this index:

select s.sql_id, s.sql_text
  from v$sql_plan p, v$sql s
 where p.sql_id = s.sql_id
 and object_name = 'YOUR_INDEX_NAME_HERE' ;

Also, and most importantly, you can make the index invisible for the optimizer.
That way you can clearly see if it affects performance of your queries.

Examples - https://oracle-base.com/articles/11g/invisible-indexes-11gr1


How much does table size affect performance? Let's say I have a MyISAM table (amongst other tables) with 2938347 rows. This table is merely being used for logging and doesn't get emptied very often.

By not regularly archiving or deleting these rows, does having this table around affect server performance?

Then, what about tables/databases on a MySQL server that are massive in size, but aren't accessed all that often. These tables co-exist with frequently accessed/queried tables, and I'm wondering if they're causing problems or not.

Darius

This would depend upon the queries you issue (suppose the large table is mydb.logtable).
ASPECT #1
For starters, think about the MyISAM storage engine. It only caches index pages in the MyISAM key cache (sized by key_buffer_size). If any queries against mydb.logtable reads a significant number of index pages from /var/lib/mysql/mydb/logtable.MYI, the query mya purge out index pages from other tables. This would causes other queries for those other table to have to reread them index pages from disk again.
ASPECT #2
Any queries against a large MyISAM table that is infrequently accessed may have old index statistics, possibly resulting in full table scans down the road. During off hours, you should setup a crontab job that runs this SQL command:
ANALYZE TABLE mydb.logtable;

This will rebuild the index statistics.
ASPECT #3
Regardless of Storage Engine (or even RDBMS), any query that requests more that 5% of a table's total size would cause the MySQL Query optimizer to stop using indexes and do full table scans. For example, if you query data for a year and a table contains less than 20 years of log info, it is very probable that a full table scan will happen.
ASPECT #4
If the cardinality of the indexes is very low, a certain key combination could result in a query not using any indexes and do a full table scan.
For a demonstration of low cardinality affecting query performance and EXPLAIN plain generation, see my Nov 13, 2012 post Must an index cover all selected columns for it to be used for ORDER BY?
SUGGESTION
You should schedule some form of log rotation. Perhaps this:
SET @tb1 = 'mydb.logtable';
SET @tb2 = CONCAT(@tb1,'_',date_format(now(),'%Y%m%d_%H%i%s'));
SELECT CONCAT('ALTER TABLE ',@tb1,' RENAME ',@tb2) INTO @sql_1;
SELECT CONCAT('CREATE TABLE ',@tb1,' LIKE ',@tb2) INTO @sql_2;
PREPARE s FROM @sql_1; EXECUTE s; DEALLOCATE PREPARE s;
PREPARE s FROM @sql_2; EXECUTE s; DEALLOCATE PREPARE s;

This will keep the log file lean and mean and archive the most recent entries.


What happen when a transaction is committed and SNAPSHOT isolation is enabled? I am actually considering using SNAPSHOT isolation for one WCF service.
Before doing so, I would like to know how it works.

AFAIK, when it's enabled, updated rows are maintained in tempDB (instead of modifying data directly). Because of that, other readers (eg : that execute a SELECT) won't be blocked and will not see any change until transaction is committed.

What is not clear to me is what happen when transaction is committed.
I tried the following (on a huge table with SNAPSHOT enabled) :

1)

BEGIN TRANSACTION
UPDATE foo SET bar = 'something' --take a LOT of time, which is expected

2) (in another connection/tab)

SELECT * FROM foo --still works, because of SNAPSHOT isolation

3) (back to connection/tab started in 1.)

COMMIT  --very fast, almost immediate.

I expected that commit to be very slow, because I thought SQL would have to copy rows from tempDB back to the pages that contains the actual rows data.
Instead, it was almost  immediate. Why is it so ?

Also when transaction was still not committed, I took a look at tempDB but could not see any table or data (when a temporary table is created, for example, it's possible to see it in tempDB).

SQL Server will store the "before image" of the values in tempdb. And if you modify the same row several times, then you will have a chain of before-values (a linked list, essentially). The row also has information about a "logical timestamp" (so to speak) for when it is valid. This information is used by SQL Server to determine what data a SELECT should be used (as you might know, the true snapshot isolation gives us repeatable read - so a reader in the same transaction might still need to see the prior version even after the modifier did commit).

Then there is a clean-up process (one per minute, i believe) that sweeps the no longer needed versioned data and removes it from tempdb (aka "version store").

I.e., no need to copy anything at commit time, since it was the "real" data that was modified in the first place.


TempDB uses a lot of space in buffer pool I'm running a SQL Server 2014 with 2 cores and a max memory of 6GB. When I'm checking what's in the buffer pool, I see that TempDB uses 1GB of memory.
That's the query I used:

SELECT COUNT(*) AS cached_pages_count ,
(count(*) * 8.0)/1024 as MB,
        CASE database_id
          WHEN 32767 THEN 'ResourceDb'
          ELSE DB_NAME(database_id)
        END AS Database_name
    FROM sys.dm_os_buffer_descriptors
    GROUP BY DB_NAME(database_id) ,
        database_id
    ORDER BY cached_pages_count DESC;

But when I check what tables are in TempDB, there are only 40 tables and they use 728KB. This is the query:

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY  t.Name, s.Name, p.Rows
ORDER BY  t.Name

I also checked sys.dm_db_session_space_usage to see if there was a session that allocated all that space in TempDB

My buffer cache is 2.2GB and almost 50% is taken by TempDB

Can someone try to explain why TempDB is so large?

UPDATE
I did a DBCC PAGE of some pages found in the dm_os_buffer_descriptors and it looks like they are not allocated (GAM/SGAM Not Allocated, PFS 0_PCT_FULL)

  PAGE HEADER:

  Page @0x0000000DB026C000

  m_pageId = (1:159735)               m_headerVersion = 1
  m_type = 1 m_typeFlagBits = 0x0                m_level = 0
  m_flagBits = 0x20 m_objId (AllocUnitId.idObj) = -1948083318
  m_indexId (AllocUnitId.idInd) = 1 Metadata: AllocUnitId =
  435280365092864                                  Metadata: PartitionId
  = 0 Metadata: IndexId = -1              Metadata: ObjectId = 0              m_prevPage = (1:160115) m_nextPage = (1:160144)             pminlen =
  13                        m_slotCnt = 217 m_freeCnt = 3406
  m_freeData = 4352                   m_reservedCnt = 0 m_lsn =
  (13668:97392:89)            m_xactReserved = 0
  m_xdesId = (0:0) m_ghostRecCnt = 0                   m_tornBits = 0
  DB Frag ID = 1

  Allocation Status

  GAM (1:2) = NOT ALLOCATED           SGAM (1:3) = NOT ALLOCATED
  PFS (1:153672) = 0x0   0_PCT_FULL DIFF (1:6) = NOT CHANGED
  ML (1:7) = NOT MIN_LOGGED

That looks like a data page (page type=1) from a table variable or temp table (negative object id of -1948083318) in the leaf of a clustered index (indexid=1).

From the allocation information and the fact that this does not show up in your second query presumably the underlying object has been dropped. You may be able to get additional information about it, such as original object name, by looking for that allocation unit id in sys.fn_dblog if you're lucky.

If it belongs to a dropped object then one would certainly hope these pages are first candidates for putting on the free list.

However the article TempDB memory leak? and   associated connect item indicate that there might be a problem in this area...


Crash of VMWare Workstation when generating EER Diagram I'm facing an issue when I'm generating an EER diagram with MySQL Workbench 8.0
I'm using MySQL Workbench on a local Windows 10 virtual machine. My VM is running with VMWare Workstation 12.5.0.
I select some tables, a stored procedure and generate the diagram. In the progress dialog, I can see the Reverse Engineering is successful. When it comes to place Objects on Diagram, the virtual machine crashes and shows this message :

VMware Workstation unrecoverable error: (svga)
Exception 0xc0000005 (access violation) has occured

I found a way to prevent this crash.

In MySQL Workbench, go to Edit > Preferences...

Go to the Modeling section and tick "Force use of software based rendering for EER diagrams"

Restart MySQL Workbench.


ORA-00932 - Error when I try to filter all_triggers I'm trying to execute the next query:

SELECT * FROM all_triggers t
WHERE UPPER(trigger_name) like UPPER('%ABC%')
AND UPPER(TRIGGER_BODY) LIKE '%DEF%';

But I get the following error:

ORA-00932: inconsistent data types: expected CHAR has been obtained LONG

How can I avoid this error?

Best regards.

Maybe the simplest way is:

create table my_all_triggers as
select
    owner, trigger_name, trigger_type, triggering_event, table_owner,
    base_object_type, table_name, column_name, referencing_names, when_clause,
    status, description, action_type,
    to_lob(trigger_body) as trigger_body, --- <======== TO_LOB conversion
    crossedition, before_statement, before_row, after_row, after_statement,
    instead_of_row, fire_once, apply_server_only
from all_triggers
where upper(trigger_name) like upper('%ABC%') ;

select * from my_all_triggers t
where upper(trigger_body) like '%DEF%';

You can find the other methods using PL/SQL or XML functions here: working with long columns

Or just use ALL_SOURCE to find the trigger:

SELECT * FROM all_source t
WHERE type = 'TRIGGER' and UPPER(name) like UPPER('%ABC%')
AND UPPER(text) LIKE '%DEF%';