Declaring a scalar variable that its a table valued parameter?

The problem is here:

WHERE   (tsl.TransmittalSiteLeadFacility = @facilities

You are trying to compare a column on the left to a table on the right. This is like saying:

FROM sys.objects
WHERE object_id = sys.columns

...which obviously makes no sense.

That where clause doesn't belong there at all. You've already ensured that the facilities listed in the incoming table-valued parameter are matched to TransmittalSheet by using it in the JOIN clause. There is no need to filter on that match again.

My guess after several rounds of teeth-pulling is you want this:

CREATE PROCEDURE dbo.GetImpDocsThree
  @facilities dbo.Facilities ReadOnly,
  @facilitydivisionID tinyint = 6
AS
BEGIN
  SET NOCOUNT ON;

  ;WITH f AS
  (
    SELECT FacilityID FROM @faclities
    UNION ALL SELECT @facilitydivisionID
  )
  SELECT  di.DocumentNumber, di.DocumentVersion, di.DocumentTitle,
    ts.TransmittalNumber, ts.TransmittalVersion,
    FacilityImpDate = COALESCE(ts.TransmittalSiteLeadImpDate, ts.TransmittalImpDate)
   FROM dbo.DocumentInfo AS di
   INNER JOIN dbo.TransmittalSheet AS ts
     ON di.DocumentTransmittalImp = ts.TransmittalID
   INNER JOIN f
     ON tsl.TransmittalSiteLeadFacility = f.FacilityID;
END
GO

Or just insert 6 into the TVP up front too, and ditch the additional single facility variable altogether.


set session - custom variable to store user id

Before version 9.2, you needed to add your custom class variable to custom_variable_classes parameter in postgresql.conf, like:

custom_variable_classes = 'myapp'

In 9.2, this requirement has been removed:

  Remove the custom_variable_classes parameter (Tom Lane)

  The checking provided by this setting was dubious. Now any setting can
  be prefixed by any class name.

So, since 9.2 you can just set your custom class variable as you are doing currently, no need to worry about changing postgresql.conf.


Community wiki answer:
Your condition only eliminates about 50% of the rows in the table. In that case the Seq Scan will be faster than the Index Scan.
See for example this Stack Overflow answer, where user a_horse_with_no_name says:

If the SELECT returns more than approximately 5-10% of all rows in the table, a sequential scan is much faster than an index scan.
This is because an index scan requires several IO operations for each row (look up the row in the index, then retrieve the row from the heap). Whereas a sequential scan only requires a single IO  for each row - or even less because a block (page) on the disk contains more than one row, so more than one row can be fetched with a single IO operation.

Also see Index-Only Scans in the documentation, which includes:

In principle, index-only scans can be used with expression indexes. [...] However, PostgreSQL's planner is currently not very smart about such cases. It considers a query to be potentially executable by index-only scan only when all columns needed by the query are available from the index.

A separate idea would be to have a keywords table alongside your main one, with one ID per word you are interested in, and an ID related to a specific text, and just populate it as needed. The choice will obviously depend on many other constraints that only you can know.


Basically, how to sort and compare strings.

Examples:

Comparing:

Accent: à vs ä vs a
Case: A vs a

Sorting:

Swedish: z -> å -> ä -> ö
Danish: z -> æ -> ø -> å

Special character equivalence

German ß = ss

Wikipedia

Some SQL (SQL Server 2008+)

DECLARE @foo TABLE (bar varchar(2))
INSERT @foo VALUES ('z'), ('æ'), ('ø'), ('å'), ('ss'), ('ß'), ('a'), ('ä')

SELECT * FROM @foo ORDER BY bar COLLATE Finnish_Swedish_100_CI_AS
SELECT * FROM @foo ORDER BY bar COLLATE Norwegian_100_CI_AS
SELECT * FROM @foo ORDER BY bar COLLATE Latin1_General_100_CS_AS --german (western europe) and default
SELECT * FROM @foo ORDER BY bar COLLATE German_PhoneBook_100_CS_AS


Differential backup same size as of Full Backup

This was originally left in a comment by Dan Guzman:

  The differential backup includes all extents modified since the last full backup. If you reorganize or rebuild after the full backup, the diff backup will include all those modified extents. Note when you reorg with LOB_COMPACTION ON, the differential backup size will increase significantly if LOB columns are a large portion of the database space.

If you've performed a big maintenance action (rebuild, or LOB compaction) on the tables with images in them, you'll likely see the differential approach or reach the size of the full database backup, as is documented on the MS Docs page Differential Backups (SQL Server):

  A differential backup captures the state of any extents (collections of eight physically contiguous pages) that have changed between when the differential base was created and the when differential backup is created. This means that the size of a given differential backup depends on the amount of data that has changed since the base.

Another possibility, mentioned by Midhun C N, is that the "differential base" (the last full backup) is very old.

As far as actions you can take:

verify that full backups are completing successfully, so that the differential base is getting updated
consider avoiding large rebuilds or reorgs on the tables with image data in them unless there is a compelling reason to do so
if maintenance actions are necessary, try to space them out across multiple full backup iterations, to limit the size increase of differentials
if splitting them up is not possible, make sure to plan for the increased size of the differentials


Adam Machanic wrote the everuseful sp_whoisactive that can handle the task. A description of the procedure can be found at SP_WhoIsActive: Get detailed information about the sessions running on your SQL Server system


Minimum permissions required to run sp_Blitz

Yes, it requires sa permissions. But since it's a stored procedure, you can get permissions via a certificate

See the section How to Grant Permissions to Non-DBAs

USE master;
GO
CREATE CERTIFICATE sp_BlitzFirst_cert
ENCRYPTION BY PASSWORD = '5OClockSomewhere'
WITH SUBJECT = 'Certificate for sp_BlitzFirst',
START_DATE = '20130711', EXPIRY_DATE = '21000101';
GO
CREATE LOGIN sp_BlitzFirst_login FROM CERTIFICATE sp_BlitzFirst_cert;
GO
CREATE USER sp_BlitzFirst_login FROM CERTIFICATE sp_BlitzFirst_cert;
GO
GRANT EXECUTE ON dbo.sp_BlitzFirst TO sp_BlitzFirst_login;
GO
GRANT CONTROL SERVER TO sp_BlitzFirst_login;
GO
ADD SIGNATURE TO sp_BlitzFirst BY CERTIFICATE sp_BlitzFirst_cert
WITH PASSWORD = '5OClockSomewhere';
GO
GRANT EXECUTE ON dbo.sp_BlitzFirst TO [public];
GO

Then whenever you update the proc, you’ll need to reassign the permissions by adding the signature again:

ADD SIGNATURE TO sp_BlitzFirst BY CERTIFICATE sp_BlitzFirst_cert
WITH PASSWORD = 'Get lucky';
GO
GRANT EXECUTE ON dbo.sp_BlitzFirst TO [public];
GO

Hope this helps!


Recreate ##MS_SSISServerCleanupJobLogin## user as [##MS_SSISServerCleanupJobUser##

I think the solution will work. Don't forget to grant permissions:

USE [SSISDB]
GO
GRANT EXECUTE ON [internal].[cleanup_server_project_version] TO [##MS_SSISServerCleanupJobUser##]
GO
GRANT EXECUTE ON [internal].[cleanup_server_retention_window] TO [##MS_SSISServerCleanupJobUser##]
GO


Deleting archivelog data would normally be done as part of the backup operation removing older archive data after a new backup is taken.

You should read the following:

Oracle Concepts Guide (pdf), particularly chapter 11
Administrator's Guide, chapter 13 (which has useful information on Archive Logs).

Only after reading all that should you look at the Backup and Recovery guides section on Configuring an Archived Redo Log Deletion Policy. I purposely did not provide a direct link.  When you are ready, you can lookup the book in the Master Book List.


Postgresql - Point in Time recovery using recovery.conf and trigger. How to minimize the time waiting for .history files

That's not the fault of the history files.

Quoting from the documentation link you referenced:

  Normally, recovery will proceed through all available WAL segments, thereby restoring the database to the current point in time (or as close as possible given the available WAL segments). Therefore, a normal recovery will end with a "file not found" message, the exact text of the error message depending upon your choice of restore_command. You may also see an error message at the start of recovery for a file named something like 00000001.history. This is also normal and does not indicate a problem in simple recovery situations; see Section 24.3.4 for discussion

Since it looks like you're using pg_standby, you have a few options.

Adjust -r maxretries and -s sleeptime.

By default -r maxretries is 3, and since it looks like you have the -s sleeptime setting set to 60, it will wait 60 seconds on the first retry, then 120 seconds on the second retry, then 180 seconds on the third and final retry of the copy command before it gives up and brings the replica server up into standalone mode.

So, in addition to replaying all the logs, at the end when the command is trying to copy the .history files, you're waiting a grand total of 360 seconds, or six minutes, plus the time it takes to restore all your WAL segments under smart mode.
Do a fast failover instead of a smart failover.

If you're desperate to fail over quickly, and can stand the loss of any unapplied WAL segments, you can do a fast failover instead of the smart failover you're triggering, as referenced in the pg_standby documentation here.

The merits of smart failover versus fast failover depend on how fast you want to get your replica server going and how much tolerance you have for lost transactions.

Fast failover ignores unapplied WAL segments and as such, the transactions are lost. Smart failover replays all the WAL segments it possibly can before bringing up the replica in standalone mode. This can take a long time depending on how many WAL segments you need to replay.

Finally, since you're on 9.1, you could use streaming replication along with a restore_command in your recovery.conf to keep the server even more up to date than the standard log shipping can, by streaming individual WAL records as they are produced.

Additionally, if the replica can't keep up with the streaming load (due to network issues or any number of things), it can alternately use a restore_command to grab WAL segments from the archive you already have set up.

You can't use pg_standby for the restore_command though.

In theory, since there is little if no delay restoring WAL records if all is going well, instead of restoring full WAL segments, your replica should come up in standalone mode much faster.

An example of streaming replication plus archiving in action can be found here:

http://evol-monkey.blogspot.com/2014/09/offsite-replication-problems-and-how-to.html

It is also useful to monitor your replication lag to make sure that everything is as up to date as your requirements dictate. Examples of that are here:

http://www.keithf4.com/monitoring_streaming_slave_lag/

Hope that helps. =)


MySQL - What to store in orders or product_order

In the Orders (Or Order_Header) table, you should store the Orders header - i.e. Order_ID, Client_ID (FK to Client table), Order_Date... &c.

You may want to have a trigger to update an Order_Total field for the total charge/cost. This is not strictly necessary, since you can query that from the Order_Detail table (see below).

A total may be useful if you apply discounts to an entire order (10% above sum X, say, or between dates or ad-hoc), rather than to individual products.

Order_product (I think that Order_Detail is a better, more descriptive, name) should be a joining/linking table between Orders and Product with fields Order_ID (FK), Product_ID (FK), quantity, price, tax, discount, charged_price per item and total for that item in that order.

To answer your question, the individual Product price will remain the price applying at Order_Date and not change subsequently (unless you apply a discount). You can't later refer back to the Product table for price since, as you say, it may change and customers don't like surprises (unless they're discounts :-) )

The Product_price (for a given order) is only valid at the time of that order and not subsequently, therefore it becomes "fixed" at that date. This is why, when calculating Order_total, you either refer to the Order_total (in the Orders table) or the Product_price (in the Order_detail table).

The Product_price field is useful at the date of the order or possibly for providing a sum of the value of current stock for example.

You might like to take a look here (search for "e-Commerce" and "shop" - eCommerce is just commerce after all - and "customers"). I've found this site helpful in the past as a starter.

Finally, for your schema naming, choose singular or plural table names (my preference is singular - with the exception of the word "order", an SQL keyword), but choose one and stick to it.

A final word of advice, if you haven't gone far down the road into this project, I would strongly urge you to consider using PostgreSQL instead of MySQL - it is a vastly superior database product and you (and your customers) will be thankful in the long run.

Best of luck with your project.


Simultaneous SQL full backups of newly created databases

You can use a CREATE_DATABASE Event Notification to monitor database create events and this solution even works on Sql Express (where the Sql Agent doesn't exist). This link shows you how to set up SQL Mail for SQL Server Express.
I modified this example from Jonathan Kehayias, which was tracking autogrowth, to instead audit the create database event.
-- Using msdb prevents the need for certificate signing the
-- activation procedure to execute sp_send_dbmail across
-- databases
USE [msdb];
GO

-- Drop the notification if it exists
IF EXISTS (
        SELECT *
        FROM sys.server_event_notifications
        WHERE NAME = N'CaptureDatabaseCreateEvent'
        )
BEGIN
    DROP EVENT NOTIFICATION CaptureDatabaseCreateEvent ON SERVER;
END

-- Drop the route if it exists
IF EXISTS (
        SELECT *
        FROM sys.routes
        WHERE NAME = N'DatabaseCreateEventRoute'
        )
BEGIN
    DROP ROUTE DatabaseCreateEventRoute;
END

-- Drop the service if it exists
IF EXISTS (
        SELECT *
        FROM sys.services
        WHERE NAME = N'DatabaseCreateEventService'
        )
BEGIN
    DROP SERVICE DatabaseCreateEventService;
END

-- Drop the queue if it exists
IF EXISTS (
        SELECT *
        FROM sys.service_queues
        WHERE NAME = N'DatabaseCreateEventQueue'
        )
BEGIN
    DROP QUEUE DatabaseCreateEventQueue;
END

--  Create a service broker queue to hold the events
CREATE QUEUE [DatabaseCreateEventQueue]
    WITH STATUS = ON;
GO

--  Create a service broker service receive the events
CREATE SERVICE [DatabaseCreateEventService] ON QUEUE [DatabaseCreateEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

-- Create a service broker route to the service
CREATE ROUTE [DatabaseCreateEventRoute]
    WITH SERVICE_NAME = 'DatabaseCreateEventService'
        ,ADDRESS = 'LOCAL';
GO

-- Create the event notification to capture the events
CREATE EVENT NOTIFICATION [CaptureDatabaseCreateEvent] ON SERVER
    WITH FAN_IN
FOR CREATE_DATABASE TO SERVICE 'DatabaseCreateEventService'
    ,'current database';
GO
--  Alter the queue to use the activation procedure
ALTER QUEUE [DatabaseCreateEventQueue]
   WITH STATUS=ON,
      ACTIVATION
         (STATUS=ON,
          PROCEDURE_NAME = [ProcessDatabaseCreateEvents],
          MAX_QUEUE_READERS = 1,
          EXECUTE AS OWNER);
GO

I then tweaked his example stored procedure that consumes the event.
Make sure you change this line in the stored procedure to be your email address
@recipients = 'YourEmailAddress'
DROP PROCEDURE [dbo].[ProcessDatabaseCreateEvents];
GO

CREATE PROCEDURE [dbo].[ProcessDatabaseCreateEvents]
    WITH EXECUTE AS OWNER
AS
DECLARE @message_body XML;
DECLARE @message_sequence_number INT;
DECLARE @dialog UNIQUEIDENTIFIER;
DECLARE @email_message NVARCHAR(MAX);

WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION;

    -- Receive the next available message FROM the queue
    WAITFOR (
            RECEIVE TOP (1) -- just handle one message at a time
            @message_body = CAST(message_body AS XML) FROM dbo.DatabaseCreateEventQueue
            )
        ,TIMEOUT 1000;-- if queue empty for 1 sec, give UPDATE AND GO away

    -- If we didn't get anything, bail out
    IF (@@ROWCOUNT = 0)
    BEGIN
        ROLLBACK TRANSACTION;
        BREAK;
    END

    DECLARE @EventType VARCHAR(128);
    DECLARE @ServerName VARCHAR(128);
    DECLARE @PostTime VARCHAR(128);
    DECLARE @DatabaseName VARCHAR(128);
    DECLARE @TextData VARCHAR(max);
    DECLARE @GrowthPages INT;

    SELECT @EventType = @message_body.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)')
        ,@ServerName = @message_body.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(128)')
        ,@PostTime = CAST(@message_body.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') AS VARCHAR)
        ,@DatabaseName = @message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)');

    -- Generate formatted email message
    SELECT @email_message = 'The following DatabaseCreate event                                      occurred:' + CHAR(10) + CAST('ServerName: ' AS CHAR(25)) + @ServerName + CHAR(10) + CAST('PostTime: ' AS CHAR(25)) + @PostTime + CHAR(10) + CAST('DatabaseName: ' AS CHAR(25)) + @DatabaseName
    -- Send email using Database Mail
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SqlServerEmailProfile'
        ,
        -- your defined email profile
        @recipients = 'YourEmailAddress'
        ,-- your email
        @subject = 'DatabaseCreate Event Notification'
        ,@body = @email_message;

    --  Commit the transaction.  At any point before this, we could roll
    --  back. The received message would be back on the queue AND the
    --  response wouldn't be sent.
    COMMIT TRANSACTION;
END

Then, I ran a simple create database
USE [master]
GO
DROP DATABASE IF EXISTS [TestCreateDb]
GO
CREATE DATABASE [TestCreateDb]

After a few seconds or so, I received the email with the event data in it. You can modify the event consuming stored procedure to capture and process whatever you need, such as initiating the backup immediately.


Is there any performance reason to append an unused AUTO_INCREMENT column in MySQL 5.6?

Yes and no.

(The following applies to all versions, but only to ENGINE=InnoDB.)

An InnoDB table always has a PRIMARY KEY:

You could specify one; else
A UNIQUE key with non-null column(s); else
A hidden 6-byte number.  (This number does not otherwise exist.)

If you remove your PK, then option 2 or 3 will kick in.  If option 3 kicks in, each row will be 2 bytes bigger -- assuming your id is a 4-byte INT now.  This is a tiny performance hit.

The data in the table is ordered according to the PRIMARY KEY:

With AUTO_INCREMENT or the hidden key, new inserts are being "appended" to the table;
With some other UNIQUE or PRIMARY key, the rows are inserted 'where they belong'.  This is a tiny bit less efficient.

When you need to do maintenance on the table, you often need a unique way to identify each row.  For this reason, alone, I advocate having a visible, explicit PRIMARY KEY.

Which is a "better" PRIMARY KEY:

An AUTO_INCREMENT?
A "natural" key made up of one or more columns that are naturally unique?

Answer:  It depends.  Since that is not the topic of your question, I will defer the elaboration to another Q&A.  Also, since the PK is an index, there could be performance considerations for indexing.


mysqldump reimport isn't working correctly

I might be missing something, but instead of using mysqldump, use mysql which is the command line client program:

mysql -uuser -ppassword -hhost blink10 < /data/tt.sql

This assumes there are actual SQL staetments in your tt.sql file, such as CREATE TABLE and INSERT, etc


AlwaysOn commit on Primary if Secondary goes down

Books Online has a really good page on this, including:

  If primary's session-timeout period is exceeded by a secondary
  replica, the primary replica temporarily shifts into
  asynchronous-commit mode for that secondary replica. When the
  secondary replica reconnects with the primary replica, they resume
  synchronous-commit mode.

Put another way, the primary holds transactions for a short period of time, and it'll feel like there's a blocking lock that's stopping your transaction from completing. After the primary gives up on the secondary, the transaction will go through, but only be hardened on the primary - not the unavailable secondary.

The default session timeout period is 10 seconds.

If you want the primary to only accept transactions when a certain number of secondaries are available, you'll need to upgrade to SQL Server 2017 and use the new REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT setting.


Select permission inside stored procedure?

Ok, on the basis of the above comment and as per my suspicion - it seems as though you are trying to execute dynamic SQL within your stored procedure.

What you need to remember is that when you do this it does not get executed within the context of the stored procedure - it gets executed within a new session. Because of this, the fact that the statement is being called within a stored procedure is a moot point, and you will need to grant explicit permission on the objects that your dynamic SQL is using.

If you don't want to do this I would refactor your stored procedure to not use dynamic SQL.

The below link from Microsoft should help you with your problem:

PRB: Security Context of Dynamic SQL Statements Inside a Stored Procedure (Wayback Machine archive)

  This behavior occurs because a dynamic execution query (sp_executesql or EXECUTE) executes in a separate context from the main stored procedure; it executes in the security context of the user that executes the stored procedure and not in the security context of the owner of the stored procedure.

This is also discussed in the (more current) Microsoft Docs article:

Writing Secure Dynamic SQL in SQL Server

  Executing dynamically created SQL statements in your procedural code breaks the ownership chain, causing SQL Server to check the permissions of the caller against the objects being accessed by the dynamic SQL.


Modeling Conversion Events in a Data Warehouse

This is commonly represented with an accumulating snapshot fact table or timestamped accumulating snapshot fact table. Accumulating snapshot tables model events in progress for business processes that involve a predefined series of steps. The timestamped.

You'll have a date key in the table that represents the date the opportunity hit each stage. If you need to reproduce what pipeline looked like on a given day or track the fact that an opportunity went into and out of a stage multiple times, you might need to consider the timestamped accumulating snapshot.


You can extract much more data from the event instance in the Service Broker message to store in the table. Specifically, TargetLoginName should bring back the affected user for you, something like this should work:

SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname') AS [ActioningLogin],
  @message_body.value('(/EVENT_INSTANCE/TargetLoginName)[1]', 'sysname') AS [AffectedLogin]

More info here.


Login system: how is it possible to model multiple user types identified by only one account?

I don't think you should make tables to represent Roles (User, Manager, Seller, etc) I think you could implement something more scalable like this:

Completely forget about the "user-centric" model and think to a lower level, Individuals. Individuals can have any number of Accounts and Accounts can have any number of Roles. Individuals can be represented on any number of abstractions around the database or even across databases.

What do you think?


How to delete old entries based on MAX(id) after an INSERT query?

CREATE TABLE temp LIKE real;
load up `temp`
copy 'last' from real into temp  (why??)
RENAME TABLE real TO old,
             temp TO real;
DROP TABLE old;

Depending on the size of the table, this could be a lot faster than using DELETE.  Also, the table is never not there.

No 'transaction' is needed.


Oracle: Recursively self referential join with nth level record

You can use the SYS_CONNECT_BY_PATH hierarchical function to generate a list of parent IDs from the tree root to the current record, and the level pseudo column to count the number of members in the list (+1).  In the next stage, the regexp_substr function extracts the Nth path element from the ath for display as the Nth parent_Id:

var nth number
exec :nth := 2;

with t1(id, lev, parent_id) as (
select 1, 1, null from dual union all
select 2, 1, null from dual union all
select 3, 2, 1    from dual union all
select 4, 2, 1    from dual union all
select 5, 2, 2    from dual union all
select 6, 3, 5    from dual union all
select 7, 3, 3    from dual union all
select 8, 4, 7    from dual union all
select 9, 4, 6    from dual
), t2 as (
select id, lev, parent_id, level
     , ltrim(SYS_CONNECT_BY_PATH(parent_id,','),',') path
  from t1
connect by prior id = parent_id
start with parent_id is null
)
select id, lev, parent_id, path
     , :nth n
     , to_number(regexp_substr(path,'[^,]+',1,:nth)) nth
  from t2
  order by id;

        ID        LEV  PARENT_ID PATH                N        NTH
---------- ---------- ---------- ---------- ---------- ----------
         1          1                                2
         2          1                                2
         3          2          1 1                   2
         4          2          1 1                   2
         5          2          2 2                   2
         6          3          5 2,5                 2          5
         7          3          3 1,3                 2          3
         8          4          7 1,3,7               2          3
         9          4          6 2,5,6               2          5


Database design for products with multiple units

Removed the pricing from this model, just to keep this example simple and precise! It is important to use Base Unit, that means you must maintain each SKU (Barcode) with base unit. Ofcourse, it is possible to do the purchases, store and sell with Base Unit or any other applicable unit based on Unit Conversion configuration.

Here is the design I would like to propose, please review and let me know your thoughts!

Table: Products
ProductId | Barcode | Name   | BaseUnitId
1         | XXXX    | Pencil | 1

Table: Units
UnitId | Name
1      | Each / Pieces
2      | Box

Table: UnitConversion
ProductId | BaseUnitId | Multiplier | ToUnitId |
1         | 1          | 24         | 2        | // 24 pencils in a box

//Better to store the Inventories in Base Unit. You can perform conversion on the fly only if the requirement arise.
Table: Inventories
Id | ProductId | UnitId | Quantity
1  | 1         | 1          | 48                //In pieces

Table Invoices
Id | ProductId | UnitId | Quantity
1  | 1         | 2      | 1.5                   //Sold/Purchased 1.5 boxes that means 36=(1.5*24) pieces

//You can perform transaction against the products in any legitimate Unit (validate against UnitConversion table)


Can I use attribute name to identify XML element?

The .value method must always return a single value so you normally wrap it in brackets and move the ordinal ( eg [1] ) to the end.  Here's an example:

DECLARE @xml AS XML = '<DATA>
  <ROW RowNumber="1">
    <COLUMN Name="ProdID" Value="1234" />
    <COLUMN Name="AppID" Value="20144" />
    <COLUMN Name="ConsentDesc" Value="My Description" />
    <COLUMN Name="ConsentVer" Value="" />
    <COLUMN Name="ConsentAcceptance" Value="6789" />
    <COLUMN Name="ConsentType" Value="TermsAndConditions" />
    <COLUMN Name="CreatedDate" Value="8/22/2014 3:04:29 PM" />
  </ROW>
</DATA>'

SELECT  m.c.value('@RowNumber', 'varchar(50)') AS RowNumber ,
        m.c.value('(COLUMN[@Name="ProdID"]/@Value)[1]', 'varchar(50)') AS [ProdID] ,
        m.c.value('(COLUMN[@Name="AppID"]/@Value)[1]', 'varchar(50)') AS [AppID] ,
        m.c.value('(COLUMN[@Name="ConsentDesc"]/@Value)[1]', 'varchar(50)') AS [ConsentDesc.] ,
        m.c.value('(COLUMN[@Name="ConsentVer"]/@Value)[1]', 'varchar(50)') AS [ConsentVer.] ,
        m.c.value('(COLUMN[@Name="ConsentAcceptance"]/@Value)[1]', 'varchar(50)') AS [ConsentAcceptance] ,
        m.c.value('(COLUMN[@Name="ConsentType"]/@Value)[1]', 'varchar(50)') AS [ConsentType] ,
        m.c.value('(COLUMN[@Name="CreatedDate"]/@Value)[1]', 'varchar(50)') AS [CreatedDate]
FROM    @xml.nodes('DATA/ROW') AS m ( c )


How to set a parent from one-to-many relationship?

You could use a CASE an correlated subqueries.

In the subquery select the minimum id for the row's unified_id.

If the row's id is equal to this minimum, parent is null, making the article a parent. If it isn't equal to the minimum it's a child of the article with the minimum id. Set the minimum id as parent then.

INSERT INTO articles
            (id,
             parent_id)
            SELECT amo.id id,
                   CASE
                     WHEN amo.id = (SELECT min(ami.id)
                                           FROM article_map ami
                                           WHERE ami.unified_id = amo.unified_id)
                       THEN null
                     ELSE
                       (SELECT min(ami.id)
                               FROM article_map ami
                               WHERE ami.unified_id = amo.unified_id)
                   END parent
                   FROM article_map amo;

Or you can use a union. First select all parents using the minimum and a GROUP BY unified_id. Unify that with an INNER JOIN of article_map an the parents.

INSERT INTO articles
       SELECT min(am.id) id,
              null
              FROM article_map am
              GROUP BY am.unified_id
       UNION ALL
       SELECT amo.id id,
              p.id parent
              FROM article_map amo
                   INNER JOIN (SELECT min(ami.id) id,
                                      ami.unified_id
                                      FROM article_map ami
                                      GROUP BY ami.unified_id) p
                              ON p.unified_id = amo.unified_id
                                 AND p.id <> amo.id;

(Minimum and maximum (and possibly others) are interchangeable here of course.)


How trigger update in replica database on Postgres?

The problem with updates in your scenario is that my_table on the standby server does not contain the row being updated, so that the UPDATE does nothing and no row level trigger is fired. Since nothing was inserted into my_table, an UPDATE cannot be replicated.
If you want to replicate from a non-partitioned to a partitioned table with logical replication, use PostgreSQL v13 or better, where that is supported. You will bot be able to automatically generate partitions that way, but it shouldn't be a problem to create the partitions before you start replicating.


WRT the procedure - take a second look at the fields and the table you're trying to populate in the INSERT statement; there's a problem there.

I'm trying to figure out why you have an acct_queue_bridge table. This normally is used to maintain a many-to-many relationship. I'm assuming that a queue belongs to one account, not many? Even if an account can have many queues, you'd only need the bridge if one queue ties to multiple accounts.

However, it's true that each account can have many DVDs on its queue, and each DVD could be on many queues. I would eliminate acct_queue_bridge, add account_id to the queue table, and use that as a bridge between account and dvds

I see a few other similar things that I'd question:

Can a customer have multiple accounts? (this is not unusual - allowing an account for each member of the family. However, only include it if the project seems likely to need it).
If the customer has multiple accounts, would there be a separate rental plan for each? I would think not: I would expect there to be a reference table of the different rental plans in use and/or available, and for each customer to sign up for one instance of those plans; in which case, it's possible to store the rental_plan_id in the customer table directly. You might need to track the customer's rental plan history; then you would probably have a separate table with the customer, the plan, and the date range when the plan was active. The current plan could be included there, or it could live in customer, and be copied into a cust_plan_hist table when it changed.
You currently have a queue_id in the dvds table. I would expect one DVD to be able to be in many queues (however, see my next point). I don't see why you'd link a DVD to a specific queue; perhaps I'm missing something here.
I believe that dvds and your inventory table will be closely related, if not one and the same. I'm assuming a row in dvds represents the idea of a movie/show on DVD, not a specific physical disk. in an actual Netflix business, dvd_inv (or whatever) would be where the actual physical disks are represented. You would need that there because each disk will have some sort of inventory number on it, and you'd track the status of each disk: available, shipped to , missing, whatever. You would probably want to track things like the condition of each disk (so if something leaves pristine and comes back scratched up, you can approach your customer about the change), and of course which specific disk is in the hands of which specific customer. However, as this is not necessarily intended to be a "real world" system, I'm not sure if you need to implement that level of detail - you might be able to get away with just having "bucket" fields to hold those numbers in the dvds table itself (3 available, 5 shipped to customers, 1 missing = 9 total).
Note that some instructors will actual object to you designing a more realistic system than they're asking for, because it becomes more difficult for them to assess and grade. Other instructors will applaud the initiative to go above and beyond the minimum requirements. However, once you start that way, if you run into problems later on, you may have to back out and re-implement parts of your solution to meet future parts of the assignment. I'd recommend sticking as close as possible to the actual requirements in school. In the real world, thinking a step or two ahead of your customer is quite valuable during the design phase, and can save your time and their money (something of a double-edged sword, at times, I suppose :-).


extended events rpc_completed duration does not matching with rpc_starting

My question is "which one tell me how much time the query actually took? Is it Duration in sp_statement_completed or the difference between starting and completed?"

"Duration" in the sp_statement_completed event is how long the query actually took at the server.  It doesn't include the time spent sending results to the client (usually, see here for details).
I'm not 100% sure why the Extended Events timestamp column shows such a large discrepancy between the "starting" and "completed" events, but that's not where you want to focus your attention.


MySQL order by - one column conditional on another column

How about...

ORDER BY (`status` = 'complete'), `priority`

The expression status = 'complete' would resolve to 0 for non-complete, and 1 for complete... exactly the order you want... so you'd get all of the non-completed items sorted by priority, followed by all the completed items sorted by priority.


Why performance degradation with query hint FIRST_ROWS(1000)?

Query optimization isn't an exact science. Oracle cannot always find the best execution plan and it certainly can't do any magic. So here are some points that might or might not help:

With the FIRST_ROWS hint, Oracle favors execution plans with low cost for the first few rows over execution plans with minimal cost for retrieving the whole result set. It can also mean that retrieving the whole set takes longer with the hint than without. So what you are observing might be the intended behavior.
I don't how whether maxResults is a constant. If it's not, then you create a lot of queries that are different from Oracle's view even though you just slightly vary the hint. As a consequence, Oracle will spend a lot of time parsing the query and finding an execution plan. The cached execution plans won't help because they are for a slightly differnt query. So you better use a fixed value for your hint, like FIRST_ROWS(200). That should be good enough.
Oracle execution plan is derived from cost estimations. The cost estimations are based on statistical data about your tables. Please check whether your table statistics are up-to-date. Otherwise Oracle is likely to construct bad execution plans.

Please provide more information about your query so that we can give you more specific help. What WHEREconditions does it contain? Does it contain a clause like WHERE ROWNUM <= 1000? How many numbers of rows do you normally expect?


How do I place leading zeros for numbers less than 10 without affecting those 10+?

The safest way is probably to only add zeroes when the length of the column is 1 character:

UPDATE
   Table
SET
   MyCol = '0' + MyCol
WHERE
   LEN(MyCol) = 1;

This will cover all numbers under 10 and also ignore any that already have a leading 0.

EDIT

To just select the data try:

SELECT
   MyColPadded = CASE WHEN LEN(MyCol) = 1 THEN '0' + MyCol ELSE MyCol END
FROM
   MyTable;


SELECT INTO <table-decided-by-code>

You can't dynamically set the destination table like that.
If the target tables are part of a partition set partitioned on that column then SQL Server would do this for you, but you are using 2012 standard edition so that won't be possible (partitioned tables were an enterprise only feature until 2016sp1).

  Forcing it increases the cost dramatically as it does an RID Lookup.

Have you verified that this produces a slower result? Sometimes the query cost derived for plans by the planner doesn't not match actual performance.

If you are only copying a few columns from the target table, perhaps you could include those in the index with something like CREATE INDEX [ix_tblBase_col1_plus] ON ([col1]) INCLUDE ([col2] ,[col3]). This will increase the size of the index but remove the need for the extra page lookups by RID, but as you say you are taking most of the rows this is unlikely to be practical.

  There is no clustered index on the base table.

Adding any old clustered index would not help, you'll just replace the extra lookups by RID with extra lookups by clustering key.

You could try defining [col1] as your clustering key. When clustering on a non-unique key SQL Server adds a 4-byte "uniquifier" column which is appended to the indexed values. If your column is 4 bytes long or less this will result in a clustering key no larger than RID values. If the column contains wider data then space efficiency may be an issue as that extra data will be added to every row in every non-clustered index. You would need to experiment to see what difference this makes to the size of the table+indexes and any impact on other parts of your application (we could offer educated guesses if you provide a full table definition, and if your "col1" is variable length some information on the pattern of its values, but trying it in a dev copy of your DB would be more definitive).


A savepoint is a point within the current transaction.  A DDL statement like CREATE TABLE, TRUNCATE, ALTER TABLE or DROP TABLE will issue an implicit commit before and after the statement runs that will end the current transaction.  Once the current transaction ends, you can no longer rollback to a savepoint defined within that transaction.

Potentially, you want to look at using a restore point instead.  A restore point is an attribute of a database, not a transaction, so you can generate many transactions and still restore the entire database back to the restore point.  Restoring to a restore point, however, will affect every change that had been made to the database between the time that you defined the restore point and the time that you do restore-- you would lose all changes made not just by your session or your user but all sessions and users in the database.


SnapManager point in time restore problem

This is still a problem, so I’m not completely satisfied.  The IT department will upgrade to SnapManager 7.0 soon (we have 5.2 at this time), so maybe that will resolve it as well.   But I did find a viable way to restore the latest transaction logs.   It’s inconvenient, but it works.

Clone last night’s backup with no recovery
Command to run in SnapManager Powershell:
clone-backup
-Server Prod01
-Database MyDatabase
-RestoreLastBackup 0
-TransLogsToApply 0
-TargetServerInstance Test01
-RecoverDatabase $false
Copy all relevant logs from Production to Test. The only ones needed were those taken after the full backup.
Use SQL below to query file system where you copied the files. This will generate “RESTORE LOG” statements
Copy generated SQL to a new window and execute. This should run a few minutes.
Run RESTORE DATABASE [ MyDatabase__Clone] WITH RECOVERY to make the database available to query

SQL:
CREATE TABLE #DirectoryTree (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit);

INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree ‘B:\LogRestoreTest’,1,1;

SELECT *,
‘RESTORE LOG [ MyDatabase__Clone] FROM DISK = ”B:\LogRestoreTest\’ + subdirectory + ”’ WITH NORECOVERY;’
FROM #DirectoryTree
WHERE isfile = 1 AND RIGHT(subdirectory,4) = ‘.TRB’
ORDER BY id;

This post was helpful in knowing how to use master.sys.xp_dirtree:

http://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html


Indicators for right balance of the number of DB2 instances per AIX LPAR

You won't get any specific answer to this question, because as you observed, it depends.

The answer depends on your company's tolerance for having one DB2 instance adversely impact the performance of another's.  You may be able to control or limit this impact if your AIX admins are willing to learn and set up WLM policies at the operating system level (not DB2 WLM).

This is really a question of capacity management.  Understanding the workloads in your environment is key here, and being able to identify (or predict) when you'll run into limits will influence your decisions about how many instance(s) to put on a single server.


Why EXPLAIN doesn't show heap fetches for index scan

Index scans fetch from the heap for every single row.  That is what makes it not be an index-only scan.  It only makes sense to display the count for index-only scans, as that is the only case in which it is informative.

The line "Buffers:" line may be more informative in general (for a realistic case where you have more than one row at stake).  But for your IOS that line shows 4 in both cases.  The reason that that doesn't change is that before vacuuming, the table doesn't have a visibility map, so there is no page to be read into a buffer.  It realizes the vm doesn't exist, an hits the table page instead.  After vacuuming, it hits the (now existing) vm page, which tells us doesn't need to hit the table.  So you always get 4 Buffers hit, 3 for the index and 1 for either the vm or the table.

If you were to partially populate the table, then vacuum so that the vm exists, then insert another record, and then query for that record, you would see that the IOS now hits 5 buffers.  3 for the index, one for the visibility map page which says the page is not all visible, and 1 for the table.

If you have large queries which hits the index many times, then interpreting the buffer hit count gets really tricky.  Within a single query execution, it keeps a pin on the most recently used visibility map page.  Hitting the same page repeatedly while you still hold a pin on it doesn't count as a new buffer hit.  Only if you need to switch to a different page in the vm does it count as a new buffer hit.

All of this unlikely to be very effective.  Make up a realistic data set of a realistic size on realistic hardware, and run realistic queries against it, and measure actual performance.  Once you have legitimate performance differences which need explaining, you can dig into the details to find the explanation.  But trying to extrapolate up from a one-row query is not very useful.


How to move SSAS tabular instance databases?

You can back up and restore SSAS Tabular 2012 databases, but you will have to adjust the security role memberships to include users in the correct domain. You can perform the backup and restore manually or through a script in Management Studio (or executed via PowerShell).

You can also re-deploy an SSAS database from the SSDT project or using the SSAS Deployment wizard. The wizard will allow you to deploy roles and ignore members. Next populate the role memberships with appropriate users. From there, you can process the model to populate it and bring the model online.

Another option is to script the SSAS database from Management Studio. Once you do this you can remove the  collection from each role and execute the XMLA on your target server.


Update value equal to the count of not appearance on the last X records

Study the example below:

mysql> create table test(id serial primary key, val int, cnt int);
Query OK, 0 rows affected (0.25 sec)

mysql> insert into test (val)
    -> select 10*rand()
    -> from (
    -> select 1 union all
    -> select 2 union all
    -> select 3 union all
    -> select 4 union all
    -> select 5 union all
    -> select 6 union all
    -> select 7 union all
    -> select 8 union all
    -> select 9 union all
    -> select 10
    -> ) dummy;
Query OK, 10 rows affected (0.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+------+------+
| id | val  | cnt  |
+----+------+------+
|  1 |    2 | NULL |
|  2 |    7 | NULL |
|  3 |    6 | NULL |
|  4 |    0 | NULL |
|  5 |    2 | NULL |
|  6 |    2 | NULL |
|  7 |    1 | NULL |
|  8 |    1 | NULL |
|  9 |    3 | NULL |
| 10 |    9 | NULL |
+----+------+------+
10 rows in set (0.00 sec)

mysql> create trigger cnt_absent_in_last_10 before insert on test
    -> for each row
    -> set new.cnt = (select 10 - count(distinct val) from (select * from test order by id desc limit 10) dummy);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into test (val)
    -> select 10*rand()
    -> from (
    -> select 1 union all
    -> select 2 union all
    -> select 3
    -> ) dummy;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+------+------+
| id | val  | cnt  |
+----+------+------+
|  1 |    2 | NULL |
|  2 |    7 | NULL |
|  3 |    6 | NULL |
|  4 |    0 | NULL |
|  5 |    2 | NULL |
|  6 |    2 | NULL |
|  7 |    1 | NULL |
|  8 |    1 | NULL |
|  9 |    3 | NULL |
| 10 |    9 | NULL |
| 16 |    8 |    3 | -- absent in 1..10: 4,5,8
| 17 |    4 |    2 | -- absent in 2..16: 4,5
| 18 |    6 |    2 | -- absent in 3..17: 5,7
+----+------+------+
13 rows in set (0.00 sec)

mysql>


You need to declare a table variable with the same shape as the TVF result, then use INSERT INTO not SELECT INTO, and remember to add brackets/parentheses to the end of the function name when invoking it, i.e. Foo() not just Foo:

DECLARE @Table TABLE (
    [Id] [int] NULL,
    [Number] [int] NOT NULL,
    [Name] [nvarchar](255) NULL,
    ... dozens of columns
);

// not best practice, but...
INSERT INTO @Table
SELECT *
FROM dbo.ufn_DeclareMaterialTableVariable();

// better - be explicit about the columns of interest
INSERT INTO @Table (
    [Id],
    [Number],
    [Name],
    ... dozens of columns
)
SELECT
    [Id],
    [Number],
    [Name],
    ... dozens of columns
FROM dbo.ufn_DeclareMaterialTableVariable();

If you've declared a table type, then the syntax for declaring a variable of that type is the following (note no TABLE keyword);

DECLARE @Table CustomTableType;

That said, annoyingly, you can't declare the result type of a TVF to be a table type - i.e. you always have to declare the returned columns again, explicitly - which partially negates the point of the custom type in the first place, in this situation.


Is there any way to find the exact Query responsible for replication lag?

log_slow_slave_statements = ON in the Slave's my.cnf.  Also long_query_time=1.  Turn on the slow log; wait a day; look in the slowlog.  mysqldumpslow -s t or pt-query-digest will summarize the slowlog, putting the 'worst' query first.

(From the Changelogs)
----- 2013-04-23 5.7.1 Milestone 11 & 2013-04-18 5.6.11 -- Bugs Fixed -- -----

  The --log-slow-admin-statements (
  http://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_log-slow-admin-statements
  ) and --log-slow-slave-statements (
  http://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#option_mysqld_log-slow-slave-statements
  ) command options now are exposed at runtime as the
  log_slow_admin_statements (
  http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_log_slow_admin_statements
  ) and log_slow_slave_statements (
  http://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#sysvar_log_slow_slave_statements ) system variables. Their values can be examined using SHOW VARIABLES
  ( http://dev.mysql.com/doc/refman/5.7/en/show-variables.html ). The
  variables are dynamic, so their values can be set at runtime. (The
  options were actually replaced by the system variables, but as system
  variables can be set at server startup, no option functionality is
  lost.) (Bug #59860, Bug #11766693)

In older versions, the command line --log-slow-slave-statements existed even though the variables did not.


SSIS Data Import from CSV to SQL Table Failing

The key here is a short snippet from the first error message:
Data conversion failed while converting column ""???Report Refresh Date"" (37) to column "Copy of "???Report Refresh Date"" (11).

This sort of error is always a pain to track down, but in short, it's saying that for some reason it's encountering a value that it's unable to convert to a date.

One trick you can use to try and figure out where the problem is located is to look at the number of rows the package is able to process before erroring out. You can usually see this by entering into the data flow task in question. If it's able to process 15 rows before erroring, you know that the problem row is somewhere around row 16. From there, you can see if anything is in a weird format, or if any values are out-of-bounds (think 2/30/2020).


Attribute and Value tables on SQL - Is this a good practice?

When you start using a relationnal database as a key/value store, that's normally a good indicator that you are dealing with schemaless/semi-structured data. SQL databases aren't best suited for the task and there are better alternatives such as NoSQL databases that will offer you great advantages over traditional relationnal databases, such as simplicity of design and performance gains.

Note: Schemaless data isn't the only reason that makes NoSQL databases attractive, for instance they also greatly simplify the tedious Object-relational mapping problem.

It's quite common these days to use multiple different storage technologies and the reason is simple: it's better to use the right tool to solve a problem elegantly than trying to adapt one to solve them all.

However, sometimes it isin't an option to choose your persitence layer for different reasons and you will have to find a way to deal with the problem with the tools you have.

The first thing I would do in your case is to check wheter the data you are trying to store really needs that level of schema flexibility and why?

Here's an example of a few valid use cases (in my opinion):

You need to allow the creation of new entities at runtime and be able to define and change their schema dynamically.
You need to store lots entities of the same kind where each of them could have arbritrary properties (think of various activities in an activity feed). I emphasis on lots because otherwise you can generally solve the problem using single table inheritance, concrete table inheritance or class table inheritance if it doesn't lead to an explosion of tables.

Now, both of these could probably still be solved using a standard relational approach but
the solution would feel wrong. For instance, to solve #1, nothing prevents you use a solution where tables are dynamically created and altered and for #2, nothing prevents you from manually managing an explosion of tables, but I think there are more maintainable alternatives.

If your reason isin't listed above, I strongly advise you to validate that you actually need this kind of flexibility by reviewing what your solution would look like if you used a standard approach. If you only need to create a few additionnal tables, that's generally the best path since there are many drawbacks for the various alternatives I propose below.

Practical solutions (not perfect) to store semi-structured data:

First of all, if applicable, identify carefully the common properties for all the different type of entities and create a table ready to hold that structure. That will consist of the semi part of our semi-structured data.

Then:

Add a column, let it be called "data", where you will store a serialized representation of your schemaless data in your data-interchange format of choice, such as JSON or XML.

ADVANTAGES:

Reads from the DB are very fast since you do not have to perform any joins.
You can store any kind of complex object hierarchy, as long as the
serialization format supports it.

DOWNSIDES:

The data has to be deserialized to be useful (it's generally fast enough not to cause any performance issues).
Searches are more complex and would rely on full-text searches (I've never used this solution when the data had to be searchable and have no idea how it would scale)
The data is denormalized and would have to be kept in sync, if applicable.
If you are too concerned about having to sync your data since it's denormalized,       an approach I've experimented is to store computed properties instead of the property    value. For instance, instead of storing the value "John Doe", you would store meta-data that allows to fetch the data dynamically at read, such as: id, table_name, field_name. You can implement memoization to make the reads faster. However I do not recommend this approach if you need a scalable solution.

Create another table, let it be called "EntityAttribute" that will look like (that's basically what you have I believe):

 id int NOT NULL IDENTITY(1, 1) PRIMARY KEY

 entity_id int NOT NULL

 [key] varchar(50) NOT NULL

 [value] sql_variant NOT NULL

Please note that both solutions also have the disadvantage of being unable to rely on database contraints to enforce data integrity.

In conclusion, make sure to clearly identify the problem and your needs first. Check if a solution can be efficiently implemented using the tools your have. If it cannot, check if there are other tools you could use that would suit your needs. Also, have a look at alternative ways of solving your issue with the technologies you have. I haven't highlighted all the advantages and drawbacks of the alternative solutions and there are probably other alternatives as well, so be sure to look up other sources before making any choice.


help me to migrate single myslq5.6 server to mysql5.6 Cluster, follow this error!

Thanks for everyone, i have just found a way to slove my question. follow here:
1.you must configure the config.ini file in your MGM nodes
/opt/mysql-cluster/etc/config.ini
Setting this parameter of "MaxNoOfConcurrentTransactions" a bigger value
2.if you reconfigure config.ini, you must delete this file of ndb_2_config.bin.1, and then restart MGM Services
ndb_2_config.bin.1 is a cache and binary file
3.in fact, if you recofingure confing.ini, you can use "# ndb_mgmd --ndb_nodeid=1 --initial -f /var/lib/mysql-cluster/config.ini" for updating ndb_2_config.bin.1 effective.
try it!


Without some way of tying the two tables together, you are asking for 6 million rows (6000*1000).

Instead of FROM employee e, employee_categories ec, you need something like

FROM employee e
JOIN employee_categories ec  ON e.category = ec.category

AND... be sure category (or whatever it is called) is indexed (perhaps PRIMARY KEY) in one of the tables.

Those changes should speed it up by a factor of 1000 or so.

The Query cache is irrelevant (and generally should be turned off).


How SQL Server transaction works with log records

Regarding visibility, SQL Server uses locking and/or row-versioning to implement isolation levels. Changes are always visible to the current session. Uncommitted changes are visible to other sessions only they are using the UNCOMMITTED ("daring they could get a dirty read"). Normally, other sessions will be blocked (due to locking) or will see the original value before changes (due to row versioning) until the transaction is committed.

Transaction isolation levels, and their implementation, are not related to transaction durability. It doesn't matter when log and data file writes occur for isolations levels to behave properly.

To guarantee durability and atomicity, log records are always written to the log before the corresponding data files. Importantly, this includes changes for both uncommitted and committed transactions. This write-ahead logging allows SQL Server to roll forward all transactions and then rollback uncommitted ones during recovery as well as when a ROLLBACK is performed.


Error running Powershell Script in SQL Server Agent

I believe your issue is going to be the SQLPS provider. Since PowerShell steps in SQL Server Agent automatically put you into the context of that provider some commands that work in your normal console will not function the same way. A write up was done here with Set-Location. You basically have to tell SQLPS the provider you want to use.

Your code would look something like below:

set-location -Path Microsoft.PowerShell.Core\FileSystem::"\\server\companydocuments\MyApp\Application Files\"

You could also wrap the remainder into two lines if you wanted to:

cd (Get-ChildItem | Sort-Object name -Descending | Select name -First 1).name
& ".\Application.exe"


Here is an old instructions for setting up master-master replication for windows but concept is same.
Considering you already have a 3306 instance by default you can follow below steps to setup as well (if above doesn't help)

  Make datadir and correct permissions:
  mkdir /var/lib/mysql3307
  chown -R mysql:mysql /var/lib/mysql3307
  Create config
  cp /etc/my.cnf /etc/my3307.cnf (Correct relevant parameters)
  Install basic auth system / dbs:
  mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql3307 --defaults-file=/etc/my3307.cnf
  Start mysql  mysqld_safe --defaults-file=/etc/my3307.cnf --user=mysql &
  Take fulldump from 3306 instance:  mysqldump -uroot -p --all-databases --master-data=2 | gzip > backup.sql.gz
  Load it to new 3307 instance
  zcat backup.sql.gz  | mysql -uroot -p --port=3307 --socket=/var/run/mysql/mysqld3307.sock
  Grab binary log co-ordinates from  backup:
  zcat backup.sql.gz | head -30 | grep -i change
  Create replication user on master:
  grant replication slave on . to 'repluser'@'%' identified by 'replpass';
  Run change master to on 3307 slave using binlogs take from  step #7:
  CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_PORT=3306, MASTER_LOG_FILE='XXXX',
  MASTER_LOG_POS=XXXX;
  Start  slave on  3307.

You're done.


Query to determine start and end dates based on time overlap


Error Unknown command '\U' when restoring MySQL dump on Windows

MariaDB [theDBName]> SOURCE C:\Users\myUserName\theDBName_data.dump

Either of the following should be correct:

MariaDB [theDBName]> SOURCE C:\\Users\\myUserName\\theDBName_data.dump

MariaDB [theDBName]> SOURCE C:/Users/myUserName/theDBName_data.dump


How to unite 2 subqueries to reduce retrieving data from cumbersome tables?

One idea is to count using an expression:

SELECT u.id, u.login, u.last_login_time
     , COUNT(CASE WHEN COALESCE(c.message_last_read_id_user1, 0)
                     < COALESCE(message_last_write_id_user2, 0)
                   AND m.id > COALESCE(message_last_read_id_user1, 0)
                   AND m.id <= COALESCE(message_last_write_id_user2, 0)
                   AND m.poster_id = c.user2_id
                  THEN 1
             END) as unread_user1
     , COUNT(CASE WHEN COALESCE(message_last_read_id_user2, 0)
                     < COALESCE(message_last_write_id_user1, 0)
                   AND c.user1_id > 0
                   AND m.id > COALESCE(message_last_read_id_user2, 0)
                   AND m.id <= COALESCE(message_last_write_id_user1, 0)
                   AND m.poster_id <> c.user2_id
                  THEN 1
             END) as unread_user2
FROM user u
JOIN conversation c
    ON c.user1_id = u.id
JOIN message m
    ON m.conversation_id = c.id
WHERE u.last_login_time >= 1452504418
GROUP BY u.id, u.login, u.last_login_time;

I might have missed some parts when rewriting, but hopefully you will get some ideas.


I can see no strong reason to avoid having a Ticket table that stores each serial number on a separate row, assuming that makes most sense for the application that will use this database. You should still normalise the overall design to avoid repeated data in this table.

"Thousands of rows" is pretty tiny for a relational database: you should easily be able to store all the tickets for thousands of events (~millions of rows) without any performance problems, provided you have clustered and indexed the table appropriately. This of course depends on your server hardware, and on how many events you expect to be servicing.

I assume events are short lived, and the record of tickets sold would not usually be needed after the event has happened. If so, you could easily have a TicketHistory table, and archive off rows to there once the event is over so the main table remains small and performant.


How can I prevent our IT admins with sysadmin to read data stored inside an encrypted database?

Per the other comments, one way to block them is to NOT have the IT Admins in the sysadmin role.  Sysadmin can override anything pretty much.

If they really need a high level of access and you dont want to out right encrypt your data, you could try a combination of a custom server role and/or database roles.  As long as you are on at least SQL server 2012 (for the server roles piece), this would allow you to grant them a more controlled level of access to the sql instance.

The real question is "Do they need SysAdmin?"  If the answer is yes, you'll have to encrypt the data.


SQL Server Index Maintenance-Conditional Rebuilds

It's totally an opinion and observation based in my current project where we handle databases over 20 TBs . We do not do any rebuild or re-organize of indexes as that is very expensive operation. So please check if that is actually helping you out in performance. Rather we focus more on update stats daily with change or modified stats.

If needed, you can use ola hallegren which has excellent parameters to set that up else use the one available from Microsoft.  The SQL tiger team has been doing an excellent job for such activities in their git hub page for adaptive index defrag proc  here


Disk Usage Report SSMS - query to obtain same info

If you just want to find auto growth and shrink events, you can try this out:

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 260)) + N'log.trc'
FROM   sys.traces
WHERE  is_default = 1;

SELECT   te.name AS EventName,
         t.DatabaseName,
         t.DatabaseID,
         t.NTDomainName,
         t.ApplicationName,
         t.LoginName,
         t.SPID,
         t.Duration,
         t.StartTime,
         t.EndTime,
         (IntegerData * 8 / 1024.) as ChangeInSize
FROM     sys.fn_trace_gettable(@path, DEFAULT) AS t
JOIN     sys.trace_events AS te
ON t.EventClass = te.trace_event_id
WHERE    te.name = 'Log File Auto Shrink'
         OR te.name = 'Data File Auto Shrink'
         OR te.name = 'Log File Auto Grow'
         OR te.name = 'Data File Auto Grow'
ORDER BY t.StartTime DESC;

Adapted from Aaron Bertrand's query here.

More reading about querying the default trace here.


This means the that the listener is up and running, but the SID or SERVICE_NAME in the CONNECT_DATA section of your net service connect descriptor does not specify a SID or SERVICE_NAME that the listener is aware of.  The listener does not know where to send your connection.

See the full text of the error message:

$oerr ora 12505
12505, 00000, "TNS:listener does not currently know of SID given in connect descriptor"
// *Cause:  The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a SID for an instance (usually a database instance) that either
// has not yet dynamically registered with the listener or has not been
// statically configured for the listener. This may be a temporary condition
// such as after the listener has started, but before the database instance
// has registered with the listener.
// *Action:
//  - Wait a moment and try to connect a second time.
//  - Check which instances are currently known by the listener by executing:
//    lsnrctl services <listener name>
//  - Check that the SID parameter in the connect descriptor specifies
//    an instance known by the listener.
//  - Check for an event in the listener.log file.

Hope that helps.


MySQL/MariaDB Read only except for a single table

I don't believe you can do read_only = 1 (except for table x);

So I would think your best option would be to set all your users to read_only:

GRANT SELECT ON *.* TO 'user'@'1.2.3.4';

Then the users that need to write to that table would be given permission to write to just that table. e.g.

GRANT INSERT, UPDATE ON 'dbname.tablename'@'1.2.3.4';


"Lost connection to MySQL server during query" error

I am running MySQL 5.7.26 on Windows and when doing just a simple query from the MySQL command line client I get that same error you describing. I found out it only occurs when the query length is exact 64 characters! Just by adding a space fixed the problem for me.
It doesn't matter if it's a select or insert/delete/update query.
I have run 66.000 queries on my server and only the queries with exact 64 characters failed.
I checked the database for corruption. But I could also reproduce same problem when making a new database and table on that server.
I could only reproduce this problem on that production server. I created a new server with same version with exact same settings as a VMWare machine but could not recreate. Used the same MySQL client.


Adding more space + bandwidth to a MongoDB cluster

The config servers only hold metadata about which chunk is stored on which cluster, no actual data.

There is no such thing called a "master server" in mongoDB, but I think what you are talking about is the shard router (mongos). When that is the case, then that server doesn't store any data at all.

So the total capacity of the cluster is the sum of the capacity of your shards, which is 40GB * 5 = 200GB.

Should the mongo router become a bottleneck, you can add more of them. A cluster can be accessed by any number of routers. By the way: it's not uncommon to put the mongos process(es) on the same machines where the application(s) are running.

  "Or replication across two sets of clusters?"

Replication in MongoDB is on shard-level. Each shard can (and in a production setup should) be a replica-set of two or more nodes.


Select after an Uncommited Update in Python-MySQLdb

tl;dr Yes, sample will contain the rows that were just updated.

Please note that you're not in a transaction context. So every query is an implicit transaction in this specific case.

Assuming you did have a begin somewhere sample would still see the rows because it's the same connection and the same transaction.

If you started the transaction and opened a separate connection or you had a concurrent process/request it would only see the rows without commit if your mysql is running with READ-UNCOMMITTED transaction level.

You can check the global settings by:

mysql> show global variables like 'tx_isolation';

It can also be set for individual connection:

mysql> set tx_isolation='READ-UNCOMMITTED';

I hope this answers your question.


What is the difference between bridge table and helper table in data warehouse's context?

Bridge tables are the official name according to Dimensional Modeling as defined by the Kimball methodology.

Helper tables as far as I know have been used in many (often incorrect) contexts, one of which are bridge tables by people who fail to use the correct terminology.

Some people use helper table as a synonym for bridge tables, but helper tables also seem to have gotten misused in the context of a staging database or datawarehouses to name any table that helps something.

However, in dimensional modeling "helper table" isn't really defined, if it's used it's mostly as a synonym for "bridge table" (which is an actual term) or could be used to name about any table which "helps" achieving a goal but then there isn't a real definition.


MongoDB - multi wirte data to same document

With (multiple) updates to (single) document, first come, first served. So, that client who is first (in time) get lock to that document, others who come later, will get "ticket" (queue number) and will wait their turn to update that document.

So, update 2 will override field1 value and add field2, update 3 then will override field2 value..


Warning: mysqli_real_connect(): (HY000/2002): Connection refused in /var/www/html/wordpress/wp-includes/wp-db.php on line 1626

first check wp-config.php for this 4 lines

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define( 'DB_NAME', 'database_name_here' );
/** MySQL database username */
define( 'DB_USER', 'username_here' );
/** MySQL database password */
define( 'DB_PASSWORD', 'password_here' );
/** MySQL hostname */
define( 'DB_HOST', 'localhost' );

then Check Your Database Host Information
and if you failed do this action
add define('WP_ALLOW_REPAIR', true); in your wp-config.php
and after you have done ,navigate with http://yourwebsite.com/wp-admin/maint/repair.php.
and better before doing disactivate all pluging via ftp
rename plugins to plugins.deactivate.
repair wordpress
and finnaly Update WordPress Site URL in wp_options tables
Update WordPress Site URL


How to create a Relation between a table and a many-to-many relation table

In general I see nothing wrong with a many-to-many-to-many table where you have all three IDs.  In this case however, you have additional columns on the SkillLevel table meaning that the Skill+Level pair has it's own unique properties.  Given that I would go ahead and create an additional SkillLevelID column and use it in your SkillLevelStaff table.

Whether or not you use the SkillLevelID as the primary key is a bit trickier.  Honestly in my opinion, in the end it doesn't matter.  You will need at least a unique key on the SkillLevelID if you are using foreign keys and you will want at least a unique key on the SkillID + LevelID pair in order to enforce the business rule that the combination is unique.  It won't really matter which one is the primary key.  The clustered index on the other hand will matter.  If you do most of your queries joined to the SkillLevelStaff table then it might be a good idea to use the surrogate key (SkillLevelID) and it will certainly avoid fragmentation.  On the other hand something like SkillLevel probably doesn't get updated frequently so the amount of fragmentation caused by using the natural key (SkillID + LevelID) is probably going to be minimal. It will however be better if the majority of your queries use SkillID or SkillID + LevelID as part of the WHERE clause (or joining to those tables).  You will probably want to do some experimentation there.


Is it legal for SQL Server to fill PERSISTED columns with data that does not match the definition?

This is certainly a bug. The fact that the col1 values happened to be the result of an expression involving random numbers clearly doesn't change what the the correct value for col2 is supposed to be. DBCC CHECKDB returns an error if this is run against a permanent table.

create table test (
    Col1 INT,
    Contains2 AS CASE WHEN 2 IN (Col1) THEN 1 ELSE 0 END PERSISTED);

INSERT INTO test (Col1) VALUES
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5));

DBCC CHECKDB

Gives (for my test run which had one "impossible" row)

Msg 2537, Level 16, State 106, Line 17
Table error: object ID 437576597, index ID 0, partition ID 72057594041008128, alloc unit ID 72057594046251008 (type In-row data), page (1:121), row 0. The record check (valid computed column) failed. The values are 2 and 0.
DBCC results for 'test'.
There are 5 rows in 1 pages for object "test".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'test' (object ID 437576597).

It does also report that

  repair_allow_data_loss is the minimum repair level for the errors
  found by DBCC CHECKDB

And if taken up on the repair option unceremoniously deletes the whole row as it has no way of telling which column is corrupted.

Attaching a debugger shows that the NEWID() is being evaluated twice per inserted row. Once before the CASE expression is evaluated and once inside it.

A possible workaround might be to use

INSERT INTO @test
            (Col1)
SELECT ( ABS(CHECKSUM(NEWID()) % 5) )
FROM   (VALUES (1),(1),(1),(1),(1)) V(X);

Which for one reason or another avoids the issue and only evaluates the expression once per row.


Is "resort" a special type of entity? If not then you could model this as:

Customer
--------
  CustomerID - unique primary key
  Name - text
  (other customer fields)

Accomodation
------------
  AccomodationID - unique primary key
  LocationID - foreign key to "where" the accomodation is
  AccomodationTypeID - foreign key to types of accomodations
  (other stuff such as "# bathrooms", "# beds...")

Location
--------
  LocationID - unique primiary key
  LocationTypeID - foreign key to location type
  (other fields)

Reservasions
------------
  ReservationID - unique primary key
  CustomerID - foreign key to customer
  AccomodationID - foreign key to accomodation
  StartDate
  EndDate
  (any other fields you need)

LocationTypes
-------------
  LocationTypeID
  Description (such as "Resort", "campground",...)

AccomodationTypes
-----------------
  AccomodationTypeID
  Description (such as "Lean-to", "chalet", "motel",...)


PostgreSQL streaming replication stops updating

TL;DR: Do not disable max_standby_streaming_delay in the config.

For the sake of others looking for answers on this one, I thought I would answer my own question since the amount of information out there on this topic via Google is very slim.

Digging around, I managed to discover a book called Troubleshooting PostgreSQL: https://www.packtpub.com/big-data-and-business-intelligence/troubleshooting-postgresql

It comes in ebook format and was useful for helping me to track down the issue I was experiencing.  It describes using a tool called pg_view which has a lot of very useful realtime metrics on what's happening in your PostgreSQL server.

Running it on one of my servers that had halted replication showed the problem occurred during long running queries:

db.server.com up 21:34:25 36 cores Linux 4.4.5-15.26.amzn1.x86_64 load average 1.79 0.94 0.47                                                                                08:58:29
 sys: utime 5.7   stime 0.1   idle 94.3  iowait 0.0   ctxt 580 run 4   block 0
 mem: total 59.0GB free 2.3GB  buffers 41.0MB cached 55.0GB dirty 692KB  limit 29.5GB as 14.7GB left 14.8GB
 /var/lib/pgsql/9.5/data 9.5.2 standby connections: 5 of 500 allocated, 2 active
 type dev     fill   total    left   read  write    await path_size path
 data md127    0.0 499.7GB 382.8GB    0.0    0.1      0.0   116.6GB /var/lib/pgsql/9.5/data
 xlog xvdg     0.0 100.0GB  84.0GB    0.0    0.6     15.7    15.9GB /var/lib/pgsql/9.5/data/pg_xlog
   pid type    s utime stime guest read write   age  uss db   user     query
 26644 backend R 100.2   0.0   0.0  0.0   0.0 03:21 93.4 data postgres WITH query AS ( WITH original_transaction_id AS( SELECT original_transaction_id FROM table WHERE...
 26645 backend R  99.3   0.0   0.0  0.0   0.0 00:01 93.8 data postgres WITH query AS( SELECT COUNT(id) AS total, original_transaction_id FROM table GROUP BY original_tr...

The key there is the age column showing the long running query.  The secret, however, is to show all the processes on the server by pressing the 's' hotkey, which isn't described in the pg_view GitHub page.  This shows the system processes, including the startup and WAL receiver:

   pid type            s utime stime guest read write   age  uss db   user     query
 12169 startup         S   0.0   0.0   0.0  0.0   0.0        1.2               recovering 000000010000070A0000006D...
 12170 checkpointer    S   0.0   0.0   0.0  0.0   1.9        1.2               ...
 12171 writer          S   0.0   0.0   0.0  0.0   0.0        1.0               ...
 12172 stats collector S   0.0   0.0   0.0  0.0   0.0        1.2               ...
 12178 wal receiver    S   0.0   1.0   0.0  0.0   1.1        1.2               streaming 70A/6D998000...

The useful info there is on the startupline where it says recovering.  This is normal, as it is a read-only slave and is technically always recovering as new data comes in via the wal receiver.

The problem is when it has waiting on the end of that line - what this tells you is that the server is stuck waiting for a long running query to release a lock on a table it needs to update, and until it does, replication is halted, or  the max_standby_streaming_delay value is reached.

By default, max_standby_streaming_delay is set to 30ms.  However, it was disabled on my servers, perhaps for some perceived benefit to replication reliability:

max_standby_streaming_delay = -1

Removing that line from the config file and reloading fixed the issue going forward.

Another outcome was for us to move all long-running queries to a separate replica only used my these backend processes to help long-term reliability and avoid possible table locks on customer-facing databases.

Most of the troubleshooting guides say that replication issues are network related, but in this case it was a misconfiguration and poorly-formed SQL queries.


As you have seen, a simple GROUP BY will not work because it would return only one record per group.

Your join works fine.
For a large table, it will be efficient only if there is an index on the join columns (num and text).

Alternatively, you could use a correlated subquery:

SELECT *
FROM t
WHERE num = (SELECT MIN(num)
             FROM t AS t2
             WHERE t2.text = t.text);

SQLFiddle

When being executed, this query does not require a temporary table (your query does for the result of u), but will execute the subquery for each record in t, so text should be indexed. (Or use an index on both text and num to get a covering index.)


You should use MySQL's BENCHMARK() function

Here are my posts on how to use it

Apr 03, 2012 : How to use the 'select benchmark' command on a procedure
Apr 16, 2013 : MySQL: CAST + SUBSTR vs. FLOOR + MOD

BTW if you do configuartion changes, you should restart mysqld immediately after the configuration changes are in place. Then, run your I/O tests.


Compare the hash password value in mysql.user

This question was explained several times in the DBA StackExchange

Nov 21, 2013 : Why are the passwords in MySQL (5.x) not salted? (by Michael - sqlbot)
Dec 26, 2013 : MYSQL - Unable to change 'old_passwords' variable
Apr 22, 2015 : Why are mysql password hashes internally saved with a star (asterisk)?

I actually learned the formula from a post from PalominoDB (later changed name to Blackbird and then was purchased by Pythian). The post was made Dec 4, 2011 entitled "Hashing Algorithm In MySQL PASSWORD()"

So if you want to execute this function yourself, it would look something like this:

SET @my_new_pass = 'WhateverIWantAsThePassword';
SET @my_new_hash = CONCAT('*',UPPER(SHA1(UNHEX(SHA1(@my_new_pass))))) MySQLPWDHASH;

This would land in the MySQL history ~/.mysql_history but not in the binary logs.


Delete data from a specific partition in MS SQL 2012

This delete statement is invalid SQL Server 2012 syntax so it will do nothing but raise an error.

Removing a partition and it's associated boundary is done with MERGE DDL; DML statements like DELETE never remove partitions.

The most efficient way to remove all rows from a partition in SQL 2014 and earlier versions is with SWITCH (SQL 2016 allows specific partitions to be specified with a TRUNCATE). To use SWITCH, create a similarly partitioned staging table with identical schema and indexes. You can then switch data between tables as a meta-data operation and then TRUNCATE the staging table.

ALTER TABLE dbo.YourTable
    SWITCH PARTITION 1 TO dbo.YourTable_Staging PARTITION 1;
ALTER TABLE dbo.YourTable
    SWITCH PARTITION 2 TO dbo.YourTable_Staging PARTITION 2;
ALTER TABLE dbo.YourTable
    SWITCH PARTITION 3 TO dbo.YourTable_Staging PARTITION 3;
ALTER TABLE dbo.YourTable
    SWITCH PARTITION 4 TO dbo.YourTable_Staging PARTITION 4;
ALTER TABLE dbo.YourTable
    SWITCH PARTITION 5 TO dbo.YourTable_Staging PARTITION 5;
TRUNCATE TABLE dbo.YourTable_Staging;


Oracle: converting a TNS entry with SID to instantclient?

Use your working connection that connects using the SID to find which services are in the database. If you are connected run:

select name, blocked from v$active_services;

and use one of the names you get to connect to.

sqlplus myname/mypass@//myhost.example.com:1521/{a name from query}


Automate - recover database using backup controfile

Use AUTOMATIC.

RECOVER AUTOMATIC DATABASE UNTIL TIME '2015-10-20:13:54:16' USING BACKUP CONTROLFILE;

CONTINUE [DEFAULT] is a different branch and can not be used together with clauses like UNTIL and USING BACKUP CONTROLFILE.

Control file created.

SQL> recover automatic database until change 473319 using backup controlfile;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select resetlogs_change# from v$database;

RESETLOGS_CHANGE#
-----------------
           473320


How to find a data warehouse's design diagram?

You probably need a product that can perform database design reverse engineering.

Having said that, we refrain from giving product advice on dba.stackexchange.com since questions about which tool, library, product or resource you should use quickly become obsolete and often are just about the preferences of the answerer.


SQL Server restore differential after restoring full backup

No, sorry. Once the log chain was broken the following differential cannot be restored because there are at least a few transactions that would be lost.  You may feel that those 'unimportant' changes could just be ignored. But SQL Server insists on a complete restore chain.

Though there are certainly some companies that will try to reconstitute a database for you. Not for free, of course.


replication warning: transactional replication latency alert not working

As a work around (and to have more content in an alert), I wrote my own warning system that sends an email. http://www.sqlwebpedia.com/content/sql-replication-undelivered-command-count


Drop datafile without dropping a tablespace in Oracle 11g

The Database Backup and Recovery Reference says that you can do a backup by

backup incremental level 0 as compressed backupset database skip inaccessible;

or better

backup incremental level 0 as compressed backupset database skip offline;

But your problem is that the data from datafile 5 is lot if do do not have a valid backup.

If you have a valid backup vou can restore datafile 5.

If you don't have a valid backup you have two option:

you drop the tablespace and everything from this tablespace will be removed forever
export as much as possible , drop and recreate the tablespace and import what you have exported before.

"export as much as possible" means:

you can export the definitions of the objects from this tablespace because these definitions reside in the SYSTEM tablespace.
if the tablespace consists of more than one datafile you can export the data from all remaining datafiles of this taplespace.

These export can be done with the expdp datapump tool.
Use the TABLESPACES parameter to specify the name of the tablespace you want to export. If you only want to export the definitions of the objects set CONTENT=METADATA_ONLY

The import can be done with the impdp tool.


The rationale is probably based on which datetime representation is easier to handle for both MySQL and the OS (in this case, Windows XP)

DATETIME takes up 8 bytes
TIMESTAMP takes up 4 bytes

Their Data and Time Ranges would differ greatly. Note further differences in the MySQL Documentation

  The DATETIME type is used for values that contain both date and time
  parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD
  HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to
  '9999-12-31 23:59:59'.

  The TIMESTAMP data type is used for values that contain both date and
  time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to
  '2038-01-19 03:14:07' UTC.

  MySQL converts TIMESTAMP values from the current time zone to UTC for
  storage, and back from UTC to the current time zone for retrieval.
  (This does not occur for other types such as DATETIME.) By default,
  the current time zone for each connection is the server's time. The
  time zone can be set on a per-connection basis. As long as the time
  zone setting remains constant, you get back the same value you store.
  If you store a TIMESTAMP value, and then change the time zone and
  retrieve the value, the retrieved value is different from the value
  you stored. This occurs because the same time zone was not used for
  conversion in both directions. The current time zone is available as
  the value of the time_zone system variable. For more information, see
  Section 10.6, “MySQL Server Time Zone Support”.

Since both MySQL and Windows have TimeZone Support, it was probably in MySQL's best interest  to have mysqld more Windows-aware. This would probably be a secret incentive to get your MySQL database out of Windows and into Linux. (My Conspiracy Theory)


How to increase performance in Cross Database operations?

How is the maintenance of these two database?

The first have a lot of reads and the second have a lot of writes
The first database needs update statistic before the process and the second database needs a dbcc dbreindex() to avoid prevent space alocation and page split


Rebuilding fragmented indexes on SQL Server

If the indexes are fragmenting that quickly due to massive amounts of activity affecting arbitrary user-provided values then they may be little you can do.

If the indexes that are fragmenting badly are over UNIQUEIDENTIFIER columns then these will fragment quickly when they see a lot of activity because the data is effectively random. If this is the case then you can minimize this greatly by using NEWSEQUENTIALID() instead of NEWID(), or the equivalent in your application if you are generating UUIDs there instead of in the database.

If you are generating UUIDs outside SQL Server make sure that they are generated in a way that is sequential in SQL Server's sort order (different generators use different byte orderings) otherwise you lose some or all of the benefit. See this for reference to .Net's UuidCreateSequential not producing UUIDs with the same ordering properties to SQL Server's NEWSEQUENTIALID() and how to juggle the result to work.


Do I need ORDER BY in a window, if I already have a properly ordered query?

Yes

You can actually see that without going to the spec.

CREATE TABLE foo AS
  SELECT trunc(random()*10) AS x
  FROM generate_series(1,100);

with

SELECT row_number() OVER (), x
FROM foo
ORDER BY x;

 row_number | x
------------+---
         95 | 0
         93 | 0
         75 | 0
         74 | 0
         21 | 0
         55 | 0
         97 | 0
         54 | 0
         26 | 0
          5 | 0

Versus the window function over an ordered partition

SELECT row_number() OVER (ORDER BY x), x
FROM foo;

 row_number | x
------------+---
          1 | 0
          2 | 0
          3 | 0
          4 | 0
          5 | 0
          6 | 0


Any way to track who has been executing what queries? who has been on my lawn?

Queries aren't logged by SQL Server, unless they meet some criteria that puts them into the default trace or system_health Extended Events session (some details on what they collect here).

If you can control data access via stored procedures, you can easily add your own logging to those. But that doesn't sound like the case. Otherwise you will have to rely on triggers (for DML, but not SELECT), server-side trace, extended events, auditing, or 3rd party monitoring tools.


Do WHERE IN queries reindex the table after each set within the query?

No SELECT causes reindexing.  Only ALTER, OPTIMIZE, and a few other commands, may cause reindexing.
The construct (a,b) IN ((1,2), (3,4)) is not well optimized.

So,...  Make it two DELETEs, such as

DELETE FROM `table1`
    WHERE djID = 128
      AND localID = 26;

If you have lots of them, you could build a tmp table with the pairs of numbers, then do a multi-table DELETE.

To demonstrate that it is doing far more work than it ought to,

FLUSH STATUS;
DELETE ... WHERE ... IN ...;
SHOW SESSION STATUS LIKE 'Handler%';

You will probably see some numbers about 20M (or maybe 40M), indicating that it did a table scan in spite of having the index.

Then file a bug with http://bugs.mysql.com (unless there already is one).


Telling you directly the answer would be cheating, so I will give you just a hint- the problem is about a very very famous SQL problem, so much, that it has its own page on the MySQL manual:

https://dev.mysql.com/doc/refman/5.7/en/examples.html

And a search finds that this kind of problem has been asked on this site at least a dozen times already.

Telling you more than that would be against the spirit of the challenge.

I can explain why it errors out- When using an aggregated function, you can only select fields you have aggregated by, or other fields, using an aggregated function, but you cannot select non-aggregated-by fields, because SQL doesn't know you want the minimum one. What you want to do is to find the minimum, and then use that value to get the corresponding one on the same row, but you cannot "aggregate and get the value of another column" with such a simple query- once it gets aggregated, the other fields get "lost". In previous versions of MySQL, your query was legal, but it gave out a "random/not deterministic" id value.


Don't use IN ( SELECT ... ).  But it seems to be necessary due to the @row_num computation.

Don't use @row_num computation if you can do the equivalent with ORDER BY ... LIMIT ....

What are you trying to do?  Set delete_flag=true for all but the latest 5 rows?  "Latest" is defined by fileRevId?

"Million records"?  In which table?  Or do you mean that you need to call the proc a million times?

In checking for 5 rows, are there cases where you have thousands?  I ask because 5+ rows can possibly be optimized one way, but 1000+ rows may need a different optimization.

Please provide SHOW CREATE TABLE FileRevisions, I need to check what composite indexes you have.

Update

The schema, as you have it, does not allow for more than one revision per FileId -- because of PRIMARY KEY(FileId) on FileRevisions.  Also AUTO_INCREMENT demands to be the first column in some index.  So, I recommend

 PRIMARY KEY (FileID),
 KEY FileRev_FileID_FK_idx(FileId),

-->

 PRIMARY KEY (FileID, fileRevId),
 INDEX(fileRevId)


How to find the current value for 'log_history' of PURGE_LOG job in Oracle

dbms_scheduler.get_scheduler_attribute('log_history',x);

do a desc on dbms_scheduler to see the possibilities...


MongoDB Storage Architecture (MySQL innodb_file_per_table equivalent to MongoDB)

MongoDB only creates discrete file-sets for discrete databases. There isn't an option to split out collections into files like table-files in MySQL.


Temporarily Turn Off Table Type Reference Checking On A Development Database

No, there is no way to alter a table type, or drop it while any object references it directly. It would be nice to have a "just trust me, I know what I'm doing" mode, but we don't; you'll have to file a feature request to get that functionality.
In the meantime, what I would suggest is to just create a new table type, and then update the stored procedures in the order that they need to use the new type's definition. This prevents breaking the old procedures until you can modify them, too. A little more info here:

How to Alter User Defined Table Type in SQL Server


Calculations with group-wise sum and row count

Please Alter you column year to col_year and value to col_value because YEAR is a builtin function of mysql. and run below query. I also attached screenshot of query and result. Thanks

 SELECT title, col_year, col_value, (col_value/total_sum/total_row) AS  result FROM bar
LEFT JOIN
(SELECT SUM(col_value) AS total_sum, COUNT(col_value) AS total_row,col_year AS qq FROM bar GROUP BY col_year) AS sub_query
ON sub_query.qq = bar.col_year ORDER BY col_value


How to optimise T-SQL query using Execution Plan

Before getting to the main answer, there are two pieces of software you need to update.
Required Software Updates
The first is SQL Server. You are running SQL Server 2008 Service Pack 1 (build 2531). You ought to be patched up to at least the current Service Pack (SQL Server 2008 Service Pack 3 - build 5500). The most recent build of SQL Server 2008 at the time of writing is Service Pack 3, Cumulative Update 12 (build 5844).
The second piece of software is SQL Sentry Plan Explorer. The latest versions have  significant new features and fixes, including the ability to directly upload a query plan for expert analysis (no need to paste XML anywhere!)
Query Plan Analysis
The cardinality estimate for the table variable is exactly right, thanks to a statement-level recompilation:

Unfortunately, table variables do not maintain distribution statistics, so all the optimizer knows is that there are six rows; it knows nothing of the values that might be in those six rows. This information is crucial given that the next operation is a join to another table. The cardinality estimate from that join is based on a wild guess by the optimizer:

From that point on, the plan chosen by the optimizer is based on incorrect information, so it's no wonder really that performance is so poor. In particular, the memory set aside for sorts and hash tables for hash joins will be far too small. At execution time, the overflowing sorts and hashing operations will be spilled out to physical tempdb disk.
SQL Server 2008 does not highlight this in execution plans; you can monitor the spills using  Extended Events or Profiler Sort Warnings and Hash Warnings. Memory is reserved for sorts and hashes based on cardinality estimates before execution starts, and cannot be increased during execution regardless of how much spare memory your SQL Server may have. Accurate row count estimates are therefore crucial for any execution plan that involves workspace memory consuming operations.
Your query is also parameterized. You should consider adding OPTION (RECOMPILE) to the query if different parameter values affect the query plan. You should probably consider using it anyway, so the optimizer can see the value of @Param1 at compilation time. If nothing else, this may help the optimizer produce a more reasonable estimate for the index seek shown above, given that the table is very large, and partitioned. It may also enable static partition elimination.
Try the query again with a temporary table instead of the table variable and OPTION (RECOMPILE). You should also try materializing the result of the first join into another temporary table, and run the rest of the query against that. The number of rows is not all that large (3,285,620) so this should be reasonably quick. The optimizer will then have an exact cardinality estimate and distribution statistics for the result of the join. With luck, the rest of the plan will fall into place nicely.
Working from the properties shown in the plan, the materializing query would be:
SELECT
    A.A_Var7_FK_LK,
    A.A_Var4_FK_LK,
    A.A_Var6_FK_LK,
    A.A_Var5_FK_LK,
    A.A_Var1,
    A.A_Var2,
    A.A_Var3_FK_LK
INTO #AnotherTempTable
FROM @MyTableVar AS B
JOIN TableA AS A
    ON A.Job = B.B_Var1_PK
WHERE
    A_Var8_FK_LK = @Param1;

You could also INSERT into a predefined temporary table (the correct data types are not shown in the plan, so I cannot do that part). The new temporary table may or may not benefit from clustered and nonclustered indexes.