Check Java Driver version in clients from Db2 server I have a Db2 database that is being used by many applications in different servers. I want to check from the Db2 server if the Java driver used in the different client applications is up-to-date.

Is the any way to check the version of the java driver client from the Db2 server?
I have tried the following without success:

db2 list application show detail
db2 "SELECT *  FROM TABLE(MON_GET_CONNECTION(cast(NULL as bigint), -2)) AS t"

In the last command, the CLIENT_PRDID column could have some of this information, but it only shows: JCC04110 and I do not know what does it mean.

This page https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.mon.doc/doc/r0001170.html  describes the client_product_id monitor element thus

  Use this element to identify the product and code version of the data server client. It is in the form PPPVVRRM, where:

  PPP identifies the product. This is "SQL" for IBM® relational database products.
     VV identifies a 2-digit version number.
     RR identifies a 2-digit release number.
     M identifies a 1-character modification level (0-9 or A-Z).

comparing what CLIENT_PRDID shows

SELECT distinct CLIENT_PRDID  FROM TABLE(MON_GET_CONNECTION(NULL, -2))

JCC04220

vs the jdbc type 4 driver version

java -cp $DB2_HOME/java/db2jcc4.jar com.ibm.db2.jcc.DB2Jcc -version

IBM Data Server Driver for JDBC and SQLJ 4.22.29

gets a close match for me.  I can't say I know why the mod level is not set.

on a different system, looking at the type 2 driver, I get JCC03690 from CLIENT_PRDID but IBM DB2 JDBC Universal Driver Architecture 3.72.44 from java com.ibm.db2.jcc.DB2Jcc -version.. so it's maybe nearly of right...


Optimize a SQL query I have to select 2 values that were less that the given id and greater than the given id. I have tried this query but is there any better way to do it

Fiddle Sql

Begin

declare @rootValue int
declare @repID int

set @repID = 2
set @rootValue = (select Id from tblLookups where Id = @repID)

declare @rootMinusTwo int
declare @rootPlusTwo int

set @rootMinusTwo = (select count(*) from tblLookups where Id < @rootValue)
set @rootPlusTwo = (select count(*) from tblLookups where Id > @rootValue)

if @rootMinusTwo >= 2 and @rootPlusTwo >= 2
Begin
    select *
    from tblLookups
    where Id between @rootValue - 2 and @rootValue + 2
End
else if @rootMinusTwo < 2 and @rootPlusTwo >= 2
    select *
    from tblLookups
    where Id = @repID
    union
    select *
    from tblLookups
    where Id < @rootValue
    union
    select top (4 - @rootMinusTwo) *
    from tblLookups
    where Id > @rootValue
else if @rootMinusTwo >= 2 and @rootPlusTwo < 2
    select *
    from tblLookups
    where Id = @repID
    union
    select *
    from tblLookups
    where Id > @rootValue
    union
    select top(4 - @rootPlusTwo) *
    from tblLookups
    where Id < @rootValue
else if @rootMinusTwo < 2 and @rootPlusTwo < 2
    select *
    from tblLookups
    where Id = @repID
    union
    select *
    from tblLookups
    where Id < @rootValue
    union
    select *
    from tblLookups
    where Id > @rootValue
End

I forgot to add something at any point there should be 5 records if at all there are that many records satisfying the condition.

Example

The ID's are 1,2,3,4,5 if I supply 2 then also it should return 5

Quite convoluted but should work and return the 2 rows with values below the @repid, the row with @repid and the 2 rows with values above the @repid (assuming that id is a unique key).

If there are less than 2 values below or above, the query will get more from the other side. In total a maximum of 5 rows will be returned.

Note: the ORDER BY is needed. All three of them (not the 4th, that's only if you need the results in specific order):

with a as
  ( select top (4)
        *, rnk = row_number() over (order by id desc)
    from tblLookups
    where Id < @repID
    order by id desc
  ),
b as
  ( select top (5)
        *, rnk = row_number() over (order by id) - 1
    from tblLookups
    where Id >= @repID
    order by id
  ),
c as
  ( select top (5) *
    from
      ( select *
        from a
      union all
        select *
        from b
      ) x
    order by rnk
  )
select *
from c
order by id ;

Tested at SQLfiddle.


Converting 'False'(string) to False(boolean) During LOAD DATA LOCAL INFILE In MySQL is there anyway of loading "False" into a boolean column.

While loading data in table having boolean column and where data is in "True" or "False", it gives warning of:

"Incorrect integer value: 'False' for column 'c1' at row 1".

Table structure is as follow

Create Table: CREATE TABLE `n1` (
  `c1` boolean ,
  `c2` int
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I am using load data statement to load from file "file1" which is comma separated and enclosed in double quotes. Following is the data sample

"False","1"
"True","2"

The load data local infile statements is as below

load data local infile 'file1'
into table n1
  FIELDS TERMINATED BY ','
  enclosed by '\"' LINES
  TERMINATED BY '\r\n';

Convert the first value using user variables to load the True/False values.

Then, compare it to the value 'True'

PROPOSED SOLUTION

LOAD DATA LOCAL INFILE 'C:/bin/input.txt' INTO TABLE n1
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n' (@var1,c2)
SET c1 = (@var1 = 'True');

SAMPLE DATA

C:\bin>dir
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF

 Directory of C:\bin

10/21/2014  10:01 AM    <DIR>          .
10/21/2014  10:01 AM    <DIR>          ..
10/21/2014  10:01 AM                23 input.txt
               1 File(s)             23 bytes
               2 Dir(s)  685,548,244,992 bytes free

C:\bin>type input.txt
"False","1"
"True","2"
C:\bin>

CODE FOR PROPOSED SOLUTION

USE test
DROP TABLE IF EXISTS n1;
CREATE TABLE `n1` (
  `c1` boolean ,
  `c2` int
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
LOAD DATA LOCAL INFILE 'C:/bin/input.txt' INTO TABLE n1
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n' (@var1,c2)
SET c1 = (@var1 = 'True');
SELECT * FROM n1;

CODE FOR PROPOSED SOLUTION EXECUTED

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS n1;
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE `n1` (
    ->   `c1` boolean ,
    ->   `c2` int
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.38 sec)

mysql> LOAD DATA LOCAL INFILE 'C:/bin/input.txt' INTO TABLE n1
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
    -> LINES TERMINATED BY '\r\n' (@var1,c2)
    -> SET c1 = (@var1 = 'True');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM n1;
+------+------+
| c1   | c2   |
+------+------+
|    0 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Can `listen_addresses` system configuration setting in Postgres stop pre-authentication exploits? You can set various configuration parameters for Postgres by either editing the postgresql.conf file manually or by calling ALTER SYSTEM commands.

One of those config settings is listen_addresses. To quote the documentation:

  Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications. The value takes the form of a comma-separated list of host names and/or numeric IP addresses. The special entry * corresponds to all available IP interfaces. The entry 0.0.0.0 allows listening for all IPv4 addresses and :: allows listening for all IPv6 addresses. If the list is empty, the server does not listen on any IP interface at all, in which case only Unix-domain sockets can be used to connect to it. The default value is localhost, which allows only local TCP/IP “loopback” connections to be made. While client authentication (Chapter 20) allows fine-grained control over who can access the server, listen_addresses controls which interfaces accept connection attempts, which can help prevent repeated malicious connection requests on insecure network interfaces. This parameter can only be set at server start.

➥ Does this mean listen_addresses theoretically stops pre-authentication exploits?

Is there any engagement between the Postgres server and the incoming connection which might possibly result in an exploit? Or will the forbidden incoming connection be blocked without any engagement?

Of course, ideally, a firewall on the host operating system would also be in place to block unwanted incoming connections. But let’s ignore firewalls for the purpose of this Question.

listen_addresses filters connections before postgres sees them

  ➥ Does this mean listen_addresses theoretically stops pre-authentication exploits?

Yes. If PostgreSQL's postmaster isn't listening on a given interface (as identified by its local address) then no remote host may connect to it via that interface. The operating system will report the TCP port as closed and send a TCP RST to the connecting host; PostgreSQL code is never reached, so PostgreSQL bugs cannot be exploited, even pre-auth ones.1.

listen_addresses blocks pre-auth exploits

  Is there any engagement between the Postgres server and the incoming connection which might possibly result in an exploit? Or will the forbidden incoming connection be blocked without any engagement?

No. listen_addresses configures the listening TCP socket at the operating system level, binding it only to the network interface(s) specified2. It filters on the connection target address specified by the remote host. The OS won't tell PostgreSQL about connections to other interfaces at all.

Note that the same is is not true of pg_hba.conf. pg_hba.conf controls remote-host source address filtering and authentication configuration. The PostgreSQL postmaster does handle any connection that comes in on a listened-on address and is then rejected by pg_hba.conf configuration.

You can do sender-address based filtering before PostgreSQL sees the connection by configuring operating system firewall rules. Those will prevent PostgreSQL pre-auth exploits by blocking the connection from ever reaching PostgreSQL.

So if you know that only the hosts in network 111.1.0.0/16 have any business connecting to your PostgreSQL it's a good idea to configure a firewall rule accordingly. You should set pg_hba.conf as a fallback, but the firewall rule should stop anyone even attempting to connect to postgres itself.

Understanding authentication flow

To connect to postgres you must pass a series of "gates" of sorts. Ignoring UNIX sockets for this explanation, we have:

OS network and TCP level, before you hit PostgreSQL code:

Any border router or firewall between you and postgres must permit the TCP connection
Any operating system firewall configured on the postgres host must permit the TCP connection
listen_addresses - postgres must be listening on the interface or the OS treats the TCP port as closed
Any network security extensions like TCP Wrappers (/etc/hosts.allow and /etc/hosts.deny) must permit the connection. (Assuming they're supported on your OS and postgres is compiled to use them)

Within PostgreSQL code:

A pg_hba.conf host, hostssl or hostnossl rule is found when matching by source-address, ssl mode, target dbname and requested username
The matched pg_hba.conf rule must not specify reject
If SSL client certificate checks are enabled, the SSL client certificate satisfies the configured CA cert.
The requested username and database name must both exist in the PostgreSQL catalogs
The requested username or a role it inherits must have the LOGIN option in the PostgreSQL catalogs
The requested user must have CONNECT rights to the requested database. (By default the public role all users are a member of has CONNECT rights, but you can REVOKE that and GRANT it only to specific users or roles/groups).

Yay, you've connected

If a connection is blocked at an earlier stage, it never interacts with later stages. I haven't checked the exact ordering of the dbname vs username privilege checks, but the rest is right.

I ignored pg_ident.conf and username mappings, client cert DN mappings, the details of things like SSPI/GSSAPI and low level auth methods, etc here.

Now, if you're using UNIX sockets (unix_socket_directories, and a libpq host address that's a path or omitted entirely), the PostgreSQL stage is the same except that pg_hba.conf matching doesn't check source-address and looks for local lines instead of host, hostssl or hostnossl lines. The peer auth mode is supported to require a unix username to postgres username match. Details in the manual.

Exposing PostgreSQL to the Internet

Let me note that pre-auth exploits for PostgreSQL are not wholly unheard of, but are rare. It's fairly routine to expose PostgreSQL on the Internet directly.

You should use hostssl in pg_hba.conf to enforce SSL connections to protect authentication exchanges and make casual scanning a bit harder. And you should adopt the same protective measures you use for any other Internet-exposed service: use fail2ban or similar, use an IDS, monitor logs, and have firewall rules to exclude anything that you know has no business connecting.

However, if you don't need to expose PostgreSQL to the Internet, don't do it. In particular, if you only need loopback connections, bind PostgreSQL to the loopback address(es) 127.0.0.1 and ::1. Or even better, use unix sockets.

See also

The "Client Authentication" chapter of the manual
listen_addresses
pg_hba.conf
For Ubuntu and (mostly) Debian:

Ubuntu docs for Ubuntu's PostgreSQL packages
Ubuntu community help wiki for PostgreSQL

1 The attacker could still exploit OS bugs in the network stack. Or (very unlikely) they might be able to use tricks like source-address spoofing to fool a buggy OS into letting it send an initial packet to PostgreSQL even when it's bound to a different interface. Any modern, sensibly configured OS will prevent that.

2 Internally each listen_addresses entry is used to make a separate listening TCP socket. For UNIX-like OSes it's passed to the bind(...) call on each socket. See postmaster.c around line 1012, if (ListenAddresses), and see the StreamServerPort adapter in src/backend/libpq/pqcomm.c around line 532.


How to select non unique values from 1 column if a value doesn't exist in column 2 I have a table containing (among other things) 2 integer values, neither of which are unique. See below for example data:

---------------
| a    | b    |
---------------
| 1    | 1    |
---------------
| 1    | 2    |
---------------
| 2    | 1    |
---------------
| 2    | 2    |
---------------
| 2    | 3    |
---------------

How can I query column a, where certain values don't exist in the corresponding column b. E.g. for value 3, I'd want it to return the value 1 for column a
I've come up with:

SELECT a FROM table WHERE a NOT IN (SELECT a FROM table where b = 3)

But this doesn't feel like the best/most efficient way.

You query will need DISTINCT, so it doesn't return the values multiple times:

SELECT DISTINCT a
FROM table
WHERE a NOT IN (SELECT a FROM table WHERE b = 3) ;

Also beware that NOT IN may return unexpected results if your columsn are nullable. It's safer to use NOT EXISTS or LEFT JOIN / IS NULL:

SELECT DISTINCT a
FROM table AS x
WHERE NOT EXISTS (SELECT 1 FROM table AS y WHERE y.b = 3 AND y.a = x.a) ;


Sql Server Linked Server I have two sql server S1(2008) and S2(2005).

S2 is added to S1 as linked server.

S2 have a table Product.

When I try to run the code

select ProductID from Product

I get the error:

Location:    rmt_meta.cpp:347
Expression:  fFalse
SPID:        71
Process ID:  3828
Description:     Unknown column in remote index! Skipping.

Any idea?

This KB is the closest thing I can find regarding your error, that would come from SQL Server. I would see if you could find any other errors that may give you more explanation as to the issue. Since this seems to be dealing with XML data I cannot help you that much, have not worked with XML much. Other folks on this forum might be able to chime in though...


What files are safe to delete after upgrade? This question is very similar to Recommended procedure to remove old files after major version PostgreSQL database cluster upgrade, but I did the upgrade in a different way, so the answer might be/probably is different.
I administer a database on a headless Ubuntu system with disks very near capacity.  Therefore, when I upgraded my database from Postgres 10 to Postgres 12, I used the --link option.
/usr/lib/postgresql/12/bin/pg_upgrade --old-datadir=/var/lib/postgresql/10/main \
--new-datadir=/var/lib/postgresql/12/main [stuff deleted] --link

This worked great, and now I have Postgres 12 running nicely.  Time to cleanup.
I now have 2 folders in /var/lib/postgresql, called 10 and 12.  The documentation suggests that these folders (or the files within them) are joined by a hard link, but doing the following suggests otherwise:
[deleted output from du that was misleading and did not say what I thought it said]

I'd like to clean this up as best I can, both because space is at a premium, and I know that there is a danger that future me will come along and delete 10 because we're on 12.
In this situation, what do I need, and what can I delete?  How can I make the folder structure as clear as possible to my future self?
Are there things elsewhere I can delete?

That is simple: remove the old data directory, and the garbage should be gone.
You have to do that anyway, because the old data directory cannot be used any more once you have started the new server.
This will of course not remove bloat. To get rid of that, identify affected tables and indexes and use VACUUM (FULL) or REINDEX to rebuild them.


SQL Server Estimated I/O Cost in real units As I understand from other StackExchange posts, SQL Servers estimates are unitless meaning an Estimated I/O cost of 2.82387 doesn't refer to the time taken or disk I/O's, but a relative measure of how it compares to other query criteria such as the CPU cost.

Is it possible to get a real estimate of disk I/O's for a query, how many pages it believes it will read in etc.? I'm also interested in the actual costs in these units.

As part of a course I'd like to use methods for calculating estimates (Disk I/O's and result sizes) manually and comparing these to SQL Server results for the same queries.

Is it possible real information on execution plans in SQL server and if so how? Or is it only possible to get this unitless information, which I can't really use for comparison.

The best numbers for IO you can get is to turn on statistics.

SET STATISTICS IO ON

and then run your query via management studio. The set option will work for your current connection, and the information will appear in the messages tab.

Then you'll get information on how many pages will be read based on your query.

Also for ease of parsing complex/large query statistics, I use this handy little webpage: http://www.statisticsparser.com/


SSIS Execute Sql Task not executed on server I have a package that has an Execute Sql Task.  When I run it in SSDT it works fine, the query is executed.  When I deploy it on the server and run the package, the query is not executed and there is no error in the execution report.  Even if I put "BLABLABLA" as the query, there is no fail.  The query is not dynamic.

I've started SQL Profiler while running the package and I can see that the Task goes through the Pre and Post Validation, but the query is never executed.

I've tried running the package on the server with my user with no luck.

Here's a screen shot of my package.

I've disable all other task to isolate the problem.

Thanks for your help

Expanding my comment into an answer - scr_Start has a precedent constraint, f/x, that is not being satisfied when this executes on the server. You'll see that seq_Execute never shows in your SSIS execution report (assuming project deployment model).

Either disable the script task or ensure that whatever condition is being set is valid for your server deployment.


Passing ROWTYPE parameter to EXECUTE I am developing a function in Postgres which aims to recover for each record of a query the value of a result of a check contained in a set of functions. Only one of these functions will return the correct value. These functions have a common prefix 'fn_condition_' and receive an object of type 'my_table' as parameter.

As the number of functions that make the check is unknown, I decided to consult the Postgres catalog, from the table pg_catalog.pg_proc searching for functions with the prefix 'fn_condition_' and dynamically execute them with EXECUTE.

My problem is the correct form to pass the parameter for EXECUTE.

create or replace function test_conditions()
returns void as
$$
declare
    v_record my_table%rowtype;
    v_function pg_proc%rowtype;
begin
    set search_path = 'pg_catalog';

    for v_record in (select * from my_table where id in (1,2,3)) loop
        for v_function in (
            SELECT  p.proname
            FROM    pg_namespace n
            JOIN    pg_proc p
            ON      p.pronamespace = n.oid
            WHERE   n.nspname = 'operacional'
            and p.proname like ('fn_condition\\_%')
            order by p.proname)
        loop
         -- execute 'select ' || v_function.proname || '(' || v_record || ')';  -- ???
        end loop;
    end loop;
end;
$$
language plpgsql;

How to pass v_record properly in the commented EXECUTE command in the function above?

execute 'select ' || v_function.proname || '(' || v_record || ')';  -- ???

Example function:

create or replace function fn_condition_1(p_record my_table)
returns bigint as
$$
begin
    if ($1.atributo1 > $1.atributo2) then
        return 1;
    end if;
    return null;
end;
$$
language plpgsql;

In Postgres 8.4 or later you would use the USING clause of EXECUTE to pass values safely and efficiently. That's not available in your version 8.3, yet. In your version it could could work like this:

CREATE OR REPLACE FUNCTION test_conditions()
  RETURNS SETOF bigint AS
$func$
DECLARE
   _rec    record;
   _func   text;
   _result bigint;
BEGIN
   FOR _func in
      SELECT  p.proname
      FROM    pg_catalog.pg_namespace n
      JOIN    pg_catalog.pg_proc      p ON p.pronamespace = n.oid
      WHERE   n.nspname = 'operacional'
      AND     p.proname LIKE E'fn\\_condition\\_%'  -- no parens, proper string
      ORDER   BY p.proname  -- no parens
   LOOP
      FOR _rec in
         SELECT * FROM my_table WHERE id IN (1,2,3)  -- no parens needed
      LOOP
         EXECUTE 'SELECT ' || quote_ident(_func) || '(' || quote_literal(_rec) || ')'
         INTO _result;
         RETURN NEXT _result;
      END LOOP;
   END LOOP;
END
$func$  LANGUAGE plpgsql SET search_path = 'public';

Call:

SELECT * FROM test_conditions();

If you use set search_path = 'pg_catalog'; in the function body, then your table in the public schema is not visible any more. And it would be a very bad idea to globally SET the search path. The effect stays for the duration of the setting. You could use SET LOCAL to contain it to the transaction, but that would still be a bad idea. Instead, if you really need to, set the environment of the function only, like demonstrated.
More about the search path in Postgres:

How does the search_path influence identifier resolution and the “current schema”

Just executing a SELECT without assigning or returning the result would be pointless. Use the INTO clause of EXECUTE and then RETURN NEXT. In modern Postgres you would replace the inner loop with RETURN QUERY EXECUTE.
Use quote_ident() and quote_literal() to escape identifiers and literals properly when building a dynamic query string. In modern Postgres you would use format().
It's not very efficient to cast the whole row to it's string representation, escape and cast back. This alternative approach has to read from the table repeatedly, but is cleaner otherwise (the row is passed as value directly):

  FOR i IN
     VALUES (1), (2), (3)
  LOOP
     EXECUTE 'SELECT ' || quote_ident(_func) || '(t) FROM my_table t WHERE id = ' || i
     INTO _result;
     RETURN NEXT _result;
  END LOOP;

Example function

You can also radically simplify your example function with this SQL function:

CREATE OR REPLACE FUNCTION fn_condition_1(p_record my_table)
  RETURNS bigint AS
$func$
   SELECT CASE WHEN $1.atributo1 > $1.atributo2 THEN bigint '1' END
$func$  LANGUAGE sql;


Database Administrator vs Database Architect? In terms of job responsibilities and activities, what is the difference between a Database Administrator (DBA) and a Database Architect?

I would say the term Database Administrator covers the full gamut of database duties: design, development, production support, performance tuning, report writing, OLAP, etc.

A Database Architect is involved in the design and development of large or complicated database solutions. However, the Architect is usually not involved in the day-to-day operations of the system once it is deployed.

I don't really like either term.  Database Administrator is too vague, Database Architect sounds pretentious.

I am a computer programmer at heart, but aren't we all?


SQL Server drop and create all users in every DB I would like to drop and recreate every user in a database without having to manually do this for 500 users. I have this so far:

USE [Maintenance]
GO

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'xxxxxx\xxxxx')
DROP USER [xxxxx\xxxxx]
GO

USE [Maintenance]
GO

CREATE USER [xxxxx\xxxxx] FOR LOGIN [xxxxx\xxxxx] WITH DEFAULT_SCHEMA=[dbo]
GO

The real problem is when I detach and reattach db from a SQL Server 2005 and restore to a SQL Server 2008, all the logins carry over, but the do not work unless I drop and add them back. I have read many many links and other threads about them and tried How to transfer logins and passwords between instances of SQL Server(http://support.microsoft.com/kb/246133) but it did not work.

On a LAN, we have 500 domain users that access a SQL Server 2005 database. I have created a backup from the SQL Server 2005 databases and want to move all the databases to a new server that will run SQL Server 2008. I have 12 people that have SQL Server login's. I have remade those and they work. I have 500 user's that, through there dsn, should be able to access a database, if the are assigned roles db_datareader/db_datawriter to the DBNAME->Security->User list. The ones that don't work are the domain database users. Under the DBname/instancename->Security->Logins every db domain user is not currently listed under the logins

The message I keep getting on the SQL Server 2005 is This is the message I keep getting. Login failed for user 'COMPANY\USER'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: XX.XXX.XX.XX]

Here is the dsn:

Microsoft SQL Server ODBC Driver Version 03.85.1132
Data Source Name: xxxxsql002
Data Source Description: xxxxsql002
Server: xxxxsql002\xxxxbaconsql
Database: (Default)
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Integrated Security: Yes
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No

The UAC and firewall are disabled.

Thanks!

OK, there are several things that it could be but I'm not sure if we can determine which from here.  These include the following:

AD propagation issues
AD caching "stickiness" on the target SQL Server
Domain Trust issues (particularly if the target server is in a different domain)
Windows Group membership/visibility issues
UAC blocking users access to certain groups needed for the new server
Kerberos issues (very common)
Etc...

And yes, dropping and re-adding the Users does seem to fix it in some of these cases, even though this should in theory never be needed because the SIDs should always match for Windows Users in transferred databases.  It may be that the target SQL server simply lacks the same necessary server-level principal definitions as the original servers (and this as a side-effect creates explicit ones), or it may be that the act of adding the Login/user causes SQL Server to forcibly rerun the query and reload it's AD information for that user.

In any event, the real problem tends to be that dropping a user is a potentially disruptive and destructive act.  It may work, but it may also cause some bad side-effects.  Nonetheless, if you want to try that, then the best procs I know of for that are from Ted Krueger and they are located here: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/fixing-orphaned-database-users.

Other (possible) solutions that you could employ include:

Automated procedure that does not drop the User, but does add and associate a server-level Windows Login for it.
Create a Windows AD group for access to the database, add all of the users to that, and then add that to the SQL Server as a Login and Database as a User. (this is the recommended approach for managing large-scale access anyway).
...


Creating additional indexes on a table with a columnstore clustered index According to this article, a table with a clustered columnstore index can have one or more additional non-clustered indexes. However, when I try to add a non-clustered index on a table with a CCI, I get the error shown in the image below:

Am I overlooking something, or misinterpreting something? I'm using SQL Server 2016 with SP1.

You can create additional nonclustered indexes on tables with clustered columnstore indexes, the following script shows this...

CREATE TABLE dbo.T2 (C1 INT NOT NULL, C2 INT, C3 INT);
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCI ON dbo.T2;
GO
CREATE NONCLUSTERED INDEX IX ON dbo.T2 (C2);
GO

I have attempted the same thing using SSMS 16.5.3. It is possible that there is a bug in the version you are using (latest is at https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms ).

Consider creating the script using TSQL rather than the GUI to see if you get the same result.


list of batch aborting errors in SQL server In SQL Server, if XACT_ABORT is off then some errors will terminate the current statement (for example supplying the incorrect number of parameters to a stored procedure that takes some parameters) and some errors will abort the whole batch (for example supplying parameters to a stored procedure that doesn't take parameters). [Reference]: http://www.sommarskog.se/error-handling-I.html#scope-abortion.

What I would like to know is whether there is a definitive list of which errors are batch aborting and which ones are statement terminating.

I believe there are a few exceptions, but from Database Engine Error Severities (MSDN):

Error messages with a severity level of 19 or higher stop the execution of the current batch.
Errors that terminate the database connection, usually with severity from 20 through 25, are not handled by the CATCH block because execution is aborted when the connection terminates.

So it seems like you could get a definitive list from the following query (of course this will not allow you to filter out which ones can be caused by user T-SQL):
SELECT message_id, severity, [text]
FROM sys.messages
WHERE language_id = 1033
AND severity >= 19
ORDER BY severity, message_id;

In SQL Server 2012, this produces 210 rows.
In SQL Server 2016, this produces 256 rows.
By the way, I don't believe the two scenarios you describe in your question work the way you think, at least not in modern versions of SQL Server. I tried this on both 2012 and 2016 (I believe Erland's article describes SQL Server 2000 behavior, which I don't remember if it was any different, but not very relevant today even if so).
USE tempdb;
GO

CREATE PROCEDURE dbo.pA -- no parameters
AS PRINT 1
GO
CREATE PROCEDURE dbo.pB -- two parameters
@x INT, @y INT
AS PRINT 1
GO

SET XACT_ABORT OFF;
GO

EXEC dbo.pA @foo = 1;
PRINT '### Calling procedure that doesn''t take parameters with a parameter';
GO

EXEC dbo.pB;
PRINT '### Calling procedure that takes 2 parameters with no parameters';
GO

EXEC dbo.pB @x = 1;
PRINT '### Calling procedure that takes 2 parameters with not enough parameters';
GO

EXEC dbo.pB @x = 1, @y = 2, @z = 3;
PRINT '### Calling procedure that takes 2 parameters with too many parameters';
GO

These all produce errors of severity level 16, and all of them proceed with the batch, as evidenced by the print output:

Msg 8146, Level 16, State 2, Procedure pA, Line 11
Procedure pA has no parameters and arguments were supplied.
### Calling procedure that doesn't take parameters with a parameter
Msg 201, Level 16, State 4, Procedure pB, Line 14
Procedure or function 'pB' expects parameter '@x', which was not supplied.
### Calling procedure that takes 2 parameters with no parameters
Msg 201, Level 16, State 4, Procedure pB, Line 18
Procedure or function 'pB' expects parameter '@y', which was not supplied.
### Calling procedure that takes 2 parameters with not enough parameters
Msg 8144, Level 16, State 2, Procedure pB, Line 22
Procedure or function pB has too many arguments specified.
### Calling procedure that takes 2 parameters with too many parameters

As I suspected, there are exceptions, of course, as noted in the comments. Conversion failure is severity 16 but aborts the batch:
SET XACT_ABORT OFF;
SELECT CONVERT (INT, 'foo');
PRINT 'Made it.'; -- no print happens

Results do not include the print output this time:

Msg 245, Level 16, State 1
Conversion failed when converting the varchar value 'foo' to data type int.


copy an exported dump file to a target DB instance - LOG I just imported data into an Amazon RDS DB instance using this guide

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.html

But in the source DB the number of tables is 309 whereas that for the target DB is only 4

select count('1') from dba_tables where owner='NUNITO';

Is there a way to see the log files ?

I used

DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT', job_mode => 'SCHEMA', job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'sample.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'exp.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''SCHEMA_1'')');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/

You can see a list of the files in the DATA_PUMP_DIR directory by running

 select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by filename;

Additionally, you can read the contents of the file by using

select * from table(RDSADMIN.RDS_FILE_UTIL.READ_TEXT_FILE('DATA_PUMP_DIR','filename.log'));

(Credit for the last script goes to: https://www.experts-exchange.com/questions/28961936/open-a-file-on-a-remote-server-amazon-rds-oracle.html)

I hope it helps


How do I determine the current "idle timeout" value for PostgreSQL? Sigh. I've been searching forever for this and, as usual, find nothing.

I'm not talking about the "connection timeout" for how long it waits before giving up when trying to connect to the database. I'm talking about how long a database connection can be "idle" without any queries being executed (and no pg_pings) before it's considered abandoned and force-closed.

Which setting controls this, and how do I retrieve it?

Before 9.6, no such timeout exists in PostgreSQL. Thereafter, it only exists if you are idle in the middle of a transaction.

show idle_in_transaction_session_timeout;

Note however that any router, gateway, or firewall between you and the server can nuke your connection at any time it chooses.  TPC keepalives may or may not dissuade them.  PostgreSQL can't offer much insight into what your networking equipment does.


Proper use of varbinary type in MS SQL Server I am at a new position and I am being told to implement a stored procedure that will accept a list of user ids and update a flag.

When I suggested the use of a table value parameter (array emulation), I was told to implement as demonstrated below, as this is how it's been done before.  I'm not a DBA but I am a full stack developer and this smells funny to me.

FYI- the user ids are implemented as type int

CREATE PROCEDURE [dbo].[UpdateUsers]
 @rgIDs varbinary(max)      -- contains several ids
AS
 DECLARE @tblTmp TABLE (ID int PRIMARY KEY)

 DECLARE @ich int, @cch int, @ID int
 SET @cch = DATALENGTH (@rgIDs)
 SET @ich = 1

 WHILE (@ich < @cch)
 BEGIN
  SET @ID = SUBSTRING (@rgIDs, @ich, 4)

  UPDATE dbo.Users u
  SET isUpdated = 1
  WHERE u.ID = @ID

  SET @ich = @ich + 4
 END

Doesn't a table valued type work better here?  More readable,  performance, less error prone, etc...?

I will be using SQL Server 2012 or 2014, I believe.  Definitely > 2008.

I was told to implement as demonstrated below as this is how its been done before. ... this smells funny to me.

You know what smells funnier than this approach? That line of "reasoning". The old "this is how it's always been done" is simply a means of avoiding thinking about it and discussing it. And that alone should raise a red flag, even if the code is found to be the best approach. I've been told the same thing before and it was for the same sad underlying reason: that approach had been taken for an obsolete reason (such as an older version of SQL Server didn't have a particular feature, but we were using a newer version that did have that feature). Very frustrating.

  Doesn't a table valued type work better here? More readable, performance, less error prone, etc...

Assuming that you are using SQL Server version 2008 or newer, then the answer is generally YES to all of that. I mean, the current code is a somewhat clever-ish approach to avoiding a string split operation. But, a TVP can be strongly-typed so no munging the int values in the app layer into a byte[] just so that it can be unpacked in a WHILE loop here. A single set-based UPDATE statement will be much better for performance than N number of UPDATEs based on how many IDs are in that list (the current code creates, and commits) 1 transaction per each UPDATE statement since the WHILE loop is not wrapped in an explicit transaction. Hence, 20 IDs to update = 20 transactions in the current approach. This is part of why the single, set-based UPDATE is more efficient.

Just be sure to implement the full-streaming approach which means: do not dump the values from a collection into a DataTable! Doing so, which is unfortunately what you find in most examples, is a needless waste of time, memory, and CPU. Instead, create a method that implements IEnumerable, accepts the collection of UserIDs, and returns IEnumerable<SqlDataRecord>. Then, use that method as the "value" for the SqlParameter that is the TVP. In that method, cycle through the collection, and per each element, call yield return;.

I have some example code on StackOverflow in my answer to the following question:

Pass Dictionary to Stored Procedure T-SQL

Please note that I have heard, though not yet tested, that on the more recent versions of SQL Server (2014 or newer) that there might be additional performance gains by using in-memory OLTP for the User-Defined Table Type.

Also, since you mentioned this as part of a desire for "more readable code", I would suggest using meaningful parameter and variable names :-). And yes, I do realize that this is probably not your code, but just thought I would mention it. Also, I would get rid of DECLARE @tblTmp as it isn't be used, but again, I realize that this might be a part of a larger example that you redacted.


how to prevent multiple pg_dump to execute at the same time I am exposing a REST API to backup a Postgres DB. I have few questions related to that. I have seen most of these questions floating around google but could not find any suitable answer

Is there any way to know the status of the pg_dump? I can redirected the verbose log to the user but somehow I am not convinced with that.
Is there any way to know that a "pg_dump" session is in progress so that I can prevent the user from re-triggering?

You could check if there is running pg_dump process by looking at the pg_stat_activity and also the state of the process:

SELECT
    pid,
    datname,
    usename,
    state
FROM pg_stat_activity
WHERE
    application_name = 'pg_dump';

It would be better if you could also lock some file with your script when you are starting pg_dump and check the lock status of that file when starting new pg_dump to avoid re-triggering.


MySQL | Complex (for me) SQL Statement, how to? I have a table that contains posts and another table that contains meta options for each post in first table. The meta options table is a key value pare table.

Lets say I have the posts tables that look like that

Posts
---------------------------------------------
id | Other columns ....
---------------------------------------------
1  | data ...
2  | data ...
3  | data ...
4  | data ...
5  | data ...

and the meta options table that look like that

Meta
---------------------------------------------
id | post_id | meta_key | meta_value
---------------------------------------------
1  | 1       | views    | 5
2  | 1       | maxViews | 100
3  | 2       | views    | 0
4  | 2       | maxViews | 1000
5  | 1       | publisOn | 2013-05-14 10:41
6  | 1       | auhor    | MyUser
7  | 2       | auhor    | Another author

The question is, how can I get the post with id equals to 1 by making a comparison of the meta_key values views and maxViews.

For example, I like to retrive the post id with ID 1 only if the views is lower than the masViews in the Meta table.

Any help please ?

Assuming that (post_id, meta_key) has a UNIQUE constraint, you can join twice to the Meta table:

SELECT
    p.*,                                          -- replace * with the columns you need
    CAST(m1.meta_value AS SIGNED) AS views,
    CAST(m2.meta_value AS SIGNED) AS maxViews
FROM
    posts AS p
  JOIN
    meta AS m1
      ON  m1.meta_key = 'views'
      AND m1.post_id = p.id
  JOIN
    meta AS m2
      ON  m2.meta_key = 'maxViews'
      AND m2.post_id = p.id
WHERE
    CAST(m1.meta_value AS SIGNED) < CAST(m2.meta_value AS SIGNED) ;

And here is the SQL-Fiddle (thnx to @bluefeet)


Invalid column name even after checking if it exists so it should skip the query part I've got the following query:

    IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'NotificationTableLogRows' AND COLUMN_NAME = 'DestinationAddress') BEGIN
      UPDATE NotificationTableLogRows
      SET [HandlerID] = m.ID
      FROM [Unicare].dbo.MobileDevices m
      WHERE HandlerID is null
      AND m.CallNumber = NotificationTableLogRows.DestinationAddress AND NotificationTableLogRows.HandlerTypeID = 2
    END
print 'end'

But after running this, I still got the error:

Msg 207, Level 16, State 1, Line 6
Invalid column name 'DestinationAddress'.

It should however just print end

What am I doing wrong?

Edit:

Even with:

IF 1 = 0 BEGIN
    UPDATE NotificationTableLogRows
    SET [HandlerID] = m.ID
    FROM [Unicare].dbo.MobileDevices m
    WHERE HandlerID is null
    AND m.CallNumber = NotificationTableLogRows.DestinationAddress AND NotificationTableLogRows.HandlerTypeID = 2
END

It produces the same error, the problem seems to be in the way sql checks the query pre executing..

This is a parse time error, not a runtime error. You can't use an IF check to determine if a column reference will be ok to use, because the column reference is validated first, long before the IF ever runs (this is why IF 1 = 0 also fails).

  Note: The script in the question will appear to work (e.g. it will print end) if you copy the script to your own system where these objects do not exist. Due to deferred name resolution, the parse check will succeed if any of the objects do not exist yet, but will fail if all objects do exist, but any column does not.

As proof, compare:

IF 1 = 0
BEGIN
  SELECT 1 FROM sys.columns AS c
    INNER JOIN sys.objects AS o
    ON c.blat = o.splunge;
END
GO

/*
  Msg 207, Level 16, State 1
  Invalid column name 'blat'.
  Msg 207, Level 16, State 1
  Invalid column name 'splunge'.
*/

IF 1 = 0
BEGIN
  SELECT 1 FROM dbo.blats AS b
    INNER JOIN dbo.splunges AS s
    ON b.blat = s.splunge;
END
GO

/*
  "success"
*/

Now create a table called dbo.blats but without a column named blat:

CREATE TABLE dbo.blats(id int);

The second script still succeeds, because there is still an object that doesn't yet exist, and SQL Server is giving you the benefit of the doubt - at least at parse time - that you'll create it before you execute the script. It will fail when you add the second table, dbo.splunges, but without the referenced column splunge:

CREATE TABLE dbo.splunges(id int);

To get around this, you can use this cumbersome workaround,  or just use dynamic SQL (and here I use sys.columns instead of INFORMATION_SCHEMA, well, because):

IF EXISTS
(
  SELECT 1
    FROM sys.columns AS c
    INNER JOIN sys.objects AS o
      ON o.[object_id] = c.[object_id]
    INNER JOIN sys.schemas AS s
      ON o.[schema_id] = s.[schema_id]
    WHERE s.name = N'schema_name'
      AND o.name = N'object_name'
      AND c.name = N'column_name'
)
BEGIN
  EXEC sys.sp_executesql N'UPDATE ... <query that references column_name> ...;';
END

Also here is why I avoid shorthand metadata helpers like OBJECT_NAME(), SCHEMA_NAME(), etc., and instead recommend creating your own helper views.


What is the effect on primary keys when moving a CLUSTERED index to a new filegroup? I'm moving a CLUSTERED index from one filegroup to another, using the with drop_existing=on functionality.

Do I need to be concerned about primary keys?  In other words, is there some special handling I need to do to retain the existing primary/unique key?

I have tables on the primary filegroup that I need to move to a new filegroup. There are some tables with and some without indexes, some with and some without clustered indexes, and amongst those, some with and some without Primary Keys.

I'm wondering if there is any special handling I should consider for the tables with Primary Keys during the CREATE INDEX statements?

Does this even matter in this scenario?

No.

From an "on disk" organisation perspective the fact it is a constraint doesn't matter. That is, the physical and logical table layouts are separate

And PK when not clustered is just a unique non-clustered index with some rules


Soccer league ER diagram (implementing a many to many relationship) I am planning on creating a database to track teams and players in a soccer league. The stats I wish to track are as follows...

goals scored/conceded(gk), assists, minutes played, cards received and leading scorers.

As well as overall standings and fixtures and results. I have created an ER diagram and I think I have all the required tables but have some m:n relationships which I know shouldn't exist so am hoping my relationships are correct

Update
updated er diagram

I may or may not include the second tire of this league otherwise I don't think I actually need the league table.

will it cause me any problems if i implement it as is. i am not sure if it is ok to have 2 cols in the fixtures table that are referencing one column from the teams table. And my goals_scored table has the same two fk's as my player_match table.

Am I anywhere near the right track? Any help would be appreciated.

Do a Google of "open source soccer league software" and you will find a plethora of offerings already on the "market". My advice to you is to download a few of these, play with them (use last year's results).

See which ones come closest to what you want to do. Then take a look at their database schemas (and code if it's written in something you can read) and adopt and adapt the bits that suit you and leave the rest.

Unless you're planning to do this as a hobby project (and even then), but if you want to get up and running quickly, this is probably a good strategy.


Removing spaces from postgres SQL I have a sequence of insert statements which I want to run on a Postgres database. It looks something like this -

INSERT INTO test_admin.test_admins (id,
                                    "name",
                                    description,
                                    start_date,
                                    end_date)
        VALUES (
                  3,
                  'admin',
                  NULL,
                  TO_TIMESTAMP ('10/01/2015 00:00:00.00 -0500',
                                'MM/DD/YYYY fmHH24fm:MI:SS.FF Z'),

Due to the extra spaces preceding each line, neither pgadmin 3, nor the postgres console would accept this query and give syntax errors. What is the best way to remove these?

As spaces can be freely used in SQL, maybe you have some non-printable characters in your code. This problem usually relates to using Windows and/or word processors for programming. How (using what program) did you write your code?

Always use an editor designed for programming and always save in 'text only' mode or something similar. If you are in Windows, you can use Notepad (until you get a better editor which is out of scope here), copy-paste your code there and save it.


Pass .csv file as parameter file for expdp Oracle 11.2.0.4.0 Can we use .csv file as parameter file for expdp ?

Ex: expdp scott/xxx@dev parameter=table_full.csv

Thanks

No you cannot.  I'm not entirely sure what you are after here - I'm assuming that you want to unload a table in CSV format ?  If that's the case, then an easy way would be to download sqcl

http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html

It's free and small (10mb), and then its just a case of running a query against your table

SQL> spool my_output_file.dat
SQL> select /*csv*/ * from scott.emp;
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17/DEC/80,800,,20
7499,"ALLEN","SALESMAN",7698,20/FEB/81,1600,300,30
7521,"WARD","SALESMAN",7698,22/FEB/81,1250,500,30
7566,"JONES","MANAGER",7839,02/APR/81,2975,,20
7654,"MARTIN","SALESMAN",7698,28/SEP/81,1250,1400,30
7698,"BLAKE","MANAGER",7839,01/MAY/81,2850,,30
7782,"CLARK","MANAGER",7839,09/JUN/81,2450,,10
7788,"SCOTT","ANALYST",7566,09/DEC/82,3000,,20
7839,"KING","PRESIDENT",,17/NOV/81,5000,,10
7844,"TURNER","SALESMAN",7698,08/SEP/81,1500,,30
7876,"ADAMS","CLERK",7788,12/JAN/83,1100,,20
7900,"JAMES","CLERK",7698,03/DEC/81,950,,30
7902,"FORD","ANALYST",7566,03/DEC/81,3000,,20
7934,"MILLER","CLERK",7782,23/JAN/82,1300,,10

14 rows selected.


Mysql JOIN with 25million rows Recently I got stucked with a table where its grown to +25million and I have got a query that's doing a JOIN on it. The query take 2.5hours to execute even though I got Good 96GB RAM and RAID10 with BBU config.

TableA has 958090  records
TablesB has 25Million Records.

The query join is simple.

select tableA.col1, tablesA.col2, tablesA.directory, tableB.col1, tableB.col2
from tableA left join tableB ON tableA.col1=tableB.col1 AND tablesA.col2=tableB.col2
where tablesA.col2='123'
OTHER BY tablesA.directory desc;

tablesA.directory is VARCHAR(950);
INDEX ON tablesA.col2
INDEX ON tableB.col1 and tableB.col2

Please help me how to gain performance in this scenario.

Try adding index on combincation of tableB (col2, ,col1) because selection is happening on col2. This should work.


Distributor latency - slow sp_MSget_repl_commands performance One of our publishers is subscribed to by three subscribers, each subscriber is hosted on its own physical server. Two of those subscribers are 2008R2 and the third 2012. The 2012 subscriber is experiencing latency daily during peak times.

After running some extended event tracing for a couple of days I can see that the latency co-insides with slow sp_MSget_repl_commands execution times at the distributor, and more specifically the final select query found at line 724 "-- Mini join along. Only agent_id and article_id columns in dbo.MSsubscriptions". The execution time for this statement can run into >= 20 minutes.

Does the distributor reader thread wait for the entire execution of sp_MSget_repl_commands to complete before starting to write replicated commands to the command buffers? Or does it start writing replicated commands to the command buffers as soon as they are returned by the SP? I see that the problematic statement is hinted with OPTION (FAST 1) which might suggest that this is the case. If the reader thread does wait for sp_MSget_repl_commands to complete before starting to write replicated commands to the command buffer then that might explain the latency, otherwise I am unsure. Is there anything else obvious I can monitor?

The MSdistribution_history.comments suggest that the holdup is at the subscriber. However no replicated subscriber stored procedure are completing in >= 100 ms and there is no obvious physical server resource contention. I’ve ruled out the network because there are also other subscriber databases on the same physical server being populated across the same network which are not experiencing latency.

To answer my own question to an extent. Commands appear to be written to the command buffer as soon as sp_MSget_repl_commands makes them available. If the writer thread is unable to write the command to the subscriber then sp_MSget_repl_commands spins until the command has been written. Once written the writer notifies the reader and frees one of the two command buffers for the next batch of commands. This explains the long execution of sp_MSget_repl_commands.


Which are the options I have to create a structured log of all INSERT, UPDATE and DELETE from all tables of my PostgreSQL database? With MySql, I'm using replication api to record all changes from my database.
pymysqlreplication and https://github.com/nevill/zongji are what I'm using.
I wonder which are the options I have to do the same with PostgreSQL.
I'm looking for a simple solution, that doesn't depend on table schema or create trigger for every single table.

You would use logical decoding. You could choose an existing logical decoding plugin like wal2json, or you can write your own.
Note that you will only get data modifications that way, DDL statements won't be decoded. It is not absolutely required, but strange things can happen if your tables have no primary key.
If your goal is not to capture the changes but to log all statements that modify anything, set log_statement = 'ddl'.


One table with a changing variable or different tables? I have a problem where all the data except one column, which is known to have 4 different values, will be a variable.

Other than that column everything else will be of same type(only column names are same but data will be different)

For smaller applications I hope it is OK to just add the flag and make it one table.

I will be working with data containing around 100,000 rows and increasing everyday at the below ratios. If split they will be like

Type 1 - 60,000
Type 2 - 20,000
Type 3 - 10,000
Type 4 - 10,000

I want a better way to implement especially on MySQL but information about Oracle is much appreciated as I want to gain knowledge in that too.

What happens if I add this to a single table and make the type as index so the burden for where conditions will be low?

How comparable is that to having 4 different tables?

Personally, I don't like the idea of 4 tables but I am looking for an answer in performance perspective.

You have a common problem, which is how to implement table inheritance into a relational database. There is not perfect solution, as it will be fully dependent on how you are going to use your database afterwards and what you want to optimize for.

Typical way to implement your solution is one of 3 ways: create 4 separate tables, create a single table, or create 5 tables, one for the "parent" relationship and 4 for the "children". You can find more discussion on the advantages or disadvantages of each solution on:

SQL Antipatterns Book
SQL Alchemy
Many other Q&A on StackExchange such as: How to model inheritance of two tables mysql

Because the question is too general, only a general answer can be answered -optimize for the most common kind of operations you are going to be performed later; or for the restrictions you may have (simplicity of the code, minimize data size on disk, minimize CPU usage, etc.), some of which will depend on the specific dataset you have and the database storage engine you are using.


TNS-12543: TNS:destination host unreachable I have a installed oracle 19c
but i am unable to login

Please refer the following screen shot

I am not unable to login
which user credential require to login

Have you looked at the alert log to verify that the database is running. Can you show your environment settings for ORACLE_SID, ORACLE_HOME, LD_LIBRARY_PATH, TNS_ADMIN and PATH? Do you have a listener running? Can you also show the contents of your listener.ora, sqlnet.ora and tnsnames.ora files? It could be that sqlplus can't find your database. Either using the listener or having ORACLE_SID set should get you to your database. You want to verify that the database and version of Sqlplus are both using the same Oracle Home.


How could I handle heavy load and consistency for product inventory in online retailers? I have posted that question on Stackoverflow and they suggest me to repost my question here, as it would more likely to get help from a DB community (regarding this issue).

Hope you guys don't mind.

We want to design an e-commerce application, and we are mental about consitent stock numbers. We don't want our customers finding out, after they have bought an item, that that item is out of stock, that's a big thing here. The average order here has about 60 different items, which will makes things even trickier.

Let's imagine these two scenarios:

1st Scenario:

1) Customer C1 opens the online store and find a product he/she wants to buy;

2) That product is shown as "in stock" (but the current stock is 1);

3) Customer C1 puts 1 item in the basket;

4) Customer C2 gets into the website and select the same item (put in the basket), which is still marked as "in stock" (stock is still 1);

5) Customer C1 goes to checkout and confirms his purchase and the application decreases the current stock for that item to 0;

6) Customer C2 keeps buying items, let's say 35 other distinct items (it took 20 minutes to customer c2 to select the items he wanted);

7) Customer C2 goes to checkout and confirms this purchase, but now, the first item he bought is no longer available (and we CAN NOT sell it);

8) The application warns customer C2 that the first item is no longer available and that he has to check his basket;

9) Customer C2 gets pissed and close the browser without buying anything.

2nd scenario (but I think it is unnecessarily complex and buggy):

1) Customer C1 opens the online store and find a product he/she wants to buy;

2) That product is shown as "in stock" (but the current stock is 1);

3) Customer C1 puts 1 item in the basket (and the application decreases the current stock for that item to 0);

4) Customer C2 gets into the website and see the item he/she wanted is out of stock;

5) Customer C2 leaves the website;

6) Customer C1 keeps buying items (the stock decreases for it of these items);

7) Customer C1 closes the browser;

8) Every now and then some batch routine kicks in to remove the items which had decreased the stock but didn't get bought/confirmed.

We have just a few distinct products, but we have been selling about 30.000.000 items by phone, some products get sold as much as 2.000.000 every day, so the concurrency in the row responsible for the stock of that product might get many updates at the same time, so it's important we get a good performance.

Those are usual scenario, but is there any design pattern which gives the user a better experience while keeping the stock numbers consistent and yet yield a great application performance?

Any help will be much appreciated.

Cheers

First off, taking a step back, do you really need to solve the inventory management problem on the front end?  Since you're selling large volumes of a relatively small set of products, it should be relatively easy to manage your inventory so that you are never out of stock or, if you are, it doesn't prevent you from fulfilling orders.  There is a great deal of literature and examples that deal with calculating safety stock which requires just a bit of statistics to follow.  It would make far more sense to me to focus your attention on giving the company the tools (if it doesn't already have them) to manage their inventory to prevent stock-out situations rather than trying to prevent them from happening in the sales portal.

That being said, I'm not quite sure that I follow your problem with the two scenarios you outline.  Even if the database performance was flawless, if you have only 1 of item A in stock and you can't sell an item if it's not in stock, then one of the two customers, by definition, one of the two potential customers is going to lose out.  If in the first scenario C2 is going to go away without buying anything if any of his 35 items are not in stock (which seems unlikely if he spent 20 minutes filling his cart), there is nothing you can do in the database to prevent that.  Your interface could potentially have some AJAX that alerts them while they're shopping that one of the items in their cart is out of stock much like StackExchange notifies you while you're entering an answer that someone else has entered an answer.  It's not at all clear to me, that telling C2 about the problem earlier is going to be beneficial-- if he's going to leave if he can't buy all 35 items in one transaction, he's going to leave no matter when you tell him that C1 bought the item.  Realistically, there is no way to design the system so as not to disappoint one of the two customers in that case.

It might help if you can explain a bit more about why your application and your customers are so sensitive to stock-out situations.  Most customers and most retailers are relatively accustomed to the fact that sometimes after placing an order they get notified that the retailer isn't going to be able to fulfill the order as quickly as they had expected and are given the option to cancel that part of their order, the whole order (assuming the remaining items haven't shipped yet), or to wait for the item to come back in to stock.  Assuming that you do something to notify customers while they're browsing that inventory is relatively low (i.e. Amazon will tell you "N items in stock" if you're looking at an item for which they only have a handful of stock left), most customers are reasonably understanding 20 minutes when they get to the checkout and are told that the item is now out of stock since they knew in advance that they needed to order quickly.  And most retailers are comfortable that even if they run out of stock of most popular items, they can still satisfy more requests than they have inventory in hand because they undoubtedly have new inventory arriving in the next day or two or they can rush an order for new inventory.


Move DB/LOG files - Detach/Attach - Problem I wanted to move the MDF/LDF file locations for a DB. From what I read I found I should use Detach/Attach and not just take the DB off line and relocate the files. I did just that and used the below to Re-Attach the DB.

CREATE DATABASE MyAdventureWorks
    ON (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Data.mdf'),
    (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Log.ldf')
    FOR ATTACH;
GO

Of course using my new file locations which used two separate volumes and not the location moving from.

BUUUT, all this did was put the files back on the original volume that I did not specify. It also put the DB in a READ ONLY state.

Any help would be appreciated.

Thanks

SQL Server 2016

The correct (and preferred) way in my opinion should be

Use alter database....MODIFY FILE - This will update system catalog.
Offline the database
Physically move the files to the new location that you specified in step 1.
Online the database

I have written a script to rename a database - db name and the files -logical and physical and you can use that logic to adjust it as per your requirements.

Note: Before doing any detach/attach, you should take a full backup of the database. See Bad Habits: Using MDF/LDF Files .. from Aaron Bertrand.


multi column index order for order by I have created the following multi column index

CREATE INDEX ON message_log (website,  in_use, insert_timestamp);

However when I perform the following query, it does not seem to be using the query as it's taking a very long time to complete.

select id from message_log where website = 'forgezilla' AND in_use = false ORDER BY insert_timestamp NULLS FIRST;

Can the multi column index be used for order by? if so did I create the ordering wrong when creating the index?

No, your given index cannot be used for the ORDER BY.  You can see this by doing set enable_sort=off and then explaining the query, you get a sort anyway because that is the only way PostgreSQL can get the job done.

But if you create the correct index:

CREATE INDEX ON message_log (website,  in_use, insert_timestamp NULLS FIRST);

then it will be used for the ORDER BY.


Transaction Log and mirroring - looking for dumbest explanation possible First of all I must admit that I struggle with concept of Transaction Log. I mean - I do understand that it is Log of all Transactions that happen on database, but when it comes to properly putting it into context within some task I obviously lack something. So, to anyone who will be answering the question - please feel free to expand on theory behind Transaction Log.

Main question is - I have SQL Server 2008 and 2 GB database that I need mirrored (has 12GB transaction log). If I wasn't mirroring that database I presume I could either switch to Simple mode or truncate the log after backup. But in this case - what should I do if I wish to keep that transaction log under control? As I understand - I need to keep that whole transaction log if I wish to be able to easily mirror database (just do Full backup).

Is there any way around this? Ideally I wish it was possible to do backup that keeps both MDF and LDF in 1 file every time and after backup is done Transaction Log (LDF) on database is reduced to 0. The problem with this scenario are incremental backups - if my first backup truncated log, I presume that second backup would need to reference first one if I want to do mirroring later (i.e. I would be stuck with keeping bunch of files instead of just one).

So - can anyone enlighten me on this subject? I understand that I am trying to fill in lots of holes here and that my proposed "solutions" may not be the best ones, but I would sincerely appreciate if somebody can push me in right direction on Transaction Logs, how they impact mirroring and best practices with those two.

The transaction log is a important method to restore your database in a specific time. If you have a large database > 500 GB and if you must restore your database from a full backup, this will cost very much time. Also if you full backup your database everytime, think about how long this backup could take.

A very easy concept for SQL Server can be:
Set Recover Model of your Database Full

Create a Maintenance Plan(1) in SQL Server:

Do FullBackup every Week maybe in D:\yourbackup\FullDBBackup.bak
Do Differential Backup every two days in D:\yourbackup\DiffBackup.bak
Do every 2 houers Backup your Transaction log in D:\Yourbackup\Tranlogbackup.trn

Create a Maintenance Plan(2) in SQL Server:

Delete all older Files 8 days from D:\yourBackup*.bak
Delete all older Files 3 Days from D:\yourBackup*.trn

In this case, you are able to recover your Database in a specifc time, very fast very easy.
SQL Server will automaticly manage your "Backup" files, older files will be deleted after your specific time range.

I would suggest that you read about SQL Server Transaction Log here:

http://www.sqlservercentral.com/articles/Design+and+Theory/63350/

For using the Maintenance Plans in SQL Server just ask BING / google :D

you should build a small test db and test this before you go in production


Disconnect from network without stopping the query I have an opposite problem to Does disconnecting the network stop a query?. I would like to launch a query and disconnect without waiting for results but I don't want the query to stop. Is it possible?

As the answer referenced in your question indicates, SQL Server will cancel the executing query and rollback the transaction when it detects the network connection has terminated. A SQL Server session needs a connected client to query return results and messages (informational, warning, and error messages).
You have a few options to maintain the connection/session for a long-running query, with and without an interactive client applciaton.
RDP into the SQL Server machine or jump box and execute the query from there. This will allow you to disconnect from the RDP session while the client application (e.g. SSMS, SQLCMD, ADS, etc.) continues to run. You can later reconnect to the same Remote Desktop session to review the results.
Create a SQL Server Agent job with a T-SQL, PowerShell, CmdExec, or SSIS job step to execute the query without an interactive client.
Create a Windows Task Scheduler task to execute the query, using SQLCMD, PowerShell, or other command-line tool.
Encapsulate the query in a stored procedure and execute it as a Service Broker activated proc. This requires a Service Broker enabled database and the proc code will need to capture results. See Asynchronous procedure execution for code examples of this technique and other considerations.
Without an interactive client, results and messages that won't be visible so make sure these are logged/captured according to your needs. Also ensure command-line applications return a non-zero exit code after errors so it's reported as an error by SQL Server Agent or Windows Task Scheduler.


Long running query with index seek I was tuning this query, the execution plan looks fine for me but based on the statistics the Lock waits time is very high .What should i do to fix this problem?

(@P0 bigint)
SELECT mgmtemails0_.Id AS Id3_0_,
   mgmtemails0_.BCCToId AS BCCToId3_0_,
   mgmtemails0_.BCCToMe AS BCCToMe3_0_,
   mgmtemails0_.CCToId AS CCToId3_0_,
   mgmtemails0_.CCToMe AS CCToMe3_0_,
   mgmtemails0_.CreatedDate AS CreatedD6_3_0_,
   mgmtemails0_.EmailBody AS EmailBody3_0_,
   mgmtemails0_.FromId AS FromId3_0_,
   mgmtemails0_.InstanceId AS InstanceId3_0_,
   mgmtemails0_.isDraft AS isDraft3_0_,
   mgmtemails0_.EmailTemplateId AS EmailTe17_3_0_,
   mgmtemails0_.Priority AS Priority3_0_,
   mgmtemails0_.ProfileId AS ProfileId3_0_,
   mgmtemails0_.ReplyToId AS ReplyToId3_0_,
   mgmtemails0_.ReplyToMe AS ReplyToMe3_0_,
   mgmtemails0_.SendToId AS SendToId3_0_,
   mgmtemails0_.Subject AS Subject3_0_,
   mgmtemails0_.UpdatedDate AS Updated16_3_0_
   FROM dbo.MgmtEmailSent mgmtemails0_
 WHERE mgmtemails0_.Id=@P0

Usage stats : Reads: 892,766 (892,766 seek) Writes:37,457

Op Stats : 901,699 singleton lookups; 332,618 scans/seeks; 0 deletes; 0 updates;

size : 3,034,470 rows; 3.5GB; 29.2MB LOB

Lock waits : Row lock waits: 681; total duration: 7,317 minutes; avg duration: 10 minutes;

these are the top wait stats

You're hitting two waits that are huge problems:

THREADPOOL: Running out of worker threads
RESOURCE_SEMAPHORE: Running out of memory for queries

There's another telling problem, here:

Long average waits on locking

You didn't post the hardware your server has, so I can't say if that's even close to adequate, or if your server settings are appropriate for it.

What's really lousy is that when queries are being blocked by a lock wait, all of the resources they grab to run (CPU, Memory, etc.) are held for the duration of the lock waits.

That means you're not only waiting on the locks to resolve, but then other queries totally unrelated to the blocking chain start piling up waiting for blocked queries to let go of resources.

If hardware is generally adequate, it's most likely a case of needing to tune queries and indexes to make modification queries as fast as possible so the blocking chains don't pile up and cause resource contention.


Two SQL Server databases setup with same .mdf and .ldf files In a SQL Server 2008 R2 instance two databases (the second one being a restore/copy of the first one) have been setup against the same .mdf and .ldf files.

How would I go about either deleting the second one or copying/moving the files for the second one without affecting the first (production!) db?

Update: Indeed one of them is Standby/Read-Only.

For a database in SQL Server, there's a setting called Auto-Close, which is meant to minimize the amount of resources used while SQL Server is running, but no users are actively doing things.

This setting is enabled by default in Express Edition instances, as those are usually running on developer workstations where performance isn't critical, and resources are needed for other applications.

When resources are released by this feature, the implementation includes closing the file handles associated with the database files (*.mdf, *.ldf, etc) on disk. This means that any other application can potentially open them, including the same or another instance of SQL Server.

In testing out this scenario, I found out there's no constraint inside SQL Server (at least in this particular version) that prevents attaching a database to the same set of files on disk, as long as they're readable. While this isn't a good/safe thing, of course, it's a relief to know that only one of the databases can be written to at a time, because the files will be exclusively locked by SQL Server when one of the databases is opened. The other database(s) will not be accessible.

To undo the situation:

Detach the database(s) you don't want. This removes the database from the instance, but leaves the files on disk alone (whereas DROP DATABASE does remove the files on disk). It may be necessary to restart the instance to get it to recognize that the database you want to keep is accessible.
Disable auto-close for the main database, to better prevent this from happening again. You may also want to disable it in the Model system database (if applicable) so new databases don't inherit this setting. For a production system, I recommend making sure auto-close is disabled on every database; I like to use Policy-Based Management to do the checking periodically, but there are lots of ways to go about it.


SQLCLR TVF that calls web service is getting error 401: Unauthorized I am calling a web service from a SQLCLR TVF in SQL Server 2008 R2.

On the IIS server this service has open permissions. When tested using GET or POST requests on a browser the web service works as expected.  However, when the function calls the service, it returns:

  The request failed with HTTP status 401: Unauthorized.

In the typical anecdotal way, when I run the TVF on my machine to access the service on my machine, it works.  (proof that code is correct)

This error occurs when using a SQL Server on the network accessing another ISS server on the network.  Both servers are in the same domain.

How to determine the missing permissions that prevent this TVF from working?

A couple basic SQL Server checks: Is your database set to trustworthy?  Is the assembly set to allow UNSAFE execution?

If that seems to be working, there are a couple of tutorials out on the web that might help.  If none of the tutorials out there help, then it sounds like a permissions issue.  (Unfortunately, that gets tricky, as this thread discussing Kerberos versus NTLM authentication shows.)

One other check--as Maumen mentioned--make sure that you have anonymous access setup for IIS.

Ultimately, though, it sounds like something you want to avoid, if you can.  The performance and memory implications, the difficulty getting all the accounts working, and decreasing all the security settings would make me a bit nervous.


Find problematic MySQL users and databases We have a MySQL 5.1 server that is an "open" service: anyone on campus can request a database. As such, we have several thousand databases on this one server.

As you might imagine, sometimes one user's database causes problems for all the rest. However, I am finding it difficult to tell which process or account is causing the problem. Using innotop shows me a bunch of queries that are taking a long time to finish, but that could be because other queries are using up all the server's resources.

What I want is an automated (or semi-automated) way to track down the databases or accounts that are using too many resources. For example, it would be nice to be able to run a command that would show me the 10 accounts or databases using the most disk I/O or the most memory.

Are there any tools out there that might help me do this?

(Note: an answer of "you shouldn't be using MySQL this way" although true is not going to help me as I have no choice in how this service is setup: I just have to maintain it.)

I helped a company find which databases were accounting for the greatest load. We collected slow-query logs, and then ran a report with pt-query-digest. Except we subtotaled by db, instead of by query fingerprint.

$ pt-query-digest --group-by db mysql-slow.log

There are lots of other options in this tool, so you could filter or sort on the total IOPS, or total number of temp tables, or total duration of queries, etc.

Once you identify the worst db, move it off your server until its owner learns to write more reasonable SQL queries.


Unknown column in a table Create schema Nevy;

create table sailor
(
  sid INT(5) NOT NULL,
  sname Varchar(20) NOT NULL,
  rating INT(5) NOT NULL,
  age INT(5) Not NULL,
  primary key (sid));

Create table reserve
(
  sid INT(5) NOT NULL,
  bid INT(5) NOT NULL,
  DAYT date NOT NULL,
  primary key (sid,bid,dayt),
  foreign key (bid) references boat(bid),
  foreign key (sid) references sailor(sid)
);

Create Table boat
(
  bid INT(5) NOT NULL,
  bname VARCHAR(10) NOT NULL,
  color VARCHAR(10) NOT NULL,
  primary key (bid) );

INSERT INTO nevy.sailor
values(22,'Dustin',7,45),(29,'Brutus',1,33),
  (31,'Lubber',8,55.5),(32,'Andy',8,25.5),
  (58,'Rusty',10,35),(64,'Horatio',7,35),
  (71,'zorba',10,16), (74,'Horatio',9,35),
  (85,'Art',3,25.5),(95,'Bob',3,63.5);

INSERT INTO nevy.boat
values(101,'interlake','blue'),(102,'Interlake','red'),
  (103,'Clipper','green'),(104,'Marine','Red');

INSERT INTO nevy.reserve
values (22,101,'1998-10-10'),(22,102,'1998-10-10'),
  (22,103,'1998-8-10'),(22,104,'1998-7-10'),
  (31,102,'1998-10-11'),(31,103,'1998-6-11'),
  (31,104,'1998-12-11'),(64,101,'1998-5-9'),
  (64,102,'1998-8-9'),(74,103,'1998-8-9');

select sname
from sailor
Where sailor.sid= reserve.sid
  AND bid=103;

When I execute the last select statement , it gives me the error

  unknown column: reserve.sid

I don't know why this error is being generated.

The error is being generated because you currently aren't joining your sailor table to reserve so the engine doesn't have any reference to the reserve table.

In order to make the reserve table available, you'll need to JOIN the tables on the sid column similar to:

select s.sname
from sailor s
inner join reserve r
  on s.sid= r.sid
where r.bid=103;

Or if you just want to return the sname that exists in the reserve table, then you could use EXISTS:

select s.sname
from sailor s
where exists (select 1
              from reserve r
              where bid = 103
                and s.sid = r.sid);

If you needed to include all 3 tables in a single query, then you would use:

select s.name
from sailor s
inner join reserve r
  on s.sid = r.sid
inner join boat b
  on r.bid = b.bid


Reset / force SQL Agent Next run date I need to test SQL Agent over a period of time, so need to change the system clock into the future quite often (and back)... don't worry it's only my local machine not a server :-)

The trouble is when I set it back again to the present date time, the next run date still ad-hears to the future times.

e.g. If I've put my clock into the future and the job is set to run every minute and it was last run in 11/01/2080 01:01:00 then it's next run is 11/01/2080 01:02:00, even if I set the clock back to the present.

It is understandable that this is the normal behaviour of SQL Server. Is there a way of resetting the next run date easily?

I've tried running the job manually but it has no effect on the next run time.

After the good work done by Aaron, I found that executing the result from my ReloadJobSchedules column below did the trick. For some reason I couldn't get the schedule to refresh to the correct next run date by manually updating msdb.dbo.sys.sysjobschedules.next_run_date .

SELECT  j.name AS JobName
        ,sch.name AS ScheduleName
        ,'EXECUTE msdb..sp_update_jobschedule @job_name = ''' + j.name + ''', @name = ''' + sch.name + '''' AS ReloadJobSchedules
FROM    msdb.dbo.sysjobschedules s
INNER JOIN
        msdb.dbo.sysschedules sch
        ON  s.schedule_id = sch.schedule_id
INNER JOIN
        msdb.dbo.sysjobs j
        ON s.job_id = j.job_id
WHERE   sch.[enabled] = 1
ORDER BY
        j.name


How To Grant View Definition of Object in SQL Azure The tsql syntax throws an error about "view" in SQL Azure, but functions on SQL Server:

GRANT VIEW ON OBJECT dbo::foo TO [bar]

Can access be granted in SQL Azure to view the definition of an object outside of db_owner?

Your syntax is incorrect.  It should be

GRANT VIEW DEFINITION ON <object/schema> TO <user/role>

Example

GRANT VIEW DEFINITION ON SCHEMA::dbo TO [bar]

GRANT VIEW DEFINITION ON OBJECT::dbo.MyView TO [barRole]


MongoDB WiredTiger compression stats I'm using MongoDB 3.0.4 with WiredTiger storage engine. I was looking at collection stats and I'm seeking explanation on compression stats fields.
What does "page written failed to compress" indicate? Is it an error I should be concerned about with regards to data integrity and why does it happen?

Sample stats output from one of my collections:

"compression" : {
                        "raw compression call failed, no additional data available" : 0,
                        "raw compression call failed, additional data available" : 0,
                        "raw compression call succeeded" : 0,
                        "compressed pages read" : 205883,
                        "compressed pages written" : 306168,
                        "page written failed to compress" : 3147,
                        "page written was too small to compress" : 51992
                }

What does "page written failed to compress" indicate?

WiredTiger's general strategy is to only compress pages on-disk where there is a storage benefit.

The "page written failed to compress" counter is incremented when compression was attempted but didn't result in an on-disk storage saving (so a page was stored uncompressed).

Another metric for pages that are stored uncompressed is "page written was too small to compress". In this case, the size of a page was considered too small to attempt compression.

The commonly expected case is that pages can be stored compressed, which increments the "compressed pages written" metric.

  Is it an error I should be concerned about with regards to data integrity and why does it happen?

These storage metrics are informational counters and expected behaviour (i.e. unrelated to data integrity).

There isn't any obvious actionable outcome based on these metrics, but if a collection wasn't getting much overall benefit from compression the counters might provide insight into how often pages aren't compressed (as compared to compressible pages that don't have a significant level of compression).


Oracle 11, incremental backup goes wrong Got a pretty big instance  on a one-week backup cycle with an incremental (differential) strategy .. or rather, that's what it was supposed to be. Somehow the daily differentials seem to come out as cumulative!? The size of our instance means that this is a bit of a problem - daily backup window in excess of 3 hours is not acceptable at the end of the week.

Our schedule:
Once every week we do an

backup incremental level 0
  as copy tag 'sometag'
  skip readonly
  database;

and once a day

backup incremental level 1
  for recover of copy tag 'sometag'
  database plus archivelog;

We've configured block-change-tracking for the database

The problem is, that the result is a cumulative incremental backup, which means an increasing backup-window every day, with a reset in the weekends.

What are we doing wrong?

Thoughts:

Change Tracking is getting screwed up?
Strategy incompatible with
image copies?

You are doing a differential incremental backup rather than cumulative, but the syntax for recover of copy tag 'sometag' indicates that you plan to incrementally update an image file backup.  Phil was on the right tack with the missing RECOVER COPY OF DATABASE WITH TAG 'sometag'.  Since you are not running this each incremental must be able to recover the un-updated tag 'sometag' causing it to grow each day as though CUMULATIVE were specified.

See Backup Sets and Image Copies in the Concepts Guide and see  Incrementally Updating Backups in the Backup and Recovery User's Guide (11.2).  If your intention was to use incrementally updated image copies you will want something more like this:

  RUN
  {
    RECOVER COPY OF DATABASE
      WITH TAG 'incr_update';
    BACKUP
      INCREMENTAL LEVEL 1
      FOR RECOVER OF COPY WITH TAG 'incr_update'
      DATABASE;
  }

If you didn't intend to do this, then simply remove the for recover... syntax.


New SQL Server 2016 install -- confused logging on via the command line I just finished installing SQL Server 2016 (The full thing -- not just Express).   I specified Windows authentication.  During the install in the install wizard, in the Database Engine Configuration node and on the Server Configuration tab, I simply clicked the Add Current User button.  I didn't add a specific user for administration at install time.

Before posting, I also took a look at this:  https://msdn.microsoft.com/en-us/library/ms180944.aspx

Anyway, when I run this from a command prompt:

C:\Users\dogzilla>sqlcmd -S dogzilla -U dogzilla

I get this error:

Password: Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'dogzilla'..

dogzilla is the account I use to log into Windows with.  I know the password since I'm able to write this to you.

What's going on with the authentication?  Does the dogzilla password need to be set in SQL Server?  How can I change it there?

Thanks!

If you're connecting to a local instance with Windows authentication you specify -E (or nothing, it's the default). Specifying -U will attempt to login with a SQL login.

Here's an example where I connect to a local named instance using Windows auth and run a simple command.


Tuning SQL Server Performance While profiling my application against SQL Server 2008 running against a JDBC Application most of the time consumed were by following

COMMIT TRAN
FETCH API_CURSOR & exec sp_cursorfetch

I cannot reduce the frequency of commits in my app - so is there any setting/configuration like increasing the redo log buffers or log file size that can provide relief ?

And should I look at avoiding server side cursors as it eats up most of the time?

But since most of those statements involve blobs probably batch returning it makes more sense.

Please advice.

Cursors may be the right solution to a given problem, but usually not in SQL Server.  Try to use set-based solutions instead of an iterative one if at all posible.  Worst case scenario, you can look at using an iterative SQL solution.  I've done that before and ended up an order of magnitude faster than an equivalent cursor implementation.

You can return BLOBs from the DB in normal SQL statements so you don't need a cursor for that - granted, if your application can't handle the incoming data stream, you might be changing cursor waits for async_network_io waits (meaning SQL is done, but your app is too slow - Access does this all the time).

If you post more details about what your app is doing or more specifics, we might be able to get more focused advice.


Backup Plan on Always On using Maintenance Wizard doing Tlogs on Primary and Secondary I have set my back up preference to primary and the secondary is not readable. Then I did the following:

Set up the same maintenance plan on all replicas in the AG through the maintenance wizard.
Configure the Backup Preference plan is set to Primary on the availability group.
Enable the 'For availability databases, ignore Replica Priority for Backup and Backup on Primary Settings' check box and run your maintenance job.
Now when I ran the Tlog backup on the secondary it generated a Tlog back up which was not supposed to ...

Is this normal?

If you tick the "For availability databases, ignore replica priority for backup and backup on primary settings" option, then the maintenance plan jobs will perform the backup on a secondary (or any replica the job is executed on).

This option is used to ignore the AUTOMATED_BACKUP_PREFERENCE option for the Availability Group. Since your preference is set to Primary and you don't want backups on the secondary, disable the "For availability databases, ignore replica priority for backup and backup on primary settings" option and your maintenance plan jobs will not perform a backup on the secondary replicas.

Keep in mind that the backup preference option in the Availability Group will not prevent backups from being taken on a secondary. This option simply ensures that sys.fn_hadr_backup_is_preferred_replica will return the correct value, according to the configured preferences, on each replica. It is up to you to ensure this setting is validated and honoured by your backup application.

Maintenance plans generate a T-SQL command under the hood that has an IF statement that checks this function, unless you have enabled "For availability databases, ignore replica priority for backup and backup on primary settings".

Further Info: https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/02/maintenance-plan-does-not-backup-database-or-log-of-database-defined-in-availability-group/

P.S. I would recommend taking a look at Ola Hallengren's excellent SQL Maintenance Solution as an alternative to the built-in Maintenance Plans. Ola's solution has built-in intelligence for handling Availability Group databases in accordance with your backup preferences, as well as many other useful features.


What is the safest way to switch the binlog format at runtime? Because of the following warning in mysqld.log:

  [Warning] Unsafe statement written to the binary log using statement
  format since BINLOG_FORMAT = STATEMENT. The statement is unsafe
  because it uses a LIMIT clause. This is unsafe because the set of rows
  included cannot be predicted.

I want to switch the replication format to MIXED.

But according to the MySQL document:

  Switching the replication format at runtime is not recommended when
  any temporary tables exist, because temporary tables are logged only
  when using statement-based replication, whereas with row-based
  replication they are not logged.

So, the question is how can I identify if there is any temporary tables exist to switch the binary log format safely?

Since a binlog will have a specific format at the moment you do this, you may decide not to gamble with the two formats together although MySQL (eh Oracle [still can't roll off my tongue]) built this feature.

To play it totally safe without a mysql restart, try the following:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

This will leave the last binlog in the 'MIXED' format. The penultimiate (next to last) binlog exists merely bring closure the last binlog that was in the previous format.

All existing sessions prior to the first FLUSH LOGS; will start writing in the last binlog once UNLOCK TABLES; is executed.

Give it a Try !!!

CAVEAT

Giving credit where credit is due, my answer is really piggybacking off of @Jonathan's answer. I just close and open binlogs on top of that. He gets a +1 for bringing this out first.

UPDATE 2011-10-12 13:58 EDT

If you do this to an active Master and there are one or more Slaves replicating from that Master, you need to be concerned about the relay logs being in the new format as well. Here is what you can do:

On the Slave, run STOP SLAVE;

On the Master run these:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

On the Slave, run START SLAVE;

Running STOP SLAVE; and START SLAVE; rotates the relay logs and causes the new entries to be replicated whichever format it comes. You may want to apply the binlog_format change in the slave as well.


A lot of data in the table is only accessed for 4 days or so, how to handle it afterwards? We have a table that accumulates messages from local air traffic. It's approximately 5k-20k data rows a day that will only be actively accessed for a week at most. Afterwards it's just a log in case of some emergency (like inspection) and basically a very dead chunk of data. Like, archaeologically dead.

What is the optimal way to deal with it? Our "DB guy" argues that "it's all indexed" and there is no harm in having millions upon millions of accumulated unused records, but I am not really convinced. Is this really a good idea to allow the table to grow without limits?

UPD: We are using Oracle 11. The access patterns are like this:

1) A couple of applications keep track of the new rows added, querying on id > lastId and indexed TM interval of last day each approximately  2-3 times a minute

2) A couple of rows per minute are added to this table and then actively parsed into other tables

3) every time a user selects a message or does a search (I guess 10-20 times a minute per app running) a query is done into this table with custom where on approx. 10 indexed fields.

Yes, there is a cost to having lots of cold data in actively-queried tables.  One obvious case is that bigger tables have deeper indexes (i.e. more pages between the root and the leaves) so require more IO to read and write.

I'd suggest you use partitioning.  Keep the active partitions in the current table and swap the others out.  Ideally you'd then use a maintenance task to move the historical data to a separate DB for archive.  Make it clear to your external users what the time cut-off is and allow them to query either accordingly.


Keep rows only if Date exists in both table This is my first time here, so please bear with me.

Assuming i have 2 table,

Table 1
Date | Closing
1/1/2013 | 1
2/1/2013 | 2

Table 2
Date | Closing
1/1/2013 | 3
3/1/2013 | 4

What sql statements allow me to keep rows such that only Date that exists in both table remains?

End Result:

Table 1
Date | Closing
1/1/2013 | 1

Table 2
Date | Closing
1/1/2013 | 3

If you want to perform a DELETE action:

You'll delete all rows in table1 not present in table2 (by date field) and viceversa

DELETE FROM table1
WHERE NOT EXISTS(
    SELECT 'X'
    FROM table2
    WHERE table1.date = table2.date
)

and

DELETE FROM table2
WHERE NOT EXISTS(
    SELECT 'X'
    FROM table1
    WHERE table2.date = table1.date
)

If you want to perform a SELECT action

You'll show only rows presents in table1 and table2

SELECT * FROM table1
WHERE EXISTS(
    SELECT 'X'
    FROM table2
    WHERE table1.date = table2.date
)
UNION ALL
SELECT * FROM table2
WHERE EXISTS(
    SELECT 'X'
    FROM table1
    WHERE table2.date = table1.date
)


What's a good number for IOPS (RDS)? I recently verified the IOPS in my RDS instances IOPS and I believe that it is too high. What is considered a good number/average for IOPS?

Are there any tools to check integrity and problems in a Mysql instance to help lower the IOPS, better than what is seen below?

A good number is rarely peaking to the available IOPS of the hardware or Cloud provider.

Your graph show rarely getting over 25.  That is a very low number.

AWS carves up machines and sells you different amounts of CPU and IO.  I can't say what your max is.

If you are hitting limits of CPU or IO with MySQL, then the first thing to do is to find the slowest query.  It may need a better index, probably a 'composite' index.  Or it may need to be reformulated in some simple way.  Only after that, I would look at tuning.  Then at hardware or AWS plan.


Drawing an entity-relationship diagram for a store business context I have an entity called Users, with the attributes Id, Name, and Phone No. The objective is to develop an entity-relationship diagram (ERD for brevity) where Users might be Store Cashiers, and also Customers.

Cashiers have the attributes Position and Work Hours.
Customers have the attributes Credit History and Amount.
A Cashier can also be a Customer.
Only a Cashier can add a Customer to the Store's database.

The question

It is easy to develop the ERD where I can include Roles, but then how can I enforce the following condition in the ERD?

Only a Cashier can add a Customer to the Store's database.

First of all: (A Cashier can also be a Customer)
In this case, you can use a 1-to-1 relationship named "CAN ALSO BE" between Cashier Entity and Customer Entity. And also transmit Customer P.K as F.K to Cashier. This F.K (Foreign key of Customer in Cashier) can be null too and should be UNIQUE.

Edit: (based on @RDFozz notice)
We can use the similar User_ID in both Cashier and Customer to show that a Cashier can also be a Customer.

Secondly: (Only a Cashier can add a Customer to the Store's database)
Meaning that each Customer has a creator and should be a Cashier. So you have a one-to-many relationship named "CREATOR" between Cashier and Customer. So, you should transmit Cashier P.K as F.K to Customer. This F.K (Foreign key of Cashier in Customer that shows creator id) should not be null.

Thirdly: (Only a Cashier can add a Customer to the Store's database)
We can not put ALL Business Logic of our project into ERD. For this reason we creates Business Layer or Business Logic Layer (BLL) or sometimes we write some Stored Procedures or Functions with Enhanced SQLs (those have IF, Loops and etc.)

If we put P.K of Cashier as F.K into Customer and prevent it to be null, that's enough. Your Insert into Customer should has a Cashier ID to be done.