Postgresql nested row_to_json not working This probably wouldn't be an issue if I were using postgresql 9.2, however I can't (yet) upgrade from 9.1, and I'm using the json data type extension for 9.1. I'm trying to achieve a json object like the following:

[
  { "type": "add"
    , "id":"285947086237736960"
    , "version" : 1
    , "lang":"en"
    ,"fields":  {
        "id" : "285947086237736960"
        , "created_at":"2013-01-01"
        , "content":"bla bla blah LOL ROFL"
    }
  }, ...
]

The nested "fields" attribute comes from a db table called "tweets". Both id's come from the same table. However, type, version, lang are always the same. I've looked at the suggestions from https://stackoverflow.com/questions/13227142/postgresql-9-2-row-to-json-with-nested-joins, which mentions using aliased subquery expressions. That didn't work at all, I still get the f1, f2, etc. The suggestions from Set names to attributes when creating JSON with row_to_json works for a single level, but I can't get it to work for the nested "fields" attribute.

As far as I can tell, this will only work with a with statement similar to:

with tweets(type, id) as (
    select 'add' as type, tweets.id from tweets
) select row_to_json(tweets) from tweets;

But, how do I get the nested "fields"? Any help is greatly appreciated!

What about:

with tweets(type, id) as (
    select 'add' as type, tweets.id from tweets
) select row_to_json(row(array_agg(tweets))) from tweets;

This is actually one of the really cool things about PostgreSQL, arrays, and complex types.  One can aggregate them and then convert.  You should get a structure which is effectively:

tweets[] (i.e. an array of tweet tuples) which then get should converted to JSON properly).

Now, if you need a more complex structure than this, you probably want to define types separately.  Something like:

CREATE TYPE tweet_set AS (
    set_id int,
    tweets tweet[],
    generated_at timestamp,
    ....
);

And then come up with a query to generate that, and then convert it to JSON.

One of the basic issues is that since row_to_json expects a record type, you have to pass in a record, not an anyarray.  You could also do:

with tweets(type, id) as (
    select 'add' as type, tweets.id from tweets
) select array_to_json(array_agg(tweets)) from tweets;


Upgrading SQL Server 2005 to 2016 I have a requirement to get rid of our old servers, however one of them includes a database server that is currently using SQL Server 2005.

We have a new server running with 2016 so I wanted to know the best way to upgrade. From what I have discovered is that ideally we would need 2008 as an interim step. However as we do not have 2008 and this cost more I have been looking at the possibility of doing a 2005 back up and then a 2016 restore.

I have tested this out with a very simple db and it seems to work. However I have read that I should change the compatibility level from 100 (use to be 90) to 130. My question is, is the necessary? After all some of these old db's are old legacy databases which require very little change. Is it really worth it?

So what I will not be able to use the fancy new ways of doing stuff, but as these are old does that matter? After all its not like it is needed otherwise we would not have created these db. I am yet to still test with a more complex db but wanted to make sure I am on the right path.

Since you are migrating to a new server, you don't need to bring SQL Server 2008 into the mix.

As stated by SQL Server expert Paul Randal, You can upgrade from any version 2005+ to any other version.  Paul states:

  There’s a persistent myth that you cannot upgrade a database to a
  version more that is more then two versions newer.

  It’s not true.

  You can backup+restore (safest way) or detach+attach (not a safe way)
  from SQL Server 2005 (or higher) to any other newer version. You
  just can’t do an in-place upgrade to the latest version from SQL
  Server 2005.

As for changing the compatibility level, I'd recommend not changing that for several weeks and let the dust settle from the upgrade.  After you're confident that everything is functioning properly from the upgrade, why not bump the compatibility level up to the maximum and monitor performance.  You might even see an improvement in performance.  However, compatibility levels from 120 (SQL Server 2014) and above bring the new cardinality estimator into play, which might present some performance problems.  If you want, you can be more selective in your testing of the new cardinality estimator by using OPTION (QUERYTRACEON 2312) on selected queries.  You can always reduce the level should problems arise.  Do some internet searches on sql server 2014 cardinality estimator issues to familiarize yourself with possible issues.

At some point in the future when you upgrade to a later release of SQL Server, you WILL have to eventually increase the compatibility level.

Finally, check out Upgrade SQL Server for a checklist of items to consider for upgrading SQL Server.

Referencing ALTER DATABASE (Transact-SQL) Compatibility Level:

  From an application perspective, the goal should still be to upgrade
  to the latest compatibility level at some point in time, in order to
  inherit some of the new features, as well as performance improvements
  done in the query optimizer space, but to do so in a controlled way.
  Use the lower compatibility level as a safer migration aid to work
  around version differences, in the behaviors that are controlled by
  the relevant compatibility level setting. For more details, including
  the recommended workflow for upgrading database compatibility level,
  see the Best Practices for upgrading Database Compatibility Level.


X5687 vs X5690 CPUs for a SQL 2008 R2 Server I have the choice between:

2 x X5687 (Intel® Xeon® Processor X5687 - QuadCore
(12M Cache, 3.60 GHz, 6.40 GT/s Intel® QPI)

or
2 x X5690 (Intel® Xeon® Processor X5690 - SixCore
(12M Cache, 3.46 GHz, 6.40 GT/s Intel® QPI)

for a new DB Server (SQL 2008 R2 64bit), my personal preference is X5690, are there reasons that would speak against going for a SixCore CPU vs. a QuadCore CPU ?

For 2008R2, I'd take the extra cores every time.

If the server might be upgraded to Denali/2012 on release you may need to factor in the switch from per socket to per core licensing. @MrDenny summarised this well in SQL Server 2012 Licensing Changes.


Database file size from sys.master_files and sys.dm_io_virtual_file_stats are different Im using query to find out database file size. I'm using system view and DMV, because i do want to find out both- actual file size and theoretical (in case of sparse files) file size.

Select
    DB_NAME(mf.database_id) AS [Database Name],
    mf.Name,
    mf.physical_name PhysicalName,
    cast(mf.size as bigint) * 8192 mfSize_bytes,
    fs.size_on_disk_bytes fsSize_bytes
From sys.master_files mf
    Left Join sys.dm_io_virtual_file_stats(DEFAULT, DEFAULT) fs
        On mf.database_id = fs.database_id and mf.file_id = fs.file_id
Order By DB_NAME(mf.database_id)

I am confused- converted both values to "bytes", comparing them. But on all instances i checked- temdb file size (from sys.master_files) is smaller than file size on disk (from sys.dm_io_virtual_file_stats).

In all other cases if there is difference, then that is correct (size on disk is actually smaller than file size- because they are sparse files).

What is the reason for this difference?

UPDATE:
When i am querying

Select is_sparse, * From sys.database_files

On SQL Server 2005 database snapshot, then column is_sparse = 0 (because it is showing properties of the files from original database, not snapshot file. So- is there bug in documentation?).

Just from looking at the query results on my local development instance, there are two differences that I see (there are probably more):

sys.master_files returns the "virtual" size of a file, while sys.dm_io_virtual_file_stats returns the on-disk size of the file. I only see different sizes for database snapshots, which use NTFS sparse files behind the scenes (and I did verify the allocated and on-disk sizes in Windows).
sys.dm_io_virtual_file_stats does not return sizes for databases that are offline, while sys.master_files does.

For the case of tempdb, sys.master_files contains the startup file sizes for tempdb. If you investigate sys.database_files in the context of tempdb, you'll see the current sizes.


Always On AG cluster with 2 servers that fail over to each other I have 2 servers. One is dedicated as an OLTP server and the other will be a DW server. The OLTP server will have our application DB on it and our DW will have a couple of databases on it used for reporting. I would like to cluster them and build an AG for our OLTP DB that will failover to the DW server and provide the DW with a read-only replica for reporting. By the same token I would like to build the 2 DW databases into an AG and have them failover to the OLTP instance strictly for fault tolerance even though they technically could be read only replicas there as well. Is this possible? I need fault tolerance, I need the read-only replica for reporting and I need some fault tolerance for the DW too and I only have 2 servers and Enterprise licensing for those 2 servers.  Thanks!

Yes, this is absolutely possible, and a great use of resources.

You would get your OLTP databases added to an Availability Group, and then add the second machine as your secondary replica. For both replicas be sure to allow reads from secondary.

Then do the same with your DW databases.

You will end up with two AGs, and will probably want to run with each as the primary on different servers, so be sure that you include monitoring to let you know when the AGs failover, and what AG is running as the primary on each server.


Duplicated SQL Server Execution Times For Dynamic Query I'm getting duplicated SQL Server Execution Times from SET STATISTICS IO,TIME ON with dynamic queries. Is this a normal behavior?

You get an output for each statement. The query in @sql is one statement that gives you the first output and calling exec is a statement of its own and outputs duration for all the statements in @sql.
So if you have two statements in @sql you get three outputs.
exec (N'select * from dbo.YourTable; select * from dbo.YourTable;');

Output with set statistics time on:
(74 rows affected)

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 164 ms.

(74 rows affected)

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 144 ms.

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 309 ms.


Remove nulls from TSQL connection string I have a bunch of queries, against a couple different databases, that I use to generate delimited reports. I'm trying to create some new reports against a MSSQL database and I'm having trouble replicating our other processes to get consistent results.

When I query against the other sources I get '' and 0 in place of nulls. When I query against MSSQL I get the nulls.

I'm using DSN strings => ODBC => remote database for some queries... and straight connection strings for other queries.

So against a DB2 database:

SELECT OrderID, CustomerName
FROM OrderTable LEFT JOIN CustomerTable on OrderCustomerID = CustomerID

Assume the CustomerTable doesn't have a Customer, so for OrderID 1, no customerID the result would be (output as a | delimted list):

0|Joe Schmoe
1|NULL
2|Jane Doe

When using DB2, I get "1|". When using MSSQL, I get "1|null".

Using ODBC, connection strings, server settings... how can I return "" or 0 on nulls to get the output to match our other processes?

I've tried a straight connection (no DSN/ODBC). I've tried different settings in ODBC. All three SQL drivers (SQL Server, SQL Native Client, SQL Server Native Client 10.0). All are coming back with NULL instead of "default" for the data type.

Update/Clarification
I'm working with an "in house" ETL program that pulls and transforms data from in-house databases for export to other institutions/customers mainly via delimited text. Previously all of the queries I've used, when hit with nulls, have simply output ""/0. MSSQL obviously doesn't do that, so I'm trying to mimic the other sources at the most generic level I can.

I understand how IsNull/Colesce works, and if need be, I will use these to get the job done. The obvious issue with using IsNull/Colesce is the added code, across what will be many queries. Added code means harder maintenance, more creation time, more points of failure, etc. If I can simply tell the connection level to replace all nulls, I would think it'd be much easier... but that makes the question Is it possible? and if so how? Either in the SQL2005 Server instance, or on the other side of the connection at the ODBC connection or connection string.

SQL Server always returns NULLs in this case. I know of no way to globally (through server settings or connection strings) cause it to return empty strings or zeroes instead of NULLs. And I do not think it is a database library issue--using different drivers won't change a thing. This is just the way SQL Server behaves.

If you want to convert NULLs to something else, you'll need to explicitly use the IsNull(Expression, ValueIfNull) function or the similar Coalesce() function which takes multiple parameters and returns the first non-null value.

SELECT O.OrderID, IsNull(CustomerName, '') CustomerName
FROM
   dbo.OrderTable O
   LEFT JOIN dbo.CustomerTable C
      ON O.OrderCustomerID = C.CustomerID

Though one comment... why would there be an order with no customer? Wouldn't a customer with no order be more likely?

Update

Can you put in a wrapper that, client side, converts the NULLs to what you want? What process or program are you using to convert the result sets to text?


Setting up MariaDB Spider HA I try to setup High Availability using Spider Engine and MariaDB 10.0.14, but I a m not sure what configuration to use to make it work as expected.

What I want is:

Accessing a remote table using Spider Engine on a remote server A (let's say "main server" below)
If main server A is down => access "backup server" B

Based on the documentation, I set up the following configuration:

CREATE SERVER server_main
 FOREIGN DATA WRAPPER mysql
OPTIONS(
 HOST '10.2.0.1',
 PORT 3306,
 DATABASE 'db01',
 USER 'spider',
 PASSWORD '123456'
);

CREATE SERVER server_backup
 FOREIGN DATA WRAPPER mysql
OPTIONS(
 HOST '10.2.0.2',
 PORT 3306,
 DATABASE 'db01',
 USER 'spider',
 PASSWORD '123456'
);

INSERT INTO mysql.spider_link_mon_servers
(db_name, table_name, link_id, sid, server)
VALUES
('%', '%', '%', 100, 'server_main'),
('%', '%', '%', 101, 'server_backup');

SELECT spider_flush_table_mon_cache();

-- Created on 10.2.0.1 (server_main)
CREATE TABLE `np` (
  `s` text
) ENGINE=InnoDB;
INSERT INTO np VALUES ('main');

-- Created on 10.2.0.2 (server_backup)
CREATE TABLE `np` (
  `s` text
) ENGINE=InnoDB;
INSERT INTO np VALUES ('backup');

CREATE TABLE `np` (
  `s` text
) ENGINE=SPIDER COMMENT='wrapper "mysql", srv "server_main server_backup", database "db01", table "np", mbk "2", mkd "2", msi "100 101", link_status "0 0"';

Half the time spider uses server_main and server_backup:

db_spider =# SELECT * FROM np;
+------+
| s    |
+------+
| main |
+------+
1 row in set (0.00 sec)

db_spider =# Bye
[...]
db_spider =# SELECT * FROM np;
+--------+
| s      |
+--------+
| backup |
+--------+
1 row in set (0.00 sec)

But if I stop main mysqld instance, it still try to access the failed node:

db_spider =# SELECT * FROM np;
ERROR 1032 (HY000): Can't find record in 'spider_tables'
db_spider =# Bye
[...]
db_spider =# SELECT * FROM np;
+--------+
| s      |
+--------+
| backup |
+--------+
1 row in set (0.00 sec)

Maybe I misconfigured or missed some settings? Options "msi" and "link_status" are not very clear for me in fact...

I found my mistake. I realized that spider_link_mon_servers table must contain spider nodes, not backend nodes. And my spider nodes are not running on the same instance as backends. Spider tables run on port 3307.

So I need to configure monitoring servers like this (let's call them "proxy"):

CREATE SERVER server_main_proxy
 FOREIGN DATA WRAPPER mysql
OPTIONS(
 HOST '10.2.0.1',
 PORT 3307,
 DATABASE 'db01',
 USER 'spider',
 PASSWORD '123456'
);

CREATE SERVER server_backup_proxy
 FOREIGN DATA WRAPPER mysql
OPTIONS(
 HOST '10.2.0.2',
 PORT 3307,
 DATABASE 'db01',
 USER 'spider',
 PASSWORD '123456'
);

TRUNCATE mysql.spider_link_mon_servers;
INSERT INTO mysql.spider_link_mon_servers
(db_name, table_name, link_id, sid, server)
VALUES
('%', '%', '%', 100, 'server_main_proxy'),
('%', '%', '%', 101, 'server_backup_proxy');

SELECT spider_flush_table_mon_cache();

Now, if I stop "main" instance (3306 one), spider detects it and access only backup node.

There is still one feature I have not been able to operate: use only main backend if it is alive, and use backup backend only if the main backend dies...

EDIT 11/02/2014 :

The parameter "alc" (active_link_count) set to 1 is what I need in order to use second backend as a failover backend only. So something like this did the trick:

ALTER TABLE np ENGINE=SPIDER COMMENT='wrapper "mysql", srv "server_main server_backup", database "db01", table "np", mbk "2", mkd "2", alc "1", msi "100 101", link_status "0 0"';


Will SQL 2008 SSIS package files work with SQL 2016 databases after database upgrades? We plan to upgrade SQL 2008R2 to SQL 2016. We want to upgrade database servers first. We have separate SQL 2008R2 servers for SSIS and SSRS. SSIS packages are stored as files.

My question is:

After upgrading all database servers to SQL 2016, will all the SQL 2008R2 SSIS and SSRS servers work with the SQL 2016 databases?

Appreciate your experience.

At our location, we have SSIS running on SQL Server 2012. Our databases are on SQL Server 2016. We haven't had any issues with any of them, but then again, it really depends on what your packages are doing.

My recommendation is to have some sort of "sandbox" server and restore one of the databases and change the compatibility level to the latest level. Then test the SSIS packages to see if they would work.


Moving MongoDB shards from one server to another I have a new server on which i have to copy the old version of the MongoDB already configured. On the old server(3.0.4), there are two shards: one shard has 350GB, and the other has 300GB of data.

I followed the following steps:

Installed MongoDB 3.0.15 on new server
Copied the data from two shards to the new server
then ran the following commands

mongod    --configsvr --dbpath C:\mongoConfigData/configdb1 --port 27019
mongod    --configsvr --dbpath C:\mongoConfigData/configdb2 --port 27020
mongod    --configsvr --dbpath C:\mongoConfigData/configdb3 --port 27021

mongos    --configdb 127.0.0.1:27019,127.0.0.1:27020,127.0.0.1:27021 --port 27017

mongod    --dbpath F:\data --storageEngine wiredTiger --wiredTigerJournalCompressor zlib --wiredTigerCollectionBlockCompressor zlib --port 27010

mongod    --dbpath D:\data --storageEngine wiredTiger --wiredTigerJournalCompressor zlib --wiredTigerCollectionBlockCompressor zlib --port 27011

Added the shard and the shard collection details

sh.addShard("localhost:27010")
sh.addShard("localhost:27011")
sh.shardCollection("log", {"id" : 1, "eventId": 1})

Shard distribution shows both the shards but the total docs and the collection size does not match the one in the initial server.
Further the content in the mongodb is not the complete data
Why is it so? What am i doing wrong?

You are not copying the contents of the config servers and effectively creating a new sharded deployment rather than restoring a backup of an existing one. Config servers include essential information about where data lives in a sharded deployment, so you must backup & restore the config server data along with the shards.

Note: there have been changes to sharded cluster configuration in successive releases, so make sure you are using documentation for the correct MongoDB release series. The links below are specific to MongoDB 3.0.

For supported backup approaches see the MongoDB 3.0 Backup & Restore a Sharded Cluster tutorials.

  sh.addShard("localhost:27010")

  sh.addShard("localhost:27011")

If you've restored a sharded cluster from a backup, the shards will already be configured. If host names have changed from the original deployment, you will need to update the config.shards information  via a mongos rather than adding new shards (and then restart the mongod instances in your sharded cluster to ensure the new names are used). See the MongoDB 3.0 Restore a Sharded Cluster tutorial for more specific details.

  sh.shardCollection("log", {"id" : 1, "eventId": 1})

If a sharded cluster is already configured,  sharding an existing sharded collection will return an error. Since you ran this command starting from fresh config server data, the sh.shardCollection() command will be unaware of how existing data is distributed on the shards and assume that the primary shard for a database with an unsharded collection has the full range of data.


Backup very large table I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We do not have enough DB free space in order to store the full backed-up table.

Which is the best way? Is there a way to do this by blocks? I mean something like: backing up the first 100K rows from the original table, updating those 100K rows in the original table, deleting those 100K rows from the backed-up table, backing up the following 100K rows from the original table, and proceeding analogously. Is this feasible?

Two thoughts come to mind.

If you are concerned that this update may not properly affect the table the way you think, have you thought about putting the update inside a transaction.
You could do the update, query the data, and if it all looks okay, Commit the Transaction.  If it fails, you could perform a Rollback.

Alternative

You may want to look at the BCP utility to extract the table to a flat file outside of the SQL Server.

Presumably, you could store the contents of the table in a location where you aren't under as much storage pressure.  If the update process fails, you could attempt to restore the contents back into your table.


PostgreSQL extending UPDATE query I have the following update query to populate a table storing user stats

users:

int (pk) user_id

date     last_visit

int (fk) local_site

sites:

int (pk) site_id

site_stats:

int (fk) site_id

int total_users

int monthly_users

int weekly_users

To populate the 'total_users' column I am using the following query...

UPDATE site_stats
SET
    total_users = totalUsers,
FROM
(
    SELECT
        u.local_site AS site,
        count (u.*) AS totalUsers,
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site

The query above works ok, I would like to now extend it to update the monthly_users and weekly_users

Is it possible to do in the same query?

I was initially thinking of using a window function but after reading the docs I am not so sure its possible, eg.

UPDATE site_stats
SET
    total_users = totalUsers,
    monthly_users = monthlyUsers,
    weekly_users = weeklyUsers
FROM
(
    SELECT
        u.local_site AS site,
        count (u.*) AS totalUsers,
        count(u.*) OVER ( ** where u.last_visit > now()::DATE - 30 ** ) AS monthlyUsers,
        count(u.*) OVER ( ** where u.last_visit > now()::DATE - 7 ** ) AS weeklyUsers
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site

Or would a subquery be more appropriate?

You don't need window functions:

UPDATE site_stats
SET
    total_users = totalUsers,
    monthly_users = monthlyUsers,
    weekly_users = weeklyUsers
FROM
(
    SELECT
        u.local_site AS site,
        count (u.*) AS totalUsers,
        count(CASE WHEN u.last_visit >= now()::DATE - 30 THEN 1 END) AS monthlyUsers,
        count(CASE WHEN u.last_visit >= now()::DATE - 7 THEN 1 END) AS weeklyUsers
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site ;

Please check if you want >= or > in the conditions. It's better (in my opinion) to have date and time checks as ">= start_date" and "< end_date", in other words working with closed-open datetime intervals.


SQL Server instance running out of worker threads I have a SQL Server 2008 R2 SP3 Standard Edition 64-bit instance on 8 cores (576 max worker threads) with 32 GB RAM (MaxMem = 28000). It is the data store for a SharePoint installation, with 218 databases.

It was getting dozens of "SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection." errors per day, but no other errors. I found that MAXDOP = 0, which is bad for SharePoint. I gradually (over weeks) brought MAXDOP down to 1. As I did so, the frequency of those errors went down to zero on most days. But I still see them once in a while.

sys.dm_os_wait_stats has this to say about THREADPOOL waits:

waiting_tasks_count wait_time_ms    max_wait_time_ms    signal_wait_time_ms
26149               474516          4428                9

The server was last restarted at Mar 25 2018 5:55PM and current server time is Apr 20 2018 10:07PM. sp_Blitz finds nothing interesting other than use of join and order hints and slow storage writes on the drives holding tempdb files.

This is on a VM in a private cloud. Increasing the number of CPUs will be very expensive, and while it is heavily used, CPU usage doesn't seem to be a problem. In this case, would increasing the max worker threads be a reasonable thing to try, should I just leave it alone and live with the occasional 17189 error, or is there another option?

Increasing MWT can lead to increased SOS_SCHEDULER_YIELD waits. Not the end of the world, but think of it like adding a buncha kids to a teacher's class. All of a sudden, it's gonna be harder for each kid to get attention.

When a process exhausts its 4ms quantum, there will potentially be more threads ahead of it waiting to get on the CPU. It's really difficult to say if performance will be worse with that trade off.

You could try increasing MWT a incrementally.

This is how SQL Server manages it:

Notice how doubling core counts doesn't double MWT, and you get the same number with 1 core as you do with 4 cores?

It's like a sign, or something.

The equation is: 512 + ((logical CPUs - 4) * 16), which means at 10 cores you'd have 608 threads, and at 12 cores you'd have 640 threads.

Those are reasonably safe increments to try, but I wouldn't go over that without a support call with Microsoft.

Hope this helps!


Finding missing records from two tables I am trying to compare total number of records for a particular table in two databases. They are from the same database, but both the database are running in two different servers.

Table structure is same on both the tables , but no of records varies on both the tables.

I want to find out the missing records from both these tables.

mysql> select count(*) from dlfilerank;
+----------+
| count(*) |
+----------+
|    38259 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from dlfilerank;
+----------+
| count(*) |
+----------+
|    38359 |
+----------+
1 row in set (0.02 sec)

So, dump both tables into two files and then see the difference with diff:

 mysql -h host1 -NB -e "SELECT * FROM db.table ORDER BY id" > t1
 mysql -h host2 -NB -e "SELECT * FROM db.table ORDER BY id" > t2

 diff -u t1 t2


Oracle's Merge v. Select->Update/Insert What is the faster statement? A MERGE or Select->Update/Insert depending on if it returns a value?

I'm trying to optimize a database, but I haven't found any speed tests comparing the two.

I found the MERGE command through a StackOverflow answer

A MERGE would be more efficient since you're only executing the query once.  Even better would be to do a set-based MERGE rather than doing a bunch of single-row MERGE statements which is what I'm assuming you'd be implementing given the alternative of doing a SELECT to see if the row exists.

Rather than doing a SELECT and then determining whether to do an INSERT or an UPDATE, you would be better off doing the UPDATE, checking to see if you updated 0 rows or not, and then doing the INSERT if your UPDATE didn't affect any rows.  That way, source rows that require an UPDATE would be a bit more efficient and rows that require an INSERT would be essentially just as fast as if you had done a SELECT and INSERT.  This approach is still going to be slower than a MERGE, but it's better than doing the extra SELECT.


Model separate Person types as separate tables at beginning of application's lifetime? If I have a couple of distinct types of Person that will always have a set of disjoint fields, should I go ahead and model them with two different tables?

For example, say I'm setting up a system that connects Worker with Hiring Manager, where both are expected to be individual humans. These two different types of Person will never share members.

At the application layer, I don't expect either of these two types of people to log in, initially, but at some point in the application's life, they will have emails and passwords and so on.

What's the best approach at the beginning?

My gut feeling (which seems to be borne out somewhat by this question) is that you should go ahead and do different tables, since you can always migrate later to a schema where these different Persons all have the same kind of Account associated 1:1 (as suggested in this answer).


Do NT Service accounts need to be SysAdmin when using Domain Accounts I'm not sure if I'm googling for the wrong thing but I can't find an answer. We're running SQL 2014. The SQL Server and SQL Agent processes are both running under Domain Managed Service Accounts.

When SQL was installed NT SERVICE\MSSQLSERVER, NT SERVICE\SQLSERVERAGENT, NT SERVICE\SQLWriter, NT SERVICE\Winmgmt are all setup as SysAdmins.

If I am running SQL and SQL Agent as a Domain user, do I still need these NT Service accounts, and/or do they need to be SysAdmin?

If I am running SQL and SQL Agent as a Domain user, do I still need these NT Service accounts, and/or do they need to be SysAdmin?

Even if you are running SQL server as domain account, keep those NT SERVICE\* as is.

From BOL :

  Virtual accounts in Windows Server 2008 R2 and Windows 7 are managed local accounts that provide the following features to simplify service administration. The virtual account is auto-managed, and the virtual account can access the network in a domain environment. If the default value is used for the service accounts during SQL Server setup on Windows Server 2008 R2 or Windows 7, a virtual account using the instance name as the service name is used, in the format NT SERVICE\<SERVICENAME>

These logins are members of the sysadmin fixed server role, so they can do anything in the Database Engine. Keep them in SYSADMIN role even if you are using Domain account. See SQL Server Per-service SID Login and Privileges section.

A really good answer detailing above stuff - Service/Database Accounts - NT SERVICE\MSSQLSERVER & NT SERVICE\SQLSERVERAGENT … what are they for ?

  These accounts can't be selected in the list of available built-in accounts, local accounts or domain accounts. This is because they are services, not accounts. They have a security identifier (SID) in Windows, but Windows knows they aren't real users. Windows can authenticate them, but they don't have passwords that any human can use. If you run lusrmgr.msc and look at the groups, you will see groups like SQLServerMSSQLUser$computername$MSSQLSERVER and NT SERVICE\MSSQLSERVER is a member of the group.


How to take oracle 10g backup? Possible Duplicate:
  How to take oracle 10g backup?

I followed this http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php link to create a db backup. I used this command to create db backup:

expdp scott/tiger@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

but I have this error message:

UDE-00008: operation generated ORACLE error 1033

ORA-01033: ORACLE initialization or shutdown in progress

please help me to resolve this issue.

Try shutting down the database and then issuing the startup command.  Then try your script.

If you get the same error verify basic connectivity by connecting from sqlplus with the same name and password.

Check your log file location and the any operating system error logs as well.


Insert command not showing schema details I need to back up a table from a database schema.The table is located under kdb28_test_h and a schema named kref. I tried with the following code

pg_dump --host=110.11.12.16 --port=5432 --username=db_root --password --column-inserts \n
    --table=kref.abc kdb28_test_h > test_h_popl_back_up.sql

but insert command is generating like

insert into abc(----);

but I need

insert into kref.abc(------)

That's fine because your dump should have the following:

SET search_path = kref, pg_catalog;

and then table definition followed by insert statements


How do I set up primary and foreign keys referencing two different tables? I need to have two primary keys also to be the foreign keys in the same table. These two keys are referenced in different tables. I keep getting error messages that are unclear to me. My code is as follows:

CREATE TABLE ASSIGNMENT (
        ProjectID       INT             NOT NULL,
        EmployeeNumber  INT             NOT NULL,
        HoursWorked     NUMERIC(6,2)    NULL,
CONSTRAINT  AssignmentPK            PRIMARY KEY(ProjectID,EmployeeNumber),

CONSTRAINT  AssignmentFK            FOREIGN KEY(EmployeeNumber, ProjectID)
        REFERENCES  EMPLOYEE(EmployeeNumber)
            ON UPDATE NO ACTION
            ON DELETE NO ACTION,

    REFERENCES  PROJECT(ProjectID)
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);

The current error message is:

  Msg 142, Level 15, State 2, Line 2
  Incorrect syntax for definition of the 'TABLE' constraint.

Please advise.

The actual error message you are getting is because your CREATE TABLE statement is malformed as you cannot have a foreign key refer to more than one table. However you can still have the structure that you require by having two separate foreign keys. You can only use a composite foreign key if all columns that will be added to it belong to the same key in the target table.

CREATE TABLE ASSIGNMENT (
    ProjectID       INT             NOT NULL,
    EmployeeNumber  INT             NOT NULL,
    HoursWorked     NUMERIC(6,2)    NULL,

CONSTRAINT AssignmentPK PRIMARY KEY (ProjectID, EmployeeNumber),
CONSTRAINT AssignmentFK_Project FOREIGN KEY (ProjectID)
  REFERENCES PROJECT(ProjectID)
   ON UPDATE NO ACTION
   ON DELETE CASCADE,
CONSTRAINT AssignmentFK_Employee FOREIGN KEY (EmployeeNumber)
  REFERENCES EMPLOYEE(EmployeeNumber)
   ON UPDATE NO ACTION
   ON DELETE NO ACTION
);


Detecting SQL Server Utilization with a query My current project will send queries to an sql server constantly and It may use 100% of the memory or CPU.

How can I check if the server is nearing full utilization in a stored procedure so that I can decide whether to execute the queries or not or save some settings in a table so that the following queries can know the workload is high and decide what to do
If not, how can I prevent SQL server to reach full utilization?

More information about the case:
Right now I know our current test server can process 40-50 queries per second (one specific stored procedure). And now we'll decide how many queries are sent to the server every second. If we set the amount even 1 higher than the expected, in the long run, the queries will eventually fill the virtual memory and out client will have to restart their sql server instance periodically.

Expected Results (For bounty hunters):

@memory_usage float, @cpu_usage float; /* in percentage */

Any ideas are welcomed. Thanks.

If you really want to limit SQL Server memory, look at the Maximum server memory option.

Getting memory usage is possible, but it depends on what you really want.  Do you want to see memory usage as a percent of the "Maximum server memory" option?  If so, look at sys.dm_os_process_memory:

select convert(float, memory_utilization_percentage) / 100 as memory_usage
from sys.dm_os_process_memory

If you want physical memory use vs. total system memory, look in both sys.dm_os_process_memory and sys.dm_os_sys_info:

select * from sys.dm_os_sys_info

declare @physicalMemoryInUseKB bigint
declare @totalSystemMemoryBytes bigint

select @physicalMemoryInUseKB = physical_memory_in_use_kb from sys.dm_os_process_memory
select @totalSystemMemoryBytes = physical_memory_in_bytes from sys.dm_os_sys_info
select convert(float, @physicalMemoryInUseKB) * 1024
       / convert(float, @totalSystemMemoryBytes) as memory_usage

I don't think you can get the CPU usage unless you have the Resource Governor enabled.  If you do, look at sys.dm_os_performance_counters:

select
    case CPUUsageBase
        when 0 then 0
        else convert(float, CPUUsage) / convert(float, CPUUsageBase)
    end as 'cpu_usage'
from
(select cntr_value as [CPUUsage]
from sys.dm_os_performance_counters
where counter_name like 'CPU usage%'
and object_name like '%Workload Group Stats%'
and cntr_type = 537003264) Usage
cross join
(select cntr_value as [CPUUsageBase]
 from sys.dm_os_performance_counters
 where counter_name like 'CPU usage %'
 and object_name like '%Workload Group Stats%'
 and cntr_type = 1073939712) UsageBase


Oracle: Does enabling a PK rebuild its index? Scenario:

Table MY_TABLE has primary key constraint PK_MY_TABLE
Table MY_TABLE has also an index called IDX_PK_MY_TABLE which is enforces the uniqueness for constraint PK_MY_TABLE.

If I disable constraint PK_MY_TABLE and then enabled it back, does index IDX_PK_MY_TABLE get rebuilt?

It's easily tested.

There are two possible scenarios.

Scenario 1:

Scenario 1 is creation of a PK using an existing index:

SQL> create table MY_TABLE
  2  (
  3  pk number
  4  );

Table created.

SQL>

Add the index to it:

SQL> create unique index IDX_PK_MY_TABLE on MY_TABLE (pk);

Index created.

SQL>

Add the constraint:

SQL> alter table MY_TABLE add constraint PK_MY_TABLE primary key (pk) USING INDE
X  IDX_PK_MY_TABLE;

Table altered.

SQL> select constraint_name from user_constraints;

CONSTRAINT_NAME
------------------------------
PK_MY_TABLE

SQL>
SQL>
SQL> select index_name
  2  from user_indexes;

INDEX_NAME
------------------------------
IDX_PK_MY_TABLE

Insert some data:

SQL> insert into MY_TABLE values(1);

1 row created.

SQL> insert into MY_TABLE values(2);

1 row created.

SQL>

Test the constraint:

SQL> insert into MY_TABLE values(2);
insert into MY_TABLE values(2)
*
ERROR at line 1:
ORA-00001: unique constraint (PHIL.PK_MY_TABLE) violated

SQL>

Disable the constraint:

SQL>
SQL> alter table MY_TABLE disable constraint PK_MY_TABLE;

Table altered.

SQL>

Try the INSERT again:

SQL> insert into MY_TABLE values(2);
insert into MY_TABLE values(2)
*
ERROR at line 1:
ORA-00001: unique constraint (PHIL.IDX_PK_MY_TABLE) violated

It succeeds because the unique index hasn't been disabled/dropped when the constraint was disabled. Note that the original error was     ORA-00001: unique constraint (PHIL.PK_MY_TABLE) violated, whereas the index constraint PHIL.IDX_PK_MY_TABLE is violated once the PK constraint is disabled.

Scenario 2:

Scenario 2 is creation of the PK upon table creation:

SQL> create table MY_TABLE
  2  (
  3  pk number,
  4  constraint PK_MY_TABLE PRIMARY KEY (pk)
  5  );

Table created.

SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------
PK_MY_TABLE

SQL> select constraint_name from user_constraints;

CONSTRAINT_NAME
------------------------------
PK_MY_TABLE

SQL>

Disable the constraint:

SQL> alter table MY_TABLE disable constraint PK_MY_TABLE;

Table altered.

SQL> select constraint_name, status from user_constraints;

CONSTRAINT_NAME                STATUS
------------------------------ --------
PK_MY_TABLE                    DISABLED

SQL>

The index has gone!:

SQL>  select index_name from user_indexes;

no rows selected

SQL>

Re-enabling it recreates the index:

SQL> alter table MY_TABLE enable constraint PK_MY_TABLE;

Table altered.

SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------
PK_MY_TABLE

SQL>

If you want to keep the index:

SQL> alter table MY_TABLE disable constraint PK_MY_TABLE  keep index;

Table altered.

SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------
PK_MY_TABLE

SQL>


Why does this MySQL query take 25+ seconds? The query is:

SELECT T.ID, T.A, T.T
FROM  table1 AS T
WHERE (userID='333335') AND (category LIKE '1%') AND (T.T='You Ain\'t Much
Fun' AND T.A='KEITH, TOBY') GROUP BY T.A, T.T

I have a BTREE index over 2 columns - userID, and another column which doesn't feature in the above query. The index is set up with userID being the first column.

The table is innoDB, and the total table size is around 70 million, but the rows that userID='333335' equals total only about 500,000.

If userID has an index on it, and that's the first column queried, shouldn't the rest of the query be able to breeze through 500,000 rows pretty quickly?

What is it that I'm overlooking please?

Thank you for your time and help.

Your index may be finding the records relatively quickly, but it has to perform a double lookup for every single one of those rows that it finds for that userId.

Try adding the following index:

(userId, A, T, category)

to table1.

This will be a covering index, and should yield extremely quick results.


PostgreSQL - Out of memory on count group by query I'm trying to execute the following statement in PostgreSQL database:

create table tmp_nodes as select node_id,count(way_id) as count
from way_nodes
group by node_id;

This query fails because the machine runs out of memory. (Unfortunately, I can't find the corresponding error in the log files; however, I can see memory usage steadily increasing and that the process terminates with not results.) This is probably because the result table is kept in memory and is written to disk after the query has been executed, count with group by. Note, the number of rows in way_nodes is about 2.5 billion:

EXPLAIN create table tmp_nodes as select node_id,count(way_id) as count
from way_nodes
group by node_id;
                                    QUERY PLAN
--------------------------------------------------------------------------------
 HashAggregate  (cost=53797289.60..54287411.12 rows=49012152 width=16)
    ->  Seq Scan on way_nodes  (cost=0.00..41209846.40 rows=2517488640 width=16)
(2 rows)

I was wondering if there is a good approach to get the query to be executed. The options I have thought of and I did some research about are:

Is there a query parameter, or something, that forces the query to write the result earlier to the disk, meaning to not keep everything in memory?
I found cursors as a way to do the same thing as (1), but manually. However, I am not sure how to do that. (If you think this is a good approach, can you show me some example code, either a link or in your response, please?)
Thanks to TomH's response: Saying 'set enable_hashagg = false;' gives a query plan as shown below. Does this force the query to be executed in pipelined mode. (Is there any hint in query plan?) If so, this solves the problem.

Query plan for (3):

explain create table tmp_nodes as select node_id,count(way_id) as count
from way_nodes
group by node_id;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 GroupAggregate  (cost=477330853.69..496702140.01 rows=49012152 width=16)
   ->  Sort  (cost=477330853.69..483624575.29 rows=2517488640 width=16)
         Sort Key: node_id
         ->  Seq Scan on way_nodes  (cost=0.00..41209846.40 rows=2517488640 width=16)
(4 rows)

Well you can use set enable_hashagg = false to disable the HashAggregate plan type, which should force it so choose an alternate plan.

You should probably also make sure you have analyzed the table, as it may that it is a lack of statistics that is stopping postgres realising it won't have enough memory for the result, which is going to be pretty massive.


Oracle Min - ORA-00937 not a single-group group function I try to find companies whose fare has minimum fare of destination which more than one companies use that destination. My command;

SQL> select tickets.bus_id bus, tickets.fare fare, route.destination dest
     from tickets left join route on route.route_id=tickets.route_id;

       BUS       FARE DEST
---------- ---------- -------------------------
        11      21.36 Kayseri
         1      23.32 Kayseri
         1      63.32 Kars
         5      44.31 Edirne
         1      54.13 Edirne
         1      33.36 Fatsa
         4      33.37 Ankara
         4      33.39 Kayseri
         7      53.37 Istanbul
        10      51.36 Izmir
         7      51.36 Izmir
        10      32.35 Ankara
        10      42.34 Istanbul
        10      52.33 Hatay
        10      32.83 Bursa
         2      30.00 Izmir
        11      52.00 Kayseri
        11      42.00 Kayseri
         2      40.00 Kayseri

Output I want:

       BUS       FARE  DEST
---------- ---------- -------------------------
        10      32.83 Bursa
         2      30.00 Izmir

select bus, min(fare), dest from
     (select tickets.bus_id bus, tickets.fare fare, route.destination dest
     from tickets left join route on route.route_id=tickets.route_id)
group by dest having count(bus)>1;

>ERROR at line 1: ORA-00937: not a single-group group function

When I try to group dest, It gives error. I can't understand what is mistake? Has anyone idea to fix it?

--- EDIT TABLES ---

CREATE TABLE buscompanies (
      bus_id  number(4) not null PRIMARY KEY,
      name    varchar2(25) not null);

table created.

CREATE TABLE route (
     route_id number(4) not null PRIMARY KEY,
     origin   varchar2(25) not null,
     destination varchar2(25) not null);

table created.

CREATE TABLE tickets (
     tickets_id number(4) not null PRIMARY KEY,
     route_id number(4) not null,
     bus_id number(4) not null,
     fare   number(5,2) not null,
     CONSTRAINT routeid_fk FOREIGN KEY (route_id) REFERENCES route(route_id),
     CONSTRAINT busid_fk FOREIGN KEY (bus_id) REFERENCES buscompanies(bus_id));

SQL> select * from route;

  ROUTE_ID ORIGIN                    DESTINATION
---------- ------------------------- -------------------------
         1 Ankara                    Kayseri
         2 Ankara                    Kars
         3 Ankara                    Edirne
         4 Ankara                    Fatsa
         5 Denizli                   Ankara
         6 Denizli                   Kayseri
         7 Hatay                     Istanbul
         8 Hatay                     Izmir
         9 Izmir                     Ankara
        10 Izmir                     Istanbul
        11 Izmir                     Hatay
        12 Izmir                     Bursa
        13 Bursa                     Izmir
        14 Bursa                     Kayseri

14 rows selected.

--EDIT--

I used this command but that shows only for 1 city

select bus, min(fare), dest from
     (select tickets.bus_id bus, tickets.fare fare, route.destination dest
     from tickets left join route on route.route_id=tickets.route_id)
     group by bus, dest having count(dest) > 1;

       BUS  MIN(FARE) DEST
---------- ---------- -------------------------
        11      21.36 Kayseri

there should be other destinations too. Question was;

  find cheapest company for each city such that more than 1 company
  travels to the city?

You want to aggregate an aggregate, so the easiest way to see this is to group twice, I believe. You may also want to rename the subquery table aliases I've used here to make them more meaningful.

SELECT sub2_.bus, MIN(sub2_.fare), MIN(sub2_.dest)
  FROM (SELECT sub1_.bus, MIN(sub1_.fare) AS fare, sub1_.dest
          FROM (SELECT tickets.bus_id    AS bus,
                       tickets.fare      AS fare,
                       route.destination AS dest
                  FROM tickets
                  LEFT JOIN route
                    ON route.route_id = tickets.route_id) sub1_
         GROUP BY sub1_.bus, sub1_.dest
        HAVING COUNT(sub1_.bus) > 1) sub2_
 GROUP BY sub2_.bus
HAVING COUNT(*) > 1


ERROR: relation "codepool_codepoolseq_seq" does not exist SQL state: 42P01 Below is my script but I keep getting an error.

-- Table: public.codepool

-- DROP TABLE public.codepool;

CREATE TABLE public.codepool
(
    codepoolseq bigint NOT NULL DEFAULT nextval('codepool_codepoolseq_seq'::regclass),
    codenumber character(50) COLLATE pg_catalog."default",
    CONSTRAINT codepool_pkey PRIMARY KEY (codepoolseq)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.codepool
    OWNER to postgres;

The error I get is:

  ERROR: relation "codepool_codepoolseq_seq" does not exist SQL state: 42P01

In your Scheme you must have Sequences called codepool_codepoolseq_seq

Example create:

-- SEQUENCE: public.codepool_codepoolseq_seq

-- DROP SEQUENCE public.codepool_codepoolseq_seq;

CREATE SEQUENCE public.codepool_codepoolseq_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

ALTER SEQUENCE public.codepool_codepoolseq_seq
    OWNER TO postgres;


Is Overwriting the PFILE with the database on dangerous? Quick question that I cannot seem to find the answer to. I have some indexes that I want to rebuild at a scheduled interval, but they are fairly large and fill up the archive_log_dest_3 if I leave them unattended. So, I wrote a script that changes the dest_3 parameter in the pfile to a much larger mount point, and then changes it back afterwards, but the script overwrites the PFILE with an updated version. Would overwriting the PFILE while the database is on and doing these index rebuilds be dangerous or not recommended? Thanks!

Why do you need it? pfile is read only once when database starts.
Use spfile and alter system set parameter = value scope=memoryor alter system set parameter = value scope=both.


database structure - complicated requirements I have a project to build a website but it's complicated and I'm having trouble figuring out what the best way to build the database would be to handle these particular requirements.

The site is for a local builders and farmers (and anyone else who uses heavy equipment) to rent their machinery amongst themselves. Users should be able to sign up and list an item of equipment which is then searchable and bookable by other users of the site.

So a builder might sign-up and upload a listing for his concrete mixer. Then another user can search for concrete mixers to hire between 2 dates and place a booking for the mixer through the site.

So far so good.

Problem is that the builder should be able to set a default per-day rate but they should also be able to say that through-out the month of July, or on the last two weekends in August the mixers default daily rate is different. So basically everyday could end up having a different rate and I'm having trouble figuring out what is the most efficient way to structuring the database and how to calculate the total costs of renting for several days if there's potentially a different rate every day.

At the moment I'm imaging having to loop through a 365 sized array but that can't be right. I'm a bit new to this so I'm probably just confused.

Apart from the other tables you should have (like equipment, farmers ...), you can use this approach:

equipment_price
---------------
equipment_id
equipment_name
equipment_default_price

equipment_special_price
-----------------------
equipment_id
special_price_interval_start
special_price_interval_end
equipment_special_price

Now imagine that you have a concrete mixer, with a default price of 100$ per day, but for the first 2 weeks of August the equipment has different (special) price.

Your query for finding the correct price (as of TODAY) will look like:

SELECT
   a.farmer_name,
   b.equipment_name
   CASE
      WHEN d.equipment_special_price IS NULL THEN c.equipment_default_price
      WHEN d.equipment_special_price > c.equipment_default_price THEN c.quipment_default_price
      ELSE c.equipment_default_price
   END AS equipment_price
FROM
   farmers a,
   LEFT JOIN equipments b ON b.equipment_farmer_id = a.farmer_id
   LEFT JOIN equipment_price c ON c.equipment_id = b.equipment_id
   LEFT OUTER JOIN equipment_special_price d ON d.equipment_id = b.equipment_id
WHERE
   d.equipment_special_price = (   -- Usefull if you have multiple special prices, for the same equipment, in overlapping intervals
       SELECT MAX(equipment_special_price)
       FROM equipment_special_price
       WHERE
           special_price_interval_start >= NOW()
           AND
           special_price_interval_end <= NOW()
       )


enabling BINARY LOG on AWS RDS MySQL Context

I am trying to enable BINARY LOGs on a MySQL RDS instance.

Issue

I used CALL mysql.rds_set_configuration('binlog retention hours', 24) without encountering an error.

And using CALL mysql.rds_show_configuration says "binlog retention hours" value is 24

But SHOW BINARY LOGS says "#1381 - You are not using binary logging"

Am I missing something ?

Additional information :

using SHOW GRANTS results in GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP...
I tried rebooting the instance. It didn't work.
automatic backups are enabled with a 1 day retention

useful documents

Using a Amazon-Managed MySQL-Compatible Database as a Source for AWS DMS
AmazonRDS/latest/UserGuide/mysql_rds_set_configuration

I found this blog post that answered the question: https://blog.pythian.com/enabling-binary-logging-rds-read-replica/

Essentially, if you make sure your "Backup retention period" for your RDS instance is set to > 0 days, this will enable binary logs.


MongoDB mongos router and microservices architecture Any mongos process in your infrastructure can perform chunks migration while they hold the lock.

In a microservice architecture, processes are started and stopped more dynamically, i'm thinking for example in Kubernetes where you will restart Pods possibly frequently because there is no disruption.

How does that fit with killing mongos processes? If they are doing a chunk migration, what are the implications of that? In my cluster i see some 'Aborted' migrations (in sh.status()) and i believe they are cause by this.

How bad are those 'Aborted' migrations and how bad is killing mongos?

If a mongos is killed a running migration should still complete, there just won't be another balancing operation initiated until a new mongos is able to pick up the balancer lock. That lock may end up being held for a while (I am being purposely vague, just how long a stale lock sticks around is very hard to define).

Aborted chunks are usually an issue with the primaries (they do all the heavy lifting for a migration) rather than the mongos which is just doing the coordination. The most common cause of an abort is that there is already one happening, or there is cleanup happening because of previous migrations. If you want to see a full description of the whole process, have a look at this (~11 minute) video where I describe it in depth (for MongoDB University).

That video will also give you some idea as to what the steps are that you are seeing in the abort messages. If you want to debug the aborts further I will recommend that you get the relevant information about the aborts from the primaries involved, and the changelog so that we can get an idea of the step the abort happens, and what the view is from both sides of the migration.

Finally, in terms of recommendations as to how to deal with this, I would recommend running a mongos process somewhere more permanent which does not take actual traffic from your applications. You would basically run this in the same way you do a mongod - it could even share a host/vm/instance with a mongod. You can't specify which mongos takes a balancer lock but just by being the only permanent process it should have the advantage.


Calculating the percentage change from the previous row I just entered the world of window functions. I have a similar problem to solve as in this question.

I have this table:

The last column (%Grown) is given by:

-(1 - price of row 2 / price of row 1) * 100

All the information I have found so far was related to sums, avg for all rows. I don't know how to bound two rows so I can calculate the percentage.

How do I translate this into SQL?

The best way I think would be to use the LAG() or LEAD() functions:

SELECT *,
       - 100.0 * (1 - LEAD(Price) OVER (ORDER BY t.Id) / Price) AS Grown
FROM table_name AS t
ORDER BY t.Id ;

With LEAD(Price) OVER (ORDER BY t.Id) you have access to the next Price when the rows are ordered by Id. It's not clear what the order should be. Based on the data in the question, you may want (ORDER BY Price DESC).


Modelling a database for a travelling business that involves Vehicules, Routes, Subroutes and Prices I am working on travelling app, where users can book online vehicles to travel.

On the customer side I would like this flow:

Users have to select a from location and a to location
Enter how many seats they require
Select a travel date
Search for a ride/vehicle.

Which means, I need to search all the vehicles/rides whose available seats are equal or more than the user wants, and who has this route: from location - to location.

And on the merchant/vehicle owner side I would like this flow:

Owner/merchant selects a vehicle
Create a route by selecting a from location --> and a to location
Set price for that trip.

An owner can have many vehicles and each vehicle can take different routes and each route can have a different price. Also whether the route is back and forth.

However, please consider the example below:

A vehicle has a route from A to D and it has a price for that trip. i.e.,

A-----------------D     Price 100

But it can also have sub routes within that route. Example from A to B or B to C, e.t.c.

A-----B------------     Price 25

A-----------C------     Price 75

------B-----C------     Price 25

------B-----------D     Price 75

How can I design a database model for such a relationship between Vehicle and Routes, where a vehicle can have multiple routes and each route can have multiple sub routes so that I can search for all the vehicle routes? If it helps I am using Django. Thank you.

I did similar work to this many moons ago - I worked for an airline, but it's more or less the same thing!

A critical thing to get right from the start is terminology - yes, I know that you just want to start coding! :-) However, life will be much easier if everyone is singing off the same hymnsheet!

In the airline for which I worked, the word "flight" had three different meanings depending on whether you were speaking to an engineer, flight crew or sales people!

I would use the word (and table name) "leg" for a journey from one point to another where passengers can get on or off.

A "trip" can be many legs, normally, but not necessarily, a journey from point A to point B and back to point A "series" can be a number of trips over a season (don't know if this is your scenario, but it might help to have an over-arching framework and then factor in exceptions? You might have prices for legs and another price for a trip (normally lower than the sum of the legs)?

Firstly, on a technical note, I would advise you to use PostgreSQL (way before MySQL) for 5 main reasons (see below). If you want an embedded database, use Firebird which again has far more features (and has had them far longer) than MySQL.

1) it is rock-solid,

2) also has CTEs (i.e. WITH clause - also in MySQL 8 - but that is alpha)

3) Analytic functions (again, in MySQL 8?)

4) JSON functions

5) a rich language (PL/pgsql) that is comparable to Oracle's.

Note that (for PostgreSQL at any rate), the DDL (Data
Definition Language) - the code that creates tables and
indexes &c... is a powerful programming tool in its own
right - it can save you lots of coding and error-checking
in your application code. In fairness, all of the
mainstream databases have similar functionality. PostgreSQL
however is excellent (the best IMHO) for standards compliance!

My philosophy is always to put as much logic in the database
as possible - development environments come and go, databases
and data (like diamonds :-) ) are forever!

p.s. you may wonder why my names for fields, constraints, indexes
&c. seem long and maybe to some, overly verbose. This is
because I have learnt through painful experience that calling
the auto-incrementing PRIMARY KEY field for every table
'id' is a recipe for disaster - as is letting the RDBMS
assign its own machine-generated names to database objects.

I would start by creating the following tables:

CREATE TABLE location
(
  location_id SERIAL CONSTRAINT location_pk PRIMARY KEY,

  -- I find that keeping CONSTRAINT declarations in the CREATE TABLE
  -- statement makes them easier to follow and is more elegant.
  -- Furthermore, I find that keeping the declarations of CONSTRAINTs
  -- as near to the relevant FIELD as possible also helpful.

  -- I *_always_* give meaningful names to my CONSTRAINTs - makes
  -- debugging *_much_* easier - here location_pk makes more sense than
  -- some machine-generated gibberish. Oracle generates particularly
  -- opaque names for keys and other constraints. Use the capabilities
  -- of the database to the maximum!

  -- PRIMARY KEY name is **_always_** (specified by the programmer as) table_name_pk, guaranteed to be UNIQUE in
  -- the entire system. I always have singular table names - they are the equivalent
  -- of class definitions in OO programming. **_Plus_**, you may well want tables
  -- with 1 or even 0 records (think system = Nuclear_Power_Plant - table =
  -- Catastrophic_meltdown! :-)

  location_name VARCHAR(30) NOT NULL CONSTRAINT location_name_uq UNIQUE

  -- You might want to add GPS coordinates?

);

Then, insert some (sample/real) data - note that
PostgreSQL takes care of the SERIAL column incrementing!

INSERT INTO location (location_name) VALUES ('One');
INSERT INTO location (location_name) VALUES ('Two');
INSERT INTO location (location_name) VALUES ('Three');
INSERT INTO location (location_name) VALUES ('Four');

Now, create another table leg (more complex this time). The
leg table is just a list of departure points, arrival points,
departure points and amounts. You will have to determine if your
system has to allow for simple "there and back" runs (over several
locations) or more complex routes (i.e. trips).

CREATE TABLE leg
(
  leg_id SERIAL CONSTRAINT leg_pk PRIMARY KEY,

  leg_location_dep INTEGER,  -- is an FK field - no need to specify NON NULL!
  leg_location_arr INTEGER,
  leg_amount INTEGER NOT NULL CHECK (leg_amount > 0),

  -- ADD FKs to location table.

  CONSTRAINT leg_location_dep_FK FOREIGN KEY (leg_location_dep) REFERENCES location (location_id),
  CONSTRAINT leg_location_arr_FK FOREIGN KEY (leg_location_arr) REFERENCES location (location_id),

  -- Your app will *_never_* have to check that a fare is not quoted from one place back
  -- to the same place. If you are going to do return fares, then I would combine trips
  -- in some way.

  CONSTRAINT leg_dep_ne_arr_ck CHECK (leg_location_dep <> leg_location_arr),

  -- This last constraint ensures that you can never have two
  -- different fares for the same leg - this *_may_* or *_may_*
  -- suit your requirements - what you can see though, is that
  -- with intelligent use of the `CREATE TABLE` statement, you
  -- can already put a *_lot_* of logic into your database without
  -- writing a single bit of client code! You may also want to
  -- different fares for different times of day, week, year.

  CONSTRAINT leg_dep_arr_uq UNIQUE (leg_location_dep, leg_location_arr)

  );

CREATE INDEX leg_dep_idx ON fare (leg_location_dep);
CREATE INDEX leg_arr_idx ON fare (leg_location_arr);

The construction of INDEXes is a science in its own right
and any indexing strategy will depend on the queries that
you write against your tables - again, meaningful names
are very helpful for debugging.

I didn't put in data for the fare table, and the trip, vehicle
and owner tables are only outlines - but you should be able to
make a start with what I've shown here - you can drop back if you
require further assistance/information.

CREATE TABLE trip  -- not all work done!
(
  trip_id SERIAL,
  trip_location_dep INTEGER, -- references location table
  trip_location_arr INTEGER, -- references location table
  trip_date DATE NOT NULL,
  trip_time TIME NOT NULL,
  trip_fare INTEGER -- same checks as for fare.
);

What you want are JOINING tables - also called Associative
entities or simply many-to-many tables!

You could have one, for example, between trip and leg?

Call it trip_leg and have trip_id and leg_id as the
two fields - you might also want an order field i.e.
what might be A->B->C->D->E on one route, might be
A->D->C->E on another - one-way streets are another
issue - the vehicle can only be going only one way,
despite being on different trips/routes.

1 trip can have many legs and 1 leg can be on many trips, for
example, if you have a number of locations around a city, you
may have partially overlapping routes depending on your setup.

Some of your vehicles might be more
suitable for longer routes - a large bus for example, while some
(tuk-tuks or smaller taxis) might be more locally based?

Many choices in designing your system will depend on your
particular needs! Check out this page and that site
in general - you can see what others have done when faced with
a similar situation and you can customise them according to your
needs!

CREATE TABLE vehicle
(
  vehicle_id SERIAL,
  vehicle_owner INTEGER, -- references owner table (not shown)
  vehicle_driver INTEGER, -- references driver table (not shown) - may want different drivers even for same vehicle?
  vehicle_capacity
);

CREATE TABLE vehicle_owner
(
  owner_id SERIAL,
  owner_name VARCHAR(30),
  .. other fields
  ..
);

HTH, and best of luck.


SSIS Package Error when called from SQL Agent Job We have SSIS running on its own server under account 'ourserviceaccount'.

We then have SQL Agent running on a separate server under the same account.

I have set up a job that calls an SSIS package, which in turn calls a number of child packages.
However, I get this error...

  Error 0xC0202009 while preparing to load the package. SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code:
  0x%1!8.8X!.

The job is run as the same service account credentials.

In the parent package that calls the child packages, I have set the connection manager to Windows Authentication.

What step am I missing?

Thanks!

I had similar problem earlier and what I found is because the creator of the Package and executor of Package were different user it was having some issues.

To get around this issue, I had to protect the package with password and I used that password to access the package in my Job.

You should give that a try, I hope it helps.

Also, is your package doing any distributed transactions? You may need to set authentication from Services for distributed transaction.


Not Equal Operation is not working I have two tables

1st Table : center (2 columns)

+-----------------------------------+
|id | company_name                  |
+-----------------------------------+
|2  | Skytel Outsourcing Pvt. Ltd.  |
+-----------------------------------+

2nd Table : campaign_center (3 columns)

+-------------------------------------+
|campaign_id |  Center_id  | sl_no    |
+-------------------------------------+
|Demo        |    2        | 2        |
+-------------------------------------+
|MyFootPath  |    2        | 3        |
+-------------------------------------+

When I execute this query it's working perfectly: (To find center_id and company name who's campaignid is 'Demo')

select center.id, center.company_name
from center
  inner join campaign_center
  on center.id = campaign_center.center_id
where campaign_center.campaign_id = 'Demo'

but again when i am trying to find those company who's campaign_id is not 'Demo' it doesn't give correct output:

select center.id, center.company_name
from center
  inner join campaign_center
  on center.id = campaign_center.center_id
where campaign_center.campaign_id != 'Demo'

How do I get those ? Please help.

Your second query will give you all companies that have (one or more) campaign centers with id different than 'Demo'. But those that have a 'Demo' and some other campaign id will still be included.

I guess that you want something different. To find those companies that none of their campaign_id is 'Demo'. If so, you need an anti-semijoin (often called anti-join) which can be accomplished with either NOT IN or a LEFT JOIN / IS NULL or a NOT EXISTS query:

SELECT c.id, c.company_name
FROM center AS c
WHERE NOT EXISTS
      ( SELECT 1
        FROM campaign_center AS cc
        WHERE c.id = cc.center_id
          AND cc.campaign_id = 'Demo'
      ) ;


What adverse effects does a table have when it's not even in 1nf? What adverse effects would a table have if it's not even in 1nf ?

I googled and read the article on Wikipedia but I couldn't find exactly why a table should be in 1nf.
Also, I couldn't find what horrible things might happen if a table is not in 1nf.

So other than being able to use simple queries  , what monsters would crawl out of the tables while I sleep happily at home if my tables weren't in 1nf ?

No monsters will crawl out of the tables that are not in first normal form, however, you may turn into one when a simple change request requires major remodelling of your table structure. Let me go to this in further detail.

According to Wikipedia article on first normal form, it helps to

Eliminate repeating groups in individual tables.
Create a separate table for each set of related data
Identify each set of related data with a primary key

Now let's assume that Stack Exchange would have ignored this advice when designing their database back-end and have stored all their questions in one big table. When a tag gets added to a question a new row gets added to the question table. This all works fine until of the moderators has noticed that the SQL Server tag has been used inconsistently(SQL Server, Microsoft SQL, Microsoft SQ Server,etc.) and wants to clean this up.

In a normalised form this could be achieved by updating and deleting a handful of rows, however, without normalising you will need to update every single question that has been tagged incorrectly. This can result in locking of the database and freezing of the site. This means the moderator can't simply change the tags immediately using the moderator tools, and instead these changes would have to be queued until the site is less busy.

This normalised form would also have resulted in huge performance issues as the site grows in size. As each question can have multiple tags there would be many duplicate rows in your question table and it would get slower and slower to query.  You would, for example, quickly run into problems querying all available tags as you would have to query the entire question table instead of the tag table.


#1146 table 'db.tablename' doesnt exist I use AMPPS for localhost server purpose. I was working on one of my client site on localhost.

When I start working the other day, it suddenly start giving me such error

table does not exist Even though I can clearly see tables on left hand side bar as you can see in attached image.

I tried lots of ways mentioned on net. But I am unable to solve the issue. I don't know what exactly happened there.

Please help me out. Any help will do.

Thanks,
Mahesh

I know this question is a year old but thought I would post an answer considering there is no real answer to this that I have found. In this answer, for the remote server commands, I'm assuming you're using a Linux Debian-based OS. Otherwise there is another option at the end.

This sounds like an issue in the storage engine "ibdata1" file in your /var/lib/mysql/ folder. It can happen if the tables aren't copied correctly. So the storage engine can't present the database correctly even though the tables are there.

The best way to resolve this issue is to do a mysqldump on the healthy database on the remote server and restore the tables back into your local database server and vice versa.  So, if you have root or sudo access on your remote server and your mysql password you can use:

$ mysqldump -u root -p *database-name* > *sqlbackupname*.sql
$ Enter mysql password: *your-mysql-password*

This will dump your database into a .sql backup. Then to restore the database on your localhost you can run this in your linux terminal:

$ mysql -u root -p *database-you-want-to-restore-to* < *sqlbackupname*.sql

So you're going to need to have a database set up ready to accept the information into it and then the information in the mysqldump will be restored to that database. The mysqldump will look for a database to dump into.

If you DO NOT have server access and you're only using phpmyadmin, you'd be best exporting the healthy database from your remote server into a .sql file. So go to your healthy database, then click the "export" button at the top. That will give you some options and  download onto your computer. Then you can "import" the file back into your local install of phpmyadmin. This option should work on any operating system as long as you're using phpmyadmin.

I hope this works for someone who is searching for the answer to this issue.


Locking in stored procedures If a stored procedure has multiple statements, will the lock acquired for the first statement be released once its executed or will it hold on till entire stored procedure is complete?

I am running a stored procedure under transaction from Entity Framework. Invoking the same stored procedure multiple times simultaneously is causing deadlock. The stored procedure selects a bunch of records from a table and inserts back into same table and moves on to next table. For some tables, it makes join with previous table to get the list of records to be inserted.

The stored procedure doesn't have any transaction but the call from application routed via Entity Framework has a transaction

My understanding of this is that, yes, under a transaction your SP will hold locks for the duration of the transaction. (Note there are lots of types of locks - SQL Server could row lock, table lock, page lock etc)

One SP being called simultaneously (with no other activity on your DB) causing deadlocks suggests one thing to me: the SP is not locking the same things in the same order on each call. Do you have IF blocks that perform actions on a table if a condition is met? You may wish to rewrite those to make the IF condition appear in the WHERE condition instead. This will ensure the SP locks the same resources in the same order each time.


How to export data from multiple tables to csv from remote postgres server (AWS RDS) to local machine? I need to export data in CSV format from many (undertimined amount) tables from a remote postgres (aws RDS PostgreSQL 10.6) server to a local machine.
When the server was in its own machine (AWS EC2) this was possible with a function that used a loop to iterate over the necesary tables then used execute and COPY commands to create the CSV files directly onto the same server from which they could by copied over where needed.
But since the server was migrated to an RDS instance, I can't use that approach anymore because I have no direct access to the filesystem on the postgres server. I can only connect to it remotely via a client.
Every solution I've seen until now requires either filesystem access to the server or works with only one set of data at time with a known table that produces only one csv file on the local machine.
Is there a way to achieve what I need ?

if it was on premises, only thing you need to is to mount it first and then and run this as a sql query :
DO $$
DECLARE ssql varchar := 'COPY ( SELECT  * from schema.table1   ) TO ''/projeler2/Raporlar/SatisRaporlari/test'
|| TO_CHAR(NOW(),'DD_MM_YYYY')
|| '.csv'' WITH CSV DELIMITER '','' HEADER QUOTE ''"''  ;';

BEGIN
    EXECUTE ssql;
    --raise notice 'Value: %', ssql;
END; $$;

but remote server may change the game. give it a try


Does SQL Server evaluate functions once for every row? I have a query like this:

SELECT col1
FROM   MyTable
WHERE
    DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
       BETWEEN col2
       AND     col3
;

This gives a tooltip on the execution plan similar to this:

Does the dateadd part of the seek predicates get executed for every row in the query? Or does SQL Server calculate the value once for the whole query?

Certain functions that are known to be runtime constants go through the process called constant folding. By 'folding' a constant an expression is evaluated early in the query execution, the result is cached and the cached result instead when needed. The expression in your query DATEADD(dd, 0, DATEDIFF(dd, 0, getdate())) is, afaik, a runtime constant and thus will be folded and evaluated only once per query.

As trivia: the RAND() function that one would expect to be unfoldable is actually foldable, which leads to some unexpected behavior. But other, for instance NEWID(), are not foldable and will force an evaluation per row.


MariaDB/mySQL is there a way to limit a user to SELECT and SHOW queries? We have a support team member who we'd like to limit his access to our database. He has never caused any problems with UPDATE or DELETE but we'd like to limit him to SELECT/SHOW/etc if it's possible. Is there a way? I'm not talking about limiting the number of queries, but the types of queries he can run.

Use GRANT to give them the exact authority that you would like them to have. It sounds like they may only need SELECT. For global authority use:

GRANT SELECT ON *.* TO 'supportmember'@'localhost';

Or limit the authority to a specific database using:

GRANT SELECT ON specificdatabase.* TO 'supportmember'@'localhost';

Or to a specific table in a specific database:

GRANT SELECT ON specificdatabase.specifictable TO 'supportmember'@'localhost';


Get the Replication/Lag time of Redshift I'm currently running RedShift with pushes of all data from our production Postgresql databases every 10 minutes or so. Periodically, the ETL process from Postgresql to Redshift gets delayed or backed up. Is there any way to monitor the lag time between RedShift and Postgresql?

In this context, I usually create a specific table to see the last replication status.

On your PostgreSql master server, you create a simple table, example:

CREATE TABLE check_replication ( now_date timestamp );

Then you create a batch or job, that performs every minute, or more often:

INSERT INTO check_replication ( now_date ) VALUES (now());

Then, you add this table in the replicated tables from PostgreSql to RedShift

And you can check the replication delay.

HTH,
Thomas


Cannot insert row into table after creating a trigger I get an error when inserting a row into a table after creating this trigger:

DELIMITER $$
CREATE TRIGGER aio_mtltp2016 AFTER INSERT ON master_table_linfomastp2016
BEGIN
        IF master_table_linfomastp2016 (IMHQ_solicitado = 'SI')
        THEN
        INSERT INTO imhq (ID_CASO,Tipo_de_estudio,Año,Codigo_interno,Iniciales_Px,Sexo,Edad)
        VALUES (NEW.ID_CASO, NEW.Tipo_de_estudio, NEW.Año, NEW.Codigo_Interno, NEW.Iniciales_Px, NEW.Sexo, NEW.Edad);
        END IF;
END;$$
DELIMITER ;

The record im inserting is as follows:

INSERT INTO `master_table_linfomastp2016` (`ID_CASO`, `Tipo_de_estudio`, `Año`, `Codigo_interno`, `Iniciales_Px`, `Sexo`, `Edad`, `Servicio_solicitante`, `Roche_Hospital`, `Especimen`, `Macro`, `Diagnóstico`, `subtipo_ltp`, `FISH_solicitado`, `IMHQ_solicitado`, `laminilla_encontrada`, `laminilla_revisada`, `bloque_recibido`, `punch_realizado`, `en_microarreglo`, `validado_por`, `n_marcadores_solicitados`, `Localizacion`, `registro_HU`) VALUES (NULL, 'P', '15', '9106', 'IAS', 'M', '34', 'Hospital Universitario', NULL, 'BIOPSIA', 'REVISION DE LAMINIALLAS', 'LINFOMA NO HODGKIN T PERFIERICO NO ESPECIFICADO', 'Celulas T perifericas (Sin otra especificacion)', 'NO', 'SI', 'NO', 'NO', 'NO', 'NO', 'NO', 'JPF', '12', 'NODAL', '1383250-3')

and it says:

#1305 - FUNCTION linfomas_t_bd.master_table_linfomastp2016 does not exist

where did i go wrong? the insertion of records works if i remove the trigger, but I cannot insert records after creating the trigger and it gives me that error.

If i change the syntax from ` to ' it gives me syntax error. It also says sometimes that the table does not exist. What am i doing wrong? Im using phpMyAdmin but the trigger I made was using direct mysql input, not the trigger creator. The trigger is created without problems.

The IF statement should only check the column that is being inserted for 'SI', see revised code.

DELIMITER $$
CREATE TRIGGER aio_mtltp2016 AFTER INSERT ON master_table_linfomastp2016
BEGIN
        IF New.IMHQ_solicitado = 'SI'
        THEN
        INSERT INTO imhq (ID_CASO,Tipo_de_estudio,Año,Codigo_interno,Iniciales_Px,Sexo,Edad)
        VALUES (NEW.ID_CASO, NEW.Tipo_de_estudio, NEW.Año, NEW.Codigo_Interno, NEW.Iniciales_Px, NEW.Sexo, NEW.Edad);
        END IF;
END;$$
DELIMITER ;


Accidentally Deleted SQL Server Log Reader Service Account Uh, I accidentally deleted the Security Login in my SQL server that allows for the reading of log files.

Now, I can no longer see any of the log files in my SQL Server Logs node of SMSS:(

What account is it so that I can add back the login?

I think it started with "BUILT IN" or something.

Ah, I guessed it.  It was "NT AUTHORITY\NETWORK SERVICE".  When I added it back as a login, then the log files showed up after I refreshed the node tree.  (sorry for the noise)


mongoDB rs.initiate hangs I am having trouble setting up replication on a new mongoDB installation. Everything works fine when I run without replication but when I run mongod with the --replSet option, and run rs.initiate(), this command hangs. Note that I am only running a single instnace of mongod in this example. No other instances are yet running.

In the logfile, I see:

Wed Aug 15 10:02:04 [conn2] run command admin.$cmd { replSetInitiate: { _id: "regression_data", members: [ { _id: 0.0, host: "nradmin.icd.teradyne.com:27017" } ] } }
Wed Aug 15 10:02:04 [conn2] command: { replSetInitiate: { _id: "regression_data", members: [ { _id: 0.0, host: "nradmin.icd.teradyne.com:27017" } ] } }
Wed Aug 15 10:02:04 [conn2] replSet replSetInitiate admin command received from client
Wed Aug 15 10:02:04 [conn2] replSet replSetInitiate config object parses ok, 1 members specified
Wed Aug 15 10:02:04 [conn2] replSet replSetInitiate all members seem up
Wed Aug 15 10:02:11 [conn1] runQuery called local.system.replset {}
Wed Aug 15 10:02:21 [rsStart] Socket recv() timeout  127.0.0.1:27017
Wed Aug 15 10:02:21 [rsStart] SocketException: remote: 127.0.0.1:27017 error: 9001 socket exception [3] server [127.0.0.1:27017]
Wed Aug 15 10:02:21 [rsStart] DBClientCursor::init call() failed
Wed Aug 15 10:02:21 [rsStart] User Assertion: 10276:DBClientBase::findN: transport error: localhost:27017 query: {}
Wed Aug 15 10:02:21 [rsStart] runQuery called local.system.replset {}

I have played with the bind_ip option a bit but had no luck.

Here is my current config file:

dbpath = /hwnet/mongodb/regression_data/replica0
replSet = regression_data/nradmin.icd.teradyne.com
logpath = /hwnet/mongodb/regression_data/mongodb_r0.log
verbose = true
vvvvv = true
rest = true
fork = true

This is running on my work network. I did try the same setup on a standalone Mac installation and the rs.initiate() command works perfectly.

Does anyone have any idea what I should be looking at here? I'm running out of ideas.

I was able to get a solution for this problem on the mongoDB google group. Explicitly setting the oplogSize parameter in my config file solved the problem. By default, mongod tries to allocate 5% of the free space on the storage partition. I have my data on a very large shared disk and this seemed to be taking a long time. I manually set this value and the replica set came up quickly.


Imposing column level security on a view for different user Possible Duplicate:
  Column level security

I have a view which can be viewed by multiple users, and I would like to restrict some columns to specific users.

e.g I have a view with fields A, B... F, and I have users P, Q, R, S. Here's what I am trying to implement:

when user P browses the view, he will see data in A, B, C and F, and columns D and E as NULL
when user Q browses the view, he will see data in all fields
when user R browses the view, he will see data in A, D, E and F, and columns B and C as NULL

There are several options:

You can use synonyms (and as many views as there are different cases), as Phil showed in his answer.
This is a feature of Virtual Private Database, included in the Enterprise Edition.
You can do it by hand with a little code

Here's an example of how you could do it yourself:

CREATE TABLE base_table (a NUMBER, b NUMBER, c NUMBER, d NUMBER, e NUMBER, f NUMBER);

CREATE TABLE column_rights (username VARCHAR2(30) PRIMARY KEY,
                            a NUMBER, b NUMBER, c NUMBER, d NUMBER, e NUMBER, f NUMBER);

INSERT INTO column_rights VALUES ('P', 1, 1, 1, 1, NULL, NULL);
INSERT INTO column_rights VALUES ('Q', 1, 1, 1, 1, 1, 1);
INSERT INTO column_rights VALUES ('R', 1, NULL, NULL, 1, 1, 1);

CREATE OR REPLACE VIEW my_view AS
SELECT b.a * c.a a,
       b.b * c.b b,
       b.c * c.c c,
       b.d * c.d d,
       b.e * c.e e,
       b.f * c.f f
  FROM base_table b
  JOIN column_rights c ON c.username = USER;

 GRANT SELECT ON my_view TO P; -- Q, R

The above assumes that all fields are NUMBER, but this would work with anything if you use CASE. This also assumes that the users are defined at the database level, but you could also use a custom identification scheme (replace USER by your function -- V('USER') in APEX).

In a more general setting, you could use a COLUMN_RIGHTS table with TABLE_NAME and COLUMN_NAME columns (this would unfortunately require N lookups where N is the number of columns).

You could also use a single INTEGER field or RAW field for the concatenated bit representation of the column rights so that all the information would still be in a single row and this could be used for multiple views.


Managing deleted entries in large database table Currently we are using Mysql 5.6 with Amazon RDS.

We have table tracking uploads and we have a deleted flag in this table, we don't delete rows when a user subsequently deletes an upload. We do this to maintain historical upload information.

Now we are finding searches in the table very slow. We are thinking of creating a new table with just the deleted entries, but not sure what the best practice would be here.

Any ideas?

EDIT - Added fields to show what is indexed

Field             Type                 Null Key Default Extra          Index
upload_id         int(10) unsigned     NO   PRI         auto_increment Primary Key
user_id           int(11)              NO   MUL                        Foreign Key
resource_id       int(10) unsigned     NO   MUL                        Foreign Key
upload_size       int(10) unsigned     NO
upload_datetime   datetime             NO
deleted_datetime  datetime             YES
upload_name       varchar(150)         NO
upload_url        varchar(2000)        YES
upload_url_expiry datetime             YES
download_count    smallint(5) unsigned NO       0

It is the deleted_datetime field that gets updated.

Partitioning is particularly useful for this.

See https://stackoverflow.com/questions/12929624/partitions-and-update for an example of how an update can cause rows to be moved to a different partition.

This enables you to keep your table structure as it is, including almost all existing code, while queries for active data will only query the appropriate parition.


Why does this say there is no join predicate? My execution plan is posted on PasteThePlan.com. The second nested loop is warning that there is no join predicate. I'm still new to this so I'm confused. Is that because I am using a CTE to determine the values for two of the attributes?  This presentation table is used in more that a few reports, and I don't want to create a second one for this very unique one.

Here's the query:

WITH Accumulators AS
(
 SELECT DISTINCT AreaNum, SuperintendentNum, SuperName
   FROM FI.SLAnalysis
  WHERE SuperName LIKE '%ACCUM%'
)
SELECT
    slard.[IO]
  , slard.PhaseName             AS PhaseName
  , slard.JobNum              AS JobNum
--  , slard.JobName              AS JobName
  , CASE
   WHEN SUBSTRING(JobNum, 5, 2) IN ('05', '06')
    THEN ac.SuperintendentNum
   ELSE slard.SuperNum
     END                AS SuperNum
  , CASE
   WHEN SUBSTRING(JobNum, 5, 2) IN ('05', '06')
    THEN ac.SuperName
   ELSE slard.SuperName
     END                AS SuperName
  , slard.AreaNum              AS AreaNum
  , slard.AreaName             AS AreaName
  , fp.WeekEndDate             AS WeekEndDate

  -- WTD Metrics
  , slard.WeeklyRevenue            AS WTDRevenue
  , slard.WeeklyCost             AS WTDCost
  , slard.WeeklyRevenue - slard.WeeklyCost       AS WTDGP
  , CASE
   WHEN slard.WeeklyRevenue = .01 THEN 0
   ELSE slard.WeeklyRevenue
     END                AS WTDRatio
  , slard.Weekly_PER             AS WTDGPPer
  , slard.WeeklyStandOvrheadAdmin          AS WTDOverAdmin
  , slard.WeeklyStandOvrheadEquip          AS WTDOverEquip
  , (slard.WeeklyRevenue
   -slard.WeeklyCost
   -slard.WeeklyStandOvrheadAdmin
   -slard.WeeklyStandOvrheadEquip)         AS WTDOHGP

  --MTD Metrics
  , slard.MTDRevenue             AS MTDRevenue
  , slard.MTDCost              AS MTDCost
  , slard.MTDRevenue - MTDCost          AS MTD_GP
  , CASE
   WHEN slard.MTDRevenue <= .05 AND slard.MTDRevenue > 0 THEN 0
   ELSE slard.MTDRevenue
     END                AS MTDRatio
  , CASE
   WHEN slard.MTDRevenue <> 0
    THEN CAST(slard.MTDCost / slard.MTDRevenue AS DECIMAL(18, 2))
   ELSE CAST(0.00 AS DECIMAL(18, 2))
     END                AS MTDGPPer
  , slard.MonthlyStandOvrheadAdmin         AS MTDOverAdmin
  , slard.MonthlyStandOvrheadEquip         AS MTDOverEquip
  , (slard.MTDRevenue
   -slard.MTDCost
   -slard.MonthlyStandOvrheadAdmin
   -slard.MonthlyStandOvrheadEquip)        AS MTDOHGP
  -- YTD Metrics
  , slard.YTDRevenue             AS YTDRevenue
  , slard.YTDCost              AS YTDCost
  , slard.YTDRevenue - YTDCost          AS YTD_GP
  , CASE
   WHEN slard.YTDRevenue <= .53 AND slard.YTDRevenue > 0 THEN 0
   ELSE slard.YTDRevenue
     END                 AS YTDRatio
  , slard.YTDStandOvrheadAdmin          AS YTDOverAdmin
  , slard.YTDStandOvrheadEquip          AS YTDOverEquip
  , (slard.YTDRevenue
   -slard.YTDCost
   -slard.YTDStandOvrheadAdmin
   -slard.YTDStandOvrheadEquip)         AS YTDOHGP
  FROM [DW].[FI].[SLAnalysisReportData] slard
 INNER JOIN Accumulators ac
   ON slard.AreaNum = ac.AreaNum
 INNER JOIN CORP.FiscalPeriod fp
  ON slard.FY = fp.FY
   AND slard.[Period] = fp.[Period]
WHERE slard.FY = 2018
 AND slard.[Period]  = 3
    AND slard.AreaNum IN ('40')
   AND NOT (    WeeklyRevenue = 0
            AND WeeklyCost = 0
            AND WeeklyStandOvrheadAdmin = 0
            AND WeeklyStandOvrheadEquip = 0
            AND MTDRevenue = 0
            AND MTDCost = 0
            AND MonthlyStandOvrheadAdmin = 0
            AND MonthlyStandOvrheadEquip = 0
            AND YTDRevenue = 0
            AND YTDCost = 0
            AND YTDStandOvrheadAdmin = 0
            AND YTDStandOvrheadEquip = 0
           )

It's happening here because the predicate slard.AreaNum IN ('40') is pushed to both ends of the Nested Loop Join operation.

I have a blog post about this same thing, but what it boils down to is that your WHERE clause matches a JOIN clause ON slard.AreaNum = ac.AreaNum

In these cases, the optimizer can push SARGable predicates to the the index access (seek or scan), because it's assured that whichever values come out will match.

This is called an implied predicate, and you can read more about them here:

Craig Freedman

Some Bozo

You can ignore the warning in this case.

Hope this helps!


Looking for a simpler alternative to a recursive query The actual query is more involved, but the problem I'm facing can be distilled to this:

A query to filter a rowset of monotonically increasing integers so that - in the final result set, row(n+1).value >= row(n).value + 5.

For the actual problem I need to solve, the rowset count is in the 1000s.

A few examples to clarify:

if rows are: 1,2,3,4,5 : then query should return: 1
if rows are: 1,5,7,10,11,12,13 : then query should return: 1,7,12
if rows are: 6,8,11,16,20,23: then query should return: 6,11,16,23
if rows are: 6,8,12,16,20,23: then query should return: 6,12,20

I've managed to get the required results with the following query, but it seems overly complicated. Uncomment the different "..with t(k).." to try them out.

I'm looking for any simplifications or alternative approaches to get the same results.

with recursive r(n, pri) as (
    with t(k) as (values (1),(2),(3),(4),(5))   -- the data we want to filter
    -- with t(k) as (values (1),(5),(7),(10),(11),(12),(13))
    -- with t(k) as (values (6),(8),(11),(16),(20),(23))
    -- with t(k) as (values (6),(8),(12),(16),(20),(23))
    select min(k), 1::bigint from t             -- bootstrap for recursive processing. 1 here represents rank().
    UNION
    select k, (rank() over(order by k)) rr      -- rank() is required just to filter out the rows we dont want from the final result set, and no other reason
    from r, t
    where t.k >= r.n+5 and r.pri = 1            -- capture the rows we want, AND unfortunately a bunch of rows we dont want
)
select n from r where pri = 1;

This was hard! I don't know if this is simpler, but at least it doesn't use window function nor produce rows that require being filtered out.

with recursive r(k, n) as (
    with t(k) as (values (1),(2),(3),(4),(5))   -- the data we want to filter
    -- with t(k) as (values (1),(5),(7),(10),(11),(12),(13))
    -- with t(k) as (values (6),(8),(11),(16),(20),(23))
    -- with t(k) as (values (6),(8),(12),(16),(20),(23))
         ,t2(k,n) AS (select k, (select min(k) from t tt where k >= t.k+5) from t) -- precalculate what's next
    select * from (select * from t2 limit 1) x   -- limit 1 directly fails in a union!
    UNION ALL
    select t2.* from r, t2 where t2.k = r.n      -- on each iteration, keep only the value that matches the previous precalculated next one
)
select k from r

Testing

This alternative seems to be less efficient for very small sets, but more or less linear in performance, whilst the original seems to be exponentially more sluggish.

drop table if exists t;
create temp table t(k) AS
with recursive r(n) as (
  select floor(random()*10)::int + 1
  UNION ALL
  select n + floor(random()*10)::int + 1
  from r
  where n < 100000)        -- change to increase or reduce set
select * from r;           -- surprisingly fast! Go PG!
create index on t(k);

with recursive r(n, pri) as (
    select min(k), 1::bigint from t
    UNION
    select k, (rank() over(order by k)) rr
    from r, t
    where t.k >= r.n+5 and r.pri = 1
)
select count(*) from r where pri = 1; -- I aborted it after waiting for a minute

with recursive r(k, n) as (
    with t2(k,n) AS (select k, (select min(k) from t tt where k >= t.k+5) from t)
    select * from (select * from t2 limit 1) x
    UNION ALL
    select t2.* from r, t2 where t2.k = r.n
)
select count(*) from r -- 26" in my server


Something like this?

SELECT
  to_timestamp(end_time, 'YYYYMMDDHH24MISS') -
  to_timestamp(start_time, 'YYYYMMDDHH24MISS') as "time_interval"
FROM
  measurement
WHERE
  end_time IS NOT NULL;

There's an sqlfiddle if you want to adjust your question: http://sqlfiddle.com/#!1/576d4/6


What is "Field List" command in mysql processlist? In my processlist, I see a long running process. There is no query, just the command "Field List" with an empty info column. Its been waiting almost 20 minutes on a table meta data lock on an unspecified table.

Googling for "MySQL command Field List" hasn't returned anything useful. Can someone tell me what this command is trying to do and what may have caused it?

Using MySQL 5.6.35 on a CentOS 6 box

Look for any lines in the PROCESSLIST with a Time higher than that one (1188).  It probably indicates the villain.  The owner of both rows may give a clue.

A "metadata lock" involves something other than simple queries -- possibly ALTER TABLE or LOCK TABLES (as for mysqldump), etc.

"Field list" sounds like probing information_schema for the columns in the table, but I could be wrong.

Another possibility is that there is an open transaction that is waiting to be COMMITted.  (And it is 'between' statements when the PROCESSLIST was grabbed.)

SHOW OPEN TABLES; might give another clue.


Full Migration 2008R2 -> 2016 We currently have Windows Server 2008 with MS/SQL 2008R2: the default instance and two named instances.
We are spinning up a Windows 2016 Server onto which we will install MS/SQL Server 2016 Standard default instance and two named instances.  If I understand what I've read so far I can detach the databases from 2008R2, copy the files to the new 2016 environment and attach to them...  enable query store and up the comparability.  What I need to know is:

How to migrate the user records with their passwords
How to migrate the Linked Servers with their passwords

I have read posts similar to SQL Server Upgrade 2008 -> 2016 / 100 to 130 compatibility slow queries, new CE killing me but find no info' specific to Users and/or Linked Servers

f I understand what I've read so far I can detach the databases from 2008R2, copy the files to the new 2016 environment and attach to them... enable query store and up the comparability.

No. Dont do detach/attach. Instead use backup/restore.

If you have a very tight migration window, setup logshipping or Mirroring and just do a cutover to new instance.

I have detailed about pre and post steps for migration.

  How to migrate the user records with their passwords

Use dbatools to help you out in migration. For logins, just use Copy-DbaLogin.

  How to migrate the Linked Servers with their passwords

use Copy-DbaLinkedServer