Database Design Review - How to model a business domain about posting groups? I am building a website as a personal project just for some experience in a new web framework and want to make sure I model my SQL database in a good way with the appropriate relationships before building it out and getting myself in too deep. I'm coming over from using MongoDB so this will be my first application in which I have to model the SQL database.

This application will allow users to join and create groups for discussion, add categories to the groups and then post topics to a specific category in a group.

My application will have four main things: Users, Groups, Categories and Topics (assuming no replies to topics, just a simple posting for users to read).

My current advance

This is what I have so far (FK = Foreign Key):

User

id
username
password

Group

id
title
description
owner (FK user-id)

Category

id
title
group (FK group-id)

Topic

id
title
author (FK user-id)
category (FK category-id)

Membership

id
group (FK group-id)
user (FK user-id)

Responses to comments

The rules are:

A user can own/create multiple groups and can join multiple groups
A user can create multiple categories within the group that they own/created
Members of a group can create multiple topics within a category
There can't be duplicate categories within the same group

I am in no capacity a database expert and took one course on database a year ago so any feedback is appreciated

I think it looks about right for the defined business rules that you name... There's probably more to it but your structure seems pretty much whats needed in this case.

I would probably separate the Categories altogether, and then add a new table to store categories per group that way you dont end up with a bunch of duplicated categories on that table.

Something like:


SELECT a.*                                -- select all rows
FROM tableX  a                            -- from table
WHERE a.tuser = 1                         -- where either tuser = 1
   OR NOT EXISTS                          -- or there is no other
      ( SELECT *                          -- row
        FROM tableX  b                    -- in the table
        WHERE b.tuser = 1                 -- with tuser = 1
          AND b.id = a.id                 -- and same "id",
          AND b.id_user = a.id_user       -- same "id_user"
          AND b.id_word = a.id_word       -- and same "id_word"
      ) ;


Derby/SQL - How to increase performance when left joining and retrieving varchar columns Through trial and error I found that if I do a left join such as:

SELECT
    firsttable.id,
    secondtable.id,
    secondtable.varcharColumn
FROM
    firsttable
LEFT JOIN
    secondtable
ON
    firsttable.id=secondtable.id

The performance is terrible for larger tables. If I EITHER remove secondtable.varcharColumn as a column in the resultset OR change the type then the performance is about an order of magnitude better. The column is a varchar 255, so it should not make that much of a difference. If I change the type of column to integerColumn or dateColumn than I also see an order of magnitude difference in the performance. Even a varchar of a few characters seems to result in the same performance degradation.

Please note that the WHERE clause is NOT the issue here. I've omitted it because the issue is the same whether or not a WHERE clause is included. The issue has to do with the type of data in the column and not the quantity of data returned. I could also use LIMIT 1000 and have the very same issue.

Indexing the column will not help as the column is not used for joining. It is also not part of the where clause if there was one.

Any suggestions on how to improve the performance would be greatly appreciated!!

The lack of a WHERE clause or an ORDER BY clause probably means that your query is not using an index (and is most certainly selecting all rows in the table).

So, I'd suggest:

Add a "WHERE" clause
Add an "ORDER BY" clause
Make sure that you have appropriate indexes on the two tables for the ids and the columns in the WHERE and ORDER BY clauses.
Look at the query execution plan to make sure that your indexes are used.


mongodump command with timestamp query When I execute the below query in mongodb shell, it gives me the number of records updated after the mentioned timestamp.

db.getCollection('oplog.rs').find({"ts":{"$gt":Timestamp(1582823397,2)}})

I wish to execute a similar query with mongodump command. Below is how I'm trying to do it.

query.json
{ "ts" : { "$gt" : Timestamp(1582823397,2) } }

mongodump -h 127.0.0.1:27018 -d local -c oplog.rs --queryFile=query.json -o - > D:/MongoDB/Backup/1582823
397_2_oplog.bson

The above command doesn't work and throws the below mentioned error.

Failed: error parsing query as Extended JSON: invalid JSON input. Position: 19. Character: T

Which seems valid as the query.json doesn't follow the JSON rules.

If I change the query.json file as below.

query.json
{"ts":{"$gt":"Timestamp(1582823397,2)"}}

The query doesn't return any records.

All of the above is a part of a shell script that is supposed to give me incremental dumps.

Reference of the script is taken from here. Referred this question as well.

I'm open to any other suggestions of getting incremental backups of MongoDB.

Adding in response to Wernfried's comment.

{
    "ts" : Timestamp(1582823446, 1),
    "t" : NumberLong(4),
    "h" : NumberLong(0),
    "v" : 2,
    "op" : "i",
    "ns" : "uis.healthCheck",
    "ui" : UUID("60d2d4de-48a3-46ec-a948-6fcb676ba52f"),
    "wall" : ISODate("2020-02-27T17:10:46.010Z"),
    "o" : {
        "_id" : 2,
        "status" : "Success2"
    }
}

I was able to achieve what I wanted by the suggestions given in the comments by @WernfriedDomscheit and @prasad_

Now my mongodump command is as below

mongodump -h 127.0.0.1:27018 -d local -c oplog.rs --query "{\"ts\":{\"\$gt\":{\"\$timestamp\":{\"t\":${TIMESTAMP_LAST_OPLOG_ENTRY},\"i\":${INC_NUMBER_LAST_OPLOG_ENTRY}}}}}" -o - > ${OUTPUT_DIRECTORY}/${TIMESTAMP_LAST_OPLOG_ENTRY}_${INC_NUMBER_LAST_OPLOG_ENTRY}_oplog.bson 2>> "$LOG_FILE"

"TIMESTAMP_LAST_OPLOG_ENTRY" and "INC_NUMBER_LAST_OPLOG_ENTRY" are being fetched from the previous '.bson' file.

Thanks,
Rahul Paryani


Alternative to ORDER BY GROUP BY to get first matches per ForeignKey Since there is so much talk and articles about this online. I simply don't seem to have enough SQL knowledge to solve this seemingly simple problem.

I have a table with layered data for a Price per ProductModelID/ProductVariantID (BundledProductID isn't important in this situation). I want to get the Highest and Lowest Price (in the figure below the lowest) per ProductModelID.

Obviously knowing that GROUP BY will just grab first hits (likely based on the BTREE?) so it would get ID's 1,4 and 6 instead of the blue marked ones I want.

Is there any sensible way of achieving this and still profiting from indexes (e.a. not suffering greaty on performance on temp tables and such)?

I'm running 10.2.13-MariaDB Homebrew.

The query:

-- SELECT * FROM (
    SELECT * FROM DomainProductPrice
    WHERE DomainID = 1 #AND ProductVariantID IS NULL
    ORDER BY ProductModelID, Price
-- ) a GROUP BY ProductModelID

Table creation:

CREATE TABLE `DomainProductPrice` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`DomainID` int(11) DEFAULT NULL,
`BundledProductID` int(11) DEFAULT NULL,
`ProductModelID` int(11) DEFAULT NULL,
`ProductVariantID` int(11) DEFAULT NULL,
`PriceGroupID` int(11) DEFAULT NULL,
`TaxGroupID` int(11) DEFAULT NULL,
`Currency` int(11) DEFAULT NULL,
`Price` double NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Table test data:

INSERT INTO `DomainProductPrice` (`id`, `DomainID`, `BundledProductID`, `ProductModelID`, `ProductVariantID`, `PriceGroupID`, `TaxGroupID`, `Currency`, `Price`)
VALUES
    (1, 1, NULL, 1, NULL, 1, 1, 1, 24),
    (2, 1, NULL, 1, 3, 1, 1, 1, 23.5),
    (3, 1, NULL, 1, 4, 1, 1, 1, 23.75),
    (4, 1, NULL, 2, NULL, 1, 1, 1, 68),
    (5, 1, NULL, 2, 5, 1, 1, 1, 66.25),
    (6, 1, NULL, 3, 9, 1, 1, 1, 101),
    (7, 1, NULL, 3, 10, 1, 1, 1, 99.5),
    (8, 1, NULL, 1, 2, 1, 1, 1, 25);

Update 1:

This would be a solution, but it's probably super bad with tons/milions of entries?

SELECT
    p.ProductModelID, p.PriceGroupID, TaxGroupID, Currency,
    (SELECT Price FROM DomainProductPrice WHERE DomainID = 1 AND ProductModelID = p.ProductModelID ORDER BY Price DESC LIMIT 1) AS Highest,
    (SELECT Price FROM DomainProductPrice WHERE DomainID = 1 AND ProductModelID = p.ProductModelID ORDER BY Price LIMIT 1) AS Lowest
FROM DomainProductPrice p
WHERE p.DomainID = 1
GROUP BY ProductModelID

In MariaDB, you can use a window (ranking) function like ROW_NUMBER() or RANK():

with di as
  ( select *,
           rank() over (partition by ProductModelID
                        order by Price)
             as rnk
    from DomainProductPrice as d
    where DomainID = 1
  )
select *
from di
where rnk = 1 ;

Tested at dbfiddle.uk


Still i am not satisfying with Logfile Shrinking in SQL Server? Already i asked this question but still i'm not getting the very clear answer.
In my server holds more than 20 live databases in that my logfile(.ldf) is use to grow more than 5 GB per day.

Using the below script i'm shrinking my logfile size on day-to-day basis.But after reading the articles i'll come to know my travel is in wrong way.Know i need to start the Logfile shrinking in a good way.

USE ABC;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE ABC
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (ABC_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE ABC
SET RECOVERY FULL;
GO

My Database Recovery Model is 'FULL' because if i'm keeping my database recovery model in simple might be data loss can happen for that only i'm keeping my database in the Full recovery model.

Doing the above Querying is a bad idea. Here i need expertise suggestion to keep my database logfile in a best way.As well as i need to avoid the log file growing for each and every day.

In order for you to manage the transaction log  size of your database(s) which is in full recovery mode you should be taking regular and frequent transaction log backups. The frequency should be according to RPO and RTO defined

Do NOT change the recovery mode and shrink the log. You stated in your question that you didn't want any data loss by doing this you would lose point in time recovery. In the full recovery mode this is done by taking regular transaction log backups so that in the event of a failure, the backups can be used to restore the database to a point in time.

By changing the recovery mode, you are breaking the log chain which will prevent your ability to restore the database to a point in time.

Log backups will free space in the log by marking virtual log  inactive or reusable. This allows space within the log to be re-used and prevents growth (if the log is big enough initially).

You should also be aware that putting the database in the simple recovery mode will not prevent any transaction log growth. The log will record all active transactions and will grow accordingly. The difference is that in the simple recovery model, the virtual log files are marked for re-use after a database checkpoint.

Here's a couple of links for more information on the transaction log:-

Overview of the transaction log:-  http://msdn.microsoft.com/en-us/library/ms190925.aspx
Transaction log backups:- http://msdn.microsoft.com/en-us/library/ms191429.aspx


Transaction wrap around warning WARNING:  database "dbname" must be vacuumed within 5463629 transactions
  HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
  You might also need to commit or roll back old prepared transactions.
  VACUUM

I have run vacuumdb --full -h 127.0.0.1 -U postgres dbname
It spits out that message galore.

I have checked active queries and there is nothing active.

It is a cluster with many DBs on Linux (postgres 9.5.10) but only this one small DB is giving this warning.

Any suggestion would be helpful.

Don't use "--full".

Do you get any messages, other than the one you show, while vacuuming?

What is the output of select datname, age(datfrozenxid) from pg_database;


Non-sequential Primary Key performance in PostgreSQL 9.3 Due to requirement to support distributed environment, each of my clients has been assigned unique node id, it appends the node id with running number as primary key and stores at local database. E.g.

  Client A node id = 200, first row in a table will have primary key
  200,000

  Client B node id = 100, first row in a table will have primary key
  100,000

These records then replicate to centralized database. Since primary key at centralized database is not in sequence, will it cause any serious performance issue when data size getting bigger?

Possible sequence of inserting new data at centralized database:

200,000
100,000
100,001
200,001
300,000
100,002

This may cause a big performance in SQL Server with table is clustered along the PK. However, will this happens in PostgreSQL 9.3?

Notes:

I can't use composite keys as it does not play well at my presentation layers.
The 3 digits running number is just a simplified example, real
running number will be much bigger and sufficient.

I have 3 solutions for you:
Direct reference of sequence and using concat
One possible solution is to reference the seqence in insert statement directly and prepend your node-id. A similar question including answer you can find here:
https://stackoverflow.com/a/17925601/4206293
Using a UUID
Another possible solution is, if you don't need you node-id in the primary-key field, you can use the uuid-ossp extension which provides the type uuid and the functions to generate uuids: http://www.postgresql.org/docs/9.4/static/uuid-ossp.html
Use a trigger for these solutions
For both solutions: you can use a trigger to set the primary key.
Example:
-- table
CREATE TABLE test(
   id character varying(10) NOT NULL,
  "name" character varying,
   CONSTRAINT idx_pk PRIMARY KEY (id)
);

-- seqence
CREATE SEQUENCE test_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Create Function
CREATE OR REPLACE FUNCTION insert_trigger()
RETURNS TRIGGER
AS $$
BEGIN
   NEW.id := '100' || nextval('test_seq')::TEXT;
   RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

-- add Trigger
CREATE TRIGGER insert_table_trigger
BEFORE INSERT ON pkTable
FOR EACH ROW EXECUTE PROCEDURE insert_trigger();

If you now insert a value in the table
INSERT INTO test (name) VALUES ('test text');

you get your primary key '1001'
Using MINVALUE and MAXVALUE
Another way is you use the MINVALUE and MAXVALUE of SEQUENCE to define an numeric space:
CREATE SEQUENCE node100_seq
  INCREMENT 1
  MINVALUE 100000000000
  MAXVALUE 100999999999
  START 100000000000
  CACHE 1;

CREATE SEQUENCE node200_seq
  INCREMENT 1
  MINVALUE 200000000000
  MAXVALUE 200999999999
  START 200000000000
  CACHE 1;


How to obtain SQL feature list from the command line Is there a command at the CLI or in PowerShell that will list the components installed for SQL?  I'm looking for something like the Feature List you can get from running the discovery report from Tools in the SQL Server Installation Center program option.

I'm running SQL Server 2012 on the CORE version of Windows Server 2008 R2 Enterprise.  I've searched around the Net, but haven't found anything useful.

The components for SQL Server 2012 are divided by server and then management tools. You can get the server level components by using a PowerShell command like:

Get-Service *SQL*

To get the management tools would require either registry search for the uninstall list or you can query the WMI class win32_product:

get-wmiobject win32_product |
where {$_.Name -match "SQL" -AND $_.vendor -eq "Microsoft Corporation"} |
select name, version


Can I recover a SQL database from Standby/Read-Only without a standby file? Is there any way to recover a SQL database from Standby/Read-Only mode if the "standby file" is missing?

(I see some references calling this a TUF file in a log-shipping scenario, but in my case, it was just a manual restore and the standby file had a BAK extension.)

Doing a "RESTORE DATABASE foo WITH RECOVERY" results in a fatal error:

During startup of warm standby database 'foo', its standby file
('path\filename.bak') was inaccessible to the RESTORE statement.
The operating system error was '5(Access is denied.)'. Diagnose the
operating system error, correct the problem, and retry startup.

The restore was done a while ago, and unfortunately the standby file was in the backup folder and got removed by our folder cleanup routine. No copy of the file exists on any other media. The original backups that were used to restore to this point are also long gone.

Fortunately this isn't a big deal for us (it was a temp restore), but I couldn't find any other useful references out there on this question, other than "don't remove that file".

If it came down to it, I suppose I could script out the entire database, or use SSIS to copy all objects to a new database container, since I have a read-only copy of the db in front of me.

Any way to fix this issue? I certainly don't expect that I'd be able to flip it back to "restoring" and continue doing subsequent restores or anything, I'd just like to force it into normal online mode in its current state, so that I can do things like change permissions, or change the recovery mode, or take a new full backup or something.

Turns out it can be done if you use CONTINUE_AFTER_ERROR

RESTORE DATABASE foo WITH RECOVERY, CONTINUE_AFTER_ERROR

I did still get a warning when I tried it but then did a CHECKDB and received no errors.

RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.
RESTORE DATABASE successfully processed 0 pages in 4.180 seconds (0.000 MB/sec).
Msg 3441, Level 17, State 1, Line 13
During startup of warm standby database 'foo' (database ID 46), its standby file ('C:\MSSQL\Backup\foo_standby') was inaccessible to the RESTORE statement. The operating system error was '2(The system cannot find the file specified.)'. Diagnose the operating system error, correct the problem, and retry startup.

Given the warning I'm not 100% certain if I would try it on a production database where I didn't have a good backup.  Given that it's a temp restore it might be worth giving it a shot.


Updating multiple rows with different values in one query I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand.

For instance, two updates into 1 query:

UPDATE mytable SET fruit='orange', drink='water', food='pizza' WHERE id=1;

UPDATE mytable SET fruit='strawberry', drink='wine', food='fish' WHERE id=2;

I don't understand what the CASE WHEN .. THEN ... END works and how to use it.

Wonder if someone could help me on this.

UPDATE mytable SET
    fruit = CASE WHEN id=1 THEN 'orange' ELSE 'strawberry' END,
    drink = CASE WHEN id=1 THEN 'water'  ELSE 'wine'       END,
    food  = CASE WHEN id=1 THEN 'pizza'  ELSE 'fish'       END
WHERE id IN (1,2);

Personally, using CASE WHEN THEN END looks clumsy.

You could code this using the IF function.

UPDATE mytable SET
    fruit = IF(id=1,'orange','strawberry'),
    drink = IF(id=1,'water','wine'),
    food  = IF(id=1,'pizza','fish')
WHERE id IN (1,2);

Give it a Try !!!

CAVEAT : CASE WHEN THEN END is only handy when dealing with multiple values (more than 2)


Postgres jsonb vs composite type performance differences What considerations are involved in choosing between a jsonb column and a composite type column of the same structure?

For example, consider a column like that used in the Postgres documentation:

CREATE TYPE inventory_item AS (
name            text,
supplier_id     integer,
price           numeric
);

What are the tradeoffs involved between this approach vs a jsonb column mirroring this structure?

For example, I suspect that the composite type won't require storing the key names for every record, whereas the jsonb type would require this.

It's not really a question of performance,

Composite Types are strongly typed meaning you have to define their shape.
JSON is weakly typed.

Moreover, JSON has a use case, but with a composite type you almost never want to use them because it's almost always better normalizing them into their own relations. There is an exception to that though -- when you're going to create a whole library around a set of data then composite types can make everything and nicer. For example stdaddr in PostGIS is one such example. That type represents a physical address and multiple things can take that type (like the PostGIS geocoder).

So you can do

CREATE TABLE foo ( foo_id int, address postgis.stdaddr );

now you can get the geocoded information relatively easily and you only have to pass around one value rather than 15.


SQL Server Basic Availability Group: config-only replica? In SQL Server Basic Availability Groups docs, I found this interesting line:

Basic Availability Groups for SQL Server 2017 on Linux support an additional configuration only replica.

What is a configuration only replica?
Why is it only supported on Linux?

It is just the configuration data no user data - so probably of limited use
There is more information available here :-
https://docs.microsoft.com/en-US/sql/t-sql/statements/create-availability-group-transact-sql?view=sql-server-ver15
to quote :-
CONFIGURATION_ONLY Specifies that the primary replica synchronously commit availability group configuration metadata to the master database on this replica. The replica will not contain user data. This option:
Can be hosted on any edition of SQL Server, including Express Edition.
Requires the data mirroring endpoint of the CONFIGURATION_ONLY replica to be type WITNESS.
Can not be altered.
Is not valid when CLUSTER_TYPE = WSFC.
For more information, see Configuration only replica.


SQL Server Incident - How to audit A client of ours has doubts that a user erased certain information from the database they are using. I know that the database can be audited by having SQL Server Profiler enabled, but in this case, there wasn't any tool enabled to do auditing.

My question is: is there a way to know if some user has committed a transaction on the database without having profiler enabled? Is there any forensic task that can be done to accomplish this?

Thanks.

If you have the full and transaction log backups, you can use a log reading tool like Quest Litespeed or ApexSQL Log Reader. (Disclaimer: I used to work for Quest.) Those read the full backup, then the logs, in order to build a chain of what happened. You can search for transactions by syntax, table name, etc, and generate undo scripts.

Here's the catch, though: if it was a shared login, like a SQL login where multiple people knew the password, you're going to have a tough time pinning down who did it.


SQL Server: Invalid Log Scan Number (...) passed to log scan in database (...) is not valid We are getting a large number of the below errors on one of our production databases. CheckDB is completely clean.

We thought it was related to replication so we shut it down using sp_removedbreplication and the problem went away. For other reasons, we did a restore from last night backup which resurrected replication again.  Immediately the errors returned and have persisted despite my removing replication again.

Error: 9003, Severity: 20, State: 1.
The log scan number (390576:5234:44) passed to log scan in database 'XXXX' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the
problem results in a failure during startup.

I'm not finding much on the web related to this except articles for:

The master database and SQL Server not starting
Issues with replication

Any advice would be tremendously appreciated!

With the help of an outside source, the issue was nailed down to a corrupted transaction log and was fixed by reinitializing replication from scratch.

It was strongly recommended that we evaluate our I/O subsystems that house the log file to make sure there isn't a problem there which caused it.


How to do an incremental refresh of a PostgreSQL Materialized View? I'm looking at the PostgreSQL docs where:

  REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view. The old contents are discarded.

However, since 9.5 I believe, there is a way to update a MV without having to regenerate all the data.

There is no INCREMENTAL, it's very simple.. From the docs

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
    [ WITH [ NO ] DATA ]

Perhaps you're confusing it with CONCURRENTLY which is about concurrency (locking) and not minimizing updates.

  Refresh the materialized view without locking out concurrent selects on the materialized view. Without this option a refresh which affects a lot of rows will tend to use fewer resources and complete more quickly, but could block other connections which are trying to read from the materialized view. This option may be faster in cases where a small number of rows are affected.

If you need an incremental refresh, use a table to store the results and join it against the source inserting only rows that NOT EXISTS,

INSERT INTO materialized_resultset
SELECT foo,bar,baz
FROM src
WHERE NOT EXISTS (
  SELECT 1
  FROM materialized_resultset AS mr
  WHERE src.foo = mr.foo
);

or use the newer ON CONFLICT UPDATE or ON CONFLICT DO NOTHING on INSERT.


nested cursors problem I'm trying to restore all the inserts that I've done in my tables.
I have a cursor that loops through table names, a second one that loops through all the rows from that table and a third one that loops through all the columns.
The problem is that I can't declare a dynamic row because it gives an exception at runtime. Is there any solution to my problem? Or can you suggest another approach?

Here is the code:

declare
  cursor c_table is select table_name from user_tables;
  sql_query varchar2(100);
  rc sys_refcursor;
begin
  for v in c_table loop
    dbms_output.put_line('INSERT INTO ' || v.table_name ||' VALUES (');

      sql_query := 'select * from '||v.table_name;
      open rc for sql_query;
      loop
        ---- here i want to fethc all the rows
        declare
          sql_statement varchar2(200);
          v_column varchar2(50);
          re sys_refcursor;
        begin
          sql_statement := 'select column_name from user_tab_columns where table_name = '|| q'[']' || v.table_name || q'[']';
          open re for sql_statement;
          loop
            fetch rc into v_column;
            exit when rc%notfound;
          end loop;
          close re;
        end;
      end loop;
  end loop;
end;

I would not bother implementing it, because others already did it and spent a lot of time with it. SQL Developer can do this for you.

Example: Exporting Metadata and Data for a Table

In SQL Developer, click Tools, then Database Export.

Set the required options:

Select object types, after that, select the tables you want to be exported:

Result will be something like this when choosing to export data only:

--------------------------------------------------------
--  File created - Wednesday-May-10-2017
--------------------------------------------------------
REM INSERTING into BP.T1
SET DEFINE OFF;
Insert into BP.T1 (C1) values (1);
REM INSERTING into BP.T2
SET DEFINE OFF;
Insert into BP.T2 (C1) values (2);


How to isolate 1 MongoDB config server I need to preform a rolling upgrade on my MongoDB.
I've been all over the Mongo documentation and I can't find a solution to my problem (if there is one)
Here's a simple sketch of the Architecture:

I have two Sites (A and B) (telecommunications site, not website).
When I'm executing a rolling upgrade in one site I need to upgrade everything associated to it.

I place my site B in contingency (there are no requests from outside arriving to that Site) and I start upgrading everything, but when I get to the config server I find a problem.

Config servers are always in sync, and this can't happen because all communication with the outside must be terminated.

Is there a way to isolate the config server on that site? (I know they're replicated)
I hope you guys understand my question, is basically how to preform a config server rolling upgrade in multi-host environment without completely stopping all 3 config servers (stopping both sites is not a valid solution)

Will upgrading my config server will have any impact in my service? (in the other site)

Can I shut down 1 config server, upgrade and restart it?

thanks

Yes, you can simply stop one config server, upgrade and restart it. That is one of the reasons why there are three: even when taking down one, there are two others to read from, so one might fail.

A few things to keep in mind, though:

When one config server is down, the cluster meta data is read only: no chunk splits or migrations during that time. So in order to prevent JumboChunks, you better hurry a bit.
Talking of JumboChunks: you should check your cluster for them after update
As for the order: Start with the last server in your configdb string, finish with the first
Always, Always, ALWAYS read the migration guide of the version you are updating to


Managing and Removing data from huge table I have the following scenario:

A huge table, that is also the most use table by far in the system.
By huge I mean it is huge in number of rows and also huge in actual disk size, because it has 2 byte array columns that saves files from our system.

The problem is when I need to remove a large number of rows from it.
First I tried just a single delete with in statement, but is froze the whole table (inutilizing the system with it), for about 45 minutes, when I decide to restart the database.

Then I tried deleting by chunks of 10 registries, and tried deleting 8k+ rows. It went really slow, and everywhere that got data from that table was slow too, and when it reached about 700 rows deleted, I started another process that used that same table, and got a deadlock.

How should I make these mass deletions from this table?
I thought about splitting the table in 2, one for the 2 byte arrays that contains the most part of table volume, and other with the rest of the informations (that are simple things like varchars, integers, datetimes and so on). Would doing this benefit me on these cases of deletion? Or the number of rows would still cause these locks on the table?

The version of SQL is Microsoft SQL Server Standard (64-bit) 12.0.5000.0.

First off, a couple of articles:

Break large delete operations into chunks
Delayed Durability while Purging Data

You didn't mention the version of SQL Server, so I'm not sure if the second one is relevant to you, but it might be to other readers. I'll focus on the former.

Typically, deleting a lot of data causes a wide array of symptoms:

more rows affected = greater likelihood of lock escalation
longer transactions = lengthy blocking (and sometimes deadlocks, as you've found)
heavy writes to transaction log = write latency (including growth events)

So my idea in that article was to lock fewer rows by deleting in "chunks" - each delete operation (or a finite number of them) would be in a transaction, thereby reducing the number of locked rows, the length of the transaction, and the impact to the log.

So I might suggest trying something like this:

SELECT key_column
  INTO #work
  FROM dbo.big_table
  WHERE -- however you identify rows to delete;

CREATE CLUSTERED INDEX x ON #work(key_column);

DECLARE @rc  int = 1,
  @counter   int = 1,
  @batchsize int = 100; -- this may or may not be an optimal chunk size

WHILE @rc > 0
BEGIN
  BEGIN TRANSACTION;

  DELETE TOP (@batchsize) t
    FROM dbo.big_table AS t
    INNER JOIN #work AS x
    ON t.key_column = x.key_column;

  SET @rc = @@ROWCOUNT;

  COMMIT TRANSACTION;

  SET @counter = @counter + 1;

  IF @counter % 10 -- or maybe 100 or 1000
  BEGIN
    CHECKPOINT; CHECKPOINT;
  END
END

The checkpoints make sure that the log space from the transaction can be re-used (the second one ensures the log properly "wraps around"). In your full recovery environment, you'd just need a single BACKUP LOG command there... or you might consider switching to simple if you can time this maintenance work very closely to your regularly scheduled full backup where you can restart the log chain.

The trick is to find the sweet spot of balancing the reuse of the log and the work the checkpoint or log backup has to do. (In my article the frequency of checkpoints actually caused the overall operation to be slower, which might be ok, it doesn't really matter how long this takes if it truly acts like a background process - the problem isn't duration, it's interference. I just didn't fish around for the sweet spot.)

The batch size effectively limits the number of rows locked within any given transaction and reduces the amount of work required by that transaction. You can use delayed durability here if you have a modern enough version; the biggest risk is that you lose a transaction, but since what you "lose" is data removal, you haven't really lost anything.


mysql cannot find database after dropped of it and restored Due to some errors   I decided to drop of all schema and then restore db structure from dump file.  After restoration all other mysql server clients cannot find it (error 1164 ). Only mysqlworkbench   root@localhost I can see that db.
Using same connection data @localhost other clients cannot find that db.

From the root account do:

  show grants for data@'localhost';

If it's empty then re-add the grants. Your dump file probably didn't contact the MYSQL db so it didn't restore the permissions.


mysql replication and innodb_file_per_table Is it possible for the master (5.1) to have innodb_file_per_table = 0 and for the slave (5.1 or 5.5) to have innodb_file_per_table = 1 ?

Yes you can use innodb_file_per_table = 1 for your slaves.
Additionally take below points in your consideration

If you have same version on MySQL on both Master and Slave. You can directly take backup and setup replication from Master to Slave by adding  innodb_file_per_table = 1 on slave in my.cnf
Please have a look at How to set Master Slave replication

If you have different version of MySQL , Please make sure that you are taking backup properly because different version would have mismatches in System tables (MySQL Database).
Please have a look answer by @RolandoMySQLDBA for How to take backup properly


Vacuum settings for mostly append-only data I have a table with the following characteristics:

We INSERT a few 100k rows each day
We never UPDATE the rows
We DELETE "old" data once a week

From my shallow knowledge of Postgres VACUUM, it seems like this table might be a good candidate for a non-standard VACUUM strategy.  A few questions I have...

Apart from the weekly purging of old data, will a VACUUM do anything?  Seems to me there is nothing to compact/clean if we're only doing INSERTs.
Would it be a horrible idea to simply disable AUTOVACUUM on that table and manually VACUUM FULL after our weekly DELETE?

Autovacuum won't run against the table if it not changing. In your case, you are appending data to the table so autovacuum will run, but it is the ANALYZE portion that is worth mentioning. The DELETEs will kick of autovacuum runs of course.
Are you seeing performance problems when autovacuum runs? If not, I would recommend leaving it enabled. After your loading, you could issue a manual VACUUM FREEZE <your table>, but again, I would not recommend disabling autovacuum unless there is a compelling reason to do so.

Note: An unadorned VACUUM does not reclaim space, it marks it as reusable. A VACUUM FULL or CLUSTER will reclaim space however.

-- To show the current vacuum settings:

SELECT  "name",
        current_setting("name") AS current_setting,
        source
FROM pg_settings
WHERE "name" ~* 'vac'
ORDER BY "name"


Conditional field compare based on the value of another field (Case statement?) I have two tables

Alerts(iscomplete, threshold, isup, coinid)
Coins(coinid, LastPriceBtc)

Coins table is updated regularly with prices (going up n down).

I want to trigger alerts that meet the thresholds but I am having difficulty writing the correct query and making it efficient.

Basically what I would like to do is, if alert is not complete (!IsComplete) then if the IsUp field is 1 (up/true) then I want to check if the price of coin (Coins.LastPriceBtc) >= the threshold (Alerts.threshold), and if the IsUp field is 0 (down/false) then check to see if the Alerts.threshold <= Coins.LastPriceBtc.

Here is what I got so far

select  *
from    alerts a
inner
join    coins c
on      c.coinid = a.coinid
where   a.IsComplete <> 1
and     (   (a.IsUp = 1 and a.Threshold >= c.LastPriceBtc)
         or (a.IsUp = 0 and a.Threshold >= c.LastPriceBtc)
        )

Is this the most efficient way to write this query?

I don't know what is that you are trying to do with this logic but the T-SQL query that you are asking for will most probably look like this :

DECLARE @IsComplete integer,@Isup integer,@LatestAlertid integer
        SET @Isup = 1
        SET @IsComplete = 1
DECLARE @id INT
DECLARE @getcoinid  CURSOR

SET @getcoinid   = CURSOR FOR
SELECT coinid from Alerts
OPEN @getcoinid
FETCH NEXT
FROM @getcoinid  INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

    --where  IsComplete is not 1 and Isup is also not 1
        IF @Isup <> (SELECT Isup from Alerts
                     WHERE IsComplete <> @IsComplete
    and coinid=@id)
    --this query will execute
            (SELECT *
            FROM Alerts as Alerts
            join  Coins as Coins
            on Coins.coinid = Alerts.coinid and Alerts.threshold <= Coins.LastPriceBtc
            WHERE Alerts.IsComplete <> @IsComplete and Alerts.Coinid =@id);
        ELSE
        --where IsComplete is not 1 but Isup is 1
        --this query will execute
            (SELECT *
            FROM Alerts as Alerts
            join  Coins as Coins
            on Coins.coinid = Alerts.coinid and Alerts.threshold >= Coins.LastPriceBtc
            WHERE Alerts.IsComplete <> @IsComplete and Alerts.Coinid =@id);
    FETCH NEXT
    FROM @getcoinid INTO @id
END

CLOSE @getcoinid
DEALLOCATE @getcoinid

Output:

Alerts for test:

Coins for test:


MySQL - Changing from system tablespace to file per table The MySQL database I am working on has the InnoDB tables and indexes stored in the system tablespace which is now > 20Gb. Some years ago I managed a MySQL database that made use of a file per table and looking to the future this seems the way to go. The documentation says that this can be done by setting “inno_file_per_table = 1” in my.cnf. Obviously I would need to back up and reload the data into the separate tables but the procedures I have seen are not that clear. Besides my  database schema there are also schemas present for information_schema, mysql and performance schema which I suspect come into the equation. MySQL is running under Mint Linux and although I have seen an answer to this question under Windows  I am not 100% confident on translating this to a Linux OS. What is the best way to achieve this change. It is acceptable at this stage for there to be a few hours downtime.

Thank you

STEP #1

Add the option to my.cnf

[mysqld]
innodb_file_per_table = 1

STEP #2

If you are running MySQL 5.6, login to mysql and run

mysql> SET GLOBAL innodb_file_per_table = 1;

If you are not running MySQL 5.6, restart mysqld

# service mysql restart

STEP #3

Write a script to create NULL ALTER TABLE commands

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"

SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
SQL="${SQL} ORDER BY (data_length+index_length)"
mysql ${MYSQL_CONN} -ANe"${SQL}" > Extract_InnoDB_From_ibdata1.sql

STEP #4

View the script

less Extract_InnoDB_From_ibdata1.sql

If you are OK with it, then  run it

mysql ${MYSQL_CONN} < Extract_InnoDB_From_ibdata1.sql

CAVEAT

This will not shrink ibdata1. It merely extracts all InnoDB tables from it. ibdata1 will no longer grow beyond the 20GB due to the tables themselves.

If you want to shrink ibdata1, please refer to my post How do I shrink the innodb file ibdata1 without dumping all databases?

GIVE IT A TRY !!!


Exclude composite key from select I have 2 identical DBs, one of the tables has C1, C2, C3 , composite key.
I'm trying to get the rows from B that are not present in DB A.

I have tried many approaches, but at this time I even can't re-use this suggestion due too the late hour.

I think it should be by something like

Select * from B
where  not exists
     (SELECT a.C1, a.C2, a.C3
      from A.table as a, b.table as b
      where a.C1=b.C1
          , a.C2=b.C2
          , a.C3=b.C3)

But I know that I'm totally wrong.
What is the proper way to do this?

I think you need this:

Select * from b.table as b
where  not exists
     (SELECT 1
      from A.table as a
      where a.C1=b.C1 and a.C2=b.C2 and a.C3=b.C3 ) ;

You shouldn't add b.table in the subquery's from clause, because you already have it under the b alias in the main query.
You don't need to specify columns in the subquery's select clause, because that doesn't have any effect. "Is there a row satisfying the where clause?" – that's the question that the exists predicate is meant to answer.
You need AND between the conditions in WHERE, not commas.


More tables or more columns (in my simple example)? I'm modelling DB and wondering if I should create more very simple tables or just have more columns in tables.

I mean, let's assume I have s table named User, each user can have status(ACTIVE, INACTIVE, BLOCKED, ...). Should I store status directly in User(as an ENUM?) table or maybe it's better to create a table named UserStatus with status ID, status name and FK pointing to the user?

Similar with employees and their position(manager, developer, etc.).

Can you give me some advices?

Typically user would have the fk

user:
ID (pk)
Name
StatusID (fk referencing status.ID)

status:
ID (pk)
Name

A user has a status - not a status has a user


Slow postgres query that uses quick sort instead of hash-aggregate I'm on postgres 9.5 and working with a web-analytics database that records visitor traffic.

I'm trying to optimize a very slow query gives me a count of the unique people that visited a given page grouped by their session device type.

The query looks like so:

select   groupname,  count(person_id) as thecount
from (
  select distinct S.first_device_type as groupname, A.person_id
  from event_page as O
  join alias as A
    on (O.person_alias = A.alias)
  left outer join session as S
    on (O.session_id = S.session_id)
  join alias as A1
    on ( A1.alias = S.person_alias and A.person_id = A1.person_id)
  where O.timestamp_ between timestamp '2017-02-11 23:22:20.146' and timestamp '2017-03-13 23:22:20.146'
    and O.location_host = 'www.foo.bar.ca'
    and S.first_seen between timestamp '2017-02-11 23:22:20.146' and timestamp '2017-03-13 23:22:20.146'
) as alias_120134400
group by groupname

The above SQL runs in over 2.5 minutes and the output looks like this.

device type        count
--------------------------
Computer       |  163304
Game console   |      41
Mobile         |   33519
Tablet         |   10465
Unknown        |       5

There are couple peculiarities about the schema and above query I need to point out:

The event_page table is a union of monthly tables
Joining the alias table twice is necessary because an alias may change during a session. aliases to personIDs is many to one. Hence the page view alias and session alias may be different but point to the same personID.
All statistics are up to date (everything vacuum analyzed)!

As the explain below shows it appears that the Quick Sort is the bulk of my problem:

https://explain.depesz.com/s/6JjQ

In addition to that there is seq scan occurring (line 23 in the explain) on the session table yet that condition should be easily handled by the index.

I've run the same query on a different (albeit smaller) dastaset and the query plan seems more sensible. This one executes in only 3 seconds. Here is the explain:

https://explain.depesz.com/s/4k8t

Any ideas as to why the former explain chooses quick sort and the latter uses HashAaggregate to achieve the DISTINCT?

What can I do optimize the query and avoid quick sort?

Why (in both cases) is the planner choosing a seq scan of session when that filter is covered by an index?

Thanks in advance for any help.

You can see the flaw here,

  Hash Cond: (((event_page_2016_8.person_alias)::text = (a.alias)::text) AND ((event_page_2016_8.session_id)::text = (s.session_id)::text))

It thinks that condition returns 2 rows. Instead it returns 11,861,365. If it returned 2 rows, a quick sort almost certainly would have been faster. The question is primarily why does it think that it's so far off there..

You may want to try to refresh the statistics (or even raising the statistics target for those tables) with ANALYZE event_page_2016_8 alias session, or see what could be returning so many rows to begin with.

Also this conditional doesn't make much sense to me

join alias as A
  on (O.person_alias = A.alias)
left outer join session as S
  on (O.session_id = S.session_id)
join alias as A1
  on ( A1.alias = S.person_alias and A.person_id = A1.person_id)

Maybe I'm missing something. So you're self-joining A to A1 on person_id but also S.person_alias which has to share a session_id.. Why not just

join alias as A
  on (O.person_alias = A.alias)
join session as S
  USING (session_id)

And leave out A1 entirely? You're not using it in the SELECT list?


DB2: Is it good to create the index table space to use 32 KB data page I found our DB2 server configs all the index into one table space. And it then defines the table space to use 32kb data page. Is this good? How to judge it? Any advice is welcome.

From what I have read (check out Understanding DB2: Learning Visually with Examples), IBM recommends that you place data, indexes, and long data (meaning LOB type data as well as deprecated types like LONG VARCHAR) into their own tablespaces.

Main reason is for maintenance and support. If the data is in different tablespaces you can backup/restore based on your needs. For example, table data is "more" important than indexes (as indexes have no purpose without data in the tables). So if your indexes are in a separate tablespace and end up corrupted or needing a restore, you only have to restore the indexes, not the whole table (of course you may need to run reorgs and runstats to make sure your indexes are up to date).

Regarding LOB's - unless they are declared INLINE, they are not stored directly with the table data anyway (and INLINE only works for LOB's of a certain size). Hopefully you don't have too much update activity occuring on your LOB (as that can be a little costly seeing as LOB's are never cached in a bufferpool and always retreived from disk). So having them in another tablespace can help there as well. You have a separate space to store the data and if the table data is corrupted/needing a restore, but the LOB's are fine, again, you could restore just the table data without losing your LOBs.

So if you think of IBM's recommendation. They would recommend you build your table as such:

CREATE TABLE MY_TABLE (
ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
TEXT VARCHAR(100),
...
primary key (ID))
IN TS_DAT
INDEX in TS_IND
LONG in TS_LOB; --or TS_LONG if you wish.

As for size....I would recommend that you only put your tables and indexes and long/LOB data in the right sized page. If your indexes don't require a 32K tablespace, why put them there? It just wastes pages (a lot of space on the page will be ununsed) and thus waste disk. If you need to later move the indexes or the tables or the long/lob to a higher page size tablespace, it isn't too hard to do that later. (Of course you may need to have a maintenance window to do it in, but again, I think...even though disk is "cheap" so to say...why waste money?).

We used to put the tables in whatever size they needed and put the indexes only in a 4K unless they needed larger, figuring we could always change them later. But we recently have started to make sure the indexes are in the same tablespace size as the table data and LOB data. Reason being is you cannot change a tablespace after a table is created. To move the data you essentially have to create a new table and move all the data from one to the other.

Same goes for a few LOB's we have in other tables. I figured out their size and put them in an appropriately sized tablespace.

I have found the following pages on Space requirements for storage very helpful for understanding what sizes for tables and LOB's determine the required page size (and thus tablespace). The Space requirements for indexes link is helpful for indexes. Note: this is for DB2 9.7. If you need a different version, trying changing the number in the link, or start from IBM's main page.


Foreign key constraints check during replication? I am a little bit new to replication and can't seem to find an answer to this anywhere.
Let's say I have a simple relation in my database: Department table with Code column as PK and Person table referencing Department by Person.DepartmentCode -> Department.Code.

ALTER TABLE [dbo].[Person]  WITH CHECK ADD  CONSTRAINT [FK_Person_Person]
FOREIGN KEY([DepartmentCode])
REFERENCES [dbo].[Department] ([Code])

Now, I would like to implement merge replication to a branch database of only relevant departments and people. I could set up filtering by DepartmentCode in all the tables that will be partitioned, which would do the trick. But I wanted to see if I could leverage referential integrity to do part of the filtering for me. I.e. only filter dbo.Department and rely on constraints to not let irrelevant people into the replica's dbo.Person. However, what I get in the replica is the dbo.Department with filtering properly applied and dbo.Person with DepartmentCode values breaking the foreign key constraint (and the new records with "wrong" DepartmentCode continue to be replicated into the branch's DB). The constraint is applied and enforced for any other transactions, though. So, if I try to insert a record breaking the integrity with a user INSERT, I can't.

So it looks like the referential constraint isn't being checked during replication of the data.
1. Is there a way to enforce constraint checking for data inserted by replication?
2. Would it be a good idea to use that for filtering/partitioning?

Foreign Key Constraints, Check Constraints, IDENTITY columns and Triggers all support the NOT FOR REPLICAION option to control whether they are active when the replication agent updates subscribers.

See Disable Foreign Key Constraints for Replication.


Prefix full text search with numbers in SQL Server If I create the following table in SQL Server 2008 R2:

CREATE TABLE test(
  id BIGINT IDENTITY NOT NULL,
  value VARCHAR(10),
  CONSTRAINT test_pk PRIMARY KEY (id)
);

CREATE FULLTEXT CATALOG test_catalog;

CREATE FULLTEXT INDEX ON test
  (
    value Language 0
  )
  KEY INDEX test_pk
  ON test_catalog;

ALTER FULLTEXT INDEX ON test START UPDATE POPULATION;

INSERT INTO test (value) VALUES ('Xstreet 7');
INSERT INTO test (value) VALUES ('Ystreet 8');
INSERT INTO test (value) VALUES ('Zstreet');
INSERT INTO test (value) VALUES ('Wstreet9');

Wait until the index is populated and perform a prefix search for 'Ystreet 8':

SELECT * from test WHERE CONTAINS(*, '"Ystreet 8*"');

I get no results. Why is that?

Searches for '"Ystreet 8"', '"Ystreet*"' and "'Wstreet9*"' works as expected.

It seems the problem is caused by 8 (and 0,1,2,3,4,5,6,7 and 9) being on the stopword list.

A workaround is to base the table on an empty stopword list (or at least one without the 1-digit numbers):

CREATE FULLTEXT STOPLIST empty_stoplist;
ALTER FULLTEXT INDEX ON test SET STOPLIST empty_stoplist;
ALTER FULLTEXT INDEX ON test START UPDATE POPULATION;


Conversion of Row into Column I'm in very weird situation and i have researched a lot but not able to find anything similar to this scenarios.

Input looks something link this.

Where Each patient has Primary Dx code, Admit Dx code and Additional Dx code.

Note- Additional Dx code sometimes ends with ';' , Each Dx code is seperated by ';'.

There will be only one Primary Dx and Admit Dx column.

My output should be something link this

Use a split string table valued function and CROSS APPLY.

--sample split string TVF
create FUNCTION [dbo].[SplitString]
(
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO

--demo setup
Declare @Table1 table (HIC_Number varchar(5), DateFrom date, DateTo date, PrimaryDx varchar(5), AdmitDX varchar(5), AdditionalDx varchar(100))
insert into @Table1 (HIC_Number,DateFrom,DateTo,PrimaryDx,AdmitDX,AdditionalDx) values
('A','1/1/2018','1/25/2018','E559','E459',';E559;E099;E1121;E784;E039;J449')

--the solution
SELECT HIC_Number
    ,DateFrom
    ,DateTo
    ,PrimaryDx
    ,AdmitDX
    ,Item
FROM @table1 a
CROSS APPLY dbo.SplitString(a.AdditionalDx, ';')
WHERE item <> ' '

| HIC_Number | DateFrom   | DateTo     | PrimaryDx | AdmitDX | Item  |
|------------|------------|------------|-----------|---------|-------|
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | E559  |
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | E099  |
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | E1121 |
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | E784  |
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | E039  |
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | J449  |


Can Entity Framework access In Memory OLTP Tables? Are there any restrictions with having Entity Framework access In Memory OLTP Tables? Or is this not possible?

I created a small test application to see if there were any issues accessing IMOLTP tables from Entity Framework.  My test application uses the latest stable version of Entity Framework (EF6).

With the default settings, I got this error initially trying to insert a row into a memory-optimized table:

  SqlException: Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

This is because Entity Framework creates an explicit transaction automatically when using the "SaveChanges()" method (the most common approach for inserting rows):

  In all versions of Entity Framework, whenever you execute SaveChanges() to insert, update or delete on the database the framework will wrap that operation in a transaction.

The simplest way to work around this in the case of EF is to turn on the database-level option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, which essentially adds the needed WITH (SNAPSHOT) hint automatically:

ALTER DATABASE [YourDatabaseName] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON;

Other than that, there seems to be no issues with accessing the in-memory tables.  This isn't all that surprising, since under the covers Entity Framework is using the .NET Framework Data Provider for SQL Server (SqlClient), which has no documented limitations when it comes to in-memory tables.


What will be inside the last differential backup file? I am new in DBA and while learning different types of backup, one question came into my mind. So the question is, what will be inside the last differential backup, just the sum of the previous differential backups or the sum of previous backups plus t-log backups? Let's assume I have the following backups:

full_backup_2018_05_09_000000.bak
tran_backup_2018_05_09_003500.trn
tran_backup_2018_05_09_004000.trn
diff_backup_2018_05_09_004500.bak
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn
diff_backup_2018_05_09_006000.bak
tran_backup_2018_05_09_006500.trn
tran_backup_2018_05_09_007000.trn
tran_backup_2018_05_09_007500.trn
diff_backup_2018_05_09_008000.bak

I'd definitely recommend that you read A SQL Server DBA myth a day: (30/30) backup myths from SQL Server expert Paul Randal for some really good information about SQL Server backups.

To answer your immediate question, the lastest differential backup (diff_backup_2018_05_09_008000.bak) in your question would contain all changed pages since the last full (non COPY_ONLY) backup (full_backup_2018_05_09_000000.bak) **PLUS ** enough of the transaction log to be able to recover the database to a transactionally-consistent view of the database at the time the data-reading portion of the backup finished.  Transaction log backups would not affect what pages are included in full or differential backups


Getting MySQL start and shutdown time How to write a trigger that updates a table before startup and shutdown of the database in MySQL 5.7 ?

Any suggestions that could be helpful to us?

STARTUP TIME

For everyone running MySQL 5.7

SELECT NOW() - INTERVAL variable_value SECOND MySQL_Started
FROM performance_schema.global_status
WHERE variable_name='Uptime';

For anyone running MySQL 5.6 and prior, or MySQL 5.7 in MySQL 5.6 compatibility mode

SELECT NOW() - INTERVAL variable_value SECOND MySQL_Started
FROM information_schema.global_status
WHERE variable_name='Uptime';

Just mentioned this three(3) days ago (Make MySQL print a timestamp at each time it starts)

SHUTDOWN TIME

There are four ways to acquire a shutdown time

METHOD #1 : Search the Error Log

If the error log is called error.log, run this

grep "Shutdown complete$" error.log | tail -1 | awk '{print $1,$2}'

This will produce the date (YYMMDD) and time (HH:MM:SS)

METHOD #2 : InnoDB Buffer Pool

If you have innodb_buffer_pool_dump_at_shutdown configured, look for the timestamp of the file that was written. The default filename is ib_buffer_pool. Mentioned this back on Dec 06, 2017 (Control InnoDB buffer pool allocation in MySQL (5.7+) ib_buffer_pool is usually written in folder set by datadir, you can run the following after shutdown:

cd /var/lib/mysql
ls -l --time-style=+"%Y-%m-%d %H:%M:%S" ib_buffer_pool | awk '{print $6,$7}'

METHOD #3 : Binary Logging

If mysql has binary logging enabled, get the timestamp of the last binary log after shutdown.

METHOD #4 : Error Log

You are probably saying, "I DON'T WANT TO LOOK INSIDE THE ERROR LOG !!!"

You don't have to. Just get the timestamp of the error log after the shutdown is complete.

CAVEAT ON SHUTDOWN

Method #1 is not any good to you if mysqld crashes.

The other three(3) methods will simply tell you the last time mysqld did any kind of writes, whether it was a clean shutdown or a crash.

YOUR ACTUAL QUESTION

First off, a trigger is not needed nor would it fire off on shutdown.

You cannot write a shutdown time to a table when mysqld is shutting down.

The best I could suggest is to capture the shutdown datetime with one of the four(4) aforementioned methods and write it in a script. You can execute that script as follows:

Create a file called /var/lib/mysql/startup_stuff.sql

Run chown mysql:mysql /var/lib/mysql/startup_stuff.sql

Write inside that script in such a way that the datetime is written to a table

Add this line to /etc/my.cnf (see MySQL Documentation on init-file)

init-file=`/var/lib/mysql/startup_stuff.sql

Startup mysql using service mysqld start


slow select query on datetime column I have a table in MySQL which has approximately 10 million records, and now a query which used to take micro seconds now takes more that 8 seconds to execute!
Here is my table structure:

CREATE TABLE `runs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `runstub` VARCHAR(20) DEFAULT NULL,
  `userid` INT(11) DEFAULT NULL,
  `processid` INT(11) DEFAULT NULL,
  `rundata` VARCHAR(255) DEFAULT NULL,
  `token` VARCHAR(60) DEFAULT NULL,
  `created_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`)
) ENGINE=INNODB AUTO_INCREMENT=10039971 DEFAULT CHARSET=latin1;

and here is the query that takes more than 8 secs...

SELECT MIN(created_at), MAX(created_at) FROM runs ORDER BY id DESC LIMIT 10000

Is there a way I can optimise this query to get it run in less than a second?

This is the result I want to achieve: From the last 10k records (not all), the time it took. I want the time it started (MIN(created_at)) and the time it ended (MAX(created_at)). How can I achieve that without the limit?

Here's one attempt:

SELECT MIN(created_at), MAX(created_at)
FROM (
    SELECT created_at
    FROM runs
    ORDER BY id DESC
    LIMIT 10000
) as x;

Your query is evaluated as:

SELECT MIN(created_at), MAX(created_at) FROM runs

Then it is ordered according to id (which doesn't make sense) and a maximum of 10000 rows is returned (only 1 row exists)

My query first picks the 10000 most recent id's

SELECT created_at
FROM runs
ORDER BY id DESC
LIMIT 10000

From there it picks min and max for created_at


Unable to begin a Distributed transaction Can someone help me to troubleshoot this error?

  Error:OLE DB provider "SQLNCLI11" for linked server "TESTSERV"
  returned message "No transaction is active.". Msg 7391, Level 16,
  State 2, Line 7 The operation could not be performed because OLE DB
  provider "SQLNCLI11" for linked server "TESTSERV" was unable to begin
  a distributed transaction.

I am getting the error executing the code below.

CREATE TABLE #Drive
(
    drive char(1) Primary Key,
    freespace int null
)

Insert into #Drive
EXEC TESTSERV.MASTER.xp_fixedDrives

I've already done the following set up:

Allow Remote Clients
Allow Outbound

On Security tab of Local DTC Properties in Component Services:

Go To Run, type comexp.msc.
Double click "Console Root".
Double click "Component Services.
Double click "Computers".
Double click "My Computer".
Double click "Distributed Transaction Coordinator".
Right click "Local DTC" under "Distributed Transaction Coordinator", and click properties.
Click the "Security" tab.
Put tick marks on the checkboxes "Allow Remote Clients" and "Allow Outbound".

Still not fixed.
Different clone is used here for remote machine.

Map a specific instance of SQL Server to a specific instance of MSDTC?

To map a SQL Server instance to an instance of MSDTC, open an administrative command prompt and use the msdtc.exe command.

The following command is used to create a mapping between an instance of SQL Server and an instance of MSDTC.

msdtc -tmMappingSet -name <MappingName> -service <SQLServerServiceName> ^
-ClusterResourceName <MSDTCResourceName>

https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/15/msdtc-recommendations-on-sql-failover-cluster/


selecting all row of max value of a column I have this simple query.

SELECT

    t3.C_COMDTY_SHORT_DESC
    ,t3.C_HS_CODE
    ,t3.I_COMDTY_ID
FROM

    ADM_COMMODITY_MASTER t3
WHERE
 t3.C_HS_CODE = '2203.00.00'

which return two rows as below.

Now i want to select the row of corresponding the column which has max I_COMDTY_ID.
so i write like this.

SELECT

    t3.C_COMDTY_SHORT_DESC
    ,t3.C_HS_CODE
    ,t3.I_COMDTY_ID,max(I_COMDTY_ID)
FROM

    ADM_COMMODITY_MASTER t3
WHERE
 t3.C_HS_CODE = '2203.00.00'  group by t3.C_HS_CODE;

This is what is got.

Why column C_COMDTY_SHORT_DESC value is BEER ? I know how to get max row and other way around. But why it is not working ?

Your query will get the max value of I_COMDTY_ID with ANY combination of the other fields from the rows that satisfies your WHERE condition.

A better way to do that:

SELECT
    t3.C_COMDTY_SHORT_DESC,
    t3.C_HS_CODE,
    t3.I_COMDTY_ID,
    I_COMDTY_ID
FROM
    ADM_COMMODITY_MASTER t3
WHERE
    t3.C_HS_CODE = '2203.00.00'
ORDER BY
    I_COMDTY_ID DESC
LIMIT 1;


Keeping "SQL Trace was started" and "SQL Trace stopped" from filling up my SQL logs I have a SQL2008 server that runs a custom application that starts and then stops a SQL trace every 60 seconds (it does some reporting and summarization on the trace data).

The problem is that my SQL logs are filling up with the "SQL Trace ID 1 was started" and "SQL Trace stopped" entries. This makes it hard to browse through and identify other unusual events.

I can't find any way to turn logging of this event off. Any ideas?

For your reference, I did find a couple of partially-related articles:

Microsoft KB 307538 mentions that this was "fixed" (to add logging) in SQL2000 SP2
This newsgroup question asks the same question I had, the only answer given is using the "-n" startup parameter to keep all SQL events from showing up in the Application Event log. That's not what I want, though. I want to keep these from being logged in the SQL log at all.

EDIT with some additional curious findings:

This same application is also running on a SQL 2000 server (with SP4). Strangely, the trace start/stop events do not show up in the SQL logs on that server, although they do show up in the Application event log as "19030: SQL Trace started" and "19031: SQL Trace stopped".

If there were a way to revert SQL2008 to this behavior (write to app log, but not SQL log), then that would be nice :)

Please enable Trace Flag 3688 and restart the instance. This should prevent these messages from appearing in the SQL Server Error Log


Does a query with a primary key and foreign keys run faster than a query with just primary keys? SELECT something FROM table WHERE primary_key = ?

vs.

SELECT something FROM table WHERE primary_key = ? AND other_key = ?

Say that this is a scenario where the inclusion of other_key does NOT change the resultset. Is the second query faster in practice? Or do databases just use a single best key if several are provided?

Query
SELECT something FROM table WHERE primary_key = ?

This the fastest possible form. Adding any other predicate can only make it slower. Theoretically.
Exotic exceptions apply, like when the PK index is bloated for some reason, or the PK column is relatively big, or a multi-column PK, resulting in a much larger index, while the index for the added predicate on other_key is smaller. Then Postgres may decide to use the index for the added predicate, access the heap and filter on primary_key = ?. Unlikely, but possible.
If the added predicate evaluates to anything but TRUE, you get no row - a different result, so not a fair comparison - but that's not your case as you asserted.
A FOREIGN KEY constraint has no direct impact on read performance. The referencing column does not even have to be indexed (as opposed to the referenced column).
Covering index for top read performance
With tables of non-trivial size and not too much write activity, consider adding a multicolumn index on (primary_key, something) to allow index-only scans. In Postgres 10 or older that results in at least two indexes (imposing additional write / maintenance / space costs):

the PK index on (primary_key), obviously.
a plain (or, redundantly, UNIQUE) index on (primary_key, something).

Postgres 11 added true covering indexes using the INCLUDE clause, which conveniently allows to piggyback the non-key column something on the PRIMARY KEY:
CREATE TABLE tbl (
   primary_key bigint GENERATED ALWAYS AS IDENTITY
 , other_key   integer NOT NULL REFERENCES other_tbl
 , something   text
 , PRIMARY KEY (primary_key) INCLUDE (something)  -- here's the magic
);

If primary_key happens to be a much wider column than other_key you mentioned (bigint vs. int like in the example would not qualify), you can also piggyback something onto an index on other_key:
CREATE INDEX other_idx ON tbl(other_key) INCLUDE (something);

While either solution can optimize read performance for the given query, other queries not retrieving something then have to work with a bigger index. So weigh benefits and costs (like always when creating indexes).

The manual on CREATE INDEX.

Related blog entry with details from Michael Paquier:

Postgres 11 highlight - Covering Indexes


SQL Calculation based on 'similar' rows within table I'm using SQL Server 2008 R2, building a fact table for SSAS.

I can therefore accept solutions to this via either SQL query to SSIS task.

Let's say I have a fact table that looks a little like this, its a record of when people have eaten cakes, and how many (for illustrative purposes).

Surrogate Key | CakeType | PersonKey | CakesEaten | Date     | HadSeconds
1               20         1234        4            20120412   0
2               41         2345        3            20120401   0
3               25         3456        4            20120408   0
4               39         2345        2            20120402   0
5               20         4567        3            20120409   0
6               89         9876        2            20120405   0
7               31         1579        4            20120408   0
8               29         2709        3            20120402   0
9               27         3456        3            20120410   0
10              26         2345        2            20120408   0

CakeType and PersonKey and Date are all foreign keys to dimensions.

CakesEaten and HadSeconds are measures.

Techie type question: What I want to do, is to mark a fact as 'HadSeconds' where a Person has a record in the table within 2 days of the previous fact.

Business type question: Show me the instances of people going to eat cake when they've already had some cake in the previous 2 days.

For example, Person '2345' has 3 records in this fact table, IDs 2, 4, 10.

I want to mark 'HadSeconds' for ID '4' as a '1', because the Date for ID 4 is within 2 days of ID2, therefore the person ate cake in quick succession, and this instance needs to be marked as 'HadSeconds'. ID '10' has a datestamp which is 6 days after ID '4', therefore the 'HadSeconds' column for ID 10 does not need to be marked.

Note that only records were a Person has multiple entries in the fact need to be considered.

The best (lol) thing? This fact table has approx. 60 million records in it.

What's the most efficient way to achieve this? Breaking stuff down into temporary tables? Sorting? Cursors (ew...), Aggregration tasks in SSIS...

Just an UPDATE using EXISTS will be  your best bet:

UPDATE c
SET HadSeconds = 1
FROM Caketable C
WHERE EXISTS (SELECT 1
              FROM CakeTable C2
              WHERE c2.personid = c.personid
              AND c2.date > DATEADD(day, -2, c.date))

If you think this will run too long, you can always break it down by date range, cake type, etc to do smaller batches.

If you have a composite index on personid, date this should be pretty fast.

You could also do a self-join but I'm pretty confident the EXISTS will run a lot faster:

UPDATE c
SET HadSeconds = 1
FROM Caketable C
INNER JOIN CakeTable C2
  ON c2.personid = c.personid
  AND c2.date > DATEADD(day, -2, c.date)


MySQL - Delete with IN Clause taking more time In MySQL 5.6 I have around 60 million rows. I wanted to remove rows based on Id. Here Id is the primary key.

One strange behavior I had today.

delete from tbl where id=1;

Its executed in milliseconds.

Then I did this.

CREATE TEMPORARY TABLE DELETE_ID (ID int);
Insert into DELETE_ID select id from (subery1(subquery2(subquery3)));

The DELETE_ID has around 150 ID.

Delete from tbl where id in (select id from DELETE_ID);

Its taking more than 30mins, but deleting a single row.

There were no locks or deadlock.

In innodb_trx the status was sometimes fetching rows sometimes unlocking rows.

Can anyone help me to understand why this is happening?

WHERE .. IN is slow in most cases.

Use multi-table delete:

DELETE tbl.*
FROM tbl, DELETE_ID
WHERE tbl.id = DELETE_ID.id


why the optimizer doesn't choose the optimal plan I know, if the statistic is not updatad, it is possible, that the optimizer doesn't choose the optimal execution plan?

Does any other reason/reasons exist?

The optimizer strives to get a plan that is "good enough", and this is not always the optimal one.

A very common reason is a too complex query. Breaking it down to a few queries helps the optimizer choose a better plan.

In some cases, too many indexes on a table can also cause this, as the optimizer
might use an index that is not the best one because as we said, it doesn't look for the best plan but rather a plan that is good enough.

Other reasons can be the use of local variables (the optimizer doesn't know their values), non SARGABLE arguments, missing indexes, improper use of hints and plan guides, and more..


Foreign key as part of composite primary key or not? I'm designing a relational database. This is what I'm trying to design:

I will have production orders and products.

A production order will have one product, but a product could be on zero or more production orders (In fact, there will be always at least one production order for each product. It has no sense to have a product that it's not going to be produce on database).

In other words, with a production I will produce a product, but I will have a lot of production orders to produce the same product.

We are going to distinguish each production order by its name (alphanumeric) and it must be unique.

I have two questions:

Is the production order's name its primary key? I think I can use a integer as primary key.
Does product's key must be part of production order's primary key? I don't think so but I'm not sure.

By the way, I'm going to use SQL Server.

It depends on the specific scenario.

For example, if you have a many-to-many relationship, then it is common to have a join table between the two tables. The join table will contain a foreign key to each of the primary keys and most of the time, the combination of the two foreign keys will become the primary key:

CREATE TABLE users
(
    id INT NOT NULL PRIMARY KEY
);
CREATE TABLE roles
(
    id INT NOT NULL PRIMARY KEY
);
CREATE TABLE users_roles
(
    user_id INT NOT NULL REFERENCES users(id),
    role_id INT NOT NULL REFERENCES roles(id),
    PRIMARY KEY (user_id, role_id)
);

For the most part, I prefer natural keys rather than surrogate keys - especially if I will never use the surrogate key to ever query the table.

With that being said, to answer your question directly:

Firstly, can a string be a primary key? Though the name may be its natural key, you have to take into consideration the size of the key if you made it the primary key. Not just in your main table, but also in the tables that refer to it. Remember, that in order to create a foreign key you must copy the key value into any dependant tables - this can quickly bloat your database and slow down queries depending on the size and type of data. For instance, short fixed-length text columns can perform better than variable-length text columns and can be indexed easier than longer text values. A good example of this that I use each day is a currency table. My primary key is a 3 character currency code - it is short, of a fixed length, and I am always going to query the table using it even if the table had a surrogate key defined on it.

CREATE TABLE DimCurrency
(
    Code CHAR(3) NOT NULL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);
CREATE TABLE FactSales
(
    CurrencyCode CHAR(3) NOT NULL REFERENCES DimCurrency(Code),
    Date DATE NOT NULL,
    SalesAmmount DECIMAL(18,2) NOT NULL
);

Secondly, does the product id need to be part of the productions primary key? Well, this depends on a few factors. If you made the name part of the primary key, and the name was short then potentially, yes it could. But, does any other table have a reference to the productions table? If so, then by adding the product id into the tables primary key you would also need to add it into all of the foreign keys that refer to it. Personally, I don't like this. I am happy to do it if I can guarantee that no table will ever need a foreign key to it (such as a fact table in a dimensional model) but otherwise I think it is wasteful and makes querying your tables over complicated.

You should also consider that if you define further non-clustered indexes on the production table when you have a composite key, the row locator for the non-clustered index is a copy of the primary key itself. This can cause all of your non-clustered indexes to bloat out of control very quickly without your realising it. For reference: Clustered and Nonclustered Indexes Described

I think given your particular scenario, it is perfectly reasonable to add a foreign key in your production table which refers to the id of the product that is produced. As Gulrez Khan stated in his answer, you may want to add a non clustered index on the foreign key - it is not required, but can enhance performance in some queries.

CREATE TABLE production
(
    id INT NOT NULL PRIMARY KEY,
    product_id INT NOT NULL REFERENCES product(id),
    name VARCHAR(50) NOT NULL UNQIUE
);

CREATE INDEX ix_production_product_ncl ON production(product_id);

Remember that all indexes need to be built, and rebuilt when they are modified. The more fields you cram into them, the longer it takes to perform actions against them. Especially clustered indexes, which are generally defined upon the primary key. These indexes physically sort your data at the storage level. If you insert a value that happens to not be at the end of the existing chain of values, you could cause performance problems due to page splits. For me, this is the biggest advantage of a surrogate key over a natural key - the ability to ensure that new data is always appended and not injected into the middle of an existing index.

Finally, if in doubt - try it out! There is no substitution for a good testing strategy. Try querying the data against both structures, look at the query plans, do some capacity planning and choose the one that is right for you.


What's the best design for a logical structure that contains different properties of various types for different entity categories? (MySQL or NoSQL) I'm designing a MySQL database that needs to contain a list of products. Those products, based on their category, will need to have a list of various properties.

Let's say all products in category "displays" will need to have a property "name" (varchar), and "description" (text), whereas the products in category "keyboards" will need to have properties "cord length" (float) and "keyboard highlighting" (boolean).

I came up with a following structure:

Table product:

id_product INT [PK]
category ENUM('displays','keyboards', ...)

Table property:

id_property INT [PK]
name VARCHAR(50)

Table value:

id_value INT [PK]
id_product INT [FK]
id_property INT [FK]
type ENUM('varchar','boolean', ...)

Table value_varchar

id_value INT [FK]
value VARCHAR(255)

Table value_boolean

id_value INT [FK]
value BOOLEAN

As you can see, the structure contains two basic tables product and category and a many-to-many relation in value table, but it doesn't keep the property values in value table (because they can be of different types). Instead, the values are kept in additional type-specific tables value_varchar, value_boolean and so on (there should be more such tables, like value_text etc.).

Such structure seems suboptimal to me, as if I want to retrieve all values for a particular product, I would need to retrieve the value types at first and then run additional queries to retrieve the values from the tables related to those types.  However, I can't come up with anything better right now as far as MySQL is concerned. Maybe NoSQL is an answer to such a construct? If so, which one of NoSQL engines I should look into? I have no experience with NoSQL.

Thanks in advance for helping me out.

I recommend you to read about the dangers of the Entity-Attribute-Value pattern on this presentation by @Bill Karwin.

One of the solutions is storing NoSQL-like data in a serialized BLOB, if you do not need to read and write individual properties. That is, storing a key (product) as the primary key, and the variable property-value pairs all together in a single blob. This is a non-normalized structure, leaving the responsibility of the constraints to the application layer, but it works great for certain patterns (the same that usually work well with NoSQL solutions). Even if you need indexing, you can create inverted indexes on a separate table as needed.

This and other solutions can be seen on this presentation in the latest MySQL Conference, including when you should search for alternative database engines.


Thai and English in a postgresql I have a postgresql DB with Thai and English values/strings in several tables.

Currently I have the following settings

| Encoding |   Collate   |    Ctype |
| UTF8     | en_US.UTF-8 | en_US.UTF-8  |

If I order by a column with Thai values, the sort order is not correct.

What settings should I change to have postgresql sort correctly?

On PostgreSQL 9.1 and newer, you can use the COLLATE qualifier on an operation to override the database's default collation. See the manual for information on collation support.

E.g.

SELECT a, b FROM mytable ORDER BY c COLLATE 'th_TH.UTF-8';

Note that PostgreSQL can't mix different collations, using a dynamic collation based on detected language. It doesn't work like that.

On prior versions you must use a single database-wide collation. So you'd have to dump your database, CREATE DATABASE ... ENCODING 'UTF-8' LC_COLLATE 'th_TH.UTF-8', and re-load.


Can SQL Server OpenQuery/OpenRowSet to an LDAP Linked Server be used for Authentication? I can fetch data back from LDAPS (port 636/3269) by first creating a Linked Server using sp_addlinkedserver and sp_addlinkedsrvlogin.
However, what I really need to do is to authenticate a Username/Password pair against LDAPS, to determine if the credentials are Valid.
Can SQL Server be used in any way to authenticate Username/Password credentials against AD/LDAP?  Is it possible to use an Extended Stored Procedure or the CLR to do this?  I need to abstract this away from a .DLL because the same authentication needs to be used in a legacy application and on a website.

I'm not sure why you'd need a linked server to LDAP to validate credentials? SQL Server has Active Directory (Windows) authentication built into it, so you should be able to leverage that as long as you add the appropriate Login object in SQL Server for either the AD User or AD Group that user is a part of, so they have the ability to connect.
Unless I'm missing something like you're trying to connect to LDAP of a different domain than that of the SQL Server instance, but then we'd more details on your goal.


Warning for missing statistics in execution plan I have a situation that I can't understand. My SQL Server execution plan tells me that I have missing statistics on the table, but the statistics are already created:

But if we look at the table, we will see that there is a statistic that has been created automatically:

Can somebody help to understand how it can be?

Auto_Update and Auto_Create statistics is turned on on the current DB.

I am using SQL Server 2014.

The warning does not always correspond to missing single-column statistics.

There is no easy way to precisely determine the exact statistic the optimizer went looking for and did not find in all cases, but it is almost always a multi-column statistic that would provide some correlation information across multiple equality predicates in the query.

Perhaps in your case it corresponds exactly with the predicates in the Filter following the scan where the warning appears.

It would be nice if SQL Server reported all columns for the 'missing statistic' warning, but that is not how it works today, unfortunately.

A certain amount of trial-and-error may be necessary to identify the multi-column statistic you need to create to remove the warning. Note that multi-column statistics cannot be created automatically. Despite the warning, you may or may not find the quality of the estimate improves even if the missing statistic is provided.


unique constraint violated (ORA-00001) in concurrent insert I have a procedure that is called from concurrent transactions:

//Some actions here

INSERT INTO table1
                (table1_id, table1_val1, table1_val2, table1_val3)
              VALUES
                (gettablenewid('TABLE1'), val1, val2, val3);

INSERT INTO table1
                (table1_id, table1_val1, table1_val2, table1_val3)
              VALUES
                (gettablenewid('TABLE1'), val1, val2, val3);
INSERT INTO table1
                (table1_id, table1_val1, table1_val2, table1_val3)
              VALUES
                (gettablenewid('TABLE1'), val1, val2, val3);
//some other actions

Function gettablenewid code (id_table stores PKs for each table):

create or replace
    function        GetTableNewId(tablename in varchar2)
      return number is
      PRAGMA AUTONOMOUS_TRANSACTION;
      Result number;
    cursor c1 is SELECT ig.id_value+1 id_new
                              FROM id_table ig
                              WHERE ig.table_identifier = tablename
                              FOR UPDATE of  ig.id_value;
    begin
    for c1_rec in c1 loop
      UPDATE id_table ig
         SET ig.id_value = c1_rec.id_new
       WHERE current of c1 ;
       Result:=c1_rec.id_new;
    end loop;
    commit;
      return(Result);
    end GetTableNewId;

Occasionally insert statement fails with ORA-00001 for table1_id, I can't undestand why it happens.

@A.B.Cade you're right! I found another process that modified id_table concurrently for table_identifier = 'TABLE1'. Thanks to all for your help.


Ola Hallengren's Backup solution error message after moving files using cmdexec stops job, this disagrees with faq I am using Ola Hallengren's Maintenance script, and I am having an issue which seems documented in the FAQ.

For context, there was an issue where the existing SQL Server backups had to be moved to new share, and so the history in MSDB and the current location of the new backups would not match what exists now.

When running the default job that MaintenanceSolution.sql creates, "DatabaseBackup - USER_DATABASES - FULL" (with minor modifications to the parameters it runs on) I see the job now fails because it appears that deleting the files it expects would exist:

EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'\\UNC\Path\Server\Database...  Process Exit Code 1.  The step failed.

After the first error of this type, the entire job comes to a halt.

In the FAQ https://ola.hallengren.com/frequently-asked-questions.html , I found the following paragraph which addresses the issue I believe I am having:

  Why does my job stop after the first error?

  This issue is happening because you’re using T-SQL job steps. I recommend that you use CmdExec job steps with sqlcmd and the -b option. Then the job will continue after an error.

  You can use the MaintenanceSolution.sql script to create the jobs.

I double checked (because I thought this was the default that the solution configured) and based on what I see in SSMS it is (Type: Operating System (CmdExec)) and found that this was the case.

I also verified that the job is also using the -b option:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'\\UNC\Path\', @BackupType = 'FULL', @Verify = 'N', @CleanupTime = 750 , @CheckSum = 'Y', @LogToTable = 'Y'" -b

I was quickly running through the stored procedure and found that it simply assigns any error from the xp_delete command to a variable and raises it as an error (completely bog standard sql), so is there anything missing to make this work without modifying his solution?

Thanks!

After further digging (and almost emailing Ola) I found the source of my
confusion.

After querying the CommandLog table (@LogToTable = 'Y') found that while the TSQL job steps were reporting the error, the job actually completed, logged the commands to the table as successful (and of course the files existed on disk.)

It looks like not only do TSQL job steps stop reporting on the first error, which is referenced in a different part of the FAQ.

If you are seeing that the SQL Server Agent History reports a failing job, verify that the same failure is reported in the master.dbo.CommandLog table, and ensure you have @LogToTable = 'Y' specified.

Ola's job is behaving as described in the FAQ, and the reference to TSQL job steps was the hint I needed.


What is the difference between  All and Not In Could you please tell me the differences between

select CustomerId from Customer Where TerritoryId <> All(select TerritoryId from Salesperson)

select CustomerId from Customer where TerritoryId NOT IN (select TerritoryId from Salesperson)

There is no difference in result but there is a bit different semantics.

X [comparison] ALL(set) mean that set is empty or the comparison is TRUE for each entry in the set.

X NOT IN (set) means that X does not belong to the set.

While [comparison] is "not equal", both forms are equivalent. But for other comparisons it may be different.


Syntax for correctly capturing current user in a trigger So this worked as a test:

CREATE TRIGGER `Metrosky_spotters_2020_au`
BEFORE UPDATE
ON `Metrosky_spotters_2020`
FOR EACH ROW
SET NEW.trnmswid = 5000;

Now I'm getting syntax error substituting "5000" with  CURRENT_USER()

per chapter 6.4 MySQL 8.0 reference this would be correct reference...but not allowing the info to be passed?

I don't understand how you have found a problem.

select CURRENT_USER();

| CURRENT_USER()                |
| :---------------------------- |
| fiddle_XQKHLETMKAXKBKBXJWXJ@% |

CREATE TABLE test (id INT, user VARCHAR(64));

CREATE TRIGGER tr_bi_test
BEFORE INSERT
ON test
FOR EACH ROW
SET NEW.user = CURRENT_USER();

INSERT INTO test (id) SELECT 1;

SELECT * FROM test;

id | user
-: | :----------------------------
 1 | fiddle_XQKHLETMKAXKBKBXJWXJ@%

db<>fiddle here

Search the uncorrectness elsewhere.


Can I load a CSV into a stored procedure that inserts into multiple tables? OK, an example is often easier to follow, so here is my procedure:

delimiter //
CREATE PROCEDURE mysql.insertTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime,  _hashid int,
  _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1))
begin
  START TRANSACTION;

  INSERT INTO MYDB.person (`first_name`, `last_name`, `hashID`)
    VALUES (_first_name, _last_name, _hashid);

  INSERT INTO MYDB.id_crimes (`id`, `id_status`, `criminal_status`, `hashID`)
    VALUES (_id, _id_status, _criminal_status, LAST_INSERT_ID());

  INSERT INTO MYDB.dob_state (`stateID`, `stateID_status`, `dob`, `hashID`)
    VALUES (_stateid, _stateid_status, _dob, LAST_INSERT_ID());

  INSERT INTO MYDB.timestamps (`create_time`, `update_time`, `hashID`)
    VALUES (_create_time, _update_time, LAST_INSERT_ID());

  COMMIT;
end//
delimiter ;

Now, what I'd like to know is whether I can load a CSV file to populate the tables directly using that procedure. I've seen workarounds for how to load to a single table, but this is to multiple tables. Any thoughts?

Here is my thought:

Create a table with all the fields that exist in the csv file.
Create a trigger (After insert on the table created in step 1) that calls your procedure that you already have, or let the trigger itself insert new values to the corresponding tables.
Load the CSV file to the table you created in step 1
The other tables will be populated automatically (Nothing to do here)
Drop the table that you created in step 1


In Microsoft SQL Server 2008, syntax generates the error "The Parallel Data Warehouse (PDW) features are not enabled." I have the following virtual column generated from an aggregate over a sorted partition,

MIN(picture_id) OVER ( PARTITION BY [360_set] ORDER BY picture_id ASC )

However, when I execute that, I get the following.

Msg 11305, Level 15, State 10, Line 12
The Parallel Data Warehouse (PDW) features are not enabled.

This is where it gets interesting though, without a sort order on the partition, it works:

MIN(picture_id) OVER ( PARTITION BY [360_set] )

And, further, ROW_NUMBER() a window function (not an aggregate function) works with an explicit order on the partition.

ROW_NUMBER() OVER ( PARTITION BY [360_set] ORDER BY picture_id ASC )

How come the desired statement doesn't work? Where is this documented? The version information was requested, this is what I in Help → About.

Microsoft SQL Server Management Studio          10.0.5512.0
Microsoft Analysis Services Client Tools        10.0.5500.0
Microsoft Data Access Components (MDAC)         6.1.7601.17514
Microsoft MSXML                                 3.0 6.0
Microsoft Internet Explorer                     9.10.9200.16635
Microsoft .NET Framework                        2.0.50727.5472
Operating System                                6.1.7601

The result from SELECT @@VERSION is Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64)   Aug 22 2012 19:25:47   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

The Parallel Data Warehouse (PDW) features are not enabled.

This is a parser bug that exists only in SQL Server 2008. Non-PDW versions of SQL Server before 2012 do not support the ORDER BY clause with aggregate functions like MIN:

Windowing function support was considerably extended in 2012, compared with the basic implementation available starting with SQL Server 2005. The extensions were made available in Parallel Data Warehouse before being incorporated in the box product. Because the various editions share a common code-base, misleading error messages like this are possible.

If you are interested, the call stack when the aggregate is verified by the parser is shown below. Because the aggregate has an OVER clause with ORDER BY, a check for PDW is issued:

This check immediately fails with a parser error:

Luckily, you do not need an windowed aggregate that supports ORDER BY framing to solve your code problem.


Calculating season timelines for a farm Long time lurker, first-time poster. I have been looking for a solution to this problem for a while and can't quite think of what to search for anymore.

I work for a farm and we have this farm management system that allows people to put in activities (such as planting/irrigating/etc...). I'm ultimately trying to calculate every activity day from planting so each field is comparable to each other. The current problem I'm running into is calculating the 'season' timeline as we might have multiple crop plantings in a field within the year.

BTW Postgres used as my DB

Essentially my table looks like this:

Ideally, I would like to somehow query this to create a date range seasonality table so I could cross-reference all other activities to see what "season" the activity falls under and as such can grab the correct planting date.

I'm thinking the table would look something like:

I assume some kind of log transform query for user activities on websites might work similarly to categorize time ranges, but I haven't found anything helpful yet.

Let me know if you have any thoughts/ideas!

Thank you!

It seems you need in simple

SELECT Paddock,
       Date_Time_Completed Season_Start,
       COALESCE(LEAD(Date_Time_Completed) OVER (PARTITION BY Paddock
                                                ORDER BY Date_Time_Completed),
                CURRENT_DATE) Season_End,
       Crop,
       YEAR(Date_Time_Completed)
           || '_'
           || ROW_NUMBER() OVER (PARTITION BY Paddock
                                 ORDER BY Date_Time_Completed) Season_Name
FROM source_table


How to Repair "Orphaned" Database in SQL Server 2016 So, in my haste, I ran a script to create a database but I did not select the
full script before running it. I ended up with a bunch of tables without a "parent" database. Then I created a database intending to somehow attach the tables to it. But I have no clue on how to do it.

Short of dropping all the objects one by one and rerunning the original script, or changing the name of the database and running the script, is there a way of attaching the tables to the database? Tables are empty, i.e., no rows values have been inserted into them yet. Maybe using ALTER statements to attach tables to database?

You cannot attach a table to a database.  You can get the system to make a script for CREATE TABLE which you would then have to run on the other database.  However, you already have that script so you might as well use it.

If you know the table names that were accidentally created and you don't want to delete them one by one then you can make a quick script to delete the "bad" copies from the other database.

IF EXISTS (SELECT * FROM information_schema.tables WHERE name = 'badtable')
 DROP TABLE badtable;

In 2016 you could also:

DROP TABLE IF EXISTS dbo.badtable;


How to traverse a tree in SQL? Sybase edition Possible Duplicate:
  Traversing tree-like data in a relational database using SQL

Whats the best way to traverse a tree in sybase?

Basically, what I want is to gather all the decedents of a certain node.
(aka the decedents of the root node is every node in the tree)

I've thought of two things I can do.

Use recursion on the java end and query multiple times. (obviously less than optimal)
Transform the naturally recursive problem into an iterative problem and use a while loop and one or two temp tables. This wouldn't actually be that bad.

Is there a better way?

It's bugging me that this question doesn't have at least one answer.  So...

You could use a mix of primary/foreign keys within the table per Joe Celko.  Alternatively, you might want to consider a closure table.

Generally, the table referencing itself seems to be the most common method for handling this.  Of course, you would have to consider looping.  Some DBMSs have CTEs which can use Recursion, so the looping is inherent in the CTE.  For Sybase, you'll probably have to throw a loop in code somewhere.