You can temporarily change QUOTED_IDENTIFIER.

DECLARE @TestHyphens TABLE(StoreName varchar(500),topItemSale varchar(500))

INSERT INTO @TestHyphens (StoreName, topItemSale) VALUES
('Bob''s Burger''s','Pen'),
('Jacks Coffee Shop','Pencil'),
('Larry''s Hair Shack','Rainbow'),
('Manny''s Sandwich Shop','Sunglasses');

SET QUOTED_IDENTIFIER OFF

SELECT StoreName, TopItemSale
FROM   @TestHyphens
WHERE  StoreName = "Bob's Burger's"

SET QUOTED_IDENTIFIER ON

| StoreName      | topItemSale |
|----------------|-------------|
| Bob's Burger's | Pen         |

Rextester here

Using within the cursor

DECLARE @sql nvarchar(MAX), @storename varchar(500)

DECLARE db_cursor CURSOR FOR
SELECT StoreName
FROM #TestHyphens

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @storename

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @SQL = N'SET QUOTED_IDENTIFIER OFF '
               + N'Select * from #TestHyphens where StoreName = "' + @storename + N'"'
               + N'SET QUOTED_IDENTIFIER ON';

    EXECUTE SP_EXECUTESQL @SQL;

    FETCH NEXT FROM db_cursor INTO @storename
END

CLOSE db_cursor
DEALLOCATE db_cursor

This is the result:

| StoreName             | topItemSale |
|-----------------------|-------------|
| Bob's Burger's        | Pen         |
| Jacks Coffee Shop     | Pencil      |
| Larry's Hair Shack    | Rainbow     |
| Manny's Sandwich Shop | Sunglasses  |

Rextester here


"TRUNCATE doesn't run ON DELETE trigger" true or false

Your first link (Why is truncate DDL?) is about Oracle, not MySQL. While relevant, different DBMS implement same features differently. In Oracle and SQL Server TRUNCATE is DDL, not DML.

In MySQL, it matter which version you use:

Until version 5.1, the documentation says:

  Logically, TRUNCATE TABLE is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

  For an InnoDB table:

  If there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one.
  When you use this fast truncation technique with the innodb_file_per_table option enabled, the operating system can reuse the freed disk space. For users of the InnoDB Plugin, the space is reclaimed automatically, as described in Reclaiming Disk Space with TRUNCATE TABLE. If you do not have the InnoDB Plugin installed, issue the OPTIMIZE TABLE statement to free the disk space for the table.
  If there are any FOREIGN KEY constraints that reference the table, InnoDB processes TRUNCATE TABLE by deleting rows one by one, processing the constraints as it proceeds. If the FOREIGN KEY constraint specifies DELETE CASCADE, rows from the child (referenced) table are deleted, and the truncated table becomes empty. If the FOREIGN KEY constraint does not specify CASCADE, the TRUNCATE TABLE statement deletes rows one by one and stops if it encounters a parent row that is referenced by the child, returning this error:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign
key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1`
FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

But for versions 5.5 and later, the documentation says:

  Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, it bypasses the DML method of deleting data. Thus, it cannot be rolled back, it does not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships.

  Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways in MySQL 5.5:

  ...


How to convert rows into columns in sql server using pivot or with out pivot

You need to UNPIVOT and then PIVOT.

;WITH x AS
(
  SELECT ServiceName, num, sn
  FROM (
    SELECT ServiceName,
      HeatExchanged         = CONVERT(varchar(32), HeatExchanged),
      MTD                   = CONVERT(varchar(32), MTD),
      PerfTube_PressureDrop = CONVERT(varchar(32), PerfTube_PressureDrop),
      Design_TubeOD         = CONVERT(varchar(32), Design_TubeOD),
      Design_NoTubes        = CONVERT(varchar(32), Design_NoTubes)
    FROM dbo.Quotes_Output_Thermal
  ) AS d
  UNPIVOT (num FOR sn IN
    (HeatExchanged, MTD, PerfTube_PressureDrop, Design_TubeOD, Design_NoTubes)
  ) AS unp
)
SELECT ServiceName = sn, CLO, IC, AC FROM x
PIVOT (MAX(num) FOR ServiceName IN (CLO,IC,AC)) AS p;

Though wanting a different set of values called "ServiceName" in the output might be confusing for consumers of the query.

Couple of caveats:

I have no idea where QuoteServiceID fits in from your question.
I assumed that this is all of the data. You have to explicitly name the columns twice, and the ServiceName values once. If you have 40 more service names and a bunch of additional measurement columns, you can use dynamic SQL to build those commands - but that is an order of magnitude more complicated.
There is no straightforward way to get the ordering you showed in the question - the (un)pivoting will yield seemingly arbitrary results that don't match the physical ordering of the columns in the table. You can use a CASE expression or charindex against a hard-coded list, but you'd have to manually specify the columns a 3rd time.


It's a bit hard to tell how your tables are linked together, but I think the following should be more efficient:

WITH weeks AS (
  SELECT make_interval(weeks => n) as week
  from generate_series(0, 3) AS n
)
SELECT week,
       count(x.user_email) as "Returning Users"
FROM weeks
  LEFT JOIN (
     SELECT act.user_email, age(act.timestamp, acc.timestamp) as diff
     FROM accounts acc
       JOIN action act on act.user_email = acc.user_email
  ) x ON x.diff >=  week and diff < week + '1 week'::interval
FROM weeks
GROUP BY week;


How to check existence in Oracle without scanning the entire table?

Just combine the two:
SELECT count(*)
FROM DUAL
WHERE EXISTS (
    SELECT 1
    FROM foo
    WHERE bar = 'baz'
)


Connecting to mySQL from 2 different machines

So what you can do :

If you want to change IP from 192.168.1.4 to 192.168.1.3 then you can update mysql.user table

Update mysql.user  set host =' 192.168.1.3 where host='192.168.1.3' ;

Flush privileges:

You cannot define a range of hostname in single query of creating user. Either you need to create same user for different IP or you can give % as hostname but i will not recommend this

If you want different user , create same user with required IP.

Let me know if need more clarity

EDIT

You cannot define a range for hostname in mysql.user table.
To have this you can create same user with same role but on different IP. You have to do this for every IP individually.

UPDATE

Please try creating user :

grant select to 'root'@'192.168.1.%' on db.* Identified by 'pass' ;

UPDATE 2

This is because IP should be placed between '' as a string.

So here you go:

GRANT ALL PRIVILEGES ON db_name.table_name TO 'root'@'192.168.1.%' IDENTIFIED BY '' WITH GRANT OPTION;


SQL Server - How to improve/optimize remote scan on a linked server

Based on my personal experiences, you will get far better performance by transferring data between the two servers and performing the update on the target server.

After watching one of these at a more detailed level (i.e. amount of data transferred between the servers), I determined that at least 90% of the remote table is pulled over to the local server before comparisons are run. This may be improved by indexing your temp tables correctly, but no guarantees.

The reason is simple: The local server doesn't understand the distribution of the remote server. It doesn't know the file structure, indexing, or any such information, so it assumes that it will have to do a table scan to identify what needs to be changed (at least this is my assumption based on experience.) Manually transferring the data to the server to be updated, then running a process there to do the update is the only way I achieved reasonable increases in efficiency.

It is awkward, and I hope someone has a better answer for you, but there you have it - my personal success story for optimizing these types of cross-server queries between the cloud and a local server.


How to make sure a record is only retrieved once

I'm assuming this is the following case:

You can look up coupon multiple times (example: verifying the coupon exist)
Once you use the coupon something the coupon becomes invalid

Easiest way is to have two fields called isUsed int, ExpirationDate datetime
Then create two APIs that do the following:

CheckCoupon (coupon id)

This checks against the table for coupon id, ExpirationDate, isUsed. If the stored procedure returns 1, the coupon is valid, else invalid.

Create a stored procedure called usp_check_coupon

SELECT count(*)
FROM Coupon
Where id = @id
and ExpirationDate > [today's_date]
and IsUsed = 0

UseCoupon (coupon id)

Marks the coupon as used. Before updating IsUsed to 1, check to make sure IsUsed is 0.

Create a stored procedure called usp_use_coupon

DECLARE @isused int =
    SELECT count(*)
    FROM Coupon
    Where id = @id
    and ExpirationDate > [today's_date]
    and IsUsed = 0

IF (@isused == 1)
BEGIN
    UPDATE COUPON
    SET IsUsed = 1
    WHERE ID = @id

   SELECT 1 --Updated Successfully
END

SELECT 0 --Updated Failed

Then on the API, if usp_use_coupon returns 0, that means the coupon is used or expired. If 1, it has been processed.


Oracle DBID what is logic to create oracle DBID?

DBID is a numeric identifier that utilities like RMAN use to identify databases.

You can get your DBID like this:

select DBID from v$database;

However it NOT unique especially if you have copies of your database where you hot start them with:

recover database until change <SCN> using backup controlfile;

but if you don't have (control file) copies, then it is unique.

Because it's a number it's not a nice way to uniquely identify databases.

What we do to uniquely identify our databases across the whole company, is to create TNS entries in the form of "SID.host" because SID is unique on the host, and then use this format in our global tnsnames.ora.


PostgreSQL - pgAdmin error creating a server

You need to enter the password of the database user, not the OS user. The "root password" is not applicable. Do you know the password of the database user postgres?

Most systems are set up allowing peer authentication for local connections, meaning a system user can log into the database with a database user of the same name without password. localhost is not a local connection. Its privileges are determined by host entries in your pg_hba.conf file.

Since you seem to be running Postgres and pgAdmin on the same server, I suggest you try a local connection without password. Quoting the pgAdmin manual:

  On Unix based systems, the address field may be left blank to use the
  default PostgreSQL Unix Domain Socket on the local machine, or be set
  to an alternate path containing a PostgreSQL socket.

Bold emphasis mine.
Of course, you have to run pgAdmin as the system user postgres for this to work.

This related answer on SO has a lot more details and links.


Get max, min, avg value within each 1 day range in sql server

you can create a recursive CTE that will result in a table returning all dates between the give dates, and then JOIN this with your result:

WITH abc as
(
 SELECT CAST('2019-04-20' as date) as i
 UNION ALL
 SELECT DATEADD(d,1,i)
 FROM abc WHERE i < CAST('2019-04-25' as date)
)
SELECT
  ISNULL(AVG(Value),0) AS VALUE,
  CAST(Timestamp AS DATE) AS TimeStamp
FROM abc
LEFT JOIN MyTable on TimeStamp = abc.i
GROUP BY abc.i


Your expression is too complex.  There are many severe limitations of what can go into BY RANGE.  Use simply UNIX_TIMESTAMP(time).  Even if it would work, FLOOR does not add anything to the expression.

BTW, the trailing hh:mm:ss is optional in this situation.  These are identical:

UNIX_TIMESTAMP('2015-08-01 00:00:00')
UNIX_TIMESTAMP('2015-08-01')

Now, on to my favorite question:  Why are you using PARTITIONing?  PARTITIONingdoes not inherently provide any performance.  There are only a few use cases I know of where you can trick PARTITION into providing more performance than a carefully chosen index.


Nested json response from 3 one to many tables

In PostgreSQL:

SELECT
    row_to_json (r1.*)
FROM
    (
        SELECT
            table_1_id,
            somedata,
            array(
                SELECT
                    row_to_json (r2.*)
                FROM
                (
                SELECT
                        table_2_id,
                        somedata,
                        array(
                            SELECT
                                row_to_json(r3.*, true)
                            FROM
                            (
                                SELECT
                                    table_3.table_3_id,
                                    table_3.somedata
                                FROM
                                    table_3
                                WHERE
                                    table_3.table_2_id = table_2.table_2_id
                                ORDER BY
                                    table_3_id
                            ) r3

                        ) AS table_2_data
                  FROM
                        table_2
                 WHERE
                        table_2.table_1_id = table_1.table_1_id
                 ORDER BY
                        table_2_id
                ) r2
           ) AS table_2_data
        FROM
            table_1
    ) AS r1

(with a few caveats, because your first level response is not an array; and I think it should).

The syntax is a bit convoluted and can probably be simplified

Test it at http://rextester.com/SEZ32413


MySQL - Search Alphanumeric string using wildcards - underscore (_) and asterisk (*)

If you want to eliminate the 177 value, you have to do the following (see the fiddle here):
CREATE TABLE p (p_id VARCHAR (25));

and populate it:
INSERT INTO p VALUES
('110177'),
('110177T'),
('177');

and then run the following SQL:
SELECT * FROM p WHERE p_id LIKE '___177%';

Result:
p_id
110177
110177T

Note that the ___177% (3 underscores) predicate here will pick up all values that have 3 characters (any single character) followed by 177 followed by any other characters or none.
This is due to the difference between the % (percent) wildcard and _ (underscore) - the _ means that the placeholder represents one, and precisely one character whereas the % wildcard represents 0 or more characters.
So, the 177 isn't picked up because it has no characters before the 177 - it's  explained well here.
The != solution proposed by @Akina will also work but it implies knowing the values to be excluded in advance - my reading of your question is that you want to eliminate any really short product_ids and not just particular ones!
If you have more sophisticated requirements, you should take a look at regular expressions - an example from PostgreSQL can be found here - MySQL documentation here.
p.s. welcome to the forum!


DB2 removing white spaces in field values

If you define your XML document in a XML column, you can control how the whitespaces are stored.

For XML parsing, you can take a look a this page: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.xml.doc/doc/c0023533.html

To define the XML Schema, and how the whitespaces should be treated: http://www.w3schools.com/schema/schema_facets.asp

However, you are using a LOB and you are not exploiting the hybrid motor of DB2.


What is the benefit of creating an Oracle context over just granting package execution?

But I'm not seeing much benefit to a context at session level. I attach a package, but I still have to grant execute on it to any users I want to be able to use it. What am I missing?

Security

IMHO - Using SYS_CONTEXT() (instead of a Package) is a Security thing.  The only way to set SYS_CONTEXT() values is through a Package.  This package will most likely have access to data that the user does not.

This extra access can be done by:

Creating the Package as a Definer's Rights package
Creating the Package as an Invoker's Rights package and Grant it a Role (12.1+).

For extra security, the Context Package is not create in any of your DATA_SCHEMAs, CODE_SCHEMAs, or Parsing Schemas (eg the connected user or APEX Application Parsing Schema).  As such, I recommend you use place it in a separate "Security Schema".


MySQL - stay in cache priority for queries

First of all, there are two caches relevant to your query.

The Query cache uses SQL_(NO)_CACHE and is the less important cache.  For production systems with lots of writes, you may as well turn off the QC.  Every write to a table flushes all entries in the QC for the table(s) involved.  That leads to another point:  A large QC can lead to a lot of flushing activity.  Limit query_cache_size to no more than about 50M.  The QC can help with CPU.

The buffer_pool is the important cache for InnoDB.  You should give about 70% of RAM to innodb_buffer_pool_size.  That is where blocks of InnoDB data and indexes are cached.  It helps because it avoids much of the I/O.

Often, the most important thing to do is to identify the slowest queries (using SHOW PROCESSLIST and/or the slowlog) and work on speeding them up.  Sometimes adding an index, sometimes a minor change to a SELECT, etc, can speed up the system far more than tweaking the cache sizes.

The buffer_pool has a way to avoid letting a table scan blow out the cache.  The QC has no mechanism like what you are asking for.

If you do keep the QC on, then set query_cache_type=DEMAND and use SQL_(NO)_CACHE.


Partial backup and restore using filegroups

I don't know of anyway to do a schema backup or restore in SQL Server.  Your solution of putting all of the schema data on a filegroup will allow you to restore the database to another server, but will require that you restore the primary filegroup as well.  (note, this is possible on SQL Server Enterprise Edition versions 2005 and above). One problem for your situation might be that you could only have one of the partially restored databases running on the same instance of SQL Serve  This link covers the piecemeal restore process:  http://msdn.microsoft.com/en-us/library/ms177425.aspx


Why keeps Oracle writing into WARNING_SETTINGS$?

Oracle inserts into warning_settings$ when you compile an object that produces a compilation warning.

I traced an object creation with (ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL') & without (ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL') warnings enabled to show this.

With warnings set, we can see the following SQL in the trace file:

PARSING IN CURSOR #5 len=94 dep=1 uid=0 oct=2 lid=0 tim=1334927684277374 hv=2385338619 ad='d891dc48' sqlid='fdvfrjy72us7v'
insert into warning_settings$(obj#, warning_num, global_mod, property) values (:1, :2, :3, :4)
END OF STMT
PARSE #5:c=0,e=250,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1334927684277373

Interestingly, ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL' only seems to suppress the output of the warnings to SQL Plus. A trace still shows the table being populated by Oracle:

oracle@ironforge:/u01/app/oracle/diag/rdbms/phill11g2/PHILL11G2/trace$ tail -f PHILL11G2_ora_26759_PHIL_DBA.trc | grep -i warning
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL'
delete from warning_settings$ where obj# = :1
STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  WARNING_SETTINGS$ (cr=2 pr=0 pw=0 time=0 us)'
STAT #6 id=2 cnt=1 pid=1 pos=1 obj=254 op='INDEX RANGE SCAN I_WARNING_SETTINGS (cr=2 pr=0 pw=0 time=0 us cost=1 size=5 card=1)'
insert into warning_settings$(obj#, warning_num, global_mod, property) values (:1, :2, :3, :4)

Test PL/SQL snippet used for this & the warning it generated:

PHIL@PHILL11G2 > ALTER SESSION SET sql_trace = true;

Session altered.

PHIL@PHILL11G2 > ALTER SESSION SET tracefile_identifier = PHIL_DBA;

Session altered.

PHIL@PHILL11G2 >
PHIL@PHILL11G2 > ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

Session altered.

PHIL@PHILL11G2 > CREATE OR REPLACE PROCEDURE phil_1
  2  IS
  3  BEGIN
  4    NULL;
  5  END;
  6  /

SP2-0804: Procedure created with compilation warnings

PHIL@PHILL11G2 >
PHIL@PHILL11G2 > show errors;
Errors for PROCEDURE PHIL_1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1  PLW-05018: unit PHIL_1 omitted optional AUTHID clause; default
     value DEFINER used

PHIL@PHILL11G2 >


Find Without primary key Table in SQL Server 2008 R2?

Try this:

SELECT TABLE_NAME FROM information_schema.tables
WHERE TABLE_NAME
NOT IN(
SELECT TABLE_NAME from information_schema.table_constraints
WHERE constraint_type = 'Primary Key' )
AND TABLE_TYPE = 'BASE TABLE'

Edit: As per the comment from @MarkSinkinson this query will be wrong in case of Null value  information_schema.table_constraints.table_name, so I would advice the OP to follow with link added in comment which is

SELECT OBJECT_SCHEMA_NAME( object_id ) as SchemaName, name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'tablehasprimaryKey') = 0
ORDER BY SchemaName, TableName ;

to get the list of table not having Primary Key

Know more about this OBJECTPROPERTY which is a better solution here.


Database 'msdb' cannot be opened. it has been marked suspect by recovery

The fastest way is to restore the MSDB database, but if it's your first time doing that, here's an easier shortcut.

Restore the MSDB backup onto an existing (working) database server, but use a different database name than MSDB. The restore should go quickly (because MSDB is typically very small), and you can then verify that your objects are in there.
Detach the database, and copy the mdf/ldf files over to the broken instance.
Move the broken mdf/ldf files somewhere for safekeeping, and replace them with your newly restored mdf/ldf files.

Start the SQL Server instance again, and you're set.

One gotcha - in step 1, make sure you're restoring onto the same major version of SQL Server. If the broken server is SQL Server 2005, don't do the restore on SQL Server 2012, because the SQL 2005 instance won't be able to attach databases that have been touched by a newer version of SQL Server.


How can I generate an .mdf file containing a subset of my tables and rows?

With some clever jiggery-pokery with partitioning and filegroups you could get a particular sub-set of your data onto just one of the physical files used by a particular database.  Trying to attach that into an existing database at the recipient would be "challengining", I think.  In 20-some years of doing this stuff I've not heard of anyone trying that.*

My suggestions would be
a) copy your DB, delete the stuff you don't want to pass on, send a backup of that to the recipient
b) use BCP with the queryout option to dump just the required data to external files.  Send those to the recipient.

*I'm always willing to learn, however.


Problem Connecting Report Server to Report Server Database in SSRS

Re-run through the database configuration part to select the same database and it will re-issue the grants to make sure the permissions are in place, but make sure the account you run the wizard with has sysadmin access to the instance.  If it's 2005 you can generate the grant script but it's kind of buggy.


Yes, it is possible to do. There is a CREATE TABLE ... SELECT clause in MySQL that is designed specifically for that. I recommend you start with a simple SELECT statement that clearly defines what the new table would contain. E.g. if you have a mammals table:

CREATE TABLE mammals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO mammals (name) VALUES
    ('dog'),('cat'),('whale');

and a birds table

CREATE TABLE birds (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO birds (name) VALUES
    ('chicken'),('duck');

You can build this query:

SELECT name FROM mammals
UNION ALL
SELECT name FROM birds;

The result will contain all animals. However, you may need to keep the animal type in the new table. Modify the query like this:

SELECT name, 1 as type FROM mammals
UNION ALL
SELECT name, 2 as type FROM birds;

and create a new table

CREATE TABLE animaltype (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     type CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animaltype (type) VALUES
    ('mammal'),('bird');

Now, when you are completely happy with the result execute:

CREATE TABLE animals AS
SELECT name, 1 as type FROM mammals
UNION ALL
SELECT name, 2 as type FROM birds;

ALTER TABLE animals ADD COLUMN id INT AUTO_INCREMENT NOT NULL PRIMARY KEY;

Optionally:

DROP TABLE mammals;
DROP TABLE birds;

You can also create a foreign key for the animaltype.type column


Adding Column value from Previous Row based on Where Condition.

Try using over clause.
Over Clause Documentation

Select Symbol,
       Epoch,
       Volume,
       Sum(Volume)Over(Partition by Symbol,Substring(Epoch,1,6) Order by Epoch) AS CuVol
From YourTableName


Is it possible to move mongodb indexes to another file system?

To be clear, this feature is only available when using the WiredTiger storage engine.

To use, invoke mongod like so:

mongod --dbpath /tmp --storageEngine wiredTiger --wiredTigerDirectoryForIndexes

In your dbpath, you will see a directory called indexes where indexes will be stored.

Also update yaml as

storage:
  engine: wiredTiger
wiredTiger:
  engineConfig:
  directoryForIndexes: true

Kindly note to try this in test environment once.
Please ask if any further help is required.

Source: https://jira.mongodb.org/browse/SERVER-965


Get records with max value on grouped SQL results (mysql)

You can get max(created_dt) of each conversation and then join it with main table in this way:

select   tbl.*
from     tbl
join     (select   conversation_id, max(created_dt) created_dt
          from     tbl
          group by conversation_id) t1
on       tbl.conversation_id = t1.conversation_id
and      tbl.created_dt = t1.created_dt;

id | member_a_id | member_b_id | conversation_id | created_dt          | content
-: | ----------: | ----------: | --------------: | :------------------ | :------
 3 |         105 |         100 |               2 | 2018-04-01 14:00:00 | ccc
 5 |         100 |         110 |               1 | 2018-04-01 16:00:00 | eee

db<>fiddle here


In a WordPress/WooCommerce table the prices are hosed. How to realign them? (a key=value structure)

My translation of your problem is (slightly altered from your description):

For each post_id, find the value from meta_value column where meta_key = '_max_variation_regular_price' and put it in meta_value in all other rows with same post_id and where the meta key is a "price" (i.e. meta_key LIKE '%\_price')*.

Now, before we do anything and change the data in a production database, the best approach is to take a backup, restore the backup as another database and play around in the restored (copy) database, where we won't mind making mistakes.

Lets identify the rows that have the values we need:

-- query1
SELECT post_id, meta_value AS price
FROM ab_postmeta
WHERE meta_key = '_max_variation_regular_price' ;

If (post_id, meta_key) has a UNIQUE constraint, that's all the values we need. If not, we may have to take an additional step to make sure that the result has one value for every post_id and not more (skipping this step).

Now, lets find the rows that need updating:

-- query2
SELECT post_id, meta_key, meta_value
FROM ab_postmeta
WHERE meta_key LIKE '%\_price'
  AND meta_key <> '_max_variation_regular_price' ;

Then we can combine the two selects into one:

-- query3
SELECT upd.post_id, upd.meta_key,
       upd.meta_value  AS old_price,
       good.meta_value AS good_price
FROM ab_postmeta AS good             -- the table with the good prices
  JOIN ab_postmeta AS upd            -- the table to be updated
    ON good.post_id = upd.post_id
WHERE good.meta_key = '_max_variation_regular_price'
  AND upd.meta_key LIKE '%\_price'
  AND upd.meta_key <> '_max_variation_regular_price' ;

We can run this and inspect the results to see what an update will. No UPDATE run yet!. After that - and if the inspection seems right - we can run the actual UPDATE. Note that it's very similar to the previous SELECT. The WHERE clauses are identical and the FROM clause has become UPDATE. But we'll do it inside a transaction, just to be sure:

BEGIN ;           -- start a transaction.

UPDATE ab_postmeta AS good           -- the table with the good prices
  JOIN ab_postmeta AS upd            -- the table to be updated
    ON good.post_id = upd.post_id
SET
    upd.meta_value = good.meta_value
WHERE good.meta_key = '_max_variation_regular_price'
  AND upd.meta_key LIKE '%\_price'
  AND upd.meta_key <> '_max_variation_regular_price' ;

Lets run query2 again, to check the new values:

SELECT post_id, meta_key, meta_value
FROM ab_postmeta
WHERE meta_key LIKE '%\_price'
  AND meta_key <> '_max_variation_regular_price' ;

Now, is everything OK? Do all the updates look good?

If no, run ROLLBACK ; and no harm was done. Start again, by evaluating your algorithm or running with a smaller set.
If yes, run COMMIT ; and the changes will be permanent. You can safely do this (again) in production.

* Thnx to Andriy for the bugfix. the LIKE should escape the _ character with \_ as it's a wildcard.


Is it possible to find the user whose aggregated score reaches X value first?

First you will need a table with a column representing how many sales a seller has accumulated at a certain point in time:

SELECT
  `sellerId`,
  `saleDate`,
  ( SELECT SUM(`saleValue`)
    FROM `mzarzycki_test_sale` b
    WHERE b.`sellerId` = `a.sellerId`
    AND b.`saleDate` >= '2001-01-01 00:00:00'
    AND b.`saleDate` <= a.`saleDate`
    GROUP BY `sellerId`
  ) AS `saleSum`
FROM
  `mzarzycki_test_sale` a
WHERE
  `saleDate` >= '2001-01-01 00:00:00'
GROUP BY
  `sellerId`,`saleDate`
ORDER BY `sellerId`,`saleDate`

The rest is easy:

SELECT SaleDate,SellerId FROM
(...) c
WHERE c.Salesum >= 30
ORDER BY c.SaleDate
LIMIT 1

SQL Fiddle


Oracle average active sessions high threshold

To know whether 7 is high or low, you'd need to know something about the database server.

If, for example, you have a single CPU with 4 cores, 7 sessions would mean that, at any given time, 3 of your 7 threads were probably waiting to get scheduled for the CPU since your machine is only capable of running 4 sessions at any instant in time.  That would imply that your server was under a pretty decent load.  If, on the other hand, you have 4 CPUs each with 8 cores, then only 7 of your 32 cores were, on average, busy at any instant in time leaving 25 of your 32 cores free.  That would imply that your server itself wasn't particularly heavily taxed.

You'd also want to know a bit about the application.  A 15 minute spike where the average number of active sessions went up to 7 that was preceded and followed by a lower number would generally be less concerning than a report of a 24 hour period where the average number of active sessions was 7 because it's likely that there is a lot more variation over the 24 hour period (i.e. sometimes the number of active sessions was much higher than 7).  Application history also comes in to play.  If the application has always seen the average number of active sessions top out at 3 or 4 under peak load and it suddenly starts spiking to 7, that's more of a concern than if the application has always had those performance characteristics.


There's lots of options.

PowerShell
SSMS's Multi Server Query Functionality
Linked Servers
Batch File with a loop and sqlcmd variables


Your best option is the following:

STEP 01 : Create a SQL script that will write a Windows batch file
STEP 02 : Execute the SQL script to create the Windows batch file
STEP 03 : Execute the Windows batch file

STEP 01

First let's create the SQL file. The SQL file will have these rules

Find the Last Thursday as of Today
Locate the database
If today is Thursday and you run this script, then pick DB for the Thursday before that
Echo the Windows batch file, starting with @echo off

Here is the Windows batch file

SET @dborigin='rolando';
SET @today = DATE(NOW());
SET @this_thurs = @today - INTERVAL WEEKDAY(@today) DAY - INTERVAL 3 DAY;
SET @ut1 = UNIX_TIMESTAMP(@this_thurs);
SET @ut2 = UNIX_TIMESTAMP(@today);
SET @last_thurs = @this_thurs - INTERVAL IF(@ut1=@ut2,1,0) WEEK;
SET @user = 'root';
SET @pass = 'password';
SET @dumpfile = 'C:\\TransferDB.sql';
SELECT '@echo off';
SET @SQL='SELECT CONCAT(''mysqldump -u'',@user,'' -p'',@pass,'' --routines --triggers '',db,'' > '',@dumpfile) mysqldump_command FROM (SELECT schema_name db FROM information_schema.schemata WHERE LEFT(schema_name,6) = DATE_FORMAT(@last_thurs,''%y%m%d'') AND RIGHT(schema_name,LENGTH(@dborigin))=@dborigin) A';
PREPARE s FROM @SQL; EXECUTE s; DEALLOCATE PREPARE s;

Save the SQL script as C:\Make_mysqldump.sql

STEP 02 and 03

C:\> mysql -uroot -ppass -AN < C:\Make_mysqldump.sql > C:\Exec_mysqldump.bat
C:\> C:\Exec_mysqldump.bat

I will demonstrate Steps 01 and 02

DEMONSTRATION

Here the databases on my Windows machine

mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| 131128_1234_rolando |
| 131129_1234_rolando |
| 131130_1234_rolando |
| 131203_1234_rolando |
| 131204_1234_rolando |
| 131205_1234_rolando |
| 131206_1234_rolando |
| alisha              |
| beercan             |
| musicdata           |
| mysql               |
| performance_schema  |
| test                |
+---------------------+
14 rows in set (0.00 sec)

mysql>

Here is the text file:

C:\>type Make_mysqldump.sql
SET @dborigin='rolando';
SET @today = DATE(NOW());
SET @this_thurs = @today - INTERVAL WEEKDAY(@today) DAY - INTERVAL 3 DAY;
SET @ut1 = UNIX_TIMESTAMP(@this_thurs);
SET @ut2 = UNIX_TIMESTAMP(@today);
SET @last_thurs = @this_thurs - INTERVAL IF(@ut1=@ut2,1,0) WEEK;
SET @user = 'root';
SET @pass = 'password';
SET @dumpfile = 'C:\\TransferDB.sql';
SELECT '@echo off';
SET @SQL='SELECT CONCAT(''mysqldump -u'',@user,'' -p'',@pass,'' --routines --triggers '',db,'' > '',@dumpfile) mysqldump
_command FROM (SELECT schema_name db FROM information_schema.schemata WHERE LEFT(schema_name,6) = DATE_FORMAT(@last_thur
s,''%y%m%d'') AND RIGHT(schema_name,LENGTH(@dborigin))=@dborigin) A';
PREPARE s FROM @SQL; EXECUTE s; DEALLOCATE PREPARE s;

Here is the creation of the batch file and its contents:

C:\>mysql -AN < C:\Make_mysqldump.sql > C:\Exec_mysqldump.bat

C:\>type C:\Exec_mysqldump.bat
@echo off
mysqldump -uroot -ppassword --routines --triggers 131129_1234_rolando > C:\\TransferDB.sql

C:\>

CAVEAT : I'll leave the rest to your imagination to run the mysqldump and execute C:\TransferDB.sql on the target DB server.

Give it a Try !!!

UPDATE 2013-12-06 18:51 EST

Here is a crazy idea requiring some elbow grease

STEP #1

Remove the first line of C:\Make_mysqldump.sql do it looks like this:

SET @today = DATE(NOW());
SET @this_thurs = @today - INTERVAL WEEKDAY(@today) DAY - INTERVAL 3 DAY;
SET @ut1 = UNIX_TIMESTAMP(@this_thurs);
SET @ut2 = UNIX_TIMESTAMP(@today);
SET @last_thurs = @this_thurs - INTERVAL IF(@ut1=@ut2,1,0) WEEK;
SET @user = 'root';
SET @pass = 'password';
SET @dumpfile = 'C:\\TransferDB.sql';
SELECT '@echo off';
SET @SQL='SELECT CONCAT(''mysqldump -u'',@user,'' -p'',@pass,'' --routines --triggers '',db,'' > '',@dumpfile) mysqldump_command FROM (SELECT schema_name db FROM information_schema.schemata WHERE LEFT(schema_name,6) = DATE_FORMAT(@last_thurs,''%y%m%d'') AND RIGHT(schema_name,LENGTH(@dborigin))=@dborigin) A';
PREPARE s FROM @SQL; EXECUTE s; DEALLOCATE PREPARE s;

STEP #2

Create a new batch file (Call it C:\MakeBackupJob.bat) whose sole purpose is to make a dborigin variable and append the rest. It should have these lines:

@echo off
echo set @dborigin='%1'; > C:\MakeJob_%1.sql
type C:\Make_mysqldump.sql >> C:\MakeJob_%1.sql

EPILOGUE

To use C:\MakeBackupJob.bat, simply specify the database tag name. Suppose the databases are rolando,pamela,dominique,diamond. Simply run the following:

C:
cd \
MakeBackupJob.bat rolando
MakeBackupJob.bat pamela
MakeBackupJob.bat dominique
MakeBackupJob.bat rolando

You should have 4 SQL files in C:\

MakeJob_rolando.sql
MakeJob_pamela.sql
MakeJob_dominique.sql
MakeJob_diamond.sql

Now, run the following:

echo @echo off  > C:\Exec_mysqldump.bat
C:\>mysql -AN < C:\MakeJob_rolando.sql   >> C:\Exec_mysqldump.bat
C:\>mysql -AN < C:\MakeJob_pamela.sql    >> C:\Exec_mysqldump.bat
C:\>mysql -AN < C:\MakeJob_dominique.sql >> C:\Exec_mysqldump.bat
C:\>mysql -AN < C:\MakeJob_diamond.sql   >> C:\Exec_mysqldump.bat

Now, C:\Exec_mysqldump.bat contains 4 mysqldumps to separate files.

Give it a Try !!!


Did you get the very, very latest?

The latest stable build (here) is 5.1.19.  However, the bug fix has been put in place for 5.2.21 (per here).

The absolute latest version available is 5.2.34, which should have the bug fix.  However, that's not the latest stable version; it's just the latest generally available (GA) version.  (It's available here, for what it's worth.)

Edit

Since you have the latest version (with the alleged bug fix), I would report this as a new bug.

My only advice... Try to figure out if there is an exact sequence of steps that you can perform to make this happen every time.  Then give them all the information you can (including the version number you're using).


How to do a point in time recovery in postgresql?

AFAIK in recovery.conf file apply only for the duration of the recovery
it, this file provide the parameters that PostgreSQL needs to

perform an archive recovery of a database, or to act as a log-streaming.

replication standby.

you need to turn on archive_log
the log show you there is no "cp: cannot stat '/home/duc/backup/database/postgre_backupdb/000000020000000C00000042': No such file or directory" have no that file it mean database couldn't make checkpoint to archive it.
use some tool to do backup such as PG_RMNA (ex: pg_rman restore --recovery-target-time '2017-06-16 9:00:00' -B /BACKUP/pgrman -D $PGDATA -U postgres)


During installation you were prompted to add Windows users to the SQL instance. Did you add any? Unless you added yourself as a SQL user you won't be able to connect.

if you added Administrators as SQL users then you must run SSMS w/o the LUA token restriction, in other words you must right click the launch icon and select "run as Administrator"
if you did not add any user as  SQL user then you must follow the steps described in Connect to SQL Server When System Administrators Are Locked Out
if you don't know what you did and cannot follow the steps mentioned above, discard the VM and install SQL again. Pay attention to the installation procedure and make sure you add yourself as a valid SQL user. Read Database Engine Configuration - Account Provisioning for more details.

  All SQL Server Services are 'running'.

Make sure SQL Server Browser Service is running.


When should I reference the User record or User Profile record

If both tables - user and user_profile - have as primary key the column user_id, and there is a one-to-one mapping between rows in these tables, what you have there is a vertical partitioning of a single logical entity.
In the logical data model this should be represented as a single entity type. In the physical data model that single entity type is implemented as two tables. There are reasons why you would want to do this. Security is one, as you have mentioned, though that could be handled just as well with views.
I have not come across this implementation nor seen articles advocating it. I'm not convinced this paradigm is that common.
If there could be many rows in user_profile for each row in user then you have a simple parent-child pattern. Other tables will follow the usual pattern, too.
For other tables which have obvious one-to-many relationships to user your options are not a simple binary. In the logical model the situation is simple - entity type Payments references entity type User. In the physical implementation with vertically partitioned user table you have options. If either of user or user_profile is optional the foreign key must be declared as referencing the mandatory one. If both are required then it simply doesn't matter.
To be explicit, it is OK to join in columns which are not explicitly declared as foreign keys. So you could have the DRI written as .. payments.user_id references user.user_id and legitimately write .. from user_profile up inner join payments p on up.user_id = p.user_id. That is OK; it will work.
If you really wanted to, and your DBMS didn't object, you could even declare two foreign keys for the child table. One FK will link Payment to User. The second will link Payment to User_Profile. My feeling is that this is excessive and adds little, but it is not "wrong".
To answer the stated question, I would follow the principle of least surprise. If table Payment has a column user_id, and there exists a table called User I would expect Payment.user_id to reference User.user_id.


Load Balancing multiple databases in a multi-tenant setup

You can use Galera for multi-master replication. If you could move individual clients to specific nodes you will not run into locking issues.  It also has automatic recover of failed nodes.
http://galeracluster.com/


What happens when we call pg_dump while users are still using the system?

I was wondering about the state of this sql dump if the users are still using the system

pg_dump relies on PostgreSQL's MVCC support to ask PostgreSQL to take a snapshot of the database state at a given point in time.

Changes made by concurrent transactions after the point where pg_dump gets its snapshot do not affect pg_dump, whether they're committed or not.

There are a few things that aren't necessarily consistent, like sequences, which continue to increase while pg_dump is running. But applications have to be prepared for gaps in sequences anyway, so this isn't considered to be a problem.

  Also, I would like to know if pg_dump is the best choice for taking backups in this case

No, you're way better off using WAL archiving and PITR for your purpose. Look into PgBarman and read the docs on continuous archiving.

You should still take logical dumps for disaster recovery, but it need not be an all-the-time thing.


SQLite constraint violation - How to insert data to this schema?

First, do you have any data in movies_genres?
SELECT * FROM movies_genres;

If not, you can check if the primary key is missing in any of the sources tables
SELECT movie_id FROM movies GROUP BY movie_id HAVING COUNT(1) > 1;

and the same for genres. If above is ok, the error should not happen, but you can check with:
SELECT m.movie_id, g.genre_id
FROM ( -- I assume the parentheses was a typo and moved it
    SELECT movie_id
    FROM movies
    WHERE title = arg1
) AS m
CROSS JOIN ( -- see comment above
    SELECT genre_id
    FROM genres WHERE title = arg2
) AS g
GROUP BY m.movie_id, g.genre_id
HAVING COUNT(1) > 1;

You can btw simplify your query to:
SELECT m.movie_id, g.genre_id
FROM movies m
JOIN genres g
    ON m.title = arg1
   AND g.title = arg2


Removing duplicate rows with two or more left join tables

I have found a solution. I am not sure if it is optimal, but it works.

SELECT tbl_a.id, tbl_a.name,
       COALESCE( ( SELECT json_agg(tbl_b.*)
                     FROM tbl_b
                    WHERE tbl_b.a_id = tbl_a.id ), '{}'::json),
       COALESCE( ( SELECT json_agg(tbl_c.*)
                     FROM tbl_c
                    WHERE tbl_c.a_id = tbl_a.id ), '{}'::json)
  FROM tbl_a;

Which correctly returns

id  name    coalesce                             coalesce
--  ------  -----------------------------------  ----------------------
 1  "Test"  "[{"a_id":1,"foo":{"foo":"World"}},  "[{"a_id":1,"bar":{"bar":"abc"}},
              {"a_id":1,"foo":{"foo":"Hello"}},    {"a_id":1,"bar":{"bar":"def"}}]"


idle in transaction means pretty much what it suggests: there is an open transaction doing nothing at the moment.

What you see can be easily reproduced.  Do the following in one session (disable autocommit in your client if necessary - usually it isn't, an explicit BEGIN; will do that automatically):

test=>
    BEGIN;

    SELECT 1;

In an other session, let's see how it looks in pg_stat_activity:

test=> SELECT pid,
              datname,
              usename,
              xact_start,
              query_start,
              state,
              state_change,
              query,
              now()
       FROM pg_stat_activity;

pid          │ 13639
datname      │ test
usename      │ dezso
xact_start   │ 2015-10-23 10:14:36.853529+02
query_start  │ 2015-10-23 10:15:15.838252+02
state        │ idle in transaction
state_change │ 2015-10-23 10:15:15.838365+02
query        │ SELECT 1
now          │ 2015-10-23 10:16:24.340721+02

Here we see four different timestamps:

the time when the transaction started (xact_start),
the time when the query started (query_start),
the time when the transaction state last changed (state_change, I suppose it's the end of the query) and
the current timestamp (when the above query was run).

Between the last two the transaction is idling, but the last query is still shown.

To answer your question: many clients have autocommit disabled by default.  In this case, a transaction is started silently when the first command (SELECT 1;, for example) is issued - and then never closed.  In this case you'll see what you experience currently and I reproduced above.

AFAIK there is a way to persuade JBoss AS to turn it off.  This might help: https://developer.jboss.org/wiki/Non-transactionaldataaccessandtheauto-commitmode


Selection of first and last in non grouped item in SQL Server

Here are a couple of options:

   SELECT TOOLNAME,
           TOOLKEY,
           MAX(CASE WHEN Toolstatus = 'Running' THEN 'Running' END) Toolstatus1,
           MAX(CASE WHEN Toolstatus = 'Completed' THEN 'Completed' END) Toolstatus2
    FROM TOOLS
    GROUP BY TOOLNAME,
             TOOLKEY

It is eliminating nulls with the aggregate MAX() function in this code, if that's a concern, you may want this:

SELECT TOOLNAME,
       TOOLKEY,
       [Running] Toolstatus1,
       [Completed] Toolstatus2
FROM (SELECT TOOLNAME,
             TOOLKEY,
             TOOLSTATUS
      FROM TOOLS) AS src
PIVOT
(
MAX(TOOLSTATUS)
FOR TOOLSTATUS IN ([RUNNING], [COMPLETED])
) AS pvt


SVV_TABLE_INFO is the table I was looking for!


What precautions to take when moving a mirrored server

I would remove the witness from the session. The reason being that if the 1 online partner (principal) loses communication with the witness, the principal will take itself offline because it has no way to know for sure that the mirror isn't online.

There is no need to change the operating mode. When the principal becomes disconnected, it operates asynchronously. When the mirror reconnects, it will continue to operate asynchronously until they are synchronized, and then it will return automatically to operating synchronously.


Mysql error 28 no space left on the device

"no space left on the device" could also mean that the partition is out of inodes. Use df -i to check inode usage.

Edit: Each file, directory, and symbolic link requires one inode. So the idea is to remove some files from the / partition. It doesn't matter how large the files are. You can of course just pick some files on the partition and move them to another partition, if you can find some that are convenient to move.

It's unusual to run out of inodes, so it's possible you have some directory full of small or zero-length files somewhere that you're not aware of. Unfortunately, I don't know of an easy way to find this sort of thing. Running find / -xdev would print the name of every file and directory on the partition. You might be able to spot some suspicious directory full of empty files.

If you can add additional disk partitions to the system, you could pick some directory on the / partition and make it into a separate partition. Copy all of the files in that directory to the new partition, delete them from the / partition, then mount the new partition on top of the old (and now empty) directory.

The nuclear option would be to back up the / partition, wipe it, and rebuild it with more inodes. That's an advanced topic and I won't try to explain it here.


FATAL: catalog is missing 1 attribute(s) for relid 2662

Upon database corruption, before doing anything else, one should read https://wiki.postgresql.org/wiki/Corruption and take a file system level backup.

You need to restore from backup or, if backup is missing, check corrupted tables for example

select distinct relname
from pg_class
where not exists (select 1 from pg_attribute where attrelid = relfilenode)`

or

select distinct relname
from pg_class
where relfilenode > (select max(attrelid) from pg_attribute)

And then your need to restore missing record in pg_attribute (for example create simm table and

copy with q as
( select attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff, atttypmod, attbyval, attstorage,attalign, attnotnull, atthasdef, attisdropped, attislocal, attinhcount, attcollation, attacl, attoptions, attfdwoptions
  from pg_attribute
  where attrelid::regclass = your_table::regclass) )
 INSERT INTO pg_catalog.pg_attribute select [id from your pgclass missing instead new attrelid], * from q

Then check, that atttypid exists and do vacuum on pg_class and pg_attribute. So, your problem must be solved.


Error while connecting psql to PostgreSQL 9.6

Your standby server has been down long enough that the primary doesn't have the required transaction log information any more. In addition, the standby was not shut down cleanly.
You will have to remove the standby server and rebuild it from scratch, unless you have a WAL archive that still contains the required segments.
Consider using replication slots next time to avoid that problem.


Run script on multiple schemas on PostgreSQL DB

You could run the following bash script. It will expect the list of schemas in the schemas.txt file, and your script in myscript.sql. It will run the script on each schema (as a default schema) in mydatabase, and will produce, for each schema, a file named myscript-output-<the_schema_name>.txt.

For Unix, the script is:

cat schemas.txt | while read schema; do
  (
    echo "SET search_path TO $schema,public;"
    cat myscript.sql
  ) | psql -f - -o myscript-output-${schema}.txt mydatabase
done

For windows, the script is (use %%S instead of %S when used in a batch file):

for /F %S in (schemas.txt) do @(
  echo SET search_path TO %S,public;
  type myscript.sql
) | "C:\Program Files\PostgreSQL\9.3\bin\psql" -f - -o  myscript-output-%S.txt mydatabase


Does @os_run_priority in sp_add_jobstep actually work in SQL Server 2008 R2?

This is part of the job step definition and you may even see it have values used or defined in other areas.

After taking a look in the source code (I work at Microsoft and have access), while the value is indeed passed as part of the job step information sent to each subsystem, I couldn't find a place that actually set the value as part of the job step execution. There are, however, threads that do run in different priority levels as part of SQL Server Agent and those threads may or may not help with job step functionality or subsystems that fill a specific role.

While I didn't do an exhaustive check, it would be a safe best to assume this value is - as described - "reserved". Just because it doesn't seem to be used doesn't mean it can't be at any other point as the plumbing exists.


SSMS / SQL-Server delays console output (RAISEERROR WITH NOWAIT)

To have full control over the message display, you could execute the script with PowerShell using System.Data.SqlClient, handling the InfoMessage event and setting FireInfoMessageEventOnUserErrors on the connection. I don't think there is a way to control the behavior in SSMS or other SQL Server tools. – Dan Guzman yesterday


Unique Index/Constraint with multiple columns, one column is nullable

Since the alias column is nullable, if you add a unique constraint on the composite (last_name, first_name, birth_date, alias), there will still be duplicates allowed, with the same values in the first 3 columns and NULL in the alias. The constraint is skipped / accepted when at least one value is null. MySQL documentation on CREATE TABLE is not very clear but you can test the behaviour:

  A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

What you could do is to define the alias as not null and add a default value, (say 'NONE' or 'DEFAULT' or the empty string ''). You (or the user) will not have to provide that value, it will be automatically saved in all rows. Once someone tries to add a new row with same last name, first name and birth date as an existing row, the unique constraint will forbid it. I guess you could add some procedure at that point, that asks for  a different value for the alias and adds the new row with it.


Postgres - How to get Multi-Count Query using a Where Clause for a Join Table

It looks like you can use COUNT(DISTINCT expression), so I'd try replacing

count(distinct eo.orderlineid) as orderline,

with this

COUNT(DISTINCT CASE WHEN     o.deliveryrequesteddate >= '2018-03-06'
                         and o.deliveryrequesteddate <= '2018-05-06'
                 THEN eo.orderlineid
                 ELSE NULL
               END) as orderline,

NULL values are not counted by COUNT, and the DISTINCT should still ensure you're only counting each orderlineid once.


How to export my query output to CSV in SSMS

If you have a working R-in-Database (installation option of SQL-Server) and you need to do this job to be done programatically you can use R. Use this way if you try to export your data to a final sink (office for the enduser) or to use some extra functionality when exproting (graphic, computation...)

If you hava a working SSIS-environment (data-tools installaled) you can export data most easily with data flow task. You can produce this packages with SSMS and the Export data assistant - at least the first version.

If you want to have direct access to the other database: Don't use CSV. Use a driver to connect to both databases and use SSIS or any other tool to write from database to database using drivers (ODBC, JDBC, OLE DB).

If you don't have any other possibility and really need CSV: Don't write your own "export script". Ctrl+A, Ctrl+C does not do the job properly. There are lot of issues to keep in mind: date formats, escape sequences, separators, end of lines...

And: Alwaye test in advance. And test early&everything. Don't start by exporting GBs of data and sending this anywhere. This will most certainly not work.


I can only tell you what we do now and what I have done in the past. Our shop uses MSTFS (Microsoft Team Foundation Server), so any version we have the ability to get the latest version of the script.

When we create the scripts we include the customary header - Description, Date, Author, Title, and sometimes Build number. This helps us to maintain code versioning and build numbers with database code.

We branch SQL code with functional code so as we integrate branches from developers, their SQL code comes with. When we moved to production, it all moved together.

In other positions, the data group worked with developers to create the database ERD. Then the data group came in as another branch to the project and create database objects. When the functional code was complete and the move was going to production, their branch was deployed to the data tier while the code was deployed to the app tier. The members of the DEVOPS group tied it all together.

There are many ways you can go. You just have to pick one. I suggest reading further on Application Lifecycle Management (ALM).

Typical ALM treats DB versioning like an afterthought if at all. Many companies treat the DB in the same fashion.

The answer is, it depends. I use a tool (MySQL Workbench, VS) to script my database object creation and I version the scripts based on build number. Typically it's YYYYMMDD#. Most DB tools are rudimentary so your mileage may vary. Try some different methods and find what works for you.


postgresql optimize query with 'where not in' clause

First you may try two things:

Do not use WITH because it's an optimization fence. As of PostgreSQL 10, the result of the CTE query will be materialized even if it would be better not to.
Use NOT EXISTS instead of NOT IN. Long explanation of why on stackoverflow: https://stackoverflow.com/questions/173041/not-in-vs-not-exists

 INSERT INTO table2
 SELECT * FROM table1
 WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.id=table1.id);

Also, please study and share the result of an EXPLAIN of the query or if possible EXPLAIN ANALYZE.


How to show rows with 0 without any records in database

I suppose you know the date_start and date_end and you won't work on more than 1000 days although it can be extended.

This example works with these values:

SET @date_start := CAST('20150101' as date);
SET @date_end := CAST('20150108' as date);

Below queries return each day between @date_start (2015-01-01) and @date_end(2015-01-08). This is similar to a calendar. The full list of days can then be LEFT JOIN to the list of available days in the table.

Using SELECT ... UNION ALL ...

SELECT @date := date_add(@date, INTERVAL 1 DAY) as date
FROM (
    SELECT 0 as n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  )d10, (
    SELECT 0 as n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  )d100, (
    SELECT 0 as n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) d1000
  , (SELECT @date := date_add(@date_start, INTERVAL -1 DAY)) v
WHERE @date < @date_end
;

Output:

date
2015-01-01
2015-01-02
2015-01-03
2015-01-04
2015-01-05
2015-01-06
2015-01-07
2015-01-08

Each inner SELECT ... UNION ALL ... returns 10 rows from 0 to 9
This SELECT is used and CROSS JOIN 3 times and returns 1000 rows (10*10*10) from day 0 to day 999
Because 1 is added to @date, it means that it can return up to 1000 consecutives days
You can work on 10.000 days if you add one more SELECT ... UNION ALL ... and so on or on 100 if the third join is removed
You can then LEFT JOIN this query with your table

Using a view

It is probably easier to create a view with numbers from 0 to 9 and CROSS JOIN it 3 times or create 1 single view from 0 to 999:

CREATE VIEW viewDec As
  SELECT 0 as n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
  UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
;

SELECT @date := date_add(@date, INTERVAL 1 DAY) as date
FROM viewDec d10, viewDec d100, viewDec d1000
    , (SELECT @date := date_add(@date_start, INTERVAL -1 DAY)) v
WHERE @date < @date_end
;

You can work on 10.000 days by adding an extra viewDec d10000 ...

Using an existing table

It is also possible to replace the SELECT or VIEW by an existing table with enough rows (1000 in this example):

SELECT @date := date_add(@date, INTERVAL 1 DAY) as date
FROM (SELECT 1 as n FROM table_with_over_1000_rows LIMIT 1000) t
    , (SELECT @date := date_add(@date_start, INTERVAL -1 DAY)) v
WHERE @date < @date_end
;

Full query (using viewDec)

SELECT `date`, count(t.`id`)
FROM (
  SELECT @date := date_add(@date, INTERVAL 1 DAY) as date
  FROM viewDec d10, viewDec d100, viewDec d1000
    , (SELECT @date := date_add(@date_start, INTERVAL -1 DAY)) v
  WHERE @date < @date_end
) d
LEFT JOIN (
    SELECT `id`, `timestamp`
    FROM Tabs
    /* WHERE sensor = 10 */
) t
    ON d.`date` = t.`timestamp`
GROUP BY d.`date`
;

In this SQL Fiddle timestamp has been replaced by a type date column.

Output:

date        count(t.`id`)
2015-01-01  2
2015-01-02  3
2015-01-03  0
2015-01-04  1
2015-01-05  2
2015-01-06  0
2015-01-07  0
2015-01-08  0


How can I list database names within a mysqldump file?

PROPOSED SOLUTION
DUMPFILE=mydump.sql
grep "^USE " ${DUMPFILE} | sed 's/;//' | sed 's/`//g' | awk '{print $2}'

EXAMPLE
# grep "^USE " dump.sql | sed 's/;//' | sed 's/`//g' | awk '{print $2}'
CR1281653
CR1289379
MasterDB
ProjectDB
TPLB23
a110107
a110107_copy
cardfree_orders_lab
mysql
nulldatetest
part_lab
part_test
partition_engine
redwards
scmp_lab
topup_lab
wsms2_lab
zenith_lab
CR1281653
CR1289379
MasterDB
ProjectDB
TPLB23
a110107
a110107_copy
cardfree_orders_lab
mysql
nulldatetest
part_lab
part_test
partition_engine
redwards
scmp_lab
topup_lab
wsms2_lab
zenith_lab
#

You could collect them in a variable (Note: You must escape the backquote)
# DBLIST=`grep "^USE " dump.sql | sed 's/;//' | sed 's/\`//g' | awk '{print $2}'`
# echo ${DBLIST}
CR1281653 CR1289379 MasterDB ProjectDB TPLB23 a110107 a110107_copy cardfree_orders_lab mysql nulldatetest part_lab part_test partition_engine redwards scmp_lab topup_lab wsms2_lab zenith_lab CR1281653 CR1289379 MasterDB ProjectDB TPLB23 a110107 a110107_copy cardfree_orders_lab mysql nulldatetest part_lab part_test partition_engine redwards scmp_lab topup_lab wsms2_lab zenith_lab
#

GIVE IT A TRY !!!


Whats the name for columns which purpose is not to identify?

Those columns are generally referred to as non-key attributes, in my experience.

Googling for that phrase backs up the fact that it is commonly used, if you need a reference.


SQL services wont start due to incorrect drive letter after windows restore

I assume you've restored entire partitions, but the partition drive letters are now wrong.

If SQL is looking for I: then you have three choices

a) Fix the drive letter of the partition that's now got the data.  You said this is not an option.  Is that because I: is already taken, or because something else is now looking for this data based on its new drive letter (E:)

b) Add an additional drive letter for the partition - nothing stops you referring to the same partition by more than one drive letter.  This isn't an option if I: is already taken and you can't move that data from I:

c) Create a symbolic link on your new I: drive to point things back to what's now on E:.  Start an elevated command prompt.  Then type

I:
cd "\Program Files\"
mklink /d "Microsoft SQL Server" "E:\Program Files\Microsoft SQL Server"

If you already have a Microsoft SQL Server folder on your new I: then change the mklink command to make the MSSQL.1 folder as the symlink rather than the Microsoft SQL Server folder.


MySQL Create Table with Partition Slow on server machine

ANALYTICAL EXPERIMENT

Looking at CREATE TABLE and seeing that you are creating 300 partitions, I need you to perform an experiment. Please run the following script:

DROP DATABASE IF EXISTS rolandos_test;
CREATE DATABASE rolandos_test;
USE rolandos_test
SET @T1 = UNIX_TIMESTAMP(NOW());
CREATE TABLE CTM_MyISAM (
  `ServerID` BIGINT NOT NULL,
  `ClientID` BIGINT NOT NULL,
  `CommunityID` INT NOT NULL,
  PRIMARY KEY (`ClientID`,`CommunityID`),
  UNIQUE INDEX `IX_CTM` (`ServerID` ASC,`CommunityID`),
  INDEX `IX_CTM_ClientID` (`ClientID` ASC))
ENGINE=MyISAM
PARTITION BY HASH (CommunityID) PARTITIONS 300;
SET @T2 = UNIX_TIMESTAMP(NOW());
CREATE TABLE CTM_InnoDB (
  `ServerID` BIGINT NOT NULL,
  `ClientID` BIGINT NOT NULL,
  `CommunityID` INT NOT NULL,
  PRIMARY KEY (`ClientID`,`CommunityID`),
  UNIQUE INDEX `IX_CTM` (`ServerID` ASC,`CommunityID`),
  INDEX `IX_CTM_ClientID` (`ClientID` ASC))
ENGINE=InnoDB
PARTITION BY HASH (CommunityID) PARTITIONS 300;
SET @T3 = UNIX_TIMESTAMP(NOW());
SET @MyISAM_TIME = @T2 - @T1;
SET @InnoDB_TIME = @T3 - @T2;
SET @CTM_Ratio = FORMAT(@InnoDB_TIME/@MyISAM_TIME,2);
SELECT @InnoDB_TIME,@MyISAM_TIME,@CTM_Ratio;

I have the following on my laptop at home

6 GB RAM
650 GB Disk
Windows 8.1
MySQL 5.6.21

I got the following results

mysql> DROP DATABASE IF EXISTS rolandos_test;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> CREATE DATABASE rolandos_test;
Query OK, 1 row affected (0.00 sec)

mysql> USE rolandos_test
Database changed
mysql> SET @T1 = UNIX_TIMESTAMP(NOW());
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE CTM_MyISAM (
    ->   `ServerID` BIGINT NOT NULL,
    ->   `ClientID` BIGINT NOT NULL,
    ->   `CommunityID` INT NOT NULL,
    ->   PRIMARY KEY (`ClientID`,`CommunityID`),
    ->   UNIQUE INDEX `IX_CTM` (`ServerID` ASC,`CommunityID`),
    ->   INDEX `IX_CTM_ClientID` (`ClientID` ASC))
    -> ENGINE=MyISAM
    -> PARTITION BY HASH (CommunityID) PARTITIONS 300;
Query OK, 0 rows affected (1.09 sec)

mysql> SET @T2 = UNIX_TIMESTAMP(NOW());
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE CTM_InnoDB (
    ->   `ServerID` BIGINT NOT NULL,
    ->   `ClientID` BIGINT NOT NULL,
    ->   `CommunityID` INT NOT NULL,
    ->   PRIMARY KEY (`ClientID`,`CommunityID`),
    ->   UNIQUE INDEX `IX_CTM` (`ServerID` ASC,`CommunityID`),
    ->   INDEX `IX_CTM_ClientID` (`ClientID` ASC))
    -> ENGINE=InnoDB
    -> PARTITION BY HASH (CommunityID) PARTITIONS 300;
Query OK, 0 rows affected (2 min 49.83 sec)

mysql> SET @T3 = UNIX_TIMESTAMP(NOW());
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MyISAM_TIME = @T2 - @T1;
Query OK, 0 rows affected (0.02 sec)

mysql> SET @InnoDB_TIME = @T3 - @T2;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @CTM_Ratio = FORMAT(@InnoDB_TIME/@MyISAM_TIME,2);
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT @InnoDB_TIME,@MyISAM_TIME,@CTM_Ratio;
+--------------+--------------+------------+
| @InnoDB_TIME | @MyISAM_TIME | @CTM_Ratio |
+--------------+--------------+------------+
|          170 |            1 | 170.00     |
+--------------+--------------+------------+
1 row in set (0.00 sec)

mysql>

COMPARISON OF STORAGE ENGINES

MyISAM

It took 1.09 seconds to make 300 partitions using MyISAM. . It create the following:

.frm file (Manage the Table)
.par file (Manage Partitions)
300 .MYD files (Partition Data)
300 .MYI files (Partition Indexes)

File Listing from Windows Command Line

C:\MySQL_5.6.21\data\rolandos_test>dir ctm_myisam*
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF

 Directory of C:\MySQL_5.6.21\data\rolandos_test

02/15/2015  10:02 PM                 0 ctm_myisam#P#p0.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p0.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p1.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p1.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p10.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p10.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p100.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p100.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p101.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p101.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p102.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p102.MYI
.
.
.
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p95.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p96.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p96.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p97.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p97.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p98.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p98.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p99.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p99.MYI
02/15/2015  10:02 PM             8,650 ctm_myisam.frm
02/15/2015  10:02 PM             1,708 ctm_myisam.par
             602 File(s)        317,558 bytes
               0 Dir(s)  674,767,982,592 bytes free

InnoDB

It took 2 min 49.83 seconds to make 300 partitions using InnoDB. It create the following:

.frm file (Manage the Table)
.par file (Manage Partitions)
300 .ibd files:

File Listing from Windows Command Line

C:\MySQL_5.6.21\data\rolandos_test>dir ctm_innodb*
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF

 Directory of C:\MySQL_5.6.21\data\rolandos_test

02/15/2015  10:02 PM           131,072 ctm_innodb#p#p0.ibd
02/15/2015  10:02 PM           131,072 ctm_innodb#p#p1.ibd
02/15/2015  10:02 PM           131,072 ctm_innodb#p#p10.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p100.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p101.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p102.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p103.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p104.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p105.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p106.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p107.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p108.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p109.ibd
02/15/2015  10:02 PM           131,072 ctm_innodb#p#p11.ibd
.
.
.
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p90.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p91.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p92.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p93.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p94.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p95.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p96.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p97.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p98.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p99.ibd
02/15/2015  10:02 PM             8,650 ctm_innodb.frm
02/15/2015  10:02 PM             1,708 ctm_innodb.par
             302 File(s)     39,331,958 bytes
               0 Dir(s)  674,766,995,456 bytes free

C:\MySQL_5.6.21\data\rolandos_test>

CONCLUSION OF THE EXPERIMENT

InnoDB's storage engine management is a definite bottleneck. This is not such much the number of files. Why ? If the number of files per partition was the sole factor, it should have taken 0.545 seconds to create half the number of file. It took 2 min 49.83 sec. How ?

MySQL's partition management is essentially the same. Once the storage engine layer is reached, InnoDB's data dictionary is evidently monitoring each partition's registration.

YOUR ORIGINAL QUESTION

Now that you know how InnoDB is functioning while files are being created, now look at your disks.

You have Disks on 3 other Windows OS platforms

Windows 7 (10 seconds)
Windows 2012 VM (16 seconds)
Windows 2008 (1 min 40 seconds (100 seconds))

I can confidently say the following

All three of your servers have better disk performance than my laptop's disk
ATA SCSI is not your Friend

RECOMMENDATION

GET FASTER DISKS
Please don't use RAID5 : See my post Is raid 5 suitable for a mysql installation?
Make sure you have Battery-Backed Cache
Check the Batteries on the Disk Controller

GIVE IT A TRY !!!


This is by design.  In the situation where your client is able to connect to the primary server but the database has been failed over to the secondary server, the primary server will inform the client of this.  Because the server has no awareness of the CNAME you're using it will use the information it has from the mirroring configuration to direct the client to the current location of the database.

Where your CNAME would come into play would be in a situation where the primary server is no-longer contactable.  The client will then need to time out on contacting the primary before attempting to connect to the secondary server configured in the connection string.

The following page from MS describes the behavior...
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/database-mirroring-in-sql-server


Need only single table from ibdata1 file

I would put the whole backup file onto a testserver, is that possible for you? And then do:
mysqldump -u root -p test1 catalog_product_index_price > dump_catalog_product_index_price.sql


When I looked at the DDL per Brian's suggestion the problem immediately became clear: The usr_cookbook_status table was using MyISAM engine.  I switched it to InnoDB and everything works now.


Postgres initialization of pg_basebackup

A physical backup (like pg_basebackup) results in an already initialized directory.  You don't run initdb.  You just start it up, possibly after changing the config files in whatever way is appropriate for your situation.

You probably should have configured this for streaming though with a named slot though, or it will have fallen behind while the backup was being taken, but that would be a different question.

Why not just move the drive array from one server to the other?  Is the desire to change the storage hardware as well as the server hardware?


Is there any way to filter or ignore messages written to log?

In postgresql.conf the parameter log_checkpoints (boolean) causes checkpoints and restartpoints to be logged in the server log. Some statistics are included in the log messages, including the number of buffers written and the time spent writing them. This parameter can only be set in the postgresql.conf file or on the server command line. The default is off.

Not sure if this is the same thing, is yours commented or not?

#log_checkpoints = off

And anyway if you viewed your log using something like Glogg you could set an exclude pattern like:

^(?!.*incomplete startup packet).*$

As simple as that.


Find multiple rows that share one field value but have one unique value

Join card_decks and cards twice.

SELECT pw.id AS pw_card_id,ss.id AS ss_card_id, d.id AS deck_id
FROM decks d
INNER JOIN card_decks ssd on ssd.deck_id = d.id
INNER JOIN card_decks pwd on pwd.deck_id = d.id
INNER JOIN cards pw ON pw.id = pwd.card_id AND pw.planeswalker
INNER JOIN cards ss ON ss.id = ssd.card_id AND ss.signature_spell ;

you can also group the result

SELECT pw.id AS pw_card_id,ss.id AS ss_card_id, count(*)
FROM decks d
INNER JOIN card_decks ssd on ssd.deck_id = d.id
INNER JOIN card_decks pwd on pwd.deck_id = d.id
INNER JOIN cards pw ON pw.id = pwd.card_id AND pw.planeswalker
INNER JOIN cards ss ON ss.id = ssd.card_id AND ss.signature_spell
GROUP BY 1,2 order by 3 desc, 2, 1;


"Cannot open user default database. Login failed" after altering MDF and LDF locations and bringing DB offline

I may have input the incorrect Name parameter above (Would the mdf/ldf files be dropped in this case?)

This would not have dropped the files, if the NAME values weren't correct, the command would have failed.

  I also did not do step 2 in the answer referenced above (Stop log reader agent, distribution and merge agent (if they are running).

Are you using replication on this database? If not, then this doesn't matter as these steps are not necessary.

When you login to SQL Server via Management Studio, you can explicitly select a database to connect to or use your default database. If your default database is unavailable, you will get a login failure.

This link details how to explicitly select a database to login to in SSMS. Basically, on the login window, click Options then select to Connection Properties tab. Under "Connect to Database:" type "master" and try logging in again.

If this allows you to regain access to the SQL Server, follow the steps in this SE answer to change the default DB for your administrators to "master" so that they have a better chance of being able to log in even when user databases are taken offline.


I have had this happen when it loses a trust relationship with the domain. One of my solutions is to open up a command window and type in ipconfig /registerdns, wait 15 minutes and try again.


When should you shrink your transaction log?

Transaction log sizing is important also because of VLF fragmentation which could wreck havoc on your restores and transaction log performance.  Ensure your tlog VLF isnt heavily fragmented first I'd say.
Secondly a good way Paul Randal stated was to let your app run natively for a week, and see how big it gets. Make sure you do your regular maintenance on it and such.
Now maybe it gets so big because of ad hoc maintenance, queries, badly written queries, but to find the current good size that would work.
https://www.sqlshack.com/what-is-sql-server-virtual-log-file-and-how-to-monitor-it/


"Limit 1000,25" vs "limit 25 Offset 1000"

In terms of operation
SELECT id,name,description FROM tablename LIMIT 1000,25
SELECT id,name,description FROM tablename LIMIT 25 OFFSET 1000

there is absolutely no difference in the statements
siride's comment:

from https://dev.mysql.com/doc/refman/5.6/en/select.html

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.

is exactly the point.

LIMIT 1000,25 means LIMIT 25 OFFSET 1000

From the same Documentation

LIMIT row_count is equivalent to LIMIT 0, row_count

YOUR ACTUAL QUESTIONS

Does this actually do the same or is my understanding wrong?
Is one slower/faster in larger tables

Since both queries are the same, there is no difference

Does the result of offset change when I do WHERE column=1 (say column has >100 different values)
Does the result of offset change when I do ORDER BY column ASC (asuming it has random values)

Using LIMIT does not change any result sets. They simply navigate within the result set.
This query
SELECT id,name,description FROM tablename ORDER BY id LIMIT 1000,25

would be different from
SELECT * FROM (SELECT id,name,description FROM tablename LIMIT 1000,25) A ORDER BY id;

because the LIMIT is being applied at a different stage.
The first query returns nothing if tablename has less 1000 rows
The second query returns nothing if the subquery has less 1000 rows
CONCLUSION
You will have to sculpt the query to make sure you are sorting data at the right stage


Will this work for you?  It's not dynamic as you need to know what the skills are ahead of time, but it gets the job done.  I've added another skill to show you how to do that.  You only need to change it in one place.

DECLARE @Table TABLE
    (
      Skill VARCHAR(10) NOT NULL
    , MinValue INT NOT NULL
    , IdealValue INT NOT NULL
    , MaxValue INT NOT NULL
    );

INSERT  INTO @Table
        ( Skill, MinValue, IdealValue, MaxValue )
VALUES  ( 'C++', 0, 1, 1 )
        , ( 'VB', 1, 1, 1 );

SELECT  *
FROM    @Table;

WITH    CTE_Table
          AS ( SELECT   T.Skill
                      , T.MinValue AS Value
                      , 'Min' AS ValueType
               FROM     @Table T
               UNION ALL
               SELECT   T.Skill
                      , T.IdealValue AS Value
                      , 'Ideal' AS ValueType
               FROM     @Table T
               UNION ALL
               SELECT   T.Skill
                      , T.MaxValue AS Value
                      , 'Max' AS ValueType
               FROM     @Table T
             )
    SELECT  *
    FROM    CTE_Table AS C
          PIVOT ( SUM(Value) FOR Skill IN ( [C++], [VB] ) ) AS P
    ORDER BY CASE WHEN ValueType = 'Min' THEN 1
                  WHEN ValueType = 'Ideal' THEN 2
                  WHEN ValueType = 'Max' THEN 3
                  ELSE 3
             END;

Or, using UNPIVOT:

SELECT
    Criteria = P.Name,
    P.[C++],
    P.VB
FROM @Table AS T
UNPIVOT (value FOR name IN (MinValue, IdealValue, MaxValue)) AS U
PIVOT (MAX(Value) FOR Skill IN ([C++], [VB])) AS P;

To make the query dynamic, see this Stack Overflow question and:

Dynamic pivot: sum of sales per month


Database Backup - In Process - Power Outage

Obviously the backup can't be completed if it was still in progress when electricity stopped flowing. That doesn't necessarily mean it is corrupt, but it is almost certainly going to be unusable.

In the unlucky (though unlikely) event that you lose power during a full backup, and the database itself was also corrupted as a result (this is not necessarily the case; often databases will recover from a power outage just fine), then you would have to resort to the previous full backup + any diff/log backups taken in the meantime. Similar if you lose power during a log backup, you would have to resort to the last successful log backup. So your recovery plan should account for this possibility (meaning you want your log backups to happen frequently enough that they cover your tolerance for data loss).

Once the database itself is recovered and confirmed to be fine, start the backup chain over by creating a fresh full backup.

BTW this is why people have redundant power supplies and/or UPS devices. This is certainly an avoidable problem at not a very high cost.


Postgres cannot rename table because of AccessExcusiveLock

It is not error - it is log message so your request sent (and maybe still waits) cancel request to autovacuum. It is usually not any issue. Vacuum should be finished in next iteration.


GROUP BY gives wrong result with MIN() aggregate function

As a MySQL DBA, I sadly admit that MySQL can be rather cavalier in its SQL processing. One of the most infamous feats of this is its GROUP BY behavior.
As example, Aaron Bertrand answered the post Why do we use Group by 1 and Group by 1,2,3 in SQL query? where he described MySQL's GROUP BY as cowboy who-knows-what-will-happen grouping. I just had to agree.
SUGGESTION
Rewrite the GROUP BY using code
select code,min(price) as total
from product group by code

Do three things

Make the query a subquery
Use price as alias instead on total
Join it back to the product table on code and price

Here is the proposed query
select b.* from
(select code,min(price) as price from product group by code) a
inner join product b using (code,price);

or
select b.* from
(select code,min(price) as price from product group by code) a
inner join product b ON a.code=b.code AND a.price=b.price;

Checkout the SQL Fiddle for this
GIVE IT A TRY !!!
UPDATE 2017-01-06 16:17 EST
If there exists more than 1 row with the same minimum price for a given code, you have take the query, make it a subquery, join it to retrieve the minimum id for each (code,price) and join that back to product by id:
select bb.* from
(select a.code,a.price,min(b.id) id from
(select code,min(price) as price from product group by code) a
inner join product b using (code,price)
group by a.code,a.price) aa
inner join product bb using (id);

Checkout the SQL Fiddle for that


Duplicate key on delete and update of unique key

The mentioned issue will not surface for the given code. If you could create a new code with which we shall reproduce the same error, immediate reply is guaranteed.

The choices are

Do not alter the unique key. You shall redesign your logic.
Unique key fields takes little longer to get updated as they need to be updated both in the data file and the index file. So, give some time gap before using the same key.
Try (atleast) committing the delete and then move on to creating another row with the same deleted key row (though not a good suggestion).


I, personally, use sp_restorecriptgenie by Paul Brewer. Here's the link to the SCC article that has it. Under 'The Procedure'

http://www.sqlservercentral.com/articles/Restore+database/95839/

Basically, once you build the stored procedure, just run exec sp_restorescriptgenie and the script will go out and get all backups for every DB (even transaction logs) and give you a script to generate them. Also generates the DBCC CHECKDB commands to check the DBs after restoring.

I actually just used this script about an hour ago so I can do a test restore and verify my backups.


Using PgBarman for PostgreSQL 9.3 backup

You are looking for point in time recovery (for them awkward moments when you forget the where clause  "delete from customers")

Barman sounds like a good way to go.  Barman uses built in Postgres functionality but makes managing the base backups and WAL logs a lot easier (backup and WAL log cataloger, Point in time recovery manager, manage many servers from 1 location)

Your suggested strategy sounds fine, you can set the backup location in the barman conf to point at your NAS, you can then set the archive_command in postgresql.conf to rsync WAL's to your NAS as they are rotated.

There is documentation on their website http://docs.pgbarman.org/#introduction


Memory continually being eaten up since I had SQL Server 2014 SSMS installed

SQL Server is set to start by default on system start. SSMS is just your management studio and not the SQL Server engine itself.
You can set SQL Server service (services.msc) to manual, and set the maximum memory it can allocate lower if that is a concern to you.

Important:
SSMS <> SQLServer.exe


SSRS Subscription Error: "Failure sending mail: Report processing has been canceled by the user. Mail will not be resent."

You sometimes see this error when the report is cancelled due to a timeout. You can check the current timeout setting in Report Manager > Site Settings.


How can I get MySQL to use the value returned by a function as a default?

Using a trigger

You can't use functions as defaults in MySQL, except for CURRENT_TIMESTAMP for TIMESTAMP columns


How to handle nested groupings with possible null elements in one query

You need to apply one small change to your query which is emphasised below:

SELECT events.id,
CONCAT('[', GROUP_CONCAT(event_dates.id SEPARATOR ','), ']') AS event_date_ids,
CONCAT('[', COALESCE(GROUP_CONCAT(event_times_per_event_date.event_time_ids SEPARATOR ','), '[]'), ']') AS event_time_ids
FROM events
INNER JOIN event_dates ON event_dates.event_id = events.id
LEFT JOIN (
  SELECT event_dates.id AS event_date_id, CONCAT('[', COALESCE(GROUP_CONCAT(event_times.id SEPARATOR ','), ''), ']') AS event_time_ids
  FROM event_dates
  LEFT JOIN event_times ON event_times.date_id = event_dates.id
  GROUP BY event_dates.id
) AS event_times_per_event_date ON event_times_per_event_date.event_date_id = event_dates.id
GROUP BY events.id;

In the derived table you are correctly grouping by event_dates.id but retrieving event_times.date_id instead. That is a mistake because when there is no match, it is null. And it is because one value is returned as null that the outer query omits the corresponding empty group. Replacing event_times.date_id AS event_date_id with event_dates.id AS event_date_id fixes the issue.


What is the correct result for this query?

Per the standard:

SELECT 1 FROM r HAVING 1=1

means

SELECT 1 FROM r GROUP BY () HAVING 1=1

Citation ISO/IEC 9075-2:2011 7.10 Syntax Rule 1  (Part of the definition of the HAVING clause):

  Let HC be the <having clause>. Let TE be the <table expression> that
  immediately contains HC. If TE does not immediately contain a
  <group by clause>, then “GROUP BY ()” is implicit. Let T be the
  descriptor of the table defined by the <group by clause> GBC immediately
  contained in TE and let R be the result of GBC.

Ok so that much is pretty clear.

Assertion: 1=1 is true search condition. I will provide no citation for this.

Now

SELECT 1 FROM r GROUP BY () HAVING 1=1

is equivlent to

SELECT 1 FROM r GROUP BY ()

Citation ISO/IEC 9075-2:2011 7.10 General Rule 1:

  The <search condition> is evaluated for each group
  of R. The result of the <having clause> is a grouped
  table of those groups of R for which the result of the
  <search condition> is True.

Logic: Since the search condition is always true, the result is R, which is the result of the group by expression.

The following is an excerpt from the General Rules of 7.9 (the definition of the GROUP BY CLAUSE)

  1) If no <where clause> is specified, then let T be the result of the preceding <from clause>; otherwise, let T be the result of the preceding <where clause>.

  2) Case:

  a) If there are no grouping columns, then the result of
  the <group by clause> is the grouped table consisting of T as its only group.

Thus we can conclude that

FROM r GROUP BY ()

results in a grouped table, consisting of one group, with zero rows (since R is empty).

An excerpt from the General Rules of 7.12, which defines a Query Specification (a.k.a a SELECT statement):

  1) Case:

  a) If T is not a grouped table, then [...]

  b) If T is a grouped table, then

  Case:

  i) If T has 0 (zero) groups, then let TEMP be an empty table.

  ii) If T has one or more groups, then each <value expression> is applied
  to each group of T yielding a table TEMP of M rows, where M is the
  number of groups in T. The i-th column of TEMP contains the values
  derived by the evaluation of the i-th <value expression>. [...]

  2) Case:

  a) If the <set quantifier> DISTINCT is not specified, then the
  result of the <query specification> is TEMP.

Therefore since the table has one group, it must have one result row.

Thus

SELECT 1 FROM r HAVING 1=1

should return a 1 row result set.

Q.E.D.


Restore .bkp file of different version oracle

No, you can't restore 12c backup in 11g environment.

If you need to copy data, then restore backup with 12c version and use expdp to export. After that import data to 11g.


Aggregation, joining, and filtering with many-to-many table

if you group by material, you can select the max sales and group_concat the applicability. this will give one value for the sale of material x with a new column that shows the combined, filtered uses. then a subquery will show the correct sum of sales per material (or order, or however else you decide to group).

I added group by OrderID in the subquery since it seems like you're looking for John's lifetime sales?

select ID, Name, sum(Sales), Material, Applicability
(Select ID, Name, max(Sales) as Sales, Material, group_concat(Applicability separator ', ') as Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and (applicability = 'car' or applicability = 'bike')
GROUP BY OrderID, ID, Name, Material) sub
GROUP BY ID, Name, Material


Add windows user to a particular database in SQL Server

Yes, you must first create a Login on the SQL Server instance, then create a User in the database which maps to that Login.

Without a Login, the user cannot be authenticated to SQL Server (unless you're using a Contained Database, which is not a feature of your version of SQL Server), so they won't be able to access the database.


How to display all the dates in the date-range having group by dates?

A basic implementation of a Calendar table.

CREATE TABLE Calendar(cDate datetime, cDay int, cDayOfWeek int, cDayName varchar(20));

DECLARE @date date = '20180101';
WHILE @date <= '20180131'
BEGIN
    INSERT INTO Calendar VALUES (@date, DAY(@date), DATEPART(weekday, @date), DATENAME(weekday, @date));
    SET @date = DATEADD(day, 1, @date);
END
GO

31 rows affected

CREATE TABLE products(Productive int, Name char(3), Qty int, Created date);
INSERT INTO products VALUES
(111, 'ABC', 10, '1/10/2018'),
(222, 'XYZ', 20, '1/10/2018'),
(333, 'PQR', 30, '1/11/2018'),
(444, 'MNC', 40, '1/15/2018'),
(555, 'DEF', 50, '1/11/2018');
GO

5 rows affected

Notice I've used a LEFT JOIN to get all records from calendar table and only those records that match on products table.

SELECT     c.cDate AS Created, COALESCE(Qty, 0) AS Qty
FROM       Calendar c
LEFT JOIN  (SELECT   CONVERT(date, Created, 101) AS [Created],
                     COUNT(Qty) AS [Qty]
            FROM     products
            GROUP BY CONVERT(date, Created, 101)) p
ON         p.Created = c.cDate
WHERE      c.cDate >= (SELECT MIN(Created) FROM products)
AND        c.cDate <= (SELECT MAX(Created) FROM products);
GO

Created             | Qty
:------------------ | --:
10/01/2018 00:00:00 |   2
11/01/2018 00:00:00 |   2
12/01/2018 00:00:00 |   0
13/01/2018 00:00:00 |   0
14/01/2018 00:00:00 |   0
15/01/2018 00:00:00 |   1

dbfiddle here


mysql 5.5 corrupt - missing temporary table

There are those rare times when an InnoDB table gets stuck in the ibdata1  deeply that you must try forecing mysql to start up in spite of it.

Try using innodb_force_recovery in /etc/my.cnf (Option values 1-6)

[mysqld]
innodb_force_recovery = 1

then restart mysql.

This should at least allow a mysqldump

mysqldump -u... -p... --routines --triggers --all-databases > /root/MySQLData.sql

Once you have mysqldumped all data, remove the innodb_force_recovery

Then, perform a complete cleanup of InnoDB as follows

service mysql stop
mv /var/lib/mysql /var/lib/mysql_old
mkdir /var/lib/mysql
mkdir /var/lib/mysql/mysql
cp /var/lib/mysql_old/mysql/* /var/lib/mysql/mysql/.
chown -R mysql:mysql /var/lib/mysql
service mysql start
mysql -u... -p... < /root/MySQLData.sql

After service mysql start, new InnoDB files (ibdata1, ib_logfile0, ib_logfile1) are created

Give it a Try !!!


Thanks for the fiddle, I extended it a bit: fiddle

select distinct effective_from, archived, cal
from (
    select s1.effective_from
         , s1.archived
         , s1.cal
         , row_number() over (partition by s2.effective_from
                              order by s1.archived desc) as rn
    from stack_question s1
    join stack_question s2
        on s1.effective_from < s2.archived
) as t
where rn = 1
;

I'm in a bit of a hurry, so I don't time to investigate further, but it should give you a start. The idea is to enumerate the rows from s2 to get archive


Unable to Force a WSFC Cluster to Start Without a Quorum

In the screenshot it shows the attempt to use -FixQuorum as part of the Start-ClusterNode cluster powershell commands, however the cluster log shows that quorum was not forced.

Your log shows the following:

Creating cluster node <vector len='1'>
<item>ClusSvc</item>
</vector>

Which is just starting up the cluster service. The log is also missing the fact stating that on this node quorum was forced.

Here is an example of how a cluster log (WSFC 2016, like the one you posted) would look if you forced quorum on the node:

Creating cluster node <vector len='2'>
<item>ClusSvc</item>
<item>/FixQuorum</item>
</vector>

Additionally, you'd see another entry further down:

[CS] FixQuorum mode is on

My suggestion is to log locally onto the server and try running the powershell command (after shutting down the cluster service if it's still running):

Start-ClusterNode -Node SQL2 -ForceQuorum

Then check the status about 15 seconds later with:

Get-ClusterNode -Node SQL2

Check to see what is the status and also check the cluster log to see if the ForceQuorum switch was properly passed.


Optimizing query using a WHILE statement

Iterative approaches do have their place, but generally speaking it is much better to use set based approaches when working with databases.  I've heard that the math says that "set based solutions will be faster than iterative solutions in the vast majority of the cases" several times, but I can't seem to find a good reference at the moment.

If you really need to format it "like a chart" then you would pivot the data.  Here is the pivot:

SELECT
    *
FROM
(
    SELECT
        [EmployeePaid] * 2 AS [EmployeePaid] -- Why 2x?
        , N'Total ' + CAST([FYear] AS NVARCHAR(4)) AS [Year] -- This output must match the 'IN' clause below
    FROM [TblRecords]
) as s
PIVOT
(
    SUM([EmployeePaid) -- Aggregate
    FOR [Year] IN ([Total 2013], [Total 2014], [Total 2015]) -- Match to pivoted columns
)as [pivot]

That gives you the output you wanted:

========================================
‖ Total 2013 ‖ Total 2014 ‖ Total 2015 ‖
========================================
‖ 42983.31   ‖ 76345.10   ‖  87233.5   ‖
========================================

Also in parting I would recommend trying for increased clarity instead of compacting your code.  While it is true the best line of code is the one you never write, the lines of code you do write must be maintainable.  That being said, PIVOT queries aren't the nicest to read because you're trying to get SQL Server to output data contrary to what it wants.  It might be worthwhile to have the application layer format the data instead.  Then your query would be as simple as:

SELECT
    [FYear]
    , SUM([EmployeePaid]) * 2 AS [YearTotal] -- Why 2x?
FROM [TblRecords]
WHERE
    [FYear] IN (2013, 2014, 2015)
GROUP BY
    [FYear]


I think I got the answer:
You need to put those options under the [mysqld] section
[mysqld]
slow-query-log=1
slow-query-log-file=/var/logs/my.slow.log
long_query_time=1

and restart mysql
UPDATE 2013-03-05 16:36 EST
I don't know why this is still happening, but please try this:
service mysql stop
rm -f /var/logs/my.slow.log
touch /var/logs/my.slow.log
chown mysql:mysql /var/logs/my.slow.log
service mysql start

then run SELECT SLEEP(10); and see if it lands in /var/logs/my.slow.log


As I pointed out your XML file isn't well formed. If you can convert your XML text into this:

declare @XML xml =
'<?xml version="1.0" encoding="utf-8"?>
 <config>
   <company>
     <Practice>
       0514
       <Name>Company A</Name>
       <ContactName>Alan Smith</ContactName>
       <ContactTel></ContactTel>
       <ContactMail>random@random.net</ContactMail>
       <SystemType>type A</SystemType>
     </Practice>
   </company>
 </config>';

Then you could use next query to get data content:

select T2.lin.value('(./Code)[1]', 'char(6)') Code,
       T2.lin.value('(./Name)[1]', 'varchar(100)') Name,
       T2.lin.value('(./ContactName)[1]', 'varchar(100)') ContactName,
       T2.lin.value('(./ContactTel)[1]', 'varchar(15)') ContactTel,
       T2.lin.value('(./ContactMail)[1]', 'varchar(100)') ContactMail,
       T2.lin.value('(./SystemType)[1]', 'varchar(100)') System
       into #tmp_Company2
from   @XML.nodes('/config/company/Practice') T2(lin);

select * from #tmp_Company2;
GO

Code   | Name      | ContactName | ContactTel | ContactMail       | System
:----- | :-------- | :---------- | :--------- | :---------------- | :-----
0514   | Company A | Alan Smith  |            | random@random.net | type A

db<>fiddle here


Joe Changs ExecStats provides a nice GUI for analysing which queries touch each index.

It can be downloaded from: http://www.qdpma.com/JoeChang.html

Or write Joe directly for the latest version. The tool uses sys.dm_exec_query_stats so it is very lightweight.