Extra tables are added when I create a new Database

Someone added tables to the model database.

  When a CREATE DATABASE statement is issued, the first part of the
  database is created by copying in the contents of the model database.
  The rest of the new database is then filled with empty pages.

  If you modify the model database, all databases created afterward will
  inherit those changes. For example, you could set permissions or
  database options, or add objects such as tables, functions, or stored
  procedures.


EMS Data Export for MySQL

When I search "MySQL Export Latex" dozens of links leads to this product.


Transforming Huge Database table to Another Table

Since existing records are static they can be converted to the new schema any time after the migration process is signed off.  Specifically, as soon as the new DB is available you can start loading it with old data.  Start with the oldest and work your way forward one day at a time.  Assuming it takes less than twenty four hours to convert one day's data, you can run this process non-stop until you have caught up with today's data.

Thereafter you can continue with a more-or-less parallel run, with two options.  Option "A" is to load each new day's data into the old DB, then run the convertion process to get it into the new DB.  Option "B" is to run the old ETL to load today's source file into the old DB and run the new ETL to load the same soruce file into the new DB.  Option "B" has the advantage of testing your new ETL process, too.

Then on cut-over day you will have at most one day's worth of data to move.

As for the convertion process itself I would suggest you read all the data for one business transaction from the old tables (i.e. a referentially in tact set), convert it, then write it entirely to a set of staging tables in the new schema.  Note not to the live tables themselves but to a parallel set of staging tables.  Then you can write lots of reconciliation checkes against these staging tables.  If there's a problem you can bulk-delete the staging tables without affecting the new DB's data, fix the convertion and re-run.

Once the reconciliation completes successfully you can bulk-copy from the staging to the live tables.  The staging tables can have additional columns to help with reconciliation that you would not want in the live tables (source surrogate ID, for example).  Retain the staging tables for a few months for any post-mortem on your go-live problems.

I know it sounds like all this staging and reconciliation is eating precious time during a cut-over.  And it is, but is nothing compared to the time it will take to post-fix two hundred million rows in an active production system.


Storing IP addresses - varchar(45) vs varbinary(16)

how to stored the actual IP address - in text or bytes format. Which is going to be better?

Since "text" here refers to VARCHAR(45) and "bytes" refers to VARBINARY(16), I would say: neither.

Given the following information (from Wikipedia article on IPv6):

  Address representation
  The 128 bits of an IPv6 address are represented in 8 groups of 16 bits each. Each group is written as 4 hexadecimal digits and the groups are separated by colons (:). The address 2001:0db8:0000:0000:0000:ff00:0042:8329 is an example of this representation.

  For convenience, an IPv6 address may be abbreviated to shorter notations by application of the following rules, where possible.

  One or more leading zeroes from any groups of hexadecimal digits are removed; this is usually done to either all or none of the leading zeroes. For example, the group 0042 is converted to 42.
  Consecutive sections of zeroes are replaced with a double colon (::). The double colon may only be used once in an address, as multiple use would render the address indeterminate. RFC 5952 recommends that a double colon must not be used to denote an omitted single section of zeroes.[41]

  An example of application of these rules:

          Initial address: 2001:0db8:0000:0000:0000:ff00:0042:8329
          After removing all leading zeroes in each group: 2001:db8:0:0:0:ff00:42:8329
          After omitting consecutive sections of zeroes: 2001:db8::ff00:42:8329

I would start by using 8 VARBINARY(2) fields to represent the 8 groups. The fields for Groups 5 - 8 should be NULL as they will only be used for IPv6 addresses. The fields for Groups 1 - 4 should be NOT NULL as they will be used for both IPv4 and IPv6 addresses.

By keeping each group independent (as opposed to combining them into either a VARCHAR(45) or a VARBINARY(16) or even two BIGINT fields) you get two main benefits:

It is much easier to reconstruct the address into any particular representation. Otherwise, in order to replace consecutive groups of zeroes with (::) you would have to parse it out. Keeping them separate allows for simple IF / IIF / CASE statements to facilitate this.
You will save a ton of space on IPv6 addresses by enabling either ROW COMPRESSION or PAGE COMPRESSION. Since both types of COMPRESSION will allow for fields that are 0x00 to take up 0 bytes, all of those groups of zeroes will now not cost you anything. On the other hand, if you stored the example address from above (in the Wikipedia quote), then the 3 sets of all zeroes in the middle would take up their full amount of space (unless you were doing the VARCHAR(45) and went with the reduced notation, but that might not work well for indexing and would require special parsing to reconstruct it to the full format, so let's assume that is not an option ;-).

IF you need to capture the Network, create a TINYINT field for that called, um, [Network] :-)

For more info on the Network value, here is some info from another Wikipedia article on the IPv6 address:

  Networks

  An IPv6 network uses an address block that is a contiguous group of IPv6 addresses of a size that is a power of two. The leading set of bits of the addresses are identical for all hosts in a given network, and are called the network's address or routing prefix.

  Network address ranges are written in CIDR notation. A network is denoted by the first address in the block (ending in all zeroes), a slash (/), and a decimal value equal to the size in bits of the prefix. For example, the network written as 2001:db8:1234::/48 starts at address 2001:db8:1234:0000:0000:0000:0000:0000 and ends at 2001:db8:1234:ffff:ffff:ffff:ffff:ffff.

  The routing prefix of an interface address may be directly indicated with the address by CIDR notation. For example, the configuration of an interface with address 2001:db8:a::123 connected to subnet 2001:db8:a::/64 is written as 2001:db8:a::123/64.

For indexing, I would say create a Non-Clustered index on the 8 Group fields, and possibly the Network field if you decide to include that.

End result should be something like the following:

CREATE TABLE [IPAddress]
(
  IPAddressID INT          NOT NULL IDENTITY(-2147483648, 1),
  Group8      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
  Group7      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
  Group6      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
  Group5      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
  Group4      VARBINARY(2) NOT NULL, -- both
  Group3      VARBINARY(2) NOT NULL, -- both
  Group2      VARBINARY(2) NOT NULL, -- both
  Group1      VARBINARY(2) NOT NULL, -- both
  Network     TINYINT      NULL
);

ALTER TABLE [IPAddress]
  ADD CONSTRAINT [PK_IPAddress]
  PRIMARY KEY CLUSTERED
  (IPAddressID ASC)
  WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE);

CREATE NONCLUSTERED INDEX [IX_IPAddress_Groups]
  ON [IPAddress] (Group1 ASC, Group2 ASC, Group3 ASC, Group4 ASC,
         Group5 ASC, Group6 ASC, Group7 ASC, Group8 ASC, Network ASC)
  WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE);

Notes:

I recognize that you plan on using BIGINT for the ID field, but do you really expect to capture more than 4,294,967,295 unique values? If so then just change the field to be BIGINT and you can then even change the seed value to be 0. But otherwise you are better off using INT and starting with the minimum value so that you can make use of the entire range of that datatype.
If desired, you can add one or more NONpersisted Computed Columns to this table to return text representations of the IPAddress.
The Group* fields are arranged purposefully going down, from 8 to 1, in the table so that doing SELECT * will return the fields in the expected order. But the index has them going up, from 1 to 8, as that is how they are filled out.
An example (unfinished) of a computed column to represent the values in text form is:

ALTER TABLE [IPAddress]
  ADD TextAddress AS (
IIF([Group8] IS NULL,
    -- IPv4
    CONCAT(CONVERT(TINYINT, [Group4]), '.', CONVERT(TINYINT, [Group3]), '.',
      CONVERT(TINYINT, [Group2]), '.', CONVERT(TINYINT, [Group1]),
      IIF([Network] IS NOT NULL, CONCAT('/', [Network]), '')),
    -- IPv6
    LOWER(CONCAT(
      CONVERT(VARCHAR(4), [Group8], 2), ':', CONVERT(VARCHAR(4), [Group7], 2), ':',
      CONVERT(VARCHAR(4), [Group6], 2), ':', CONVERT(VARCHAR(4), [Group5], 2), ':',
      CONVERT(VARCHAR(4), [Group4], 2), ':', CONVERT(VARCHAR(4), [Group3], 2), ':',
      CONVERT(VARCHAR(4), [Group2], 2), ':', CONVERT(VARCHAR(4), [Group1], 2),
      IIF([Network] IS NOT NULL, CONCAT('/', [Network]), '')
     ))
   ) -- end of IIF
);

Test:

INSERT INTO IPAddress VALUES (127, 0, 0, 0, 4, 22, 222, 63, NULL); -- IPv6
INSERT INTO IPAddress VALUES (27, 10, 1234, 0, 45673, 200, 1, 6363, 48); -- IPv6
INSERT INTO IPAddress VALUES (NULL, NULL, NULL, NULL, 192, 168, 2, 63, NULL); -- v4
INSERT INTO IPAddress VALUES (NULL, NULL, NULL, NULL, 192, 168, 137, 29, 16); -- v4

SELECT [IPAddressID], [Group8], [Group1], [Network], [TextAddress]
FROM IPAddress ORDER BY [IPAddressID];

Result:

IPAddressID   Group8   Group1   Network  TextAddress
-----------   ------   ------   -------  ---------------------
-2147483646   0x007F   0x003F   NULL     007f:0000:0000:0000:0004:0016:00de:003f
-2147483645   0x001B   0x18DB   48       001b:000a:04d2:0000:b269:00c8:0001:18db/48
-2147483644   NULL     0x003F   NULL     192.168.2.63
-2147483643   NULL     0x001D   16       192.168.137.29/16


mysqld-multi with first DB as Slave and second DB as Master

I address something like this back on May 31, 2011 : MySQL connection doesn't work anymore

Essentailly, you must make sure each mysql instance has a unique socket file, datadir, slow log, error log, and its fair share of memory.

You could look into setting up a separate mysqld service for a specific port : Running multiple instances on the same host

You should make sure db2 has a unique server_id. Furthermore, if the Slave is to be a Master also, make sure you have log-slave-updates in my.cnf for this Slave.


Grouping rows by looking at two columns without considering the order and summing each separately

It's not pretty but it works.

SELECT * INTO YourTable
FROM
(
    SELECT 'A' AS [SOURCE],'B' AS DEST, 1 AS VALUE
    UNION ALL
    SELECT 'B','A',2
    UNION ALL
    SELECT 'A','B',3
    UNION ALL
    SELECT 'C','D',5
    UNION ALL
    SELECT 'D','C',6
) A;

WITH CTE
AS
(
    SELECT  CASE
                WHEN [Source] < Dest THEN [Source]
                ELSE Dest
            END col1,
            CASE
                WHEN [Source] < Dest THEN [Dest]
                ELSE [Source]
            END col2,
            SUM(Value) AS [SUM(Total)]
    FROM YourTable
    GROUP BY
        CASE WHEN [Source] < Dest THEN [Source] ELSE Dest END,
        CASE WHEN [Source] < Dest THEN [Dest] ELSE [Source] END
)

SELECT  A.col1,
        A.col2,
        SUM(B.VALUE) AS [SUM(SOURCE)],
        [SUM(Total)]-SUM(B.VALUE) AS [SUM(DEST)]
FROM CTE A
INNER JOIN YourTable B
    ON A.[col1] = B.[SOURCE]
    AND A.col2 = B.DEST
GROUP BY A.col1,A.col2,[SUM(Total)]

DROP TABLE YourTable

Results:

col1 col2 SUM(SOURCE) SUM(DEST)
---- ---- ----------- -----------
A    B    4           2
C    D    5           6


How do I get the MySQL binary log coordinates from the binary files?

Hopefully, you did not start mysqld on the slave yet.

It just so happens that a binlog position is also the binlog size at the end of every binlog event. In your case, you can use the last binlog the slave sees, which I can assume is mysql-bin.000006. Get the filesize and use that as the position.

Therefore, on the Slave you would run

STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='...',
    MASTER_PORT=3306,
    MASTER_USER='repluser',
    MASTER_PASSWORD='replpassword',
    MASTER_LOG_FILE='mysql-bin.00006',
    MASTER_LOG_POS=720164815;
START SLAVE;

and that should do it !!!

I have discussed the binlog position being the filesize before

Nov 30, 2012 : How can you monitor if MySQL binlog files get corrupted?
Oct 20, 2011 : How to handle the My SQL DB Master to Master replication when there was a disk space outage

You also asked

  what happens if you set the MASTER_LOG_POS to an earlier point in time?

Yes, it will replay those binlog event and cause issues.


Postgres scalability - What is the impact of connection pooling?

I think you see a false dichotomy that does not exist.

It can be useful to have connection pooling in place even if you expect a 1:1 mapping of clients to back-ends. If your connections are long-lived, you won't benefit from reducing backend setup/teardown overhead, as it's small and amortized across a long period. A pool like PgBouncer may remain useful for other reasons:

Block until a connection is available rather than return an immediate max_connections exceeded error;
You can switch the pool target server if you fail-over to a standby without having to reconfigure applications;
You can limit application database workers to lower than max_connections, so you can still make reporting and maintenance connections as a non-superuser.

Additionally, if suitable for your application you can use transaction-level pooling to greatly increase the number of clients that can be served by your server.

I would not try to keep a strict 1:1 mapping from Apache workers to PostgreSQL workers, personally. If you've got (say) 16 cores and good I/O on your PostgreSQL box you might want something like 16-20 active PostgreSQL workers for optimal performance. You're almost certain to want more Apache workers than that, since they'll be kept busy by things like:

persistent HTTP connections from idle clients;
Unresponsive or very slow clients;
Intentional DoS connections;
Network interruptions between client and server; etc

If possible, consider a transaction-pooling design with short-lived transactions instead.


You did not specify your platform but it should be relatively easy to do that:

DELETE FROM mytable
 WHERE Status = 'R'
    OR Status = 'X'
    OR
    (
       Status = 'P'
       AND ID IN (SELECT ID FROM mytable WHERE Status = 'X')
    );

Here are some links you may find helpful:

DELETE

WHERE

IN

AND, OR, NOT


Selecting a range of users active in the past month, but not in the past week

You can write a CTE[1] to compute a date interval, take a look:

WITH last_month_minus_thisweek AS(
     SELECT GENERATE_SERIES(CURRENT_DATE - '1 Month'::interval, CURRENT_DATE - '1 week'::INTERVAL, '1 day') AS day
), date_list AS (
     SELECT MIN(day) AS start_date, MAX(day) as end_date FROM last_month_minus_thisweek
)

SELECT * FROM date_list;

To use with your query, put date_list on there FROM clause when adjust your filter, eg:

WITH last_month_minus_thisweek AS(
     SELECT GENERATE_SERIES(CURRENT_DATE - '1 Month'::interval, CURRENT_DATE - '1 week'::INTERVAL, '1 day') AS day
), date_list AS (
     SELECT MIN(day) AS start_date, MAX(day) as end_date FROM last_month_minus_thisweek
)

SELECT  u.id, u.given_name, u.family_name, u.email
FROM users u, last_month_minus_thisweek
JOIN meetings m ON (u.id != m.user_id)
AND m.created_at BETWEEN %s and %s
AND u.state ='active'
AND u.id in (SELECT user_id
             from meetings
           where created_at BETWEEN date_list.start_date and date_list.end_date)
GROUP BY u.id, u.given_name, u.family_name, u.email;

I Hope it helps.

[1] https://www.postgresql.org/docs/current/static/queries-with.html


Go to Settings -> Keyboard and change the shortcut for the action


SQL Server 2008: sequence which restarts daily

One potential method of doing this would be (see the better method, at the end):

USE tempdb;

CREATE TABLE [dbo].[tblIDs]
(
    IDName nvarchar(255) NOT NULL
    , LastID int NULL,
    CONSTRAINT [PK_tblIDs] PRIMARY KEY CLUSTERED
    (
        [IDName] ASC
    ) WITH
    (
        PAD_INDEX = OFF
        , STATISTICS_NORECOMPUTE = OFF
        , IGNORE_DUP_KEY = OFF
        , ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
        , FILLFACTOR = 100
    )
);
GO

CREATE PROCEDURE [dbo].[GetNextID](
    @IDName nvarchar(255)
)
AS
BEGIN
    /*
        Description:    Increments and returns the LastID value from
                                tblIDs for a given IDName
        Author:         Max Vernon / Mike Defehr
        Date:           2012-07-19
    */

    DECLARE @Retry int;
    DECLARE @EN int, @ES int, @ET int;
    SET @Retry = 5;
    DECLARE @NewID int;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET NOCOUNT ON;
    WHILE @Retry > 0
    BEGIN
        BEGIN TRY
            UPDATE dbo.tblIDs
            SET @NewID = LastID = LastID + 1
            WHERE IDName = @IDName;

            IF @NewID IS NULL
            BEGIN
                SET @NewID = 1;
                INSERT INTO tblIDs (IDName, LastID) VALUES (@IDName, @NewID);
            END
            SET @Retry = -2; /* no need to retry since the operation completed */
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205) /* DEADLOCK */
                SET @Retry = @Retry - 1;
            ELSE
                BEGIN
                SET @Retry = -1;
                SET @EN = ERROR_NUMBER();
                SET @ES = ERROR_SEVERITY();
                SET @ET = ERROR_STATE()
                RAISERROR (@EN,@ES,@ET);
                END
        END CATCH
    END
    IF @Retry = 0 /* must have deadlock'd 5 times. */
    BEGIN
        SET @EN = 1205;
        SET @ES = 13;
        SET @ET = 1
        RAISERROR (@EN,@ES,@ET);
    END
    ELSE
        SELECT @NewID AS NewID;
END
GO

CREATE TABLE dbo.HumanReadableSequence
(
    HumanReadableSequence_ID VARCHAR(20) NOT NULL
        CONSTRAINT PK_HumanReadableSequence
        PRIMARY KEY CLUSTERED
    , SomeData VARCHAR(386) NOT NULL
);

GO
CREATE PROCEDURE dbo.HumanReadableSequence_Insert
(
    @SomeData VARCHAR(386)
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @NextID INT;
    DECLARE @Today VARCHAR(20);
    DECLARE @t TABLE
    (
        ID INT NOT NULL
    );
    SET @Today = (CONVERT(VARCHAR(20), GETDATE(), 101))

    INSERT INTO @t (ID)
    EXEC dbo.GetNextID @IDName = @Today;

    INSERT INTO dbo.HumanReadableSequence (HumanReadableSequence_ID, SomeData)
    SELECT (@Today + '_' + CONVERT(VARCHAR(20), ID, 0))
        , @SomeData
    FROM @t;
END
GO

EXEC dbo.HumanReadableSequence_Insert N'this is a test';

SELECT *
FROM dbo.HumanReadableSequence;

The results:

Having said all that, I would ask why not simply maintain two separate columns that could be concatenated in the presentation layer:

CREATE TABLE dbo.HumanReadableSequence
(
    CreateDate DATETIME NOT NULL
        CONSTRAINT DF_HumanReadableSequence_CreateDate
        DEFAULT (DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())))
    , HumanReadableSequence_ID INT NOT NULL
    , SomeData VARCHAR(386) NOT NULL
    , CONSTRAINT PK_HumanReadableSequence
        PRIMARY KEY CLUSTERED
        (CreateDate, HumanReadableSequence_ID)
);

DECLARE @ID INT;
DECLARE @t TABLE
(
    ID INT NOT NULL
);
DECLARE @Today VARCHAR(20);
SET @Today = (CONVERT(VARCHAR(20), GETDATE(), 101))

INSERT INTO @t (ID)
EXEC dbo.GetNextID @IDName = @Today;

SELECT @ID = t.ID
FROM @t t;

INSERT INTO dbo.HumanReadableSequence (SomeData, HumanReadableSequence_ID)
VALUES ('This is a test', @ID);

SELECT HumanReadableSequenceValue =
        REPLACE(CONVERT(VARCHAR(20), hrs.CreateDate, 101)
        + '_'
        + CONVERT(VARCHAR(20), hrs.HumanReadableSequence_ID, 0), '/', '-')
    , SomeData
FROM dbo.HumanReadableSequence hrs;

The results:

The above method is much more capable of scaling well, and offers flexibility in the presentation of the human readable sequence number.


Changing InnoDB data directory prevents MySQL service from starting

You mention that you are running on Windows. The path to Windows directories are not the same as UNIX ( you cannot just use /ibdata). Instead try setting it like:

innodb_data_home_dir="C:\path\to\ibdata"


Entity set that has a relationship with itself

There's nothing in the ERD to constrain the model either way. Without suitable constraints, a child could be recorded as being born before his/her parent, a person could be their own child or parent, and cycles over any number of generations could exist.

For example, assuming the ERD is implemented as:

Person (personId PK, dateOfBirth, name, gender)
ChildOf (personId PK/FK, parentId FK)

we could record the following sets of rows:

Person (1, 2000-11-01, John, Male)   ChildOf (1, 2)
Person (2, 1970-05-23, Jane, Female) ChildOf (2, 4)
Person (3, 1985-01-11, Jack, Male)   ChildOf (3, 3)
Person (4, 1950-11-01, Joan, Female) ChildOf (4, 1)

There's nothing in the ERD to prevent any constraints either. As it stands, I would have to answer "yes" or "it depends on what unstated constraints you assume or implement" to both questions. If such constraints were required, I would recommend stating them explicitly.


The placement of database files in a shared folder

The key differentiator between the recommended locations (e.g. local storage, a SAN, or an iSCSI-based network) and a network share is quite simply, redundancy.  All of the recommended approaches provide an option for redundant paths for I/O to take to persistent storage.

For instance, you can RAID local storage, providing redundancy if any disk fails.  With either the SAN or iSCSI-based network storage approaches, these technologies use Multipath Input/Output (MPIO) drivers, providing redundancy to the storage.

A network drive, in contrast, does not employ or allow for any redundant I/O paths.  If a Network Interface Controller (NIC) fails on either end, the share likely disappears.  Even if you have multiple NICs, there will still be a brief outage as a different IP address will now host that share, so any data sent to the old/failed IP will timeout and disappear.  Basically a network share wasn't designed with this level of redundancy, and a loss of data mid stream may corrupt your database (or worse just get lost without a trace).  The whole point of a database is to reliably store data and a network share brings that whole reliable aspect into question.


MySQL - When Command column in Processlist shows "Prepare" or "execute"

The command status you refer to is pretty self explanatory:

1) The thread is preparing a statement, for example when the following:

PREPARE STATEMENT my_stmt FROM 'SELECT * FROM countries WHERE id = ?';

2) The thread ist executing a formerly prepared statement:

EXECUTE my_stmt;

You can also refer to the documentation: Thread Command Values


How can i improve MySQL Delete performance?

When You SELECT data - You are work with InnoDB memory cache, You not write to disk, You are not log operations.

It is normal when same select thousands time faster then DELETE.

When You run DELETE command, MySQL must:

do the same as SELECT - found rows
log operation (write into redo-log files)
mark rows as deleted
lock deleted rows - wait if any other operations use them
update indexes

2 sec it is not significant huge time, but if it make collisions for other operations - You can to split operation for chunks


Check if contiguous date interval exists

First, we combine intervals that overlap to find all the contiguous "islands" of the intervals:

with c as
  ( select *, max(end_date) over (order by start_date
                                  rows between unbounded preceding
                                           and 1 preceding)
                as previous_max
    from my_table
  )
select start_date,
       coalesce(lead(previous_max) over (order by start_date),
                (select max(end_date) from my_table)
               ) as end_date
from c
where previous_max < start_date
   or previous_max is null ;

After that, it's easy to check if a given interval in completely surrounded by one of the found contiguous islands.

   with c as
  ( select *, max(end_date) over (order by start_date
                                  rows between unbounded preceding
                                           and 1 preceding)
                as previous_max
    from my_table
  ) ,
cont as
  ( select start_date,
           coalesce(lead(previous_max) over (order by start_date),
                    (select max(end_date) from my_table)
                   ) as end_date
    from c
    where previous_max < start_date
       or previous_max is null
   )
select *
from cont
where tsrange(start_date, end_date)
       @>                             -- contains
      tsrange('2015-04-02 22:10:00', '2015-04-02 22:30:00')
limit 1 ;

Test at SQLfiddle


What is the best method to add error handling in SQL 2005 stored procs?

Alex Kuznetsov has a great chapter in his book Defensive Database Programming (Chapter 8) that covers T-SQL TRY...CATCH, T-SQL transactions & SET XACT_ABORT settings, and using client-side error handling. It will help you a lot in deciding which of the options makes the most sense for what you need to accomplish.

It is available for free at this site. I am in no way affiliated with the company, but I do own the hard copy version of that book.

There are a lot of little details on this subject that are explained very well by Alex.

Per Nick's request... (but not all of this is in the chapter)

In terms of scaling, you need to be brutally honest about which activities need to be in the db code and which should be in the app. Ever notice how fast-executing code tends to come back to designing for a single concern per method?

The easiest way to communicate would be custom error codes (> 50,000). It's also pretty fast. It does mean you'd have to keep the db code and app code in sync. With a custom error code, you can also return useful information in the error message string. Because you have an error code strictly for that situation, you can write a parser in the app code tailored to the error's data format.

Also, which error conditions need retry logic in the database? If you want to retry after X seconds, then you're better off handling that in the app code so the transaction doesn't block as much. If you are only re-submitting a DML operation right away, repeating it in the SP could be more efficient. Keep in mind, though, that you'll have to possibly duplicate code or add a layer of SPs to accomplish a retry.

Really, that's currently the biggest pain with TRY...CATCH logic in SQL Server at the moment. It can be done, but it's a bit of an oaf. Look for some improvements coming to this in SQL Server 2012, especially re-throwing system exceptions (preserving the original error number). Also, there's FORMATMESSAGE, which adds some flexibility in constructing error messages, especially for logging purposes.


Because that is how it was designed and implemented. See here

  If BC was not specified, and if the year field was two digits in length, then adjust the year to four digits. If the field is less than 70, then add 2000, otherwise add 1900.

(although two digits should probably be "one or two")

People often use the last digits of a recent past or near future year to represent the year, dropping the 19 or the 20.  This heuristic was designed to accommodate that raw fact.

That the year 0 doesn't exist has nothing to do with it.  The years 1, 2, and 69 do exist, and yet the heuristic applies to that as well.


How do i find out the most active database?

Capture this:

Select  instance_name As Database_Name,
        cntr_value As TranSec
From    sys.dm_os_performance_counters where counter_name = 'Transactions/sec'
And     instance_name <> '_Total'
Order By instance_name

Then run it again at the end of the day. Subtract one set of values from the other. That will give you the most transactions.

You can look for a similar counter for logins/sec but it may be misleading depending on how the app connects to the database and if it keeps the connection open or not.

Another good way is by the disk IO but this can be misleading too as a lot can show up and be caused by backups/maintenance.

Select  Db_Name(mf.database_id) As Database_Name,
        Sum(a.num_of_reads) As Reads,
        Sum(a.num_of_bytes_read) As Bytes_Read,
        Sum(a.num_of_writes) As Writes,
        Sum(a.num_of_bytes_written) As Bytes_Written
From    sys.master_files mf
Cross Apply sys.dm_io_virtual_file_stats(mf.database_id, mf.file_id) a
Group By Db_Name(mf.database_id)
Order By 1

Again you'd run it in the morning and again in the afternoon and subtract to compare.


Is there any way to graceful restart mysqld?

Exact duplicate?

Is there a graceful or safe restart for mysql like for apache httpd?

Basically, it involves some SQL scripting along with whatever operating system scripts or controls you are using to do the server restart.
Just be mindful of how the locking interacts with transactions: http://dev.mysql.com/doc/refman/5.6/en/lock-tables-and-transactions.html


have you looked at having Historic Players and Historic Team collections?

Then if you wanted stats about the players at the time, you'd probably want to copy out the current players on the team in your historic games documents.  You'd probably want to store that as an array in the Historic Games table as the current players list might be different than the players from last week/year.

Keep the position of the players in the document in mind so you can end up using proper indexes.

historic players
_id: jfoi23j8xx
PlayerID:Bo Jackson
weekinfo<array or tags>
[week 1- hits:30, misses:3, bunts:7, TeamID: LA Raiders],
[week2 - hits:17, misses:2, bunts:3, TeamID: Kansas City Royals]

historic teams:
_id:10u40o321js
TeamID: LA Raiders
Weekinfo<array or tags>
[week 1 - consolidated hits: 73, misses: 12],
[week 2 - consolidated hits:65, misses: 16]

Something like that might be a good start.  Does it meet your requirements?  You might want to have your app handle the aggregation but that's up to you.


What steps are required to setup PostgreSQL and create a db in Windows?

In the psql client, you can only run SQL statements and psql commands (which start with a backslash).
So, to create a database, you would have to run
CREATE DATABASE demo;

The semicolon in the end is required to tell psql that the statement is complete.

The prompt you see:
postgres-#

shows that you didn't do that and psql is waiting for a continuation line. You see that from the -.
To delete the input buffer and get back to the prompt
postgres=#

simply press Ctrl+C. Then you can enter the CREATE DATABASE statement.

createdb is not an SQL statement, but an executable (program) that you would call from the shell (or from cmd.exe on Windows).
So the alternative is not to start psql, but type
createdb -U postgres demo


You don't define a column USERNAME in your MemberShip table

CREATE TABLE Membership
(
  MembershipNo INT Primary Key,
  DatePaid date NOT NULL,
  DateTo date,
  AnnualFee INT NOT NULL,
  username varchar(20) not null, -- << you are missing this
  foreign key (USERNAME) references Person(USERNAME)
);

alternatively directly behind the column:

CREATE TABLE Membership
(
  MembershipNo INT Primary Key,
  DatePaid date NOT NULL,
  DateTo date,
  AnnualFee INT NOT NULL,
  username varchar(20) not null references Person(USERNAME)
);


Multi Session Mirroring using 1 witness server, cannot find certificate

The answer is in the error - you will need to make sure that both servers i.e. the new db server and the witness can communicate using the certificate in question.

There will be a certificate for each individual server and must exist on witness as well. So witness cert on the p/m servers and vice-versa


A way to take a backup of SQL Server 2000 database on a different domain network

The example of issuing xp_cmdshell to execute a 'NET USE' and then issuing a BACKUP DATABASE won't work because the 'NET USE' is executed outside of the known environment of SQL Server.
The xp_cmdshell starts a separate shell|cmd session, executes the NET USE then closes the shell|cmd session and returns to SQL. So the cmd session that the net use/mapping was applied to no longer exits when the BACKUP DATABASE is executed.

You might try writing a .bat/.cmd file that contains:
NET USE
SQLCMD -S -E -Q" Backup database....." -o backup.out
OR SQLCMD -S -E -i backup.sql -o backup.out

and then call backup.bat via xp_cmdshell "backup.bat"

But, I don't think that will work either.

The only way to make a mapped drive available to SQL is to have the NET USE execute when the Server/Host starts up, BEFORE the SQL Server service starts, and, the NET USE will need to be initiated under the same credentials as the SQL Server is using.

Maybe set the SQL Service to MANUALLY start and create a Scheduled Task that runs at startup that first maps the NET USE then does a NET START for SQL Server?

But I'm doubtful on that too...

Upon further investigation, it is possible to backup to a UNC path with SQL 2000, but, there are prerequisites:
https://support.microsoft.com/en-us/help/555128

Backups to a network folder require the following prerequisites:
1)  The SQL Server service must be running under a domain-level account.
2)  The SQL Server service account must have FULL CONTROL rights to the file system folder and to the share.
3)  The remote share should only be accessed via UNC name.  Mapped drives may not be consistently visible to the SQL Service.

After that, you can set vars in your SQL script and pass them to the backup command:

DECLARE @FileName AS VARCHAR(100)
DECLARE @Date AS CHAR(8)

SET @Date = CONVERT(CHAR(8), GETDATE(), 112)

SET @FileName = '\BackupHost\BACKUP\dbname' +  @Date + '.bak'

BACKUP DATABASE dbname TO DISK = @FileName


I managed to solve this problem using this query:
select to_jsonb(array_agg(jsonb(
                COALESCE(
                        jsonb_set(
                                imageElements,
                                '{id}',
                                to_jsonb(uuid_generate_v4()),
                                true
                            ), imageElements
                    )
            )))
from  jsonb_array_elements(
    (
        select entitiesArray->'content' -> 'images' from (
                select jsonb_array_elements(column) as entitiesArray from table
        ) as column
            where entitiesArray->> 'type' = 'imageGalleryModule'
    )
    ) as imageElements;


How to store GPS coordinate and search places in a radius from a NoSQL DBMS (like DynamoDB)

The classic answer is to query the database for the set of points within the 100 mile BOX and then remove the subset of points that are outside the 100 mile CIRCLE in your application layer using your programming language of choice (not SQL).

I've done this with data in Amazon SimpleDB efficiently, and you could do the same in DynamoDB with a couple "index tables" one using lat and another using lon as hash keys...


SQL Server 2014 Triggers and where are they created

Triggers will be in the same Schema as the Table that they are created on. This is either handled automatically if the Schema is not specified in the CREATE TRIGGER statement, or it is enforced if it is specified but not the same as the Schema of the Table. The MSDN documentation for CREATE TRIGGER states:

  DML triggers are scoped to the schema of the table or view on which they are created.

And a simple test confirms the enforcement of this rule:

CREATE SCHEMA [TriggerTest];
GO

CREATE TABLE dbo.TriggerSchemaTest (Col1 INT);
GO

CREATE TRIGGER [TriggerTest].[DifferentSchemaThanTable]
ON [dbo].[TriggerSchemaTest]
AFTER INSERT
AS
BEGIN
  DECLARE @DoNothing INT;
END;
GO

produces the following error:

  Msg 2103, Level 15, State 1, Procedure DifferentSchemaThanTable, Line 6
  Cannot create trigger 'TriggerTest.DifferentSchemaThanTable' because its schema is different from the schema of the target table or view.

The syntax for dropping a Trigger, according to the SQL Server 2012 & 2014 MSDN page for DROP TRIGGER, is (essentially):

DROP TRIGGER [schema_name.]trigger_name;

So, if you want to know what Schema a particular Trigger (and its parent Table) is in, use the following query:

SELECT ss.[name] AS [TableAndTriggerSchemaName],
       so.[name] AS [TriggerName],
       st.[name] AS [TableName]
FROM   sys.objects so
INNER JOIN sys.schemas ss
        ON ss.[schema_id] = so.[schema_id]
INNER JOIN sys.tables st
        ON st.[object_id] = so.[parent_object_id]
WHERE  so.[name] = N'trgr_hashval_mara';

Also:

This is one reason (readability) why it is best to always specify the Schema name when creating Schema-bound objects (rather than relying upon default behavior). Hence you should be using: CREATE TRIGGER land.trgr_hashval_mara ON land.mara...
If that is your entire, actual Trigger code, there is a huge problem: it is updating all rows of the table for each and every INSERT and UPDATE statement. Is that really what is desired? You need to be joining to the INSERTED pseudo-table so that you only update rows that are actually changing.
You might want to add SET NOCOUNT ON; at the top of each Trigger.


Why are there any Innodb_buffer_pool_reads for a small db?

These are counts since the server was started.

The pages must have been read at some stage to get into the buffer pool.

As a test, try a mysqldump --all-databases --skip-locks to ensure every row is read. And then look at the Innodb_buffer_pool_reads. Then see if this value increases over the next few days.


Limit MySQL server login attempts without fail2ban

No, phpMyAdmin doesn't have any built-in protection directly against brute force attacks. phpMyAdmin can log failed connection attempts to syslog, a feature which was added specifically so that fail2ban could be easily used for this protection (rather than re-developing the wheel in the application itself). You may be able to work something out with MySQL's resource limits, but those are really designed for restricting valid authenticated users, not stopping brute force attacks. As an alternative, if you have a firewall front end, you may be able to enact some protections there if you really wish to not use fail2ban, but the officially sanctioned means is indeed using fail2ban.


To get rid of heavy archive logs in PostgreSQL 9.1

If you don't need to analyze again that log. You can reduce the things that database will be log.
Some of them are:

log_statement: Controls which SQL statements are logged. You don't need to log all statement. It can be switched to none.
client_min_messages: can be set to ERROR
log_min_messages: can be set to ERROR
log_min_duration_statement: can be set to a -1 - this means disable it.

All of them are store in file configuration postgresql.conf

You can read more document here.

Hopefully this answer will help you.


This sounds like a horrible idea. You're much better off

doing this in the app (where you can make better use of asyc with v8).
using PostGIS tiger geocoder

On the flip side, I don't think you can. PLv8 can load modules if they're in the database. But, I don't think it has network access.


You are explicitly setting the role to noinherit so you will need to use set rolecp before your select to use the permissions from role cp (but I'm guessing you probably just want to inherit)

From the docs:

  INHERIT
  NOINHERIT

  These clauses determine whether a role "inherits"
  the privileges of roles it is a member of. A role with the INHERIT
  attribute can automatically use whatever database privileges have been
  granted to all roles it is directly or indirectly a member of. Without
  INHERIT, membership in another role only grants the ability to SET
  ROLE to that other role; the privileges of the other role are only
  available after having done so. If not specified, INHERIT is the
  default.


It removes them from the GRANT / CREATE statements as per the doc you linked and also per this documentation: Importing and Exporting Databases

Only the global read lock is released when the backups are started per the documentation you linked:

When all threads have started their transactions, the instance is
locked for backup and the global read lock is released.

In other words, the data is read safe but likely still holds other locks preventing the data or schema from being changed during the backup process (aka blocking transactions).
This website goes into good detail on global locks and specifically read locks in MySQL which is relevant to this question: Deep Understanding of MySQL Global Lock and Table Lock
Specifically this part mentions what type of statements are locked:

A global lock is to lock the entire database instance. MySQL provides
a way to add a global read lock. The command isFlush tables with read
lock (FTWRL)。
When you need to make the whole library read-only, you can use this
command, and then the following statements of other threads will be
blocked: data update statements (data addition and deletion), data
definition statements (including table building, table structure
modification, etc.) and submission statements for updating class
transactions.

So yes, technically the data is a snapshot in time because no other transactions are allowed to change the data or schema during the backups. The amount of memory needed is going to be dependent on the amount of data being backed up.


The concept of a "database" in SQL Server is much closer to a schema in DB2.  An entire MS SQL database engine is much closer to a DB2 database:

master database <======> SYSIBM/SYSCAT schema
msdb   database <======> SYSPROC       schema

It's not a perfect comparison; there are a number of differences:

DB2 does not have an equivalent to the model database.
tempdb doesn't really map to a schema; its purpose is handled by system temporary tablespace(s) like TEMPSPACE1.
Each database in MS SQL has separate log files, where DB2 uses a single set of transaction logs for the entire database

Therefore, technically the closest equivalent to MS SQL's USE statement is SET SCHEMA.  Issuing the SET SCHEMA x statement tells DB2 where to look for unqualified objects.

However, it's not a requirement to issue the SET SCHEMA statement if you fully qualify your object names.  The following statements:

set schema x;
select c1, c2 from mytable;

are equivalent to:

select c1, c2 from x.mytable

Using set schema simply allows you to use unqualified objects.


From the docs, mysql.db is the table that handles database-specific GRANTS. That is to say, if you explicitly indicated a database in your GRANT command, it would show up in this table:

GRANT SELECT, INSERT ON foo.* TO `bar`@`localhost`;

So the user bar@localhost would have SELECT and INSERT marked as 'Y' in the mysql.db table.

To remove entries from this table, likewise you need to specify the database:

REVOKE SELECT, INSERT ON foo.* FROM `bar`@`localhost`;

Issuing a REVOKE INSERT ON *.* statement (all databases) will not affect this table.

Also, a DROP USER statement will clean up all the entries in the various mysql.* tables that deal with user authentication, such as mysql.db.


When does it make sense to duplicate data for querying

My suggestion is to design the system the way it makes logical sense, and deal with problematic joins when they prove to cause performance issues. Don't prematurely optimize just because you think joins are costly.

Joins that are performance problems are usually because the queries are horrible (which could be requirements or bad query logic), there are missing indexes, or both.


It's a good practice always restore controlfile before restore the database?

It depends entirely on the nature of the failure and the need of recovery.  The ends-and-outs are too complex to put in a single answer here, but the Database Backup and Recovery User Guide has a whole section of scenarios on "Diagnosing and Responding to Failures". You would do well to study it.  And build yourself a private VM under VirtualBox, to practice on.

Recovering a database is the Prime Directive of any DBA.


How to recover data from corrupted innoDB table from a crashed mySQL server?

You could try to use MySQL's CHECK TABLE and REPAIR TABLE functions (but make sure you grab a copy of the table first):

mysql -uroot -p database1
CHECK TABLE table1

If that doesn't work, try using:

CHECK TABLE table1 EXTENDED

According to the CHECK TABLE documentation:

  EXTENDED is to be used only after you have run a normal check but still get strange errors from a table when MySQL tries to update a row or find a row by key. This is very unlikely if a normal check has succeeded.

If any errors are reported by either of the above, try:

REPAIR TABLE table1

If that doesn't work, you can try:

REPAIR TABLE table1 EXTENDED

Finally, if that still doesn't work, you may be able to use myisamchk while MySQL is offline - according to the documentation:

  REPAIR TABLE does not implement all the options of myisamchk


i usually say to people don't save data with any delimiter

CREATE TABLE [test] (JOBNUMBR VARCHAR(15))
GO

INSERT INTO test SELECT '57299_87961_003' UNION SELECT '66515_88567_OOO' UNION SELECT '58OO7_88695_OO1'
GO

SELECT JOBNUMBR
,Reverse(ParseName(Replace(Reverse(JOBNUMBR), '_', '.'), 1)) As [no1]
,Reverse(ParseName(Replace(Reverse(JOBNUMBR), '_', '.'), 2)) As [no2]
,Reverse(ParseName(Replace(Reverse(JOBNUMBR), '_', '.'), 3)) As [no3]
FROM (Select newid() AS [ID], JOBNUMBR from test) As [t]
GO

JOBNUMBR        | no1   | no2   | no3
:-------------- | :---- | :---- | :--
57299_87961_003 | 57299 | 87961 | 003
58OO7_88695_OO1 | 58OO7 | 88695 | OO1
66515_88567_OOO | 66515 | 88567 | OOO

db<>fiddle here


MySQL Grouping Data Together From Same User

If I'm following, you want to have one panel per user the current user has exchanged messages with, and show all messages from that person in that panel.

To get all people the current user has received messages from (broken into multiple lines for readability - you can put it in one line in your code):

SELECT SenderID, ProfileName
  FROM ve_messages m JOIN ve_users u ON m.SenderID=u.UserID
 WHERE m.RecieverID= '$loginid_session'
 GROUP BY UserId, ProfileName
;

Loop through these results to create your panels.

You'll need a second loop within each panel to show each message, using the results of this query:

SELECT *
  FROM ve_messages m JOIN ve_users u ON m.SenderID=u.UserID
 WHERE m.RecieverID= '$loginid_session'
   AND m.SenderID = {$inbox['SenderID']}
 ORDER BY SentOn DESC
;

You may actually want to change the name of the $inbox associative array, as it now just has the ID and profile names of senders.

The ORDER BY assumes that you'd want the most recent messages at the top; change DESC to ASC to flip that.


Best practices for configuring the Drive/Filegroup setup for Microsoft SQL 2008

I'm going to assume that, as TomTom went through in detail, some of your hardware acronyms are incorrect regarding your actual server (which you apparently already have).

I'm also assuming that your reference to a "75GB database" indicates you have one database of 75GB that's your primary DB.  This should have only one log file.

Assuming you have 8 disks on modern hardware, one generalized maximum performance setup would be:
2 drives in RAID1 (mirroring) for OS
4 drives in RAID5 for all data files and most log files other than the primary DB's log file.
2 drives in RAID1 (mirroring) for your primary DB's log file.

RAID5 is surprisingly fast on modern hardware, and 4 disks in RAID5 gives 3 disks worth of storage.

The primary DB's log file is by itself so that it really is sequential access - it should be the only file on that entire raidset.  If you had many log files, the drive heads would have to jump from one to the other, which is not sequential.

On reading the additional information in the updated question: a 72GB SAS drive clearly cannot hold a 75GB database even without accounting for growth.  Either a 3 or 4 drive RAID5 or a 4 drive RAID 10 is the bare minimum, given the new hardware information.  For any one specific case, try a few configurations and benchmark them - but with 64GB of RAM on a 75GB database, I'd expect almost the entire database to be in buffer, rendering disk performance much less important; write performance for the transaction log will be one of the first things to watch.


Is DISTINCTROW in MS-Access the same as selecting all the fields and doing a group by on all of them?

Taken from the MSDN page:

  Omits data based on entire duplicate records, not just duplicate fields. For example, you could create a query that joins the Customers and Orders tables on the CustomerID field. The Customers table contains no duplicate CustomerID fields, but the Orders table does because each customer can have many orders. The following SQL statement shows how you can use DISTINCTROW to produce a list of companies that have at least one order but without any details about those orders:

SELECT DISTINCTROW CompanyName
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY CompanyName;

  If you omit DISTINCTROW, this query produces multiple rows for each company that has more than one order.

  DISTINCTROW has an effect only when you select fields from some, but not all, of the tables used in the query. DISTINCTROW is ignored if your query includes only one table, or if you output fields from all tables.

To answer your question, is it the same as a GROUP BY on all columns?  No.

By the way, that was the very first link returned from a search for microsoft access distinctrow site:msdn.microsoft.com


Why a backup created on SQL Server 2016 is significantly larger than one created on SQL Server 2012?

Sometimes your backup device can be bigger in comparison to the previous version for reasons like:

Your transaction log increased, check your log to validate if you have not grown it.
You could have extra features enabled in your new instance that are not in the previous instance. i.e: if you have used Full Text-search in your SQL 2016. One time a customer database backup got exponential growth for this.
Your backup device on the new SQL Server version is not initialized {NON Init | Init} in other words your backup file appends the backup into the same file each time that you performed a backup.

--Media Set Options
  { NOINIT | INIT }
| { NOSKIP | SKIP }
| { NOFORMAT | FORMAT }

More details here.


Slow query log is not storing slow queries

There are two ways to collect general and slow logs:

File (this is the default)
Table (this needs to be specifically enabled)

Based on the info you provided, it looks like MySQL is using the default value for log_output which is "Log output goes to file(s) defined by general_log_file and slow_query_log_file". You can verify this by looking at these files (look in folder /usr/local/mysql/data/ and see what *.log files are there)

If you want the logs to go to database, on a MySQL client when logged in as a privileged user e.g. root:

SET GLOBAL log_output='TABLE';
SET GLOBAL general_log=ON;
SET GLOBAL slow_query_log=ON;
SET GLOBAL long_query_time=0;

The above will send global and slow logs to mysql.general_log and mysql.slow_log tables.

Note that I have set long_query_time to 0 seconds i.e. every query goes to slow_log(that is lot of output into the table). You can change this to another value that suits your use case.

Also, general_log produces a huge amounts of logs so you should only enable it for certain amount of time.


Is unique index better than unique constraint when an index with an operator class is required

For a moment I thought one might be able to use a pre-existing  text_pattern_ops index with the USING INDEX clause when adding a UNIQUE CONSTRAINT. But that fails, because:

ERROR: index "book2_name_like" does not have default sorting behavior

Per documentation:

  The index cannot have expression columns nor be a partial index. Also,
  it must be a b-tree index with default sort ordering. These
  restrictions ensure that the index is equivalent to one that would be
  built by a regular ADD PRIMARY KEY or ADD UNIQUE command.

For instance, a unique index like that would allow a FK constraint referencing it, but perform terribly, because it does not support standard operators.
Per documentation:

  Note that you should also create an index with the default operator
  class if you want queries involving ordinary <, <=, >, or >= comparisons to use an index.

So to answer the question:

If you need a UNIQUE CONSTRAINT (among other reasons: to reference it with a FK), your first variant with constraint and index is the only option. Additionally, the default operator class of the index created by the constraint supports more operations (like sorting in default sort order).

If you don't need any of that go with your second variant because, obviously, just one index is cheaper to maintain: just a UNIQUE text_pattern_ops index.

Differences between index and constraint:

How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

Alternative with COLLATE "C"

Instead of creating two indexes, there is another alternative for xxx_pattern_ops indexes that may be preferable. The documentation:

  The difference from the default operator classes is that the values
  are compared strictly character by character rather than according to
  the locale-specific collation rules. This makes these operator
  classes suitable for use by queries
  involving pattern matching expressions (LIKE or POSIX regular
  expressions) when the database does not use the standard "C" locale.

And:

  The index automatically uses the collation of the underlying column.

You can create the column without collation (using COLLATE "C"). Then the default operator class behaves the same way as text_pattern_ops would - plus the index can be used with all the standard operators.

CREATE TABLE book2 (
   book_id serial PRIMARY KEY,
   book    text NOT NULL COLLATE "C" UNIQUE  -- that's all!
);

Now, LIKE can use the index:

SELECT * FROM book2 WHERE book LIKE 'foo%';

But ILIKE still can't:

SELECT * FROM book2 WHERE book ILIKE 'foo%';

SQL Fiddle.

Consider a trigram index using the additional module pg_trgm for a more general solution.:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL


Postgresql: set default psql client encoding

Is there a way to permanently configure this setting, either in the .pgpass file or anywhere else

Yes there is: it's ~/.psqlrc (or %APPDATA%\postgresql\psqlrc.conf in Windows)

See the manual for details: http://www.postgresql.org/docs/current/static/app-psql.html#AEN88713


PGPool for Postgres load balancing, where to sit it

Direction:
I would definitly recommand this step by step tutorial that may point you to the right direction. This is the one I used when setting up similar config. You can adapt it to your needs eyes wide open.

Multiple PgPool:
The tutorial seems long (and it is: 6 parts!) but the part related to setting up multiple Pgpool is less than half one of 6 parts (see part 5 and 6). Even if I could consider setting up pgpool as a pain, objectively, this part is not the worth (consisting in setting up ip arping and watchdog). If you have already been through the rest for configuring one pgpool and stream or wal replication that will not be a problem.

Decentralized or not:
This may be only based opinion, but if I had to setup pgpool redondancy (I did not), I would prefer placing pgpools along postgresql servers in order to simplify my setup and make the whole thing accessible from outside "as if" it was a simple pgsql server. App and database layers stay good at what they are supposed to do and could stay technically "agnostic" to ascendant/descendant layer.


Crash-safe logged tables require at least two writes for every transaction:

A write to the write-ahead log (WAL) recording the data the server intends to write to the table; then
at some later stage, a write to the real table to apply the change.

The real write can occur after commit, but the WAL write must happen before the commit returns (unless synchronous_commit=off, which has data safety consequences).

To force that to happen PostgreSQL does an fsync(), which flushes the data written to WAL to disk. This operation takes time - on spinning magnetic hard drives it usually takes about one revolution; on an SSD it depends a lot on the amount of other data buffered for writing and on the erase block size etc. Either way, it can take some time.

Unlogged tables do not write to WAL. They just write into shared memory, and as shared memory fills up they write to the real table. No fsync() is done because unlogged tables aren't crash-safe.

To reduce the cost of commit flushes on logged tables you should batch your work into bigger transactions. Where the application allows, use asynchronous commit or enable a small commit delay to allow group commit.


Why is Clustered Index on Primary Key compulsory?

Why Primary Key constraint creates Clustered Index on the PK column by default?

That is what MS-SQL-Server programmers decided the default to be. A good clustered index is one that has unique values (as the Primary Key), is narrow (as most primary keys are or at least should be) and is ever-increasing. So, most of the times, the primary key is a good (or the best) choice for the clustered key (there can be at most one clustered key per table).

  Can we create a table which has a primary key, but NO clustered index?

Yes, you can. By explicitely defining all indices and especially the primary key as non-clustered. If you think that you don't need a clustered key on a table, you can do that and have the primary key as non-clustered. The unique and not null constraints will still be enforced.


Posible to obtain history ledger(balance) with sql query?

To start, and this is just to get you somewhere,

SELECT *,
  jsonb_agg(jsonb_build_object('p',price,'a',amount))
    FILTER (WHERE type='buy')
    OVER (PARTITION BY item ORDER BY id)
    AS cumpurchased
FROM operations;
 id | type | item  | price | amount |             cumpurchased
----+------+-------+-------+--------+---------------------------------------
  1 | buy  | item1 |     2 |     10 | [{"a": 10, "p": 2}]
  2 | buy  | item1 |     3 |      4 | [{"a": 10, "p": 2}, {"a": 4, "p": 3}]
  3 | sell | item1 |     4 |     12 | [{"a": 10, "p": 2}, {"a": 4, "p": 3}]

Here, we serialize the purchases into cumpurchased. This seems simple, now we can blow this up by expanding cumpurchased to the individual transactions.

SELECT id, type, item, price, amount, cumsale, e, eid
FROM (
        SELECT *,
                sum(amount) FILTER (WHERE type='sell') OVER (PARTITION BY id) AS cumsale,
        jsonb_agg(jsonb_build_object('p',price,'a',amount))
                        FILTER (WHERE type='buy')
        OVER (PARTITION BY item ORDER BY id)
        AS cumpurchased
        FROM operations
) AS with_agg
CROSS JOIN LATERAL jsonb_array_elements(cumpurchased)
        WITH ORDINALITY
        AS jsonb(e,eid)
ORDER BY id, eid;
 id | type | item  | price | amount | cumsale |         e         | eid
----+------+-------+-------+--------+---------+-------------------+-----
  1 | buy  | item1 |     2 |     10 |         | {"a": 10, "p": 2} |   1
  2 | buy  | item1 |     3 |      4 |         | {"a": 10, "p": 2} |   1
  2 | buy  | item1 |     3 |      4 |         | {"a": 4, "p": 3}  |   2
  3 | sell | item1 |     4 |     12 |      12 | {"a": 10, "p": 2} |   1
  3 | sell | item1 |     4 |     12 |      12 | {"a": 4, "p": 3}  |   2
(5 rows)

You should be ale to see where I'm going with this...

SELECT id,type,item,e,eid,sum(
        (e->>'a')::int
) OVER (PARTITION BY id ORDER BY eid)
        - coalesce(cumsale,0)
  AS qty_remaining
FROM (
        SELECT *,
                sum(amount) FILTER (WHERE type='sell') OVER (PARTITION BY id) AS cumsale,
        jsonb_agg(jsonb_build_object('p',price,'a',amount))
                        FILTER (WHERE type='buy')
        OVER (PARTITION BY item ORDER BY id)
        AS cumpurchased
        FROM operations
) AS with_agg
CROSS JOIN LATERAL jsonb_array_elements(cumpurchased)
        WITH ORDINALITY
        AS jsonb(e,eid);
 id | type | item  |         e         | eid | qty_remaining
----+------+-------+-------------------+-----+---------------
  1 | buy  | item1 | {"a": 10, "p": 2} |   1 |            10
  2 | buy  | item1 | {"a": 10, "p": 2} |   1 |            10
  2 | buy  | item1 | {"a": 4, "p": 3}  |   2 |            14
  3 | sell | item1 | {"a": 10, "p": 2} |   1 |            -2
  3 | sell | item1 | {"a": 4, "p": 3}  |   2 |             2
(5 rows)

From this point, you need to filter out the -2, we wrap the query again and put that conditional in there. Then we just select from that.

SELECT id, e->>'p' AS price, least(qty_remaining,(e->>'a')::int) AS amount
FROM (
      SELECT id,type,item,e,eid,sum(
        (e->>'a')::int
      ) OVER (PARTITION BY id ORDER BY eid)
        - coalesce(cumsale,0)
        AS qty_remaining
      FROM (
        SELECT *,
          sum(amount) FILTER (WHERE type='sell') OVER (PARTITION BY id) AS cumsale,
          jsonb_agg(jsonb_build_object('p',price,'a',amount))
            FILTER (WHERE type='buy')
            OVER (PARTITION BY item ORDER BY id)
            AS cumpurchased
        FROM operations
      ) AS with_agg
      CROSS JOIN LATERAL jsonb_array_elements(cumpurchased)
        WITH ORDINALITY
        AS jsonb(e,eid)
      ) AS t
WHERE qty_remaining > 0;
 id | price | amount
----+-------+--------
  1 | 2     |     10
  2 | 2     |     10
  2 | 3     |      4
  3 | 3     |      2
(4 rows)


Call Powershell command string from Sql Query Publish profile script

If you turn on SQLCMD mode (from the SSMS Query menu), you can use SQLCMD commands within an SSMS query window. A line beginning with !! will execute the text that follows as an OS command on the client. For example:

!! Powershell -Command "Write-Host ('This message is from PowerShell running on machine ' + $env:COMPUTERNAME)"

The command will similarly be executed when the script is run with the SQLCMD command-line utility with this technique.

Note that the "script string" mentioned in your question will need to be hard-coded in the T-SQL script.


Querying sys.server_principals does not return results for specific user

Tried your query, worked fine for me.. only thing I can think about for you is try adding N to the 'Domain\User' in your case:

SELECT * FROM sys.server_principals WHERE sid = SUSER_SID(N'Domain\User') OR name = N'Domain\User'


Update single field in a document (document.update($set)?)

Had to rewrite like so (profile.picture in one line with dot notation), this works:

Meteor.users.update(
    userId,
    {
        $set: {
            'profile.picture': fileURL
        }
    }
);

Answered by @coagmano on meteor forums


How does one export/import registered servers with passwords?

It will only work on the same machine, not a different one.


Grouping based on the status, then count its product and the total based on its category ID

You need conditional aggregation using sum(case):

select cat_id,
   sum(case when status = 1 then 1 else 0 end) as published,  -- only count status 1
   sum(case when status = 0 then 1 else 0 end) as unpublished,-- only count status 0
   count(*) as totals
from tab
group by cat_id

Edit:

My crystal ball was apparently broken, but now it's fixed :-)

If you need to count each product_id only once you can use this:

select cat_id,
   count(distinct case when status = 1 then product_id end) as published,  -- only count status 1
   count(distinct case when status = 0 then product_id end) as unpublished,-- only count status 0
   count(distinct product_id) as totals
from tab join whatever...
group by cat_id


postgres btree index on large text column

You can use an exclusion constraint instead of a unique constraint:

alter table protein_seq add exclude using hash (sequence with =)

An exclusion constraint is much more general than a unique constraint, and allows you to exclude on things like overlapping ranges.  When you exclude "with =", it reduces to basically the same thing as a unique constraint, except it retains the flexibility to use a hash index.


query to get list of tables that has same columns from information_schema.columns?

something like this:

select distinct c1.table_schema||'.'||c1.table_name
from information_schema.columns c1
where exists (select 1
              from information_schema.columns c2
              where c2.table_name <> c1.table_name
                and c2.column_name = c1.column_name
                and c2.table_schema = c1.table_schema);


DECLARE @myDate AS datetime
SELECT @myDate = min(cast(f.datedue as DATE)) FROM tablef f
SELECT
    sum(case when cast(l.settleddate AS date) < @myDate
             Then 1
             else 0
             end) as BeforeFirstDue
FROM tablel l


SQL Server Agent Notification Email if a Step has an error, but the job is succesfull?

Yes you can achieve this with help of SP:

CREATE PROCEDURE TestEmailOnFail
AS
BEGIN
    BEGIN TRY
        /*
            Perform some action that might fail
        */
        SELECT 0/0; --THIS WILL FAIL
    END TRY
    BEGIN CATCH
        DECLARE @subject nvarchar(max) = 'Job Failure Notification';
        DECLARE @body nvarchar(max) = 'TestEmailOnFail Job Failed'
            + CHAR(10) + CHAR(13) + 'Error Number:  ' + CAST(ERROR_NUMBER() AS nvarchar(max))
            + CHAR(10) + CHAR(13) + 'Error Message: ' + ERROR_MESSAGE();
        DECLARE @to nvarchar(max) = 'somebody@email.com';
        DECLARE @profile_name sysname = 'SQLMailProfileName';
        EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile_name,
            @recipients = @to, @subject = @subject, @body = @body;
    END CATCH
END

You can refer here how it is being used.

Adding@ You can recieve email if Database mail is configured in very first place. On how to set that up read How to Set Up Database Mail for SQL Server Job Failures

Also, In SQL Agent Job Properties-> step > edit> Advanced --you can also check Include step output in history to get the message from step failure.

Leave the On failure action to Quit the job reporting failure.


OPENROWSET can't import an excel spreadsheet into SQL Server 2005 Express

Ok well I found the solution or at least its one or both of the two

Instead of running express with the Network Service account, I created a regular user.  Then I gave that user rights to the folder with the excel spreadsheets.
I had already installed AccessDatabaseEngine.exe but I hadn't restarted the computer.  This time I restarted it.

This is the OPENROWSET SELECT statement I used

  SELECT
  FT_ID_SOCIETE_FF2C,FT_NOM,FT_ADR1,FT_ADR2,FT_CP,FT_VILLE,FT_TEL,FT_FAXFROM
  OPENROWSET  ('Microsoft.Jet.OLEDB.4.0','Excel
  8.0;Database=D:\SQLFTP\LISTEFF3CFTP.xls;HDR=YES',  'select * from [Requêtechauffage fioul$]');

So maybe it was a permissions problem?  Weird since I could import csv files.
Maybe it was just having to restart the computer after having installed AccessDatabaseEngine.exe.


The best option would be to use the Flash recovery area option, then set a restore point before you make your change and you can revert to the restore point after you make your change, hence you can rerun the script over and over until you work out the issues. You probably need Enterprise edition to do that, but you can certainly try to make it work. The next best option would be to do an RMAN backup and then run your script and recover the database after the script runs. If it really is a dev system, and if space is tight you might as well turn off archive logging and do a warm backup of the database. hence it is in a consistent state and you don't need to worry about recovering archive logs.

http://docs.oracle.com/cd/E17781_01/server.112/e18804/storage.htm#CIHEHCGI


How to select blocks from a result by condition?

A classical gaps-and-islands problem:

SELECT master_id, min(created_at) AS date_from, max(created_at) AS date_to
FROM  (
   SELECT *
        , count(*) FILTER (WHERE step) OVER (PARTITION BY master_id ORDER BY created_at) AS range
   FROM  (
      SELECT *, created_at > lag(created_at) OVER (PARTITION BY master_id ORDER BY created_at) + interval '1 month' AS step
      FROM   tbl
      ) sub1
   ) sub2
GROUP  BY master_id, range
ORDER  BY master_id, range;

Related (with explanation):

Combining separate columns into smallest possible related ranges
Select longest continuous sequence
Calculate Difference in Overlapping Time in PostgreSQL / SSRS


Don't know why MySQL is not creating table with primary key constraint

Correct syntax, with minimal changes to your style:

create table uinfo_master(
uname Varchar(50) NOT NULL,
CONSTRAINT uname_pk PRIMARY KEY (uname),
pwd Varchar(50),
fname Varchar(50),
lname Varchar(50),
email Varchar(50),
phno Varchar(50),
address Varchar(200),
city Varchar(50),
state Varchar(50),
pin Varchar(50),
country Varchar(50));

You cannot use the CONSTRAINT syntax for the PK on the same line as the column definition.

See the CREATE TABLE syntax:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...

Defining the column itself is done on the col_name column_definition version of the syntax. The | that follows that stands for OR in the documentation, meaning an alternate CREATE TABLE (comma separated) "line" can use the syntax that starts with CONSTRAINT, which optionally includes a "symbol" (that's what brackets mean: optional), then PRIMARY KEY, optionally an index_type, and then the column itself, in parentheses.

From the look of it, you tried to do all that on one line, which was where your problem was. You can declare a column to be a PRIMARY KEY on the line the column is defined, but you cannot use the CONSTRAINT syntax to do so.


indexing on multiple nvarchar(max) columns

I know this is already answered, but I'm going to put in my vote for using HASHBYTES.
I talk a little about it in my post http://michaeljswart.com/2013/11/hashing-for-indexes/ and I describe what that looks like in this case below:

If you can get away with nvarchar(4000) instead of nvarchar(max), then, this schema becomes feasible:

CREATE TABLE ProductDetail
(
    ProductDetailId INT IDENTITY PRIMARY KEY,
    ShipperName NVARCHAR(4000),
    ShipperNameHash as HASHBYTES('SHA1', ShipperName) PERSISTED,
    ConsigneeName  NVARCHAR(4000),
    ConsigneeNameHash  as HASHBYTES('SHA1', ConsigneeName) PERSISTED,
    UNIQUE(ShipperNameHash, ConsigneeNameHash )
)
go

When creating it, SQL Server will warn you about the maximum key length for that unique index being over 900 bytes. But in practice, SHA1 never returns more than 20 bytes, so in this particular case, the warning is ignorable.

See that it works with this insert statement

insert ProductDetail (ShipperName, ConsigneeName)
values
(
  REPLICATE(N'A', 3999) + N'B',
  REPLICATE(N'A', 3999) + N'B'
),
(
  REPLICATE(N'A', 3999) + N'C',
  REPLICATE(N'A', 3999) + N'C'
)

See that it fails with this insert statement:

insert ProductDetail (ShipperName, ConsigneeName)
values
(
  REPLICATE(N'A', 4000),
  REPLICATE(N'A', 4000)
),
(
  REPLICATE(N'A', 4000),
  REPLICATE(N'A', 4000)
)


Having issue with window function COUNT counting duplicates

It looks like you are trying to return the count of orders associated with an e-mail id? In which case I think you can change the partition to

count(orders.order_id) over(partition by line_items.email) as num_orders

Rather then using a window function, you could also just do a group by

select t.email,
          count(*) as num_orders
     from (
  select line_items.email,
         orders.order_id
    from orders
    join line_items
      on orders.order_id = line_items.order_id
    group by line_items.email,
              orders.order_id
    ) t
    group by t.email


Problems with sqlcmd -q and quotations marks

Try to use this. ;-)

use DB; EXEC sp_MSforeachtable @command1=N'print ''?'' DBCC DBREINDEX (''?'', '' '', 80) ';

Double quotation marks will escape the following quotation mark.

Here an example of sqlcmd:

sqlcmd -S YOURSERVER -Q "use net_temp_test; EXEC sp_MSforeachtable @command1=N'print ''?''';"


Database design for surveys and experiments

You may wish to start with http://tdan.com/data-model-quality-where-good-data-begins/5286 Particulary i found following technique very useful when building relationships:

  Relationship Names
  Your author studied under Richard Barker and is a strong believer in the Oracle/SSADM[5] standard structure for
  relationship names.  It provides discipline, and it permits the
  relationships to be read as easy-to-understand but precise English
  sentences.  When models are being presented to the public, this is
  extremely valuable.   Each of the two roles in a relationship should
  be named with this structure:

Each
<entity type 1>
must be           (Side of relationship line closest to first entity type is solid)
(or)
may be             (Side of relationship line closest to first entity type is dashed)
<relationship>
one and only one  (No crow’s foot next to second entity type)
(or)
one or more       (Crow’s foot next to second entity type)
<entity type 2)

  For example, in Figure 10, the first relationship consists of the
  following two roles:
   •    Each ORDER must be  from one and only one PARTY.
   •    Each PARTY may be a buyer in one or more ORDERS.
  The second  relationship consists of the following two roles:
  • Each ORDER must be to one and only one PARTY.
  • Each PARTY may be a seller in one or  more ORDERS.
  (Note that these sentences are much more graceful than
  something like “A PARTY buys in zero, one, or more ORDERS”.  Non-data
  modelers don’t talk like that.)


Calling a .NET method in a SQL Server script

It would be a tough call between a CLR assembly and using SSIS given that your current migration script is just a script.

CLR Assembly:

Must be installed into a database (both the assembly binary and the exposed wrapper method(s)).
Requires setting up an external project in Visual Studio, something you may not be very familiar with.
Very easy to integrate into your existing script.
Debugging can be a bit involved, because you have to either debug locally (i.e., on the server), set up remote debugging, or set up a comprehensive test suite. All of these can be problematic for different reasons. (Note: setting up a test suite is a good idea, but if time is a factor and you've already received a tested chunk of code, it's much safer to skip it in this scenario than if you converted the code to T-SQL, for example.)

SSIS Script Component:

Means running a totally separate process to do the calculation.
Much easier to get started than with a separate assembly, where you have to provide all the boilerplate code. With SSIS, pretty much all you do is fill in the required method bodies.
BIDS, which is x86, can't debug x64 scripts, so there's some ridiculousness that comes from that. (See here for a quick fix.) This would allow you to debug locally (on your workstation), though, which is an advantage.

While I haven't tested, I'll go out on a limb and say that the performance of both methods should be similar. If performance is a big concern, set up both scenarios and comparison test. In fact, you may want to set up both anyway just for practice, so you can see some of the differences and similarities for yourself, which is way more interesting and fun than reading about it (well, at least I think so).

A third option would be to move the process entirely to SSIS, which is more suited to migration/ETL types of tasks. This may or may not be feasible. You wouldn't necessarily need to retool the whole process, as you can, of course, execute arbitrary scripts.

Finally, another option is to convert the code to T-SQL. Depending on the complexity, though, it may be quite a challenge. Certainly there are manipulation tasks much better suited to using a proper programming language/framework like .NET. Without seeing the code itself, it's impossible to say if it would even be worth an attempt. As I mentioned previously, you would definitely need to develop a comprehensive test suite in this case.

Personally, I would probably create a CLR assembly because I'm strong on the programming side of things and it would be the fastest, least intrusive way to integrate the code. But that's me, and I haven't seen the code, so it may not be the best solution for you and your environment.


Creating a database snapshot for every previous month

How precise in time does this snapshot have to be? To the hour? Second? You still didn't answer that in your first comment.

So you are using the "Database Snapshot" technology. IMO, this will lead you into trouble in the long run. You add more and more overhead to your database for each snapshot you have. Having database snapshot existing for a longer time period isn't something that I would recommend.  For more information, I suggest you read up on how database snapshot are implemented, with the copy-on-write technology. https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server?view=sql-server-ver15

I recommend you re-work your approach and use backups. Whether or not you will have to complement your full backups with log backups depend on the answer to my above questions.

As for past time, you have to go find old backups. There is nothing inside SQL Server that can produce a snapshot of a database from a previous point in time. (Except if you have some date columns and/or temporal turned on for each table, but puzzling that together is likely a huge undertaking.)

Having some date column for only some tables won't help you create snapshots for older data. Hunt down old backups and use them instead.


Query optimization: how to efficiently occlude overridden records?

In Postgres, typically the most efficient way is to use distinct on ()

select distinct on (started_on) started_on, recorded_on, image_id
from the_table
order by started_on, recorded_on desc
where started_on between ...;


Any disadvantage to using oraToolkit for installing oracle

This is an open question.  It's a tool kit so its certainly possible for you use it in a way that would not be optimal for some other future use.  It may not include tools for some of the things you want to set up or in the way you'd like to set them up.

The oracle installation process isn't difficult, is well scripted and has a nice graphical interface.  It is designed for that specific job and guides you through each step and wherever possible it stops you doing insane things and prompts you if you want to do something odd.  A great many people use this method and so it is well tested and highly supported not just by a few folks but by an army of people at Oracle and on the internet.  That means there is help available from multiple sources and that most likely an answer there for your issue before you even have it.

IMO it is better for you to understand the system you will have to support.  Spend a bit of time reading the installation guide and plan your installation out beforehand based on your needs (give some thought to Oracle Flexible Architecture, backups and redundancy).  Then do a software only install.  After that you can create a database with DBCA which isn't hard to use.  It does all the work but you make most of the decisions (usually from a small list of sensible choices).  The benefit of all this is that you will understand much more about how your installation is set up and that will pay off later.

Later - that leads on a another consideration.  Oracle will always have an installer and a database creation utility for their databases.  It will be designed to work with the version it ships with and it'll get regular updates over time.  Third party tools come and go, tend to lag behind releases and tend only to work in a sub-set of situations.  They might not be available for the next job.  Use caution investing your time in non-standard tools that save your time by reducing your understanding - and having an good understanding of the basics is vital if you are to move onto more complex tasks, especially those not covered by the tool-kit in question.


Use Stored Procedure or View to return Data from Multiple Tables

This seems like a strange question to me, but here are some opinionated brief one liner guidelines for each.

stored procedure: If you want use parameters and are not joining to the results.
in-line table-valued function: If you want use parameters and might join the results.
views: If you want to simplify querying a set of tables, and promise not to make views that include other views.

What you have posted looks like it would be a view to me. Unless you want to pass in parameters, then I would say it would probably be a stored procedure.


Database Design: Normalization and NULL values

If ResponseToTransactionId is always NULL for items marked 'REQUEST', and contains the 'REQUEST' items ID where the status is 'ALLOW' then It means that from an application point of view you should always be able to locate which records are linked by this field.

And if NULL then you know there is no link.

It is a bit unusual to do it this way, but it should be fine


AFAIK, you cannot force a plan to stay in cache. However, a query can be thrown out of the cache for several reasons. Read a blog about execution plans. It states some reasons why execution plans get invalidated:

  Changing the structure or schema of a table referenced by the query
  Changing an index used by the query
  Dropping an index used by the query
  Updating the statistics used by the query
  Calling the function, sp_recompile
  Subjecting the keys in tables referenced by the query to a large number of inserts or deletes
  For tables with triggers, significant growth of the inserted or deleted tables
  Mixing DDL and DML within a single query, often called a deferred compile
  Changing the SET options within the execution of the query
  Changing the structure or schema of temporary tables used by the query
  Changes to dynamic views used by the query
  Changes to cursor options within the query
  Changes to a remote rowset, like in a distributed partitioned view
  When using client side cursors, if the FOR BROWSE options are changed

Another pitfall could be that the query changes. This can happen if the where clause changes (e.g. you filter by date) and you don't use bind

Thinking about, you haven't stated that you checked whether the execution plan is still in cache or not. You should query the cache to figure that out. However, since you say your query runs only once a day, the plan might just be expired. The blog actually mentions the formula on how sql server determines when to expire a plan:

  If the following criteria are met, the plan is removed from memory:

  More memory is required by the system
  The "age" of the plan has reached zero
  The plan isn't currently being referenced by an existing connection

In general, 7 seconds is not to bad if you have a big report. It takes longer to get a coffee. Does it create any other issue than 6 seconds of wasted productivity for an end user?


How to remove these duplicate records in an Oracle Database 12c?

A lesser-known feature of Oracle databases- every row has a [hidden] column, called ROWID.

These meaningless,, character values can be used to isolate duplicates like this and get rid of them.

This query should get you candidates rows to be deleted:

select user_id, message_date, max(ROWID)
from messages m
inner join user_messages um
      on   m.id = um.message_id
group by user_id, message_date
having count( * )  > 1
order by 1, 2 ;

user_id | message_date | rowid
      1 | 2019-01-01   | AB12CD34EF...56
      1 | 2019-02-01   | AB12CD34EF...78
      1 | 2019-03-01   | AB12CD34EF...89

You can then delete rows using the ROWID directly.

delete from user_messages
where rowid = 'AB12CD34EF...56' ;

All that said, do not be tempted to use them for anything else!!
ROWID's are potentially volatile and so can change on you over time.


Database server down: file /dev/vda1 used up all diskspace

I probably found the reason, I made nightly backups to external server, but those were also stored on our server which had exceeded allowed disk usage. I didn`t understand /dev/vda1 at first, but I now get that is not a file but probably a partition or something like that.


How to find the 'Instance ID' for PowerShell Invoke-TroubleshootingPack?

You don't need to enter a 'Instance ID' for PowerShell Invoke-TroubleshootingPack

Just hit enter, when prompted for the Instance ID, without a value.

After I figured it out, I went back and looked at the training directions and found the following hiding in the middle of the paragraph.

  you’ll be asked for an “Instance ID.” Hit Enter

I learned a lot on this journey, this was frustrating. Posting so others will find the answer sooner than I did.


#1025 - Error on rename of table errno: 150 : Table was deleted while tried to assign foreign key

You have a weird situation, but it has happened others.

Some worked around this

(Bug?) InnoDB MySQL error 1025, errno 150 Foreign Key
What does mysql error 1025 (HY000): Error on rename of './foo' (errorno: 150) mean?

Others have tried to explain it

MySQL's ERROR 1025 explained
SQLSTATE[HY000]: General error: 1025 Error on rename of

I think the problem stems from the InnoDB Architecture (Vadim Tkachencko's Picture)

See the data dictionary at the top of ibdata1 ? Although the physical file (table1.ibd) to the InnoDB table is gone, the data dictionary still thinks the table exists.

I have addressed this data dictionary issue before

Jul 03, 2014 : Can't create table, but table doesn't exist
Jun 09, 2014 : temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave

You may need to mysqldump the database and load it into a new server (as suggested by the OP since it was unresolved but worked around), or look for a tool that can flush out the bad data dictionary entry.


Best way to store, query and update 300M rows of data

I ended up storing data in DynamoDB and doing daily sync with Redshift. I've tried Redshift with 600M sample data on 4 node cluster and it runs extremely fast. It is exactly what i need.


Retrieve 16 digit card numbers from a string


Effectively searching through entire 1 level nested JSONB

For pattern-matching, there is currently (pg 10) no better way with standard operators and indexes than to unnest values and test each individually. At least, use an efficient EXISTS query:

SELECT *
FROM   tbl
WHERE  EXISTS (SELECT FROM jsonb_each_text(jsonb_column) WHERE value ~* 'val');

A specialized solution for this particular use case: concatenate all string values per row with an IMMUTABLE function and create a trigram GIN index on the expression. You need the additional module pg_trgm installed. Read this first if you are not familiar:

How is LIKE implemented?

Function:

CREATE OR REPLACE FUNCTION f_all_value_string(jsonb)
  RETURNS text AS
$func$
SELECT string_agg(value, '|') FROM jsonb_each_text($1)
$func$  LANGUAGE sql IMMUTABLE;

Chose a separator that never interferes with your patterns - '|' in the example.

Index:

CREATE INDEX tbl_all_value_string_trigram_idx ON tbl
USING GIN (f_all_value_string(jsonb_column) gin_trgm_ops);

Query (matching the index):

SELECT * FROM tbl WHERE f_all_value_string(jsonb_column) ~* 'val';

Related:

Search for nested values in jsonb array with greater operator
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL


select * from information_schema.user_privileges;

EDIT:

As mentioned by Shlomi Noach:

  It does not list database-specific, table-specific, column-specific,
  routine-specific privileges. Therefore, the grant GRANT SELECT ON
  mydb.* TO myuser@localhost does not show in
  information_schema.user_privileges. The common_schema solution
  presented above aggregates the data from user_privileges and other tables
  to give you the full picture.


There is a semicolon missing after the DO statement:

...
END     -- semicolon is not necessary here
$do$;   -- but here it is
COMMIT;

The first example works, because the semicolon is optional at the end of a script. That's also why the semicolon after END inside the block is optional.


Connection to an oracle db from another computer

Type:

lsnrctl start

On your Unix box. That starts the listener.

If you still can't connect after that it's either a firewall issue or the listener isn't configured.


Why can't I get the available free space after shrinking?

In shrink action, you need to choose:

"Reorganize pages before releasing unused space"

And choose desidered size.

Actually you don't have released space because you have still used pages ad the end of your file.

Edit:
You can do it via query, with instructions like this:

DBCC SHRINKFILE (N'MATRIX24X7' , 449000)


Installation of SQL Server Express LocalDB 2017

LocalDB is a quirky flavor of SQL Server. In most cases, unless there's specific functionality in LocalDB that you need, I'd suggest running the "regular" Express Edition--as you can avoid quirks like the one you're running into. Additional details of differences are included in this article.

LocalDB has an "automatic" instance that is tied to current user. To connect to that instance, you would use a connection string like "Server=(localdb)\MSSQLLocalDB;Integrated Security=true".

You can also create an explicitly named instance using the SQLLocalDB.exe command line utility. Depending on your installed version, the path may vary (in this example, 130 is SQL Server 2016, but you would use 140 for 2017, etc):

C:\> cd "C:\Program Files\Microsoft SQL Server\130\Tools\Binn"
C:\> SqlLocalDB.exe create MyLocalDB
C:\> SqlLocalDB.exe start MyLocalDB

The SQLLocalDB info command can be used at any time to determine the state of an instance, whether it is running or not:

C:\> SqlLocalDB.exe info MyLocalDB

Additional info on creating & connecting to a LocalDB instance can be found on docs.microsoft.com.


I only learned this in the past year.  You can query an excel sheet as if it's a table.  As long as your keys are also in excel you can join the excel sheet to the user table.

You can have a look at this Microsoft Article that discusses options for importing from Excel to SQL Server.

http://support.microsoft.com/kb/321686


Allowing non-sysadmin users to see SQL Server execution plans

Community Wiki answer generated from question comments

Yes this is possible. See:

Syntax for Granting, Denying, and Revoking the SHOWPLAN Permission1 2
Showplan Security2
SHOWPLAN Permission and Transact-SQL Batches2

1 Contributed by Mark Sinkinson
2 Contributed by Aaron Bertrand♦


As you simply cannot create a foreign key against an array column, you should store the experiments in the proper relational way.  This is what you mention as a junction table.

It is a table like the following:

CREATE TABLE experiments (
    method_id integer REFERENCES methods (method_id),
    method_class_id integer REFERENCES method_class (method_class_id)
);

Here you will have one row per method-class pair (which, in turn, may mean several trials, based on your comments above), instead of just one per trial as in your current design.  This means a slight storage overhead, but it gives you the flexibility - queries will use proper joins which can be supported by indexes, for example.  Also, removing methods from a class will be much easier.

Note: I suppose above that you have a method_class table, too.


Writing a stored procedure for deleting a table rows

Create a stored procedure that builds a dynamic SQL statement based on your input parameters and then executes the command

First, let's create a test table with some data to play with.

 --Create a test demo table and insert a couple of rows
IF OBJECT_ID('dbo.TestDelete', 'U') IS NOT NULL
  DROP TABLE dbo.TestDelete;
 go
create table TestDelete (ID int, Name varchar(100), DateOnTable Date)
insert into TestDelete(ID, Name, DateOnTable) values (1,'NameOnTable','2017-01-01')
insert into TestDelete(ID, Name, DateOnTable) values (1,'NameOnTable','2017-03-01')
go

Now, let's create the 'delete' stored procedure.  My example takes in 3 parameters.  DeleteDate, SchemaName and TableName.

--Drop procedure if is already exists
IF EXISTS (
        SELECT *
        FROM dbo.sysobjects
        WHERE id = object_id(N'[dbo].[DeleteTableRowsLessThanSpecificDate]')
            AND OBJECTPROPERTY(id, N'IsProcedure') = 1
        )
    DROP PROCEDURE [dbo].[DeleteTableRowsLessThanSpecificDate]
GO

--Create the procedure
create PROCEDURE [dbo].[DeleteTableRowsLessThanSpecificDate] @DeleteDate DATE
    ,@SchemaName SYSNAME
    ,@TableName SYSNAME
AS
BEGIN
    SET NOCOUNT ON
    SET XACT_ABORT ON

    --Example error checking to prevent deleting rows with a date within the last month
    IF @DeleteDate >= Dateadd(MONTH, - 1, sysdatetime())
    BEGIN
        RAISERROR ('Delete Date Too Recent - Delete cancelled',16,1);

        RETURN
    END

    --Declare a variable to hold the dynamic SQL command
    DECLARE @Command NVARCHAR(4000)
    DECLARE @ParmDefinition NVARCHAR(4000) = '@SchemaName sysname, @TableName sysname, @DeleteDate date'

    --Build the command by concatenating the input TableName and input DeleteDate
    SET @Command = 'DELETE FROM ' + quotename(@SchemaName) + '.' + quotename(@TableName) + ' WHERE DateOnTable < ''' + convert(VARCHAR(20), @DeleteDate) + ''''

    --Printing just to verify the syntax
    PRINT @command

    --Dynamically execute the command you just built
    --(Using EXEC() instead of sp_executesql)
    --Check this blog post about why you should use sp_executesql instead of EXEC()
    --https://sqlblog.org/2011/09/17/bad-habits-to-kick-using-exec-instead-of-sp_executesql
    EXECUTE sp_executesql @Command
        ,@ParmDefinition
        ,@SchemaName = @SchemaName
        ,@TableName = @TableName
        ,@DeleteDate = @DeleteDate;
END
go

Now, let's test the stored procedure by passing in some different DeleteDates and checking the results.

--Now, we're ready to test the stored procedure by deleting all rows
--less than 2017-04-08.  That should leave only one row in our example (2017-06-01)

--Check rows before calling the stored procedure
select * from TestDelete

--Call the stored procedure
EXEC dbo.DeleteTableRowsLessThanSpecificDate @DeleteDate = '2017-02-01'
    ,@SchemaName = 'dbo'
    ,@TableName = 'TestDelete'

--Check rows after calling the stored procedure
select * from TestDelete

I also added some error checking logic in the stored procedure to show how you might prevent rogue deletes that are too aggressive due to the date being passed in.  My example stored procedure throws an error if you try to delete any rows within the past month. (I wrote this answer on 4/9/2017 and on that date, the following throws an error.

--Testing error logic by trying to delete rows within the last month
EXEC dbo.DeleteTableRowsLessThanSpecificDate @DeleteDate = '2017-04-01'
    ,@SchemaName = 'dbo'
    ,@TableName = 'TestDelete'

  Msg 50000, Level 16, State 1, Procedure
  DeleteTableRowsLessThanSpecificDate, Line 14 [Batch Start Line 72]
  Delete Date Too Recent - Delete cancelled


Aggregate function to count occurrences of specific word

Sure, have 3 SUMs of the needed values converted to 1, and others to 0.

select
  id,
  sum(case when enum = 'A' then 1 else 0 end) as a_count,
  sum(case when enum = 'B' then 1 else 0 end) as b_count,
  sum(case when enum = 'C' then 1 else 0 end) as c_count
from
  table
group by
  id
;


ORA-47500: XE edition memory parameter invalid or not specified

XE is limited to 1 GB, you can not increase its memory.

Oracle Database XE RAM Limitation

  The maximum amount of RAM that an Oracle Database XE database uses
  cannot exceed 1 gigabyte, even if more is available.


In InnoDB, does a Transaction imply any implicit locking of a table?

In repeatable reads, there is always row-level locking imposed via the gen_clust_index (aka the Clustered Index). This is the beauty of Transactions. What is even more interesting is that InnoDB has four transaction isolation levels, not just one:

There are four values for tx_isolation you can set:

READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ (default)
SERIALIZABLE

In your particular case, inserting data into TableA actually does not get written to disk. The necessary changes are recorded in three(3) distinct places:

log buffer in memory
in ibdata1

undo tablespace
rollback segments
double write buffer

redo log info in either ib_logfile0 or ib_logfile1

The same applies with the delete in step 5.

Executing a rollback will undo the delete and then undo the inserts.

You must remember something very important: If you want to rollback multiple SQL commands, you must begin like this:

SET autocommit = 0;
START TRANSACTION;

Transaction begins
Read data from Table A
If (Table A has Any Data) End Transaction (via ROLLBACK) and exit
If Table A has No Data, Proceed further
Delete a record in Table B
Transaction ends

COMMIT;

Give it a Try !!!

When everyone is using repeatable reads

your INSERTs are only seen by you
someone else's DELETEs are only seen by the other person

CAVEAT : Table level locking is never implicit for InnoDB. If you want to lock a table, you must issue, LOCK TABLE explicitly.


For MySQL, you might use use the ExtractData function.

If you are also using Windows (it needs Net Framework) then this old and outdated tool might still do the job.

http://xmltodb.sourceforge.net/

I have used it in the past and it converts the XML file to plain INSERT commands. For simple XML files it actually works. Of course the INSERT commmands will need some tweaking (change " or ') according to the db flavor but 90% of the actual job is done with the tool.

Commercial tools like XMLSpy http://www.altova.com/xmlspy/database-xml.html have a similar functionality (you may try the trial editions).

You can even try importing the file to MS Excel and then export to CSV for importing into the MySQL database. MS Excel has a nice feature XML maps that can map XML attributes and entities to columns. I tested it and it will easily work with a well formatted XML like yours.