Difference between sql agent account logged in as NTLM and UPN? Can someone please tell the difference between a sql agent account logged in as NTLM and UPN?

What is the difference if the sql agent account is logged in as:
domain\username and username@domain.com?

In what way will the functionality affect and where?
Would it affect 'execute as' statements in sql stored procedures?

Microsoft SQL Servers do not allow the use of User Principle Name (UPN) addresses for service accounts.  There is a post from a few years ago in Microsoft Connect and a more recent post:

https://connect.microsoft.com/SQLServer/feedback/details/519008/upn-user-principal-name-as-services-account-name#details

http://www.ryanjadams.com/2014/02/error-authenticating-proxy-sql-and-upns/#axzz2x0igTI3n

Therefore you need to stick with the DOMAIN\USERNAME format for the service accounts.

(Original post failed to commit.  Repost.)


How to manage concurrencies without locking tables in MySQL? In our php app, we have a table that records orders recieved at the reception. Since we can have up to 7 agents/active accounts, how is it possible to manage the concurrencies in the orders table without having to lock the table each time.

The issues are:

That before inserting any row in the main table, there must be a chain of check ups to run on the customer, etc... which impose multiple selects, then the insert.
Orders are loaded by batch, not one by one, by the agents. I fear locking the table will impose them to redo the orders' registration again due to the database forbiding access.

Should I even be worried of the writing management of MySQL or, indeed I have to procede to a locking system?

Concurrency and isolation are the two topics you are dealing with here - the ability for more than one user to use the system simultaneously and for each's work to be unaffected by the others'. RDBMS handle this through isolation levels and locks. It is how they are designed to work; it is what they do. Our job as application architects is to choose settings which meet the requirements and build the system in such a way that minimises the time window in which conflicts can occur.

In this system you want consistency between rows in the Order table and other tables. For this you need locks. This is precisely what locks are for. Don't be scared of them. They are your friend and protect your data. For the reference tables you want to ensure you are taking shared locks by only reading and not writing, and preferably row locks. This way multiple concurrent activities can share the reference data.

Writing the Order will create an exclusive lock. Hopefully on just the new row but maybe on the table. It is therefore important to release that lock quickly by committing the transaction as soon after the insert as possible.

What you do not want is that these locks are held while the agents are typing. That is an eternity in computer terms. By all means validate input as they type but only as single statements, never in a transaction spanning the whole data entry session. Then at the end, when the order is saved, start a transaction, re-read the reference data, insert the Order and commit. On any platform that should complete in milliseconds. The other operators won't even notice it happening. If there is an error or a conflict the agent will get immediate feedback, which they can correct and re-submit.

There is an alternative, which I wouldn't recommend, but list here for interest's sake.  You could architect the system on an asynchronous, message-processing design. When an agent saves data it is written to a single staging table which has one column per data value.  No validation, no normalisation. One single insert and the data's persisted. With 7 operators the chances of there even being two operations in the DB at one time are almost zero. A second process polls this table to read one row at a time. It validates and writes to the normalised schema. The result - success or failure - is recorded. Then the next row in the staging table is processed, and so on. This guarantees that all inserts are fully serialised, at the expense of a significantly more complicated design, no immediate feedback to agents and the need for additional business processes and programming to correct errors. Also this will not protect against activity from other programs connecting to the database. Something has to be reading those orders after all. As I said, not recommended.

Seven agents entering data at the same time is not a big number.  You will be able to construct a system to handle this, I promise.


Recovering a SQL Server database In a series of unfortunate mistakes, I lost 4 months of data and all the rapid database modifications. I’m not a database administrator, I’m a programmer who also designs and happens to maintain a database.

Initially I just wanted to only compare some database objects to another database. I did that using OpenDBDiff tool. I selected the objects that I wanted to update and hit ‘Update selected’. It failed for some reason, but I didn’t pay any attention to it. So I generated a script to execute directly into SSMS. I again failed to notice that the script included all objects, and not just the selected ones. I realized too late, by then, some of the destination's database objects i.e. tables, views, functions, etc. were already dropped.

And then it got worse,  I know we have daily backup and decided to restore from there. It turns out that for some reason, this database, on that server has its last backup 4 months ago and didn't run afterwards successfully. I decided that I’d restore from its transaction log to the current date. I still don’t know if that’s even possible, but I decided to do it on a live database. I did not take a new backup even at this point, I was afraid that this will destroy the transaction log. So I ran a command like this:

USE [master]
BACKUP LOG [mydb] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\mydb_LogBackup_2019-01-17_13-49-00.bak' WITH NOFORMAT, NOINIT,  NAME = N'mydb_LogBackup_2019-01-17_13-49-00', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5
RESTORE DATABASE [mydb] FROM  DISK = N'D:\Backups\mydb_2018_08_30_19_01_01\mydb.bak' WITH  FILE = 1,  MOVE N'exact_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mydb.mdf',  MOVE N'exact_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mydb.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [mydb] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\mydb_LogBackup_2019-01-17_13-49-00.bak' WITH  NOUNLOAD,  STATS = 5,  STOPAT = N'2019-01-17T13:00:00'
GO

The log backup failed. The database is now in restoring state. Before all this, mydb.mdf and mydb.ldf files where located in the D:\db folder, now they are not there. Instead they are in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\. I tried to attach them to my local server, it fails because they are in restoring state too. I download and managed to open the with a SQL Server database repair tool, but the only hold the data from 4 months ago.

I’m currently scanning the computer for deleted files to search for the original .mdf and .ldf files, but I don’t have much hope on this because the quick scan didn’t show anything and I’m running a deep scan now.

Questions

How can I take the database out of that (restoring…) state and what will happen if I do so? This is why I decided to ask before I do anything else that might be destructive.
My goal now is to restore the database in the most recent possible state, so how can I achieve that?

The last successful restore was the RESTORE DATABASE. Since you specified NORECOVERY, the database is, as expected, in restoring state. If you don't have any more backups to restore and just want to get out of the restoring state, you just do:

RESTORE DATABASE mydb WITH RECOVERY

That's it.  You find the database as it were when that full backup you restored was produced.

As for log backups, you need an unbroken chain of log backups. You can't skip a log backup, like you most probably tried to do. You likely got an error message saying that the log backup was too recent to restore, citing LSN numbers and that it requires some earlier log backup with some earlier LSN number. There's no way around the "unbroken chain of log backups" requirement. If you can't find those log backups, or a more recent full backup, then you are probably stuck with a 4 month old database.


completely remove mariadb database and root password The system is Fedora 32. Mariadb was installed and operational; it is now necessary to reinitialize Mariadb and all data and users.
I removed mariadb-server and mariadb and erased /etc/my.cnf and my.cnf.d. I then reinstalled mariadb and mariadb-server and ran the mysql_secure_installation script. It found the original root password but otherwise completed normally.
I then logged into mariadb. I expected to find the users from the original installation had been removed; however they had not.
show databases; shows the original tables. They were not removed.
select * from mysql.user; showed the original users.
How can mariadb be completely removed including all users, data, and passwords?

You need to delete the data in your datadir, since that is where this is stored. The default location is /var/lib/mysql/. Shut down MariaDB and delete everything inside the datadir without deleting the datadir itself (as you want to avoid having to recreate the directory with exactly the right permissions):
sudo systemctl stop mariadb
sudo rm -rf /var/lib/mysql/*

Then use mysql_install_db to re-initialise the datadir.


How can I improve a simple join on a very small table? (postgres) I have a large table with a foreign key to a smaller table:

CREATE TABLE cars (
  make_model_id INTEGER INDEXED REFERENCES models (make_model_id),
  color TEXT INDEXED,
  year INTEGER INDEXED,
  details TEXT
);

CREATE TABLE models (
  make_model_id SERIAL PRIMARY KEY,
  make TEXT,
  model TEXT
);

cars is hundreds of millions of rows, while models has about 30 rows total.  Most queries look like this:

SELECT * FROM cars JOIN make_model USING (make_model_id)
WHERE
  color = 'green' AND
  year = 2015 AND
  make = 'Honda';

Looking at EXPLAIN, the query planner does a nested loop to join the two tables rather than collecting the rows from models and using the index on cars.  If I manually run SELECT make_model_id FROM models WHERE make = 'Honda';, that result is instant, then if I use the result in the original query with an IN clause, it shortens the run time by more than half.  Is there a way to do this two-step process in a single query?

I've tried moving the query to an in-line clause (make_model_id IN (SELECT make_model_id FROM models...) and as a CTE, but both yield near-identical performance to the original query.

This is the best index

CREATE INDEX ON cars (color, year);

Make sure that you have correct statistics, particularly on the small table:

ANALYZE make_model;
ANALYZE cars;


Triggers not reflecting on procedure I have a trigger

create trigger flightinput2 on reservation
after insert
as
begin
update reservation
  set Departure_Airport='LDN',
      Arrival_Airport='KUL'
  where Flight_Code='EM0088'
end

then I have a procedure to modify Flight_Code say from "MH0077" to "EM0088"

create procedure modifyvalue @ID int,@flightcode varchar(6), @date datetime as
begin
update reservation
  set Departure_Time=@date,
      Flight_code=@flightcode
  where ID=@ID

  select * from reservation
end

drop procedure modifyvalue

execute modifyvalue '12','EM0088','2014-08-16'

The result I get made changes to Flight_Code and other necessary data from the procedure but not the departure_airport and arrival_airport as the designed trigger.

I hope I explained my situation clear enough. What did I do wrong, I figured I might not doing this the correct way for the trigger to fire

Your trigger is designed to only fire after an insert not an update. Try changing the code to:-

create trigger flightinput2 on reservation AFTER INSERT, UPDATE as
begin
   update reservation set Departure_Airport='LDN',Arrival_Airport='KUL' where Flight_Code='EM0088'
end

EDIT:- As pointed out, the code above will update all records within the database that have a flight code of EM0088

To prevent this you could update the code to:-

 create trigger flightinput2 on reservation
 after insert, update
 as
 begin
      update reservation
        set Departure_Airport='LDN',
            Arrival_Airport='KUL'
        from inserted ins
  inner join reservation r on ins.PrimaryKeyColumn = r.PrimaryKeyColumn
       where r.Flight_Code='EM0088'
  end

Just change PrimaryKeyColumn to the PK of your table.

EDIT2 - If you want to change values in the table based on flight codes you could do this:-

  create trigger flightinput1 on reservation
  after update
  as
  begin

  declare @code char(10);

  select @code =
    ins.flight_code
  from inserted ins

  if @code = 'MH0007'
    update reservation
    set Departure_Airport='XXXX',
    Arrival_Airport='KUL'
    from reservation r
    inner join inserted ins1 on ins1.resid = r.resid

  else if @code = 'EM0088'
    update reservation
    set Departure_Airport='LDNXX',
    Arrival_Airport='KUL'
    from reservation r
    inner join inserted ins1 on ins1.resid = r.resid

  end


Is there any need to persist a (precise) computed search column that I've indexed? I have a number of columns that store data that may contain some extra characters for formatting/grouping. Think white space and dashes in data like phone numbers and license plate numbers.

When querying, we can't be sure if and where these dashes are present, so we need to strip them from the column. Doing so while querying isn't great for performance, so I want to create a computed column for the stripped column and put an index on that.

-- trimming the length down to 50 characters
-- which is the length of the original field
ALTER TABLE foo
ADD search_license_plate AS (LEFT(REPLACE(license_plate, '-', ''), 50));

CREATE INDEX ix_foo_search_license_plate ON foo(search_license_plate);

These computed columns are always precise, so they don't need to be persisted because of a lack of precision.

The data will typically only be entered once and will rarely be altered. Never in high volumes, so I don't care much about any performance impact of persisting the column.
Also, the computed column will never be returned from a query; it will only be used in conditions on the query, typically

SELECT
   foo.id,
   foo.license_plate
FROM foo
WHERE foo.search_license_plate LIKE '%bar%';

... although I can't guarantee that there will be no SELECT * done on these tables.

Is there any need to persist these columns?

Is there any need to persist these columns?

Generally, no, though there are a number of potential issues that will require validation with your particular workload and client applications.

The major requirements (including SET option requirements for all clients) are listed in the documentation at Indexes on Computed Columns. You may also, for example, be required to add the PERSISTED attribute for a number of reasons e.g. to add a CHECK or NOT NULL constraint.

The reason I have emphasised the need to test above is that SQL Server will sometimes choose not to use an index on a computed column (even if persisted!) in a range of scenarios that are tricky to enumerate in advance - and where using the index is "obviously" the best strategy. For some examples and background, please see my article Properly Persisted Computed Columns.

The upshot is that each query needs to be tested to ensure that SQL Server reliably chooses the index where that strategy is important for performance.

You can encourage selection of the index access method by making it covering for the target queries (so the index contains all columns needed by the query), but even this does not come with a guarantee. Using an index hint does come with a guarantee, but has risks including throwing an error if the index is unavailable.

There is also a limit to which a b-tree index can assist for leading-wildcard searches as given in the question:

WHERE foo.search_license_plate LIKE '%bar%';

At best, this will result in a full scan of the index on the computed column. You may also be interested in Trigram Wildcard String Search in SQL Server as an alternative.


What is better join, in or in with join I have these tables:

calls: rows [ 'created_at', 'user_id']

users: rows ['id', 'username']

And I need to get all user without calls from date:
How can I get it? Or what way is better and why?

I know three ways:

first:

SELECT id
FROM users
WHERE id NOT IN (SELECT user_id FROM calls where calls.created_at >= date)

second(I think it is not right, but I am not sure):

SELECT id
FROM users
LEFT OUTER JOIN calls
   ON calls.user_id = users.id
WHERE calls.created_at >= date AND calls.user_id ISNULL

last:

SELECT id
FROM users
WHERE id NOT IN (
   SELECT user_id
   FROM calls
   INNER JOIN users AS call_users
      ON call_users.id = users.id
   WHERE calls.created_at >= date

DB: PostgreSQL

SQL is specifically meant to express simple queries more or less like you would ask them as a question in English. For instance, your question is like:

  "What are all the id from users that have no
  associated call in calls with a created_date greater or equal than date $1 ?"

SQL translation:

SELECT id FROM users
  WHERE NOT EXISTS (
    SELECT 1 FROM calls WHERE call.user_id=users.id AND created_date >= $1
  );

It's a rather direct translation except maybe for SELECTing 1, which is an arbitrary value.

There is no reason to assume that PostgreSQL would produce a non-optimal execution plan for this query. The optimizer is certainly smart enough for that kind of queries. In general if there's a straightforward way to express your query, there's no reason to pretend to be smarter than the optimizer.

When you doubt that the optimizer found the best possible plan (say for more complex queries), you can start from the output of EXPLAIN to figure out if/why it's not optimal and typically try to improve the query by trial and error.

If you want to compare how different variants of the same query are planned, compare the outputs of EXPLAIN ANALYZE of the queries.

https://wiki.postgresql.org/wiki/Slow_Query_Questions is a good start too.


Selecting multiple columns from multiple rows in one column (subquery?) I'm working with MySQL 5.1.68.

I have a situation where I'd like to select rows from another table into a column.

I'd like to output this:

id # name # citizen_name (multiple citizen names)

1 # The Hague # Barack Obama, Marc Zuckerberg, George Bush

I know how to do this with multiple queries, but I'd like to do this with just one query. I've been looking at group_concat and concat_ws, but unfortunatly this hasn't brough me the result I was looking for. I've also tried to put the results from a SELECT sub query into a variable and CONCAT those results, but that didin't work either.

My SQL fiddle to show the schema and some test data: http://sqlfiddle.com/#!2/c7c7e/1.

What can I do?

You need to use GROUP_CONCAT to aggregate the citizen names first. Then use CONCAT

SELECT CONCAT(id,' # ',name,' # ',citizens) listing FROM
(
    SELECT A.id,A.name,GROUP_CONCAT(B.name,' ',B.surname) citizens
    FROM city A INNER JOIN citizen B
    ON A.id = B.city_id GROUP BY A.id,A.name
) AA;

Here is the SQL Fiddle to prove it : http://sqlfiddle.com/#!2/9f4b3/1

If the names become too long due to truncation, you will have to extend GROUP_CONCAT's maximum length. To set it to 16K, run the following:

SET group_concat_max_len = 1024 * 16;
SET GLOBAL group_concat_max_len = 1024 * 16;

First line is for the session, the second is for all new DB Connections.


MySQL: check max chracter length (ERROR 1118 (42000): Row size too large) I'm trying to convert/copy MyISAM to InnoDB table and I'm getting the following error message:

mysql> INSERT INTO test.innodb_table
    SELECT * FROM test.myisam_table
ERROR 1118 (42000): Row size too large (> 8126).
Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC
    or ROW_FORMAT=COMPRESSED may help.
In current row format, BLOB prefix of 768 bytes is stored inline.

The MyISAM table has many VARCHAR(255) columns so it looks like it's reaching the maximum row size limit for InnoDB table.

Questions:

How can I check on which row(s) the query above is actually failing so I can potentially remove it? I tried to use where clause and I was able to insert ~5000 rows this way but the orignal table has over 2 million rows.
I would like to limit the size of these variable length columns. How I can check the max character length for all columns? Pseudocode: if column type equal to VARCH (255), show rows with max character length

UPDATE: Thu Apr 16 15:58:19 BST 2015:

I constructed the following query to get a list of rows that exceed the max row length:

mysql> SET SESSION group_concat_max_len = 102400;
    SELECT CONCAT('SELECT id, ',
             GROUP_CONCAT('LENGTH(', 'IFNULL(', COLUMN_NAME, ',
                      0)', ')' SEPARATOR '+'), ' AS total_length
        FROM myisam_table HAVING total_length > 8126;') AS 'Query'
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = (SELECT DATABASE())
      AND TABLE_NAME = 'myisam_table'\G

When I execute a query from the output of the query above, I receive a list of rows with a size greater than 8126 bytes:

+---------+--------------+
| id      | total_length |
+---------+--------------+
|     489 |        10938 |
|    5244 |         9141 |
|    5869 |         9680 |
|    6083 |        21284 |
|    6654 |         8644 |
...

I can then successfully insert this query so I don't understand how this exactly works and where is this size limit imposed:

mysql> INSERT INTO test.innodb_table SELECT * FROM test.myisam_table WHERE id = 489;

According to this, your problem is not with VARCHAR fields. However:

SELECT id, LENGTH(field1)+LENGTH(field2)...+LENGTH(field_n) AS total_length WHERE total_length > 8126
Can be done using 2 queries for each field. (This can be done in a complex query if you want to use INFORMATION_SCHEMA):

SELECT MAX(LENGTH(field1)) INTO @max_length1 FROM table_name;
SELECT * FROM table_name WHERE LENGTH(field1)=@max_length1;

Note: Increasing innodb_page_size's value may be helpful in your case.

Edit

To generate all the queries with the help of information_schema, run the following queries:

CREATE TABLE queries(field_name varchar(255), query1 varchar(4000), query2 varchar(4000));

INSERT INTO queries SELECT column_name,
CONCAT('SELECT MAX(LENGTH(',column_name,')) INTO @max_length FROM table_name;'),
CONCAT('SELECT * FROM table_name WHERE LENGTH(',column_name,')=@max_length;')
FROM information_schema.columns WHERE table_schema='test' and table_name='myisam_table';

SELECT * FROM queries;

Note that you have to run query1 then query2 from the result. DO NOT run all query1 then all query2!


How to update two tables using subquery in postgresql? I have two tables

main table is

Complaintid Status  Templateid
123           2
135           5      2
142           5      8
143           3
145           5

Second table

Parent_complaint_id Currentstatus   Reopen  Photo
123                     0             0     Abc.jpg
123                     2             0     Abc.jpg
135                     5             0     Xyz.jpg
142                     5             0     Sim.jpg
143                     3             0     123.jpg
145                     2             0     Ing.jpg
145                     5             0     Ing.jpg

After updation first table

Complaintid Status  Templateid
123          2
135          1       2
142          1       8
143          3
145          5

After updation second table

Parent_complaint_id Currentstatus   Reopen  Photo
123                  0                0     Abc.jpg
123                  2                0     Abc.jpg
135                  1                0     Xyz.jpg
142                  1                0     Sim.jpg
143                  3                0     123.jpg
145                  2                0     Ing.jpg
145                  5                0     Ing.jpg

Here is the first query

Update complaints
set status='1'
where templateid is not null

but i don't know how to add subquery in above query

For example

UPDATE second_table
SET Currentstatus = 1
WHERE EXISTS ( SELECT 1
               FROM first_table
               WHERE first_table.Complaintid = second_table.Parent_complaint_id
                 AND first_table.templateid is not null )

Or

UPDATE second_table
SET Currentstatus = 1
FROM first_table
WHERE first_table.Complaintid = second_table.Parent_complaint_id
  AND first_table.templateid is not null

Or ... too many variants.


Postgres master and slave out of sync I am not used to working on the postgres backend. So forgive me if I leave something obvious out.
I have postgres 9.4 running on 3 (debian) servers. #1 is the main prod server. #2 is slave to #1. #3 is for dev.
Currently #2 is out of sync with #1.  The #2 server rebooted and was stuck waiting for a password to restart. Several days later it was brought to my attention that #2 was down.  I went through the hosting site and was able to get the server back online. Postgres sync between #1 and #2 is broken. I do know that we have the WAL files backed up in an S3 bucket. What I can find online about this issue talks about rebuilding the server.
Is there a way to restart the sync without rebuilding the server?

Only if you have a WAL archive. In that case, you can configure restore_command to catch up from the archive.
Otherwise, rebuilding the standby us your only option.


Advice for ETL from Oracle to SQL Server? I'm building a data mart in SQL Server which needs to extract each month from an Oracle database.  Currently this is done by exporting to flat files, then running all the inserts/updates in SQL Server...

There are approximately 10 flat files to export then insert before setting off the updates.  Each file contains around 3.5 million rows and 5 to 10 columns per file.

Everything now is done strictly through stored procedures and I'm looking into SSIS assuming the permissions are properly granted this week.  I've also come across Rhino ETL.

Does anyone have advice on efficient though relatively painless ETL from Oracle to SQL Server?

Appreciate the help!

SSIS does a good work of exporting data from Oracle to SQL Server. I suggest you create staging tables in SQL Server and use SSIS to extract the data for the 10 Oracle tables. Then either use stored procedures in SQL Server and execute them using SSIS or use Merge syntax and try to do both insert as well as update in Control Flow. Updates tend to be slow in Data Flow task of SSIS using Oledb command for very large number of rows.

Go through this link Using SSIS to get data out of Oracle: A big surprise! which explains the performance of SSIS while loading fairly large amount of data in a short time.

To get started for ETL process from Oracle to SQL Server try to read this documentation.


Installing Oracle 11gR2 on Oracle Linux 6 - error possibly related to database (unique) name Installing 11gR2 on Oracle Linux 6.4. System is a desktop used by one developer. It's on DHCP. Hostname is similar to ip-t3400-1547.mydomain.local

Please note I have no issues installing this software on systems with fixed IP address, where the hostname is a solid alphanumeric string (no dashes).

I am loosely following:

Oracle® Database Quick Installation Guide 11g Release 2 (11.2) for Linux x86-64 E10860-01 August 2009
Oracle Database 11g Release 2 on Red Hat Enterprise Linux 6 Deployment Recommendations Version 1.1 March 2012

I've installed the package oracle-rdbms-server-11gR2-preinstall, which takes care of many pre-install tasks. I've set SELinux to permissive. Quote from /home/oracle/.bash_profile:

export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=joe
export ORACLE_UNQNAME=joe
export ORACLE_HOSTNAME=ip-t3400-1547
umask 022

"joe" is the owner of the machine, so I'm using his name as the SID (real name edited for privacy). Please note that everything described below happens with or without ORACLE_UNQNAME being set here.

I select Server Class, Single Instance, Advanced Install, Enterprise Edition, General Purpose configuration type, then:

Global database name: joe
Oracle Service Identifier: joe

The above parameters, and the variables in .bash_profile, are different when installing on a system with a fixed IP address. In that case, I set ORACLE_HOSTNAME to the actual hostname of the machine (e.g.: dbserver13.mydomain.local), ORACLE_UNQNAME to the hostname (dbserver13), and SID same as UNQNAME (dbserver13). Works great.

I cannot do that on the DHCP-based workstation, because our naming convention includes the dash character in the hostname, which is not accepted by the installer for all those parameters.

I believe this difference is what causes the installer to fail, but I can't figure out an alternative.

I have tried to add "joe" and "joe.mydomain.local" to /etc/hosts, on the loopback entry (beginning with 127.0.0.1) but it makes no difference.

Character Sets: Unicode. Select Use Database Control, notifications disabled.

Some pre-requisite packages fail the check, but this is fine.

The installer keeps chugging along, then towards the end it throws this warning:

Enterprise manager configuration succeeded with the following warning -

Error starting Database Control. Please execute the following command(s).

1) Set the enrvironment variable ORACLE_UNQNAME to Database unique name

2) /u01/app/oracle/product/11.2.0/dbhome_1/bin/emctl start dbconsole

The problem is, I have that variable set already. It makes no difference.

As a result, the installer only asks me to run one post-install script, instead of the usual two scripts. The Web UI doesn't seem to work.

I am obviously doing something wrong.

How should I choose all those database names and SIDs and whatnot, in a way that allows me to install 11gR2 on a DHCP-based desktop?

you must edit bash-profile then install oracle database .
in that you can set oracle_home and oracle_sid and etc .
i have similar mistake and resolve it with edit bash-profile in oracle user to real and correct values
at the end , you can refer to this article , it is useful

http://www.oracle-base.com/articles/11g/oracle-db-11gr2-installation-on-oracle-linux-6.php


Using expressions in the SELECT list to thin down a CASE statement I have a query I'm working through rewriting - it has a huge bunch of case statements in the select list that all basically look like this:

select
    Column1 =
        case when (Something = 2) and (SomethingElse is null) and (AnotherThing in (1, 2))
        then 1
        else 0
        end,
    Column2 =
        case when (Something = 3) and (SomethingElse is not null) and (AnotherThing in (2, 3))
        then 1
        else 0
        end,
    ...

With particular note to the "when <expressions> then 1 else 0" logic common across all of them.

It seems really bloated and wordy, a heavy-handed use of the case statement - my programming brain is telling me this could be achieved much more succinctly with some basic binary &ing logic, removing the case entirely:

select
    Column1 = (Something = 2) & (SomethingElse is null) & (AnotherThing in (1, 2)),
    Column2 = (Something = 3) & (SomethingElse is not null) & (AnotherThing in (2, 3)),
    ...

Unfortunately whilst SQL Server is able to handle something like select 1 & 0 (returns 0), it seems to choke on parsing expressions in the select list (Incorrect syntax near '=') - is it possible to do something like this? Some kind of evaluation function, maybe?

T-SQL doesn't support Booleans like that. But you could use CROSS APPLY with a subquery that uses CASE to produce a bunch of 1s and 0s, and then combine them in the SELECT clause using the bitwise operators & and |.


sys.sysprocesses versus DMVs I've seen a couple of blog posts where fairly knowledgeable sounding MVPs are still using sys.sysprocesses rather than the recommended DMVs: sys.dm_exec_connections, sys.dm_exec_sessions and sys.dm_exec_requests.

Given that sysprocesses is deprecated, I'm curious why anyone would continue to use it, particularly MVPs.  Is it just that using sysprocesses is less hassle than joining three DMVs or is there a better reason for using it?

The two blog posts I mentioned were:

Adam Machanic: Smashing a DMV Myth

Tim Chapman: Find blocking processes using recursion in SQL Server 2005

sys.sysprocesses still contains information that is either very cumbersome or impossible to get from the DMVs, since they weren't complete (they are a bit better with each new release, but not 100% yet). Two standout examples:

database_id for a non-active request (e.g. there is a row in sys.dm_exec_sessions but not in sys.dm_exec_requests)
open transaction count

I suspect this backward compatibility view will remain around until a few versions after they supply the information directly through the DMVs.


Combine multiple queries into single query I am trying to run the following query:

SELECT Freight.Freight_No,
       Freight.Available,
       Locomotive.Loco_No,
       Locomotive.Available,
       Driver.Driver_No,
       Driver.Name,
       Driver.Available
FROM Freight,Locomotive, Driver
WHERE Freight.Available = 'Y'
  AND Locomotive.Available = 'Y'
  AND Driver.Available = 'Y'
GROUP BY Freight.Freight_No, Locomotive.Loco_No, Driver.Driver_No;

What I am trying to do is check available freight wagons, locomotives and drivers. I can individually run a SELECT statements. However I want to combine into one query. I understand I can do this using UNION but I may not be going about it the correct way.

Tables

Freight

CREATE TABLE Freight
(
  Freight_No integer PRIMARY KEY, Serial_No integer UNIQUE,
  Freight_Wagon_Type_Name varchar(20)
    REFERENCES Freight_Wagon_Type(Freight_Wagon_Type_Name),
  Available varchar2(1)
     CONSTRAINT AvailableFreight CHECK(Available IN('Y','N')) NOT NULL
);

Locomotive

CREATE TABLE Locomotive
(
  Loco_No integer PRIMARY KEY,
  Serial_No integer UNIQUE,
  Loco_Class_No integer
    REFERENCES Locomotive_Class(Loco_Class_No),
  Loco_name varchar2(20),
  Available varchar2(1)
     CONSTRAINT Available CHECK(Available IN('Y','N')) NOT NULL
);

Driver

CREATE TABLE Driver
(
   Driver_No integer PRIMARY KEY,
   Name varchar2(20),
   Available varchar2(1)
     CONSTRAINT AvailableDriver CHECK(Available IN('Y','N')) NOT NULL
);

You can use UNION [ALL] to combine rows from multiple queries, as long as the number of columns are the same and the columns are compatible, if not of the exact same types.

SELECT 'Driver' as SourceTable, Driver_No, Name, Available
  FROM Driver
 WHERE Available = 'Y'
 UNION ALL
SELECT 'Freight', Freight_No, Freight_Wagon_Type_Name, Available
  FROM Freight
 WHERE Available = 'Y'
 UNION ALL
SELECT 'Locomotive', Loco_No, Loco_Name, Available
  FROM Locomotive
 WHERE Available = 'Y';

(added SourceTable column per a_horse_with_no_name's suggestion)

Note that the column names are taken from the first query in the UNION, which means you end up with (SourceTable, Driver_No, Name, Available).  You can always alias them as required.


MySQL Error 1062 after reboot node in multi master replication I setup 2 MySQL Community Server (8.0.20) with a replication master to master. Today one of the nodes went down, and when it went up, the replication failed. I tried to just ignore a registry with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;, but the errors keep coming in.
This is the status log of the particular node that failed:
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: database_1
                  Master_User: replicador
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000122
          Read_Master_Log_Pos: 217195469
               Relay_Log_File: 33afd376b907-relay-bin.000105
                Relay_Log_Pos: 2829
        Relay_Master_Log_File: mysql-bin.000122
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table gc57125800.ate_logs; Duplicate entry '49328847' for key 'ate_logs.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000122, end_log_pos 188792316
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 188791943
              Relay_Log_Space: 28407134
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1062
               Last_SQL_Error: Could not execute Write_rows event on table gc57125800.ate_logs; Duplicate entry '49328847' for key 'ate_logs.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000122, end_log_pos 188792316
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: bef45e1b-99d6-11ea-a355-3e2547e4f083
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 201029 17:59:51
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

Not sure what I should do. Should I dump the another master and do it all over again? Do I need to do this every time a server fails?

You should use pt-table-checksum to see the differences between the source and the replica. If the differences are many, importing data again from the source could be the fastest solution. Otherwise, you could restart MySQL with --slave-skip-errors=1062, fix the errors with pt-table-sync, and then restart MySQL normally.
To understand how these tools work and how they are used, take a look at this article: MySQL replication primer with pt-table-checksum and pt-table-sync.
You may also want to take a look at another tool called twindb-table-compare, which is complementary to pt-table-checksum. It reads the diffs generated by pt-table-checksum on the source and on the replica, and prints them in a format that is easier to understand, similar to the output of Linux utility diff.


what are all possible filters to add to a server-side trace? On this question you can see how to set up a filter on your trace.

this code below filters a trace file to capture only events that happen on database 'AdventureWorks'

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @bigintfilter = 1000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

exec sp_trace_setfilter @TraceID, 35, 0, 6, N'AdventureWorks'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

from the example above:

1) what is 13?

2) I know that 35 must be database Name.

How can I find what could be the other options (hostname, program, ObjectName, etc?) where are them stored? which system table or view?

Here you can see what filters you have on your trace.

I found this link helpful:
Need sp_trace_setfilter xref list of @columnid parameters

from this question: Find out what columns are in a trace file

there is this script

--  Gets all Events for a given trace id
SELECT a.[EventID], b.[name] AS [Even Name], a.[ColumnID], c.[name] AS [Column Name], d.[name] AS [Category]
FROM fn_trace_geteventinfo(<trace ID>) AS a --<<Put the trace ID you are interested in
    INNER JOIN sys.trace_events AS b ON a.EventID = b.Trace_Event_ID
    INNER JOIN sys.trace_columns AS c ON a.ColumnID = c.Trace_Column_ID
    INNER JOIN sys.trace_categories AS d ON b.Category_ID = d.Category_ID
ORDER BY a.[EventID], a.[ColumnID]

that shows all the columns of a trace, due to the DMV sys.trace_columns

I have put in place a server side trace with the following filters:
(this is part of the script)

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 53e0541a-7b74-4fce-bcac-9255e79a6372'
exec sp_trace_setfilter @TraceID, 34, 1, 6, N'udpProductTaxRateGet'
exec sp_trace_setfilter @TraceID, 34, 1, 6, N'udpProductBulletPointSelectByTier1NoteTypeCode'
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'US16AUTPProduct'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID

When I run this query now, I can see what columns are there, and exactly how the filters would work.

------------------------------------------------------------
-- what filters are set on @traceid
-- and how they are working
-- marcelo miorelli sql server 2005 - 8-8-2016
------------------------------------------------------------
declare @traceid int
select @traceid = 2
select a.[columnid]

     ,c.[type_name]
     ,c.[name] as [Column Name]

     ,[ @comparison_operator ] = CASE A.comparison_operator
                                     WHEN 0 THEN '= (Equal)'
                                     WHEN 1 THEN '<> (Not Equal)'
                                     WHEN 2 THEN '> (Greater Than)'
                                     WHEN 3 THEN '< (Less Than)'
                                     WHEN 4 THEN '>= (Greater Than Or Equal)'
                                     WHEN 5 THEN '<= (Less Than Or Equal)'
                                     WHEN 6 THEN 'LIKE'
                                     WHEN 7 THEN 'NOT LIKE'
                                     ELSE 'NOT SURE'
                               END
     ,a.value
     ,[ @logical_operator ]    = CASE A.LOGICAL_OPERATOR
                                     WHEN  1 THEN 'OR'
                                     ELSE         'AND'
                                 END
 from fn_trace_getfilterinfo( @traceid ) a
    INNER JOIN sys.trace_columns AS c ON a.ColumnID = c.Trace_Column_ID

  --[ @logical_operator = ] logical_operator

  --Specifies whether the AND (0) or OR (1) operator is applied.

  --logical_operator is int, with no default.


Problem with INSERT function I use this function INSERT (http://www.smallsql.de/doc/sql-functions/string/insert.html) to add a value in a data:

INSERT (log, POSITION(' edited' IN log), 1, 'NOW()')

I use also the NOW function to add the date in the data but it does not work. What is the problem?

When you write your INSERT query:

INSERT (log, POSITION(' edited' IN log), 1, 'NOW()')

In this way the DBMS thinks you try to put in your date field a string named "NOW()" instead of function to retrieve the current timestamp

You must remove the ' ' around NOW() function. In this way:

INSERT (log, POSITION(' edited' IN log), 1, NOW())


mysql copy and insert rows in same table with new primary key and sequence number I have multiple table with primary key, data columns and batch no.

I require procedure which dynamically creates statement to copy rows from table and insert into same table by changing auto increment value batch no.

Original Table

mysql query should copy records of batch 1 and paste them as batch 10 as below

I have tried to write query but it is coping all records, also it gets failed timeout for large no of records.

CREATE PROCEDURE `duplicateRows`(_schemaName text, _tableName text, _omitColumns text, var_run_seq_no int,var_old_run_seq_no int)
    BEGIN
SELECT IF(TRIM(_omitColumns) <> '', CONCAT('id', ',', TRIM(_omitColumns),'batch_no'), CONCAT('id',',batch_no')) INTO @omitColumns;

SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.columns
WHERE
table_schema = _schemaName
AND
table_name = _tableName
AND
FIND_IN_SET(COLUMN_NAME,@omitColumns) = 0
ORDER BY ORDINAL_POSITION INTO @columns;

SET @sql = CONCAT('INSERT INTO ', _tableName, '(', @columns, ',batch_no)',
  'SELECT ', @columns, ',', var_batch_no,
  ' FROM ', _schemaName, '.', _tableName);

-- select @sql;
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
END

Suppose we have the following table, containing test data as specified in your question.  Notice that the id column is "AUTO_INCREMENTed". (MySQL version 5.7)

create table batches (
  id int auto_increment primary key
, first_name varchar(32)
, last_name varchar(32)
, address varchar(32)
, batch_no integer
);

insert into batches (first_name, last_name, address, batch_no) values
 ('william','Faulkner','UK',1), ('suzy','nicholus','US',1)
,('ganesh','bora','india',1), ('mahadev','patil','india',1)
,('prasad','k','UK',2), ('sandeep','g','US',2)
,('prajakta','J','india',2), ('Nimish','G','india',2);

select * from batches;
+----+------------+-----------+---------+----------+
| id | first_name | last_name | address | batch_no |
+----+------------+-----------+---------+----------+
|  1 | william    | Faulkner  | UK      |        1 |
|  2 | suzy       | nicholus  | US      |        1 |
|  3 | ganesh     | bora      | india   |        1 |
|  4 | mahadev    | patil     | india   |        1 |
|  5 | prasad     | k         | UK      |        2 |
|  6 | sandeep    | g         | US      |        2 |
|  7 | prajakta   | J         | india   |        2 |
|  8 | Nimish     | G         | india   |        2 |
+----+------------+-----------+---------+----------+

The following procedure may help you to find a solution that fits your purpose.  Your initial idea - assembling a combination of an INSERT and a SELECT, and executing it - is still in there, just the method of finding all necessary columns is different.

delimiter //

create procedure duplicate_rows (
  schema_ text
, table_ text
, oldbatch_ int
, newbatch_ int)
begin

  declare colINSERT_ text default '' ; -- columns needed for INSERTing
  declare colSELECT_ text default '' ; -- columns needed for SELECTing
-- Find all columns for INSERT and SELECT, respectively. () contain "insert" cols.
-- Exclude the 'id' and 'batch_no' columns from the column list(s).
  select
      concat( '(', group_concat(column_name), ',batch_no)')
    , concat( group_concat(column_name), ',', newbatch_ )
    into colINSERT_, colSELECT_
    from information_schema.columns
   where table_schema = schema_
     and table_name = table_
     and column_name not in ('id', 'batch_no');

  set @sql = concat(
    'insert into '       , table_, colINSERT_
  , ' select '           , colSELECT_
  , ' from '             , table_
  , ' where batch_no = ' , oldbatch_
  );

  prepare stmt_ from @sql;
  execute stmt_;
  set @sql = '';

end//

delimiter ;

Test

mysql> call duplicate_rows('test', 'batches', 1, 20);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from batches;
+----+------------+-----------+---------+----------+
| id | first_name | last_name | address | batch_no |
+----+------------+-----------+---------+----------+
|  1 | william    | Faulkner  | UK      |        1 |
|  2 | suzy       | nicholus  | US      |        1 |
|  3 | ganesh     | bora      | india   |        1 |
|  4 | mahadev    | patil     | india   |        1 |
|  5 | prasad     | k         | UK      |        2 |
|  6 | sandeep    | g         | US      |        2 |
|  7 | prajakta   | J         | india   |        2 |
|  8 | Nimish     | G         | india   |        2 |
|  9 | william    | Faulkner  | UK      |       20 |
| 10 | suzy       | nicholus  | US      |       20 |
| 11 | ganesh     | bora      | india   |       20 |
| 12 | mahadev    | patil     | india   |       20 |
+----+------------+-----------+---------+----------+
12 rows in set (0.00 sec)

Notice: when using this procedure, you may encounter "gaps" in auto-incremented ID columns.  More information about this:

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

https://stackoverflow.com/questions/29315012/what-if-auto-increment-gaps-caused-by-mysql-insert-on-duplicate-key-update

https://stackoverflow.com/questions/1841104/how-to-fill-in-the-holes-in-auto-incremenet-fields


Postgresql: Does autovacuum impact query performance? Does it apply locks which impact the performance of other queries? Postgres recommends using the autovacuum to remove the dead tuples. But does running the autovacuum end up making other queries to the same table slow? Does it add locks (row or table level) which impact performance? Is there a significant load on CPU or does it consume lot of memory.

This will help us decide if we should turn on autovacuum for one of our tables which has very high amount of writes and deletes or if we should rather run a manual vacuum regularly during off peak hours.

It's memory usage is constrained by autovacuum_work_mem or maintenance_work_mem.  It's IO consumption is constrained by autovacuum_vacuum_cost_delay and friends, and the defaults for these lead to quite low IO demand.  The CPU usage is not directly constrained, but is indirectly constrained by the IO consumption throttle, and with the default setting will usually be close to negligible.

Autovacuum does take a lock on the table, but it is a weak lock which does not interfere with normal operations (SELECT, UPDATE, DELETE) but will interfere with things like adding indexes, or truncating the table.  Autovacuum will usually cancel itself and surrender the lock when it detects it is blocking something, but it won't do that if it is in "to prevent wraparound" mode.

Autovacuum is on by default.  You should not turn it off.  Running manual vacuums during off-peak hours is fine, but leave autovacuum on as a back-stop to catch whatever your manual vacuum misses.


Visual Studio Code Postgres SQL Connection I would like to use Visual Studio Code to preform my PostgreSQL queries, and general DB maintenance as an alternative to pgAdmin4. I have read the documentation included with the appropriate extention (PostgreSQL by MS). When I try to Create A New Profile I get the following prompt:

I am working from an Apple Mac so I attempted localhost at first. That did not work. I tried the ip address of the local computer with the same result. I attempted just giving the name of the DB, but that did not work either. Any suggestions as to what I can try next. I am terrible at spelling and rembering names so I would like some form of IDE support when I am working.

This is the error I get:
PostgreSQL: Failed to connect: could not translate host name "localhost\Local" to address: nodename nor servname provided, or not known

As per documentation here Npgsql has a Visual Studio extension (VSIX) which integrates PostgreSQL access into Visual Studio. It allows connecting to PostgreSQL from within Visual Studio's Server Explorer, create an Entity Framework 6 model from an existing database, etc. The extension can be installed directly from the Visual Studio Marketplace page.

The VSIX doesn't automatically add Npgsql to your GAC, App.config, machines.config or any other project or system-wide resource. It only allows accessing PostgreSQL from Visual Studio itself.

As here you van find out, How to Connect Visual Studio LightSwitch to PostgreSQL with dotConnect for PostgreSQL Data Provider.

As per postgresql documentation here  Could not translate host name localhost, service 5432 to address: nodename nor servname provided, or not known.

Usually this error is caused by broken /etc/hosts file. The problem could be a missing localhost entry, syntax errors or incorrect whitespace.

For reference, here is what this file should look like by default on macOS:

##
# Host Database
#
# localhost is used to configure the loopback interface
# when the system is booting.  Do not change this entry.
##
127.0.0.1   localhost
255.255.255.255 broadcasthost
::1             localhost

For further you ref here, here, here and here


Local login impersonation not working with a linked server I have created a linked server on my server using the following:

exec sp_addlinkedserver
    @server=N'MyOldDB',
    @srvproduct=N'',
    @provider = N'SQLNCLI',
    @datasrc=N'MYSERVER',
    @catalog=N'TheDefaultDatabase';

Now, despite the following description of how logon credentials are determined (from MSDN):

  A default mapping between all logins on the local server and remote
  logins on the linked server is automatically created by executing
  sp_addlinkedserver. The default mapping states that SQL Server uses
  the user credentials of the local login when connecting to the linked
  server on behalf of the login. This is equivalent to executing
  sp_addlinkedsrvlogin with @useself set to true for the linked server,
  without specifying a local user name.

I am getting the following error:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

When I try to do this:

select top 10 * from MyOldDB.TheDefaultDatabase.dbo.Batches

Can anyone point me in the right direction here?

The error you have is unrelated to logins as such.

This is caused when SQL Server tries to "pass through" the NT login token to the remote server. It doesn't have permission to pass the token through. The remote server looks for this because the local servers connects with Integrated Security.

You need "Security Account Delegation" to be configured for the local server.

As for the default mapping...

What MSDN says is that if you have "MyDomain\bob" locally then you have an "virtual" entry "stored" locally called "MyDomain\bob". There is no reconciliation of the 2 servers between the 2 servers. This is the same as running this:

EXEC sp_addlinkedsrvlogin 'MyLinkedServer', @useself = 'TRUE';

Note that "MyDomain\MyGroup" is not mapped: only discrete NT users and SQL logins. Not NT Groups.

This is all described in "Security for Linked Servers"

  The default mapping for a linked server configuration is to emulate the current security credentials of the login. This kind of mapping is known as self-mapping. When a linked server is added by using sp_addlinkedserver, a default self-mapping is added for all local logins. If security account delegation is available and the linked server supports Windows
  Authentication, self-mapping for the Windows authenticated logins is supported.

  ...

  If security account delegation is not available on the client or sending server, or the linked server/provider does not recognize Windows Authentication Mode, self-mapping will not work for logins that use Windows Authentication.


Does CPU utilization affect the cost of foreign NUMA access? Scenario
Let's assume I have a SQL Server with 4 sockets with each 1 NUMA node. Each socket has 4 physical cores. There is 512 GB of memory total so each NUMA node has 128 GB of RAM.
A key table is loaded into the first NUMA node.
Question
Let's assume we have a lot of traffic reading from that table. If all physical cores of the socket that owns the NUMA node have 100 percent CPU utilization, does that negatively influence the cost of non-local NUMA access coming from other sockets? Or on the other hand is the cost of non-local NUMA access is irrespective of how busy that socket is?
I hope my question makes sense. Please let me know if it doesn't I will try to clarify.
Background
We had a database issue in our production server last week and some of our business processed appeared more impacted than others. We had queries with few logical reads taking more than 1 minute. We looked at overall CPU utilization which was around 60 percent. We did not look at socket specific CPU metrics. I/O metrics were average.

A hefty question :-)
I'll outline some of the factors involved.  In any given context, these factors and others can vary and produce an interesting result.

Sorry I wasn't able to make this much shorter...

Accumuated CPU ms vs logical IO
SQL Server logical memory node alignment with physical NUMA nodes
Spinlock contention in query workspace memory allocation
Task assignment to schedulers
Relevant data placement in the buffer pool
Physical memory placement

Accumuated CPU ms vs logical IO

I use graphs of logical IO (or in perfmon terminology "buffer pool page lookups") against CPU utilization very often, in order to gauge cpu efficiency of workloads and look for spinlock prone cases.

But SQL Server accumulates CPU time with lots of activity other than page lookups and spinlocks:

Plans are compiled and re-compiled.
CLR code is executed.
Functions are performed.

A lot of other activities will chew up significant cpu time without being reflected in the page lookups.

In the workloads I observe, chief among these "non logical IO intensive but CPU-gobbling" activities is sorting/hashing activity.

It stands to reason: consider a contrived example of two queries against a hashtable with no nonclustered indexes.  The two queries have identical resultsets, but one of the resultsets is completely unordered and the second resultset is ordered by more than one of the selected columns.  The second query would be expected to consume more CPU time, even though it would reference the same number of pages in the buffer pool.

More about workspace memory, and how much of granted workspace has been used, in these posts:

http://sql-sasquatch.blogspot.com/2015/08/sql-server-grantedreservedstolen_4.html
http://sql-sasquatch.blogspot.com/2015/08/sql-server-workspace-memory-with-twist.html
http://sql-sasquatch.blogspot.com/2015/03/resource-governor-to-restrict-max-query.html

SQL Server logical memory node alignment with physical NUMA nodes

SQL Server (since incorporating its NUMA-aware strategies) by default creates a SQLOS memory node for each NUMA node on the server.  As memory allocations grow, each allocation is controlled by one of the SQLOS memory nodes.

Ideally, the SQLOS memory nodes are completely aligned with the physical NUMA nodes.  That is to say, each SQLOS memory node contains memory from a single NUMA node, with no other SQLOS memory node also containing memory from that same NUMA node.

However, that ideal situation is not always the case.

The following CSS SQL Server Engineers blog post (also included in Kin's response) details behavior which can lead to persisting cross-NUMA node memory allocations for the SQLOS memory nodes.  When this happens, the performance impact can be devastating.

http://blogs.msdn.com/b/psssql/archive/2012/12/13/how-it-works-sql-server-numa-local-foreign-and-away-memory-blocks.aspx

There have been a few fixes for the particularly painful case of persistent cross-NUMA node reference.  Probably others in addition to these two, as well:

FIX: Performance problems occur in NUMA environments during foreign page processing in SQL Server 2012 or SQL Server 2014
FIX: SQL Server performance issues in NUMA environments

Spinlock contention during allocation of workspace memory

This is where it starts to get fun.  I've already described that sort and hash work in workspace memory consumes CPU but is not reflected in the bpool lookup numbers.

Spinlock contention is another layer to this particular fun.  When memory is stolen from the buffer pool and allocated for use against a query memory grant, memory access is serialized with a spinlock. By default, this takes place with a resource partitioned at the NUMA node level.  So every query on the same NUMA node using workspace memory can potentially experience spinlock contention when stealing memory against grants.  Very important to note: this isn't "one time per query" contention risk, as it would be if the point of contention were at the time of the actual grant.  Rather, its when memory is stolen against the grant - so a query with a very large memory grant will have many opportunities for spinlock contention if it uses most of its grant.

Trace flag 8048 does a great job of relieving this contention by further partitioning the resource at the core level.

Microsoft says "consider trace flag 8048 if 8 or more cores per socket".  But... it's not really how many cores per socket (as long as there are multiple), but rather how many opportunities for contention in the work being done on a single NUMA node.

On the glued AMD processors (12 cores per socket, 2 NUMA nodes per socket) there were 6 cores per NUMA node.  I saw a system with 4 of those CPUs (so eight NUMA nodes, 6 cores each) that was jammed up in spinlock convoy until trace flag 8048 was enabled.

I've seen this spinlock contention drag down performance on VMs as small as 4 vCPUs.  Trace flag 8048 did what it was supposed to when enabled on those systems.

Considering that there are still some 4 core frequency optimized CPUs out there, with the right workload, they'd benefit from trace flag 8048 also.

CMEMTHREAD waits accompany the type of spinlock contention that trace flag 8048 relieves.  But a word of caution: CMEMTHREAD waits are a corroborating symptom, not root cause for this particular issue.  I've seen systems with high CMEMTHREAD "wait starts" where trace flag 8048 and/or 9024 were delayed in deployment because accumulated CMEMTHREAD wait time was fairly low.  With spinlocks, accumulated wait time is usually the wrong thing to look at. Rather, you want to look at wasted CPU time - represented primarily by the spins themselves, secondarily by the associated waits which represent potentially unnecessary context switches.

How It Works: CMemThread and Debugging Them

Task assignment to schedulers

On NUMA systems, connections are distributed to NUMA nodes (well - actually to the SQLOS scheduler groups associated with them) round-robin, assuming there aren't connection end points associated with particular NUMA nodes.  If a session executes a parallel query, there is a strong preference to use workers from a single NUMA node.  Hmmm... consider a 4 NUMA node server with a complex query broken into 4 paths, and default 0 MAXDOP.  Even if the query used only MAXDOP worker threads, there would be 4 worker threads for each logical CPU on the NUMA node.  But there are 4 paths in the complex plan - so each logical CPU on the NUMA node could have 16 workers on it - all for a single query!

This is why sometimes you'll see one NUMA node working hard while others are loafing.

There are a few other nuances to task assignment.  But the main takeaway is that CPU busy won't necessarily be evenly distributed across the NUMA nodes.
(Also good to realize that bpool page inserts (reads or first-page-writes) will go into the bpool in the SQLOS memory node associated with the scheduler the worker is on.  And stolen pages will preferentially come from the "local" SQLOS memory node, too.

I've found that bringing maxdop from 0 to no more than 8 is helpful.  Depending on the workload profile (primarily imo on the number of concurrent expected potentially long-running queries), going all the way to MAXDOP=2 may be warranted.

Adjusting the cost threshold for parallelism may also be helpful.  Systems I work on tend to be consumed with high cost queries and rarely encounter a plan below 50 or 100, so I've had more traction by adjusting maxdop (oten at workload group level) than adjusting cost threshold.

SQL Server PLE with 8 NUMA nodes

In this post, a combination of spinlock contention around workspace memory and uneven task distribution is discussed.  See - these things really do all weave together :-)
40 concurrent SQL Server parallel queries + (2 sockets * 10 cores per socket) = spinlock convoy

Relevant data placement in the bpool

This is the condition that I think is most intuitive when dealing with NUMA servers.  Its also, most typically, not extremely significant to workload performance.

What happens if the table is read into the bpool on NUMA node 3, and later a query on NUMA node 4 scans the table performing all bpool lookups across NUMA nodes?

Linchi Shea has a great post on this performance impact:

http://sqlblog.com/blogs/linchi_shea/archive/2012/01/30/performance-impact-the-cost-of-numa-remote-memory-access.aspx

Accessing memory across NUMA nodes incurs a small amount of additional memory latency.  I'm sure there are some workloads that need to eliminate that additional base memory latency for optimal performance - its not been an issue on the systems I work with.

But - cross-node access also brings another point of transfer which can potentially saturate.  If there is so much activity that memory bandwidth between NUMA nodes is saturated, memory latency between the nodes will increase.  The same work will require additional CPU cycles.

Again - I'm sure there are workloads such that memory bandwidth is a critical consideration.  For my systems, though, the other considerations I am listing have been more significant.

Physical memory placement

This one is rare but when it matters, it really matters.  On most servers, memory install is almost naturally going to balance across the NUMA nodes.  But ins some cases, special attention is needed for balancing the memory across the nodes.  Performance in some systems can be absolutely trashed if the memory was slotted in such a way that its not balanced.  This is set-it-and-forget-it, though.  Pretty rare to discover a problem like this after months of production service as opposed to after the first really busy day :-)

THE BIG FINISH!

Someone else made the point that poor plan choice, perhaps due to outdated stats, could result in the symptoms you've seen.  That hasn't been the case in my experience.  Poor plans can easily make a query take longer than expected - but usually because more logical IOs than necessary are being performed.  Or due to spill to tempdb.  Massive spill to tempdb should be evident when observing the server - and rather than high CPU one would expect measurable wait time for the spill-related disk writes.

Instead, if the situation you observed is NUMA-related, I'd expect it to be a combination of the factors enumerated above, mostly:

use of workspace memory(which won't show up in logical IO counts)
which may be cross-NUMA node due to persistent foreign memory condition(if this is the case, look for relevant fixes)
and which may incur spinlock contention within the NUMA node each time an allocation is made against a grant(fix with T8048)
and may be performed by workers on logical CPUs overloaded by other parallel query workers(adjust maxdop and/or cost threshold of parallelism as necessary)


is automated data movement via Excel considered ETL? I am wondering if informal methods of automatically extracting, transforming, and loaded data constitute "ETL." For example, I code Excel VBA to extract from databases onto a spreadsheet, transform the data into the desired format, and load by moving the Excel file to the correct folder. Would it be incorrect to showcase this as ETL for resume? Is there a term or lingo for this?

The process you describe is very extremely techinically ETL, though if I were reviewing qualifications with a candidate during an interview for a position that required what I usually think of as ETL experience and they described the process you mention as the "ETL" process they were familiar with, it's very likely that the interview would end politely and quickly.

It would be much better to describe this experience in terms of what it unambiguously is- VBA and advanced Excel skills are very valuable. You could call it "data preparation and processing using VBA and Excel," for example.

Calling these processes "ETL" experience is asking for trouble when an employer is looking for experience in one of the formal ETL tools or methodologies is unhappily suprised by the distinction.


Recommended plugin for SQL server 2008 Is there any recommended plugin for SQL server 2008 to use when you are working as database administration, Business Intelligence developer or Database developer?

SentryOne Plan Explorer (free) - analyze execution plans in a way you could never do with Management Studio alone (I also wrote a demo kit here)
SSMS Tools Pack (free) - lots of valuable features here - snippets, connection color coding that works, CRUD generation, and query execution history


Table contains multiple names for same company I have a table of monetary transactions that has a field for who the money was paid to or received from.  Transactions are recorded using info from paper invoices, paper reciepts, internet reciepts, or whatever other type of reciept is turned in or available.

Some (many?) companies use different names for themselves in different places.  The name that shows up on a credit card statement is likely  to be different (slightly or greatly) for the name they print on their POS paper receipts, etc..  The people entering the data can't always be relied on to remember all the naming variations on all the different companies.

So in our db, one company may have transaction records under several different names.

I'd like to have a form where I can enter known naming variations for companies and have the db keep track of multiple names that are really the same company, so later I can query and get far more trustworthy data returned.  Also, I need to be able to query using any of a company's "AKA's" and bring back all their transactions no matter which name was used when the transactions were entered.

The problem is that I'm not sure about the table structure to use. There could be 3 or more AKA's for one company.  How can I do this?

Thank you.

In this case I'd create a new table called CompanyAlias that will contain all possible aliases that you get for all companies. A possible structure would be:

CompanyAlias:

AliasId - int - Primary Key - some generated id
Alias - varchar(100) - the name of the alias
CompanyID - int - Foreign Key - references the original table of companies (in case you don't have one, I believe you should create one table with all unique companies and any other details that you might get)
any other column you'd like to have (description, creator..etc)

A query that will bring all the transactions for the main company of a given alias could look like:

declare @alias varchar(100);
set @alias = 'MicroS';

Select t.TransactionId, t.Alias, c.Company
from Transaction t
join CompanyAlias ca on ca.Alias = t.Alias
join Company c on c.CompanyId = ca.CompanyId
where t.Alias in
(
    Select ca.Alias
    from CompanyAlias ca
    where ca.CompanyId =
    (
        Select ca.CompanyId
        from CompanyAlias ca
        where ca.Alias = @alias
    )
);

Where the tables would be:

Transaction - contains monetary transactions (should contain company alias used by client)
CompanyAlias - defined earlier
Company - defined by you to have unique companies and their main details

PS: in case the SELECT statement is too bulky (written in notepad), I want to mention that I wanted to:

get the CompanyId of the searched alias (the one that you specify in the search form)
get all the aliases of the mentioned CompanyId
get all the transactions of the mentioned aliases


What is the correct SQL for using where ID in List and the items are with AND operator I need a SQL Statement to use IN operator within the items with "And" connected.

SELECT * from tbl_Users
WHERE id in [1,2,3]

is equivalent to:

SELECT * from tbl_Users
WHERE (id=1) or (id = 2) or (id = 3)

What I need is to generate using IN operator (or equivalent):

SELECT * from tbl_Users
WHERE (id=1) AND (id = 2) AND (id = 3)

Because I collect all the IDs (ex: [1,2,3,4,5]) in one string and search with it in the DB.

More Explanation:

ID is always a primary key

The case is I have a table which has all countries, and each country has several electrical standards in separate table.

Example:
Germany requires always "standard1" and "standard2"
USA required always "Standard1" and "standard3"
France required always "Standard2"

If the user in C# application search for a country which has "Standard2", the system should give him Germany and France.

BUT If the user wants countries with "Standard1" AND "standard2", the system should only give him Germany.  -> because of "AND"

My Query was

Select  * from [tables and joins]
where STANDARD_id IN [1,2]

[1,2] is a concatenated list which the user already choose from a list of standards.

I think what you are trying to do is relational division. Here is the best explanation I could find

https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

Honestly I do not fully understand this myself yet, but I believe this should work (using an intermediate table):

CREATE TABLE tblCountry (
    countryId int,
    countryName nvarchar(100)
)

INSERT INTO tblCountry
    SELECT 1, 'Germany' UNION
    SELECT 2, 'United States' UNION
    SELECT 3, 'France'

CREATE TABLE tblStandards (
    standardId int,
    standardName nvarchar(100)
)

INSERT INTO tblStandards
    SELECT 1, 'standard_1' UNION
    SELECT 2, 'standard_2' UNION
    SELECT 3, 'standard_3'

CREATE TABLE tblCountryStandards (
    countryId int,
    standardId int
)

INSERT INTO tblCountryStandards
    SELECT 1, 1 UNION
    SELECT 1, 2 UNION
    SELECT 2, 1 UNION
    SELECT 2, 3 UNION
    SELECT 3, 2

SELECT countryName
FROM tblCountry
    INNER JOIN tblCountryStandards ON tblCountry.countryId = tblCountryStandards.countryId
    INNER JOIN tblStandards ON tblStandards.standardId = tblCountryStandards.standardId
WHERE tblStandards.standardId IN (1, 2) -- unique standards selected by user
GROUP BY tblCountry.countryName
HAVING COUNT(DISTINCT tblStandards.standardId) = 2 -- number of standards selected by user above

This is based on your example, but I tested more combinations and it works if the commented values are changed.

There are many other ways of doing the division explained in the link provided above - you should use the one that suits your purposes best.


MySQL excessive tmp disk tables after Upgrade to 5.6 After upgrading two MySQL-Servers which run Master-Slave replication from Percona MySQL 5.5 to Percona MySQL 5.6 an excessive amount of temporary Tables gets created on the slave.

The Master seems to have a similar rate as before.

On the slave we went from an average of roughly 35 per second to about 1000 per second. (these are very rough averages).
We have about 3200 Queries per second on this server.

root:/var/lib/mysql# mysql -e "show global status like 'Created_tmp%_tables';" -e "show global status like 'Uptime%';"

+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 1485718 |
| Created_tmp_tables      | 1505629 |
+-------------------------+---------+
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Uptime                    | 1523  |
| Uptime_since_flush_status | 1523  |
+---------------------------+-------+

As you can see, nearly all of the temporary tables are created on disk (~98%).
Or rather a RAM-Disk for increased performance.

The configuration in my.cnf hasn't really changed.
At most some Options have been renamed to the full length name.

Tweaking tmp_table_size, max_heap_table_size, join_buffer_size and sort_buffer_size doesn't seem to make any difference.

So my question now is what could cause this behaviour?

I've read the Upgrade notes, searched this site and generally googled this issue, but didn't come up with anything that would help me.

If I've forgotten some much needed information, please tell me.

Any help is greatly appreciatet.

There are several reasons why that could be happening, and it is very difficult to point one specific cause -it could be because a change on the query optimizer, bad query statistics, parameters whose defaults changed from version to version, the changes on replication format, the data types you are handling, a change on the row format, etc.

The critical point here is to identify the differences on query execution: it would be nice if that would have been identified before the upgrade, but if you say that the master is not showing that behaviour, that may be enough. You need to discover which queries are creating those temporary tables. As you are using Percona Server 5.6, there are 2 main tools:

use the extended logging available on Percona and using pt-query-digest to identify and summary the offending queries, or
Use the performance_schema available since 5.6

Both tools can be used for identifying those bad queries. After that, you will be able to profile each query with the standard tools: EXPLAIN, the profiling metrics, the handler statistics, etc. In some cases, you may need to change the configuration, in other the queries, indexes or the structure itself of your database (for example, blobs usually cause problems because temporary tables using them cannot be on memory).

One thing that may actually be useful, independently of all of above is the replication format. If you are suffering from temporary table creation on the slave you are using STATEMENT or MIXED replication format (or your tables do not have primary keys). Row based replication, in some cases (when queries do not modify many records at the same time) may help with both query latency and avoiding slave drifting, so you should at least consider that.


InsertId in one table and insert the same id with concat of array at the end in other table I have two tables namely orders and suborders.
table 1 - Order table
Columns - Order Id and qty
Table 2 - suborders
Column - SubId
Whenever a new order is placed the same id has to be inserted in the both table but in the table 2 would I like to generate a the subid as  same id+ with iteration of total number of qty .
Eg
Table1
Exak123 , 59

I would like to get
Table 2
Exak123_1
Exak123_2
..
..
Exak123_59

You can do this in a single statement using a data modifying CTE
with new_t1 as (
  insert into table_1 (order_id, qty)
  values ('Exak123', 59)
  returning *
)
insert into table_2 (sub_id)
select concat(t1.order_id, '_', g.nr)
from new_t1 t1
  cross join generate_series(1, t1.qty) as g(nr);


Explain plan gives different results depending on schema I have a query that joins a multi million row table to one containing hundreds of thousands of rows.
Schema owner 'ABC' owns the smaller table and schema owner 'DEF' owns the larger table. They both have select, insert and delete privileges on each of the tables however 'DEF' also has SELECT CATALOG role.

There are no synonyms pointing either user to different versions of the same table.

When performing an EXPLAIN plan from user 'ABC' we see NESTED LOOP as the join mechanism and the query just runs and runs. the same query when EXPLAINed from the 'DEF' user (the one with SELECT CATALOG) shows a HASH join. It seems that the HASH join is the more efficient in this case but that is largely irrelevant for the question.

My question is why would the optimizer choose different joins for the same query depending on which user is running the query ?

In general, if 2 queries are identical, they will have the same sql_id, and as a result, the same execution plan (which may change over the time, but it shouldn't depend on which user executes the query).  (** It's oversimplified, because starting with 11g Oracle can generate multiple plans based on values of bind variables, presence of histograms ).

So first thing I'd check if they're really identical - run both of them, and check v$sql/v$sql_area .

Another thing is OPTIMIZER_MODE. When it's set to FIRST_ROWS(N), optimizer prefers NESTED LOOPS over HASH/MERGE JOINS ; when it's ALL_ROWS , HASH JOIN is more preferable for optimizer.  It's quite common that different users set different optimizer mode , FIRST_ROWS is great for OLTP applications; ALL_ROWS is more suitable for DSS.

You can also check whether any outlines are used.

If you're on Oracle 11 and higher, check IS_BIND_AWARE, IS_BIND_SENSITIVE values in v$sql.

Finally, always check actual execution plan with dbms_xplan.display_cursor, don't rely too much on the output of EXPLAIN without executing the query :
 - run query
 - find sql_id in v$sql
 - check plan SELECT * FROM table(dbms_xplan.display_cursor('[sql_id from previous step'],null));


LOAD DATA INFILE on a MyISAM table blocks user read-queries, can we get around this? I've got a large reference table, 100GB, in a MyISAM table, load it up from scratch goes well (4ish hours) using LOAD DATA INFILE.

But we have 1GB each day we want to update in a batch process using LOAD DATA INFILE. Unfortunately LOAD DATA INFILE blocks users from querying against that table.

Is there a way I can get around this?

For example:

Can I load the new data into another table and insert into from that table or something similar to allow loading the data and read queries against the primary table at the same time?
Can I do some kind of partitioning with new tables added daily, joined by a view? Would this be a bad idea with such a large table?

If your table is a MyISAM engine you can try to specify CONCURRENT in your statement.

Load data ref.

Concurrent insert ref.

  With LOAD DATA INFILE, if you specify CONCURRENT with a MyISAM table
  that satisfies the condition for concurrent inserts (that is, it
  contains no free blocks in the middle), other sessions can retrieve
  data from the table while LOAD DATA is executing. Use of the
  CONCURRENT option affects the performance of LOAD DATA a bit, even if
  no other session is using the table at the same time.

UPDATE FOR FREE BLOCK EXPLAIN

When you delete a row a free block can occur, if you have free blocks in the "middle" of your insert, for example if you replace deleted rows(index and/or primary key) with your load data, concurrent insert do not work, when free blocks are filled in future insert become concurrent again.


MySQL Create table syntax error CREATE TABLE plan
  (
     id                VARCHAR(30) NOT NULL,
     name              VARCHAR(20),
     amount            INT,
     interval          INT,
     currency          CHAR(3),
     object            VARCHAR(20),
     livemode          BOOLEAN,
     interval_count    INT,
     trial_period_days INT,
     created           TIMESTAMP,
     updated           TIMESTAMP,
     deleted           TIMESTAMP,
     PRIMARY KEY (id)
  );

Produces

'ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'interval          INT,
     currency          CHAR(3),
     object            ' at line 6'

Doesn't work. However if the 'l' at the end of 'interval' is removed. It works.

CREATE TABLE plan
  (
     id                VARCHAR(30) NOT NULL,
     name              VARCHAR(20),
     amount            INT,
     interva           INT,
     currency          CHAR(3),
     object            VARCHAR(20),
     livemode          BOOLEAN,
     interval_count    INT,
     trial_period_days INT,
     created           TIMESTAMP,
     updated           TIMESTAMP,
     deleted           TIMESTAMP,
     PRIMARY KEY (id)
  );

I don't understand if this is a problem with attempting to use 'interval' and 'interval_count'. What's going on here.

The word "interval" is a reserved word. You can still use it if you quote it correctly.

See the MySQL Manual for a list of the reserved words and how to use them.
In fact, "interval" is the first example in the link provided:

mysql> CREATE TABLE interval (begin INT, end INT);
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'interval (begin INT, end INT)'

mysql> CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)


How to solve multiple minimal covers for functional dependencies? I have the schema: a,b,c,d,e,g and the following functional dependencies:

b->c, dg->ce, bc->dg, e->a, g->bd

for which I want to find the minimal cover. It seems that more than one scenario is possible.

First I represent the dependencies by splitting the right-hand side:

b->c, dg->c, dg->e, bd->d, bc->g, e->a, g->b, g->d

For dg->c there's one extraneous attributed. Same goes for dg->e.

For bc->d the attribute c is extraneous as well as for bc->g.

For g->bd the attribute d is extraneous because if we have g->b then g+ = {gbcd} which contains d.

So we have 4 possible minimal covers:

b->c, g->c OR g->e, b->d OR b->g, g->b, e->a.

The correct answer is g-->c; g-->e; b-->g; e-->a; g-->b; g-->d according to this tool.

I cannot get the same answer from simplifying my minimal cover. What am I doing wrong?

Starting from the dependencies:

b->c, dg->ce, bc->dg, e->a, g->bd

and splitting the right hand side, one obtain:

b->c, dg->c, dg->e, bc->d, bc->g, e->a, g->b, g->d

(Note that you have written bd->d instead of bc->d).

Then you have correctly found that the attribute d is  extraneous both in dg->c and dg->e, while c is extraneous both in bc->d and in bc->g. So that the set of dependencies is now:

b->c, g->c, g->e, b->d, b->g, e->a, g->b, g->d

Then you say:

  For g->bd the attribute d is extraneous because if we have g->b then g+ = {gbcd} which contains d

but no dependency g->bd exists (it has already been split in the two dependencies g->d and g->b).

The last step is to find the superfluous dependencies in:

b->c, g->c, g->e, b->d, b->g, e->a, g->b, g->d

that are b->d and b->c. So at the end we have:

g->c, g->e, b->g, e->a, g->b, g->d

which is exactly the answer given by the tool.

Finally, consider that actually a set of functional dependencies could have more than one minimal cover: this depends on the order in which the attributes and the dependencies are considered for the elimination.


Why does "DELETE WHERE (VARCHAR)=0" execute successfully? DELETE FROM stats WHERE name=0

"name" is type VARCHAR(64). The above resulted in all records in the table being deleted, instead of just the 10~ that should have been affected. Only that many were actually equal to "0".

Why did this succeed in such cases as: "Mike"=0

Thankfully this was done on a personal development server and not production! I surely want to avoid this happening in the future.

When a string (VARCHAR) is compared to a number (0), any digits at the beginning of the string are converted to a number.  If there are no digits (eg, 'Mike'), the numeric value is 0.

WHERE 'Mike' = 0 -- succeeds
WHERE 'Mike' = '0' -- fails

Coding like that cannot be automatically prevented.  This is part of debugging your application.

Similarly, if you use an INT in a string context (eg, CONCAT()), the INT value will be converted to a string.


commit statement missing for a dml statement in binary log I have noticed 'commit' statement missing for a DML statement in binary log. Does it mean that this statement is rolled back in server? I am using mysql 5.5.40.

sample binlog

at 159527792

160229 23:25:22 server id 104  end_log_pos 159528127   Query   thread_id=45195 exec_time=0     error_code=0(in comments)

SET TIMESTAMP=1456809922/!/;

update table_name set col1=4 where col2=4
/!/;

at 159528275

160229 23:25:22 server id 104  end_log_pos 159528484   Query   thread_id=45195 exec_time=0     error_code=0(in comments)

SET TIMESTAMP=1456809922/!/;

insert into table_name values (1)
/!/;

at 159528632

160229 23:25:22 server id 104  end_log_pos 159528511   Xid = 22993533(in comments)

COMMIT/!/;

As per mysql documentation

Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.

http://dev.mysql.com/doc/refman/5.5/en/binary-log.html

please let me know whether my undersatnding is correct or not. if any statement getting rolled back, server write rollback to binlog?

mysql will write rollback to binary logs provided the transaction has mixture of engines (myisam and innodb tables). As the myisam can not be rollbacked.

I guess you'd like this piece of documentation...

  ... This ensures that the binary log reflects the exact data of InnoDB
  tables, and so, that the slave remains in synchrony with the master
  (not receiving a statement which has been rolled back).


Query data before a commit transaction My understanding is that, in a MS SQL Server Management Studio window, after doing a "begin transaction" and then making some changes like add data to a table , you can only query that table and those changes from the same window until you do a "commit transaction".

Is there any way to do a query from another source prior to doing the "commit transaction"?

Specific to my current goal and to add some context.  I do some SQL queries from Excel Power Query.  I'd really like to be able do do these queries prior to the "commit transaction" so that I can do some analysis and figure out if I should be doing a rollback instead of a commit.

Yes, it's possible if you change the transaction isolation level for the session (that's what you call "window" in SSMS) that queries modified data. Often this is not such a great idea, as you might get some unexpected results. Consider the side effects carefully. I have no idea if it's possible to change the transacion isolation level in the Excel Power Query.

For example, the following set of queries would insert some data and display the update correctly even without commit/rollback.

-- Session 1
begin tran tx_test;
-- Assume the Test table exists and insert is okay
insert dbo.Test(datadate, content) values (getdate(), 'transaction');
select * from Test; -- Shows the new data
-- After select, one would execute one of the following
-- commit;
-- rollback;

Meanwhile, the second session executes a select that doesn't seem to do anything:

-- Session 2
-- This waits for uncommitted transaction
-- and returns results after 1st session commits/rollbacks
select * from Test;

Create a third session and alter its isolation level:

-- Session 3
set transaction isolation level read uncommitted;
-- This reads the inserted data from the 1st session, even before commit
select * from Test;


transactional Replication of millions of updates I have standard transactional replication setup from 2005 to 4 2008 subscribers. One of the tables that is replicated is updated several times from an agent job resulting in 6 million updates statements every morning which is causing massive deadlock chains on the subscribers.

I think the resolution is to change this to replicate just a stored procedure call?  Do I simply have to add the new stored procedure into the publication & add the article at the subscriber ?

I have attempted this as still seem to be transmitting all of the update statements, I have added the article properties to replicate the call of the stored procedure as well but still seeing all rows in replication monitor

Panic over !

When adding the stored procedure to the Article, in the properties there is a setting called 'replicate'. By default this is schema only. When i changed this to Execution of the stored procedure' instead of 'Execution in a serialized transaction of the SP'. and restarted all the agents, it worked as one transaction this time


Importing CSV file using SQL Server Import and Export Wizard (SQL Server Express) So I am having a heck of a time importing a CSV file. The ultimate goal is to create a repeatable process to import the file. The only way I've been able to import the file is in a roundabout way using MS Access (Import into Access then export to SQL Server), which it happily and merrily does without issue.

But surely SQL Server (Express or otherwise) should be able to import it also.

The file is out in the wild here: http://ourairports.com/data/ and specifically: http://ourairports.com/data/airports.csv

It is a great data set to play with (real world data) and I have designs on normalizing it, bringing it into SSRS and what not, largely to learn and have something to 'show' a prospective employer.

But I'll be damned if the file will import without errors. Ideally the columns will import and convert appropriately, i.e. columns that are numeric should end up in the table defined as Int or whatever is most appropriate.

The most frequent error I am seeing is:

  Data conversion failed. The data conversion for column "wikipedia_link" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."

Ideally I'd like to import the file via T-SQL and Bulk Insert, but have similar issues.

Perhaps if someone can take a gander and see what's up, ideally I'd learn something here and not looking for just a quick answer, unless of course I'm just doing something stupid like forgetting a check box.

Thanks

The method of insert doesn't appear to be the problem, more the data itself

The error you are receiving is because one of the columns you have in your database table is too short (see your column 'wikipedia_link') see what length it is and if you can increase it, note it is quite possible to have more errors on other columns that are similar, judging by the data contained in that field the longest field is 128 characters, so I'd make the field nvarchar(130) as a minimum

If you load the csv in excel you can find out the max length of a column by using {=MAX(LEN(Q:Q))} (NOTE to get the {} array calculation after typing the query press ctrl + shift + enter)

Check all your fields are long enough and try again


How to increase max_locks_per_transaction in MacOSX I've been performing kind of intensive schema dropping and creating over a PostgreSQL server,

  ERROR: out of shared memory

  HINT: You might need to increase max_locks_per_transaction.

I need to increase max_locks_per_transaction but how can i increase it in MAC OSX

Check the postgres.conf file. The parameter is defined there. On windows I found it in the data directory so I guess it will be the same for Mac OSX.


Mongodb incremental oplog Dump not working I am not able to take an incremental oplog dump. I am not getting any error messages the connection happens fine and also there is a operation recorded(shows up in db.currentOp()), but still nothing happens, not sure why?

I have executed the same command on a smaller database where everything works fine no problems at all, but the same command on a larger database(talking about 10-11 billion records which is increasing day by day), does not work.

The command is mentioned below:

mongodump --host $MONGODB_HOST:$MONGODB_PORT --authenticationDatabase admin -u $MONGODB_USERNAME -p $MONGODB_PASSWORD -d local -c oplog.rs -o backup/oplogDump/$currentTime --query '{"ts":{$gt: Timestamp( 1452157469, 37)}}'

After executing this command, the entire secondory mongo machine gets stuck, i mean letrely i need to restart the machine to get mongod start running again.

Another change that i have done recently is, i have increased the nssize to 1 GB as per developers requirement, I am not sure since when this issue started or what is causing this issue, Any Help will be really appreiciated?

You are essentially attempting to dump the entire oplog and hence paging in all of it, not just the most recent data.  That is going to cause a lot of IO, especially when your oplog is too large to fit into memory.  On a smaller database it's not a problem because the oplog is relatively small.

The reason why it's dumping the full table is because I believe you are missing some zeroes at the end of your time:

> new Date(1452157469)
ISODate("1970-01-17T19:22:37.469Z")

I assume you wanted this:

> new Date(1452157469000)
ISODate("2016-01-07T09:04:29Z")

The speed this dump happens will depend on a number of factors, but mostly it will depend on how much data has been inserted into the oplog recently and how much of it is still in memory when you start the dump.


How is combined index implemented in SQL Server I understand how single indexed column works in SQL Server and how it is implemented using balanced trees. There are plenty of interesting videos on YouTube on this topic. However I don't understand how it works if the index is based on multiple columns. For example:

CREATE NONCLUSTERED INDEX idxItemsCatState
ON Items (Category,OfferState)
INCLUDE ([Id],[Ranking])

And how it can speedup query like

SELECT ID, Ranking FROM Items where Category = 1 AND OfferState < 3

It is still implemented as B-Tree? How it can evaluate the combination of values? What are the restrictions for such feature?

To store rows in a b-tree and perform a seek, all that is needed is an ordering in which the rows should be sorted. Just like you can sort on (Category), you can also sort on the tuple (Category, OfferState). In the latter case, rows are first sorted by Category and then any ties are broken by sorting by OfferState.

The resulting index will use the same b-tree structure, but the value for each entry in the b-tree will be a (Category, OfferState) tuple.

  And how it can speedup query like...

For your query, SQL Server can perform a seek in the following way:

Seek to the first row that matches Category = 1. This can be done using the same b-tree seek you are familiar with, with SQL Server only needing to use the Category portion of each (Category, OfferState) tuple.
Begin reading rows, and continue until a row with OfferState >= 3 is found

In this way, SQL Server will be able to seek directly to the beginning of the range of rows that are needed, read those rows, and stop at the end of the range of rows. Note that you can see how this seek works by looking at the Seek Predicate property of the Index Seek operator in your query plan.

More generally, a multi-column index across columns (a, b, c, d, ...) can support a seek on any leading subset of columns, such as (a) or (a, b, c), when you are matching for equality (using =).

If you are looking for a range (e.g., b < 3), SQL Server can no longer seek on any columns that fall later in the index. In such a case, it would have to perform a separate seek within each distinct value of b, which is not supported (except in a more specific case you probably don't need to worry about: across partitions of a partitioned table).