How to store date & time in DECIMAL(18,3)? I am currently working with a database scheme that contains a DECIMAL(18,3) column that is supposed to contain date and time. Sadly, I do not have any example data and need to create it myself.

However, I do not know how to correctly format the dates I want to insert, because when you add together year, month, day, hours, minutes and seconds, you get 14, if you add 3 more numbers for the milliseconds you get 17. Where is the eighteenth number hiding from me?

MySQL has two date+time formats:

For 1-second resolution, use the datatype DATETIME or TIMESTAMP:

mysql> SELECT NOW() AS sample_DATETIME, UNIX_TIMESTAMP() AS sample_TIMESTAMP;
+---------------------+------------------+
| sample_DATETIME     | sample_TIMESTAMP |
+---------------------+------------------+
| 2018-11-26 12:45:15 |       1543265115 |
+---------------------+------------------+

To include milliseconds, use DATETIME(3) or TIMESTAMP(3):

mysql> SELECT NOW(3) AS sample_DATETIME;
+-------------------------+
| sample_DATETIME         |
+-------------------------+
| 2018-11-26 12:46:35.093 |
+-------------------------+

Think of DATETIME as a picture of the clock.  Note that it has suitable hiccups when Daylight Savings comes or goes.

TIMESTAMP is essentially UTC, but converted from/to your timezone.

I have not heard of anyone using DECIMAL.


Meeting with DBAs regarding server upgrade project - Common concerns I'm more of a network/windows admin and I've been tasked with overseeing a SQL server upgrade project. I need to meet with the DBAs and discuss their needs / wants regarding the upgrade. I don't want to go in totally blind, so I thought I would ask you guys first. We are moving from SQL Server 2008 to SQL Server 2008 R2 and likely moving to Windows Server 2008 R2 where possible. As a DBA, what would be your concerns with such an upgrade? Anything you'd like to see happen at the same time?

Without any information on the nature of the system (see my comment on the question) or why you're upgrading, it's difficult to offer any specific and/or concise advice.

As a starting point, there are plenty of excellent checklists for building a new server, Brent Ozar and Jonathan Kehayias are two good examples. From the many recommendations in those guides, there are a couple of items worth highlighting. These are those which I encounter mis-configured most often.

Storage - Check partition alignment, although this is less of an issue for W2K8+ as manual partition alignment is not usually required (non-standard/exotic SAN aside). Format with 64k block size, not 4kb default, for data drives. Run a basic set of SQLIO tests so you a) have a yardstick to compare server X with server Y and b) you have a level of confidence in this servers capability.
Antivirus - Ensure MDF, NDF and LDF files are excluded from your antivirus scanner. These can cause chaos on a busy system, fix it before it does.
Model database - Any changes made to the Model database are reflected in every user database you create. Set the model size and growth rates to sensible values for your environment/system. In lieu of any other guide, SIMPLE recovery (in case someone forgets to configure log backups), 2048MB datafile with 1024MB growth, 1024MB logfile with 512MB growth (as per JK's checklist).

For a server/system/application which is considered business critical and is subject to a tight SLA, plan for the worse. With these you need to be as close to 100% sure as possible that the upgrade doesn't turn out to be a downgrade as far as the business or users are concerned. For that level of confidence you're going to have to test, test and test some more.

In any large scale system there will be a query or two which required a hack/workaround/hint to optimise. Some are deliberate and based on the best advice at the time, others will be emergency fixes that got forgotten about. These are the queries that will unexpectedly change behaviour on upgrade as a result of tweaks and improvements to the query optimiser. Only one way to spot them, run them on your new kit.

My preferred approach is workload capture and replay with the RML tools. There is an excellent guide from SQL CAT to using RML for exactly this purpose, Precision Performance for Microsoft SQL Server using RML Utilities 9.0.


In a SSDT project how to avoid redeploying the assembly? I have a SSDT project that has a few sql clr elements. They rarely get altered but every time I redeploy my db project it decides it must drop the assembly and recreate it.

I think its somewhat wasteful to keep doing that. Is there anyway I can configure it so that it will only recreate the assembly anytime I actually change the correlated C# sprocs?

If this will be helpful here is the profile I am using to generate the change script:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>#{DatabaseName}</TargetDatabaseName>
    <DeployScriptFileName>#{DatabaseScriptName}</DeployScriptFileName>
    <TargetConnectionString>Data Source=#{DatabaseServer}</TargetConnectionString>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <ScriptDatabaseOptions>True</ScriptDatabaseOptions>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <GenerateSmartDefaults>True</GenerateSmartDefaults>
  </PropertyGroup>
</Project>

I use octopus to do the deployment.

I've got the same problem when using the Schema Compare tool in Visual Studio 2013. From what I can tell, the reason it wants to keep deploying the SQLCLR assemblies is the Module Version ID (MVID) in the DLL. The MVID is a guid that is automatically generated by the C# compiler during compilation so each rebuild get the DLL a new MVID.

One option is to try and avoid a rebuild of the DLLs. If you're building locally with Visual Studio, you'll have to avoid the "clean" and "rebuild" options. If you're using a build server such as TFS you'll need to configure the build to not clean the workspace.

The other option that I'm going to try is to have the C# code in a separate project, and "import" the DLLs into the main database project as and when the C# code changes. Pretty much as I used to do when using the older style "DataDude" projects. Using the latest version of SSDT for VS2013, I've added the SQLCLR assembly DLL as a reference  in the main SQL Server project, and set the "Model Aware" property to "True". This properties seems to be required to allow me to reference the assembly in the store procedure / function DDL statements.


SSRS set datasource of report model I created a Report Model which will be consumed by the End users to create ad hoc reports using Report builder tool. The Report Model can be created using the Report Model Project in Business Intelligence Studio. It has 3 stages to create the Report Model.

Data Source
Data Source Views(.DSV)
Report Model(.SMDL)

How to create Report Model for multiple data sources so that the end user can access different databases (in one report model) while creating the report using Report Builder tool?

Any possibility to change the datasource of Report Model in RunTime?

Depending on the version of Report Builder, you can only build from a single data source.  I'm pretty sure this is still the case with SQL Server 2008R2, although Report Builder 3 can consume data directly from a database without needing a report model, although you need to build data sets using the individual connections and I don't think you can group items from different data sets by a common value.

You can do this gouping with SSRS (see this article for an overview), although I can't see an obvious way to do it in RB3.  This article indicates that you can't nest items based on different data sets, so it's looking like common rollups aren't possible in RB3.


Postgres, benefit of named schema va public schema? I am new to Postgres and I am trying to understand how schemas fit into everything and what is the benefit of a non-'public' schema. For example, does it provide security, access control benefits or something else?

The setup I am working with is one where postgres owns the database and tables, but grants permissions to another user to select, insert, update, and delete. Would creating the tables in the 'public' schema vs 'myotherschema' make much of a difference?

what is the benefit of a non-'public' schema.

A schema provides a standardized level of logical organization. If you've never used them, it's like asking what's the value of directories. You don't technically need to have them, but you can use them to

Organize & Componentize
Control access
Reduce namespace-collisions

Also worth mentioning, the query planner can plan across schema boundaries. In the same sense that tools that operate on files can work across directories.

  Perhaps more to the point of the Question you could explain that there is nothing special about the schema named public, just an arbitrarily chosen name for the default schema. – Basil Bourque 1 min ago

^ Valid point too. If you don't use your own customized schema, you're still using schema. It's just named public and placed in your search_path by default.


How can I set the index fill factor back to server default? I have some indexes that I was playing with on my dev server.  I set the fill factor of these indexes using the following command:

alter index all on dbo.Table
rebuild with (fillfactor=80)

That sets and rebuilds all of the index on dbo.Table.  Now what I want is to set the fillfactor back to the default of the server.  IE, at the server level all indexes are set to have a fillfactor of 95.  I want the indexes to use the server default again, not just have the fill factor set to 95.

The reason I want to do this is so that, if I script this index and move it to a new server, it will use the default fill factor of that server rather than the specified 95.

Through testing, it seems as though to get back to the default fillfactor on an index you need to drop and recreate it.  When you do an ALTER INDEX ... REBUILD, any unspecified parameters will remain the same in the index's metadata unless otherwise explicitly changed in the ALTER INDEX command.

drop index IX_YourIndex
on YourTable
go

create index IX_YourIndex
on YourTable(YourKeyColumn)
go

To verify this:

select
    name,
    fill_factor
from testdb.sys.indexes
where name = 'IX_YourIndex'

fill_factor should be set to 0 to denote that it is using the instance's default value.  (Note: 0 is for the default value, even if the instance configured fill factor is set to 95)


Using SELECT within to_tsvector call in CREATE INDEX I'm attempting to create a GIN index for full-text search on a field that may have text in it that is one of several different languages.  The languages are pre-set and we'll know which language we want to search in at query time.  Here is the schema I've got thus far...

CREATE SCHEMA source;
CREATE SCHEMA common;

CREATE TABLE common.lang (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    code TEXT NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

INSERT INTO common.lang (name, code) VALUES
  ('english', 'en'),
  ('arabic', 'ar'),
  ('chinese', 'zh'),
  ('undefined', 'und');

CREATE TABLE source.user (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    screen_name TEXT NOT NULL UNIQUE,
    profile_lang_id INTEGER NOT NULL REFERENCES common.lang(id) ON DELETE CASCADE ON UPDATE CASCADE,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE INDEX source_user_names_idx ON source.user USING GIN (to_tsvector(SELECT common.lang.name FROM source.user INNER JOIN common.lang ON common.lang.id = source.user.profile_lang_id, name || ' ' || screen_name));

It reports a syntax error at or near "SELECT", which makes sense...I didn't think I could just shoehorn a select statement in there, but I'm not sure how to correctly use a select statement within the to_tsvector call.

I looked at https://stackoverflow.com/a/21299033/4471711, however that hardcodes the languages, whereas I want the languages to be pulled from a table dynamically at index creation time.  Obviously, if/when we add a language, we would need to recreate the index.

I also looked at https://stackoverflow.com/a/15135730/4471711, however that is not in support of creating an index and only requires 1 parameter to to_tsvector, whereas I seem to need to pass in both the language and the text column.

I am also trying to create a full-text search index on a more document-style field as below:

CREATE TABLE source.post (
    id BIGSERIAL PRIMARY KEY,
    text TEXT NOT NULL
);

CREATE INDEX status_text_idx ON source.post USING GIN (to_tsvector(common_lang_id, text));

Again, with the above index, I want to also use the language which is specified by the foreign key to the lang table.

My two questions are:

How do I properly use the SELECT statement to grab the language from the lang table using its foreign key in the user table?
What if the lang is "undefined" as is one of the options?  I wouldn't mind defaulting to english for purposes of the to_tsvector call...How do I add in that default while still allowing the user table to reference "undefined"?

You don't. You can't create an index that references another table.
To set the default see default-text-search-config

Workaround

You can however use functions in an index. And functions can references external tables. That said, using this is kind of hack because changing the table (common.lang) will require reindexing and clearing the session cache.

CREATE FUNCTION common.mylookup(id int)
RETURNS regconfig AS $$
  SELECT name::regconfig
  FROM common.lang
  WHERE id = id
$$
LANGUAGE sql
IMMUTABLE;

CREATE INDEX
  ON source.user
  USING GIN (to_tsvector(common.mylookup(profile_lang_id), name || ' ' || screen_name ));

You can mark functions as IMMUTABLE which makes this permissible. If the underlying table mutates you'll have to REINDEX the index. In projects that use this hack, like PostGIS, they recreate indexes on point releases.

Followup

  @EvanCarroll, I'm trying to create the tsvector on name || ' ' || screen_name. – Brooks 4 mins ago

Full text search isn't there to do what you think it does. It's not there to search multiple fields. It's there to vectorize word content and make use of dictionaries, stubbing, lexers, gazetteers, stop-word elimination, and a slew of other tricks none of which apply. If this doesn't make sense to you, you'll have to read the docs. If what you want is grep then FTS is only seldom what you want. If you want to grep over small chunks of non-standard text (like names) it's not what you want. What you likely want trigram indexing.

If all you want is a %term% on two fields, you're better off just doing that with a trigram index.

CREATE EXTENSION pg_trgm;
CREATE INDEX ON source.user USING GIN ((name || ' ' || screen_name) gin_trgm_ops);
WHERE name || ' ' || screen_name like '%$1%';

Or even better,

CREATE INDEX ON source.user USING GIN (name gin_trgm_ops, screen_name gin_trgm_ops);
WHERE name LIKE '%$1%' OR screen_name LIKE '%$1%';


Need advice on how to migrate from existing database mirroring to availability groups Need some practical/general guidance on moving from database mirroring to AOAG

Current setup - 2 standalone servers with database mirroring configured as async.

Server A: Windows 2016 running SQL Server 2017, no cluster, simple standalone server as Primary
Server B: Windows 2016 running SQL Server 2017, no cluster, simple standalone server as Mirror
File share server will be provided in different DC

Distance between the servers is about 150 miles.

We need to use the above servers only and convert database mirroring to AG's with automatic failover option to provide HA as a bonus because with the above setup it's mostly a DR solution.

Clusterless AG came to my find but it seems HA wont be available as automatic failover might not work. Am I missing something here or is it possible?
We can have an outage up to 8 hours to convert db mirroring to AG if needed.

How should I plan or if any resources available to do this? I tried searching for lot of articles but could not find any, please suggest

As a general rule, this migration should be simple. AGs use the database mirroring framework and endpoints for transmitting log blocks, so the transition is quite straightforward.

One thing to note is you currently run your mirrors async but you've stated you want to run your AG sync to provide HA. This shouldn't be a problem, but you need to check your latency between the servers to ensure you're not creating a bottleneck that is going to hamper write performance.

Synchronous commit AGs require all transactions to be hardened to the log on the secondary prior to committing them on the primary, so if your latency across the 150 miles between servers is too high, this can cause substantial performance issues.

  Clusterless AG came to my find but it seems HA wont be available as
  automatic failover might not work. Am I missing something here or is
  it possible?

No, you are correct. Clusterless AGs, or read-scale availability groups, are not an HA option. There is no automatic failover in a read-scale availability group. See this link here which states:

  This is not a high-availability setup. There is no infrastructure to
  monitor and coordinate failure detection and automatic failover.
  Without a cluster, SQL Server can't provide the low recovery time
  objective (RTO) that an automated high-availability solution provides.
  If you need high-availability capabilities, use a cluster manager
  (Windows Server Failover Cluster on Windows or Pacemaker on Linux).

  The read-scale availability group can provide disaster recovery
  capability. When the read-only replicas are in synchronous-commit
  mode, they provide a recovery point objective (RPO) of zero.

To your second point-

  We can have an outage up to 8 hours to convert db mirroring to AG if
  needed.

You could do the following:

Enable clustering on Server A and Server B.
Configure the Availability Group with both SQL instances on Server A and Server B as replicas
Stop log backups on both servers
Remove mirroring from DBs on Server A/B
Add databases to AG on Server A
Join databases to AG on Server B - This should be fairly seamless as the DBs on Server B should in recovery since mirroring was disabled. As long as no log backups have occurred, you should be able to sync the DBs in the AG without backup/restore or seeding.
Update App connections to use the listener name.

Some of these steps require the servers or SQL Server instances to be restarted, so it will be multiple outages but each is shorter than your allowed 8 hours.

This is a very simple overview, and the process needs to be tested and any problems identified and resolved prior to running through this in production. If you have a non-production environment, start there and script every step so you can replay the actions in production accurately.

If you don't have a non-production environment, ask your sysadmins to clone your servers in an isolated network where you can practise the change. If you're running physical servers, then testing without a non-production environment is going to be difficult, but you could build a similar environment in Azure or AWS to dry-run the process.

Here is a useful link on configuring clusters for AGs. Its a bit older but the information is still valid. If you're not confident with this process, especially if you do not have a non-production environment available, it may be better to engage a third-party contractor/consultant/expert who can plan and execute this for you.


Database Replication Issue We had two MySQL 5.6.35 Server in our organization working as Master/Slave replication. The replication was working fine in this situation. But we have a new requirement to place another server which will be replicated from the slave server.

For example:

Server1 - Will work as master

Server2 - Will work as slave in Server1 perspective and will work as master in Server3

Server3 - Will act as slave and will receive replication from Server2

Kindly help me on that.

You are asking for Master --> Relay --> Slave.  This is quite possible.

Server2: STOP SLAVE
Server2: Pretend it is a Master and do the operations necessary to add its first Slave (Server3).  (Dump data Server2, copy to Server3, etc)
Server2: START SLAVE

Be sure to turn on log_slave_updates and log_bin and have different server-id.

Why do you want M->R->S?  It is a rather unusual setup.  Is it the start of something more grandiose?  I ask because there may be other tips to impart.


SQL Server detected 2 sockets with 4 cores per socket but only one NUMA node We have a SQL Server 2012 SP 2 CU 8 instance in a VMWare host. The operating system is Windows Server 2012 R2.

Windows Task Manager says that we have two sockets and 8 virtual cores. This number corresponds with the actual hardware beneath the guest.

In Task Manager I also can't change the view to NUMA nodes.

In SQL Server still only one NUMA node to see

Also this snippet from Glen Berrys diagnostics queries gives me this information

EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';

  SQL Server detected 2 sockets with 4 cores per socket and 4 logical
  processors per socket, 8 total logical processors; using 8 logical
  processors based on SQL Server licensing. This is an informational
  message; no user action is required.

From all this information i can suspect that we are running on only one NUMA node but across two sockets.

My Questions:

Is it right that we are running on only one NUMA node?
Can this hurt performance even when everything is located on one NUMA node?
What component exactly dictates the NUMA boundaries? E.g. if I have a
two socket mainboard with two Intel E5 processors does the mainboard
dictate how many NUMA nodes I have or the processor.

I think there is a lot of misinformation out there about NUMA nodes. If someone can answer my question or point to a useful resource that explains this concept in detail that would be really appreciated.

The following two articles make it clear that VMware will present a virtual NUMA configuration to the guest OS that is optimal.  Accordingly, the number of sockets that are configured on the VM are more or less irrelevant as to the number of NUMA nodes.  If the actual hardware has at least 8 cores in a NUMA node, then VMware is only going to present one NUMA node to a guest that has 8 CPUs, regardless of the VM's socket count.

Does corespersocket Affect Performance?

Virtual Machine vCPU and vNUMA Rightsizing – Rules of Thumb

Determining NUMA node boundaries for modern CPUs contains two helpful quotes that show that, even on hardware, number of sockets is not always equal to number of NUMA nodes:

When we originally did this analysis, the common multi-core processors were dual core or at most quad core.  On these chips, the hardware manufacturers divided the NUMA boundaries into cores, rather than sockets.  But it appears that that configuration is not the same for the larger multi-core (6, 12, etc.) chips.

and

In short, don’t assume that your NUMA boundaries are divided into cores – it very much depends on your specific CPU architecture.

I also ran across an TechNet article,Soft-NUMA (SQL Server),  stating that each socket is usually represented by one NUMA node, but it doesn't explain when it wouldn't be.

You also might check the BIOS of the hardware to ensure that NUMA is enabled.  I would be surprised if it was disabled, but I've learned through my career to never assume that someone didn't do something completely irrational.

Answers to your questions:

Given the evidence at hand, yes since both SQL Server and Windows are telling you that there is only one NUMA node, and we have some documentation from sources at VMware that it will present the optimal vNUMA configuration to the guest, which will be one NUMA node as long as the guest is configured with less than or equal to the amount of cores in the hardware sockets, and the number of sockets presented to the guest is irrelevant.
If the system architecture is telling the OS that there is one NUMA node, then there is one NUMA node and that is best because all CPUs are using the same memory.  If it were actually two NUMA nodes but SQL Server only sees one, then yes, it would impact performance for anything that runs parallel.
The CPU architecture, which in turn dictates the mainboard architecture.  On VMs, the NUMA configuration presented to the guest is determined by virtualization host (VMware, Hyper-V, etc.).


Oracle: Scopes of the CURSOR_SHARING parameter In Oracle databases (12.2 and above), is it possible to configure the CURSOR_SHARING parameter differently for different schemas, tables or views, or is that parameter always global?

You can create LOGON TRIGGER ON SCHEMA and use
execute immediate 'alter session set CURSOR_SHARING=...';

to set it on session level


tuned lacks oracle profile. How could I switch to it? I'm trying to configure tuned using this tutorial

And following it I'm trying to switch to oracle profile:

# tuned-adm profile oracle

  Invalid profile. Use 'tuned-adm list' to get all available profiles.

# tuned-adm list

Available profiles:
- desktop-powersave
- spindown-disk
- default
- virtual-guest
- virtual-host
- throughput-performance
- enterprise-storage
- latency-performance
- laptop-battery-powersave
- laptop-ac-powersave
- server-powersave
Current active profile: default

So how to switch to profile that does not exist in the list?

Looking at tuned-adm oracle's documentation, I've found the next:

  oracle   A profile optimized for Oracle databases loads based on
  throughput-performance profile. It additionaly disables
  transparent huge pages and modifies some other performance related
  kernel parameters. This profile is provided by
  tuned-profiles-oracle package. It is available in Red Hat
  Enterprise Linux 6.8 and later.

So, the installation of the tuned-profiles-oracle package solved this issue:

# yum install tuned-profiles-oracle

# tuned-adm profile oracle

# tuned-adm active

Current active profile: oracle
Service tuned: enabled, running
Service ktune: enabled, running


Oracle - Sync Sequence nextval using CONNECT - side effects? DB
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Action
After a data migration - from an old table to a new table - we created a new sequence and executed this command to sync it's next value to be equal to table maximum id.
SQL
-- SYNC SEQUENCE WITH TABLE
SELECT level, {SEQUENCE_NAME}.NEXTVAL
FROM dual
CONNECT BY level <= (
 SELECT MAX(ID)
 FROM {TABLE}
);

Question

Is there any side effect on calling this SQL - since the keyword CONNECT is used to make connections to DB ?
Is there a more efficient way to do such task?

Useful Links
Oracle - CONNECT Keywork - Reference

CONNECT in this context has nothing to do with database connections.

hierarchical_query_clause ::=

You can change the increment of a sequence to set it to a required value. Example:

SQL> create sequence s1;

Sequence created.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
         1

SQL> alter sequence s1 increment by 100;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       101

SQL> alter sequence s1 increment by 1;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       102

You can also use the undocumented RESTART clause, but that does not work in 11.2:

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 30 12:44:26 2019
Version 19.3.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create sequence s1;

Sequence created.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
         1

SQL> alter sequence s1 restart start with 101;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       101

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       102


Clever repeatable benchmarking and bottleneck detection test bench - PostgreSQL Has anyone usefull ideas, inspration or literature for me to design a good test bench for PostgreSQL. I need repeatable results, which are applyable in realworld and production environement (no Journaling off for example ;) ). I need the testbench to see effects on query and/or configuration tuning. Further it would be interessting to detect CPU, Memory or HD bottlenecks.

I know this question is rather general, but right now I try to understand the process of improving prameters and tuning queries through consistent and repeatable results. Down the road it is about tuning and optimizing a routing database for GIS applications.

Thanks for your input!
Martin

While I can't speak to measuring specific metrics like CPU, storage throughput, etc. in your environment, I have found HammerDB to be a good tool for running a good, standardized benchmark against your databases.  This tool runs an unofficial TPC-C benchmark against your database. You can configure for concurrent user connections and workloads.  Using this conjunction with other metric tracking will help you evaluate your system for bottlenecks in an application agnostic manner.

As a note, HammerDB supports the following platforms at this time:

Oracle
SQL Server
MySQL
PostgreSQL
Redis


PostgreSQL: psql command-line client does not show all schemas I have PostgreSQL 9.3 server. Connecting using an JDBC client (Squirrel) and executing

select schema_name from information_schema.schemata

shows me all the schemas on the server.

Connecting the same using psql via an SSH session:

su -l postgres
psql -U postgres -W -p 5432 -h localhost

and the same select shows only a few schemas:

schema_name
--------------------
pg_toast
pg_temp_1
pg_toast_temp_1
pg_catalog
public
information_schema

There is only one DB instance on the server. Username and password are the same. Why is that? Do I need to configure something for psql?

It's not the client application that makes the results differ, but the database user you're connected with.

In 9.3, schemata returns only the schemas that the session's user owns, either directly or indirectly through a granted role.

The doc says:

  The view schemata contains all schemas in the current database that
  are owned by a currently enabled role.

Note that it was changed in later versions to be less restrictive.

See What permissions are required to return rows from information_schema.schemata? (9.3)
and How to check if PostgreSQL public schema exists? on stackoverflow


Raise error if BCP command fails to dump data into a file Using the BCP command I am generating files of SQL Server DB tables. The BCP command creates an empty file for each table when it's unable to dump data into the files. This can be caused by an error in query written or an empty variable is passed to the BCP command.

Is there any way we can capture these events as errors and make it return some error code?

I am performing this from a stored procedure. Is there any way I can handle this in SP?

Powershell is your friend here. When working with cmd commands in Powershell you can use the $LASTEXITCODE variable to read the result of the command you executed.

The code below passes a BCP command into the Invoke-Expression cmdlet and captures it's output.

$OutputPath = "C:\temp\Numbers-20151230.dat"

try
{
    $Command = "bcp dbo.Numbers out $OutputPath -T -n -S Localhost\JamesA_Test -d UtilityDB"
    $Output = Invoke-Expression -command $Command

    if ($LASTEXITCODE)
    {
        throw $Output
    }
}
catch
{
    Write-Host "BCP command failed: $Output"
}

I'm not sure how you want to handle the error so I just used Write-Host to display the error for this example. You could log the error to the event log, a file, a table in SQL, etc.


Scalability: Desiging a message table This question might be deadly simple but I wanted to make sure I am going through the correct path and not making any simple and irrevertible mistakes.

My project will be based on messaging and it won't be as less-used as a side tool like in Twitter or Facebook (not that they are not used but it's not the key option presented to users)

I was wondering will a simple table design like;

from  (id)
to  (id)
content  (text)
created_at (date)
isRead (boolean)

will suffice because, even though it is planned to be a small project, I do not believe that all the messages should be stored in one table and for each user when they want to view their messaging history, I shouldn't be scanning the entire table to do so. I am thinking about how scalable would this structure be and how would I benefit using a NoSQL DB like mongoDB.

You can go through the following embedded json document to implement
  your message collection and can ensure index on "mail_id" key to avoid
  scanning all documents. For your information here collection serve as
  table and each document serve as row of a table.

{
mail_id:string
sentMails:{
            [
                {
                sent_at: datetime,
                subject: string,
                sent_to: string,
                cc_to:[array of mailid strings],
                bcc_to:[array of mailid strings],
                attachments:[
                                {
                                attachment_name:string,
                                attachment_type:strng
                                }
                            ],
                sent_mail_content: string
                }
            ]
            }

receivedMails:{
                [
                    {
                    received_at: datetime,
                    subject: string,
                    received_from: string,
                    cc_to:[array of mailid strings],
                    attachments:[
                                    {
                                    attachment_name:string,
                                    attachment_type:strng
                                    }
                                ],
                    received_mail_content: string
                    }
                ]
            }
}

I hope it will help.


How to know what are the necessary log files for a backup in DB2? Is there a way to know what are the necessary log files for specific backup?

Un example of backup and restore process:

db2 BACKUP DATABASE TST1 ONLINE TO '/db2/backup'
db2 RESTORE DATABASE TST1 FROM '/db2/backup' TAKEN AT 20150826131633 TO '/db2/data' INTO TST2

db2 rollforward db TST2 to end of logs
db2 rollforward db TST2 complete

Now, in order to be able to rollfoward to end of logs I need to copy the logs from the backuped DB to the target archive dir. What I do is coping the last 2 days and it always passed. Some day it will not.

So how can I know during backup time(right after it) what files exactly in addition I need to backup..?

By default an online backup places the logs necessary to rollforward to the point of the end of the backup inside of the backup file. You can extract the logs from the backup file and then ROLLFORWARD to the end of them. Use the LOGTARGET clause of the RESTORE command to extract the logs to a location (I have tended to restore them to a location other than my logs directory.) Then use the OVERFLOW LOG PATH clause of the ROLLFORWARD command to point the ROLLFORWARD command to the logs you just extracted.

Example:

db2 RESTORE DATABASE TST1 FROM '/db2/backup' TAKEN AT 20150826131633 TO '/db2/data' INTO TST2 LOGTARGET '/tmp/extractedlogs'

db2 ROLLFORWARD db TST2 TO END OF LOGS AND COMPLETE OVERFLOW LOG PATH ('/tmp/extractedlogs')


Between Operator for varchar datatype I have column 'hdd' having values '1GB','500GB,''1TB' and '100TB'

When retrieving rows where value between 1TB  and 100 TB like this:

select * from product where hdd between '1TB' and '100TB'

It returns the rows that contain 1GB also.

Also tried like this:

select *
from product
where cast(replace(hdd, 'TB','') as int) between 1 and 100

It said error near ')int

I'm using mysqli.

The column that you have is a VARCHAR so the ordering applied by the RDBMS will be alphabetical instead of numeric.

What is the smallest possible unit of storage for a HDD these days? GB? One way to solve your problem would be to create another column that transforms the storage into GB (or some other unit). Make sure that this column is integer or numeric and not a VARCHAR. Using your example data and assuming 1000 GB per TB:

'1GB', 1
'500GB', 500
'1TB', 1000
'100TB', 100000

Filtering on the second column is a much more straightforward way to get the results that you want.

If you don't want to create a new column, it would be possible, but messy, to do that conversion within the SQL query.


Part of parallel execution plan has serial portion I have a big linq to sql query which runs in parallel mode.
It is returning results in 8 seconds and it is pretty good.
I noticed in the execution plan that the extreme right part(from where execution plan is read) of the execution plan has a serial portion.
I checked the execution plan and i see that the serial part is calculating some expressions such as the where conditions Start Date and End date is applied and calculated.
But i don't understand why it is being in serial mode.
AS per the screenshot,it says Scalar Operators[Expr1169] and Scalar Operator(Expr1170)

As you can see in the next screenshot, after the expressions are calculated and after from Nested Loops ,the execution goes into Parallel mode.
I am trying to understand the serial part of the query ,because i have optimized the query few times to strictly run in parallel mode.
I have removed some scalar functions earlier and other db objects which can cause serial execution.

Screenshot 1

Screenshot 2

Please find the anonymized execution plan.
Execution PLan

Additional Info:

Statistics are updated
Index Fragmentation is taken care off
96 GB out of 128GB is used by SQL Server.
SQL Server 2016

Statistics are updated.

In answer to the question being asked (why is this portion of the query serial), I would ask another question: what would you gain from that part of the query being parallel?

Zooming in a little on one of the small serial portions of the plan that you mentioned:

There is only one row coming out of the "Merge Interval" operator.  Having that one row flow in to a parallel nested loops join would result in essentially the same behavior as the current plan: a serial nested loops join (with the added negative repercussion of there being unnecessary thread reservations).

There will be at most 2 rows coming out of the "Merge Interval" operator, depending on the specific values of the parameters being used in the query (manifested as the separate "Constant Scan" operators see this question and answer for a general explanation of what's going on there).  In the 2-row scenario, you're still unlikely to get a lot of benefit from parallelism here.

Overall: even if you were able to manage to get the NL join to be parallel, it doesn't seem like it would help you very much.

For a more general treatment of why parallelism might be inhibited in portions of a query plan, or the entire thing, see SQL not engaging parallelism for extremely large query.


How can user determine which hash their password uses? Is there a query that a MySQL user that does not have admin rights can run to determine if they are using the old (pre-4.1) hash or the new (4.1-and-later) hash? This user was explicitly granted access only to their own database and not to any of the system tables (such as mysql.user).

This is on a MySQL 5.5 system.

No, a user would need SELECT on mysql.user to be able to read the password column, or the SUPER privilege to be able to see the password in the output of SHOW GRANTS. I think both of those would be classed as "admin rights".


MySQL replication problem: slave has become out of sync I have a Master & Slave setup. Embarrassingly I managed to execute some data changing statements (INSERT and UPDATEs across 3 tables) on the Slave. Obviously this stopped the Slave replication.

I don't need the updates I accidentally made on the Slave. How can I get it back in sync with the Master?

A bit more information would be useful - did you update a single table, or multiple tables? Was it a structure change or a pure data change?

It may be possible to get the original table from the master, dump it and restore to a new table on the slave, rebuild the data on the slave by matching the records in the slave data to that of the master table and updating data fields as necessary using some update queries with joins between the two tables on the primary key.

Otherwise, can you go back to a recent backup and restore that to the slave, including the master and slave locations for that backup, so that you can then play-forward through the relay / master logs on the slave in order to catch up with the master??


MySQL 8.0 cannot insert NULL values to date field I'm trying to insert a data from file to MYSQL 8.0 using the InnoDB engine.

I'm using this query:

LOAD DATA INFILE '/var/lib/mysql-files/item_new_1_4.dat'
INTO TABLE Item FIELDS TERMINATED BY '|';

I'm getting an error message:

ERROR 1292 (22007) at line 1: Incorrect date value: 'NULL' for column 'i_rec_end_date' at row 1

The table was created by this query:

CREATE TABLE IF NOT EXISTS Item(
    i_item_sk integer ,
    i_item_id char(16) NOT NULL,
    i_rec_start_date date,
    i_rec_end_date date,
    i_item_desc varchar(200),
    i_current_price decimal(7,2),
    i_wholesale_cost decimal (7,2),
    i_brand_id integer,
    i_brand char(50),
    i_class_id integer default ,
    i_class char(50),
    i_category_id integer,
    i_category char(50),
    i_manufact_id integer,
    i_manufact char(50),
    i_size char(20),
    i_formulation char(20),
    i_color char(20),
    i_units char(10),
    i_container char(10),
    i_manager_id integer,
    i_product_name char(50),
    PRIMARY KEY (i_item_sk)
);

The data at line 1 is:

1|AAAAAAAABAAAAAAA|1997-10-27|NULL|Powers will not get influences. Electoral ports should show low, annual chains. Now young visitors may pose now however final pages. Bitterly right children suit increasing, leading el|27.02|23.23|5003002|exportischolar #2|3|pop|5|Music|52|ableanti|N/A|3663peru009490160959|spring|Tsp|Unknown|6|ought|

It's because MySQL is actually trying to insert the literal string 'NULL' into a DATE column, not the actual value NULL.

Your choices are:

Global search for |NULL| in your file and replace with ||
Change your LOAD DATA statement to deal with it:

  LOAD DATA INFILE '/var/lib/mysql-files/item_new_1_4.dat' INTO TABLE
  Item FIELDS TERMINATED BY '|'
      (i_item_sk integer ,
                                      i_item_id,
                                      i_rec_start_date,
                                      @i_rec_end_date,
                                      i_item_desc,
                                      i_current_price,
                                      i_wholesale_cost,
                                      i_brand_id,
                                      i_brand,
                                      i_class_id,
                                      i_class,
                                      i_category_id,
                                      i_category,
                                      i_manufact_id,
                                      i_manufact,
                                      i_size,
                                      i_formulation,
                                      i_color,
                                      i_units,
                                      i_container,
                                      i_manager_id,
                                      i_product_name )  SET
      i_rec_end_date = nullif(@i_rec_end_date,'NULL');


Sum multiple columns, based on distinct values in different Columns I have the following table, in an Azure SQL DB that has duplicate values that I'm trying to Sum.
Here is the Logic: If the PaymentID is unique, then Sum Payment,
If the creditID is unique, then sum credit,
if the debitid is unique, then sum debit.
And take the max(source)
The idea is to get a single line, per ID with distinct values for the ID summed.

id
payment
credit
debit
Source
paymentid
creditid
debitid

1510142123
-589.53
0
0
CC
5831879
NULL
NULL

1510142123
-589.53
0
0
CC
5831882
NULL
NULL

1510142123
-155.06
0
0
CC
5898896
NULL
NULL

157771145
-126.42
0
0
CC
5885900
NULL
NULL

157771145
-58.73
0
0
CC
5885903
NULL
NULL

158088837
-55.14
0
-3.45
CC
5897306
NULL
5897303

158088837
-5.75
0
-3.45
CC
5897309
NULL
5897303

158464166
-161
0
-3.45
CC
5910551
NULL
5910548

158464166
-24.15
0
-3.45
CC
5910554
NULL
5910548

1591970734
-111.61
0
0
Bank
5939648
NULL
NULL

1591970734
-0.01
0
0
Cash
5939711
NULL
NULL

1591970734
-0.01
0
0
Cash
5939714
NULL
NULL

159297565
-708.93
20
0
CC
5943728
5910848
NULL

159297565
-0.02
20
0
Cash
5948207
5910848
NULL

For example:
158464166 | -185.15 | 0 | -3.45 | CC | 5910551 | 5910548
(in the above - I've taken the min(paymentid) to make it look nicer
Please note that although in the above snippet, Creditid and Debitid only have a duplicate ID, it's possible that they may have distinct IDs, so any code will have to be able to handle that. PaymentID will always be unique.
It is also possible that the values for payment, credit and debit may not be unique (e.g. a payment of $50 is made twice to a single ID), so we can't group on payment.
I got as far as this:
SELECT id, sum(payment), sum(credit), sum(debit), max(source), creditid, debitid
FROM (
  SELECT *,
         COUNT(*) OVER (PARTITION BY id) AS cnt
  FROM Temp_Payment) AS t
WHERE t.cnt > 1

group by id, creditid, debitid

but it's not giving the expected outcome.

For the one row you posted expected results for, this will return what you want.
In the future, please post your table as an insert script so that it's easier to work with.
SELECT
    x.*
INTO #x
FROM
(
    VALUES
        (1510142123,-589.53,0,0,'CC',5831879,NULL,NULL),
        (1510142123,-589.53,0,0,'CC',5831882,NULL,NULL),
        (1510142123,-155.06,0,0,'CC',5898896,NULL,NULL),
        (157771145,-126.42,0,0,'CC',5885900,NULL,NULL),
        (157771145,-58.73,0,0,'CC',5885903,NULL,NULL),
        (158088837,-55.14,0,-3.45,'CC',5897306,NULL,5897303),
        (158088837,-5.75,0,-3.45,'CC',5897309,NULL,5897303),
        (158464166,-161,0,-3.45,'CC',5910551,NULL,5910548),
        (158464166,-24.15,0,-3.45,'CC',5910554,NULL,5910548),
        (1591970734,-111.61,0,0,'Bank',5939648,NULL,NULL),
        (1591970734,-0.01,0,0,'Cash',5939711,NULL,NULL),
        (1591970734,-0.01,0,0,'Cash',5939714,NULL,NULL),
        (159297565,-708.93,20,0,'CC',5943728,5910848,NULL),
        (159297565,-0.02,20,0,'Cash',5948207,5910848,NULL)
)AS x (id,payment, credit, debit, [source], paymentid, creditid, debitid);

SELECT
    x.id,
    SUM(DISTINCT y.payment) AS payment,
    SUM(DISTINCT y.credit) AS credit,
    SUM(DISTINCT y.debit) AS debit,
    MAX(x.source) AS source,
    MIN(x.paymentid) AS min_paymentid,
    MAX(x.debitid) AS max_debitid
FROM #x AS x
CROSS APPLY
(
    SELECT
        SUM(x2.payment) AS payment,
        SUM(x2.credit) AS credit,
        SUM(x2.debit) AS debit
    FROM #x AS x2
    WHERE x.id = x2.id
    AND   x.paymentid = x2.paymentid
    GROUP BY x2.id,
             x2.creditid,
             x2.debitid
) AS y
GROUP BY x.id
ORDER BY x.id;


Dynamic Folder Creation when Backing up to Azure BLOB Storage Is there a way to dynamically create folders in BLOB so each DB goes into its own folder?  I know in Azure BLOB Storage they are not real folders, but I hope you get my point.  In essence I am using the @URL instead of @directory

I am backing up (TLog) 30+ DB's every 5 minutes, and it is very cumbersome to manage in BLOB.

Any insight or help would be appreciated.

Steve

I haven't seen any OOTB way to do this. I've had similar issues with backing up to Azure BLOB, though, (it can't handle stripes) so I generally have used PowerShell to fill in the gaps:

Perform your backup to disk
Use PowerShell + AZCopy to upload files to Azure Blob using Ola's default directory structure on your local disk.

I usually wrap both these into a SQL Agent job for backup and have had good results.


You do not have permission I have this grant permission below

use eNtsaRegistrationDB
go
grant select on UserReg to Users;

Cannot find the object 'UserReg', because it does not exist or you do not have permission.

Both of table were created and granted permission(UserReg) under properties on my sql server Login folder as well mapped with my Damain, although using Window authentication mode. Still getting that issue, please help mates.

Your question has already been answered in multiple comments and I hope you were able to solve them by now.

Let me try to summarize them for you in terms of roles, logins and users.

  Logins and users: Logins and Users are basic security concepts in SQL
  Server. They are often, and incorrectly, considered to be pretty much
  one in the same so it is sometimes confusing to some SQL Server users.
  Another important security concept tied to a login and user in SQL
  Server is Security Identifiers (SID). A login is a security principal
  at the scope of the SQL Server instance, and a SQL Server instance can
  contain numerous databases. There are two main types of logins;
  Windows authenticated login and SQL Server authenticated login. Simply
  stated, a login allows you to connect to a SQL Server instance.

  Once you are connected to SQL Server instance, you will typically need
  access to a particular database. To allow access to a database, this
  login must be mapped to a database user. Internally within SQL Server,
  a login is mapped and identified to a user using security identifier
  (SID).

  Roles: Using SQL Server database roles, is the simplest
  security method to assign and manage user permissions. I think this is
  the most common method that Database Administrators (DBA) use to
  handle permissions using either fixed database roles or creating
  user-defined database roles. This comes from over two decades of doing
  SQL database administration work.

  Traditionally SQL Server provides two types of database-level roles:
  fixed-database roles that are predefined in the database and
  user-defined database roles that you can create. The database roles
  are defined at the database level and exist on each database. When the
  DBA maps the logins to databases, he/she can also create members of
  these database roles that manage the security in the database.

Having above definition, in your case you need to be considerate towards granting role - role can be granted on any objects(tables, views, functions, stored procedure etc) to a login(login is not a user as stated above).

For example - we can grant read permission on table employee in a database called Usersdb to a login called John. This can be done using below commands on database:

USE Usersdb;
go
CREATE USER John FOR LOGIN John
go
grant select on employee to John
go

Above example is based on two consideration that login John is not already a user in database Usersdb and employee table is created under default schema dbo.

Second issue with your command is grant all. As it has already been highlighted in comments that this is deprecated by Microsoft and hence shouldn't be used.

  This option (ALL) is deprecated and maintained only for backward
  compatibility. It does not grant all possible permissions. Granting
  ALL is equivalent to granting the following permissions:

  If the securable is a table, ALL means DELETE, INSERT, REFERENCES,
  SELECT, and UPDATE

So, you need to use below commands collectively instead of grant all

GRANT DELETE ON tablename to login;
GRANT INSERT ON tablename to login;
GRANT UPDATE ON tablename to login;
GRANT SELECT ON tablename to login;
GRANT REFERENCES ON tablename to login;

Many points have been already highlighted in comments by Mr. Randi Vertongen, Ronaldo, McNets.

Hope above helps.


Select rows until a condition is met Given:

CREATE TABLE T
(  x int not null primary key
,  y int not null );

INSERT T(x,y)
VALUES (1, 0)
  ,(2, 0)
  ,(3, 0)
  ,(4, 0)
  ,(5, 3)
  ,(6, 0)
  ,(7, 4)
  ,(8, 0)
  ,(9, 0);

The requirement is to return rows (ordered by x) where y = 0, until a row is encountered where y is not equal to zero.

The expected result for the data above is to return the rows where x is between 1 and 4. The other rows where y = 0 should not be returned, because they come after the row where x = 5 and y = 3.

First a note: when relational databases filter rows, they check each row separately if it fulfils given conditions. Other rows from the same table are not taken into account at that moment but again checked separately for the same (row-independent) conditions. So there is no "simple" command for what you want.

But there are multiple ways to accomplish it:

Use a variable which is initialized to true and then for each row it is used in the filter and set to @var = @var AND (y = 0) - that way first encountered non-zero value will set the variable to false and no other row can fulfil the condition. There is a problem with this - setting a value of user variable in the same statement as reading it is sort of undefined behavior - it often works but it may not work sometimes..
select the lowest x for which the y is non-zero select min(x) from T where y > 0; or select x from T where y > 0 order by x asc limit 1; - keep the value in app or in variable and then just select all the rows where x is less than the value you found select x, y from T where x < @stored_x order by x asc;
instead of app/sql variable use a subquery for the previous - select x, y from T where x < (select min(x) from T where y > 0) order by x asc; - this uses a sependent subquery which may be evaluated once per each row if the server version does not implement subquery caching or some kind of materialization so it might be slow
as previous but use join and a derived table (which is evaluated only once and materialized into a temp table):

-

select T.x, T.y
from T
join (
    select min(x) as limit
    from T
    where y > 0
) T2
where T.x < T2.limit
order by T.x asc;

The important thing is - you cannot think of it as "Select rows until some condition is met." but as "Which rows do meet this condition? The ones where X is smaller than that of a(ny) row with nonzero Y." and write that using standard SQL.


PostgreSQL CAST() giving unexpected results I can't seem to figure out how this is rounding overflow digits. It is most definitely not any of:

Round up if last digit is 5 or higher.
Round to even if last digit is 5.
Randomly round up or down if last digit is 5.

In fact, whatever logic it is using is definitely 1) deterministic, and 2) not [completely] based on the last digit being 5.

Some example inputs and output of:

CAST ('input' AS DOUBLE PRECISION);

Here, 'input' is not a variable, just a placeholder in this post for a literal numeric value with quotes around it, e.g. below in input column.

input                 output
34.55555555555595     34.5555555555559    *Obviously not rounding up
34.555555555555951    34.5555555555559    *Why not round up here?
34.5555555555559514   34.5555555555559    *Another unexpected round down
34.5555555555559515   34.555555555556     *Unexpected/seemingly inconsistent
34.55555555555595145  34.5555555555559
34.55555555555595146  34.5555555555559
34.55555555555595148  34.5555555555559
34.55555555555595149  34.555555555556     *Huh!?

Version: PostgreSQL 9.3.13 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

Based on your last comment,

JavaScript parseFloat("34.55555555555595149").toPrecision(15)

the cast should be to the numeric datatype

CAST ('input' AS numeric(17,15))

https://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-NUMERIC-DECIMAL


Disk space recovery on Mongodb replicaset secondaries Is it possible to recover disk space on replica set secondaries based on the results of a repairDatabase call performed on a primary? I can't seem to locate anything that would work short of bringing the secondaries down and restarting them minus their membership in the replica set. In my scenario I don't have the option to bring them down nor will repairDatabase work on a secondary. I looked at compact, but it does not free disk space.

Informational link: http://www.mongodb.org/display/DOCS/Excessive+Disk+Space

Compact will not recover space, because it really just defragments the data inside the existing data files, making the data more dense, more compact.  In fact, a compact occasionally needs extra space to do its work and so can end up using up more space rather than reclaiming it.

Because a repair rewrites the data files from scratch, it reclaims the "wasted" space and recovers disk space.

Besides bringing the secondaries down and running a repair there as you mention, another option is to delete the data files from a secondary and re-sync them from the primary from scratch.  This has a similar effect to a repair, because in both cases the data files are re-written, the difference being that you don't need ~2x the disk space because you wipe out the originals and pull the data from the primary.

Once you have done the first secondary, generally the quickest way to do the second would be to snapshot (with journaling enabled) or fsync and lock the secondary and just to a file based copy to seed the other secondary.

Your final option assuming you have run the repair already on the primary, which is really just a variation, would be to snapshot the primary (or fsync and lock) and use its repaired files as a seed to populate a secondary.

These methods are covered in more detail as part of the Backup docs:

http://www.mongodb.org/display/DOCS/Backups#Backups-Methods


The backup of the file or filegroup "fileStream" is not permitted, because it is not online I'm trying to backup a database, which has been created by someone I don't have access to. I get this error message:

  The backup of the file or filegroup "fileStream" is not permitted, because it is not online.

and then it suggests to backup database via T-SQL to only backup online filegroups. However, this is not my requirement.

I want to bring the offline filegroup online again. I saw this answer which suggests using alter database command to take a fielgroup offline.

alter database dn_name
modify file (name='filegroup_name', offline)
go

However, as I saw the documentation of the mentioned command, there is no option to bring a fielgroup online back. What should I do? How can I bring a filegroup (or a file) back online in SQL Server 2012?

If the file is still present, you should be able to do this:

RESTORE DATABASE YourDB FILEGROUP=YourFG WITH RECOVERY;

If the file is no longer present (or present but not eligible to recover the data), you're in for a rough weekend. Depending on the complexity of the database, I would probably start making a backup plan (no pun intended):

create a new database
use the "Generate scripts" option against the old database to script the objects and data (or use a 3rd party tool and do a schema/data compare)
make sure all database settings, logins etc. are mapped the same to the new database (this can get complex if you're using TDE, certificates, service broker, etc.)
rename the old database
rename the new database

(You could also skip 4. and 5. if you can adjust your applications to point to the new copy of the database.)


mysqldump: Got error: 1066: Not unique table/alias What usually causes this mysqldump error?

exec error: Error: Command failed: mysqldump: Got error: 1066: Not unique table/alias: 'TableFoo' when doing LOCK TABLES

A common case for this error is when the same table appears more than one in the mysqldump list of tables:

mysqldump [options] TableFoo  ... TableFoo ...


interchange MySQL/MariaDB .frm files Is there any effect if i interchange MySQL/MariaDB .frm files from dbs with similar structure on any CRUD operation?

note: the different is some tables have different fields, whether its existence, datatype, default value or any others.

The .frm drives the parsing of rows.  So if the datatype or number of columns changes, it seems very likely that the rows cannot be read.

Consider using a VIEW to "change" the structure of a table.


How to Audit 2 or more SQL Logins (server principals) using SQL Server Audit I need to audit the few SQL Server Logins, I have used filters while creating Server Audit by using

CREATE SERVER AUDIT [audit123]
TO FILE
(   FILEPATH = N'D:\'
    ,MAXSIZE = 2048 MB
    ,MAX_ROLLOVER_FILES = 6
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 5000
    ,ON_FAILURE = CONTINUE
)
WHERE ([server_principal_name]='AAA')

But when I am including more SQL logins using where ([server_principal_name] in ('AAA','BBB','CCC')) it's giving error. I have tried using below but failed to create audit for 2 or more logins.

ALTER SERVER AUDIT audit123 WHERE server_principal_name ='BBB';
ALTER SERVER AUDIT audit123 WHERE server_principal_name ='CCC';

I believe you are using a part of SQL server audit specification in very first step:

Firstly you just need to create a server audit object using SQL Server Audit. Now in this audit section you are using where clause, which also requires a creation of sql server audit specification:
Check the permissions for altering an audit:

a)To create, alter, or drop a server audit, principals require the ALTER ANY SERVER AUDIT or the CONTROL SERVER permission.

b)Users with the ALTER ANY SERVER AUDIT permission can create server audit specifications and bind them to any audit.

Refer to below eg : from MSDN whihc might help you in understanding the audit using principals in where clause:

  CREATE DATABASE TestDB;
  GO
  USE TestDB;
  GO
  CREATE SCHEMA DataSchema;
  GO
  CREATE TABLE DataSchema.GeneralData (ID int PRIMARY KEY, DataField varchar(50) NOT NULL);
  GO
  CREATE TABLE DataSchema.SensitiveData (ID int PRIMARY KEY, DataField varchar(50) NOT NULL);
   GO
    -- Create the server audit in the master database
 USE master;
 GO
  CREATE SERVER AUDIT AuditDataAccess
  TO FILE ( FILEPATH ='C:\SQLAudit\' )
 WHERE object_name = 'SensitiveData' ;
 GO
 ALTER SERVER AUDIT AuditDataAccess WITH (STATE = ON);
  GO
 -- Create the database audit specification in the TestDB database
  USE TestDB;
  GO
CREATE DATABASE AUDIT SPECIFICATION [FilterForSensitiveData]
FOR SERVER AUDIT [AuditDataAccess]
ADD (SELECT ON SCHEMA::[DataSchema] BY [public])
WITH (STATE = ON);
GO
-- Trigger the audit event by selecting from tables
 SELECT ID, DataField FROM DataSchema.GeneralData;
 SELECT ID, DataField FROM DataSchema.SensitiveData;
 GO
 --- Check the audit for the filtered content
  SELECT * FROM fn_get_audit_file('C:\SQLAudit\AuditDataAccess_*.sqlaudit',default,default);
   GO


ST_Contains Aggregation Problem I am using the postgis function ST_Contains to find all of the census blocks in different counties, the counties have been split by congressional district boundaries. I am trying to find the population of each county part that has been split. I used this sql statement...

  SELECT cp.state, cp.countyfp, cp.county, cp.cd, sum(bd.pop00)
  FROM countyparts2008_split_v2 AS cp
  JOIN us_block_2000_with_data AS bd
    ON ST_Contains(cp.geom, bd.geom)
    AND cp.state = '01'
  GROUP BY cp.state, cp.countyfp, cp.county, cp.cd

and i get this result

"01";"115";"St. Clair";"04";60306
"01";"125";"Tuscaloosa";"06";161992
"01";"125";"Tuscaloosa";"07";161992
"01";"115";"St. Clair";"03";60306
"01";"115";"St. Clair";"06";60306
"01";"073";"Jefferson";"04";647119
"01";"025";"Clarke";"01";27178
"01";"025";"Clarke";"07";27178
"01";"037";"Coosa";"02";10411
"01";"101";"Montgomery";"03";221731
"01";"103";"Morgan";"04";108021
"01";"107";"Pickens";"04";20143
"01";"103";"Morgan";"05";108021
"01";"125";"Tuscaloosa";"04";161992
"01";"037";"Coosa";"06";10411
"01";"107";"Pickens";"01";20143
"01";"107";"Pickens";"06";20143
"01";"107";"Pickens";"07";20143
"01";"107";"Pickens";"03";20143
"01";"037";"Coosa";"03";10411
"01";"073";"Jefferson";"06";647119
"01";"073";"Jefferson";"07";647119
"01";"101";"Montgomery";"02";221731

The numbers for each county part should be different, so for example each St. Clair entry should have a different population total (the last column). I figure I am aggregating it wrong or something, but not sure exactly.

I also tried this sql query

SELECT cp.state,
       cp.countyfp,
       cp.cd,
       (SELECT SUM(pop00)
          FROM us_block_2000_with_data AS bd
         WHERE ST_Contains(cp.geom, bd.geom)
         GROUP BY cp.cd, countyfp, state)
  FROM countyparts2008_split_v2 AS cp
 WHERE cp.state = '01'

It's difficult without the data but let's recreate a mental experiment.

CREATE TEMPORARY TABLE foo AS
SELECT * FROM ( VALUES
 ( 1, 2, true ),
 ( 1, 3, true ),
 ( 1, 4, false)
) AS t(cp, bd, contains);

Now... What happens if I join this table to itself on condition that returns true more than once.

SELECT *
FROM foo AS f1
JOIN foo AS f2
  USING (contains);
 contains | cp | bd | cp | bd
----------+----+----+----+----
 t        |  1 |  2 |  1 |  3
 t        |  1 |  2 |  1 |  2
 t        |  1 |  3 |  1 |  3
 t        |  1 |  3 |  1 |  2
 f        |  1 |  4 |  1 |  4
(5 rows)

Now this may make perfect sense in this form, but if you conceal a table from the result..

SELECT f1.*
FROM foo AS f1
JOIN foo AS f2
  USING (contains);
 cp | bd | contains
----+----+----------
  1 |  2 | t
  1 |  2 | t
  1 |  3 | t
  1 |  3 | t
  1 |  4 | f

Now you're going to be confused. So the problem here is that you have multiple cases where one cp.geom contains numerous bd.geoms

Now how does that work if you add a GROUP BY..

SELECT f1.*, sum (f2.bd)
FROM foo AS f1
JOIN foo AS f2
  USING (contains)
GROUP BY f1.cp, f1.bd, f1.contains
 cp | bd | contains | sum
----+----+----------+-----
  1 |  3 | t        |   5
  1 |  2 | t        |   5
  1 |  4 | f        |   4

And, that's precisely your problem. Here you're getting a sum() of 5. Which is understood given the above tables that produced that.

So if you want to debug this check the condition ST_Contains(cp.geom, bd.geom) output cp.geom and bd.geom using ST_AsText()


Mysql Replication backup and restoration failed multipal time I tried restoring MySQL backup several time but unfortunately it gets failed by some or the new reason.

I have 2 host Master & Slave with below details.

Server1_Master
os-centos6.7
mysql- 5.6.28-2.el6

Server2_Slave
os- centos6.6
mysql-  5.5.41

Engine: innodb  (on both host)

Below are the respected step used by me.

Master Backup

  mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A  > ~/dump.sql

Restoring mysql on slave

  mysql -u root -p < /location/dump.sql

  CHANGE MASTER TO MASTER_HOST='<>',MASTER_USER='replicant',MASTER_PASSWORD='<>', MASTER_LOG_FILE='<>', MASTER_LOG_POS=<>;

START SLAVE;

Now the new error which we am getting after server hrs of restoration is below

  "ERROR 1449 (HY000) at line 150536: The user specified as a definer ('lipl_ga_app'@'%') does not exist "

Please let me know the best way to start new replication.

The error itself is happening because there is a create view (or procedure) statement and the slave doesn't have the user who created it. In mysqldump you will have this format:

CREATE ALGORITHM=UNDEFINED DEFINER=`lipl_ga_app`@`%` SQL SECURITY DEFINER...

That's not specific to replication. It happens even if you would want to restore it to an empty db.

On the replication side

1) One way to do it is mysqldump but you need to LOCK the tables or make sure you don't get any writes during the process of dump (read_only=1 for example). Otherwise you get your slave out of sync from the start. Also there is certainly an impact on the application which is connected to the master. More on this you can read on the mysql doc: http://dev.mysql.com/doc/refman/5.7/en/replication-howto.html

To same or higher major version (these cannot be done if master is 5.6 and slave is 5.5):

2) You can also retrieve the data without impacting the master with hot backup solutions like percona Xtrabackup (https://www.percona.com/doc/percona-xtrabackup/2.3/index.html). This is probably going to be faster as well. Once you applied the logs it behaves as normal mysql binaries and you can do the change master command. You will have an xtrabackup_binlog_info file which contains the master position you need to point the slave to.

3) You can stop the master after acquired the master position, copy the binary to the slave, start mysql and change master to ... with the position you saved. This could be also done by snapshotting the filesystem (LVM, ZFS, BTRFS, etc.) if you want to quickly restart mysql. Usually this is the fastest way.

If your version mismatch you might need to run mysql_upgrade since with the latter two you're copying binary data not logical.

A sidenote:

Why are you replicating from 5.6 to 5.5? Replicating from higher version to lower is not guaranteed to work.


Should I refresh query plan cache Please let me explain my problem and situation:

I have a web application - MVC3, MSSQL Server 2005, LinqToSQL. It has been running great until one fine morning I pushed a lot of rows to a table that is heavily used and since then I was getting query timeouts. In order to fix the problem I run the Database Tuning Advisor and I added some Indexes and Statistics. I also created a maintenance plan to rebuild indexes daily. After those additions, the application has been behaving unstable; it would work fast for couple of hours then it would start timing out again. Next, life forced me to clean up the table in matter, and the amount of rows in it is even smaller now than it was before but the timeouts are still happening. So, I removed all indexes that I created and now the website is much more stable but from time to time I still see some timeouts.

I've been trying to figure out how to fix those queries and when I profile it and paste the query directly into the SQL Management Studio it returns the results in 1 second, but when I run this query from my application, it's about 25 seconds. Then after it runs for the first time, next time it goes as fast as on the server!

I started doing some research and it looks like when I played with all those indexes my query plans got messed up and now they are creating issues.

My questions are :

Should I refresh my query plan cache (around 22000 queries - a lot of them has been used only once) and
If I do it, what would the impact be on the SQL while they are all rebuilding?

Lets take your problem step by step:

  It has been running great until one fine morning I pushed a lot of rows to a table that is heavily used and since then I was getting query timeouts.

Whenever you do large updates/inserts to you tables, highly recommend to update stats and reorg/rebuild indexes. That way query optimizer does not select or produce bad plans on wrong estimates.

  In order to fix the problem I run the Database Tuning Advisor and I added some Indexes and Statistics.

Never do that without understanding your workload and proper testing the recommendations. Refer to Don’t just blindly create those “missing” indexes! by Aaron Bertrand.

  I also created a maintenance plan to rebuild indexes daily.

I would recommend you to look at the fragmentation ratio of the indexes and accordingly reorganize them or rebuild them. Best is to use SQL Server Index and Statistics Maintenance as it is best free software out and implemented widely.

  Next, life forced me to clean up the table in matter, and the amount of rows in it is even smaller now than it was before but the timeouts are still happening. So, I removed all indexes that I created and now the website is much more stable but from time to time I still see some timeouts.

Same as above. Now the optimizer has wrong statistics and hence inefficient query plan can be produced. Best is to UPDATE STATISTICS and mark that table for recompile using sp_recompile , so next time the optimizer will generate new plan based on updated stats available.

Also read up on : Slow in the Application, Fast in SSMS?


How can I create a filegroup on SQL Server attaching a bare filename? I'm trying to script a database creation from a Java program. I need to create 2 file groups, and link them to the database. In the example I can see in the official docs, I only see examples with paths, like in this case:

  B. Adding a filegroup with two files to a database

  The following example creates the filegroup Test1FG1 in the
  AdventureWorks2012 database and adds two 5-MB files to the filegroup.

USE master
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1;
GO

However, I have no knowledge of what any user's setup would look like (and it could potentially be Windows OR Linux, so an answer I found where the person re), but I would like to add these file group files to the DATA/ folder of the installation, or some other standard/default location. To achieve that, I tried to execute the following command:

ALTER DATABASE myapp_db ADD FILEGROUP data_dg

ALTER DATABASE myapp_db ADD FILE (
    NAME=data_fg,
    FILENAME='data_fg.ndf',
    SIZE=32MB,
    MAXSIZE=4096MB,
    FILEGROWTH=32MB
) TO FILEGROUP data_fg

However, I get the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: A file activation error occurred.
The physical file name 'data_fg.ndf' may be incorrect.
Diagnose and correct additional errors, and retry the operation.

Is this because SQL Server does not allow relative file paths/names for FILEGROUPs? (if so, why is it called FILENAME instead of FILEPATH?) What options exist for achieving this?

You can't construct a path inside the ALTER DATABASE command, so you're going to have to use dynamic SQL.

If you want to just put the new data file in the default instance path, you can do as Scott suggests - use the server property for default data path:

DECLARE @dbname sysname = N'myapp_db';

DECLARE @path nvarchar(511) =
  CONVERT(nvarchar(511), SERVERPROPERTY('InstanceDefaultDataPath');

DECLARE @sql nvarchar(max) = N'ALTER DATABASE $db$
  ADD FILEGROUP data_fg;

ALTER DATABASE $db$ ADD FILE (
    NAME=data_fg,
    FILENAME=''data_fg.ndf'',
    SIZE=32MB,
    MAXSIZE=4096MB,
    FILEGROWTH=32MB
) TO FILEGROUP data_fg;';

SET @sql = REPLACE(REPLACE(@sql, N'$path$', @p), N'$db$', @dbname);

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

If, however, you want to base this off an existing database (maybe the one you're altering, if they pass that by a parameter), then you could do this:

DECLARE @dbname sysname = N'model';

DECLARE @p nvarchar(511);

;WITH x AS (SELECT TOP (1) p = physical_name, type_desc
  FROM sys.master_files
  WHERE database_id = DB_ID(@dbname)
  AND type_desc = N'ROWS'
  ORDER BY file_id
)
SELECT @p = SUBSTRING(p, 1, LEN(p)-CHARINDEX('/',REVERSE(p))+1)
FROM x;

DECLARE @sql nvarchar(max) = N'ALTER DATABASE $db$
  ADD FILEGROUP data_fg;

ALTER DATABASE $db$ ADD FILE (
    NAME=data_fg,
    FILENAME=''data_fg.ndf'',
    SIZE=32MB,
    MAXSIZE=4096MB,
    FILEGROWTH=32MB
) TO FILEGROUP data_fg;';

SET @sql = REPLACE(REPLACE(@sql, N'$path$', @p), N'$db$', @dbname);

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

You may even want to allow your users to override with a specific path (think of the case where the current drive is getting crowded and they want to start this new file in a totally new location).

It also strikes me as weird that you'd bother creating a data file that is only 32 MB. That's barely big enough to hold the metadata about the file, and will surely start a cycle of autogrowths as soon as it becomes active.


Best option to index an application log table for quick insertion and retrieval in date order? Suppose I have an application trace log table with three columns: (id uniqueidentifier, message nvarchar(max), and started datetime2).

The primary key (clustered) is on [id]. I need to be able to insert records as quickly as possible, but I'm not sure if that's the best primary key. It makes sense because I'm using EntityFramework and that's the default, but the uniquidentifier type has pretty random values for each insertion, so it would insert rows all over the place when clusted on id. I think that's good, because it lowers page contention (vs inserting them all on the last data page all the time), but I'm not sure about that.  At the same time, I need a way of retrieving the rows quickly in order by [started].

My question is, would it be better to:
1. Change the primary key to be composite on (started, id) so it's the only index and the data pages are in order by date, or should I
2. Leave the primary key on [id] and add a unique non-clustered index on (started, id).

My reasoning for including both (started,id) in the 2nd option is so it can be a unique index that would include the clustering key (which is always implicitly included anyway). I don't want to include any other columns in the index because it would duplicate a lot of [message] data unnecessary, when all I'm really interested in is an index that speeds retrieval of rows in order.  Or, is there a better option than the one's I mentioned?

I think that's good, because it lowers page contention (vs inserting them all on the last data page all the time), but I'm not sure about that.

At insert rates of under 10,000/sec hot page latch contention is not a big issue, and the efficiency and locality of end-of-index inserts is preferable.

Change the primary key to be composite on (started, id) so it's the only index and the data pages are in order by date

Yes.  Prefer the solution where you don't need two indexes.  And look here for a solution to using a datetime2 as the only unique index on the table by filling the sub-second precision with values generated by a sequence.


use IFNULL in laravel I have a query in laravel which is working fine.

$subjects = $app->db->table('subjects')->LeftJoin('downloads', 'subjects.subjectID', '=', 'downloads.subject_id')
        ->where('universityID', $currentUser->universityID)->where('semesterID', $currentUser->semesterID)->where('courseID', $currentUser->courseID)
        ->select('subjects.subjectID', 'subjects.subjectName', 'subjects.price',  'downloads.is_download' )
        ->orderBy('subjectID')
        ->get();

There is only one issue that is_download comes null when there is no relative entry in table. After some research I found that there is a function IFNULL by using which I can change is_download null to 0. So here is my query which is not working. Blank screen is showing.

$subjects = $app->db->table('subjects')->LeftJoin('downloads', 'subjects.subjectID', '=', 'downloads.subject_id')
        ->where('universityID', $currentUser->universityID)->where('semesterID', $currentUser->semesterID)->where('courseID', $currentUser->courseID)
        ->select('subjects.subjectID', 'subjects.subjectName', 'subjects.price',  IFNULL( `downloads`.`is_download` , 0 ) )
        ->orderBy('subjectID')
        ->get();

I am able to write this query in my phpmyadmin but not know how to write in laravel

This is the api, So whole code looks like

use Illuminate\Database\Query\Expression as raw;
use project\Models\Subject;
use project\Models\Semester;
use project\Models\StudentRegistration;

$app->get('/api/getSubject/:studentID', function($studentID) use ($app) {
error_reporting(E_ALL);
    $currentUser = StudentRegistration::where('studentID', $studentID)->first();

$subjects = $app->db->table('subjects')->LeftJoin('downloads', 'subjects.subjectID', '=', 'downloads.subject_id')
        ->where('universityID', $currentUser->universityID)->where('semesterID', $currentUser->semesterID)->where('courseID', $currentUser->courseID)
        ->select('subjects.subjectID', 'subjects.subjectName', 'subjects.price',  IFNULL( `downloads`.`is_download` , 0 ) )
        ->orderBy('subjectID')
        ->get();
print_r($subjects);
    return $app->response->write(json_encode([
                'error' => 0,
                'subjects' => $subjects
    ]));
});

replace

->select('subjects.subjectID', 'subjects.subjectName', 'subjects.price',
IFNULL( `downloads`.`is_download` , 0 ) )

with

->select('subjects.subjectID', 'subjects.subjectName', 'subjects.price',
DB:Raw(' IFNULL( `downloads`.`is_download` , 0 )') )

and add an alias

->select('subjects.subjectID', 'subjects.subjectName', 'subjects.price',
DB:Raw(' IFNULL( `downloads`.`is_download` , 0 ) as is_download') )


Query Execution Time I am querying a SQL server 2008 database table having more than 500 million records.
The query I fired to move around 10 million records to temp table took 9 hours.
Similar query I fired which has been going for more than 10 hours.

How to know:

time it will take to complete the running query
time it will take to complete query before execution

Certain statements report the percent complete in sys.dm_exec_requests percent_complete column:

  Percentage of work completed for the
  following commands:

  ALTER INDEX REORGANIZE
  AUTO_SHRINK option with ALTER DATABASE
  BACKUP DATABASE
  DBCC CHECKDB
  DBCC CHECKFILEGROUP
  DBCC CHECKTABLE
  DBCC INDEXDEFRAG
  DBCC SHRINKDATABASE
  DBCC SHRINKFILE
  RECOVERY
  RESTORE DATABASE,
  ROLLBACK
  TDE ENCRYPTION

For other statement, you'll have to wait it out and see.

First of all you'd have to check whether the 'query' is making any progress or is just sitting idle, blocked b something else. The same DMV mentioned above will immediately reveal this, the wait_time, wait_type and wait_resource are a dead give away: if they change, its making progress, if they stay fixed (and wait_time is growing) the query is stuck and the blocking _session_id is the blocker.

If the query was indeed making progress, and is one single query that is moving the rows, I gotta say: one does not go around and blindly moves 10M rows. If you did this in one single transaction, then your transaction log has been growing over the past 10 hours continuously. Every log growth event is a complete freeze of the database as it waits for the log to expand to zero out the new allocated disk space. Then the activity will resume, only to hit a new growth event in a short bit. By now, your database log must had grown several hundred times likely. This happens under all recovery models, including SIMPLE.

You could abort the query, but you'd have to wait until the 10 hours of the transaction will roll back. A 10h transaction can take another 10h to roll back, or more, or less, it depends on many many factors.

You could shutdown the server in panic and restart it, only to face the recovery of the database that is trying to undo the 10h transaction. Recovery will last, guaranteed, longer than rolling back the transaction.

So this is why one does never move 10M rows in a single 'query'. Batching the move in smaller sets is a must. Of course, this requires a carefully written program to do it.

Some users are lucky, they learn by stumbling on posts like this and making a note to self 'don't move 10M rows in one single operation'. Other learn the hard way... but they (hopefully) know better next time.


Generate XML using Oracle XML schema I have some XML schema definitions that have been registered using DBMS_XMLSCHEMA.  I have created tables using the XMLTypes generated.  I can create XML using XMLQuery and if I understand correctly, the query results can be inserted into the table if the definition is correct.

Ultimately the XML needs to be written to a file, but the part I am unsure about is if writing XMLQueries from scratch is the best way to generate the XML.  Is there any way to have the XML schema generate an XML stub for a guide or generate template XMLQuery?  The data is currently all in regular Oracle tables, but arranged quite differently, so any information on easing the export to XML based on a specific set of registered schema would be useful.

It is fairly direct to push an already existing select to XML: http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#i1028612

Creating XML is part of the basic SQL specifications and is available on most servers. You can check out SQL server's version of it here: http://blog.sqlauthority.com/2009/02/12/sql-server-simple-example-of-creating-xml-file-using-t-sql/


Does restoring an Oracle backup of the whole database restore schema objects? I used RMAN with,

RMAN>backup database archivelogs all;

After that I truncated a table.

I then tried to restore with,

RMAN>restore database;
RMAN>recover database;
RMAN>alter database open;

I connected with sqldeveloper, and the table is still empty.  I have tried many times.  I made sure there wer archive files with crosscheck backup.  It simply never restores the data to that table in the schema.  I know you cannot restore a single table, so I restored the whole database thinking it would put my data back.

There is one database, one backup, truncate, one restore (did this a lot).  I have tried set until for an incomplete backup, many things.  I don't need a point in time, however.  I was trying to get anything to work.

I received no errors.  Everything looked good.

(edit)

All disk backup.
Oracle Enterprise Linux 6.5.
Oracle 11gR2, 11.2.0.3

(edit)

I have not used logminer.

What am I doing wrong?

You're issuing a "recover database" command, which will recover the database as far as possible.  If that includes the archivelogs/redo logs which contain your "truncate" command, then this will also be re-played and the table truncated again.

Try doing the restore on it's own, or recovering to a specific time using RECOVER UNTIL... then opening the DB read-only to see if it's in the state you expect.


Counting occurrence a record based on consecutive days I have a query to display record of stationary issued according to date.

SELECT date,stationary_id FROM stationary_issued

How do i check if a stationary was issued in consecutive days.I have tried group by and sum row.

Current table

date       stationary_id
2019-04-01 2
2019-04-01 3
2019-04-01 4
2019-04-01 5
2019-04-02 3
2019-04-02 2
2019-04-03 3

Expected

stationary_id consecutive_occurrence
3             3
2             2

I have tried grouping date and stationary while counting stationary_id but the result is not correct

I'd argue your results are actually...

stationary_id consecutive_occurrence
3             2
2             1

You have 3 days present. Only two of those days are consecutive (04/02 and 04/03), meaning there was a day preceding them.

I'm making a few assumptions...

Each record in the table is an occurrence; stationary was delivered that day
A record is marked as a consecutive delivery, if (and only if) there was a delivery the prior day.
Since we don't have data for 03/31, we can't say whether deliveries on 04/01 were consecutive.

Generating sample data matching your post...

create table #thinger ([date] date, stationary_id tinyint);
go
insert into #thinger ([date], stationary_id) values ('2019-04-01', 2);
insert into #thinger ([date], stationary_id) values ('2019-04-01', 3);
insert into #thinger ([date], stationary_id) values ('2019-04-01', 4);
insert into #thinger ([date], stationary_id) values ('2019-04-01', 5);
insert into #thinger ([date], stationary_id) values ('2019-04-02', 3);
insert into #thinger ([date], stationary_id) values ('2019-04-02', 2);
insert into #thinger ([date], stationary_id) values ('2019-04-03', 3);
go

Here is a quick and dirty query to retrieve the summation you asked for...

;WITH consecutive_stationary_orders AS (
    SELECT cday.[stationary_id]
         , (
            select 1
              from #thinger pday
             where pday.[date] = DATEADD(day, -1, cday.[date])
               AND pday.stationary_id = cday.stationary_id
           ) AS was_consecutive
      from #thinger cday
)
SELECT [stationary_id], SUM(was_consecutive)
  FROM consecutive_stationary_orders
 GROUP BY [stationary_id]
HAVING SUM(was_consecutive) IS NOT NULL;

Forgive me. I used SQL/T-SQL syntax as I don't have a MySQL instance at this very second to use. But, this should give you an idea of what I'm doing.

First result set returns a list of dates, stationary issued (by ID), and whether that day's delivery was consecutive. The second query (wrapper) aggregates the first query. Whatever RDBMS you use, that's the approach I'd take.


Initializing Transactional Replication From Backup There is an option when setting up a publication for replication to Allow Initialization From Backup. We have been creating replicated databases for several years now and have always initialized from a backkup but have never set this flag (we just noticed it for the first time a couple of days ago). The replication certainly has worked without issue all this time.

I've found a lot of hits explaining that this needs to be used but none that explain why.

Does anyone have an understanding of what this actually does? From my vantage, it doesn't seem to be necessary but I'm thinking I have to be missing something.

Putting the comments into an answer so the question can be marked answered:

@crummel4 says, "See the third section from the bottom of http://replicationanswers.com/NoSyncOn2005.asp titled How "initialize with backup" Works, and How to avoid Pitfalls"

@Kin says, "For step by step instructions, please refer to http://www.mssqltips.com/sqlservertip/2386/initialize-sql-server-replication-using-a-database-backup/ or http://blogs.msdn.com/b/repltalk/archive/2010/03/16/deep-dive-on-initialize-from-backup-for-transactional-replication.aspx


Can we do PITR of one specific PostgreSQL database I am new to PostgreSQL, so please ignore if my question sounds stupid.
Imagine we have three databases in PostgreSQL and someone mistakenly ran a TRUNCATE command an hour ago on one of the databases. Now we want to do a PITR for that database alone, since there is an issue with only one database.
Is it possible in PostgreSQL to do PITR for one specific database OR it can happen only at instance level?
I read the documentation for Continuous Archiving and Point-in-Time Recovery (PITR). It looks like it can only be done at instance level (I guess we say instance as "Database Cluster" in PostgreSQL world, I come from SQL Server world).

As with the plain file-system-backup technique, this method can only support restoration of an entire database cluster, not a subset.

If PITR is not possible at DB level, then how Prod DBAs are handling this situation?
Also one last thing, I have a test server with multiple DBs and if PITR is possible at DB level, then please share me the steps so that I can test it myself.
I am mainly interested in supported versions of Postgres. My test server is PostgreSQL 9.6.16.

Restore your backup on another server. Perform a point-in-time recovery. Export the data / tables you’re interested in and import them into your production database.
This is generally how it’s done. You don’t want to lose all the other data but cherry-pick the data you need from a restore on another server.


select from many to many by matching field within a column Having following tables:

Order table:

OrderID
1
2
3
4
5

CustomerOrderLink table:

CustomerID  OrderID
1           3
1           4
2           2
2           4
3           3
4           4

Customer table:

CustomerID    Forename  Surname
1             A         Guy
2             Ted       Something
3             Gaby      Guy
4             Katie     Kitty

the task is to select all orders made by more than one person, but only when all the persons making order have same surname. Let's say that when customers have matching surname, they are a family. So to rephrase it: We want to have all the orders all families made together (we consider a family ordering when there are two or more customers with same surname for single order).

So the expected result for dummy data above would be:

OrderID Customers
3       2

I already have a part for selecting orders that are made by more than one customer:

 select OrderID, COUNT(distinct CustomerID) as CustomersCount
 from CustomerOrderLink group by OrderID having count(distinct CustomerID) > 1

but I struggle to implement checking, wheter they have matching surname:

select OrderID, COUNT(distinct CustomerID) as CustomersCount left join Customer on Customer.CustomerID = CustomerOrderLink.CustomerID
     from CustomerOrderLink group by OrderID having count(distinct CustomerID) > 1 and ???

I'll be thankful for help.

Replace the ??? in your attempted query with

COUNT(DISTINCT Customer.Surname) = 1

So, the whole query, with a couple of improvements, would be

SELECT
  co.OrderID,
  CustomerCount = COUNT(distinct c.CustomerID),
  FamilyName    = MAX(c.Surname)
FROM
  dbo.CustomerOrderLink AS co
  INNER JOIN dbo.Customer AS c ON c.CustomerID = co.CustomerID
GROUP BY
  co.OrderID
HAVING
  COUNT(DISTINCT c.CustomerID) > 1
  AND COUNT(DISTINCT c.Surname) = 1
;

An inner join would be better here since orders without associated customers would not match the COUNT(DISTINCT c.CustomerID) > 1 condition anyway. Talking of that condition, if the CustomerOrderLink table can never have more than one CustomerID/OrderID pair (and it should not), then you can replace it with COUNT(c.CustomerID) > 1 or even COUNT(*) > 1.

Note that if Customer.Surname is a nullable column and some customers do have a null there, the above may not return what you expect, because COUNT ignores nulls (and so it will return 1 if a group contains a couple of customers with the same surname and the rest without a surname).


SQL server - Re. Filegroup / partitioning Assuming VLDB is split into multiple file groups, each having its own file and on a separate disk.

Now let's say there is a disk crash bringing down one of the file groups - then will the other parts of the database be accessible?

Is it good practice to assign multiple disks to 1 filegroup OR create multiple file groups with independent disks?

It will depend on the predicate of the query you write against the partitioned table. If you filter on the column that is used as the basis of the partition function and scheme and that filter restricts the data to come from filegroups in the partition that are still online, your query can return results.

This is what makes partial database restores possible; you can restore the PRIMARY filegroup followed by read-write filegroups to bring 'current' data for your VLDB online and then restore the read-only filegroups.

As for what's the better disk-to-filegroup strategy, it'll depend on whether you're using older rotational or newer SSD media, whether the VLDB is for an OLTP application that is IOPS-heavy or a OLAP data warehouse where throughput when scanning tables is paramount, and whether the storage is local to the host or attached via SAN. Ultimately, you'll want to try different configurations, collaborating with your hardware support team and vendor, using benchmarks like from TPC and your application's typical and peak workloads.

For a deeper dive into the methods and strategy, Microsoft's series of documents describing their Fast Track Data Warehouse architecture over the years are a good resource.


MySQL RDS - Unable to Kill Hung Queries I have a MySQL RDS instance that's got 100+ queries all stuck waiting for a table metadata lock.

The good news

I have permission to kill all of the sessions, and have 0 concern for data loss as this database is completely reloaded as needed.

The bad news

It's a MySQL instance running as a hosted RDS instance in AWS AND while I have access to the "root" MySQL account, I don't have access to the AWS console that this RDS instance is hosted in.

What I've tried

When this has happened previously, I look through the connections and run CALL mysql.rds_kill(_each_and_every_process_id_one_at_a_time). However, this time, when I try run one of these kill statements, it simply hangs. (I'm doing this through MySQL workbench)

What options do I have? Is there someway to "escalate" my mysql.rds_kill call so that it's handled right away? Do I try executing this outside of workbench somehow?

Despite Workbench freezing on me, I was able to connect to the MySQL instance via the command line and run CALL mysql.rds_kill(_each_and_every_process_id_one_at_a_time) successfully.