Whats the difference between Transaction logs and Binary logs? Coming from a SQL Server shop, I now work with MySQL and I was curious.

What are the differences between MySQL's binary log and MSSQL's transaction log?

By the outlook thus far, it seems that there is only one binary log per MySQL instance as opposed to a transaction log per database as in MSSQL.

Answering only the MySQL Part of the Question

A binary log records completed SQL statements. You can have many binary logs. Under default settings, binary logs rotate at the 1G mark (see expire_logs_days and max_binlog_size).

You can see binary logs by running one of the following:

SHOW BINARY LOGS;
SHOW MASTER LOGS;

Ther current master log is always the last one in the list. To see just the last binary log, which is the current one, run this:

SHOW MASTER STATUS;

When it comes to InnoDB storage engine and transactions

There is a metadata file (ibdata1, which holds, by default, data pages, index pages, table metadata and MVCC information), also known as the InnoDB tablespace file.
You can have more than one ibdata file (see innodb_data_file_path)
There are redo logs (ib_logfile0 and ib_logfile1)
You can have more than two redo logs (see innodb_log_files_in_group)
You can spread data and indexes across multiple ibdata files if innodb_file_per_table is disabled
You can separate data and index pages from ibdata into separate tablespace files (see innodb_file_per_table and StackOverflow Post on how to set this up)


Query data via username - pair - best performance RDBMS: MariaDB 10.3
Let's say you have a table which stores specific information (info_1, info_2, etc.) according to a given pair of usernames; like so:
+----------+----------+----------+----------+----------+------+--..
|  user_1  |  user_2  |  info_1  |  info_2  |  info_3  |  ..  |  ..
+----------+----------+----------+----------+----------+------+--..

where the values of user_1 and user_2 each equal a specific & distinct username (VARCHAR(50)), and are defined as multi-column index (user_1,user_2) of the table upon creation.
The queries my app does search in function of:
A) a given user pair:
WHERE user_1 = name_1 AND user_2 = name_2 OR user_1 = name_2 AND user_2 = name_1
B) a given user / return all the records where a specific user is one of the members:
WHERE user_1 = name_1 OR user_1 = '%' AND user_2 = name_1
Problem: I must make sure that searches are always indexed. When using an OR clause, a full table scan is however executed each time.
SOLUTION I)
Thanks to this answer, possible queries resulting in indexed searches for these cases is:
A)
SELECT * FROM my_table WHERE user_1 = name_1 AND user_2 = name_2
UNION ALL
SELECT * FROM my_table WHERE user_1 = name_2 AND user_2 = name_1;

B)
SELECT * FROM my_table WHERE user_1 = name_1
UNION ALL
SELECT * FROM my_table WHERE user_1 = '%' AND user_2 = name_1;

However with the drawback of always using two SELECT statements and one WHERE clause which will always be in vain (as there will always be one record per user-pair). That's why I attempted to further optimize the query by using a fulltext index, which resulted in the following table:
SOLUTION II)
+---------+----------+----------+----------+------+--..
|  users  |  info_1  |  info_2  |  info_3  |  ..  |  ..
+---------+----------+----------+----------+------+--..

where users has been defined as FULLTEXT VARCHAR(150), and its value always corresponds to the format name_1-name_2, whereas I used a hyphen because a comma is used in the MATCH...AGAINST syntax to execute fulltext index queries.
With this structure, I now get the following new query possibilities for A) and B):
A)
SELECT * FROM my_table WHERE MATCH(users) AGAINST ('"name_1-name_2","name_2-name_1"');

B)
SELECT * FROM my_table WHERE MATCH(users) AGAINST ('name_1');

Which in my opinion drastically facilitate the query structure, and use neither two SELECT statements, nor an additional WHERE clause which will be in vain. Still, as I don't know a lot yet about query performance optimization and the comparison of fulltext index searches with regular index searches, I wondered which solution of I and II may perform better, and why ? Or is there even a better approach?

Critique
1A cannot use indexes because of OR
1B -- user_1 = '%' prevents the use of INDEX(user_1, ...), so solution 1B is out.
1B can be salvaged by having a second index:  INDEX(user_2).  Then simply say
SELECT * FROM my_table WHERE user_1 = name_1
UNION ALL
SELECT * FROM my_table WHERE user_2 = name_1;

2-- FULLTEXT has limitations, such as "word" length.  So be cautious.
2A  (WHERE MATCH(users) AGAINST ('"name_1-name_2","name_2-name_1"') -- it is probably sufficient to say simply
WHERE MATCH(users) AGAINST ('+name_1 +name_2' IN BOOLEAN MODE); The +` says that both are required, but in any order.
2B -- OK (with the above caveats)
FULLTEXT, when it is applicable, is likely to be very performant.  Here are two tricks I have used:
WHERE MATCH(text) AGAINST("R +James" IN BOOLEAN MODE)

The short 'word' (R) will be ignored; it will search for James efficiently.  Note the selective use of '+'.
WHERE MATCH(text) AGAINST("R +Anders" IN BOOLEAN MODE)
  AND user LIKE "%R. Anders%"

This assumes that the name might be in the middle of a large column, and I need to check the initial.  The FT test will be first (and fast), followed by the LIKE (slow, but against few rows).
That still has problems because of "E. Anders and R. Anderson" would be wrongly caught.
(Etc, etc.  But I digress from your simple 2-word use case.)
Bottom line:
1B (extra index) is optimal.  (But I suspect you watered down the Question.)
FULLTEXT would be my next choice.
Let me add an option 3:
Sort the users before inserting and when querying.  That is
INSERT ... (user1, user2)
   VALUES
   (LEAST(?, ?), GREATEST(?, ?))

then you need (for case A) only INDEX(user1, user2) together with
WHERE user1 = LEAST(?, ?)
  AND user1 = GREATEST(?, ?)

Alas, that will probably mess up case B.  (Do all rows have 2 user names, but some queries have only 1 user?)
And Option 4
Have another table that maps user names to my_table by id.  Typically 2 rows in this new table for each row in my_table.  Optionally toss the user columns from my_table.
I won't go into the rest of the detail; they do get messy.


What is this expression changing process? Consider this table definition:

CREATE TABLE [dbo].[Post]
(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [PostType] VARCHAR(10) NOT NULL,

    CONSTRAINT [CK_Post_PostType] CHECK ([PostType] IN ('Question', 'Answer', 'Comment'))
)

If I run that and then look in sys.check_constraints:

select [definition]
from sys.check_constraints
where [name] = 'CK_Post_PostType';

This is the output:

([PostType]='Comment' OR [PostType]='Answer' OR [PostType]='Question')

So it changed my "in" statement to a series of "or" statements instead.

The same thing is true of (at least)

BETWEEN (gets changed to OR statements) and
CAST (gets changed to CONVERT).

I realize this isn't a functional problem in SQL Server, since the expressions are logically equivalent.  But it causes issues with schema compare tools like SSDT, because the source code is out of sync with the deployed code.  I've blogged in more detail about the problem here: SSDT problems: deploying the same change over and over

Is there a name for this conversion process / behavior?  Is there any documentation for the different conversions that can occur, so we can plan for them (or try to avoid them)?

Broadly, this is known as standardization or normalization.

Examples of this include (for expressions) Conjunctive Normal Form and Disjunctive Normal Form.

There is no Microsoft documentation I am aware of that lists all the steps of the multiple processes involved. Essentially, SQL Server converts things into a standard format to make things easier for it to work with internally. This occurs at various times, including during parsing, and the normalization stage of query compilation and optimization.

As far as CAST versus CONVERT is concerned, it may simply be that CONVERT was first (and/or because it offers a superset of functionality). You should find that built-in (intrinsic) functions are generally preferred.


How to automatically drop and restore PostgreSQL database I am using dhis2 with PostgreSQL as database but I want to verify my backups from the cloud by restoring and checking whether the data are ok but I need it to be automatic.

This is going to depend on how you are doing your backups and if you are doing restores on the same database cluster that you are using for your live data.

The simplest case ( for small databases ) is that you are dumping your databases with pg_dump and storing them in the cloud as SQL files, possibly compressed.

Once you have backups you can load them into a database cluster using psql; you have various options in how you do this whether or not you specify that pg_dump drops entities before creating them and whether to include database creation statements.

If you are wanting to ensure the integrity of your backups, without overwriting a database you are using ( doing the restore on the same cluster you dumped from ) you want to dump the database without the create database statements and you want to have the restore script create a database with a different name.

There are a lot of variables in play in terms of what you can include in the dump and a fair bit of subtlety about ownership and permissions that are going to be dependent on what sort of restore you are doing and what roles are going to need access.


Can't migrate standalone MongoDB to Cluster I'm trying to convert a standalone MongoDB database into a cluster in order to migrate my database to MongoDB Atlas.

I've followed the following steps to try and convert it:

Stop my mongodb instance
Changed the configuration to the following content:

........................

#how the process runs

processManagement:
     timeZoneInfo: /usr/share/zoneinfo

security:
   authorization: enabled

replication:
  replSetName: "rs0"

Restarted mongodb with the following command: sudo mongod --config /etc/mongod.conf --fork
Logged into mongodb, to the admin database, with a user who has the following roles: clusterMonitor, readAnyDatabase, userAdminAnyDatabase
Executed the command rs.initiate()

And it gave me the following error:

{
        “ok” : 0,
        “errmsg” : “not authorized on admin to execute command { replSetInitiate: undefined, $db: \“admin\” }“,
        “code” : 13,
        “codeName” : “Unauthorized”
}

I have tried the previous steps in a dev server, and everything worked fine, bun in my production environment it didn't work, any help is appreciated.

Logged into mongodb, to the admin database, with a user who has the following roles: clusterMonitor, readAnyDatabase, userAdminAnyDatabase

...

  not authorized on admin to execute command { replSetInitiate: undefined, $db: \“admin\” }“

This error is expected since clusterMonitor is a read-only role.

To initialise or update replica set configurations when access control is enabled your user needs a cluster management role like clusterManager or clusterAdmin.


Postgres: turn off formating for xml export? I want to export some table to xml file:

\o output.xml
SELECT table_to_xml('modified_records', TRUE, TRUE, '');

However, I'm struggling with psql formatting. The best I managed to get is:

\t on
\x on
\pset format unaligned
\o modified_records.xml
SELECT table_to_xml('modified_records', TRUE, TRUE, '');
\o

Which results in

table_to_xml|<modified_records xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <tag1>val1</tag1>
  <tag2>val2</tag2>
</modified_records>

<modified_records xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <tag1>val1</tag1>
  <tag2>val2</tag2>
</modified_records>

<modified_records xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <tag1>val1</tag1>
  <tag2>val2</tag2>
</modified_records>

So I still have to remove table_to_xml|. Which is not hard:

cat modified_records.xml | sed '1s/^table_to_xml|//'

# or even in psql:
\o | sed '1s/^table_to_xml|//' > modified_records.xml

but feels like fighting with Postgres.

Is there a way to dump just the value?

(there are plenty of options for \pset, but I can't find its docs)

The psql command \? shows the option to turn off expanded formatting:

Formatting:
...
  \x [on|off|auto]       toggle expanded output (currently off)

Typically it will show table data in one record per line if its "off" or else each field in its own line when switched on, e.g.:

postgres=# \x off
Expanded display is off.
postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | en_IN   | en_IN |

When its switched on:

postgres=# \x on
Expanded display is on.
postgres=# \l
List of databases
-[ RECORD 1 ]-----+----------------------
Name              | postgres
Owner             | postgres
Encoding          | UTF8
Collate           | en_IN
Ctype             | en_IN
Access privileges |
-[ RECORD 2 ]-----+----------------------

With xml output, since everything is in one stream, it shows the record label before printing out the xml.
The difference between /x on and /x off is as follows:

/x on

table_to_xml|<pg_attribute xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <attrelid>1255</attrelid>
  <attname>proname</attname>
  <atttypid>19</atttypid>
  <attstattarget>-1</attstattarget>

With /x off the output is:

/x off
<pg_attribute xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <attrelid>1255</attrelid>
  <attname>proname</attname>
  <atttypid>19</atttypid>
  <attstattarget>-1</attstattarget>

So before writing to the file you should change the commands issued as follows:

\t on
\x off
\pset format unaligned


attach a damaged mdf file in SQL server In SQL Server 2012 I am trying to attach a .mdf file to a database. The thing is there were still open transactions when my server was shut down. Then I reinstall the server and I have deleted the .ldf file.

Now when I try to attach the .mdf file in SSMS, an error says without .ldf file a .mdf file cannot be attached because there was open transaction.

Does anyone know a solution?

Many thanks!

Ypu can use hack attach method described by Paul Randal here:
hack-attach a damaged database

  To attach the database you need to fool SQL Server into thinking it’s
  already attached. The set of steps to go through are as follows:

  This only works if the server instance you’re using is the same
  version as the database you’re trying to attach

  Create a dummy database, with the same name, the same number of data    and log files, with the exact same file names (extremely
  important!)    and same file IDs (make sure you have instant file
  initialization    enabled so the file creations don’t take ages – see
  this blog post).    This can be tricky to do if you had added or
  removed files to the    database before it was corrupted, but you need
  to make sure the file    IDs are exactly the same.
  Set the dummy database offline (ALTER DATABASE RealDatabaseName SET    OFFLINE) or shut the server down
  Delete all the data and log files from the dummy database
  Drop in as many of the data and log files from the damaged database    as possible
  Set the dummy database online (ALTER DATABASE    RealDatabaseName SET    ONLINE) or start the server if you shut it down    for step 2

In your case you should delete both mdf and ldf files of newly created database and copy only your mdf to the file location.

Your database will be attached but cannot run recovery, so it will be marked as SUSPECT
Then you can try to put it into EMERGECY mode and extract as much data as possible.

Seee here an example: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database


Unexpected result when joining a table in a year range I'm working with an users, user_roles and point_standards. Users need to achieve a certain points in a certain period based on which role they have. Points are retrieved from courses which are done in a specific year. Retrieving the user points is not the problem.

For the certain points a user has to achieve, a criteria is defined. These are made for 3 years and can not overlap. See the example below.

This means, from 2018 to 2020, the user with the role that is assigned to these criterias needs to achieve 93 points.

In 2018, the user should achieve 31 points (93 / 3).

The problem is when the user wants to check the criteria for years corresponding to more criteria. Lets say the user wants to check the criteria in the years 2018, 2017 and 2016. This means:

(93 / 3) + (50 / 3) + (50 / 3)

When ceiling these sums, this should result into a criteria of 65 points for 2018, 2017 and 2016.

For my expected end result, I want to get an overview with all users and the criterias for specific years.

In the example below, I'm trying to retrieve the criteria for a single user for the years 2018, 2017 and 2016.

SELECT `users`.first_name,
        ps.points

FROM `users`
       # Join the user roles table
       INNER JOIN `user_roles`
         ON `users`.`role_id` = `user_roles`.`id`

       # Get the criteria for 2018, 2017 and 2016
       LEFT JOIN (SELECT id, role_id, COALESCE(points / 3) AS points
                  FROM point_standards
                  WHERE 2018 BETWEEN YEAR(start_year) AND YEAR(end_year)
                     OR 2017 BETWEEN YEAR(start_year) AND YEAR(end_year)
                     OR 2016 BETWEEN YEAR(start_year) AND YEAR(end_year)) ps
         ON (user_roles.id = ps.role_id)

WHERE users.id = 123
GROUP BY `users`.`id`, ps.id
ORDER BY `points_internal` DESC

This results in 2 rows with the correct criteria.

Expected result

In this case, it should return 3 rows. This because of I want to see the criteria of the years 2018, 2017 and 2016.

I then hoped I could sum the points of these 3 rows for each user.

Questions

Why is it only returning 2 rows instead of 3? I've selecte 3 years to look in.
How can I sum these amount of rows together so I can get an overview of all users?

Let me suggest another approach by adding a Years table.

create table p (id int, criterium decimal(18,2), init_year int, end_year int);

create table years(cyear int);

insert into years values(2010),(2011),(2012),(2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020);

insert into p values (1, 93.0, 2018, 2020), (2, 50.0, 2015, 2017), (3, 74.5, 2010, 2012);

Now you can expand dates between initial and ending date, then simply sum year values that match user dates.

select id,
       cyear,
       (criterium / ((end_year - init_year) + 1.0)) as va
from   p
join   years
on     cyear >= init_year
and    cyear <= end_year
order by id;

id | cyear |        va
-: | ----: | --------:
 1 |  2018 | 31.000000
 1 |  2019 | 31.000000
 1 |  2020 | 31.000000
 2 |  2017 | 16.666667
 2 |  2015 | 16.666667
 2 |  2016 | 16.666667
 3 |  2010 | 24.833333
 3 |  2011 | 24.833333
 3 |  2012 | 24.833333

db<>fiddle here

select sum((criterium / ((end_year - init_year) + 1.0))) as total
from   p
join   years
on     cyear >= init_year
and    cyear <= end_year
where  cyear >= 2016 and cyear <= 2018;

|     total |
| --------: |
| 64.333333 |

db<>fiddle here


ORA-12154 TNS: could not resolve the connect identifier specified with SQLPLUS I am using Toad to connect my database and its working fine for me .
But when i try to connect with the same credential , i am getting the error message as shown below:

ORA-12154 TNS: could not resolve the connect identifier specified

Steps:

Opened command promt
Reach to the path of SQLPLUS (C:\programs\Oracle\Ora11g\BIN)
C:\programs\Oracle\Ora11g\BIN\sqlplus username@schema_name
Enter Password :

ERROR:
 ORA-12154 TNS: could not resolve the connect identifier specified
But when i open this with the owner of the schema ,it works for me
C:\programs\Oracle\Ora11g\BIN\sqlplus schema_name@schema_name
Enter Password:
Connected to :......................

So,my question is why my username is not able to connect that particular schema ,while the owner of that schema can connect .
Note:I can connect to that schema with my username with Toad ,but not with sqlplus

My TNSNAME.ORA is under TNS_ADMIN folder

Please help me out of this .

So you don't need to specify the full path when you run sqlplus.exe:

Set your %TNS_ADMIN% variable to  the full path of the relevant folder (C:\programs\Oracle\Ora11g\TNS_ADMIN ?). Instructions for doing so are a google away.  At the same time, make sure %ORACLE_HOME% is set to C:\programs\Oracle\Ora11g and that your %PATH% has C:\programs\Oracle\Ora11g\bin added to it.

The problem you're actually having is because of the parameters you're passing to sqlplus.

You've been doing:

C:\programs\Oracle\Ora11g\BIN\sqlplus username@schema_name

Which is wrong & interpreted by sqlplus as:

C:\programs\Oracle\Ora11g\BIN\sqlplus username@database_name

Also, in Oracle, the owner of a schema (hence the schema name) is the user name. Obviously, grants can be given to other users to perform DML/DDL on other schemas, but you still have to login with a user with sufficient rights. (Proxy logins exist, as does alter session set current_schema=blah, but both are beyond the scope of this answer).


Oracle cluster top activity class "cluster" When under high load (many queries in parallel) our Oracle RAC gets in a state where most of the activity is reported as "cluster" by Enterprise Manager.  It stays like that for 30 minutes to a couple of hours, and then just recovers by itself and happily proceeds.

Sometimes instead of "cluster" the activity is reported as "configuration" or "concurrency".

I'm looking for any advice on how to go about diagnosing what is going on (and how to make it stop doing it). The graph shows only one node in question.  Queries are run on a service which is limited to that node.

Investigate v$session view about what particular event is causing those cluster waits. E.g.:

select s.event, s.* from v$session s order by s.event;

event column will get you offending event event name. Then you can google the event or add information to you question.
Also "Blocking Sessions" screen in Performance menu in Enterprise Manager in such cases helps quite often. Some session may simply block other sessions.


Explicit usernames in references to objects Much of our code -- both in external programs and in stored procedures -- uses explicit usernames, when referring to various DB-objects:
SELECT * FROM prod.object

or
prod.StoredProcedure(.....)

This causes obvious problem, when a copy of the database is loaded into a schema with a different name -- the same object becomes qa.object now...
I proposed, we drop such explicit references completely, referring simply to the object and StoredProcedure instead.
However, this would cause problems when another account (such as prod_ro) logs in and tries to execute prod's stored procedures or browse prod's tables.
How do people handle such situations?

You can use
alter session set current_schema=qa;

Then, all references to objects that aren’t qualified with a schema will be treated as if you wrote qa.object. You will still need the necessary privileges to work with the relevant object.


Why does this query return users not born on the date provided? I am trying to search for a client using name and date of birth. My query is included below.

Select *
From Client
Inner Join Address
On Client.num = Address.cl_num
Where
   Client.fname LIKE ‘%john%’ and Client.sname LIKE ‘%doe%’
Or
    Client.fname LIKE ‘%doe%’ and Client.sname LIKE ‘%john%’
And Client.year = 1973
And Client.month = 06
And Client.day = 05

The results however show clients who weren’t born on that day. Is there a problem with my statement?

The query is run on an iSeries server.

The OR clause is short circuiting your selection. OR binds lower than AND.  Use parenthesis to group that criteria as follows:

Select *
From Client
Inner Join Address
On Client.num = Address.cl_num
Where
(
Client.fname LIKE ‘%john%’ and Client.sname LIKE ‘%doe%’
Or
Client.fname LIKE ‘%doe%’ and Client.sname LIKE ‘%john%’
)
And Client.year = 1973
And Client.month = 06
And Client.day = 05

Precedence rules for operators exist in all languages.  For example, 3 + 5 * 2 will give you 13 and not 16 in most programming languages.   * has higher precedence that +, the same way AND has higher precedence than OR. If you want the addition, 3+5, to happen before the multiplication, you'll use parentheses: (3 + 5) * 2


How to perform a left join of list of dates with an existing table of records smartly I am using the following query to get a list of dates between two given dates:

SELECT selected_date FROM
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
WHERE selected_date BETWEEN '2016-03-08' AND '2016-03-11';

This gives me the following output:

+---------------+
| selected_date |
+---------------+
| 2016-03-08    |
| 2016-03-09    |
| 2016-03-10    |
| 2016-03-11    |
+---------------+

The following query gives me a list of all employees required to work for a particular date:

SELECT employee.id, employee.firstname
FROM Users employee
LEFT JOIN Table1 table1 ON table1.fId = employee.tId
WHERE table1.day = dayofweek('2016-03-10')

This yields:

+----+-----------+
| id | firstname |
+----+-----------+
|  1 | Mat       |
|  2 | Tom       |
+----+-----------+

The results are empty if no employee is required to work on the given date.

I have a thrid table which lists the performance of each employee for a particular day:

SELECT * FROM performance;

+----+------------+------------+------+
| id | employeeId |    date    | val  |
+----+------------+------------+------+
|  1 |          1 | 2016-03-08 | 100  |
|  2 |          2 | 2016-03-08 | 90   |
|  3 |          1 | 2016-03-09 | 80   |
|  4 |          2 | 2016-03-09 | 100  |
+----+------------+------------+------+

I want to join the above three results to yield:

+----+------------+-----------+------------+------+
| id | employeeId | firstname |    date    | val  |
+----+------------+-----------+------------+------+
|  1 |          1 | Mat       | 2016-03-08 | 100  |
|  2 |          2 | Tom       | 2016-03-08 | 90   |
|  3 |          1 | Mat       | 2016-03-09 | 80   |
|  4 |          2 | Tom       | 2016-03-09 | 100  |
|  5 |          1 | Mat       | 2016-03-10 | null |
|  6 |          2 | Tom       | 2016-03-10 | null |
+----+------------+-----------+------------+------+

Note : On 2016-03-10 Mat and Tom aren't required to work. Hence, their values should be null in the final result. The performance table will not have entries for Mat and Tom for 2016-03-10 in this case.

I am kind of lost on how to achieve this.

You must CROSS JOIN the Users table with your Calendar query.

It will give you this result with one of each date for each user:

selected_date | firstname
2016-03-08    | Mat
2016-03-09    | Mat
2016-03-10    | Mat
2016-03-11    | Mat
2016-03-08    | Tom
2016-03-09    | Tom
2016-03-10    | Tom
2016-03-11    | Tom

This can then be LEFT JOIN with the Performance table. (SQL Fiddle)

Query:

SELECT u.id, u.firstname, cal.selected_date, p.val
FROM Users u
CROSS JOIN (
    SELECT selected_date FROM (
        SELECT adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date
        from
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
    ) v
    WHERE selected_date BETWEEN '2016-03-08' AND '2016-03-11'
) cal
LEFT JOIN performance p
    ON u.id = p.employeeId AND cal.selected_date = p.date;

Output:

 id | firstname | selected_date |    val
  1 |       Mat |    2016-03-08 |    100
  2 |       Tom |    2016-03-08 |     90
  1 |       Mat |    2016-03-09 |     80
  2 |       Tom |    2016-03-09 |    100
  1 |       Mat |    2016-03-10 | (null)
  2 |       Tom |    2016-03-10 | (null)
  1 |       Mat |    2016-03-11 | (null)
  2 |       Tom |    2016-03-11 | (null)


Replacing double digit numbers I have column with numeric values that I need to replace into text values.

I used the sql statement:

  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(loanstatus,
  '1', 'Apple'), '2', 'Banana'), '3', 'Citrus'), '4', 'Deer'), '5',
  'Elephant'), '6', 'Fruits'), '7', 'Grey'), '8', 'Hope'), '9',
  'Island'), '10', 'June'), '11', 'Kate'), '12', 'Live'), '13', 'Moon')

I've tried using Double Quotations (") in place of Singles (').
I've also tried using String('1') and String("1").

Values 1 to 9 works fine.
The problem is if the insert value is "11" then the output is AppleApple
If "12" then "AppleBanana"
when it should come out as "Kate" for 11 and "Live" for 12.

The doubling up is because of your nested parenthesis. Processing will start with the inner-most pair and work outwards. In this case the innermost replacement is 1 -> Apple meaning 11 -> Apple1 -> AppleApple.

Reverse the nesting, starting with the longest source value and work outward. Better still, put the mappings in a separate table and join to it (if Foxpro supports join-like syntax).


Audit trail tables in Oracle 12c I came across this description of this feature, AuditTrail Tables: https://docs.oracle.com/cd/A60725_05/html/comnls/us/fnd/auddat02.htm

Our project needs to track changes on a per table basis, so this feature seems perfect.
We use the 12c version of the database.

Is audit trail tables still the way to go or is this built into unified audit?
Can we enable it through some sql
statements, and if so, is there a guide for that? Or do we need to
enable it through a tool?

The link that you have provided is related to Oracle Application not the database. The solution being explained on that website is the way to audit changes from application side.

In Oracle database, we don't have such tables to hold changes.

  Is audit trail tables still the way to go or is this built into unified audit?

No, in unified auditing there is only one view called UNIFIED_AUDIT_TRAIL to display all the data related to the audit.

  Can we enable it through some sql statements, and if so, is there a guide for that? Or do we need to enable it through a tool?

Again, the link that you have referenced is related to Oracle Application not the Oracle database and there is way to do it in it. Audit Installations Window.

Better approach, from database side, is using Oracle Flashback Data Archive.

This article from Oracle Magazine explains why Flashback Data Archive is a better way as compared to manual auditing using audit tables and triggers to track changes.

A Fresh Look at Auditing Row Changes


How SQL Server handles concurrent requests? Suppose a SQL Server table has two MailID columns, with max value = 5.

If two different users at the same time execute insert queries that try to insert Max(MailId)+1 into the MailID column, is it possible that both new rows may get the value 6?

I want to learn how SQL Server internally handles more than one request at the same time.

Yes, it's possible (as per JNK's answer: 2 overlapping MAX reads, both get 6, one fails with duplicate assuming a constraint)

Use IDENTITY columns for this very reason.

If you have some odd notion to not use IDENTITY, then you have to use lock granularity (TABLOCKX ) or semaphores (sp_getapplock) to restrict concurrency and allow only one process to run MAX at a time.

Edit:

Two requests that come in at the same time (or very close to it) run concurrently. They are both separate from each other but overlapping.

So each process will read the table using MAX and both get 6. At this point, both processes and reading and the INSERTs have not started yet.

The leading processes INSERTs 6, closely followed by the lagging process. The lagging process will get the unique key violation (you have uniqueness, right?)

If the gap between processes is enough so that the 2nd read happens after the 1st INSERT you're OK.

However, if you have enough calls that you have this risk of duplicate, then using locks/semaphores to decrease concurrency is madness...


SQL Select query to include each row a number of times This is probably easy to do (I hope), but my experiments with OUTER JOIN and CROSS JOIN etc do not give the results I am after, so here's the question:

I need to do do a projection on fields in a table for each day of the week, where the day of the week represents a certain weight. I assumed a number table would help, but how?

Input table:

Key  prc   fixweight
1   23.4   0.2
2   78.1   0.6
3    8.7   0.3

Required output (left out: weight calculation on the columns, based on the day of the week):

1   23.4   0.2   1
1   23.4   0.2   2
1   23.4   0.2   3
1   23.4   0.2   4
1   23.4   0.2   5
1   23.4   0.2   6
1   23.4   0.2   7
2   78.1   0.6   1
2   78.1   0.6   2
2   78.1   0.6   3
2   78.1   0.6   4
2   78.1   0.6   5
2   78.1   0.6   6
2   78.1   0.6   7
3    8.7   0.3   1
3    8.7   0.3   2
3    8.7   0.3   3
3    8.7   0.3   4
3    8.7   0.3   5
3    8.7   0.3   6
3    8.7   0.3   7

Silly, I just misinterpreted the results by using wrong ordering. The CROSS JOIN was indeed the correct approach. Here's what I did (please do correct me if this is not the proper approach to take):

WITH week(day) AS
(
  SELECT TOP (7) number + 1
  FROM [master].dbo.spt_values
  WHERE [type] = N'P' ORDER BY number
)

SELECT key, prc, fixweight, week.day
  FROM weights
  CROSS JOIN week
  ORDER BY key

As have been suggested by the comments: use spt_values only as a quick hack for ad-hoc queries or testing, in production you should use a number table or, if the list of numbers is so slow, you can use ypercube's solution:

SELECT key, prc, fixweight, week.day
  FROM weights
  CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7)) AS week(day)
  ORDER BY key


Repository for organizing company SQL statements? We have a few people here that use SQL and have quite a few queries we each individually use and share.  I'm wondering if there's a solution to store all of these statements in a central repository where they can be used and copied but not modified. We did consider a folder on a network drive and storing the .sql files, but were wondering if there was a better solution out there.

We've used SVN for this, seems to be quite popular as several companies I've spent time with used it. You should be able to control who can and cannot write/update files, as well as who can see what. And one of the more popular clients is Tortoise, as vercelli mentions.


How to copy the postgresql's xlog files safely I mean how to safely copy xlog files when postgresql is running? Any reply will be appreciated.

The best (and recommended) way to do it is to configure the database server to copy them via the archive_command configuration variable. See also the chapter Setting up WAL Archiving.

If you are hell bent on copying them manually then another way might be to look at the contents of the archive_status subdirectory. When you see a file in this directory with the suffix .ready it means that the corresponding WAL file is completed and should be safe to copy. However I don't think this would happen unless WAL archiving was turned on in the first place - if it is not turned on then postgres will just delete the WAL files soon after they are written (which will not give you much chance to copy them!)


Are GIST Index point geometry useful to speed up spatial query? I know the question is a bit generic.
I have multiple tables with point geometries and a GIST Index eating many gigabits of space.
From what I understrand about Gist Index on polygons or polylines, it will store the bounding box and computes a first calculation on the bounding box instead of the full geometry resulting in faster processing.
But what about point geometry?
Will it to anything more that ordering the points spatially?
Thanks

A GiST index on points can be very useful. It will speed up queries with st_contains, st_overlaps and similar functions in the WHERE condition, where one of the arguments is the indexed column.


De-duplicate rows in place vs de-duplicate on transfer Doing what: Loading data from many flat table CSV files into their final destination, a "Target" table.

Using: TSQL / SQL Server 2008

Goals:
Don't include "duplicate" rows
What are duplicates in this context? Two scenarios:

For some tables, if two rows have the same value in all columns. ex  "*"
For other tables, if two rows have the same value in
some columns  ex. "COL_A, COL_B, COL_C"

I know which tables fall into which scenario, and in scenario 2, which columns are the some.

Current Strategy:

Load "raw" data in batches (i.e. 1-5.csv, 5-10.csv, etc) into separate "Staging"
database
Dynamically create a TSQL script that transfers from
staging to target removing duplicates both...

Within the staged data
itself
And between the staged data, and the target database where
it's being transferred

Execute the transfer TSQL script (in batches corresponding to each Staging Database) to move to a single Target database

For each of the two scenarios (i.e. whether a Table's duplicates is based on all columns (1) or some columns (2)), I have a different transfer query. Here is a pattern of each, respectively:

INSERT INTO TARGET.TABLE1 SELECT * FROM STAGING.TABLE1 EXCEPT SELECT * FROM TARGET.TABLE1
INSERT INTO TARGET.TABLE2
SELECT * FROM (
SELECT STAGING.*,
ROW_NUMBER() OVER (PARTITION BY STAGING.COL_A, STAGING.COL_B, STAGING.COL_C) AS ROW_NUMBER
FROM STAGING.TABLE2 as STAGING
LEFT OUTER JOIN TARGET.TABLE2 TARGET
ON ISNULL(STAGING.COL_A, TARGET.COL_A) IS NULL OR STAGING.COL_A = TARGET.COL_A
{... repeat for COL_B and COL_C }
WHERE TARGET.COL_A IS NULL)
as T WHERE ROW_NUMBER = 1

I can add unique constraints if that would help.

My questions:

Is there anything wrong with overall approach of using staging databases to store "raw data", and transferring batches of that stage into a target, eliminating duplicates within stage and between stage+target during the transfer.

I was thinking it might be more efficient to just load all raw data into its final destination, and "de-duplicate" "in-place" (though I don't know what this looks like)

If the general approach is sound, are the above queries OK? Is "EXISTS" a safe clause for scenario 1? Is the ROW_NUMBER PARTITION BY and JOIN BY the some columns a fast way to avoid transferring duplicates in scenario 2?

Is there anything wrong with overall approach of using staging
  databases to store "raw data", and transferring batches of that stage
  into a target, eliminating duplicates within stage and between
  stage+target during the transfer.

This is a standard and typically the best approach. Staging should be a fast, logicless "ETL" step. Get into your OLTP source systems, get your data, and get out! The only reason to spend time doing logic on the source query is if it will result in a total reduced load on the OLTP. e.g. Incremental logic on a 10million row table may result in a faster query and shorter locks than if you try and do a dumb load of 10million rows every execution.

If you don't have resource contention issues (like a flat file) you can still maintain this approach, such that all of your Transform operations occur in a DB and ETL tool for efficiency, maintenance, and consolidation of ETL logic. Nothing like tricking another dev by leaving a little key piece of logic in the staging steps while the majority of data transformation resides elsewhere.

  If the general approach is sound, are the above queries OK? Is
  "EXISTS" a safe clause for scenario 1? Is the ROW_NUMBER PARTITION BY
  and JOIN BY the some columns a fast way to avoid transferring
  duplicates in scenario 2?

I personally love Window Functions as they are performant with clean, concise code. EXCEPT is also, typically, a very efficient operator and you are likely safe using it. There's nothing immediately wrong with your approach, so I'd save optimization until it actually becomes an issue. Since you used the word "safe" just double check the rules and how they apply to your data - you may get unexpected results if you don't account for how EXCEPT operates.


"Permission denied" in Postgres 12 trying to use to_regclass() on table partitions I have a partitioned table public.events owned by user web. Running this query:
SELECT to_regclass('public.events_52642b42e4b0889a77dcd6db')

(to confirm that a partition is present for a given partition key) I get:
ERROR:  permission denied for schema public
SQL state: 42501

If I run the same command as the postgres user (superuser) I get the expected result:
public.events_52642b42e4b0889a77dcd6db

I likewise get the same result if I make the web user a superuser, however that's not a viable approach.  This was working under PG 11, not sure what changed and the docs don't provide detail (that I could find anyway) as far as what permissions are required for the "System Catalog Information Functions".  What am I missing?

The user that got the error does not have the USAGE permission on schema PUBLIC.
I recommend to grant that to everyone:
GRANT USAGE ON SCHEMA public TO PUBLIC;


Undocumented Wait Type QDS Loaddb and an unusable database Has anyone ever encountered the wait type QDS_Loaddb? I have found zero documentation for this wait. The description on the microsoft site says TBD.

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql

I have an unusable database that shows QDS_Loaddb waits any time a query is run. It doesn't matter if the query is against system tables or user tables. The wait is the same QDS_Loaddb.

If I knew what this wait meant, I could have a shot at fixing it.

Has anyone ever encountered the wait type QDS_Loaddb?

I have not, however I did some digging.

  I have found zero documentation for this wait. The description on the microsoft site says TBD.

Disclaimer: This is not meant to be an official documentation or replace any documentation that the official team publishes. It may or may not contain errors and is not meant to be an official source.

QDS_LoadDB is a wait type, associated with (you guessed it) Query Store. This is the wait type that will be encountered when Query Store is starting up for a specific database. Once startup is finished the wait should go away.

While query store is starting up, many different things happen... one of these seems to block queries against the database. Eventually, when startup is completed, those queries will be signaled and would continue to execute.

Please note that the above is a simplification of many processes and is not meant to be an exhaustive look at Query Store components.

  I have an unusable database that shows QDS_Loaddb waits any time a query is run. It doesn't matter if the query is against system tables or user tables. The wait is the same QDS_Loaddb.

Part of this I explain above but let me go a little further.

First, thank you for the comments explaining a few things about what you did and the environment. Sadly, since this was production, immediate and drastic measures were taken. Also, there was no hint of any errors in the errorlog, which is odd.

What I believe happened is the database was brought online and Query Store was initialized. There was a failure of some sort (which we won't know now, why) on the initialization of Query Store which was suppressed from the errorlog (again, no idea why). Since the initialization fails, the queries are never signaled and thus you get into the state you are in. Again, this is my assumption of what happened based on what I looked into.

The interesting item is that there should be a timeout period, where if Query Store isn't loaded within said timeout - everything should just continue on and not wait any longer. You should have easily hit this timeout - which makes we not exactly sure this is what happened... again, though, not too much to go on.

There are a few extended events that we can monitor to see if this truly was the case or not.

query_store_load_started
query_store_loaded
query_store_database_initialization_failed
query_store_catch_exception
query_store_unloaded

So what is the fix?

Unfortunately it is very hard to say why it happened - and even if my assumption was correct, we'd need to know what error was being swallowed. Since we have neither of those, and just a wait type, I'm afraid the answer is "I don't know".

Having said that, since it has to deal with Query Store (and if my assumption is correct) then turning off Query Store - without digging into the issue further - would be the only good route to go.

Disclaimer: I work for Microsoft

If you run into this again (or anyone for that matter) I would highly advise you open up a support case with Microsoft.


What type of SQL Server Replication fits our scenario? Merge or Transactional? Background

In our current setup, we have a single SQL Server 2005 instance on our remotely hosted web server.  We additionally have another (non-MSSQL) database which we use for our POS system, which automatically updates the web server when things (such as product information) changes.  This has two problems:

Connections can be slow, so we can't easily work with local copies of the code
Sometimes we can't reach the web database (even though the site still works), which causes the POS system to hang.

The solution I'm working towards is setting up a second SQL Server instance (2005 or 2008) locally at the corporate headquarters, directing the POS system at it, and using some form of Replication to sync changes between them.  However, I can't tell whether we should use merge replication or transactional.

Question

Will Merge or Transactional replication serve us better?

Our requirements are:

Update product data at HQ and push it to the website as read-only.
Update order data on the website and push it to HQ

Sync small changes from HQ back to website (order status)

No disruption in availability on either end when communication is impossible

i.e. The HQ can still access and update our copy, and the website can update its, and when communication is restored, changes will be synced.

(Optional) An easy way to make a copy of the HQ database with the replication removed, for code testing purposes.

Which one is the better fit for our requirements?

A few thoughts. It sounds to me like you may be able to get away with a couple options if replication is what you end up with. First a word of caution - Replication isn't something that should just be configured in production and enabled/used unless you have some experience with it. This becomes truer with more important workloads and busier systems. Replication can cause headaches if not properly done. I'd invest in mentoring/training/consulting to help you along the path from folks who have been burned by mistakes already.

That said. It looks to me like your requirements don't seem to have any 2-way changes that need to be synced up or merged. It looks like you have a bunch of things that need to go one directional. Orders never come in locally, only on the website. Order status never gets generated on the website, only consumed, etc.

If that is the case - this sounds like Transaction Replication to me. Merge is more for when you need to have multiple versions that can make changes and receive changes at the same time. The tired old Microsoft example still works - a bunch of sales folk all with a local app that does lead management and a big central server. They can get their leads when in the home office and get data, then they work disconnected and sync up their changes. With Merge you have to deal with conflicts (if the same data is modified in two places, who wins?) Transactional is more what is used when you have changes going in one direction (but you can have it go bidirectional here too even).

And you can set replication up to have various tables included or not included in the publication as articles.

A couple links to give you a bit more info:

Transactional Replication Overview

Merge Replication Overview

Now if you have data that can be updated on each and needs to be "merged" then merge may be a better answer. When I work with replication - I prefer to stay as close to Transactional as I can, though. It is easier to administer and there is no conflict resolution worries if you don't need it.

Depending on your volume - if it is low enough - and your latency - if some lag time is acceptable - you might also consider something more programatic or ETL like. Perhaps staging data and sending it over in batches. Sending messages across as data changes in one side, etc.


Need to get identify all tables and columns in a SQL Query I have a massive task to go through hundreds of SQL scripts and identify the Table and Columns in each query as we are updating our system and some of the columns and tables may be renamed or disappear completely.

Is there any product of script that anyone can recommend that will allow me to do this, taking into consideration that whatever program/script is used will need to take into account alias as well as ensuring that it can differentiate between blocks of select code or union.

I have hundreds of queries being used for reports. I need to go through each query and identify tables and columns that are being used within each query.

not "ready to use" solution, but may be is what You need:
http://www.sqlparser.com

description

as I explain in comment, this is framework, it not ready to use solution, but it has very good set of code examples, as well as live-demo.

Live demo

USE [Audit]
GO
ALTER PROCEDURE [dbo].[ReportData3]
    -- Add the parameters for the stored procedure here
    @PERNR int,
    @PERID int,
    @SNAME int,
    @DateSt date,
    @DateFin date,
    @GROSS_PAY bit

AS
BEGIN
    SET NOCOUNT ON;

IF @GROSS_PAY = 0

   IF @PERNR IS NOT NULL
   SELECT        PERNR, SNAME, WEEK_BEGIN_DATE, PERID, PLSTX, STLX, VDSK1_TEXT, HIRE_DATE,
                       WEEK_ENDING_DATE, WOSTD, HR_REG, REG_PAY, HR_HOL, HOL_PAY, HR_VAC, VAC_PAY, HR_SIC, SIC_PAY, HR_OVT, OVT_PAY, HR_PRE, PRE_PAY, HR_OTH,
                       OTH_PAY, HR_TOT_WORKED, HR_STR, STR_PAY, HR_TOT, TOT_PAY, Subtotal, RATR_OF_PAY_HORLY, RATR_OF_PAY_WEEKLY, GROSS_PAY, PAYDT,
                       AVG_STRAIGHT, AVG_TOTAL, AVG_GROSS, RESULT, SEQNR, TERMDT
   FROM            data_hist
   WHERE WEEK_ENDING_DATE >= @DateSt AND WEEK_ENDING_DATE <= @DateFin and

-- DELETED 118 rows of code

Result of parsing:

Tables:
data_hist
Fields:
data_hist.AVG_GROSS
data_hist.AVG_STRAIGHT
data_hist.AVG_TOTAL
data_hist.GROSS_PAY
data_hist.HIRE_DATE
data_hist.HOL_PAY
data_hist.HR_HOL
data_hist.HR_OTH
data_hist.HR_OVT
data_hist.HR_PRE
data_hist.HR_REG
data_hist.HR_SIC
data_hist.HR_STR
data_hist.HR_TOT
data_hist.HR_TOT_WORKED
data_hist.HR_VAC
data_hist.OTH_PAY
data_hist.OVT_PAY
data_hist.PAYDT
data_hist.PERID
data_hist.PERNR
data_hist.PLSTX
data_hist.PRE_PAY
data_hist.RATR_OF_PAY_HORLY
data_hist.RATR_OF_PAY_WEEKLY
data_hist.REG_PAY
data_hist.RESULT
data_hist.SEQNR
data_hist.SIC_PAY
data_hist.SNAME
data_hist.STLX
data_hist.STR_PAY
data_hist.Subtotal
data_hist.TERMDT
data_hist.TOT_PAY
data_hist.VAC_PAY
data_hist.VDSK1_TEXT
data_hist.WEEK_BEGIN_DATE
data_hist.WEEK_ENDING_DATE
data_hist.WOSTD


Why is IDENTITY_INSERT ON only allowed on one table at a time? It is the case that IDENTITY_INSERT can only be set to ON in one database table at a time, but why? Since IDENTITY columns aren't globally unique I can't think of any dangerous situation that could be caused by inserting identities into more than one table at the same time (at least not more dangerous than generally fudging with IDENTITY INSERT).

IDENTITY INSERT should rarely be used but what is the reason for the hard limit?

I think it's to make it difficult.  If you could just leave it on all the time, why even have an identity field?

There are actually a couple of restrictions, though:

It persists only on that connection
It's only settable on one table per connection

Based on the connection-related restrictions, I think it's mainly so it's never accidentally left ON.

Imagine if someone turned on ID insert on one of your tables, then you didn't realize and a (normally) invalid insert was performed that broke the integrity of your ID field?

Bear in mind ID fields can have duplicate values if there is no constraint or unique index in place...


Update same column, same DB with specific records We are migrating a site from one Active Directory system to another but in the third-party app, we need to keep the old accounts, so we renamed the old accounts with "_Old" appended to the last name. Now we need to update a particular column from the old accounts into the new accounts so that their roles in the app match what they were before the migration.
I've figured out how to do it with one person.  I need to take this same logic and apply it to multiple accounts.
Here's the code I've got so far:
Update Staff
Set Role_Id =
    (   SELECT Role_Id
        FROM Staff
        where Staff_First_Name = 'Jane'
            AND Staff_Last_Name = 'Doe_Old')
Where Staff_First_Name = 'Jane'
    AND Staff_Last_Name = 'Doe'

That wiuld only work when you have fixed rules
Like Doe and Doe_Old, so you can link the records.
IF you can't find a rule for the ON clauseit us impossible and you have to go over every record
Edit: it goes actually easier

CREATE tABLE  Staff (Role_Id  INT, Staff_First_Name VARCHAR(10), Staff_Last_Name VARCHAR(10));
INSERT INTO  Staff VALUES (10, 'Jane' , 'Doe_Old');

INSERT INTO  Staff VALUES (NULL, 'Jane', 'Doe')
GO

SELECT * FROM Staff;
GO

Role_Id | Staff_First_Name | Staff_Last_Name
------: | :--------------- | :--------------
     10 | Jane             | Doe_Old
   null | Jane             | Doe

Update s1
Set Role_Id = s2.Role_Id
FROM Staff s1
JOIN  Staff s2 ON s1.Staff_First_Name = s2.Staff_First_Name
                        AND s2.Staff_Last_Name = CONCAT(s1.Staff_Last_Name,'_Old')
GO

1 rows affected

SELECT * FROM Staff;
GO

Role_Id | Staff_First_Name | Staff_Last_Name
------: | :--------------- | :--------------
     10 | Jane             | Doe_Old
     10 | Jane             | Doe

db<>fiddle here
This Version is only necessary if you need some calculation made in th subquery

CREATE tABLE  Staff (Role_Id  INT, Staff_First_Name VARCHAR(10), Staff_Last_Name VARCHAR(10));
INSERT INTO  Staff VALUES (10, 'Jane' , 'Doe_Old');

INSERT INTO  Staff VALUES (NULL, 'Jane', 'Doe')
GO

SELECT * FROM Staff;
GO

Role_Id | Staff_First_Name | Staff_Last_Name
------: | :--------------- | :--------------
     10 | Jane             | Doe_Old
   null | Jane             | Doe

Update Staff
Set Role_Id = s2.Role_Id
FROM Staff s1
JOIN
(SELECT Role_Id,Staff_First_Name,Staff_Last_Name
        FROM Staff) s2 ON s1.Staff_First_Name = s2.Staff_First_Name
                        AND s2.Staff_Last_Name = CONCAT(s1.Staff_Last_Name,'_Old')
GO

SELECT * FROM Staff;
GO

Role_Id | Staff_First_Name | Staff_Last_Name
------: | :--------------- | :--------------
     10 | Jane             | Doe_Old
     10 | Jane             | Doe

db<>fiddle here


MySQL Workbench sync keeps requesting the same changes I am using MySQL Workbench, and when I try to "synchronize" it with my remote database, it keeps detecting some changes to make.

Specifically, the most recurrent ones are:

Dropping foreign keys and re-creating the same ones again
Changing the collation

I was compliant and executed all the queries given to me (and added the semi-colon that they forgot). MySQL didn't complain and executed them.

However it didn't help, I can run it 20 times in a row, it will still ask the same useless changes.

It is possible that this is a bug in MySQL Workbench. You should confirm this with some test database to see if you can replicate the issue and file a bug report.


Identical Log Sequence Numbers Below is the log Sequence number of the log back ups and full back ups

Why are LSN numbers same from 11.30 to 11.45 pm...Was it due to the fact that there was no changes made ? When can i find out that the log sequence has been broken ?

11.30 pm Log first LSN:  325:31307:1,   last LSN: 325:31307:1
11.45 pm Log first LSN:  325:31307:1,   last LSN: 325:31307:1
12.00 am Full first LSN: 325:31409:239, last LSN: 325:31507:1
01.00 AM lOG first LSN:  325:31307:1,   last LSN: 325:42530:1

Was it due to the fact that there was no changes made ?

Yes, it's your case.

  When can i find out that the log sequence has been broken ?

You can do smth like this:

select database_name,
       first_lsn,
       last_lsn,
       lag(last_lsn) over (order by first_lsn) as prev_last_lsn
from msdb..backupset
where type = 'L' and database_name = 'yourDB'
order by first_lsn;

In unbroken log backup chain first_lsn = prev_last_lsn as every next lob backup starts where the previous backup stopped.

In your case the chain is unbroken.

  Having said that, even the action of taking a backup will normally
  cause an increment to the LSN

This is not true.

Log backup does not write anything to the log.

Full and differential do write to the log as they always do checkpoint at the beginning, so they write checkpoint operation to the log.

And in fact, after the Full backup in OP we see that the next log backup has changed its LastLSN.


Optimum design / structure for product data database? What would be the optimum design/structure for product data database, where each product have many variations and many attributes? Attribute sets vary between products, and some attributes have values common for all variations of a given product. A typical example of product variations & attributes table is in the image below:

(The 'ATTRIBUTE X' is here to give you and idea of a different attribute type than for 'Product 1').

Would it be practical to try to 'group' some of the attributes (to save time and minimise errors when creating new product entries)? If so, how complicated would it need to be?

If it's relevant - I'm thinking of using mySQL engine, but I'd be happy to see any applicable example from other engines.

EDIT :
More information: at the moment we have over 400 variations in a spreadsheet, each one about 15-20 attributes, so that would give about 6000-8000 'data points'. It would be great to be able to list all possible values for a given attribute (to create a dropdown menu when adding new entries), and also to have a choice of possible attributes for a given product. It would be great to be able to specify which attributes are applicable for product variations of each product.

All you need is two tables. 1 for Products and 1 for Attributes. You would then have a one to many relationship from the Products table to the Attributes table.

This means that each product can have as many attributes as it needs.

Table structure would look something like this.

Products
Product_Id INT PK Auto Increment
ProductCode VARCHAR(255)

Attriutes
Attribute_Id INT PK Auto Increment
Product_Id INT FK
AttributeName VARCHAR(255)

AttributeValues
AttributeValue_Id INT PK
Attribute_Id INT FK
AttributeValue VARCHAR(255)


How to avoid table crash? Couple of days back I had a table with 7GB of data and it was showing 63MB of overhead in phpMyAdmin and I mistakenly started its optimization and after working of more than 40 mins it corrupted the whole table.

What are the safe practices to avoid table crash?

I would say to try to use a different storage engine like InnoDB.

It sounds like you were using MyISAM which isn't crash safe. If you want to continue using MyISAM and it crashes in the future, you can fix it with an external program called myisamchk.

http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html

 myisamchk --force --key_buffer_size=512M --sort_buffer_size=64M --read_buffer_size=8M --write_buffer_size=8M  /path/to/datadir/*/*.MYI


Convert varchar column to inet I have a table column containing IP addresses, datatype is varchar(255). I want to switch to inet datatype now. Is there a way to convert existing addresses in IP column to inet datatype? Tried:

ALTER TABLE request_logs ALTER COLUMN ip type inet USING ip::inet;
ALTER TABLE request_logs ALTER COLUMN ip type inet USING CAST(ip AS inet);

But no luck, getting:

PG::DatatypeMismatch:
ERROR:  default for column "sign_up_ip" cannot be cast automatically to type inet

All values are '127.0.0.1'. Postgres version is 9.6.9.

The ALTER TABLE statement works for me. The error message indicates that the column sign_up_ip has a DEFAULT value attached to it, which is not valid for the new data type inet. Drop the default of the column first, then convert the data type. Then add a new default (if applicable).

Even if it would seem valid. The manual, chapter "Changing a Column's Data Type":

  PostgreSQL will attempt to convert the column's default value (if any)
  to the new type, as well as any constraints that involve the column.
  But these conversions might fail, or might produce surprising results.
  It's often best to drop any constraints on the column before altering
  its type, and then add back suitably modified constraints afterwards.

There is no cast registered between varchar and inet. That's why you had to add the USING clause with an explicit conversion. Postgres makes it work via input / output conversion. But that's not possible for the saved DEFAULT expression.

You can do it all in a single DDL command to minimize friction with concurrent transactions (if that should matter):

ALTER TABLE request_logs
   ALTER COLUMN sign_up_ip DROP default
 , ALTER COLUMN sign_up_ip type inet USING ip::inet
 , ALTER COLUMN sign_up_ip SET DEFAULT '192.168.1.1';  -- arbitrary example

Using your actual column name sign_up_ip instead of the symbolic ip in your code example. Tested in Postgres 10.

If your column should hold any varchar values not valid as type inet, your conversion still fails, but this time with something like:

ERROR:  invalid input syntax for type inet: "1234.123.123.123"

That's one reason why doing all in a single statement (or at least in a single transaction) is a good idea: Everything is back to the original state after the error and the automatic ROLLBACK.


query using the where clause if variables are not null i am trying to query without success, what i am trying to do is that

  ALTER PROCEDURE [dbo].[spPagination] -- ORDER BY id
  @filterCol NVARCHAR(20) = NULL, --<<<<
  @filterValue NVARCHAR(40) = NULL, --<<<<
  @PageNumber INT,
  @PageSize   INT
AS
BEGIN
  SET NOCOUNT ON;
        SELECT      Emp.id , Emp.email, Emp.[firstName], Emp.[lastName], Emp.[salary], Emp.[startDateWork], Emp.age  , Rol.[name] as Role
    FROM        [dbo].tblEmployees5m    Emp
    inner join  [dbo].[tblRoles]        Rol
    ON          Emp.roleId = Rol.id
    WHERE       @filterCol like '%' + @filterValue + '%' --<<<<
    ORDER BY    id
    OFFSET      @PageSize * (@PageNumber - 1) ROWS
    FETCH NEXT  @PageSize ROWS ONLY OPTION (RECOMPILE);

    select count(1) as totalCount from [dbo].tblEmployees5m
END

i am trying to apply filter to the result, according to @filterCol and @filterValue - if they are not NULL then I want to return the results with the where clause (which don't work now).

ELSE, if there 2 values are NULL then don't apply the where clause.
@filterCol will hold the column name.
@filterValue will hold the column value.

how can I achieve that? there is a better way?

Here's the code:

ALTER PROCEDURE [dbo].[spPagination] -- ORDER BY id
    @filterCol NVARCHAR(20) = NULL, --<<<<
    @filterValue NVARCHAR(40) = NULL, --<<<<
    @PageNumber INT,
    @PageSize   INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = 'SELECT      Emp.id , Emp.email, Emp.[firstName], Emp.[lastName], Emp.[salary], Emp.[startDateWork], Emp.age  , Rol.[name] as Role
    FROM        [dbo].tblEmployees5m    Emp
    inner join  [dbo].[tblRoles]        Rol
    ON          Emp.roleId = Rol.id
    '
    IF @filterCol IS NOT NULL
    BEGIN
        SET @SQL = @SQL + 'WHERE       [' + @filterCol + '] LIKE ''%' + @filterValue + '%'''
    END

    SET @SQL = @SQL + '
    ORDER BY    id
    OFFSET      ' + CAST((@PageSize * (@PageNumber - 1)) AS VARCHAR(50)) + ' ROWS
    FETCH NEXT  ' + CAST(@PageSize AS VARCHAR(25)) + ' ROWS ONLY OPTION (RECOMPILE);'

    EXEC sp_executesql @SQL

    select count(1) as totalCount from [dbo].tblEmployees5m
END

As has been mentioned, dynamic SQL is vulnerable to SQL Injection, so take care when utilising dynamic SQL that you're sufficiently protected against these attacks.

SQL Injection Protection: https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet


Should I use Asynchronous Mirroring? I plan on setting up mirroring on a SQL 2012 database which is currently in production. My concern though is how much it will affect the performance of the principal database.

I am using mirroring for disaster recovery and nobody will require access to the mirrored database except in the event of a problem.

I am planning on doing asynchronous mirroring. Is this a good option?

Database mirroring is more of a high availability solution, not a disaster recovery one. What makes you think synchronous commit will lead to high latency in your environment? Are the principal and mirror server geographically separate?

Asynchronous commit will lead to potential data loss in the event that you need to do a forced service failover, therefore you would need to ensure that your SLA guidelines are congruent with that. BOL reference on Force Service in a Database Mirroring Session:

  Forcing service may involve some data loss. Therefore, force service only if you are willing to risk losing some data in order to restore service to the database immediately.

And equally important, database mirroring is marked for deprecation:

  This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use AlwaysOn Availability Groups instead.

Is there a reason why you don't want to utilize AlwaysOn AG? See BOL for an overview of AlwaysOn Availability Groups.

AlwaysOn AG has all of the capabilities of database mirroring with many more advancements.


Tracking the last execution date of a query My manager is requesting to know when was the last time a number of reports were last ran to see if they are getting used. He requested info from our vendor and the vendor if coming to me for SA level permission to dig through the relative SQL Server.

Obviously, I won't be doing that but I would like to know is there a way to track the last time a specific query has been executed?

This database resides on an old SQL Server 2008 R2 install so I am sure my options are limited if not non-existent especially since he wants to look back as far as 18 months.

Just figured I would ask in case there might be at least a partial way to go back in history and track this information.

When were your servers last booted? Unless you have some form of auditing running, then that is a far back as you will be able to query the sys.dm_exec_query_stats view and sys.dm_exec_sql_text() table-valued function.

You can then use the following statement to see when a statement was last executed:

SELECT sdest.TEXT , sdeqs.last_execution_time, *
FROM   sys.dm_exec_query_stats AS sdeqs
   CROSS APPLY sys.dm_exec_sql_text(sdeqs.sql_handle) AS sdest

However, there are some restrictions with this approach:

Size of Memory reserved for SQL Server instance
Execution of DBCC FREEPROCCACHE
Execution of DBCC FREESESSIONCACHE
Execution of DBCC FREESYSTEMCACHE('ALL')
other possible reasons for SQL Server to free up cache including, but not limited to:

Server reboots
Instance restarts


sqlite 3.4 updating table with data from different table - changed with 3.3 - newbie question All of the other questions on this seem to be before 3.3 when sqlite added an update join feature? Newbie here, so I may even be getting the terminology wrong. I am trying to update the issbn field in table GULLfromLG (currently empty), with data from the issbn field in BIBinfo (field can be empty in a given record).
I am using SQLiteStudio. From looking through the documentation, sqlitetutorial, w3resource, and general web searches I believe the following should work. But it doesn't. The processing happens, is declared successful, but all the fields in GULLfromLG are empty. What am I missing?
    UPDATE GULLfromLG
   SET issbn = (
   select issbn
  FROM BIBinfo
 WHERE bibno = bibno)

I have also tried naming the fields with variations of
GULLfromLG.issbn GULLfromLG.bibno BIBinfo.issbn BIBinfo.bibno
Any help would be greatly appreciated!

You need to gove sqllite the chance to know which column from which table you mean
UPDATE GULLfromLG
   SET issbn = (
   SELECT issbn
  FROM BIBinfo
 WHERE bibno = GULLfromLG.bibno)
[Example](http://sqlfiddle.com/#!5/c6687/1)


Mariadb concurrent insert not working for MYISAM tables I am using mariadb 10.1.13. I have a large MYISAM table ( > 2000000 rows ) which used to have frequent 'INSERT' operations from multiple scripts.
Recently I had to add  a new 'SELECT' query to the same table as a part of another script , which is causing the 'INSERT' query to be locked. I used to 'DELETE' some of the old data to keep the table size from increasing beyond a limit. From MySQL documentation I saw that deletion can cause holes in data which will cause insert to lock.
But now the concurrent_insert is set to 'ALWAYS' so that it will work even if there are holes.
MySQL documentation says
Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.

But still the insert queries seems to be blocked with 'waiting for table level lock' status when SELECT is going on. After 'OPTIMIZE TABLES' is executed it will work without issues but locking happens again after holes are created. I couldn't find any reason for the locking. Nobody seems to have reported such an issue anywhere.
Any help is appreciated.

Face it, MyISAM survives only because it uses table locks for almost every kind of write.  Even a SELECT can get caught up in the middle.
The obscure exception has to do with multiple INSERT that are inserting into a table with AUTO_INCREMENT PRIMARY KEY (and, I suspect, no other UNIQUE keys).  In that case, they can play around at the "end" of the table with less locking.
Switch to InnoDB.
If your goal is to ingest lots of data from lots of sources, here's a technique:  http://mysql.rjweb.org/doc.php/staging_table


Importing CouchBase dump to BigQuery After creating a CouchBase dump and copying it to a Google Storage bucket I tried importing the data to BigQuery.

I failed miserably, apparently the design.json or the files that appear in the same directory as data-0000.cbb are not working when I'm trying the json import.

Do I need to convert the dump somehow?
Is there any other way to migrate the data from CB to BQ?

Thank you all in advance.

I managed to export the couchbase data to CSV, after converting to CSV I discovered the files were corrupted and there were some entries I had to give up on.

When I first tried there was no "Ignore Errors" option in BigQuery so I couldn't import the files at all, after several weeks I tried again and found the "Ignore Errors" option, I immediately imported my files, first each one of them to a single DB, later on combined them together, it looks great now but it was not tested for compliance yet, I lost several records (a little less than 20) out of 80 gigs of data.


How to select a row based on the maximum value of a column, when ties are expected SUBSCRIBER  DATE         TIME            VALUE
aaa         23-04-2015   04:04:07        10
aab         23-04-2015   12:04:32        5
bbb         23-04-2015   01:04:05        20
bbb         23-04-2015   12:04:20        20
bbb         23-04-2015   12:04:31        20
ccc         23-04-2015   05:04:00        10

I need a query to get the subscriber details based on the maximum value (and the minimum time if there are ties on max(value)) for a particular subscriber.

select subscriber, date_column, time_column, value from (
  select
    subscriber, date_column, time_column, value,
    rank() over (partition by subscriber order by value desc, time_column) as r
  from mytab
) where r = 1
and subscriber = 'ccc';


How to enable TLS1.2 for port 1433 I have received vulnerability on Microsoft SQL server 2008 (RTM)

  [high] [1433/tcp/mssql] TLS Version 1.2 Protocol Not Enabled
  TLS v1.2 is not enabled on this port.

Already enabled TLS 1.2 on OS level (Windows Server 2008 R2).

below are the details Product version:
Microsoft SQL server 2008 (RTM) - 10.0.1600.22(x64)
Product level: RTM Product Edition : Express edition(64bit)
Whether I need to go for SQL Server 2008 SP4 10.0.6547.0(First build supports TLS 1.2)

Please let me know how to resolve this issue.

There is this KB article, which lists the required updates you need to install https://support.microsoft.com/en-us/kb/3135244 , which also lists which updates are needed on the client machines trying to connect to a TLS 1.2 enabled SQL Server.

Also, read http://blogs.sqlsentry.com/aaronbertrand/tls-1-2-support-read-first/ , which lists known problems and caveats. It states

  It seems straightforward, but as of today, not all builds will enable you to rush out and convert to TLS 1.2 exclusively


get max serial no. according to sum of its corresponding amount I have a table with data as follows:

s_no  amount
1      500
2      500
3      500
4      500
5      500

I need a query to find the max(s_no) according to the sum(amount)
For e.g. when sum(amount)<=1600 i need 4 as result.I can get the desired output by using cursor but it would be easier if i could use query.. I tried a query as follows:

SELECT MAX(s_no) from table HAVING SUM(amount)<=1600

But it doesn't work. I would appreciate any kind of help.
Thank you.

Another way – that works in versions prior to 2012 – is to use a recursive CTE.

Tested at rextester.com:

WITH ct AS
  ( SELECT TOP (1) s_no, amount, running_total = amount
    FROM tableX
    ORDER BY s_no

    UNION ALL

    SELECT n.s_no, n.amount, n.amount + n.previous_total
    FROM
      ( SELECT t.s_no, t.amount, previous_total = ct.running_total,
               rn = ROW_NUMBER() OVER (ORDER BY t.s_no)
        FROM ct
          JOIN tableX AS t
            ON t.s_no > ct.s_no
        WHERE ct.running_total < 1600
      ) AS n
    WHERE n.rn = 1
  )
SELECT *
FROM ct
WHERE running_total >= 1600
OPTION (MAXRECURSION 0) ;          -- no limit on recursion

The CTE's anchor (the UNION ALL's first leg) selects only the first row. Then in each iteration the CTE's recursive part (the second leg) selects the next row and computes the running total until the sum is 1600 or more.

The ROW_NUMBER() trick and subquery is to overcome the limitation that TOP is not allowed in the recursive parts of CTEs.


Frequent, "random" SQL Server connection timeouts For the last few days our clients have been suffering intermittent connection timeouts to our database server.

Multiple Ajax calls on the same page which connect to the server multiple times are having mixes of success and failure, and at times specific website users are getting connection issues while other users are not from the same site.

I am not sure if the issue lies with the databases or the server itself, but we may be having connection issues to a different database on the same server.

The server is an Amazon Cloud VPC running Windows Server 2008 RC, and the version of SQL Server is SQL Server 2008 R2. I can provide more information if necessary.

Can someone give me any hints on how to go about debugging this? Maybe a few methods to test the connections?

Many thanks.

Basically, when you failed to connect to your SQL Server, the issue
  could be:

  Network issue.
  SQL Server configuration issue.
  Firewall issue.
  Client driver issue.
  Application configuration issue.
  Authentication and logon issue.

I thik you can follow THIS article here for more information.


Can not add list partitions Following is the create table statement that is working as expected. But if I have a table without any partitions and if I try add partitions, it seems that I can not add more than 16 values in it.

   CREATE TABLE `mypart` (
      `part_id` int(11) DEFAULT NULL
    ) ENGINE=MyISAM
    PARTITION BY LIST (part_id)
    (
    PARTITION Jan10 VALUES IN (1,2,3,4,5) ENGINE = MyISAM,
    PARTITION Mar10 VALUES IN (734201,734202,734203,734204,734205,734206,734207,734208,734209,734210,734211,734212,734213,734214,734215,734216, 734217) ENGINE = MyISAM
    );

All the following statements should execute with success and create a table as shown above.

drop table mypart;
create table mypart (`part_id` int) ENGINE=MyISAM ;
alter table mypart partition by list(part_id) (PARTITION Jan10 VALUES IN (1, 2, 3, 4, 5));

mysql> alter table mypart add PARTITION (PARTITION Mar10 VALUES IN (734201, 734202, 734203, 734204, 734205, 734206, 734207, 734208, 734209, 734210, 734211, 734212, 734213,  734214, 734215,734216, 734217  ) ) ;
ERROR 1657 (HY000): Cannot have more than one value for this type of LIST partitioning

At this point, the second alter table statement does not work and hence I can not have the 17 values for Mar10 partition as shown in the first example. If I delete the last value 734217 then the alter table statement is successful!

mysql> alter table mypart add PARTITION (PARTITION Mar10 VALUES IN (734201, 734202, 734203, 734204, 734205, 734206, 734207, 734208, 734209, 734210, 734211, 734212, 734213,  734214, 734215,734216));
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

Is there some limit in the alter table statement that does not applies to create table statement ?

This is a bug that's been fixed in MySQL 5.6.5.

See http://bugs.mysql.com/bug.php?id=62505


MongoDB - Replica sets - Syncing and DC issue I have 3 instances of MongoDB running on my local machine.  One is native and the other two are on a Virtualbox Windows 7 VM.

LocalMongo = My local mongo.
VM1a and VM1b = My two instances of mongo running in my VM.

I was able to correctly setup a replica set within these and verified that they were operating correctly and able to sync to each other properly before I proceeded forward.  This is my test setup for a failure within this system:

Step 1: Start
LocalMongo (offline - the "original primary")
VM1a (online - new primary)
VM1b (online - secondary)

Step 2: Import data
LocalMongo (offline)
VM1a (online - push 1,000 documents into the collection "test")
VM1b (online)

Step 3: Reconnect "original primary" and sync to current primary
LocalMongo (online - secondary during the sync)
VM1a (online)
VM1b (online)

Step 4: Swap the "original primary" back into a primary
LocalMongo (online - primary)
VM1a (online - secondary)
VM1b (online - secondary)

Ok so the two things I noticed which seem like odd behavior to me are the following:

1) Right as the swap happens where LocalMongo becomes the primary again I notice that my connection to all of my MongoDB instances drop for 2-3 seconds.  When I try and run rs.status() I get:
    2014-09-17T14:56:15.632-0400 Socket recv() errno:10053 An established connection was aborted by the software in your host machine. 198.162.55.101:27017
    2014-09-17T14:56:15.647-0400 SocketException: remote: 198.162.55.101:27017 error: 9001 socket exception [RECV_ERROR] server [198.162.55.101:27017]
    2014-09-17T14:56:15.647-0400 DBClientCursor::init call() failed
    2014-09-17T14:56:15.647-0400 Error: error doing query: failed at src/mongo/shell/query.js:81
    2014-09-17T14:56:15.663-0400 trying reconnect to 198.162.55.101:27017 (198.162.55.101) failed
    2014-09-17T14:56:15.679-0400 reconnect 198.162.55.101:27017 (198.162.55.101) ok

Is this normal behavior during the transition where they have to take themselves offline?

2) I noticed that after my LocalMongo became the primary again, VM1b (always stays secondary) points to VM1a as it's syncing target and not the newly elected primary.  This seems bad to me but I'm still fairly new to Mongo so I don't know if this is how the algorithm works when determining syncing targets. Since both VM1a and VM1b exist within the VM is Mongo just determining that it's the "path of least resistance" and that's why it's a sync target?

{
    "_id" : 0,
    "name" : "198.162.55.1:27017",
    "health" : 1,
    "state" : 1,
    "stateStr" : "PRIMARY",
    "uptime" : 70,
    "optime" : Timestamp(1410979849, 637),
    "optimeDate" : ISODate("2014-09-17T18:50:49Z"),
    "lastHeartbeat" : ISODate("2014-09-17T18:56:59Z"),
    "lastHeartbeatRecv" : ISODate("2014-09-17T18:56:59Z"),
    "pingMs" : 1,
    "electionTime" : Timestamp(1410980169, 1),
    "electionDate" : ISODate("2014-09-17T18:56:09Z")
},
{
    "_id" : 1,
    "name" : "198.162.55.101:27017",
    "health" : 1,
    "state" : 2,
    "stateStr" : "SECONDARY",
    "uptime" : 4227,
    "optime" : Timestamp(1410979849, 637),
    "optimeDate" : ISODate("2014-09-17T18:50:49Z"),
    "infoMessage" : "syncing to: 198.162.55.1:27017",
    "self" : true
}
{
    "_id" : 2,
    "name" : "198.162.55.101:27018",
    "health" : 1,
    "state" : 2,
    "stateStr" : "SECONDARY",
    "uptime" : 4227,
    "optime" : Timestamp(1410979849, 637),
    "optimeDate" : ISODate("2014-09-17T18:50:49Z"),
    "lastHeartbeat" : ISODate("2014-09-17T18:56:59Z"),
    "lastHeartbeatRecv" : ISODate("2014-09-17T18:57:01Z"),
    "pingMs" : 1,
    "lastHeartbeatMessage" : "syncing to: 198.162.55.101:270

    "syncingTo" : "198.162.55.101:27017"
}

My only concern in this situation is what happens if VM1a goes down right after LocalMongo comes up?  Wouldn't that mean that the only place where data is actually being stored would be on the primary and that it would create a broken link where VM1b (which never went down) wouldn't sync to anything?

Thanks!

Is this normal behavior during the transition where they have to take themselves offline?

It is expected that your replica set instances will drop network connections when there is a change in primary.

  My only concern in this situation is what happens if VM1a goes down right after LocalMongo comes up? Wouldn't that mean that the only place where data is actually being stored would be on the primary and that it would create a broken link where VM1b (which never went down) wouldn't sync to anything?

Replica set secondaries can sync off of other secondaries, which can help reduce load on your primary. If your secondaries sync source goes down, it will reevaluate and change sync source to an available member.


Sort spills to tempdb but estimated rows equals to actual rows On a SQL Server 2016 SP2 with max memory set to 25GB we have a query that executes about 80 times in a minute. The query spills about 4000 pages to tempdb. This causes for a lot of IO on the disk of tempdb.

When you take a look at the query plan (simplied query) you'll see that the number of estimated rows is equal to the number of actual rows but still spills occur. So out-of-date statistics can't be the cause of the problem.

I did some testing and following query spills to Tempdb:

select id --uniqueidentifier
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)

But if I select a different column no spills occur:

select startdate --datetime
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)

So I tried to 'enlarge' the size of the id column:

select CONVERT(nvarchar(512),id)
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)

Then also no spilling occurs.

Why is the uniqueidentifier spilling to tempdb and a datatime column not?
When I delete about 20000 records then also no spilling happens when I select the id column.

With following script you can reproduce the problem:

CREATE TABLE SortProblem
  (
     id             UNIQUEIDENTIFIER,
     startdate      DATETIME,
     sequencenumber BIGINT,
     status         VARCHAR(50),
     PRIMARY KEY CLUSTERED(id)
  )

SET nocount ON;

WITH nums(num)
     AS (SELECT TOP 103000 ROW_NUMBER()
                             OVER (
                               ORDER BY 1/0)
         FROM   sys.all_objects o1,
                sys.all_objects o2)
INSERT INTO SortProblem
SELECT newid(),
       DATEADD(millisecond, num, GETDATE()),
       num,
       CASE
         WHEN num <= 100000 THEN 'A'
         WHEN num <= 101000 THEN 'B'
         WHEN num <= 102000 THEN 'C'
         WHEN num <= 103000 THEN 'D'
       END
FROM   nums

CREATE NONCLUSTERED INDEX [IX_Status]
  ON [dbo].[SortProblem]([status] ASC)
  INCLUDE ([sequencenumber])

Enable trace flag 7470.

FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct

As I wrote in answer to Query Plan question:

  This trace flag corrects an oversight in the calculation. It is quite safe to use, and in my opinion ought to be on by default. The change is protected by a trace flag simply to avoid unexpected plan changes.


Query Not Summing Appropriately I adapted this syntax to fit my need from a post from @ypercubeᵀᴹ - but in my instance shown below - the Select statement only returns the top most value for the patientID it does not SUM() the value for each DoctorName listed.  In this instance, I want the SUM() for TotalBill by each DoctorName

What is set-up wrong in the query below?

Declare @TotalBill Table
(
     doctorname varchar(100)
    ,patientid varchar(10)
    ,totalbill decimal(10,2)
    ,vdate date
)

Declare @HospitalInfo Table
(
     doctorname varchar(100)
    ,patientid varchar(10)
    ,vdate date
)
INSERT INTO @TotalBill (doctorname, totalbill, patientid, vdate) VALUES
('Doctor Z',    '11.48',    '4321',         '2017-01-25'),
('Doctor Z',    '11804.00', '3431',         '2017-01-31'),
('Doctor Z',    '879.40',   '1111',         '2017-01-26'),
('Doctor Z',    '11.24',    '0089',         '2017-01-26'),
('Doctor A',    '11.24',    '6654',         '2017-01-26'),
('Doctor A',    '55.11',    '0011',         '2017-01-26'),
('Doctor A',    '66.24',    '5466',         '2017-01-26'),
('Doctor A',    '22.24',    '1098',         '2017-01-26'),
('Doctor A',    '13.24',    '9987',         '2017-01-26')

Insert Into @HospitalInfo (doctorname, patientid, vdate) Values
('Doctor Z',    '1111',         '2017-01-26'),
('Doctor Z',    '0089',         '2017-01-26'),
('Doctor Z',    '3431',         '2017-01-31'),
('Doctor Z',    '4321',         '2017-01-25'),
('Doctor A',    '6654',         '2017-01-26'),
('Doctor A',    '0011',         '2017-01-26'),
('Doctor A',    '5466',         '2017-01-26'),
('Doctor A',    '1098',         '2017-01-26'),
('Doctor A',    '9987',         '2017-01-26')

Declare @Startdate datetime = '01/01/2017', @Enddate datetime = '01/31/2017'
Select
a.[doctorname]
,a.[patientid]
,[Total Bill] = COALESCE(sumTB,0)
FROM
    ( SELECT [doctorname]
             ,patientid
             ,vdate
             ,rn = ROW_NUMBER() OVER (PARTITION BY a.[doctorname]
                                     ORDER BY [patientid])
      FROM @HospitalInfo As a
    ) As a
    OUTER APPLY
    ( SELECT sumTB = SUM(b.[totalbill])
      FROM @TotalBill b
      WHERE a.patientid = b.patientid
      AND a.[doctorname] = b.[doctorname]
        AND a.rn = 1
    ) AS b
WHERE CAST([vdate] As Date) BETWEEN @Startdate AND @Enddate

EDIT
My desired output is like the image below.  SUM the totalbill - and only show it for ONE patientID.  I do not care if it is a random id or the Max() or Min() as longn as it is only one

You seem to be making the query a lot more complicated than it needs to be, and while I agree with Erik that the results you want don't seem to make any logical sense (if you want total bills to a doctor, then why have individual rows for each patient?), here is a simpler way:

DECLARE @Startdate date = '20170101',
        @Enddate   date = '20170131';

;WITH data AS
(
  SELECT doctorname, patientid, bill = SUM(totalbill) OVER (PARTITION BY doctorname),
    r = ROW_NUMBER() OVER (PARTITION BY doctorname ORDER BY doctorname)
  FROM @TotalBill
  WHERE vdate >= @Startdate
    AND vdate <  DATEADD(DAY,1,@Enddate)
)
SELECT doctorname, patientid, totalbill = COALESCE(CASE r WHEN 1 THEN bill END, 0)
FROM data;

A couple of notes:

don't use datetime for your variables when the column is date
don't use messy and unnecessary CONVERT() functions (why convert date to date?)
don't use unsafe regional formats like 'mm/dd/yyyy' - further reading
don't use BETWEEN for any range queries - further reading

(While it's true that BETWEEN works fine for queries only involving date queries, it is far too easy to copy that logic elsewhere, where it doesn't work -
just be consistent.)

also see this post about "dating responsibly"


MySQL Slave has unmanagbly higher IO load than master A MySQL slave having a higher disk load than the master or not being able to keep up is not unusual, nor are questions about it on Stack Exchange, but I did want to run my issue by here, because in my case, the difference is so great.

(Edit3: by now, I have figured out that a large part of this is that I DELETE FROM x LIMIT y a lot, in binlog MIXED mode. See below.)

My MySQL slave basically has 100% disk load all the time, and cannot get better than being a day behind. Here are some Munin graphs:

Master disk utilization:

Slave disk utilization:

The first peak in the graph (the 50% peak) is when I loaded the 200GB master dump into the slave. Then on the 20th, it started processing bin logs.

I have tried both STATEMENT and MIXED format; there wasn't much difference. In the end, I can't use STATEMENT, because there are quite a lot of unsafe statements.

The master gets thousands of new rows per minute, and also deletes expired rows daily with DELETE ... limit x repeated a lot (in a script). However, as you can see, that doesn't face the master much. From the moment replication was started (the 20th in the graph), the master graph shows less load (because it no longer does daily mysqldump), yet the slave is perpetually busy.

The Munin graphs for queries per second show approximately the same amount. Master sql queries:

Slave sql queries:

The master:

virtual machine, 2.4 GHz, 16 GB RAM, HDD storage. RAID10, but since it's an online service, I don't know the details.
MySQL 5.5
query_cache_limit = 4M
query_cache_size = 512M
innodb_buffer_pool_size = 4G
innodb_log_file_size = 1024M
innodb_flush_method=O_DIRECT

The slave:

Physical machine, 4 GB RAM, HDD.
Quad core Intel(R) Xeon(R) CPU E5405  @ 2.00GHz
3 drive RAID1 (WDC WD2003FYYS; 2 TB)
MySQL 5.5
query_cache_limit = 4M
query_cache_size = 512M
innodb_buffer_pool_size = 1G
innodb_log_file_size = 1024M
innodb_flush_method=O_DIRECT

This can't be normal, right? How can I properly debug this? I can't really do explain analyze on the binlog...

edit: When I do (while replication is running and while it's making a sqldump):

dd if=/dev/zero of=big3 bs=4096 bs=1M count=1000 oflag=direct

I get:

1000+0 records in
1000+0 records out
1048576000 bytes (1.0 GB) copied, 51.0737 s, 20.5 MB/s

iostat -x 1 normally show about 5 MB/s of writing, even less reading. Dd'ing increases this to about 25. Apparently, the disks have throughput to spare. I guess MySQL must be causing seeks.

Edit2: I thing that might be useful: the thousands of new rows per minute are added using multi row insert statements. We used to use multiple single row statements, and using multi row statements improved performance drastically. How is that deal with in MIXED binlog format? When I still used STATEMENT, they weren't mentioned as unsafe, so I guess they must still be processed as multi row inserts?

Edit3: What I mentioned above, is that there is also a daily script that deletes old data on the master, a couple of million rows per day. I disabled that for a few days, and the slave was able to catch up. Because we're dealing with so many records, I use a LIMIT clause on the DELETE, turning it into an usafe statement. In the MIXED log format, this statement seems to be transferred as STATEMENT, because otherwise it would be unsafe.

Can I force this statement being logged as STATEMENT? I already turned it into an ordered delete, and I wouldn't even really care about the unsafety of it (but there are other queries for which I do care, so I can't use STATEMENT as global binlog format).

Munin's formula for "device utilization" is (milliseconds spent doing I/O)/second, which assumes you can't do any I/O in parallel, so I'm not sure that this is a meaningful metric. However, you do clearly have a genuine performance issue here since replication can't stay caught up.

Is there a significant difference in the I/O subsystems between the servers?

The way I would approach this is to compare the I/O subsystems and then if there isn't a glaring difference in architecture, run some I/O benchmarking at a quiet time with a tool like bonnie++ or fio to narrow down the difference in performance.

Note that the slave likely has an equal write workload to the master because it has replay all the writes, plus it may have a considerable read workload (for reading any parts of the database that have to be updated) and might be less efficient for reads than the master because it has less RAM for caching. In this situation I would not necessarily expect the slave to be able to catch up because it's entirely possible for the master to generate work at a rate faster than the slave can consume it.


DBCC unique cluster index corruption I am very new to the world of DB maintenance and recently ran to an issue that will appreciate your advice on.

I have a Database that the earliest solid backup file I can see without any corruption belongs to 7 months ago. I have run DBCC CHECK DB for my current database and I see there is issues with the indexes.

When I do a compare between what has changed in database I realized that someone added a “Unique cluster index” to a child table. (Not sure how to determine if it is a child table or parent table but I assume it is a child table)

My question is this: is it possible to break the records in child/parent tables relationship with applying “unique cluster index”?

I learned what cluster index and non-cluster index are but couldn’t find a solid document that tells me if applying unique cluster index can impact other related tables or not?

I appreciate your help in advanced and will be grateful if you can point me to any link to learn more about this subject.

Creating a new index, clustered or not, will not break existing foreign key relationships.

Be aware that creating a new unique index will of course fail if duplicate keys exist.


Are Database duplication and Mirroring one and the same? I am going over the concepts of Distributed database management system. I read about duplication and replication. Then there is also something called Mirroring. I googled to find if duplication is the same as mirroring but could not find. Are they same?

How is duplication different from snapshot replication?

Duplicate Database
It is separate database contains all or subset of the data of Source Database.
Its purpose varies like:

Test backup/recovery procedure
Test upgrade of new release
Test effect of DB performance
Generate reports
Export data table and import in production environment if inadvertently dropped

Snapshot Replication
Here data is replicated at specified time by copying data changes from Source Database (Publisher) to receiving database (Subscriber)
Its purpose varies like:

If data change is infrequent
If database is of small size or data change occurs in large amount


Changing replication snapshot folder, require re-snapshot? We have SQL Server 2012 SP2 running on Windows Server 2012.

We need to change the snapshot folder for all (including default) transaction replication publications.

I have read that this means the replication needs to be re-initialized?

Why is the case? If I update table msdb.dbo.MSdistpublishers with the new working_directory then I can't see why the replication should need to be re-initialized?

Any recommendations appreciated.

You can change in this way

exec sp_changepublication  @publication
@property='snapshot_in_defaultfolder'
@value='0'
@force_invalidate_snapshot=0,--always remmeber that
@force_reinit_subscription=0--always remmeber that

exec sp_changepublication  @publication
@property='alt_snapshot_folder'
@value='C:\your_dir'
@force_invalidate_snapshot=0,--always remmeber that
@force_reinit_subscription=0--always remmeber that


SQL Server 2005: Full-text search catalog design I'm new to full-text searches, and am curious about how to properly design the full-text catalogs.  I have an "articles" table.  I would like to do full-text search on both the "title" and the "body" at the same time.

Does this require two separate catalogs?  If not, is it better to use two separate catalogs, or just one?  If another unrelated table requires full-text indexing, should it be part of the same catalog, or a different one?

Just asked a Microsoft Certified Trainer during a developer course for SQL Server.  He suggested using a single catalog, and having the two indexes, "title" and "body" in the same catalog.

The only thing to watch is with too many indexes in the same catalog, there is a greater chance for fragmentation within the catalog.  With two columns though, this should not be a concern.