Store Function Value into temporary table Hi i'm currently trying to run a SQL query on a 15 000 + entry dataset. I'm using a custom Mysql function to determine the start of the current week and the end of it. Probleme is, I'm using those values in the WHERE clause of my query so it is executed at each comparaison, which take a lot of time.  I've discovered that those values never change, so I need to execute the function only one time to get the value, I can then pass the value to the WHERE clause.

The function itself does not takes long to execute, it's doing it 60 000 times that takes a long time.

I was wondering if there is a way to save those values into some kind of table where I could check if the start and end of the week has already been calculated and if so, use those value.
 I tried using a temporary table but I would need to know when the query is done so I could delete it.

Here is my code so far :

SELECT TIME_TO_SEC(TIMEDIFF(STR_TO_DATE(otherShifts.sft_end, '%Y-%m-%dT%H:%i:%s.000Z'),
                            STR_TO_DATE(otherShifts.sft_start, '%Y-%m-%dT%H:%i:%s.000Z'))) / 3600 as timestamps
                    FROM shift_sft as otherShifts,
                         shift_sft as originalShift
                    WHERE (
                        otherShifts.sft_start BETWEEN getStartOfWeek(originalShift.sft_id) AND getEndOfWeek(originalShift.sft_id) OR
                        otherShifts.sft_end BETWEEN getStartOfWeek(originalShift.sft_id) AND getEndOfWeek(originalShift.sft_id))
                    AND originalShift.sft_id = 1;

getStartOfWeek :

create function getStartOfWeek(shiftId int) returns varchar(122)
BEGIN
      DECLARE shiftStart varchar(122);
      DECLARE currentUserId INT(11);
      DECLARE currentSunday DATETIME;
      DECLARE currentWeekStart DATETIME;
      DECLARE currentStartingWeekSetting int;
      SELECT sft_start, sft_usr_id INTO shiftStart, currentUserId FROM shift_sft WHERE sft_id = shiftId;
      SET currentSunday :=  DATE_ADD(STR_TO_DATE(SUBSTRING_INDEX(shiftStart, 'T', 1), '%Y-%m-%d'), INTERVAL(1-DAYOFWEEK(STR_TO_DATE(SUBSTRING_INDEX(shiftStart, 'T', 1),  '%Y-%m-%d'))) DAY);
       SELECT FIELD(sde_day_week_start,'sun','mon', 'tue', 'wen', 'thu', 'fri', 'sat')
       FROM setting_default_sde, setting_business_sbu, v_user_business
       WHERE v_user_business.usr_id = 1
       AND setting_business_sbu.sbu_sde_id = setting_default_sde.sde_id
       AND setting_business_sbu.sbu_bus_id = v_user_business.bus_id;
       SET currentWeekStart := DATE_ADD(currentSunday, INTERVAL (currentStartingWeekSetting - 1) DAY);
       RETURN DATE_FORMAT(currentWeekStart, '%Y-%m-%d');
     END;

getEndOfWeek:

create function getEndOfWeek(shiftId int) returns varchar(122)
BEGIN
   DECLARE startOfWeek varchar(122);
   DECLARE endOfWeek DATETIME;
   SELECT getStartOfWeek(shiftId) INTO startOfWeek;
   SET endOfWeek := DATE_ADD(startOfWeek, INTERVAL 7 DAY);
   RETURN DATE_FORMAT(endOfWeek, '%Y-%m-%d');
 END;

Build a table with every day for the next few years:

 CREATE TABLE foo (
     dy DATE NOT NULL,   -- every day
     bow DATE NOT NULL,  -- date (yyyy/mm/dd) of the Beginning Of Week
     eow DATE NOT NULL,
     PRIMARY KEY(dy)
 ) ENGINE=InnoDB;

Then, instead of having a function, JOIN to that table to fetch the bow and/or eow needed.

Meanwhile, DATE_FORMAT(some_date, '%a') yields Sun, Mon, etc.  And WEEKDAY(some_date) gives you 0 for Monday, etc.  These might simplify some of your code.


Geo localized database RTREE I'm using MYSQL V5.6.24 GPL

I have a database with 200K rows and growing and I'm trying to get the nearest, based on points. I have all the database in InooDB, and to use Spatial data, I create one table with the Points with Mysiam.

everything works great, the problem is that when I sort based on ST_Distance, I'm not using any index, I create a SPATIAL index with the coordinates, and still using file_sort to sort. And Mysql doesn't allow me to use RTREE INDEX

Any idea?

In order to get advantage of the SPATIAL index you have to filter results using a bounding box (or any other geometry type) with the functions st_within() or st_contains(). You have some examples here:

Query Optimization with MySQL 5.7 and MariaDB 10 - GIS Improvements
Using the new spatial functions in MySQL 5.6 for geo-enabled applications
MySQL 5.7 and GIS, an Example


How can the concatenatation of a text and a NULL value return a non-null result? Assert 1
The concatenation operator || can concatenate any string type values, returning text. In Postgres, every type has a text representation and can be cast to text. Consequently, quoting the manual:

However, the string concatenation operator (||) still accepts non-string input, so long as at least one input is of a string type

Related:

How to concatenate columns in a Postgres SELECT?

Assert 2
Concatenating one or more NULL values makes the result NULL.
test=# SELECT (text 'foo' || NULL) IS NULL
test-#      , (text 'bar' || char '1' || NULL ) IS NULL
test-#      , (NULL::bigint || text 'baz') IS NULL;
 ?column? | ?column? | ?column?
----------+----------+----------
 t        | t        | t

Question
Is it possible to concatenate a text and a NULL value and get a non-null result?
In other words, how is this possible?
test=# SELECT col IS NULL AS col_is_null
test-#     , (text 'foo' || col) IS NULL AS result_is_null
test-# FROM   tbl;
 col_is_null | result_is_null
-------------+----------------
 t           | f

Applies to any Postgres version.
A client of mine stumbled over this, relying on the result to be NULL, and I found it intriguing enough to share.
It's a bit of a trick question as I know the answer.
Note: CASE or COALESCE catching NULL values are typically good style, but that's not what this question is about. It's about concatenation with an actual NULL value, with the concatenation operator || and still getting a non-null result.

That's because the system of CASTing array types in PostgreSQL is a bit weird (on a first sight) :-)

text || text[] makes both sides coerced to arrays.

CREATE TABLE tbl (col text ARRAY);

INSERT INTO tbl SELECT NULL;

SELECT col IS NULL AS col_is_null,
  (text 'foo' || col) IS NULL AS result_is_null
  FROM tbl;

 col_is_null | result_is_null
-------------+----------------
 t           | f
(1 row)

Another example which may shed more light:

create temp table x as select 'foo' test, null::text[] col;
SELECT test, col, test || col from x;
 test | col  | ?column?
------+------+----------
 foo  | NULL | {foo}
(1 row)


MySql Galera Multi site high availability We have a datacenter with 3 percona glera nodes

We have a store that wants to be in sync with our datacenter(multi master)

our store's internet connection is not reliable ( may be down for 30 min), it is adsl , then
we decided to get two internet connections from two different ISPs with static ip address

Questions are:

Is it possible for a single galera node to have multiple public ips?

2.When both internet connections go down. Local galera node will be
    unavailable too , How can we can configure it to avoid this?

3.Do you have any recommendation
    or better solution in this regard?

Consider:

Make it a 4-node Galera Cluster.  Have gcache set large enough to handle more than 30 minutes' worth of changes.  Then let Galera take care of the rest.

Meanwhile, your clients should not connect just to galera4, but via a proxy to any node.  (You may want to tell the proxy that "galera4" is preferred.)

But the real problem comes when you try to write to galera4 when it has lost connection to the other nodes.  A core principle of Galera says that an isolated node (like that) shall not accept writes.  Otherwise the data can become inconsistent.

Once you face that problem, you realize that "galera4" is not the solution.  Instead, working on the network is the solution, and having the proxy robust enough to find a way to the 3-node datacenter is sufficient.

Or...

NDB Cluster is a different clustering offering from MySQL.  It has a "eventually consistent" model for replication.  You can have any number of disconnected Masters, write to any of them.  The issue comes when you have conflicting writes (for example, two clients write with the same unique key).  The inconsistency is "eventually" caught, but this could be long after the client finished talking to the Master that it happened to reach.  At that point, some rule (such as "oldest timestamp wins") controls which record is kept and which is rejected.

NDB works well if you naturally are not going to write conflicting records.


MySQL how do I priorities single column by multiple keywords I have a column of product title, I have to order by result by max word's matched from entered string
Select Id, tittle, description,.....,
Case
When a.title REGEXP [[:<:]]Men's shoes[[:>:]] Then 0
When a.title REGEXP [[:<:]]Men's[[:>:]] Then 1
When a.title REGEXP [[:<:]]shoes[[:>:]] Then 2 END AS priority
From products
.
.
.

Order by priority ASC

It's work fine, when string come in order, but I want order by max matched word count,
or assigned first priority to that record which have max word matches

Full text searches have their good site, but need a bit of learning, but are interesting for big tables with alot of text.
you can combine columns also for searches
Also check the manual
A small example how it works

CREATE TABLe t1 (title varchar(20),
          FULLTEXT (title))

insert into t1 VALUES ("men\'s t'shirts"),("men\'s socks"),("men\'s socks short")

SELECT * ,
(IF(MATCH (title)
        AGAINST ("men\'s" IN NATURAL LANGUAGE MODE) > 0,1,0)
        )+
        (IF(MATCH (title)
        AGAINST ('socks' IN NATURAL LANGUAGE MODE) > 0,2,0) )
+
        (IF(MATCH (title)
        AGAINST ("+men\'s +socks" IN NATURAL LANGUAGE MODE) > 0.03,3,0))    as weight
        FROM t1

title             | weight
:---------------- | -----:
men's t'shirts    |      1
men's socks       |      6
men's socks short |      6

db<>fiddle here
For much bigger searches and more possibilities, you could also look into elasticsearch


Top 10,20,30 in Group Morning all!  I have a program and database I created that pulls down data from a website (player cost for MLB teams for a game) and I aggregate that data and use it for various things.

One of the things I need to find is the total cost per team for the cheapest 10, 20, and 30 players.  I've tried a number of suggestions I've seen online and none seem to work for me.

My database has the following fields:

Player | Overall | Sell | Buy | Series | Dash | Team | League | Division | Position | Pull_Date_Time

Here's what I currently use to get the total cost for each team:

select team, sum(buy) from Players
where series = 'Live' and pull_date_time = '2019-04-09 08:46:04'
group by team
order by sum(buy)

I need the same data - just only selecting the cheapest 10, 20 and 30 players for each team and grouped by team like shown above.  I do need to include the where clause that I'm currently using.  I don't need this to be just 1 query - I fully expect that each count (10, 20, 30) will need it's own query.

Assuming that you have SQLite 3.25.0 or later, use a window function to compute the number of a row in each sorted team:

SELECT Team,
       sum(Buy)
FROM (SELECT Team,
             Buy,
             row_number() OVER (PARTITION BY Team
                                ORDER BY Buy     ) AS RowNum
      FROM Players)
WHERE RowNum <= 10
GROUP BY Team
ORDER BY sum(Buy);


why backuped binlog is late I have problem, I would like to use mysqlbinlog for the backup, I do not know what causes it but the backuped binlog seems to be 4KB behind the binlog. I made a small shell.
while [ $i -lt 30 ]
do
mysql -D almafa -e 'insert  into b values("0HHHHHH");commit;'
   echo 'NUMBER:'$i >>/tmp/fut.lof34
   ls -la /tmp/almafamariadb-bin.000034  >>/tmp/fut.lof34
   ls -la /var/lib/mysql/mariadb-bin.000034   >>/tmp/fut.lof34
   i=`expr $i + 1`
done
systemctl restart mariadb

the new binlog was mariadb-bin.000034
sync_binlog=1
result is:
...
NUMBER:17
-rw-r--r-- 1 root root 0 Feb  3 10:30 /tmp/almafamariadb-bin.000034
-rw-rw---- 1 mysql mysql 3476 Feb  3 10:31 /var/lib/mysql/mariadb-bin.000034
NUMBER:18
-rw-r--r-- 1 root root 0 Feb  3 10:30 /tmp/almafamariadb-bin.000034
-rw-rw---- 1 mysql mysql 3650 Feb  3 10:31 /var/lib/mysql/mariadb-bin.000034
NUMBER:19
-rw-r--r-- 1 root root 0 Feb  3 10:30 /tmp/almafamariadb-bin.000034
-rw-rw---- 1 mysql mysql 3824 Feb  3 10:31 /var/lib/mysql/mariadb-bin.000034
NUMBER:20
-rw-r--r-- 1 root root 0 Feb  3 10:30 /tmp/almafamariadb-bin.000034
-rw-rw---- 1 mysql mysql 3998 Feb  3 10:31 /var/lib/mysql/mariadb-bin.000034
NUMBER:21
-rw-r--r-- 1 root root 4096 Feb  3 10:31 /tmp/almafamariadb-bin.000034
-rw-rw---- 1 mysql mysql 4172 Feb  3 10:31 /var/lib/mysql/mariadb-bin.000034
NUMBER:22

Set sync_binlog = 1
When sync_binlog is set to 0 (default), the OS caches all writes to disk and writes in 4K batches. This is for speed. If set to 1, every database update will be immediately written to the binary log. This may cause a higher iops consumption.


can you setup replication on mirrored SQL database Just moved to SQL2017 Ent. Currently, do transactional replication for 1.5TB DB. the replicated database is used for data analytics, mainly Power BI reports. Need near real-time data. When I have issues, initializing the DB is a pain due to size, I initialize from a backup but it takes too long and it is a manual process. was wondering if mirroring is set up, can I replicate the mirrored database, meaning can I set up full transactional replication on the mirror. This way I could initialize from a snapshot on the replicated DB instead of from a backup.
is the mirror only for failover? Can I make the mirror an active read-only copy?

Mirroring has been deprecated for a while - I wouldn't build out things with it at this point.
I think you may be better off spending time preventing issues that cause you to have to re-do your replication, but aside from that note I'd recommend looking at using Availability Groups instead of, or with, transactional replication.
For using with transactional replication:

When a published database is aware of Always On availability groups, the distributor that provides agent access to the publishing database is configured with redirected_publishers entries. These entries redirect the originally configured publisher/database pair, making use of an availability group listener name to connect to the publisher and publishing database. Established connections through the availability group listener name will fail on failover. When the replication agent restarts after failover, the connection will automatically be redirected to the new primary.

and

When an Always On availability group containing a database that is a replication subscriber fails over, the replication subscription might fail. For transactional replication push subscribers, the distribution agent will continue to replicate automatically after a failover if the subscription was created using the AG listener name.

are relevant.
But you could also potentially just use an asynchronous replica in an Availability Group to offer an up to date copy to your BI users as well.
You have a lot of options here and without knowing more details, I'm hesitant to recommend anything specific. But the documentation here is good and should be able to help you devise where to go next.


Does HP Vertica run on Windows and is it compatible with our existing Oracle SQL? I am new to HP Vertica. I read the installation documentation for HP Vertica, but it seems to be totally unix focused.  Can we install HP Vertica under Windows?

Additionally, will we need to update our existing Oracle SQL commands for use with HP Vertica?

I'm also unclear on the Vertica terminology:

Host: is a computer where the Vertica is installed.
Instance: it is also a computer where the Vertica is in running state.
Node: it is the combination of HOST & INSTANCE.
Cluster: I am not clear about this. May be this is about the instances of Vertica running on different computers.
Database : It is the actual installed database software where the data & catalog resides to which other nodes are connected (not sure about this also).

Please help me understand the basic concepts.

Vertica only runs on Linux, you can have a Windows client to acesso Vertica. But don't get scared Linux is not such a scary dragon kkkkk. Vertica installs as 1,2,3 check this link for a full installation article.
  Vertica uses SQL ANSI so no need for you to learn other languages, it has it's differences but is easy to work with.
 No need for you to break you brain in doing performance tuning and all those Oracle crazy queries, or create a billion indexes in SQL SERVER, Vertica does this for you.
With the Database designer you can throw your query into Vertica and Vertica will create the optimum design for you.

I think that Vertica arch is so simple that people are confused about it !! kkkk
HOST: is a computer where the Vertica is installed.
- is a linux box that has the Vertica software installed with the required configuration
to see the configuration needs go to this link for a full how to tutorial

INSTANCE: it is also a computer where the Vertica is in running state.
- this is the linux box with Vertica installed and up and running(as a single node or as part of a cluster) take a look at this link for more details about Vertica arch topology

Node: it is the combination of HOST & INSTANCE.
 Once more the link i gave you would explain this point. A node is a part of a cluster that has the Vertica software up and running on it. The instance is made of the processes that Vertica software creates when the Vertica engine is started.
 So in short Node is made of the Host(is the linux box with the Vertica software) and Instance(are the processes that the Vertica software creates) - this is not specifically for Vertica, it applyes to all databases.

CLUSTER: I am not clear about this. May be this is about the instances of Vertica running on different computers.
- a cluster is a collection of Vertica instances. Why do cluster is good ? MPP(massive parallel processing), HA(high availability).

DATABASE: It is the actual installed database software where the data & catalog resides to which other nodes are connected (not sure abt this also).
The database is combination of Software(all those binary+ more files filed with code + datafiles(these files hold the data that is processes by your database)) and the instance (once again the instance is the process created by your database software).
- I hope i could help !
Feel free to visit this link  for more Vertica scripts, articles and tutorials.
Also this link


What happens when CREATE INDEX gets interrupted (manually or accidentally)? It takes hours or even days to create an index for a table of millions of rows.

What happens to the database / data when CREATE INDEX gets interrupted? Such as when I manually used Ctrl-C or when there's an accident like a power-failure.

Does it do harm to the database / data?

If so, how do we recover from such an interruption?

Index creation is done within a transaction.  If you interrupt it, SQL Server will rollback the transaction.  This might take a while, but it's harmless.  Of course, there are locks held while the transaction is active so this can impact your server's performance.

If you lose power in the middle, SQL Server will automatically rollback the partially completed transaction when the server boots.  Again, there's a performance hit but there's no harm done to your data.

http://technet.microsoft.com/en-us/library/ms188317(v=sql.105).aspx

http://technet.microsoft.com/en-us/library/ms190925.aspx

http://technet.microsoft.com/en-us/library/aa224747(v=sql.80).aspx


How does Snapchat store its data? I am a highschool student who is interested in developing apps and learning more about databases and servers, so please bear with me if this is the wrong place to ask or if it's a obvious answer.

I have developed a few small database iPhone apps with Parse.com, so what somewhat of an idea of how a database works. But of course, I'm sure that is nothing compared to a real database.

My question is: How does an app like Snapchat go about storing its data (do you think)?

Does it just have one huge table of all the images? Meaning when someone uploads and app, does it create an object/row in a large table with the picture, who sent it, recipients, date, etc.? And then when the user logs in, does the app query that large table for all objects in that large table that has the currently logged in users id associated with recipients?

Talking very high-level, conceptual. This is my thought of how it would work, but I know there is millions of pictures on Snapchat servers, so I'm not sure how the speed of querying a large table like that would work, or how else it would be organized.

All good, future-proofed databases are normalised to some extent. Normalisation is described by Louis Davidson ~ Pro SQL Server 2008 Relational Database Design and Implementation as

  The process of normalization is really
  quite straightforward: take entities that are complex and extract
  simpler entities from them. The process continues until every table in
  the database represents one thing and every column describes that
  thing.

So based on your example I would imagine that the Snap Chat database (assuming that we are talking about OLTP model and not using NOSQL / BIGData) would have multiple entities, with each entity containing relevant data and be related to other entities via the appropriate relationship i.e. 1..Many, Many to Many etc. For example, a person entity might contain UserName and Password and be related to a loginToSite entity via a 1..M relationship. I suggest a 1..M relationship because one person can log in to the site multiple times but each login to site record will only be related to one user. If all attributes were stored in the a single entity then in this example the UserName and Password information would be duplicated per Login record and break the rules of normalisation. This would also lead to a database usability nightmare!!

Depending on business rules, and these would be questions you would have to ask snap chat. One user record might be related to one or many photo records or maybe one user can be related to one or many photo records and one photo record can be related to one or many user records. In this case, there would be a many to many relationship.

When thinking about database design it's important to first work out the conceptual design by talking with end users, reviewing reports / spread-sheets (anything containing data used by the end user) and generating an ERD. Then once the relationships have been agreed, all data to be stored captured and the ERD modelled you can move onto the physical side of things and work on performance, choosing data types, deciding on indexes etc...


Check if data from database is up to date I'm storing some data from database, and want to check if that data is up-to-date from the database version. What is the best way to do this?

My ideas:

Getting checksum from the table and comparing with last update
checksum (must be an overkill)
Getting table modification date and
comparing with current data update time

Just keep "last updated/last stored" as timestamp value. Easy to do even on a record level (i.e. for each record, and not for whole table).


Passing arguments to psql I'm running a plpgsql script in Postgres 8.3 - I would like to pass arguments to this script via psql. I'm currently executing the script like:

psql -d database -u user -f update_file.sql

I came across This link which explains PGOPTIONS environment variable, but that doesn't work for "custom" arguments. i.e. I receive an error because the setting isn't listed in the postgres.conf file.

-bash-3.2$ export PGOPTIONS='--pretend=true'
-bash-3.2$ psql -d my_db -f update_database.sql
psql: FATAL:  unrecognized configuration parameter "pretend"

Any other ideas? Ideally I'd like to avoid environment variables...

To add another functionality for -v... If you're trying to add the quotation, add it in the command line:

psql -v action="'drop'"

and this will run the code for:

select * where :action;

The same as

select * where 'drop';


How do I load a data-only dump created with pg_dump to another database? I have two PostgreSQL databases on my development server, one I use for development and another for production where I test my web applications. When I was using an earlier version of PostgreSQL I was able to easily create a TAR dump file of my prod database in pgAdmin and use that same TAR file to restore my development database after selection the option to clean the database first. A while back I upgraded pgAdmin to the version (1.20.0 I think) that is supposed to work with PostgreSQL 9.4. For whatever reason I cannot restore files in that version. I have tried TAR and SQL but the restore button is always protected.

I decided to use pg_dump and create a SQL file with only insert statements for my production database data. What I want to do is replicate what I was able to do in pgAdmin: Clean my development database and load my pg_dump SQL file into it.

I've search but so far I have not found any examples of how to do this in pgAdmin or in the terminal.

If you are happy with recreating the tables upon restore (and I see no reason why one should not be happy :), then you could use the --clean option:

  -c
  --clean

  Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Unless --if-exists is
  also specified, restore might generate some harmless error messages,
  if any objects were not present in the destination database.)

  This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call
  pg_restore.

Use it with pg_dump when creating plaintext dumps, or with pg_restore when using any other format.  In both cases, you will need a full dump, as a data-only one would lack the information for creating the tables.

Note: don't use INSERT statements in your dumps (--inserts does this in pg_dump).  It is almost invariably slower than relying on the default COPY functionality.


MySQL Replication - 1 Master - 2 Slaves. Inserting works for One, not the other I am a Python developer, working with 3 databases, configured in Master -> slave,slave style.

Each slave has their own application associated with it.

I am not too familiar with database administration as it relates to replication, but I do have a working scenario between the Master, and one of the slaves

The first slave stays in synch at all times properly at this time.

The second slave however, fails on the first insert

Table creation queries, user creaiton all replicates fine, but when I input any values, the first slave gets the record, while the second slave does not.

I tried importing the master DB to the second slave, updates to records will replicate, but new records will not.

My research leads me to believe this is an issue with autoincrement but I have not been able to use this to fix the issue. As opposed to a hack and slash method which is not working I figured id ask here.

If there is any more info I can provide, please let me know.

I am on Windows. Using WAMP Server 2.5 (MySQL 5.6.17)

Thanks for reading

EDIT: I am using binlog_format=mixed

Have you set your second slave to server_id = 3 (or depends on you, should be different from 1st slave)

and in mysql of second slave:

CHANGE MASTER TO MASTER_HOST='IP Address of Master Server',
MASTER_USER='slave_user2',
MASTER_PASSWORD='slave_user_password',
Below lines will need the values from ‘SHOW MASTER STATUS’ in Master Database
MASTER_LOG_FILE='',
MASTER_LOG_POS=;


Design database with versioning and without redundant information I have the following tables:

  Orders -> Procedures -> Steps -> Tasks -> NeededMaterials -> Materials

When I receive an order I want to generate procedures for its execution. The procedures are different depending on the order and its parameters.
Each procedure has some steps and each step contains several tasks. For each task are assigned materials.
In each procedure, the steps, tasks, and materials may be different, depending on the order's parameters, so I want to generate and store them.

All of the Procedures, Steps, Tasks and NeededMaterials are predefined, but at any time they may be updated(changed) and I need to keep track of all of the executed(generated) procedures with their steps, tasks, and materials. I need the possibility to trace back an order for example that was generated a year ago, and since then the procedure has been changed several times.

The problem is that I don't know how to do it while avoiding to store redundant information. Is it actually possible? Is there a design pattern? I can easily create another set of tables with the generated content, but then on every order I will store all of its data(i.e. all material names), because everything may be changed at any time.

If there is no way of avoiding redundancy, is NoSQL database a better approach?

I'd avoid using a NoSQL database for what is clearly relational data.

As far as I can tell, this design provided below might work for your system by providing versioning for all steps, components, materials, etc, without requiring unnecessary redundancy.  Clearly, if there is a new version of a given material, some of the material meta-data may not change, such as the material name, so there will naturally be some redundancy; however this design keeps that to a minimum.

USE tempdb;

CREATE TABLE dbo.Procedures
(
    ProcedureID int NOT NULL
    , ProcedureVersionNum int NOT NULL
    , ProcedureName varchar(30) NOT NULL
    , CONSTRAINT PK_Procedures
        PRIMARY KEY CLUSTERED
        (
            ProcedureID
            , ProcedureVersionNum
        )
);

INSERT INTO dbo.Procedures (ProcedureID, ProcedureVersionNum, ProcedureName)
VALUES (1, 1, 'Create SQL Server');

CREATE TABLE dbo.Steps
(
    StepID int NOT NULL
    , StepVersionNum int NOT NULL
    , StepName varchar(30) NOT NULL
    , ProcedureID int NOT NULL
    , ProcedureVersionNum int NOT NULL
    , ProcedureStepOrder int NOT NULL
    , CONSTRAINT FK_Steps_Procedure
        FOREIGN KEY (ProcedureID, ProcedureVersionNum)
        REFERENCES dbo.Procedures (ProcedureID, ProcedureVersionNum)
    , CONSTRAINT PK_Steps
        PRIMARY KEY CLUSTERED
        (
            StepID
            , StepVersionNum
        )
);

INSERT INTO dbo.Steps (StepID, StepVersionNum, StepName, ProcedureID, ProcedureVersionNum, ProcedureStepOrder)
VALUES (1, 1, 'Deploy Virtual Machine', 1, 1, 1)
    , (2, 1, 'Install SQL Server', 1, 1, 2)
    , (3, 1, 'Configure SQL Server', 1, 1, 3);

CREATE TABLE dbo.Tasks
(
    TaskID int NOT NULL
    , TaskVersionNum int NOT NULL
    , TaskName varchar(30) NOT NULL
    , StepID int NOT NULL
    , StepVersionNum int NOT NULL
    , TaskStepOrder int NOT NULL
    , CONSTRAINT FK_Tasks_Step
        FOREIGN KEY (StepID, StepVersionNum)
        REFERENCES dbo.Steps (StepID, StepVersionNum)
    , PRIMARY KEY CLUSTERED
        (
            TaskID
            , TaskVersionNum
        )
);

INSERT INTO dbo.Tasks (TaskID, TaskVersionNum, TaskName, StepID, StepVersionNum, TaskStepOrder)
VALUES (1, 1, 'Create VM', 1, 1, 1)
    , (2, 1, 'Install Operating System', 1, 1, 2)
    , (3, 1, 'Join VM to Domain', 1, 1, 3)
    , (4, 1, 'Run SQL Server Setup', 2, 1, 1)
    , (5, 1, 'Configure SQL Server Security', 3, 1, 1)
    , (6, 1, 'Create databases', 3, 1, 2);

CREATE TABLE dbo.Materials
(
    MaterialID int NOT NULL
    , MaterialVersionNum int NOT NULL
    , MaterialName varchar(30) NOT NULL
    , CONSTRAINT PK_Materials
        PRIMARY KEY CLUSTERED
        (
            MaterialID
            , MaterialVersionNum
        )
);

INSERT INTO dbo.Materials (MaterialID, MaterialVersionNum, MaterialName)
VALUES (1, 1, 'Virtual Machine')
    , (2, 1, 'Operating System Install Media')
    , (3, 1, 'SQL Server Install Media')
    , (4, 1, 'Disk');

CREATE TABLE dbo.NeededMaterials
(
    NeededMaterialID int NOT NULL
    , NeededMaterialVersionNum int NOT NULL
    , MaterialID int NOT NULL
    , MaterialVersionNum int NOT NULL
    , CONSTRAINT FK_NeededMaterials_Material
        FOREIGN KEY (MaterialID, MaterialVersionNum)
        REFERENCES dbo.Materials (MaterialID, MaterialVersionNum)
    , TaskID int NOT NULL
    , TaskVersionNum int NOT NULL
    , CONSTRAINT FK_Tasks
        FOREIGN KEY (TaskID, TaskVersionNum)
        REFERENCES dbo.Tasks (TaskID, TaskVersionNum)
    , PRIMARY KEY CLUSTERED
        (
            NeededMaterialID
            , NeededMaterialVersionNum
        )
);

INSERT INTO dbo.NeededMaterials (NeededMaterialID, NeededMaterialVersionNum, MaterialID, MaterialVersionNum, TaskID, TaskVersionNum)
VALUES (1, 1, 1, 1, 1, 1)
    , (2, 1, 2, 1, 2, 1)
    , (3, 1, 3, 1, 4, 1)
    , (4, 1, 4, 1, 1, 1)

CREATE TABLE dbo.Orders
(
    OrderID int NOT NULL
        PRIMARY KEY CLUSTERED
    , OrderDate datetime NOT NULL
    , CustomerName varchar(30) NOT NULL
);

INSERT INTO dbo.Orders (OrderID, OrderDate, CustomerName)
VALUES (1, '2018-03-15 09:24:00', 'Max Vernon');

CREATE TABLE dbo.OrdersProcedures
(
    OrderProceduresID int NOT NULL
        CONSTRAINT PK_OrdersProcedures
        PRIMARY KEY CLUSTERED
    , OrderID int NOT NULL
    , ProcedureID int NOT NULL
    , ProcedureVersionNum int NOT NULL
    , CONSTRAINT FK_OrdersProcedures_Procedures
        FOREIGN KEY (ProcedureID, ProcedureVersionNum)
        REFERENCES dbo.Procedures (ProcedureID, ProcedureVersionNum)
);

INSERT INTO dbo.OrdersProcedures (OrderProceduresID, OrderID, ProcedureID, ProcedureVersionNum)
VALUES (1, 1, 1, 1);

Show the order details:

SELECT o.CustomerName
    , o.OrderDate
    , p.ProcedureName
    , p.ProcedureVersionNum
FROM dbo.Orders o
    INNER JOIN dbo.OrdersProcedures op ON o.OrderID = op.OrderID
    INNER JOIN dbo.Procedures p ON op.ProcedureID = p.ProcedureID AND op.ProcedureVersionNum = p.ProcedureVersionNum;

╔══════════════╦═════════════════════════╦═══════════════════╦═════════════════════╗
║ CustomerName ║        OrderDate        ║   ProcedureName   ║ ProcedureVersionNum ║
╠══════════════╬═════════════════════════╬═══════════════════╬═════════════════════╣
║ Max Vernon   ║ 2018-03-15 09:24:00.000 ║ Create SQL Server ║                   1 ║
╚══════════════╩═════════════════════════╩═══════════════════╩═════════════════════╝

SELECT p.ProcedureName
    , s.StepName
    , t.TaskName
    , t.TaskStepOrder
    , m.MaterialName
FROM dbo.Procedures p
    LEFT JOIN dbo.Steps s ON p.ProcedureID = s.ProcedureID AND p.ProcedureVersionNum = s.ProcedureVersionNum
    LEFT JOIN dbo.Tasks t ON s.StepID = t.StepID AND s.StepVersionNum = t.StepVersionNum
    LEFT JOIN dbo.NeededMaterials nm ON t.TaskID = nm.TaskID AND t.TaskVersionNum = nm.TaskVersionNum
    LEFT JOIN dbo.Materials m ON nm.MaterialID = m.MaterialID AND nm.MaterialVersionNum = m.MaterialVersionNum
ORDER BY p.ProcedureName
    , s.ProcedureStepOrder
    , t.TaskStepOrder;

Results:

╔═══════════════════╦════════════════════════╦═══════════════════════════════╦═══════════════╦════════════════════════════════╗
║   ProcedureName   ║        StepName        ║           TaskName            ║ TaskStepOrder ║          MaterialName          ║
╠═══════════════════╬════════════════════════╬═══════════════════════════════╬═══════════════╬════════════════════════════════╣
║ Create SQL Server ║ Deploy Virtual Machine ║ Create VM                     ║             1 ║ Virtual Machine                ║
║ Create SQL Server ║ Deploy Virtual Machine ║ Create VM                     ║             1 ║ Disk                           ║
║ Create SQL Server ║ Deploy Virtual Machine ║ Install Operating System      ║             2 ║ Operating System Install Media ║
║ Create SQL Server ║ Deploy Virtual Machine ║ Join VM to Domain             ║             3 ║ NULL                           ║
║ Create SQL Server ║ Install SQL Server     ║ Run SQL Server Setup          ║             1 ║ SQL Server Install Media       ║
║ Create SQL Server ║ Configure SQL Server   ║ Configure SQL Server Security ║             1 ║ NULL                           ║
║ Create SQL Server ║ Configure SQL Server   ║ Create databases              ║             2 ║ NULL                           ║
╚═══════════════════╩════════════════════════╩═══════════════════════════════╩═══════════════╩════════════════════════════════╝

Cleanup:

DROP TABLE dbo.OrdersProcedures;
DROP TABLE dbo.Orders;
DROP TABLE dbo.NeededMaterials;
DROP TABLE dbo.Materials;
DROP TABLE dbo.Tasks;
DROP TABLE dbo.Steps;
DROP TABLE dbo.Procedures;


Term for a one-row table? Is there a specific term to describe a relation with a cardinality of one?

I'm thinking particularly of RDBMS "special" one-row tables, for example, DUAL (Oracle), SYSIBM.SYSDUMMY1 (DB2, Derby) and RDB$DATABASE (Interbase, Firebird).

Singleton Table. In relational terms it is a relation with primary key ∅ (the empty set).


Unknown column in 'where clause' I've a query in my php script

$result = mysql_query("SELECT State_Id FROM state_master WHERE State_Name='$statename'") or die(mysql_error());
$row=mysql_fetch_assoc($result);
return $row['State_Id'];

Which returns 23Unknown column 'State_Name' in 'where clause' where 23 is the correct state ID. What I'm doing wrong?

Is there any way to avoid the error and just to return the value?

I'm new in this so I hope someone will point out my stupid mistake.

You need to run DESC state_master;. This will show you all the columns in that table.

Fine the correct column name for the state name. Evidently, from the error, it is not State_name. Then, use that correct column name in the SQL Statement.

If State_Name is the actual column name, then try lowercase on all column names

$result = mysql_query("SELECT state_Id FROM state_master WHERE state_name='$statename'") or die(mysql_error());
$row=mysql_fetch_assoc($result);
return $row['state_Id'];

Give it a Try !!!


How to import sql file in stored procedure in MySQL? What I want to do is as follows.

In a stored procedure:

Access the DB to retrieve some record of a file name X
Check whether a file named X.sql exists
source X.sql; (or something similar)

Is that doable? Or do I have to go to UDF?

Thanks.

Not everything can be done in SQL.  In particular, it is not possible (without a UDF) to reach outside of MySQL to do file operations (other than LOAD FILE).  This is a security limitation.

The "right" way to deal with a requirement is to code it in your application language (PHP / Java / Python / ...).  Some steps would be SQL; some would be file operations.


How do I shrink the physical Transaction Log file when it's the principal in a mirror? We setup database mirroring over the weekend, and forgot to re-enable the job that backs up the transaction logs. When I came in this morning, the transaction log had ballooned to 58GB, and was taking up most of the drive space.

I did a manual backup of the transaction log to disk to get the database running again, however running DBCC SHRINKFILE does not appear to decrease the physical size of the transaction log file.

DBCC SHRINKFILE (N'MyDatabaseName_Log', 1000)

If I check out the log usage using

DBCC SQLPERF(LOGSPACE)

I can see that only 22% of the current log is being used

Database Name    Log Size(MB)    Log Space Used (%)    Status
MyDatabaseName   55440.87        22.38189              0

If I check out log_reuse_wait_desc in sys.databses, the only record I see is DATABASE_MIRRORING, so I am guessing the mirror is playing a role in why the physical size of the log file won't shrink?

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = N'MyDatabaseName';

I've also noticed my principal database mirroring state is Suspended, and trying to Resume it immediately fails with the following error:

  The remote mirroring partner for database 'MyDatabaseName',
  encountered error 5149, status 1, severity 25. Database mirroring has
  been suspended.  Resolve the error on the remote server and resume
  mirroring, or remove mirroring and re-establish the mirror server
  instance.

The error logs on the mirror server contains this error as well, but also contains errors about the log file drive being full

  MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file.

and

  F:\Databaselogs\MyDatabaseName_1.ldf: Operating system error 112(There is not enough space on the disk.) encountered.

The principal server has 60GB on the log file drive (there are other databases hosted here), while the mirrored server only has 45GB.

Backing up the log file made the database usable again, however I also want to decrease the size of the physical log file on the disk, and get the mirroring resumed.

How can I shrink the size of my physical transaction log file without compromising the mirroring or the backup chain?

I am running SQL Server 2005

From what I can tell, I can't.

I believe much of the log file is waiting to be mirrored to the mirror server, but the mirror is down and can't be resumed because the mirrored transaction log has also grown to take up all the space on the disk.

This theory is further backed up by the fact once I remove the mirroring, the DBCC SHRINKFILE command correctly shrinks the physical log file, and the log_reuse_wait_desc is back to waiting on LOG_BACKUP

I can't shrink the log file on the mirrored server because it's acting as a mirror and can't be opened, so I think the mirror is unrecoverable.

So I am going to delete the mirror entirely, and set everything up again (very slow process with a 300GB database). And this time, I'll make sure the transaction log size is fairly small before starting up the mirror, and that the transaction log backups are running once the mirror is back up and running.

I'm also going to set a limit on how big the transaction log can grow on the production server, so the mirror doesn't ever try to grow its transaction log beyond the size available on the disk.


SQL Server - Recursive "find all FK connections for ID through the entire DB" query I'm dealing with a DB with hundreds of tables, with interconnecting FK references producing potentially thousands of different permutations for how for example table A and table K can join together. Now, I need to create a query that will be given a single table name and it's PK ID, and then using FK references in the DB would find ALL connections regardless of how far down the line it has to go.

Now, I'm not looking for a script, as I understand this will become quite complex. I'm looking for strategic advice on what would be the best way to create such a script, or tips on where to find previous similar examples.

Currently what I have is a procedure that can find all TABLE + PK_ID values referencing, or referenced by one TABLE + PK_ID. And what I was thinking of doing is building up some kind of a looping TSQL that would use that procedure and a number of #temp tables to find the connections in levels. But it's going to be tricky to make this thing truly recursive.

Basically, say I was looking for the connections to and from table A, it could produce a list logically similar to the one below:

      A - J   I - E
     / \     / \ /
H - B   C - D - F - O
 \ /       /
  G ------/

How about implementing a child/parent relationship in a table including the hierarchical structure. You could fill that iteratively for every table in your DB, if you add a parent you'd update all its children's hierarchical structure.

To find specific results for a certain table you'd then simply have to select all records that contain that table in its hierarchy.

(Hierachies in your example would look something like this:

/H/B
/H/G/B
/H/B/A
/H/G/B/A
...
)


activate Service Broker on a database in an Availability Group I found some older posts dealing with this(5 yrs old or more), but I am confused on the process or if this information is out of date, hence my new question.  I am on sql 2016 SP2-CU11 enterprise.
I need to enable service broker on a database that is in an availability group.  Its 1.8TB in size, so I really don't want to take it totally out of the AG and reseed it all over again with a fresh backup if I can avoid it.  Some older posts say you have to completely remove and add the db back in the AG, others reference just running some code to create endpoints, and routes which I have never run across before.
Is the process below the only way to enable service broker on a DB in an AG where it is not enabled?

remove the secondary copy of the DB, then the primary copy from the AG.

Run below code to activate service broker:
ALTER DATABASE XXXXXX SET ENABLE_BROKER;
GO

Take a full backup, restore it to the secondary with no recovery.

join the DB to the AG on the secondary.

First, create and configure the service broker endpoint
select * from sys.tcp_endpoints

Create a new end point for the service broker:
CREATE ENDPOINT [Avaruu5alus]
    STATE = STARTED
    AS TCP  (LISTENER_PORT = 4022, LISTENER_IP = ALL )
    FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS)

GRANT CONNECT ON ENDPOINT::[Avaruu5alus] TO [PUBLIC]

SQL Server Broker is not Multisubnet aware. You need to set RegisterAllProvidersIP to 0
Thereafter take the availability group offline and bring online again.
USE [master]
GO
ALTER DATABASE [Avaruu5alusDB] SET  ENABLE_BROKER WITH NO_WAIT
GO


Moving a date between tables is causing error ORA-01841 Here's my script:

INSERT INTO BRAND_RECORD
SELECT
    STRENGTH,
    GENERIC_NAME,
    TO_DATE(a1.FIRST_CLAIM_DATE) as INTERCHANGEABILITY_DATE
FROM DRUG_INFO
INNER JOIN
(
    SELECT
        FIRST_CLAIM_DATE,
        SEQUENTIAL_ID
    FROM DRUG_INFO
    WHERE
        SEQUENTIAL_ID IN
        (
            SELECT MIN(SEQUENTIAL_ID)
            FROM
            (
                SELECT *
                FROM DRUG_INFO
                WHERE
                    SEQUENTIAL_ID NOT IN
                    (
                        SELECT MIN(SEQUENTIAL_ID)
                        FROM DRUG_INFO
                        GROUP BY GENERIC_NAME, STRENGTH
                    )
            )
            GROUP BY GENERIC_NAME, STRENGTH
        )
) a1
    ON (a1.SEQUENTIAL_ID = DRUG_INFO.SEQUENTIAL_ID + 1);

Summary: Everything after INSERT INTO BRAND_RECORD works perfectly well. It is a query that produces a STRENGTH, GENERIC_NAME, and INTERCHANGEABILITY_DATE.
BRAND_RECORD is a table with the columns STRENGTH, GENERIC_NAME, INTERCHANGEABILITY_DATE.

However, I get the following error:

  Error report - SQL Error: ORA-01841: (full) year must be between -4713
  and +9999, and not be 0
  01841. 00000 -  "(full) year must be between -4713 and +9999, and not be 0"
  *Cause:    Illegal year entered
  *Action:   Input year in the specified range

This seems like some sort of conversion problem. But I'm selecting a date and putting it into a table that takes a date! Any idea what the problem might be?

UPDATE: I changed my default date format to MM/DD/YYYY HH24:MI:SS. I now get the following error when I try this:

  Error report - SQL Error: ORA-01858: a non-numeric character was found
  where a numeric was expected 01858. 00000 - "a non-numeric character
  was found where a numeric was expected" *Cause: The input data to be
  converted using a date format model was incorrect. The input data did
  not contain a number where a number was required by the format model.
  *Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation. Error
  starting at line : 1 in command - INSERT INTO BRAND_RECORD Error at
  Command Line : 1 Column : 24 Error report - SQL Error: ORA-00926:
  missing VALUES keyword 00926. 00000 - "missing VALUES keyword" *Cause:
  *Action:

Here are my create table statements for both tables:

CREATE TABLE BRAND_RECORD
(
  GENERIC_NAME VARCHAR2(60 BYTE) NOT NULL
, INTERCHANGEABILITY_DATE DATE
, STRENGTH VARCHAR2(25 BYTE)
)
LOGGING
TABLESPACE DATAL03
PCTFREE 10
INITRANS 1
STORAGE
(
  INITIAL 65536
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  BUFFER_POOL DEFAULT
)
NOCOMPRESS
NOPARALLEL;

CREATE TABLE DRUG_INFO
(
  DIN VARCHAR2(20 BYTE) NOT NULL
, TRADE_NAME VARCHAR2(50 BYTE) NOT NULL
, FORM VARCHAR2(40 BYTE)
, STRENGTH VARCHAR2(25 BYTE)
, GENERIC_NAME VARCHAR2(60 BYTE) NOT NULL
, MANUFACTURER VARCHAR2(100 BYTE) NOT NULL
, CR_DATE DATE
, NOC_DATE DATE
, FIRST_CLAIM_DATE DATE
, SEQUENTIAL_ID NUMBER NOT NULL
)
LOGGING
TABLESPACE DATAL03
PCTFREE 10
INITRANS 1
STORAGE
(
  INITIAL 65536
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  BUFFER_POOL DEFAULT
)
NOCOMPRESS
NOPARALLEL;

Zgguy answered this in comments:

Explicitly stating columns in the insert solved the problem, as it was trying to put columns in the wrong columns.

INSERT INTO BRAND_RECORD (STRENGTH, GENERIC_NAME, INTERCHANGEABILITY_DATE)


Why some of mysql system variables are readonly There are almost more or less 277 mysql server system variables .
In these variables some of the variables are dynamic and few are not.

I don't under stand what is the reason behind read only variables .  why few of mysql system variables are read only. what will happen if they make those variable dynamic.

Is there any deep reason for the read only variables?

For now we can consider :

innodb_buffer_pool_size
innodb_checksums
datetime_format
log-bin

and there are many other variable we can find at
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html

Of course, I know we can't change variable like version. But many other variables like log-bin are not dynamic. For me, it does not make sense if general_log and log_slow_queries are dynamic. Why is log-bin not dynamic? like wise there are many other variables.

innodb_buffer_pool_size
The InnoDB Storage Engine has too many moving parts to just allow the buffer pool to be resized on the fly because the buffer pool interacts with system tablespace (ibdata1) and the transactions logs (ib_logfile0, ib_logfile1).
log-bin
In spite of the MySQL Documentation, you can change the effect of log-bin using another variable called SQL_LOG_BIN. Look at the effects:
SESSION
If you run
SET SQL_LOG_BIN = 0;

this will disable your DB Session from recording binlog events.
If you run
SET GLOBAL SQL_LOG_BIN = 0;

this will disable all incoming DB Sessions from recording binlog events.
General Log / Slow Log
As a DBA, I am very glad that the general log and slow log are dynamic. Otherwise

how could do perform any log rotation ?
how to stop these logs before diskspace runs out ?

Here are some posts I made about doing log rotations

Nov 10, 2012 : Redirecting MySQL errors and slow logs into another database
Mar 30, 2012 : MySQL slow log configuration
Jun 30, 2011 : How do I output MySQL logs to syslog?

Datetime Format
mysqld is usually cognizant of timezone and internalization of datetime displays. I do not see a beneficial reason for shifting the datetime format on demand. This may actual cause problems, not so much with mysqld, but with PHP/Python/Ruby scripts that read dates from the MySQL instance and may misinterpret '04/12/2013' as April 12th when it should be Dec 4th. That would be developer's nightmare to deal with. Having datetime format a read-only variable simply provides a safety net against such a death-defying move whereas proper planning of the database and code interoperability with dates would be in order.


Increasing enough RAM for OS from Total Physical RAM Here is my situation:

Total Physical RAM : 128 GB

Assigned to SQL Server : 122 GB (Lock pages in memory for SQL Server Account)

So for OS we have close to 6 GB left now.

Now we are facing issue like below:

  SQL Server:Buffer Manager Page writes/sec has exceeded the threshold
  of 90

Now we are planning to decrease the assigned SQL Server Maximum memory from
122 GB to 111 GB.

I used below links to set maximum server settings for SQL Server.

https://github.com/bornsql/scripts/blob/master/max_server_memory.sql

https://bornsql.ca/s/memory/

Will, increasing Physical RAM for OS level help us ?

When I checked the Task Manager process utilization SQL Server doesnot seems to be using a lot of Memory.
It is servicehosts and Microsoft Monitoring tool that consumes along with SQL Server occupies second and third position.

I know there are plenty of forums which will help to understand the memory logics in SQL Server but may I know where should I start learning about SQL Server Memory utilization.

How can I find what is consuming the most memory in SQL Server ?

Please advice me ?

(Full disclosure: I created the Max Server Memory Matrix and associated script that you are referring to in your question.)

Per this excellent blog post, which contains a detailed explanation of the issue you're experiencing, you'll find this little quote:

  The recommended Page reads/sec value should be under 90. Higher values
  indicate insufficient memory and indexing issues.

Without knowing too much about your environment, as you did not give us very much to go on, you may benefit from more RAM. For instance, your database is 400GB in size, and data access patterns require large scans to access lots of data.

On the other hand, your indexes might need attention. For instance, your database is 400GB in size, and data access patterns require large scans, when in fact you only need to read one or two columns in that table.

Instead of worrying about buying hardware, perhaps it would benefit you to start with the basics:

Have you analysed the missing index DMVs to identify possible new indexes?
Have you considered using filtered indexes?
Have you rewritten your queries to make better choices with data?

The point is, there's a lot you can do before considering purchasing more RAM. Figure out what your queries are doing, and help them make better life choices.

Brent Ozar has just open-sourced his course on how the database engine works, and I reckon you would benefit from that, as well as many other free online resources.


How to identify the origin of a invalid connection with sql1060 I am getting a message similar to the next one:

2007-08-08-09.28.41.745208-240 I10292373A694 LEVEL: Error
PID : 1720384 TID : 1 PROC : db2agent (DPADB) 0
INSTANCE: XXXXX NODE : 000 DB : DPADB
APPHDL : 0-154 APPID: GA381C80.O107.00D748132841
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1060 sqlerrml: 8
sqlerrmc: YYYYY
sqlerrp : sqlrrait
sqlerrd : (1) 0x8012006D (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:

I want to know from which machine the connection is being tried.
Do I have to use a sniffer, or does DB2 have a tool to identify this point?

The APPID in the diagnostic log entry contains the IP address and port, albeit encoded in a weird manner (see note 11). The APPID is formatted address.port.timestamp. When the connection is local the "address" portion will be something like "*LOCAL" but obviously in this case it's remote.

The remote address and port are encoded in hexidecimal but the first character (of each) is munged so that "0" maps to "G", "1" to "H", etc. (there's probably some internal requirement for APPIDs to appear non-numeric). So in this case the remote address is:

GA381C80     -- original
GA 38 1C 80  -- split it up into the octets
0A 38 1C 80  -- de-munge the first char
10.56.28.128 -- convert to decimal

Same story for the port (O107 = 0x8107 = 33031). How the timestamp is decoded I can't recall (the page linked above claims it's the "unique identifier" portion but I could swear I remember it being a timestamp, or timestamp-based).


maximum allocate RAM for MS SQL Server I have MS SQL Server with different size of RAM.
So I would like to control the values which are set for the RAM.
Somewhere I read a 'rule' to calculate the maximum of RAM.

These SQL Server are only for DB's. At the server itself is nothing else running.
Only two remote session are possible.

How many RAM does the OS and the two remote session require?

Calculation is something which you need to analyse an figure out with some calculation on basis of that. Kindly go through the below discussion which will help you out in deciding what values you can assign:

What is a deterministic method for evaluating a sensible buffer pool size?

Also, would recommend to go through below article for indepth explanation:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/07/understanding-the-vas-reservation-aka-memtoleave-in-sql-server.aspx


MongoDB data directory space is increasing I have a worry, mongoDB data directory space is increasing very rapidly:

I have a two data files and each one has reached 2GB limit and starts spawning 2 GB file multiple files. Is there any where I can compress the data?

The short answer: No

No, you can't do so easily.

The long answer: As always, it depends

First: The reason for those data files to be created is simple: The mongod preallocates a new datafile as soon as the first document is written in the previously preallocated datafile in order to prevent unnecessary latency if when the space of a new datafile is needed. So you will always have at least 2GB more space allocated then you need as soon as the file size of the data files reaches 2GB. As space is cheap, this is a reasonable procedure.

What can happen is that the data in the data files are fragmented. When a document is deleted, it is marked as such in the data files and the space it occupied is used again. So a new document may be written, either fitting into the formerly occupied space or not. When it doesn't fit into that space, a matching space is searched. If there isn't any, the new document is written to the preallocated data file (as documents are never fragmented) and the preallocation of a new data file is triggered. So this is why it can happen that a new data file is preallocated despite of the fact that there is mathematically enough space in the data files for the new document. There are a few possibilities to reclaim disk space, but all of them require some time and effort.

Option 1: using mongodump

You can create a dump of your data using mongodump, drop the database, delete the data files if necessary and use mongorestore to restore your database. Make sure you use the --oplog parameter, if applicable. So what happens during the restore is that all documents are written in contiguous order, and without datafile fragmentation, which may result in a smaller number of data files! effectively reclaiming some disk space. However, there is no guarantee that disk space will be freed, obviously.

There are a few drawbacks using this option:

you need the according disk space to create a full dump
without the --oplog option, you will have a point in time snapshot of the data, resulting in data written to the database from the start of the dump until it's completion will be lost

Indices aren't dumped, but rebuilt on restore. Hence, a restore may take a Very Long Time.

Option 2: "Abusing" the initial sync mechanism of replica sets

You may use MongoDB's mechanism for initially synching a new member of a replica set to achieve the same result as described above, without the risks. On the other hand, arbitrarily removing a member from the replica set reduces your redundancy. The procedure is documented in the MongoDB docs.

Option 3: (Ab)use repairDatabase

This option may be used to compact the database and reclaim disk space! but it has a lot of caveats as described in the documentation of the repairDatabas command.  Don't use it to reclaim disk space, unless you are absolutely positively sure that your database and it's collections are in a working state and every other option can't be taken. If you do, do it at your own risk. You have been warned.


SQL Server Changing logical name I have some db's created with an incorrect logical name for both rows and log. how can I change the logical names to match the db name with a script? (and does not want to change physical file names)

Also how can I find all the databases with same logical name?

(SQL Server 2012)

thanks

Just use ALTER DATABASE:

ALTER DATABASE mydb
MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )


MySQL keeps getting “connect” commands 100X per minute! My server was having performance issues, things loading slow, pages not loading, etc so I ran the "top" command after logging in via SSH. I noticed the CPU kept jolting up to 40%, on and off, when literally no one is accessing the website. I have other (much less powerful) servers with resting heartrates at 1-5% so I know something is wrong. I log into MySQL, enable general logging and this is what I find:

    event_time Descending 1     user_host   thread_id   server_id   command_type    argument
    2014-09-20 04:35:07     [root] @ localhost []   46833   0   Connect     root@localhost on
    2014-09-20 04:35:07     [root] @ localhost []   46834   0   Connect     root@localhost on
    2014-09-20 04:35:04     [root] @ localhost []   46832   0   Connect     root@localhost on
    2014-09-20 04:35:03     [root] @ localhost []   46829   0   Connect     root@localhost on
    2014-09-20 04:35:03     [root] @ localhost []   46830   0   Connect     root@localhost on
    2014-09-20 04:35:03     [root] @ localhost []   46831   0   Connect     root@localhost on
    2014-09-20 04:34:58     [root] @ localhost []   46828   0   Connect     root@localhost on
    2014-09-20 04:34:57     [root] @ localhost []   46827   0   Connect     root@localhost on
    2014-09-20 04:34:48     [root] @ localhost []   46826   0   Connect     root@localhost on
    2014-09-20 04:34:41     [root] @ localhost []   46823   0   Connect     root@localhost on
    2014-09-20 04:34:41     [root] @ localhost []   46824   0   Connect     root@localhost on
    2014-09-20 04:34:41     [root] @ localhost []   46825   0   Connect     root@localhost on
    2014-09-20 04:34:39     [root] @ localhost []   46822   0   Connect     root@localhost on
    2014-09-20 04:34:37     [root] @ localhost []   46821   0   Connect     root@localhost on
    2014-09-20 04:34:36     [root] @ localhost []   46820   0   Connect     root@localhost on
    2014-09-20 04:34:33     [root] @ localhost []   46819   0   Connect     root@localhost on
    2014-09-20 04:34:30     [root] @ localhost []   46818   0   Connect     root@localhost on
    2014-09-20 04:34:29     [root] @ localhost []   46815   0   Connect     root@localhost on
    2014-09-20 04:34:29     [root] @ localhost []   46816   0   Connect     root@localhost on

There is literally something like 100 "connect" commands mySQL is processing every minute. I'm sure this is what is causing my performance issues. But my problem is that I don't know where these commands are coming from or how I might go about tracing the source. Any ideas?

I would check / verify if there is a init_connect option in your my.cnf, if so that would be a great place to start to see what the var is set to.

Also:

While the server is running, in terminal, run:

ps -aef | grep mysql

and identify the user(s) that are running processes up against mysql. If you end up seeing a sort of trend in the processes than grep out the parent processes for each child process to get to the route PID that is engaging.

Also:

If this MySQL instance is not in production, I would suggest a server restart as well. We all know MySQL can be finicky and in many cases a clean restart clears up buggy issues.

Side note: Is there a proxy in front of your MySQL server? If so, I would look into this layer just as intensive as the database.

Gluck! Cheers.


Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data? Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data?

I need to nullify the data in all encrypted columns in a development server (according to our business rules). I know most of the columns because we use them regularly, but I want to be thorough and also I want to be able to prove that I've found them all.

I've searched the web, looked in INFORMATION_SCHEMA and checked the DMVs I thought would be useful and also sys.columns and sys.objects -- but so far no luck.

Assuming you are talking about data that is encrypted with SQL Server keys, there is way to find these columns.

The Key_name() Function will return the name of the key used for the encryption for that particular value and will return NULL if there isn't anything encrypted with a "known" key (3rd party, or simple not encrypted).

With that knowlegde we can test every column to see if it contains at least one row which has a varbinary value that returns a key name

functionality of key_name()

--create a test database
CREATE DATABASE [Test_ENCR]
GO

--change context
USE [Test_ENCR]
GO

--because it's possible to encrypt different rows with different keys I'll create 2 keys for this demo
-- Create a symmetric key
CREATE SYMMETRIC KEY symmetricKey1
   WITH ALGORITHM = AES_128
   ENCRYPTION BY PASSWORD = 'password01!';
GO

-- Create a second key
CREATE SYMMETRIC KEY symmetricKey2
   WITH ALGORITHM = AES_128
   ENCRYPTION BY PASSWORD = 'password02!';
GO

--create a table that will have a column holding:
--1: encrypted row with key1
--2: encrypted row with key2
--3: a non encrypted just varbinary value

CREATE TABLE encryptedTable
(ID int IDENTITY PRIMARY KEY,
EncryptedCol varbinary(256) NOT NULL);
GO

-- open key1
OPEN SYMMETRIC KEY symmetricKey1
    DECRYPTION BY PASSWORD = 'password01!';
GO

-- open key2
OPEN SYMMETRIC KEY symmetricKey2
    DECRYPTION BY PASSWORD = 'password02!';
GO

--insert encrypted data with key1
INSERT INTO encryptedTable(encryptedCol)
VALUES ( ENCRYPTBYKEY (Key_GUID('symmetricKey1'), 'EncryptedText1'));
GO

--insert encrypted data with key2
INSERT INTO encryptedTable(encryptedCol)
VALUES ( ENCRYPTBYKEY (Key_GUID('symmetricKey2'), 'EncryptedText2'));
GO

--insert just varbinary data
INSERT INTO encryptedTable(encryptedCol)
VALUES (CONVERT(varbinary(256),'NotEncryptedTextJustVarBinary'))

--have a look, without the key, all varbinary for you.
SELECT * FROM encryptedTable
GO

results:

--Return all key_names
SELECT DISTINCT     key_name(encryptedcol),
                    EncryptedCol
FROM encryptedTable;

results:

How to implement it to find encrypted columns

--How do we dynamically find all the columns that have at least one row with a encrypted value?

-- first we will find all tables and column with a varbinary datatype
-- then we will test all those columns with a simple select
-- If the key_name() function returns a value, the column and table name are stored together with the keyname

--create a table to hold all varbinary columns and tables
CREATE TABLE #TablesWithVarbinCols (    ID int IDENTITY,
                                TableName nvarchar(128),
                                ColumnName nvarchar(128)
                                );

--create a table to hold the end result
CREATE TABLE #TablesWithEncryption (
                                TableName nvarchar(128),
                                ColumnName nvarchar(128),
                                KeyName varchar(128)
                                );

--find and store all table and column names of user tables containing a varbinary column
INSERT INTO #TablesWithVarbinCols (TableName,ColumnName)
SELECT      o.[name] as TableName,
            c.[name] as ColumnName
FROM        sys.objects o
INNER JOIN  sys.columns c
ON          o.[object_id]=c.[object_id]
INNER JOIN  sys.types t
ON          c.system_type_id=t.system_type_id
WHERE       o.[type]='U'
AND         t.name=N'varbinary'
AND         c.max_length > -1;

DECLARE @col nvarchar(256)
DECLARE @tab nvarchar(256)
DECLARE @c int = 1
DECLARE @MaxC int
DECLARE @SQL varchar(max)

SELECT @MaxC=MAX(ID)
FROM #TablesWithVarbinCols

--loop the previous result and create a simple select statement with a key_name() is not null where clause.
--If you have a result, store the details
WHILE @c <= @MaxC
BEGIN
    SELECT  @Tab=TableName,
         @col=ColumnName
    FROM    #TablesWithVarbinCols
    WHERE   ID=@c

    SET @SQL='  INSERT INTO #TablesWithEncryption (TableName, ColumnName, KeyName)
                SELECT DISTINCT '''+@Tab +''',''' +@col +''', key_name('+@Col +') from '+ @tab +'
                WHERE key_name('+@Col +') is not null;'
    exec (@SQL)

    DELETE
    FROM #TablesWithVarbinCols
    WHERE id=@c;
    SET @c=@c+1
END

--select the result
SELECT * FROM #TablesWithEncryption;

results:

--cleanup
DROP TABLE #TablesWithVarbinCols;
DROP TABLE #TablesWithEncryption;


Warning about memory "Excessive Grant" in the query plan - how to find out what is causing it? I am running a query that is giving the warning about a memory Excessive Grant.

There are too many tables and indexes used, including a complex view, and therefore it is difficult to add all of the definitions here.

I am trying to find what might me causing the Excessive Grant. Can it be conversions?

Looking at the execution plan I can see the following:

<ScalarOperator
  ScalarString="CONVERT(date,[apia_repl_sub].[dbo].[repl_Aupair].[ArrivalDate] as [repl].[ArrivalDate],0)">
  <Convert DataType="date" Style="0" Implicit="false">
    <ScalarOperator>
      <Identifier>
        <ColumnReference Database="[apia_repl_sub]" Schema="[dbo]" Table="[repl_Aupair]" Alias="[repl]" Column="ArrivalDate" />
      </Identifier>
    </ScalarOperator>
  </Convert>
</ScalarOperator>

And this one:

<ScalarOperator ScalarString="CONVERT(date,[JUNOCORE].[dbo].[applicationPlacementInfo].[arrivalDate] as [pi].[arrivalDate],0)">
  <Convert DataType="date" Style="0" Implicit="false">
    <ScalarOperator>
      <Identifier>
        <ColumnReference Database="[JUNOCORE]" Schema="[dbo]" Table="[applicationPlacementInfo]" Alias="[pi]" Column="arrivalDate" />
      </Identifier>
    </ScalarOperator>
  </Convert>
</ScalarOperator>

Here is the query, although you can see the query with the execution plan here too:

DECLARE @arrivalDate DATEtime = '2018-08-20'

SELECT      app.applicantID,
            app.applicationID,
            a.preferredName,
            u.firstname,
            u.lastname,
            u.loginId                       AS emailAddress,
            s.status                        AS statusDescription,
            CAST(repl.arrivalDate AS DATE)  AS arrivalDate,
            app.moodleCourseComplete,
            app.moodleCourseCompleteUpdated,
            u.loginId,
            c.countryName
FROM        app.application                  AS app
JOIN        app.applicant                    AS a    ON a.applicantId = app.applicantId
JOIN        usr.[user]                       AS u    ON u.userId = a.userId
JOIN        app.ref_applicationStatus        AS s    ON s.statusCode = app.status
JOIN        APIA_Repl_Sub.dbo.repl_Aupair    AS repl ON repl.JunoCore_applicationID = app.applicationID

JOIN        app.Country                      AS c    ON c.countryCode = a.nationalityCode
WHERE       repl.arrivalDate = @arrivalDate

UNION ALL

(
    SELECT      app.applicantID,
                app.applicationID,
                app.preferredName,
                app.firstname,
                app.lastname,
                app.emailAddress,
                ap.status,
                CAST(app.arrivalDate AS DATE)    AS arrivalDate,
                app.moodleCourseComplete,
                app.moodleCourseCompleteUpdated,
                app.emailAddress                 AS loginId,
                c.countryName
    FROM        JUNOCore.dbo.vw_SelectApplication    AS app
                INNER JOIN JUNOCore.dbo.country c ON c.countryCode = app.nationalityCode
                INNER JOIN JUNOCore.dbo.application as ap ON ap.applicationID = app.applicationID
    WHERE        arrivalDate    = @arrivalDate AND
                app.applicationID NOT IN (SELECT p4.applicationId FROM APCore.app.application p4)
)

This is how the warning looks like:

The execution plan is here.

How do I tackle this warning?

As I said before I was looking at conversions. Is there anything I can look for in the execution plan that would indicate the possible causes of this excessive grant?

Obs. I said there are too many objects involved, however, I can add here whatever is required upon request if it might help to tackle this issue. No problem.

The most common two types of operators that consume memory are:

Sorts
Hashes

If a plan is parallel, memory requirements will go up some amount to compensate for exchanges for threads to pass rows through. Parallel plans do not require the entire serial memory grant * DOP (though there may be a relationship between serial required memory and DOP). The full grant is split and (hopefully) used evenly across all threads in the plan.
In some cases, Nested Loops Join may ask for memory as well.
Memory grants are calculated by the optimizer based on the number of rows, and the size of the data that will pass through memory consuming operators. As above, if the plan is parallel, it may ask for more. Another factor is that memory consuming operators can share memory.
For instance, some memory consumed by a sort can be passed to upstream operators after data is sorted, and hashes may pass memory upstream after the initial build phase is complete, and probed rows start moving upstream. This can be observed via the Memory Fraction information.
In your plan, you have three Sort operators.

Which, combined, the optimizer thinks it'll need 2 MB of memory to run without spilling to disk. It ends up only needing 24 KB, hence the warning.
Memory grant mis-estimates can come from many places. In your query, you have a single variable: @arrivalDate.
It's not clear if this parameter is within a stored procedure, or if you're calling it locally. In either case, you might try a recompile hint to see if that removes the warning by getting a different cardinality estimate.
If that doesn't work, you may want to try adjusting indexes so that separate sort operations aren't necessary, but that may be more complicated than is worthwhile for such a small amount of memory.
For reference:

Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)

Troubleshooting Parameter Sniffing Issues the Right Way: Part 1 (links to other parts are provided in the first post)

Yet Another Post About Local Variables


Using different servers for front-end and back-end? I recently noticed that the load time of my website has slowed significantly.  I'm not a developer, but my programmer mentioned this is due to the large amount of data that is being searched through when a user comes to the homepage (due to recording tracking information).

Would using separate servers for the front-end code and the back-end database help resolve this problem?  Any recommendations I can talk to my developer about would be helpful.

Thank you,

David

Depending on your server config it might.  It is usually recommended that you don't have anything else running on the SQL Server other than SQL Server so that you can dedicate all the memory to the SQL Server instance.  You may also need to do some index tuning to help solve the performance problem.


Upgrade Postgres without doing a pg_dumpall I'm upgrading from postgres 8.4 to 9.0 and looking at the manual it looks like this is my only choice:

pg_dumpall -p 5432 | psql -d postgres -p 6543

Which means I'll need to stop any writes to the database and wait whilst a couple of gb's of data is transferred.

Is there a better way in which I can use the Write Ahead Log (WAL) to replay any new SQL onto the new database?

Since 8.4, pg_upgrade is the alternate choice to dump-restore for upgrades between major versions. It is actually linked at the end of the doc page on migration you already mentioned.

As for replaying WAL files from an older version, the doc says we can't:

  In general, log shipping between servers running different major
  PostgreSQL release levels is not possible

(in Log-Shipping Standby Servers).


Inserting many documents in collection, but only unique elements, based on specific field I cannot seem to find an answer on this anywhere. I need the following:

Given an array of objects with the structure:

    {
       link: 'some-link',
       rating: 25,
       otherFields: '..',
       ...
    }

I want to insert them into my collection. So I would just do insertMany... But I only want to insert those elements of the array that are unique, meaning that I do not want to insert objects with the field "link" being something that is already in my collection.

Meaning if my collection has the following documents:

    {
      _id: 'aldnsajsndasd',
      link: 'bob',
      rating: 34,
    }
    {
       _id: 'annn',
       link: 'ann',
       rating: 45
    }

And I do the "update/insert" with the following array:

    [{
      link: 'joe',
      rating: 10
    },{
      link: 'ann',
      rating: 45
    }, {
      link: 'bob',
      rating: 34
    }, {
      link: 'frank',
      rating: 100
    }]

Only documents:

    {
      link: 'frank',
      rating: 100
    }
    {
      link: 'joe',
      rating: 10
    }

Would be inserted in my collection

First add an unique constraint on field link.

db.collection.createIndex({"link": 1}, {unique: true})

And then without using insertMany, insert each document of the list using javscript functions.

Lastly remove the index(unique constraint) we no longer need.

db.collection.dropIndex("link_1") // or your index name


Which TCP port to choose for SQL Server to listen to for security reasons? I know that the default TCP port for MS SQL Server is 1433 and it is better to change that for some security reasons. But I want to know which port is better to choose instead of 1433 for SQL Server in order to work properly and not to intervene with the work of other programs? What if I choose a port which is already used by another program?

Thanks for your time!

This question may be better suited to https://security.stackexchange.com/ as the idea and implications are not at all specific to SQL Server, they apply to any other internal service.

Changing the SQL Server instance port generally adds very little to security - it is a "theatre" action rather than a real security benefit, something to put on a checklist to make it look like you are doing lots for a security-in-depth policy[1]. If the browser service is still open anyone getting in can ask that what port SQL is listening on. Multiple instances of SQL on different ports is not uncommon so automated attacks against SQL Server will generally scan for instances on other ports if one is not found on 1433, while an attack could take some tens of seconds longer an automated attack bot has all the time in the world to wait.

If the outside world can see port 1433 then that is the problem (your firewall/other setup is too open) and changing ports probably won't fix that because the new port will likely be open to the world too. If an attacker is inside you infrastructure so can see the SQL Server listening port for that reason then you have a significant security problem elsewhere that moving SQL's port also won't help with.

[1] NOTE: I don't mean to imply that security in depth is a bad thing, it very much isn't. Security in depth should be a core goal of all environments. But try to avoid security theatre which adds hassle but not actually any real security benefit when you could be spending your time doing something else that actually does improve security.


Cassandra one-to-many table design I am new to Cassandra from 20 years of RDBMS, and over the last few days have been reading and watching everything I can get my hands on that applies to my situation.  I'm sure this is a basic question, but for some reason it's not clicking so please excuse me if this is answered elsewhere.

I am trying to design a table to store a list of associations between users.  Any user may have any number of associated users.  It's sort of like a friends list.

Right now I have 3 fields:

id (timeuuid) (PK)
user (uuid)
friend (uuid)

I want to be able to do:

SELECT * FROM friends WHERE user = ?

So because user is not a PK, it cannot be used in a query.  And, if I make it part of the PK, it has to be unique, meaning users can only have 1 friend max.

I sort of solved this by using a secondary index on user, but I imagine that approach is not the best idea.  The query is rather slow to respond, obviously because it has to ask all my cluster nodes for their part of the data.

So: what is the proper way to design this table?  Thank you very much in advance for any guidance.

Schema design in Cassandra, for efficient tables, will grate against your RDBMS experience; for efficiency, the Cassandra prefers denormalization, not normalization.  By this, I mean that if you have some user information and you want to look up that data using two different primary keys, then using Cassandra, it actually is better to use two tables (and duplicate the data).  Yes, this means more storage space, but it also allows for faster reads.

As a side note, based on my own experiences, I would recommend against using a secondary index, and instead simply use another table.  Secondary indexes in Cassandra are treated a little differently, with background threads which update the indexes periodically; this makes reading from an index not quite as reliable (i.e. more likely to surprise you, in a not good way) than just using a table.

Thus I would recommend something like the following two tables for your needs:

CREATE TABLE users (
  id TIMEUUID PRIMARY KEY,
  user UUID,
  friend UUID
);

CREATE TABLE friends (
  id TIMEUUID,
  user UUID,
  friend UUID,
  PRIMARY KEY (user, friend)
);

This second table would let you do your CQL query:

SELECT * FROM friends WHERE user = ?

Notice that this friends table uses a compound primary key.  This allows there to be multiple friend values associated with that single user value.

One of the downsides of this multiple-table approach is that your application code now has to be responsible for writing into both tables for a single "update", and you have to deal with any potential skew/reconciliation.  Cassandra achieves its performance, in many ways, by avoiding enforcing of foreign key constraints and such and leaving that to the application.

Hope this helps!


The same server logs multiple updates into the VPX_HOST table (which looks like VMware's Virtual Center, I'm guessing) so this gets a little tricky.

This is technically what you're asking for:

SELECT DISTINCT DNS_NAME
FROM dbo.VPX_HOST no_updates
WHERE NOT EXISTS (SELECT * FROM dbo.VPX_HOST updates WHERE no_updates.DNS_NAME = updates.DNS_NAME AND updates.LAST_PERF_TIME > DATEADD(mm, -30, GETDATE()))
ORDER BY DNS_NAME

But you won't actually like it because if you ever have a DNS_NAME drop offline permanently, like if you throw away a host, it'll keep showing up in here. So let's modify the query so that it only includes hosts that added an entry in the last day, but not in the last 30 minutes:

SELECT DISTINCT DNS_NAME
FROM dbo.VPX_HOST no_updates
WHERE no_updates.LAST_PERF_TIME > DATEADD(dd, -1, GETDATE())
AND NOT EXISTS (SELECT * FROM dbo.VPX_HOST updates WHERE no_updates.DNS_NAME = updates.DNS_NAME AND updates.LAST_PERF_TIME > DATEADD(mm, -30, GETDATE()))
ORDER BY DNS_NAME

That first WHERE clause on the 3rd line says only give me servers who have updated in the last day. If you wanted to go farther back, you could change the dd, -1 to something like dd, -3 to go 3 days back.

Edit about performance - be aware that this is going to scan the VPX_HOST table, so the bigger that table gets, the slower this query will get. If you're going to run it frequently, you'd probably want an index on (DNS_NAME, LAST_PERF_TIME).


Ideal values for wait time in SQL server This is related to my own post here

The SQL server starts everyday at 8:30 AM and stops around 9:30 PM.( So wait times will reset everyday)
SQL server will be used by around 60 users simultaneously(used directly to modify data and also back-end database for application which we use in our office)
The server has 8 GB ram. Every hour log backup will be done for most of the databases.(Backup done by our own backup tool which automates SQL backup)

As described in this article I used below query.

select *
from sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
order by wait_time_ms desc;

And the result was as shown below(Only top 25 rows are shown)

Now after getting this result i am confused whether these values are really high or it is normal scenario.( I have also taken performance counter values related to SQL,but not able to troubleshoot using that, as most articles only describe which performance counters to be used,not about analysing it properly or it is hard part for me to find performance of my server)So what is the ideal value for these? Whether I have performance problem in my sql server? How to decide it?

What you should think about is not the just absolute values of wait_time_ms or waiting_tasks_count you should also look at the average wait times. When you look at an average wait, you should ask yourself: "Is this a reasonable amount of time to wait on this resource?"

For example (if I am doing the math right) your PAGEIOLATCH wait is in the range of 4-5ms. This is "good" - because that is the response time of a hard drive to fetch an I/O request. However, if you are running on SSD (where this value is expected to linger in the 1ms range), it is "bad".

As another example, your WRITELOG wait is over 10ms. This is "bad" because if you do sequential I/O right, this value (even on spinning rust) should really be in the 1ms range.

However, all of this has to be seen in the context of what you are trying to achieve. If you are looking to "generally make things faster" (not an uncommon request) you need to look at the biggest waits (by wait_time_ms) first as they are most likely to hurt you. If your tuning is more targeted, look at specific wait types, even if they are lower on the list.

For example, you have a few LCK_M_S waits that average 70ms. They don't look important because there are so few of them. But maybe they affect some important users. If this is an OLTP system where queries should get in and out fast - 70ms is a "bad" number (because locks in short running transactions should be held only a few milli- or even microseconds).

Another example: If you are trying to make DML queries faster, you will want to target the WRITELOG and PAGEIOLATCH_EX waits. If you are optimising for reads, you would focus more on either reducing the PAGEIOLATCH_SH wait (for example with SSD or RAM) or making the database do less read I/O (for example by optimising indexing).


Can postgresql WAL be regenerated or resent to the standby server? Does anyone know if there's a way to resend or regenerate a WAL file that was missed by the standby server? During my testing I've gotten into a situation where the main server fails to send a file or 2 due to some issue on the standby server. Once I correct the problem then the main server is able to send files just fine except it does not attempt to resend the files it missed and so the standby server gets "stuck" waiting for file x. In the mean time WAL files are stacking up in my wal archive directory on the standby server, they don't get processed because postgresql is waiting for a specific file.

So what I'd like to know is if there's a way to resend and/or regenerate this file on the primary server. If so, how? Or is that data lost? and if so, how do I tell the standby server to stop waiting for that file and move on to the next?

I'm using omnipitr-archive and omnipitr-restore commands for my archiving and restoring.

except it does not attempt to resend the files it missed and so the standby server gets "stuck" waiting for file x

Your archive_command is buggy.

A correct archive command will never return zero (success) until the WAL of interest is safely archived. If the command returns non-zero (failure) then PostgreSQL will retry archival that WAL segment until it succeeds and will not discard the WAL segment until it's archived.

Either your archive_command is returning true before the archive is truly safely stored, or there's something wrong with the restore_command that's causing it to lose/remove WAL segments.

What you describe should not happen in normal, correct operation.

Now, if somehow you do land up with a gap of WAL segments between the standby's current position and the oldest available WAL segment, the only thing you can do is re-initialise the standby. The simplest and safest way to do this is with pg_basebackup -X stream. If you want to get more sophisticated and save some time/bandwidth you can instead use rsync to copy just changed parts of files:

Stop the standby
pg_start_backup() on the master
rsync -a --delete the master data dir to the standby data dir
pg_stop_backup() on the master
wait for the final WAL segments to archive
start the standby


Extract only single value from multiple rows based on certain condition for same id in Oracle I'm an Oracle beginner and I'm having troubleshooting the issue below.

My table:

+-------+------+-------+------ +
| ID    | GRP  | ITDESC| DEN   |
+-------+------+-------+------ +
| 12345 | MANX | Apple | SendV |
+-------+------+-------+------ +
| 12345 | MANX | Apple | Manual|
+-------+------+-------+-------
| 12345 | MANX | Apple | UnVeri|
+-------+------+-------+------ +
| 12346 | MANX | Mango | UnVeri|
+-------+------+-------+------ +
| 12347 | MANX | PineAp| SendV|
+-------+------+-------+------ +
 12348  | MANX | Pine | Manual|

I am expecting:

+-------+------+-------+------ +
| ID    | GRP  | ITDESC| DEN   |
+-------+------+-------+------ +
| 12345 | MANX | Apple | SendV |
+-------+------+-------+------ +
| 12346 | MANX | Mango | UnVeri|
+-------+------+-------+------ +
| 12347 | MANX | PineAp| SendV|
+-------+------+-------+------ +
| 12348  | MANX | Pine | Manual|
+-------+------+-------+------ +

I have multiple rows that have only the DEN column different for the same id. My aim is for the same id perform the check.  If the value of DEN is 'Manual' then check to see if there is 'SendV' in DEN column for that id.  If it is present then consider 'SendV' otherwise consider 'Manual'.
Note in the provided example, the order is random.  The SendV can be in 2nd row or 3rd row or 4th row based on the action, however the text of DEN will be same as said above.

select * from table t1 where DEN IN ('Manual', 'SendV').

I am not aware how to write the condition?

So what happens then there is no row with Manual, but there is with SendV? Should it be SendV as in your example output?

with data as
(
  select 12345 as id, 'MANX' as grp, 'Apple' as itdesc, 'SendV' as den from dual union all
  select 12345 as id, 'MANX' as grp, 'Apple' as itdesc, 'Manual' as den from dual union all
  select 12345 as id, 'MANX' as grp, 'Apple' as itdesc, 'UnVeri' as den from dual union all
  select 12346 as id, 'MANX' as grp, 'Mango' as itdesc, 'UnVeri' as den from dual union all
  select 12347 as id, 'MANX' as grp, 'PineAp' as itdesc, 'SendV' as den from dual union all
  select 12348 as id, 'MANX' as grp, 'Pine' as itdesc, 'Manual' as den from dual
)
select id, grp, itdesc, den from
(
  select
    id, grp, itdesc, den,
    row_number() over (partition by id, grp, itdesc order by
      case grp when 'SendV' then 1 when 'Manual' then 2 else 3 end) as rn
  from data
)
where rn = 1
order by id;

        ID GRP  ITDESC DEN
---------- ---- ------ ------
     12345 MANX Apple  SendV
     12346 MANX Mango  UnVeri
     12347 MANX PineAp SendV
     12348 MANX Pine   Manual


Constraint to prevent 'duplicates' only when column > 0 I have a table with a fk column that should be unique when another int column is > 0. If that sentence doesn't make sense, here is an example of valid rows:

CREATE TABLE foo
AS
  SELECT fk,int
  FROM ( VALUES
    ( 1, -12 ),
    ( 1,  20 ),
    ( 1,   0 )
  ) AS (fk, int);

Adding a new row to the table with a fk value of 1 and a int column that is > 0 would be invalid.

Is there a way to do this with a constraint or index? I feel like an exclude constraint nearly gets me there, but I couldn't figure out a way to get it to work.

I'm using Postgres 9.6.2.

Add a conditional unique index

CREATE UNIQUE INDEX IndexName ON foo (fk) WHERE (int>0);

dbfiddle here


MongoDB allow access only from local server I am utilizing MongoDB as a database platform into my application.
As a security measure I need to allow my database to be accessible only from my server.
Looking forward for appropriate solution.

You have to enable MongoDB access control. This uses a bindIp value in the mongod.conf file that by default listens to connections locally (e.g. bindIp:127.0.0.1 or with another local address, bindIP:127.0.0.1,10.0.0.10).

For that, you will need to create an admin or root user by doing this:

use admin
db.createUser(
  {
    user: "root",
    pwd: "abc123",
    roles: [ { role: "root", db: "admin" } ]
  }
)

Something similar you will need to do to create a user for your application:

use appDatabase
db.createUser(
  {
    user: "appUser",
    pwd: "appUserPassword",
    roles: [ { role: "readWrite", db: "appDatabase" } ]
  }
)

Then, in your mongod.conf file (in a CentOS server is located at /etc/mongod.conf), do the following:

Uncomment 'security'.
Add below it 'authorization: enable' (keep authentication commented out).
Make sure the bindIp value is set to 127.0.0.1

Then, when logged onto your server, if you need to connect to your MongoDB instance, do that with the admin/root user you just created:

mongo -u root -p --authenticationDatabase admin

You should also make sure you can successfully log onto mongo with your appUser:

mongo -u appUser -p --authenticationDatabase appDatabase

From your application, you will need to connect with an URL similar to this one:

mongodb://appUser:appUserPassword@localhost:27017/appDatabase?authSource=appDatabase

Reference:
MongoDB - Enable Authentication |
MongoDB - Configuration File Options


How to find the rows from a table which has exactly two 'n' s in the employee name? I want to find all the employees whose names have exactly two n's in their name. How can this be done?

something like this should be enough:

select * from employee_table where Length(lastname) - length(replace(lastname, 'n','')) = 2

be aware that may cause a full table scan :-)


Performance issues from RDS Aurora 5.6 to Aurora 5.7 Recently upgraded one of my RDS instances to aurora mysql 5.7 from 5.6 and I've had to triple the instance class while I try to resolve this (cpu eventually pegs and never declines).
Most reads are slow now (some about 30% slower, others are up to 400% slower).  Looking at explain output, the queries are still using proper indexes. innodb_buffer_pool_size us set to default RDS value (instance class*3/24 I think) and most other innodb_* settings are not modifiable.
Here's an example Mysql5.6:
| count(*) |
+----------+
| 20646739 |
+----------+
1 row in set (0.06 sec)

Mysql5.7:
mysql> select count(*) from nope;                                                                                +----------+
| count(*) |
+----------+
| 20646739 |
+----------+
1 row in set (3.77 sec)

Anyone else encounter this that can provide some insight into some setting I can tweak?
Both instances are db.r3.2xlarge.

Rate Per Second = RPS
Suggestions to consider for your 5.7.12 Aurora Parameters group
innodb_write_io_threads=16  # from 4 to enable higher IOPS to your SSD device
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function
innodb_max_dirty_pages_pct_lwm=.0001  # from 0 to enable pre-flushing
innodb_max_dirty_pages_pct=.0001  # from 75 % dirty tolerated to reduce innodb_buffer_pool_pages_dirty count of 354,764 - will take hours
net_buffer_length=98304  # from 16384 to reduce count of interruptions for send/receive pkts
innodb_fast_shutdown=0  # from 1 to avoid recovery cycle on restart

This is just the beginning of your journey to improve performance.
Thank you for the opportunity to serve your team.
Not all these Global Variables are dynamic.  Will require instance stop/start.