How to model a limited history of a table Context

I have 3 entities (user, shop and car), a car can only have one regNum, one shopId and one ownerId at a time, that's why they are embedded in the car table.

create table "user"
(
  id   bigint primary key,
  name varchar(40) not null
);

create table "shop"
(
  id   bigint primary key,
  name varchar(40) not null
);

create table "car"
(
  id      bigint primary key,
  ownerId bigint,
  regNum  varchar(8),
  shopId  bigint,
  price   numeric(10,2),
  constraint foreign key(ownerId) references "user",
  constraint foreign key(shopId) references "shop"
);

Problem

I want to keep history of the car's regNum, ownerId, shopId and eventually other future fields (but not necessarily all of them).
What's the best solution (scalability/performance/easeOfUse) ? I have found the ones below, maybe someone has faced the same problem, maybe there is another solution ?

Solution 1

I add as much history tables as I have fields to "watch".
This seems to be a normalized way to do the job but it also looks pretty complex to maintain, it's also more greedy as if I modify all fields at once (regNum, shopId and ownerId), I need to insert 3 records (one for each history and so on if I watch other fields later).

create table "carOwner"
(
  id        bigint primary key,
  carId     bigint not null,
  changedAt timestamp not null,
  ownerId   bigint,
  constraint foreign key(carId) references "car",
  constraint foreign key(ownerId) references "user"
);

create table "carShop"
(
  id        bigint primary key,
  carId     bigint not null,
  changedAt timestamp not null,
  shopId    bigint,
  constraint foreign key(carId) references "car",
  constraint foreign key(shopId) references "shop"
);

create table "carRegNum"
(
  id        bigint primary key,
  carId     bigint not null,
  changedAt timestamp not null,
  regNum    varchar(8),
  constraint foreign key(carId) references "car"
);

Solution 2

I keep history in a single table, which is a light snapshot of the car table at a given time. It seems easier to maintain but it's not precise as I cannot directly see what changed if I don't have previous record.

create table "carHistory"
(
  id        bigint primary key,
  carId     bigint not null,
  changedAt timestamp not null,
  ownerId   bigint,
  regNum    varchar(8),
  shopId    bigint,
  constraint foreign key(carId) references "car",
  constraint foreign key(shopId) references "shop",
  constraint foreign key(ownerId) references "user"
);

Not sure I understand your initial design:

create table car
( id      bigint primary key
, ownerId bigint
, regNum  varchar(8)
, shopId  bigint
, price   numeric(10,2)
,    constraint foreign key(ownerId) references user (id)
,    constraint foreign key(shopId) references shop (id)
);

If for example, a car_owner is not mandatory, you may consider moving it to a separate entity:

create table car
( car_id bigint primary key -- vin is a standard identifier for a car
, -- other mandatory car attributes
);

create table car_owner
( car_id bigint not null
      references car (car_id)
, owner_id bigint not null
      references owner (owner_id) -- user?
,   primary key (car_id)
);

etc for other non-mandatory attributes

The history for the owner of a car:

create table car_owner_history
( car_id bigint not null
, owner_id bigint not null
, begin_time timestamp not null
, end_time timestamp
,     primary key (car_id, begin_time)
);

car_owner_history can be maintained via triggers. When a car is assigned it's first owner

insert into car_owner (car_id, owner_id)
values (c_1, o_1);

This results in the following change in history

insert into car_owner_history (car_id, owner_id, begin_time)
values (c_1, o_1, t_0);

When car changes owner from o_1 to o_2

update car_owner set owner_id = o_2
where car_id = c_1 and owner_id = o_1;

The following change is made in history:

update car_owner_history
    set end_time = t_1
where car_id = c_1
  and owner_id = o_1
  and end_time is null;

insert into car_owner_history (car_id, owner_id, begin_time)
values (c_1, o_2, t_1);

When a car no longer has an owner end_time is updated to reflect that.

Who owned car c1 at time t_x?

select owner_id
from car_owner_history
where car_id = c_1
  and t_x between begin_time and coalesce(end_time, t_x)

What owners have car c_1 had?

select owner_id
from car_owner_history
where car_id = c_1
order by begin_time;


Error reading MySQL 5.5 binary log using mysqlbinlog I'm having some trouble with a replication setup I'm using. It has been working fine for months, and then over the weekend, the slave stopped reading updates from the masters binlog with an error Got fatal error 1236 from master when reading data from binary log: 'bogus data in log event'

When trying to read the relevant binary log using mysqlbinlog, I get the error shown below;

[root@slglcd-01] # mysqlbinlog ibm-pr-slglcd-01.000075 > /dev/null
ERROR: Error in Log_event::read_log_event(): 'Event too small', data_len: 0, event_type: 0
ERROR: Could not read entry at offset 1828: Error in log format or read error.
[root@slglcd-01] #

Frustrating! I have tried using various combinations of --start-position and --offset to try and get past the bad data, and nothing seems to be working...

What I'm after is a way of skipping (or removing) this error from within the binary log, creating a new binary log without the offending item, so that I can continue my replication.

I'm not worried about loosing the statement, this is a syslog collection database, and one missing row isn't going to hurt.

What I can't do is re-create the slave from the master, as the master uses the BLACKHOLE engine, and therefore has no real data stored...

If the worst comes to the worst, I will have to start from the next binary log in the sequence and loose the data left in the offending log.

Thanks in advance,
Dave

Since the binary log on the Master is corrupt, there is nothing else you can do. My condolences. Just skip to the next binlog:

STOP SLAVE;
CHANGE MASTER TO master_log_file='ibm-pr-slglcd-01.000076',master_log_pos=107;
START SLAVE;

Of course, 107 is the start position for MySQL 5.5 binlogs. If the Master is 5.1 (or 5.0.95), use 106. If the Master is prior to 5.1, use 98.

If Master BinLog Corruption is frequent, you may need to consider using a smaller size for the binary logs on the Master (perhaps 128M instead of the default 1G):

[mysqld]
max_binlog_size=128M

This will not stop corruption, but lowering the size it will minimize data loss from 1G to 128M.


How do you manage service account passwords? The security folks want all AD passwords to expire every three months. I'm really not excited about this, since I definitely won't remember, and I'll likely be on my boat, drunk, when they expire.

Question How often do you/should you change the passwords on your sql service accounts?

The security folks want all AD passwords to expire every three months.

Normally 1 year is pretty standard, 3 months might be expected in a more secure environment where virtual accounts can't be used.

  ... since I definitely won't remember, and I'll likely be on my boat, drunk, when they expire.

I thought it was in a submarine under the polar ice caps (Dilbert Reference)

  How often do you/should you change the passwords on your sql service accounts?

I'll reiterate that once a year is fairly standard, but it still isn't a great story. You have to change the account, there is a service restart required, and in general no one is happy about it except InfoSec.

This is why you should modernize by using Managed Service Accounts and/or Group Managed Service Accounts (or virtual accounts). In MSAs, the password is automatically rotated and is not known by anyone, gMSAs work a bit different but you can think of them the same as MSAs for use with multiple computer objects. The automatic password rotation does not require a service restart.


Any relation between SQL Server Service Name and Instance Name? My SQL Server's instance name is SQLEXPRESS and SQL Server Service Name looks like MSSQL$SQLEXPRESS. Is there any relation between instance name and service name? I'm trying to check SQL Server Service Status by name and I wonder can SQL Server Service name be different on another computers?

Yes, the service name is always MSSQL$<Instance Name> for a named instance and MSSQLSERVER for a default instance. I don't believe either can be altered or overridden, nor can I think of any reason why you'd want to.

You can enumerate the installed instances on a server via the registry, using Powershell for example:

Get-ItemProperty 'HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL'


Wordpress blog where pages are almost-always served from cache: SSD or more RAM? I am working on the design of a Wordpress blog which when finished will almost-always serve pages from disk cache and this is because all posts will be published in batch and then cached, with at least 5 hours between batches.
Database size is only 30MB at the moment as this is a new blog.
My server options are two:

a. Either a 4GB RAM server with an SSD or

b. An 8GB RAM server with a normal HDD.
Am I right in assuming that since pages will almost-always be served from disk cache, SSD is a better option?
Or maybe I should still go for more RAM?
I ask because years ago I worked on a blog generating almost 2m pageviews per month and when it was crashing we identified the heavy HDD usage as the problem. In fact after tweaking innodb_buffer_pool_size so as to use more RAM and less disk, we never had any more issues.
Thank you.
Edit By RolandoMySQLDBA
Please run this query
select var,concat(numunit,' ',unit) size from
(
    select var,format(num/power(1024,ex),2) numunit,SUBSTR(units,ex*2+1,2) unit
    from
    (
        select var,num,FLOOR(LOG(IF(num=0,1,num))/LOG(1024)) ex
        from
        (
            select variable_name var,variable_value*pagesize num
            from information_schema.global_status AAA,
            (
                select variable_value pagesize
                from information_schema.global_status
                where variable_name='innodb_page_size'
            ) BBB
            where AAA.variable_name like 'innodb_buffer_pool_pages%'
        ) AA
    ) A,(select 'B KBMBGBTB' units) B
) M;

and post its output so was can see the Buffer Pool's current usage.
Also, please run SELECT VERSION();
Edit
The blog is still on the development machine so I believe the first query is of no use. I can provide the result of the second though:
+------------+
| VERSION()  |
+------------+
| 5.6.16-log |
+------------+
1 row in set (0.03 sec)

It will be the very latest version when we go in Production.

If you are going to put MySQL in a wimpy environment (WIMP = Windows, IIS, MySQL, PHP/Perl/Python), your first priority needs to be RAM. Please get more RAM on the box.I suggest 32GB.

As for MySQL, you need to tune InnoDB as much as possible. Here is the InnoDB Architecture

InnoDB Architecture

Recommendation #1

By default, 25% of the Buffer Pool is used to process changes to secondary indexes. Thus, I suggest double the amount of data (you said 3-4 GB) as the Buffer Pool Size

innodb_buffer_pool_size = 8G

Recommendation #2

Since you have a dual core machine, split the Buffer Pool to prevent swapping

innodb_buffer_pool_instances = 2

Recommendation #3

You will need good read and write I/O threading

innodb_read_io_threads = 16
innodb_write_io_threads = 16

Recommendation #4

You need to the Log Buffer to be larger for increased write performance

innodb_log_buffer_size = 64M

Recommendation #5

You need the Log Buffer flushed often since you are in Windows

innodb_flush_log_at_trx_commit = 1

CAVEAT #1: The default for innodb_flush_log_at_trx_commit is 1 to maintain ACID compliance. Do not change this in Windows since Windows has the final say on disk caching.

CAVEAT #2: There is an option for InnoDB to cache data instead of letting the OS do it called innodb_flush_method. Unfortunately, the setting O_DIRECT is not available for Windows. If you switch to Linux, setting this option takes disk caching by the OS out of the equation.

Recommendation #6

Sequential and Random Reads from SSD can be the same or a mixed bag depending on the model SSD you get. If your IOPs exceed 10,000, you can change innodb_io_capacity to 10,000 and innodb_io_capacity_max to the maximum IOPs.

Summary

Get 32GB of RAM for the WIMP machine and use these setting in my.ini

[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 2
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 10000
innodb_io_capacity_max = (Maximum IOPs of the SSD)

Alternative to SSD Only

If you are will to configure it, there is a design that a Facebook Engineer uses to split up InnoDB tables and logs to lengthen the life of the SSD by writing the sequential stuff for InnoDB (Double Write Buffer, Log Files) to a fast HDD.

I have discussed this before

Feb 06, 2014 : MySQL on SSD - what are the disadvantages?
Aug 14, 2013 : How do I determine how much data is being written per day through insert, update and delete operations?
Jun 25, 2013 : Postgres Write Performance on Intel S3700 SSD
Jun 18, 2013 : How big can innodb_doublewrite_file grow?


Tracking Down Plan Cache Clearing I'm dealing with a database whose plan cache seems to be periodically clearing out by itself. As an example, in the span of about 2 minutes, the SQL plan cache went from 12.5k MB to 3.1k MB. At this time and shortly after, the number of compilations shot up dramatically as well. The rest of the cache shrinks as well, with everything seeming to get smaller by a large amount.

I've looked through the default trace and don't see anything obvious. At first, I thought it was related to a linked server due to a correlation of some SHOW_STATISTIC calls coming from it during the same minute as when the cache cleared out, but it seems that that's what happens when a linked server executes a query across the way as it needs statistics to use. I don't see any DBCC FREEPROCCACHE calls in the default trace logs for any of these events either.

I have the transaction log that covers the timeframe when one of these events happened, but I'm still unfamiliar with navigating it.

I haven't seen any evidence to suggest it's one of the ALTER DATABASE or sp_configure commands that will clear the cache either. There are no events in the SQL Server Log that happen at the time of this clearing out.

I've noticed in SQL Sentry that a number of times that this happened, another process on the SQL box was taking up ~10% of CPU, but it's not during all of the events so I'm not certain if that's worthwhile to look into for this.

At this point, I suspect that a lot of data was changed during each of these events, and the resulting statistic changes made many plans in the cache obsolete, so they were kicked out. I'm not certain how to best check this though, without bogging the system down with an expensive trace. Is there an easy way to determine this through the default trace and transaction log?

Another factor in this is that some of the queries have over a minute (!) compile time, and are requesting multiple GB of memory per execution, while using a small fraction of that. I suspect these are all related problems, but am trying to determine the cause of the cache clearing to minimize the minute compilation issues.

In summary:
* Plan cache is dropping considerably in the span of a minute.
* The log doesn't contain any mention of commands that will clear the cache.
* I have the transaction log and default trace contents for the timeframe of a few of these events.

This is on SQL Server 2016 SP1 CU2, running in 2012 compatibility mode. Version: 13.0.4001.0. The server has 300 GB of Memory available. I haven't seen the cache get much larger than 14 GB, but most of the time it is about 12.5 GB.

Any ideas on where to go from here on how to determine what's causing this to drop?

on SQL2014 SP1 changing sp_configure parameters seemed to solve problem :

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
exec sp_configure 'access check cache bucket count',512;
exec sp_configure 'access check cache quota',2048;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO


psql's \set command unable to save json file's content in a variable I am trying to below set command inside bash:

#!/bin/bash

psql -X -h localhost -U <dbuser> -d <dbname> <<EOF
CREATE TABLE cardtable(id SERIAL PRIMARY KEY, charcolumn VARCHAR(20) NOT NULL, cardcolumn JSON NOT NULL);

\set var `cat /tmp/jsonfile.json`
INSERT INTO cardtable(charcolumn, cardcolumn) VALUES ('USA', :'var');

EOF

Content of jsonfile.json:

{
    "OT": [
        {
            "CD": 40,
            "ED": "2013-12-23",
            "ID": [
                "daf52501-330a-48a-d40988fa7a-a8",
            ]
         }
     ]
}

for some reason, the content of "var" was just '{' !! As a result INSERT command treats it as if the json data is of in-correct format and throws a syntax error.

Can someone please help me in understanding why "\set" command is behaving like this?

FYI: same command when executed manually at the psql prompt ends successfully!!

There here-doc feature in bash treats backticks as command invocations, so it executes the cat /tmp/jsonfile.json, which wrongfully injects the json contents into the psql commands:

  If word is unquoted, all lines of the here-document are subjected to
  parameter expansion, command substitution, and arithmetic expansion,
  the character sequence \newline is ignored, and ‘\’ must be used to
  quote the characters ‘\’, ‘$’, and ‘`’.

You may quote the EOF word to remove all expansions, as in :

psql [...] << 'EOF'
... psql script...
\set var `cat /tmp/jsonfile.json`
...
EOF

That's the safest method when you don't need to inject anything from bash into the here-doc script.

Or, if any kind expansion in the here-doc string is needed, the above-mentioned characters must be quoted with backslash, as in:

myfile="/tmp/jsonfile.json"

psql [...] << EOF
... psql script...
\\set var \`cat $myfile\`
...
EOF


Finding optimal indices, different query competing indices My schema:

create TABLE "logs" (
    "id" serial not null default nextval('logs_id_seq'::regclass),
    "request" varchar(1024),
    "token" varchar(512) default NULL,
    "was_batch_request" bool not null default false,
    "created" timestamp,
    PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX "idx_logs_token_created" ON "logs" ("token", "created");
CREATE UNIQUE INDEX "idx_logs_created" ON "logs" ("created");

I've around 5.000.000 entries with varying token and I've two queries I regularly perform against this table. Think of token usually having 200 to 500 chars. There are about 600 distinct token in this table.

Query 1:

SELECT
    TO_CHAR("logs".created, 'YYYYMMDDHH24') AS date
  , COUNT(*)                                AS num
FROM "public"."logs" AS "logs"
WHERE "logs"."created" BETWEEN NOW() - INTERVAL '24 hour' AND NOW()
GROUP BY TO_CHAR("logs"."created", 'YYYYMMDDHH24')
ORDER BY "date" DESC
LIMIT 24

For this query I've an index on created.

Query 2

SELECT count(*)
FROM logs
WHERE token = 'token_600_chars_long'
      AND
      created >= NOW() - INTERVAL '1 day';

For this query I created an index on token,created.

Some observations:

Query 1 takes around 2s
Query 2 takes around 1s

The issue with Query 1 .. unknown. It uses the created index; if the created index does not exist, it takes as long as 5s.

The issue I see with Query 2: it does not use the token,created index but the created index.

However, when I do the following changes, Query 2 uses the token,created index:

I drop created index (but this slows Query 1 down to 5s)
I change the interval to e.g. 4 days and then it starts using the token,created index and finishes in 100-200ms.

Explain for Query 1:

QUERY PLAN
Limit  (cost=75793.01..75793.07 rows=24 width=8) (actual time=2077.100..2077.103 rows=23 loops=1)
  ->  Sort  (cost=75793.01..75814.66 rows=8662 width=8) (actual time=2077.098..2077.099 rows=23 loops=1)
        Sort Key: (to_char(created, 'YYYYMMDDHH24'::text))
        Sort Method: quicksort  Memory: 26kB
        ->  HashAggregate  (cost=75442.85..75551.12 rows=8662 width=8) (actual time=2076.830..2076.979 rows=23 loops=1)
              Group Key: to_char(created, 'YYYYMMDDHH24'::text)
              ->  Index Only Scan using idx_logs_created on logs ""logs""  (cost=0.44..72326.16 rows=623337 width=8) (actual time=0.244..1804.994 rows=876114 loops=1)
                    Index Cond: ((created >= (now() - '24:00:00'::interval)) AND (created <= now()))
                    Heap Fetches: 876143
Planning time: 0.968 ms
Execution time: 2077.309 ms

Explain for Query 2:

QUERY PLAN
Aggregate  (cost=70856.83..70856.84 rows=1 width=0) (actual time=640.327..640.327 rows=1 loops=1)
  Output: count(*)
  ->  Index Scan using idx_logs_created on public.logs  (cost=0.44..70795.30 rows=24610 width=0) (actual time=0.096..637.190 rows=30863 loops=1)
        Output: id, request, token, was_batch_request, created
        Index Cond: (logs.created >= (now() - '1 day'::interval))
        Filter: ((logs.token)::text = 'DazToken'::text)
        Rows Removed by Filter: 843711
Planning time: 0.381 ms
Execution time: 640.417 ms

In MySQL I could force certain queries to use specific, to my knowledge PostgreSQL does not provide such a think.

Is it possible to speed up the queries with the current schema?

The query planner seems to think that created is more selective than token.  Are your table statistics up to date?  Check the last_analyze column in:

SELECT  schemaname, relname, last_analyze
FROM    pg_stat_all_tables
WHERE   relname = 'logs'

To really force the issue, you can use a temporary table:

begin;
create temporary table tmp_logs (created timestamp);
insert tmp_logs select created from logs where token = 'token_600_chars_long';
select count(*) from tmp_logs where created >= NOW() - INTERVAL '1 day';
commit;

The first query only uses a condition on token, so the optimizer can only use an index that starts with token.


Why does it take so long to calculate an Execution Plan? One of our customers has just upgraded to a new server.

For a particular stored procedure the first time you execute it, is taking over three minutes to run. Subsequent runs are less than 1 second.

This leads me to believe that the initial three minutes are primarily taken up calculating the execution plan. Subsequent runs then just use the cached plan and run instantaneously.

On our test databases it is taking about 5 seconds to calculate the plan for the same procedure.

I don't see anything terrible in the plan itself - although I don't imagine its relevant as the plan shows how long it takes to run the query, not calculate itself.

The server is a 16 core with 24 gb memory. No heavy CPU or memory load occurs.

What is it that could be causing such a slow calculation only on a particular database?

What steps can I take to find the cause of the problem?

Edit

So I have managed to access the server and have run the query with SET SHOWPLAN_XML ON.

I can confirm that the CompileTime for the query is taking up 99% of the query execution time. The StatementOptmEarlyAbortReason is "TimeOut", on our test database with a copy of their database the reason is MemoryLimitExceeded.

I hate to answer my own question, especially since I have had so much help from others to work out the solution, but here goes.

The problem was due to some dud statistics in the database. Looking at the execution plan, the optimiser was expecting 11.5tb of data returned from the query. In reality is was receiving 87kb. I now know that huge mismatches between expected and actual rows returned is a sign that the statistics are out of date.

Simply running

exec sp_updatestats

forces the database to update the statistics for all tables.

This has taken the query execution time down from 3 minutes to 6 seconds. Everyones a winner!

Thanks for all the help guys. :0)


Get the Messages stream in an SSRS RDL Report So I'm trying to troubleshoot a particular SSRS RDL Report that literally takes forever to run (mostly because it has a mile of SQL to run).

My question is: Is there any way to retrieve the execution messages from a Dataset? (i.e. 15 row(s) affected).

My reasoning is that executing the SQL directly with static parameters tends to be much faster than using the RDL.

Note I'm not using the pagination thing that tries to keep all of the rows on one page, nor is the ending dataset all that large (a little less than 1500 rows), it's just the processing that's odd.

Ideally I just want the messages stream returned as a string to display as a tooltip.

Clarification: I'm primarily doing this to compare script portion performance between sections of code. Essentially, someone merged a bunch of stored procs into one, and it is my job now to figure out why it's suddenly taking 20x longer to run, but only when called on SSRS.

I was thinking of using a Global Temp table to store "log" entries, but I have no idea  how to control which datasets get executed in which order for rendering, so I haven't attempted that; though it seems almost like the most likely to work based on info below/

Removing what I had previously answered, since it was way off. Based on your comment regarding PRINT statements and getting the rows affected messages...

The rows affected message is something SSMS returns, you would generally not see that with datasets in SSRS that I know of.

I did some testing with PRINT statements and in SSDT-BI for SQL Server 2012 the statement is ignored and not returned in my case. As well, there are only so many places that you can actually put a tooltip so you might need to expand a bit more on how and where you want to use this information.

With regards to your specific report if you are having issues with a report rendering only 1500 rows there is no real way for us to assist in that unless you share it more about it, or the actual RDL file.


SQL Server 2012 error when exporting to Excel Let me start off by saying I know little and less about SQL. I received a .bak file from a client earlier today and they want me to move it to a new database (not SQL). I've created a SQL Server 2012 instance and restored the .bak file to a database and now it is .mdf file.

The last step of this process is getting it into either Excel or a .csv file (the inputs accepted by the new database system). I tried following the basic "right click and then select export and a destination" instructions, but I keep getting an error kicked back to me. I've copy/pasted the report below:

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Copying Rows (Error)
Messages
Error 0xc002f210: Preparation SQL Task 1: Executing the query "CREATE TABLE `aspnet_Profile` (
`UserId` GUID,
`..." failed with the following error: "The Microsoft Jet database engine could not find the object 'aspnet_Profile'.  Make sure the object exists and that you spell its name and the path name correctly.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 (SQL Server Import and Export Wizard)

- Post-execute (Stopped)

Seems like more often than not I run into issues trying to export to excel using the SSMS import/export wizard and I've never taken the time to learn and understand the quirks.

This doesn't necessarily answer your question, but might be a viable alternative.
Instead of exporting to excel, consider importing into excel. This YouTube video appears to demonstrate it:
http://youtu.be/VQrKg0GgZR0

Of course, if you need to export a lot of tables or need to do this often this is a rather tedious approach, but it has worked very well for me with a lot fewer headaches when only exporting a few tables.


Should I favor using a param or a scalar function result within a SELECT? I have a select statement in SQL Server that must compare a large set of rows against a single calculated date.

The calculation itself is negligible; just looking for the prior Monday. Given that the column this calculated date will be matched against is indexed, is there any difference whether I pass in the calculated date from application code as a parameter or use a scalar function to calculate the date?

Said another way: should I favor using a param or calling a scalar function, or are they basically equal in this scenario?

It sounds like a situation where you could potentially fall prey to - or benefit from - parameter sniffing. In a nutshell, SQL Server will use parameter/variable values when compiling an execution plan in order to determine optimal index usage (based on column statistics). Depending on the value that's passed in, you could get very different execution plans, and very poor performance if the "wrong" value is used later. (A nice article about it.).

I'd personally calculate the date within the procedure using a scalar function, unless there's a need for whatever is calling this procedure to specify the date itself. Then you could always use the scalar function in the procedure to fall back on a default value. As long as parameter sniffing isn't giving you surprising performance changes, it's mostly just a matter of application design.


How can I append new data from a particular mySQL table to an excel spreadsheet? We have a database trigger that automatically adds new clients to a table when they first contact the server, it takes a number of unique identifiers that the client provides on first contact and stores them, things like processor number, mac address, firmware version, etc...

We want this data exported to an excel file. The current plan is that an excel file will be created and new clients added as they appear on the system, then once a specific number of clients have been added (we'll usually do it in batches) we'll take a copy of that file and delete the one on the server until the next time we want to add new clients.

I'd be grateful if any of you could point me towards some resources to show me  how to do this with mySQL / Mariadb.
I've found many solutions for importing excel files into the database, and a few about exporting to excel automatically from Microsoft SQL Server, but nothing about doing the same in mySQL, although I'll admit I haven't been looking very long. Anyway, any pointers you may have would be greatly appreciated. Also, just thinking, comma delimited text files may be good enough too.

I've changed my plan, instead of outputting to an excel file I'm going to build a web app that presents the requested data to the user, provides the ability to modify it if required along with the functionality to export to excel or csv format.


How can I extract the perfmon counter "Memory: Pages/Sec" using t-sql? I've googled this and so far, no luck so turning to all you awesome gurus for help.

I'm trying to figure out if there's a way to get the perfmon counter "Memory: Pages/Sec" using t-sql as I'm trying to add it as a custom metric in our Ignite monitoring tool.  I've looked at the sys.dm_os_performance_counters DMV but didn't find it there.

Anyone know of a way doing this?  Thanks in advance :)

Following @Srutzky's example of just looking at page faults/sec (not pages/sec), you can do this:

DECLARE @before BIGINT, @after BIGINT;

SELECT @before = page_fault_count FROM sys.dm_os_process_memory;

WAITFOR DELAY '00:00:10';

SELECT @after = page_fault_count FROM sys.dm_os_process_memory;

SELECT PageFaultsPerSec = (1.0*@after - @before)/10.0;

It is likely that you can just have Ignite poll for the following metric:

SELECT page_fault_count FROM sys.dm_os_process_memory

And then set up alerts based on thresholds or deltas. But I still recommend you contact them to find out how to best implement a custom metric based on a column from an arbitrary DMV, which might be different from the perf counters DMV.


Can I disable the spyware in SQL Server? There are two titles for the spyware in SQL Server,

Customer Experience Improvement Program (CEIP)
Telemetry

I'm wondering how I disable them. You can see them referenced here,

My SQL Server says in their new license_Eval_Linux.txt

  We collect data about how you interact with this software. This includes data about the performance of the services, any problems you experience with them, and the features you use. This also includes data about your device and the network you use to connect to our services, including IP address, device identifiers, regional and language settings. It includes information about the operating systems and other software installed on your device, including product keys. By using this software, you consent to Microsoft’s collection of usage and performance data related to your use of the software.

The text in the new license_Std_Linux.txt reads,

  IMPORTANT NOTICE:  AUTOMATIC UPDATES TO PREVIOUS VERSIONS OF SQL SERVER. If this software is installed on servers or devices running any supported editions of SQL Server prior to SQL Server 2017 (or components of any of them) this software will automatically update and replace certain files or features within those editions with files from this software.  This feature cannot be switched off.  Removal of these files may cause errors in the software and the original files may not be recoverable.  By installing this software on a server or device that is running such editions you consent to these updates in all such editions and copies of SQL Server (including components of any of them) running on that server or device.

  The software enables Microsoft to collect telemetry by default. You may turn off telemetry at the Server and client levels by following the instructions at http://go.microsoft.com/fwlink/?LinkID=733886. There are separate controls to turn off telemetry at the Server level and the client level.  If you turn off telemetry at the Server level only, Microsoft will collect telemetry through the client level unless you also turn off telemetry at the client level.

Older versions of v.Next, had the following text (/usr/share/doc/mssql-server/LICENSE.TXT.gz)

  Customer Experience Improvement Program (CEIP) This software
  uses CEIP. CEIP automatically sends Microsoft information about your hardware and how you use this software. We do not use this information to identify or contact you. To learn more about CEIP, see http://go.microsoft.com/fwlink/?LinkID=733654&clcid=0x409.
  That link seems to be dead. I got into looking through the docs on what exactly is being sent, and I can't find any information about it or how to disable it.

I don't think Microsoft refers to it as "CEIP" in the official docs. I believe Microsoft is refering to "Customer Feedback for SQL Server on Linux". You can disable it like this,

sudo /opt/mssql/bin/mssql-conf set telemetry.customerfeedback false
sudo systemctl restart mssql-server.service

Additionally you can block the Telemetry Servers at the network layer

You can verify this by checking the conf file.


Design: How do you decide when items are related vs when they are attributes? I've been trying to figure this out for a while.  Let's say you have a person, for example.  An attribute of a person is his or her social security number, right?  But a person also has a social security number.  So, in an ER diagram, you could draw a square box for person and a square box for ssid, and you could connect them by a diamond, has.  Alternatively, you could draw a circle, ssid, and you could connect it to a square person box.

This isn't just true of ssid, though.  It's possible to draw either-or with concrete things, like a car, a pet, or a phone number, or with conceptual things, like a phone number, a friendship, or a mood.  So, where do I draw the lines?  Is the object of design just to get everything down on paper and looking nice, or should I be using a guide?

One rule of thumb is that if it is 'physical' (concrete as opposed to abstract) then it is an entity otherwise it is an attribute.

Another rule of thumb in ER modelling is that an table EITHER models an entity OR a relationship between entities but never both. There can be other kinds of table, of course e.g. it is sometimes conducive to put all attributes of the same type (e.g. colors) in a table, known as a lookup table (i.e. is neither an entity table nor a relationship table but is still valid and useful).


Change existing datadir path I want to change the datadir path in the my.ini file.

The existing datadir path is C:/ProgramData/MySQL/MySQL Server 5.1/Data/

The size of my C: drive is 30 GB and the MySQL data folder is occupying 25 GB. So I want to change the datadir to F:/naveen/data.

Is this possible?

My machine is running Windows Server 2003.

From your paths, I can safely assume the following:

You are running MySQL in Windows
You used the MySQL MSI to install

What you need to do is establish the file my.ini

When installing MySQL for Windows using the MSI, the location of my.ini is expected to be C:/ProgramData/MySQL/MySQL Server 5.1.

Please run the following in a DOS Window:

cd C:/ProgramData/MySQL/MySQL Server 5.1
dir *.ini

You will see some sample my.ini files. However, if there is no file named my.ini in that folder, you must create one. Whether one exists or not, please run this:

cd C:/ProgramData/MySQL/MySQL Server 5.1
notepad my.ini

If you are asked to create it, please do so.

Next, create the following entry under the [mysqld] group header in my.ini:

[mysqld]
datadir=F:/naveen/data

Save my.ini

Next, stop mysql from the DOS command line like this:

C:\> net stop mysql

Next, make a copy of the entire data folder in the new location

C:\> xcopy "C:\ProgramData\MySQL\MySQL Server 5.1\data" F:\naveen\data /s

Last step, start up mysql

C:\> net start mysql

Try logging into mysql. Once you can login to mysql successfully, run this command:

show variables like 'datadir';

If F:\naveen\data shows up as the datadir, CONGRATULATIONS, YOU HAVE DONE IT RIGHT !!!

Once you are satisfied all your apps hitting MySQL works, you can delete everything in C:\ProgramData\MySQL\MySQL Server 5.1\data\*

Give it a Try !!!


When does an execution plan not show in sp_WhoIsActive? I use Adam Machanic's excellent sp_WhoIsActive procedure to see activity and to log to a table to troubleshoot long running queries.

Why does the execution plan not show all the time in the results? Most of the time, the query_plan value is NULL. If I look at the SQL_TEXT column in the results, I do see the query with parameters, but no values, like below:

(@P0 nvarchar(4000),@P1 nvarchar(4000))
select blah from foo where a = @P0 and b = @P1

How do I get the execution plans when they don't display here? Why don't they display? Must I use Profiler or Extended Events? Would putting in dummy values into these parameters and running the query with the actual execution plan included generate the same plan that was actually used when I was logging?

I have verified with the vendor that they are using Hibernate 3.5.

How do I get the execution plans when they don't display here?

One option could be to capture the plan_handle and then look up the query plan afterwards for that plan_handle using the following query:

SELECT CONVERT(XML, query_plan) from sys.dm_exec_text_query_plan(
    0x0600050059E32C0/*Truncated for brevity, replace with your full plan_handle*/,
    DEFAULT,
    DEFAULT
)

You can capture the plan_handle using the query in the Reason #1 section below or you could make a small edit to sp_whoisactive to expose it in the results. (It's already captured in the intermediate #sessions table that is used within sp_whoisactive).

In each of the cases where I observed a NULL query plan, the query plan was available shortly afterwards (< 1 second in all my cases) via this method.

  Would putting in dummy values into these parameters and running the query with the actual execution plan included generate the same plan that was actually used when I was logging?

This could give you an idea of what the plan might look like, and if the RetrievedFromCache property of the query plan is true, you are very likely seeing the same plan that was being used.

  Why don't the query plans display in sp_whoisactive?

There are at least a few possible reasons why a plan might not show up, but I haven't observed this with the same frequency that you report, and it will be interesting to see any other answers that fill in the gaps.

Reason #1: sys.dm_exec_requests reports a statement_start_offset of -1

First, let's take a look at a simplified version of the section of sp_whoisactive that looks up query plans:

SELECT t.text,
    r.plan_handle,
    r.statement_start_offset,
    r.statement_end_offset,
    query_plan = (
        SELECT CONVERT(xml, query_plan)
        FROM sys.dm_exec_text_query_plan (
            r.plan_handle,
            r.statement_start_offset,
            r.statement_end_offset
        )
    )
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r
    ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1

And here is a sample result from my server:

Based on the documentation, it does not seem like -1 is an expected value for sys.dm_exec_requests.statement_start_offset. But it does seem to appear somewhat frequently, and the sp_whoisactive procedure does not make an attempt to handle this situation.

In my testing, I'm not even sure if it would be possible to do so; even when calling sys.dm_exec_text_query_plan with the default values (to find the whole plan, not the specific plan based on the offset), I found that no plan was provided when sys.dm_exec_requests.statement_start_offset was -1.

I think there is the possibility of a situation where the request exists, but the plan and current statement offset are not yet available. I do not know the full explanation for this behavior though. Since a plan_handle is available (at least in my cases with a NULL plan), I don't think it's the case that plan compilation is still happening.

Reason #2: It took more than 5ms to find the query plan

The full implementation of sp_whoisactive actually uses a cursor to look up each plan one at a time. There is a LOCK_TIMEOUT of 5ms to avoid delaying the entire procedure too much in case a lock is encountered when looking up a particular plan.

--Wait up to 5 ms for the SQL text, then give up
SET LOCK_TIMEOUT 5;
WHILE @@FETCH_STATUS = 0
...

However, the output for sp_whoisactive should show <timeout_exceeded /> in the query_plan field in this case. I've seen this a few times, so I think that a NULL value is more likely reason #1 in your case.

Reason #3: The query plan XML is too complex

This doesn't seem likely in your case if the query text is as simple as your example. However, it looks like there is also the possibility of a NULL query plan being shown due to the XML being too complex.

However, from looking through sp_whoisactive, it looks like this case is handled with a message Could not render showplan due to XML data type limitations. and instructions to convert the raw XML into a .sqlplan file.


How to grant execution rights on DBMS packages to a PDB user? I'd like to grant execution right on dbms_lock package to a local PDB user.

I have created new PDB with a local administrative user. I'm assuming I can use that local user for all management tasks in PDB. But when I try to grant execution right on dbms_lock to another local user I get ORA-01031: insufficient privileges.

I'm using Oracle 12c database app development VM.

The steps to re-produce the problem:

Create new PDB as CDB SYS user

Creating a PDB Using the Seed

create pluggable database test1
admin user test1admin identified by test1admin
roles = (DBA)
file_name_convert = ('/pdbseed/', '/test1/')
;

alter pluggable database test1 open;

Log in to PDB as test1admin and create new local non-administrative user

create user test1 identified by test1;
grant connect to test1;
grant execute on dbms_lock to test1;

Error starting at line : 3 in command -
grant execute on dbms_lock to test1
Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

I have used test1admin succesfully to grant several other privileges to th elocal users. I have also verified test1admin user has access to dbms_lock package. But it seems that dbms_lock-package (and btw dbms_crypto too) is different.

By default, noone is granted the EXECUTE privilege on DBMS_LOCK with grant option. Even the DBA role does not have the grant option. test1admin has access to DBMS_LOCK, but test1admin is not authorized to grant privileges on DBMS_LOCK to additional users. You need to connect as SYS to grant privileges on DBMS_LOCK to additional users.

Connect to the CDB root as SYS and switch to your pluggable database:

sqlplus / as sysdba
alter session set container = test1;

Or connect to the PDB directly:

sqlplus sys@test1 as sysdba

And issue the grant:

grant execute on dbms_lock to test1;

If you want test1admin to be able to grant privileges to DBMS_LOCK to others, then as SYS, issue the below:

grant execute on dbms_lock to test1admin with grant option;


What would be the better schema to represent this data? I suspect this is one of these questions to which there is not going to be a single definitive answer.  But I'm hoping to gain some insight into what might generally be considered best practice, most of the time anyways.  So, thanks in advance to those who weigh in on this.

I have two types of folders for storing records.  Personal Folders and Team Folders.

There are two ways that I could represent this in the database...

Option 1: Two separate and unrelated folder tables.

personalFolders( id, userId, title )
teamFolders( id, teamId, title )
records( id, folderId, folderType, title )

Option 2: A common folder table with two join tables

folders( id, title )
personalFolders( folderId->folders.id, userId )
teamFolders( folderId->folders.id, teamId )
records( id, folderId->folders.id, title )

My question is:  Generally, what schema would be considered best practice?

(My gut is telling me to go with option 2, because then I can keep the foreign key constraint in the records table.  But, are there situations where it might be better to go with option 1?)

Without knowing complete and exact requirements, we cannot design a complete data model. Based on my understanding from question, the following rules can be derived.

Each folder must have a folder type
Each folder type may contain more than one folder
Each folder must belongs to an owner
Each owner may contain more than one folder
Each owner must belongs to an owner type (either individual or team)
Each record must belongs to a folder
Each folder may contain more than one records

Here, I am not clear about about few things, such as:

Whether a record can be a part of more than one folder
Whether a folder belongs to both individual and team
Whether folder type other meaning such as archival or sales or something else
Whether is there any chance of adding more folder type or owner type (group, external, vendor, client, etc)

I am designed the below model, hoping future changes are adoptable without much changes in the design.

My suggestion is to start from conceptual data model, regardless of the size of the project. More questioned you answered on this process, more better the application will be.
I hope this answer will be helpful. Thanks!


From both tables I have change in ean field and in quantity field

To solve THIS task you must use the next query:

SELECT t1.asn, t1.ean, t2.ean oldean, t1.belegnr, t1.quantity, t2.quantity Oldqty
FROM temp_table t1, details t2
WHERE t1.asn = t2.asn
  AND t1.belegnr = t2.belegnr
  AND (    t1.ean != t2.ean
        OR t1.quantity != t2.quantity )

BUT...

Looking at your example data and desured result I see that you want to get the record pairs when ean OR quantity is changed.

You can obtain the shown result from shown source data using the next query:

SELECT t1.asn, t1.ean, t2.ean oldean, t1.belegnr, t1.quantity, t2.quantity Oldqty
FROM temp_table t1, details t2
WHERE t1.asn = t2.asn
  AND t1.belegnr = t2.belegnr
  AND (   (     t1.ean = t2.ean
            AND t1.quantity != t2.quantity )
       OR (     t1.ean != t2.ean
            AND t1.quantity = t2.quantity ))

fiddle


Why can't rows inserted in a CTE be updated in the same statement? In PostgreSQL 9.5, given a simple table created with:

create table tbl (
    id serial primary key,
    val integer
);

I run SQL to INSERT a value, then UPDATE it in the same statement:

WITH newval AS (
    INSERT INTO tbl(val) VALUES (1) RETURNING id
) UPDATE tbl SET val=2 FROM newval WHERE tbl.id=newval.id;

The result is that the UPDATE is ignored:

testdb=> select * from tbl;
┌────┬─────┐
│ id │ val │
├────┼─────┤
│  1 │   1 │
└────┴─────┘

Why is this? Is this limitation part of the SQL standard (i.e. present in other databases), or something specific to PostgreSQL that might be fixed in future? The WITH queries documentation says multiple UPDATEs are not supported, but does not mention INSERTs and UPDATEs.

All sub-statements of a query with CTEs happen virtually at the same time. I.e., they are based on the same snapshot of the database.
The UPDATE sees the same state of the underlying table as the INSERT, which means the row with val = 1 is not there, yet. The manual clarifies here:

All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" one another's effects on the target tables.

Each statement can see what's returned by another CTE in the RETURNING clause. But the underlying tables look all the same to them.
You would need two statements (in a single transaction) for what you are trying to do. The given example should really just be a single INSERT to begin with, but that may be due to the simplified example.


Install Only Pgbench and Not PostgreSQL Server I'm looking to benchmark some custom PostgreSQL statements using pgbench but my problem with this is that upon trying to install pgbench it tries to install PostgreSQL server. The pgbench utility package is documented here

The machine in question is a production box (Ubuntu) and I have refrained from installing any extra libraries that are not needed.

Is there a way to only install pgbench without completely installing the PostgreSQL package which includes PostgreSQL server?

You can't install the pgbench utilty without installing some required packages. These can be seen in the link you provided in your question:

Package: postgresql-contrib-9.3 (9.3.21-0ubuntu0.14.04 and others) [security]

Each package that has a depends (red dot) in front of it, is required to install the package you are installing.

In your case the postgresql-contrib-9.3 package depends on:

libc6
libossp-uuid16
libpq5
libssl1.0.0
libxml2
libxslt1.1
postgresql-9.3
zlib1g

If you follow the packages you will find addtional dependencies. The postgesql-9.3 package for example depends on:

...
postgesql-client-9.3
postgesql-common
-...

This goes on and on until all the prerequisites are met, so that your desired  package can be installed.

Your question

  Is there a way to only install pgbench without completely installing the PostgreSQL package which includes PostgreSQL server?

My answer

No it is not possible to install the pgbench utility contained in the postgresql-contrib-9.3 package without installing additonal packages, because of the dependencies.


generate_series : how to remove or no generate the last one I use this function for generating time slots. It's work well. But, there is a little problem.

My business opens at 10:00 and close at 12:00.

-- generate slots

CREATE OR REPLACE FUNCTION slots(start_date TIMESTAMP, end_date TIMESTAMP, delay INTERVAL) RETURNS SETOF TIMESTAMP AS $$
BEGIN
RETURN QUERY SELECT * FROM generate_series(start_date::timestamp, end_date, delay);
END
$$
LANGUAGE plpgsql;

-- call the function

SELECT slots('2019-12-19 10:00', '2019-12-19 12:00', '30 mins')

-- Results

2019-12-19 10:00:00
2019-12-19 10:30:00
2019-12-19 11:00:00
2019-12-19 11:30:00
2019-12-19 12:00:00

As you can see, the last generated slot is not available, because of my business close at 12:00. How can i remove or not generate this one?

CREATE OR REPLACE FUNCTION slots(start_date TIMESTAMP, end_date TIMESTAMP, delay INTERVAL) RETURNS SETOF TIMESTAMP AS $$
BEGIN
RETURN QUERY SELECT * FROM generate_series(start_date, end_date - delay, delay);
END
$$
LANGUAGE plpgsql;

Last timeslot is not generated even it is partial (not empty at all) - i.e.

SELECT slots('2019-12-19 10:00', '2019-12-19 12:10', '30 mins')

will not generate '2019-12-19 12:00' timeslot because it has 10 mins length.

If such partial timeslot must be generated, then substract '1 second'::interval instead of delay from end_date.


Full Text Index and database structure First forgive me, I am not a DBA and our company really doesnt have one. I am a developer and Googling for answers is just not helping any more. Thanks in advance.

My question is on optimal structure for our database. We have a website that allows users to search and find PDFs easily. Mostly its just meta data searching but we do allow full text searching (All words, Any Words, Exact Phrase).

There are ~3 million PDFs (growing daily) at about 2.3 TB in size. These are stored in tables corrisponding to date (File1980, File1990, File2000, etc) using VARBINARY(MAX). We didnt make it a single table because our system admin was having trouble with backups. Each of the file tables (File1990,etc) has it's own MDF file.

Our website is using Microsoft Dynamic Data and Entity framework. We search across the PDFs using one of the table value functions below that joins with the rest of the LINQ query.

ALTER FUNCTION [dbo].[udf_ContainsSearch]   (@keywords nvarchar(4000))
RETURNS @containsTable TABLE
(
    [key] [uniqueidentifier] NOT NULL, --PRIMARY KEY NONCLUSTERED,
    [rank] [int] NOT NULL
    PRIMARY KEY NONCLUSTERED ([key], [rank])

)

AS
BEGIN
    insert into @containsTable

    select [key],[rank] from ContainsTable(File2010,Content,@keywords)
    union
    select [key],[rank] from  ContainsTable(File2000,Content,@keywords)
    union
    select [key],[rank] from  ContainsTable(File1990,Content,@keywords)
    union
    select [key],[rank] from  ContainsTable(File1980,Content,@keywords)
    union
    select [key],[rank] from  ContainsTable(File1970,Content,@keywords)

    RETURN
END

ALTER FUNCTION [dbo].[udf_FreetextSearch]   (@keywords nvarchar(4000))
RETURNS @containsTable TABLE
(
    [key] [uniqueidentifier]  NOT NULL, --PRIMARY KEY NONCLUSTERED,
    [rank] [int] NOT NULL
    PRIMARY KEY NONCLUSTERED ([key], [rank])
)

AS
BEGIN
    insert into @containsTable

        select [key],[rank] from FreetextTable(File2010,Content,@keywords)
        union
        select [key],[rank] from  FreetextTable(File2000,Content,@keywords)
        union
        select [key],[rank] from  FreetextTable(File1990,Content,@keywords)
        union
        select [key],[rank] from  FreetextTable(File1980,Content,@keywords)
        union
        select [key],[rank] from  FreetextTable(File1970,Content,@keywords)

    RETURN
END

We have been getting timeout issues on the web site and have been looking into performance. Running the database tuner and adding some indexes on a weeks worth of queries helped but not enough.

Now the questions:

Is there a better way to structure the data that would give better performance?

We use professional SQL Server 2008, would Enterprise help us in anyway?

Is there a better way for the full text search portion?

Did you try to replace the insert..union statements with separate insert statements?

For example:

insert into @containsTable select [key],[rank] from ContainsTable(File2010,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File2000,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File1990,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File1980,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File1970,Content,@keywords)

It could be as simple as the increased processing cost of union plus the increased memory usage from having 5 processes open at the same time to use them on that insert, slowing down your UDF.

(Also, since you're querying from - supposedly - unrelated data tables, the 'distinct' part of union should be unnecessary.)


Backup Failure on SQL TDE Database with Maxtransfersize Specified for Compression Recently, my company has upgraded to 2016 and enabled TDE on all of our databases. Additionally, we have the production databases replicated on high availability servers.

Our backups are taken from the HA server and use the maxtransfersize hack in order to compress the backups.

All of the backups are successful, save one, where the following error is spit out by our monitoring software:

  Error: 3624, Severity: 20, State: 1.
  A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.

In the server log there are a few more details at the point of failure:

  input buffer 70 bytes
  ProcessID: 1884
  SPID:200
  Expression: m_inputQueue.head()==null
  location: backuplog.cpp 1564

This is my backup command:

backup database @DBName to disk = @Path
  with noformat, noinit, name=@Name, skip, norewind, nounload,
  compression, stats=10, copy_only, MAXTRANSFERSIZE = 65537

Has anyone run into a similar problem before?

I've used a few different values for maxtransfersize, albeit in a kinda random manner, picking from a chart in TDE and Backup Compression: Together At Last by Erik Darling.

Not using TDE is not an option because it contains sensitive data. Not being compressed may be an option if we have the drive space.

Community wiki answer

When I got back from lunch the backup completed successfully when maxtransfersize was not specified. After seeing it fail all morning, and now having one successful, uncompressed backup, it seems as if the failure is happening when it gets to the log file.

  The error you're hitting is a sanity check (it seems) for having consumed all of the vlf which is why it errors there. I cannot tell you why it is failing the check. – Sean Gallardy Jan 3 2017

Thanks for the hint. The number of VLFs was the root of my problem. This particular database had CDC issues which meant the transaction log wasn't being cleared out on backups. It had grown to over 300 GB. Once I disabled CDC, I was able to flush the log and give it a better growth plan. The compressed backup works again.


How to fix all duplicate key value violates unique constraint I have a Postgres database that I created with a Django application. When I transferred it over, it never reindexed anything and it gives me errors trying to do this. It seems as if there are lots of tables that have this issue. Is there a way I can use this code that I found on another question and use it for all sequences and tables?

SELECT setval('tablename_id_seq', (SELECT MAX(id) FROM tablename)+1)

The code you display is not related to "reindexing". (Compare: REINDEX). It gets a sequence behind a serial column (or IDENTITY column in Postgres 10 or later) back in sync - by setting it to the maximum existing ID. Consider this query instead:

SELECT setval('tablename_id_seq', max(id)) FROM tablename;

No subselect needed, no off-by-one error to fix (the next value returned from the sequence is incremented by default).
Or, better yet, you don't have to provide the name of the sequence:

SELECT setval(pg_get_serial_sequence('tablename', 'id'), max(id)) FROM tablename;

Details:

How to reset postgres' primary key sequence when it falls out of sync?


Scripting SQLite with dot commands Is it possible to write scripts that contain SQLite dot commands ( vis. .read file.sql; .separator ,; .import file.csv; )?

I'm building and repeatedly rebuilding an SQLite database and need to type in roughly twenty four dot command statements every time I rebuild the database. It would be really nice if I could put those commands in a script and have SQLite read them.

Is there a way to put dot commands ( not SQL statements ) into a script and have SQLite run them?

I'm on Mac OS X using bash.

I searched the Internet for an answer last night and found nothing. Of course, today after posting this question I try again--and find an adequate response almost immediately.
The gist of it? Put your commands in a text file and direct it to SQLite using the input file descriptor, or just script everything in a bash script.
First Method:
sqlite3 database.db < commands.txt
Second Method:
#!/bin/bash --
sqlite3 -batch $1 <<"EOF"
CREATE TABLE log_entry ( <snip> );
.separator "\t"
.import logfile.log log_entry
EOF

And then on the command line:
import.sh database.db


Does SQL Server 2014 Express, with Advanced Services, actually support full text search? I've installed SQL Server 2014 Express edition, with advanced services. I was hoping to try out the full-text search features. I read here that full text search is supported by the 2014 express edition. But, after installing and trying to create a full text index, I get an error stating that full text indexes are not support in this version of sql server.

Are full text indexes actually supported or not? Is there a chance I'm installing the wrong version (not advanced services)? I've uninstalled and re-installed two times just to be sure, but same issue both times. I'm positive I'm installing using the advanced services installer.

I'm on Windows 7, 64-bit.

There is already an existing answer posted by Aaron Bertrand for SQL Server Express 2012.

First install SQL Server 2014 Express with Advanced Services as you did. Then read Aaron's instructions at:

https://stackoverflow.com/questions/10407337/express-with-advanced-services-cant-create-full-text-index

The short version is that the user interface does not behave properly when setting up full text.  But you can enable and set up Full Text Indexing and Search through T-SQL code.


Galera Cluster Setup - Primary and Secondary Site Scenario I'm very new to Galera Cluster and is exploring a potential setup with reasonable resiliency to node failure and network failure. Looking at the very bottom part of this documentation, the Weighted Quorum for a Primary and Secondary Site Scenario is quite promising. For ease of reading, I've extracted the setup from the document as follows:

  When configuring quorum weights for primary and secondary sites, use
  the following pattern:

Primary Site:
  node1: pc.weight = 2
  node2: pc.weight = 2

Secondary Site:
  node3: pc.weight = 1
  node4: pc.weight = 1

  Under this pattern, some nodes are located at the primary site while
  others are at the secondary site. In the event that the secondary site
  goes down or if network connectivity is lost between the sites, the
  nodes at the primary site remain the Primary Component. Additionally,
  either node1 or node2 can crash without the rest of the nodes becoming
  non-primary components.

But there seems to be two drawbacks:

If there are two failed nodes and one of them happened to be on the primary site, the quorum will be <= 50% and the remaining two nodes will become non-primary components.
Despite pc.weight is a dynamic option that can be changed while the server is running, flipping between primary site and secondary site requires modification on all nodes, which is a bit troublesome.

So I've come up with another idea in mind - leave the weight as 1 for all nodes, and in the primary site add a Galera Arbitrator. In this case:

The primary site will remain the Primary Component on network issue,
just like the original setup.
The cluster still functions even if two nodes failed.
Flipping between primary and secondary site just require a move of the Galera Arbitrator.

May I know if there's anything wrong with my idea, or if there will be any practical difficulties? Appreciate if you can share your thoughts with me.

"Weighting" was added late in the game, when they realized that a 2-datacenter setup was too vulnerable.  (3 datacenters is resilient, and can use garbd in one of them.)  The example you quote is resilient to any single server, datacenter, or network outage.

As I read the last sentence of the quote, node1 or node2 died but the other three nodes are alive and talking to each other.  That is, there is a Quorum, and the system is still reliable.

However, I agree that the sentence is ambiguous -- It can be read that after the network died, node1 or node 2 died.  This leaves three clumps:  (node1), (node2), (node3,node4), each with a weight of 2.  None should be considered "primary" because none has Quorum.

You bring up garbd, yet it is not in the example??  And where would you put it?

You should not be changing the configuration while the system is hobbled -- you should be fixing the broken components.

The main goal of is to allow a single point of failure -- a single node, the network, a data center.  It would take a really large and complex system to survive two failures.  For example, I think it would require 5 datacenters to survive 2 network failures.

So, focus on a single point of failure.


How to enforce that a parent must have at least one child when the PK is composite? Consider the following business domain:

An Airline has a unique airline id (aid) and contains zero-or-more Planes.
A Plane has a unique plane id (pid) in the Airline it flies for (but planes from different Airlines can have overlapping ids).
Each Plane has one-or-more Seats.
A Seat has a unique seat id (sid) in its plane (but Seats from different Planes may have overlapping ids).

My attempt so far
Here is my attempt at solving this:
CREATE SEQUENCE planes_seq;

CREATE TABLE Airlines (
    aid INTEGER PRIMARY KEY DEFAULT nextval('planes_seq')
);

CREATE TABLE Planes (
    aid INTEGER REFERENCES Airlines(aid)
,   pid INTEGER
,   PRIMARY KEY(aid, pid)
);

CREATE TABLE Seats (
    aid INTEGER
,   pid INTEGER
,   sid INTEGER
,   PRIMARY KEY(aid, pid, sid)
,   FOREIGN KEY(aid, pid) REFERENCES Planes(aid, pid)
);

ALTER TABLE Planes ADD CONSTRAINT fk_seats FOREIGN KEY(aid, pid)
REFERENCES Seats(aid, pid);

However, it fails because the final ALTER TABLE is illegal since the pair (Seats.aid, Seats.pid) is indeed not unique.
Questions

How could I enforce the "a plane has at least one seat" constraint?
Is this scheme in 3rd Normal Form?

PS: I am new to SQL and this is a (small part) of a homework assignment. I have tried following the parent-with-at-least-one-child example from another DBA question (Constraint to enforce "at least one" or "exactly one" in a database) but there are way too many tricks there that I cannot understand (multiple WITH queries, a RETURNING clause and so on). It looks to me like there must be a simpler way to do it.

The CTEs (WITH and RETURNING) are there to avoid the use of deferred constraints.

It is simpler conceptually to use deferred constraints and for a homework assignment, I suggest you start with using them. Example, with comments where it differs from yours:

CREATE SEQUENCE planes_seq;

CREATE TABLE airlines (
    aid INTEGER PRIMARY KEY DEFAULT nextval('planes_seq')
);

CREATE TABLE planes (
    aid INTEGER NOT NULL REFERENCES airlines (aid)
,   pid INTEGER NOT NULL
,   sid INTEGER NOT NULL                -- we add a default seat
        DEFAULT 1 CHECK (sid = 1)       -- in every plane
,   PRIMARY KEY (aid, pid)
);

CREATE TABLE seats (
    aid INTEGER NOT NULL
,   pid INTEGER NOT NULL
,   sid INTEGER NOT NULL
,   PRIMARY KEY (aid, pid, sid)
,   FOREIGN KEY (aid, pid) REFERENCES planes (aid, pid)
);

ALTER TABLE planes ADD CONSTRAINT fk_seats
    FOREIGN KEY (aid, pid, sid)
    REFERENCES seats (aid, pid, sid)
    DEFERRABLE INITIALLY DEFERRED ;      -- the DEFERRABLE is important

Now we can add some data:

INSERT INTO airlines (aid)
VALUES (1) ;                      -- our 1st airline

BEGIN ;
    INSERT INTO  planes      -- its 1st plane
        (aid, pid)           -- notice: sid is added by default as 1
    VALUES
        (1, 1) ;

    INSERT INTO seats
        (aid, pid, sid)
    VALUES                    -- 3 seats in the 1st plane
        (1, 1, 1),
        (1, 1, 2),
        (1, 1, 3) ;
COMMIT ;

We need to insert new planes (and their seats) inside a transaction, otherwise it would fail.

DEFERRABLE means that we can "defer" the check of the FOREIGN KEY constraint until the end of the transaction (not deferred constraints are checked immediately after insert/update/delete statements.) If you try to insert only in planes, it will fail.

INITIALLY DEFERRED means that we want to be "deferring" the foreign key constraint now, as soon as it is created. This could have been done later (you can set and unset constraints from deferred, if they have been defined as DEFERRABLE).


Join 3 tables, aggregate over date field and subtract sum of two columns I have 3 tables and the they have the following columns.

shipments: shipment_id (PK), shipment_date (date)

invoices: invoice_id (PK), shipment_id (FK), amount (float)

bills: bill_id (PK), shipment_id (FK), amount (float)

Each shipment may have multiple invoices and bills. The net revenue of a month is defined as the sum of all bill amounts subtracted from the sum of invoice amounts in that month (derived from shipment_date). While a whole shipment may span across multiple months, we're only concerned about net revenue of the month as an aggregate.

Is there a way to calculate net revenues of all the months (depending on the data) with a single SELECT query?

Ignore the database design. And I'm trying the following query.

SELECT
    u.`month`  AS `month`,
    FORMAT(SUM(u.`amount`),2) AS `net_revenue`
FROM
    (SELECT
        i.`amount` AS `amount`,
        DATE_FORMAT(s.`shipment_date`, "%m/%Y") AS `month`
    FROM
        `invoices` i
        LEFT OUTER JOIN `shipments` s
            ON s.`shipment_id` = i.`shipment_id`
    UNION
    SELECT
        (-1 * b.`amount`) AS `amount`,
        DATE_FORMAT(s.`shipment_date`, "%m/%Y") AS `month`
    FROM
        `bills` b
        LEFT OUTER JOIN `shipments` s
            ON s.`shipment_id` = b.`shipment_id`) u
GROUP BY u.`month`
ORDER BY u.`month` ASC;

This is the final query that worked, with help from Akina's comments.

SELECT
    DATE_FORMAT(s.`shipment_date`, "%m/%Y") AS `month`,
    FORMAT(SUM(u.`amount`),2) AS `net_revenue`
FROM
    (SELECT
        `amount`,
        `shipment_id`
    FROM
        `invoices`
    UNION ALL
    SELECT
        (-1 * `amount`) AS `amount`,
        `shipment_id`
    FROM
        `bills`) u
    JOIN `shipments` s
        ON u.`shipment_id` = s.`shipment_id`
GROUP BY `month`
ORDER BY `month` ASC;


Oracle 11g connectivity over windows based client-server net I have two PCs as follows: I want to create network for both PCs and share Oracle database from PC1

PC1 (windows 10 Pro 64-bit operation system) - Oracle 11g Installed
PC2 (windows 10 Pro 64-bit operation system)

I need SQL Developer connectivity and application connectivity. The application is developed with vb.net 2015. Is this possible?

If yes then please tell me how. Tried many things but not working.

Assuming that these machines are sitting in the same network domain, have no firewalls between them and that you're running with everything "as standard", then you should be able to access the Database Instance running on PC1 from PC2 using these parameters in SQL Developer:

Connection Type: Basic
Hostname:        PC1
Port:            1521
SID:             SID of database instance (ORACLE_SID from PC1)

For connecting your Application, check out https://www.connectionstrings.com/

If you're getting Oracle (ORA-?????) errors, add them into the question so we can [try to] diagnose the problem.


How to install Distributed Replay (dreplay.exe) for SQL Server 2017 In SQL Server 2014, dreplay.exe was available under "Management Tools – Basic" feature. However, in SQL Server 2017 that feature no longer exists (Enterprise edition) and it doesn't come with SSMS installer either.
Here is what available from SQL Server 2017 installer:

Did they move dreplay.exe somewhere else?

Any help is much appreciated!

Update 1.

It seems there is a bit of confusion around Distributed Replay and what exactly I am looking for. So I thought to include a diagram from this article:

Administration tool (dreplay.exe) is what I am looking for.

Controller (DReplayController.exe) is Distributed Replay Controller from feature selection screen. It is useless without Administration tool.

Client (DReplayClient.exe) is Distributed Replay Client from feature selection screen. It is useless without Controller.

Update 2.

OS: Windows Server 2016 Standard

SQL Server Management Studio version: 18.4 **

** (As this post suggests dreplay.exe came with SSMS 17.x, but got removed from 18.x)

SQL Server < Shared Components > version: 14.0.1000.169 ***

*** (displayed at Installation Type step or can be found in MediaInfo.xml Property Id="BaselineVersion" that is located in the root of disk with SQL Server installer)

Binn folder contents:

 Directory of C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn

18/11/2019  15:19    <DIR>          .
18/11/2019  15:19    <DIR>          ..
18/11/2019  15:19    <DIR>          DQ
22/08/2017  18:39           697,528 DReplayCommon.dll
22/08/2017  18:39            20,592 DReplayServer.tlb
22/08/2017  18:40            32,952 DReplayServerPS.dll
04/11/2019  15:25    <DIR>          Resources
04/11/2019  15:28    <DIR>          schemas
15/06/2019  11:09           602,848 SqlManager.dll
22/08/2017  18:47            60,088 SQLPS.exe
22/08/2017  18:48               379 SQLPS.exe.config
15/06/2019  11:09            29,472 sqlresld.dll
15/06/2019  11:09            29,264 SqlResourceLoader.dll
15/06/2019  11:08            59,672 SQLSCM.DLL
15/06/2019  11:08           133,200 SQLSVC.DLL
              10 File(s)      1,665,995 bytes
               5 Dir(s)  97,550,450,688 bytes free

@Randi Vertongen: You really find dreplay.exe in the location you mention just by installing the DReplayClient and the DReplayController from the SQL 2017 ISO? I just installed the Controller and the Client and it is definitely not there. As mentioned in my post in the MSDN Forum  it can be installed by installing SSMS 17.x which I am going to do now - but that cannot be the solution. I have opened a Connect Item back in the days - Maybe you would like to upvote it:
https://feedback.azure.com/forums/908035-sql-server/suggestions/38242585-where-is-dreplay-exe


Single quote paste problem MSSQL When I'm pasting text in to my query window from Office the single quotes are being changed to a backtick. I have been through all the options in both Office and SQL but I can't find the/a setting to stop this happening. Is there such an option?

What I believe you're talking about is Microsoft Office changing single quotes to Smart Quotes.

It's an Autoformatting setting that you can change through the Options menu in Microsoft Word.

Go to Options -> Proofing -> Autocorrect Options

There are two places to change this:

Under 'Autoformat', untick Replace -> Straight Quotes with Smart Quotes
Under 'Autoformat as you type', untick Replace as you Type -> Straight Quotes with Smart Quotes

Alternatively, stop copying SQL from Microsoft Word.

https://www.simple-talk.com/blogs/2011/11/09/how-to-stop-office-2010-changing-and-to-smart-quotes/


Auto scaling MongoDB as a service in Microsoft Azure I see a MongoDB service in Microsoft Azure but that deploys a VM and installs MongoDB in that. I am guessing this will not auto scale and also charge me for the entire VM and MongoDB management (not just for my usage).

Is there any managed MongoDB as a service available in Microsoft Azure which will:

a) Auto scale.
b) Charge me only for my usage.

I see MongoDB container image by Bitnami but I might have to deploy this on Helm.

What are my options here?

MongoDB Atlas in a managed database as a service officially provided by MongoDB and it is readily available in all the three (aws, azure, gcp) major cloud providers.

Automated MongoDB service on Azure


How can I tell if my tempdb database has a performance issue/need to add more data files? SQL Server works in database called tempdb to handle a lot of tasks. Query operations like joins and aggregations happen there. Online index rebuilds, order by, aggregate function, triggers are also done in tempdb.

How can I measure when should I add another tempdb data file? And how many tempdb data files are required?

Paul Randal explains how many tempDB files to create here Correctly adding data files to tempdb

  If your server has less than 8 logical cores (e.g. a one CPU server
  with 4 physical cores and hyperthreading enabled has 8 logical cores),
  use # tempdb data files = # logical cores, equally sized

  If your
  server has more than 8 logical cores, start with 8 tempdb data files,
  and add sets of four at a time, equally sized, until the contention is
  alleviated

Note that it is critical to first alter all existing tempDB files so that they are the same size and with the same AUTOGROW settings before you begin. Then create all new TempDB files with the same size and autogrow settings as the existing files.

As for do you currently have a problem? View your wait statistics and determine your top waits, if PAGELATCH_UP or PAGELATCH_EX are up there then you need to investigate further. Glen Berry has a good query for that included here SQL Server Diagnostic Information Queries for September 2016

When you notice a slow down on your SQL Server you should return sys.dm_exec_requests wait type and wait resource information, and keep an eye out for pagelatch_* wait types. You can then pass the wait resource through DBCC PAGE to determine the database and internal pages experiencing page latching. You can also use the very useful query by SQLSoldier here Breaking Down TempDB Contention (part 2) to better determine which, if any, internal database pages are experiencing issues and if adding additional tempDB files is the answer. Assuming that you are seeing pagelatches on the internal GAM, SGAM or PFS pages then you want to keep adding files, based on the above advice until the PAGELATCH_ contention reduces.


Multiple foreign keys with shared columns for weak entities this is my first question at stackexchange.

I have a parent table (P) with two child (CC and CD):

CREATE TABLE P
(
    A CHAR(3), B CHAR(2),
    CONSTRAINT PK_P PRIMARY KEY(A, B)
)

CREATE TABLE CC
(
    A CHAR(3), B CHAR(2),
    C CHAR(1),
    CONSTRAINT PK_CC PRIMARY KEY(A, B, C),
    CONSTRAINT FK_CC_P FOREIGN KEY(A, B) REFERENCES P(A, B)
)

CREATE TABLE CD
(
    A CHAR(3), B CHAR(2),
    D CHAR(1),
    CONSTRAINT PK_CD PRIMARY KEY(A, B, D),
    CONSTRAINT FK_CD_P FOREIGN KEY(A, B) REFERENCES P(A, B)
)

I want a table (T) with optional references to CC and CD:

CREATE TABLE T
(
    TId INT AUTO_INCREMENT PRIMARY KEY,

    -- more fields

    CurrentA CHAR(3), CurrentB CHAR(2),
    CurrentC CHAR(1),
    CurrentD CHAR(1),

    CONSTRAINT FK_T_CurrentCC
        FOREIGN KEY(CurrentA, CurrentB, CurrentC)
        REFERENCES CC(A, B, C),

    CONSTRAINT FK_T_CurrentCD
        FOREIGN KEY(CurrentA, CurrentB, CurrentD)
        REFERENCES CD(A, B, D)
)

So that if both records Current CC and Current CD are present, they must refer to the same parent P record.

This is the first option, but I intuitively think it may create some problems which I'm not really sure about.

I'm also considering a second option:

CREATE TABLE TAlternative
(
    TId INT AUTO_INCREMENT PRIMARY KEY,

    -- more fields

    CurrentCCA CHAR(3), CurrentCCB CHAR(2), CurrentCCC CHAR(1),
    CurrentCDA CHAR(3), CurrentCDB CHAR(2), CurrentCDD CHAR(1),

    CONSTRAINT FK_TAlternative_CurrentCC
        FOREIGN KEY(CurrentCCA, CurrentCCB, CurrentCCC)
        REFERENCES CC(A, B, C),

    CONSTRAINT FK_TAlternative_CurrentCD
        FOREIGN KEY(CurrentCDA, CurrentCDB, CurrentCDD)
        REFERENCES CD(A, B, D),

    CONSTRAINT CHK_CurrentCC_CurrentDD_Same_P
        CHECK
        (
            -- Parent not specified
            (
                (CurrentCCA IS NULL AND CurrentCDA IS NULL)
                AND
                (CurrentCCB IS NULL AND CurrentCDB IS NULL)
            )
            -- Same parent
            OR
            (
                (CurrentCCA = CurrentCDA)
                AND
                (CurrentCCB = CurrentCDB)
            )
        )
    )

Which would be better?

EDIT: I want the T table to be another entity with optional references to CC and/or CD, not a table to optimize (see the --more fields comment). Think about CC and CD being two kind of parts of a machine P that cannot exist without P (for example CC defines the positions inside the machine, and CD defines other kind of zones inside the machine), and T being a final product positioned in a position and/or zone.

Both solutions for T or T-alternate are considered denormalizations. Denormalizations optimize a datamodel that is normally in third normal form into a structure that is more convenient for select queries. The correct data already exists in CC and CD. T or T-alternate are duplicating the data. With duplicate (denormalized) data you have to make sure that data cannot get out of synch with its parent.

I think the first option is the better one. The constraints are simple and straight-forward. There is no check constraint on multiple fields with nulls. You need to make sure that any insert into CC or CD also inserts into T. After-insert triggers on CC and CD would automatically insert A,B,C,null into T or A,B,null,D into T.

One thing to plan for is that a parent has two children, each with many rows per parent. Lets assume a row in P has 3 rows in CC, 0 in CD. If you write a query over P inner joining to CC and CD, it shows zero rows. An left outer join (parent on left) shows 3 rows.

Lets assume CC has 3 rows and CD has 4 rows. Inner join and outer join queries would show 3*4 = 12 rows.

Lets assume a 3rd set of data. CC is the main child table with 20 rows per P. CD is a descriptive text field with 3 rows per P. You could reduce the resulting row to 20 per P with an aggregation function like Oracle 11g's LISTAGG over CD. This combines the D descriptive field into a comma separated list "Planes, Trains, Automobiles".

Having CCA and CDA fields in T-alternative increases the chances for data to get out of synch, since CCA might not equal CDA when something goes wrong in the code. The T-alternative is not the standard way of approaching this problem. The first option does have some challenges, but it is simpler, more standard, and more likely to hold up under production systems.


Sharded key-value store using MongoDB Would like to set up a key-value store that is sharded across multiple machines.

We are currently using MongoDB, is there a reason why we shouldn't use MongoDB for this purpose?

We also use Redis, however for this use case, we would like to use the hard drive and Redis is in-RAM only.

There are a number of reasons not to use MongoDB as a pure key-value store, and there are some reasons to consider it. Mongo is optimized as a document store - it indexes all the fields in a document, and has rich primitives for JSON objects and hierarchies. You can use it as a key-value store, but the single-threaded nature means you won't be getting good performance out of your hardware. Storing simple blobs removes a number of the benefits of Mongo. Mongo has algorithms where it splits data chunks as you insert, which can create lag. Monogo's system for re-partitioning is cumbersome, as well. The benefit of a key-value system is it should be really simple and really fast, so you can scale up and keep server and management costs down.

Other systems are more tuned for key-value use. You mention Redis, one of the best key-value stores, but the repartitioning/clustering in Redis is still alpha-level, and there is a requirement of DRAM. Some people build their own shard layers and partitioning layers on Redis - this is very common among some of the larger Chinese social networks.

Cassandra is sometimes used as a key-value store. This isn't the best use of Cassandra, as Cassandra's "super column families" provide rich indexing. Cassandra isn't as fast as databases written in C like Redis and Mongo, but does have strong clustering capabilities.

One store you should strongly consider in this area is Aerospike. Aerospike has very flexible cluster management - adding a single node by just bringing it up - as well as support for both DRAM and SSD/Flash - and easy replication for HA. It's in use at very high levels of scale by advertising platform companies who need huge key value stores. Aerospike has a free version that supports node sizes to 200G.

CoucheBase (was MemBase) is another system to look at for key-value use. It provides some clustering primitives, and is focused more around in-memory use.


Manual failover error, not in correct state I'm doing some tests to build one database with one mirror database and one witness.

so far, I'm failing in the first test,
I want to kill the witness, then the Main database, and then put the Mirror database working as Main database.

So when I killed the witness,and then the Main database and did

ALTER DATABASE [MyDataBase] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

but I got:

Msg 1455, Level 16, State 106, Line 1
The database mirroring service cannot be forced for database "MyDataBase" because the database is not in the correct state to become the principal database.

I've been watching this tutorial and in that video everything goes just has planed.

You can see here my databases and my query to see if I'm missing something.

I'm having the same problem with both principal and mirror up, I'm unable to force mirror to turn to principal.

What am I missing?

edit:
I can put the mirror database up with these commands

ALTER DATABASE [MyDatabase] SET PARTNER OFF
RESTORE DATABASE [MyDatabase] WITH RECOVERY

but if I do this I loose my mirroring settings, and when the other database comes up, is not connected to this one.

Your secondary database needs to be in a "Restoring..." state.  Can you verify that?  When you restore the database on the secondary server, it needs to be with NORECOVERY to put it in this state.


Is it possible to restore a database snapshot from a 64 bit system to a 32 bit one? I would like to perform a restoration test on a snapshot backup of my MariaDB database using a test server running on a 32-bit OS. My production server is a 64-bit system. I would like to know if the architecture of the OS makes a difference to the database files. Both servers are running the same version of MariaDB.

MyISAM [and] InnoDB data and log files are binary-compatible on all
  platforms having the same floating-point number format.

Source.

You can copy freely between those two platforms if you maintain the same vendor and version.

Obviously, the binary executables for 64 bits will not work on 32-bit.


How to combine a parent with all children in the same row SQL Statement Question: I have two tables PARENT and STUDENTS.

PARENT Table

ParentID | FName | LName | StudentID
---------+-------+-------+----------
       1 | John  | Doe   |        11
       1 | John  | Doe   |        12
       2 | Jane  | Doe   |        11
       2 | Jane  | Doe   |        12
       3 | Joe   | Dirt  |        13

STUDENT Table

StudentID | FName   | LName
----------+---------+------
       11 | Janet   | Doe
       12 | Jackson | Doe
       13 | Jane    | Dirt

Expected Outuput

ParentID | FName | LName | StudentID1 | Student1FName | StudentID2 | Student2FName |...
---------+-------+-------+------------+---------------+------------+---------------+---
       1 | John  | Doe   |         11 | Janet         |         12 | Jackson
       2 | Jane  | Doe   |         11 | Janet         |         12 | Jackson
       3 | Joe   | Dirt  |         13 | Jane          |       NULL | NULL

I would like to have the output of the two tables, but sometimes the parent might have more than two students and some might only have one.

How can I achieve the expected result set while still being flexible in the number of students per parent?

Note: I don't have a definite upper limit on the number of students per parent.

Here is one of the solutions, but in this particular case we have limited number of students per parent. I wrote the query for 3 students.

with cteParent ( ParentID, FName, LName, StudentID )
as (
              select 1, 'John', 'Doe',  11
    union all select 1, 'John', 'Doe',  12
    union all select 2, 'Jane', 'Doe',  11
    union all select 2, 'Jane', 'Doe',  12
    union all select 3, 'Joe',  'Dirt', 13
) ,
cteStudent ( StudentID, FName, LName )
as (
              select 11, 'Janet', 'Doe'
    union all select 12, 'Jackson', 'Doe'
    union all select 13, 'Jane', 'Dirt'
) ,
cteParentNumbered
as (
    select p.ParentID
         , p.FName
         , p.LName
         , p.StudentID
         , row_number() over (partition by p.ParentID order by p.StudentID) as StudentNum
    from cteParent p
)
select
       pvt.ParentID
     , pvt.FName
     , pvt.LName
     , s1.StudentID as Student1Id
     , s1.FName as Student1FName
     , s2.StudentID as Student2Id
     , s2.FName as Student2FName
     , s3.StudentID as Student3Id
     , s3.FName as Student3FName
from cteParentNumbered p
    pivot ( min (StudentID) FOR StudentNum IN  ( [1], [2], [3] /*... add more if needed */ ) ) AS pvt
    left join cteStudent as s1 on s1.StudentID = pvt.[1]
    left join cteStudent as s2 on s2.StudentID = pvt.[2]
    left join cteStudent as s3 on s3.StudentID = pvt.[3]
    --... you can add more if needed

To remove limit of students per parent you should build your sql query dynamically and run it via sp_executesql, but it has no practical use. Usually this handled by client application and/or report engine. For example it can be displayed in report as hierarchical list:

Parent 1

student 1
student 2
...
student N

Parent 2

student 1
student 2

...


How to share a unique value between tables efficiently [postgres] We are modeling the concept of a global "reference code" for some of the records in different tables in our system (e.g., ABC123).  A "reference code" needs to be unique, easy to say over the phone, and can refer to a handful of different entities in our platform (e.g., people or places).
The criteria is:

one pool of codes that is unique across different tables of records
each record must have a code (not null)

How do I enforce uniqueness of this value across tables (either via a foreign key or just the value itself?)
CREATE TABLE reference_codes (
    code public.citext NOT NULL
);

CREATE UNIQUE INDEX index_reference_codes_on_code ON public.reference_codes USING btree (code);

CREATE TABLE people (
  id bigint NOT NULL,
  reference_code public.citext NOT NULL
)

CREATE TABLE places (
  id bigint NOT NULL,
  reference_code public.citext NOT NULL
)

-- now that I have two tables with foreign keys,
-- how do I ensure they do not point to the same reference_code row?

In this case, both a person and a place could end up referencing the same code (which we do not want).  Any suggestions?  My internet search kung fu isn't turning up enough on its own.
I thought about perhaps a custom constraint that would check each table that is foreign keyed to the reference_codes table to ensure uniqueness, but it seems cumbersome (especially when more and more tables are added).
Or perhaps a materialized view which combines all the codes across tables?  I am not sure what would be best.

Use bigint values generated by a single sequence. Numbers are quite easy to say over the telephone.
Forget about the requirement of enforcing database-wide uniqueness. Unless someone manually messes with the data, the sequence will guarantee the requirement. The performance cost of enforcing such a requirement with database means would greatly outweigh its usefulness.
An alternative would be to have a unique alphabetic prefix for each table that you store in a "table of tables". Then each table has its own sequence, and you generate the primary key in a BEFORE INSERT trigger by concatenating the table's prefix with the sequence value. That would be slighly more expensive, but would lead to more pronouncable names.


Possible to query all SQL Server Indexes which have page locks enabled? Is it possible to run a SQL Server Query in order to view all SQL Server Indexes which have page locks enabled?

SELECT OBJECT_SCHEMA_NAME(object_id) AS object_schema,
       OBJECT_NAME(object_id)        AS object_name,
       name                          AS index_name
FROM   sys.indexes
WHERE  allow_page_locks = 1


how to change mysql table auto_increment primary key Now i have a table with Auto_increment id as Pri key, and exchange_id as key, How could I change the primary key to field [ticker]..?

The auto_increment field must be an index, or be the first column of one. If you try to just drop the primary key, MySQL will complain. You have to delete the auto_increment property before moving it:

mysql> ALTER TABLE mytable MODIFY id int, DROP PRIMARY KEY, ADD PRIMARY KEY (ticker);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytable;
+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| id                | int(11)      | YES  |     | NULL    |       |
| exchange_id       | int(11)      | YES  | MUL | NULL    |       |
| ticker            | varchar(32)  | NO   | PRI | NULL    |       |
| instrument        | varchar(64)  | NO   |     | NULL    |       |
| name              | varchar(255) | YES  |     | NULL    |       |
| sector            | varchar(255) | YES  |     | NULL    |       |
| currency          | varchar(32)  | YES  |     | NULL    |       |
| created_date      | datetime     | NO   |     | NULL    |       |
| last_updated_date | datetime     | NO   |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

If you want id to continue being auto_increment, you have to do also:

mysql> ALTER TABLE mytable ADD INDEX `id` (id), MODIFY id int auto_increment;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytable;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | int(11)      | NO   | MUL | NULL    | auto_increment |
| exchange_id       | int(11)      | YES  | MUL | NULL    |                |
| ticker            | varchar(32)  | NO   | PRI | NULL    |                |
| instrument        | varchar(64)  | NO   |     | NULL    |                |
| name              | varchar(255) | YES  |     | NULL    |                |
| sector            | varchar(255) | YES  |     | NULL    |                |
| currency          | varchar(32)  | YES  |     | NULL    |                |
| created_date      | datetime     | NO   |     | NULL    |                |
| last_updated_date | datetime     | NO   |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

Please make sure to create if with the right index (multiple, unique) and nullability (null, not null).

Please also note that, depending on the engine used and/or the queries to be performed, a character field for a primary key may be suboptimal.


Azure Sql Server VPN connection I saw a couple of questions already submitted 1 or 2 year ago, without a clear answer. I'd like to know if I can connect my private Network, or whatelse to an Azure Sql Server.

Point-to-Site VPN

I'd like to attach a VNET to my Azure Sql Server, Set up a Network Security Group and VPN GATEWAY and connect (with enterprise certificate or self signed) to my DB's directly from Visual Studio or Microsoft Sql Management studio.

I've solved with a Preview Solution and connected from the inside virtual network to Sql Server on port 1433.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-private-endpoint-overview

Next step: Certification Management of the VPN'S client.

Other Solution in Preview also....

https://docs.microsoft.com/en-us/azure/vpn-gateway/openvpn-azure-ad-client