Self-join unique pairs I have a table (players) containing a list of players, I would like to pair up these players in a unique way, so that every player's opponent is unique every round (ie. every time the SELECT query is called).

The player_opponent_log column is an integer[] which contains the player_ids of players who have played with that player in a previous round (and is used to help pick out unique players). This column is populated afterwards using the results of the SELECT query - and is outside the scope of this question.

The table for instance would have the following data;

 player_id | player_opponent_log
-----------+---------------------
         1 | {2,3}
         2 | {1}
         3 | {1}
         4 | {}
         5 | {}
         6 | {}
         7 | {8}
         8 | {7}

What I am trying to achieve would be something along the following lines:

 player_id1 | player_id2
------------+------------
          1 |          4
          2 |          3
          5 |          7
          6 |          8

I have tried countless different approaches, including GROUP BY, DISTINCT ON (), self JOINS, but haven't been able to come to a working solution.

The following result is what I am currently getting and am trying to avoid:

 player_id1 | player_id2
------------+------------
          1 |          4
          2 |          3
          3 |          4
          4 |          1
          5 |          1
          6 |          1
          7 |          1
          8 |          1

Where I'm getting stuck is how to eliminate a single player being allocated for a round more than once per SELECT query.

Any ideas on how to solve this would be highly appreciated.

Every row of the result depends on the previous row. A recursive CTE comes to mind, I tried that. But one would need to refer to the worktable in an OUTER JOIN or a subquery expression which is not allowed. This does not work (building on the table layout in my fiddle):

WITH RECURSIVE
   t0 AS (SELECT *, COALESCE(array_length(opp_log,1), 0) AS len FROM tbl)
,  t1 AS (
   SELECT t1.player_id AS pl, t2.player_id AS p2
         ,t1.len AS len1, t2.len AS len2
   FROM   t0 t1, t0 t2
   WHERE  t2.player_id <> t1.player_id
   AND    t2.player_id <> ALL (t1.opp_log)
   )
, cte AS (
   (
   SELECT pl, p2
   FROM   t1
   ORDER  BY len1 DESC, len2 DESC
   LIMIT  1
   )

   UNION ALL
   (
   SELECT pl, p2
   FROM   t1
   LEFT   JOIN cte c ON t1.p1 IN (c.p1, c.p2)
                        OR t1.p2 IN (c.p1, c.p2)
   WHERE  c.p1 IS NULL
   ORDER  BY len1 DESC, len2 DESC
   LIMIT  1
   )
   )
SELECT *
FROM   cte;

> ERROR:  recursive reference to query "cte" must not appear within an outer join

I don't think there is a halfway decent way to solve this with pure SQL. I suggest:

Procedural solution with PL/pgSQL

CREATE OR REPLACE FUNCTION f_next_round()
  RETURNS TABLE (player_id1 int, player_id2 int) AS
$func$
DECLARE
   rows int := (SELECT count(*)/2 FROM tbl);  -- expected number of resulting rows
   ct   int := 0;                             -- running count
BEGIN

CREATE TEMP TABLE t ON COMMIT DROP AS         -- possible combinations
SELECT t1.player_id AS p1, t2.player_id AS p2
     , COALESCE(array_length(t1.opp_log,1), 0) AS len1
     , COALESCE(array_length(t2.opp_log,1), 0) AS len2
FROM   tbl t1, tbl t2
WHERE  t2.player_id <> t1.player_id
AND    t2.player_id <> ALL (t1.opp_log)
AND    t1.player_id <> ALL (t2.opp_log)
ORDER  BY len1 DESC, len2 DESC;               -- opportune sort order

LOOP
   SELECT INTO player_id1, player_id2  p1, p2 FROM t LIMIT 1;

   EXIT WHEN NOT FOUND;
   RETURN NEXT;
   ct := ct + 1;                              -- running count

   DELETE FROM t                              -- remove obsolete pairs
   WHERE  p1 IN (player_id1, player_id2) OR
          p2 IN (player_id1, player_id2);
END LOOP;

IF ct < rows THEN
   RAISE EXCEPTION 'Could not find a solution';
ELSIF ct > rows THEN
   RAISE EXCEPTION 'Impossible result!';
END IF;

END
$func$  LANGUAGE plpgsql VOLATILE;

How?

Build a temporary table with remaining possible pairs. This kind of cross join produces a lot of rows with big tables, but since we seem to be talking about tournaments, numbers should be reasonably low.

Players with the longest list of opponents are sorted first. This way, players that would be hard to match come first, increasing the chance for a solution.

Pick the first row and delete related pairings now obsolete. Do need to sort again. Logically any row is good, practically we get the player with the longest list of opponents first due to initial sort (which is not reliable without ORDER BY, but good enough for the case).

Repeat until no match is left.
Keep count and raise an exception if the count is not as expected. PL/pgSQL conveniently allows to raise an exception after the fact, which cancels any previous return values. Details in the manual.

Call:

SELECT * FROM f_next_round();

Result:

player_id1 | player_id2
-----------+-----------
1          | 7
2          | 3
4          | 8
5          | 6

SQL Fiddle.

Note

This does not guarantee to calculate the perfect solution. I just returns a possible solution and uses some limited smarts to improve the chances to find one. The problem is a bit like solving a Sudoku, really and is not trivially solved perfectly.


How to handle MySQL database connections/pooling from Lambda -> RDS with Node I'm running into some problems while testing my Lambda function. I my test, I execute in parallel N executions of my Lambda function. In my lambda function, I retrieve a database connection through Knex. I was messing around with the setting in the db init pool: { min: 0, max: [1 or 5] }, which seems to fix/break things, depending on other things in my code.

Executing my lambda function 20 times outputs correct results, however, when I up it to 500 (what I am expecting to see in production will easily exceed this), things start to break. I'm running into errors such as Error: ER_CON_COUNT_ERROR: Too many connections, Error: pool is draining and cannot accept work (if I use Knex's knex.destroy() interface at the end of my lambda function). What is the correct way to handle connections and pooling for scale in AWS Lambda and RDS? Are the problems that I'm seeing on my local machine going to replicate if I were to run the same stress tests on AWS?

Spinning off thousands of processes to do tiny pieces of a task is simple.  But inefficient.  "Processes" are heavyweight.  Threads are somewhat heavy.  Thousands of connections to MySQL is possible, but not all at the same time.

In any multi-process or multi-threaded environment, having "too many" will slow things down.  This is because the OS (or some entity) is doing a lot of work to share the inadequate resources.  MySQL, for example, can handle hundreds of idle connections, but bogs down with more than a few dozen active connections.  When that is exceeded, it is best to go up the chain to the client, and throttle how often it is spawning new connections.

I am not familiar with the other products you mention, but I would recommend keeping simultaneous activity down to dozens, not hundreds.

Or find a way. in your application, to "iterate" instead of "recurse".  Meanwhile, keep in mind that MySQL is happier to handle "vectors" (tables) of data than to handle individual rows.  Maybe you can push some of what is "parallel" in the app down into MySQL as "vectors"?  Win-win:  Fewer connections; more efficiency in MySQL.


is it possible to reset innodb_row_lock without restart MySQL 5.7 Now I could using this command to check the innodb_row_lock values:
show status like 'innodb_row_lock%';

Innodb_row_lock_current_waits   4
Innodb_row_lock_time    22471927
Innodb_row_lock_time_avg    52
Innodb_row_lock_time_max    22327
Innodb_row_lock_waits   426696

is it possible to reset the data without restart database? I aready searching from internet but no one talks about this.

No.
The workaround:

SHOW GLOBAL VARIABLES LIKE 'Innodb_row_lock%';
Run your query/queries
SHOW GLOBAL VARIABLES LIKE 'Innodb_row_lock%';
Programmatically subtract the values.  (Well, perhaps only Innodb_row_lock_waits can really be subtracted.)

It may be possible to use information_schema.GLOBAL_STATUS, but (in some cases) you get hit by the 'Uncertainty principle'.
In my experience, these values are "typical":
Innodb_row_lock_current_waits  -- rarely more than 0
Innodb_row_lock_time  --  non interesting
Innodb_row_lock_time_avg  --  Usually 10-1000ms
Innodb_row_lock_time_max  --  Usually < 50K (ms)
Innodb_row_lock_waits  --  not useful, except in your case of "subtracting"
Where are you hoping to take this inquiry?
If you would like a review of your variables and status:  http://mysql.rjweb.org/doc.php/mysql_analysis#tuning


Can't open the mysql.plugin table error when installing MySQL on Mac OS Sierra I am attempting to install MySQL on macOS Sierra 10.12.5. I had a previous (non-functioning) install of MySQL so I followed the steps here to install a clean copy with Homebrew.

I had to create a var/log/mysql/error.log as it wasn't created automatically, however now it is logging I get the following error when I do a mysql.server start:

2017-07-24T13:36:28.554840Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2017-07-24T13:36:28.563492Z 0 [ERROR] unknown variable 'key_buffer=16M'
2017-07-24T13:36:28.563534Z 0 [ERROR] Aborting

This is how I initialised MySQL:

mysqld -initialize --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql

When I start MySQL with homebrew I get no error:

CRMPiccos-MacBook:~ crmpicco$ brew services start mysql
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)

Version info:

CRMPiccos-MacBook:log crmpicco$ mysql --version
mysql  Ver 14.14 Distrib 5.7.19, for osx10.12 (x86_64) using  EditLine wrapper

In my case mysql failed after the update because the user running mysqld (The user mysql) being unable to open the mysql data directory with write permission. The files were instead owned by myself. I fixed this with:

sudo chown -R mysql /usr/local/mysql/*

The orignal cause (for me) was not the Sierra upgrade itelf, but the overall process of updating applications in response. Specifically other instructions online for updating PHP with brew after the Sierra update had included a chown:

sudo chown -R $(whoami):admin /usr/local # DO NOT DO THIS

This had unfortunately altered the file permissions within /usr/local/mysql within /usr/local, causing the error.


Why when I use variables on SQL the Database Engine avoid the indexes? I'm improving our SSIS process passing the variables through a declare and I found this:
Here you have the plan execution using variables brentozar.com/pastetheplan/?id=rkSxs7VPv for the following statement:
select *
from fact_Venta
where Data_ID >= @data1
and Data_ID <= @data2

And here without variables brentozar.com/pastetheplan/?id=SJ8soXEww
SELECT *
FROM [fact_Venta]
WHERE [Data_ID]>=@1
AND [Data_ID]<=@2

As you can see, when I use the declare SQL Database Engine doesn't use the index. The index used is a non-clustered for the column used on the where.
Both executions were on the same server, with the same set options and at the same time executed.

Why do you believe using variables instead of parameters will improve performance?
With local variables, the plan is optimized for unknown values using average density values gleaned from statistics to estimate row counts. With parameters, OTOH, SQL Server uses the actual parameter values provided for the initial compilation to estimate row counts. This generally provides a more optimal plan but at the risk of reusing a cached plan that is suboptimal for different values.
I suggest you use a parameterized query and ensure stats are up-to-date. Add an OPTION (RECOMPILE) hint to the query to avoid parameter sniffing for the range query. This will avoid reusing a suboptimal plan and the compilation overhead is insignificant in the case of a large query executed relatively infrequently by SSIS. The recompile hint should be used judiciously and isn't appropriate for an OLTP workload query executed many times per second.
Note that the actual values are sniffed with either parameters or variables when the recompile hint is specified.


how to grant a user permission to create databases? I use phpMyAdmin powered by Mamp (free version) to manage Mysql databases.

I need to know how to grant a user permission to create databases.

Try this command

GRANT CREATE ON *.* TO 'user'@'host';

For further information, please see MySQL Documentation on GRANT syntax


SQL Server 2008 simple recovery mode issue During my daily maintenance backup task, I am attempting to set specific databases to recovery model simple along with shrinking the database.

ALTER DATABASE db_name SET RECOVERY SIMPLE
GO
DBCC SHRINKDATABASE (db_name)
GO

This happens at 1am along with some other tasks that use the same database. However, I am getting an error where

  Execute T-SQL Statement Task (AERIS\SQL2008) Execute TSQL on Local server connection   Execution time out: 0 Task start:  2011-12-08T01:00:04.
  Task end: 2011-12-08T01:16:45.
  Failed:(-1073548784) Executing the query
  "alter database db_name set recovery simple" failed with the following error: "ALTER DATABASE failed because a lock could not be
  placed on database 'db_name'. Try again later.
  ALTER DATABASE statement failed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly,
  parameters not set correctly, or connection not established correctly.

Could the issue be that since a user is logged on the the server at the point the recovery model is being changed? I'm puzzled since sometimes the recovery model change works intermittently.

If you change the recovery model to SIMPLE to back to either FULL or BULK_RECOVERY you make all your transaction log backups useless.  The recovery model shouldn't every be changed regularly.

Shrinking the database is simply fragmenting all the objects within the database tables.

You should stop doing both of these things.


You can join against a date dimension table.  I've included a sample Common Table Expression date dimension table - (my sample is for months instead of days).
To create a permanent Date Dimension table (also known as a calendar table), check out this post - Creating a date dimension or calendar table in SQL Server
--demo setup
CREATE TABLE #TEMP
(GRP_ID CHAR(8),
BILL_YR INT,
BEGIN_DT DATETIME,
END_DT DATETIME
)

INSERT INTO #TEMP
(GRP_ID, BILL_YR, BEGIN_DT, END_DT)
VALUES
('12345678','1','2019-01-01','2019-12-31'),
('12345678','2','2020-01-01','2020-12-31')

--common table expression date dimension
;with dates ([Date]) as (
    Select convert(date,'2019-01-01') as [Date] -- Put the start date here

    union all

    Select dateadd(month, 1, [Date])
    from dates
    where [Date] <= '2020-12-31' -- Put the end date here
)

select GRP_ID
    ,  BILL_YR
    ,  DATEPART(MONTH,[DATE]) as BEGIN_DT
    ,  DATEPART(YEAR,[DATE]) as END_DT
    from dates d
    join #temp t on d.Date >= t.BEGIN_DT
        and d.Date <= t.END_DT
option (maxrecursion 32767) -- Don't forget to use the maxrecursion option!

| GRP_ID   | BILL_YR | BEGIN_DT | END_DT |
|----------|---------|----------|--------|
| 12345678 | 1       | 1        | 2019   |
| 12345678 | 1       | 2        | 2019   |
| 12345678 | 1       | 3        | 2019   |
| 12345678 | 1       | 4        | 2019   |
| 12345678 | 1       | 5        | 2019   |
| 12345678 | 1       | 6        | 2019   |
| 12345678 | 1       | 7        | 2019   |
| 12345678 | 1       | 8        | 2019   |
| 12345678 | 1       | 9        | 2019   |
| 12345678 | 1       | 10       | 2019   |
| 12345678 | 1       | 11       | 2019   |
| 12345678 | 1       | 12       | 2019   |
| 12345678 | 2       | 1        | 2020   |
| 12345678 | 2       | 2        | 2020   |
| 12345678 | 2       | 3        | 2020   |
| 12345678 | 2       | 4        | 2020   |
| 12345678 | 2       | 5        | 2020   |
| 12345678 | 2       | 6        | 2020   |
| 12345678 | 2       | 7        | 2020   |
| 12345678 | 2       | 8        | 2020   |
| 12345678 | 2       | 9        | 2020   |
| 12345678 | 2       | 10       | 2020   |
| 12345678 | 2       | 11       | 2020   |
| 12345678 | 2       | 12       | 2020   |


Get specific result without using loop I've a table in which data is like this for single user

ID - Number - SubNumber - Name

1   101        201101     Jack
2   101        201102     Jack
3   101        201103     Jack
4   101        201107     Jack
5   101        201111     Jack
6   101        201112     Jack
7   101        201113     Jack
8   101        201161     Jack
9   101        201162     Jack
10  101        201163     Jack
11  101        201164     Jack
12  101        201165     Jack

I want to get records like this without using any kind of loop.

Number - Name -  SubNumber

101     Jack     (201101-201103, 201107, 201111-201113, 201161-201165)

Currently I'm able to get records in form of this

Number - Name -  SubNumber

101     Jack    (201101,201102,201103, 201107, 201111.201112,201113, 201161,201162,201163,201164,201165)

Query to get upper result is

SELECT  Number, Name
,STUFF((SELECT ', ' + CAST(SubNumber AS VARCHAR(50)) [text()]
     FROM [Table]
     WHERE Number= t.Number
     FOR XML PATH(''), TYPE)
    .value('.','NVARCHAR(MAX)'),1,2,' ') SubNumber
FROM [Table] t
GROUP BY Number,Name
having Number= '101'

Am totally stuck over here. Any kind of help will be appreciated.

First add a row_number() value to the mix, because I'm guessing you can't rely on ID to have no gaps.

WITH NumberedRows AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Number, Name ORDER BY SubNumber) AS RowNum
  FROM [Table]
)

--now compare RowNum to SubNumber. When the difference changes, there's been a gap in the sequence of SubNumber values.

, Diffs AS (
  SELECT *, SubNumber - RowNum AS TheDiff
  FROM NumberedRows
)
, Ranges AS (
  SELECT Number, Name,
    MIN(SubNumber) AS StartRange,
    MAX(SubNumber) AS EndRange
  FROM Diffs
  GROUP BY Number, Name, TheDiff
)
, RangeStrings AS (
  SELECT Number, Name,
    CASE WHEN StartRange = EndRange
         THEN CAST(StartRange AS VARCHAR(10))
         ELSE CAST(StartRange AS VARCHAR(10)) + '-' + CAST(EndRange AS VARCHAR(10))
    END AS RangeString
  FROM Ranges
)

--And then just do your concatenation as you have already solved. :)

SELECT ...
FROM RangeStrings;


server running- mysqladmin error- Development Source Tree I'm frustrated!
I have installed mysql using Development Source Tree(version 5.7.5) in Ubuntu 14.04 32-bit.

Starting

I started mysql in safe mode using:

mysqlnew@dpaks:~$ mysqld_safe &
[1] 4548
mysqlnew@dpaks:~$ 141024 11:18:58 mysqld_safe Logging to'/usr/local/mysql/data/dpaks.err'.
141024 11:18:58 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

However, on entering 'mysqladmin version', I get the following error:

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'deepaks'@'localhost' (using password: NO)'

If I give a prefix '--skip-grant-tables', mysqladmin starts and gives the following:

  mysqladmin  Ver 8.42 Distrib 5.7.5-m15, for Linux on i686

  Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.

  Server version        5.7.5-m15

  Protocol version  10

  Connection        Localhost via UNIX socket

  UNIX socket       /tmp/mysql.sock

  Uptime:           7 sec

  Threads: 1  Questions: 2  Slow queries: 0  Opens: 84  Flush tables: 1  Open tables: 79  Queries per second avg: 0.285

So, what is happening with the grants table?

Permissions:

For the purpose of running mysql, I created a user as well as a group by the name mysqlnew.
My entire installation at /usr/local/mysql is owned by mysqlnew. Permissions
were set to 777 recursively for the entire directory except for the data directory.
Data directory was given 744.
my.cnf was set to 755 for user mysqlnew. Its temp file was owned by root.
mysql.sock had permissions 777

Server is running? Yes, it is!

Its listed in process status.

mysqlnew  5022  4341  0 12:22 pts/8    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe
mysqlnew  5154  5022  3 12:22 pts/8    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/dpaks.err --pid-file=/usr/local/mysql/data/dpaks.pid --socket=/tmp/mysql.sock --port=3306

Also, the netstat command gave me the following:

tcp        0      0 localhost:mysql         *:*                     LISTEN      4680/mysqld

Error Log
The error log says the following:

2014-10-24T05:49:00.835656Z 0 [Note] InnoDB: 5.7.5 started; log sequence number 1296682
2014-10-24T05:49:00.854718Z 0 [Warning] Failed to setup SSL
2014-10-24T05:49:00.854736Z 0 [Warning] SSL error: SSL context is not usable without certificate and private key
2014-10-24T05:49:00.854757Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2014-10-24T05:49:00.854795Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2014-10-24T05:49:00.864657Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2014-10-24T05:49:01.399455Z 0 [Note] Event Scheduler: Loaded 0 events
2014-10-24T05:49:01.399499Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.5-m15'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

which means that server is running! Right?

Conf file Following is an excerpt from my my.cnf file located in etc directory:

[client]

port=3306
socket=/tmp/mysql.sock

[mysqld]

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
user = mysql
bind-address = 127.0.0.1
port = 3306
socket = /tmp/mysql.sock

I also verified that mysql.sock is being created in /tmp directory.

Question: Why am I getting an error when I start mysqladmin without using the prefix '--skip-grant-tables' while the server is running?

Any help would be wholeheartedly appreciated

PS: I have read thoroughly and tried all the solutions given in similar questions here but to no avail.

Update 1

When I gave --skip-grants-table and then when ran 'mysql', its working! But why should I skip it?

OMG! I can't believe I solved it!

I started mysqld. However, client could not be started. When I gave the command 'mysql', the following error was shown:

  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
  mysqlnew@dpaks:/usr/local/mysqlnew/bin$ ./mysql

Why not create a user account in mysql?! Hence, I created a user account 'root' with 'root' as the password by writing the following in a text file named init-file and placing it where the user mysqlnew have access right. Note that they must be in different lines. Also, ensure that the server is not running.

  UPDATE mysql.user SET Password=PASSWORD('root') WHERE User='root';
  FLUSH PRIVILEGES;

I started the server by:

  ./mysqld --basedir=/usr/local/mysqlnew --datadir=/usr/local/mysqlnew/data --query_cache_type=1 --init-file=/home/mysqlnew/init-file &

I then started the client by

  mysqlnew@dpaks:/usr/local/mysqlnew/bin$ ./mysql -u root -p
  Enter password:

Since I have now created a user account in mysql, I don't need to use --skip-grant-tables anymore for starting server.

However, its not yet finished!

  mysql> select 1+1;
  ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

So, now I need to rectify this!

  mysql> SET PASSWORD = PASSWORD('root');
  Query OK, 0 rows affected (0.28 sec)

And that is it! :)


How can I import an old pgdump into an updated schema? I'm DB newb doing a software upgrade, and my predecessor's original migration plan may not have been as well thought out as I thought...

The Plan was to use our backup, created via

pg_dump -U derp -h db-host -Fc derp > backup

Flash a clean VM with an upgraded software, with its fancy new schema and use

pg_restore -v -U derp -n public -c -1 -h db-host -d derp backup

to load it with all its fancy old data.

Unfortunately I'm getting errors because of the updated schema, with new columns that create new relations.  "cannot drop constrain X on table Y because other objects depend on it"

I tried using -a (data only) but that fails fast because there are many lookup tables with now duplicated keys.

Can I somehow import the existing copy?

If not, can I alter the new database schema in a way that will allow my old dump to work?

If not.... can I make a different, more useful copy of the old database?

If I'm asking really dumb questions, what should I be asking?

It sounds you are dealing with some 3rd party's application.  In other words, the app vendor and your predecessor are not the same person.

The 3rd party would generally provide you with two things, an installer to use for new customers, and an upgrade process to upgrade the schema between versions of their app for existing customers.  You either need to use one, or the other, not some bastardized combination between them.  Restoring a dump of the old schema+data over the top of a newly-initialized schema for new customers would not be expected to work, unless the vendor said it does.

The vendor might also provide instructions on how to upgrade your PostgreSQL version, either by itself or coupled with upgrading the application version.  If they do, then find and follow those instructions.  If they don't then you have a few options.

Go back to using the old database.  Run the upgrade scripts against it.  Worry about migrating that to a new database server later.
Create an empty database cluster (completely empty, not using the vendor's new-customer initialization stuff) and restore to that database cluster.  The easiest way to do that is probably to use the "-C" (note upper case) flag to pg_restore.  This will create the new database for you, with the name, encoding, etc. which was recorded in the dmp file.  Alternatively, perhaps you just use your existing cluster (initialized with the app vendors new-customer schema) and add the -C flag to your restore.  What that will do is drop the database they provided in-bulk (which will get around the dependency problems) and recreate it from what is in the dump file, meaning you get the old schema not the new one.  Either way, you will now have the old application schema running in a new PostgreSQL database.  Once this is working, then run the application upgrade script against this.
Ask the 3rd party app vendor what to do.


Managing tables in database I am new to web development field and particularly very poor at database management. I am developing a web application which resembles Project Management System where new companies can add their profile, create a project under them, under each project they can create a new task and so on....

Hence, my question is right now is should i use one common table for all the new profile (abc & co) created and one common table for all the users registered under profile (abc & co) created, or should i create new tables for each when a new profile is created and new table for each users and project created ?

If I understand your question well, and you want to build a database by making use of a relational database management system (RDBMS), you should create a table for every entity that you have determined as relevant in your scenario, e.g., one table for Company, one for Person (who are Employees of a given Company), one for Project, and one for Task.

Once you have defined your database structure representing entities by means of tables, you will face the need to store a given ocurrence of an entity, then you should use the corresponding table for doing so. For instance, if you want to store three occurrences of the entity called Company, say “Database Modeling Corporation”, “Application Programming Enterprises” and “User Experience Industries”, then you should INSERT (using DML in SQL) each one of them in the table called Company.

So, no, I would not suggest you creating a new table for storing every particular ocurrence of an entity, since this approach would be very unpractical and inflexible. In this sense, it is best to use the tables that you have already defined for each respective entity.

Every stored row (an occurence of an entity) must be uniquely identified in your tables so that, among other things, you prevent redundancy, therefore you need to use a PRIMARY KEY in this regard.

You also need to set the relationships that exist between the different entities/tables that you are working with, and the concept of FOREIGN KEY is a very powerful relational tool pertaining to this aspect. For example, a specific Task may belong to a particular Project, so yo should add a FOREIGN KEY to the table called Task, and such FOREIGN KEY should reference the PRIMARY KEY that has being set for the table called Project.


SQL Backup routine regarding DB Logs I recently had an incident that got me rethinking my backup plan: One of our MSSQL driven aplications stalled because the log fle had grown too big on the filesystem (it was 50 GBs IIRC).
It turned out that the aplication developer is setting the DB recovery method to 'Full', and the full backup nighlty routing does not purge and shrink that log file. In order to shrink it  had to backup it up, change the recovery method to 'Simple', and backup with shrink.
I obviously want to prevent that from happening, so I am looking for options.
This is an MSSQL 2012 Express x64 instance.
The application developer is totally non-cooperative (external contractor, works against everyone) so I am assuming he wouldn't want me to leave the DB in 'simple' mode.
I could easily tune my sqlcmd scripts to do a "backup in 'full' -> switch to 'simple' -> backup in 'simple' -> revert to 'full'" and keep the file at bay, but I fear I could be running some risk I am not foreseeing right now.
It seems to me that I should do this relatively often, as the larger the log file grows the more time it takes to backup and shrink, causing noticeable slowdowns.
If that routine is reasonable, and after each cycle I end up with 'a backup prior to purging the log' and 'a backup after the log was purged', and assuming both proceses exit without error, would it be wise to discard the first one? I would be keeping the 'data' backup  and the 'purged log' backup.
I should add that AFAIK by doing "full backups" but not "regular log backups" I am sacrificing the possibility to revert to "states between two full backups", but I am not hindering the full backups themselves, as I have restored and cloned them many times in the past without issues. But if this is wrong please do let me know.
Note: For simplicity's sake let's say that -for now- being able to revert to a nightly full backup is good enough.
I hope I was able to explain myself.
Thanks in advance.

I could easily tune my sqlcmd scripts to do a "backup in 'full' -> switch to 'simple' -> backup in 'simple' -> revert to 'full'" and keep the file at bay, but I fear I could be running some risk I am not foreseeing right now.

Don't do this. It is a horrible idea because it gives anyone else looking at the system the wrong impression and is only a workaround to lie to the primary developer. That is not a good reason to make a technical decision around database recovery. There are unknown risks since we don't know anything about your company, application, etc. so only you can speak to those.
The correct path is to get a consensus among stakeholders, including any stubborn developers, and agree on a RTO/RPO that meets the business needs. Then adjust the recovery model accordingly.
Assuming you don't want to do that based on your post:
If you are 100% sure you don't care about recovery more granular than daily backups, keep it in full recovery mode and have a transaction log backup job that points to NUL so that growth is not an issue. It won't save the files anywhere, but will still allow you keep keep log growth under control.
Also see:

Switching to Simple Recovery - shrinking transaction logs
expressdb.io: SQL Server Express Backups - Some more background on RTO/RPO and some sample jobs using Ola Hallengren's backup solution with some specific guidance for SQL Server Express.

Full disclosure: expressdb.io is my site.


Is long running query always more heavy than short running query Perhaps a stupid question but since I don't know how the databaseengine really works under the hood I don't know the answer.

More specifik I've learnt that the sql always tweaks the query so that it is the most optimized for the server. Not only just optimizing speed but also resource management.

So could could it be that my tuning of the query has a greater impact on server resources than the original long running query and can I see that somehow?

If so what are the usual ways to bring down the server trying to tune a query?

Eg. if I have a query that runs for 10m and I optimize to 30s could those 30s have a greater impact on the server performance than the 10min query?

Query can consume more server resources when executing faster.

For example, recreating index with SOTRT_IN_TEMPDB = ON impacts tempdb.

  This option increases the amount of temporary disk space that is used to create an index, the option could reduce the time that is required to create or rebuild an index when tempdb is on a set of disks different from that of the user database.

  https://msdn.microsoft.com/en-us/library/ms188281.aspx


Find changes made in the table in sql server 2012 I am using a SQL Server.I have more 1000 tables in a database which i want to track for data changes.I have read about CDC(Change Data Capture) and will enable it on all the 1000 tables.It creates multiple cdc tables to keep track of changed data.

But the problem here is on daily basis less than 200 tables are gonna be modified. But still i have to check all the 1000 tables cdc.[capture_instance]_CT tables to find if new data is been added to that particular table.

So is there a way i can find the list of tables which are modified with new data only.

Thanks in advance.

You can configure SQL Server Auditing (Server and Database Audits), you have the ability to audit CRUD operations as well as other activity. One warning is to be aware of how much data you are auditing, logs fill up fast.


Proper Index for WHERE X  1 AND Y  1 ORDER BY Z I have a relatively large table that is accessed in exactly one way, by exactly one query (on the read side).

The query filters the table by two columns (both bit), and sorts by a third column (an integer).  Note that the query names the columns (it doesn't actually use "*").  The "TOP 1000" is intentional and is part of the production query:

The query is generated--not sure why it generates the way it does.

SELECT TOP 1000
     *
FROM
     MyTable m
WHERE
     m.IsFlag1 != 1
     AND m.IsFlag2 != 1
ORDER BY
     m.SomeId

I have a covering index, with the main columns being IsFlag1 and IsFlag2.

The execution plan shows and Index seek on this index, and then a sort.  The sort takes up 97% of the cost of the query according to the execution plan.

I tried adding SomeId as a main column of the index (the third column), but the execution plan remained the same.

I then tried adding a covering index using only SomeId as a main column, leaving the original index in place.  The execution plan then does an index scan on the new query, and the operator cost is significantly lower (and is functionally much faster).

All that said, I'd like to optimize this query as much as possible.  Is there a way to modify the index so that it simply does a seek?

In general it is not possible to use an index seek on a condition x <> 1 and y <> 1.

With an index on x,y the best you can do is convert it into two range seeks (x < 1 and  x > 1) with a residual predicate on y <> 1 (and this wouldn't be able to use additional index key columns to avoid a sort)

For a bit column as it can only have three values. 0, 1, NULL logically WHERE bit_column <> 1 is equivalent to  WHERE bit_column = 0 but seems SQL Server doesn't take advantage of that here and convert the <> to = conditions for you.

Adding a couple of check constraints does the job though even though these are apparently redundant in that they don't actually restrict the allowable values for the datatype in any way (for NULL if a check constraint evaluates to UNKNOWN it counts as passing)

CREATE TABLE MyTable
  (
     Foo     INT,
     IsFlag1 BIT NULL CHECK (IsFlag1 IN (0, 1)),
     IsFlag2 BIT NULL CHECK (IsFlag2 IN (0, 1)),
     SomeId  INT
  );

CREATE NONCLUSTERED INDEX ix
  ON MyTable(IsFlag1, IsFlag2, SomeId)
  INCLUDE (Foo);

The plan now does show a seek on IsFlag1 = 0 AND IsFlag2 = 0

Or alternatively this filtered index also avoids the need for a SORT

CREATE NONCLUSTERED INDEX ix
           ON MyTable(SomeId)
           INCLUDE (Foo,IsFlag1, IsFlag2)
           WHERE IsFlag1 != 1 and IsFlag2 != 1

It does a scan of the filtered index (the qualifying rows ordered by SomeId) with a TOP to stop scanning after the 1,000 rows are retrieved. IsFlag1, IsFlag2 are INCLUDE-d in the index to avoid an unnecessary look up that occurs without this.


can I select columns based on their name in Mysql? is there a way to select columns using '%' or 'like'?
for example:
I have a table with 20 columns , and 4 of them are starting with "GPS_"(GPS_long,GPS_Lat,GPS_sat,GPS_Speed)
I want to run something like this:( I know this is NOT the way...)
SELECT like '%GPS_%' FROM sample.table

so it will bring only the GPS columns  ?
Thanks ,

No, you can't, nor should you even consider it.
This is a variation on ...

Never use "select *" in Production Code

.. and the risk is the same.
At the moment, your table is nice and tidy, with a few columns of limited size.
At some point, someone is going have the "Great Idea" of adding a whole bunch of BLOB fields and populating them with terabytes of video data.  If you're unlucky and those new fields just happen to be prefixed "GPS_", then your code would start retrieving these fields, even though it has absolutely no interest in them.
Always specify the fields that you want to retrieve explicitly.


How to store chess positions in a relational database (in particular, Postgres) I'm exploring the idea of building a website that involves analysis of chess positions. Chess positions themselves are described using a format called FEN, e.g. the starting position for a chess board can be described as:

rnbqkbnr/pppppppp/8/8/8/8/PPPPPPPP/RNBQKBNR w KQkq - 0 1

The part I'm particularly interested in ideas around how to model is the everything up to the first space. This describes the layout of each rank of the chessboard from the 1st rank to the 8th. Pieces are described using a combination of letters and upper/lower case to signify the piece kind and if it belongs to the black player (lowercase) or white (uppercase). The integer 8 indicates the number of empty spaces. After a move like 1. e4

rnbqkbnr/pppppppp/8/8/4P3/8/PPPP1PPP/RNBQKBNR b KQkq e3 0 1

You can see now how the 5th rank portion describes 4 spaces, a white pawn then 3 spaces.

So how can we go about modelling this in a relational database like Postgres? My naive approach is to start with storing the entire FEN string as a varchar, however, in the future, I'd like to migrate this to a structure that would enable me to easily search for structures within a chess position. An example could be, find me all the positions in the database where white pawns occupy the e4 and e5 positions (the rank segment for the 5th position: 3PP3).

A regex search of the FEN string could find the positions I'm interested in but would it be enough to simply index the FEN column? Should I think about storing each rank of the board as an individual column? Or could the entire board be represented as a matrix somehow.

If you want to hold the positions as text that's fine. But there are better text editors than Postgres. Holding each game as a file and each position as a row within that file, in FEN notation, would be a good approach. The files can be read and searched using the tools of your choice (grep; Python). Expanding the spaces as Daniel Vérité suggests would make life easier.

If you really want to use a DBMS Elasticsearch has a good reputation for finding patterns within textual data.

If you really want to use a relational database, however, I'd suggest designing a data structure using relations (i.e. tables) rather than relying on SQL's poorer text parsing capabilities.

One must make the mental step from the rules of chess as a game to those of relational schema design. In the latter we define entity types, and facts that are known about them. The entity types can be actual real-world items, concepts or events. There are many possible ontologies for any given problem. Specifically we must separate the database internal representation of information and how that information is formatted for human consumption. I do not present my solution as "correct" in an absolutist sense, only that it comes from a relational perspective on data modelling. Other equally relational approaches are possible.

What tables are required? Most obviously there's Game:

Game
  GameId        int  primary key
  WhitePlayer   varchar(50)
  BlackPlayer   varchar(50)
  .. others

I'd model each piece as a distinct row. Pawn 1 will be distinct from pawn 2 and so forth. I still need to record colour and type to reproduce a position. When a pawn is promoted the new piece must have an PieceId distinct from those pieces present at the start of a game. This is required by how I re-create a position below.

Piece
  PieceId    smallint  primary key
  Type       char(1)              -- (P)awn, (R)ook, (K)ing etc.
  Colour     char(1)              -- (W)hite, (B)lack;

Finally there will be a table to store moves. The choice here is between storing a move (like PGN) or storing a whole position (like FEN). Storing the positions introduces much redundant data - all the blank squares and the pieces which did not move on this turn. So I would store the moves individually.

Move
  MoveId       int
  GameId       int foreign key referencing Game.GameId
  PieceId      int foreign key referencing Piece.PieceId
  ToRow        char(1)
  ToColumn     smallint
  IsRemoved    boolean           -- set when a piece is taken from the board

  primary key (MoveId, PieceId)

The pair (ToRow, ToColumn) is where the piece ends up after a move. I'm thinking of the a1-h8 format. I do not need FromRow/ FromColumn. If we need this it can be recreated from PieceId and the history of moves. Neither will I check that the rules of chess are obeyed. That is a job for a different tier of the application.

Some chess moves will result in two rows in this table. For example during capture there will be one row for the taking piece and another for the taken. The latter will have IsRemoved set.

Each game starts with the same 32 rows in Move for the initial positions of the pieces. This may seem redundant but is required to reproduce any position. If not explicit it would have to be implied in each query as some pieces may not move during a game. This also allows for puzzle setup.

To reproduce a position we observe that each piece will be placed wherever it was most recently moved before that position. Take the following as pseudo-code; I've not tested it.

select p.GameId, p.MoveId, pc.Colour, pc.Type, q.ToRow, Q.ToColumn
from Move as p
inner join Move as q
   on q.GameId = p.GameId
   and q.MoveId <= p.MoveId
inner join Piece as pc
  on pc.PieceId = q.PieceId
where not exists
( select 1
  from Move as c
  where c.GameId = q.GameId
  and c.PieceId = q.PieceId
  and c.MoveId <= p.MoveId
  and c.MoveId >= q.MoveId
)
and not exists
( select 1
  from Move as d
  where d.GameId = q.GameId
  and d.PieceId = q.PieceId
  and d.MoveId <= p.MoveId
  and d.IsRemoved = True
)

Read this as for every move of every game (aliased as p) i.e. every position, find all moves in the same game which happened before this move (aliased as q). Omit those moves which have a subsequent move for the same piece in the current game, or which have been captured.

Once a position can be re-created as above, it is a simple extension to find positions where certain pieces (or types of pieces) sit in given configurations. It would be possible to structure the query to look for certain pieces in certain positions, then ensure they're in place at the same time, then retrieve the remaining pieces at that point. I would note that white pawns may occupy e4 and e5 for a large portion of a great many games so a lot of positions would satisfy this criteria.

Whether this is faster than the simple-but-dumb text approach only testing can tell.

This question sparked my interest. I wondered what a "relation-y" answer would look like, since an RDBMS was specified. I approached it somewhat as a Gedankenexperiment. Having worked through some queries, however, I'm not sure I have the ontology quite right. If the objective is to identify positions, I think positions are the objects that should be stored. This leads back to the OP's original suggestion. Yes, there is redundancy in the storage as most pieces do not change between positions. Practically speaking, though, at 64 bytes per position, say 50 moves per game, then a database of 10,000 games needs about 30MB. This is trivial. Although SQL is not great at text parsing it is certainly up to deciding if given offsets in a string contain specific values e.g. e4 and e5 hold white pawns. On balance I think the most practical implementation will be FEN with explicit spaces.


Derive Date Spans from Start and End Dates in SQL Server table I am using SQL Server 2016
I have a table that contains 1 row per month that a patient is assigned to a particular Provider.
A patient can be assigned to multiple providers during the year.
How can I derive date spans (startdate & enddate) to represent the time a patient was assigned to each provider.
My table looks like this:
+----------+---------------+------------+-----------+
| Provider | Patient       | StartDate  | EndDate  |
+----------+---------------+------------+-----------+
| 1922157  | 12345         | 20191201  | 20191231 |
| 1904176  | 12345         | 20191101  | 20191201 |
| 1904176  | 12345         | 20191001  | 20191101 |
| 1904176  | 12345         | 20190901  | 20191001 |
| 1904176  | 12345         | 20190801  | 20190901 |
| 1904176  | 12345         | 20190701  | 20190801 |
| 1904176  | 12345         | 20190601  | 20190701 |
| 1904176  | 12345         | 20190501  | 20190601 |
| 1904176  | 12345         | 20190401  | 20190501 |
| 1904176  | 12345         | 20190301  | 20190401 |
| 1904176  | 12345         | 20190201  | 20190301 |
| 1922157  | 12345         | 20190101  | 20190201 |
| 1922157  | 56789         | 20190101  | 20190201 |
+----------+---------------+------------+-----------+

In this case, patient 12345 was assigned to 2 different providers. One for 2 months, January and then December and the other for the rest of the year (10 months) February through November. Patient 56789 was only assigned to 1 provider (1922157) for 1 month (in December).
I'm trying to make it so my output looks like the below table but I am running into issues I think because the patient is assigned to the same pcp during 2 different times of the year. I tried using the lag function but I only get the correct results for some cases but not all such as this particular case.
+----------+---------------+------------+-----------+
| Provider | Patient       | StartDate  | EndDate  |
+----------+---------------+------------+-----------+
| 1922157  | 12345         | 20190101  | 20190201  |
| 1904176  | 12345         | 20190201  | 20191201  |
| 1922157  | 12345         | 20191201  | 20191231  |
| 1922157  | 56789         | 20191201  | 20191231  |
+----------+---------------+------------+-----------+

Update: Was doing some more research and came across the following post:
https://stackoverflow.com/questions/35900765/ms-sql-combine-date-rows-into-start-end-date
I just fit my table into the code in the answer for above question and tested for a few of my cases and it looks like it might get the job done. Unfortunately, my base table has 140k rows of dates it will need to calculate through so I am not sure how long it will take to run. Has been running now for 6 minutes, I will post back with results.

I think I understand what you're trying to do. You're trying to get the start date and end date of a patient at a provider, as long as there is no gap between the start and end dates of the periodes. I've created a test table with the data you sampled.
Create table test (Provider int, Patient int, startdate date, enddate date)
insert into test (Provider, Patient, StartDate, EndDate)

SELECT * FROM
(SELECT 1922157 as Provider  , 12345 as Patient         , '2019-12-01' as StartDate , '2019-12-31' as EndDate
union all SELECT 1904176  , 12345         , '2019-11-01'  , '2019-12-01'
union all SELECT 1904176  , 12345         , '2019-10-01'  , '2019-11-01'
union all SELECT 1904176  , 12345         , '2019-09-01'  , '2019-10-01'
union all SELECT 1904176  , 12345         , '2019-08-01'  , '2019-09-01'
union all SELECT 1904176  , 12345         , '2019-07-01'  , '2019-08-01'
union all SELECT 1904176  , 12345         , '2019-06-01'  , '2019-07-01'
union all SELECT 1904176  , 12345         , '2019-05-01'  , '2019-06-01'
union all SELECT 1904176  , 12345         , '2019-04-01'  , '2019-05-01'
union all SELECT 1904176  , 12345         , '2019-03-01'  , '2019-04-01'
union all SELECT 1904176  , 12345         , '2019-02-01'  , '2019-03-01'
union all SELECT 1922157  , 12345         , '2019-01-01'  , '2019-02-01'
union all SELECT 1922157  , 56789         , '2019-01-01'  , '2019-02-01' )t

The Idea is to start by ordering data and trying to get those that start date and end dates match, in order to detect a hole in the dates. I do that with the "ROW_NUMBER" function. I then find all the rows that match and take the first StartDate and max EndDate for those who match, and then I add all the rows that are "alone" and have no match.
I think it works wit the data you provided. I didn't get to test it with other data.
Recursivity is another option to find the Min/Max dates of different values but I didn't go with recursivity in this case. (feel free to give better names, I went a little fast)
;With RowsWithNum AS
(
SELECT Provider, Patient, StartDate, EndDate, ROW_NUMBER() OVER (ORDER BY Provider, patient, StartDate) as RowNum
FROM test
)
,BeforeAndAfterDates AS
(
SELECT a.Provider, a.Patient, a.StartDate, a.RowNum, a.EndDate, b.StartDate EndStartDate, DATEPART(DAYOFYEAR, b.StartDate)-DATEPART(DAYOFYEAR,a.EndDate) as DateDiffInDays, b.EndDate as EndEndDate, b.RowNum as EndRowNum
FROM RowsWithNum a
LEFT JOIN RowsWithNum b ON b.Provider=a.Provider and b.Patient=a.Patient and b.StartDate=a.EndDate
)
SELECT Provider, Patient, Min(StartDate) as StartDate, Max(EndEndDate) as EndDate, Min(RowNum) as RowNum
FROM BeforeAndAfterDates
WHERE DateDiffInDays=0
GROUP BY Provider, Patient
UNION
SELECT a.Provider, a.Patient, a.StartDate, a.EndDate, a.RowNum
FROM BeforeAndAfterDates a
LEFT JOIN BeforeAndAfterDates b ON b.EndEndDate=a.enddate
WHERE a.DateDiffInDays IS NULL AND b.RowNum IS NULL

And here is my result.


Changing default permenant USERS tablespace to bigfile I'm trying to build an oracle database server but it's setting up the users tablespace as a smallfile.   How can I either install brand new database with users being bigfile or changing the existing USERS tablespace to bigfile?

Change existing:

SQL> create bigfile tablespace users2 datafile '/oradata/ORCL2/users.dbf' size 100M;

Tablespace created.

SQL> alter database default tablespace users2;

Database altered.

Create new one:

DBCA allows you to choose bigfile tablespace for a Custom Database:

A "General Purpose" or "Data Warehouse" database is actually a pre-created database, there you do not have this option.

Or you can specify bigfile when creating a database with the CREATE DATABASE statement.


Are there any database engines which will intuit the join condition based on the existing foreign keys? It seems strange to me that, when I've defined a foreign key, the engine cannot use this information to automatically figure out the correct JOIN expressions, but instead requires me to re-type the same clauses. Are there any databases, perhaps some kind of research project, that will inspect the existing foreign keys?

I'm not aware of any that use foreign keys (and that would can be messy if you have more than one foreign key back to the same table)

But Oracle will base joins off of name of columns if you don't specify a specific join type nor a join condition, or if you use NATURAL JOIN


Data Extract and submit across the network I've been given a somewhat vague requirement regarding a data extract process which I need some help with.  I'm fairly new to database warehousing etc. but I will try to explain everything relevant.

We are beginning a project to set up a shared services domain between our site (A) and another site (B).  Currently we both have independent and unrelated systems to store the same thing.  Site A has the best system and so the plan is to move our database (SQL Server) to the shared services domain and then both sites will use the single database.  We will both have high-speed dedicated network bandwidth to the shared services domain.

The infrastructure is being taken care of by network guys but I am going to have to implement the business reporting side of things.  The requirements around this are:

Database X has data 'belonging' to both site A and site B (but referring to the same nature of information)
Site B's data needs to be extracted and manipulated into some kind of report format (Site B's data can be logically separated from Site A's data based on some values)
The data needs to be sent to Site B over the network
The process needs to be quick

I have some basic knowledge of SSIS, which just because of what I've read on MSDN and forums seems like the best way to go, but actually I'm not aware of any alternatives to SSIS.

What are the options for this requirement?

Thanks

If I understand correctly, I wouldn't use SSIS. This is invoked on demand or scheduled to shift data whereas I read this as "automatic shift" of data

So, I'd consider replication or database mirroring and the let the DB engine move data for you:

Replication: use this if you need a subset of the datas (not all column, rows or tables)
Mirroring: use this to copy the entire database (you'd need a snapshot on the mirror to get data out though)


Manual failover of AAG in SQL Server 2012 Just a question regarding manual failovers.  Does it matter if we use SSMS to perform the failover vs going to the Failover Cluster Manager and move the role to the secondary node?  Is there a preferred method or either one is fine to do?

Do not use Failover Cluster Manager for your failovers. It can cause problems.

  Do not use the Failover Cluster Manager to move availability groups to
  different nodes or to fail over availability groups. The Failover
  Cluster Manager is not aware of the synchronization status of the
  availability replicas, and doing so can lead to extended downtime. You
  must use Transact-SQL or SQL Server Management Studio.


SQL 2005 Unused procedures I'm trying to get a list of unused procedures across the whole SQL server. I've accomplished getting a list of ALL procedures and now I'm using the following script to identify which procedures have been used.

DECLARE @command varchar(1000)
SELECT @command =

'USE [?] SELECT

qt.text AS "SP Name",
qs.execution_count AS "Execution Count",
qs.max_logical_reads,
qs.max_logical_writes,
qs.creation_time,
DB_NAME() as DatabaseName,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS "Age in Cache"
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qt.text DESC'

EXEC sp_MSforeachdb @command

I'm aware that this will only show procedures which have been used post restart. The data seems to work somewhat - however; the issue i'm having is that it seems to show the WHOLE procedures text IE:

  CREATE PROCEDURE dbo.proc_MSS_PropagationGetQueryServers  AS
  DECLARE @Temp table(          ServerID int,          SearchServerName
  nvarchar(31),          IndexDirectory nvarchar(260),
  MachineStatus int,          LastPropagationTime datetime,
  PropagationStatus int      )        INSERT INTO          @Temp
  SELECT          SearchServerID,          SearchServerName,
  IndexDirectory,           MachineStatus,          LastPropagationTime,
  1      AS           PropagationStatus      FROM
  dbo.MSSPropagationSearchServerTable        UPDATE          @Temp
  SET          PropagationStatus = 0      WHERE          NOT EXISTS
  (SELECT * FROM dbo.MSSPropagationSearchServerReady AS R WHERE ServerID
  = R.SearchServerID) AND          (1 < DATEDIFF(m, LastPropagationTime, GETUTCDATE()) OR          30 < DATEDIFF(s, LastPropagationTime,
  GETUTCDATE()))        CREATE TABLE #UnresponsiveQueryServers (
  ServerID int )      INSERT #UnresponsiveQueryServers          EXEC
  dbo.proc_MSS_PropagationGetUnresponsiveQueryServers        UPDATE
  @Temp      SET          PropagationStatus = 3      WHERE
  ServerID IN ( SELECT ServerID FROM #UnresponsiveQueryServers )
  UPDATE          @Temp      SET          PropagationStatus = 2
  WHERE          MachineStatus = 0 OR          MachineStatus = 1 OR
  MachineStatus = 2        SELECT           SearchServerName,
  IndexDirectory,          MachineStatus,          PropagationStatus
  FROM           @Temp

I just want the name of the procedure itself. So in this case  proc_MSS_PropagationGetQueryServers

Anyone able to help on this?

What you are looking for is not really possible, at least not in the spirit of what you are ultimately trying to accomplish. As I explained in my answer on what is pretty much a duplicate question -- Query last time a view, funcion or sp was last used/read -- this approach doesn't take into account infrequently accessed code paths in the app code and "support" code that is executed manually, usually in SSMS. It sucks, but you really just need to do an exhaustive review of all code that could be executing Stored Procedures, as well as support tickets that include instructions on what to run in case of X, or even internal wiki pages that might have instructions indicating to execute Stored Procedures (or use UDFs, etc).

Also, you can create a table to log usage, and for those Stored Procedures that you believe are "unused", add a line at the top to insert into this log table. Check that log table periodically, and set a cut-off time with your team / department where everyone feels comfortable getting rid of that code if it hasn't been accessed within that amount of time.

P.S. I have gone through this process before. And I have had support people call me (or my manager) shortly after a release because I had dropped some code that was not referenced in any other code or internal wiki page, etc (and believe you me, I was quite thorough in my research!). BUT, it was being used in a Microsoft Access app that a support engineer created years prior to make certain support tasks / requests take less time (i.e. not needing to wait 2 weeks for a DBA to get around to running the Stored Proc so that a customer could do what they needed to do). And since none of us database engineers was aware of this app even existing (it was only on a few desktops in the support area, and certainly not in SVN), it made it a bit difficult to take into account.

P.P.S. regarding this statement:

  I'm aware that this will only show procedures which have been used post restart.

That is the most that can be shown. But, a restart is not the only way to clear cached system data. Either of the following commands will also clear that data:

DBCC FREEPROCCACHE;

DBCC FREESYSTEMCACHE('ALL');

AND, SQL Server will periodically dump the cached data when there is memory pressure (i.e. low available physical memory).

Hence, you cannot use the output of any of the DMVs / DMFs for this purpose.

P.P.P.S. The sys.dm_exec_query_stats DMV works at the server / instance level, not the database level. Meaning, not only do you not need to wrap such queries in sp_MSforeachdb, but you don't want to do that anyway since the same result set will get repeated per each of your databases.

P.P.P.P.S. The sys.dm_exec_sql_text DMF that you are already using conveniently returns columns for objectid and dbid. The dbid column can be used along with the DB_NAME() built-in function that you are already using to get the "current" database name for that query. And, both the dbid and objectid columns can be used along with the OBJECT_NAME() built-in function to get the name of the stored procedure, relative to that "current" database (as opposed to the database that you are running this query in, which can return erroneous values since the objects are potentially not in that database). And then both of those functions can be used again in the ORDER BY clause.

Taking all of that info into account, the re-written query is:

SELECT  DB_NAME(qt.[dbid]) AS [DatabaseName],
        OBJECT_NAME(qt.[objectid], qt.[dbid]) AS [SP Name],
        qs.execution_count AS [Execution Count],
        qs.max_logical_reads,
        qs.max_logical_writes,
        qs.creation_time,
        DATEDIFF(Minute, qs.creation_time, GetDate()) AS [Age in Cache]
FROM    sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
ORDER BY DB_NAME(qt.[dbid]) DESC,
         OBJECT_NAME(qt.[objectid], qt.[dbid]) DESC;


Why does this query work? I have two tables, table_a (id, name) and table_b (id), let's say on Oracle 12c.

Why does this query not return an exception?

select * from table_a where name in (select name from table_b);

From what I understand, Oracle sees this as

select * from table_a where name = name;

But what I don't get is why?

The query is syntactically correct SQL even if table_b does not have a name column. The reason is scope resolution.

When the query is parsed, it is first checked whether table_b has a name column. Since it doesn't, then table_a is checked. It would throw an error only if neither of the tables had a name column.

Finally the query is executed as:

select a.*
from table_a  a
where a.name in (select a.name
                 from table_b  b
                );

As for the results the query would give, for every row of table_a, the subquery (select name from table_b) - or (select a.name from table_b  b) - is a table with a single column with the same a.name value and as many rows as table_b. So, if table_b has 1 or more rows, the query runs as:

select a.*
from table_a  a
where a.name in (a.name, a.name, ..., a.name) ;

or:

select a.*
from table_a  a
where a.name = a.name ;

or:

select a.*
from table_a  a
where a.name is not null ;

If table_b is empty, the query will return no rows (thnx to @ughai for pointing that possibility).

That (the fact that you don't get an error) is probably the best reason that all column references should be prefixed with the table name/alias. If the query was:

select a.* from table_a where a.name in (select b.name from table_b);

you would have got the error straight away. When table prefixes are omitted, it is not difficult for such mistakes to happen, especially in more complex queries, and even more important, go unnoticed.

Read also in Oracle docs: Resolution of Names in Static SQL Statements the similar example B-6 in Inner capture and the recommendations in the Avoiding Inner Capture in SELECT and DML Statements paragraphs:

  Qualify each column reference in the statement with the appropriate table alias.


Backing up MySQL w/MyISAM on Amazon RDS So, for various good reasons, we're maintaining a mySQL database using a MyISAM engine on Amazon RDS.  I've just taken over this application, and I'm concerned we're not getting good backups, since snapshots aren't reliable.

I've had an idea. I propose to

periodically create a read replica
elevate it to be a separate instance
and do a mysqldump from that replica.

Does this make sense?

MyISAM tables are not guaranteed to be consistent with disk and, effectively, Amazon recommends to manually do the equivalent to a FLUSH TABLES WITH READ LOCK (you cannot do that in RDS, so you have to lock and flush each MyISAM table individually). So you are right in the fact that backups may not be doing correctly on your setup.

Creating a backup from a slave is a very common way of doing backups, because in your case, you do not care about blocking or affecting the performance of the slaves. But you are not really solving the problem, because MyISAM is not supported for read replicas, as it basically uses the same snapshoting mechanism, potentially inconsistent with non transactional databases.

If you really need hot & consistent backups, you need InnoDB. Otherwise, lock, flush & snapshot.

If you want to guarantee the consistency of a backup, though, setting a replica and seeing that it works is one of the typical easy ways to partially do it. So creating a replica and leaving it on for a while without replication breaking may mean that at least it has all the records and tables you used in your latest transactions (with no guarantee overall).


design DB review and check constraint for postalcode help? Hi. I created this database to just to sharpen my skill. I have one Question - when I create I make the FOREIGN KEY personID in Addresses table but I changed it to be AddressID in Persons table because I can have a list of Addresses. When I create program  is that fine? I create check constraint to check the canadian postal code is my  constraint right ?

CREATE TABLE dbo.Addresses(
  AddressID int IDENTITY(1,1) PRIMARY KEY,
  Street varchar(255),
  City varchar(255),
  Province varChar(255),
  PostalCode varchar(10) CHECK(PostalCode>='t1w1v1' and PostalCode<='t9w9v9')
)

CREATE TABLE dbo.Persons (
  PersonID int IDENTITY(1,1) PRIMARY KEY,
  FirstName varchar(255) NOT NULL,
  LastName varchar(255) NOT NULL,
  MiddleName varchar(255) null,
  DateOfBirth date NOT NULL,
  Gender char(1) NULL CHECK(Gender='M' OR Gender='F'),
  AddressID int FOREIGN KEY REFERENCES dbo.Addresses(AddressID)
)

Yes, that would be fine if your design goal is that Persons have only 1 Addresses. With this design, each Persons can have 1 Addresses but two or more Persons can have the same Addresses.

It's all a matter of your business needs. If the above is what you are trying to get, then yes, it is correct.

However, I think it's most common the other way around where the Addresses would have a foreign key to the Persons because a Persons could have more than one Addresses.

As for your constraint to check the postal code - well first off you are missing the space and it's lower case. Whether that will work will depend on which database system you are using. I tested it with PostgreSQL and it does not work.

I don't think you can really have such a simple constraint to fully validate a Canadian postal code. For example, there are some letters and some numbers which are never used. I'm a bit rusty on my Canadian postal office codes but I seem to recall that number 5 is never used as it's too similar to S, etc.


Sleeping SPID with Open Transcations This is an output from sp_whoisactive. Should I be worried about the sleeping spids with high cpu numbers and an open transaction? The sql_command value is the same as sql_text. CPU and reads do increase over time.

I have hundreds of these at any given time...would this impact CPU?

I am not sure if your case is similar, but you can see similar behavior with Distribution Agent SPIDs on subscriber.

When its not applying any changes it looks something similar.

So in this case its normal behavior.

Hundreds of idle sessions, as you can imagine, would not impact CPU.  But those sessions might be waking up and doing things. The real question is where are those spids coming from? Are all of them from the same application? If so, it might be time to ask the application support guys why it has so many connections hanging around. Maybe they are not closing connections properly.


MongoDB stuck in startup2 state I have a Mongo replica set (MongoDB server version: 4.0.3) with primiry, secondary and arbiter. Size of database is about 7 Tb. I add a member to replica set. It stucks in sturtup2 mode for a month! In first day it synhronize 3 Tb and then go slower and slower synchronyzing about 200 Gb each day. Previous member I add to this replica set was added for about a two or three days. I remove all load from replica set, there is nothing else take place - only synchronisation. Maybe there is a way to tell it to synchronyse faster?

Maybe there is a way to tell it to synchronize faster?

Since the amount of data copied to the new secondary is very large (7TB) so the issue happens. MongoDB suggests the following procedure for a large amount of data set.

Sync by Copying Data Files from Another Member

This approach “seeds” a new or stale member using the data files from an existing member of the replica set. The data files must be sufficiently recent to allow the new member to catch up with the oplog. Otherwise the member would need to perform an initial sync.

Copy the Data Files

You can capture the data files as either a snapshot or a direct copy. However, in most cases you cannot copy data files from a running mongod instance to another because the data files will change during the file copy operation.

Sync the Member

After you have copied the data files from the “seed” source, start the mongod instance and allow it to apply all operations from the oplog until it reflects the current state of the replica set.

STARTUP2

Each member of a replica set enters the STARTUP2 state as soon as mongod finishes loading that member’s configuration, at which time it becomes an active member of the replica set and is eligible to vote. The member then decides whether or not to undertake an initial sync. If a member begins an initial sync, the member remains in STARTUP2 until all data is copied and all indexes are built. Afterwards, the member transitions to RECOVERING.


Formatting Timestamp in Default Constraint Can you format a TIMESTAMP WITH TIME ZONE column in a CREATE TABLE default constraint in Postgres v10.x?

Example Code:

create_date TIMESTAMP WITH TIME ZONE
    DEFAULT to_char(current_timestamp, 'DD-MON-YYYY HH24:MI:SS')
    NOT NULL

When running this, I get the following error:

  ERROR:  column "create_date" is of type timestamp with time zone but default expression is of type text
  HINT:  You will need to rewrite or cast the expression.
  SQL state: 42804

However, according to the Postgres Docs this type of formatting should be possible.

I've also tried casting this with the to_timestamp() method, however, I get the following error:

  ERROR:  function to_timestamp(timestamp with time zone, unknown) does not exist
  HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
  SQL state: 42883

Can you format a TIMESTAMP WITH TIME ZONE column in a CREATE TABLE default constraint in Postgres v10.x?

No because that doesn't make any sense. Timestamps are stored as timestamps. It's a special type. You format things for display. Any point in time is stored the same way regardless. Formatting time should arguable not be done in the database at all.

Think of it like this, you may need Roman Numeral representation for some columns, but even if you do the database will stored integers in their binary representation.

Now if you want to change the clients default output formatting for timestamps, you can certainly do that.  From the docs

  The date/time style can be selected by the user using the SET datestyle command, the DateStyle parameter in the postgresql.conf configuration file, or the PGDATESTYLE environment variable on the server or client.


MySQL - Simple update is very slow We are having trouble with simple updates on a single table taking a long time. The table contains ~5 Million rows.

Here is the table:

CREATE TABLE Documents (
    GeneratedKey varchar(32) NOT NULL,
    SourceId int(11) NOT NULL,
    Uri longtext,
    ModifiedDateUtc datetime NOT NULL,
    OperationId varchar(36) DEFAULT NULL,
    RowModifiedDateUtc datetime NOT NULL,
    ParentKey varchar(32) NOT NULL,
    PRIMARY KEY (SourceId, GeneratedKey),
    KEY IX_RowModifiedDateUtc (RowModifiedDateUtc),
    KEY IX_ParentKey (ParentKey),
    KEY IX_OperationId (OperationId(36))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here is the update query:

UPDATE Documents
SET OperationId = 'xxxx'
WHERE SourceId = 12345
AND ParentKey = '0965b3983ceb0e8e41ab47b53e37d0f3';

This query updates ~80k rows and takes around 60 seconds to complete, and the more rows updated the longer it takes which results in timeouts. The index IX_ParentKey cardinality is ~830k. Note that a select with the same WHERE clause returns very quickly (< 1s).

Query profiling:

starting                0.000072
checking permissions    0.000006
Opening tables          0.000019
init                    0.000023
System lock             0.000266
updating                50.415424
end                     0.000039
query end               0.024398
closing tables          0.000037
freeing items           0.000051
cleaning up             0.000022

When switching to MyISAM, the query took only 2 seconds. What could be slowing down the updates, I assume the index needs to be rebuilt? Is there any way we can optimize this?

There are many possible explanations:

UUIDs have terrible performance on large tables.
What was the value of innodb_buffer_pool_size?  It should be about 70% of available RAM.  Poor caching, especially due to UUIDs could ba a problem.
That UPDATE needs INDEX(SourceId, ParentKey) (in either order).
Why prefix the index on OperationId when it is already that length?
Don't use utf8 on hex strings.
Packing UUIDs into BINARY(16) would shrink the table, thereby providing more speed.
Updating 80K rows at once takes a lot of effort, especially when planning for a possible ROLLBACK.  This may partially explain why InnoDB seems to be slower than MyISAM.  Will you often update that many rows?  Sound like a design flaw.


MySQL replication backup How to take MySQL Master-Master and Master-Slave replication backup?. I have setup Master-Master replication with a separate slave for each master on Ubuntu.

What happens if,

I issue or schedule mysqldump on master servers.
I allow developers to make changes directly connecting to master servers with the GUI tools(workbench).

Any help is appreciated. Thank you!

QUESTION #1

  I issue or schedule mysqldump on master servers.

ANSWER TO QUESTION #1

DEPENDS ON STORAGE ENGINE

If your database is all InnoDB, you can launch mysqldump to get a live snapshot of the Master, but you can run the great risk of slowing down transactions. (See my old post MySQL quick database transfer between two servers for caveats)

DEPENDS ON DB TRAFFIC

The slowdown of transaction incurred would be due to maintaining snapshots of data to support repeatable reads.

MORAL OF THIS STORY

It is best not to do it on a live server, even if it is possible

QUESTION #2

  I allow developers to make changes directly connecting to master servers with the GUI tools(workbench)

ANSWER TO QUESTION #2

You can and will break the transaction issued by the mysqldump --single-transaction. See my old post row locking within ACID transaction innodb to see the commands that will disrupt a consistent backup if executed on the master during a mysqldump

MORAL OF HIS STORY

Use a MySQL Replication Slave and issue mysqldumps without disturbing the Master


Import Data replaces NULL with 0 I am using sql server 2008 r2 express. I am getting some problems when i use import data feature to import data from one database to another. I am using SSMS to do it. While using the wizard i choose "Select identity insert" option to copy the identity element property. By using this identity elements are imported properly. But after importing i compared the source database and destination database and found some changes!!. All NULL values in the table are replaced by 0. So how can i avoid it.Is there any option to copy it as it while using import data wizard? Please help

Choose to save the package instead of running it right away.
Then edit the dtsx file and on your copy task check "keep nulls".
Then run the dtsx.
I didn't test it but think it will solve your case.

Testing the suggestion on the comment on express with advanced services... it worked well:)
You create the destination table, set a null default on the column and after importing data with the dtsx you remove the default or set it to whatever you want. dtsx import will replace nulls with the default value of the column when you can't check the "keep nulls".

create table dbo.test (
 a int null
 , other columns ...
 )

alter table dbo.test add constraint x default null for a

--Run Import task here

alter table dbo.test drop constraint x


How to grant more memory to run R processes in SQL Server 2016 R Services I'm running a fresh install of SQL Server 2016 Standard Edition (13.0.5026.0) with R Services on Windows Server 2016 Standard.  With all sorts of happiness I went to run an R script that completes within 15 mins on my lame desktop machine (8 GB RAM, 1 Intel proc @3.4 GHz) and found that it takes over two hours to run on my carefully configured server.

I'm wondering what else I need to do to get my R processes using more memory?  My script spawns 12 R processes, which sit there using up to 460MB each even though there is plenty available and I've told them to use more.  On my desktop in R Studio this same script happily grabs all available memory.

Here's the relevant details on my setup:

72 GB RAM
2 Intel Xeon procs @ 2.0GHz with 6 cores each
SQL Server max memory set to 32 GB (I've also had it at 57.6GB, no difference noted)
Though I don't think it matters since I don't have Enterprise Edition w/Resource Governor, I've set the internal and external pools to use 40 and 60 max memory percent, respectively.
I have my MAX DOP set at 8 per MS's recommendations, but I've also tried disabling parallel processing by setting this to 1 as I've heard that under certain conditions R's parallel processing can complete with SQL server's.  No change in memory usage by R was seen, but certainly my CPU usage decreased significantly.
I've also added a line to the RLauncher config file (E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\rlauncher) to use MEMORY_LIMIT_PERCENT=60.
I've confirmed that the SQL Server Service and SQL Server Launchpad services have the correct user rights in the local security policy to replace a process level token, manage memory, etc.
Yes, I can successfully run basic R scripts.
Yes, I've restarted the server after making my configuration changes.
I've ensured that the server is running in high performance mode and not power saving mode in both the BIOS and in Windows.
Ensured no errors in the event logs that point to an issue.
Ensured that the worker accounts MSSQLSERVER01-20 all have permissions to log on locally and are part of the SQLRUserGroup.

You'll want to use the resource governor.  Here is a good blog.

Enable Resource Governor (do some research on this):

ALTER RESOURCE GOVERNOR RECONFIGURE;

This will tell you how much % of your memory R can use.

SELECT * FROM sys.resource_governor_external_resource_pools

Sample query to change values from the link:

-- Default value
ALTER EXTERNAL RESOURCE POOL [default]
WITH (AFFINITY CPU = AUTO)
GO

CREATE EXTERNAL RESOURCE POOL RService_Resource_Pool
WITH (
     MAX_CPU_PERCENT = 10
    ,MAX_MEMORY_PERCENT = 5
);

ALTER RESOURCE POOL [default] WITH (max_memory_percent = 60, max_cpu_percent=90);
ALTER EXTERNAL RESOURCE POOL [default] WITH (max_memory_percent = 40, max_cpu_percent=10);
ALTER RESOURCE GOVERNOR reconfigure;

Don't forget to run reconfigure again:

ALTER RESOURCE GOVERNOR RECONFIGURE;


Order by values with most incidence I have a table as this example:

CREATE TABLE customerEvent(
    customerID  int NOT NULL,
    eventID     int NOT NULL
)

customerID may appear in many records. I use the following query to identify most frequent customerID:

SELECT
    customerID,eventID
    ,row_number() OVER(PARTITION BY customerID ORDER BY eventID) as frequency
FROM customerEvent
ORDER BY frequency DESC

With this I see those most frequent customerID. But they appear only once on the top of the list. Example:

customerID  eventID frequency
7           3       5
15          9       4
7           1       4
9           4       3
7           10      3
15          2       3

I need all records of the most frequent customerID to appear on the top, example:

customerID  eventID frequency
7           3       5
7           1       4
7           10      3
15          9       4
15          2       3
9           4       3

Any idea how I can build a query that does that?

It seems you don't really need the "frequency" for the sorting, only the "maximum frequency" per customer. You can use the COUNT(*) OVER (PARTITION BY customerID):

SELECT
    customerID,
    eventID,
    ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY eventID) AS frequency
FROM
    customerEvent
ORDER BY
    COUNT(*) OVER (PARTITION BY customerID) DESC,
    customerID,
    frequency DESC ;

The second part of the order by needs to be customerID just in case of ties. I assume  that if you have two or more customers with the same number of events, you don't want their events mixed up.


Will Rebuild and Reorganize improve Database Migration Script performance execution? I would like to know if a Database Schema Migration Script (generated by Visual Studio 2008 or greater) will run faster if my target database got indexes rebuilt and/or reorganized.

Note: For all tables, when index fragmentation level is between 5% and 30% I reorganize the index. When index index fragmentation level is above 30% I rebuild the index.

Note 2: Database schema migration script generated by Visual Studio has a lot of commands to alter table structure, add constraints, foreing keys and indexes in order to keep target database structure updated/syncronized with files contained in our Database project (.dbproj file).

If you are just migrating the Schema itself, and not any of the data, Index Fragmentation of your user database tables should be irrelevant. The instructions in the migration script will come from system database tables holding the metadata for your user database/schema.


security tools for mysql We are using MySQL Community version on Windows and are trying to secure it as best we can.  Assuming that changing the version (for example purchasing the Enterprise edition) is not possible in our environment, I would love to hear how others are doing things such as:

Auditing
Alerts
Monitoring
Password rules
Account disabling after inactive for X days
Backups
Integrity checking
Encryption
Steganography detection

For example, we are using the general log for our cheap "auditing".  Maybe not the best solution, but something is better than nothing.  Any free tools out there, or maybe homegrown scripts anyone would like to suggest?

Here are a few starting points (and I don't have answers to everything).
Disclosure: I suggest a couple tools that were written by myself (and of course used in production).

Consider this login audit plugin (open sourced, find in GitHub). I developed & compiled in on Linux, you will have to find your way on Windows. This plugin will log any login attempt (successful or failed) onto your server.

We use the above to audit any login on any of our servers -- using logstash to aggregate all our logs, and kibana to view/query it. Likewise, you can query this log and send alerts to graphite / query by Nagios -- I don not know what bests suits Windows.

Consider common_schema's security_audit. This will generate a report on bad security settings (duplicate passwords, excessive privileges etc.). It does not do any steganography.
Check out GreenSQL - I never tried it myself. It's a proxy sittting between your clients and your MySQL server, that can analyse and block queries on the fly.
Check out MariaDB security enhancements, e.g. role management. Available for Windows.


Backup Oracle Data Guard with TDPO We are using TSM/TDPO SBT to backup Oracle Databases. When using Oracle Data Guard with TDPO, what is the best strategy to ensure backups continue after failover/switchover?
TSM/TDPO uses a feature called datamove which makes sure that backups from primary and standby are moved to one TSM node. (TSM node A (primary) and B (standby) move there Backups to C. Restore will use C).
The RMAN scripts called by TDPO need a logic to prevent backups are taken from both (primary and standby). This could be done by querying the role of the database.

When using Oracle Data Guard with TDPO, what is the best strategy to
  ensure backups continue after failover/switchover?

Use RMAN catalog, because without a catalog, backups taken on one site do not appear in the controlfile of the database on the other site. You need a centralized catalog for backups in a Data Guard configuration.

Use role-based services, and TNS entries with connect-time failover, using these services. Use these TNS entries for logging in to the database when performing backups. This way you always log in to the primary/standby database, and you do not need to check for the role of the database.

  TSM/TDPO uses a feature called datamove which makes sure that backups
  from primary and standby are moved to one TSM node. (TSM node A
  (primary) and B (standby) move there Backups to C. Restore will use
  C).

The database/RMAN layer has nothing to do with. Just configure TSM and TDPO properly, and this will be transparent for the database, always using the same tdpo.opt file.

  The RMAN scripts called by TDPO need a logic to prevent backups are
  taken from both (primary and standby). This could be done by querying
  the role of the database.

No, see above, role-based services, connect-time failover TNS entries.


Revoke User Access to all System Information Functions on Postgresql Is there a way to remove access to all system information functions for a given user (which is already limited to read only access) in Postgres?

For example, a user could normally run pg_conf_load_time() or SELECT current_setting('datestyle');.

Normally you could do do

REVOKE ALL ON FUNCTION current_setting(text) FROM my_user;

but I get no privileges could be revoked for "current_setting".

Is there a way to disallow access to all functions on these pages?

https://www.postgresql.org/docs/current/functions-admin.html
https://www.postgresql.org/docs/current/functions-info.html

You would have to

REVOKE EXECUTE ON FUNCTION ... FROM PUBLIC;

because that's the default privilege for functions.

Revoking a privilege that was not granted is a no-op in SQL.

After revoking the privilege from PUBLIC, you'd have to explicitly grant it to all users that should have it.

But I think you shouldn't do that.

Why not?

These modifications will not survive a major upgrade.
PostgreSQL does not consider this information security critical, and I think that is correct.

Everybody should be able to see the settings relevant for the session.

Note that certain parameters that reveal details about the operating system where PostgreSQL is running are only visible to superusers or members of the pg_read_all_settings role anyway.
You may break applications that rely on these functions.


Impact of deleting 100GB table on Mirroring I'm about to delete a large table which is approximately 100GB in size.  I'd like to understand the impact on the mirroring that's in place on this db.  Can anyone confirm?

What is type of mirroring Sync or Async

Yes there would be impact and impact would be in terms of amount of transaction log being generated. Delete logs each row which is being deleted so logging will be heavy. Your approach should be to delete in batches this would minimize lock escalation, blocking and thus would not hamper concurrency much. You should also consider performing this activity when load is relatively less

How strong is your network connecting principal and mirror. You are going to put heavy load on it if you delete 100G table so becareful with that aspect also its good to ask network team to have a look on network during this period of time you would not like your mirror to lag behind principal.

Can you truncate the tables or selectively truncate tables, it is super quick and logging is very very less as compared to delete but NOTE there are limitations with truncate table and before proceeding please read below Microsoft documentation

http://msdn.microsoft.com/en-gb/library/ms177570.aspx


How do I import a specific line from a text file into MySQL I'm building an app to import data from a text file to a database.

I want to import only specific lines from the text file.  With Navicat I found that we could limit number of line when importing data.

I also found an article saying that we could limit it, so I tried this:

load data infile 'c:/coop/wkwk.txt' into table jo fields
    terminated by '|' lines terminated by '\n' limit 10,100;

But I get an error in SQL syntax, what is the QUERY to make this work?

If you are using Unix/Linux you could try using a fifo buffer:

mkfifo /tmp/mysql.pipe
# Select rows 101 to 110
sed -n 101,110p filename > /tmp/mysql.pipe &
mysql –uusername -p -Ddatabasename -e "LOAD DATA LOCAL INFILE ' /tmp/mysql.pipe ' INTO TABLE tablename"

rm -f /tmp/mysql.pipe


Mongo Create a user as admin for any database raise an error I am trying to create a simple user with the rights permission to access to any database and can do any actions. When I trying to execute the createUser command I got this error:

db.createUser({ user: "mongoadmin" , pwd: "mongoadmin", roles: ["userAdminAnyDatabase", "dbAdminAnyDatabase", "readWriteAnyDatabase"]})
2015-08-20T17:09:42.300+0000 E QUERY    Error: couldn't add user: No role named userAdminAnyDatabase@new_vehicles_catalog

The problem above only happen when I enable the auth configuration and I need it.

So, How do I create an user with admin permission for any database. I want it because I configured my mongo services to uses authentication connection. If I want to execute a dump of my data I have to use this authentication parameters.

Please any help?

Using mongo version 3.0.5.

the service is on Amazon Linux AMI 2015.03 (HVM), SSD Volume Type - ami-1ecae776

The '...AnyDatabase' roles should be added to the admin database for the user:

use admin
db.createUser({ user: "mongoadmin" , pwd: "mongoadmin", roles: ["userAdminAnyDatabase", "dbAdminAnyDatabase", "readWriteAnyDatabase"]})

Any database:

Database User Roles
Database Administration Roles

Admin database:

Cluster Administration Roles
Backup and Restoration Roles
All-Database Roles

Extra: To give different roles on different database and admin:

db.createUser({user:"replSetManager",pwd:"password",roles:[{role:"clusterManager",db:"admin"},{role:"dbOwner", db:"adminsblog"},{role:"readWrite", db:"departmentblog"},{role:"read", db:"otherblog"}]})


Unable to delete Log Shipping configuration I need some help with my SQL 2005 server.
I am trying to setup log shipping, I've successfully done so for all user-databases, except one.
From the looks of it, it once had log shipping configured until the secondary server was decommissioned before log shipping could be deleted. The old DBA deleted the old jobs.

So what's left is a database that is configured for log shipping, but the LSBackup job has been deleted.
Which means, every time I try to open the log shipping configuration wizard, I am told that the job does not exist and to delete the configuration (Seems sensible).
But I can't delete it because it is trying to connect to the secondary server... which does not exist so just times out and fails.

Any suggestions would be much appreciated!

If something is still setup on the secondary server you need to run sp_delete_log_shipping_secondary_database there.

You can clear the log shipping configuration on the primary by executing sp_delete_log_shipping_primary_secondary

USE master;
GO
EXEC master.dbo.sp_delete_log_shipping_primary_secondary
    @primary_database = N'<PrimaryDatabase>'
   ,@secondary_server = N'<SecondaryServer>'
   ,@secondary_database = N'<SecondaryDatabase>'
GO


Inserting 30k prices each minute, how can I skip an entry if the price did not change? I receive around 30k price updates for various articles every minute and I need to write the new prices into our database (Which is a MySQL Database running InnoDB)

The table containing the prices right now has only three columns: Article_id, InsertTime, and ArticlePrice

My Problem is: I do not want to insert duplicate prices, but I want to store historical data. So when the price goes up or down I insert it. When it stays the same I skip it. Since I receive a lot of data I can not query first and then decide if I can insert or not, as this approach will be too time consuming.

Can this be solved on a database level? For example by adding a time column and using it as a primary key?

I also thought of solving it in the application itself, for example by keeping a List of the previous prices and compare them there before inserting into the database, but I want to guarantee a consistent database and this seems complicated.

Any help is appreciated.

You have 30K new records for the 'current' minute; put them in a temp table; include PRIMARY KEY(ArticleId).

Note: History will need PRIMARY KEY(ArticleId, InsertTime).

You need to find the 'latest' existing price for each of the 30K articles in the existing history table.  But first, you need to find the latest InsertPrice for each article in this table:  (This is 'select#1')

SELECT ArticleId, MAX(InsertTime) AS InsertTime
    FROM History
    GROUP BY ArticleId

Now back to the latest price (select#2):

SELECT ArticleId, ArticlePrice
    FROM ( select#1 ) t1
    JOIN History  USING(ArticleId, InsertTime)

Now let's find the changed prices (select#3):

SELECT temp.ArticleId, temp.LastInsert, temp.ArticlePrice
    FROM ( select#2 ) t2
    LEFT JOIN temp  USING(ArticleId)
    WHERE temp.ArticlePrice != t2.ArticlePrice

I may have also allowed for 'new' Articles to arrive.  (But I am not sure.)

Now, let's copy the new Prices over:

INSERT INTO History (ArticleId, InsertTime, ArticlePrice)
    select#3;


Access Variable Outside Dynamic SQL What am I missing here?  If I run a print @sql statement and run that statement directly in SSMS I get the value that I am after.  However, if I run the dynamic sql and try to access Print @value1 outside the dynamic sql no value is returned.

How can I access the value of @value1 from outside my dynamic sql?  In my syntax below, it always returns nothing, as if the value does not exist!

Declare @Name varchar(100), @value1 varchar(100), @sql nvarchar(max)
Set @Name = 'Jalen'
set @sql = 'Declare @value1 varchar(100) Select @value1 = id
            from openquery(192.168.500.300,''Select id
             from massmailer where empname in ('''''+@Name+N''''')'')'
Print @value1
EXEC sp_executesql @SQL

I tried to modify my Exec statement to:

EXEC sp_executesql @SQL, @retvalOUT=@value1 OUTPUT;

This gives me an error of:

  Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
  Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

Community Wiki answer originally left as question comments

You need:

EXEC sp_executesql
    @sql,
    N'@value1 varchar(100) OUTPUT',
    @value1=@value1 OUTPUT;

An online demonstration of the technique: http://rextester.com/GDL49591


MySQL Drop User Fails for Remote Host We're clearing out some old MySQL users that connected from remote hosts during a database transfer.

Why is this DROP USER query failing for the user asdfasdf_imac4? I'm logged in as root.

mysql> select user,host from mysql.user;
+------------------+----------------------+
| user             | host                 |
+------------------+----------------------+
| root             | 127.0.0.1            |
| root             | ::1                  |
| debian-sys-maint | localhost            |
| forum            | localhost            |
| root             | localhost            |
| asdfasdf_imac4   | ‘303.303.303.90’     |
| asdfasdf_imac4   | ‘303.303.303.92’     |
+------------------+----------------------+
7 rows in set (0.00 sec)

mysql> drop user 'asdfasdf_imac4'@'303.303.303.90';
ERROR 1396 (HY000): Operation DROP USER failed for 'asdfasdf_imac4'@'303.303.303.90'

I have never seen an IP address like that before. Since 303 is an impossible number for an IP address, you should just save yourself the headache and delete both from mysql.user

DELETE FROM mysql.user WHERE user='asdfasdf_imac4';
FLUSH PRIVILEGES;

Give it a Try !!!


Drop multiple columns at the same time from a SQL Server table I am currently looking some way to delete multiple columns at the same time from a given table in sql server.

One way of doing that is to use ALTER statement as shown below:

Alter Table [USER_DB].[dbo].my_Table
DROP COLUMN "ID",
            "Name",
            "Relationship"  ;

I am using above statement in some stored procedure to drop the columns and it's working fine. However, it creates problem when I re-ran this statement and columns are already dropped.

I found some nice solution on SO which doesn't fails even when columns are dropped.

IF EXISTS(SELECT 1
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME='my_Table'
          and column_name= 'ID'
          and Table_SChema='DBO')
  BEGIN
    ALTER TABLE my_Table
    DROP COLUMN
     ID
  END

I tried above statement to drop multiple columns at the same time but it's not working.

I was looking something like this.

IF EXISTS(SELECT 1
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME='my_Table'
          and column_name= 'ID'
          and column_name= 'Name'
          and column_name ='Relationship'
          and Table_SChema='DBO')
  BEGIN
    ALTER TABLE my_Table
    DROP COLUMN
     ID
    ,Name
    ,Relationship
  END

You're currently looking for a column_name that has different values, so you'll never pass the test. You have to search in different rows to make it work.

    IF EXISTS(SELECT 1
              FROM INFORMATION_SCHEMA.COLUMNS
              WHERE TABLE_NAME='my_Table'
              and column_name= 'ID')
AND EXISTS (SELECT 1
              FROM INFORMATION_SCHEMA.COLUMNS
              WHERE TABLE_NAME='my_Table'
              and column_name= 'Relationship')
      BEGIN
        ALTER TABLE my_Table
        DROP COLUMN
         ID
        ,Name
        ,Relationship
      END