Linked server to Oracle doesn't use filters. Are there any performance changes in different versions of SQL Server? My customer somehow only has SQL Server 2005 instance. It has linked server to oracle database. I've noticed that sql server doesn't send any filters to oracle side and apply them only after it retrieves all data from oracle. Which is quite opposite to work of sql server - sql server linked server.

I'm wondering - could it be because of sql server version? Maybe 2005 is to old for that?

For example:

SELECT * FROM OPENQUERY (ORCLLS, 'Select * from ADMIN.IE_Docs_Subscrs where F_DRSO = ''84c9e2c5-2634-4f6a-b1b7-e1505d4a1c39''')

Result: 0 seconds. 24 rows

SELECT * FROM OPENQUERY (ORCLLS, 'Select * from ADMIN.IE_Docs_Subscrs')
WHERE  F_DRSO = '84c9e2c5-2634-4f6a-b1b7-e1505d4a1c39'

Result: 1m 33 seconds. 24 rows

EDITED

Got it. It's because of OPENQUERY. But how can I turn it into four-part name?

By default SQL Server doesn't believe that string comparisons will have the same semantics on the linked server that they have locally.  So it can't "push the predicate" to the remote server.

In particular Oracle is always case-sensitive, and SQL Server by default is not.  So in SQL Server you get the same results with

WHERE  F_DRSO = '84c9e2c5-2634-4f6a-b1b7-e1505d4a1c39'

and

WHERE  F_DRSO = '84C9e2E5-2634-4f6A-B1B7-E1505D4A1C39'

But not in Oracle.

You can set the "Collation Compatible" setting of the linked server to allow SQL Server to push predicates.

But I really prefer to use OPENQUERY or EXEC ('sql') AT LinkedServer and retain explicit control over the remote query.

To enable the predicate to be sent to the linked server, the query needs to use 4-part names.

For oracle this would look something like:

select *
from ORCLLS..ADMIN.IE_Docs_Subscrs
WHERE F_DRSO = '84c9e2c5-2634-4f6a-b1b7-e1505d4a1c39'

Oracle traditionally (pre 12) has only a single database per instance, so the second part of the 4-part name is empty.


Give execute permission to user which does not have execute permission for specific stored procedure I have a user which just have view rights i.e. it does not have execute permission. But for a specific stored procedures I want it to have execute permission.So I followed the following process.
A user have permission to execute stored procedure and B user does not have the execute permission.

So while creating on stored procedure I wrote as.

CREATE PROCEDURE sampleSP
WITH EXECUTE AS 'A'
AS
BEGIN
      --stored procedure body
END

And I am executing stored procedure as following with the login rights of B user.

EXEC sampleSP

But it is giving me the following error.

The EXECUTE permission was denied on the object 'sampleSP', database 'SAMPLEDB', schema 'dbo'.

While a procedure runs, normally it has access to data in the database like the owner of the procedure, which is usually "dbo" and can access everything.  I may have got this wrong: I seem to be describing "EXECUTE AS OWNER" and I have documentation which says that the normal setting is "EXECUTE AS CALLER".  I just thought that is what a "procedure" was -for-.  Anyway, either is an option.

A procedure's references to objects in different databases use the rights of the actual user - they must do because a procedure only understands security in its own database.  An exception is where a user's connection invokes an "application role"; in that case, the user has the access of the "guest" account to objects in other databases.  This normally is or ought to be "no access".

None of this relates to who is allowed to execute a procedure in the first place, and that is what you're trying to set.  That has to be allowed with GRANT.

However, if you issue GRANT and DENY commands that apply to the same object access, the result is to DENY - that's absolute.  Perhaps you've made that mistake.  To un-grant privileges, use REVOKE when you don't want to DENY.  Also, normal practice is to GRANT or DENY privileges to roles, then manage which roles a user is a member of.  Or even roles with privileges, whose members are roles with business functions (job titles), whose members are actual user accounts.

What "EXECUTE AS" does is to control the object access rights that a user may be "given" (or denied) in a procedure which the user doesn't have in an ordinary batch of Transact-SQL commands.


Different types of users in one table, How to add data off different users I have 5 type of users:

Super Admin
Coordinator
Supervisor
Student
External Examiner

All of them have some common attributes like ID, name, email etc. I have inserted them in one table user differentiating them with user_type attribute.

I have also made different tables for each user like Coordinator, Student because each user have different attributes.

My Question is:

  if I want to add a coordinator user into table, I can add it into user table with user_type=Coordinator but how will I link this coordinator from user table into 'coordinator' table?

Can anyone provide me with PHP-MySQL code for it?

Here is the table structure

CREATE TABLE `users`
( `userid` INT NOT NULL AUTO_INCREMENT,
  `user_name` VARCHAR(50) NOT NULL,
   user_type int 1,
  `email` VARCHAR(100) NOT NULL,
   PRIMARY KEY (`userid`)
);

CREATE TABLE `user_type`
( `user_typeid` INT NOT NULL AUTO_INCREMENT,
  `user_type` VARCHAR(50),
   PRIMARY KEY (`user_typeid`)
);

CREATE TABLE `tbl_corordinator`
( `coordinator_id` INT NOT NULL AUTO_INCREMENT,
   user_id int 1,
  `field1` VARCHAR(100) NOT NULL,
   PRIMARY KEY (`coordinator_id`)
);

When you will add 'Coordinator' users in users table, you need to add information in  tbl_corordinator & you will add userid (which is the primary key of users table.)

Thus using this foreign key concept, you can achieve this relation.


How to get random datetime of current day in SQL Server I am working on a SQL query where I need to get datetime of any specific date.
For this I used the following query:

DECLARE @RandomDate datetime
DECLARE @fromDate datetime = '2018-04-07'

SELECT
    @RandomDate = (DATEADD(day, ROUND(DATEDIFF(day, @fromDate, @fromDate)
* RAND(CHECKSUM(NEWID())), 5), DATEADD(second, CHECKSUM(NEWID()) % 24000, @fromDate)))

SELECT @RandomDate

Here in the above example, I want to get datetime of date '2018-04-07' i.e 7 April. But sometime it is showing datetime of 6 april also. How to solve it?

The only way to go 'back-in-time' would be to add a negative number.  Try adding ABS to your query.

DECLARE @RandomDate datetime

DECLARE @fromDate datetime='2018-04-07'

SELECT @RandomDate= (DATEADD(day, ROUND(DATEDIFF(day, @fromDate, @fromDate)
* RAND(CHECKSUM(NEWID())), 5),DATEADD(second, abs(CHECKSUM(NEWID())) % 86400,
@fromDate)))

SELECT  @RandomDate

ABS - A mathematical function that returns the absolute (positive) value of the specified numeric expression. (ABS changes negative values to positive values. ABS has no effect on zero or positive values.)


how do i use "?" as variables for my values so that i don't get hacked? I was wondering how do I use question marks so that I don't get hacked because when I use this:
SQL = "INSERT INTO users (id, username) VALUES ( " + message.author.id + ", " + message.author.username + ")"

it gives me an error: Unknown column '(my username)' in 'field list'
I know you will say that there is already a post like this, but I am kinda a beginner still so I don't understand what they are saying

Caution These methods of escaping values only works when the NO_BACKSLASH_ESCAPES SQL mode is disabled (which is the default state for MySQL servers).
In order to avoid SQL Injection attacks, you should always escape any user provided data before using it inside a SQL query. You can do so using the mysql.escape(), connection.escape() or pool.escape() methods:
var userId = 'some user provided value';
var sql    = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);
connection.query(sql, function (error, results, fields) {
  if (error) throw error;
  // ...
});

Escaping-Query-Values


Set bit in Table A if values are present in Table B I have two tables, Containing up to ~10 million entries.

TableA which has 3 columns: IDA (bigint), IDB (int) and CHK (bit).
(IDA, IDB) is the primary key.

TableB which has 2 columns:  IDA (bigint), IDB (int). (IDA, IDB) is the primary key.

Now I need to set the CHK bit for every IDA+IDB that is in TableA and TableB.

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00,
 i.e. SQL Server 2005 Service Pack 2

update a
    set a.CHK = 1
from tableA a
join tableB b
    on b.IDA = a.IDA and b.IDB = a.IDB


During a log backup is the data backed up to the start or end of the operation? Say I am running a log backup, and that log backup takes 10 minutes to complete. During that 10 minute window, further transactions are run. Given the below example, which transactions does the log backup actually contain?

  Transaction A commits
  Transaction B opens
  Log backup begins
  Transaction C opens
  Transaction B commits
  Log backup completes
  Transaction C commits

The log backup file shall contain all the transactions (including the ones that hasn't been committed yet) up to the moment the backup finished running and you can verify it as follows:

CREATE DATABASE MyDB;
ALTER DATABASE MyDB SET RECOVERY FULL;

USE MyDB;
CREATE TABLE LogTest (numbers int);

Once created the database and the table that's gonna be used on the test, take the first full backup so that it will be possible to take a log backup later.

BACKUP DATABASE MyDB
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\MyDB_FULL.bak';

Now that a full backup was taken let's insert some data on the table:

USE MyDB;

INSERT INTO LogTest(numbers) VALUES (1);

BEGIN TRAN
INSERT INTO LogTest(numbers) VALUES (2);

Observe that the second insert is under a BEGIN TRAN that hasn't been committed yet. Next we'll use the function [fn_dblog to check the content of the log file before taking a log backup.

SELECT [Current LSN], [Operation], [SPID], [Transaction Name], [Begin Time], [End Time] FROM fn_dblog(NULL, NULL);

On the picture we can see LSNs related to the first and second INSERT

BACKUP LOG MyDB
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\MyDB_LOG.trn';

A log backup was taken and we can read its content with the function fn_dump_dblog using the following query:

SELECT [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time]
FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\MyDB_LOG.trn',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

As we can see those LSNs related to the unfinished transaction actually are on the transaction log backup even though they were not committed. The catch is that as Microsoft says

  The transaction log is a critical component of the database. If there
  is a system failure, you will need that log to bring your database
  back to a consistent state.

Hence after performing a restore of that log backup the data related to those unfinished transactions will be rolled back to keep the database in a consistent state.
You can consider safe (can be restored) all the transactions that were committed. It can be verified by dropping the database MyDB and restoring the backups that were generated with the steps above.


How to map single-products to categories in my scenario? I have the tables depicted in the following diagram:

Let's say I have only two products in my database at moment, one that has variants, while the other does not have them.

Now my question is: how do I map my Product WITHOUT variants to a CATEGORIES in the PRODUCTS_TO_CATEGORIES_MAPPING table?

For simplification, add a "variant" even for products without variants.

Is there any more info on categories other than cat_name?  If not, then I suggest it is "over-normalization" to even have the Categories table.  Simply put cat_name in Product_to_Categories_Mapping.

Product_to_Categories_Mapping needs

PRIMARY KEY(product_id, category),  -- the _pair_ is unique
INDEX(category, product_id)         -- to go the other direction


how to transfer data from table to table I have two tables Table1, Table2. From Table1 want to transfer data to Table2.

Suppose I have the following query:

Select *
from Table1
where UserID between 5 and 10

I want to store above result to Table2. Both tables save same type of columns. How to transfer data from table to table?

If have any questions, please ask.

following INSERT INTO statement:

INSERT INTO TABLE2 SELECT * FROM TABLE1

Now suppose you do not want to copy all the rows, but only those rows that meet a specific criteria. Say you only want to copy those rows where COL1 is equal to "A." To do this you would just modify the above code to look like this:

INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 = 'A'

See how simple it is to copy data from one table to another using the INSERT INTO method? You can even copy a selected set of columns, if you desire, by identifying the specific columns you wish to copy and populate, like so:

INSERT INTO TABLE2 (COL1, COL2, COL3)
SELECT COL1, COL4, COL7 FROM TABLE1

The above command copies only data from columns COL1, COL4, and COL7 in TABLE1 to COL1, COL2, and COL3 in TABLE2.


Select only databases with a certain table name and specific columns So my first query is this one:

SELECT *
FROM information_schema.columns
WHERE table_name='logsys' and
column_name in
('CD_LOG'
,'NM_USUARIO'
,'DT_ACAO'
,'DS_HISTORICO'
,'NOME_MAQUINA'
,'USUARIO_MAQUINA'
,'DATA_REFERENCIA'
,'ID_SISTEMA'
,'VERSAO'
,'LOCAL'
,'ACAO'
,'ID_LOG'
)

Then to run it in all databases, I made this:

DECLARE @command varchar(1000)
SELECT @command =

   'use [?] SELECT
   DB_NAME() as DatabaseName, *
FROM information_schema.columns
WHERE table_name=''logsys'' and  column_name in
(''CD_LOG'',''NM_USUARIO'',''DT_ACAO'',''DS_HISTORICO'',''NOME_MAQUINA'',''USUARIO_MAQUINA'',''DATA_REFERENCIA''
,''ID_SISTEMA'',''VERSAO'',''LOCAL'',''ACAO'',''ID_LOG'')'

EXEC sp_MSforeachdb @command

But I'm just confused about something. I want THESE specific columns. I want tables with all these columns. not more, not less. but with in, obviously it lists tabes that have at least one of these columns. I'm confused about this simple query. How can I use it, in all databases, to find only those tables that has all those columns?

Should I use and column = x, and column= y.... and so on?

And a bonus question. How can I insert foreachdb inside a table?

EDIT1:

Thanks for answers, for now, I have this:

--CRIA TABELA DO INSERT
CREATE TABLE TB_DB_LOGSYS
    (
        DATABASENAME VARCHAR(200),
        TABLE_CATALOG VARCHAR(200),
        TABLE_NAME VARCHAR(200),
        COLUMN_NAME VARCHAR(200),
        DATA_TYPE VARCHAR(100)
    )
GO
---------------------------------------------------------------------------
--PROCURA AS LOGSYS QUE POSSUEM 12 COLUNAS
TRUNCATE TABLE TB_DB_LOGSYS
DECLARE @command varchar(1000)
SELECT @command =

   'use [?] SELECT
   DB_NAME() as DatabaseName,  TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM information_schema.columns
WHERE table_name=''logsys'' and  column_name in
(''CD_LOG'',''NM_USUARIO'',''DT_ACAO'',''DS_HISTORICO'',''NOME_MAQUINA'',''USUARIO_MAQUINA'',''DATA_REFERENCIA''
,''ID_SISTEMA'',''VERSAO'',''LOCAL'',''ACAO'',''ID_LOG'')'

--INSERE O FOREACHDB NA TABELA
INSERT INTO TB_DB_LOGSYS
EXEC sp_MSforeachdb @command
---------------------------------------------------------------------------
SELECT * FROM TB_DB_LOGSYS

SELECT DATABASENAME, COUNT(COLUMN_NAME) AS [COUNT] FROM TB_DB_LOGSYS
GROUP BY DATABASENAME
HAVING COUNT(COLUMN_NAME)=12

but using having is not what I really want. maybe there's another column, and not the one I'm looking for.

I've set up this sample on fiddle.uk just to show you one option.

CREATE TABLE logsys_a(id int, v1 int, v2 int, v3 int, v4 int, v5 int);
CREATE TABLE logsys_b(id int, v1 int, v2 int,         v4 int, v5 int);
CREATE TABLE logsys_c(id int, v1 int, v2 int, v3 int, v4 int, v5 int);
GO

Instead of use USE [?] you can use TABLE_CATALOG to filter on which database are you searching for. In my example I've used LIKE 'fiddle%' because it changes every time you executes the query.
You can check if EXISTS a table with all the required columns using HAVING COUNT() = x, as you can see in this case it only returns logsys_a and logsys_b.

As far as I don't know if you need all table columns I haven't used again COLUMN_NAME IN ('v1','v2','v3','v4','v5') on the main query.

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM   INFORMATION_SCHEMA.COLUMNS T1
WHERE  TABLE_CATALOG LIKE 'fiddle%'
AND    TABLE_NAME LIKE '%logsys%'
AND    EXISTS (SELECT   T2.TABLE_SCHEMA, T1.TABLE_NAME
               FROM     INFORMATION_SCHEMA.COLUMNS T2
               WHERE    TABLE_CATALOG LIKE 'fiddle%'
               AND      T2.TABLE_SCHEMA = T1.TABLE_SCHEMA
               AND      T2.TABLE_NAME = T1.TABLE_NAME
               AND      T2.COLUMN_NAME IN ('v1','v2','v3','v4','v5')
               GROUP BY T2.TABLE_SCHEMA, T2.TABLE_NAME
               HAVING COUNT(*) = 5);

GO

TABLE_CATALOG                           | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME
:-------------------------------------- | :----------- | :--------- | :----------
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | id
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | v1
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | v2
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | v3
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | v4
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | v5
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | id
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | v1
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | v2
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | v3
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | v4
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | v5

dbfiddle here


How do I create a query which takes certain time to execute? I have configured statement_timeout=1000 in Postgres DB
I am accessing a DB table using a JDBC driver from a java application. JDBC driver will throw an exception if the statement times out. I have a logic that will get executed if the exception is thrown.
Now I want to test this functionality, is there a way to make a query not return the result within 1000 millsecs?

As @McNets suggests in his comment, this is really easy using the pg_sleep() function:
select pg_sleep(2);

will return after 2 seconds. And the resulting dataset will contain one row and one column too, so your test application can easily handle it (if it gets through the timeout).


How to Add Federated engine after installing Mysql I have Mysql 5.5.18
and upon show engines:

show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+

as you see federated engine is not one of the list. i need to enable it!! any idea of why BLACKHOLE, FEDERATED, and ARCHIVE engines are missing from the list? and how can i add/enable them?
EDIT
I have ubuntu 11.10 and installed mysql from ppa ... i have added the following to /etc/apt/sources.list

deb http://ppa.launchpad.net/nathan-renniewaldock/ppa/ubuntu oneiric main
deb-src http://ppa.launchpad.net/nathan-renniewaldock/ppa/ubuntu oneiric main

Thanks for your help

I have managed to find a solution...
there is a plugin already available at

/usr/lib/mysql/plugin/ha_federated.so

i just logged in to mysql as root and run the following query

mysql>install plugin federated soname 'ha_federated.so';

then i added federated to my.cnf and restarted mysql


Connect to Database Engine Prompt on Script Object Every time I right-click an object in SSMS 2016 and click Script Object As > CREATE To > New Query Editor Window, I get the prompt below. Not sure if I changed a setting or what, but was hoping someone knew how to make it so it doesn't prompt to open a new connection each time.

I was able to reproduce the problem you're encountering.

Originally, I wasn't being prompted like you are being prompted.  There is a button just to the right of New Query that invokes the Database Engine Query.  This button would be very easy to click because it's close the the New Query button

This brings up the connection dialog box you are experiencing.  The problem is, once you click this button, you will continually be prompted for a connection until you exit out of SSMS and come back in (at least, that is how I corrected the problem).


Granting a SQL Server database only user execute on xp_instance_regread fails So I'm not finding an answer to my specific issue.

SQL Server 2012 database
SSMS 17 admin tool

Log on as a database (not domain) user foo.

Receive sys.xp_instance_regread no permission errors.

In past versions of SSMS you get the errors, but you can do your work on your database.

I've seen this before, the user needs execute permission on the stored procedure. Shoot I even have a script I use all the time for domain users

Grant Execute ON [sys].[xp_instance_regread] TO [domain\user];

So I modify it:

Grant Execute ON [sys].[xp_instance_regread] TO [foo];

but get this error:

  Msg 15151, Level 16, State 1, Line 1
  Cannot find the user 'foo', because it does not exist or you do not have permission.

Now the user exists and my account has sysadmin access.

Any ideas on how to fix this?

So if anyone is curious the fast answer was to go into the UI and add the foo user to master thru the user mapping tab.

Wonder why?


Postgresql BDR Update Update conflict detection Apparently only the new values of rows are sent between nodes on update.  So how can there ever be detectable conflict?   Node A receives an update from Node B, and a different update from node C.  How can A know that there was a conflict?  That the B and C updates were not conducted in some well defined order?

And what does "last-update-wins" mean?  Are the updates timestamped?  In which case does it rely on synchronized clocks?  Or does it just mean whichever update happens to get to Node A last wins?

SUMMARY

Last Update Wins means based on commit time (requiring fairly synchronized clocks), and not the time a node sees an update.  So should be globally consitant.
There aren't any Update/Update conflicts that are detectable (except constraint violations).  And they would be difficult to resolve if they were.
Conflict resolution is row by row, not transaction by transaction.  Also see http://sdf.org/~riley/blog/2016/01/04/is-bi-directional-replication-bdr-in-postgres-transactional/

The manual discusses much of this.

Yes, commits are timestamped. Conflict resolution uses commit timestamp, not the timestamp of an individual tuple insert/update/delete. See track_commit_timestamps in the postgres docs.

In your example, node A might or might not see a conflict. If B's update happened first (according to B's clock), and B's update reaches A before C's does, A doesn't see a conflict. But if C's update reaches A before B's, and B's happened earlier, then when B's update arrives at A, A needs to know to discard it since it's already applied a newer one. Otherwise it'd become inconsistent with the state of B (which will apply C's update when it receives it) and C (which has overwritten B's older update with its own newer one).

Because it's a loosely coupled replication system with no global lock manager, snapshot manager, transaction manager, etc, there's no one concept of "now" and no one globally consistent state. Or at least it's fuzzy due to latency and replication lag. You'll see such systems (somewhat incorrectly) referred to as "AP", for the "availability" and "partition tolerence" of CAP. It's an eventually consistent system with weaker inter-node guarantees than made by stock PostgreSQL. You can concurrently UPDATE a tuple on two different nodes. You can INSERT the same key into a UNIQUE-constrained column (or same PRIMARY KEY) on two different nodes. etc.

When the nodes replicate and sync up these conflicts need to be resolved so that all nodes have the same outcome and no errors result. We can't just ERROR because the xacts already committed on their origin nodes and we have no way to "un-commit" them; even if we did, we shouldn't, since other xacts on the local nodes might've used those committed changes as inputs into other computations so we're stuck with them.

So we have to do things like pick the newest of two inserts. We use the timestamp of commit for this. It's sent on the protocol messages, and it's recorded by postgres for local commits.

The old row values don't matter for this. For example, we only care that some peer node updated table "x" but we've updated it more recently, so we presume the newer update should be kept. Otherwise when our change replicated to the peer node, it'd apply our change and if we'd also applied theirs, we'd have different final states. To get a consistent result, we must discard their change.

Similarly, if two new rows are inserted, we must consistently pick one of them to keep when they conflict on the same PK. We pick the newest of the two rows based on recorded commit timestamp.

So yes, it relies on clocks. The clocks don't have to be in perfect sync, but roughly right is desirable, otherwise you might overwrite newer tuples with significantly older ones when doing conflict resolution, i.e. pick the wrong "last" update. The manual should discuss clock sync a bit more.

Having access to old row values would be nice in some cases, but is doesn't let you know for sure what the right outcome is. You still have to pick one of multiple possible "correct" results. Consider this:

given table

CREATE TABLE x (
  id integer primary key,
  y integer not null
);

INSERT INTO x (id, y) VALUES (1, 1);

Two nodes run

UPDATE x SET y = y + 1 WHERE id = 1;

at the same time. Both produce a new local row with value y = 2 as a result. Now each replicates to the other.

If we know the old value was 1 and the new value is 2, we might say "well, duh, the correct result is 3 on both nodes".

But we have no way to know that one or both nodes didn't instead run

UPDATE x SET y = 2;

instead of y = y + 1. So it might want the final result to be 2, not 3. The only way to know is to know the application logic. BDR can't tell based on just the old and new tuples.

So really we don't need the old tuple values.


How to restore a database using mdf and ldf files in SQL Server 2008? Asking for expert opinion. A very unfortunate event happened. One of our PCs crashed few days ago. That PC had SQL Server 2008 installed. Unfortunately no backup was running on that server. We sent the disk to a data recovery place and looks like they can recover some of the .mdf and .ldf files.

We are particularly interested in 1 particular database.

My questions are:

Is this possible to restore a particular  database using corresponding mdf and ldf files?
Do we need to have any mdfs from the  system databases? Any other files we need to ask data recovery company to look for?
Steps and/or or best practices to restore that database into a new machine.

Thanks for your help in advance.

Is this possible to restore a particular database using corresponding mdf and ldf files?

Yes, you can try to attach them, probably just try to attach using:

CREATE DATABASE restored_copy_of_db_name
ON (filename = 'c:\temp\backup_of_mdf.mdf'),
   (filename = 'c:\temp\backup_of_ldf.ldf')
FOR_ATTACH;

It is very common for this to fail, because the log file gets damaged from this type of event (see this post for reasons we don't rely on mdf/ldf files as backups), so then you can try:

CREATE DATABASE restored_copy_of_db_name
ON (filename = 'c:\temp\backup_of_mdf.mdf')
FOR_ATTACH_REBUILD_LOG;

  Do we need to have any mdfs from the system databases?

If you're trying to restore full functionality out of the gate, then your attached database above will not include server-level logins, jobs, etc. So you may need master and msdb in order to recover some of that information (but I would attach it as a user database (msdb_copy), rather than replacing msdb, and try to restore master on a throwaway instance - see this thread for possible ideas here, but I would not be confident you'll get that info back).

  Steps and/or or best practices to restore that database into a new machine.

Nothing really in addition to the above, except, once you have these databases back, please set up a proper backup and recovery plan, which should include periodically testing that you can restore your databases - the fact that a backup was successful is not enough to ensure you won't be doing this again.


What are the pros/cons of between these 2 database design options where the join table can have 2 or 3 foreign keys? I am trying to model the following scenario, but I have come up with 2 options and I cannot figure out how to decide between the 2 options.  Both options seem to work, but I worry that I am missing some edge case that suggests one over the other.  Any recommendations or suggestions on where one option might be better than the other option?
Thank you for your time :)
The model:
A user has many labels
A property has many labels
A label has many properties

Option 1 (join table with 2 foreign keys):
user
----
id
name

property
--------
id
name

label
-----
id
name
user_id (foreign key for user.id)

property_label
--------------
property_id (foreign key for property.id)
label_id (foreign key for label.id)

Option 2 (join table with 3 foreign keys):
user
----
id
name

property
--------
id
name

label
-----
id
name

user_property_label
-------------------
property_id (foreign key for property.id)
label_id (foreign key for label.id)
user_id (foreign key for user.id)

Potential Queries:
Get all labels associated with a user
Get all properties associated with a label of a user

Example data:
User1 has labels "old" and "new" for property "bicycle".
User2 has labels "1 year", "2 years", and "3+ years" for property "bicycle".
User 3 has labels "<3 years" and "3+ years" for property "car"
User 4 has labels "red", "blue", and "black" for property "car"
User 5 has labels "daily beater" and "weekend fun" for property "car" and labels "<3 years" and "3+ years" for property "bicycle"
User 6 has labels "1 year", "2 years", and "3+ years" for properties "bicycle" and "car"

The list of properties is large, but finite.  Users will reference the same property and cannot have their own variation in its spelling.  So for example, "bicycle" will always be "bicycle" for every user.  A user cannot have "bike" or "bi-cycle" or some other spelling of the property.
The labels are all user defined so it can be infinite depending on how each individual user wants to label their properties.
Update:
While musing on Jim's comments, the solution came.
The join table with 2 foreign keys is better because it is further normalizing the data.
For instance, this scenario:
User1 has labels "old" and "new" for property "bicycle"
User1 has labels "old" for property "car"

will produce this data using the option with 3 foreign keys:
user (id, name)
---------------
1, User1

property (id, name)
-------------------
10, bicycle
11, car

label (id, name)
----------------
100, old
101, new

user_property_label (user_id, property_id, label_id)
----------------------------------------------------
1, 10, 100
1, 10, 101
1, 11, 100

The user_id foreign key is redundant information because user_id:1 will always be paired to label_id:100.  Hence, the data can be further normalized by using the 2 foreign key option. And will look like this instead:
user (id, name)
---------------
1, User1

property (id, name)
-------------------
10, bicycle
11, car

label (id, name, user_id)
-------------------------
100, old, 1
101, new, 1

property_label (property_id, label_id)
--------------------------------------
10, 100
10, 101
11, 100

It's hard to answer that question without knowing what you are trying to model and the queries that are relevant to your problem domain. I recommend that you trace out some queries and see how that design works.   Absent any other information, I would tend towards the 3 column option because the interpretation of a label depends on the property and you probably will do the query "give me all the labels associated with a user."
I think the most important question you should ask yourself are all the labels unrelated. If you are storing user preferences for an application, I can see the utility of your data model. On the other hand, if you want to store postal addresses, email address, access permissions, etc, then a different design is appropriate. Generic designs like this often shift data management and manipulation out of the DBMS into your application. Tables are cheap and I would recommend having tables of related facts/attributes (e.g. POSTAL_ADDRESS).
Revised answer based on edits:
First, never store an age in a database. Age depends on the current date.  You should store a date and calculate the age (you can use a virtual column to do that).
Second, I suggest you identify the types of things you want to store in the database. Bicycles and car are types of vehicles. I would recommend a VEHICLE table that contains the facts (attributes, property). You can then store properties relevant to bicycles in a BICYCLE_ATTRIBUTE (or whatever name you prefer) table. Do this for all the types of data you want to store and try to make groups that related. Postal address might be a group and education might be another.
You should try to avoid a completely generic database because you will end up creating a DBMS in your application and the underlying DBMS has been relegated to a fancy file system.


which command to execute to run multiple OLAP queries? I have 10 sql files that contains different OLAP queries.

I want to run them as a batch. Meaning by batch, Postgres will consider each file as a batch and execute the queries at once (depends on its own managing process)

The follow command, execute each query one by one

psql -h localhost -d databasename -U user -p port -a -q -f input.sql -o output.txt

Is that possible ? and how to calculate the time it takes to execute that batch sql queries ?

You can use i.e. GNU Parallel which provides possibility to run arbitrary commands/programs at the same time, the syntax would be something like:

parallel psql -h localhost -d databasename -U user -p port -a -q -f input{}.sql -o output{}.txt ::: 1 2

this command will execute 2 queries located in input1.sql and input2.sql at the same time.

Another option would be going for a dedicated load testing tool like Apache JMeter, if you add Postgres JDBC Driver to JMeter Classpath you will be able to provide your connection details under JDBC Connection Configuration and the query(ies) in the JDBC Request Samplers. Check out The Real Secret to Building a Database Test Plan With JMeter article for more details if needed.

Then you can increase the number of threads in Thread Group and JMeter will execute the queries according to your workload model. You will be able to observe performance metrics either using Listeners or via HTML Reporting Dashboard. This way you will get more comprehensive results and be able to correlate increasing number of concurrent threads with increased queries execution time, identify saturation points, bottlenecks, etc.


How to design data about a column can belong to the whole table? I'm not a DBA so I apologize for lack of knowledge.
Here's my situation...Say I have several tables. Products, Customers, Vendors, etc.

Our application may have a help text for some of the data in those tables.
Products may hold a column "SpecNumber" which needs to be in a specific format that may need a help text to display to the user, Customers table may hold another column, and so forth.
Our application will need a way to maintain this list of help text so keeping it as one table with maybe ID, helpText, etc. May have other columns if we need to keep other information about the field in the future.

Now, how do I go about querying this help text?
In an application where I display a list of products, for "SpecNumber", I want to show this text once on the form. So having a foreign key to HelpText table in Products table makes no sense.

Products table may have Id, Name, VendorId, MedicalCode. (sample data may be Id=1, Name='MedicineABC', VendorId=12 (foreignkey to vendor table), MedicalCode='123ABC3232') HelpText table may have Id, description.

Products
--------
  ID
  Name
  VendorID
  MedicalCode

HelpText
--------
  ID
  Description

Given the tables you have (I'm assuming this structure):

Products
--------
  ID
  Name
  VendorID
  MedicalCode

HelpText
--------
  ID
  Description

I'd suggest adding 2 new tables:

product_help_text
-----------------
  product_id
  help_Text_id

help_text_types
---------------
  id
  description

And changing HelpText:

HelpText
--------
  ID
  Description
  HelpText_type_id

So now, you could have some data like this:

Products
ID | Name      | MedicalCode
-----------------------------
1  | red pills | 445631-XS3
2  | blue pill | 043-PPLE-A

HelpText
ID  |  TypeID  |  Description
--------------------------------------------
1   | 1        | Take 3 times / day w.water
2   | 3        | Verify patient's health card
3   | 2        | check for other drug reactions

helpText_Types
ID  | Description
-----------------
1   | For the patient
2   | For the doctor
3   | for the pharmacist

product_help_text
product_id  |  help_text_id
---------------------------
1           |  1
1           |  3
2           |  1
2           |  2

product_help_text tells you which text goes with which product, and you can use the help text types to control which text gets seen where (for patient, pharmacist, doctor, drug sales rep, etc...).


Version Store usage and entries in dm_tran_active_snapshot_database_transactions So I'm trying to understand why are having occasional problems with tempdb filling up with version store entries.
I'm closing in on the problem, and am now seeing entries in dm_tran_active_snapshot_database_transactions, but I don't understand why the entries are there.
The databases in question do not have ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT enabled.
Checking the transaction via dm_tran_active_transactions I can see the following properties:

transaction_begin_time (90 minutes ago)
transaction_type 1
transaction_state 2
transaction_status 0
transaction_status2 258
dtc_state 0
dtc_status 0
dts_isolation_level -1

Any ideas how to troubleshoot this in a methodical manner?

Perhaps triggers are involved, which use the version store to provide the before/after row images exposed by the inserted and deleted virtual tables. As a first step, check active queries using the version store based on the session_id:
SELECT sql_text.text
FROM sys.dm_tran_active_snapshot_database_transactions AS tranactions
JOIN sys.dm_exec_requests AS requests ON requests.session_id = tranactions.session_id
CROSS APPLY sys.dm_exec_sql_text(requests.plan_handle) AS sql_text;


How to access the number of rows accumulated by RETURNs in PL/pgSQL When performing

RETURN QUERY ...

in a PL/pgSQL function, is it possible afterwards to directly access the number of rows accumulated into the pile of records, which are returned when the function ends?

Example:

RETURN QUERY SELECT * FROM tableA; -- 14 records
RETURN QUERY SELECT * FROM tableB; -- 8 records
RETURN QUERY SELECT * FROM tableC; -- 22 records

The number of accumulated records should now be 44.

Use

GET DIAGNOSTICS integer_var = ROW_COUNT;

More in the manual in the chapter Obtaining the Result Status.
Your example could look like this:

CREATE OR REPLACE FUNCTION f_test()
  RETURNS SETOF table_a AS
$func$
DECLARE
    i  int;
    ct int := 0;
BEGIN

RETURN QUERY SELECT * FROM table_a;    -- 14 records
GET DIAGNOSTICS i = ROW_COUNT;  ct := ct + i;

RETURN QUERY SELECT * FROM table_b;    -- 8 records
GET DIAGNOSTICS i = ROW_COUNT;  ct := ct + i;

RETURN QUERY SELECT * FROM table_c;    -- 22 records
GET DIAGNOSTICS i = ROW_COUNT;  ct := ct + i;

RAISE NOTICE 'Returned % rows', ct;    -- 44

END
$func$ LANGUAGE plpgsql


AWR Report - Tuning Database We have an OLTP system, following are our AWR reports taken during peak hour window for three days once each day. We see a lot of direct path reads; we want to add more PGA in-order to reduce wait time on this wait event. Following is the current setting in our 11g db.

Sqls order by cpu

CPU Time (s) Executions  CPU per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
7,279.38 9,694 0.75 31.50 7,297.79 99.75 0.27 0dp0c8wux0jnt    select case when max(LMA.LEAS...
6,562.18 1,395 4.70 28.40 6,582.28 99.69 0.31 3g2kw67v3v0qs    SELECT NAME, vpd_predicates.ge...
4,229.12 836,735 0.01 18.30 4,234.99 99.86 0.00 55a17fhdqg315 DBMS_SCHEDULER  begin :con := SELECT_POLICY_FU...
2,683.17 968 2.77 11.61 2,605.03 103.00 7.61 dfd3gcuamscs8    select sc.Doc_ID docId , sc.DO...
2,434.46 853,025 0.00 10.54 2,442.95 99.65 0.00 4y5m5y6y6wvzs DBMS_SCHEDULER  SELECT COUNT (*) FROM CONTACT_...

Top 5 Timed Foreground Events 08-Feb-2011
Event   Waits   Time(s) Avg wait (ms)   % DB time   Wait Class
DB CPU      23,108      94.74
db file sequential read 1,332,461   797 1   3.27    User I/O
direct path read    430,600 523 1   2.14    User I/O
db file scattered read  245,444 224 1   0.92    User I/O
SQL*Net more data to client 2,057,853   155 0   0.64    Network

It appears that something like 95% of your wait time is on CPU.  Even if you completely eliminated the direct path reads, you'd eliminate less than 2% of your total wait time.  It seems unlikely that a 2% reduction in your wait time would even register as an improvement.

Taking a look at what is consuming all that CPU-- you have a handful of very CPU intensive queries. 0dp0c8wux0jnt and 3g2kw67v3v0qs are consuming 60% of the CPU your system is using. Have you looked at optimizing those queries so that they use less CPU? 3g2kw67v3v0qs in particular takes nearly 5 seconds per execution.

What else do you have running on this machine?  A total SGA & PGA that is 6% of the memory available on the machine implies that you probably have a number of other things running (perhaps many database instances).


multi master replication Let us say I have a master(m) slave(s) replication. Now, in the scheme of things, I introduce another database/schema(foo) not associated with the master in any way and I want this schema to be replicated into the slave(s). Can I do this?

AFAIK, this cannot be done. What is the best way to pull this off? The reason I want to do this is I want to join tables from foo with s/m.

The data replication need not happen in real time, can be a daily cron job too. Is mysqldump the way to go? Is there some hack that I can pull off?

I don't know if it's the best way, but you can always pull the binary log and let it execute somewhere else.

A MySQL node can be Master AND Slave at the same time, but it cannot be Slave of two Masters.

See this similiar post:

https://stackoverflow.com/questions/1576603/is-it-possible-to-do-n-master-1-slave-replication-with-mysql


Which is faster to update thousands of table rows? With stored procedures in PostgreSQL v10.1.2, which is the fastest way or which is better: to check if rows exists and then update or try to update directly with the possibility that no rows matching the condition are found? I need to check a lot of tables with the same condition, tables are not 'denormalized', I made a couple of tests and sometimes option 1 is faster in other cases option 2 ...

Option 1:

IF EXISTS ( SELECT  1
FROM   public.table1
WHERE  column1 = 'oldvalue' )
THEN
   UPDATE public.table1
   SET   column1 = 'newvalue' , date_update= ....
   WHERE  column1 = 'oldvalue';
END IF ;

Option 2:

UPDATE public.table1
SET   column1 = 'newvalue' , date_update= ...
WHERE  column1 = 'oldvalue';

Option 3:

perform FROM   public.table1 WHERE  column1 = 'oldvalue' ;
if found then
   UPDATE public.table1 SET column1='newvalue', date_update = ... WHERE column1 = 'oldvalue';
end if ;

I made:

BEGIN;
EXPLAIN ANALYZE VERBOSE select a_test_w_perform();
ROLLBACK;

for every option and the time average vary, results in time are something like:

Option 1

Query returned successfully in 311 msec. / 423 msec./ 242 msec./ 367

Option 2

Query returned successfully in 308 msec. / 307 msec./311 msec./ 200

*Option 3

Query returned successfully in 204 msec./ 279 msec./451 msec. / 230

It's generally faster to just try the UPDATE.

Issuing a SELECT first, just increases the read cost. Plus, more importantly, it's unreliable under concurrent write load to the database anyway. You would have to add FOR UPDATE or similar to lock rows. But don't. Just issue the UPDATE.

One way to increase performance in many cases, though: avoid empty updates:

UPDATE public.table1
SET    column1 = 'newvalue'
     , date_update = ...
WHERE  column1 = 'oldvalue'
AND   (column1   , date_update) IS DISTINCT FROM
      ('newvalue', ...        );  -- to skip empty updates

Related:

How do I (or can I) SELECT DISTINCT on multiple columns?

It can make sense to issue SELECT first if you want to involve LIMIT (or similar), which is not allowed in UPDATE directly. But consider concurrency:

Postgres UPDATE ... LIMIT 1

Resulting performance can vary with lots of parameters. Warm cache, concurrent transactions, load on the sever, etc. Your case may be particularly tricky:

  check a lot of tables with the same condition

Involving lots of tables makes it hard to find the best query plan. If you know that a certain predicate is particularly selective - and Postgres doesn't  - then it might pay to run a cheap, simplified SELECT first. But you need to deal with concurrency properly.

Typically, this would indicate problems with your query or your server configuration concerning cost settings or statistics, and it would be better to fix that instead of working around the problem. But Postgres does have a few blind spots, like no combined statistics for multiple conditions or no statistics for nested values in document types like jsonb, xml etc.

Update: Postges 10 added "multi-column optimizer statistics to compute the correlation ratio and number of distinct values". (Still does not help with values nested in document types.) Details in the manual.

When updating large parts of the whole table, different strategies might pay:

Optimizing bulk update performance in PostgreSQL


Is there a way to list the "last backup" file of a Database? I'm trying to do something really simple, but with one problem:

declare @cmd varchar(1000)

set @cmd='restore verifyonly from disk =''\\server\backup_folder\''?'''

exec sp_msforeachdb @cmd

Obviously its not working. I need the name of the backup file to run Verifyonly ( in the above code, i'm taking the Database name ).

Is there a way to automate Verifyonly, to run in all backup files?

How can I use the name of the backup file that has been made, in my code?

Thank you very much.

( People are saying MsForeachdb is not safe. I will try to search another option, like Foreachdb )

SQL SERVER 2008

EDIT1:  Well, As i see here, I don't think verifyonly is a good idea, but will be good to know how to do it ( only for knowledge )

This is just a matter of using dynamic SQL to process your data. You can also leverage msdb.dbo.backupset and msdb.dbo.backupfamily to find your most recent full backups.  This script will get the most recent full backup file location for each database and use it to run your cursor:

declare @dbname sysname
    ,@backupfile nvarchar(max)
    ,@sqlcmd nvarchar(max)

declare recscan cursor fast_forward
for
select
    bs.database_name
    ,bm.physical_device_name
from
    msdb.dbo.backupset bs
    join msdb.dbo.backupmediafamily bm on (bs.media_set_id = bm.media_set_id)
    join (select database_name
            ,max(backup_finish_date) last_backup
          from msdb.dbo.backupset
          where type = 'D'
          group by database_name) lb on (bs.database_name = lb.database_name and bs.backup_finish_date = lb.last_backup)
where type = 'D'

open recscan
fetch next from recscan into @dbname,@backupfile

while @@fetch_status != -1
begin
    PRINT 'Validating '+@dbname
    select @sqlcmd = N'restore verifyonly from disk = '''+@backupfile+''';'

    exec sp_executesql @sqlcmd

    fetch next from recscan into @dbname,@backupfile
end

close recscan
deallocate recscan


Difference between SSAS 2005 and SSAS 2008 I'm asking this question since i'm planning to book a seminar, but the provider told me that they only offer trainings for SSAS 2008 or as individual training which are expensive.

So my question: would it be worthwile to book the SSAS 2008 seminar although we're using SSAS 2005 or are the differences too big?

I went through a pretty thorough 2008R2 training a year ago. It wasn't radically different from 2005, but it was different enough that you're going to face quite a bit of frustration when you can't do things in 2005, or the things you want to do are done differently. SSAS has been changing quite a lot over the last several releases. This is the list of changes between 2005 & 2008, and it's not small. The new designers are the things that are going to cause you the most pain.


SQL Server Query Optimizer Time Spent and Numbers In Microsoft SQL Server, is it possible to find how much time the query optimizer spent to generate N numbers of execution plans? (Let's say for a specific stored procedure).

Also, is it possible to know how many execution plans the query optimizer generated and which execution plan it has selected for that specific stored procedure?

Or does the query optimizer completely works like a sealed box?

Thanks!

Yes, SQL Server can report how long it took to do any of those actions (though you may have to run it to get some additional details such as actual row counts returned)

Statistics Time

SET STATISTICS TIME { ON | OFF }

Displays the number of milliseconds required to parse, compile, and execute each statement.
https://msdn.microsoft.com/en-us/library/ms190287.aspx

Execution Plans

To pull the execution plan you have many options:

Query the plan cache: (https://www.mssqltips.com/sqlservertip/1661/analyzing-the-sql-server-plan-cache/)
Trace/extended events the xml: https://www.mssqltips.com/sqlservertip/1264/capturing-graphical-query-plans-with-sql-server-profiler/)
Gather it in SSMS: via the "Include Actual Execution Plan" button in the toolbar https://msdn.microsoft.com/en-us/library/ms189562.aspx

On the SQL Server Management Studio toolbar, click Database Engine
Query. You can also open an existing query and display the estimated
execution plan by clicking the Open File toolbar button and locating
the existing query.
Enter the query for which you would like to display the actual
execution plan.
On the Query menu, click Include Actual Execution Plan or click the
Include Actual Execution Plan toolbar button
Execute the query by clicking the Execute toolbar button. The plan
used by the query optimizer is displayed on the Execution Plan tab in
the results pane. Pause the mouse over the logical and physical
operators to view the description and properties of the operators in
the displayed ToolTip.
Alternatively, you can view operator properties in the Properties
window. If Properties is not visible, right-click an operator and
select Properties. Select an operator to view its properties.
You can alter the display of the execution plan by right-clicking the
execution plan and selecting Zoom In, Zoom Out, Custom Zoom, or Zoom
to Fit. Zoom In and Zoom Out allow you to zoom in or out on the
execution plan, while Custom Zoom allows you to define your own zoom,
such as zooming at 80 percent. Zoom to Fit magnifies the execution
plan to fit the result pane.


Fixing MyISAM database without losing data My old server was crashed but I was able to recover the databases from MySQL through testdrive.

I'm trying to dump a database using mysqldump but it gives error for certain tables are crashed and should repaired.

I tried myisamchk --safe-recover /var/lib/mysql/database/table.MYI
And also myisamchk -r -q /var/lib/mysql/database/table.MYI

The tables fix themselves but unfortunately they delete that data from MYD files.

Is there a way to fix the tables without losing data from MYD or is it possible to convert the MYD into human readable format like a txt file?

First of all, if your data is worth anything, don't store it in MyISAM.

I don't think you can reconstruct a table from corrupted MYD file. Records in MyISAM have different format that depends on values stored in each record. There are about 20 block types! To make it more fun the records in MyISAM can be fragmented. So, if you absolutely need the data from MyISAM table the only way I'm aware of is a C program with wildcard masks.

You can partially recover the table if you select from it ranges of the primary key. Corrupt PK ranges will be lost, but at least if the range is good you can get it back.

Here's a script that I wrote a while ago to read as many records is possible from a corrupted table. Try it out, I hope it'll help:

#!/bin/bash

# Adjust your values here
host=localhost
user=root
db=test
table=test
PK=id
newtable=newtest2
# end of user defined values

read -p "Password: " -s password
echo ""
if [ "xxx$password" = "xxx" ]
then
        pass_cmd=""
else
        pass_cmd="-p$password"
fi
mysql -h $host -u $user $pass_cmd -e "show databases" $db
if [ $? -eq 0 ]
then
        echo "Credentials are ok!"
else
        echo "Can't run query SHOW DATABASES"
        echo "Check what's wrong"
        exit
fi
minPK=`mysql -sN -h $host -u $user $pass_cmd -e "SELECT MIN($PK) FROM $table" $db`
maxPK=`mysql -sN -h $host -u $user $pass_cmd -e "SELECT MAX($PK) FROM $table" $db`
a=$minPK
b=$maxPK
let chunk=$b-$a

function insert_range {
q="INSERT IGNORE INTO \`$newtable\` SELECT * FROM \`$table\` WHERE \`$PK\` >= $1 and \`$PK\` <= $2"
mysql -h $host -u $user $pass_cmd -e "$q" $db 2>/dev/null
}
successful_tries=0
echo "Primary key ($PK) range: $minPK .. $maxPK"
while true
do
        insert_range $a $b
        if [ $? -eq 0 ]
        then
                echo "Good, primary key range: $a .. $b"
                let a=$b+1
                let b=$b+$chunk
                let successful_tries=$successful_tries+1
        else
                echo "Bad luck, primary key range: $a .. $b"
                let chunk=$chunk/2
                let b=$a+$chunk
                mysqladmin -h $host -u $user $pass_cmd ping > /dev/null 2>&1
                while [ $? -ne 0 ]
                do
                        mysqladmin -h $host -u $user $pass_cmd ping > /dev/null 2>&1
                done
                successful_tries=0
        fi
        if [ $a -gt $maxPK ]
        then
                exit
        fi
        if [ $chunk -eq 0 ]
        then
                let a=$a+1
                let b=$a+1
                let chunk=1
        fi
        # If things are going well, increase pace
        if [ $successful_tries -gt 5 ]
        then
                let chunk=$chunk*2
        fi
done


Steps after restoring to a different server We have a 30 GB Database on SQL Server 2008 R2 Standard Edition. Yesterday we backed up the database and restored it to a different Virtual Machine which has exactly the same specs as the previous machine - The only exception being the new VM has a lot more RAM.

However we found that the performance of the older VM was much better than this new VM. My questions are:

Should we re-create / refresh all the indexes in the Database after such a restore ? If so then is there a good way to refresh all indexes with ease (as opposed to one index at a time).
Is there any other obvious step which we are missing - We don't have an in-house DBA as the work we do on the database front is not much.

Should we re-create / refresh all the indexes in the Database after such a restore ? If so then is there a good way to refresh all indexes with ease (as opposed to one index at a time).

Its always a good idea to do a reorg/rebuild and update stats after a database refresh to a different server. This is one of the post restore step that I do when I  restore a database to a different server

You can happily use the SQL Server Index and Statistics Maintenance - from Ola Hallengren.

  Is there any other obvious step which we are missing - We don't have an in-house DBA as the work we do on the database front is not much.

I have a detailed post restore steps outlined in this answer. That will help you.
Even though it is for an upgrade, but the post restore steps are same.

  The only exception being the new VM has a lot more RAM

If the only exception is more RAM on the new server, then try enabling the Trace Flag T2335. Paul White has a detailed explanation of why that would happen with MORE Memory. I have shared, what Microsoft recommended in our case as well.


SQL order by query results in any arbitrary (but reproducible) manner For testing purposes I need to get some data from a set of database tables in any arbitrary (but reproducible) order. The idea being that I can later compare two runs using a textual diff tool. Is there an idiom for that?

E.g. I can obviously do a:

SELECT * FROM table_with_N_columns ORDER BY column_1, ... , column_N

I am just asking if there is an idiomatic way to achieve the same effect (for my purposes) without bothering to list every column in the ORDER BY clause. Any ordering will do so long as it is reproducible with subsequent runs of the query.

Use PK columns

Sorting by all columns of a table is potentially (very) expensive or sometimes simply impossible. Example: table with a json column or xml, or point or one of many other data types which cannot be used in ORDER BY, lacking the necessary operators. You get an exception.

If it doesn't fail, it should work for your purpose, but it still would not guarantee a true stable sort order. Without unique or primary key, completely duplicate rows are allowed, which are still sorted arbitrarily. Two concurrent sessions could both change the "first" in a set of dupes without conflict, resulting in two different rows being changed separately.

Use the primary key column(s) of each table. Unique constraint would work, too, but you really should have a pk on every table to begin with. Besides being correct and guaranteed to work, it's also typically much faster.

Function

Query the system catalogs pg_constraint and pg_attribute to get columns for a PRIMARY KEY or UNIQUE constraint.

I wrapped the query in a handy SQL function:

CREATE OR REPLACE FUNCTION f_order_cols(_tbl regclass)
  RETURNS text AS
$func$
SELECT string_agg(_tbl::text || '.' || quote_ident(attname), ',')
FROM (
   SELECT conrelid AS attrelid, conkey
   FROM   pg_catalog.pg_constraint
   WHERE  conrelid = $1
   AND    contype = ANY ('{p,u}'::"char"[]) -- primary or unique constraint
   ORDER  BY contype                        -- pk has priority ('p' < 'u')
           , array_length(conkey, 1)        -- else, fewest columns as quick tie breaker
   LIMIT  1                                 -- ... but any 1 of them is good
   )   c
JOIN   unnest(c.conkey) attnum ON TRUE      -- unnest array, possibly mult. cols
JOIN   pg_catalog.pg_attribute a USING (attrelid, attnum)

UNION  ALL                                  -- Default if first query returns no row
SELECT _tbl::text || '::text COLLATE "C"'   -- See chapter "Default" below
LIMIT  1
$func$ LANGUAGE sql;

This is very fast.
The function takes the table name as parameter, type regclass to be precise. Resolves the name according to the current search_path and raises an exception if invalid. At the same time provides a properly quoted identifier in the result automatically.
Any PRIMARY KEY or UNIQUE constraint is good. PK has priority, else the unique constraint with the fewest columns is picked.
The constraint can consist of multiple columns, attribute numbers of which are stored in the array pg_constraint.conkey. Using an implicit JOIN LATERAL to unnest() that.
Result columns are table-qualified to get syntax ready for queries on multiple tables. This optional feature may have to be modified if you are using table aliases in queries.
Also quote column names correctly to avoid SQL injection. quote_ident() does that for you. More about that:

SQL injection in Postgres functions vs prepared queries

Default

If no constraint is found, the function returns NULL. Wrap the result into COALESCE and use the text representation of the whole row as default: tbl::text.

This is not going to sort rows like original values would, but you get a consistent sort order that works for every table. Since you are going to compare text representations anyway.
Better still, add COLLATE "C" to ignore collation rules. Faster, and just as good for your purpose.

ORDER BY tbl::text COLLATE "C"

Still a potentially very inefficient measure of last resort. Use PKs if at all possible. I put the default into the function above. Everything put together, for a query:

SELECT * FROM u JOIN p USING (a) JOIN n ON n.b = p.b ORDER BY ?

This returns the complete string for ?:

SELECT concat_ws(', ', f_order_cols('u')
                     , f_order_cols('p')
                     , f_order_cols('n')) AS order_by;

SQL Fiddle demonstrating all.


SELECT * WHERE VarCharColumn IN (...) Optimization I have a list of 3000 strings, and I am passing them (twenty at a time) into a parametrized IN clause. It definitely isn't getting the results I would like to see ~ 500ms per execution.

The column is an index. Do you know a better way than this:

SELECT * FROM [ohb].[dbo].[MasterUrls] WITH (NOLOCK) WHERE Hash
IN(@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19)

A list of 3000 takes between 3 and 5minutes. I really need that down to around 30 seconds. Is this possible?

I am using MSSQL 2008 R2 on a server with 24 gigs of RAM, and dual 8core NUMA Xeons @2.4Ghz running on a 6HDD (@15k/rpm) RAID 10 ISCSI.

The table has 1.4M rows, and the index is a non-clustered index.

Execution plan shows the index scan as 90% of the total execution.

SELECT * will invalidate any optimal use of an index (it isn't covering) even if hash is indexed. Your index scan is most likely on the clustered index because of this.

Personally, I'd start with

putting the 3000 search values into a table with an index
Edit: as per Marian's comment, this can be passed in a list or table already
use this in any of JOIN, IN, EXISTS (same plan usually)
ensure my index on MasterUrls suits using Hash and covers col1, col2, col3

Something like

CREATE TABLE #foo (Hash ...)
INSERT #foo...
CREATE INDEX IX_FOO ON #foo (hash)

--either
CREATE NONCLUSTERED INDEX IX_Hash ON MasterUrls (hash) INCLUDE (col1, col2, col3)
--or
CREATE CLUSTERED INDEX IXC_Hash ON MasterUrls (hash)

SELECT col1, col2, col3
FROM [ohb].[dbo].[MasterUrls] M
JOIN
#foo F ON M.Hash = F.Hash


Does the pgpool-II extension allow for parallel execution of any query? I read about pgpool-II here and it states that:

  Using the parallel query function, data can be divided among the multiple servers, so that a query can be executed on all the servers concurrently to reduce the overall execution time. Parallel query works the best when searching large-scale data.

I am wondering whether anyone has experience with using this function? Does it have similar functionality as Greenplum, Aster Data or Stado/GridSQL? These all offer the ability to utilize many computers to process any SQL query in parallel, which is a huge benefit if one wants to undertake data-mining on a large dataset. I was unable to find any documentation on this on the Pgpool-II pages.

To my knowledge it does not allow that level of parallelism. If that's what you are trying to do the best approach is to go with Postgres-XC or GridSQL, but Postgres-XC while more complex is also more flexible.

http://postgres-xc.sourceforge.net/ is the project page.


mongoDB: Limit user to find documents only by ObjectId I'm using mongoDB version 4.0.9 for various things. One of them is storing and retrieving files in mongoDB via GridFS. For that purpose, I have created a role/user in mongo with the privileges "createCollection", "find", "insert", and "createIndex".

However, I would like to limit the user's reading rights to only find documents by their ObjectId because this is all I need: The mentioned mongoDB user gets the ObjectIds from another data source and retrieves documents only by their id.

Right now, that user could list all documents via the find command. That means if an attacker was able to hijack that user's account, they could read everything.

If, however, there was a way to limit the user's rights to only read documents by their id then the attacker would be forced to brute force the ObjectId and that could easily be detected in the log files.

Currently, The lower level you can control the access of resources is at the collection level.

One option you can try is to create collections at each user level and give the user access to only that collection.


How-to implement an entity with an unknown maximum number of attributes? I am designing a baseball simulation program and I have run into a problem with designing the boxscore schema. The problem I have is that I want to track how many runs are scored in each inning. The way I do this in the actual program is to use a dynamic array that grows for each inning played.

For those unfamiliar with the game of baseball, games are usually nine innings long unless the game is tied still at the end of the 9th inning. Baseball games therefore, have an undetermined length which means I cannot design the database to only have 9 columns for the runs scored each inning (well technically 18 (9-innings * 2-teams). One idea I have had is to serialize the array and encode it as Base64 before storing it in the database. However, I do not know if this a good technique to use and I was wondering if anyone has a better idea.

In case it matters, the database I am developing around is PostgreSQL.

Any suggestions are greatly appreciated! Thanks!

You could do this. It would allow good performance for normal duration games, while allowing you to also store long running games.

CREATE TABLE InningRuns (
    GameId INT NOT NULL REFERENCES [...],
    Team CHAR(4) NOT NULL, --'Home','Away'
    Inning1 TINYINT, --Seeing how more than 255 runs are not really possible in an inning
    Inning2 TINYINT,
    [...],
    Inning9 TINYINT,
    ExtraInnings XML | TINYINT[] | VARBINARY | ETC., --Use to hold any runs in extra innings.
    PRIMARY KEY (GameId, Team)
)

You could further normalize and have a row for each unique combination of game, team, & inning. This would allow you as many innings as the InningId datatype would allow.

CREATE TABLE InningRuns (
    InningRunId INT IDENTITY PRIMARY KEY,
    GameId INT NOT NULL REFERENCES [...],
    Team CHAR(4) NOT NULL, --'Home','Away'
    InningId TINYINT, --Seeing how more than 255 innings might be excessive
    Runs TINYINT,
    UNIQUE (GameId, Team, InningId)
)

Edit: I know PostgreSQL uses Sequences instead of IDENTITY, I don't recall the correct syntax off hand, so translate accordingly.


Should child comment be treated as separate table? I'm building a commenting system, currently my table indicates which comment is child with parent id in 1 table, I would like to know what is the best way of doing this?

Current Table Scheme (1 Table):

CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent` int(11) DEFAULT NULL,
  `post` int(11) NOT NULL,
  `comment` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  PRIMARY KEY (`id`),
  KEY `child_idx` (`parent`,`id`),
  KEY `post_FK_idx` (`post`),
  CONSTRAINT `post_comment_FK` FOREIGN KEY (`post`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=158 DEFAULT CHARSET=latin1

Question:

Should I add a second table for the child and do a JOIN when retrieving The comments?
If child can be in 1 table, how should I delete all children when parent get deleted? (I have read about Foreign Key, but that doesn't work on 1 table.)

Foreign Key:

When I create a Foreign Key on 1 table, it works and get created, but when I try to enter data I get the following error.

Cannot add or update a child row: a foreign key constraint fails
(`App`.`comments`, CONSTRAINT `child_comment_FK` FOREIGN KEY (`parent`)
REFERENCES `comments` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)"

Statement:

INSERT INTO comments SET
    parent = :parent,
    post = :post,
    comment = :comment;

Screenshot of Error:

Your code is:

INSERT INTO comments
SET parent = 0,
    post = 385,
    comment = 'Hello, World' ;

Do you have a row in comments with id = 0? If not, the error is what one would expect. Put parent=NULL, not parent=0, if you want a comment to not have a parent.


InnoDB: Is a covering index in the form of (PK, col1, col2, col3) redundant? Table structure for config:

property (primary key)
value
description

Query:

SELECT property, value FROM config

If I put a covering index on (property, value) the optimizer still chooses the PRIMARY index, but the extra in EXPLAIN is NULL. If I tell the optimizer to use my covering index, the extra in EXPLAIN is USING INDEX.

What's really happening here? Why is the optimizer choosing the PRIMARY index over my covering index by default? Am I avoiding disk IO by telling the optimizer to use my covering index?

PRIMARY index in InnoDB is a tree where the primary key field (property) is a key and remaining columns are the value. In a secondary index the key are the index fields and the value is a primary key. In your case the secondary index has fields (property, value, and property). (property, value) is a key and property is a value.
The secondary index is larger and redundant, so MySQL make a right decision to use the PRIMARY index.

Check out a presentation I gave on InnoDB indexes structure for more details.
https://www.slideshare.net/akuzminsky/efficient-indexes-in-mysql


Business Logic in Data Warehouse my question is:

Should business logic be stored in the data warehouse data store? Or should it only exist in the reporting applications?

To give a more concrete example:

In the CRM operational system there are abstract concepts of leads and opportunities, and lead statuses and opportunity stages. These concepts are very common, well understood, and used across different CRM tools.

And business users can legitimately ask questions like, what is the conversion rate for all leads from status "raw lead" to status "inside sales qualified lead".

Then for the data warehouse designer, the question becomes:
1. When someone changes a lead status from x to y after a call, should that be a row in a fact table somewhere? (perhaps in the same row as the call)
If yes, then we are storing abstract business concepts, rather than just real world events, in a fact table.
2. And the bigger question is, should the data warehouse even be aware of terms like "leads" and "opportunities"?
If yes--
2. Should the fact table have columns for old_status and new_status?
3. Or, should status change be handled as a slowly changing dimension?

If business logic is stored directly in the data warehouse, a lot of business questions become easier to ask. (lead status conversion metrics, opportunity stage advancement velocity metrics, and etc) But it seems more brittle to changes, is more complex to implement, and perhaps pollute the "verbs are facts and nouns are dimensions" paradigm.

How should I approach this design, and what are the best practices and guiding principles here?

Max

My answer is a big YES - you should store business logic in data warehouse. That is one of the ideas of data warehouses in the first place. If you have tens of reports showing or filtering leads imagine if a rule to qualify someone as lead would change. Also, what if you have to access DWH data with different tools/systems - you'd need to replicate all the logic. Lastly, calculating this kind of labels can be time consuming and that's why you'd like to have everything precalculated in DWH for the reports to run fast.

To answer your first question - yes, you should go with SCD. There are couple apporaches and you should choose the one that suits best your requirements - it's all about how users want to analyze data (this is where the desing should start.


SQL Agent Jobs Automatically Disabled After Modifying Maintenance Plan I modified a maintenance plan yesterday, to find out this morning (by using the last modified date from sysjobs) that all jobs related to the maintenance plan are disabled.

I can't figure out why this may be. The SQL Job Owner is sa and I merely changed a maintenance clean-up task with the maintenance plan and saved the job.

You haven't done anything wrong or gone crazy - it's a known bug and we've experienced the same thing.

Our process was changed that we would script out job status and reapply after any maintenance plan work - until we could move off of maintenance plans entirely (which we did).

Continuing on from the comments on the main question, there is an owner associated with the package itself which then gets copied over to the jobs and there's no UI to change it (you can modify it directly in the MSDB package tables though!)

We remediated that later on but I am unaware if there's a link between that referenced login disappearing and the jobs being disabled. It's possible.


Can someone explain the magic of Opendatasource/Openrowset? When pulling information from an excel file (or ms-access DB) I commonly use something like this:

SELECT *
 FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
 'Data Source=C:\test.xls;Extended Properties=''EXCEL 12.0;HDR=NO;IMEX=1'' ')...[Sheet1$]

Sometimes it works. Sometimes it doesn't.

Does anyone know a guide to setting this up?

I know about the temp folder, I know about downloading the correct drivers, I know about the extended properties, I know about not having the file open. Sometimes though, I still get the -

  OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

I ran into this problem yesterday. I restarted my machine - didn't work. Then restarted my instance again and P00f! Magically it worked.

So that's my question - When getting the awesome "Unspecified error", what do you need to check to make sure all the stars are in line for this to work.

Check Windows Event Viewer.  Look in the application logs, security logs, and system logs.  If you've nailed down all of the obvious stuff (and it sounds like you have), then it could be anything.  The file's folder could have Windows domain security on it, and your machine might not be able to authenticate against the DC, for example.


How to handle a many to many relationship with varying number of columns needed? I'm designing a database that tracks hikes I have gone on. There is a Hikes table I have columns that include HikeID, LocationID, Type, Summit, #Friends. I then have a table called Friends which documents friends I've gone on hikes with.

What is the best way to document which friends have gone on which hikes without a host of null values?

My current idea is to create an intermediary table called FriendCombinations with a number of columns equal to the most friends I'd expect to ever hike with and then have an ID row in this table to reference in the Hikes table. Obviously this will produce null values but I'd rather have those be in the FriendCombinations table rather than the hikes table.

Is there a simpler and more elegant solution I'm just not realizing? Thanks.

The preferred method is to use an intermediate table to just intersect friends and hikes.  Then join through that table to list off friends who have gone on which hikes.

I've included a sample below as well as a sample query that shows each hike and the friends that have gone on that with you.

CREATE TABLE dbo.Hikes
    (
    HikeID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , LocationID INT NOT NULL
    , Summit NVARCHAR(100) NULL
    , DateStart DATETIME2 NOT NULL
    , DateEnd DATETIME2 NOT NULL
    )

CREATE TABLE dbo.Friend
    (
    FriendID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , FullName NVARCHAR(100) NULL
    , IsRealFriend BIT NULL DEFAULT 0
    )

CREATE TABLE dbo.HikeFriend
    (
    HikeFriendID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , HikeID INT NOT NULL REFERENCES dbo.Hikes (HikeID)
    , FriendID INT NOT NULL REFERENCES dbo.Friend (FriendID)
    )

CREATE UNIQUE INDEX IDXU_HikeFriend ON dbo.HikeFriend (HikeID, FriendID)

--Sample Query
SELECT H.LocationID
    , H.Summit
    , H.DateStart
    , H.DateEnd
    , F.FullName
FROM dbo.Hikes AS H
    LEFT OUTER JOIN dbo.HikeFriend AS HF ON HF.HikeID = H.HikeID
    LEFT OUTER JOIN dbo.Friend AS F ON F.FriendID = HF.FriendID
WHERE H.Summit = 'Kilimanjaro'


MySQL 5.1: Upgrade innodb plugin My application is running on MySQL 5.1.58 community edition. As I studied on SO websites that After acquisition by Oracle, new InnoDB plugin has been released and MySQL 5.1 with InnoDB plugin is the best for performance. Below is the command which displays the current InnoDB configuration in my system:

mysql> SELECT * FROM information_schema.plugins where plugin_name like 'InnoDB';
PLUGIN_NAME : InnoDB
PLUGIN_VERSION : 1.0
PLUGIN_STATUS : ACTIVE
PLUGIN_TYPE : STORAGE ENGINE
PLUGIN_TYPE_VERSION : 50158.0
PLUGIN_LIBRARY : NULL
PLUGIN_LIBRARY_VERSION : NULL
PLUGIN_AUTHOR : Innobase OY
PLUGIN_DESCRIPTION : Supports transactions, row-level locking, and foreign keys
PlUGIN_License: GPL
1 row in set (0.00 sec)

To upgrade this plugin, I have added below in my.ini file:

[mysqld]
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.dll;

My plugin folder is below:

E:\MySQL\MySQL Server 5.1\lib\plugin

ha_innodb_plugin.dll and ha_innodb_plugin.pdb are available in the above plugin directory.

Now, when I check using show plugins; command, I can't see InnoDB entry in the resultset.

Please answer me below things:

Why am I not able to see the InnoDB in plugin list?
Is it possible to upgrade InnoDB plugin with my existing MySQL version?
If yes, then is it my approach correct?
If not, what will be the correct approach?
Will it help me to improve the system performance?
For this, should I upgrade from mysql 5.1.58 to 5.1.73?

I don't know why you can't see the InnoDB plugin. I've never seen that happen.

You can also use this method to check what version of InnoDB you have loaded:

mysql> SHOW VARIABLES LIKE 'innodb_version';

If it returns a number, then you're using the InnoDB plugin. If it returns nothing, then you're using the builtin InnoDB (or no InnoDB at all).

I suggest that you upgrade to MySQL 5.5. You don't have to fuss with loading the InnoDB plugin, it's loaded and enabled by default, and the builtin InnoDB is not present. Also, MySQL 5.1 is officially end-of-life, and it's no longer a supported product as of 2013-12-31.

  Then, is there any other way through which I can upgrade InnoDB plugin related Dll files?

Virtually everyone does this by upgrading the version of MySQL, for example 5.1.58 (2011-07-05) to 5.1.73 (2013-12-03). As you can imagine, there were a lot of important fixes, including security fixes, in the 29 months between these two versions.

In theory, you could download a Zip file of 5.1.73, unpack it and retrieve the InnoDB plugin DLL, and manually move it into the plugin directory of your installed 5.1.58 instance. You should shut down the MySQL Service before doing this.

The caveat is that you would be enabling a version of the plugin (5.1.73) that was never tested with the older version of MySQL. They aren't supposed to break backward compatibility between point-releases, but internal interfaces aren't as strict about that.

Also, upgrading only the InnoDB plugin would not include any fixes implemented in the rest of the MySQL code outside the storage engine.

PS: For what it's worth, the InnoDB plugin existed in MySQL before the Oracle acquisition of MySQL.

2005-10-07: Oracle announces acquisition of Innobase Oy.
2008-04-15: First alpha of the InnoDB plugin.
2009-09-01: First beta release of the InnoDB plugin in MySQL 5.1.38.
2010-01-27: Oracle completes the acquisition of Sun Microsystems.
2010-04-06: First GA release of the InnoDB plugin in MySQL 5.1.46.


Backup files - Are they safe to delete? I've got a back up process which initially writes to a local disk, then the disk gets backed up remotely. After each back up (log, diff and full) I'd like to delete all the files on the local disk. Will SQL Server notice this?

Does MS SQL read the bak files to know what has been backed up and what hasn't for example?

I've got a database on AWS with a disk used only for backups. After every backup a snapshot is taken of the disk (via an AWS CLI script). So now I have the data on the disk and in the snapshot. The disk costs $200+ dollars a month.

I want to change our back up process to create the disk, do the back up, take the snap shot and then drop the disk.
Will SQL Server get upset if I mess with it's log files in this way?

I can't speak to 'via an AWS CLI script' specifically. But SQL does not care where the backups are.  It knows they got taken and where they were written to.

Assuming you are using a system wide backup product like TSM to backup your SQL backups as well as the rest of the machine:

Don't delete the fulls and diffs, overwrite them.

If you run your full and/or differential backups at 6AM and you backup them up remotely at noon.  You can safely overwrite them at 6AM the next day.

You can delete the local t-logs when they are at the remote location.  You might want to leave 24-48 hours of them locally for "accident" (someone did something stupid) recovery.

It is important that ensure that your SQL backups are completed before the the remote (i.e. TSM) backup process runs.  You create SQL backups, because products like TSM will not backup files in use, like your mdf & ldf files.  It will also not backup, your SQL backups if they are being written to during the off site backup.  I ran a 7 week experiment appending backup files, and TSM did not move the backups of site until the experiment ended.

  Off site backups, It looks like my offsite backup solution (IBM Spectrum) is not backing up the trn file. I suspect this is because the file is constantly being edited. Source

Besure to regularly validate your backup solution with restores from your offsite location.


Is there a way to reduce the amount of time spent on a point in time recovery? I'm pretty sure I know the answer to this, but the limitations of a point in time recovery blows my mind.

Say I have a 2TB database set to FULL recovery mode. I diligently take weekly full backups, daily diff backups, and log backups every 30 minutes.

Now, I want to roll the database back to 1 hour ago. Do I really have to restore the entire full backup, diffs, and logs up to one hour ago? Am I correct that this will take a very long time?

Is there no way to ROLLBACK the changes using the transaction log files so that this type of recovery takes a few minutes?

Yes, you would have to restore the full backup, at maximum one diff backup, and all necessary log backups to perform a point-in-time recovery in SQL Server. In SQL Server, you cannot roll back already committed transactions to go back in time. You would likely want to use the STOPAT syntax to restore your database to the exact desired point in time.

In fact, because you are doing transaction log backups every 30 minutes, SQL Server is also clearing out the transaction log with each log backup--so to go back to an hour prior, SQL Server wouldn't even have the necessary information to roll back that far, even if SQL Server had such a feature (which it doesn't, sadly).

I can't answer whether it would take a "very long time" because restore time varies quite a bit based on how large the database is, how many changes are contained in the log / diff backups, and how many log backups need to be restored. For a small database that isn't very busy, the restore could be quite fast, but for a very busy multi-terabyte database, it could take a while.

Oracle has a feature called Flashback that will let you do what you describe, but SQL Server doesn't have an equivalent feature.


How hard would it be to have PostgreSQL run a test to adjust the cost parameters? By using EXPLAIN one can see the cost of running a query. The cost is in artificial units, and they are based on pre-defined parameters. 1 is one disk fetch and a CPU cycle is 0.01 units, I believe. My question is why PostgreSQL does not automatically run a test upon installation to determine the real numbers based on the hardware? Shouldn't this be an obvious quick win?

It the costs were constant there might be a marginal gain.  The units are necessarily artificial as the various resources are not interchangeable.  Even on the same system the costs can vary at different times.

If the CPU becomes fully loaded, then CPU cycles become more expensive.
On a multi-core system CPU cost may be less than observed by testing one CPU.
If the disks become busy the cost of a disk access increases.
Systems may have different disks with different performance (read cost).  Cost will vary depending on which disk or disk subsystem the data is on.
Different distribution of data across disks may vary the performance.
Different placements of indexes and data may vary the performance.
Placement of data across disks of different performance will result in disk I/O having multiple costs depending on which data is being accessed.
Different types of queries (batch vs OLTP) may have different appropriate cost distributions.

Once the database is created, it may be possible to determine better metrics from the database.  Use of the updated metrics may change loads in ways than make them invalid.  There are margins beyond which behavior becomes relatively un-predicable and likely to seriously degrade.


how do I limit the characters in selected row and con-cat with '...' I have a query which return a column named comment. I want to limit the number of characters returned by select statement to 50 and if the comment row contain more than 50 characters, I want to append ... at the end of it. How can i achieve this using on SQL query ?

Here is the query for my question.

select comment from mip
where active = 1

You can use MySQL's IF function

select
    IF(LENGTH(comment) > 50,CONCAT(LEFT(comment,50),'...'),comment) comment
from mip where active = 1;

or

select
    IF(LENGTH(comment) > 50,CONCAT(SUBSTR(comment,1,50),'...'),comment) comment
from mip where active = 1;


MS SQL servers in different countries linked for ETL via SP The title could be a bit complicated, but the question is very simple...
We developed an application which uses a proprietary database (call it DB_ONE). Then, there's another application with its own database (call it DB_TWO). Some data stored in DB_TWO are useful for DB_ONE (in the way they already are or with a little bit of modifications). So we developed some stored procedures to extract data from DB_TWO, trasform them and load them in DB_ONE (like an ETL process). We must maintain the data aligned, so the SPs are called quite often.

We first developed this solution thinking that the two databases would reside in the same SQL Server, but we used synonim with four part name convention to support different servers. In the last case, the servers must be linked and we observed that performance were good, with no modifications or just little modifications to the SPs. This happened when the data weren't so much or in a LAN.

Now the problems start... We would like to use the same solution with a customer having the DB_ONE in a country and DB_TWO in a different one. The amount of data is very big and in the country of DB_ONE the connection quality is not that good... The solution still works, but it requires a lot of bandwidth, the execution time is long and the synchronization can't happen to often (which is bad for some SPs).

Do you have any suggestion to improve the quality of the solution without adopt a different approach (like HTTPS compressed data exchange)...? Is it possible to compress the data, optimize the load and/or the transfer? In this scenario, the encryption of data over the WAN would also be useful...

I had to work around a similar issue where the two locations where 13,000 kilometers away and the internet connection on one of the locations was 1995 level of poor:

(Forget about merge replications and all other fancy methods).

So after all kinds of test including Azure and other cloud services I realized I was gonna have to go with something far less orthodox.

I created a new custom TCP/IP service (basically a silent console application that runs in the background on a separate thread than the users frontend) using C# that basically queries the database on either end and finds modified, deleted and new records every 15 minutes (using GUID to make sure records on both sides have same identifiers) it will then compress and encrypt this data and send it to the other location where a queue table with timestamps will determine whether or not the records are to be committed. If a record on the receiving instance has a newer timestamp that the record being brought in (accounting for a 4 hour difference) then they go into a conflict resolution routine.

Then some modifications to the users frontend where necessarily mainly so they're aware of data changing on one side or new records added to the database.

All of this was possible because they only needed 3 tables to be doing this cycle, I will not recommend it for drastically more complex scenarios.


Calling stored procedure from job does nothing I have very simple stored procedure

CREATE PROCEDURE FillMissingBirthDateFromMemberNumber
AS
BEGIN
    UPDATE Members
    SET BirthDate =
        CASE
            WHEN CONVERT(DATE, SUBSTRING(m.MemberNumber, 0, 7), 12) > '2030-01-01'
            THEN dbo.fn_LocalTimeToUTC(DATEADD(YEAR, -100, CONVERT(DATE, SUBSTRING(m.MemberNumber, 0, 7), 12)))
            ELSE dbo.fn_LocalTimeToUTC(CONVERT(DATE, SUBSTRING(m.MemberNumber, 0, 7), 12))
        END
    FROM Members m
    WHERE
        m.BirthDate IS NULL AND
        m.MemberNumber IS NOT NULL AND
        m.MemberStatus IN (1,2)
END

that when executed in a query window, performs exactly as promised. It fills out missing birth dates based on the first six chars of the member number (with some caveats, as the CASE WHEN indicates).

Now, I have created a SQL job that simply EXECs this stored procedure and reports success when run.
Only: nothing happens.

Also when I paste the code from the SP into the Command section directly, the result is identical: success but no rows affected when checking afterwards.

I have recreated the SP and the job, thinking I did something wrong. I have also changed the job owner to different accounts (from my own all the way to admin) thinking it had something to do with rights - the job history consistently reports success across the board.

What am I doing wrong or what am I not seeing?

I started yesterday by profiling the job execution and found nothing of interest.

As it turns out, the devil was in calling [MyOtherDatabase].dbo.fn_LocalTimeToUTC.

For some reason (that is beyond me), there's some credentials checking going on (yes: in a time conversion function...) but if that fails, no error is returned - but also no result. Don't ask. I didn't write it.

Once I decided to skip the conversion everything worked flawlessly.

I'll be wanting those two days of my life back now.


Linked Server Query is not working in SQL Agent job I have a problem with linked server query from SQL Agent job. Linked server connection working fine. But while executing query from sql agent job it fails.

  Microsoft OLE DB Provider for SQL Server: Login failed for user

I used current context credential. All fine from linked server settings. But, I can not run the query from SQL Agent job.

When you run the SQL Server Agent job, it executes the query using the credentials of the account for which the SQL Server Agent service is running.

For linked servers and utilizing Windows Authentication, it has been my experience that it is necessary to set up the Keberos authentication for the service accounts in Active Directory, and set the Service Provider Name (SPN) by running the SETSPN command for the SQL Server service account for the SQL Server machine. See this article: https://technet.microsoft.com/en-us/library/bb735885.aspx

If you are using the SQL Authentication, I would recommend trying to log in using the account that the SQL Server Agent service uses and seeing if the query utilizing the Linked Server works correctly.

I found this code a while back, and I don't remember exactly where. So, I can't give proper credit. It should generate the code to set the SPNs if they aren't done correctly.

SET NOCOUNT ON

-- service account
DECLARE @DBEngineLogin VARCHAR(100)

EXECUTE master.dbo.xp_instance_regread
   @rootkey = N'HKEY_LOCAL_MACHINE',
   @key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
   @value_name = N'ObjectName',
   @value = @DBEngineLogin OUTPUT

-- SELECT [DBEngineLogin] = @DBEngineLogin

DECLARE @physicalServerName varchar(128) = '%' + cast(serverproperty('ComputerNamePhysicalNetBIOS') as varchar(64))+ '%'
DECLARE @ServerName varchar(128) = '%' + cast(SERVERPROPERTY('MachineName') as varchar(64)) + '%'
DECLARE @spnCmd varchar(265)

SET @spnCmd = 'setspn -L ' + @DBEngineLogin
CREATE TABLE #spnResult (output varchar(1024) null)
INSERT #spnResult exec xp_cmdshell @spnCmd

CREATE TABLE #spnLIst (output varchar(1024) null)

INSERT #spnLIst
SELECT output as 'SPN List for Service Account' FROM #spnResult
WHERE output like @physicalServerName or output like @ServerName

Declare @NodeName VARCHAR(128)
DECLARE db_cursor CURSOR FOR
SELECT '%' + NodeName + '%' AS NodeName FROM fn_virtualservernodes()

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @NodeName

WHILE @@FETCH_STATUS = 0
BEGIN
   INSERT #spnLIst
   SELECT output as 'SPN List for Service Account' FROM #spnResult
   WHERE output like @NodeName

   FETCH NEXT FROM db_cursor INTO @NodeName
END

CLOSE db_cursor
DEALLOCATE db_cursor

SELECT DISTINCT output as CurrentSPNRegisterStatus INTO #spnListCurrent FROM #spnLIst

TRUNCATE TABLE #spnLIst

-- GET Port Number
DECLARE @PortNumber varchar(10)
SELECT @PortNumber = cast(local_tcp_port as varchar(10))
FROM sys.dm_exec_connections WHERE session_id = @@SPID

-- GET FQDN
DECLARE @Domain NVARCHAR(100)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\Tcpip\Parameters', N'Domain',@Domain OUTPUT

INSERT #spnLIst
SELECT 'MSSQLSvc/' + cast(serverproperty('ComputerNamePhysicalNetBIOS') as varchar(64)) + ':' + @PortNumber
UNION ALL
SELECT 'MSSQLSvc/' + cast(serverproperty('ComputerNamePhysicalNetBIOS') as varchar(64)) + '.' + @Domain  + ':' + @PortNumber
UNION ALL
SELECT 'MSSQLSvc/' + cast(SERVERPROPERTY('MachineName') as varchar(64)) + ':' + @PortNumber
UNION ALL
SELECT 'MSSQLSvc/' + cast(SERVERPROPERTY('MachineName') as varchar(64)) + '.' + @Domain + ':' + @PortNumber

-- If this serve is clusterd, need to check for all Physical nodes
IF SERVERPROPERTY('IsClustered') = 1
BEGIN

   INSERT #spnLIst
   SELECT 'MSSQLSvc/' + NodeName + ':' + @PortNumber
   FROM fn_virtualservernodes()

   INSERT #spnLIst
   SELECT 'MSSQLSvc/' + NodeName + '.' + @Domain  + ':' + @PortNumber
   FROM fn_virtualservernodes()

END

IF NOT EXISTS(SELECT CurrentSPNRegisterStatus FROM #spnListCurrent)
   SELECT 'NO SPN has been registered' as CurrentSPNRegisterStatus
ELSE
   SELECT CurrentSPNRegisterStatus FROM #spnListCurrent

SELECT
   CASE
      WHEN A.CurrentSPNRegisterStatus is NULL THEN '*Missing SPN - See SPNGenerateCommandLine'
      ELSE A.CurrentSPNRegisterStatus END AS 'CurrentSPNRegisterStatus',
   CASE
      WHEN B.output IS NULL THEN '*** Review for Remove or you have multiple instance ***'
   ELSE B.output end as SuggestSPNList,
   CASE
      WHEN B.output is null THEN
          'SETSPN -D ' + A.CurrentSPNRegisterStatus + ' ' + @DBEngineLogin
   ELSE 'SETSPN –S ' + output + ' ' + @DBEngineLogin END as SPNGenerateCommandLine
FROM #spnListCurrent A
FULL OUTER JOIN #spnLIst B on REPLACE(A.CurrentSPNRegisterStatus,CHAR(9),'') = B.output
WHERE CurrentSPNRegisterStatus is NULL OR B.output IS NULL

IF @@ROWCOUNT = 0
 SELECT 'All SPN has been registered correctly. If you are running for AG Group, this script does not check so please check manually' as SPNStatus

DROP TABLE #spnResult
DROP TABLE #spnLIst
DROP TABLE #spnListCurrent
GO


Special Formatting of Numbers I need help formatting numbers in a specific way.

If a number has 3 digits or less, I would like it to remain the same.

If a number has more than three digits, I would like it place a decimal after the third decimal.

For instance:

123 --> Stays the same
1234 --> 123.4
1234567 --> 123.4567

I am using SQL Server and want to UPDATE the value in the table. The value is stored as a numeric.

Assuming they are all ints and you always want the first three digits to appear before the decimal:

WITH n(r) AS (
  SELECT 123 UNION ALL SELECT 1234 UNION ALL SELECT 1234567
)
SELECT LEFT(r, 3) + CASE
  WHEN LEN(r) > 3 THEN '.' + SUBSTRING(RTRIM(r),4,38) ELSE '' END
FROM n;