Update a value after an hour when a record is inserted I am doing a booking system for an iPhone app and I want to update the Status value to "Completed" after 1 hour.

I initially wanted to check for the TIMEDIFF of the current datetime and StartDateTime to get the remaining time, and update the value through the app once the timer is completed instead. But, I am not able to create the timer when the application terminates, so I need to create it in the database instead.

I'm not sure whether this is achievable, can anyone please help me?

First thing you should do: be self-critical with your original data model:

Why do you actually need to have a Status column in the table?

The status can be computed from the StartDateTime and EndDateTime. You just have one straightforward alternative: compute its value when needed. If (current_time < start + 1 hour) and (end time is null) => we're in use, otherwise, it's completed. You can have this as a computed column in a VIEW. In MySQL you'd do:

CREATE VIEW job_log_with_status AS
SELECT
    ID, ResourceID, StaffID, StartDateTime, EndDateTime,
    TimeDiff(Coalesce(EndDateTime, now()), StartDateTime) AS TimeSpent,
    CASE
        WHEN EndDateTime IS NOT NULL THEN 'Completed'
        WHEN TimeDiff(now(), StartDateTime) > time '01:00:00' THEN 'Completed'
        ELSE 'In Use'
    END AS status
FROM
    job_log ;

(Assumption: your original table is called job_log)

When used, this view would output something similar to:

SELECT * FROM job_log_with_status;

ID | ResourceID | StaffID | StartDateTime       | EndDateTime         | TimeSpent | status
-: | :--------- | :------ | :------------------ | :------------------ | :-------- | :--------
 1 | CM-01      | 175124N | 2017-07-13 18:41:53 | null                | 02:00:00  | Completed
 2 | CM-02      | 175124N | 2017-07-13 20:11:53 | null                | 00:30:00  | In Use
 3 | CM-01      | 175124N | 2017-07-13 19:26:53 | 2017-07-13 20:26:53 | 01:00:00  | Completed
 4 | CM-02      | 175124N | 2017-07-13 19:56:53 | 2017-07-13 20:06:53 | 00:10:00  | Completed

You can check the setup and some sample data at dbfiddle here


How to count three consecutive records with same values in a column based on the value of another column in PostgreSQL? I have a table attendance as follows:

create table attendance (id int, name varchar(30), status varchar(10), date date)

This table has the following records:

insert into attendance values (1,'John','absent','2016-03-01');
insert into attendance values (1,'John','absent','2016-03-02');
insert into attendance values (1,'John','absent','2016-03-03');
insert into attendance values (2,'Sam','present','2016-03-04');
insert into attendance values (3,'Sam','absent','2016-03-05');
insert into attendance values (1,'John','absent','2016-03-06');
insert into attendance values (1,'John','absent','2016-03-07');
insert into attendance values (1,'John','absent','2016-03-08');
insert into attendance values (1,'John','present','2016-03-09');
insert into attendance values (1,'John','absent','2016-03-10');
insert into attendance values (1,'John','absent','2016-03-11');
insert into attendance values (1,'John','present','2016-03-12');
insert into attendance values (1,'John','absent','2016-03-13');

Now I want to count that how many times a person has three consecutive absent records.
The result should be as follows:

id name count
 1  John   2

because John is absent for three consecutive days two times.

If they have 6 absents on 6 consecutive dates, then it should count 2 i.e (3+3).

It should count 1 for 3 consecutive absents on 3 consecutive dates i.e if John is absent for 1,2 and 3 march, it should count 1. but if John is absent for 1,2 and 4 march then it should not count 1.
4 or 5 absents should be counted as 1.

If there is no entry for a date, It is considered 'Present'.

Any help is appreciated.

Another solution. Recursive part creates groups of rows of status "absent".
The solution uses recursive CTEs and window functions.

WITH RECURSIVE a(id, name, date, n)  as (
    SELECT id, name, q.date, 1 as n
    FROM (
        SELECT id, name, date,
            date-lag(date) OVER (PARTITION BY name ORDER BY date) as lag
        FROM  attendance
        WHERE status='absent'
    ) q
    WHERE lag >1 or lag is null
    UNION
    SELECT a.id, a.name, a.date, a.n + 1
    FROM a
    JOIN attendance at ON (
           a.id = at.id and at.name = a.name and at.date = a.date + n)
    WHERE at.status='absent'
)
SELECT id, name, sum(long_absences) FROM (
    SELECT id, name, count(*)/3 as long_absences
    FROM a
    GROUP BY id, name, date having count(*) >=3
    ) as absences
GROUP BY id, name;


Multi Master Replication With One Slave I'm in the middle of my little project here and i had some problem here, this is the detail of it.

I have 4 server, 3 server using for getting data from my sensor module and 1 is using for storing data. I try to using chained replication but it seems didn't work for me. I'm using multi master in 3 server and 1 slave in other server.

S1 <--> M2 <--> M3 <--> M4

Each server have different database, in M2 I have database name "PT100", M3 = "PT100_2" and M4 = "PT100_3", so I created 3 database on each server.

The problem is M2 cannot receive replication with database "PT100_3" from M4, even though M3 is receiving "PT100_3" from M4.
S1 even worst, it only can receive "PT100" from M2, even though there is "PT100_2" replication running on M2, nothing either on "PT100_3".

What I want is, storing all the database from M2, M3 and M4 in S1, that's all.
How can I achieve this? Is it really possible to doing this and I just do some mistakes in configuration?

3 Masters must be in a circle.  A Slave can hang off any of them.

However, Multi-master is a nightmare when any Master dies for any reason.  I strongly recommend against building such a system.

Replication systems usually expect all data to be on all servers.  There are many exceptions, but I await your justification for not doing that.


Admin on phpMyAdmin does not have Super privileges I have Plesk via GoDaddy and I am the sole admin and user. When I go to phpMyAdmin and try to execute the following:

SET GLOBAL event_scheduler = 'ON'

I get the error "#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation"

Considering I don't have the tab for security how can I change the sql database privileges to allow this? It would be easy to do if it was local hosted but I can't find anything on how to do this with my specific setup.

Plesk via GoDaddy does not allow you to have admin privileges over phpMyAdmin. This seems to be a result of shared hosting. In order to do any action that does require that privilege you have to use work arounds or third party software.

I answered this here as it seems that this information cannot be found on the Plesk or goDaddy forums.


How to prevent #Error values derived from Nulls in an outer join Suppose I have two tables to outer join

Tab1:
ID  Nam
1   Joe
2   Moe
3   Flo

Tab2:
ID  Val     y   w
1   stuff   66  33
2   duff    67  44

and one of the resulting columns is derived -- i.e., it's a function of two other columns

SELECT Tab1.*, Tab2.Val, Tab2.y, Tab2.w, week(Tab2.y,Tab2.w) AS week
FROM Tab1 LEFT JOIN Tab2 ON Tab1.ID = Tab2.ID

As expected, where there is no join, nulls appear under the actual columns, but #Error appears under the derived column:

ID  Nam Val     y   w   week
1   Joe stuff   66  33  66w33
2   Moe duff    67  44  67w44
3   Flo                 #Error

First, I thought I could prevent #Error via the useful-for-handling-null-strings function Nz() but Nz(week(Tab2.y,Tab2.w)) did nothing.
Next, I thought it was something I had to handle in my week() function -- i.e., handle cases of IsNull(y) -- but I discovered that it doesn't even get called for the "null cases".

How to prevent #Error values from showing?

You need to apply the function (Nz) that replaces the null with another value before the week function call. Use:

week( Nz(Tab2.y, Value_for_y) , Nz(Tab2.w, Value_for_w) )

You probably also need tests against the special Value_for_y or Value_for_w inside week().


Remote MySQL Dump not working with cnf file I have created a user 'backup'@'%' with Grant all privileges access to .

I have added the user backup to my.cnf file

When I do the following in CMD:

mysqldump -p3306 -h<dns/ip> db-name table-name > D:\table-name.sql

I get an error 1045: Access denied for the user 'backup'@'' using password: YES when trying to connect

However, when I do the below with user and password credentials it work

mysqldump -p3306 -h<dns/ip> --user=backup --password=<secret> db-name table-name > D:\table-name.sql

I cant get my head around this can someone please help

Or you can put the credentials in a .cnf file in the correct section. Mysql commands will search that file and use those. This is typically how servers do local backups and maint scripts and such things. I.e.
In /etc/my.cnf
# bottom of my.cnf file
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Then in some file such as /etc/my.cnf.d/credentials.cnf
#
[client]
user                  =  admin
password              =  ';\m^P[9MVJ'
#
[mysqldump]
user                  =  root
password              =  ';\m^P[9MVJ'
#

Make only root has rights to read them. I.e.
-rw-------. 1 root root  657 Dec 1 14:22 /etc/my.cnf.d/client.cnf


sp_executesql not accepting multiple types I have the following table:
CREATE TABLE [dbo].[Settings](
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [Key] [varchar](50) NOT NULL,
   [Value] [nvarchar](max) NULL,
   CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED ([Id] ASC)
   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I'm trying to run this query
exec sp_executesql
   N'INSERT INTO Settings ([key], [value]) VALUES (@k6,@v6), (@k8,@v8)',
   N'@k6 nvarchar(4000), @v6 nvarchar(4000), @k8 nvarchar(4000), @v8 bit',
   @k6=N'DefaultLocale', @v6=N'eb', @k8=N'ReCaptchaIsEnabled', @v8=1

Unfortunately, I'm getting this error:

Conversion failed when converting the nvarchar value 'eb' to data type
bit.

When I remove the bit definition or remove Nvarchar definition it working well. From what I see, I cannot mix different value types in this query.
Can you tell me why?
Thank you

It's the same reason this doesn't work:
DECLARE @x nvarchar(4000) = N'foo', @y bit = 1;

SELECT x = @x
UNION ALL
SELECT y = @y;

Same error. If you're going to use the wrong data types (for example, key is varchar(50), so why is @k6 nvarchar(4000)?), you're going to need to massage them to prevent unexpected errors that come from how SQL Server might construct an execution plan. Try making your statement:
INSERT INTO dbo.Settings ([key], [value])
VALUES (@k6,CONVERT(nvarchar(max),@v6)),
       (@k8,CONVERT(nvarchar(max),@v8));


Difference between backup methods: Oracle and MySQL I was reading that Oracle databases have cold, warm, full and redo backups.

Questions

How is it possible that MySQL doesn't have as many options?
I have read that MySQL has cold backups (it puts the DB into a lock mode by itself); are there any other methods?
How can I implement methods similar to those that Oracle has in MySQL?

Because MySQL is not as mature.

But there are options, such as Percona's (free) hot backup for innodb:

http://www.percona.com/software/percona-xtrabackup/

There is also MySQL Enterprise Backup from Oracle, but it is not free.


SSIS: "The buffer manager cannot write 8 bytes to file" We have about 60 or more SSAS cubes on one server. All the cubes have the same structure. Each cube has a different size because each cube has a different underlying data warehouse. However the schema of the data warehouses are the same, the only difference is the data.

Each data warehouse is populated using the same ETL job in SSIS. A parameter is passed into the SSIS package that changes the source and destination.

To speed processing we created partitions in the cube. The cubes are processed and ETL ran individually throughout the day with some overlap.

I am looking for advice about how to debug the following error messages we get from SSIS:

  The buffer manager cannot write 8 bytes to file
  "C:\Users\SQLSER~1\AppData\Local\Temp\DTS{A627C703-7524-4906-84D5-6D239B9745FF}.tmp".
  There was insufficient disk space or quota

  Failed to load task "DFT Load Target Stage", type "SSIS.Pipeline.2".
  The contact information for this task is "Performs high-performance
  data extraction, transformation and loading;Microsoft Corporation;
  Microsoft SQL Server v10; (C) 2007 Microsoft Corporation; All Rights
  Reserved;http://www.microsoft.com/sql/support/default.asp;1".

  Unable to load XML data into a local DOM for processing.

  Error 0x800705AF while loading package file "D:\eTrigue
  Library\eTrigue\eTrigue Deploy\BETA\Exec
  Packages\PKG_EventActivity_Staging.dtsx". The paging file is too small
  for this operation to complete.

Why am I not getting the errors every time the SSIS packages are run?

How can I go about finding the root of the problem?

The errors started to happen after I added the partitions to the cube.  I use an SSIS script task to processes partitions that is called in the ETL.

Is there a way to find memory leaks in SSIS script tasks?

Anybody want to see the code?

You don't have sufficient disk space to process everything you're trying to do concurrently.  Data flow operations that spill to disk and temp files that get created are just some of the many reasons you might run out of space.

You should be able to run Perfmon during one of these runs to see how disk space and activity play out.

It's next to impossible for us to recommend anything without intimate knowledge of your system.  The fact you're running several processes concurrently - where the actual speed and state of each process can vary widely from run to run - makes it impossible to know when or why the processes are encountering the "perfect storm" of starving each other of disk space.

You will have to understand the resource usage of each one of your processes independently then you'll be able to know if you should even try to run them concurrently.


Locks in SQL Server - Update/Select I am new to SQL Server and am not sure about how the locking mechanism works here. Please help

Issue: Need to delete close to 5 mil records in a table. A website pulls data from that table quite frequently.

My understanding is: delete would lock only the row hence this shouldn't affect the website. Is that right? Then, what would happen if the website is trying to pull the record that is being deleted? I know I sound very amateurish but I badly need to understand these basics.

Could one of the gurus please share a link where I can read up about the various kinds of locks in SQL Server as well

Sql Server's locks are described in good detail in the product documentation. A lock might or might not be escalated to page/table level, the behavior is documented, again, in Technet.

A fairly common tactic for deleting large amount of rows is to split up the delete in batches. Instead of deleting all the 5M rows at once like so,

DELETE FROM MyTable WHERE foo='bar'

rather try and delete data in, say, 100 * 50k row batches like so,

WHILE (@rows > 0)
BEGIN
    DELETE TOP (50000) FROM MyTable
    WHERE foo='bar'
    SET @rows = @@ROWCOUNT
END

By splitting the delete in batches, you should lock way less data than with a all-encompassing single-pass delete. YMMV, depending on the selectivity and other activity in your table.

In addition, keep in mind that delete is logged and you might get a nasty surprise with your transaction log size.

This article has nice an overview about how to batch delete data and mind the logs too.


Migrating all MySQL databases from from one server another. Getting error during import I create a mysqldump on all the databases on the source server with this command:

  mysqldump -h mysql-01 -u root -p --all-databases > newdump.sql

This takes about an hour, but runs fine.

Now, to migrate the data of the dump file on the destination server I run this:

  mysql -h mysql-vm-01 -u root -p < newdump.sql

all seems to be going fine for like 30 minutes and then I get this error

  ERROR 1449 (HY000) at line 100083 : The user specified as a definer ('dc_reporter'@'%') does not exist.

What should I do ??

Perhaps you should dump the users and load the users into the VM

Here is my post : Export all MySQL users

After running the code in it and saving it to a file called MyDatabaseUSers.sql, you log into mysql on the VM and run this:

mysql> source MyDatabaseUSers.sql

This will create all the users with the same grants and passwords. Don't worry: Passwords are encrypted.

After importing the users, you should be able to reload the mysqldump.

GIVE IT A TRY !!!

NOTE: If you loaded all your data successfully, the error might have occurred loading a MySQL object that has a DEFINER associated with it

stored procedures
triggers
views
events.

Do the following:

cat newdump.sql | grep -A 2 -n "dc_reporter"

This will show you what might not have loaded. Your data may all be there.

You could try dumping your stored procedures and triggers and reloading them instead of reloading everything. Please read my old post Can mysqldump dump triggers and procedures?, go dump your triggers and stored procedures, and reload them into the VM. If you have views to import, please read my old post Modify DEFINER on Many Views and use the code to mysqldump just the views.


Startup failing with ORA-14694 after running utl32k.sql in upgrade mode Here are the steps

Creating the db on Oracle 12c (12.2.0.1.0)
Creating the admin views and tables
Starting the db in upgrade mode and running utlk32.sql
Starting up is failing with these errors codes and messages

  ORA-00603: ORACLE server session terminated by fatal error
  ORA-01092: ORACLE instance terminated. Disconnection forced
  ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration

Can someone please help me to fix this
The following are the detailed steps of the commands:

orapwd file=$ORACLE_HOME/dbs/pwdorclpdb1.ora password=mypwd entries=5
sqlplus /nolog
CONNECT / AS SYSDBA
startup nomount
SET SQLBLANKLINES ON

Create the database

CREATE DATABASE mydb
 USER SYS IDENTIFIED BY mypwd
USER SYSTEM IDENTIFIED BY mypwd
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/mydb/redo01.log') SIZE 100M,
        GROUP 2  ('/u01/app/oracle/oradata/mydb/redo02.log') SIZE 100M,
        GROUP 3 ('/u01/app/oracle/oradata/mydb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET AL32UTF8
DATAFILE '/u01/app/oracle/oradata/mydb/system01.dbf' SIZE 325M REUSE
         AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
        '/u01/app/oracle/oradata/mydb/system02.dbf' SIZE 325M REUSE
         AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
        '/u01/app/oracle/oradata/mydb/system03.dbf' SIZE 325M REUSE
         AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u01/app/oracle/oradata/mydb/sysaux01.dbf' SIZE 325M REUSE
 AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL
DEFAULT TABLESPACE def_t DATAFILE '/u01/app/oracle/oradata/mydb/def_t.dbf'
  SIZE 100M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp_t
   TEMPFILE '/u01/app/oracle/oradata/mall/temp_t.dbf' SIZE 100M REUSE
   AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
UNDO TABLESPACE wcs_undo_t
   DATAFILE '/u01/app/oracle/oradata/mydb/undo_t.dbf' SIZE 200M REUSE
   AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;

Create dictionary tables and views

@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/sqlplus/admin/pupbld.sql

Create users

shutdown immediate
startup
alter session set "_ORACLE_SCRIPT"=true;
CREATE USER user1 IDENTIFIED BY mypwd DEFAULT TABLESPACE def_t TEMPORARY TABLESPACE temp_t QUOTA UNLIMITED ON def_t;
GRANT CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM,
CREATE TABLE, CREATE TRIGGER, CREATE VIEW, CREATE MATERIALIZED VIEW TO user1;

Apply Oracle12c extended type

PURGE DBA_RECYCLEBIN;
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED;
@?/rdbms/admin/utl32k.sql
show parameter MAX_STRING_SIZE
NAME               TYPE        VALUE
------------------ ----------- --------------
max_string_size    string      EXTENDED

SHUTDOWN IMMEDIATE;

The issue - All previous commands were successful.

STARTUP;
ORACLE instance started.
Total System Global Area 3221225472 bytes
Fixed Size                  8625856 bytes
Variable Size            2365587776 bytes
Database Buffers          838860800 bytes
Redo Buffers                8151040 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration
Process ID: 20774
Session ID: 2 Serial number: 387

I figured it out
- PURGE DBA_RECYCLEBIN;
- CREATE SPFILE FROM PFILE = '$ORACLE_HOME/dbs/initorcl.ora';
- SHUTDOWN IMMEDIATE;
- startup mount;
- ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED SCOPE=SPFILE;
- shutdown;
- STARTUP UPGRADE;
- @?/rdbms/admin/utl32k.sql
-show parameter MAX_STRING_SIZE
    NAME               TYPE        VALUE
    ------------------ ----------- --------------
    max_string_size    string      EXTENDED
- SHUTDOWN IMMEDIATE;
- STARUP;

Now startup is successful and max_string_size = EXTENDED


Making database tables 1NF I am creating a database for a DVD rental shop, I have various entities that are related to this question, such as Film, FilmStar.

  For each film, you record its unique number, title, the year in which it was made, its category (action adventure, science fiction, horror, romance, comedy, classic, children's), its director, and all stars that appeared in it. For each film, you also want to store the type of DVD hire (new release, classics, other).

I am mostly unsure about "all the stars that appeared in it". I first thought just having an attribute in the 'Film' Entity, for example filmStar and then each star would be inserted into that attribute, for example: "John Doe, Jane Doe" for each film. But then I realised that this wouldn't be 1NF as : "the domains of attributes must include only atomic values, the value of an attribute must be a single value from the domain of that attribute", as it contains more than one value and isn't atomic.

I then thought about having a separate entity that contains certain attributes such as: filmID, filmStarID. So John Doe would have the filmStarID of '0001' (all of this would be in the FilmStar entity, which is a separate entity). But then the same problem would occur, for example the filmID attribute would have all of the filmID's that the filmStar has starred in, for example: John Doe would have "101, 115, 009". Which again wouldn't be 1NF.

I was just wondering what your thoughts are on this?

You're on the right track with splitting up your domains and using a linking entity.  Separating the Films from the Stars is absolutely the first step.

 ---------------------------    -------------------------
| FilmID |     FilmName     |  | StarID |   StarName     |
|--------+------------------|  |--------+----------------|
| EVIL01 | The Evil Dead    |  | 000001 | Bruce Campbell |
| EVIL02 | Evil Dead II     |  | 000002 | Sarah Berry    |
| EVIL03 | Army of Darkness |  | 000003 | Marcus Gilbert |
 ---------------------------    -------------------------

The missing piece is tying the two together.  Obviously, stuffing the StarIDs into the Film table is in violation of the first normal form, since you end up needing to store multiple references to actors in a single row.

 --------------------------------------------
| FilmID |     FilmName     |     StarIDs    |
|--------+------------------+----------------|
| EVIL01 | The Evil Dead    | 000001         |
| EVIL02 | Evil Dead II     | 000001, 000002 |   Wrong,
| EVIL03 | Army of Darkness | 000001, 000003 |   Wrong
 --------------------------------------------

Splitting the offending column out to its own table restores the integrity of the original Film table, but continuing to store multiple references in a single attribute still invalidates 1NF for the data model.

 ---------------------------    -------------------------
| FilmID |     FilmName     |  | FilmID |     StarIDs    |
|--------+------------------|  |--------+----------------|
| EVIL01 | The Evil Dead    |  | EVIL01 | 000001         |
| EVIL02 | Evil Dead II     |  | EVIL02 | 000001, 000002 |   Still
| EVIL03 | Army of Darkness |  | EVIL03 | 000001, 000003 |   Wrong
 ---------------------------    -------------------------

To rectify this is fairly simple, as you merely need to associate each FilmID with the respective StarID, bringing your model in-line with the tenants of 1NF.

 ---------------------------    -----------------    -------------------------
| FilmID |     FilmName     |  | FilmID | StarID |  | StarID |   StarName     |
|--------+------------------|  |--------+--------|  |--------+----------------|
| EVIL01 | The Evil Dead    |  | EVIL01 | 000001 |  | 000001 | Bruce Campbell |
| EVIL02 | Evil Dead II     |  | EVIL02 | 000001 |  | 000002 | Sarah Berry    |
| EVIL03 | Army of Darkness |  | EVIL02 | 000002 |  | 000003 | Marcus Gilbert |
 ---------------------------   | EVIL03 | 000001 |   -------------------------
                               | EVIL03 | 000003 |
                                -----------------

Now, with the the data normalized in this manner, it is easy to add attributes to expand the model.  Say an actor's character name in a movie was desirable, you could add CharacterName to the FilmStar table - maybe useful for getting a list of everybody who's ever played "James Bond," for instance.


How to bring data from two tables that could/could not be present in one of them Background: I have two tables, let's call them "purchaseTransactions" and "Products", the first one, holds all the transaction made by product, and the second one has all my products available, let's see an example oh how the look:

PurchaseTransactions

PurchaseDate
ProductId
Quantity
Some other fields...

Products

ProductName
ProductNumber
Some other fields...

Basically what I want to obtain is the quantity sold by month and product from a specific timelapse, For instance:

Let's say we have the Products

Name: Product A, No: 01
Name: Product B, No: 02
Name: Product C, No: 03

For Purchase Orders

PurchaseDate: Jan/01/2018, ProductId: Product A, Quantity: 100
PurchaseDate: Jan/18/2018, ProductId: Product A, Quantity: 150
PurchaseDate: Feb/02/2018, ProductId: Product A, Quantity: 150
PurchaseDate: Feb/24/2018, ProductId: Product A, Quantity: 200
PurchaseDate: April/04/2018, ProductId: Product A, Quantity: 200
PurchaseDate: January/01/2018, ProductId: Product B, Quantity: 222
PurchaseDate: March/02/2018, ProductId: Product B, Quantity 250
PurchaseDate: May/04/2018, ProductId: Product B, Quantity: 333
PurchaseDate: May/20/2018, ProductId: Product B, Quantity: 666

An example of what I want is to bring ALL the products sold grouped by Year,Month, Product and Sum of its quantity. Something like this.

 Year Month, ProductId, QuantitySold
 -----------------------------------
 2018 Jan   Product A   250
 2018 Jan   Product B   222
 2018 Jan   Product C   0
 2018 Feb   Product A   250
 2018 Feb   Product B   0
 2018 Feb   Product C   0
 2018 March Product A   0
 2018 March Product B   0
 2018 March Product C   250
 .... etc

Notice that the query should return even the products that does not have any purchases in the specified month, this is what I have tried so far:

SELECT YEAR(PurchaseDate), MONTH(PurchaseDate), ProductId, SUM(Quantity)
FROM PurchaseOrders
WHERE Product IN (Product A, Product B...) AND MONTH(PurchaseDate) => DATEADD(MONTH, -3, GETDATE()) --Could be by some specific products and for a specific timelapse, ex the past three months.
GROUP BY YEAR(PurchaseDate), MONTH(PurchaseDate), ProductId

But this only returns the Products existing in the Purchase table, I have tried with a LEFT JOIN with the Products table to get all the products, but I think I am not implementing it correctly.

Any Idea of how to do this?

Try changing your GROUP BY YEAR(PurchaseDate)...
to GROUP BY ALL YEAR(PurchaseDate)...

  GROUP BY ALL -> "Specifies to include all groups in the results regardless of whether
  they meet the search criteria in the WHERE clause. Groups that don't
  meet the search criteria have NULL for the aggregation."

For some reason, MS decided to deprecate the feature, but it still works.
See the GROUP BY topic in the documentation.


More Fields or multiple tables I run multiple pool leagues and have been keeping the information in excel. I'm working on moving this to the web and using MySQL for the data. The largest amount of data is in the matches table, with information about every match played.

I have multiple leagues based on different games, straight pool, one pocket, 9 Ball & 8 Ball. There are a few fields specific to each game, like high run in straight pool but no others, break & run in 8 Ball and 9 Ball but not the other two, 8 on the break in 8 Ball but no others, etc.

I could put all of these fields in one table and have a field called 'game' which flags which game this record is about. Or, I could have a table for each game, with all and only the fields needed for that game.

The single table, if I go that route, will be small to start, but if I'm able to expand the number of places where I have these leagues the number of records could grow to 20,000 or more in a couple years.

Which way would be best?

P.S. I do not envision running queries that look at records of more than one game in the same query.

If your queries know beforehand which type of game you're refering to, and you are only having one game per query, the cleanest way is most probably to have different surrogate-tables for different games, and always JOIN the main table with the one specific for your type of game.

For instance, you would have a table for all the different types of games:

 CREATE TABLE games
 (
      game_id integer PRIMARY KEY,
      game_name varchar(100) NOT NULL
 ) ;

 INSERT INTO games
     (game_id, game_name)
 VALUES
    (1, 'straight pool'),
    (2, 'one pocket'),
    (3, '9 Ball'),
    (4, '8 Ball') ;

A table for players (and probably for leagues, seaons, etc.)

 CREATE TABLE players
 (
     player_id integer PRIMARY KEY, -- probably autoincrement
     player_name varchar(100)
 ) ;

 INSERT INTO players
    (player_id, player_name)
 VALUES
    (1, 'Player 1'),
    (2, 'Player 2'),
    (3, 'Player 3'),
    (4, 'Player 4') ;

Your matches table:

 CREATE TABLE matches
 (
      match_id integer PRIMARY KEY, -- probably autoincrement

      game_id integer NOT NULL REFERENCES games (game_id),
      player_1_id integer NOT NULL REFERENCES players (player_id),
      player_2_id integer NOT NULL REFERENCES players (player_id),
      match_date date NOT NULL,
      -- probably a league_id
      -- probably a season_id
      -- some other data common to all matches

      UNIQUE (game_id, player_1_id, player_2_id, match_date)  -- Alternate key
 ) ;

And then, for each specific game type, a table with all the columns that makes sense:

 CREATE TABLE straight_pool_matches
 (
    match_id integer REFERENCES matches(match_id),
    -- Don't know enough to know what to put here
    info_about_straight_pool_matches varchar(255)
 ) ;

So, let's image there is a match of "straight pool" on 1st Jan 2017, between players 1 and 2. You would add data to two tables:

 INSERT INTO matches
     (match_id, game_id, player_1_id, player_2_id, match_date)
 VALUES
     (1, 1, 1, 2, '2017-01-01') ;

 INSERT INTO straight_pool_matches
     (match_id, info_about_straight_pool_matches)
 VALUES
     (1, 'Something that makes sense about a straing pool match') ;

And the way you would perform your queries would most probably be:

 SELECT
     m.match_id, p1.player_name AS player_1, p2.player_name AS player_2, m.match_date,
     s.info_about_straight_pool_matches
 FROM
     matches m
     JOIN players p1 ON p1.player_id = m.player_1_id
     JOIN players p2 ON p2.player_id = m.player_2_id
     JOIN straight_pool_matches s ON s.match_id = m.match_id
 WHERE
     m.game_id = 1
 ORDER BY
     m.match_date, m.match_id ;

 match_id | player_1 | player_2 | match_date | info_about_straight_pool_matches
 -------: | :------- | :------- | :--------- | :----------------------------------------------------
        1 | Player 1 | Player 2 | 2017-01-01 | Something that makes sense about a straing pool match

dbfiddle here


Permanent count of waiting tasks (2) in Activity monitor Now I'm observing in Activity Monitor permanent count of waiting tasks:

and at "Resource waits" tab I see next:

so, my question is: How to find and resolve this wait tasks?

I suspect that it related with transactional replication, which running on the server. This wait tasks I see only on Distributor. This issues appeared after restarting SQL server service.

sp_whoisactive - doesn't show any locks.

Thanks a lot for any information.

Apart from Resources waits tab i believe you should expand the tab above which says processes.The Processes pane shows the information about the currently running processes on the SQL databases, who runs them, and from which application.

Also, you can simultaneously use below script to find the info on whats running and causing waits thereby:

SELECT
      [spid] = session_Id
    , ecid
    , [blockedBy] = blocking_session_id
    , [database] = DB_NAME(sp.dbid)
    , [user] = nt_username
    , [status] = er.status
    , [wait] = wait_type
    , [current stmt] =
        SUBSTRING (
            qt.text,
            er.statement_start_offset/2,
            (CASE
                WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt.text)
                ELSE er.statement_end_offset
            END - er.statement_start_offset)/2)
    ,[current batch] = qt.text
    , reads
    , logical_reads
    , cpu
    , [time elapsed (ms)] = DATEDIFF(mi, start_time,getdate())
    , program = program_name
    , hostname
    --, nt_domain
    , start_time
    , qt.objectid
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50              -- Ignore system spids.
AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
ORDER BY 1, 2
GO


How to use three query as a subquery? Below is query ,i want to convert it into sub query
    declare @FirstDOM datetime, @LastDOM datetime
set @FirstDOM = (select CONVERT(DATE,dateadd(dd,-(day(getdate())+30),getdate())) )
set @LastDOM = (select dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())+1,0)))

--First Query

SELECT SUM(QTY) AS QTY, SUM(IWeight) AS Weight, EntryDate
FROM     ConIssuance
where EntryDate between @FirstDOM and @LastDOM
GROUP BY EntryDate
order by EntryDate asc

--Second Query
SELECT SUM(s.prdqty) AS qty, SUM(i.weight) AS Expr1, s.EntryDate
FROM     Probale AS s INNER JOIN
                  ItemMasterFile AS i ON i.CodeItem = s.Codeitem
                  where S.EntryDate between @FirstDOM and @LastDOM
GROUP BY s.EntryDate

--Third Query

SELECT SUM(Bweight) AS weight, SUM(Bpqty) AS qty, EntryDate
FROM     Bigbalprd
 where EntryDate between @FirstDOM and @LastDOM
GROUP BY EntryDate

Output  Horizontally.

you can do it this way:
;WITH FirstQuery  AS (
    SELECT SUM(QTY) AS QTY, SUM(IWeight) AS Weight, EntryDate
    FROM     ConIssuance
    where EntryDate between @FirstDOM and @LastDOM
    GROUP BY EntryDate
    order by EntryDate asc
)
,SecondQuery AS (
    SELECT SUM(s.prdqty) AS qty, SUM(i.weight) AS Expr1, s.EntryDate
    FROM     Probale AS s INNER JOIN
                      ItemMasterFile AS i ON i.CodeItem = s.Codeitem
                      where S.EntryDate between @FirstDOM and @LastDOM
    GROUP BY s.EntryDate
)
,ThirdQuery AS (
    SELECT SUM(Bweight) AS weight, SUM(Bpqty) AS qty, EntryDate
    FROM     Bigbalprd
     where EntryDate between @FirstDOM and @LastDOM
    GROUP BY EntryDate
)
SELECT *
FROM FirstQuery
JOIN SecondQuery ON SecondQuery.EntryDate = FirstQuery.EntryDate
JOIN ThirdQuery ON ThirdQuery.EntryDate = FirstQuery.EntryDate;

If you have gaps (no records for some dates in some of three tables) you may need to use FULL JOIN with a bit more complex join conditions or even generate a list of dates (in case if you need a row for each date even when data for this date is not presented in any of your tables)


Multiple Instances Of SQL Server Windows NT Eating Up Resources - What do they do? I am fairly new to MS SQL Server and am curious as to what each instance of SQL Server Windows NT does. I currently have 4 of them running, and they are consuming most of my test server's resources. A few days ago my cpu would idle around 0~5% usage even with the 4 instances of SQL Server Windows NT running, but now they've gone wild and are using up almost all of my cpu and ram. (See image below)

I am only running 1 database as far as I know, and was curious what would happen - or if I could, end the task on 2 or 3 of these.

I also tried restarting my database, but they all came right back.

When you install SQL Server it installs some shared components and also some components that are specific to the instance itself. In its simplest form, an instance can be likened to a virtual machine or a container running on a host server. You can have multiple instances but each one requires it's own resources and has it's own limitations in terms of how many databases may be created. Check out the Microsoft docs to understand more about instances and shared components.

Something to be aware of especially if you are new to SQL Server or come from an Oracle background is that you do not need to install more than one instance to have more than one database. Each instance of SQL Server can have up to 32767 databases on it. More information on limits can be found on Microsoft Docs.

A common scenario for having multiple instances is when you need to develop against different versions of SQL Server. For example, on my one of my development workstations I have 2008, 2008R2, 2012 and 2014 installed. However, I normally would not have them all active at one time.

From your question above it sounds like you don't have many databases so unless you need to isolate a development environment (such as having two separate 2014 instances e.g. one DEV and one QA) or support multiple versions of SQL Server, I would actually recommend uninstalling the instances that you don't require - not only to free up CPU and RAM but also to free up any disk space that was used. Be sure to back up anything you may need first. To uninstall SQL Server follow these instructions.

Alternatively you can disable the service's so that they do not automatically start when you boot your machine. The instructions on how to do this can be found here. The only side-effect to stopping the service is that you will no longer be able to connect to the instance. But you could easily restart it when you do need it.

If you do require all instances to be running at the same time, then I would suggest setting a limit to how much RAM and CPU resources are allocated to each instance. I suggest looking at the documentation on how to set the max memory and processor affinity. However, as a new user to SQL Server I would recommend one of the solutions I proposed earlier over changing server properties until you understand them better.


False-failures when mirrored in high-safety mode without a witness A number of DBs are mirrored in high-safety mode without a witness. I'm trying to understand what might happen in the case that a DB (or DBs) experiences a false-failure while the primary server remains up and functioning and all other mirrored DBs are in their normal SYNCHRONIZED state.

Would the mirroring state for the false-failure DB become DISCONNECTED but, because the server had not failed, the DB on the primary still be able to accept connections and do transactions, although it would be running unprotected?  Or, would the DB on the primary become DISCONNECTED and unavailable?  And, what would be the recovery for this?  Is it most likely to be a simple ALTER DATABASE  SET PARTNER RESUME?

Previously, we've had these DBs in high-safety mode with witness (auto failover) and increased the timeout to 20 seconds to eliminate false-failures, which worked well for a year and a half.  But, this week, we had a single DB failover for unknown reasons.  I've increased the timeout to 30 seconds but we're also considering removing the witness because the app needs manual intervention in the case of failover anyway so we might as well make the DB failover manual as well.  I'm fine with dealing with a planned failover but I'm not so comfortable with the case I described.

OK, so this is high safety without automatic failover with no witness.

Here's the official books online answer:

  If the mirror server instance goes down, the principal server
  instance is unaffected and runs exposed (that is without mirroring the
  data)." Source

I have seen that to be true in my experience. In other words, disconnected, but available for reads and writes. The transaction log will grow until the situation is fixed, because it needs to keep the info to send to the mirror when it comes back around.

Note: I absolutely recommend setting up a test environment for yourself before you make any changes. Don't just take my word for it. You want a place to be able to stage the changes and need to test what happens for yourself before going live.

Edit: As for what happens when the mirror comes back, typically it will automatically resume in my experience, but I know of cases where it hasn't and TSQL had to be run to make it resume. Very easy to simulate in a demo environment to get yourself comfortable with it.


how to sum decimal values in sql server I am working on an sql queries where i have a temp table named

 DECLARE @tcal table(TagIndex smallint,TagName nvarchar(20),Total decimal(18,2))

in which decimal numbers are used to stored. The values coming are:

284.45
233.97
240.17
81.39
67.55
0.00
71.57
72.91

I used to sum all the above decimal number by using following query:

select  sum(CAST(Total AS decimal(18,2)))  FROM @tcal where TagIndex>44

The sum coming after the query is :-

767.56

But the actual result should be:

1052.01

How to solve it?

Ok it's not an anwer just a better formated comment because you are wrong in some way, I has writen the below example.

DECLARE @tcal table(TagIndex smallint,TagName nvarchar(20),Total decimal(18,2)) ;

insert into @tcal
values
 (45,null,284.45)
,(45,null,233.97)
,(45,null,240.17)
,(45,null,81.39)
,(45,null,67.55)
,(45,null,0.00)
,(45,null,71.57)
,(45,null,72.91) ;

select  sum(CAST(Total AS decimal(18,2)))  FROM @tcal where TagIndex>44 ;
select  sum(Total)  FROM @tcal where TagIndex>44 ;

and it works. So you are filtering your data wrong or did some mistake.

My guess is your data is missing the first row because it's tag = 44.

(Remember in SQL > <> >=  =))


Reporting services existing in SQL Cluster instance but unable to see on SSMS? This is first time I am working on SQL Failover Cluster environment and I installed Reporting services on SQL server Failover Cluster Instance both Active Nodes(Node1 & Node2) and configured through Reporting services configuration manager, when I was opened SSMS I haven't find any Reportserver and ReportserverTempDB Databases. Which means did I miss something in installation? or we have to configure those separately? When I was trying to add Reporting services feature I got an error like below.

If you used the Reporting services configuration manager to configure reporting services, which SQL instance did you point Reporting Services at?  Go back into Reporting services configuration manager and confrim which instance it's pointing to, this should be the instance that the ReportServer and ReportServerTempDB databases reside on.


How to create a differential backup considering the base the previous differential backup in SQL Server? I am studying differential backups. I know that I can create a differential backup which will contain all the modified data from last full  backup.
I wonder isn't it possible to create a backup from the last differential backup?
I mean to create a base full backup, then differential backup1, which contains all the data modified from full backup, then create backup2 which contains modified data from backup1, not full backup.

If it is possible how can I do it? Also can I do the same only with Log file backups?

Thanks for your time.

You are essentially asking for incremental, which is pretty much what a log backup is.  So your Diff1 and Diff2 would actually be log backups, which still gets you point in time restores.

Why are you trying to do this...purely academic reasons?


Can't find location of undo *.tuf file I run SQL Server 2000. Database is in standby mode. I have multiple files with MyDb.tuf name on a different drives. How may I know which one is in use?

I would expect the one with the most recent datetime stamp to be the one that is in use.


MySQL allows records longer than varchar(N) We have a database with a field of type VARCHAR(255) but in it I can see some records having a length of larger than 255, such as 264. How can it be possible? Can it be that the database is corrupt? Or somehow MySQL allows larger records? Or is there a known bug?

MySQL version: 5.5.47

Charset: utf8mb4

Use CHAR_LENGTH - you presumably have some > 1 byte characters and LENGTH returns bytes.

You can read more about the string functions in the manual:

MySQL 5.5 Reference Manual / Functions and Operators / String Functions


What is aggregation in SQL Profiler? I want to know what is an aggregated result of an event and what to do in order to get aggregated results?

Thanks for your time!

Say you aggregate on the LoginName column for instance. Then you get one blue section for each login name, and it will show you also how many events you have for that login name. To see the actual events, for that login, you look at the white rows under that blue row. In your example, you have aggregated over duration, and in the pic you included you have no two events with the same duration. Aggregating over something which has very few duplicates isn't very meaningful. You can think of aggregation such as a group by with a COUNT(*), that is what you see in the blue section for each value in the columns that you "grouped/aggregated" over.


Availability Groups automatic failover is not so automatic Behavior when I stop SQL Server Service :

The availability group fails (resolving on SECONDARY SQL SERVER and fail state on Cluster management).
SQL Server does not connect with the Cluster ip (I'm using a fixed cluster ip inside the software for it to connect on both SQL1 and SQL2. using x.x.x.10 should connect me on both x.x.x.9 (SQL1) and x.x.x.8(SQL2) because of the windows failover cluster.

I need to manually move AG to secondary ( I connect on SQL2 and right click > failover).

To return everything as it was, I need to start SQL1 SERVICE manually, connect on SQL1 SSMS and right click > failover manually. Stop NODE2 service on cluster management so it turns SQL1 node primary again.

Behavior when I stop the primary cluster node:

The availability group goes to SQL2/NODE 2 (Secondary is now primary).
Primary AG is not Resolving...
SQL Server connect with the cluster ip ( I'm talking about SSMS . connecting with ip X.x.x.10,  connects me with x.x.x.8 that is the secondary node, after killing node x.x.x.9 ( Primary ).
When I start the cluster node (primary) again, it doenst automatically returns do SQL1 (primary). connecting with x.x.x.10 still connects me with the secondary node.
If I want to make SQL1 primary again, I need to **stop** node2 cluster service and start it again. this way, SQL1 (primary) is primary again connecting with Cluster IP.

This is totally not the automatically I think I would have. I thought that if SQL Server dies, all connections goes to the secondary automatically, but it just failover if the primary NODE is down (not sql server service). and then to return to the primary, I need to STOP SQL2 cluster service, so the primary becomes primary again.

Is there something that I'm missing?

We are not using a Failover Cluster with shared storage. There are 2 servers with their own disks.

You have a quorum issue in this setup.  There are only two participants in the cluster:

SQL1
SQL2

If SQL1 goes down, there's no way for SQL2 to know that it should be the primary.  For all SQL2 knows, the network has gone down and SQL1 is still operating as the primary.  Thus, SQL2 goes to the RESOLVING state (it's not sure what to do).  This is to prevent a "split brain" scenario.
For a two-node cluster, generally you need to add a File Share Witness as a resource in the WSFC cluster.  This will allow the remaining instance, SQL2, to establish quorum with the FSW, and take over as the primary.
Additionally, the behavior of AGs is not to automatically fail back to the "original primary" as soon as it's back up.  Automatic failover is only in the case of an outage on the current primary, it's not intended to "automatically fail back."

Note that the other scenario you described, stopping the primary cluster node, is not how you want your AGs to failover.  Don't do that:

Do not use the Failover Cluster Manager to manipulate availability groups, for example:

Do not add or remove resources in the clustered service (resource group) for the availability group.

Do not change any availability group properties, such as the possible owners and preferred owners. These properties are set automatically by the availability group.

Do not use the Failover Cluster Manager to move availability groups to different nodes or to fail over availability groups. The Failover Cluster Manager is not aware of the synchronization status of the availability replicas, and doing so can lead to extended downtime. You must use Transact-SQL or SQL Server Management Studio.


SQL Grouping rows in a feed database table, output only grouped rows adding a group row I'm trying to write an SQL query to work on a raw feed that's been imported to a MySQL database table.  The table's rows loosely resemble this example.

| sku        | group_name | description       | size | type   |
|------------|------------|-------------------|------|--------|
| EX-1000-XS | NULL       | Long text         | XS   | single |
| EX-1000-S  | NULL       | Long text         | S    | single |
| EX-1000-M  | NULL       | Long text         | M    | single |
| EX-1000-L  | NULL       | Long text         | L    | single |
| EX-1001-M  | EX-1001    | Another long text | M    | single |
| EX-1001-L  | EX-1001    | Another long text | L    | single |
| EX-1001    | NULL       |                   |      | group  |

I'm trying to get the ones without a group_name, but with the determined group name set, along with a group row. Ignoring stuff that already was in a grouping. So the output I want from the query is like this.

| sku        | group_name | description       | size | type   |
|------------|------------|-------------------|------|--------|
| EX-1000-XS | EX-1000    | Long text         | XS   | single |
| EX-1000-S  | EX-1000    | Long text         | S    | single |
| EX-1000-M  | EX-1000    | Long text         | M    | single |
| EX-1000-L  | EX-1000    | Long text         | L    | single |
| EX-1000    | NULL       |                   |      | group  |

I've figured out some substring function on the sku column, to get a "key" like value,

SELECT
  SUBSTRING_INDEX(
    sku,
    '-',
    LENGTH(sku) - LENGTH(REPLACE(sku, '-', ''))
  )
FROM
  products
WHERE
  group_name IS NULL
  AND type = "single";

No idea how to use that as a grouping key yet,

I set up a fiddle with the example, https://www.db-fiddle.com/f/ua6iwXGHcPDVKbCkqHvs2E/0

db-fiddle has strange effect on columns are not in order as per sql statement but works fine on my local Mysql 8.0

select sku,
       case  WHEN group_name IS NULL
             AND  type='single'
             THEN  TRIM(TRAILING '-' FROM REPLACE(sku,size,''))
              ELSE group_name
        END
        AS group_name,
        description,
        size,
        type
from products

This gives your desired output with cte expressions i.e excluding existing rows plus grouping key(same output as bottom table in your question).Tested on your dbfiddle

WITH cte1
AS
  (
   SELECT sku,
          TRIM(TRAILING '-' FROM REPLACE(sku,size,''))
          group_name,
          description,
          size,
          type
   FROM   products
   WHERE  group_name IS NULL
   AND    type = 'single'
  )

SELECT * FROM cte1

UNION

SELECT   DISTINCT(group_name) sku,
         NULL group_name,
         '' description,
         '' size,
         'group' type
FROM     cte1


Join tables on a partial string match input: I have a table containing (any number of) user-supplied strings:

╔═══════╗
║ Value ║
╠═══════╣
║ To    ║
║ An    ║
╚═══════╝

And a table of user details:

╔════════╦══════════╗
║ UserID ║ UserName ║
╠════════╬══════════╣
║     12 ║ Tom      ║
║    132 ║ Ann      ║
║     32 ║ Dina     ║
║      2 ║ Mark     ║
╚════════╩══════════╝

I need a query to return all records from the users table where UserName is a partial string match for any Value record in the input table.  Expected output in this case is:

╔════════╦══════════╗
║ UserID ║ UserName ║
╠════════╬══════════╣
║     12 ║ Tom      ║
║    132 ║ Ann      ║
╚════════╩══════════╝

This should do what you want:

DECLARE @Users AS TABLE
(
    UserID      integer,
    UserName    nvarchar(50)
);

INSERT @Users
    (UserID, UserName)
VALUES
    (12, N'Tom'),
    (132, N'Ann'),
    (32, N'Dina'),
    (2, N'Mark');

DECLARE @Input AS TABLE
(
    Value    nvarchar(500) NOT NULL
);

INSERT @Input (Value)
VALUES
    (N'To'),
    (N'An');

SELECT *
FROM @Users AS u
WHERE
    EXISTS
    (
        SELECT *
        FROM @Input AS i
        WHERE
            u.UserName LIKE N'%' + i.Value + N'%'
    );

Output:

╔════════╦══════════╗
║ UserID ║ UserName ║
╠════════╬══════════╣
║     12 ║ Tom      ║
║    132 ║ Ann      ║
╚════════╩══════════╝


How to prevent logins using Windows Authentication in SQL Server? SQL Server supports creating logins using SQL, Windows, certificates and asymmetric keys. Is it possible to remove the option of creating logins with Windows authentication (and just allow SQL authentication for example)?

Natively you cannot prevent using Windows Authentication in SQL Server.

  SQL Server supports two authentication modes, Windows authentication
  mode and mixed mode.

  Windows authentication is the default, and is often referred to as
  integrated security because this SQL Server security model is tightly
  integrated with Windows. Specific Windows user and group accounts are
  trusted to log in to SQL Server. Windows users who have already been
  authenticated do not have to present additional credentials.

  Mixed mode supports authentication both by Windows and by SQL Server.
  User name and password pairs are maintained within SQL Server.

Microsoft also recommends using windows authentication over SQL Server logins.

  We recommend using Windows authentication wherever possible. Windows
  authentication uses a series of encrypted messages to authenticate
  users in SQL Server. When SQL Server logins are used, SQL Server login
  names and encrypted passwords are passed across the network, which
  makes them less secure.

As commented by Nick you can programmatically restrict the creation of logins from Windows user account, trusted domain account, or Windows groups.

Reference: Authentication in SQL Server

A similar question was also answered here by Paul White.


Mongo DB Replica set Stuck at RECOVERING state We have created a replica set and now the problem is 2 members of replica set [3 members set] are in recovering mode from 48 hours. Initially the size of recovering nodes was increasing and now even that has stopped. So in recovering nodes they are stuck after 90 GB of data with 60+ GB of local data.

How to come out of this mode ?

The easy, albeit a bit unsecure way

Stop the first secondary
Delete the content of it's dbpath
Restart the secondary
Wait for it to catch up with the primary
Repeat process with the second secondary

This is a bit unsecure as it is unknown why the secondaries entered the Recovering state.

The more secure, but also more intrusive way

As above, but stop your application during the process. This prevents the possibility that your application is inserting more data than the secondaries are able to replicate. However, the problem may occur during production.

The most secure, but also most intrusive way

Shut down the whole replica set
Remove the content of dbpath on both secondaries
Copy the content of dbpath to both secondaries' dbpath
Start the old primary.
Start one of the old secondaries.
Wait until a new primary is elected.
Start the remaining secondary.

Some notes:

Use MMS. It's free, it's easy to set up and it gives you good information about your replica set. Try to keep the value for "replication lag" around 0, and take all means necessary that your replication lag is never greater than the "replication oplog window".

Always make sure you have a 1Gb network and a (sorry) shitload of RAM. The more, the better. Additional rule of thumb: rather half the RAM and SSDs than double the RAM and no SSDs (with RAM remaining within reasonable limits).

Disclaimer: Always make a backup of production data before fiddling with it.


DBMS_METADATA.GET_DDL not displaying complete DDL for big tables While generating the DDL for a schema, big tables' DDL are getting truncated. After displaying close to 110 rows I am getting an ellipsis (...).

I am using SQL developer to run:

SELECT dbms_metadata.get_ddl('TABLE',TABLE_NAME,OWNER)
FROM DBA_TABLES
WHERE OWNER = 'XYZ'

Just set the "long" in the sql sheet window big enough to hold the desired ddl.

Example:

set long 1000
select dbms_metadata.get_ddl('TABLE', 'XP','OPS$RM74384') from dual;

-- Then just hit Run Script button or press f5.


Is it safe to have Mongo replicas on single server? I am planning a minimum setup for a low traffic discussion forum. Everybody says to use replicas because otherwise I am in a risk of data loss. I see an advantage that I can add new more powerful node without a downtime. While I was reading Mongo performance best practices, I noticed this sentence:

With appropriate sizing and resource allocation using virtualization
or container technologies, multiple MongoDB processes can safely run
on a single physical server without contending for resources.

What do you think? Is it safe? If the server or disk dies then all replicas are down.

Replication is a risk mitigation strategy, amongst other purposes. If you understand those risks, and the consequences should they eventuate, and that's all acceptable in your context then by all means run multiple replicas on the same hardware.
For a dev environment I can imagine this would be OK. A hardware failure would affect one person only. Recovery is likely quick. Replication can be tested at minimal cost.
For production, however, it would be much riskier. Hardware failure brings down the whole service. Recovery requires more, and more invasive, intervention. Resuming normal service may take longer and violate RTO.
My reading of the paragraph quoted in the question is that performance will not be affected by having multiple processes on the the same VM host, as long as those VMs are appropriately configured. I don't read it as a recommendation but as "you can do it this way if you like at it won't necessarily break." My point is that an application architect should also consider availability. That is covered in a separate paragraph

For availability, multiple members of the same replica set should not be co-located on the same physical hardware.


High Network usage on SQL Server port Possible Duplicate:
  Find out what queries are causing the biggest amount of network traffic

We are getting over 90 Mbps from SQL Server to each web servers on port 1433.

We are using connection pooling.
We have CLR Integration enabled.
We are running CDC.
We have snapshot transactions turned on.
We are using SQL Server 2008 R2 SP1

But when we get SQL Profiler on the case and remove all the Login and Logout events we are seeing low activity.

Any recommendation of getting great visibility on what's going over the wire?

Or are the Login and Logout events very chatty over the network?

We used Wireshark and SQL Profiler and were able to uncover an SP returning 700,000 records repetitively.

As far as we were able to tell then login and logout were part of connection pooling.


distrb.exe & Logread.exe using huge SQL.txt file in C:\Users\sqlsrvacct\AppData\Local\Temp folder I have the following situation:
SQL server 2008 R2 SP2
I have both the Distribution and Subscription database in this server.
LogReader agent and Distribution agent are running in continuous mode

the following file is growing currently (103GB) with readable data
C:\Users\svc-20121\AppData\Local\Temp\SQL.LOG

sample content extracted from the file opened with emeditor:

distrib         2780-768    ENTER SQLAllocEnv
        HENV *              0x000000000019AA00

distrib         2780-768    EXIT  SQLAllocEnv  with return code 0 (SQL_SUCCESS)
        HENV *              0x000000000019AA00 ( 0x00000000006CFE00)

distrib         2780-768    ENTER SQLFreeEnv
        HENV                0x00000000006CFE00

.....
SQLAGENT        2af0-1e24   ENTER SQLGetDiagFieldW
        SQLSMALLINT                  3
        SQLHANDLE           0x00000000040DAE80
        SQLSMALLINT                  1
        SQLSMALLINT              -1150
        SQLPOINTER         0x000000001EBBDDFC
        SQLSMALLINT                 -6
        SQLSMALLINT *       0x000000001EBBDDF0

SQLAGENT        2af0-1e24   EXIT  SQLGetDiagFieldW  with return code 100 (SQL_NO_DATA_FOUND)
        SQLSMALLINT                  3
        SQLHANDLE           0x00000000040DAE80
        SQLSMALLINT                  1
        SQLSMALLINT              -1150
        SQLPOINTER         0x000000001EBBDDFC
        SQLSMALLINT                 -6
        SQLSMALLINT *       0x000000001EBBDDF0

When i tried to delete it windows will say it is used by LogRead.exe and Distrib.exe and when i stop them it says another unrelated instance on that machine is using it

any help appreciated?

the tracing was enabled for ODBC and it was reponsile for this file.
I do not know why after Stoppin tracing from below screenshot all instances and agents was using this file and we have to stop them one by one until deleting was possible.


Database design for monthly leaderboards I have a system where users can make predictions on events. There are about two events a week. When the event ends the system scores all the predictions and updates this in the database. Here is an approximate representation of my current DB strucutre:

Users table

+----+-----------+--------+
| ID |  username |  score |
+----+-----------+--------+
|  1 |  alice    |     12 |
|  2 |  bob      |     22 |
+----+-----------+--------+

Games table

+----+-----------------+----------------------+------------+----------+
| ID |    gameTitle    |       gameTime       |  questions |  answers |
+----+-----------------+----------------------+------------+----------+
|  1 |  Football match |  2019-08-11 14:00:00 |  {json}    |  {json}  |
|  2 |  Hockey game    |  2019-07-11 15:00:00 |  {json}    |  {json}  |
+----+-----------------+----------------------+------------+----------+

Predictions table:

+----+--------------+--------------+-------------+--------+-------+
| ID |  gameID (FK) |  userID (FK) |  prediction |  score |  rank |
+----+--------------+--------------+-------------+--------+-------+
|  1 |            1 |            1 |  {json}     |      6 |     1 |
|  2 |            1 |            2 |  {json}     |      4 |     2 |
|  3 |            2 |            1 |  {json}     |      2 |     2 |
+----+--------------+--------------+-------------+--------+-------+

So initially 'score' and 'rank' in predictions are NULL, and then after the event ends I run a script that goes through all entries for that game and scores it for the user and generates a rank.

With this structure I can do things like:

List the highest scoring users of all time (SELECT from USERS, score descending)
List the rankings for each event (SELECT rank from PREDICTIONS where gameID = X, descending)

However, what I now want to do is be able to get time-constrained rankings. So I want to be able to see which users scored highest for all events in the current month, or for the month of February etc.

I know I could do this manually by going through the Games table and getting all gameIDs that are in the given month, and then going through the Predictions table for all predictions with these IDs, and adding up the score for each user and then sending this. However that seems crazily inefficient and resource-intensive. Especially considering that this will be an API call

I'm therefore wondering how I can accommodate this in my current database - if it's possible and/or advised, or if I should use some other technology.

One idea would be have generate a new table for each month, and
update and pull from this when relevant.
Another would be to have a 'monthly score' column in the User table,
that resets each month. However this wouldn't have historical data,
which would be nice (though I guess could always be generated)

I feel like there might be some database paradigm trick that I'm missing, so it'd be great to hear a proposed solution for this.

After thinking around a bit more, a solution I think could work would be to have a new table of monthlyScores, structured like this:

+----+--------------+----------+--------+
| ID |  userID (FK) |   month  |  score |
+----+--------------+----------+--------+
|  1 |            1 |  03/2019 |     12 |
|  2 |            1 |  04/2019 |     23 |
|  3 |            2 |  03/2019 |      3 |
|  4 |            1 |  05/2019 |     22 |
|  5 |            2 |  06/2019 |     11 |
+----+--------------+----------+--------+

Where month is the current month, I could increment this value for the current months' games, thus meaning I could provide a leaderboard of the leaders for the current month. Similarly I could display scores for each user for previous months.

Posting this here as an answer as I feel like it could work. That said, as you can probably gather I'm very new to databases and so would be interested to hear about other solutions, and/or if this is advisable!


Load balancing MySQL ndbcluster I have successfully setup ndbcluster version 7.1.26. This contains 2 data nodes[NDBD], 2 mysql [MYSQLD] nodes and one management [MGMD] node. Replication works successfully. My Web application is deployed in JBoss-5.0.1 and using JNDI for connection resources which are specified in application specific ds.xml file in load balanced url forms e.g. jbdc:mysql:loadbalance:host1:port1,host2:port2/databaseName.

host1 : refers to first mysqld node and port1 refers the port it is running on. host2 : refers to second mysqld node and port2 refers the port it is running on.

When both of the [MySQLD] nodes are up and running everything works fine and cluster responds well, replicates data, and data retrieval operations also work properly.

But issues are raised when any of the [MySQLD] nodes goes down. Data gets inserted/updated/replicated but the application is unable to retrieve data from cluster and web page remains busy working which means busy retrieving data. As soon as the node which was down goes up it responds properly and application goes forward and shows up data retrieved from cluster.

At JBoss 5.0.1 startup it showed up a NullPointerException in class LoadBalancingConnectionProxy.invoke(LoadBalancingConnectionProxy.java:439). Tell me if the above Exception plays any role in the above explained issues.

If anyone had faced issues like above and if has any solution regarding the issues please let me know.

Thanks and regards.

I have resolved the issue as it was a bug in the connectorJ's version. As The project I am working on was already using both the buggy jar mysql-connector-java-5.0.8.jar and the jar version in which the issue is already resolved i.e. mysql-connector-java-5.1.13-bin.jar. After all the search when I removed the jar mysql-connector-java-5.0.8.jar my issues got resolved. All that was problematic was that the ConnectorJ/Driver was getting referred from the buggy jar. The bug id and url which refers to this issue is: http://bugs.mysql.com/bug.php?id=31053 .

Thanks for considerations.


How to enable MySQL general log? I would like to enable the MySQL General Log to view all the (slow) queries. I'm using XAMPP as server and have no idea how to enable it. I can't find a good guide on the internet.

Can someone help me please?

Thanks!

The flags you need to activate the general log and the slow on in the MySQL Documentation

log (to activate the general log)
general log (to name the log file)
slow query log (to activate the slow log)
log output (specify either FILE or TABLE for where logging info is written)

I would recommend using log-output=TABLE

It will save the logging of general log and slow log info to MySQL tables

mysql.general_log
mysql.slow_log

You should convert them to MyISAM. That way, you can index the tables by the timestamp and perform SELECT queries from them.

By default, these files use the CSV storage engine.

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.09 sec)

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.05 sec)

mysql>

Convert the general log and index it by event_time:

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);
SET GLOBAL general_log = @old_log_state;

Convert the slow log and index it by start_time:

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);
SET GLOBAL slow_query_log = @old_log_state;

Give it a Try !!!


How can I remove references to tables after removing .frm files? So I had a bunch of csv files in my /var/lib/mysql/imports directory for importing into mysql tables I had created.  To clean up the folder I deleted all the files in the directory (incorrectly thinking it only contained .csv files).  The only problem was that the folder also stored table data from my imports database.  Now that I realized my error I was just going to recreate the database I did delete and re-import from the csv file.  However I cannot create the table again because mysql must have references to the table.  How can I remove this table completely so that I can simply recreate it and re-import the information?

Edit:

mysql> DROP TABLE imports.ncaa_passing_statistics;
ERROR 1051 (42S02): Unknown table 'ncaa_passing_statistics'
mysql> SELECT * FROM imports.ncaa_passing_statistics;
ERROR 1146 (42S02): Table 'imports.ncaa_passing_statistics' doesn't exist
mysql> CREATE TABLE imports.ncaa_passing_statistics (id INT);
ERROR 1050 (42S01): Table '`imports`.`ncaa_passing_statistics`' already exists
mysql>

Shut down mysql.  What files do you see?  Restart mysql; are things better now?

One possible fix is to CREATE a new table (with a different name), copy the .frm to somewhere else, drop the table, shutdown, copy the .frm back as the problematical table name, restart.


Header and line item data source mismatch I'm working with a star schema for a data warehouse and I am running into a problem with header and line items from different data sources.

CREATE TABLE DataSourceAHeader
(
     OrderId INT NOT NULL
    ,TotalCost MONEY NOT NULL
    -- Date, etc...
);

CREATE TABLE DataSourceALine
(
     OrderId INT NOT NULL
    ,LineNumber INT NOT NULL
    -- Dates, etc...
);

CREATE TABLE DataSourceBLine
(
     OrderId INT NOT NULL
    ,Cost MONEY NOT NULL
    ,LineNumber INT NOT NULL
);

I have data sources A and B which represent the same data in different ways. Data source A contains headers and line items, but it only has the net outcome (Total Cost) in the header. Data source B contains only line items and each item has an outcome (Cost).

I could keep two fact tables (one for the header and one for the line items), but I have researched and it seems inadvisable. Is there a strategy to deal with this kind of mismatched format or should they be stored in separate data warehouses (one warehouse per data source)?

My current strategy:

CREATE TABLE Fact.Order
(
     Id BIGINT IDENTITY PRIMARY KEY
    ,OrderId INT NOT NULL
    ,Cost MONEY NOT NULL
    -- Date key, etc...
);

CREATE TABLE Fact.OrderLine
(
     Id BIGINT IDENTITY PRIMARY KEY
    ,OrderFactId BIGINT NOT NULL REFERENCES Fact.Order (Id)
    ,LineNumber INT NOT NULL
    -- related line stuff
);

DataSourceAHeader and DataSourceBLine are inserted into Order and OrderLine. DataSourceBLine is split one line per row.

Here is an example for a DataSourceAHeader and DataSourceALine

SELECT * FROM Fact.Order;
|------------------------------------|
|   Id   |   OrderId   |   Cost      |
|   1    |     1100    |   12000.00  |
|   2    |     1101    |   10000.00  |
|------------------------------------|

SELECT * FROM Fact.OrderLine;
|-------------------------------------------|
|   Id   |   OrderFactId   |   LineNumber   |
|   1    |        1        |       1        |
|   2    |        1        |       2        |
|   3    |        1        |       3        |
|   4    |        2        |       1        |
|   5    |        2        |       2        |
|   6    |        2        |       3        |
|-------------------------------------------|

Here is an example for a DataSourceBLine

SELECT * FROM Fact.Order;
|---------------------------------|
|   Id   |   OrderId   |   Cost   |
|   1    |     1000    |   12.00  |
|   2    |     1000    |   10.00  |
|---------------------------------|

SELECT * FROM Fact.OrderLine;
|-------------------------------------------|
|   Id   |   OrderFactId   |   LineNumber   |
|   1    |        1        |       1        |
|   2    |        2        |       2        |
|-------------------------------------------|

Edit:

the TotalCost in the header cannot be brought down to the line level. I chatted with an architect acquaintance and his advice was to implement two separate fact tables, one for header (summary) and one for the lines (detail), and just have NULL values for the missing line information for DataSourceA.

Edit2:

I'm trying to be generic with the OrderId since I have several more data sources that may contain similar OrderId schemes (collisions). I have implemented a Mapping table in order to translate the source identifiers into the warehouse.

Edit3:

With the intention that this question be helpful to more than just myself, I would like the answer to have the following details
(mostly to compile what everyone has already reasoned about):

In general what are the approaches to resolving related disjoint data sets taking the form of summary/detail (single fact table or summary/detail fact tables)?
What are the drawbacks to each approach?
What kind of structure could the fact table take to cope with missing (or irrelevant) data?
(two fact table approach) In what cases would it be prudent to roll down the summary versus rolling up the details?

If you want to de-normalize this into a single fact table, the fact table is going to be about line items. Therefore, the facts from DataSourceAHeader need to be split up and distributed to the relevant line items so they are not duplicated. As it is currently presented, that means dropping your total order cost and calculating this by summing the line item costs.

The DataSourceAHeader dimension keys (e.g. order date) can be taken from DataSourceAHeader and applied to the fact rows generated from DataSourceBLine. In the example there doesn't seem to be any info contained on DataSourceALine which isn't already included on either DataSourceAHeader or DataSourceBLine, but if there is this can be mapped across in a similar way.

This approach relies on a number of assumptions, the key one being that all the facts from DataSourceAHeader can accurately be distributed among its constituent line items. If this isn't true, loading two separate fact tables (one for the order and one for the line items) might well be a better approach. The same might be true if there are a lot of questions to be asked about orders, which do not consider line item specific info. This is labelled as "Bad Idea #2" in the article which you've referenced, but I have found that in  certain circumstances, it's actually a good idea.

Finally this assumes that the two data sources are in sync. If they're not, you'll be limiting yourself to loading data at the pace of the slower data source. This might be fine, but needs to be considered in the context of your needs and the difference between the two data sources.

Edit: De-normalizing into a single fact table may significantly impact performance when counting orders, as it's essentially a distinct count, which would be my main reason for considering two separate fact tables.

Edit 2 (in response to question edit):

Here, the issue is that at the most granular level (line) data is incomplete, in as much as not all rows have a cost value. However, the total cost information is available at the next level up (header).
This presents the situation where you cannot derive the higher level from the lower; let’s consider the resulting options:

Have a single fact table at the lowest granularity available (line). This is a non-starter, as we are now relying on the incomplete line data to answer questions at the higher level, which we know we could have answered.
Have a single fact table at the higher granularity (header). This means we can now answer questions at the higher level with the complete data, but can no longer answer questions at the more granular level at all. This may be considered to be acceptable, but in most cases we are throwing away potentially valuable data.
Have two related fact tables, one for the incomplete, more granular data (line) and one for the complete, less granular data (header). This is the ideal solution, as we can now answer questions at the higher level in full, and can give the best possible answer to questions at the lower level, given the incompleteness of the source data.

This question was raised because of doubts about having two related fact tables. The doubts stem from the fact that maintaining and joining two large fact tables can be resource intensive. That's true, and if your most granular information can be used to provide a full description of the situation then using a single fact table is preferable. However, in situations like this where that's not possible, two fact tables are required if you want to preserve as much information as possible.


Persisting connections over AG failover I see that: ‘When an availability group failover occurs, existing persistent connections to the availability group are terminated and the client must establish a new connection in order to continue working with the same primary database or read-only secondary database.’  - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b8857583-0ad7-419c-904b-e9e2a13511c0/sql-connections-to-the-sql-2012-always-on-availability-group?forum=sqldisasterrecovery
And the fix – setting multisubnetfailover = true
https://technet.microsoft.com/en-us/library/hh213417.aspx#CCBehaviorOnFailover
Is there a way to get this to work using a single subnet. Is there a way to detect such failures by the application and have it retry.

I want to point out something. When a failover occurs, any client using the listener to connect will have its' connection closed. This doesn't matter if MultiSubnetFailover is set or not. What MultiSubnetFailover helps with is the client driver to spin up multiple connection threads (1 for each IP) without the application knowing and connecting to the currently active IP transparently to the app.

Having said that, since on a single subnet this setting isn't required or needed (but best practice is to still put it in the connection string as you don't know if it may be needed later or not) there isn't anything needing to be done.

  Is there a way to detect such failures by the application and have it retry.

Yes! It's the same error as if you were disconnected for any other reason. Depending on the driver used, the error checking may be different. For example if you're using .Net and the ADO.Net sqldata client you can use the StateChanged event to know when you connect or become disconnected. If your delegate is fired, check the connection. If you are disconnected, attempt to connect again, etc.


How to perform asynchronous cascade slave replication of PostgreSQL database? This is a feasibility/architectural question (I am no database administrator). Let's imagine a PostgreSQL database running online (like those from Heroku for example).

One would like to maintain a slave copy of this database on a remote PC by connecting to this online database (24/48 hour intervals). Is this possible from a PostgreSQL perspective? If yes, what would be the best Postgres implementation/configuration strategy? What do I need to learn?

The slave copy should catch up on all modifications made to the online database (eventually by specifying some kind of 'until' date if necessary). This slave would be used in read mode only. Ideally, slaves to the slave must be implemented in this asynchronous way too (i.e., in a cascading fashion).

It's Postgres or PostgreSQL, not PostGre.  The method you want is streaming replication or Point In Time Recovery (PITR) which is supported in 9.0 and above.  It's covered in the docs.  Read up on it, and then if you have any questions, come on back with them


Size of TempDB Microsoft SQL Server 2005 Are there drawbacks to having a large tempdb in a SQL Server database? Assuming the disk is not full.

I am creating large temporary tables in a couple of queries. Once I am finished running queries, I drop the temporary tables. However, tempdb remains relatively large. Should I be worried about any subsequent problems with the server? Such as performance, or something I am not able to think of.

Tempdb will be recreated if the server is restarted and at that time tempdb will be reset size wise to initial size settings. In the meantime once the temporary tables are dropped space will free in the data files but size on disk will remain the same.

I wouldn't however worry about the size unless you have space issues.  Next time you run queries that use tempdb a lot you will save on growth operations and get a bit of a boost to performance since it will use the above mentioned free space in the data files


Does an IOT guarantee order in a select? We need to add a priority column to a table that gets hit about 250 times a second, approx 170 selects, 125 inserts and 60 updates. The column will be a simple number(1).

The priority does not matter for the inserts or updates i.e. not part of the primary key, which I'll enforce separately.

We basically don't want to have to do an order by over a range scan 170 times a second, as the number executed will drop massively.

Does an index organised table guarantee that priority = 1 will always come before priority = 9 when running the following query:

select *
  from my_table
 where rownum < 2

For slightly more context a typical query would be:

select *
  from my_table
 where mod(to_number(to_char(tstamp,'ss')),1) = 0
   and done is null
   and country = 'gbr'
   and rownum < 2

The pk constraint, for the IOT, would become priority, rest of the pk with a separate constraint on the pk solely for structure. done is null in approximately 99% of the table so this isn't very selective anyway.

Main index used is, I think, country, done, to_number(to_char(tstamp,'ss'), we tested about 20 combinations and this came up top by a long way.

I am completely unwilling to add any time at all to these queries, 0.01s a day added by select is 41 minutes a day. We'd much rather settle for "good enough" than perfection.

No.

The only thing that guarantees result set order is an ORDER BY clause in your query.

This is a popular question about SQL so it's worth repeating what I've written in response to similar questions about SQL Server and MySQL:

  In the SQL world, order is not an inherent property of a set of data.
  Thus, you get no guarantees from your RDBMS that your data will come
  back in a certain order -- or even in a consistent order -- unless you
  query your data with an ORDER BY clause.

In Oracle, an Index-Organized Table (IOT) will minimize the amount of work the database has to do to get you the data sorted in the index's sort order.  Though you may find that Oracle tends to return rows selected from an IOT in that same order, you are only guaranteed that order when you query the IOT with an ORDER BY clause.


sp_send_dbmail fails when mailing queries, but no error returned, query just hangs I have an issue SQL Server 2005.

sp_send_dbmail fails when mailing out with the result of a query (inline or attatchment).

No error message is given / reported, the query just keeps executing but nothing returned (have left it for 28 minutes so far) with no time out etc.

Mails go out fine when no queries are involved.

Does anyone know how to solve this?

Is it possible that you're running on a deadlock situation? Create a test table that you know for sure no one is accessing it, populate it with some sample data, and run a query in proc against that table just to test it out.


Is it normal for tempdb TPS to be higher than all other databases combined? I'm investigating some performance issues on a production DB here, and I've noticed a slightly odd thing that I'm not sure is weird or not.

If I run a quick Performance Monitor trace for the SQLServer:Transactions/sec counter for each DB on the server, I'm seeing that tempdb accounts for a huge majority of the transactions - as they're transactions against 'real' tables, shouldn't there be some correlation with other database stats too?

Database  Average
-----------------
tempdb    2,127
master      115
MyDb1        63
OtherDbs      5

TOTAL     2,700

I have a query I was given to show queries active on the tempdb, and that seems to show queries that aren't temp tables etc, but 'normal' queries on other databases.

SELECT
es.host_name,
es.login_name,
es.program_name,
st.dbid as QueryExecContextDBID,
DB_NAME(st.dbid) as QueryExecContextDBNAME,
st.objectid as ModuleObjectId,
SUBSTRING
(
    st.text,
    (er.statement_start_offset / 2) + 1,
    (
        (
            CASE
                WHEN er.statement_end_offset = -1 THEN
                    LEN(CONVERT(nvarchar(max),st.text)) * 2
                ELSE
                    er.statement_end_offset
            END
        ) - (er.statement_start_offset / 2)
    )
) as Query_Text,
tsu.session_id,
tsu.request_id,
tsu.exec_context_id,
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,
er.start_time,
er.command,
er.open_transaction_count,
er.percent_complete,
er.estimated_completion_time,
er.cpu_time,
er.total_elapsed_time,
er.reads,
er.writes,
er.logical_reads,
er.granted_query_memory
FROM sys.dm_db_task_space_usage tsu
inner join sys.dm_exec_requests er ON (tsu.session_id = er.session_id and tsu.request_id = er.request_id)
inner join sys.dm_exec_sessions es ON (tsu.session_id = es.session_id)
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
-- WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) > 0
WHERE program_name <> 'Microsoft SQL Server Management Studio - Query'
ORDER BY
    (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) +
        (tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) DESC

For poorly designed databases and queries, tempdb will often become very active. Since it is a shared resource between all databases on the instance - dont be surprised to see a high transaction count there.

As Mark also comments, snapshot isolation can have a strong effect on tempdb too.


Can I make my distribution database use In-Memory OLTP? Is it possible to have all the distribution tables use In-Memory OLTP (formerly known as Hekaton) for enhancing performance of the distribution database?

Have you tried it?

Did that increase the overall performance when the distribution database was the bottleneck for your replication?

Is it possible to have all the distribution tables use In-Memory OLTP (formerly known as Hekaton) for enhancing performance of the distribution database?

This is a good question and made me thinking. But .... the answer is NO as of SQL Server 2016 CTP 3.0.

The documentation says :

  Replication tables on the distributor cannot be configured as memory-optimized tables.


Estimating Records capacity A small MySQL 5.5.58 Ubuntu test database was setup with a constraint of 5MB.  It consists of a single table:
CREATE TABLE IF NOT EXISTS scanpoint (
         xxx      CHAR(6)              NOT NULL DEFAULT '',
         YYYYYYY  CHAR(2)              NOT NULL DEFAULT '',
         ZZZZZZ   SMALLINT UNSIGNED    NOT NULL DEFAULT 0,
         AAAAAA   CHAR(3)              NOT NULL DEFAULT '',
         BBBBBB   TINYINT UNSIGNED     NOT NULL DEFAULT 0,
         CCCCCC   TIMESTAMP
       );

I would like to estimate the records capacity of a 5MB database:
CHAR:       11 bytes =6+2+3
SMALLINT:    2 bytes
TINYINT:     1 byte
TIMESTMAP:  7 bytes?

TOTAL        21 bytes per record  => 2.38E5 records = 5MB /21 bytes/rec
QUESTION: Is there any consideration / logic that would adjust the calculation to sharpen the estimate?
UPDATE
xxx records will always have 6 characters.  In similar fashion,  the same other character arrays follow suit.  YYYYYY will always have 2 charaters and AAAAAA will always have 3 characters

TIMESTAMP is 4, not 7.  So, you are starting with 18 bytes.

Since it is probably ENGINE=InnoDB, multiply that by 2 or 3.  Sorry, I can't be more precise than that.  So, perhaps 60 bytes/record.

If it is MyISAM, add 1 for overhead.  So, 19 bytes/record.  Add 1 more for up to 8 NULLable columns (apparently 1, the timestamp).  This takes a byte of 8 bits.  So, 20 bytes/record.  If you can make all columns NOT NULL, this byte can go away.

What is the average length of xxx?  If it is less than 6-1, use VARCHAR(6) instead of CHAR(6).  A short VARCHAR takes 1 byte for length plus the actual number of bytes needed for the string.

Consider normalizing xxx to save more space.

Are all the string columns ascii or latin1?  If not, then the above numbers may be wrong.  utf8 may take 3 bytes per character.  utf8mb4, 4 bytes.

If there are not many different values for the CHARs, consider a 1-byte ENUM.

What about indexes?  They take space.

InnoDB without PRIMARY KEY -- a 6-byte hidden PK.  So, it is almost always advisable to have an explicit PK.  If some column (or combo of columns) is always distinct, use it.

Any secondary index, plus MyISAM's PK -- another BTree, with another calculation.  Treat each index (but not  InnoDB's PK) like another table, but add 40% overhead due to BTree.