SQLCMD: "Login failed for user" on SQL Azure, but it works with SSMS I have an SQL Azure database, and I'm able to connect to it using SQL Server Management Studio. When connecting to it, it displays

myServer.database.windows.net (SQL Server 12.0.2000.8 - myUser)

However, when I'm using the SQLCMD utility on the same machine, against the same SQL Azure server, with the same credentials, it always answers

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login failed for user 'myUser'

The syntax I'm using is

sqlcmd -S tcp:myServer.database.windows.net -U myUser@myServer -P "myPassword"

I tried everything I could think of:

Added the port: myServer.database.windows.net,1433
Removed the tcp: protocol
Enclosed the user in double quotes, like the password
Tried the -I and -C command line options
Combinations of the above.

The error is always the same.

Is there some specific flag that has to be activated for SQLCMD to work with SQL Azure? Any other ideas?

The error was some encoding issue with Powershell. I suppose the password's special characters were not read correctly by Powershell.

In cmd.exe, it worked flawlessly.


How to connect to another db using stored procedures? I need to get some data from an external db and make some calculations with it in another db, is it possible to connect to an external db from a stored procedure? Thanks guys.

PS.

Im using Oracle and both databases are in the same server.

You want to use a Database Link (DBLink).

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_5005.htm

http://psoug.org/reference/db_link.html

-- create tnsnames entry for conn_link
conn_link =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = perrito2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orabase)
    )
  )

CREATE DATABASE LINK conn_user
USING 'conn_link';

SELECT table_name, tablespace_name FROM user_tables@conn_user;

@conn_user on the end of a procedure or table name will tell the pl/sql engine to query the db link specified for that info.


Backing up multiple databases to the same point in time Usually when we start the backup we do not allow changes to commit or database will be not be accessible. I mean database will be in single user mode but I want to start the backup and release the db for use. Also, once I start the backup I do not want the ongoing changes to be written to the backup file.
I want to know how I can achieve this in Microsoft SQL Server 2012.Please assist me.

Well, let me explain my problem first. Currently I am setting the database to single user mode until the backup completes. This mode will serve my purpose of avoiding the data changes when backup is in progress. But my application is tied with the multiple databases(each databases are interlinked with each other and there are var dbs which keeps creating on monthly basis). So backing up all these databases has become tedious process and more importantly I have to keep the users out of the system when backup is in progress.

So I am looking for backup mechanism which will meet below mentioned requirements.

Start the backup of all the dbs at a time and release the db for use.
As databases are inter linked to each other I want to have data consistency maintained in backup files. So because of this data consistency requirement I don't want on going changes to be committed to my backup file.

All I want is - backup of all the dbs at a given time.

I am assuming that your task is to create a set of backups where you can restore all of them to a single static (no open transactions) point (all in sync).  Similar to what a quiesce point will do for you in DB2.  Unfortunately there is nothing in SQL Server that does exactly that.  However, assuming your databases are in FULL recovery mode, you can do something similar.

You can create a single MARKED transaction across all of the databases at once.  Then you take a transaction log backup of each database.  Assuming you are keeping up with your log backups (which you should be anyway) log backups don't take very long.  And there is no need to stop anyone from being in the system (except maybe long enough for you to create your marked transaction).

At this point if you need to do a restore then you do a RESTORE STOPBEFOREMARK on each of your databases.  This will restore all of the databases to the same point (based on your transaction).  Note: There is also a STOPATMARK if you want to include that transaction.

If you want an example I've got one in the link above along with quite a bit of additional reading.

I realize this doesn't exactly answer your question but hopefully it solves your problem.


Count instances of value from one column in another column located in another table Let's say I have the following tables :

Table 1

task | date
100  | 2016-01-01
101  | 2016-01-01
103  | 2016-01-01

Table 2

task2 | date
113   | 2016-01-01
113   | 2016-01-01
103   | 2016-01-01

I would like to get to this result:

task_result | Count
100         | 0
101         | 0
103         | 1

Basically count data included in task1 table1 present in task2 table2.

Any idea?

You can do this:

SELECT Table1.Task, COUNT(Table2.Task)
FROM Table1
LEFT JOIN Table2
    ON Table1.Task = Table2.Task
GROUP BY Table1.Task ;

Table1 is your base set, so join to it. You want to retain all values, so you use a LEFT JOIN and you join on the matching task values. The group and count operations happen on different columns - the GROUP BY is based on your tasks from Table1, and you COUNT the of tasks from Table2.

Here's a demo (using CTEs as implemented by SQL Server, PostgreSQL, Oracle, etc.):

WITH Table1 AS
(
    SELECT 100 AS Task
    UNION ALL
    SELECT 101
    UNION ALL
    SELECT 103
)
,Table2 AS
(
    SELECT 113 AS Task
    UNION ALL
    SELECT 113
    UNION ALL
    SELECT 103
)

SELECT Table1.Task, COUNT(Table2.Task)
FROM Table1
LEFT JOIN Table2
    ON Table1.Task = Table2.Task
GROUP BY Table1.Task ;


Upgrade and migrate SQL Server 2014 AlwaysOn AGs to SQL Server 2016, using the existing WSFC name Here are the components:
WSFC Name: SQLPROD1
Existing Nodes in this "cluster": SQL2014OnSite and SQL2014OffSite
New Nodes to be added (?) to SQLPROD1: SQL2016OnSite and SQL2016OffSite
All 4 servers running Windows Server 2012 R2 Enterprise. All 4 instances of SQL Server are Enterprise Edition.

I successfully added the two new servers to the WSFC. I successfully configured the two new SQL2016 instances to use AlwaysOn.

For testing purposes, I created a new AOAG named TestMigrate with a single database, AdventureWorks2014 and configured it to be identical to the 3 production AOAGs (Asynchronous commit, manual failover and non-readable secondary).

I then proceeded to add the 2 new nodes as replicas to the AG (worked great). I read this article (https://msdn.microsoft.com/en-us/library/dn178483.aspx), in the section titled "Availability Group with One Remote Secondary Replica" that indicated it was necessary to change the Availability Mode to Synchronous Commit, then failover to the new primary node, and finally change the Availability Mode back to Asynchronous Commit. Up to this point, everything worked as expected.

The problem happened when I removed the original 2 nodes from the AG. Within a few seconds, both replica copies of the database switched to "Restoring", and the AG disappeared from both 2016 instances.  Is there some "background" metadata that links the AG to the instance where it was created?

Also, it was my assumption that this was the best way to upgrade/migrate these AGs, but since it completely dropped the AG once I removed the two original nodes/instances, I'm guessing it's not...or I have missed a rather crucial step. I've searched high and low online for a straight-forward approach to accomplish this task, but haven't found anything that addresses this specific requirement. Several "similar" tasks, but not quite the same...and not close enough to give me the "missing piece(s)".

Any suggestions or recommendations would be very much appreciated.

Ah-ha!! I figured out what I did wrong. In my first attempts, I was doing all the work from the existing primary (SQL2014OnSite), and when I removed it from the AG, I must have done it in such a way that it blew away the AG. This time, after failing over to the new primary node (SQL2016OnSite), I made the change on this instance by right-clicking the AG, choosing Properties, and in that window, highlighted SQL2014OnSite, and clicked the Remove button. It has now been running in the new configuration, which is SQL2016OnSite as primary and SQL2016OffSite as secondary, for about 15 minutes after removing SQL2014OnSite from the AG, and...so far, so good. I think that was my error: performing the "after-migration" work from the 2014 instance instead of the 2016 instance.
Still, if anyone has any insights or recommendations for a better way to do this, by all means, please suggest away.


postgres: how can I allow index creation but no table mutations or table drops by the same user? I am setting up a postgres server with what is going to be a (mostly) read-only workload.

I would like to allow myself and colleagues to create/drop indices as we see fit, without allowing ourselves to drop tables or mutate the tables.

I spent time reading the grant/revoke documentation as well as role documentation pages, but it seems the table owner roles get both powers.

One thing I found was that, supposedly, grant create on tablespace allows index creation, but it didn't work ( note, I did not create any other tablespaces).

to be specific, I tried:

mydb=# grant create on tablespace pg_default to bob;
GRANT

... then switched over to bob:

mydb=> create index on foo(a);
ERROR:  must be owner of relation foo

I also tried making bob an owner, but then bob gets the right to drop tables, and there is seemingly no way to revoke that privilege.  So I'm stuck with both or none.

Any ideas?

At the SQL level you can't, since all those tasks are governed by table ownership.

The CREATE on a tablespace is required but not sufficient to create an index on a table and store the index in that tablespace. If you don't have the CREATE right on the tablespace you want to put the index in then you cannot CREATE INDEX that index. However, having that right is not enough; otherwise anybody could create indexes on any table if they had the right to create anything in any tablespace, and we don't want that. Indexes have a performance cost, take heavy locks during creation, and perhaps most importantly an expression index can leak data about the table via a malicious function or operator. So you must also own the table the index is to be created on.

Support for a separate INDEX right on a table could be added to PostgreSQL, but has not been, and might not get accepted if submitted. For now, you're stuck with having to own the table.

You could write a C extension that installs a ProcessUtility_hook that checks what operations are being performed and the current user identity, then rejects or permits them as appropriate. You can find examples of ProcessUtility_hook use in contrib/sepgsql and externally in the bdr_commandfilter.c file in the BDR project source code. You have to compile the extension, install it into the file system, then add it to shared_preload_libraries to install it, so you need full filesystem level access to the server, and usually root access.

A more practical approach is to use a SECURITY DEFINER function as a wrapper. Write a PL/PgSQL function that runs as the table owner and accepts as arguments the table to index, the column(s) to index, etc. Have it create the CREATE INDEX expression using format(...) then pass it to EXECUTE. Do not allow the user to pass arbitrary SQL expressions as arguments, or you're basically giving them full access via SQL injection. Want multiple columns? You'll have to accept colname text[] as an argument and quote_ident each one. And so on. Search for "dynamic SQL plpgsql" to learn more about this approach.


Can I truncate log in simple recovery mode? I have a few SQL Server 2005 databases in simple recovery mode. The disk volume in which my transaction logs are stored had only 1GB free, when I started a stored procedure (massive delete in a single table) the log grows a lot and the disk is running out of space, and the stored procedure seems to work indefinetly. How can I manage this transaction log?

You have two choices:

Break the delete into chunks, as Jonathan explained and as I blogged about here:

Break large delete operations into chunks

Give the transaction log more space to grow to accommodate the log activity (and don't shrink it after every delete; all this does is make the next delete take longer). If you run a delete command that deletes 100K rows, the log has to be able to record all of that activity, regardless of recovery model.

For a much more thorough treatment of the subject, see:

Why Does the Transaction Log Keep Growing or Run Out of Space?


Server Principal SID showing as NULL and name as empty string In my SQL Server 2016 server,i set up a user logins Audit for Login successful and Login Failed action.
Here is the script.

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20190404-091348]
FOR SERVER AUDIT [Audit_SQL_Logins]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON)
GO

USE [master]
GO

CREATE SERVER AUDIT [Audit_SQL_Logins]
TO FILE
(   FILEPATH = N'D:\SQL_Logins_Audit\'
    ,MAXSIZE = 20 MB
    ,MAX_FILES = 50
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = '77795756-1928-4ce4-85f5-e3ad2e3e22e5'
)
WHERE ([server_principal_name]<>'name1' AND [server_principal_name]<>'name2' AND ([server_principal_name]<>'name3' AND [server_principal_name]<>'name4') AND ([server_principal_name]<>'name5' AND [server_principal_name]<>'name6'))
ALTER SERVER AUDIT [Audit_SQL_Logins] WITH (STATE = ON)
GO

In the Audit Logs,

I can see lots of failures with Server Principal SID as NULL and name as empty string.Earlier when i set up the login i didn't give any filters and that time the Audit logs showed only success and there were no failures.

I don't understand what is getting failed here since the log shows NULL .

Additional Info:

Here is the screenshot.
This is a production database server and users are logging in from the .Net Web application.
I see the message "Network error code occurred while establishing a connection;the connection has been closed."

I created the exact same audit and couldn't reproduce the problem you describe. When I log in with a login that doesn't actually exist:

And for an intentionally wrong password:

I even tried a name with a huge number of leading spaces and tabs, just to see if the name would get truncated and look blank, but leading whitespace gets trimmed.

Of course I would expect SID to be null in the first case, because what SID would you want to show for a login that doesn't exist (and therefore can't have a SID)? And for the second case, perhaps because a login couldn't authenticate, a match to SID wasn't made?

You'll have to provide more info to determine why server principal name isn't being populated. What is the application name? What does the statement say? What is the client (IP address in the statement)? Can you ask that person what they're doing?


Confusion about HA , LOG backups and availability So I started to work with HA and I'm on a new job, in a new company that has already HA implemented, but nobody can remember who created or if it's working.

1) Availability Groups are in sync with it's replicas by log files, right? So the primary replica sends information from the LOG files to the secondary.

2) With log shipping, I need a job to backup log from primary, copy it to another drive, send it to the netword, and then another job in the secondary replica to restore this log into the database. I dont need this naymore with Availability groups right? Primary replica already sends log data directly from the log file to the secondary replica. Or is this wrong?

3) I ran a query and I noticed that there aren't backups in this server ( primary) since 2017, and logs since 2018. Is this a problem for Availability groups? with no log backup files, there's no information to send to the replica. Or 2) is right already?

1) Availability Groups are in sync with it's replicas by log files, right? So the primary replica sends information from the LOG files to the secondary.

Looking at it form a very high level, yes.

  2) With log shipping, I need a job to backup log from primary, copy it to another drive, send it to the netword, and then another job in the secondary replica to restore this log into the database.

You do'nt have to necessarily copy it/move it, etc., if you take the backup to a place that is already shared with all other log shipping partners. The overall steps, though, are the gist.

  I dont need this naymore with Availability groups right? Primary replica already sends log data directly from the log file to the secondary replica. Or is this wrong?

Very high level, you're correct.

  3) I ran a query and I noticed that there aren't backups in this server ( primary) since 2017, and logs since 2018. Is this a problem for Availability groups?

It's a problem in general. Should you need to restore from a backup, there aren't any recent ones. Since the logs haven't been backed up, their log files will continue to grow until they max out or the disk is filled, whichever comes first.

  with no log backup files, there's no information to send to the replica. Or 2) is right already?

The log is transmitted regardless of backup activities.


Oracle Automatic Memory Management - SGA_MAX_SIZE When using Automatic Memory Management, what value should SGA_MAX_SIZE be?  Should it be 0?  These are my current settings, and I'm unsure whether to leave SGA_MAX_SIZE or set it to 0.

memory_max_target     2147483648
memory_target         2147483648
pga_aggregate_target  0
sga_max_size          1610612736
sga_target            0

Oracle 11gR2

The docs for sga_max_size say:

  On 64-bit platforms and non-Windows 32-bit platforms, when either MEMORY_TARGET or MEMORY_MAX_TARGET is specified, the default value of SGA_MAX_SIZE is set to the larger of the two parameters.

And according to Oracle document Automatic Memory Management (AMM) on 11g (Doc ID 443746.1), if you have both sga_target and pga_aggregate_target unset, then:

  If neither is set, they will be auto-tuned without any minimum or default values. We will have a policy of distributing the total memory set by MEMORY_TARGET parameter in a fixed ratio to the the SGA and PGA during initialization. The policy is to give 60% to the SGA and 40% to the PGA at startup.

So the simplest thing to do if you want Oracle to completely control the SGA is to reset it and let the default apply:

SQL> alter system reset sga_max_size scope=spfile;

(And restart.)

If you want AMM but would like minimum values for the SGA or the PGA (or both), do so by setting the corresponding _target parameter:

  The preceding steps [to set up AMM] instruct you to set SGA_TARGET and PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and instance PGA are tuned up and down as required, without restrictions. You can omit the statements that set these parameter values to zero and leave either or both of the values as positive numbers. In this case, the values act as minimum values for the sizes of the SGA or instance PGA.

Whatever you do, don't set the PGA or SGA target or max size to greater than the memory target. This prevents instance startup.


Clarification on MySQL Workbench Options File Configuring When in the MySQL Workbench tool, configuring the "Options File," I see a drop-down in the lower right corner with the options:

client
mysql
mysqld

By default, 'mysqld' is selected and I see my configuration file's (which is loaded and in use) absolute path, opposite (left) of the aforementioned drop-down.  When I alternate between the different drop-down options, the fields change in the various tabs of options that are modifiable.  I expect this behavior.  However, what I don't understand is what the 3 drop-down options are for, overall.  I see the 'Apply' and 'Discard' button, so I'm under the assumption they're there for a reason.  I don't know what the differentiation is intended for or how it would impact my server if I were to select versus the other and click Apply.

Can someone explain to me what these are for and the impact they have?  I've tried researching this issue, already, but have had no luck with any explanation to this.

Thank you!

Windows 7 Pro (Server)
MySQL 5.6.20 (64-bit)
MySQL Workbench 6.2.3

The options file in MySQL is a human-readable text file, typically called my.cnf on Unix and my.ini on Windows.  This single file contains configuration entries for all things MySQL, including the server, and the client library, and the various other official client utilities.

The screen you're referring to is a glorified configuration file editor for that file.

Within the file there are "groups" with headers in brackets, that determine which portions of the file are read by the different components of MySQL -- the command line client hast its [mysql] section, the various client utilities (like mysqlcheck) will also read what's in [client], and the server itself is configured under [mysqld].  Some of the other utilities may also have their own optional sections, for utility-specific features and options that would not be valid for all client utilities, like [mysqldump].

What they're doing here is letting you edit different sections of a single file, without actually telling you what they're letting you do.

Take a look at your local options file, and this part of Workbench will perhaps be a little more intuitive.


Pipe psql timing to file I am currently using \timing on to do some simple performance testing in Postgres.  I would like to run many queries and pipe the timing results to a file.  However, all of the options I have tried (\o, \l, and their command-line equivalents) pipe only the query results to a file.  The Time: 1.234 ms message is not written to the file.

Is there any way I can pipe the timing output caused by \timing on into a file, or will I have to choose some other method for performing my tests?

You can do this by piping the query into psql, then grepping the output, like so:

postgres@winterspring:~$ cat foo.sql
\timing on
select * from foo;
postgres@winterspring:~$ psql < foo.sql | grep "^Time:"
Time: 0.505 ms
postgres@winterspring:~$

Redirect just the timing to a file if needed:

postgres@winterspring:~$ psql < foo.sql | grep "^Time:" >> timing.txt

The grep will have a slight performance impact.


SQL select top X rows having sum limit I am using SQL Server 2012,

I have a table with a format such as :

MyOrders :
 ID -> PK int
 Cost -> float
 Category -> int

I want to get the following :

Input :

category (lets say 2).
sum_limit (lets say 500).

I need to get :

Top X (need to find x) rows from MyOrders, filtered by category (lets say where category = 2)
having sum of cost smaller than the sum limit (lets say 500) but the largest that is smaller than the limit, ordered by ID.

In other words, go over the rows one by one (ordered by ID) who has category 2, and summarize the cost until i pass the cost limit, than return all those rows except the last one (that caused me to pass the limit).

Also i want to avoid using loops.

I thought of creating a query that selects a number (from 1 to the number of rows in MyOrders after filtering the category) and its sum of cost as the first stage,

I created a numbers table (https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table)
And than tried some queries but could not get it exactly.

Thanks for any help.

EDIT:

OK I used the following to get what i wanted :

select top 1 Number from
 NumbersTest
 where Number <= (select count(1) from MyOrders where category = 2) and
 (select sum(cost) from MyOrders where ID in (select top(Number) ID from MyOrders where category = 2 order by ID desc)) < cost_limit
 order by Number desc

Where NumbersTest is a table created as mentioned before (https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table)

Just Join the table to itself and make the appropriate sum.

This query will match each row in the table to all rows in the table with a smaller ID and the same category. You can then easily sum these rows and filter out all rows with a total greater than your limit.

Using sub queries in the where clause like you do might result in many unnecessary query executions if your sub-queries are not optimized to an INNER JOIN by the database engine:

DECLARE @Limit FLOAT
SELECT @Limit = 30

SELECT
    o1.Id
    , o1.Cost
    , o1.Category
FROM
    MyOrders o1
    -- Join all orders with same category and inferior ID
    INNER JOIN MyOrders o2 ON o2.ID <= o1.ID AND o1.Category = o2.Category
GROUP BY
    o1.Id
    , o1.Cost
    , o1.Category
HAVING
    SUM(o2.Cost) < @Limit
ORDER BY
    ID


Constrain Partial Values in Timestamp I'm working on a some Create Table statements for a project I have.  I have two columns (StartDate, EndDate) where the data type is Timestamp.  One of the requirements is to ensure that the date is fixed (January 1, 2000) but leave the time variable.  How would I go about including that in my Create Table statement?

Try something like

CREATE TABLE test_stmp(StartDate TIMESTAMP NOT NULL,
constraint chk_test_filed check(trunc(StartDate) = to_date('2000-01-01','yyyy-mm-dd')));


SQL Server 2012 Replication - MSrepl_commands growing all set to in progress I'm stuck looking at this problem that we've had since our last restart almost a week ago.

The distribution database is growing and no longer pruning; it appears that everything in the MSdistribution_history are all set to either 3 or 4 (in progress / idle) all the idle report as no replicated transactions are available)

We're now at 261K values in the history table and 195M rows in the repl_commands table

All replication through this distributor goes from one location to up to three different servers, there is currently a maximum of 4 seconds latency across all replication, cant see anything that isn't complete

Obviously running the sp_MSmaximum_cleanup_seqno command returns nothing as there is nothing that is set to be success (status 2)

Does anyone know what might have gone wrong or any possible solutions.

General things:

All replication is transactional without any snapshots
immediate_sync is turned off on all replication
max retention is set to 72 hours
no errors are being reported
there were a few issues when the server came back up where all the jobs retried twice.
The publisher server was patched from 2012_SP2_CU7 to SP3_CU2 and the distributor instance has not yet been patched

We're going to try a fail over to the secondary node tomorrow to see if that will clear it out

I can always manually delete stuff from the repl_commands but I'm highly hesitant to do this.

Any advice or ideas?

So we're working now, I'm not 100% sure which part of this fixed the issue but I'm putting it down for if anyone else has this issue in the future where there doesn't seem a logical reason for the distribution growth.
Our distributor is on a windows failover cluster across two machines along with a Reporting Instance (and a MSDTC).

The issue occurred after a restart where the distributor moved from
server 2 to server 1 running along side reporting(and MSDTC).

After server 2 restart was complete Reporting was moved over to
Server 2.

This is when the issue was noticed.

The Distributor was moved over to Server 2 (Alongside reporting) but this seemed to have no beneficial effect

The Distributor was moved back to Server 1 and MSDTC was moved to server 2 with Reporting
At this point running the sp_MSdistribution_cleanup with a max time of just shy of how long the server had been running for (by 6 hours) and it cleared out everything (Took close to 3 hours to complete but it got there)


Setup standalone with all the data from cluster, mongodb I have a mongo cluster with 3 nodes(a primary, a secondary, and an arbitrary) with replication enabled for primary and secondary. For some reason would like to take down the cluster and have a standalone instance(in arbitrary). Since data is not available in arbitrary, have copied data from cluster nodes to the path(/data/db). Removed the replication factor in mongod.conf and started mongo instance. But couldn't see the databases. What else is required? OR do I have to get mongo-dump and mongo-restore? If so, doing so for 200GB is a costly and time-consuming process. Help me with this.

As per MongoDB documentation Replica Set Arbiter An arbiter does not have a copy of data set and cannot become a primary. Replica sets may have arbiters to add a vote in elections for primary. Arbiters always have exactly 1 election vote, and thus allow replica sets to have an uneven number of voting members without the overhead of an additional member that replicates data.

  Changed in version 3.6: Starting in MongoDB 3.6, arbiters have
  priority 0. When you upgrade a replica set to MongoDB 3.6, if the
  existing configuration has an arbiter with priority 1, MongoDB 3.6
  reconfigures the arbiter to have priority 0.

Primary with a Secondary and an Arbiter

A three member replica set with a two members that store data has:

One primary.
One secondary member. The secondary can become primary in an election.
One arbiter. The arbiter only votes in elections.

Since the arbiter does not hold a copy of the data, these deployments provides only one complete copy of the data. Arbiters require fewer resources, at the expense of more limited redundancy and fault tolerance.


How to create an Oracle Database Link when the password has special characters I'm trying to create a DBLink. SQL Developer is the client I'm using. The password to my remote user has *'s in it. I'm having trouble figuring this out. This syntax isn't working:

CREATE DATABASE LINK my_link
CONNECT TO daniel identified by *password
USING 'hostname:port/servicename';

I tried '\' and wrapping the password in {}'s,', to no avail. I get a "Command Not Properly Ended" error. Any advice would help. I know it's a simple issue but having trouble figuring it out from googling.

Use quotation marks:

CREATE DATABASE LINK my_link
CONNECT TO daniel identified by "*password"
USING 'hostname:port/servicename';


Problem tracing Error: 8623 with extended events From time to time we are getting SQL Server error:

Error: 8623, Severity: 16, State: 1
The query processor ran out of
internal resources and could not produce a query plan. This is a rare
event and only expected for extremely complex queries or queries that
reference a very large number of tables or partitions. Please simplify
the query. If you believe you have received this message in error,
contact Customer Support Services for more information.

To be able catch what is causing this error I have created an extended event (XE) session on XE event error_reported. The XE session seems to be working as other errors are logged but not 8623.
My XE session is created like this:
CREATE EVENT SESSION [ErrorReported]
ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)
    WHERE ([severity]> 15))
ADD TARGET package0.event_file(SET filename=N'C:\trace\ErrorReported.xel',max_file_size=(250),max_rollover_files=(4))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

Anyone that help me to get XE to log also error 5623? Any help hugely appreciated.
We are currently running Microsoft SQL Server 2016 (SP2-CU10).

Thanks to Ronen Ariely over at MSDN and Jonathan Kehayias article on SQL Performance I was able to solve the problem. The problem was that event size was to big so that it was dropped. You can check this by querying sys.dm_xe_sessions as e.g.
   SELECT
   s.name,
   s.total_regular_buffers,
   s.regular_buffer_size,
   s.total_large_buffers,
   s.large_buffer_size,
   s.dropped_event_count,
   s.dropped_buffer_count,
   s.largest_event_dropped_size
FROM sys.dm_xe_sessions AS s;

If any event has been dropped you will se the number of dropped events in column dropped_event_count and the max size of any dropped event in largest_event_dropped_size. If this value is larger that the regular_buffer_size then you need to increase the regular buffer size.
This was the case for me and when I did change the Max Memory size from the default to 64MB the regular buffer size was increased so much that I was able to catch the error!
As a side note the problematic query was a entity framework generated SELECT with an huge (> 38 000 values) IN clause! No wonder that the database engine got enough.


SQL Server: Recursive dynamic SQL puzzle I was looking at this post of trim all columns of a table. In the answer query script the column names gets concatenated without any looping operation. I simplified it to check its work flow as explained below.

Create a new database
Create a new table with three columns as in below script.(insert
values not required)

Create table table1(id int, name varchar(20), city varchar(20))

Execute below T-SQL Script, which results xxx [id] [name] [city]  in single row.

Declare @a as varchar(4000)
SET @a = 'xxx '
SELECT @a = @a + '['+COLUMN_NAME+'] ' FROM INFORMATION_SCHEMA.COLUMNS
SELECT @a

Where as below script results in 3 rows

Declare @a as varchar(4000)
SET @a = 'xxx '
SELECT 'b' = @a + '['+COLUMN_NAME+'] ' FROM INFORMATION_SCHEMA.COLUMNS

To understand more better I executed the below query using Top 1. This results xxx [id]

Declare @a as varchar(4000)
SET @a = 'xxx '
SELECT Top 1 @a = @a + '['+COLUMN_NAME+'] ' FROM INFORMATION_SCHEMA.COLUMNS
SELECT @a

I got puzzled With Top 2 query. This resulted xxx [id] [name].

Where as I was expecting xxx [id] xxx [id] [name] i.e. First value + second value. Where am I going wrong.

I am curious to know how the column names getting concatenated. Sorry I could not frame better title for this puzzle.

I am curious to know how the column names getting concatenated.

Can't really answer that. Some internal workings of SQL Server creates a result like that, sometimes.

The technique has been around for a considerable amount of time. Microsoft does not support the functionality and advises not to use it.

From SET @local_variable (Transact-SQL)

  Do not use a variable in a SELECT statement to concatenate values
  (that is, to compute aggregate values). Unexpected query results may
  occur. This is because all expressions in the SELECT list (including
  assignments) are not guaranteed to be executed exactly once for each
  output row.

Instead you can use a cursor or the for xml trick to do the same.

declare @a as varchar(4000);
set @a = 'xxx ';

set @a = @a + (
              select quotename(COLUMN_NAME) as '*'
              from INFORMATION_SCHEMA.COLUMNS
              for xml path(''), type
              ).value('text()[1]', 'varchar(4000)');

Result: xxx [city][id][name]


Is possible to bypass the 32 bit limitation in MongoDB? Can you help me please? I have got server for research purposes, where I have used MongoDB. But in this time size of my data exceed 2 GB limit. I do not any possibility of migrating to 64 bit. version. Is possible to bypass the 32 bit limitation in MongoDB some way please? Or it is definitely the end of my research on this system?

You could configure sharding  because 2Gb limit only applies to individual mongodb processes.
Pls refer the documentation
sharded-clusters


Which DBMS are more standard compliant? I find it frustrating that the same queries don't work in different DBMS.

Example, some DBMS (such as Microsoft SQL Server and MySQL) seems to support the information_schema table, while others (such as SQLite) don't.

Some databases such as MySQL allows you to do SHOW TABLES, etc.

Some limit the results using SELECT TOP 10 ... while others with ... LIMIT 10.

Why are different DBMS so incompatible and deviant?

Why don't they follow the SQL standard?

Which are good at adhering to the SQL standard?

Which are notoriously bad at adhering to the SQL standard?

I find it frustrating that the same queries don't work in different DBMS.

Yes. I find it frustrating that the headlights and wiper controls are not always in the same place in different cars. Since I can't control it, I learn to live with it. I generally stick to certain car brands but the location of these things is not anywhere near the top of the list of primary decision criteria. Similarly, I don't pick a database platform because of what percentage of the standard they cover or how many queries work the same as in Oracle and MySQL.

  Example, some DBMS (such as Microsoft SQL Server and MySQL) seems to support the information_schema table, while others (such as SQLite) don't.

INFORMATION_SCHEMA was an interesting choice. Yes, some platforms have opted not to support that standard at all. Others have covered the bare minimum. I make a pretty strong argument against using INFORMATION_SCHEMA in SQL Server, for example:

The case against INFORMATION_SCHEMA views

  Some databases such as MySQL allows you to do SHOW TABLES, etc.

Well, yes, some databases are more notorious than others for just making up stuff that seems to make sense to the developer knee-deep in code at the time (LIMIT and their perverse GROUP BY forgiveness are other examples).

  Why are different DBMS so incompatible and deviant?

I'm sure some do things like SHOW TABLES because that is considered easier than SELECT name, other cols FROM sys.tables;. In fact I would bet that that's the problem with the majority of the world's codebase, RDBMS included; people tend to do what's easier rather than what's right.

But I'm not sure it's a problem that the different platforms are incompatible and "deviant" as you call them.

  Why don't they follow the SQL standard?

Covering the entire standard is hard. Covering only the standard is even harder. Vendors develop extensions to the standard, and proprietary features not (yet) covered by the standard, to give users more powerful platforms. Imagine if we sat around waiting for the next iteration of the standard before we could develop anything? Imagine if all of the platforms covered the full standard and nothing but the standard? Imagine if all of the platforms only implemented any new functionality that they all agreed to develop the same and release at the same time? What would be the point of having different platforms? They'd all be the same, and the incompatibility would no longer be an issue because you could switch freely between platforms without risk - but then why would you, if they're all the same? See the problem you're "solving" - it just creates a different problem.

Take indexes for example. The standard doesn't care about physical implementation, and some vendors have made decisions to implement all kinds of beneficial index variations and physical storage mechanisms to better their platforms - SQL Server has filtered indexes, include columns, XML indexes, full-text indexes, in-memory tables, columnstore, etc. etc. None of which you'll read a lick of in the standard. So as they develop these things, how are they supposed to make it compatible with anything along the same lines that Oracle may or may not be developing or planning? Postgres has gone on and made the decision that elaborate JSON support is far more important than, say, performance gains from parallel processing. That's fine, and totally within their right. That doesn't mean I want SQL Server to do the same thing.

Normal ad hoc CRUD stuff should work identically for basic vanilla tables across platforms (though even there, MySQL has done some funky things like use backticks around table names by default, which need to be stripped out if trying to port the code to any other platform). As soon as you start doing anything more exotic outside of standard storage and retrieval, the standard throws its arms in the air. You want Window functions? Okay, you need your vendor to roll up their sleeves and implement them, because most platforms don't have them, and they take a lot of work; even SQL Server is not yet complete, but they're way ahead of the other guys. And they have plenty of other language features that I'm very happy are there, in spite of them not being in the standard.

  Which are good at adhering to the SQL standard?
  Which are notoriously bad at adhering to the SQL standard?

I don't know enough about the full standard and all of the platforms to comment. But I will suggest this: If this is the reason you're choosing one platform or another, stop what you're doing right now, and ask yourself why this is so important. I will also suggest that SQL Server has gotten much better about adhering to the standard and implementing extensions where they are beneficial (a look back in history at things like timestamp and, yes, top shows that they used to care a lot less). Some other platforms give a big middle finger to the standard, and continue to do so.


How to restore a database, ignoring the free space within each file? Using the following script I get all the disk space used by each file in my database:

;with radhe as (
SELECT

DatabaseName = 'my Database', --DB_NAME(),

a.FILEID,

[FILE_SIZE_GB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000/1024.000, 2)),

[SPACE_USED_GB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, 'SpaceUsed') / 128.000/1024.000, 2)),

[FREE_SPACE_GB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128.000/1024.000, 2)),

a.NAME, a.FILENAME

FROM dbo.sysfiles a)

select * from radhe

this results:

As you can see on the above picture, the amount of free space inside MyDatabase is high.

I need a copy of this database so that I can test some partitions operations.

Is there a way I can restore this database ignoring the free space in each file?

Basically I will use my "new database" to develop and test something, I would prefer to keep it small.

How could I achieve that?

Is there a way I can restore this database ignoring the free space in each file?

No you cannot. You can restore the database and then do a shrink of database file.

I assume that you are aware of all the caution that the smart people have given.

I Need to Shrink My Database - I just freed a lot of space
Why Does the Transaction Log Keep Growing or Run Out of Space? - since you have a large ldf file.

  Basically I will use my "new database" to develop and test something, I would prefer to keep it small.

Why dont you use a data generator or from here or just get a sample of data from your PROD for testing ?


Retrieving most recent X rows for each given user from a table We've got a seemingly simple query that's running extremely slowly, but I can't quite figure out the best solution to the problem. The scenario is as follows.

We have an inbox table, which has the columns (with a few left out to keep things simple - the database structure is extremely old and not particularly well normalized):

id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
recipient varchar(55) KEY
sender varchar(55) KEY
subject varchar(255) DEFAULT NULL
message mediumtext NOT NULL

This table has a number of rows, normally in the magnitude of 200,000+. The table uses the InnoDB engine.

One of our background tasks runs every night to try and manage the inbox to keep the table size down by archiving messages. It attempts to keep only the X most recent message for each recipient (where X is configurable - it is usually 30). Currently the system runs a query to get the IDs of the messages to keep, then deletes the messages where ID isn't in this list. The current query (which runs extremely slowly) to get the IDs to keep is:

SELECT  x.id
    FROM  inbox x
    JOIN  inbox y ON y.recipient = x.recipient
                 AND  y.id >= x.id
    GROUP BY (x.recipient), x.id
    HAVING  COUNT(*) <= 30;

This query runs extremely slowly and bogs the whole system down, with MySQL using very large amounts of CPU time. I enabled slow query logging, and got the below output:

# Time: 160208 10:51:17
# User@Host: root[root] @  [127.0.0.1]
# Thread_id: 29  Schema: test QC_hit: No
# Query_time: 3568.641446  Lock_time: 0.000718  Rows_sent: 1312  Rows_examined: 585499843
SET timestamp=1454928677;
Select  x.id
    FROM  inbox x
    JOIN  inbox y ON y.recipient = x.recipient
                 AND  y.id >= x.id
    GROUP BY (x.recipient), x.id
    HAVING  COUNT(*) <= 512;

The whole query seems like a bit of a mess, but I can't currently think of the best way to go about doing what I want. It's easy enough to get the single most recent message for each recipient, but I can't think of the best way of expanding that.

I've put together a quick SQL Fiddle with the database structure and a small subset of data, found here: http://sqlfiddle.com/#!9/04df7/1

I'm currently running MariaDB 10.0.23, which is the current most recent MariaDB 10.0 series release.

I hope you are willing to do some programming outside of SQL.

To find the recipients that need the most pruning:

SELECT recipient
    FROM inbox
    GROUP BY recipient
    HAVING COUNT(*) > 30
    ORDER BY COUNT(*) DESC
    LIMIT 100

Then, for each recipient, first find the 31st id:

SELECT id
    FROM inbox
    WHERE recipient = $recipient
    ORDER BY id DESC
    LIMIT 30,1

And delete the excess baggage:

DELETE FROM inbox
    WHERE recipient = $recipient
      AND id < $id

You would need INDEX(recipient, id).

This process could be running continually.  The first SQL would be the slowest, but it would run "Using index" and probably not be too bad, maybe 0.1 sec for 200K rows in inbox.

If you want to stay in SQL, this would let you do one recipient at a time:

BEGIN;
SELECT @recipient := recipient
    FROM inbox
    GROUP BY recipient
    HAVING COUNT(*) > 30
    ORDER BY COUNT(*) DESC
    LIMIT 1
    FOR UPDATE;
SELECT @id := id
    FROM inbox
    WHERE recipient = @recipient
    ORDER BY id DESC
    LIMIT 30,1
    FOR UPDATE;
DELETE FROM inbox
    WHERE recipient = @recipient
      AND id < @id;
COMMIT;

Then, put that in a Stored Procedure with a loop around it.  And, if you want to be further 'nice', add a SELECT SLEEP(1); in the loop but outside the transaction.

(Trying to do the entire thing in a single statement makes my brain hurt.)


Is it possible to re-use a query plan from the plan cache from one database in another database? For example, if I hit the sys dynamic views to select a specific query plan, am I able to insert that query plan into another database's plan cache, that runs the same exact query? (I know the query is hashed and compared to determine when to generate a new plan, so in my example I would ensure the query truly is exactly the same character by character.)

No

Ok, so that's the answer to your question, but you probably want to know why.

Ok, so why not?

Different databases might have different data, and thus need different plans.

Say you restore the WideWorldImporters database to the same server TWICE (Let's call it WWI_1 and WWI_2). You've got two identical databases. SQL Server could possibly create one plan and use it in queries for both databases.

But the problem is, the instant these two databases come online, thier "sameness" forks. They can be altered independently. Even if they maintain the same schema, their data can change independently. Thus, SQL Server must consider the databases independently when compiling plans. WWI_1 could have different statistics from WWI_2, which could result in different plans. In order for SQL Server to use the same plan for both databases, SQL Server would need to keep track of the differences after forking--which would be more complicated & more expensive than just compiling/maintaining separate plans.

Let's take a real-world example

Let's pretend you are a software company, and host software for your customers. Every customer gets their own database. In your hosting environment, you might have hundreds of databases on every server, where every database has identical schema, but data unique to each client.

One client has a customer base with 95% California clients. Querying the address table for all customers in California would result in a table scan.

-- For CustomerA this query returns 95% of the table, so it scans
SELECT CustomerID
FROM dbo.Addresses
WHERE StateCode = 'CA';

A different client has a customer base that is evenly distributed across every US state, and has a significant international business as well. A comparatively small percentage of clients are from California. In this case, querying the address table for all customers in California would result in a table seek.

-- For CustomerB this query returns <1% of the table, so it seeks
SELECT CustomerID
FROM dbo.Addresses
WHERE StateCode = 'CA';

Identical queries, on databases with identical schema, with radically different plans due to different statistics. Even if these two databases were both originally restored from the same source backup, SQL Server will need to compile separate plans.

It's not enough that the two queries are 100% identical, and the schema is 100% identical. A reused plan based only on those criteria could be very wrong--and thus SQL Server won't do it.

Could you do it if you really wanted to?

Uh....kind of.

Let's use the same "you are a software company, and host software for your customers" example. You want to force the same plan across every hosted database, regardless of what SQL Server wants to do. You could use a plan guide and apply the same guidance to every single database on the server. This isn't quite the same as "insert [one] query plan into another database's plan cache"... but it would be virtually the same thing.

What about a totally trivial plan?

Something like SELECT COUNT(*) FROM dbo.SomeTable would be simple enough to use the same plan on both two databases, right? No, not even then!

Let's create an example:

Create a sample database
Create a table & populate it with some data

Note the table has a Clustered PK & a non-clustered index

Backup & restore a second copy to the same server

Here's some code to do that:

CREATE DATABASE Sample1;
GO

USE Sample1
GO

CREATE TABLE dbo.SomeTable (
    SomeID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    StuffType int,
    OtherStuff varchar(100),
    INDEX OtherStuff(OtherStuff)
);
GO

SET NOCOUNT ON;
INSERT INTO dbo.SomeTable (StuffType,OtherStuff)
SELECT  object_id%50,
        name
FROM sys.objects;
GO 1000

BACKUP DATABASE Sample1 TO DISK = '/var/opt/mssql/data/Sample1.bak' WITH INIT;

RESTORE DATABASE Sample2 FROM DISK = '/var/opt/mssql/data/Sample1.bak'
    WITH MOVE 'Sample1' TO '/var/opt/mssql/data/Sample2.mdf',
         MOVE 'Sample1_log' TO '/var/opt/mssql/data/Sample2_log.ldf';

Now, let's do that COUNT(*) query. Same plan on both? Yes! On my laptop, it's performing the count by scanning the clustered PK

SQL Server is choosing to scan the PK because it's smaller. The non-clustered index is larger because it's highly fragmented.

Now index maintenance happens on the Sample1 database (but not Sample2). Someone rebuilds the indexes on dbo.SomeTable:

ALTER INDEX ALL ON Sample1.dbo.SomeTable REBUILD;

How does that affect the query plan?

On Sample1, the non-clustered index is now nice & compact. It is smaller than the PK. Scanning the non-clustered index will be the right choice on `Sample1 because it's smaller, fewer IOs, and will be faster.

On Sample2, scanning the PK is the right choice, because it's smaller, fewer IOs, and thus faster.

Identical schemas with identical data, with the second forked from a recent backup of the first. Both of these queries have different execution plans, but both also have the best query plan for their scenario.


accelerating a SQL quadruple self-join with a complex alternation in the WHERE clause The following Sqlite query has a triple self-join on table t:

SELECT "update_variable",
    lhs_acc.name_suffix || ":" || rhs_var.name_suffix,
    op.span,
    op.path
FROM t op
JOIN t lhs_acc ON (lhs_acc.path GLOB op.path || "?*")
JOIN t rhs_acc ON (rhs_acc.path GLOB op.path || "?*")
JOIN t rhs_var ON (rhs_var.path GLOB op.path || "?*")
WHERE (op.name_prefix = "assignment"
    AND lhs_acc.name_prefix = "assignment_lhs_identifier"
    AND rhs_acc.name_prefix = "assignment_rhs_atom"
    AND rhs_var.name_prefix = "assignment_rhs_atom"
    AND rhs_acc.name_suffix != rhs_var.name_suffix
    AND lhs_acc.name_suffix = rhs_acc.name_suffix)
OR (op.name_prefix = "augmented_assignment"
    AND lhs_acc.name_prefix = "assignment_lhs_identifier"
    AND rhs_acc.name_prefix = "assignment_rhs_atom"
    AND rhs_var.name_prefix = "assignment_rhs_atom")
OR (op.name_prefix = "method_call"
    AND rhs_acc.name_prefix = "method_call_name"
    AND lhs_acc.name_prefix = "method_call_object"
    AND rhs_var.name_prefix = "call_argument"
    AND rhs_acc.name_suffix != rhs_var.name_suffix
    AND rhs_acc.name_suffix REGEXP "(append|extend|insert|add|update)$")
GROUP BY op.span,
        lhs_acc.name,
        rhs_var.name

It is far too slow for my application. I can accelerate it enough by restricting each instance of t to the lines I am interested in, which somehow duplicates the WHERE conditions. So, the FROM clause becomes:

FROM
(SELECT *
FROM t
WHERE t.name_prefix IN ("assignment",
                        "augmented_assignment",
                        "method_call")) op
JOIN
(SELECT *
FROM t
WHERE t.name_prefix IN ("assignment_lhs_identifier",
                        "method_call_object") ) lhs_acc ON (lhs_acc.path GLOB op.path || "?*")
JOIN
(SELECT *
FROM t
WHERE t.name_prefix IN ("assignment_rhs_atom",
                        "method_call_name") ) rhs_acc ON (rhs_acc.path GLOB op.path || "?*")
JOIN
(SELECT *
FROM t
WHERE t.name_prefix IN ("assignment_rhs_atom",
                        "call_argument") ) rhs_var ON (rhs_var.path GLOB op.path || "?*")

Is there some technique which would lead to a less verbose / ugly / redundant query ?

Edit: The test duplication (but not the ugliness) can be avoided as follows:

SELECT "update_variable",
    lhs_acc.name_suffix || ":" || rhs_var.name_suffix,
    op.span,
    op.path
FROM
(SELECT *
FROM t
WHERE t.name_prefix IN ("assignment",
                        "augmented_assignment",
                        "method_call")) op
JOIN t lhs_acc ON (lhs_acc.name_prefix = (CASE op.name_prefix
                                            WHEN "method_call" THEN "method_call_object"
                                            ELSE "assignment_lhs_identifier"
                                        END)
                AND (lhs_acc.path GLOB op.path || "?*"))
JOIN t rhs_acc ON (rhs_acc.name_prefix = (CASE op.name_prefix
                                            WHEN "method_call" THEN "method_call_name"
                                            ELSE "assignment_rhs_atom"
                                        END)
                AND (rhs_acc.path GLOB op.path || "?*"))
JOIN t rhs_var ON (rhs_var.name_prefix = (CASE op.name_prefix
                                            WHEN "method_call" THEN "call_argument"
                                            ELSE "assignment_rhs_atom"
                                        END)
                AND (rhs_var.path GLOB op.path || "?*"))
WHERE op.name_prefix = "augmented_assignment"
OR (op.name_prefix = "assignment"
    AND lhs_acc.name_suffix = rhs_acc.name_suffix
    AND rhs_acc.name_suffix != rhs_var.name_suffix)
OR (op.name_prefix = "method_call"
    AND rhs_acc.name_suffix REGEXP "append|extend|insert|add|update)$"
    AND rhs_acc.name_suffix != rhs_var.name_suffix)
GROUP BY op.span,
        lhs_acc.name,
        rhs_var.name

In case someone is interested, I answer my own question: since I reuse a large number of this filtered tables in several queries, I ended up precalculating them. This address both the readability and performance issues.


Local MySQL instance running extremely slow compared to remote version I'm working on a local project and it have a remote version running normally and I did imported all data from external DB to local instance. When I run a query with a specific FUNCTION, it run too slow, but too slow, that never finish (I wait about 10 minutes, and nothing), but on the remote server, it does very fast, less than 1 second.

I checked and copied a lot of mysql configs and saved to my.ini from the remote MySQL instance. Nothing changed. You can see the difference that remained here. Basically, system things, directories, bits difference, etc. The uniques differences that I noted and I can't change is the compiled version (local is 32 bits, remove is 64 bits), MySQL version (local is 5.6.16, remove is 5.6.21).

This problem starts to occur after I create a MySQL FUNCTION that return a "better version" of SOUNDEX native function. It's very simple, and run fine remotely (the same version of function). Basically get a string and return the SOUNDEX for each word, for instance, SOUND_EX("John Doe") returns J530 only. My SOUNDEX_SPLITTED("John Doe", ",") will return J500,D000. With correct usage, it allow I get the name John Server Doe searching "incorrectly" by Jon Doeh.

CREATE DEFINER=`root`@`localhost` FUNCTION `SOUNDEX_SPLITTED`
        (`strValue` TINYTEXT, `strSeparator` TINYTEXT)
    RETURNS tinytext CHARSET latin1
    LANGUAGE SQL
    DETERMINISTIC
    NO SQL
    SQL SECURITY INVOKER
    COMMENT 'Executa um SOUNDEX separando por espaços.'
BEGIN
    DECLARE spaceFirst INT;
    DECLARE spaceLast INT DEFAULT 0;
    DECLARE strLength INT DEFAULT LENGTH(strValue);
    DECLARE soundexPart TINYTEXT;
    DECLARE soundexValue TINYTEXT;

    IF strValue RLIKE "[0-9]" THEN
        RETURN NULL;
    END IF;

    SET strValue = CONCAT(strValue, " ");
    IF strSeparator IS NULL THEN
        SET strSeparator = " ";
    END IF;

    STR_LOOP:
    WHILE spaceLast < strLength DO
        SET spaceFirst = spaceLast + 1;
        SET spaceLast = LOCATE(" ", strValue, spaceFirst);
        SET soundexPart = SUBSTRING(strValue, spaceFirst, spaceLast - spaceFirst);

        IF LENGTH(soundexPart) <= 2 THEN
            ITERATE STR_LOOP;
        END IF;

        SET soundexValue = CONCAT_WS(strSeparator,
                                     soundexValue,
                                     SOUNDEX(soundexPart));
    END WHILE;

    RETURN TRIM(soundexValue);
END

If I cancel this function to running completly (like a RETURN SOUNDEX(...) imediatly after declarations), the full query back to work again.

You have at least one bug in your function that allows it to enter an infinite loop on bad input data.  Care always has to be taken with loops in stored programs, to be certain this doesn't happen, regardless of the input.  I haven't analyzed this thoroughly, so there may be other cases that can break it, but here's the one I spotted.

You're using the input variable strValue internally, and it's a TINYTEXT, which is limited to 2^8-1 (255) characters.  But then, you're appending a trailing space.

SET strValue = CONCAT(strValue, " ");

What happens when your input value is >= 255 characters?

mysql> SELECT SOUNDEX_SPLITTED(REPEAT('a',255),',');
-- nothing happens

You have an infinite loop, because you never find the trailing space you're expecting, so this...

WHILE spaceLast < strLength DO

...remains true forever, because this...

SET strValue = CONCAT(strValue, " ");

...did not actually add a space.  It couldn't, because there's no room.  So the value was truncated back to its original, without a trailing space.

Actually, it's not true that nothing happens.  There's a warning generated, if the input is actually larger than 255 characters, and truncated at the input of the function... but you'll never see it, because it won't be visible until after the function runs.

+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1265 | Data truncated for column 'strValue' at row 1 |
+---------+------+-----------------------------------------------+

If the input data is exactly 255 characters, there's a similar warning that should be generated within the function when you try to concatenate that single space, but Oracle has mangled the proper handling of the diagnostics area within running programs in a misguided attempt to make the right errors show up at the right time.  Even if it were there, though, you wouldn't see it, since the function is still running in an endless loop.

I'm not suggesting the following is the correct fix, but will easily demonstrate the problem (works with MySQL 5.5 and up):

SET strValue = CONCAT(strValue, " ");
-- verify that we actually did just now add a trailing space
IF strValue NOT RLIKE '[[:space:]]$' THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'string overflow';
END IF;

Now...

mysql> SELECT SOUNDEX_SPLITTED(REPEAT('a',255),',');
ERROR 1644 (45000): string overflow

A better solution might be to use an internal variable that's of a longer type than the input, and copy the input plus the trailing space into that new variable, or modify the code to find the end in a different way, so that the trailing space isn't required.


Error Code: 1133. Can't find any matching row in the user table Im reading O'Relly "Learning MySQL" and it says that you can create a user in MySQL by doing just this:

mysql> GRANT ALL ON *.* TO 'jill'@'%.invyhome.com' IDENTIFIED BY 'the_password';
Query OK, 0 rows affected (0.01 sec)

But if I try to do:

-> grant select on testgrounds.personas to ''@'localhost';
--> Error Code: 1133. Can't find any matching row in the user table

But it succeds if I use a password:

->grant select on testgrounds.personas to ''@'localhost' identified by 'pass';
--> 0 row(s) affected

I had to restore the privileges for user root because i was experimenting and i messed them up, so i think before doing that i could actually use the statement in the book (i dont remember 100%)

-> select current_user;
--> 'root@localhost'

-> show grants;
--> 'GRANT ALL PRIVILEGES ON *.* TO \'root\'@\'localhost\' IDENTIFIED BY PASSWORD \'*4ACFE3202A5FF5CF467898FC58AAB1D615029441\' WITH GRANT OPTION'
--> 'GRANT ALL PRIVILEGES ON `testgrounds`.* TO \'root\'@\'localhost\' WITH GRANT OPTION'
--> 'GRANT PROXY ON \'\'@\'\' TO \'root\'@\'localhost\' WITH GRANT OPTION'

Is there something wrong with my MySQL installation or set-up?

This is not a problem with MySQL installation or set-up.

Each account name consists of both a user and host name like 'user_name'@'host_name', even when the host name is not specified.  From the MySQL Reference Manual:

  MySQL account names consist of a user name and a host name. This enables creation of accounts for users with the same name who can connect from different hosts.

This also allows MySQL to grant different levels of permissions depending on which host they use to connect.

When updating grants for an account where MySQL doesn't recognize the host portion, it will return an error code 1133 unless if it has a password to identify this account.

Also, MySQL will allow an account name to be specified only by it's user name, but in this case it is treated as 'user_name'@'%'.


Schema Migration: SQL Server Data Tools vs Liquibase and Flyway This might seem like a stupid question, but I've been looking into open source solutions for schema migration, namely Liquibase and Flyway.

However, my boss told me that SQL Server Data Tools (SSDT) achieves the same job.  I'm not sure if agree, but I can find very little on the Internet that directly compares it to Liquibase and/or Flyway.

My view is that SSDT is a development, data modelling and design tool for SQL Server and also supports schema comparison (and generating scripts thereof) and source control.  It tackles a different problem although there may be some overlap with Liquibase/Flyway in some aspects of schema migration.  But as an overall schema migration tool, Liquibase and Flyway are fully dedicated tools whereas SSDT is more for the design and development of a database.

Any opinions would be much appreciated even if it's just to say there's no comparison and SSDT is not a schema migration tool per se at all.

SSDT is comparable to Liquibase/Flyway as it does what they do but by taking a different approach. With SSDT you have the development environment so you get things like go to definition, find references and intelli-sense as well as the ability to compile a project into a dacpac and then deploy that dacpac to a database.

The SSDT way (and redgate sql compare way) to do a deloyment is to declare what you want so if you want to change a table that looks like:

create table a(id int)

to a table that looks like:

create table a(id int, another_column varchar(12))

with SSDT you just change your table definition to the second one and let SSDT worry about how to upgrade it (can it do an alter table, add column or does the column order change so you will need to rebuild the table etc).

With Liquibase (DbUp, ReadyRoll, manual methods etc) what you do is in this case have to write the alter table yourself and make sure that you run the scripts in the correct order, consider this scenario:

Release 1 - create column hello on table
Release 2 - rename column hello to joe_blogs
Release 3 - rename column joe_blogs to hello
Release 4 - create column joe_blogs

If any of the releases are missed, none of the next ones can continue.

Benefits of upgrade scripts (Liquibase, DbUp, etc):

You have complete control over the scripts
DBA's / Developers are used to this

Benefits of compare / merge (SSDT, Redgate SQL Compare):

Do not have to write upgrade scripts
It is easy to get to any specific version just compare and merge that version

Drawbacks of upgrade scripts:

Must be run in order
Rely on humans no making mistakes
Can be slow especially if you have a lot of changes
Unless your team is very disciplined databases in different environments (dev, test, staging, prod etc) often become out of sync making any testing invalid
Downgrading a release means writing the reverse of all the scripts you have already written

Drawbacks of using compare / merge:

Tools are not 100% trusted, perhaps unfairly
SSDT requires a working project, many many databases have code that doesn't actually compile or run (think dropped tables but not procedures etc), I have seen this in about 8/10 databases I have inherited :)
Many DBA's / developers are hesitant to give up developing in SSMS / notepad

Personally I really think SSDT is a professional development environment and it means that I can concentrate on writing useful code and tests rather than writing upgrade scripts which are in themselves just a means to an end.

You asked for opinions so there you go :)

ed


Maximum number of columns in PRIMARY KEY What is the maximum number of columns that can be used in a multi-column PRIMARY KEY in MariaDB?

I know about MySQL, but I can't find the limitation in MariaDB's documentation.

It's the same for MySQL and MariaDB: an index can consist of 16 columns, at maximum.
Since a PRIMARY KEY constraint automatically adds an index, it can't have more than 16 columns.

See MySQL Reference Manual / ... / The Main Features of MySQL:

  Support for up to 64 indexes per table. Each index may consist of 1 to 16 columns or parts of columns. The maximum index width is 767 bytes (per column) for InnoDB tables, or 1000 for MyISAM. An index may use a prefix of a column for CHAR, VARCHAR, BLOB, or TEXT column types.


sp_help for statements In SQL SERVER the

SP_HELP 'table_name'

returns a description of each column including it's type, length, nullable state etc. I'm using this info to auto-generate DTOs (classes) and mappings.

If I want to do this for an inline SQL statement (which includes aliases, joins etc.) I create a view temporarily with the definition being my SQL statement and then run sp_help for this view - which gives me all I need. After that I just delete the view.

The question:

Is there any other - simpler way of getting this info for inline SQL - preferably without going through the view creation?

Yes. All object definitions are available from within SQL Server as tables or views. These meta data sources are collectively known as "the catalog."

The industry standard way is through the information schema views. However, I've heard reports that SQL Server's implementation can be a bit rough.

There's another set with names like sys.tables, sys.views etc. These are the ones most often used.

A set of program APIs are published called server management objects (SMO). These can give meta data or script whole objects. They are what SSMS Object Explorer uses. The documentation can be terse but if you persevere it is very powerful. SMO can be called from C#, powershell or other languages.

To create the DTO for an arbitrary query I would reference the catalog views for the individual columns returned. Frankly your technique of creating a transient view is as good a way as any. You could also create a temporary table using select .. into #SomeName. Temp tables clean themselves away when they go out of scope. Similarly wrap the query in a temporary stored proc and use sys.dm_exec_describe_first_result_set.


find rows with no child and if has child latest child I have a simple table like this:

id
parent_id
created_at

with parent/child relationship.
If a row is child, then it has a parent_id, else its parent_id is 0.

Now I want to select all rows that either have no child (so itself) or have a child, so get the latest child by created_at (and not include itself in the final results).

As a visual example you can look at this tiny picture:

I just want rows 24 and 27 to be selected.

Is id guaranteed to be increasing along with created_at? In other words, can I just do a MAX(id) to always find the latest child value for the same parent_id? Or do I actually need to use the created_at date?

If I don't have to actually use the date, then you can do:

--rows that are not parent or child
SELECT id, parent_id
FROM mytable
WHERE parent_id = 0                              --not a child
AND id NOT IN (SELECT parent_id FROM mytable)    --not a parent

UNION ALL

--latest child of parent
SELECT MAX(id) as id, parent_id
FROM mytable
WHERE parent_id <> 0
GROUP BY parent_id

Gets kinda funny with the GROUP BY if you are wanting to include a bunch of other columns from the table.

But either way, a good general strategy is handling the two parts separately and use UNION ALL TO mash the results together. (UNION will check the two sets for duplicates, so a UNION ALL is preferred for performance reasons, since we know there won't be an overlap here.)


Use of ANY function for multiple values I am trying to use the ANY function of PostgreSQL to search the value from array interger type column.

My SQL:

SELECT
    *
FROM
    company_employee_contacts
WHERE
    corporate_complaint_type_ids = ANY(ARRAY[1,3]::integer[])

But it is giving me below error:

  ERROR:  operator does not exist: integer[] = integer

Can anyone tell me why I am getting this error while I am typecasting it?

Expected Result should be like:

id   | corporate_complaint_type_ids
----------------------------------
3212 | {1,3}
3216 | {1}
3218 | {3}
3220 | {1,2,3,4}
3221 | {3,4,5}

The reason for the error is that your code compares an array to an integer, i.e. the array in the column with any of the values in the array [1,3].

From the comments, it seems that the wanted result is when the two arrays (column and given array) have at least one common item, i.e. they overlap. The operator for "overlap" is &&:

SELECT
    *
FROM
    company_employee_contacts
WHERE
    corporate_complaint_type_ids && ARRAY[1,3] ;

For more operators on arrays, see the documentation: Array Functions and Operators

The idea of using ANY is good but you need a "double ANY" and this syntax would work (if it was valid):

WHERE       -- not valid syntax
    ANY(corporate_complaint_type_ids) = ANY(ARRAY[1,3]) ;

You could unfold both arrays or one of the two (using unnest() function) and combine it with ANY. This would be equivalent to the overlaps operator and work - although I see no reason to use something so complicated:

-- unfold with unnest and ANY
WHERE
    EXISTS
    ( SELECT 1
      FROM unnest(corporate_complaint_type_ids) AS u
      WHERE u = ANY(ARRAY[1,3])
    ) ;

-- or the reverse
WHERE
    EXISTS
    ( SELECT 1
      FROM unnest(ARRAY[1,3]) AS u
      WHERE u = ANY(corporate_complaint_type_ids)
    ) ;

-- or both with unnest
WHERE
    EXISTS
    ( SELECT 1
      FROM unnest(corporate_complaint_type_ids) AS u1,
           unnest(ARRAY[1,3]) AS u2
      WHERE u1 = u2
    ) ;


RSync Database File Deltas in SQL Server 2008 Alright, I feel like this is a terrible idea, but I need some help understanding why this is bad:

I'm still working on implementing a disaster recovery/business continuity solution for our datacenter. We're running MSSQL 2008 Enterprise and we plan on running a passive instance in the cloud. Currently I'm suggesting Log Shipping, but I've been asked to explore using RSync (or something similar) to push MDF/LDF file deltas to the cloud instead of using the internal tools.

The goal would be to reduce our footprint and not run the cloud SQL instance most of the time due to licensing issues (our Passive/DR license is already in use in our current datacenter, but if that center goes down, the license becomes available).

I've found a solution that uses VSS to create and push deltas even if the files are locked, but I'm wondering what sorts of issues could show up. Can I get some insight? We would be pushing deltas every 15 minutes, the database in question is roughly 2GB with maybe 10 MB of T-Logs generated in a 15 minute window.

I would not trust rsync to copy the ldf and mdf files for user or system databases, nor would I trust anything I hacked together (VSS or otherwise) in a production environment. SQL Server is very fussy about when (and if) things get written to the ldf and the mdf files. Software (rsync) that isn't designed with that in mind might not get it right, especially if it doesn't understand that the ldf files and mdf files need to be treated as an interrelated system of files. If that software doesn't get things right, nothing might be noticed until you failover, try to go live and have your databases flagged as suspect due to what SQL Server sees as data corruption. Even worse, "getting it right" might be dependant on how much load is on the system and you might not find the problem in a lightly-loaded test environment.

I have seen enough examples of people who thought that they were replicating their files but were not. They were left with corrupt files on the recovery site and with inaccessible backup files at the primary site. So, that meant no database for them.

In short, you are making an appointment for trouble.

If you had some sort of block-level replication technology like EMC's SRDF or were looking at shared-nothing clusters, that might be different. Those technologies interface with SQL Server and the clustering services that Windows provides in a way that your writes will be safe and your files will/should be consistent.

If my only disaster recovery option was a remote site that was normally down, I'd use log shipping and make sure that I had all of the pieces to restore the database(s) on the remote site. If you can't make the built-in log shipping do that, writing your own isn't that hard. I've probably written three or four (simplistic) log shipping systems from scratch in the last 14 years.

At a minimum, the key things you need are:
A full backup needs to be taken and copied over to the remote site.
Your tlog backups need to be taken and copied over to the remote site.
You need an automated method to restore that full backup and any relevant tlog backups. Ideally, this should be simple enough for someone else to do and/or simple enough for you to figure out at 3AM when your primary server fails and you are half asleep.

When you have an event, bringing up the other server will take longer because you would have so much manual stuff to do. That means that this isn't as good as simply implementing regular log shipping. You will need to test this periodically, as well.

(Of course, you need to worry about other things too, like jobs, packages, login and user synchronization, changing DSNs on the web servers during a failure, etc. If you have a large environment and a serious disaster, like the loss of your primary data center, you will be trying to do this when IIS guys, file server guys, network guys and whatever else guys are trying to bring their stuff up too.)

If it were me, I would be agitating for a warm standby server at the remote site. That would make (standard, out-of-the box) log shipping easier and database mirroring possible. It sounds like you have tried that.


How to toggle a boolean field by sql in postgresql? There is a field show which is a boolean type in postgesql.

I want to write a sql to update that table, to toggle the value of show. If it's true, it becomes false, if it's false, it becomes true.

Is it possible?

This will do:

SET show = NOT show

so a value of

TRUE becomes FALSE,

FALSE becomes TRUE,

UNKNOWN (NULL) stays UNKNOWN.


Return values from Left Table Only Once This is my table structure and a sample query.  However, my query returns these results:

ID   event1  Exists
1    cycle   No
1    cycle   Yes
2    run     Yes
3    walk    No

As you can see ID 1 is returned twice, a yes and a no value.  I only want each item from event1 returned 1 time in my query.  Desired output is:

ID   event1  Exists
1    cycle   Yes
2    run     Yes
3    walk    No

Here is sample table structure and my query (which please feel free to completely re-write), what must be altered in order to return my desired result set?

create table #robschneider
(
  ID int IDENTITY(1,1) PRIMARY KEY
  ,event1 varchar(100)
  ,uuid int
)
create table #bigponyhorses
(
  ID int IDENTITY(1,1) PRIMARY KEY
  ,uuid int
  ,empid varchar(10)
)
Insert Into #robschneider Values ('cycle', '1'), ('run', '2'), ('walk', '3')
Insert Into #bigponyhorses VALUES ('1','mn12'), ('2','mn12'), ('3', 'cr23'), ('1', 'bb11')

Declare @empid varchar(10)
Set @empid = 'mn12'

select
DISTINCT
[rs].[ID],
[rs].[event1],
case
  when [bph].[empid] = @empid AND [bph].[uuid] is not null then 'Yes' else 'No'
end as [Exists]
from #robschneider rs
LEFT join #bigponyhorses bph
on [rs].[uuid]=[bph].[uuid]

If it exists, I want a yes returned, if it does not exist I want a no returned.

This could be solved with an EXISTS subquery

select
    rs.ID,
    rs.event1,
    [Exists] =
        case when exists
                ( select *
                  from #bigponyhorses as bph
                  where rs.uuid = bph.uuid
                    and bph.empid = @empid
                )
            then 'Yes' else 'No'
        end
from
    #robschneider as rs ;

or with OUTER APPLY operator:

select
    rs.ID,
    rs.event1,
    [Exists] = coalesce(b.result, 'No')
from
    #robschneider as rs
  outer apply
    ( select top (1) result = 'Yes'
      from #bigponyhorses as bph
      where rs.uuid = bph.uuid
        and bph.empid = @empid
    ) as b ;

The TOP 1 is needed when using OUTER APPLY to make sure you get in the result one row for each #robschneider, even if there are 2 or more matching rows in the #bigponyhorses table. If there is a UNIQUE constraint on #bigponyhorses (uuid, empid), then it's not needed.


Shutdown mode fast for stopping PostgresSQL standby server Is it correct to assume that shutdown mode smart, i.e.

pg_ctlcluster 9.6 main stop

won't work for PostgreSQL standby servers and that I will always have to use shutdown mode fast (or immediate) instead:

pg_ctlcluster 9.6 main stop -m fast

I am assuming so because of this paragrah in pg_ctl(1):

  Three different shutdown methods can be selected with the -m option.
  “Smart” mode waits
         for all active clients to disconnect and any online backup to finish. If the server is in
         hot standby, recovery and streaming replication will be terminated once all clients have
         disconnected.  “Fast” mode (the default) does not wait for clients to disconnect and will
         terminate an online backup in progress. All active transactions are rolled back and clients
         are forcibly disconnected, then the server is shut down.  “Immediate” mode will abort all
         server processes immediately, without a clean shutdown. This will lead to a crash-recovery
         run on the next restart.

I am hesitating a bit, because I have been used so far in using fast (or immediate) only in cases of relative emergencies.

A WAL receiver is neither an active client, nor an online backup. So a so-called "smart" shutdown executed on the master will not wait indefinitely for the WAL receiver to go away on its own.  Rather, it will actively disconnect the WAL receiver.  This is pretty much the last thing it does--it waits for the shutdown checkpoint to finish, and streams the WAL containing that checkpoint record over to the replica, and then disconnects it.

If you are concerned about "If the server is in hot standby, recovery and streaming replication will be terminated once all clients have disconnected", that is describing what happens when smart shutdown is initiated against the replica, not against the master.  The master is never "in hot standby", that is a status that only a replica can be in.


How to give read only access to a user to view the tables of different owner in oracle I have 2 users are present in oracle ex User1 and User2, Under user1 some tables are present ex t1,t2,t3 etc.. . what are the minimum permissions to view the tables of user1 by using user2 ( Just want to view the table description not the Data) .

The REFERENCES privilege kind of provides this:

SQL> create user u1 identified by u1;

User created.

SQL> create user u2 identified by u2;

User created.

SQL> grant create session to u1, u2;

Grant succeeded.

SQL> grant create table to u1;

Grant succeeded.

SQL> create table u1.t1 (c1 number);

Table created.

SQL> grant references on u1.t1 to u2;

Grant succeeded.

SQL> conn u2/u2
Connected.
SQL> desc u1.t1
 Name           Null?     Type
 ------------------------------------
 C1                       NUMBER

SQL> select * from u1.t1;
select * from u1.t1
                 *
ERROR at line 1:
ORA-01031: insufficient privileges


postgres - pg_stat_statements directory Does pg_stat_statements use the same stats_temp_directory directory? Our stats_temp_directory is under /var/run/postgresql which resides in tmps. We have about 500 databases in an instance and set pg_stat_statements.max to 10000000 (20000 statements / db).

We don't want pg_stat_statements to be a major factor of i/o (we execute tons of short running queries). Can't seems to find this info in the pg_stat_statements.

postgres=# select version();
                                                                     version
-------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.10 on x86_64-pc-linux-gnu (Ubuntu 9.6.10-1.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit
(1 row)

pg_stat_statements always uses the literal directory "pg_stat_tmp", even if the "stats_temp_directory" parameter has been changed to point elsewhere.

See this note from the source code:

  Location of external query text file.  We don't keep it in the core
   system's stats_temp_directory.  The core system can safely use that GUC
   setting, because the statistics collector temp file paths are set only once
   as part of changing the GUC, but pg_stat_statements has no way of avoiding
   race conditions.  Besides, we only expect modest, infrequent I/O for query
   strings, so placing the file on a faster filesystem is not compelling.

Are your tons of short-running queries all entirely different queries?  If they all normalize to a smaller set of queries (i.e. groups that differ only in supplied parameter values, not in SQL syntax elements or SQL identifiers) then they won't generate IO because pg_stat_statements only needs to update the in-memory counters, not the on-disk normalized query text.


RIGHT JOIN did not work as expected I have a question about joining two tables.

Schema

CREATE TABLE [dbo].[DCString] (
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [DCDistributionBoxID] [bigint] NOT NULL,
    [CurrentMPP] [decimal](18, 2) NULL,
    CONSTRAINT [PrimaryKey3] PRIMARY KEY CLUSTERED ( [ID] ASC )
)

ALTER TABLE [dbo].[DCString]
    ADD CONSTRAINT [FK_DCString_DCDistributionBox] FOREIGN KEY([DCDistributionBoxID])
    REFERENCES [dbo].[DCDistributionBox] ([ID])

CREATE TABLE [dbo].[StringData](
    [DCStringID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [DCCurrent] [decimal](18, 2) NULL,
    CONSTRAINT [PrimaryKey4] PRIMARY KEY CLUSTERED ( [TimeStamp] DESC, [DCStringID] ASC)
)

The [StringData] table has the following storage stats:

Data space: 26,901.86 MB
Row count: 131,827,749
Partitioned: true
Partition count: 62

Usage

I now want to JOIN the data in the [StringData] table with the data from the [DCString] table.

Something like:

declare @begin datetime = '22.02.2016';
declare @end datetime = '23.02.2016';
declare @dcStringID bigint = 6658;

SELECT [DCString].[ID], [StringData].[TimeStamp]
FROM [StringData]
RIGHT OUTER JOIN [StringData] ON [StringData].[DCStringID] = [DCString].[ID]
WHERE [StringData].[ID] = @dcStringID
AND [StringData].[TimeStamp] >= @begin
AND [StringData].[TimeStamp] < @end;

What I expect on searched date range where matching data in the [StringData] table exist is this:

ID   | TimeStamp
6658 | 22.02.2016 10:00:00
6658 | 22.02.2016 11:00:00
6658 | 22.02.2016 12:00:00

...and on a searched date range where no matching data in the [StringData] table exist is this:

ID   | TimeStamp
6658 | NULL

Question

What I get on a searched date range where no matching data in the [StringData] table exist, is a 0 rows result. Why?

I simply want always to get all [DCString].[ID]s. Whats wrong with my JOIN or did I mess up completely?

Update 1 (related to the answer of @Aaron Bertrand):

I tried your way already, but I have to cancel testing the query because after 10min it is still running. It looked like this:

declare @begin datetime = '22.02.2016';
declare @end datetime = '23.02.2016';
declare @dcStringID bigint = 6658;

SELECT [DCString].[ID], [StringData].[TimeStamp]
FROM [StringData]
LEFT JOIN [DCString]
    ON [StringData].[DCStringID] = [DCString].[ID]
    AND [StringData].[TimeStamp] >= @begin
    AND [StringData].[TimeStamp] < @end
WHERE [StringData].[ID] = @dcStringID;

If I understand your requirements correctly, you want each row in DCString, even when there is no matching row in StringData. The two queries you have now are incorrect:

The first one performs DCString RIGHT JOIN StringData - this is backward to what you seem to want (and is one of the reasons most sane people avoid RIGHT JOIN whenever possible).
The second query performs StringData LEFT JOIN DCString - this is also backward to what you seem to want. I suggested changing the RIGHT JOIN to a LEFT JOIN, without changing the table order, but you swapped the tables too, for no net change. Why that one is currently not returning could be due to a lot of things, no way for us to speculate. Try and generate an estimated plan and see if you are falling prey to any of these reasons.

Again, based on what I think you want (you'll get better answers if you provide some sample data and desired results):

-- don't use regional, ambiguous formats like dd.mm.yyyy:
-- also you can declare multiple variables in one statement

DECLARE @begin datetime    = '20160222',
        @end   datetime    = '20160223',
        @dcStringID bigint = 6658;

-- don't litter your code with square brackets except where necessary:

SELECT d.ID, s.[TimeStamp] -- bad choice for a column name

-- always use schema prefix!
FROM dbo.DCString AS d
LEFT OUTER JOIN dbo.StringData AS s
  ON d.ID = s.DCStringID
  AND s.[TimeStamp] >= @begin
  AND s.[TimeStamp] <  @end
WHERE d.ID = @dcStringID;

Further reading:

Ambigous date formats
Schema prefix


Get partition column for a table in Greenplum How can I find what the partitioning scheme is for a table? Is it in information_schema or in the pg_class related tables?

Thanks to @a_horse_with_no_name for pointing me in the right direction,  pg_partition_columns is the table I need.


Create primary keys based upon higher-level entity in access? Is it possible to have access create primary keys for items by appending a string to the primary key one level above it?

I am trying to create an inventory management database for my company's server ops team, to keep track of things such as switches, servers, UPS's, etc.  I have things organized to separate our different locations, the different rooms in said locations, the different racks in said rooms, and finally a 1-1 relationship between an occupied rack U and a device.  The primary key for locations would be a simple, 3-letter designation that we use as a site code.  The primary key for a room would be that site code plus "-###" where "###" is the displayed room number.  This is to simplify the attributes for the room/lower levels (i originally had an auto-gen number for pKey, with a separate attribute for display number).  this would continue down, until the rackU and/or device entities.  For them, i'm looking to allow the admins to use a form, select the correct Location, Room, and Rack#, then enter a rackU and serial number/asset tag for the device.  Then the rackU primary key would need to generate itself to be the primary key value of its associated rack, plus the U number.

To simplify, I would be manually inputting each entry down to rackU, and primary keys would look like:

[Loc] = ABC
[Room] = [Loc]-###
[Rack] = [Room]-A/1 (some racks are lettered, some are numbered)

Then, when entering a new device, user would select Location, Room, Rack, and input a rackU number.  I need some way to auto-generate the rackU primary key to be the linked Rack primary key plus the given U number.

It's one of those things where I'm fairly certain I could make it work if I was writing this database from scratch in python or PHP, or if i was using an actual mySQL database, but I'm completely lost as to how to handle this in Access.

I think the difficultly lies in using the parent table as part of the sub-table's primary key. So long as you have a reference to the parent table, you can always generate a reference that combines Location+Room or Room+Rack. Otherwise the primary key will get too hard to update and you have bad primary key.

I can see two ways to tackle this depending on how many locations, rooms, racks you have. It depends on quantity for your interface.

Firstly, a schema...

tblLocation(locationID[PK++], locationReference)
tblRoom(roomID[PK++], keyLocation[tblLocation.locationID], roomNumber)
tblRack(rackID[PK++], keyRoom[tblRoom.roomID], rackReference)
tblDevice(deviceID[pk++], keyRack[tblRack.rackID], positionU, deviceLabel)

...tables for custom devices can extend tblDevice

Foreign keys in square brackets, PK++ means an auto increment primary key.

As you can see, a Location has many Rooms, a Room has many Racks, a Rack has many Devices.

I have made primary keys as auto IDs as I prefer them to be integers and there's less to think about should you change a location/room/rack reference.

Populate this with Locations, Rooms and Racks as appropriate.

Solution 1 :: Not a lot of racks

Have three drop downs on the form.
User chooses a location, you then select all the rooms of that location in the next combobox.
User then selects the room, you then fill the next combobox with the racks in the room.
User then fills in device details and U position (which you probably want to validate to make sure that you don't have anything allocated there.)

Solution 2 :: Lots of racks

Have a query similar to:

SELECT
    rackID,
    locationReference + '-' + roomReference + '-' + rackReference AS [Full Location]
FROM tblLocation
    INNER JOIN tblRoom
        ON tblLocation.locationID = tblRoom.keyLocation
    INNER JOIN tblRack
        ON tblRoom.roomID = tblRack.keyRoom
ORDER BY
    locationReference ASC,
    roomNumber ASC,
    rackReference ASC

To generate a list of Location-Room-Rack references.

ABC-101-A/1
ABC-101-A/1
ABC-101-A/2
ABC-101-A/2
ABC-105-B8
ABC-105-B8
FAY-208-X8
FAY-210-A9T
FAY-210-A9T
FAY-210-F4
NAT-410-78
NAT-410-78

User selects one, then fills in the information. List will be equal to the number of racks (which could get a bit excessive).

Advantages

If you move a rack (or racks) of equipment from one room to another, it's just a simple update query to update the database
If a location/room/rack reference changes, you only have one piece of data to update.

Further schema additions

I've used racks in the past, I simplified the schema but these are some additions that spring to mind.

Add rackSize to tblRack: the size in U's
Add sizeU to tblDevice: the size of the device in U's

You can then do further validation: if you have a 39U rack and somebody tries to put a 5U device in at location 38, you can throw an error (as U's 40-42 don't exist!). Also if somebody puts a 3U device in at location 10, and something is in location 11, you know it won't fit or they have not updated the details of the device that is no longer there!

You could also make some clever queries that would tell you how much space there is in your racks (and where). But that's beyond the scope of this question!

Hope this helps.


How to wrap long lines when SELECTing SQL text columns? I'm selecting from a table with long text columns. I'd like to wrap long lines to a maximum line length.

From:

SELECT * FROM test;
test_id |                                  text
--------+-----------------------------------------------------------------------
      1 | Lorem ipsum dolor sit amet, consectetur adipiscing elit. Mauris lorem

To:

test_id |              text
--------+-----------------------------
      1 | Lorem ipsum dolor sit amet,+
        | consectetur adipiscing elit+
        | . Mauris lorem

If you're using the psql command line tool, issue this command first:

\pset format wrapped

It should then wrap long lines to your terminal window like so:

test_id |              text
--------+-----------------------------
      1 | Lorem ipsum dolor sit amet,.
        |.consectetur adipiscing elit.
        |.. Mauris lorem

You can also set the number of columns to wrap to with

\pset columns 100

and you can change the dots to ellipses with

\pset linestyle unicode

More info: http://www.postgresql.org/docs/current/static/app-psql.html


AWS RDS showing more disk usage than PgAdmin statistics I am using AWS RDS to host one PostgreSQL database. I notice that the disk usage at the AWS dashboard is much more (2x) than what is shown at PgAdmin. Hopefully someone can help me to figure out it.
AWS

PgAdmin

I find this issue after I tried several times to create a materialized view for caching very complex query results. All failed because of out of space (for computing?). I expect the remaining space (~7GB originally) would return but it didn't!!

It seems to be a lot of junk produced by the failed operations. A vacuum fix this issue.


Object changed during datapump If some object is being changed when  data pump is run how will that object go into datapump file?

If someone is INSERT/DELETE/UPDATE on a table at the same moment your expdp is working on that table, it won't affect that object.  You will get a consistent snap of the table at the moment that expdp got to that table.  Basically expdp is consistent WITHIN a table, but not BETWEEN/AMONG tables, unless ...

Since expdp processes your tables serially, they will not be consistent between each other unless you do your expdp with the FLASHBACK_TIME=SYSTIMESTAMP option.  I typically do this on every expdp unless I have a good reason not to.  This does not require database flashback to be enabled, but makes use of UNDO (rollback segments).  If your data changes too rapidly, your expdp will error with "ORA-01555 snapshot too old" and the table will be skipped. This means that there was not enough space in UNDO to preserve read-consistency across your entire expdp job. Increase UNDO retention in this case.

Finally, if someone or some process does DDL on your table (such as TRUNCATE, DROP, or adding/dropping columns/partitions) while expdp is runnning, then the expdp will fail on this object with ORA-01466: unable to read data - table definition has changed.  Fortunately this doesn't happen too often, but I have seen it with ETL workloads.


Any disadvantage to automatically changing FULL backup on AlwaysON Secondary to COPY_ONLY? I've been playing around with Ola's maintenance scripts recently, and they are absolutely great. However, I was considering one scenario and was surprised when I found it wasn't catered for. Maybe I'm just missing something, so hopefully someone can help me out.

I would like to push out Ola's DB backup script to all of my instances, including those hosting AlwaysON groups. I know the script handles Primary/Secondary preferences, but if you set your backup preference to Secondary Only, then allow the script to run a normal FULL backup, the backup will not be completed because the Secondary only allows COPY_ONLY backups.
I've edited Ola's DB backup script to include a @CopyOnSecondary switch where if set to Y, the backup will automatically change the FULL backup on a Secondary to a FULL COPY_ONLY backup on the Secondary.

Besides being unaware that your backup is being performed as a COPY_ONLY backup instead of not being backed up at all, are there any caveats that anyone can think of if this switch is set to default Y or even if it's used at all?

Cheers.

EDIT: Listing answers
 - Taking only COPY_ONLY FULL backups will not allow for DIFF backups or log truncation via log backups.

Besides being unaware that your backup is being performed as a COPY_ONLY backup instead of not being backed up at all, are there any caveats that anyone can think of if this switch is set to default Y or even if it's used at all?

A COPY_ONLY full backup is still a full backup. The only difference between the two is the COPY_ONLY will not reset certain tracking bitmaps. This means your differential backups will eventually be the size of your database.

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server