Multiple Indexes and Tuning I'm utilising Azure SQL Database 2019.
Let's say I have columns A-F and the following indexes.

Primary Key (clustered): A
Index 1: B (ASC),C (ASC)
Index 2: B (ASC)

If I wanted to run run the following query below, is Index 2 needed or could it use Index 1 and not impact on performance?
SELECT A,B,C,D,E FROM table WHERE B = 'X'

It depends™
Let's go through this step by step. We'll create a table and all relevant indexes and then populate our table with some data.
Create Table Q275204
USE [StackExchange]
GO

/****** Object:  Table [dbo].[Q275204]    Script Date: 10.09.2020 07:54:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Q275204](
    [A] [nchar](1) NULL,
    [B] [nchar](1) NULL,
    [C] [nchar](1) NULL,
    [D] [nchar](1) NULL,
    [E] [nchar](1) NULL,
    [F] [nchar](1) NULL
) ON [PRIMARY]

GO

Now we have an empty table. Let's add the indexes.
Create Index CPIX_Q275204_A_hot2use_20200910
This will be a clustered primary key index on the table Q275204 containing the column A.
USE [StackExchange]
GO

/****** Object:  Index [CPIX_Q275204_A_hot2use_20200910]    Script Date: 10.09.2020 07:56:44 ******/
CREATE CLUSTERED INDEX [CPIX_Q275204_A_hot2use_20200910] ON [dbo].[Q275204]
(
    [A] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Create Index NIX_Q275204_BC_hot2use_20200910
Next index up is the non-clustered index on table Q275204 containing the columns B and C.
USE [StackExchange]
GO

/****** Object:  Index [NIX_Q275204_BC_hot2use_20200910]    Script Date: 10.09.2020 07:58:40 ******/
CREATE NONCLUSTERED INDEX [NIX_Q275204_BC_hot2use_20200910] ON [dbo].[Q275204]
(
    [B] ASC,
    [C] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Create Index NIX_Q275204_B_hot2use_20200910
USE [StackExchange]
GO

/****** Object:  Index [NIX_Q275204_B_hot2use_20200910]    Script Date: 10.09.2020 08:00:09 ******/
CREATE NONCLUSTERED INDEX [NIX_Q275204_B_hot2use_20200910] ON [dbo].[Q275204]
(
    [B] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

First Query Without Data
We now have an empty database without any data. What index will be used for your query? Will an index be used?
SELECT A, B, C, D, E FROM dbo.Q275204 WHERE B = 'X'

As it turns out, the Query Engine selects the Clustered Index. This is because the Clustered Index is the actual data and because of that, the fastest way to retrieve the data is to just scan the clustered index:

The query plan can be viewed here online (Brent Ozar's Paste The Plan)
Add Data And Query Table For 2nd Time
Let's add some data to the table to change things:
USE [StackExchange]
GO
INSERT INTO [dbo].[Q275204]
  ([A], [B], [C], [D], [E], [F])
VALUES
  ('X', 'X', 'X', 'X', 'X', 'X')
GO 10000

...and query the table again
SELECT A, B, C, D, E FROM dbo.Q275204 WHERE B = 'X'

...to get the following execution plan:

And online (Brent Ozar's Paste The Plan)
Differences
This time the Query Engine has decided to use clustered index scan again, but is suggesting that we should create a new index on column B and to include the columns A, C, D and E.
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Q275204] ([B])
INCLUDE ([A],[C],[D],[E])
GO

The query optimizer hasn't used the existing index NIX_Q275204_BC_hot2use_20200910 (columns B and C) nor has it used the index NIX_Q275204_B_hot2use_20200910 (column B).
Musings
At this point you could be misled to believe that deleting all the indexes would be the best option. I mean, hey, they aren't being used and only taking up space.
That assumption could be wrong. The indexes aren't being used yet, because the distribution of the current data (all data are X) doesn't require the query optimizer to consider using other indexes other than the clustered index (with the scan option). The query optimizer knows (because of the statistics of the index) that the data in the table are all X and so scanning the clusterd index to retrieve all the data is still the fastest option available.
Statistics?
Yes. When you create an index the database engine will create statistics based on the distribution of the data in the table/index. The statistics assist the query optimizer in deciding which indexes to use when it is building the query execution plan of a statement.
Here are the indexed and statistics of our current table:

The data or histogram of the distribution of the data in an index can look like this:
Statistics for INDEX 'NIX_Q275204_B_hot2use_20200910'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NIX_Q275204_B_hot2use_20200910  Sep 10 2020  8:59AM             10000                           10000                           1                               0                               4                               YES                                                             10000

All Density                     Average Length                  Columns
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1                               2                               B
1                               4                               B, A

Histogram Steps
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
X                               0                               10000                           0                               1

The statistics provide information regarding the distribution of the data in the index (and the table). Based on this information the query optimizer decides on which (existing) plan it will create (use).
Add More Data And Query Table For 3rd Time
We'll add another 20'000 records of Ys and Zs and see what happens:
USE [StackExchange]
GO

INSERT INTO [dbo].[Q275204]
  ([A], [B], [C], [D], [E], [F])
VALUES
  ('Y', 'Y', 'Y', 'Y', 'Y', 'Y'),
  ('Z', 'Z', 'Z', 'Z', 'Z', 'Z')

GO 10000

The query is the same so let's go straight to the query plan.

And online (Paste The Plan)
(No) Differences
The query plan is the same and the missing index is still being suggested.
Musings
Let's go and have a look what Microsoft has to say about statistics and especially about density. (Have a look at the statistics for non-clustered index after the table contains 30'000 records)

Density is information about the number of duplicates in a given column or combination of columns and it is calculated as 1/(number of distinct values). The query optimizer uses densities to enhance cardinality estimates for queries that return multiple columns from the same table or indexed view. As density decreases, selectivity of a value increases. For example, in a table representing cars, many cars have the same manufacturer, but each car has a unique vehicle identification number (VIN). An index on the VIN is more selective than an index on the manufacturer, because VIN has lower density than manufacturer.

So as the density of an column goes down the selectivity increases and the probability that the index will be used in a SELECT statement is higher.
Let's go and add more data and see if the index will be used at some point...
Add Lots More Data And Query Table For 4th Time
We'll add another 230'000 records of all the letters we haven't yet used and see what happens:
USE [StackExchange]
GO

INSERT INTO [dbo].[Q275204]
  ([A], [B], [C], [D], [E], [F])
VALUES
  ('A', 'A', 'A', 'A', 'A', 'A'),
  ('B', 'B', 'B', 'B', 'B', 'B'),
  ('C', 'C', 'C', 'C', 'C', 'C'),
  ('D', 'D', 'D', 'D', 'D', 'D'),
  ('E', 'E', 'E', 'E', 'E', 'E'),
  ('F', 'F', 'F', 'F', 'F', 'F'),
  ('G', 'G', 'G', 'G', 'G', 'G'),
  ('H', 'H', 'H', 'H', 'H', 'H'),
  ('I', 'I', 'I', 'I', 'I', 'I'),
  ('J', 'J', 'J', 'J', 'J', 'J'),
  ('K', 'K', 'K', 'K', 'K', 'K'),
  ('L', 'L', 'L', 'L', 'L', 'L'),
  ('M', 'M', 'M', 'M', 'M', 'M'),
  ('N', 'N', 'N', 'N', 'N', 'N'),
  ('O', 'O', 'O', 'O', 'O', 'O'),
  ('P', 'P', 'P', 'P', 'P', 'P'),
  ('Q', 'Q', 'Q', 'Q', 'Q', 'Q'),
  ('R', 'R', 'R', 'R', 'R', 'R'),
  ('S', 'S', 'S', 'S', 'S', 'S'),
  ('T', 'T', 'T', 'T', 'T', 'T'),
  ('U', 'U', 'U', 'U', 'U', 'U'),
  ('V', 'V', 'V', 'V', 'V', 'V'),
  ('W', 'W', 'W', 'W', 'W', 'W')

GO 10000

That's looking good.
Hold The Horses!
Before we go along and select the data, let's have a look at the statistics for the index that was created for column B.
Statistics for INDEX 'NIX_Q275204_B_hot2use_20200910'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NIX_Q275204_B_hot2use_20200910  Sep 10 2020  8:59AM             10000                           10000                           1                               0                               4                               YES                                                             10000

All Density                     Average Length                  Columns
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1                               2                               B
1                               4                               B, A

Histogram Steps
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
X                               0                               10000                           0                               1

No change!
Statistics aren't updated automatically, because it can be an expensive operation. The database engine will update the statistics when required or when manually triggered by a maintenance job.
Query Table For 4th Time
We'll run the query for the 4th time and see what happens.

And Paste The Plan
Findings
The index on column B still isn't being used, but the missing index is now shouting that the benefits would be immense:

Missing Index (Impact 98.0765): CREATE NONCLUSTERED INDEX ....

Let's take a short break and go back to your question.

Your Question / My (Current) Answer

If I wanted to run run the following query below, is Index 2 needed or could it use Index 1 and not impact on performance?

Up to know you could drop both indexes and you wouldn't notice much difference. But as the density in the index/statistic sinks, the selectivity increases up to a point where your index might be required.
It depends....

Add More Data And Query Table For 5th Time
We'll add another 230'000 records containing all the other letters, except X, Y and Z and see how the query responds.
Lo and behold: no change.

The Missing Index is still shouting and we are still triggering the use of the Clustered Index.
Add More Data And Query Table For 5th Time
Let's add a 0 to the GO statement of our INSERT statement and add 2.3 Mio records to the table and then select away....
2.3 Mio records added and the query is still using the Clustered Index. The Missing Index impact has increased again, but we still aren't using any of the non-clustered indexes.
Add More Data And Query Table For 6th Time
Let's add a further 0 to the INSERT and add 23 Mio records to our already existing 3.04 Mio records. Then we'll SELECT again and see if the query optimizer starts using the NIX_Q275204_B_hot2use_20200910 index on column B.
Sorry, that took a while [18:00 Min] to insert the 23 Mio records

And the Paste The Plan to examine online.
Musings / Findings
Well what a surprise. Your query has used the index that is covering the B and C columns of your table. Nearly like what the MISSING INDEX  was shouting about.

Your Question / My (Current) Answer

If I wanted to run run the following query below, is Index 2 needed or could it use Index 1 and not impact on performance?

Well, with enough data, your index 1 (covering columns B and C) will eventually be used. Index 2 is not (yet) required.
But It depends...
Parameterized Queries
If you change the query to use parameters, then the index on columns B and C is no longer used.
DECLARE @x AS NVARCHAR(1)
SET @x = 'X'
SELECT A, B, D, E FROM dbo.Q275204 WHERE B = @x

The query engine goes back to scanning the Clusterd Index.
Missing Index
If you create the missing index as suggested earlier on, then the query optimizer uses that index.
CREATE NONCLUSTERED INDEX NIX_Q275204_B_INCL_ACDE_hot2use_20200910 ON [dbo].[Q275204] ([B]) INCLUDE ([A],[C],[D],[E])

Run the query and:

See also Paste The Plan

Conclusion
Optimizing Indexes is a very time-consuming activity. Determining whether an index should be used/dropped/created equally so.
You have to observe your environment, your application and your database, to determine if and when an index could be used. Don't create every index that is suggested, but chose wisely. Sometimes they can help, other times they are just additional data in a database.


XML database for linux I want to be able to store multiple xml objects in a database.  But I don't want to just serialize the object and dump it out.  Rather I would like to be able to access the database representation of the xml object in the same way as I would the xml itself.

Multiple modules need to access the same data but it would be efficient if they could be told which node to start reading from.

In other words traverse the database as if it were actually a binary tree.  CouchDB says it uses a binary tree but at first glance I see no way to store and retrieve data in a tree-like fashion.  Is this the domain of graph style databases?

I don't know much about graph database system and their capabilities but several traditional (SQL) database systems have options to store and manipulate XML documents:

PostgreSQL: there is a XML data type and quite a lot of XML functions since version 8.3.
SQL Server: has support for XML Data since version 2012 (and as announced this year, the DBMS will be soon available in Linux and not only Windows).
Oracle: XML DB Developer's Guide, I think since version 9.
DB2: offical docs: pureXML overview -- DB2 as an XML database and a nice article that explains its XML capabilities: DB2 Native XML.


Two Databases on same server handle date differently I have two databases on the same server, one is a backup of the other from a few weeks ago.

I have a third party application that has started misbehaving.  It logs the LastLoginDate of a particular user when they log on using the following command.

Update DBO.theTable Set LastLoginDateTime = '2015/10/27 15:14:34'
Where UserName = 'theUser'

Now in the live database this works fine, yet when I run the same command on the Backup database I get the following error

  The conversion of a varchar data type to a datetime data type resulted
  in an out-of-range value.

This looks like a simple date format error, but I can't find out how to rectify it.  From what I understand the language of the database is set at the server level, so this should be the same.  Also, the same user at the server level is being used, so again, the Language of that user should not come into play.

As mentioned above, it's a third-party app so I can;t change the underlying SQL to be a different (more universal) date format, so I'll have to handle it at the database level.

Does anyone have any ideas on what I should be checking beyond the above to fix this?

UPDATE

I have worked out a fix to this problem but would still appreciate advice on why the databases are behaving inconsistently.  The two databases share a User 'AdminUser'.  There are also user accounts set at the server level such as 'AppUser'.  'AppUser' has experienced no problems while I have been investigating this issue.  They are set to use British English language

Yet when they log on to the database the database context switches to US English (as captured using SQL Server Profiler)

As a result of this I have switch the AdminUser to have their default Language set to 'English', and now it works as expected

So when I execute the query posted at the top I get the result

  (1 row(s) affected)

While the application is now working as expected, it surely can;t be right to have some users set to 'British English', and some set to (US) 'English' to achieve the same behaviour!  Any ideas are welcomed.

If I understand the problem correctly, when both users have British as default language, one user's request goes through fine because the language setting changes automatically to us_english.  But for another user, the language setting doesn't change automatically, hence the error.

The BOL states following regarding default language and how it can be overridden.

  The default language for a login can be overridden by using CREATE
  LOGIN or ALTER LOGIN. The default language for a session is the
  language for that session's login, unless overridden on a per-session
  basis by using the Open Database Connectivity (ODBC) or OLE DB APIs.
  Note that you can only set the default language option to a language
  ID defined in sys.syslanguages (0-32). When you are using contained
  databases, a default language can be set for a database by using
  CREATE DATABASE or ALTER DATABASE, and for contained database users by
  using CREATE USER or ALTER USER. Setting default languages in a
  contained database accepts langid value, the language name, or a
  language alias as listed in sys.syslanguages.

Based on this the following can affect the language setting.

Database default
User default
DSN setting, connection strings, etc..
SET LANGUAGE in session

If underlying statements can't be changed, is there a setting that can affect it in the third party app?  Or is there are trigger for a certain user login?  Though I believe it's one or a combination of 2 or more in the above list....


Data warehouse server. How do you calculate RAM/CPU specifications? I am trying to write a spec for a data warehouse server for our planned data warehouse upgrade.

As we run virtual servers on VMWare hosts we have the ability to add or remove resources as necessary. In the past we've incrementally added RAM and CPU as required. As our demands have increased we've lobbied for more resources. (primarily disk & RAM).

We ask for more. They give us as little as possible.

However recently whenever we talk about resources we are now criticized for not spec'ing the machine right in the first place, and I am now being told the dev hosts are maxed out, there is no more RAM available.

We're a small Local Government organisation with ~50 regular users of the DW. In normal daily use it runs fine. We get good mdx query performance, and our reports and dashboards are fast. Users are happy.

However our ETL processes run throughout the night, and we're starting to see evidence of memory pressure when processing datamarts simultaneously. Last night SSIS failed with warnings about an "out of memory error".

Our existing DW server is Win 2008 R2 with 4 CPU's and 16Gb of RAM running SQL 2012 Std. I have max server memory set to 12GB, leaving 4GB for OS and services etc. Our existing DW has 3 datamarts/OLAP cubes, and we are developing 2 more.

+----------+----------+---------------+-----------+---------------+
| Datamart | Files GB |  Fact (Rows)  | Fact (Mb) | ETL & Process |
| OLAP cube|          |               |           | Time (hours)  |
+----------+----------+---------------+-----------+---------------+
| PBI      |       3  |  190,000      |  180      |  0.2          |
| FBI      |      30  |  26,100,000   |  10,000   |  1.5          |
| RBI      |     175  |  62,000,000   |  32,000   |  8.3          |
| ABI*     |     100  |  44,050,000   |  21,000   |  4.0          |
| EBI*     |      11  |  100,000,000  |  6,000    |  2.0          |
+----------+----------+---------------+-----------+---------------+
* Planned/Estimated

Our new server is planned to be Win 2012 running SQL 2016 Enterprise. It will run SQL, SSIS, SSRS & SSAS. Storage isn't an issue, but i'm not sure about RAM & CPU.

According to the Fast Track Data Warehouse Reference Guide for SQL Server 2012,  the minimum I should have is 128Gb for a 2 socket machine... which seems a bit excessive. The Hardware and Software Requirements for Installing SQL Server 2016 recommends a minimum of 4Gb of RAM for SQL 2016.
That's quite a difference!

So.. What is a good starting point? 32Gb? 64Gb? How do I justify my starting position (spec) to IT?

Are there any good guides about how to calculate server resources?

Are there any good rules of thumb?

What are the key ingredient/metrics for RAM sizing in a DW context?

The volume of data?
The number of cubes?
The time it takes to do ETL or process a cube?
Peak processing load overnight or performance as viewed by end users during the day?

Great question, and I did a session about this at TechEd a few years ago called Building the Fastest SQL Servers:

https://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI328

In it, I explain that for data warehouses, you need storage that can provide data fast enough for SQL Server to consume it. Microsoft built a great series of white papers called the Fast Track Data Warehouse Reference Architecture that goes into hardware details, but the basic idea is that your storage needs to be able to provide 200-300MB/sec sequential read performance, per CPU core, in order to keep the CPUs busy.

The more of your data that you can cache in memory, the slower storage you can get away with. But you've got less memory than required to cache the fact tables that you're dealing with, so storage speed becomes very important.

Here's your next steps:

Watch that video
Test your storage with CrystalDiskMark (Here's how)
With 4 cores, you'll want at least 800MB/sec of sequential read throughput
If you don't have that, consider adding memory until the pain goes away (and caching the entire database in RAM isn't unthinkable)

Say you've got a 200GB database that you're dealing with, and you can't get enough storage throughput to keep your cores busy. It's not unthinkable to need not just 200GB of RAM, but even more - because after all, SSIS and SSAS really want to do their work in memory, so you have to have the engine's data available, plus work space for SSIS and SSAS.

This is also why people try to separate out SSIS and SSAS onto different VMs - they all need memory simultaneously.


How Database Formats Files for Disk This SO answer outlines how MySQL organizes its database into files, but doesn't describe the structure of the files themselves.

I am wondering, at the physical level, how relational database management systems store their records in files. What the structure of the file is roughly, where the columns/values are (if it's just a CSV), and how it is traversed.

I think it is opportune to start pointing out that, in a relational database —as per Dr. E.F. Codd— information is represented by means of one and only one structure, the relation, which is usually portrayed as a table.

The relational model deals exclusively with aspects of the logical level of abstraction, so a table (an abstraction) is part of the logical level of a database (it is susceptible of undergoing logical operations, e.g., projections, joins, restrictions, etc.), so it is completely independent from the physical storage utlized by the database management system (DBMS for brevity) of use.

That being so, since the relational model has to do solely with logical-level aspects, it offers physical data independence, which sets DBMS (e.g., SQL platforms like IBM DB2, Microsoft SQL Server, PostgreSQL, etc. —that although quite powerful, strictly speaking, are semi-relational, i.e., they do not comply fully with the stipulations of the relational framework—) free to (a) store at the physical level of abstraction in any way their developers/vendors deem convenient (b) the data represented via logical-level tables.

It is important to note that the physical-level storage can in turn be divided in distinct sub-levels of abstraction.

Therefore, the physical arrangements depend on the particular DBMS under consideration, hence they can vary from platform to platform.

Examples of physical structures utilized by different SQL platforms are detailed

in the Storage Page Layout content from the PostgreSQL official documentation; and
in the post entitled Inside the Storage Engine: Anatomy of a record, regarding Microsoft SQL Server, by Paul Randal; and
in SQLite's Database File Format page.

In case you are interested, you may find of help this answer to a question about some of the different levels of abstraction of a computerized information system.


Register XML schema using dbms_xmlschema in Oracle The requirement is to register XML schema from XSD files in Oracle 11gR2. I am using the following code:

DECLARE

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://[...]/spin-catalog-to-bpm.xsd',
    SCHEMADOC => bfilename ('/home/joe','spin-catalog-to-bpm.xsd'),
    CSID => nls_charset_id('AL32UTF8'));
END;
/

The file spin-catalog-to-bpm.xsd is under /home/joe but when I try to register it, it gives me this error:

DECLARE
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 805
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 131
ORA-06512: at line 3

You firstly need to create and Oracle directory.

As a user with the relevant permissions:

CREATE OR REPLACE DIRECTORY HOMEJOE AS '/home/joe';

Grant permission to the user that needs to use DBMS_XMLSCHEMA.registerSchema:

GRANT READ, WRITE ON DIRECTORY HOMEJOE TO YOURUSER;

Then register the schema, using the newly created directory:

DECLARE
BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://[...]/spin-catalog-to-bpm.xsd',
    SCHEMADOC => bfilename ('HOMEJOE','spin-catalog-to-bpm.xsd'),
    CSID => nls_charset_id('AL32UTF8'));
END;
/


Unable to Start PostgreSQL. kernel's SHMMAX parameter exceeded you limit When I am trying to start my PostgreSQL I get the following error in the Postgresql.log file:

FATAL:  could not create shared memory segment: Invalid argument
DETAIL:  Failed system call was shmget(key=5432001, size=41279488, 03600).
HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.  You can either reduce the request size or reconfigure the kernel with larger SHMMAX.  To reduce the request size (currently 41279488 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
        If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for.
        The PostgreSQL documentation contains more information about shared memory configuration.

So in the etc/sysctl.conf file I have changed the values to

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 52033798400
# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 5203379

I restarted the server still I was getting the same error.
So I tried this command to reload

sysctl -p

Then I came up with this error in console

    error: "Read-only file system" setting key "net.ipv4.ip_forward"
error: "Read-only file system" setting key "net.ipv4.conf.default.rp_filter"
error: "Read-only file system" setting key "net.ipv4.conf.default.accept_source_route"
     error: "Read-only file system" setting key "kernel.sysrq"
error: "Read-only file system" setting key "kernel.core_uses_pid"
error: "Read-only file system" setting key "net.ipv4.tcp_syncookies"
error: "Read-only file system" setting key "net.bridge.bridge-nf-call-ip6tables"
error: "Read-only file system" setting key "net.bridge.bridge-nf-call-iptables"
error: "Read-only file system" setting key "net.bridge.bridge-nf-call-arptables"
error: "Read-only file system" setting key "kernel.msgmnb"
error: "Read-only file system" setting key "kernel.msgmax"
error: "Read-only file system" setting key "kernel.shmmax"
error: "Read-only file system" setting key "kernel.shmall"

HOw should I over come this error. What to do to start the Postgres.

This page in the manual has detailed instructions how to deal with SHMMAX and SHMALL. It depends on your OS.

For Linux (example from the manual, to allow 16 GB):

$ sysctl -w kernel.shmmax=17179869184
$ sysctl -w kernel.shmall=4194304

Your number is very high: 52033798400 translates to 48 GB. Do you have that much RAM available?

Also, more quotes:

  Note that SHMALL is measured in pages rather than bytes on many systems.

And:

  A page is almost always 4096 bytes except in unusual kernel configurations with "huge pages" (use getconf PAGE_SIZE to verify).

Bold emphasis mine. You applied a factor 10000 instead. The matching setting for SHMALL would be 12703564 instead of 5203379.

In addition edit the settings in /etc/sysctl.conf to persist the settings for a server restart.
Note that changing this file alone does not affect the running system.


Connection to a MySQL server fails after server restart Until this morning I could connect via Sequel Pro to one of our networked machines that has the MySQL server on it.

Today, however, it's another story: when I am trying to connect, it times out. Even if I start a simple PHP PDO connection and run it from my machine it returns "504 gateway time out".

It's been set up for years and the details are simple:

HOST: 192.168.1.4 --
DB: ??? --
PW: ???? --
PORT: 3306 --

etc. I have not changed or edited any users.

Now the only thing that happened overnight was we had a power cut and this machine was restarted, so I am not sure if this could have caused the issue, because I have restarted the machine many times and it also has done so automatically with updates etc.

And after trying what I could find I am stuck, at what I can do next.

Your advise would be appreciated. Thanks

Local Server Firewall settings.

in my case the local server was using McAfee, so enabling the port 3306 access to all pc's fixed the issue.


How to schedule PostgreSQL replication? I was reading postgresql replications solution but, even I just starting understaning how it works, another doubt has arrisen.
I'll be using postgres internal replication solution but as far as I understand, every event will be replicated just as it is achieved; for example, if I insert some data into the master, automatically it will be replicated to the slave...Am I right?

Well, is that so, I was searching for way of scheduling this replications as a passive backup but the thing is, I need for example, every monday on the night the database gets replicated with all the data it had inserted in it that day to a slave database in Amazon EC2 virtual servers. I was thinking on using cron as a first approach but reading postgres documentation I got aware that practically only configuring the necessary files, postgres do it for you...

So, how could I schedule replication in just desired moments?

PD.-The schedule may vary so it needs to be completely generic, also...I'm using Ubuntu 12.04 and PostgreSQL 9.1 as in the master as in the slave.
Thanks

You can use pg_basebackup to take a copy of the PostgreSQL database over the replication protocol. It copies the whole DB though, it isn't incremental. You'll want to use the --xlog-method=stream argument unless you have a shared WAL archive set up.

Streaming replication isn't really well suited to the purpose you describe. You can set it to have replication lag, but it's more minutes or hours rather than days, as the amount of WAL you have to retain on the master or in an archive to get days of replication lag is huge. It's also fixed, not schedulable.

You could achieve what you describe with lots of WAL archiving plus a custom-created recovery.conf for each backup, but it'll be fiddly at best.

The thing you need to realise is that by (say) Friday, the master no longer has any record of the state it was in on Monday. It can't update last Friday's snapshot to this Friday because it no longer knows what state it was in on intermediate days like last Monday ... or last Friday. It doesn't know what state the old replica is in, nor how to get it from there to where it is now.

You can tell it to remember that much state by archiving WAL segments, but you need an immense amount of disk space for that and it's very inefficient to catch the backup up from WAL if your DB is busy.

I'd recommend just using pg_basebackup and/or pg_dump on a schedule.


What is the best smallest data type size for 19 digit number? I'm using this code to get a mix of Date and Time to use it like a unique ID

DateTime.Now.ToString("yyyyMMddHHmmssfffff")

In SQL Server 2017 I'm confused as to which best smallest size of data type would be that I can use to store this string.

What is the best smallest data type: bigint, timestamp or varchar(19)?

For sure you don't want to use a varchar: you are trying to store a number, so storing it into a string is a non sense. Plus, regardless of what you are going to do it later, varchar is the field that allows you the less flexibility: really no point in using it.

Your want to avoid timestamp, too, as it is deprecated and anyway it's intended for a different use (it's just a synonym for rowversion)

Your choice is then between bigint and datetime2(5), but given your requested precision of 5 they will both use 8 bytes, so no clear winner here. The only edge that datetime2 may have is that being a datetime it's better handled by datetime functions. Bigint, instead, will allow you to represent negative years (in case you need it).


SQL Server Backup failing Error :3041 I am running SQL Server 2008R2 Express edition. For all databases full backup, I am using expressmaint.exe. In this, I am using the command

-S server name -D ALL -T DB -R
D:\SQL_BACKUP\SQL_EXPRESSMAINT_LOGS\FULL_BACKUP_LOGS -RU DAYS -RV 5 -B
D:\SQL_BACKUP\SQL_FULL_BACKUP -BU DAYS -BV 5.

I have created schedule backup from Task Scheduler. It was running 3 days then after start failing the backup plan for only 1 database.
System database has been successfully running except 1 database.

  Error: BACKUP failed to complete the command BACKUP DATABASE (database name)

From logs I got this error message:

  An exception occurred while executing a Transact-SQL statement or batch.
      Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

From Event Viewer logs I got below error code: 3041

  The backup or restore was aborted.BACKUP failed to complete the command BACKUP DATABASE Database Name. Check the backup application log for detailed messages.

Can you please anyone help me with this.

Try to increase the timeout period by adding -TO <time period in minutes> to your command:

-S server name -D ALL -T DB -R
D:\SQL_BACKUP\SQL_EXPRESSMAINT_LOGS\FULL_BACKUP_LOGS -RU DAYS -RV 5 -B
D:\SQL_BACKUP\SQL_FULL_BACKUP -BU DAYS -BV 5 -TO 25

ExpressMaint issues tab


Import errors importing CSV to SQL Server table I am using SQL Server 2014 Management Studio to import a very large .CSV file into a table.

So far I have been trying to accomplish this by right-clicking on the database and selecting Import Data, then using the wizard to import from a flat file.

The problem I am having is that every time I try to import I either run into truncation errors (some of my fields exceed nvarchar(max)) or that there are missing fields on some rows.

How can I set the import to just ignore a failed row, instead of failing the entire operation. I've looked everywhere in the options but cannot find where to set this.

At the end of the import/export wizard you have the option to save your SSIS package (yes that's what the wizard uses behind the scenes).  Save your package to a file then open it in SQL Server Data Tools.  Go into the data flow and open the source.  In the "Error Output" tab you will see an option to ignore failure (for truncation specifically or errors in general).  Modify it for the columns you are worried about.

All that being said you are probably(99.999%) not seeing a truncation because the column is to big to fit in a varchar(max).  What is probably happening is that SSIS "thinks" your column is shorter than it is.  If you go into the connection manager for your CSV file you can modify the "width" of your input columns in the advanced tab.


Two-way encryption in PostgreSQL version 9.1 database What is the process of two-way encryption of data in PostgreSQL database version 9.1 using built-in functions?

PostgreSQL's built-in two-way encryption functions are documented here. The functions that come in the encrypt/decrypt pairs are the two-way encryption options.


Unable to view partition table in a schema I am using oracle 11g enterprise edition,

I have created a schema as "TESTSCHEMA"  and in it i have created a partition table as "sales_test".

The query for the table,

create table sales_test (year number(4),
                    product varchar2(10),
                   amt number(10,2))
     partition by range (year)
     (partition p1 values less than (1992),
     partition p2 values less than (1993),
     partition p3 values less than (1994),
     partition p4 values less than (1995),
   partition p5 values less than (MAXVALUE));

The problem is,
After creating the table it does not display the table from dbvisualiser, but it can display unpartitioned tables.

I have created one user of my own as MDR_139 and it has ALL PRIVILEGES like what DBA has.

If you created the table with the above statement as user MDR_139, then that table belongs to the MDR_139 schema, not TESTSCHEMA.

If you want to create tables in other schemas, add the schema specifier to the create statement, for example:

create table TESTSCHEMA.sales_test (year number(4),
                    product varchar2(10),
                   amt number(10,2))
     partition by range (year)
     (partition p1 values less than (1992),
     partition p2 values less than (1993),
     partition p3 values less than (1994),
     partition p4 values less than (1995),
   partition p5 values less than (MAXVALUE));


Compare multiple field to single IN query I have a situation where I need to compare 3 different fields of a same table to a single IN query, eg:

SELECT unit
FROM trips
WHERE owner1,owner2,owner3 in (select id from login)

Can this be done in PostgreSQL?

Using version 9.3.4

You haven't supplied a clear definition of the problem, so I have to guess you probably mean "if any of these field values are in this list" or "if all of these field values are in this list".

Either way, you'll want to use logical array operators. The "any" case is array-overlaps, &&:

ARRAY[owner1,owner2,owner3] && (select array_agg(id) from login)

and the "all" case is array-contains, <@:

ARRAY[owner1,owner2,owner3] <@ (select array_agg(id) from login)


Listener IP not changing after failover We have a multi-subnet AlwaysOn availability group. There are 2 subnets and those are listed on the cluster and also the listener.  Whenever I fail over and ping by the listener name it still shows the old IP address before failover. Any expert opinion to get over this will be greatly appreciated.
I have set the RegisterAllProvidersIP -Value 1.

After failover the client does not connect as the listener is picking up the old IP address.  After a couple of minutes it picks up the correct one and the client connects.

This behavior occurs due to the time to live (TTL) on the DNS records.  A client does not do a DNS lookup every time it attempts to connect to a server.  It first looks in its DNS cache to see if there is a cached entry.  You can see what is in the cache by running ipconfig /displaydns.  Once you run this you'll see the "Time To Live" property on each record.  This indicates how many more seconds the record will stay cached.

So your client will not connect to the new IP address until the old record is timed out of the cache.  If you dig into the Microsoft documentation, they recommend setting the time to live on listener DNS records to 5 minutes to minimize this delay.

Additionally, if the SQL client in use supports "multi-subnet failover", you need to enable that option.  When this option is enabled, the client will try to connect to all IP addresses that are registered for the listener name.  So for this to work nicely, you will have both IP addresses registered for the listener hostname at all times.

See SQL Server Multi-Subnet Clustering (SQL Server)


A query for a latest version of data MySQL.

Let there is a table with fields startdate and kind.

Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is UNIQUE would be also interesting.

Let a date d is specified. I want to retrieve the data of given kind which corresponds to the latest startdate which is before d.

As far as I understand this cannot be efficiently done with views.

So, should it be done with a stored procedure? I am a novice in the topic of stored procedures. Your code would be welcome.

This is easy.

let @d be the date and @k be the kind :

select *
from table a
where
  startdate = (select max(b.startdate)
               from table b
               where b.startdate < @d
               and b.kind = @k)
  and a.kind = @k


CROSS APPLY on Scalar function I have this:

SELECT
A
,B
,dbo.Func(C)
,dbo.Func(D)
,dbo.Func(E)
,F
FROM abcdef
WHERE
0 = dbo.Func(C) + dbo.Func(D)

I have read that this is not good practice because the function is called million of times and it has a bad impact on performance.

I tried to rewrite it with CROSS APPLY:

SELECT *
 FROM abcdef
  CROSS APPLY dbo.Func(D) as FD

but it returns this error:

Invalid object name 'dbo.Func'

Can I use CROSS APPLY only on TABLE VALUED functions ?

And is CROSS APPLY suitable for scalar functions (converted to table valued function) anyway ? Because on my small performance test CROSS APPLY is a bit slower.

I read that this is not good practice because function is called 'zilion' times and it have bad impact on performance.

While CROSS APPLY can be useful in some cases, I don't expect any difference in performance between calling the function in WHERE or CROSS APPLY in the specific case. If the table has a million rows (and columns C and D possibly a million different values), a million times the function will be called. How can it be otherwise?

  I tried to rewrite it with CROSS APPLY.

Here's how:

SELECT
    t.A,
    t.B,
    ca.Fc,
    ca.Fd,
    dbo.Func(t.E) AS Fe
    t.F,
FROM abcdef AS t
  CROSS APPLY
    ( SELECT
          dbo.Func(t.C) AS Fc,
          dbo.Func(t.D) AS Fd
    ) AS ca
WHERE 0 = ca.Fc + ca.Fd ;

or:

SELECT
...
FROM abcdef AS t
  CROSS APPLY
    ( SELECT
          dbo.Func(t.C) AS Fc,
          dbo.Func(t.D) AS Fd
      FROM (SELECT NULL) AS dummy
      WHERE 0 = dbo.Func(t.C) + dbo.Func(t.d)
    ) AS ca ;

Again, I don't think this will have any effects on efficiency.


Doing a insert across two databases with same collations and getting an error SQL query :

insert DB1.dbo.tbl1

select distinct Number, Name, 'Unknown', 'Unknown', 'Unknown', NULL, 'No', 'GL', 'N/A'
from DB2.dbo.tbl1 T2
where T1.Number not in (select distinct AccountNumber from DB1.dbo.tbl1)

Error  message :

  Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Ok, so when you ran

SELECT * FROM sys.columns WHERE object_id = object_id('tb1')

for each of the tables you found that the collation was not the same as the database default.  At this point you have two options.

Change the collation in the query

All you have to do here is use the COLLATE  phrase after the column that needs it.

select distinct Number, Name, 'Unknown', 'Unknown', 'Unknown', NULL, 'No', 'GL', 'N/A'
from DB2.dbo.tbl1 T2
where T1.Number not in (select distinct AccountNumber COLLATE SQL_Latin1_General_CP850_CI_AI from DB1.dbo.tbl1)

The above would force the AccountNumber column to use SQL_Latin1_General_CP850_CI_AI in this particular spot.

Change the collation of the columns

This could have ramifications elsewhere so you want to be careful before doing it but to change collation of a column it's usually just a simple ALTER TABLE ALTER COLUMN statement.

ALTER TABLE tbl1 ALTER COLUMN AccountNumber char(10)
COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL


Issues converting MyISAM table to InnoDB (auto column issue) I'm having issues trying to convert a table from MyISAM to InnoDB in MySQL 5.6.

The following is the table dump:

--
-- Table structure for table `companies`
--

DROP TABLE IF EXISTS `companies`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `companies` (
  `uid` int(20) NOT NULL,
  `cid` int(20) NOT NULL AUTO_INCREMENT,
  `cname` varchar(500) NOT NULL,
  `rfc` varchar(20) NOT NULL,
  `address` varchar(1000) NOT NULL,
  `dbUseExternal` tinyint(1) NOT NULL DEFAULT '0',
  `dbHost` varchar(50) NOT NULL,
  `dbPort` varchar(50) NOT NULL,
  `dbUser` varchar(50) NOT NULL,
  `dbPass` varchar(50) NOT NULL,
  `dbSSL` varchar(50) NOT NULL,
  `dbDriver` varchar(50) NOT NULL,
  `dbName` varchar(50) NOT NULL,
  `status` int(10) NOT NULL,
  PRIMARY KEY (`uid`,`cid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

It works as MyISAM. But, if I try to convert it to InnoDB (or if I try editing this dump to insert it on the command line as a sql file), I get the following error:

Incorrect table definition; there can be only one auto column and it must be defined as a key

I understand that error - or at least I thought I did. I'm not really using more than one AUTO_INCREMENT column and it is defined as primary key.

Also, the information I've found regarding the error is always because of an obvious missing key or a duplicate AUTO_INCREMENT definition. One more thing I see generally commented is that the same is true for MyISAM and InnoDB.

So, why does it work for MyISAM and not for InnoDB?

Thanks in advance for any comments.

Francisco

At present, only MyISAM permits auto_increment values in a composite key. I wrote about this over a year ago : How can you have two auto-incremental columns in one table?

According to the Book

Section 5.7 Page 89 Paragraph 1 states the following:

  The MyISAM Storage Engine supports composite indexes that include an AUTO_INCREMENT column. This allows the creation of independent sequences.

which I know you are well aware of. I simply stated this for the benefit of other readers.

As I stated in my past post

  This is not possible with InnoDB based on auto_increment columns being tied directly to the gen_clust_index (aka Clustered Index) !!!

Allowing this would require redesigning the gen_clust_index to accommodate such a mechanism.


MySQL constraint to prevent combinations of columns I have a table that represents files in directories.
dir   | file
----------------
dir_A | file_0
dir_A | file_1
dir_A | file_1
dir_B | file_2
dir_C | file_3
...   | ...

I want to put a constraint on that table to make sure a file can never be in 2 directories. But the same file can be in the same directory multiple times.
Example of permitted condition:
dir_A | file_1
dir_A | file_1
dir_B | file_2

Example of failed condition:
dir_A | file_1
dir_B | file_1
-- should be prevented, because `dir_A file_1` exists
-- same file, but different directory

The question has been asked for PostgreSQL with solution:
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE table_name
    ADD CONSTRAINT only_one_dir_per_file
        EXCLUDE USING gist ( file WITH =, dir WITH <> );

I was wondering if the same could be done with MySQL?

you can use Triggers for that.

CREATE TABLE files_dir (
  `dir` VARCHAR(5),
  `file` VARCHAR(6)
);

INSERT INTO files_dir
  (`dir`, `file`)
VALUES
  ('dir_A', 'file_0'),
  ('dir_A', 'file_1'),
  ('dir_A', 'file_1'),
  ('dir_B', 'file_2'),
  ('dir_C', 'file_3');

CREATE TRIGGER before_file_insert
    BEFORE INSERT ON files_dir
    FOR EACH ROW
 BEGIN
 IF EXISTS(SELECT 1 FROM files_dir WHERE `file`= NEW.`file` AND `dir` = NEW.`dir`) then
  BEGIN
  END;
 ELSE
    IF EXISTS(SELECT 1 FROM files_dir WHERE `file`= NEW.`file`) THEN
          SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = "file has another directory";
    END IF;
   END IF;
 END;

 INSERT INTO files_dir
  (`dir`, `file`)
VALUES
('dir_A', 'file_1');

✓

 INSERT INTO files_dir
  (`dir`, `file`)
VALUES
('dir_B', 'file_1');

file has another directory

SELECT * FROM files_dir;

dir   | file
:---- | :-----
dir_A | file_0
dir_A | file_1
dir_A | file_1
dir_B | file_2
dir_C | file_3
dir_A | file_1

db<>fiddle here
For an BEFORE UPDATE TRIGGER you can do something eual.
Of course you can always change your insert statement with a join like ypercubeᵀᴹ already said.


Shorthand for on primary key? Is there a shorthand for merging rows based on the primary key? For example:

MERGE INTO [MyDatabase].[MySchema].[MyTable] AS target
USING [MyLinkedServer].[MyDatabase].[MySchema].[MyTable] AS source
   -- The following line is pseudocode illustrating what we want to accomplish
   ON (target primary key equals source primary key)
WHEN MATCHED THEN
    UPDATE SET target.MyColumn = source.MyColumn
WHEN NOT MATCHED BY TARGET THEN
    INSERT (MyCol1, MyCol2, MyCol3)
    VALUES (source.MyCol1, source.MyCol2, source.MyCol3);

Writing out the conditions is tedious if the primary key contains a large number of columns. Also, if the primary key changes, many joins and merges might suddenly break.

You might ask, "Why is the primary key changing?" Real scenario: We were told that an object was uniquely identified by its distinguished name. Later on, we found out that a distinguished name could change if the object moved to another subnet. New requirements rolled in that changed how we uniquely identify the objects. This broke the product in quite a few places. Some of the mess could have been avoided if we had merged on primary key instead of on specific columns.

No there isn't anything similar to what you are asking built into the product.

Standard SQL has the concept of a NATURAL JOIN that joins on common column names but SQL Server does not implement this and there would be no guarantee that any such names would correspond with the PK anyway.

It would be possible in theory for you to write some sort of script pre-processor or dynamic SQL code generator that did this if it was a massively important need for you.


Can I be notified of a rollback in my DDL Trigger? I'd like DDL changes in SQL Server to trigger an external process (outside of SQL). The problem is, is there a way for me to be notified via my trigger that a rollback occurred?

For instance, with the following:

BEGIN TRAN

CREATE TABLE [dbo].[TestRollbackTable](
    col1 varchar(100) NOT NULL)

ROLLBACK

A DDL trigger on CREATE_TABLE would be called, but nothing seems to be called with the ROLLBACK. And since my trigger is calling an external process, it is not automatically subjected to the rollback. How can I catch the fact that the ROLLBACK negated that action?

Don't use DDL triggers. Use Event Notifications instead. Event Notifications convey the very same data as DDL triggers and occur on the very same events, but they are asynchronous and loosely coupled. You get notified only after the original DDL committed. If the DDL rolls back, you don't get any notification. It seems to be exactly what you need.


Better way for multiple joins on same table but different id ranges In our application we have a slow performing reporting query that needs to be optimized.
The performance loss is mainly because there are up to 11 left joins on the same table and we can't put in foreign keys. There already are indexes on the typeid as they are foreign keys to a Type table. The situation can be reduced and abstracted to the following:

USE tempdb;

IF OBJECT_ID('person') IS NOT NULL
  DROP TABLE person;
IF OBJECT_ID('AdditionalColumnValue') IS NOT NULL
  DROP TABLE AdditionalColumnValue;
IF OBJECT_ID('AdditionalColumn') IS NOT NULL
  DROP TABLE AdditionalColumn;

CREATE TABLE person (
  Id INT PRIMARY KEY
);
CREATE TABLE AdditionalColumn (
  Id   INT PRIMARY KEY,
  Name VARCHAR(100)
);
CREATE TABLE AdditionalColumnValue (
  Id                 INT PRIMARY KEY,
  EntityId           INT,
  AdditionalColumnId INT FOREIGN KEY REFERENCES AdditionalColumn (Id),
  [value]            NVARCHAR(4000)
);

INSERT INTO person VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
INSERT INTO AdditionalColumn
VALUES
  (1, 'EatsStoneFruit'),
  (2, 'EatsBerry'),
  (3, 'EatsCitrus'),
  (4, 'FavoriteAnimal');
INSERT INTO AdditionalColumnValue
VALUES
  (1, 1, 'mango'),
  (1, 1, 'lychee'),
  (1, 2, 'blueBerry'),
  (1, 2, 'raspberry'),
  (1, 3, 'lemon'),
  (1, 3, 'orange'),
  (1, 3, 'blood orange'),
  (1, 3, 'grapefruit'),
  (1, 4, 'earthworm'),
  (1, 4, 'raven'),

  (2, 1, 'mango'),
  (2, 2, 'raspberry'),
  (2, 3, 'lemon'),
  (2, 3, 'grapefruit'),
  (2, 4, 'tardigrade');

-- Query
SELECT person.Id, fruits.value fruits, animals.value animals
FROM person
LEFT JOIN AdditionalColumnValue fruits on fruits.EntityId = person.Id and fruits.AdditionalColumnId in (1,2,3)
LEFT JOIN AdditionalColumnValue animals on animals.EntityId = person.Id and animals.AdditionalColumnId in (4)
-- up to 9 more of these

I have the idea I should be able to optimize by somehow partitioning on the typeId's and then only do the costly join once.
But I can't figure out how.

As I expect people to wonder why I'm not solving this problem on a DML level:
This table allows our consultants to extend any table with non-standard fields on request.
As they can be linked to any table the link between the tables is on convention, not on foreign key.
It probably was a good solution when it was used sparingly, but things evolved.  The business value of this system's flexibility is considerable and it will be a hard sell to change anything about it. If I can't get this to work reasonably fast setting up a batch job to to denormalize the data would be our plan B.

UPDATE
in my effort to boil the problem down to it's core I made some mistakes. Updated the query and added buildup sql.

The LEFT joins are actually INNER joins, as all the tables in the right sides have columns that appear in WHERE clause. So you could change them - just to be precise, there is no efficiency gain.

For the performance problems, my suggestion would be to add various composite indexes. EAV designs need queries with multiple joins and without appropriate indexes, they are often inefficient.

For the specific query, you could try indexes either on (Entity, TypeID, EntityID) INCLUDE (Value) or on (Entity, EntityID, TypeID) INCLUDE (Value). Then check the execution plans to see which of the two is used.

If the conditions TypeId IN (1,2,3), etc are fairly common and stable, you could use even more targeted indexes, like this:

 (Entity, EntityID) INCLUDE (Value) WHERE TypeID IN (1,2,3)


MySQLDump wrong dump i'm trying to generate a dump from my database but when i try to load the dump file it gives me an error. What should i do ?
My MySQL ( loader) version is mysql "Ver 14.14 Distrib 5.1.57, for apple-darwin10.3.0 (i386) using readline 5.1" my MySQL(dumper) version is "mysqldump  Ver 10.11 Distrib 5.0.51a, for debian-linux-gnu (x86_64)".

The error is:
ERROR 1166 (42000) at line 1572: Incorrect column name 'DATEDIFF(current_date,DATE_ADD(created_at,INTERVAL (updated_at*30) DAY))'

head  file:

$ head dump.sql

> -- MySQL dump 10.13  Distrib 5.1.34, for apple-darwin9.5.0 (i386)
> --
> -- Host: localhost    Database: moov3_production
> -- ------------------------------------------------------
> -- Server version 5.0.51a-24+lenny2

Just reading the header you put in the question shows something interesting. In fact, the question shows three things:

MySQL dump 10.13  Distrib 5.1.34, for apple-darwin9.5.0 (i386) indicates you used mysqldump from apple-darwin9.5.0 (i386) binaries
Server version 5.0.51a-24+lenny2 shows the version of mysql you used mysqldump to dump from.
You wanted to load the mysqldump file into Ver 14.14 Distrib 5.1.57, for apple-darwin10.3.0 (i386) using readline 5.1

What a jumble of versions to do this with.

If you want to see if mysqldump has an issue with the line that has DATEDIFF, try dumping just the schema.

mysqldump --no-data --all-databases ... > MySQLSchema.sql

This will display ony the schema. No INSERTs will be in the output. You can then hunt down that lines. You may also want to dump the data onyl without the schema,

mysqldump --no-create-info --all-databases ... > MySQLData.sql

Splitting the dumps allows you to load the schema into an editor and see if there are any problems. If you do not see any problems, load the MySQLSchema.sql into the target server. If the error is reproduced, you can fix the schema file and reload. Once the schema is loaded, you can separate load MySQLData.sql

BTW you should use mysqldump binary whose version is 5.0.51a-24+lenny2. Use dumps from version as mysqld is usually better to port and may minimize problems like this.

Give it a Try !!!


How to restart MariaDB Galera cluster to change my.cnf I have 3 dbs servers with mariadb galera cluster.

I need few my.cnf change but each time I try to just restart with mysql restart command , replication gets affected.

So can anybody let me know proper way to restart mysql without downtime.

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="web_cluster"
wsrep_cluster_address="gcomm://172.16.1.173,172.17.1.173,172.18.1.173"

# Galera Synchronization Configuration
wsrep_sst_method=rsync
#wsrep_sst_auth=cluster-user:clusterpass
#wsrep_sst_method=mariabackup

# Galera Node Configuration
wsrep_node_address="172.16.1.173"
wsrep_node_name="web-db-01"

rsync is usually not a good SST method as it can block writes on the donor node while performing SST. Use mariabackup instead to avoid that.
Use wsrep_provider_options="gcache.recover=yes; gcache.size = xM;" where x is enough megabytes to hold all write sets for any expected downtime, if you want to be able to use IST and avoid SST after restarts. E.g. the amount of downtime expected for upgrading MariaDB on a single node. 128 is what I use, but it obviously depends on how much writing is usually happening on your cluster. gcache.recover=yes; is needed to try and use gcache-based IST when a node starts up instead of SST. See the MariaDB KB article on wsrep_provider_options for details.


OLTP Indexed Views or SQL Calculation At our financial company, we have different OLTP transactions for customers. We have a transaction table (debits, credits) and balance table.
We will only have 1000 transactions per month.

What is the best way to have account balance table?

1) One person wanted to use an indexed view, since we have relatively static data.

2) I want to calculate account balance right in sql code. I did not want to deal with issue of queries sometimes not using indexed view in execution plans, deadlocks during update, complicated execution plans when updating/delete data etc. Prefer indexed views for reporting/Olap databases.

Is there a correct answer for concurrency transactional databases? Any issues to watch out for using indexed views in small transactional oltp databases?

create table dbo.AccountTransaction
( AccountTransactionId int primary key identity(1,1),
  CustomerId int null,
  TransactionAmount money not null )

create table dbo.AccountBalance
( AccountBalanceId int primary key identity(1,1),
  CustomerId int null,
  BalanceAmount money null )

SQL Calculation Method:

begin transaction
    insert into AccountTransaction
    values(@CustomerId, @TransactionAmount)

    update AccountBalance
    set BalanceAmount = BalanceAmount + TransactionAmount
    where CustomerId = @CustomerId

end transaction

Indexed View Method:

create view dbo.AccountBalanceVw
with schemabinding
as
select
    customerid,
    sum(transactionamount) as BalanceAmount,
    COUNT_BIG(*) AS CountLines
from dbo.AccountTransaction
group by customerid

create unique clustered index cx_AccountBalanceVw on AccountBalanceVw(CustomerID);

This is a very open ended question where lots of factors you havent revealed could change peoples opinions.

That said; I've had to deal with similar scenarios in my career.

To get the current balance you are going to need to sum up all the transactions.  This leads to the view that an indexed view is the right solution when using SQL Server.  It is indeed a good one, however, it is not without its limitations.  You need to consider these factors:

How much impact is a schema change going to cost me if I have an indexed view?
Will I need to drop the index and rebuild it?
How long will that take?
Can I afford the blocking on the base table(s) during that rebuild?
Does my indexed view prevent refactoring the transaction table(s) later in anyway?

These could become considerable blockers to your application development in the future.

Because of these (and I am sure other) factors you might start leaning towards calculating the balance "on the fly".  That is, you essentially have your view but without the index on it.  Or just query the table and sum the transactions.  If you start looking at this approach you have to consider:

Will we have lots of transactions per account?
And if we do, do the balance queries perform?
Will calculating the balance impact other queries (notably adding new transactions) in any way (think blocking here)

You have given the third option of calculating and storing the balance at the time of data updates.  This seems attractive as it avoids all the downsides of the previous two scenarios I've covered.  You have fast look ups and my schema is not hampered or tied to the indexed view.  There are some gotchas here too:

Do I calculate the new balance manually each time I update the table?
Do I use a trigger to maintain the balance (and avoid the previous point)
Do I have to cater for rows also being removed or updated (protip: you will...even if you think you wont)
Can I afford the performance impact of any trigger I create? Do its benefits outweigh this cost?

We havent even thought about transaction isolation levels yet!  Are you going to be using READ COMMITTED SNAPSHOT or SNAPSHOT ISOLATION at all? Because these have an impact you need to consider.

What I would probably do from your information given

You mention you only expect 1000 transactions per month. I'm going to read that as 1000 per customer per month as a worse case scenario.  Thats not really a lot of data, especially early on.  Granted, if you have 1,000,000 customers all doing 1000 transactions you're going to have a lot of data.  I'm assuming a small number of customers too.

I wouldn't bother with an indexed view or a calculated table/column for the balance.  I'd simply query it on the fly to start with.  This way you can enhance and expand your schema and not worry about the impacts the other choices might have had.

Longer term, as the schema settled down and the application matured I would look at those other two options to see which suits the use case best.  I love what indexed views can give you, however, they are not without their downsides and can be quite a pain later on when you want to make changes.

This is approach follows the "premature optimisation" issue one can face in application programming.  You cannot always do this with database design, however, I think here you can as the optimisation you could later is pretty easy.  Especially if you abstract the balance retrival through a view in the beginning.  No matter what approach you take later, you simply update that view to retrive the data from the new object(s).

Slightly Off Topic:

Couple of tips for your schema (Im also aware you may have simplified it for the question)

Your schema shows a list of transactions per customer. This says each customer has one and only one account.  That may be the case today, however, I would thoroughly recommend designing your schema to accommodate many accounts per customer.
I wouldnt recommend using the money data type.  It doesn't have enough precision or scale.  Often when dealing with financial data you might need to go to 6 decimal places depending on the currency.  Or beyond the max size of the money datatype.


How to optimize Merge Anti Join? While trying to find the fastest way to do a bulk update of a large table, I came up with the following plan:

having main_table (potentially above 1000 mln rows) and update table up to a few million rows. Data in update are indexed according to main_table (have corresponding id columns).

copy rows from main_table into temp_table where id not in update id table  (where data has actually changed).
simple append of the update table onto temp_table
drop main_table
rename temp_table to main_table
renumerate main_table pk
create the next update with python and repeat.

The above is to be done with the following sequence of queries:

CREATE TABLE temp_table (like main_table including all);
INSERT INTO  temp_table (listing_id,date,available,price,timestamp)
    (SELECT listing_id,date,available,price,timestamp
         FROM   main_table c
         WHERE  NOT EXISTS (SELECT
                            FROM   update
                            WHERE  id = c.id));
INSERT INTO temp_table (listing_id,date,available,price,parsing_timestamp)
    (SELECT listing_id,date,available,price,timestamp
     FROM   update c);
DROP  TABLE main_table cascade;
ALTER TABLE temp_table RENAME TO main_table;
ALTER TABLE main_table DROP COLUMN id
ALTER TABLE main_table ADD COLUMN id SERIAL PRIMARY KEY;
DROP  TABLE update cascade;

While debugging the queries, I have found the slow queries with explain:

EXPLAIN (ANALYZE, BUFFERS) CREATE TABLE temp_table (like main_table including all);
INSERT INTO  temp_table (listing_id,date,available,price,timestamp)
    (SELECT listing_id,date,available,price,timestamp
         FROM   main_table c
         WHERE  NOT EXISTS (SELECT
                            FROM   update
                            WHERE  id = c.id));

This is the result of EXPLAIN (ANALYZE, BUFFERS) as requested in comments:

QUERY PLAN
Merge Anti Join (cost=513077.42..4789463.48 rows=109800833 width=40) (actual time=1216.018..48520.564 rows=112757269 loops=1)
Merge Cond: (c.id = update.id)
Buffers: shared hit=4 read=1359891 written=2838, temp read=8701 written=15077
-> Index Scan using cals_pkey on cals c (cost=0.57..3958576.01 rows=113119496 width=44) (actual time=0.857..29573.798 rows=113119497 loops=1)
Buffers: shared hit=1 read=1330192 written=2838
 -> Sort (cost=513076.85..521373.51 rows=3318663 width=8) (actual time=1215.147..1260.191 rows=362229 loops=1)
Sort Key: update.id
Sort Method: external merge Disk: 58480kB
Buffers: shared hit=3 read=29699, temp read=8701 written=15077
-> Seq Scan on update (cost=0.00..62881.63 rows=3318663 width=8) (actual time=0.179..423.100 rows=3318663 loops=1)
Buffers: shared read=29695
Planning Time: 0.259 ms
Execution Time: 52757.349 ms

How to optimize the queries?

Concerning the sort performance in the query, there is this:

Sort Method: external merge Disk: 58480kB

Meaning, there is not enough work_mem to sort in RAM. The operation spills to disk, which is typically a lot more expensive. If you have more RAM to spare, increase the setting for the current session (only!).

See:

Improving sort performance in GROUP BY clause

Check with SHOW work_mem;, then add 100 MB to the number you get and use it below instead of '???MB'. If you still see mention of "disk" in the EXPLAIN output, add more.

But there is more: You create the new table with:

CREATE TABLE temp_table (like main_table including all);

INCLUDING ALL, as the wording suggests, also includes constraints and indexes. Obviously, at least a PK constraint as indicated by cals_pkey in the EXPLAIN output. I assume a column:

id bigserial PRIMARY KEY

It's a lot cheaper to create an index on a filled table than to update it incrementally. The index is also typically better optimized. So you should drop the PK (and all other indexes and constraints) before you write to the table, and recreate them afterwards.

Also, dropping and re-creating the id column on a huge table is needlessly expensive. Create new values for id on the fly with row_number() (do you really need to renumber?). Then update the associated SEQUENCE accordingly.

Related:

Copy table with separate sequences for serial columns
How to copy structure and contents of a table, but with separate sequence?

Assuming ...

you need to renumber all rows
no concurrent load on the table
parsing_timestamp is a typo in the question and should really be the same column timestamp. Else, adapt accordingly.

... the operation could look something like this:

BEGIN;

SET LOCAL work_mem = '???MB';  -- see above

CREATE TABLE temp_table (LIKE main_table INCLUDING ALL);

ALTER TABLE temp_table DROP CONSTRAINT temp_table_pkey;  -- before!
-- drop more indexes or constraints?

INSERT INTO temp_table
      (id                  , listing_id,date,available,price,timestamp)
SELECT row_number() OVER (), listing_id,date,available,price,timestamp
FROM  (
   SELECT listing_id,date,available,price,timestamp  -- No parentheses around SELECT
   FROM   main_table c
   WHERE  NOT EXISTS (SELECT FROM update WHERE id = c.id);

   UNION ALL
   SELECT listing_id,date,available,price,timestamp  -- parsing_timestamp ???
   FROM   update
   ) sub

DROP  TABLE main_table CASCADE;
DROP  TABLE update CASCADE;

ALTER TABLE temp_table RENAME TO main_table;
ALTER TABLE main_table ADD CONSTRAINT main_table_pkey PRIMARY KEY (id);  -- after!
-- recreate more indexes or constraints?

SELECT setval(pg_get_serial_sequence('main_table', 'id'), max(id)) FROM main_table;  -- reset sequence

COMMIT;

Also, some more of the advice for "Populating a Database" in the manual may well apply to your case. Maybe more maintenance_work_mem, maybe ANALYZE or VACUUM ANALYZE afterwards, ... Have a look.


Maintenance Job Failing I didn't realize but it appears you can put switches on your job to run a maintenance plan. Am I correct on that?

We have 3 jobs that are running the same maintenance plan but doing different things with it.

The first two jobs succeed, and the 3rd job fails. Here are the jobs .I cannot figure out why that 3rd job fails. I was able to find a table in msdb that shows that plan ID, and all three of these jobs have the same plan id, so I know it uses the same maintenance plan.

    The code for the three of them are:

    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 5E6B7EDB-56E5-4EAD-81E6-BDFD1514F9DD  -RebldIdx 100 '
    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 5E6B7EDB-56E5-4EAD-81E6-BDFD1514F9DD  -VrfyBackup -BkUpMedia DISK -BkUpDB "F:\Backup" -DelBkUps 1DAYS -BkExt "BAK"'
    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 5E6B7EDB-56E5-4EAD-81E6-BDFD1514F9DD  -CkDBRepair  '

The first two pass, and the 3rd one is the one that fails…

One thing to note is that xp_sqlmaint is scheduled to removed in the future and is not the recommended method for running the maintenance plans. (Though it is still supported at this time.)

For SQL Server maintenance I would recommend downloading the Ola Hallengren solution at https://ola.hallengren.com/.  This is a freely provided product for managing the integrity checks as well as, if you wish, to automate your database backups. And Ola is quick to respond with personal help.

These are highly respected scripts, which I have been using for several years.

You seem to have a corruption problem if the -CkDBRepair cannot finish the check. If that is the case, then you will need to be careful in how you fix the problem so as to avoid as much loss as possible. So please carefully read:

DBCC CHECKDB (Transact-SQL)

Be sure to read the warnings on DBCC CHECKDB and the impact of allowing data loss when resolving the problem.


What is the meaning of "available RAM" for calculating MySQL max_connections The question was asked previously, "how do I calculate MySQL max_connections" and this was the answer:

  max_connections = (Available RAM - Global Buffers) / Thread Buffers

Since I don't have enough reputation to add a comment to the existing question I had to ask a brand new question.

I can get the buffer values by executing : SHOW VARIABLES LIKE '%buffer%'; but what is the available RAM part of this equation exactly?

Is this value in bytes, kilobytes, megabytes?  Something else?  Also, do I use the available RAM shown in the resource monitor while my site is being used, or do I need to use the total amount of RAM installed on the machine?

I'd say 'Available RAM' means max memory mysql can claim. And 'Global buffers' would mean buffers like innodb_buffer_pool, key_buffer_size, query_cache_size, etc.


Invalid length parameter passed to the LEFT or SUBSTRING function when inserting data into a table that has computed columns I have a process that needs to split a string of key-value pairs. To do so, I'm using a combination of a temp table that has computed columns to calculate the key and the value from each key-value pair, and a string_split function in the INSERT statement to separate the key-value pairs into rows. This is how the table looks like:

CREATE TABLE #KeyValuePairs (
  [PK]       uniqueidentifier NOT NULL,
  [KeyValue] varchar(500) NOT NULL,
  [Key] AS CONVERT(varchar(35), SUBSTRING([KeyValue],1,CHARINDEX('=',[KeyValue])-1)) PERSISTED NOT NULL,
  [Value] AS SUBSTRING([KeyValue],CHARINDEX('=',[KeyValue]) + 1, LEN([KeyValue]) - CHARINDEX('=',[KeyValue])) PERSISTED NULL
);

The clustered index just helps me to find a specific combination of primary key and value key efficiently. The following is how I insert data into it:

INSERT #KeyValuePairs ([PK], [KeyValue])
SELECT T.[PK], kv.[value]
FROM BaseTable T
CROSS APPLY string_split([KeyValueStringColumn],'*') kv
WHERE [KeyValueStringColumn] != ''
  AND kv.[value] != ''

Occasionally, this query fails with the following error:

  Invalid length parameter passed to the LEFT or SUBSTRING function.

If I remove the computation from the table definition, leaving the fields NULL by the INSERT and instead run an UPDATE afterwards with the exact same formula, the UPDATE succeeds always.

This is a similar issue to the one discussed here, but I think it deserves its own question/solution as in my case the issue was not the use of substring in a query, but in a table that's being inserted to. Though, the reason apparently is the same ... as Aaron Bertrand stated in his answer to another question:

  ... because you can't always rely on SQL Server filtering rows before attempting calculations.

While that's understandable in a case where the filter and the computation happen in the same query, it is not so obvious that the same appears to happen when inserting data into a table that has computed columns. Apparently, the persisted columns are computed before being inserted into the table, therefore (I guess depending on which plan the optimizer chose) also computing values of rows that due to the filter condition are not being inserted into the table. Changing the table definition making sure the length parameter cannot become negative solved the problem.

CREATE TABLE #KeyValuePairs (
  [PK]       uniqueidentifier NOT NULL,
  [KeyValue] varchar(500) NOT NULL,
  [Key] AS CONVERT(varchar(35), SUBSTRING([KeyValue],1,NULLIF(CHARINDEX('=',[KeyValue]),0)-1)) PERSISTED NOT NULL,
  [Value] AS SUBSTRING([KeyValue],CHARINDEX('=',[KeyValue]) + 1, LEN([KeyValue]) - NULLIF(CHARINDEX('=',[KeyValue]),0)) PERSISTED NULL


How to pivot multiple columns in PostgreSQL 9.5? I have the sample data as follows and want to find if I can use crosstab function in this case.

Sample Data:

ITEM   | CD | TYPE |PARTS |  PART | CNT |
Item 1 | A  | AVG  |2     |  1    | 10  |
Item 1 | A  | AVG  |2     |  2    | 20  |
Item 1 | B  | AVG  |2     |  1    | 10  |
Item 1 | B  | AVG  |2     |  2    | 20  |
Item 1 | A  | SUM  |2     |  1    | 10  |
Item 1 | A  | SUM  |2     |  2    | 20  |
Item 1 | B  | SUM  |2     |  1    | 10  |
Item 1 | B  | SUM  |2     |  2    | 20  |

Expected Result:

ITEM   | CD | AVG_1 | SUM_1 | AVG_2 | SUM_2 |

Item 1 |  A | 10    | 10    | 20    | 20    |

Item 1 |  B | 10    | 10    | 20    | 20    |

In addition, if the parts are 3, then there will be an additional row for both AVG and SUM for A and B codes. This is dynamic and user can enter this value. How can multiple rows be flattened ?

In this requirement, Item 1 and code A has to display Part 1 Sum and Average and then Part 2 Sum and Average and this can change based on the value in Parts column.

Appreciate your help on this !!

The following query produced the results I require. However, if there is more than 2 parts (part of database column), then additional columns, AVG3, SUM3 need to be generated dynamically.

select a.ITEM, a.CD, a.AVG1, b.SUM1, a.AVG2, b.SUM2
from ( SELECT substr(row_name,1,POSITION('|' IN row_name)-1) as item
              substr(row_name,POSITION('|' IN row_name)+1,99) as cd,
              AVG1,
              AVG2
         FROM crosstab('SELECT item||''|''||cd, part::text, cnt::text
                          FROM a
                         WHERE type = ''AVG''
                        order by item, cd, part',1)
           AS ct (row_name text, AVG1 text, AVG2 text)
     ) a,
    ( SELECT substr(row_name,1,POSITION('|' IN row_name)-1) as name,
             substr(row_name,POSITION('|' IN row_name)+1,99) as cd,
             SUM1,
             SUM2
        FROM crosstab('SELECT item||''|''||cd, part::text, cnt::text
                         FROM a
                        WHERE type = ''SUM''
                       order by item, cd, part',1)
          AS ct (row_name text, SUM1 text, SUM2 text)
    ) b
where a.item = b.item
  and a.cd = b.cd;


Possible to keep max number of records in postgresql? Basically part of our Postgresql table is used to keep server access logs, and as such sometimes during production this can get pretty large. is there any way of setting in postgresql to have a maximum number of records a table can have and to push off the oldest record?

You can define a trigger to maintain your desired row number:

CREATE OR REPLACE FUNCTION trf_keep_row_number_steady()
RETURNS TRIGGER AS
$body$
BEGIN
    -- delete only where are too many rows
    IF (SELECT count(id) FROM log_table) > rownum_limit
    THEN
        -- I assume here that id is an auto-incremented value in log_table
        DELETE FROM log_table
        WHERE id = (SELECT min(id) FROM log_table);
    END IF;
END;
$body$
LANGUAGE plpgsql;

CREATE TRIGGER tr_keep_row_number_steady
AFTER INSERT ON log_table
FOR EACH ROW EXECUTE PROCEDURE trf_keep_row_number_steady();

This is probably not the best performing option, but once you reach the limit, it will never be exceeded.  If there is space for fluctuation, then you can check the row number periodically and delete excess rows from the beginning.

EDIT:
If you have really large logs (say a million per month) than partitioning can be the easiest solution.  You can then simply drop the unnecessary tables (say where max(timestamp) < CURRENT_DATE - 1 year).  You can use your timestamp (or a derived date) as condition for range partitioning.

But be careful before discarding old logs.  Are you sure you will never need those?


How to speed up execution time in a SQL Server 2016 database which has restored from 2008 R2? I have restored the backup of the database from SQL Server 2008 R2 to SQL Server 2016 and now facing the performance issue.
How can the issue be solved without changing database Compatibility?

have restored the backup of the database from SQL V-2008R2 to SQL version 2016 and now facing the performance issue.

Tossing away considerations like hardware,

Are your configuration settings set?

Is your max memory setting high enough? Is your Power plan set to high performance?

The database should be in the same state as it was on the SQL 2008 R2 Instance when the backup occured.
You could always update your statistics to get some better query plans.

  How can the issue be solved without changing database Compatibility

If you can set your compatibility level to 110 (2012) then you could enable traceflag 2312 to get the sql 2014 Cardinality estimator
(this can improve but also degrade the performance of queries).
Traceflag 4199 might also help.

It is hard to help without more information.


Replicate SQL Server 2008 across domains I have been tasked with migrating a database, size being up to 15Gb, from a SQL Server 2008 database in a 3rd party cloud VM, to SQL Azure. On go-live day, when we switch from the old host, the Azure, we have a 1 hr downtime allowance.

Unfortunately, the link speed is 1Mbps. So that would take around 30 hours to transfer. What I would like to do is create a Azure VM, with SQL Server 2008R2. Then, using either transaction replication, mirroring or log shipping, allow the database to replicate to my new VM from the source. I have no time limit for this, as the application migration is at my discretion. Could be a day, could be a few months.

My question is - how can I achieve this? I think transaction replication would be best, so that the database is basically up to date all the time. I need to demonstrate that it's possible, so I have created two VMs in Azure (ClientSourceVM and ClientStagingVM) in separate vnets (Trying to simulate across the internet replication).

The initial snap shot would take up to two days to transfer. Would this cause any adverse effect on the source database? Locking?

And is it possible to do replication across the internet? It seems I need to share folders and setup permissions, but not sure how that works with servers in separate domains across the internet. Is it possible?

With SQL Server 2008R2 - is there any better option that can serve my needs?

Note, once the database gets to 2008R2, I'll then hopefully migrate that to it's Azure SQL destination on go-live day. The replication is purely to allow an up to date database be available on the go-live day without waiting nearly 2 days for the backup file to copy.

Any advice and ideas would be greatly appreciated.

I could do on-prem SQL 2008 directly to Azure? And then on go live - simply stop the replication? I’d need to do more to the source database to make it a stand-alone? Or do I just remove replication - setup users and I’m good to go? I’d obviously do dry runs on a separate test environment - but the idea is do-able? We don’t have huge data spikes at all. It’s a database with only a few concurrent users. Less than 10.

nick-mcdermaid: I can tell you that I've set up a push replication from on-prem SQL Server (2008) to Azure SQL. The snapshot took a while but didn't appear to cause any issues.

The subsequent transactional replication worked, but due to the batch load nature of the source, we kept running out of log space and we could not keep it running. So as long as changes to the database are consistent (and you don't have huge spikes of huge amounts of activity) it should work fine.

Cross domain is only relevant if you want to use Windows Authentication. Since Azure SQL doesn't support windows authentication, just plan to use SQL authentication.

  I could do on-prem SQL 2008 directly to Azure? And then on go live - simply stop the replication? I'd need to do more to the source database to make it a stand-alone? Or do I just remove replication - setup users and I’m good to go? I’d obviously do dry runs on a separate test environment - but the idea is do-able? We don’t have huge data spikes at all. It’s a database with only a few concurrent users. Less than 10.

It’s completely doable. You just have to do push subscription from on-prem using SQL authentication. But first be aware of the major gotchas of migrating to SQL Azure. Three obvious ones:

No windows authentication
No cross database calls
No ability to restore from BAK (not a problem for you)

Transactional Replication to Azure SQL DB now in public preview


Oracle 10g: Properly using ora_rowscn to detect table row changes (ie, inserts, updates, deletes) Some research into checking when a table's records were last updated, modified, or deleted has lead me to the pseudo column known as ora_rowscn.

First, I do this:

select max(ora_rowscn) from tablename;

I take note of the number.  Then I do an insert, update, and a delete, check that max value before and after each.  It appears to increment for each type of change.

If you're wondering why I am doing this, we cache a list of entities in our C# windows service.  This service runs on two load-balanced servers, so there's a separate instance of each running.  When an update occurs on server A, server B needs to know about it.  What I want to do is cache max(ora_rowscn) into a variable.  Every time our application goes to insert, update, or delete a record, it will get a new max from the database.  If the value is different then it obviously knows it needs to go get a new list from the database.

So my actual question is this:  Are there any other snags I should be aware of that might result in an insert, update, or deletion of a record not incrementing this value?

Edit: Can someone add ora_rowscn as a tag?

Are there any other snags I should be aware of that might result in an
  insert, update, or deletion of a record not incrementing this value?

ora_rowscn is always incremented when a row changes - but in a default configuration it can also be incremented when a row does not change

If you need to check the whole table for udates, one method is to use auditing. On the other hand if you only need to check the row you are trying to update for conflicts, ora_rowscn with rowdependencies is ideal.


How can I batch a drop column operation? I have a BLOB column in my database schema that I no longer need. I need to drop this from my production server. I'm running MYSQL-5.6

Testing this on my development copy suggests that it will take about 1.5 hours to run. Right now, it's not feasible for us to bring the production DB down for that long for maintenance. What's the best way for me to do this in batches? e.g., I can acquire a write-lock on the table for about 5-10 mins at a time without causing any production failures.

I don't have a DB administrator background so I hope this isn't a really basic question covered elsewhere.

I think the following thread might prove useful to you: MySQL Drop Column From Large Table

The suggestion is to copy the data to a new table MINUS the column you wish to lose, then drop the old table and rename the new one.  This is if you can't secure a maintenance window, say overnight/off-production hours, to do the work instead.

From my understanding - and others, please correct me if I'm wrong - although you could clear/NULL the data in this column in batches, you couldn't drop the column in batches since a column drop implies rewriting the row without that column - and you can't have a row in a table that doesn't have an attribute column that the other rows have.


MySQL Binary Installation (mysql_install_db) While installing from binaries. The below always gives error when we execute from 'scripts' directory

[mysql@hostname ~]$ cd /mysql/product/5.6.12/scripts/
[mysql@hostname ~]$ . mysql_install_db --defaults-file=my.cnf

-bash: use: command not found

But the same works fine when I execute one step back from scripts directory

[mysql@hostname ~]$ scripts/mysql_install_db --defaults-file=my.cnf

Question: Why this happens ?

I think you have a mistake, you can't execute:

[mysql@hostname ~]$ . mysql_install_db --defaults-file=my.cnf

Because the correct line is:

[mysql@hostname ~]$ ./mysql_install_db --defaults-file=my.cnf

You don't have in PATH environtment variable the current directory ".".


Can a database be created with no user and/or owner? Maybe this is a good or a bad question but in my Google research I wasn't able to find out a good answer (maybe because I suck at performing a good search). In a few words:

can a DB be created without a user and/or owner?

I am trying to answer a question on Stack Overflow and the user has shared it DB configuration and something caught my attention:

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = '';
$db['default']['password'] = '';
$db['default']['database'] = 'gotche';
$db['default']['dbdriver'] = 'mysqli';

The database does not have a username!

Regardless the DB used - which is in scenario is MySQL (don't know the version) - is this possible?

I don't think the username and password were removed for security reasons: It would be better to change it to something like ***** - in that case everyone would know there is a username/password but I can ask the user if that's the reason.

MySQL allows for a blank user with a blank password (anonymous access) to connect.

See Securing the Initial MySQL Accounts

  If accounts for anonymous users were created, these have an empty user
  name. The anonymous accounts have no password, so anyone can use them
  to connect to the MySQL server.

Besides that, MySQL does not have the concept of "database owner", like SQL Server and Postgres do. There is a privilege system, where users can be assigned various types of access to databases, tables, etc, with GRANT statement.


How to fetch related rows on multiple condition with ranking I have following table for storing item information in PostgreSQL.

id  |  name      | category | seller | m_age | status | type_id | region |
--------------------------------------------------------------------------
10  | TBox       |  Gun     | 14     |  4    | Q4     | 12      |   EU   |
11  | Globe      |  Gun     | 36     |  12   | Q1     | 17      |   EU   |
12  | Nike       |  CFK     | 12     |  5    | Q2     | 12      |   AS   |
32  | Raven      |  Knife   | 21     |  14   | Q1     | 12      |   EU   |
46  | TBox       |  Gun     | 14     |  10   | Q4     | 12      |   AS   |
46  | Blaster    |  Gun     | 14     |  10   | Q4     | 12      |   EU   |
78  | PaintGun   |  Gun     | 12     |  10   | Q2     | 54      |   US   |
98  | TBox       |  OD      | 21     |  4    | Q3     | 54      |   AS   |

I want to find related items on basic of certain criteria.

Category > title > Tech Info Matched(m_age, status, type_id, region) > Seller

So for id 10 - TBox item rank should be following(name and category column value and Info matched and reason cololms are for visual reference only)

id  | name  | category | INFO MATCHED | RANK |             REASON              |
--------------------------------------------------------------------------------
10  | TBox  |  Gun     |      -       |   -  |                -                |
--------------------------------------------------------------------------------
11  | Diff  |  Same    |      1       |   3  | same cat diff name 1 info match |
12  | Diff  |  Diff    |      1       |   6  | diff cat diff name 1 info match |
32  | Diff  |  Diff    |      2       |   7  | diff cat diff name 2 info match |
46  | Same  |  Same    |      3       |   1  | same cat and name               |
46  | Diff  |  Same    |      4       |   2  | same cat diff name 4 info match |
78  | Diff  |  Same    |      0       |   4  | same cat diff name 0 info match |
98  | Same  |  Diff    |      1       |   5  | diff cat same name              |

I am still a novice to SQL. I have tried with normal CASE and function. but haven't got proper results. Rank will not work with this many condition. Is there any way to pull this results anyhow with PostgreSQL?

SQL Query to replicate the data:
CREATE TABLE Products (
  Id INT,
  name NVARCHAR(max),
  category NVARCHAR(max),
  seller INT,
  m_age INT,
  status NVARCHAR(2),
  type_id INT,
  region NVARCHAR(2)
);

INSERT INTO Products
  (Id,
  name,
  category,
  seller,
  m_age,
  status,
  type_id,
  region)
VALUES
('10','TBox','Gun','14','4','Q4','12','EU' ),
('11','Globe','Gun','36','12','Q1','17','EU' ),
('12','Nike','CFK','12','5','Q2','12','AS' ),
('32','Raven','Knife','21','14','Q1','12','EU' ),
('46','TBox','Gun','14','10','Q4','12','AS' ),
('46','Blaster','Gun','14','10','Q4','12','EU' ),
('78','PaintGun','Gun','12','10','Q2','54','US' ),
('98','TBox','OD','21','4','Q3','54','AS');

Solution:
DECLARE @id INT = 10,
@name NVARCHAR(MAX) = 'TBox';

Select
    P4.id,
    P4.name,
    p4.category,
    P4.seller,
    P4.m_age,
    P4.status,
    P4.type_id,
    P4.region,
    (DENSE_RANK() OVER (ORDER BY P3.Category DESC, P3.name DESC, P3.Info DESC, P3.seller DESC)) -1 AS Rank
From (
    Select
        *,
        ROW_NUMBER() OVER (ORDER BY ID) AS SNO
    From Products
) P4
LEFT JOIN
(Select
    ROW_NUMBER() OVER (ORDER BY P1.ID) AS SNO,
    CASE WHEN P1.Category=P2.Category THEN 1 ELSE 0 END Category,
    CASE WHEN P1.name=P2.name THEN 1 ELSE 0 END name,
    (
        CASE WHEN P1.m_age=P2.m_age THEN 1 ELSE 0 END +
        CASE WHEN P1.status=P2.status THEN 1 ELSE 0 END +
        CASE WHEN P1.type_id=P2.type_id THEN 1 ELSE 0 END +
        CASE WHEN P1.region=P2.region THEN 1 ELSE 0 END
    )                                                       info,
    P1.seller
From Products P1
CROSS Join
(
    Select * From Products
    WHERE Id = @id and name = @name
) P2 ) P3
ON P3.SNO = P4.SNO
ORDER BY P4.ID;

Query Output:
id|name    |category|seller|m_age|status|type_id|region|Rank|
--|--------|--------|------|-----|------|-------|------|----|
10|TBox    |Gun     |    14|    4|Q4    |     12|EU    |   0|
11|Globe   |Gun     |    36|   12|Q1    |     17|EU    |   3|
12|Nike    |CFK     |    12|    5|Q2    |     12|AS    |   7|
32|Raven   |Knife   |    21|   14|Q1    |     12|EU    |   6|
46|TBox    |Gun     |    14|   10|Q4    |     12|AS    |   1|
46|Blaster |Gun     |    14|   10|Q4    |     12|EU    |   2|
78|PaintGun|Gun     |    12|   10|Q2    |     54|US    |   4|
98|TBox    |OD      |    21|    4|Q3    |     54|AS    |   5|

FYI: Rank of Id - 12 and 32 should be flipped as Id 32 record has 2 matched info so it's rank should be higher compared to Id 12 record.