Understanding Explain Plan on Composite Index On PostgreSQL 12.1, given:

postgres=> create table abc(a int not null, b text not null, c boolean not null);
CREATE TABLE

postgres=> create index on abc (a, b, c);
CREATE INDEX

I then prepared a query that only filters on a, i.e. the first element in the index.

postgres=> prepare only_a(int) as select 42 from abc where a = 1;
PREPARE

postgres=> explain execute only_a(100);
                                 QUERY PLAN
----------------------------------------------------------------------------
 Bitmap Heap Scan on abc  (cost=4.20..13.67 rows=6 width=4)
   Recheck Cond: (a = 1)
   ->  Bitmap Index Scan on abc_a_b_c_idx  (cost=0.00..4.20 rows=6 width=0)
         Index Cond: (a = 1)
(4 rows)

It's able to use the index in order to filter on a, per the above.

Then, I prepared a query that filters on a and c, i.e. does not use b.

postgres=> prepare a_and_c(int) as select 42 from abc where a = 1 and c = true;
PREPARE

Then, I ran explain execute,

postgres=> explain execute a_and_c(100);
                                 QUERY PLAN
----------------------------------------------------------------------------
 Bitmap Heap Scan on abc  (cost=4.21..11.32 rows=3 width=4)
   Recheck Cond: (a = 1)
   Filter: c
   ->  Bitmap Index Scan on abc_a_b_c_idx  (cost=0.00..4.21 rows=3 width=0)
         Index Cond: ((a = 1) AND (c = true))
(5 rows)

My understanding is that Index Cond: ((a = 1) AND (c = true)) means that abc_a_b_c_idx was able to use both a and c values, despite b not being involved in the where.

If so, how can c be used since b is not used?

"c" is just being used as an in-index filter.  It jumps to the part of the index where "a"=1 and scans it until "a" becomes >1.  For all of those rows it filters out anything where "c" is not true, based on the value of "c" found in the index.

It is unfortunate that the EXPLAIN syntax doesn't distinguish a column used for jumping to a specific part of the index (or stopping in index scan at the end of the relevant values), from one just used for filtering within the index.  But EXPLAIN doesn't understand the internals of indexes, it just knows that "c" is being used, it doesn't know how efficiently it is being used.


Postgresql, case, select in when System

PostgreSQL 11.5 (Ubuntu 11.5-0ubuntu0.19.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 8.3.0-6ubuntu1) 8.3.0, 64-bit

Case

Does select work in case condition? I need return 1 state from 4 states in case. I try to explain.

Shop has eshop (store_id = 7) and stores (store_id = 1 - 10, others, but not 7).

Eshop and every store have available_count - in stock.

Logic is:

When available_count > 1 where store_id = 7 (eshop) then in stock.

When available_count = 0 where store_id = 7 (eshop) and available_count > 1 where store_id != 7 (at least one value is greater than 1 for all stores, for eshop is 0) then in stock on store.

When available_count <= 1 where store_id = 7 (eshop) or available_count <= 1 where store_id != 7 (at least one value is 1, others are 0, but if all are 0, then not in stock) then on request.

When count_availability = 0 where store_id = 7 (eshop) and count_availability = 0 where store_id != 7 (all values are 0) then not in stock.

I tried

case
when (select ja.available_count where ja.store_id = '7') > 1 then 'IN STOCK'
when (select ja.available_count where ja.store_id = '7') = 0 and (select ja.available_count where ja.store_id != '7') > 1 then 'IN STOCK ON STORE'
when (select ja.available_count where ja.store_id = '7') <= 1 or (select ja.available_count where ja.store_id != '7') <= 1 then 'ON REQUEST'
when (select ja.available_count where ja.store_id = '7') = 0 and (select ja.available_count where ja.store_id != '7') = 0 then 'NOT IN STOCK'
end as "availability",

It returns more than one value for availability, just 'IN STOCK' or '[NULL]' and sometimes seems there is problem with right value.

Is betterr way to do that?

Thank you for any help.

You need one row per product. So GROUP BY needed. And the query will look like

SELECT product_id, (expression for availability)
FROM sourcetable
GROUP BY product_id

You have 3 general variants for the amount - 0, 1 or >1 (if some record is absent we must assume its amount value is 0). For to formalize we will substract 1 and get the sign (it will compact the amount into 3 values of -1, 0 or +1). For store_id != 7 we can get max value only and ignore all another. So source data can be prepared in CTE, and final query will be

WITH cte AS ( SELECT product_id,
                     SIGN(MAX(CASE WHEN store_id != 7
                                   THEN available_count
                                   ELSE 0 END) -1) stock,
                     SIGN(MAX(CASE WHEN store_id = 7
                              THEN available_count
                              ELSE 0 END) -1) eshop
              FROM sourcetable
              GROUP BY product_id )
SELECT product_id,
       CASE stock * 3 + eshop
           WHEN -4 THEN ...
           WHEN -3 THEN ...
           ...
           WHEN 3 THEN ...
           WHEN 4 THEN ...
       END AS availability
FROM cte

Test this. Fill the variants in final CASE by yourself, please...


Insert query optimization Need your help optimising an insert query.

I have two tables in my MySQL DB. I will be inserting into Table1. Information will be retreived in Table2.

Table 2 contains a couple of million records.
My insert query should look for all users who has a previous event in Table2 and insert it into Table1.

This is my insert query - but it's killing my server.

INSERT INTO Table1(
SELECT
    d.created,
    d.userID,
    d.eventID,
    d.previousEvent
FROM (
SELECT
    e.created,
    e.userID,
    e.eventID,
    (SELECT eventID FROM TABLE2 pe WHERE e.userID=pe.userID AND e.created>pe.created ORDER BY pe.created DESC LIMIT 1) AS e.previousEvent
FROM
    TABLE2 e
WHERE
    e.eventID not in (Select f.EventID from TABLE1 f)
ORDER BY
    e.created desc
) d
WHERE
    d.preEvent is not null
GROUP by
    d.eventID,
    d.created,
    d.userID
);

eg:

Table1:

created     |   userID    |   eventID(unique)   |   previousEvent
2015-11-15      66666          1045698664           4566660001

Table2:

created     |   userID    |   eventID(unique - random)
2015-11-15      55555          4755500001
2015-11-15      66666          4566660001
2015-11-15      77777          8634757777
2015-11-15      88888          1845562565
2015-11-15      66666          1045698664

not in (Select f.EventID from TABLE1 f)

optimizes poorly.  Turn it into a LEFT JOIN:

LEFT JOIN TABLE1  ON e.EventID = f.EventID
WHERE f.EventID IS NULL


How do you write to a folder with spaces in it? I've got a bunch of simple SSIS packages that output SQL data to excel, then I rename the files and move them out to the end user.

Some of them use file paths with spaces in them, and they aren't excited about using new folders.  Here's the code I'm using:

    declare @date varchar(25),
    @sql varchar(1000)

    set @date = cast(datepart(month, current_timestamp) as varchar(2)) + '_' +  cast(datepart(day, current_timestamp) as varchar(2)) + '_' +  cast(datepart(year, current_timestamp) as varchar(4))
    print @date

    set @sql = 'copy D:\Data\ED_72.xlsx \\ehsintra3\ED_72_weekly\ED_72_' + @date + '.xlsx';
    print @sql

    exec  xp_cmdshell @sql

but it wouldnt work without the underscores.

Question how can I make this work without underscores?

You should (pretty much always) enclose paths inside quotation marks.  So, like this:

declare @date varchar(25),
@sql varchar(1000);

set @date = cast(datepart(month, current_timestamp) as varchar(2)) + ' ' +  cast(datepart(day, current_timestamp) as varchar(2)) + ' ' +  cast(datepart(year, current_timestamp) as varchar(4));
print @date;

set @sql = 'copy "D:\Data\ED_72.xlsx" "\\ehsintra3\ED 72 weekly\ED 72 ' + @date + '.xlsx"';
print @sql;

exec  xp_cmdshell @sql;

Putting paths inside quotation marks ensures the command-processor can understand where a path starts and ends.

Also, instead of using copy you probably want to look at either robocopy or xcopy.  The copy command is brutally weak and frail.

Also, a minor note, add semi-colons to the end of your statements in T-SQL.


Non Clustered Index Scan Properties window - Predicate order different than query order SQL 2012 Data Warehouse type environment...

I have a Non Clustered Index on a Type 2 Dimension table, which I'll just call DimStudent.

The index is defined as below, with data types and other info in parentheses for context:

(

 [StudentID] ASC (VARCHAR(10))

,[EffectiveStartDate] ASC (SMALLDATETIME)

,[EffectiveEndDate] ASC (SMALLDATETIME)

)

INCLUDE [StudentKey] (INT, IDENTITY, PK).

The query in question is being run during an SSIS ETL package, and is defined as below. It is specified in a Lookup Transformation using a partial cache, in the Advanced tab using the Custom query section, which is mostly auto-generated by Visual Studio (which put the [refTable] alias and SELECT * into the query) with some modifications to the query for SCD2 lookup usage:

select * from (SELECT

 LTRIM(RTRIM(StudentID)) AS StudentID_Trimmed

,StudentKey

,EffectiveStartDate

,EffectiveEndDate

FROM dbo.DimStudent

)
AS [refTable]

where [refTable].[StudentID_Trimmed] = @P1

and [refTable].[EffectiveStartDate] <= @P2

and [refTable].[EffectiveEndDate] > @P3

Note that the order of the columns in the query is the student ID (@P1), then the Start date (@P2) and then End Date (@P3).

Also note that I am currently TRIMMing the StudentID. I have determined that this is no longer necessary (it was necessary at some point for some reason), and I'm already working on changing the ETL to remove that TRIM aspect.

In the query plan for this query (using Adam Macahanic's sp_WhoIsActive), I've confirmed it's doing a Non Clustered Scan using this index. When I view Properties of the scan operator, it lists the Predicates as: (db and schema removed)

[DimStudent].[EffectiveStartDate] <= **[@P2]**

AND [DimStudent].[EffectiveEndDate] > **[@P3]**

AND ltrim(rtrim([DimStudent].[StudentID])) = **[@P1]**

My questions are:

Why are the predicates in the query plan in a different order (@P2, @P3, @P1)than how the query was written? (@P1, @P2, @P3) Caused by the TRIM?
Why is it a scan and not a seek as the index was designed to do for this query? Caused by the TRIM (not SARGable)?

SQL Server thinks that the dates are more selective because it knows what the value in the Start and End date fields look like.  Because of the trimming on the StudentId column it has no idea how many rows there are.  Once you fix that to not use the trimmed computed column it should work as expected.


Why MySQL ignores foreign keys when importing? i don't have a big knowledge in database so excuse me if there is bad syntax or bad description but i did my best :)

When i importing to child table it ignores foreign key though the parent table is empty and hasn't any data yet, i used the following command to dump the data without this parent table data

mysqldump --ignore-table --no-create-info old_database > data.sql

then i importing this data to new database I've created

mysql new_database < data.sql

then i dumped the parent row data only using this command

mysqldump --tab="\\uploads"

importing parent_data as below

mysqlimport "\\uploads\parent_data.txt" new_database

why MySQL accepts this and ignore the existing of foreign key ? and is there a way to force MySQL to not ignore FKs when importing ?

child table show create info

| child |
CREATE TABLE `child`
(
    `id` bigint(19) NOT NULL,
    `flag` int(10) NOT NULL,
    PRIMARY KEY (`id`, `flag`),
    KEY `FK72610D66B7AE7814` (`flag`),
    CONSTRAINT `FK72610D66AFB765B8`
        FOREIGN KEY (`id`) REFERENCES `parent` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

parent create info

| parent |
CREATE TABLE `parent`
(
    `id` bigint(19) NOT NULL AUTO_INCREMENT,
    ...
) ;

By default mysqldump automatically includes a statement in the dump output to set foreign_key_checks to 0.

So If you want force MySQL to not ignore FKs when importing, Use --compact switch in your command:

For Example:

mysqldump --ignore-table=example.test --compact --no-create-info old_database > data.sql

Refrence: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_compact

Update

By defult, foreign_key_checks is set 1. this means if each of your DML queriies has FK error, the query execution will be rejected with the error.

For incresing the speed of restores,
MySQL set foreign_key_checks 0 (in Dump file) and after that the restoring was done, MySQL set foreign_key_checks 1. unless you had set foreign_key_checks 0.


pgpool2 setup on master-slave replication using postgreql 9.1 I have already setup 2 ubuntu server 12.04 LTS on my local machine using virtual box and is using the master-slave replication (postgresql 9.1).

192.168.0.109 = master
192.168.0.110 = slave

my question is how to apply the load-balancing based on the two database server? I've heard about pgpool2 and pgbouncer, which one should I use?

Thanks guys and sorry I am new to postgres replication.

Of PgBouncer and PgPool-II, only PgPool-II supports load balancing.

PgPool-II's load balancing supports only read-only queries that meet certain requirements; see the pgpool docs. Additionally, if you start load-balancing queries you are no longer guaranteed to get a consistent view of the database because the replica might be lagging behind the master.

Personally, I'd look into adding a caching layer (memcached / Redis) before doing fiddly things like read-query load balancing.


Backing up SQL Jobs for an Instance I would like to ensure that I have a backed up copy of every job on my SQL server 2008 R2 instance. Any suggestions?

The jobs are themselves stored in MSDB:

I believe taking regular backups of system-- MSDB database should do good:

Else you can perform some manual stesps as listed below:

  Start SQL Server Management Studio.
  Expand the SQL Server Agent folder, and then expand the list of Jobs.
  Right-click the job you want to create a backup script for, and then select Script Job as.
  Click CREATE To or DROP To, then click New Query Editor Window, File, or Clipboard to select a destination for the script. Typically,
  the destination is a file with a .sql extension.

Also, for multiple jobs to copy at one go:  In SSMS click on the Jobs folder under SQL Server Agent,  then go to Object Explorer Details.  You can select multiple jobs and script them all at the same time.


Query memory grant and tempdb spill I have a long running query (fact table with 100 million rows joining a number of small dim tables then group by) that's spilling to tempdb, even though (after some tweaking) the CE is very close to the actual number of rows, see plan:

Looking for an explanation, I noticed the following memory grant info:

Environment: SQL Server 2012 SP1 Enterprise, server RAM 256 GB, SQL Server max memory 200 GB, buffer pool size 42 GB, workspace max size 156 GB (GrantedMemory = 156 * 25% ~= 38 GB)
Questions

does that mean no matter how good the CE is, the query has no chance of not spilling over? since the query max ram is hard capped at 38 GB
does query optimizer not take max query ram into consideration when building the plan? (forcing a Hash Match aggregate would eliminate the sort step and significantly improve query performance, unfortunately, the actual query is coming from Cognos and we have no control of it)
will increase the 25% cap to close to 100% be a sensible option here? (assuming that the said server access can be controlled to limit the number of concurrent query requests)

Anonymized query plan at Paste The Plan
When forcing a hash match aggregate (instead of a sort + stream aggregate), the query consistently finishes 3 - 4 times quicker. Unfortunately, the actual query is coming from Cognos, and we have no way of changing it.
There is no hash spill in the hash aggregate plan. The query optimizer won't pick hash match aggregate because if I look at the operator cost for hash vs stream aggregate, the CPU cost of hash group is 2 - 3 times higher than doing stream aggregate.
In both stream and hash aggregate, the estimated output rows is exactly the same as the input (~100 million rows).
The query uses a single NC columnstore index, and column stats are all regularly updated.

does that mean no matter how good the CE is, the query has no chance of not spilling over? since the query max ram is hard capped at 38 GB

The overall memory grant for your query appears capped at 37GB given your current hardware and SQL Server configuration.

If the Sort cannot be performed within the Memory Fraction (0.860743 in that plan) of the query memory grant, it will spill to tempdb. Note also that this parallel Sort splits its fraction of the query memory grant equally across 12 threads, and this allocation cannot be rebalanced at runtime.

  does query optimizer not take max query ram into consideration when building the plan? (forcing a Hash Match aggregate would eliminate the sort step and significantly improve query performance, unfortunately, the actual query is coming from Cognos and we have no control of it)

Yes, it does, but only as an input to the general costing framework. The optimizer chooses the plan that looks cheapest according to its model. If the numbers are wrong, the plan choice is not likely to be optimal.

In your case, the actual number of rows produced by the Stream Aggregate is significantly less than estimated:

The optimizer favours Hash Aggregate when fewer, larger groups are expected (since each group occupies a slot in the hash table). The misinformation about the density leads to an incorrect choice of Sort + Stream Aggregate.

The best plan would likely be a hash join instead of the nested loops join, and a hash aggregate. This should be able to extend batch mode processing to the important aggregation step.

SQL Server 2012 was quite limited in its transitions between row and batch mode. The execution engine never returns to batch mode once row mode processing has begun (so row-batch-row is ok, but batch-row-batch is not).

  will increase the 25% cap to close to 100% be a sensible option here? (assuming that the said server access can be controlled to limit the number of concurrent query requests)

If you want to increase the amount of memory available for this query, you could certainly do so by changing your Resource Governor setup. Increase the limit by degrees to see if you can locate a good compromise. I would be wary of going too close to 100%.

If the query is suitable for a plan guide, try a HASH GROUP hint.

Longer term, upgrading to SQL Server 2016 will pay dividends since more operators can execute in batch mode (including Sort), dynamic memory grant increases are possible, and ... about a thousand other improvements in columnstore/batch mode processing in general.


MongoDB - Mongoose query findOneAndUpdate() doesn't update/duplicates the DB I am trying to save and update (upsert: true - creates the object if it doesn't exist) the result of the Web Api which contains multiple arrays of data to populate the stock chart. Each time I enter a symbol and click the "Get Quote" button it should fetch data from the web api, save/update it under the Child Schema in the database. For some reason it adds duplicate values. Is there a way to fix avoid duplicate values? Here is the code that I tried....

let curValueSchema = new parentSchemaSymbol()

      curValueSchema.symbol = curValue
      highLow.map(item => {
        curValueSchema.data.push(item)
      })
      const query = { symbol: `${curValue.toUpperCase()}` }
      const update = curValueSchema
      const options = { upsert: true, new: true }
      parentSchemaSymbol.findOneAndUpdate(query, update, options).then(doc => {
        console.log('Saved the symbol', doc)
        return res.send(highLow)
      }).catch(e => {
        console.log(e)
      })

SOLUTION

Because Mongoose by default creates a new MongoDB ObjectId ( this hidden _id field) every time you pass it a Javascript Object to update the field of a document.

To go around you can tell Mongoose to not create a new ObjectId, by making sure your mongoose schema is as followed:

Folder - Models - Stock.js

  "_id": false - Has to be added to the Schema

const mongoose = require('mongoose')
mongoose.Promise = global.Promise
mongoose.connect('mongodb://localhost:27017/myapp', { useNewUrlParser: true })
const slug = require('slug')

const childSchemaData = new mongoose.Schema({
  "_id": false,
  date: mongoose.Decimal128,
  open: mongoose.Decimal128,
  high: mongoose.Decimal128,
  low: mongoose.Decimal128,
  close: mongoose.Decimal128,
  volume: mongoose.Decimal128
})

const parentSchemaSymbol = new mongoose.Schema({
  "_id": false,
  symbol: {
    type: String,
    trim: true,
    minlength: 2,
    maxlength: 4,
    required: 'Plese enter a valid symbol, min 2 characters and max 4'
  },
  // Array of subdocuments
  data: [childSchemaData],
  slug: String

});

//we have to PRE-save slug before save the parentSchemaSymbol into DB
parentSchemaSymbol.pre('save', function (next) {
  if (!this.isModified('symbol')) {
    next()//skip it
    return//stop this function from running
  }
  this.slug = slug(this.symbol)
  next()
  //TODO make more resiliant soslug are unique
})

module.exports = mongoose.model('Stock', parentSchemaSymbol)

As MongoDB Github collection documented with respect to Node.js MongoDB Driver API here Find a document and update it in one atomic operation, requires a write lock for the duration of the operation.

Syntax

findOneAndUpdate(filter, update, options, callback)

// Example of a simple findOneAndUpdate operation

var MongoClient = require('mongodb').MongoClient,
  test = require('assert');
MongoClient.connect('mongodb://localhost:27017/test', function(err, db) {
  // Get the collection
  var col = db.collection('find_one_and_update');
  col.insertMany([{a:1, b:1}], {w:1}, function(err, r) {
    test.equal(null, err);
    test.equal(1, r.result.n);

    col.findOneAndUpdate({a:1}
      , {$set: {d:1}}
      , {
            projection: {b:1, d:1}
          , sort: {a:1}
          , returnOriginal: false
          , upsert: true
        }
      , function(err, r) {
        test.equal(null, err);
        test.equal(1, r.lastErrorObject.n);
        test.equal(1, r.value.b);
        test.equal(1, r.value.d);

        db.close();
    });
  });
});

// Example of a simple findOneAndUpdate operation using a Promise.

var MongoClient = require('mongodb').MongoClient,
  test = require('assert');
MongoClient.connect('mongodb://localhost:27017/test', function(err, db) {
  // Get the collection
  var col = db.collection('find_one_and_update_with_promise');
  col.insertMany([{a:1, b:1}], {w:1}).then(function(r) {
    test.equal(1, r.result.n);

    col.findOneAndUpdate({a:1}
      , {$set: {d:1}}
      , {
            projection: {b:1, d:1}
          , sort: {a:1}
          , returnOriginal: false
          , upsert: true
        }
      ).then(function(r) {
        test.equal(1, r.lastErrorObject.n);
        test.equal(1, r.value.b);
        test.equal(1, r.value.d);

        db.close();
    });
  });
});

// Example of a simple findOneAndUpdate operation using a Generator and the co module.

var MongoClient = require('mongodb').MongoClient,
  co = require('co');
  test = require('assert');

co(function*() {
  var db = yield MongoClient.connect('mongodb://localhost:27017/test');
  // Get the collection
  var col = db.collection('find_one_and_update_with_generators');
  var r = yield col.insertMany([{a:1, b:1}], {w:1});
  test.equal(1, r.result.n);

  var r = yield col.findOneAndUpdate({a:1}
    , {$set: {d:1}}
    , {
          projection: {b:1, d:1}
        , sort: {a:1}
        , returnOriginal: false
        , upsert: true
      }
    );
  test.equal(1, r.lastErrorObject.n);
  test.equal(1, r.value.b);
  test.equal(1, r.value.d);

  db.close();
});

As Mongoosejs documented here

Query.prototype.findOneAndUpdate()
Parameters

[query] «Object|Query»
[doc] «Object»

[options] «Object»

[options.rawResult] «Boolean» if true, returns the raw result from
the MongoDB driver
[options.strict] «Boolean|String» overwrites the schema's strict mode
option
[options.multipleCastError] «Boolean» by default, mongoose only
returns the first error that occurred in casting the query. Turn on
this option to aggregate all the cast errors.
[options.lean] «Object» if truthy, mongoose will return the document
as a plain JavaScript object rather than a mongoose document. See
Query.lean().
[callback] «Function» optional params are (error, doc), unless
rawResult is used, in which case params are (error, writeOpResult)

This function triggers the following middleware.

findOneAndUpdate()

Available options

new: bool - if true, return the modified document rather than the
original. defaults to false (changed in 4.0)
upsert: bool - creates the object if it doesn't exist. defaults to
false.
fields: {Object|String} - Field selection. Equivalent to
.select(fields).findOneAndUpdate()
sort: if multiple docs are found by the conditions, sets the sort
order to choose which doc to update
maxTimeMS: puts a time limit on the query - requires mongodb >= 2.6.0
runValidators: if true, runs update validators on this command.
Update validators validate the update operation against the model's
schema.
setDefaultsOnInsert: if this and upsert are true, mongoose will apply
the defaults specified in the model's schema if a new document is
created. This option only works on MongoDB >= 2.4 because it relies
on MongoDB's $setOnInsert operator.
rawResult: if true, returns the raw result from the MongoDB driver
context (string) if set to 'query' and runValidators is on, this will
refer to the query in custom validator functions that update
validation runs. Does nothing if runValidators is false.

  Callback Signature function(error, doc) {   // error: any errors that
  occurred   // doc: the document before updates are applied if new:
  false, or after updates if new = true }

Examples

query.findOneAndUpdate(conditions, update, options, callback) // executes
query.findOneAndUpdate(conditions, update, options)  // returns Query
query.findOneAndUpdate(conditions, update, callback) // executes
query.findOneAndUpdate(conditions, update)           // returns Query
query.findOneAndUpdate(update, callback)             // returns Query
query.findOneAndUpdate(update)                       // returns Query
query.findOneAndUpdate(callback)                     // executes
query.findOneAndUpdate()                             // returns Query


How to create a relatively complex MySQL query I'm not good with MySQL but have been trying to create a query that would join from three tables where:

Table 1 (macs) is like this:
ipAddr, vlanID, macAddress, interfaceName

Table 2 (network_devices) is like this:
network_device_id, nd_location, nd_name, nd_ip_address, etc

Table 3 (vlans) is like this:
vlan_id, vlan_name

And I got this query thats works:

SELECT
    m.vlanID
    , m.interfaceName
    , count(m.macAddress) as mac
    , n.nd_name
FROM macs m, network_devices n
WHERE
    n.nd_ip_address = m.ipAddr
GROUP BY
    n.nd_name
ORDER BY
    m.vlanID

Which provides the following:
vlanID, interfaceName, mac, nd_name

And works ok. I'm now trying to add to this result the vlan_name which is in the vlans table.

How can I get this or where can I find information that would help me with this?

If I understand then this is what you want:

SELECT   m.vlanID, v.vlan_name, m.interfaceName, count(m.macAddress) as mac, n.nd_name
FROM     macs m, network_devices n, vlans v
WHERE    n.nd_ip_address = m.ipAddr
AND      m.vlanID        = v.vlan_id
GROUP BY m.vlanID, v.vlan_name, m.interfaceName, n.nd_name
ORDER BY m.vlanID


Speeding up MongoBD query { $ne: [] } I'm currently doing a MongoDB aggregation but then have a query looking for all documents where a certain field, whose value is an array, is empty.

obs = db.collection.count({'things.titles': {'$ne': [] }})

To ensure this query uses an index, do I just need to do something like this?:

db.collection.ensureIndex({'things.titles': 1})

I understand this creates a multikey index, but it still takes a very long time (more than an hour) on a collection sized at 4739208 documents.

Your question specifies:

  ... a query looking for all documents where a certain field, whose value is an array, is empty.

Your query is count all documents where the array is not empty, is this intended?

Depending upon the cardinality of the values of this field, this could be causing these values to be expensive.

To query for empty arrays, the operation would be:

 db.collection.explain().count( 'things.titles' : [ ] } )

Note that there are edge cases to this query. For example, the following would be returned:

  { "_id" : ..., "things.titles" : [ [ ] ] }

The index usage of the count operation can be determined using the collection.explain() function.

For example:

 db.collection.explain().count({'things.titles': {'$ne': [] }})

The output will provide information showing the index bounds being used (if they exist).


Extract query fingerprints and tables from large log files using pt-query-digest I tried to parse a large log file using pt-query-digest but it gives "Out of memory!" when I try to read the file. I have 3GB RAM, 40GB empty space on HDD, the file is 20GB. I did not found anything in the documentation about memory.

I reduce the command at:

pt-query-digest --group-by fingerprint --print --no-report /path/to/largeLogFile.log
pt-query-digest --table-access /path/to/largeLogFile.log

I have 2 questions:

How can I extract the query fingerprints from a large log file generated by general_log ?
How can I extract all the tables that were used ?

Edit:
Tried @DTest --sample option (with 2 and 1), but the result is the same. Just before it reaches 50% memory in htop it dies every time (see screnshoot):

I did an strace just before it dies I get:

read(3, "phone from teams as t ri"..., 4096) = 4096
mremap(0x5b27a000, 1543499776, 1543503872, MREMAP_MAYMOVE) = -1 ENOMEM (Cannot allocate memory)
mmap2(NULL, 1543503872, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)
brk(0x64c2d000)                         = 0x8c2e000
mmap2(NULL, 1543634944, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)
mmap2(NULL, 2097152, PROT_NONE, MAP_PRIVATE|MAP_ANONYMOUS|MAP_NORESERVE, -1, 0) = 0x5b07a000
munmap(0x5b07a000, 548864)              = 0
munmap(0x5b200000, 499712)              = 0
mprotect(0x5b100000, 135168, PROT_READ|PROT_WRITE) = 0
mmap2(NULL, 1543503872, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)
write(2, "Out of memory!\n", 15Out of memory!
)        = 15
munmap(0x5b27a000, 1543499776)          = 0
... [output removed] ...

The only option I can think so far is splitting the file "bite size" pieces (let's say 20 files of 1GB each), digest them and merge the result.

I would try to use the --sample option:

pt-query-digest --sample 2 --print --no-report /path/to/largeLogFile.log

This would filter out all but the first 2 occurrences of a query fingerprint. --no-report removes the memory and cpu overhead of aggregating.


Is there a best practice for recurring calendar events I'm not a dba but a php programmer, so that I'm not an SQL pro. Now, I have searched in both programmers and dba stackexchange sites but the answers are too specific or not conclusive.

My question is very simple, I need to create a calendar. just a calendar, like Google calendar, with normal and recurring events.

Now to summarize what I have learn in my research about recurring events, appears to be 2 main schools:

Create a "parent" event and an occurrences table with one row for each repetition of the event.
Create an events table (1 row for a recurring event) and a second table to store repetition pasterns (generally 1 or 2 row per event).

There are good and bad things in each of the 2 solutions:

Easy to query, easy to create exception (like changing only 1 occurrence in the series), easy to store complex patters, BUT will create a lot of rows, hard to export (ex iCal), easy to cause data integrity issues.
Easy to store complex patterns, clean, easy to export, BUT difficult to query.

When a I say hard or easy to query, I mean to query for a specific time range. For example I want to query all events (or occurrences) for this month, having 2 Timestamps.

The complexity of the recurring events should also be very granular. For example, repeat every 2 months on the 2nd Saturday, or repeat every 2 weeks on Friday and Monday, or repeat every Friday to Monday of the first week of every other month.

To summarize I am creating a very basic task here and I'm sure there is a well know DB schema for this. Lets call it a best practice.

So, what is the best Schema to create those events?

BTW: I use MySQL.

This is just a suggestion which, while obviously not helping the OP, may help others with similar/analogous problems (won't fit as a comment).

You may also wish to consider using MySQL Events for your application. Depending on your app's functionality, they may provide a more elegant solution to a scheduling/calendar range of capabilities.

Check out the documentation here, and these sites (1, 2). I was looking at this recently and it's really quite powerful and can do a lot of the heavy lifting in the scheduling/polling area of this sort of programme.


collection partitioning in mongodb Is there any semantic like table partitioning in MongoDB? My data is not that much that it would need sharding. but data is not important(but not completely useless) after several months.

Partitioned collection in TokuMX 1.5 is available now.

See Introducing Partitioned Collections for MongoDB Applications


Drop database from remote that has connected users I have a "small" problem. There is a database server that contains a database where some test frontends are connected to. I want to update that database from a dump + some scripts to the most recent version. For that I need to drop the db. But when I kill the connections to the DB, the clients immediately reconnect within a second. :-(

So I try to disallow new connections, kill the connections and then drop the DB. For that I write a small script. The essential part is that:

SQL="UPDATE pg_database set datallowconn = 'false' where datname = '$DB_NAME';
     SELECT pg_terminate_backend(procpid) FROM pg_stat_activity where datname = '$DB_NAME' and current_query
     NOT LIKE '%SELECT pg_terminate_backend(procpid)%'; DROP DATABASE $DB_NAME;"

psql -h $DB_HOST -W -U $DB_USER "$DB_NAME" -c "$SQL"

But psql is not able to perform the drop database in a multiline statement...

So I tried (just for the sake of trying it out) to do the drop db in a second statement. But as I expected that complains that it can't drop the DB because the DB does not allow new connections...

Isn't there a reliable way to perform an action like this?

I can't simply stop all clients that are connected, because I don't know in my script which clients are connected from where. So that option is not possible.

First, you can issue DROP DATABASE; from a multiline script, but it has to be the only statement of its transaction:

CREATE DATABASE droptest;

\c droptest

CREATE TABLE a (id integer);

Then run the script:

SELECT id
FROM a;

\c postgres

DROP DATABASE droptest;

It results in:

 id
----
(0 rows)

psql (9.1.9, server 9.0.6)
WARNING: psql version 9.1, server version 9.0.
         Some psql features might not work.
You are now connected to database "postgres" as user "postgres".
DROP DATABASE

And the documentation says:

  DROP DATABASE cannot be executed inside a transaction block.

This means it can't be run like psql -1 -f the_above_script.

So, now my solution (hopefully):

First disable connections on the DB:

REVOKE CONNECT ON database_name FROM ALL;

Then do what you do now:

SELECT pg_terminate_backend(procpid)
FROM pg_stat_activity
WHERE
    datname = '$DB_NAME' and
    current_query NOT LIKE '%SELECT pg_terminate_backend(procpid)%'
;

DROP DATABASE database_name;


Create a file with variable name in power shell Power shell I am trying to execute the below power shell script to script out the db in sql server.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "SRVBLRDBATST98\MSSQLSERVER1"

$abc=$s.databases

foreach ($tables in $abc)
{
$tables.Script() | out-File e:\pruthvi\db.txt -Append
}

The above power shell will script the out put to db.txt, but i need the output like dbname.txt for each database which will be created everytime for each databases.
any help?

This code should do what you want. To make it more readable and understandable, I expanded out much of the logic. I also added comments to describe what each piece of code is doing.

## Import the SMO assembly from the GAC
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

## Create a Server object for connection to the SQL instance
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "SRVBLRDBATST98\MSSQLSERVER1"

## Loop over the databases on the instance
$server.Databases | ForEach-Object {
    ## Store the name of the database
    $databaseName = $_.Name

    ## Generate a script for the database
    $databaseScript = $_.Script()

    ## Save the script to a file, overwriting what may have already been there
    $databaseScript | Out-File -FilePath "E:\pruthvi\$($databaseName).sql" -Force
}

The key to this change are the parameters passed to Out-File. First off -FilePath "E:\pruthvi\$($databaseName).sql" is what saves the script to a file named after the database. Secondly, -Force ensures that if the target file already exists, it will be replaced.


Table Valued Parameter not pulling the correct data for my sp? http://sqlfiddle.com/#!3/b2643/5/1

http://sqlfiddle.com/#!3/b2643/5/0

This link above has the sample database tables I created on SQLfiddle.

I have 3 main tables in my stored procedure:

TransmittalSheet, DocumentInfo and TransmittalSiteLead. Each TS can have many documents released with it and also many site lead staffs for each Transmittal that implements at different times.
Our users run reports through the use of a VB application but its pulling the data from a SQL Server in the back end via stored procedures. Currently, they can pull all docs implemening per facility individually/ seperately, Sometimes these documents are shared or implemented in multipple facilities at the same date.

My goal is to get all the documents implementing for all facilities (5 of them) at once for the one division (West) within specific date range, then at the end I would like to take care of duplicates or I mean showing the same shared document once.

With the help of Aaron Bertrand, he was able to show me the use of Table Valued Parameter, now I get partial results, versus all documents that I see when I run the original individual stored procedure that has one variable pointing to one facility at a time, so I wasn't sure what I'm missing in my query, please advice

CREATE TYPE dbo.Facilities AS TABLE
(
  FacilityID TINYINT PRIMARY KEY
);
go

declare @F dbo.Facilities;
Insert @F Values (1), (2), (3), (4), (5), (7);

-- Facility 6 is considered the Division
go

create PROCEDURE [dbo].[GetImpDocsThree]
  @facilities dbo.Facilities ReadOnly,
  @facilitydivisionID tinyint = 6,
  @startdate smalldatetime = '06/16/2005',
  @enddate smalldatetime = '04/28/14'
AS
BEGIN
  SET NOCOUNT ON;

  ;WITH f AS
  (
    SELECT FacilityID FROM @facilities
    UNION ALL SELECT @facilitydivisionID
  )

  SELECT  di.DocumentNumber, di.DocumentVersion, di.DocumentTitle,      f.FacilityID,tsl.TransmittalSiteLeadFacility,
ts.TransmittalNumber, ts.TransmittalVersion,
FacilityImpDate = COALESCE(tsl.TransmittalSiteLeadImpDate, ts.TransmittalImpDate)
   FROM dbo.DocumentInfo AS di
   INNER JOIN dbo.TransmittalSheet AS ts
     ON di.DocumentTransmittalImp = ts.TransmittalID
     LEFT OUTER JOIN dbo.TransmittalSiteLead AS tsl ON ts.TransmittalID =   tsl.TransmittalSiteLeadTSID
   INNER JOIN f
     ON tsl.TransmittalSiteLeadFacility = f.FacilityID

-- TSSiteLeadImpDate sometimes have different dates than TransmittalImpDate

-- Select documents implementing within the date range

        Where transmittalSiteLeadImpDate
    BETWEEN  @startdate AND @enddate
     order by di.DocumentNumber

    END

-- Above runs successfully but it returns partial data as I need something like this   for the last part in the date range query as it was working for the individual sp:

/*
-- Select documents implementing within the date range
            where ((SELECT dbo.TransmittalSiteLead.TransmittalSiteLeadImpDate
            FROM dbo.TransmittalSiteLead
            --WHERE dbo.DocumentInfo.DocumentTransmittalImp = dbo.TransmittalSiteLead.TransmittalSiteLeadTSID
            where (dbo.TransmittalSiteLead.TransmittalSiteLeadFacility = f.facilityID OR
                    dbo.TransmittalSiteLead.TransmittalSiteLeadFacility = @facilitydivisionID))
                     BETWEEN  @startdate AND @enddate)

order by di.DocumentNumber

              END
*/

I'm going to suggest working to the solution in two phases. The first part is to make sure all the data you need is being returned. The second part is to combine the rows the way that you want.

I'm having trouble getting SqlFiddle to work with table types as well, so I'm going to work with the query directly. Here is the base query from above:

;WITH f AS
(
SELECT FacilityID FROM @facilities
UNION ALL SELECT @facilitydivisionID
)
SELECT
  di.DocumentNumber, di.DocumentVersion, di.DocumentTitle,tsl.TransmittalSiteLeadFacility,
  ts.TransmittalNumber, ts.TransmittalVersion,
  FacilityImpDate = COALESCE(tsl.TransmittalSiteLeadImpDate, ts.TransmittalImpDate)
FROM
  TransmittalSiteLead tsl
  INNER JOIN TransmittalSheet as ts
    ON ts.TransmittalID =   tsl.TransmittalSiteLeadTSID
  INNER JOIN documentInfo as di
    ON ts.transmittalID = di.documentTransmittalImp
  INNER JOIN f
    ON tsl.TransmittalSiteLeadFacility = f.FacilityId
WHERE
  transmittalSiteLeadImpDate
    BETWEEN  @startdate AND @enddate

Even if there are duplicates or extra information, all of the data you want to return should be in there. If it isn't, then look at the JOINs or WHERE clause to see how to include that info.

The second part of the problem is to condense or limit the rows that are returned. There are a lot of ways to do this and the best way depends on a lot of factors. Here's what I'm sure is a partial list:

SELECT DISTINCT to limit results
GROUP BY with MIN to include information about a related table (See below)
EXISTS to limit the rows return
ROW_NUMBER() to pick a row based on an ordering (See below)
APPLY with TOP to get some information from a joined table

All this is to say that there's a lot of ways to do this, so do what makes sense for your situation. I've given two ways of doing this below, these aren't best for performance critical situations, but are easier to understand.

This query selects the document info and the staff id of someone who is implementing it.

;WITH f AS
(
SELECT FacilityID FROM @facilities
UNION ALL SELECT @facilitydivisionID
)
SELECT
  di.DocumentNumber
  ,di.DocumentVersion
  ,di.DocumentTitle
  ,MIN(tsl.TransmittalSiteLeadStaffId) AS TransmittalSiteLeadStaffId
FROM
  TransmittalSiteLead tsl
  INNER JOIN TransmittalSheet as ts
    ON ts.TransmittalID =   tsl.TransmittalSiteLeadTSID
  INNER JOIN documentInfo as di
    ON ts.transmittalID = di.documentTransmittalImp
  INNER JOIN f
    ON tsl.TransmittalSiteLeadFacility = f.FacilityId
WHERE
  transmittalSiteLeadImpDate
    BETWEEN  @startdate AND @enddate
GROUP BY
    di.DocumentNumber
    ,di.DocumentVersion
    ,di.DocumentTitle
ORDER BY
    di.DocumentNumber

Another way of doing it is using ROW_NUMBER. This way is very flexible, but can be more difficult to construct and understand.

This query selects matching documents and the facility and contact of the first to implement it. PARTITION sets how you want to divide the rows and ORDER BY sets how you want to select which matching row you want to use.

;WITH f AS
(
SELECT FacilityID FROM @facilities
UNION ALL SELECT @facilitydivisionID
),
FullData AS (
    SELECT
      di.DocumentNumber
      ,di.DocumentVersion
      ,di.DocumentTitle
      ,tsl.TransmittalSiteLeadFacility
      ,tsl.TransmittalSiteLeadStaffId
      ,ROW_NUMBER() OVER (PARTITION BY di.DocumentNumber ORDER BY tsl.TransmittalSiteLeadImpDate) AS RowNum
    FROM
      TransmittalSiteLead tsl
      INNER JOIN TransmittalSheet as ts
        ON ts.TransmittalID =   tsl.TransmittalSiteLeadTSID
      INNER JOIN documentInfo as di
        ON ts.transmittalID = di.documentTransmittalImp
      INNER JOIN f
        ON tsl.TransmittalSiteLeadFacility = f.FacilityId
    WHERE
      transmittalSiteLeadImpDate
        BETWEEN  @startdate AND @enddate
)
SELECT
    DocumentNumber
    ,DocumentVersion
    ,DocumentTitle
    ,TransmittalSiteLeadFacility
    ,TransmittalSiteLeadStaffId
FROM
    FullData
WHERE
    RowNum = 1
ORDER BY
    DocumentNumber


T-SQL to check whether or not a indexed view can be created on tableX? After applying my database changes on the test environment, the test team starts testing their processes,
one of the things I get is a truncate table error because the table is used in an indexed view.

    (1 row(s) affected)
    ProductIconTypes ending
    ProductImageCategories starting
    Msg 3729, Level 16, State 2, Line 1
    Cannot TRUNCATE TABLE 'dbo.ProductImageCategories'
because it is being referenced by object 'VWProductImageGetByStyle'.

Questions:

1)  next time I want to put an indexed view on one of my databases
what exactly I should be checking?

2) Can I get this done through my favourite route (T-SQL)?

I would not mind powershell either.

Unfortunately on these servers in question we are still on SQL Server 2005 AND SQL Server 2008 R2.

A Comment:

These versions impose a lot of limitations on things that we can do as alternatives. Filtered indexes for instance are not available on SQL Server 2005.

I am actually looking for a way if possible to find out what would stop working if I created this indexed view. In my example, the truncate tables could no longer work.

Since creating an indexed view is essentially creating a view WITH SCHEMABINDING you can no longer execute any DDL statements against the underlying objects which would affect the view definition. In this case TRUNCATE (a DDL operation) is viewed as view-affecting.

Detecting any queries that would break when you create views with schemabinding would equal detecting queries that execute DDL statements against the underlying tables and that depends on where your queries are coming from, but you basically need to look for ALTER TABLE|VIEW <myobject> for all objects used in your view or TRUNCATE TABLE <mytable>

If all your access is coming from stored procedures you could search them using this method.

If all your queries are generated from your application and you have the source code of it, you could search the source code or source control repository.

If you have a precompiled 3rd party app you could get lucky by using strings which also has a windows build.

If your applications are using ORM like frameworks you could be out of luck.


Converting character sets and foreign key constaints I'm trying to convert the character set and collation of my MySQL database and all containing tables from utf8/utf8_unicode_ci to utf8mb4/utf8mb4_unicode_ci.

I will refer to my database name as: MyDB
I will refer to my table(s) as: MyTable1 [, MyTable2, MyTable3, etc]

I have set the database character set and collation using:

ALTER DATABASE MyDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Then I used this SQL script to generate all of my table commands:

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ",
    "ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ")
    AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'MyDB';

This SQL generated a table with these contents:

ALTER TABLE MyDB.MyTable1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable2 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
... etc

Now my table has a lot of foreign key constraints and if I directly execute this query I get foreign key constraint errors because of incompatible character sets half way through the modification process:

#1025 - Error on rename of './MyDB/#sql-bba_3975' to './MyDB/MyTable1' (errno: 150)

So I figured I could just set FOREIGN_KEY_CHECKS to 0 before I run my query. I remember doing this before when I had to convert my database from latin1 to utf8.

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE MyDB.MyTable1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable2 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
... etc

I still get the same error for the same table.

SHOW ENGINE INNODB STATUS;

...
LATEST FOREIGN KEY ERROR
------------------------
161103  9:59:06 Error in foreign key constraint of table MyDB/MyTable1:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
 CONSTRAINT "MyTable1_ibfk_4" FOREIGN KEY ("MyTable1_Column1") REFERENCES "MyTable2" ("MyTable2_PK") ON UPDATE CASCADE
...

How could I still be getting foreign key errors if I have disabled foreign key checks?

I have tried some strange experiments...

SELECT @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=0;
SELECT @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=1;
SELECT @@FOREIGN_KEY_CHECKS;

All the selects return 1 when it should return 1 0 1.

But if I execute this:

SELECT @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=0;
SELECT @@FOREIGN_KEY_CHECKS;
#SET FOREIGN_KEY_CHECKS=1;
SELECT @@FOREIGN_KEY_CHECKS;

All of the selects return 0 when it should return 1 0 0.

This causes me to think that something is fundamentally wrong with my understanding of how the server session variables work.

Note:

I know I could remove all of the foreign keys in my database before I do the conversion but I'd rather not do that if I don't have to. My database has a lot of foreign keys and I don't want to miss one during the conversion.

Sort the ALTERs so that the FK checks will stop complaining.  Do a child before a parent (or is it the other way??).

If you have any circular references, then, yeah, remove and re-apply the FKs.

(You have pointed out yet another reason to eschew FKs.)

While you are at it, consider utf8mb4_unicode_520_ci; it comes from a newer Unicode standard.


Encounter installation problems on postgres 12 i install postgres-12,but report this error,i have execute sudo systemctl restart postgresql-12 ,and follow this install postgres12 on centos 7, and when i step into Step 5: Enable remote access,and execute sudo systemctl restart postgresql-12 it report error
[root@localhost ~]# systemctl status postgresql-12
● postgresql-12.service - PostgreSQL 12 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Sun 2020-10-25 12:29:05 CST; 11min ago
     Docs: https://www.postgresql.org/docs/12/static/
  Process: 7462 ExecStart=/usr/pgsql-12/bin/postmaster -D ${PGDATA} (code=exited, status=1/FAILURE)
  Process: 7456 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 7462 (code=exited, status=1/FAILURE)

Oct 25 12:29:05 localhost.localdomain systemd[1]: Starting PostgreSQL 12 database server...
Oct 25 12:29:05 localhost.localdomain systemd[1]: postgresql-12.service: main process exited, code=exited, status...LURE
Oct 25 12:29:05 localhost.localdomain systemd[1]: Failed to start PostgreSQL 12 database server.
Oct 25 12:29:05 localhost.localdomain systemd[1]: Unit postgresql-12.service entered failed state.
Oct 25 12:29:05 localhost.localdomain systemd[1]: postgresql-12.service failed.
Hint: Some lines were ellipsized, use -l to show in full.

[root@localhost ~]# journalctl -xe
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql-12.service has begun starting up.
Oct 25 12:56:02 localhost.localdomain postmaster[7586]: 2020-10-25 12:56:02.994 CST [7586] LOG:  starting PostgreSQL 12.4 on x86_64-pc-linux-gnu, compile
Oct 25 12:56:02 localhost.localdomain postmaster[7586]: 2020-10-25 12:56:02.995 CST [7586] LOG:  could not bind IPv4 address "172.18.56.1": Cannot assign
Oct 25 12:56:02 localhost.localdomain postmaster[7586]: 2020-10-25 12:56:02.995 CST [7586] HINT:  Is another postmaster already running on port 5432? If
Oct 25 12:56:03 localhost.localdomain postmaster[7586]: 2020-10-25 12:56:02.995 CST [7586] WARNING:  could not create listen socket for "172.18.56.1"
Oct 25 12:56:03 localhost.localdomain postmaster[7586]: 2020-10-25 12:56:02.995 CST [7586] FATAL:  could not create any TCP/IP sockets
Oct 25 12:56:03 localhost.localdomain postmaster[7586]: 2020-10-25 12:56:02.995 CST [7586] LOG:  database system is shut down
Oct 25 12:56:02 localhost.localdomain systemd[1]: postgresql-12.service: main process exited, code=exited, status=1/FAILURE
Oct 25 12:56:03 localhost.localdomain systemd[1]: Failed to start PostgreSQL 12 database server.
-- Subject: Unit postgresql-12.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql-12.service has failed.
--
-- The result is failed.
Oct 25 12:56:03 localhost.localdomain systemd[1]: Unit postgresql-12.service entered failed state.
Oct 25 12:56:03 localhost.localdomain systemd[1]: postgresql-12.service failed.
Oct 25 12:56:03 localhost.localdomain sudo[7572]: pam_unix(sudo:session): session closed for user root
Oct 25 12:56:03 localhost.localdomain polkitd[3564]: Unregistered Authentication Agent for unix-process:7574:595409 (system bus name :1.50, object path /
lines 2127-2149/2149 (END)

Your log lines are truncated (use --no-pager with journalctl when you want the full output) but the full error message that is the most relevant is probably:

2020-10-25 12:56:02.995 CST [7586] LOG: could not bind IPv4 address "172.18.56.1": Cannot assign [truncated here] requested
address

You did not mention what you set listen_addresses to, but a plausible explanation for this error is that it's set to 172.18.56.1, and this address is not assigned to a network interface of the host, so it's a mistake to use it in   listen_addresses.
Use ifconfig or ip a list to see the network addresses on the host.


Restoring SQL Server Database Command Line Error I have a restore database script in a SQL file, and I'm trying to restore a backup generated from one DB to a new DB (that exists). That SQL file is being executed by SQLCMD on the command line. The command window lets me know the restore finished successfully; however, the database is in a restoring state still, and cannot be accessed. Because of that, the rest of my batch process fails. How can I get the database out of the restoring state, and back into an online state so that I can run additional batch process scripts?

I'm using this to do it:

restore filelistonly
from disk = 'c:\program files\microsoft sql server\mssql10.mssqlserver\mssql\backup\olddb.bak'
GO

RESTORE DATABASE NewDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\olddb.bak'
WITH NORECOVERY, REPLACE,
MOVE 'OldDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\newdb.mdf',
MOVE 'OldDB_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\newdb.ldf'

Any ideas why my db is still in restoring state if it says it completed successfully?

Thanks.

You used NORECOVERY which leaves the database ready to receive diff/log restores

You can remove it above, or simply run this

RESTORE DATABASE NewDB WITH RECOVERY

See MSDN RESTORE, look for "[ RECOVERY | NORECOVERY | STANDBY ]" header


Microsoft SQL Server 2012 Mirrors Go Into Disconnected I have some Microsoft SQL server 2012 mirrors, without a witness in asynchronous mode, that are sporadically going into disconnected.  I am running SQL Server 2012 11.0.5058.  Looking at the logs on the mirror side I see

  Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 1

There is a whole bunch of memoryclerk messages

  Error: 802, Severity:17, state: 0.

There is insufficient memory available in the buffer pool.

Error:1454, Severity: 16, State: 1.

  Database mirroring will be suspended. Server instance 'instance' encountered error 802, state 0, severity 17 when it was acting as a mirroring partner for database 'database'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.

Any help as to what to check to find out why the mirrors go into suspended and then disconnected following this chain of events would be appreciated.  I can reestablish the mirrors after this and they work just fine for a couple of days and then it happens all over again.

I experienced very similar issue in my previous company. Database miroring was periodically getting suspended with the same error as you are getting. Mirroring never ever reconnected on its own, but usually I was able to resume it using:

ALTER DATABASE <Database Name> SET PARTNER RESUME

After a few weeks we figured out that even though there was enought memory on the server and enough memory was allocated to the instance our workload was very unevenly distributed and sometimes 100% of the memory was used. Since most of the time we had enough memory we were not authorised to purchase more. I ended up increasing buffer pool extension file and placing it on one of the SSDs which happened to have enough empty space to accommodate it. The problem with mirroring never returned again. I cannot guarantee that the same will work for you, I am mainly sharing my experience.


Microsoft Access Export to Excel I have an Excel spreadsheet that is filled with a ton of data which is organized by column (a column for names, a column for occupation etc.). I have recently moved all of this data into Microsoft Access 2010 to work with, but I do require to move this data back and forth between Access and Excel. Since then, I have made a lot of changes to the data and I now want to export this data back from Access into Excel, with the data appearing in the same columns that they were imported in from. I have tried exporting the data back into Excel, but it doesn't look the same as the excel file that I originally exported the data from.

I was wondering if there is a way to export all of the data in Access back into Excel so that it is formatted exactly the same way it was before it was exported into Access

Sorry if this is a noobish question, I'm new to Access and don't have much experience with it yet.

Thanks

The first and easiest option is to select the "External Data" option on the top and select Excel from the Export Data options. This lets you customize the export location and if you want to keep the Access formatting. If you wanted a different format you would have to manually change the column order before exporting to achieve this. If your referring to it not looking the same as before in Excel because it was a table there is a quick fix. Use the keyboard shortcuts ctrl + Home > ctrl + A > ctrl + L to create a table object which formats it nicely.

You can save this manual export process and re run it manually or in a macro if you choose to in the future. This saves time with manually recreating the export each time.

A more advanced option would be creating a VBA module to do the exporting and formatting keyboard shortcut for you. I have the code you can copy and paste from Access to Excel Export Code. You could additionally customize this code to format the columns in the correct order. I would recommend looking into the fld.OrdinalPostion property to do this here Microsoft Field.OrdinalPostion


BIGINT UNSIGNED value is out of range I suddenly experienced a query on my production site returning no results but also producing no errors, it was just returning zero rows.  Come to find out a subquery was producing the below error:

Error Code: 1690. BIGINT UNSIGNED value is out of range in '(s.subscriber_count - (s.subscribed_count - s.unsubscribed_count))'

My data set looks like:

I came across https://stackoverflow.com/a/11780905/197606 and haven't actually tried it because I want to understand why this is producing this error.  One comment mentions a negative expression, but there's no scenario in my situation where unsubscribed_count is less than subscribed_count.

Can anyone help me understand why I'm seeing this error?

The subtraction (-) causes the error.  It is pessimistic.  You are stuck with using CAST(... AS SIGNED) to avoid the error.

SHOW VARIABLES LIKE 'sql_mode';

See if it includes NO_UNSIGNED_SUBTRACTION

For 'rationale', it may be best to quote some (of many) bug reports on the topic:

https://bugs.mysql.com/bug.php?id=61410 :

  This is intentional change in behavior in 5.5. For those who needs this kind of statements to work and produce meaningful (unlike in pre-5.5) results, there is NO_UNSIGNED_SUBTRACTION SQL mode (http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_no_unsigned_subtractio...).

https://bugs.mysql.com/bug.php?id=46799 :

  http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html

  Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default. If the result would otherwise have been negative, it becomes the maximum integer value. If the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is negative.

https://bugs.mysql.com/bug.php?id=21070 :

  "Warning: When you use subtraction between integer values where one is of type UNSIGNED, the result is unsigned unless the NO_UNSIGNED_SUBTRACTION SQL mode is enabled. See Section 12.8, “Cast Functions and Operators”."


Why does SQL Server "Compute Scalar" when I SELECT a persisted computed column? The three SELECT statements in this code

USE [tempdb];
GO

SET NOCOUNT ON;

CREATE TABLE dbo.persist_test (
      id            INT NOT NULL
    , id5           AS (id * 5)
    , id5p          AS (id * 5) PERSISTED
);

INSERT INTO dbo.persist_test (id)
VALUES (1), (2), (3);

SELECT id
FROM dbo.persist_test;

SELECT id5
FROM dbo.persist_test;

SELECT id5p
FROM dbo.persist_test;

DROP TABLE dbo.persist_test;

generate this plan:

Why does the final SELECT, which is selecting a persisted value, generate a Compute Scalar operator?

Just to summarise the experimental findings in the comments this seems to be an edge case that occurs when you have two computed columns in the same table, one persisted and one not persisted and they both have the same definition.

In the plan for the query

SELECT id5p
FROM dbo.persist_test;

The table scan on persist_test emits only the id column. The next compute scalar along multiplies that by 5 and outputs a column called id5 despite the fact that this column is not even referenced in the query. The final compute scalar along takes the value of id5 and outputs that as a column called id5p.

Using the trace flags explained in Query Optimizer Deep Dive – Part 2 (disclaimer: these trace flags are undocumented/unsupported) and looking at the query

SELECT id5,
       id5p,
       ( id * 5 )
FROM   dbo.persist_test
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);

Gives the output

Tree Before Project Normalization

LogOp_Project

    LogOp_Get TBL: dbo.persist_test dbo.persist_test TableID=1717581157 TableReferenceID=0 IsRow: COL: IsBaseRow1002

    AncOp_PrjList

        AncOp_PrjEl QCOL: [tempdb].[dbo].[persist_test].id5

            ScaOp_Arithmetic x_aopMult

                ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id

                ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=5)

        AncOp_PrjEl QCOL: [tempdb].[dbo].[persist_test].id5p

            ScaOp_Arithmetic x_aopMult

                ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id

                ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=5)

        AncOp_PrjEl COL: Expr1004

            ScaOp_Arithmetic x_aopMult

                ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id

                ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=5)

Tree After Project Normalization

LogOp_Project

    LogOp_Get TBL: dbo.persist_test dbo.persist_test TableID=1717581157 TableReferenceID=0 IsRow: COL: IsBaseRow1002

    AncOp_PrjList

        AncOp_PrjEl QCOL: [tempdb].[dbo].[persist_test].id5

            ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id5

        AncOp_PrjEl QCOL: [tempdb].[dbo].[persist_test].id5p

            ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id5

        AncOp_PrjEl COL: Expr1004

            ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id5

So it appears that all the computed column definitions get expanded out then during the Project Normalization stage all the identical expressions get matched back to computed columns and it just happens to match id5 in this case. i.e. it does not give any preference to the persisted column.

If the table is re-created with the following definition

CREATE TABLE dbo.persist_test (
      id            INT NOT NULL
    , id5p          AS (5 * id) PERSISTED
    , id5           AS (5 * id)
);

Then a request for either id5 or id5p will be satisfied from reading the persisted version of the data rather than doing the calculation at runtime so the matching appears to happen (at least in this case) in column order.


Postgres column type unknown I am trying to port a database where there is a table with a column of type unknown. pg_restore raised the error

  pg_restore: [archiver (db)] could not execute query: ERROR:  column "color" has pseudo-type unknown`.

I don't understand why such tables can exist given that the doc says

  A pseudo-type cannot be used as a column data type

However, I want to keep the database as it is and do not do any modifications. Which error code should I specify to catch this exception?

This must be a dump from an old PostgreSQL database where it was still possible to generate unknown columns.

The best thing would be to fix the source database, but if you cannot do that, generate an SQL script from the dump with pg_restore and fix the column definitions there.


Calculating the total row size of a table - SQL Server I have a table 'Connections' with the following schema in SQL Server:

[ID] [int] IDENTITY(1,1) NOT NULL,
[User] [nvarchar](20) NOT NULL,
[UserID] [int] NULL,
[True] [bit] NOT NULL,
[Comment] [nvarchar](max) NOT NULL,
[Created] [datetime] NOT NULL,
[Flag] [bit] NULL,
[Destination] [varchar](20) NULL

and it has a primary key clustered index on the ID column.

I need to get the size generate by the entries on this table per month. I have search for any existing SP, function or any DMV that can help me with this but I only found how to get the size of the table not per row. Also I need the total size of the rows per month so cannot get the total size/minimum/maximum of the rows on the  whole table (as the solution provided in other stackexchange posts).

My attempt to this is as follows:

USE DB1;
SELECT DATEPART(year,created),
       DATEPART(month,created),
       (count (*))*(4+2*ISNULL((max(len([User]))),2)+4+1+2*ISNULL((max(len([Comment]))),2)+8+1+ISNULL(max(len([Destination])),2)) 'BytesPerMonth'
FROM Connections
GROUP BY DATEPART(year,created),DATEPART(month,created)

In the above I multiplied the number of rows with the byte size of a row and considered the following:

int - 4 bytes
nvarchar - 2 bytes per character ([nvarchar](max) also take 2 bytes per character, same as if we had [nvarchar](40), correct?)
bit - 1 byte
datetime - 8 bytes
varchar - 1 byte per character

However, this only provides an estimate due to only considering the max length of the variable columns and multiples that (the maximum) by the amount of rows which results in a much bigger value than the actual size of the rows. Is there a way that I can get the actual size per row in this context?

Furthermore, I am aware of the row header which is per row - another 4 bytes per row (currently I did not include this since my result was already huge due to considering the max of the variable columns). Also I have found that I should consider 3 bytes due to the null values and 8 bytes due to the variable columns in my schema, do they need to be considered per row/ per column? How can I calculate the size of the index?

You can use the DATALENGTH function to get the number of bytes for each column in a row. You can sum these up to get the total for the row, get the MIN, MAX, AVG and so forth.

SELECT DATEPART(year,created) AS [Year],
       DATEPART(month,created) AS [Month],
       COUNT(*) AS Rows,
       SUM(DATALENGTH([User]) + DATALENGTH([UserID]) + DATALENGTH([True]) + DATALENGTH([Comment]) + DATALENGTH([Created]) + DATALENGTH([Flag]) + DATALENGTH([Destination])) AS TotalRowSize,
       AVG(DATALENGTH([User]) + DATALENGTH([UserID]) + DATALENGTH([True]) + DATALENGTH([Comment]) + DATALENGTH([Created]) + DATALENGTH([Flag]) + DATALENGTH([Destination])) AS AvgRowSize,
       MIN(DATALENGTH([User]) + DATALENGTH([UserID]) + DATALENGTH([True]) + DATALENGTH([Comment]) + DATALENGTH([Created]) + DATALENGTH([Flag]) + DATALENGTH([Destination])) AS MinRowSize,
       MAX(DATALENGTH([User]) + DATALENGTH([UserID]) + DATALENGTH([True]) + DATALENGTH([Comment]) + DATALENGTH([Created]) + DATALENGTH([Flag]) + DATALENGTH([Destination])) AS MaxRowSize
FROM Connections
GROUP BY DATEPART(year,created),DATEPART(month,created)

The advantage of DATALENGTH is that it provides the actual used bytes for variable length fields, so there is no need to guess/estimate. See this db<>fiddle for a working example.


Why can't relational databases meet the scales of Big Data? It is often repeated that the Big Data problem is that relational databases can not scale to process the massive volumes of data that are now being created.

But what are these scalability limitations that Big Data solutions like Hadoop are not bound to? Why can't Oracle RAC or MySQL sharding or MPP RDBMS like Teradata (etc.) achieve these feats?

I am interested in the technical limitations - I am aware that the financial costs of clustering RDBMS can be prohibitive.

MS just had a tech talk in the Netherlands where they discussed some of this stuff.  It starts off slowly, but gets into the meat of Hadoop around the 20 minute mark.

The gist of it is that "it depends".  If you have a sensibly arranged, (at least somewhat) easy to partition set of data that (at least somewhat) is homogeneous, it should be fairly easy to scale to those high data volumes with an RDBMS, depending upon what you're doing.

Hadoop and MR seem to be more geared to situations where you are forced to to large distributed scans of data, especially when those data aren't necessarily as homogeneous or as structured as what we find in the RDBMS world.

What limitations are Big Data solutions not bound to?  To me, the biggest limitation they're not bound to is having to make a rigid schema ahead of time.  With Big Data solutions, you shove massive amounts of data into the "box" now, and add logic to your queries later to deal with the lack of homogeneity of the data.  From a developer's perspective the tradeoff is ease of implementation and flexibility on the front end of the project, versus complexity
 in querying and less immediate data consistency.


Keeping all SQL Server logs I can not find my SQL Server logs for all days in the past. In only found for last 4 days. Is it because that I restarted the server and logs are dumped if the hosting server is restarted?

How can I keep entire logs?

I am using the GUI to view the logs.

You have to configure  using SSMS GUI :

Make sure you have error logs set to manageable size.

Check MSDN : Configure SQL Server Error Logs and kb 2199578 for managing SQL Server error log.

EDIT:

Below is what we do in our PROD environments:

Recycle error logs using master.sys.sp_cycle_errorlog with a SQL Agent job.
If you need more than 99 days of log (since you mentioned in your comment - The maximum is 99 which will be reached very soon), you can use PowerShell to move the files to a safe location. I would doubt that you would need error logs past 99 days ! Just make sure that you keep them in a manageable size, else you it would be difficult for log viewer to open very large files.


MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+) I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records)

I'm going to be converting this table to InnoDB in a production environment.

The DB is on a cloud server with 8GB of RAM (will likely upgrade to 30GB RAM just for the conversion).

One method to do this is to simple write an ALTER query:

  What settings and configs would be optimal to convert in this way as quickly and smoothly as possible?

Some of the suggested settings I've read:

large innodb_buffer_pool_size (is 4GB the largest possible?)
preread your old myisam data/index files using shell commands
increase innodb_log_file_size (what's best size in the case?)
Do the alter table in X parallel threads, where X is the qty of CPU cores on your server (how can you do this?)
other minor tweaks (innodb_doublewrite=0, innodb_flush_log_at_trx_commit=0)

  Note: Similar question here (but much smaller table example with
  different server size):
  https://stackoverflow.com/questions/2081823/speeding-up-conversion-from-myisam-to-innodb/2081871#2081871

Of course, there are other methods to do this like:

Do a MYSQLDUMP of the old table and reinsert into a new clean InnoDB table,
Run "SELECT * ... INSERT INTO... " query, or
Run "SELECT INTO OUTFILE..." query on old table and then a "LOAD DATA INFILE... " query into the new table

1. How would you configure your settings for the purpose of making this alter go as quickly and as smoothly as possible?

2. Which method do you think is the best alternative?

RECOMMENDATION #1

Let's presume you have a fair amount of disk space on your cloud server. You create the InnoDB table in stages to watch the effect of diskspace and RAM in slow motion.

Let's say your MyISAM table is called mydb.mytable and you want to convert it to InnoDB.

Try the following:

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ENGINE=InnoDB;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Play with the table.

If you like its performance, then

DROP TABLE mydb.mytable_old;

If you do not like its performance, then

TRUNCATE TABLE mydb.mytable_old;
INSERT INTO mydb.mytable_old SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_zap;
ALTER TABLE mydb.mytable_old RENAME mydb.mytable;
DROP TABLE mydb.mytable_zap;

RECOMMENDATION #2

You mentioned you are running MySQL 5.1. Unless you install the InnoDB Plugin, InnoDB is single threaded. To get InnoDB to be multithreaded:

Install MySQL 5.6
Install MySQL 5.1's InnoDB Plugin if you want to keep MySQL 5.1 (Applicable to MySQL 5.1.38+)

Once you do either one, please configure InnoDB to engage multiple cores. See my past posts:

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance


Cassandra cluster configuration I have a cluster of 4 nodes in a data center dc1
Rack1. Now I have to create data center dc2 into same cluster. Will I need to reconfigure whole cluster or is there any way?

Before adding new datacenter in production environment, the first step should be to prevent the client from connecting to the new datacenter and to ensure reads or writes does not query the new datacenter unless it is completely ready to serve since it may result into latency and timeout errors.

These are few point should follow while adding a new datacenter.

Make sure to change QUORUM consistency level to LOCAL_QUORUM and ONE to LOCAL_ONE so your driver wont hit new data center until it is not ready to serve.
you have to Alter Keyspaces to use network topology if not using (Do not mention new datacenter in Alter Keyspace command at this point).
if using PropertyFileSnitch, cassandra-topology.properties file should be updated with new node's IPs on all the existing nodes.

After adding datacenter successfully.
1.alter Keyspace to set replication factor in new datacenter and mention the number of replica as well.


Limit has no effect on aggregation I have 2 tables:
users:
id
------
1

permissions:
id | action | userId
---|--------|--------
 1 |  read  |    1
 2 |  write |    1

When I run the following query (return users with its permissions):
select
    users.*,
    (string_agg(permissions.action::text, ',') as permissions
from
    "users"
join "permissions" "permissions" on
    "userId" = "users"."id"
    and "userId" = 1
group by
    "users"."id"
order by
    "users"."id" asc
limit 1

I get:
id | permissions
---|-------------
 1 |  read,write

Which is great (this is what I need) but I expected it to return:
id | permissions
---|-------------
 1 |  read

Because of the query has limit 1 constraint.
Anyone can shed some light on what happens in the background?

LIMIT is applied late in the query, after aggregation, so you get the complete first group.
You should apply the LIMIT in a lateral subquery:
SELECT users.*, p.action
FROM users
   CROSS JOIN LATERAL (SELECT permissions.action
                       FROM permissions
                       WHERE permissions."userId" = users.id
                       ORDER BY permissions.id
                       LIMIT 1) AS p
WHERE users.id = 1;

And alternative is the non-standard DISTINCT ON:
SELECT DISTINCT ON (users.id)
       users.*, p.action
FROM users
   JOIN permissions AS p
      ON p."userId" = users.id
WHERE users.id = 1;
ORDER BY users.id, permissions.id
LIMIT 1;


Make custom query from two table access I have two table that table 1 contains Number, code, ID and table 2 contains Name, Family name, Number, ID, code, Phone number,...

Now I want to make a query that contains table 2 fields but the query should check that if for example table2 contains  table1.Number, show the table 2 fields that contains Table1.Number and if table2 doesn't contains the Table1.Number, show the table 2 fields but just fill the fields that table1 and table 2 have them and just for example put "-" in the filed Phone number because table1 doesn't has that but it has code field and table 2 has it too so it should be fill it with the table 1 parameter
And this proccess should be continue for all of the codes of table 1

For example:

Table1 has this data

Table 2 data

Query example:

Can someone help me with this please?

Open the query designer for a new query.
Add the tables you are interested in.
Drag the field number from table1 to table2
Drag the field code from table1 to table2
Right click on each of the lines between the two tables and select join properties
Select option 2

Lastly in the bottom section of the designer select the columns you wish to see in your output


Can IDENTITY COLUMNS generate UUIDs? I'm just wondering if either the spec of the implementation (PostgreSQL) provides for generating identity columns from UUIds or the like.

Is there an alternative to,

CREATE TABLE f (
  id uuid DEFAULT gen_random_uuid()
);

Especially one that can protect the column under GENERATED ALAWYS

It seems that currently PostgreSQL only provides the following

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

The PostgreSQL docs go on to say

  This clause creates the column as an identity column. It will have an implicit sequence attached to it and the column in new rows will automatically have values from the sequence assigned to it.

So it seems it's sequence specific. The SLQ 2011 spec implies there is nothing more to it,

  The declared type of an identity column is either an exact numeric type with scale 0 (zero), INTEGER for example, or a distinct type whose source type is an exact numeric type with scale 0 (zero). An identity column has a start value, an increment, a maximum value, a minimum value, and a cycle option. An identity column is associated with an
  internal sequence generator SG.


Always check with SELECT before INSERT? If I want to INSERT a row only if the primary key doesn't yet exist, is it more efficient/simpler to execute INSERT directly and ignore the error in the case of duplication, or should i always run SELECT to check if it already exists first?

You could use WHERE NOT EXISTS to check new values before insert a new record.

INSERT INTO

(
    field1, field2, field3
)
SELECT value1, value2, value3
FROM dual
WHERE NOT EXISTS (SELECT 1
                  FROM

                  WHERE <pk fields> = <new values>);

CREATE TABLE foo(id int, v1 int, v2 int);
INSERT INTO foo VALUES (1, 100,100);
INSERT INTO foo VALUES (2, 200, 200);

This record exists and should not be inserted:

INSERT INTO foo (id, v1, v2)
SELECT 1, 101, 101
FROM   dual
WHERE NOT EXISTS (SELECT 1 FROM foo WHERE id=1);

This is a new record:

INSERT INTO foo (id, v1, v2)
SELECT 3, 300, 300
FROM   dual
WHERE NOT EXISTS (SELECT 1 FROM foo WHERE id=3);

The final result:

SELECT * FROM foo;

id |  v1 |  v2
-: | --: | --:
 1 | 100 | 100
 2 | 200 | 200
 3 | 300 | 300

dbfiddle here


MongoDB CSV Export: One Array/Column & Array Index on rows, or vice versa I have a MongoDB schema that looks like this:

const batchSchema = mongoose.Schema({
  _id: mongoose.Schema.Types.ObjectId,
  time: {type: [Number]},
  tmp: {type: [Number]},
  hum: {type: [Number]},
  co2: {type: [Number]},
  coolRelay: {type: [Boolean]},
  humRelay: {type: [Boolean]},
  fanRelay: {type: [Boolean]},
  lightRelay: {type: [Boolean]},
});

My goal is to have a csv file I can import to excel for use in creating charts, graphs, and other visuals based on the data.

Using Studio 3T(https://robomongo.org/), this doesn't seem to be possible unless I'm just not understanding the settings. Doesn't really matter if the data is organized horizontally or vertically. As long as each is on it own column/row, excluding the _id field. 3T seems to be only capable of placing them all along the same row.

I just tried using mongo's native cli csv export tool but found that didn't seem to come with my release. I'm running version 3.6 on openSuse, arm64, on a raspberry pi.

mongoexport as a command was not found, and when I ran the suggested cnf mongoexport to find a package containing it, nothing was returned either.

I know that I can export one file, per array using 3T, then cut/paste them together, however I hadn't planned on paying the annual license for 3T and was only using the free trial to get a more usable gui while in development. Was hoping to be able to write predefined bash script that could be reused repeatedly to export each "batch" document as it was completed.

Is there something I am missing about mongoexport or is this a limitation of the aarch64 release? I really don't want to have to go back, learn, and re-write everything for postgreSQL at this point. I mean, I will if I have to, but wanted to check in with you all first.

MongoDB CSV Export: One Array/Column & Array Index on rows, or vice
  versa

I will suggest to you, if you are unable to import or export collections data from MongoDB CLI. Then you install the Compass, From MongoDB Compass you can easily import and export the collection data.
As per MongoDB documentation here MongoDB Compass works with MongoDB Server version 3.0 and higher, both Enterprise and Community editions. It runs on 64-bit versions of Mac OS X, Windows, Ubuntu 14.04+ and RHEL 7+.

  Note: As of MongoDB Server version 3.6, MongoDB packages a platform-specific Compass installation script with MongoDB
  Server downloads.

For example

I have a database test and collection articles having the records such as

> use test
switched to db test
> show collections
alpha
articles
books
oplog
people
sales
sample
team
users

> db.articles.find().pretty();
{ "_id" : 1, "subject" : "coffee", "author" : "xyz", "views" : 50 }
{ "_id" : 2, "subject" : "Coffee Shopping", "author" : "efg", "views" : 5 }
{ "_id" : 3, "subject" : "Baking a cake", "author" : "abc", "views" : 90 }
{ "_id" : 4, "subject" : "baking", "author" : "xyz", "views" : 100 }
{
        "_id" : 5,
        "subject" : "Café Con Leche",
        "author" : "abc",
        "views" : 200
}
{ "_id" : 6, "subject" : "Сырники", "author" : "jkl", "views" : 80 }
{
        "_id" : 7,
        "subject" : "coffee and cream",
        "author" : "efg",
        "views" : 10
}
{ "_id" : 8, "subject" : "Cafe con Leche", "author" : "xyz", "views" : 10 }
>

The collections records are appearing in MongoDB Compass in Table format such as

Now I am going to export the collections records through MongoDB compass tool. After collecting with your host computer (either local or remote) with MongoDB compass tool . Select the database (here I have taken test database) then click on the collections name (here I have been taken articles) then click on the Collection menu tab. It will appears like that

According to your desire you click on the option, As here I want to export the collection data in .csv format then I will click on export command and then the screen will be appears like that

Then browse the file location as per your desire and export the collection records in .csv format.

After exporting the collections records from the compass in .csv format the appearance in .csv of data will be look like as follows:


Is there a way to get instance_id from msdb.dbo.sysjobhistory during the execution of a SQL Job step? I'm writing a logging process that will be stored in tables so that the information can be easily referenced and reported. Most of the structure already exists in the system tables for the SQL jobs (msdb.dbo.sysjobs, msdb.dbo.sysjobsteps, etc); however, I want more detail for each execution of a package, so I'm implementing a logging system that will keep track of each task in the package. Basically, for each task (Data Flow, Execute SQL Task, etc), a detailed, custom description of what has occurred will be stored. More specifically, in the event of a failure, the name of the task that failed and the reason for the failure can be quickly determined.

Tying each log record to the ID of the job and step is not an issue, those already exist and are easily captured. The instance_id generated in msdb.dbo.sysjobhistory, however, does not exist until after the execution of the step. Getting that ID doesn't seem possible during the execution of the package.

By the way, I tested when a record is inserted into msdb.dbo.sysjobhistory by creating a job and running a single step with the following T-SQL code:

WAITFOR DELAY '00:01:00'

The goal was to see when the instance_id is generated and inserted into msdb.dbo.sysjobhistory, which occurred after the step had completed.

If you create dummy steps between each real step, will that work?

So if step1 is Start Import, ans step2 is Run Import, you'll see step1 completed and you'll know step2 is running.

Alternatively, just insert all the steps into your own own audit table?


Automatically purge dropped tables in Oracle I am using an Oracle 18c database in a build system.

One of my builds is for a JPA/Hibernate project, which is configured as "create-drop". This means it will create all tables during startup and drop all of them again at the end.

The problem is: I can't configure Hibernate to create "drop ... purge" statements.

It seems that as a result the recycle bin has grown with each build, exceeding now the maximum allowed database size of 12 GB - and I am not even able to open the database anymore:

alter database open
*
ERROR at line 1:
ORA-12954: The request exceeds the maximum allowed database size of 12 GB.

I have now droped the PDB and created a new one - but this will last for at most a month, before I run into the same problem again.

My question: Is there any option to tell Oracle to immediately purge dropped tables?

Is there any option to tell Oracle to immediately purge dropped tables?

That would be disabling the recycle bin, if only for the current session. Include ALTER SESSION SET recyclebin = OFF in your build process, or, if this is a development system, you can consider turning it off via the instance initialization parameter.

Alternatively, you can choose running the build as a dedicated Oracle user; you will then DROP USER ... CASCADE at the end, which will drop all user's tables and other objects without placing them in the recycle bin.


sql agent job hangs without any errors or warnings Problem:

we have a SQL Agent job which runs everyday in the morning at 4:00 am. This job hangs sometimes without providing any errors or warnings. It just hangs. BUT when i stop the job and run again it works perfectly. This happens once in a week atleast.

This job is executing a SSIS package which loads data from oracle (source) to SQL SERVER 2008R2 (destination)

Steps Taken to Resolve issue

1) I thought the ssis package is losing connection in the middle of the process and i had increased the Connection Timeout=60 in the config file of this package but this doesn't worked. {I am using a xml configuration file for connection string }

2) I had configured logging for this package by using sysssislog table and i had checked this table when the job hanged, it doesn't showed any error information.

3) I informed this issue to our network administrator to check is there any network issues, but he told that everything is fine with the network.

4) in the OLEDB Destination editor i had used

Data Access Mode=Table or view-fastload

Table lock=checked

keep nulls=checked

check constraints=checked

rows per batch : blank

The above steps didn't worked yet, and it continues hanging oftenly.

Please share your opinions on this issue. I need your advice to troubleshoot this issue

I have a similar problem in SQL Server 2008. We have hourly schedule which pulls data from multiple Sybase Adaptive and Sybase IQ servers and populates the SQL Server (Using System DSN, Linked Server). Every Friday after 2 AM those jobs hang. After a little bit of research on my Windows Server which has the SQL Server I found that the McAfee weekly schedule runs every Friday from 2 AM.

As a short term solution, the anti virus scan can be cancelled and restart the SQL Server Service to clear the hang.

For long term, our security team has advised us to remove McAfee and load Kaspersky.

A note here.. during the anti virus scan only those jobs hang which pulls data from Sybase (as mentioned above), all other jobs will run perfectly fine.


SQL Server 2016 Restore Failure on DB Using Clustered Shared Volumes (Error 7627) We are new to using clustered shared volumes and SQL Server 2016 Enterprise Edition.

When restoring SQL Server 2005 databases with full text search catalogs onto our new 2016 instance, we get the following SQL Server error:

  Msg 7627, Level 16, State 1, Line 2
  Cannot create the full-text catalog in the directory C:\ClusterStorage\Volume2\SQLData\LogBook for the clustered server. Only directories on a disk in the cluster group of the server can be used.

We have restored this same database to a SQL Server 2016 without issue using:

USE [master];
RESTORE DATABASE [MYDATABASE]
FROM DISK = N'\\BACKUPSERVER\BACKUPS\DATABASE\Database_backup_201805070600.bak'
WITH FILE = 1,
     MOVE N'DatabaseData'
     TO N'C:\ClusterStorage\Volume2\SQLData\Database.mdf',
     MOVE N'DatabaseLog'
     TO N'C:\ClusterStorage\Volume1\SQLTranLogs\Database_log.ldf',
     MOVE N'sysft_LogBook'
     TO N'C:\ClusterStorage\Volume2\SQLData\LogBook',
     NOUNLOAD,
     REPLACE,
     STATS = 5;

Have any of you ran into this using clustered shared volume instead of just a plain volume?

Here are some shots of the setup on the cluster using CSVs.

Using the old method on shared volumes our dependencies looked like this example:

Now using clustered shared volumes which are using SMB they look like this:

In Windows Failover Cluster 2016, you need to ensure all storage (except for node-local-tempdb) is configured as a dependent resource in the SQL Server cluster role.

In the example below, you can see this cluster has two disks, Disk_F and Disk_G; those are the only places where SQL Server will allow us to create data and log files for SQL Server.

When restoring a SQL Server 2005 database that has a full text catalog, you likely need to specify the location of the full-text catalog files, otherwise SQL Server will attempt to put them in the default location, which may not be on a cluster shared volume.  Use RESTORE FILELISTONLY FROM DISK = '\path\to\backup'; to determine the files that exist in the backup.  Ensure your restore statement has a MOVE clause for each file in the backup.

As a workaround, you might try restoring the SQL Server 2005 full-text-search-enabled database onto a non-clustered SQL Server 2016 instance, then take a backup of the database from the 2016 instance to restore onto the clustered 2016 instance.  I'm thinking it's possible there is a bug in the restore process for old full-text-enabled databases onto modern clustered instances.


SSIS 2016 conditional split with multiple columns in conditions I am having an issue in executing a conditional split for upserting records into my production table, using SSIS 2016 and MSSQL 2016 (Standard Ed.)

I am trying to load two separate files (produced from an OpenVMS database) that contain similarly-formatted content, however they are from two different companies: AB_CustomerData.txt and CD_CustomerData.txt.

Customer Format files

RecordType: CU01
----------------
RecordType 2 characters
Company 2 characters
CustomerNumber 7 characters
CustomerName 50 characters

RecordType: CU02
----------------
RecordType 2 characters
Company 2 characters
CustomerNumber 7 characters
City 9 characters
State 8 characters

RecordType: CU03
----------------
RecordType 2 characters
Company 2 characters
CustomerNumber 7 characters
Phone 10 characters

AB_CustomerData.txt
-------------------
CU01AB0001234ABC Company
CU02AB0001234SmalltownAnywhere
CU03AB00012342135551212
CU01AB0002345Unbrella Corp
CU02AB0002345SmalltownAnywhere
CU03AB00023452135551213
CU01AB0003456MegaCorp
CU02AB0003456SmalltownAnywhere
CU03AB00034562135551214

CD_CustomerData.txt
-------------------
CU01CD0001234Jake's Widgets
CU02CD0001234SmalltownAnywhere
CU03CD00012342134441313
CU01CD0005678Jane's Doohickies
CU02CD0005678SmalltownAnywhere
CU03CD00056782135551314
CU01CD0006789Frank's Thingamabobs
CU02CD0006789SmalltownAnywhere
CU03CD00067892135551315

My end result is to have this in my production table:

Company | CustomerNumber | CustomerName         | City      | State    | Phone
-----------------------------------------------------------------------------------
AB      | 0001234        | ABC Company          | Smalltown | Anywhere | 2135551212
AB      | 0002345        | Umbrella Corp        | Smalltown | Anywhere | 2135551213
AB      | 0003456        | MegaCorp             | Smalltown | Anywhere | 2135551214
CD      | 0001234        | Jake's Widgets       | Smalltown | Anywhere | 2135551313
CD      | 0005678        | Jane's Doohickies    | Smalltown | Anywhere | 2135551314
CD      | 0006789        | Frank's Thingamabobs | Smalltown | Anywhere | 2135551315

I have a ForEach container to loop through these files in my directory, and do the following:

load the file into a pre-staging table
process the customer record
type (CU01, CU02, CU03 for each customer) into record-type specific
staging tables (ie: record-type CU01 goes to a CU01 staging table,
etc)
merge the record types into one larger staging table, containing
all records
merge join the staging table and the production table, to
prepare for upserting
upsert the production table

My conditional splits are defined as follows:

INSERT: (ISNULL(Production_CustomerNumber) && !ISNULL(Staging_CustomerNumber)) && (ISNULL(Production_Company) && !ISNULL(Staging_Company))

UPDATE: (!ISNULL(Production_CustomerNumber) && !ISNULL(Staging_CustomerNumber)) && (!ISNULL(Production_Company) && !ISNULL(Staging_Company))

DELETE: (!ISNULL(Production_CustomerNumber) && ISNULL(Staging_CustomerNumber)) && (!ISNULL(Production_Company) && ISNULL(Staging_Company))

On the first pass of the ForEach container, the data from the first company file loads correctly all the way through to production.  However, on the second pass of the ForEach container, any data pre-existing in the production table gets deleted.  I am almost positive it is because of my conditional split definitions, but I can't seem to figure out where.

I was close, but no cigar.  If I moved the "upsert the production table" task outside of the ForEach container (but population the staging table with the merge join inside the ForEach container), everything works as expected.