How to setup replication(Master/slave) in MySQL 5.5.20? I have two servers both running on MySQL 5.5.20 and I want to setup replication, so every update/insert on Master would reflect immediately on Slave machine.

I tried following this tutorial (I used instructions given on this website.) but it is not working. I found that after doing all this I couldn't ... (((Please update this part with your experiences)))

Could you please suggest me a tested and an efficient way to do this in MySQL 5.5.20?

Please create this script

Fron the Linux prompt, open up an editor like vi

vi /root/CreateMySQLSlave.sh

and add these lines to it

MYSQL_MASTER_HOST=10.1.2.20
MYSQL_SLAVE_HOST=10.1.2.30
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQL_MASTER_CONN="-h${MYSQL_MASTER_HOST} ${MYSQL_CONN}"
MYSQL_SLAVE_CONN="-h${MYSQL_SLAVE_HOST} ${MYSQL_CONN}"

MYSQLDUMP_OPTIONS="--master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --flush-privileges"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"

CREATE_REPL_USER="GRANT REPLICATION SLAVE ON *.* TO repluser@'%' IDENTIFIED BY 'replpass'"
mysql ${MYSQL_MASTER_CONN} -AN -e"${CREATE_REPL_USER}"

RELOAD_FILE=/root/MySQLData.sql
echo "STOP SLAVE;" > ${RELOAD_FILE}
echo "CHANGE MASTER TO master_host='${MYSQL_MASTER_HOST}'," >> ${RELOAD_FILE}
echo "master_port=3306," >> ${RELOAD_FILE}
echo "master_user='repluser'," >> ${RELOAD_FILE}
echo "master_password='replpass'," >> ${RELOAD_FILE}
echo "master_log_file='dummy-file'," >> ${RELOAD_FILE}
echo "master_log_pos=1;" >> ${RELOAD_FILE}
mysqldump ${MYSQL_MASTER_CONN} ${MYSQLDUMP_OPTIONS} >> ${RELOAD_FILE}
echo "START SLAVE;" >> ${RELOAD_FILE}

mysql ${MYSQL_SLAVE_CONN} -A < ${RELOAD_FILE}

Next, make the file executable and run it

chmod +x /root/CreateMySQLSlave.sh
/root/CreateMySQLSlave.sh

That's it !!!

Make sure you set the IP Address for MYSQL_MASTER_HOST and MYSQL_SLAVE_HOST

By the way, don't worry about the dummy-file in the master_log_file parameter for CHANGE MASTER TO. Line 29 of the dump file should have the correct setting there due to the --master-data=1. You can see it by doing this to the dump file

head -29 /root/MySQLData.sql | tail -1

Make sure binary logging is already enabled on the master. Here is how you can tell: run SHOW MASTER STATUS;. If you get nothing back, you need to enable it like this:

Add this to /etc/my.cnf

[mysqld]
log-bin=mysql-bin

then restart mysql

OK that takes care of binary logging for the Master.

You also need to make sure the Master and the Slave have different server IDs. Here is how you can tell:

Run SHOW VARIABLES LIKE 'server_id'; If the result comes up as 1, you must set it explicitly.

Goto the Master and add this to /etc/my.cnf

[mysqld]
server-id=1

Goto the Slave and add this to /etc/my.cnf

[mysqld]
server-id=2

The main thing here is to make sure server_id is explicitly set in my.cnf on both Master and Slave. You must also make sure the server_id values are different from all other servers involved in the replication topology.

If you are new to this, have a sysadmin join you to do this.


Is there a way to store int in ObjectID? I'm trying to migrate MySQL user ids to MongoDB. user ids are incremental values like 1,2,3,4...

Is there a way to store int in ObjectID?

If you don't specify _id field when inserting a document in MongoDB, the database internally generates ObjectID and stores the document. In your case, the _id column will be populated with automatically generated ObjectID.

If you want to convert 1, 2, 3, 4 as ObjectID, No you cannot. Because ObjectID should be in 12 bytes .

The _id Field


Sql Server Mirroring Failover and Failback I have the following mirroring setup for DR.

Principal (ServerA)
Mirror    (ServerB).

When we do a DR test we do manual failover from Principal (ServerA) to Mirror(ServerB), so now:

ServerB is Prinicpal
ServerA is Mirror

Applications do their testing against the new principal which is ServerB but don't want to take those changes or sync to ServerA (Mirror).

Is there any way to do this without breaking the mirror?

Finally it will go back to the original setup after the test, where Server A is Principal and Server B will be its mirror.

Applications do their testing against the new principal which is ServerB but don't want to take those changes or sync to ServerA (Mirror).
Is there any way to do this without breaking the mirror?

No this is not possible to prevent changes to ServerA. You have to resetup mirroring from original primary ServerA to ServerB
What you can do is :

Failover ServerA to ServerB.
Ask Users to test (making changes to serverB).
When the test is going on, in the background make full backup and transfer it to serverB. Make sure you use compression and instant file initialization. Depending on the database size, this will take some time.
Once the testing is done, you can point your application to serverA.  In the background, restore the database on serverB WITH NORECOVERY. This is initializing the mirroring from backup.
Take transaction log backup on primary (serverA) and then restore it on mirror database (serverB).


How to add indexing for a search based on multiple columns in MySQL table I am adding a search button with the suggestions displayed while we type on it similar to google and I need to search multiple columns in a single table. How to add indexing for that columns?

I have two tables in my application named as Employer and Employee. I want to add a search box so that the Employer can search employees of only his organisation based on their name or email or phone number. When the employers types on the search box I need to show the suggestions below. For Ex. when employer types Jo on the text box, I will show suggestions as

1. Jon, jon@gmail.com, 12345

2. Bobby, jon-bobby@gmail.com, 9876

3. .....

The Employee table has columns id, name, email, phone, employer_id fk references Employer table.

What I think the query might be is

SELECT *
FROM Employee
where employer_id = 1
and LOWER(name) like '%jo%'
or LOWER(email) like '%jo%'
or LOWER(phone) like '%jo%'

I want to know whether this is a valid query to use. I am not sure about its performance. Also I am not sure how to add index to this table in this particular use case.

I would really appreciate your valuable suggestions.

If you need to increase performance you can do the following:

Add FULLTEXT index to the columns name, email and phone

ALTER TABLE Employee ADD FULLTEXT (name), ADD FULLTEXT (email), ADD FULLTEXT (phone)

Dont use select * ( no caching )

select name, email, phone
, (
  10 * (MATCH (name) AGAINST ("Jon*" IN BOOLEAN MODE))
+  8 * (MATCH (email) AGAINST ("Jon*" IN BOOLEAN MODE))
+  2 * (MATCH (phone) AGAINST ("Jon*" IN BOOLEAN MODE))
) as points
from Employee HAVING points > 1
order by points DESC LIMIT 10

In this solution you get an result in a better order of your results.
If your keyword match with name, it is more helpfull to show than a match with phone.

You can change your my.cnf and add: ft_min_word_len = 3to index words up to 3 chars.


MySQL, would like to get both a field and a count inside GROUP_CONCAT I can get a list of statuses that a tracking ticket has had, as well as the id of those statuses, as follows:

SELECT
t.id, t.name, GROUP_CONCAT( CONCAT(s.id, ':', s.name) ORDER BY s.id) AS
all_statuses_history
FROM tracking t LEFT JOIN changelog c ON t.ID = c.tracking_id
LEFT JOIN statuses s ON c.changed_to = s.id

That will have duplicates (status can bounce around in the real world), so I change the GROUP_CONCAT with DISTINCT keyword:

GROUP_CONCAT(DISTINCT CONCAT(s.id, ':', s.name) ORDER BY s.id) AS .. (etc)

That's better, but what I'd REALLY like is the id, status name, and count of instances, looking something like this:

1:Requested(1), 2:Pending(3), 3:On Hold(2), 4:Completed(1)

You'd read that as "the ticket bounced around between pending and on hold a bit and was finally completed".

I can't put COUNT(*) or apparently GROUP BY inside of a GROUP_CONCAT function.  Is it possible to get all of this information inside of a GROUP_CONCAT?

As a schema:

SELECT name,
       GROUP_CONCAT(CONCAT(status_id, ':', status_name, '(', status_cnt, ')')) AS all
FROM ( SELECT name,
              status_id,
              status_name,
              COUNT(status_name) AS status_cnt
       FROM source_tableset
       GROUP BY name, -- first level GROUP BY and counting
                status_id,
                status_name ) AS subquery
GROUP BY name -- second level GROUP BY and group concatenation


How can I get the last items by date desc then by id desc? I have a employee course table which holds the current record for each of my employees who have some courses that they have taken. Then I have a history that keeps a static version of the save data with some more metadata. The histories can be inserted in any order. That is the importer can just put them in any order. With each history that is added there is a next due date which states when they have to take the course again. I need the live employee course table to be updated with the latest record from the history ordered by NextDueDate Desc, then by Id Desc. There are two properties(HasAttachments, UniqueId) that need to be copied from the history table. The employeeId and companycourseid act as a reference to the history table.

Here is the code I came up with. I think this will work but I need some second opinions.

WITH History AS (
    SELECT  Id, h.EmployeeId, CompanyCourseId, NextDueDate, HasAttachments,
        ROW_NUMBER() OVER (Partition BY EmployeeId,CompanyCourseId ORDER BY
        NextDueDate DESC, Id DESC) AS rownum
    FROM TrainingHistory h
),
Latest AS
(
    SELECT h.* FROM History h
    WHERE h.rownum = 1
)
 select ec.Id, l.HasAttachments
  INTO TempEmployeeCourse from Latest l INNER JOIN
      EmployeeCourse ec on l.CompanyCourseId = ec.CompanyCourseId AND
      l.EmployeeId = ec.EmployeeId

I just put the results I need for updating into a temp table so I can easily update the required UniqueId and HasAttachments columns into the employee course table.

update EmployeeCourse
set HasAttachments = ISNULL(
        (select HasAttachments from dbo.TempEmployeeCourse where Id
         EmployeeCourse.Id), 0),
UniqueId = ISNULL(
        (select UniqueId from dbo.TempEmployeeCourse where Id =
         EmployeeCourse.Id), newid())
GO

DROP TABLE [dbo].TempEmployeeCourse
GO

This seems like the most elegant solution. Is there anything I might be missing here?

EmployeeCourse

CREATE TABLE [dbo].[EmployeeCourse](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NOT NULL,
    [CompanyCourseId] [int] NOT NULL,
    [HasAttachments] [bit] NOT NULL,
    [UniqueId] [uniqueidentifier] NULL,

 CONSTRAINT [PK_Employee_Courses] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

TrainingHistory

CREATE TABLE [dbo].[TrainingHistory](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NULL,
    [NextDueDate] [date] NULL,
    [CompanyCourseId] [int] NULL,
    [UniqueId] [uniqueidentifier] NULL,
    [HasAttachments]  bit NOT NULL DEFAULT 0,
 CONSTRAINT [Training_History$PrimaryKey] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Your query seem ok.

you should thoroughly Test the Resultset in all server.

Only concern is that when it is ISNULL then Why you will update with 0.Is it really necessary ?

Also second CTE is not required.

WITH History AS (
    SELECT  Id, h.EmployeeId, CompanyCourseId, NextDueDate, HasAttachments,
        ROW_NUMBER() OVER (Partition BY EmployeeId,CompanyCourseId ORDER BY
        NextDueDate DESC, Id DESC) AS rownum
    FROM TrainingHistory h
)

 select ec.Id, l.HasAttachments
  INTO TempEmployeeCourse from History l INNER JOIN
      EmployeeCourse ec on l.CompanyCourseId = ec.CompanyCourseId AND
      l.EmployeeId = ec.EmployeeId
where WHERE l.rownum = 1

update EC
set HasAttachments =TEC.HasAttachments
,UniqueId =TEC.UniqueId
from EmployeeCourse EC
inner join dbo.TempEmployeeCourse TEC
on TEC.Id =EC.EmployeeCourse.Id


SQL Server 2008 R2 - Service shutdown - what with running stored procedures? I would like understand below behavior of SQL Server:

SQL Version:

Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Let's assume that we have stored procedure as the following (some code omitted for brevity):

CREATE PROCEDURE Test
AS
BEGIN TRY
    INSERT INTO test_log ('Stage 1. PROC started')

    ...

    EXEC (@ScheduledProcedure) -- Execution of this takes 8-10 mins
    INSERT INTO test_log ('Stage 2. Scheduled Procedure completed')
END TRY
BEGIN CATCH

    ...

END CATCH;

INSERT INTO test_log ('Stage 3. PROC Completed')

I have done some testing to check what happens with my procedure when I shut SQL Server down when my procedure is still running and here are the results:

When I go to the services.msc and stop the SQL Server, the service will be in 'Stopping' state until @ScheduledProcedure is completed (8-10 mins).
When I shut the OS down it seems that SQL Service cancels stored procedure. I think that it is the case, because Stage 2 and 3 messages are not available in test_log table.

Can someone try to explain this SQL Server behavior ?

When I go to the services.msc and stop the SQL Server, the service
  will be in 'Stopping' state until @ScheduledProcedure is completed
  (8-10 mins).

What you are seeing is the default behavior when you shut down SQL Server. If you do not want want to wait you can use WITH NOWAIT switch but when SQL server starts recovery time will be longer as transactions has to roll forward and roll back.

Details here.

Unless the WITHNOWAIT option is used, SHUTDOWN shuts down SQL Server by:

Disabling logins (except for members of the sysadmin and serveradmin
fixed server roles).
Waiting for currently running Transact-SQL statements or stored
procedures to finish. To display a list of all active processes and
locks, run sp_who and sp_lock, respectively.
Inserting a checkpoint in every database.
Using the SHUTDOWN statement minimizes the amount of automatic
recovery work needed when members of the sysadmin fixed server role
restart SQL Server.

  When I shut the OS down it seems that SQL Service cancels stored
  procedure. I think that it is the case, because Stage 2 and 3 messages
  are not available in test_log table.

In this case SQL Server has no control as operating system issue a shut down process command and that is why you are seeing immediate shutdown.Transactions that were completed and written to the transaction log but not yet applied to the database, will be applied to the database. Open, unfinished, transactions are rolled back, again to ensure the database is in a consistent state.  Details about roll forward and roll back here.

Different ways to shut down SQL Server.


on duplicate key update I'm trying to execute the following statement in MySQL:

  INSERT IGNORE INTO shop_item_details (shop_id,product_id,product_name,Product_model,Product_category,Product_details,price)
VALUES (1, NULL, 'camera', 'sony','', 'hd', '5000')
ON DUPLICATE KEY
UPDATE (shop_id='1',product_id='',product_name='camera', Product_model='sony',Product_category='', Product_details='hd',price='5000')

But it produces an error:

  #1064 - 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 '(shop_id='1',product_id='',product_name='camera', Product_model='sony',Product_c' at line 1

What is wrong with my query?

Community Wiki answer originally based on notes by ypercubeᵀᴹ and Rick James suggested in the comments on the question.

The immediate issue is the parentheses around the UPDATE set list: they should not be there according to the syntax. That is what the error message is about.

Once you have fixed that one, there are other issues with your INSERT statement.

The IGNORE keyword just does not make sense when used together with ON DUPLICATE KEY. You either perform an update when a duplicate key is encountered, or you ignore the duplicate key entry completely, and it looks as though you want the former – therefore, lose the IGNORE keyword.
Setting product_id to '' in the ON DUPLICATE KEY clause is not consistent with setting it to NULL in the VALUES clause: an empty string ('') is not the same as a null. Judging from the column name, you probably want to have NULL specified in both places.
Similarly, seeting id to '1' in the ON DUPLICATE KEY clause is not consistent with setting it to 1 in the VALUES clause: the string ('1') is not the same as the number (1). This will probably not be an issue as the string will be converted to a number but it doesn't hurt to be explicit and very careful with the types of your column and values you pass to them.
While this is not exactly a problem, you don't have to specify the same values twice (in VALUES and in ON DUPLICATE KEY UPDATE clauses). You can specify them once and use them in two places, using the VALUES() function, like this:

INSERT INTO shop_item_details
  (shop_id, product_id, product_name, Product_model,
   Product_category, Product_details, price)
VALUES
  (1, NULL, 'camera', 'sony', '', 'hd', '5000')
ON DUPLICATE KEY UPDATE
  shop_id          = VALUES(shop_id),
  product_id       = VALUES(product_id),
  product_name     = VALUES(product_name),
  Product_model    = VALUES(Product_model),
  Product_category = VALUES(Product_category),
  Product_details  = VALUES(Product_details),
  price            = VALUES(price) ;

Please note that the above is just an example. Since you are updating the same set of columns as those you are inserting into, setting each of them using the column = VALUES(column) pattern is slightly redundant. The columns that define the unique (or primary) key being violated (you have not specified which columns they are in your case - perhaps shop_id and product_id? Or shop_id and product_name and product_model?) can be safely removed from the ON DUPLICATE KEY UPDATE clause.


How would I improve my "algorithm" for figuring out my average spending on food per month? I have a database table keeping track of every grocery delivery. It's stored as timestamps and sums (EUR).
Currently, I do this:
SELECT (SUM("amount in EUR") / 3)
FROM personal."food purchases"
WHERE "purchase time" >= now() - INTERVAL '3 months';

Now, let's say that the current day is 1st of September 2020. This "algo" will count together all the deliveries from exactly three months back from the current moment, which I guess means from the first day of June, and then simply slash this price into 3 to symbolize "three months".
Sometimes, depending on the day, this will give a reasonable value. Other times, and I cannot fully describe/understand why, this gives either a too high or too low value. I suspect that the reason for this is that my food purchases/deliveries don't always happen on the exact same day, and vary quite a bit in price as well. Thus, the resulting sum, even though I do an interval of three months and then slash with three, and thus "should" give the average food cost "recently", is oftentimes misleading.
I can't tell for sure, but I think that it sometimes includes one purchase "too many", and sometimes "one too few". It might even be worse than that.
Can you help me improve my formula to be more "fair"? I want to know the average amount I pay for food each month, and I'm not convinced that I'm getting that value with my current query.
I think I need to drop the part which goes three months back from the current time, and instead somehow calculates the... last three... months... um... Frankly, I don't know what I need to do. I just don't think that the current method is correct.
I'd appreciate if your suggestion/solution stays as close as possible to my original query. I want to understand what is happening. I actually suspect that this is a very common issue and probably built into PG and all other database softwares.

I would first calculate the sum per month, then calculate the average from that:
select avg(amount) as avg_amount_per_month
from (
  SELECT date_trunc('month', "purchase time"), SUM("amount in EUR") as amount
  FROM personal."food purchases"
  WHERE "purchase time" >= now() - INTERVAL '3 months'
  group by date_trunc('month', "purchase time")
) t
;

The inner query will return the amount for each month of the chose interval (yielding three rows), and the outer query will then calculate the average from that.


Reporting Service 2014 Service Pack 1 Failing to work with error - wrong database version Tonight we upgraded our SQL Server 2012 (SP2+CU6) cluster with SQL Server 2014 Service Pack 1.

On that cluster nodes, we have Native mode Reporting Services scale-out configuration.

After in-place upgrade to SQL Server 2014 SP1, we got error from Reporting Services web page:

  The version of the report server database is either in a format that
  is not valid, or it cannot be read. The found version is '163'. The
  expected version is '162'.

So there go question - why we have this error when we have upgraded to the most recent version of SQL Server 2014 and Service Pack 1?

There are two workarounds:

Delete one record from table ServerUpgradeHistory

USE [name_of_you_SSRS_database]
DELETE FROM ServerUpgradeHistory WHERE ServerVersion = 163

Install Cumulative Update 1 for SQL Server 2014 Service Pack 1. Before updating your SQL Server 2014 SP1 with Cumulative Update 1 - test it hard in your lab.

BE AWARE - Pay attention to backup encryption keys before you upgrade SSRS 2012 to
2014. After you apply CU1 all your encrypted content will be inaccessible.


Alternative to triggers on system views for user auditing Even writing the title I'm cringing at the idea I've tried this (but hey, I've learned something in the process).

I've got a problem with developers running migration scripts on the database and in the process overwriting stored procedures already existing in the database (DEV, thank a deity).

However, there's no way of finding out who made these changes because:

they don't admit it
SQL Server does not populate the principal_id column in the sys.objects  system view, because everyone is connecting as dbo (more info here).

So, I've tried writing a trigger on sys.objects and that didn't work (I don't know why I ever expected it to) as I'm getting a error:

  The object 'DEV_DB.sys.objects' does not exist or
  is invalid for this operation.

I've also tried creating a user for each developer so that when they do their automatic migrations they will use the user I've made them and hopefully all changes made to sys.objects by that developer will be logged as the user they logged in with.

But that didn't work either, since principal_id still wasn't populated (what I have done was create a new login / user for each Developer & DB and give the login specific server roles : public, db_datawriter, db_datareader, db_ddladmin).

Any suggestions on what else I could try as an alternative?

You can always tap up the default trace to catch any changes to objects, including stored procs.

Give the following script a whirl, it's easy enough to find out who changed what and when.  It's helped me catch out more than one sly developer ;)

SELECT  t.DatabaseName, t.ObjectID,t.NTUserName,t.HostName,t.StartTime, te.name
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL13.SQL2016\MSSQL\Log\log.trc', DEFAULT) t
JOIN sys.trace_events te  on t.EventClass = te.trace_event_id
ORDER BY t.StartTime DESC;

You'll need to change the path to wherever your SQL trace file is, to find out where it is run:

select path
from sys.traces
where id = 1

One tip, remove the _number from the file name to read all available trace files e.g. if the file name is log_16.trc, change it to log.trc.


Query Multiple tables in MS Access I have three tables:

Table City has Two columns:

City_ID, CityName <City_ID is Primary Key>

Table Accommodation has Two Columns:

Accommodation_ID, CategoryType <Accommodaton_ID is Primary Key>

Table Instance has four columns:

Instance_ID, Description, City_ID, Accommodation_ID <Instance_ID is Primary Key; City_ID is Foreign Key, Accommodation_ID is Foreign Key>

I am trying to run an SQL query to "Get all Description as long as "CityName=Melbourne" and "CategoryType=Hotel".

My query returns multiple rows of same values:
See my sql code below:

SELECT C.Description, A.CityName, B.CategoryType
FROM Instance AS C, City as A, Accommodation as B
where A.City_ID = (select City_ID from City where CityName = 'Melbourne') AND A.City_ID = C.City_ID AND B.CategoryType = 'Hotel';

I am using MS Access
Please what am I doing wrongly..
Thank you

Your query is missing AND C.Accommodation_ID = B.Accommodation_ID

If you are just getting started with SQL then you might consider building simple queries like this in the Access query designer and then switch to SQL view to see what the query designer has generated. In your case we have

and Access generates the following SQL (which I've indented for clarity)

SELECT
    Instance.Description,
    City.CityName,
    Accommodation.CategoryType
FROM
    (
        City
        INNER JOIN
        Instance
            ON City.City_ID = Instance.City_ID
    )
    INNER JOIN
    Accommodation
        ON Instance.Accommodation_ID = Accommodation.Accommodation_ID
WHERE (((City.CityName)="Melbourne") AND ((Accommodation.CategoryType)="Hotel"));

Note that the query uses INNER JOIN syntax, which is generally preferred over the older method of putting the join conditions in the WHERE clause.


Check if partition elimination works I implemented partitions on one of the tables our DEV/TEST environment, and modified some of the procedures to use the partition key when accessing this table.

How can I verify that partition elimination is actually occurring in the DB?

I tried checking it with this query.
But I saw only that "partitioned = true" is mentioned, but no how many partitions were accessed.

SELECT
  eqs.execution_count,
  CAST((1.)*eqs.total_worker_time/eqs.execution_count AS NUMERIC(10,1)) AS avg_worker_time,
  eqs.last_worker_time,
  CAST((1.)*eqs.total_logical_reads/eqs.execution_count AS NUMERIC(10,1)) AS avg_logical_reads,
  eqs.last_logical_reads,
    (SELECT TOP 1 SUBSTRING(est.text,statement_start_offset / 2+1 ,
    ((CASE WHEN statement_end_offset = -1
      THEN (LEN(CONVERT(nvarchar(max),est.text)) * 2)
      ELSE statement_end_offset END)
      - statement_start_offset) / 2+1))
    AS sql_statement,
  qp.query_plan
FROM sys.dm_exec_query_stats AS eqs
CROSS APPLY sys.dm_exec_sql_text (eqs.sql_handle) AS est
JOIN sys.dm_exec_cached_plans cp on
  eqs.plan_handle=cp.plan_handle
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) AS qp
WHERE est.text like '%<TABLE_NAME>%'
OPTION (RECOMPILE);

Do you have any other query that can help me identify if partition elimination occurs in my DB?

Thanks !

Roni.

Use Extended Events to capture actual execution plans, via the query_post_execution_showplan Event Library.  Be aware of the following warning, found in the New Session Wizard, when creating the session:

query_post_execution_showplan
Occurs after a SQL statement is executed. This event returns an XML representation of the actual query plan. Using this event can have a significant performance overhead so it should only be used when troubleshooting or monitoring specific problems for brief periods of time.

The following T-SQL will create the required Extended Events session.  Do not run this on a production SQL Server unless you want to slow it to a crawl.
CREATE EVENT SESSION [CapturePlans] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(2048))
WITH (STARTUP_STATE=OFF)
GO

ALTER EVENT SESSION [CapturePlans] ON SERVER STATE = START;


MySQL flush logs, unknown error I gave been trying to solve this for few days now, and cant seem to figure this out.

I have been trying to set log rotation for mysql slow using linux logrotate feature, and my script seems to get stuck on mysqladmin flush-logs.
So I logged into mysql and just entered FLUSH LOGS;. Not surprisingly I get an error:

mysql> flush logs;
ERROR 1105 (HY000): Unknown error

Now I have been going around asking for ideas, but none seem to work, its not permissions, not owners, not SELinux

I know that there used to be some bug, like in 2005 or 2008 ... but its 2012, I assume its fixed.

So if anyone has any ideas, would be much appreciated.

If you are running MySQL on Debian 6 squeeze stable with vMySQL-5.5 then there is a known issue mentioned about dotdeb packaging:

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


Preventing users from accessing DB while indexes are being rebuilt I need to rebuild the indexes in two very large (almost 750 table) database but want to prevent users from accessing the database while this happens. Is there a state I can put the database in that prevents the users from accessing it but still allows me to rebuild the indexes? For reference I plan on the script provided at http://instadba.com/quick-script-to-defragment-your-sql-server-indexes/ to do the rebuild.

USE master;
GO
ALTER DATABASE YourDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
USE YourDatabase;
--Run index maint scripts...
GO
ALTER DATABASE YourDatabase
SET MULTI_USER;
GO


Possible to run two DBCC INDEXDEFRAG commands simultaneously, each on a different table? I am currently running a script which performs a DBCC INDEXDEFRAG on every table in a SQL Server 2005 database, one table at a time.  Using DBCC DBREINDEX instead of INDEXDEFRAG is not an option, due to space constraints and uptime requirements.

I have noticed that it takes a long time for certain tables to be defragmented.  For instance if I examine the "sys.dm_exec_requests" dynamic management view, I can see that the following INDEXDEFRAG is currently churning away on the clustered index of a table that has a table_id of 829610394:

DBCC INDEXDEFRAG (0, 829610394,         1)

I know that it will be a long time before the defragmentation process completes.  Leaving aside the fact that the script currently running will eventually defragment all the tables, is there any harm in me manually running another DBCC INDEXDEFRAG on the clustered index of another table while the current command executes?  Will both tables actually be defragmented at the same time if I do this?

Yes, you can do it for multiple tables. You can't do it for multiple indexes on the same table - I invented a new index lock subresource to prevent it. Same behavior in ALTER INDEX ... REORGANIZE that I replaced it with in 2005.

Thanks


Failover Cluster Role Properties dialog Can someone please explain diff between different 'Priority' options viz. High, Medium, Low and No Auto Start ?

This is same as 'Startup Priority' introduced in Windows Server 2012. You can set it from two places if you are using GUI. One what you have shown in the question and other one is below.  If you right click on any role name and click Change startup priority you will get to same options.

You can also use PowerShell to change the same setting. Example here.

Medium is the default priority. In the case of node failure, this priority setting determines the order in which roles are failed over and started on another node. A higher priority role both fails over and starts before the role of the next highest priority. If you assign the No Auto Start priority to a role, the role is failed over after the other roles but is not started on the new node. The purpose of startup priority is to ensure that the most critical roles have prioritized access to resources when they fail over to another node.

Ref:

Assigning role startup priorities


SQL Server Mirroring kicks in before cluster has time to fail over I have two clusters each with two nodes configured in active/passive; the two clusters take part in a high-availability mirroring session using a separate server as the witness.

When a node fails in the primary cluster, the mirroring failover seems to kick in before the secondary node of the primary cluster has had time to come online.

Is there anyway of telling the witness server to let the passive node attempt to come online before initiating the failover to the secondary cluster?

Database mirroring questions from TechEd: failovers and partner timeouts

  ... when the cluster is failing over and the principal instance is
  unavailable for 60-90 seconds, the other instances in the mirroring
  session will time-out after ten seconds and initiate a mirroring
  failover... The solution to the problem is to change the partner time-out value - a facility that isn't well known. To change the partner time-out value for a mirroring session, use the following code:

ALTER DATABASE mydatabase SET PARTNER TIMEOUT 90;


SQL Server DB table data size query result not increasing if data count increasing I have a query which will show the Table Name, Number of Rows and Size of the record of a Database tables in SQL Server. when I executed the query I got a result showing

TableName            NumberOfRows   SizeinKB
TBL_PROCESS_AUDIT2   1              16

But When I inserted more record into the same table using the same insert query and executed the query, it showing more number of rows (50) but the same size (16kb).

TableName            NumberOfRows   SizeinKB
TBL_PROCESS_AUDIT2   50             16

Actually when the number of rows of a table is increasing, it's size also should increase. So for 1 record the size is 16KB so for 50 records, the size should be 800KB. When I inserted bulk record like some 2000 records, the size is varying.

What would be the logic or problem in my query? I want the actual size of the records of a DB table.

My Query:

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),
       reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),
       unused VARCHAR(18))

EXEC       sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

SELECT     TableName,CONVERT(bigint,rows) AS NumberOfRows,
           CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB
FROM       #RowCountsAndSizes
ORDER BY   NumberOfRows DESC,SizeinKB DESC,TableName

DROP TABLE #RowCountsAndSizes

SQL Server stores data on 8kb pages. You can't have a table that's smaller than than two pages. So when you add one row to the table, you have a minimum space consumption of 2 * 8kb = 16kb. But those pages aren't full, which means adding 15 more rows doesn't increase table size by (15 * initial size), it just means that the data page holding the initial row had enough room for more data. If you add 1000 more rows, I bet your size will increase, but this will vary depending on what's in a row and how many rows can fit on an 8kb page.

I don't know that it matters, really, how much data is in a table if you want to only look at the space on a page that's used, and ignore the space on a page that isn't used. You're taking 8 kb on disk and 8 kb in memory whether the page has 10 bytes of data or 7,997 bytes. So you will see that most space calculation scripts just take (number of pages) * 8,192 because the unused space on a page is very rarely relevant.


How do I prevent a massive SELECT from blocking other statements? Our SQL Azure database contains a massive SELECT statement that runs something like once per day. The heavy SELECT statement contains no locking hints. Recently we observed some stalls in production and here's what sys.dm_exec_requests was showing for that period of time... The longest running query was that heavy SELECT which had PAGEIOLATCH_SH wait type. Next were other queries - most often INSERT statements which had PAGEIOLATCH_EX wait type, all running for dozens of seconds instead of completing promptly. So basically that heavy SELECT alone disrupts other queries.

How do I resolve this? I'm okay with heavy SELECT running slowly but other queries should not be disrupted.

Your main options are:

Tune the SELECT query so it uses fewer I/O resources.
Run the query at a quiet time.
Run the query on a separate copy of the database (e.g. a readable secondary).
Run the query in an I/O-limited resource pool as described here*.
Move to a size/tier with higher I/O capacity.

Side note: This answer does not mention using isolation levels like RCSI or SI. Those apply to lock blocking, and the question contains no reference to that: PAGEIOLATCH_XX waits are associated with waits on physical I/O.

*This feature may not be available in Azure SQL Database.


Insert data from a source table into a destination table with more columns? I want to use INSERT INTO SELECT * to insert data into a dest table from a source table, but I have some extra columns added to destination table. These columns are distributed around the table like 2 are at top and 2 are at bottom.

Also for insertion I can insert some default value like null to these columns.

SELECT * selects col1, col2, col3, as defined in table2, and if the selection doesn't match the target, it will fail.

the conclusion is to alter the selection:

INSERT INTO table1
SELECT col1, col2, NULL as col_extra, 'default' as col_extra2, col3, [...]
FROM table2

not tested, but should work


IN subselects vs INNER JOIN (performance) I have to make a choice:

choice 1: using IN subselect
choice 2: using INNER JOIN

See the examples:

IN subselect:

SELECT contractServiceCode FROM contractServices
WHERE contractID IN (
    SELECT contractID FROM contracts
    WHERE companyID IN (
            SELECT companyID FROM tblcompanies
            WHERE informationProvider = 1000000
    )
)

INNER JOIN's:

SELECT cs.contractServiceCode
FROM contractServices as cs
INNER JOIN contracts c ON (c.contractID = cs.contractID)
INNER JOIN tblcompanies tc ON (tc.companyID = c.companyID)
WHERE
    tc.informationProvider = 1000000

In terms of readability I would say that the INNER JOIN is more readable.
But I'm not worried about readablity. In my case performance / CPU / memory usage are import.

So the question is:

Which choice is better in terms of performance?

The best thing to do would be to test them in a non-production environment with similar workload and look at the Execution plans for both.  This is an advanced topic and I don't pretend to know everything about it but you might see noticeable execution time differences from the get-go in bottom right of SSMS.  If not, looking at the plans, pay attention to 'scans' and 'key lookups' as those should be avoided whenever possible.  What you generally want to see is a small number of seeks, which means it's going into the table with purpose and knows what it's looking for.  Scans mean it's going row by row through the entire table so much slower.  If one does a seek versus a scan, go with the seek!

You can also use commands like:

set statistics time ON;
set statistics IO ON;
GO;

And then run the queries individually to see the number of reads/writes as well as seeks and scans if you are uncomfortable with the execution plan approach.  Here is a good link as an introduction and if you aren't familiar with Brent Ozar, I'd highly suggest checking out his free scripts and he's blogged a ton about perform tuning.


SQL Server on CentOS using CAL licensing I have been running a PoC for a large data science workload with SQL Server on CentOS.
I recently discovered that SQL is using only 20 cores of a 56-core server.  I understand that this is because it is operating under the Server+CAL licensing mode, as shown by this entry in the SQL log:

  SQL Server detected 2 sockets with 28 cores per socket and 28 logical processors per socket, 56 total logical processors; using 20 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

I know I can change Edition using mssql-conf, but is it possible to change the licensing mode so that I can address all CPU sockets?

Yes, the wrong SQL Server installation files (for the CAL-based setup) were used. The 20-core-limit version is designed for people upgrading from older versions of SQL Server, as explained by Aaron Bertrand.

Check with the team who got your installation files for SQL Server (ISO, CD, ZIP, etc) to get the core-based installation files instead of CAL-based installation files.

Microsoft's instructions for Linux don't include CentOS, but if you read through the comments on that page, they tell you how to pick your repo. If you're still using the latest CU repo and you're still getting the issue, then contact Microsoft support - they may have deployed the wrong binaries to the repo. (This wouldn't be something an outsider could fix.)


SQL Server 2008 R2 Express single CPU core usage and need of upgrade to SQL Server 2014 Express In our office we are using SQL Server 2008R2 Express which is installed in Windows server 2012. The SQL Server database is accessed by more than 60 users in network simultaneously. In recent days we are experiencing slow performance of SQL Server and our programs which makes use of SQL Server.

From last few years we were using Windows Server 2003 with SQL Server 2008 R2 Express. Few months we changed server to  Windows Server 2012.

I can analyze the performance of SQL Server using performance counters etc. But before that I wanted to know below details

Is there any limitation in SQL Server 2008 R2 Express like it uses only one core of the CPU? And does it may be a reason for slow performance?
Are there compatibility problems when using old version (SQL Server 2008 R2 Express) on Windows Server 2012?
Is there any need of upgrading to SQL Server 2014 Express? Will I get any good benefits?

Express editions are limited, see Features Supported by the Editions of SQL Server:

  Maximum Compute Capacity Used by a Single Instance: Limited to lesser of 1 Socket or 4 cores
  Maximum memory utilized: 1 GB
  Maximum relational Database size: 10GB

These limitations apply to 2008R2, 2012 and 2014 versions, and for 2005 and 2008 the max database size is even less: 4GB (CPU and memory restrictions are the same).

To troubleshoot your performance problems I recommend you read How to analyse SQL Server performance. If you discover that (likely) you are hitting the Express Edition limitations then you need to upgrade to a paid license edition (probably Standard). You will know you are hitting the limitations:

if you see CPU contention you'll see high SOS_SCHEDULER_YIELD wait times and high CPU perf counters on only 4 cores while otehr are idle.
symptoms of the 1GB memory utilization you'll see high PAGEIOLATCH_XX wait times and high disk utilization on OS counters (low memory triggers more IO).

Upgrading Express Edition from 2008R2 to 2012 should be a trivial matter, same for 2014. I don't think there is any feature in 2014 or 2012 that you could leverage in Express Edition, but reasons to choose the latest version always exists: T-SQL language enhancements, product support. See What's new in SQL Server 2014 and What's new in SQL Server 2012.


Will our application server have issues connecting to a Postgres 11.4 server with a 9.6 client? Our application server is on EC2 running on Amazon Linux 1.  Postgres dropped support for Amazon Linux and so we depend on Amazon providing the postgres client.

The client is 9.6 and our Amazon hosted RDS postgres server is 11.4.  Because of our automation, it would take a good amount of time to upgrade from Amazon Linux 1 to Amazon Linux 2.  Even then Amazon Linux 2 only has a postgres 10 client.

It's a small app that uses the Rails ORM Active Record and only does simple queries and inserts.

Is it a no-go to use 9.6 as the client?  I'm wondering what the risk are.

I don't believe that PostgreSQL dropped support for any Linux distribution. Perhaps you mean that there are no binary installation packages provided.

Using a 9.6 client is no problem, since 9.6 is a supported release. You will not be able to use new features like scram-sha-256 authentication, but I guess you can live without that.

Building PostgreSQL from source would be another option.


How to check if the table has rows? I am completely new to PL/SQL. I have written the following PL/SQL script. But it doesn't execute and gives compilation error:

set serveroutput on SIZE 1000000;
IF EXISTS (select * from my_table)
begin
dbms_output.put_line('has rows');
end;
else
begin
dbms_output.put_line('no rows');
end;

Can anyone tell me what is wrong with this?

How can I do this?

Anonymous PL/SQL blocks don't start with IF. The above code should be in between a begin and end; at least.

EXISTS is an SQL function, it can't be used in PL/SQL like that.

Try something like this:

set serveroutput on
declare
  c number;
begin
  select count(*) into c from my_table where rownum = 1;
  if c != 0 then
    dbms_output.put_line('has rows');
  else
    dbms_output.put_line('no rows');
  end if;
end;
/

Yes, using EXISTS in the query is also possible:

set serveroutput on
declare
  c varchar2(10);
begin
  select case when exists (select * from my_table) then 'has rows' else 'no rows' end into c from dual;
  dbms_output.put_line(c);
end;
/

Note that both the EXISTS and rownum = 1 version will stop on the first row found, and that's the point, so we don't need to read the whole table/index.


Estimated vs Actual rows differences (actual much smaller than estimated) - sort I'm running a query that is processing some nodes out of an XML document.  My estimated subtree cost is in the millions and it seems it all comes from a sort operation sql server is performing on some data that I extract from xml columns via XPath.  The Sort operation has an estimated number of rows to be around 19 million, whereas the actual row count is about 800.  The query itself runs reasonably well (1 - 2 seconds), but the discrepancy has me wondering about the query performance and why this difference is so large?

There is no statistics generated on XML columns. The estimates is guessed based on the expressions used when querying the XML.

Using this table:

create table T(XMLCol xml not null)
insert into T values('<root><item value = "1" /></root>')

And this rather simple XML query:

select X.N.value('@value', 'int')
from T
  cross apply T.XMLCol.nodes('root/item') as X(N)

Will give you one row returned but the estimated rows returned is 200. It will be 200 regardless of what XML or how much XML you stuff into the XML column for that one row.

This is the query plan with the estimated row count displayed.

A way to improve, or at least change, the estimates is to give the query optimizer some more information about the XML. In this case, because I know that root really is a root node in the XML, I can rewrite the query like this.

select X2.N.value('@value', 'int')
from T
  cross apply T.XMLCol.nodes('root[1]') as X1(N)
  cross apply X1.N.nodes('item') X2(N)

That will give me an estimate of 5 rows returned.

The rewrite of the query will probably not speed up the shredding of the XML but if the estimates are better, chances are that the query optimizer can make smarter decisions for the rest of the query.

I have not found any documentation on what the rules are for the estimates other than a presentation by Michael Rys where he says:

  Base cardinality estimate is always 10’000 rows!
  Some adjustment based on pushed path filters


SQL Server Database Role not working I have tried to set up a database role that the users are able to execute or view stored procedures or functions as well as SELECT, INSERT, UPDATE, DELETE data on tables via stored procs. This is what I have done is;

CREATE ROLE  db_execute_procedure_only
GRANT EXECUTE ON SCHEMA::dbo TO db_execute_procedure_only
GRANT  VIEW DEFINITION ON SCHEMA::dbo TO db_execute_procedure_only
DENY CONTROL  ON SCHEMA::dbo TO db_execute_procedure_only

Then set the users database membership to db_execute_procedure_only

Problem is it does not work in SSMS. I have tracked down the problem. If I explicitly state execute as user = 'XXXXXX', I get the desired result. Issue is that SSMS uses CURRENT_USER which still points to dbo which has db_owner rights.

Question is - how can I get CURRENT_USER pointing to a user which has say read/write only access?

CURRENT_USER refers to the user role your current login has on the specific database.
For instance i have a user called PlebUser, who has db_owner rights on a specific database A, has no rights (not even viewing) on database B and has generic guest rights (server role public) for my master db (C).
Running Select CURRENT_USER for each of these databases returns me:

A: PlebUser (defined role on my database)
B: Nothing, can't even run it because i'm denied on this DB
C: Guest

You need to check whether you are logged in as your defined user (and not as DBO/SA, whatever admin account you are using to create the new users), and that they are properly mapped to specific databases.
You can do this by checking the server wide security logins, properties on the specific login and check user mapping.


It seems there is an always-running program that reconnects to this database as soon as it's disconnected.

Before terminating the connections, you might prevent this from happening again with:

REVOKE CONNECT ON DATABASE mydb FROM public, myname;


DB2 archive log extraordinary growth for any operation after runstat/reorg My environment:
DB2 10.1.4 Enterprise, IBM Security Identity Manager 6.0 FP6, AIX 7.1
Each archive log have a size of 40MB

Yesterday, we do a maintainance on ISIM database by doing online backup. The backup size is around 7GB. After that, we prune the ISIM database by using
db2 prune history 20151001 and delete

Then we runstat and reorg all the table in ISIM database (No automatic maintainance is set on this database and never runstat/reorg for more than 6 months) using Data Studio 4.1.0.

After we reorg the database, we runstat again and notice an increase amount of space that archive logs take up (around 3 GB of archive log generated during runstat). So we thought something might hung inside db2 process so we restart db2 using db2stop / db2start

After restart DB2, we test the system and everything look fine, around 10GB available after we restart DB2 in the night. However, in the morning we found out that all spaces are used up by archive log.

We allocate 10GB to the disk in the morning and it exhausted very fast in a way that we never seen before.
Normally, when ISIM reconcile one system, we didn't even notice the change in archive log's size even it is reconcile every hour, however after last night maintanace, The same reconcile took up more than 50 GB of space even it is just one time reconcile and it slow down ISIM reconcile process as well, all of those space occupied by archive logs that seems to generate new file every second during reconcile.

Now, our HADR DB can't catch up with this amount of archive log file and all are disconnected, we decided to fix the problem on main DB first but using db2pd to check any active runstat and reorg didn't give any result. Restore the database now is not an option as it is a production database. We think that we might do offline runstat / reorg tonight but we don't sure if it can solve problem or not.

Anyone have any suggestion?

Finally found what actually happen after 150GB in one day for archive log.
After doing offline runstat again, It seems that we ignore the "SQL2310N The utility could not generate statistics. error -930" on table RECONCILIATION_INFO

At first, we thought it might not be an important error but when we dig through db2diag.log, we found error 964 (transaction log full) as well during runstat however, we didn't found any error during reconcile in db2diag.log

So we increase LOGPRIMARY value to 25 (ISIM default is 13) and then, runstat work smoothly with a few GB increased in archive log. After that, reorg and runstat again didn't eat up space anymore.

We did some test by reconcile as usual and now disk space is not increase anymore.

I still wonder why runstat can cause a disk space problem like this even we just run it manually, restart database didn't help at all once we trigger it.


UPDATE not working properly with linked server Please see a question I asked here earlier: https://stackoverflow.com/questions/24324823/update-not-working-properly-with-linked-server

Dommer suggested using OPENQUERY and this does appear to resolve the problem. Does anyone know why OPENQUERY resolves the problem? I have read the documentation for OPENQUERY here: http://msdn.microsoft.com/en-gb/library/ms188427.aspx, but I have not found an answer to my specific question.

The fundamental issue is: Which server processes the query?

When using a linked server query, the query is executed on the LOCAL server and uses the linked server to access data on the remote server. (Linked servers are subject to several limitations, some caused by running through the Distributed Transaction Coordinator (DTC).)

When using OPENQUERY, the query is sent to the REMOTE server and is executed there in order to return data to the local server.  (You will notice that in OPENQUERY the query uses a three-part name, since it all runs on the remote server, rather than the four-part name used for the linked server.)

A simple exposure to the limitations can be found in this thread: http://www.sqlservercentral.com/Forums/Topic948316-338-1.aspx

It mentions network topology, data types, type of remote server, the code in the query, etc.

EDIT: See more on the Guidelines for Using Distributed Queries at:
http://technet.microsoft.com/en-us/library/ms175129(v=sql.105).aspx

The subheading Other Guidelines mentions:  "To create the best query plans ..., the query processor must have data distribution statistics from the linked server. ... If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server."

If your connection to the remote server does not have access to those statistics that might cause a poor plan.  Ideally not an inaccurate plan, but the query processor is imperfect.


several idle connections SET bytea_output i have a situation that i don't know if it is normal, i think not.

I have several idle connections with the query SET bytea_output='escape', so i arrive to max_connections and have issues with my applications, why are there so many connections idle with that query?

Many libraries which manage connections for you will execute some housekeeping queries on the new connection before returning it to the caller.  SET bytea_output='escape' is just the type of housekeeping query you might expect to see.

So the likely explanation is that someone keeps asking for a new connections, and then immediately forgets about it without either using it or closing it.  To figure out who, you should be looking at other fields of "pg_stat_activity", like usename, application_name, and client_addr.

And ultimately if you don't know who is connecting to your database, you should be changing the passwords or other authentication methods.


inserting data from one table to another after casting the values I have a #temp table containing all the data that needs to be inserted into a table named Customer.
The column names of the two tables are exactly the same, but the data types are different. So I need to cast from one type to another before inserting the data into the Customer table.

The solution has to be implemented for a dynamic condition. i.e, I don't know the column name or data type.

I would be writing some code to do this, I'm not sure whether it would be achievable using purely SQL. You'd need to dynamically create a (or some) SQL statements, which may be a question for the chaps over in the programming section, however:

SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tablename' AND COLUMN_NAME = 'columnname'

Would give you the datatype; you could then select the data from your other table and cast it to the datatype, you can also pull column_name from information_schema but without a table name.. That's another question!


Why can't I restore the DB from an SQL dump in PostgreSQL 9.4 (problem with dblink)? I have created a dump of a DB using:

pg_dump db_name > db_dump.sql

and proceed to restore it with:

psql --single-transaction db_name < db_dump.sql

Prior to attempting to restore the DB, I create the DB as:

DROP DATABASE IF EXISTS db_name;
CREATE DATABASE db_name;
GRANT ALL ON DATABASE db_name TO db_user;

Also, prior to creating the DB db_name I add the extension dblink to the DB template1 as the user postgres as per:

CREATE EXTENSION dblink;

The command \df+ executed in the DB db_name shows:

 List of functions
 Schema |  Name  | Result data type | Argument data types |  Type  | Security | Volatility |  Owner   |...
--------+--------+------------------+---------------------+--------+----------+------------+----------+---
 public | dblink | SETOF record     | text                | normal | invoker  | volatile   | postgres |...
 public | dblink | SETOF record     | text, boolean       | normal | invoker  | volatile   | postgres |...
 public | dblink | SETOF record     | text, text          | normal | invoker  | volatile   | postgres |...
 public | dblink | SETOF record     | text, text, boolean | normal | invoker  | volatile   | postgres |...

When I go to execute psql --single-transaction db_name < db_dump.sql I see the following error message:

ERROR:  function dblink(text, text) does not exist

The output from \df+ tells me that the extension has been added though.

Why is this happening? What have I missed?

The comment from @a_horse_with_no_name helped me solve the issue i.e., the extension was not installed in the right schema when attempting to restore the DB. When moved to the right schema the restore finished with no errors.


pg_get_viewdef error while running pg_dump in PostgreSQL Occasionally, I get the following error while doing a simple pg_dump (
pg_dump -U postgres -Fc -f mybackup.dmp mydb):

pg_dump: [archiver (db)] query failed: ERROR:  could not open relation with OID 11072443
    pg_dump: [archiver (db)] query was: SELECT pg_catalog.pg_get_viewdef('11072443'::pg_catalog.oid) AS viewdef

The dump is then aborted.

I'm using PostgreSQL and pg_dump 9.2.2 for Windows.

Solutions welcome.

Thanks guys for the suggestions, I think I found the problem.

Each night, several bash scripts drop and recreate some tables, which each time get a new OID. This would explain why a different OID was appearing in each successive error message, as suggested by Josh.

I believe that on some occasions, pg_dump started while some of these tables were still being dropped/recreated, hence the error message.


How to select the first unique data type in a table Sorry for the messy layout question. This is my first post.

I have the following table structure under ORACLE database

List item

  Row     A      B        Date
  --------------------------------
  1       X      X        01-2012
  2       X      X        01-2013
  3       Null   Null     01-2013
  4       New    Apple    01-2016
  5       New    Orange   08-2015
  6       Null   Null     09-2015

I want to be able to query for the first time that "New" is in the database entry and get the value for both A, B and Date

Tried using syntax first_row but i get an invalid identifier result. Also attempted to create subqueries to limit and group by 'New' data but the issue I'm having is to only look at the "first" ever row per Date

Due to the bad naming convention on your question, I will use ColumnA as A, ColumnB as B and ColumnDate as Date.

I am assuming that you require the first item as it's fetched from storage, no particular order. And you have to know that if the row gets updated it can be migrated from the current block and so, the query result will eventually change.

This queries are examples only and you have to edit them to get exactly what you want.

You may try the following query:

SELECT ColumnA, ColumnB, ColumnDate
FROM YOURTABLE
WHERE ColumnA='New'
AND rownum = 1;

In Oracle every result set have an implicit row number that you can use for limiting output.

If, by any chance, you're using oracle 12c you can use the brand new feature for top n queries

SELECT ColumnA, ColumnB, ColumnDate
FROM YOURTABLE
WHERE ColumnA='New'
FETCH FIRST 1 ROWS ONLY;

I hope this helps!


Troubleshooting high CPU usage from postgres and postmaster services? I'm using an open source (RHEL 6.2) based machine running SIEM software. When I run the top command, I see postgres and postmaster both with 96% CPU usage. Is there a way to pin-point or see what causing these service to stack up?

You can match a specific Postgres backend ID to a system process ID using the pg_stat_activity system table.

SELECT pid, datname, usename, query FROM pg_stat_activity; can be a good starting point.
Once you know what queries are running you can investigate further (EXPLAIN/EXPLAIN ANALYZE; check locks, etc.)


To get the result that you want, it appears that you'll need to use a FULL JOIN. A FULL JOIN will join your 2 tables on the bln column but it will return data from both tables, even if the value of the column doesn't appear in both tables.

The basic syntax of the query will be:

select
  bln = coalesce(t1.bln, t2.bln),
  thn = coalesce(t1.thn, t2.thn),
  qty1 = isnull(t1.qty1, 0),
  qty2 = isnull(t2.qty2, 0)
from table1 t1
full join table2 t2
  on t1.bln = t2.bln

See SQL Fiddle with Demo.  This will give a result of:

| BLN |  THN | QTY1 | QTY2 |
|-----|------|------|------|
|   1 | 2014 |   10 |    0 |
|   1 | 2014 |   20 |    0 |
|   2 | 2014 |   30 |  100 |
|   ....
|   5 | 2014 |    0 |  400 |
|   6 | 2014 |    0 |  600 |

You'll notice that I used a COALESCE on the columns bln and thn - this will return the first non-null value in those columns. I then used IsNull on the qty1, and qty2 columns to replace the null values returned for these columns with a zero.  You could use either COALESCE or ISNULL for these operations - I'm using both for demo purposes.

Now, since you want to get the sum of the qty1 and qty2 columns, you'll add the aggregate function with a group by.  I'd actually do this in a subquery for each table, otherwise you could get an elevated total for each quantity.

select
  bln = coalesce(t1.bln, t2.bln),
  thn = coalesce(t1.thn, t2.thn),
  qty1 = isnull(t1.qty1, 0),
  qty2 = isnull(t2.qty2, 0)
from
(
  select bln, thn, qty1 = sum(qty1)
  from table1
  group by bln, thn
) t1
full join
(
  select bln, thn, qty2 = sum(qty2)
  from table2
  group by bln, thn
) t2
  on t1.bln = t2.bln

See SQL Fiddle with Demo.  This gives the result:

| BLN |  THN | QTY1 | QTY2 |
|-----|------|------|------|
|   1 | 2014 |   30 |    0 |
|   2 | 2014 |   80 |  600 |
|   3 | 2014 |   40 |  200 |
|   4 | 2014 |   60 |  300 |
|   5 | 2014 |    0 |  400 |
|   6 | 2014 |    0 |  600 |

Finally, you just use the query to create a view:

create view dbo.your_view as
select
  bln = coalesce(t1.bln, t2.bln),
  thn = coalesce(t1.thn, t2.thn),
  qty1 = isnull(t1.qty1, 0),
  qty2 = isnull(t2.qty2, 0)
from
(
  select bln, thn, qty1 = sum(qty1)
  from table1
  group by bln, thn
) t1
full join
(
  select bln, thn, qty2 = sum(qty2)
  from table2
  group by bln, thn
) t2
  on t1.bln = t2.bln

See Demo


galera cluster with mariadb 10.3.13 is not replicating on RHEL 8.1 I'm installing a galera cluster using mariadb 10.3.13 with two nodes in redhat 8.1 server.
I enabled ports 3306, 4567, 4568 y 4444 ports. selinux is disabled. firewall disabled.
The first node is started by galera_new_cluster. It works.
When I try to start the second node by systemctl start mysql. It delays to finish the process. The data is not replicating but I see no errors.
By tcpdump I see traffic incomming to second node but it's not replicating.

What can I do?

server.cnf configuration:

[server]

[mysqld]
log_error=/var/log/mysql/mysql_error.log

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://172.24.10.53,172.24.10.54
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

mysql_error.log

2020-01-30 23:39:37 0 [Note] WSREP: STATE EXCHANGE: got state msg: afb4ec56-43e3-11ea-9fc0-c7a8579dc264 from 1 (dbaCluNodo1.pki)
2020-01-30 23:39:37 0 [Note] WSREP: Quorum results:
    version    = 4,
    component  = PRIMARY,
    conf_id    = 1,
    members    = 1/2 (joined/total),
    act_id     = 0,
    last_appl. = 0,
    protocols  = 0/9/3 (gcs/repl/appl),
    group UUID = 5a3e63d8-43db-11ea-9acf-7a26185f2888
2020-01-30 23:39:37 0 [Note] WSREP: Flow-control interval: [23, 23]
2020-01-30 23:39:37 0 [Note] WSREP: Trying to continue unpaused monitor
2020-01-30 23:39:37 2 [Note] WSREP: New cluster view: global state: 5a3e63d8-43db-11ea-9acf-7a26185f2888:0, view# 2: Primary, number of nodes: 2, my index: 1, protocol version 3
2020-01-30 23:39:37 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2020-01-30 23:39:37 2 [Note] WSREP: REPL Protocols: 9 (4, 2)
2020-01-30 23:39:37 2 [Note] WSREP: Assign initial position for certification: 0, protocol version: 4
2020-01-30 23:39:37 0 [Note] WSREP: Service thread queue flushed.
2020-01-30 23:39:39 0 [Note] WSREP: (e88922b7, 'tcp://0.0.0.0:4567') turning message relay requesting off

Ok, I know what happened. What I did:

1) vi /etc/selinux/config

2) Change state to from enforcing to disabled

3) Apply init 6 (restart server)


How to specify the exact .ndf file on a filegroup to save a table in? I have a filegroup(not the primary one) which contains two different .ndf files, for example a1.ndf and a2.ndf.

Now I want to create a table and I want my table to be saved in a2.ndf file not a1. Is there a way to do things like this? I am using SQL Server.

Thanks.

No, you can't specify the exact data file you want the data to go into.

What you can, though, is specify the filegroup at the table creation. If you want to fill in a big table on a specific disk drive, then you have to create a different filegroup, and create the table on that filegroup.


Migrate MySQL 5.7 to 8.0 via mysqldump I have an Ubuntu server 18.04 LTS with MySQLl 5.7
I have new Ubuntu backup server 20.04 LTS with MySQL 8.0
MySQL 5.7 backup created:
mysqldump --all-databases > "filename" -u root -p

Restore to 8.0 fails on MySQL database due to ISAM tables.
Restoring individual databases, except MySQL, works as expected.
Once I have an 8.0 backup I'll upgrade the 18.04 server to 20.04 with MySQL 8.0
Is it possible to modify the MySQL 5.7 dump to load on 8.0?

Recreate the dump from MySQL 5.7
Personally, I would recreate the dump without mysql schema.
SQL="SET group_concat_max_len = 1024 * 1024;"
SQL="${SQL} SELECT GROUP_CONCAT(schema_name separator ' ')"
SQL="${SQL} FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql','sys')"
DBLIST=`mysql -u... -p... -AN -e"${SQL}"`
mysqldump -u... -p... -B ${DBLIST} > mydbs.sql

Then, you can load mydbs.sql in the MySQL 8.0 instance.
What about the dump you already have ?
First, find out the lines in the dump where each database starts and the number of line in the dump
grep -in "^USE" mydbs.sql
wc -l < mydbs.sql

As an example, I created a dump from a MySQL 5.7 vagrant instance.
root@LAPTOP-57FSQ99U:~# grep -in "^USE" mydbs.sql
24:USE `DMP492`;
122:USE `DMP551`;
190:USE `DMP579`;
228:USE `abeersul`;
262:USE `dbname`;
292:USE `karma`;
300:USE `mysql`;
1212:USE `pch_prod`;
1220:USE `pchcom_prod`;
1286:USE `tryout`;
root@LAPTOP-57FSQ99U:~# wc -l < mydbs.sql
1341
root@LAPTOP-57FSQ99U:~#

Next, create a new dump. Copy everything above and below the mysql schema
In this case, everything before line 300
Then, copy everything from the first DB after mysql schema
In this case, subtract 1212 (first line of the next DB after the mysql schema) from 1341 (the number of lines in the original dump you made) and add 1. Then copy that number of lines from the bottom of the original dump into the new dump:
head -299 mydbs.sql > newdump.sql
(( LINES = 1341 - 1212 + 1 ))
tail -${LINES} mydbs.sql >> newdump.sql

As a demonstration, note how the new dump will not have the mysql schema
root@LAPTOP-57FSQ99U:~# head -299 mydbs.sql > newdump.sql
root@LAPTOP-57FSQ99U:~# (( LINES = 1341 - 1212 + 1 ))
root@LAPTOP-57FSQ99U:~# tail -${LINES} mydbs.sql >> newdump.sql
root@LAPTOP-57FSQ99U:~# grep -in "^USE" newdump.sql
24:USE `DMP492`;
122:USE `DMP551`;
190:USE `DMP579`;
228:USE `abeersul`;
262:USE `dbname`;
292:USE `karma`;
300:USE `pch_prod`;
308:USE `pchcom_prod`;
374:USE `tryout`;
root@LAPTOP-57FSQ99U:~#

Now, just load newdump.sql into MySQL 8.0


How to delete an endpoint only if exists I'm creating a script to automatically configure 2 databases for mirroring, using this how to but I want to make sure there is no end point or master key already created so I would like to first do some clean up as:
DROP ENDPOINT End_Mirroring;
DROP CERTIFICATE PRIM_cert;
DROP MASTER KEY

But how can I do these drops only if they already exist?(i need this to avoid my script to exit with error)

IF EXISTS ( SELECT * FROM sys.tcp_endpoints WHERE name = 'End_Mirroring')
DROP ENDPOINT End_Mirroring;
IF EXISTS ( SELECT * FROM sys.certificates WHERE name = 'PRIM_cert' )
DROP CERTIFICATE PRIM_cert;
IF EXISTS (SELECT * FROM sys.symmetric_keys WHERE [name] LIKE '%DatabaseMasterKey%')
DROP MASTER KEY";

OK, found it :)


T-SQL CREATE VIEW returning 'There is already an object named x' SQL Server 2014. I have a database on which I created a view called 'Sales-Analysis'. I also have a second database with the same schema as the first and I want to add the view to that. So in SSMS I performed 'Script view as CREATE to new Query Editor window'. I changed the first line of the script to reflect the name of the second database and ensured it was selected in the SSMS databases dropdown too. However when I execute I get:

"There is already an object named 'sales-analysis' in the database."

There isn't other than the one in the original database. No temporary tables involved. VS 2015 is closed.

It also doesn't work even if I take the original db offline, or if I save the script as an .SQL file and execute it with SQLCMD.

Same server. Refreshed views. Can successfully create a view with a '2' appended into the target database. It's just when the view name is the same as the original database. use [database] is already at the beginning of the script -'Script view' adds it. I changed it to the target database name. The view 100% does not exist in the target db. I checked via querying sys.views.

Turns out the target db had a TABLE (not view) with the same name. I didn't create it, I suspect what has happened is that an Entity Framework project which I have created it when I changed the app.config connection string to point at the second database and ran the project.

Naughty Entity Framework.


Failed to start of service MongoDB community4.2 using Homebrew in Mac OS Mojave 10.14 Error message while starting the mongoDB service.
Error:

  Permission denied @ rb_sysopen -
  /Users/syedahmed/Library/LaunchAgents/homebrew.mxcl.mongodb-community.plist

I installed/reinstall the mongoDB couple times.
But I am getting same problem

  " error : permission denied"

I am using the community@4.2 edition in Mac OS Mojave 10.14

I have tried different ways after reading suggestions in stack overflow but nothing has worked.

I have installed following the instruction from mongoDB.

The services doesn't start.

how to config the username and password using config?  I am not sure.

while installing from brew it did not ask for config any password or username.

brew services start mongodb-community@4.2

if I start service typing just:  mongo
Following error message comes:

  MongoDB shell version v4.2.0 connecting to:
  mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb
  2019-09-01T23:23:31.392-0400 E QUERY [js] Error: couldn't connect to
  server 127.0.0.1:27017, connection attempt failed: SocketException:
  Error connecting to 127.0.0.1:27017 :: caused by :: Connection refused
  : connect@src/mongo/shell/mongo.js:341:17 @(connect):2:6
  2019-09-01T23:23:31.397-0400 F - [main] exception: connect failed
  2019-09-01T23:23:31.397-0400 E - [main] exiting with code 1 –

use code below before mongo

sudo brew services start mongodb-community


Which database management system type to choose for price tracking? I'm writing a price tracking application.

Every day I need to store price information about ~15M products. (So it's about 15M * 365  = 5.5B records per year).

One product has many prices over history. My main table (if I opt for a SQL DBMS) should include the following attributes:

product_id (8bytes int)
price (4bytes int) can be NULL
date (only date without time since daily update)
currency (3byte string)

E.g. (9223372036854, 85.41, 14-01-2019, "USD")

I don't want to mark the latest price as 'active' or something, so I'm interested only in price history itself. The database should have high IO throughput (~1000 simultaneous reads).

Queries I'm planning to run:

Get all price for the specific product_id for the specific time range
Basic math operations like min, max, avg

So, I am curious:

Should I stick to a SQL DBMS (Postgres) or use some NoSQL product (e.g. Amazon Redshift)
Which DBMS can you recommend?

I would suggest building a test system using whatever DBMS you're most familiar with.  As speed is a mission-critical requirement, I would put the data on an NVMe SSD, and load it up with as much fake data as you can create, then put that through a rigorous set of tests.

Back-of-the-envelope calculations suggest you'll need around 100 GB of space for a years-worth of data, so a 1 TB NVMe SSD should suffice.  Of course, if data redundancy is a concern, you may want two, with data being mirrored between them.

PostgreSQL should have no difficulty handling your data.


PostgreSQL 9.2; Altering a table to use another sequence I've created a table like so:

create table doki_data_item2 (like doki_data_item including defaults);

but this new table uses the same sequence as the old table.

So two questions:

How can I copy and table and the necessary sequences easily?
How can I replace a sequence on one table with another sequence?

Why don't you create a new sequence:

create sequence new_sequence;

Then create the new table

create table doki_data_item2 (like doki_data_item including defaults);

Finally modify the column that is using the old sequence to use the new one:

alter table doki_data_item2 alter column seq_column set default nextval('new_sequence');

You can obviously set various parameters for the newly created sequence to suit your needs.


DELETE and INSERT - is this simple query statement sufficient to preserve atomicity? I intend to update a table by going for a DELETE then INSERT on postgres in a single query. Obviously I want the DELETE to only take place if the INSERT is successful, i.e. preserve atomicity.

Is writing the query as below, sufficient for this?

DELETE FROM table_one WHERE some_id = 1;
INSERT INTO table_one(col1, col2, col3) VALUES (val1, val2, val3), (val4, val5, val6), ...

Thank you very much.

EDIT:

After reading around, I believe I am supposed to use transactions. Is this correct?

BEGIN;
DELETE FROM table_one WHERE some_id = 1;
INSERT INTO table_one(col1, col2, col3) VALUES (val1, val2, val3), (val4, val5, val6), ...
COMMIT;

So if DELETE fails, then the entire transaction fails?

Yes you can use transactions.

And yes if one of transaction statements fails all of it will rollback. Meaning : Nothing will happen or everything will be done.

  Your code :

BEGIN;
DELETE FROM table_one WHERE some_id = 1;
INSERT INTO table_one(col1, col2, col3) VALUES (val1, val2, val3), (val4, val5, val6), ...
COMMIT;

See following link for more information about transactions: Transactions in PostgreSql


Performance on SQL Join I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK.

The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits
So RN1234567 for example.

However if I strip out the first 2 letters (  right(tbl1.id,7) = right(tbl2.id,7)  )and join only on the digits the query speed improves greatly. Why is this?

Your statistics need updating.

See Statistics, row estimations and the ascending date column.

Recent dates are not proportionately represented in the statistics and so SQL Server underestimates the number of rows that will match the >= 2012-12-31 predicate (as shown by the Estimated no of rows of 1 vs actual 13922). So it chooses a plan with nested loops and a seek on the other table.

The use of right(tbl1.id,7) = right(tbl2.id,7) prevents a seek being used so you get the hash join more suitable for the actual number of rows involved.

If you update  the statistics on that column it should choose a more appropriate plan naturally. You might also consider using Trace Flags 2389 & 2390 to make this issue less likely in the future.


How to set IP segment and connection string with IPv6? For this PostgreSQL configuration example:

https://repmgr.org/docs/4.4/quickstart-authentication.html

    local   replication   repmgr                              trust
    host    replication   repmgr      127.0.0.1/32            trust
    host    replication   repmgr      192.168.1.0/24          trust

    local   repmgr        repmgr                              trust
    host    repmgr        repmgr      127.0.0.1/32            trust
    host    repmgr        repmgr      192.168.1.0/24          trust

I have two questions.

Question 1

This is how a netmask looks for IPv4: 192.168.1.0/24.

How to do with an IPv6 IP address like 230b:c010:103:5858:a6a3:3:0:1?

Question 2

If use JDBC to connect to a PostgreSQL server, I can use a connection URL containing 192.168.1.11:5432. How to do it with IPv6? Is it like 230b:c010:103:5858:a6a3:3:0:1:5432?

Answer 1:

Like the documentation says:

  An IPv6 address range might look like ::1/128 for a single host (in this case the IPv6 loopback address) or fe80::7a31:c1ff:0000:0000/96 for a small network. 0.0.0.0/0 represents all IPv4 addresses, and ::0/0 represents all IPv6 addresses. To specify a single host, use a mask length of 32 for IPv4 or 128 for IPv6. In a network address, do not omit trailing zeroes.

Answer 2:

Like the documentation says:

  host

  The host name of the server. Defaults to localhost. To specify an IPv6 address your must enclose the host parameter with square brackets, for example:

jdbc:postgresql://[::1]:5740/accounting


SQL Server - How many type of timeouts can happen, and how? When working with SQL Server, there can be multiple application host accessing it, and each application can have one or multiple connections. Each connection can potentially have multiple transactions (please correct me if I am wrong). Each transaction can perform query or non-query SQL.

In my experience, I easily run into timeout if I query a table that is exclusively locked. I also saw SQL Server detect and throw a deadlock exception instead of timeout if two different applications lock on the same resource. I also show rebuilding index timeout, which possible due to someone still have connection to table.

However, I also encounter a kind of deadlock where SQL Server doesn't detect it or timeout out. In this application, it opened two connections, two separate transactions, where 1st transaction locked a resource, and the 2nd transaction try to access the same resource, but it hasn't close the first transaction.

Would someone provide a list of types of timeouts and/or deadlocks, it would help me to avoid these kind of cases when working on the application.

Well from an application point of view there are:

connection timeout (how long the app is willing to wait to establish a connection to SQL Server)
command timeout (how long the app is willing to wait for a command to complete, including pulling the results down from SQL Server)

Back in my classic ASP days, the defaults for these were 15 and 30 seconds respectively, I have no idea what they are by default in .NET today.

SQL Server has its own set of timeouts, for example:

Remote query timeout. Default is 600 seconds (10 minutes).
Remote login timeout. Default is 10 seconds.
Query wait. Default is -1 (25 x query cost).
Full-text protocol handler timeout. Default is 60 seconds.

You can see these values for your system here:

SELECT * FROM sys.configurations
WHERE configuration_id IN (1519,1520,1541,1557);

There is also @@LOCK_TIMEOUT (which defaults to -1 (infinity)). This is how long SQL Server will wait on a blocked resource. You can override this for a particular session using SET LOCK_TIMEOUT. More details here.

Deadlocks I suppose could also fall into this category as well. The system scans for deadlock situations every 5 seconds, and there is no magic formula to determine when the deadlock will occur in relation to when any of the involved requests started. This is because SQL Server doesn't let the oldest transaction win; it chooses the victim based on DEADLOCK_PRIORITY and the estimated amount of resources required to roll the victim back. More details here.

There is also a memory grant timeout (which may be customized using Resource Governor). Depending on concurrency, a query won't necessarily fail if it reaches the timeout before obtaining all of the memory requested, it will just run with the allocated amount (and therefore might be less efficient). If it fails you will likely see Msg 8645.

You can get an idea for other potential timeout scenarios that may occur within SQL Server by reviewing these error messages:

SELECT message_id, [text]
  FROM sys.messages
  WHERE language_id = 1033
  AND ([text] LIKE '%timeout%' OR [text] LIKE '%time out%')

However I don't think it is practical, feasible or productive for anyone to try to provide you with a complete and exhaustive list of every single timeout situation possible. Solve the problems you're having, rather than prematurely solving a whole bunch of problems you probably never will...


How to log DML statements executed by a pl/pgsql function? I have a pl/pgsql function (see below) that lists some fields and clears their contents using dynamically constructed UPDATE commands.

When I set log_statement = 'mod', I can see nothing on the log upon execution of the function with SELECT fnct_clear_temp_fields();.
When I set log_statement = 'all', and execute the function I can see SELECT fnct_clear_temp_fields(); in the log, but not the underlying UPDATE commands.

Is there a way to have the UPDATE commands appear in the log as well ?

For info, here is the function:

CREATE OR REPLACE FUNCTION fnct_clear_temp_fields() RETURNS VOID AS $$
DECLARE
    --Put into a cursor a view dynamically listing all user-defined fields beginning with 'temp_'
    dataset_1 CURSOR FOR
        SELECT
            column_name,
            table_name
        FROM information_schema.tables
        NATURAL JOIN information_schema.columns
        WHERE
            table_schema='public'
            AND table_type='BASE TABLE'
            AND column_name ~ '^temp_'
        ORDER BY table_name,column_name;

    --Record variable to go through each line of the view above
    dataset_1_row RECORD;

BEGIN
    OPEN dataset_1; --Open the cursor
    FETCH dataset_1 INTO dataset_1_row; --first row of the view

    WHILE FOUND LOOP
        RAISE NOTICE 'Table: %, Column: %',  dataset_1_row.table_name,dataset_1_row.column_name;

        --Set to NULL the contents of the current 'temp_' column
        EXECUTE 'UPDATE '||dataset_1_row.table_name||' SET '||dataset_1_row.column_name||'=NULL WHERE '||dataset_1_row.column_name||' IS NOT NULL';

        FETCH dataset_1 INTO dataset_1_row; --next row please.
    END LOOP; --while end

    CLOSE dataset_1;

    RETURN;
END;
$$ LANGUAGE plpgsql;

Question asked

There is a built-in way to log all statements inside plpgsql functions: auto-explain

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 1; -- exclude very fast trivial queries
SET auto_explain.log_nested_statements = ON; -- statements inside functions

Details under this closely related question:
Postgres query plan of a UDF invocation written in pgpsql

Potentially generates a lot of log output. I would only use it for debugging, not in production.
If you just need the one statement logged, go with @dezso's advice.

Code audit

Consider this rewritten function:

CREATE OR REPLACE FUNCTION fnct_clear_temp_fields()
  RETURNS void AS
$func$
DECLARE
   rec record;
   qry text;
BEGIN
   FOR rec IN
      SELECT quote_ident(c.relname) AS tbl, quote_ident(a.attname) AS col
      FROM   pg_namespace n
      JOIN   pg_class     c ON c.relnamespace = n.oid
      JOIN   pg_attribute a ON a.attrelid = c.oid
      WHERE  n.nspname = 'public'
      AND    c.relkind = 'r'
      AND    a.attname LIKE 'temp_%'  -- LIKE is faster than ~
      AND    a.attnum > 0
      AND    NOT a.attisdropped
      ORDER  BY 1,2
   LOOP
      RAISE NOTICE 'Table: %, Column: %', rec.tbl, rec.col;
      qry := format('UPDATE %1$s SET %2$s = NULL WHERE %2$s IS NOT NULL', rec.tbl, rec.col);
      RAISE LOG 'Query: %', qry;
      EXECUTE qry;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Major points

You must sanitize all identifiers that you build into dynamic SQL, else it can fail with non-standard names that require double-quoting. Worse, you are open to SQL injection.
Demonstrating quote_ident(), since you are using the sanitized identifiers multiple times. There are more options with regclass or format():
Table name as a PostgreSQL function parameter
I prefer to base such queries on the system catalog instead of the painfully slow views of the information schema. That's a matter of requirements and taste, though. Demonstrating the equivalent, which is ~ 10 times faster (not regarding UPDATE commands). More:
How to check if a table exists in a given schema
LIKE is generally faster than the more powerful regular expression matching (~). If LIKE can do the job, use it.
Some other minor simplifications.
Related answer with more details:
Update record of a cursor where the table name is a parameter


Update all rows but with limited number at a time First of all, sorry if this question has been answered before. I am new to Microsoft SQL and StackExchange and I wasn't able to find help regarding this thing I want to improve.

So I have one task to update all rows in a table with data from another row.
This table has currently 3,9 million rows and counting.
I want to update all rows in the table but only 200k-300k rows at a time.

My current script looks like this:

alter table TableA add blob_image_tmp varbinary ( max )
GO

update TableA
 set blob_image_tmp = cast ( blob_image as varbinary ( max ) )
 where columnA < 200000
GO

update TableA
 set blob_image_tmp = cast ( blob_image as varbinary ( max ) )
 where columnA > 199999 and columnA < 400000
GO

update TableA
 set blob_image_tmp = cast ( blob_image as varbinary ( max ) )
 where columnA > 399999 and columnA < 600000
GO

....and so on up to 3,9million.

Then I end it with the following code:

alter table TableA drop column blob_image
GO

exec sp_rename 'TableA.blob_image_tmp' , 'blob_image' , 'COLUMN'
GO

alter table TableA rebuild

Is there a simpler way to achieve the same result?
I have another table I need to do the same thing with but that one has more than 10 million rows so it feels annoying to split each part up like this.

I would set up a WHILE loop similar to the following example:

set nocount on
IF OBJECT_ID('tempdb..#TableToUpdate') IS NOT NULL
    drop table #TableToUpdate
create table #TableToUpdate (KeyColumn int IDENTITY(1,1) NOT NULL,OriginalColumn int, NewColumn int)
go
insert into #TableToUpdate(OriginalColumn,NewColumn) values(1,1)
go 105

declare @RowsInTable int
declare @BeginRange int = 0
Declare @EndRange int = 0
Declare @Increment int = 10
set @RowsInTable=(select count(*) from #TableToUpdate)

while @EndRange < @RowsInTable
begin

set @EndRange += @Increment

print 'updating ranges from ' + convert(varchar(100),@BeginRange) + ' to ' + convert(varchar(100),@EndRange)

update #TableToUpdate
 set NewColumn = OriginalColumn
 where KeyColumn between @BeginRange and @EndRange

set @BeginRange = @EndRange

end
GO


Partition SQL Server Table by Year, 35 days rolling period We are trying to partition SQL Server tables by year (2015, 2016, 2017, 2018) and we want to create one more partition which hold current 35 days period data always.

Partitions: Part_2015, Part_2016, Part_2017, Part_2018, Part_35Day

Create Partition Function RangePartFunction (datetime2)
as Range Right For Values ('20150101', '20160101', '20170101')

I'm struggling how do we create for 2018 and 35 day rolling period

Can anyone please advise...

SQL Server does not currently have a way to declaratively specify a sliding partition window. It is necessary to execute T-SQL to remove the old boundary and create a new one. A T-SQL script (perhaps encapsulated in a stored procedure) can be scheduled daily for this purpose.

It is generally best to split and merge only empty partitions but this is not possible in your case because of the historical yearly partitions in addition to the 35-day window partition in the same table. One method to accomplish this with relative efficiency is to employ an identical partition scheme, function, and staging table. This allows you to operate on the 35-day window partition without excessive data movement and logging that would be required with SPLIT and MERGE of non-empty partitions.

The script below slides the daily window and moves data outside the active window using DELETE...OUTPUT (100K daily rows). The heavy lifting for the data within the active window (about 3.5M rows) is done with CREATE INDEX...WITH(DROP_EXISTING=ON). I would expect this to run well within your 3 hour maintenance window, probably seconds or minutes at worst.

This assumes your table and indexes are aligned. Below is example DDL.

CREATE PARTITION FUNCTION RangePartFunction (datetime2)
    AS RANGE RIGHT FOR VALUES ('20150101', '20160101', '20170101', '20180101');
CREATE PARTITION FUNCTION RangePartFunction_Staging (datetime2)
    AS RANGE RIGHT FOR VALUES ('20150101', '20160101', '20170101', '20180101');
CREATE PARTITION SCHEME RangePartScheme
    AS PARTITION RangePartFunction TO ([PRIMARY], Part_2015, Part_2016, Part_2017, Part_2018);
CREATE PARTITION SCHEME RangePartScheme_Staging
    AS PARTITION RangePartFunction_Staging TO ([PRIMARY], Part_2015, Part_2016, Part_2017, Part_2018);

--partition for last 35 days
DECLARE @Start35DayWindowDate datetime2 = CAST(DATEADD(day, -35, CAST(SYSDATETIME() AS date)) AS datetime2);
ALTER PARTITION SCHEME RangePartScheme
    NEXT USED Part_35Day;
ALTER PARTITION SCHEME RangePartScheme_Staging
    NEXT USED Part_35Day;
ALTER PARTITION FUNCTION RangePartFunction()
    SPLIT RANGE(@Date);
ALTER PARTITION FUNCTION RangePartFunction_Staging()
    SPLIT RANGE(@Date);
GO
--example tables
CREATE TABLE dbo.Table1(
      Col1 datetime2
    , Col2 int
    , Col3 char(1000)
) ON RangePartScheme(Col1);
CREATE CLUSTERED INDEX cdx ON dbo.Table1(Col2, Col1)  ON RangePartScheme(Col1);
CREATE NONCLUSTERED INDEX idx ON dbo.Table1(Col2) ON RangePartScheme(Col1);
CREATE TABLE dbo.Table1_Staging(
      Col1 datetime2
    , Col2 int
    , Col3 char(1000)
) ON RangePartScheme(Col1);
CREATE CLUSTERED INDEX cdx ON dbo.Table1_Staging(Col2, Col1)  ON RangePartScheme_Staging(Col1);
CREATE NONCLUSTERED INDEX idx ON dbo.Table1_Staging(Col2) ON RangePartScheme_Staging(Col1);
GO

Daily partition maintenance script (see inline comments):

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;

    DECLARE @Start35DayWindowDate datetime2  = CAST(DATEADD(day, -35, CAST(SYSDATETIME() AS date)) AS datetime2);
    DECLARE @LastBoundaryDate datetime2 = (
        SELECT MAX(CAST(value AS datetime2))
            FROM  sys.partition_functions AS pf
            JOIN sys.partition_range_values AS prv ON prv.function_id = pf.function_id
            WHERE
                pf.name =   N'RangePartFunction'
        );

    IF @Start35DayWindowDate > @LastBoundaryDate
    BEGIN

        --switch last partition into staging table
        ALTER TABLE dbo.Table1
            SWITCH PARTITION $Partition.RangePartFunction(@LastBoundaryDate)
            TO dbo.Table1_Staging PARTITION $Partition.RangePartFunction_Staging(@LastBoundaryDate);
        --merge original function (with empty partition)
        ALTER PARTITION FUNCTION RangePartFunction()
            MERGE RANGE(@LastBoundaryDate);

        --create new yearly partition when the 35-day window passes the first of the year
        --NOTE: current year filegroup must be created and this script changed with the new filegroup name before February 6th of each year
        IF @LastBoundaryDate = DATEADD(year, DATEDIFF(year, '', @LastBoundaryDate), '')
        BEGIN
            ALTER PARTITION SCHEME RangePartScheme
                NEXT USED Part_2019;
            ALTER PARTITION FUNCTION RangePartFunction()
                SPLIT RANGE(@LastBoundaryDate);
        END;

        --split original function for new 35-day window boundary
        ALTER PARTITION SCHEME RangePartScheme
            NEXT USED Part_35Day;
        ALTER PARTITION FUNCTION RangePartFunction()
            SPLIT RANGE(@Start35DayWindowDate);

        --rebuild staging table using original scheme
        CREATE CLUSTERED INDEX cdx ON dbo.Table1_Staging(Col2, Col1) WITH(DROP_EXISTING=ON) ON RangePartScheme(Col1);
        CREATE NONCLUSTERED INDEX idx ON dbo.Table1_Staging(Col2) WITH(DROP_EXISTING=ON) ON RangePartScheme(Col1);

        --switch partition with new window from staging table back into original table
        ALTER TABLE dbo.Table1_Staging
            SWITCH PARTITION $Partition.RangePartFunction(@Start35DayWindowDate)
            TO dbo.Table1 PARTITION $Partition.RangePartFunction(@Start35DayWindowDate);

        --move remaining rows into original table historical yearly file group (single day older than 35 days)
        DELETE dbo.Table1_Staging
            OUTPUT deleted.* INTO dbo.Table1;

        --apply same changes to staging function, scheme, and table to prepare for next day
        ALTER PARTITION FUNCTION RangePartFunction_Staging()
            MERGE RANGE(@LastBoundaryDate);
        ALTER PARTITION SCHEME RangePartScheme_Staging
            NEXT USED Part_35Day;
        ALTER PARTITION FUNCTION RangePartFunction_Staging()
            SPLIT RANGE(@Start35DayWindowDate);
        IF @LastBoundaryDate = DATEADD(year, DATEDIFF(year, '', @LastBoundaryDate), '')
        BEGIN
            ALTER PARTITION SCHEME RangePartScheme_Staging
                NEXT USED Part_2019;
            ALTER PARTITION FUNCTION RangePartFunction_Staging()
                SPLIT RANGE(@LastBoundaryDate);
        END;
        CREATE CLUSTERED INDEX cdx ON dbo.Table1_Staging(Col2, Col1) WITH(DROP_EXISTING=ON) ON RangePartScheme_Staging(Col1);
        CREATE NONCLUSTERED INDEX idx ON dbo.Table1_Staging(Col2) WITH(DROP_EXISTING=ON) ON RangePartScheme_Staging(Col1);
    END;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO


View foreign key constraints so I can delete a table I have copied a database I was working on so I could change some key elements of its design for my new version.

I want to delete totally some tables from the database however I cannot due to some foreign key constraints.

1) How can I view the foreign key constraints that exist on a table?

2) How can I delete the foreign keys and the table?

I am viewing this doc for 2008R2 but I am not understanding it sys.foreign_keys

And here's how to generate the script @Shark showed for all the tables you want to drop. Let's say you have the following tables:

USE tempdb;
GO

CREATE TABLE dbo.z(z INT PRIMARY KEY); -- we won't delete this one

CREATE TABLE dbo.a
(
 a INT PRIMARY KEY FOREIGN KEY REFERENCES dbo.z(z)
);

CREATE TABLE dbo.b
(
 b INT PRIMARY KEY,
 a INT FOREIGN KEY REFERENCES dbo.a(a)
);

CREATE TABLE dbo.c
(
 c INT PRIMARY KEY,
 b INT FOREIGN KEY REFERENCES dbo.b(b),
 a INT FOREIGN KEY REFERENCES dbo.a(a)
);

-- we won't drop this table either, but we'll need to drop
-- the constraint:

CREATE TABLE dbo.d
(
 d INT,
 c INT FOREIGN KEY REFERENCES dbo.c(c)
);

But we only want to delete a, b, and c.

-- load the tables you want to delete into a table variable:

DECLARE @tables_to_delete TABLE (t NVARCHAR(512));

INSERT @tables_to_delete VALUES('dbo.a'),('dbo.b'),('dbo.c');

DECLARE @sql NVARCHAR(MAX) = N'';

-- build a list of the foreign keys you'll have to drop first:

SELECT @sql += CHAR(13) + CHAR(10) + N'ALTER TABLE '
    + QUOTENAME(OBJECT_SCHEMA_NAME(f.parent_object_id))
    + '.' + QUOTENAME(OBJECT_NAME(f.parent_object_id))
    + ' DROP CONSTRAINT ' + QUOTENAME(f.name) + ';'
FROM sys.foreign_keys AS f
INNER JOIN @tables_to_delete AS t
ON f.referenced_object_id = OBJECT_ID(t.t);

-- then the DROP TABLE commands:

SELECT @sql += CHAR(13) + CHAR(10) + N'DROP TABLE '
    + t + ';'
FROM @tables_to_delete;

PRINT @sql;
-- EXEC sp_executesql @sql;

Result (the constraint names will look different if you run this):

ALTER TABLE [dbo].[b] DROP CONSTRAINT [FK__b__a__2D27B809];
ALTER TABLE [dbo].[c] DROP CONSTRAINT [FK__c__a__30F848ED];
ALTER TABLE [dbo].[c] DROP CONSTRAINT [FK__c__b__300424B4];
ALTER TABLE [dbo].[d] DROP CONSTRAINT [FK__d__c__32E0915F];
DROP TABLE dbo.a;
DROP TABLE dbo.b;
DROP TABLE dbo.c;

When you're happy about the result, uncomment the EXEC line.

(Note, you won't be able to validate the script in its entirety when using PRINT if the script is very large. The script is truncated by Management Studio because it still has an archaic limit to how many characters it will show. The string won't be truncated like this when it gets passed to sp_executesql.)