Which join to use ? and how to add rank column on the result I have two tables

Table4 - Columns (ID, NAME, Money)
Table5 - COlumns(ID, PLACE)

Note: Table 4 has more IDs than table5

I am trying to use a join such that I get the following table

Table6 - Columns(Rank,ID,Place,Name,Money)

Also I want it to be such that when I get this result the ID values that are contained in Table4 whether or not they are contained in Table5 tp retain their ID and Name and Money Values.

In addition I would also like to have a rank column in the new table as the first column where the rank shown is 1 if your Money is the highest in the ID, and 2 if it is second highest etc.

So far I tried using the following join

Select *
From Table4
  left outer join Table5  on Table4.ID = Table5.ID

but the problem here is that the LID values unique to table4 drop their corresponding values for name and money.

Also how can I use the rank function here to get the desired result I tried using this but to no avail

select *
from (
   SELECT  RANK() OVER (PARTITION BY ID ORDER BY Money DESC) as RN,ID,Place,Name,Money
   FROM Table4
)

With out some sample code to go on I created my own set

I had to join on both ID and Name for the rank in order to prevent cross joins

create table #Table4 (ID int, Names nvarchar(25), Moneys Money)
create table #Table5 (ID int, Place nvarchar(25))

insert into #Table4(ID,Names,Moneys)values(1,'waffles',1000),
   (2,'Pancakes',2000),(3,'Blueberries',3000),(3,'Eggs',4000),(3,'Toast',5000)
insert into #table5(ID,Place)values(1,'House'),(2,'Kitchen')

select x.Rn, t4.ID,t5.Place,t4.Names,t4.Moneys
from #Table4 t4
left join #table5 t5
    on t4.ID = t5.ID
inner join (select t4a.ID, t4a.Names, rank() over(partition by t4a.ID order
     by t4a.Moneys desc) as Rn from #Table4 t4a) x
    on t4.ID = x.ID
    and t4.Names = x.Names
order by ID,RN

Results will look something like this

Rn  ID  Place   Names       Moneys
1   1   House   waffles     1000.00
1   2   Kitchen Pancakes    2000.00
1   3   NULL    Toast       5000.00
2   3   NULL    Eggs        4000.00
3   3   NULL    Blueberries 3000.00


postgres; do indexes automatically reindex new data? If I have a bunch of rows in a Postgres table, then create an index, does the index get automatically updated if new data rows are inserted after it's creation?

Alternatively stated, is REINDEX needed only in some rarer scenarios, or whenever new data is added?

New data gets indexed automatically. Do note however that the presence of an index will therefore slow down inserts and updates (and deletes too of course). For tables with an extremely high volume of transactions, you have to be very careful about adding indexes. For most tables in most systems this is not an issue.

reindex is rarely needed under normal circumstances. Of these rare cases, the most common is probably "bloat" caused by lots of mutations -- usually when those mutations cause data on the disk to be removed from the "middle" and added to the "end".


Capture all queries that use hints How can I capture or list all queries that use hints and have executed since the last instance restart?

I am using SQL Server 2005 Standard Edition.

At my prodding @buckley already provided a query from which you can derive information. I'll add that you can capture snapshots of this DMV periodically so that you capture things even if they have been pushed out of the cache since their last use. I won't get into how to maintain an archive of DMV data here; maybe a good blog post in the making.

Once you have the data (or just looking at the current plans that are cached), some common hints you can derive from the XML returned by sys.dm_exec_query_plan.query_plan:

    <QueryPlan      NonParallelPlanReason="MaxDOPSetToOne"
    ---- this query used OPTION (MAXDOP 1) ^^^^^^^^^^^^^^
    ---- or perhaps was forced using server maxdop settings or resource governor

    <IndexScan Ordered="0" ForcedIndex="0" ForceScan="1" NoExpandHint="0">
    ---- this query used WITH (FORCESCAN) ------------^

    <IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="1">
    ---- this query used WITH (NOEXPAND) ------------------------------^

    <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="1" ...>
    ---- this query used WITH (FORCESEEK) ------------------------------------^

    <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="1"
    ---- this query uses WITH (FORCESEEK(index(col))) ----------^-------------^

    <IndexScan      Ordered="0" ForcedIndex="1" ForceScan="0" NoExpandHint="0">
    ---- this query used WITH (INDEX = ...) -^

I am NOT an XML guy so I have no clue how to tell you to extract that information dynamically. (More on this below.)

I can't see any way to determine whether isolation has been set (at the batch level or using things like WITH (NOLOCK)) - you might have to:

capture those in the act by polling sys.dm_exec_requests (though transaction_isolation_level really only tells you about things like SET TRANSACTION ISOLATION LEVEL, not table-level hints, since you can apply NOLOCK only to some tables in the query), or
rely on parsing of the query text itself from sys.dm_exec_sql_text (noting that this can yield false positives if, say, NOLOCK is commented out, or you have a table name like dbo.SubmitNoLocks).

I also can't see any difference in the plan for query hints like OPTION (FORCE ORDER) or OPTION (FAST n), so likewise these may also need to rely on parsing the query text as opposed to relying on the plan XML.

EDIT

Since you can get a non-XML version of the plan from an alternate DMF, sys.dm_exec_text_query_plan, you don't necessarily have to become an XML guru to perform this parsing. For example, the following will capture all query plans in the cache that used WITH (INDEX...), WITH FORCESEEK[(...)], WITH FORCESCAN, WITH (NOEXPAND) or WITH (NOLOCK):

SELECT t.[text]--, qp.query_plan
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_text_query_plan(p.plan_handle, 0, -1) AS qp
WHERE
 t.[text] NOT LIKE '%dm_exec%' -- to keep this query out of result
 AND
 (
  qp.query_plan LIKE '%ForceSeek="1"%'
  OR qp.query_plan LIKE '%Forcescan="1"%'
  OR qp.query_plan LIKE '%NoExpandHint="1"%'
  OR qp.query_plan LIKE '%ForcedIndex="1"%'
  OR t.[text] LIKE '%NOLOCK%'
 )
 -- t.[dbid] = DB_ID() -- to limit results, but may be too exclusionary
;

As identified earlier, this could produce false positives for queries like this:

SELECT * FROM dbo.table /* WITH (NOLOCK) */
SELECT * FROM dbo.RenoLocksmiths;

But outside of parsing the query text for NOLOCK, for all the plan attributes - since the XML gets entitized and characters like quotes get escaped - it will not return false positives for queries like this:

SELECT * FROM dbo.table /* Forceseek="1" */
SELECT * FROM dbo.[WITH (FORCESEEK)];


Implementing a one to zero or one relationship Suposse I am using a SQL platform and want to implement a relationship of cardinality one-to-zero-or-one (1-0..1). This situation would pertain to a business context in which let us say that:

A product can have zero or one price.

So:

What are the differences between the two scenarios (DDL structures) shown bellow?
When should one be preferred over the other?
Is any of them considered a bad design?

Scenario 1:

CREATE TABLE product
(
    product_id       INT      NOT NULL,
    name             CHAR(30) NULL,
    product_price_id INT      NULL,
    CONSTRAINT PK_product       PRIMARY KEY (product_id),
    CONSTRAINT UQ_product_price UNIQUE      (product_price_id)
);

CREATE TABLE product_price
(
    product_price_id INT     NOT NULL,
    price            DECIMAL NOT NULL,
    tax_rate         DECIMAL NOT NULL,
    currency_id      INT     NOT NULL,
    CONSTRAINT PK_product_price            PRIMARY KEY (product_price_id),
    CONSTRAINT FK_product_price_to_product FOREIGN KEY (product_price_id)
        REFERENCES product (product_id)
);

Scenario 2:

CREATE TABLE product
(
    product_id INT      NOT NULL,
    name       CHAR(30) NULL,
    CONSTRAINT PK_product PRIMARY KEY (product_id)
);

CREATE TABLE product_price
(
    product_id  INT     NOT NULL, -- Assume that this column might be set as PRIMARY KEY or UNIQUE.
    price       DECIMAL NOT NULL,
    tax_rate    DECIMAL NOT NULL,
    currency_id INT     NOT NULL,
    CONSTRAINT PK_product_price            PRIMARY KEY (product_id),
    CONSTRAINT FK_product_price_to_product FOREIGN KEY (product_id)
        REFERENCES product (product_id)
);

Scenario 1 could have multiple products linked to the same product_price. The second scenario, where the tables have the same PK (and one is a FK), is forced to being 1 to {0,1}.

Edit: Well now that you've changed Scenario1, my answer doesn't fit any more. Now, your two scenarios are identical, but your first table has two PKs. Scenario2 is fine and achieves what you want. If most products have prices, then you could also have them in a single table, which would look like the results of a Left Join, such that the products without prices just have NULL in those columns.


SQL Server: how to troubleshoot login timeout expired error I have a production SQL Server 2012 (SP1) running on Windows Server 2008 R2 Enterprise edition.

A lot of time while connecting to this server from a remote host I to face connection timed out error. Developers also reported same error from their scripts as well.

Below is the error which I used to get from Management studio:

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: Named
  Pipes Provider, error: 40 - Could not open a connection to SQL Server)
  (Microsoft SQL Server, Error: 5)

However this is not so frequent and in next retry I use to connect to database.

From Python Scripts below is the error:

  11/05/2015 10:21:01 AM [ERROR] ('HYT00', '[HYT00] [Microsoft][ODBC SQL
  Server Driver]Login timeout expired (0) (SQLDriverConnect)')
  11/05/2015 10:21:11 AM [ERROR] global name 'traceback' is not defined

Above error is reported a lot of time by the scripts. In the SQL Server error log I didn't find any issue. Is there some issue with the SQL Server driver or with SQL Server?

A lot of time while connecting to this server from a remote host I to face connection timed out error. Developers also reported same error from their scripts as well.

Interestingly, it's not "all the time" which sounds like you can connect "some" of the time. Since this is intermittent it definitely sounds like a name resolution issue. Try registering and connecting to SQL Server with only the SQL Server IP address within SQL Server Studio.  For named instances you can type something like W.X.Y.Z\SQLInstanceName.


Obtaining database information and values in extended properties I need to obtain each DB (together with its information) from SQL Server and I am doing so by the below:

use master;
select *
from sys.databases
where database_id > 4
order by name

I also need to obtain a particular value from extended properties. Currently I am doing the below FOR EACH DB manually to obtain this value:

use <db_name>
select value from sys.extended_properties
where class_desc='DATABASE' and name = 'Application Name'

However, I need to get the value outputted from the last query in a column for each of the DB displayed by the first query respectively. Can this be done?

You could use a cursor to loop over the databases and store the resultsets in a temporary table

CREATE TABLE #TEMP(Databasename nvarchar(500),
                   value sql_variant)

DECLARE @sql NVARCHAR(max);
DECLARE @dbname NVARCHAR(500);
DECLARE c cursor LOCAL FAST_FORWARD FOR SELECT NAME
                                        FROM sys.databases
                                        WHERE database_id > 4
                                        ORDER BY NAME;
OPEN c
FETCH NEXT FROM c INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = '
use '+QUOTENAME(@dbname)+'

SELECT '''+QUOTENAME(@dbname)+''' as databasename,
        value
FROM sys.extended_properties
WHERE class_desc=''DATABASE'' and name = ''Application Name''';

INSERT INTO #TEMP
EXEC(@sql);

FETCH NEXT FROM c INTO @dbname;
END
CLOSE c;
DEALLOCATE c;

SELECT * FROM #TEMP;
DROP TABLE #TEMP;

Testing

USE my_test;
GO
EXEC sp_addextendedproperty
@name = N'Application Name',
@value = 'ValueHere';

USE Test;
GO
EXEC sp_addextendedproperty
@name = N'Application Name',
@value = 'ValueHere';

Result

Databasename    value
[my_test]       ValueHere
[Test]          ValueHere


How to know exactly why SQL SERVER 2014 Express installation failed? I downloaded ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe from Microsoft. When I tried to install it I get the following error:

But it does not say exactly what I have missing.

My environment is configured as follows:

Windows 7 Ulitimate 64 bits
4 GB of RAM and enough HDD space
Intel i3 processor (enough for SQL SERVER 2014)
.Net 3.0, 3.5, 4.0 installed on my computer

Still it says something is wrong without even telling me what.

How to solve that issue?

If you can open the SQL Server Installation Center with this installer it will give you a lot of useful info.

In the Planning section you can find the Hardware & Software requirements. Or: https://msdn.microsoft.com/en-us/library/ms143506(v=sql.120).aspx


Is it useful to split a table into a frequently used and a archive table? I have a table with 1.000.000+ entries. Only the newest 100.000 entries are used very frequently. The other 90% are used very seldom.

Is it useful to split this table into a frequently used table with the 100.000 entries and a archive table?

I would have to move approx. 10.000 elements to the archive table every day.

The server logic to find an element would be:

Search in the frequently used table.
If not found there, search in the archive table.

Background

I've done some testing with random data in a small table and a big one (10 times more data). A SELECT query for one specific element took 0.6 times more time in the big table than in the small one. I believe that makes a difference in overall performance with 1000+ queries per second.

@Rick James

The create is

CREATE TABLE IF NOT EXISTS `note` (
  `note_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `title` varchar(100) NOT NULL,
  `content` text NOT NULL,
  `date_added` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  PRIMARY KEY (`note_id`),
  KEY `FK_note_user` (`user_id`),
  CONSTRAINT `FK_note_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The test query was just:

SELECT *
FROM `note`
WHERE `note_id` = $note_id
LIMIT 1

@Walter Mitty

Your second thought was what i meant. The critical transactions only happen on the newest 10% of data. So would splitting make sense?

Personally, I would split that table. Definitely consider Rick James' point about ensuring everything is Indexed properly and is being queried efficiently first, but, at the end of the day, you're removing 90% of the data that you have to sift through to get what you want.  Archiving that data will make queries faster by reducing row counts and shrinking the Index(es) considerably. There will be more Indexes because you'll have to duplicate them on your Archive table, so disk space will suffer a little, but my expectation is that performance will improve...

UNLESS:
The only part of this that jumps to mind that would KILL what I'm suggesting is if the data being moved to the Archive is accessed more than you think.  In that case, you're now querying two tables, totaling to MORE than what you had before (including duplicated Indexes/Keys); not only is there not a benefit, you've now taken more time (with more overhead) than your original setup would have taken.

In short: you need to be VERY sure about how often that Archived data is being accessed, and HOW it's being accessed, before you move it.


2ndQuadrant BDR installation not working anymore I previously used the following to install BDR (9.4) on Ubuntu:

echo "deb http://packages.2ndquadrant.com/bdr/apt/ trusty-2ndquadrant main" > "/etc/apt/sources.list.d/2ndquadrant.list"
wget --quiet -O - http://packages.2ndquadrant.com/bdr/apt/AA7A6805.asc | sudo apt-key add -
echo "deb http:// apt.postgresql.org/pub/repos/apt/ trusty-pgdg main" > "/etc/apt/sources.list.d/pgdg.list"
wget --quiet -O - https:// www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
apt-get update
apt-get install bdr-9.4

Since the end of May when I make use of this, it only installs normal PostgreSQL.
I've tried this on Ubuntu 14.04, 15.10 and 16.04.

Any help in this regard would be highly appreciated.

I am not too sure about this, but it seems that BDR is build into postgresql, since I added a shared_preload_library like this:
shared_preload_libraries = 'bdr', it seems to be working the same as BDR or maybe better.


Go back in time from a PostgreSQL backup using xlog I have a PostgreSQL backup and WAL archived before the db snapshot was taken.

Is it possible to restore the database using PITR to a time before the snapshot was taken?

No. You can't.
PITR can let you go forward in time, not backward.

You have to have a backup taken before your WAL were created and you must have each WAL between your backup and the time you want to reach.

You can find this documentation helpfull.
https://www.postgresql.org/docs/current/static/continuous-archiving.html


Postgres 10 shutting down on installation in Ubuntu 14 On trying to install postgres on Ubuntu 14 I am seeing postgres is shutting down. I am seeing the below errors in logs

[19043] LOG:  listening on IPv4 address "127.0.0.1", port 5432
[19043] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
[19044] LOG:  database system was shut down at 2017-12-16 02:41:39 PST
[19043] LOG:  database system is ready to accept connections
[19051] [unknown]@[unknown] LOG:  incomplete startup packet
[19043] LOG:  received fast shutdown request
[19043] LOG:  aborting any active transactions
[19043] LOG:  worker process: logical replication launcher (PID 19050) exited with exit code 1
[19045] LOG:  shutting down
[19043] LOG:  database system is shut down

Can some one let me know what could be going wrong? Why is postgres shutting down after the installation ?

listening on IPv4 address "127.0.0.1", port 5432, listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432", database system is ready to accept connections

Just your server starting up.

database system was shut down at 2017-12-16 02:41:39 PST

You always get this message at bootup, it's produced by the "expected state" of the write-ahead log manager.

incomplete startup packet

Nothing happened here.. a client buzzed in and then left., produced by a backend.

received fast shutdown request, aborting any active  transactions, database system is shut down, worker process: logical replication launcher (PID 19050) exited with exit code 1

This is just the postmaster shutting down


SQL Server Partitioning vs Indexes on Separate Filegroups I am just about to start on an excercise to reorganise a partitioned table and I was looking for best practises or recommendations.

Let us say I have a billion row table and 8 logical disks available to me for this table.

The question I have is, is it better to create a partition scheme which divides the data into 8 filegroups and has the indexes storage aligned with the data...

Or would it be better to create 4 file groups for data and 4 for indexes (not aligned), then place each of the filegroups onto one logical disk?

Any suggestion or comments would be welcome.

First and foremost: those logical disks better be backed by at least 8 different physical disks. If you're asking about load balancing 8 logical disks created on the same physical storage (the same spindles) then you're wasting time.

The best (and simplest!) option is to create a single filegroup with 8 files (equal in size and pregrown), each on a spindle, and then place the table and the indexes in this filegroup. SQL Server will balance the data equally among the files.

Partitioning is a feature for ETL switch in and switch out. It should not be used for performance, as the best you can hope for is equal performance with the original table. For performance use a well designed clustered index, one that matches the typical load.

If your data is really known upfront and the index usage characteristics are very well understood then you may try to balance them explicitly on their own filegroups. But trying to wrestle manual control over this is more likely to cause harm than benefit. The simpler option of single file group with 8 files balances IO better than manual explicit control 99% of the times.


Extracting Multiple Values from Single Field I'm an SQL beginner and I'm having trouble searching correctly for the answer to this question, although I'm sure that it's been asked before.  I have an column in an SQL table that looks like this:

custom_fields (this is the column name)
age_range=35-49
gender=Male
how_heard=Other  (All three of these values are in the same field)

Is there any way to reach inside of this field with an SQL statement to get the values individually?  I'd like to make three lists of the individual values to export to CSV files in a vertical columns.

Basically, the result should be a column like (for each value):

age_range
35-49
45-55
25-35
... etc.

I've been trying:

SELECT custom_fields.age_range FROM arp3_cam WHERE 1

But this is obviously incorrect.  I'd really appreciate any hints on how to filter for values inside the same field -- feel free to redirect me to an answer if this has already been asked somewhere else.  Thanks!

EDIT: based on other posts I've been reading, this setup violates first normal form, however, I still need to extract the data, if possible.  I definitely wish the values in "custom_fields" had been added as individual columns.

Try this

select substring(custom_fields,instr(custom_fields,'gender')-7,5) as age_range from tbl;


How to only update if nothing has changed? I need to validate several things before I insert Say I have two tables: runners and waypoint_checkins. I want to add waypoint checkin entries but I can never add a waypoint if the runner has finished the race, (see below for schema). Finally. A runner can never check into a waypoint if the waypoint is listed as closed.

table: runners
id | name | is_finished
---+------+------------
15 | abe  | false

table: waypoints
id  | currently_open
----+---------------
1   | true
2   | true
3   | false
... | ...

table: waypoint_checkins
runner_id | waypoint_id | time
----------+-------------+-------
15        | 1           | 5:00
15        | 2           | 5:23
15        | 4           | 5:52

So on a checkin I'm given (runner_name, waypoint_id, time). First I need to find the runner and make sure they are still running:

SELECT id, is_finished FROM runners WHERE name = %name;

Then I need to check if the runner is at an open waypoint;

SELECT currently_open FROM waypoints WHERE id = %waypoint_id;

Finally I need to insert a new waypoint checkin:

INSERT INTO waypoint_checkins VALUES (%runner_id, %wp_id, %time);

But between query 1 and 3 the runner may have finished. How can I do the insert statement where I can guarantee that the earlier select is still valid?

Instead of issuing those checks as separate SELECT statements, make them part of the INSERT statement. You will need to replace the INSERT...VALUES syntax with the INSERT...SELECT one to be able to use a WHERE clause:

INSERT INTO
  waypoint_checkins (runner_id, waypoint_id, time)
SELECT
  runners.id,
  %waypoint_id,
  %time
FROM
  runners
WHERE
  name = %runner_name
  AND is_finished = 'false'
  AND EXISTS (
    SELECT
      *
    FROM
      waypoints
    WHERE id = %waypoint_id
      AND currently_open = 'true'
  )
;

You can see that the first check is directly integrated into the INSERT statement, because the argument applied to runners is a name and you need to get an ID from it as one of the values to be inserted. The second check is in the form of an EXISTS predicate.

All three initial arguments (runner_name, waypoint_id, and time) are thus parameters of this single statement.


MySQL Replication Lag Behaving Erraticly I've got a vanilla MySQL replication, using "MIXED" mode.

The mysql slave lag calculation behaves very strangely - one moment it is 0 and the one after it it's 3630 seconds (or a similar number), then back to 0, and so on and so forth. Obviously, something is wrong in the replication configuration, since MySQL computes the lag based on the timestamps in the relay log.

I've tried checking the time of the servers, which is identical (using SELECT NOW() in MySQL). I've also checked the timezone (SELECT @@system_time_zone) which is set to CDT on both master and slave.

What else can I verify to make sure this issue is resolved? Did anyone else encounter this problem?

MySQL Replication performs weird calculations for Seconds_Behind_Master based on a few things.

What NOW() returns for SQL run on the master as recorded in relay logs
What NOW() returns on the slave
The last position from the master that was executed

Here is a SHOW SLAVE STATUS\G and how to identify the Last Executed SQL from the Master

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

Notice the following fields in SHOW SLAVE STATUS\G

Master_Log_File (Line 6) : Log File on the Master whose position was last read
Read_Master_Log_Pos (Line 7) : Last position read on the Slave from the Master
Relay_Master_Log_File (Line 10) : Log File on the Master whose position was last executed
Exec_Master_Log_Pos (Line 22) : Last position executed on the Slave from the Master
Relay_Log_Space (Line 23) : Sum of bytes from all relay logs

To calculate Seconds_Behind_Master, it would essentially figured out

NOW() as recorded in Master LogFile 'Master_Log_File' LogPos 'Read_Master_Log_Pos'
minus
NOW() on the Slave

This number can intermittently grow because the I/O thread can collect more entries from the Master and loading it into the last relay log while processing the SQL statement located at Master LogFile 'Relay_Master_Log_File' and Master LogPos 'Exec_Master_Log_Pos'. This increasing number is also manifested by three(3) variables changing: Relay_Log_Space, Master_Log_File, Read_Master_Log_Pos. If Seconds_Behind_Master is 0, this is good indication that Read_Master_Log_Pos and Exec_Master_Log_Pos virtually the same. Also worth noting is the fact that long running SQL can fool you into thinking that replication is behind. Once that SQL statement is complete and no other statements have sufficiently backlogged, Seconds_Behind_Master can drop dramatically, even to 0.

So, why would Seconds_Behind_Master bounce back and forth between 0 and an increasing number. It is possible for network latency in transmitting SQL through the I/O thread to contribute to miscalculation because the needed TIMESTAMP variable was not reached yet in the back of the last relay log (remember this is asynchronous replication). To verify this, run mysqlbinlog against any binary log or relay log and see where the TIMESTAMP variables are written between statements.

This behavior was first addressed June 22, 2007 and the bug was supposedly fixed then.

Here is one sure way to take MySQL Replication and screw its head on correctly (For this example, use the same SHOW SLAVE STATUS\G)

Using Relay_Master_Log_File mysql-bin.002814 and Exec_Master_Log_Pos 823078734 as the Log FilePosition to Restart From, run these commands

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.002814',MASTER_LOG_POS=823078734;
START SLAVE;

These steps should 1) kill both I/O and SQL Threads, 2) wipe out All Collected Relay Logs, 3) start with a New Relay Log, and 4) establish New I/O and SQL Threads.

Replication should be good to go from here. As to whether or not this bug can truly be addressed, this now in Oracle's hands (That last statement does not inspire any confidence, does it ???)


Order by exact matches (jsonb array), then lexeme similarity Say I have a table like this in Postgres 9.5:

CREATE TABLE public.posts
(
    content text,
    tags jsonb,
)

I'd like to design a single query that:

Finds results based on exact tag OR matches (tags ?! array['tag1','tag2']) AND on matched free-form text (to_tsvector(content) @@ plainto_tsquery('some phrase'))
Orders by tags matches first, based on the # of matches - i.e. if row A has [apple, orange] and row B has just [apple], then a search for array['orange', 'apple'] would yield row A higher than row B (but they'd both be returned)
Orders by content second, based on the weight/similarity of the returned result. So a search for the keyword 'french hello' would yield a row containing content of "how do I say 'hello' in French?' higher than 'what's the weather like in the French Riviera?'

How would I go about combining the above in a single query, so that both exact tags matches and/or fuzzy content matches yield results using the weighting above?

Check if this is what you're looking for [I didn't fully understand your first OR / AND condition. I assumed it was just an OR].

WITH posts AS
(
SELECT
    *
FROM
    (VALUES
        ('how do I say ''hello'' in French?', '{"orange":1, "apple":2}'::jsonb),
        ('what''s the weather like in the French Riviera?', '{"peach":3, "lemon":4}'::jsonb),
        ('awful weather in England', '{"peach":5, "lemon":6}'::jsonb),
        ('awful weather in England', '{"pineapple":5, "strawberry":6}'::jsonb),
        ('doubtful french fries', '{"blueberry":5, "pear":6}'::jsonb),
        ('the rain, in Spain, is mainly in the plain', '{"melon":7, "watermelon":8, "banana":9}'::jsonb)
    ) AS posts(content, tags)
)

SELECT
    *,
    /* Use ts_rank to compare level of full text search coincidence */
    ts_rank(to_tsvector(content), plainto_tsquery('french') ||
          plainto_tsquery('hello')) AS rank,
    /* Subquery to count number of tag matches */
    (SELECT
          count(case when tags ? a then 1 end)
     FROM
          unnest(array['melon', 'banana', 'lemon']) AS a
    ) AS number_of_matching_tags
FROM
    posts
WHERE
    /* Check for any of the tags */
    tags ?| array['melon', 'banana', 'lemon']
    OR
    /* Check for any of the search terms. You have to || tsqueries */
    to_tsvector(content) @@
        (plainto_tsquery('french') || plainto_tsquery('hello'))
ORDER BY
    number_of_matching_tags desc nulls last,
    rank desc ;

(The inclusion of number_of_matching_tags and rank columns is only to clarify results)


Why must TVPs be READONLY, and why can't parameters of other types be READONLY According to this blog parameters to a function or a stored procedure are essentially pass-by-value if they aren't OUTPUT parameters, and essentially treated as a safer version of pass-by-reference if they are OUTPUT parameters.

At first I thought the goal of forcing TVP to be declared READONLY was to clearly signal to developers that the TVP can't be used as an OUTPUT parameter, but there must be more going on because we can't declare non-TVP as READONLY. For example the following fails:

create procedure [dbo].[test]
@a int readonly
as
    select @a

  Msg 346, Level 15, State 1, Procedure test
  The parameter "@a" can not be declared READONLY since it is not a table-valued parameter.

Since statistics aren't stored on TVP what is the rationale behind preventing DML operations?
Is it related to not wanting TVP to be OUTPUT parameters for some reason?

The explanation seems to be tied to a combination of: a) a detail from the linked blog that was not mentioned in this question, b) the pragmatics of TVPs fitting within how parameters have always been passed in and out, c) and the nature of table variables.

The missing detail contained in the linked blog post is exactly how variables are passed in and out of Stored Procedures and Functions (which relates to the phrasing in the Question of "a safer version of pass-by-reference if they are OUTPUT parameters"):

  TSQL uses a copy-in/copy-out semantics to pass parameters to stored procedures and functions....

  ...when the stored proc finishes executing (without hitting an error) a copy-out is made which updates the parameter passed in with any changes that were made to it in the stored proc.

  The real benefit of this approach is in the error case.  If an error occurs in the middle of a stored procedure's execution, any changes made to parameters will not propagate back to the caller.

  If the OUTPUT keyword is not present no copy-out is made.

  The bottom line:
  Parameters to stored procs never reflect the partial execution of the stored proc if it encountered an error.

Part 1 of this puzzle is that parameters are always passed "by value". And, it is only when the parameter is marked as OUTPUT and the Stored Procedure completes successfully that the current value is actually sent back. If OUTPUT values were truly passed "by reference", then the pointer to the location in memory of that variable would be the thing that was passed, not the value itself. And if you do pass in the pointer (i.e. memory address) then any changes made are immediately reflected, even if the next line of the Stored Procedure causes an error and it aborts execution.

To sum up Part 1: variable values are always copied; they are not referenced by their memory address.
With Part 1 in mind, a policy of always copying variable values can lead to resource issues when the variable being passed in is quite large. I have not tested to see how blob types are handled (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), XML, and those that should not be used anymore: TEXT, NTEXT, and IMAGE), but it is safe to say that any table of data being passed in could be quite large. It would make sense for those developing the TVP feature to desire a true "pass-by-reference" ability to prevent their cool new feature from destroying a healthy number of systems (i.e. to want a more scalable approach). As you can see in the documentation that is what they did:

  Transact-SQL passes table-valued parameters to routines by reference to avoid making a copy of the input data.

Also, this memory management concern was not a new concept since it can be found in the SQLCLR API that was introduced in SQL Server 2005 (TVPs were introduced in SQL Server 2008). When passing NVARCHAR and VARBINARY data into SQLCLR code (i.e. input parameters on the .NET methods within a SQLCLR Assembly), you have the option to go with the "by value" approach by using either SqlString or SqlBinary respectively, or you can go with the "by reference" approach by using either SqlChars or SqlBytes respectively. The SqlChars and SqlBytes types allow for full streaming of the data into the .NET CLR such that you can pull small chunks of large values as opposed to copying an entire 200 MB (up to 2 GB, right) value.

To sum up Part 2: TVPs, by their very nature, would have a propensity to consume a lot of memory (and hence deteriorate performance) if staying within the "always copy the value" model. Hence TVPs do a true "pass by reference".
The final piece is why Part 2 matters: why would passing in a TVP truly "by reference" instead of making a copy of it change anything. And that is answered by the design goal that is the basis for Part 1: Stored Procedures that do not complete successfully should not alter, in any way, any of the input parameters, whether they are marked as OUTPUT or not. Allowing DML operations would have an immediate affect on the value of the TVP as it exists in the calling context (since passing by reference means you are changing the thing that was passed in, not a copy of what was passed in).

Now, someone, somewhere, is at this point probably talking to their monitor saying, "Well, just build in an automagic facility for rolling back any changes made to TVP parameters if any were passed into the Stored Procedure. Duh. Problem solved." Not so fast. This is where the nature of Table Variables comes in: changes made to Table Variables are not bound by Transactions! So there is no way to roll the changes back. And in fact, this is a trick used to save info generated within a transaction if there needs to be a rollback :-).

To sum up Part 3: Table-Variables do not allow for "undoing" changes made to them in the case of an error that causes the Stored Procedure to abort. And this violates the design goal of having parameters never reflecting the partial execution (Part 1).

Ergo: the READONLY keyword is needed to prevent DML operations on TVPs since they are Table Variables that are actually passed "by reference", and hence any modifications to them would be immediately reflected, even if the Stored Procedure encounters an error, and there is no other way to prevent that.

Additionally, parameters of other datatypes can't use READONLY because they are already copies of what was passed in, and so it wouldn't protect anything that isn't already protected. That, and the way that parameters of the other datatypes work was intended to be read-write, so it would probably be even more work to alter that API to now include a read-only concept.


Fast way of delete MySQL record I have a 1.2 million record in a database table( it use index column also)

I want to delete old 1 million record. I try to do it using PhpMyadmin but it take more time.

DELETE FROM `table` WHERE `id` < 1000000

Is there any way to do it fast?

I have another question: if i did this using SSH client in command line, will it be fast?

Another good way to do this is to copy 0.2 mln records to another table and drop the first table with 1.2 mln.


How do I determine if a SSIS transform is non-blocking, partially blocking, or fully blocking? There are several blogs which claim to categorize SSIS Transforms into blocking (asynchronous), non-blocking(synchronous) and partially blocking(asynchronous).

When looking into the specific question:
Is multicast synchronous(non-blocking) or asynchronous(partially blocking)?

One resources claims async:  "The Multicast is an asynchronous (also known as partially blocking) transformation" source:  http://social.technet.microsoft.com/wiki/contents/articles/7392.ssis-multicast-transformation.aspx

and another syncronous:
https://jorgklein.com/2009/04/14/ssis-lookup-is-case-sensitive/

Other resources claim the DQS Cleansing transform is a non-blocking transform, but it seems to me to be partially blocking.

Please don't answer with a link to a list of transforms categorized by type.  The answer to this question will hopefully enable a more rigorous method to PROVE a correct answer.

As the partially-blocking and blocking transformations will move data to new buffers as opposed to the non-blocking which operate on the buffer in place, I suspect that the solution will be to watch buffer creation during transform execution, but I am unsure (a) if this will produce the definitive answer and (b) how to do this.

I believe that a non-blocking transform will not cause a new execution tree to begin, so it is possible that the answer lies in logging the PipelineExectionTrees and PipelineExecutionPlan.  This may differentiate between synchronous and asynchronous, but may not differentiate between partially blocking and fully blocking.

An operation which is blocking must wait until all rows have been seen and handled before it can start populating buffers.

An operations which is partially-blocking writes data onto new buffers, which only get handled by the next operation once each buffer (typically just under 10,000 rows) is populated.

An operation which is non-blocking can have the subsequent operation working on the same buffer, as each row is handled.

But many texts will refer to 'partially-blocking' as 'non-blocking', as the behaviour is much closer to 'non-blocking' than 'blocking'.

You should be able to tell what's going on by watching the data flow run, and seeing when the numbers are increasing at each point.


How to set default innodb_buffer_pool_size before mysqldump and set it back to 1600M after mysqldump? I want to make backups every day automatically. It was fine when database was 700-800Mb, but now database (dumped sql file) is 1.5G and when using mysqldump it errors on random table with 2013 error.

When I comment out my setting of innodb_buffer_pool_size, mysqldump works perfectly.

Is there nice way to set to defaults innodb_buffer_pool_size before mysqldump and then back to 1600M?

p.s. I'm using mysqldump as following

mysqldump --max_allowed_packet=500M --net_buffer_length=548576 --single-transaction?

90% is dangerously high for any server.  For a "tiny" 2G, a much lower 500M should be safe.  Swapping is terrible for performance.  But, things are worse -- --max_allowed_packet=500M is taking up a terribly large amount of RAM.  Try 50M.  If that gives an error, then raise it and lower the buffer_pool.


How to execute two transactions concurrently in PostgreSQL? I want to execute two transactions concurrently in pgAdmin:

--The first transaction should modify the age of Paul
BEGIN;
DECLARE newage INTEGER;
--the original age of Paul is 32
newage := Select age FROM COMPANY WHERE name = Paul;
newage := newage+10;
update company set age := Sage where name = Paul;
commit;

--The second Transaction shows all information about Paul including his age
Begin
select * from company where name = 'Paul';
commit;

Both parts were written in the same SQL Editor. My first problem is that it doesn't compile, I obtain the message:

ERROR:  syntax error at or near "INTEGER"
LINE 2: DECLARE newage INTEGER;

Each pgAdmin query window has it's own session. You can run one transaction at a time in each session. It's not possible to run two transactions concurrently in the same session (at least not in pgAdmin or psql).

You can open and bind more sessions to the same query window with the "connection bar" in the menu of the query window, then switch between multiple open sessions. But it's simpler to just open another query window.

pgAdmin runs everything you execute at once inside a transaction. It's wrapped into BEGIN; .. COMMIT; implicitly - unless you issue explicit transaction control commands.

Start a transaction start with BEGIN; followed by SQL commands and don't commit yet. Switch to the other query window and do the same. Now both transactions are running concurrently. Some effects don't show until you COMMIT; one or both transactions.

Similar answer with detailed instructions on SO:

How to simulate deadlock in PostgreSQL?

As for your code and error message:

Like @Jack already told you, you are using PL/pgSQL code, the default procedural language extension in Postgres (similar to PL/SQL in Oracle). It's only valid in a function or anonymous code block.

A code block can only be executed as a whole.


Can I use the same keywords/symbols as DATEADD and DATEPART in user-defined functions The SQL functions DATEADD, DATEDIFF and DATEPART take a keyword parameter specifying the date/time period you're dealing with - it's not a varchar/string, it's a bare keyword. (https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql)

I'm creating some user-defined functions that do date/time manipulation, and need to include an argument which does the same thing as these functions - i.e. I want to create a user defined function that looks like this:

DATESEQUENCE(DAY, '2017-01-01', '2017-06-01')

or

DATESEQUENCE(MINUTE, '2017-07-31 11:00', '2017-07-31 15:00')

Is there any way to define an argument to a user-defined function using the built-in set of keywords/symbols for date/time periods?

Although you can't pass a datepart keyword as a parameter to a user-defined function, you can pass a string literal and perform the needed conversion. Below is an table-valued function example that leverages a numbers table to return an adhoc range of datetime values you can extend as needed.

CREATE TABLE dbo.Numbers(
    Number int NOT NULL
        CONSTRAINT PK_Numbers PRIMARY KEY
    );
WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1000 AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,t1000000 AS (SELECT ROW_NUMBER()
        OVER (ORDER BY (SELECT 0)) AS num FROM t1000 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c CROSS JOIN t10 AS d)
INSERT INTO dbo.Numbers WITH (TABLOCKX)
SELECT num - 1 AS Number
FROM t1000000;
GO

CREATE FUNCTION dbo.DATESEQUENCE(
      @DatePartName varchar(7)
    , @StartValue datetime2(0)
    , @EndValue datetime2(0)
    )
RETURNS TABLE AS
RETURN(
    WITH UnitOfMeasure AS (
        SELECT CASE @DatePartName
            WHEN 'second' THEN 1
            WHEN 'minute' THEN 60
            WHEN 'hour' THEN 3600
            WHEN 'day' THEN 86400
            WHEN 'month' THEN 1
            WHEN 'year' THEN 12
        END Units
    )
    SELECT
        CASE WHEN @DatePartName IN('month', 'year') THEN
            --use month for month and year
            DATEADD(month, Number * Units, @StartValue)
        ELSE
            --use second for second, minute, hour, and day
            DATEADD(second, Number * Units, @StartValue) END AS DateSequence
    FROM dbo.Numbers
    CROSS JOIN UnitOfMeasure
    WHERE
        Number <= CASE WHEN @DatePartName IN('month', 'year') THEN
            DATEDIFF(month, @StartValue, @EndValue) / Units
        ELSE
            DATEDIFF(second, @StartValue, @EndValue) / Units
        END
);
GO


How can I efficiently search for all records from a given month, or a given year? We have a couple queries that involve datetime fields. In particular, we are checking for things like

SELECT field1, field2, ..., field20 FROM table WHERE MONTH(startDate) == 1
SELECT field1, field2, ..., field20 FROM table WHERE YEAR(startDate) == 2014

So for example, maybe I want all January records (regardless of year), or I want all records from a particular year, or maybe I want all records for January 2014.

I have an index on the startDate column, but EXPLAIN tells me that both queries are doing full searches on the table because I'm using the MONTH and YEAR functions.

How can I make these queries more efficient?
Our table is very small, with about 100k records, but it still takes about 200-300 ms to complete.

Why not the classic way to avoid functions

WHERE startDate BETWEEN '2014-01-01' AND '2014-12-31';

or

WHERE startDate LIKE '2014-01-%' ;


How to get back sql table data? IDE: Microsoft SQL Server Management Studio

Mistakenly I have cleaned a table data using

delete from table tblBookData

and now it has cleaned that table, please tell me is there any way to get back that data?

If yes than please tell me the process, as I have studied there is a way to get back data if we have not closed the session.

Restore the DB into another DB , while restoring select "To a point in Time" and select "A specific date and time" and enter the date time before you deleted the record


Planned release date for the next version of SQL Server Has Microsoft published a roadmap for the next version, identifying a planned release date?

If not, how long do you think before the next version becomes production ready?

No, there is no public information about when SQL Server 2016 will be released, and in general this is always true for the next version, until they make the release target date public.

The general mantra is "when it's ready" - for this release, it will certainly be some time in the 2016 calendar year, but anything more specific than that would be guesswork at this point.

The people who know more specifics would only know target dates (e.g. gold, RTM, GA) today, and those dates definitely don't guarantee anything, and they are not able to share those publicly (otherwise they already would have).

Also, this type of question is not generally useful here, because it will be completely obsolete once the date is made public (and even more so once the date passes and the product is available). I am leaving it up because this can serve as the canonical "this question is unanswerable" duplicate target whenever this question comes up again (for this release or a future release).

And during my edit I thought it may be useful to post a list of the release dates of past releases (you might see a pattern here, at least for the three most recent releases, to help shape hypothetical expectations):

SQL Server 2016       - 2016-06-01
SQL Server 2014       - 2014-04-01
SQL Server 2012       - 2012-03-06
SQL Server 2008 R2  - 2010-04-21
SQL Server 2008       - 2008-08-07
SQL Server 2005       - 2005-11-07

And also a pointer to the post I maintain showing the releases of SQL Server 2016 builds to date (and which will be updated when RTM dates are announced):

Latest Builds of SQL Server 2016
Announcement of SQL Server 2016 GA Dates


Recursive update for tree structure in PostgreSQL I have the following structure of table.

id     chNum        parentid
---    ------       ---------
1        1            NULL
2        1.1          1
3        1.1.1        2
4        2            NULL  (Here add the new row)
5        3            NULL
6        4            NULL

After added new row in between the rows, this and all the following rows (based on chNum) should change.

Result as follows:

id     chNum        parentid
---    ------       ---------
1        1            NULL
2        1.1          1
3        1.1.1        2
4        2            NULL
7        3            NULL --\
5        4            NULL -- + -- these rows are changed
6        5            NULL --/

Is this possible to use recursive update SQL query in pl/pgsql and is this best way for faster data update than through application side?

Sample scenarios for "chNum" auto update:

First, lets try to generate the chnum column using a recursive query (without using the generated, of course):

WITH RECURSIVE r AS (
        SELECT c.id, c.parentid, ARRAY[row_number() OVER(ORDER BY c.id)] AS chnum
        FROM chapters c
        WHERE c.parentid IS NULL
        UNION ALL
        SELECT c.id, c.parentid, array_append(r.chnum, row_number() OVER(PARTITION BY c.parentid ORDER BY c.id)) AS chnum
        FROM chapters c JOIN r ON c.parentid = r.id
)
SELECT r.id, array_to_string(r.chnum, '.') AS chnum, parentid FROM r
ORDER BY r.chnum;

Now, we just need to take this result and update every id that doesn't match the chnum:

WITH RECURSIVE r AS (
        SELECT c.id, c.parentid, ARRAY[row_number() OVER(ORDER BY c.id)] AS chnum
        FROM chapters c
        WHERE c.parentid IS NULL
        UNION ALL
        SELECT c.id, c.parentid, array_append(r.chnum, row_number() OVER(PARTITION BY c.parentid ORDER BY c.id)) AS chnum
        FROM chapters c JOIN r ON c.parentid = r.id
)
UPDATE chapters c
SET chnum = array_to_string(r.chnum, '.')
FROM r
WHERE c.chnum IS DISTINCT FROM array_to_string(r.chnum, '.') AND c.id = r.id;

See this SQL Fiddle that I used to test it.

I'm making some assumptions here:

The id column is primary key.
The order is defined by the id column, probably a pos column or after/before thing would be better.

Also, unless it is a really huge table, I wouldn't store the chnum result, instead I'd create a view with the first query and just use it on the code.


right join leaving one row from right table I am using MariaDB,in my sample database I am having two tables department and emp.
I want to display all the department having less than 50 employees.
In this case it should display all the department names.

select  count(a.deptId),b.deptname
    from  emp as a
    right join  department as b on a.deptId = b.deptId
    group by  a.deptId
    having  count(a.deptId) <= 50 ;

I tried above mentioned query, it is returning correct count for the department but it is leaving the last deptname operations mysteriously.
Can anybody explain me why it is happening?
My understanding : As per right join the right side table's all row will be fetched and matched with the other table. If matches found result will be displayed otherwise NULL will be printed.

+--------+-------------+
| deptId | deptname    |
+--------+-------------+
|    501 | HR          |
|    502 | Accounts    |
|    503 | Development |
|    504 | Operations  |
+--------+-------------+

+--------+--------+------+-------+--------+
| emp_id | salary | name | bonus | deptId |
+--------+--------+------+-------+--------+
|      1 |    850 | NULL |    10 |    501 |
|      2 |    920 | NULL |  NULL |    502 |
|      3 |    550 | NULL |  NULL |    501 |
|      4 |    450 | NULL |     5 |    502 |
|      5 |    800 | NULL |  NULL |    501 |
|      6 |    920 | NULL |  NULL |    502 |
|      7 |    155 | NULL |  NULL |    501 |
|      8 |     50 | NULL |    20 |    501 |
+--------+--------+------+-------+--------+

You are grouping by the thing you are counting, not by the department name.
Change your group by to:
group by b.deptname

Edit:Adding a little flavor text as requested.

Basically what is happening is you are choosing to aggregate one of the values (in this case the counts of the salary) so that it "rolls up", and the group by generally indicates which value you want to do the rolling up by.

It makes some sense to say "I want to group by the number of employees" but you are actually trying to express "I want to return the number of employees grouped by department."


DTS Type Mismatch at Join I am new to ETL and VB in general and need some help resolving this issue. I have looked online and found some interesting answers but none seem to be working for me.

varamt = Lookup (
    "Lookup_tbl1",
    [Lookup_tbl1].[Amount],
    Array(DTSSource("DEPT_ID"), DTSSource("ACCT_ID")))

IF NOT isEmpty(varamt) or NOT isNULL(varamt) or NOT varamt="" THEN
    DTSDestination("AMOUNT") = varamt
ELSE
    DTSDestination("AMOUNT") = DTSSource("AMOUNT")
END IF

Lookup_tbl1 has ACCT_ID and DEPT_ID as the primary keys. My destination table does not have these as primary key and ACCT_ID has NULL values in the table. Dept_ID and Acct_ID are varchar.

When I tried this transformation where ACCT_ID was notNULL, it worked, but not in the table where I want it to work.

Here is the error message that I get:

Inserting into [AMOUNT] ...
Error details: The number of failing rows exceeds the maximum specified.
(Microsoft Data Transformation Services (DTS) Data Pump (80020101):
Error Code: 0 Error Source= Microsoft VBScript runtime error
Error Description: Type mismatch: 'Join' Error on Line 36)
Execution Failed.`

There is no line 36 in my code. Please suggest solutions / pointers.

Ok. I was able to find the root cause (hopefully) and circumvent it. Most likely the reason was type mismatch 'join' was because ACCT_ID was NULL in the source table.

The below worked fine:

IF NOT isNULL(DTSSource("ACCT_ID"))
    varamt = Lookup ("Lookup_tbl1",
        [Lookup_tbl1].[Amount],
        Array(DTSSource("DEPT_ID"), DTSSource("ACCT_ID")))
    IF (NOT isEmpty(varamt) or NOT isNULL(varamt)) or varamt<>"" THEN
        DTSDestination("AMOUNT") = varamt
    ELSE
        DTSDestination("AMOUNT") = DTSSource("AMOUNT")
    END IF
ELSE
    DTSDestination("AMOUNT") = DTSSource("AMOUNT")
END IF


Increasing the number of tempdb files question about initial size I am planning on increasing the number of tempdbs we have on our sql cluster. I do have one question that I can't seem to find a clear cut answer to...

I have one tempdb that is over 50GB and I want to add an additional 11 more tempdb files but I do not want them all to be 50GB so I want to 'shrink' the original tempdb but my question is if I set the initial size to 4GB on the original tempdb file and I bounce the server will it shrink the original tempdb to 4GB or will it stay at 50GB?

Or

Should I just use DBCC SHRINKFILE (tempdev, 4096) on the original tempdb?

If you are going to bounce the server then nothing to do.

Just define the files and the initial size and they will be built with the initial size when you restart the service.

It does not shrink as nothing is retained in a log file.


JDBC Sqlserver to elastic search : one input to different output? I have a sqlserver2016 database that I want to index in elasticsearch using logstash.

Here is my logstash config file which somewhat works :

input {
jdbc {
jdbc_driver_library => "C:\elastic\Microsoft-JDBC-Driver-6.0-for-SQL-Server\sqljdbc_6.0\enu\jre8\sqljdbc42.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://10.11.12.13:1433;databaseName=testdb1;integratedSecurity=false;user=ElasticExtractor;password=flyingweisels;"
jdbc_user => "ElasticExtractor"
jdbc_password => "flyingweisels"
statement => "select top 150000 Item_ID itemid,merchant_id merchantid,modelnumber,language_id from items order by Item_Id desc"
}
}
output {
elasticsearch {
hosts => "localhost:9200"
index => "testdata"
document_type => "testtype"
document_id => "%{itemid}"
}
}

So what this file is supposed to do, as configured is insert 150k items in elasticSearch. Somewhat it only imports about a third of that, such as 62 382 in this case. If I try to insert 50k it only inserts about 20k.
Is there an obvious reason why it would do that ?

Here is the current execution log :

[2017-09-01T08:16:31,923][INFO ][logstash.outputs.elasticsearch] Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=>[http://localhost:9200/]}}
[2017-09-01T08:16:31,927][INFO ][logstash.outputs.elasticsearch] Running health check to see if an Elasticsearch connection is working {:healthcheck_url=>http://localhost:9200/, :path=>"/"}
[2017-09-01T08:16:32,006][WARN ][logstash.outputs.elasticsearch] Restored connection to ES instance {:url=>"http://localhost:9200/"}
[2017-09-01T08:16:32,007][INFO ][logstash.outputs.elasticsearch] Using mapping template from {:path=>nil}
[2017-09-01T08:16:32,042][INFO ][logstash.outputs.elasticsearch] Attempting to install template {:manage_template=>{"template"=>"logstash-*", "version"=>50001, "settings"=>{"index.refresh_interval"=>"5s"}, "mappings"=>{"_default_"=>{"_all"=>{"enabled"=>true, "norms"=>false}, "dynamic_templates"=>[{"message_field"=>{"path_match"=>"message", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false}}}, {"string_fields"=>{"match"=>"*", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false, "fields"=>{"keyword"=>{"type"=>"keyword", "ignore_above"=>256}}}}}], "properties"=>{"@timestamp"=>{"type"=>"date", "include_in_all"=>false}, "@version"=>{"type"=>"keyword", "include_in_all"=>false}, "geoip"=>{"dynamic"=>true, "properties"=>{"ip"=>{"type"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"}, "longitude"=>{"type"=>"half_float"}}}}}}}}
[2017-09-01T08:16:32,050][INFO ][logstash.outputs.elasticsearch] New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=>["//localhost:9200"]}
[2017-09-01T08:16:32,053][INFO ][logstash.pipeline        ] Starting pipeline {"id"=>"main", "pipeline.workers"=>4, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>5, "pipeline.max_inflight"=>500}
[2017-09-01T08:16:32,219][INFO ][logstash.pipeline        ] Pipeline main started
[2017-09-01T08:16:32,313][INFO ][logstash.agent           ] Successfully started Logstash API endpoint {:port=>9600}
[2017-09-01T08:16:32,643][INFO ][logstash.inputs.jdbc     ] (0.050000s) select top 150000 Item_ID itemid,merchant_id merchantid,modelnumber,language from items order by Item_Id desc
[2017-09-01T08:16:49,805][WARN ][logstash.agent           ] stopping pipeline {:id=>"main"}

Second thing is, let’s say I want to insert a row from SQL server that comes from this input, what plugin can I use so that if the row has a specific “merchant_id” it goes in an elastic TYPE named with that ID. Also, if it has a specific “language” it goes in an elastic INDEX with that language as the name.
Can that be done ?
Should I simply create multiple Logstash config files, one for each of those tasks ?

So I figured out what I was doing wrong, there were 2 different problems.
1. Wrong inserted row count
This was caused by the SQL query I was using. Elastic wants unique document_id values and the left join I was using caused multiple rows to be returned with the same values. What elastic does in such case is it overwrites the existing row with the new values.
2. Assign rows to different indexes and types
I had tried using the same syntax as in the document_id which ended up looking like this:
index => "%{Language}"
document_type => "%{MerchantID}"

This would result in rows getting inserted with in an index called %{Language} which was obviously not the desired result.
The problem was simple: ELASTIC DOES NOT LIKE UPPERCASE LETTERS.
So my final configuration file ended up looking like this:
input {
jdbc {
jdbc_driver_library => "C:\elastic\Microsoft-JDBC-Driver-6.0-for-SQL-Server\sqljdbc_6.0\enu\jre8\sqljdbc42.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://10.11.12.13:1433;databaseName=testdb1;integratedSecurity=false;user=ElasticExtractor;password=flyingweisels;"
jdbc_user => "ElasticExtractor"
jdbc_password => "flyingweisels"
statement => "select top 50000 id itemid,item_id itemid,Merchant_ID merchantid,model,case when (ID%2=0) then 'germanindex' else 'englishindex' end language from items order by id desc"
}
}
output {
elasticsearch {
hosts => "localhost:9200"
index => "%{language}"
document_type => "%{merchantid}"
document_id => "%{itemid}"
}
}

It works great !


Identify faulty sub-query (subquery returned more than 1 value) I am trying to resolve an issue with a query that fails with this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

This error isn't detailed enough for me as the query in question has several sub queries of which some have another level of sub queries.

I found the query itself using SQL Server Profiler but I want to know if there is a tool I can use to identify which sub-query this fails at.

The only other way I can think of resolving this is by brute forcing: remove each sub query until the outer query works. However, given the amount of brute forcing I would have to do, it seems like must be a better way to find out.

I think the best way would be to "prove" that your subqueries - that are compared with a value - do always return one or no value.

By making sure they all have either:

TOP (1) ... ORDER BY ... (or the equivalent modern ORDER BY ... FETCH NEXT 1 ROWS ONLY)
WHERE clause that uses the primary or a unique key, e.g.: WHERE table_id = 496
HAVING clause that uses the primary or a unique key
GROUP BY () or an aggregate function in the HAVING or SELECT clause (this ensures that the subquery will return 1 or 0 rows)

This of course is not at all trivial with complex queries (example: nested derived tables or ctes and/or nested correlated subqueries) and even harder when the unique keys are not documented.

It's not trivial because for the most complex queries, you may have to figure out the unique keys of all the derived tables/ctes/subqueries which may involve joins and more nested subqueries (and for the queries that involve joins, the foreign key constraints may be needed as well to find the unique keys.)


Table was deleted during ALTER, how to recover? I tried to ALTER a table to add a FOREIGN KEY as:

ALTER TABLE artists ADD
FOREIGN KEY(country_id) REFERENCES countries(country_id) ON DELETE CASCADE;

and I received an error of

ERROR 2013 (HY000): Lost connection to MySQL server during query

I tried twice and the same error. The third time, my table was gone. How can I recover the dropped table? MySQL server (Ver 14.14 Distrib 5.7.21, for Linux (x86_64)) is on localhost.

The entries in other tables which have FK to this table still exist. Thus, I believe the table has not been deleted, but InnoDB map is damaged.

I checked the files, table_name.ibd exists but table_name.frm is missing. I cannot CREATE the table as the TABLESPACE already exists. How can I recover or create the table schema to use the available data? My concern is about the FOREIGN KEYS, which still exist in other tables.

I had a backup, but it is too old to restore the backup. I have to keep the current version. And it is a huge database. The table has about 1 million rows. The ibd file is over 100MB.

Install the same version of MySQL onto another VM. Create the same database. Create that same table; just the schema, no data. Shutdown original MySQL server. Copy the .frm from VM-MySQL to original in proper location. Fix permissions, if necessary. Start original MySQL. This should allow MySQL to see the table.


Problems splitting and aggregating data in SQL Hello I have the following data in csv file:

OrderID, OrderType, Amount, Price, Time
      1,       Buy,     10,     1, 10/01/2018 10:30 AM
      2,       Buy,      2,     1, 10/01/2018 11:30 AM
      3,      Sell,      5,     3, 10/01/2018 01:30 PM
      4,      Sell,      3,     6, 11/01/2018 10:30 AM
      5,       Buy,     13,     2, 11/01/2018 11:30 AM
      6,      Sell,      3,     3, 12/01/2018 10:30 AM
      7,      Sell,     11,     2, 13/01/2018 10:30 AM

What I need to do is I need to aggregate the records, so that I have matching amounts for buy and sell.

So I need to split the buy or the sell in order to have matching amounts.
like this:

ver1: (do nto modify buy records, just sell)

for OrderID=1 I have 10 buy but OrderID=3 (first sell) has just 5.
So what I have to do is search next sell (ID 4) 3, now the sum is still under 10, so I go to the next. If the next is taken at full I will have sum over 10, so I need to split OrderID=6 into two lines, one with the remaining to the 10 (2) and the second with the remaining (3-2=1). Then I look into OrderID=2. Amount is 2, from OrderID=6 I have 1 remaining that I need to use + 1 from (ID 7).

ver2: (keep sell orders and split buy)

In this case OrderID=1 has to be split into 5,3,2 (to be used in OrderID= {3,4,6})
Then OrderID=2 has to be split into two for (OrderID={6,7})
And OrderID=5 is ok to be used in full,s o no modification needed...

Is there a way to do all this in normal SQL or do I need some external program to manipulate the data accordingly?

My data is coming in csv, but I can import it in MySQL.
The program that I use to read the csv with sql is QlikView, but I am looking for general solution for general SQL or MySQL.

As I mentioned in comments on your original question, one option is to leave the original records alone, and have a separate table that you use to tie "buy"s to matching "sell"s.

CREATE TABLE reconcile
     ( id INT AUTO_INCREMENT
      ,buy_order_id INT
      ,sell_order_id INT
      ,amount INT);

Then, create a stored procedure something like this:

CREATE PROCEDURE reconcile_buy_sell()
BEGIN
  DECLARE c_buy CURSOR FOR
  SELECT t.OrderID, (t.`Amount` - rec.rec_amount) as `Amount`
    FROM MyTable t
           INNER JOIN (SELECT buy_order_id, SUM(`amount`) as rec_amount
                         FROM reconcile
                        GROUP BY buy_order_id
                      ) rec ON (t.OrderId = rec.buy_order_id)
   WHERE t.OrderType = 'Buy'
     AND t.`Amount` > rec.rec_amount
   ORDER BY t.`Time`
  ;

  DECLARE c_sell CURSOR FOR
  SELECT t.OrderID, (t.`Amount` - rec.rec_amount) as `Amount`
    FROM MyTable t
           INNER JOIN (SELECT sell_order_id, SUM(`amount`) as rec_amount
                         FROM reconcile
                        GROUP BY sell_order_id
                      ) rec ON (t.OrderId = rec.sell_order_id)
   WHERE t.OrderType = 'Sell'
     AND t.`Amount` > rec.rec_amount
   ORDER BY t.`Time`
  ;

  DECLARE buy_id, sell_id, buy_amount, sell_amount INT;
  DECLARE done INT DEFAULT FALSE;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN c_buy;
  OPEN c_sell;

  FETCH c_buy INTO buy_id, buy_amount;
  FETCH c_sell INTO sell_id, sell_amount;

  WHILE NOT done
  DO
    IF buy_amount < sell_amount THEN
      INSERT INTO reconcile (buy_order_id, sell_order_id, `amount`)
      VALUES (buy_id, sell_id, buy_amount);
      SET sell_amount = sell_amount - buy_amount;
      SET buy_amount = 0;
    ELSE
      INSERT INTO reconcile (buy_order_id, sell_order_id, `amount`)
      VALUES (buy_id, sell_id, sell_amount);
      SET buy_amount = buy_amount - sell_amount;
      SET sell_amount = 0;
    END IF;

    IF buy_amount = 0 THEN
      FETCH c_buy INTO buy_id, buy_amount;
    END IF;

    IF sell_amount = 0 THEN
      FETCH c_sell INTO sell_id, sell_amount;
    END IF;
  END WHILE;

  CLOSE c_buy;
  CLOSE c_sell;
END;

[Note: code is untested, and I'm not a MySQL expert - treat this as pseudocode, and feel free to correct any errors found.]

The two cursors will iterate through the "buy"s and "sell"s in the order in which they were entered. They only return orders where the full amount of the "buy" or "sell" has not already been reconciled in the reconcile table, and only return the unreconciled portion of the amount for those rows.

In the WHILE loop, we add rows to reconcile. If the amount of the current "buy" is less that the amount of the current "sell", then the full (remaining) amount for the buy goes into thereconcile` row; otherwise (whether the "sell" amount is less that or equal to the "buy" amount), we can use the amount for the sell.

We reduce the remaining amount for the current "sell" (or "buy", whichever is more) by the remaining amount for the "buy" (or "sell") we just used up, and set the "buy" (or "sell") amount to 0. If the amounts are the same, they'll both be 0 now.

Then, for whichever amounts are zero, we pull in the next record from the cursor.

When either cursor is out of records (including the degenerate case, where one or the other cursor returned no records), we stop reconciling.

NOTE: For performance purposes, it may be worth adding a reconciled bit column to MyTable (or whatever you actually call the table your CSV data gets imported to), and marking rows where the full amount has been reconciled as reconciled = TRUE. This could speed up locating the rows that aren't reconciled.


List of database users: can you run C# Code in SQL Server Management Studio? I'm trying to obtain a list of users associated with the AD Groups represented by the logons in my database. I understand from my research that the best way to do it is using this code:

{
Server srv = new Server();
//Iterate through each database and display.

foreach ( Database db in srv.Databases) {
   Console.WriteLine("========");
   Console.WriteLine("Login Mappings for the database: " + db.Name);
   Console.WriteLine(" ");
   //Run the EnumLoginMappings method and return details of database user-login mappings to a DataTable object variable.
   DataTable d;
   d = db.EnumLoginMappings();
   //Display the mapping information.
   foreach (DataRow r in d.Rows) {
      foreach (DataColumn c in r.Table.Columns) {
         Console.WriteLine(c.ColumnName + " = " + r[c]);
      }
      Console.WriteLine(" ");
   }
}
}

is this the best approach? Also, this code is in C#, how will I be able to run it in my SSMS?

The only language1 supported in SQL Server is T-SQL.  Having said that, it's pretty simple to use T-SQL to get the details you need.

/*
    Description: Display the Active Directory group
    membership permission path for all users
*/
SET NOCOUNT ON;
DECLARE @t TABLE (
    [account name] varchar(255)
    , [type] varchar(255)
    , [privilege] varchar(255)
    , [mapped login name] varchar(255)
    , [permission path] varchar(255)
);

INSERT INTO @t
EXEC xp_logininfo;
DELETE
FROM @t
WHERE type = 'group';
DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
SELECT 'EXEC xp_logininfo @acctname = ''' + name + ''', @option = ''members'';'
FROM master.sys.server_principals
WHERE type = 'G';
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @t
    EXEC sp_executesql @cmd;
    FETCH NEXT FROM cur INTO @cmd;
END
SELECT *
FROM @t t
ORDER BY t.[account name];
CLOSE cur;
DEALLOCATE cur;

The results returned show users with access to the SQL Server, and shows the group they are a member of that is providing that access.

You'll need membership in the sysadmin fixed server role or membership in the public fixed database role in the master database with EXECUTE permission granted to execute xp_logininfo.  You'll need standard user rights in Active Directory to enumerate the items in the directory.

1 - R is supported in certain configurations, and Python is supported in SQL Server 2017 and above.  Generally speaking, you'll want to use T-SQL.  Depending on your acceptable security posture, you may be able to use C# or Visual Basic to create a SQLCLR stored procedure that can be called from T-SQL in SQL Server 2005 and above.


Querying sums of grouped consecutive rows in PostgreSQL 9 I have data about people traveling in different countries like this:

country | begintimestamp      | distance

Germany | 2015-01-01 00:00:00 | 100
Germany | 2015-01-01 01:12:13 | 30
France  | 2015-01-01 02:13:14 | 40
France  | 2015-01-01 03:14:15 | 20
Spain   | 2015-01-01 04:15:16 | 10
France  | 2015-01-01 05:16:17 | 30
France  | 2015-01-01 05:17:18 | 5
Germany | 2015-01-01 06:18:19 | 3

What I need is to be able to receive a result like this - the distance of consecutive rows summed with the earliest begintimestamp:

country | begintimestamp      | distance

Germany | 2015-01-01 00:00:00 | 130  // 100+30, the distance of two first rows summed.
France  | 2015-01-01 02:13:14 | 60   // 40+20
Spain   | 2015-01-01 04:15:16 | 10   //
France  | 2015-01-01 05:16:17 | 35   // 30+5
Germany | 2015-01-01 06:18:19 | 3

I've tried to play around with PG window functions but have not been able to come up with anything that would lead me closer to the result.

select min(country) as country,
       min(begintimestamp) as first_begin_ts,
       sum(distance) as distance
from (
  select t1.*,
         sum(group_flag) over (order by begintimestamp) as grp
  from (
      select *,
             case
                when lag(country) over (order by begintimestamp) = country then null
                else 1
              end as group_flag
      from travel
  ) t1
) t2
group by grp
order by first_begin_ts;

The inner most query (alias t1) creates a number each time the country changes). The second level query (alias t2) then does a running sum on those flags, which essentially gives each consecutive set of countries a different number. The outer most query then groups by that number and sums the distance. The min(country) is necessary to make the group by operator happy, but as all rows with the same grp have the same country anyway, it doesn't matter.

SQLFiddle: http://sqlfiddle.com/#!15/fe341/1


Split the results of a query into parts I got this question in an exam, the teacher asked us to raise the employers' salaries using the following rules using a cursor:

An extra 4% for the first quarter with the highest salary.
An extra 6% for the second quarter with the highest salary.
For the rest an extra 8%

How to approach solving this.

My attempt was SELECT SALARY FROM (SELECT * FROM EMP ORDER BY SALARY) rownum > count(*)/4 to get the salary at first then add the %4  raise and update it to the new value.

I haven't tested it but I guess it might work, but I think it's a bit too far fetched and there may be a better or more efficient solution, your thought guys ?

To solve your issue, I did the following:

Create table and data:

CREATE TABLE salesperson (id INTEGER, salary INTEGER);

INSERT INTO salesperson VALUES (1, 100000);
INSERT INTO salesperson VALUES (2,  80000);
INSERT INTO salesperson VALUES (3,  60000);
INSERT INTO salesperson VALUES (4,  50000);
INSERT INTO salesperson VALUES (5,  30000);
INSERT INTO salesperson VALUES (6,  25000);
INSERT INTO salesperson VALUES (7,  12000);
INSERT INTO salesperson VALUES (8,  10000);

Then run this query (SQLFiddle here):

WITH cte AS
(
  SELECT id, salary, NTILE(4) OVER (ORDER BY salary) AS quartile
  FROM salesperson
)   --     <<== you can do a SELECT * FROM cte to see what's happening!
SELECT
       id,
       salary,
       CASE quartile
         WHEN 1 THEN salary * 1.08
         WHEN 2 THEN salary * 1.08
         WHEN 3 THEN salary * 1.06
         WHEN 4 THEN salary * 1.04
       END as new_salary
FROM cte;

Result:

ID  SALARY  NEW_SALARY
1   100000  104000
2   80000   83200
3   60000   63600
4   50000   53000
5   30000   32400
6   25000   27000
7   12000   12960
8   10000   10800

HTH and welcome to the forum! :-)

You can read the Oracle NTILE documentation here and an explanation from the excellent oracle-base site here.

EDIT:

You can also do it in one pass - but I think the cte (COMMON TABLE EXPRESSION - or WITH clause) shows the thought process more clearly. Thus:

SELECT
       id,
       salary,
       CASE NTILE(4) OVER (ORDER BY salary)
         WHEN 1 THEN salary * 1.08
         WHEN 2 THEN salary * 1.08
         WHEN 3 THEN salary * 1.06
         WHEN 4 THEN salary * 1.04
       END as new_salary
FROM salesperson
ORDER BY id;

Same result (fiddle)!


Do dormant databases impact server performance? On our web server, my team has been creating new databases in MySQL to manage backups.  They simply create new database_DATE and copy over our live site.  Which made me think today...  Can having a database (no possibility of ever being used) impact performance (guessing memory) of the server?

Yes, you are right. Memory is used by dormant databases

I mentioned this in the following posts over the years

Nov 13, 2015 : #1041 - Out of memory issue in mysql
Apr 22, 2014 : Do Inactive MySQL Databases Consume Memory?
Apr 21, 2014 : Adding new tables -- memory usage increases

Lots of tables ? Lots of columns ? Of course, that is less memory for mysqld to work with.

You just asked

  Is there a way to ensure a database is using no memory?

If you never access a table, no data or index pages will ever touch the InnoDB Buffer Pool, the MyISAM Key Cache, or any session's read_buffer or read_rnd_buffer.

Notwithstanding, having too many tables with lots of columns will result in nothing more that a lot of dormant information_schema memory. Neither the number of rows in each table nor the size of any of the tables matter.


Secure a hosted MySQL DB We manage an internet based SaaS app which has a MySQL DB as its backend DB.
It is a Windows 2008 R2 server running MySQL 5.5/Apache 2.2
I have started using MonYog to monitor the health of the DB Server and get regular (hourly) emails informing me that the number of refused connections is high.
Looking in the logs for the DB I can see constant login attempts as there are 'Access denied' entries in the log.
I suspect someone is trying a brute force hack attempt.

Question is, how can I best secure this server. At present I have port for DB open on firewall and cannot restrict that usage to a 'white list' of client IP addresses as I have developers working on the system who do not have a fixed IP address (working on the road, at home etc). The main app (web site) is on the same server so that connects as a local host only user so I guess that is pretty secure.

The challenge is the remote techs who need direct DB access for coding procedures etc.

Is there something I am missing in being able to lock the server down more and stop these constant hack attempts into the open TCP port of the DB?

Thanks
Mark

Enable SSL (Do this in addition to options below)

Besides the options listed below you should also enable SSL on your MySQL database. Otherwise usernames/passwords will be sent over the internet in plaintext. Based on your use case if a developer is sitting at a Starbucks and connects to your database (without SSL) then anybody could eavesdrop and steal the credentials to login.

You can read more about setting up SSL on MySQL here: http://dev.mysql.com/doc/refman/5.5/en/ssl-connections.html

Option 1 - Bastion Host

One approach to allow you to lock down access to your database is to setup a bastion host. The bastion host could be setup to allow connections from any IP address and your database would be setup to only allow connections from the bastion host. Basically it's like a proxy server.

The standard setup for it is to use a *nix machine with each developer having SSH access with key based authentication but no command execution privileges. Each developer could then SSH to the bastion and forward a port to the DB server but not execute anything else.

Securing the bastion becomes much easier than securing the DB server itself as authentication is done via SSH keys (vs passwords) and thus cannot be brute forced. Also, it doesn't matter that your MySQL server is running on Windows, only the bastion host needs to be *nix.

Option 2 - fail2ban (equivalent)

If you were running on a *nix OS I'd recommend running fail2ban. It's a daemon that scans your log files for unusually activity and sets up IP block rules based on it. For example you can configure it to blacklist IP addresses that are repeatedly trying to login to a website by having it scan the access logs.

You mention that your server is running Windows so fail2ban itself won't run on it (it's *nix only) but it looks like some other folks have tried similar things. I haven't tried either of these approaches so can't vouch for them but you could try them out:

https://serverfault.com/questions/27757/does-fail2ban-do-windows
https://serverfault.com/questions/43360/cygwin-sshd-autoblock-failed-logins

In both cases I think you'd have to tweak it to work with MySQL (it's setup to protect sshd).

Option 3 - JackDB

Full Disclosure: I'm the founder of JackDB

If your remote developers are connecting to your database to query/analyze/debug it via a database client then then you could have them use JackDB. It's a database client that runs entirely in your web browser.

Once it's setup, you could set up your firewall to only allow remote access from JackDB's IP address (it's a single static IP address) and your developer's could then use it to access an query your database. Your DB server would then be firewalled from the rest of the internet.

Note that this will only be a viable solution if your remote developers will only be executing SQL commands against the database and not remotely developing against it. JackDB allows you to run any SQL (SELECT, INSERT, UPDATE, DELETE, etc) but it does not allow for programmatic access for custom programs (it's a database client, not a driver interface).

You can read more about it and try it out here: http://www.jackdb.com/


SQL Server Maintenance Plan - Multiple vs Single Plan / Cleanup History First Question:
I have created multiple Maintenance Plans for SQL-Server via SSMS. Now I'm wondering what is the "best" for similar plans: Multiple or single plans with subplans? Is it safe to use multiple tasks in one subplan?
At the moment I have 7 separate plans:

Backup - Full (weekly)
Backup - Diff (daily)
Backup - Transaction (every 30min)
Delete old "bak" files (older than 6 months, executed weekly)
Delete old "trn" files (older than 6 months, executed weekly)
Delete old maintenance log files "txt" (older than 6 months, executed weekly)
Cleanup history (older than 6 months, executed weekly)

For example I could create one backup-plan with 3 subplan (full, diff, transaction - because of different times) and a second plan with one subplan (executed weekly) with 4 tasks inside (delete bak, trn, txt, history) executed at the same time. What would be your recommendation?
I'm monitoring the failed jobs with icinga2 => for the second subplan with 4 tasks => will this reported as failed job if one single task fails?
Second Question:
How big can the history get - with the knowledge of the first question => How long should I keep it?  For example I have chosen for the cleanup history 6 months => "synchronized" with deleting bak/trn files - because if is not equal either I can't select the restore "point in time" (history deleted, bak/trn files available) in the GUI or I will see a "point in time" (GUI) without having bak/trn files for it.
But I'm not sure how big the database (containing the history) can get? Is it "safe" to only delete it all 6 months or should I cleanup it more often?

First Answer
It depends, i.e. number of databases, database size (maybe all database might not need daily backup and log backup). However, for flexible and reliable approach I would refer this solution.
Second Answer
The retention period must be decided based on the requirement of business and nature of the data, most cases 30-60 days retention period followed, but again it depends on your environment and business, to get an idea with the backup storage requirement quickly, take the following query result into excel, hope you can easily analyze to get average backup size per day, per month etc..
select    backup_finish_date
        , FORMAT (backup_finish_date, 'yyyyMM') as backup_month
        , database_name
        , cast( (backup_size / 1048576) as numeric (12,2)) backup_size_MB
        , cast( (compressed_backup_size / 1048576) as numeric (12,2)) compressed_backup_size_MB
from msdb.dbo.backupset

Note: BACKUP Compression not possible in SQL Express edition


Unable to update a record with a trigger using a dynamic column on a PostgreSQL DB I'm trying to get a column named XXX_LASTCHANGE updated whenever a record is updated.

As the XXX would suggest, this is a dynamic value I want to pass with the trigger definition rather than creating dozens of similar triggers.

I came up with the following, but I cannot get this working as an error message is raised saying the error stack is full.

CREATE OR REPLACE FUNCTION update_lastchange()
RETURNS TRIGGER AS $$
BEGIN

    EXECUTE format('
        UPDATE "%s" t
        SET    "%s_LASTCHANGE" = NOW()
        WHERE  ($1)."%s_ID" = ($2)."%s_ID"',
        TG_TABLE_NAME, TG_ARGV[0], TG_ARGV[0], TG_ARGV[0])
    USING NEW, OLD;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER cnt_lastchange BEFORE UPDATE ON "CONTENT" FOR EACH ROW EXECUTE PROCEDURE update_lastchange('CNT');

I'm not sure what I'm missing. Any hint is really appreciated.

In PostgreSQL, every UPDATE will create a new row version. An attempt to modify the old row version in a BEFORE trigger won't work.

The new table row version that is about to be added to the database is available in the NEW variable of the trigger. You have to modify NEW in order to modify the new table row.

Dynamic modification of NEW is what you need, but this is hard to impossible in PL/pgSQL.

I suggest that you use a PL/Python trigger instead:

CREATE OR REPLACE FUNCTION update_lastchange() RETURNS trigger
   LANGUAGE plpythonu AS
'TD["new"][TD["args"][0] + "_lastchange"] = plpy.execute("SELECT current_timestamp")[0]["current_timestamp"]
return "MODIFY"';

CREATE TRIGGER update_lastchange
   BEFORE UPDATE ON content
   FOR EACH ROW EXECUTE FUNCTION update_lastchange('cnt');

What does the trigger do?

It runs SELECT current_timestamp on the database to get the current timestamp and retrieves that value from the result set (the column labeled current_timestamp from the first row (row 0) of the result set).

Then it stores that value in the appropriate entry of the TD["new"] mapping that contains NEW in PL/Python.

Finally, "MODIFY" is returned to indicate that the UPDATE should proceed.


PostgreSQL 9.6 performance problem with Sum() aggregate query I have the following table:

     id                | bigint                      | not null default nextval('shares_id_seq'::regclass)
     poolid            | text                        | not null
     blockheight       | bigint                      | not null
     networkdifficulty | double precision            | not null
     miner             | text                        | not null
     worker            | text                        |
     ipaddress         | text                        | not null
     created           | timestamp without time zone | not null
     useragent         | text                        |
     payoutinfo        | text                        |
     difficulty        | double precision            | not null default 0

Indexes:
        "shares_pkey" PRIMARY KEY, btree (id)
        "idx_shares_pool_block" btree (poolid, blockheight)
        "idx_shares_pool_created" btree (poolid, created)
        "idx_shares_pool_miner" btree (poolid, miner)
        "idx_shares_pool_miner_diff" btree (poolid, miner, difficulty)

I cannot wrap my head around why the following query takes so long:

explain analyze SELECT SUM(difficulty) FROM shares WHERE poolid = 'xmr1' AND miner = '4BCeEPhodgPMbPWFN1dPwhWXdRX8q4mhhdZdA1dtSMLTLCEYvAj9QXjXAfF7CugEbmfBhgkqHbdgK9b2wKA6nqRZQCgvCDm';
                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4150712.54..4150712.55 rows=1 width=8) (actual time=25490.101..25490.101 rows=1 loops=1)
   ->  Bitmap Heap Scan on shares  (cost=389414.64..4143195.97 rows=3006629 width=8) (actual time=2499.409..24815.011 rows=7445802 loops=1)
         Recheck Cond: ((poolid = 'xmr1'::text) AND (miner = '4BCeEPhodgPMbPWFN1dPwhWXdRX8q4mhhdZdA1dtSMLTLCEYvAj9QXjXAfF7CugEbmfBhgkqHbdgK9b2wKA6nqRZQCgvCDm'::text))
         Rows Removed by Index Recheck: 55232916
         Heap Blocks: exact=149273 lossy=2641988
         ->  Bitmap Index Scan on idx_shares_pool_miner  (cost=0.00..388662.98 rows=3006629 width=0) (actual time=2449.977..2449.977 rows=7445802 loops=1)
               Index Cond: ((poolid = 'xmr1'::text) AND (miner = '4BCeEPhodgPMbPWFN1dPwhWXdRX8q4mhhdZdA1dtSMLTLCEYvAj9QXjXAfF7CugEbmfBhgkqHbdgK9b2wKA6nqRZQCgvCDm'::text))
 Planning time: 0.256 ms
 Execution time: 25490.137 ms
(9 rows)

I specifically created index idx_shares_pool_miner_diff for this scenario, yet it isn't even used. Can't PostgreSQL use the index due to MVCC?

Update:

Running vacuum analyze shares as suggested made PostgreSQL use the aforementioned index:

 Aggregate  (cost=546165.94..546165.95 rows=1 width=8) (actual time=2489.446..2489.447 rows=1 loops=1)
   ->  Index Only Scan using idx_shares_pool_miner_diff on shares  (cost=0.69..537874.79 rows=3316458 width=8) (actual time=0.041..1924.396 rows=7461785 loops=1)
         Index Cond: ((poolid = 'xmr1'::text) AND (miner = '4BCeEPhodgPMbPWFN1dPwhWXdRX8q4mhhdZdA1dtSMLTLCEYvAj9QXjXAfF7CugEbmfBhgkqHbdgK9b2wKA6nqRZQCgvCDm'::text))
         Heap Fetches: 16575
 Planning time: 0.122 ms
 Execution time: 2489.477 ms
(6 rows)

I assume that's as fast as it gets without using parallel workers.

After reading the docs about index-only-scans, it looks like I'm taking a performance hit due to the constant heavy write activity on the shares table (> 200 inserts/sec), causing the many Heap-Fetches. Interestingly the number of Heap-Fetches is apparently only going up, never down.

If your table is INSERT-only, there are ways to get your sums (much) faster.

Assuming there is a column with monotonically increasing values (like id or created in your example), create a MATERIALZED VIEW to pre-compute sums older than a (recent) given threshold. And then just add the sum of recent additions to it:

CREATE MATERIALIZED VIEW shares_summed AS
SELECT poolid, miner, SUM(difficulty) AS sum_diff
FROM   shares
GROUP  BY  poolid, miner
ORDER  BY  poolid, miner;  -- optional, but to optimize some more
WHERE  created <  '2018-01-01 0:0';

If there are many combinations of (poolid, miner), add a UNIQUE index on that. Also required to refresh CONCURRENTLY, see below.

Fetching a row from that table cost next to nothing. Then you only add recent additions to it:

SELECT sum(sum_diff) AS total_sum  -- takes care of possible missing rows
FROM  (
   SELECT sum_diff
   FROM   shares_summed
   WHERE  poolid = 'xmr1'
   AND    miner = '4BCeEPhod...'

   UNION ALL
   SELECT SUM(difficulty)
   FROM   shares
   WHERE  poolid = 'xmr1'
   AND    miner  = '4BCeEPhod...'
   AND    created >= '2018-01-01 0:0'
   ) sub;

You only have to adapt the threshold value from time to time and REFRESH the MV. Best with the CONCURRENTLY option, to avoid concurrency issues with your many INSERTs. Consider:

Analyze needed after a refresh materialized view?

Remember the value and adapt queries accordingly. You might store it in another table.

To make it even faster, create a partitioned table and add another partition with pre-computed sums after every month (or whatever). You can easily schedule a monthly cron job to automate this. Then the threshold is always the start of the month or so:

SELECT sum(sum_diff) AS total_sum
FROM  (
   SELECT SUM(difficulty) AS sum_diff
   FROM   shares_summed_master  -- includes all partitions
   WHERE  poolid = 'xmr1'
   AND    miner = '4BCeEPhod...'

   UNION ALL
   SELECT SUM(difficulty)
   FROM   shares
   WHERE  poolid = 'xmr1'
   AND    miner  = '4BCeEPhod...'
   AND    created >= date_trunc('month', now())  -- careful, current time zone affects it
   ) sub;

Make sure there is no overlap, and pre-computed sums are current.

Table partitioning was largely improved and simplified with Postgres 10. Be sure to run the latest point release, some corner case bugs were fixed in Postgres 10.2. The above link to the manual is for version 9.6. Current version here.


Halting a multi-step Agent job without resorting to "Quit the job reporting success"? I have a multi-step process that sometime should stop in the middle, not because of any failure but for business reasons which are not failures.
How can I tell Agent to successfully exit the whole job?  (Using "Quit the job reporting success" throws a dreaded Yellow Triangle for the job, and the Red Circle for the step, even though there are no errors.)
Since there are many steps, any one of which might legitimately fail, I'd rather not lump everything into one giant Step.

One solution is to use multiple job steps and branch depending on the success/failure condition of the business condition - but that means the job will produce that "dreaded" yellow triangle and red circle for the step.
A second solution is to use 3 jobs and call one of two jobs based on the result of the business condition.
STEP 1 : Do something
STEP x : Test for business condition
    IF dbo.fnBusinessCondition = 1
       EXECUTE msdb.dbo.sp_start_job 'Job2'
    else
       EXECUTE msdb.dbo.sp_start_job 'Job3'


Creating optimized table without duplicates in MySQL I am struggling to create a table with a structure that shows values of user-date relation.
Sample pseudo-database:

+------+----------+----------+----------+-----
| user | 01012016 | 02012016 | 03012016 | ...
+------+----------+----------+----------+-----
| 1    | 2        | 57       | ...      | ...
| 2    | 12       | 73793    | ...      | ...
| 3    | 56       | 468      | ...      | ...
| 4    | 1689     | 12357    | ...      | ...
| 5    | 946      | 13       | ...      | ...
+------+----------+----------+----------+-----

Values are integer.

According to MySQL Reference Manual, key range is limited.

What structure a table should have to allow unlimited amount of columns and rows in this case?

I am aware that I can create a separate table for each user, but the idea is to keep everything optimized - in a smallest possible number of tables.

I am also aware of a simple structure (user, date, value), but there will be a lot duplicates, to be specific, in 'user' and 'date' columns:

+------+----------+----------+
| user | date     | value    |
+------+----------+----------+
| 1    | 01012016 | 57       |
| 1    | 02012016 | 73793    |
| 1    | 03012016 | 468      |
| 2    | 01012016 | 12357    |
| 2    | 02012016 | 13       |
| 2    | 03012016 | 618      |
| ...  | ...      | ...      |
+------+----------+----------+

And even if I decide to use this structure, what key should I define a primary one?

You should take a look at Second Normal Form (2NF), one of the recommended forms designed for optimized database data.

In 2NF, the users should be in one table, and references to the users should be in other tables, as appropriate. Since you're storing data for various dates with values, the table should definitely have the form of user, date, and value.

If the date is significant and has other data not related to that value, you might even end up with three tables, not just two, in order to satisfy 2NF, and this is perfectly normal.

If you're still not convinced, imagine you're now up to 100 days, and you need to figure out the total of the values. Using your scheme, you end up with a query that looks like this:

select field1+field2+field3+field4...field100
from mytable where user = 5

Conversely, to find the total for 100 days using 2NF, you simply do this:

select sum(value) from mytable where user = 5;

This is easier to maintain in the long run, even if the database allowed for unlimited columns, which it doesn't.

As for the Primary Key (PK), it's obvious: you should use a composite PK, consisting of the user's ID and the date value:

create table mytable(
    `user` int,
    `date` date,
    `value` int,
    primary key(`user`,`date`),
    index(`date`)
)

Your PK should ideally use the order you intend to use more frequently, and then index the other field if you're interested in doing faster queries involving the date field.