Making sure balance field is always consistently updated with triggers This is my database schema, related to the problem.

This is one of the triggers:

CREATE OR REPLACE FUNCTION "public"."update_balance_bet"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
    DECLARE
    currentBalance INTEGER;
    BEGIN

    currentBalance = (SELECT balance FROM "public"."Users" WHERE id=NEW."UserId");
    UPDATE "public"."Users" SET balance=(NEW.amount+currentBalance);
    RETURN NEW;
END
$BODY$

CREATE TRIGGER bet_trigger
    AFTER INSERT ON "public"."Bets"
    FOR EACH ROW
    EXECUTE FUNCTION update_balance_bet();

Basically from my app I insert records into table Bets, Bonuses, Withdrawals and Deposits, then the rest of the work is done by triggers. My questions is whether the mechanism I used will guarantee consistency in all cases, or should I use raw locking? Am I right in assumption that inserting into Bets table is one transaction along with trigger function updating balance, so in case something goes wrong with insertion into Bets, balance wont be updated OR if something wrong goes with balance updating, new record wont be inserted into Bets table?

First, this UPDATE is buggy:

 UPDATE "public"."Users" SET balance=(NEW.amount+currentBalance);

because it updates the entire "Users" table, which is obviously not what's wanted.

Let's assume that a WHERE clause is added and the code becomes:

DECLARE
currentBalance INTEGER;
BEGIN

currentBalance = (SELECT balance FROM "public"."Users" WHERE id=NEW."UserId");

UPDATE "public"."Users" SET balance=(NEW.amount+currentBalance);
  WHERE id=NEW."UserId"

RETURN NEW;

This code is subject to a race condition under the default isolation level (Read Committed). Say this transaction is T1 and another one is T2.
If T2 was updating the balance for this user, and it commits
between the SELECT and the UPDATE, the UPDATE of T1 will overwrite the row, leading the change of T2 being lost and the balance being wrong.

To implement this logic in a concurrent-safe way, the recommendation of the SQL standard is to use transactions at the serializable isolation level. See Transaction Isolation in PostgreSQL documentation. Alternatively, you may implement your own locking strategy at the less isolated levels to avoid race conditions, but it's harder to get it right.

  Q: Do I right in assumption that inserting into Bets table is one
  transaction along with trigger function updating balance, so in case
  something goes wrong with insertion into Bets, balance wont be updated
  OR if something wrong goes with balance updating, new record wont be
  inserted into Bets table

Yes, but that's a virtue of transactions, not of triggers.
Normally, you get atomicity by putting the list of SQL statements between a BEGIN...COMMIT pair, not by moving them into a trigger. The point of triggers is to implement side-effects, not the main logic. Quoting Tom Kyte's The Trouble with Triggers excellent post:

  Triggers should be viewed with a skeptic’s eye. If the trigger is in
  place to enforce entity integrity, be very suspicious of it. Think
  about multiuser conditions. Think about what happens when two or three
  people operate on similar data at, or at about, the same time.

  [...]

  Triggers should be the exception, not the rule. They should be used
  only when you cannot do something any other way. Given the concurrency
  issues, the problems with doing nontransactional operations in them,
  and the maintenance problems, triggers are something to use sparingly,
  if at all.


Verify conditions before creating a constraint I've taken over an application database that has been grown from an Access db. As such, the database is in need of some significant refactoring.

The first step that I'm planning is adding a number of Foreign (and at least one Primary) Key constraints. One-by-one, I've been tracking down constraints that have failed when I try to apply them to my development copy, but  I plan on adding a significant number of check constraints in the future.

I'd like to keep a running report of problem areas I need to focus on before I can apply any structural changes. Is there a way to verify the constraint criteria on the live system without attempting any schema or other changes?

You can find duplicate PKs:

select pk_col, count(*)
from tab
group by pk_col
having count(*) > 1 -- multiple rows with the same PK exist

Or the actual rows violating the PK:

select * from tab
where pk_col in
 (
   select pk_col
   from tab
   group by pk_col
   having count(*) > 1 -- multiple rows with the same PK exist
 )

This returns bad FKs:

select * from child_tab as c
where not exists  -- invalid values in FK column
 (
   select * from parent_tab as p
   where c.fk_col = p.pk_col
 )

Now you can fix the bad data before you add the constraint.


Truncate parent table but not children I have a table in PostgreSQL 9.4 that I partitioned so that all inserts to the parent actually go into one of the child partitions. The problem is that I already have hundreds of millions of rows of data in the parent table from before I created partitions.

The parent and children exist in different schemas:

parent: public.history_uint
children: partitions.history_uint_p2015_mm_dd

Here is how I created the partitions.

How to TRUNCATE the parent but not the children?

Use the ONLY key word:

TRUNCATE ONLY public.history_uint;

The manual:

  If ONLY is specified before the table name, only that table is
  truncated. If ONLY is not specified, the table and all its descendant
  tables (if any) are truncated. Optionally, * can be specified after
  the table name to explicitly indicate that descendant tables are included.

The schemas where parent and children live are only relevant as far as user privileges on the schema are concerned. Otherwise it's all the same to Postgres.


Tempdb tables, table in database or even cte for usp for reporting I am about to start a project for SSRs report base on a USP. The procedure will have to use two cursors to compare orders v stock position (similar to a mrp run ): I am thinking of storing the data into a table that can be used In the ssrs report.

So the question is for a result set of no more than a few hundred lines what sort of table should I use. One in the production dB. A temp table in tempdb or can you use cte ?

The table will be dropped each run of the USP/ report

What comments are there

Thanks

If your data needs to be persistent over a failure & restart then a "real" table in your database would be best.  You need a mechanism to isolate one user's execution of this from another's and to tidy up nicely.

If data is to be disposed of if the SP crashes and will be re-created on a restart then a table-valued variable would be adequate.  These are not indexed, however.  If the data will be re-read according to different predicates this may be a problem.

Temporary tables e.g. create table #abc... will keep one user's data isolated from another users'.  The tables will be tidied away at the end of the SP.  Indexes and statistics are held for #Tables so these perform better for larger row counts or multiple access paths.


MySql - Get all children in JOIN if one child matches a condition I have a one-to-many relationship between two tables Parent and Child. I want to get all children if one child matches a condition. Something like below:

SELECT * FROM Parent p
INNER JOIN Child c ON c.parent_id = p.id
WHERE c.first_name LIKE '%abc%' OR c.last_name LIKE '%abc%'

This query returns only the child with name containing abc. I want to get all siblings of this child as well. How can i do that?

SELECT p.*, c2.*
FROM Parent p
INNER JOIN Child c
    ON c.parent_id = p.id
INNER JOIN Child c2
    ON c.parent_id = c2.parent_id
WHERE
    c.first_name LIKE '%abc%' OR c.last_name LIKE '%abc%'


mysqli error: [1093: ...] I have an SQL select, that gives two rows back.
The SQL update gives the error message 1093 back;

  mysqli error: [1093: Table 'HD_TICKET' is specified twice, both as a
  target for 'UPDATE' and as a separate source for data]

update HD_TICKET, HD_STATUS as T5
    set HD_TICKET.HD_STATUS_ID = T5.ID,
HD_TICKET.TIME_OPENED  = IF(T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED),
HD_TICKET.TIME_CLOSED  = IF(T5.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED),
HD_TICKET.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED),
HD_TICKET.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING),
HD_TICKET.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT),
HD_TICKET.TITLE = (SELECT NAME FROM ORG1.ASSET WHERE ORG1.ASSET.ID = (SELECT ASSET_ID FROM HD_TICKET WHERE HD_TICKET.ID = HD_TICKET.PARENT_ID))
  where T5.NAME = 'New' and
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and
    (HD_TICKET.ID in (<TICKET_IDS>))

Can anybody help me, please? Thanks!

As far as I understand from the comments, you wanted a correlated subquery, to find the "parent" ticket and from that the realted asset (and its name):

HD_TICKET.TITLE = (SELECT NAME
                   FROM ORG1.ASSET
                   WHERE ORG1.ASSET.ID = (SELECT parent.ASSET_ID
                                          FROM HD_TICKET AS parent
                                          WHERE parent.ID = HD_TICKET.PARENT_ID))

Now, you'd still get a similar error with the above code change, for the same reasons. MySQL does not allow the table that is updated to appear in a subquery in the WHERE clause.

Solution is to move the second reference to the table to the UPDATE clause and trasnform the subqueries into outer joins:

  left join HD_TICKET as PARENT
    on PARENT.ID = T.PARENT_ID
  left join ORG1.ASSET as A
    on A.ID = PARENT.ASSET_ID

Since we did that, it feels right to convert the existing join to T5 to the same JOIN syntax as well:

update HD_TICKET as T
  join HD_STATUS as T5
    on T.HD_QUEUE_ID = T5.HD_QUEUE_ID
  left join HD_TICKET as PARENT
    on PARENT.ID = T.PARENT_ID
  left join ORG1.ASSET as A
    on A.ID = PARENT.ASSET_ID
set
  T.HD_STATUS_ID = T5.ID,
  T.TIME_OPENED  = IF(T5.STATE = 'opened', NOW(), T.TIME_OPENED),
  T.TIME_CLOSED  = IF(T5.STATE = 'closed', NOW(), T.TIME_CLOSED),
  T.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), T.TIME_STALLED),
  T.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_RATING),
  T.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_COMMENT),
  T.TITLE = A.NAME
where T5.NAME = 'New'
  and T.ID in (<TICKET_IDS>) ;


Using existing SQL Server credentials for log shipping reporting purposes I have set up log shipping, but I don't see how I can reset orphaned SQL Server credentials on the destination server as databases are in standby/read-only mode. I am getting push-back on implementing this functionality. In light of this, I would like to make it as easy as possible for developers to use the log shipping databases for reporting as opposed to the live transactional databases.

I would prefer to use Windows credentials which wouldn't have to be reset, but wouldn't that somehow require all users (of reports) to be validated ?

The trick is to avoid the orphaned logins in the first place, not to synchronize them afterwards.

As you probably know, 'orphaned' users are due to the SID associated with the user in the database not matching the SID associated with the login on the server. When a login is created on a server, the value for the SID is either taken from Active Directory (if you are using "integrated" or "windows" logins) or generated by SQL Server (in the case of SQL logins). With SQL Server logins, the value of the SID is generated by SQL Server. The value will vary each time you create a login [hint] unless you take special care.

IOW, if you create a login named "foo" on two servers, each login will have a different SID value. If you create a login named "foo", drop it and create it again, each occurrence of "foo" will have a different SID value.

Strategy #1: use integrated security

With Integrated security, the SID used by SQL Server for the login on the server and the user in the database is taken from the Active Directory login. Because of that, the values can't differ. Changing from SQL logins to integrated logins is usually a hassle, developers generally don't like to spend time on infrastructure work and it's probably a non-starter.

Strategy #2: use the "SID" parameter when creating logins on the destination server.

If you create a login using TSQL code, you can specify a specific value for a SID. You can construct a set of CREATE LOGIN statements (including SIDs and exported passwords) so that the SIDs match on both servers. That way, when you restore the database (via shipped log backups or any other way), the SIDs will match and people can get to the data.

Microsoft provides code that will help you create these CREATE LOGIN statements based on what exists in the source server. You can then copypaste those statements over to the destination server and run them. The name of the procedure is sp_help_revlogin. The code varies slightly between SQL Server versions, but the version for SQL2008R2 is available from Microsoft here

TLDR: I'd recommend using sp_help_revlogin on the source server to create new CREATE LOGIN statements for the logins that have to sync up. (You don't need to do every login, just the ones that have problems.) Then, use those statements to drop and recreate the logins on the destination server. Since you will be dropping logins, you should be aware of any database permissions that those logins already have (maybe there are other databases on the destination server that the developers already have permissions in?) and you should be able to recreate those permissions after you get the login recreated.


How to figure out what a DTSRun command is doing I have taken over a SQL 2000 database. I am trying to figure out what a job is doing. Its command is:

DTSRun /~....a big hex string....

I have tried to run:

DTSRun /~....a big hex string.... /!Y

to see what it is doing, but it just repeats the command. Is there a way I can see what this string represents?

You ave to use /!X /!C after the DTS command.

/!X ==> Do not run the package

/!C ==> copy the results to clipboard

To avoid this, you can create a  SQL Agent Job and choose CmdExec as a step type with below commandline:

 DTSRun /N"your package name" /S<servername> /E   --- for trusted connection

OR

DTSRun /N"Your package Name" /S<ServerName> /U<UserName> /P<Password>   --- for SQL authentication

As a sidenote, its time for migration to something supported by Microsoft sql 2005, sql 2008 R2 or 2012 along with migrating DTS packages to SSIS.

Refer to :

How to identify which DTS Package is being called by scheduled job
dtsrun Utility


Mixed based replication between two databases on the same server I have two apps with a current configuration as follows:

App1

DB on server A/MySQL Instance A

App2

DB on server B/MySQL Instance B

Students can apply for internship using App1 and those applications are stored on App1 DB. Because we want to be able to review those applications on App2 and update their statuses. Master-Master replication is used and we are able to get
the apps. Now requirements have changed and we want both Apps and DBs to be on server B with a presume config as follows

Server B

App1
App2
MySQL Instance B contain DB1 and DB2

How can I configure Master-Master replication between two databases on the same instance or is it better to have just one DB now and make both apps use the one DB?

Have two instances of MySQL on that one server.  Each has its own data directory, its own port (3307 is typically used for the second one), each has its own my.cnf and logs.  But, since they share the RAM, you need to cut back many tunables relating to RAM.

Why?  What do you gain?  Certainly not HA.  You may be able to better use lots of Cores.  But you are doubling the "writes" on that machine (since the 'Slave' is repeating the writes).


all tables used by a stored procedure and all sps that use a particular table - recursive cte I am working on a script that gives me a list of tables that use a specific stored procedure.

It also works on all tables used by a stored procedure.

on the example below, all stored procedures that use the table 'ProductItemDetailsDenorm'

USE US15WINMPRODUCT
GO

DECLARE
      @sp_name nvarchar(128) -- NULL shows all stored procedures that use the table @table
     ,@table   nvarchar(128)

SELECT
      @sp_name = NULL
     ,@table   = 'ProductItemDetailsDenorm'

            ;WITH stored_procedures AS (

                SELECT
                schema_name(o.schema_id) as proc_schema,
                o.name AS proc_name,
                schema_name(oo.schema_id) as table_schema,
                oo.name AS table_name,
                ROW_NUMBER() OVER(PARTITION BY o.name,oo.name ORDER BY o.name,oo.name) AS row
                FROM sysdepends d
                INNER JOIN sys.objects o ON o.object_id=d.id
                INNER JOIN sys.objects oo ON oo.object_id=d.depid
                WHERE o.type = 'P'

            )

            SELECT proc_schema,
                   proc_name,
                   table_schema,
                   table_name
            FROM stored_procedures
            WHERE [row] = 1
            AND ((@sp_name IS NULL) OR (proc_name = @sp_name)) AND ((@table IS NULL) OR (table_name = @table))
            ORDER BY proc_name,table_name

            --sp_help 'sys.objects'

This gives me the list below (partial):

The question, the bit I am struggling with is the following situation:

what if inside the stored procedure SP1 there is a stored procedure SP2 that uses the table @MyTable?

I want that to be listed as well.

I thought the solution could be:

  there should be a recursive cte to cater for procedures inside
  procedures that might use the table @table

but so far I have failed to implement it.

recursive CTEs should have 2 parts: anchor and UNION ALL.
how would I apply these two in the current CTE?

any hints?

the following script comes from this question
and it has worked fine for my tests
using sql server 2005 (stored procedures inside other stored procedures)

example of output:

this is the code:

--=========================================================================

--https://stackoverflow.com/questions/15072445/query-to-recursively-identify-object-dependencies

--== utlGetAllDependentObjectsRecursive - Uses recursive common table
--==     expression to recursively get all the dependent objects as well
--==     as the child objects and child's child objects of a
--==     Stored Procedure or View or Function.  can be easily modified to
--==     include all other types of Objects

--Wrote this Stored Procedure below which RECURSIVELY lists all
--the dependent child objects and child's dependent objects and child's child...etc.

--The input parameter can be Stored Proc, User Function, View.

--Can easily be altered to get a Unique List of Column 5, regardless of what Level the Object
--was called and how deep and by which object.

--COLUMNS

--UsedByObjectId - The parent object that uses the dependent object
--UsedByObjectName - The name of the parent object
--UsedByObjectType - Type of the parent object (P,V,FN)
--DependentObjectId - The child object the parent uses
--DependentObjectName - Name of the child object
--DependentObjectType - Type of the dependent child object (P,V,FN, U)
--Level - How deep, the nested recursive level which the object is used

--=========================================================================

USE US15WINMPRODUCT
GO

DECLARE
      @sp_name nvarchar(128) -- NULL shows all stored procedures that use the table @table
     ,@table   nvarchar(128)

SELECT
      @sp_name = NULL
     ,@table   = 'ProductItemDetailsDenorm'

   -- Supports Stored Proc, View, User Function, User Table
declare @PARAM_OBJECT_NAME VARCHAR(500)

select @PARAM_OBJECT_NAME = 'sp1'
--select @PARAM_OBJECT_NAME = 'ProductItemDetailsDenorm'

   ; WITH CTE_DependentObjects AS
    (
        SELECT DISTINCT
        b.object_id AS UsedByObjectId,
        b.name AS UsedByObjectName, b.type AS UsedByObjectType,
        c.object_id AS DependentObjectId,
        c.name AS DependentObjectName , c.type AS DependenObjectType
        FROM  sys.sysdepends a
        INNER JOIN sys.objects b ON a.id = b.object_id
        INNER JOIN sys.objects c ON a.depid = c.object_id
        WHERE b.type IN ('P','V', 'FN') AND c.type IN ('U', 'P', 'V', 'FN')
    ),
    CTE_DependentObjects2 AS
    (
       SELECT
          UsedByObjectId, UsedByObjectName, UsedByObjectType,
          DependentObjectId, DependentObjectName, DependenObjectType,
          1 AS Level
       FROM CTE_DependentObjects a
      WHERE a.UsedByObjectName = @PARAM_OBJECT_NAME

       UNION ALL

       SELECT
          a.UsedByObjectId, a.UsedByObjectName, a.UsedByObjectType,
          a.DependentObjectId, a.DependentObjectName, a.DependenObjectType,
          (b.Level + 1) AS Level
       FROM CTE_DependentObjects a
       INNER JOIN  CTE_DependentObjects2 b
          ON a.UsedByObjectName = b.DependentObjectName
    )

    SELECT DISTINCT * FROM CTE_DependentObjects2
    ORDER BY Level, DependentObjectName


Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table? I stumbled upon this question on a Twitter conversation with Lukas Eder.

Although the correct behavior would be to apply the ORDER BY clause on the outermost query, because, here, we are not using DISTINCT, GROUP BY, JOIN or any other WHERE clause in the outermost query, why wouldn't a RDBMS just pass the incoming data as it was sorted by the inner query?

SELECT *
FROM (
    SELECT * FROM table ORDER BY time DESC
) AS t

When running this example on PostgreSQL, at least, you get the same Execution Plan for both the inner query and this derived table example, as well as the same result set.

So, I would assume that the Planner will simply discard the outermost query because it's redundant or simply pass through the results from the inner table.

Does anyone think this might not be the case?

Most databases are quite clear about the fact that an ORDER BY in a subquery is either:

Not allowed: E.g. SQL Server, Sybase SQL Anywhere (unless complemented with TOP or OFFSET .. FETCH)
Meaningless: E.g. PostgreSQL, DB2 (again, unless complemented with OFFSET .. FETCH or LIMIT)

Here's an example from the DB2 LUW manual (emphasis mine)

  An ORDER BY clause in a subselect does not affect the order of the rows returned by a query. An ORDER BY clause only affects the order of the rows returned if it is specified in the outermost fullselect.

The wording is quite explicit, just like PostgreSQL's:

  If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

From this specification, it can be followed that any ordering resulting from the ORDER BY clause in a derived table is merely accidental and may coincidentally match your expected ordering (which it does in most databases in your trivial example), but it would be unwise to rely on this.

Side note on DB2:

In particular, DB2 has a lesser known feature called ORDER BY ORDER OF <table-designator>, which can be used as follows:

SELECT C1 FROM
   (SELECT C1 FROM T1
      UNION
    SELECT C1 FROM T2
    ORDER BY C1 ) AS UTABLE
ORDER BY ORDER OF UTABLE

In this particular case, the ordering of the derived table can be explicitly re-used in the outer most SELECT

Side note on Oracle:

For years it has been a practice in Oracle to implement OFFSET pagination using ROWNUM, which can be reasonably calculated only after ordering a derived table:

SELECT *
FROM (
  SELECT rownum AS rn, t.* -- ROWNUM here depends on the derived table's ordering
  FROM (
    SELECT * FROM table ORDER BY time DESC
  ) t
) t
WHERE rn BETWEEN 10 AND 20

It can be reasonably expected that at least in the presence of ROWNUM in a query, future Oracle versions will not break this behaviour in order not to break pretty much all the legacy Oracle SQL out there, which has not yet migrated to the much more desireable and readable SQL standard OFFSET .. FETCH syntax:

SELECT * FROM table ORDER BY time DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY


Using dynamic database name for DML in trigger I am using SQL Server 2008 in my application. Here I need to do database operations between multiple databases. The complexity is database names are user defined except one masster database.
I can have information about secondary database name in my master database, from which I have to fire DML statements. I am using following approach to achive the same:

USE [Database1]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[CTR_Production_InsertRecordInDatabase2] ON [dbo].[Table1]
AFTER  INSERT, UPDATE, DELETE
AS

SET NOCOUNT ON;

    DECLARE @SecondDatabaseName varchar(125)

    --Fetch second db name
    SET @SecondDatabaseName = dbo.sfn_GetSecondDbName()
    :SETVAR SecondDatabaseName @SecondDatabaseName

------------------------------INSERT TO Second DB------------------------------

    INSERT INTO $(SecondDatabaseName).[dbo].[pDM_Production_RecordInfo]
       (  Column1
        )
        (
          SELECT Column1 FROM INSERTED i
        );

But due to some misinterpretation of SQLCMD usage could not achieve the same.

Here my requirement is to,

Fetch secondary db name from Table1 in Database1
set to some variable
use it to insert data in secondary database

Could anyone please guide me to achieve the same?

What a brittle solution. Does the return value of sfn_GetSecondDbName() really change that often? Where does its value come from? Would be much easier to hard-code the database in the trigger, and change the trigger when that changes, rather than change the function and try to "save work" by not having to update the trigger.

Your current approach will require nested levels of dynamic SQL, and this becomes more complicated because dynamic SQL can't see the scope where inserted is, so additionally you'll have to dump those contents into a #temp table before you can access them from dynamic SQL. Ugly, ugly, ugly.

ALTER TRIGGER [dbo].[CTR_Production_InsertRecordInDatabase2]
ON [dbo].[Table1]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  CREATE TABLE #inserted(column1 INT); -- guessing
  INSERT #inserted(column1) SELECT column1 FROM inserted;

  DECLARE
    @db  SYSNAME = dbo.sfn_GetSecondDbName(),
    @cmd NVARCHAR(320),
    @sql NVARCHAR(MAX) = N'INSERT dbo.pDM_Production_RecordInfo(column1)
      SELECT column1 FROM #inserted;';

  SET @cmd = @db + N'.sys.sp_executesql';
  EXEC @cmd @sql;
END
GO

(Also note that nothing will get inserted in the event of a delete, since inserted will be empty.)

Better yet, store this data in a staging table that is in this database, then create a backup job that moves the data to the other database. That way the trigger doesn't have to do all this dynamic dynamic dynamic SQL, and the transaction that called it isn't affected by any delays there or communicating with the other database (think about the fact that another database could be offline, single user, detached, or the customer could have fat-fingered the name when they changed it - do you want any of these things to block DML on the primary?).


OPENROWSET Bulk insert Text File NULL Columns I need to automate a weekly import of .txt log files into a SQL database. I have already created my table, format file and can get some of the data to work using OPENROWSET BULK, but the data from the log files is not consistent.
The table has 10 columns 9 of which are populated from the log file, last 3 columns are all descriptions but the log file may only have a single description column populated (the max is always 3).
In the image below the highlighted rows work because they have the valid 3 column worth of data separated by a comma. Is there a way to force BCP to always populate the 3 columns or mark them as NULL then move onto the next row?

Ended up using a combination of Power Shell and SQL, PS cleans the files first, adds the correct headers and formatting combined with a format file I have been able to get this to work and it is reliable.
If anyone does have any thoughts or suggestions please post them.


SQL20448N "05/09/2016" cannot be interpreted using format string "YYYY-MM-DD" for the TIMESTAMP_FORMAT function I am getting below error in db2 when executing the sql query-

  SQL20448N  "05/09/2016" cannot be interpreted using format string
  "YYYY-MM-DD" for the TIMESTAMP_FORMAT function.

here is my sql qyery-

SELECT DLR_CD,
       FIN ,
       YEAR(TO_DATE(CURRENT_DATE ,'YYYY-MM-DD'))-YEAR(TO_DATE(CRTNG_DTE,'YYYY-MM-DD'))
       AS AGE
FROM ASPECT.RO_CAR_PARK_EXTRACT WHERE YEAR = PARM_YEAR

what could be the reason for this?

Database - DB2 v9.7.200.358
Platform-  home basic 7

The error message is very clear. I suppose the type of CRTNG_DTE is text/varchar and at least one of the values is not in the YYYY-MM-DD format (05/09/2016 clearly isn't!) and the conversion TO_DATE(CRTNG_DTE, 'YYYY-MM-DD') fails.

The only unclear in the message is the mention of TIMESTAMP_FORMAT() function. This part of the DB2 docs, function TO_DATE() clears that:

  The TO_DATE scalar function is a synonym for the TIMESTAMP_FORMAT scalar function.

The confusion may stem from wrong assumptions about what the TO_DATE() function does.

The TO_DATE(CRTNG_DTE, 'YYYY-MM-DD') is trying to convert strings that have the format YYYY-MM-DD to a date. The dates internal format is irrelevant and you cannot modify it. You cannot choose what format the dates are internally saved as.

Their internal format is a representation of date that is different from DBMS to DBMS and may also change from version to version. And developers need not care what that representation is!

Another issue is the first conversion:

YEAR(TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'))

Since TO_DATE() is expecting a string (to be converted) and a format string but you pass to the function a date and a format string, DB2 converts first the date value (CURRENT_DATE) to a string (I guess using the default in your installation output format of MM/DD/YYYY) so it then tries to calculate:

TO_DATE('05/09/2016', 'YYYY-MM-DD')

which of course fails for the same reason as described above.

So, to solve the issue (or issues):

Use YEAR(CURRENT_DATE) instead for the first calculation.
Make sure that all values of the CRTNG_DTE column have been saved with the format ('YYYY-MM-DD') you expect.


oracle slow query with diferent user I have a question with an oracle query that takes almost 20 minutes when it is executed from a schema and yet when it runs as sys users it runs in 1 second maximum ... how is this possible? ... I do not know much about tuning plsql and I find it difficult to find the cause of this tremendous delay, I would like to know if there is any tool to autotuning the queries. The sentence in question is:

SELECT FC.ID_SER
     FROM MIGER.FONT_COUNTER FC
     WHERE TO_CHAR(FC.ID_SER) NOT IN (SELECT
     ID_SER FROM INAC.SERV)
     -- AND (FC.STATE <>'R' OR FC.STATE <> NULL)
     AND FC.T_ACT='M'

Thank you

In past I noticed that query plan could be different, because of the Locale (language) being used on the client. Mostly the locale is enherited from client OS.


Why is my UTF-8 document raising UTF-8 encoding errors in Azure Data Lake Analytics? I have a document that was compressed in gunzip from a unknown source system. It was downloaded and decompressed using a 7zip console application. The document is a CSV file that appears to be encoded in UTF-8.

It's then uploaded to Azure Data Lake Store right after compression. Then there is a U-SQL job setup to simply copy it from one folder to another folder. This process fails and raises a UTF-8 encoding error for a value: Ã©e

Testing

I downloaded the document from the store and removed all records but that one with the value flagged by Azure. In Notepad++, it shows the document as UTF-8. I save the document as UTF-8 again and upload it back to the store. I run the process again and the process succeeds with that value as UTF-8

What am I missing here? Is it possible the original document is not truly UTF-8? Is there something else causing a false positive? I'm a bit baffled.

Possibilities

The document is not truly UTF-8 and needs to be recoded
Maybe the method that's uploading the file is recoding it
Maybe 7zip is recoding it incorrectly

Environment/Tools

Windows Server
Python 2.7
Azure Data Lake Store
Azure Data Lake Analytics
7Zip.exe
gz
Azure API

USQL

Just the base USQL job that defines the schema then selects all fields to a new directory. No transformation happening outside of leaving out the headers. The file is CSV, comma delimited with double quotes on strings. Schema is all strings regardless of data type. Extractors tried is TEXT and CSV with both set to be encoded:UTF8 even though both are default to UTF8 according to Azure documentation on the system.

Other Notes

This same document was uploaded in the past to BLOB storage and imported in the same fashion into Azure Data Warehouse without errors via Polybase.
The value that causes the UTF-8 encoding error is a URL mangled among 1 million other records.
It looks like there are ASCII characters coming in even though it's a UTF-8 document.
When I convert it to ANSI and use the ASCII extractor the file succeeds.
Azure Data Lake Analytics does not allow you to ignore the error as it's an encoding issue. I'd be happy invalidating the record all together like you can in Azure Data Warehouse.

Notes

There are several things to go over here:

Just to get this out of the way: gz[ip] and 7zip.exe have nothing to do with any of this. Compression does not alter encoding or any of the original bytes (else compression / decompression wouldn't be reliable). Well, theoretically there can be a bug in one of them such that the decompressed output is somehow slightly different, but that would be a wide-spread problem, I would think, and these algorithms, and these two tools in particular, have been around for a long time and are known to be reliable.
Keep in mind: files, even text files, contain bytes, not characters. Those bytes can represent some set of characters, or another set, or something else. But in the end, it is just a collection of bytes. So if you see Ã, that is not because there is an Ã in the file, it is due to there by one or more bytes that are currently being interpreted as representing an Ã. It could be that those same bytes should truly be interpreted as representing something else. And, at the same time, it could also be that some other sequence of bytes, under a different interpretation, also represent Ã.

  Then there is a U-SQL job setup to simply copy it from one folder to another folder.

Ok, this right here should raise a red flag: why would "simply copying a file" produce an encoding error? There can only be an encoding error if the file is being read, else it's just a collection of bytes moving from point A to point B (or perhaps it's merely being relinked and not moving, but either way).

  This process fails and raises a UTF-8 encoding error for a value: Ã©e

Here is another key indicator (that is likely being misinterpreted by some): the error is a UTF-8 encoding error. This tells us two things:

The file is being interpreted as UTF-8 already, so the byte sequences are not being interpreted as some other encoding (e.g. Windows-1252, or ISO-8859-1). This means that the Ã© characters are UTF-8 encoded bytes already (i.e. 0xC383C2A9), and not Windows-1252 bytes (0xC3A9) that should instead be interpreted as UTF-8 to produce é.
The fact that it's an error means that Ã©e is not the error, because if it was the error, then it wouldn't be able to display it. An encoding error is when a byte sequence can't produce a character in that encoding. Meaning: there is something missing from the Ã©e sequence. The Ã©e sequence should be the remaining parts of what could be decoded from the bytes in the file.
Keep in mind that Unicode is a specification, not software. The implementation is left up to each vendor. There are guidelines regarding compliant implementations, and those include how to handle decoding errors. But, there is some flexibility as well. So some implementations might throw a hard-error in this case, or others might display the "replacement" character, �, or some might display nothing in that position.
In either case, the Ã©e sequence, taken literally, is most likely a red-herring and not what we should be looking at.

  In Notepad++, it shows the document as UTF-8. I save the document as UTF-8 again and upload it back to the store. I run the process again and the process succeeds with that value as UTF-8

Ok. So, in Notepad++, when it shows "UTF-8" on the right side of the bottom bar upon opening a file, that is not necessarily a guarantee that the file is, in fact, encoded as UTF-8. That is a best-guess, probably based on common byte sequences. If the encoding indicator instead showed "UTF-8-BOM", then that would be a guarantee that the file was encoded as UTF-8. The "-BOM" indicates that a Byte Order Mark is present. a BOM is an optional 2 - 4 byte sequence at the beginning of a file that should be hidden but indicates the encoding of the file. This is only available for Unicode. There are two 2-byte BOMs for UTF-16, one for Little Endian and one for Big Endian. There are also two 4-byte BOMs for UTF-32, one for each Endianness. UTF-8 only has a single 3-byte BOM since Endianness does not apply to UTF-8 (since the code units are a single byte, there is only one order that the byte can be in). Of course, having a BOM does not guarantee that it will be respected or even understood by whatever is reading the file.
In the case presented here, having a BOM should not have any affect on the outcome since the file is already being read as a UTF-8 file.
And again, if Notepad++ is showing Ã©e and shows "UTF-8" in the lower-right corner, then it's already UTF-8 instead of Windows-1252 / ISO-8859-1 that needs to be told to be read as UTF-8 in order to produce é.
If Notepad++ thought that the file was UTF-8, then saving the file as UTF-8 shouldn't have really changed anything. Something else must have changed.

  The file is CSV, comma delimited with double quotes on strings.

and:

  It's a URL mangled among 1 million other records.

So, is Ã©e the entire value for that field? Is it within double-quotes in the file? Ã©e does not appear to be a URL ;-)

Also, does the value shown in the error match, exactly, the value you see in the file? If so, there is likely a hidden character in the file that is causing this problem.

  Looks like there are ASCII characters coming in even though it's a UTF-8 document.

What do you mean by "ASCII" characters? One of the features of UTF-8 is that the encoding of the first 128 code points (U+0000 through U+007F) are identical to standard ASCII. This was the primary design goal of UTF-8: full ACSII compatibility (not 8-bit Extended ASCII, just 7-bit standard ASCII). So, if the majority of the document is using US English characters and punctuation, then yes, you should expect to see that just the same whether you open the file as ASCII or UTF-8.

  When I convert it to ANSI and use the ASCII extractor the file succeeds.

This is not surprising. I don't think it's possible to get an ANSI / ASCII encoding error. There are only a few code points / values that are undefined in code page Windows-1252, and they generally behave as "hidden" characters.

Of course, if you do have valid UTF-8 encoded characters in that file, converting to ANSI will change them into "?" if they are not available in Windows-1252.

Take-aways

If this were an ANSI / Windows-1252 / ISO-8859-1 encoded file such that Ã© indicated a byte sequence of 0xC3A9, then there would be no UTF-8 encoding error since the byte sequence of 0xC3A9 is valid UTF-8 for é.
Since the error itself cannot be shown (else it wouldn't be an encoding error):

Ã©e isn't the error, but instead a result of the error
We need to see the exact bytes of the row that has the encoding error in order to see what could not be decoded.

Nothing says that a file needs to be imported as UTF-8. If a file (even if not this one) is encoded as Windows-1252, ISO-8859-1, or anything else, and the data is all correct in the file, then rather than trying to force the file to be UTF-8, it is better to tell whatever process / tool is reading the file what the actual file encoding is. There is no reason to not import a Windows-1252 encoded file as Windows-1252.

Bottom-line is: there is too much guessing / wild-goose-chase without seeing the original bytes of the line that has the encoding error. Having that line will help determine both a) what happened, and b) how to proceed.

If the poster of the bounty could add a comment to this answer providing additional details, especially the exact byte sequence that is getting the error, that would help solve this.


InnoDB table vs MEMORY table for a mysql environment with many inserts I run my sites all on InnoDB tables which is working really well so far. Now I like to know what is going on in real-time on my sites, so I store each pageview (page, referrer, IP, hostname, etc) in an InnoDB table. There are about 100 inserts per second, and this table is only read once in a while when i'm browsing the logs.

I clean out the table every minute with a cron that removes old items. This leaves about 35.000 rows in that table on average, with a size of about 5MB.

Would it be easier on the server if I were to transfer the InnoDB table to a MEMORY table? As far as I can see this would save a lot of disk IO right? Restarting Mysql would result in a loss of data, but this does not matter in my case.

Question: In my case, would you recommend a Memory table over a InnoDB table?

Clearly, it will be faster and more efficient to write/read to/from memory. In your case, losing the data is not a big issue as you stated. However, you need to be careful about the memory usage and limit the number of rows stored.


SQL Server - Required Permissions For Executing Stored Procedures With SHOWPLAN ON In order to get a stored procedure plan I'm running the following code:

set showplan_all on
exec sp_name param
set showplan_all off

Now I'm wondering if there's any way of doing that without granting execute permissions to the user running this code. After all, this code does not really execute the procedure but rather display it's plan.

The user must have permission to run the stored procedure at the minimum to get the showplan information.  You can just grant the user access to run the one stored procedure and not all of them.

GRANT EXECUTE ON sp_name TO username;


Can I pass in a frozen time as a setting into postgresql and not have clock drift? I would like to freeze time on a connection level across multiple transactions. Is there a conventional way of achieving this similar to

SET search_path = 'foo';

Rather than me passing in the time and binding it on each statement myself? Ideally I would want to be able to use NOW() and write conventional SQL, but freeze that time across multiple transactions.

The reason for this is because I want to avoid clock drift on a single web request.

You could abuse a placeholder variable:

SET request.time = '2019-11-21 09:00:00';

Then you can use current_setting('request.time') in your queries to retrieve the string.


JOIN 2 tables, and query data from them 3 times Basically I have 2 tables: post_meta and posts.

post_meta has a post_id, key and value:

post_id  key      value
6343     make     Chevy
6343     model    Volt
6794     make     Chevy
6794     model    Silverado

posts has a post_id and content:

post_id  content
5434     Hello World!
6343     Chevy Volt review
6794     The New Silverado

What I need to do is pull the model based on the make (pull all unique models with the make "Chevy").  The database isn't very big so it doesn't need to be a real fast query.  Basically the plan in my mind was to get the model of every post_id that has the post_meta.make "Chevy".

SELECT post_id FROM postmeta
WHERE key = 'make'
AND value = 'Chevy'

then take the post_id

SELECT DISTINCT value FROM postmeta
WHERE post_id = {an array of post_id's}

something to that extent.

Given your table structure, this should extract what you are looking for:

SELECT DISTINCT p1.value
FROM postmeta p1
INNER JOIN postmeta p2 ON p1.post_id = p2.post_id
WHERE p2.key = 'make'
    AND p2.value = 'Chevy'
    AND p1.key = 'model';


Removing Quotes From String I am passing a parameter to a dynamic sql string and have the parameter properly encompassed in ' marks to work in my dynamic sql.  However, now I am needing to use the value in a diff location in my code, and want to remove all the ' marks.  I tried the Replace() function, but I must be using incorrect syntax as it still leaves the string with single quotes on each side.  Take the sample DDL Below

DECLARE @Param1 nvarchar(500) = N'''Blue Bell Ice Cream''';
PRINT REPLACE(@Param1, '''''', '')

Prints this

  'Blue Bell Ice Cream'

My desired result to be returned is

  Blue Bell Ice Cream

How should I write the REPLACE() function in order to remove all single quotes?

You are using 6 quotes, this is not correct, you are trying to replace every single quote ', then use only a double quote '' to indicate the string pattern.

If you use '''' four quotes you are really searching for a double ''

REPLACE(@Param1, '''', '')


Merge Replication - Deleting from the subscriber database? I have 2 SQL Server instances where I use merge replication on a database. A developer accidentally inserted data to both the subscriber and publisher at the same time.  Now I have data in both databases but with different rowguids.

Q: Can I delete those rows with different rowguids from the subscriber and the rows in the publisher remain untouched? If so, will the rows from the publisher replicate back to the subscriber but now with the same rowguid?

If the same logical row has two different rowguid values at publisher and subscriber, then you're having a conflict.

Changes are replicated using the rowguid column, so different rowguid means different rows as far as replication is concerned. You can safely delete the duplicate row at the subscriber and the changes won't be replicated to the publisher (the row with a matching rowguid is not present at the publisher due to the upload conflict).

Once the row is deleted at the subscriber, the merge agent will be able to insert at the subscriber the row from the publisher.


What would be the first things you do to find out about an MYSQL environment you are taking over? I am new to DBA role, and wanted to know if there was any commands (or free software) that come with Mysql 5.5 (master/slave/slave) replication to quickly get familiar with
the database, and also making sure my.cnf is set up with the correct parameters.

The pt-mysql-summary tool from Percona toolkit gives you a very nice overview of your running server.

It will tell you stuff like what the major config params are set to, what technologies ara available in your server, take a 10 second status sample, analyze your schema etc.

Also worth to run pt-duplicate-key-checker at least once, to detect redundant keys.

I'm authoring the common_schema tool for MySQL, which also offers some diagnostics: the redundant_keys view is parallel to pt-duplicate-key-checker; the data_size_per_schema view will tell you, well, the datasize per schema; the auto_increment_columns view will tell you about "free space" in your AUTO_INCREMENT columns; and more.

Watching the processes as they go, using innotop or the old-timer mytop is also worthwhile: get to know the processes that run most, the longest etc.


How to log multiple invoke-sqlcmd verbose outputs into single log file along with showing/not showing them through flag into prowershell prompt Lets say we have multiple invoke-sqlcmd statements like below:
invoke-sqlcmd -Query "PRINT 'Hello World 1'" -ServerInstance Server\SQL2017 -Database abc -Verbose
invoke-sqlcmd -Query "PRINT 'Hello World 2'" -ServerInstance Server\SQL2017 -Database abc -Verbose
invoke-sqlcmd -Query "PRINT 'Hello World 3'" -ServerInstance Server\SQL2017 -Database abc -Verbose

I want to do the following things:

I want to log verbose output statements of multiple invoke-sqlcmd commands into a single log file
Along with logging into the log file, can we show the verbose output in powershell window too? Also, can we manage it through any flag to show the output in powershell window or not?

Please suggest if it's possible or not.

For 1, you can use PowerShell redirection operators. As per Redirectable output streams, steam 4 is Verbose Stream which is what you want.

invoke-sqlcmd .... -verbose 4>> "D:\temp\yourlogfile.log"

Above will append all your invoke-sqlcmd into just one log file "D:\temp\yourlogfile.log"
your question part 2 is unclear to me.


SSIS Derived Column Conditional Time Modification I'm using SSIS to transfer data from a MySQL database to a SQL Server one. There's one point that I'm currently stuck on, however. MySQL TIME datatype supports values greater than or equal to 24:00:00, whereas SQL Server TIME caps at 23:59:59. One of the tables I'm transferring has a TIME column that, in MySQL, is 24:00:00. As logically in our business case this is a 'stop time' and thus equivalent to 00:00:00, I'm attempting to find a way to use SSIS to convert to that should that value come up.

Derived columns seem the way to go about doing this, but I don't seem to have a firm grasp on the syntax and very few resources are out there for TIME literals in SSIS conditional expressions. This is what I'm attempting to work with so far:

time == '24:00:00' ? time = '00:00:00' : time = time

Which of course returns only errors as I'm certain that part of that syntax is incorrect. I have (I believe) the logic, just spottily documented syntax is the issue.

So close. It'd be

time == "24:00:00" ? "00:00:00" : time

The Ternary Operator looks like (condition) ? true : false With the notable exception of the ForEach Enumerator, nowhere else in SSIS expressions are you able to use an assignment like time = "00:00:00". It'll basically be implied by whatever column or variables it's being assigned to.

If the data type of time column is actually detected as DT_TIME the above expression is likely to break because that would be outside the allowable domain for DT_TIME values but it'd actually break/fail/error at the source component level (OLE DB/ADO NET/ODBC source)


SQL Transactions: When do I Roll Back? This is probably a simplistic question, but I would like to check on the mechanics of rolling back a transaction.

As far as I understand, the steps are as follows:

BEGIN TRANSACTION (or equivalent)
One or more SQL statements
COMMIT or ROLLBACK

As far as I know, the transaction will be automatically rolled back if one of the SQL statements fails, such as an invalid INSERT or UPDATE.

This would suggest that the above process, up to committing could be in a manually entered collection of statements.

However, at what stage do I make the decision to roll back? Does this suggest that ROLLBACK is more something that would occur, say, in a procedure? In other words, does one ever roll back manually?

By procedure, I’m not necessarily referring to a stored procedure. I suppose some application might be doing the same thing.

I think this question applies to any SQL database in general (provided it supports transactions, of course).

In other words, does one ever roll back manually?

No not as such, unless someone is doing testing I have hardly seen people manually doing a rollback. The rollback is specified in stored procedures or transactions in which you either want complete change to be entered or nothing at all.

  As far as I know, the transaction will be automatically rolled back if one of the SQL statements fails, such as an invalid INSERT or UPDATE.

Depending on how transaction is initiated, if it is under explicit begin transaction and commit yes it will rollback completely to its initial state after the query fails, while if the transaction is not under begin transaction and commit it will store the changes done till the query failed and from after the query failed the it would be stopped from making further changes.

A classic case where system initiates a rollback is when a deadlock happens and SQL Server by internal mechanism chooses deadlock victim, kills the transaction and it may or may not rollback depending on whether it was explicit or implicit transaction. Like when normal select statement is killed their is no rollback as such.


Get a specific number of rows from parent table? I have a parent table, foo, and a child table, bar, in a mySQL database.

Each row in foo may have zero or more child records in bar.

I want to get exactly 15 rows from the parent table, and all their child records. I initially did:

SELECT <columns>
FROM foo
LEFT JOIN bar
    ON foo.a = bar.a
WHERE <condition>
ORDER BY <condition>
LIMIT 15;

However, this will give me 15 joined rows. It's possible that the last row retrieved is a foo with multiple bar's, and that this query doesn't get all of the bar's. My requirement is that if a foo is in the result set, all of the bar's associated with that foo have to be returned as well.

What's the most efficient way to write a query that will get me 15 foo's with all of their bar's? I can think of a couple of ways of doing this, but none of them seem to be terribly elegant. (Doing a select on the parent table and then doing a second query with a join using WHERE <foo.id> IN ..., for example.) I'm not a DBA so I figured I'd ask the pros here.

Here's one way to do what you describe:

SELECT <columns>
FROM (
    SELECT <columns>
    FROM foo
    WHERE <condition>
    ORDER BY <expression-list-1>
    LIMIT 15
) AS T
LEFT JOIN bar
    ON T.a = bar.a
ORDER BY <expression-list-2> ;

You basically get 15 rows from foo according to some criteria and join the result with bar.

The first, internal ORDER BY list affects which 15 rows will be selected. The second, final list affects only the presentation ordering of the results.

The two ORDER BY lists do not need to be identical, although in practice they'll often be the same or the second an extension of the first, to resolve ties (how the children of the same parent are ordered).


Run SSIS Package as different user I have been googling for ages and messing around with the package properties but I can't seem to be able to get my package to run as a different user than my current windows login from within Visual Studio.

In particular I want a connection manager to run as a different windows account and not a SQL account.

Can anyone advise?

Amusingly enough, you have the answer in your question, specifically, run as. RunAs and in case MS butchers links again, RunAs

RunAs allows you to run a program as another user. You can't automate the entry of credentials, which is a pain and you won't be able to see your password but it does work. It also fails in situations like SSRS development when you attempt to preview a report, that is spawned in a separate process which does not inherit the credentials. But, I had no issues with it from an SSIS perspective.

runas /netonly /user:OtherDomain\billinkc "devenv.exe /nosplash"

where "devenv.exe" is the full qualified path to your Visual Studio version and whether you have a 64 bit OS. On my machine, that could be

C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe
C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe
C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\devenv.exe
C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe


Best solution for cross-datacenter MySQL master-slave replication We are working on a new system architecture for our company. We have a HPC, which runs in our own data-center and we are planning our front-end and a fallback system on Amazon Web Service.

System Architecture:

Preconditions:

The HPC cluster has a lot of write operations
Website and API both read the data most of the time and write rarely
Ping from AWS to our cluster is ~35ms
In case of our local data-center failing, the HPC should be replicated on AWS, turning the MySQL Slave into the new Master

Question:

What is the best solution to replicate the MySQL database in such a setup?

There are three(3) suggestions I have that you may want to look into...
SUGGESTION #1
You could use MySQL 5.5 both locally and in AWS. You would also setup Semisync Replication. That way, every SQL statement executed on the Master has its latest recorded SQL statement sent over to the Slave.
In my past post (Jul 26, 2012 : Mysql database replication on different vlan/subnet/another site) I had recommended SemiSynchronous Replication over a geographic distance. Interestingly, @AaronBrown points out by experimentation that High Latency would degrade Semisynch Replication (See his blog on this one).
SUGGESTION #2
If all your data is InnoDB, you could use Percona XtraDB Cluster (preferable to have two Slaves in AWS but one would do). Why Percona XtraDB Cluster? You have synchronized writes. You only major bottleneck would strictly be network communication (again High Latency would have an adverse affect)
Here are my past posts on using Percona XtraDB Cluster

Aug 26, 2011 : MySQL Master/Slave Master/Master setup?
Jul 25, 2012 : Scaling Percona datacenters: setup and replication

SUGGESTION #3
If you want to strengthen each data center's local copy of the data, I would suggestion using DRBD, not over a geographic distance, but having a DRBD cluster in each data center. In your case, at least have DRBD in the local data center.
Here is my past post on this suggestion :

Mar 29, 2011 : MySQL high availability, failover and replication with Latency
Aug 29, 2011 : MySQL Replication : 1 Slave / Multiple Masters


How to log verbose output from mysqldump? You typically save the verbose output of a Linux command like this:

# command > output.txt

But when you use a command like mysqldump, the > option outputs the dump of the database tables to the desired file:

# mysqldump --username=whatever --password=whatever -h localhost database > dump.sql

If you add in the -v (verbose) option to mysqldump, it outputs help information about what the command is doing. How do I save this verbose output to a file since the > option is used to output the database table information to a file?

More specifically, I'm dumping the output of one database into another database like this:

mysqldump -alv -h 123.123.123.123 --user=username --password=p@ssw0rd --add-drop-table databasename | mysql --user=username --password=p@ssw0rd -h localhost localdatabase

I tried the following but the output file was empty in the end

mysqldump -alv -h 123.123.123.123 --user=username --password=p@ssw0rd --add-drop-table databasename | mysql --user=username --password=p@ssw0rd -h localhost localdatabase > output.log

Also tried the following, but it redirected the database information to the output.log instead of the target databse:

mysqldump -alv -h 123.123.123.123 --user=username --password=p@ssw0rd --add-drop-table databasename > output.log | mysql --user=username --password=p@ssw0rd -h localhost localdatabase

And I don't see any sort of --log-file=FILE option for mysqldump either.

Your 1st mysqldump makes table structures and INSERTs and puts it in dump.sql.

Your 2nd dump is a remote dump that is piped straight into mysql in localhost.

If you are trying to catch any output based on errors, try this:

mysqldump -alv -h 123.123.123.123 --user=username --password=p@ssw0rd --add-drop-table databasename 2> output.log | mysql --user=username --password=p@ssw0rd -h localhost localdatabase

Using 2> will catch any error-based output (aka stderr). The mysqldump should still pipe normal console output (aka stdout) to the other mysql session and load the data as intended.

EXAMPLE : I have a small database called sample on my PC.

I ran this:

C:\LWDBA>mysqldump -u... -p... --verbose sample 2>sample.txt > sample.sql

C:\LWDBA>type sample.txt
-- Connecting to localhost...
-- Retrieving table structure for table users...
-- Sending SELECT query...
-- Retrieving rows...
-- Disconnecting from localhost...

C:\LWDBA>type sample.sql
-- MySQL dump 10.13  Distrib 5.5.12, for Win64 (x86)
--
-- Host: localhost    Database: sample
-- ------------------------------------------------------
-- Server version       5.5.12-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `users_tbl_points` int(11) NOT NULL,
  `users_tbl_rank` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `users_tbl_points` (`users_tbl_points`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1,785523,9),(2,443080,20),(3,858830,7),(4,964909,3),(5,248056,24),
(6,345553,21),(7,983596,2),(8,881325,6),(9,455836,19),(10,635204,16),(11,808514,8),
(12,136960,28),(13,259255,22),(14,885399,5),(15,649229,15),(16,589948,18),(17,2055,30),
(18,240429,25),(19,195981,26),(20,258620,23),(21,705158,12),(22,749931,11),(23,634182,17),
(24,921117,4),(25,703038,13),(26,751842,10),(27,650093,14),(28,994943,1),(29,24437,29),
(30,137355,27);
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-03-02 15:49:54

C:\LWDBA>

Give it a Try !!!


Join one-to-many field in a single result without concatenation I suffer from the same problem as in Concatenate one-to-many field in single result?, but I need a result where the 'many' fields occupy their own columns and aren't concatenated in one column. Any suggestions?

Sample data:

___ID      Mapped_Heading    Source_Value
1          English           pass
1          Math              pass
1          History           fail

___ID      Name              Age
1          Bob               12
2          Harry             12
3          Emily             14

And the End result would be:

___ID      Name      Age  English   Math   History
1          Bob       12   pass      pass   fail

I was hoping I wouldn't have to use PIVOT and PARTITION BY because I'm not familiar with those functions, but I keep bumping into them for this problem so think I might have to?

One way you can do this is with a PIVOT.

Here is the setup code:

create table grades (
  ID             int,
  Mapped_Heading varchar(10),
  Source_Value   varchar(10)
);
create table students (
  ID   int,
  Name varchar(10),
  Age  int
);
insert grades
values
(1,'English', 'pass'),
(1,'Math',    'pass'),
(1,'History', 'fail');
insert students
values
(1, 'Bob',   12),
(2, 'Harry', 12),
(3, 'Emily', 14);

Here is the PIVOT code:

with temp as (
  select s.*, g.Mapped_Heading, g.Source_Value
  from students s
  join grades g on g.ID = s.ID
)
select *
from (
    select *
    from temp
) a
pivot (
    max(Source_Value)
    for Mapped_Heading in ([English],[Math],[History])
) as b

The output looks like this:

+----+------+-----+---------+------+---------+
| ID | Name | Age | English | Math | History |
+----+------+-----+---------+------+---------+
| 1  | Bob  | 12  | pass    | pass | fail    |
+----+------+-----+---------+------+---------+

db<>fiddle demo


Insert data between 30 minute time intervals I want to divided my to my time interval depending to the start time and end time. To explain it further, let me have this example.
I have this table:

User           Elapsed Time        Start Time            End Time
User01         01:05               07-02-2015 08:45      07-02-2015 09:50

I want to divide its elapsed time to 30 minute time interval so that i will have an output like this.

Interval From        Interval To         User       Elapsed Time
07-02-2015 08:30     07-02-2015 09:00    User01     00:15
07-02-2015 09:00     07-02-2015 09:30    User01     00:30
07-02-2015 09:30     07-02-2015 10:00    User01     00:20

Thanks in advance

Here's a different approach that doesn't rely on loops or additional tables, however it does touch the source table twice. (You'll have to replace dbo.aTable with the name of your actual table, and add where clauses where commented below if you want to filter.)

DECLARE @i INT = 30; -- interval in minutes

DECLARE @ft SMALLDATETIME, @lt SMALLDATETIME;
SELECT @ft = MIN([Start Time]), @lt = MAX([End Time]) FROM dbo.aTable -- WHERE?;

;WITH d1(dt) AS
(
  SELECT TOP (DATEDIFF(MINUTE,@ft,@lt)/@i+2)
    DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(MINUTE,@i*(ROW_NUMBER()
    OVER (ORDER BY [object_id])-1),@ft))/@i*@i,0)
  FROM sys.all_columns
), d2(s,e) AS (SELECT dt,DATEADD(MINUTE,@i,dt) FROM d1
), t AS
(
  SELECT [User],[Start Time],[End Time],
    StartInterval = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [Start Time])/@i*@i, 0),
    EndInterval   = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [End Time])/@i*@i, 0)
  FROM dbo.aTable -- WHERE?
),
n AS
(
  SELECT d2.s, d2.e, [User], [Start Time], [End Time],
    sr = CASE WHEN [Start Time] >= d2.s AND [Start Time] < d2.e THEN 1 END,
    er = CASE WHEN [End Time] > d2.s AND [End Time] <= d2.e THEN 1 END
   FROM d2 INNER JOIN t ON d2.s BETWEEN t.StartInterval AND t.EndInterval
 )
 SELECT [Interval From] = s, [Interval To] = e, [User],
   [Elapsed Time] = CASE WHEN sr = 1 OR er = 1 THEN
       DATEDIFF(MINUTE, CASE WHEN sr = 1 THEN [Start Time] ELSE s END,
       CASE WHEN er = 1 THEN [End Time] ELSE e END)
   WHEN s = [End Time] THEN 0 ELSE @i END
 FROM n ORDER BY [User], [Interval From];


Migration from Sybase to a different platform How feasible it is move the Sybase database to a different more popular platform such SQL Server or any other. Which one is preferred? Is it feasible in the first place?

How feasible it is move the Sybase database to a different more popular platform such SQL Server or any other.
Is it feasible in the first place?

Very feasible and supported by the vendors (e.g. Microsoft in-case of SQL Server). I have done plenty of SybaseASE to SQL Server migrations.
Best is to use SQL Server Migration Assistant (SSMA) for migration.
Refer to :

Migrating from Sybase ASE to SQL Server – How to use SSMA for Sybase
SQL Server Migration Assistant (SSMA) Team's Blog

Which one is preferred?

Its up to your business what RDBMS they want to use. We are a Microsoft shop running majority of our software on Microsoft stack and so we move our Sybase Clients to SQL Server.
If you have any specific step that you find hard to proceed further, let me know and I will help you out, but above links will give you  a good start.


Cannot induce exit code 3 from psql The docs page states (emphasis mine)

  Exit Status

  psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own occurs (e.g. out of memory, file not found), 2 if the connection to the server went bad and the session was not interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set.

However, I seem to be unable to induce exit code 3.

bash-4.2$ uname -a
Linux mvpg-centos-76.vagrantup.com 3.10.0-957.21.3.el7.x86_64 #1 SMP Tue Jun 18 16:35:19 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
bash-4.2$ psql --version
psql (PostgreSQL) 10.12
bash-4.2$ psql -v ON_ERROR_STOP=1 -c 'select 1/0;'
ERROR:  division by zero
bash-4.2$ echo $?
1
bash-4.2$

I've also reproduced this behaviour on FreeBSD 11.3 and Darwin 19.3. Am I misunderstanding the documentation or using a faulty syntax?

Got it to work a few ways:

postgres@ironforge:~$ cat onerrorstop.sql
\set ON_ERROR_STOP on
select * from gurumeditationerror;
postgres@ironforge:~$ psql -f 'onerrorstop.sql'
psql:onerrorstop.sql:2: ERROR:  relation "gurumeditationerror" does not exist
LINE 1: select * from gurumeditationerror;
                  ^
postgres@ironforge:~$ echo $?
3
postgres@ironforge:~$

and:

postgres@ironforge:~$ echo "\set ON_ERROR_STOP on
> select * from smellyfarts;" | psql
ERROR:  relation "smellyfarts" does not exist
LINE 1: select * from smellyfarts;
                  ^
postgres@ironforge:~$ echo $?
3
postgres@ironforge:~$

and:

postgres@ironforge:~$ cat onerrorstop.sql
select * from gurumeditationerror;

postgres@ironforge:~$ psql -v ON_ERROR_STOP=on -f 'onerrorstop.sql'
psql:onerrorstop.sql:1: ERROR:  relation "gurumeditationerror" does not exist
LINE 1: select * from gurumeditationerror;
                      ^
postgres@ironforge:~$ echo $?
3
postgres@ironforge:~$

Version:

    postgres@ironforge:~$ psql --version
    psql (Post greySKULL) 9.5.19
    postgres@ironforge:~$


MariaDB: disable foreign key checks On MySql, in order to truncate a table or delete rows when it's normally impossible because of foreign keys (InnoDB only), we use this command:

SET FOREIGN_KEY_CHECKS=0;

On MariaDB, while this command is accepted, it does nothing.

The documentation says I have to run these commands instead:

On a per-table basis:

ALTER TABLE `...` DISABLE KEYS;

or, globally:

SET @@session.unique_checks = 0;
SET @@session.foreign_key_checks = 0;

So I tried to run this script:

SET FOREIGN_KEY_CHECKS=0;
SET @@session.unique_checks = 0;
SET @@session.foreign_key_checks = 0;
ALTER TABLE `country` DISABLE KEYS;
DELETE FROM `country` WHERE 1;

And that leads me to:

  Cannot delete or update a parent row: a foreign key constraint fails (database.region, CONSTRAINT FK_F62F176F92F3E70 FOREIGN KEY (country_id) REFERENCES country (id))

Using TRUNCATE has the same effect.

If you have any idea on what I may have missed, thank you by advance. Because the documentation and the existing questions weren't much of help here.

It should work with:

SET FOREIGN_KEY_CHECKS=0

I tried the following:

create table parent (x int not null primary key) engine = innodb;
create table child (x int not null primary key, constraint aaa foreign key (x) references parent (x) on delete restrict) engine = innodb;
insert into parent (x) values (1),(2);

-- test if f.k is active
insert into child (x) values (1),(3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ("test"."child", CONSTRAINT "aaa" FOREIGN KEY ("x") REFERENCES "parent" ("x"))

insert into child (x) values (1);
SET FOREIGN_KEY_CHECKS=0;
delete from parent;
select * from parent;
Empty set (0.00 sec)

select * from child;
+---+
| x |
+---+
| 1 |
+---+

select @@version
-> ;
+-----------------+
| @@version       |
+-----------------+
| 10.1.16-MariaDB |
+-----------------+

So the problem is not with MariaDB, but with PhpMyAdmin. It has a checkbox on the pages where you can execute SQL, that overrides SET FOREIGN_KEY_CHECKS=. One must uncheck it if one wants to disable foreign key validation.


UPDATE ... FROM ... syntax for iSeries for a MS SQL Server developer When I wanted to update a table to the values from another table in SQL Server I would use following syntax:

UPDATE t1
SET t1.a = t2.a
FROM t1
INNER JOIN t2 ON t2.b = t1.b

What would be iSeries 7 syntax for that?

I don't know about iSeries7 but some DBMS (like Oracle) allow this - as long as t2 (b) is a unique or primary key:

UPDATE
  ( SELECT t1.a AS t1a, t2.a AS t2a
    FROM t1
      INNER JOIN t2 ON t2.b = t1.b
  ) u
SET
    t1a = t2a ;

Tested at (Oracle 11g) SQL-Fiddle

Other DBMS allow this syntax (like SQL-Server which doesn't make any fuss about the uniqueness, although that is not a good thing*):

WITH u AS
 ( SELECT t1.a AS t1a, t2.a AS t2a
   FROM t1
   INNER JOIN t2 ON t2.b = t1.b
 )
UPDATE u
SET
 t1a = t2a ;

Tested at (SQL-Server 2008) SQL-Fiddle

*See the blog article: Let's deprecate UPDATE FROM! where Hugo Kornelis mentions):
 ... SQL Server will happily update the same row over and over again if it matches more than one row in the joined table, with only the result of the last of those updates sticking. ...

You can also check this syntax that doesn't use CTEs or updatable views - and thus will work in almost all DBMS:

UPDATE t1
SET a =
  ( SELECT t2.a
    FROM t2
    WHERE t2.b = t1.b
  )
WHERE EXISTS
  ( SELECT *
    FROM t2
    WHERE t2.b = t1.b
  ) ;

According to the online manual for DB2 i series7 : UPDATE - and if I read it correctly - only the 3rd way is an option in DB2 for iSeries7. The 1st way will work too if the derived table (u) has been defined as a view.


How many files are created by default using WiredTiger storage engine? So, I got a question on a test (without answer unfortunately) that left me with a big doubt whether it's true or false.

The question is this: "By default, WiredTiger storage engine creates one file per collection and per index".

I went to check all the documentation, but I couldn't find a really satisfying answer to this question. I would assume by logic that it of course creates one file per collection and one per index, but how to be sure about that? I think those files are managed by the DB system and thus are not visible to the user.

I just remember that somewhere there are the files of the database looking like dbname.0 and when it grows after a certain threshold, it creates another file dbname.1 etc. so I would assume the collection is inside that file and not a separate entity, thus making the test statement wrong?

Yes, WiredTiger Engine creates one file per collection and per index.

Files are the part of storage layer that is being maintained by the Storage Engine part. you can locate the created files in the dbpath specified in the configuration file used to start the server instance. For example in case if your underlying OS is ubuntu the default config file path is:

/etc/mongod.conf

In case of WiredTiger engine when you create any collection, then you will be able to find new files like:

collection-22--9037026691096837958.wt
index-23--9037026691096837958.wt

here numbers 22,23 indicates the unique identity of collection and index to the storage engine and being maintained withing the storage engine's metadata.

in last part of your question about threshold, you may be interested in storage capacity limits, so you can consider following these links:

https://docs.mongodb.com/manual/reference/limits/
https://stackoverflow.com/questions/4327723/mongodb-limit-storage-size


How to get psql to prompt for a password with the superuser postgres? I'm using PostgreSQL 9.6.2 on Windows 7 and when I start psql from the command prompt with:

psql -U myusername

it asks for a password just fine and logs in.
However if I do:

psql -U postgres

it's gonna log in directly to the main database with superuser rights without asking for any kind of password. If I type in:

psql -U postgres -W

then it's gonna ask for the su password which is by the way the one that I set during the one-click installation.

I've checked the pg_hba.conf and it's set to md5 for all users, all databases. I've even changed the password from the db with: postgres=# ALTER USER postgres PASSWORD 'myPassword';, to no avail.

The question is why when I log as a normal user it asks for a password and doesn't when i log as superuser?  It's not a major issue as I can access my databases in either case, but it just doesn't seem safe at all. Any workaround would be very much appreciated.

why when I log as a normal user it asks for a password and doesn't when i log as superuser?

  it just doesn't seem safe at all.

The small but important detail that you're missing is that nobody but you (or your DBA Team) should be allowed anywhere near the database server and absolutely should not have the postgres account credentials.  Without these, "logging in as postgres, with or without a password" simply won't be a option for them.

Remember: As the DBA, you'll have to cleanup the "mess" that other people make, so always keep the biggest and best tools for yourself!


Import batches of rows from Excel in parallel We receive an Excel file weekly currently containing about 250k rows. The file grows by about 300 rows each week.

In SSIS I've hooked up an Excel source to an OLE DB destination to import the file but it takes a long time.

I've looked at using the SQL Command to specify how to select part of the sheet but I can't see how to turn this in to a parallel operation.

Is it possible in SSIS to split the Excel file (by rows) and import it in parallel?

I would Export the Excel file to a Text file (e.g. tab-delimited) and load it into SSIS in that format.  It will be much quicker and less subject to odd errors which sometimes come up when reading Excel files.

I would expect any attempt at parallel Excel imports to be slower than a single import - Excel is not a server.


High Level SAN Migration Strategy. Is this a good idea? In the company I work for we are planning decommissioning an old SAN serving our production servers Which are 2 Nodes running 2 SQL Server instances using Clustering (one is the backup for one instance and the primary for the other and visceversa). Instead of that old SAN device (I believe it is a VNX 500, but the technical specifications really doesn’t matter from what I’m asking here…) we will have a new one that more or less should replace the existing one with better capabilities and more storage. In a nutshell, what we are going to accomplish is this.

Me as a DBA, have participated in tons of SQL Server Migrations, but in this case we are not really migrating the database servers, we are only pulling the SAN rug underneath and replacing it with a flashier, fancier one…
We have discussed an approach with our enterprise architect and it goes more or less like this (veeeery high level)

Set up file synchronization using some tool (TBD) that will work at the file-block level to synchronize the contents of the data and log files. This will be running as a service while the existing database server in production is working (using the old SAN).
Have this synchronization running for a day or two and observe no errors occur.
On the night were we do the Cutover we

Shutdown the SQL Server Services (on both instance) using MS Cluster Management Console
Shutdown the Clusters themselves using MS Cluster Management Console
Present the new SAN device to the SQL Servers
Remove the old partitions (un assign letters)
Assign those letters to the LUNs on the new SAN
Bring the Cluster online
Start SQL Server Service

Test, Test, Test: consistency Checks, Verify SQL Server Error Logs and as look for other issues

In other words, we are thinking about putting SQL Server to sleep for a while, make the necessary infrastructure changes that should be transparent to SQL Server so when we bring the service online things will look to SQL just the same they were before, but only this time we are reading off the new SAN.

The reason for not doing a straight backup/restore of the databases is because we are trying to minimize downtime and we believe if we find the right tool to synchronize at the file level, we could end up just doing a final sync to catch up with the latest changes (once we shut down SQL Server Service) as opposed to major file copy operations (our databases are rather big and the downtime must be minimal).

I am not asking for a full review of the strategy or discussing this at a super low level, all I’m asking the community members who would like to chime in is:

Have you done anything remotely similar to this? did it work?
Any gotchas?
Anything missing here (at a high level please, I know I’m missing TONS of stuff, but I’m talking about ciritical high level steps)
DO you know of any tool that may provide the functionality we are looking for in order to synchronize files this way (do an initial sync and then only sync bits/sectors/clusters inside the file that have changed)

Thanks and sorry for the long post, but I felt that if I didn’t include at least a bit of detail the questions would be rather vague.

If you are using vnx you should be able to use replication manager or mirrorview both are emc products.
If your new SAN is also from emc you should have emc support and use them to get recommendations for this type of operation.


Convert date in date list condition to list of date ranges I want to look for all the records that occur on specific dates.

SELECT *
FROM table1
WHERE date(column) in ($date1, $date2, ...);

However, as many of you, know this kind of comparison doesn't get along with indexes. So, I was wondering if there is a simple way to convert this query to something of the style of the following query without too much effort (i.e., : not using an external tool).

SELECT *
FROM table1
WHERE (column >= $date1 AND column < $date1 + interval 1 day)
   OR (column >= $date2 AND column < $date2 + interval 1 day)
   ...

So the optimizer can still use the indexes. (I'm using MySQL, but ANSI SQL would be great)

SUGGESTION #1

SELECT A.* FROM table1 A INNER JOIN
(
    SELECT '2015-03-01' dtcolumn
    UNION SELECT '2015-03-15'
    UNION SELECT '2015-04-01'
    UNION SELECT '2015-04-15'
    UNION SELECT '2015-05-01'
) B ON
A.dtcolumn >= B.dtcolumn
AND A.dtcolumn < B.dtcolumn + INTERVAL 1 DAY;

SUGGESTION #2

SELECT * FROM table1 WHERE
(column >= '2015-03-01' AND
column < '2015-03-01' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-03-15' AND
column < '2015-03-15' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-04-01' AND
column < '2015-04-01' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-04-15' AND
column < '2015-04-15' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-05-01' AND
column < '2015-05-01' + INTERVAL 1 DAY);


How to drop variable in SQL Server I have declare a variable in SQL Server: Declare @variable

How can I drop or enable the variable when I don't need it?

The scope of a variable lasts for the duration of when it is declared to the end of the batch or stored procedure.  So in other words if you have something like this:

declare @someVar varchar(100)
select @someVar = 'this is a test'    -- this is legal

go

select @someVar = 'this will not work'    -- the variable @someVar is out of scope

See the MSDN Reference on T-SQL Variables:

  The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.

In other words, you don't explicitly drop variables like you do for other database objects.  (the same goes for table variables as well)


Which system catalog view has the create_date of a database which name was modified? In the Stack Exchange Data Explorer each Sunday all databases are dropped and recreated one by one with this stored procedure.

For each database the script starts with (paraphrased) for example Dba.StackExchange:

Create Database [dba_Temp]
          ON  PRIMARY (NAME = 'dba', FILENAME = 'dba.mdf')
          LOG ON (NAME = 'dba_log', FILENAME = 'dba_log.ldf')

Then it does for the tables an select into from a table that is on a linked server, for example for the Users table (paraphrased again):

Select * Into [dba_Temp].[dbo].[Users] From [CO_SQL03].[StackExchange.Dba].[dbo].[vExportUsers];

At the end the current StackExchange.Dba database is dropped and the dba_temp is renamed to StackExchange.Dba

Alter Database [dba_temp] Modify Name = [StackExchange.Dba]

Rinse repeat for the other 349 databases.

I'm interested in how long it took for each database to get refreshed, for example in a query like this so we can provide better support for questions like I'm getting an “Invalid object name 'StackExchange.Cstheory.Meta_Temp.dbo.posts'.” when executing a cross-site query in SEDE

The output of sys.databases for a restored database baffles me a bit. I've been using this query.

The create_date for the StackOverflow database is 2020-05-24 10:36:19 but that isn't the creation date, that is the last action that took place. That is also confirmed by looking at the create_date of Tables. The first table (Users) is created on 2020-05-24 05:49:59, the last table on 2020-05-24 10:31:07. The last statement in the refresh script is the creation of the database principal. Its create date is 2020-05-24 10:36:19. That matches the create_date of the database.

I'll accept that I can always use the workaround to investigate to create_data of the objects in the database to find the closest possible date to the create_data of the database.

Is there any other system catalog view I could query to get the actual create_date of the database in the context of the Stack Exchange Data Explorer?  That last part might be a frustrating limitation as the SEDE instance seems pretty hardened. Probably because I knocked a bit too hard on the door now and then. For example sys.database_files does return rows but the SVC_SEDE user doesn't have permission on sys.master_files. None of those views have a create_date (except that the date and time is encoded in the database filename, uuggghhh) so even if that user had access it wouldn't have solved my problem.

Which system catalog view has the create_date of a database which name was modified? Or an alternative reliable method if such view doesn't exist.

Which system catalog view has the create_date of a database which name was modified? Or an alternative reliable method if such view doesn't exist.

You're correct in your assessment of create_date in sys.databases, which is confirmed by the documentation:

  Date the database was created or renamed.

So the create_date is the ideal value to use as the end time of the refresh process, being that the rename is the final step.

That being said, given the restrictions in place here I don't think you'll find any more accurate of a start time than the timestamp on the first table.

So the unsatisfying answer is you're as close as you're going to get, I think.


Oracle 11.2.0.4.0 32 bits full client installation I need to install an Oracle Client on a Windows 2008 Server R2.
The client must be 32 bits since the application that it is going to use it only works under 32 bits.

I´m getting crazy knowing what specific packages I need to download and install. I can´t find what components I have to install to simply install the client and the ODBC drivers. I cannot believe that Oracle is so bad at this. Why cannot Oracle have a simple installer where you install everithing you need?, instead of having to donwload multiple packages, merging them and running console installers.

I´ve donwloaded and installed:

ODAC112040Xcopy_32bit
instantclient-basic-nt-11.2.0.4.0
instantclient-odbc-nt-11.2.0.4.0

And I still cannot get the freaking ODBC driver to appear on the list of availables ODBC drivers on ODBC connections.

Can someone please tell me or point me out what do I have to install to accomplish this?.

Thanks.

This is not an Oracle, but a Microsoft Windows issue. When you install a 32-bit ODBC driver on a 64-bit operating system, and try to configure ODBC data sources from the Start menu or Control panel, it opens the utility (C:\Windows\System32\odbcad32.exe) for managing 64 bit drivers and data sources with them.

If you want to manage ODBC with a 32-bit driver, run this instead:

C:\Windows\SysWOW64\odbcad32.exe

Newer versions of Windows offer 2 seperate menu entries for managing 32-bit and 64-bit ODBC.

  Why cannot Oracle have a simple installer where you install everithing
  you need?, instead of having to donwload multiple packages, merging
  them and running console installers.

Here is the full, graphical installer (win32_11gR2_client.zip) for 11.2:

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html

The 11.2.0.4 version can be downloaded from https://support.oracle.com/ with a valid support contract, which is required for downloading updates.


Splitting up tables with similar structures since they contain unrelated data? (huge numbers) Is my reasoning correct in splitting up tables with similar structures since they contain unrelated data?

I'll explain the situation with an example :

Suppose there is a server which hosts an online game's database. The game has multiple gameworlds which operate in parallel. The schema for each gameworld of the game is same, let's say there are 15 relational tables in the database design of the game. Also assume that the server decides not to delete the data ofold servers and instead, archives it.

Approach 1 :

Now since the gameworlds are hosted on one server, It might seem logical to put all the game data into one database containing 15 tables. The table structure of an example table might look like this :

CREATE TABLE `table` (
    playerid ...,
    score ....,
    gameworldid ...,
    PRIMARY KEY(gameworldid,playerid)
) ENGINE = InnoDB

So, everytime I need to show top 10 players with most score, I will have to do something like SELECT * FROM table WHERE gameworldid = {id} ORDER By score DESC LIMIT 10

Approach 2 :

Since the data for one gameworld doesn't relate to data of the other gameworld in any way (except for the structure), we may make different databases for different gameworlds, or different set of tables for each gameworld. The table structure will look something like this :

CREATE TABLE `gameworldid_table` (
    playerid ...,
    score ....,
    PRIMARY KEY(playerid)
) ENGINE = InnoDB

In this case, top 10 players with most scorequery will be SELECT * FROM table ORDER BY score DESC LIMIT 10

The question is, which approach is better in this situation? The storage engine being used is InnoDB, and in all , 6 billion and up rows can be expected to be there in the biggest merged table, and 500 and up gameworlds. (Up since the old data isn't deleted, so when a gameworld restarts, a new gameworld is created.

In my opinion, the advantages of Approach 2 over Approach 1 are :

Less space consumed by primary key and indices, making them more efficient
Faster sorting, joining and other stuff as compared to merged table in approach one which contains lots of extra data that might come in way of these operations.
Faster inserts as compared to ones in larger tables
Data won't keep just piling up forever in the same table

And the advantages of Approach 1 over Approach 2 ?

Better manageability : 15 tables sound better as compared to 500 * 15 tables, if you ever have to do some manual fixing (rare).
Sounds less insane
Too many tables may just make database slower ( don't know for a fact, can't find it on the internet)

I guess my opinions on advantages and disadvantages may be biased, but I really want to know what all problems can come my way if I choose one Approach over the other. Also, is there any other possible solution for this solution?

Your issues are probably less about performance, assuming reasonable equipment and a good indexing structure, and more about other impacts that you face.

Approach 1 means some additional programming complexity to deal with filtering rows in each table to restrict access to a single gameworld. It also means that if you need to restore a gameworld, you would have to (a) restore all gameworlds (since they are in the same database) or (b) restore the one database to a recovery database and then script the appropriate gameworld data from Recovery to your Gameworlds database.

Approach 2 means that you need to be able to manage schema changes across many databases.  This can be done by scripting as needed.  It is also easier to choose to have different schemas for some gameworlds, but that adds management overhead.  However, if a a single gameworld crashes, you can restore the latest good backup(s) and get it going again.

Some years ago some Microsoft folks wrote up their take on Multi-Tenanted databases.  See: http://msdn.microsoft.com/en-us/library/aa479086.aspx

Having more tables should not, in itself, cause performance problems within the size range you describe.

Choosing your approach should take into account leveraging your strengths so that you have a system you (and your project members) can readily manage.


SQL Server SSL connection - What am I missing? I am trying to set up my SQL Server 2008 R2 instance so that it only accepts SSL connections.  My understanding is that once this is set up, any client machine should need to have a certificate installed in order to connect.

I have followed the instructions on http://msdn.microsoft.com/en-us/library/ms191192(v=sql.105).aspx and done a lot of reading on a lot of other sites as well.  I have created a certificate, selected it in the SQL Server Configuration Manager (Network Configuration/Protocols/Properties), and I have set 'Force Encryption' to true.  I restarted the service running my instance.

After doing all this, when I try to connect to my instance remotely, I succeed.  Normally I like succeeding but in this case I haven't installed a certificate my development machine would recognize (this is where my instance is) and when I look at the connection properties, it says the connection is not encrypted.  If I choose to try an encrypted connection from the client (selecting this in SQL Server Management Studio's Options in the connection box), I get the certificate problem I expect.

So my questions are:  am I right that if things are set up correctly the connection from the client should fail (because it lacks an appropriate certificate) without my having to configure anything on the client?  And is there something obvious that I might be missing?  Some other place where I have to change a property, maybe?  I am hoping that someone who has done this before will have some ideas.

By the way, in creating my cert I followed SQL Dude's instructions at http://thesqldude.com/tag/makecert/.  The cert's name is my dev machine's full qualified domain name.

Note that when you force encryption on the server side, the connection will be encrypted by using ssl. You can use any certficate for this. Your connection will be encrypted, However the client will not check if the identity of the SQL Server is in fact valid. Without this check you could be open for a man in the middle attack.

If you choose force encryption on the client side, a full validity check will be performed. That way not only is the connection encrypted, also the client knows that the server it is connection to, is in fact the server mentioned in the certificate because it can trust that the certificate has not been tampered with.

For this to work, the certificate used on the SQL Server must be issued by a certificate authority that the client trust. This can be any well known certificate authority or for example when you have a windows domain and a CA in that domain, you can have that CA issue one. You then have to import the root certificate of that CA into all clients and that will work too.

to check if your connection is encrypted use the following:

SELECT session_id,encrypt_option
FROM sys.dm_exec_connections