SQL Server query scanning more partitions than expected So my team has the following select statement in one of our stored procedures

SELECT
    ai.Name
    ,dc.Component
    ,SUM(dc.Value) Value
FROM
    Warm.DailyCosts dc
    JOIN Warm.AccountInfo ai
        ON dc.AccountInfoId = ai.Id
WHERE
    ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
    AND ((dc.CalendarId >= (20190601 + ai.DayOfMonth - 1)
        AND dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601
        AND dc.CalendarId < (20270601 + ai.DayOfMonth - 1)))
GROUP BY
    Name
    ,Component

And we've partitioned the DailyCosts table on the months of each year as follows:

CREATE PARTITION FUNCTION [PF_CostDateByYearMonth](int) AS RANGE RIGHT FOR VALUES (
    20180101,
    20180201,
    20180301,
    20180401,
    20180501,
    20180601,
    20180701,
    20180801,
    20180901,
    20181001,
    20181101,
    20181201,

    ...

    20300101,
    20300201,
    20300301,
    20300401,
    20300501,
    20300601,
    20300701,
    20300801,
    20300901,
    20301001,
    20301101,
    20301201)

We noticed that when we ran the procedure, rather than finding the appropriate partitions right away it looked like it was scanning partitions from either end. For the above example, it viewed partitions 20180101 up to 20190701 and 20301201 down to 20270601 totaling 62 partitions.

When we removed the math from the where statement (+ ai.DayOfMonth - 1), the partitions read dropped down to 2, 20190601 and 20270601, as expected. Note that we use seed data and DayOfMonth is set to 15 on every account.

What causes the server to scan the partitions when this math is included, and is it actually looking through all of the indexes in these partitions or is it just checking their ranges and moving on?

Any and all sources you can provide would go a long way in helping us understand!

Reasoning

When comparing the column of the non partitioned table against the partitioned table, sql server will not be able to know what DayOfMonth will hold, even if all of them are 15.

As a result it would not know which partitions to return to satisfy this filtering when joining the two tables.

A different example to give some more insight can be found here.

Testing

I was able to recreate your issue, for further questions please add as much information as possible.
This could be Table definition, indexes, partition scheme, ...

The DDL & DML is at the bottom.

When running the query we can get the same result:

62 partitions returned.

On the seek predicates it tries to filter what it can, this is the two filters on CalendarId that do not have the  + DayOfMonth-1.

This translates to

WHERE ...
    AND (( dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601))
...

When running the query with these filters you will see the same amount of rows returned on accessing the table in the query plan.

Only after getting this data, it can and will be joined to the AccountInfo table on the ID's and the DayOfMonth - 1

To confirm your statement that when we remove the column that only two partitions are scanned:

SELECT
    ai.Name
    ,SUM(dc.Component )
    ,SUM(dc.Value) Value
FROM
    Warm.DailyCosts dc
    JOIN Warm.AccountInfo ai
        ON dc.AccountInfoId = ai.Id
WHERE
    ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
    AND ((dc.CalendarId >= (20190601  - 1)
        AND dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601
        AND dc.CalendarId < (20270601  - 1)))
GROUP BY            Name

Only two are accessed and used:

Solution

I am going to assume that DayOfMonth only goes up to 31 days.

If you know these boundaries, you can 'hard code' them so that sql server knows what partitions to look for. After this you can add the extra filtering.

E.G.

WITH CTE
AS
(
SELECT
    ai.Name
    ,dc.Component
    ,dc.Value
    ,dc.CalendarId
    ,ai.DayOfMonth
FROM
    Warm.DailyCosts dc
    JOIN Warm.AccountInfo ai
        ON dc.AccountInfoId = ai.Id
WHERE
    ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
    AND ((dc.CalendarId >= (20190601 )
        AND dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601
        AND dc.CalendarId < (20270701)))

)

SELECT Name,Component,SUM(Value)
FROM CTE
WHERE ((CalendarId >= (20190601 + DayOfMonth - 1)
        AND CalendarId < 20190701)
    OR (CalendarId >= 20270601
        AND CalendarId < (20270601 + DayOfMonth - 1)))

GROUP BY
    Name
    ,Component

The sole purpose of the  cte is to let sql server know that it can also filter on dc.CalendarId >= dc.CalendarId >= (20190601 ) OR dc.CalendarId < (20270701)).

Sidenote: Adding a constraint does not work to enforce this.

This query gives us the result we want, with the correct  partition elemination:

Only accessing partitions 19 & 21.

You can probably also use OR / AND logic to get the same result if you prefer. The important part is getting the boundaries known.

Test data

CREATE SCHEMA WARM
GO

CREATE TABLE Warm.DailyCosts(ID INT IDENTITY(1,1) NOT NULL,
                            Component int,
                             Value int,
                             CalendarId INT,
                             AccountInfoId int
                             )

CREATE TABLE Warm.AccountInfo(Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                              Name VARCHAR(25),
                              CorrelationId uniqueidentifier,
                              DayOfMonth int
                              );
USE [master]
GO
ALTER DATABASE [test] ADD FILEGROUP [Partitionfg]
GO
USE [test]
GO
CREATE PARTITION FUNCTION [PF_CostDateByYearMonth](int) AS RANGE RIGHT FOR VALUES (
    20180101,
    20180201,
    20180301,
    20180401,
    20180501,
    20180601,
    20180701,
    20180801,
    20180901,
    20181001,
    20181101,
    20181201,
    20190101,
    20190201,
    20190301,
    20190401,
    20190501,
    20190601,
    20190701,
    20270601,
    20270701,
    20270801,
    20270901,
    20271001,
    20271101,
    20271201,
    20280101,
    20280201,
    20280301,
    20280401,
    20280501,
    20280601,
    20280701,
    20280801,
    20280901,
    20281001,
    20281101,
    20281201,
    20290101,
    20290201,
    20290301,
    20290401,
    20290501,
    20290601,
    20290701,
    20290801,
    20290901,
    20291001,
    20291101,
    20291201,
    20300101,
    20300201,
    20300301,
    20300401,
    20300501,
    20300601,
    20300701,
    20300801,
    20300901,
    20301001,
    20301101,
    20301201)

CREATE PARTITION SCHEME [PS_CostDateByYearMonth]
AS PARTITION [PF_CostDateByYearMonth]
ALL TO ( [Partitionfg] );

CREATE UNIQUE CLUSTERED INDEX ptci ON  Warm.DailyCosts(CalendarId,Id) ON [PS_CostDateByYearMonth](CalendarId) ;
USE [master]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'TestPartition', FILENAME = N'D:\DATA\TestPartition.ndf' , SIZE = 3072KB , FILEGROWTH = 65536KB ) TO FILEGROUP [Partitionfg]
GO
USE [test]
GO

INSERT INTO  Warm.DailyCosts(Component,
                             Value,
                             CalendarId,
                             AccountInfoId
                             )
SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20180101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
-- 2018
INSERT INTO  Warm.DailyCosts(Component,
                             Value,
                             CalendarId,
                             AccountInfoId
                             )
SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20190101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
-- 2019
INSERT INTO  Warm.DailyCosts(Component,
                             Value,
                             CalendarId,
                             AccountInfoId
                             )

SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20280101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
-- 2028

INSERT INTO Warm.AccountInfo(
                              Name ,
                              CorrelationId,
                              DayOfMonth
                              )

SELECT TOP(3000000) --3M
        CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS varchar(10)) + 'a',
       '00000000-0000-0000-0000-000000000000',
       15
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
CREATE NONCLUSTERED INDEX [IX_Component_Value_DailyCosts] ON Warm.DailyCosts
 (Component,Value)
ON [PS_CostDateByYearMonth](CalendarId);
GO


Convert table from MyISAM to InnoDB and keep MyISAM rows order I'm currently rewriting old project and I've got this strange scenario.

It's an online store, it has products with images. The images are stored in separate table. Here is example query. The part after the id- in the img column is the same for every row for pid.

I want to migrate this table to InnoDB, but keeping this order. InnoDB orders by id by default.

SELECT * FROM products_gallery WHEREpid= :productID

This is MyISAM

There is no particular order, probably they are stored in the way the are inserted, altrought if that was the case they should be ordered by id. I don't know that is really happening, but I think MyISAM has it's own order then no ordering is specified. I want to migrate this to InnoDB and keep this same order.

The only solution coming to my mind is to go trough product images before changing the storage engine and record the position rows are returned. After that I'll add new column to the table position and update it with the recorded position and sorting by position after migration.

So my question is there any easier solution?

@a_vlad has your answer... create a new table with the same structure without keys and autoincrement (it is faster to manage indexes in batch than one row at a time).  Then insert into the new table select * from the old.  After population, any indexes (including the pk) can be applied and add autoincrementation advanced to the next value you want to generate.


Temporary Resolving State on Synchronous HA, is there a way around this? So we're having an issue whereby any loss of communication between our 2 HA nodes causes the primary node to temporarily go into a 'resolving' state. This is for all of 1-3 seconds however we serve a DB to an application which doesn't retry connection and requires users to close and reopen meaning they lose work in progress.

This cluster is comprised of 2 nodes, one active and one non-readable secondary with a fileshare witness. All of our other applications don't mind this temporary blip however as mentioned this has a huge visible impact on other users. Is this a given when running HA in synchronous commit?

My concern is also when patching the secondary, I will be kicking users off when the secondary goes down. Would switching to async remove this constraint or is this an issue with how it has been configured?

It's difficult to answer the first part of your question as it seems to be related to the network and/or cluster configuration and not related to the availability mode. You should take a good luck at your cluster logs to see what is the root cause.

1.    Checking  cluster status
a.  Login to failover cluster manager console
b.  Click on the cluster name
c.  Check the summary details for errors
d.  Click on networks under the cluster name
e.  Check the summary and network connections tab at bottom of the window for errors
f.  Since this appears to be a 2 node cluster w/file share witness supporting AlwaysON, make sure each node and the FS has a vote in the cluster.

2.    Checking for file share witness errors
a.  Confirm the share is online and available to the network

3.    Obtaining cluster logs
a.  Login to a cluster node as an Administrator
b.  Examine both the System and Application event logs.
c.  Use powershell commandlet:  get-clusterlog  -uselocaltime ***this will save a cluster.txt file to C:\Windows\Cluster\Reports examine the log

As to your second question regarding patching, I assume that the nodes and the FS each have a vote.  If the secondary node is getting patched (windows patching or SQL SP/CU), the AG on the primary node will be available provided quorum is maintained between the primary node and the file share witness. If quorum is not maintained, the AG will shutdown  in order to protect the databases. The Availability mode is not a factor.


Permission denied with Copy command when using network share I am running the following Copy command via npgsql (.Net) from a client on the network:

COPY work_narcra00_2017_q2.a2_aevent FROM '//BRAHMA//EnvironProcessing//dbprocessfixedasciiimport.tmp' WITH CSV

I am getting this error:

ERROR: could not open file "//BRAHMA//EnvironProcessing//dbprocessfixedasciiimport.tmp" for reading: Permission denied
SQL state: 42501

I also tried the command from PgAdmin III and I get the same error.

The obvious answer is the file/folder permissions, but both the share and NTFS permissions have read/write permissions to Everyone.

Another post suggested adding permissions for Network Service but that makes little sense for accessing a network share because I cannot add the DBServer/NetworkService account to the client computer's share (though I tried adding the local Network Service account just in case)

Moving the file to a local folder on the server of course works, but that is not a viable long-term option right now.

Edit: I can see and edit the file just fine from within Windows using Notepad.

With COPY from a named file, the file is read on the server by the user who owns the database cluster (usually a service account) not on the client.  So the server (both machine and account) is what needs to have access to it. If your file is readable on the client, you can use COPY...FROM STDIN in conjunction with the support functions described here.


How to create Polygon from Point datatype in MySQL? I have a table like this:

CREATE TABLE `aois` (
  `aois_id` int(11) NOT NULL DEFAULT '0',
  `WS_A` point DEFAULT NULL,
  `WS_B` point DEFAULT NULL,
  `WS_C` point DEFAULT NULL,
  `WS_D` point DEFAULT NULL,
  `DB_A` point DEFAULT NULL,
  `DB_B` point DEFAULT NULL,
  `DB_C` point DEFAULT NULL,
  `DB_D` point DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Now i want to create a polygons from the points:

SELECT aois_id,
polygon((WS_A, WS_B, WS_C, WS_D, WS_A)) as geom
FROM aois;

But i always get the error message:

Operand should contain 1 column(s)

(I'm using MySQL Server Version 8.0.16 on Windows 10, if that is of any importance.)

Can anyone point me in the right direction?

Thanks a lot!

SELECT aois_id,
polygon(
   linestring(WS_A, WS_B, WS_C, WS_D, WS_A)
) as geom
FROM aois;


Error 3201 Operating system error 5(Access is denied.) Creating backup on network drive Msg 3201, Level 16, State 1, Line 1
Cannot open backup device
     '\\nst-shantanug\c$\Dummy Db Backup\dummydb.bak'.
Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

What all permissions do I need to assign to drive or pc where I want to take backup of my database.

The SQL Service account should have write access to the folder to be able to write the backup there.

Depending if you also want to create subdirectories, overwrite backups or delete old backups you need appropriate rights for that too.


SQL Insert error Column name or number of supplied values I have one of the stored procedures for my replication giving this error

  Insert Error: Column name or number of supplied value does not match table definition".

The stored procedure is spInsertsrra and the table is srra, I have put the syntax below for the 2 articles.

       CREATE PROCEDURE SpInsertSrra @num_SessIdSession NUMERIC(10),
                                      @tin_SrraCptAnom1  TINYINT,
                                     @tin_SrraCptAnom2  TINYINT,
                                     @tin_SrraCptAnom3  TINYINT,
                                     @tin_SrraCptAnom4  TINYINT,
                                     @tin_SrraCptAnom5  TINYINT,
                                    @tin_SrraCptAnom6  TINYINT,
                                    @tin_SrraCptAnom7  TINYINT
AS
    INSERT INTO srra
    VALUES      (@num_SessIdSession,
                 @tin_SrraCptAnom1,
                 @tin_SrraCptAnom2,
                 @tin_SrraCptAnom3,
                 @tin_SrraCptAnom4,
                 @tin_SrraCptAnom5,
                 @tin_SrraCptAnom6,
                 @tin_SrraCptAnom7)

GO

CREATE TABLE [SRRA] (
[SESS_ID_SESSION] [numeric](10, 0) NOT NULL ,
[SRRA_CPT_ANOM1] [tinyint] NULL ,
[SRRA_CPT_ANOM2] [tinyint] NULL ,
[SRRA_CPT_ANOM3] [tinyint] NULL ,
[SRRA_CPT_ANOM4] [tinyint] NULL ,
[SRRA_CPT_ANOM5] [tinyint] NULL ,
[SRRA_CPT_ANOM6] [tinyint] NULL ,
[SRRA_CPT_ANOM7] [tinyint] NULL ,
[rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT
           [DF__SRRA__rowguid__5F94079C] DEFAULT (newid()),
       CONSTRAINT [PK_SRRA] PRIMARY KEY  CLUSTERED
           (
    [SESS_ID_SESSION]
         )  ON [PRIMARY] ,
         CONSTRAINT [FK_SRRA_CPTRS_ANO_SESS_SAN] FOREIGN KEY
         (
    [SESS_ID_SESSION]
        ) REFERENCES [SESS_SANTE] (
    [SESS_ID_SESSION]
     )
          ) ON [PRIMARY]
          GO

SRRA has 9 columns, but you're only inserting 8. Specify DEFAULT for your rowguid column.

INSERT INTO srra
    VALUES      (@num_SessIdSession,
                 @tin_SrraCptAnom1,
                 @tin_SrraCptAnom2,
                 @tin_SrraCptAnom3,
                 @tin_SrraCptAnom4,
                 @tin_SrraCptAnom5,
                 @tin_SrraCptAnom6,
                 @tin_SrraCptAnom7,
                 DEFAULT)


What does MySQL "USE INDEX()" without any arguments do? Does the USE INDEX() have any effect here?

SELECT `posts`.`id` FROM posts USE INDEX() where `posts`.`size` > 1024;

(I have it presently in some SQL because of the way the query is programmatically constructed depending on certain criteria.)


BEFORE TRIGGER on DELETE statement Just a question, based on some current events. Is it possible and useful to set a trigger on MySQL databases, before executing specific DELETE statements?

While having periodically back-ups is a must, I thought it might be useful to make the DELETE statement more safe, by creating a back-up before actually executing the DELETE statement, for example (like a pre-commit in github, might check for syntax issues before actually committing the code).

Is it possible and recommended to have such triggers? Like I have worked with Database migrations earlier, and the problem with rollbacks was actually you executed a DELETE statement on a column and removed the new data which was added as well. I thought it would be nice to have a (partially) back-up of the data which will be deleted.

Any thoughts?

There are many ways.  All "work":

Add a deleted column.  (This may have performance problems in the long run.)
Have the application move the row to another table.  (Poor man's Trigger; error prone.)
Trigger that copies the row to another table.  (Clumsy, slow, needs transaction.)  And, as you ask, what about the deleting the row.
Stored Procedure to "delete" a row.  (Users must call it; need to deny DELETE PRIVILEGE to everyone except the Procedure.)
Application procedure.
My preference for big, multi-user, applications:

I like the "database layer" in the application.  Instead of letting the users perform actual SQL statements, require them to call APIs in application code (PHP/Java/whatever).  This way, changes to the underlying schema can be changed with little or no retraining of the users.

If the API is "Delete(key)", then the "layer" could deal with a new deleted column, or moving the row to new table or calling a stored proc instead of issuing DELETE FROM tbl ....  Etc.

And if I decide that the solution picked is less than perfect, I can change it without even informing the users.


Is it ok to add a table years in a Relational DataBase? How can i explain my coworker that this is unnecessary complexity and redundant data?

He wants to have a table of years because many tables have the 'year' value, and he also want to have relation table with names and years, this would add unnecessary inner joins and Foreign  i insist that this is wrong, i just want to make sure this is not a good practice...

vehicle_year should be datatype YEAR.

Dates should be datatype DATE.  Even if you need to break apart a date into its component parts, it is almost always better to do it that way instead of having a dimension table with dates and their parts.

In general, do not "normalize" any 'continuous' value -- dates, integers, floats, etc.

As kevensky pointed out, there is a use case for going the other direction for "reports" that need to display zero for missing years (or whatever).  But this is not linked in any way to the main tables.  Instead it is used something like

SELECT y.year,
       COALESCE(SUM(m.stuff), 0),
       ...
    FROM Years AS y
    LEFT JOIN my_table AS m
    GROUP BY...

Note how the LEFT JOIN includes all the years from the Years table.  (You may want to limit the range with a WHERE clause.)

And the COALESCE is used to turn NULL for a missing year into 0.  Or N/A.  Or No data.  Or whatever.

While I am at it, I would suggest that normalizing "model" is also "over-normalization".  The model name spelled out is perfectly fine in the Vehicle table.

When should you normalize?

Not unique -- People's names.
When the value is likely to change -- Vehicle owner (but this needs a many:many table)
There is a lot of ancillary data -- Company (with address, etc)
To save space -- the name is long and the table is big; not for 2-letter country_code versus 4-byte INT.

Model year is self-identifying, never changes, not big, has no ancillary data.

Vehicle make and model are mostly similar to model year.  Ditto for engine size, color, price, etc.

Let me branch off into a hypothecal query:  "What years (model_years, that is) did Chevrolet produce their Impala model?"

That could be answered by `SELECT DISTINCT model_year FROM Vehicle WHERE make = ...;".  That gets the answer from the available Vehicles in your table.

Or you might have it from a historical web site that lists the answer.  Now you need a table with PRIMARY KEY(make, model) and various info on the history of old cars.

That leads to a messier situation -- hierarchical info.  Note:  GM > Chevrolet > Impala > LT.  "Locations" have a similar problem:  USA > Georgia > Fulton County > Atlanta > address.  Generally, normalization at each level is gross overkill and should be avoided.

  because many tables have the 'year' value

Well, the 'textbook' argument for normalization fails miserably here.  It says that you should normalize in order to have the value sitting in a single place to make it easy to change.  But if that year represents a vehicle's model_year for one table, but your child's birthday for another table and your graduation in another, you certainly don't want to be changing the value.

Think of the normalization table as an "Entity" such as a person, a place, a company, a picture, a web posting, etc.  You give the Entity a unique identifier (PRIMARY KEY) so that everyone can refer to it easily.  In the table, you have a printable name, a location, a "likes" counter, etc, etc.


Is there a way to throttle MySQL requests to mitigate DoS traffic? I've a server with ~200 wordpress websites hosted. When Google Favicon bot pays us a visit it searches all the sites indexed on our IP, that means it's requesting ~200 favicons. Most of the sites doesn't have one, so the request is passed to wordpress which has to render a 404 page. This means in a matter of seconds dozens of php processes and mysql connections (with different users), which causes a server overload.

I mitigated this by limiting the number of MySQL connection and it partially works, because the server is not crashing anymore, but when this happens it still gets overloaded and unresponsive for some time.

Given that I could either

create an empty favicon.ico file for every site
add a custom htaccess rule for favicon.ico
enable some caching plugin on wordpress

is there a better way of controlling this issue, which doesn't involve touching websites?

UPDATE

Indeed I understand I didn't explain very well why I posted here and not on stackexchange.

I believe the MySQL server can be more fine-tuned in order to avoid overloading the server. As said I already limited the maximum number of connections, but I was wondering what can be done on the databases side, if there's some way to queue or semaphore them, so that when there are 200 users connected and doing basic queries the server can somehow sustain them.

The server is a quad core with 8G of ram, running Ubuntu 12.04. My custom MySQL config:

[mysqld]
table_cache=1024
query_cache_size=64M
join_buffer_size=512K
tmp_table_size=64M
max_heap_table_size=64M
open_files_limit=3200
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
innodb_buffer_pool_size=600M
innodb_thread_concurrency=8
thread_concurrency=8
max_user_connections = 15
max_connections = 80

I usually consider things like this best to be caught as high in the stack as possible on Nginx or Apache level. Wordpess is doing it's job of serving a 404 page for a url that doesn't exist. The lower you get in the stack the harder it gets to distinguish between usual requests and this behaviour. From MySQL it's just a normal legit request from php.

I have this in many of my sites Nginx config:

location /favicon.ico {
    expires 365d;
    return 200;
}

For apache with htaccess you can put a shared favicon.ico (an empty image or something) and this should work (I didn't test this):

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond favicon.ico
RewriteRule .* path/to/shared/favicon.ico [L]


Cannot telnet SQL Server port 1433 I have Windows Server 2008 with SQL Server 2012 Enterprise installed on it.

I disabled the Windows firewall and I'm sure that there isn't another firewall.

I cannot telnet my server.

I enabled TCP/IP, named pipes etc. in the SQL Server Configuration Manager and restarted everything.

By the way I can telnet locally, the problem is telnet via the network.

Any idea why I cannot telnet?

Here's a powershell script I wrote for checking connectivity to SQL servers as you are using the default port (1433) all you need to do is pass in the server name.  If the connection fails the exception should give you a clue as to why.

e.g.
CheckSql.ps1 server\instance

CheckSql.ps1

param([Parameter(Mandatory=$true,ValueFromPipeline=$true)][string]$serverInstance);

$connectionString = "Data Source={0};Integrated Security=true;Application Name=CheckSQL.ps1" -f $serverInstance;
$sqlConn = new-object("Data.SqlClient.SQLConnection") $connectionString;

Write-Host $connectionString

try {
    $sqlConn.Open();
    $sqlConn.Close();
    Write-Host "Connected OK"-foregroundcolor white -backgroundcolor green
}
catch {
    Write-Host $_ -foregroundcolor white -backgroundcolor red
}
finally {
    $sqlConn.Dispose();
}


SSIS: How to check if a record DOESN'T exist in flat file but exists on the database I am working on preparing an SSIS job where I am importing a .CVSV file to OLE DB destination (sql database). We are going to get these files on daily basis. The .CSV file contains records of doctors. Each row represents a doctor. Below image shows how I am able to do this successfully. No problems upto this point.

Here's what I need help with:

If the doctor is no longer active we are going to get the same .CSV file without the record of him/her. How do I check to see if the record is not in .CSV file but it exists in SQL database? I need to update that doctor row in SQL database and update the IsActive field for that row to false.

How do I check to see if the record is not in .CSV file but it exists in SQL database?

Have a staging table (e.g. dbo.tmp_DOCTORS or whatever naming convention that you follow) that will first truncate (everytime you load a CSV, make sure to truncate the staging table) and then import the entire CSV. Then you can update the main table by checking if a particuliar record is in the staging table or not.

This way you get better control of the process and only update the main table or add new records to the main table. You can do it using TSQL (Merge) or SSIS.

This is what we do and it incurs less overhead on the main table, since you will update or add only records that have changed.

Also, I really like the idea of doing a soft delete IsActive = TRUE or FALSE. This way you can preserve the history as well.


Get date based on interval days in sql I have a table containing range table like this.

DayFrom Dayto Frequent
60      46    3     = interval 15 days (15/3 = Every 5 days send email for 3 times)
45      37    3     = interval 9 days (9/3 = Every 3 days send email for 3 time)
36      31    6     = interval 6 days (email will sent everyday)

I will sent email based on that table. The case is, there will be an Event/Promotion that start on 1 June 2016. I want to get the date based on that range becoming 1 row. When 2 April = GETDATE() the email will send, the email will send again on 7 April and so on until the range on Range table is finished.

a)  46-60:
1st: 2, April
2nd: 7, April
3rd: 12, April

b)  37-45:
1st: 17, April
2nd: 20, April
3rd: 23, April

c)  31-36:
1st: 26, April
2nd: 27, April
3rd: 28, April
4th: 29, April
5Th: 30, April
6th: 1, Mei

I'm just a newbie to select like in this case.. Usually i'm just query for normal case :(
I've tried the following query. But the frequent is not work.

select h.DayFrom ,h.DayTo ,h.Frequent ,
       ( h.DayFrom - h.DayTo + 1 ) AS AlarmDay ,
       (( h.DayFrom - h.DayTo + 1 ) / h.Frequent ) AS ReminderTimes ,
       ( F.PromotionFrom - ( DATEDIFF(d, GETDATE(), F.PromotionFrom) ) ) AS ReminderDate  from PF F INNER JOIN PendingApprovalDetail H ON F.SBU = H.SBU AND H.DocumentType = 'PF'
AND DATEDIFF(d,GETDATE(),F.PromotionFrom) BETWEEN H.DayTo and H.DayFrom

I base my idea on generating the records for each interval.For this I use master..spt_values.( you can use anything else, that generate a list of numbers 0,1,2...).

So , for interval 60-46, generate 3(frequency) records.Those records have the starting point = the event date - 60 days. (This is the date when you start to send emails)

DECLARE @dtCurrentDT AS DATETIME ;
SET @dtCurrentDT = '20160402';
--SET @dtCurrentDT = '20160403';

WITH EventsCTE AS
( SELECT '20160601' AS EventPromotion)
,RemindersCTE AS
(SELECT 60  aS DayFrom,46 AS Dayto, 3 AS Frequent UNION ALL
 SELECT 45,37,3 UNION ALL
 SELECT 36,31,6)

SELECT
    R.DayFrom
    ,R.Dayto
    ,R.Frequent
    ,DATEADD(DAY,
            ((R.DayFrom-R.DayTo + 1)/ R.Frequent) * CA.number ,
            DATEADD(DAY,-R.DayFrom,E.EventPromotion)) AS StartDateEvent_ToSendMail
    --,CA.number
    --,DATEADD(DAY,((R.DayFrom-R.DayTo + 1)/ R.Frequent) * CA.number , @dtCurrentDT) AS RemainderDay
    --,DATEADD(DAY,-R.DayFrom,E.EventPromotion) AS Event_StartDate
FROM
    RemindersCTE AS R
    CROSS APPLY
    (SELECT TOP(R.Frequent)
        V.number
     FROM
        master..spt_values AS V
     WHERE
        V.type ='P'
     ORDER BY
        V.number ASC
     )CA
     CROSS JOIN EventsCTE AS E
WHERE
    DATEADD(DAY,
            ((R.DayFrom-R.DayTo + 1)/ R.Frequent) * CA.number ,
            DATEADD(DAY,-R.DayFrom,E.EventPromotion))
    >= @dtCurrentDT;

My output is :

DayFrom     Dayto       Frequent    StartDateEvent_ToSendMail
60          46          3           2016-04-02 00:00:00.000
60          46          3           2016-04-07 00:00:00.000
60          46          3           2016-04-12 00:00:00.000
45          37          3           2016-04-17 00:00:00.000
45          37          3           2016-04-20 00:00:00.000
45          37          3           2016-04-23 00:00:00.000
36          31          6           2016-04-26 00:00:00.000
36          31          6           2016-04-27 00:00:00.000
36          31          6           2016-04-28 00:00:00.000
36          31          6           2016-04-29 00:00:00.000
36          31          6           2016-04-30 00:00:00.000
36          31          6           2016-05-01 00:00:00.000


Migrating 300 GB mysql database from one server to another cleanly with minimum downtime I have a 300 GB mysql database which i want to migrate to another server in order to setup Master-Master replication between the two and my main aim is to achieve this with least possible downtime.

My database has only one table around 30GB where inserts are happening round the clock. All the other tables are history tables(static).

1) What will be the best way to go forward in this case?

Taking mysqldump of the whole database and transferring the dump to
other server and importing it to the new server will do the job i want to achieve but will take a lot of
downtime (maybe more than 14hrs) which is not permissible.
Or can i take individual table dumps for the static tables without stopping mysql  and import it to new server ,and after all this is over, take downtime for the single active table so that no new inserts will happen and both my databases will be in sync? First of all is it possible to do such a thing ? and if possible what issues it might present while setting up a master-master replication?
Or is there any other method to do this with very less downtime?

This solution can be done with mysqldump but with a bit of a risk

For the sake of this example, suppose you have the following:

The database name is mydb
The source DB server's private IP is 10.20.30.40
The target DB server's private IP is 10.20.30.50
User is root on both source and target DB Servers
Password is whatever on both source and target DB Servers
Binary Logs on both servers are named mysql-bin

Here are your steps

STEP 01 : Create Replication User

On the Live Master, run the following

CREATE USER repluser@'%' IDENTIFIED BY 'replpass';
GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO repluser@'%';

STEP 02 : Set Up Replication

On the target DB Server, run the following:

If you have GTID enabled on source and target servers, do this:

CHANGE MASTER TO
master_host='10.20.30.40',
master_port=3306,
master_user='repluser',
master_password='replpass',
master_auto_position=1;

If you do not have GTID enabled on source and target servers, do this:

CHANGE MASTER TO
master_host='10.20.30.40',
master_port=3306,
master_user='repluser',
master_password='replpass',
master_log_file='mysql-bin.000001',
master_log_pos=4;

STEP 03 : Create a script to perform live mysqldump and load

Create a shell script called live_dump_and_load.sh

Put the following lines in it

MYSQL_USER=root
MYSQL_PASS=whatever
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB_TO_DUMP=mydb
MYSQLDUMP_OPTIONS="--routines --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single_transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} -B ${DB_TO_DUMP}"

date > live_dump_and_load.runlog
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} | mysql -h10.20.30.50 ${MYSQL_CONN}
date >> live_dump_and_load.runlog

STEP 04 : Run the shell script

chmod +x live_dump_and_load.sh
nohup ./live_dump_and_load.sh &

STEP 05 : Monitor the run log

watch cat live_dump_and_load.runlog

STEP 06 : Start Replication

When the dump is loaded onto the target server, go to the target server and run

START SLAVE;

GIVE IT A TRY !!!


how to use dual foreign key in oracle 10g I have created two tables

create table ref1(id varchar2(3) primary key);
create table ref2(id varchar2(3) primary key);

Now I want to create a third table with a single column as foreign key for both ref1.id and ref2.id

create table actual(p varchar2(3) );

How can i do it?

You could try this:

CREATE TABLE ref1
(
    id VARCHAR2(3) PRIMARY KEY
);

CREATE TABLE ref2
(
    id VARCHAR2(3) PRIMARY KEY
);

CREATE TABLE look
(
    p VARCHAR2(3),
    CONSTRAINT fk_p_ref1 FOREIGN KEY (p) REFERENCES ref1(id),
    CONSTRAINT fk_p_ref2 FOREIGN KEY (p) REFERENCES ref2(id)
);

Tested on a 10G R2 server with no complaint (yet).

Edit:

Simple test result:

SQL> insert into ref1 (id) values ('abc');

1 row created.

SQL> insert into ref2 (id) values ('def');

1 row created.

SQL> insert into look (p) values ('abc');
insert into look (p) values ('abc')
*
ERROR at line 1:
ORA-02291: integrity constraint (LINEQZ.FK_P_REF2) violated - parent key not
found

SQL> insert into ref2 (id) values ('abc');

1 row created.

SQL> insert into look (p) values ('abc');

1 row created.


MySQL- Best practices for using rollback What is the best practice / recommended way of using ROLLBACK operation, if any DML operation fails ?

Any ROLLBACK, COMMIT mechanism ?

FYI: I'm using InnoDB tables which complies with Atomicity.

There are two ways not to commit a transaction

Explicit ROLLBACK
Close a connection w/o COMMIT

Both of them are fine to use depending on what fits better the app logic.

When to rollback explicitly?

If you want to reuse a connection. If you want to make transactions as short as possible (a good thing to aim for).

When to close the connect w/o commit?

If a transaction is one-time task, and you'd like to simplify error handling.

Few things to consider.

Do not keep uncommitted transactions for long time.  Often that happens when the app opens a trx, goes to do something else, comes back later to complete and commit the transaction. This really hurts. It leads to high CPU usage and kills performance.
ROLLBACK usually goes into except ... block (or whatever applies to the specific language). But ROLLBACK itself may fail, so make sure to handle exceptions correctly to avoid "Failed to rollback a transaction" errors.
ROLLBACK often takes more time than you'd expect. If transaction was large, the rollback takes more time than all the previous statements in the transaction. Make the transactions as short as possible.


Why is the page life expectancy 0 (zero) on SQL Server 2005 We have been wanting to know the page life expectancy for our SQL Server. So we look at the counter using Perform. The value is ZERO and never changes. I thought there must be some bug, so I checked the SQL Server DMV query

SELECT [object_name],[counter_name],[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

That also returns zero, all day long.

To make this more interesting, we checked the 'buffer cache hit ratio' counter, with it averaging around 99-100.

So how is the page life expectancy zero while the 'buffer cache hit ratio' is 100?

What are we missing? if it's always zero, it would mean to me that nothing stays in the buffer cache, which seems incorrect if the buffer cache hit ratio is 100?

Thanks in advance

I would start with doing the steps outlined here:

How to rebuild the performance counters of a SQL Server 2005 or 2008 instance

Update August 2015: Above link appears broken. Alternative reference:

Troubleshooting: SQL Server (2005, 2008) Performance Counter Collection Problems (CSS)

Essential steps from the linked resource

Using an elevated administrator command prompt perform the following steps.

Change the path to the BINN directory of the SQL Server instance you desire to correct.
(Ex: C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn)
Execute unlodctr <<REGISTERED SERVER NAME>>
For example: unlodctr MSSQL$SQL2008 or SQLAgent$SQL2008 ...
Execute lodctr /T:<<perf-sql* matching the counters you desire to load>>
For example: perf-MSSQL$SQL2008sqlctr.ini or perf-SQLAgent$SQL2008sqlagtctr.ini for SQLAgent. The /T is important to load the SQL Server performance counter provider as a trusted provider.
Cycle the remote registry service:
net stop "Remote Registry" then net start "Remote Registry"
Force a WMI synchronization using winmgmt /resyncperfctr "<<PID>>"
where PID is the process id of the WinPriv.exe (you can get this from Task Manager)

Additionally, from this resource, the following may also be required:

Make sure that the correct security permissions have been granted to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009
registry key:

Grant Full Control permissions to the Creator Owner account.
Grant Full Control permissions to the Administrators account.
Grant Read permissions to the SQL Administrators on that box/Node.
Grant Full Control permissions to the System account


Records creates double entry, should only be one Need an assist on a database design.

I'm creating a system to track students in a course.

Currently, I have courses.  Courses are made up of modules. A given module can be in multiple courses (eg 2 courses have the same module, so students from both course A and course B are in module Z.

Students are enrolled in modules.  So student john gets enrolled into all the modules that makeup course A.

However, when I put john into module Z, he shows up in course A and course B, even though he should only show in course A.

Currently, I have a student table, a module table, and a course table, linked together with a studentinmodule and moduleincourse table (both many to many).

Any insight into how to redesign so a module can be in multiple courses, but a student enrolled in a module only shows up in one course?

If you link Student-->Course-->Module, you would avoiding having Module Z causing both Course A & B to show up for that student.

Alternatively, you could slightly modify the current to have Student-->(Module & Course). If your Enrollment table includes all three identifiers, it would be trivial to determine if the student is enrolled in Module Z because they are also enrolled in a specific class, or because they are enrolled in the module directly, rather than via a class.


Facing permission error while trying to execute a stored procedure published through CLR I've created a stored procedure using SQL CLR c# stored procedure in Visual Studio 2017 and I've published it in MSSQL Server. Now I can see my stored procedure, but I face this error when I want to execute the procedure.
  The error is:

  Msg 6522, Level 16, State 1, Procedure cust_diff, Line 0 [Batch Start
  Line 2] A .NET Framework error occurred during execution of
  user-defined routine or   aggregate "cust_diff":
  System.Security.SecurityException:
  System.Security.Permissions.SecurityPermission
  System.Security.SecurityException:  at LevenshteinDistance.cust_diff()

I've added these two at the beginning of my code in C#

using System.Security;
using System.Security.Permissions;

and also this is part of my code that I use to create the procedure:

 [Microsoft.SqlServer.Server.SqlProcedure]
 [PermissionSet(SecurityAction.Assert, Unrestricted = true)]
 public static void cust_diff()
 {

    // Put your code here
    LevenshteinDistance ld = new LevenshteinDistance();
    ld.Method1();
 }

but despite using these two, I cannot execute my procedure in SSMS.

This setting must be enabled on the database in order to allow CLR procedures to run.  Query this setting which should show a value of 1

SELECT name,
CAST(value as int) as value_configured,
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name ='clr enabled';

If it is not then using a login with dba privileges run this

EXECUTE sp_configure 'clr enabled',1;
RECONFIGURE;

It would be advisable when creating the assembly to mark what access it needs.  From the documentation an example

CREATE ASSEMBLY SQLCLRTest
FROM 'C:\MyDBApp\SQLCLRTest.dll'
WITH PERMISSION_SET = SAFE;

The choices of access are SAFE (most restrictive), EXTERNAL_ACCESS and UNSAFE (least restrictive).  What to mark the assembly depends on what it accesses and where the resource is located.

If your assembly requires EXTERNAL or UNSAFE access such as the ability to access local data or native code then you are also required to do either of these:

the assembly is signed with a certificate and has a login with EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission. This is preferred as it restrict permissions to the lowest level: one user
database owner has EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY

From a security audit perspective using CIS standards CLR assemblies are always flagged for further review.  This does not mean you should not do this, just that there should be no other way of accomplishing what is required.


One connection vs multi connection I'm redesigning my client's website in order to add about 16 mini sites for their web site. It's a website using PostgreSQL. And my question is:

Performance wise, what is the better database design:

1 (one) database, with about 190 tables, or
17 databases, one with 30 tables, and the rest with about 10 tables each.

It's for a low to middle traffic web site, but 4 or 5 times a year the traffic will be high.

It's a Centos 6 VPS, with 3 GB of RAM. Other softwares are Apache and PHP.

In postgreSQL, database is a logical concept and a PostgreSQL server manages one database-cluster which contains all databases.
The number of databases does not affect to the server performance.

I recommend that you divide databases at the viewpoint of logical structure. If your system can be completely divided into each mini site, you may create databases corresponding to the sites. (When the access of one site increases, you can divide and relocate the site to  other server.)


Improve select query performance The following select query from my application is taking more than 200ms. The table has around 2 million rows. Is there any ways to improve the performance of this query ?

select count(id) from table1 where status='A' and empID=123

Table Indexes

PRIMARY KEY (`id`),

KEY `empID` (`empID`),
KEY `status` (`status`),
KEY `testID` (`testID`),

CONSTRAINT `fk_1` FOREIGN KEY (`empID`) REFERENCES `emp_info` (`id`)

A composite index on (status, empID) will be faster.  It might be better as (empID, status) depending on the cardinality of these two columns.  An EXPLAIN would have shown you that it was only using one value against one index.


Which AWS database services are designed for storing logging records like log4j or log4perl? I have been using the file system for log4j and log4perl records and want to start using a database to store the information, especially to lookup records organized by pid. We are using MySQL RDS for most of our databases, so my initial reaction is to continue using so (or experimenting with Aurora RDS), but RDS requires InnoDB for snapshots and I would have preferred MyISAM for speed.

I prefer to keep 30 days of records, at 135M records a day. This could increase by 10x. The most important thing here is speed of inserts, although I would like to be able to retrieve by date or pid within 5 minutes.

RDS services are relational databases, so the retrieval method I require is a good match, but it isn't designed for the huge amount of data being inserted without constraints.

Are SimpleDB or DynamoDB designed for logging records more so than MySQL or Aurora?

Since you're looking at storing and querying 135,000,000 * 30 * 10 records that likely wouldn't benefit from traditional RDBMS features, I think Hadoop would be the way to go.

My experience is exclusively with Microsoft Azure - if you're not attached to Amazon you might check it out. Either way, Hadoop is open source so the majority of operations and activity should be the same regardless of your platform provider. I'd go with an option that allows you to test the performance of different cluster sizes so you're only paying for what you really need (especially with the 10x increase as an unknown right now).

The following link, despite its Azure focus, gives you a good tutorial of how to query log4j records with Hive. Amazon EMR appears to have great documentation.

Key points for your situation:
Create a table structure over your files:

    CREATE EXTERNAL TABLE log4jLogs
    (t1 string, t2 string, t3 string, t4 string, t5 string, t6 string, t7 string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ';'
    STORED AS TEXTFILE LOCATION 'yourdrive:///logs/';

With the table defined you can then query similar to SQL:

    SELECT t4 AS sev, COUNT(*) AS ErrorCount
    FROM log4jLogs
    WHERE t4 = '[ERROR]'
    AND INPUT__FILE__NAME LIKE '%.log' GROUP BY t4;

With the EXTERNAL definition you can have multiple files and the table will include them all, so just drop them in the appropriate directory. If you're keeping 30 days worth you may want to get fancier and separate days into different folder structures/partition your Hive table.


Critical tablespace usage UNDOTBS (98%) This tablespace is in my case 8GB. I'm on oracle 10g and got critical warning:

Tablespace usage critical: UNDOTBS (96%)
I also executed this command:

select sum (bytes/1048576) MB
FROM
DBA_SEGMENTS
WHERE
tablespace_name = 'UNDOTBS' ;
It gives me 7625,625MB.

Could I extend this tablespace with this command:

ALTER TABLESPACE "UNDOTBS" ADD DATAFILE '/path/to/ts.db' SIZE 4194304000;
Can I do it on running system and wont I destroy anything?

Most probably this is fake alarm. the space UNDO tablespace is allocated differently then on other tablespaces. Oracle simply does not clean out old versions of block unless necessary. There are no real data in this tablespace.

You UNDO space was really full you would get ORA-01555 Snapshot too old, or other types of errors. Check the view v$undostat.


Error: "A slave with the same server_uuid/server_id as this slave has connected to the master" I am testing a node.js client which connects to a mysql DB (mariadb). It works fine on its own, but if I launch this client simultaneously on another host, the first instance gets disconnected with the following error. I have read some posts suggesting to change the server-id in my.cnf, but that didn't help. Besides, why would I need to change the id of the server since both clients are pointing to the same server?

{ Error: UNKNOWN_CODE_PLEASE_REPORT: A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'mysql-b
in.000024' at 334, the last event read from 'mysql-bin.000024' at 256, the last byte read from 'mysql-bin.000024' at 334.
    at Binlog.Sequence._packetToError (/home/myapp/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Binlog.Sequence.ErrorPacket (/home/myapp/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:17)
    at Protocol._parsePacket (/home/myapp/node_modules/mysql/lib/protocol/Protocol.js:278:23)
    at Parser.write (/home/myapp/node_modules/mysql/lib/protocol/Parser.js:76:12)
    at Protocol.write (/home/myapp/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/home/myappp/node_modules/mysql/lib/Connection.js:91:28)
    at Socket.<anonymous> (/home/myapp/node_modules/mysql/lib/Connection.js:502:10)
    at Socket.emit (events.js:182:13)
    at addChunk (_stream_readable.js:283:12)
    at readableAddChunk (_stream_readable.js:264:11)
    --------------------
    at Protocol._enqueue (/home/myapp/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Immediate._start (/home/myapp/node_modules/@rodrigogs/zongji/index.js:234:31)
    at processImmediate (timers.js:632:19)
  code: 'UNKNOWN_CODE_PLEASE_REPORT',
  errno: 4052,
  sqlMessage:
   "A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'mysql-bin.000024' at 334, the last event
 read from 'mysql-bin.000024' at 256, the last byte read from 'mysql-bin.000024' at 334.",
  sqlState: 'HY000' }
{ Error: Connection lost: The server closed the connection.
    at Protocol.end (/home/myapp/node_modules/mysql/lib/protocol/Protocol.js:112:13)
    at Socket.<anonymous> (/home/myapp/node_modules/mysql/lib/Connection.js:97:28)
    at Socket.<anonymous> (/home/myapp/node_modules/mysql/lib/Connection.js:502:10)
    at Socket.emit (events.js:187:15)
    at endReadableNT (_stream_readable.js:1094:12)
    at process.internalTickCallback (internal/process/next_tick.js:72:19) fatal: true, code: 'PROTOCOL_CONNECTION_LOST' }

@Michael-sqlbot hit it on the nail. Following his suggestion I reviewed the documentation @rodrigogs/zongji and sure enough there is an option to set the serverId. Like so:

const instance = new MySQLEvents(DBcon, {
        startAtEnd: true,
        serverId: 1,
        excludedSchemas: {
                mysql: true,
        }
});


Refactor SQL to left join on TVF requiring input variable I would like to join two tables and a table-valued function. The TVF take a date as input variable.

The expected output is:
Date, LimitName, Exposure, ProductValueEligible, ProductValueNonEligible

The Tables:

TABLE_A: Date, LimitName, LimitValue, Limit >> main table that
specify limits I want to check
TABLE_B: Date, LimitName, ProductType (one-to-many) >> helper table that specify which products types map into each limit
TABLE_C (TVF): (several identification columns, like ID value, dates) + Date, ProductType, ProductValue, IsProductEligible >> the
main product data to be aggregated and mapped into the limits

I am able to get the expected result using a query as below.

However, because the TVF takes a date as input variable, this query requires specifying the date 3 times. I would like to refactor the query to specify the date once, and have that condition applied to the TVF.

Reading up on this, I think that cross apply and outer apply should be able to do this, but I cannot seem to get syntax that works. I end up having to put where conditions in place, which drops rows (works like inner join rather than left join). I would appreciate some direction on how to make this work.

select
  a.Date
, a.LimitName
, sum(c_elig.ProductValue) as ProductValueEligible
, sum(c_non.ProductValue) as ProductValueNonEligible

-- TABLE_A: Date, LimitName, LimitValue, Limit
from Limits a

-- TABLE_B: Date, LimitName, ProductType (one-to-many)
left join LimitsProductTypes b
on b.Date = a.Date
and b.LimitName = a.LimitName

-- TABLE_C (TVF): <identification columns, like ID value, dates> + Date, ProductType, ProductValue, IsProductEligible
left join (
    -- Find eligible rows, and group by Product
    select Date, ProductType, sum(ProductValue) as SumProductValue
    from Function_DataSet('2019-05-15') --<------------------------------------- TVF, takes a date input
    where IsProductEligible = 'TRUE'
    group by Date, ProductType
) c_elig
on c_elig.Date = a.Date
and c_elig.ProductType = b.ProductType

left join (
    -- Find non-eligible rows, and group by Product
    select Date, ProductType, sum(ProductValue) as SumProductValue
    from Function_DataSet('2019-05-15') --<------------------------------------- TVF, takes a date input
    where IsProductEligible = 'FALSE'
    group by Date, ProductType
) c_non
on c_non.Date = a.Date
and c_non.ProductType = b.ProductType

where a.Date = '2019-05-15'   --<------------------------------------------- want to just specify date here

group by a.Date, a.LimitName

Use the OUTER APPLY operator to join your TVF to your tables and still return the NULL records as well.

NOTE: In the below sample your NULL values might be excluded because there is no NULL handling in the WHERE clause, but without sample data or DDL it was difficult to write this portion.

SELECT
    a.[Date],
    a.[LimitName],
    SUM(c_elig.ProductValue) as ProductValueEligible,
    SUM(c_non.ProductValue) as ProductValueNonEligible,
FROM Limits a
LEFT JOIN LimitsProductTypes b ON b.[Date] = a.[Date] AND b.LimitName = a.LimitName
OUTER APPLY Function_DataSet(a.[Date]) c_elig
OUTER APPLY Function_DataSet(a.[Date]) c_non
WHERE a.Date = '2019-05-15'
    AND (c_elig.IsProductEligible = 'TRUE'
    AND c_elig.Date = a.Date
    AND c_elig.ProductType = b.ProductType)
    AND (f2.IsProductEligible = 'FALSE'
    AND c_non.Date = a.Date
    AND c_non.ProductType = b.ProductType)
GROUP BY a.Date, a.LimitName


MySQL can't login and/or reset password There are many similar questions, but none of them helped me.

I had been using MySQL on Windows with root/root credentials for a while, but since lately, I'm not able to login

> mysql
ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)

> mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

> mysql -uroot -p
Enter password: ****
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

> mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I've tried to reset root password: (in admin console)

> cat C:\mysql-init
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

> mysqld --init-file=C:\mysql-init --console

but there is an error line:

2015-12-17T10:34:14.616701Z 1 [ERROR] 1131  You are using MySQL as an anonymous user and anonymous users are not allowed to change passwords

Enable skip-grant-tables in my.ini and restart MySQL. Now root can login without password so that you can reset your password:

update mysql.user set password=password('newpassword') where user='root';


Postgres JSON field for many to many relationships I'm designing a schema that has a many-to-many relationship as such:

A lead can have several stages and each stage can have several leads. I currently have three tables, with one of them as being a junction table.

stages
----------
id | name | workflow_id | created_at | updated_at

leads
----------
id | name | created_at | updated_at

stage_leads
--------------
id | stage_id | lead_id | created_at | updated_at

(stages belong to a workflow)

I've got indices set up.

**

When a lead moves from one stage to another, a new entry is created in the stage_leads table. My use cases seem simple:

a) find/count all leads on a particular stage, and

b) find/count all stages a particular lead has gone through

My concern is that since every move is creating a new entry into the junction table -- and there are potentially hundreds of stages a lead can go through -- it may lead to hundreds of thousands of rows for very few leads and that could probably impact the lookup time when I'm trying to fetch a specific set.

My question is this: would it be better to use Postgres' JSON functionality to store my data something like this:

stages
----------
id | name | workflow_id | created_at | updated_at

leads
----------
id | name | current_stage_id | stages | created_at | updated_at

... where the stages column on "leads" is a JSON object that logs the stage_ids that the lead has gone through, the timestamps, and a couple of other fields?

This seems to satisfy my use case of finding all leads on a particular stage (I can run a query against the current_stage_id of all leads) and it probably makes retrieving the stages for a particular lead easier. I'm not very confident about it though. I realize I'd be giving up on things like constraints, etc.

Are there any downsides to an approach like this?

would it be better to use Postgres' JSON functionality to store my data something like this:

No.

  Are there any downsides to an approach like this?

GIN / GiST indexes on jsonb are bigger and slower than b-tree indexes. Additionally, whenever you update a field it must be rewritten, you can't just insert an extra element inside the json document in the row.

  My concern is that since every move is creating a new entry into the junction table -- and there are potentially hundreds of stages a lead can go through -- it may lead to hundreds of thousands of rows for very few leads and that could probably impact the lookup time when I'm trying to fetch a specific set.

This is what RDBMSes are good at.

Use the conventional relational modelling for this unless you have a really good reason not to.


How to use WITH inside IF statement in PostgreSQL I am writing a function that accepts a text flag based on which I  need to run some code. So I am using an IF clause. However the code that has to execute inside IF and ELSE is a recursive CTE which has different joins for the different text flags. Following is sample that I am trying to write but getting an error. I tried writing the code that starts with WITH and applying the IF with the final SELECT statement but it also gave the same error which is "syntax error at or near "IF"".
How can I effectively use IF in this function?
CREATE OR REPLACE FUNCTION public.get_something(myTextFlag VARCHAR(20)) RETURNS TABLE
(
rNum BIGINT,
Colmn1 Text
)
AS $body$

IF (myTextFlag = 'FirstPart') THEN
   WITH recursive cte_1 AS
(
code with table 1 and 2 and cte_1
)
Select some_columns from cte_1 and table 10, 11 and 12;

ELSE

   WITH recursive cte_2 AS
(
code with table 1,2,3 and 4 and cte_2
)
Select * from cte_2 and table 10, 11, 13, 14;

END IF;

$body$
LANGUAGE SQL
SECURITY DEFINER
 STABLE;

IF does not exist in the SQL language (which is why you get a syntax error) but it's available in plpgsql, the procedural language.
With plpgsql, you could write something like
CREATE OR REPLACE FUNCTION public.get_something(myTextFlag VARCHAR(20)) RETURNS TABLE
(
rNum BIGINT,
Colmn1 Text
)
AS $body$
BEGIN
IF (myTextFlag = 'FirstPart') THEN
  RETURN QUERY <insert your 1st query here>
ELSE
  RETURN QUERY <insert your 2nd query here>
END IF;
END
$body$
LANGUAGE plpgsql
SECURITY DEFINER
 STABLE;

Your 1st and 2nd queries must return the same structure that also must match the function definition, but aside from that, they can be completely different inside.


SUM same column twice performance DBMS: SQL Server 2012 Express

I have an SQL Statement which looks something like this:

SELECT

    OverSpeedings = SUM(OverSpeeding),
    OverspeedingPoints = SUM(OverSpeeding) * 5,

FROM
    MyTable

GROUP BY
        COLUMNS_HERE

I was wondering does Adding the same column twice cause an impact on Performance? Meaning does SQL Server add the column twice? Or will it add the column once and use for both columns in the result set?

Or should I use a CTE with one SUM like the following:

SELECT

    OverSpeedings,
    OverspeedingPoints = OverSpeedings * 5,

FROM
    (
    SELECT

        OverSpeedings = SUM(OverSpeeding)

    FROM
        MyTable

    GROUP BY
        COLUMNS_HERE
    ) A

was wondering does Adding the same column twice cause an impact on Performance? Meaning does SQL Server add the column twice? Or will it add the column once and use for both columns in the result set?

Sum will be calculated once and result is used for multiple columns referring the same sum

Below is sample setup from my instance ,which will prove the same

select
sum(empid) as empidsum,
sum(empid)*2 as empidsum,
sum(empid)*4 as empid4
 from orders

execution plan:

In the above execution plan,highlighted computed scalar shows below

Scalar Operator([Expr1003]*(2))
Scalar Operator([Expr1003]*(4))

Where Expr1003 is the sum calculated by previous compute scalar

  should I use a CTE with one SUM like the following

CTE is just for readability,using a CTE won't help performance .

There are cases where materialzing CTE will help in improving the query.Look out this answer for more details:

Which are more performant, CTE or temporary tables?


Database design for stores with and without branches I am building a database with primarily these two kind of stores:

a) Simple small family stores

b) Big and many branches stores

Those two kinds of stores are represented in a Stores table; with the b) case I'm grouping them by same store with the Branches table, something like this:

Stores table   -> 'The Apparel Store'; IDStore 1
Branches table -> 'California Branch'; IDBranch 1; IDStore 1
Branches table -> 'Miami Branch'; IDBranch 2; IDStore 1
Branches table -> 'Texas Branch'; IDBranch 3; IDSTore 1

The problem for me is, is this a good approach for the single and small businesses? i.e. an Ice Cream family store in a neighborhood; I did think about just having these kind of stores in the Stores table with just one record in Branches table, but I'm worried about the simplicity of the design.

Simplicity in the sense that, I will have thousands of single little stores, they are just perfectly representated with only the Stores table. With that being said, also there is a small possibility the little ice cream store have another branch, you never know.

Is this the most simple yet flexible design?

What you want to think about is what attributes you would track at each of the levels ("store" and "branch")?

You don't want to repeat information in multiple places or have ambiguity around which table a certain type of data is stored.

I'd suggest that you probably need to think a little bit differently.  Instead of "store" and "branch" how about "organization" and "location"?  It's a subtle, but maybe an important distinction.

The location (aka branch) table would have information (columns) about where a business is conducted and perhaps how to contact them (branch phone number, for example).  You might also hand location-specific information off of this table.  Not necessarily just by having columns on the "location" table, but also by having relationships that refer to the location table.  Things that occur to me in this vein might include store hours, inventory and sales, for example.

At the organization level you might have more broadly based information and relationships. Things like vendors, employees, and the like.

The basic idea is to split data attributes (and relationships) in a sensible way so that the things in one table belong with other things in that table.  You want to avoid thinking about trying to model the same thing in two different ways because they operate at two different scales.  You asked about simplicity. The simplest data model is the one that you apply consistently.


Create MySQL trigger that updates position of each row when sorted by a specific field Let's see an example, I've got this table that contains teams, each of them having a certain number of points:

CREATE TABLE teams (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  points INT(11) NOT NULL,
  position INT(11),
  PRIMARY KEY (id)
);

In the position field, I want to have their current position in comparison with other teams, so for example I've got 3 teams, sorted by the position:

id  name   points  position
2   team1  1700    1
3   team2  1500    2
1   team3  1300    3

Every time teams table is updated, I want to update the position. For example, I update the team2 with 500 points so it has 2000 points now and is first:

id  name   points  position
3   team2  2000    1
2   team1  1700    2
1   team3  1300    3

So how do I update position field every time points are updated? My idea was to create a trigger as follows:

delimiter |

CREATE TRIGGER updatePoints AFTER UPDATE ON teams
  # How to go over each row (not only updated rows) and change each row's position (rank) when teams are sorted by points?
  BEGIN
    # And here code to update the points

  END;
|

delimiter ;

However I have no idea how to create a trigger that will update positions correctly when the table is sorted by points.

EDIT: The idea is to create a trigger that will update the team by assigning correct position to each team in order to be able to retrieve the position of a specific team just by doing:

SELECT position FROM teams WHERE id = 1

You (as in, the user) won't generally sort data within a table.  You (as in, a user) will sort the output from queries.

The order that the data is stored is generally irrelevant to you, and the database will store the data in a way that makes sense for it.

If you want/need the data ordered when you run a query, put the order by statement in the query...

select x,y,z
from table1
order by x asc, y desc


Need Expert suggestion for choosing best DB scalability I am looking best opinion on choosing best DB scalability based on below requirement.

Application
App install tracking platform (Ad serving).
It's used to track click, impression, app install.

Framework
Spring Boot, Java 8 with Tomcat 9

HAProxy
Application Load Balancing.
At present there are 3 nodes connected to it.

Database
MySQL

We are expecting 1000 clicks per sec (it can go up to 10,000-100,000 clicks per sec). So please let me know best solution to active it.
As of now I am little confused which one I used to go either:

MySQL Master Master Replication
MySQL Master Slave
MySQL Cluster

Please assume we have 85% read and 15% write. Read is for checking campaign daily, Monthly or total conversion capping before redirecting clicks to landing page.
Also please suggest whether MySQL is best fit for these type of requirement or not. If not which one is best.
Also please highlight what will be the:

Backup Strategy
Future table or row adding
Network Failure

Will you be INSERTing one row per click?  Or just incrementing a counter?

INSERTing or UPDATEing as many as 100K rows in one second requires some special planning.  First, plan on gathering a bunch of rows, then batching the insert or update; do not try to insert/update one click at a time.  (More on this in a minute.)

Build and maintain "summary tables" so that the queries will not have such a significant impact the system.  (More to come.)

I would suggest a Galera Cluster (MariaDB 10 / PXC / Mysql+Galera) to give you some scaling, HA, etc.  Since you have a large read requirement, you might need multiple slaves hanging off each cluster node.

Backups could be handled by taking a slave offline.  (There are other options; I don't see it as much of a problem.)

You have not explained the 85% reads enough for me to finish this discussion; I will focus on the writes and some of the reads.

Use SSDs of sufficient size.  Have sufficient network capacity.

Have enough client machines -- possibly more than MySQL servers; the number depends on several things.  The design should easily grow by adding clients, so I am not too worried about how many clients until the system is being prototyped.

The best high-speed ingestion I know about is here .  It allows multiple clients to insert into a temp table, ping-pong to another table while doing the INSERTs (or UPDATEs).  If all you are doing is counting hits, this design takes a load off the main table because it would do some of the tallying in the tmp table.  Also, you would probably use INSERT ... ON DUPLICATE KEY UPDATE ... SELECT ... to move the data from the temp to real table.  (Caveat:  The blog was written with a single Master in mind; I need to do some research to tailor it to Galera, especially if this is happening on all 3 nodes.  Initial thoughts suggest that only minor tweaks are needed.)

Normalization can, and should, be done from the tmp table.  (The blog discusses that.)

With Summary table(s), the 85% estimate for reads may drop significantly.  The idea is to create and maintain tables that are subtotals over an hour or day.  Then the queries actually hit the summary tables and run a lot faster than against the real table.  (Sometimes 10x faster.)  The summary table(s) may be denormalized for extra convenience/speed.  The Summary table updates are likely to be IODKU from the tmp table.

I need to see more details about the schema and the tentative inserts/updates and selects.  This entire discussion will probably expand beyond what can/should be handled in a free forum.

Reference: Summary table blog


Retention policy of archive/ I have archive_mode = on and archive_command = 'cp %p /var/lib/postgresql/11/main/archive/%f'

What is retention policy of files under archive/? Will pg clean up it periodically or let it pile up until administrator panic and get involved?

The retention policy is whatever you decide on and then implement.

Putting this under your control is what archive_command is for.  If you don't want to be in control and want PostgreSQL to do it for you, then use replication slots or wal_keep_segments instead.


ORA-00979: not a group by expression I'm getting the error: 'ORA-00979: not a group by expression' when I try to run this query.

select empno, empname
from emp.employee
group by empno, empname
having empmsal > avg(empmsal);

I'm attempting to find employees with a salary above the average.
Can you see anything wrong with the query?

Sorry if this is obvious. I'm new to sql.

Because the group by on empno and empname has basically no point (supposing they're unique in the table), a better way to do this would be:

select empno, empname
from emp.employee
where empmsal > (select avg(empmsal) from emp.employee)

The thing is that (select avg(empmsal) from emp.employee) is computed only once - appearing in the where condition, and unless you have an index on empmsal it would perform a table scan, and then another for the main query. That's as best as it could get.

Another way is using window functions:

with cte as
( select empno, empname,
         avg(empmsal) over () as avg_empsal
  from emp.employee
)
select empno, empname
from cte
where empmsal > avg_empmsal ;


When you drop or truncate tables in SQL Server do old data pages get overwritten with new data? When you drop tables, does the empty space get automatically reused, or do you need to do some sort of maintenance to allow that space to get reused?

Consider a situation where you drop and recreate a large lookup table routinely. For instance, every day. Is this going to use more and more space until you perform some maintenance like shrinking the data file, or something like that? Or will the old space get overwritten with new data as if it were a hard drive?

SQL 2014 if it makes a difference.

When in doubt, read the documentation first:

  TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

So it deallocates the pages and make them available for reuse.


Joining relations based on distance between geometry columns? I have two tables:

User(id)
Region(id, userID, origin Geometry('point'), radius))

User has a one-to-one relationship with Region.

I want to query all users that have a region that contains a point (lat and long). To define contains, if the distance between the given point and region.origin is less than region.radius then that region contains the point.

I'm able to query just regions that contain a given point like this:

SELECT "id", "origin", "radius", "regionID", ST_Distance("origin", ST_MakePoint(lat, long), false) AS "distance"
FROM "regions" AS "region"
WHERE
  ST_DWithin("origin", ST_MakePoint(lat, long), maxDistance, false) = true
AND
  ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
ORDER BY distance ASC
LIMIT 10;

But I really want the users and regions and don't want to query each user individually based of the region. Here is what I have:

SELECT *
FROM user, regions as region
WHERE
  user.id = region.userID
AND
  ST_DWithin("origin", ST_MakePoint(lat, long), maxDistance, false) = true
AND
  ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
LIMIT 10;

With this query, I don't get any results, where the first query I get results.

ST_MakePoint(lat, long)

That's the wrong point order. You want ST_MakePoint(long,lat), check the docs here ST_MakePoint

AND ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0

That doesn't make any sense, it should be

ST_DWithin(origin, ST_MakePoint(long,lat), radius)

I think this is what you want,

SELECT *
FROM user AS u
INNER JOIN regions AS r
  ON u.id = r.userID
  AND ST_DWithin(r.origin, ST_MakePoint(long,lat)::geog, radius)
ORDER BY ST_MakePoint(long,lat) <-> r.origin
FETCH FIRST 10 ROWS ONLY

maxdistance and radius are the same thing. You have a LIMIT 10 there, what is the ORDER that you want? Do you just want any 10 rows?

Don't forget to add your spatial index. In this case you can actually add a compound index.

CREATE EXTENSION btree_gist;
CREATE INDEX ON region USING GIST (userID, origin);

See also,

Finding the nearest geo points across two tables?


Saving daily data for each user - MySQL I'm currently thinking about how to store data that the user might (or might not) change each day. Please note, I'm not a rdb expert and I'm still trying to learn everything.
For example, the user may change his weight today, (2021-04-24) to 80kg then tomorrow change it to 80.25kg, and so on (its also possible to go back in time inside the app and change the weight a month ago for example)
What I'm struggling with is how to save the data in a scalable way.
I know that I might want to add different data_types, for example, the option to change the daily waist measurement.
All options are assuming that the app have about 10K daily active users (not a lot but its just for demonstration sake)
Option 1: Save each day as one row, if I'll need to add new data_type, I'll have to do a ALTER TABLE ADD COLUMN `waist` . Which I know will lock the table and its probably not a great idea in production (or am I wrong?)
CREATE TABLE `daily_data` (
  `date` date NOT NULL,
  `user_id` int NOT NULL,
  `weight` float NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`date`, `user_id`)
);

Number of rows per year: 3,650,000.
Option 2:
Split the table in to 2 tables, So now I can easily add a new data_type in production by just adding a new row in data_types
CREATE TABLE `data_types` (
  `type_id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL UNIQUE,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
);

CREATE TABLE `daily_data` (
  `date` date NOT NULL,
  `user_id` int NOT NULL,
  `type_id` int NOT NULL,
  `data` float NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`date`, `user_id`, `type_id`)
);

The number of rows per year increases significantly (10,000 * number_of_types * 365).
Lets say that there are 3 types: 10,950,000.
This makes me think if its a good solution because 10K daily active users is not a lot of users but the number of rows is very big.
Option 3:
A bit more efficient because now instead of inserting new row per user per day, I'll insert a new row only when the user actually changes the data.
 CREATE TABLE `daily_data` (
   `id` int PRIMARY KEY AUTO_INCREMENT,
   `user_id` int NOT NULL,
   `start_date` date NOT NULL,
   `end_date` date,
   `type_id` int NOT NULL,
   `data` float NOT NULL,
);

With this solution there are a lot less rows because now potential duplicated data will not occur.
Minimum number of rows: (10,000 * 3) - None of the users ever changed their data
Maximum number of rows: 10,000 * 3 * 365 - Each user changed each data_type one day a year.

What's the best solution?

Is it reasonable to have this many rows for 10K daily active users? what if the app grows to 100K users? is it still fine?

Is there a better solution I'm missing?

Note: the third option refers to Option no.2 schema but can obviously be applied to Option no.1 as well.
My main goal is to be able to retrieve data as quickly as possible for my app.
Any kind of help is much appreciated!! if something is unclear, please comment and I'll clarify.

Definitely save new day data only if something has changed.
Now the question is onr table with data , or types and data.
Depends on number of columns and the periodicity of change.
For two table, there is a higher overhead, but iwth single wide table (lets say 10 or more types or data), you will be storing lot of duplicate data. In addition, more joins will be needed.
I would say single table is better if storage is not an issue


What exactly are secondary members acknowledging in replicasets with MongoDB? I try to understand the mechanics and behavior of MongoDB's replica-features better. I hope to get some insightful information from someone who is familiar with mongo's internals and behaviour. My aim is to understand Mongo's durability guarantees in a high-availability setup. The data I want to maintain would have unique constraints (by using two unique indexes), to maintain a 1:1 relationship by having two unique values in one doc. Inconsistencies or only partial durability would be fatal for my 1:1 relation and hardly resolvable once happened, especially undetected.

My question:

What exactly are secondaries acknowledging in a replica-set setup and using the writeconcern:"majority"?

That only the statement is acknowledged and applied in the secondary or that the whole oplog up until the new statement is applied?

Explanation of what exactly I am looking for:

According to their own documentation and some other sources (e.g. Aphyr's try on simulating network-partitions and his follow-up) using write-concern "majority" will make sure, that no write will be lost even in partitioning-scenarios and the like. (since I cannot post more than two links, I will not be able to refence mongodb's documenation). Stale reads are acceptable in my use-case.

But one thing that I just cannot figure out from their documentation is, what exactly is acknowledged on the replicas.

Replica-Sets are replicated using the oplog (collection of statements that modified data) by distributing them asynchronously. For avoiding a rollback once a failed primary tries to join the cluster with writes, that were not distributed (because of the asynchronous nature), they recommend using the majority-writeconcern (Doc->Replica->Replica Concepts->High Availbilty->Rollback). To my understanding, there is a case, where this cannot be achieved, depending on what exactly is acknowledged.

Let's say we have three instances, (1), (2), (3). One is primary (1,P) and the other two are secondaries (2,S), (3,S).

We are now trying to insert two new rows/docs {1} and {2}. And due to network-instability, instances will be unavailable to each other at some times.

Let's say the replicasets acknowledge single statements from the primary, then the following can happen:

instance (3,S) is unavailable, but inserting record {1} will succeed since there are 2 out of 3 available (majority achieved).

Current state:

(1,P):{{1}}

(2,S):{{1}}

(3,S):{{}} unreachable

instance (2,S) becomes unavailable and instance (3,S) happens to be online (not restarted, just online to the others). Meanwhile no asynchronous replication was initiated by the primary. Record {2} is inserted. Majority achieved again (2 out of 3)

Current state:

(1,P):{{1},{2}}

(2,S):{{1}} unreachable

(3,S):{{2}}

If (1,P) goes down, an election will happen. (2) or (3) will become primary. But that will make the database inconsistent, since the new primary has no knowledge of the other lost doc/row. Now some sort of rollback is bound to happen when the original primary comes online (and thus losing data).

If acknowledges are made by applying all latest oplog-entries, then this should not happen. (2,S) would have both entries when acknowledging {2}. And then will also win the election. Data is not lost.

I am well aware that this is unlikely to happen. But if it happens, I don't see how it would even be detected or avoidable unless automatic failover is just disabled.

Ok, while the question(s) is(are) rather complex, the answer(s) is(are) pretty simple, albeit a bit lengthy.

The replication (simplified)

When an operation is saved in the oplog, it does not get distributed to the other members. It gets pulled by the replica set members.

A failed member rejoining the replica set will contact the primary and pull the oplog entries it hasn't processed. So it will eventually become consistent.

However, there is a caveat. For performance reasons, the oplog is a capped collection. This means there is limited space available for it. With limited space, you have a limited number of operations which can be written to the oplog. And limited operations reach back only a limited amount of time, since the oldest entries of the oplog will be overwritten when a new entry would exceed the oplogs size limit. The limited time frame is called the replication oplog window.

If member rejoins the cluster and its last operation is older than the oldest entry in the primaries oplog, it becomes stale and needs to have an complete resync of the data.

So in your example, after 3,S went down, 2,S still would happily pull the oplog.
In your example, all three are up for a short amount of time, until 2,S failed. The election now would take place, and since there is no need to change the primary 1,P would still serve in this function.

Now, when 3,S came up within the oplog window, it would simply connect to the primary, pull the oplog, apply the oplog entries to its data set and all lived happily ever after (except for the admin who has to get his back moving to get up 2,S again for double redundancy).

In case 3,S comes up outside the oplog window, you'd have to initialize a full resync by

Shutting down the instance of mongod
Wipe its dbpath
Restart the instance

Note that if 2,S isn't up and resynced during that procedure, the cluster wouldn't have a majority up and running and hence be unwritable.

Acknowledgement of operations

With a write concern of {w:1}, the primary would acknowledge the query to be

Received and
syntactically correct since it is
Applied to the in memory data set of both the data and the oplog

With a write concern of {w:1,j:1}, all of the above applies. Additionally, modifications are written to the journal, making the data guaranteed to be durable - except for the edge case of the journal being damaged.

Keep in mind that operations are written to the journal every commitIntervalMs, 100msecs by default, one third of the configured value with a write concern of {"w":x, "j":1}. We will come back to this later.

With the replication process as detailed above, it is sufficient that the {"w":x, "j":1} for x > 1 works different, since the primary is then guaranteed to have the latest data of all replica set members: With said write concern, the write to the journal is only acknowledged for the primary.

The replica set is either writable from the clients point of view (the majority of non-hidden, non-delayed members is up) or not. If it is writeable, the primary is guaranteed to have the latest data, and secondaries failing back will either automatically resync from the oplog or need to be fully resynced.

Edge case for {w:1}

There is a special condition, however. With a write concern of {w:1} it can happen that a primary about to fail accepted writes which did not make it to the secondaries. When this this machine rejoins the replica set, it will notice that its newest write operation is newer than the oldest of the new primaries oplog it hasn't processed. The instance will then identify all write operations it has accepted but didn't make it to the secondaries, writing them to the rollback folder in dbpath, up to a limit of 300MB.

Dealing with rollbacks can be quite tricky, however, it is easy to prevent: set w > 1.

Rule of thumb: Never set w < 2 unless you can afford to loose data

Conclusion

With a write concern of w<1, you prevent rollbacks and can be pretty sure that a write operation is durable. It only would not be durable if all replica set members the data was supposed to be processed by failed within a time frame of commitIntervalMs, so (for the default values) 100ms in a worst case scenario (all journals are written at the exact same point in time), 50ms in a best case scenario (the journals are shifted by exactly 50ms), giving us an average of 75ms in which all affected replica set members had to fail. If that is too much for you, either add more replica set members, lowering the probability that all servers supposed to process the operations fail, or set j:1. Still, this applies only if w replica set members fail simultaneously. With a geographically distributed replica set, the probability for this scenario equals 0 for all practical purposes. And in case of the Zombie Apocalypse happening simultaneously with an invasion by little green aliens, I assume your concerns (no pun intended) will shift.

If write operations are extremely important to be written to disk when acknowledged, you still can take the sledgehammer of forcing a file sync before you consider a write operation to be successful.


Full or bulk-logged recovery model? My database is about 10 GB and I use full recovery model.

Sometimes I have to add a lot of data at once and during that time, I change the recovery model from full to bulk-logged.

What do you think?
Is this a good idea?
Or it is risky?

You probably misunderstood the recovery model concept. From BOL Doc

  A recovery model is a database property that controls how transactions
  are logged, whether the transaction log requires (and allows) backing
  up, and what kinds of restore operations are available

Recovery model controls the logging and there by how much data you can recover. Only certain transactions which would be minimally logged would be minimally logged all other transactions would be fully logged in bulk logged recovery model. So it depends on how much data you can loose keeping that in mind choose recovery model. You should read Backup under BL recovery model.

If you are performing a bulk logged operation and dont really care about point in time recovery during the period the BL operation runs go ahead change recovery model to bulk logged. But read considerations before changing to BL recovery Model

Risk.

If you perform operation that are minimally logged then you loose point in time recovery for the time period this transaction is running. After this transaction finishes and other transaction starts which is fully logged you can perform point in time recovery. You must read Understanding Logging and recovery in SQL Server


Retrieving data from stored procedure's cursor I want to use the ResultSet in java program to get data from Stored procedure's cursor. Is that possible with SQL Server?

My code:

CREATE PROCEDURE dbo.SampleProcedure AS
DECLARE @FirstName varchar(64)
DECLARE @LastName varchar(64)
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEE

OPEN c1

FETCH NEXT FROM c1
INTO @FirstName, @LastName

CLOSE c1
DEALLOCATE c1

When I use EXEC SampleProcedure, I just got the output as "Command(s) completed successfully" and the Resultset shows null in java program. So what to change in my code so that my ResultSet retrieves data?

I want to know how to prepare ResultSet in the SQL Server code so that I can retrieve the same in java program.

SQL Server stored procedures can return record-sets from a query, a CURSOR would not be required (unlike in Oracle). The query below is all that's needed to get a recordset:

CREATE PROCEDURE dbo.SampleProcedure
AS
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEE


sql transactional replication - slow renitialization / slow bulk insert i recently set up sql transnational replication and i have some performance issue
when i'm initializing/re-initializing it from snapshot.
The biggest table have is about 8gb as snapshot file
network connection is fine - when im copying (just to check network speed is not the case) this snapshot file into desktop of this subscriber machine then it is around 200 MBps.
When this source machine witch is distributor also is bulk inserting this table into subscription database then CPU on this subscriber machine almosts sleeps , batch requests is between 0 and 1 and DATABASE IO is 4 MBps max...
Why it is going so slow ? is there any way to speed this up ? 8gb table will be +- 30min what if i will have 50gb table some day?
Can it be the case that the publisher machine is distributor also ? and because of that there are some network bulk insert commands limitations ?
It is how i think that in that case that the distributor is actually opening this snapshot that he generates before and doing this bulk insert like thru linked server in that case?
On my destination recovery models is simple so it is not this also.
thanks and regards!

actually opening this snapshot that he generates before and doing this bulk insert like thru linked server in that case?

Is this a push subscription or a pull subscription?  For Push the Snapshot Agent runs at the distributor; for Pull it runs at the subscriber.  The Snapshot Agent reads the snapshot files and connects to the subscriber to create the tables and bulk load them.
You can also copy the snapshot folder to an Alternate Snapshot Location and have the Snapshot Agent read from there.  If you're using Push Subscriptions because you have SQL Express on the subscriber and so don't have SQL Agent, you can syncronize your Pull Subscription through PowerShell.

Why it is going so slow ?

Look at the Wait Stats and CPU utilization on the Subscriber.

Can it be the case that the publisher machine is distributor also ?

Yes.  Called Local Distribution.  Most common configuration.


Is using ASM a pre-requisite for RAC? I am about to build our first RAC development environment, and as I'm new to RAC and have not used ASM I wanted to keep things as simple as possible.

I was under the impression that ASM is a pre-requisite of RAC, but reading I've done recently implies it's optional (although ASM offers better performance particularly on reads).

So is a non-ASM RAC environment supported by Oracle? We are using 11.2.0.4 on a RedHat Linux 6 environment.

i've found the definitive answer from the oracle docs

http://docs.oracle.com/cd/E11882_01/license.112/e47877/editions.htm#DBLIC109

in the Oracle Database Standard Edition and Oracle Real Application Clusters (Oracle RAC) section, it specifies

"Oracle Automatic Storage Management is required for creating and managing all Oracle database file types. Raw volumes, partitions, or third-party cluster file systems are not supported for storing Oracle database files with Oracle Standard Edition and Oracle RAC"


How to start Oracle database's listener service after a change in the IP address? I'm using Windows XP as my Oracle database server. Today there was a change in the IP address if the database server.

I want start my database instance's listener again. Windows XP prompst that:

OracleOraDb11g_home1TNSListenerMZOA_LISTENER service on the local computer started and stopped. Some services stop automatically if they do not have anything to do.

(ps:MZOA is the name of my db instance)

I updated listener.ora to reflect the new IP.

And my listener.ora is:

# listener.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

ADR_BASE_MZOA_LISTENER = D:\app\Administrator

MZOA_LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =CHINA-F781B4F30)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1521))
 #caution:192.168.1.100 is my new ip
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.100)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_MZOA_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  (SID_DESC =
      (SID_NAME =mzoa)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
   )
  )

Unfortunately when I want start the listener, Windows still shows the above prompt. And I donn't know how to do.

To resolve this it is best to delete the listener using the net configuration tools and recreate it.  Stop the listener service before doing this.  Oracle gets very picky when IP addresses change.


to_number, ORA-01722: invalid number error I have a batch job which is trying to load data from a .dat file into an Oracle table.

Below is the script within our control file:

LOAD DATA
INTO TABLE TABLE_1
WHEN (1:1)="1"
(
A_id                       POSITION(0002:0005)    CHAR             ,
c_code                     POSITION(0006:0008)    CHAR             ,
status                     POSITION(0009:0009)    CHAR             ,
FTC                        POSITION(0011:0024)    char
                           "to_number(:FTC,'S99,999,999.99')"      ,
TCER                       POSITION(0029:0038)    decimal external ,
ATC                        POSITION(0039:0052)    char
                           "to_number(:ATC,'S99,999,999.99')"      ,
FTR                        POSITION(0053:0066)    char
                           "to_number(:FTR,'S99,999,999.99')"      ,
TRER                       POSITION(0067:0080)    decimal external ,
ATR                        POSITION(0081:0094)    char
                           "to_number(:ATR,'S99,999,999.99')"
)

Records in the file:

1GSXBGBPNR        384.66    0.60801391        632.00       -384.66    0.60801391       -632.00
1GSXBEURNR         10.42    0.70548409         14.00          0.00    0.00000000          0.00
1GSXBEURRE        136.01    0.70533631        192.00          0.00    0.00000000          0.00
1GSXBUSDRE         95.91    0.90472597        106.00        -95.91    0.90472597       -106.00

While the job runs we get the ORA-01722: invalid number error  error on the column FTC. How can I fix it?

Your format picture, 'S99,999,999.99', requires that the number begin with a sign -- either "+" or "-" -- which your actual data does not contain.

Remove the S from the format picture.