Find tables with columns with empty and NULL values in Postgresql After some research I found an example where I can find tables and its columns with NULL values. But the function also returns true when the column is empty. It gives me errors when I try to add an or statement. How can I modify the function so it returns also true when the column contains blank values? This is the function I found:
create function has_nulls(p_schema in text, p_table in text, p_column in text)
                returns boolean language plpgsql as $$
declare
  b boolean;
begin
  execute 'select exists(select * from '||
          p_table||' where '||p_column||' is null)' into b;
  return b;
end;$$;

Assuming "empty" and "blank values" means empty string ('').
This function checks whether the passed table has any NULL or empty values ('') in the passed column (which must be a string type or some other type where the empty string is valid; not the case for numeric types for instance):
CREATE FUNCTION f_has_missing(_tbl regclass, _col text, OUT has_missing boolean)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE
   format($$SELECT EXISTS (SELECT FROM %s WHERE %2$I = '' OR %2$I IS NULL)$$, _tbl, _col)
   INTO has_missing;
END
$func$;

Call:
SELECT f_has_missing('tablename', 'column')

Or, optionally, schema-qualified:
SELECT f_has_missing('schema.tablename', 'column')

db<>fiddle here
Probably most important: Never concatenate parameter values into SQL code blindly. That's begging for SQL injection. I sanitized the code with format(). See:

Table name as a PostgreSQL function parameter

There are a couple of smart expressions to check for both:
(string_col = '') IS NOT FALSE
(string_col <> '') IS NOT TRUE
coalesce(string_col, '') = ''

See:

Best way to check for “empty or null value”

But I chose this plain and more verbose expression for two reason:
string_col = '' OR string_col IS NULL

I have grown fond of simple, obvious code, and none of the above is as clear as this. But more importantly, this expression can use an index on (string_col), while the above cannot - which makes a big difference for big tables. See the added demo in the fiddle!
db<>fiddle here
Obviously, we want a different name for the function than "has_nulls", now.
I use an OUT parameter for convenience and short code. Now we can assign to it and be done.
And I use regclass as IN parameter for the table name. This way I can provide a schema explicitly or not. Again, see:

Table name as a PostgreSQL function parameter

Aside, one might loop through all columns of a table or a whole db to find any such column at once. Related:

Replace empty strings with null values
Set empty strings ('') to NULL in the whole database


Redefine Transaction log size restricion fails I am trying to set the transaction log's max size restriction to UNRESTRICTED using SSMS using the following dialogue:

The actual setting is set to 2.097.152 MB - a nonsense value that exceeds the capacity of the drive.

I have tried to set it to some other value e.g. 85 GB (something between actual TL size and max drive capacity) as well as to unrestricted.

Neither of those settings is getting persisted. The original restriction setting returns again and again.
Why is it impossible to change?

(Note: All DBs are running in Compat Lvl 80)

I ran the command:

ALTER DATABASE myDB
MODIFY FILE ( NAME = N'myDB_Log', MAXSIZE = UNLIMITED)

against several servers. Some of them afterwards show MAXSIZE=-1. Others show max_size=268435456 in sys.database_files. The ones having -1 also show the correct radio button selected in the dialogue.

If you look at Maximum Capacity Specifications for SQL Server, you will see that the maximum size for transaction log is 2 TB:

SQL Server Database | Maximum sizes/numbers    | Maximum sizes/numbers
Engine object       | SQL Server (32-bit)      | SQL Server (64-bit)
 ...                | ...                      | ...
File size (log)     | 2 terabytes              | 2 terabytes

Then once converted to MB:

2 TB <=> 2.048 GB <=> 2.097.152 MB

According to ALTER DATABASE File and Filegroup Options, Unlimited means that the value is set to the maximum file size:

  MAXSIZE { max_size| UNLIMITED }
  ...
  UNLIMITED
  Specifies that the file grows until the disk is full. In SQL Server, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB.

Either of these query should set it to unlimited:

USE [master]
GO
ALTER DATABASE [mydb] MODIFY FILE ( NAME = N'mydb_log', MAXSIZE = 2097152MB)
GO
ALTER DATABASE [mydb] MODIFY FILE ( NAME = N'mydb_log', MAXSIZE = UNLIMITED)

Using this query once set to UNLIMITED:

SELECT max_size, max_size/1024*8 FROM sys.database_files

Output for row 2 (LOG) should be:

maxsize     | maxsize in MB
268435456   | 2097152

268435456  is the number of 8 KB pages
2097152 is the same number converted to MB (<=> 2 TB)

Although maximum size cannot be set to a negative value, sys.database_files may show -1 for maximum size:

  Databases that are upgraded with an unlimited log file size will report -1 for the maximum size of the log file.

The maximum value for MAXSIZE using ALTER DATABASE ... MAXSIZE ... is 2147483647 pages (<=> 16777208 MB => 16 TB) but with a LOG file and a size above 2097152 MB, it fails with this error:

  Msg 5150, Level 16, State 2, Line 1
  The size of a single log file must not be greater than 2 TB.

Besides 2 TB is also probably an old limitation due to MBR:

  Master boot record
  ...
  Since block addresses and sizes are stored in the partition table of an MBR using 32 bits, the maximum size as well as the highest start address of a partition using drives that have 512-byte sectors (actual or emulated) cannot exceed 2 TiB−512 bytes (2,199,023,255,040 bytes or 4,294,967,295 (232−1) sectors × 512 (29) bytes per sector). Alleviating this capacity limitation was one of the prime motivations for the development of the GPT.
  Source: Wikipedia


Capturing sp_executesql statement in sql profiler After catching some data in a SQL Profiler session, I can almost always see a second entry of type RPC:Completed for each query using a sp_executesql statement. I know that this is done to create re-usable execution plan by reducing the query with parametrization.

e.g.

exec sp_executesql N'SELECT s.* FROM s WHERE s.C= @O',
@O uniqueidentifier',
@O='11112222-4444-3333-1212-5555666677'

Duration: 10 ms

Next entry:

SELECT s.* FROM s WHERE s.C= @O

Duration: 10 ms

Looking at the total sum(Duration) of the profiler events, do I have to count both of those? The query was executed once, so 10 ms was the correct result. Instead the total result is 20 ms, with I think is just WRONG and a program-error of Profiler. Do I have to filter one of them away anyhow and how?

You don't have to count both. As you see, you get an rpc completed for sp_executesql. Your Select which shows up as StmtCompleted is a "subtask" of the rpc. The SQL trace already has the sum of all statements inside a rpc summed up for you.


Transposing hierachical data from one VARCHAR to two INTs THE PROBLEM

The table where the data is recorded is

CREATE TABLE [dbo].[Almoxarifado](
    [idAlmoxarifado] [varchar](20) NOT NULL,
    [tipoAlmoxarifadoId] [varchar](30) NOT NULL,
    [entidadeId] [bigint] NOT NULL,
    [dtInclusao] [smalldatetime] NOT NULL,
    [dtUltimaAlteracao] [smalldatetime] NULL,
    [descricao] [varchar](255) NOT NULL,
    [terceiro] [bit] NOT NULL
) ON [PRIMARY]

And this is a SELECT TOP 10 * example (the table has more than 1 million entries):

As you can see, the idAlmoxarifadofield is storing a hierarchical data that has no hierarchical relationship as most hierarchical table have.

Now I want to bring this data to a usual hierarchical table:

CREATE TABLE [dbo].[Almoxarifado2](
    [idMaster] [bigint] IDENTITY(1,1) NOT NULL,
    [idAlmoxarifado] [int] NOT NULL,
    [idAlmoxPai] [int] NOT NULL DEFAULT ((0)),
    [entidadeId] [bigint] NOT NULL,
    [tipoAlmoxarifadoId] [varchar](30) NOT NULL
    [dtInclusao] [datetime] NULL DEFAULT (getdate()),
    [dtUltimaAlteracao] [datetime] NULL,
    [descricao] [varchar](255) NOT NULL,
    [terceiro] [bit] NULL DEFAULT ((0)),
    PRIMARY KEY ([idMaster])
)

On this table I'll have a usual father-son relationship where the "father" of a given field will always be it's idMastercorrespondent (unless it is on the first degree of the hierarchical chain; then it's "father" would be 0).

So, if we follow the same order shown on the image above, we should have (after inserting the transposed data on the new table) :

THE QUESTION

How can I do this transposition is a safe and painless way since the original table has more than 1 mi entries?

THE RULES

There are no restrictions on how many top-levels could exist; neither children levels. Our idea is to work with a mask to make the data useful on our application (we workd with Motorola Handheld Computer; check MC45 and MC3190). Let's say that the customer that buys our solution has several Warehouses where he stocks his products. In this case he'll be able to define a mask (say 9.99.99.999 or 9.9.99.99.99.999) and our system will have to be able to use this mask to allow him to register his warehouses in those levels.

So you can basically say that our system is foccused on logistics in a fashion.

Where the 1st level could be "NY South Warehouse", the 2nd "Room #2", 3rd "Corridor A", 4th "Locker 2" and 5th "Shelf 3".

As your data looks a bit like hierarchyId I thought about using that.  Initial versions did not scale well up to 1 million rows, however a few indexing choices on the main temp table have helped.  However issues could also be due to my test data, so can you tell me a bit more about your hierarchy?  For example how many top-level parents are there, how many levels are there on average, how many children might each level have?

In the meantime, here is a version so you can have a look through it and see if hierarchyId could work for you.

The basic idea is to create a temp table with the original ids converted into hierarchyIds, then walk the hierarchy to work out the lineage.  This temp table (which could be permanent) could then be used for the migration:

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('[dbo].[Almoxarifado]') IS NOT NULL DROP TABLE [dbo].[Almoxarifado]
IF OBJECT_ID('[dbo].[Almoxarifado2]') IS NOT NULL DROP TABLE [dbo].[Almoxarifado2]
GO

CREATE TABLE [dbo].[Almoxarifado](
    [idAlmoxarifado] [varchar](20) NOT NULL,
    [tipoAlmoxarifadoId] [varchar](30) NOT NULL,
    [entidadeId] [bigint] NOT NULL,
    [dtInclusao] [smalldatetime] NOT NULL,
    [dtUltimaAlteracao] [smalldatetime] NULL,
    [descricao] [varchar](255) NOT NULL,
    [terceiro] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Almoxarifado2](
    [idMaster] [bigint] IDENTITY(1,1) NOT NULL,
    [idAlmoxarifado] [int] NOT NULL,
    [idAlmoxPai] [int] NOT NULL DEFAULT ((0)),
    [entidadeId] [bigint] NOT NULL,
    [tipoAlmoxarifadoId] [varchar](30) NOT NULL ,
    [dtInclusao] [datetime] NULL DEFAULT (getdate()),
    [dtUltimaAlteracao] [datetime] NULL,
    [descricao] [varchar](255) NOT NULL,
    [terceiro] [bit] NULL DEFAULT ((0)),
    PRIMARY KEY ([idMaster])
)
GO

/*
-- Test data
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
VALUES
    ( '1', 'TESTE', 12, '29 Apr 2016', NULL, '0000000', 0 ),
    ( '1.1', 'TESTE', 12, '29 Apr 2016', NULL, '0000001', 0 ),
    ( '1.1.01', 'TESTE', 12, '29 Apr 2016', NULL, '0000002', 0 ),
    ( '1.1.01.01', 'TESTE', 12, '29 Apr 2016', NULL, '0000003', 0 ),
    ( '1.1.01.01.001', 'TESTE', 12, '29 Apr 2016', NULL, '0000004', 0 ),

    ( '1.1.01.01.002', 'TESTE', 12, '29 Apr 2016', NULL, '0000005', 0 ),
    ( '1.1.01.01.003', 'TESTE', 12, '29 Apr 2016', NULL, '0000006', 0 ),
    ( '1.1.01.01.004', 'TESTE', 12, '29 Apr 2016', NULL, '0000007', 0 ),
    ( '1.1.01.01.005', 'TESTE', 12, '29 Apr 2016', NULL, '0000008', 0 ),
    ( '1.1.01.01.006', 'TESTE', 12, '29 Apr 2016', NULL, '0000009', 0 )
GO
*/

-- Add 10 parent levels
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
VALUES
    ( '1', 'TESTE', 1, '1 Jan 2016', NULL, '00000001', 0 ),
    ( '2', 'TESTE', 1, '1 Feb 2016', NULL, '00000002', 0 ),
    ( '3', 'TESTE', 1, '1 Mar 2016', NULL, '00000003', 0 ),
    ( '4', 'TESTE', 1, '1 Apr 2016', NULL, '00000004', 0 ),
    ( '5', 'TESTE', 1, '1 May 2016', NULL, '00000005', 0 ),
    ( '6', 'TESTE', 1, '1 Jun 2016', NULL, '00000006', 0 ),
    ( '7', 'TESTE', 1, '1 Jul 2016', NULL, '00000007', 0 ),
    ( '8', 'TESTE', 1, '1 Aug 2016', NULL, '00000008', 0 ),
    ( '9', 'TESTE', 1, '1 Sep 2016', NULL, '00000008', 0 ),
    ( '10', 'TESTE', 1, '1 Oct 2016', NULL, '00000010', 0 )

-- For each parent, add 3 sublevels
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
SELECT
    idAlmoxarifado + '.' + CAST( x.y AS VARCHAR(10) ),
    tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro
FROM [dbo].[Almoxarifado] a
    CROSS JOIN ( SELECT TOP 5 idAlmoxarifado y FROM [dbo].[Almoxarifado] ) x

-- add n sublevels
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
SELECT
    idAlmoxarifado + '.0' + CAST( x.y AS VARCHAR(10) ),
    tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro
FROM [dbo].[Almoxarifado] a
    CROSS JOIN ( SELECT TOP 8 idAlmoxarifado y FROM [dbo].[Almoxarifado] WHERE idAlmoxarifado Not Like '%.%' ) x
WHERE idAlmoxarifado Like '%.%'

-- Add 8 sublevels
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
SELECT
    idAlmoxarifado + '.0' + CAST( x.y AS VARCHAR(10) ),
    tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro
FROM [dbo].[Almoxarifado] a
    CROSS JOIN ( SELECT TOP 13 ROW_NUMBER() OVER( ORDER BY idAlmoxarifado ) y FROM [dbo].[Almoxarifado] ) x
WHERE idAlmoxarifado Like '%.%.%'

-- Add 9 sublevels
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
SELECT
    idAlmoxarifado + '.0' + CAST( x.y AS VARCHAR(10) ),
    tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro
FROM [dbo].[Almoxarifado] a
    CROSS JOIN ( SELECT TOP 21 ROW_NUMBER() OVER( ORDER BY idAlmoxarifado ) y FROM [dbo].[Almoxarifado] ) x
WHERE idAlmoxarifado Like '%.%.%.%'
GO

INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
SELECT
    idAlmoxarifado + '.0' + CAST( x.y AS VARCHAR(10) ),
    tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro
FROM [dbo].[Almoxarifado] a
    CROSS JOIN ( SELECT TOP 9 ROW_NUMBER() OVER( ORDER BY idAlmoxarifado ) y FROM [dbo].[Almoxarifado] ) x
WHERE idAlmoxarifado Like '%.%.%.%.%'
GO

-- Main hierarchyId processing
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT
    IDENTITY( INT, 1, 1 ) AS idMaster,
    idAlmoxarifado,
    CAST( '/' + REPLACE( REPLACE( REPLACE( idAlmoxarifado, '.0', '.' ), '.0', '.' ), '.', '/' ) + '/' AS hierarchyid ) hId,
    CAST( '/' + REPLACE( REPLACE( REPLACE( idAlmoxarifado, '.0', '.' ), '.0', '.' ), '.', '/' ) + '/' AS hierarchyid ).ToString() hString,
    CAST( NULL AS INT ) AS idAlmoxarifado2,
    CAST( NULL AS INT ) AS idAlmoxPai

INTO #tmp
FROM [dbo].[Almoxarifado]
GO

-- Index temp table to help with recursive CTE
ALTER TABLE #tmp ADD PRIMARY KEY ( idMaster )
ALTER TABLE #tmp ALTER COLUMN hId hierarchyId NOT NULL
CREATE UNIQUE INDEX _idx ON #tmp ( hId ) INCLUDE ( idAlmoxarifado, hString )
GO

-- Walk the hierarchy
;WITH cte AS (
SELECT 1 AS xlevel, idMaster, idAlmoxarifado, hId, hString, hId.GetLevel() getLevel, 0 AS parentId
FROM #tmp
WHERE hId.GetLevel() = 1

UNION ALL

SELECT xlevel + 1, t.idMaster, t.idAlmoxarifado, t.hId, t.hString, t.hId.GetLevel() getLevel, c.idMaster AS parentId
FROM cte c
    INNER JOIN #tmp t ON c.hId  = t.hId.GetAncestor(1)
)
UPDATE t
SET
    -- Parse out the individual node, by taking its parent and stuffing it in front
    t.idAlmoxarifado2 = CAST( REPLACE( STUFF( c.hString, 1, LEN(c.hId.GetAncestor(1).ToString()), '' ), '/', '' ) AS INT ),
    t.idAlmoxPai = parentId
FROM cte c
    INNER JOIN #tmp t ON c.idMaster = T.idMaster

-- Check results
SELECT *
FROM #tmp

This whole script (including test data creation) runs in only a few minutes on my laptop.


Testing multiple queries from input file I have set of tables and need to execute select query based on different users. Table names are stored in file and I am looking forward to write a script to execute a script to read table name from the file and execute select query as specific user. Need to validate if the query was successful or failure.

Any suggestion on the tools for Oracle database / scripting(python or java) to be used for automation.

Thanks in advance.

It looks like you need to write dynamic SQL. Dynamic SQL is SQL the creates SQL that can get run. Here is a simple example of dynamic SQL.

SELECT 'ALTER ' ||
       CASE object_type
       WHEN 'PACKAGE BODY' THEN 'PACKAGE '
       WHEN 'TYPE BODY'    THEN 'TYPE '
       ELSE object_type ||' '
       END || owner ||'.' || object_name ||' '||
       CASE object_type
       WHEN 'PACKAGE BODY' THEN 'COMPILE BODY;'
       WHEN 'TYPE BODY'    THEN 'COMPILE BODY;'
       ELSE 'COMPILE;'
       END text
  FROM dba_objects
 WHERE status = 'INVALID'
 ORDER BY 1;

You should also have formatting in the SQL to keep the output of the SQL as clean as possible.

SET TERM      OFF
COLUMN text FORMAT A150
SET ECHO      OFF
SET TRIMSPOOL ON
SET TAB       OFF
SET FEEDBACK  OFF
SET PAGESIZE  0
SET LINESIZE  150
SET TIMING    OFF
SET TERM      ON

Then you just need to write the SQL that generates SQL, if you need to connect as a different user you can include a line like:

SELECT 'CONNECT user1/cdjkfghljsdg@sidname' text from dual;

But you need to figure out how to write the SQL, since you have not given many details.
You will spool to a file, then turn on feedback, set pagesize 50 or so, probably set timing on etc, then run the output of the spool file. You may want to spool to a log file during the run phase. There are alot of examples, you just need to query for an example that matches what you want to do.


MongoDB instance for production I am seeking advice before releasing my mongodb server into production from deployment mode.

Just to point out the server is working fine on development mode.

Scenario: Let's say I have two server Ubuntu 16.04 instances, where instance A is the (application instance), and instance B is the (mongo db 3.2 database).

For this instance B I don't think that I need scale/sharding/replicas, the idea is to let mongo occupy this instance all by itself to store secured users' credentials and some api data. (if you object, I am listing :) )

How they communicate: Instance B only talks to Instance A through port 27077 since it's not recommended to use the default port 27017. Moreover in the config file of mongo I bind the A's static ip.

Just to point out only three ports are allows in that instance B, ssh and 2 mongo db port 27077 and 28017

  First Question: In order for this communication to be secure, would it
  be a better idea to use HTTPS port 443 instead of that basic TCP port 27077
  , then disable the HTTP interface in the mongo.conf file? Is that enough to make the communication secured?

Authentication & Authorization: I create two user roles on the database, where one is the total admin and the other with basic rights which might prevent me from doing something stupid one day. For example deleting the wrong collection so and so.

  Second Question: What would be the best way to always make sure the
  mongo server is always responsive or running? (for example if the
  server was rebooted, I need to make sure that the mongo is still
  running) or (to avoid mongodb.lock file) because I had that issue before.

  Third Question: Since I am not doing replica set, what would be the
  best way to create a backup?

If you have any order advice, I am listening and thank you for your time.

First Question: In order for this communication to be secure, would it be a better idea to use HTTPS port 443 instead of that basic TCP port 27077 , then disable the HTTP interface in the mongo.conf file? Is that enough to make the communication secured?

--yes enough.As port and ip security is there apart from that you can use iptables for more security. As currently you are just preventing mongo attack only.

What would be the best way to always make sure the mongo server is always responsive or running? (for example if the server was rebooted, I need to make sure that the mongo is still running) or (to avoid mongodb.lock file) because I had that issue before.

-- if you are running it as service then lock file issue will not be there.in case you are using directly from bin then put it in rc.d simple.
advised you to create a script to check your server status and configure alerts accordingly.

BACKUP: As you are not using replica then simply mongodump will be advisable but you can also start your single machine as replica means without any secondary you will have oplog for incremental backup.
You can start instance with replset option or change in config file.
then below command.

>rs.initiate()

it will give you something like :

{
    "info2" : "no configuration specified. Using a default configuration for the set",
    "me" : "machine:27017",
    "ok" : 1
}

check one oplog.rs will be there in local database.


postgresql: PANIC: cannot operate with inconsistent data I'm trying to setup a replica using repmgr:

repmgr -D /var/lib/postgresql/9.3/main -p 5432 -U repmgr -R postgres \
   --verbose standby clone psql.master.example.com
repmgr --verbose standby register

I've manage to sync the DB's but the standby replica won't start:

postgres@psql01a:~$ /usr/lib/postgresql/9.3/bin/postgres --single -D /var/lib/postgresql/9.3/main -P -d 1
2016-04-18 14:02:05 UTC [30048]: [1-1] user=,db=,client= LOG:  database system was shut down in recovery at 2016-04-18 14:00:51 UTC
2016-04-18 14:02:05 UTC [30048]: [2-1] user=,db=,client= LOG:  entering standby mode
2016-04-18 14:02:05 UTC [30048]: [3-1] user=,db=,client= DEBUG:  checkpoint record is at 27B5/BA68B550
2016-04-18 14:02:05 UTC [30048]: [4-1] user=,db=,client= DEBUG:  redo record is at 27B5/B3626B20; shutdown FALSE
2016-04-18 14:02:05 UTC [30048]: [5-1] user=,db=,client= DEBUG:  next transaction ID: 0/2281005353; next OID: 230242292
2016-04-18 14:02:05 UTC [30048]: [6-1] user=,db=,client= DEBUG:  next MultiXactId: 879585; next MultiXactOffset: 1823275
2016-04-18 14:02:05 UTC [30048]: [7-1] user=,db=,client= DEBUG:  oldest unfrozen transaction ID: 2094018845, in database 134461654
2016-04-18 14:02:05 UTC [30048]: [8-1] user=,db=,client= DEBUG:  oldest MultiXactId: 1, in database 16546
2016-04-18 14:02:05 UTC [30048]: [9-1] user=,db=,client= DEBUG:  transaction ID wrap limit is 4241502492, limited by database with OID 134461654
2016-04-18 14:02:05 UTC [30048]: [10-1] user=,db=,client= DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 16546
2016-04-18 14:02:05 UTC [30048]: [11-1] user=,db=,client= DEBUG:  resetting unlogged relations: cleanup 1 init 0
2016-04-18 14:02:05 UTC [30048]: [12-1] user=,db=,client= DEBUG:  initializing for hot standby
2016-04-18 14:02:05 UTC [30048]: [13-1] user=,db=,client= LOG:  redo starts at 27B5/B3626B20
2016-04-18 14:02:05 UTC [30048]: [14-1] user=,db=,client= DEBUG:  recovery snapshots are now enabled
2016-04-18 14:02:05 UTC [30048]: [15-1] user=,db=,client= CONTEXT:  xlog redo running xacts: nextXid 2281009749 latestCompletedXid 2281009746 oldestRunningXid 2281009747; 2 xacts: 2281009748 2281009747
2016-04-18 14:02:05 UTC [30048]: [16-1] user=,db=,client= PANIC:  btree_xlog_delete_get_latestRemovedXid: cannot operate with inconsistent data
2016-04-18 14:02:05 UTC [30048]: [17-1] user=,db=,client= CONTEXT:  xlog redo delete: index 1663/16546/215742765; iblk 363218, heap 1663/16546/215740352;
Aborted

Any idea how to start the replica?

The main problem was different configuration in postgres.conf, after modifying shared_buffers and several others (configuration based on pgtune for given hardware):

maintenance_work_mem = 1GB
effective_cache_size = 22GB
work_mem = 15MB
wal_buffers = 8MB
shared_buffers = 7680MB
max_connections = 1024

After that I've hit another error:

2016-04-18 15:36:39 UTC [5150-1] FATAL:  could not create semaphores: No space left on device
2016-04-18 15:36:39 UTC [5150-2] DETAIL:  Failed system call was semget(5432064, 17, 03600).
2016-04-18 15:36:39 UTC [5150-3] HINT:  This error does *not* mean that you have run out of disk space.
  It occurs when either the system limit for the maximum number of semaphore sets
 (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be
 exceeded. You need to raise the respective kernel parameter.  Alternatively,
 reduce PostgreSQL's consumption of semaphores by reducing its max_connections
 parameter. The PostgreSQL documentation contains more information about
configuring your system for PostgreSQL.

Which could be fixed by increasing kernel limits:

echo 250 32000 256 256 > /proc/sys/kernel/sem

(this shouldn't be needed for PostgreSQL > 9.3)


Temp Table Filtered Index Creation is Blocked When Stored Procedure is Run Multiple Times Concurrently Are there any known blocking issues in SQL Server 2014 that are specific to creating one or more filtered indexes on a temp table after the initial table creation? (i.e. not an inline index creation)

I am not permitted to post the actual code; so, I have changed table/column names to protect the innocent.  The representative but very rough logic below is included at the beginning of a stored procedure.

The blocking occurs when the stored procedure is run multiple times concurrently, I believe.  According to my DMV query, the stored procedure is being blocked on the 1st nonclustered index creation statement.

CREATE TABLE #temp_table_name_goes_here (
    [the_first_col] BIGINT NULL
    ,[the_second_col] INT NULL
    ,[another_col] VARCHAR(20) NULL
    );

CREATE INDEX tmp_indx_temp_table_name_goes_here_1 ON #temp_table_name_goes_here (the_first_col)
WHERE the_first_col IS NOT NULL;

CREATE INDEX tmp_indx_temp_table_name_goes_here_2 ON #temp_table_name_goes_here (the_second_col)
WHERE the_second_col IS NOT NULL;

Repro!

I was able to reproduce your issue using SQL Query Stress, both using your example, and changing things to create the indexes inline, as Paul suggested:

CREATE TABLE #temp_table_name_goes_here
     (
         the_first_col BIGINT NULL,
         the_second_col INT NULL,
         another_col VARCHAR(20) NULL,
         INDEX tmp_indx_temp_table_name_goes_here ( the_first_col )
               WHERE the_first_col IS NOT NULL,
         INDEX tmp_indx_outstanding_inventory ( the_second_col )
               WHERE the_second_col IS NOT NULL
     );

Of course, SQS was running 200 concurrent sessions for 20 iterations., so it took some work to get there.

I'm on SQL Server 2017, so the situation was not changed by enabling Trace Flag 3427.

If tempdb is not configured optimally on your system, I'd start there:

One data file per core up to 8 cores
Enable Trace Flags 1117 and 1118

If you still have issues, I'd open a support case with Microsoft. Scalability issues like this should be front and center.

Follow Up

After stumbling on this question, I decided to re-try the test using table variables. Under the same circumstances as above, the same contention was observed.

DECLARE @temp_table_name_goes_here TABLE
     (
         the_first_col BIGINT NULL,
         the_second_col INT NULL,
         another_col VARCHAR(20) NULL,
         INDEX tmp_indx_temp_table_name_goes_here ( the_first_col )
               WHERE the_first_col IS NOT NULL,
         INDEX tmp_indx_outstanding_inventory ( the_second_col )
               WHERE the_second_col IS NOT NULL
     );

I also re-ran the temp table test with an explicit DROP IF EXISTS (or the old style check for a NOT NULL object_id) at the beginning, per Jonathan Fite's comment:

  I've hit this error before. My solution was to add explicit drop of
  the indexes and the temp table (DROP IF EXISTS) before creating it. It
  only happens when the same session runs the stored procedure multiple
  times.

This did help. Contention was either non-existent or minimized.

Adding an explicit DROP TABLE, or DROP IF EXISTS at the end had an odd effect: curiously, the contention switched to dropping the temp tables:


why the processing is sometimes terminated , sometimes not DECLARE @Error int;
INSERT INTO Person.BusinessEntityContact
 (BusinessEntityID
 ,PersonID
 ,ContactTypeID)
 VALUES
 (0,0,1);
SELECT @Error = @@ERROR;
PRINT ‘The Value of @Error is ‘ + CONVERT(varchar, @Error);

I got :

  Msg 547, Level 16, State 0, Line 4 The INSERT statement conflicted
  with the FOREIGN KEY constraint
  “FK_BusinessEntityContact_Person_PersonID”....The statement has been
  terminated. The Value of @Error is 547

that means the process was still going on after the error was thrown.

But if I try create a table(run the below script twice to trigger the error) as:

CREATE TABLE OurIFTest(
 Col1 int PRIMARY KEY
 );
IF @@ERROR != 0
 PRINT 'Problems!';
ELSE
 PRINT 'Everything went OK!';

then I got

  Msg 2714, Level 16, State 6, Line 2 There is already an object named
  'OurIFTest' in the database.

which means the process was terminated when the error was thrown.

So why the process terminated for 'Create' but not for 'Insert'?

You can force the same behaviour accross errors with SET XACT_ABORT

SET XACT_ABORT ON;

From the documentation:

  Specifies whether SQL Server automatically rolls back the current
  transaction when a Transact-SQL statement raises a run-time error.

Source

By default XACT_ABORT = OFF this means that different errors create different outcomes.

To force the behaviour on error to be the same, add SET XACT_ABORT ON; at the beginning of your script.

Testing with your example

SET XACT_ABORT ON;

DECLARE @Error int;
INSERT INTO Person.BusinessEntityContact
 (BusinessEntityID
 ,PersonID
 ,ContactTypeID)
 VALUES
 (0,0,1);
SELECT @Error = @@ERROR;
PRINT 'The Value of @Error is ‘ + CONVERT(varchar, @Error)'

With the only error shown:

  Msg 547, Level 16, State 0, Line 17 The INSERT statement conflicted
  with the FOREIGN KEY constraint "FK_Person". The conflict occurred in
  database "my_test", table "Person.Person", column 'PersonID'.

Also note this important statement regarding error handling and SET XACT_ABORT

  The THROW statement honors SET XACT_ABORT. RAISERROR does not.
  New applications should use THROW instead of RAISERROR.

Source


MySQL/Pivot table for two tables I was trying to get multiple rows into a single row with dynamic columns created as per the data. I have two tables combined with foreign key.

Table 1:

| id | name | Invoice value | invoice_date |
|----|------|---------------|--------------|
| 1  | A    | 5000          | 30-01-2016   |
| 2  | B    | 8000          | 02-05-2016   |
| 3  | C    | 10000         | 03-05-2016   |

Table 2:

| id | invoice_id | duedate    | amount | percentage |
|----|------------|------------|--------|------------|
| 1  | 1          | 15-01-2016 | 2500   | 50%        |
| 2  | 1          | 30-01-2016 | 2500   | 50%        |
| 3  | 2          | 15-02-2016 | 8000   | 100%       |
| 4  | 3          | 15-05-2016 | 5000   | 50%        |
| 5  | 3          | 19-05-2016 | 2500   | 25%        |
| 6  | 3          | 25-05-2016 | 2500   | 25%        |

Desired output:

| name | invoice_value | invoice_date | due date1  | due amount1 | due date2  | due amount2 | due date3  | due amount3 |
|------|---------------|--------------|------------|-------------|------------|-------------|------------|-------------|
| A    | 5000          | 30-01-2016   | 15-01-2016 | 2500        | 30-01-2016 | 04-11-1906  | null       | null        |
| B    | 8000          | 02-05-2016   | 15-02-2016 | 8000        | null       | null        | null       | null        |
| C    | 10000         | 03-05-2016   | 15-05-2016 | 5000        | 19-05-2016 | 2500        | 19-05-2016 | 2500        |

When I tried have used group-concat for the multiple columns it's giving results with comma separated. But I want as desired output. Please somebody help to solve this issue how to write a query for this.

I was using the following query but it's giving results as comma separated result:

SELECT  T1.name,T1.invoice_value,T1.invoice_date,T1.duedate,T1.dueamount
    FROM
      ( SELECT  table1.name , table1.invoice_value, table1.invoice_date,
               group_concat(table2.duedate1) as duedate,
               group_concat(table2.dueamount1) as dueamount
            FROM  table1
            LEFT JOIN  table2  ON table1.id=table2.invoice_id
      )T1
    Group By  T1.id

(Technically, this is 'not an answer', but claim it needs to be said.)

There will be an arbitrary number of columns, correct?  Then (1) the output is impractical, and (2) it would be virtually impossible to generate the output in SQL.

So, (1) rethink the requirement, then (2) consider whether to do it in PHP instead.


re size datafile oracle thanks for reading.

I am currently working with an Oracle 11G.I have a problem with a tablespace, I have very little space left, and I need to increase its capacity.I know what to do with this command:

alter tablespace datafile '+DG_DATA/sificore/datafile/auditoria.282.999190187' resize 160000m;

But when I'm trying to find the dbf, it appears to me like this.

My question is this:

I can't find the file with the dbf extension, can I run the command with what appears to me there (+ DG_DATA / sificore / datafile / audit.282.999190187) and would it have no problem?

the database, is currently in active redolog mode, would it have conflicts?

Should I do the issue of putting it in online, offline and recover datafile mode?

Thanks for reading, I will greatly appreciate your help.

The database is using Oracle Automated Storage Management, so the data files are not located in a normal file system where you can "cd" to the directory and see them. You would need to have privileges within the ASM management database and use a tool like "asmcmd" to see more information. As long as the database recognizes the file as online, you are fine. You do not need to take anything offline or jump through any other hoops. See this link for more info: http://www.oracle-wiki.net/startdocsusefuloraclecommands


sqlcmd -b doesn't exit on error I'm confused how should I use -b parameter of sqlcmd. I'm trying to write simple automation script for my DB and wanted to stop execution in case of any errors. I'm using SQLServer 2008r2.

For testing I'm calling simple script

sqlcmd -b -i test.sql

content of test.sql:

PRINT 'test1';
RAISERROR ('Error raised', 15, 15);
PRINT 'test2';

I expected that after first print I would see error msg and script would stop. But every time second print is executed and I see following output:

  test1 Msg 50000, Level 18, State 15, Server TOMEK, Line 3
  Error raised test2

What I'm doing wrong?

The -b parameter applies to the batch, not statements within the script.  If you separate each statement with a GO batch terminator, you will get the behavior you expect.

Remember that SQLCMD sends the entire batch of statements at once to SQL Server for execution.  You'll need T-SQL control flow statements (including TRY/CATCH) to handle errors on the server side, if needed.


Can't stop "SQL Server Polybase Data Movement" When I start Windows the "SQL Server Polybase Data Movement" service is in the "Change pending..." state and I can't close it. When I try to stop it mmc crashes.

In Windows services, the status = starting, but it never changes either. I cannot stop it here either.

Does anyone know how to stop it?

PolyBase needs TCP enabled to work. By default TCP is disabled in SQL Network Configuration.


TSQL (2014) - Importing XML with accented and punctuation Characters Using this solution from Forrest:

DECLARE @XML xml =
'<?xml version="1.0" encoding="UTF-8"?>
   <Orders>
    <Order>
        <OrderID>334</OrderID>
        <AmountPaid currencyID="EUR">17.10</AmountPaid>
        <UserID>marc58</UserID>
        <ShippingAddress>
            <Name>Marc Juppé</Name>
            <Address>Rue  garçonneé III° arrondissement</Address>
            <City>Paris</City>
            <StateOrProvince></StateOrProvince>
            <Country>FR</Country>
            <Phone>333333333</Phone>
            <PostalCode>22222</PostalCode>
         </ShippingAddress>
         <ShippingCosts>4.50</ShippingCosts>
         <Items>
            <Item>
               <Details>
                    <ItemID>3664</ItemID>
                    <Store>47</Store>
                    <Title>MCPU DDA010</Title>
                    <SKU>mmx</SKU>
                </Details>
                <Quantity>1</Quantity>
                <Price currencyID="EUR">6.2</Price>
            </Item>
            <Item>
               <Details>
                    <ItemID>3665</ItemID>
                    <Store>45</Store>
                    <Title>MCPU DFZ42</Title>
                    <SKU>mmy</SKU>
                </Details>
                <Quantity>2</Quantity>
                <Price currencyID="EUR">3.2</Price>
            </Item>
        </Items>
    </Order>
</Orders>'

SELECT
    x.value('./ItemID[1]','int') AS ItemID,
    x.value('./Store[1]','int') AS Store,
    x.value('./Title[1]','nvarchar(100)') AS Title,
    x.value('./SKU[1]','nvarchar(100)') AS SKU,
    x.value('../Quantity[1]','int') AS Qty,
    x.value('../Price[1]','decimal(11,2)') AS Price,
    x.query('//OrderID[1]').value('.','int') AS OrderID,
    x.query('//AmountPaid[1]').value('.','decimal(11,2)') AS AmountPaid,
    x.query('//UserID[1]').value('.','nvarchar(100)') AS UserID,
    x.query('//ShippingCosts[1]').value('.','decimal(11,2)') AS ShippingCosts
FROM @XML.nodes('//Item/Details') i(x)

I receive an error like

  XML parsing: line 162, character 34, illegal xml character

when need to parse fields containing accented characters (àèòìùé) or °

I tried converting é int &egrave; but get this error:

  XML parsing: line 162, character 41, well formed check: undeclared entity

while if I convert to &#232; it works.

The problem is I cannot HTMLEncode the XML File since I expect it will convert also all the "<" and ">" of the XML Structure, and probably also something else.

Can suggest the best solution to HTMLEncode the XML before parsing?

The XML you have is invalid in UTF-8 encoding. The accented characters needs to be encoded. For instance ° should be encoded as Â°.

Here is a shorter version that also fails.

declare @X xml = '<?xml version="1.0" encoding="UTF-8"?>°';
select @X.value('text()[1]', 'nchar(1)');

  Msg 9420, Level 16, State 1, Line 1 XML parsing: line 1, character 39,
  illegal xml character

Should be like this instead if you use UTF-8.

declare @X xml = '<?xml version="1.0" encoding="UTF-8"?>Â°';
select @X.value('text()[1]', 'nchar(1)');

You need to trace the source of the data backwards to the producer to see where you end up with invalid XML.


Forgot mysql owner/root username and password Recently, I changed my mysql root user to someother username (say kevin). But, now both the accounts don't login with the old password. It says access denied.

I'm also not sure if the username is that (kevin). How can I retrieve both the root username and password?

I also tried resetting my mysql root password but I cannot, it says Access denied. Even if I use the root username, it logs in but I cannot see any database like mysql (Clearly, I don't have any privilege). Just the two basic databases are accessible. I am using Ubuntu 13.10, if that helps.

PS - I cannot reinstall mysql as I have important data stored in the databases.

You have a variety of options to reset the password http://code.openark.org/blog/mysql/recovering-a-mysql-root-password-the-fourth-solution

UPD:
Following the Max's advice let me elaborate. The easiest way to reset any password is to restart MySQL with skip-grant-tables in my.cnf. Then MySQL won't ask the password. To set new password execute this:

 mysql -e "UPDATE mysql.user SET password = PASSWORD('newpassword') WHERE user = 'root' and host = '<host>'"

where <host> is host where user connects form.

Then restart MySQL again, but without skip-grant-tables option.


Trigger Access table on Insert I am using Access 2016 and building an in-house HR app for my team.

I have a Position  Table, an Employee Table, a Position_Employee Table and a Staffing Table

Position Table
-------------
position_id autonumber
position_number
position_title
group_id FK
level_id FK

Employee Table
-------------
employee_id autonumber
last_name
first_name
dob

Position_Employee Table
-------------
position_employee_id autonumber
start_date
end_date
is_indeterminate
is_current
position_id FK
employee_id FK

Staffing Table
-------------
staffing_id autonumber
reference_number
hr_confirmation
start_date
end_date
staffing_status_id FK
staffing_type_id FK
position_id FK
employee_id FK
hiring_manager_id FK

My logic is based on the position as more than one employee can occupy the same position.

The logic is the following:
 - An Employee can have a indeterminate position
 - An Employee can also have a determinate position while keeping their indeterminate position
 - An Employee can only have a determinate position
 - A Position can have an indeterminate Employee
 - A Position can also have a determinate Employee while having an Indeterminate employee
- A Position can also have a determinate Employee and no Indeterminate employee (i.e vacant)
 - The Staffing table is where the different staffing processes goes so an employee can be hired and put in a position as determinate, indeterminate and so on.

I was told not to use flag as it could become a nightmare to maintain later on so technically, in my table Position_Employee, I shouldn't have is_indeterminate, is_current.

What I want to do in pseudo-code is the following:

Trigger on Staffing table
If Insert in Staffing table
    If Staffing.staffing_status_id = 1  * staffing_stadus_id = 1 = Completed *
        Insert New position_employee_id in Position_Employee table
        position_employee.position_id = staffing.position_id
        position_employee.employee_id = staffing.employee_id
        position_employee.start_date = staffing.start_date
        position_employee.end_date = staffing.end_date
    If staffing_type = 1 *staffing_type = 1 = Indeterminate*
        position_employee.is_indeterminate = 'yes'
    If staffing_type <> 1
        position_employee.is_current = 'yes'

I want to be able later on to differentiate, the indeterminate position of an employee but also all the previous position that employee had occupied and be able to pull the employee indeterminate and current position which can be the same but can also be different.

Does this logic makes sense? if yes, how can I do this best?

This explanation does not provide a complete answer to the question, but it does address critical considerations in coming up with a good solution.  So it is an answer, just not a complete one.

Since I was the one who discouraged "flags", I'll further qualify my previous answer. The bad flags I mentioned where used to keep track of related database rows that could otherwise be handled by proper, normalized table and relationship design. Such bad flags do not tell you anything about the state of the data objects that you want to store, rather where only for database organizational purposes. Boolean (yes/no) flags may be okay and even necessary for recording an existing, valid state of the object(s) you are storing. That's the first test for determining if the flags are okay.

The next consideration, for any type of flag, is whether or not the chosen database schema allows contradictory states to be stored. The answer to how you handle this situation can be complicated, but

you might be able to change/augment/refactor the database schema to avoid or eliminate contradictory states, or
be prepared to write all queries and updates to consider and handle such possible contradictions. Sometimes this is necessary if the real-world scenario itself allows such contradictions. For instance, perhaps you assign an employee to two positions, thinking that perhaps they can handle 3 positions, so you leave is_indeterminate checked, but then after a week on the job they are overwhelmed and you realize that they could not handle more work, so their is_indeterminate value is unchecked. In that case, there was nothing of an automated fashion that could've fixed that scenario.

On the other hand, what if that employee were now given a 3rd position, but the is_indeterminate checkbox was not properly updated. Now the system has inaccurate data. Is that tolerable? Will the workplace have someone that runs reports to periodically audit and fix bad data? Will your update routines handle possible bad data, so that, for example, the employee isn't underpaid because it says they need more work? Etc. etc.

Per #1 suggestion above, a possible change would be to replace a basic Boolean flag column with a position counter column. A query could simply indicate if the position has the correct number of employees assigned and flag shortages or over-staffing... but this time the "flag" is dynamically determined in the report, not stored in the database where it can become outdated.

Regarding the pseudo-code...  We're back to using a bad flag like staffing_stadus_id = 1 == complete.  I'll admit that sometimes such a flag can be useful for efficiency or simplicity reasons... like a final stamp that means "it's all done and don't ever bother me with that position again".  Maybe that's okay.  BUT, on the other hand, simply by adding a record to the [Position_Employee] table should be sufficient to determine if that particular position is complete.  In other words, a query looks for all related records and if there are sufficient employees assigned to the position, then it is dynamically determined to be complete... no need to update a separate column with specialized codes that will certainly become outdated either by bugs or people not updating the status appropriately.  If the schema/app is designed properly, the only way to mark the position as complete is to actually assign employees properly to the correct table... No accidentally updating the status, but forget to actually add the employee that was put in the position.

But once again, it could be necessary to have a "status flag", for instance if you want to maintain a history of positions, but perhaps a position is not yet filled but also determined to be obsolete.  In that case, you would indeed want a flag that you could use to mark it as obsolete.

This might all seem subtle and even contradictory, but go back to the original 2 points and consider why you are adding each flag and consider if it can be implemented in a more natural, relational-database-esque manner.

Also, why are there both employee_id and position_id FK columns in both the [Staffing] table AND the [Position_Employee] tables?  Once again, this sets up possible contradictory columns that could be different/outdated between the two tables.  Once an employee is chosen for the position, then a record is simply added to the [Position_Employee] table. Why add the employee_id to the [Staffing] table to then just copy it into the [Position_Employee]?

If there is another legitimate business reason for having the employee_id in the [Staffing] table, then schema needs to consider that requirement separately.  For instance, perhaps you want to store a potential employee for manual approval, and they are not put into the position until final approval is obtained.  Then a better relational-database-esque schema would be another table, perhaps a [Staffing_potential] table with a staffing_id column and employee_id column.  Once they are approved, then move (atomic transaction of copy/insert then delete) from the [Staffing_potential] to the [Position_Employee] table.  Once again, the staffing status is implied in what tables the rows exist rather than messing with flag columns.

Update

It's always difficult to judge from the questions and the context the reason for each particular detail, despite the attempt to describe those details.  It is now apparent that your design and requirements are mature enough that not only are there probably many ways to accomplish what you need, but there is really no answer to "how I can do this best?" without a full iterative analysis.  That can't be done on these forums.  In fact, I started with Stack Exchange long ago enough that I previously would have rejected this question as "too broad".  But the recent push for being "nice and open" has lead me to provide more wordy and specific answers.  I won't take back the advise I posted, but I'll wrap up this answer with...

Especially for historical or archival reasons, it may indeed be necessary to store duplicate data or even have supplemental "flag" fields.  As long as there is a documented purpose and reason for this, database normalization rules are not necessarily "broken".  I ran into that often with financial data where it was critical to record unalterable total transaction amounts along with constituent rows, even though the total could have been obtained from the detail rows.  BUT the key is that either documentation or column names or some other feature distinguish between the purposes of the fields, not to be mistaken for non-normalized data.  It's up to the developer(s) to ensure that the purpose of such fields are not eventually used for reasons that break stable, normalized design.  (FYI, it often good to have separate tables where such historical rows are kept, which are separate from the "working data".)

The fact that you are conscious and aware of the purpose of individual features of your schema is critical.  I don't have any further advise about handling the indeterminate employment cases.


pg_replication_slots: how to determine how far behind a consumer is? Given the replication api exposed in postgres 9.6.6, for a given replication slot and a consumer, what system catalogs or view could inform how far behind the consumer was the latest state of the wal?

The view you are looking for is pg_stat_replication.  In 9.6 you can only get this info expressed in LSN, not clock time.  Starting in v10, you can get it in clock time.


Determining max database and table size supported and present size One of my MySQL databases has been running on a single PC from the last four years.

Here is my setup:

MySQL 5.5.14
Windows XP on NTFS partition
All of my tables are using InnoDB engine

Here are two things I want to determine:

the maximum database and table size supported by this version and engine on the platform I mentioned above
the current database size and table sizes of all the tables in a database.

Here are some nice queries I have used for years:

This reports the amount of data and indexes for each storage engine:

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

This reports the amount of data and indexes for each database:

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM (SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,index_length XSize,data_length+index_length TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA GROUP BY DB WITH ROLLUP) AA,
(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

This reports the amount of data and indexes for each database  by storage engine:

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size" FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,IF(ISNULL(engine)=1,10,0) engine_score,IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,CONCAT("Storage for ",B.table_schema),CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') DataSize,CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') TableSize FROM (SELECT table_schema,engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 3 pw) A) AA ORDER BY schemaname,schema_score,engine_score;

Please notice at the end of each query there is an inline query (SELECT 3 pw). The pw is simply an exponent used as a power of 1024. Here is how to use this:

(SELECT 0 pw) reports in bytes
(SELECT 1 pw) reports in kilobytes
(SELECT 2 pw) reports in megabytes
(SELECT 3 pw) reports in gigabytes
(SELECT 4 pw) reports in terabytes
(SELECT 5 pw) reports in petabytes (If you got this much data, write me or at least post the display in DBA.SE. I'm sure we'd all love to see that)

Give it a Try !!!

As for table limits, here are those limits for MyISAM and InnoDB.


How to get a column A which has the smaller column B grouping by column C? I'd like to get the ID of a dataset for each category (my grouping column) which has the smallest "order" column.
Here a set of data to explain my thoughts:

CREATE TABLE DATAS (
    ID INT(2) ,
    CATEGORY INT(2) ,
    ORD INT(1)
);

INSERT INTO DATAS (ID, CATEGORY, ORD)
VALUES (1, 1, 3), (2, 1, 2), (3, 1, 1), (4, 2, 1), (5, 2, 2);

Result expected:

ID   CATEGORY
-------------
3    1
4    2

Bonus question, how to retrieve just one ID even if there's some duplicates for the "order" column (ORD) ?

INSERT INTO DATAS (ID, CATEGORY, ORD)
VALUES (1, 1, 3), (2, 1, 2), (3, 1, 1), (4, 2, 1), (5, 2, 1), (6, 3, NULL), (7, 3, NULL);

Result expected (using a MIN for example or other suggestion):

ID   CATEGORY
-------------
3    1
4    2
6    3

Oracle or MySQL queries are welcomed, thanks a lot.

Oracle solution to both problems:

SELECT ID, Category FROM (
   SELECT FIRST_VALUE(ID) OVER
      (PARTITION BY Category ORDER BY Ord) IDOfSmallestOrdForCategory
      , Category, ID FROM DATAS
   )
   WHERE ID = IDOfSmallestOrdForCategory;

Generic solution to both problems:

SELECT MIN(a.ID), a.Category FROM DATAS a
JOIN (SELECT CATEGORY, COALESCE(MIN(ORD),0) MINORD FROM DATAS GROUP BY CATEGORY) b
ON COALESCE(a.ORD,0) = b.MINORD AND a.Category = b.Category
GROUP BY a.Category;

Oracle DDL/DML:

CREATE TABLE DATAS (
    ID       Integer,
    CATEGORY Integer,
    ORD      Integer
);

INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (2, 1, 2);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (3, 1, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (4, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (5, 2, 2);

INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (2, 1, 2);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (3, 1, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (4, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (5, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (6, 3, NULL);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (7, 3, NULL);


SQL*Loader-605: Non-data dependent ORACLE error occurred As I try to load data into Oracle, I've encountered SQL*Loader-605: Non-data dependent ORACLE error occurred error.

Data is (are?) in .txt file and its size is 95GB.

Here are some more details about the error:

  value used for ROWS parameter changed from 64 to 19

  ORA-01653: unable to extend table M.ECZ by 8192 in tablespace USERS

  SQL*Loader-605: Non-data dependent ORACLE error occurred -- load discontinued.

  Specify SKIP=8351926 when continuing the load.

  Table ECZ:
    8351925 Rows successfully loaded.
    0 Rows not loaded due to data errors.
    0 Rows not loaded because all WHEN clauses were failed.
    0 Rows not loaded because all fields were null.

  Space allocated for bind array:                 245100 bytes(19 rows)
  Read   buffer bytes: 1048576

  Total logical records skipped:          1

  Total logical records rejected:         0

  Total logical records discarded:        0

What do you advice? Thanks.

You've simply run out of space in the DB you are loading the data into. Try:

SQL> select file_name, bytes/1024/1024/1024 as gb from dba_data_files where tablespace_name='USERS';

Let's say the file was `/data/users01.dbf', 32G in size. Then you might do

SQL> alter tablespace users add datafile '/data/users02.dbf' size 1g autoextend on next 1g maxsize unlimited;

That will give you up to another 32G of space. Repeat as necessary, 03, 04 etc. I don't know how much Oracle data your 95G text file will end up as, but you can see what is available with:

SQL> select tablespace_name, sum(bytes)/1024/1024/1024 as Gb from dba_free_space where tablespace_name='USERS' group by tablespace_name;


Oh foxpro  my first programing language

Visual Foxpro 5+ can run most of the SQL92 commands with modifications to its syntax

standard sql commands will work for a join

Select * from table1 left join table2 on table2.id = table1.id
into cursor my_joined_tabled

this will create a read only cursor  aka in memory only table.  Cursor in foxpro are in memory tables

for a union

select cast(columns to same data type), and number of columns from table1
union
select cast(columns to same data type), and number of columns from table2
into cursor my_unioned_table

if you want to use Xbase commands  that's allot more code and those brains cells fell dormant 10 years ago


Oracle query against SQL Server database giving inconsistent results We have multiple Oracle 11.2.0.2 databases with og4odbc database links to a SQL Server 2008 database.  A particular table in the SQL Server database has two Varchar(36) columns.  The following statement returns 1292 rows:

select "id", "folder_id" from dbo.mediasite_presentation@ms;

Querying for a specific value in the first column returns a row:

select "id", "folder_id" from dbo.mediasite_presentation@ms
   where "id"='006d815d-6e9e-4004-9104-51213a1ecd52';
id                                   folder_id
------------------------------------ ------------------------------------
006d815d-6e9e-4004-9104-51213a1ecd52 54aa9b6e-1c55-4de5-a06f-033d8b19fff0

But querying for a specific value in the second column returns no rows:

select "id", "folder_id" from dbo.mediasite_presentation@ms
   where "folder_id"='54aa9b6e-1c55-4de5-a06f-033d8b19fff0';
no rows selected

Using the where clause that works and wrapping each field in a to_char returns the following interesting results:

select to_char("id"), to_char("folder_id") from dbo.mediasite_presentation@ms
   where "id"='006d815d-6e9e-4004-9104-51213a1ecd52';
TO_CHAR("ID")                        TO_CHAR("FOLDER_ID")
------------------------------------ ------------------------------------
006d815d-6e9e-4004-9104-51213a1ecd52 ┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐

Interestingly enough I can produce the same results just by adding a rownum clause:

select "id", "folder_id" from dbo.mediasite_presentation@ms
   where "id"='006d815d-6e9e-4004-9104-51213a1ecd52' and rownum<=1;

id                                   folder_id
------------------------------------ ------------------
006d815d-6e9e-4004-9104-51213a1ecd52 ┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐

Even stranger is the fact that creating a materialized view for query that returns no data actually returns data.

Create Materialized View TempMV as (
   select "id", "folder_id" from dbo.mediasite_presentation@ms
      where "folder_id"='54aa9b6e-1c55-4de5-a06f-033d8b19fff0' and rownum<=1);
Materialized view created.
select * from TempMV;

id                                   folder_id
------------------------------------ ------------------------------------
006d815d-6e9e-4004-9104-51213a1ecd52 54aa9b6e-1c55-4de5-a06f-033d8b19fff0

Please answer even if you can only explain a piece of this puzzle.

I'd suspect some for of characterset conversion issue.
When you do a TO_CHAR, you are getting back "┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐" (18 characters). That suggests that there is some confusion about whether the data contained in the column is single-byte or multi-byte.

"The uniqueidentifier data type in SQL Server is stored natively as a 16-byte binary value"
This should ideally come across to Oracle as a RAW datatype not a VARCHAR2 or CHAR.

If you do a

CREATE VIEW temp_view as select  "id", "folder_id" from dbo.mediasite_presentation@ms

what shows up when you do a DESC temp_view


ORA-01017: invalid username/password;logon denied There were other similar questions asked, but none that were able to tell me why I can't connect using a C# .NET web site I've stood up.  While I know this forum is more for DBAs connecting to databases using tools and not so much troubleshooting apps connecting, I thought I'd give it a shot posting the question here.

The web site works/connects from my workstation when ran from Visual Studio, but when I publish it to the server, it gives me the ORA-01017:  invalid username/password;logon denied error, even though I can connect via everything else with the same account/password. Situation:

I can connect using Oracle SQL Developer, SQL*Plus just fine with the account I'm using... let's call it "myaccount".
Using Net Manager, I can expand "Service Naming" and see the entries from my tnsnames.ora.
a. The same string that exists in tnsnames.ora/Service Naming area, exists as a hard-coded string in my application, with the exception of things like the host name, service name, port, user name, and password, which are passed-in as parameters from my web.config.
b. I can click a connection name and everything appears correctly, and clicking "Test Service" > "Change Login" > enter "myaccount" and password > click OK > click "Test" succeeds.
c. I have double-checked my parameters in my web.config - nothing has changed since the web site ran on my local workstation.
tnsping CONNECTION_NAME succeeds.
Environmental variables are the same on both computers, as are the Oracle client locations.
a. ORACLE_HOME = C:\oracle\product\12
b. PATH = C:\oracle\product\12\bin; .....
c. TNS_ADMIN = C:\oracle\product\12\network\admin
d. Registry entry added to HKLM\Software\Oracle\KEY_OraClient12Home for TNS_ADMIN = C:\oracle\product\12\network\admin
I've tried the ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE; command and reset the password, just in case that was the issue. I tried connecting with all-caps versions of myaccount and its password, as well as all lower-case versions of both (lower-case on both is how I had it originally when it worked on my workstation).
The account has a PASSWORD_VERSIONS of 10G and 11G - I don't know if it matters that I'm using a 12c client and 12C is not there. Main difference between the server and my workstation is I only have the 12c client on the server - I have 10g, 11g, and 12c on my workstation. I was going to try using the 11g ODP.NET assembly, Oracle.DataAccess.dll, but my code won't compile (I get "Could not load file or assembly 'Oracle.DataAccess' or one of its dependencies. An attempt was made to load a program with an incorrect format.".), even after changing the namespace from using Oracle.ManagedDataAccess.Client to using Oracle.DataAccess.Client.
Attempted passing in the password with quotes around it and without--both methods worked on my Visual Studio instance, just not on the server.
If I deliberately put in a totally bogus password, and run the site on the server enough times, it will lock the account, so I know it's connecting to the database.

Running out of things to try... any help/suggestions would be appreciated.

I have seen this only once so far, but there is an issue described in the My Oracle Suppport note:

FIPS use causes ORA-01017: Invalid Username/password with Managed ODP.NET (Doc ID 1937500.1)

I will not post the full content of this as it is not public, but here is the essence:

  This bug has been fixed in 12.1.0.2 Managed Provider. Please note that the DB version must be 12.1.0.2 or higher for this fix to work as expected.

  The workaround is to set in the registry
  System\CurrentControlSet\Control\Lsa\FIPSAlgorithmPolicy\Enabled to
  0.

Some public references to this matter:

https://stackoverflow.com/questions/26410951/oracle-manageddataaccess-and-ora-01017-invalid-username-password-logon-denied

https://stackoverflow.com/questions/31785650/ora-01005-error-connecting-with-odp-net

https://community.oracle.com/thread/2557592?start=0&tstart=0


What is the best way to backup logs and shrink logs in SQL Server 2008 r2? What is the best way to shrink and backup our db logs?

I have seen many DBAs that do it :

ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE
DBCC SHRINKFILE(<log_file_name_Log>)
ALTER DATABASE [mydatabase] SET RECOVERY FULL

But I wonder, is this the best way?

Am I affecting my database by losing data when changing the status of the recovery log?

The best way is not to shrink it at all.

You are not losing any data when you shrink the log or when switching recovery models. You are losing recovery points though.

But there's a reason the file was sized that way.

Backup your transaction logs frequently, such as every minute, to minimize its size. If the size is still bigger than what you'd think, LEAVE IT ALONE or figure out which are your large transactions. Those transactions might be able to be broken up, though it is likely an index rebuild that can't be broken up.

To answer your question, I just run my backup log job and then use DBCC SHRINKFILE. I may have to free the procedure cache in tempdb though: https://www.brentozar.com/archive/2016/02/when-shrinking-tempdb-just-wont-shrink/


MS SQL Server 2012 Data Quality Services (DQS) installation failure I tried to install MS SQL Server 2012 DQS using DQSInstaller.exe, but installation failed. In the console output started by the install tool (DQSInstaller.exe) I found this line:

Password validation failed.
The password does not meet Windows policy requirements because it is too short.

It was a bit odd, since my password was 15 characters long, but who knows. I used a different one and made it sure that it is 30 chars long. That didn't help and the installer insisted that the password is still too short.

Next thing was upgrading my SQL Server 2012 to SP1. That didn't help either.

The Installer is started with admin privileges. The database instance is NOT a default instance, but I see the script finds that out by itself.

What could be the problem?

The solution was starting one of the installer scripts manually and starting installer again afterwards.
I've searched for recreate_schema.bat script on my PC, opened cmd, changed directory to the script location and started the script the way installer starts it...

cd C:\Users{my user name}\AppData\Local\Temp\j1bnpjct.k3b
recreate_schema.bat RS-1OT-45OAR200\MSSQL2012 DQS SQL_Latin1_General_CP1_CI_AS

It finished without errors.
After that I started installer again and it worked.
I hope this will help someone.


Select from other table if exist hi I want to union 2 tables but I am not sure if the table is exits
this is my query:

select  a from table
union
select  case when exists(select a from table1) then (select a from  table1)
else (select a from table2) end;

got this

  ERROR: ERROR 4840:  Subquery used as an expression returned more than one row

I need to use limit 1 in a subquery but it's not the result that I wanted.

do you have other suggestions?
thanks

table, for example, I just created them in my local env for testing:

table 1

CREATE TABLE public.table1
(
    a int,
    b varchar(80),
    c int
);

CREATE TABLE public.table
    a int,
    b varchar(80),
    c int
);

In principle this won't work, as you can't check if table exists and then join it in a query.
The error is unrelated. You are selecting a value in a subselect and it returns multiple rows, as the table exists.
What you can do is:

Create temp table
Check if table1 exists using V_Catalog.table and if specific column existence is important, v_catalog.column
If it exists, select data from table1 into temp table
Use the temp table in the union instead of table1


Can't install Microsoft SQL Server 2016 Release Candidate 3 (RC3) on Windows Server 2008 R2 I am attempting to install Microsoft SQL Server 2016 Release Candidate 3 (RC3) on Windows Server 2008 R2.

I have removed all other versions of SQL Server, and removed VS 2015 Community and all it's SQL Server components.

When I run setup from the SQLServer2016RC3-x64-ENU.iso, (downloaded from Microsoft, and successfully installed on Windows 10, with VS 2015 Community installed), I get a dialogue with the following message:

  Microsoft SQL Server 2016 Release Candidate 3 (RC3) Setup:
  Setup limited to Reporting Services for SharePoint
  This server is running Windows Server 2008 R2. You can continue with setup to install or upgrade Reporting
  Services functionality in SharePoint:
  0 Reporting Services - SharePoint
  0 Reporting Services Add-in for SharePoint Products

The server does not have SharePoint installed.  Why is this happening, and how can I install Microsoft SQL Server 2016 Release Candidate 3 on this server?

I found the answer here:
Warning Setup Limited to Reporting Services for SharePoint by Alberto Morillo
Which, in short, says:

The only SQL Server 2016 features allowed to install on Windows Server 2008 R2 are Reporting Services (SharePoint) and Reporting Services Add-in for SharePoint products as mentioned on the "Hardware and Software Requirements for Installing SQL Server 2016" article created for SQL Server 2016 Preview (CTP versions).
You won't be able to install the Database Engine or any other feature because they are not supported on Windows Server 2008 R2.


Postgres: Filtering large jsonb arrays coming from a sub-query I have a table called external_data in Postgres designed with two columns :

+----+---------------------------------------------+
| id |                    data                     |
+----+---------------------------------------------+
|  1 | [{"firstName":"John","lastName":"Doe"},...] |
|  2 | [{"productName":"Mouse","price":123},...]   |
+----+---------------------------------------------+
id: integer
data: jsonb

The purpose of this table is to save arrays of over a million objects.

So, in order to get a specified jsonb data, I'm just doing a simple query:

SELECT data
FROM external_data
WHERE id = 1
LIMIT 1

but I don't want to get all the entries in the json array, since there are too many of them.
Basically this is what I do to filter the entries:

SELECT json_agg(elem) FROM
(SELECT jsonb_array_elements(data) AS elem FROM external_data WHERE id = 1) x
WHERE elem @> '{"property":"value"}'

This works well, taking 1 to 1.5s to perform, but I wonder if i can optimize this request using indexes or something else ?

I've already read about GIN indexes using jsonb_path_ops, but since the jsonb data is from a subquery, creating an index with this command

CREATE INDEX idx_data on external_data using GIN(data jsonb_path_ops)

my query doesn't use the index to perform faster :

"Aggregate  (cost=2.77..2.78 rows=1 width=32)"
"  ->  Subquery Scan on x  (cost=0.00..2.77 rows=1 width=32)"
"        Filter: (x.elem @> '{"Property": "value"}'::jsonb)"
"        ->  ProjectSet  (cost=0.00..1.52 rows=100 width=32)"
"              ->  Seq Scan on external_data  (cost=0.00..1.01 rows=1 width=32)"
"                    Filter: ((id)::integer = 1)"

And I created some functions using plv8 language and tried to use expression index, and it didn't work. Also, using theses functions would increase the query duration by adding 8 to 9s.

Is there an efficient way to optimize these query or it is the optimal way ?

You're doing as well as can be managed with your data structure. The GIN indexes serve the same purpose as any other index - finding rows given a condition. They don't help once the row is located. If you want this to be improved, you'd need to unroll the array elements into separate rows.


Why is this select statement not returning what I've provided? I have 3 tables in my SQL Server I am wondering why is my select statement not working or giving the result with my condition in the where clause. I learned back there to use this kind of method..

Subject table
SubjectID      CourseCode
   6            SubjectA
   7            SubjectB
   8            SubjectC

Schedule table
ScheduleID     Room
   15          Room1
   16          Room2
   17          Room3

AssignSchedule table
AssignScheduleID       ScheduleID       SubjectID
    14                     15               6
    15                     16               7

I want to get the room of the subject SubjectA. In order to get that I came up with this select statement query

Select Subject.CourseCode,
Schedule.Room from Subject,
Schedule, AssignSchedule
where
AssignSchedule.ScheduleID = (Select Schedule.Schedule.ID
from Schedule where Room = 'Room1')
AND AssignSchedule.SubjectID = (Select Subject.SubjectID from Subject where CourseCode = 'SubjectA');

What happens is like this

CourseCode      Room

  SubjectA      Room1
  SubjectB      Room1
  SubjectC      Room1
  SubjectA      Room2
  SubjectB      Room2
  SubjectC      Room2
  SubjectA      Room3
  SubjectB      Room3
  SubjectC      Room3

My expected output would be like this

CourseCode     Room
SubjectA       Room1

What is wrong? Help guys..

The WHERE clause returns the desired single row AssignSchedule but you have no correlation between that row and the outer Subject and Schedule tables so you get a Cartesian product of all Subject and Schedule combinations.  You could add the outer table correlations to your existing query to get the desired result:

SELECT  Subject.CourseCode
      , Schedule.Room
FROM    Subject
      , Schedule
      , AssignSchedule
WHERE   AssignSchedule.ScheduleID = ( SELECT    Schedule.ScheduleID
                                      FROM      Schedule
                                      WHERE     Room = 'Room1'
                                    )
        AND AssignSchedule.SubjectID = ( SELECT Subject.SubjectID
                                         FROM   Subject
                                         WHERE  CourseCode = 'SubjectA'
                                       )
        AND AssignSchedule.ScheduleID = Schedule.ScheduleID
        AND AssignSchedule.SubjectID = Subject.SubjectID;

A better approach would be use simple equality predicates instead in the ugly subqueries.  Below are examples using the old ANSI SQL-89 syntax you've been using as well as the ANSI SQL-92 equivalent.  I suggest the latter as it helps avoid missing join predicates.

SELECT  Subject.CourseCode
      , Schedule.Room
FROM    Subject
      , Schedule
      , AssignSchedule
WHERE
            AssignSchedule.ScheduleID = Schedule.ScheduleID
        AND AssignSchedule.SubjectID = Subject.SubjectID
        AND Schedule.Room = 'Room1'
        AND Subject.CourseCode = 'SubjectA';

SELECT  Subject.CourseCode
      , Schedule.Room
FROM    Subject
JOIN AssignSchedule ON AssignSchedule.SubjectID = Subject.SubjectID
JOIN Schedule ON AssignSchedule.ScheduleID = Schedule.ScheduleID
WHERE
        Schedule.Room = 'Room1'
    AND Subject.CourseCode = 'SubjectA';


Replication with Global Transaction Identifiers across different MySQL versions I have a master MySQL server running 5.0.67. Would it be possible to configure a GTIDs replication as a slave that runs 5.7.17 that connects to this master? I have tried the traditional binary log position replication and have no issues.

GTID was implemented as feature of MySQL 5.6, so short answer for 5.0.67 <-> 5.7.17 - NO

Long answer - even if 2 minor different versions support GTID, better and correct from points of view - have same versions for all servers in network, it help made Your life much more easy.


Why does a UNIQUE constraint allow only one NULL? Technically, NULL = NULL is False, by that logic no NULL is equal to any NULL and all NULLs are distinct. Shouldnt this imply that all NULLs are unique and a unique index should allow any number of NULLs?

Why does it work this way? Because way back when, someone made a design decision without knowing or caring about what the standard says (after all, we do have all kinds of weird behaviors with NULLs, and can coerce different behavior at will). That decision dictated that, in this case, NULL = NULL.

It wasn't a very smart decision. What they should have done is have the default behavior adhere to the ANSI standard, and if they really wanted this peculiar behavior, allow it through a DDL option like WITH CONSIDER_NULLS_EQUAL or WITH ALLOW_ONLY_ONE_NULL.

Of course, hindsight is 20/20.

And we have a workaround, now, anyway, even if it isn't the cleanest or most intuitive.

You can get the proper ANSI behavior in SQL Server 2008 and above by creating a unique, filtered index.

CREATE UNIQUE INDEX foo ON dbo.bar(key) WHERE key IS NOT NULL;

This allows more than one NULL value because those rows are completely left out of the duplicate checking. As an added bonus, this would end up being a smaller index than one that consisted of the entire table if multiple NULLs were allowed (especially when it isn't the only column in the index, it has INCLUDE columns, etc). However, you may want to be aware of some of the other limitations of filtered indexes:

How filtered indexes could be a more powerful feature


If you have a known number of columns, then you can use a static version similar to the other answer. But if you have an unknown number then you can use a prepared statement similar to this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when kode = ''',
      kode,
      ''' then jum else 0 end) AS ',
      kode
    )
  ) INTO @sql
FROM yourtable;

SET @sql = CONCAT('SELECT name, ', @sql, ', sum(jum) as `count`
                  FROM yourtable
                  GROUP BY name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with demo

Result is the same:

| NAME | KODE1 | KODE2 | COUNT |
--------------------------------
| aman |     2 |     1 |     3 |
| amir |     0 |     4 |     4 |
| jhon |     4 |     0 |     4 |


What is wrong with my trigger? For reasons unknown to me,  the control flow doesnt reach "ITS CORRECT BUT PRIZE IS MONETARY" block, that corresponds to variable bounty not being NULL. I'm pretty confident that SELECT Bounty FROM Questions WHERE QuestionID = NEW.QuestionID must return INT UNSIGNED.

So, is my syntax wrong? Am I using some kind of bad IF condition?

The trigger code: pastebin

CREATE TRIGGER `CheckCorrect` AFTER INSERT ON `UserAnswers`
FOR EACH ROW
BEGIN
DECLARE bounty INT UNSIGNED;
SET bounty:=(SELECT `Bounty` FROM `Questions` WHERE `QuestionID` =
 NEW.`QuestionID`);
 IF (SELECT `IsCorrect` FROM `Answers` WHERE `AnswerID` = NEW.`AnswerID`)<>0
THEN     // IF ITS CORRECT
  IF bounty IS NULL THEN    // FIELD `Bounty` being NULL means that a prize is set.
    IF EXISTS (SELECT * FROM `Prizes` WHERE `QuestionID` = NEW.`QuestionID` AND `ClaimerID` IS NULL) THEN    //ANY SPARE
      BEGIN
      UPDATE `Prizes` SET `ClaimerID` = NEW.`UserID` WHERE `QuestionID` = NEW.`QuestionID` AND `ClaimerID` IS NULL ORDER BY
             `PrizeID` LIMIT 1;
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NEWPRIZE", NOW(), FALSE);
      END;
    ELSE    //IF NOT LEAVE A MESSAGE!
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NOPRIZE", NOW(), FALSE );
    END IF;
  ELSE     // ITS CORRECT BUT PRIZE IS MONETARY
    IF (SELECT `Budget` FROM `Questions` WHERE `QuestionID`=NEW.`QuestionID`)>bounty THEN  // DO WE HAVE ENOUGH BUDGET TO PAY?
      BEGIN
      UPDATE `Questions` SET `Budget`=`Budget`-bounty WHERE `QuestionID` = NEW.`QuestionID`;
      UPDATE `Users` SET `Balance`=`Balance`+bounty WHERE `UserID`=NEW.`UserID`;
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, CAST(bounty AS CHAR(50)), NOW(), FALSE);
      END;
    ELSE   // NO MORE MONEY, I'M AFRAID
     INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NOMONEY",
 NOW(), FALSE );
   END IF;
  END IF;
END IF;
END;

Updated my code like so due to Jacob H's suggestion. No results.

  CREATE TRIGGER `CheckCorrect` AFTER INSERT ON `UserAnswers`    //CLAIM PRIZE AND NOTIFY!
FOR EACH ROW
BEGIN
DECLARE bounty INT UNSIGNED;
SET bounty:=(SELECT `Bounty` FROM `Questions` WHERE `QuestionID` = NEW.`QuestionID`);
IF (SELECT `IsCorrect` FROM `Answers` WHERE `AnswerID` = NEW.`AnswerID`)<>0 THEN     // IF ITS CORRECT
   BEGIN
  IF bounty IS NULL THEN    // IF ITS A PRIZE CLAIM IT
    BEGIN
    IF EXISTS (SELECT * FROM `Prizes` WHERE `QuestionID` = NEW.`QuestionID` AND `ClaimerID` IS NULL) THEN    //ANY SPARE PRIZES?
      BEGIN
      UPDATE `Prizes` SET `ClaimerID` = NEW.`UserID` WHERE `QuestionID` = NEW.`QuestionID` AND `ClaimerID` IS NULL ORDER BY `PrizeID` LIMIT 1;
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NEWPRIZE", NOW(), FALSE);
      END;
    ELSE    //IF NOT LEAVE A MESSAGE!
      BEGIN
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NOPRIZE", NOW(), FALSE );
      END;
    END IF;
    END;
  ELSE     // ITS CORRECT BUT PRIZE IS MONETARY
    BEGIN
    IF (SELECT `Budget` FROM `Questions` WHERE `QuestionID`=NEW.`QuestionID`)>bounty THEN
      BEGIN
      UPDATE `Questions` SET `Budget`=`Budget`-bounty WHERE `QuestionID` = NEW.`QuestionID`;
      UPDATE `Users` SET `Balance`=`Balance`+bounty WHERE `UserID`=NEW.`UserID`;
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`,CAST(bounty AS CHAR(50)), NOW(), FALSE);
      END;
    ELSE
      BEGIN
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NOMONEY", NOW(), FALSE );
      END;
    END IF;
    END;
  END IF;
  END
END IF;
END;

All I did was change

SET bounty:=(SELECT `Bounty` FROM `Questions` WHERE `QuestionID` =
NEW.`QuestionID`);

to

SELECT `Bounty` INTO bountyvar FROM `Questions` WHERE `QuestionID` = NEW.`QuestionID`;

and it started working... Why though xD

Some light can be shed on that from this question: apparently some versions of MySQL just don't like some variations of syntax. Oh well. I'm glad this is over. I'm tired.


Should I drop dbo from the database role db_owner? I did a Vulnerability Assessment for my database today. I found VA2108 and it suggests me to
ALTER ROLE [db_owner] DROP MEMBER [dbo]

But it's the default setting of any SQL instance.
Should I go for it? And is there any risk?

Thanks

Don't worry about it, as J.D. says.
In fact, you cannot drop dbo from db_owner. There's a hard-wired blocker in the engine prohibiting you to do so. Try and you get the error:
Msg 15405, Level 16, State 1, Line 1
Cannot use the special principal 'dbo'.


show statement_timeout as millis in postgres I want to create a view that will return the numeric value of the current statement_timeout in milliseconds. However, the result of the SHOW command is human-readable and may have different suffixes such as min and ms:
begin transaction;
set local statement_timeout = 120000;
show  statement_timeout;
--------------
'2min'

How to retrieve the raw, untranslated value?

You can query pg_settings directly:
select setting
from pg_settings
where name = 'statement_timeout'


Bulk Import appending the data to the data file We have a bulk import task running in SQL Server 2016 which imports more than 5 million rows. The task itself is configured at cloud using third party services. The strange thing is that the new data being imported is appended to the data file (.mdf) and already allocated empty space is not being used. Tables are being truncated and reloaded. All the tables have clustered indexes defined. We have even used pre-processing task of truncate all related tables -> reorganize all related tables -> DBCC updateusage but to no avail. We have even double the size of the data file but we still see some auto growth events. Although such events decreased by 80 percent. After the import we are left with 70 percent of data file being empty. Am I missing something or any specific setting/change in SQL Server 2016 which is why we are experiencing such behavior? Thanks.

Bulk inserts with small batch sizes can result in a significant increase in unused reserved space due to a full extent allocation for each batch for fast inserts per this Tiger Team post. I see from your comment that the batch size is only 200 rows and this explains the main reason for the large amount of unused space, although I would expect existing unallocated extents (not to be confused with unused space) to be reused before growing the file.

If you don't have control over the optimal batch size (e.g. third-party application), you can turn on trace flag 692 to disable fast inserts and reduce space needs but at the expense of performance.


trigger - Sql2008 - Msg 7391 - "no transaction is active/unable to begin trans" I have a trigger on my SqlServer, located in server7.

It inserts a data on another server ( server5 ), after insert, as you see the code below:

CREATETRIGGER [dbo].[trgSPTInsereDepartamento]
   ON  [dbo].[tblDepartamento]
   AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into [Server5].alfabase.dbo.tblDepartamento (CodDepto,DescDepto,IncluiOutroDepto)
Select ins.codDepartamento, ins.DescDepartamento, 0
From inserted ins where not exists(select 1 from [Server5].alfabase.dbo.tblDepartamento sptsta where sptsta.CodDepto = ins.CodDepartamento)
END

When i make an insert, the SQL shows me this:

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

Local DTC properties is all checked ( it allows remote users ).

I've restarded the server and the DTC service. No Luck for me.

I read a lot of posts but none of them helped me.

Thanks for the help.

  update1:

Checked firewall. disabled it but still doesn't work.

DTC is ok ( only server5 is on a cluster. )

I'm really out of ideas guys.

Thank you very much.

  Update 2:

From server7 to server5, I have the problem above. To test, I made a trigger on a database from server4 to server5. and it worked. So, the problem is on server7.

Server7 is not on the Cluster ( server3,4,5 are clustered ).

Thanks.

FIXED!

Every site has the same step-by-step.

  Start
  DCOMCNFG
  Expand "Component Services"
  Expand "computers" >
  Expand "My computer"
  Expand "DTC"
  right click on "Local DTC"
  Properties
  Security  check  "Network DTC Access" and " allow remote clientes".

It for sure will help a lot of people. The problem is that the server is in a cluster, so what a did is:

  Start
  DCOMCNFG
  Expand "Component Services"
  Expand "computers" >
  Expand "My computer"
  Expand "DTC"
  Expand "CLUSTERED DTCs" << This is the trick
  Right click on the server and make the same config as above for the "local DTC"

Restart service. Enjoy you boss Happy.


SQL Server : Index Seek with very high Scan Count I have a query that joins 2 tables:

Documents table, that has DocID as Unique Clustered Index
DocumentsRows table, that has DocID, RowID as Unique Clustered Index

When I join those 2 tables, I usually do:

SELECT <somefields>
FROM Documents
INNER JOIN DocumentsRows ON Documents.DocID = DocumentsRows.DocID
WHERE <something>

If I run the query with STATISTICS IO ON and SHOWPLAN, I see that the engine choose Index Seek on DocumentsRows but the Number of Executions is equal to the Scan Count for the same table.

  Table 'DocumentsRows'. Scan count 2277, logical reads 12591, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Documents'. Scan count 5, logical reads 17526, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I know that since I'm not joining on the entire index, the seek does not retrieve only 1 value (in fact it retrieves all the rows for a specific document), then why does SQL Server choose to seek on that index instead of just scan it?

Why should an Index Seek have a so high scan count? How can I solve this?

I believe this has already been answered and would be helpful for you if you can refer to below discussion:Also, what action where clause is performing needs to be looked into:

Why so many logical reads?

Since Logical Reads are from the data cache (memory) I would think the fact that there is a lot of reads will make little difference and it would seem that the second query is more efficient when reading a lot of data in small chunks while the first query reads the data in large chunks.

Overall, the query performance significantly can be improved using below several aspects as well:

Only the correct subset of rows from the * table to be retrieved, potentially reducing physical IO and locking contention
The CPU intensive hash functions to be eliminated
The memory footprint for both data and hash buckets to be reduced.

Index Seek will be used only if conditions from WHERE or ON clauses are the first in the list of columns of the Index. Also, this fields must be selective which means that the condition must filter just a little percent or rows from a table.

Also, you don't need to include column that is part of the clustered index into list of columns of a nonclustered index, because Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table.

Actually, this is very complex subject. To know more about using indexes in Sql Server you can read this article https://technet.microsoft.com/en-us/library/jj835095.aspx


Query to get logs between Start and End Night hour I am working on Procedure where we pass in the Night Start Hour and the Night End Hour, I need to get the Logs between these hours.

So:

If the StartHour = 18 and EndHour = 5, the Stored Procedure should return all logs between 6:00 PM and 5:00 AM.
If the StartHour = 1 and EndHour = 5, the Stored Procedure should return all logs between 1:00 AM and 5:00 AM.

I tried creating a SQL Fiddle but there seems to be a problem with creating the fiddle in either SQL Server 2014 or SQL Server 2008 Today.

I AM USING SQL SERVER 2014 - But Fiddle is in MySQL

(I have created one in MySQL to display the data) [Wrong Fiddle: http://sqlfiddle.com/#!9/6f0a6/1]

Correct: http://sqlfiddle.com/#!9/6467d8/3

This is what my Script looks like:

Select
    *
FROM
(

    Select
        iVehicleMonitoringId,
        dtUtcDateTime,
        HourPart = DATEPART(HOUR, dtUtcDateTime)
        From VehicleMonitoringLog vm

        Where vm.dtUTCDateTime > @utcStartDate AND vm.dtUTCDateTime < @utcEndDate
        AND  DATEPART(HOUR, vm.dtUtcDateTime) >  @iStartHour

UNION
    Select
        vm.iVehicleMonitoringId,
        dtUtcDateTime,
        HourPart = DATEPART(HOUR, dtUtcDateTime)
        From VehicleMonitoringLog vm

        Where vm.dtUTCDateTime > @utcStartDate AND vm.dtUTCDateTime < @utcEndDate
        AND  DATEPART(HOUR, vm.dtUtcDateTime) <  @iEndHour
) As G
Order by dtUtcDateTime

But this query will be wrong for the second condition. Because 18,19,20.... are all greater than 1, so I am confused on how to write this query.


SSIS Data Flow Error Escape I have an SSIS process that is built to push records to a web service that has some sporadic auth timeouts. My design is working well but we are running large batches of records 250,000 per batch over Millions of rows. What I'm looking to do is something similar to this post https://stackoverflow.com/questions/7076018/how-to-fail-ssis-component-on-invalid-rows but instead of failing the data flow task I want to to just complete successfully without processing more rows so it iterates on to my next loop iteration. Essentially once I get an error I know the rest for that batch will fail and I want to iterate my loop which I already have logic built to re-run failed records. Any thoughts on how to accomplish?

It is possible to process individual rows and redirect the records that failed. You could even validate records before you try to insert them.

How do you want it to work?
Unless they specifically need to be processed together as a batch I would be processing individual records and storing failed records for correction and resubmission.


What causes DBCC CHECKTABLE to return "session is in the kill state"? A scheduled job step ran DBCC CHECKTABLE on a table but returned the following error:

Cannot continue the execution because the session is in the kill state. [SQLSTATE HY000] (Error 596). The step failed.

What causes this error?

After poking around, discovered that the volume was very low on space so probably didn't meet the free space requirements for "dbcc checktable".


SQL Server SSL Connection - Performance I know there are plenty of posts here regarding SQL Server and SSL encrypted connections, however I didn't find any that touched on this particular subject. In this article, it mentions that performance is hindered with enabled SSL connections due to the extra processing required. In a shared database server, where database-A will have encrypted connection, but database-B will not, will database-B's performance be affected somehow due to the encrypted connections being processed on database-A?

The connection encryption happens at the SQL Server instance level, not at the level of individual databases.

You can have encryption turned on at the instance, in which case every connection to it will be encrypted, or you can set a client to require encryption. Keep in mind every instance such a client connects to will have to have a certificate which the client trusts. Some light reading: https://support.microsoft.com/en-us/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-microsoft-management-console

So to answer your question directly, yes, if one client connects to an instance with encryption and others don't, that one client will require more processing power than the others. Having said that, unless there's a configuration problem or the server is having processing capacity issues, nobody should notice.


PostgreSQL undocumented timenow() function? Looking at the documentation of PostgreSQL I couldn't find anything on timenow(). Yet if I call the function it works.
So what is the difference between now() and timenow() ?
I am going to guess that now() is based on transaction while timenow() is server OS time or am I completely off?
The below is DataGrip's auto generated definition. All system functions (for example sum) all have missing source code and as you can see it's part of the pg_catalog schema.

Yes, this is undocumented, very old, deprecated for a long time ago stuff.
The key difference from the NOW function is the data type of the result. timenow used the type abstime, which was less accurate and prone to the year 2038 problem. There is a small note in the documentation:

The types abstime and reltime are lower precision types which are used internally. You are discouraged from using these types in applications; these internal types might disappear in a future release.

This type was completely removed in postgresql 12 with all related functions. Per source code I can confirm: yes, timenow will return real time by every call, not the time of transaction start.
You could achieve same result by using clock_timestamp() function instead of now()