PLSQL: Package stored procedure cannot call exe imediate statement I'm working with oracle 11g R2.

Issue:

I am unable to preform dynamic SQL in SchemaB package against SchemaA tables. SchemaB is being called by the owner of SchemaB.

Background:

I have a package/procedure that has the rights to preform a selection statement on another schema's table.

So I can call the below with no issues.

select * from SchemaA.TableA where blah

I need to be able to run this call from dynamic SQL, because the table from SchemaA could be any of 10. I created the below SQL. I get an error

  ORA-01031: insufficient privilegeswhen I run the same statement but withexecute immediate

Code:

v_query varchar2(2000);
v_query := 'select id, value1, value2 from SchemaA. ' || TableName || ' where id = ' || id;

execute immediate v_query ;

I know that the package does not get the user roles, so I even put the following code into the signature of my package AUTHID CURRENT_USER ; which changed nothing.

What other permissions do I need to set in order to do this?

OR if you have another solution for dynamic table selection, that would be great too.

You're doing something wrong. Test case follows...

Create schemaa and schemab:

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 21 14:28:47 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> create user schemaa identified by "schemaa";
SP2-0640: Not connected
SQL> conn / as sysdba
Connected.
SQL> create user schemaa identified by "schemaa";

User created.

SQL> grant connect, resource to schemaa;

Grant succeeded.

SQL> create user schemab identified by schemab;

User created.

SQL> grant connect, resource to schemab;

Grant succeeded.

Login as schemaa, create two tables & grant select on the first to schemab, but don't grant anything on the 2nd table:

SQL> conn schemaa/schemaa;
Connected.

SQL> create table satable ( aaa integer ) ;

Table created.

SQL> insert into satable values ( 1 );

1 row created.

SQL> grant select on satable to schemab;

Grant succeeded.

SQL> create table satablenograntsforb( aaa integer ) ;

Table created.

Login as schemab, create the stored procedure & then test it with the table we have permission to select from:

SQL> conn schemab/schemab;
Connected.

SQL> CREATE OR REPLACE PROCEDURE schemabproc(TableName IN VARCHAR2)
  2  IS
  3  v_query varchar2(2000);
  4  BEGIN
  5
  6  v_query := 'select aaa from schemaa. ' || TableName ;
  7  execute immediate v_query ;
  8
  9  END;
 10  /

Procedure created.

SQL> exec schemabproc('satable');

PL/SQL procedure successfully completed.

SQL>

As you can see, it executed ok.

Let's try on the table we don't have permission on:

SQL> exec schemabproc('satablenograntsforb');
BEGIN schemabproc('satablenograntsforb'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCHEMAB.SCHEMABPROC", line 7
ORA-06512: at line 1

Let's grant and re-test:

SQL> conn schemaa/schemaa
Connected.

SQL> grant select on satablenograntsforb to schemab;

Grant succeeded.

Now we should be able to select from the 2nd table without error:

SQL> conn schemab/schemab

SQL> exec schemabproc('satablenograntsforb');

PL/SQL procedure successfully completed.

SQL>

Conclusion: There's something else happening. Note that the procedure above runs with AUTHID DEFINER, which is the default.


Result set to eventvwr I'm looking for a solution to the following case:

I have a trace(only captures queries above 5000ms duration, which is about 20 per hour) running in MSSQL which I can query by using:

SELECT TextData, Duration, CPU, Reads, Writes
FROM fn_trace_gettable(N'C:\temp\TRACE.trc', DEFAULT)
WHERE EndTime > DATEADD(MI,-15,GETDATE()) AND Reads < 10 AND Writes < 10

I want this query to run this query every 15 minutes and the results should be written(each result in it's own event log) to the eventvwr using xp_logevent. Our monitor software will then trigger on the results of the Event Viewer.

I tried to achieve this by putting the results in a temp table, and then using a while to loop through each result. Because there are no ID's in the data, I can't keep count of the rows. Should I look at a SELECT TOP 1 query which deletes the row after processing to the Event viewer? Or is there an easier way?

Could someone point me in the right direction?
PS: This should work both on SQL 2008 R2 and SQL 2012

A CURSOR with the above query as it's definition would allow you to insert the records into the event log one-by-one.

Another option you may want to look at is querying sys.dm_exec_query_stats:

SELECT TOP 10
    st.text,
    qs.max_logical_writes,
    qs.max_logical_reads,
    qs.max_elapsed_time,
    deqp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text( qs.sql_handle ) AS st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS deqp
ORDER BY
    qs.max_elapsed_time DESC

This query will return the top 10 longest running queries along with their plans. This is just a sample, it could easily be tweaked, and may point you in another direction.


Where clause no give expected result where clause not show expected result.

declare @PracticeId bigint =1000
select * from insurance i
WHERE ISNULL(I.DELETED,0)=0
    and (
          (  @PracticeId =1000 and(I.PracticeId=@PracticeId and I.PracticeId<>0) )
        OR( (I.PracticeId=0 OR I.PracticeId=@PracticeId) and I.PracticeId<>1000 )
    )

I want show result when

  @practiceid=1000

than only 1000 practices show and when

  practiceid <>1000

than show practiceid in (0,@practiceid)

but when i pass @practiceid=1000 than its also get 0 practiceid

Your logic problem occurs in the way you are ORing i.PracticeId=0 into the criteria. That row is ORed so that it's always true. Effectively, you're using OR when you want it to act like ELSE (that's not how OR works).

I believe you want to add in this AND @PracticeId<>1000 to get the desired results:

declare @PracticeId bigint =1000
select * from insurance i
WHERE ISNULL(I.DELETED,0)=0
    and (
          (  @PracticeId =1000 and(I.PracticeId=@PracticeId and I.PracticeId<>0) )
        OR( ((i.PracticeId=0 AND @PracticeId<>1000) OR I.PracticeId=@PracticeId) and I.PracticeId<>1000 )
    )

Note: this query will probably perform terribly and be very slow due to the way the criteria are ORed together. Solving this with dynamic SQL or an IF/ELSE block would likely simplify your logic and perform much better.


Use MDX to select Dimension-Members instead of Measure Values Is it possible, with MDX, to select Dimension Members instead of Measure Values?

Here is what I currently do (simplified):

SELECT
    {[Time]...} ON 0,
    {[Gender]...} ON 1
FROM [Cube];

Result:

  2008 2009
M  1    -
W  -    2

This is my desired result (in my dataset the returned cell amount would be much lower than in the above result):

Gender Time
M      2008
W      2009
W      2009

Or alternatively:

Gender Time Measure
M      2008    1
W      2009    2

In my project I need to select from 5 different dimension axes, some of them containing several hundred members, leading to a cellset of about 8,000,000 cells, which executes very slow (about 30 seconds in my setup). I already tried to improve the performance with the NONEMPTY function but it is still too slow. My desired results would only return about 400,000 cells (querying the SQL source table requires only 2 seconds, so this is the speed at which the desired data can be retrieved in theory).

Obviously, in this special situation querying the relational source table would be much faster, however, the OLAP server should be single-point-of-truth so I cannot just query the source database.

Are there any MDX functions that allow this kind of query or do you have any other tipps on how to improve the performance of aforementioned query?

You can achieve your desired results by using a crossjoin (the * notation) and putting the dimensions on the columns axis. Here's an example based upon the Adventure Works SSAS DB.

select {} on 0,
{
[Customer].[Gender].[Gender].members
*
[Date].[Calendar Year].[Calendar Year].members
}  on 1
from [Adventure Works]


Converting jsonb values to array I have a table with a column of type jsonb called attributes.

I have rows with values like -

{
    "id1": "something1",
    "id2": "something2",
    "id3": "something3",
}

and I want to convert them into -

{
    "id1": ["something1"],
    "id2": ["something2"],
    "id3": ["something3"],
}

How can I do that?

You would have to unroll it with jsonb_to_record or write a function in plv8,

SELECT jsonb_build_object(
  'id1', jsonb_build_array(id1),
  'id2', jsonb_build_array(id2),
  'id3', jsonb_build_array(id3)
)
FROM (VALUES ($${
  "id1": "something1",
  "id2": "something2",
  "id3": "something3"
}$$::jsonb)) AS t(j)
CROSS JOIN jsonb_to_record(j)
  AS t2(id1 text, id2 text, id3 text);


Assigning permissions for sysmail_update_account_sp I have a Microsoft SQL Server 2014. The mail system is configured via DBMail and
there is one stored proc in main web app (having its own database) which executes msdb's sysmail_update_account_sp by selecting the appropriate profile using msdb.dbo.sysmail_account.

The problem I am facing is the web application has a SQL login and I want to switch context and execute sysmail_update_account_sp.

EXEC AS USER = @UserName

    SET @AccountNr = ( SELECT TOP 1 account_id FROM msdb.dbo.sysmail_account WHERE name = @DatabaseName ORDER BY account_id )

    IF @AccountNr > 0

    EXECUTE msdb.dbo.sysmail_update_account_sp
                @account_id = @AccountNr
                , @email_address = @ParamSMTPFrom
                , @account_name = @DatabaseName
                , @display_name = @DatabaseName
                , @replyto_address = @ParamSMTPFrom
                , @description = @EmailAccountDescription
                , @mailserver_name = @ParamWebAppSmtpServer
                --[ , [ @mailserver_type = ] 'server_type' ] 'optional only SMTP supported'
                , @port = @ParamWebAppSMTPPort
                , @username = @ParamWebAppSMTPUser
                , @password = @ParamWebAppSMTPPwd
                , @use_default_credentials = 0 -- we will always have nor domain credientials @DefaultCredentials
                --use account name and password
                --not databse credentials
                , @enable_ssl = @SSL -- bit 1 for use ssl
REVERT

Error occurring is:

  Cannot execute as the database principal because the principal
  "SERVERNAME\dbMailUser" does not exist, this type of principal cannot
  be impersonated, or you do not have permission

Things I checked:

SERVERNAME\dbMailUser has DatabaseMailUserRole permission in msdb currently.
If I add the executing stored proc user (from web app) as sysadmin,I don't have to switch or anything. But I want to limit the permission to it's minimum.

How can I execute as a different user (SQL or Windows Login) by switching context properly? The current SQL login is a database owner.

The requirement is the login should be able to configure DBMail from a stored procedure and able to send mails (the DatabaseMailUserRole), without it being in the sysadmin role.

Am I in the right direction, regarding my understanding of how DBMail configuration works?

This request is very similar to, if not the same as, another question on here (DBA.StackExchange) regarding the need for very granular elevated permissions. I provided working code in my answer, and that could be used here as well:

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

The overall concept is the same: give the Stored Procedure itself the required permission by being associated (via the Certificate) with a Login that has the proper permission. The only difference would be that instead of granting VIEW SERVER STATE TO [MrDoStuff], you would probably just add [MrDoStuff] to the sysadmin fixed server-level role.

One difference, though, between this question and the "What minimum permissions..." question is that the other question simply needed access to a DMV and not to any code objects outside of the code being signed. It just so happens that signatures do not, by default, transfer to code called by the signed code. But there is a way of allowing this to happen: counter-sign the external code being called. Counter-signing allows code executed by signed-code to transfer the signature without actually being signed itself (and so the external code cannot be called independently). Counter signing is handled by the same ADD SIGNATURE function that was used to sign the initial Stored Procedure:

Restore the Certificate into the [msdb] database
Counter-sign the dbo.sysmail_update_account_sp Stored Procedure

Continuing the example code from that other answer, the additional steps should be:

USE [msdb];

CREATE CERTIFICATE [DoStuffCert]
    FROM FILE = 'C:\temp\ViewSqlAgentStatus.CER'
    WITH PRIVATE KEY (
        FILE = 'C:\temp\ViewSqlAgentStatus.PVK',
        DECRYPTION BY PASSWORD = 'DontStartNoneWontBeNone',
        ENCRYPTION BY PASSWORD = 'W0rdUp,Yo!'
    );

ADD COUNTER SIGNATURE TO dbo.sysmail_update_account_sp
    BY CERTIFICATE [DoStuffCert] WITH PASSWORD = 'W0rdUp,Yo!';

It is possible that if sysmail_update_account_sp executes any stored procedures, then they might also need to be counter-signed.


Equivalent of utf8_general_ci in Postgres/ICU? In MySQL there is a collation utf8_general_ci which provides case-insensitive comparisons in a variety of languages. For example, these are all 1 (true):
SELECT 'ı' = 'I' SOLLATE 'utf8_general_ci';
SELECT 'i' = 'I' COLLATE 'utf8_general_ci';
SELECT 'ä' = 'Ä' COLLATE 'utf8_general_ci';

Can I define a similar collation using PostgreSQL's ICU?
I tried
CREATE COLLATION "undefined_ci_nondet_old" (
  PROVIDER = 'icu',
  LOCALE = "@colStrength=secondary",
  DETERMINISTIC = false
);

But that doesn't seem to include the Turkish I/ı conversion:
SELECT 'ı' = 'I' COLLATE undefined_ci_nondet_old; -- false

The dotless I is a special case. It's processed by the ICU collation service with rules that depend on the language.
If the locale refered to the Turkish or Azerbaijani languages, it would produce the result that speakers of these languages might expect (that is, i and ı are two different letters with İ and I being their respective uppercase counterparts; cross-comparisons return false). Otherwise the result is normally that i is the lowercase version of I, whereas ı is not.

postgres=# CREATE COLLATION "undefined_ci_nondet_old" (
  PROVIDER = 'icu',
  LOCALE = 'tr@colStrength=secondary',
  DETERMINISTIC = false
);
CREATE COLLATION

postgres=# SELECT 'ı' = 'I' COLLATE undefined_ci_nondet_old;
 ?column?
----------
 t
(1 row)

postgres=# select 'i'='İ' COLLATE undefined_ci_nondet_old;
 ?column?
----------
 t
(1 row)

postgres=# select 'i'='I' COLLATE undefined_ci_nondet_old;
 ?column?
----------
 f
(1 row)

postgres=# select 'ı'='İ' COLLATE undefined_ci_nondet_old;
 ?column?
----------
 f
(1 row)


How to insert the gathered dates into another table using INSERT SELECT in MySQL? insert into rfdate (date)
select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
(select 0 t0 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t4
where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday')

Above code doesn't work.

Code below gathers the needed date irregardless of where database you are in since it's just date. You can try it too even without using a table with the columns presented.

select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
(select 0 t0 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday');

PROPOSED QUERY

insert into rfdate (date)
select selected_date from
(
    select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date
    from
        (select 0 t0 union select 1 union select 2 union select 3 union select 4
        union select 5 union select 6 union select 7 union select 8 union select 9) t0,
        (select 0 t1 union select 1 union select 2 union select 3 union select 4
        union select 5 union select 6 union select 7 union select 8 union select 9) t1,
        (select 0 t2 union select 1 union select 2 union select 3 union select 4
        union select 5 union select 6 union select 7 union select 8 union select 9) t2,
        (select 0 t3 union select 1 union select 2 union select 3 union select 4
        union select 5 union select 6 union select 7 union select 8 union select 9) t3,
        (select 0 t4 union select 1 union select 2 union select 3 union select 4
        union select 5 union select 6 union select 7 union select 8 union select 9) t4
) A where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday');

PROOF THAT IT WORKS

mysql> DROP DATABASE jeffvalerio;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE jeffvalerio;
Query OK, 1 row affected (0.00 sec)

mysql> USE jeffvalerio
Database changed
mysql> CREATE TABLE rfdate
    -> (
    ->     id int not null auto_increment,
    ->     date date,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into rfdate (date)
    -> select selected_date from
    -> (
    ->     select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date
    ->     from
    ->         (select 0 t0 union select 1 union select 2 union select 3 union select 4
    ->         union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    ->         (select 0 t1 union select 1 union select 2 union select 3 union select 4
    ->         union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    ->         (select 0 t2 union select 1 union select 2 union select 3 union select 4
    ->         union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    ->         (select 0 t3 union select 1 union select 2 union select 3 union select 4
    ->         union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    ->         (select 0 t4 union select 1 union select 2 union select 3 union select 4
    ->         union select 5 union select 6 union select 7 union select 8 union select 9) t4
    -> ) A where selected_date between '2015-02-01' and '2015-02-28'
    -> and dayname(selected_date) in ('Monday', 'Tuesday');
Query OK, 8 rows affected (0.09 sec)
Records: 8  Duplicates: 0  Warnings: 0

Here is the table's contents

mysql> select * from rfdate;
+----+------------+
| id | date       |
+----+------------+
|  1 | 2015-02-02 |
|  2 | 2015-02-03 |
|  3 | 2015-02-09 |
|  4 | 2015-02-10 |
|  5 | 2015-02-16 |
|  6 | 2015-02-17 |
|  7 | 2015-02-23 |
|  8 | 2015-02-24 |
+----+------------+
8 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Connecting to SQL Server with SQL Server Management Studio I've just install SQL Server 2017, it showed this window:

When I open SQL Server Management Studio it has default Server Name as DESKTOP-6ECNPU1\matt which is the SQL Administrator value of the server. I'm confused because I thought the value that would be needed is the Instance Name MSSQLSERVER. I have connected to the database but I'm not sure if it's the right one, DESKTOP-6ECNPU1\matt could be a previously installed SQL Server that came with Visual Studio. So my question, have I connected to the SQL Server I just installed?

The default server name from your screenshot is showing DESKTOP-6ECNPU1, not DESKTOP-6ECNPU1\matt, the latter is the user you are connecting into sql server with.

The sql server browser service is used to route the connection to the appropriate instance on the server. The default sql server instance is installed on port 1433. If you were to connect to a named instance, then the name will be translated to the port that the instance is running on, otherwise if only the machine name is specified you get routed to the default instance.

Based on your screenshot that installation is the default instance (because instance name is MSSQLSERVER), so if you only specify the name of the machine, you will be connecting
to that 2017 sql server instance you installed.

If you want to double check even further, see James answer and run that command, also see @@servicename


Stats Maintenance after Restoring to Newer SQL Server What's the ideal (or necessary) sequence of call when restoring a SQL Server 2012 backup onto a SQL Server 2014 instance and updating the compatibility level?

-- Update Statistics for all tables
EXEC sp_MSforEachtable 'Update Statistics ? with FULLSCAN';

-- Update page and row counts in catalogviews
DBCC UPDATEUSAGE('MY_DATABASE') ;

-- Change DB Compatibility level
EXEC sp_dbcmptlevel 'MY_DATABASE', '120';

I'm not sure if the db compatibility level effects the stats or simple the engine on the server. If the db compatibility level influences stats than it should be done before update stats. Also unclear if UPDATEUSAGE is still recommend and if it should be before update stats as well.

I would recommend to Update statistics as well as compatibility level to the latest version of sql server (provided your application supports it and you have done proper full business life cycle testing). I have written about full list of steps when moving from lower version to higher version

As a side note, please don't use sp_MSforEachtable - Its not supported. There is a clever way of doing it.

Compatibility level matters - see What is the actual behavior of compatibility level 80? and Clinging to old compatibility levels form Aaron Bertrand


How to grant drop table/function privileges to a role in PostgreSQL? I want to grant drop privileges on all tables and functions (not only those owned by the user) in certain schema of a specific database to a specific role. However, GRANT ALL PRIVILEGES is not enough and I didn't find how to do without making the role a superuser - superuser has rights over other databases on the same server, which is not what I want. I wouldn't mind superuser privileges limited to a specific database, but I'm not sure how to do it.

My code:

CREATE USER _administrator PASSWORD 'pwd12345';
CREATE ROLE administrator NOLOGIN ADMIN _administrator;

GRANT ALL PRIVILEGES ON DATABASE "myDB" TO administrator;
GRANT ALL PRIVILEGES ON SCHEMA public TO administrator;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO administrator;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO administrator;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO administrator;

administrator is the group of myDB database administrators, _administrator is the most powerful role my client app will be able to log in as.

What I did miss or do wrong?

Only the owner (and superusers) can drop objects. Per documentation:

  The right to drop an object, or to alter its definition in any way, is
  not treated as a grantable privilege; it is inherent in the owner, and
  cannot be granted or revoked. (However, a similar effect can be
  obtained by granting or revoking membership in the role that owns the
  object; see below.) The owner implicitly has all grant options for the
  object, too.

So, make administrator own such objects that users should be able to drop.

ALTER FUNCTION foo() OWNER TO administrator;
ALTER TABLE foo      OWNER TO administrator;

And you remembered to actually grant group membership, right?

GRANT administrator TO _administrator;


why Threads_connected falls down automatically in some cases How can I monitoring, when Threads_connected increase from which place(files) or when it decrease, because some of cases it dose not fall down automatically in our production server and we are getting to max connection error there, So i need to identify issues in which files has problem,  My site in magento 1.9 and max_connections=151 so i want to know what the ideal count for that and how can i monitoring ?

It sounds like there are a few long queries that lock lots of things, then runs for a while.  Meanwhile, other connections come and try to get at the locked things, but get stalled.

If you are using MyISAM, switch to InnoDB.

Set long_query_time to 1 and turn on the slowlog.  After a while, it will catch the naughty query.  Show us the query, plus SHOW CREATE TABLE for the table(s) that it touches.


Restore Oracle Database excluding some tablespaces I want to restore a backup in cold full (without archivelog) made with RMAN in another server and I do not want to do a full restore but partial, that is to restore only some tablespaces ... I have read that during the backup it can be configured to exclude some tablespaces of this but in this case what I need is ... already with the full backup do a restore of the database excluding certain tablespaces.
Is it possible to do this? it's an Oracle 9i

Thank you

RESTORE

  Use an optional SKIP TABLESPACE 'tablespace_name' argument to avoid
  restoring specified tablespaces, which is useful when you want to
  avoid restoring tablespaces containing temporary data.

  If you specify SKIP FOREVER TABLESPACE, then RMAN specifies the DROP
  option of ALTER DATABASE DATAFILE ... OFFLINE when taking the
  datafiles that belong to the tablespace offline before the restore.
  The DROP option indicates that RMAN does not intend to recover these
  files and intends to drop their tablespaces from the database after
  the database is opened again. In other words, FOREVER indicates that
  RMAN never intends to do anything with the skipped tablespaces again.

Example:

RESTORE DATABASE SKIP FOREVER TABLESPACE TOOLS, EXAMPLE;


How to correctly discard old backups? I have an Execute SQL Task in my SSIS control flow that's backing up a number of databases before it proceeds to run the overnight ETL:

EXECUTE master.dbo.xp_create_subdir N'G:\Backups\DQ'
GO
EXECUTE master.dbo.xp_create_subdir N'G:\Backups\master'
GO
EXECUTE master.dbo.xp_create_subdir N'G:\Backups\Metadata'
GO
EXECUTE master.dbo.xp_create_subdir N'G:\Backups\NDS'
GO
EXECUTE master.dbo.xp_create_subdir N'G:\Backups\Staging'
GO
BACKUP DATABASE [DQ] TO  DISK = N'G:\Backups\DQ\DQ.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'DQ', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [master] TO  DISK = N'G:\Backups\master\master.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'master', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [Metadata] TO  DISK = N'G:\Backups\Metadata\Metadata.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'Metadata', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [NDS] TO  DISK = N'G:\Backups\NDS\NDS.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'NDS', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [Staging] TO  DISK = N'G:\Backups\Staging\Staging.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'Staging', SKIP, REWIND, NOUNLOAD,  STATS = 10

The intent is to only retain 3 days, and discard anything older.

The problem is that the .bak files just keep growing and growing for a week or two, until the G drive fills up and the Execute SQL Task fails, which aborts the overnight ETL.

It seems the .bak is apparently not discarding old backups; looks like I could go back all the way to the last time I deleted all the backups to free some disk space on that G drive:

The question is simple: it looks like my Execute SQL Task is doing it wrong. How do I fix it? Or is it something else?

My best recommendation for you is to use already applauded backup solution from Ola

CleanupTime
Specify the time, in hours, after which the backup files are deleted. If no time is specified, then no backup files are deleted.

I normally, use backup database ... with init and then delete the backup files older than X days.
Ola's solution is a one stop solution and its very flexible, so you can tune it as per your needs.


Create partial index with single value I have PostgreSQL 12 DB with a table with a several columns, two of them are a bigint ("numCol") and a boolean ("boolCol"). numCol can have any integer number, and 0 is about 0.1%-5% of the table. It is frequently updated. The table can be big (>1B rows)
I have frequent queries for numCol=0 and boolCol is false, so I created a partial btree index:
myindex(numCol,boolCol) where numCol = 0 and boolCol is false.
Although the query performance is good, this index will have a single value, and I think that btree might consume more disk space than necessary.
Is there a more suitable index type for this case? What is the best practice in such cases?

A partial index typically doesn't need the columns from the WHERE clause in the index as well. You should put other columns that you retrieve in the queries that make use of that index in the column. e.g. the PK or something else.
So if you e.g. frequently use this query:
select pk_column, other_column
from the_table
where num_col = 0
and bool_col = false;

Then it usually makes sense to create the index like this:
create index on the_table (pk_column, other_column)
where num_col = 0
and bool_col = false;

That way the index scan can be used to also get the values needed for the


UPDATE with JOIN: Incorrect syntax near the keyword 'RIGHT' I'm using SQL Server Management Studio 2005. I want to look up a column value in another table, using a shared ID tblOrder_ordernr in both tables, but only update one table.

To me, the query is strangely written (UPDATE tblTid but SET viewOrderAlla...), but this is what I have to work with. How would I rewrite this query to do the same?

UPDATE tblTid RIGHT JOIN viewOrderAlla ON tblTid.sOrdernr = viewOrderAlla.tblOrder_ordernr
SET viewOrderAlla.tblOrder_verkligtid = 0
WHERE viewOrderAlla.tblOrder_verkligtid<>0 AND tblTid.iTidID Is Null
AND viewOrderAlla.tblOrder_levdatum>=DATEADD(month,-6,GETDATE())

I've tried moving the SET to before the RIGHT JOIN as per some other questions here, but I find no answer that even lets me parse the expression without a syntax error.

The tables are linked into an Access 2013 database, and there, the query runs fine. But not in SQL server.

Try something like this:

UPDATE viewOrderAlla
SET viewOrderAlla.tblOrder_verkligtid = 0
FROM tblTid
RIGHT JOIN viewOrderAlla ON tblTid.sOrdernr = viewOrderAlla.tblOrder_ordernr
WHERE viewOrderAlla.tblOrder_verkligtid <> 0
    AND tblTid.iTidID IS NULL
    AND viewOrderAlla.tblOrder_levdatum >= DATEADD(month, - 6, GETDATE())


Database Mirroring of SQL server I have two databases that are mirrored to another server using database mirroring.

The mirror server has to be down for some reason for few days.

Now, the production server is having principal databases in (PRINCIPAL/DISCONNECTED) State.

Clients can access those databases.

Questions

So what happens when they keep on adding data to these databases??
Will the data get committed or waits till the mirror comes up?

Your .LDF log is growing, as it cannot be truncated because the mirror is unavailable. Eventually you'll run out of space unless you either break the mirroring or bring the mirror back online.


CDC not picking up changes CDC has been set up for one of our databases in our test environment but it's not capturing any of the changes. I've made sure the capture job is running. I've disabled CDCs for the database, enabled them again and added the tables back, still not working.

I'm not seeing any errors (checked the job and sys.dm_cdc_errors), any suggestions on what to check next?

This database is set up in an availability group on a SQL 2012 server.

Thanks,
Tim

Figured out what the issue was.  For some reason the primary database had been joined to the availability group but the secondary was not, do not know if another DBA forgot to join it when they restored or if there was an error.  Either way, this wasn't allowing the logs to harden so CDC wasn't picking up the changes.

Did a fresh backup of the primary, restored to the secondary and joined it up and CDC started working again.

Thanks,
Tim


Combine rows with instruction from separate list Is there a way to combine (SUM) rows with unique ID (column) numbers by specifying the ID numbers in a list and then make MYSQL combine rows in a table according to the specification in the list?

The data in the database I use change frequently changed but the customer ID that needs to be combined remains the same so it would be great if there was a a way to have the list in a separate file/table or similar to read form when I re-create the database with new values.

For example I want to combine ID 112 and 115 when I recreate the database to ID 112 with value 1 equal to 9 and value 2 equal to 12 (the sum of 112 and 115).

ID | VALUE 1  | VALUE 2
------------------------
...
112| 2       |5
113| 5       |6
114| 6       |6
115| 7       |7
...

To new table:

ID | VALUE 1  | VALUE 2
------------------------
...
112| 9       |12
113| 5       |6
114| 6       |6
...

This is sort of what I am looking for but is there a way to read the ID's from a list with several values? For example ( 112,115), ( 449,622), ( 123,423,321) etc.?

Something like:

select min(id) as newId,
sum(value_1) as newValue1,
 sum(value_2) as newValue2
 from myTable t

where id in list;

How would the list need to be formatted?

The following query should give you 112, 9, 12

select min(id) as newId,
       sum(value_1) as newValue1,
       sum(value_2) as newValue2
  from myTable t
 where id in ( 112,115);


What are Identity Columns? I was reviewing the commit-fest scheduled for 7/01 for PostgreSQL and I saw that Pg is likely going to get "identity columns" sometime soon.

I found some mention in information_schema.columns but nothing much

is_identity         yes_or_no         Applies to a feature not available in PostgreSQL
identity_generation character_data    Applies to a feature not available in PostgreSQL
identity_start      character_data    Applies to a feature not available in PostgreSQL
identity_increment  character_data    Applies to a feature not available in PostgreSQL
identity_maximum    character_data    Applies to a feature not available in PostgreSQL
identity_minimum    character_data    Applies to a feature not available in PostgreSQL
identity_cycle      yes_or_no         Applies to a feature not available in PostgreSQL

The Wikipedia Page doesn't say much either

  An identity column differs from a primary key in that its values are managed by the server and usually cannot be modified. In many cases an identity column is used as a primary key; however, this is not always the case.

But, I don't see anything else on them. How do identity columns work? Do they provide any new functionality or is this just a standard method to create sequences? Any breakdown of the new feature and how it works?

This is to implement the feature found in the standard. (copied from a draft, date: 2011-12-21):

  4.15.11 Identity columns

  The columns of a base table BT can optionally include not more than one identity column. The declared type
  of an identity column is either an exact numeric type with scale 0 (zero), INTEGER for example, or a distinct
  type whose source type is an exact numeric type with scale 0 (zero). An identity column has a start value, an
  increment, a maximum value, a minimum value, and a cycle option.
  ...
  ...  The definition of an identity column
  may specify GENERATED ALWAYS or GENERATED BY DEFAULT.

It is a property of a column which basically says that the values for the column will be provided by the DBMS and not by the user and in some specific manner and restrictions (increasing, decreasing, having max/min values, cycling if the max/min value is reached).

Sequence generators (usually called just "sequences") are a related SQL standard feature: it's a mechanism that provides such values - and can be used for identity columns.

Note the subtle difference: a SEQUENCE is an object that can be used to provide values for one or more identity columns or even at will.

The various DBMS have so far implemented similar features in different ways and syntax (MySQL: AUTO_INCREMENT, SQL Server: IDENTITY (seed, increment), PostgreSQL: serial using SEQUENCE, Oracle: using triggers, etc) and only recently added sequence generators (SQL Server in version 2012 and Oracle in 12c).

Up to now Postgres has implemented sequence generators (which can be used to provide values for column, either with the special macros serial and bigserial or with nextval() function) but has not yet implemented the syntax for identity columns, as it is in the standard.

Defining identity columns (and the slight difference from serial columns) and various syntax (eg. GENERATED ALWAYS, NEXT VALUE FOR, etc) from the SQL standard is what this feature is about. Some changes / improvements may need to be done on the implementation of sequences as well, as identity columns will be using sequences.

If you follow the link identitity columns (from the page you saw), you'll find:

  identity columns

  From:     Peter Eisentraut
  To:   pgsql-hackers
  Subject:  identity columns
  Date:     2016-08-31 04:00:42
  Message-ID:  6adbacbf-73bc-dd1a-2033-63409180fd18@2ndquadrant.com

  Here is another attempt to implement identity columns.  This is a
  standard-conforming variant of PostgreSQL's serial columns.  It also
  fixes a few usability issues that serial columns have:

  need to set permissions on sequence in addition to table (*)
  CREATE TABLE / LIKE copies default but refers to same sequence
  cannot add/drop serialness with ALTER TABLE
  dropping default does not drop sequence
  slight weirdnesses because serial is some kind of special macro

  (*) Not actually implemented yet, because I wanted to make use of the
  NEXT VALUE FOR stuff I had previously posted, but I have more work to
  do there.

  ...

Update 2017, September: seems like the feature will be in Postgres 10, which is to be released in a few days/weeks:  What's New In Postgres 10: Identity Columns

Oracle have also implemented identity columns and sequences, in version 12c. The syntax is according to the standard, as far as I checked:
Identity Columns in Oracle Database 12c Release 1 (12.1)

  The 12c database introduces the ability define an identity clause against a table column defined using a numeric type. The syntax is show below.

GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]


MYSQL Timezone support We are having a shared hosting plan and they are saying  that do provide MYSQL Timezone support in a shared hosting plan. I can create timezone related tables in our database and populate them with required data(data from from our local MYSQL Timezone related tables. How to view the code syntax for MySQL "CONVERT_TZ" function?

Thanks
Arun

In order to timezone support to be around, the hosting company should have executed this:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql ...

The mysql_tzinfo_to_sql utility should load the time zones. Please make sure the host company has done this. If they can't or won't,

you could into Amazon EC2, you could have full access to MySQL and can run it yourself
Find a willing hosting company

BTW Here is the MySQL Documentation for CONVERT_TZ : http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz


Duplicate table structure with different data I am looking for a way to resolve a design issue with a Microsoft Access project that is being converted to use SQL Server 2014 as a backend.

We have several Access backend files, two of them have the same table structure, but different data. In the "Quotes" MDB backend file, we have tables containing sales quotes. In the "Jobs" MDB backend file, we have tables containing jobs (quotes that have been sold), but also the same sales quotes tables are duplicated. When a quote is sold, it's copied from one backend to the other. Which means the data exists in two places and is not kept in sync.

When I originally started upsizing tables from the MDB backends, i didn't think about this issue but now it's becoming a problem that needs to be resolved. We don't know how the structure should be fixed. It's my understanding that the combined set of all the tables in the various MDB backends for this software, should be in one database in SQL Server. The only reason for splitting them up as they are now is to overcome limitations of the Access MDB format.

To complicate matters further, the tables have exactly the same names. The Access front-end application switches between backends on-the-fly, when the user changes between quoting or working with sold orders.

Since you have determined that the two database approach is not right for you, you want to merge the two databases. (And I agree for all the little value that is)

(1) One suggestion, the simplest, is to rename one of the tables and make the needed changes to the code to use the proper table instead of the proper database and table.

(2) Another suggestion would be to merge the two tables into a single table. You would need to add a column to differentiate between the "Quotes" rows and the "Jobs" rows.

Also, you would need to make sure that your primary keys are unique between the two tables. If these rows were generated with Access ascending keys it is likely that you have duplicate keys between the two tables.

If this is the case, you would need to change the Primary Keys in, let us say "Jobs", to avoid any PK conflicts with "Quotes" rows.  Then also change all the referencing tables to have Foreign Key values that reference the new Primary Key values.

If there are many duplicate tables, then repeat as needed to bring everything into the new united database schema.

This will be a bit of work to set up, but once completed you should not need to think about that any more.


not a single-group group function when trying to add two sum() function I have two tables:

create table a
(
  index_ numeric(1),
  value_ numeric(2)
);
create table b
(
  index_ numeric(1),
  value_ numeric(2)
);

Now I want to get the number of these two tables sum,I tried :

select sum(value_) - (select sum(value_) from b) from a ;

it is not working without group by, how can i do this without group by?

select
  (select sum(value_) from a)
  -
  (select sum(value_) from b)
from dual;


Table structure/schema for spreadsheet-like web app (ex: Google Docs)? I'm a front-end developer/designer who is trying to branch out and learn more about the back-end world. I've chosen Python, Django, and Postgres as my starting place.  My goal is to build a personal web app, and use that as my catalyst for learning. It's been really fun.

I'm at the stage where I'm designing the data model. I would normally tinker and figure things out, but in this case I'm curious what is possible.

A good analogy for my app's data model is a spreadsheet. The user can create a "spreadsheet", then determine "columns", and subsequently the data type per column.

So, as a pure example, a new sheet has the following columns:

Name (text)
Cost (number / currency)
Date (time stamp
Frequency (number)
Notes (text)

Then, "rows" can be added where each entry has values for name/cost/date/frequency/notes, as well as some meta data like who created the row, at what time, etc.

I'm unsure what is the best schema to accomplish this example.  Below is the best idea I came up with.  I'm wondering if anyone would be willing to comment on whether this is the best approach, or give feedback on a different direction.

I've never attempted something like this, but maybe a schema such as this would work:

Spreadsheet
    spreadsheet_id (unique key)
    name
    num_rows
    num_cols

column_types
    type_id (unique key)
    type_name

spreadsheet_rows
    spreadsheet_row_id (unique key)
    spreadsheet_id (refers to spreadsheet.spreadsheet_id)
    row_seq_num (for on-screen row-ordering)
    row_name

spreadsheet_cols
    spreadsheet_col_id (unique key)
    spreadsheet_id (refers to spreadsheet.spreadsheet_id)
    col_seq_num (for on-screen column-ordering)
    column_type_id (refers to column_types.type_id)
    column_name

spreadsheet_cells
    cell_id (unique key)
    spreadsheet_id (refers to spreadsheet.spreadsheet_id)
    row_id (refers to spreadsheet_rows.spreadsheet_row_id)
    col_id (refers to spreadsheet_cols.spreadsheet_col_id)
    cell_value (holds the actual value!)

Of course  you'd only create records in spreadsheet_cells for non-null values...


Mysql shutting down during a mysqldump of a specific database I have a server which is running Centos 6.6 with Cpanel.  I have been attempting to do mysqldump backups on individual databases.  All of the databases are under 5MB except for one.

The database that is around 250MB causes a mysql shutdown in the middle of running a mysqldump.  The other databases work fine during a mysqldump.

My question is could this be due to a corrupted database?  What should I do to start troubleshooting this?

Is there another way to backup this database other than mysqldump.  I just need one copy so I can move the troubled database to another server and remove it from this one.

Here is my.cnf

[mysqld]
table_open_cache=100K
max_connections=500
thread_cache_size=4
query_cache_size=8M
query_cache_limit=1M
query_cache_type=1
innodb_file_per_table=1
default-storage-engine=MyISAM
open_files_limit=10000
max_allowed_packet=268435456
innodb_buffer_pool_size=123731968

Let me know if you need more information.

You will have to mysqldump around moments_production_wp

I have old posts to bypass databases and tables

Dec 16, 2011 : How do you mysqldump specific table(s)?
Feb 20, 2013 : Any option for mysqldump to ignore databases for backup?

Using the same concepts, here is how to mysqldump all except moments_production_wp

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DATABASES_TO_EXCLUDE="moments_production_wp"
EXCLUSION_LIST="'information_schema','mysql','performance_schema'"
for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done
SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYSQLDUMP_DATABASES="--databases"
for DB in `mysql -ANe"${SQLSTMT}"`
do
    MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done
MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > GoodDatabases.sql

After dumping all the other databases, mysqldump the one database

mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} moments_production_wp > OneDatabase.sql

It will mysqldump tables in alphabetical order. It should break near the bad table.

You can then mysqldump that database one table at a time

Let's say you know tables tb1 and tb2 makes mysqld crash

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DBTODUMP=moments_production_wp
SQL="SELECT table_name FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DBTODUMP}'"
SQL="${SQL} AND table_name NOT IN ('tb1','tb2')"
TBLIST=`mysql ${MYSQL_CONN} -AN -e"${SQL}"`
MYSQLDUMP_OPTIONS="--hex-blob --max-allowed-packet=1073741824"
for TB in `echo "${TBLIST}"`
do
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DBTODUMP} ${TB} > ${DBTODUMP}-${TB}.sql
done

This should help narrow the beam as to which table is causing the issue.

I do have one further thing to share. I have a 4-year-old post on the silent killer of mysqldumps : MySQL server has gone away obstructing import of large dumps. You will need to set your max_allowed_packet to 1G in my.cnf and restart mysqld. If you cannot change the max_allowed_packet at this time, you should mysqldump one row at a time using --skip-extended-insert to throttle the amount of data passing into your current MySQL Packet.


Could someone explain bizarre behavior executing millions of UPDATES? Could someone explain this behavior to me? I ran the following query on Postgres 9.3 running natively on OS X. I was trying to simulate some behavior where the index size could grow much larger than the table size, and instead found something even more bizarre.

CREATE TABLE test(id int);
CREATE INDEX test_idx ON test(id);

CREATE FUNCTION test_index(batch_size integer, total_batches integer) RETURNS void AS $$
DECLARE
  current_id integer := 1;
BEGIN
FOR i IN 1..total_batches LOOP
  INSERT INTO test VALUES (current_id);
  FOR j IN 1..batch_size LOOP
    UPDATE test SET id = current_id + 1 WHERE id = current_id;
    current_id := current_id + 1;
  END LOOP;
END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT test_index(500, 10000);

I let this run for about an hour on my local machine, before I started getting disk issue warnings from OS X. I noticed that Postgres was sucking up about 10MB/s from my local disk, and that the Postgres database was consuming a grand total of 30GB from my machine. I ended up cancelling the query. Regardless, Postgres did not return the disk space to me and I queried the database for usage statistics with the following result:

test=# SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 20;

           relation            |    size
-------------------------------+------------
 public.test                   | 17 GB
 public.test_idx               | 14 GB

However, selecting from the table yielded no results.

test=# select * from test limit 1;
 id
----
(0 rows)

Running 10000 batches of 500 is 5,000,000 rows, which should yield a pretty small table/index size (on the scale of MB). I suspect that Postgres is creating a new version of the table/index for each INSERT/UPDATE that's happening with function, but this seems strange. The entire function is run transactionally, and the table was empty to start.

Any thoughts on why I'm seeing this behavior?

Specifically, the two questions I have are: why has this space not yet been reclaimed by the database and the second is why did the database require this much space in the first place? 30GB seems like a lot even when accounting for MVCC

Short version
Your algorithm looks O(n*m) on first glance, but effectively grows O(n * m^2), because all rows have the same ID. Instead of 5M rows, you are getting >1.25G rows
Long version
Your function is inside an implicit transaction. That's why you see no data after cancelling your query, and also why it needs to maintain distinct versions of the updated/inserted tuples for both loops.
Additionally, I suspect you have a bug in your logic or underestimating the number of updates made.
First iteration of the outer loop - current_id starts at 1, inserts 1 row, then the inner loop performs an update 10000 times for the same row, finalizing with the only row showing an ID of 10001, and current_id with a value of 10001. 10001 versions of the row are still kept, as the transaction is not finished.
Second iteration of the outer loop - as current_id is 10001, a new row is inserted with ID 10001. Now you have 2 rows with the same "ID", and 10003 versions in total of both rows (10002 of the first one, 1 of the second one). Then the inner loop updates 10000 times BOTH rows, creating 20000 new versions, getting to 30003 tuples so far...
Third iteration of the outer loop: current ID is 20001, a new row is inserted with ID 20001. You have 3 rows, all with same "ID" 20001, 30006 row/tuples versions so far. Then you perform 10000 updates of 3 rows, creating 30000 new versions, now 60006...
...
(If your space had allowed) - 500th iteration of the outer loop, creates 5M updates of 500 rows, just in this iteration
As you see, instead of your expected 5M updates, you got 1000 + 2000 + 3000 + ... + 4990000 + 5000000 updates (plus change), which would be 10000 * (1+2+3+...+499+500), over 1.25G updates. And of course a row is not just the size of your int, it needs some additional structure, so your table and index gets over ten gigabytes size.
Related Q & A:

set isolation level for postgresql stored procedures
Making sense of Postgres row sizes


How to query data in backups? I have an ASP.NET app using SQL Server 2017. There's a bug somewhere that's causing some records to be deleted, and I'm trying to recover the data and find the cause of the bug. We take daily backups, and I have a query to find which records should be there but aren't, so I want to go through and run the query on each backup. The backups are fairly large, but I only need 2 tables out of the whole database for the query. What would be the most efficient way to do this over more than a month of backups?

My advice would be to just restore the entire backup. In my current environment, someone accidentally screws up some table once every few months. It just happens. Of course, it always hits our biggest database. 200GB backup file, 1+TB when fully restored. It is just easier to restore a copy somewhere, find the specific table we need, and just update that specific data. The whole process takes about an hour or so. I doubt any other solution would be quicker, worth the extra financial costs, or added complexity.


Prevent failure in conditional insert in mysql database The infrastructure of our system looks like this.

An AWS lambda function receives requests such as (accountId, .....). It creates an entry in the MySQL database using a newly generated UUID as caseId. (caseId, accountId, ....).

The insert is a conditional insert operation discussed in detail below.

I am able to avoid race condition by setting transaction isolation to SERIALIZABLE. However, the issue is that I do not have any control over how many concurrent requests will be successfully processed.

For example, consider following concurrent requests.

request  | accountId | field1 | ...  <condition>
 1         a1          value1   ....   true     --- create a new entry with caseId Idxxx
 2         a1          value2   ....   false    --- update existing entry with caseId Idxxx
 3         a1          value3   ....   false    --- update existing entry with caseId Idxxx
 4         a1          value4   ....   false    --- update existing entry with caseId Idxxx

With our current implementation we are getting CannotAquireLockException.
What are the ways in which I can avoid retry failures (CannotAquireLockException) ?

The detailed table schema and condition are described below:

The database is a mysql database system with the following table schema.

Table1: case table

|caseId(PK) | accountId | status |  .....

Table2: case reopen table

|caseId(FK)| casereopenId(PK)| caseReopenTime|

Table3: Alert table

Id (incrementing id) | alertId | accountId |

The lambda function tries to "create" a case in the database.

the create wrapper, generates a UUID for caseId.

The goal is :

check if an accountId already exists in case table.
if it does, then

check if status is OPEN
get the caseId for the accountId.
check if the caseId is present in case reopen table.

if above condition is false, then add an entry into the case table.

Thanks!

Don't bother with SERIALIZABLE.

START TRANSACTION;
SELECT caseid, status, ... FROM ... WHERE ... FOR UPDATE;
check `status`, do some processing
optionally do
    INSERT ...
COMMIT;

With that logic, you can handle lots of concurrent actions.


SQL Server 2012 extended event session to expose the network protocol I am using SQL Server 2012 (11.0.5058.0) extended events, and wish to know the network protocol used by each connection (TCP/IP, shared memory etc).

Event session created for the login event via: -

CREATE EVENT SESSION [Login] ON SERVER
ADD EVENT sqlserver.login(
    ACTION(
sqlserver.client_app_name
,sqlserver.client_connection_id
,sqlserver.client_hostname
,sqlserver.client_pid
...
...

I have added all available actions for the login event (SELECT * FROM sys.dm_xe_objects WHERE package_guid = '655FD93F-3364-40D5-B2BA-330F7FFB6491' AND object_type = 'action' ORDER BY name) but none appear to give the network protocol.

It may be of course that Login is not the correct event to give this information, but I can't see a connection event (or similar) within the XE DMV.

To confirm, I want an extended event session to expose the net_transport information that is returned by dm_exec_connections: -

SELECT net_transport FROM sys.dm_exec_connections

Found the answer.

The login event is correct and the network protocol is exposed via the options_text action. options_text was always blank for me previously and this needs to be set on via SET collect_options_text=(1). An example session might be: -

CREATE EVENT SESSION [Login] ON SERVER
ADD EVENT sqlserver.login(SET collect_options_text=(1)
    ACTION(
    sqlserver.session_id
    ,sqlserver.username
    ))
ADD TARGET package0.ring_buffer
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=ON
)
GO

And this would then populate options_text with something like: -

  network protocol: TCP/IP  set quoted_identifier on  set arithabort off
  set numeric_roundabort off  set ansi_warnings on  set ansi_padding on
  set ansi_nulls on  set concat_null_yields_null on  set
  cursor_close_on_commit off  set implicit_transactions off  set
  language us_english  set dateformat mdy  set datefirst 7  set
  transaction isolation level read committed

I found this out be chance by creating a session from the "Connection Tracking" Microsoft shipped extended events template.

SSMS | Management | Extended Events | Sessions | Right click | New session | General page | Template = Connection Tracking.

That template includes "SET collect_options_text=(1)" and I wasn't previously aware such an option existed.

An example is also given in this sql-server-performance article. The last screenshot shows options_text populated with "network protocol: LPC.." LPC is shared memory.


Failover strategy for SQL Server 2016 Standard Edition I am using SQL Server 2016 Standard Edition.

I have two database servers, SQL01 and SQL02 with only one database, and I am utilizing transactional replication for bringing data from SQL01 to SQL02.
Users should be able to read from database in both server at any time and transactional replication allows that.

I am thinking of a failover strategy to failover to SQL02 and then back to SQL01 if there is such a situation (patches/maintenance).

I know that transactional replication is not a HA solution, but since I am using SQL Server 2016 Standard edition I have Log shipping/Database mirroring options only.

Current failover strategy for a patch/maintenance in SQL01:

Transactional Replication on from SQL01 to SQL02.
So point the application to SQL02. Now new data comes into SQL02.
To keep SQL01 in synch with SQL02, restore full backup of the database from SQL02 and keep the SQL01 initialized.
Implement Log Shipping from SQL02 to SQL01.

For me this looks like a solution I can use. Possible issues I see are:

My database is like 2TB, so a backup of that in SQL02 will require 1 hour and restore in SQL01 will require 3 hours. Maybe I can avoid that by using a previous-night full backup for SQL02 and restore that at an earlier time. Please comment if it is okay to do that.
Other issue is, if I have regular transactional log backups of a SQL02 database (not from log shipping), would that also be restored by log shipping?

Do you see any issues other than these?

Please add your suggestion(s).

Separate from your Replication solution, could use a Failover Cluster Instance or Basic Availability Group for HA of your publisher.

Or, if you have a stomach for obscure replication solutions, you could implement Bidirectional Transactional Replication, which is similar to Peer-to-Peer replciation, but older and available on Standard Edition.


Return table with separate columns from function I have this function, it's simple enough:

CREATE OR REPLACE FUNCTION test_function()
RETURNS TABLE(
        id INTEGER,
        organization_id INTEGER,
        email_address CHARACTER VARYING
)
AS $BODY$
        SELECT id, organization_id, email_address FROM users;

$BODY$ LANGUAGE SQL;

The output of this functions gives me something that looks like this:

> SELECT test_function();                                                                                test_function
---------------
 (1,1,test)
 (3,1,test2)
 (4,2,2test)

When I run the query by itself, I expect to get something more like this:

> select id, organization_id, email_address FROM users;
 id | organization_id | email_address
----+-----------------+---------------
  1 |               1 | test
  3 |               1 | test2
  4 |               2 | 2test

How can I get a similar result back like that so I can parse it in a SQL function?

To get decomposed, separate columns, call the table function with:

SELECT * FROM test_function();

But you also have sneaky naming conflicts. The column names listed in the RETURNS TABLE clause are implicit OUT parameters, visible in every SQL statement in the function body. Avoid using names that conflict with column names and/or table-qualify all possibly conflicting table column names in the function body. Like:

CREATE OR REPLACE FUNCTION test_function()
  RETURNS TABLE (id int, organization_id int, email_address varchar) AS
$func$
SELECT u.id, u.organization_id, u.email_address FROM users u;
$func$ LANGUAGE SQL;

See: Variable Substitution in the manual.


What is the new JDBC 4.3 Connection Request feature with `beginRequest` & `endRequest` methods? The JDBC 4.3 update arrived with Java 9 2017-10. Amongst its few changes is the new feature on Connection where the beginRequest and endRequest methods signals…

  Hints to the driver that a request, an independent unit of work, is beginning on this connection. Each request is independent of all other requests with regard to state local to the connection either on the client or the server. Work done between beginRequest, endRequest pairs does not depend on any other work done on the connection either as part of another request or outside of any request. A request may include multiple transactions. There may be dependencies on committed database state as that is not local to the connection.

…and…

  … is optional, vendor specific and should largely be transparent.

➠ Please explain the purpose of this feature.

It seems to be about something larger than transactions yet is not about two-phase commits. Is it related to sharding, the major new feature in JDBC 4.3?

Is it some kind of scope that wraps around multiple transactions?
What databases make use of this feature?

In particular, does Postgres, H2, or or Microsoft SQL Server have such a feature?

I am not asking about whether these databases have JDBC drivers updated yet for JDBC 4.3 yet. I am asking about the bigger picture, what is the meaning and purpose of a "request" in the context of a database connection across connections.

Oddly, the JDBC 4.3 specification makes only this one mention, with no explanation:

  JDBC API changes

  The following changes were made to existing JDBC interfaces..

  java.sql.Connection

  Added the methods beginRequest,endRequest, setShardingKey,
  setShardingKeyIfValid.

These new JDBC4.3 APIs beginRequest and endRequest have to do with connection management. It's really a hint provided by the connection pool to the driver that a connection has been checked out of the pool (beginRequest) or checked back into the pool (endRequest). Without these APIs the driver has really no clue about request boundaries. The driver can then use these request demarcations to do dirty laundry like pinging the database, replacing the connection without another one for load balancing, clearing out dirty states, etc.

These APIs were defined as Oracle specific APIs in the Oracle JDBC thin driver before JDBC4.3. Oracle's UCP (Universal Connection Pool) already calls these APIs. Now that they are part of the standard, other connection pools can start using them (and other drivers will implement them).


How can MySQL return an empty row matching a given table's schema, given the table name as an input string? How can a query or procedure return an empty copy of a new record?
Ideally suited for objects which are in a transactional allocation process:
It is highly preferable to have authoritative defaults and NULLS for arbitrary tables when constructing new objects() that may-or-may not ultimately be saved to the database.
For example, a CMS user relies on AJAX which calls for a new post() or new product() dynamically. Rather than trusting stored profiles to set values in the scripting language, a blank "instance" of a row would be rather useful.

From a pure database perspective this isn't really possible, unless you made a View that had a single row with the default values you needed that matched the schema of your table.
This question might be better suited on StackOverflow for how to construct a new instance of an entity object in JavaScript or whichever other object-oriented languages you're using.


Solutions for Introducing a new Entity In Between An Existing Association I'm looking for best practices in regards to a specific change in schema. I'm using sql.

I had the following entities: Project and batches - a project had many batches. The batches table had a project_id.

As time went on, we decided that a Project would have orders which in turn would house those same batches. So now a project has many orders and orders have many batches. With that said, batches now have an order_id and project_id. My issue is this - the app depends on the presence of both fields, older batches don't have order_id and new batches don't have project_id so I'm getting exceptions throughout the application. I was thinking about getting rid of the project_id field on batches, but we need to keep the reference. We cannot create orders for each of those batches and migrate the field with a script since those orders will not be able to be completed/invoiced/etc (they already were in the real world).

Old Schema

create table Company
(
  id int not null primary key
)

create table Project
(
  id int not null primary key,
  company_id int not null references Company
)

create table Batch
(
  id int not null,
  project_id int not null references Project
)

My current schema:

create table Company
(
  id int not null primary key
)

create table Project
(
  id int not null primary key,
  company_id int not null references Company
)

create table Orders
(
  id int not null,
  project_id int not null references Project,
  po_number int
)

create table Batch
(
  id int not null,
  order_id int not null references Order
  project_id int references Project
)

I would have to add a lot of conditional logic in the application in order to handle all of the cases, which I certainly don't want to do.

What are best practices or good solutions for situations like this? Perhaps adding application logic is the only way to go.
Should I find a way to remove project_id entirely? Then how would I make sense of getting order updated appropriately?

Thanks!

For such a structural change in your data model you will have to go “all in”. You can’t model the same entities in 2 ways. As you correctly observed, it will lead to endless grief for years.

That means your “old” view of the world will have to adjust.
Have you considered creating a single order to contain all batches for existing projects, and name it “Migration Order” or something meaningful? Your application may choose to ignore it when presenting, or not. This will maintain a unified hierarchy.


Oracle 11g database copy I got database oracle 11g on my localhost on laptop. I would like to copy it to run on other PC (windows) for some tests. How can I do that?

Once you've installed the Database Instance onto your other PC:

Use SQL Developer's "Database Copy" to copy straight from one to the other, or "Database Export" to simply export the source database into file(s).
They're available in the "Tools" menu.

Both instances should be running if you choose "Database Copy."

If you choose "Database Export," you can choose to have it all go to one or the other.

Beware of disk space and wallet issues, and ensure your other instance's environment is ready to take the new structure/files.


SQL Server not properly using memory? Ihave a database with two tables in it. One table has approximately 20 million rows and another has approximately 70 million rows.

When I run sp_spaceused on these two tables I find that the total size of the data is ~6.7GB and the total size of the index is ~2.6GB. Combined this is a total of ~9.3GB.

This is the only data on this server.

I am a SQL novice so I would assume that the amount of RAM these two tables will eat up would be somewhere close to the 9.3GB after the entire data set and indexes have been loaded into memory. But I am apparently woefully wrong as after running queries for a few minutes the memory taken up by the SQL Server process will hit 30+GB and continue to climb. I've even tested running the same query over and over again and the amount of memory being taken continues to climb.

The reason for the excessive amount of RAM is that the eventual size of the data will be 5-6 times this size and this will be used for data analytics.

My question is why is SQL Server using so much memory? If the entire table and index are loaded into memory what is it continuing to load into memory? Is there a way I can control this? Is there a different type of index I can be using besides nonclustered indexes if my goal is in-memory analytics? I have been reading into columnstore indexes but after testing on a smaller dataset I was actually able to achieve better performance by removing indexes altogether than I was with a columnstore index.

Overall, I'm confused as to what is going on with this server. Can anyone chime in with some guidance? At this time I'm trying to test out SQL Server 2014 but it's still in CPT2 so it isn't a solution until release.

I've also looked into column based storage technologies such as Amazon Redshift but their service is not HIPAA compliant.

On this question I found a few queries to run.  The cached size of my database right now is 32230.390625.  This is greater than 3 times the total size of the database.

db_buffer_pages = 4125490

db_buffer_MB = 32230

I also wanted to do a bit of testing and decided to completely wipe the database, restart the SQL service, and reload the data.  When I did that it was using ~20 GB of RAM.  I can't understand why.  That is over two times the size of the raw data and index.

As stated in the referenced answers, SQL Server will use as much memory as you let it use. Additionally, SQL Server uses memory for a variety of things that are isolated to the domain of "data storage." In addition to the things that mrdenny noted, it also caches the query plan of a query

In SQL Server, memory use is tracked by Memory Clerks. Take a look and see how many there are:

SELECT *
    FROM sys.dm_os_memory_clerks;

These can be roughly broken down into four categories:

General ("Default") - These are the ones that start with "MEMORYCLERK_" (buffer pool is tracked in here so that's where your "buffer pages" query is looking)
CACHESTORE - "CACHESTORE_" objects. This is where query plans are monitored and stored.
USERSTORE - "USERSTORE_" objects. Database metadata and permissions are monitored and stored.
OBJECTSTORE - "OBJECTSTORE_" objects. Internal operations are handled here.

How SQL Server coordinates requests for memory is through what's called a Memory Broker.

SELECT *
    FROM sys.dm_os_memory_brokers;

These brokers are in charge of managing the memory. The CACHE broker will be the first to release memory to system when pressure is encountered. What this means is that your query plans will be evicted from memory before your data pages are flushed from the buffer pool. (Most of the above taken from my notes from Bob Ward's memory presentation at PASS Summit 2013).

This is just a brief overview. There's more to it than just this, but the reality is that there are so many things going on in your system that it's not quite correct to assume that SQL Server isn't handling memory properly just because the size-on-disk value of your database isn't less-than-or-equal-to the value of buffer pages*8/1024.

Here's another query that lists buffered objects, just for fun.

        SELECT

            object_sysname = OBJECT_NAME(p.[object_id]),
            index_id = p.index_id,
            buffer_size_mb = COUNT(*)/128.,
            buffer_count = COUNT(*),
            compression_type = p.data_compression_desc,
            allocation_type_desc = a.type_desc,
            rows = p.[rows]
        FROM sys.allocation_units AS a
            INNER JOIN sys.dm_os_buffer_descriptors AS b
                ON a.allocation_unit_id = b.allocation_unit_id
            INNER JOIN sys.partitions AS p
                ON a.container_id = p.partition_id
        WHERE b.database_id = CONVERT(int,DB_ID())
            AND p.[object_id] > 100
        GROUP BY p.[object_id], p.index_id,
            p.data_compression_desc,
            a.type_desc, p.[rows]
        ORDER BY buffer_count DESC

(A modified version of Glenn Berry's DMV query).

To see what you're allowing SQL Server to use for the buffer pool (including all page allocations) and CLR allocations, you can run this query:

SELECT *
FROM sys.configurations
WHERE name = N'max server memory (MB)';

OR

sp_configure N'show advanced options',1;
RECONFIGURE WITH OVERRIDE;
GO
sp_configure N'max server memory (MB)';
GO

A config_value of this (2147483647) means that you're allowing SQL Server to use everything it can get. This may lead to resource starvation on the OS which will, in turn, start taking memory back and swapping to the page file - a nasty scenario. Glenn Berry (again) has a good recommendation for choosing an appropriate setting for max server memory (though it's for up to 2008 and I've not found good guidance for >= 2012).

Additionally, since you've noted that you're testing in SQL Server 2014, there is also the possibility of extending the buffer pool to disk (only if you have SSD storage attached).


Load csv into MySQL database programmatically I know about LOAD DATA INFILE command -- however, this requires that we already have the table with appropriate columns in place. When I have csv with hundreds of columns, it is not feasible to type all the columns definitions in by hand.

There's also a website that allows uploading the .csv file and it will produce the appropriate MySQL CREATE TABLE and INSERT codes. However, 1) this is not easily reproducible, and 2) there are quirks such as BIT-type data has to be modified so that it's enter as b'1' with the b.

Is there any way to import csv into SQL programmatically, for example using R, Python, Java, etc.? I work in social sciences, and very frequently we need to download some csv files that need to be imported.

What you want is nearly impossible, no one can know if you want to import '1' as a characters string, n decimal integer, a base-2 integer, an hex integer, a float, a blob, a "boolean", ...

You can try heuristic programs, or import them all as strings and run an analysis program like SELECT... ANALYZE TABLE();. It would be easier if the csv had the datatypes as an information.

Regarding programs that create the table (with chars) based on the list of columns, that is easier to find (GUI) and program.

Here it is one in bash as an example.


What is a Data Source and where can I find it in SSMS? I have just recently started using SQL Server Management Studio. After some hitches, I have managed to create a Database. Inside the DB I have also created some Tables and populate them.

Now I am trying another software that needs to be connected to this DB. When running the software it asks me:

"Data Souce" and "Catalog"

Apparently the "Catalog" is the name of the DB I have created but I don't know what "Data Source" refers.
It must be a rather begineer question but what does "Data Source" refers to?

Yes, Initial Catalog refers to the database name and Data Source refers to your server name.

See more on SQL Server connection strings


SUM a new value to an existing value I have a php script which processes a form with a single input.

I want the value I send through input to be added to an existing value in database (mysql).

For example in database I have value = 10

When I type into the input 25 and submit the info. I want the value in database to be 35. I don't need any php code. I just want to know the correct mysql query

Edit:

UPDATE value =  value + $newvalue

something like this would be right ?


Deadlock Detection | Should the node T4 point to T1 and T2? This example regarding deadlock detection was shown during one of my lectures and I have a doubt about it. The image is shown below.
For deadlock detection mechanism, why does T4 point only to T2 and not T1? Chronologically, T1 requests for the exclusive lock before T4. So shouldn't T4 point to T1 as well?

T4 is only requesting B, which is held by T2. The line represents the dependency on the resource from a requestor to a holder; the graph does not model a "queue" of waiters, that's a different problem.
To help understand the graph draw the 4 processes T1-T4. Each time a resource is granted to a process, put the letter next to it. When you realize a process needs a resource held by another process draw a line from the waiter to the holder.
So in order:

T1 is granted A
T2 is granted B exclusively, T1 requests B. Draw a line from T1 to T2.
T3 is granted C
T2 requests an exclusive lock on C which is incompatible with the shared lock held by T3. Draw a line from T2 to T3.
T4 requests requests an exclusive lock on B which is held by T2. Draw a line from T4 to T2.

This is not a deadlock, btw.
If this helped you, please don't forget to mark this as solved; thank you.


Wal segment still exists on master, but logs on slave and master say it's been removed I'm trying to set up streaming replication on Postgresql 9.5

The master and slave are configured as below and WAL files are accumulating on the master. However, something is wrong as I get complaints that the WAL files are missing:

Slave:

  FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 0000000200000059000000BA has already been removed

Master:

  repuser@[unknown] ERROR:  requested WAL segment 0000000200000059000000BA has already been removed

The WAL file does exist on the master, and the slave will restore happily if I ship the WAL files over and use the restore_command option in recovery.conf.

postgres$ ls -l /db/archivedir/0000000200000059000000BA
-rw------- 1 postgres postgres 16777216 Mar 31 10:18 /db/archivedir/0000000200000059000000BA

Master config - postgresql.conf:

wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /db/archivedir/%f && cp %p /db/archivedir/%f'
max_wal_senders = 3

The master also has a replication slot configured:

brp=# SELECT * FROM pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | active | active_pid | xmin | catalog_xmin | restart_lsn
----------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------
brp_uk    |        | physical  |        |          | f      |            |      |              |

Slave - postgresql.conf:

hot_standby = on

Slave config - recovery.conf:

standby_mode = on
primary_conninfo = 'host=xxx port=5434 user=repuser password=xxx'
trigger_file = '/tmp/postgresql.trigger.5434'
primary_slot_name = 'brp_uk'

Then the pg_basebackup is run and the slave started.

The slave has all the data as of the time of the backup, but no new data from the WAL files, and the error above.

What have I mis-configured?

It is as dezso alluded to in the comment.
The reason you get the error is because the WAL has already been archived.

You can configure how many segments to store on the master with the wal_keep_segments configuration. You should set it to at least the amount of WAL's created during the base backup (and some extra as buffer).

Since the standby doesn't connect to the replication slot until it is ready to use it (which is after the base backup is finished), it means that the slot has not been activated yet and therefore is not storing the old WAL's.


Can we minimise the risk of using xp_cmdshell? Using xp_cmdshell can be quite helpful and sometimes possibly the only answer to some scenarios. I've read some posts on the internet that enabling xp_cmdshell might jeopardize the security of the database/server.

My question is that, is there anything we can do to reduce the risk? For example can we set some restrictions/applying users role, etc providing safeguards to mitigate the risk?

Thanks.

Well, you could not grant execute on it explicitly to end users (deny it even), and only enable it in stored procedures that use EXECUTE AS with some login that does have execute permissions. Then grant execute on only that stored procedure to the user that needs to run the command.

First, make sure xp_cmdshell is enabled for the instance:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;

Now depending on operating system and SQL Server service account, you may need to set up a proxy account (and to do this, thanks to UAC, you may need elevation and launch SSMS as an administrator):

EXEC master..sp_xp_cmdshell_proxy_account 'Domain\User', 'Password';

Then create a wrapper in your database that does whatever you need it to do (this is just a sample of a VERY generic wrapper that doesn't really protect you from anything; only using it to demonstrate):

USE yourdb;
GO
CREATE PROCEDURE dbo.uxp_cmdshell
  @cmd VARCHAR(2048)
WITH EXECUTE AS OWNER
AS
BEGIN
  EXEC master.dbo.xp_cmdshell @cmd;
END
GO

Now give your user or role permissions to execute the procedure:

GRANT EXECUTE ON dbo.uxp_cmdshell TO [your_user_or_role!];

Now when your_user logs in, and tries to execute willy-nilly calls to xp_cmdshell:

EXEC master.dbo.xp_cmdshell 'dir c:\';

They will get:

  Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
  The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

However if they pass the same command to your new procedure:

EXEC dbo.uxp_cmdshell 'dir c:\';

It will work just fine (assuming your proxy account is set up correctly and/or the SQL Server service account has adequate permissions).

This can take some work, but effectively this allows you to control exactly what your users can do with xp_cmdshell.


What is the history of the SQL Server ANSI (ISO) compatibility settings? I know what these settings do and when to use them, what I'd really like to know is why this problem even exists.
Recently, I came across an ugly situation--make that, yet another ugly situation--involving the settings of what I'm goint to call the ANSI Seven:
ansi_nulls                ON
ansi_padding              ON
ansi_warnings             ON
arithabort                ON
concat_null_yields_null   ON
numeric_roundabort       OFF
quoted_identifier         ON

I've listed them above with what I believe to be the most commonly recommend default settings. (Of interest, SET ANSI_DEFAULTS will set all of them ON or OFF, without special treatment for numeric_roundabort, but I digress.)
These impact code, queries, query plans, and a whole host of stuff as can be found by searching for any of them throughout SO. There are so many hits, they obviously have caused pain and anguish to hundreds, if not thousands, of developers for decades. (I hit issues with them long before SO was there to help.)
What I would like to know is: why?
Why is this an issue? Where did these settings come from? Who thought it was a good idea? Most importantly, why are these seven pretty much always lumped together?
Does anyone have any insights on this? Links to articles, blogs, even Learning Channel Documentaries would be great.

SQL Server came from Sybase, which had different default settings for these kind of things. Gradually, MS introduced SET settings to allow us to control behavoiur. A big push to follow ANSI standard was with the 6.0 release in 1995. I believe they even hired Joe Celko with the work to be ANSI SQL certified (yes, there was a certification process back in the days - anybody ever used SET FIPS_FLAGGER? :-)). This was when the specific SET ANSI setting was inroduced - to allow us to "be ANSI" with only one setting. But this is all a mess with the combo of database settings (which are largely ignored) along with different APIs and tools that has different combo of these settings.


PgPool 2 with HA Proxy So, I'm looking to add high availability to a postgresql server. I've looked into some tutorials on the web but, haven't been able to find exactly what I'm looking for.

E.g.:

PostgreSQL Replication and Hot Standby – In Practice
http://blog.3dtin.com/2012/07/26/postgresql-replication-and-hot-standby-in-practic/

Is a great tutorial for how to set that up, BUT failover is manual.

Replication and Load Balancing with PostgreSQL and Pgpool2
http://www.keyup.eu/en/blog/89-replication-and-load-balancing-with-postgresql-and-pgpool2

Seems to be perfect only if the pgpool server was not the single point of failure.

So, my question is, if I add a HAProxy cluster, just like any pool of web servers distribute load, would I be able to have the automatic failover to the pgpool and therefore to the postgresql servers??

                    Application Servers
                             |
                             |
          ip1 -------- (floating) ip3 -------- ip2
        HAPROXY   ------- hearbeat -------- HAPROXY
        pgpool II                           pgpool II
         |    \                             /     |
         |     \                           /      |
         |      \                         /       |
        ip4<-----\-----------(to same master ip4) |
    postgres      ip5<------------------(to same slave ip5)
     Master      postgres
                 slave

Yep, you don't need HAProxy with the newest versions pgpool-II. I'm not sure in which version it is introduced, but "watchdog" feature that is now embedded in pgpool does everything needed for automatic fialover and real HA.

Take a look at this post - it'll help for sure.


How to separate values from one column into two columns in SQL Server I have the below data-set where a user has more than 1 Contact No associated to their Name.  The below is just a small sample of my data-set.

Name     Contact No
-------  ----------
Deepika  75865558
Deepika  207586558
Rnveer   746655558
Rnveer   946655558
vaibhav  2356841
vaibhav  6655558

I want to split the Contact No into their own columns to consolidate the information as shown below.

Name     Con 1      Con 2
-------  ---------  ---------
Deepika  75865558   207586558
Rnveer   746655558  946655558
vaibhav  2356841    6655558

How can I get data that is only located in a single table, and in the same column to output in 2 columns?  See the above example.

Here's an example which does what you want and will get you started with using PIVOT. Sorry it is a bit rushed, but hopefully it will get you started and show you how it can be done fairly easily.

There will be limitations and I would fully advise researching and playing with the pivot functions because they are really powerful.

-- Some sample data to work with
DECLARE @SampleData AS TABLE (
    NAME VARCHAR(20)
    ,contactnumber BIGINT
    )

INSERT INTO @SampleData
VALUES (
    'Deepika'
    ,75865558
    )
    ,(
    'Deepika'
    ,5181568
    )
    ,(
    'Deepika'
    ,56954121
    )
    ,(
    'Rnveer'
    ,4561896
    )
    ,(
    'Rnveer'
    ,48640655
    )
    ,(
    'vaibhav'
    ,48640655
    );

--The pivot
WITH PivotData
AS (
    SELECT NAME --The row group
        ,'Contact ' + CAST(ROW_NUMBER() OVER (
                PARTITION BY NAME ORDER BY contactnumber ASC
                ) AS VARCHAR(1)) AS TheRow --The value that will form columns
        ,contactnumber
    FROM @SampleData
    )
SELECT *
FROM PivotData
PIVOT(AVG(contactnumber) FOR TheRow IN (
            [Contact 1]
            ,[Contact 2]
            ,[Contact 3]
            ,[Contact 4]
            )) AS ThePivot1


Inserting ID and Date to locate data for C#/VBA application I am trying to allow users to upload an excel sheet to insert data into the sql database. When they upload data I want a datagrid view to show the data they just uploaded in the database and to be able to see that in retrospect as well based on the date.

I created a table like this:

**dbo.DB_TRACK**
V_ID Identity
TBL_N varchar(20)
ID  int
ENTR_DT date

I wrote Stored Procedure for one of the database tables like this:

Create Procedure dbo.Insert_Students
(
SName varchar(10),
SAge varchar(10),
SAdmit_Dt varchar(10)
)
    Declare @id table(id int),
     @pk int,
     @iSAge int,
     @iSAdmit_Dt date

     Set @iSAge=@SAge
     Set @iSAdmit_Dt=Convert(Date,@SAdmit_Dt)

    BEGIN TRY

      BEGIN TRANSACTION

      Insert into dbo.Student(Name,Age,AdmissionDt)
      OUTPUT INSERTED.S_ID into @id
      Values
      (@SName,@iSAge,@iSAdmit_Dt)

      COMMIT TRANSACTION

      BEGIN TRANSACTION

      Insert into dbo.DB_TRACK (TBL_N, ID, ENTR_DT )
      VALUES ('dbo.Student', (Select @pk=id from @id),GETDATE)

      COMMIT TRANSACTION

    END TRY
    BEGIN CATCH

      IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION

    END CATCH

I am getting the error:

  Incorrect syntax near '='.

UPDATE:
Not getting this error anymore (Thanks to David Browne) and was able to save the SP but when executing the SP, I am getting this error:

  Conversion failed when converting date and/or time from character
  string.

I tried Convert(date,GetDate())  but it did not work

The code that calls this stored procedure is in VBA like this:

Dim SName, SAge,SAdmit_DT as String
Dim oSName, oSAge,oSAdmit_DT as ADODB.Parameter

SName= ws.Cells(1, 11)
SAge = ws.Cells(1, 12)
SAdmit_DT = ws.Cells(1, 13)

Set oSName = cmd.CreateParameter("@SName", adVarChar, adParamInput, 10, SName)
cmd.Parameters.Append oSName
Set oSAge = cmd.CreateParameter("@SAge", adVarChar, adParamInput, 10, SAge)
cmd.Parameters.Append oSAge
Set oSAdmit_DT = cmd.CreateParameter("@SAdmit_DT", adVarChar, adParamInput, 10, SAdmit_DT)
cmd.Parameters.Append oSAdmit_DT

cmd.Execute' This is where it errors out

I tried removing SAdmit_Dt but still got the error
I tried passing DATE() through excel but got an Arithmatic overflow error
Any idea how to fix this? Also is there a better way to do what I am trying to do?

-Please don't use short, cryptic column names.
-There's no reason to wrap individual statements in explicit transactions.

You'll want to extract the key value from the table variable in a separate statement.  Something like:

Create Procedure dbo.Insert_Student
(
SName varchar(10),
SAge int,
SAdmit_Dt date
)
BEGIN
    Declare @ids table(id int)
    Declare @pk int

    BEGIN TRY

      BEGIN TRANSACTION

      Insert into dbo.Student(Name,Age,AdmissionDt)
      OUTPUT INSERTED.S_ID into @id
      Values  (@SName,@SAge,@SAdmit_Dt);

      set @pk = (Select id from @id);

      Insert into dbo.DB_TRACK (TBL_N, ID, ENTR_DT )
      VALUES ('dbo.Student', @pk,GETDATE());

      COMMIT TRANSACTION

    END TRY
    BEGIN CATCH

      IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION

      THROW;

    END CATCH
END


Database design for a lot of rows with data archive i'm new to database design on larger scale. I have some knowledge and experience with MySQL but now i have some performance issues.

Currently i'm using MySQL with 3 databases:

History
Snapshot
Static

In history database i'm inserting data. For one user there are approx 5mio rows on month (users are slowly growing every week). As my select queries have gotten slow probbably due to large amount of data i have triggers that copy inserts into snapshot database and delete older entires. Select statements are very quick on snapshot database. But as i add more triggers, mysql seems to fail to whitstand the load. The static database is just users table and some metadata.

So my question is, how can i achieve push/pop with my tables using MySQL or any other database technology?

I want to insert data into snapshot database and all data that has timestamp older than 12 hours should be moved to history (for later data analysis - once or twice a week) and deleted from snapshot so it keeps minimal amount of data to work with. Is this achievable with MySQL or something else? What would be recommended hardware setup for such database design?

As of writing and reading from database i do most of writing from python with pymysql, also some reading and most of reading with NodeJS mysql plugin.

I hope there is some not too complicated solution for my issue. But i will be happy with any advice or guide direction on that to use.

A sliding time scale is very efficient by using PARTITIONs.  Implement 14 hourly partitions using PARTITION BY RANGE(..).  Use REORGANIZE PARTITION to create a new partition every hour.  Use "transportable tablespaces" (assuming you are using 5.6, or preferably 5.7, and InnoDB) to move the 'old' partition away from the main table to the archive table.

More on timed partitions (including code).

More thoughts:

Although TRIGGERs may be functional, I worry that they are costly in your situation.  Can you elaborate.  Perhaps partitioning can serve a purpose there, too.
I hope the Query cache is completely turned off:  query_cache_type = 0 and query_cache_size = 0; otherwise it could be causing some of the slowdown.
How do you do the millions(?) of INSERTs per day?  One row at a time?  Or batched (much better)?
For the 'data analysis', do you build and maintain "Summary tables"?  With such, you may not need the triggers, archive, etc.  Reporting from a summary table is easily 10x faster, and does not touch the main table.
Have you looked into Master-Slave as a way of (1) archiving the data, and (2) providing a Reporting platform separate from the main table.  This, too, could eliminate the need for triggers/archive/etc.

Could you provide more numbers -- how many users?  inserts/sec?


MySQL Server 5.7.33 or upgrade Is MySQL v5.7.33 an acceptable version for production or might be upgraded to mysql 8.
I am mostly thinking in security concerns.
Thanks,
·_-

Support
The 5.7 release is still maintained, and has support until October 2023. So there is nothing that makes it a bad idea to use it for Production.
However, if you are starting from scratch, using 8.0.23 (the newest release in the 8.0 chain) will get you some new features, and support until April 2026.
Security
If 8.0 was bleeding edge, a case could be made that it is likely to have more yet-to-be-discovered security vulnerabilities than 5.0, while the 5.0 release chain has had more time to find and patch them. But because 8.0 has been around for 2+ years, I think it is not likely to suffer from that problem. And other than bleeding-edge considerations, the newer version is probably more secure.
Performance
A benchmark from severalnines.com indicates that 8.0 is quite a bit faster than 5.7.

The benchmark results reveals that there has been an impressive improvement, not only on managing read workloads, but also on a high read/write workload comparing to MySQL 5.7.

Overall, MySQL 8.0 has dominated MySQL 5.7 efficiently.

General
And the MySQL marketing materials suggest that 8.0 is better in many ways.

MySQL 8.0 includes significant performance, security and developer productivity improvements

Summary
5.7.33 would be fine, but unless you have a specific reason to stick with 5.7.x, go to 8.0.