psql command, how to enforce certificate check? I have created a cockroachdb v20.1.0 cluster based on this: https://www.cockroachlabs.com/docs/stable/deploy-cockroachdb-on-premises.html

I have also created a test user like this:

CREATE USER test with password '****';
CREATE DATABASE test;
GRANT ALL ON DATABASE test TO test;
SHOW GRANTS ON DATABASE test;
SHOW GRANTS FOR test;

Then I have created a unix user called test, and tried to connect to this database.

I put this into .pgpass:

localhost:26257:test:test:*****

If I try to connect with psql client, everything seems to be fine:

psql --host=localhost -d test -p 26257

psql (12.2 (Ubuntu 12.2-4), server 9.5.0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.

test=>

The connection is encrypted with TLSv1.0, the authentication succeeds.

However, if I try to connect with cocroachdb sql command, then it fails:

$ cockroach sql --host=localhost --user=test                                                                                130 ↵
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
ERROR: cannot load certificates.
Check your certificate settings, set --certs-dir, or use --insecure for insecure clusters.

problem with CA certificate: not found
Failed running "sql"

It is clearly stated in the cockroachdb docs ( https://www.cockroachlabs.com/docs/stable/authentication.html#client-authentication ) that the cockroachdb client always needs a CA certificate to validate the node's certificate.

In the postgresql documentation ( https://www.postgresql.org/docs/12/libpq-ssl.html ) they say the following:

  By default, PostgreSQL will not perform any verification of the server
  certificate. This means that it is possible to spoof the server
  identity (for example by modifying a DNS record or by taking over the
  server IP address) without the client knowing. In order to prevent
  spoofing, the client must be able to verify the server's identity via
  a chain of trust.

This documentation also tells that when "sslmode is set to verify-full" then libpq will verify the server's certificate.

However, I do not see any command line option (for the psql command!) to turn on certification verification forcedly.

Is there an environment variable or a command line option for psql that will enforce certificate check for the connection?

To have psql verify the server certificate, you must specify the appropriate connection string options. This is done by the libpq client shared library, so it will work the same with all clients that use it.

You can directly specify the options in the connection string:

psql 'host=... port=... dbname=... user=... sslmode=verify-full sslrootcert=/path/to/ca-certificate sslcrl=/path/to/certificate-revocation-list'

If the CA certificate of the revocation list are in the default locations, you can omit the respective options.

See the documentation on sslrootcert and sslcrl.
You can use environment variables to specify the options:

export PGSSLMODE=verify-full
export PGSSLROOTCERT=/path/to/ca-certificate
export PGSSLCRL=/path/to/certificate-revocation-list
psql -h ... -p ... -d ... -U ...

See the documentation on environment variables for details.

For details on how server verification works, consult the appropriate section of the documentation.


SQL Server: Duplicate Index Question We recently has a massive performance problem on our system, which we narrowed down to a problem with I/O on the LUN.  IOPS increased twenty-fold during the period when the problem was occuring.  The LUN in question was the one that tempdb uses.  This is all according to our network/server admins (I have no network/SAN/Server expertise).

Everyone on the team was investigating the problem, until one team member found an expensive query in activity monitor that look suspicious.  He inspected the table and found and index that was a duplicate.  Actually, it was technically an overlap, since it had one additional included column over and above from the original index.

The overlapping index had been in production for about three weeks with no problems.

The index was dropped in production, and the performance problems stopped almost immediately after  We were not all in sync monitoring and trying fixes (never mind that this isn't a good practice).  So it's possible it was a coincidence, but no other changes were made in the same timeframe, and we're 99% sure this index was somehow the cause.

I realize a superfluous index can cause performance problems, but I have never heard of a case where the problem would be that pronounced.  Adding the index back also caused no problems (I know--also not a good practice in production).

Any idea of what the root cause of the problem could have been?  The duplication?  A corrupted index (if such a think exists)?

Any online sources as references would be deeply appreciated as well.

Without the execution plans, it's impossible to say, but I've seen a similar scenario pop up.  Had a client once whose database was teetering on the edge of being able to fit in memory.  Someone added an index on a large table, queries started using it (which kept big chunks of it in memory.)  Things all used to fit in memory perfectly, but they hit a tipping point where they started to hammer the disk much more frequently.  We caught it via the sudden spike in disk access and a sudden drop in Page Life Expectancy.

Ideally, you should capture these Perfmon counters continuously on database servers, and alert when they drop dangerously.  That'd help correlate things so you'd know if the PLE drop also happened, and that'd indicate you might be running out of memory to cache things.


Automate - recover database using backup controfile I am trying to automate a periodic clone of a database from one environment into another changing the database name.  I have done this many times manually by doing a begin/end backup on the source system, copying the storage shares and cloning them into the new location.  My hangup comes in automating the recovery piece.  After recreating the control file, I do the following command (generated from the backup of the control file to trace):

recover database using backup controfile

The problem is that this command has a prompt.  Because of the way I am doing the process, the archive logs needed are all present, so all that is necessary is to type AUTO.  Then after waiting for it to complete, do the alter database open resetlogs.

My question is, how can I automate this last piece?  I have a few ideas, but I'm not fond of any of them.  Hopefully, there is something obvious I am missing.

Update:
I've tried using the CONTINUE DEFAULT syntax, but it doesn't seem to work.

SQL> RECOVER DATABASE UNTIL TIME '2015-10-20:13:54:16' USING BACKUP CONTROLFILE CONTINUE DEFAULT;
ORA-00274: illegal recovery option CONTINUE

SQL> RECOVER DATABASE UNTIL TIME '2015-10-20:13:54:16' CONTINUE DEFAULT USING BACKUP CONTROLFILE;
ORA-00274: illegal recovery option CONTINUE

Use AUTOMATIC.

RECOVER AUTOMATIC DATABASE UNTIL TIME '2015-10-20:13:54:16' USING BACKUP CONTROLFILE;

CONTINUE [DEFAULT] is a different branch and can not be used together with clauses like UNTIL and USING BACKUP CONTROLFILE.

Control file created.

SQL> recover automatic database until change 473319 using backup controlfile;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select resetlogs_change# from v$database;

RESETLOGS_CHANGE#
-----------------
           473320


Better defining a parent-child relationship on a table Our team is developing a new system and it has a Warehouse table that must have a hierarchical relationship between entries since a customer can have 1st, 2nd, 3rd and Nth degree warehouses.

We first thought that this was the best way to do it. By creating a Parent and a Child column on the table and then making Parent the FK to Child (on the same table) but friend DBA told us that there is a different way.

So, for instance, we had thought about making a table like this:

CREATE TABLE Warehouse(
filterId INT PRIMARY KEY IDENTITY,
parentId INT NOT NULL,
active BOOL DEFAULT 1,
warehouseName VARCHAR(30) NOT NULL,
FOREIGN KEY parentId REFERENCES Warehouse(filterId)

| FILTERID   | PARENTID | ACTIVE | WAREHOUSENAME |
--------------------------------------------------
|          1 |        0 |      1 | N WAREHOUSE 1 |
|          2 |        1 |      1 | ROOM 1        |
|          3 |        1 |      1 | ROOM 2        |
|          4 |        1 |      1 | ROOM 3        |
|          5 |        1 |      1 | ROOM 4        |
|          6 |        2 |      1 | SHELF 1       |
|          7 |        2 |      1 | SHELF 2       |
|          8 |        2 |      1 | SHELF 3       |
|          9 |        2 |      1 | SHELF 4       |
|         10 |        2 |      1 | SHELF 5       |
|         11 |        2 |      1 | SHELF 6       |
|         12 |        2 |      0 | SHELF 7       |
|         13 |        3 |      1 | BOX 1         |
|         14 |        3 |      0 | BOX 2         |
|         15 |        3 |      1 | BOX 3         |
|         16 |        3 |      1 | BOX 4         |
--------------------------------------------------

But this DBA said that we should work on something like this:

CREATE TABLE Warehouse(
filterId VARCHAR(20) PRIMARY KEY,
active BOOLEAN DEFAULT 1,
warehouseName VARCHAR(30) NOT NULL)

| FILTERID   | ACTIVE | WAREHOUSENAME |
---------------------------------------
| 1.00       |      1 | N WAREHOUSE 1 |
| 1.01       |      1 | ROOM 1        |
| 1.02       |      1 | ROOM 2        |
| 1.03       |      1 | ROOM 3        |
| 1.04       |      1 | ROOM 4        |
| 1.01.01    |      1 | SHELF 1       |
| 1.01.02    |      1 | SHELF 2       |
| 1.01.03    |      1 | SHELF 3       |
| 1.01.04    |      1 | SHELF 4       |
| 1.01.05    |      1 | SHELF 5       |
| 1.01.06    |      1 | SHELF 6       |
| 1.01.07    |      0 | SHELF 7       |
| 1.01.01.01 |      1 | BOX 1         |
| 1.01.01.02 |      0 | BOX 2         |
| 1.01.01.03 |      1 | BOX 3         |
| 1.01.01.04 |      1 | BOX 4         |
---------------------------------------

Which way should we follow?

On one hand we have a relationship between parentId and filterId; on the other we do not have any relationship, but it is easy to understand where does the entry belong.

Not all the warehouses are of the same level - that's what I mean by degree. They should be ordered like: For Customer A, 1 is parent of 1.1 and 1.2, 2 is parent of 2.1, 3 is parent of 3.1 and 4 is parent of 4.1 and 4.2 - but 1, 2, 3 and 4 are of the same level (1st level), and 1.1, 1.2, 2.1, 3.1, 4.1 and 4.2 are also of the same level (2nd level) but not of the same parents.

There are several models for hierarchical data in relational databases. These include

Adjacency List. This is your suggestion above.
Path Enumeration. This is the DBA's suggestion.
Nested Set
Closure Table

Each has a different insert, delete, neighbour-read and set-read characteristic. You will have to determine which, on average, is best for your application performance and development needs. For example, nested sets is great for reading a whole sub-tree but is expensive for inserts, and can be difficult to comprehend. Adjacency list is simple to understand and code, read/write is fast but frequent, large recursive queries can be expensive.

Since you're using SQL Server be aware of the hierarchyid data type.

I have found Joe Celko's book "Trees and Hierarchies in SQL for Smarties" to be most readable and informative.

There's a discussion on Stackoverflow which discusses this widely.


Post Migration error in sql server error log I am seeing below error messages when we have moved/migrated databases to new server/instances from SQL2K12 stanadlone to cluster

last target outstanding: 20800, avgWriteLatency 30
average throughput:  69.12 MB/sec, I/O saturation: 243550, context switches 398948
FlushCache: cleaned up 579723 bufs with 388331 writes in 65526 ms (avoided 125170 new dirty bufs) for db 19:0

I have no clue if this points any issue with stoage.

Now the concern is, before we did migration i had similar error messages on the this instance but they were like coming after half hour 2 hours or may 15mins. no fixed pattern.

But after migration above error message keeps coming every 2 mins from the day we migrated. That is what sounds alarming.

On the old server from where we migrated, yes we had similar messages but again there was no fixed patterm, sometimes 2 mins, an hour may be after 4 hours, but not continuous like on new server.

I got some info here https://blogs.msdn.microsoft.com/psssql/2012/06/01/how-it-works-when-is-the-flushcache-message-added-to-sql-server-error-log/

But still i am confused how to deal with this and what exactly these messages point dba's to look for?

Please suggest

Those messages indicate checkpoint activity is occurring.  Checkpoints in SQL Server 2012 occur frequently, depending on several configuration variables, and are typically not something to be overly concerned about.

Check the recovery interval for your server:

SELECT c.name
    , c.value_in_use
FROM sys.configurations c
WHERE c.name = 'recovery interval (min)';

From the Microsoft Docs:

  The default recovery-interval value is 0, which allows the Database Engine to automatically configure the recovery interval. Typically, the default recovery interval results in automatic checkpoints occurring approximately once a minute for active databases and a recovery time of less than one minute. Higher values indicate the approximate maximum recovery time, in minutes. For example, setting the recovery interval to 3 indicates a maximum recovery time of approximately three minutes.

What is your recovery time set to?  Are you experiencing poor performance?


Best way to store a list of numbers where individual numbers can be deleted I want to store a list of 50 or less nine digit numbers for each primary key in a DynamoDB. Usually I would be using a get request to retrieve the list for a specific primary key.
Sometimes I would need to add or delete a number from the list. Would it be better to replace the whole list when changes are made or is there a way to structure the data so that individual numbers can be added or deleted. If that is possible, would it be a less efficient way of doing things even though the individual changes would be easier?

look up put with conditional expression attribute_not_exists so you will only put the item if it does not exist.  delete single item only by parturition key and range key, not all of them. also the primary key combination must be unique anyways so you can't have duplicates.


Replace content of only one table with new version (should disable foreign keys?) I have 2 tables. 2nd has foreign keys to reference 1st.
I need to replace the whole content of the 1st table with the new version from another database. The new version satisfies all constraints and foreign keys.
I need a generic way to do it for any table so no exact name of foreign keys or constraints or columns may be "hardcoded" (but script "generation" like pg_dump does is ok).

I can't just truncate 1st table and insert everything from the new version: pg doesn't allow to delete referenced rows.

I tried to use deferred like this (I planned to automatically append it to pg_dump generated sql):

START TRANSACTION;

SET CONSTRAINTS ALL DEFERRED;

DELETE FROM X;

// ORIGINAL PART OF GENERATED SCRIPT
INSERT INTO X VALUES (10, ....)

COMMIT;

but I see Key (id)=(10) is still referenced from table.

The constraint:

CONSTRAINT second_fkey FOREIGN KEY (X) REFERENCES first (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY

The deferred constraints solution (see an example in the question) actually works if I make the keys deferrable in normal way.

This didn't work because I did UPDATE pg_catalog.pg_constraint SET condeferrable = true; but forgot to set the same for pg_catalog.pg_trigger.


SQL Server 2014 In Memory Table Creation Error When I create an in-memory table, I get this error:

  The data types char(n) and varchar(n) using a collation that has a code page other than 1252 are not supported with memory optimized tables

i solved the problem with this link: http://www.yigitaktan.com/archives/492

The problem's source is that: Sql Server instance's collation was Turkish_CI_AS. Memory-Optimized processes haven't supported my collation.

SELECT NAME AS [Collation]
    ,COLLATIONPROPERTY(NAME, 'codepage') AS [CodePage]
FROM sys.fn_helpcollations()
WHERE COLLATIONPROPERTY(NAME, 'codepage') = 1252

This query lists supported collations.


What's the best solution for offline db syncing I'm building a system which requires a master db (BIG master) that contains things like accounts, balances, actions, etc.

This db then needs to be accessible at a physical place, like a fairground say by point of sale systems. The accounts are used by customers at the fair to buy things with their balance.

The network connectivity locally is fine, i.e. we can create a wifi network and have a copy of the master db available locally (SMALL master) . The issue is that network connectivity to the outside is patchy/non-existent for hours.

My thinking is that I need master to master replication because people could be signing up online (to BIG master) and then head to the fair. People will also be spending their balance at the fair (SMALL master) and obviously we can't have people spending their balance in 2 places (at the fair and online)

I'm using Postgres (latest) and was thinking of using bucardo for the replication.

Is this the best solution for this problem? Is there another solution to patchy db sync?

Bucardo is the standard tool for this sort of job.  It's well tested to run when the two servers are disconnected from each other for a while.  You could use any master/master replication approach instead:  Londiste, Slony, or the early release of BDR from 2ndQuadrant.

With the network setup you've described, you will be hard pressed to avoid race conditions here in all conditions no matter what you do.  The only way to keep someone from spending money in both locations here is to setup a two-phase commit transactions or do some form of synchronous replication, basically only putting the record on one machine if it's coordinated on both.  One nice thing about Bucardo is that it's designed expecting this will happen.  You can setup a conflict resolution mechanism, to recognize and potentially resolve conflicts on nodes when things are changed on both.  The other multi-master solutions are not aiming as directly at the disconnected network / conflicting change problems.


Sql Server thinks I have different Data Compression when swaping partitions I am setting partition switching for my database.

I created a script using SqlCmd variable so that the same script would make both my source and target tables for partition swapping.

Here is an example:

--:setvar IsArchive ""
--:setvar Suffix _Partitioned

:setvar IsArchive Archived
:setvar Suffix _Archived

CREATE TABLE [$(IsArchive)Orders].[Order$(Suffix)](
    [OrderId] [BIGINT] NOT NULL DEFAULT(NEXT VALUE FOR order.NextOrderId),
    [OtherStuff] [VARCHAR](100) NOT NULL,
 CONSTRAINT [PK_Order$(Suffix)] PRIMARY KEY CLUSTERED
(
    [OrderId] desc
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON OrderIdPartitionScheme(OrderId) WITH (DATA_COMPRESSION=PAGE)
GO

I run it once with the top two :setvar options and again with the bottom two :setvar options.

So they are very similar with mostly only name differences:

[Orders].[Order_Partitioned]
[ArchivedOrders].[Order_Archived]

When I run this:

ALTER TABLE Orders.Order_Partitioned SWITCH PARTITION 1
      TO ArchivedOrders.Order_Archived PARTITION 1

I get the following error:

  ALTER TABLE SWITCH statement failed. Source and target partitions have different values for the DATA_COMPRESSION option.

But they clearly must be the same!  Or at least it seems to me that they must be the same.

Why would Sql Server think that they are different data compressions?

Below is a complete script that works as expected without the error.

:setvar IsArchive ""
:setvar Suffix _Partitioned

CREATE SCHEMA Orders;
GO

CREATE SCHEMA ArchivedOrders;
GO

CREATE SEQUENCE Orders.NextOrderId;
GO

CREATE PARTITION FUNCTION OrderIdPartitionFunction(bigint) AS
    RANGE RIGHT FOR VALUES();
GO

CREATE PARTITION SCHEME OrderIdPartitionScheme AS
    PARTITION OrderIdPartitionFunction ALL TO ([PRIMARY]);
GO

CREATE TABLE [$(IsArchive)Orders].[Order$(Suffix)](
    [OrderId] [BIGINT] NOT NULL DEFAULT(NEXT VALUE FOR Orders.NextOrderId),
    [OtherStuff] [VARCHAR](100) NOT NULL,
 CONSTRAINT [PK_Order$(Suffix)] PRIMARY KEY CLUSTERED
(
    [OrderId] desc
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON OrderIdPartitionScheme(OrderId) WITH (DATA_COMPRESSION=PAGE);
GO

:setvar IsArchive Archived
:setvar Suffix _Archived

CREATE TABLE [$(IsArchive)Orders].[Order$(Suffix)](
    [OrderId] [BIGINT] NOT NULL DEFAULT(NEXT VALUE FOR Orders.NextOrderId),
    [OtherStuff] [VARCHAR](100) NOT NULL,
 CONSTRAINT [PK_Order$(Suffix)] PRIMARY KEY CLUSTERED
(
    [OrderId] desc
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON OrderIdPartitionScheme(OrderId) WITH (DATA_COMPRESSION=PAGE);
GO

ALTER TABLE Orders.Order_Partitioned SWITCH PARTITION 1
      TO ArchivedOrders.Order_Archived PARTITION 1;
GO


PIVOT function Says Invalid Column I'm trying to teach myself to use the SQL Server 2012 PIVOT function, using the old example pubs database from Microsoft, I have written this code..

SELECT [au_lname], [CA], [OR], [IN]

FROM    (SELECT [au_lname], [state], [au_id]
        FROM [authors]
        WHERE STATE IN('CA','OR','IN')) AS Data

PIVOT   (COUNT([au_id]) FOR au_lname IN([CA], [OR], [IN])) as PvtTbl;

The column [au_lname] has a red wavy line under it, and when I try and execute the code, I get the error message:

  "Msg 207, Level 16, State 1, Line 1
  Invalid column name 'au_lname'."

I've tried refreshing the IntelliSence Local Cache (Ctrl + Shift + R) but this didn't help. The column definitely exists in the [authors] table, and this code returns results as expected:

SELECT [au_lname]
FROM [authors]

Please can someone tell me if there an error in my PIVOT function code, or is there something else going wrong?

You can't select the column you're pivoting on.

Change your query to SELECT PvtTbl.*, like this:

SELECT PvtTbl.*

FROM    (
        SELECT [au_lname], [state], [au_id]
        FROM [authors]
        WHERE STATE IN('CA','OR','IN')
        ) AS Data

PIVOT   (
    COUNT([au_id])
    FOR au_lname IN(
        [CA], [OR], [IN])
    ) as PvtTbl;

The other columns from the source table, authors, are automatically added to the columns returned by the PIVOT clause, so output contains the state column, as well as [CA], [OR], [IN].

I'm attempting to infer your desired output.  It looks like you want a count of each au_lname for each state.  The most likely "correct" pivot would be:

USE tempdb;
IF OBJECT_ID(N'dbo.authors', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.authors;
END
GO
CREATE TABLE dbo.authors
(
    au_lname sysname
    , [state] sysname
    , [au_id] int
);

INSERT INTO dbo.authors (au_lname, [state], au_id)
VALUES ('Vernon', 'CA', 1)
    , ('Darling', 'OR', 2)
    , ('Obbish', 'IN', 3)
    , ('White', 'CA', 4)
    , ('Vernon', 'OR', 5)
    , ('Vernon', 'OR', 6);

SELECT  *

FROM    (
        SELECT [au_lname], [state], [au_id]
        FROM [authors]
        WHERE STATE IN('CA','OR','IN')
        ) AS Data

PIVOT   (
    COUNT([au_id])
    FOR [state] IN(
        [CA], [OR], [IN])
    ) as PvtTbl;

The output looks like:

╔══════════╦════╦════╦════╗
║ au_lname ║ CA ║ OR ║ IN ║
╠══════════╬════╬════╬════╣
║ Darling  ║  0 ║  1 ║  0 ║
║ Obbish   ║  0 ║  0 ║  1 ║
║ Vernon   ║  1 ║  2 ║  0 ║
║ White    ║  1 ║  0 ║  0 ║
╚══════════╩════╩════╩════╝


Referential integrity when two FK columns need to match themselves in the same table? It's possible my design is wrong, or there's simply a better way. I'll use a very simple example:

    --------- dbo.Book----------
   |                            |
   |  BookID int identity (1,1) |
   |  ShelfID int FK            |--
    ----------------------------   |
                                   |
    --------- dbo.Row-----------   |
   |                            |  |
 --|  RowID int identity (1,1)  |  |
|   ----------------------------   |
|                                  |
|   -------- dbo.Shelf----------   |
|  |                            |  |
|  |  ShelfID int identity (1,1)|--
 --|  RowID int FK              |
    ----------------------------

Simple enough.

But what if I wanted to assign a Row without the Shelf? Maybe we know the row but not the shelf just yet:

    --------- dbo.Book----------
   |                            |
   |  BookID int identity (1,1) |
   |  RowID int FK              | <-- New
   |  ShelfID int FK NULL       |
    ----------------------------

Is there a better design method for this? I need to make sure that ShelfID and RowID match up in their own respective parent/child tables.

What's the best way? A constraint that calls a function? Seems expensive. Different table structure? I could be missing something super simple obvious.

Elaboration:

If dbo.Shelf has the following value:

[ShelfID]  [RowID]
------------------
 3          1

Then we should expect the dbo.Row table to have a primary key with the value of 1. I want this same referential integrity on the dbo.Book table:

[BookID] [RowID] [ShelfID]
--------------------------
1        1       3

How can I enforce this? Is a function-called constraint the best way? Or is my design wrong?

My requirement is to uphold data integrity if both values are present in the Book table. There must be a correlating relationship in the other two tables. Maybe I've over complicated it and a check constraint that calls a function is the way to go.

I'm aware of being able to set a FK to null. Basically, if ShelfID and RowID both have a value in the main Book table, what is the best way to make sure that RowID and ShelfID match up? ShelfID must exist in the dbo.Shelf table, that one is easy. But the RowID in Book table must be the same parent found in dbo.Shelf.

What is the best way to make sure a ShelfID and RowID don't end up in the Book table that is a false relationship? For example, RowID = 1, and ShelfID = 3, but in the Shelf table, there is no such relationship.

You need two things:

to add an extra foreign key from Book that references Row.

to modify the foreign key from Book to Shelf so it includes RowID and becomes a composite foreign key: (RowID, ShelfID) REFERENCES Shelf (RowID, ShelfID). This is needed in order for the 2 foreign keys to be aligned. Otherwise, you may end up with a Book that references RowID = 1 and ShelfID = 33 while Shelf 33 is referencing RowID = 2.

and additionally - for the the above modification to succeed - you need to add a UNIQUE constraint on Shelf (RowID, ShelfID). Alternatively, you can modify the Shelf primary key.
All the columns will be not nullable, except Book.ShelfID. There is a way to have all columns not null but it would require an additional table.

The tables become:
        --------- dbo.Row-----------
       |                            |
     --|  RowID int   PK            |-----
    |   ----------------------------      ^
    ^                                     |
    |                                     |
    |   -------- dbo.Shelf----------      ^
    ^  |                            |--   |
  FK|  |  ShelfID int PK     UQ1    |  |  |
     --|  RowID int      FK  UQ1    |  |  ^
        ----------------------------   ^  |
                                       |  |
                                    FK1|  ^
        --------- dbo.Book----------   ^  |
       |                            |--|  |
       |  BookID int  PK            |     ^FK2
       |  RowID int      FK1 FK2    |-----|
       |  ShelfID int    FK1        |
        ----------------------------


Reset value each year with a trigger I want to set up a trigger which will increment a value (based on previous record). But this will also reset the value if the year of date_creation is different of the current year.

ref        ref_handlerdate_creation
x_0001 0001          2016-06-16 00:00:00
y_0002 0002          2016-06-17 00:00:00
z_0001 0001          2017-01-01 00:00:00

PS : I also have an id field which is the primary key and increment itself

I already wrote that

BEGIN

    DECLARE next_handler integer;
    DECLARE current_id integer;

    -- select the current highest id
    SET @current_id := (select max(id) from billing_invoice );

    -- select the current highest ref_handler and increment it
    SET @next_handler := (select (ref_handler)+1 from billing_invoice WHERE id = @current_id);

    -- compare the current year ith the year of the creation_date of the previous record.
        -- true : do nothing
        -- false : reset the handler to 0001
     IF( (SELECT EXTRACT(YEAR FROM date_creation) FROM billing_invoice WHERE (id = @current_id)) = YEAR(CURDATE()) ,SET @next_id := @next_handler, SET @next_handler := 0001);

     -- if it is the first record
     IF ISNULL(@next_handler) THEN
         SET @next_handler := 0001;
     END IF;
 -- then set your new row accordingly
 SET NEW.ref_handler = @next_handler;
 SET NEW.ref= concat(NEW.case_id, '_', @next_handler, '_', DATE_FORMAT(NEW.date_creation, '%Y%m%d'));

END

But the statement which extract the the year is failing I do not know why.

But the statement which extract the the year is failing I do not know why.

Change the way it is assigning the the variables. Use SELECT...INTO. Below is an example,

SELECT IF( (SELECT EXTRACT(YEAR FROM date_creation) FROM billing_invoice
WHERE (id = @current_id)) = YEAR(CURDATE()) ,@next_id := @next_handler, @next_handler := 0001) INTO @dummy;

This is not a perfect solution in that it creates the @dummy variable, but I can't comment so I just posted it.


Insert XML File into specific sql table I have the following xml document

<XML_Articles>
  <Articles>
    <ID>12</ID>
    <ReferenceNum>123</ReferenceNum>
    <ArName>الهدف</ArName>
    <EnName>GOAL</EnName>
    <PublicationDate>2016-11-15</PublicationDate>
    <TypeID>15</TypeID>
    <CreatedBy>2</CreatedBy>
    <CreatedOn>2016-11-15</CreatedOn>
  </Articles>
</XML_Articles>

insert them into table by

INSERT INTO dbo.Articles
(ReferenceNum,ArName,EnName,PublicationDate,PublisherID,TypeID,CreatedBy,CreatedOn)
SELECT * FROM OPENROWSET(BULK 'C:\Users\dana_\Desktop\DataBase\Assignments\Assignement 6\Articles.xml',SINGLE_BLOB) AS x

But I get an error:

  The select list for the INSERT statement contains fewer items than the
  insert list. The number of SELECT values must match the number of
  INSERT columns.

This error is fairly self-explanatory.  The number of columns in the 'insert' column list do not match the number of columns in the 'select' column list.  Check the number of columns being returned by your SELECT *  I would suggest that it is rarely a good idea to use SELECT * when trying to insert into another table.  You should always explicitly specify the selected columns.

I played around with your example XML data by putting it in a file and then executed the following

IF OBJECT_ID('tempdb..#Table') IS NOT NULL DROP TABLE #Table
Create table #Table (ID int, ReferenceNum varchar(20), ArName Varchar(20), EnName Varchar(20),
PublicationDate Date, CreatedBy Varchar(200), CreatedOn Varchar(20))
;
WITH XmlData
AS (
    SELECT cast(BulkColumn AS XML) AS XmlData
    FROM OPENROWSET(BULK 'C:\Test\TestXml.txt', SINGLE_CLOB) AS x
    )
insert into #table
SELECT a.value('ID[1]', 'varchar(15)') AS ID
    ,a.value('ReferenceNum[1]', 'varchar(20)') AS ReferenceNum
    ,a.value('ArName[1]', 'varchar(20)') AS ArName
    ,a.value('EnName[1]', 'varchar(20)') AS EnName
    ,a.value('PublicationDate[1]', 'varchar(20)') AS PublicationDate
    ,a.value('CreatedBy[1]', 'varchar(20)') AS CreatedBy
    ,a.value('CreatedOn[1]', 'varchar(20)') AS CreatedOn
FROM xmlData
CROSS APPLY XmlData.nodes('XML_Articles/Articles') AS XMLArticles(a)
GO
select * from #table


MySQL: find full table scan queries Using MySQL 5.6.24.

What is the best way to extract the text of queries using full-table scans? The "Full Table Scans" report in MySQL Workbench doesn't include the complete query (some text is replaced with ...), for example:
SELECT Db AS scope , SYSTE ... sql.dbWHERE ? LIKEdb`

I also tried querying the sys database directly but the same limitations exist there:
select * from sys.statements_with_full_table_scans

Any suggestions?

Thanks!

Queries that do "full table scan" are the ones that don't use indexes. Try to log them in the slow query log using this option log_queries_not_using_indexes

Be careful though that small tables that have frequent queries running against will fill your slow query log files. You may want to enable this option for limited amount of time. To reduce this chance, you may set min_examined_row_limit variable to a reasonable value, depending on your small tables.


indexes are not created on localdb When using localdb and executing this, when browsing the database in sql server object explorer (in visual studio), the index is not created. I have to manually create it to make it appears. Why ?

CREATE TABLE [Instructor] (
    [ID] int NOT NULL IDENTITY,
    [LastName] nvarchar(50) NOT NULL,
    [FirstMidName] nvarchar(50) NOT NULL,
    [HireDate] datetime2 NOT NULL,
    CONSTRAINT [PK_Instructor] PRIMARY KEY ([ID])
);

I don't see any indexes in the Visual Studio object explorer.  But if you ran that code, the index is certainly there.

Check with TSQL:

select *
from sys.indexes
where object_id = object_id('Instructor')

Or connect with SSMS to (LocalDb)\MSSQLLocalDB and check with the SSMS object explorer.


ER Modeling; multiplicity basics - one to many or many to many? Apologies for a basic question, but I’m having difficulties determining the multiplicity constraints for a database I’m trying to design.

Our team undertakes a number of services/projects...each involve (at least one) activity (prototyping, evaluations, design). The database is intended to record details of each project we complete, and which activities were used in a particular project (plus other details).

Now, down to my confusion:

In one sense, it is a one to many relationship (1..1  1..*); each project involves at least one and possibly multiple activities.

But, if considering it from a different perspective, it is a many to many relationship (0..* 1..*); an activity could be in the database that is never used in any project on record, and, the same activity could be undertaken in multiple projects (for example, multiple projects could involve ‘design’).

Which perspective is correct?

Kind regards,
Mitch

I think your design could be stated more formally as:

One company has many projects.
One project belongs to one company.
One project has one or more activities.
One activity has exactly one activity type (prototyping, design).
One activity belongs to exactly one project.

So, if that's true, you have the following tables:

COMPANY
CompanyID
....

PROJECT
ProjectID
....

ACTIVITY_TYPE
ActivityTypeID
Name (e.g. "Prototyping", "Design")

ACTIVITY
ActivityID
ProjectID
ActivityTypeID
StartDate
EndDate
...


Why are there differences in data when comparing table to itself? TSQL I'm comparing two tables that should have the same data.  I've got a couple of different queries that are supposed to return rows that don't match.  However, the queries return rows that DO match.  I'm not sure why.  Here is an example:

select
which = 'TableA',
*
from (
select * from compareTempA
except
select * from compareTempB
) x
union all
select
'TableB',
*
from (
select * from compareTempB
except
select * from compareTempA

) x
order by dateindex, empid, serviceSales, productSales

For some reason I get results like the following:

As you can see these rows appear to match.  I've also tried:

select sum(case when A.productSales = B.productSales then 0 else 1 end) from
compareTempA A
join compareTempB B
on A.dateindex = B.dateindex
and A.empid = B.empid
and A.storeid = B.storeid

To which I get 3011.  I'm baffled because the data is supposed to be the same in both tables.  The only thing I can think of is that I formed the two tables by inserting into each of them in the following way:

insert into compareTempA
select * from someView

And I did the same for compareTempB.  I tried truncating the tables first and reinserting.  Please help!

The only thing that comes to my mind is that you are not using the same data type for the column productSales on both tables.

For example, if your data type on compareTempA is float and on compareTempB is decimal, that's exactly what should happen.

By the way, this happens even if both columns are float, due to the fact that float is not a precise datatype.

I suggest that you change your datatype to a precise one, like decimal.

"Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. The ISO synonym for real is float(24)."
Source: https://docs.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-2017

Similar problems:

Same problem here - https://stackoverflow.com/questions/16149966/comparing-2-float-values-in-sqlserver

Best Pratices Red Gate - https://documentation.red-gate.com/codeanalysis/best-practice-rules/bp023

Another question about floats and precise comparisions - https://stackoverflow.com/questions/10737004/should-we-use-float-as-primary-key-in-sql-server


Two databases same server with different time zones I want to have two databases on same server but want the data in each to reflect the time zone of the client.  Do I need to adjust database parameters or can this be handled with client (JDBC or OCI) settings?

If you just want to store the same data and have it automatically converted to the timezone of the client, that is just a matter of a session level setting with TIMESTAMP WITH LOCAL TIME ZONE Datatype. Example:

SQL> create table t1(c1 timestamp with local time zone);

Table created.

SQL> alter session set time_zone = '+02:00';

Session altered.

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
03-JUN-19 07.05.01.908465 PM +02:00

SQL> insert into t1 values(current_timestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter session set time_zone = '-09:00';

Session altered.

SQL> select * from t1;

C1
---------------------------------------------------------------------------
03-JUN-19 08.05.11.187305 AM

SQL> insert into t1 values(current_timestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter session set time_zone = '+02:00';

Session altered.

SQL> select * from t1;

C1
---------------------------------------------------------------------------
03-JUN-19 07.05.11.187305 PM
03-JUN-19 07.05.36.714308 PM

SQL>

If you really want to store both the timestamp and the timezone offset, then you can use TIMESTAMP WITH TIME ZONE Datatype.


How to configure my.cnf in 5.6.17? On production server(linux machine mysql 5.6.17), I tried to find my.cnf to change the system variables, but my.cnf was not in /etc/my.cnf location. It was in usr/ directory.
Also that file contains only comments like -

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

Please tell me where should I copy my.cnf and how I can change variables like -

innodb_buffer_pool_size = 200M
max_connections = 300

mysql started with option --defaults-file=FILE  Use the specified defaults file

change config.file(my.cnf)


Sql Max value from multiple columns in Computed Column In this article solution 1, it talks about finding the maximum value from many columns. I would like to conduct this in a computed/persisted column. How would I do this?
https://www.mssqltips.com/sqlservertip/4067/find-max-value-from-multiple-columns-in-a-sql-server-table/
create table dbo.TestAmount
(
    Amount1 int,
    Amount2 int,
    Amount3 int,
    MaxValuedata as (select MAX(MaxAmount) FROM (VALUES (Amount1),(Amount2),(Amount3)) AS MaxAmount(LastAmount))
)

May have 10 values in the future, trying to prevent long case statement.

First thing... I'm not advocating THAT you do this... I'm simply showing you HOW to do this. If your table experiences high volume inserts and/or updates, you could see a noticeable performance hit. Using scalar UDFs in computed columns will force all queries against the table to run serially.

Start by creating a scalar function similar to the following...

CREATE FUNCTION dbo.GreatestOfThreeInts
/* ================================================================================================
Scalar function created for the sole purpose of calculating the MaxVal computed column on dbo.Test.
================================================================================================ */
(
    @C1 INT,
    @C2 INT,
    @C3 INT
)
RETURNS INT WITH SCHEMABINDING --, RETURNS NULL ON NULL INPUT --<< use this if NULLs are a possibility..
AS
BEGIN
    DECLARE @MaxVal INT = ( SELECT MAX(x.Val) FROM ( VALUES (@C1), (@C2), (@C3) ) x (Val) );
    RETURN ISNULL(@MaxVal, 0);
END;
GO

Either create your table with a PERSISTED computed column or, if the table already exists, use the ALTER / ADD syntax to add the PERSISTED computed column...

CREATE TABLE dbo.Test (
    C1 INT NOT NULL,
    C2 INT NOT NULL,
    c3 INT NOT NULL,
    MaxVal AS dbo.GreatestOfThreeInts(C1, C2, C3) PERSISTED NOT NULL    -- persist the value so that it doesn't need to be constantly recomputed
    );
GO

CREATE NONCLUSTERED INDEX ix_Test_MaxVal ON dbo.Test (MaxVal) INCLUDE (C1, C2, c3);
GO

Why do I keep saying PERSISTED?... Buy once, cry once... Unless you have a very write heavy usage pattern, you'll be better off computing the values on inserts & updates, than every time you reference the column in a select... Especially if that column is going to be used in a predicate or sorting operation.

Sooo... Let's see it in action...

INSERT dbo.Test (C1, C2, c3) VALUES
    (123,456,789),
    (345,478,123),
    (523,321,852),
    (111,471,951),
    (874,320,357),
    (965,102,478);
GO

SELECT * FROM dbo.Test t ORDER BY t.MaxVal OPTION(QUERYTRACEON 176);
GO

SELECT * FROM dbo.Test t WHERE t.MaxVal >= 800 AND t.MaxVal < 900 OPTION(QUERYTRACEON 176);
GO

Results...

C1          C2          c3          MaxVal
----------- ----------- ----------- -----------
345         478         123         478
123         456         789         789
523         321         852         852
874         320         357         874
111         471         951         951
965         102         478         965

C1          C2          c3          MaxVal
----------- ----------- ----------- -----------
523         321         852         852
874         320         357         874

Hope this helps,
Jason

Edit #1: A BIG THANK YOU to Erik for adding the link, pointing out the fact that using a scalar UDF to compute a column will prevent the optimizer from considering a parallel execution plan... Even when the computed column is persisted. A fact that I actually knew but completely omitted from my initial answer. What I didn't know is the OPTION(QUERYTRACEON 176) thing... Picking up that little nugget, more than covered the cost of admission for me!

Edit #2: Without inviting the religious debate of "NULL vs NOT NULL" column constraints, I'll simply state that my personal "default" is make all columns NOT NULL unless there is a compelling reason to do otherwise... That said, @MartinSmith makes some good points... Including the fact that the OP, by not specifying NULLability, made all columns NULLable. Plus, after the back & forth, I was just curious to see if the RETURN ISNULL(@MaxVal, 0); was doing anything other than irritating people reading the T_SQL...
Short answer... It does not.

The following includes the introduction of a "control" table (no computed column) and NULLable versions of dbo.GreatestOfThreeInts & dbo.Test (dbo.GreatestOfThreeInts_2 & dbo.Test_2)

CREATE FUNCTION dbo.GreatestOfThreeInts_2
/* ==================================================================================================
Scalar function created for the sole purpose of calculating the MaxVal computed column on dbo.Test_2.
================================================================================================== */
(
    @C1 INT,
    @C2 INT,
    @C3 INT
)
RETURNS INT WITH SCHEMABINDING
AS
BEGIN
    DECLARE @MaxVal INT = ( SELECT MAX(x.Val) FROM ( VALUES (@C1), (@C2), (@C3) ) x (Val) );
    RETURN @MaxVal;
END;
GO

CREATE TABLE dbo.Test_2 (
    C1 INT NULL,
    C2 INT NULL,
    c3 INT NULL,
    MaxVal AS dbo.GreatestOfThreeInts_2(C1, C2, C3) PERSISTED   -- persist the value so that it doesn't need to be constantly recomputed
    );
GO

CREATE NONCLUSTERED INDEX ix_Test2_MaxVal ON dbo.Test_2 (MaxVal) INCLUDE (C1, C2, c3);
GO

CREATE TABLE dbo.Control (
    C1 int NOT NULL,
    C2 int NOT NULL,
    c3 int NOT NULL,
    MaxVal INT NOT NULL
    );
GO

CREATE NONCLUSTERED INDEX ix_Control_MaxVal ON dbo.Control (MaxVal) INCLUDE (C1, C2, c3);
GO

And because the 6 rows in my original answer isn't much of a test, the following will load all 3 tables with 1 million rows of test data...

-- clear out any existing data...
TRUNCATE TABLE dbo.Test;
GO
TRUNCATE TABLE dbo.Test_2;
GO
TRUNCATE TABLE dbo.Control;
GO

-- add 1M rows of test data...
WITH
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
    cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
    cte_Tally (c1, c2, c3) AS (
        SELECT TOP (1000000)
            ABS(CHECKSUM(NEWID())) % 9000 + 1000,   -- randomly generate INTs between 1000 and 9999
            ABS(CHECKSUM(NEWID())) % 9000 + 1000,   -- no, I don't have an actual reason for using that specific range...
            ABS(CHECKSUM(NEWID())) % 9000 + 1000    --
        FROM
            cte_n3 a CROSS JOIN cte_n3 b
        )
INSERT dbo.Test (C1, C2, c3)
SELECT
    t.c1,
    t.c2,
    t.c3
FROM
    cte_Tally t;
GO

-- use dbo.Test to insert dbo.Test_2 & dbo.Control so all 3 tables will have the exact same data values...
-- (to compare actual insert performance, use the cte_Tally to load all tables)

INSERT dbo.Test_2 (C1, C2, c3)
SELECT
    t.C1, t.C2, t.c3
FROM
    dbo.Test t;
GO

INSERT dbo.Control (C1, C2, c3, MaxVal)
SELECT
    t.C1, t.C2, t.c3, t.MaxVal
FROM
    dbo.Test t;
GO


XtraBackup fails with my.cnf Percona Configuration After using a percona optimized .cnf file the backup fails
Installing my.cnf
# service mysql stop
(Installed my.cnf)
# rm ib_logfile0
# rm ib_logfile1
# service mysql start

Mysql creates new log files on startup

my.cnf
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208

[client]

# CLIENT #
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]

# GENERAL #
user = mysql
default_storage_engine = InnoDB
pid_file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
basedir = /usr/local/mysql
tmpdir = /tmp

# MyISAM #
key_buffer_size = 32M
myisam_recover = FORCE,BACKUP

# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql_mode = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION
sysdate_is_now = 1
innodb = FORCE
innodb_strict_mode = 1
bind_address = 127.0.0.1

# DATA STORAGE #
datadir = /var/lib/mysql

# BINARY LOGGING #
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 14
max_binlog_size = 100M
sync_binlog = 1

# CACHES AND LIMITS #
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
max_connections = 500
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 1024
table_open_cache = 2048

# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
innodb_buffer_pool_size = 6G

# LOGGING #
log_error = /var/lib/mysql/mysql-error.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log

error log
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

131210 11:53:50 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
131210 11:53:50 innobackupex: Connected to MySQL server
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".

innobackupex: Using mysql server version 5.6.14-log

innobackupex: Created backup directory /var/www/dyntest.dk

131210 11:53:50 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tmp --tmpdir=/tmp --stream=tar
innobackupex: Waiting for ibbackup (pid=6728) to suspend
innobackupex: Suspend file '/tmp/xtrabackup_suspended_2'

xtrabackup_56 version 2.1.5 for MySQL server 5.6.11 Linux (x86_64) (revision id: undefined)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Error: log file ./ib_logfile0 is of different size 268435456 bytes
InnoDB: than specified in the .cnf file 50331648 bytes!
innobackupex: Error: The xtrabackup child process has died at /usr/bin/innobackupex line 2579.

the hint is in last error messages as follows

xtrabackup: innodb_log_file_size = 50331648
InnoDB: Error: log file ./ib_logfile0 is of different size 268435456 bytes

my.cnf implicitly refereced by xtrabackup has innodb_log_file_size = 48M (50331648 bytes), but actual log file size is 256MB (as shown your my.cnf)

I think you should specify my.cnf explicitly like this

xtrabackup --defaults-file=/path/to/my.cnf


CUBE and calculating the number of dimensions if a data presented with 4-dimension in which each dimension is dependent to hierarchical 3-level aggregate like (country, city, street), then we can summarize it into 4096 ways!

we know for cube with n dimension in which none of dimension is
hierarchical we have 2^n summarizing ways, but in this case that each
dimension has hierarchical 3-level aggregate have 4^4=256 ways.

Why does the above statement mention that there are 4096 ways?
if I couldn't state my problem very well because of a flaw in my English usage, please see:

This is my formula, however my answer is very different from 4096!

It seems like the "hierarchical dimension"s example given as (year, month, week) is treated really the same as 3 independent dimensions - all 8 possible combinations are allowed - that implies they are independent/orthogonal.
So if you have 4 of these triplet dimensions, that's the same as 12 independent dimensions.
But in any case, the answer is basically the same.
The number of possible combinations of 0 to m possible choices of m independent dimensions is a sum of the binomial coefficients and Wolfram helpfully even simplifies that for us:
 (https://www.wolframalpha.com/input/?i=sum+m+choose+n+for+n+%3D+0+to+m)
This is where the 8 possible combinations come from for 3 dimensions and shows why that list contains combinations with between 0 and 3 elements.
You can also look at that as a binary choice for each dimension - we know you can either choose to include or exclude a certain dimension, so you have 2 choices for whether each dimension is included or not, so you have 2 choices for the first, 2 for the second etc.  The product of all those is 2 * 2 * ... = 2^n
So for 12 dimensions, that is 2^12 or 4096.
If you treat it as 4 of the fatter dimensions (basically going from binary choices to octal choices), then the number of combinations in the product is 8 for each hierarchy, so you have 8 combinations possible of the first "dimension", combined with 8 combinations of the second "dimension", and so on, giving 8 * 8 * 8 * 8 = 8^4 = 4096 - and you can simply just view this as regrouping of the 2s into groups of 3.
I don't want to be remiss in pointing out that, in practical terms, many times hierarchical dimensions are not grouped in arbitrary ways as if the component dimensions are independent.
After all, they are in a hierarchy for a reason!
I notice even in the example given with (year, month, week), the (month, week) combination is pretty weak - because I am assuming that week is week number within the year.  That aggregating would be for all years, and the weeks don't fit nicely into months, so is probably pretty rare for analysis.
There are many cases of 3-hierarchies where you would only be interested in grouping (d1, d2, d3), (d1, d2), (d1), () - never leaving out a parent - (country, state, city, street, ...) is a good example - a real state entity doesn't really exist as a concept independent of the country-state combination.


How do I open Data Pump Import Wizard in SQL Developer 4.1? How do I find and open the Data Pump Import Wizard in SQL Developer 4.1?

I opened the Help search tool in SQL Developer 4.1 and searched for Data Pump, and found directions that include:

1.) Select View > DBA from the View menu.
2.) Expand a connection in DBA Navigator.
3.) Right click on Import Jobs.
4.) Select Data Pump Import Wizard.

The problem is that, when I expand a connection in what seems to be DBA Navigator (after selecting View > DBA), there is no Import Jobs option, even though there are many other options listed.  I did go to the trouble of expanding every single option including nested submenus, and there still was no Import Jobs option anywhere.

In addition to the embedded help within SQL Developer 4.1, I also googled for instructions, but the results were only for version 3.1, such as this link.

I am trying to import data into an Oracle 11g r2 database using the Data Pump Import Wizard.

So how do I open the Data Pump Import Wizard in SQL Developer 4.1?

View > DBA

Add a Connection

Expand the tree.

Go to the Data Pump node.

Expand, expand again to get to existing DP Import Jobs.

This will show the results of

SELECT
    name,
    fname,
    type
FROM
    (
        SELECT
            job_name name,
            owner_name
            || '.'
            || job_name fname,
            'DATAPUMP_IMPORT_JOBS' type
        FROM
            dba_datapump_jobs
        WHERE
            TRIM(operation) = 'IMPORT'
    )

To create a new one, right-click and use the Wizard.


Where can I find autoextend events? In Oracle, where can I see the history of DBFs autoextending? Or get the timestamp that a DBF last autoextended, and by how much?

I'm not aware of a particular method that records this, the information doesn't appear in file$ or x$kccfe . In general though I wouldn't worry about these events so much as how much free space I have in both the tablespace and the file system. There are alternative methods to monitor this

Updated At Leigh's suggestion

In answer to Gaius comment - but too long for a comment itself.

I think there are 2 basic things to consider for "efficiency" and the results will be system and site dependent. Its worth re-iterating though that I think the efficiency or otherwise of autoextend from a performance viewpoint is way down the list of priorities. (it was just interesting at a quiet time).

First is, overall how efficient do you want the allocation of space to be. The best result will be achieved if you never autoextend but size exactly correctly to begin with :) In general the fewer file extension operations you do the better, but its a declining win.

Second, how much do you want to delay any individual transaction that triggers a file extension event? here the opposite applies, the more small file extensions you have the shorter each individual event will be.

I have some figures and a sample script for others to test/critique etc at http://orawin.info/blog/2012/01/04/proof-by-extension/


SQL Server 2012 patching failure - The language of SQL Server instance MSSQLSERVER does not match the language expected by the SQL Server update I've picked up the support for a SQL Server 2012 instance on a server at our German site and need to apply SQLServer2012SP4-KB4018073-x64-ENU.
The patching is failing with the installer showing error

The language of SQL Server instance MSSQLSERVER does not match the language expected by the SQL Server update. The installed SQL Server product language is German (Germany) and the expected SQL Server language is English (United States).

The server properties general tab (in SSMS) shows Language German (Germany), but the SERVERPOPERTY('LCID') shows 1033 (US_English). The registry entry shows 1031 (German) as the install language (https://www.sqlserverscience.com/basics/localization/language-used-sql-server-binaries/#comment-191)

Trying to use the German patch file SQLServer2012SP4-KB4018073-x64-DEU results in

This SQL Server setup media does not support the language of the OS, or does not have the SQL Server English-language version installation files. Use the matching language-specific SQL Server media; or install both the language specific MUI and change the format and system locales through the regional settings in the control panel

Any pointers as to a way forward so I can apply the patch?
Attempted patching after first suggested fix

Please try to go to Control Panel, Regional Settings and change the "Format" language.  close , re-open it and change format back to yours. Retry the setup of the patch


Comparison in 2 Tables in SQL Hellow Good Afternoon, I have created a visual representation of my 2 tables and here they are.

Before I ask my question I want to explain this both tables. The table tbl_AdMastefile is the Masterfile of my system which will be the basis of comparison from my tbl_GMFormat.

My tbl_GMFormat has 3 important columns and they are Count Date,Gondola Type and No.Of Units but all of them will be used.

Little story about this tbl_GMFormat : Everytime I will import an Excel file in my program and I will fetch those data.

How can I achieve this ?

Let me explain the Image above, I want to compare those data that is same on the following columns Outlet Name and Gondola Type If the data has no match then leave it blank or else compare them.

I hope someone helps me.

If as You explain in parallel question - Combine two Data from 2 Tables using SQL

Your current database MS SQL Server, You can start from:

SELECT
    tbl_ADMasterfile.QuartNo,
    tbl_ADMasterfile.SORG,
    tbl_ADMasterfile.Ace_Code,
    tbl_ADMasterfile.Outlet_Code,
    tbl_ADMasterfile.Outlet_Name,
    tbl_ADMasterfile.RSM,
    tbl_ADMasterfile.KAS,
    tbl_ADMasterfile.Gondola_Type,
    tbl_ADMasterfile.No_Of_Unit,
    tbl_ADMasterfile.Rent_Unit,
    tbl_ADMasterfile.Monthly_Rental,
    tbl_GMFormat.Count_Date,
    tbl_GMFormat.Gondola_Type
    tbl_GMFormat.no_of_units
FROM
    tbl_ADMasterfile
FULL JOIN tbl_GMFormat
     ON tbl_ADMasterfile.Outlet_Name = tbl_GMFormat.Outlet_Name
     AND tbl_ADMasterfile.Gondola_Type = tbl_GMFormat.Gondola_Type

then You can add GROUP BY conditions (with aggregate functions) if necessary, DISTINCT You do not need (at first look)

little more information about JOINs - http://www.w3schools.com/Sql/sql_join_full.asp


Simplest way to check if current user has permission to a MySQL table (from PHP / mysqli) for some desired operation? While using an already established mysqli connection to a MySQL database from PHP, I'm looking for a simple way to check if this connection (i.e. the authenticated user for this connection, if any) has some desired access to a certain table (or at least any access to the table whatsoever).

If it matters, the table in question is in another database than the currently USED one, and I'm trying to access it by the following kind of query:

SELECT * FROM some_other_database_on_the_same_server_as_the_currently_used_database.some_table WHERE id = 1

My research so far has only found the ridiculously complex solution of manually parsing and resolving the depressingly messy raw-string output of the SHOW GRANTS command, which I just cannot accept being the only way of doing this!?

Things I have tried (and failed) so far are the following:

1.
To simply perform the desired operation in question against the table in question, and evaluate the error codes if it fails. To my big surprise, a SELECT query (issued e.g. with the mysqli_query() API) simply returns False if I don't have the appropriate permissions for the table in question, without any error code or error message being reported at all (inspected both manually in the debugger on the connection object and by means of the mysqli_error() API). The problem with this is that False will also be returned if e.g. the queried table doesn't exist at all, so I cannot know from this if it failed because of permission problems or because of something completely different.

2.
To search for some kind of equivalent of the HAS_PERMS_BY_NAME command of Microsoft SQL Server, which I always come up empty-handed with though, consistently just being politely herded back by Google to the extremely clumsy SHOW GRANTS command of MySQL.

So my question is, again:

How can I, with no holds (or hacks) barred, check if my mysqli connection has some desired access (or at least read access if nothing else) to a certain MySQL table?

There really must be some easy way of doing this, right?

Perhaps this can lead to some SELECTs to solve your problem?

For the 'ana_index':

mysql> SHOW GRANTS FOR ana_index@localhost;
| GRANT USAGE ON *.* TO 'ana_index'@'localhost' IDENTIFIED BY PASSWORD '*...'
| GRANT ALL PRIVILEGES ON `test`.* TO 'ana_index'@'localhost'

These tables were populated:

mysql> SELECT * FROM mysql.user WHERE user = 'ana_index'\G
*************************** 1. row ***************************
              Host: localhost
              User: ana_index
          Password: *1E62D0DB2482ED4455102B56D823285C02BAAF8F
       Select_priv: N
       Insert_priv: N
       Update_priv: N
       Delete_priv: N
       Create_priv: N
(etc)

mysql> SELECT * FROM mysql.db WHERE user = 'ana_index'\G
*************************** 1. row ***************************
             Host: localhost
               Db: test
             User: ana_index
      Select_priv: Y
      Insert_priv: Y
      Update_priv: Y
      Delete_priv: Y
      Create_priv: Y
        Drop_priv: Y
       Grant_priv: N
(etc)

mysql.user says what permissions, if any, the user has for all databases.

mysql.db says what permissions the user has for particular database(s), test in this example.


Can I delete SQL Server 2008 R2 "audittrace" (.trc) files This is precaution question :)

When I was configuring SQL Server 2008 R2 database engine, I checked "Enable C2 audits" which I don't really need. From that time it created a lot of files like audittrace20130918235148.trc. I disabled C2 auditing in SQL Server Management Studio by unchecking checkbox.

Are those files really from C2 audit? If so, can I safely remove them without worring about data stored in databases? Again: I do not need audit logs.

Are those files really from C2 audit?

The audit file format is audittraceyyyymmddHHMMSS.trc

After you disable C2 audit using

EXEC sp_configure 'c2 audit mode', 0
go
reconfigure with override

and you are absolutely sure that you dont need the audit files, you can delete the files.

  If so, can I safely remove them without worring about data stored in databases?

The C2 Audit Mode uses SQL Trace to capture audit events, which are stored in trace files i.e. they are not stored in database, unless you have a job that loads the files into a database.

Note : The C2 security standard has been superseded by Common Criteria Certification.


SQL Server: what should be done when a process is blocking another process? I'm an accidental DBA and still learning.

When I see from DMV:s or Activity Monitor that some process is blocking another process, what should be done?

Should I simply kill those processes or is there a way to "release" or maybe preemptively preempt this blocking?

Despite this question having already being answered and the answer accepted, I'm going to try and make a case for the contrary approach...

DO NOT EVER BLINDLY KILL A SPID

Unless you know what work that SPID has done, you have no understanding of the scale of the rollback you're about to initiate.

The worst kind of blocking chains are those which have unexpectedly brought your 24/7 system to its knees. 99% of the time, the query at the head of that blocking chain is going to be a plain old select query gone awry, queueing up any and all write activity behind it. In these circumstances, a KILL will save the day.

The other 1% of the time, that rogue query will be the last step in a long running transaction that will take as long to rollback as it did to get to the stage it's at now. Taking a moment to understand how you got here will 99% of the time be unnecessary. 1% of the time, you could be saving minutes/hours/days of downtime.


How can I prevent having inconsistent data under isolation level serializable in Oracle SQL? I have the following table:

InStores
+--------+---------+-----+
| ProdID | StoreID | qty |
+--------+---------+-----+
| p1     | s1      |  25 |
| p1     | s2      |  70 |
+--------+---------+-----+

and two transactions, T1 and T2.

First comes T1:

declare s number(10);

begin
 SELECT SUM(qty) into s
 FROM instores
 WHERE prodID = 'p1';

 if s > 90 then
    update instores
    set qty = qty - 30
    where prodid = 'p1'
    and storeid = 's2';
 end if;
end;

T1 sees the sum of 95 and substracts 30 from p1, s2 (but doesn't commit yet).

T2 makes exactly the same:

declare s number(10);

begin
 SELECT SUM(qty) into s
 FROM instores
 WHERE prodID = 'p1';

 if s > 90 then
    update instores
    set qty = qty - 30
    where prodid = 'p1'
    and storeid = 's2';
 end if;
end;

which also sees the sum of 95, as T1 hasn't commited yet and also doesn't update yet, as there is a Write Lock from T1.

Now T1 commits and T2 is now able to continue and substracts the value of 30 from p1, s2.

We end up having the value 10 for p1, s2 instead of the expected value of 40, as T2 should have seen the sum of 65 (because of the first update (substraction) from T1) and shouldn't have made that second subtraction.

I know that Oracle SQL works with Snapshot Isolation, which forces all transactions to work with the latest commited values, but how can this problem be fixed? I guess that the solution would be that when T1 is active it should prevent all other transactions from starting, but I don't exactly know how I can achieve that. What can I do?

You're not quite right in what you expect.

When T2 issues its UPDATE, it has the sum, 95, in its local variable 's'.  That is, the T2 query is complete and the T2 'if' statement happens before T1 commits and because the sum is greater than 90 it proceeds to the update.

There is no reason why the local variable 's' would be updated to track the T1 commit.

I think that you should experiment with using SELECT FOR UPDATE NOWAIT in a loop, e.g.

<<do_tr3quart1sta_thing>> declare
   totqty number;
   lock_nowait_exception exception;
   pragma exception_init (lock_nowait_exception, -54);
begin

   <<try_for_lock>> loop
       begin
           select sum (qty) into totqty
           from instores where prodid = 'p1'
           FOR UPDATE OF qty;
           exit try_for_lock if sql%rowcount > 0;
       exception
           when lock_nowait_exception then
               exit try_for_lock;
       end;
       dbms_lock.sleep (2); -- for heaven's sake please don't run these in a tight spin!
   end loop try_for_lock;

   if totqty > 90
   then
       update instores
       set qty = qty - 30
       where prodid = 'p1'
         and storeid = 's2';
   end if;
   commit; -- release the lock!
end do_tr3quart1sta_thing;
/

Don't have experimentation time - I think the NOWAIT will raise exception -54 if it's locked, but in my usual belt-and-suspenders way I'm allowing for the possibility that the SELECT FOR UPDATE will just return zero rows.

Also note that this will let any other exception raised bubble through and blast out.

How in the world can you get T2 to block in this case?  You could run this thing hundreds of thousands of times a second!


I need to know if a JOIN query is possible I have one table like this:

And one like this:

What I want to do is select that "1" from status in the second table if the product_id number matches the number in the query column in the first table and is prefixed by product_id=. category_id and others should be ignored. Is something like this even possible?

I used Magier's example to come up with this:

SELECT oc_url_alias.keyword, oc_product.status, oc_product.date_modified,
REPLACE(query, 'product_id=', '') AS keymatch
FROM oc_url_alias
JOIN oc_product on REPLACE(query, 'product_id=', '') = oc_product.product_id
WHERE oc_product.status = 1;

which outputs exactly what I need.

Thanks for all the help everyone posted.


How to store lots of small text in a SQL Server database? I have a table with a varchar(max) column that stores text ~4-10KB in size. Data is just being added - never modified, it is a log. My problem is that a table grows too large in size - I would like to keep about 1000000 rows in this table.

I am limited to SQL Server Express and hit the limit on database size. Compression is not available in SQL Server Express and I afraid that Filestream may not be appropriate and the number of files would be too big.

What is the best solution in this case?

Because this is a log of sort, and you have space limitations on your database (due to your Express edition) I would recommend either archiving your historical log data away from the database or simply deleting data that is older than a certain elapsed duration threshold. It is not uncommon to have an agreement that history is retained only for a certain time period. Just ensure this is covered in your service level agreement provided it pertains.


SQL logins Audit for SQL Server 2005/2008 For the Security Audit of SQL servers 2005/8 R2,

We are looking for a method which can list us SQL accounts which have not changed password for last X days. For a single login we can do it via LastSetTime in Loginproperty. But for many servers there are more than 80 logins. How should we extract this information for existing logins?

Also, for those we found who have not changed password in last X days, I believe we need to have existing password. Now the problem is, that existing password cannot be found as was set up way back and password change policy was not marked. What should we do in such cases where we need to change the password of more than 80 logins in a go, as we have plenty of servers to perform this activity?

Thanks!

I had thought you would be able to use policy based management but couldn't find a way to get the PasswordLastSetTime property.  It didn't exist in the Login facet.

However what you an do is run the following query to get the PasswordLastSetTime for all SQL Logins on the instance, then use a tool such as Idera's Multi Query or a CMS (Central Management Server) to run it across all of your instances.

SELECT name, LOGINPROPERTY(name,'PasswordLastSetTime')
FROM sys.server_principals
WHERE TYPE = 'S'

Also as stated in the comments by @Reaces if you are a sysadmin or have control server then you can change the password without the old_password.

EDIT:
In response to your comment you can add an additional column to generate the ALTER LOGIN command dynamically.  This does of course assume that you are sysadmin and don't need the OLDPASSWORD keyword added.

SELECT name, LOGINPROPERTY(name,'PasswordLastSetTime'),
    'ALTER LOGIN ' + quotename(name) + ' WITH PASSWORD = ''TemporaryPassword'' ' +
    'MUST_CHANGE, CHECK_EXPIRATION = ON '
FROM sys.server_principals
WHERE TYPE = 'S'

I've also added the MUST_CHANGE clause to force the user to change their password from your generic password the next time they log in.


How to set weights on a Postgres TSVECTOR column? Currently, I'm using Postgres 9.3.5 for full-text search but I don't want to set weight or convert to tsvector on the fly (a lot of articles suggest that, and that just seems like a terrible idea to me). Here's what my table looks like:

CREATE TABLE documents
(
   id serial NOT NULL,
   name character varying(255),
   raw_data text,
   user_id integer,
   active boolean,
   created_at timestamp with time zone,
   updated_at timestamp with time zone,
   search tsvector
)

And here's the query I run to set the tsvector column (search):

UPDATE documents SET search = (setweight(to_tsvector(name), 'A') || setweight(to_tsvector(raw_data), 'B'));

Now, whenever I run a select query, like so:

SELECT name FROM documents WHERE search @@ to_tsquery('some & tokens');

The only results I ever get are if I type in the exact name of the data so for instance, I'd have a hello document so if I use the hello token, I get the result; however, if I type in anything from the raw_data column, nothing shows up. So, for example, I have a row where raw_data contains the sentence "Exhibit B, more information attached". Searching using any of those words comes up with nothing.

I'm trying to do the same thing locally, and it seems to work. The only difference is that I'm running on a windows machine and production is on linux and that the production table is UTF8. Could that make a difference?

Okay so this was dumb of me. The weights work correctly. The issue I was having was because of the forced UTF8 encoding (I simply set "utf8" on the db, didn't rebuild. I set it back to latin1 and it works now).


Adding a foreign key constraint without losing the current data I have one table called users and another table called teachers (the users table has data in it).

I want to add a column to teachers with a foreign key constraint making a reference to a column named id in the users table.

How can I do that without losing the rows contained in the users table?

Why would you lose the rows in the users table when you add a column to it? The rows just get initialized with a null value.  After you place the foreign key constraint, future rows with that column set to non-null get checked against the table the column references.

There are three ways for you to lose rows.

DELETE
TRUNCATE
DROP TABLE


how to change "localhost" in MariaDB I wonder if there is any way to change localhost in MariaDB.
For example, while creating a user in database, it shows
'user'@'localhost'
I would like to know how to change localhost to any other hostname.
For example, I will be able to create a user in database like the following:
 'user'@'mydomain.com'
By the way, I'm very new in MariaDB and SQL server stuff.
Thank you in advance!

The hostname component is the reference to the remote address of the connection. See create user.
Recommend keeping this constrained to a subnet.
Domain based grants while they look good, need reverse DNS setup to resolve this to an IP before allowing a connection. I recommend avoid them and using skip-name-resolve as a configuration option for extra speed and reliability.


Edition of Power BI Report Server We have faced an issue where the Dev team is not able to view their reports. The installations were all done by them and we had to jump to see what is wrong. We doubt they installed the Evaluation version and the 180 days are over. The error states thats the issue however, we still want to check the edition installed.

We were able to find the Version from registry which was Version 1.4.6969.7395 (Build 15.0.1102.235)

How to find the edition of Power BI Report Server?

If you open Report Server Configuration Manager, you should be able to see the edition on the first screen that pops up as indicated below.


If my understanding of BCNF is correct, how did we know that R1 was not in BCNF? Given that R(ABCDE)

  AB->C

  BC->D

  CD-> E

  DE->A

Since, B is the only attribute absent on the right-hand side, we would start with B.

{B}+ = B . Not a CK.

Next, we combine different attribute with B.

{AB}+ = AB = ABC = ABCD = ABCDE. This is a CK.

{BC}+ = BC = BCD = BCDE = BCDEA. This is a CK.

{BD}+ = BD . Not a CK.

{BE}+ = BE . Not a CK.

{BDE}+ = BDE = BDEA = BDEAC. This is a CK.

So, total number of CK/minimal keys are 03 (three).

Since, in some FDs, attributes are not dependent on keys, R is not in BCNF.

(1) Finding the violating FDs:

CD -> E and DE -> A are the violations of BCNF. Coz, in these FDs, attributes are nor dependent on keys.

(3) Decomposition:

Start with any one of the invalid FDs:CD -> E .

(i) Find the closure of CD and decompose:

R1 = {CD}+ = CD = CDE = CDEA = ACDE

R2 = (R - R1) + {CD} = (ABCDE - ACDE) + CD = BCD

Now, we will have to check to see whether both R1 and R2 in BCNF.

  R1(ACDE), R2(BCD)

  AB->C

  BC->D

  CD-> E

  DE->A

I have a question, how did we know that R1 was not in BCNF and needs to be decomposed?

Since, CD->E is already applied in a decomposition, we don't need to use that again. Coz, it will produce the same result.

(1) Finding the violating FDs:

DE->A is violating the BCNF and it is also not applied yet.

(2) Decomposision:

Start with DE->A.

(i) Find the closure of DE and decompose:

R3 = {DE}+ = DEA = ADE.

R4 = (R1 - R3) + {DE} = (ACDE - ADE) + {DE} = CDE.

So, our final BCNF decompositions are,

R2{BCD},
R3{ADE},
R4{CDE}.

You are asking:

  I have a question, how did we know that R1 was not in BCNF and needs to be decomposed?

In the BCNF Decomposition Algorithm, when a relation is decomposed, one should find the dependencies of the subschemas, in this case R1(ACDE) and R2(BCD).

Let’s start from R1. To find the dependencies that hold in R1, one should actually project the original dependencies over the subschema, but, for simplicity, we would consider only those dependencies in which all the attributes are present in the subschema.

So, in R1 the dependencies {C D → E, D E → A} hold, C D is the key, and the algorithm continues by searching if the dependency D E → A violates the BCNF (of course the first one does not violates it since C D is the key of R1).

And it is easy to see that D E → A violates the BCNF since D E is not a superkey of the relation, so that now R1 must be decomposed.

So, the answer to your question is:

  The algorithm knows that R1 is not in BCNF simply by checking if the dependencies that hold in R1 have a determinant which is not a superkey of R1.

From comments:

  After the 1st decomposition, we should calculate the minimal keys again. Am I correct?

We know the key of R1 because of the way in which it is built (as the closure of the determinant of the original dependency CD -> E). We must find the key of R2, instead.

  Which part of my answer needs to be corrected?

Simply cancel the sentence "Since, CD->E is already applied in a decomposition, we don't need to use that again. It will produce the same result.", and continue with "(1) Finding the violating FDs:
DE->A is violating the BCNF and it is also not applied yet..."


Diskbased to in memory tables sql server 2016 ram requirement I am planning to migrate my tables to memory optimized tables. Currently, I have a total of 250 gigabytes database size on disk. If I migrate this to in memory tables, how much physical RAM will I need?

Start with the Books Online page on estimating memory requirements for in-memory OLTP. It goes into deep detail, and here's just a snippet:

  When there is an active workload, additional memory is needed to account for row versioning and various operations. How much memory is needed in practice depends on the workload, but to be safe the recommendation is to start with two times the expected size of memory-optimized tables and indexes, and observe what are the memory requirements in practice. The overhead for row versioning always depends on the characteristics of the workload - especially long-running transactions increase the overhead. For most workloads using larger databases (e.g., >100GB), overhead tends to be limited (25% or less).

Keep in mind that that's only for the in-memory OLTP data - not your query workspace, buffer pool for other objects, AG log caching, etc.


How to get CPU usage in MySQL per user? I want to know is there anyway to get CPU usage per user in MySQL? for example I have 3 different users 'user1', 'user2' and 'user3'. I want to get the average/real time memory usage and CPU usage of these users.I googled a lot but I did not find any suitable tool. Maybe I can get these data from MySQL queries, but I don't know where are they stored.

Some resource limitations: https://dev.mysql.com/doc/refman/5.6/en/user-resources.html and https://mariadb.com/kb/en/library/grant/#resource-limit-options
Switch to MariaDB to get "user stats" (connections, bytes, rows, CPU, etc): https://mariadb.com/kb/en/library/information-schema-user_statistics-table/ and  https://mariadb.com/kb/en/library/user-statistics/


one table for all blob with a one to one table for metadata which link to one or many tables? I'm going to start by saying that I'm not a DBA but i got that role in my current project so I'm doing my best.

The application must be able to store files(attachments, official
documents, etc)
The application is using entity framework 6 (EF6) to query the
database.
Sql server that I'm using (2012) doesn't have the
FileStream/FileTables functionality activated and I have to assume
it will never be activated.
I don't have access to a fileserver.

I'm not the only one that could change the DAL in the application and I know that if you are not careful with how you write the query with EF6, it's by default select * from so while doing the database I made the choice, to be safe, of creating one table for all blob with a identity as primary key and a one to one mapping to another table which contain the metadata (filename, uploaded time, uploaded by, etc).

A file can have relation to many others tables(dataset) and a dataset can have multiple files.

I see two way of design, I'm showing 2 datasets here(notice and noticemaster) but I could have more;

Option 1

Option 2

My question is; which one is a proper implementation? is there an option 3?

Bonus question; did i made a good choice to have 1 table for all files and 1 table for all metadata?

Either of these models look reasonable. The NoticeMasterFile intersection table is not strictly needed since you can get the information through either model. The question is: How do you intend to use the NoticeMasterFile information? Perhaps this change would support some future plans that you have.

Of course, the problem is that looking at a data model does not really reveal the thinking behind the model.

Nonetheless, separating the blob seems like a very good idea to me. If nothing else it protects you when someone runs SELECT * on the FileMeta table.

As an aside and for what it is worth, if you use sp_tableoption to set 'large value types out of row'=1 the blobs will leave a 16-byte key that references the blob pages.  The blob will still be considered part of the table. The separation would allow you to query the other columns of FileMeta without the processing overhead of the blob data.  (This is the default setting.)

Questions: How many documents do you expect to accumulate in your database? How large is the average size? Et cetera?

If your document load will be relatively light, then this should be fine. But remember that every document is included in the backups that you take.

If you think it likely that you will eventually move into the Terabyte size range, you should do some further planning for how to store the documents.

Perhaps:

Consider creating a series of filegroups for the documents as the data grows. At the proper time, set the older filegroups to read only. This protects the data and gives you the option of backing them up less frequently.
Ask your business to reconsider the value of storing documents in the file system.

For what it is worth, we have done it both ways over the years and both ways have their issues. But more systems that were using embedded documents are now using a file share instead.


EXISTS clause in SQL I have following requirement: main_set table has all the ids, user_input table has all user entered ids.

I want result of ids that exist in user_input table. If nothing in user_input table then I want all the ids from main_input table.

Here's what I have so far:

create table main_set as
select 1 id from dual
union all
select 2 from dual
union all
select 3 from dual

create table user_input as
select 1 id from dual
union all
select 2 from dual

select *
  from main_set ms
  where exists (select null
                        from user_input ui
                       where ui.id = ms.id)

select id from user_input
union all
select id from main_set where not exists(select * from user_input);

--edit based on your comment

select *
from main_set
where id in(select id from user_input) or not exists(select * from user_input)

or, seeing as you mention performance, perhaps you should try a join as an alternative:

select main_set.*
from main_set left outer join user_input on(user_input.id=main_set.id)
where user_input.id is not null or not exists(select * from user_input)


Error: Unknown character set: 'ascii' When trying to import a database from a .mysql file, I get an error:
ERROR 1115 (42000) at line 24: Unknown character set: 'ascii'

The part the error occurs at is from columns using ASCII:
`column` varchar(64) CHARACTER SET ascii NOT NULL

I checked in /usr/share/mariadb/charsets/, and ascii.xml is there. ASCII is listed in the Index.xml as well. However, when looking at the database it's not:
MariaDB [db]> show character set;
+---------+------------------+---------------------+--------+
| Charset | Description      | Default collation   | Maxlen |
+---------+------------------+---------------------+--------+
| big5    |                  | big5_chinese_ci     |      2 |
| latin1  |                  | latin1_swedish_ci   |      1 |
| latin2  |                  | latin2_general_ci   |      1 |
| ujis    |                  | ujis_japanese_ci    |      3 |
| sjis    |                  | sjis_japanese_ci    |      2 |
| tis620  |                  | tis620_thai_ci      |      1 |
| euckr   |                  | euckr_korean_ci     |      2 |
| gb2312  |                  | gb2312_chinese_ci   |      2 |
| cp1250  |                  | cp1250_general_ci   |      1 |
| gbk     |                  | gbk_chinese_ci      |      2 |
| utf8    |                  | utf8_general_ci     |      3 |
| ucs2    |                  | ucs2_general_ci     |      2 |
| utf8mb4 | UTF-8 Unicode    | utf8mb4_general_ci  |      4 |
| utf16   | UTF-16 Unicode   | utf16_general_ci    |      4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci  |      4 |
| utf32   | UTF-32 Unicode   | utf32_general_ci    |      4 |
| binary  |                  | binary              |      1 |
| cp932   |                  | cp932_japanese_ci   |      2 |
| eucjpms |                  | eucjpms_japanese_ci |      3 |
+---------+------------------+---------------------+--------+
19 rows in set (0.000 sec)

At this point, I don't know what's wrong or how to get the ASCII character set detected and working.
I am using wodby/mariadb with tag MARIADB_TAG=10.5-3.9.5.

The issue was that I had the following in my docker-compose.yml:
command: >-
  mysqld
    --character-sets-dir='/usr/share/mariadb/charsets'
    --character-set-client-handshake=FALSE

This was wrong, as the directory was actually /usr/share/mysql/charsets.


How to create database/tablespace/schema and users for quickstart developers on Oracle 12? Background

I need to create a contained development space for a new application. This requires a user account to connect with all permissions, such as create/drop tables and stored procedures. Following the principle of least privilege, all of the application's database operations will be wrapped in stored procedures, so another application user account is required with only execute permissions to those stored procedures (itemized in production; all in development).

I've got a fresh Oracle 12c installation on Windows Server 2008 R2 Enterprise with available connections to what I think is called a database named 'orcl'. Now I can start sqlplus from cmd.exe

sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 8 09:49:03 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

How do I functionally accomplish the following?

I've provided the MySQL console (mysql -u root -p) equivalent instructions under each of my requirements.

new database/schema created

CREATE DATABASE appdb;

new dbo user created for DDL

CREATE USER 'appdb_owner'@'localhost' IDENTIFIED BY 'c0mpl3xpw0rd';
GRANT ALL PRIVILEGES ON appdb.* TO 'appdb_owner'@'localhost';

new test user for application execution

CREATE USER 'appdb_user'@'localhost' IDENTIFIED BY 'simplerpw0rd';
GRANT EXECUTE ON appdb.* TO 'appdb_user'@'localhost';

Failures

I'm very familiar with MySQL, but totally new to Oracle and sqlplus. I think some of my confusion stems from the distinction between a database, tablespace, and a schema from my lesser familiarity with DB2. From what I've seen on the 11.1 create database page, it looks unnecessarily complex (like a new installation) for what I'm trying to achieve, so I think what I am looking for instead is a new schema that will simply provide isolated access to the data. But when looking at the 10.2 documentation for schema creation, it says this:

  This statement does not actually create a schema. Oracle Database automatically creates a schema when you create a user (see CREATE USER). This statement lets you populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions.

But when I try to create a user from the 10.2 documentation

sqlplus / as sysdba
SQL>CREATE USER appdb_owner IDENTIFIED BY c0mpl3xpw0rd;

I get the following error

  ORA-65096: invalid common user or role name

Which led me to an answer about distinguishing between common names, which I gather have access to all schemas, which is not what I want. So I'm not sure which comes first, the chicken or the egg?

I found another answer that said I needed to connect as SYSTEM, but sqlplus / as SYSTEM resulted in an error without even prompting for the password.

You already have a database, so you don't need the 'create database'.
Since you are working with a 12.1 database, you need to throw out your 10.2 docs and go to the 12.1 doc set.
When you create a user, don't use that @localhost construct.  Your simple create user foo identified by bar is the correct syntax, but as @a1ex07 pointed out, you need to be attached to the pluggable database, not the container.  This is a drastically new architecture for Oracle, beginning with 12c.  You need to spend a bit of time with the 12c Concepts manual (see my link above), especially Part VI - Mutlitenant Architecture.


Is there any scenario where a exported (.sql) database dump could be bigger in size, than the database itself? I'm a developer in a local company, and I'm currently working on a project which involves migrating a website to my company's server, from some other hosting provider.

For some reason this other company won't give us access to the cPanel, but rather provided us with the project (files) and an exported db script(.sql).

Now I'm having issues after importing the db because it lacks few tables.
I actually think the db export is okay itself. While the script file is rather large ~1.5GB, the db after importing is only ~1GB.

Q:

I'm using cPanel & phpMyAdmin, and now I'm wondering if there's any scenario where the db SQL script file could be larger than the database itself, or might there be a limitation within cPanel or phpMyAdmin regarding the size, which would cause the import to skip/stop or anything similar on about 1GB?

I always thought (and still think it makes more sense) that the db could be only bigger than the script file, due to how the SQL scripts are built, lack of indexes etc.

Just wanted to check if this is possible, before I start inspecting the 1.5GB sql file myself and see wether tables are missing there too.

Thanks PS couldn't find similar questions.

Lots of cases...

VARCHAR needs 1 or 2 extra bytes for the length; as text it needs two quotes, a comma, and possibly some escape characters.
The BIGINT 123456789012345 takes 8 bytes in the table, but 16 in the dump (15 digits, plus a comma).
A NULL value dumps as 5 bytes, regardless of what the datatype needs, which is probably 0.
DECIMAL(11,2) with -123456789.12:  5 bytes in the table -> 14 bytes in the dump
(and the DATETIME/TIMESTAMP example given in a Comment)

Going the other direction:

BIGINT 1:  8 bytes -> 2
CHAR(100) 'x':  100 characters -> 4 bytes.  (This example varies with charset, version and engine.)
DECIMAL(11,0) with 0:  4 bytes in the table -> 2 bytes (0,) in the dump

Etc.


Ideal way to start 'Database Server' in PostgreSQL 11 PostgreSQL version : 11.2
OS : RHEL 7.5

I am new to PostgreSQL.
I would like to know which is the ideal way to start 'Database server' . In Oracle world, we call this starting the instance.

Should I be using Option A or Option B ?

Option A. As root user , run

# systemctl start postgresql-11

Option B. As postgres user, run

$ postgres -D /var/lib/pgsql/11/data

NB: For Option B, PostgreSQL 11's official doc mentions a different directory (/usr/local/pgsql/data) as shown below . Maybe the doc not updated.

$ postgres -D /usr/local/pgsql/data

https://www.postgresql.org/docs/11/server-start.html

I would prefer option A.

Using systemctl ensures the service is properly (re)started when the computer is (re)started.

As far as the location of the data directory is concerned: the manual shows some defaults. The actual location of the data directory is essentially driven by the packager that built the RPM (or whatever package the OS uses) and the standards the distribution expects or uses. You could also put it under /databasedata/postgres/pg11/data if you want, or /very/important/data/postgres


How to upload multiple csv files to sql server without constrains I backed up an old SQL Server DB with .CSV files for each table. The old DB is now gone; the .CSV files are all I have.

Now I want to create new DB and upload all those csv file (my old db removed) .

I looking for a way to upload multiple csv files via the sqlServer gui and without any constrain .

I want to disable the constrain because if not the sql will tell that I a problem with the FK

I thought to running this command EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" but I think it not help me because I need to running it before I upload all the csv files , and before I upload them I not have any table in my new DB
thank you all.

Using SSMS, you can run the "Import Data" command (right click DBv2, go to "Tasks", and select "Import Data...").

This will allow you to import one of your .CSV files into a new table. The column names, datatypes, and sizes will be set by default from what's in the .CSV file; you can view and edit the actual CREATE TABLE command before you run the import. This would let you set up IDENTITY columns and primary keys immediately. If you're using an identity column, do make sure you tell it to keep the existing values.

Once all tables are loaded, you can go back through and set up the foreign key relationships.

Alternately, if you're careful about the order in which you load the tables, you could even set the foreign key constraints up in the CREATE TABLE statement, as long as the referenced tables have already been loaded.

Note that this will be a somewhat tedious process, if you have a large number of tables to load. You would need to set each one up individually, manually.

Most of the ways I can think of to automate this would require that the data already be in a database, instead of in a number of .CSV files. Of course, you've said you want to use the GUI tools anyway.