OLE DB provider "MSOLEDBSQL" with SQL Server not supported? I have been using linked server with the old provider (SQLNCLI) without any issue, as recommended by Microsoft, I'm planning to switch to new provider (MSOLEDBSQL). I'm able to add linked server using following T-SQL after installing the drivers
EXEC sp_addlinkedserver
   @server=N'SQL02\DEV1',
   @srvproduct=N'',
   @provider=N'MSOLEDBSQL',
   @datasrc=N'SQL02,1933';

Unfortunately, getting following error when I try to query new linked server:
Queries that I tried:
--- example 1
select * from OPENQUERY ([SQL02\DEV1], 'select name from sys.databases');

--- example 2
select name from [SQL02\DEV1].master.sys.databases;

--- example 3 (without linked server dependency)
SELECT c.* FROM OPENROWSET(
      'MSOLEDBSQL'
    , 'Server=SQL02,1933;Database=master;Integrated Security=True;'
    , 'SELECT name FROM sys.databases;'
    ) c;

Getting same error from all examples:

Out-of-process use of OLE DB provider "MSOLEDBSQL" with SQL Server is not supported.

Does that really mean SQL-2016 is not supported for using new provider MSOLEDBSQL especially in Linked Servers, or is there anything I missed other than re-installing drivers and restarting the SQL Server.

You seem to have done everything right. However the link you posted has a comment on the page which states:

This page is no longer maintained. Please read the details below.

If you open the details tab by clicking the + sign you will be greeted with the following information:

This page is no longer maintained. To download the Microsoft OLE DB Driver 18 for SQL Server, please go to the documentation page at https://aka.ms/downloadmsoledbsql.

This will take you to https://docs.microsoft.com/en-gb/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15 (Just in case the short link becomes obsolete.)
I would grab the newest OLE DB drivers from there.
Microsoft OLE DB Driver for SQL Server
Take your time and read the information about the Different generations of OLE DB Drivers on the web page. In the sub section titled 3. Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) there is this small note that states:

The new OLE DB provider is called the Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL). The new provider will be updated with the most recent server features going forward.

Based on what you wrote in your question, you must be using the correct OLE DB driver. Good....

Create Linked Server from SQL Server 2019 to SQL Server 2016
I have multiple instances on my laptop and created a linked server from my 2019 instance to a SQL Server 2016 instance using the following stored procedures:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
    @server = N'MSOLEDBDSQL',
    @srvproduct=N'SERVER\sql2016',
    @provider=N'MSOLEDBSQL',
    @datasrc=N'SERVER\sql2016',
    @catalog=N'master'

GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MSOLEDBDSQL', @locallogin = NULL , @useself = N'True'
GO

I then queried the linked server with the following statement:
SELECT name, create_date FROM MSOLEDBDSQL.master.sys.databases

Which promptly returned:

+----------------------+-------------------------+
|         name         |       create_date       |
+----------------------+-------------------------+
| master               | 2003-04-08 09:13:36.390 |
| tempdb               | 2020-11-03 16:29:26.787 |
| model                | 2003-04-08 09:13:36.390 |
| msdb                 | 2016-04-30 00:46:38.773 |
| SSODB                | 2020-01-03 14:35:34.143 |
| BizTalkMgmtDb        | 2020-01-03 14:35:39.570 |
| BizTalkDTADb         | 2020-01-03 14:35:43.370 |
| BizTalkMsgBoxDb      | 2020-01-03 14:35:45.137 |
| BizTalkRuleEngineDb  | 2020-01-03 14:36:21.603 |
| BAMPrimaryImport     | 2020-01-03 14:36:34.713 |
| BAMArchive           | 2020-01-03 14:36:35.333 |
| DemoDB               | 2020-01-15 12:04:41.427 |
| BAMAlertsApplication | 2020-01-28 14:40:20.767 |
+----------------------+-------------------------+

Seems to work just fine.
Create Linked Server from SQL Server 2016 (CI) instance to SQL Server 2016 instance
I then created a linked server from my 2016 Case-insensitive instance to the same SQL Server 2016 instance using the following stored procedures:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
    @server = N'MSOLEDBDSQL',
    @srvproduct=N'SERVER\sql2016',
    @provider=N'MSOLEDBSQL',
    @datasrc=N'SERVER\sql2016',
    @catalog=N'master'

GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MSOLEDBDSQL', @locallogin = NULL , @useself = N'True'
GO

I then queried the linked server in my CI instance with the following statement:
SELECT name, create_date FROM MSOLEDBDSQL.master.sys.databases

Which promptly returned:

Msg 7430, Level 16, State 3, Line 1
Out-of-process use of OLE DB provider "MSOLEDBSQL" with SQL Server is not supported.

Completion time: 2020-11-05T13:41:43.0333451+01:00

This seems to correlate with what you are observing..... Hmmm. Let's think this over.
Possible Causes

Collation has to be the same: CI or CS
Drivers aren't fully 2016 compatible when connecting from a 2016 to a 2016 instance.
Something is different on SQL Server 2019 instance compared to SQL Server 2016 instance.

Let's go and have a look at the ...
Ojbect Explorer | Server Objects | Linked Servers | Providers | MSOLEDBSQL Properties
We'll open up the properties of the driver on the SQL Server 2019 instance I used first:

...and the properties of the driver on the SQL Server 2016 CI I used in my second test:

And there we have it. The property Allow inprocess on the SQL Server 2016 CI configured instance isn't ticked. The SQL Server 2019 instance has this setting ticked. Let's change it for our SQL Server 2016 instance and run the query. It works!
Solution
Ensure the option Allow inprocess is ticked for the Microsoft OLE DB Driver for SQL Server in its properties in the Linked Server | Providers.


backup & recover data in postgresql I want to backup postgresql data,
right now I use disable insert,update,delete in my application level, become readonly mode.

But I'm wondering what happen if insert,update,delete during dump database,
how to prevent it with postgresql function?
does dump automatic lock(insert,update,delete) table,
or is there any method do this, lock(insert,update,delete) and dump in same query?

my dump query:
pg_dump -U postgres -f backup/dbname.sql dbname

You don't need to disable writes to the database while pg_dump is running. It's designed to create consistent backups by default. Quoting the manual:

  pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently.  pg_dump does not block other users accessing the database (readers or writers).


Issues with MySQL 8.0.17 Recently we upgraded MySQL version on our UAT database server to 8.0.17 from 5.7.18.
We are facing issues with our web application, Website is loading slowly if there are more users accessing it I mean even if 10-20 users open application simultaneously. We didn't face this issue when using the previous version of MySQL 5.7.18.
The main difference in MySQL settings is query cache, It's been deprecated in MySQL 8.0.17, We were using query cache in the previous version could this be a cause? The total size of the database is about 1TB. We are not experiencing any high CPU or Memory issues at the moment.
When reverted back to MySQL 5.7.18 we don't observe slowness in website loading.
Machine Details:
It's a AWS EC2 instance,
RAM : 8GB,
OS : Windows Server 2012,
SSD : 2TB EBS Volume
MySQL Settings from my.ini [Most of them were the same in the older version as well]
default-character-set=utf8mb4
skip_ssl
event_scheduler=OFF
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
port=3306
default-storage-engine=MYISAM
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
log-output=FILE
skip-log-bin
lower_case_table_names=1
max_connections=1500
table_open_cache=2000
tmp_table_size=16M
thread_cache_size=9
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=32M
key_buffer_size=3584M
read_buffer_size=512K
wait_timeout = 480
read_rnd_buffer_size=1M
skip-innodb
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=8M
innodb_buffer_pool_size=72M
innodb_log_file_size=48M
innodb_thread_concurrency=8
innodb_autoextend_increment=64M
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=70
flush_time=0
join_buffer_size=256K
max_allowed_packet=1060M
max_connect_errors=100
open_files_limit=4110
sort_buffer_size=1M
table_definition_cache=1400
binlog_row_event_max_size=8K
wait_timeout = 480
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
loose-local-infile = 1

Note: We don't use InnoDB tables in our application all tables are in MyISAM, I aware that all internal tables are in InnoDB since Mysql version 8. Also, we are not using replication.
In production, we expect about 200-500 users accessing portals simultaneously, Can anyone please suggest changes in MySQL settings for our application loading time to decrease.
SHOW GLOBAL VARIABLES : pastebin
SHOW GLOBAL STATUS : pastebin
SHOW ENGINE INNODB STATUS : pastbin
InnoDB Metrics : pastbin

Rate Per Second = RPS
Suggestions to consider for your my.ini [mysqld] section
max_connections=750  # from 1500 to conserve RAM and have 50% more capacity than your expected 500 users
thread_cache_size=100  # from 9 to minimize threads_created overhead
innodb_io_capacity=1900  # from 200 to enable more of your SSD IOPS capacity
read_rnd_buffer_size=64K  # from 1M to reduce handler_read_rnd_next RPS of 39,200

You will find these changes will significantly reduce footprint and CPU busy.
For additional suggestions, view profile, Network profile for contact info and free downloadable Utility Scripts to improve performance.


How to determine the size of my tables in the SQL Server database Is there any built-in function/stored procedure/query which is helpful to retrieve information about the size of MyTable in the SQL Server database?

If you don't want to write a script, you can also open the much underused 'Object Explorer Details' in SSMS (Shortcut key F7).

From the Top-Level, open the Tables folder to get a list of all the tables in your database.

You may need to customise the columns to see the Space Used. This can be done by right clicking on the header row and choosing the columns you wish to display.

There's plenty more data like this available in Object Explorer Details.


Table replication with extra TIMESTAMP column on slave I have  a table on master and same table on slave with an extra column like

InsertTime timestamp DEFAULT CURRENT_TIMESTAMP

I want to keep record of time when there is update on slave.

Replication is working fine, but added column InsertTime on slave DB is storing 000.000.00 00-00-00 value. It is not storing current system timestamp. When I issue separate insert statement on slave side, it is storing system time but not with replication. Can anyone suggest me how to achieve system timestamp on slave side?

Other details as follows:

MySQL DB: MariaDB 5.5.23
Replication Type: Row based
Platform: Linux

The behavior you are seeing is normal for row-based replication.  Extra columns are valid at the right-hand "side" of the table, but row-based replication operates at a level, on the slave, where automatic timestamps aren't updated.

Statement-based replication has been suggested, but it seems likely to become even more fragile than it already is.

MariaDB 10.1 introduces slave triggers for row-based replication, which could accomplish the purpose, assuming the master does not already have triggers on the table.

Normally, when an event is replicated in row format, triggers do not fire on the slave, because the changes made on the master by the triggers will have replicated with the row events.

MariaDB 10.1 can be configured such that row events will fire on the slave, under one condition: if the event encounters a trigger on the slave, but no trigger fired on the master, because there is no trigger defined on the master, then the slave trigger will fire.  Since this requires the master to include new information in the binlog (I can't think of any facet of binary loggong that captures this now) then the master would need to be running 10.1 also.

This capability seems like it could accomplish what you want.


Converting to date and performing a count SELECT a.UserID, a.SignUpDate, DATE_DIFF(CURRENT_DATE, a.SignUpDate, day) As DaysActive
FROM (SELECT u.Userid, CAST(MIN(u.SessionLogin)AS DATE) AS SignUpDate
      FROM database.data_source.user_session_table as a)

The above code pulls the session login time from the user_session table performs a min function to collect their initial login, whilst converting it from a timestamp to a date format. DaysActive shows how many days since they have logged in.

What i am trying to do is find how many days they have logged into the application since their initial sign in.(Days Active)
SessionLogin field captures a timestamp each time they login, i am trying to convert that to a date, whilst also adding it to the above table as a count.

Current Output

UserID  | SignUpDate| DaysActive |
--------+-----------+-------------
001     |2017-12-04 | 35         |
002     |2017-12-08 | 31         |
003     |2017-12-05 | 34         |

user_session_table

UserID  |SessionLogin         |
--------+---------------------|
001     |2017-12-04 01:37:49  |
001     |2017-12-08 05:47:04  |
001     |2017-12-05 04:40:00  |
002     |2017-12-08 12:25:39  |
002     |2017-12-09 01:37:49  |
003     |2017-12-05 05:47:04  |
003     |2017-12-05 08:40:00  |

Desired Output

UserID  | SignUpDate | DaysActive| DaysEngaged
--------+-----------+------------+-----------
001     |2017-12-04 | 35         |3
002     |2017-12-08 | 31         |2
003     |2017-12-05 | 34         |1

Code: MSSQL / Bigquery

Don't know about big query but for SQL Server you could do it like this.

select S.UserID,
       min(cast(S.SessionLogin as date)) as SignUpDate,
       datediff(day, min(cast(S.SessionLogin as date)), getdate()) as DaysActive,
       count(distinct cast(S.SessionLogin as date)) as DaysActive
from dbo.SessionLogin as S
group by S.UserID;


distinct values from multiple fields in array I have a similar mongodb collection:

[{
    "country": "France",
    "exchange": {
        export: [{
            resource: 'MILK',
            origin: ['Toulouse', 'Bordeaux']
        }]
        import: [{
            resource: 'BEEF',
            origin: ['Toulouse', 'Bordeaux']
        }]
    }
}, {
    "country": "Spain",
    "exchange": {
        export: [{
            resource: 'PORK',
            origin: ['Madrid', 'Barcelona']
        }]
        import: [{
            resource: 'WHEAT',
            origin: ['Valencia', 'Bilbao']
        }]
    }
}]

I want distinct values of resource for both import and export.
Expected result is:

{
    "countries" : ["Spain","France"],
    "export" : ["PORK", "MILK"],
    "import" : ["WHEAT","BEEF"]
}

Here's what I'm doing

db.countries.aggregate([
{$group: {
    _id: null,
    countries: {$addToSet: '$country'},
    export: {$addToSet: '$exchange.export.resource'},
    import: {$addToSet: '$exchange.import.resource'}
    }}
])

which returns:

{
    "countries" : ["Spain","France"],
    "export" : [["PORK"], ["MILK"]],
    "import" : [["WHEAT"],["BEEF"]]
}

these extra arrays (["PORK"]) surrounding values are annoying: how can I avoid them ?

Nevermind, just found the answer, just need to add 2 unwind stages

[{
    $unwind: {
        path: '$exchange.export'
    }
}, {
    $unwind: {
        path: '$exchange.import'
    }
}, {
    $group: {
        _id: null,
        countries: {$addToSet: '$country'},
        export: {$addToSet: '$exchange.export.resource'},
        import: {$addToSet: '$exchange.import.resource'}
    }
}]


Uninstalling / upgrading PostgreSQL on OSX The problem
I have PostgreSQL 9.3 installed on my MacBook Pro. I don't remember how I installed it (there are numerous ways), and now I want to upgrade to PostgreSQL 9.5.
Foresnsics
The running PostgreSQL processes are:
  503    89     1   0 Fri09PM ??         0:06.57 /Library/PostgreSQL/9.3/bin/postmaster -D/Library/PostgreSQL/9.3/data
  503   236    89   0 Fri09PM ??         0:00.02 postgres: logger process
  503   249    89   0 Fri09PM ??         0:01.10 postgres: checkpointer process
  503   251    89   0 Fri09PM ??         0:03.06 postgres: writer process
  503   252    89   0 Fri09PM ??         0:03.09 postgres: wal writer process
  503   253    89   0 Fri09PM ??         0:05.79 postgres: autovacuum launcher process
  503   254    89   0 Fri09PM ??         0:15.80 postgres: stats collector process

Which means that the binaries are located at:
/Library/PostgreSQL/9.3/bin/postmaster
/Library/PostgreSQL/9.3/bin/psql
...

and the data at
/Library/PostgreSQL/9.3/data

What have I tried
I made sure it was not installed using Homebrew.
Any idea which installation of PostgreSQL is installed on my mac, and how to upgrade it?

try to use the edb installer to upgrade your postgresql version[1].

If you want to make your life easer, I recommend you backup your databases, uninstall your current postgresql configuration and use the postgres app[2].

For a full list of installers, take a look here[3]

Hope it helps.

References:

http://www.enterprisedb.com/products-services-training/pgdownload#macosx
http://postgresapp.com
https://www.postgresql.org/download/macosx/


Migrate SQL Server database to multiple files (shrinkfile emptyfile vs log shipping) I have a SQL Server 2008 database in production that we are moving onto a new server. The current database has a single ~400GB .MDF file. The new server will be running SQL Server 2012, and we are running mirrored Intel 910 SSDs. These drives will present us with 4x 200GB partitions.

To make this work, we will need to split the single .MDF into 4 smaller ones using DBCC SHRINKFILE with EMPTYFILE. We have done this in test, and it still takes ~ 3.5 hours to do which is too long. The existing database is OLTP, and 365/24/7 and I know blocking will occur during this process, so we can't do it on production first.

My question, is there a way to backup and restore the database to the new server in a temp location. create the new files, EMPTY the temp .MDF into the new locations, then apply transaction logs after? That way we can move the data while current old production is up and running, then do a short shutdown, apply logs, and bring up the new DB?

Or are there any other options to get from Server A with one file and Server B with 4 files on different drives with minimal downtime?

In a word, no.  You have to do it live.

What you'll want to do it add 3 new files.  Then simply start doing index rebuild operations.  As long as you are doing rebuilds not defrags SQL will start spreading the data across all the data files.

I'm assuming that you are running on SQL Enterprise Edition and can do online index rebuilds.


SQL Server doesn't use expected index I have the following query (simplified from an Entity Framework query, I don't have direct control of the SQL):

SELECT TOP 15
HouseHoldingRuns.Id,
(
    SELECT TOP 1 ManCoId FROM (
        SELECT TOP 1 GridRuns.Id
        FROM GridRuns
        WHERE HouseHoldingRuns.Id = GridRuns.HouseHoldingRunId
    ) AS [FirstGridRun]
    INNER JOIN Documents ON FirstGridRun.Id = Documents.GridRunId
    ORDER BY Documents.Id
)
FROM HouseHoldingRuns
ORDER BY HouseHoldingRuns.Id

Which produces the following plan:

Adding a WITH (FORCESEEK) on the Documents table results in the following plan, which performs much better.

Why is SQL Server getting it so wrong?  I'm guessing the problem lies in the large difference between estimated and actual rows, but I don't know how to fix that... (I've tried updating all stats and rebuilding indexes).

Note: I don't really want the ORDER BY Documents.Id in there, but put it in for Evgeny to see the results of his suggestion.

In the version of the query with FORCESEEK, the majority of the query cost is in the key lookup. You can eliminate this key lookup by creating a nonclustered index on GridRunId and MancoId.

Have a look at the estimated cost figure in the SELECT (root) node of your plans. The optimizer will chose the plan with the lowest estimated cost. By adding a covering nonclustered index, you eliminate the need for the key lookup. This should make the estimated cost of the index seek less than the index scan. You might also want to run the query with OPTION (RECOMPILE) to make sure it uses a fresh execution plan. The index could look something like this:

CREATE NONCLUSTERED INDEX [IX_GridRunId_MancoId] ON [dbo].[Documents]
(
    [GridRunId] ASC,
    [MancoId] 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
)
ON [PRIMARY];

The optimizer would ordinarily cost the scan higher than the seek, but the effect of the TOP means the optimizer computes costs for the desired number of rows (a 'row goal') rather than for the whole set (it estimates that a partial scan will locate the 15 desired rows more efficiently than the seek + lookup combination).


Query - Want the count of ID based on the LAST DATE it appears This will be in MySQL. Table consists of following fields:

Date, StockNo, blah, blahblah, blahblahblah

I believe the primary key needs to be Date & StockNo.

This is Inventory Data for widgets.
The assumption I am making is that when the StockNo no longer appears in the data, it was sold as of the date it last appeared.

The thing is this...sometimes a StockNo will disappear on Day T+1 (and thus we'd assume it was sold), but then reappear on Day T+4 (due to data error, or a return, etc...), and disappear again on Day T+6 and NEVER reappear.  So Day T+6 is when we'd consider it SOLD.

So what I am trying to know is the following:

1) How many unique StockNo disappear on Day T+1 vs. Day T? (for all Days)

2) Then AS OF THIS MOMENT how many unique StockNo had their LAST day in the database on Day T?

So the output would show those two counts for all Dates in the database.

In addition to the dummy data below, I have sample data in CSV (40,000 entries per day) which i can send.

Thank you!!!

Additional info:

So if the StockNo shows up in the database on 9/9/16 but NOT on 9/10/16 then we would say that it disappeared and therefore add it to the total count for date 9/9/16.

If that same StockNo later reappears on 9/11/16, when we run the query again that ID will no longer be added to the count as of 9/9/16.  If on 9/12/16 that ID disappears again then it would be added to the count as of 9/11/16.

here's some sample data.  I did not put any data in for the "blahs" since it is not relevant for the query (i believe)

Date,   StockNo,    blah,   blahblah,   blahblahblah

9/8/2016,   1
9/8/2016,   2
9/8/2016,   3
9/8/2016,   4
9/8/2016,   5
9/8/2016,   6
9/8/2016,   7
9/8/2016,   8
9/9/2016,   1
9/9/2016,   2
9/9/2016,   3
9/9/2016,   4
9/9/2016,   5
9/9/2016,   9
9/9/2016,   10
9/9/2016,   11
9/10/2016,  2
9/10/2016,  4
9/10/2016,  6
9/10/2016,  11
9/10/2016,  12
9/10/2016,  13

The expected output run on 9/9/16 would be:

Date, Count of StockNo Last Day

9/8/16,3

9/9/16,8

The expected output run on 9/10/16 would be:

Date, Count of ID Last Day

9/8/16,2

9/9/16,4

9/10/16,6

Note that when the query is run on 9/10/16, the total for 9/8/16 is different from when it was run on 9/9/16 because an StockNo(number 6) disappeared but then re-appeared.

So here's what i did --

For # 1:
I used the answer given here: need help with this query

For #2 Here's what I did:

CREATE VIEW MaxDateStockNo(StockNo, Date) AS
SELECT StockNo, MAX(Date)
FROM my_table
GROUP BY StockNo

Then I ran a second query
SELECT Date, Count(*)
FROM MaxDateStockNo
GROUP BY Date

Now Any idea how i can get both of these Querys into one so that the output is just Date, Query 1 Result, Query 2 Result?

Thanks!


Mysql 8.0.21 long lived prepared statements have a memory leak ? or are we doing something weird -- updated the question because it was easy to initially assume I was asking for mysql tuning tips,  although some of the tips did help me narrow down the problem --
-- further update. After doing only a partial upgrade on some servers, we found the problem is occurring on MySQL 5.7 as well, so guessing it was some sort of change in behaviour between 5.6 and 5.7. It's still a bit weird to us how it didn't cause a problem in MySQL 5.6 --
We recently upgraded from MySQL 5.6 to MySQL 8.0 on a few servers,   one of the servers was fine,  and has had no problems, but it
has significantly less load than one of our other servers which has been running out of memory.
Our server launches,  then grabs 300 connections, and keeps them open with a C3P0 pool to the mysql server.
Over 4 days, this particular server accumulated 3500+ opened prepared statements, some of them taking more than 300 Mb in memory/sql/Prepared_statement::main_mem_root
It's normal for our server profile to have the connections / prepared statements open, and hasn't been a problem on mysql 5.6 or 5.7
looking that up at mysql https://dev.mysql.com/doc/dev/mysql-server/latest/classPrepared__statement.html#a999eb56b28a5202c0fb1f5df96db3c74
I can see it's somehow related to prepared statements, but 'allocate parsed tree elements (instances of Item, SELECT_LEX and other classes).' doesn't tell me much,  is it caching results? is that how it's growing ?
we're using connector/j 8.0.18  I've looked at the release notes for 8.0.18 -> 8.0.23 and there isn't any obvious memory leak fixes
our connection parameters include
cachePrepStmts", "true");
useServerPrepStmts", "true");

We were running these servers on AWS on MySQL 5.6 with the same overridden parameters on 8GB of RAM,  when we upgraded to MySQL 8.0.21 we started running out of RAM in about 1 day. We grew the server to 32Gb but didn't change the parameters. It's gone over 15 GB used and still going up.
We're pretty sure it's related to the per connection thread memory,  but not sure why.
Looking at memory_by_thread_by_current_bytes we have connections with over 200Mb
The server is running 8.0.21 on AWS RDS  m4.large
I've audited the code and all resultsets are being closed.  the prepared statements are in code as well, but the cache behavior above is keeping them open.
normally the innodb buffer pool is 4GB, but we dropped it to 3GB when we were on the 8GB machine for a bit more room.
show global status / memory_summary_by_thread_by_event_name / MySQL tuner run / show engine innodb status / show variables / sys.memory_by_thread_by_current_bytes / sys.memory_global_by_current_bytes
https://gist.github.com/DaveB93/138f6bac254fee5bbbbb7ce2af7c2fef
-- update --
we 'fixed' this by connecting to our application via JMX and changing the C3P0 connection pool settings to "maxIdleTimeExcessConnections" to 300 (up from 0)
This cleaned up all of the long lived connections, and freed up 10GB of ram,   This doesn't seem like the long term solution I want though.

The possible reason for RAM consumption can be here:
[!!] Temporary tables created on disk: 85% (249K on disk / 290K total)
When automatic temporary table is created it is always created with ENGINE=MEMORY first. If it doesn't fit the max_heap_table_size = 134217728 limit, it will be transferred on disk for further proceeding. Temptables are associated with session/connection so each thread can create a number of 128MB large temptables in the RAM - in addition to the buffers allocated for reads/joins/sorts.
I suggest to decrease max_heap_table_size to some reasonable value like 16M as far as in 85% cases temptables don't fit 128M anyway.
Another suggestion is to optimize your queries having longest execution times (due to the slow on-disk temptables operations). Slow queries log is a good starting point.


Oracle Schema Privileges Other Users I have a user with only CONNECT privileges and I'd like to know if RESOURCE would be enough to query objects in other schemas and create objects in my own schema based on that.

i.e.: create a view in my own schema by selecting tables from other schemas.

Would a SELECT ANY TABLE privilege be enough or does my user have to be granted specific object privileges for any action on another user?

A little late but this block will surely help someone.

Replace OWNER_SCHEMA_NAME with desired table owner schema.
Replace NEW_TARGET_SCHEMA_NAME in below script to the schema you want to give permissions.
Please take a look at the permissions below (which are placed as sample) before you execute this script.
Similarly, you can build a script for revoking permissions.

here goes the pl/sql block.

BEGIN
    FOR t IN (
        SELECT
            object_name,
            object_type
        FROM
            all_objects
        WHERE
            owner = 'OWNER_SCHEMA_NAME'
            AND   object_type IN (
                'TABLE',
                'VIEW',
                'PROCEDURE',
                'FUNCTION',
                'PACKAGE',
                'SEQUENCE'
            )
    ) LOOP
        IF
            t.object_type IN (
                'TABLE',
                'VIEW'
            )
        THEN
            EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON OWNER_SCHEMA_NAME.'
            || t.object_name
            || ' TO NEW_TARGET_SCHEMA_NAME';

        ELSIF
            t.object_type IN (

                'SEQUENCE'
            )
        THEN
            EXECUTE IMMEDIATE 'GRANT SELECT ON OWNER_SCHEMA_NAME.'
            || t.object_name
            || ' TO NEW_TARGET_SCHEMA_NAME';

        ELSIF t.object_type IN (
            'PROCEDURE',
            'FUNCTION',
            'PACKAGE'
        ) THEN
            EXECUTE IMMEDIATE 'GRANT EXECUTE ON OWNER_SCHEMA_NAME.'
            || t.object_name
            || ' TO NEW_TARGET_SCHEMA_NAME';

        END IF;
    END LOOP;
END;


Are TCP loadbalancers an alternative to Postgres 9.X read slaves load balancers like pgPool II or pgBouncer? To scale read traffic on PostgreSQL streaming replication slaves, I want to be able to load-balance the requests. Postgres documentation suggests using tools like pgPool and pgBouncer, but I am wondering if there is something wrong (in principle) with using a TCP load balancer (like HAProxy or AWS Elastic Load Balancer) fronting the postgres read slaves.

The load balancer serves as a single Read End Point for read requests that need to be made by clients. A significant advantage would be that read requests are not impacted when read slave servers go down, as other servers in the load balancer can pick up the load.

There's nothing wrong with it in principle, though I haven't used it myself yet.

Most client applications maintain long sessions - so the load balancing is effectively always "sticky". This can easily lead to one node having a lot more load than other nodes. A workaround can be to intentionally limit connection duration, forcing apps to reconnect occasionally and potentially be assigned a different node. That'll also let you shrink the system when load is light by collapsing work onto fewer nodes, rather than being stuck with lots of nodes, each mostly idle with only one or two active connections.

Your applications must be written carefully to check all return codes / handle exceptions. They must retry transactions that fail due to transient errors like connection loss, which means they have to remember everything they did until they commit each transaction. This is good application design anyway, it's just easier to get away with being lazy about it if disconnects and transaction aborts aren't a normal part of day to day operations.

In theory PgPool can allow "transparent" load balancing to read replicas while still permitting write queries to the master. In practice I find it's usually fiddly and hard to get right, so I usually want to have the app aware of separate "read-only" and "write/master" connections anyway.

The main advantage PgBouncer offers over TCP load balancing is that (in transaction pooling mode) it lets applications maintain idle connections without imposing overhead on the backing PostgreSQL server(s). So you can do your expensive handshake, SSL negotiation, authentication, etc once against PgBouncer, and let it bind the connection to an actual PostgreSQL backend from the pool only when it's doing something. This allows optimal utilization of Pg backends and reduces the number of idle backends sitting around doing nothing except using up resources and waiting for work. This is only valuable if your application can cope with transaction pooling (or statement pooling) mode, though; there's no benefit if you can only use session pooling mode due to use of session variables, listen/notify, advisory locks, or whatever.

If you TCP load balance you should be able to do SSL offloading just like with PgPool or PgBouncer, at least with a smart enough load balancer. You won't be able to do things like client certificate authentication that depends on pg_hba.conf, though.

I tend to scale up rather than out most of the time, so as yet this isn't something I've needed to explore. I've got some work in the pipeline that'll make it very interesting to scale out with Pg, though, so I'll have to play with this.


A simple database and Query System? I am new to databases and trying to solve one problem in which I have to build a simple database having few entries and then querying that database and then recording that answers from the database. I have to send many queries one by one. It is kind of experiment to learn database.
Could anyone suggest which simple software or language can I use to do that. I would prefer some high-level language. I know that it is doable in C/C++ but it does look fun in doing C/C++.

Thanks!

I would suggest using Postgres as the database for a beginner. This is the most ANSI compliant one of the open source databases. Unlike MySQL, it wont teach you any bad habits.

Pretty much all languages support querying Postgres, MySQL and SQL Server - just pick the languages you want to learn.


Effective backup and archive strategy for database and linked files I am using Postgres to store a variety of application data for a webapp.  Part of the application involves storing and retrieving user uploaded files.  I am storing the files in the filesystem with some associated metadata in the database.

I am trying to come up with a backup and archive strategy so that I can effectively backup and archive/restore the database and the linked files.  Here are the things I want to accomplish.

Perform routine backups that can be used for recovery from failures and which include all DB data and the linked files.  Ideally, this backup would be done while the app is running.  Live backup is certainly possible with a DB but I am not sure how to keep the linked files consistent with the database during the backup process
Archive chunks of data as they become "old".  These chunks must includes the database data plus any linked files.  It should be possible to put the archived data back into production again.  It would be ideal if it were easy to determine which ranges of objects were stored in each chunk.

Do you have any advice for how to accomplish these goals?  If the files were in the database as BLOBS these tasks would be much easier since normal database backup and restore functionality would handle this.  I am not sure how to accomplish the same thing when file data is linked to database rows.

Note: I asked this question on ServerFault but I did not get any response there.  The link to that question is: https://serverfault.com/q/284218/85719

If you store your files by hash to and link with the hash to the database row, you can simplify maintaining consistency.

In that case you can either:

keep on incrementally backing up the filesystem (eg with rsync) and never delete

or
if you need to free up space, keep an audit of link count and when the last link was deleted - you can then periodically purge the filesystem of files not linked to in your recovery window. Something like rsnapshot might help you archive these long-term in a space efficient way


Collect results of many sql server jobs I intend to create a simple job on a bunch (100+) sql servers (of differing flavors) that does a simple automated query periodically (like weekly). These jobs then need to push the result out to a central location. What is going to be a reliable, minimal config way to push those results?

I was thinking of converting the result set to text/xml and posting to a url. It seems that doing anything http from tsql is not simple, or standard. I think a CLR stored proc is probably my best option. I've been shying away from it because of the multi-step deployment.

(I also considered using a script that connects to every server and executes the query. I rejected this because of security concerns in introducing a shared account across all our servers.)

Can anyone suggest better options?

Thanks

I've created an SSIS package that connects to multiple servers and runs a query and copies that data to a central SQL server that I report against.   Just took a while to get the query to contain all the information I needed but the SSIS package is pretty straight forward, so may be an easier option than txt/xml


Help me to query this two tables properly Here is my mySQL query which I used to get the details from three tables.

    SELECT `pg`.`id`,
ref, business_name, `usr`.`full_name`,
date, `pmt`.`amount`, `pg`.`status`
    FROM kv_ad_orders AS `pg`
    LEFT JOIN kv_users AS `usr`
           ON `pg`.`sales_rep_id` = `usr`.`ID`
    RIGHT JOIN kv_payments AS `pmt`
            ON `pg`.`ref` = `pmt`. `transId`

here it shows only the kv_payments table entries only. Not the whole results. I want to  display all the selected columns.

If I make it as left join table, than its showing other results except the amount.

If you want all the rows that have Orders and Payments but maybe don't have user go for the Inner Join/Left join

SELECT `pg`.`id`, ref, business_name, `usr`.`full_name`, date, `pmt`.`amount`, `pg`.`status`
  FROM kv_ad_orders AS `pg`
INNER JOIN kv_payments AS `pmt` ON `pg`.`ref` = `pmt`. `transId`
LEFT JOIN kv_users AS `usr` ON `pg`.`sales_rep_id` = `usr`.`ID`

If all the data must match, go for this:

SELECT `pg`.`id`, ref, business_name, `usr`.`full_name`, date, `pmt`.`amount`, `pg`.`status`
  FROM kv_ad_orders AS `pg`
INNER JOIN kv_payments AS `pmt` ON `pg`.`ref` = `pmt`. `transId`
INNER JOIN kv_users AS `usr` ON `pg`.`sales_rep_id` = `usr`.`ID`

If you are talking about query projection (columns to be returned regardless of the matching data) go for select pg.*

SELECT `pg`.*, `usr`.`full_name`, `pmt`.`amount`
  FROM kv_ad_orders AS `pg`
LEFT JOIN kv_users AS `usr` ON `pg`.`sales_rep_id` = `usr`.`ID`
RIGHT JOIN kv_payments AS `pmt` ON `pg`.`ref` = `pmt`. `transId`


How to SUM by date range within a view I have a view in which I need to Group By or SUM OVER Partition By year and quarter, but the year and quarter will be computed within the view. I would like to keep it all within a view instead of needing tables and SPs.

The goal is to get SUMs by of all records within the first quarter of a year, spanning over multiple years. What I have is:

SELECT MIN(PAYROLL)                     AS MinPayroll
      ,MAX(PAYROLL)                     AS MaxPayroll
      ,YEAR(CHECK_DATE)                 AS PayrollYear
      ,MONTH(CHECK_DATE)                AS PayrollMonth
      ,SUM(sit)                         AS TotalStateTax
      ,SUM(FIT)                         AS TotalFedTax
...
...
FROM CHECKS
WHERE (PayrollMonth = 01 OR PayrollMonth = 02 OR PayrollMonth = 03)
GROUP BY PayrollYear

This gives a good idea of what I want to do, but SSMS doesn't like it, saying 'Invalid column name 'PayrollMonth', and 'Invalid Column name 'PayrollYear'

I'll start working on sample data to help, but I wanted to get this out now in case someone knew what to do off the top of their heads without data.

You can't use columns aliased in the SELECT list directly in the WHERE clause. See blog post here. Full disclosure; I wrote it.

You'd have to do this. Note that leading zeroes are discarded for integers.

SELECT MIN(PAYROLL)                     AS MinPayroll
      ,MAX(PAYROLL)                     AS MaxPayroll
      ,YEAR(CHECK_DATE)                 AS PayrollYear
      ,MONTH(CHECK_DATE)                AS PayrollMonth
      ,SUM(sit)                         AS TotalStateTax
      ,SUM(FIT)                         AS TotalFedTax
...
...
FROM CHECKS
WHERE MONTH(CHECK_DATE) = 1 OR MONTH(CHECK_DATE) = 2 OR MONTH(CHECK_DATE) = 3
GROUP BY YEAR(CHECK_DATE);

Or use a CTE

WITH really_bad_idea AS (
SELECT MIN(PAYROLL)                     AS MinPayroll
      ,MAX(PAYROLL)                     AS MaxPayroll
      ,YEAR(CHECK_DATE)                 AS PayrollYear
      ,MONTH(CHECK_DATE)                AS PayrollMonth
      ,SUM(sit)                         AS TotalStateTax
      ,SUM(FIT)                         AS TotalFedTax
...
...
FROM CHECKS
GROUP BY YEAR(CHECK_DATE)
)
SELECT *
FROM really_bad_dea
WHERE (PayrollMonth = 1 OR PayrollMonth = 2 OR PayrollMonth = 3);

Though I would ask very nicely that you not write non-SARGable WHERE clauses like that, because they are performance nightmares.

Also, it's doubltful the query will compile with just that column in the GROUP BY.


SQL Server 2014 Management Studio Backup error I had installed Microsoft SQL Server 2014 Management Studio and had been working on a database for sometime now. My laptop had Windows 7 (64 bit) installed. And I had been taking regular backup on that machine and also stored the backup on an external drive.

Due to some reason my laptop stopped working. So now I started working on a different laptop. This other laptop has Windows 7 (32 bit) installed on it. I installed Microsoft SQL Sever 2014 Management Studio and restored my database using the .bak file.

I have been working on this database for sometime now and now the problem is when I try to take backup of my database I get this error:

Can someone please tell me how to resolve this error and how can I again take backup on this Windows 32 bit laptop?

Thanks

Change your Backup command to something like this

BACKUP DATABASE MyDB
TO DISK = N'D:\MyDB.bak'
WITH INIT, NOUNLOAD,
     NAME = N'MyDB backup', STATS = 10, FORMAT

Notice the FORMAT at the end.

This should do it. You're changing NOFORMAT to FORMAT and remove NOSKIP


How to create a stored procedure with columns from three different tables in SQL Server 2008? I'm a fresher and I would like to get some suggestions on how to create a stored procedure in which I have to select columns from three different tables. 2 columns customername and customercode from first table, loan type from second table and account number, status from 3rd table.

SELECT
    C.customername
    ,C.customercode
    ,L.[loan type]
    ,S.[account number]
    ,S.status
FROM
    [first table] AS C
    INNER JOIN [second table] AS L
    ON C.customercode = L.customercode
    INNER JOIN  [3rd table] AS S
    ON S.customercode = C.customercode

This is only as a guidance; it is better to post some sample data and definitions.


Use non-printable characters in SQL Server BCP xml format file as TERMINATOR I have a flat-text data file containing records which fields are separated by the non printable character "File Separator" (0x1c). I am trying to use SQL Server's bcp utility to load this data into my database. Yet, when using the hex encoded value of the File Separator as TERMINATOR, I get a syntax error.

I have tried using

the hex encoded value : "0x1c"
the XML encoded value as hex: "&#x1c;"
the XML encoded value as decimal: "&#28;"

None of these work, yet when using the same encoding for a printable character, like tab, this does work: &#x09;, &#9; (0x9 doesn't. Not unsurprisingly, since this is an XML file.)

The conclusion seems to be that non-printable characters are not supported. Is this the case? That would be ironical, since the non-printable separator characters are created exactly for this purpose...

Below you can find all code to reproduce this issue:

XML format file: test.xml

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="&#x1c;" MAX_LENGTH="10" COLLATION="Latin1_General_CS_AS_WS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="41"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="COL1" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="COL2" xsi:type="SQLNUMERIC" PRECISION="4" SCALE="0"/>
 </ROW>
</BCPFORMAT>

Data file: test.txt
This is just one row, as a test case. StackExchange doesn't show the separator in the row below, yet when you click "Edit" for this post, the separator is included, and you should be able to copy-paste this.

1111111112008

Commandline

bcp TEST_DB.dbo.UL_TEST in "test.txt" -T -f "test.xml"

It seems, from the documentation about BCP - Specify Field and Row Terminators (SQL Server), that non-printable characters are not supported:

  Characters Supported As Terminators

  The bcp command, BULK INSERT statement, and the OPENROWSET bulk rowset
  provider support a variety of characters as field or row terminators
  and always look for the first instance of each terminator. The
  following table lists the supported characters for terminators.

As an option, you could use POWERSHELL to read in the source file and search\replace the 0x1C characters with some other character like a tilda (~) and output to a different file.  Then use that character as your terminator.

(Get-Content c:\test\test.txt) | ForEach-Object {$_ -replace [char]0x1C,'~'} | Set-Content c:\test\testout.txt


Missing mysqld.sock file on linux server I have this error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

Does anyone know what is wrong? It seems it isn't installing mysqld.sock when I run apt-get install mysql-server. I tried find / -name "mysqld.sock" but it can't find anything. How can I get mysqld.sock?

It should not install mysqld.sock. This file is a unix socket, it doesn't refer to a byte stream on your hard disk.

It is more like a TCP port: programs can listen here, and other programs can connect it to communicate with it. The only significant different is that TCP ports are identified by a number between 1 and 65535. Unix sockets are identified by their path, like /var/run/mysql/mysqld.sock.

mysqld.sock is created by the mysql daemon on start, and it is removed (or, becomes un-connectable) if this daemon stops.

This message is given by some client process, trying to connect your mysqld on this socket without success.

Its most likely reason is that the mysqld doesn't run.

Why it doesn't run? Try to start it (systemctl start mysqld) and check the /var/log/mysql* .


SQL server timeout after downgrade After a downgrade from SQL 2008 R2 Enterprise to SQL 2008 R2 Standard. SQL Server after some time starts lagging and CPU use is around 95%-100% and I can't log in in "MS SQL server management studio", another application gets time outs from SQL Server.

The log files don't have any error, but all files are filled with this text:

Log file:

https://drive.google.com/file/d/0B8niPQ9GLmG-ZHFuR3hqQUphMUU

The log seems to display an output of the DBCC MEMORYSTATUS, which is what happens when the engine runs out of memory. Did you configure the maximum amount of memory SQL Server can use ? To find out, you can run the following query :

SELECT  *
FROM    sys.configurations
WHERE   name LIKE '%mem%'

For the row for which the column named "name" is valued to "max server memory (MB)", you should see a value different greater than zero.

If it is set to zero, you can easily configure it with the following code, assuming that you server has a couple GB or RAM and is dedicated to SQL Server :

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

EXEC sp_configure 'max server memory (MB)', [your_server_RAM_amount_in_MB_minus_4GB]
GO
RECONFIGURE
GO

EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

This takes effect immediately, without the need to restart the SQL Server instance.

If it is not this, did you update all the statistics of all databases on this instance ?


Joining Two Rows (single table) to Single Row, Multiple columns Is there any way to JOIN two rows from a single table to a single row, based on two columns.

I built a scrappy little data base for my business, and I finally ran into a situation where I thought I had built in the necessary extensibility. Unfortunately, I found out pretty fast that I can't use the JOIN as I was before now that I need to join info from two columns to a single row (previously, I was only joining on one column).

Here is what I have:

| Name |    ID   |    ID_2 |
|----- |---------| --------|
| Jake |    a    |    b    |
| Jane |    b    |    c    |
| John |    c    |    NULL |

ID   | Info
-----------
a    |  1
b    |  3
c    |  5

And here is what I'm looking for:

Name | Info_1  |    Info_2
--------------------------
Jake |    1    |    3
Jane |    3    |    5
John |    5    |    NULL

Or, better yet, this:

Name | Info_Sum
---------------
Jake |    4
Jane |    8
John |    5

You Simply have to do a Two JOINs on the same Table

SELECT n.name, i1.info AS info_1, i2.info AS info_2
FROM names n
LEFT JOIN info i1 ON i1.ID = n.ID
LEFT JOIN info i2 ON i2.ID = n.ID_2

Just like here http://sqlfiddle.com/#!9/9f703e/1/0

For the Summed one, You have to use COLEACES to calculate the NULL values like the following

SELECT n.name, (COALESCE(i1.info, 0) + COALESCE(i2.info, 0)) AS Info_Sum
FROM names n
LEFT JOIN info i1 ON i1.ID = n.ID
LEFT JOIN info i2 ON i2.ID = n.ID_2

Just like here http://sqlfiddle.com/#!9/9f703e/3/0

In your next question I hope if you create the Table in SQLFiddle or dbfiddle and share it Or put the CREATE code so we can built the Table quickly to provide faster answers.


Always being logged in as SA and not Windows account First off i would like to appologise if i sound stupid as i do not have much knowladge of SQL Server.

I have recently started a project working with SQL Server 2008 R2. I have access to the database using Windows Authentication. Once logged in i can access all of the databases etc... My problem is, for my user DOMAIN/Glen i would like to add a default schema for one of the databases. I have changed the Default schema for 'DOMAIN\Glen' within the users section under the database in question however whenever i run a script without the schema prefix on the table it will not run.

I have been looking around and trying some things out and to my knowladge i am for some reason using the SA account and not the DOMAIN\Glen login.

If anyone could help me change my Loged in user from SA to DOMAIN\Glen that would be a great help.

Thanks

Boldonglen

EDIT

When i am accessing the database directly on the server it is logging me in as DOMAIN\Glen but when i am accessing it from my computer it is logging me in as SA.

EDIT 2

Also when i connect to SQL Server on the server normally i am getting the default schema of the one i have added however if i run SQL server on the server as an administrator i am getting the default schema of dbo.

Any Ideas?

DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed role. If DOMAIN\Glen is in the sysadmin role, the default schema is always dbo and you have to explicitly use the schema name.


How to get columns from system tables or views like sys.database_files (SQL Server 2012) How can I write a query to return all columns of, for example, the sys.database_files table? I see many suggestions for using information_schema.columns for getting table names for user databases/tables, but i can't figure out how to do this for system tables.

I see that the sys.database_files is a view under the master table. I'm using SQL Server 2012.

Since you are on SQL Server 2012, best is to use the new DMF sys.dm_exec_describe_first_result_set. This dmf will take a transact-SQL statement as a parameter and describes the metadata of the first result set for the statement.

so for example, you want to find out the columns names along with the datatypes and precison for sys.database_files, simply use

SELECT
    name,
    system_type_name,
    is_nullable
 -- add/remove columns as per your need ....
FROM
    sys.dm_exec_describe_first_result_set
    (
        N'SELECT * FROM sys.database_files;', NULL, 0
    ) AS f
ORDER BY
    column_ordinal;

Below are the results :

For versions below sql 2012:

SELECT object_NAME(c.object_id) AS OBJECT_NAME
    ,c.NAME AS COLUMN_NAME
    ,t.NAME AS DATA_TYPE
    ,c.max_length AS MAX_LENGTH
FROM sys.all_columns c
INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
WHERE object_name(c.object_id) = 'database_files'

Excellent post is written by Aaron Bertrand -- SQL Server v.Next (Denali) :Metadata enhancements which explains this in much details. Also, refer to the CAVEATS in the section down the article.

  I see many suggestions for using information_schema.columns for getting table names for user databases/tables.

Avoid using INFORMATION_SCHEMA views. Worth reading Aaron's post on The case against INFORMATION_SCHEMA views.


Schema review for system to advertise cars Ok, so I am carrying out a rather simple task as part of a job requiretement. Here is the task in a nutshell:

Design a form for creating ad for selling car
Form has 2 have:

2 drop downs:

Car make
Car model (car model has to be dynamic, based on selected model

textaera for short description
Atleast 10 checkboxes with various properties (air con, electric windows etc)

Database has to be implemented in a way, so it is easy to search

Here is EER for the design I have at the moment:

And for those that prefer SQL, you can find it here

So couple points I want to ask:

Initially I have thought to only have reference to "car_model" in "posts" table. Cause technically I could access the car make via the "car_model" table. But then decided that for search purposes it would be easier to have both keys in this table, correct decision?

Another point, is with properties. Obviously this is a many to many relationship (many posts, can have many properties), so a relationship table is an obvious choice. But as far as searching goes, this complicates a bit, cause now for example I am searching for particular model, with particular properties I need to look up 2 tables. Is there better way? I was thinking to have an extra "text" field in the "post" table with comma separated list with all property ids, and use a `LIKE %property%" query (in addition to the relationships table). Any better solution?

And finally, just in general, any other suggestions given the specification?

Both answer's really good an helpful, but accepted the xuma202 answer, as he needs some of that sweet rep points more at the moment :D

Initially I have thought to only have reference to "car_model" in "posts" table. Cause technically I could access the car make via the "car_model" table. But then decided that for search purposes it would be easier to have both keys in this table, correct decision?

No,
this adds redundancy to your database and this is something you generally do not want to have at all. It also does not fit with the 3rd Normal Form.

To read about Normal Forms look [here][1]

  Another point, is with properties.

It is no problem and very typical to have database querys across multiple tables. (This is basically why we use databases and have multiple table and not smash everything together) Do not worry about it and please do NOT add extra redundancy with such an extra text field.

  any other suggestions given the specification?

Yes, there is no need for the idproperties_post_rel column in your properties_post_rel table. You should make the both foreign keys the primary key so you can not accidentally set a property on one offer twice.
Also the relationship is well enough defined by the unique combination of these FKs so no need to waste space for an extra synthetic key.


What causes disk space growth from Informatica load onto SQL Server? We've been dealing with huge disk growth on SQL Server from Informatica.  After the load, the database grows to 2.4TB.  After the database shrink, it goes to 1.05TB.  What could likely cause this to happen?  What settings can we check in Informatica and/or SQL Server for our next run to troubleshoot this or do a guess/check?

EDIT:

There are two ways to move data using an Informatica mapping.  Using SQL Overrides (running straight SQL) using the SQL Transformation type or using the built in data streams with Informatica's out of the box functionality.  In this case, we are using data streams.  When data streams are used, straight SQL is still used, but Informatica creates the SQL code behind the scenes.  We are loading in 1,000,000 record increments.  I thought perhaps Informatica might be tellinig SQL Server to allocate disk space as it loads, but I'm not even sure what command(s) to look for if it did.

Check some of the following:

-Free space of DB in prod before backup.  You might just have lots of free space in prod pre-reserved.  It used to be a best practice before IFI was introduced.  You'll still want to pre-allocate.

-Transaction Log size post recovery but pre-shrink by running DBCC SQLPERF (LOGSPACE) both times.  Maybe the transaction log doesn't have open transactions post restore and you are now checkpoint/shrinking it.  This would mean you might not be taking prod tlog backups and your recovery model is FULL.

-List the sizes for all of your tables in order of size post recovery and then against after shrink, then compare.


Giving a session-local temporary name to a schema I have a schema foo_bar but also foo_x. During a particular session, is it possible to just set foo_bar to bar in PostgreSQL (8.4)?

I don't think you can. If you check the documentation on ALTER SCHEMA (PostgreSQL 8.4), there is not such a thing as ALTER SCHEMA TEMPORARILY or anything similar.
What you could actually do (per session), is change your search_path setting. Citing the documentation:

search_path (string)
This variable specifies the order in which schemas are searched when an object (table, data type, function, etc.) is referenced by a simple name with no schema component. When there are objects of identical names in different schemas, the one found first in the search path is used. An object that is not in any of the schemas in the search path can only be referenced by specifying its containing schema with a qualified (dotted) name.

Depending on your specific use-case (the need for a temporary change of schema name wasn't mentioned), this might be helpful.


Permission error when restoring MsSQL database with full text on Linux I'm trying to restore a .bak file created on Windows SQL Server to the Linux version of MSSQL, but I get an error like the following:
Msg 7610, Level 16, State 1, Line 112
Access is denied to "/var/opt/mssql/data/sysft_Catalog.ndf", or the path is invalid.

    Msg 3156, Level 16, State 50, Line 112
File 'sysft' cannot be restored to '/var/opt/mssql/data/sysft.ndf'. Use WITH MOVE to identify a valid location for the file.

    Msg 3119, Level 16, State 1, Line 112
Problems were identified while planning for the RESTORE statement. Previous messages provide details.

    Msg 3013, Level 16, State 1, Line 112
RESTORE DATABASE is terminating abnormally.

The command I'm running is the following (or one of many variants I've tried):
RESTORE DATABASE Imported_Database FROM
DISK = '/var/opt/mssql/backup/database_to_import.bak'
WITH
  MOVE 'Dat' TO '/var/opt/mssql/data/Dat.mdf',
  MOVE 'ftrow' TO '/var/opt/mssql/data/Catalog.ndf',
  MOVE 'Log' TO '/var/opt/mssql/data/Log.ldf',
  MOVE 'sysft_Catalog' TO '/var/opt/mssql/data/sysft_Catalog.ndf',
  STATS = 5 -- REPLACE, PARTIAL, etc.
GO

The database is hosted on Ubuntu 20.04.1 LTS in a container created by docker-compose with:
version: "3.2"
services:
  sql-server-db:
    container_name: mssql
    image: mcr.microsoft.com/mssql/server:2019-latest
    ports:
      - "1433:1433"
    environment:
      SA_PASSWORD: "{{ PASSWORD }}"
      ACCEPT_EULA: "Y"
    volumes:
      - ./imports:/var/opt/mssql/backup:ro

What I've noted is that:

The error message is incorrect, as the path of the restore is writeable (i.e. touch /var/opt/mssql/data/sysft_Catalog.ndf as user mssql works fine)
Other databases restore fine, notably if they don't have full text search
The .bak file does restore to MSSQL running on Windows
Importing, then re-exporting from Windows to a new backup doesn't solve the issue
Creating an empty database and restoring to that empty database doesn't help
Creating the files in advance doesn't help
The database would not restore to an Azure SQL Server ("unknown error") or to Google Cloud SQL (same)
The exact same failure occurs when MSSQL is installed directly on Ubuntu (i.e. without Docker)
The MSSQL errorlog seems to have no additional information
Copy Database from Windows to Linux in SSMS doesn't work
Installing mssql-server-fts doesn't help

Some links to seemingly related issues:

https://github.com/docker/for-win/issues/7259
https://support.microsoft.com/en-my/help/4057087/kb4057087-can-t-restore-database-with-replace-if-filepath-has-path-sep
Restore database in docker container

Brian told me on the chat:
"@FrancescoMantovani Importing the database, dropping the catalog, then re-exporting looks to have resolved the issue. Thank you so much for your help, this was very valuable."
This was the root cause:
https://support.microsoft.com/en-us/help/923355/error-message-when-you-perform-a-full-backup-of-a-database-in-sql-serv
@Brian, please add info if you need


Offline Index Rebuild on a Partitioned Table If I partition a table with ntext, text or image datatypes and rebuild an index on a single partition with online = off, does that lock the whole table or just the partition in question?

I had some time to look at this, and since I already had some of the demo scripts written, it was pretty easy to check on the rest. Let's do the setup, then look at the results. This will create the necessary partitioned table and index.

CREATE PARTITION FUNCTION YourMom ( INT )
    AS RANGE LEFT FOR VALUES ( 1000000, 2000000, 3000000, 4000000, 5000000 );

CREATE PARTITION SCHEME YourDad
    AS PARTITION YourMom
    ALL TO ( [PRIMARY] );

CREATE TABLE dbo.YourAuntDebbie
(
    Id INT,
    StopUsingDeprecatedDataTypes NTEXT
) ON YourDad (Id);

DECLARE @counter INT = 1;
WHILE @counter < 6
    BEGIN

        RAISERROR('Run number: %d', 0, 1, @counter) WITH NOWAIT;

        INSERT dbo.YourAuntDebbie WITH ( TABLOCK ) ( Id, StopUsingDeprecatedDataTypes )
        SELECT TOP 1000000 x.n + CASE WHEN @counter = 1 THEN 0
                                      WHEN @counter = 2 THEN 1000000
                                      WHEN @counter = 3 THEN 2000000
                                      WHEN @counter = 4 THEN 3000000
                                      WHEN @counter = 5 THEN 4000000
                                      ELSE 0
                                      END,
                           REPLICATE(N'A', x.n % 10000)
        FROM   (   SELECT ROW_NUMBER() OVER ( ORDER BY @@ROWCOUNT ) AS n
                   FROM   sys.messages AS m
                   CROSS JOIN sys.messages AS m2 ) AS x;

        SET @counter += 1;

    END;

CREATE CLUSTERED INDEX ix_whatever
    ON dbo.YourAuntDebbie ( Id ) ON YourDad(Id);

That gives me 5 partitions with 1 million rows, and one empty partition.

SELECT OBJECT_NAME(p.object_id) AS table_name, p.partition_number, p.rows
FROM   sys.partitions AS p
WHERE  p.object_id = OBJECT_ID('dbo.YourAuntDebbie');

Fancy table:

+----------------+------------------+---------+
|   table_name   | partition_number |  rows   |
+----------------+------------------+---------+
| YourAuntDebbie |                1 | 1000000 |
| YourAuntDebbie |                2 | 1000000 |
| YourAuntDebbie |                3 | 1000000 |
| YourAuntDebbie |                4 | 1000000 |
| YourAuntDebbie |                5 | 1000000 |
| YourAuntDebbie |                6 |       0 |
+----------------+------------------+---------+

Here's the XE session I'm using to see which locks the index rebuilds need:

CREATE EVENT SESSION Locks
    ON SERVER
    ADD EVENT sqlserver.lock_acquired
    ( SET collect_resource_description = ( 1 )
     ACTION ( sqlserver.sql_text )
     WHERE (   sqlserver.equal_i_sql_unicode_string(sqlserver.database_name, N'Crap')
               AND package0.equal_uint64(sqlserver.session_id, ( 61 )))),
    ADD EVENT sqlserver.lock_released
    ( SET collect_resource_description = ( 1 )
     ACTION ( sqlserver.sql_text )
     WHERE (   sqlserver.database_name = N'Crap'
               AND sqlserver.session_id = ( 61 )))
    ADD TARGET package0.event_file
    ( SET filename = N'c:\temp\Locks' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 30 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = ON,
           STARTUP_STATE = OFF );
GO

With that in place, I can rebuild my partitions, and then dig into XE.

ALTER EVENT SESSION Locks ON SERVER STATE = START;

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 1 WITH (ONLINE = OFF);
GO

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 2 WITH (ONLINE = OFF);
GO

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 3 WITH (ONLINE = OFF);
GO

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 4 WITH (ONLINE = OFF);
GO

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 5 WITH (ONLINE = OFF);
GO

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 6 WITH (ONLINE = OFF);
GO

ALTER EVENT SESSION Locks ON SERVER STATE = STOP;

Now, I'm gonna put the XE event shredding stuff at the end, because it's pretty ugly, and there's no reason to make everyone sit through that to see the results. I'm going to use the first partition's results as an example, but they're just about identical across all 6 partitions, even the empty one.

I'm limiting the results of the update to only the object level locks. These are the only ones we care about.

   +---------------+-------------------------+----------------+--------------+-------+------------------+--------+
|   EventName   |        EventDate        |   ObjectName   | ResourceType | Mode  | PARTITIONREBUILT | Events |
+---------------+-------------------------+----------------+--------------+-------+------------------+--------+
| lock_acquired | 2017-10-03 13:21:14.554 | YourAuntDebbie | OBJECT       | SCH_M | PARTITION = 1    |      1 |
| lock_acquired | 2017-10-03 13:21:14.554 | YourAuntDebbie | OBJECT       | SCH_S | PARTITION = 1    |      1 |
| lock_released | 2017-10-03 13:21:14.554 | YourAuntDebbie | OBJECT       | SCH_S | PARTITION = 1    |      1 |
| lock_acquired | 2017-10-03 13:21:14.603 | YourAuntDebbie | OBJECT       | S     | PARTITION = 1    |      6 |
| lock_acquired | 2017-10-03 13:21:14.603 | YourAuntDebbie | OBJECT       | SCH_S | PARTITION = 1    |     30 |
| lock_released | 2017-10-03 13:21:14.603 | YourAuntDebbie | OBJECT       | SCH_S | PARTITION = 1    |     24 |
| lock_released | 2017-10-03 13:21:14.867 | YourAuntDebbie | OBJECT       | SCH_M | PARTITION = 1    |      1 |
+---------------+-------------------------+----------------+--------------+-------+------------------+--------+

From what I can tell, for each partition, a SCH-M lock  is taken out at the beginning of the index rebuild, and released at the end ON THE TABLE.

For example, if I run a single partition rebuild in a transaction:

BEGIN TRAN

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 1 WITH (ONLINE = OFF);

--ROLLBACK

And then in another SSMS window:

SELECT *
FROM dbo.YourAuntDebbie AS yad
WHERE yad.Id = 6000000
AND 1 = (SELECT 1)

The select is blocked until I kill the rebuild. When it finishes, the query plan shows partition elimination occurring, so only partition 1 being rebuilt appears to effect the entire table.

Hope this helps!

Now here's the awful XE session shredding code:

    CREATE TABLE #Locks
       (
         ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
         WaitsXML XML
       );

INSERT  #Locks
        ( WaitsXML )
SELECT    CONVERT(XML, event_data) AS TargetData
FROM      sys.fn_xe_file_target_read_file( 'c:\temp\Locks*.xel', NULL, NULL, NULL);

WITH locks
AS ( SELECT l.WaitsXML.value('(/event/@name)[1]', 'VARCHAR(128)') AS EventName,
            l.WaitsXML.value('(/event/@timestamp)[1]', 'DATETIME2(3)') AS EventDate,
            l.WaitsXML.value('(event/data[@name="object_id"]/value)[1]', 'NUMERIC') AS ObjectId,
            l.WaitsXML.value('(event/data[@name="resource_type"]/text)[1]', 'VARCHAR(128)') AS ResourceType,
            l.WaitsXML.value('(event/data[@name="mode"]/text)[1]', 'VARCHAR(128)') AS Mode,
            l.WaitsXML.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(128)') AS SQLText,
            l.WaitsXML
     FROM   #Locks AS l )
SELECT   locks.EventName,
         locks.EventDate,
         ISNULL(OBJECT_NAME(locks.ObjectId), 'Unknown') AS ObjectName,
         locks.ResourceType,
         locks.Mode,
         SUBSTRING(
             locks.SQLText,
             CHARINDEX('PARTITION', locks.SQLText),
             CHARINDEX('WITH', locks.SQLText, CHARINDEX('PARTITION', locks.SQLText))
             - CHARINDEX('PARTITION', locks.SQLText)) AS PARTITIONREBUILT,
         COUNT(*) AS Events
FROM     locks
WHERE    OBJECT_NAME(locks.ObjectId) = 'YourAuntDebbie'
         --OR OBJECT_NAME(locks.ObjectId) IS NULL
GROUP BY ISNULL(OBJECT_NAME(locks.ObjectId), 'Unknown'),
         SUBSTRING(
             locks.SQLText,
             CHARINDEX('PARTITION', locks.SQLText),
             CHARINDEX('WITH', locks.SQLText, CHARINDEX('PARTITION', locks.SQLText))
             - CHARINDEX('PARTITION', locks.SQLText)),
         locks.EventName,
         locks.EventDate,
         locks.ResourceType,
         locks.Mode
ORDER BY locks.EventDate;


Why does CAST(1 AS SIGNED INTEGER) return a BIGINT on MySQL? If I do CAST(1 AS SIGNED INTEGER) I always end up getting a BIGINT returned, for example:

$ mysql -u root -p --column-type-info
Enter password:

--- Copyright and help message snipped for brevity ---

mysql> select cast(1 as signed integer);
Field   1:  `cast(1 as signed integer)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG            <== LONGLONG i.e. 64 bit integer
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM

+---------------------------+
| cast(1 as signed integer) |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

I would have expected the return type from that cast to be a LONG (32 bit integer).

If I select a column from a table that has an INT I see that it is indeed just a LONG:

mysql> describe contact;

+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| contact_id | int(11) | NO   | PRI | NULL    | auto_increment |

      == remainder of table snipped ==

mysql> select contact_id from contact where contact_id = 20;
Field   1:  `contact_id`
Catalog:    `def`
Database:   `centreon`
Table:      `contact`
Org_table:  `contact`
Type:       LONG                     <== LONG i.e. 32 bit integer
Collation:  binary (63)
Length:     11
Max_length: 2
Decimals:   0
Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY

+------------+
| contact_id |
+------------+
|         20 |
+------------+
1 row in set (0.00 sec)

mysql>

If I cast the same column to a signed integer, I again get a 64 bit integer returned:

mysql> select CAST(contact_id as signed integer) from contact where contact_id = 20;
Field   1:  `CAST(contact_id as signed integer)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     11
Max_length: 2
Decimals:   0
Flags:      NOT_NULL BINARY NUM

+------------------------------------+
| CAST(contact_id as signed integer) |
+------------------------------------+
|                                 20 |
+------------------------------------+
1 row in set (0.00 sec)

There is a similarly reported issue here:

  http://bugs.mysql.com/bug.php?id=64084

But sadly the OP doesn't get a straight answer.

Is this a bug in the CAST() function or is this by design?

As you can see on https://www.w3schools.com/sql/func_mysql_cast.asp

SIGNED    Converts value to SIGNED (a signed 64-bit integer)
UNSIGNED  Converts value to UNSIGNED (an unsigned 64-bit integer)

And from the official MySQL documentation: https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html

MySQL supports arithmetic with both signed and unsigned 64-bit values. For numeric operators (such as + or -) where one of the operands is an unsigned integer, the result is unsigned by default (see Section 12.6.1, “Arithmetic Operators”). To override this, use the SIGNED or UNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer, respectively.

So looks like the CAST output will actually be a 64 bits integer when you use SIGNED or UNSIGNED datatypes.


Update to MySQL 5.7 and high disk space consumption Using: MySQL 5.6 on Debian 9, total DB size is around 450GB

Updated to 5.7, ran mysql_upgrade, noticed that around 150 GB has been taken up. 2 tables are really large and they stayed in 'copying to tmp table' for a couple of hours

Noticed innodb_file_per_table was on and created large ibd files that weren't there previously.

Restored from a snapshot, disabled the file_per_table, ran mysql_upgrade again. 100GB gone, which is almost 1/4 of my total DB.

In the first case, it pulled the data from ibdata and put it into a separate file but ibdata never shrinks so taken space almost doubled.

What happens in the second case? Does the temp table get created within the ibdata file that never shrinks so even when table is not used anymore - space is still gone?

Another thing I noticed is that space consumption doesn't start until query has been in copying to tmp table status for like an hour or so.

1) Is there any way to avoid/minimize space increase?

Would running update with file_per_table on, then disabling it and running alter table engine innodb free up the space?

2) Any way to predict how much space will be occupied? At least per table

ibdata1 never shrinks.
.ibd files never shrink.
When a table is in ibdata1 and is ALTERed, space in that file is temporarily consumed, thereby possible stretching the file.
With .ibd, an ALTER will probably create a new copy, then toss the old one, thereby temporarily using diskspace, then releasing it.
There are too many variables to make any predictions.  But a fairly safe rule is to never fill up more than half the disk.

The best thing going forward is rather painful:

Dump all the tables
Toss ibdata1 (or make a fresh install)
Turn on innodb_file_per_table
Reload the dump file.

That will

leave a small ibdata1 file, which is necessary for meta data, etc.
Create each table in its own .ibd file.
Allow future ALTERs to only temporarily consume extra disk space.

Another thing to note:  innodb_file_per_table (ON or OFF) applies only to tables CREATEd or ALTERed after that.  All existing tables say either in ibdata1 or .ibd until such an action is taken.  Correlary:  It is possible to have some tables in ibdata1 and some in separate .ibd files.  It may be beneficial to put small tables in ibdata1 (OFF) and large ones in .ibd files (ON).


How could I improve my DB design? I'm a second year software engineering major looking to create an iOS app that connects to a MySQL database (database not yet created) over the summer. I have a strong understanding of programming and object-oriented design, but have never taken a course in relational databases and don't know much about them at all. I could whip up an OO class diagram for this project in a matter of minutes but I can't seem to map that design to a relational database. This stuff is trickier than I thought.

Background Info About App: Well it's not incredibly complicated. The idea is to make an app that allows skateboarders to upload skateparks, write reviews on them, and discover new parks based on location, user reviews, photos, and other park characteristics. Users sign up for accounts within the app and get a username and password. They can search for nearby parks and filter them. They can bookmark their favorite parks. They can flag both parks and park photos if deemed inappropriate or inaccurate. Park reviews can be voted on as helpful or unhelpful. It's basically just a directory of skateparks w/ reviews and photos consolidated into an iPhone app.

Below is an image of my ERD at this moment in time. I'd appreciate it if you could pinpoint any flaws in the design (sorry for being so vague).

Specific areas I'm struggling with:

Do my primary and foreign keys make sense? Should I have an auto-increment ID for each table or is that not necessary?
Users need to filter parks by: distance, average rating, popularity (number of ratings and photos), free/paid admission, and types of riders allowed in the park (skateboarder, biker, inline skater, or any combo). Does the way my DB is currently layed out support efficient filtering of parks by these attributes?

Notes:

Just pretend that I have a fully functional user database and user_id works to identify unique users. I'm more focused on the park design than the user management design right now.
Ignore the relationships between entities. I don't really understand crow's notation at the moment..

Thanks so much!

This might not be the answer but a general tip for designing is to remember that for your initial logical design. A logical relational DB schema is only made up of one-to-many relations. e.g. One Park has many Photos and One User has many photos. That allows for a many-to-many such as; A user may log photos for many parks and a park may have photos logged by many users.  These are called business rules. You want to always go for just one-to-many relations in your model of the business rules.  Later you might add a few one-to-one entities but rarely.  As for many-to-many , just exclude them from your model and represent them as one-to-many-to-one ( i.e. as two one-to-many relations). Which you have done.

Your schema design looks fairly close to reasonable but you have included some one-to-one relations. They are normally not part of a new schema design. They are basically extension tables/entities and done as a way to optimise performance in cases where some fields are rarely used.  For now, I would exclude modelling them.  But actually in your particular model, I think you just made a mistake with the notation.  So you probably want to keep those entities but correct the relation types.  e.g. Park-to-Favorite Park should be one-to-many ( 1:M ) .  The Park Type to Park should be either 1:M or M:M ( via two 1:M ) , depending on your business rule.  Can a Park be of more than one type; I expect a ParkType can be used by several Parks. If so then the relation is 1:M ( ParkType:Park ). If a Park can also be of several types then you'll need another entity and create the relation 1:M:1 via ParkType:ParkTypePark:Park .
Now, location is tricky.  Example data would help, as you list Address, long, lat as fields I assume that a location record is only usable by exactly one Park.  If location is like a region where many Parks can be associated with one Regional Location then the entity makes sense to me (as a 1 Location: M Parks relation ) . But if the Location is very precise so only one Park can ever be there, then you don't need the Location entity, store that data in the Park entity.  However, for optimisation you may put it back in if you think that searching for Parks in a Location area would be faster using a separate table with only that in it.  But I doubt you need to do that.

Not sure what ParkSubmission is, I'm guessing it is suggestions for new parks. If so, possibly you don't need it and can instead have a status flag on the Park record to show that it is a Suggested, Draft, Approved, Rejected, etc.


Grouping on an expression (and possible HAVE) I have the following query, which I rewrote as a 'simple' example below.

The expression CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c is just a weird example of a complex function.

I am questioning myself:
 Why do I have to put this complex expression 3 times in my query?

DROP TABLE #TEMP
CREATE TABLE #TEMP (a int, b int, c int)

INSERT INTO #TEMP VALUES( 1,1,1)
INSERT INTO #TEMP VALUES( 1,1,2)
INSERT INTO #TEMP VALUES( 1,1,3)
INSERT INTO #TEMP VALUES( 1,2,1)
INSERT INTO #TEMP VALUES( 1,2,2)
INSERT INTO #TEMP VALUES( 1,2,3)
INSERT INTO #TEMP VALUES( 2,1,1)
INSERT INTO #TEMP VALUES( 2,1,2)
INSERT INTO #TEMP VALUES( 2,1,3)
INSERT INTO #TEMP VALUES( 2,2,1)
INSERT INTO #TEMP VALUES( 2,2,2)
INSERT INTO #TEMP VALUES( 2,2,3)

SELECT
    a,
    b,
    CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c as d
from #TEMP
GROUP BY a, b, CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c
HAVING CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c >= 0
ORDER BY 3 DESC

The question is mainly for using in SQL Server, but answers for other database management systems are welcome too.

You don't have to.  You can just rewrite it to CTE/inline view , and refer by alias :

SELECT t.* FROM
(
SELECT
    a,
    b,
    CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c as d
from #TEMP
)t
GROUP BY a,b,d
HAVING d > 0
ORDER BY 3 ;

or

WITH cte1 AS
(
SELECT
    a,
    b,
    CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c as d
from #TEMP
)
select t.*
from cte1 t
group by t.a,t.b,t.d
having t.d > 0
order by 3;

Also, there is no need for  GROUP BY( and HAVING can be replaced by WHERE)  in this particular case - SELECT DISTINCT will do the job.


What privileges do I have to grant to allow a user to select from information_schema.table_privileges? I'm using MS SQL Server 2008, and I am trying to get a complete listing of all privileges assigned to all grantees by running this query:

select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES

However, right now I'm only getting back records associated with the "public" grantee. I've tried adding the db_owner role to this user account, and I've also run this query within the database:

GRANT VIEW DEFINITION TO myuser

Neither of these actions has changed the response from the above query - still only getting public grantee records. What is the minimum setting I need to do to grant the user the rights to all the records?

Thanks

This information_schema view (my bold)

  Returns one row for each table privilege that is granted to or granted by the current user in the current database.

This doesn't take into account roles etc: it all works on DATABASE_PRINCIPAL_ID which can be seen if you look at the definition of INFORMATION_SCHEMA.TABLE_PRIVILEGES

Personally, I'd just use sys.database_permissions and not bother with the information_schema rubbish. This relies solely on Meta data visibility to filter rows, so you'll see the actual permissions unfiltered by the INFORMATION_SCHEMA.TABLE_PRIVILEGES view which adds a further filter that bollixes you.

For completeness, here is the view. Note the implicit "old style" JOIN :-)

CREATE VIEW INFORMATION_SCHEMA.TABLE_PRIVILEGES
AS
SELECT
    USER_NAME(p.grantor_principal_id)   AS GRANTOR,
    USER_NAME(p.grantee_principal_id)   AS GRANTEE,
    DB_NAME()                       AS TABLE_CATALOG,
    SCHEMA_NAME(o.schema_id)            AS TABLE_SCHEMA,
    o.name                          AS TABLE_NAME,
    convert(varchar(10), CASE p.type
        WHEN 'RF' THEN 'REFERENCES'
        WHEN 'SL' THEN 'SELECT'
        WHEN 'IN' THEN 'INSERT'
        WHEN 'DL' THEN 'DELETE'
        WHEN 'UP' THEN 'UPDATE'
        END)                        AS PRIVILEGE_TYPE,
    convert(varchar(3), CASE p.state
        WHEN 'G' THEN 'NO'
        WHEN 'W' THEN 'YES'
        END)                        AS IS_GRANTABLE
FROM
    sys.objects o,
    sys.database_permissions p
WHERE
    o.type IN ('U', 'V')
    AND p.class = 1
    AND p.major_id = o.object_id
    AND p.minor_id = 0  -- all columns
    AND p.type IN ('RF','IN','SL','UP','DL')
    AND p.state IN ('W','G')
    AND (p.grantee_principal_id = 0
        OR p.grantee_principal_id = DATABASE_PRINCIPAL_ID()
        OR p.grantor_principal_id = DATABASE_PRINCIPAL_ID())


SQL Server 2012 Standard Edition using more than 64GB of memory, why and how? I put SQL Server 2012 Standard on a virtual machine (VMWare) which has roughly 98GB of Memory.

Now I checked the query below how much memory is taken by SQL Server.

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)

And see that he ate already 81GB of the memory and the max Memory is set to ~90GB.

Using:

select (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 ) VAS from sys. dm_os_process_memory

SQL Server 2012 Standard should support only 64GB of memory.

Why does my SQL Server eat more than he can? Or is the info incorrect?

Any idea? Is that related to a virtual machine using virtual memory?

I don't know if balloon driver is activated on VM Host...

If you need any more info I will add it.

The 64GB limit (and 128GB in 2014) applies only to buffer pool. The KB article "Memory configuration and sizing considerations in SQL Server 2012" says:

  Starting with SQL Server 2012, these memory limits are enforced only for the database cache (buffer pool).

And as we all know, max server memory also does not control all of SQL Server's memory. The official documentation is in the process of being updated to provide the following guidance on this (this exact wording was provided by Bob Ward):

  The server-level max server memory setting controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and clr memory (basically any “clerk” as found in sys.dm_os_memory_clerks). Memory for thread stacks, memory heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.

So, it is quite feasible that you will see sqlservr.exe use more than 64GB of memory if there is significant allocation to anything other than the buffer pool, even in Standard Edition (and more than 1GB even in Express Edition).

If you want to stop SQL Server from using more than 64GB of memory, you'll need to set max server memory to something less than 64GB (you may need to experiment here to find the right mix, but understand that the non-buffer pool memory can fluctuate, so you may still see things that venture beyond both the licensing limit and the configured limit).

Or, you can just leave it as is, and be thankful that you can use up to 64GB for buffer pool and plenty else for other things that might otherwise cut into buffer pool usage if the 64GB limit were an enforced hard cap.

You can see how memory is distributed in various ways. Among the memory clerks, you can use:

SELECT [type], mem_MB = pages_kb/1024
 FROM sys.dm_os_memory_clerks
 ORDER BY pages_kb DESC;

In most cases, the top consumer is going to be MEMORYCLERK_SQLBUFFERPOOL, but you should see significant volume in CACHESTORE_* entries as well. You can group it at a higher level using something like:

SELECT [type] = COALESCE(SUBSTRING([type],1,CHARINDEX('_',[type])-1),'-- total'),
  SUM(pages_kb/1024)
FROM sys.dm_os_memory_clerks
GROUP BY GROUPING SETS((),SUBSTRING([type],1,CHARINDEX('_',[type])-1));

To see how cache/user store memory is distributed:

SELECT [type], name,
 mem_MB = CONVERT(DECIMAL(19,2),pages_kb/1024.0)
 FROM sys.dm_os_memory_cache_counters
 ORDER BY pages_kb DESC;

Currently, on my lowly VM, the query in your question (provided by @Shanky) says that SQL Server is using 2,085 MB of memory (with 2,004 MB locked in memory). However my query above against the memory clerks DMV says that the total memory being used there is only 1,793 MB.

The query in Chris' answer says that the total is 2,054 MB, with a target of 3,072 MB (this is because I have explicitly set max server memory to 3,072 MB).

So where is the extra ~200 MB? Who knows. (I can also see this difference if I compare total server memory to Process > sqlservr > Private Bytes in Performance Monitor.) I could start parsing the output of DBCC MEMORYSTATUS to try and piece it together (and Tim Chapman wrote some PowerShell to make that easier), but is it worth it? Is all memory even reported there? I'm not 100% certain that it is (or that you should use the output at all in a NUMA system), or that any DMV can give a fully accurate picture of all of the memory being used by SQL Server. I also don't believe all the memory clerk types are even documented anywhere - so even if that gave a complete picture, you might still be scratching your head over what the results really mean.

Task Manager, meanwhile, says sqlservr.exe is only using 42 MB. Whatever you do, please stop using Task Manager. For anything, really. I know you have removed this detail from your question in your most recent edit, but I can't stress enough how useless it is for showing SQL Server memory usage.


(How) does table inheritance interfere with locking in postgres? I have a table inheritance setup that might be simplified like this:

CREATE TABLE p (
    id BIGSERIAL PRIMARY KEY,
    type_id BIGINT,
    approved BOOLEAN
);

CREATE INDEX ON p(approved);

CREATE TABLE a (
    a_field VARCHAR,
    PRIMARY KEY (id),
    CHECK(type_id = 1::BIGINT)
) INHERITS (p);

CREATE TABLE b (
    b_field INT,
    PRIMARY KEY (id),
    CHECK(type_id = 2::BIGINT)
) INHERITS (p);

CREATE INDEX ON b(approved);

CREATE TABLE c (
    c_field NUMERIC,
    PRIMARY KEY (id)
    -- this table is missing the check constraint (for no good reason)
) INHERITS (p);

I now have a long-ish running transaction that locks table a. While this is running, I observe queries on table b that wait for that lock to be released, even though they are guaranteed to not use the data in a. For example:

SELECT TRUE FROM p WHERE id = 12345 AND approved = false AND type_id = 2 LIMIT 1;

Running EXPLAIN on that query shows something like this:

                              QUERY PLAN
----------------------------------------------------------------------------
Limit  (cost= ...)
    ->  Result  (cost= ...)
       ->  Append  (cost= ...)
         ->  Seq Scan on p  (cost= ...)
                 Filter: ((NOT approved) AND (id = 12345) AND (type_id = 2))
         ->  Index Scan using b_approved on b p  (cost= ...)
                 Index Cond: (approved = false)
                 Filter: ((NOT approved) AND (id = 12345) AND (type_id = 2))

I am not sure if I'm reading that correctly, but it appears to me that the query planner thinks it needs to go through the parent table because it does not know whether table c might contain the rows it's looking for. In reality, there's more than one table (in fact, a couple of dozens) like c, which lack the CHECK constraint, so it seems plausible to me that the planner thinks a Seq Scan on the parent might be the most efficient route.

Will I avoid locks on a interfering with an unrelated (in terms of data) query on b if I add proper check constraints to c (the c-like tables all could get one, it would be some work, though)?

Are there other remarks on this kind of setup with regard to locking?

Note that one of the reasons why this partitioning exists is that the application running the queries has parts that are agnostic to what child table it is dealing with, hence the query shown goes to p. Additionally, such a query might affect a small number of tables (3-5) instead of just b, i. e., the query plan shows additional Index Scan lines like the one for b shown here.

Apparently, you want to see constraint exclusion at doing its work.  As in similar setups the parent table has no constraint (and, just to mention it, usually no rows), it will be always visited by queries on the parent table.  Using your example schema, see the output:

test=# EXPLAIN SELECT count(1) FROM p;
                            QUERY PLAN
───────────────────────────────────────────────────────────────────
 Aggregate  (cost=576.00..576.01 rows=1 width=0)
   ->  Append  (cost=0.00..501.00 rows=30001 width=0)
         ->  Seq Scan on p  (cost=0.00..0.00 rows=1 width=0)
         ->  Seq Scan on a  (cost=0.00..164.00 rows=10000 width=0)
         ->  Seq Scan on b  (cost=0.00..164.00 rows=10000 width=0)
         ->  Seq Scan on c  (cost=0.00..173.00 rows=10000 width=0)

test=# EXPLAIN SELECT count(1) FROM p WHERE type_id = 1;
                            QUERY PLAN
───────────────────────────────────────────────────────────────────
 Aggregate  (cost=412.00..412.01 rows=1 width=0)
   ->  Append  (cost=0.00..387.00 rows=10002 width=0)
         ->  Seq Scan on p  (cost=0.00..0.00 rows=1 width=0)
               Filter: (type_id = 1)
         ->  Seq Scan on a  (cost=0.00..189.00 rows=10000 width=0)
               Filter: (type_id = 1)
         ->  Seq Scan on c  (cost=0.00..198.00 rows=1 width=0)
               Filter: (type_id = 1)

In the second case, table b is not visited, because the planner knows for sure that there cannot be any rows matching the condition.  At the same time, p is present in both cases.  The same applies to c, too: it has no easy way (the check) to exclude the table from the plan.  In this case, it has to visit all tables that have the possibility to contain the actual rows.

Let's add some check there, too:

ALTER TABLE c ADD CHECK (type_id = 3);

test=# EXPLAIN SELECT count(1) FROM p WHERE type_id = 1;
                            QUERY PLAN
───────────────────────────────────────────────────────────────────
 Aggregate  (cost=214.00..214.01 rows=1 width=0)
   ->  Append  (cost=0.00..189.00 rows=10001 width=0)
         ->  Seq Scan on p  (cost=0.00..0.00 rows=1 width=0)
               Filter: (type_id = 1)
         ->  Seq Scan on a  (cost=0.00..189.00 rows=10000 width=0)
               Filter: (type_id = 1)

Much better now.

Now let's have a look on the locks that are taken.

BEGIN; -- to be able to see the locks

SELECT count(1) FROM p WHERE type_id = 1;

SELECT relname, locktype, mode
  FROM pg_locks
  JOIN pg_class c ON relation = c.oid
  JOIN pg_namespace n ON c.relnamespace = n.oid
 WHERE nspname = 'dba'
   AND relkind = 'r';

 relname │ locktype │      mode
─────────┼──────────┼─────────────────
 c       │ relation │ AccessShareLock
 b       │ relation │ AccessShareLock
 a       │ relation │ AccessShareLock
 p       │ relation │ AccessShareLock

This is not so nice...  Apparently (and somewhat surprisingly), going to the parent table locks all children, too.

But:

ROLLBACK;
BEGIN; -- to be able to see the locks

SELECT count(1) FROM a WHERE type_id = 1; -- touching only "a"

-- the locks taken:
 a       │ relation │ AccessShareLock

This means that if you can constraint your query to touch only a subset of the tables, it will save you the locks on the other children.  Playing around a bit it shows that this applies to other statements (for example, an UPDATE), too, just the lock modes are different.