how to confirm or refute that performance degradation is due because of too many indexes maintained

Before trying to determine what indexes to disable (not doable in Sybase ASE) or drop, I'd want a better understanding of what 'some degradation on another part of the system appears' means.  I'd want a lot more details as to the type and degree of degradation.

For the sake of discussion I'm going to assume 'degradation' refers to some queries that are now performing poorly, in which case I'd want to look at what work's been done to (re)tune said queries; are query plans showing the 'wrong' indexes being used? are query plans showing the 'wrong' join order?

Yes, the performance degradation could be tied to the increase in the number of indexes, but what I'm thinking is that the degradation doesn't have anything to do with the overhead of updating the indexes (again, assuming 'degradation' is related to queries now performing poorly) but rather the generation of 'poor' query plans.

When optimizing a query, Sybase ASE is going to attempt to evaluate every possible join order, utilizing every possible index, utilizing every possible statistic on all possible columns and indexes. Net result is the optimizer has a LOT of work to do for queries that reference a large number of tables, indexes and column stats.

While it's technically possible that the Sybase ASE optimizer could go off for an hour while trying to optimize some big-arse, gnarly query ... and yes, the optimizer does have some 'smarts' that allow it to quickly eliminate some join/index combinations ... in reality the optimizer has limitations (via configuration parameters) on how long it can search for the 'best' query plan, with the (unfortunate) result being that for really large, complicated queries the optimizer could time-out before it finds the 'best' query plan, thus leaving us with a 'best up to this point' (ie, not-so-efficient) query plan.

Assuming your degradation issue is related to some queries that have all-of-a-sudden started performing poorly, I'm guessing the additional indexes could have increased the optimizer's workload to the point that it's now timing out before it can find the 'best' query plan.

Under this scenario there are a few options for (re)tuning the queries ... give the optimizer more time to find the 'best' (or at least a 'better') query plan ... provide hints (eg, index hints, abstract query plans) to limit the combinations the optimizer has to consider ... rewrite the query to simplify the optimizer's workload ...

While there may be some other explanations for the degradation you're seeing, the first step would be to get more details about the type and degree of degradation ... something we're not given in this thread ... and (unfortunately) a process that is likely going to be too large/complicated to cover in detail in this medium.


Partitioning a table only divides it into "chunks" based on the partition function. The clustered index will give order to the data within each partition.

If you're planning to run queries that involve parts of a partition (i.e., show me sales between Jan 5th and Jan 12th), then it can be advantageous to those queries to have the date as the leading column of the clustering key. This type of structure will result in clustered index seeks, instead of partition scans. (Assuming there are no other suitable indexes on the table.)

If the queries only touch entire partitions, it doesn't matter as much, as partition elimination is enough to isolate the needed data. That said, ordering by date first could eliminate the need for an expensive sort operation, depending on what you're doing.

But this also depends on which columns you need from the table. If you only need to do something like aggregate total sales amounts over a date range, it may be sufficient to use a covering nonclustered index with the date as the leading (or only) column of the key, instead of rebuilding the entire table just for that.

If you do change the clustered index, this will affect singleton lookups (probably by the identity column which I assume is the primary key) as they will now involve a nonclustered index seek + key lookup. If this type of activity isn't a major part of the workload, this will be fine, but you have to be really careful that not too many rows are selected by these queries, or the optimizer will revert to a partition scan on the assumption that it's cheaper. Again, depending on which columns you need, it may be advantageous to create a covering nonclustered index that includes only the columns you need.


Setting shared_buffers in postgresql.conf does not seem to take effect

The canonical unit for shared_buffers is pages of 8kB, so the actual memory allocated in bytes is:

524288 * 8192 = 4294967296 or 4096*1024*1024 as requested.

You can also check the size of the segment of memory with ipcs -m


How can I exclude rows from two tables whose values are encompassed by each other?

According to you question, I think this is what you want:
select * from #TableB b
where not exists (
    select 1 from #TableA a
    where b.StartPos between a.StartPos and a.EndPos
    and A.RID = b.RID
)
union
select * from #TableA a
where not exists (
    select 1 from  #TableB b
    where a.StartPos between b.StartPos and b.EndPos
    and a.EndPos between b.StartPos and b.EndPos
    and A.RID = b.RID
)
;

Fiddle


Behavior of transactions on multiple connections

What you see is the correct behaviour for the default isolation setting (REPEATABLE READ) and even for lower (READ COMMITTED) setting. More details can be found in MariaDB and MySQL docs:

SET TRANSACTION
Transaction Isolation Levels
Consistent Nonlocking Reads

which basically sums up to:

What one transaction is writing, other transactions should not be able to read. Not before the one transaction commits.

When a transaction is writing something to the database, there is no guarantee that the write will persist. The transaction may get an error and all of the writes it has done be rolled back. So, what you observe

  However, in my testing, the job tracking record doesn't get updated until the transaction for the job is committed.

is correct from that point of view. There is nothing to be seen from the outside, progress is 0, until something is committed. That's what the Isolation property of transactions is all about.

I see 2 solutions to the issue:

A. use the "worker", transaction 1 for progress report, as well. Who's better to know how much its work has progressed, than the worker itself?
B. In the 2nd, "progress report" connection, use the lowest isolation setting (READ UNCOMMITTED):

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

Now, the transactions from this connection will be able to do "dirty reads", i.e. see uncommitted writes from the 1st transaction and will report correctly the (uncommitted) progress.


It worked correctly for me with the below

1- Convert from XLSX to XLS

2- Add incremental primary key at XLS sheet

2- Then save as text Unicode

3- Go to Microsoft SQL then right click on the database then import flat file

4- Convert data types to nvarchar(max) if need it


Does having a .ldf for a index file cause issues?

By convention, the following file extensions are used in SQL Server:

Primary Data File:  .mdf
Secondary Data File(s):  .ndf
Log File:  .ldf

Having said that, you're actually free to use any file extension you like for any SQL Server file.  In fact, it's completely fine to use no extension at all.

SQL Server does not look at or care about the file name in any way other than that it needs to be compliant with NTFS file name standards.

You can see what files are being used by a database with this query:

SELECT d.name
    , mf.name
    , mf.physical_name
    , SizeInMB = CONVERT(decimal(10, 2), mf.size) * 8192 / 1048576
    , FileGrowth =
        CASE mf.growth
        WHEN 0 THEN 'fixed size, will not grow'
        ELSE
            CASE mf.is_percent_growth
            WHEN 1 THEN CONVERT(varchar(10), mf.growth) + ' percent'
            WHEN 0 THEN CONVERT(varchar(30), mf.growth * 8192 / 1048576.0) + ' MB'
            END
        END
    , MaxSizeInMB = CASE mf.max_size
        WHEN 0 THEN 'fixed size, will not grow'
        WHEN -1 THEN 'file will grow until disk is full'
        WHEN 268435456 THEN 'log file will grow to maximum size of 2TB'
        ELSE CONVERT(varchar(30), CONVERT(decimal(10, 2), mf.max_size) * 8192 / 1048576.0) + ' MB'
        END
FROM sys.databases d
    INNER JOIN sys.master_files mf ON d.database_id = mf.database_id
WHERE d.name = '<database_name>';

Replace <database_name> with the name of the database you're concerned about, or remove the entire WHERE clause to see files for all databases.


Custom columns {{shudder}}.

There's not One True Way® that comes to mind, but what about one or more rows in the new UserNotificationMetadata table, which has a MetaKey VARCHAR(x) not null (with a small but practical "x")  and primary key of (NotificationId,MetaKey) and a MetaValue TINYTEXT not null (or a comparably suitable data type)?

This is one of the relatively few cases where a sort of EAV-style structure is arguably not a case of urDoinItWrong™ since the data, here, isn't part of your core schema and there's (again, arguably) not a need to be able to impose constraints on the data... a number or date stored here is just a string of characters that's going into a message.

For each (yes, that is also a little bit procedural-sounding) row matching the UserNotification, take CONCAT('{{',MetaKey,'}}') and use REPLACE() to substitute it with MetaValue in the message.

I would also suggest that 'seen' should be a timestamp, rather than a boolean.


SQL Server Standard edition attach partitioned database

You cannot restore a database that contains partitioned tables in Standard Edition. You also cannot attach a database without supplying all the data files. You could restore individual files or filegroups, but it won't achieve what you're after.

Your only option is to get rid of partitioning before you take a backup and move it to Standard Edition.

BTW, if you query sys.dm_db_persisted_sku_features you'll get a list of all the active features in the database that might impact your migration.

EDIT 2017-09-15: not true any more since SQL Server 2016 SP1.


Why is my postgres `base` directory filling up rapidly?

To generate 10k WAL files overnight, your database is obviously very busy.  It is not surprising that the non-WAL of such a busy database might also grow rapidly. Indeed, why wouldn't it?  If your database were that busy but not growing, that is the weird thing which would be in need of explanation, based on what you know about how your database is used.

Do you have hot_standby_feedback turned on?

If your database is still up, you can use these queries to figure out where all the space is going, although I usually just start with '\l+' and '\dit+' in psql (those ones don't sort by size, so if you have a lot of objects to scan through, the other ones may be better).  If your database is not up, you can use OS tools like 'du' and 'ls' to to figure out where the space is going.  But translating file names back to table names without a running database will not be easy.


Microsoft recommends using Rowversion over timestamp, but I can not find "rowversion" data type inside my sql server 2008 R2

rowversion is just an alias for timestamp. Under the covers, they are the exact same thing.

If you're using the GUI (prior to 2012), you have to use timestamp. (Peripheral hint: please stop using the GUI.)

But it doesn't stop there. Even if you generate this table using DDL:

CREATE TABLE dbo.x(y ROWVERSION);

The data type in the metadata will be timestamp:

SELECT t.name
  FROM sys.types AS t
  INNER JOIN sys.columns AS c
    ON c.system_type_id = t.system_type_id
  INNER JOIN sys.tables AS st
    ON c.[object_id] = st.[object_id]
  INNER JOIN sys.schemas AS s
    ON st.[schema_id] = s.[schema_id]
  WHERE st.name = N'x'
    AND s.name = N'dbo'
    AND c.name = N'y';

And if you generate a script for this table, it will also include timestamp:

CREATE TABLE [dbo].[x](
    [y] [timestamp] NOT NULL
) ON [PRIMARY]

And in fact:

SELECT * FROM sys.types WHERE name = N'rowversion';

Results:

----
0 row(s) affected.

So there isn't even a type in the system named rowversion - any DDL that includes it gets parsed and translated before the DDL is even executed against the server.

Microsoft has been a walking contradiction on this since they first introduced the poorly-named data type (which also happens to violate the SQL standard, which dictates that the timestamp data type should be used for date/time data). I asked back in 2007 for this to be properly deprecated. My Connect item is still active (archive)- feel free to vote and, more importantly, comment about how the documentation contradicts the possible usage of the product.

Others have written about this too.


Unclear oracle problem with getting response of query

Any query which returns more than 50 rows could seem to execute faster on SQL Developer. This is because SQL developer only fetches 50 rows (and more after you press Page Down), while your application possibly fetches many more rows.

The proper way to test is to use sqlplus with SET AUTOTRACE TRACEONLY.


Detect if only one column is changing in a trigger

Subject to the limitations mentioned in the other answers, COLUMNS_UPDATED can be used to check if only one (or some) column(s) were targeted by the triggering UPDATE statement.

This can be useful where update statements are written to only mention columns that may be updated. The following script demonstrates:

Table and data

-- A table with an identity key and 48 other integer nullable columns
CREATE TABLE dbo.Test
(
    ID integer IDENTITY PRIMARY KEY,
    col01 integer NULL, col02 integer NULL, col03 integer NULL, col04 integer NULL,
    col05 integer NULL, col06 integer NULL, col07 integer NULL, col08 integer NULL,
    col09 integer NULL, col10 integer NULL, col11 integer NULL, col12 integer NULL,
    col13 integer NULL, col14 integer NULL, col15 integer NULL, col16 integer NULL,
    col17 integer NULL, col18 integer NULL, col19 integer NULL, col20 integer NULL,
    col21 integer NULL, col22 integer NULL, col23 integer NULL, col24 integer NULL,
    col25 integer NULL, col26 integer NULL, col27 integer NULL, col28 integer NULL,
    col29 integer NULL, col30 integer NULL, col31 integer NULL, col32 integer NULL,
    col33 integer NULL, col34 integer NULL, col35 integer NULL, col36 integer NULL,
    col37 integer NULL, col38 integer NULL, col39 integer NULL, col40 integer NULL,
    col41 integer NULL, col42 integer NULL, col43 integer NULL, col44 integer NULL,
    col45 integer NULL, col46 integer NULL, col47 integer NULL, col48 integer NULL
);

-- A single row of sample data
INSERT dbo.Test
(
    col01, col02, col03, col04, col05, col06, col07, col08, col09, col10,
    col11, col12, col13, col14, col15, col16, col17, col18, col19, col20,
    col21, col22, col23, col24, col25, col26, col27, col28, col29, col30,
    col31, col32, col33, col34, col35, col36, col37, col38, col39, col40,
    col41, col42, col43, col44, col45, col46, col47, col48
)
VALUES
(
    01, 02, 03, 04, 05, 06, 07, 08, 09, 10,
    11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
    21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
    31, 32, 33, 34, 35, 36, 37, 38, 39, 40,
    41, 42, 43, 44, 45, 46, 47, 48
);

Trigger

The logic here is to construct the expected value of  COLUMNS_UPDATED when only col30 is targeted by the UPDATE, and to compare that with the value actually encountered. The trigger uses COLUMNPROPERTY to find the id of the column to account for possible future metadata changes. Repeating the logic for col30 would enable other columns to be tested at the same time.

CREATE TRIGGER Test_AU
ON dbo.Test
AFTER UPDATE
AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    -- Zero @Mask to the same length as COLUMNS_UPDATED()
    DECLARE @Mask varbinary(128) =
        SUBSTRING(CONVERT(binary(128), 0x), 1, DATALENGTH(COLUMNS_UPDATED()));

    DECLARE
        @ColumnID integer,  -- metadata ID of the target column
        @Byte integer,      -- byte of @Mask to change
        @Work binary(1);    -- working value of the byte to change

    -- Find the object ID of the trigger's parent table
    DECLARE @OID integer;
    SELECT @OID = T.parent_id FROM sys.triggers AS T WHERE T.[object_id] = @@PROCID;

    /* Set @Mask bits for column(s) of interest */

    -- col30

    -- Find byte, bit, and bit value to change
    SET @ColumnID = COLUMNPROPERTY(@OID, N'col30', 'ColumnId') - 1;
    SET @Byte = 1 + (@ColumnID / 8);

    -- Retrieve the right @Mask byte and set the target bit
    SET @Work = SUBSTRING(@Mask, @Byte, 1);
    SET @Work = @Work | POWER(2, @ColumnID % 8);
    SET @Mask = CONVERT(varbinary(128), STUFF(@Mask, @Byte, 1, @Work));

    -- Test if the resulting mask matches COLUMNS_UPDATED
    -- If not, other columns were updated
    IF @Mask != COLUMNS_UPDATED() PRINT 'Other columns updated';
END;

Tests

Neither of these updates produce a message, since only col30 is targeted by the UPDATE:

UPDATE dbo.Test
SET col30 = col30;

UPDATE dbo.Test
SET col30 = 1;

All of these statements produce the "Other columns updated" message:

UPDATE dbo.Test
SET col30 = col30,
    col31 = col31;

UPDATE dbo.Test
SET col30 = 2,
    col31 = 3;

UPDATE dbo.Test
SET col31 = 4;


How Can I get multi-Dimensional array by value

I found it myself. I wil llet you guys know so may other people get use it.

SELECT * FROM mydb AS f WHERE f.user_tag @> '[{"id":1}]'

That' s it. I can now get rows contains id=1


How do I use currval() in PostgreSQL to get the last inserted id?

If you create a column as serial PostgreSQL automatically creates a sequence for that.

The name of the sequence is autogenerated and is always tablename_columnname_seq, in your case the sequence will be names names_id_seq.

After inserting into the table, you can call currval() with that sequence name:

postgres=> CREATE TABLE names in schema_name (id serial, name varchar(20));
CREATE TABLE
postgres=> insert into names (name) values ('Arthur Dent');
INSERT 0 1
postgres=> select currval('names_id_seq');
 currval
---------
       1
(1 row)
postgres=>

Instead of hardcoding the sequence name, you can also use pg_get_serial_sequence() instead:

select currval(pg_get_serial_sequence('names', 'id'));

That way you don't need to rely on the naming strategy Postgres uses.

Or if you don't want to use the sequence name at all, use lastval()


The following seems to work in Mysql 5.5.

ALTER TABLE `t_keyword_conversion_ga` REORGANIZE PARTITION p201106  INTO (
    PARTITION p201105 VALUES LESS THAN ('2011-05-01') ENGINE = InnoDB,
    PARTITION p201106 VALUES LESS THAN ('2011-06-01') ENGINE = InnoDB
);

as you can check by sqlfiddle


The problem is just as the error message explains. The second argument of CONTAINS must be a literal string or a variable. It’s not the case that everywhere a string is allowed in T-SQL that you can also use a column name.
You could do something kludgy like this, but performance would likely be abominable.
CREATE FUNCTION DoesContain(@arg VARCHAR(30))
RETURNS tinyint AS BEGIN
  RETURN
    CASE WHEN EXISTS (
      SELECT * FROM B
      WHERE CONTAINS([Name],@arg)
    ) THEN 1 ELSE 0 END
END;
GO

SELECT
  A.*,
  (SELECT TOP 1 B.Name FROM B WHERE dbo.DoesContain(A.Name)=1) as [Name]
FROM A;


How to safely resume a SQL Server Mirroring scheme that had a expired certificate?

Since Mirroring operates by shipping over a live transaction log to the secondary servers, you'll need to start a new mirroring session.

Your secondary database has been out of sync for a week and unless you've managed to maintain a transaction log on the primary that spans that entire time (hint: you haven't), you'll need to start with a fresh backup on the secondary to establish a mirror session again.

This is also faster than theoretically letting a week's worth of logs get re-applied to the secondary anyways.

As for the primary database's availability, yes it will be available during any sync (whether resuming after a pause or setting up a new mirror), because it's always sending transaction logs to the secondary but that does not interfere with it's own availability.


I would recommend you start by reading this excellent introduction to SSAS processing.

To answer your questions:

You should ProcessUpdate your Date dimension then ProcessIndexes your cube. ProcessUpdate will not unprocess (unload) your measure group partitions like ProcessFull which is important if you only want to process the latest year partition. But it is a best practice to ProcessIndexes at the end of the transaction to rebuild the flexible aggregations and indexes that were dropped when dimension rollups changed during ProcessUpdate.
Yes if the Postal Code dimension changes only yearly you can omit it from your daily processing scripts.
For the measure groups with just one partition, processing the partition has the same effect as processing the measure group. I would recommend a ProcessData on measure groups or partitions that need reprocessing then a ProcessIndexes on the cube.

By the way beware of the Analysis Services processing task in SSIS. When you set it up the database ID is hardcoded in the command it builds and editing the connection string of the SSAS connection manager to point at a different SSAS database name won't change which database it processes. So that SSIS task only works well if you are sure you will be processing the same database ID on each server and won't ever need to process a second SSAS database on the same server.


I understand your frustration with this behavior.  There seem to be internal obstacles to implementing this and could lead to larger indexes than are necessary.

With a unique index supporting a primary key, constraint validation is done before the unique index is modified and one look-up can verify whether the entry can be inserted or not.1
With a non-unique index supporting a primary key, constraint validation is done after changes are made to the non-unique index and multiple look-ups are required to verify whether the entry can be inserted or not.1

A primary key using the leading column of a unique index would require non-unique index constraint logic on a unique index.  This would require changes and/or additions to the logic.

If id is unique/primary then (id,val) is inherently unique, and normally you would not need/want a unique constraint/index on (id,val).

You might want a unique index on (id,val) if there were a query referencing (id,val) and you wanted to prevent a table look-up.  If this is your situation, you may have to decided which is the lesser tradeoff between allowing the table look-up, increasing the primary key to (id,val) or having two unique indexes.  For most situations I suspect the table look-up would be preferable.


Oracle - ORA-01654: unable to extend index in tablespace

This error code, [ORA-01654], means the server has failed to allocate an extent of the required number of blocks for an index segment in the specified tablespace. As you have said you have got 13 datafiles in your tablespace, you are using small file tablespace. In order to get rid of this error, you can simply add data file using ALTER TABLESPACE ADD DATAFILE command to the specified tablespace.

First of all, you need to identify in which data file or files your index is being stored. Your index might have expanded into multiple data files. To identify the data files you can use the following SQL statement.

select a.owner, a.index_name, b.file_name
from dba_indexes a, dba_data_files b
where a.tablespace_name=b.tablespace_name and a.index_name='Your index name'


SQL Server Express averages 2-3% CPU but gets stuck at 100% sometimes

It's probably related to  your AutoClose setting.  Almost every resource you will find recommends turning this setting OFF.
Essentially it closes connections automatically, but it can cause a lot of issues since it's not done well.  It can and does cause all sorts of mysterious performance issues.
Here's a post from Buck Woody about the reasoning.
Here's a blog post from another SQL Server MVP about a situation where it caused issues.
Step 1 should be turning that setting off, then testing for a while.
There was a request to deprecate the feature but it looks like they will be leaving it in.


how to display running count of records in query

You'll want to use a window function such as ROW_NUMBER() for this one like so:
SELECT CUSTOMER_ID, TRANSACTION_ID, ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY TRANSACTION_ID) AS [Count] -- Running incremental count of rows / TRANSACTION_ID per CUSTOMER_ID
FROM TRANSACTIONS
GROUP BY CUSTOMER_ID, TRANSACTION_ID
ORDER BY CUSTOMER_ID, TRANSACTION_ID


How to efficently handle large amounts of insert statements

If each line of the file is a well-formed SQL INSERT statement, listing target columns and has a VALUES clause you can simply submit is as input using the command line tool of your choice. It will be slow but it will work. It would be better to split the one 30GB file into many smaller files (perhaps about 100MB each?) and have a script submit each in succession. Then failures can be more easily recovered, the server will become less flooded, and the load can be halted and resumed as required.

If the file can be converted to raw data, say a CSV format, that can be submitted using whichever ETL or bulk load tool you have. Again, I'd split the source into multiple smaller files for easier management.

Of course getting the source files as close as possible to the server will reduce run-time network latency.

Finally, disable all triggers, foreign keys, secondary indexes etc. on the target table. They can be rebuilt or rechecked after all the data is in place. It will be faster overall than having them checked for each row as it is processed. Make sure the DB has sufficient disk to handle the new data and the DBMS can get to it. Figure out how you'll manage log growth as data is processed. Halt all other activity on the server while the load is in progress.


is there a way to run the same job multiple times without scheduling it?

I've been using the following stored procedure (I can't even remember where I got the code from) for a long time.  It allows you to start a job and waits for the job to complete before returning control to the user.  You could stack 15 occurrences of this call in a tsql script

CREATE procedure [dbo].[StartAgentJobAndWait](@job nvarchar(128), @maxwaitmins int = 5) --, @result int output)
as begin

set NOCOUNT ON;
set XACT_ABORT ON;

    BEGIN TRY

    declare @running as int
    declare @seccount as int
    declare @maxseccount as int
    declare @start_job as bigint
    declare @run_status as int

    set @start_job = cast(convert(varchar, getdate(), 112) as bigint) * 1000000 + datepart(hour, getdate()) * 10000 + datepart(minute, getdate()) * 100 + datepart(second, getdate())

    set @maxseccount = 60*@maxwaitmins
    set @seccount = 0
    set @running = 0

    declare @job_owner sysname
    declare @job_id UNIQUEIDENTIFIER

    set @job_owner = SUSER_SNAME()

    -- get job id
    select @job_id=job_id
    from msdb.dbo.sysjobs sj
    where sj.name=@job

    -- invalid job name then exit with an error
    if @job_id is null
        RAISERROR (N'Unknown job: %s.', 16, 1, @job)

    -- output from stored procedure xp_sqlagent_enum_jobs is captured in the following table
    declare @xp_results TABLE ( job_id                UNIQUEIDENTIFIER NOT NULL,
                                last_run_date         INT              NOT NULL,
                                last_run_time         INT              NOT NULL,
                                next_run_date         INT              NOT NULL,
                                next_run_time         INT              NOT NULL,
                                next_run_schedule_id  INT              NOT NULL,
                                requested_to_run      INT              NOT NULL, -- BOOL
                                request_source        INT              NOT NULL,
                                request_source_id     sysname          COLLATE database_default NULL,
                                running               INT              NOT NULL, -- BOOL
                                current_step          INT              NOT NULL,
                                current_retry_attempt INT              NOT NULL,
                                job_state             INT              NOT NULL)

    -- start the job
    declare @r as int
    exec @r = msdb..sp_start_job @job

    -- quit if unable to start
    if @r<>0
        RAISERROR (N'Could not start job: %s.', 16, 2, @job)

    -- start with an initial delay to allow the job to appear in the job list (maybe I am missing something ?)
    WAITFOR DELAY '0:0:01';
    set @seccount = 1

    -- check job run state
    insert into @xp_results
    execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

    set @running= (SELECT top 1 running from @xp_results)

    while @running<>0 and @seccount < @maxseccount
    begin
        WAITFOR DELAY '0:0:01';
        set @seccount = @seccount + 1

        delete from @xp_results

        insert into @xp_results
        execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

        set @running= (SELECT top 1 running from @xp_results)
    end

    -- result: not ok (=1) if still running

    if @running <> 0 begin
        -- still running
        return 0
    end
    else begin

        -- did it finish ok ?
        set @run_status = 0

        select @run_status=run_status
        from msdb.dbo.sysjobhistory
        where job_id=@job_id
          and cast(run_date as bigint) * 1000000 + run_time >= @start_job

        if @run_status=1
            return 1  --finished ok
        else  --error
            RAISERROR (N'job %s did not finish successfully.', 16, 2, @job)

    end

    END TRY
    BEGIN CATCH

    DECLARE
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    SELECT
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    SELECT @ErrorMessage =
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
            'Message: '+ ERROR_MESSAGE();

    RAISERROR
        (
        @ErrorMessage,
        @ErrorSeverity,
        1,
        @ErrorNumber,    -- original error number.
        @ErrorSeverity,  -- original error severity.
        @ErrorState,     -- original error state.
        @ErrorProcedure, -- original error procedure name.
        @ErrorLine       -- original error line number.
        );

    END CATCH

end

GO


How do I replicate a MySql server's data without using mysqldump?

The question essentially equals this one: "what is a fast backup + recovery method for MySQL". This is because setting up MySQL replication is essentially taking a backup of some sorts, and opening it on another machine (and then syncing the slave with the master, which is a simple operation).

So it boils down to the fact mysqldump is too slow for you (makes sense). I've just answered the same here, but here are the highlights:

Percona Xtrabackup is a common and tested (and free) backup tool. It is of interest to you if you're using InnoDB since it makes a hot backup of the InnoDB tablespace, with small impact on the running server. The resulting backup reflects the time at which the backup process ended, which means shorter initial lag for your slave.
File system snapshots, via filesystems such as ZFS, or via LVM, or otherwise disk based snapshots, are all good solutions. Some (LVM in particular) making for more load than others.

What's common to the above solutions is that they make for a binary backup, not a logical one.

For your next question - yes. You can use MyDumper, which makes for a multithreaded backup (complemented by the multithreaded MyLoader tool). This backup is logical, and uses mysqldump internally, concurrently. It is typically a few times faster than standard mysqldump.


2 SSRS Instances, one in native mode and one in sharepoint integrated mode with PowerPivot and PowerView on same server

So after messing with this off and on for a while I figured it out. It is possible. All this was done on one server with SharePoint 2013 enterprise and SQL Server 2012 Enterprise with SP2.

I have a default SQL server instance with all features selected, specifically the shared features "Reporting Services - Sharepoint" and "Reporting Services Add-in for Sharepoint Products". Then you have to run through the installer again and add a new instance. Then select "SQL Server PowerPivot for Sharepoint" (relational database check box at this point was not needed). After you do this you have to activate this feature in SharePoint. Here is a blog on how to do the PowerPivot setup.

Make sure this instance is at the same service pack level once you install it. After this I followed this walk-through, starting at step 2, to enable SSRS in SharePoint.

That is pretty much it. I now have a native SSRS site and SharePoint integrated SSRS site as well as PowerPivot/PowerView all on the same server. All databases are on the default instance. I only have SSAS installed for the PowerPivot instance and that is it.


If your table is named Articles, do you name your column article_number or simply number?

I would do article_id. It is tempting to just make it "id" but when it starts becoming a FK in other tables, it is nice to have the column have the same name on both sides.


SSMS Registered Servers with ReadOnly Application Intent

SQL Management Studio (versions prior to 2016)

Unfortunately, there are some caveats that make using Application Intent in SQL Management Studio somewhat painful:

To connect manually with ReadOnly intent, after bringing up the Connect
       to Server dialog from the Object Explorer, users must remember to:

Click Options >>.
Go to the Additional Connection Parameters tab.
Enter the additional parameter as ApplicationIntent=ReadOnly;
(Note: Users must not click the Options << button after entering
        the Additional Connection Parameters or the parameters will be
        lost.)
Click Connect.
Always launch query windows by right-clicking on the desired database in the Object Explorer view and choosing New Query to avoid running into the #3 caveat below.

The caveats that apply are as follows:

Although you can get SQL Management Studio to connect with Read Only Intent, it does not store the Additional Connection Parameters when a connection is added to Registered Servers.
Behavior when hand editing the locally registered servers in the RegSrvr.xml file to add the Application Intent is extremely inconsistent and will be overwritten any time a change is made through the GUI making this workaround unreliable.
The Always On database must be selected before the query window is opened; otherwise, the connection gets routed to the primary server. If you attempt to select the database using the query window's drop down after the query window has already been opened to a non-Always On database, you will get an error dialog. If you try to change the database to an Always On database with a USE statement after the query window has already been opened to a non-Always On database, the results look like this when you attempt to execute the SQL query:

      Msg 979, Level 14, State 1, Line 1
      The target database ('AlwaysOnDatabase') is in an availability group
      and currently does not allow read only connections. For more
      information about application intent, see SQL Server Books Online.

SQL Management Studio (versions 2016 or later)

SQL Server Management Studio 2016 or higher can connect with Read Only Application Intent (using the same 6 steps as prior versions) and it does store the Additional Connection Parameters. There are still some caveats:

The Object Explorer view will not enumerate any of the tables or
other objects in the AlwaysOn databases. Attempting to expand them
results in an access denied message.
You cannot have a Read Only and a non-Read Only connection to the
    same listener open at the same time.
Intellisense for object names in the database does not work. (Oddly
    enough, Objects get enumerated just fine in the Query Designer that
    you can launch using Design Query in Editor... from the
    right-click menu.)
Caveat 3 from the prior versions' caveats still applies.

Third-Party Products

LinqPad stores the whole connection string including Application Intent and the database when you save a connection and therefore might be a viable option for performing Read Only queries against Always On databases.


Why would mysql "show global status" query be taking 15 minutes?

That sounds very unusual, but not surprising. All it takes to run SHOW GLOBAL STATUS; is the USAGE privilege, which is just general connectivity as stated in the last sentence of the first paragraph of the MySQL Documentation on SHOW STATUS:

This statement does not require any privilege. It requires only the ability to connect to the server.

Given these facts, there can only be two possible causes:
POSSIBLE CAUSE #1
In MySQL 5.1 and MySQL 5.5, values for global status are accessible via the INFORMATION-SCHEMA database

INFORMATION_SCHEMA.GLOBAL_VARIABLES in MySQL 5.5
INFORMATION_SCHEMA.GLOBAL_VARIABLES in MySQL 5.1

Here is the table layout:
mysql> show create table information_schema.global_status\G
*************************** 1. row ***************************
       Table: GLOBAL_STATUS
Create Table: CREATE TEMPORARY TABLE `GLOBAL_STATUS` (
  `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Notice it is a MEMORY table. Like MyISAM, all INSERTs, UPDATEs, and DELETEs into a MEMORY table require a full table lock (See Locking Granularity for MEMORY Storage Engine). There has to be some intermittent lock to populate values into this table. Since this table is local to the DB Connection, only the DB Connection is affected.
POSSIBLE CAUSE #2
User authentication may be blocked for a time due to host caching and DNS. Note paragraph 2 the MySQL Documentation on DNS Lookup Optimization and the Host Cache:

For each new client connection, the server uses the client IP address to check whether the client host name is in the host cache. If not, the server attempts to resolve the host name. First, it resolves the IP address to a host name and resolves that host name back to an IP address. Then it compares the result to the original IP address to ensure that they are the same. The server stores information about the result of this operation in the host cache.

If you do not have skip-host-cache and skip-name-resolve configured, please add these to my.cnf:
[mysqld]
skip-host-cache
skip-name-resolve

and restart mysql ASAP.
EPILOGUE
Cause #2 is a bit of a stretch since authentication should not trigger an entry into the slow query log.
Cause #1 is a lot more likely because if the global status is being loaded into a table, it is an SQL query with discernible passage of time.Thus, if the query's running time (in seconds) exceeds long_query_time, it will get recorded into the slow query log.


MySQL update based on main table data with cross table subquery

First of all, the fact that two tables don't have ids in common does not mean you cannot join them. You can JOIN two tables using any boolean expression. This can be table1.id = table2.id, but in can also be table2.latitude = table1.lat AND table2.longitude = table1.latitude.

Let's assume these are your tables:

SELECT * FROM table1;

id |       lat |       lon
-: | --------: | --------:
 1 | 41.390205 |  2.154007
 2 | 40.416775 | -3.703790
 3 | 41.979401 |  2.821426
 4 | 40.304665 | -3.723679

SELECT * FROM table2;

 id |  latitude | longitude
--: | --------: | --------:
101 | 41.390205 |  2.154007
102 | 40.416775 | -3.703790
103 | 41.979401 |  2.821426
124 | 43.262985 | -2.935013

We could perform your original query with a subquery, and get:

SELECT
    table1.id,
    table1.lat,
    table1.lon,
    (SELECT id
       FROM table2
      WHERE latitude=table1.lat AND longitude=table1.lon
      LIMIT 0,1
    ) AS subqueryresult
FROM
    table1

id |       lat |       lon | subqueryresult
-: | --------: | --------: | -------------:
 1 | 41.390205 |  2.154007 |            101
 2 | 40.416775 | -3.703790 |            102
 3 | 41.979401 |  2.821426 |            103
 4 | 40.304665 | -3.723679 |           null

(Nearly) the same can be done with a JOIN through the following query:

SELECT
    table1.id, table1.lat, table1.lon, table2.id AS table2_id
FROM
    table1
    JOIN table2 ON table2.latitude = table1.lat AND table2.longitude = table1.lon

id |       lat |       lon | table2_id
-: | --------: | --------: | --------:
 1 | 41.390205 |  2.154007 |       101
 2 | 40.416775 | -3.703790 |       102
 3 | 41.979401 |  2.821426 |       103

(If you want to get the row with no match on table2, you can use LEFT JOIN instead of JOIN).

If you want to UPDATE table1.id to be the same as table2.id, you can do so on MySQL by using:

UPDATE
    table1
    JOIN table2 ON table2.latitude = table1.lat AND table2.longitude = table1.lon
SET
    table1.id = table2.id

SELECT * FROM table1

 id |       lat |       lon
--: | --------: | --------:
  4 | 40.304665 | -3.723679
101 | 41.390205 |  2.154007
102 | 40.416775 | -3.703790
103 | 41.979401 |  2.821426

Note that this is not standard SQL, which does not allow to have a JOIN on this part of an UPDATE. If more than one matching row exists on table2, the value used for the update could be any of the matching ones.

The standard SQL way to do the same thing is:

UPDATE
    table1
SET
    id =         /* Your subquery! */
    (SELECT
        table2.id
    FROM
        table2
    WHERE
            table2.latitude = table1.lat
        AND table2.longitude = table1.lon
    ORDER BY id     /* good to have a criterion to select id */
    LIMIT 1
    )
WHERE
    EXISTS     /* The subquery actually returns something */
    (SELECT
         table2.id
    FROM
        table2
    WHERE
            table2.latitude = table1.lat
        AND table2.longitude = table1.lon
    ) ;

Or:

-- Standard SQL
UPDATE
    table1
SET
    id = coalesce(
        (SELECT
            table2.id
        FROM
            table2
        WHERE
                table2.latitude = table1.lat
            AND table2.longitude = table1.lon
        ORDER BY id
        LIMIT 1
        ), table1.id
    ) ;

You can check everything at dbfiddle here


Importing True/False as tinyint failing when last field


Can I downgrade SSIS packages to run on older version of SQL

SSIS Downgrade should do exactly what you need.


TRY/CATCH only reports the last (and most useless) error

You can try using THROW to get the full exception raised:

If the THROW statement is specified without parameters, it must appear
inside a CATCH block. This causes the caught exception to be raised.

begin try
    BACKUP DATABASE @DB TO DISK = @targetfile
    WITH DIFFERENTIAL, RETAINDAYS = 2, COMPRESSION,
    STATS = 4
end try
begin catch
    SELECT ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState
            , ERROR_MESSAGE() as ErrorMsg;
    THROW;
end catch

Result

Msg 3035, Level 16, State 1, Line 8 Cannot perform a differential
backup for database "...", because a current database backup does
not exist. Perform a full database backup by reissuing BACKUP
DATABASE, omitting the WITH DIFFERENTIAL option. Msg 3013, Level 16,
State 1, Line 8 BACKUP DATABASE is terminating abnormally.


Configuring Oracle Data Guard or GoldenGate Standby DB replication while restricting DELETE DMLs

You can not do this with a physical standby, you will need some kind of logical replication.

With a logical standby, you can skip DMLs, but that would skip all DMLs.

With Oracle Goldengate, you can easily skip deletes, as you can specify the IGNOREDELETES option: IGNOREDELETES

Ok, now let's assume you have configured your system to ignore deletes. Imagine the following situation:

insert into mytable (unique_column, other_column) values (1, 1);
commit;

delete from mytable where unique_column = 1;
commit;

insert into mytable (unique_column, other_column) values (1, 2);
commit;

The above will break your replication because of the unique constraint violation. If you ignore the error, you will not have fresh data in the destination database.

It sounds to me you want to build a history table, but there is more to that than just ignoring delete operations, have a look at the below support note:

Oracle GoldenGate Best Practices: Creating History Tables (Doc ID 1314698.1)


Is Postgres sharding available on Aurora (AWS)

From @nathanlong's comment to my question:

  On Sep 25, 2018, they announced "Amazon Aurora with PostgreSQL Compatibility Supports PostgreSQL 10" - https://aws.amazon.com/about-aws/whats-new/2018/09/amazon-aurora-postgresql-compatibility-supports-postgresql104/. They explicitly mentioned "native table partitioning", though there are features in PosgreSQL 11 that presumably are not included; see https://pgdash.io/blog/postgres-11-sharding.html.


Denormalization to the 'Vehicle' table is still option, technically, but based on the responses in the comments I would probably sub-class it as well. So, you're at the options I mentioned in my last comment.  There's no magic bullet.  It's string concatenation for dynamic SQL or separate statements. One way or another you're going to have to change your app code.


Incorrect statistics are used on index seek

The estimate for the inner (lower) input to a nested loops join is per iteration in SSMS. The 'actual' number of rows shown in SSMS is a total over all iterations. This often causes confusion, and is the result of a questionable design decision.

The one row estimate therefore needs to be multiplied by the estimated number of executions. Many people prefer to view plans in SQL Sentry Plan Explorer, which does this for you, so the numbers are directly comparable (both estimate and actual are shown over all iterations).

Using a temporary table to materialize a relatively small result to improve cardinality estimation is a very valid tuning technique. If the code executes as part of a function or stored procedure, you may need to manually update statistics on the temporary table and request a recompile to get the best plan in some circumstances, see my article Temporary tables in Stored Procedures for details.

There are still times when the optimizer will choose the 'wrong' join type even with good cardinality and distribution information, but these are relatively rare. You certainly maximize your chances of getting a good plan by ensuring the optimizer has accurate information to base its decisions on.

I realize this answer is a bit generic, but there's only so much to be said on the basis of a couple of partial execution plan images. If you add showplan XML for both queries to the question (link to an off-site host e.g. gist), I might may be able say more.


How can I import an old pgdump into an updated schema?

It sounds you are dealing with some 3rd party's application.  In other words, the app vendor and your predecessor are not the same person.

The 3rd party would generally provide you with two things, an installer to use for new customers, and an upgrade process to upgrade the schema between versions of their app for existing customers.  You either need to use one, or the other, not some bastardized combination between them.  Restoring a dump of the old schema+data over the top of a newly-initialized schema for new customers would not be expected to work, unless the vendor said it does.

The vendor might also provide instructions on how to upgrade your PostgreSQL version, either by itself or coupled with upgrading the application version.  If they do, then find and follow those instructions.  If they don't then you have a few options.

Go back to using the old database.  Run the upgrade scripts against it.  Worry about migrating that to a new database server later.
Create an empty database cluster (completely empty, not using the vendor's new-customer initialization stuff) and restore to that database cluster.  The easiest way to do that is probably to use the "-C" (note upper case) flag to pg_restore.  This will create the new database for you, with the name, encoding, etc. which was recorded in the dmp file.  Alternatively, perhaps you just use your existing cluster (initialized with the app vendors new-customer schema) and add the -C flag to your restore.  What that will do is drop the database they provided in-bulk (which will get around the dependency problems) and recreate it from what is in the dump file, meaning you get the old schema not the new one.  Either way, you will now have the old application schema running in a new PostgreSQL database.  Once this is working, then run the application upgrade script against this.
Ask the 3rd party app vendor what to do.


How to count multiple occurences on the same date


SQL 2016 AlwaysOn Database Health Level Detection

If I restore a database, the state of the database will be RESTORING; will that trigger a failover event?

No, because you can't restore a database that is in an AG. Thus, to restore it'd have to be out of the AG and wouldn't be subject to database level health detection.

  ... my understanding is that the database enters a Suspended state, which would make me expect the databases to failover as well.

Suspended isn't a database state - suspect is, not sure which you meant. Also, the key with this is that it may "attempt" to failover but doesn't mean it will be successful.

  So, is "Database Level Health Detection" only recommended if your cluster is only being used for one application?

It works at the Availability Group level, that's up to you on how and what you put into your AGs. You might have a single app in an AG or you might have multiple. Personally, I would only put the databases that need to be together in the same AG and not co-mingle any other application databases.

  For example, one of our clusters is going to host about 50 different databases used by our Intranet; if one of those databases needs to be restored or has corruption, I would not want the other 49 to have a fail over event and a small outage, or would that not be an issue?

If your entire intranet relied on all 50 databases being on the same instance, then yes I'd put them all together... whether or not you want the AG to failover if a single database (might be the most important one or the least important one) has a state change. I can't make that business decision for you. I also can't change the need to have all 50 together.


Why does pre-sorted index not help me in window function?

You haven't shown that it doesn't spot the index, only that it doesn't use it.  If you set enable_sort = off; then what happens?
If the table is not well vacuumed and the correlation of team_id with the physical row ordering is low, then reading the index in order will mean hopping all over the table to check that each row from the index is actually visible, leading to the expectation of a lot of costly random IO.  This will be slower (or is expected to be slower) than a nice sequential scan followed by a sequential-IO-optimized sort.


You don't need the derived table, you can use the window function to order by the sum.

The LEFT JOIN works as an INNER JOIN as columns from both tables appear in the WHERE clause, so it's probably better to explicitly state it as an inner join.

I added the school_name in the joining condition. It seems like it be there but it's not very clear in the question description. (Could a player be in one school and appear in a different school in the double table? The foreign key definition indicates that it can't.)

You state that you want "for each player, its total score over all tournaments" but the code has WHERE tournament_number >= 2. Which is correct?

SELECT d.player_name_fkey,
       d.school_name_fkey,
       sum(d.score) AS sum_scores,
       rank() OVER w AS scorerank
FROM players AS p
    JOIN double AS d
    ON  p.name = d.player_name_fkey
    AND p.school_name = d.school_name_fkey
WHERE p.gender = 'Masculin'
  AND d.category = 'Benjamin'
  AND d.tournament_number >= 2
GROUP BY d.player_name_fkey,
         d.school_name_fkey
WINDOW w AS (PARTITION BY d.school_name_fkey ORDER BY sum(d.score) DESC) ;


Data distribution in mongos with shards or replica sets

So let's look at the "roles" here represented in the referenced architecture. Keep in mind, this is the "2 cent tour" of what the architecture is all about.

Mongos

This process is the "router" in that it acts as the interface for a "client" to connect to a sharded cluster. You can have many mongos instances, so you are not limited to "one" only. As pointed to in the diagram, these communicate with the "config servers" in order to determine which "shard" to send requests to based on the query or operation submitted.

Config Server

These are the "brains" of a sharded cluster. Essentially they contain the "meta data" which says which "chunks" or "parts of the data" appear on which "shard" as it were. These stay consistent with each other in a "reasonably redundant way" in order to provide that information to the mongos "router".

Shard

Mostly a "meta" concept here. Though it is possible have a single server as a "shard" it is generally recommended not to and to use a "replica set" as a shard node otherwise. The purpose of a "shard" is to hold data "specific" to a "defined range". This works by a "shard key" that is "split" between "nodes" of the sharded cluster. The full nature of the "splitting" is determined by the "shard configuration" which lives on the "config servers" already described. The data on a "shard" only lives on that shard and "no-where else".

Replica Set

The purpose of a replica set is rather self explanatory. It essentially "copies" data from a given "Primary" node which is the only node that accepts "write" operations. This is done to what is one or more "Secondary" nodes. You only need one more "Secondary
 node for a replica set, but if you do that then you really need another "Arbiter" node in order to break the deadlock on elections.

What "elections" means is that when one member of the replica set goes down or is otherwise un-available, there is an "election" held to determine which one then becomes the new "Primary". There needs to be a "majority" vote here, hence the requirement for an "odd number" of members that can vote (not all member types can vote).

Summary

There is no "duplication" over shards. There may be a "brief" time in which the same data appears on one shard and another at the same time, but this is never reflected in the "config servers" ( if it is then this is corruption ). There is a process called "chunk migration" which is what all of this is about, where data allocated to one shard is "balanced" to exist on another. But the "meta data" mentioned earlier is "atomically updated" so there should be a consistent view of where the true source of data actually resides.

The "replica set" members are where there is intentional duplication. This is for data redundancy, where if one member goes down then there are other members to carry on the work. They "should" have a consistent view, but this generally is largely determined by "write concern" in your application as to the "guarantee" of consistency. Still, there may be problems if only rare. (But network partitions in the cloud happen more often than not).

The role of "shards" is opposite to the "redunancy" of replica sets. Their role is to "distribute" data. That means by definition that data is not "duplicated" over shards (moment case already given) so that general CRUD operations are either "distributed" or "targeted", across or directed to as appropriate the relevant "shard" that is going to deal with the data for your operation.

In real short. Shards are for "data distribution" in order to make operations scalable, by either "targeting" workload to a specific "shard node" or "splitting" the workload across many nodes. Whatever is appropriate to your application purpose.

Always different data across shard nodes, and that is what they exist for.


SQL Server job failure email without Internet connection

You just need to enable mail profile and get the email exchange server IP and listening port as well as a valid active directory mail account for setting up email alert notification for jobs as well as alert on multiple things(dis.

Please check below link for setting up mail profile and operator:

https://www.mssqltips.com/sqlservertip/1523/how-to-setup-sql-server-alerts-and-email-operator-notifications/

https://sqlbackupandftp.com/blog/how-to-set-up-sql-server-email-job-notifications

For setting up notification on alerts:

https://www.sqlshack.com/create-configure-sql-server-agent-alerts/

Hope above helps.


Use plpgsql to count records selected for output AND output those records

I don't get your example, but if that's just an illustration of the question in the heading, the answer is a window function:

SELECT /* all your columns and expressions */,
       count(*) OVER () AS total_count
FROM /* the rest of your query */

This will append a column to each result row with the total number of result rows.

The window function is called after GROUP BY, but before ORDER BY and LIMIT.


As provided by the OP:

As stated in here, the way to proceed was to initiate mysql daemon in safe mode, just as follows:

Start server bypassing privilege system
$ sudo mysqld_safe --user=root --skip-grant-tables
Just afterwards, you must be able to connect to your mysql instance by console (using mysql command) or by any gui client (e.g. MySQL Workbench).

My guess is that the database system became broken after the latest update (perhaps an unsafe power off, reboot, or so on...). As for now, my MySQL databases will be placed on the NAS, as MySQL won't be updated there. I'll also schedule backup tasks.


You should rebuild the system databases.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

This would not remove the data and log files relating to the old databases - it just removes all references to them. You would have to manually remove the files to free up the disk space.

You must also install all service packs and hotfixes again.


Hybrid Synchronous / Asynchronous Replication?

It's simpler that you may expect.

Use one of the nodes in the Galera/PXC cluster as the master for the async replica.

The node acting as master for the slave needs the following additional configuration parameters for this to work:

log_bin
log_slave_updates
binlog_format = ROW

Connect an asynchronous MySQL/Percona/MariaDB slave to it using a normal asynchronous master/slave replication configuration.

That should be about it.  The slave ideally will be the same flavor and release series as the cluster nodes, either MariaDB or Percona on both ends, though a MariaDB slave is pretty much always a safe bet, regardless of the master.  In no case should the slave run an older version, so when you do upgrades, always upgrade the slave first.  You can also subtend additional slaves from the first one, or connect a second slave to a different cluster node as its master.

Bonus: enable slave_compressed_protocol on the slave so that the data streamed from the master is compressed on the wire.


The generalization

follows the covering constraint, so: E1 ⋃ E2 ⋃ E3 = E0
does not follow the overlapping 'constraint' ⇒ is disjoint,  so: E1 ⋂ E2 ⋂ E3 = ∅

The maximum and minimum amount of entities in E0 is 60. Since no overlapping is allowed there will be 15 + 20 + 25 = 60 distinct entities, which are all entities of superentity E0 at the same time because of the covering constraint.

E1 ⋃ E2 ⋃ E3 = E0 ⇒  max{|E1|, |E2|, |E3|}  ≤ |E0| ≤ |E1| + |E2| + |E3|

(E1 ⋃ E2 ⋃ E3 = E0  ⋀ E1 ⋂ E2 ⋂ E3 = ∅) ⇒ |E0| = |E1 + E2 + E3|


DTSInstaller.exe consumes a file called with an extension of SSISDeploymentManifest

The contents of it look like

<?xml version="1.0"?>
<DTSDeploymentManifest
    GeneratedBy="HOME\administrator"
    GeneratedFromProjectName="SSISHackAndSlash"
    GeneratedDate="2010-04-30T21:19:08.6336677-05:00"
    AllowConfigurationChanges="true">
        <Package>Package.dtsx</Package>
        <Package>Package2.dtsx</Package>
        <Package>Package7.dtsx</Package>
</DTSDeploymentManifest>

That file defines the universe of SSIS packages the DTSInstaller will operate upon.

It sounds like the manifest file you are using does not have match the list of packages in your project. You can right click on the project itself and select Properties, Deployment tab and change the value of CreateDeploymentUtility from false to true.

Now when you right click on your project to build, or deploy, your bin\Deployment folder will contain a new version of the manifest file. Double click on that and you should have all the desired packages ready for installation.

An alternate approach, would be to use your manifest file but have PowerShell generate the deploy commands. Advantage of that is you can audit what's being deployed versus the black box of DTSInstaller.exe PowerShell, dtutil, SSISDeployManifest - How to deploy SSIS packages with PowerShell


SQL Server doesn't have this functionality built in, but you could start with this DBA.se question:

Query to report disk space allocation and used space

Then based on the output, grow the log files appropriately.

I'll be honest though: I'm decent with T-SQL, and it'd take me longer to write that than it would to just change the threshold in my monitoring software so that it's more appropriate. Because here's the problem: if you start growing your log files like crazy, next thing you know, your monitoring software is going to be complaining about having less than 40% free space on your drives, hahaha.


SQL Server auto-trim of varchar value in equal comparison but not like comparison

My initial answer suggested that the ANSI_PADDING flag set to OFF may be to blame for the difference in behavior. However, this is incorrect; this flag only has an effect on storage, but not equality comparison.

The difference stems from Microsoft's implementation of the SQL standard. The standard states that when checking for equality, both strings left and right of the equality operator have to be padded to have the same length. This explains the following results:

insert into test_padding (varchar_clmn, nvarchar_clmn) values ('space ', 'nspace ')
go
-- equality for varchar column
select count(*) from test_padding where varchar_clmn = 'space' -- returns 1
select count(*) from test_padding where varchar_clmn = 'space ' -- returns 1
select count(*) from test_padding where varchar_clmn = 'space    ' --returns 1
-- equality for nvarchar column
select count(*) from test_padding where nvarchar_clmn = 'nspace' -- returns 1
select count(*) from test_padding where nvarchar_clmn = 'nspace ' -- returns 1
select count(*) from test_padding where nvarchar_clmn = 'nspace    ' --returns 1

The LIKE operator does not pad its operands. It also behaves differently for VARCHAR and NVARCHAR column types:

-- likeness for varchar column
select count(*) from test_padding where varchar_clmn like 'space' -- returns 1
select count(*) from test_padding where varchar_clmn like 'space ' -- returns 1
select count(*) from test_padding where varchar_clmn like 'space    ' -- returns 0
-- likeness for nvarchar column
select count(*) from test_padding where nvarchar_clmn like 'nspace' -- returns 0
select count(*) from test_padding where nvarchar_clmn like 'nspace ' -- returns 1
select count(*) from test_padding where nvarchar_clmn like 'nspace    ' -- returns 0

The behavior of the LIKE operator for the ASCII type is SQL Server-specific; for the Unicode type it is ANSI-compliant.


Is creating a joining/bridging table the most practical and efficient way of normalizing numerous M:M relationships in a database?

The real best differs case to case. May be after checking all of your 50 / 60 tables this could be arrived at. Though there is clear difference between 3NF and 6NF for sure, do not limit yourself on this.

For what you had explained, if and only if you wanted to have 6NF satisfied, the third table is essential. And you can have very simplest queries starting at class or starting at user.

But, going that way, for 50 / 60 tables, you will land up with too many cross reference tables which may be worth of having or not (depending upon your other tables by design). Remember that you are making all your queries bigger too. Also, being in 6NF sometimes affects scalability.

It is well normalized even if you just have CID within users table. I do not say - stick to just two tables and not three.

There are cases when you had worked with 50 / 60 tables with all cross reference tables in place for a while, you yourself need to denormalize to improve the performance. Though normalization is essential while designing the databases, it will not always lead to best performance.

Refer
https://en.wikipedia.org/wiki/Denormalization


sp_MS_marksystemobject doesn't appear to be working

Why are you using the master prefix? That is saying "go execute this procedure in the context of master."

Try just EXEC dbo.sp_WhereAmI; or even EXEC tempdb.dbo.sp_WhereAmI;...


Query turned into a CPU gobbling monster

The first query is so slow because it will do a full table scan on thing.table for every row in that table for which DAY(SnapshotDate) <> 1. So if you have 100k rows in the table in the worst case you'll do 100k scans which means reading through 10 billion rows. If the table is small enough it'll stay in memory so your parallel query will appear to burn through CPU.

You can tell by looking at the query plan carefully. The scan is on the inner side of a nested loop join. If that's not your cup of tea you can try live query statistics to see the query as it executes. That way you can get some of the information from the actual plan without needing the query to finish. There's no way to save off old actual plans without setting up extended events.

The second query is faster because the query optimizer is freer to rearrange the elements of the query due to the lack of the CASE expression. Instead of calculating the subquery for MAX(SnapshotDate) once per row the calculation is done once per query.

You'll definitely want to fix this query in some way or the execution time will continue to grow quadratically with the number of rows in the table. One workaround would be to add an index to the SnapshotDate column. The subquery will still execute once for each row but getting the maximum value will be a very cheap operation. A better way is to save off the value of the subquery to a local variable and to use that in your UPDATE query. Unless you have to worry about concurrency that shouldn't be an issue.

You can also stick to the fix that you found if you want. One suggestion that can help in some cases (depending on the table structure) would be to add a where clause to your first UPDATE:

UPDATE thing.table
SET YYYYMM = NULL
WHERE YYYYMM IS NOT NULL;


Why didn't primary file size change after moving large table to new file group?

Once you move data out of a filegroup on SQL Server, you must run a DBCC SHRINKFILE command to reclaim that physical space.

The official documentation has all you need to know about this, but the general technique is as follows (this example is from the documentation, unedited).

USE AdventureWorks2012;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GO

Note that this example assumes your database is called AdventureWorks2012. You'll have to substitute in the logical name for the file and database.

The recovery model change in the above example must be preceded and followed by appropriate backups. Make sure your backups are well-tested, and stored in a secure off-site location as well. While this particular command shouldn't cause any corruption, backups are critical.


How to get accurate row counts in partitions in a table

You need to ignore index_id with values that are not 0 (heap) or 1 (clustered index)

SELECT rows  FROM sys.partitions
WHERE object_id = object_id('name of table')
AND index_id < 2


How to do two actions in conditional UPDATE?

If mysql had window functions, it would be quite easy. However, it doesn't, and even though there are quite a few solutions that use variables, most of them (at least the ones I read) warn about potential problems due to the changing order of evaluation when it comes to emulation PARTITION BY part.

My solution doesn't use variables at all (I know, performance is gonna be terrible for big amount of data, but it seems to have no side effects). I hope it can be somehow useful :

I split the task into 2 parts , 1st - creating select statement that returns required rank/rownumber, and 2nd - update which is trivial (simple update ... join) .

(Example in http://sqlfiddle.com/#!2/5c3926/1, but I post it here as well )
Table :

 create table table1(table1_id int auto_increment not null primary key,
  col1 char(1) not null,col2 int );
insert into table1(col1)
values ('d'), ('a'), ('b'), ('y'), ('x')
, ('a'), ('g'), ('y'), ('d'), ('d'), ('d')
, ('t'),('b'), ('y'),('y');

SELECT which returns desired value (hopefully) in "rank_column":

select a.*,
  (
  select count(1) from table1 c
WHERE c.table1_id >
    IFNULL
    (
      (
       select MAX(table1_id) as next_id
       from table1 b where col1='Y' and b.table1_id <a.table1_id
      )
    ,0)

   and c.table1_id <= a.table1_id
  )
as rank_column
 from table1 a

Now update itself which just repeats previous select :

  UPDATE table1 curr_val
  INNER JOIN
  (
  select a.table1_id,
  (
  select count(1) from table1 c
WHERE c.table1_id >
    IFNULL
    (
      (
       select MAX(table1_id) as next_id
       from table1 b where col1='Y' and b.table1_id <a.table1_id
      )
    ,0)

   and c.table1_id <= a.table1_id
  )as rank_column

 from table1 a
)new_val ON (curr_val.table1_id = new_val.table1_id)
SET curr_val.col2 = new_val.rank_column


Oracle: What should be better, alter table CACHE or INDEXES?

indexes and caching serve different purposes:

indexes allow you to read only a (hopefully small) part of the table.
Caching reduces the time it takes to read a block as you only need logical (in buffer) reads instead of disk reads.

The two mix very well together as Oracle would happily use a cached index if it fits the query and statistics.

For example reading 10 rows via index will be faster then scanning 40gb table in memory, and if the index is cached then even more so.


How does MySQL still function even though I move .ibd file to arbitrary places?

Do NOT edit a .ibd file!  Things are likely to go wrong:

The layout of the file is complex, and you are likely to make it unreadable by MySQL.
The data is probably cached in RAM, so your changes are not immediately recognized and/or they will be overwritten when MySQL decides to rewrite the 16KB block you modified.
There could be a block split, further rendering your edits a mess.

As for "Moving" a file, let me give you a basic lesson in the *nix filesystem.  A file is (in simplistic terms) two things:  A chunk of data sitting somewhere on disk, and a directory entry pointing to that chunk of data.  When a "file" is "opened" (which MySQL did long ago), it grabs the pointer to the chunk of data, but does not look at the directory entry again.  You did mv, which only removes one directory entry and creates a new entry.  This new entry points to the same chunk of data on disk.

So, until, MySQL closes the file, it still has the file, regardless of what path(s) it is known by.

Note that that explains how "hard" links can work trivially.  A second reference (hard link) to the same file is simply a different directory entry pointing to the same chunk of data.

Please study the structure of *nix filesystem.  Your incomplete knowledge is rapidly propelling you into disaster!

By the way, if you leave the .ibd in its new location. MySQL won't find it after it restarts.  You will have "lost" that table, and have a repair situation that could be baffling.


Backup and Restore SQL server files and filegroups

Query the backup file with

RESTORE HEADERONLY FROM DISK = 'D:\sec_file_ndf.bak'

and

RESTORE FILELISTONLY FROM DISK= 'D:\sec_file_ndf.bak'

...to verify the contents of your D:\sec_file_ndf.bak file.

You might have multiple backups in one *.BAK file. If so add the option FILE = 2 to your RESTORE command to restore your most recent backup of the *.NDF file.


Maybe something like this:

SELECT
  p.last_name,
  p.first_name,
  a.street,
  a.house_nr,
  a.post_code,
  a.city
FROM persons AS p
LEFT JOIN addresses AS a
WHERE p.id=42


Improve select rows from table1 in table2 but not in table3

Unless I'm misunderstanding your question, I would think a combination of EXISTS and NOT EXISTS would give you what you want.

--Setup
set nocount on
DECLARE @Code TABLE (CodeId INT)
DECLARE @Aggregation TABLE (AggregationId INT)
DECLARE @AggregationChildren TABLE (AggregationChildrenId INT)

INSERT INTO @Code (CodeId)
VALUES (1),(2)

INSERT INTO @Aggregation (AggregationId)
VALUES (1),(2)

INSERT INTO @AggregationChildren (AggregationChildrenId)
VALUES (1)

--The query
SELECT c.CodeId
FROM @Code c
WHERE EXISTS (
        SELECT *
        FROM @Aggregation
        WHERE AggregationId = c.codeid
        )
    AND NOT EXISTS (
        SELECT *
        FROM @AggregationChildren
        WHERE AggregationChildrenId = c.codeid
        )

| CodeId |
|--------|
| 2      |


PostgreSQL function that returns a 'casted' complex type from query

CREATE OR REPLACE FUNCTION delivery_last_contract(delivery bigint)
                   RETURNS last_contract AS $func$
  DECLARE ret last_contract;
  BEGIN
    SELECT tcf.contract_id, tcf.date
      INTO ret
      FROM task_delivery_fetch tdf
      INNER JOIN task_contract_fetch tcf ON tcf.fetch_id = tdf.fetch_id
     WHERE tdf.delivery_id = delivery
     ORDER BY tdf.delivery_id, tcf.contract_id DESC
     LIMIT 1;
    RETURN ret;
  END;
$func$ LANGUAGE plpgsql;

works. Simpler is better.


How to check if the SYSTEM_VERSIONING for a table is ON?

You could query sys.tables

ALTER TABLE EmpSalary SET (SYSTEM_VERSIONING = On );
select name, temporal_type, temporal_type_desc from sys.tables where name = 'EmpSalary'

| name      | temporal_type | temporal_type_desc              |
|-----------|---------------|---------------------------------|
| EmpSalary | 2             | SYSTEM_VERSIONED_TEMPORAL_TABLE |

ALTER TABLE EmpSalary SET (SYSTEM_VERSIONING = OFF );
select name, temporal_type, temporal_type_desc from sys.tables where name = 'EmpSalary'

| name      | temporal_type | temporal_type_desc |
|-----------|---------------|--------------------|
| EmpSalary | 0             | NON_TEMPORAL_TABLE |


What type of datastore should I use for high volume time series data?

A 'data logging' approach to the problem
For anything involving 50,000 inserts a second you will need to batch your inserts.  If you have to run it on cheap hardware then you need to look at the efficiency of your I/O.
If you assume:

Up to the second latency is not necessary (i.e. batched writes are acceptable)

You have no requirement for analytics within the application except your graphs by device.

Any further analytic requirements can be met by batching data into an external system

Some administrative overhead is acceptable in adding devices to the system (i.e. they have to be registered).

A polled once per second read is acceptable - incoming items can be batched and recorded on a per-second basis with a null recorded if a datum is not produced by a given device within that period.

Then you can store one row per second with all the devices de-normalised into an array of readings.  The application maintains a registry of offsets into this array by device.  As you add more devices the BLOB simply expands.
This changes your problem to one of storing one 50K-ish BLOB per second indexed on the time, which can be done by pretty much any DBMS platform that supports BLOBs.  You might even be able to use a key-value pair system such as Berkely DB.
50K per second is 180MB per hour, 4.3GB per day and approximately 1.5TB per year.  This should be possible to manage even with fairly modest hardware.  Depending on how much you need to archive you can periodically clear down historical data.  You will need something that supports partitioned tables to do this efficiently, though.
Getting the data back out
One disadvantage of this approach is that you would have to read your entire data set to query the statistics for any given device, which would imply scanning 4GB of data for a single day.  If you need to support a lot of users querying the devices on an ad-hoc basis you will need to find a way to supplement this store with a fast querying capability.  Some possibilities for this are:

One hour's data is approximately 180MB in memory.  Cache a rolling period of a few hours - either within your application, or in leading partitions on a fast store (maybe a SSD) that can be archived off on an hourly basis.If your applications that displayed the graphs queried this on a rolling basis (i.e. only got the most recent data) then this structure could be keyed on time and still support a significant query workload.You may need to benchmark the BLOB reader performance of your platform to judge whether you need to build additional caching within your middle-tier, although you will almost certainly need a middle tier server application of some description to avoid shipping the entire BLOB record out to the client.

If you only expect to monitor a small subset of sensors at any given time, then you may be able to run caching on a per-sensor basis, with the cache living for some period (e.g. 10 minutes) after the last read for that sensor.  The main cache would push values out into the per-sensor cache until the per-sensor cache expires.  Per-sensor caching could be implemented fairly efficiently as a ring buffer of 1-2 hours (or whatever period seems appropriate) worth of sensor readings with supplementary data for the beginning and end periods.  A time period should then resolve into an offset within the ring buffer.  In this case the memory overhead per sensor amounts to a few bytes of header information.

Calculate aggregates of average readings for each minute, 10 minutes, hour or suitable period, and store those in a supplementary table.  Graphing data over longer time periods can use the aggregates.

Take the aggregate values and un-pivot them into a dimensional structure in a supplementary data mart (i.e. with time and sensor ID dimensions).  This can be done on (for example) a nightly basis.  Ad-hoc queries on historical data can come from the data mart.

Some combination of these approaches will allow quick access to near-realtime data with a fair amount of read traffic.  Even if you have to cache this with your application and write something hand built to do it.
Administrative overhead
A sensor will have to be registered with the system and given an ID (essentially the ID is - or maps to - an array index within the BLOB).  As the BLOB is opaque the database doesn't need to care about columns.  A KVP store that can deal with BLOBs efficiently may be sufficient.
A sensor can be registered with the system, which will then start recording data.  You may need to explicitly record start/end dates of sensor connections, and maintain metadata that links sensor IDs to offsets within the array.  You will need to recycle array slots as it's a static structure.  Any middle-tier caching will have to be aware of this as well.
Message queues
You may wish to consider an asynchronous architecture using a transactional queue manager such as RabbitMQ to manage spikes in the read workload with the regular writes.  The logging process and a query fronting process would collect requests to be processed in a batch queue.  This would smooth out spikes in the system load.  If you anticipate a significant ad-hoc query workload you may wish to look into this.
Pros and Cons - summary
This structure is optimised to minimise the storage space requirements, which should in turn minimise the I/O overhead on the system and allow it to run on relatively cheap hardware - in particular hardware with relatively cheap storage subsystems.  In principle an ordinary PC should be able to handle the write workload from the sensors, although you may need faster disk hardware or SSDs to cope with any substantial query workload.
However, this architecture requires a fair bit of plumbing to get the data back out.  The Sensor ID cannot be indexed in the database, as it is just an offset into an array of sensor readings.1  This means an entire periods worth of data must be read to get a single sensor reading out.  The querying functionality may therefore require a fairly sophisticated caching function in the middle tier to get decent performance, so this approach comes at the price of some development overhead.
1 This is an egregious violation of first normal form, although a relational store may not be necessary or even desirable for this application.


Multiple criteria inside CASE WHEN statement

You can use following:

CREATE tABLE test(UserFlag varchar(100),QtyAvailable INT,OrderStatus VARCHAR(100))
GO

SELECT
CASE WHEN
UserFlag IN ( 'On Order', 'Sold Out', 'On Order,Sold Out')
AND
QtyAvailable = 0
AND
OrderStatus = 'Pending Fulfillment'
then 'BackOrder'
ELSE 'Not a BackOrder'
END AS [Orders Backorder]
,
 CASE WHEN
      OrderStatus in ('Billed','Partially Fulfilled','Pending Billing','Pending Billing/ Partially Fulfilled')
 THEN 'Shipped' ELSE 'Not Shipped' END AS [Orders Shipped]
FROM test;
GO

Orders Backorder | Orders Shipped
:--------------- | :-------------

db<>fiddle here


How can I drop and create postgresql views in dependency order?

You may take a look on "drop table base cascade". after that you have to recreace the structure on your own. I think its the easyer way. Or you can extend the answer, you found with some search in the pg_depend table.
For the "clean" method, please check the docs, as a dirty way here is a query:

WITH RECURSIVE t AS (
  SELECT c.oid,c.relname, 0 as level FROM pg_class c where relname='base' UNION ALL
  SELECT c.oid,c.relname, a.level+1 FROM t a
  JOIN pg_depend d ON d.refobjid=a.oid
  JOIN pg_rewrite w ON w.oid= d.objid AND w.ev_class!=a.oid
  JOIN pg_class c ON c.oid=w.ev_class
) SELECT distinct * FROM t order by level;


Why Postgres uses a custom defined index instead of the default one on the primary key

Due to padding for memory alignment, adding a small column to an index can often take up no extra room.  The index might even be smaller, if it is fresher and so more densely packed.  And even if not, the cost estimate for looking up one row in an index is very weakly dependent on index size, so the cost is likely to be a tie between the two indexes.  When there is a tie between indexes they are broken arbitrarily, and it seems like it is the one created most recently that is usually chosen.


How do I write IPv4 slash notation in IPV6

The /24 in IPv4 is for small network. You can translate it in /96 for IPv6.

Here's the quote from documentation :

  An IPv6 address range might look like ::1/128 for a single host (in
  this case the IPv6 loopback address) or fe80::7a31:c1ff:0000:0000/96
  for a small network.


Postgres 10. Hide users/roles from other users

pgAdmin is using pg_roles to show that information, so it is enough to run

REVOKE SELECT ON pg_catalog.pg_roles FROM public;

But since the information is available in other views as well, you would need to hide them, for instance the pg_user and pg_shadow views.

It should not stop users from logging in nor breaking anything since those views are not used during authentication, so you should make sure that you didn't change anything else.
The logs might show you the reason for why they can't log in.


How to restore SQL Server database with mirroring

You cannot restore a database that is involved in Mirroring without breaking/removing Mirroring.

To remove mirroring

ALTER DATABASE <database_name> SET PARTNER OFF

ON the mirror database

RESTORE DATABASE <database_name> WITH RECOVERY;

Some useful links for mirroring can be found here.


Here is a more secure way to reset password without skip-grant-tables
Suppose you want root to have myn3wp@ssw0rd as the password
Step 01 : Create a script to execute when mysqld first start up
Create a file called /var/lib/mysql/init-file.sql with these two lines
GRANT ALL PRIVILEGES ON *.* to root@localhost
IDENTIFIED BY 'myn3wp@ssw0rd' WITH GRANT OPTION;

Step 02 : Run these three(3) lines in the OS
chown mysql:mysql /var/lib/mysql/init-file.sql
service mysql restart --init-file=/var/lib/mysql/init-file.sql
rm -f /var/lib/mysql/init-file.sql

Step 03 : THERE IS NO STEP 03. YOU ARE DONE !!!
Give it a Try !!!
CAVEAT

I used to recommend two restarts of mysql that required editing my.cnf
I learned a better approach from @ShlomiNoach
This answer improves on it more by not editing my.cnf at all

UPDATE 2013-06-17 07:05 EDT
OK Since mysqld for Ubuntu does not like init-file on the command, you must edit the my.cnf. Please do these next two steps
STEP 01) Add these lines to my.cnf under the [mysqld] group header
[mysqld]
init-file=/var/lib/mysql/init-file.sql

STEP 02) Allow mysql to read files from /etc/mysql
sudo vim /etc/apparmor.d/usr.sbin.mysqld

Add the line:
/etc/mysql/*.sql r,

STEP 03) Restart MySQL
This should work for you. Give it a Try !!!


How to get full query text from performance_schema.threads?

As I read the manual, ... (In other words, I did not test the following.)
It cannot be set dynamically; the manual is clear on that.  Instead, you should be able to set performance_schema_max_sql_text_length in the config file (my.cnf, etc).  That would require a restart of the server.
Note: The setting is limited to 1MB.


Under what user does my process run on Oracle Linux?

This is such a common problem when the database was installed with user separation (grid + oracle user) and DBAs tend to overlook this.

When you use RAC or even just Oracle Restart (with or without ASM), you need to install Grid Infrastructure. Grid Infrastructure can be installed as a different user (typically grid).

When you have Grid Infrastructure, the proper way to handle listeners is through Grid Infrastructure. If Grid Infrastructure was installed with grid user, then the listener runs as grid user.

In Oracle architecture, by design, remote connections log in through the listener, and the database server process is forked by the listener. On Linux/UNIX platforms, the oracle binary is owned by oracle user, and it has the setuid bit enabled. grid and oracle users share a common group, and the oracle binary can be executed by the members of this group.

Given the above information, remote connections coming through the listener running as grid user can spawn processes whose UID and EUID is the same as the UID of oracle.

So far this is what usually everyone knows. The difference is however the RUID and the inherited privileges because of it.

On a machine with users as (this is the default configuration taken from an Exadata X5-2 compute node, so this is how Oracle officially deploys its configuration):

$ id oracle
uid=1001(oracle) gid=1001(oinstall) groups=1001(oinstall),1004(asmdba),1002(dba),1003(dboper)
$ id grid
uid=1000(grid) gid=1001(oinstall) groups=1001(oinstall),1004(asmdba),1005(asmoper),1006(asmadmin)

When you log in locally, all user IDs are 1001 (=oracle):

sqlplus user/password

SQL>select spid from v$process where addr = (select paddr from v$session where sid = sys_context('userenv', 'sid'));

SPID
------------------------
85510

SQL>!ps -o uid,euid,ruid -p 85510
  UID  EUID  RUID
 1001  1001  1001

But when you log in through the listener:

sqlplus user/password@orcl

SQL>select spid from v$process where addr = (select paddr from v$session where sid = sys_context('userenv', 'sid'));

SPID
------------------------
90372

SQL>!ps -o uid,euid,ruid -p 90372
  UID  EUID  RUID
 1001  1001  1000

Notice that the RUID is different, it is the UID of grid user.

For example, this can lead to a situation, where file generation from the database with UTL_FILE is successful when executed in a scheduled job, but fails when executed from a remote session.

With the above setup, server processes forked by the listener inherit the privileges of grid user. Scheduled jobs spawned by the database itself inherit the privileges of oracle user. If they do not have the same privileges, they will behave differently.


SQL enforce value of a column if other column is NOT NULL

Answers to a comment must be on the same article. So is there a way to contraint this?

If so the logic must be that the record have arcticle_id IS NULL when parent_id IS NOT NULL. And the article ID must be obtained from its parent for that record.

SELECT ... ,
       CASE WHEN t1.parent_id IS NULL
            THEN t1.article_id
            ELSE t2.article_id
       END article_id,
       ...
FROM table t1
LEFT JOIN table t2
    ON t2.user_id = t1.parent_id
...

So if parent_id is set, the article_id is taken from parent's record, and the value from current record is simply ignored.


Row based replication on slave where as statement based on Master

You shouldn't have any trouble. For you replication you use the binlogs in the master, and the your slave reads them and "translates" them to his relay logs. Then your slave executes that relay log and for the replication, that's it.

In fact, you have already "different" settings, since you weren't doing any binlogs at all in your slave, so enabling RBR won't do any harm.


If a stored procedure is altered while it's currently being used in a cursor, does the cursor continue using the old query before it was altered?

So per @LowlyDBA's suggestion I decided to test this for myself (to a degree).
I was actually in the middle of debugging a hot production issue so couldn't really stop to write out the code for a test case. But the code I'm debugging is this scenario. So I made my update to the procedure and looked at the running queries and saw the query text changed from before and after my change to the procedure as the cursor continued to iterate.


How to add a new listener on a new port and restrict it to one instance only

See the documentation about configuring multiple listeners in listener.ora, and about static service registration for each. https://docs.oracle.com/en/database/oracle/oracle-database/19/netag/configuring-and-administering-oracle-net-listener.html#GUID-C3C40DBA-4282-41E1-9562-4B8B10947C4E
LISTENER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    )
  )

QA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1526))
    )
  )

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=prod.us.example.com)
      (ORACLE_HOME=/oracle12c)
      (SID_NAME=prod))
  )

SID_LIST_QA=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=qa.us.example.com)
      (ORACLE_HOME=/oracle12c)
      (SID_NAME=qa))
  )

If you are working with Oracle Enterprise Edition, then another option would be to use Oracle Connection Manager; it would allow you to restrict client access by IP address and database service name. I wrote a white paper on it several years ago, but the basics are still the same with the latest versions: https://pmdba.wordpress.com/2013/12/01/deploying-an-oracle-11gr2-connection-manager/


How we can we know SQL Replication components installed in SQL server

You can us the below T-SQL command to query the registry and confirm is Replication Components are installed:

USE master;
GO
DECLARE @installed int;
EXEC @installed = sys.sp_MS_replication_installed;
SELECT @installed;

if @installed = 0 then replication is not installed. That stored procedure actually reads the registry using the below command, which is another way of checking:

EXEC master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
        'SOFTWARE\Microsoft\MSSQLServer\Replication',
        'IsInstalled'

sp_MS_replication_installed example

Underlying code


What is the rationale for requiring a column list when inserting into a table with identity column?

If you don't specify the column list then this implicitly assumes a column list including all user insert-able columns (non identity, rowversion, computed) as below.

CREATE TABLE #T
(
C INT IDENTITY,
D INT,
E INT
)

INSERT INTO #T
VALUES (2,3)

This is far more generally useful as the most common reason for having an IDENTITY column is to allow SQL Server to manage the values.

I suppose it could imply that if there was an extra column in the insert source on an identity table that it should generate an execution plan for the explicit identity_insert case but not much benefit to this IMO.


Not able to install any DB2 LUW 10.5 fixpacks

From your screenshot this is Express-C edition. It cannot be upgraded with fixpack.
See here

In my opinion your best bet is to leave it as is or plan for migration to newer version. IBM provides only newest Express-C edition from what I could gather.


Azure SQL Elastic Pools - When to Create a new one versus upping the eDTU?

It seems that you are below the limits in both cases, so it seems pretty the same.
These are the limits:

As you can see, the only thing that really seems to matter in your case and that donsen't double when you switch from 100edtu to 200edtu, is the max concurrent sessions.
So, if you are far below this limit, I suggest you to choose basing on the easiest design.
If you are reaching the session limit per pool, it's better to split it up in two 100 edtu pool.
Don't change winning team. :-)


sqlplus, command line parameters and ORACLE_HOME with spaces

sqlplus and many other programs do not require ORACLE_HOME to be set.

In this documentation 9.2 Oracle states:

  Oracle Corporation recommends that you never set the ORACLE_HOME
  environment variable, because it is not required for Oracle products
  to function properly. If you set the ORACLE_HOME environment variable,
  then Oracle Universal Installer will unset it for you.

I did not find similar statement for never releases of Oracle, so it might be outdated. Newer installation guides for Windows do not mention ORACLE_HOME environment variable at all, so most likely it is really not needed.


How can I safely rename a sequence in PostgreSQL, ideally without downtime?

If the sequence is used in the DEFAULT clause of the table column, it is enough to rename the sequence.

That is because the DEFAULT clause is not stored as string, but as parsed expression tree (column adbin in catalog pg_attrdef). That expression tree does not contain the name of the sequence, but its object ID, which is unchanged by renaming the sequence. Tools like psql's \d re-construct a string from the parsed expression, so the DEFAULT clause will appear to reflect the renaming.
If the sequence name is used elsewhere, like in your client code or in a PostgreSQL function, you would have to change the name in that code. PostgreSQL functions are stored as strings (column prosrc in catalog pg_proc), so renaming a sequence can make a function that uses the sequence fail.

In this case, you would have to suspend activity until you have changed the code and renamed the sequence if you want to avoid errors.


How to fetch records from table which has csv

Easiest way to achive this could be:

SELECT * FROM table WHERE Campaigns LIKE '%AMT%' OR Campaigns LIKE '%065%'

You can also look at this answer:

https://dba.stackexchange.com/a/170603/74024


Find exact and partial matches in the tables

use OR instead of AND, also 'cat' is LIKE '%cat% so you do not need to check with = also

SELECT P.*, U.*
    FROM PRODUCTS AS P
        LEFT JOIN USERS AS U ON (U.USER_ID = P.STORE_ID)
            WHERE P.PRODUCT_NAME LIKE ('%cat%')
               OR U.USERNAME LIKE ('%cat%')
                    GROUP BY P.PRODUCT_ID, U.USER_ID


How do I tell which Edition and Version my SSAS Instance is running?

The easiest way is :

using SSMS -- connect to instance --> reports -> standard reports --> General

There are other ways as well - How to find Analysis Services Server Version ?.


Select rows from table 1, table 2, then selecting based on ID

While you already seem to have resolved the problem to your satisfaction, the classical solution to this is to use a FULL OUTER JOIN:

SELECT COALESCE(t1.State, t2.State) as State
      ,COALESCE(t1.ID, t2.ID) as ID
      ,COALESCE(t1.Conditional_Value, t2.Conditional_Value) as Conditional_Value
  FROM Table1 t1
         FULL OUTER JOIN Table2 t2 ON (    t1.State = t2.State
                                       AND t1.ID = t2.ID)

Note: code untested, and I haven't worked with teradata specifically, so there may be some syntax issues I can't predict. However, the basic commands and functions should be correct, from what I can see.

The code merges the two tables based on the State and ID values. The COALESCE functions ensure that whichever table has a value for a particular row, that's the value that will be used. Since we look at Table1 first, if both Table1 and Table2 have a value for Conditional_Value, the value from Table1 will be used.