NoSQL or RDBMS for a Giant Lookup Table? What is the best way store leaked website credentials? I've come up with two that would get the job done for the most part, but I am trying to find the fastest and most efficient solution.

Take for example this site, which has a pretty basic search from the look of it, a more basic example would be the site haveibeenpwned. Doing a small scale test, so really I have two questions, first, would this be a good fit for a NoSQL style database? Or should I stick with a RDBMS? The second question is which structure would be able to return the results the fastest (and most completely)?

I could do it with something like this, but it would have lots of redundant information:

{   "uiid": "18c13cb5-bb91-41c6-8267-fd35e868d4c0",
    "username": "foouser",
    "email": "foo@bar.tld",
    "ip": "127.0.0.1",
    "fullname": "Koolaid Man",
    "phonenumber": "+1800123456",
        "creds":    [{
        "hash": "1ed6c8f1465d39e64055e18762fca52850244c9aed2158d6e63406693e775b4a",
        "algo": "sha256",
        "salt": "andpepper",
        "plaintext": "iesh0oow3ieg4thoweonga5aengeaGee3xahbohCuu6oog7oorei2jooPi1hooR6Shi9aoji7wu8ahreeG4see7Thothoo2ev7ch",
        "dateadded": "2012-04-23T18:25:43.511Z"
        },
        {
        "hash": "1ed6c8f1465d39e64055e18762fca52850244c9aed2158d6e63406693e775b4a",
        "algo": "sha256",
        "dateadded": "2012-04-23T18:25:43.511Z"
        }
        ]
}

Using a RDMS, I could so something like this:

CREATE TABLE `identities` (
    `uiid`  UUID NOT NULL UNIQUE,
    `email` INTEGER,
    `username`  INTEGER,
    `fullname`  INTEGER,
    `ip`    INTEGER,
    -- ...
);
CREATE TABLE `hashed_plain` (
    `uhid`  UUID NOT NULL UNIQUE,
    `type`  INTEGER,
    `hash`  TEXT NOT NULL UNIQUE,
    `salt`  INTEGER,
    `plain_text`    INTEGER NOT NULL,
    -- ...
);
CREATE TABLE `hash_instance` (
    `id`    UUID NOT NULL PRIMARY KEY UNIQUE,
    `uhid`  TEXT,
    `uiid`  TEXT,
    -- ...
);
-- ...

I know that both of these are not very good implementations, hopefully someone more knowledgeable than myself can point me in the right direction.

There Are Many "People" (by this I mean the information that identifies the user : username, email, ip ,name, etc.)
There are many "Keys" (by this I mean the information that validates the person: password, hash, salt, etc.)
Each Person can have many keys
Each Key can have many People

I want to be able to search by any field (with the exception of the UUID's) and return everything else associated with that piece of information.  What's the best way to do that?

As for NoSQL vs Relational, Postgres gives you the best of both but with the benefit of ACID. Along with its robust reliable relational engine, the JSONB data type allows you to save JSON data, internally stored in an optimized binary format that provides for indexing individual attributes. So you get the flexibility of less-structured data (JSON) with the very fast performance of Postgres indexed queries.

Also, Postgres supports UUID as a data type, storing the native 128-bit values. Support includes indexing and use as primary key.

As for your database structure, while I am do not know your data well, it appears from your example data that have a simple parent-child relationship between an "identity" or "login" or whatever you want to call it, and a "cred". On the cred table, for rows without a salt, just leave the extra fields null or with empty text ('').

Your description suggests you have a many-to-many between Person and Key entities. If so, in a relational database a many-to-many is always represented with a third table containing at lead two fields, the key of each table, so a pair of foreign keys. Classic example: Book table and Author table are connected by an Authorship table contains an Author ID and a Book ID for each author contributing to each book.

Tip: devise another name for your Key entity as the word key is, well, key to deigning and using a database. So, "key" is a confusing entity name.

Seems like you should be able to store all this data relationally rather than as JSON. But Postgres gives you both options.


How to grant xp_delete_file to non-sysadmin login? I want to delete old backups from shared folder, which are located on separate NAS storage, using newly created SQL login. Is there any workaround to execute xp_delete_file using another account which is not a member of sysadmin role?

Deleting files on NAS should not be done by sql server. You should rather use powershell to do it.

You can use my function Clean-NASOldFiles which uses robocopy to scan files extremely fast and will then delete based on your input.


How should I delete branch from adjacency list in SQL Server? Given a basic acyclic adjacency list like this:

What is the most effective way to handle deletions of branches?

My current approach uses a recursive CTE to find all descendant nodes and their depths (n). Then I iterate (ugh) over those depths in reverse and issue n deletes.

CREATE TABLE [dbo].[TreeNode](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentNodeId] [int] NULL,
    [Name] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_TreeNode] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TreeNode]  WITH CHECK ADD  CONSTRAINT [FK_TreeNode_TreeNode] FOREIGN KEY([ParentNodeId])
REFERENCES [dbo].[TreeNode] ([Id])
GO

ALTER TABLE [dbo].[TreeNode]  WITH CHECK ADD  CONSTRAINT [CK_TreeNode_Id_ParentNodeId] CHECK  (([Id]<>[ParentNodeId]))
GO

ON DELETE CASCADE won't work here because it isn't allowed in self-referencing FKs

No need for a procedure, just combine the CTE with a DELETE

with doomed as (
  select id, ParentNodeId
  from treenode
  where id = ... --<< start node of the branch to be deleted
  union all
  select c.id, c.ParentNodeId
  from treenode c
    join doomed p on c.parentnodeid = p.id
)
delete from treenode
where id in (select id from doomed);


Move multiple databases from C: to D: at once I have SQL Server 2008 R2 with 323 databases consuming some 14 GB on my C: drive, a fast SSD.

Because I want to reclaim some space on my C: drive, I would like to move them to my D: drive.

I have found this MSDN article, but that seems to be the procedure to move only one database.

Is there an automatic way or script to move all my databases at once?

I use Powershell for this sort of work.  In fact, I use Powershell to generate Powershell, because I have a script that will loop through my databases and generate my final move script.  You will have to move each database one at a time, but this will at least help you script 90% of the work.

#load SMO
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
#Added line if using SQL Server 2012 or later
Import-module SQLPS
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

#Create server object and output filename
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server "localhost"
$outputfile=([Environment]::GetFolderPath("MyDocuments"))+"\FileMover.ps1"

#set this for your new location
$newloc="X:\NewDBLocation"

#get your databases
$db_list=$server.Databases

#build initial script components
"Add-PSSnapin SqlServerCmdletSnapin100" > $outputfile
"Add-PSSnapin SqlServerProviderSnapin100" >> $outputfile
"Import-Module SQLPS" >> $outputfile
"[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') `"localhost`" | out-null" >> $outputfile
"`$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server " >> $outputfile

foreach($db_build in $db_list)
{
    #only process user databases
    if(!($db_build.IsSystemObject))
    {
        #script out all the file moves
        "#----------------------------------------------------------------------" >> $outputfile
        "`$db=`$server.Databases[`""+$db_build.Name+"`"]" >> $outputfile

        $dbchange = @()
        $robocpy =@()
        foreach ($fg in $db_build.Filegroups)
        {
            foreach($file in $fg.Files)
            {
                $shortfile=$file.Filename.Substring($file.Filename.LastIndexOf('\')+1)
                $oldloc=$file.Filename.Substring(0,$file.Filename.LastIndexOf('\'))
                $dbchange+="`$db.FileGroups[`""+$fg.Name+"`"].Files[`""+$file.Name+"`"].Filename=`"$newloc`\"+$shortfile+"`""
                $robocpy+="ROBOCOPY `"$oldloc`" `"$newloc`" $shortfile /copyall /mov"

            }
        }

        foreach($logfile in $db_build.LogFiles)
        {
            $shortfile=$logfile.Filename.Substring($logfile.Filename.LastIndexOf('\')+1)
            $oldloc=$logfile.Filename.Substring(0,$logfile.Filename.LastIndexOf('\'))
            $dbchange+="`$db.LogFiles[`""+$logfile.Name+"`"].Filename=`"$newloc`\"+$shortfile+"`""
            $robocpy+="ROBOCOPY `"$oldloc`" `"$newloc`" $shortfile /copyall /mov"
        }

        $dbchange+="`$db.Alter()"
        $dbchange+="Invoke-Sqlcmd -Query `"ALTER DATABASE ["+$db_build.Name+"] SET OFFLINE WITH ROLLBACK IMMEDIATE;`" -Database `"master`""

        $dbchange >> $outputfile
        $robocpy >> $outputfile

        "Invoke-Sqlcmd -Query `"ALTER DATABASE ["+$db_build.Name+"] SET ONLINE;`" -Database `"master`""  >> $outputfile
    }
}

The output will be a FileMover.ps1 script in your MyDocuments folder that looks something like this:

    Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100
    Import-Module SQLPS
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') "localhost" | out-null
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
    #----------------------------------------------------------------------
    $db=$server.Databases["AdventureWorks2012"]
    $db.FileGroups["PRIMARY"].Files["AdventureWorks2012_Data"].Filename="X:\NewDBLocation\AdventureWorks2012_Data.mdf"
    $db.LogFiles["AdventureWorks2012_Log"].Filename="X:\NewDBLocation\AdventureWorks2012_log.ldf"
    $db.Alter()
    Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012] SET OFFLINE WITH ROLLBACK IMMEDIATE;" -Database "master"
    ROBOCOPY "C:\DBData" "X:\NewDBLocation" AdventureWorks2012_Data.mdf /copyall /mov
    ROBOCOPY "C:\DBFiles\Log" "X:\NewDBLocation" AdventureWorks2012_log.ldf /copyall /mov
    Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012] SET ONLINE;" -Database "master"
    #----------------------------------------------------------------------
    $db=$server.Databases["AdventureWorks2012DW"]
    $db.FileGroups["PRIMARY"].Files["AdventureWorksDW2012_Data"].Filename="X:\NewDBLocation\AdventureWorksDW2012_Data.mdf"
    $db.LogFiles["AdventureWorksDW2012_Log"].Filename="X:\NewDBLocation\AdventureWorks2012DW_log.ldf"
    $db.Alter()
    Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012DW] SET OFFLINE WITH ROLLBACK IMMEDIATE;" -Database "master"
    ROBOCOPY "C:\DBData" "X:\NewDBLocation" AdventureWorksDW2012_Data.mdf /copyall /mov
    ROBOCOPY "C:\DBData" "X:\NewDBLocation" AdventureWorks2012DW_log.ldf /copyall /mov
    Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012DW] SET ONLINE;" -Database "master"

...

Caveats

The script moves all files, no matter their source location, to the
same destination.  You will need to tweak for custom location paths.
The script is designed to run on the server you need to move the
files on (see all usages of localhost').  Replace localhost with your
instance name if you run this remotely.
The user you run this as
needs access to all folder paths involved in the move, both for
updating the SQL server filename information and for moving the
files.
I use the InvokeSQLCmd for Offline/Online execution because of the funky nature of the .SetOffline() and .SetOnline methods.  I've found this to be more reliable.


MongoDB - Replication Oplog Window has gone below 1 hours I'm working with MongoDB Atlas and I have a cluster of 3 with M30 and 100gb of storage.

My current usecase is the following:
- An user dispatches one search in my platform
- The platform dispatches this search to other providers (12)
- I get like 2k documents per provider for this search

For the configuration, I'm working with Mongodb Atlas in a 3 replica-set cluster (M30 = 8 GB RAM • 100 GB storage). I also have an TTL index to expire the documents in the only database/collection I'm using to delete the documents after 10 minutes based on a date field in each document (searchStartedAt).

What I was trying to understand is this alert I'm getting ("Replication Oplog Window has gone below 1 hours"), if I got this right is how long the master node can continue receiving data before the slave nodes get out of sync.

I would like to check if my understanding is right and if there is any tunning possible to avoid this.

Can someone give me a hint? If any other information can help regarding this, please let me know and I'll update the question.

Thanks a lot for any tips regarding it.

If replication OpLog window goes under one hour, it means that timestamp between first and last  lines in the OpLog is under one hour. This can happen when there is lots of changes in the DB and your size of OpLog is "too small".
No, this is not fatal. It just means that your secondary nodes cannot "fallback" more than what is your OpLog window. I mean that if your window is f.ex. 55 minutes, you cannot stop your secondary node for over 55 minutes, because it cannot "catch-up" anymore and it needs to do "full sync" and if that full sync takes more than 55 minutes, it cannot be done.

What you can do is change OpLog size to bigger. If you double your OpLog size, you get double size window.


DELETE on a heap table Is there any way to deallocate empty pages on a heap table during a delete operation other than putting an exclusive lock on the table or rebuilding it? None of these options are acceptable for my production environment.

Sure, put a clustered index on it. Tables with a clustered index will automatically deallocate space.

Otherwise, you're looking at:

ALTER TABLE (mytablename) REBUILD - which takes it offline
Doing deletes with TABLOCK hints
TRUNCATE TABLE (mytablename)

I know some folks think it's trendy, but heaps just aren't a good fit for active OLTP systems that have to deal with deletes (which cause the empty space problem) and updates (which cause the forwarded fetches problem.)


How to keep the data warehouse synchronised with the source systems? I'm a DBA who is responsible for exporting data from an ERP system for Business Intelligence purposes. The data from ERP system is being sent to the data warehouse and they are used in the ETL process.

We used to run the export process at the end of the day. The queries which collect the data used the WHERE clause with a document creation/modification date = yesterday.

This mostly works just fine - there isn't too much data to export from the source system (less resource consuming on both source and destination systems).

But some source tables don't have the modification date column (only the create date).

At the moment I can see only 2 possibilities:

exporting all the records from the source system (very costly and impossible to mainain in a long run)
exporting records created in the last 7 days (less costly and pretty effective because older data is rarely modified)

I am looking for a better solution. I heard that SQL Server Integration Services have a MERGE JOIN and a LOOKUP transformations. I'm not sure if one of these provide the possibilities that I'm looking for.

What tool can you recommend? I'm sure it can be done efficiently in SSIS but I just don't know the right solution.

Change tracking offers the functionality you're looking for. When switched on, SQL Server creates additional internal tables.  Changes to your data tables are noted in these internal tables.  System functions allow you to pull changes out of these internal tables and transfer only changed rows.

A related technology - Change Data Capture - allows you to see the "before" and "after" values of changes.


Is it better to put filters on JOIN clauses instead of WHERE clauses MySQL 5.5.8:

Question 1:
Is there any difference in the following statements and if so, which is preferred performance wise? Can this even be answered without knowing the row counts of the tables before and after the where clause and possible index usage?

SELECT *
FROM a JOIN b ON a.id = b.id AND a.col2 BETWEEN 1 AND 5;

SELECT *
FROM a JOIN b ON a.id = b.id
WHERE a.col2 BETWEEN 1 AND 5;

My reason for asking is because if for instance the tables are massive but the where clause filters 99.9999% of the records out, I don't want to waste time joining massive tables if after the join 99.9999% of the records are not used.

Question 2:

Take the same situation, which statement (if any difference) requires less IOPS. (index present on b.id and one on a.col2)

With any good query planner (and IIRC mysql's is good enough in this regard) for queries where all joins are inner joins (as your's is here, unless otherwise specified joins default to inner joins), it does not matter whether you put the filtering or joining comparisons in an ON clause or in the WHERE.

In the presence of outer joins more complicated queries this is a lot less clear cut as the position of the filtering clause can (but does not always) have a significant effect depending on the input data.

To convince yourself, generate a large data-set and try both to see if there is any difference. As a learning exercise to get a good feel for what the optimisations in your chosen DB engine can and can't do, very little beats some well thought out experimenting, monitoring and benchmarking.


SQL Server Mirror: Witness Server(s) Inherited a new SQL estate that has never been touched by a full time DBA. Mirroring has been setup and removed on multiple servers but not fully, old witness servers are still active.

Is there anyway from the witness server to determine if a principle/mirror is still communicating or active.

I have picked through the DMV's with the DMV below being the most helpful but one of the four witness instances does report a connection, any advice would be helpful.

sys.dm_db_mirrroing_connections

Not exactly understand the need, but i guess below might help:

To get the information between Principal and mirror via witness query the dmv sys.database_mirroring_witnesses

You can get the info from columns

principal_server_name -- Name of partner server whose copy of the database is currently the principal database

mirror_server_name  --Name of the partner server whose copy of the database is currently the mirror database.

Also, check out the cool commands from dbatools Get-DbaDbMirror

PS C:\> Get-DbaDbMirror -SqlInstance  sql2016

Gets properties of database mirrors and mirror witnesses on sql2016 SQL Server instance


CTE query that fails to find ancestors I have a table foo

with the content

So the records of the table foo can graphically be represented with this graph.

When I run a procedure that should return ancestors using @starting_id=9
(represented with a circle in the graph), I get this result

9

instead of

9
3  and
1

Why?
CREATE PROCEDURE [dbo].[GetParents]
    @starting_id int
AS
BEGIN
    WITH chainIDsUpwards AS
    (
        SELECT id, parent_id FROM foo WHERE id = @starting_id

        UNION ALL

        SELECT foo.id, foo.parent_id FROM foo
        JOIN chainIDsUpwards p ON p.id = foo.parent_id
    )

    SELECT id FROM chainIDsUpwards
END

Fiddle at https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=81be3d86dc7581eb60bc7af4c09077e4

I believe you made a mistake in your join predicate. Try:
WITH chainIDsUpwards AS
    (
        SELECT id, parent_id FROM foo WHERE id = @starting_id

        UNION ALL

        SELECT foo.id, foo.parent_id FROM foo
        JOIN chainIDsUpwards p ON p.parent_id = foo.id
    )

    SELECT id FROM chainIDsUpwards

It is a common mistake, and I often do it myself. I find it easiest to get it right if I start with the CTE:
    SELECT foo.id, foo.parent_id
    FROM chainIDsUpwards p
    JOIN foo p

and then think of the CTE as a linked list where I need to find the next element:
        ON p.parent_id = foo.id


Analysis Services not allowing other/remote connections other than localhost Analysis services not allowing any connections  except localhost. Authentication box is disabled and only allow windows authentication. How can I connect to different remote analysis servers using sql server authentication.
Thanks.

We always run our Analysis Services on a named server, e.g. AnSrv123, and being run by a Domain Login such as DomainName\ManageAnSRV124.

Regarding connecting to Remote Analysis Servers using SQL Server authentication please note the following.

Connect from client applications (Analysis Services)
https://msdn.microsoft.com/en-us/library/dn141153.aspx

This says, in part: "Authentication is always Windows authentication, and the user identity is always the Windows user who is connecting via Management Studio."


Change oracle port from 1523 to 1521 when something else is on 1521 I'd like to have oracle running on the default port of 1521 so I don't need to change connection strings in 100 places. My newly installed database is listening on 1523 though:

$tnsping $ORACLE_SID
.... (PORT = 1523)

I want that to be 1521. I confirmed that I can connect to 1523 but not on 1521. However, it appears that oracle is actually listening on port 1521,1522 and 1523:

$netstat -tulpn
...
tcp        0      0 0.0.0.0:1521                0.0.0.0:*                   LISTEN      119935/tnslsnr
tcp        0      0 0.0.0.0:1522                0.0.0.0:*                   LISTEN      17766/tnslsnr
tcp        0      0 0.0.0.0:1523                0.0.0.0:*                   LISTEN      185301/tnslsnr

Why would it be listening on 3 ports, but not working on 1521? Is it normal for oracle to be listening on 3 ports with 3 instances of tnslsnr?

Before even posting I found the answer to my question.

As it turns out there should only be one of those processes running.

The first thing I did was try to find out more about this process on port 1521. I took the PID and searched for it.

ps -ef | grep 119935
oracle   119935      1  0 May11 ?        00:00:04 {Old install path}/bin/tnslsnr LISTENER -inherit

I noticed that the path shown here is to a directory that doesn't exist. I had previously installed oracle to this location and then uninstalled and reinstalled (I had to do this twice...). I had even "rm -rf"ed the whole directory. So this process must be from the old install, still running in memory. There's probably a bunch of stuff running that shouldn't be from my hacking so I rebooted the machine.

Now processes are gone:

$netstat -tulpn | grep tnslsnr
nothing here

With port 1521 free I can change the listeners port


How to update a sequence column using single update statement I have a table, lets call it foo which consist a column priority and has a unique constraint on it.

lets assume below is the current state of foo table :-

mysql> select * from foo;
+-----+----------+
| id  | priority |
+-----+----------+
| 1   |        5 |
| 2   |        6 |
| 3   |        7 |
+-----+----------+

Now I have a requirement where,if I insert a another row with priority value of 6, then it should insert this row and move priority of all the rows which has its value >= to 6 to 1 priority down.

So revised table should look like below :-

mysql> select * from foo;
    +-----+----------+
    | id  | priority |
    +-----+----------+
    | 1   |        5 |
    | 2   |        7 |
    | 3   |        8 |
    | 4   |        6 |
    +-----+----------+

I want to use a sql statement like

update foo set priority=priority+1 where priority >=6; But obviously it will fail with below error :-

  ERROR 1062 (23000): Duplicate entry '7' for key 'priority'.

Is there is any other way of doing the same thing.

Here is a solution that can help you.

update foo set prio = prio+1 where prio >= 6 order by prio desc;
insert into foo (prio) values(6);


MySQL Query Doubling Sums I am designing a query to retrieve the sum of labor hours for a given period, this query works beautifully except when I get to this sonum. All of the values are doubled. I can't for the life of me figure out what is wrong. If I do not join the tables this does not occur. Any help?

SELECT UPPER(`2016`.sonum) as sonum, SUM(`2016`.lbrhrs) as 'lbrhrs', SUM(`2016`.burhrs) as 'burhrs', MONTH(`2016`.date) as 'date', `2016`.dept as dept, closedates.closedate as 'closed date'
FROM `2016`
LEFT JOIN closedates on UPPER(`2016`.sonum)=UPPER(closedates.sonum)
WHERE `2016`.sonum=3470 and month(`2016`.`date`)=1
GROUP BY UPPER(`2016`.sonum), `2016`.dept, month(`2016`.date);

In this case, there is an easy workaround to the "explode-implode" problem that happens with JOIN + GROUP BY:

SELECT  UPPER(`2016`.sonum) as sonum,
        SUM(`2016`.lbrhrs) as 'lbrhrs',
        SUM(`2016`.burhrs) as 'burhrs',
        MONTH(`2016`.date) as 'date',
        `2016`.dept as dept,
        ( SELECT MAX(closedate)
              FROM closedates
              WHERE sonum = `2016`.sonum   -- see note below
        ) as 'closed date'   -- Here's the trick
    FROM  `2016`
    WHERE  `2016`.sonum=3470
      and  month(`2016`.`date`)=1
    GROUP BY  UPPER(`2016`.sonum),
              `2016`.dept,
              month(`2016`.date);

If sonum has any ..._ci collation, then it is "Case Insensitive", so the UPPER() is unnecessary for comparing.  Please provide SHOW CREATE TABLE for both tables.

The 'trick' is to avoid the JOIN, thereby avoiding the multiplication of rows before doing the SUMs.


How innodb alter table inplace algorithm inner work? From the mysql doc  , it says :

  ALTER TABLE operations are processed using one of the following
  algorithms:

  COPY: Operations are performed on a copy of the original table, and
  table data is copied from the original table to the new table row by
  row. Concurrent DML is not permitted.

  INPLACE: Operations avoid copying table data but may rebuild the table
  in place. Typically, concurrent DML is supported for InnoDB tables by
  a DML log that is applied after table alterations are committed.

It just says mysql can dml when modify column , but how innodb alter table inplace algorithm inner work ?

There are too many different cases.  I suggest you read https://dev.mysql.com/doc/refman/8.0/en/innodb-create-index-overview.html , then come back with a more specific question.  (Back up the version if you are using something older.)

My feeling in watching MySQL evolve over the years is this last round of Optimizations of ALTER has added ways to speed up just about everything in ALTER.  That includes avoiding rebuilding the table (when practical) and avoiding locks (when practical).

"says mysql can dml when modify column" -- Well, that depends on the modify column -- add/remove NULL, change ENUM / DEFAULT / etc, ADD/DROP, etc.

The first ALTER optimization was for adding an option to ENUM at the end and not exceeding 255.  That was added many versions ago -- It was rather trivial in that it changed the "meta" data only.  That can even be INSTANT in the new 8.0 terminology (even better than INPLACE).


pg_stat_monitor: see placeholders instead of actual query I have started using pg_stat_monitor extension  from :  https://www.percona.com/doc/percona-monitoring-and-management/2.x/manage/conf-postgres.html  with my postgresql database.I  have a problem here. I though of using this so that I can see my query with actual parameters instead of  placeholders. However, still I see my query with placeholder. Even though , I have set pg_stat_monitor.pgsm_normalized_query =0 and by default it's 0 only. Which means it has to show me query with actual parameters.
You can refer attached screenshot:

While the docs do say that the default is 0, the default is clearly not 0.  Looks like a bug.  After you set it to really be 0, did you execute the reset so you wouldn't be looking at old data?
But the entire pgsm_normalized_query=0 feature seems misbegotten.  With it, the query is still normalized, it just that the text reported is the parameterized text of an arbitrary (the first?) execution of that normalized query in that bucket, but all the stats seem to have been aggregated across the normalized query even though the text reported is not normalized.
Also, it seems pretty flaky even within those constraints:
select bucket, calls, query  from pg_stat_monitor where query like 'UPDATE pgbench_accounts%' order by total_time desc;
 bucket | calls  |                                   query
--------+--------+----------------------------------------------------------------------------
      3 | 95,109 | UPDATE pgbench_accounts SET abalance = abalance + 3008 WHERE aid =
      4 | 92,216 | UPDATE pgbench_accounts SET abalance = abalance + 3186 WHERE aid = 421795
      8 | 93,381 | UPDATE pgbench_accounts SET abalance = abalance + 1998 WHERE aid =
      5 | 90,735 | UPDATE pgbench_accounts SET abalance = abalance + -2438 WHERE aid = 574540
      9 | 81,484 | UPDATE pgbench_accounts SET abalance = abalance + 3594 WHERE aid =
      6 | 86,158 | UPDATE pgbench_accounts SET abalance = abalance + -41 WHERE aid = 1
      0 | 90,503 | UPDATE pgbench_accounts SET abalance = abalance + -2664 WHERE aid =
      7 | 93,446 | UPDATE pgbench_accounts SET abalance = abalance + -3997 WHERE aid =
      1 | 55,960 | UPDATE pgbench_accounts SET abalance = abalance + 4804 WHERE aid =

Why is the final parameter sometimes there and sometimes not?
Overall, I would take the caution on that page "pg_stat_monitor is beta software and currently unsupported." to heart.


Get people with similar interests I have a table named SKILLS with two columns: id and skills.
id is the id of the user, skill is a particular skill.

I need to find other users who have similar interests, like who has the most similar skills to the current user.

How do I structure the join to find such users?

I can show you an example for SQL Server, but it should you help for your RDBMS aswell.

I hope I understood the question. Suppose you want to get TOP users based on the number of same skills as the current user.

Some sample data:

CREATE TABLE skills (
  id INT NOT NULL,
  skill INT NOT NULL);

INSERT INTO skills (id,skill)
VALUES
  (1,1),(1,2),(1,3),
  (2,1),(2,6),
  (3,1),(3,3),(3,4),(3,5),
  (4,1),(4,5),
  (5,1),(5,2),(5,3),(5,5),
  (7,2),(7,4),(7,5),(7,6),
  (8,8);

The query:

SELECT
  cus.id AS current_user_id
  ,ous.id AS other_user_id
  ,COUNT(*) AS same_skill_count
FROM
  skills AS ous -- other users' skills
  JOIN skills AS cus -- current user's skills
    ON cus.skill = ous.skill -- we are looking for users that have some of the current user's skills
      AND cus.id <> ous.id -- exclude the current user from the list
GROUP BY
  cus.id
  ,ous.id
ORDER BY
  cus.id
  ,same_skill_count DESC

SQLFiddle demo

Hope that helps.


how to change primary node on mongo replica set without losing cursor Today, the primary machine's loading is extremely high on a replica set.
Users complain about query/insert operations are very slow.
So I tried to change the priority of node to make secondary/primary exchanged:

cfg = rs.conf()
cfg.members[0].priority = 1
cfg.members[1].priority = 0.5
rs.reconfig(cfg)

Then, 2 users complained about their program get exception from running cursor lost.

Is there any way to avoid the running cursor lost when primary node changed?

As at MongoDB 3.4, when a primary transitions to a non-primary state (eg. as the result of an election) all active connections are dropped so they can be re-established on the new primary.

Cursor state is specific to a given mongod, so you cannot resume a cursor on a different member of the replica set.

A recommended area to investigate would be why your primary was heavily loaded and why a change in primary would have reduced the load significantly. Generally electable secondaries in the same replica set should be identically provisioned in terms of hardware resources, so exchanging server roles should have pushed similar load onto the new primary. If the load was coming from suboptimal queries that were terminated on the former primary (or due to other resource contention), you could perhaps have avoided reconfiguring your replica set by finding and addressing the root cause.

The MongoDB manual has some information on how to Evaluate Performance of Current Operations. You should also implement a monitoring solution (if you haven't already) in order to capture a baseline of normal activity and help identify metrics that change significantly when your deployment is under load.

If you have long running queries which are likely to be interrupted by a restart you could consider:

Adding some logic to your application to restart the query using criteria from the last document seen while iterating.
Using secondary reads for these queries if your use case can tolerate eventual consistency. Before doing so, it's worth reading Can I use more replica nodes to scale?.


How should I configure my postgresql.conf file to backup the database once per hour? My goal is to back up the PostgreSQL 10 database on a Ubuntu 18.04 VPS once per hour.

However, I clearly do not understand how to achieve this. Here is what I've done.

I modify the postgresql.conf file in four ways:

wal_level = replica             # minimal, replica, or logical
archive_mode = on               # enables archiving; off, on, or always
archive_command = 'cp %p /test/%f'  # copy the database file to the /test directory
archive_timeout = 3600            # force a logfile segment switch after this
                                # number of seconds; 0 disables

These are the only entries in postgresql.conf that I've changed. AIUI, I'm saying: copy the current database file every hour to /test using the same filename as the database filename.

I then restart PostgreSQL using /etc/init.d/postgresql reload (I've also tried restart).

However, nothing ever copies to /test. Also, the /etc/postgresql/10/main/pg_wal directory does not contain any files.

What am I doing wrong? Maybe other entries in the file need to be changed? Thanks.

For hourly backups,  you can achieve this by scheduling Cronjob to take Pg_dump.  Alternatively,   you can configure Bart and schedule the backups.


How to CREATE TABLE ... LIKE without the NOT NULL Constraints? I'm trying recreate a table's structure inside a function by using some dynamic SQL.

EXECUTE 'CREATE TABLE ' || my_table_name || '_bk' ||
    ' (like ' || _my_table_name || ')';

That will be similar to:

CREATE TABLE my_table_bk
(like my_table);

But I need to discard all constraints. Using EXCLUDING CONSTRAINTS in the Like Options, it still copy the NOT NULL Constraints (Documentation confirms this behavior):

CREATE TABLE my_table_bk
(like my_table EXCLUDING CONSTRAINTS);

The question is, how can I recreate the table structure without the NOT NULL constraints, or, in alternative, remove all NOT NULL constraints in a table?

Try this:

CREATE TABLE my_table_bk
AS
  SELECT *
  FROM my_table
  WHERE false;

Or, you can append LIMIT 0 instead of the WHERE clause.

This will create a table my_table_bk with the same structure as my_table without constraints and without data.


syntax error at or near "current_timestamp" while creating partition table I am trying to create a partition for the last 30 days. Yet when I run this code:

create table Test_Older30day partition of Test
    for values from (current_timestamp + interval'-30 day') to (current_timestamp);

I get this error:

syntax error at or near "current_timestamp"

I am confused, what am I doing wrong here?

the table looks like this:

create table if not exists Test(
source_name varchar,
event_name varchar,
service_data json,
stamp timestamptz
)partition by range(stamp);

data types match and I have used the partition by range on the date field.

I already know I can insert them manually

create table Test_Older30day partition of Test
        for values from ('2019-08-11') to ('2019-09-11')

like that. What I am trying to attempt is a more automated way of doing it. Basically the idea is to avoid typing it every single month or week. Just to run the query and it would use the current date.

what am I doing wrong here?

You are incorrectly using non-literal values for partition bound specifications. Documentation states that partition_bound_spec is

IN ( { numeric_literal | string_literal | TRUE | FALSE | NULL } [, ...] ) |
FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )
  TO ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )

that is, only literals or special keywords MINVALUE and MAXVALUE are allowed.  current_timestamp is a function, not a literal value.

As suggested in the comments, you will need to generate your create table ... partition ... statement dynamically if you want to use the function return value as the partition bound.


query to check all SCD tables that have data for specific date It's easy to find all SCD tables through

select
    *
from
    syscat.TABLES t
    join
    syscat.COLUMNS s
    on t.tabname = s.tabname
where
    s.COLNAME = 'EFFECTIVE_DTE' and t.type='T'
    with ur;

problem is how to further apply SQLs on the resut tables sets and check if each contain data for specific date ? say:

select effective_dte, count(*) from TABLE1 group by TABLE1 where ...

is it possible to futher apply the queries to all result tables

You should be able to create a stored procedure that executes your query to get the table names. Then you loop over the results and execute your effective date query for each table individually.

You might even be able to do this without wrapping it into an stored procedure.


Limit Access To mysql information_schema In case of mysql attacks, many times user extracts information from information_schema.

can i do something in mysql that can restricts user to xtracts information from information_schema while all privileges on rest database is available.

When you grant permission, you specify the database and table names that you're allowing access to. Don't give unprivileged users modification privileges to *.*, list the databases explicitly in the grants.


Numbering rows consecutively for a number of tables I have a number of tables, all containing attributes aid,bid,cid, and xid of type integer, other attributes may differ. For every (given) table T, I would like to order the rows according to aid,bid,cid ascending and update column xid with incremented values starting with 0. What's the best way to achieve that?

My current solution involves:

selecting table T
openining a cursor over the ordered tuples of a table
assigning the autoincrement value to xid
inserting the tuple into a temporal table T_temp
deleting all records from T
inserting all records from T_temp into T

Since the tables have different schemas, I programmed half of the code in PL/pgSQL and half of the code with bash scripting.

Question 1: Any comments how to have it program that in pure PL/pgSQL?

Question 2: Any comments how to implement that more elegantly?

If I understand correctly, you want to restart the numbering with 0 for every table.
Use the window function row_number() in an UPDATE:

UPDATE tbl t
SET    xid = n.xid
FROM  (SELECT ctid, row_number() OVER (ORDER BY aid, bid, cid) - 1 AS xid FROM tbl) n
WHERE  t.ctid = n.ctid;

Using ctid as poor man's surrogate for a primary key, since you neglected to disclose your table definition.

SQL Fiddle.
db<>fiddle here


Fastest way to change column type from varchar2 to number on large table in Oracle I have a table which contains a VARCHAR2 column that must me changed into NUMBER.

The table contains more than 30 millions rows.

The table definition :

CREATE TABLE EFF (
    MOD         VARCHAR2(6)     NOT NULL,
    MP          VARCHAR2(6)     NOT NULL,
    PROG        VARCHAR2(1)     NOT NULL,
    RNK         VARCHAR2(4)     NOT NULL,
    EFF_STS     VARCHAR2(1),
    EFF_PART    VARCHAR2(1),
    CONSTRAINT PK_EFF PRIMARY KEY (MOD, MP, PROG, RNK)
);

The code I used on Oracle 11g to change the RNK column type :

ALTER TABLE EFF ADD (NRNK NUMBER(5));
UPDATE EFF SET NRNK = TO_NUMBER(RNK);
ALTER TABLE EFF MODIFY (NRNK NUMBER(5) NOT NULL);

Then I would modify the primary key and drop the old column :

ALTER TABLE EFF DROP PRIMARY KEY DROP INDEX;
ALTER TABLE EFF ADD CONSTRAINT PK_EFF PRIMARY KEY (MOD, MP, PROG, NRNK);
ALTER TABLE EFF DROP COLUMN RNK;

The UPDATE phase is very long, at least 20 minutes.
What could be the best way to do it faster ?

There is no indexes other than the primary key.

I guess, you can use parallel operations:

Parallel DML

Let your table support parallel operations:

ALTER TABLE EFF parallel;

Then you perform all your steps as you describe in the question.

Parallel DDL + CTAS

CREATE TABLE temp_table_EFF
PARALLEL
AS
SELECT MOD, MP, PROG, TO_NUMBER(RNK), EFF_PART
  FROM EFF

Then you drop your table:

DROP TABLE EFF;

Then you rename your temp table:

ALTER TABLE temp_table_EFF RENAME TO EFF;

Then you add indexes for to this new table:

ALTER TABLE EFF ADD CONSTRAINT PK_EFF PRIMARY KEY (MOD, MP, PROG, NRNK);

I have no tables with 30M records, so I can't tell you what method is faster. Try both. If you think that there won't be decent performance improvement try to use the NOLOGGING option to forbid your table generate the redo information.


Its necessary to relate the ComplaintMessages entity with the User and Admin entities or is enough to relate with the Complaint entity? (ER Model) I'm in doubt in the ER model of a scenario where a user can make complaints.

The user enters the title and the description of the complaint and submits the info. A site admin can see all user complaints and answer.

Also, the user then has access to the Admin answer and the user should be able to answer to that Admin answer similar to a chat scenario where then the admin will also have access to the new complaint message of the user, so there can be several messages.

I'm in doubt in which ER model is correct for this scenario if the 1 or 2, do you know if it is also necessary to relate the ComplaintMessages entity with the User and Admin entities?

1) It's not necessary to relate the ComplaintMessages entity with the User and Admin entities?

2) Or it is also necessary to relate the ComplaintMessages entity with the User and Admin entities?

Depends if a different admin or a different user could join in on the followup conversation, other than those who started it in the originating Complaint.

If a complaint's message thread is forever tied to the single, originating user, then both the connection (foreign key) and idUser column should be excised from ComplaintMessages. Same with the admin if it's fixed, and there's no chance a different admin might have to answer the followups (this side might be less true).

Consider if it even makes sense to have this many separate tables. Most would keep all users, regular and admin alike, in the same table, and have a field - or a connected rights table - to determine who has what permissions.

Similarly, there doesn't seem to be much reason to keep  Complaint and ComplaintMessages separate. Considering it sounds like there could be multiple followup messages, you are currently missing some kind of mechanism to keep the followup messages in chronological order (other than possibly relying on the primary key id field providing that ordering).
Instead of the two, you could have a single Messages table, with a "thread ID" field added, referencing the first entry in the same table. And probably a date/timestamp field to keep everything in order, or a reference back to the previous message's ID.


How to reconfigure primary key columns I have a series of tables in my database that are using DATE as the primary key. I would like to reconfigure the tables so that an AUTO_INCREMENT ID field is the primary key.

What is the best way to change the tables so the first record ID in each table start at 1 and auto_increment by 1 up to, and beyond, the latest records?

Subsequently, two of the tables do not have data going back as far as the other tables so I'd like to have their starting ID fields begin at a certain integer and auto_increment from there.

MySQL version: 5.5.45
Engine: MyISAM
Interface: phpmyadmin

This takes one from a table that has a PK on date (or datetime, change accordingly), to a table with an auto_increment column. Then it takes a child table if you will (that relied on the previous date PK FK setup) in a new direction using the new parent table's auto_increment PK.

It could be done inline in the original tables. But I am risk adverse, and not wanting to mess up anyone's originals. At the end, do drops and renames. By the way, one interesting functionality with mysql rename is doing hot-swap renaming of tables that are safe even in high concurrency environments.

So in the below, think of origThought and nextThought as the before and after of a main table. And think of sotv1 and sotv2 as the before and after of some-other-table before and after conversion.

Create the Schema

create table origThought
(   myDate date not null, -- this can be a date or datetime, either one (but used throughout all tables consistently)
    thing varchar(10) not null,
    primary key(myDate)
);

create table nextThought
(   id int auto_increment primary key,
    myDate date not null,   -- remember that this was the driving force in old table as a PK
    thing varchar(10) not null
);

create table sotv1
(   -- some other table version 1
    id int auto_increment primary key, -- let's just say it started with this one
    ref_ToOldPK_myDate date not null,
    colC varchar(10) not null,
    constraint `asdf` foreign key (ref_ToOldPK_myDate) references origThought(myDate)
);

create table sotv2
(   -- some other table version 2
    id int auto_increment primary key,
    ref_ToOldPK_myDate date not null, -- comes along til we clean things up
    colC varchar(10) not null,
    newPkValue int not null, -- this is our new FK value pointing back to nextThought(id)
    -- when I say newPkValue, I mean the new PK value in nextThought table (not the PK value id in this table)
    constraint `asdf2` foreign key (newPkValue) references nextThought(id) -- our new PK in 'parent' table
    -- point with asdf is that asdf will fail cuz that name is already used, so named it something else if you need to
    -- many people don't care and leave out the `asdf` part
);
-- show create table sotv2; -- can see key and constraint names with this

Now load some data

insert origThought (myDate,thing) values('2015-01-02','frog'),('2015-01-07','fish');
-- convert our main table to nextThought
insert nextThought (myDate,thing) select myDate,thing from origThought order by myDate;
-- ok that is done, now onward
insert sotv1(ref_ToOldPK_myDate,colC) values ('2014-02-02',1); -- error 1452 showing FK was in force
insert sotv1(ref_ToOldPK_myDate,colC) values ('2015-01-02',77); -- success
insert sotv1(ref_ToOldPK_myDate,colC) values ('2015-01-07',88); -- success
insert sotv1(ref_ToOldPK_myDate,colC) values ('2015-01-02',99); -- success

Complete the conversion of sotv2 table

Now we merely want to patch our new sotv2 table using appropriate newPkValue.

Well not patch it because our FK is in place, so let's try to do it all in one insert statement.

Else it will fail due to FK (which is your friend). Your foreign key constraints are your friends.

insert sotv2(ref_ToOldPK_myDate, colC, newPkValue)
select t1.ref_ToOldPK_myDate, t1.colC, t2.id
from sotv1 t1
join nextThought t2
on t2.myDate=t1.ref_ToOldPK_myDate;

Look at results

select * from sotv2;
+----+--------------------+------+------------+
| id | ref_ToOldPK_myDate | colC | newPkValue |
+----+--------------------+------+------------+
|  1 | 2015-01-02         | 77   |          1 |
|  2 | 2015-01-07         | 88   |          2 |
|  3 | 2015-01-02         | 99   |          1 |
+----+--------------------+------+------------+

bingo, what we want to see.

Drop the prev date residue we don't need anymore in this 'child' table

alter table sotv2 drop column ref_ToOldPK_myDate;

Now we have

select * from sotv2;
+----+------+------------+
| id | colC | newPkValue |
+----+------+------------+
|  1 | 77   |          1 |
|  2 | 88   |          2 |
|  3 | 99   |          1 |
+----+------+------------+

That's even better !


Poor man's replication Currently we have two SQL Server Standard Edition mirrored, one serving as 'hot-backup'. We are planning to migrate to a new cloud hosted solution, where we will use SQL Server Web Edition. So mirroring (or replication) is no longer an option.

We are looking into different types of poor man's replication, as we want to maintain the 'hot-backup' situation. We focus on replication through Transact SQL scripting and Transaction log distribution.

I'm a developer, not a database administrator and I'm struggling with figuring out the impact both methods will have on performance.

I hope anyone here can point me in the right direction.

Given that we have:

around 20 database
each around 1GB in size
the closer we can get to realtime replication, the better

What would be the pro's and con's of the two methods (mainly looking at performance)? Or is there another (better option) to achieve our hot backup?

I know "upgrade to SQL Server Standard or Enterprise" is the best option. I also know it's encouraged to ask questions in stead of starting a discussion in this community. Still I hope someone can help out!

Kind regards

You can still use SQL Log Shipping in Web Edition, this will provide the functionality you need.
It will basically make log backups on schedule and restore to your secondary location.


Java application not using Transparent Application failover in Oracle database I have a customer that have a RAC environment (2 nodes, database version is 11gR2).
Clusterware version:
[grid@server2 ~]$ crsctl query crs releaseversion
Oracle High Availability Services release version on the local node is [11.2.0.4.0]
[grid@server2 ~]$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.4.0]

Their aplication not use correctly the TAF (transparent application failover)
After some research I've tested using swingbanch and I've found the same behavior in my LAB environment:
INST_ID USERNAME   SERVER    SERVICE_NAME         PROGRAM              STATUS   FAILOVER_TYPE FAILOVER_M FAI
---------- ---------- --------- -------------------- -------------------- -------- ------------- ---------- ---
         1 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         1 SOE        DEDICATED mootdb_prod          sqlplus.exe          INACTIVE SELECT        BASIC      NO
         1 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         1 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         1 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         1 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         1 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO
         2 SOE        DEDICATED mootdb_prod          JDBC Thin Client     ACTIVE   NONE          NONE       NO

The only session using the TAF above is a session created manually from sqlplus (failover_type column). I'm using the same tnsnames alias in all sessions.
Why is this happening. Is there some requirement in the application side as well for it work correctly?

The output shows that your sessions use the "Thin" driver (JDBC Thin Client), which is incapable of using TAF.
Transparent Application Failover

Transparent Application Failover (TAF) is a feature of the Java
Database Connectivity (JDBC) Oracle Call Interface (OCI) driver.

Sometimes the JDBC OCI driver is also called as the "Thick" driver.
Here is a blog series about this topic:
https://blog.yannickjaquier.com/oracle/jdbc-client-high-availability-features-part-1.html
Part 2 is about TAF:
https://blog.yannickjaquier.com/oracle/jdbc-failover-highly-available-part-2.html
Make sure you configure swingbench to use oci driver type. It has the -dt oci commandline option for that. Also make sure you use a proper connection string with the failover options and addresses.


MySQL Stored Procedure Compiles Without Error (I want the error to occur) I have a MySQL stored procedure that runs fine and does its job quite well. Yesterday, I edited it in MySQL Workbench and introduced a spelling mistake on purpose. For example: -

Valid statement: insert into dept (dept_id, dept_name) values (1,'Development');

Statement with intentional typo: insert into dept (dept_id, dept_names /*Note plural*/) values (1,'Development');

My dept table does not contain a column called dept_names (plural) - correct column is dept_name (singular). Upon compiling the procedure, I was expecting MySQL Workbench to show an error message indicating non-existent column, but no such error occurred. The error message was shown when I executed the procedure. Coming from Oracle background, this is somewhat unusual for me. Oracle performs all schema checking (i.e. bad column names) when we Save stored procedures.

Now comes the question: What can I do to make MySQL Workbench (or even mysql server command line prompt) show any and all error messages when I save stored procedures and not wait until I run/execute/call them?

Thanks!

Additional Info 1: Just wanted to add that the "bad" INSERT statement above is wrapped within a IF statement. This caused stored procedure to continue executing without errors for many months until one fine day it entered the IF statement. Error message Unknown column 'dept_names' in 'field list' was received immediately after entering IF statement upon executing the "bad" INSERT.

Additional Info 2: Of course, runtime errors can wait until I actually run/execute the stored procedure. My "bad column" example above is not a runtime error so I would like to see the error when saving/compiling the stored procedure.

When you right click on stored procedures, you hit create stroed procedures.

this should show spelling errors like INsRT and systax erors

But logic errors it will not find.

So start using error handling and user defined variables to debug it properly.

But you can test all queries in a normal Query tab this would also check for syntax errors


Is it possible to write a function in postgresql which directly writes its parameters to a text file? I have a text file which contains some rows of data,say 10 rows. Each row has 5 columns, each of the type varchar. Is it possible to write a function in postgresql, which takes its parameters as 5 varchar values, and appends those values to that file directly?

Using COPY, this boils down to one simple SQL command:

COPY (SELECT 'a','b','c','d','e') TO PROGRAM 'cat >> /path/to/file/my.csv';

You can wrap it into a function if required:

CREATE OR REPLACE FUNCTION public.f_dump_row(varchar,varchar,varchar,varchar,varchar)
  RETURNS void LANGUAGE plpgsql AS
$func$
BEGIN
EXECUTE format($$COPY (SELECT %L,%L,%L,%L,%L) TO PROGRAM 'cat >> /path/to/file/my.csv'$$
             , $1,$2,$3,$4,$5);
END
$func$;

Why the complication with EXECUTE?

How to set column DEFAULT using value of a variable

cat is a standard UNIX utility (shell command).

Your Postgres role needs appropriate privileges. The manual:

  COPY naming a file or command is only allowed to database superusers
  or users who are granted one of the default roles
  pg_read_server_files, pg_write_server_files, or
  pg_execute_server_program, since it allows reading or writing any file
  or running a program that the server has privileges to access.

If you need this for under-privileged roles, consider a SECURITY DEFINER function. But do it properly as instructed in the manual to avoid misuse.

And the target file must be writable by the system user running the Postgres process, typically postgres.

Related:

copy command in postgresql to append data


How to install SQL Server Management Console in 2008 Express Version? I started a dozen attempts to install the SQL Server Management Studio for the 2008 Express Version of SQL Server.

What i use:
Windows Vista 2007 SP 2

What i did:
a) I downloaded the install file SQLManagementStudio_x86_DEU.exe from Microsoft's Download Center

b) I started the setup and chose "Installation"/"Adding Feature"

c) The feature selection appears, but I can chose only from SQL Client Connectivity SDK

d) This is wrong I guess; but even if I click next I get an error message like "Please chose a feature first"

What can I do?

Thanks for any advice!!!

Do any of these links help?

http://connectsql.blogspot.com/2009/07/how-to-install-sql-server-management.html
http://blogs.msdn.com/b/bethmassi/archive/2011/02/18/step-by-step-installing-sql-server-management-studio-2008-express-after-visual-studio-2010.aspx
http://msdn.microsoft.com/en-us/library/bb500441%28v=sql.100%29.aspx


CTE Running in Infinite Loop My CTE runs in an infinite loop for a specific customer and I am not able to find out why.

Here is the query:

;WITH ClassTree
           AS (SELECT ID, NAME, Parent_ID
               FROM   TableName
               WHERE  ID = 1
               UNION ALL
               SELECT T.ID, T.NAME, T.Parent_ID
               FROM   TableName T WITH (NOLOCK)
                      JOIN ClassTree
                        ON Parent_ID = ClassTree.ID
)
SELECT * FROM ClassTree

Here is a little something that you can use to find your cycles.

declare @T table
(
  ID int,
  Parent_ID int
)

insert into @T values
(1, 3),(2, 1),(3, 2),    -- This is a cycle
(4, 4),                  -- This is a cycle
(5, null),(6, 5),(7, 6)  -- This is not a cycle

;with C as
(
  select T.ID,
         T.Parent_ID,
         cast(',' + cast(ID as varchar(10)) + ',' as varchar(max)) as Path,
         0 Cycle
  from @T as T
  union all
  select T.ID,
         T.Parent_ID,
         C.Path + cast(T.ID as varchar(10)) + ',',
         case when C.Path like '%,'+cast(T.ID as varchar(10))+',%'
           then 1
           else 0
         end
  from @T as T
    inner join C
      on T.Parent_ID = C.ID
  where C.Cycle = 0
)
select *
from C
where Cycle = 1

Result:

ID          Parent_ID   Path       Cycle
----------- ----------- ---------- -----------
4           4           ,4,4,      1
3           2           ,3,1,2,3,  1
2           1           ,2,3,1,2,  1
1           3           ,1,2,3,1,  1


SQL Methods Function to add "Gold Medal" for anniversary I'm trying to create a method function on SQL using Oracle Developer.

The member function will give employees a gold medal after 12 years of service to the bank, a silver medal after 6 years of service and a bronze medal after 2.

The issue I am having is with the pre-defined date_type that I have in my employee_type which is as follows

CREATE type employee_Type
AS object(
    name name_type,
    supervisor REF EMPLOYEE_TYPE,
    position VARCHAR2(20),
    salary   NUMBER(5),
    ninum    VARCHAR2(8),
    joindate DATE);

And below is the Method I'm trying to implement

    ALTER type employee_type ADD member FUNCTION give_medals
  RETURN string CASCADE;
CREATE OR REPLACE TYPE BODY employee_type
AS
  MEMBER FUNCTION give_medals
  RETURN STRING
IS
BEGIN
  IF self.join_date > 12 THEN
    RETURN 'Gold Medal';
  ELSIF self.join_date = 6 THEN
    RETURN 'Silver Medal';
  ELSIF self.join_date = 2 THEN
    RETURN 'Bronze Medal!';
  END IF;
END give_medals;
END;

But again my issues is lies with the ELSIF statement, as you can see I'm returning a Gold Medal for anything over 12 - but because the date_type is like 01-JAN-98 everything in the join_date value will be given a gold medal.

So how could I change the function to give Medals for years of service instead of just anything > 12

First, I assume that the fact that you're using join_date with an underscore in your code while the type uses joindate is a typo.  Otherwise, you'd get a compilation error.

You want to calculate the difference between join_date and the current date.  My guess is that you want to use months_between to calculate the number of months between the two dates and build your logic based on that.  months_between probably does what a human wants when he or she says "12 years" but date arithmetic gets tricky particularly when things like leap days are involved (if you joined on Feb 29, 2016, do you hit your 2 year mark on Feb 28, 2018?  Or March 1, 2018?).  If you need to account for time zones, that makes the logic more difficult.

IF months_between( sysdate, self.join_date ) >= 12*12
THEN
  RETURN 'Gold';
ELSIF months_between( sysdate, self.join_date ) >= 6*12
THEN
  RETURN 'Silver';
ELSIF months_between( sysdate, self.join_date ) >= 2*12
THEN
  RETURN 'Bronze';
ELSE
  RETURN null;
END IF;


Preserving existing logins during database refresh I am trying to do database refresh within Sql server 2014 instances from lower env to production for making it to golive. All the required logins exist on current prod and as far as I understand while doing db refresh they will be overwritten or replaced. I know there is sp_helprevlogin but to script out from current prod and then recreate which seems time consuming activity in prod. Just looking if there is a better way i can still preserve those logins or users from getting overwritten?

Update@ Also what about the database roles?

Not sure how you're doing your DB refresh. A DB restore of a user database doesn't change the logins; that should only be an issue if you are replacing the master DB, which I wouldn't generally recommend.

However, if the SID of a login in prod doesn't match the SID of that same login in the source environment, then the users may not match up correctly; that's when you'd have to deal with "orphaned" users in your databases.

As you note, scripting out the users in one environment, and loading them to another, should ensure that the SIDs in both environments line up correctly. That said, unless you're doing this when first setting up a server, I wouldn't try this.

Microsoft has an article that helps you troubleshoot orphaned users for SQL Server 2014.

To find orphaned users, run the following:

USE <database_name>;
GO
sp_change_users_login @Action='Report';
GO

This will identify users tied to a login SID that doesn't exist.

You can fix these with the following statement:

USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';
GO

This updates the login SID for the user, so it matches that of the login you specify.

NOTE: the above works (as best I recall) going back at least as far as SQL Server 2008. However, the process has changed for SQL Server 2016 & 2017. Here's a link to the SQL 2016 version of the page I linked to above.


Automatic Failover on SQL Server Always On Availability Groups is not triggered when primary replica becomes offline I have set up a 2-node Always On Availability Group. Both replicas are in synchronous mode with automatic failover. If I perform a manual failover, everything works as expected: Primary and Secondary servers get switched, there is no data loss and Listener works as expected.

However, if I suddenly shut down the primary server (for testing purposes), the secondary does not switch to be primary. Primary replica gets stuck at "Resolving" status and the listener is not reachable until the primary server is back online.

In short, the availability group is working fine in manual failover, but not in automatic failover. It seems like if the cluster was not aware that one of the nodes is no longer available.

Am I supposed to perform any particular configuration to enable something like "unavailability detection"? I think this is a built-in behavior.

I ran a Cluster Validation, and the following warnings were reported:

The cluster is not configured with a quorum witness. As a best practice, configure a quorum witness to help achieve the highest availability of the cluster. (Should I really change quorum settings through Failover Manager?)
This resource does not have all the nodes of the cluster listed as Possible Owners. The clustered role that this resource is a member of will not be able to start on any node that is not listed as a Possible Owner. (I read that changing this through Failover Manager is not recommended, so I didn't do it)
Node NODE01 is reachable from Node NODE02 by only one pair of network interfaces. It is possible that this network path is a single point of failure for communication within the cluster. Please verify that this single path is highly available, or consider adding additional networks to the cluster. (Is this talking about adding redundancy cards?)

Add a quorum configuration in the failover cluster manager. In order for the cluster to continue to be online a majority of nodes need to be online, you have only two nodes. If one goes down, only one is online and that’s not majority. If you add a witness like shared drive which needs to be stored off the nodes, then if one node goes down, majority two are still online so cluster will remain online.


Delete rows in a table based on a table value parameter I have a table A and a user defined table type on which a table value parameter (tvp) tvpA is based.  Both have the same column structure except that tvpA is not defined with any constraint.

What I'd like to do is to delete rows in A that are not in tvpA based on one of their field called ID.

How can I do that ?

Is there a reason this simple correlated delete statement won't do the trick?

DELETE A
FROM TABLE_A A
WHERE NOT EXISTS (
        SELECT *
        FROM TVP_A
        WHERE ID = A.ID
        )


How to select from a table without including repeated column values? In a previous question How to merge data sets without including redundant rows? I asked about filtering redundant historical data during import, but @DavidSpillett correctly replied that I couldn't do what I was trying to do.

Instead of filtering the table during import, I now want to create a view on the table that returns only records where the price has changed.

Here's the original scenario rephrased to suite this question:

We have a table of historical prices for items. The table contains rows where the same price is recorded for multiple dates. I want to create a view on this data which only shows price changes over time, so if a price changes from A to B I want to see it, but if it "changes" from B to B then I don't want to see it.

Example: if the price yesterday was $1, and the price today is $1, and there were no other price changes, then the price today can be inferred from the price yesterday so I only need the record from yesterday.

Example (http://sqlfiddle.com/#!3/c95ff/1):

Table data:

Effective            Product  Kind  Price
2013-04-23T00:23:00  1234     1     1.00
2013-04-24T00:24:00  1234     1     1.00 -- redundant, implied by record 1
2013-04-25T00:25:00  1234     1     1.50
2013-04-26T00:26:00  1234     1     2.00
2013-04-27T00:27:00  1234     1     2.00 -- redundant, implied by record 4
2013-04-28T00:28:00  1234     1     1.00 -- not redundant, price changed back to 1.00

Expected view data:

Effective            Product  Kind  Price
2013-04-23T00:23:00  1234     1     1.00
2013-04-25T00:25:00  1234     1     1.50
2013-04-26T00:26:00  1234     1     2.00
2013-04-28T00:28:00  1234     1     1.00

My initial attempt used ROW_NUMBER:

SELECT
    Effective,
    Product,
    Kind,
    Price
FROM
(
    SELECT
        History.*,
        ROW_NUMBER() OVER
        (
            PARTITION BY
                Product,
                Kind,
                Price
            ORDER BY
                Effective ASC
        ) AS RowNumber
    FROM History
) H
WHERE RowNumber = 1
ORDER BY Effective

Which returned:

Effective               Product  Kind  Price
2013-04-23T00:23:00     1234     1     1.00
                                             -- not 2013-04-24, good
2013-04-25T00:25:00     1234     1     1.50
2013-04-26T00:26:00     1234     1     2.00
                                             -- not 2013-04-27, good
                                             -- not 2013-04-28, bad

I tried searching for a similar question/answer but it's hard to work out how to phrase the search, an example is worth a lot of words.

Any suggestions appreciated. Thanks

SQLfiddle

SELECT H.*
FROM History AS H
OUTER APPLY
(
    SELECT TOP (1)
        H2.Price
    FROM History AS H2
    WHERE
        H2.Product = H.Product
        AND H2.Kind = H.Kind
        AND H2.Effective < H.Effective
    ORDER BY
        H2.Effective DESC
) AS X
WHERE
    NOT EXISTS (SELECT X.Price INTERSECT SELECT H.Price);

This is a good execution plan for the small number of rows given in the question. For a larger table, the ideal index for this query is:

CREATE UNIQUE INDEX [dbo.History Product, Kind, Effective]
ON dbo.History (Product, Kind, Effective DESC)
INCLUDE (Price);

That index is essentially the clustered index keys in a more helpful order. Depending on how the table is used for other queries, it might be better to replace the clustered index instead of creating this new index.

Do not use batch estimated cost percentages to compare different queries. This is not a valid comparison in general. The costs are always optimizer estimates, and not intended to be used this way. Check actual performance metrics (elapsed time, I/Os, CPU usage, memory usage) by all means, but do not put your faith in the percentages.

Whether this query or the one based on ROW_NUMBER is more efficient depends on the distribution of the data, and other factors. Each has their advantages and disadvantages. This query has advantages when there are many rows for each (product, kind) combination.


Unable to connect to Mysql via JDBC but works fine with Mysql I have a mysql server running locally to which I am able to connect using

mysql -h localhost -u root

It does not require a password. But when I try to connect with the same attributes (jdbcurl = "jdbc:mysql://localhost:3306/mysql", user = root) via JDBC, I get this weird exception

he last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
    at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
    at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:91)
    at com.mysql.cj.NativeSession.connect(NativeSession.java:144)
    at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:956)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:826)
    ... 33 more
Caused by: java.net.ConnectException: Connection refused (Connection refused)
    at java.base/java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.base/java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:399)
    at java.base/java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:242)
    at java.base/java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:224)
    at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:403)
    at java.base/java.net.Socket.connect(Socket.java:591)
    at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:155)
    at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:65)
    ... 36 more

What could be the issue here?

EDIT
Tried out the suggestion given in the comments to try to connect to mysql database via mysql as being done in java.

mysql -h localhost -u root mysql

This works and I get connected to the mysql db.

Which MySQL Version you are using ?

You won't be able to use root user I guess.

Create new application user and use that user for application.

CREATE USER appuser@'%' identified by 'PASSWORD';
Grant SELECT on *.* to appuser@'%';
flush privileges;


move AG database files between disks Environment: SQL 2016 SP1, AG with 2 replicas, sync commit, no readable secondary. 1 file in the PRIMARY FG, 1 for the index FG.
Problem: How to move the files for a database in this AG, from the current disk to a new disk, with no downtime, not taking it out of AG ?

I'd try to add a file to each file group, on the new disk, run shhrinkfile emptyfile, remove the empty files from the old disk, all on the primary. Log file will stay on the old disk. Not sure how to do it on the secondary.

Can someone please confirm I'm on the correct path, or improve on it, please ? Thank you.

Took me some time to test it but this actually works.

Step 1

Add the new disks to both nodes of the SQL Server Availability group

Step 2

Stop the SQL Server service of your passive node

Step 3

Move the SQL Server database files from the old disk to the new disk

Step 4

Adjust the drive letter of the original disk to another letter using f.e. diskpart

Step 5

Rename the disk where you move the database files to to the drive letter of the original disk

For Example= Old disk D: --> renamed to F:  New Disk E:  --> renamed to D:

Step 6

Restart SQL Service on Passive node and wait untill everything is back into sync.

Step 7

Failover to the passive node & do the exact same process on your previously active node.

Downtime?

If you follow this process you will have no downtime, except for the failover of your availability group.


Drupal 7 Uses InnoDB, so do I need other configs? If I'm optimizing a database that will be delivering multiple Drupal 7 websites, all using InnoDB, do I need to worry about [myisamchk] & [isamchk] sections, or can I just worry about the stuff within [mysqld]?

I'm assuming if I delete these sections, that the defaults will just kick in if they are needed.

I'm trying to document, outline my.conf best practices here http://groups.drupal.org/node/289613

The other group headers have no bearing on InnoDB whatsoever. [mysqld] is all you really need.
With regard to InnoDB itself, you will need to tune InnoDB in many aspects
System Tablespace / Individual Tablespaces

Sep 26, 2012 : How do I shrink the innodb file ibdata1 without dumping all databases?
Jul 05, 2012 : Move ibdata1, set innodb_data_file_path
Apr 19, 2012 : MySql - Clean ibdata1
Apr 01, 2012 : Is innodb_file_per_table advisable?

Buffer Pool

Oct 22, 2012 : How much memory do I need for innodb buffer pool?
Jun 13, 2012 : What to set innodb_buffer_pool and why..?

Transaction Logs

Jan 17, 2013 : MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?
Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'?

Multiple CPU / Multiple Core Engagament

May 21, 2012 : MySQL - Installing InnoDB Plugin
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?

Epilogue
There are so many other aspects I have no time to write about because I probably have written it already. Please see all my posts on InnoDB.
What is also worth noting is that you should be using the latest version of MySQL

MySQL 5.6.10 now supports FULLTEXT indexing. You need to test this fully.
InnoDB in MySQL 5.6 is faster than InnoDB in MySQL 5.5

On a side note, did you know the StackExchange has a Drupal Q&A site ? I have posted answers there as well.


The media family on device incorrectly formed for read only file group restore Yes the databases (source and destination) are the same version.  I have checked 3 times.  Both are "Microsoft SQL Server 2019 (RTM-CU8-GDR) (KB4583459) - 15.0.4083.2 (X64)"
The database itself restores ok without error.  Only when I try the read-only file group restore does it give the error.   I have redone the back up and attempted the restore again just in case I had something wrong with the backup.
I have done this numerous times with ss 2016 and even when upgrading from ss2016 to ss2019.  But now I am testing the restores it just looks broken to me.  Almost like this is a  bug

I think you have encountered a problem with disk sector size, the old (very old) standard was 512 bytes per sector and the new one is 4kb. When you restore a database between these 2 standards the restore re-writes the data to 4kb sectors but with a read-only filegroup my hypothesis is that this would not be allowed. There is a basic discussion of the change here :-
https://techcommunity.microsoft.com/t5/sql-server-support/sql-server-new-drives-use-4k-sector-size/ba-p/316277


Do long-running and locking queries on subscriber databases cause performance issues on the publisher dbs? One of our servers has a subscriber database. I've not used replication before and cannot find information on how the mechanism works.
Does the publisher have to wait on the replication completing before transactions are committed or does it make use of a queue or the transaction log before applying changes to the subscriber?
The subscriber is used as the back-end for a custom report builder (amongst other uses) which cannot make good use of indexes so tables are frequently locked for several minutes. I am concerned for any effects this might have on our publisher, which is on our live production server.
Both publisher and subscriber are in Full Recovery.

Long running and locking queries on subscriber databases wouldn't typically cause performance issues on the publisher. The distributer can be run separately if you are worried about them affecting the performance of your SQL server.

The Log Reader Agent will take the changes from the Publisher and store them in the distribution database. The Distribution Agent will attempt to apply these to the Subscriber. If the subscriber is busy or tables are locked it will wait for them to be available or timeout and keep retrying on the schedule defined during setup.

You can view how many unreplicated commands exist and how far behind your subscriber is from the Publisher by using the Replication Monitor or querying the sp_browsereplcmds procedure.

For more info on the agents, and the settings you can change, take a look at this Microsoft docs page.


Differential/T-Log LSN question So, this is now my third question on this topic.  I just keep finding more and more than i want to know and understand about how FULL recovery works.

We transitioned a database to FULL yesterday by taking a full backup at 7am.  Since then we take T-Log backups every hour and differentials nightly at 12:01am.  I'm generating some reports that can be automatically emailed to me (hooray automation!) and im noticing something funny with the LSN numbers.  Maybe I'm just not understanding how they work, but here goes.

Picture of query result displaying files, dates, sizes, LSNs, etc since we initiated full recovery yesterday:

I've highlighted the LSNs that are confusing to me and hopefully it makes it easier for you all to read.  Here are my questions:

The high LSN for the full backup is '308000000583700001', shouldn't the differential backups [Row 13 in picture] low LSN be that also in order for the log to chain correctly? Am I misunderstanding how the full and differential log files are synced to each other?
You'll notice that the differential, taken at 12:01am has a high LSN of '311000000469900001' but the T-Log backup taken at 1:00am has the exact same high LSN. Is this simply because there was no activity and the T-Log simply 'caught up' to match the LSN of the differential?

I'm racking my brain trying to make sure not only do i know how to implement a recovery strategy like this, but also that I completely understand exactly how the LSNs all sync up when generated.

Thanks again for all your help, as usual, on this one!

Here's the SQL used to pull this result, in case you would like to investigate using your system:

    SELECT TOP 48 CAST(s.database_name as char(10)) AS DB
    ,CAST(' ' as char(1)) + CAST(SUBSTRING(m.physical_device_name, 46, 40) as char(25)) AS FileName
    ,CAST(CAST(s.backup_size / 1000000 AS INT) AS CHAR(4)) AS MBSize
    ,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS CHAR(4)) AS SecsTaken
    ,s.backup_start_date
    ,CAST(s.first_lsn AS CHAR(20)) AS first_lsn
    ,CAST(s.last_lsn AS CHAR(20)) AS last_lsn
    ,CASE s.[type]
        WHEN 'D'
            THEN 'Full'
        WHEN 'I'
            THEN 'Diff'
        WHEN 'L'
            THEN 'TLog'
        END AS BackupType
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
    AND backup_start_date > '2014-07-24 07:00:00'
ORDER BY backup_start_date DESC

From @Shankys response, here is what the result set looks like with database_backup_lsn and chockpoint_lsn added to the query.  As you can see, the backup_lsn is the same for all, referencing that they are ties to the full backup created yesterday.  I hope this information helps anyone else trying to understand how LSNs work!

I will answer you question point wise like you asked

No its not necessary that LSN of differential backup would match the LSN of full backup. LSN is basically a stamp of last records that accessed the log. You should have also included DatabaseBackupLSN/checkpointLSN and then you will note that DatabasebackupLSN of differential backup will match the checkpointLSN of full backup and this will show to which full backup this differential backup relates to. Differential backup is also kind of full backup just taking backup of extents which has changed so LSN number might not be same as that of Full backup.
Neither Transaction Log backup nor LSN chain would is affected by Differential backup or Full database backup. Only log backup effects the chain.Proof you can see last LSN of 12 AM trn backup is same as first LSN of 1 AM transaction log backup.

Hope this helps


How bad is adding new column each time to compare the fixed values? As for now we have a program that gets some data weekly which need to be compared with previous values, maimly for uniqueness.

All data keys are dynamic and are expected to be changed.

So creating the table for the data with specific columns is not a solution, as their title and number may vary.

We proposed to a client storing XML or json with dynamix data in the table and then deserialize it for analysis.

But eventually we left with the decision to append the new column to the table each week, and will it with as much data as we have and then compare the lists inside of columns.

Basically, it's a horizontal-growing table.
Deep inside I see that it's a bad idea, but unfortunately I don't have a strong argument about it. Could you please help me?

How bad is this design and which consequences we should expect with it?

-----------------------------------------------------------------------------
| PropertyName W1 | Property Value W1 | PropertyName W2 | Property Value W2 |
-----------------------------------------------------------------------------
| Title           | SomeTitle         | OtherProp       | PropValue         |
-----------------------------------------------------------------------------
| SomeProp        | SomeVal           | Title           | SomeUpdatedTitle  |
-----------------------------------------------------------------------------

If the values in one row are not related to each other, adding columns is pointless.

Writing values into the new columns requires rewriting entire rows, and reading a value requires reading the entire row (up to the desired column). So that schema becomes increasingly inefficient.

Put the week information into a separate column:

week  name       value
---------------------------------
  1   Title      SomeTitle
  1   SomeProp   SomeVal
  2   OtherProp  PropValue
  2   Title      SomeUpdatedTitle

With this schema, you do not have to pick specifics weeks to compare; you can also search for weeks where something changed, or compute distances between changes.


Do I understand indexes correctly? I'm trying to wrap my head around the difference between various kinds of database indexes. I have created a little example for myself and I don't know whether I'm understanding everything correctly.
Let's say we have an fictional database table like this:
addr col1  col2
===============
1    a     b
2    c     b
3    a     c
4    d     d
5    c     a
6    a     b
7    c     b
8    d     d
9    a     c

addr here is a physical location of the corresponding tuple. Here is my understanding of how a single-column, a multi-column and a covering index would look like (not physically, rather on a conceptual level):
create index on col1
a - 1, 3, 6, 9
c - 2, 5, 7
d - 4, 8

create index on (col1, col2)
a b - 1, 6
a c - 3, 9
c a - 5
c b - 2, 7
d d - 4, 8

create index on col1 include col2
a - 1b, 3c, 6b, 9c
c - 2b, 5a, 7b
d - 4d, 8d

So my question is: do I get it right?

You're pretty much right but this isn't the best way to conceptualize it, rather you should draw out a Tree since that is the logical data structure typically used to hold the data within an index.
Using your notation, your first example and your third example are a little hard to distinguish between what is meant to be the key / row ID for referencing the actual row and what is the included field (but I understand what you mean, given your schema details).
Here is a good article on indexing and B-Trees which has a good example of how to conceptualize it. (You can ignore the database system specific comments, such as the section on SQLite, as the generals in this article apply to pretty much any modern relational database management system.) Additionally you can play around with this B-Tree simulator to help you visualize how they grow as data is added to an indexed table.


How to avoid dropping schemas when publishing DB in SSDT Database project When publishing a database from SSDT, there are several options to "do not drop" an object if it is not in the SSDT project. However, it seems that there is no option to avoid dropping schemas. Do anyone know if this is possible to achieve, or do I have to manually edit the publish script?

This is problem when I choose to "do not drop tables", but then it will try to drop schemas that has tables in it.

br
Gert

Use this:

https://agilesqlclub.codeplex.com/

And choose to keep schema's and t won't drop them anymore.


Adding index only on read replica of MySQL RDS instance I have created read replica of RDS production database for reporting purposes. Cross-team is using the replica for their use.

Consumers of this replica are looking for additional indexes for faster search, but the developers are not in favour of that (adding these indexes to master) concerning more time for the insertion of records in the master database.

Is it possible to add index only on read replica of MySQL database?
How this is practised in the industry? Or what other ways this problem could be solved?

If by any chance you are using AWS MySQL RDS as your database infra, you can refer this:

  To further maximize read performance, Amazon RDS for MySQL allows you to add table indexes directly to Read Replicas, without those indexes being present on the master.


MySQLSyntaxErrorException: You have an error in your SQL syntax; This is the issue I am trying to fix since hours, almost a day. I am unable to get an understanding of what might be wrong.
I have a SQL table notification

I am trying to persist a java object via hibernate
public void persistNotification(Notification notify){
        sessionFactory.getCurrentSession().save(notify);
    }

notify object passed to the method looks fine to me with all the values
Notification [notificationId=null, notificationMessage=ethan is now following you, notificationType=follow, notificationRead=false, actor=ethan, subject=diggle, notificationCreationTime=2015-12-09T19:13:23.002, userId=1]

But I am encountering a SyntaxErrorException.
2015-12-09 18:52:05.0550 DEBUG http-bio-8080-exec-10 org.hibernate.SQL – insert into notification (actor, creation_time, notification, read, type, subject, user_id) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into notification (actor, creation_time, notification, read, type, subject, user_id) values (?, ?, ?, ?, ?, ?, ?)
2015-12-09 18:52:05.0579 DEBUG http-bio-8080-exec-10 org.hibernate.engine.jdbc.spi.SqlExceptionHelper – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read, type, subject, user_id) values ('ethan', '2015-12-09 18:51:54', 'ethan is ' at line 1 [n/a]
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read, type, subject, user_id) values ('ethan', '2015-12-09 18:51:54', 'ethan is ' at line 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)

2015-12-09 19:00:08.0027 DEBUG http-bio-8080-exec-10 org.springframework.web.servlet.mvc.support.DefaultHandlerExceptionResolver – Resolving exception from handler [public org.springframework.http.ResponseEntity<java.lang.String> com.mycricket.controller.UserController.followUnfollowUser(java.lang.String)]: org.hibernate.exception.SQLGrammarException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read, type, subject, user_id) values ('ethan', '2015-12-09 19:00:07', 'ethan is ' at line 1
2015-12-09 19:00:08.0028 DEBUG http-bio-8080-exec-10 org.springframework.web.servlet.DispatcherServlet – Could not complete request
org.hibernate.exception.SQLGrammarException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read, type, subject, user_id) values ('ethan', '2015-12-09 19:00:07', 'ethan is ' at line 1
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)

Can anyone help me out what might be the issue? Any clue, guesses would be appreciated.
I also dropped & recreated the table but no luck. I don't I am using any reserved SQL words.

The word read is a reserved keyword in MySQL.

https://dev.mysql.com/doc/refman/5.5/en/keywords.html

You need to use a more appropriate column name.


Does the SQL Server optimizer inline function calls in stored procedures? I'd like to factor out some of the functionality in our SPs into separate functions/stored procedures, but I'm worried about the potential performance penalty. In particular, how efficient will be a join with a table-valued function, as opposed to writing out the query from that function in full?

An in-line table valued function (TVF) is simply a macro that expands into the containing query: no more, no less. Just like a standard view.

A multi-statement TVF on the other hand has to run to completion and won't be optimised for subsequent operations on the results


why binlog doesn't log the DML statement? two method compared
1. use test; create table t (a int);
2. create table test.t1(a int);

my result is the first one can log the binlog but the second one can't not

use test;create table t(a int);

mysqlbinlog -v mysql-bin.000005|grep create
use `test`/*!*/;
SET TIMESTAMP=1490090893/*!*/;
create table t(a int)
/*!*/;
DELIMITER ;

but when I do not use "use", DML cannot log in binlog,can tell me why?

create table test.t(a int);

mysqlbinlog -v mysql-bin.000005|grep create test.t

Do you use statement based or row based logging? If it is statement based then you most likely have binlog-do-db set in your replication filters e.g. binlog-do-db=test so that only queries where the default database is test are written to your binary log.

e.g.

USE test;
CREATE table t . . .

USE test;
CREATE table otherdb.table . . .

both appear in your binary logs, but

USE otherdb;
CREATE table t2. . .

USE otherdb;
CREATE table test.t . . .

Don't appear.

In the first instance your default database is test, in the second it is otherdb.

see: https://dev.mysql.com/doc/refman/5.5/en/replication-rules-db-options.html


What is the point of the Script Date in a stored procedure script generated by SSMS? When I generate a stored procedure script, SSMS additionally generates a comment containing the name of the object and a Script Date timestamp:

The timestamp is just the current time at the moment the script was generated, and I am trying to understand the point of it.

I could see SSMS including an automatic feature to capture when a stored procedure is created, or last updated, or ran, for that matter. Any of those might be of use to a programmer in their day-to-day activity. What is the point of including the time it is viewed, though?

I have tried searching for an answer across the Internet but nothing useful has come up.

Why not? It allows you to see when the script was created if you save it & reload another time. The others you mention (created/last modified/last ran) aren't so useful as they're subject to frequent change. And can be grabbed from system views when/if you do need them.

You can turn it off in Options here if you really want to:


Generate Agent Job Report We have a few jobs that run nightly that the status has to be reported every hour.  I am trying to find in MSDB where I can capture the information below hourly and send out an excel file.

Server          Job Order - Name        NormalStartTime NormalEndTime   ActualStartTime ActualEndTime
Server A        1. JobA     11:00:00 PM     11:05:00 PM     11:00:00 PM     11:06:15 PM
Server A        2. JobB     11:05:00 PM     11:15:00 PM     11:06:16 PM     11:31:09 PM
Server A        3. JobC     11:15:00 PM     11:45:00 PM     11:31:09 PM     12:52:04 AM
Server A        4. JobD     11:45:00 PM     11:50:00 PM     12:52:04 AM     12:53:08 AM
Server A        5. JobE     11:50:00 PM     12:00:00 AM     12:53:09 AM     12:56:59 AM

Server = Server Name
Job Order - Name = This is the list of Jobs I want to search for.  The 1-5 is not in the job name.
NormalStartTime = Earliest Start time
NormalEndTime = Latest End Time
ActualStartTime = The time the job started on today's run
ActualEndTime = The time the job finished today's run

Below is the script as per your need

SELECT h.SERVER AS [Server Name]
  ,j.NAME AS [Job Name]
  ,CONVERT(TIME, a.start_execution_date) AS [Normal Start Time]
  ,CONVERT(TIME, a.last_executed_step_date) AS [Normal End Time]
  ,CONVERT(TIME, a.run_requested_date) AS [Actual Start Time]
  ,CONVERT(TIME, a.stop_execution_date) AS [Actual End Time]
  ,h.run_time
FROM msdb.dbo.sysjobhistory AS h
INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobactivity AS a ON h.job_id = a.job_id
WHERE h.run_date = (
      SELECT CONVERT(VARCHAR, GETDATE(), 112)
      )
  AND H.run_time >= (
      SELECT REPLACE(REPLACE(CONVERT(TIME(0), CONVERT(TIME, DATEADD(HOUR, - 1, GETDATE()))), '.', ''), ':', '')
      )
  AND h.step_id = 1
  AND a.start_execution_date IS NOT NULL
  AND a.last_executed_step_date IS NOT NULL
  AND a.run_requested_date IS NOT NULL
  AND a.stop_execution_date IS NOT NULL