Require uniqueness with group by I'm not sure how to couch this question. I have a table that has multiple columns including an id column, a foreign key column 'JobId', and a 'GxpId' column.

I have a requirement that a 'JobId' can have at most one 'GxpId' but the table can have multiple rows with the same 'JobId' and 'GxpId'.

There is a separate process that is importing data into the table and the application using the table has failed because the import process has created two rows with the same 'JobId' but different 'GxpId'.

I would like to add a constraint to the table to ensure that any row for a specific 'JobId' must have the same 'GxpId'. Hope that makes sense.

In SQL Server, you need a two step process to do this. The check constraint can't have complex logic in it, but it can reference a function which can.

This constraint is a pretty big red flag for how the schema is designed. Looking at your other questions, it looks like you're aware of this and have limited options to work with.

CREATE TABLE TestTable (TestTableId INT IDENTITY(1,1), Jobid INT NOT NULL, GxpId INT NOT NULL)

GO
CREATE FUNCTION dbo.TestTable_GxpCountForJobId_fn(@JobId AS INT)
RETURNS INT
BEGIN
    DECLARE @GxpCount INT
    SELECT @GxpCount = COUNT(*)
    FROM (
        SELECT DISTINCT
            GxpId
        FROM TestTable
        WHERE JobId = @JobId
    ) AS DistinctGpx

    RETURN @GxpCount
END
GO

ALTER TABLE TestTable
ADD CONSTRAINT TestTable_SingleRelation_ck
CHECK (dbo.TestTable_GxpCountForJobId_fn(JobId) <= 1)
GO

INSERT INTO TestTable VALUES (1,1)
INSERT INTO TestTable VALUES (2,3)
INSERT INTO TestTable VALUES (1,1)

-- This will fail
INSERT INTO TestTable VALUES (1,2)


Strange issue regarding my.cnf I'm getting a weird message from phpmyadmin saying:

#2006 - MySQL server has gone away

What I found online is that I should increase the size of max_allowed_packet witch should be in the my.conf file witch is located at /etc/my.cnf. Untill now everything is fine, but the problem is that this file does not contain what I need.

Here is the content of the .cnf file on my server:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Where should I look in order to increase the size of the packet? I'm new in the linux world and I'm lost.

You need to change the my.cnf file to include the single line under [mysqld] in your file

max_allowed_packet=16M

now restart the MySQL service and you are done.

You can see its current value in mysql like this:

SHOW VARIABLES LIKE 'max_allowed_packet'

You can try changing it like this, but it's unlikely this will work on shared hosting:

SET GLOBAL max_allowed_packet=16777216;

Also please read this: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html


REINDEX or REBUILD the indexes of a table after truncating it and then repopulating it? Is it necessary to REINDEX or REBUILD the indexes of a table after truncating it and then repopulating it?

I have a table with approximately 3.5 million rows in Oracle. Every week new data is recharged, truncating the table and then loading the data.

There are 2 or 3 indexes in the table. Should I delete and re-create the indexes or simply rebuild the ones I already have? , some indexed columns are encrypted with TDE.

Thank you

If your data is not sorted (by the indexed columns), the maintenance of indexes during the load of large amount of data may have some serious overhead because of the leaf node splits (not 90-10 splits), so:

truncate the table
alter index ... unusable on all indexes
load data
alter index ... rebuild on all indexes

This will work if SKIP_UNUSABLE_INDEXES is set to true, which is the default value, but keep this in mind:

  If an index is used to enforce a UNIQUE constraint on a table, then
  allowing insert and update operations on the table might violate the
  constraint. Therefore, this setting does not disable error reporting
  for unusable indexes that are unique.

Alternatively, yes, you can drop the indexes before the load, and recreate them after.


improve long running query I have this query

INSERT INTO interactions(
    user_id, external_object_id, origin, interaction_type,
    interaction_sub_type, published_at, origin_id, created_at, updated_at)

SELECT find_user(id), find_external_object(external_object_id), 'facebook' as origin,
    interaction_type, interaction_sub_type, published_at, origin_id,
    current_date, current_date
from legacy_table

The legacy_table have around 9 million records, and it is running for around 20 hours now and find_user and find_external_object are functions that look for the return the id of an user and an external object (of course) based on id from legacy table, like this

SELECT legacy_user.uid into uid
    from legacy_user
    where legacy_user.id = $1;

SELECT users.id into user_id
    from users
    where users.uid = uid;

RETURN user_id;

What can I do to improve the query?

Try creating a couple of temporary tables, indexing them, analyzing them, and then join to them..

CREATE TEMPORARY TABLE luid_lookup AS
  SELECT id, find_user(id) AS fuid FROM legacy_table;

CREATE TEMPORARY TABLE eoid_lookup AS
  SELECT external_object_id, find_external_object(external_object_id) AS feid FROM legacy_table;

CREATE INDEX luid_lookup_tmp_ix1 ON luid_lookup (id, fuid);
CREATE INDEX eoid_lookup_tmp_ix1 ON eoid_lookup (external_object_id, feid);

ANALYZE luid_lookup;
ANALYZE eoid_lookup;

And then

INSERT INTO interactions(
    user_id, external_object_id, origin, interaction_type,
    interaction_sub_type, published_at, origin_id, created_at, updated_at)

SELECT b.fuid, c.feid, 'facebook' as origin,
    a.interaction_type, a.interaction_sub_type, a.published_at, a.origin_id,
    a.current_date, a.current_date
from legacy_table a
join luid_lookup b on a.id = b.id
join eoid_lookup c on a.external_object_id = c.external_object_id

If the creation of the temporary tables takes a long time, then your find_user() and your find_external_object() methods need to be tuned (as opposed to the actual insert query).

One additional note .. if the id or external_object_id are NOT pkeys/unique of their tables, you will want to make sure that you're only getting unique values..  (If you don't, then you'll get duplicate rows when you join)  In that case, make sure you do a subselect and get the distinct result (example below).

CREATE TEMPORARY TABLE luid_lookup AS
  SELECT id, find_user(id) AS fuid FROM (SELECT DISTINCT id FROM legacy_table) l_tab;


Not able to update one of the key value by using JSON_MODIFY function in SQL Server I have the following json record and I'm trying to update the 'counter2' value to 2000 using JSON_MODIFY function. The query doesn't throw any exception but at the same time the value is not getting updated.

Table Data:

Json data:

[ {"counter1": 9,
"counter2": 1000,
"counter3": 109 }]

SQL:

UPDATE test01
set json_data = JSON_MODIFY(json_data,'$.counter2',2000)
where test01_id =10

Am I missing something here?

Your 'counter' properties are in an object within an array, so your JSON path in JSON_MODIFY isn't correct. You need to reference the index of the item in the array to correctly update the value:

UPDATE test01
set json_data = JSON_MODIFY(json_data,'$[0].counter2',2000)
where test01_id =10

If you had additional objects in the array with different counter 1/2/3 values, then you would need to reference them with their correct index, i.e. [1], [2] and so on. You can see a working example of this here.


PostgreSQL GIN pg_trgm default operator class I am using GIN indexes with the pg_trgm module for indexing varchar fields.  So, to define an index I have to write something like this:

CREATE INDEX gin_field_idx ON table_name USING gin (field gin_trgm_ops);

If I exclude the operator class (git_trgm_ops) and write:

CREATE INDEX gin_field_idx ON table_name USING gin (field);

PostgreSQL will raise an error:

  ERROR:  data type character varying has no default operator class for
  access method "gin"
  HINT:  You must specify an operator class for the
  index or define a default operator class for the data type.

How do I define a default operator method from the module?

This does not help. It describes how to define a new operator class from extension operators and functions. But I have to define the default operator class from a module.

Any help will be appreciated :-).

you can update the pg_opclass table and set the default.

update pg_opclass set opcdefault = true where opcname='gin_trgm_ops'

pg_trgm doesn't do this by default as you might have/want a different GIN default.

select * from pg_opclass where opcname = 'gin_trgm_ops';

than you can create an index:

create index ON table USING gin (field); -- it will use the new default


filter a table based on a second table matching all criteria Having trouble writing a query to return rows from one table matching rows in a second table.  Simple joins I can pull in combinations, but how do I get only those rows that match ALL of the criteria?

For example:

Attribute Table

+---------+------------+------------+
|  itemno | attrname   | attrvalue  |
+---------+------------+------------+
+    1    +  Color     +  Black     +
+    1    +  Connector +  3.5mm     +
+    1    +  Output    +  5W        +
+    2    +  Color     +  Black     +
+    2    +  Connector +  USB       +
+    2    +  Output    +  10W       +
+    3    +  Color     +  Silver    +
+    3    +  Connector +  3.5mm     +
+    3    +  Output    +  5W        +
+    4    +  Color     +  White     +
+    4    +  Connector +  USB       +
+    4    +  Output    +  2W        +
+---------+------------+------------+

Filter Table 1

+------------+------------+
| attrname   | attrvalue  |
+------------+------------+
+  Color     +  Black     +
+  Color     +  Silver    +
+  Output    +  5W        +
+------------+------------+

would return only items 1 and 3, but NOT item 2 because it didn't match both criteria in the filter.

thanks in advance,

This may not be the best performing way to do this but it should work for you.

Set up code: (Anyone else wanting to answer feel free to copy this part)

CREATE TABLE #Attrib (itemno int, attrname varchar(50), attrvalue varchar(50));
INSERT INTO #Attrib VALUES
    (1, 'Color', 'Black'),
    (1, 'Connector', '3.5mm'),
    (1, 'Output', '5W'),

    (2, 'Color', 'Black'),
    (2, 'Connector', 'USB'),
    (2, 'Output', '10W'),

    (3, 'Color', 'Silver'),
    (3, 'Connector', '3.5mm'),
    (3, 'Output', '5W'),

    (4, 'Color', 'White'),
    (4, 'Connector', 'USB'),
    (4, 'Output', '2W');

CREATE TABLE #Filter1 (attrname varchar(50), attrvalue varchar(50));

INSERT INTO #Filter1 VALUES
    ('Color','Black'),
    ('Color','Silver'),
    ('Output','5W');
GO

Query:

SELECT DISTINCT itemno
FROM #Attrib A
JOIN #Filter1 F
    ON A.attrname = F.attrname
    AND A.attrvalue = F.attrvalue
GROUP BY itemno
HAVING COUNT(DISTINCT A.attrname) = (SELECT COUNT(DISTINCT attrname) FROM #Filter1);


Split a column at every whitespace Let's say I got word transcriptions (space separated) in one column. Furthermore I got a set of legal phones.

How can I split every transcription @ a whitespace to check whether all used  phones are legal ones?

Main question is: how to get a distinct on a column split @ a whitespace?

Let's say the data looks like this:

ID | transcriptions
 1 | th i s
 2 | i s
 3 | s o m e
 4 | t e st
 5 | d a t a

Now the output should look like the following:

  th
  i
  s
  o
  m
  e
  t
  st
  d
  a

This should work (sqlfiddle):

SELECT distinct SUBSTRING_INDEX( SUBSTRING_INDEX( transcriptions, ' ', l10.n * 10 + l1.n + 1), ' ', -1) AS transcription
FROM data
CROSS JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UN ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0
) l1
CROSS JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0
) l10
WHERE LENGTH(transcriptions) - LENGTH( REPLACE( transcriptions, ' ', '')) >= l10.n * 10 + l1.n
;

Please note that it may not be extremely efficient. You may consider changing your data model and avoid spliting string using SQL.

Each CROSS JOIN (l1 and l10) create a number table from 1 to 100 (10*10). If strings are longer that 100, extra CROSS JOIN must be added (10*10*10 => 1.000, 10*10*10*10 => 10.000, ...).
The number table is used on characters at each position in the number table and search for spaces.

Sample Data:

CREATE TABLE data(id int, transcriptions varchar(100));
INSERT INTO data(id, transcriptions) VALUES
    (1, 'th i s')
    , (2, 'i s')
    , (3, 's o m e')
    , (4, 't e st')
    , (5, 'd a t a');

Output (no specific order):

transcription
i
s
o
e
a
m
st
t
th
d


Deleting rows from child table without deleting from parent I have 2 tables, connected with inheritance.
I want to delete some rows from the child table. The deletion part works but it deletes rows from the parent as well:
CREATE OR REPLACE FUNCTION earthquakes_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO earthquakes VALUES (NEW.*);
    DELETE FROM ONLY earthquakes WHERE datetime < (now() - '2 days'::interval);
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER earthquakes_trigger
        BEFORE INSERT ON earthquakes_ovr
        FOR EACH ROW EXECUTE PROCEDURE earthquakes_trigger();

Parent table : earthquakes_ovr
Child table: earthquakes
I also tried to break up the trigger function, and have the deletion part as trigger function on the child, but that didn't work either.

As you can see that is not the case, when  the tables are not linked in anyway

CREATE tABLE earthquakes_ovr ("datetime" timestamp)

INSERT INTO earthquakes_ovr VALUES (NOW() - INTERVAL '6 DAYS')

INSERT INTO earthquakes_ovr VALUES (now() - INTERVAL'4 days')

CREATE UNLOGGED TABLE  earthquakes AS SELECT * FROM earthquakes_ovr

    CREATE OR REPLACE FUNCTION earthquakes_trigger()
    RETURNS TRIGGER AS $$
    BEGIN
        INSERT INTO earthquakes VALUES (NEW.*);
        DELETE FROM ONLY earthquakes WHERE datetime < (now() - '2 days'::interval);
        RETURN NEW;
    END;
    $$
    LANGUAGE plpgsql;

    CREATE TRIGGER earthquakes_trigger
            BEFORE INSERT ON earthquakes_ovr
            FOR EACH ROW EXECUTE PROCEDURE earthquakes_trigger();

INSERT INTO earthquakes_ovr VALUES (now())

SELECT * FROM earthquakes_ovr

| datetime                   |
| :------------------------- |
| 2021-04-18 00:11:15.191671 |
| 2021-04-20 00:11:15.203608 |
| 2021-04-24 00:11:15.267616 |

SELECT * FROM earthquakes

| datetime                   |
| :------------------------- |
| 2021-04-24 00:11:15.267616 |

db<>fiddle here


How to create aggregate function, like sum, with an implicit DISTINCT? We have an ERP system which allows aggregates to be used (eg SUM(foo)) but not with DISTINCT (eg SUM(DISTINCT foo).

Is it possible to create an aggregate function (SUM_DISTINCT), that returns the same result as as SUM(DISTINCT foo), so SUM_DISTINCT(foo) = SUM(DISTINCT foo)?

Is it possible to create an aggregate function (SUM_DISTINCT), that returns the same result as as SUM(DISTINCT foo), so SUM_DISTINCT(foo) = SUM(DISTINCT foo)?

Yes, it is possible — you need a User-defined Aggregate, such as this:

create or replace function f_sum_distinct (numeric[], numeric) returns numeric[]
    language sql as $$
select $1||$2;
$$;

create or replace function f_sum_distinct_final (numeric[]) returns numeric
    language sql as $$
select sum(v) from (select distinct unnest($1) v) z;
$$;

create aggregate sum_distinct(numeric)
( sfunc     = f_sum_distinct
 ,stype     = numeric[]
 ,finalfunc = f_sum_distinct_final
);

with w(v) as (select 2 union all select 2 union all select 3)
select sum(v) "Plain SUM", sum(distinct v) "SUM(DISTINCT)", sum_distinct(v) "SUM_DISTINCT" from w;

/*
|Plain SUM|SUM(DISTINCT)|SUM_DISTINCT|
|--------:|------------:|-----------:|
|        7|            5|           5|
*/

dbfiddle here

Note though (thanks @Erwin), that performance is going to be very substantially worse than the built-in aggregates. If this matters you will have to consider writing the helper functions in C, which is much more of an undertaking.


Oracle 12c create user I have some problem with creating user in Enterprise Oracle Database System. A could'n create user using command like

create user name identified by password;

because of exception:

  ORA-65096: invalid common user or role name

So I found solution on Google:

alter session set container=PDBORCL;

and after that - is work. User was created.

But I can't log in to this account. What is wrong? I add all privileges to this account (CREATE SESSION of course too). I got error:

  ORA-01017: invalid username/password; login denied.

What the hell is going on?

If you want to create a user that can admin any of the pluggable databases, prefix the username with C##.

For example:

create user c##name identified by password;


copy only tables with data from one database to another database I have two database , dbOne(version - 10.50.1600 - locate in office server )  and dbTwo(version - 10.0.1600 - locate in my local server) .
I want to copy dbOne's tables with data to dbTwo .
Is there any way or script to do it ? I don't want to upgrade my local server-version !

Here are several options you can try:

Export/import wizard: If you only want to copy tables and don’t want to bother with relationships and such this is probably the quickest way to do this.

Generate scripts wizard: Generate scripts for all tables you want to migrate. Right click on the database -> Tasks -> Generate scripts -> select only tables you want to migrate.
Also make sure that you manually review execution order of scripts because SSMS doesn’t order these correctly be default (doesn’t take dependencies into account)

Third party tools: There are many database comparison tools out there you can use for this such as Red-Gate’s SQL Compare, ApexSQL Diff and many others…. All of these are premium tools but you can get the job done in trial mode.


InnoDB multicolumn fulltext search: SELECT MATCH requires single column index? I have table with two fields and run a fulltext query with weighted order:

SELECT
  MATCH (`my_table`.`text_1`) AGAINST ('+lorem' IN BOOLEAN MODE) AS `reltext_1`,
  MATCH (`my_table`.`text_2`) AGAINST ('+lorem' IN BOOLEAN MODE) AS `reltext_2`,
  my_table.id
FROM
  my_table my_table
WHERE
  (
    MATCH (
      `my_table`.`text_1`,
      `my_table`.`text_2`
    ) AGAINST ('+lorem' IN BOOLEAN MODE)
  )
ORDER BY
  (2 * `reltext_1` + `reltext_2`) DESC

There's one multicolumn index containing both fields text_1 and text_2. This was a MyISAM table in the past and everything run fine.

Now when I switch the table over to InnoDB I see a Can't find FULLTEXT index matching the column list error. What I figured out is that everything works again if I add two additional single column indices for text_1 and text_2. Is this really necessary? Do I do something wrong?

Edit: Tested on MySQL 5.6.38

Alas that is one of a few differences between the two separate implementations of FULLTEXT.

Alas. the workaround is to add two more indexes:

FULLTEXT(text_1)
FULLTEXT(text_2)

But... Don't count on relevance numbers coming back from different indexes as being comparable.


Handling currencies in a database How should money currency be handled in the database to be consistent? Should I choose the specific currency to use across my database or is it better to mix different values of different currencies?

Let's say I have Product table, this product has a price, but what currency should the price be in? Should it always be the same or should I also have Currency  column with currency code or CurrencyID pointing to specific currency?  Should I set currency per product or globally per database?

What is your experience with that?

There is no answer to that - it depends. If you are a smaller outfit that with locality (like a dozen shops in the USA) then all you really care about is the price in USD. If you are an international retailer with websites in many countries with local pricing, you need to keep likely even multiple prices per product in different currencies.

  Let's say I have Product table, this product has a price, but what currency
  should the price be in?

It should not be there. it assumes not only one currency (which wold be the base currency the system runs in) but also no rebate system at all. Pricing, except for really simplistic systems, is a separate table.

Suggested reading: Data Model REssouce Book, Volume 1 - whole chapter on storing prices.

If yo ustore it like this, it is either base currency (stored somewhere central) or yes, you add a currency id field to reference the currency. I suggest using the ISO currency code for that, and adding negative numbers if needed when no official code is assigned.


Which are the minimum privileges required to get a backup of a MySQL database schema? Which are the minimum privileges required to get a backup of a MySQL database schema? I am interested only on the schema, not on the data.

Are SELECT privileges required?

According to the MySQL Documentation

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

Thus, to run

mysqldump --no-data --routines --triggers ...

the privileges required are

SELECT
SHOW VIEW (If any database has Views)
TRIGGER (If any table has one or more triggers)
LOCK TABLES (If you use an explicit --lock-tables)

You will also need the SELECT privilege on mysql.proc in order to dump Stored Procedures.


Foreign Key as Primary Key when "converting" conceptual entity-relationship elements to logical relations Hi guys I'm trying to implement database models using MySQL. One thing struck me when I was trying to "convert" an entity-relationship diagram (ERD) to relations (or SQL tables).

Note: Please keep in mind that a) "relationship" from the entity-relationship view and b) "relation" from the relational model are different constructs.

As far as I know, the following points are the standard when converting entity sets and relationships (as per the entity-relationship view) to relations (as per the relational model).

Entity Sets: Simply use all the attributes as columns (key attributes become the primary key).
Relationships (Many-to-Many): Use key attributes (from both entity sets) as relation attributes (i.e., table columns). So this means that when I'm trying to create a relation (table) for any relationship, I have to bring the key(s) from existing relations (tables) as a foreign key.

But when I looked up, almost everyone told me I shouldn't use a referenced foreign key as a primary key for that table (because foreign keys should allow non-unique values). This doesn't make sense because all kinds of relationship have to reference the keys from entity sets (relations).

I'm totally confused, somebody please help me!

Apparently, the problem at hand is about "resolving" many-to-many relationships that may occur in ERDs.  A popular approach is: to add an intersection entity - aka as junction and under various other names - that has 1-to-many relationships to the original entities.  Example - entities: NUMBER and LETTER.  Suppose the (integer) numbers 1-26 can be combined with the letters A-Z.  And: we only want to allow unique combinations of numbers and letters.  Initially, we have a M:M relationship between the 2 entities.  In order to resolve this, we add an INTERSECTION (the original M:M relationship is still there, but will not be needed at the later stages)

When using Oracle's "datamodeler", we can forward-engineer the ERD to a relational model, which shows us some implementation details:  1 the 2 columns of the INTERSECTION have foreign key constraints (referencing the tables NUMBER and LETTER, respectively.  This will allow multiple occurrences of (one and the same) letter/number.  2 the 2 columns "NUMBER_number_" and "LETTER_letter_" have a primary key constraint ie (among other things): only unique number/letter combinations are allowed.  (Notice that this particular modelling software even generates an intersection table "Relational_1" automatically ...)

The resulting DDL code, and some code for testing, could be something like (table/column/constraint names differ from the models, MySQL 5.7):

-- test tables and data
-- parent tables
create table t_numbers ( number_ integer primary key ) ;
create table t_letters ( letter_ varchar(1) primary key );

drop procedure if exists populate_n_l;
delimiter //

create procedure populate_n_l()
begin
  declare v1 int default 1 ;

  while v1 <= 26 do
    insert into t_numbers ( number_ ) values ( v1 ) ;
    insert into t_letters ( letter_ ) values ( char( v1 + 64 ) ) ;
    set v1 := v1 + 1 ;
  end while;

end//

delimiter ;

call populate_n_l() ;

Finally:

-- intersection
create table t_intersect (
  inumber integer
, iletter varchar(1)
-- constraints
, constraint fkey_number
     foreign key( inumber ) references t_numbers( number_ )
, constraint fkey_letter
     foreign key( iletter ) references t_letters( letter_ )
, constraint pkey_combination
     primary key ( inumber, iletter )
) Engine=InnoDB;

Testing:

-- okay (valid combination)
  insert into t_intersect ( inumber, iletter )
    values ( 1, 'A' ) ;

-- does NOT work ( as expected! - number/letter combinations must be unique ... )
  insert into t_intersect ( inumber, iletter )
    values ( 1, 'A' ) ;
-- ERROR 1062 (23000): Duplicate entry '1-A' for key 'PRIMARY'

  insert into t_intersect ( inumber, iletter )
    values ( 1, '1' ) ;
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ...

  insert into t_intersect ( inumber, iletter )
    values ( 0, 'D' ) ;
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ...

-- fine
  insert into t_intersect ( inumber, iletter )
    values ( 1, 'B' ) ;
  insert into t_intersect ( inumber, iletter )
    values ( 1, 'C' ) ;

mysql> select * from t_intersect;
+---------+---------+
| inumber | iletter |
+---------+---------+
|       1 | A       |
|       1 | B       |
|       1 | C       |
+---------+---------+

Dbfiddle here.


SSIS with MultiSubnet Availability Groups I  am planning for a "multi-subnet" AG group . 2 at the primary data center in DC and one at San Jose. We are planning to test a vendor application which requires SSIS. I am planning for the SSIS to be in a separate instance and not on the AG, and if a failover was performed to SanJose, how do I configure SSIS to make sure it's available?

The application will be creating 3 databases, one of which I am planning to add to the AG. The renaming two will be on the SSIS server itself. Due to restrictions, I cannot install SSIS on the SQL Servers itself. This is one of the reasons I had to separate the SSIS into a standalone instance

If you are setting up an availability group listener across multiple subnets and plan to use static IP addresses, you need to get the static IP address of every subnet that hosts an availability replica for the availability group for which you are creating the listener. Usually, you will need to ask your network administrators for the static IP addresses.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server#Prerequisites

So your SSIS packages will point to the Listener name which will have multiple static IP addresses each on a different subnet.

https://www.mssqltips.com/sqlservertip/4597/configure-sql-server-alwayson-availability-group-on-a-multisubnet-cluster/

Regarding failover of SSIS, you can have the SSIS Packages deployed on the Primary as well as the secondary server either on the AG Instance itself or on a different standalone instances of the servers. As part of deployment process, you can make sure that every package you deploy on primary SSIS instance is also deployed on the secondary. Either on Primary or Secondary SSIS Instance, the packages will always point to the Listener name of the AG and hence failover of AG instance should not impact the SSIS package executions.

If the server on which the primary SSIS instance resides goes down the you can enable the SSIS jobs on the Secondary standalone SSIS Instance.


InnoDB has a transaction running, but "SHOW PROCESSLIST;" shows nothing I have a replicated database setup, mostly Innodb databases/tables.  The query that I believe caused the issue ran in about 20 minutes on the master database. This is where it gets weird. Show processlist has nothing listed, except for my connection and the replicant connection. Innodb has a transaction running according to show innodb status.  The transaction has been running for 7+ hours.

Replication seems to have stopped, despite the slave_io and slave_sql both running. The Exec_Master_Log_Pos hasn't budged and cpu usage is at 100% (1 core).

Is there any way to see what query it's actually running as well as maybe a way to figure out how long the transaction may take? I'm kind of lost here and about to blow away the slave and resetup replication if I can't get anywhere soon.

Is it possible that your InnoDB settings are way too small ?

The default innodb_buffer_pool_size is 8M. Could you increase it ?

The default innodb_log_file_size is 5M. Could you increase it ?

You should resize them as follows:

Goto this link to compute the proper size for the innodb_buffer_pool_size based on the amount of InnoDB you have or 75% of installed RAM, whichever is smaller.

Once you have this number, set innodb_log_file_size to 25% of the computed innodb_buffer_pool_size. In order to resize them, you will have to shutdown mysql, delete the innodb log files, and start mysql.

Give it a Try !!!

CAVEAT

IMHO The reason for this conjecture is that lots of MVCC activity + default InnoDB configs spells a lot of internal InnoDB maintenance we may never know is going on.

Here are other possibilities :

Could the .ibd file be corrupt ?
Is the .ibd on a degraded array ?
Is there an insufficient number for the innodb_open_files option ?
Is tmpdir folder too full (tmp tables log jamming in the tmp folder) ???
Are the my.cnf files of the two instances identical (less server_id) ???


Expressing the largest floating point value in PostgreSQL? How to express the largest floating point value (for FLOAT8) in PostgreSQL?
I need it to replace 'Infinity'::FLOAT8 during conversion to jsonb field values, because json does not allow NaN and infinity values.
C++ has numeric_limits<double>::max(). Is there something equivalent in PostgreSQL?
(I'm using the latest PostgreSQL 11/12).

There is no constant for that in PostgreSQL, but if your architecture uses IEEE floating precision values, the maximum should be 1.7976931348623158e308.

Maybe it would be a good idea to use a value that's larger than any reasonable value, but obviously artificial, like 1e300.


Generate view queries using a loop I have this PostgreSQL query where I have 2x $i in a bash-like notation to make it comprehensible that I want to print out a number i ranging from 0 to 20 at these positions using a for loop. Briefly, the idea is to generate some SQL code as a temp table that I can dump to a file. Which file is then executed to actually run these queries.
SELECT CONCAT(
    'CREATE MATERIALIZED VIEW IF NOT EXISTS "',
    table_name, '_factor', $i,
    '" AS SELECT id, POWER(2,', $i,') AS factor ',
    'FROM "', table_name, '" WHERE factor IS NOT NULL;'
)
FROM information_schema.tables
WHERE table_name LIKE '%^prefix%'
  AND table_name NOT LIKE '%suffix$'
  AND table_schema not in ('information_schema', 'pg_catalog')
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

Notice also that this SELECT statement already loops over N tables.
From what I've read so far, this would be the kind of structure in which to embed my code:
DO $$
BEGIN
    FOR i IN 0..20 LOOP
        -- insert the previous query here
    RAISE NOTICE 'factor number is: %', i;
    END LOOP;
END; $$

But when I do so and actually execute it, I got this error message:
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function inline_code_block line 4 at SQL statement
SQL state: 42601

And if I replace SELECT by PERFORM, I end up with a single word "DO" dumped in my file.
How could I generate all my queries using a for loop?
Resulting queries should look like:
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefix1_table_0" AS SELECT id, 1 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefix1_table_1" AS SELECT id, 2 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;
...
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefix1_table_20" AS SELECT id, 1048576 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;
...
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefixN_table_0" AS SELECT id, 1 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefixN_table_1" AS SELECT id, 2 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;
...
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefixN_table_20" AS SELECT id, 1048576 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;

Precisions: I store this SQL statement in a /sql_queries/generate_views_queries.sql file and execute it to dump the resulting queries in an other file /sql_queries/do_create_views.sql:
psql -d "host=${HOST} port=${PORT} dbname=${POSTGRES_DB} user=${POSTGRES_USER} password=${POSTGRES_PASSWORD}" \
  -f "/sql_queries/generate_views_queries.sql" \
  -t --output="/sql_queries/do_create_views.sql"

PG version is 13.1 from the official PostgreSQL Docker image.

If I understand you correctly below anonymous block should do it.
DO $$
declare

tabs cursor for
SELECT table_name
FROM information_schema.tables
WHERE table_name LIKE '%^prefix%'
  AND table_name NOT LIKE '%suffix$'
  AND table_schema not in ('information_schema', 'pg_catalog')
  AND table_type = 'BASE TABLE'
ORDER BY table_name;
rec record;

query_statement text;
BEGIN

open tabs;
loop
fetch tabs into rec;

exit when not found;

    FOR i IN 0..20 LOOP

    query_statement:= 'CREATE MATERIALIZED VIEW IF NOT EXISTS "'||rec.table_name||'_factor'||i||'" AS SELECT id, POWER(2,'||i||') AS factor FROM "'||rec.table_name||'" WHERE factor IS NOT NULL;';
    RAISE NOTICE '%' , query_statement;
    RAISE NOTICE 'factor number is: %', i;
    END LOOP;
end loop;
END; $$


How AlwaysOn Availability Group secondary replica catches up with primary after secondary server long downtime Can someone point me to the MS article or blog post that explains in details how AlwaysOn Availability Group secondary replica catches up with primary after secondary server long downtime?

I did below tests with AAG (async, manual failover, read-only configuration).
A) Killed secondary instance during continuos insert into primary and started secondary instance few minutes after. AAG dashboard turned into a green almost immediately after secondary restart and started to catch up with primary until number of rows became the same in both instances. No transaction log backup was done.
B) Same as A) but few transaction logs were done from primary during the test.

Questions are:

1) What is the size of log cache/messaging framework etc that are used to keep tran log blocks (which are sent to secondary replica)

2) Can above structure (log cache/send queue etc - whatever is used as transport for AAG replication) sizes be configured/increased (similar to encrease of tran log backup retention period in log shipping, for example)?

3) As I backed up (truncated) tran log in test B) and secondary replica was syncronised with primary automatically what was used to find row difference between primary and secondary (apparently not tran log as it was truncated) and then bring then in sync?

4) How does this automatic catch up process work and what are its limitations?

There is no 'cache'. Is just log. The primary log is the messaging framework. Primary writes into the log file, and secondaries receive a copy. When connected the secondaries receive the log immediately and the primary is free to reuse the log. When disconnected the primary is forbidden from truncating the log and must append to it. When reconnected the secondary receives the log it missed and primary is free to reuse again the log, no longer has to append (grow).

You taking log backups on the primary does not change anything. When the log is required to be kept for a disconnected secondary, backup does not truncate the log.

There is no 'message' to sent to the secondaries to keep it updated. The secondary is simply running recovery on the database, that's all it does, applying the log received from the primary. The Write Ahead Protocol guarantees that whatever the secondary is 'recovering' is going to be identical to whatever the primary has in the database.

There are some optimization (eg. when connected the primary usually sends the log from memory, not from disk) and there are a bunch of additional control messages sent outside of the log stream, but these are details that distract from the core issue: the secondary is simply running recovery applying the log received from the primary, and the primary has to keep said log until it was acknowledged by secondary.

Your confusion arise from the believe that backup has truncated the log on the primary, therefore there must be another mechanism to synchronize. This is incorrect, as backup did no in fact truncate the log.


How to mount a query to join by searching by last date I have the following tables:
TB_PRODUCTS

ID     COD_PROD     Name
===============================================
1       100    Video monitor
2       101    Keyboard
3       102    Mouse

TB_COSTS

COD_PROD    DATE_ATT    COST
===============================================
100        2016-03-01    1.00
100        2016-02-29    2.00
101        2016-03-01    1.00
101        2016-02-29    2.00
102        2016-03-01    1.00
102        2016-02-29    2.00

This is the query that I have:

SELECT
TB_PRODUCTS.ID,
TB_PRODUCTS.COD_PROD,
TB_PRODUCTS.Name,
TB_COSTS.DATE_ATT,
TB_COSTS.COST
FROM
TB_PRODUCTS
INNER JOIN TB_COSTS ON TB_COSTS.COD_PROD = TB_PRODUCTS.COD_PROD
WHERE
TB_COSTS.COST > 0

But the query does not return me the cost of the last date, and what I need is to do a join with the cost of the last date.
How could I filter the Join only taking the cost of the most recent date ?

One way to do it is to use CTE (but I haven't tested the syntax myself). For example:

WITH ANSWER AS
(SELECT TB_COSTS.COD_PROD
    , TB_COSTS.DATE_ATT
    , TB_COSTS.COST
    , ROW_NUMBER() OVER (PARTITION BY TB_COSTS.COD_PROD ORDER BY DATE_ATT DESC) AS ORDER_VALUE
FROM TB_COSTS)

SELECT TB_PRODUCTS.ID
, TB_PRODUCTS.COD_PROD
, TB_PRODUCTS.Name
, ANSWER.DATE_ATT
, ANSWER.COSTS
FROM TB_PRODUCTS
INNER JOIN ANSWER on TB_PRODUCTS.COD_PROD = ANSWER.COD_PROD
AND ANSWER.ORDER_VALUE = 1
WHERE TB_COSTS.COST > 0


SQL Server Express Edition feature and upgrade concerns I want to install and learn T-SQL with SQL Server. The lightest edition that I can find is the Express Edition 2017.

Will the express edition support procedures, triggers, dynamic sql, PIVOT, sequences, dynamic management views and functions?
Will it be upgradable to adding ssis, ssrs and ssas in future (I read documentation on Microsoft community blogs, but couldn't find an answer specific to this edition), maybe I will need them in future.

You should simply download and use SQL Server 2017 developer edition. Dev edition is same as enterprise edition so regarding feature you would not have any limitation and free to use for testing and learning. Do not use it in production


Does rebuilding a clustered index on an IDENTITY column do anything useful? We have a COTS product that has created a very large transaction log table. It has a single clustered index which supports an IDENTITY column declared as a key. Last night the index got rebuilt and the server ran out of disk space so I've been up since 12:30 am.

Now I'm wondering does SQL Server even do anything when it rebuilds this index? There are no leaf pages or page splits since all the inserts go into the last page. And we never delete rows (not yet anyway).

Obviously the server tried to do something, but if it had succeeded would the table be any different? I am thinking this is just a giant no-op and a waste of time. Obviously this is not true if the table has non-clustered indexes as well. And actually if this table did have additional indexes it still doesn't NEED to rebuild the primary index first if like our table it was all just adds, right?

My DBA says he manages all the indexes at the database level, not at the table level.

Additional information

My assumption is that after the rebuild the pages on disk look the same as they did before and we're just wasting our time database shuffling.

Since you don't delete rows, no, rebuilding the index will provide no real value outside of updating the statistics for said index, as they are implicitly updated with a rebuild operation.  A more efficient approach is to just run an UPDATE STATISTICS statement instead.

Per Benjamin Nevarez:

  Rebuilding an index, for example by using ALTER INDEX … REBUILD, will also update index statistics with the equivalent of using WITH FULLSCAN.

To answer your other questions:

  would the table be any different?

Other than located at a different page address, no, structurally it would likely be no different.

  Obviously this is not true if the table has non-clustered indexes as well.

Rebuilding the clustered index doesn't automatically rebuild any non-clustered indexes associated with the table.  To do this, you would need to specify the keyword ALL instead of the clustered index name.  Per MS:

  ...using ALTER INDEX…REBUILD to rebuild a clustered index does not rebuild its nonclustered indexes by default... You have to specify ALL for the index name in order to rebuild all indexes.

And the last question:

  if this table did have additional indexes it still doesn't NEED to rebuild the primary index first if like our table it was all just adds, right?

Correct.  If you never modify a record after it's added to the end of the table, you'd really only ever need to update statistics in order to avoid the Ascending Key Problem as identified by Scott Hodgin's comment below.


Changing a certifcate+key algorithm by dropping & recreating with the same names We have a situation where a symmetric key has been created (a long time ago) using TRIPLE_DES.  This is used to encrypt a password column, and is used by around ten stored procedures (that utilizes these with OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE MyCertificate01;)

I want to replace the certificate and key (to offer a modicum of future-proofing) with AES_192.  However, I'd prefer to avoid editing the stored procedures if possible.

I wrote the following routine that:

Unencrypts the password column into a plain text field
Drops the existing key and certificate
Creates a new certificate and key with the same names as above
Re-encrypts the plain text column using the new cert/key

My question is, to the trained-eye, is there anything here that sets off alarm bells, particularly because the old and new names are the same, and that these are used within stored procedures?

I've tested the code and it worked, but my lack of experience with encryption and a dose of information-overload today makes me very nervous, hence the plea for expertise...

-- Decrypt data

ALTER TABLE [dbo].[tbl_Users] ADD PasswordClear nvarchar(250) NULL;
GO

OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE MyCertificate01;
 UPDATE [tbl_Users] SET PasswordClear = CONVERT(nvarchar(250), DECRYPTBYKEY(PasswordEnc))
CLOSE SYMMETRIC KEY SSN_Key_01;
GO

-- Drop old encryption

ALTER TABLE [dbo].[tbl_Users] DROP COLUMN PasswordEnc;
GO

DROP SYMMETRIC KEY SSN_Key_01;
GO

DROP CERTIFICATE [MyCertificate01];
GO

-- Create new certificate and key

CREATE CERTIFICATE MyCertificate01 WITH SUBJECT = 'My Certificate 01';
GO

CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = AES_192 ENCRYPTION BY CERTIFICATE MyCertificate01;
GO

ALTER TABLE [dbo].[tbl_Users] ADD PasswordEnc varbinary(256) NULL;
GO

OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE MyCertificate01;
 UPDATE [tbl_Users] SET PasswordEnc = ENCRYPTBYKEY(Key_GUID('SSN_Key_01'), PasswordClear);
CLOSE SYMMETRIC KEY SSN_Key_01;
GO

ALTER TABLE [dbo].[tbl_Users] DROP COLUMN PasswordClear;
GO

ALTER TABLE [dbo].[tbl_Users] ALTER COLUMN PasswordEnc varbinary(256) NOT NULL;
GO

Just to feed back on this, the process worked fine and the data was re-encrypted successfully.

Therefore despite some concerns raised over symmetrical data encryption, (of which there are business rules to justify this), all worked well.

All stored procedures that reference the key continued to work fine without any modification being required.


SQL Server Agent properties I have several dozen servers and would like to be able to check configurations on them ever quarter or so to make sure nothing has changed. I am having difficulty finding how to read a few configurations. Those settings I'm having problems with are in the SQL Server Agent properties:

Under the Alert System tab:
   Enable Mail profile
   Mail system
   Mail Profile
   Enable Fail-safe operator
   Operator
   Notify Using

Under the History tab:
   Limit size of job history log
   Remove agent history

Hopefully someone can point me in the direction of where I can run a cross server query or better yet powershell query to check all my servers configurations. Thanks!

For a "cross-server" query you could set up a Central Management Server and add Registered Servers.


Is there any risk associated with stopping mysqldump incomplete? I started to backup 30GB table at wrong time and now I want to stop it because it is estimated to take 7.5hours.

I used following command and options:
mysqldump -u db -p user table --hex-blob | gzip> 25Nov-table.sql.gz

My question is how to stop mysqldump now (leaving backup incomplete) without inflicting any damage to my database (though I think there will be no risk)?

You should be able to stop mysqldump by pressing control-c... or if it's running in the background, by killing the mysqldump process.

The server will realize almost immediately that the client connection mysqldump was holding has gone away and will release any table locks (held by mysqldump, if any) automatically.

This should be a completely safe operation.


Is it a good practice to use index hints in production code? I came across a situation where one of my MySQL (5.6) views (type - MERGE) was performing quite bad in order to fetch records (~3500 rows) for an entity id say 123.
This view performs inner joins across a few tables in order to get these records.
Surprisingly, the view returns results in ~200ms on my local docker MySQL container but takes ~10s on an AWS MySQL RDS instance.
On viewing the query plans in both environments, I found that the join order was different because the index utilised for one of the tables in the join was different (unique index is used by RDS but PRIMARY by docker mysql).
I tried a few things such as rebuilding indexes and rebuilding table stats using the analyse command on all concerned tables but all in vain. The connection collation is also the same as the table collations. The DB engine is InnoDB.
Moreover, the data on both environments is also identical as they have been setup using the same dump.
I have also verified that the indexes are the same in both environments.
The only way I can see running the query optimally on RDS is by using an index hint and that performs as expected.
In such a case, is it okay to use index hints in production code?
Or do we have any suggestions on how to troubleshoot this issue?

Although your question is about MySQL, Brent Ozar has a good article on this topic about SQL Server.
Generally you want to avoid it for a few reasons, data/stats/indexes can change in the future and then using that index would be bad and hurt performance instead. But if nothing else will solve this, then you can always use it and document it for the future.
I've used RDS a few times and it can be night and day different than using a database on a machine I've noticed. Are you using one for development and one for production? Or what's the different uses for the two? And do both have the same amount of CPU/RAM?


Implementing foreign keys on generalized data model Suppose the ASCII tree below is a UML generalized concept model.  To represent these entities/classes in UML, lets assume [mammal] a super class, and the level 1 classes, [wild] and [domestic] are sub classes in UML.  In other words, class [wild] inherits all attributes from [mammal], and [domestic] inherits all attributes from [mammal] as well.  Furthermore, [raccoon] and [white tail deer] inherit all attributes from [wild].

I want to convert the UML model to a relational database model.

Notice how [raccoon] extends both [wild] and [domestic].  In other words, it has two parents.  I would normally make the primary key of all descendant tables the same as their parent primary key.  In addition, I'd make the foreign key in the descendant table the same as the primary key in its parent table.  Because [raccoon] has two parents, how would I build that table?

mammal
 |
 +- wild
 |  |
 |  +- raccoon
 |  |
 |  +- white tail deer
 |
 +- domestic
    |
    +- cat
    |   |
    |   +- siberian
    |
    +- dog
    |   |
    |   +- golden retriever
    |   |
    |   +- lab
    |
    +- raccoon

You can have two (or more) foreign keys in a table. They can be both regarding the same attribute and referencing different tables. So you could have 2 foreign keys from racoon(pk), one referencing wild(pk) and the other referencing domestic(pk). Something like:

CREATE TABLE raccon
( mammal_id INT  PRIMARY KEY
, FOREIGN KEY (mammal_id)
    REFERENCES domestic (mammal_id)
, FOREIGN KEY (mammal_id)
    REFERENCES wild (mammal_id)
) ;

(assuming all tables have mammal_id as the primary key)


SQL Server thread status Our SQL is configured to use max of 704 threads,some times we get alerts stating only 10 threads are left,so i am not able to understand whether SQL keeps the thread open ,since again creating a new thread is expensive.

so my question is

How to know whether a thread is available for new request or currently busy with other requests.
I am trying to link task_address from sys.dm_exec_requests with sys.dm_os_tasks and worker_address like below

select * from sys.dm_exec_requests ec
join
sys.dm_os_tasks tsk
on tsk.task_state=ec.task_address
join
sys.dm_os_workers wrk
on wrk.worker_address=tsk.worker_address

I don't see any output,does this mean i can assume all threads are free

Currently below is the state of my worker threads,What does this suspended mean ?.I could see count more than 500 with suspended status.I don't see any blockings

4.Can i use below query to find that i have worker thread starvation

select status from sys.dm_Exec_requests

if status is pending,can i assume SQL is waiting for new worker threads

Currently I see one session,is having more than 250 rows in sysprocesses,when I query sys.dm_os_Waiting_tasks,I could see more than 2186 rows and 90% of them are for same session.So my question here is how can a query span these many threads

Will I get available worker count using below query,is this correct ?

select (
        select max_workers_count
        from sys.dm_os_sys_info
        ) - (
        select sum(active_workers_count)
        from sys.dm_os_Schedulers
        )

How to know whether a thread is available for new request or currently busy with other requests.

You can use DMV sys.dm_os_schedulers to get this information. The column you have to refer is work_queue_count. As per BOL it means

  Number of tasks in the pending queue. These tasks are waiting for a worker to pick them up. Is not nullable.

You can use below query to check number of waiting taks for all online schedulers

select work_queue_count from sys.dm_os_scheduler where status='Visible online'

I am not sure what you are trying to achieve but below is what I got from this blog

  select wt.session_id,
  ot.task_state,
  wt.wait_type,
  wt.wait_duration_ms,
  wt.blocking_session_id,
  wt.resource_description,
  es.[host_name],
  es.[program_name]
  FROM  sys.dm_os_waiting_tasks  wt
  INNER  JOIN sys.dm_os_tasks ot
  ON ot.task_address = wt.waiting_task_address
  INNER JOIN sys.dm_exec_sessions es
  ON es.session_id = wt.session_id
  WHERE es.is_user_process =  1

  I could see count more than 500 with suspended status.I dont see any blockings

A suspended is thread which is waiting for some resource and is currently not active. The wait can be I/O, network etc.. See the blog I have shared for more details.

  Can i use below query to find that i have worker thread starvation

No that is not the correct query.

Stop using sys.sysprocesses is legacy view and MS does not recommends it to use. Instead use sys.dm_exec_requests
You can get worker thread count for each scheduler using DMV sys.dm_os_scheduler

   select current_workers_count from sys.dm_os_scheduler where status='visible online'


Help with wait type CHECK_TABLES_THREAD_BARRIER I have a 2016 SQL Server freshly built - 13.0.5149.0 - OS 2016 (Microsoft Windows NT 6.3 (14393)). The environment is VMWare. No SQL Server HA configuration.

It has a problem where CheckDB "stops" with a wait type of CHECK_TABLES_THREAD_BARRIER. It has happened twice on 2 different databases. I don't see much listed on online resources for what to do with this wait. Microsoft lists "TBD" on information. This is a non production proof of concept environment for an upgrade.

Can anyone provide any insight or next steps?

Although I don't have any info on this yet in my waits library (will be fixed soon), you're likely hitting this hang when CHECKDB has hit an error state and it waiting for the various threads to finish running.

This is a rare, known issue that Microsoft is investigating but as yet has not identified or fixed.

Thanks


How to (re)start Postgres on Windows command line in background? My local Postgres server is configured to start when the computer starts. I just changed postgresql.conf file and had to restart the server. I did:

pg_ctl restart

from the command line BUT that started the server on the next command prompt:

pg_ctl: old server process (PID: 2968) seems to be gone
starting server anyway
server starting

D:\>2016-10-12 16:24:48 EDT LOG:  redirecting log output to logging collector proces
2016-10-12 16:24:48 EDT HINT:  Future log output will appear in directory "pg_log".

If I ctrl-c out of it or close the command window, that closes the server. How can I use pg_ctl to (re)start the server in the background, emulating how it works by default (as a Windows service)?

As it turns out, it was running in the console because I didn't tell it where to stream the log. The following sent the process in the background:

pg_ctl restart -l %PGDATA%\server.log


Oracle 12cR2 - Image Copy Backup Scenario Backup type: Image copy.
Scenario: I want to 1 full db + 6 incremental backup and keep last 7 days backup on storage.
Here is my script:
RUN {

allocate channel c1 device type disk format '/path/%U';

allocate channel c2 device type disk format '/path/%U';

allocate channel c3 device type disk format '/path/%U';

allocate channel c4 device type disk format '/path/%U';

allocate channel c5 device type disk format '/path/%U';

allocate channel c6 device type disk format '/path/%U';

allocate channel c7 device type disk format '/path/%U';

allocate channel c8 device type disk format '/path/%U';

CROSSCHECK ARCHIVELOG ALL;

CROSSCHECK BACKUP;

CROSSCHECK BACKUPSET;

CROSSCHECK COPY;

DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

DELETE NOPROMPT EXPIRED BACKUP;

DELETE NOPROMPT EXPIRED BACKUPSET;

DELETE NOPROMPT EXPIRED COPY;

BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'my_tag_01' DATABASE;

RECOVER COPY OF DATABASE WITH TAG 'my_tag_01';

DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-3';

BACKUP SPFILE FORMAT '/path/spfile_%T_%U';

BACKUP CURRENT CONTROLFILE FORMAT '/path/cfile_%T_%U';

}

exit

How can I design my script according my scenario?
Thank you so much.
Best regards,

I assume you want something like this:

RUN
{
  RECOVER COPY OF DATABASE
    WITH TAG 'incr_update'
    UNTIL TIME 'SYSDATE - 7';
  BACKUP
    INCREMENTAL LEVEL 1
    FOR RECOVER OF COPY WITH TAG 'incr_update'
    DATABASE;
}

The first run takes a level 0 backup (even if the backup statement has level 1)
All the following backups are level 1 backup that contain only the differences to the previous backups. The recovery statement recovers until SYSDATE-7, that is until one week ago. After deleting all backups that aren't needed you have the following backups on disk:

a level 0 backup that is from 7 days ago
the daily  level 1 backup of the last 6 days including today's backup
the archive log backups of the last seven days

I never tried out this strategy but I think a recovery window of 7 days is the appropriate policy.
For control files the following can be found in the Database Backup and Recovery User's Guide

Backing Up Control Files with RMAN
You can back up the control file when the database is mounted or open.
...
If the CONFIGURE CONTROLFILE AUTOBACKUP command is set to ON (by default it is OFF), then RMAN automatically backs up the control file and server parameter file after every backup and after database structural changes. The control file autobackup contains metadata about the previous backup, which is crucial for disaster recovery.
If the autobackup feature is not set, then you must manually back up the control file in one of the following ways:

Run BACKUP CURRENT CONTROLFILE.
Include a backup of the control file within any backup by using the INCLUDE CURRENT CONTROLFILE option of the BACKUP command.
Back up data file 1, because RMAN automatically includes the control file and server parameter file in backups of data file 1.

We do a backup of the whole database that contains a backup of data file 1, so it will contain a backup of a control file, too. But I recommend to enable the autobackup of the control file, too.

CONFIGURE CONTROLFILE AUTOBACKUP ON;

or

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO '/path/autobackup/%U';

You want to be able to recover to every point in time ind the last 7 days, so you should configure

CONFIGURE  RETENTION POLICY  TO RECOVERY WINDOW OF 7 DAYS;

This deletion policy also includes the backups of the archive log.
You can also configure the archive log deletion policy:

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE disk;

This deletion policy is related to theog archiving destinations, not to the archive log backups. Usualy archiving is done to the "recovery_file_dest".

The archived log deletion policy applies to all log archiving destinations, including the fast recovery area. The policy does not apply to archived redo log files in backup sets.
Only archived redo log files in the fast recovery area are automatically deleted by the database. You can execute the BACKUP ... DELETE INPUT, DELETE ARCHIVELOG, or DELETE OBSOLETE commands to delete logs manually from log archiving destinations, including the recovery area. If FORCE is not specified on the deletion commands, then these deletion commands obey the archived log deletion policy. If FORCE is specified, then the deletion commands ignore the archived log deletion policy.

So finally you have the following scripts:
A configuration script that you have to run once:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO '/path/autobackup/%U';
CONFIGURE  RETENTION POLICY  TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE disk;

a script for the daily backup:

RUN {
allocate channel c1 device type disk format '/path/%U';
allocate channel c2 device type disk format '/path/%U';
allocate channel c3 device type disk format '/path/%U';
allocate channel c4 device type disk format '/path/%U';
allocate channel c5 device type disk format '/path/%U';
allocate channel c6 device type disk format '/path/%U';
allocate channel c7 device type disk format '/path/%U';
allocate channel c8 device type disk format '/path/%U';
RECOVER COPY OF DATABASE
  WITH TAG 'incr_update'
  UNTIL TIME 'SYSDATE - 7';
BACKUP
  INCREMENTAL LEVEL 1
  FOR RECOVER OF COPY WITH TAG 'incr_update'
DATABASE;
BACKUP ARCHIVELOG ALL DELETE INPUT;
DELETE OBSOLETE;
}

and the following script for the frequent backup of the archive logs. Maybe you want to use less channels for the archive logs.

RUN {
allocate channel c1 device type disk format '/path/%U';
allocate channel c2 device type disk format '/path/%U';
allocate channel c3 device type disk format '/path/%U';
allocate channel c4 device type disk format '/path/%U';
allocate channel c5 device type disk format '/path/%U';
allocate channel c6 device type disk format '/path/%U';
allocate channel c7 device type disk format '/path/%U';
allocate channel c8 device type disk format '/path/%U';
BACKUP ARCHIVELOG ALL DELETE INPUT;
}

If you do not want the recovery following each backup than another way is the following strategy using incremental backups
Once a week run the following script to get a level 0 backup

BACKUP
  INCREMENTAL LEVEL 0
  DATABASE;

On every other day take a levele 1 backup

BACKUP
  INCREMENTAL LEVEL 1
  DATABASE;

or you can do a

BACKUP
  INCREMENTAL LEVEL 0 CUMULATIVE
  DATABASE;

Again you should specify a recovery windows for 7 days and frequently take archive log backups.
You avoid the recovery of the database copy after each backup, but now you need more space for your backup because now you have always two full backups on your backup space. Also the number of level 1 backups ist more than 6.
Assume that  your retention policy is 7 days and you do your level 0 backup always on the same day at the same time (this is the way one usually does) , e.g. you start your backup always on Sunday at 12:00. Now we have Sunday the 29th, 12:00. We have already two level 0 backups on disk, one started on 22nd at 12:00 and one started at 15th at 12:00. If you want to restore the database now to the point in time 22nd, 12:00, this should be possible, because your retention policy is 7 days. But for this restore you need the backup of the 15th, because the backup didn't have finished (or even not started) on 22nd 12:00.
So if you issue a 'DELETE OBSOLETE' command at 29th, 12:00, this will not delete that level 0 backup started at 15th, 12:00. And if you start a level 0 backup now, you will have three level 0 backups on disk. If you want to avoid this to save space you can think about using a recovery window of 6 days instead of 7 days. Then you can issue a DELETE OBSOLETE statement on 29th, 12:00, before you start the backup and the backup of taken on 15th will be removed.
So finally you will have these scripts:
A configuration script that you have to run once:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO '/path/autobackup/%U';
CONFIGURE  RETENTION POLICY  TO RECOVERY WINDOW OF 6 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE disk;

A script for the weekly level 0 backup

RUN {
allocate channel c1 device type disk format '/path/%U';
allocate channel c2 device type disk format '/path/%U';
allocate channel c3 device type disk format '/path/%U';
allocate channel c4 device type disk format '/path/%U';
allocate channel c5 device type disk format '/path/%U';
allocate channel c6 device type disk format '/path/%U';
allocate channel c7 device type disk format '/path/%U';
allocate channel c8 device type disk format '/path/%U';
DELETE OBSOLETE;
BACKUP
  INCREMENTAL LEVEL 0
DATABASE PLUS ARCHIVELOG DELETE INPUT;
}

a daily script (except for the day when the level 0 runs) for the level 1 backups:

RUN {
allocate channel c1 device type disk format '/path/%U';
allocate channel c2 device type disk format '/path/%U';
allocate channel c3 device type disk format '/path/%U';
allocate channel c4 device type disk format '/path/%U';
allocate channel c5 device type disk format '/path/%U';
allocate channel c6 device type disk format '/path/%U';
allocate channel c7 device type disk format '/path/%U';
allocate channel c8 device type disk format '/path/%U';
DELETE OBSOLETE;
BACKUP
  INCREMENTAL LEVEL 1
DATABASE PLUS ARCHIVELOG DELETE INPUT;
}

and script for the archive log backups

RUN {
allocate channel c1 device type disk format '/path/%U';
allocate channel c2 device type disk format '/path/%U';
allocate channel c3 device type disk format '/path/%U';
allocate channel c4 device type disk format '/path/%U';
allocate channel c5 device type disk format '/path/%U';
allocate channel c6 device type disk format '/path/%U';
allocate channel c7 device type disk format '/path/%U';
allocate channel c8 device type disk format '/path/%U';
BACKUP ARCHIVELOG ALL DELETE INPUT;
}


SQL Server 2016: Temporal table performance vs. Triggers and CDC It seems to be quite difficult to find comparisons between system-versioned temporal tables and the older options, such as DB triggers and CDC. I currently don't have the time to write an extended test on SQL Server 2016, so I thought I'd ask about it here.

Basically, the typical advantage with triggers is that they are easier to manage in stand-alone and clustered / alwaysOn environments, are real-time for being synchronized, and have access to session data such as the user ID.

CDC on the other hand while requiring a bit more management and being asynchronous, is much lighter, and thus performs far better. So if there's any doubt at all that the bottle necking caused by triggers could become a problem, CDC will basically be the superior solution. In terms hardware requirements, there's a negligible extra space requirement by CDC due to using logs and cdc audit tables for tracking the changes.

The question: How do temporal tables compare to the two above? In terms of speed, performance, storage space usage. WHEN should I use temporal tables instead of triggers or CDC? When should I not?

I understand anything as potentially complex as the business requirements and technical limitations behind DB auditing isn't going to have one easy answer, as it depends largely on the requirements and scope of the project. But anything to shed more light on the questions above would be appreciated. Thanks!

It depends on your business case, Temporal tables and change data capture offer different functionality.

Temporal tables are used to provide a version of your table at a point in time. A use case might be a slowly changing dimension where you want to track the changes in dimension attributes and report them from any moment in time.

Change data capture might be used on an OLTP table, to allow you to easily facilitate the export to a data mart. It logs all changes to a separate table, so you can easily view changed rows since your last export LSN point.


Copy on Write for SQL Server I have 20 users that each keep a copy of a database on their local computer (technology other than SQL Server).
Their copy (we call it a sandbox) is updated each morning and their previous changes are throw away.
We are migrating this data to SQL Server and want to maintain the sandbox feature, but we don't want the overhead of installing SQL Server on each of their computers or making 20 separate databases.
Is there a "copy on write" feature that is easy to turn on that will essentially allow each person to only see their changes to the database while still using a common database?  I don't want to make big changes to the code to achieve this.  If big changes to the code are necessary I will probably go with SQL Server Express and have the database on their local machine.
The users are not technical.
The application that reads the data is an .exe that runs on the users' machine.
We can run what ever version of SQL Server that would support this.
There are enough nuances to the database interaction with the app that I am only going to use SQL Server as trying to switch to e.g. SQLite would find all kinds of problems.
The users need to update the data in sandbox so a read-only solution is not an option.

If the overhead you are concerned about with the local SQL installs is a licensing overhead, and the database is smaller than 10Gb, then you could instead install Express edition which is free (but has the 10Gb limit). If the DB is a little over 10Gb then you could restore to the server, compress enough tables & indexes to make up the difference, then backup and restore that locally.
There is no copy-on-write support in SQL Server itself. If you are running it on Linux you could perhaps try using LVM snapshots to try acheive this:

Drop the existing DBs if present
Drop the LVM snapshots if present from last time
Backup the DB and restore it with the data and log files on the reference LVM volume
Detach (sp_detach_db) the restored copy
Make enough read-write LVM snapshot volumes of the volume now containing the updated DB copy
Attach the DB from each of the snapshots (sp_attach_db)

If your storage subsystem is a SAN with RW-snapshot features than you could potentially use a similar method with that.
I would not recommend this, or other options with 20 copies of the DB on the same instance and drive(s), if the users might pose much load - they will all compete for IO, they will all compete for buffer pool memory, and while the on-disk data is CoW the SQL instance will not know this so will happily hold duplicate copies of unmodified data in memory.


Max Server Memory configured but it increases more of its configuration Following a previous post I've written, I've configured my server with the following parameters:

I put 22497 as MAx Server Memory, disable AWE, since it runs in 64 bit environment and it's a production server.
During a reboot, it starts from the min configuration, and slowly increase memory consumption until almost midnight that reaches the max memory configuration.
Until then if I monitor memory consumption, the task manager reports that memory increases to 25 GB of RAM.  Yesterday we removed AWE, reboot the instance, and it started slowly increasing memory Until around 17:00pm , it was using almost 25.2 GB of RAM:

Today after a reboot, it is now using 11GB.
I now, that in a previous post said this memory consumption is "Normal Behaviour" of SQL Server, but why it suddenly use more of the max memory I set as maximum?
UPDATE 2021-03-31:
As for Today March 31, 2021 08:09AM, my server is using 25.3 GB of RAM:

Using Aleksey Script This are the numbers SQL Reports is using:

Resource Monitor says SQL Server IS using all the RAM:

Yesterday at 17:00 it was using 13 GB

And this is the behaviour of the server, That's why I'm asking if this is normal or not, what is making the server use 25.3 GB if it's limit should be 22.5 GB (rounded per my calculations).  This is the issue.
I know Microsofts literature says  a lot of stuff, but I haven't get a clue of why this behaviour, I've already check  Stored Procedures, Indexes, Jobs, etc.. and I don't see what is making the SQL Server use more of the assigned maximum server memory.
I disabled AWE, since this instance is running in a Virtual Machine in Hyper V, 64 bit Environment, (Windows 2008 R2 64 Bit, Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor) and I can't figure what's going on with this behaviour.

MAX SERVER MEMORY worked a little differently before SQL Server 2012:

Starting with SQL Server 2012, Multi-Page allocations and CLR
allocations are also included in memory limits that are controlled by
max server memory (MB) and min server memory (MB).

Memory configuration and sizing considerations in SQL Server 2012 and later versions


Grouping Data Using Joined Tables with Certain Distinct Values I am struggling to get my head around how to do this so please bare with me while I explain.

We have an order system which records orders, order lines, items, users etc.
What I am interested in in this instance are the orders and order lines.

At the moment, we produce reports with sales figures, showing sales, returns etc, but it is slow and inefficient. This is because we are querying individual tables using PHP/MS SQL Server, looping through arrays and then bringing the data together.

I have been tasked to try and make this more efficient, and so I have tried to produce one query with joined, aliased, sub-query tables. These work fine and join together, but I am getting the wrong values due to the way I am grouping tables.

Note, I am unable to change any schema and I am working with an existing system.

This explanation may seem quite long-winded, and I probably will lose you with what I am trying to ask, but please stay with me.

Anyway, here is a diagram of the schema, sample data, and the result of my query at present:

SELECT
`ol`.`storeID`,
SUM(DISTINCT `o`.`grossValue`) AS 'Total',
SUM(DISTINCT `o`.`paymentValue`) AS 'paymentTotal'
FROM `orders` AS `o`
LEFT JOIN `orderLines` AS `ol` ON `o`.`orderID` = `ol`.`orderID`
WHERE (`o`.`orderDate` BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59')
GROUP BY `ol`.`storeID`

I have the produced the above quickly in MySQL as I am at home at the moment, and I imagine the solution would be similar for SQL server, but please correct me if I'm wrong, or perhaps provide solutions for both, which would be great!

Basically, the orders have many lines to them. The orders record the total value of the lines, and how much has been paid so far.
Each order line is a reference to a stock item.
Each line records which store it is recorded to, as different items could be sourced from different stores.
We want to produce reports that show how much each store makes in turnover.

As you can see in the result of my query store 06 is showing 4.99, however this should be 9.98.

What is happening is the netValue is being made distinct. I DON'T want this.
I DON'T want duplicates of order <=> orderlines, but I DO want duplicates of the netValue as these are individual orders.

Have I confused you yet?

Is there a way to still group by the storeID and sum the netValue, by perhaps grouping by the orderID, or orderLineID?

I have tried all types of combinations, and gotten many errors which I don't remember now, but mostly about aggregate columns and not being able to group etc.

All suggestions and help welcome :)

Create table code:

CREATE TABLE `orderLines` (
  `orderLineID` int(10) NOT NULL AUTO_INCREMENT,
  `orderID` int(10) NOT NULL,
  `itemCode` varchar(30) NOT NULL,
  `netValue` decimal(10,2) NOT NULL,
  `taxValue` decimal(10,2) NOT NULL,
  `storeID` varchar(2) NOT NULL,
  PRIMARY KEY (`orderLineID`)
);

CREATE TABLE `orders` (
  `orderID` int(10) NOT NULL AUTO_INCREMENT,
  `grossValue` decimal(10,2) NOT NULL,
  `paymentValue` decimal(10,2) NOT NULL,
  `typeID` int(10) NOT NULL,
  `orderDate` datetime NOT NULL,
  PRIMARY KEY (`orderID`)
);

Expected Result:

+---------+-------+--------------+
| storeID | Total | paymentTotal |
+---------+-------+--------------+
| 02      | 10.00 |  8.00        |
| 06      | 9.98  |  9.98        |
| 07      | 3.00  |  3.00        |
+---------+-------+--------------+

Remove the DISTINCT.  But... Then you will probably find the results are too big.  Here's why:

Think of gathering all the data for all the Orders + OrderLines -- one row for each combination.  Notice how that will include o.grossValue is multiple times.

What you need to do is to the o. summations before JOINing to ol.

But now I am wondering about something.  Your schema allows for one order to be fulfilled from multiple stores.  However, since your data does not have an example of such, I wonder which is true:

An order can come from only one store.  In this case moving the storeID to orders would be 'right' and make the query much simpler.
An order can come from multiple stores.  In this case, please augment the data set so that a proposed solution can be checked.

I don't think you need 'LEFT'.


Auto_increment value needs to be set as S1 Hi I need to set auto increment for a table as S1. I tried by altering the table and also by creating by new table
I am aware how to set auto_increment but when i try to set auto increment as S1 i am unable to do. since i need the primary key values should be S1,S2,S3 as following records. can any one please suggest the data type for that,

 desc test_1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | varchar(10)  | NO   | PRI | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE test_1 AUTO_INCREMENT='S1';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''S1'' at line 1

mysql> create table tbl(ID varchar(100) not null primary key auto_increment,name varchar(100)) auto_increment='s1';
ERROR 1063 (42000): Incorrect column specifier for column 'ID'
mysql> create table tbl(ID text not null primary key auto_increment,name varchar(100)) auto_increment='s1';
ERROR 1063 (42000): Incorrect column specifier for column 'ID'
mysql> create table tbl(ID varchar(100) not null primary key auto_increment,name varchar(100)) auto_increment='s';
ERROR 1063 (42000): Incorrect column specifier for column 'ID'

Autoincrement values can only be numeric (integer and floats types if I remember correctly).

If you want to store S1, ...., S1000, just store an integer, and return the extra S on select or on client side:

create table tbl(ID bigint not null primary key auto_increment,name varchar(100)) auto_increment=1;

insert into tbl (name) values ('a');
...

select concat('S', id) as id, name from tbl;
+----+------+
| id | name |
+----+------+
| S1 | a    |
| S2 | a    |
| S3 | a    |
| S4 | a    |
...


Help with SELECT queries I need help with some queries. I struggled do find out how to do it, but I think I finally caught on or at least got the results I was looking for in the database.

These are the tables (given by my professor):

Here are the problem statements and the solutions I have come up with:

  1) From that database, get supplier numbers for supplier who supply project J2, in supplier number order.

select SNO
from SPJ
Where JNO = 'J2'
Order by SNO ASC;

  2) Get part numbers for parts supplied by a supplier in LA

select DISTINCT PNO
from spj
where SNO in
(select SNO
from Suppliers
where CITY = 'LA' ) ;

  3) Get part numbers for parts supplied by a supplier in LA to a project in LA:

select PNO
from spj
where JNO in
(select JNO
from projects
where CITY in
(Select CITY
from Suppliers
Where CITY = 'LA' ));

  4) Get the total quantity of part P2 supplied by supplier S3

select SUM(QTY)
From spj
where PNO = 'P2'
and SNO = 'S3' ;

  5) for each part being supplied to a project, get the part number, the project number, and the corresponding total quantity

select PNO, JNO, QTY
from spj

  6) Get project names for project supplied by supplier S1 located in HON

Select Jname
from projects
where CITY = 'HON'
and JNO in
(Select JNO
from spj
where SNO = 'S1' );

  7) Get part numbers for parts supplied to any project in LA

select PNO
from spj
where JNO in
(Select JNO
from projects
where CITY = 'LA' );

SQLFiddle with test data here

Your answer to number 5 is lacking a GROUP BY (corresponding total quantity).

If your course requires you to return sets (as opposed to multi-sets) you may need to eliminate potential duplicates from some of those queries. Your results may not show duplication now, due to the data in the tables, but to be correct for different states of the database (different contents) you may need to add DISTINCT, unless constraints exist to make it unnecessary.

Some of your queries could equally be expressed using joins - if you have covered those, it might be a good idea to use some.


Upgraded Postgres; how to access now-missing databases? My local connection on my Mac (10.9.5) wasn't working to my Postgres (9.3) database (and therefore I couldn't actually export the data), so I tried upgrading it to 9.4. Luckily I can now connect to the database, but... My old databases (and tables, views, etc.) are now missing...

Is it possible to recover these? How would I go about doing that?

Before every major upgrade you need to do a backup/restore. The databases are there but 9.4 does not recognize them. The easiest way would be to remove 9.4, reinstall 9.3, do a full backup, then install 9.4 again and do a restore.


SSIS 2012 Project Connection Manager does not expose ServerName property for configuration in SSMS 2012 We have deployed one SSIS 2012 project with two ADO.Net Project Connection Managers. (There are five packages within the project.)

The project has a related Environment, with a variable containing a server name. I want to map the variable to the project connection manager ServerName property.

When I right-click on the SSIS project node in SSMS 2012, launching the Configure dialog box, I can locate the Connection Manager, and highlight it to see its properties, but ServerName is not visible in the Properties list.

Usually, the ServerName property is listed under the RetainSameConnection property. It's visible on other Project Connection Managers in the same project.

Why would this property be hidden, just for one Connection Manager?

I found the answer by opening the project with Visual Studio 2012.

Open the SSIS project in Visual Studio, and select the .conmgr file in the Solution Explorer pane. Use the context menu and select the "View Code" menu option. This will show the XML within the Connection Manager file.

In the XML, there was this tag:

<DTS:PropertyExpression
    DTS:Name="ServerName">@[$Project::my_server_name]</DTS:PropertyExpression>

The ServerName property was set via an expression!

This fact is not easily discernible via the Solution Explorer tree itself. When the conmgr file is selected, the Properties pane only displays the File Name and Full Path properties.

When you open a package that uses the Connection Manager, and select that Connection Manager within the package editor pane, the properties pane shows many other properties, including Expressions. The ServerName property will be visible in the Expressions list only if it has been set.

There are two ways to resolve this issue:

Within the Visual Studio project, open a package. Select the Connection Manager from the package editor pane, and delete the Property Expression from the Properties pane.
Remove the PropertyExpression tag from the .conmgr file with a text editor.

Either way, once the ServerName property is no longer set by an expression within the connection manager definition file, it is exposed as a configurable parameter in the Configure dialog box in SSMS 2012.


Restoring oracle database I come from SQL Server and I need to restore a oracle .bkp file in my database. I'm using RMAN and the path and name for the .bkp file is:

F:\Oracle Back Ups\Back.bkp

The command that I use in RMAN is:

RMAN> restore controlfile: "F:\Oracle Back Ups\Back.bkp"

But it shows an error:

ORA-19870 error while restoring backup piece F:\Oracle Back Ups\Back.bkp
ORA-19690 backup piece release 11.2.0.4.0 incompatible with oracle release 11.2.0.2.0

I need to know the proper commands to successfully complete this operation.

Note: The guy that provided the back up data gave me two files: One with the extension .bkp, and the second with .ctl. I don't know how the .ctl file is supposed to be used.

I'm using Oracle XE 11.0.0.2.0

Can you copy the 11.2.0.4.0 rman executable from your friend or get and perform a second "dummy" installation of Oracle Database on another temporary computer/server? If so, here's how you can solve it:

Let's say you were able to copy the 11.2.0.4.0 ORACLE_HOME folder from your friend, and called it dbhome_2 (do not replace current, otherwise you can mess up things).

Using the 11.2.0.4.0 rman executable:
- startup instance in nomount and restore controlfile from backup
- mount the db
- restore the archivelogs required (recover database until cancel)

Shutdown the database, and using 11.2.0.2.0 executable:
- restore the control file again, from a 11.2.0.2.0 generated backup
- mount the db
- recover database until cancel
- open database with resetlogs

Hope it helps.


Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade In mysqld.log I have thousands of lines like:

Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade

So, according to virtually every post on the matter (and error msg itself):

mysql_upgrade -u root -p

Enter password:
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
...

and all tables in all databases are like

...
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
The sys schema is already up to date (version 1.5.1).
Checking databases.
...

But, I am still getting the error in the log

2019-01-27T18:37:15.304587Z 66190 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2019-01-27T18:37:15.304620Z 66190 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2019-01-27T18:37:15.304684Z 66190 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2019-01-27T18:37:15.304707Z 66190 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade

The structure appears to be okay.  Is there a config issue?  Please advise.

The answer appears to be - reboot server.  I do not have rights to do this, but after admin rebooted, issue appears to be resolved.  Not sure why just rebooting MySQL did not fix?

Maybe this information is useful.
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-23.html

Note
This release includes a change to the innodb_index_stats and innodb_table_stats system tables. When upgrading to this release, be sure to run mysql_upgrade in order to include these changes.


Use composite type to create new table In ORDBMS databases I have seen that complex types can be used:

create type name as( ...)

And I can references those types when creating a new table, like:

create table example (row_name ref(name))

How can I achieve the same in PostgreSQL?

You can use a typed table:

CREATE TYPE mytype AS (some_id int, some_col text);

CREATE TABLE example OF mytype (PRIMARY KEY (some_id));

I added a PK constraint (which you did not ask for).

The syntax is documented as second variant in the manual for CREATE TABLE, which also explains further down:

  OF type_name

  Creates a typed table, which takes its structure from the specified composite type (name optionally schema-qualified). A typed
  table is tied to its type; for example the table will be dropped if
  the type is dropped (with DROP TYPE ... CASCADE).

  When a typed table is created, then the data types of the columns are determined by the underlying composite type and are not specified
  by the CREATE TABLE command. But the CREATE TABLE command can add
  defaults and constraints to the table and can specify storage
  parameters.

There is also a code example at the end of the examples section.


Implementation of a many-to-many relationship with total participation constraints in SQL How should I implement in SQL the scenario depicted in the following Entity-Relationship diagram?

As it is shown, every A entity type occurrence must be related to at least one B counterpart (indicated by the double connecting lines), and vice versa. I know I should create the three tables that follow:

    CREATE TABLE A
    (
        a INT NOT NULL,
        CONSTRAINT A_PK PRIMARY KEY (a)
    );

    CREATE TABLE B
    (
        b INT NOT NULL,
        CONSTRAINT B_PK PRIMARY KEY (b)
    );

    CREATE TABLE R
    (
        a INT NOT NULL,
        b INT NOT NULL,
        CONSTRAINT R_PK      PRIMARY KEY (a, b),
        CONSTRAINT R_to_A_FK FOREIGN KEY (a)
            REFERENCES A (a),
        CONSTRAINT R_to_B_FK FOREIGN KEY (b)
            REFERENCES B (b)
    );

But, what about the implementation of the total participation constraints (i.e., enforcing that each instance of either A or B is involved in a minimum of one relationship occurrence with the other)?

It's not easy to do in SQL but it is not impossible. If you want this enforced through DDL alone, the DBMS has to have implemented DEFERRABLE constraints. This could be done (and can be checked to work in Postgres, that has implemented them):

-- lets create first the 2 tables, A and B:
CREATE TABLE a
( aid INT NOT NULL,
  bid INT NOT NULL,
  CONSTRAINT a_pk PRIMARY KEY (aid)
 );

CREATE TABLE b
( bid INT NOT NULL,
  aid INT NOT NULL,
  CONSTRAINT b_pk PRIMARY KEY (bid)
 );

-- then table R:
CREATE TABLE r
( aid INT NOT NULL,
  bid INT NOT NULL,
  CONSTRAINT r_pk PRIMARY KEY (aid, bid),
  CONSTRAINT a_r_fk FOREIGN KEY (aid) REFERENCES a,
  CONSTRAINT b_r_fk FOREIGN KEY (bid) REFERENCES b
 );

Up to here is the "normal" design, where every A can be related to zero, one or many B and every B can be related to zero, one or many A.

The "total participation" restriction needs constraints in the reverse order (from A and B respectively, referencing R). Having FOREIGN KEY constraints in opposite directions (from X to Y and from Y to X) is forming a circle (a "chicken and egg" problem) and that's why we need one of them at least to be DEFERRABLE. In this case we have two circles (A -> R -> A and B -> R -> B so we need two deferrable constraints:

-- then we add the 2 constraints that enforce the "total participation":
ALTER TABLE a
  ADD CONSTRAINT r_a_fk FOREIGN KEY (aid, bid) REFERENCES r
    DEFERRABLE INITIALLY DEFERRED ;

ALTER TABLE b
  ADD CONSTRAINT r_b_fk FOREIGN KEY (aid, bid) REFERENCES r
    DEFERRABLE INITIALLY DEFERRED ;

Then we can test that we can insert data. Note that the INITIALLY DEFERRED is not needed. We could have defined the constraints as DEFERRABLE INITIALLY IMMEDIATE but then we'd have to use the SET CONSTRAINTS statement to defer them during the transaction. In every case though, we do need to insert into the tables in a single transaction:

-- insert data
BEGIN TRANSACTION ;
    INSERT INTO a (aid, bid)
    VALUES
      (1, 1),    (2, 5),
      (3, 7),    (4, 1) ;

    INSERT INTO b (aid, bid)
    VALUES
      (1, 1),    (1, 2),
      (2, 3),    (2, 4),
      (2, 5),    (3, 6),
      (3, 7) ;

    INSERT INTO r (aid, bid)
    VALUES
      (1, 1),    (1, 2),
      (2, 3),    (2, 4),
      (2, 5),    (3, 6),
      (3, 7),    (4, 1),
      (4, 2),    (4, 7) ;
 END ;

Tested at SQLfiddle.

If the DBMS does not have DEFERRABLE constraints, one workaround is to define the A (bid) and B (aid) columns as NULL. The INSERT procedures/statements will then have to first insert into A and B (putting nulls in bid and aid respectively), then insert into R and then update the null values above to the related not null values from R.

With this approach, the DBMS does not enforce the requirements by DDL alone but every INSERT (and UPDATE and DELETE and MERGE) procedure has to be considered and adjusted accordingly and users have to be restricted to use only them and not have direct write access to the tables.

Having circles in the FOREIGN KEY constraints is not considered by many the best practice and for good reasons, complexity being one of them. With the second approach for example (with nullable columns), updating and deleting rows will still have to be done with extra code, depending on the DBMS. In SQL Server for example, you can't just put ON DELETE CASCADE because cascading updates and deletes are not allowed when there are FK circles.

Please read also the answers at this related question:
How to have a one-to-many relationship with a privileged child?

Another, 3rd approach (see my answer in the above mentioned question) is to remove the circular FKs completely. So, keeping the first part of the code (with tables A, B, R and foreign keys only from R to A and B) almost intact (actually simplifying it), we add another table for A to store the "must have one" related item from B. So, the A (bid) column moves to A_one (bid) The same is done for the reverse relationship from B to A:

CREATE TABLE a
( aid INT NOT NULL,
  CONSTRAINT a_pk PRIMARY KEY (aid)
 );

CREATE TABLE b
( bid INT NOT NULL,
  CONSTRAINT b_pk PRIMARY KEY (bid)
 );

-- then table R:
CREATE TABLE r
( aid INT NOT NULL,
  bid INT NOT NULL,
  CONSTRAINT r_pk PRIMARY KEY (aid, bid),
  CONSTRAINT a_r_fk FOREIGN KEY (aid) REFERENCES a,
  CONSTRAINT b_r_fk FOREIGN KEY (bid) REFERENCES b
 );

CREATE TABLE a_one
( aid INT NOT NULL,
  bid INT NOT NULL,
  CONSTRAINT a_one_pk PRIMARY KEY (aid),
  CONSTRAINT r_a_fk FOREIGN KEY (aid, bid) REFERENCES r
 );

CREATE TABLE b_one
( bid INT NOT NULL,
  aid INT NOT NULL,
  CONSTRAINT b_one_pk PRIMARY KEY (bid),
  CONSTRAINT r_b_fk FOREIGN KEY (aid, bid) REFERENCES r
 );

The difference over the 1st and 2nd approach is that there are no circular FKs, so cascading updates and deletes will work just fine. The enforcement of "total participation" is not by DDL alone, as in 2nd approach, and has to be done by appropriate procedures (INSERT/UPDATE/DELETE/MERGE). A minor difference with the 2nd approach is that all the columns can be defined not nullable.

Another, 4th approach (see @Aaron Bertrand's answer in the above mentioned question) is to use filtered/partial unique indexes, if they are available in your DBMS (you'd need two of them, in R table, for this case). This is very similar to the 3rd approach, except that you won't need the 2 extra tables. The "total participation" constraint has still to be applied by code.


Is it good practice to have relationship between the tenant table and all other tables in a multi tenant application? I am developing a multi tenant SaaS that uses Azure SQL for data storage. I make use of sp_set_session_context to make sure that any query only touches records relevant to the current tenant.

I occationally get requests to delete a tenant from the database. That means the record in the Tenants table and all other records in other tables with the same Tenant_Id. Today I do this with one DELETE statement per table.

Is it a good idea to create a relationship between Tenant and all other tables? This way I could use cascade delete to delete a tenant by simply removing a single record from the tenants table.

I am concerned about the more difficult recovery in the case of a mistakenly deleted tenant. I also want to be confident that there will be no serious performance issues with such a schema.

Is it a good idea to create a relationship between Tenant and all other tables?

Yes. Absolutely yes, you should have a relationship between them physically (it is already there logically).

This way I could use cascade delete to delete a tenant by simply removing a single record from the tenants table.

Er, um, not so sure about this one. Depending on the size / complexity of your schema, you might not be able to do this anyway due to potential circular references. I would do cascading delete FKs for subsets of tables that together act as a particular feature or property. But I don't see this as a good thing for doing over the entire schema, even if you don't get circular reference errors. Still, adding ON DELETE CASCADE to subsets of tables will at least reduce the total number of delete statements (although, you should already be doing the DELETEs in a transaction, so transactionally this won't really be any different; it will be fewer DELETE statements for you to code / maintain, though the main benefit will be helping to ensure integrity within those subsets).

For more info, please see my answer, here on DBA.SE, to the following question:
Composite Primary Key in multi-tenant SQL Server database