How to make a better Data model I am creating a database system using MySQL for a group in my school (a University). I am new to MySQL and its usage.I have started with the data modelling process, since this process will be the main foundation on which others will be built, It is necessary for me to know if I am heading towards the right direction.

Below is the business rule from the group.

1. They need a database that will store their members information. The
information should include Name and Level (thus their level of
study,we have level 100,200,300 and 400).

2. The database must store information about their executives. The information should
include the names of president, secretary and
treasurer for every year.

3. A member could hold an executive position for one year,He/She can hold one position
for more than one year ( so a member can be the
president for more than one year before he/she hands over to another
person), A member could also hold different executive position for
more than one year (so a member who is the secretary in year 1 can
be the president for year 2.

Below is the data model for the information above.

MEMBERS
--------------
MemID: SMALLINT

Name : Varchar(40)

Level: enum(100,200,300,400)

POSITIONS
----------------------
PosID: SMALLINT

PosName: Varchar(20)

EXECUTIVES
-----------------
MemID: SMALLINT-FK1

PosID: SMALLINT-FK2

AcademicYear: Year

As the years tick over will a member's level change?  Is it important to have history?  For Example Chris was Secretary while at level 100 and became President while in level 300?  If so you'll have to separate Level out of Members into a new table and make that the foreign key into Executives.


MySQL: Can not add foreign key into create table statement The queries below (assigned to php variables) seem to have an issue on the 2nd query, where I set the foreign key to the "couponId" column on the first table. My IDE lints "couponId" (inside the foreign key declaration) as well to show there's a issue.

Does anyone see what the issue is? Below is the error and the create function/query

// codeception is a test runner so I think it injects "temporary" into create statements

WordPress database error Cannot add foreign key constraint for query CREATE TEMPORARY TABLE IF NOT EXISTS wp_frequentVisitorCoupons_targets (
      targetId mediumint not null auto_increment unique,
      primary key (targetId),
      isSitewide tinyint(1) not null,
      targetUrl varchar(500),
      displayThreshold tinyint(5) not null default 20,
      offerCutoff tinyint(5),
      fk_coupons_targets mediumint not null unique,
      foreign key (fk_coupons_targets) references wp_frequentVisitorCoupons_coupons(couponId) on delete cascade
    ) made by Codeception\Application->run,  <continues on ...>

// the create table function
public static function createTablesIfNotExists() {
    global $wpdb;

  $createCouponTableQuery = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}fvc_coupons (
  couponId mediumint not null auto_increment unique,
  primary key (couponId),
  totalHits mediumint not null,
  isText boolean not null,
  fileName varchar(200),
  folderDateString varchar(7)
  )";

  $createTargetTableQuery = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}fvc_targets (
    targetId mediumint not null auto_increment unique,
    primary key (targetId),
    isSitewide tinyint(1) not null,
    targetUrl varchar(500),
    displayThreshold tinyint(5) not null default 20,
    offerCutoff tinyint(5),
    fk_coupons_targets mediumint not null unique,
    foreign key (fk_coupons_targets) references {$wpdb->prefix}fvc_coupons(couponId) on delete cascade
  )";

  require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
  dbDelta($createCouponTableQuery);
  dbDelta($createTargetTableQuery);

}

I am not sure about word press, but if you are using MySQL then you need to change your foreign key structure
Original:

foreign key (fk_coupons_targets) references wp_frequentVisitorCoupons_coupons(couponId)

After adjust:

foreign key fk_coupons_targets (col_name) references wp_frequentVisitorCoupons_coupons(couponId)

example:

ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name(columns)
REFERENCES parent_table(columns)

Note: if you want to add them foreign key to create table then you need to add only the last two lines


Select the greatest value from each column, even if one of the values is null I have a road_age_test table:

create table road_age_test (
    surface_year int,
    base_year int
);

insert into road_age_test (surface_year, base_year) values (10,20);
insert into road_age_test (surface_year, base_year) values (30,null);
insert into road_age_test (surface_year, base_year) values (null,40);
insert into road_age_test (surface_year, base_year) values (null,null);
insert into road_age_test (surface_year, base_year) values (50,50);

commit;

select * from road_age_test;

SURFACE_YEAR  BASE_YEAR
------------ ----------
          10         20
          30       null
        null         40
        null       null
          50         50

I would like to select the greatest value from each column, even if one of the values is null:

GREATEST_YEAR
-------------
           20
           30
           40
         null
           50

What's the simplest way to do this?

Note: Where both values are null, I want to return null, not 0.

select
GREATEST(nvl(surface_year,0),nvl(base_year,0)) as greatest_year
from
road_age_test;

--Edit using NULLIF
select
NULLIF(GREATEST(nvl(surface_year,0),nvl(base_year,0)),0) as greatest_year
from
road_age_test;

--Adding a segmented version for readability (some find it worse, eye of the beholder I guess)
select
NULLIF(
    GREATEST(
            nvl(surface_year,0),
            nvl(base_year,0) --Just keep adding columns here if you need to
            )
      ,0) as greatest_year
from
road_age_test;

Can't imagine it's much more efficient (and shouldn't be used in a where clause due to indexing), but it's a little cleaner than the case statement and allows you to add more values (columns) easily.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions060.htm


Using pgsql_fdw in a function result “ERROR: cache lookup failed for type 0” Now we want to use pgsql_fdw to select a table of  remote postgresql database，When we
select the table in a session it is okey ,but when we use the foreign table in a funciton
it turns out "ERROR:  cache lookup failed for type 0" ， anybody knows it ,thanks !

--1 base informaiton

skytf=> \d ft_test;
       Foreign table "skytf.ft_test"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(32) |
Server: pgsql_srv

skytf=> \des+ pgsql_srv
                                                List of foreign servers
   Name    | Owner | Foreign-data wrapper | Access privileges | Type | Version |                Options
-----------+-------+----------------------+-------------------+------+---------+----------------------------------------
 pgsql_srv | skytf | pgsql_fdw            |                   |      |         | {host=127.0.0.1,port=1923,dbname=mydb}
(1 row)

--2 destination table
mydb=> \d test
             Table "mydb.test"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(32) |
Indexes:
    "idx_test_1" btree (id)

--3 function
 CREATE or replace FUNCTION  func_sync_bill() RETURNS INTEGER  AS $$
    BEGIN

     begin
      insert into test_tf (id,name) select id,name from ft_test;
      return 1;
     end;
    END;
  $$ LANGUAGE 'plpgsql';

--4 it works in a session
skytf=> create table test_tf(id integer,name varchar(32));
CREATE TABLE

skytf=> insert into test_tf select * from ft_test;
INSERT 0 1990000

--5 function call error
skytf=> truncate table test_tf;
TRUNCATE TABLE

skytf=> select func_sync_bill();
ERROR:  cache lookup failed for type 0
CONTEXT:  SQL statement "insert into test_tf (id,name) select id,name from ft_test"
PL/pgSQL function "func_sync_bill" line 5 at SQL statement

when I call the function func_sync_bill() which will select a foreign table , it turns out the error.
  Is this a bug of pgsql_fdw?

--verbose meeage

    skytf=> \set VERBOSITY verbose
    skytf=> select func_sync_bill();
    ERROR:  XX000: cache lookup failed for type 0
    CONTEXT:  SQL statement "insert into test_tf (id,name) select id,name from ft_test"
    PL/pgSQL function "func_sync_bill" line 5 at SQL statement

LOCATION:  getTypeOutputInfo, lsyscache.c:2441

Someone recently submitted a formal bug report that looks identical to your question. Without any follow-up so far.

If the problem is a mismatch between pgsql_fdw implementation and the way the plpgsql interpreter prepares its queries, using EXECUTE might help:

EXECUTE 'insert into test_tf (id,name) select id,name from ft_test';

This forces plpgsql to avoid pre-processing the query, and it's somehow the generic workaround against this kind of errors.

But anyway it looks like pgsql_fdw is quite experimental at the moment, it's not included in the PostgreSQL contrib directory, and its source code is suprisingly not available for download at: http://sourceforge.net/projects/interdbconnect/files/ where we would expect it.
Currently, you may have more success with dblink, the "old way".


How to handle errors in a transaction in a stored procedure? I need to catch errors in a transaction in a stored procedure and log them in a table in the catch block.

After starting the transaction, a loop will attempt to insert a handful of values in the table.  I surround each insert statement with a try/catch block, so that if a Primary Key violation occurs on any one of the inserts, I handle the error by inserting a record in a log table.  I will then commit the transaction once the loop completes and it has attempted all inserts, even if one of them failed.

I'm concerned that calling SET XACT_ABORT ON at the beginning of the procedure will cause the transaction to be aborted when the PK error occurs, even though I'm catching and handling the error.  Is that true, or does the try/catch intercept the error and suppress it in a way that the transaction is not aborted?

If try/catch doesn't stop the error from aborting the transaction, then could I call SET XACT_ABORT OFF instead to get the error trapping behavior I need?

Here is some code to test the behavior:

First, create the target table and a log table:

CREATE TABLE ErrorTestTable (a int primary key clustered)
CREATE TABLE ErrorLogTable (m nvarchar(500), d datetime2(7))

Next, create the procedure:

CREATE PROCEDURE [dbo].[ErrorTest]
AS
BEGIN
    SET XACT_ABORT ON;
    SET NOCOUNT ON;

    DECLARE @Try int = 0;
    DECLARE @MaxTries int = 5;

    BEGIN TRAN;

    WHILE @Try < @MaxTries
    BEGIN

        BEGIN TRY
            print 'begin try - @@trancount: ' + cast(@@TranCount as nvarchar(max)) + '; xact_state: ' + cast(XACT_STATE() as nvarchar(max))
            INSERT INTO ErrorTestTable (a) VALUES (1)
            INSERT INTO ErrorLogTable(m,d) VALUES ('successfully inserted record!', sysutcdatetime());
        END TRY
        BEGIN CATCH
            PRINT 'begin catch - @@trancount: ' + cast(@@TranCount as nvarchar(max)) + '; xact_state: ' + cast(XACT_STATE() as nvarchar(max))
            INSERT INTO ErrorLogTable(m,d) VALUES ('pk violation!', sysutcdatetime());
        END CATCH
        SET @Try += 1;
    END

    COMMIT TRAN;

 END

Finally, run these statements and observe the behavior:

DELETE FROM ErrorLogTable
DELETE FROM ErrorTestTable
SELECT * FROM ErrorLogTable
SELECT * FROM ErrorTestTable
BEGIN TRY
   EXEC dbo.ErrorTest;
END TRY
BEGIN CATCH
   PRINT 'Error thrown by procedure.';
END CATCH
SELECT * FROM ErrorLogTable
SELECT * FROM ErrorTestTable

From testing a simple procedure, I've found a few things out. With SET XACT_ABORT ON, when the first primary key violation occurs, execution jumps to the catch handler. At that point, the transaction according to XACT_STATE is uncommitable (-1). Within the catch block, when I try to insert into the log table, another error is thrown and the stored procedure exits. I catch that error outside the sproc and select from the two tables, where I can then see the uncommitted data.

At that point the transaction is still not rolled back, and it's not until the whole batch is complete (including the 'select' calls after the sproc call) that it finally displays an error message saying

  Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

and finally rolls back the operations

Response to comments

  Why not just validate the information first, or do the error handling in the application calling the proc?

Already doing both. Not only validating, but taking out a custom application lock on the value-to-be-inserted, so lock contention is virtually non-existant, and PK-violations are basically impossible. I just want to know if I can handle the error if it were to occur. This is a very high-concurrency (100+ threads) rapid insert of "unused" random identifiers, so it has to not only check whether X random values are already in use, but settle on one to insert by attempting to take out a lock on the value. If, after successful lock acquisition, it still is not in use, I'll insert it.

Your problem stems from the fact that once the transaction becomes uncommittable (i.e. an error is raised) your loop does not honor the failure and continues to insert data into the table. When the DBE attempts to commit your subsequent changes it cannot because the transaction is no longer valid. Moving your TRY/CATCH outside of the WHILE loop resolves part of your problem.

CREATE PROCEDURE [dbo].[ErrorTest]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @error TABLE  (m nvarchar(500), d datetime2(7));
DECLARE @Try int = 0;
DECLARE @MaxTries int = 5;

BEGIN TRAN;
BEGIN TRY
    WHILE @Try < @MaxTries
    BEGIN

        print 'begin try - @@trancount: ' + cast(@@TranCount as nvarchar(max)) + '; xact_state: ' + cast(XACT_STATE() as nvarchar(max))
        INSERT INTO ErrorTestTable (a)
        VALUES (1);
        INSERT INTO ErrorLogTable(m,d)
        VALUES ('successfully inserted record!', sysutcdatetime());

        SET @Try += 1;
    END
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    PRINT 'begin catch - @@trancount: ' + cast(@@TranCount as nvarchar(max)) + '; xact_state: ' + cast(XACT_STATE() as nvarchar(max))
    INSERT INTO @error(m,d)
    VALUES ('pk violation!', sysutcdatetime());
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH

INSERT ErrorLogTable(m,d)
SELECT *
  FROM @error;
END
GO

However, once the transaction is rolled back any numbers entered during the transaction will be lost.

Also, from experience I have found problems issuing an INSERT statement from inside a CATCH block. Sometimes this INSERT can also be rolled back with the transaction. However, as you can see from my answer there is a work-around which is to declare a table variable, insert your custom state into that and then perform the logging after you have correctly handled the transaction. Any data inserted into the table variable will still be available after the transaction is committed/rolled back.

You could use this to capture the numbers that are being entered, and then when the transaction encounters the primary key violation you could reinsert the numbers from the tracked table up to the number that failed, like so:

DECLARE @numbers TABLE (a INT);
DECLARE @hasError BIT = 0;
BEGIN TRAN;
BEGIN TRY
    WHILE @value < @max
    BEGIN
        INSERT ErrorTestTable VALUES (@value);
        INSERT @numbers VALUES (@value); -- this will not be reached if an error occurs...
        SET @value += 1;
    END
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    SET @hasError = 1;
END CATCH

-- re-insert the numbers that were successful
IF @hasError = 1
BEGIN
    INSERT ErrorTestTable
    SELECT a
      FROM @numbers;
END

If you want to ensure that the subsequent numbers are entered, even if one number fails you will need to add logic outside of the transaction to do it. Personally I would rewrite the stored procedure to use a set-based operation instead of the manual loop and also include logic to try and avoid the primary key violation altogether, like so:

ALTER PROCEDURE [dbo].[ErrorTest] (
    @value INT OUTPUT,
    @max INT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @error TABLE  (m nvarchar(500), d datetime2(7));

BEGIN TRAN;
BEGIN TRY
WITH numbers
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY o.object_id) AS RN
      FROM sys.objects AS o
CROSS JOIN sys.objects AS p
)
INSERT ErrorTestTable
SELECT RN
  FROM numbers
 WHERE rn between @value and @max
   AND NOT EXISTS (SELECT * FROM ErrorTestTable WHERE a = RN);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
INSERT INTO @error(m,d)
VALUES ('pk violation!', sysutcdatetime());
IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
THROW;
END CATCH

INSERT ErrorLogTable(m,d)
SELECT *
  FROM @error;
END
GO


Create SQL job to send email in Excel format I am not skilled in SQL and need some help.  I have a SQL2005 server and I created a job to send an email with a CSV attachment. My job runs fine but I need the end result to be an Excel file in either .xls or .xlsx format.

Here is the code for the job:

USE My_DB
GO

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBMail'
    ,@recipients =  'me@my_org.com'
    ,@copy_recipients = ''
    ,@execute_query_database = 'My_DB'
    ,@query = 'exec MY_RPT_MonthlyWIPSp'
    --@body =@bodyContent
    ,@subject = 'Monthly WIP Report'
    ,@attach_query_result_as_file = 1
    ,@query_attachment_filename = 'result.csv'
    ,@query_result_separator=',',@query_result_width =32767
    ,@query_result_no_padding=1;

What do I need to change and where to get the data to come out in the format I need?  Do I have to modify my SP?

tl;dr I don't think what you want is an option with the tools you have available.

I do not believe it is possible with SQL 2005, to actually have your data export as .xls or .xlsx format.

Over the years I have tried with several different tools to do just this.  I did not have the option to select different tools.

In anycase, for most window devices opening a .csv file will open it in Excel.  Normally the .csv file is physically smaller then it would be as .xls or .xlsx, so there are some benefits to keeping it .csv.

Once you have the .csv file you can open it with Excel and save it as .xls or .xlsx.  I think that is your only option.

Update This is outside the parameters of the question but worthy of mention: There is a PowerShell module dfinke/ImportExcel that will allow you to convert table data to Excel even if you don't have Excel on the machine, and put in a folder.  I saw this in a demo of Dbatools and it looks really interesting.


Streaming replication and lag when performing VACUUM on master My streaming replication standby server is showing a delay of a few minutes when vacuuming a large table on the master.

What is the reason for the lag, and is there a way to reduce it?

Both servers have identical hardware on a 10GB network. Relevant settings are:

wal_buffers = 16MB
wal_writer_delay = 20ms
checkpoint_segments = 16
shared_buffers = 14GB
archive_timeout = 10
maintenance_work_mem = 1GB

Nick, based on the information we've gathered, I'm going to venture a guess, but if I'm wrong, come back and let us know and there may still be something we can do. I wouldn't say I'm confident about the internals of auto-vacuum processes on hot standby servers.

Streaming Replication, aka Log-Shipping

Streaming replication, hot standby, log shipping...there's a lot of different names that people use, but the general idea is that you replicate your back up server by implementing a system where the transaction logs from your primary server are sent and re-implmented on a secondary server to keep the two identical.

Now, you've stated that when you VACUUM your table on the primary, you have significant lag on your standby, and also that your configuration for log_autovacuum_min_duration is set to -1, which disables logging of autovacuum actions.  As a potential remedy, you could set this value to 0, indicating you wish to log all autovacuum operations.

My guess is that, while autovacuuming is occurring periodically on your master, since it isn't being logged, it isn't replicated on the standby.  When you run a manual VACUUM, only then does the logged VACUUM become replicated, which may be what's eating into your performance on the standby.

How are things if you run a VACUUM on the master, followed immediately by another VACUUM on master?  This will give us some indication of if I'm right or if I'm full of hot air.


Is deriving a value using multiple joins a good idea? I am working on a university project. I need to create a database from conceptual to implementation. That allows customers to create an order with multiple products the specification says that we should store the final cost.

I am having a few issues with this part of the specification, and the lecturer is not forthcoming with a definitive answer.

  Should this be derived from the total products in the order, or should it be entered in by the application logic when the order is complete?

Can anyone shed some light on how to go about this approach. Because currently I have made the assumption that because it says "database driven application" the whole logic needs to be provided by the DBMS. And I have created Views to achieve the joining of tables, because triggers seemed harder to ensure the integrity of the data.

If the specification states that you should store the final cost then you should store the final cost (even if this is is not normal form) but it is not saying anything about how you compute that cost before storing it.

If you are proposing that you compute the price each time you look at it you need to carefully consider if the base data could change over time. If a product can change price you would need to keep a full audit of the prices over time so you could find the right price when deriving the total. If you are maintaining such an audit already then this is "free" but if not it could be a significant amount of extra work to save storing the derived value at the point of sale. If course you could add an extra Entity between order and Product which stores the price paid for that particular product in that particular sale, which means you could easily implement ad-hoc price changes such as temporary "manager's special"s or one-off discounts.

To answer the direct question from your title: finding a values with many joins is fine as long as appropriate index structures are present to make this process efficient. Whether you are best doing this in the database or another layer of your application is subject to much debate and depends on your overall design philosophy and efficiency (follow the design philosophy to be consistent, except where that has significant impact on efficiency (though at this point question the philosophy too instead of just skirting around it this once)). It is probably more efficient for a low-load use case to have the database due the work as this is the sort for thing SQL as a query language was created for and the DB has all the information at its fingertips. For working at larger scales though pushing logic to the outer layers can be preferable because (in a DB server plus web server example) it is easier to scale the web server (a web far is relatively easy to create) than the database side (where you have a whole family of new issues like whether you need master/master replication or one of the other many options, and whichever option you chose how due you make sure every node is in sync at any given time and how much of a hit is acceptable to take to ensure full transactional consistency over all the DB nodes, is "eventually consistent" good enough, and so forth). As the DB side can be ore complex to scale out, there is a strong case for pushing as much logic as possible to outer layers and letting the DB be in charge of nothing more than data storage/retreval and core constraint policing (this also makes it easier to migrate between DB platforms if needed in future).

With regard to triggers, consider their flexibility against the fact that they can be harder to debug as they are essentially things that happen behind your code's back. They have their place, you need to decide if your project is one of those places. Stored procedures for saving are another option, as is moving the logic to another layer of the application.

Your lecturer is being deliberately cagey about your query, and I've made an effort to not give any straight answer but instead listed some "things to consider" (there may be more "things to consider", please try to think about it), because he/she wants you to come to a design decision by yourself to prove that you have an understanding of the general field rather than just an ability to follow specific instructions. Don't worry too much as long as you can offer some justification for the choices you make - in an academic test you will get marks for giving a less-than-optimal solution if you can somehow justify the choices you made and any you rejected. Be careful to state the assumptions you made and factors you thought of but discounted as not relevant to this implementation - if your assumptions are wrong then you'll still get some credit for a reasonable choice based on those iffy assumptions, if you make a bad choice because of bad assumptions (that would be good if the assumptions were correct) and don't document the assumptions you'll get neither half of the credit as the marker can't tell why you made the choice (bad input assumptions or just simple bad thinking).

Some lecturers are clue up enough to scan sites like this to see if people are asking questions about their assignments. Don't be tempted to try get an answer from here that you can just copy+paste as you'll get zero marks (or worse!) if it is noticed that is what you have done.


Backup plan for MySQL NDB cluster databse not innoDB I have a Database which will grow more than 250GB all data is in NDB engine(2 datanodes) and no other mysql engine used for data store.

What are the best approaches or plans for MySQL Cluster NDB database backup.
Is MySQL Enterprise Backup( part of MySQL Cluster CGE) or any other tool does backup for NDB?
Can a beginner like me do these backup of NDB easily or needs specialised skills?

Kind regards,

You should go through some more articles regarding cluster to make better understanding.
I have put here the steps of taking cluster backup and restore with 4 data node scenario.

1.MySQL Cluster Native Backup Tool (ONLINE Backup) and Restore

1.1

ndb_mgm> START BACKUP <Option>

<Option>:NOWAIT, WAIT STARTED, WAIT COMPLETED

NOWAIT - This returns a response to the display immediately and             begins the backup without waiting for responses from the data nodes.

WAIT STARTED - This returns a response to the display only when a response is returned from all the data nodes saying the backup has begun.

WAIT COMPLETED - This returns a response to the display only when a response is returned from all the data nodes saying that the backups have been completed successfully.
In most cases, using the START BACKUP WAIT COMPLETED is the best option:

 ndb_mgm> START BACKUP WAIT COMPLETED

Files that are created on Disk

1.1.1   .ctl - This group of files (labeled backup-..ctl) is          the metadata from the cluster for each data node.

1.1.2   .log - This group of files (labeled backup-..log)             contain the UNDO log files to keep the backup consistent and to ensure that no locks are needed during the backup process.

1.1.3   .Data - This group of files (labeled backup--                    ..Data) contains the data for the entire system.

1.2 To interrupt the backup in progress.

If the abort was successful, a message will be displayed stating this and issuing an error (which is to be expected).If a backup id is entered, but there is no backup occurring that is associated with that id, there is a response but without an error being issued:

ndb_mgm> ABORT BACKUP <backup id>
ndb_mgm> ABORT BACKUP 6

1.3  ndb_mgm> <node_id> REPORT BackupStatus

This command provides a status report on a cluster backup at anytime during the backup for node_id, or for all data nodes using ALL.

1.4  Restoring cluster Backup

1.4.1 Shutdown the cluster and then Restart Management Node

1.4.1.1

 ndb_mgm>SHUTDOWN

1.4.1.2

/usr/local/mysql/bin/ndb_mgmd

1.4.2   Start and empty the data nodes:-

Prior to restoring a backup, it is important to start and empty the data nodes.This is accomplished by starting each data node with the --initial option.

./ndbd  --initial

1.4.3   Restore the Cluster Backup

1.4.3.1 ndb_mgm> ENTER SINGLE USER MODE [Node-Id of First Free Slot]

1.4.3.2 In an operating system console, enter the following                     command from the /var/lib/mysqlcluster/BACKUP/BACKUP-1/ directory:

ndb_restore -m -r -b 1 -n 2   /* -- For First DataNode*/
ndb_restore -r -b 1 -n 3      /*-- For Second DataNode */

1.4.3.3

ndb_mgm> EXIT SINGLE USER MODE

2. MySQL Cluster Backup through MySQLDump Utility

2.1

shell> mysqldump -u root -p --lock-tables Dd_Name > Db_Name-backup.sql

2.2 Restoring mysqldump Files

shell> mysql -u root -p < Db_Name-backup.sql


Using a SSL-cert from a trusted CA offline to comunicate with SQL Server I'm setting up a secure link between client machines and an SQL database server on a intranet without Internet-access.

The question I have is if I can buy and use a cert from a trusted CA on a local offline intranet without getting any message boxes about things like being unable to verify the validity of the certificate, if it has been revoked etc?

If not, then maybee it's just as good to have a self-signed CA (where I'm sure to get a message about giving trust to a unknown CA when installing the cert...)

A trusted CA does not require online connectivity to validate the certificate. The certificate is signed with a chain of signatures that is rooted in a private key for which the corresponding public key is already present in your system (this is what 'trusted CA' means, after all). You may be thinking at the certificate revocation list, which must be periodically refreshed online, but the system will work with even if the CRL cannot be refreshed.

Within an intranet there are options to use a corporate CA, but it does require a domain and PKI deployed.

Self-signed certificates with SQL Server provide only encryption, no server authentication. See Using Encryption Without Validation. There are no dialogs to popup for the clients, your connection attempt succeed or fails depending on a number of factors (read the article linked).


RMAN backups, incremental, not compressed: estimate size? We´re setting up RMAN to backup a database over NFS and ZFS, for which I have to create shares and mounting points (4 shares in my case).

I am trying to anticipate the size of the shares, which should be the maximum size of the backup. We´re going to proceed with an initial backup level 0 and then we´ll do daily level 1 backups.

Is there a mathematical formula for me to know the maximum backup size at the end, using x as the database size?

Thank you,

S.

In short: Not really.

An RMAN backupset (e.g. BACKUP DATABASE) will backup up any blocks in your datafiles that have never been used.  This is different from empty blocks.  There's no way that I am aware of to differentiate between blocks which are currently empty and blocks which are unused.

From: http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmcncpt.htm#BRADV89481:

  "RMAN always skips blocks that have never been used"

If you were to do an image copy (e.g. BACKUP AS COPY DATABASE), then this is essentially a direct copy of the datafiles.  So if the datafile making up your system tablespace was called system.dbf, RMAN essentially copies this whole file.  This means your backup will be the same size as your datafiles.


Can we tell dbcc to ignore subnormal real values? Currently dbcc DATA_PURITY complains about subnormal real values in some of my columns, such as 7.561407E-42. I would rather keep them in my tables, and ask dbcc to ignore them.

I am OK with the check on upper limit, and other checks on other column types. So I would rather keep running the utility, but have it stop complaining about legitimate very low values.

Is there a way to accomplish that?

The whole purpose of DBCC CHECKDB WITH DATA_PURITY is to check the database for column values that are not valid or are out-of-range.

In sql server 2000, it was possible to store out-of-range values in columns of certain datatypes like Float,real, datetime, etc. But moving from SQL 2005 and up, SQL Server will flag out those out-of-range values as it follows Single-precision floating-point format. Check @PaulWhite's answer here.

From BOL :

  DBCC CHECKDB detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.

You must fix those errors reported as described in KB923247.


Difference between "Table-Incremental" and "Table-Update" for "Population Type" when scheduling Full Text Index Population I want to schedule a Full Text Index's population to keep it more up to date rather than leaving it to Automatic and not knowing the next population time
as the users are complaining about the data they enter would not show up until too long a time has passed.
For this, I right click on the table on which Full Text Index is defined and go to Schedules. It asks to choose the "Population Type". "Table-Incremental" and "Table-Update" are two of the options. I don't want to go with the "Table-Full" option as it might consume a lot of resources.
What is the difference between these two ("Table-Incremental" and "Table-Update") options?
Thank you.

By default the Full-Text Engine uses automatic population on a full-text index and changes are tracked as data is modified in the base table, and the tracked changes are propagated automatically. The full-text index is updated in the background.

For incremental population your table must have a column of the timestamp data type. At the end of a population, the Full-Text Engine records the largest timestamp value and will use that for next incremental population

Table-Update will use change tracking to maintain a full-text index after its initial full population. The SQL Server will then maintain a record of the rows in the base table or indexed view that have been modified by updates, deletes, or insert and will use this information to populate the Full Text Index. This comes with an overhead.

In most cases you are better off using Automatic updates, but if you have a table that's frequently updated and you have a timestamp column a manual Table-Incremental can help. (https://technet.microsoft.com/en-us/library/ms142575%28v=sql.100%29.aspx)


How to measure I/O contention after adding calculated fields on indexed views to a table in SQL Server? I have successfully added several calculated fields to different tables, for example:

ALTER TABLE dbo.tblBGiftVoucherItem
ADD isUsGift AS CAST(
    (
        ISNULL(
        CASE
            WHEN sintMarketID = 2
                AND strType = 'CARD'
                AND strTier1 LIKE 'GG%' THEN 1
            ELSE 0
        END
        ,0)
    ) AS BIT)
PERSISTED;

This doesn't seem to have had any impact on write performance on the involved table, in this case dbo.tblBGiftVoucherItem.

However, when I have added indexed views, especially those which touch more than one table with an INNER JOIN, I have noticed a considerable increase on the number of deadlocks directly related to the tables in question.

What DMVs can I use that will give me an indication of I/O pressure by database object?

For example the table dbo.tblBGiftVoucherItem from the example above.

Is there any way I can measure/monitor how long the storage engine is taking to deal with writing requests specifically related to this table?

If I knew this I would have a better idea of how many calculated fields I could add to the table, and the cost involved in each of them.

Same with the indexed views.

What DMVs can I use that will give me an indication of I/O pressure by database object?

Probably the closest is sys.dm_db_index_operational_stats. This gives low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.

Adding persisted computed columns typically has little impact, though they will add the usual extra costs if indexed. This is just the normal trade-off for adding an index though, and not specific to the underlying column being computed.

Using indexed views will tend to increase the chance of deadlocks, because indexed view maintenance has to be performed internally at the serializable isolation level (which typically acquires transaction-length key-range locks) to guarantee consistency when more than one table is referenced in the view.

Related question: How, when and why does implicit changing of transaction isolation level happen?

Indexed views can also increase the risk of incompatible locks if the view contains aggregation, because this has the effect of concentrating data ranges in the underlying tables into fewer rows in the indexed view.

There is always a trade-off when adding indexes and indexed views. In many cases, the deadlock risks can be minimized through careful design and attention to detail with respect to the execution plans generated for statements that modify the additional indexes.

My other piece of advice would be to look at investing in a commercial performance monitoring tool. It is possible to write your own based on DMVs, but it is not always easy, and much of the hard work (including presentation and historical reporting) has already been done in these products.


Alter postgres password from python? When I run this query from psql shell everything works fine:

ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'somePassword';

but when I try exactly the same query from python (with psycopg2 package)
authentication fails when I try to connect with the password.

what can go wrong? I tried simple passwords with no non-ansi characters etc...

You have two options when running code from Python,

Python has a method called autocommit you either have to turn this on,
or, you have to run a manual COMMIT; after you issue the command.

Without either of those the command will automatically ROLLBACK;

answer inspired by @a_horse_with_no_name in the comments


Locking a table and disabling the indexes hangs mysql - 100% CPU CREATE TABLE `runs` (
  `RunID` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`RunID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `runs`
--
-- WHERE:  runid=430864838

LOCK TABLES `runs` WRITE;
ALTER TABLE `runs` DISABLE KEYS;
INSERT INTO `runs` VALUES (430864838);

As soon as ALTER TABLE `runs` DISABLE KEYS; runs it seems to hang the database - a core is 100% being used and it will not respond.

What is the cause of this behavior?

I switched over to Mariadb and successfully ran the query.


Permissions for Edit Top X Rows in SSMS What are the minimum permissions needed to allow Edit Top 200 in SQL Server Management Studio?

Edit Top 200 Rows is simply a GUI way of performing INSERT, UPDATE or DELETE operations on a table, so you only require the same permissions you would require for performing these actions via T-SQL scripts.

The minimum required is the following:

Contained Database User or SQL Server Login mapped to Database User
VIEW ANY DATABASE on server to Login
GRANT SELECT* on table to User
GRANT UPDATE** on table to User
GRANT INSERT** on table to User
GRANT DELETE** on table to User

* SELECT is required because when you click Edit Top 200 Rows, SSMS must first SELECT the top 200 rows to display them for editing.

** You only need to grant the permission for the action you want users to be able to perform.

This is the minimum requirement for a single table. To allow Edit Top 200 Rows on all tables, you can either grant the permissions to all tables individually or use a built-in role such as db_datawriter.

EDIT: As per David Browne's comment, you also need VIEW ANY DATABASE to see the DB in SSMS. By default, this is granted via membership in the Public server role during login creation, however if the Public role membership has been revoked, you'll need this explicit permission.


pass selected variable value to inside select query MySQL i have two tables connected with one to many relationship. i have to sort parent table data according to child table data.
tables :
product
---------------------------
|id | name | default_price |
---------------------------

variant
-----------
|id | name|
-----------

product_stock
------------------------------------------------
|id | product_id | variant_id | price |available
------------------------------------------------

one product can have many product_stock rows.
I just need to sort product by product_stock minimum price
here what i did:
SELECT id,(SELECT price FROM `product_stock`
             WHERE product_id = 1
             ORDER By price LIMIT 1) as price
    from product ORDER BY price

this is working for only product id 1 . i need to pass dynamically selected product id  to it . is this possible? thank you

SELECT product.*
FROM product
JOIN ( SELECT product_id id, MIN(price) price
       FROM product_stock
       GROUP BY 1 ) min_prices USING (id)
ORDER BY min_prices.price

If some products are absent in product_stock they won't be listed. If you need in them then use LEFT JOIN.


New SQL Server 2008 instance, load old instance DB's BACK STORY:
I'm a developer with a local instance of SQL Server 2008 on my workstation. I just recently upgraded my motherboard and found out (much to my dismay) the old motherboard's RAID drivers apparently are not compatible with the new mother board so essentially, I'm unable to use the Windows instance I had with the old motherboard. Luckily I have a full backup of all it's contents.

My problem:
I want to install SQL Server 2008 on my new box but I want to load the old data files. I have quite a few databases I don't want to recreate. I have all the original MDF's and LDB's but no actual SQL Server "backups".

Is there a way I can do this?

Thanks for your help.

You can actually attach a database file.

CREATE DATABASE MyAdventureWorks
    ON (FILENAME = 'C:\PathToDataFile\DataFileName.mdf'),
    (FILENAME = 'C:\PathToDataFile\LogFileName.ldf')
    FOR ATTACH;

Modify the paths with the paths to your files. The trick is the FOR ATTACH clause.

You can attach a database by right-clicking on the Databases node then selecting the Attach item.

References:

How to: Attach a Database File to SQL Server Express

Attach a Database


Converting SQL Server queries to PostgreSQL I am trying to convert the following SQL Server T-SQL block to a PostgreSQL compatible one using, preferably CTE or a function. The issue with converting is I am not able to figure out how I return the last inserted id (scope_identity() in SQL Server returns the last inserted id in the identity column).
 IF (
        NOT EXISTS (
            SELECT p_id
            FROM qr_request
            WHERE q_ota = 'Done'
                            )
        )
BEGIN
    INSERT INTO qr_request (
        u_id
        ,c_id
        ,r_id
        ,q_ota
        )
    VALUES (
        1
        ,2
        ,3
        ,'P'
        )

    COMMIT

    SELECT Scope_Identity() AS id
        ,p.email AS n
    FROM person p
    WHERE p.uid = 33
        AND deleted = 0

END
ELSE
    SELECT NULL AS id
        ,p.email AS n
    FROM person p
    WHERE p.uid = 33
        AND deleted = 0

Any help would be appreciated.

Since PostgreSQL doesn't use the same traditional "ID" concept on Tables like SQL Server, rather it uses Sequences, getting the last value is a little different but there are a multitude of ways to do so as noticed in this well versed StackOverflow answer.
Specifically the functions you can leverage are CURRVAL and LASTVAL.
Relevant info from the aforementioned and linked StackOverflow answer:
CURRVAL():
INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
  SELECT currval('persons_id_seq');

The name of the sequence must be known. Caveat: currval() only works
after an INSERT (which has executed nextval() ), in the same session.

LASTVAL():

This is similar to the previous, only that you don't need to specify
the sequence name: it looks for the most recent modified sequence
(always inside your session, same caveat as above.)


Default value for UUID column in Postgres In Postgres 9.x, for a column of type UUID, how do I specify a UUID to be generated automatically as a default value for any row insert?

tl;dr

Call DEFAULT when defining a column to invoke one of the OSSP uuid functions. The Postgres server will automatically invoke the function every time a row is inserted.

CREATE TABLE tbl
(
  pkey UUID NOT NULL DEFAULT uuid_generate_v1() ,
  CONSTRAINT pkey_tbl PRIMARY KEY ( pkey )
)

If you already use the pgcrypto extension, consider the Answer by bpieck.

Plugin Required To Generate UUID

While Postgres out-of-the-box supports storing UUID (Universally Unique Identifier) values in their native 128-bit form, generating UUID values requires a plug-in. In Postgres, a plug-in is known as an extension.

To install an extension, call CREATE EXTENSION. To avoid re-installing, add IF NOT EXISTS. See my blog post for more details, or see this page in StackOverflow.

The extension we want is an open-source library built in C for working with UUIDs, OSSP uuid. A build of this library for Postgres is often bundled with an installation of Postgres such as the graphical installers provided by Enterprise DB or included by cloud providers such as Amazon RDS for PostgreSQL.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Generating Various Kinds Of UUID

See the extension’s doc to see a list of multiple commands offered for generating various kinds of UUID values. To get the original version of UUID built from the computer’s MAC address plus current date-time plus a small random value, call uuid_generate_v1().

SELECT uuid_generate_v1();

  672124b6-9894-11e5-be38-001d42e813fe

Later variations on this theme were developed for alternate kinds of UUIDs. Some people may not want to record the server’s actual MAC address, for example, for security or privacy concerns. The Postgres extension generates five kinds of UUIDs, plus the “nil” UUID 00000000-0000-0000-0000-000000000000.

UUID As Default Value

That method call can be made automatically to generate a default value for any newly inserted row. When defining the column, specify:

  DEFAULT uuid_generate_v1()

See that command used in the following example table definition.

CREATE TABLE public.pet_
(
  species_ text NOT NULL,
  name_ text NOT NULL,
  date_of_birth_ text NOT NULL,
  uuid_ uuid NOT NULL DEFAULT uuid_generate_v1(),  -- <====
  CONSTRAINT pet_pkey_ PRIMARY KEY (uuid_)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.pet_
  OWNER TO postgres;

UUID versions

The uuid-ossp plugin can generate various versions of UUID.

uuid_generate_v1()Contains MAC address of current computer + current moment. Commonly used, but avoid if you are sensitive about disclosing the MAC of your database server or the time when this value was generated. Defined by specification as a Version 1 UUID.
uuid_generate_v1mc()Like Version 1, but with a random multicast MAC address instead of the real MAC address. Apparently a way to use Version 1 but substituting another MAC rather than the actual MAC of your database server if you are sensitive about disclosing that fact.What is a ‘random multicast MAC’? I do not know exactly. After reading section 4.1.6 of RFC 4122, I suspect this is a random number used in place of the MAC but with bits set to indicate a multicast MAC address rather than the usual unicast so as to distinguish this variation of Version 1 from a usual real-MAC Version 1 UUID.
uuid_generate_v3( namespace uuid, name text )Contains an MD5 hash of text you provide. Defined by specification as a Version 3 UUID, namespace-based UUID.
uuid_generate_v4()Based on randomly-generated data for 121-122 of the 128 bits. Six or seven bits used to indicate Version & Variant. This kind of UUID is practical only if implemented with a cryptographically-strong random generator. Defined by specification as a Version 4 UUID.
uuid_generate_v5( namespace uuid, name text )Same as Version 3 but using SHA1 hashing. Defined by specification as Version 5 UUID.
uuid_nil()A special case, all bits set to zero 00000000-0000-0000-0000-000000000000. Used as a flag for an unknown UUID value. Known as a nil UUID.

To compare types, see Question, Which UUID version to use?

If you are interested only in Version 4 (randomly generated), and are already using pgcrypto, see the Answer by bpieck.

If you are curious about Versions 3 & 5, see this Question, Generating v5 UUID. What is name and namespace?.

For more discussion, see my Answer to a similar Question and my blog post UUID values from JDBC to Postgres.


Index usage on a temporary table I have two rather simple queries. The first query

 UPDATE mp_physical SET periodic_number = '' WHERE periodic_number is NULL;

and it's plan

 duration: 0.125 ms  plan:
    Query Text: UPDATE mp_physical  SET periodic_number = '' WHERE periodic_number is NULL;
    Update on mp_physical  (cost=0.42..7.34 rows=1 width=801)
      ->  Index Scan using "_I_periodic_number" on mp_physical  (cost=0.42..7.34 rows=1 width=801)
            Index Cond: (periodic_number IS NULL)

And the second one:

 UPDATE observations_optical_temp SET designation = '' WHERE periodic_number is NULL;

and it's plan:

duration: 2817.375 ms  plan:
    Query Text: UPDATE observations_optical_temp SET periodic_number = '' WHERE periodic_number is NULL;
    Update on observations_optical_temp  (cost=103.55..9223.01 rows=5049 width=212)
      ->  Bitmap Heap Scan on observations_optical_temp  (cost=103.55..9223.01 rows=5049 width=212)
            Recheck Cond: (periodic_number IS NULL)
            ->  Bitmap Index Scan on "_I_per_num_temp"  (cost=0.00..102.29 rows=5049 width=0)
                  Index Cond: (periodic_number IS NULL)

I expect the second plan shold be the same the first one. But it's not. Why?
Here are dumps of the tables.

CREATE TABLE public.mp_physical(
    id_mpp serial NOT NULL,
    id_comet_parts integer,
    "SPK_id" public.nonnegative_int,
    designation varchar(30),
    name varchar(100),
    prefix varchar,
    "is_NEO" bool,
    "H" double precision,
    "G" double precision,
    diameter public.nonnegative_double,
    extent varchar(30),
    extent_error public.nonnegative_double,
    geometric_albedo public.nonnegative_double,
    rot_per public.nonnegative_double,
    "GM" public.nonnegative_double,
    "BV" public.nonnegative_double,
    "UB" public.nonnegative_double,
    "spec_B" varchar(30),
    "spec_T" varchar(30),
    lca double precision,
    multiplicity public.nonnegative_int,
    polar_ang double precision,
    polar_slope_ang double precision,
    a double precision,
    b double precision,
    mass public.nonnegative_double,
    mp_type public.mp_type NOT NULL,
    periodic_number varchar(5),
    diameter_method_def varchar(200),
    discovery_info text,
    "H_sigma" public.nonnegative_double,
    "G_sigma" public.nonnegative_double,
    diameter_sigma public.nonnegative_double,
    geometric_albedo_sigma public.nonnegative_double,
    rot_per_sigma public.nonnegative_double,
    "GM_sigma" public.nonnegative_double,
    "BV_sigma" public.nonnegative_double,
    "UB_sigma" public.nonnegative_double,
    lca_sigma public.nonnegative_double,
    a_sigma public.nonnegative_double,
    b_sigma public.nonnegative_double,
    polar_ang_sigma public.nonnegative_double,
    mass_sigma public.nonnegative_double,
    CONSTRAINT "_C_id_ap" PRIMARY KEY (id_mpp)
);

CREATE INDEX "_I_name" ON  mp_physical  USING btree (name);
CREATE INDEX "_I_designation" ON mp_physical USING btree(mpp_designation);
CREATE INDEX "_I_periodic_number" ON mp_physical USING btree(periodic_number);
CREATE INDEX "_I_mp_type" ON mp_physical USING btree(mp_type);

And

  CREATE TEMPORARY TABLE "observations_optical_temp"(note_1,date,"RA","Dec",magnitude,band,id_observatory,id_mpp,"Dec_degree",observatory_code,periodic_number,mpp_designation,mp_type)
AS SELECT note_1,date,"RA","Dec",magnitude,band,id_observatory,id_mpp,"Dec_degree",'1'::varchar(3),'1'::varchar(8),'1'::varchar(30),'A'::public.mp_type FROM observations_optical;

CREATE TABLE observations_optical(
    id_obs_o bigint
    note_1 varchar,
    date timestamp NOT NULL,
    "RA" time NOT NULL,
    "Dec_degree" integer NOT NULL,
    "Dec" time NOT NULL,
    magnitude double precision,
    band varchar,
    id_observatory integer,
    id_mpp integer,
    CONSTRAINT "_PK_id_obs_o" PRIMARY KEY (id_obs_o)
);
CREATE INDEX "_I_temp_1" ON observations_optical_temp USING btree(mpp_designation);
CREATE INDEX "_I_temp_2" ON observations_optical_temp USING btree(periodic_number);
CREATE INDEX "_I_temp_3" ON observations_optical_temp USING btree(mp_type);

The choice between index scan and bitmap index scan is basically decided by how many rows per data page Postgres expects to retrieve - which depends on statistics about data distribution in the table and selectivity of your query predicates. And by the expected cost of random memory access - which is defined by cost settings, most prominently random_page_cost and effective_cache_size.
If Postgres expects to find enpugh rows on the same data page, it switches to bitmap index scan, which is more efficient for this kind of physical data distribution. (If most data pages are retrieved, a lowly sequential scan is faster anyway.) So even if columns and indexes of your two tables look all the same, you may still get these different query plans depending on data distribution and selectivity of your query predicates.

Optimizing queries on a range of timestamps (one column)
"Recheck Cond:" line in query plans with a bitmap index scan

But your tables are very different to begin with. mp_physical has much wider rows, so that only few rows are located on the same data page (default 8kb). This heavily favors index scans, since bitmap index scans won't buy much (if anything) even when retrieving a higher percentage of all rows.
Also, be aware that temporary tables are not covered by autovacuum and hence not analyzed automatically. You may have to do this manually to get accurate table statistics for the query planner to work with:
ANALYZE observations_optical_temp;

Are regular VACUUM ANALYZE still recommended under 9.1?


Unable to start the services of SQL server after Downgrading from Enterprise to Standard We are performing the downgrade activity from SQL server 2008 R2 Enterprise to Standard.

After the uninstall and re-install the standard version, (once the saved system databases were copied to the new installation location), we are unable to start the SQL service which was running under local system account:

Error from event viewer:

  Error 2(The system cannot find the file specified.) occurred while
  opening file 'C:\Program Files\Microsoft SQL
  Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf' to obtain
  configuration information at startup. An invalid startup option might
  have caused the error. Verify your startup options, and correct or
  remove them if necessary.

Earlier before downgrade these system databases were on D:\, but i believe that may not be the cause because we have not witnesses this earlier during the previous times when we downgraded.

Please suggest what possibly could have gone wrong here:

Thanks

Go to SQL Server Configuration Manager, highlight SQL Server Services in the left side, then right-click this instance on the right side and choose Properties. On the Startup Parameters tab, make sure the locations for -d, -e, and -l are updated and correct. Right now it sounds like they are set to the defaults, which won't work if you've moved the system databases to D:

Also it's not clear if you followed the proper process to move the system databases in the first place. Please go through these articles and verify that you done this correctly:

Move System Databases
Move all SQL Server system databases at one time
How to move the Master database


Filestream database backup error We have DatabaseA that has filestream files in it. We then have an archive process which first deletes files in DatabaseB (different server) and then inserts new files from DatabaseA into DatabaseB via SSIS.

Each night we try to backup the database and we get the following error:

  The operating system returned the error '1392 (The file or directory is corrupted and unreadable.)' while attempting 'FindNextFile' on \foldername guid.
  [SQLSTATE 42000] (Error 3634)
  BACKUP DATABASE is terminating abnormally.
  [SQLSTATE 42000] (Error 3013). The step failed.

When I look at the file path and folder name, the name is not the same as the folder name guid reported in the error. When we reorganize the index on the filestream guid column, we are able to backup the database. How do we recover from this corruption? DBCC checkdb? Is there something else we should be doing with filestream enabled database backups or the way that we are deleting the rows with the filestream column?

We are using native SQL Server

Backup command:

  BACKUP DATABASE
  to disk = '\share\backupshare\databasename\databasename.bak'
  with noformat, init,
  name='databasename-full database backup',
  skip, norewind, nounload, compression, stats 10, checksum

FindNextFile is a Windows API call, so what's happening is the backup process is enumerating all the files in the FILESTREAM folder and backing them up. The error message indicates that the operating system cannot read the file system; this indicates, at least to me, that the file/folder structure on disk is corrupt. There would be a different error returned if the file/folder was simply not found. This may mean the I/O subsystem has problems. I would run chkdsk in Windows on that volume.

From the DBCC CHECKDB documentation, it doesn't look like FILESTREAM checks go beyond ensuring there is linkage between the database and the file system. Running chkdsk has the ability to do a low-level check of the disk contents itself, which is probably what's needed in this scenario. It certainly wouldn't hurt to run CHECKDB on the database anyway (read: regularly) either.


Select lowest ID and calculate deviation from the lowest I'm trying to create a view. I have some data in my SQL Server database, and I want to order them by their mark1 and mark2. Now when I sort them I want to select the mark1 who has the lowest mark2 and calculate what's the deviation from other mark1s.
The table is looking right now like this, but without deviation.

|   mark1    |    mark2    |      mass    ||    deviation |
|:-----------|------------:|:------------:||:------------:|
| 0001       |      10000  |     10,0     ||    min0001   |
| 0001       |      15000  |     12,0     ||    +20%      |
| 0002       |      11200  |     18,0     ||    min0002   |
| 0002       |      13000  |     35,5     ||    +97.2%    |
| 0002       |      14565  |     85,5     ||    375%      |
| 0003       |      15004  |     15,4     ||    min0003   |
| 0003       |      21250  |     17,4     ||    +12.9%    |
| 0003       |      60764  |     16,4     ||    6.49%     |

And now, as you see it's ordered by mark1 and mark2.
Now, my problem is the deviation column, I need it to take mark1, Let's say 0001 (his mark2 is 10000) and find the lowest mass and find the deviation from other 0001 (15000). As you can see the in the table.

FIRST_VALUE (Transact-SQL)

  Returns the first value in an ordered set of values in SQL Server
  2014.

Partition on mark1 and order by mark2.

Something like this should do it for you:

select T.*,
       100 * (T.mass - T.FirstMass) / T.FirstMass as Deviation
from (
     select T.*,
            first_value(T.mass) over(partition by T.mark1 order by T.mark2) as FirstMass
     from dbo.YourTable as T
     ) as T;

SQL Fiddle


What is the difference between Primary key and Super key in DBMS I am new to DBMSs and I am still learning the theory.

I am getting really confused with this key business and after googling I have narrowed it down to just 2 keys I don't get (primary and super key).

I have a few questions on DBMS. I would be grateful if you can answer them for me.

1) What is the difference between Primary key and Super key in DBMS?
Highly appreciate it if you can use a comprehensive example to explain properly

2) Can Primary key and Super key both have multiple columns combined to form Primary key and Super key?

3) Is Primary key a subset of Super key or vice versa?

A Super Key is simply a non-minimal Candidate Key, that is to say one with additional columns not strictly required to ensure uniqueness of the row.

A Primary Key is a minimal Candidate Key, which is to say all constituent columns are strictly required in order to ensure uniqueness.

As a database developer/designer of 30 years experience, I had never even heard the term Super Key until I saw this question, and looked it up. The concept of Super Key seems more germane to the topic of performance and Physical Schema design as it directly maps to the concept of a unique nonclustered index with additional columns for improved query covering.


SQL Server 2014 CTP 2014 expiration date I installed SQL Server 2014 CTP2 on a server and I have been testing the new features in SQL Server 2014. On the web site where I down loaded the software I notice the statement "expires on March 31, 2014". Can you explain what this means? Will we still be able to test SQL Server 2014 CTP2 after that date or does the SQL Server engine stop working?

The service will not startup after the date mentioned.  Prior to that date, you will need to obtain the SQL Server 2014 RTM to continue using the product past the end of March.

This is a bit of conjecture on my behalf, but since Microsoft almost never releases a third community technology preview (CTP) edition of any product, they are highly likely to make the RTM version available to MSDN subscribers within the next week or two, and then to everyone shortly thereafter and certainly before the end of March.

Just announced; Microsoft has RTM'd SQL Server 2014 for GA on April 1st.  No joke.


Does Postgres read blocks sequentially, even if a disk is high-latency? I'm using Postgres on AWS EBS, when running a query that uses a bitmap heap scan and a bitmap index scan, the execution time seems proportional to the number of "heap blocks" read, as shown by the EXPLAIN ANALYZE (I've not 100% verified this). Because EBS is networked attached storage, it has a high (~1ms) latency, but a high throughput/bandwidth, meaning Postgres wouldn't be taking full advantage of the disk.
Is it the case that when reading from Disk postgres queries blocks/pages sequentially?

In the case of bitmap heap scan, it can issue async block reads.  This is controlled by effective_io_concurrency.


Mapping SQL Server integer to Oracle Datatype Well I know, that an SQL Server integer is best represented by NUMBER(10).

But sometimes I have migrate some maintenance scripts to Oracle and I wonder if I must carefully
map integer to NUMBER(10) or if I can just use the ansi type integer in Oracle.

Is there any reason not to use integer?

EDIT:

I verified it against 10g.

create table BK_int_test(
i_col integer,
f_col float);

insert into BK_int_test values (1, 1);
insert into BK_int_test values (1.1, 1.1);

Select * from BK_int_test;

and got

     I_COL      F_COL
---------- ----------
         1          1
         1        1,1

2 rows selected.

Thanks that saves a lot of work.

The short form int of integer is supported as well.

I don't see any reason to not use integer in Oracle. It will be mapped to the approriate Oracle datatype but keeps the DDL at least somewhat portable.

I also always use VARCHAR instead of VARCHAR2 as they are equivalent anyway. Oracle does not recommend this, as in the future this might change, but this has been true since version 6, so I doubt that it will actually change at all ;)


Is using the rename database feature in PHPMyAdmin safe? I know the rename database functionality has been taken out of MySQL. My question is, does renaming it in PHPMyAdmin work and is safe? I know they have written functionality to deal with a number of issues that can arise but I haven't found any commentary on whether it's safe.

By safe I mean no data/structure loss.

Thank you

According to this stack overflow answer , there might be data loss. The same is confirmed via MySQL official document here. Please correct me if something is missing from my point of understanding.


Can't create/write to file '/var/log/mariadb/mariadb.log' I'm troubleshooting a MySQL no-start on CentOS 7 VM. It is a bit unusual to see this since we have not made any configuration changes. The manual start command was taken from systemd logging. The error is the age old:

# /usr/bin/mysqld_safe --basedir=/usr
181221 17:42:49 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
181221 17:42:49 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
/usr/bin/mysqld_safe_helper: Can't create/write to file '/var/log/mariadb/mariadb.log' (Errcode: 13)

According to Red Hat Bugzilla – Bug 1061045, mysql:mysql needs the appropriate permissions, which we seem to have:

[root@ftpit w]# ls -Al /var/log
total 52764
...
drwxrwx---  2 mysql mysql      4096 Aug 16 11:05 mariadb

And:

[root@ftpit w]# ls -Al /var/log/mariadb/
total 99852
-rw-rw---- 1 mysql mysql 102138776 Dec 21 17:16 mariadb.log

And:

[root@ftpit w]# grep mysql /etc/passwd
mysql:x:27:27:MariaDB Server:/var/lib/mysql:/sbin/nologin

I also tried g-w as shown in the bug report.

Where should I look next for this error? Or how do I fix it?

Is SELinux running in enforcing mode? Run sestatus to find out. If so, it should be logging to the /var/log/audit/ directory. You can do grep mysql /var/log/audit/* to see if there are any problems related to "mysql" i.e. MariaDB.

Also, the correct privileges and SELinux context for the log directory and files are:

# ls -ldZ /var/log/mariadb/ drwxr-x---. mysql mysql
system_u:object_r:mysqld_log_t:s0 /var/log/mariadb/
# ls -ldZ /var/log/mariadb/mariadb.log
-rw-r-----. mysql mysql system_u:object_r:mysqld_log_t:s0 /var/log/mariadb/mariadb.log

For instructions on how to set the right SELinux context, refer to the documentation: What to Do if MariaDB Doesn't Start - SELinux and MariaDB Path Changes.


Help with good RDBMS transactional schema design that mimicks a sports league I need to design a data model for a typical RDBMS that will mimic the structure of your typical sports league.  Architecturally my requirements are:

Well normalized
Transactional
Application developers can model it reasonably well with popular ORM tools

Attributes of the desired schema:

There can be many Leagues.
There may exist 1 or more Divisions in a single League.
There may exist 1 or more Conferences in a single Division.
There may exist 1 or more Teams in a single Conference.
There may exist 1 or more Teams in a Division if and only if the Division has not been partitioned into Conferences (In other words, a Division does not need to be split into Conferences and instead a Team may have a reference directly to a single Division instead of a Conference)
There may exist 1 or more Players in a single Team.
Players exist in 1 and only 1 Team.

This would be simple enough to do and maintain referential integrity if the League structure was to be rigid.  Here is what I was thinking, but I wondered if this would be a good design or if there is a better approach for this situation.

League Table: ...
LeagueGroup Table: FK - LeagueId NOT NULL, FK - LeagueGroupParentId NULLABLE, LeagueGroupType
Team: FK - LeagueGroupId NOT NULL
Player: FK - TeamId NOT NULL

So basically I was thinking that Divisions and Conferences would be the same table with a self-referential foreign key looking upwards for its grouping parent.  The advantage of this is that league grouping can actually go N deep.

Disadvantages are that this might not be very easy or useful to map in an ORM framework.  Further the application developers might need to write a bit of logic to build this into tree like object structure that is useful for their purposes.  Further still, without a trigger of some kind, I wouldn't know how to enforce that teams can't exist at both the Division and the Conference level at the same time from the Database level.

What are your thoughts on this approach, and do you see any viable alternatives to this?

I imagine that most sports leagues don't change their hierarchy of conference, division, etc... very often so it's probably safe to have each level in its own table.

If your goal is a system that can handle different leagues that have different hierarchy depths, then the recursive table approach is probably better.

I don't know how easy it would be to do this with an ORM but I have a general mistrust of ORMs when it comes to anything other than simple CRUD operations... To prevent a team from existing at multiple tiers of the hierarchy, I'm not sure why your proposed Team.LeagueGroupId wouldn't work. You know what level the LeagueGroup referred to by LeagueGroupID is so you know that the team exists at the level of that LeagueGroup.


Upgrading 10g database to 11g database Is doing a full data export (expdp) from an Oracle 10g (10.2.0.1.0) database and then doing an import of that dump file (impdp) into an 11.2.0.2 database a valid and acceptable upgrade path from 10g to 11g?

Sure-- there is even a chapter in the Upgrade Guide on using DataPump to move the data from the old database to the new database.


Is it possible to change collation for an existing database with PostgreSQL? Such as change from C to utf8?
I tried this but seems not allowed.
postgres=# ALTER DATABASE mydb SET "Collate" To 'en_US.UTF-8';
ERROR:  unrecognized configuration parameter "Collate"

No, that is not possible.
The reason is that there may be data structures in the database that depend on the database's collation: indexes over string data types. If you'd be able to change the collation on the fly, all these indexes would implicitly become corrupted.
You might say that is OK, let's just rebuild all these indexes. Well, if you are ready to do that, there is a way: pg_dump the database, create a new database with the desired collation and restore the dump into the new database. Sure, that will take longer than just rebuilding the string indexes, but the expense is in the same order of magnitude, and this is a safe way.
You may be tempted to meddle with the pg_database system catalog manually, but I can only discourage that: as the saying is, if you do that and things break, you get to keep both pieces.


Default values in SQLite3 When I created the table structure of my SQLite database, I provided a DEFAULT value which is used when no value is provided by the user. Now, because the application code changed, I need to modify the default but can't seem to find how to do it.

Is it possible?

SQLite only implements a very limited subset of ALTER TABLE statements. You'll have to drop and recreate the table, preferably in one transaction:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE tbl_tmp(col1, col2);
INSERT INTO tbl_tmp SELECT col1, col2 FROM tbl;
DROP TABLE tbl;
CREATE TABLE tbl(col1 integer, col2 text DEFAULT 'foo'); -- with new default
INSERT INTO tbl SELECT col1, col2 FROM tbl_tmp;
DROP TABLE tbl_tmp;
COMMIT;


Is it possible to insert un-formatted data for money data type in PostgreSQL By default, PostgreSQL's money data type inserts values with currency code and grouping. I have observed that it always inserts values with $.

How can I insert locale specific values?
How can I insert values with my own digit grouping and decimal grouping formats?
How can I read unformatted data?
Why is PostgreSQL's money data type different than MS SQL's money data type?

How can I insert locale specific values?

Set LC_MONETARY accordingly. (Only affects the money type and some monetary details in formatting output!) You can do this as permanent default for your whole DB cluster in postgresql.conf, just for the current session with SET or for the transaction only with SET LOCAL. (Or in several other ways.)

Demo on a Windows machine:

BEGIN;
CREATE TEMP TABLE mdemo(id serial, m money);

SET LOCAL lc_monetary = 'German_Austria.1252';
INSERT INTO mdemo(m) VALUES ('123.45');

SET LOCAL lc_monetary = 'English_US.1252';
INSERT INTO mdemo(m) VALUES ('123.45');
TABLE mdemo;

 id |     m
----+------------
  1 | $12,345.00
  2 |    $123.45

ROLLBACK;  -- to clean up

As you can see the string literal '123.45' was interpreted according to the current setting of LC_MONETARY. In Austria, the dot (.) serves as group separator and , is the decimal point. While in the USA it's the other way round.

It's important to find correct locale names. The manual:

  What locales are available on your system under what names depends on
  what was provided by the operating system vendor and what was
  installed. On most Unix systems, the command locale -a will provide a
  list of available locales. Windows uses more verbose locale names,
  such as German_Germany or Swedish_Sweden.1252, but the principles are the same.

  How can I insert values with my own digit grouping and decimal grouping formats?

Use to_number() to produce a numeric value from the string literal, which can in turn be cast to money.

Say,  you have this literal: '12,454.8-':
2 digits, , as group separator, 3 digits, . as decimal point, 1 digit, pending sign. This does not work:

SELECT money '12,454.8-';

Works:

SELECT to_number('12,454.8-', '99,999.9S')::money

Since we are reading in a general number the more general setting for LC_NUMERIC applies for the import while the setting for LC_MONETARY still dictates how the result is displayed! The cast from numeric to money is locale-agnostic (immutable). Don't get confused.

And the pattern in the 2nd parameter is tricky, too. Symbols like , and . are taken literally and interpreted according to C locale (no locale), while patterns (you could use '99G999D9S', too) are interpreted according to the current locale.

I suggested clarifications to the manual on pgsql-docs.

  How can I read unformatted data?

You can read it, but you can hardly cast this to any numeric type without a minimum of information about its format.

I still would rather not use the data type money. Narrow, quirky specification. Use numeric to cover all bases. Or just integer signifying cents, performs better. See:

PostgreSQL: Which Datatype should be used for Currency?


Aliased Column name in SQL Server Sometimes, when developing in a hurry, devs make poor choices on column names.

Changing them can be a challenge, having to stage code, and time migration perfectly.

I'm told that Oracle has a way to give a secondary name to a column. Does SQL Server have anything like this? The best I can think of is to add a calculated column to duplicate the column, but this will lead to follow on indexing and performance challenges.

Any good patterns or a magic bullet for SQL Server 2008 or 2012?

(Someone mentioned a view, which would definitely work, but wondering if there's a built-in option.)

I guess I'm looking for a Synonym for Columns.
http://msdn.microsoft.com/en-us/library/ms177544.aspx

As @JNK mentioned, you can use a view.  There are some steps to take, of course.  The view should map only to columns in a single table.

I assume that you do not want to deploy completely updated code that uses the new name.  So, you can create a view with a different (but similar) name and any new code could use the view, and the new name. Existing code would still be going directly to the table.

This would get you moving, but one day you will probably want everthing to use the new column name.  Eventually, you will have to deploy new code.  Which would still mean a number of name changes if you don't want to have the view layer over the table.


PgAdmin 4.14 connection error in cloudSQL : could not send data to server, socket is not connected, could not send ssl negotiation packet I am trying to connect cloud sql with pgadmin but it throws error when I try to execute query.

  "Could not send Data to server, socket is not connected, could not
  sent SSL negotiation pocket"

But When I connect with different Database tool like DBViz / Psql, I can execute the query - no such error in other tools.
I am kind of lost in the error, could you please help me to resolve this.

Subash's answer worked for me. Switching from localhost to 127.0.0.1 allowed the connection successfully.


SQL Server Linked server query problem (group by) I have been using an old SQL Server 2000 installation for a long time that is linked to several Oracle servers, and it all works very well.

I am trying to migrate to SQL Server 2012 via SQL Server 2008.

I am encountering some problems with the linked Oracle servers on SQL Server 2008 and SQL Server 2012.

I downloaded the last Oracle 10 Instant Client since we have some older Oracle databases I need access to, and have configured tns_admin, and checked with SQL Plus; all good so far.

I created an ODBC system DSN to point to one of the Oracle databases.  I then created my linked server using that DSN.

The linked server returns the standard date query, select sysdate from dual, just fine, and will return the count of specific queries too.  However, when I try to return the actual rows from a table, I get a smaller number than I should.

Also, if I run an aggregate GROUP-BY query, I only get one row returned!

I would have tried downloading the full client from Oracle, but in their infinite wisdom, the v10 client is no longer available!

Any help or advice would be greatly appreciated.

The latest full client will work fine with 10g.
I have experienced similar problems using anything other than the full client, even though according to Oracle the others should work.
Just make sure that you completely uninstall all elements of the previous installs as having multiple clients/versions can create another set of problems.


Oracle 12c Client to 10g Server (OPENQUERY) Scenario: We currently have a SQL 2008 R2 server (with Oracle 10g Client) using OPENQUERY to return data from an Oracle 10g database.

Problem: We would like to upgrade to SQL Server 2014 SP1 (with Oracle 12c Client) and still pull data from the Oracle 10g server. We have run some test on returning the data using OPENQUERY and the results are less then satifactory. A simple select * from a table went from 9seconds to 54 seconds!

Testing: We created a test SQL Server 2008 R2 server but this time put Oracle 12c client on it. The select * query ran in 26seconds this time but still alot longer then the 9 from the original server.

Question: Could there be a setting that isn't set on the new servers that would affect the speed this much? If so does anyone have any suggestions?

Note: I believe using a 12c client to connect to a 10g server is supported correct?

Thanks in advance.

As Oracle support note "Client / Server Interoperability Support Matrix for Different Oracle Versions" ( Doc ID 207303.1 ),  Oracle Client 12.1 does not support connections with Oracle DB servers 10.1 and below.

Connections with Oracle DB 10.2 are still supported, but not recommended.

The recommended option its to use Oracle 10.2 client.


SQL query to aggregate data from three tables I have the following three tables:

The "users" table:

user_id
first_name
last_name
username
password

The "users_addresses" table:

address_id
user_id
street
country_id
email

The "countries" table

country_id
name

A user can have multiple addresses, but each user has at least one address.

What I would like to do is to write a single query to get all the users with their latest address (the address with the higher address_id for that user_id).

I tried this query, but apparently, it doesn't return the latest address if there's more than one address for a user:

SELECT u.first_name
     , u.last_name
     , u.username
     , u.password
     , a.user_id
     , a.street
     , a.email
     , c.name
  FROM users u
  LEFT
  JOIN user_addresses ua
    ON ua.user_id = u.user_id
  LEFT
  JOIN countries c
    ON ua.country_id = c.country_id
 GROUP
    BY a.user_id
 ORDER
    BY u.last_name ASC

Any help is appreciated.

Thank you!

You need to get the maximum address_id per user_id

SELECT user_id,MAX(address_id) address_id
FROM users_addresses GROUP BY user_id;

Make that query a subquery and join back to the other tables

SELECT u.first_name
 , u.last_name
 , u.username
 , u.password
 , a.user_id
 , a.street
 , a.email
 , c.name
FROM
(SELECT user_id,MAX(address_id) address_id FROM users_addresses GROUP BY user_id) ua
INNER JOIN users u           ON ua.user_id    = ua.user_id
INNER JOIN users_addresses a ON ua.address_id = a.address_id
INNER JOIN countries c       ON a.country_id  = c.country_id
ORDER BY u.last_name,u.firstname
;

Give it a Try !!!


Inline function Plan Properties in SQL Server 2008 Currently I use the below two queries to get some properties from the execution plan in stored procedures, the problem arises when I try to run it in a SQL Server 2008 version. Do you know if there is something similar to get these atributes for a SQL inline table-valued function in SQL Server 2008?

First query

SELECT
    ExecutionPlan                       = QueryPlans.query_plan ,
    CachedDateTime                      = ProcedureStats.cached_time ,
    LastExecutionDateTime               = ProcedureStats.last_execution_time ,
    ExecutionCount                      = ProcedureStats.execution_count ,
    AverageElapsedTime_Microseconds     = CAST ((CAST (ProcedureStats.total_elapsed_time AS DECIMAL(19,2)) / CAST (ProcedureStats.execution_count AS DECIMAL(19,2))) AS DECIMAL(19,2)) ,
    SetOptions                          = PlanAttributes.value
FROM
    sys.dm_exec_procedure_stats AS ProcedureStats
CROSS APPLY
    sys.dm_exec_query_plan (ProcedureStats.plan_handle) AS QueryPlans
CROSS APPLY
    sys.dm_exec_plan_attributes (ProcedureStats.plan_handle) AS PlanAttributes
WHERE
    database_id = DB_ID (N'db')
AND
    object_id = OBJECT_ID (N'procedure')
AND
    PlanAttributes.attribute = 'set_options';
GO

Second query

SELECT
    ExecutionPlan                       = QueryPlans.query_plan ,
    CachedDateTime                      = ProcedureStats.cached_time ,
    LastExecutionDateTime               = ProcedureStats.last_execution_time ,
    ExecutionCount                      = ProcedureStats.execution_count ,
    AverageElapsedTime_Microseconds     = CAST ((CAST (ProcedureStats.total_elapsed_time AS DECIMAL(19,2)) / CAST (ProcedureStats.execution_count AS DECIMAL(19,2))) AS DECIMAL(19,2)) ,
    ANSI_NULLS_Value                    = IIF (CAST (PlanAttributes.value AS INT) & 32 = 32 , N'True' , N'False') ,
    ANSI_PADDING_Value                  = IIF (CAST (PlanAttributes.value AS INT) & 1 = 1 , N'True' , N'False') ,
    ANSI_WARNINGS_Value                 = IIF (CAST (PlanAttributes.value AS INT) & 16 = 16 , N'True' , N'False') ,
    ARITHABORT_Value                    = IIF (CAST (PlanAttributes.value AS INT) & 4096 = 4096 , N'True' , N'False') ,
    CONCAT_NULL_YIELDS_NULL_Value       = IIF (CAST (PlanAttributes.value AS INT) & 8 = 8 , N'True' , N'False') ,
    NUMERIC_ROUNDABORT_Value            = IIF (CAST (PlanAttributes.value AS INT) & 8192 = 8192 , N'True' , N'False') ,
    QUOTED_IDENTIFIER_Value             = IIF (CAST (PlanAttributes.value AS INT) & 64 = 64 , N'True' , N'False')
FROM
    sys.dm_exec_procedure_stats AS ProcedureStats
CROSS APPLY
    sys.dm_exec_query_plan (ProcedureStats.plan_handle) AS QueryPlans
CROSS APPLY
    sys.dm_exec_plan_attributes (ProcedureStats.plan_handle) AS PlanAttributes
WHERE
    database_id = DB_ID (N'db')
AND
    object_id = OBJECT_ID (N'procedure')
AND
    PlanAttributes.attribute = 'set_options';
GO

I converted the IIF to CASE statements and cleaned it up a bit.

Combining them into one query, I came up with this:

DECLARE @dbname NVARCHAR(128) = N'dbname'
DECLARE @procname NVARCHAR(128) = N'procname'

SELECT
    ExecutionPlan                       = QueryPlans.query_plan ,
    CachedDateTime                      = ProcedureStats.cached_time ,
    LastExecutionDateTime               = ProcedureStats.last_execution_time ,
    ExecutionCount                      = ProcedureStats.execution_count ,
    AverageElapsedTime_Microseconds     = CAST ((CAST (ProcedureStats.total_elapsed_time AS DECIMAL(19,2)) / CAST (ProcedureStats.execution_count AS DECIMAL(19,2))) AS DECIMAL(19,2)) ,
    SetOptions                          = PlanAttributes.value,
    ANSI_NULLS_Value                    = CASE WHEN CAST (PlanAttributes.value AS INT) & 32 = 32 THEN N'True' ELSE N'False' END ,
    ANSI_PADDING_Value                  = CASE WHEN CAST (PlanAttributes.value AS INT) & 1 = 1 THEN N'True' ELSE N'False' END ,
    ANSI_WARNINGS_Value                 = CASE WHEN CAST (PlanAttributes.value AS INT) & 16 = 16 THEN N'True' ELSE N'False' END ,
    ARITHABORT_Value                    = CASE WHEN CAST (PlanAttributes.value AS INT) & 4096 = 4096 THEN N'True' ELSE N'False' END ,
    CONCAT_NULL_YIELDS_NULL_Value       = CASE WHEN CAST (PlanAttributes.value AS INT) & 8 = 8 THEN N'True' ELSE N'False' END ,
    NUMERIC_ROUNDABORT_Value            = CASE WHEN CAST (PlanAttributes.value AS INT) & 8192 = 8192 THEN N'True' ELSE N'False' END ,
    QUOTED_IDENTIFIER_Value             = CASE WHEN CAST (PlanAttributes.value AS INT) & 64 = 64 THEN N'True' ELSE N'False' END
FROM
    sys.dm_exec_procedure_stats AS ProcedureStats
CROSS APPLY
    sys.dm_exec_query_plan (ProcedureStats.plan_handle) AS QueryPlans
CROSS APPLY
    sys.dm_exec_plan_attributes (ProcedureStats.plan_handle) AS PlanAttributes
WHERE
    database_id = DB_ID (@dbname)
AND
    object_id = OBJECT_ID (@procname)
AND
    PlanAttributes.attribute = 'set_options';

IIF statements started in SQL Server 2012 (https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-server-2017)

After converting those, everything else worked on SQL2008R2.


When and why should I reset session in a connection pool? I am using Python to implement a high speed query push service into a MySQL database.
Considering a broad usage of various MySQL elements like temporary tables in stored procedures, when and why  should I use pool_reset_session feature on a 256 connections-based pool?

Always set pool_reset_session to True, unless you have a specific and strong reason not to. I cannot think of a reason not to reset the session when returning a connection to the pool.
The risk of not resetting a session is that data associated with your session will be leaked into other sessions. Say for example you are doing a banking transaction on a website that serves multiple concurrent users. Part of the code saves some data about your transaction to a temporary table. Then the code returns that MySQL connection to the connection pool, where it is handed off to some other user's banking interaction. Their session can now see some details about your banking! The same applies to other session state, like user variables, or last_insert_id.
If you need to keep using session state, like reading temp tables several times, then your code should not return that connection to the pool until it is done with that temp table.


Rounding of SMALLMONEY Data Type in SQL Server 2005 I'm on SQL Server 2005, and I am trying to figure out a rounding issue with smallmoney data types.

For example, the value 1.2849, when rounded to 2 decimal places, produces 1.28, but shouldn't it produce 1.29?  e.g. 9 rounds up for 1.285, 5 rounds up for 1.29.

Here is the example:

SELECT
    'CAST(1.2849 AS SMALLMONEY)' OPERATION, CAST(1.284948 AS SMALLMONEY) RESULT
UNION
SELECT
    'ROUND(CAST(1.2849 AS SMALLMONEY), 2)', ROUND(CAST(1.284948 AS SMALLMONEY), 2)
UNION
SELECT
    'ROUND(ROUND(CAST(1.2849 AS SMALLMONEY), 3), 2)', ROUND(ROUND(CAST(1.2849 AS SMALLMONEY), 3), 2)

I am fairly certain that it just looks at the third decimal digit... and that's 4 so it rounds down to 1.28.

Clarify - it looks to the digit one greater than what you are rounding to... so it looks to the third because you are rounding to the second.


How To Remove Spaces Between Characters In PostgreSQL? I want to match two tables based on a specific column which is a card number, there are spaces between characters in some of card numbers in the second table like below:

'NES 123_4_5'
'MTS 678_9_0'

What i want is to remove the space between the characters and numbers and change it to following form:

'NES123_4_5'
'MTS678_9_0'

What would be the SQL query of function to do this?

simplest way - use REPLACE function

select REPLACE('NES 123_4_5', ' ', '')
Result - NES123_4_5

full list of string functions - https://www.postgresql.org/docs/9.6/static/functions-string.html


password confusion in postgres sql I have logged into postgresql server using

sudo -u postgres psql

Then I also created a database from within /usr/local/bin directory using the command.

createdb -h localhost -p 5432 -U postgres testdb

When prompted for password, I entered the PostgreSQL admin user password (that I had changed from 'postgres' to 'upendra').

When I do

\l

in the postgres prompt, I get the following output

When I connect to testdb from within the postgres prompt, I am not required to enter the password. the connection is instantaneous.

However, when I try to connect to this database from the terminal using

psql -h localhost -p 5432 postgres testdb

I am required to enter password. Why is it so?Moreover, no matter what password I type it always says that authentication failed. what's the catch?

Also, why does the prompt say password authentication failed for user "testdb". From what I have understood, testdb is the name of the database and not the name of the user. The user  name is postgres.

First, to answer the question you asked about the behavior of this command:

 psql -h localhost -p 5432 postgres testdb

  Also, why does the prompt say password authentication failed for user "testdb". From what I have understood, testdb is the name of the database and not the name of the user. The user name is postgres.

Because you have given two trailing options to this psql command: postgres and testdb. Since neither the -d nor -U option were given, psql assumes the database name should be the first argument and the username should be the second argument, per the documented Synopsis of the psql command.

Second, to answer your other question:

  When I connect to testdb from within the postgres prompt, I am not required to enter the password. ... However, when I try to connect to this database from the terminal using [... psql, and getting a password prompt ... ]

psql has some logic by which it determines whether it is safe to keep and reuse the password you entered initially, see the logic about keep_password in command.c. Presumably when you are able to use the \c meta-command to reconnect from within an existing psql session, psql is preserving your initial password and reusing it -- either that, or your pg_hba.conf rules allow a connection without a password with the given user/database, e.g. a trust or ident rule.


Select count of timestamp formatted field in short date format to give number of rows I have a field titled DTUpdated and it is a timestamp field.
I am trying to select a count of the dates in the rows that are xx old. i.e. 0-3 months, 3-6 months etc.
I am not at the writing date selection criteria for the selection yet as i am stuck getting the first part of the query to simply count the occurrences of each day in a results pane.
The query i have so far is as follows but it is not grouping the data by the days as returned.
SELECT CONVERT(VARCHAR(10),[SCAData].[dbo].[Camera].[DTUpdated],105) AS Date_Updated,
COUNT(DTUpdated) AS No_of_Rows
FROM [SCAData].[dbo].[Camera]
GROUP BY DTUpdated
ORDER BY DTUpdated;

Result:
Date_Updated    No_of_Rows
03-10-2006      1
03-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
06-10-2006      1
06-10-2006      1
06-10-2006      1
06-10-2006      1
06-10-2006      1

Can someone please help or also suggest how to then get these back based on date selections of last 3 months, 3-6 months?
Many thanks in advance.

Since the column type is a datetime and this is SQL Server 2016, converting it to a date is easy :
SELECT CONVERT(Date,[SCAData].[dbo].[Camera].[DTUpdated]) AS Date_Updated,
COUNT(DTUpdated) AS No_of_Rows
FROM [SCAData].[dbo].[Camera]
GROUP BY CONVERT(Date,[SCAData].[dbo].[Camera].[DTUpdated])
ORDER BY 1;

If you really want to do it via an VARCHAR field, just do it like that :
SELECT CONVERT(VARCHAR(10),[SCAData].[dbo].[Camera].[DTUpdated],105) AS Date_Updated,
COUNT(DTUpdated) AS No_of_Rows
FROM [SCAData].[dbo].[Camera]
GROUP BY CONVERT(VARCHAR(10),[SCAData].[dbo].[Camera].[DTUpdated],105)
ORDER BY 1;


returning a null value instead of no value Why are the last 2 selects (cases 5 and 6) returning a null value? This may seem like a trivial question but it actually caused a bug in one of my joins where I expected rows to be filtered out by cross applying to some value and instead it returned the row with a null value.

DROP TABLE IF EXISTS #temp;
CREATE TABLE #temp (t VARCHAR(1));

Cases 1-4 return expected "no value" result.

-- CASE 1
SELECT *
FROM #temp;

-- CASE 2
SELECT (SELECT t)
FROM #temp;

-- CASE 3
SELECT (T)
FROM #temp;

-- CASE 4
SELECT CAST(value AS VARCHAR(MAX))
FROM STRING_SPLIT('t', ',') s
WHERE EXISTS (
        SELECT 1
        FROM #temp
        WHERE t = 't'
        );

Problem cases - returning "NULL".

-- CASE 5
SELECT (
        SELECT value
        FROM STRING_SPLIT('t', ',') s
        WHERE EXISTS (
                SELECT 1
                FROM #temp
                WHERE t = 't'
                )
        );

-- CASE 6
SELECT (
        SELECT T
        FROM #TEMP
        );

Your last 2 SELECTs are without a FROM clause. These will always return a single row of scalar values.
The fact that the subquery did not return any rows, caused the expression to be NULL - unknown.
Think about the following statement - how would you expect the engine to handle this:

 SELECT 1 AS FirstCol, (<some SELECT that returns no rows>) AS SecondCol

Note: This is a non standard T-SQL extension. In Oracle for example, you need to add the FROM DUAL or you will get ORA-00923: FROM keyword not found where expected. However, this same statement with FROM DUAL added run in Oracle will also result in a single row of scalar values.