Unable to create / Move data to New Partitions from the existing partition SQL Server 2014 We have an SQL Server 2014 Enterprise Edition and a database around 6 TB in size.

Just to give quick background of infra as Server is in Azure and having premium disk of 9 TB for datafiles.

Have a few large table that is already partitioned by an identifier (MonthlyDate integer Eg 01012016,... 31122016 ). We have monthly partition starting from 2014 to 2016 (01012014 , 01022014......till 31122016).

Now we are trying to create partition for 2017 and 2018 month-wise.

I tried to use the partition wizard, but couldn't find an option for what I wanted. As far as I can tell, I need to run something like:

Alter partition Scheme [PartPScheme_BIGTAB] Next Used [PartFileGrp_201701]

Alter partition Function [PartPFN_BIGTAB] () split range(20170131)

I have tried to run the abrove script multiple time , But it have taken more then 7 hours just only 1 gb of the data has been moved to above partition and finally we have to roll back the script.

I'm looking for someone to point me in the right direction as to best practice for this sort of thing.  Not really sure how to approach this.

What could be the issue?

The current partition function is:

CREATE PARTITION FUNCTION [PartPFN_BIGTAB](int) AS RANGE LEFT FOR VALUES (
      20141031
    , 20141131
    , 20141231
    , 20150131
    , 20150231
    , 20150331
    , 20150431
    , 20150531
    , 20150631
    , 20150731
    , 20150831
    , 20150931
    , 20151031
    , 20151131
    , 20151231
    , 20160131
    , 20160231
    , 20160331
    , 20160431
    , 20160531
    , 20160631
    , 20160731
    , 20160831
    , 20160931
    , 20161031
    , 20161131
    , 20161231
    );

If your intention is to partition this data by year/month, I think your current partitioning column (which is an integer and not a 'true' date data type and not even in yyyymmdd format even as an integer) is all wrong for the job.  I'm also assuming you don't have another column in the table that IS the MonthlyDate in a true Date data type.

In my opinion (and assuming you have extra disk space), you should:

Create a new partition function that uses a DATE data type.  It is recommended that this should be RANGE RIGHT (see Dan Guzman's link at the bottom of this answer for explanations).  Make sure you create it with ranges well below your minimum date as well as ranges well above your maximum date.  Also, as time goes by, make sure you are splitting empty partitions well ahead of any data already being populated in them.
Create a new partition scheme that references the new partition function.
Create a new 'replacement' table (that references the newly created partition scheme) by scripting a create of the current table, but add a computed/persisted DATE column (ex:PartitionedColumn DATE) that is derived/converted from your integer MonthlyDate column.
Set up an insert statement, inserting records from the existing table into the new table, say a few thousand or a hundred thousand in a batch, within a transaction. When the transaction completes perform a log backup (if you're not in SIMPLE recovery).  That'll keep you transaction log from running out of space. Then repeat the batch... keep doing that until all records are written to the new table with the persisted computed column.
Then change the old table name, change the new table name to that of the old table

Keep in mind that all unique indexes that participate in a partitioning scenario, MUST have the partitioning column defined as part of the base index definition and not simply an included column.

Also, to enable partition switching (often used in a "sliding window"), all indexes on the table must be aligned.  BOL has good information about this.  Look for Special Guidelines for Partitioned Indexes.

Lastly, check out Dan Guzman's excellent post on Sql Server partitioning.


SQL Server 2008 R2 database mirroring - is it possible to have a secondary server for multiple primary servers? Scenario is that I have two servers running SQL Server 2008 R2 (64-bit), let's call them DB1 and DB2. I have set up database mirroring for all the databases on these servers so that DB1 is the principal and DB2 is the secondary.
Now the question is, can I set up database mirroring on a new server DB3 running it as principal and have DB2 work as a secondary for both DB1 and DB3? Or will I need a separate new secondary server (DB4) for DB3? Would this configuration be possible using separate instances on DB2?

A SQL Server instance can host multiple mirroring partnerships. Mirroring is between databases and databases are hosted on SQL Server instances, so choosing a name like 'DBx' for servers is a rather poor choice. Lets call the instances SRV1, SRV2 and SRV3 and the databases DB1 and DB2. What is possible is:

to have the database DB1 mirrored by SRV1 (primary) and SRV2 (secondary)
to have the database DB2 mirrored by SRV3 (primary) and SRV2 (secondary)

But the question is What do you use mirroring for? Primarily it is a High Availability feature. The question is: will the SRV2 instance be able to take over the primary role for both DB1 and DB2 in case of failover? Nothing prevents both SRV1 and SRV3 from suffering a problem at the same time. Also, if SRV2 suffers a disaster, you will have to come up with a backup plan to restore the HA for both DB1 and DB2. Are you prepared to handle all these cases?

  Would this configuration be possible using separate instances on DB2?

Yes, but is not necessary.


Which MySQL engine should i use where most operations are read operations I want to develop an application for which i will need a database to store data. I am using MySQL as backend. My question is Which MySQL engine should i use?

Well, i have mentioned some important characteristics of operations that will be performed on db:

99.99% read operations.
I don't need foreign-keys.
I don't need transactions.
Simple read operations and nothing else.

I know that InnoDB is latest default engine in MySQL. But, as far as i can conclude, i don't need most of the features which are main reasons to use InnoDB. So, now i am left with MyISAM.

I am thinking of using MyISAM as i just want to read data from database. I have refered the official documentation for storage engines and also googled it. But, still may be that my knowledge-base is lacking some important updates. So, i am seeking help from community.

Is my choice of using MyISAM storage engine for specified scenario proper?

Thanking Community in anticipation.

I thank ypercube to help me narrow-down my choice.

Although MyISAM genrally matches your characteristics, I advise you against using it, for the following reasons:

Integrity, MyISAM is non-trasnactional: yes, I know you wrote you do not need transactions. The thing is, with MyISAM not even a single operation is "atomic". You see, a power failure in the middle of a write (you do have 0.01% writes), may corrupt your data; now even a single row has any integrity.

You don't need transactions just for complex "grouping multiple queries" or "rollback to savepoint". You need transactions to know your data has integrity. MyISAM has nothing of the sort. There is no mechanism to guarantee you data. A corruption may (and will) occur, and either you will not even know it, or you will not be able to fix it. The closest you have is a REPAIR TABLE which at best will fix your table structure.

I say if you case about your data -- MyISAM is a no-go.
Lack of development: no one is actually improving much of the code. Everyone is busy making InnoDB better and better. It's not that by picking on MyISAM you will stay behind: you're starting waaay behind. Do not expect bugfixes as quick as you would for InnoDB.
Poor for live backups. For taking a live backup of your database you will need to place a READ LOCK on your database for the entire operation. It may not be such a bid deal for you; you may also use replication and have the backup execute on the slave (but replication with MyISAM is again so unsafe due to lack of integrity and error-recovery on master). Or you can also use LVM.


Ms Sql Server Table Design question Background

I'm designing a table which will serve as a repository of payment information to be used for research purposes. This table will have around 4.5 million records initially and will grow at a rate of around 400k annually. The PaymentReference column would be the only unique column in the table on an on going basis meaning after the initial historical load of data which would contain nulls.

Table example:

    CREATE TABLE [dbo].[Bil_ReturnsRepository](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [PolicyNumber] [nvarchar](32) NOT NULL,
    [PaymentReference] [nvarchar](32) NULL,
    [AccountingDate] [datetime] NOT NULL,
    [ReturnDate] [datetime] NOT NULL,
    [PaymentAmount] [decimal](19, 2) NOT NULL,
    [RegionCode] [nvarchar](5) NULL,
    [PolicyStateCode] [nvarchar] (3) NULL,
    [CompanyCode] [nvarchar](4) NULL,
    [LineOfBusiness] [nvarchar](4) NULL,
    [ReturnReasonCode] [nvarchar](3) NULL,
    [PaymentType] [nvarchar](20) NULL,
    [PaymentPlan] [nvarchar](20) NULL,
    [EntryUserId] [nvarchar](20) NULL,
    [PolicyTermId] [bigint] NULL,
    [PaymentDate] [datetime] NULL,
    [DateOfFirstReturn] [datetime] NULL,
    [BankId] [nvarchar](9) NULL,
    [EquityDateOfPolicy] [datetime] NULL,
    [OrginalStartDateOfPolicy] [datetime] NULL,
    [PaymentSource] [nvarchar](20) NULL,
    [DBTransactionTimeStamp] DATETIME NOT NULL DEFAULT(GETDATE()))

Access to Data

Business users will enter search criteria on a front end UI screen. The available options to use as search criteria will be every column located on the table except ID and DBTransactionTimeStamp. Every column of the table will have an associated text box that the user will have the ability to enter the necessary value for that column to use for the search. The only field that is not optional for the search is the ReturnDate field. For example, a search could be performed with just ReturnDate and PolicyNumber. ReturnDate, PaymentReference. ReturnDate, PolicyNumber, PaymentReference, AccountingDate. ReturnDate only. etc....

A stored procedure will be created to actually perform the read of the table and will use the parameters passed by the front end UI when querying the table.

Question

Since this is a new design I'm not currently experiencing any performance issues but I would like to be proactive and prevent any of those possible issues from occurring. I would imagine that with so many records on the table and the ability to search on all fields there would be table space scans that would occur. That would result in a slow response time for the output to be returned to the front end UI.  So with that being said would it be beneficial to add any indexes and or keys to this table ? If so what Keys ? What indexes ?

Since the search always includes the ReturnDate column, it seems reasonable to create the clustered index on that column.
Based on your stated assumption that you'll add around 400,000 rows per year, and you have 4,000,000 rows in the table now, it seems like you might reasonably have 10 years of data, or 3650 unique ReturnDate values at the current time.  (4,000,000 / 400,000 = 10)
With the clustered index on ReturnDate, selectivity on the clustered index would be on average around 1,300 rows per ReturnDate value.
If SQL Server does a seek into the clustered index to the start of the range of values fitting the desired ReturnDate, it will only need to scan approximately 1,300 rows to find the other values to fit the search criteria.
I've setup a simple test-bed to test this hypothesis:
IF OBJECT_ID(N'dbo.Bil_full', N'U') IS NOT NULL DROP TABLE dbo.Bil_full;

CREATE TABLE dbo.Bil_full
(
      PolicyNumber nvarchar(32) NOT NULL
    , PaymentReference nvarchar(32) NULL
    , AccountingDate datetime NOT NULL
    , ReturnDate datetime NOT NULL
    , PaymentAmount decimal(19, 2) NOT NULL
    , RegionCode nvarchar(5) NULL
    , PolicyStateCode nvarchar (3) NULL
    , CompanyCode nvarchar(4) NULL
    , LineOfBusiness nvarchar(4) NULL
    , ReturnReasonCode nvarchar(3) NULL
    , PaymentType nvarchar(20) NULL
    , PaymentPlan nvarchar(20) NULL
    , EntryUserId nvarchar(20) NULL
    , PolicyTermId bigint NULL
    , PaymentDate datetime NULL
    , DateOfFirstReturn datetime NULL
    , BankId nvarchar(9) NULL
    , EquityDateOfPolicy datetime NULL
    , OrginalStartDateOfPolicy datetime NULL
    , PaymentSource nvarchar(20) NULL
    , DBTransactionTimeStamp DATETIME NOT NULL
        CONSTRAINT DF_bil_ts
        DEFAULT(GETDATE())
) ON [PRIMARY];

CREATE CLUSTERED INDEX CX_Bil
ON dbo.Bil_full(ReturnDate);

Here, I'm adding 4,000,000 rows to the table, with a variety of "made-up" data that perhaps approximates the data you might actually have.
;WITH src AS (
    SELECT num = 1.0 + v1.num
        + (v2.num * 10)
        + (v3.num * 100)
        + (v4.num * 1000)
        + (v5.num * 10000)
        + (v6.num * 100000)
        + (v7.num * 1000000)
    FROM  (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v1(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v2(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v3(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v4(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v5(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v6(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v7(num)
)
INSERT TOP(4000000) INTO dbo.Bil_full (PolicyNumber, PaymentReference, AccountingDate, ReturnDate, PaymentAmount, RegionCode, PolicyStateCode, CompanyCode, LineOfBusiness, ReturnReasonCode, PaymentType, PaymentPlan, EntryUserId, PolicyTermId, PaymentDate, DateOfFirstReturn, BankId, EquityDateOfPolicy, OrginalStartDateOfPolicy, PaymentSource, DBTransactionTimeStamp)
SELECT PolicyNumber = N'PN' + RIGHT(N'000000000' + CONVERT(nvarchar(10), src.num), 10)
    , PaymentReference = N'PR' + RIGHT(N'000000000' + CONVERT(nvarchar(10), src.num - 10000), 10)
    , AccountingDate = DATEADD(DAY, src.num % 3715, N'2012-01-01T00:00:00')
    , ReturnDate = DATEADD(DAY, src.num % 3715, N'2012-03-01T00:00:00')
    , PaymentAmount = src.num * CONVERT(int, CRYPT_GEN_RANDOM(1)) * 0.47
    , RegionCode = CASE WHEN src.num % 3 = 1 THEN REPLICATE(char(65 + src.num % 26), 5) ELSE NULL END
    , PolicyStateCode = CASE WHEN src.num % 3 = 2 THEN REPLICATE(CHAR(65 + src.num % 26), 3) ELSE NULL END
    , CompanyCode = CASE WHEN src.num % 5 = 3 THEN REPLICATE(CHAR(65 + src.num % 26), 4) ELSE NULL END
    , LineOfBusiness = CASE WHEN src.num % 6 = 3 THEN REPLICATE(CHAR(66 + src.num % 26), 4) ELSE NULL END
    , ReturnReasonCode = CASE WHEN src.num % 3 = 2 THEN REPLICATE(CHAR(69 + src.num % 26), 3) ELSE NULL END
    , PaymentType = CONVERT(varchar(10), CRYPT_GEN_RANDOM(8), 1)
    , PaymentPlan = CONVERT(varchar(10), CRYPT_GEN_RANDOM(8), 1)
    , EntryUserId = CONVERT(varchar(10), CRYPT_GEN_RANDOM(8), 1)
    , PolicyTermId = CONVERT(bigint, CRYPT_GEN_RANDOM(1))
    , PaymentDate = DATEADD(DAY, src.num % 3715, N'2012-01-01T00:00:00') + 27
    , DateOfFirstReturn = DATEADD(DAY, src.num % 3715, N'2012-03-01T00:00:00') - 16
    , BankId = CONVERT(varchar(8), CRYPT_GEN_RANDOM(6), 1)
    , EquityDateOfPolicy = DATEADD(DAY, src.num % 3715, N'2012-01-01T00:00:00') + 365
    , OrginalStartDateOfPolicy = DATEADD(DAY, src.num % 3715, N'2012-01-01T00:00:00') + 15
    , PaymentSource = CONVERT(varchar(10), CRYPT_GEN_RANDOM(8), 1)
    , DBTransactionTimeStamp = DATEADD(SECOND, src.num, N'2013-01-01T00:00:00')
FROM src
ORDER BY src.num

The table has 1346 rows per ReturnDate from 2012-03-05 to 2022-05-02.
Assuming every query against this table does in fact include a ReportDate value, you could expect execution to consist of a single clustered index seek, which is going to be quite efficient.
For instance, the query:
SELECT *
FROM dbo.Bil_full
WHERE Bil_full.ReturnDate = N'2016-04-01 00:00:00.000'
    AND Bil_full.PolicyNumber LIKE N'%89%'
    AND Bil_full.PaymentType LIKE N'XX%'

Has this query plan:

Statistics I/O shows the following:
Table 'Bil_full'. Scan count 1, logical reads 50, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0,
  lob read-ahead reads 0.

You may notice I've removed the ID column from the table.  If you don't need the column, why include it?  If the date columns do not include times, you can save some space per row by changing the data type to DATE instead of DATETIME.  The nvarchar(..) columns could be changed to just varchar(..) columns if you are not actually storing unicode data; if that is applicable those columns will become half the size they currently are.
I've not created a primary key on the table since your question makes no mention of requiring one for referential integrity or other requirements such as uniqueness.  The table does have a clustered index, which optimizes the physical layout of the table on disk versus the table being a simple heap.  The clustered index (i.e. the table itself) will experience a fair amount of page splits if the ReportDate value for rows being inserted are not monotonically increasing.
You asked about performing a range-scan for the ReportDate where your client is asking for rows between two dates - as in:
SELECT *
FROM dbo.Bil_full
WHERE Bil_full.ReturnDate >= N'2016-04-01 00:00:00.000'
    AND Bil_full.ReturnDate < N'2017-04-02 00:00:00.000'
    AND Bil_full.PolicyNumber LIKE N'%89%'

Queries like this will still perform relatively well; the above query returned 19432 rows very quickly.  Of course, the size of the date range will affect query performance. Keeping the ranges as small as possible, i.e. allowing a maximum range of 30 days, will ensure performance remains manageable.


Inputs on handling large volumes of data for specific tables in SQL Server standard I have large OLTP ERP database with 10 million records in few tables. The database size is as large as 10GB.
I have few critical transaction tables like this:
1. Payments
2. Cheques etc.,
and each of these tables have more than 30 columns and at least 10 million records each.

I have identity column as the primary key in all these tables and each of the tables is having the transaction date (which is as well non cluster indexed). Recently I found out that my INSERT, UPDATE and DELETE's on these tables is taking considerable time. The users are interested in accessing last 3 years of data (most likely, except for reporting). I would like to optimize this and came up with these two approaches:
1. Archiving all records and moving them to a new database for those records, which are more than 3 years old.
2. Creating staged tables for each of the years and moving the data into them and doing CRUD operations on an indexed view (with union of all the staged tables).

Since I'm not using SQL Server Enterprise I cannot create partitioned tables.

I'm thinking that the second method is appropriate and suitable than the first one since the maintenance is easy. Kindly suggest me if I'm correct. Also give your kind inputs on any other ideas.

Thanks in advance.

Use an archive table and a live table.

Don't use a "table per year": this quickly becomes messy and you'll end up needing dynamic SQL when you UNION + view becomes a bottleneck

10 million rows isn't a lot though, and 30 columns is quite wide. So, some options:

review data types to reduce data in disk.

Do you need nvarchar?
Can you use smalldatetime?
Do you have int where tinyint will do?
(n)varchar when you have fixed length data?

do you have regular index and statistics maintenance?
do you have incorrect or missing indexes?
have you looked at most expensive queries?


I managed to come up with a query that suits my need. Probably not the most efficient one, but the table is not too big...

select c.CurrencyID, c.MarketName, t.TickTime, t.Last, t.Volume
from (select t.*,
         (@rn := if(@v = CurrencyID, @rn + 1,
                    if(@v := CurrencyID, 1, 1)
                   )
         ) as rn
  from Ticker t cross join
       (select @v := -1, @rn := 0) params
  order by CurrencyID, TickTime desc
 ) as t
INNER JOIN Currency c
    on c.CurrencyID=t.CurrencyID
where
    rn <= 3;


Updating a large table in sql server I have a wide table (80 columns) with 70 million rows in it. I can currently trying to set a foreign key across all rows within the table.

Approach one
An update statement over the entire table such as:

BEGIN TRAN
UPDATE Cx
SET Cx.CohortId = Chx.CohortId
FROM Customers Cx
INNER JOIN Cohorts Chx ON Chx.NameField = Cx.NameField
COMMIT TRAN

I was informed this has the potential to fill up the transaction log (please note recovery mode is currently set to simple) and it would be better to batch the updates:

Approach two
Update in batches

DECLARE @Rows INT,
    @BatchSize INT;

SET @BatchSize = 20000;
SET @Rows = @BatchSize;

WHILE (@Rows = @BatchSize)
BEGIN
UPDATE TOP(@BatchSize) Cx
SET Cx.CohortId = Chx.CohortId
FROM Customers Cx
INNER JOIN Cohorts Chx ON Chx.NameField = Cx.NameField
WHERE Cx.CohortId IS NULL -- Do not update records that have already been uplifted to include the FK

SET @Rows = @@ROWCOUNT
END

Does the second approach yield any benefit in terms of the transaction log on large table updates?

I was informed this has the potential to fill up the transaction log . . . and it would be better to batch the updates

It would be better if it didn't fill up the transaction log.  But the proposed remedy is the wrong one.

You should size your transaction log to support your transactions, not the other way around.

  Does the second approach yield any benefit in terms of the transaction log on large table updates?

Yes.  The transaction log can be reused between batches, so long as all the batches aren't in a transaction.

  increasing the disk space to support that would be troublesome

Yes, sometimes you do have to code around space issues, but coding isn't free.  Spending money on infrastructure to reduce coding time is usually a wise move.

Also if you are trying to minimize log space, consider loading an empty table with your query, and then performing an ALTER TABLE … SWITCH.  Loading the new table can be minimally logged, and the ALTER TABLE is a metadata operation.

This will also enable you to perform the operation in a transaction, and not commit intermediate results.


How do I Add Integration Services to existing SQL Server 2012 Install that has no internet access Did a bunch of hunting, but I have a SQL 2012 Developer instance that's on an isolated network (no internet access, except for VPN to it). At install time we didn't install integration services, and my developer needs to deploy/configure SSIS packages.

I tried a few things, and got stuck. Part of the issue is that we appear to need some flavor of VS, and I'd assumed that I already had it per below.

Would appreciate a step by step.. keeping in mind that whatever I put on the machine can't be dependent on getting files from the internet.

You have to create an administrative install point:
SSDTSetup.exe /layout <destination>
then copy the files to your isolated instance.
Check out https://blogs.msdn.microsoft.com/ssdt/2014/07/23/creating-an-administrative-install-of-ssdt-update-for-visual-studio-2013/ for details.


SQLAlchemy: table version already exists. pgAdmin4 fails on Linux Mint 20 and 20.1 I already found the solution, I just want to share it in case someone has the same problem.
Situation
Install pgAdmin4 version 4.30 on Linux Mint 20 (Ulyana) and Linux Mint 20.1 (Ulyssa) inside virtualenv. The bulk of pgAdmin4 is a Python web application written using the Flask framework. Therefore, it is an ideal candidate to be implemented in a Python virtualenv as indicated in:
How to Install PostgreSQL with pgAdmin4 on Linux Mint 20
And,
Installing pgAdmin 4 on Linux Mint 20 (ulyana)
And in Spanish,
Cómo instalar PostgreSQL con pgAdmin4 en Linux Mint 20
Error
After finishing the process, when trying to run pgAdmin4 for the first time, the following error appears:
Traceback (most recent call last):
  File "venv/lib/python3.8/site-packages/pgadmin4/pgAdmin4.py", line 87, in <module>
    exec(open(file_quote(setup_py), 'r').read())
  File "<string>", line 449, in <module>
  File "<string>", line 372, in setup_db

  [...]

  File "/home/mint/pgAdmin4/venv/lib/python3.8/site-packages/flask_sqlalchemy/__init__.py", line 575, in get_options
    self._sa.apply_driver_hacks(self._app, sa_url, options)
  File "/home/mint/pgAdmin4/venv/lib/python3.8/site-packages/flask_sqlalchemy/__init__.py", line 908, in apply_driver_hacks
    sa_url.database = os.path.join(app.root_path, sa_url.database)
AttributeError: can't set attribute

In the second attempt to execute pgAdmin4 the error shown is:
Traceback (most recent call last):
  File "/home/mint/pgAdmin4/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
    self.dialect.do_execute(
  File "/home/mint/pgAdmin4/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 681, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: table version already exists

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "venv/lib/python3.8/site-packages/pgadmin4/pgAdmin4.py", line 94, in <module>
    app = create_app()

  [...]

  File "/home/mint/pgAdmin4/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 681, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table version already exists
[SQL:
CREATE TABLE version (
    name VARCHAR(32) NOT NULL,
    value INTEGER NOT NULL,
    PRIMARY KEY (name)
)

]
(Background on this error at: http://sqlalche.me/e/14/e3q8)

This situation started to occur in March 2021 and as far as I know, it is identical on both Linux Mint 20 and Linux Mint 20.1.

Note: a more suitable location to put the virtualenv would have been /opt, but for multiple tests I opted for /home from a live USB with Linux Mint 20. Then apply the final changes to my Linux Mint 20.1

Solution
Downgrade SQLAlchemy to an earlier non-conflict version.
At the time of publishing the posts that explain the steps to install pgAdmin4 within virtualenv on Linux Mint 20, pip was implementing SQLAlchemy 1.3.23. Then, in these last days, the version used is SQLAlchemy 1.4.1
Downgrade can be done with pip install --upgrade.
--upgrade can be used for both, downgrade or upgrade.
(venv) mint@mint:~/pgAdmin4$ pip install --upgrade SQLAlchemy==1.3.23

After this, when trying to run pdAdmin4, a new problem will be displayed:
Traceback (most recent call last):
  File "venv/lib/python3.8/site-packages/pgadmin4/pgAdmin4.py", line 94, in <module>
    app = create_app()
  File "/home/mint/pgAdmin4/venv/lib/python3.8/site-packages/pgadmin4/pgadmin/__init__.py", line 347, in create_app
    if not os.path.exists(SQLITE_PATH) or get_version() == -1:
  File "/home/mint/pgAdmin4/venv/lib/python3.8/site-packages/pgadmin4/pgadmin/setup/db_version.py", line 19, in get_version
    return version.value
AttributeError: 'NoneType' object has no attribute 'value'

This new issue is resolved by removing the ~/.pgadmin directory (created during installation) and then rerunning "python pgAdmin4.py"


High Log Flush Wait Time and filling Transaction logs I have problem with Log Flush Wait Time - as of now, they are averaging at 30000ms (min 0 max 35000); database itself is performing poorly, transaction logs are also growing beyond reasonable description. Log files have to be shrunk to keep the size at bear, not filling up whole disk drive with log files.  I checked DB waits and they are in general LCK_M_S, also OLEDB. Database itself lives on SQL 2016 Always-on.

I still believe that there may be some SQL server issues with settings - I can also see that there is a lot of SQL Memory pressure (30000 free stalls, ~900000000 page reads, 30000 page life exp.) - server has 32GB RAM, hosting ~80 databases.

I know the application is the probably main reason for this but still, any suggestions on proceedings?

Usual supect, in this case: The IO subsystem is slow. Are you using SSDs?

DMVs are your friends.

Identity & Isolate Issues

Log Flush Wait Time

This is an indication of Underlying IO bottleneck
What is the write latency?
Find out using sys.dm_io_virtual_file_stats &
sys.dm_io_pending_io_requests

Size log files appropriately

Your application seems to be a very high transactional load type.
If log files are frequenting to a certain size limit, it means it needs such size.
I would size my log files to be 750 MB if the log file is frequently reaching that size at its peak.

Transactional log backups

Maybe you just need a more frequent log backup.
Test & decide.

As far as memory, a 30000 PLE looks good to me on a 32 GB RAM server. Assuming this is SQL's memory, not the server's.

Resources:

Paul Randal's Transaction Log Monitoring
Paul Randal's Page Life Expectancy


Get all records from inner joined tables if second table does not have data I follow below syntax by which I achieve the result. but it takes time

IF NOT EXISTS(select * from #tblxyz)
BEGIN
INSERT INTO #tblxyz select NULL
END

Select a.*
        FROM
            tblabc a
            INNER JOIN #tblxyz b ON a.ID = ISNULL(b.ID,a.id)

If second joined table has some rows, I only want to return those joined rows only. Only if the second table has no rows, I want to return al lrows from the first table.

I think the condition ON a.ID = ISNULL(b.ID,a.id) is not the best way to do this. It may lead to inefficient plans and may return unwanted results (according to your specifications). If the tblxyz table has rows with NULL values, you'll still get all the rows from table a (and possibly multiple times).

I would write the query without using the ISNULL() function, like this:

SELECT a.*
FROM tblabc a
    INNER JOIN #tblxyz b
    ON a.ID = b.ID
    OR NOT EXISTS (SELECT * FROM #tblxyz) ;

or like this:

SELECT a.*
FROM tblabc a
    INNER JOIN #tblxyz b
    ON a.ID = b.ID

UNION ALL

SELECT a.*
FROM tblabc a
WHERE NOT EXISTS (SELECT * FROM #tblxyz) ;


SQL Server Query performance with window function We write a query that include unpivot, partition by and order by.Query is:

SELECT PersonId
    ,SalaryDate
    ,ID
    ,Type
    ,SalaryValue
    ,ROW_NUMBER() OVER (
        PARTITION BY PersonId ORDER BY SalaryValue
        ) AS rn
FROM (
    SELECT lp.PersonId
        ,lp.SalaryDate
        ,lp.Salary1
        ,lp.Salary2
        ,lp.Salary3
        ,lp.ID
    FROM rdd.Salaries AS lp WITH (NOLOCK)
    WHERE lp.SalaryDate > DATEADD(day, - 31, getdate())
    ) AS t
unpivot(SalaryValue FOR Type IN (
            lp.Salary1
            ,lp.Salary2
            ,lp.Salary3
            )) AS UnpivotTable

The query returns about 68.000.000 rows and execution time is 20 minutes.
Can I improve the query's performance or rewrite effectively? What is the alternative of partition by?

You may find that the following index and query rewrite performs better, because it sorts per person rather than once over the whole set, and row estimates are more likely to be accurate:

-- Index
CREATE INDEX IX_Salaries_PersonId_SalaryDate_Inc_ID_Salary1_Salary2_Salary3
ON rdd.Salaries (PersonId, SalaryDate)
INCLUDE (ID, Salary1, Salary2, Salary3);

-- Query
WITH People AS
(
    SELECT DISTINCT
        S.PersonId
    FROM rdd.Salaries AS S
    WHERE
        S.SalaryDate > DATEADD(DAY, -31, GETDATE())
)
SELECT
    P.PersonId,
    CA.SalaryDate,
    CA.ID,
    CA.SalaryValue,
    CA.rn
FROM People AS P
CROSS APPLY
(
    SELECT
        S.SalaryDate,
        S.ID,
        V.SalaryValue,
        rn = ROW_NUMBER() OVER (ORDER BY V.SalaryValue)
    FROM rdd.Salaries AS S
    CROSS APPLY
    (
        SELECT S.Salary1 WHERE S.Salary1 IS NOT NULL
        UNION ALL
        SELECT S.Salary2 WHERE S.Salary2 IS NOT NULL
        UNION ALL
        SELECT S.Salary3 WHERE S.Salary3 IS NOT NULL
    ) AS V (SalaryValue)
    WHERE
        S.PersonId = P.PersonId
        AND S.SalaryDate > DATEADD(DAY, -31, GETDATE())
) AS CA
ORDER BY
    P.PersonId,
    CA.rn
OPTION (QUERYTRACEON 8649);

You can omit the OPTION clause if you find a parallel query is generated naturally, or if you find non-parallel performance is good enough. The desired plan shape is roughly as follows:


Getting the count of same value in consecutive rows that are ordered by different field I have a table with the following structure

finals(ID, name, result)

CREATE TABLE `finals` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `result` char(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Where:

ID is an auto increment key,
name is the person's name,
result is either Pass or Fail

Sample data

mysql> select * from finals;
+----+------+--------+
| id | name | result |
+----+------+--------+
|  1 | John | Pass   |
|  2 | John | Fail   |
|  3 | John | Pass   |
|  4 | Kyle | Pass   |
|  5 | John | Pass   |
|  6 | Kyle | Pass   |
|  7 | Kyle | Pass   |
|  8 | Kyle | Fail   |
+----+------+--------+
8 rows in set (0.00 sec)

Explanation

I am trying to get the names of people who have at least 3 consecutive "Pass".
So, the desired result is:
"Kyle"
Because "Kyle" has 3 cosecutive "Pass", IDs 4, 6, 7.

Note please that I don't care if this sequence is interrupted by a record of other person, but I care if it was interrupted by a record for the same person with "Fail" value in the result.

That is why "John" is not in the desired result. i.e. Although "John" has 3 "pass" in total, they are interrupted with a "Fail", so I want to exclude it from the result.

I Tried this query:

mysql> SELECT name, GROUP_CONCAT(result ORDER BY id) as result_str FROM finals GROUP BY name having result_str LIKE '%Pass,Pass,Pass%';
+------+---------------------+
| name | result_str          |
+------+---------------------+
| Kyle | Pass,Pass,Pass,Fail |
+------+---------------------+
1 row in set (0.00 sec)

It works, and this is the desired result. However, I am looking for alternative ways to

Be more Generic
avoid the constraints on the length of the result of GROUP_CONCAT.

Thank you in advance.

I have a solution that does not need GROUP_CONCAT

PROPOSED SOLUTION

SET @x = 0;
SET @name = '';
SET @result = '';
SELECT name,consecutive FROM
(SELECT
    name,
    (@nametag:=MD5(CONCAT(name,':',result))),
    (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
    (@name:=@nametag) inc
FROM finals ORDER BY name,id) A
WHERE consecutive >= 3;

PROPOSED SOLUTION EXECUTED

mysql> SELECT name,consecutive FROM
    -> (SELECT
    ->     name,
    ->     (@nametag:=MD5(CONCAT(name,':',result))),
    ->     (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
    ->     (@name:=@nametag) inc
    -> FROM finals ORDER BY name,id) A
    -> WHERE consecutive >= 3;
+------+-------------+
| name | consecutive |
+------+-------------+
| Kyle |           3 |
+------+-------------+
1 row in set (0.02 sec)

mysql>

SUBQUERY'S OUTPUT

mysql> SELECT
    ->     name,
    ->     (@nametag:=MD5(CONCAT(name,':',result))),
    ->     (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
    ->     (@name:=@nametag) inc
    -> FROM finals ORDER BY name,id;
+------+------------------------------------------+-------------+----------------------------------+
| name | (@nametag:=MD5(CONCAT(name,':',result))) | consecutive | inc                              |
+------+------------------------------------------+-------------+----------------------------------+
| John | 84cc30b986fe149dfb765dd09fad8a60         |           1 | 84cc30b986fe149dfb765dd09fad8a60 |
| John | 534b3d163a04b74a72c6dbe68db1c01e         |           1 | 534b3d163a04b74a72c6dbe68db1c01e |
| John | 84cc30b986fe149dfb765dd09fad8a60         |           1 | 84cc30b986fe149dfb765dd09fad8a60 |
| John | 84cc30b986fe149dfb765dd09fad8a60         |           2 | 84cc30b986fe149dfb765dd09fad8a60 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           1 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           2 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           3 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 4a4e0aaa102c37f098bd6afd13ccfea0         |           1 | 4a4e0aaa102c37f098bd6afd13ccfea0 |
+------+------------------------------------------+-------------+----------------------------------+
8 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

I just answered a similar question 2 days ago (Logic Value of Assignment Expression)


Azure Analysis Service Cube Sizes Changed Since July we have come across very strange thing the over all size and individual sizes of all AAS cube databases where reduced with out any changes happened. Did anyone saw this change dip and any cause?

it should have resolved now, Microsoft mentioned that was bug which hit most of the regions , we are on WEST US and MS released patch to make the size look accurate now


MySQL on Unix, should administrator accounts join the operating system "mysql" group? When I install MySQL via Yum, it automatically creates the mysql operating system user and group.  Then mysqld executes as mysql (instead of as root), and all the files in the data directory are owned by mysql:mysql.

This is obviously correct security practice, I understand why this happens and don't want to subvert it.

When I'm administering a MySQL system, I've just gotten in the habit of using sudo when I need to interact with a protected file (e.g., reading a binary log with mysqlbinlog, creating a backup tarball, etc).

Would it also work to add my OS user to the mysql group?  Are there major downsides of this I'm not considering?

When exporting information from MySQL for jobs using mysqlbinlog, using sudo is convenient for me too because it is usually to fix or troubleshoot something broken and time is a concern.

If you intend to re-import data extracted from MySQL then you need to work around the problem of permissions issues at some point. When creating dump files using the root account, the files created assume the permissions of the root account which MySQL can have difficulty accessing those files. (I also avoid sticky permissions as a workaround as it just gets messy when you don't deal with it on a regular basis)

What I might suggest:

add your OS account to the mysql group as it makes it convenient to view the files in the MySQL data folder if your duties include administrating/troubleshooting MySQL on that server.
limit the OS accounts that have access to the MySQL data/log files from the OS to reduce the level of exposure to your data.
developers do not need access to the OS (assuming DB servers are kept separate on the intranet and do not house the web server)
do not create files/scripts that requires your account to persist if you change duties. (i.e. If I am managing one of your DB servers, I should not find automated scripts under your account.)

For regular administrative tasks, instead of using my local account, I tend to create separate OS 'service' accounts to interface with MySQL that are in the mysql group.

If you look at the running processes (ps -ef | grep mysql) you should see that MySQL is initialized by the root account (mysqld_safe) which (as you mentioned) runs the MySQL daemon (mysqld) under the mysql account.

Using this same approach, I tend to create scripts I can run with root privileges which calls and runs another file in a linux shell as a specific user with less permissions. This also makes it easier to add the same script to the root crontab.

I start by creating a service account called 'mysqlbackup' and in the home directory add a file .my.cnf

/------ file content

[client]

user = osbackup

password = 12345

------/

Create the administrative script to be run by 'root' which executes another file from a shell

file: /var/lib/scripts/run-db-backup.sh (chmod 700 & chown root:root)
file contains: su -l mysqlbackup -c /var/lib/scripts/db-backup.sh

Create your 'real' script to be run as the OS user 'mysqlbackup'

file: /var/lib/scripts/db-backup.sql (chmod 700 & chown mysqlbackup:mysql)


ERROR: currval of sequence "rss_sub_source_id_seq" is not yet defined in this session When I used this query to see the current primary key seed of my PostgreSQL 13 table:
select CURRVAL(pg_get_serial_sequence('rss_sub_source', 'id'))

it told me:

SQL Error [55000]:
ERROR: currval of sequence "rss_sub_source_id_seq" is not yet defined in this session

The id column is defined like this:
ALTER TABLE rss_sub_source
    ALTER id SET NOT NULL,  -- optional
    ALTER id ADD GENERATED ALWAYS AS IDENTITY
        (START WITH 1233);

Table definition:
CREATE TABLE public.rss_sub_source (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    sub_url varchar NOT NULL,
    created_time int8 NOT NULL DEFAULT date_part('epoch'::text, now()),
    --  ... many more columns
    CONSTRAINT rss_sub_source_pkey PRIMARY KEY (id),
    CONSTRAINT unique_sub_url UNIQUE (sub_url)
);

How to see the current auto-increment seed for the primary key?

The functions currval(regclass) and lastval() only make sense after nextval() has been used in the same session (not transaction!). Concurrency control in PostgreSQL is reason behind this behavior.
If you are just curious about the current state of the sequence (not the last serial ID actually used in the session), you can always just call nextval():
SELECT nextval(pg_get_serial_sequence('foo', 'id'));

Returns the next serial ID, which had not been used, yet. This carries the flaw of actually consuming the returned ID (which should not matter in a proper relational design). But that side effect can be avoided. In Postgres, serial and IDENTITY columns are implemented using a SEQUENCE internally, which happens to be a table-like object that can be queried like a table. If you are curious about the current state of a SEQUENCE, just use SELECT:
SELECT * FROM public.rss_sub_source_id_seq;

(Requires the SELECT privilege on the sequence, while the above mentioned sequence functions require the USAGE privilege.)
You need to know the name of the sequence for this, and you already know how to get it: pg_get_serial_sequence('rss_sub_source', 'id'). It's easy enough to guess, too. See:

PostgreSQL next value of the sequences?

For ad-hoc use, just compile the query by hand. Or you can use \gexec in psql:
SELECT 'SELECT last_value - CASE WHEN is_called THEN 0 ELSE 1 END FROM ' || pg_get_serial_sequence('b2', 'big_id')\gexec

See:

Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?

For automated (or regular) use, you need dynamic SQL to use the text value returned by the function as SQL identifier. Here is a simple custom function to do that. Also factored in is_called:
CREATE OR REPLACE FUNCTION sequence_currval(_tbl text, _col text, OUT currval bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
  EXECUTE 'SELECT last_value - CASE WHEN is_called THEN 0 ELSE 1 END FROM ' || pg_get_serial_sequence(_tbl, _col)
  INTO currval;
END
$func$;

Call:
SELECT sequence_currval('foo', 'id');

This is safe against SQLi because pg_get_serial_sequence() returns the identifier safely double-quoted as needed.
The function returns the last value that has been consumed from this sequence. (Does not mean it made its way into the table.) The next one will be greater. (Assumes a default sequence with INCREMENT 1!)
It does not consume a serial ID and does not require that nextval() has been called in the same session.
Consider this demo:
db<>fiddle here


Oracle SQL Developer: Copy paste tables, with 2 different instances, with different table structure Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2, with additional filtration and joining with another table called LKUP.CTL_RWA_VERSION Below is the syntax that I have that is running in DEV.

begin
    for r in (select DISTINCT TABLE_NAME from all_tab_columns where owner = 'DDSHIST' and COLUMN_NAME = 'SNAPSHOT_DT')
      loop
      begin
       execute immediate     'INSERT INTO ||r.table_name|| @DBLINKSIT2
                          select a.*
                          from DDSHIST.||r.table_name|| a
                          INNER JOIN LKUP.CTL_RWA_VERSION b ON a.SNAPSHOT_DT = b.SNAPSHOT_DT and a.DDS_VERSION = b.DDS_VERSION
                          WHERE b.GOLDEN_COPY = 'N'';
       exception when others then null;
      end;
      end loop;
    end;

I put COLUMN_NAME = 'SNAPSHOT_DT' because some of the tables do not contain this column. So the joining condition is both SNAPSHOT_DT are the same, and DDS_VERSION are the same, WHERE golden copy in LKUP table = 'Y'. then loop the script, and insert into @DBLINKSIT2.

But I can't get the script to run and I don't know where I am getting this wrong.

Any help would be appreciated. Thank you.

"However, the tables in the DEV keeps adding/changing, which is why when I run the query, it hit the error saying the table/view does not exist."

Short of putting a lock on every object in the remote database during your copy, I'm not sure this is avoidable. In truth your approach this is unneccessarily complicated. If you want to clone your DB, use the existing cloning tools. There's no need to reinvent the wheel for this application. I'd suggest using datapump to export/import your schema if you want a consistent snapshot.


What blocks a query in Postgres I have a query running in Postgres 9.3 for more than 24h now (namely an EXPLAIN ANALYZE VERBOSE, linked to another question).

I check the "Server status" in pgAdmin which shows the query "Active". I ran some standard lock detection but it didn't return anything.

I'm not sure why it's taking so long: is it waiting for a lock that it can never get (no point in waiting)? Is it just slow (let it run longer)?

In case it's relevant, the database itself is quite big (500 million rows on one of the foreign table, several millions on others) and the data directory uses 240 out of the 340 GB of the disk, so has ~100 GB left. It is a Windows 2012 Server with 12 GB RAM, 6 GB left.

EDIT: The query itself finally finished after 28h30 (because of recursive-like delete), but the question is still valid: what can I check to make sure I'm not waiting in vain? (removed postgresql-9.3 because it would be better to have a more general answer)

That your query on the lock table didn't return anything provided the answer to your question--it was not waiting on a lock.  (Assuming you did it correctly).

It is still kind of annoying and error-prone to have to open a new session and run the query and interpret the results, so I wrote a patch to send NOTICE when you are waiting on a lock.  There were several proposed ways to improve it, but no consensus on the best way, so for now it is just a proposal.  If you are willing to compile your own PostgreSQL you can always adopt this patch for your use.  I don't know what pgAdmin will do with such notifications, however, as I use psql not pgAdmin as my main tool.

As @a_horse_with_no_name indicates, you can also use columns in pg_stat_activity to get information on this.  The quality of that information improves from version to version.  Running older versions does come at a price.  Putting in the effort to upgrade every couple years is well worth it.

Finally, your system monitoring tools are invaluable. A quick look at "top" (for Linux) or "Task Manager" (for Windows) would probably have immediately indicated that your job was busy doing something--either CPU or disk.


Synchronous data transfer from SQL Server to Oracle I have a requirement like:

  The table is present both in a SQL Server Database and an Oracle database with appropriate data types. Whenever a record is inserted into or updated in SQL server database table, then the same operation us to be performed on the Oracle database table.

Example: Table_SQL in SQL Server DB and Table_Oracle in Oracle DB. A record with id=1 is inserted into Table_SQL, now the same record will also be inserted into Table_Oracle automatically without running an external package or anything.

Is there anyway to do it?

One option with SQL Server is Heterogeneous Database Replication. This works with SQL Server as a source and destination being either of:

  The following non-SQL Server databases are supported as Subscribers to snapshot and transactional publications:

  Oracle for all platforms that Oracle supports.
  IBM DB2 for AS400, MVS, Unix, Linux, and Windows.

Snapshot Replication is best if:

  Using snapshot replication by itself is most appropriate when one or more of the following is true:

  Data changes infrequently.
  It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time.
  Replicating small volumes of data.
  A large volume of changes occurs over a short period of time.

Therefore it seems that Transactional Replication is your best option:

  Transactional replication is typically used in server-to-server environments and is appropriate in each of the following cases:

  You want incremental changes to be propagated to Subscribers as they occur.
  The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.
  The application requires access to intermediate data states. For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.
  The Publisher has a very high volume of insert, update, and delete activity.
  The Publisher or Subscriber is a non-SQL Server database, such as Oracle.

You could also look down the Linked Server road and update it with a trigger. I think it could be a lot easier to setup if you have only a single table to sync.

However I will only insert some links as I have never tried it and can't try it right now:

Microsoft How to set up and troubleshoot a linked server to an Oracle database in SQL Server
MSDN Creating a Linked Server for Oracle in 64bit SQL Server
MSDN How to get up and running with Oracle and Linked Servers
Technet Oracle Provider for OLE DB

Be careful with the 32/64-bit providers and choose the right one.


Any suggestions on having master-master replications around the world by nosql solutions? I would like to set up databases that are put at different data centres around the world. For this, I think some nosql solutions may suit the case. For example, couchdb, maybe. Eventual consistency is ok for this.

The case does not limit to document based solution. But I would like to know the possibility, potential problems for setting up master-master replications around the world as I see the cases the servers are in same data centre most likely.

Thanks.

The two NoSQL DBs that come to mind when mentioning cross datacenter replication ("master-master replications around the world") are:

Cassandra
Couchbase

Both of them seem to have fairly decent cross datacenter replication abilities, so which you should go with should be decided upon in a different manner.

If you want a KV-Store (where you can simply look up binary blobs using keys), then you ought to go with Couchbase.

If you want something similar to the SQL concept of rows and tables and whatnot (albeit, without relational functionality such as JOIN), then Cassandra and CQL might suit you better.


E11000 duplicate key error While Adding Shard I have setup a replica set and want to add it in a mongos. But I got below error in mongos:

mongos> sh.addShard("rs3/172.19.0.12:27017,172.19.0.6:27017,172.19.0.5:27017")
{
    "code" : 11000,
    "ok" : 0,
    "errmsg" : "E11000 duplicate key error collection: admin.system.version index: _id_ dup key: { : \"shardIdentity\" }"
}

from the above message, it says admin.system.version has duplicate key error. The collection admin.system.version should be set by mongodb. I don't understand why it has a duplicate key.

All mongo instances are 3.4.4 version.

Below is the replica set status:

rs3:PRIMARY> rs.status()
{
    "set" : "rs3",
    "date" : ISODate("2017-07-09T02:13:37.146Z"),
    "myState" : 1,
    "term" : NumberLong(10),
    "heartbeatIntervalMillis" : NumberLong(2000),
    "optimes" : {
        "lastCommittedOpTime" : {
            "ts" : Timestamp(0, 0),
            "t" : NumberLong(-1)
        },
        "appliedOpTime" : {
            "ts" : Timestamp(1499566407, 1),
            "t" : NumberLong(10)
        },
        "durableOpTime" : {
            "ts" : Timestamp(1499563905, 1),
            "t" : NumberLong(9)
        }
    },
    "members" : [
        {
            "_id" : 0,
            "name" : "172.19.0.12:27017",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 2493,
            "optime" : {
                "ts" : Timestamp(1499566407, 1),
                "t" : NumberLong(10)
            },
            "optimeDate" : ISODate("2017-07-09T02:13:27Z"),
            "electionTime" : Timestamp(1499563936, 1),
            "electionDate" : ISODate("2017-07-09T01:32:16Z"),
            "configVersion" : 414750,
            "self" : true
        },
        {
            "_id" : 1,
            "name" : "172.19.0.5:27017",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 1403,
            "optime" : {
                "ts" : Timestamp(1499566407, 1),
                "t" : NumberLong(10)
            },
            "optimeDurable" : {
                "ts" : Timestamp(1499563905, 1),
                "t" : NumberLong(9)
            },
            "optimeDate" : ISODate("2017-07-09T02:13:27Z"),
            "optimeDurableDate" : ISODate("2017-07-09T01:31:45Z"),
            "lastHeartbeat" : ISODate("2017-07-09T02:13:35.870Z"),
            "lastHeartbeatRecv" : ISODate("2017-07-09T02:13:35.854Z"),
            "pingMs" : NumberLong(0),
            "syncingTo" : "172.19.0.12:27017",
            "configVersion" : 414750
        },
        {
            "_id" : 2,
            "name" : "172.19.0.6:27017",
            "health" : 1,
            "state" : 3,
            "stateStr" : "RECOVERING",
            "uptime" : 2487,
            "optime" : {
                "ts" : Timestamp(1499070510, 1000),
                "t" : NumberLong(3)
            },
            "optimeDurable" : {
                "ts" : Timestamp(1499070510, 1000),
                "t" : NumberLong(3)
            },
            "optimeDate" : ISODate("2017-07-03T08:28:30Z"),
            "optimeDurableDate" : ISODate("2017-07-03T08:28:30Z"),
            "lastHeartbeat" : ISODate("2017-07-09T02:13:35.865Z"),
            "lastHeartbeatRecv" : ISODate("2017-07-09T02:13:36.965Z"),
            "pingMs" : NumberLong(0),
            "configVersion" : 414750
        }
    ],
    "ok" : 1
}

Problem is that your replica set (rs3) has been or it is part of some other cluster! Replica set what has never been connected to shard don't have that document. Error comes when cluster tries to add this document to this admin.system.version during connection-to-cluster procedure.

If you query that collection, you can see "which" cluster that RS thinks it belongs.

If you know that this rs3 is not part of any other cluster, you can connect directly to that replica set and remove that document from admin.system.version -collection. After that, addShard command on mongoS should work fine.


SQL Insert Into New Table Or Else Insert Overwrite Into Existing Table What is the SQL to do the following in SQL Server:

I want to take rows from tbl_A and insert the distinct grouping of them into tbl_B.

If tbl_B doesn't yet exist, I want tbl_B to be created exactly like tbl_A, but receive only the distinct grouping of tbl_A's rows.

If tbl_B already exists, I still want the same data inserted but I want all existing rows in tbl_B to be deleted beforehand.

Of course, I need to do this across multiple tables (about 100).

Also, at the expense of stating the obvious, I do not want to have to specify any column names anywhere. The only parts of the script that should need to change as I do this for each pair of tables is the table names. Thanks!

Since you are not going to specify the column names you are going to have to go with SELECT * it is not ideal, but works if the tables are going to be new.

You could do it with the query below, but you will have to change your grouping columns each time. Unless you can simply use DISTINCT.

If your tables are big, You should look into splitting it in batches.

More info on batches

Test TableA

CREATE TABLE  dbo.tbl_A(ID INT, Val varchar(255));

INSERT INTO  dbo.tbl_A(id,val)
VALUES(1,'bla'),(1,'bla'),(2,'bla'),(2,'ugh'),(3,'bla');

Query with group by

IF (EXISTS (SELECT *
                 FROM INFORMATION_SCHEMA.TABLES
                 WHERE TABLE_SCHEMA = 'dbo'
                 AND  TABLE_NAME = 'tbl_B'))
BEGIN
    TRUNCATE TABLE dbo.tbl_B
    INSERT INTO  dbo.tbl_B
    SELECT *
    FROM dbo.tbl_A
    GROUP BY ID,VAL;
END
ELSE
BEGIN
    SELECT *
    INTO dbo.tbl_B
    FROM dbo.tbl_A
    GROUP BY ID,VAL;
END

Query with distinct

  IF (EXISTS (SELECT *
                     FROM INFORMATION_SCHEMA.TABLES
                     WHERE TABLE_SCHEMA = 'dbo'
                     AND  TABLE_NAME = 'tbl_B'))
    BEGIN
        TRUNCATE TABLE dbo.tbl_B
        INSERT INTO  dbo.tbl_B
        SELECT DISTINCT *
        FROM dbo.tbl_A;
    END
    ELSE
    BEGIN
        SELECT DISTINCT *
        INTO dbo.tbl_B
        FROM dbo.tbl_A;
    END

DB<>Fiddle


How to use single domain name to connect to multiple database servers? We have multiple Windows server 2016 machines hosting single SQL server instance on each server. Every server hosts ~100 databases. We have a master table in which has a record for each database & its connection string. Connection string contains IP of the server where database is hosted & some other typical information.  Since databases are distributed across multiple servers we have to query on master table first to get correct connection string & then connect to the desired database to perform any operation.

As we have multiple DB server(and new ones might get added in near future), it would be great if we can put all our DB servers under single domain name so that all DBs can be accessed through one domain name. This will give us flexibility of changing IPs if needed without updating connections strings. Also adding new servers will become easy without changing any application code.

To be able to achieve this, Can someone please put me in right direction on how to do following?

How to add a domain name to existing SQL server instance?
How to add a proxy which will accept connection requests from application code & forward it to correct database server using some logic?

Thanks in advance!

Assuming your intent is for the application code to connect and use the proxy as if it were a SQL instance (i.e. connect using a SQL API and executes queries), don't go there.

Consider creating a DNS alias entry (CNAME record) for each database (not instance) and specify that name in the connection string along with the database name. That will provide abstraction over instance hosting the database so database can be moved with no connection string changes.

Example with 2 SQL instances hosting 3 databases:

db1.yourdomain.local -->instance1.yourdomain.local
db2.yourdomain.local -->instance1.yourdomain.local
db3.yourdomain.local -->instance2.yourdomain.local

Example connection string:

Data Source=db1.yourdomain.local;Initial Catalog=db1;Integrated Security=SSPI;


how to automatically create the associated schema when I `pg_restore` a table? I backed a table into a file using something like pg_dump --table=ex.tab.

But when I restore the dump file into a newly created database, it complains that the schema ex does not exist. Looking into the dump file ( listed below), it does not have a CREATE SCHEMA statement.

Is there an option that I can add to pg_dump or pg_store so that the schema of the table is automatically created?

Note: I do this for multiple tables in the command line (multiple --table=), and do not want to manually create schema for each table beforehand (if possible).

--
-- PostgreSQL database dump
--

-- Dumped from database version 11.1 (Ubuntu 11.1-3.pgdg18.04+1)
-- Dumped by pg_dump version 11.1 (Ubuntu 11.1-3.pgdg18.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: tab; Type: TABLE; Schema: ex; Owner: myuser
--

CREATE TABLE ex.tab (
    name character varying(80),
    state_ character varying(2)
);

ALTER TABLE ex.tab OWNER TO myuser;

--
-- Data for Name: tab; Type: TABLE DATA; Schema: ex; Owner: myuser
--

COPY ex.tab (name, state_) FROM stdin;
San Francisco   CA
ST LOUIS    MO
LOS ANGELES CA
\.

--
-- PostgreSQL database dump complete
--

Use pg_dump --schema option for this.

With:

$ psql
psql (9.4.19)
Type "help" for help.

mevzek=# CREATE SCHEMA test1;
CREATE SCHEMA
mevzek=# CREATE TABLE test1.atable (id SERIAL);
CREATE TABLE
mevzek=# \q

You can compare the results of the two commands:

$ pg_dump --table test1.atable > dump1.sql
$ pg_dump --schema test1 > dump2.sql
$ diff -u dump1.sql dump2.sql
--- dump1.sql   2019-07-18 12:55:53.000000000 -0500
+++ dump2.sql   2019-07-18 12:56:59.000000000 -0500
@@ -10,6 +10,15 @@
 SET check_function_bodies = false;
 SET client_min_messages = warning;

+--
+-- Name: test1; Type: SCHEMA; Schema: -; Owner: mevzek
+--
+
+CREATE SCHEMA test1;
+
+
+ALTER SCHEMA test1 OWNER TO mevzek;
+
 SET default_tablespace = '';

 SET default_with_oids = false;

If you add --table it does not seem to create the schema anymore. You could go around that with two commands: pg_dump --schema test1 --exclude-table-data '*' and then pg_dump --table test1.atable --data-only or similar.


Using Left or substring with wildcard in SQL I am looking to retrieve information from a field, but am having troubles getting exactly what I want.  The information I want has no set order and I am also looking for two different things.  Here is an example of the information I am working with.

|PEvent|   PDate     |         PDescription                          |
|  3  | 2014-11-21 | Padding, 90 Series, Stage, SF550                |
|  4  | 2014-11-21 | Padding, 45 Series, 144 Stage SF90, 15 spor, AR |
|  5  | 2013-03-06 | 400 Series, 96 STAGE RE FLEX 30                 |
|  6  | 2015-01-09 | Padding, 20 Series, 13 Stage SF 550, Housing    |

What I want to get is SF and the number and FLEX and the number that follows it, in these cases SF550, SF90, SF 550 FLEX 30.  I would also like to get FLEX and the number that follows it.  The problem I am having is that there are so many differences in the data.  I cannot say 10 spaces from the left since it is not always 10 spaces.  I have the same problem from the right.  The other issue I have is that sometimes SF and FLEX will be right against the number where other times there will be a space in between them.

I have created a SQL Fiddle sample database with more records for testing.

SQL Fiddle

The following assumptions have been made (some of them possibly repeating parts of your description):

SF/FLEX is always followed either by a number immediately or by a space character and then a number.
There is always one space character before SF/FLEX, unless the item is at the beginning of the string.
There is always a comma or a space after the number that follows SF/FLEX, unless the item is at the end of the string.

With those in mind, here is my approach:

SELECT
  t.*,
  Item = LEFT(item.S, CHARINDEX(' ', item.S + ' ', LEN(prefix.S) + spaces.N + 1))
FROM
  dbo.testdesc AS t
  CROSS JOIN (VALUES ('SF'), ('FLEX')) AS prefix (S)
  CROSS JOIN (VALUES (0), (1)) AS spaces (N)
  CROSS APPLY (SELECT NULLIF(PATINDEX('% ' + prefix.S + SPACE(spaces.N) + '[0-9]%', ' ' + t.PDescription), 0)) AS startpos (N)
  CROSS APPLY (SELECT CHARINDEX(',', t.PDescription + ',', startpos.N + LEN(prefix.S) + spaces.N)) AS endpos (N)
  CROSS APPLY (SELECT SUBSTRING(t.PDescription, startpos.N, endpos.N - startpos.N)) AS item (S)
WHERE
  startpos.N IS NOT NULL
;

This is the output of the above query:

PEvent  PDate                    PDescription                                               Item
------  -----------------------  ---------------------------------------------------------  -------
3       2014-11-21 00:00:00.000  Padding, 90 Series, Stage, SF550                           SF550
4       2014-11-21 00:00:00.000  Padding, 45 Series, 144 Stage SF90, 15 spor, AR            SF90
8       2013-06-14 00:00:00.000  Padding, 900 Series, 140 Stage SF1100, 1/16, Money, TS XB  SF1100
10      2014-12-30 00:00:00.000  Padding, 444 Series, 47 Stage SF330, 5/9, house            SF330
6       2015-01-09 00:00:00.000  Padding, 20 Series, 13 Stage SF 550, Housing               SF 550
5       2013-03-06 00:00:00.000  400 Series, 96 STAGE RE FLEX 30                            FLEX 30
7       2013-06-14 00:00:00.000  Padding, FLEX 10 400 Series, 11 Stage, BYHMP               FLEX 10
9       2015-02-27 00:00:00.000  Flex 10 40 87 stage                                        Flex 10

And to explain some of the trickery employed by the query, this is how it works:

The original row set is cross joined with two inline views, one that represents the required prefixes (prefix) and one that specifies how many spaces there may be between the prefix and the subsequent number (spaces). This way the query will probe for all combinations of prefixes and numbers of delimiting spaces (SF, SF , FLEX, FLEX ).
The PDescription string is searched for each combination, one after one, to calculate the starting position of the prefix. It uses PATINDEX, which, for each prefix and space number, builds its own search pattern. As per the assumptions above, PATINDEX expects a space before the prefix and at least one digit after the prefix and, possibly, a space.

So, if, for instance, the current prefix is SF and the current expected number of spaces after it is 1, the pattern would be % SF [0-9]%. To make sure there is a space before the prefix, a space is added at the beginning of PDescription.

That trick accomplishes two things: it helps to find the prefix if it is at the beginning of the string and it makes the resulting starting position accurate. The latter is important because technically the returned position would match not the prefix itself but the preceding space. So, to get the actual position of the prefix, we would need to increment the result by 1. However, because we had added a space at the beginning of the source, all the positions were shifted forward already. So, in the end, the result points exactly at the beginning of the prefix.

One final touch here is that if the returned position is 0, it is transformed into NULL by NULLIF. That helps to avoid errors about negative length values passed to SUBSTRING later.
The found position is then used in CHARINDEX to find the first comma in PDescription found after that position. Again, to make sure there is a comma, the character is appended to PDescription. The result constitutes the ending position of the sought item.
Using the obtained starting and ending positions, the item is extracted from PDescription using the SUBSTRING function.
Finally, in the SELECT clause, before actually returning the item, the query additionally checks if there is a space after the number, just in case the item was not properly delimited by a comma. By appending a space to the item, it uses the same trick as when searching for the comma position, also taking into account the length of the prefix and the expected number of spaces after the prefix to make sure the found space character will be the one after the number.
In cases where the prefix is not found in the string, Item returns NULL. Such rows, however, are excluded from the output by the WHERE clause, which specifies that the starting position be not NULL.

I am not sure if the above description is clear enough but hopefully it will help together with the code itself.


In sqlite, how are return values chosen for columns that are not used for aggregation or grouping? Given an SQLite database with three columns (char, char, int), the following query

select column2, avg(column3) from table_name group by column2;

will return the average values of column3 aggregate for each distinct value of column2.

What will the following query return?

select column1, column2, avg(column3) from table_name group by column2;

I came across this case while playing with Codeacademy's SQL tutorial. This particular query returns a value of column1 for each distinct value of column2, but I can't figure out how these values were chosen.

The database will return the value from some arbitrary row in the group.
(In the current implementation, it's the value from that last row in the group that was processed, but due to indexes or other optimizations, the processing order might not be predictable.)

Since SQLite 3.7.11, using MIN() or MAX() guarantees that values from a row with the minimum/maximum value are returned.


How to define shardKey on GridFS collections to achieve Location/Data Centre affinity (MongoDB) I have set up a mongo cluster as follows:

3 shards, one per global region APAC/EMEA/AMER (in different data centres)
Shard Key is REGION + 'MONOTONICALLY INCREASING PRIMARY KEY'
The "writing" clients for the cluster [typically] connect to the mongos in their region, and will be writing documents with their region's region.
The "reading" clients for the cluster [typically] connect to the mongos in their region, and will be reading documents from across multiple regions (broadcast query).

The system is highly write intensive, with very few reads. Thus, overall I believe the above setup is optimal, since writers get fast "local" writes, while readers get convenience of single query to read across the shard, albeit slightly slow [and just to underline, for reading, I value convenience over speed].

OK so that's the background. But then suddenly I realized: I'm also using GridFS to store files, and I want the files to get stored in same way (i.e. write to local region).

Hopefully in the context of the above, my original question makes sense:

How to define shardKey on GridFS collections (fs.files/fs.chunks) to achieve Location/Data Centre affinity?

This is what I've tried:

I've noticed that I can add meta-data in during the upload operation, such that the fs.FILES collection has the region in as an accessible field that could be used as part of the sharding key for fs.files (i.e. sharding key REGION + "FILE_ID").

However, what about fs.chunks? will they follow around their "parent" fs.file record and get routed to the same shard as the "parent" fs.file?

Thanks in advance!

However, what about fs.chunks? will they follow around their "parent" fs.file record and get routed to the same shard as the "parent" fs.file?

The answer is no!

You could use files.aliases or files.metadata to store that REGION.

How ever maybe best solution (both chunks and files) is NOT to shard your GridFS. Create three different GridFS databases (APAC/EMEA/AMER) and then use command movePrimary to change "primary shard" to point right shard in your cluster. Then change your application so that it selects GridFS database base on the user's location. This, of course, works with all other collections what are REGION specific.


Solution to error NID-00135 in Oracle 10g, Solaris 10 We are trying to build a test version of a production database by restoring the production backup using RMAN and renaming the database to test.

We've successfully restored and opened the database as production (with different IP from the real production server).

When using the NID utility to rename the database to test, we are encountering this error:

NID-00135: There are 1 active threads.

We've been having difficulty getting the database to shutdown normally using the SHUTDOWN IMMEDIATE command.  After an hour or so of waiting for the damn thing to die, we are forced to resort to grepping all the ora_* processes and killing them.

We left the listener up, the SQLPlus thread running, the bash thread owned by the Oracle user running, but that's about it for all Oracle-owned processes.

We've even restarted the server.

Are we missing something here?

The "damn thing" tries to prevent you from destroying your database. The error is described in the manual. You did not await the shutdown immediate of your database but killed the oracle processes. Therefor not all sessions could be rolled back. Start the database again and make a shutdown immediate and wait until the database is shut down cleanly.

Stop the listner make a local connection with the dbnewid utility. The dbnewid utility is described in the manual too. You have always a good chance to destroy your database if you handle the dbnewid utility in the wrong way. The dbnewid is necessary to change the id of the database that is used by rman in an rmancatalog. if you dont use an rmancatalog th change of the dbid is not necessary. Renaming can be done by recreating the controlfile. Often it suffices only to change the insatnce name. This can be done in the parameterfile (init.ora, spfile.ora)

The simplest and therefore best way to create a copy of the production database for testing is to use the rman duplicate database command.

The reason why it tooks so long to shutdown your database is that there is a long running transaction that must be rolled back. Such a situation can be avoided if you recover your database to a point in time where no such long transactions where running on the database.


How to get something written to MySQL error log without restart I want to cause the MySQL 5.5 error log to be written to at will without a service stop or start. Note that I do not care what gets written to the error log, as long as something gets written.

I tried running a bad query and setting a nonexistent system variable, but those actions do not seem to generate an entry in the error log.

How can I do this?

Bad queries and other invalid actions typically only write to the error log when there's not a client connection to write the error to... such as when a thread that isn't a client thread encounters an error... or from the event scheduler.

From within a scheduled event, SIGNAL can be used to throw errors and warnings that will be written to MySQL's error log.  And, of course, you can "schedule" an event to run only once, right now.

mysql> USE test;
Database changed
mysql> CREATE EVENT test_error_log ON SCHEDULE AT NOW() ON COMPLETION NOT PRESERVE
       DO SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'hi from the error log';
Query OK, 0 rows affected (0.01 sec)

$ sudo tail /usr/local/mysql/data/hostname.err
--8<-- snip --8<--
150121 14:25:06 [ERROR] Event Scheduler: [sqlbot@%][test.test_error_log] hi from the error log

Alternately, there's a 3rd party user-defined function log_error() that can write to the error log: https://github.com/mysqludf/lib_mysqludf_log

That's assuming you're not "really" wanting to trigger MySQL to politely close and reopen the log file... this is done with FLUSH ERROR LOGS;.


Conditional formatting based on number of days in SSRS against a cell value in reference to another field I have a SSRS report where I have cell displaying a field called activity_date. I would like this to be colour coded based on a number of days against another dataset field called valid_to where if it is greater than 14 days it is green, within 14 days it is yellow and if it is later than the valid_to date it is red. Can any one help me?

I have made some changes to the requirements:

RED     valid_to is prior to today AND cell_value is null
ORANGE  valid_to is prior to today AND cell_value is NOT null
YELLOW  valid_to is between today and 14 days ahead of today and cell_value is null
BLUE    valid_to is in the future and cell_value is NOT NULL
GREEN   valid_to is 15 or more days ahead of today and cell_value is NOT NULL

You will need to come up with an expression for your fill color of that cell or field you want to change. So an example:

The basic setup is I just have 2 variables to enter a date and then a text box on the body of the report I will adjust the fill color based on the days between those two dates:

So I go into the text box properties and find the Fill page, I will set the "Fill Color" to this expression:

=IIF (DateDiff("d", Parameters!activity_date.Value, Parameters!valid_to.Value) > 14
,"Green","Yellow")

Then my result:

In your case you will just build out the "IIF" to follow the criteria you have for your formatting.

This is just a guess from doing it in my head (and writing it out in Notepad++) but I think this should be what you are looking for:

=IIF(Fields!valid_to.Value = Today() AND IsNothing(Fields!cell_value.Value) = 1, "Red",
    IIF(Fields!valid_to.Value < Today() AND IsNothing(Fields!cell_value.Value) = 0, "Orange",
        IIF(Fields!valid_to.Value = Today() AND DateDiff("d",Today(),Fields!valid_to.Value) <= 14, "Yellow",
            IIF(Fields!valid_to.Value > Today() AND IsNothing(Fields!cell_value.Value) = 0, "Blue",
                IIF(DateDiff("d",Today(),Fields!valid_to.Value) >= 15 AND IsNothing(Fields!cell_value.Value) = 0, "Green","White")
                )
            )
        )
    )


Should the Redo log on a production MySQL server be configured to another location? Concerning the innoDB storage engine, should the innodb_log_group_home_dir option (The directory path to the InnoDB log files) be configured to another disk, other than the default directory, in order to separate the log I/O activity from the actual data files?

Are there advantages, like in other DBMS, to put the data directory on a RAID5 storage volume and the redo log on a RAID1?

Yes, that would be a good idea. I have mentioned a FaceBook Engineer's Blog about doing that, along with putting the DoubleWrite Buffer and Tablespace Files in other places, in my other posts

Feb 24, 2012 : Tuning dedicated Percona Server with XtraDB for OLTP (Also See Switching Gears TO RAID)
Feb 06, 2014 : MySQL on SSD - what are the disadvantages?
Jan 27, 2015 : what would be the primary disadvantages of splitting my tables into table spaces and/or separate schemata

RAID5 ??? OMG, NO !!!!!
Please do not put anything InnoDB-related in RAID5

Feb 14, 2012 : Is raid 5 suitable for a mysql installation?
Mar 24, 2013 : Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating

Since you will need the the redo logs only in the event of crash recovery, I would go with RAID10 SAS Drives for the redo logs because they are sequentially written files. I would shy away from RAID1 because you want full redundancy of disk in the event of a disk crash. RAID5 does plenty of parity checking which can inhibit sequential writes.


Multibyte replace not working in SQL Server Why does one work and the other doesn't?

SELECT REPLACE(N'IAșI' ,N'ș','x') -- doesn't match (returns 'IAșI')
SELECT REPLACE('IAșI' ,'ș','x') -- works (returns 'IAxI')

My guess would be that in the second example SQL Server sees 'ș' as 2 characters (0x19 02) and doesn't do some collation optimisations that ruin the multibyte match.

What should be done for the multibyte example to work?

I can't explain why the first one isn't working (perhaps some subtlety of that particular Unicode character). This produces a rather unexpected result:

SELECT REPLACE(N'IAșI', N'șI', 'x') -- returns 'xAșx'

The second one will appear to work because SQL Server replaces multi-byte characters with '?' when casting down to varchar.

This small example demonstrates the behavior, as all arguments will be converted to varchar literals:

SELECT 'IAșI', REPLACE('IA?I', 'ș', 'x'), REPLACE('IAșI', '?', 'x') -- returns 'IAxI'

EDIT: Seems to be collation related.

SELECT REPLACE(N'IAșI' COLLATE Latin1_General_CI_AS, N'ș', 'x') -- doesn't match (returns 'IAșI')
SELECT REPLACE(N'IAșI' COLLATE Latin1_General_Bin, N'ș', 'x') -- returns 'IAxI'


Find X parents with recursive postgres Have issue with building a recursive query in Postgres.

Tried with this one but it doesn't seem to work. So table user have user_id and referred_by_user_id. referred_by_user_id can be empty, if it is empty we don't need to go up anymore.

So need to find all referrers above a user, going in dynamical depth. Current implementation with limit is naive, I would assume I can exit as soon as I reach X depth.

WITH RECURSIVE uplines(user_id, referred_by_user_id) AS (
     SELECT t.user_id,  t.referred_by_user_id
  FROM postgres.user AS t
  WHERE t.user_id = $userId
  UNION ALL
  SELECT t.user_id, t.referred_by_user_id
  FROM postgres.user AS t JOIN uplines AS a ON t.user_id = a.referred_by_user_id
)
SELECT uplines.user_id FROM uplines LIMIT $depth;

Have read bunch of SO posts about it but still cannot get it to work.

I think what you want can be achieved by adding in the column list a "depth" or "level" that increases by one for each recursion:

WITH RECURSIVE uplines(user_id, referred_by_user_id, depth) AS (
  SELECT t.user_id,  t.referred_by_user_id, 0
  FROM postgres.user AS t
  WHERE t.user_id = $userId
  UNION ALL
  SELECT t.user_id, t.referred_by_user_id, a.depth + 1
  FROM postgres.user AS t JOIN uplines AS a ON t.user_id = a.referred_by_user_id
  WHERE a.depth < $depth
)
SELECT user_id, depth
FROM uplines
ORDER BY depth ;


Creating an entity-relationship diagram for a recipes business domain I am doing this simple ASP project for my uni, just wondering if you can guys direct me or give me some advice.

The project Idea is to create a recipe website which will allow users to add recipes and then admin will approve it.

Recipes will be divided in to few categories: Food Region: 'East', 'South', etc., and Categories:'Beef', 'Noodles', etc.

Can you guys check if I am on the right direction?
Is my entity-relationship diagram (ERD) below correct?

It's difficult to say if the ER is correct not knowing what is acceptable entered data.  Based on your current design, including UserID in UserRecipes is unnecessary since you can get that information through RecipeID and joining it with the Recipes table and then using fkUserID.

But I think if Recipes table is supposed to be a dish like "Lasagna" and different users can submit their own lasagna recipes and you wish to capture that in UserRecipes, you need to probably move at least "Preptime", "Cooktime", "Ingredients", "Tools" and "Photos" to UserRecipes table.  Then establish a many-to-many relationship between Recipes and Users in UserRecipes.  Then remove fkUserID from Recipes and keep UserID in UserRecipes.

Just a side-note, I'd suggest keeping a more consistent naming convention for naming your foreign keys.  In some cases, you are using CatID as a foreign key for CategoryID and then you're using fkUserID as a foreign key for UserID.


how do i recover an inconsistent backup after another full backup without restoring the new full backup? I have restored a 2 week old full inconsistent backup and recovering it daily using level 1 incremental backup. And then another full backup was taken. Can I still recover the old restored file using the incremental log taken after the new full backup?

No.

About RMAN Incremental Backups

  A level 1 incremental backup can be either of the following types:

  A differential incremental backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0.
  A cumulative incremental backup, which backs up all blocks changed after the most recent incremental backup at level 0.

Your recent incremental backups contain changes since your another full backup, not the previous full backup.

But you can still recover your database using archivelogs.

Edit: and here is a demo: https://pastebin.com/kn2R5FXa


RCSI and index maintenance I have a database that is RCSI enabled and I've been rebuilding my indexes online (SQL 2014 Enterprise) for as long as I can remember.  My understanding is that if we were to rebuild the indexes offline, we would lose those additional 14 bytes. However as long as we continue to rebuild online or reorganize we will retain the 14 bytes per row.  Is that correct?

However as long as we continue to rebuild online or reorganize we will retain the 14 bytes per row. Is that correct?

This is true.  REBUILD WITH (ONLINE = ON) and REORGANIZE will leave the 14 bytes as-is.  A normal REBUILD will clear it.

If you disable RCSI, the 14 byte version tag will be cleared by any of the three operations you mentioned:

REBUILD
REBUILD WITH (ONLINE = ON)
REORGANIZE

For your future reference, it's not too hard to test this yourself.  Just create a database and table, and then try the different operations, checking the row size each time.

USE [master];
GO
CREATE DATABASE AreSeaEssEye;
GO

USE [AreSeaEssEye];
GO

CREATE TABLE dbo.Foo (c1 INT NOT NULL);
INSERT INTO dbo.Foo (c1) VALUES (1);

ALTER TABLE dbo.Foo ADD CONSTRAINT PK_Foo PRIMARY KEY (c1);
-- Baseline (11)
select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id('AreSeaEssEye'), object_id('AreSeaEssEye.dbo.Foo'), null, null, 'DETAILED');

ALTER DATABASE AreSeaEssEye SET ALLOW_SNAPSHOT_ISOLATION ON;
UPDATE dbo.Foo SET c1 = 2;
-- After enabling RCSI and updating (25)
select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id('AreSeaEssEye'), object_id('AreSeaEssEye.dbo.Foo'), null, null, 'DETAILED');

ALTER INDEX PK_Foo ON dbo.Foo REBUILD WITH (ONLINE = ON);
-- After online rebuild (still 25)
select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id('AreSeaEssEye'), object_id('AreSeaEssEye.dbo.Foo'), null, null, 'DETAILED');

ALTER INDEX PK_Foo ON dbo.Foo REORGANIZE;
-- After reorg (still 25)
select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id('AreSeaEssEye'), object_id('AreSeaEssEye.dbo.Foo'), null, null, 'DETAILED');

ALTER INDEX PK_Foo ON dbo.Foo REBUILD;
-- After normal rebuild (back to 11)
select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id('AreSeaEssEye'), object_id('AreSeaEssEye.dbo.Foo'), null, null, 'DETAILED');

And the results?  As expected:


How to restore SQL Server database with mirroring I have SQL Server with mirroring.

I am trying to restore the database using the command:

USE [master]
ALTER DATABASE [CentralStorage] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [CentralStorage] FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\XXX.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [XXX] SET MULTI_USER

I tried the followings:

I set the "remove tail log"
I set the "close connections"
override existing database.

I get the error:

  Msg 1468, Level 16, State 1, Line 2
  The operation cannot be performed on database "XXX" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
  Msg 5069, Level 16, State 1, Line 2
  ALTER DATABASE statement failed.
  Msg 3104, Level 16, State 1, Line 3
  RESTORE cannot operate on database 'XXX' because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability group.
  Msg 3013, Level 16, State 1, Line 3
  RESTORE DATABASE is terminating abnormally.

Additional info:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

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.


How to SELECT rows with time duration stored in varchar column? There is a column in table defined like this :

duration varchar(10)

It has value for duration of a MP3 file stored in it like : 52:19  (Minutes : Seconds)

How can I create a SQL query, for selecting rows with duration between 3:00 and 6:45 (i.e. SELECT songs neither too short nor too long)

The database is hosted on some 3rd party server so I cannot modify table structure.

SELECT   * FROM table
WHERE (SUBSTRING_INDEX(duration,':',1)*60)+SUBSTRING_INDEX(duration,':',-1)
BETWEEN 180 AND 405

SQL Fiddle

Use SUBSTRING_INDEX to isolate the minutes and seconds,convert minutes to seconds and add them up so the where condition has something to compare.Assuming all durations in the table have minutes and seconds separated by ':'


How are multiple indexes used in a query by MySQL? Imagine a table where there are separate indexes on columns a and b.

The query runs somewhat like this:

SELECT col1, col2 FROM <table> WHERE a = 'value_1 AND b = 'value_2';

Now, does this query always use the indexes on both the columns? If so, what is the mechanism? And if not, is there any condition which MySQL checks to decide which index to use when more than one are available?

MySQL is capable of doing an INDEX MERGE which allows MySQL to search on both indexes and merge the results. Of course, the MySQL Query Optimizer is the arbitrator of this process.

From my perspective, just looking at your query and its WHERE clause, I would create a compound index on your table and an additional index. Which compound index, (a,b) or (b,a) ?

I would next check the cardinality of each column as follows:

SELECT COUNT(DISTINCT a) a_count FROM `table`;
SELECT COUNT(DISTINCT b) b_count FROM `table`;

Based on these counts

If a_count < b_count, then I would create compound index (a,b) and an index on b
If a_count > b_count, then I would create compound index (b,a) and an index on a
If a_count = b_count, then I would create one pair or other

For more information, please read the following links:

http://www.mysqlperformanceblog.com/2009/09/19/multi-column-indexes-vs-index-merge/
http://brian.moonspot.net/2008/04/22/playing-with-mysql-index-merge/
http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/index-merge-performance


How to select fields using alias name? I have an select:

SELECT
tb_dim_equipe.no_equipe "EQUIPE",
tb_dim_profissional.no_profissional "PROFISSIONAL",
no_cidadao "CIDADÃO",
a.nu_cns "CNS",
sum(case when co_dim_tempo >= 20190100 and co_dim_tempo <= 20200131 then 1 else 0 end) as "TOTAL"
from (
SELECT no_cidadao,
tb_fat_cad_individual.nu_cns,
tb_fat_cad_individual.co_dim_profissional,
tb_fat_cad_individual.co_dim_equipe from tb_fat_cidadao_pec
join tb_fat_cad_individual on tb_fat_cad_individual.nu_cns = tb_fat_cidadao_pec.nu_cns
join tb_fat_cidadao on tb_fat_cad_individual.co_seq_fat_cad_individual = tb_fat_cidadao.co_fat_cad_individual
where st_mudou = 0
and st_vivo = 1
and st_gestante = 1
and tb_fat_cidadao.co_dim_tempo_validade = 30001231) a

left join (
SELECT tb_fat_atendimento_individual.nu_cns,
tb_fat_atendimento_individual.co_dim_tempo
from tb_fat_atendimento_individual
join tb_dim_tempo on tb_dim_tempo.co_seq_dim_tempo = tb_fat_atendimento_individual.co_dim_tempo
where co_seq_dim_tempo >= 20190100
and co_seq_dim_tempo <= 20200131
and ds_filtro_ciaps like '%ABP001%'

union SELECT tb_fat_proced_atend.nu_cns,
tb_fat_proced_atend.co_dim_tempo
from tb_fat_proced_atend
join tb_dim_tempo on tb_dim_tempo.co_seq_dim_tempo = tb_fat_proced_atend.co_dim_tempo
where co_seq_dim_tempo >= 20190100
and co_seq_dim_tempo <= 20200131
and ds_filtro_procedimento like '%0301010110%') b
on a.nu_cns = b.nu_cns
join tb_dim_equipe on tb_dim_equipe.co_seq_dim_equipe = a.co_dim_equipe
join tb_dim_profissional on tb_dim_profissional.co_seq_dim_profissional = a.co_dim_profissional
group by no_equipe, no_profissional, no_cidadao, a.nu_cns
order by no_equipe, no_profissional, no_cidadao

This return to me five columns EQUIPE, PROFISSIONAL, CIDADÃO, CNS and TOTAL

I want to create a new select command like this:

SELECT sum(c.TOTAL) from *query above* c

but I receive the error that column not exists.

How I can use a select with the names of alias columns EQUIPE, PROFISSIONAL, CIDADÃO, CNS and TOTAL?

You quoted your column aliases:

SELECT
tb_dim_equipe.no_equipe "EQUIPE",
tb_dim_profissional.no_profissional "PROFISSIONAL",
no_cidadao "CIDADÃO",
a.nu_cns "CNS",
sum(...) as "TOTAL"

Postgres, unlike standard-compliant SQL databases, normalizes unquoted identifiers by converting them to lower case, such that the reference to c.TOTAL is equivalent to c.total. However, quoted identifiers like as "TOTAL" are not normalized and remain known to Postgres exactly as they are written.

You should either drop quotes in aliases (which I would recommend):

SELECT
tb_dim_equipe.no_equipe AS equipe,
tb_dim_profissional.no_profissional AS professional,
no_cidadao AS cidadao,
a.nu_cns AS cns,
sum(...) AS total

or always reference quoted identifiers as quoted identifiers:

SELECT sum(c."TOTAL") from ... AS c


Finding table name referfences in SSIS packages We are in the process of migration onto a later version of SQL Server, from 2012 to 2016.  Our SSIS packages are deployed to SSISDB, in order to find the tables being referenced by the SSIS packages, we have to open it and go through each SSIS package.
Is there a way to query the table names belonging to SSIS packages?

Dennis

If you check the table [SSISDB].[catalog].[packages], you'll find the packages XML. From there, you can parse the XML (might be quite painful) and extract the  tag.

This might be a good start: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6c31e43f-4f25-476c-8cfa-87f91fbf609e/help-navigating-xml-file-with-sql-nodes-method-against-a-column-of-xml-type?forum=sqlxml


Repair Database using .FRM .IBD .OPT Files ( Hard copy mysql folder ) I had a crash on my server.
The only thing I could do was hard copy the mysql folder
Now I am trying to access my database again through the PHPMyAdmin interface.
Some databases (mostly WordPress) run and run well
But some databases are only viewed and their content is not displayed
For example

I used REPAIR TABLE query but it doesn't help

FINALLY
Thanx Dexter!
Answer link after about 6hours searching the internet:
https://stackoverflow.com/a/56641702/12873930
###Files require to restore
xampp(old folder)/mysql/data/
ibdata1
ib_logfile0
ib_logfile1
<databasename>/*.frm
<databasename>/*.ibd

###Step 1

After installed the same version of xampp.
Do not start the apache or myql

###Step 2

Go to the mysql/data folder and replace the ibdata1, ib_logfile0, and ib_logfile1
Now copy paste your database folder from your old xampp backup to the newly installed xampp folder c:/xampp/mysql/data/ that contain .frm and .ibd files, If you are not sure try with one database.

###Step 3

Go to c:/xampp/mysql/bin and look for my.cn.
Open the my.cn file and look for #skip-innodb and under that look for the line that says innodb_log_file_size=5M change it to 170M. innodb_log_file_size=170M. This is your log file size and if you are not sure just set it to 170

###Step 4

Now open the file mysql_start.bat (Windows Batch file) that is in the c:/xampp/ directory.

Add –innodb_force_recovery=6 after the ... --console
 ....

 mysql\bin\mysqld --defaults-file=mysql\bin\my.ini --standalone --console
 –innodb_force_recovery=6
 if errorlevel 1 goto error
 goto finish

###Step 5

Now Start your Apache and Mysql.
Go to your phpmyadmin and check for your database and its tables. if you do not get any errors you are on the right track.
Stop the Apache and Mysql and copy paste the rest of the databases.


Is there a sql command I can use to manually start replication? The title pretty much says it all. I need a command or a script that will manually kick off SQL Server 2012 replication if it is beyond the time it scheduled to start but hasn't yet.

Is there such a thing?

Sample

-Publisher [ServerName] -PublisherDB [DatabaseName] -Distributor [ServerName] -Publication [PublicationName] -DistributorSecurityMode 1


Is it better to use an ID (int) or a Name (varchar) as PK for a table? Let's suppose I have an ORDERS_LINE table and a PRODUCT table and the ORDERS_LINE table must have the information of which product was ordered. I prefer to use a Primary Key based on the Name since it will be easier to read in the other table and it reduces the chance to have duplicated items. But this is my opinion, I don't really know if there is a standard or if one approach is more efficient than the other.

As usual, it depends. Let's first consider what a key is, it is something that uniquely identifies something in your business. Even if you choose to use a surrogate key for some reason you should still identify and implement the natural candidate key's.

A key ideally has a number of properties

unique
stable  -- should not change frequently, not necessarily immutable though
irreducible  -- no subset of the attributes qualify as a key
simple  -- few attributes
familiar  -- meaningful to the user

Quite often there is a conflict between these properties and some kind of compromise has to be made. A natural key - such as name in your case - may not be stable enough and therefor is a bad choice for a key. A surrogate key on the other hand is not familiar to the user.

To conclude, there is no easy answer to your question, it depends on the situation. Design your keys, and make the best out of it. I would not recommend blindly choosing one approach over the other.