Why is sql_text "select @@trancount" in open but sleeping transaction? Using SQL Server Management Studio, open a query window and execute

BEGIN TRAN

In another window execute

select [text],
from sys.sysprocesses
cross apply sys.dm_exec_sql_text(sql_handle)
where status = 'sleeping'
and open_tran = 1

I see select @@trancount

What's up with this? Who's counting transactions? Is this a Management Studio thing?

This is a SQL Server Management Studio thing. At least SSMS version 13.0.15500.91. This version of SSMS will count the number of open transactions on your behalf using the same connection as your query window.

Here's how I found out,

I connected to another server running 2012 and also saw SELECT @@TRANCOUNT
Using a 2012 version of SSMS, I connected to a variety of servers and saw BEGIN TRAN
I used profiler and saw that SSMS 2016 executes SELECT @@TRANCOUNT after every execution.
Incidentally, SSMS 2016 seems to execute SELECT SERVERPROPERTY('EngineEdition') AS DatabaseEngineEdition before every execution.
BEGIN TRAN turns out to be a bit of a red herring. SSMS 2016 executes these extra queries no matter what gets executed in the query window... even if your batch is just whitespace.
I coded an c# application that uses ado.net and profiled it. It does not execute these extra queries.

@@trancount is per session so you would see a value of 1 if you ran it in the BEGIN TRAN window.

You are seeing a sleeping entry for the first session because it's not actually doing anything.  The transaction is open but no work is being done.  It would be the same thing if you opened a transaction, did a bunch of work (that finished) but never actually closed the transaction.

Ok, I think I understand your question a bit better now.  I saw a BEGIN TRAN the first time I ran your query, then I tried running SELECT @@trancount in the first session.  When I came back to the second session I then saw the SELECT @@trancount like you did.

It's going to show you the very last thing that was run in that first session.


Replicating data between SQL Server 2014 to SQL Server 2005 - temporary solution I will be installing a new SQL Server 2014 database system, which is based on our current SQL Server 2005 database system. The new system will be on a completely new infrastructure when we go live, leaving the current infrastructure available for rollback should we encounter any issues.

The business requires that a rollback option be available for 2 weeks minimum. Therefore in the event of a disaster scenario we would be required to switch back to the SQL Server 2005 database system. So the challenge I have is replicating the data upon going live with the SQL Server 2014 database system.

I am looking for a way to replicate the data (not necessarily the schema) from the SQL Server 2014 system to the SQL Server 2005 system.

I am not really a fan of SQL Server Replication and my preference is not to use this. My understanding is that this is not an option for us anyway due to the versions being too far apart, and therefore not supported.

I want to reach out to the community to find out if any tools are available to achieve this type of replication. It may be possible for us to implement a schema change freeze if this helps in finding the right solution.

Please can you advise what options are available to us, if any? Or should we look to develop a bespoke system internally?

Are you aware of any Microsoft partners that might achieve this for us through a solution already developed?

Thanks in advance for any advice.

Joe

Reading your question and thinking a bit more about a possible solution leads me to recommend transactional replication.

Make sure you do implement a change freeze to your schema.

From BOL :

  For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example, a SQL Server 2000 Publisher can have SQL Server 2008 Subscribers, and a SQL Server 2008 Publisher can have SQL Server 2000 Subscribers.

Based on above truth,

Take backup of your current sql server 2005 database and make sure you have instant file initialization enabled on all servers.
Freeze all the data and schema changes <-- This is your downtime.
Migrate your database from sql server 2005 to 2014 and do all post restore steps.
Repoint your application to the new server instance.
Set up T-Rep from sql server 2014 to sql server 2008R2 (this will be an intermediate server or instance and can be on the same new server if you want).You care about this step since, you want to be on supported version. Make sure you initialize replication manually using tsql.
Now have another layer of replication from sql server 2008 R2 (intermediate) to your sql server 2005 server. Make sure you initialize replication manually using tsql.

Note: If the servers are in same datacenter, then you can even do a snapshot which will be very fast.

Above exercise will be worth, since you want a supported rollback plan. Since your business wants to keep things around for 2 weeks, you should be good once you test above.

Note: I have not tried (as I have not been in a situation wherein I have to replicate data from sql server 2014 to 2005), but it should work (though not supported) theoretically. Give it a try for small database and you will easily be able to gauge the effort required.

Have you looked at perhaps log shipping or differential backups. Basically if the 2005 just needs to be available and on standby these two options will work.

What you need for differential backups is first a full backup from 2014 then restore to 2005 using no recovery then differential backup restores can take place for the data to remain up to date. You will of course always have to restore last full backup before differentials. Let me know if this helps.


How to get last identity row Inserted when using instead of trigger When I insert into tables using instead of triggers, @@Identity, IDENT_CURRENT('Table') and SCOPE_IDENTITY() return null. How I can get the last identity of inserted row?

In your instead of trigger, you definitely can get the inserted value... but not until after you've performed the insert.

USE tempdb;
GO

CREATE TABLE dbo.SmellThis
(
  id INT IDENTITY(1,1),
  name VARCHAR(32)
);
GO

CREATE TRIGGER dbo.SmellThis_First
ON dbo.SmellThis
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ids TABLE(id INT);

    IF NOT EXISTS
    (
      SELECT 1 FROM sys.objects AS o
        INNER JOIN inserted AS i
        ON o.name = i.name
    )
    INSERT dbo.SmellThis(name)
      OUTPUT inserted.id INTO @ids
      SELECT name
      FROM inserted;

    SELECT id FROM @ids;
END
GO

INSERT dbo.SmellThis(name) SELECT 'Remus';
GO

Results:

id
----
1

Now clean up:

DROP TABLE dbo.SmellThis;

As an aside, you should never, ever, ever be using @@IDENTITY or IDENT_CURRENT() anyway. And SCOPE_IDENTITY should be reserved for situations where you know only one row can ever be inserted. A common misconception with triggers is that they fire per row, like in other platforms, but in SQL Server they fire per operation - so a multi-row insert using VALUES(),(),() or INSERT...SELECT - which SCOPE_IDENTITY would you be setting to your variable?

Main Problem : Trigger and Entity framework both work in diffrent scope. The problem is, that if you generate new PK value in trigger, it is different scope. Thus this command returns zero rows and EF will throw exception.

The solution is to add the following SELECT statement at the end of your Trigger:

SELECT * FROM deleted UNION ALL
SELECT * FROM inserted;

in place of * you can mention all the column name including

SELECT IDENT_CURRENT(‘tablename’) AS <IdentityColumnname>


Storing arrays in MYSQL? I've googled. I keep getting answers that have to do with a PHP function named "serialize". I won't be working with PHP, so that solution won't work for me sadly.
I worked with a NoSQL db (MongoDB I think) before, and I basically define a collection as such
images [
    {"title": "Cute puppy", "file"="cute.png", "tags": ["cute", "puppy", "summer", "pool"]}
]

I'm wanting to store multiple values in one row for the tags column (honestly I don't know if I said that right, just began searching about MySQL)
+------------+----------+--------------------------+
| title      | file     | tags                     |
+------------+----------+--------------------------+
| Cute Puppy | cute.png | cute,puppy,summer,pool   |
+------------+----------+--------------------------+

The only idea that's coming to me is if I were to use MySQL to read the row, then just split the tags by a comma, that'd work most likely, but I'd be working with many more "tags" possibly in the millions.
My question really is, is MySQL a good choice if you want to store multiple values in one column?

My question really is, is mysql a good choice if you want to store multiple values in one column?

I would suggest that the answer to this question is no - there is a better choice on all 3 levels for the treatment of arrays in database servers.

1. Simple arrays:

MySQL doesn't really have an array datatype - the closest thing they have is the SET datatype, the functionality of which is very limited. As you note in your question, a search leads to lots of links to PHP code which implements array functionality in the app rather than the db.

PostgreSQL has a an ARRAY datatype with a large range of functions to deal with manipulating, comparing and finding elements in arrays.

2. JSON:

Nowadays, arrays have been largely superseded by JSON - hardly surprising since JSON is basially an array++ (but XML-- :-)). This is true for MySQL and for PostgreSQL.

There are a large number of JSON functions available on both systems - MySQL and PostgreSQL - but more on PostgreSQL. I'll use the terms JSON and JSONB (binary - example is for mongodb but applies to binary JSON generally) interchangeably. MySQL JSON is only binary, PostgreSQL has both - but JSONB is generally regarded as much more efficient. See this post for a good discussion of what PostgreSQL "NoSQL types" are good for. The "NoSQL" is a misnomer; you can do SQL JOINs using these types.

With respect to indexing - MySQL essentially implements this by means of a GENERATED column hack whereas PostgreSQL implements 3 sorts of index (Gin, Btree and Hash - see section 8.14.4. jsonb Indexing here) for JSONB.

Joins can be done between JSON and tables in both MySQL and PostgreSQL.

Overall, it is fair to say that JSON has been around longer and is more mature in PostgreSQL than in MySQL.

Classic SQL:

You can of course implement some "multi-element" functionality by using a classic Associative Entity approach. Associative entities are otherwise known as  joining, bridging, many-to-many or m-to-n tables. This strategy is outlined here with an example using students enrolling in university courses. 1 course can have many students and 1 student can attend many courses. The joining table contains the PRIMARY KEY from both the course and student tables and the PK of the joining table itself is the two together - a student can't be in the same course twice!

In favour of the classic approach, Erwin Brandtstetter (a guy well worth listening to about databases!) pointed out in his answer referring to a JSON solution: (Aside: A normalized DB design with basic data types would be way more efficient for this.). JSON is not a panacea and judgement must be exercised in its use. There's been a lot of bandwagon-hopping in recent years with the NoSQL "Cambrian Explosion" from ~ 2008 - 2015 (Mongodb especially springs to mind). Now virtually all of the NoSQL systems have or are trying to put SQL interfaces and instrumentation into their systems. For my money, if your arrays are relatively simple, stick with the classic approach, otherwise go with JSONB.

SQL wasn't designed for and is not good at manipulating comma-separated lists of values. In any case they are a breach of Rule 2 of Codd's Rules which states that Each and every datum (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.. With .csv lists a further positional parameter is required which SQL doesn't deliver but various systems have "tricks" to get round this. SQL's forté is "slicing and dicing" records made up of small simple atomic datums.

PostgreSQL is the F/LOSS (Free/Libre and Open Source Software) system which comes closest to offering the best for all 3 array methods. MySQL is more widespread and possibly has better inbuilt and 3rd party replication and/or sharding, but PostgreSQL is more standards compliant and has been making strides in those areas recently. It also has superior JSONB functionality.

p.s. - oh, yes, I forgot - welcome to the forum! :-)

Your mongodb will be better choice for that. In mysql there will be a lag for reading operation. Go with mongodb it's management is easy and faster.


Backing up multiple databases to the same point in time Usually when we start the backup we do not allow changes to commit or database will be not be accessible. I mean database will be in single user mode but I want to start the backup and release the db for use. Also, once I start the backup I do not want the ongoing changes to be written to the backup file.
I want to know how I can achieve this in Microsoft SQL Server 2012.Please assist me.

Well, let me explain my problem first. Currently I am setting the database to single user mode until the backup completes. This mode will serve my purpose of avoiding the data changes when backup is in progress. But my application is tied with the multiple databases(each databases are interlinked with each other and there are var dbs which keeps creating on monthly basis). So backing up all these databases has become tedious process and more importantly I have to keep the users out of the system when backup is in progress.

So I am looking for backup mechanism which will meet below mentioned requirements.

Start the backup of all the dbs at a time and release the db for use.
As databases are inter linked to each other I want to have data consistency maintained in backup files. So because of this data consistency requirement I don't want on going changes to be committed to my backup file.

All I want is - backup of all the dbs at a given time.

I am assuming that your task is to create a set of backups where you can restore all of them to a single static (no open transactions) point (all in sync).  Similar to what a quiesce point will do for you in DB2.  Unfortunately there is nothing in SQL Server that does exactly that.  However, assuming your databases are in FULL recovery mode, you can do something similar.

You can create a single MARKED transaction across all of the databases at once.  Then you take a transaction log backup of each database.  Assuming you are keeping up with your log backups (which you should be anyway) log backups don't take very long.  And there is no need to stop anyone from being in the system (except maybe long enough for you to create your marked transaction).

At this point if you need to do a restore then you do a RESTORE STOPBEFOREMARK on each of your databases.  This will restore all of the databases to the same point (based on your transaction).  Note: There is also a STOPATMARK if you want to include that transaction.

If you want an example I've got one in the link above along with quite a bit of additional reading.

I realize this doesn't exactly answer your question but hopefully it solves your problem.

--]---- Begin Code ----[
--–declaring variables
DECLARE @dbname VARCHAR(100) -- database name
DECLARE @bupath VARCHAR(100) -- path for backup location
DECLARE @filename VARCHAR(100) -- filename used for backup files
DECLARE @datestamp VARCHAR(25) -- date used for backup file timestamp
-- specify database backup directory
SET @bupath = 'D:\DB Bkp\'
-- file date formatting
SELECT @datestamp = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE
(CONVERT(VARCHAR(20),GETDATE(),108),':','')
--specify databases to backup
DECLARE db_cursor CURSOR for
SELECT name
FROM master.dbo.sysdatabases
WHERE name not IN ('tempdb') -- excludes these databases, if excluding multiple databases, seprate them by a comma
--backup process
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @filename = @bupath + @dbname + '_' + @datestamp + '.bak'
   BACKUP DATABASE @dbname TO DISK = @filename WITH INIT,
   CHECKSUM; --init overwrites existing files with the same name, and checksum verifies the backup
       FETCH NEXT from db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

for more details: https://blogs.technet.microsoft.com/letsdothis/2013/11/06/backup-multiple-sql-databases-in-a-single-swoop/


How do I write a conditional statement (IF) with MySQL? What is wrong in below simple query? I tried to google it but not found

IF 1 = 1 THEN
  SELECT 1;
ELSE
  SELECT 12;
END IF;

What you want is a CASE expression which is standard-SQL method of implementing a conditional and supported by every major database,

SELECT CASE WHEN 1 = 1 THEN 1 ELSE 12 END;

Or you can use the totally silly and proprietary IF statement

SELECT IF( 1=1, 1, 12 );

This simple query can't be run outside the stored routines as stated by chapter 13.6.5 of the manual. For the single queries IF() function should be used instead. CASE may be a bit confusing because the CASE statement and the CASE operator has exactly the same syntax, inspite of completely different nature.


Store regular MySQL query results in a timeseries DB I'm looking into installing a timeseries database like InfluxDB or Prometheus to handle data for our Grafana monitoring system.

One of the things I like to do is run a couple of MySQL queries every few minutes to gather business metrics (like number of subscribers or application usage metrics).

Documentation for these timeseries databases feature plenty of examples of how to get MySQL performance metrics into their stores but I can't find any that demonstrates how to store data from queries.

Google search results seem to be completely drowned out by the more typical use case of gathering MySQL performance metrics.

Is this at all possible with either InfluxDB or Prometheus and where can I find information on how to set this up?

You can write a simple script, which would periodically execute SQL query and push its results to a TSDB via one of the supported data ingestion protocols. Various TSDBs support various data ingestion protocols: Influx line protocol, Graphite plaintext protocol, OpenTSDB telnet put protocol, JSON, CSV, etc. For example, all these protocols are supported by VictoriaMetrics - see these docs for details.


Incremental backup in postgresql in windows I am developing an application with postgresql as backend. So my requirement is to take the incremental backup either hourly or daily basis.
I have gone through many related articles (like https://coderwall.com/p/jxebnw/incremental-backups-for-postgresql) but i found that postgresql database does not support incremental backup independently. It means that the postgresql needs third party tools like pg_rman in order to get incremental backup. Also the pg_rman tools only works on UNIX not windows.

Is it really so? I mean its hard to believe that the database like postgresql do not support incremental backup.

So my question is do postgresql support incremental backup independently? If yes then what is the procedure for achieving it.

Yes, it is right that the incremental backups are not possible in postgresql.

I don't know if there is an alternative for pg_rman in windows but you can achieve the incremental backup in postgres with the continuous archiving. Which can be later restored with PITR (point in time recovery)

Details on:
https://www.postgresql.org/docs/current/static/continuous-archiving.html


MySQL unknown column in subquery I'm running a fantasy game where the ratings for each player are calculated in the following way:

Each round (gameweek) a player plays zero, one or more games. The rating for that round is the average rating scored in all games (stored in match_player)
The total season points for each player is the sum of all season round ratings.

I'm trying to list the season points for each player with this query:

SELECT
    `player`.`name`,
    COALESCE(
        (
        SELECT SUM(points)
        FROM
            (
            SELECT ROUND(AVG(`match_player`.`points`)) AS `points`
            FROM `match_player`
            JOIN `match` ON `match`.`id` = `match_player`.`matchID`
            JOIN `round` ON `round`.`id` = `match`.`roundID`
            WHERE `match_player`.`playerID` = `player`.`id`
               AND `round`.`competitionID` = 1
               AND `round`.`season` = 2018
               AND `match`.`status` = 'finished'
            GROUP BY  `match`.`roundID`
        ) AS roundPoints
    ), 0) AS seasonPoints
FROM `player`

Byt MySQL is throwing this error:

#1054 - Unknown column 'player.id' in 'where clause'

Is there any way to group by round, calculate average rating and the sum all values for each player?

SQL Fiddle: http://sqlfiddle.com/#!9/bebd2/1

Alternative approach:

SELECT
        `player`.`name`,
        COALESCE(
           SUM(
                (
                  SELECT ROUND(AVG(`match_player`.`points`)) AS `points`
                FROM `match_player`
                JOIN `match` ON `match`.`id` = `match_player`.`matchID`
                JOIN `round` ON `round`.`id` = `match`.`roundID`
                WHERE `match_player`.`playerID` = `player`.`id`
                   AND `round`.`competitionID` = 1
                   AND `round`.`season` = 2018
                   AND `match`.`status` = 'finished'
                GROUP BY  `match`.`roundID`
              )
        ), 0) AS seasonPoints
    FROM `player`

This time, the error returned is:

Subquery returns more than 1 row

http://sqlfiddle.com/#!9/bebd2/2

I've updated the SQL Fiddle with the reference answer

1) Referenced answer seems to have a misprint: -- Player 1 (Round 1: 5pts ((10+5)/2), Round 2: 6pts), whereas (10+5)/2 = 7.5 (or 8 while rounding to integer).

2) Look at

SELECT `player`.`name`, `match`.`roundID`, ROUND(AVG(`match_player`.`points`)) AS `points`
FROM player
JOIN `match_player` ON `match_player`.`playerID` = `player`.`id`
JOIN `match` ON `match`.`id` = `match_player`.`matchID`
JOIN `round` ON `round`.`id` = `match`.`roundID`
WHERE `round`.`competitionID` = 1
  AND `round`.`season` = 2018
  AND `match`.`status` = 'finished'
GROUP BY `player`.`name`, `match`.`roundID`
;

It fits your reference answer.

Change 'player'.id to 'match'.id like this.

SELECT
    `player`.`name`,
    COALESCE(
        (
        SELECT SUM(points)
        FROM
            (
            SELECT ROUND(AVG(`match_player`.`points`)) AS `points`
            FROM `match_player`
            JOIN `match` ON `match`.`id` = `match_player`.`matchID`
            JOIN `round` ON `round`.`id` = `match`.`roundID`
            WHERE `match_player`.`playerID` = `match`.`id`
               AND `round`.`competitionID` = 1
               AND `round`.`season` = 2018
               AND `match`.`status` = 'finished'
            GROUP BY  `match`.`roundID`
        ) AS roundPoints
    ), 0) AS seasonPoints
FROM `player`


Cost threshold for parallelism and wait stats We have an Stored Proc on our prod server (SQL Server 2014) that takes 3hr 40m (on a Sunday: low server activity) to run (collection job costs on 220 jobs with each requiring 6 SP calls, so 13200 SP calls and then some aggregating)

On my laptop with cost threshold set to default of 5 it runs in 30 mins. If I set cost threshold to 50 on my laptop it goes to 1hr 10mins (1/3 of time it takes in prod server)

If i set cost threshold on prod server to 25 it drops to 1hr 30mins, but wait stat go through the roof. See attached: green is threshold of 50, yellow is threshold of 25

Top waits are CXPACKET and SOS_Scheduler_Yield

I don't have a comparable dev or staging server (CPU's, RAM and Disk), so I play too much with a bunch of settings while i replay a workload

Tow questions

Am I better off with a cost threshold of 25 and the high wait stats and 60% performanc gain. Or will the wait stats cause a lot of issues when the load increases
Any thoughts on where the bottleneck is and how to solve. my uneducated guess would be CPU (from the blue line in the charts, it's not too high (under 70%) but is it that it can't serve enough threads in parallel?

SOS_SCHEDULER_YIELD and CXPACKET indicates that you’ve got contention and that queries are spending a lot of time waiting to get access to a CPU.
Check MAXDOP and Cost Threshold for Parallelism to determine when the query should be parallelized.


What permissions are necessary for truncating a table? I have a SQL account with the following permissions on a database:

The db_executor role you see this account being a member of was created by this script:

CREATE ROLE [db_executor] AUTHORIZATION [dbo]
GO

GRANT EXECUTE TO [db_executor]
GO

When I run a select, update, insert or delete on the table, it works fine. When I try to truncate the table, it gives me this error message:

  Cannot find the object "TableName" because it does not exist or you do not have permissions.

What permission is this account missing?

The best place to look for this information is in books online. The article on TRUNCATE TABLE here indicates:

  The minimum permission required is ALTER on table_name. TRUNCATE TABLE
  permissions default to the table owner, members of the sysadmin fixed
  server role, and the db_owner and db_ddladmin fixed database roles,
  and are not transferable. However, you can incorporate the TRUNCATE
  TABLE statement within a module, such as a stored procedure, and grant
  appropriate permissions to the module using the EXECUTE AS clause.

So ALTER is the minimum permissions required. You can get that as DB Owner, you can get that as DB_DDLAdmin. Or just grant alter.

If you think about what truncate does and how it works, this makes sense, it is a pretty "severe" command and empties the table of data and does it quickly.

As far as I understand it Truncate is not something you can rollback.  So the Begin Transaction/Commit Transaction is unnecessary.


SQL Server replication over internet error I have 2 SQL Servers connected over internet, I can connect (with no problem) from each one to other using SQL Server Management Studio by using publicIP,1433\server as server address.

So, I created a publication on server1 called PUB1 but if I try to create a subscription (from server1) to server 2, I get this error message

  TITLE: Connect to Server

  Can not connect to MyPublicIP,1433 .

  ADDITIONAL INFORMATION :

  SQL Server replication requires the real name of the server to establish a connection. Connections via alias, IP address or other reference are not supported. Enter the real name of the server, " SERVEUR\SERVER ". ( Replication.Utilities )

So, I resolved the problem by creating an alias whith the (expected) name: SERVEUR\SERVER, protocol: TCP/IP, port: 1433, server: MyPublicIP\SERVER

sql replication over internet is working very well.


Non-standard number data types in Oracle I'm not sure that this question belongs to this StackExchange.
I've asked this question on StackOverflow:

Besides the "usual" number data type where precision is greater then scale there are many "non-standard" number data types where scale greater then precision or where scale is negative.
For example
NUMBER(2, 5) means that there are 5 digits in the fractional part, 3
of them is obligatory zeros. NUMBER(2,-6) Here the scale is -6, which
means the value is rounded to millions and the precision is 2, so 2
significant digits can be stored. Can somebody provide examples of
using such data types in practice?

Some clarifications:
I am very interested in the motivation for using such data types (NUMBER(2, 5) and NUMBER(2,-6)).
What tasks can such a table design solve?

It prevent Aoplicatuions from inserting invalid data. P.For example for many currecncies it maks no senes to have more than 2 digits right from the decimal point.


Entity Relationship Diagram for Hotel I tried creating an ERD model for hotel reservation and billing. Are the cardinalities correct, I'm a bit confused about that. And are there any attributes that are not in the right place? If someone could help me and go over this I would appreciate it. Thank you.

This looks mostly complete, though the BookingID field in Guest seems a little pessimistic. What will you do if a guest wants to stay at the hotel again in the future? As you already have a relation between Guest and Booking via GuestID, you can probably remove BookingID from Guest. A similar thing can be done in Bill as, if you know the BookingID then you know the GuestID. So GuestID can be removed from Bill … if you choose, of course. You don’t need to make either change.

Yes the cardinalities look completely fine on this ERD.
I am a bit confused about the "Booking" to "Bill" cardinality (although this could be a gap in my domain knowledge on hotel operations).
In what instances would you have multiple "Bills" per a singular "Booking"?


How to disable instance recovery in ORACLE? I want my DB to stop instance recovery during opening, how to do it?

I want it because it crashes when trying to do instance recovery, I am testing some cases and want to disable it

Is there any possibility?

Instance Recovery is necessary to get the database back to a consistent state after shutdown abort or some other abnormal shutdown event. Oracle is never going to let you open the database in an inconsistent state, so no, there is no possibility that you can disable it. If Oracle is crashing during Instance Recovery you need to get in touch with Oracle Support or revert to a backup.
The concepts guide has essential reading about instances and more particularly what Instance Recovery is and why it is sometimes needed:

If an instance of an open database fails, either because of a SHUTDOWN ABORT statement or abnormal termination, then the following situations can result:

Data blocks committed by a transaction are not written to the data files and appear only in the online redo log. These changes must be reapplied to the database.

The data files contains changes that had not been committed when the instance failed. These changes must be rolled back to ensure transactional consistency.

Instance recovery uses only online redo log files and current online data files to synchronize the data files and ensure that they are consistent.

If the Instance Recovery crash is caused by a corrupt datafile you could mount the instance, drop the offending datafile and then open the database - but you will lose the tablespace that file is in.

You can perform inconsistent OPEN, but it is HIGHLY not recommended :)


Run SSIS Package as different user I have been googling for ages and messing around with the package properties but I can't seem to be able to get my package to run as a different user than my current windows login from within Visual Studio.

In particular I want a connection manager to run as a different windows account and not a SQL account.

Can anyone advise?

Amusingly enough, you have the answer in your question, specifically, run as. RunAs and in case MS butchers links again, RunAs

RunAs allows you to run a program as another user. You can't automate the entry of credentials, which is a pain and you won't be able to see your password but it does work. It also fails in situations like SSRS development when you attempt to preview a report, that is spawned in a separate process which does not inherit the credentials. But, I had no issues with it from an SSIS perspective.

runas /netonly /user:OtherDomain\billinkc "devenv.exe /nosplash"

where "devenv.exe" is the full qualified path to your Visual Studio version and whether you have a 64 bit OS. On my machine, that could be

C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe
C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe
C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\devenv.exe
C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe

You can use Configuration files in SSIS.  You have to enable your package to use them and then edit the .config file to override the package connection properties with the properties (username/password/Server/etc.) you want it to use.


PLS-00049 bad bind variable when compiling a trigger I'm trying to implement a dedupe trigger to a table.

CREATE OR REPLACE TRIGGER USER.TBL_ACTION_DEDUP
BEFORE INSERT
ON USER.ERRORTABLE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
   IF (:OLD.Identifier = :NEW.Identifier) THEN
      SELECT :OLD.tally + 1 INTO :NEW.tally FROM DUAL;
   END IF;
END TBL_ACTION_DEDUP;
/

Error is:
On line:  2
PLS-00049: bad bind variable 'OLD.IDENTIFIER'

you should remove fragment: "USER.". Properly is:

CREATE OR REPLACE TRIGGER TBL_ACTION_DEDUP
BEFORE INSERT
ON ERRORTABLE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF (:OLD.Identifier = :NEW.Identifier) THEN
  SELECT :OLD.tally + 1 INTO :NEW.tally FROM DUAL;
END IF;
END TBL_ACTION_DEDUP;
/


Showing data in a table horizontally I have a table that stores sensor data (in SQLite) and looks like this;

TABLE Timestream:
idTimestream PK autoincrementing integer,
time int not null,
value float not null,
idSensor integer not null FK

Some but not all sensors have matching times, but I'll only be considering those that do. What I want to do is rearrange the table into the following format,based on a set of sensors listed in the query, not whole set from the table:

Time Sensor1 Sensor2 etc.

I was thinking of Creating a temporary table and then inserting time and the first column, then doing a join on the time for subsequent queries, and finally selecting the whole lot. This doesn't sound too efficient though and I was wondering if there was a better way?

It looks to me like you are trying to 'pivot' the data, which can be done with multiple case statements:

test data:

create table timestream(
  idTimestream integer primary key autoincrement
, time int not null
, value float not null
, idSensor integer not null);

insert into timestream(time, value, idSensor) values(1,0.1,100);

insert into timestream(time, value, idSensor) values(1,0.2,101);

insert into timestream(time, value, idSensor) values(1,0.3,102);

insert into timestream(time, value, idSensor) values(2,0.4,101);

query:

select time, sum(case idSensor when 100 then value end) as s100,
             sum(case idSensor when 101 then value end) as s101,
             sum(case idSensor when 102 then value end) as s102
from timestream
group by time;

time | s100 | s101 | s102
:--- | :--- | :--- | :---
1    | 0.1  | 0.2  | 0.3
2    | null | 0.4  | null

dbfiddle here

Try creating a view.  This will allow you to pull the data you want, in the order you want without having to do a join with a temp table.  One thing to note is i don't believe sqlite will allow you to write to a view, so if you want to do more than just look at the data, you may have to use the view to create a temp table.


Who changed the size of the transaction log? Can I know, who and when changed size of database transation log?

Most likely you did. Most cases database files, including transaction log, are configured for auto-growth. Auth-growth enables the file to increase its size as needed, any time is needed. The trigger for growth can be any activity that requires log space. The activity that triggered the growth is most cases ordinary, normal activity. The true problem is why is the log growing. Most times is simply lack of log truncation, see Factors That Can Delay Log Truncation. Other time it can be a single event that generates a lot of log and requires growth. But most often is just bad maintenance (lack of truncation).

To shrink the log now, see How to Shrink the SQL Server log.

If the log was explicitly increased in size, the event is likely captured in the default administrative trace.

No. Point. This stuff is not tracked in SQL Server.

You can possibly see when (there is an event log that is available via standard reports) and see if it it was someone or auto growth (as per settings), but if it was a user, no - it is not tracked who issued the command.

That is pretty much never needed in any professional setup. You know, where you do not have clueless people do random changes. In a professional setup you have change tickets first, so you can see when a change was planned, implemented.


MySQL case sensitive table names on Linux Moving a MySQL database from Windows to Linux I have the problem that on Linux the names of the tables are case sensitive. This is a problem, because the Java application that I am developing can't find tables.

I have changed my /etc/mysql/my.cnf file adding the row:

lower_case_table_names=1

But that did not change anything.

My server version is:
5.1.61-0ubuntu0.11.10.1 (Ubuntu)

How can I configure MySQL to ignore case in table names?

Just altering the lower_case_table_names setting isn't enough.  It needs to be done before you import your database(s).

The MySQL 5.1 documentation lists a procedure for moving between Windows and Linux/UNIX.  This will ensure that your desired rules for enforcing case sensitivity are followed.  Take a look and verify that you did these steps in the correct order:

  To convert one or more entire databases, dump them before setting
  lower_case_table_names, then drop the databases, and reload them after
  setting lower_case_table_names:

  1 - Use mysqldump to dump each database:

  mysqldump --databases db1 > db1.sql

  mysqldump --databases db2 >
  db2.sql

  ... Do this for each database that must be recreated.

  2 - Use DROP DATABASE to drop each database.

  3 - Stop the server, set lower_case_table_names in the [mysqld] section of your \etc\mysql\my.cnf file, and restart the server.

  4 - Reload the dump file for each database. Because lower_case_table_names
  is set, each database and table name will be converted to lowercase as
  it is recreated:

  mysql < db1.sql

  mysql < db2.sql

File in etc/my.cnf
Find this # The MySQL server [mysqld]
and set lower_case_table_names = 1

There is no need to drop db. It works so first check with this.
Reason is default value of lower_case_table_names =1 for Windows.


Mandatory to use "Three Parts Name" in T-SQL statements of SP Using three parts name in SQL statements is a good practice it helps in object identification and performance improvement as well. It can also help for many other purposes like if you want to move SPs,View Functions to different database with same T-SQL statement..

So I wanted to create a policy which can enforce developers to use "Three Part Naming" when they'll create SPs, Views and functions.

Is there any inbuilt policy or way in SQL Server 2016 which can help me out?

Response will be appreciated.. :)

Using three-part names in queries that reference objects in the current database is a terrible practice and you should create a rule against it.  The most frequent problem with it is that it prevents you from having two instances of your application database on a single server, and can lead to unintentional cross-database access.
Using two-part names in dynamic SQL has some, slight, performance benefit as the object name resolution doen't have to check the user's default schema and then the dbo schema.  But in a view or stored procedure is not as helpful as object name resolution uses the object's schema.  And in the paradigm case of a proc/view in dbo referencing a table in dbo it doesn't help at all.

Please follow this link. Might help you.
https://www.mssqltips.com/sqlservertip/2298/enforce-sql-server-database-naming-conventions-using-policy-based-management/


Is there a valid reason to alias a column to the exact same name/case? I inherited some code like this:

SELECT
  Col_1,
  Col_2 Col_2,
  Col_3
...

Does the Col_2 alias actually accomplish anything, or is it completely unneeded?

In the code as presented in your question, no there is no need but it will not cause any problems. The alias could be there because the column name used to be different and has been renamed, with the references to it being updated by some automated method.

I have sometimes deliberately aliased columns to the same name just to keep line syntax alignment when working with many columns some of which are aliased and some of which are not like so:

SELECT tbl1.col1          AS column1
     , tbl1.anothercolumn AS column2
     , tbl1.column3       AS column3
     , tbl2.somevalue     AS column4
FROM   ...

Here column 3 has an alias specified that is does not need as the output would have the desired name anyway.

There is no need to alias a column name unless you are changing it to something besides what the column name in the database is.

This select

SELECT
  Col_1,
  Col_2 Col_2,
  Col_3
...

And this selct

SELECT
  Col_1,
  Col_2,
  Col_3
...

Will both return the same column names to the end user so in this case there is no need to add the alias as it will have no impact on the returned results or column names.

Regarding duplicate column names as brought up by sp_BlitzErik this will depend on your rdms software as depending on what you are using it will automatically change duplicate column names so that the result set does not have any duplicate column names. For example Oracle (11g) adds an _1, _2 for the 2nd, 3rd column and follow the pattern for each duplicate column,


Unsure what this relationship is called Essentially, I have googled around but I am unsure what would this type of relationship be called. Table 3 contains a field [Labelled as STRING], that has multiple 1 to many relationships to Table 2 [Labelled as STRING and STRING2, STRING connects to both]. However, Table 1 has a 1 to many relationship with Table 2 through STRING2.
I am thinking that Table 3's STRING is erroneously linked to Table 2's STRING2, but I am unsure if this is intended, and whether if these types of relationships do exist. I am thinking that STRING could be a composite of STRING and STRING2 in table 2, but I am sure that breaks a few db rules (e.g.atomicity), does anyone have any pointers I should read up on?

What you're describing looks like a many-to-many relationship between table1 and table3 through table2.
Direct many-to-many relations between two tables are illegal and require an intermediate table.
Typically, many-to-many relations are usually verbs in natural language with T1 and T3 being nouns, and T2 being a verb - e.g. Customer (T1) Buys (T2) Books (T3). T2 should have both the PK of T1 and T3 as FKs in its own table to ensure the data matches across the three tables.
The design you have is problematic as strings are not unique, and therefore you can end up joining MJones in T1 to MJones in T3 with no assurance that the two individuals are the same person.
This type of error is acceptable in say a marketing database where some errors may be non-critical, and typically this is a good use in a non-relational database e.g. Big Data. However, in a database where accuracy is paramount e.g. a database recording medicines administered to patients this type of error would have disastrous consequences.
If you use a unique key in your design you will always ensure data consistency - strings are potentially problematic.


SQL actually executed as planned in ORACLE? I wonder if SQL is definitely executed as execution plan.

I mean.. is the actual SQL execution process always consistent with execution plan, i.e. does the same SQL always give the same plan?

The short answer is that no, the same SQL doesn't automatically lead to the same execution plan.

For example, one may have a small table and the optimizer may realise that there's no point in using an index since an FTS (Full Table Scan) would be cheaper than trawling through an index and then performing lookups.

The "No" answer is the reason that Oracle introduced Outlines to implement Plan Stability. Many posts on the Oracle forums concern poor DBAs complaining about the fact that their system worked fine on Tuesday but that on Wednesday it had slowed right down. Further investigation showed that this was due to a critical change in the Execution Plan for a particular query.

What Plan Stability is basically doing is while accepting that some plans may be sub-optimal, the DBAs/Devs would prefer a stable system over one which might change catastrophically in an unpredictable fashion.

Some good information about this can be found here and here. The oracle-base site is one of the best sources of Oracle information and Tanel Poder is a very big hitter in the Oracle world.

Basically, yes in the short term. If it were intending to go a different way it would generate a different plan, and no database (to my knowledge) backtracks and finds a different plan once it has started processing.

If you are looking at a plan generated before a query was run then any statistics (rows, number of iterations of lopped constructs, and so forth) will be estimates, so these are likely to differ from what exactly happens during execution. This is because it would be to time and IO expensive to scan/analyse everything in exquisite depth in order to make the stats exact before actually running the query, and this is why query engines sometimes get things wrong and take a less than optimal path (needing index hints and such to encourage/force them in a better direction) - the analysis required (and/or stats that would need to be stored) to always get the most efficient solution would be impossibly expensive in the general case.

There is one huge caveat when considering the longer term though: a plan generated now may differ from a plan generated for the same query later today, even without structural changes (changes to indexes and such) because changes in data update the recorded statistics so may alter what the planner sees as the best way to go. It is even possible for the plan to change from one instant to the next (such as between you getting the actual plan for the last query asking for it to be executed again) but this is much rarer as the more intelligent database engines (Postgres, MS SQL Server, Oracle, ...) cache generated plans to reduce repeated analysis.

If you explicitly tell the engine not to cache a plan (or it decides not to for some reason) then the plan used could potentially change between you asking for an estimated plan and actually running a query. It could also change between you getting an actual plan output from one instance of running the query and running the same thing again, but again this is unlikely as the stats used to decide the plan are unlikely to have changed significantly.

Pinch of salt time: most of my in-depth experience is with MS SQL Server, so there may be details in other engines I am inaccurate on.


MySQL Workbench run SQL script file Character Set encoding issue Upon running a .sql file for creating a schema in MySQL Workbench 6.1, it prompts a window saying it cannot decide the right character set

What's the proper thing to do?

If it is the "world" database downloaded from here, it is in latin1 encoding. Use that for importing.

UTF-8 (utf8 or utf8mb4 in MySQL) has become the de facto standard nowadays for operating systems and global communications (except in some countries and specific areas of knowledge), and utf8 is now the default encoding in MySQL (since 5.5). See also http://utf8everywhere.org

latin1 was the 1 byte representation of characters for the western languages. MySQL supports several encodings, you can see them by executing:

SHOW CHARACTER SET;

You asked in a comment:

  Given a *.sql file, how do I unambiguously find out its encoding?

If you used mysqldump to generate it, it usually presets the encoding used at the beginning like this (check the "set names" execution), so that it is imported correctly, without needing to specify it.

In general, it is impossible to know the encoding used of any arbitrary text file, but you can try to auto-detect it with tools like the standard GNU file tool on Amazon Linux file -i <filename>. There should be tons of tools like that, even more specialised.

Just close workbench and Restart MYSQL server from local services.
my issue resolved this way...:)


Huge slowdown to SQL Server query on adding wildcard (or top) I've got a zoo of 20 million animals which I track on my SQL Server 2005 database. About 1% of them are black and about 1% of them are swans. I wanted to get details of all the black swans and so, not wanting to swamp the results page I did:

select top 10 *
from animal
where colour like 'black'
and species like 'swan'

(Yes, inadvisedly those fields are freetext but they are both indexed). It turns out we have no such animals as the query returns an empty set in about 300 milliseconds. It would have been about twice as fast if I'd used '=' rather than 'like' but I have a premonition the latter is about to save me some typing.

It turns out the head zookeeper thinks he may have entered some of the swans as 'blackish' so I modify the query accordingly:

select top 10 *
from animal
where colour like 'black%'
and species like 'swan'

Turns out there are none of those either (and in fact there are no 'black%' animals except 'black' ones) but the query now takes about 30 seconds to return empty.

It seems it's only the combination of 'top' and 'like %' causing trouble though because

select count(*)
from animal
where colour like 'black%'
and species like 'swan'

returns 0 very quickly, and even

select *
from animal
where colour like 'black%'
and species like 'swan'

returns empty in a fraction of a second.

Does any one have any idea why 'top' and '%' should conspire to cause such a dramatic loss of performance, especially in an empty result set?

EDIT: Just to clarify, I'm not using any FreeText indexes, I just meant that the fields are freetext at the point of entry, i.e. not normalized in the database. Sorry for the confusion, poor wording on my part.

This is a decision of the cost based optimiser.

The estimated costs used in this choice are incorrect as it assumes statistical independence between values in different columns.

It is similar to the issue described in Row Goals Gone Rogue where the even and odd numbers are negatively correlated.

It is easy to reproduce.

CREATE TABLE dbo.animal(
    id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    colour varchar(50) NOT NULL,
    species varchar(50) NOT NULL,
    Filler char(10) NULL
);

/*Insert 20 million rows with 1% black and 1% swan but no black swans*/
WITH T
     AS (SELECT TOP 20000000 ROW_NUMBER() OVER (ORDER BY @@SPID) AS RN
         FROM   master..spt_values v1,
                master..spt_values v2,
                master..spt_values v3)
INSERT INTO dbo.animal
            (colour,
             species)
SELECT CASE
         WHEN RN % 100 = 1 THEN 'black'
         ELSE CAST(RN % 100 AS VARCHAR(3))
       END,
       CASE
         WHEN RN % 100 = 2 THEN 'swan'
         ELSE CAST(RN % 100 AS VARCHAR(3))
       END
FROM   T

/*Create some indexes*/
CREATE NONCLUSTERED INDEX ix_species ON  dbo.animal(species);
CREATE NONCLUSTERED INDEX ix_colour ON  dbo.animal(colour);

Now try

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black'
       AND species LIKE 'swan'

This gives the plan below which is costed at 0.0563167.

The plan is able to perform a merge join between the results of the two indexes on the id column. (More details of merge join algorithm here).

Merge join requires both inputs to be ordered by the joining key.

The nonclustered indexes are ordered by (species, id) and (colour, id) respectively (nonunique non clustered indexes always have the row locator added in to the end of the key implicitly if not added explicitly). The query without any wildcards is performing an equality seek into species = 'swan' and colour  ='black'. As each seek is only retrieving one exact value from the leading column the matching rows will be ordered by id therefore this plan is possible.

Query plan operators execute from left to right. With the left operator requesting rows from its children, which in turn request rows from their children (and so on until the leaf nodes are reached). The TOP iterator will stop requesting any more rows from its child once 10 have been received.

SQL Server has statistics on the indexes that tell it that 1% of the rows match each predicate. It assumes that these statistics are independent (i.e. not correlated either positively or negatively) so that on average once it has processed 1,000 rows matching the first predicate it will find 10 matching the second and can exit. (the plan above actually shows 987 rather than 1,000 but close enough).

In fact as the predicates are negatively correlated the actual plan shows that all 200,000 matching rows needed to be processed from each index but this is mitigated to some extent because the zero joined rows also means zero lookups were actually needed.

Compare with

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'

Which gives the plan below which is costed at 0.567943

The addition of the trailing wildcard has now caused an index scan. The cost of the plan is still quite low though for a scan on a 20 million row table.

Adding querytraceon 9130 shows some more information

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'
OPTION (QUERYTRACEON 9130)

It can be seen that SQL Server reckons it will only need to scan around 100,000 rows before it finds 10 matching the predicate and the TOP can stop requesting rows.

Again this makes sense with the independence assumption as 10 * 100 * 100 = 100,000

Finally lets try and force an index intersection plan

SELECT TOP 10 *
FROM   animal WITH (INDEX(ix_species), INDEX(ix_colour))
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'

This gives a parallel plan for me with estimated cost of 3.4625

The main difference here is that the colour like 'black%' predicate can now match multiple different colours. This means the matching index rows for that predicate are no longer guaranteed to be sorted in order of id.

For example the index seek on like 'black%' might return the following rows

+------------+----+
|   Colour   | id |
+------------+----+
| black      | 12 |
| black      | 20 |
| black      | 23 |
| black      | 25 |
| blackberry |  1 |
| blackberry | 50 |
+------------+----+

Within each colour the ids are ordered but the ids across different colours may well not be.

As a result SQL Server can no longer perform a merge join index intersection (without adding a blocking sort operator) and it opts to perform a hash join instead. Hash Join is blocking on the build input so now the cost reflects the fact that all matching rows will need to be processed from the build input rather than assuming it will only have to scan 1,000 as in the first plan.

The probe input is non blocking however and it still incorrectly estimates that it will be able to stop probing after processing 987 rows from that.

(Further info on Non-blocking vs. blocking iterators here)

Given the increased costs of the extra estimated rows and the hash join the partial clustered index scan looks cheaper.

In practice of course the "partial" clustered index scan is not partial at all and it needs to chug through the whole 20 million rows rather than the 100 thousand assumed when comparing the plans.

Increasing the value of the TOP (or removing it entirely) eventually encounters a tipping point where the number of rows it estimates the CI scan will need to cover makes that plan look more expensive and it reverts to the index intersection plan. For me the cut off point between the two plans is TOP (89) vs TOP (90).

For you it may well differ as it depends how wide the clustered index is.

Removing the TOP and forcing the CI scan

SELECT *
FROM   animal WITH (INDEX = 1)
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'

Is costed at 88.0586 on my machine for my example table.

If SQL Server was aware that the zoo had no black swans and that it would need to do a full scan rather than just reading 100,000 rows this plan would not be chosen.

I've tried multi column stats on animal(species,colour) and animal(colour,species) and filtered stats on animal (colour) where species = 'swan' but none of these help convince it that black swans don't exist and the TOP 10 scan will need to process more than 100,000 rows.

This is due to the "inclusion assumption" where SQL Server essentially assumes that if you are searching for something it probably exists.

On 2008+ there is a documented trace flag 4138 that turns off row goals. The effect of this is that the plan is costed without the assumption that the TOP will allow the child operators to terminate early without reading all matching rows. With this trace flag in place I naturally get the more optimal index intersection plan.

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'
OPTION (QUERYTRACEON 4138)

This plan now correctly costs for reading the full 200 thousand rows in both index seeks but over costs the key lookups (estimated 2 thousand vs actual 0. The TOP 10 would constrain this to a maximum of 10 but the trace flag prevents this being taken into account). Still the plan is costed significantly cheaper than the full CI scan so is selected.

Of course this plan might not be optimal for combinations which are common. Such as white swans.

A composite index on animal (colour, species) or ideally animal (species, colour) would allow the query to be much more efficient for both scenarios.

To make most efficient use of the composite index the LIKE 'swan' would also need to be changed to = 'swan'.

The table below shows the seek predicates and residual predicates shown in the execution plans for all four permutations.

+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+
|                 WHERE clause                 |       Index       |                         Seek Predicate                         |              Residual Predicate              |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+
| colour LIKE 'black%' AND species LIKE 'swan' | ix_colour_species | colour >= 'black' AND colour < 'blacL'                         | colour like 'black%' AND species like 'swan' |
| colour LIKE 'black%' AND species LIKE 'swan' | ix_species_colour | species >= 'swan' AND species <= 'swan'                        | colour like 'black%' AND species like 'swan' |
| colour LIKE 'black%' AND species = 'swan'    | ix_colour_species | (colour,species) >= ('black', 'swan')) AND colour < 'blacL'    | colour LIKE 'black%' AND species = 'swan'    |
| colour LIKE 'black%' AND species = 'swan'    | ix_species_colour | species = 'swan' AND (colour >= 'black' and colour <  'blacL') | colour like 'black%'                         |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+

I belive this may be due to the underlying nature of MSSQL 2005 and the way the query optimizer decides which execution plan is the most efficient.

If you use a SQL variable, it should 'trick' the query optimizer into using hash matches instead of nested loops which will result in a much higher degree of parallelism.

Try:

DECLARE @topn INT = 10
SELECT TOP (@topn) *
FROM    animal
WHERE   colour LIKE 'black%'
AND species LIKE 'swan'


Is it possible to limit timeout on Postgres server? I set connection and command timeout to 10 minutes in my application (client side).

Than my application runs a simple query: SELECT pg_sleep(65)

On some servers it works fine, but other servers close the connection after 60 seconds.

Could this be some sort of PostgreSQL server configuration which limits timeouts and ignores my client settings?

Yes, it's possible

There are two settings mentioned in the docs (idle_in_transaction_session_timeout is new to version 9.6x)

statement_timeout (integer)

  Abort any statement that takes more than the specified number of milliseconds, starting from the time the command arrives at the server from the client. If log_min_error_statement is set to ERROR or lower, the statement that timed out will also be logged. A value of zero (the default) turns this off.

  Setting statement_timeout in postgresql.conf is not recommended because it would affect all sessions.

idle_in_transaction_session_timeout (integer)

  Terminate any session with an open transaction that has been idle for longer than the specified duration in milliseconds. This allows any locks held by that session to be released and the connection slot to be reused; it also allows tuples visible only to this transaction to be vacuumed. See Section 24.1 for more details about this.

  The default value of 0 disables this feature.

It's important to not set the statement_timeout in postgresql.conf unless you want amusement.

Here is an example of it working

SET statement_timeout = 10000;
SET
test=# SELECT pg_sleep(15);
ERROR:  canceling statement due to statement timeout

Yes It is possible.
There are some Connection Parameters which you need to set.

Please refer below link.
https://jdbc.postgresql.org/documentation/head/connect.html

loginTimeout
connectTimeout
socketTimeout
cancelSignalTimeout


How to enforce uniqueness of compound primary key made up of two foreign keys to the same table? I have two tables: a user table and a friendship table. Say the friendship table looks like this:

friendship
------------
user_one_id
user_two_id
other_fields

I need to enforce the uniqueness of the combination of values of (user_one_id, user_two_id) and disregard the ordering, so:

user_one_id | user_two_id
------------+------------
          1 |          2
          2 |          1  -- the DBMS should throw a unique constraint error when trying to insert a row like this one.

Another important point is that user_one_id represents the initiator of the friendship and user_two_id represents the recipient, so I cannot just make the "smaller" of the two ids user_one_id.

The question

Is there a way to do this using constraints or should I implement this some other way?

In light of your comment,

  For my use case, user_one_id represents the initiator of the friendship, and user_two_id represents the recipient of the friendship. So I can't just use the lowest value as user_one_id.

Well, you can still do it. Your use case just excludes a row-constraint to ensure that. What you want is to use a table-constraint, something like this.

CREATE TABLE friendship (
  user_one_id int NOT NULL,
  user_two_id int NOT NULL,
  CHECK (user_one_id != user_two_id ),
  PRIMARY KEY (user_one_id, user_two_id)
);
-- you can do least first if you want. doesn't matter.
CREATE UNIQUE INDEX ON friendship (
  greatest(user_one_id, user_two_id),
  least(user_one_id, user_two_id)
);

We have a lot going on here.. We make sure.

Both are NOT NULL
Both are not equal to each other
Both are UNIQUE (user_one_id, user_two_id)

That leaves one remaining problem of commutative uniqueness we solve that with a custom unique table-constraint implemented with an index.

Proof in the pudding

INSERT INTO friendship VALUES ( 1,2 );
INSERT 0 1
test=# INSERT INTO friendship VALUES ( 2,1 );
ERROR:  duplicate key value violates unique constraint friendship_greatest_least_idx"
DETAIL:  Key ((GREATEST(user_one_id, user_two_id)), (LEAST(user_one_id, user_two_id)))=(2, 1) already exists.

As an important friendly note your names are all kinds of silly. The relationship is fine. In production, please give them better names..

friendship
----------
request_initiator
request_target
other_fields

If you set the logic to set that the value of user_one_id is lower than the value of user_two_id, you won't have any issue.


OLAP or OLATP - How to determine I have SQL express server with database that is used by one application. I am trying to optimize the DB but am not sure if the DB is OLTP or OLAP.

There is no analysis server installed, the DB is inside the SQL Server instance hence it  should be OLTP, but when I run the read/write ration on the DB I have the following:

          Reads    Writes    Read%   Write %

DB file   400 000   75 000   85%     15%

DB log        250   30 000    1%     99%

Standard OLTP DB should have more reads than writes, right? This makes me suspect that maybe It is OLAP DB.

I am asking this regarding the use of HyperThreading technologies, MAXDOP setting and whether to configure cache as write-through or write-back among other things.

The query I am using is as follow:

SELECT DB_NAME(DB_ID()) AS [Database Name] ,
[file_id] , num_of_reads , num_of_writes , num_of_bytes_read , num_of_bytes_written ,
CAST(100. * num_of_reads / ( num_of_reads + num_of_writes ) AS DECIMAL(10, 1)) AS [# Reads Pct] ,
CAST(100. * num_of_writes / ( num_of_reads + num_of_writes ) AS DECIMAL(10,1)) AS [# Write Pct] ,
CAST(100. * num_of_bytes_read / ( num_of_bytes_read + num_of_bytes_written ) AS DECIMAL(10, 1)) AS [Read Bytes Pct] ,
CAST(100. * num_of_bytes_written / ( num_of_bytes_read + num_of_bytes_written ) AS DECIMAL(10,1)) AS [Written Bytes Pct]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) ;

Any thoughts from senior DBAs?

From a DBA perspective, the key difference between OLAP and OLTP is the tuning method you apply to the queries. The read/write ratio doesn't really tell you anything useful.

I have a little "magic quadrant" that I use to illustrate the difference (in your case, consider BI/DW and ETL the same as OLAP):

Basically, if you must touch a lot of data to produce query results, you are OLAP. Converse if you can get away with touching very little data to produce return values, you are OLTP.

Obviously, if you have poor indexing strategies, then OLTP can end up looking like OLAP - simply because you end up doing table scans all the time. A good way to make that distinction is to look at how much data MUST be touched by the top 10 most important queries in the system - no matter how good your indexing strategy is.

Once you have settled on what the majority of your workload is, you can start applying workload specific tuning techniques. Here are some of the workload specific tricks you might consider (always tailor to your specific workload)

OLAP:

Prefer hash joins
Optimise table scan speed (typically by laying out data for sequential I/O)
Aggregate view are often better than indexes
Column store indexes are better than B-trees
Sequential I/O
Aggressively de-normalise
Use high MAXDOP
Optimise network for bandwidth

OLTP:

Prefer loop joins
Optimise for index seek (there should always be an indexed path to the data)
Rarely aggregate
B-tree indexes
Random I/O
Keep database in 3NF
Use MAXDOP 1
Optimise network for latency

Since the database is created in a normal SQL Server instance, so it's an OLTP database as OLAP databases are created in Analysis Server instances only. Moreover, you cannot insert, update or delete from OLAP databases, so as a software developer you don't have the option to use OLAP as the core database for your solution. Not only that, you cannot query OLAP databases with normal SELECT statements, you should use MDX statements.

The OLTP stands for Online Transaction Processing, meaning that it depends on transactions. However, on the other side, OLAP depends on what is called Processing. OLAP databases depend on a normal database with a certain schema, after the data is loaded (probably using SSIS), the Processing takes place to load the data in OLAP database.


Are availability groups required for AlwaysOn Beginning a project to create an HA SQL server environment.  We are limited to using SQL Server Standard edition, currently 2016.  From what I have found, AO(AlwaysOn) for standard edition limits you to basic AG (availability group) and thus  only 1 database per AG.  This is going to be tough since most of our SQL instances contain 30+ databases.  Can you implement AO without AGs?  Are there other ways to configure AO to address our needs?  All databases on any of our instances are managed similarly and thus wouldn't need the flexibility that managing at an AG provides.

If you have an underlying Windows Cluster, and shared storage you could look at using Failover Cluster Instances - this would work at an instance level. Standard edition supports this for two nodes.

Yes or 'it depends'. Microsoft changed the naming some time ago so there's some confusion over Always On. I'm probably oversimplifying but:

Always On Failover Cluster Instances (This is just plain old windows failover clustering) This requires shared disks and might fulfill your HA requirements. Works with standard edition using 2 nodes only.

Always On Aviailability groups (this is more or less mirroring with some bells and whistles). This might cover HA and/or DR depending on how you choose to configure synchronization and automatic failovers.


SSIS Foreeach Container Not Failing I have a foreach container which is looking in a share directory for files to then send on to another location.  When I ran this as part of a job the package was running successfully however the files were not being sent.  The reason was because the agent account needed access to the share.

My question is how can I get the package to fail should it not have permissions to access the directory?

Thanks

Surprise, surprise, surprise!  SSIS doesn't fail the Foreach File loop container if the path is inaccessible – wow... how fun.  You'll have to add a script task to check before the loop fires.


Error While restoring backup in SSMS When I tried to restore a backup an error occurred showing access denied. The error message is as follows:

  Restore failed for Server 'VAIO\sqlexpress'.
   (Microsoft.SqlServer.SmoExtended)

  System.Data.SqlClient.SqlError: The operating system returned the
  error '5(Access is denied.)' while attempting
  'RestoreContainer::ValidateTargetForCreation' on 'C:\Program
  Files\Microsoft SQL
  Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ihrd_mvk_newspaperboy.mdf'.
  (Microsoft.SqlServer.Smo).

Open "SQL Server Configuration manager"=>set "SQL Server" logon as=>Local System.


How can I optimize a query where a IN clause is replaced with multiples UNION ALL? I have a query like this:

select *
from slow_view
where id = 1
union all
....
union all
select *
from slow_view
where id = 1000

-- 50 ids, exec time: 10.615 sec,

This would run in a very acceptable time if it was replaced with:

select *
from slow_view
where id in (1, ...., 1000)

-- 50 ids, exec time: 1.3 sec,

or

with slow_view_cached as (
select * from slow_view)
select *
from slow_view_cached
where id = 1
union all
....
union all
select *
from slow_view_cached
where id = 1000

-- 50 ids, exec time: 1.83 sec,

But unfortunately it is generated by an ORM and I don't have a chance to modify it. It's quite bad as an ORM, I don't think I can do much to improve its behavior.

Is there something I can do to my query or to my view to make it "cached" for all the subqueries in this case? I know I could use a materialized view but it doesn't fit my case very well.

AFAIK in Postgres it's not possible to define indexes on a view, so id is not indexed or unique.

Is there anything else I can try?

I'm using Postgresql 9.5 (but if you have a solution for PG 9.6 or even PG 10 it would be acceptable, since I plan to update very soon).

The code is a bit too complex to post here. In essence this query is generated in the following case. slow_view is an association of a Model m. What I do is basically m.find().populate(slow_view, criteria); The documentation is here.

criteria is excluded from the query for the sake of simplicity, id is the foreign key from _slow_view_ to Model.

I could call a stored procedure from this ORM, but it would mean altering the application a bit too much.

if slow_view don't have too many records, you can consider fetching all the records to application and apply the filter in the application side result set.
This approach helped me in a specific case to reduce the database load.

You say you're using m.find().populate(slow_view, id: ids); It sounds to me like this is not what you want, but instead, from the docs you linked,

m.find().populate(
  slow_view,
  where: {
    id: [1,2,3]
  }
);


Remove duplicates in sequence based on keys I am looking to remove duplicates on a row by row comparison based on keys Member and Group and data sorted ASC on Member, Group and StartDate. Just return the records in the select query and not delete in the table.

I am basically looking at a row by row comparison to eliminate the duplicates based on the 2 keys and value of the Status column with the data sorted by Member, Group and Start_date.

Sample data:

Member  Group   Status        Start_date        End Date
1         2     Active       4/12/2011 01:19    NULL
1         2     Active       4/12/2011 02:19    NULL
1         2     Inactive     4/12/2011 03:19    NULL
1         2     Inactive     4/15/2011 04:19    NULL
1         2     Inactive     4/16/2011 05:19    NULL
1         2     Pending      4/16/2011 11:23    NULL
1         2     Active       4/16/2011 11:25    NULL
1         2     Pending      4/16/2011 11:27    NULL
11        22    Inactive     4/16/2011 11:27    NULL

Expected Output:

Member  Group   Status        Start_date        End Date
1         2     Active       4/12/2011 01:19    NULL
1         2     Inactive     4/12/2011 03:19    NULL
1         2     Pending      4/16/2011 11:23    NULL
1         2     Active       4/16/2011 11:25    NULL
1         2     Pending      4/16/2011 11:27    NULL
11        22    Inactive     4/16/2011 11:27    NULL

DDL and DML:

CREATE TABLE `temp1` (
  `MEMBER_ID` int(10) unsigned NOT NULL,
  `GROUPIE_ID` int(10) unsigned NOT NULL,
  `STATUS` varchar(12) CHARACTER SET utf8 NOT NULL,
  `START_DATE` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'Active', '2011-04-12 01:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'Active', '2011-04-12 02:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'InActive', '2011-04-12 03:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'InActive', '2011-04-15 04:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'InActive', '2011-04-16 05:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'Pending', '2011-04-16 06:23:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'Active', '2011-04-16 07:25:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'Pending', '2011-04-16 08:27:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'InActive', '2011-04-17 09:27:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (11,22,'Active', '2011-04-12 01:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (11,23,'InActive', '2011-04-13 02:19:51 PM', NULL);

This works on consecutive Start_date for each partition (on Member):

SELECT `Member`, `Group`, `Status`, `Start_date`, `End Date`
FROM (
    SELECT @row := CASE WHEN @status=Status AND @member=Member AND @group = `Group`
        THEN @row + 1 ELSE 1 END as row
        , @member:=Member as Member
        , @group:=`Group` as `Group`
        , @status:=Status as Status
        , `Start_date`, `End Date`
    FROM data d
        , (SELECT @row := 0, @status := '', @member := 0, @group := 0) v
    ORDER BY Member, `Group`, Start_date
) as n
WHERE row = 1
ORDER BY Member, Start_date
;

Sample SQL Fiddle.

The behavior of this query is similar to the ROW_NUMBER()  available in Oracle (>= 10g), PostgreSQL (>= 8.4) and SQL Server (>= 2012).

Notes on Partitions:

This query and SQL Fiddle partition by Members and order Status by Members and Start_date.
Partition can be remove by removing @member=Member from CASE
Partition can be added or set by both Members and Group like this (SQL Fiddle):

Add a test on @group = 'group' to the CASE
Add @group := 'Group' as 'Group' in the inner SELECT [n]
Add @group := 0 in the variable SELECT [v]
Add Group to ORDER BY clauses

In term of performance, an index matching the order and columns used in the ORDER BY and CASE is very likely needed.

Output:

Member  | Group     | Status    | Start_date                | End Date
1       | 2         | Active    | April, 12 2011 03:19:00   | (null)
1       | 2         | Inactive  | April, 12 2011 05:19:00   | (null)
1       | 2         | Pending   | April, 16 2011 13:23:00   | (null)
1       | 2         | Active    | April, 16 2011 13:25:00   | (null)
1       | 2         | Pending   | April, 16 2011 13:27:00   | (null)
11      | 22        | Inactive  | April, 16 2011 13:27:00   | (null)

Maybe you could filter the start_date with MAX or MIN and group by member, group, status, and start_date


Default phpMyAdmin Login Credentials I downloaded and installed phpMyAdmin 4.8.5 on my windows. Upon opening it in the browser, it immediately asks for a Username and Password. I tried to submit root for username while leaving the password black. I even try password as password. Unluckily, both didn't work by default.

So I checked the web and I saw this from stackoverlow. Unfortunately, all of the given answers did not work for me and hello no, the thread is already closed.

Here's what I did

Open phpmyadmin directory
You will see config.sample.inc php file
Clone it, but remove the sample from the title
After cloning, open the file config.inc and look for $cfg['Servers'][$i]['AllowNoPassword'] = false;
Set it to true

By doing this, we are allowing the phpMyAdmin to submit an empty password.

Now, try root as a username and leave the password blank.

Hope this will work for you as well.

Username: Root
Password: Empty- Nothing to write, Click Enter


Mysql - Problem speed of access on query (Low latency query sometime) My web application is real time site that need a speed of access on every query (SELECT, UPDATE, INSERT) that must less than 200ms

Because one of my php script task is query (SELECT, UPDATE, INSERT) the work on every secound
and then loop every secound alway across web server running.

Well, On average 98% each query time is less than 0.001-0.005 secound on each query stagement. the problem is
Sometime around 3 time per hour, the query (SELECT, UPDATE, INSERT) can take up to 1s and may be 5s, so they has waiting and effect the next query loop that damage for whole business application.

i so find the solution such as row locking, turning mysql it not ensure speed of access less than 200ms, sometime it query 5s ooh!!
but the solution that i found to reducing the problem are move that from INNODB to MEMORY but sometime still a problem and the important thing are the need the recovery when system has crash.

  Example >> in some UPDATE problem that table size < 1M with 30 record and update on primary key it not sence

the question are it has many thing effect each other. HOW to slove this problem. How to start to find problem.

Do you have any idea or algorithm or if u need more some information i will give you.

thank you
so sorry about my english. i am thai

You may switch the table from innodb to memory, but ensure the size of the table isn't that huge so that it doesn't eat up entire of its memory. Yes, you may safe guard or recover the table during system crash. Have master/slave replication setup and once the table is created on master with memory as engine type and change the table engine type to innodb (on slave only). So that even at the instance of master crash/restart you will get the data from slave.


BACKUP failed to complete - with spaces, for no reason Every day my database backup fails for some databases and does fine for others. I'm calling the backup through a Stored Procedure that runs scheduled in a job. Tonight I'm going to run the procedure in a query window with a WAITFOR in place just to check. In the \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG the message is:

2011-12-28 07:49:52.43 Backup      Error: 3041, Severity: 16, State: 1.
2011-12-28 07:49:52.43 Backup      BACKUP failed to complete the command BACKUP DATABASE                                                                                . Check the backup application log for detailed messages.

Obs: the log actually contains all these spaces and no further info.

Well, it looks like 3041 is the generic "something went boom with the backup" error.

Have you tried just running the backup SQL from SQL Agent without using the wrapping stored procedure?  I'm assuming the stored procedure you're using for the backup lives in the master DB (or at least not in the DB you're backing up).

Also, you could use this query to check your backup status (last 7 days).  There might be something else going on that you don't know about and that's interfering with your backups.

SELECT
 CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
 msdb.dbo.backupset.database_name,
 msdb.dbo.backupset.backup_start_date,
 msdb.dbo.backupset.backup_finish_date,
 msdb.dbo.backupset.expiration_date,
 CASE msdb..backupset.type
   WHEN 'D' THEN 'Database'
   WHEN 'L' THEN 'Log'
 END AS backup_type,
 msdb.dbo.backupset.backup_size,
 msdb.dbo.backupmediafamily.logical_device_name,
 msdb.dbo.backupmediafamily.physical_device_name,
 msdb.dbo.backupset.name AS backupset_name,
 msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
  msdb.dbo.backupset.database_name,
  msdb.dbo.backupset.backup_finish_date

The problem here is the backup file was locked, we need to close the file.

Steps to close the file:

Computer Management-->>System Tools-->Shared Folders-->>Open Files-->(here we can see our back up file)-->>right click-->>Close Open file

Now take backup, if backup doesn't go through then we need stop the services of client scheduler and client Acceptor

Computer Management-->>Services and Applications-->>Services-->>find the client acceptor and schdulers, stop the services go to backup folder and delete the file, start the services back and take a backup

Cheers...!!!


MySQL remote connection: Public vs. private IP address I just went through the obligatory pain of getting MySQL to accept a remote login. Yes, I had to create a user of the form ''@'%', and I did so. Yes, I had to GRANT ALL ON . to my new '@%' user and then flush privileges, and I did so. Yes, I had to open up port 3306 on the server, and I did so. But, trying to access MySQL via HeidiSQL, I continued to have no luck at all.

Cutting to the chase, my struggle was with the bind address. I'm running Ubuntu Linux on an AWS server instance, so I have a public IP address and a private IP address. And of course there's always the MySQL default IP address of 127.0.0.1. That default wasn't allowing me any remote connections, though, so I figured I should try the public IP. But in that case, MySQL would not start. Finally I took a shot and used the private IP address as the bind address, and everything fell into place: I was able to stop and restart MySQL, and I was able to connect from HeidiSQL

But I am totally confused. HeidiSQL addresses this server via its public IP address. But MySQL refuses to bind to the public IP address! (As I said, when I tried, MySQL wouldn't even start.) MySQL has the private IP address as its bind address. What's going on here? Why, to get a remote connection, do I need HeidiSQL targeting port 3306 at the public IP address but MySQL bound to the private IP address?

The explanation here is a straightforward one: You can't bind a listening socket to an IP address that your machine's IP stack isn't aware of.

In AWS (whether EC2 Classic or EC2/VPC) as in many network environments these days, your external IP is not configured in your machine -- it's mapped through an external device (router, firewall) to your machine's private IP via NAT (network address translation).

You can confirm that your server itself has no idea about the external IP:

 $ ifconfig

The simplest solution, of course, is binding to 0.0.0.0 unless you have a specific reason not to.

http://en.m.wikipedia.org/wiki/Network_address_translation

Try this:

The MySQL server's /etc/hosts file must contain server IP address so please verify it once..

$ sudo cat /etc/hosts


"Can't find file mysql/plugin.frm" when starting up MySQL When I start my MySQL server, I get this error:

/usr/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

I have run mysql_upgrade to rebuild it, and it works to rebuild. Before I did that, I removed and installed MySQL. However, when I tried to re-start, I get the same error. I am sure it is not a permission problem, as all files and directories are created by the mysql user.

Could you advise me on how to fix this error?

If you have mysql data directory at non-standard location and your system has apparmor, ensure that /etc/apparmor.d/usr.sbin.mysqld has your data directory whitelisted. The configuration should look like:

/mysql/data/dir/ r, #trailing slash is mandatory
/mysql/data/dir/** rwk,

Apparmor daemon has to be restarted after you make any changes to config.

This simple chown worked for me also, no more crashing process:

sudo chown -R mysql:mysql  /var/lib/mysql

Thankfully, playing with apparmor gets complicated and hard to edit.


11gR2 didn't ask me to set username or any password I have installed Oracle 11gR2 (latest version 11.2.0.1.0) on my system. During the installation it didn't ask me to set username or any password. I'm unable to start sql plus because i don't have the username and password.
Can anyone tell me what is the default username and password or what is the default password for SYS or SYSTEM?
Thanks

If the Universal Installer included with 11gR2 did not ask for a password, it is likely that the option to create a database was not selected during installation.

Solution? Create a database with the dbca tool and supply a sys password when prompted.

With older versions, perhaps including Oracle Database XE (11g), you might have luck with one of the default password combinations of sys/change_on_install or system/welcome1 or system/manager. On engineered systems I have also seen sys/welcome1 and sys/changeme

if you have administrator permission, after entering "sqlplus sys as sysdba" command press the enter. and press the Enter again for password will let you query the database ! and then you can change the password for sys from $ Tables.
hope this help


View active MySQL connections per user I need a query that will give me the active or open number of connections to a certain database for a certain user. I have been searching for hours and have come up with nothing so far. I am close, so here is what I have tried.

SHOW STATUS WHERE `variable_name` = 'Threads_connected';
SHOW STATUS LIKE '%onn%';
SELECT * FROM information_schema.processlist WHERE USER='database_user';

I work for a web hosting company, and one of our clients keeps hitting his max_user_connections limit, so in troubleshooting why I need to know how many connections his user is using right now since he is no a shared server. I am currently using MySQL (InnoDB) version 5.5.36. Any help would be greatly appreciated!

What you need is a breakdown by user and hostname along with a total

SELECT IFNULL(usr,'All Users') user,IFNULL(hst,'All Hosts') host,COUNT(1) Connections
FROM
(
    SELECT user usr,LEFT(host,LOCATE(':',host) - 1) hst
    FROM information_schema.processlist
    WHERE user NOT IN ('system user','root')
) A GROUP BY usr,hst WITH ROLLUP;

This will handle host address that have a colon separating hostname and port number

I hope everybody is not logging in as root

There was a discussion in detail for this: https://dba.stackexchange.com/a/47160/385

In short, the queries you need are

SELECT max_user_connections FROM mysql.user
WHERE user='db_user' AND host='localhost';

and

SHOW VARIABLES LIKE 'max_user_connections';

UPDATE

Maybe this is not direct answer to your question, but according to http://dev.mysql.com/doc/refman/5.5/en/user-resources.html MySQL allows you to limit the number of access per hour. So, maybe that answers why a certain user hits limit while the number of connections of them are small according to process list. Unfortunately, I cannot find reference on how to get the access counter.


How to limit the number of sessions for a specific login How can we let just one program to connect to the database, in case many programs have the connection string.
(limit the number of sessions for a specific login)

If all programs are using the same credentials in the connection string, you might be able to achieve what you're after by using a Logon Trigger to limit the number of sessions for a specific login. For example, in the following code (taken from the documentation), the logon trigger denies log in attempts to SQL Server initiated by login login_test if there are already three user sessions created by that login.

USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
    CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK;
END;

Just have one logon per database.  Don't share it with other programs.


Join on primary keys to primary key vs primary key in range Assume we have an #RelevantOrders table and an OrderRow table.

create table #RelevantOrders(Order_Id int primary key)

create talbe OrderRow (Order_Id int, OrderRow_Id, [lots of data columns], primary key (Order_Id, OrderRow_Id)

Now what I by instinct did initialy do was a simple join

select OrderRow.* from #RelevantOrders as ro join OrderRow on OrderRow.Order_Id = ro.Order_Id

Now, due to the data, the Order_Id in #RelevantOrders are several ranges of Ids, ie. 1,2,3,4,5,100,101...120, 240..250 ... hope i am being clear that.

So what happens, if there are 1000 Relevant orders, execution plan does 1000 index seeks on OrderRow table, 1 per each Order_Id. This leads quite 'big' scan count and logical reads (~5000).

SO it got me thinking, i would expect that instead of doing 1000 index seeks, it would do ~80 (number of continuous ranges) index seeks.

So i tried an alternative:

create table #RelevantOrderRanges(f int, t int)

select OrderRow.* from #RelevantOrderRanges
join OrderRow on OrderRow.Order_Id between f and t

The good news is ammount of index seek and logical reads drops to 80 index seeks (equal to amount of ranges) and 609 logical reads. Much better. The only issue i have here and thats due to my experience with it causing to gobble up memory is the estimated number of rows and estimated data size, which goes up to about 3.5GB in this case instad of 49KBs.

Now i know that in case number of ranges approaches number of orders any gain is lost.

So this got me thinking and i have two questions:
1) am i overthinking the estimates too much? I know more testing is required but i am a bit afraid the estimates would bite me later due to memory hog

2) is there any way to 'better explain' SQL what to expect? Indices, hints, statistics.... ?

Thanks for discussion

PERFORMANCE UPDATE:
TC 1:
Order_Id count: 9045
Range count: 5499
Total rows produced:

Range based approach:
~800ms CPU, ~800ms ellapsed time
Scan count: 5,500
Logical reads: 37,896

ID join approach:
~900ms CPU, ~900ms ellapsed time
Scan count: 9045
Logical reads: 56774

TC 2:
Order_Id count: 19684
Range count: 12849
Total rows produced: 124561

Range based approach:
~718ms CPU, ~770ms ellapsed time
Scan count: 12849
Logical reads: 74127

ID join approach:
~950ms CPU, ~950ms ellapsed time
Scan count: 19684
Logical reads: 110352

Edit. I misunderstood the question. I thought this was about app design rather than query performance.

For an App: Personally i would avoid using an intermediate table. Its just extra steps and complication.

The #table makes sense for queries.

Provided you have useful indexes and your stats are up to date, you are typically better off leaving SQL Server to decide the best execution plan.

Depending on the growth and distribution of data the best execution plan may change over time. So i would be reluctant to invest a lot of time and effort coding to squeeze the last 1% of extra performance.


Turning off auto shrink on all SQL Server databases. Why doesn't this work? I figured I could use sp_MSforeachdb to solve this problem, but I get an error message.

sp_MSforeachdb '
BEGIN
USE [?]
DECLARE @dbid INT
SET @dbid = DB_ID()
IF(@dbid > 4)
BEGIN
    --PRINT ''[?]'' + CONVERT(VARCHAR, @dbid)
    --ALTER DATABASE [?] SET AUTO_SHRINK OFF
END
END;
'

If I run the above query with the PRINT line uncommented, I get a list of all databases except the system databases. However, when I uncomment the ALTER DATABASE line, I get these two error messages:

  Msg 5058, Level 16, State 2, Line 9
  Option 'AUTO_SHRINK' cannot be set in database 'master'.
  Msg 5058, Level 16, State 1, Line 9
  Option 'AUTO_SHRINK' cannot be set in database 'tempdb'.

This seems to break the operation at some point, so that only some of the databases get auto shrink disabled.

Any idea how I can disable auto shrink on all databases? Bonus question: why doesn't my approach work?

Unfortunately, the procedure sp_MSforeachdb is and will always be unsupported and thus, kind of unreliable.

There's an article on CodeProject that shows how to filter databases when using this DB. But I have the same problem as yours on my local 2008 R2 installation. Filtering doesn't work.

Our own friend, Aaron, wrote a nice article some time ago, about writing a different and better version of sp_MSforeachdb. See his article here. Use that SP and the filtering parameters will do what you need.

although this is answered I just though it would be nice to drop this little line of code that does exactly the filtering most people want and sets AUTO_SHRINK OFF:

EXECUTE sp_MSforeachdb 'IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model'')) EXECUTE (''ALTER database ? SET AUTO_SHRINK OFF'')


Use Dark Theme on SSMS Results Grid, Object Explorer and other windows There are several ways to use a Dark Theme on SSMS main coding window, either importing a vsettings file, applying Dark Theme that is disabled on a config file or doing it manually. But all those options do not affect Grid Results, Object Explorer and other windows. Those 2 are the main ones I use other than the coding one.

I tried the usual Tools>Options>Environment>Fonts and Colors then selected on the combo box Show settings for: the option Grid Results and using White for Item Foreground and Black for Item Background. Saved, restarted SSMS but just the text is white, background is still white.

Any idea on what is happening or how to do it? I can't find how to do it for the Object Explorer.

These options are ignored as of SQL Server 2005 SP2 and still not fixed.

See the relevant connect item: SP2: Results Grid Background color property ignored by Management Studio. archive link

The status is closed as fixed, but only because it's been converted into an internal issue

  Thanks for this feedback. I have converted this request to either a
  defect (something is broken) or a Design Change Request (something new
  to add to the product) and so I'll close this ticket out. We will
  consider your information for SQL Server 2008 or a subsequent release.
  Thanks!
  - Buck Woody, SQL Server Program Manager

There is a second connect item: SSMS 2012 Grid Results background color does not work (unfortunately not archived) which is closed as wontfix.

  Hello Chris. Thanks for the feedback. We triaged this issue and at
  this time do not plan to address this in a future version of SQL
  Server.
  -Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)

There is however one user commenting

  I found the answer! This is actually controlled by the tooltip color
  you set up in your windows environment. Man, That was a PAIN to find!

But unless that works I fear you are out of luck

Visual Studio 2017 has Dark theme and supports SQL queries. Results are shown in white text on black background (except for the heading line, which is black text on white background).