MySQL year 2038 vulnerable I was studying Drupal and asked this question:

https://drupal.stackexchange.com/questions/45443/why-timestamp-format-was-chosen-for-users-created-field/45446#45446

I decided to check, how the CMS will behave after the end of Unix epoch. Set my local date to 01.01.2040 and rebooted.

To my surprise, the site didn't work at all. And the reason was, that MySQL was down. I tried to connect manually and got an error:

>mysql -uroot -ppass
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

I am using MySQL 5.5 on Win XP. Why such strange behaviour?

The rationale is probably based on which datetime representation is easier to handle for both MySQL and the OS (in this case, Windows XP)

DATETIME takes up 8 bytes
TIMESTAMP takes up 4 bytes

Their Data and Time Ranges would differ greatly. Note further differences in the MySQL Documentation

  The DATETIME type is used for values that contain both date and time
  parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD
  HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to
  '9999-12-31 23:59:59'.

  The TIMESTAMP data type is used for values that contain both date and
  time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to
  '2038-01-19 03:14:07' UTC.

  MySQL converts TIMESTAMP values from the current time zone to UTC for
  storage, and back from UTC to the current time zone for retrieval.
  (This does not occur for other types such as DATETIME.) By default,
  the current time zone for each connection is the server's time. The
  time zone can be set on a per-connection basis. As long as the time
  zone setting remains constant, you get back the same value you store.
  If you store a TIMESTAMP value, and then change the time zone and
  retrieve the value, the retrieved value is different from the value
  you stored. This occurs because the same time zone was not used for
  conversion in both directions. The current time zone is available as
  the value of the time_zone system variable. For more information, see
  Section 10.6, “MySQL Server Time Zone Support”.

Since both MySQL and Windows have TimeZone Support, it was probably in MySQL's best interest  to have mysqld more Windows-aware. This would probably be a secret incentive to get your MySQL database out of Windows and into Linux. (My Conspiracy Theory)


SQL Server Agent Jobs - Long running job delaying other jobs? I have several agent jobs that run on different schedules.

Job 1 runs every 5 minutes, only executing a stored procedure that usually takes less than 30 seconds to run.

This job Selects from table "users" then uses a cursor to loop through the results and insert records into local tables. After the cursor finishes 4 batch calls are made to a remote linked server to insert the records from the local tables to remote tables. Then the stored procedure ends.

The stored procedure sql is wrapped in a Begin, Begin Try, End Try, End statement.

Job 2, runs every hour, it too only executes a stored procedure, however, this job can take 20 minutes to run.

Job 2 performs two different Selects both referencing the "users" table (which Job 1 also selects from) and inserts the results into local tables, but without using a cursor. Then 4 batch calls to a remote linked server to insert the records from the local tables to remote tables. This stored procedure is also wrapped in a Begin, Begin Try, End Try, End.

When I check the Job Activity Monitor log for Job 1, it shows the duration is consistently under 30 seconds, except for the time that it runs on the hour, with Job 2. At this time it shows the duration 20+ minutes - I guess as a result of Job 2, the long running job, running.

Does this mean that Job 1 begins and as it begins Job 2 starts causing Job 1 to not complete until after Job 2 finishes (the reason why SQL agent log shows the duration of Job 1 as 20 minutes, even though this job never takes 20 minutes to actually execute)?

Is there a way that I can keep this long running job isolated so that it doesn't cause other fast running jobs to be delayed?

An easy workaround solution would be to nudge either of the jobs to start earlier/later and to not run together with the other job.

E.g.

    Job 1 Schedule: 00:14, 00:29, 00:44, 00:59, 01:14, ....
    Job 2 Schedule:                        01:00, .......

It's not an issue with the job scheduler, but an issue with the underlying tables that are being accessed. When Job 2 starts, it manages to acquire a lock on the users table (possibly: IX, IU) before the Job 1 is able to acquire its required locks on the same users table. Then Job 1 has to wait.

You are encountering blocking as previously observed by @MaxVernon. You could run the following script at the full hour to see if you can catch the statements involved:

SELECT sp.spid,
       sp.kpid,
       sp.blocked,
       sp.hostprocess,
       sp.waittype,
       sp.lastwaittype,
       DB_NAME(sp.dbid),
       sp.cpu,
       sp.physical_io,
       sp.memusage,
       CASE
            WHEN sp.blocked = 0 THEN sp.spid
            WHEN sp.blocked != 0 THEN blocked
       END               AS chain,
       dest.text
FROM   sys.sysprocesses  AS sp
       OUTER APPLY sys.dm_exec_sql_text(sp.sql_handle) AS dest
WHERE  spid > 50
       AND spid <> @@spid
       AND (
               spid IN (SELECT blocked
                        FROM   sys.sysprocesses
                        WHERE  blocked != 0
                               AND spid > 50
                               AND spid != @@spid)
               OR blocked != 0
           )

The output will show statements that are blocking or blocked. You will probably find that the statements are from the Job 1 and Job 2.

Possible solutions:

Adding a NOLOCK hint can help, but should be avoided if possible.
Then you could also consider optimising the code so that the steps in Job 2 run faster.
Changing the schedules of the Job 1 and/or Job 2.


do MYSQL views occupy physical space? do MYSQL views occupy physical space? Or in other words, is a MYSQL database limited to a certain number of views?

To answer your question literally, yes, in MySQL, views do exist as occupied space on the disk. But of course they do: if the didn't, where would they exist? If you rebooted your server, how would the views persist?

I imagine what you really meant was "do MySQL views occupy physical space in proportion to the number of rows they contain?", in which case the answer is no, they do not.

In MySQL, well:

  Views are stored queries that when invoked produce a result set. A view acts as a virtual table.

It's query that is stored, not the results.

The manual's section on view processing algorithms sheds more light on this: the normal method of handling a view is that the definition of the view is inlined into any query referring to the view (this is called the MERGE algorithm). In some cases, an alternative method is used, where the view's query is executed alone, and the results are stored in a temporary table, against which the referring query is then executed (this is called the TEMPTABLE algorithm).

The MERGE algorithm does not ever involve storing the results of the view on disk. The TEMPTABLE algorithm does, but only temporarily.

Views work the same way in other databases. This is how they work in PostgreSQL, for example.

However, some databases support another approach as well: materialised views. With these, the view's query is executed ahead of time, and the results are stored on disk. Materialised views views do indeed occupy physical space in proportion to the number of rows they contain. This consumes more space, requires work to be done ahead of time, and introduces a lot of complexity (shared by the user and the database to varying extents) around making sure the view is up-to-date, but offers potentially much faster queries, because the work involved in the view's query has already been done. Oracle supports materialised views, and has for a long time. PostgreSQL currently does not, but will do as of the next version (which will be 9.3). In databases without built-in support for materialised views, there are ways to emulate them using triggers.


SQL Server 2012 Installation Error I been trying to install SQL Server 2012 on Windows 7 SP1 32 bit.

Logged in as Administrator and launched setup.

Everything installs fine but SQL Server DB Services fails with following error while

One or more command-line switches were invalid

and current file which it was reading was : sql_config_repair_loc_wmi_provider_cpu32_1033

and in log its says:

  The SQL server feature 'SQL_Engine_Core_Inst' is not in a supported state for repair, as it was never successfully configured.  Only from successful installations can be repaired.  To continue, remove the specified SQL Server feature.

Because SQL Server 2012 isn't out yet, and we're dealing with beta code, this is probably best handled as a support request at Connect.Microsoft.com.  Include all of your logs, and they may be able to diagnose it.

If you do want to pursue it here, include the full build number that you're using, and expect to hear people answer with, "You should try again with the latest release candidate."


MySQL 5.6 Slave Stops Processing Binlog on Master Restart I have a pair of Debian 7 servers with MySQL 5.6.23. The slave server is a clone of the master (but the uuid has been regenerated on the slave).

For some reason, whenever MySQL is restarted on the Master via "service mysql restart," the Slave will continue reading the Master_Log_File and Read_Master_Log_Pos, but won't process the Relay_Master_Log_File.

Slave I/O and Slave SQL are both running, but the Seconds_Behind_Master continue to increment.

The Slave_SQL_Running_State says "Waiting for Slave Worker to release partition."

Obviously, I've tried to stop and start the slave, restart the MySQL service on the slave and master, and restart the slave and master servers.

To fix the issue, I have to blank and recreate the slave. I do this via a script I've made to dump the master file and position, use mysqldump to pull the database onto the slave, and then start slaving from the recorded master file and position. The mysqldump flags are --skip-lock-tables --flush-logs --hex-blob --master-data=2 --single-transaction --comments --routines

Am I missing something? Any help is greatly appreciated.

Guess what ? You have a nasty little bug  (Replication stall with multi-threaded replication)

This bug was first reported in MySQL 5.6.17 on 20 Jun 2014 15:45

SUGGESTION #1

You need to look over all the release notes from MySQL 5.6.18 up to MySQL 5.6.22 to see if it was resolved. If any of those release notes claim the problem was fixed, then someone missed a patch. I do advise that you do not regress to anything before MySQL 5.6.21 (Any known issues upgrading from MySQL 5.1.73 to 5.6.21? under ISSUE #3 : Security Issues)

SUGGESTION #2

Don't use multithreaded replication until this bug is fixed.

SUGGESTION #3

In the aforementioned bug report, it says this somewhere in the middle

  When MySQL comes back up again, the log is rotated, and IO thread starts writing from the event group which was partially written.

  .
  .
  .

  You can see that the "end_log_pos" values are the same for the partial event I showed earlier and this one. It is the same UPDATE transaction that was partially written.

  Now comes the interesting part. When the coordinator reads the relay log, it sends the partial event to the worker, since the event is partial hence the worker never commits the transaction and the transaction is kept open. The coordinator reads the next event (which is the full version of the partial event) but the coordinator cannot assign the next event to another worker because of one of the workers having an open transaction.

  And apparently, MTS waits for workers to commit transactions when it sees log rotated.

I would suggest not using --flush-logs with mysqldump, just in case there are some issues with group commits that are all-of-a-sudden split by the mysqldump and this bug isn't viewing incoming binlog events correctly.


'Refreshing' SSRS subscriptions (parameter values not valid due to lack of data in the tables) In a place where I work we have lots of subscriptions set for SSRS reports. Some reports have as many as 100 subscriptions set up.

Our data warehouse is refreshed every night, with all the data being dropped and loaded in again.

Because of that, on occasion when things go wrong and the data isn't loaded up in the morning when the subscriptions are supposed to run, they are unable to fill in the parameters and get bugged like this:

Now to fix them, we have to manually open every subscription and hit save again. Sometimes this is not enough and we have to manually set up every parameter again. This is very frustrating and time consuming. So this brings me to my question:

How to prevent this? Is there a way to automatically 'refresh' all subscriptions? Other solution I have thought of, but lack the knowledge of ReportServer db to execute is:

Perhaps run a script before the subscriptions are set to execute. The script would check if data exists in warehouse database tables. If there is no data, delay subscriptions for an hour and then try again. Is this doable? If it is, I would be extremely grateful for a query that gives me access to execution times of subscriptions. I tried and couldn't build one myself.

Any other solutions are extremely welcome, thanks in advance!

What you do is to create your subscription to run only once. Then you go to the agent job it created and copy the step's code. This you can then run in a stored procedure after checking if there are data:

if (select * from table where date = '2018/05/18')
exec [ReportServer].dbo.AddEvent
    @EventType='TimedSubscription',
    @EventData='xxx data copied from job xxx'

The AddEvent will then execute the report to execute.


How do I switch off SCHEMABINDING for a view without recreating it? How do I switch off SCHEMABINDING for a view without recreating it?

Yes. It's good that you use SCHEMABINDING (we do always) and sometimes you have to remove it to change a dependent object. Just ALTER the view

ALTER VIEW myView
--Remove this WITH SCHEMABINDING
AS
SELECT ...
GO


mysql RANGE partitioning - how do I find partition boundaries? I am implementing partitioning support for mysql in eludia engine
I need to compare partitioning description in model with actual database contents and alter table, if necessary.
To extract all partition info about table I use information_schema.partitions
ALTER TABLE foo PARTITION BY RANGE (is_archive)
(
 PARTITION p0 VALUES LESS THAN 1,
 PARTITION p1 VALUES LESS THAN MAXVALUE
);
SELECT * FROM information_schema.partitions WHERE table_schema = database() AND table_name = 'foo';

I've inspected information_schema.partitions, it has partition_method, partition_expression but not ranges (LESS THAN 1, LESS THAN MAXVALUE parts)
I know I can extract this from SHOW CREATE TABLE. It is hard way:(
How should I programmatically retrieve partition boundaries in mysql?
Enviroment: mysql 5.1+

I am not 100% sure, but 5.1 documentation says:

  PARTITION_DESCRIPTION: This column is used for RANGE and LIST
  partitions. For a RANGE partition, it contains the value set in the
  partition's VALUES LESS THAN clause, which can be either an integer or
  MAXVALUE. For a LIST partition, this column contains the values
  defined in the partition's VALUES IN clause, which is a
  comma-separated list of integer values.

  For partitions whose PARTITION_METHOD is other than RANGE or LIST,
  this column is always NULL.

In my 5.6 installation I can get:

mysql> SELECT PARTITION_NAME, PARTITION_DESCRIPTION FROM information_schema.partitions WHERE table_schema = database() AND table_name = 'foo';
+----------------+-----------------------+
| PARTITION_NAME | PARTITION_DESCRIPTION |
+----------------+-----------------------+
| p0             | 1                     |
| p1             | MAXVALUE              |
+----------------+-----------------------+
2 rows in set (0.00 sec)

Please note than your original ALTER TABLE syntax had a problem, and it should be written as:

mysql> ALTER TABLE foo PARTITION BY RANGE (is_archive) (
         PARTITION p0 VALUES LESS THAN (1),
         PARTITION p1 VALUES LESS THAN (MAXVALUE)
       );
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0


Matching two rows in a single table if two specific keys have different values I have a table called settings in that there are two columns and data like following.

settings           value
-------            ------
depots             12
projectid          156
client             icici bank
clientvisits       2
DBname             icicibank
progress           true
displayvisits      2
visitsskipped      0

Here , the values of rows clientvisits and displayvisits should be same always.
I want a query which display the only these two fields along with values if their values are different. That means if clientvisits=2 and displayvisits=4 then it should throw an error (I mean it should display the output along with values).

I tried  by using self join  like this  but it is not working.

select * from settings TS,settings TS1
where TS.settings=TS1.settings and CONVERT(integer,ts.value)=1

select * from settings TS,settings TS1
where TS.settings=TS1.settings and ts.setting like 'visitstodisplay'=1

Also, I tried to do some thing else but I didn't get.
Please let me know how to do it.

You can do a join on Value <> Value for the settings you are looking for.

select S1.Settings as Settings1,
       S1.Value as Value1,
       S2.Settings as Settings2,
       S2.Value as Value2
from Settings as S1
  inner join Settings as S2
    on S1.Value <> S2.Value and
       S1.Settings = 'clientvisits' and
       S2.Settings = 'displayvisits'

Result:

Settings1            Value1               Settings2            Value2
-------------------- -------------------- -------------------- --------------------
clientvisits         2                    displayvisits        4


Table cache hit rate is 0% - is that a huge concern? Hi all of a sudden my server has slowed to a crawl, queries that were 0.10 of a second consistently now taking 20 seconds.  somewhat randomly, sometimes it snaps right back.  so I have a really sudden and harsh performance problem.

I ran mysqltuner.pl and it reported one thing that might be a problem :

[!!] Table cache hit rate: 0% (400 open / 1M opened)

I am just starting to delve into this performance problem, but does anyone know if this a big immediate red flag?

I can do some DD but am pressed right now.  any help much appreciated.

Don

The query cache was only hit 400 out of 1,000,000 times.

That is a query cache hitrate of 0.04 %

This indicates that 99.96% of your queries are unique and are not reusable. That's not unusual if your are storing session data, doing blogging, audit trails, or just plain logging of events.

While you could setup slow query logging, there is a better approach to checking on SQL performance. I recommend using pt-query-digest with --processlist option or --tcpdump option

This will allow you to catch bad queries in the act of being bad.


SQL Server hashbytes seed I'm trying to compare hashes generated by SQL Server HASHBYTES() with hashes generated with python's hash() (or hashlib) function. For both functions, the same algorithm is used. The problem is I don't know SQL's seed so I cant generate the same output using python. Is there a way to set SQL's seed? I wasn't able to find any information related to it.

I am able to get identical results between python and T-SQL code with the MD5 algorithm. For example, the NO COLLUSION string hashes to 0x5CA1A58C070F24EF1D4D2900E5727F37 on both platforms.
Example T-SQL code:
SELECT HASHBYTES('MD5', 'NO COLLUSION');

Example Python code:
import hashlib
result = hashlib.md5(b'NO COLLUSION')
print(result.hexdigest())

I'm not an encryption expert, but it's not clear to me what you mean by "SQL's seed". The MD5 algorithm doesn't appear to call for a random number to be added by the implementer. Such an algorithm would be quite inconvenient because it would result in the same problem that you're running into right now: different implementations of the same hashing algorithm would give different results for the same input.


How to calculate required ressources from a baseline and what should be measured at all? I have to deal with the following situatiuon:

Currently we have

1 cluster with 5 nodes running 10 instances (SQL Server 2008 Enterprise edition), and
1 cluster with 2 nodes running 5 instances (SQL Server 2014 Standard edition)

in use. All servers are virtual servers running in VMWare.

We want to setup a new cluster (SQL Server 2014 Enterprise). In a first step, 1 instance from the 2008 Enterprise cluster and 1 instance from the 2104 Standard cluster are to be migrated.

Therefore, my boss asked 2 questions:

How many cores do we need (aim: minimize license costs)?
How much RAM do we need?

My answer was: "It depends ..." Now I have to deliver hard facts by monitoring the next few weeks. Great! (beware of irony)

My approach for question number 1:

Using perfmon.exe I plan to monitor

Processor\% Processor Time (_total and single cores),
Processor\% User Time (_total and single cores),
Processor\% Interrupt Time (_total and single cores) - is this really necessary? -, and
System\Processor Queue Length.

The question is, where to get these data from? From the node? From the SQL Server?

In the first case it should be easy: the first instance in question - vsql2008ent-1\instanceX for the sake of simplicity - is currently running on a node, let's name it node sql2008NodeA. No other instances, nor server should run under normal conditions on this node. So it should not matter where I get the data from, should it? In case of a disaster other instances will be running on this node, too. But we want to have a baseline for normal operation.

The second instance - vsql2014stan-1\instanceY - shares it's node - sql2014NodeA - with 2 other instances. In this case I can never be sure, how much cores the instance will truely need for smooth operation, right? So I can monitor the instance. But what does the result mean? It shows the CPU ressources actually used by this instance, only. But would more cores have been used if they were available? So what would be the answer to the question mentioned above?

RAM is the other question. Due to several disasters in the past when all instances landed on the same node I have set an upper limit for the maximum server memory for each instance. This limit depends on the available memory of the node (currently 100GB or 120GB respectively). So how to monitor this? If all memory is used up, everything seems clear: insufficient memory. If all goes slow: insufficient memory. But how much memory do I really need?

I try to summarize my questions:

Where should I get the measures from (node vs. server)?
Do I need to monitor the interrupt time, if I want to know the number of cores required?
What should I monitor under the given circumstances to answer the question, how much RAM I need (I know: "The more the better.")?

Thank you very much for your help!

Best regards!

The questions of whether/how to directly measure CPU core usage etc. are beyond my understanding, but here's what I'd consider trying:

Run a standard profiler trace with database name added, during your normally busiest period. Total up the CPU column for the SQL:BatchCompleted and RPC:Completed events by database, and you'll get a rough idea of how much CPU resources (which may be spread across multiple cores) each database is consuming. (Perhaps also total up the CPU column for the other events to see if anything major was missed. And save the trace "as trace table" for analysis.)
Exactly how to translate that to how many cores you'll need, I can't say. But if you also measure the total system CPU usage during the profiler run you might be able to estimate against the specific database's ratio of the total.

Note: If your server takes less than a few hundred batch requests a second (see SSMS activity monitor), then a standard profiler trace even across the network will almost certainly not affect performance. And if you instead script a server-side trace then more requests a second can be handled without slowing anything, but I make no promises for your environment.

For RAM, I wonder if http://www.sqlshack.com/sql-server-memory-performance-metrics-part-4-buffer-cache-hit-ratio-page-life-expectancy/ might help you determine if your instances need less/more. I don't think there's any way to do this by database though.


Do signed certificates from public certificate authorities add any value for always encrypted? If I create Always Encrypted columns in Microsoft SQL Server from the SSMS gui, it makes a self signed certificate. Is there any value in making a CSR and paying a certificate authority (or using letsencrypt.org) to make a certificate? Does that chain of trust add and value in this case?

Certs from trusted CAs (like VeriSign) are used when you need a certificate that must be able to prove its issuer, purpose, validity, etc... Certs for data encryption like Always Encrypted typically do not require such proof since your certs typically don't float beyond your org. I don't know of any use case where you would benefit from using a 3rd party CA cert in an AE deployment. Even when you have strict management/rotation policies, I can't think of any common policy that you can't implement using a corporate issued cert or even a self-signed cert (though the latter will have a bit more manual work).


Postgres auto analyze performance On postgresql 9.4 I have a table that never has any updates or deletes, only inserts and selects.  It therefore never gets auto vacuumed, but it does still get auto analyzed and when it does it can take over 100 seconds.  For that 100 seconds and for a significant amount of time afterwards DB performance is terrible, with my app reporting db response times in the 40-150 second range instead of the normal 2ms range.

I've always heard that you shouldn't disable auto vacuum but when I set autovaccum_enabled=false for this table the db performs much more reliably. Is there a reason to have it enabled on this table?  Will the query planner eventually suffer from not having auto analyze running on a table that never gets updates or deletes? Is there a better way to solve this?

EDIT: Potentially modifying autovacuum_analyze_threshold and autovacuum_analyze_scale_factor for this table to make analyze happen more frequently would be better?

I think your best option is to use your favorite OS scheduling tool to run ANALYZE <table> explicitly and pre-emptively at the time of your choosing, say at 3 a.m.

PostgreSQL's autovacuum and autoanalyze are activity-counter driven.  This has the unfortunate effect that the time when the activity counters cross the thresholds is mostly likely to be exactly when the database is most active, which is when you least want those maintenance tasks to run.  By running the task preemptively during a off-peak time you will reset the activity counters, so that the auto-versions rarely will find work to do.  But in case of something going wrong, they are still there to save your bacon.

  Will the query planner eventually suffer from not having auto analyze running on a table that never gets updates or deletes?

This can't be answered with the information you provide.  If that table gets nothing but single-row lookups via a unique index, for example, out-of-date statistics are unlikely to be a problem.  If it gets complicated analytics queries, it is likely to be much more of a problem.


SQL Server service shutdown due to 'bad?' SSL cert This morning it transpired that SQL Server 2012 service running on Win2012 had shut itself down.  When I logged in as the server admin and tried to access SSMS, after entering my credentials I got the 'transport' error.  On checking Windows Services, both SQL Server and Agent were stopped.  I tried to start them, but got a timeout error.
The SQL event log contains this entry:

The server could not load the certificate it needs to initiate an SSL
connection. It returned the following error: 0x8009030d. Check
certificates to make sure they are valid.

The Windows event logs contain these entries:

Log Name: Application
Source: MSSQLSERVER
Date: 14/03/2013 09:01:40
Description: Unable to load user-specified
certificate [Cert Hash(sha1)
"A1CF6EE1C352B7A185950EB400013269759C24BD"]. The server will not
accept a connection. You should verify that the certificate is
correctly installed. See "Configuring Certificate for Use by SSL" in
Books Online.
Description: TDSSNIClient initialization failed
with error 0x80092004, status code 0x80. Reason: Unable to initialize
SSL support. Cannot find object or property.
Description: TDSSNIClient initialization failed
with error 0x80092004, status code 0x1. Reason: Initialization failed
with an infrastructure error. Check for previous errors. Cannot find
object or property.
Description: Could not start the network library
because of an internal error in the network library. To determine the
cause, review the errors immediately preceding this one in the error
log.
Description: SQL Server could not spawn
FRunCommunicationsManager thread. Check the SQL Server error log and
the Windows event logs for information about possible related
problems.

The following procedure fixed the problem:

Start > SQL Server Configuration Manager
SQL Server Network Configuration > Protocols for MSSQLSERVER (Properties)
Uncheck General > Force Encryption
Start > Services > Start MSSQLSERVER service

Update:
The above only fixed the problem until the next reboot, whereby it happened again.  Apparently the SSL certificate needs permissions to be read by SQL2012, but I daren't keep rebooting until this gets fixed, so am accessing via RDC now instead of SSMS over SSL.

There are applications out there that change the ACLs on the machine keyset container folder. When this occurs, legitimate keyset users loose access to these keysets. See KB 278381: Default permissions for the MachineKeys folders for the correct permission set.

If you want absolute confirmation, see BUG: You cannot enable encryption by using a certificate when SQL Server 2005 is running under the Network Service account for a 'workaround' (it involves compiling a C++ app that will reset the ACLs properly...). But you can simply set the ACLs according to mentioned KB278381 and see if the problem dissapear.

More related KBs:

Peer-to-Peer Framework APIs return a "PEER_E_NO_KEY_ACCESS" error message
How to troubleshoot the Data Protection API (DPAPI)
Error message when you use Service Broker or database mirroring to connect to an instance of SQL Server 2005: "Connection handshake failed"

I am yet to find which application changes the ACLs. My money is on some antivirus apps. Don't ask me how I know about all this random pieces of disparate infos...


What is the name of this schema pattern Its been a few years... but I've run into a problem-set that reminds me of a schema  design pattern and I'm trying to recall the details.

Essentially, the pattern consists of one database which is 3 NF (or something else) that services all Inserts, Updates, and Deletes. On some periodic or event-driven basis it would publish all (or some) of its state to another database which is optimized for high-performance read-only access. Essentially, the publish would generally target a completely different or de-normalized schema.

The question is... Is this a well known pattern? If yes, what is it called, and what are the pitfalls aside from duplicative storage of the same state.

What you are describing is a data warehouse.  The live, normalized, read-write system is OLTP (online transaction processing) and the denormalized read-only snapshot is a data warehouse.  The structure of the data warehouse could be a Star Schema, especially if it's highly denormalized.  Data warehouses often have summarization in addition to denormalization.  There can be many copies of the same data summarized over various dimensions and/or timeframes.

The disadvantages of this technique are that the snapshot is generally not 100% up to date and you have to be very careful about how your snapshot is taken or you could actually introduce discrepancies other than timeliness into your data warehouse.  Another possible issue is that you may have difficulty doing some kinds of reporting out of a data warehouse because of the choices you made when rolling up details into you summary tables.  Also, if your data warehouse has multiple summaries over different timeframes, for example, you have to be careful to keep these consistent with one-another.

The timeliness issue in particular is one you have to be careful about.  I've seen users make a change to their online system and then get angry that it didn't show up right away in a report that is run against the data warehouse.  Users tend not to know or care about the vagaries of reporting systems.


How to join 3 tables together given 2 many-to-many relationships tables in between? I have 3 main tables: Person, Publication and Conference

and 2 many-to-many link tables in between: person_publication and person_conference

I want to join all 3 main tables together (showing me all the publication and conference a person was involved in)

But so far, I am only able to query either person-to-publication or person-to-conference like this:

select person.*, publication.*
from person
left join person_publication on person_publication.person_id = person.id
left join publication on person_publication.publication_id = publication.id;

select person.*, conference.*
from person
left join person_conference on person_conference.person_id = person.id
left join conference on person_conference.conference_id = conference.id;

How can I join them all together?

Join conference to person_conference, selecting the person key from person_conference. Do the same for publication and person_publication. Make the two queries sub queries in a from clause and join the two on the person key.

Edit:

You would want to do something like this:

SELECT
    p.person_id,
    p.name,
    a.conference,
    b.publication
FROM
    person AS p
    LEFT JOIN (SELECT
                   pc.person_id,
                   c.conference
               FROM
                   person_conference AS pc
                   INNER JOIN conference AS c
                     ON pc.conference_id = c.conference_id) AS a
      ON p.person_id = a.person_id
    LEFT JOIN (SELECT
                   pp.person_id,
                   ppp.publication
               FROM
                   person_publication AS pp
                   INNER JOIN publication AS ppp
                     ON pp.publication_id = ppp.publication_id) AS b
      ON p.person_id = b.person_id

The inner queries are called sub queries. These will act as a "table" (they are not the same) that you can then query against. If you intend to use sub-queries as a "table" though, you do need to alias them using the AS key-word.

EDIT:

I have learned a lot since 2014 and the query above shows that. I am leaving it as a reference for the answer that was given but the following would be a better query:

SELECT per.person_id,
    per.name,
    conf.conference,
    pub.publication
FROM person AS per
    LEFT JOIN person_conference AS pconf
        ON per.person_id = pconf.person_id
    LEFT JOIN conference AS conf
        ON pconf.conference_id = conf.conference_id
    LEFT JOIN person_publication AS ppub
        ON per.person_id = ppub.person_id
    LEFT JOIN publication AS pub
        ON ppub.publication_id = pub.publication_id


Find values which occur in every row for every distinct value in other column of the same table I need a select statement which finds values from Column B which occurs in all distinct values for A. Meaning in my example it would return:  x since x occurs in every value of A. (1,2,3 and 4)

I don't know which values are in Column B so I can't specify this in my query. How can I achieve this in SQL Server 2014?

    +---+----+
    | A | B  |
    +---+----+
    | 1 | x  |
    | 2 | x  |
    | 3 | y  |
    | 4 | y  |
    | 3 | x  |
    | 4 | x  |
    +---+----+

Count distinct A grouping by B, and return those where count(distinct A) match.

select B, count(distinct A)
from table_AB
group by B
having count(distinct A) = (select count(distinct A) from table_AB);

 B   A
=== ===
 x   4


Casting int YYYYMMDD to date produces incorrect value in Squirrel-SQL I have extracted some data from a legacy database where dates are stored as Numeric(8) values, in YYYYMMDD format.  I've stored them in a staging table in that same format, and now I would like to convert them into actual date fields in SQL Server, so I went hunting for integer-to-date conversion options, and came up with a variety of things involving CAST or CONVERT.

However, when I am testing a query in Squirrel-SQL (v3.9.1, using Java 1.8.221) using those conversion options with the date datatype (which seems appropriate since I don't have time values), the dates I get back are off by two days:

select cast('20200101' as date) ,
cast(left(20200101, 8) as date) ,
convert(date, cast(20200101 as char(10)), 112) ,
convert(date, convert(char(8), 20200101)) ,
convert(datetime, convert(char(8), 20200101)) ,
dateadd(day, datediff(day,0,cast(20200101 as varchar(10))), 0) ,
convert(date,dateadd(day, datediff(day,0,cast(20200101 as varchar(10))), 0))
/* results:
2019-12-30
2019-12-30
2019-12-30
2019-12-30
2020-01-01 00:00:00.0
2020-01-01 00:00:00.0
2019-12-30
*/

Note especially those last two, where the DateAdd expression explicitly produces the correct datetime, and then using Convert on it backs it up by two days again.

When I run the same query through SSMS, it produces the expected values.

I haven't found anything anywhere that offers to explain this behavior, so I am hoping that one of you can shed some light?

One other clue that may be relevant (but which has also produced no usable google results) is that SysDateTimeOffset() is not affected, but SysUTCDateTime() appears to be off by two days as well:

select cast(sysdatetimeoffset() as varchar(40)) as Sysdtoff, sysutcdatetime() as sysUTC
/* output (actual date should be 1/22)
2020-01-22 11:45:39.9878059 -06:00
2020-01-20 17:45:39.9878059
*/

The actual time on the server is correct, and it is set to the expected time zone (-6 hours), and my examination of Squirrel-SQL's settings doesn't turn up any date-related options.

For integer to date conversion you may consider using the system function in msdb called dbo.agent_datetime(date int, time int).  This will automatically convert to a datetime (which you could then cast as a date).

Source: https://blog.sqlauthority.com/2015/03/13/sql-server-interesting-function-agent_datetime/


How to handle dimension hierarchies that re-join? The obvious example is a time dimension where days roll up by day>month>year but also roll up days>weeks>year. BIDS complains about it, but I see no other way to model the situation.

You can have more than one hierarchy on a dimension.  Your time dimension could have a year-quarter-month-day hierarchy and a year-week-day hierarchy.  The hierarchies can share the same 'day' attribute - set up attribute relationships for both of the hierarchies, and 'week' and 'month' can both be attributes of 'day'


MySQL growth 500k records/day Im not a DBA however am an infrastructure guy used to installing and configuring database servers such as MSSQL, MySQL, MongoDB etc...

I have a MySQL database which is being used for archival of building systems data. Once a night, the building management system will archive its data which results in around 500k rows of data being added across various tables in the database.

There are 12,000 tables in this database and overall it contains 7 billion records.

Are there any issues with growth? The database is currently 150GB and this is a purely write only load. There will hardly ever be any read activity on this DB.

I understand IOPS and performance scaling, this is a question about MySQL itself and specifically its ability dealing with a DB of this size.

Is there anything I should be aware of?

EDIT: I am fully aware that this is not ideal, hence this post but I am totally 100% unable to change the way the third party stores its info in the DB so don't have much of a choice but ask how I can best 'optimise' SQL to work with what its given.

Thanks

MySQL can handle it, but not without some care.

12,000 tables is a red flag.  Each table is one (or more) OS file.  This large a number, especially if in a single database, can cause sluggishness.

Are the records merely written to the tables?  Or are they used to "update" existing rows?

I would load the 500K rows into a table(s), cleanup the data, then copy it into the real tables.

Generally, I would do the work 1000 rows at a time.  But this might vary depending on whether you have Replication, which Engine you are using, etc.

7B in 150GB smells like MyISAM; is it?

If you are "never" reading it, why put it in a database?  Why not in flat files?

What does the incoming data look like?  CSV?  mysqldump?  Other?

Simple-minded code should be able to load a nightly dump in an hour or two.  More advanced coding might get it down to less than an hour.


Split table vs. same values in multiple rows I have a table products:

#products
ID | category | type | criteria1 | criteria2

, with category and type being foreign keys of other tables.

Should I split this table up into category1_type1_products, category1_type2_products and so on? It seems to me I should, because right now in my table, there are some thousands of entries with the same ID and category value. Loads of redundand information.

Also, a query could perform faster if mysql didn't have to search for all rows with the specific category and type first. (right?)

Is this recommended structuring behaviour at all? If not, what if my table has got 5 million tuples?

No. That would mean the table NAME contains DATA - you then have to modify the database structure (create new table) just to add one more category or type.

If your category/type column is tinyint or smallint (depending on reasonably expected number of possible values), then you actually have proper normalization in place. If there is no (functional) dependence between two keys to categories for two different rows then those two values are not a redundancy but a minimal way to store the actual data.

From performance standpoint the important part is proper indexing for your queries. If you often search for rows with specific category and/or type, you should have in place index on that column (or composite index on both columns, and possibly other) to optimize such queries. You should enable the slow query log and periodically check what queries are taking most time (you may use pt-query-digest to analyze the log) and optimizing those (adding proper indexes and/or rewriting the queries).

When you have proper indexes in place then in your proposed case of multiple tables actually selecting the right table to read will take about the same time as MySQL needs to "jump" to the right part of the index. (It is a bit oversimplification but the point is that selecting the right table to read is an overhead in itself).

From the "schema" standpoint I would be more concerned about those two columns criteria1 and criteria2 if these are their real names and usage then that seems like a possible problem - but that may depend on your use case and may be actually OK. But if there is any chance you will once add criteria3 and others then that would tell you to add separate table for those, one row per criteria and FK to the current table.


Oracle 11g increasing shared pool size and java pool size i need to install/configure oracle jvm on 11g. to be able to install same i need to increase the size of shared_pool_size and java_pool_size. i have automatic memory management enabled. my sga_max_size = 512M.
Can any one advise how to change the shared/java pool.

Thanks for the help.

You're have AMM enabled, so Oracle will manage it for you automatically. SGA_TARGET will be set to a non-zero value, which means:

  Specifies the total size of all SGA components. If SGA_TARGET is
  specified, then the buffer cache (DB_CACHE_SIZE), Java pool
  (JAVA_POOL_SIZE), large pool (LARGE_POOL_SIZE), and shared pool
  (SHARED_POOL_SIZE) memory pools are automatically sized.

You can set it to a non-zero value that is equal to or less than SGA_MAX_SIZE, so if you need to allocate more memory, just up SGA_MAX_SIZE and SGA_TARGET.

eg:

ALTER SYSTEM SET SGA_MAX_SIZE = 2048M;
ALTER SYSTEM SET SGA_TARGET = 2048M;

See this documentation section (search for "Enabling Automatic Shared Memory Management") - Read that whole documentation section to gain a better understanding.

As an aside, your SGA_MAX_SIZE does seem very small.


write "ENGINE=INNODB DEFAULT CHARSET=UTF8MB4" once for the whole script When exporting my database structure there is this ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 after every table creation. Do you have to write it for every table? Or is there a way to write it once for every table like (pseudo SQL)

CREATE DATABASE IF NOT EXISTS `mydb`;
USE `mydb`;
SET (ENGINE, `INNODB`);
SET (DEFAULT CHARSET, `UTF8MB4`);

You can do it relatively easily by following these steps:

1) Take a dump of your database - for the sake of this example, I've used the MySQL sakila sample database "designed to represent a DVD rental store". The command I used was:

  mysqldump -u root -p sakila > sakila.sql

You will then be asked for the password for the user that you are using - in this case I used root - this is not best practice!

Your dumpfile will contain many sections (2 examples shown) like the following (16 for the Sakila schema - one for each table in your database/schema):

  DROP TABLE IF EXISTS `actor`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `actor` (
  `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

and

DROP TABLE IF EXISTS `category`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `category` (
  `category_id` tinyint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

2) The key lines in these sections are at the ends of the table definitions:

) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;

and

) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

3) use your editor of choice to change utf8 to utf8mb4. Or, if you're on Linux, you can use the sed utility:

sed 's/utf8/utf8mb4/g' sakila.sql > sakila.sql.bis // the "g" is for "global"

4) Check your output file (belt and braces!) using your editor.

5) Drop your Sakila schema using your mysql prompt or from MySQL Workbench (or your tool of choice):

DROP SCHEMA sakila;  -- "DROP DATABASE" does the same thing

6) Recreate an empty Sakila schema.

CREATE SCHEMA sakila;  -- ditto "CREATE DATABASE"

7) Having verified sakila.sql.bis, rename it sakila.sql overwriting the old file.

8) Load your modified SQL file, again using mysqldump (in reverse so to speak - note the change of > to < - output to input).

mysqldump -u root -p sakila < sakila.sql

Your final step is to set the default character set to utf8mb4 in my.cnf/my.ini.

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

So now, all your tables will be UTF8MB4 and any future tables will be created with the same character set. You can, as mentioned in the last link, also use different collations per table and/or per field, but that's not part of the question - an internet search should be sufficient to deal with this requirement should you need it.


What indexed view capabilities does Azure SQL Database have? Does Azure SQL Database support indexed views? Does it support automatic indexed view matching without NOEXPAND? Does the answer depend on the pricing tier?

I was unable to find definitive answers to these questions. The feature differences guide does not mention indexed views.

The indexed view documentation does not contain the word "Azure".

I was just searching for an answer to this and I found an MSDN blog entry from 2016 that suggests that Azure SQL does support indexed view matching and may (in at least some circumstances) use indexed views without NOEXPAND. From the linked article:

  YES,The code proves that the  Azure SQL supports automatic use of indexed views without specifying the NOEXPAND hint.

The blog entry also provides a code example that the author uses to support their assertion. I won't reproduce it here but check out the article at: https://blogs.msdn.microsoft.com/azuresqldbsupport/2016/09/04/does-azure-sql-db-support-automatic-indexed-views/


Could not find a "psql" to execute I have a bash script that run on a Linux (AWS Centos) server. There's an odd, but annoying warning message that pops up in the stdout whenever the script executes psql: could not find a "psql" to execute.
What is odd is that that the Postgresql command line psql runs just fine! Yet it still generates this error message.
For further context, the bash script is executed from a shell_exec in a php page (fastcgi/nginx). If you run the bash script from the command line, you don't see the warning. But again, even with the warning, psql does actually run properly.
Does anyone know why this warning appears, and how to eliminate it?

For further context, the bash script is executed from a shell_exec in a php page (fastcgi/nginx).

Try hard setting the path to the psql script so you don't rely on the environmental PATH and PHP setting that when it calls the shell,
Rather than,
$output = shell_exec('psql ...');

Try (or wherever command -v psql shows it's located)
$output = shell_exec('/usr/bin/psql ...');


Diakrites land wrong in Oracle database Every time we are rolling out a new release same tables are trancated and filled with fresh data. This process is done on a Windows pc with scripts that are run in SqlPlus.

When we do this on our development environment the diakrites are inserted as upsidedown questionmarks (¿) in stead of (ä, ë, ï or ö).

If the data is insert by hand in PL/SQL Developer the data is inserted in the right way. Because of the great amount of data this is not exceptable to do every time. It is also not possible to do this in the acception and production database.

I have tried to find some solution for this problem on the internet, but cloud not find it.  Is there a solution for this problem?

sqlplus inherits the encoding from calling cmd.exe codepage. You must tell Oracle which codepage is used with NLS_LANG value. Value of NLS_LANG and used codepage have to match.

Try this:

c:\>chcp 1252
Active code page: 1252

c:\>set NLS_LANG=.WE8MSWIN1252

c:\>sqlplus ...

Note, if you run sql-files then ensure they are saved as "ANSI" (i.e. CP1252 for "Western" Windows). If you use a different encoding then set chcp and NLS_LANG accordingly, for example:

c:\>chcp 65001
Active code page: 65001

c:\>set NLS_LANG=.AL32UTF8

c:\>sqlplus ...

See also https://stackoverflow.com/questions/33783902/odbcconnection-returning-chinese-characters-as/33790600#33790600


SELECT with lots of left join is very slow in SQL Server 2014 I've got a query with, more or less, this structure:

SELECT
    MAINTABLE_ID
FROM
    MAINTABLE WITH (NOLOCK)
LEFT JOIN
    TABLE_1 WITH (NOLOCK) ON TABLE_1_FK_MAINTABLE_ID = MAINTABLE_ID
LEFT JOIN
    TABLE_2 WITH (NOLOCK) ON TABLE_2_FK_MAINTABLE_ID = MAINTABLE_ID
[...]
LEFT JOIN
    TABLE_N WITH (NOLOCK) ON TABLE_N_FK_MAINTABLE_ID = MAINTABLE_ID
WHERE
    TABLE_1_ID IS NULL
    AND TABLE_2_ID IS NULL
    [...]
    AND TABLE_N_ID IS NULL

N is about 50. The table names are aliases to four part named tables. This query is pretty fast from SQL Server 2008 to SQL Server 2012. But in SQL Server 2014 (same database) it never ends. The query has been tested with different hardware configurations (these work with the others editions of SQL Server).

What's wrong with it?

Thank you.

It may be to do with the new cardinality estimator.  Try running the query with this disabled.  Also try the FORCE ORDER hint, eg

...your query
OPTION ( QUERYTRACEON 9481 ) -- turn off new cardinality estimator

...your query
OPTION ( FORCE ORDER )

I would also ask what foreign keys, indexes and constraints do you have in place?


How to pass parameters to delete statement? While executing this PL/SQL block:

CREATE OR REPLACE PROCEDURE procAuth_Title_Delete (auth_ID NUMBER,
                                        title_Nos IN NUMBER)
IS
BEGIN
DELETE FROM AUTHOR_TITLE where author_id='||auth_ID||' and title_number='||title_Nos||';
dbms_output.Put_line ( 'all records have been deleted with the values auth_ID: '||auth_ID||' and title_Nos: '||title_Nos||'.' );
END;
/
EXECUTE procAuth_Title_Delete ( 1234,1 );

I got the error as

ORA-01722: invalid number

I know that there is something wrong in the 'where' clause of delete statement. How to let the delete statement substitute those parameters '(1234,1)' in place of 'auth_ID' and 'title_Nos'?

You can't concatenate variables like that in a SQL statement and there is no need to do it anyway:

CREATE OR REPLACE PROCEDURE procAuth_Title_Delete (auth_ID NUMBER,
                                        title_Nos IN NUMBER)
IS
BEGIN
  DELETE FROM AUTHOR_TITLE
    where author_id = auth_ID  --<< just use the parameter
      and title_number = title_Nos;

  dbms_output.Put_line ( 'all records have been deleted with the values auth_ID: '||auth_ID||' and title_Nos: '||title_Nos||'.' );
END;
/


Hierarchical Storage and Query I am editing and providing new info to this question because i've found what I need, just have a couple of questions about efficiency.

Now, I have this table:

category
--------------------------
category_id   name         parent
1           Phillips       (null)
2           Barnes          1
3           Moore           2
4           de Mohrenschildt   3
5           Oswald          9
6           Hunt            2
7           Burnham         6
8           Buckley         7
9           Paine           4
10          Sturgis         6
11          Hunt            3

I have this query:

SELECT
  t1.name AS lev1,
  t2.name AS lev2,
  t3.name AS lev3,
  t4.name AS lev4,
  t5.name AS lev5,
  t6.name AS lev6
FROM category t1
  LEFT JOIN category t2
    ON t2.parent = t1.category_id
  LEFT JOIN category t3
    ON t3.parent = t2.category_id
  LEFT JOIN category t4
    ON t4.parent = t3.category_id
  LEFT JOIN category t5
    ON t5.parent = t4.category_id
  LEFT JOIN category t6
    ON t6.parent = t5.category_id
WHERE t1.name = 'Phillips'
ORDER BY lev2

that returns:

lev1        lev2    lev3    lev4         lev5      lev6
Phillips    Barnes  Hunt    Sturgis      (null)    (null)
Phillips    Barnes  Moore   de Mohr'ldt  Paine     Oswald
Phillips    Barnes  Hunt    Burnham      Buckley   (null)

and, for a new "node":

SELECT
  t1.name AS lev1,
  t2.name AS lev2,
  t3.name AS lev3,
  t4.name AS lev4,
  t5.name AS lev5,
  t6.name AS lev6
FROM category t1
  LEFT JOIN category t2
    ON t2.parent = t1.category_id
  LEFT JOIN category t3
    ON t3.parent = t2.category_id
  LEFT JOIN category t4
    ON t4.parent = t3.category_id
  LEFT JOIN category t5
    ON t5.parent = t4.category_id
  LEFT JOIN category t6
    ON t6.parent = t5.category_id
WHERE t1.name = 'Moore'
ORDER BY lev2;

which returns

lev1    lev2        lev3    lev4    lev5    lev6
Moore   Hunt        (null)  (null)  (null)  (null)
Moore   de Mohr'dt  Paine   Oswald  (null)  (null)

Which, as you can see, is awfully close to this, in form, with some html:

Phillips supervised:

Barnes supervised:

Moore supervised

de Mohrenschildt knew:

Oswald ...

Sturgis knew

Hunt knew

Moore worked for...

And can hopefully mature into this one day (what's below the tabs in the image)...:

I have a couple of questions about this table and query.

1) as can be seen in the name "Hunt", the problem I may have is that when someone is assigned to more than one parent, I'm going to have to enter a new record for him. And as there are going to be, I'm guessing 5 or 600 unique persons, to repeat many of them seems to me to be unnecessary. Can someone see a better way to handle this issue, or is it even an issue? I just don't like redundancy...

2) along these lines, if you look at the image, you'll see that some of the names are in RED, denoting an AJAX dropdown paragraph of pertinent information regarding that immediate relationship. So "Hunt" may have a paragraph or two under this particular branch, and another on another branch somewhere else. I'm thinking maybe the need to repeat "Hunt" wherever necessary in the table would allow for calling another set of data to fill the AJAX dropdown. Am I asking for trouble with what I have here so far...?

3) since there will be so many branches starting with different names, is this query efficient enough if I have to run the query perhaps 40 or 50 times on one, (of many) pages, especially if it's calling some extra data as mentioned above?

I like this "schema," if that's the right word, because it seems to be really extensible. This database has the potential to grow indefinitely.

I really appreciate anyone's help with this. I'm so happy I've finally found something that's working for me (I never even knew what an Hierarchical DB is until last night, and that that's what I was looking for...)

Since the depth is variable and an item can have multiple parents, I recommend a general "relationship", not a parent-child hierarchy.

See this for the optimal way to write a many-to-many table.

Then you need to write multiple queries, not a single, fixed-depth, query.  There is no "good" way to traverse a tree/DAG/web (until 8.0, with CTEs).  And the speed of multiple queries, with suitable indexes and suitable caching, should be quite adequate.


Strategy on Standby restore option for hourly log shipping file I am trying to come up with a strategy of keeping up with daily database update process using "standby" restore mode.
I am getting 24 log shipping files (for the previous day's each hour transaction log files) from a third party's FTP site. I would update these 24 files on a nightly run.
I get these files originally in SQB file formats, and then I have a tool and script to convert these SQB files into BAK file format.
Now, I am trying to come up with a strategy of continuous back up plan.
Database does not have to be updated or modified, but just to be read.
Do I restore each transactional log file as "standby" all the time and just leave them as "standby" mode?
I am planning to create a separate database to retrieve only necessary data from some tables from this "read-only" database.
I have one more question. If I accidentally run a script to restore this database as "NoRecovery or Recovery" mode, is there way to change the mode back to "Standby" by running a script or do I have to restore full bak file again as "Standby" (do the whole process again)?

Do I restore each transactional log file as "standby" all the time and just leave them as "standby" mode?

Either will work, but it is faster to apply all the logs except the last one using NORECOVERY. The last one applied uses STANDBY, making the database available for read-only access. SQL Server has to do extra work to make the database available for reading in a transactionally-consistent state. It then has to undo that work (using the standby file) when you ask it to apply the next log backup. In short, specifying STANDBY unnecessarily can really slow things down because you end up doing and undoing part of the recovery process (see link by Paul Randal below).
In your case, that would mean applying 23 transaction log backups with NORECOVERY, then the 24th with STANDBY. Next day, you do the same thing.
Note that you will need to ensure that nothing is connected to the database in standby mode when you go to start the next restore sequence. That ought not be an issue in your case, since you control all access to the database. If you do need to forcibly disconnect other users:
ALTER DATABASE DbName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

Don't forget to make the database available to other users again later with:
ALTER DATABASE DbName
SET MULTI_USER;

If I accidentally run a script to restore this database as "NoRecovery or Recovery" mode, is there way to change the mode back to "Standby"

You can change from NORECOVERY to STANDBY and vice-versa e.g.:
-- From norecovery to standby
RESTORE DATABASE DbName
WITH STANDBY = N'C:\StandbyFiles\DbName.sby';

-- From standby to norecovery
RESTORE DATABASE DbName
WITH NORECOVERY;

You do not need to change from STANDBY to NORECOVERY to apply the next set of log backups.
Once the database is recovered (WITH RECOVERY), you cannot go back, and must start the whole restore sequence again. You would never do that in your scenario.
The standby file will be automatically deleted (or reused) by the next restore step, but SQL Server does not lock the file while it is not actively in use by a restore command. Treat the file with the same care you would any other database file - if it is lost, so is the database.
Recommend reading:

Reporting From a Log Shipping Secondary in STANDBY mode by Kendra Little.
Log shipping performance problems by Paul Randal


Does it matter which database you connect to when querying across multiple databases? I'm building a Sharepoint application with Nintex workflow that runs a single SQL query over multiple databases (on the same MS SQL server). Does it matter which database I specify in the connection string in terms of speed?
So if my query looks like this:
SELECT col1, col2 FROM db1.table UNION ALL
SELECT col1, col2 FROM db2.table UNION ALL
SELECT col1, col2 FROM db3.table
would it make any difference if my connection string looks like this:
Server=***;Database=db1; Integrated Security=SSPI; Connection Timeout=900
or this
Server=***;Database=db2; Integrated Security=SSPI; Connection Timeout=900
or this?
Server=***;Database=db3; Integrated Security=SSPI; Connection Timeout=900
The table from db1 has more records than db2, which has more records than db3.
EDIT: My query is actually more complex than what I wrote above, I just simplified it because I didn't know that would matter. The real queries have a WHERE clause and a LEFT JOIN on a fourth database (db4).
The compatibility level of db2 and db3 are SQL Server 2008 (100), for db1 and db4 it's SQL Server 2017 (140).

In your example there is no difference.
But there can be difference if your query is more complicated, has parameters and the databases have different compatibility level/parameterization options.
Compatibility level of the database where the query is executed will affect cardinality estimations, so if your predicates contain AND OR conditions the estimated number of rows will be calculated according to different optimizator's rules, there are cases when someone migrates to a new server (say 2014) but leaves user databases compatibility level 100, in this case in he executes the query in the master's context new 2014 optimizator will do calculations, but if he executes the same query in user database the legacy optimizator will do estimations and they will differ.
Also forced parameterization can affect the plan.
If your query contain joins and constants in where filter, these constants will be preserved when estimation is done if database has simple parameterization, so the estimation will use statistics for filtering field.
But if the same query is executed in the context of database with forced parameterization the query will be parameterized and the plan will be built by using unknown value for the filter.


MySQL on RDS, data transfer from one instance to another as a production job I have a MySQL RDS instance ("production") from which I need to copy incremental data to another MySQL RDS instance ("reporting").  “Incremental data” being the new rows in certain tables.  Currently I accomplish this via a PHP script which queries from “production” based on the last update time and writes on CSV files to disk and then I use mysqlimport.exe to import the files into “reporting”.
My current method takes ~80 seconds.  Is this the most robust and efficient way to do this?  I’ve found other tools for “one off” transfers, but nothing in terms of an “enterprise incremental database replication” tool.
Our current process is not a “continuous” replication because of batch operations on the reporting side.  This may be negotiable though.  Would easing that constraint open up tools that would make this easy?

Relevant related articles/questions:
Amazon’s article on importing data seems to mainly deal with the batch (initial load) case.
http://aws.amazon.com/articles/2933

This can be done with a stored proc when moving from one DB to another on the same instance (the problem with my case is they are different instances).  See: Keeping MySQL staging database on same server updated from production
I can't put both DBs on the same instance because our instances are under quite a bit of load.

MySQL RDS allows you to do two options
OPTION #1
You set up a Read Slave. Then, run the reports from it. There is a tool in the RDS CLI called rds-create-db-instance-read-replica to create a Read Slave from a Running RDS instance. The following are done under the hood:

Snapshot Made
Snapshot Brought Up
CHANGE MASTER TO executed with proper binary log and position

OPTION #2
Take a live snapshot of Production. Then, open a new instance using that snapshot.
The tools for this are

rds-create-db-snapshot
rds-restore-db-instance-from-db-snapshot

CONCLUSION
The only difference between these options

the Read Slave is continuous
Snaphot brought up as a point-in-time Instance

In both cases, once you done, simply drop the new Instance (saves money) at your discretion.
If you combine the two ideas, you could load your PHP script's output into the point-time instance. Any needed data to retrieve from production could be read from the Read Slave to prevent further load issues with production.
WARNING
Bringing up instances can be time-consuming. So, plan to have the instance brought up early enough for report and import time.
This is more ranting and brainstorming than a concrete answer.


In Oracle how do you move a Schema to a different Tablespace? I noticed that a Schema has been created in the USERS Tablespace but our local rule is that it should be in a Tablespace by itself. In Oracle how do you move a Schema to a different Tablespace?

A schema itself can not be stored nor can have changed tablespace en bloc in any way. In fact, it is just a meta-structucre. Instead - there is DEFAULT TABLESPACE attribute of underlying USER. If you change it, then new objects are created in this tablespace by default (unless you excplicitly specify another one).

In addition, each type of structure (Table/Index/Partition...) has it's own way of moving to another tablespace. (i.e. moving a table does not move indexes built upon it - which would be rather undesirable, because you want to store indexes in another filesystem for better performance). This means, that you will have to move contents of the schema object by object.


get query execution time in command prompt I'm doing this query select * from rentals. This table has more than 10,000 rows and it takes more than 4 minutes to retrieve the results.

But I get this time after I get the results: 10000 rows in set (0,05 sec).

Do you know how to get the real execution time?

I try with:

set profiling = 1;
select * from rentals;
show profiles;

But also appears that the query duration is: 0,5080950.

In all probability, your query takes half a second to run, but it takes a lot more time to display all the results.

The latter depends on the speed of your network and that of your computer and is not really relevant from a performance tuning perspective.

To answer your question, the query execution time (0.5 seconds) is the execution time.


How do you ensure accuracy of your query I am have a table with about 100,000 rows.

Lets suppose the primary key is some sequence of numbers like a barcode for example.

I am doing checks like

SELECT count(*) FROM TABLE WHERE LEFT(Barcode, 1)  is 0

I have about a 100 more rules to implement. They should ALL return 0 rows. But how do I ensure that they are returning 0 rows because the data is in fact correct OR that it is returning 0 rows because of a poorly written query.

I know that all my queries should return 0 since there are constraints that prevent bad data from getting in the first place but this is still needed by the company.

You can do this pretty easily using SUM and CASE:

SELECT
    COUNT(*) as 'Total Rowcount',
    SUM(CASE WHEN LEFT(BARCODE, 1) = 0 THEN 1 ELSE 0 END) as 'Leading Zero Barcode',
    SUM(CASE WHEN LEFT(Foo, 1) = 'X' THEN 1 ELSE 0 END) as 'Leading X in Foo'
....

Then you get a number for the WHOLE table/query, and a breakdown of each category, all on the same line.  It'll be easy to see if you have a bad query since you'll get a low/high rowcount or something else.

This has the added advantage or processing more efficiently than a bunch of separate queries, since you just evaluate the whole table once.


Database design help required Need help designing in my database tables.

I have a set of table which are being populated on every execution, like wise this executions are user specific if user executes an execution data at that point of time data is being populated in table set.

I have 3 approaches to define data for my executions, but i am a bit confused which one to use. It would be great if someone throw some light or some better approach.

--APPROACH 1
CREATE TABLE #USERS(
    fname nvarchar(100),
    mname nvarchar(100),
    lname nvarchar(100),
    ExecutionID int
)

In approach 1 data gets repeated for each execution, with its executionID.

--APPROACH 2
CREATE TABLE #USERS_2(
    fname nvarchar(100),
    mname nvarchar(100),
    lname nvarchar(100),
    FirstExecutionID int,
    LastExecutionID int
)

In approach 2 data gets populated with first and last execution id, if same data gets repeated in consecutive execution, I won’t create new record instead update LastexecutionID to latest one. This is similar to SCD Type – 2.

--APPROACH 3

CREATE TABLE #USERS_3(
    fname nvarchar(100),
    mname nvarchar(100),
    lname nvarchar(100),
    mappingID int
)

CREATE TABLE #USERS_Mapping_3(
    ExecutionID int,
    mappingID int
)

Approach 3 is similar to first approach but I will be storing execution ID with its mapping ID in different tables.

Any other alternative for saving this sought of execution specific data?

This doesn't call for any SCD design -- the state of an entity is not changing, slowly or otherwise. What you have is a series of events that you want to refer to the user associated with the event.

create table Executions(
    ID      int not null auto_increment,
    UserID  int not null,
    ExcDate date not null,
    <any other data concerning the Execution event>,
    constraint PK_Executions primary key( ID ),
    constraint FK_Execution_User foreign key( UserID )
        references Users( ID ),
);

Then remove any Execution related data (ExecutionID) from the Users table. Each execution event relates back to the associated user. So the Users table contains only user data and the Executions table contains only execution event data, including the user that performed the execution. And it maintains all such events not just the first and/or last.


How to join two result sets from same table with different conditions on same column? select * from unit_of_measure;
+--------+-----------+--------+------------+--------------+-----------------+-------------+---------------+------------------+
| uom_id | uom_name  | symbol | created_by | created_date | created_ip_addr | modified_by | modified_date | modified_ip_addr |
+--------+-----------+--------+------------+--------------+-----------------+-------------+---------------+------------------+
|      1 | number    | NULL   | NULL       | NULL         | NULL            | NULL        | NULL          | NULL             |
|      2 | kilograms | NULL   | NULL       | NULL         | NULL            | NULL        | NULL          | NULL             |
|      3 | liters    | NULL   | NULL       | NULL         | NULL            | NULL        | NULL          | NULL             |
+--------+-----------+--------+------------+--------------+-----------------+-------------+---------------+------------------+

From this table I am trying to combine two result sets like:

select uom_name as uomname1 from unit_of_measure where uom_id=1;

select uom_name as uomname2 from unit_of_measure where uom_id=2;

Can anyone help me to join these two result sets. I have tried with union but it didn't solve the issue.

If you want number and kilograms to appear as a single column (comma separated), do this:

SELECT GROUP_CONCAT(uom_name) UnitsOfMeasure
FROM unit_of_measure WHERE uom_id IN (1,2);

If you want number and kilograms to appear as a single column (space separated), do this:

SELECT GROUP_CONCAT(uom_name SEPARATOR ' ') UnitsOfMeasure
FROM unit_of_measure WHERE uom_id IN (1,2);

If you want number and kilograms to appear as separate columns, do this:

SELECT * FROM
(SELECT uom_name AS uomname1 FROM unit_of_measure WHERE uom_id=1) A,
(SELECT uom_name AS uomname2 FROM unit_of_measure WHERE uom_id=2) B;

Give it a Try !!!


Anything like a hash for Postgresql databases? I have an export process I want to check. I want to verify that source and destination databases are equal. There are about 25000 rows, and schema is the same.

Is there a way to somehow "hash" the database so that two databases containing the same data are confirmed equal?

I guess pg_dump output may differ even if data are same.

It sounds like you might be looking for pg_comparator.

I haven't used it myself, but I've got some database check/compare work to do soon, and it's one of the options I need to look into as a starting point.


Equivalent of PGAdmin III Server Status Activity Window in PGAdmin 4 v5 I'm in the process of upgrading to Postgres 13.2 from a much older version.  This entails an upgrade from PGAdmin III to PGAdmin 4.
PGAdmin III has a brilliantly designed feature called Server Status Activity Window which lists all open connections on a server.  If you sort the connections by the "Query Start" column (descending) you can see the connections that have the longest-running queries.  The rows are color coded: active connections are initially green, then after a query is running for 10-15 seconds it turns brown.  Idle queries are blue.  The autovacuum queries are usually sitting in brown color because they tend to run for a while.
So with PGAdmin III Server Status Activity Window, a dba can quickly spot queries that have been running for a long time.
PGAdmin 4 v5 has something similar called "Server Activity" but it falls short.  Only the connection start timestamp is available as a column that can be sorted by.  You have drill into the connection details to see the "Query Start" timestamp and you can't sort by it.
What work-arounds are available in PGAdmin 4 v5 to get this view that was available in PGAdmin III?
A related question appears here: PGAdmin 4 - server status / view log file

I came up with this query that returns the same columns as PGAdmin III Status Activity window but alas there is no color-coding of queries running longer than 5-10 seconds and no button that will terminate the query or the connection.
select pid as process_id,
application_name,
datname as Database,
usename as username,
client_addr as client_address,
backend_start as client_start,
query_start,
xact_start,
state,
state_change,
pg_blocking_pids(pid) AS blocked_by,
query
from pg_stat_activity
where state is not null
order by state,xact_start desc;
This query is based loosely on this post and this post


Does hard-coding schema names in procedure calls for Oracle jobs have any performance impact? A database application I'm working with uses Oracle scheduled jobs to perform certain tasks. The DBA insists that hard-coding the schema name in the procedures that organise these jobs improves performance, but I can't see how it makes any difference. (I'm not even sure why it's necessary to have the schema name in there at all - the procedures run by the jobs are always in the same schema as the the job definer.)

Can there possibly be any truth to this? In general, does specifying the schema name make any difference when calling a procedure in the same schema as the connected user?


Multiple plans (with/without missing index) in query store for same query I'm a newbie regarding the query store and have some problems understanding what I'm seeing.
We have a third-party application (running on SQL Server 2016 Enterprise SP1 CU7) that uses the query-hints KEEP PLAN and KEEPFIXED PLAN. When I do some monitoring in the querystore I see that some queries has multiple plan ids in the plan summary window.

Why can a query that uses KEEP PLAN and KEEPFIXED PLAN have multiple plans (sometimes completely different, sometimes the same)?

And the second question, if there are plans that look exactly the same (same physical operators, same set options, same queryhash), how can one plan have a missing index and the second plan not?

This are two anonymised plans: Plan 1 Plan 2

I'll Start with the second question

The reason for the 'missing index' advice for Plan 2 is the subtree cost calculated
The parameters passed results in a subtree cost (right click/hoover on the nested loop join) that is 10 times higher than that for Plan 1, and it is because the estimated number of rows is 149, instead of 16.
What is suggested as missing is a covering index, so that it doesn't have to make 149 keylookups to get the missing values from the index.
For Plan 1 it was considered ok to make 16 keylookups

What you are experiencing is what Erland Sommaskog is calling a “morally equivalent plan”

Kendra Litttle explains it in this article https://sqlworkbooks.com/2018/03/what-is-a-morally-equivalent-execution-plan-and-why-is-it-good/
Using the feature of Query store to force a plan, but i believe it is the same when using KEEPFIXED PLAN

As to why several plans for the same query exists.
Many reasons including restart, cache getting cleared due to memory pressure, changing the structure or schema of a table referenced by the query.

This will cause the plan to be recompiled

Normally also updating the statistics, but since you have KEEPFIXED PLAN that is not he case.

Having KEEPFIXED PLAN can cause performance degregation since as your data changes, the statistics change and the optimal plan may be different, but KEEPFIXED PLAN is forcing the same plan all the time.

i am guessing, but is the setting

OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN)

used on all queries ?
then the DBA will have to monitor if optimal plans are used (if data distribution have changed much since the plan was created)

An extra comment. Having both KEEP PLAN and KEEPFIXED PLAN in the hints is not needed. KEEPFIXED PLAN is overruling KEEP PLAN.
KEEP PLAN, disables the “6 rows” rule, as far as statistics invalidation is concerned. KEEPFIXED PLAN disables recompilations due to statistics changes completely.


Understanding "max_wal_size" and "min_wal_size" parameters default values from postgresql.conf file Default values are, according to documentation for min_wal_size and max_wal_size parameters:

For max_wal_size: The default is 1 GB
For min_wal_size: The default is 80 MB

Then I look this parameters from my database config:

select name, setting, unit
from pg_settings
where name in ('min_wal_size', 'max_wal_size')

Gives result:

name         |  setting | unit
----------------------------------
max_wal_size | 64       |
min_wal_size | 5        |

I have 2 questions:

1) Why these values doesn't match default values, which are shown in docs? I never changed config settings at all.

2) Why unit column is empty/NULL for these parameters? What means 64 and 5 values in this case? MB? GB? Or what?

Why this is not like  for example  work_mem parameter, when everything is clear:

name         | setting  | unit
----------------------------------
work_mem     | 4096     | kB

These are 16 MB WAL segments by default. The manual:

  The system physically divides this sequence into WAL segment files,
  which are normally 16MB apiece (although the segment size can be
  altered when building PostgreSQL)

So, it's just default values as advertised:

select name, setting, setting::int * 16 || 'MB' AS setting_in_mb
from pg_settings
where name in ('min_wal_size', 'max_wal_size');

unit in pg_settings is present when it's an actual base unit like second or MB. In this case the "unit" would be "WAL segments", which can be configured before compiling Postgres. The unit column is just NULL for those.


MySQL SUM related to parent query I'm trying to put together a query to aggregate some information into a view, and am having trouble working out how to get that data I need from a couple of different tables. I'll start by describing the 3 tables I'm working with.

I have artists, which is as follows:

Ref | Artist
  1 | Metallica

I have tracks:

Filename | Title             | artist_ref
     001 | Sad But True      |       1
     002 | Through The Never |       1

And lastly I have popularity:

Filename | week | month | quarter
     001 |   23 |    42 |     138
     002 |    4 |    23 |      42

What I'm trying to do is get an aggregated popularity table formatted as so:

artist_ref | week | month | quarter
         1 |   27 |    65 |     180

I understand I'll have to use some kind of subquery and SUM, but my knowledge of this area is fairly limited. Any help is hugely appreciated as I'm finding this a bit of a brain buster!

Select artist_ref, SUM(week) as week,
SUM(month) as month,SUM(quarter) as quarter
FROM tracks LEFT JOIN popularity
USING (Filename)
group by artist_ref;

SAMPLE FIDDLE


Slow SQL Server Query First let me start off by saying I am in no way a DB expert. I know enough to be dangerous, but I am primarily a c# developer. I have a query off a view that is slow, but I am having problems figuring out the root cause. I have attached the query plan, hoping I can get some insight here.

https://www.brentozar.com/pastetheplan/?id=H1GT43J8e

Here is the view info as well:

CREATE VIEW [dbo].[ReSyncAdwords]
WITH schemabinding
AS
  SELECT dbo.currentadwordsmaxbid.maxcpc - dervtable.Bid AS Expr1,
         dbo.currentadwordsmaxbid.webid,
         dbo.currentadwordsmaxbid.maxcpc,
         dbo.currentadwordsmaxbid.adgroupid,
         dbo.currentadwordsmaxbid.keywordid,
         dervtable.SKU,
         dervtable.Bid,
         dbo.currentadwordsmaxbid.campaginid
  FROM   dbo.currentadwordsmaxbid
         LEFT OUTER JOIN dbo.WebSkuLookup
                      ON dbo.currentadwordsmaxbid.webid = dbo.WebSkuLookup.webid
         LEFT OUTER JOIN (SELECT SKU,
                                 Bid,
                                 Channel,
                                 DTStamp,
                                 campaginid
                          FROM   dbo.BidHistory AS ph1
                          WHERE  ( DTStamp = (SELECT MAX(DTStamp) AS Expr1
                                              FROM   dbo.BidHistory AS ph2
                                              WHERE  ( campaginid = ph1.campaginid )
                                                     AND ( ph1.SKU = SKU )) )) AS dervtable
                      ON dbo.WebSkuLookup.sku = dervtable.SKU
                         AND dbo.currentadwordsmaxbid.campaginid = dervtable.campaginid
  WHERE  ( dbo.currentadwordsmaxbid.maxcpc - dervtable.Bid <> 0 )

GO

Thanks.

Why all the ( ) and long names?
Why mixed lowerecase camel case on table names?

Just basic cleanup

  SELECT dbo.currentadwordsmaxbid.maxcpc - dervtable.Bid AS Expr1,
         dbo.currentadwordsmaxbid.webid,
         dbo.currentadwordsmaxbid.maxcpc,
         dbo.currentadwordsmaxbid.adgroupid,
         dbo.currentadwordsmaxbid.keywordid,
         dbo.currentadwordsmaxbid.campaginid,
         dervtable.SKU,
         dervtable.Bid
  FROM   dbo.currentadwordsmaxbid
         LEFT OUTER JOIN dbo.WebSkuLookup
                      ON dbo.currentadwordsmaxbid.webid = dbo.WebSkuLookup.webid
         LEFT OUTER JOIN (SELECT SKU, campaginid, Bid
                          FROM   dbo.BidHistory AS ph1
                          WHERE  DTStamp = ( SELECT MAX(DTStamp) AS Expr1
                                             FROM   dbo.BidHistory AS ph2
                                             WHERE  ph1.campaginid = ph2.campaginid
                                             AND    ph1.SKU = ph2.SKU )
                        ) AS dervtable
                      ON dbo.WebSkuLookup.sku = dervtable.SKU
                     AND dbo.currentadwordsmaxbid.campaginid = dervtable.campaginid
                     AND dbo.currentadwordsmaxbid.maxcpc <> dervtable.Bid

row_number()

  SELECT dbo.currentadwordsmaxbid.maxcpc - dervtable.Bid AS Expr1,
         dbo.currentadwordsmaxbid.webid,
         dbo.currentadwordsmaxbid.maxcpc,
         dbo.currentadwordsmaxbid.adgroupid,
         dbo.currentadwordsmaxbid.keywordid,
         dbo.currentadwordsmaxbid.campaginid,
         dervtable.SKU,
         dervtable.Bid
  FROM   dbo.currentadwordsmaxbid
         LEFT OUTER JOIN dbo.WebSkuLookup
                      ON dbo.currentadwordsmaxbid.webid = dbo.WebSkuLookup.webid
         LEFT OUTER JOIN (SELECT SKU, campaginid, Bid
                               , row_number over (partition by SKU, campaginid order by DTStamp desc) as rn
                          FROM   dbo.BidHistory AS ph1
                        ) AS dervtable
                      ON dbo.WebSkuLookup.sku = dervtable.SKU
                     AND dervtable.rn = 1
                     AND dbo.currentadwordsmaxbid.campaginid = dervtable.campaginid
                     AND dbo.currentadwordsmaxbid.maxcpc <> dervtable.Bid


Can some redundancy in this nested SELECT COUNT query be eliminated? For the record, this is not a homework question.  I am a professional brushing up on my SQL skills.

Given a table called world that looks like this,

I want to find the continents where all countries have a population <= 25000000. Specifically, I want the name, continent, and population of the countries associated with these continents.

I wrote this query, and it appears to be functionally correct:

SELECT name, continent, population
FROM world x
WHERE (
  SELECT COUNT(name)
  FROM world y
  WHERE y.population <= 25000000
  AND x.continent = y.continent
) = (
  SELECT COUNT(name)
  FROM world z
  WHERE x.continent = z.continent
)
ORDER BY continent, population DESC

At first glance, that query looks unnecessarily redundant.  I'd like to simplify the query (or improve it in other ways), but I'm unsure how to do that.  Can someone show some better alternatives?

MySQL or Postgres are preferable feature sets, if it matters.

The problem is exactly as it appears in exercise 9 of this page in sqlzoo.  Queries can be tested and iterated upon there as well.

At least the inner countings are not necessary. You can easily use an EXISTS query, in negative:

SELECT name, continent, population
FROM world x
WHERE NOT EXISTS (
  SELECT 1
  FROM world y
  WHERE y.population > 25000000
  AND x.continent = y.continent
)
ORDER BY continent, population DESC

This is more readable also since it literally says "give me the continents, except if there is a country where population is larger than the limit" and the person reading it doesn't have to spend time wondering what the counts are. Also the DB engine doesn't have to do any counting.

Another way would be to ask for the continents which have smaller countries and exclude those:

SELECT name, continent, population
FROM world x
WHERE continent NOT IN (
  SELECT DISTINCT continent
  FROM world y
  WHERE y.population > 25000000
)
ORDER BY continent, population DESC

This may have better performance since it needs to only once find the continents that don't match and then run the main query as opposed to the previous one checking continents separately.


Cumulate count between date with 0 when there are records for a given day How could one write a Postgresql query that gives both daily and cumulative counts without gaps? E.g. if there is no data for a given day, it would show 0 as a daily count for that that day and the same cumulative sum as the previous day?
I think I'd need GENERATE_SERIES, but I don't know how to do it. I'm also not entirely sure if order by day asc rows between unbounded preceding and current row would work correctly always, but maybe not the biggest issue here.
I have tried to craft the query with PARTITION BY and
Let's say I write a table and add data such as
create table test
(
    id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
    data int4,
    created_at timestamptz NOT NULL DEFAULT now()
);

insert into test(data, created_at) values(1, '2021-04-01');
insert into test(data, created_at) values(2, '2021-04-01');
insert into test(data, created_at) values(3, '2021-04-02');
insert into test(data, created_at) values(4, '2021-04-03');
insert into test(data, created_at) values(5, '2021-04-05');
insert into test(data, created_at) values(6, '2021-04-07');

and then create a queries such as
SELECT
  created_at as "Date",
  count(1) as "Daily count"
FROM test
WHERE created_at >= '2021-04-01'
  AND created_at <= '2021-04-30'
GROUP BY 1

giving

Date
Daily count

2021-04-01 00:00:00
2

2021-04-02 00:00:00
1

2021-04-03 00:00:00
1

2021-04-05 00:00:00
1

2021-04-07 00:00:00
1

with data as (
  select
    date_trunc('day', created_at) as day,
    count(1)
  from test
  group by 1
)
select
  day,
  running_total(count) over (order by day asc rows between unbounded preceding and current row)
from data

day
running_total

2021-04-01 00:00:00
2

2021-04-02 00:00:00
3

2021-04-03 00:00:00
4

2021-04-05 00:00:00
5

2021-04-07 00:00:00
6

But as noted, how could these two be combined without gaps on daily values? Somehow it feels I get close but bump into some (syntax) problem. Maybe those two queries are the simplest and cleanest examples of what I'm thinking.

You write in your question:

I think I'd need GENERATE_SERIES, but I don't know how to do it.

Indeed you do - and you can as follows (all the code below is also given in the fiddle here):
CREATE TABLE cal_tab (cal_date) AS
(
  SELECT  GENERATE_SERIES
  (
    '2021-04-01'::DATE,
    '2021-04-07'::DATE,
    '1 DAY'
  )
);

Just to check:
SELECT cal_date::DATE FROM cal_tab;

Result:
cal_date
2021-04-01
2021-04-02
2021-04-03
...
... snipped for brevity - you determine how many rows there are with the
... second parameter to GENERATE_SERIES
...

Then, I use your data:
CREATE TABLE test
(
    id         INT4 NOT NULL GENERATED ALWAYS AS IDENTITY,
    data       INT4,
    created_at DATE NOT NULL DEFAULT NOW()
);

Populate (slightly modified from the question - so as not to have the id the same as the provided data (i.e. 1...6), - instead, I explicitly inserted hundreds - improves legibility):
INSERT INTO test (data, created_at) VALUES (100, '2021-04-01');
INSERT INTO test (data, created_at) VALUES (100, '2021-04-01');

INSERT INTO test (data, created_at) VALUES (200, '2021-04-02');
INSERT INTO test (data, created_at) VALUES (300, '2021-04-03');
                                                               -- GAP for 4th of April
INSERT INTO test (data, created_at) VALUES (500, '2021-04-05');
                                                               -- GAP for 6th of April
INSERT INTO test (data, created_at) VALUES (600, '2021-04-07');

And then run the following SQL:
SELECT
  DISTINCT  -- try with and without DISTINCT
  ct.cal_date::DATE,
  COALESCE(t.data, 0) AS data, -- t.created_at::DATE,

  COUNT(t.created_at) OVER (PARTITION BY t.created_at
                              ORDER BY ct.cal_date ASC) AS "Cnt/day",
  COUNT(t.created_at) OVER (ORDER BY ct.cal_date ASC) AS "Cum. cnt/day",

  SUM(t.data) OVER (ORDER BY ct.cal_date::DATE ASC) AS "Sum/day"
FROM
  cal_tab ct
LEFT OUTER JOIN test t
  ON ct.cal_date = t.created_at::DATE
ORDER BY ct.cal_date::DATE ASC;

Result:
  cal_date  data    Cnt/day Cum. cnt/day  Cum. sum/day
2021-04-01  100           2            2           200
2021-04-02  200           1            3           400
2021-04-03  300           1            4           700
2021-04-04    0           0            4           700
2021-04-05  500           1            5          1200
2021-04-06    0           0            5          1200
2021-04-07  600           1            6          1800

A result which, I believe covers all your requested elements.
In this case, you don't have to worry about the FRAME clause (i.e. the ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) bit - FRAMEs are a kind of sub-partitioning method - but the long phrase above is actually the default - see here for a good introduction to window frames in PostgreSQL.
You could also (depending on your requirements) use a CTE (Common Table Expression) for your query if you don't wish to create a permanent calendar table (see my answer here for an example of this). (+1 for having made me think!).


Consume data from other database motor from stored procedure in Oracle 12c I have a project in which you must develop services in Oracle OSB which will call a procedure stored in Oracle 12c. The issue is that I also need data from an Informix database to build those services.
The initial idea was to make a DBlink from Oracle to informix, but it is very expensive, because it needs licensing. I need alternatives to this problem.

Regards!

Database Gateway and Generic Connectivity (DG4ODBC) Licensing Considerations (Doc ID 232482.1)

  The Oracle Database Gateway for ODBC (DG4ODBC) is included in your
  Oracle Database Server license without extra cost.

  ...

  While the
  Database Gateway for ODBC (DG4ODBC) does not require an additional
  license from Oracle, a third party ODBC driver is required, which may
  require a purchase independent of Oracle.

Yes, Database Gateway for Informix requires the purchase of additional license.

No, Database Gateway for ODBC does not require the purchase of additional license.

All you need is a 3rd party ODBC driver which is compatible with Informix, then you can configure DG4ODBC to use that.

I have done this with IBM DB2, PostgreSQL, SQL Server before, it should work with Informix as well.


Mongodb as windows service I am using mongo db with replica sets. I am starting the mongodb using command prompt.

Python Script:

os.system('start mongod --configsvr --dbpath C:\mongoConfigData/configdb1 --replSet conf --port 27019')
os.system('start mongod --configsvr --dbpath C:\mongoConfigData/configdb2 --replSet conf --port 27020')
os.system('start mongod --configsvr --dbpath C:\mongoConfigData/configdb3 --replSet conf --port 27021')

os.system('start mongos --configdb "conf/localhost:27019,localhost:27020,localhost:27021" --logpath E:\mongoSLogs\mongoLog.log  --port 26060')

os.system('start mongod --replSet conf --shardsvr   --dbpath E:\data --storageEngine wiredTiger --wiredTigerJournalCompressor zlib --wiredTigerCollectionBlockCompressor zlib --wiredTigerCacheSizeGB 7 --port 27010 ')

os.system('start mongod --replSet conf --shardsvr  --dbpath F:\data --storageEngine wiredTiger --wiredTigerJournalCompressor zlib --wiredTigerCollectionBlockCompressor zlib --wiredTigerCacheSizeGB 7 --port 27011 ')

os.system('start mongod --replSet conf --shardsvr  --dbpath G:\data --storageEngine wiredTiger --wiredTigerJournalCompressor zlib --wiredTigerCollectionBlockCompressor zlib --wiredTigerCacheSizeGB 7 --port 27012 ')

I want to start the mongodb as a windows service.
Let me know how to convert the above in to windows service

I would  recommend a Configuration File instead a bunch of command line options.

There have a closer look at Windows Service Options

Once you created the configuration file you can install the service like this:

mongod.exe --config c:\MongoDB\config\mongo.cfg --install

Note, the default service name is MongoDB. Unless you specify processManagement.windowsService.serviceName you have to use os.system('net start MongoDB') in order to start the service.

However, if you like to run more than one MongoDB service, the service names have to be distinct.

Running start mongod ... starts the service in foreground and the terminal remains open.


Preferred node for read on Availability Group in a multisite configuration We are going to set up a multi (at least 2) site configuration, and we want to achieve both HA as well as a good resource usage.

The sites will be interconnected through a high speed, dedicated fiber network, so latency will generally be very low.

However we still prefer that each site uses the nearest secondary server for read intent data.

Is this possible?

It is possible, but it isn't an out of the box configuration.  What you would need to do is setup a separate DNS named which points to all the secondary servers that you want to route requests to.  Then setup the read only routing to point to that secondary DNS name.  Then assuming that AD is setup correctly when people are redirected to that DNS name they'll always go to the local one.


How to change DB file location with Replication I need to move few DB files to another location on same server due to low disk space. Replication is present with that DB and this scenario is on subscriber server. Is there any way to do this without doing reconfiguration?

Here's how I'd do it:

Schedule some time where the database can be down for the amount of time to move the files
Execute a series of 'alter database [foo] modify file (name = 'foo_1', filename = 'new location here') statements
alter database [foo] set offline (you'll have to kill any active spids in the db or wait for them to finish their business)
move your files
alter database [foo] set online

If you want to be really clean about this, you can stop the distribution agent while the database is offline and restart it when it's back online.  But that's not necessary.  Just so you know, I've also done this with a distribution database and would imagine that it would work fine with a publisher, too.