How can I define and declare a good PRIMARY KEY for my dataset? I have a large historical dataset that has the following attributes:

productCode: Alphabetic code used to identify a product name, e.g., 'LE' (limited edition), 'LI' (limited inclusion)
productMonth: The month of the product manufacturing (multiple products can be made per month)
productPrice: the price level for a particular product
dateSold: the day the product was sold

Note: A productCode can have multiple productMonths and multiple productPrice levels. For example, for a productCode 'LE':

Note 2: Once a product (e.g., 'LE', 'Mar 16') is sold at a certain priceLevel (e.g., '2.0') on a certain day, it is no longer available at that priceLevel for the rest of such day. It will be available at the priceLevel again the next day.

This data will be pulled for analysis and the database will also be updated daily. As a beginner I want to make sure I employ best practices. From my research, I have been told that every table should have a primary key. In my case (my dataset below), I'm not sure how to best construct one.

Sample data

In order to exemplify the pieces of information in tabular form, I have provided the following sample data:

 productCode dateSold productMonth price randomData
 ----------- -------- ------------ ----- -----------
 LI            Dec 16       Mar 16  1.65        0.05
 LE            Dec 16       Mar 16  1.65        0.05
 LE            Nov 16       Mar 16  1.65        0.04
 LE            Nov 16       Apr 16  2.00        0.03
 LE            Nov 16       Apr 16  5.00        0.01

Considerations

I believe I need a PRIMARY KEY (PK) for the case of UPDATE/INSERT into the database, I'd only want to INSERT to the database if there wasn't an existing row already (having duplicate rows would skew analysis of data). I believe a PK can help identify duplicates if I construct it based on the existing data and check as I INSERT into the database.

A unique ID can be made for every item if I combine the productCode, dateSold, productMonth and price columns. This is because once the product (productcode, productmonth, price) is sold on a particular day (dateSold), it can not be sold again for the rest of the day.

However, I'm not sure if this is the proper way of building a PK or if there is a better key for my particular dataset. Any guidance would be greatly appreciated.

I've asked some questions in the comments, but in general:

If you have a combination of columns that would make a row unique according to the business rules of the data, you can add those columns to a unique index/constraint and the DBMS will take care of no duplicates.  Concatenating the columns and putting them in a PK would not add any more uniqueness and it would impact performance in a negative way by taking up more space and potentially being a wider than needed composite key.


Number of tables in join and query performance Recently I came across a standards in my project which strangely asserts that we should refrain from joining more five tables in SQL query. More than five tables in a query leads to performance degradation. Even though I strongly disagree with such assertion (regardless it's SQL Server or Oracle) I was wondering is there any suggestion as to how many tables we should use in join in SQL Server or Oracle. Does it depends on the database type (SQL Server Vs Oracle Vs MySQL) or number of rows in the tables, indexing, INNER Vs OUTER Join, cardinality of the tables joined, etc.? My understanding is since SQL is not a procedural language we should try to add more tables in JOIN provided that we are using the right columns for joining.

More than five tables in a query leads to performance degradation.

A join will increases the cost of running a query to some degree no matter what.  But what's the right thing to do ultimately depends on what's ideal or acceptable for any given project.

For example.  I have a 1-column table that contains 1 million row, whose only column is an ordered bigint clustered index. Make 10 queries ranging from no join to 10 joins, and compare execution plan and execution time.  You'll see the increased cost and time pretty much in a linear manner.  In my personal environment, SELECT * with no join might take 5 seconds to complete while 6 joins might take 6 seconds.  What if the table had only 10,000 records?  It will run significantly faster even if the number of joins is doubled (in my environment <200ms).

Now, if the only way to get the right data set is by joining 10 tables and it takes 5 seconds, is that good or bad?  If the speed is critical to your application, like high-speed stock trading, then you might want to reconsider.  What if it was a report for sales department?

  Does it depends on the database type (SQL Server Vs Oracle Vs MySQL)
  or number of rows in the tables, indexing, INNER Vs OUTER Join,
  cardinality of the tables joined, etc.?

Database product, yes and no.  Each products have different optimization logic.  So one might not execute a query the same as others.  One might be faster than others in some cases and vice versa.  Everything else you mentioned affects performance, including selectivity.  OUTER can be slower than INNER.  There are occasions where OUTER joins can be faster or costs less.

Of course, my very simple example is far from any real-life scenario but the underlying logic is the same.  How many you should join depends on your environment and your requirements.  Things you mentioned will affect the performance, and if the query is running below acceptable level or started having a performance issue, you should examine them.  But none of them are relevant to how many joins you SHOULD have.


A bit confused on how mongo and mongod load configuration files I'm new to MongoDB and was working on getting my first replica set up and running using the MongoDB Cloud Manager.  However, the server I deployed to already had mongodb installed.

I currently have everything seemingly working, including SSL and authentication, but I'm wondering how mongodb decides what config files to use and which mongod to run.

When I used the deploy option in Cloud Manager to my remote server, it had me set up an automation agent, which included its own mongo and mongod executables.  I would have thought I needed to use these to connect to my database, but regardless of what mongo or mongods I'm running (I've tried all 4 combos of the automation agent's and the preinstalled ones in /usr/bin), as long as I type:

mongo --host localhost:port#

and specify the port number for the primary in my replica set, I connect with the proper configuration settings.  I've noticed the replica set has an automation_agent.conf file on the primary, but how does merely specifying the port # tell mongo to use that configuration file?

On a somewhat related note (I was trying some command line diagnostics in the mongo shell to figure out the configuration behavior), is the normal process to log in once you've set up authentication to use the admin database, and then authenticate in the mongo shell? Am I overcomplicating things?

Sorry if these questions seem a bit scattered, I'm just unsure I'm not shooting myself in the foot later by not uninstalling the old mongo and mongod executables.

When you are using automation agent, configuration is done at OPS manager (or cloud front) and there is not much what you can do at actual machine. Yes, in this case, automation agents config file includes information of port and other parameters.

When you start locally installed mongod (f.ex. systemctl start mongod), it is set at (systemctl) mongod.service file that startup command is mongod -f /etc/mongod.conf, so config file is /etc/mongod.conf where all startup parameters has been defined.


MS Access- Attachments to SQL Server -Images or Varchar(max) I am trying to move a database from MS Access to SQL Server 2014 and I am using SSMA for Access. A few of the tables have attachments (pdfs, jpgs, emails, etc.).

When I move it to SQL Server, the attachments show up as File Names:

Is there anyway to do this from MS Access side or SQL Server side?

I downloaded all the files to my system and was thinking of matching the names with the files in the folder to upload them to the database through SQL, but then there are multiple files for one row in some instances, so basically I'm not sure how to go about it.

If the files was in the same sql server you can you the  OPENROWSET like this

Select * from Openrowset(Bulk 'C:\HaxLogs.txt', SINGLE_BLOB) as a

Or in the remote server

Select * from Openrowset(Bulk '\\server\backup\CSVcontagem_SEND_Akarollynegs.csv', SINGLE_BLOB) as a


mysql 5.1 -- unknown variable 'basedir=c:/mysql/' I recently changed the default port(3306) to another for mysql.
I did it by changing the my.ini file and did restart of the server.
No other changes were made whatsoever.
When connecting using a GUI no problem.
When I try using command prompt (mysql -u root -p) I get the following error.

mysql: unknown variable 'basedir=c:/mysql/'

Running on
Windows Server 2008 R2
MySQLServer 5.1

my.ini FILE info (removed some commented lines)

[client]
port= new_port

##The MySQL server
Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/MySQL/"
#Path to the database root
datadir="C:/MySQL/"
#Set the default character set.
default-character-set=latin1

[mysqld]
#password=your_password
port = new_port
#socket = /tmp/mysql.sock
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size=4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size=201M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

#Path to installation directory. All paths are usually resolved relative to this.
basedir = "C:/MySQL/"

#Path to the database root
datadir = "C:/MySQL/Data/"

#The default storage engine that will be used when create new tables when
default-storage-engine=MyISAM

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=160

# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
table_cache=320

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size=205M

#Enable this option only if you would like to use InnoDB tables.
skip-innodb
#Don't allow new user creation by the user who has no write privileges to the mysql.user   table.
safe-user-create
#Start without grant tables. This gives all users FULL ACCESS to all tables!
skip-grant-tables
#Set the default character set.
default-character-set=LATIN1

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id   = 1

#If no specific storage engine/table type is defined in an SQL-Create statement the   default type will be used.
default-storage-engine=myisam
#Enable this option only if you would like to use InnoDB tables.
skip-innodb
#The number of simultaneous clients allowed.
max_connections=160
#The number of open tables for all threads.
table_cache=320
#If an in-memory temporary table exceeds this size, MySQL will automatically convert it  to an on-disk MyISAM table.
tmp_table_size=205M

#Don't allow new user creation by the user who has no write privileges to the mysql.user   table.
safe-user-create
#Start without grant tables. This gives all users FULL ACCESS to all tables!
skip-grant-tables

#Set the default character set.
default-character-set=LATIN1

Thank for posting the my.ini

PROBLEM: You have the basedir and datadir under the [client] section

basedir and datadir are server-only options, not client program options.

SOLUTION : Remove them from under the [client] section and you are good to go !!!

When done, this

[client]
port= new_port

##The MySQL server
Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/MySQL/"
#Path to the database root
datadir="C:/MySQL/"
#Set the default character set.
default-character-set=latin1

should now be this:

[client]
port= new_port

Give it a Try !!!


Is the ADO.NET Entity Framework known for generating poorly performing queries? This question is inspired by the comment posted to the latest ServerFault blog post:

  Are you guys still using LINQ to SQL?

I know I can use SQL Server Profiler and/or the ToTraceString method to see the generated queries and analyze them myself. However, I am looking for opinions from people with hands-on experience with administering databases accessed by applications utilizing the Entity Framework.

Are Entity Framework queries a common cause of performance problems?
Can LINQ queries be optimized in such cases or is raw Transact-SQL the only solution?

It depends on what the queries are. ORMs are usually really good at CRUD, as they are usually simple. The more complex the query, the greater the chance of a bad query. You can tweak the generated queries by tweaking the LINQ statements. Sooner or later though, you'll get tired of fighting and use SQL queries or stored procedures for anything that is complex.


Multi master mysql with automerge data if node disconnected I have final project for my course about distributed system, so here the case :

  A company need 3 database server ( example : database a,b,c ) who
  works together. So if someone insert a data to database a , the other
  database ( b and c ) will have that data too. But when network
  disconnected, the database server will work like standalone server.
  But when network connected again, all data that inserted during
  network disconnected, will distributed to other database. Example :
  The database a disconnected to other database, and someone insert a
  data  to database a. And after database a connected to other database,
  other database will have that data again.

I try with galera, but it will fail to insert data when network disconnected.

Galera has the principle that a "quorum" in needed to declare that the cluster is alive.

For example if 'a' disconnects from 'b' and 'c', but 'b' and 'c' are still talking to each other, then

b and c have a quorum (more than half the servers), so they declare they are the cluster.
'a' does not have a quorum, so it refuses to accept any writes.
If the network gets repaired, 'a' will ask b & c for the latest updates and get back in sync.

That is Galera.  If you have plain MySQL multi-master, you have a nightmare when the network breaks.

NDB Cluster works a different way -- every node can be disconnected.  However, you must provide rules for "eventual consistency" of the data.


Slow proc Used to take a few seconds now takes well over a minute I'm at a new place and working on several issues. One item that was passed to me was this proc that used to take a few seconds is now taking well over a minute or timing out.

The data hasn't changed too drastically.

The filesReceived table has 902,596 records.
The fileuploads table has 629,862 records.

We run SQL2016.
Can you see if there is anything I can do to help speed this back up?

The plan:
https://www.brentozar.com/pastetheplan/?id=BJc4DsywZ

Easy answer for this. Updating statistics takes care of this slowness.


SQL Server: grant select access to a user in a view and not in its tables I have a SQL Server 2012 instance with a few databases. In one of them I created a view, that selects tables in more than a database.

I want a user to be able to select that view, but it must not select its tables. The view was created exactly because the user can't select the tables.

I've read https://stackoverflow.com/questions/368414/grant-select-on-a-view-not-base-table and http://msdn.microsoft.com/en-us/library/ms188676.aspx and still it is not working.

If I do a GRANT SELECT TABLE TO USER to all tables, the user is able to select the view. But if I revoke to any table, it fails.

This should be a easy procedure to do, but I'm having trouble to make it work. I've seen it happen before (the owner of a instance gave me access to a view and didn't do it to its tables) but I'm unable to do it or find somebody who knows how.

Could somebody provide me a tutorial on how to do it, or a code example?

When the user SELECTs the view I get the message:

  The SELECT permission was denied on the object <TABLE>, database <DB>, schema dbo.

If I grant select to that table, the error message changes the table name to another table the view reads.

If you want users to select from the view, why are you granting to the table? By "revoke" do you mean explicitly revoke/deny? Deny will override grant so there's your problem... you should be able to accomplish this by adding grant to the view and not doing anything either way on the tables.

Here's a quick example where SELECT has not been explicitly granted on the table, but has been on the view. The user can select from the view but not the table.

CREATE USER foo WITHOUT LOGIN;
GO
CREATE TABLE dbo.a(id INT);
CREATE TABLE dbo.b(id INT);
GO
CREATE VIEW dbo.v
AS
  SELECT a.id FROM a INNER JOIN b ON a.id = b.id;
GO
GRANT SELECT ON dbo.v TO foo;
GO
EXECUTE AS USER = N'foo';
GO
-- works:
SELECT id FROM dbo.v;
GO
-- Msg 229, SELECT denied:
SELECT id FROM dbo.a;
GO
REVERT;

Note that this assumes foo has not been granted elevated privileges through explicit permissions on the schema or database, or through role or group membership.

Since you are using tables in multiple databases (sorry I missed the end of that first sentence initially), you also may need explicit grants on the table(s) in the database where the view does not exist. In order to avoid granting select to the table(s), you could create a view in each database, and then join the views.

Create two databases and a login:

CREATE DATABASE d1;
GO
CREATE DATABASE d2;
GO
USE [master];
GO
CREATE LOGIN blat WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO

In database d1, create a user, then create a table and a simple view against that table. Grant select to the user only against the view:

USE d1;
GO
CREATE USER blat FROM LOGIN blat;
GO
CREATE TABLE dbo.t1(id INT);
GO
CREATE VIEW dbo.v1
AS
  SELECT id FROM dbo.t1;
GO
GRANT SELECT ON dbo.v1 TO blat;
GO

Now, in the second database, create the user, then create another table and a view that joins that table to the view in d1. Grant select only to the view.

USE d2;
GO
CREATE USER blat FROM LOGIN blat;
GO
CREATE TABLE dbo.t2(id INT);
GO
CREATE VIEW dbo.v2
AS
  SELECT v1.id FROM dbo.t2
    INNER JOIN d1.dbo.v1 AS v1
    ON t2.id = v1.id;
GO
GRANT SELECT ON dbo.v2 TO blat;
GO

Now launch a new query window and change the credentials to be for the login blat (EXECUTE AS does not work here). Then run the following from the context of either database, and it should work fine:

SELECT id FROM d1.dbo.v2;

These should both yield Msg 229 errors:

SELECT id FROM d1.dbo.t1;
GO
SELECT id FROM d2.dbo.t2;

Results:

  Msg 229, Level 14, State 5, Line 1
  The SELECT permission was denied on the object 't1', database 'd1', schema 'dbo'.
  Msg 229, Level 14, State 5, Line 3
  The SELECT permission was denied on the object 't2', database 'd2', schema 'dbo'.


Large MYSQL DB on single file I have a large MYSQL innodb database (115GB) running on single file mode in MySQL server.

I NEED to move this to file per table mode to allow me to optimize and reduce the overall DB size.

Im looking at various options to do this, but my problem falls in there only being a small window of downtime (roughly 5 hours).

Setup a clone of the server as a slave. Set the slave up with file_per_table, take a mysqldump from the main DB, run in the slave and have this replicating.

I will then look to fail over to the slave.

The other option is the usual mysqldump, drop DB and then import.

My concern is around the time to take the mysqldump and the quality of the dump being such a large size. I have BLOB data in the DB also.

Can anyone offer advise on a good approach?

Thanks

If you're concerned about the downtime mysqldump might cause you can look into hot backups. For example percona-xtrabackup.

Here is what I would do:

Create a slave with xtrabackup (set master_info_repository to
table if you have MySQL 5.6.2+)
Have it replicated and let it catch up
Stop the replication: stop slave
(optional) Make a backup of master info file if you have MySQL older than 5.6.2
Make a note of the position with show slave status
Make the mysqldump
Destroy the database and all the files in mysql_datadir and remove all the relay logs
Restore the database with mysql_install_db and execute the dump (make sure you have file_per_table settings)
(only on <5.6.2) If necessary restore replication (if it was in table than the dump contains the information so no need)

Then you can do the switchover and you'll have only a couple of seconds of downtime.


Is transaction log lost after backup on SQL Server? I have database that has regular nightly backup (full recovery, full backup). I noticed some irregularities in data and wanted to check out transaction log to see what happened. I restored earlier backup on my developer machine and noticed transaction log is empty. So ...

Transaction log is not written along with full backup ?

Do I lose transaction log on a database when I do full backup ( is log truncated on full backup ) ?

If I want to have data backup and transaction logs backup what should I do ?

Current backup script:

BACKUP DATABASE MyDB TO
DISK = @MyFileName
WITH NOFORMAT, INIT,  NAME = N'MyDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,
STATS = 10

Transaction log is not written along with full backup ?

This is not correct.
In FULL recovery mode – the database backup contains all the log necessary to make the restored database transactionally consistent as of the end of the backup operation.

Refer myths around full database backups from Paul Randal. Below is an excerpt

*A full database backup, when restored either explicitly using WITH RECOVERY, or when a recovery option is not specified ALWAYS results in a transactionally-consistent database. The point-in-time at which the database is restored to is the point at which the data-portion-reading part of the backup operation completed. All database backups include transaction log, otherwise there would be no way to rollback transactions that were active at the time the data-reading portion of the backup ended.*

Do I lose transaction log on a database when I do full backup ( is log truncated on full backup ) ?

No you do not loose T-Log when you do full database backup. Refer to

Misconceptions around the log and log backups: how to convince yourself for understanding how log backups work.

If I want to have data backup and transaction logs backup what should I do ?

If you want to have data backup and T-log backups, you have to first perform FULL database backup and then take subsequent T-log backups. So when you restore your database, you can restore a full backup and then subsequent T-log backups to do a Point-in-Time recovery.


Copy whole table to another database I want to insert a whole table's contents to another database which has the same table name.

How can I achieve this?

I am using SQL Server Express 2008 R2.

you can create a table with same schema in another database first and copy the data like this. Let say my new Database name is NewDataBase and I want to copy data from table Sales.SalesPerson of [AdventureWorks2012] database and both database are in same server.

CREATE TABLE [NewDataBase].[dbo].EmployeeSales
( BusinessEntityID   varchar(11) NOT NULL,
  SalesYTD money NOT NULL
);
GO

INSERT INTO [NewDataBase].[dbo].EmployeeSales
    SELECT BusinessEntityID, SalesYTD
    FROM [AdventureWorks2012].Sales.SalesPerson;
GO


when scripting SQL server agent jobs, is it possible to use @@servername in the @command settings line I am trying to script an SQL server agent job so that one script can be run on multiple servers to set up the same job.  we have the same users and will use the same job name on the different servers. however there is one part that I am struggling with, and that is getting the server details for the relevant servers pulled through as part of the script.
the initial script has something like this:

@command=N'/ISSERVER "\"\SSISDB\SSP SSIS\step1.dtsx\"" /SERVER (servername) /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E',

I am aware that I could replace our server name with localhost, however then you can only view the configuration settings from the server that the job will run on, which isn't ideal as not everybody has access to all server, especially the production server.

I was hoping to use "/SERVER' + @@SERVERNAME + '/Par" but in breaking the string for the @command line I receive an incorrect syntax error at the + symbol and I can't find any way of getting this to accept this command.

Use the SQL Server Agent tokens when scripting the jobs something like

 /SERVER $(ESCAPE_NONE(SRVR))


TSQL script to backup database using a driver table and stored procedure I am in need to fulfill a client request that wants me to create a backup solution that includes a stored procedure that will pick up the backup information from a driver table that will hold all the parameters needed such as backup file path, type of backup, no. of days to retain the backup etc and based on that information the stored procedure will initiate the backup. I spoke to the client regarding the Ola's script for backing up the database but as per them they want an in-house developed script and is adamant about that. Can someone please help. I don't have much experience writing T-SQL code. I have a fair idea about what would be the end result but I don't know about the implementation logic. Please do let me know if someone already has a script that does the same.

There's a script out there that does almost the same thing and it's Ola's script. While the table is not programmable, the execution procedure is, so you can customize the job steps vs the table, it becomes more portable and easier to deploy in any environment. You'll find it hard to find another backup solution online that works as well to copy verbatim because solutions like Ola's already exist.

If they want an in-house built process utilizing T-SQL and you do not have the ability to write the T-SQL for the process, I would look at where you are now and how do you get to your end goal.

First, get your fundamentals together. Make sure you understand backups inside and out. Here are two good links to get started:

Backup ref 1

Backup ref 2

Next up on the list, T-SQL. You're in for a real bad time if you have to write all your code in T-SQL and if you don't know T-SQL. Check out Microsoft Developer Essentials. It comes with SQL Server Dev 2016 which is similar to Enterprise. It also comes with a 3 month trial of Pluralsite which you can and probably should immediately begin using to bolster your T-SQL. You can also check out these three books to help learn.

SQL in minutes

SQL Fundamentals

T-SQL Dev guide

MS Dev

In the end, you're going to have to pick a method for your process. This article  talks about methods of administration that you will have to use to complete this task. You may have to incorporate all of them to achieve your goal.

I'd start there and progress, unfortunately I don't see any shortcuts in this scenario if you want something done correctly. Backups and data accessibility are huge in the land of database. See Brent Ozar's hierarchy of DBA needs for more reference there. I cannot stress the importance of doing backups correctly, it's one of the foundations for DBA's and most IT roles.

Once you get into this for awhile, I would recommend to come back here with specific questions about the process you have already began to write and supply us with good sample code. See here for best practice asking questions.


COUNTING results from self-referenced table recursively I have a table called Clients. And at this table, I have an idFather to know who added this row to the table.

Sample:

id | user    | idFather | createdAt
---+---------+----------+-----------
01 | raphael | null     | 01-06-2016
02 | jose    | 01       | 03-06-2016
03 | ryan    | 01       | 02-06-2016
04 | alyssa  | 03       | 04-06-2016
05 | laysa   | 04       | 04-06-2016
06 | karen   | 02       | 04-06-2016
07 | roger   | 03       | 04-06-2016

Now i need know how much peoples an user invited indirectly. Sample:

The user (01 - Raphael ) invited the users ( 02 and 03 ) so:
    Raphael invited 2 directly
    And user (02 - Jose) invited no one
    But user (03 - Ryan) invited (04 and 07) total 2
    And user (04 was invited by 03) invited also the user (05) total 1

So Raphael invited 2 directly ( this i did not know )
And Raphael invited 3 indirectly ( this i need know )

I i'm trying look 7 levels under the user...
i'll try explain with this tree:

EDIT:
If the users from lv1 was hard to kick out from Couting... I can do this math on my system... i just need do recursively by 7 lvs... and count just lv1 and get the difference.

I'm looking to get a query result like this:

user    | indirectInviteds
--------+-----------------
raphael | 4

I'll search just 1 user per time... i`ll not try select ALL clients and look directly.. it will explode...!!!

Thanks for the help guys!

As your data is a tree structure, consider using the Nested Set data model.

A good write-up can be found at Mike Hillyer's website, here:  http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

complete with lots of SQL to manage the tree data.


Finding code tab's associated schema in MySQL Workbench I have a single-tenant setup of MySQL databases; i.e. a more or less identical schema instance for each client.

When I'm tasked with modifying the code of stored routines, I use MySQL Workbench. After some time I usually find myself having code tabs with similar or identical code for several distinct database schemas.

How do I know to what schema a certain stored routine tab belongs?

Actually, there's no schema associated with a certain code tab. All tabs use the active schema. There's already a feature request for a schema-per-tab feature.

Update: you meant object editors and indeed there's no indication to which schema they belong. Please file a feature request to get this into MySQL Workbench (http://bugs.mysql.com).


What is the 'repair code' in MySQL? OK I was adding an index to a table, and I checked on progress using show processlist and under state it output Repair by sorting.

From the MySQL docs:

  Repair by sorting

  The repair code is using a sort to create indexes.

So what is the 'repair code'? Did something break?

Nothing broke, it's just part of the process of rebuilding indexes on a table. This can take quite a while on very large tables.


Oracle Top CPU Consuming SQL is "select longdbcs from javasnm$ where short = :1" For the Oracle 12c database I've been administrating, I usually carry out the monitoring using Oracle Enterprise Manager Cloud Control 13c. From time to time, the database suffers from performance issues that are felt by end users.

When I check the performance details of the database in EMCC following the menu in Database Home page Performance -> Top Activity, I see some application specific SQLs are problematic. In addition to problematic SQLs, I also usually see the following SQL listed as the top consumer:

select longdbcs from javasnm$ where short = :1

I know that the 3rd party application running on top of this Oracle database is Java based, and it might not have the most optimized code in terms of Oracle calls.

Would anyone have any idea what this SQL would be about and how to figure out if it is really needed or something that could have been disabled? (at Oracle level or with a slight change in application level)

I searched the internet for relevant information but was not able to find anything clear / comprehensible enough for me. For anyone who would have any points, suggestions and answers are welcome. Thanks in advance.

From what I remember about javasnm$, it is a table that stores long names of java objects, as oracle objects can only be up to 30 characters (stored in field SHORT). Most java objects are over 30 characters so it constantly has to do these look-ups.

The SQL we have is:

select longname from javasnm$ where short = :1

We had similar performance concerns about the high execution counts of similar SQL when apps made use of the JVM built in to oracle, but nowadays most of our java apps don't use the built-in JVM and that issue isn't there anymore.

Check with your third-party vendor if they can move it off the database and use something like connection pooling e.g. Weblogic to interact with oracle.


Why does PSQL not allow hash indexes with Bitmapscan? For doing a simple equality query such as

EXPLAIN SELECT * FROM table where x = 1;

I see that a Bitmap Index scan is done using the BTree indexes. Apparently, Bitmap Index scans disable Hash indexes but I'm curious as to why? Wouldn't the query be faster if we can hash the pointer values and then run the page fetches afterwards?

Hash indexes are obsolete. Don't use them. They don't support crash safety either. Performance tests show that b-tree indexes tend to be on par with hash indexes for most purposes anyway.


Broken NTFS Permissions for MSSQL$SQLEXPRESS I have been "securing" our database server. To do this I removed some permissions from the folders that the SQL Express install created.
D:\SQL
D:\SQL\Data
D:\SQL\Backup
etc..

I kept the permissions on the Data folder as they were, but I removed anything I thought I didn't need from the others.
Now I can't backup databases. If I try selecting the backup location the dialog doesn't show anything in the d:\SQL folder and if I put the path in myself it tells me this:-

Cannot access the specified path or file on the server. Verify that you have the necessary security privilages and that the path and file exists.
...

It looks like folder permission problem. Another server configured in a similar way has a bunch of "allow" permissions for an account called "MSSQL$SQLEXPRESS", (which I know I deleted), But I can't re-add these permissions because there is no such group, user or built-in security principle on the server.
How do I add permissions for this MSSQL$SQLEXPRESS account?

This account is a service SID.

The full name is NT SERVICE\MSSQL$SQLEXPRESS.


DB/table design help I have 50,000 unique cards in a table. A minimum of 2 cards and up to 20 cards can be chosen by the user and they can write a blurb about it. There will be times when the cards chosen by different users will overlap. When the exact same cards are chosen, I'd like to store the new blurb but keep the table clean as possible as many people may choose the same 2 cards but write different blurbs about the 2 cards. How should I design my table to store this information?

I'd suggest, you store the 'chosen cards' in an separate assignment-table and group this assignment per user.

For example your table 'cards':

 table: cards
--------------
 card_id | PK
 ...

The following table stores the blurb for the given user:

 table: blurbs
--------------------------------------------------------------
 blurb_id   | PK
 user_id    | (given user, that wrote the blurb)
 blurb_text | (whatever you want to store here, e.g. a blob?)

Last, but not least, you need the assignment-table, to define, which user/blurb is assigned to which cards:

 table: card_assignment
------------------------------------------------------------------------------------------
 blurb_id | PK
 card_id  | PK (the cards to be chosen - card can only be assigned once per 'user-blurb')

Now, you can select any information, you need across that three tables:

E.g.: Any cards chosen by a particular user:

SELECT *
FROM card_assignment a
LEFT JOIN cards c ON (a.card_id = c.card_id)
WHERE EXISTS (
    SELECT 1
    FROM blurbs b
    WHERE b.user_id = :user_id
    AND b.blurb_id = a.blurb_id)

Indeed, the select statements can get very complex, if you have to use multiple tables that way, but in my opinion, this solution is as clean as possible.

Don't forget to put referential constraints on the fields, if you use mySQL with Inno-DB!


Execution plan for staging In-Memory tables estimate row count =1 We are using In-Memory tables (durability schema only) as temporary staging tables for part of the ETL process.
the nature of these staging tables is empty most of the time.
The ETL process executed per organization asynchronicity.

Problem:
The execution plan is generated with estimate row number = 1 (probably the table was empty for first execution)

Manually update statistics will not fix the issue since:
the table may be empty or the no record for an organization that ETL will execute first.

I add OPTION recompile to query but still it not stable

I'm looking for correct pattern working with in-memory tables as the Staging table

MS SQL 2017 CU 13
Standard edition

In-Memory table schema:

CREATE TABLE [dbo].[process_aggregation_close] ( [organization_id] [bigint] NOT NULL, ...... INDEX [ix_process_aggregation_memory_computer_id_process_id_test] NONCLUSTERED ( [organization_id] ASC, [computer_id] ASC, [process_id] ASC ) )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )

Destination Clustered Column Store (disk base)

CREATE TABLE [dbo].[process_aggregation_5m]( [organization_id] [bigint] NOT NULL, ....... ) ON [ps_process_aggregation_close]([partkey]) GO CREATE CLUSTERED COLUMNSTORE INDEX [IX_process_aggregation_5m] ON [dbo].[process_aggregation_5m] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE) ON [ps_process_aggregation_close]([partkey])

Thank you
Yuri

Are you using memory-optimized tables, or memory-optimized table variables? Memory-optimized table variables suffer from the same issues as "regular" table variables: the cardinality is assumed to be very low.

The other potential issue with using memory-optimized tables as a destination for staging ETL, is that all writes to memory-optimized tables are serial. So you will have to "roll your own" parallelism.


error query did explode the postgres database I had some problems last month. I had a bad query in Postgres (PostGIS) of a user launched during the night. This query took all the memory and I had 0% of free disk.

Fortunately, I restored the database with my dumps, it's ok, but I'm afraid for the next time.

Can I predict a bad query in Postgres and stop it immediately?

Which tools? Which scripts? Do you recommend any?

Limiting is hard, but you can set a timeout on query for a user :

ALTER ROLE <user> SET statement_timeout TO '60s';

As of the disk I'm not aware of any technique to limit disk usage of a query in postgresql.


Oracle ram usage higher than memory_max_target To begin, while I've worked with Oracle for many years, I'm far from a DBA.  (and it'll take probably a few weeks before I can get one assigned to look at this.)

I've got an Oracle instance that is consuming way more ram then is set with the memory_target and memory_max_target parameters.

SQL> show parameter memory_target;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
memory_target                        big integer 8000M

SQL> show parameter memory_max_target;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
memory_max_target                    big integer 9024M

However Oracle is taking up about 11GB of ram at the moment.

AMM is turned on

SQL> show parameter pga_aggregate_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
pga_aggregate_target                 big integer 0

SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
sga_target                           big integer 0

But oracle is still being a memory hog

When I reduced memory_target and memory_max_target to the current values (down by 2GB each) the only thing that changed was the primary ORCL instance reduced it's memory footprint, but the RCAT instance took up the available freed ram.

The machine has been rebooted with no change in memory consumption.

I'm assuming RCAT here is the recovery catalog, but what would cause it to consume higher memory than is assigned via the memory target params?

Is there another area I should be looking at? (i.e. is memory for the recovery catalog instance controlled by another parameter?)

What additional information would be helpful in narrowing down the issue?

Windows 2012R2
16GB assigned ram
Oracle 18C
Approx 130 sessions with very little activity (basically idle)

Those are 3 seperate instances.

You checked and listed the memory parameters from only 1 instance.

We do not know which instance that is, and we do not know anything about the other 2.

Each instance has its own memory parameters.

None of the instances use more memory than the value listed above.

I see no problem here.


What Type of Join to Use in this Search? I have the following query that is bound to a datagrid:

select
  distinct  a.Person_ID,
            a.Person_Last_Name,
            a.Person_DOB,
            CASE
              WHEN a.Person_Last_Name IS NULL
                THEN ''
              ELSE CONVERT(varchar, a.Person_Last_Name)
            END
            + ', ' +
            CASE
              WHEN a.Person_First_Name IS NULL
                THEN ''
              ELSE CONVERT(varchar, a.Person_First_Name)
            END
            + ' ' +
            coalesce(a.Person_Suffix,'')
            + ' ' +
            coalesce(a.Person_Middle_Name,'')
            +
            CASE
              WHEN a.Person_Archived='T'
                THEN ' (Archived)'
              ELSE ''
            END
            +
            CASE
              WHEN a.Person_Deceased='T'
                THEN ' (DECEASED)'
              ELSE ''
            END AS PersonName
FROM PERSON a
  LEFT OUTER JOIN COMPANY c
    ON a.Person_Company_ID = c.Company_ID

I need to also pull up an image for each person when one is available (but display 'None' when no image is available). There are potentially multiple images for each Person - I only want to select the first one. However, there are also possibly no images on file, in which case a default value should be populated. Here is the current SQL statement to fetch images for Persons:

SELECT
  PImage_Path
FROM PIMAGE
  WHERE PImage_Subject_ID=@Subject_ID

When no images are available, however, I would like it to return 'None' as the value of PImage_Path and not restrict the original select statement from finding Persons with no image. What type of join should I use? How would this query look?

Probably something along these lines, courtesy of OUTER APPLY:

select
    --A bunch of stuff
    ISNULL(pic.PImage_Path, 'None') AS PImage_Path
FROM PERSON a
  LEFT OUTER JOIN COMPANY c
    ON a.Person_Company_ID = c.Company_ID
  OUTER APPLY (
    SELECT TOP 1
      PImage_Path
    FROM PIMAGE
    WHERE PIMAGE.PImage_Subject_ID = a.Person_ID --Fix this if I'm making the wrong assumption about your key columns.
    ORDER BY ... --Something sensible to determine which picture will be used
  ) pic


How to make delete duplicates faster? On a mysql table with about 1.7M rows, I tried to delete duplicates posts:

delete a FROM comment a
  INNER JOIN comment a2
     WHERE a.id < a2.id
     AND   a.body = a2.body;

The result was:

  Query OK, 35071 rows affected (5 hours 36 min 48.79 sec)

The table schema:

CREATE TABLE `comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `author_id` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `title` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `body` longtext COLLATE utf8_unicode_ci NOT NULL,
  `post_id` int(11) NOT NULL,
  `published` tinyint(1) NOT NULL,
  `cid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_postid_idx` (`id`,`post_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1774682 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

This happened on my almost idle workstation with Intel(R) Core(TM) i7-4790 CPU @ 3.60GHz.
I'm wondering if there are some tricks to make this delete operation faster?

Your algorithm is "Order(N^2)".  If you have a million rows, it will take about a trillion comparisions!  (Actually closer to N*N/2.)

I assume you want to run this again later?  Or you want to avoid inserting duplicates?  In either case, the following will speed things up greatly.

ALTER TABLE `comment` ADD COLUMN hash BINARY(20);
UPDATE `comment` SET hash = UNHEX(SHA1(body));
ALTER TABLE `comment` ADD UNIQUE(hash);  -- (see discussion below)

When INSERTing, include hash with a value of UNHEX(SHA1(body)).  If the hash is a dup, then body is extremely likely to be a dup, and the INSERT will suitably fail.

INDEX versus UNIQUE:

INDEX gives you a much faster way to do the dup-elimination:  check hash instead of body.
UNIQUE prevents future insertion of dups.

So, let's do it in two phases:

Add INDEX(hash) (not UNIQUE(hash))
Do the dedupping -- With an INDEX(hash), the DELETE is Order(N) -- a million tests, not a trillion.
Change to UNIQUE:  DROP INDEX + ADD UNIQUE. -- Now future INSERTs will balk; so add suitable code to deal with such.  Consider INSERT IGNORE.

All of that will take minutes, not hours.  And you won't have to do any of it again.

(With a newer version of MySQL, you could use GENERATED ALWAYS to avoid some of the code.)

One shot

To do the delete once and do it without modifying the table:

CREATE TABLE t (
        id INT NOT NULL,
        hash BINARY(20),
    PRIMARY KEY(id),
    INDEX(hash) ) ENGINE=InnoDB;

INSERT INTO t (id, hash)
    SELECT id, UNHEX(SHA1(body))
        FROM `comment`;

DELETE FROM `comment`
       JOIN  t AS t1  ON t1.id = comment.id
       JOIN  t AS t2  ON t2.hash = t1.hash
                     AND t2.id < t1.id;

DROP TABLE t;

Caveat:  I have not tested this code.  Recommend you test it separately, perhaps with a subset of the data.


Are multiple Primary Keys in MySQL allowed? I haven't used SQL for ages and now I have to create a software with a small DB. I've decided to use MySQL with MySQL Workbench to help me design this database (by drawing a model with ER diagram).

When I've started adding relations, I found out that with each relation added to table, another PK is added to it. At first, I thought that it is how this GUI marks both Primary and Foreign Keys, but then I saw the output SQL code (query), e.g.:

CREATE TABLE IF NOT EXISTS `test`.`table` (
    `id` INT NOT NULL,
    ...
PRIMARY KEY (`id`, `ppe_id`, `type_id`, `code_id`),
...

So my question is - are those other fields/columns also Primary Keys? Up till now I thought that only a single PK is permitted per table.

Actually, those additional PKs gave me an error in the end, when I tried running SQL statements generated by Workbench on server, because I need one of those "key-fields" to allow NULL values. To solve that I removed a tick for PK checkbox on this nullable column. So additional (strongly related) question: should I remove all those additional PKs from all of my tables as well? Even if they can have NOT NULL constraint, their values won't be unique, because those are used mostly for one-to-many relations.

Only one primary key per table is permitted. But a primary key can consist of any number of columns. Having many columns in a primary key however is often (not always) a bad choice.

Some notes about this...

InnoDB tables are organized by primary key.
As a consequence, all secondary indexes contain the primary key. Having a big primary key means having big indexes.
Another consequence is that rows should be inserted in the order of the primary key (this happens automatically if you use an autoincrmeental id). Otherwise, InnoDB will have to do additional work to maintain the physical order of rows.
Yet another consequence is that updating the primary key values is a slow operation that shouldn't happen normally.
Lastly, if you don't keep in mind that the primary key is contained by all secondary indexes, with InnoDB it is easy to create indexes with duplicated columns.
A query can use a whole index or a contiguous leftmost part. In your case, for example, a query that does not mention id cannot possibly take advantage of the primary key.


Why SMO's Transfer tries to login into linked server when scripting? Consider this code sample:

            var sourceDatabaseConnectionString = "Data Source=MyServer;Initial Catalog=MyDatabase;User Id=MyLogin;Password=MyPassword";
            var serverConnection = new ServerConnection
            {
                ConnectionString = sourceDatabaseConnectionString
            };

            var server = new Server(serverConnection);
            var builder = new SqlConnectionStringBuilder(sourceDatabaseConnectionString);
            var database = server.Databases[builder.InitialCatalog];
            var transfer = new Transfer(database);

            // setting transfer options
            transfer.CopyAllObjects = true;
            transfer.CopyAllSynonyms = true;
            transfer.CopyData = false;

            transfer.Options.WithDependencies = true;
            transfer.Options.DriAll = true;
            transfer.Options.Triggers = true;
            transfer.Options.Indexes = true;
            transfer.Options.SchemaQualifyForeignKeysReferences = true;
            transfer.Options.ExtendedProperties = true;
            transfer.Options.IncludeDatabaseRoleMemberships = true;
            transfer.Options.Permissions = true;
            transfer.PreserveDbo = true;

            // scripting database
            var script = transfer.ScriptTransfer();

It tries to get a blank from existing database.

Some objects in this database depend from objects, located on linked servers. When this code tries to make a script for such objects, ScriptTransfer fails with "Login failed for user 'MyLogin'".

Log analysis shows, that error happens when MyLogin tries to log into liked server. But what is the need for this? After all, I just want to get a bunch of CREATE ... scripts for MyDatabase, I don't want to read any data from linked server.

My SQL Server and SMO version is 2012 SP1.

You can only get data from a SQL Server that you are logged into.  The metadata of table definitions, stored procedures, and so forth is also data.

So, your login through the linked server must be successful.  This can be done by granting your login rights on the other server.  Or, the security of the linked server could be through a specific login that has the needed rights.

However, getting rights to the metadata of a database does not imply that you need rights to the data in the database.  For example:

USE DBName;
GRANT VIEW DEFINITION ON DATABASE::DBName TO [YourDomain\YourLogin]

This will allow you to script the definitions from the database without having access to the user data.  (Although that can also be granted separately if needed.)

EDIT: SQL Server does not support deferred name resolution across a linked server.  Therefore, I am fairly sure that since the reference to the remote table cannot be resolved that is why the scripting is failing.

Of course, if the stored procedure is not encrypted then the text is stored in sys.sql_modules of your local server.  You could extract the text from there.

 SELECT definition FROM DBName.sys.sql_modules
 WHERE object_id = object_id(N'DBName.Schema.ObjectName');


'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces I am trying to backup mysql using the command
mysqldump -u root -p  database_name > backup.sql

, but it is throwing an error:

'Access denied; you need (at least one of) the PROCESS privilege(s)
for this operation' when trying to dump tablespaces

Also, I wanted to make a backup my database with all tables seperately stored as a file. How can I do it?
mysql version = 5.7.31

use --no-tablespaces
see mysqldump-Documentation

mysqldump requires at least the SELECT privilege for dumped tables,
SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES
if the --single-transaction option is not used, and (as of MySQL
8.0.21) PROCESS if the --no-tablespaces option is not used.
Certain options might require other privileges as noted in the option
descriptions.

and see documentation of param no-tablespaces

--no-tablespaces, -y
This option suppresses all CREATE LOGFILE GROUP and CREATE TABLESPACE
statements in the output of mysqldump.


DB2 PURGE (old Online Backups and Archive logs) - some of the archive logs are not removed DB is in Archive Mode.
We are creating full Online Backups every night at 23:00.
We set automatic removal of old online backups and archive logs.

AUTO_DEL_REC_OBJ ON
NUM_DB_BACKUPS   2
REC_HIS_RETENTN  2

In backup folder we have two online backups and that is ok:

Backup1 22.02 23:00
Backup2 23.02 23:00

In the archive logs folder we have these logs:

from S0000047.LOG to S0000057.LOG (from 22.02 23:00 to the 23.02 22:41)
from S0000058.LOG to S0000062.LOG (from 23.02 23:00 to this morning 24.02)

Those are OK logs of course.
But I noticed that we have also archive logs which should not be there (logs after the backup of 21.02 which is already deleted and all those transactions are already contained within the backup of 22.02). But we have archive logs

from S0000037.LOG to S0000046.LOG (from 22.02 01:41 to the 22.02 22:42)

So those are unnecessary logs.
On some other system (server) for some other customer we have also enabled this purging but there we have regular situation - only backups and archive logs after those backups are kept, there are no previous archive logs, but on this system it seems like deletion of all archive logs is late to 1 day.

Why is that I really think this is not regular situation (as I said also based on experience on some other server)

You can always clean them up manually or use a script to clean them up (maybe looking for files older than seven days or something).


Error: ERROR 1047 (08S01) at line 6955: WSREP has not yet prepared node for application use While restoring a dump from a CloudSQL instance into a node of a Percona xtradb cluster, I am getting this error. The line 6955 in the .sql dump file is in bold below:

DROP TABLE IF EXISTS `next_balls`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `next_balls` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `match_id` int(11) NOT NULL DEFAULT '0',
  `inning_id` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=176335 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `next_balls`
--

****/*!40000 ALTER TABLE `next_balls` DISABLE KEYS */;****

The error came on restoring the table 'next_balls', this table was restored in the node, but data was not inserted, so I think the error comes due to the "ALTER TABLE next_balls.." statement. Any idea why this error pops up? The restoration stops when the error comes. Any help would be highly appreciated.

Edit 1: I had a 2 node percona cluster, it seems the error comes when one of the node is down or feels that the other is down, which results in a split brain situation and the cluster halts. Is this the case? Should I add another node in the cluster and do the restore? Or should I first do the restore in one node and then set up the cluster?

I had the same issue with gcloud percona instance running on 3 servers.

After running mysql logs from the first instances i found out it was not able to reach to instance-2. I also ssh to instance-2 but i could not reach it. On tailing mysql logs in instance-3 i found out its also not able to reach instance-2.

I restarted instance-2 and everything went back to normal.
With percona mysql clustering when one cluster is down and dirty_write turned off all the write operations are blocked untill all the clusters are restored.


Constructing datetime from 2 fields: one integer and a datetime I am looking for the most efficient way to parse 2 values; the first is stored as a datetime field with (hh:mm:ss.fff all being 0) and the second time field stored as an int with the format: hhmmssmmm). I need to take these 2 fields and convert them to a datetime field. For example:

date field = '2013-09-04'
time field = 192423998

Should return:

2013-09-04 19:24:23.997

I've seen solutions using DATEADD and CONVERT and I came up with my own (shown below) but I wonder if there's a better/more efficient way.

DECLARE @dt DATETIME = '2013-09-04'

SELECT  @dt+' '+ STUFF(STUFF(STUFF(RIGHT('0'+CAST(192423998 AS VARCHAR(9)),9)  ,7,0,'.'),5,0,':'),3,0,':')

Note: In case you wonder why the RIGHT('0'+CAST(192423998 AS VARCHAR(9)),9) - this is because a time before 10 AM will only have one digit for the hour part.

So, you're going to be parsing strings and ints and doing all kinds of conversions, so I don't think you're going to really save anything by seeking out a "more efficient" way to turn crap data into good data - even on billions of rows. An alternative might be to avoid converting anything to a string at all (which is notoriously bad for performance):

DECLARE @dt DATETIME = '20130904', @t INT = 192423998;

SELECT DATEADD(MILLISECOND, @t % 100000 -- milliseconds
  + 60000 * (@t / 100000 % 100) -- minutes converted to milliseconds
  + 60000 * 60 * (@t / 10000000), -- hours converted to milliseconds
  @dt);

Of course, keep in mind that the milliseconds your trading system passes in can round in either direction when using datetime (.998 isn't possible and becomes .997, for example), so you may want instead:

SELECT DATEADD(MILLISECOND, @t % 100000 -- milliseconds
  + 60000 * (@t / 100000 % 100) -- minutes converted to milliseconds
  + 60000 * 60 * (@t / 10000000), -- hours converted to milliseconds
  CONVERT(DATETIME2(3), @dt));

I ran these each 100,000 times, and compared to your existing approach. The difference?

Your approach:               41.8 seconds
My approach (no convert):    40.5 seconds
My approach (with convert):  38.9 seconds

Now, this should not be extrapolated to mean that on 1 billion rows my method will save you (13,000 or 31,000) seconds, as the translation of 100,000 batches to operating on 100,000 rows in a single statement will not be linear. But it could be worth testing (you have the data necessary to test this; I would just be making stuff up, and I also probably have processors that have different performance characteristics when it comes to floating point operations etc., so anything else I could offer would not necessarily be relevant).

I still think you're better off taking the hit, fixing the design, and intercepting the incoming trading data and correcting it. As this system gets bigger, these translations and conversions are going to hurt more, and more, and more...


barman + postgresql not start after barman retore I use the barman for backup and restore

after some restore , we start the postgresql as the following

systemctl start postgresql.service

but postgresql not startup

from the log:

/var/lib/pgsql/backups/data/pg_log

we seen the following errors

LOG:  database system was interrupted; last known up at 2017-08-06 07:30:49 UTC
LOG:  restored log file "00000008.history" from archive
LOG:  starting point-in-time recovery to 2017-08-06 07:30:49.813508+00
LOG:  restored log file "0000000800000000000000C1" from archive
LOG:  redo starts at 0/C1000080
LOG:  recovery stopping before commit of transaction 96139, time 2017-08-06 07:30:49.855629+00
LOG:  redo done at 0/C1001B90
FATAL:  requested recovery stop point is before consistent recovery point
LOG:  startup process (PID 55858) exited with exit code 1
LOG:  terminating any other active server processes

please advice what is wrong configuration on BARMAN that cause this ?

It looks to me like you are trying to restore to a point in time that is earlier than what is recorded in the backups.

Make sure you have not specified a recovery-time that is earlier than the backup you are trying to restore.

Check the files recovery.conf and backup.label in the postgres data directory.
If you want to restore to the latest possible, then you should make sure you don't have a property named recovery_target_time in the recovery.conf.


Organizing Database Objects in a Database that are not part of the systems original Database Objects? I've been working at a place for about 2 years now with a series of heterogeneous legacy database applications, which are tied together using 3rd party Microsoft Access databases (*.mdb).  Soon we will switch them to Postgres in a new integrated system.

The 3rd party *.mdbs worked well for separating out what queries (VIEWs) and which temporary tables belonged to which process for performing a task of some sort; since they were separated into their own files along with the VBA code.

The new system will be similar to the old systems but instead have them integrated together, and there will be less need for such 3rd party databases for the time being; However, should a new need arise, and a new VIEW or temporary table be required in the Postgres database; What is a best practice for grouping a series of VIEWs or temporary tables together so that later it can be known what task/purpose they belong to?

I would think that it would be beneficial to have some sort of a library structure for the VIEWs / temporary tables such that ones used for many different tasks are separated into:

common (used for many different tasks)
task1Views (specific to task1)
task2Views (specific to task2)

I'm trying to recreate the functionality in Access where a list of queries (VIEWs) and tables related to the task at hand are listed on the left side and are searchable only for that task.  And also to prevent confusion among tables that already exist for the system (linked tables in Access) and tables that I am using to perform a task.

On one hand, a sensible naming convention can solve part of your problem.

However, a more sophisticated solution is to use schemas: they are a good way to organize your stuff and also make you able to set access rights at this level.

For example, we usually create a data schema (named after the covered functionality and suffixed with '_data') and one or more API schemas.  Tables, the types used for their columns, triggers along with their procedures, sequences and so on all go into the data schema.  As applications are allowed to call API functions only (no arbitrary SQL is allowed), it just makes sense to put all of them into a separate schema, this is what I refer as API schema above.  There may be further ones (utility schema, versioning schema and so on), too.

Of course, your needs may be different, but I hope this illustrates well how schemas can be used.

In your case it is probably better to create schemas mapping the functional grouping.  Then you issue a

CREATE SCHEMA task1_views;

command, and create the objects either with specifying the schema, like

CREATE OR REPLACE VIEW task1_views.important_view ...

or set the search_path first, if you create many objects in a row:

SET search_path TO task1_views;
CREATE OR REPLACE VIEW important_view ... -- no schema specified


In Change Data Capture (CDC), why lsn is used instead of timestamp? I am learning to use CDC in SQL server 2008, I have read that LSN is always incremented and is based upon timestamp, so why LSN is required, if it is calculated from timestamp which can be used as increamental sequence id for changes made. I am sure that there will be some other purpose as well of lsn.

A date/time value is generally not considered to be unique enough. No matter how fine you define it, it is possible to have 2 actions occurring for the same datetime value.

Prior to SQL Server 2008, the most accurate value was a rounded 3.33 milliseconds with datetime which is an eternity really. datetime2 resolves to 100 nanoseconds which isn't accurate enough either.

From another view, time is is a real number. No matter how accurately you store a time, there is always some rounding to your level of resolution (In this case, 3.33 ms or 100 ns)

Using LSN (which is numeric (25,0)) gives a strictly monotonically increasing unambiguous whole number value. It is a natural number


Separate table creation at runtime or add column I am planning to keep patient data in a patient table. I will have multiple Clients registered with me, and each client will have multiple doctors. Should I make patient table client wise or a single table with client and doctor ID field?

It is better to have single patient table.

Each doctor must have different UserId(or call PhysicianId) and then make entry in patient table with FkPhysicianId as a column and you can have one table for storing clinic information(auto incremented key can be used as ClinicId) and one for storing physicians Information like ClinicId etc.

single patient table will be better as you don't need to add table for each practice you add and data can queried using joins on these table and maintaining too many table in future will be problomatic and other implementation in the EMR application as well.

I would suggest to have a Master Table to assign auto incremented Id to physician and patient as well.


Question on conditional join or other way to create view based on two tables in Postgresql I am trying to create a view that combines these two materialized views with following rules.

if all_products.id and there is matching id (market_id not relevant) in visible_products then only return visible_products rows
if all_products.id and there is no matching id in visible_products (market_id is not relevant in match) then return rows from all_products

I am not able to figure it out with joins or union. Can this be solved with a case statement? If both tables have rows then inner join will give me the correct result and if visible_products have no matching rows a left join will work but I need a join or other solution that will work with both cases.

Both all_products and visible_products are materialzied views that I can change / add columns to if needed.

Example a

all_products
id, market_id
1, 100
1, 101
1, 102

visible_products
id, market_id
1, 100

result product_view
1,100

Example b

all_products
id, market_id
2, 103
2, 104
2, 105

visible_products
id, market_id

result product_view
2, 103
2, 104
2, 105

Any help is greatly appreciated

Jonas

SELECT a.id, a.market_id
FROM all_products a
INNER JOIN visible_products v ON v.id=a.id AND v.market_id=a.market_ID
WHERE EXISTS (SELECT 1
       FROM all_products a
       INNER JOIN visible_products v ON v.id=a.id AND v.market_id=a.market_ID
      )

UNION ALL

SELECT a.id, a.market_id
FROM all_products a
WHERE NOT EXISTS (SELECT 1
       FROM all_products a
       INNER JOIN visible_products v ON v.id=a.id AND v.market_id=a.market_ID
      )


How do I debug max_allowed_packet going to -1? I am using Amazon's MySQL Aurora and using JDBI on the client side. Every once in a while, the connection goes bad and I get errors like this -

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (70 > -1).
You can change this value on the server by setting the 'max_allowed_packet' variable.

That -1 feels suspicious and seems like an integer overflow or something. Since I have multiple servers and only one of them randomly sees this, I am guessing this has something do with client-side mysql. The error is too fleeting to be able to debug. Any ideas on why this could be happening?

max_allowd_packet= set to the size of the largest packet requesting; Usually it can set it to 32M and increase it according to ur application needs. Specify it in [mysqld] and client sections in my.cnf For example

[mysqld]
max_allowed_packet=128M

[client]
max_allowed_packet=128M


Stopping N number of nodes of a Cassandra cluster running on a single machine using bash script I have set up a Multi-Node cassandra (3.11) cluster (with N nodes) in a single Ubuntu machine. I have written a bash script which

Creates N copies of conf folder
Configures the cassandra yaml file according to the need of N nodes (Such as separating the data and log directories)
Creates N copies of cassandra script inside the bin folder

It works correctly so far. But I am a little stuck after that.

I want to stop all the nodes effectively at the end of the script. Any help will be really appreciated at this point.

Thanks in advance!

I figured it out:

kill $(ps auwx | grep '[c]assandra' | awk '{print $2}')


What's better for large changes to a table:  DELETE and INSERT every time or UPDATE existing? I am making a project where I need to change around 36K records in one table daily. I'm wondering what will perform better:

delete rows and insert new ones, or
update already existing rows

For me it is easier to just delete all the rows and insert new ones, but if this is going to fragment the table and indexes and impact performance  then I would prefer to make updates where possible and delete/insert only when necessary.

This is going to be a nightly service and I am not looking to improve the speed of the process itself. I am more concerned about the performance of queries against this table in general where I already have 89 million records and how this nightly process will affect it.

Should I delete/insert records or should I update existing ones (where possible) for this nightly process?

It really depends on how much of the data is changing. Lets say this table has 20 columns. And you also have 5 indexes - each on a diff. column.

Now if the values in all 20 columns are changing OR even if data in 5 columns are changing and these 5 columns are all indexed, then you may be better off "deleting and inserting". But if only 2 columns are changing and lets say these are not part of any non-clustered indexes, then you may be better off "Updating" the records because in this case only the clustered index will be updated (and indexes will not have to be updated).

On further research, I did find that the above comment by me is sort of redundant as SQL Server internally has 2 separate mechanism for performing an UPDATE. - An "in-place update" (ie by changing a columns value to a new in the original row) or as a "not-in-place UPDATE" (DELETE followed by an INSERT).

In place updates are the rule and are performed if possible. Here the rows stay exactly at the same location on the same page in the same extent. Only the bytes affected are chnaged. The tlog only has one record (provided there are no update triggers). Updates happen in place if a heap is being updated (and there is enough space on the page). Updates also happen in place if the clustering key changes but the row does not need to move at all.

For eg: if you have a clustered index on last name and you have the names:
Able, Baker, Charlie
Now you want to update Baker to Becker. No rows have to be moved. So this can take in-place. Whereas, if you have to update Able to Kumar, the rows will have to be shifted (even though they will be on the same page). In this case, SQL Server will do a DELETE followed by an INSERT.

Considering the above, I would suggest that you do a normal UPDATE and let SQL Server figure out the best way to how to do it internally.

For more details on "UPDATE" internals or for that matter any SQL Server related internals, check out Kalen Delaney, Paul Randal's, et al.'s book -  SQL Server 2008 Internals.


CASE expression returns wrong value when using CEILING I've run into an issue where a CASE expression does not return what I expect.

As a test, I added a decimal variable and ran the same CASE expression against it and it works fine, returning the results as I would expect (rounding the value up when IsGun=1. But when I run that same CASE expression against another decimal value, it always returns the value with the CEILING() function and never returns the original value.

Here is the SQL code:

DECLARE @Num decimal(8,2);
    set @Num = 12.54;
    WITH PQ AS
    (
        SELECT
            UPC,
            Price1,
            DBID,
            AVG(Price1) OVER (PARTITION BY UPC) AS Price1Avg
        FROM
            vProducts_PriceQty_Union
    )
    SELECT
        PQ.UPC,
        PQ.Price1,
        PQ.Price1Avg,
        (CASE WHEN p.IsGun = 1 THEN CEILING(@Num) ELSE @Num END) AS UsingVar,
        CAST(
            (CASE WHEN P.IsGun = 1 THEN CEILING(PQ.Price1Avg) ELSE PQ.Price1 END)
             AS NUMERIC(8,2))
        AS PriceAdj,
        PQ.DBID,
        P.IsGun
    FROM
        PQ
     INNER JOIN
        products P ON PQ.UPC = P.UPC

Here is a snippet of the results:

UPC             Price1      Price1Avg   UsingVar    PriceAdj    DBID  IsGun
942000899195    14.9900     14.990000   12.54       15.00       1       0
980420671300    29.9900     29.990000   12.54       30.00       1       0
980420671310    29.9900     29.990000   12.54       30.00       1       0
980426713020    29.9900     29.990000   12.54       30.00       1       0
980426713120    29.9900     29.990000   12.54       30.00       1       0
000998622130    319.0000    319.000000  13.00       319.00      1       1
000998624730    314.0000    314.000000  13.00       314.00      1       1
000998624970    419.0000    419.000000  13.00       419.00      1       1
008244284754    1015.0000   1015.000000 13.00       1015.00     2       1
010633012288    267.0000    267.000000  13.00       267.00      6       1

And here is the data it comes from vProducts_PriceQty_Union:

UPC             Price1  Price2  Quantity    DBID
942000899195    14.9900 0.0000  2.00        1
980420671300    29.9900 0.0000  3.00        1
980420671310    29.9900 0.0000  1.00        1
980426713020    29.9900 0.0000  2.00        1
980426713120    29.9900 0.0000  1.00        1

As you can see from the first five, where IsGun = 0, the first CASE expression using the fixed variable returns the UsingVar value as what we would expect, 12.54. And for the last five, it also returns the value we would expect, 13.

But in the second CASE expression (exactly the same logic), the PriceAdj uses the CEILING function on every single one of them, regardless of whether IsGun = 1 or not.

Why isn't the query returning the expected results?

In some of the tables used for the union view the data types for Price1 and Price2 were smallmoney and decimal(8,2). I have since changed them all to be decimal(8,2), but that did not affect the results.

To reproduce the problem:

SELECT *, (CASE
    WHEN IsGun=1 THEN CEILING(Price1Avg)
    ELSE Price1 END)
FROM (
    SELECT UPC, IsGun, Price1,
           AVG(CAST(Price1 AS numeric(8, 2))) OVER (PARTITION BY UPC) AS Price1Avg
    FROM (
        VALUES ('A', 0, 14.99),
               ('B', 0, 29.99),
               ('C', 1, 319.00),
               ('D', 1, 314.00)
        ) AS x(UPC, IsGun, Price1)
    ) AS sub;

What happens here is that CEILING(PQ.Price1Avg) produces a numeric(38, 0).

According to the documentation, the output type of CEILING() is of the same base datatype as the input, although the scale (the number of decimals) may change, which is what happens here.

The AVG() function, in my tests, returns numeric(38, 6).
The CEILING() function on that column, however, outputs numeric(38, 0):

To verify:

SELECT CEILING(CAST(123.45 AS numeric(38, 6)))

As a workaround, you could explicitly convert the output of the CEILING() function, which should give you the correct results:

SELECT *, (CASE
    WHEN IsGun=1 THEN CAST(CEILING(Price1Avg) AS numeric(8, 2)) -- Explicit CAST.
    ELSE Price1 END)
FROM (
    SELECT UPC, IsGun, Price1,
           AVG(CAST(Price1 AS numeric(8, 2))) OVER (PARTITION BY UPC) AS Price1Avg
    FROM (
        VALUES ('A', 0, 14.99),
               ('B', 0, 29.99),
               ('C', 1, 319.00),
               ('D', 1, 314.00)
        ) AS x(UPC, IsGun, Price1)
    ) AS sub;


Reasons for a session got executing the same SQL for hours I've found a situation where some sessions got executing the same sql_id (select) for hours (the most was 32 hours). all sessions was executing the same sql_id.. Looking in the database I didn't find any wait event holding the execution. Is this a application issue? or most likely in database side?

This is just too generic. From what you provided us, it is impossible to tell.
A few possible reasons:

network wait (waiting for a dblink connection that is already dead)
blocking wait (another transaction blocks these sessions)
programmed wait (DBMS_LOCK.SLEEP)
or session is not in wait at all but uses the CPU (inefficient PL/SQL code or SQL code/execution plan or session is simply spinning due to bug)


Which performance counters can you look at on a SQL Server Instance to determine it's performance and over all health? I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the experts in the field.

One of my questions is:

Which performance counters can you look at on a SQL Server Instance to determine it's performance and overall health?

Particularly I am interested in the threshold values when good becomes bad.

Jamil Young
Eindhoven
The Netherlands

Here's my Perfmon tutorial for SQL Server: http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

For more counters and thresholds, here's a poster we did when I was at Quest: http://www.quest.com/documents/landing.aspx?id=11635


Syncronizing process difference between replication and alwayson high availability Just wondering if SQL Server use different technology for Transactional Replication and Alwayson High Availability or is it same behind the scene.
Basically I need to know if SQL Server use same or different technology(protocol/process) for Replication and Synchronizing the secondary replicas in High Availability in SQL Server 2016.
Thanks in advance.

Transactional Replication uses the TDS protocol and applies changes using SQL.  The connections are established from the Distributor for Push Subscriptions, and from the Subscriber for Pull Subscriptions.

AlwaysOn Availability groups use TCP/IP connection between the replicas using the Database Mirroring Endpoint.  The connections are established from the instances hosting the Secondary replicas to the instance hosting the Primary replica.  But as the Primary replica can move, every server hosting a replica needs to be able to connect to every other one.


UPDATE only if COUNT from another table is not zero I update a table by counting the FK from another table as

UPDATE table1
SET number = number +
(
SELECT COUNT(*) FROM table2
WHERE  table1.id=table2.id
)

table1 is a big table, and I want to make the UPDATE only if COUNT has found something. In other words, I do not want to make the UPDATE for number=0.

Derive a table of counts by id from table2, join the results to table1 and use that join in the UPDATE statement:

UPDATE
  table1 AS t1
  INNER JOIN
    (
      SELECT
        id,
        COUNT(*) AS cnt
      FROM
        table2
      GROUP BY
        id
    ) AS t2 ON t1.id = t2.id
SET
  t1.number = t1.number + t2.cnt
;

The above statement will affect only the table1 rows that have matches in table2.


Made changes to my.cnf and mysqld wont restart - no backup I thought I'd be clever and update my.cnf file to add ft_min_word_len=2 and didn't back it up first. I will never make that mistake again. Luckily, our project is still in production and no harm done.

I have no clue as to what I have done wrong in that file, as I cannot see any error logs (/var/log/mysqld.log). MySQL was running fine before I touched the config file - well, I assume it was.

By running the following command and restarting mysqld (service mysqld restart):

mv /etc/my.cnf /etc/my.cnf.backup

I got it up and running again - so that proves the config file is corrupt somehow. As soon as I put it back and restart the service it fails to start again.

This is probably not much help but this is my terminal screen showing the my.cnf file edited:

[mysqld]
local-infile=1
atadir=/var/lib/mysql
ft_min_word_len=2
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

innodb_buffer_pool_size=2M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size=2M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2

I can't see from this is what could have gone so wrong. I have tried removing the line I added, obviously.

What are my options here? How can I restore the original config file?

atadir=/var/lib/mysql

should be

datadir=/var/lib/mysql


Difference in space used When reviewing the space used by datafiles in SQL Server, there is a large difference betweens what SQL reports and Windows.

How can this be explained?

No different.

Space Reserved for your MDF file: 100.21 GB, which is equal to 105,082,944 KB (105,082,944 / 1024 / 1024 = 100.21)

Space Reserved for *_1.ndf: 148.62 GB, which is equal to 155,843,968 KB (155,843,968 / 1024 / 1024 = 148.62)

The numbers match up just fine.  The Operation System is going to see Space Reserved, and that's how you would need to make your comparison.

Space used is going to be how much space is used within the database file, but Windows doesn't know or care about that.  The allocation and space reserved is the significant part reported by SQL Server which is what represents the footprint on the file system.


Which event to look for User Mapping changes We have been having issues where login's are having their user mapping changed unexpectedly and I am looking to have an alert fire off whenever that happens. Is there a way for me to capture that? I have been able to create a trigger for altering a login or changing their server roles, but not the user mapping itself.

Edit
To be more clear, I am referring to database role memberships for users is what changing unexpectedly.

I found what I was looking for. I needed to check for the trigger event types ADD_ROLE_MEMBER and DROP_ROLE_MEMBER in my trigger at the ALL SERVER level.

I'll leave this up in case anyone else has a better solution


mssql server set up fail with errror on debian linux I was trying to install SQL server on my Debian Linux. Installation part went well but when I tried to run setup file I am getting following error message. Is there any way to fix this issues. Thanks

File "/opt/mssql/bin/../lib/mssql-conf/mssql-conf.py", line 126, in <module>
exit(mssqlconfhelper.setupSqlServer())
File "/opt/mssql/lib/mssql-conf/mssqlconfhelper.py", line 441, in   setupSqlServer
ret = checkInstall()
 OSError: [Errno 2] No such file or directory

There are few other lines too but most of them are something similar to above and related to .py files.

I was istalling it on a docker container based on the debian:buster image,and i had the following error:
root@25d7455d0dfc:/# /opt/mssql/bin/mssql-conf setup
Traceback (most recent call last):
  File "/opt/mssql/bin/../lib/mssql-conf/mssql-conf.py", line 361, in <module>
    main()
  File "/opt/mssql/bin/../lib/mssql-conf/mssql-conf.py", line 357, in main
    processCommands()
  File "/opt/mssql/bin/../lib/mssql-conf/mssql-conf.py", line 262, in processCommands
    COMMAND_TABLE[args.which]()
  File "/opt/mssql/bin/../lib/mssql-conf/mssql-conf.py", line 75, in handleSetup
    mssqlconfhelper.setupSqlServer(eulaAccepted, noprompt=args.noprompt)
  File "/opt/mssql/lib/mssql-conf/mssqlconfhelper.py", line 971, in setupSqlServer
    if not checkInstall():
  File "/opt/mssql/lib/mssql-conf/mssqlconfhelper.py", line 941, in checkInstall
    return runScript(checkInstallScript, True) == 0
  File "/opt/mssql/lib/mssql-conf/mssqlconfhelper.py", line 930, in runScript
    return subprocess.call([sudo, "-EH", pathToScript])
  File "/usr/lib/python2.7/subprocess.py", line 172, in call
    return Popen(*popenargs, **kwargs).wait()
  File "/usr/lib/python2.7/subprocess.py", line 394, in __init__
    errread, errwrite)
  File "/usr/lib/python2.7/subprocess.py", line 1047, in _execute_child
    raise child_exception
OSError: [Errno 2] No such file or directory

So visited the python script at line 930:
vim +930 /opt/mssql/lib/mssql-conf/mssqlconfhelper.py
And I saw this if condition:
if (runAsRoot):
    if(checkSudo() == False):
        printError(_("Elevated privileges required for this action. Please run in 'sudo' mode."))
        return (errorExitCode)
    return subprocess.call([sudo, "-EH", pathToScript])
else:
    return subprocess.call([pathToScript])

Since I'm running as root and it enters the first condition 'runAsRoot' the script run the following return subprocess.call([sudo, "-EH", pathToScript])
I changed that line to:
return subprocess.call([pathToScript])
After that, the setup command worked for me.
/opt/mssql/bin/mssql-conf setup


Getting catagory title total number of questions and answers order by number of questions I have three tables. Which is in at this fiddle,
http://sqlfiddle.com/#!9/4cadb2
Which is like this?.
CREATE TABLE `user_answers` (
  `id` int(250) NOT NULL,
  `question_id` varchar(250) NOT NULL,
  `user_id` int(250) NOT NULL,
  `answer` text NOT NULL,
  `date` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `user_questions` (
  `id` varchar(250) NOT NULL,
  `user_id` int(250) NOT NULL,
  `note_id` int(250) NOT NULL,
  `title` text NOT NULL,
  `question` text NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `material_univarcity_list` (
  `id` int(100) NOT NULL,
  `name` varchar(50) NOT NULL,
  `about` mediumtext NOT NULL,
  `date` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I want to get a resultant table which has count - Name(Name is from material_univarcity_list) Total number of questions to that particular name. Here id of material_univarcity_list table is the note_id of user_questions and I want total number of answers to that particular catagory here question_id is the id of user_questions. How can I do this?
My expected result is like
no|catagory        |number_of_questions |number_of_answers
 1|DBMS 5th sem ISE|            2       | 3
 2|DBMS 6th sem ISE|            1       | 1

How can i do this?

SELECT @no := @no + 1 AS no
  ,catagory
  ,number_of_questions
  ,number_of_answers
FROM (
SELECT c.title AS catagory
    ,COUNT(DISTINCT q.id) AS number_of_questions
    ,COUNT(a.id) AS number_of_answers
FROM material_univarcity_list_content AS c
LEFT JOIN user_questions AS q ON q.note_id = c.id
LEFT JOIN user_answers AS a ON a.question_id = q.id
GROUP BY c.title ) AS t1
CROSS JOIN (SELECT @no := 0) AS t2
ORDER BY number_of_questions DESC;

Since you use MySQL 5.7 which doesn't support window functions like ROW_NUMBER you need to use user variables to emulate it.


PostgreSQL pattern to split a shared-database into a database-per-service pattern What would be your recommendation to move from a shared-database pattern, to a database-per-service pattern. In a production database with as little downtime as possible?

This would be only considering the database splitting part, the application logic split would be a different concern.

Pattern: Database per service

That's a horrible idea. PostgreSQL has schemas..

I suggest ignoring that author entirely. That whole article is trash.

  Development time coupling - a developer working on, for example, the OrderService will need to coordinate schema changes with the developers of other services that access the same tables. This coupling and additional coordination will slow down development.

How does two developers developing in different schemas vs database slow down or speed up anything? The only advantage is the planner can make use of the indexes and you can do cross-schema joins and operations.

  Runtime coupling - because all services access the same database they can potentially interfere with one another. For example, if long running CustomerService transaction holds a lock on the ORDER table then the OrderService will be blocked.

That's only possible if "CustomerService transaction" uses "OrderService". If it does not use "OrderService" you can not have that problem. Moving to a configuration, such as splitting the data into discrete databases, that makes such a thing impossible doesn't solve this problem. It just gives you more work to get there, and to get out of that mess after you're there.

  Single database might not satisfy the data storage and access requirements of all services.

What does that even mean? In any interpretation I can conjure it's even wrong. A single database in PostgreSQL can store different tables on different disks, but I'm not sure what the author even means.


Error when restoring database from disk using a variable I have the following query in SQL Server 2014. The backup of the database completes in approx 150 seconds but the restore/verify step fails with an error.

Can you tell me where I'm going wrong?

I don't understand why I can save the backup using a variable, but I'm unable to restore using the same variable.

Msg 137, Level 15, State 2, Line 36
Must declare the scalar variable "@BackupPath".
Msg 319, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Line 36 is the RESTORE VERIFYONLY line.

DECLARE @BackupPath varchar(1000)
SELECT @BackupPath = (SELECT N'F:\Backups\DBNAME_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '.bak')
PRINT @BackupPath

BACKUP DATABASE [DBNAME] TO  DISK = @BackupPath WITH COMPRESSION, NOFORMAT, NOINIT, NAME = N'DBNAME-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
DECLARE @backupSetId as int
SELECT @backupSetId = position from msdb..backupset where database_name=N'Partner' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DBNAME' )
IF @backupSetId is null
BEGIN
       RAISERROR(N'Verify failed. Backup information for database ''Partner'' not found.', 16, 1)
END
RESTORE VERIFYONLY FROM  DISK = @BackupPath WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

Try removing the 'GO' statement.

Referencing the documentation for SQL Server Utilities Statements - GO

  The scope of local (user-defined) variables is limited to a batch, and
  cannot be referenced after a GO command.


ERROR: ROWS must be positive Why the error when I run the below in pgAdmin 4 query editor?  I copied the create statement in one database and trying to move to a new server.

So I right clicked the function in pgAdmin and selected from the menu Scripts --> CREATE Script the generate the below code.  Copied and executed into a new database.

-- FUNCTION: public.osmtrip_format(bigint, numeric, numeric)

-- DROP FUNCTION public.osmtrip_format(bigint, numeric, numeric);

CREATE OR REPLACE FUNCTION public.osmtrip_format(
    num bigint,
    length numeric,
    tolerance numeric)
RETURNS bigint
    LANGUAGE 'sql'
    COST 100
    IMMUTABLE
    ROWS 0
AS $BODY$

    SELECT
        CASE WHEN $1=0 OR $2<=0.7*$3 THEN NULL ELSE $1 END;

$BODY$;

ALTER FUNCTION public.osmtrip_format(bigint, numeric, numeric)
    OWNER TO gis;

ERROR:  ROWS must be positive
SQL state: 22023

From the documentation about CREATE FUNCTION:

  result_rows
  A positive number giving the estimated number of rows that the planner should expect the function to return. This is only allowed when the function is declared to return a set. The default assumption is 1000 rows."

ROWS 0 is not allowed even in those functions (should be positive).

Since the function is not returning a set, you need to remove that ROWS n.


Will a SQL Server 2008 R2 MDF file work on SQL Server 2016 with Windows Server 2016? We release our database as a .MDF file taken from a SQL Server 2008 R2 server. I have a client who is planning to move to SQL Server 2016 on a Windows OS 2016 and is asking if our .MDF can be restored as a database on their SQL Server 2016 and will it work with Windows OS 2016.

I know that SQL Server 2016's lowest compatibility mode is 100 which is SQL Server 2008 but will it work on Windows OS 2016?

This article makes me feel that it wont https://www.sqlskills.com/blogs/glenn/operating-system-support-for-sql-server-versions/

The article you reference merely states what version of the SQL Server engine is supported on the various operating systems.

You should not encounter any problems attaching (you don't restore) your mdf file to the SQL Server 2016 instance running on Windows 2016.

Note: During the attach, the database will be upgraded to SQL Server 2016 and can never be restored back to a SQL Server version that is prior to 2016.


Slow query performance of simple SELECT query (Postgresql 9.6) In a app called 'Links', users post links of interesting content they've discovered recently (and other vote on the said posts).

These posted links are saved in a links_link table in my postgresql 9.6.5 DB.

One innocuous looking SELECT query on the links_link table is consistently showing up in slow_log. It's taking longer than 500ms, and is ~10X slower than what I'm experiencing in most other postgresql operations.

Here's an example of the corresponding SQL from my slow log:

LOG:  duration: 8648.676 ms  statement:

SELECT "links_link"."id",
       "links_link"."description",
       "links_link"."submitted_on",
       "links_link"."reply_count",
       "links_link"."net_votes"
FROM   "links_link"
WHERE  "links_link"."submitter_id" = 811645
ORDER  BY "links_link"."id" DESC
LIMIT  1

See the the explain analyze results: https://explain.depesz.com/s/Xp5v

The query ends up filtering 14,331,127 rows according to that!

What I've tried:

My gut feel is that Postgres bases this query plan on misleading statistics. Thus, I've run VACUUM ANALYZE on the said table. However this hasn't changed anything.

Being an accidental DBA of sorts, I'm looking for some quick expert guidance on the subject. Thanks in advance and apologies for the noob question (if it is).

Edit:

Here's the entire output for \d links_link:

                                         Table "public.links_link"
        Column        |           Type           |                        Modifiers
----------------------+--------------------------+---------------------------------------------------------
 id                   | integer                  | not null default nextval('links_link_id_seq'::regclass)
 description          | text                     | not null
 submitter_id         | integer                  | not null
 submitted_on         | timestamp with time zone | not null
 rank_score           | double precision         | not null
 url                  | character varying(250)   | not null
 cagtegory            | character varying(25)    | not null
 image_file           | character varying(100)   |
 reply_count          | integer                  | default 0
 device               | character varying(10)    | default '1'::character varying
 latest_reply_id      | integer                  |
 which_photostream_id | integer                  |
 is_visible           | boolean                  | default true
 net_votes            | integer                  | default 0
Indexes:
    "links_link_pkey" PRIMARY KEY, btree (id)
    "links_link_latest_reply_id_idx" btree (latest_reply_id)
    "links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
    "link_whichphotostreamid_fkey" FOREIGN KEY (which_photostream_id) REFERENCES links_photostream(id) ON UPDATE CASCADE ON DELETE CASCADE
    "links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_link_id_fkey" FOREIGN KEY (which_link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_vote" CONSTRAINT "links_vote_link_id_fkey" FOREIGN KEY (link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photoobjectsubscription" CONSTRAINT "which_link_id_photoobjectsubscription" FOREIGN KEY (which_link_id) REFERENCES links_link(id) ON DELETE CASCADE

I could imagine an index on links_link (submitter_id, id DESC) being of great help here.

CREATE INDEX CONCURRENTLY link_id_submitter_id
                          ON links_link (submitter_id, id DESC);

It can be traversed to quickly find the subtree for submitter_id = 811645. Then the first node can be picked instantly to get the first record ordered by id DESC.