which postgres do i have installed? what directory needs to be in my path to run initdb?

Based on your situation, it would make sense I think to disable whatever PostgreSQL you have and start over with a fresh install. We are dealing with completely different applications that have nothing to do with eachother. In reality, PostgreSQL is only needed to serve up data. It makes sense to me, to simplify your life by installing PostgreSQL seperately and treating it as a totally independant application.

The simplest way to go about this, would be to take the following steps.

Determine if PostgreSQL is currently running
A. If it is, locate it and disable it
B. If it isn't move onto step 2
Download PostgreSQL 9.2 from PostgreSQL's website
Install dependancies
Configure, compile and install postgresql
Initalize your database and start postgres
What's next

1. Determine if PostgreSQL is currently running

From the shell, type the following

[root@someserver ~]# netstat -tlpn | grep postmaster
tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN      1119/postmaster
tcp        0      0 :::5432                     :::*                        LISTEN      1119/postmaster

Postmaster on my machine is the PostgreSQL service. It is listening on port 5432 and has a process id of 1119.

Where is it?

Now that we have the process ID we can locate the files being used. lsof is handy because it will show you all the files and sockets a given process is using.

[root@someserver ~]# lsof -p 1119
COMMAND    PID     USER   FD   TYPE             DEVICE SIZE/OFF   NODE NAME
postmaste 1119 postgres  cwd    DIR              253,3     4096 131093 /var/lib/pgsql/9.1/data
postmaste 1119 postgres  rtd    DIR              253,0     4096      2 /
postmaste 1119 postgres  txt    REG              253,4  5205008  27145 /usr/pgsql-9.1/bin/postgres

On my server, it is using /usr/pgsql-9.1/bin/postgres to run the service and the /var/lib/pgsql/9.1/data is the data directory.

From this point you know where its running and what directories its using.

On your server, it looks like its installed in /usr/local/Cellar/postgresql. If you have to start it manually, then you can just leave it alone. When you install PostgreSQL from source, it will install in /usr/local/pgsql by default.

It is only important to be sure that the postgresql currently installed on your system is not using port 5432. This is because two services can not use the same port. If it is, you will need to kill the process and keep it from starting again. Based the contents of your question, it looks like that won't be a problem.

2. Download postgresql

From the shell

[root@someserver ~]# cd ~
[root@someserver ~]# wget http://ftp.postgresql.org/pub/source/v9.2.2/postgresql-9.2.2.tar.gz
[root@someserver ~]# tar -zxvf postgresql-9.2.2.tar.gz

3. Install dependencies

I didn't notice you mentionning what flavor of Linux you were running. You can identify your OS by running cat /etc/issue.

PostgreSQL depends on several libraries.

On CentOS, I would run this to install the libraries I needed

[root@someserver ~]# yum install readline-devel zlib-devel make gcc g++

On Debian, you would use apt-get to install and apt-cache search to locate. I included build-essential because you'll need it to compile postgres.

Something like apt-get install libreadline6 libreadline6-dev build-essential

Not sure about OSX though

4. Configure, Compile and Install PostgreSQL

[root@someserver ~]#
cd postgresql-9.2.2
The --bindir option will put the the postgresql binaries like pg_ctl, initdb, createdb all in the /usr/bin directory. This will be handy for you since you mentionned having trouble finding them.
./configure --bindir=/usr/bin
make
make install

You can check to see if the binaries are available

[root@someserver ~]# psql -V
psql (PostgreSQL) 9.2.2

Once this is done, normally you would need to add the postgres user, but he's already on your system
You do however need to create a data directory and give postgres read/write access to it

[root@someserver ~]# mkdir -p /var/lib/pgsql/data
[root@someserver ~]# chown postgres:postgres -R /var/lib/pgsql/data
[root@someserver ~]# su - postgres

PostgreSQL is installed
You have created a data directory
Now you can initalize your database

5. Initalize and start postgres

initdb -D /var/lib/pgsql/data

This will the cluster and your configuration files such as postgresql.conf, pg_hba.conf which you will need to modify later based on the users that need access, their ip addresses and what ip address and port postgresql will listen on

Now you can start PostgreSQL with the following command :

/usr/local/pgsql/bin/postmaster -D /var/lib/pgsql/data > /var/lib/pgsql/logfile 2>&1 &

When you run netstat -tlpn again, you should see

[root@someserver ~]# netstat -tlpn | grep postmaster
tcp        0      0 127.0.0.1:5432                127.0.0.1:*                   LISTEN      8721/postmaster

It is only listening to local connections for now, because by defaut that's what postgresql.conf is configured to do.

6. What's next

From here, you would need to configure postgresql.conf and pg_hba.conf appropriately. pg_hba.conf defines who can connect, from where and to what. postgresql.conf is used to configure the entire service, what ip address or addresses it listens on, what port number, maintenance cyles, etc. For your purposes, you will probabaly only be interested in changing this variable

listen_addresses = '*'

You may also want to create an init script in the /etc/init.d directory, so it will start automatically. How you go about that also depends on what flavor of Linux you are running.

If you run into any problems let me know. Since you are probably not using the exact same system, you may run into problems I haven't discussed here.


SQL delete where not in (...) query not behaving as expected

If you want to purge an outdated data then your query is a bit complicated:
DELETE FROM fares AS f1
  LEFT JOIN ( SELECT origin_id
                   , destination_id
                   , market_id
                   , cabin
                   , tripType
                   , max(created_at) AS maxDate
                FROM fares
               GROUP BY origin_id
                      , destination_id
                      , market_id
                      , cabin
                      , tripType
            ) AS f2  ON f2.origin_id      = f1.origin_id
                    AND f2.destination_id = f1.destination_id
                    AND f2.market_id      = f1.market_id
                    AND f2.cabin          = f1.cabin
                    AND f2.tripType       = f1.tripType
                    AND f2.maxDate        = f1.created_at  /* was omitted */
      WHERE f2.maxDate IS NULL
)


How to generate a dynamic file name for a mysqldump on Linux?

This should do it :

mysqldump -u yourusername -h yourusername.mysql.pythonanywhere-services.com 'yourusername$dbname'  > `date +"\%Y-\%m-\%d_\%H-\%M"`.db-backup.sql

Will generate files like : 2018-01-01_13-50.db-backup.sql. You can adapt this to whatever exact format you want (but you should avoid using special characters such as :).

If you want to compress the file on the fly :

mysqldump -u yourusername -h yourusername.mysql.pythonanywhere-services.com 'yourusername$dbname'  | gzip > `date +"\%Y-\%m-\%d_\%H-\%M"`.db-backup.sql.gz


Oracle 12c upgrade - backwards compatible with 11g?

Usual rules apply. Code changes should not be required but something will break if code is more complex. Also there will be some SQL plans regressions.

Official documentation about deprecated features is there: Deprecated and Desupported Features for Oracle Database 12c

Also you can check Metalink note 567171.1 about _fix_control parameter. This parameter is used to enable or disable certain bugfixes which affect query behaviour. In 11.2.0.4 the view V$SYSTEM_FIX_CONTROL contains 854 rows which means that there are so many potential quirks while upgrading to the newer version of Oracle Database. I am sure that 12c contains much more rows.


comparing two values in same column with another column using AND condition


The only way you can validate a backup is to restore that backup; plain and simple. Spin up a VM, install the same version of MySQL from which you took the backup, restore the backup and run some simple tests. If you make this new VM a slave of the original master, configure replication and use pt-table-checksum to validate all data is the same on the slave. At the very least you can use CHECKSUM TABLE <table>; on every table in every database to validate that MySQL is capable of reading every row.


If you read official documentation of Serverproperty (T-SQL), it says

LicenseType:   Unused. License information is not preserved or maintained by the SQL Server product. Always returns DISABLED.
NumLicenses: Unused. License information is not preserved or maintained by the SQL Server product. Always returns NULL.

SQL Server does not stores license information anywhere inside its mostly on paper or in some kind of agreement between you and Microsoft or you and Vendor who is providing the software copy. Having said that you can get very limited information when you run select @@version

Any idea how can I change LicenseType of SQL Server?

You simply cannot unless you buy a totally different license.
PS:Dont worry your SQL Server installation is fine


dynamically get last x months and output as a format

I hope that you are not repeating that expression for dateadd(month, -4, dateadd(month, -5, etc. Here is a much tidier way to develop an aggregate query that covers the past 12 months:

DECLARE @start DATE = DATEADD(DAY,1-DAY(GETDATE()),GETDATE())
;WITH Last12Months AS
(
  SELECT TOP (12) m = DATEADD(MONTH, -(ROW_NUMBER() OVER
    (ORDER BY [object_id])-1), @start)
  FROM sys.all_objects
  ORDER BY [object_id]
)
SELECT m.m, COUNT(o.[key])
FROM Last12Months AS m
LEFT OUTER JOIN dbo.OtherTable AS o
  ON o.DateTimeColumn >= m.m
  AND o.DateTimeColumn < DATEADD(MONTH, 1, m.m)
GROUP BY m.m
ORDER BY m.m;

Now, this will output the date as:

2014-12-01

If you really need 2014_12, then either (a) format it in the presentation tier (best practice!) or (b) do something really inefficient and format it in T-SQL. So the SELECT part of the query above becomes:

SELECT m = REPLACE(CONVERT(CHAR(7), m, 120), '-', '_'), c
FROM
(
  SELECT m.m, c = COUNT(o.[key])
  FROM Last12Months AS m
  LEFT OUTER JOIN dbo.OtherTable AS o
    ON o.DateTimeColumn >= m.m
    AND o.DateTimeColumn < DATEADD(MONTH, 1, m.m)
  GROUP BY m.m
) AS wasted_cpu_cycles
ORDER BY m;


Pass a table name as a parameter to a stored procedure

Unfortunately, you can't use a variable containing the table name in an INSERT statement. The error message is actually occurring because it is expecting a variable that is a table type to insert rows into, but you're passing a variable with a string value.
You can use Dynamic SQL to execute the command using a variable table name value like this:
CREATE PROCEDURE [usp_SaveToErrorLog]
    @tblName as nvarchar(50),
    @subject as nvarchar(30)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @Sql NVARCHAR(MAX)

    -- Insert statements for procedure here
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tblName))
        Begin
            SET @Sql = 'Insert Into ' + @tblName + '
            (ErrorNumber, ErrorMessage, ErrorProcedure, ErrorState, ErrorSeverity, ErrorLine)
            Select
                ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE()'

            EXEC sp_executesql @Sql
        End
END

What this does is dynamically create your T-SQL command and store it in the @Sql variable. The stored procedure sp_executesql is then used to execute the T-SQL commands in the @Sql variable, which has the actual table name from the @tblName variable.


query to find the count of each member in a column

One way is to use COUNT() with OVER :

declare @vt_source as table
( id int not null,
  name varchar(50) not null)

 insert into @vt_source(id,name)
 values (1,'pantea')
 ,(1,'pantea')
 ,(2,'shidea')

 select distinct
    id
    ,name
    ,count(*)over(partition by id
                       order by name asc
                       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as idCount
    ,count(*)over(partition by name
                       order by id asc
                       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as nameCount
 from
   @vt_Source

output:

id  name    idCount nameCount
1   pantea  2       2
2   shidea  1       1

EDIT:
as @Lennart sugested, the count part can be rewritten like this:

,count(*)over(partition by id) as idCount
,count(*)over(partition by name) as nameCount


How do I solve an error code 127 when doing a pg_dumpall through a cronjob?

If the script can work apart from a crontab, you should make sure of the following:

The script file contains the necessary environmental variables

$PGHOME
$PGDATA
$PGPORT
$PATH

Output the standard error to a file for future analysis


How to store 'n' days of web server logs in Sql Server?

You should look in to partitioning.

http://technet.microsoft.com/en-us/library/dd578580%28SQL.100%29.aspx

The cool thing about partitioning is that you have just one table name (as opposed to the multiple table approach) so your insert statements remain static.  It works with every application - it's completely transparent to queries.  You don't have to worry about what happens if you end up with different indexes or statistics on each of the tables, either.

You create a partition function that decides how to break up the table into multiple tables behind the scene.  The function can only take one input parameter/field, and in your case, it would be a date field.  The function can break up the table by date, week, month, or year - in your case, you'd want date, 24-hour period.

Then build a SQL Server Agent job that uses T-SQL to swap out the last partition every day.  The delete becomes a metadata operation, and it's blazing fast.  Swap the partition, then drop the old one out.


How to restore .wt backup file to local MongoDB?

Community wiki answer:

*.wt are binary data files used by the WiredTiger storage engine. Individual files are not usable as a standalone backup. If you want to take a file copy backup of a MongoDB dbPath you need to include all of the files using a Supported File Copy Backup Method.

If you have a valid file backup you can use it as the dbPath for another mongod instance. Aside from copying files, there is no restore special restore process for a file copy backup.

Can you clarify what files you have in your backup?


Beginner design of biological sampling database

I would rename TreeData to measurements.

Also, unless you really need the Visits table, I wouldn't use it.  If designed properly your database would look something like this if you included the visits table:

Using the visits table would complicate some of the queries that you're wanting answered.  For example, to answer number one you might use something like this in sql server:

select
     t.treeid,
     max(m.height)-min(m.height) as Growth
from trees t
     inner join measurements m
          on t.treeid=m.treeid
     inner join visits v
          on m.visitid=t.visitid
where v.date>=dateadd(y,-2,getdate())
group by t.treeid

without the visits table you could rewrite that like this:

select
     m.treeid,
     max(m.height)-min(m.height) as Growth
from measurements m
where m.date>=dateadd(y,-2,getdate())
group by m.treeid


Shrink log with TRUNCATEONLY but there's nothing to truncate?

Are these SHRINKLOG WITH TRUNCATEONLY events, even if they have nothing to truncate, still causing performance issues?

How could this not cause performance issues?  Even if there is nothing to shrink it takes work to figure that out.

I would ask them why they are using SHRINKLOG WITH TRUNCATEONLY I expect they don't know why or even that they are doing this.  Even if they know why, there is almost no chance it is for a good reason.  If they have a good reason, you should post it, I doubt anyone has ever seen a good reason for this.

If the system is in FULL Recovery, the ONLY time you can shrink free space is after the log backup has run.  In the very extreme case when there is a good reason (almost never) do it once after the log backup.

If the system is in SIMPLE recovery, the VLF files can only empty when when all the inflight work has completed. And you can only truncate the very last VLF, and then the one before that, etc.  Truncating more then once an hour or maybe at the end of the job is the only way you might actually shrink something. See an in depth view in this related Why are virtual log files not always allocated in order?

Depending on what version of SQL you are running and the current size of the log file, a 2GB grow will create between 1 and 16 VLF files.  The smallest VLF in this scenario is 125MB, so at the very best the smallest amount a shrink could remove is 125MB, see more in this post

Even if they have a really good reason for shrinking (again I doubt they do) there is not a good reason to do it as often as they are.


So I finally got this to work by using the following query:

SELECT
    games.*,
    games.game_id game_u_id,
    GROUP_CONCAT(DISTINCT genres.genre_name) genre_names,
    GROUP_CONCAT(DISTINCT themes.theme_name) theme_names
FROM
    retro_games games
LEFT JOIN retro_game_genres all_genres ON games.game_id=all_genres.game_id
LEFT JOIN retro_game_themes all_themes ON games.game_id=all_themes.game_id
LEFT JOIN retro_genres genres ON all_genres.genre_id=genres.genre_id
LEFT JOIN retro_themes themes ON all_themes.theme_id=themes.theme_id
    WHERE games.game_id='1375'
GROUP BY games.game_id

Wondering if this is the most efficient way of doing it though, or is there another method that would faster? It's fine for my one row of 1375 but it will be eventually used on thousands of rows when they view all games.


Extended Event not catching queries executed on my .NET application

The problem you might be running in to is with the filter on duration. Extended Events only trigger when filters are met, so if no queries take 1000 microseconds or longer, they won't show in the Extended Events session data.


mysql replication successful but slave not replicating

The output you presented is very confusing. On one hand, the master shows:

mysql-bin.000001   639495

The slave's PROCESSLIST indicates that replication works (the IO thread is connected to the master, the SQL thread is waiting).

The slave's SHOW SLAVE STATUS again claims all is well, but the master's position is 106.

Are all these outputs issued at roughly the same time? If not, then they are not valuable.

I would check the following:

The obvious: is 109.123.100.58 really your master?
Does SHOW PROCESSLIST on master list the connection made by the slave under the replica user?
Do you have different server-id settings for master and slave?
Do you have any replicate-do-* or replicate-ignore-* settings in your mysql.cnf file?


last_value ignore null in left join with additional dimension?

Why not this?
SELECT def.cid, c.dt, LAST_VALUE(b.amount, 'IGNORE NULLS') OVER(PARTITION BY def.cid ORDER BY c.dt)
  FROM calendar c
    INNER JOIN (SELECT DISTINCT cid FROM balance1) AS def(cid) ON 1=1
    LEFT JOIN balance1 b ON (c.dt, def.cid) = (b.dt, b.cid)
  ORDER BY c.dt, def.cid
;

Comparison in dbfiddle


Can SQL Server create collisions in system generated constraint names?


postgres read query resample time series data for hourly averages

You can use time_bucket function to make it.
Example:
SELECT
  time_bucket('1 hour', "time") AS hour,
  metric,
  avg(value)
FROM building_data
WHERE kv_tags->'equip_name' = '["35201"]' AND
  m_tags IS NOT NULL
GROUP BY hour, metric
ORDER BY hour limit 1000

Note that I'm simplifying the query and if you want to include kv_tags and m_tags you'll need to also use aggregation functions or add them to the group by clause.


Trying to set up new data_directory on Postgres 9.3

Ubuntu and Debian use pg_wrapper to manage PostgreSQL. It's in the postgresql-common package.

(As a result, this answer doesn't apply to RHEL/CentOS/Fedora).

If there's no data of value, you can just use pg_dropcluster (part of pg_wrapper) to drop (delete) your old datadir. This will destroy all data in it. Then use pg_createcluster to make a new one in /data. That's simplest, but of course it's destructive.

If you want to retain the data, you must:

Ensure that the file system on the new storage is compatible with PostgreSQL. Do not attempt to put PostgreSQL on FAT32 / vfat. I wouldn't attempt to use NTFS on Linux either.
Create every directory up to the location you want for the new datadir.
Ensure that the postgres user has at least x rights on all directories from the data dir down to the / directory, especially including the mount point. You may want to chmod a+x each directory along the path, or chgrp postgres and chmod g+x it.
stop PostgreSQL
mv the data directory, as shown by pg_lsclusters, to the desired new location. If you copy it instead, use cp -aRv to make sure permissions are preserved correctly. Don't just move all contents, move the directory its self.
Edit /etc/postgresql/mypgversion/postgresql.conf and change data_directory to point to the new data directory location.
Start PostgreSQL

I think the mistake you made was, in bold above, that you failed to stop PostgreSQL before moving the datadir. It won't stop the server because it isn't sure it'd be killing the right process, so it refuses to act. You must:

sudo pkill -u postgres

then make sure that all PostgreSQL processes have exited (very important) by making sure that this returns no results:

ps -u postgres

and only then attempt to start the server again.

Never delete postmaster.pid.


Behavior of Oracle During Very Large Transactions

Strictly speaking, you cannot overflow the redo logs, because they are endlessly reused in a circular manner. When in the ARCHIVELOG mode, however, a log file cannot be reused until it's archived, so there can be a situation where you would have to wait for archiving if you fill redo logs faster than they are archived.

In the NOARCHIVELOG mode there is no such restriction, so Oracle will just keep rewriting log files one after another in a circular manner.

In either case there is also a wait during log file switch because the database checkpoint must be performed before the next log is overwritten.

This is fully described in the administration manual, by the way.


MySQL group concat not showing distinct values when joined to another table

I entered that data like this

mysql> use test
Database changed
mysql> drop table if exists fruit;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table fruit
    -> (
    ->   id int not null auto_increment primary key,
    ->   tag varchar(20) not null
    -> );
Query OK, 0 rows affected (0.45 sec)

mysql> insert into fruit (tag) values
    -> ('apples'),('oranges'),('apples'),('pears'),('figs'),('oranges');
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

Note the output of these queries

mysql> select tag from fruit;
+---------+
| tag     |
+---------+
| apples  |
| oranges |
| apples  |
| pears   |
| figs    |
| oranges |
+---------+
6 rows in set (0.03 sec)

mysql> select distinct tag from fruit;
+---------+
| tag     |
+---------+
| apples  |
| oranges |
| pears   |
| figs    |
+---------+
4 rows in set (0.00 sec)

mysql> select group_concat(distinct tag) tag from fruit;
+---------------------------+
| tag                       |
+---------------------------+
| apples,oranges,pears,figs |
+---------------------------+
1 row in set (0.00 sec)

mysql>

According the info you just added to the question, each tag has two fruits. Please note that each tag field (holding two fruits) is unique. You should be storing the fruits as separate fields.

The GROUP_CONCAT function is for query aggregation, not set manipulation.

The only way to use the view is to do this convoluted solution

I reloaded the data like this first:

mysql> use test
Database changed
mysql> drop table if exists fruit;
Query OK, 0 rows affected (0.12 sec)

mysql> create table fruit
    -> (
    ->   id int not null auto_increment primary key,
    ->   tag varchar(20) not null
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> insert into fruit (tag) values
    -> ('apples, oranges'),('apples, pears'),('figs, oranges');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

Here are the contents now

mysql> select tag from fruit;
+-----------------+
| tag             |
+-----------------+
| apples, oranges |
| apples, pears   |
| figs, oranges   |
+-----------------+
3 rows in set (0.00 sec)

mysql> select distinct tag from fruit;
+-----------------+
| tag             |
+-----------------+
| apples, oranges |
| apples, pears   |
| figs, oranges   |
+-----------------+
3 rows in set (0.00 sec)

mysql>

Ready for the convoluted solution ???

Here is the code

SELECT CONCAT('SELECT GROUP_CONCAT(subtag) fruits FROM (SELECT ''',
REPLACE(tags,', ',''' subtag UNION SELECT '''),''') A') INTO @TagSQL
FROM (select GROUP_CONCAT(tag separator ', ') tags FROM fruit) A;
SELECT @TagSQL\G
PREPARE s FROM @TagSQL; EXECUTE s; DEALLOCATE PREPARE s;

Here is the output

mysql> SELECT CONCAT('SELECT GROUP_CONCAT(subtag) fruits FROM (SELECT ''',
    -> REPLACE(tags,', ',''' subtag UNION SELECT '''),''') A') INTO @TagSQL
    -> FROM (select GROUP_CONCAT(tag separator ', ') tags FROM fruit) A;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @TagSQL\G
*************************** 1. row ***************************
@TagSQL: SELECT GROUP_CONCAT(subtag) fruits FROM (SELECT 'apples' subtag UNION S
ELECT 'oranges' subtag UNION SELECT 'apples' subtag UNION SELECT 'pears' subtag
UNION SELECT 'figs' subtag UNION SELECT 'oranges') A
1 row in set (0.00 sec)

Now, execute the SQL generated

mysql> PREPARE s FROM @TagSQL; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+---------------------------+
| fruits                    |
+---------------------------+
| apples,oranges,pears,figs |
+---------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

I TOLD YOU IT WAS CONVOLUTED !!!


'Non-unique column' error when creating a third table using SELECT INTO and FULL JOIN

Just in case it isn't clear what the problem is the result of the SELECT has two columns called CustomerID. One from each table. A table can't have two identically named columns so the attempt to create a table with that format fails.

The solution is don't use *.

List out the columns you want explicitly and remove duplicate columns that you don't need or use column aliases to disambiguate any dupes that you do actually need.

Usually for a full outer join what you actually want is

SELECT COALESCE(Customer.CustomerID,SalesOrderHeader.CustomerID) as  CustomerID
       /*, other columns*/
INTO   CustAndOrders
FROM   SalesLT.Customer
       FULL JOIN SalesLT.SalesOrderHeader
              ON Customer.CustomerID = SalesOrderHeader.CustomerID;

but it seems dubious that you need a FULL OUTER JOIN here anyway. Probably you need a LEFT JOIN


"Don't queue it, just do it".  -- This is my advice on using queuing mechanisms in MySQL.  The effort to enqueue and dequeue small tasks is often more than simply doing the task.  However there are exceptions...

Is this a correct understanding of your "queue"?  You have exactly 70 tasks and they need to be done at times based on various external criteria.  That is, it is not really a typical queue, which can be thought of as "first in, first out".  However, sometimes you may have several of the 70 ready to go, but not enough resources to do all at once. ??

In that case, let's call it a "task status" table.  Dozens of updates per second feed status into the table.  Worker(s) issue a SELECT to see what to do now.  If the computation is too complex to do in a WHERE...ORDER BY...LIMIT 1, then simply readk all 70 and make the decision in a 'real' programming language.  (Yes, I have implemented such.)  Keep in mind that the focus of a database table is to hold raw data.  If you can process the data in a SELECT, that is just a bonus.

"Transactions"...  Keep in mind that transactions in InnoDB are not really designed to last more than a few seconds.  So, if the tasks take longer than that, you need to 'assign' a task to a 'worker'.  Better yet, have the 'worker' assign the task to itself.  Here's one efficient way:

-- with autocommit=1; that is, no need for transactional semantics
UPDATE tasks
    SET worker = $me
    WHERE worker IS NULL
    ORDER BY ...
    LIMIT 1;
if Rows_affected() > 0, then I got a task, else none to get.
SELECT task_id FROM tasks
    WHERE worker = $me;   -- find out which task I got.

When finished:

UPDATE tasks
    SET worker = NULL
    WHERE worker = $me;

Beneficial:

INDEX(worker)

Sorry if I have rambled off in the wrong direction; perhaps there are useful clues anyway.


Should the database service be restarted after performing a restore?

There is absolutely no reason to.

Once the database is restored, if over top a previous database or as a new one, there will be no information in cache (or very little). If you were to restart the services, the only thing accomplished is that SQL Server has to run recovery on all of the databases in the instance and re-acquire memory. This would actually cause a detrimental effect, assuming this isn't express edition and the buffer pool is actually being used (i.e. there is more than 1 databases on the system). Even if it were, there is still no good reason to do so.


How to select only the first distinct value in a column, but have blanks in that column in the other rows otherwise

For mysql at least:

SELECT
(
    CASE WHEN child = (SELECT child FROM people AS inner_people WHERE inner_people.name = people.name ORDER BY people.name, people.child LIMIT 1)
    THEN name
    ELSE ''
    END
) AS name,
child
FROM people ORDER BY people.name, people.child;


SQL Server 2017 MLS - cant add python package

I worked it out, it seems the VM it was running in was too high VMware hardware level.
When I dropped the hardware back to a lower version, it worked.
Thanks


Logic of Assigning values to variables in Scalar-valued Functions

Then this would always return the last item in col1.

What does "last item" mean?  In SQL Server, and indeed most if not all RDBMSs, there is no concept of "last" unless you specify an ORDER BY clause.
The apparent order of results can change drastically if the plan changes due to things like parameter sniffing, updated statistics, new or modified indexes, etc., etc.
Never1 rely on observed order behavior without an explicit ORDER BY.
This question and the attached answers make for interesting reading on the topic, as well.

Yes, I did just say never.  This instance is an exception to the rule "never say never."


A solution that will work with SQL Express would be ideal, SQL
  Standard is fine, but I dont want to have to upgrade to SQL
  Enterprise.

If you want something straight out of the box, that's going to be difficult to find. However, 2016 does bring Standard Edition Availability Groups. Standard Edition before 2016 does have Mirroring. Additionally if you're using asp.net and using the session state databases, there may be additional considerations (like not mirroring or putting it into an AG as the data is volatile and not worth/needed kept).

  A) What options do I have/suggestions do you have regarding keeping
  SQL on the IIS servers. I would rather span SQL over the 2+ web
  servers then have to add additional servers just for SQL.

The best practice for scale would be to put SQL on its' own server. If that's too pricey, it would be possible to put it on the same server as your web services... this brings up a few risks (security, performance, uptime).

  B) Are there any new features coming out in SQL 2016 that will help
  with this?

Yes and No. Yes, Basic Availability Groups are available but I honestly don't believe it's the right fit for your situation. I actually don't believe Availability Groups in general is a good fit for what you've described.

  Although I know this is not exactly a DBA question, but I'll ask
  anyway. Is there a way to force a asp.net to write to all available
  SQL databases at the same time, but can read from any of them?

Unfortunately there is nothing inside of SQL Server out of the box except for Replication. In this case you could have Peer-To-Peer, but again that's an Enterprise Edition feature so we have to throw that away. It would be possible with Merge Replication but to be honest that's a pretty terrible beast I wouldn't wish on anyone.

Taking into account your given resources and in house abilities (not meant to be rude) I would say these are the items to investigate further to see which fits your situation the best (as you're the best person to make that decision as you know the environment).

1. Hosting in Azure

I want to be completely transparent, I work for Microsoft. Having said that, it could be the most cost effective and RPO/RTO effective to transition the application to Azure.

Why? There are a few key resource constraints in your current environment that could make it rough to achieve the uptime you're looking for balanced with cost and people resources. In Azure, there can be multiple copies of the database and you can spin up/spin down (elasticity) web hosts. This gives you the RPO/RTO you would like without having to know any of the SQL Server parts and configure it through a clickable GUI. This can be scaled up/down per cost control and usage.

The biggest win is you get the RPO/RTO and don't have to buy hardware or software.

2. SQL Server Mirroring

Mirroring doesn't require a Windows Cluster or a host of other pre-requisites like Availability Groups do. It is generally much easier to setup and is straight forward. It does require at least Standard Edition and there would most likely be licensing costs per instance of SQL Server, depending on a few things.

Mirroring allows for single database failover and a witness server can be used with SQL Server Express Edition. Additionally .NET applications have the ability to set a failover partner server in the connection string to help facilitate a more transparent failover scenario. Mirroring does not require a listener or client access point like Availability Groups do, thus it's very easy to use an IP load balancing solution to do health checks and automatically point to the proper host. This does not allow for multi-master scenarios, there will still only be a single "master" server which will then update the single partner server.

This is much less flexible of a design, but meets the RPO/RTO targets and requires the least amount of administrative upkeep (other than Azure). It is a deprecated (read, removed) technology but should get you through until 2014 is out of mainstream support and there may be other solutions.

3. SQL Server Clustered Instances

This should meet the RPO/RTO requirements as well as be one of the easier implementations if you're already familiar with Windows Clustering. If you are not familiar with it, then I'd probably stay away from SQL FCIs or have a contractor come in and set it up (if you choose this route).

FCIs only hold one copy of the database and do requires shared storage to implement. There are also the Windows Clustering requirements. It does, however, provide transparent failover and can be used with Standard Edition. There needs to be at least two servers to provide high availability.

4. Log Shipping

This is not a good fit but does most of what you'd like, doesn't deliver the RPO/RTO but does meet the cost requirements. It's also extremely easy to setup and does require at least Standard Edition.

This basically works by taking log backups through SQL Server agent, copying them to a location that all instances can see, and then the secondary servers check the location periodically and restore the latest log backups. Everything after that is completely manual. Though, again, it could be used with a CNAME or IP load balancer to make things a little more seamless. It would still require a ton of administrative work to do a failover and then setup reverse log shipping to keep all the other secondary servers in sync.

Aaron Bertrand has a good post of doing this on a budget while having "readable secondary servers"


Query sometimes hangs (but can be executed in less than 4s when run in parallel)

Add an explicit
ANALYZE results;

at the end of \calculate so that the other function has accurate table statistics.


Does this do what you want?

SELECT SUM(x.records)
FROM
(
    SELECT COUNT(thecol) AS records
    FROM thetable
    WHERE thecol IS NOT NULL
    GROUP BY thecol
    HAVING COUNT(*) > 1
) AS x


How to Keep Audit Data and Effectively Query Large Tables

Add a column "current" to your existing table make it true or false.  If current it is true.

Additionally

Consider adding a second date column "End effective", when John Smith has multiple old records, you are going to want to know the date span each was correct for.


I think you need to revise the design of your db tables. The way you have described where using fixed categories as fields of a table is not so wise, what if you needed to add more categories?

So assuming your problem is to search for keywords of those items belonging to a selected category, here is the way to do it.

We can use two tables in the Database.

Table 1. Categories

==============================
|  ID  | Category            |
------------------------------
|  1   | Computer Science    |
|  2   | Engineering         |

Table 2. Items

=============================================
 ID  |  Category_ID  | Keywords             |
=============================================
  1  |      1        | Keyword A            |
---------------------------------------------
  2  |      2        | Keyword B            |
---------------------------------------------
  3  |      1        | Keyword C            |
---------------------------------------------

Now you can do try the following query in the page that gets the selected id as url parameter.

// you can use $id = $_GET['selectvalue'];
// but do remember to escape the parameter value before passing to the query to avoid any security issues
// therefore use the following

$id = mysql_real_escape_string($_GET['selectvalue']);
$query = "SELECT * FROM ITEMS WHERE category_id = $id";

//Do note that use of mysql_query() is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used.
//However to give you a simple answer

$result_query = mysql_query($query);
$result_data = mysql_fetch_assoc($result_query);

If this is not what you are trying to do and if you really want to search for a particular field with keyword values, than here is another option

$selectColumn = $_GET['selectvalue'];
$keywords = "2,3,4";

$query = "SELECT * FROM items WHERE $selectColumn IN ($keywords)";
$result_query = mysql_query($query);

UPDATED

If you want to do a fulltext search as what you are trying to do according to the details in your question, I want you to check the following.

Make sure the index of type is FULLTEXT.
Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

Inside the MATCH() you need to put the field names comma separated and AGAINST() can only have a string and optional modifier indicating what type of search you want to do. SO in your case, it should be something like this:

SELECT * FROM TableNameHere WHERE MATCH (fieldA, fieldB) AGAINST ('"$str"' IN BOOLEAN MODE)

try doing a test by using a full query with actual values instead of the $variables in the query.

For more information, have a look at these two links

http://dev.mysql.com/doc/refman/5.5/en//fulltext-search.html

http://dev.mysql.com/doc/refman/5.5/en//fulltext-boolean.html


In general, actively writing to multiple masters in MySQL is a bad idea.

MySQL Replication is asynchronous, which means there's no guarantee the changes make it to the slave before the changes are committed on the master.

The implication is that something™ (node dies, slave lag, etc) can/will go wrong with one of the masters at any time. If your application and business rules can't handle this appropriately, you are in for some pain.

To answer your bullet points:

"How quickly does replication work?"

As fast as your server resources allow: network/IO/cpu/memory.

"What happens if one of the servers go down? Would we change HAProxy to use just the one server?"

Really depends on your HAproxy config, but a typical config would handle it automatically.

"How does replication deal with loading and replicating millions of rows each day? This would happen once a day."

Depends mainly on your hardware and configuration, and the answer here is: try it out. I've seen many GB loaded with minimal lag, and I've seen it cause hours of lag to apply on the slave. The real question is: can your application handle lag that arises from this, whether it's seconds or hours? Remember, slave applying is serial (until 5.6 with multiple slave threads), so lag would affect all tables until it's caught up.


Seems WHM / CPanel comes by default with query_cache_size=0 and this was killing the performance. After set to an optimal value all its normal :)


I found this forum thread had an answer that worked well: Automatic Version increment on Data-Tier Applications

Minimum steps are:

Create Properties\AssemblyInfo.cs if it doesn't already exist, and make sure it's included in the project and contains at least this code: using System.Reflection; [assembly: AssemblyVersion("1.0.*")] Or however you want your version number to go.
Edit the *.sqlproj file in an outside editor (notepad etc) and add the following XML:

    <Target Name="SetDacVersionToAssemblyVersion" AfterTargets="CoreCompile">
        <GetAssemblyIdentity AssemblyFiles="$(IntermediateTargetFullFileName)">
            <Output TaskParameter="Assemblies" PropertyName="IntermediateTargetAssembly" />
        </GetAssemblyIdentity>
        <PropertyGroup>
            <DacVersion>$(IntermediateTargetAssembly.Split(',')[1].Split('=')[1])
            </DacVersion>
        </PropertyGroup>
        <Message Text="DacVersion set to $(DacVersion)" Importance="high" />
    </Target>


Get only overall summary WITH ROLLUP and GROUP BY for multiple fields

SELECT * FROM
(
    SELECT name, number, COUNT(1) `count` FROM test
    GROUP BY name, number WITH ROLLUP
) A WHERE (ISNULL(name) + ISNULL(number)) <> 1;

or

SELECT * FROM
(
    SELECT name, number, COUNT(1) `count` FROM test
    GROUP BY name, number WITH ROLLUP
) A WHERE ISNULL(name) = ISNULL(number);


Some clarifications:

REFERENCES is the key word used for a FOREIGN KEY constraint (which allows to cascade DELETE or UPDATE).
Your database design seems to have logical flaws. rating seems like a detail of the main table restaurant. Since you have a 1:1 relationship, you could just include the "rating" columns in the main table. If you need a separate table, you would include a restaurant_id in the rating table rather than the other way round.
Your "rating" columns average, vote_count and vote_sum indicate another table vote and these values are derived aggregates. A MATERIALIZED VIEW would be the typical solution for that. Either as separate rating table or in combination with columns from each main table ...

The clean way would be to have a separate rating table for each main table. Then you can have a FK constraint on each with ON DELETE CASCADE.

If you still need your current design, I have two ideas:

1. Multiple FK columns in one rating table

With FK constraints reversed to point in the right direction, the table could look like this:

CREATE TABLE rating (
  rating_id  serial PRIMARY KEY
, vote_count int
, vote_sum   int
, average    float8
, restaurant_id int UNIQUE REFERENCES restaurant(restaurant_id)
                    ON UPDATE CASCADE ON DELETE CASCADE
, picture_id int UNIQUE REFERENCES picture(picture_id)
                 ON UPDATE CASCADE ON DELETE CASCADE
-- more references to other tables
);

The UNIQUE constraints enforce your 1:1 requirement. Each row in each master table can have at most one row in rating. Also creates very useful indexes automatically.

Alternatively, if you have several main tables, you might want to create partial unique indexes instead to exclude irrelevant rows from each index:

CREATE UNIQUE INDEX rating_restaurant_id ON rating (restaurant_id)
WHERE restaurant_id IS NOT NULL;
-- etc.

To enforce that exactly one FK column per row is NOT NULL:

ALTER TABLE rating ADD CONSTRAINT exactly_one_fk CHECK (
      (restaurant_id IS NOT NULL)::int
    + (picture_id    IS NOT NULL)::int = 1);  -- add more ...

With a couple of master tables, it may look like a lot of wasted storage but it really is not. A bunch of NULL columns hardly cost anything. NULL storage is very cheap:

How do completely empty columns in a large table affect performance?

2. Inheritance

Since your motivation is to avoid repeating this rating schema, inheritance could be a good solution for you. A limitation of table inheritance is that foreign keys of the parent table are not inherited - which turns into an advantage for your case:

CREATE TABLE rating (
  rating_id  serial PRIMARY KEY
, vote_count int
, vote_sum   int
, average    float8
);

CREATE TABLE restaurant_rating (
   restaurant_id int PRIMARY KEY REFERENCES restaurant(restaurant_id)
                                 ON UPDATE CASCADE ON DELETE CASCADE
) INHERITS (rating);

CREATE TABLE picture_rating (
   picture_id int PRIMARY KEY REFERENCES picture (picture_id)
                              ON UPDATE CASCADE ON DELETE CASCADE
) INHERITS (rating);

-- more?

Now you have to define the basic schema only once and inherit from it.
You still have a common PK column for all ratings with a single attached sequence.
Each child table adds the ID of the master table as PK and FK, thus enforcing your 1:1 relationship and providing the important index on the column automatically.
You can query the master table to get all ratings at once. If you need to know from which child table each row originates:

SELECT tableoid::regclass::text AS origin, *
FROM   rating;

Get all partition names for a table
Retrieve all records from multiple schemas

You might want to add a rule or trigger to disallow inserts into the master table rating directly.

SQL Fiddle.

Related:

Use triggers on inherited tables to replace foreign keys
Database design - should two projects share the same table?


What is the difference between LOB and binary data in SQL Server?

According to Technet: Data Types (Transact-SQL)

  In SQL Server, based on their storage characteristics, some data types
  are designated as belonging to the following groups:

  Large value data types: varchar(max), nvarchar(max), and    varbinary(max)
  Large object data types: text, ntext, image, varchar(max),    nvarchar(max), varbinary(max), and xml

So these types are defined as LOB data types.
When you asked about binary data I suppose you are talking about the data of binary data type.

Binary data type can be one of two kinds: fixed-length and variable length.
Fixed-length binary data type or variable-length varbinary data type with the maximum number of characters instead of MAX specifier is always stored in row in the first case and (may be) off-row in the second and is NOT LOB data type.

On the other hand, according to this classification (from the same Technet article):

varchar(max), nvarchar(max), text, ntext data types are CHARACTER strings and not BINARY strings.

So LOB data types include CHARACTER strings that are not binary strings.

Conclusion

BINARY data <> LOB data

And the only case when it's the same is when your data is defined as VARBINARY(MAX)

Back to your question from the comment on how LOB data can be stored.

If the value is one of the old legacy types (text, ntext, image) then it is stored off-row by default.

Off-row means that there is a pointer from the data record which points to the location of the record storing the actual LOB value or the start of the tree that stores the LOB value:

This image is from Pro SQL Server Internals 1st Edition
by Dmitri Korotkevitch book.

But you can force the value to go in-row (obviously with the limit) using text in row option.

Starting with SQL server 2005 new LOB data types were introduced, these new types: varchar(max), nvarchar(max), varbinary(max) are stored in-row by default (of course with the limit of 8Kb)

............................................................

To complete a picture.

There is another kind of data that is stored off-row, that is row-overflow data.

Thanks to this feature, starting with 2005 it's possible not only to DEFINE (as it was in 2000) some variable-length columns with the sum of sizes that exceeds 8060 bytes, but also to INSERT data that will exceed this limit, some of these values will be pushed off-row (i.e. the value will be stored on off-row page and IN ROW there will be only a pointer to the row stored on the row-overflow page):

  A table can contain a maximum of 8,060 bytes per row. In SQL Server
  2008, this restriction is relaxed for tables that contain varchar,
  nvarchar, varbinary, sql_variant, or CLR user-defined type columns.
  The length of each one of these columns must still fall within the
  limit of 8,000 bytes; however, their combined widths can exceed the
  8,060-byte limit. This applies to varchar, nvarchar, varbinary,
  sql_variant, or CLR user-defined type columns when they are created
  and modified, and also to when data is updated or inserted.

  This restriction does not apply to varchar(max), nvarchar(max),
  varbinary(max), text, image, or xml columns

(From Row-Overflow Data Exceeding 8 KB)

  The max specifier expands the storage capabilities of the varchar,
  nvarchar, and varbinary data types. varchar(max), nvarchar(max), and
  varbinary(max) are collectively called large-value data types. You can
  use the large-value data types to store up to 2^31-1 bytes of data.

Back to the origin of this your question, how LOB data can be moved.

When we are talking about moving data to another filegroup, and about difficulties we have with LOB data, we refer to LOB data and NOT to binary data that is stored in binary columns (in-row data).

From What about moving LOB data? by Kimberly L. Tripp

  In versions prior to SQL Server 2012, an index that includes LOB
  columns cannot be built or rebuilt using online operations. However,
  even with OFFLINE index builds or rebuilds, existing LOB data is not
  moved. The one exception is when you change from a partitioned table
  to a non-partitioned table or vice versa. In honesty, the limitations
  and requirements around LOB data are a bit strange. When you create a
  table you can specify TEXTIMAGE_ON. This allows the LOB data for that
  table to reside on a filegroup other than where the data resides.
  However, this option (TEXTIMAGE_ON) is NOT available for the CREATE
  INDEX statement.

  What if you want to move the LOBData to another filegroup? The
  TEXTIMAGE_ON option is not allowed during a CREATE INDEX statement.
  So, if you want to move the LOB data to another filegroup – which is
  different than the data, then “game over” there is NO way to do this
  without exporting/importing or using INSERT/SELECT or SELECT INTO. Of
  these, the best option is INSERT/SELECT as you’ll need to first define
  the table (and you can specific exactly where you want both the data
  and LOB data will reside) before you copy it over. But, this is
  single, large transaction and while you’re migrating there will have
  to be some downtime to switch everything (by dropping the original
  table and renaming the newly created table). And, I haven’t even
  started to mention what happens if this table is being referenced by
  others. That further complicates this process.

And to your last question:

  Is it the same, in terms of binary data being stored in a seperate
  filegroup

Separate filegroup is not a requirement for storing LOB data.
But it's possible to store LOB data separately using TEXTIMAGE_ON option:

  TEXTIMAGE_ON { filegroup| "default" } Indicates that the text, ntext,
  image, xml, varchar(max), nvarchar(max), varbinary(max), and CLR
  user-defined type columns (including geometry and geography) are
  stored on the specified filegroup.

  Varchar(max), nvarchar(max), varbinary(max), xml and large UDT values
  are stored directly in the data row, up to a limit of 8000 bytes and
  as long as the value can fit the record. If the value does not fit in
  the record, a pointer is sorted in-row and the rest is stored out of
  row in the LOB storage space. 0 is the default value. TEXTIMAGE_ON
  only changes the location of the "LOB storage space", it does not
  affect when data is stored in-row. Use large value types out of row
  option of sp_tableoption to store the entire LOB value out of the row.

And the data of fixed-length binary data type just cannot be stored in a separate filegroup. It's impossible because all the data is stored in-row, and it's syntactically impossible as

  TEXTIMAGE_ON is not allowed if there are no large value columns in the
  table

More on TEXTIMAGE_ON here: CREATE TABLE (Transact-SQL)


NOLOCK hint changes order of records returned

The appearance of an ordered result set, without an ORDER BY clause, often results from a scan retrieving rows in index order. One reason why an index-order scan is generally chosen under the default READ COMMITTED isolation level is that it reduces the chances of unwanted concurrency anomalies such as encountering the same row multiple times, or entirely skipping some rows. This is detailed in several places, including in this series of articles about isolation levels.

With a NOLOCK table hint, this behavior is relaxed, and access to the table is performed under the more tolerant READ UNCOMMITTED isolation level, which may scan data in allocation order instead of index order. As described in that link, the decision about whether to use an allocation-order or index-order scan is left up to the storage engine. This choice may change between executions without a change in query plan.

This may sound very abstract, but can be more easily demonstrated with some queries using undocumented functions against the AdventureWorks2012 database.

USE AdventureWorks2012;
GO
-- Appears to be ordered by BusinessEntityID
-- File:Page:Slot goes up and down several times
-- Show physical locations with sys.fn_PhysLocFormatter (undocumented)
SELECT
    P.BusinessEntityID,
    [(File:Page:Slot)] =
        sys.fn_PhysLocFormatter(%%physloc%%)
FROM Person.Person AS P;

-- Same query with TABLOCK or NOLOCK
-- Allocation-order (IAM) scan
-- Now appears to be ordered by File:Page:Slot instead of BusinessEntityID
SELECT P.BusinessEntityID,
    [(File:Page:Slot)] =
        sys.fn_PhysLocFormatter(%%physloc%%)
FROM Person.Person AS P WITH (NOLOCK);

The queries are borrowed with slight modification from Paul White.

Finally, just to be clear, this answer is about the appearance of an ordered result set. There is no guaranteed presentation order without a top-level ORDER BY.

An allocation-order scan can occur in a variety of other circumstances, such as when a table-level lock is acquired, or the database is in read-only mode. Parallelism can also influence the order the data is returned in. The key point is that without ORDER BY, the order data is returned in can vary over time by design.


Your query for a specific department can make use of the index:
create index on employee_history (employee_id , last_update_ts desc);

The index without the desc indeed cannot be used.  I don't think there is a good reason for this, it is just some kind of oversight in the planner.  However, this index usage of the index that can be used is not going to be particularly fast anyway. It will have to read the entire index and table.  It will just avoid the sort of the entire table, not the reading of it.
This is a fundamental limitation of your questionable design.  Without some extra limitations (like employees can never move between departments) I don't see anything you can do about this in general.


Is it necessary to create primary and foreign keys in order to use tpc-h benchmark?

The specification TPC BENCHMARKTM H, Standard Specification, Revision 2.17.1, can be found here. See section "1. LOGICAL DATABASE DESIGN / 1.5 IMPLEMENTATION RULES". Indexes can be created on combinations of  fields defined in 1.4.2.2 and 1.4.2.3 and on date fields. Indexes may only contain fields of one table. So indexes can be built on the following fields:

Table PART:

P_PARTKEY

Table SUPPLIER:

S_SUPPKEY

Table PARTSUPP:

PS_PARTKEY
PS_SUPPKEY

Table CUSTOMER:

C_CUSTKEY
C_NATIONKEY

Table ORDERS:

O_ORDERKEY
O_CUSTKEY
O_ORDERDATE

Table REGION:

R_REGIONKEY

Table LINEITEM:

L_LINENUMBER
L_ORDERKEY
L_PARTKEY
L_SUPPKEY
L_SHIPDATE
L_COMMITDATE
L_RECEIPTDATE

Table NATION:

N_NATIONKEY
N_REGIONKEY


Error creating memory optimized filegroup in SSDT

I am not certain whether AUTO_CLOSE is enabled or disabled by default
  after CREATE DATABASE. If ON by default, it seems that SSDT is
  generating the deployment script in the wrong order. If OFF by
  default, then I don't understand why the error exists.

  It seems LocalDB has hard coded use of AUTO_CLOSE, ignoring model

LocalDB / SQLEXPRESS

True, when connecting to localdb and running a standard create database command

CREATE DATABASE TEST

GO

SELECT name,database_id
FROM sys.databases
WHERE is_auto_close_on = 1;

name    database_id
TEST    5

It appears that the is_auto_close_on is set to 1 even when model has auto_close disabled.

Normally the default settings are based on the model system database.

Resolving this issue in theory

You could create a trigger that automatically changes the auto close to 0 for newly created databases. This is a bit of a hacky approach but works.

Try / implement at your own risk.

CREATE TRIGGER Change_Auto_Close
ON ALL SERVER
FOR CREATE_DATABASE
AS
    DECLARE
        @DatabaseName NVARCHAR(128)
        ,@SQL NVARCHAR(4000);
    SELECT
        @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)')

    SET @SQL = 'USE [master] ALTER DATABASE ['+@DatabaseName+'] SET AUTO_CLOSE OFF WITH NO_WAIT;';

    IF @@TRANCOUNT = 1
    COMMIT TRAN; -- commit previous tran, otherwise we cannot alter the database
    -- PRINT @SQL;
    EXEC (@SQL);
    BEGIN TRAN; --begin new tran as not get error that the transaction ended in the trigger

GO

Ideas from here and here

Other editions

It could be that your model database has is_auto_close_on = 1 as databases are created as a copy of model. Below are some tests with auto_close enabled and disabled on the model database.

Set auto close on for model

USE [master]
GO
ALTER DATABASE [model] SET AUTO_CLOSE ON WITH NO_WAIT;
GO

Create a default database

CREATE DATABASE AutoClosedDB;

Validate the auto close state of the newly created database

SELECT name, is_auto_close_on
FROM sys.databases
WHERE name = 'AutoClosedDB';

Result

name            is_auto_close_on
AutoClosedDB    1

Set auto close off for model

USE [master]
GO
ALTER DATABASE [model] SET AUTO_CLOSE OFF WITH NO_WAIT;
GO

Create a database with default settings

CREATE DATABASE NoAutoClosedDB;

Validate the auto close state of the newly created database

SELECT name, is_auto_close_on
FROM sys.databases
WHERE name = 'NoAutoClosedDB';

Result

name            is_auto_close_on
NoAutoClosedDB  0

Try creating a memory optimized filegroup on both databases

ALTER DATABASE NoAutoClosedDB
    ADD FILEGROUP [MemoryOptimizedFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA;

Succeeds

ALTER DATABASE AutoClosedDB
    ADD FILEGROUP [MemoryOptimizedFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA;

Fails with error message:

  Msg 10794, Level 16, State 125, Line 33 The operation 'AUTO_CLOSE' is
  not supported with databases that have a MEMORY_OPTIMIZED_DATA
  filegroup.

To validate the auto close state on the model database, run below script

SELECT name, is_auto_close_on
FROM sys.databases
WHERE name = 'model';

Disable auto close with

USE [master]
GO
ALTER DATABASE [model] SET AUTO_CLOSE OFF WITH NO_WAIT;
GO


Is transaction log lost after backup on SQL Server?

Transaction log is not written along with full backup ?

This is not correct.
In FULL recovery mode – the database backup contains all the log necessary to make the restored database transactionally consistent as of the end of the backup operation.

Refer myths around full database backups from Paul Randal. Below is an excerpt

*A full database backup, when restored either explicitly using WITH RECOVERY, or when a recovery option is not specified ALWAYS results in a transactionally-consistent database. The point-in-time at which the database is restored to is the point at which the data-portion-reading part of the backup operation completed. All database backups include transaction log, otherwise there would be no way to rollback transactions that were active at the time the data-reading portion of the backup ended.*

Do I lose transaction log on a database when I do full backup ( is log truncated on full backup ) ?

No you do not loose T-Log when you do full database backup. Refer to

Misconceptions around the log and log backups: how to convince yourself for understanding how log backups work.

If I want to have data backup and transaction logs backup what should I do ?

If you want to have data backup and T-log backups, you have to first perform FULL database backup and then take subsequent T-log backups. So when you restore your database, you can restore a full backup and then subsequent T-log backups to do a Point-in-Time recovery.


migration from mysql 4.1.0 to mysql5.6.2

Please read this but report:

http://bugs.mysql.com/bug.php?id=69027

The workaround ("solution", really) to this is to change the password for the affected user to a post-4.1 hash. This is really a recommended best practice, regardless - password hashing and authorization process prior to 4.1 has notable security limitations (discussed in documentation at http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html).
Restoring a 4.1 version of the mysql schema on to a 5.6 server is a bad idea at any rate, because 5.6 has additional columns in some tables and some completely new tables, which may or may not be missing now depending on how you configured mysqldump when you created the dump file. You may have caused other problems that you may not see immediately.

Also, specify skip-secure-auth option in [mysqld] section of my.cnf/my.ini file of your MySQL server and restart it.


Insert statement fails silently when a FK constaint can't be statically evaluated

My bet is that:

SELECT id FROM entity
WHERE properties->>'member_id' = '92385'

returns NO ROWS (NO ERROR) and INSERT command is never executed.

I've reproduced it in this way:

create table entity
(
  id int primary key,
  foo int
);

create table hierarchy
(
  id serial primary key,
  parent_id int,
  child_id int,
  foreign key (parent_id) references entity (id),
  foreign key (child_id) references entity (id)
);

insert into entity
select x, x * 2
from   generate_series(1, 100) x;

WITH entity_result AS (
  SELECT id FROM entity
  WHERE id = 10000 )
INSERT INTO hierarchy(parent_id, child_id)
SELECT 100, id FROM entity_result;

✓

SELECT * FROM hierarchy;

id | parent_id | child_id
-: | --------: | -------:

INSERT INTO hierarchy VALUES (100, NULL);

1 rows affected

SELECT * FROM hierarchy;

 id | parent_id | child_id
--: | --------: | -------:
100 |      null |     null

db<>fiddle here


How to Capitalize letters that are after a special character?

Here is one for Caps after any NON-ALPHA character (including spaces/special characters.

Edit: Oops, I didn't realize you wanted ALL letters to be capitalized.  Just thought you meant InitCaps.  I'll leave it up so you can work with it (should just be a matter of changing the REGEX) and mold it to what you need unless others would like me to take it down.

Inspired by (stolen from):
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/sql-server-proper-case-function/

DECLARE @String VARCHAR(100)

SET @String = 'Hello.this is@aTest'
DECLARE @Position INT
;
--===== Update the first character no matter what and then find the next position that we
     -- need to update.  The collation here is essential to making this so simple.
     -- A-z is equivalent to the slower A-Z
SELECT @String   = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
        @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;
--===== Do the same thing over and over until we run out of places to capitalize.
     -- Note the reason for the speed here is that ONLY places that need capitalization
     -- are even considered for @Position using the speed of PATINDEX.
  WHILE @Position > 0
SELECT @String   = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
        @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;

SELECT @String;
-- RETURN @String; --Only for functions

--Output: Hello.This Is@Atest

And here it is as a function:

CREATE FUNCTION dbo.InitialCap(@String VARCHAR(8000))

------------------------------
RETURNS VARCHAR(8000)
     AS
  BEGIN

DECLARE @Position INT
;
--===== Update the first character no matter what and then find the next postion that we
     -- need to update.  The collation here is essential to making this so simple.
     -- A-z is equivalent to the slower A-Z
SELECT @String   = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
        @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;
--===== Do the same thing over and over until we run out of places to capitalize.
     -- Note the reason for the speed here is that ONLY places that need capitalization
     -- are even considered for @Position using the speed of PATINDEX.
  WHILE @Position > 0
SELECT @String   = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
        @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;
-----------------
RETURN @String;
    END ;


Oracle isn't restoring correctly a backup

Based on the above output, you never had the table.

But let's assume you had the table, then created the backup, then dropped the table.

You restored the database with restore database.

Then you recovered the database to the most recent point with recover database.

So you restored the database, and it had the table. Then you recovered the database using the redo log, which contained the change that dropped the table, so the table was dropped again. It is no wonder you can not see the table after opening the database.

If you took the backup at 2019-05-27 22:00:00, then dropped the table at 2019-05-27 22:10:00, and you want to restore the database to a point of time when it still had the table, you need to perform an incomplete recovery.

So instead of recover database;, specify a point of time where you still had the table, for example: recover database until time "to_date('2019-05-27 22:05:00', 'YYYY-MM-DD HH24:MI:SS')";.

Oracle did exactly what you asked from it.


Best way to store country and city information

Countries are relatively stable so it is easy to have a countries table.  City names can be duplicated multiple times within a country.   Maintaining the city table may be difficult for a couple of reasons.

Some cities have different spellings in different languages.
Some countries have multiple cities with the same name.  These are likely in different states or provinces.

You may want to allow free format for the city and keep it in the user table.  Otherwise, investigate adding a state/province table.  You will still need to deal with how to add new cities as needed.

If you index the sender, the count can be done on the index.  This can be extremely efficient for smaller counts.  Updating the user table for each message will increase the log data volume, and increase the cost of saving each message.  It is also error prone, and can lead to deadlocks if you are not consistent in the order the accesses are done.  De-normalizing the model should be done only after determining you have a performance problem.

A query like SELECT COUNT(1) FROM message WHERE sender=?ID is likely better than maintaining a count.


Is Postgres insert performance the same in the replica

It could improve performance on your INSERT operations (and thus improve performance where those operations occur in the application) depending on how your read replica is setup and if your INSERT contention is partly due to locking caused by heavy reads against your primary database.
I'm not an expert on PostgreSQL replicas in practice, but the theory is applicable, that if your read replica is asynchronously synchronized from your primary, then any SELECT queries executed on your read replica shouldn't cause locking against your primary database (like they normally would if they were executed on the primary). It sounds like it is synchronized asynchronously if it's streaming the WAL which is being asynchronously written to. If that's the case, and if your database was experiencing heavy read lock contention, it's possible you'll see INSERT performance gains on your primary database by moving your SELECT queries to the read replica.
As far as the performance of the SELECT queries goes, I believe you're likely correct, that you won't necessarily see any performance gains since they'll still be bound by the same CPU, I/O, and write lock contention as the read replica is updated.
If it is easy enough to test, I'd say it's worth trying, if you got nothing to lose.


Partitioning a table on a column in another table

Short answer: No, you cannot partition by a column in another table, because that would cause problems whenever you change something in the "other" table - rows would magically have to move between partitions.

Summarizing some of the views in the comments from Aaron, Kenneth and me on your question:

You could change AccountMonthId from a sequential number to a natural key, in this case an integer column from which you can compute the year and month. For example, the value 201503 could correspond to march 2015. This type of key is very common for date dimensions, because date dimensions don't change, so you can hard-code their keys.
With a natural key for AccountMonthId you could partition on this column, RANGE RIGHT FOR VALUES (201400, 201500, 201600) or RANGE LEFT FOR VALUES (201499, 201599, 201699).
Whether you have dates or just month values in your table, you could use a date column to store dates/months in your fact tables and in the month dimension. This would not only save storage space (three bytes, as opposed to four with int), it would also allow for your queries to use date functions among other gains. The same logic still applies with regards to partitioning.
You can still achieve what's known as "partition elimination" (querying a single partition) in your current setup, as long as you include the partition column in your query. With your current setup, that could look something like Select * from FactAccount A inner join DimMonth m on a.AccountMonthId = m.MonthId where M.Month = 2 and m.Year = 2016 and a.AccountYearId = 2016
You could partition on months instead of years. It'll take up the same disk space, although there are perhaps more partitions to manage, but this seems like a minor cost.
Adding an extra column for the year and partitioning on that column seems a bit lazy, ignorant, or at least strange of your DBA.
We have hardly touched on the subject of indexing - indexes, be they aligned or non-aligned, are often much more important than partitioning as performance goes.


How to add constraint on a varchar column to have at least one non numeric character

A check constraint with a LIKE expression could be used to ensure at least one non-numeric character exists:

CHECK (YourColumn LIKE '%[^0-9]%')

The 0-9 range there might not be exactly right for you, depending on the collation of the varchar column. For example, using the common Latin1_General_CI_AS collation, the following additional characters would be considered numeric by the 0-9 test:

²
³
¹
¼
½
¾

If that is a concern, expand the range explicitly:

CHECK (YourColumn LIKE '%[^0123456789]%')

I would expect negligible performance impact in either case.


SQL 2000 - tsql to find out failed jobs in last 24 hours

SELECT
    j.[name]
  , jh.run_date
  , jh.run_time
  , jh.sql_severity
  , jh.message
FROM
    msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j
ON  j.job_id = jh.job_id
WHERE
    jh.run_status = 0 -- Failure
AND jh.run_date > DATEADD(DAY, -1, GETDATE())
ORDER BY
    jh.run_date DESC


Oracle no privileges on tablespace USERS

You are granting the privileges to the incorrect user.

The schema owner i owns the table, and is therefore the user that needs to be granted the relevant permissions on the tablespace.


PostgresSQL: Cannot get to console under different user name

psql assumes you want to connect to the database named as the current user if you don't provide any login details.

But you can ovverride the defaults using parameters to psql, there is no need to use sudo in order to connect as a different username.

Simply run:

psql -U username -d databasename

where username is the user you want to use and databasename the database you want to connect to.

If you don't know which database you can connect to, use

psql -l

to show them all.

All this and a lot more is nicely documented in the manual: http://www.postgresql.org/docs/current/static/app-psql.html


SQL injection in Postgres functions vs prepared queries

It depends.
SQL functions
With LANGUAGE sql, the answer is generally yes.
Passed parameters are treated as values and SQL-injection is not possible - as long as you don't call unsafe functions from the body and pass parameters.
PL/pgSQL functions
With LANGUAGE plpgsql, the answer is normally yes.
However, PL/pgSQL allows for dynamic SQL where passed parameters (or parts) are concatenated to a query string and executed with EXECUTE. This can convert user input to SQL code and makes SQL injection possible. You cannot tell from outside whether the function body handles it properly. Tools are provided.
Only use dynamic SQL where you need it. Plain SQL statements using parameters as values are safe against SQL injection like SQL functions.
For dynamic SQL, preferably pass values as values with:

the USING clause. Example.

Makes SQL injection impossible on principal.
If you concatenate values in the SQL string, use:

format() with format specifier %L. Example.
quote_literal() or quote_nullable(). Example.

Wraps strings in single-quotes safely, thereby avoiding syntax errors and SQL injection.
Process parameters that shall be treated as identifiers in the SQL string with:

format() with format specifier %I. Example.
quote_ident(). Example.
a cast to a registered type - regclass for table names:  _tbl::regclass. Example.

Encloses identifier strings in double-quotes safely where required, thereby avoiding syntax errors and SQL injection.
Related:

Refactor a PL/pgSQL function to return the output of various SELECT queries
Define table and column names as arguments in a plpgsql function?

Never just build a string from user input and execute. This includes identifiers, directly passed by a user or fetched from a system catalog. Everything has to be treated like user input and quoted appropriately when building dynamic SQL!
More about performance implications in this related answer:

Function Performance

Basics on SQL-injection:

http://bobby-tables.com/

Similar considerations apply to other server-side languages that allow dynamic SQL.


Can a Movie have > 1 director (like the Marvel movies and the brothers), if so this should be a many to many.

Can a movie win more than one award? If so it should be a many to many.

Can a move EVER be shot at > 1 studio? If so you need a many to many.

You solve the many-to-many by adding an intermediate table in the logical design phase. That new table has a composite key consisting of the foreign keys from the two tables participating in the relationship.

For example, for Movie and Director, you would have Move, MovieDirector, and Director. MovieDirector would have only 2 columns: MovieID and DirectorID.


There is no definite answer to whether "it is worth the trouble". That's really a matter of taste and style and the total picture we don't have. If in doubt, just don't bother, that's typically the best course of action.

For small, simple tables where you rarely delete rows, it might be convenient for human users to remove gaps from primary keys. However, you should never rely on the absence of gaps if it is at all possible to update IDs or delete rows.

For bigger tables or with ongoing write activity, I wouldn't bother to begin with.

That said, here is a simple procedure, as long as you don't have massive concurrent access to the tables (in which case I also wouldn't bother):

If all your foreign keys are defined ON UPDATE CASCADE, the changes propagate to depending columns.

Do not use "oid" as identifier. This is traditionally used for OIDs and you don't want to cause confusion. Using a sane name for the PK column instead.

To avoid unique violations in intermediary states, you must make way for new IDs. Assuming all your IDs are positive integer numbers:

BEGIN;
LOCK tbl;

-- flip IDs to negative
UPDATE table_a SET table_a_id = table_a_id * -1;

-- assign new gap-less numbers
UPDATE table_a a
SET    table_a_id = u.rn
FROM  (
   SELECT table_a_id, row_number() OVER (ORDER BY table_a_id DESC) AS rn
   FROM   table_a
   ) u
WHERE a.table_a_id = u.table_a_id;

COMMIT;

The (often better) alternative is to just forget about those gaps and use a VIEW with row_number() where you really need gap-less numbers.

Related later answer with another alternative:

Compacting a sequence in PostgreSQL


i am trying to figure out if I should follow the same design as i have when using Domain accounts.

Domain user accounts were both over-used, and over-shared.
They were over-shared to reduce the burden of password maintenance.  With MSA/gMSA you should provision separate accounts for each service that actually needs a domain account, but that shouldn't be too many because you should be using domain accounts less these days than in the past.
They ware over-used because before the service-hardening work in Windows they actually were a best-practice.  But with the adoption of virtual service accounts/per-service SIDs, and the move to virtualization reducing the incidence of large, multi-purpose servers, you should be using the using the default virtual accounts for local permissions, and the computer account for network permissions.
So you should use the default virtual accounts whenever you don't have a specific need for a domain accounts.  The specific needs are typically limited to:

You have a cluster and need the same service account on all nodes, or

You have many different services on the same server and don't want to provision network privileges to the computer account.


MySQL does not have a way to determine the information you're looking for (client attempting the connection). The only thing you will get from a direct connection attempt is the username and hostname (as noted above).

MySQL doesn't utilize or pass User-Agent-like data in that manner.


db.comments.find( { 'reply.recomm' : '13' })
db.comments.find( {'_id' :  ObjectId("5a6d9a1ee183f06dfd91dbf0"), 'reply.recomm' : '12' })

Both above query is looks fine and will be fetch the documents of records inside the comments collections.

I am sure that you are doing mistake something like not in the tree of particular database  where your comments collection reside.

Let's from beginning see here,

First i have create the StackExchange database.

>Use StackExchange

Then i have created the comments collection inside the StackExchange database.

> use StackExchange
switched to db StackExchange
>

> db.createCollection("comments")
{ "ok" : 1 }

And inside of comments collections i have insert the documents, which you have provided in souce code.

> db.comments.insert({
...     "_id" : ObjectId("5a6d9a1ee183f06dfd91dbf0"),
...     "date" : "20180126",
...     "oid" : "000",
...     "aid" : "0000000000",
...     "currentavalible" : "7",
...     "deletedbyuser" : "0",
...     "reply" : [
...         {
...             "oidaid" : "000000",
...             "nickname" : "****",
...             "date" : "2018-01-26 07:32:45",
...             "recomm" : "13",
...             "unrecomm" : "0",
...             "contents" : "aaaaaaaaaaaa"
...         },
...         {
...             "oidaid" : "000088",
...             "nickname" : "para****",
...             "date" : "2018-01-26 08:29:54",
...             "recomm" : "12",
...             "unrecomm" : "0",
...             "contents" : "bbbbbb"
...         }
...     ]
... })
WriteResult({ "nInserted" : 1 })

Now i want to fetch comments documents records from the comments collection, which is in StackExchange database.
First check through mongo shell, what is your current database like that

> db
StackExchange
>

Here it mongo shell the current database is StackExchange.

Note:- I have create comments collection in StackExchange database. But for your confirmation , you must always checks before queering the query in mongo shell like

  show collections
  comments

Then try to find the documents inside the collection through the db.collection.find().

  Note:- Here collection is the collection name. In your case it's
  comments.

From the first query db.comments.find( { 'reply.recomm' : '13' })

> db.comments.find( { 'reply.recomm' : '13' }).pretty()
{
        "_id" : ObjectId("5a6d9a1ee183f06dfd91dbf0"),
        "date" : "20180126",
        "oid" : "000",
        "aid" : "0000000000",
        "currentavalible" : "7",
        "deletedbyuser" : "0",
        "reply" : [
                {
                        "oidaid" : "000000",
                        "nickname" : "****",
                        "date" : "2018-01-26 07:32:45",
                        "recomm" : "13",
                        "unrecomm" : "0",
                        "contents" : "aaaaaaaaaaaa"
                },
                {
                        "oidaid" : "000088",
                        "nickname" : "para****",
                        "date" : "2018-01-26 08:29:54",
                        "recomm" : "12",
                        "unrecomm" : "0",
                        "contents" : "bbbbbb"
                }
        ]
}

and from the query
 db.comments.find( {'_id' :  ObjectId("5a6d9a1ee183f06dfd91dbf0"), 'reply.recomm' : '12' })

> db.comments.find( {'_id' :  ObjectId("5a6d9a1ee183f06dfd91dbf0"), 'reply.recomm' : '12' }).pretty()
{
        "_id" : ObjectId("5a6d9a1ee183f06dfd91dbf0"),
        "date" : "20180126",
        "oid" : "000",
        "aid" : "0000000000",
        "currentavalible" : "7",
        "deletedbyuser" : "0",
        "reply" : [
                {
                        "oidaid" : "000000",
                        "nickname" : "****",
                        "date" : "2018-01-26 07:32:45",
                        "recomm" : "13",
                        "unrecomm" : "0",
                        "contents" : "aaaaaaaaaaaa"
                },
                {
                        "oidaid" : "000088",
                        "nickname" : "para****",
                        "date" : "2018-01-26 08:29:54",
                        "recomm" : "12",
                        "unrecomm" : "0",
                        "contents" : "bbbbbb"
                }
        ]
}
>

But from the mention below both query you shall not fetch the documents records from the comments collection due to their improper fetching of Array records.

db.comments.find( { 'reply' :{ 'recomm' : '13'}  })

db.comments.find( {'_id' :  ObjectId("5a6d9a1ee183f06dfd91dbf0"), 'reply' : { 'recomm' : '12'} })

As per MongoDB 3.6 Documented Here . If you want to fetch the array documents then you can use $ (projection), Or you can use Aggregation Pipeline Stages.

As you said only you want to find the records in reply that  "recomm" is 13.
so, you have to use  $elemMatch (projection)operator and your query will be as mention below

> db.comments.find({"_id" : ObjectId("5a6d9a1ee183f06dfd91dbf0")},{reply: {$elemMatch: {recomm: "13"}}}).pretty()
{
        "_id" : ObjectId("5a6d9a1ee183f06dfd91dbf0"),
        "reply" : [
                {
                        "oidaid" : "000000",
                        "nickname" : "****",
                        "date" : "2018-01-26 07:32:45",
                        "recomm" : "13",
                        "unrecomm" : "0",
                        "contents" : "aaaaaaaaaaaa"
                }
        ]
}
>

After the OP requirement to edit the code and execute the query to find out OP desire results.

Here i want to insert the second documents which having different id field records in comments documents.

> db.comments.insert({
... ...     "_id" : ObjectId("5a6d9a1ee183f06dfd91dbf1"),
... ...     "date" : "20180126",
... ...     "oid" : "000",
... ...     "aid" : "0000000000",
... ...     "currentavalible" : "7",
... ...     "deletedbyuser" : "0",
... ...     "reply" : [
... ...         {
... ...             "oidaid" : "000000",
... ...             "nickname" : "****",
... ...             "date" : "2018-01-26 07:32:45",
... ...             "recomm" : "13",
... ...             "unrecomm" : "0",
... ...             "contents" : "aaaaaaaaaaaa"
... ...         },
... ...         {
... ...             "oidaid" : "000088",
... ...             "nickname" : "para****",
... ...             "date" : "2018-01-26 08:29:54",
... ...             "recomm" : "12",
... ...             "unrecomm" : "0",
... ...             "contents" : "bbbbbb"
... ...         }
... ...     ]
... ... })
WriteResult({ "nInserted" : 1 })

Now i only find out the comments collection documents , which having the "recomm":"13" records.

> db.comments.find({},{reply: {$elemMatch: {recomm: "13"}}}).pretty()
{
        "_id" : ObjectId("5a6d9a1ee183f06dfd91dbf0"),
        "reply" : [
                {
                        "oidaid" : "000000",
                        "nickname" : "****",
                        "date" : "2018-01-26 07:32:45",
                        "recomm" : "13",
                        "unrecomm" : "0",
                        "contents" : "aaaaaaaaaaaa"
                }
        ]
}
{
        "_id" : ObjectId("5a6d9a1ee183f06dfd91dbf1"),
        "reply" : [
                {
                        "oidaid" : "000000",
                        "nickname" : "****",
                        "date" : "2018-01-26 07:32:45",
                        "recomm" : "13",
                        "unrecomm" : "0",
                        "contents" : "aaaaaaaaaaaa"
                }
        ]
}


How can this unique index allow duplicate rows?

The answer can be found in the amazing documentation .. it LOOKS like you have NULL values in your tables..  When the DB checks for uniqueness, it says "does NULL equal NULL?  NOPE!" and allows it.

The important bit below (emphasis mine):

  are not allowed. Null values are not considered equal. A multicolumn unique

If you want to keep the uniqueness across ALL THREE COLUMNS and, at the same time, treat nulls as equal, then you have to get creative with your UNIQUE indexes by making them partial indexes..

CREATE UNIQUE INDEX ix1 ON table (col1, col2wNull, col3wNull) WHERE col2wNull is not NULL and col3wNull is not Null;
CREATE UNIQUE INDEX ix2 ON table (col1, col2wNull) WHERE col2wNull is not Null and col3wNull is Null;
CREATE UNIQUE INDEX ix3 ON table (col1, col3wNull) WHERE col2wNull is Null and col3wNull is not Null;
CREATE UNIQUE INDEX ix4 ON table (col1) WHERE col2wNull is NULL and col3wNull is NULL;

As you can see .. it can easily get a bit crazy.

A different alternative would be to make col2wNull and col3wNull be defined as NOT NULL and provide some default value for when nothing is supplied. THIS MAY OR MAY NOT BE A GOOD IDEA depending on what you're doing. "Magic values" have a tendency to give you lots of problems later.

With regard to your edit and the two strings appearing to be equal, but the database reporting that they are not - I can only imagine that there are some "invisible" characters (UTF-8?) that are in the string. Or it could be something as simple as one string has an additional space on the end. It partially depends on how you are saving it into the database. (Are you performing a trim() on them, lower(), etc..)

You could try comparing the strings in various other ways (such as looking at md5 hash).  I believe you can ask postgres to convert the column value into hex to view, as well, but how to go about doing that is escaping me at the moment (my apologies).


Best backup strategy for MySQL (MariaDB) replication set

Replication status and lag are vital monitors you should take care of.  Before start a backup you must know if your slave goes well.

A simple show slave status will show you all needed infos:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.40.61
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.021934
          Read_Master_Log_Pos: 205924047
               Relay_Log_File: relay-bin.004199
                Relay_Log_Pos: 205924192
        Relay_Master_Log_File: mysql-bin.021934
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 205924047
              Relay_Log_Space: 205924384
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

The mains "counters" are Slave_IO_Running and Slave_SQL_Running for replication status and Seconds_Behind_Master for lag (ideally at 0 second).

If you have a slave dedicated to backups (that is a good practice), I recommend you to make a binary copy of  your datadir instead (or in addition) of your mysqldump. The restore will be much more easier and quick. However mysqldump is good if you want to restore a partial backup (especially InnoDB tables) or restore a clean shrinked dataset.

If your are afraid by corrumption or delta between Master and Slaves you can use the Percona tool pt-table-checksum (available in the Percona Toolkit) that "Verify MySQL replication integrity" easily.

Max.


Does SQL Server allow moving backwards with standby file?

You must restart the restore sequence.

I believe SQL Server could theoretically allow  moving backwards in time because the logs contain both undo and redo information. But this is either not implemented or it is not possible for some reason.


Import data extracted from website directly into PostgreSQL database without using any text or csv file

You can open a pipe and write your data to the pipe directly. Related answer with demo code:

Insert SQL statements via command line without reopening connection to remote database

Or you can use COPY with a program or standard input directly:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

  ...

  When PROGRAM is specified, the server executes the given command and
  reads from the standard output of the program, or writes to the
  standard input of the program. The command must be specified from the
  viewpoint of the server, and be executable by the PostgreSQL user.
  When STDIN or STDOUT is specified, data is transmitted via the
  connection between the client and the server.


The query needs ORDER BY in the end, not in the subqueries (and note that the parentheses are optional there, at least in standard SQL, not sure if Vertica requires them):

( SELECT country_id, country_name
  FROM country_dim
  WHERE iso_country_code LIKE 'US')
UNION
( SELECT country_id, country_name
  FROM country_dim
  WHERE iso_country_code NOT LIKE 'US')     -- without ORDER BY
ORDER BY
    CASE WHEN iso_country_code LIKE 'US' THEN 0 ELSE 1 END ASC,
    country_name ASC ;

But since you are grabbing all the data from the same table, there is no need for the UNION and the subqueries:

SELECT country_id, country_name
FROM country_dim
ORDER BY
    CASE WHEN iso_country_code LIKE 'US' THEN 0 ELSE 1 END ASC,
    country_name ASC ;


Access denied when disabling agent job, despite SqlAgentOperator membership

Please check to see if there is a DENY on the sp_update_job procedure.  This may be a direct DENY to your login or it may be a DENY on some group that has your login as a member.  DENY  always trumps a grant.

If so, then removing your login from the group or having an administrator REVOKE the DENY should solve the problem.

Response prompted by reading Piers7 request.  Time has passed, of course.


it’s safe to run mysqltuner on production server

Most monitoring services have small enough impact that they can (and should) be run on production servers.

mysqltuner -- once in a while.  (Tuning is not something that needs repeating very often.)
SHOW GLOBAL STATUS -- periodically.  (Some services use this to build graphs.)
Have the slowlog turned on (continually) even with a low, but non-zero, long_query_time.  (This is my favorite tool for getting useful clues about performance problems.)
performance_schema (early versions had a noticeable impact)
But not the general log -- This is likely to fill up the disk too fast.  When necessary, use it only briefly.

Here's what I request:  http://mysql.rjweb.org/doc.php/mysql_analysis  It includes

one-time SHOW VARIABLES and SHOW GLOBAL STATUS, then run that against about 200 checks.
one-time slowlog digest (after it has been running at least a day) -- followed by an analysis of the "worst" queries.  This often leads to improving the indexes and/or re-formulating some queries.  It may even lead to architectural changes to get away from problematical design patterns.


In Failover Cluster Manager, right-click on the cluster, select More Actions, and click on Destroy Cluster.