PostgreSQL 9.6 optimizer uses index even though it's slower I'm new to indexes and plans, so I had to ask for some help.

I have a table where I keep the following attributes:

id text NOT NULL
t timestamp NOT NULL
cost int NOT NULL

My query on the db is :

SELECT AVG(cost)
FROM my_table
WHERE my_to_char(t) = 'Sunday   '
;

The return time of the above query is about 4secs.
So, in order to increase query's performance I created an index on my_to_char(t).

But this proved to have the opposite result. The query now returns within 30 secs.

However, the query planner chooses to use the index rather than go for a sequential scan.

How can the index be slower than seq scan on the first place. Any explanation?

Maybe because there are too many rows with my_to_char(t) = 'Sunday   '?

EDIT - Query Plan

Aggregate  (cost=72841.43..72841.44 rows=1 width=32) (actual time=28383.473..28383.473 rows=1 loops=1)
Output: avg(cost)
->  Bitmap Heap Scan on my_table  (cost=900.60..72732.77 rows=43462 width=4) (actual time=120.778..28091.814 rows=1237954 loops=1)
Output: id, t, cost
Recheck Cond: (my_to_char(my_table.t) = 'Sunday   '::text)
Rows Removed by Index Recheck: 3053757
Heap Blocks: exact=33988 lossy=26432
->  Bitmap Index Scan on btree_date  (cost=0.00..889.74 rows=43462 width=0) (actual time=111.785..111.785 rows=1237954 loops=1)
Planning time: 0.270 ms
Execution time: 28384.284 ms

After adding an expression index to a table, you need to manually ANALYZE the table.  Otherwise PostgreSQL does not have access to the data it needs to make good decisions about the proper usage of that index.  It has to make wild guesses, and obviously those guesses are poor in this case (it thinks it will find 43462 rows, but actually finds 1237954)

Perhaps adding an expression index should automatically trigger an ANALYZE to take place--but currently it does not work that way.


find 16,000 words and replace with ## I need to do a find a replace but rather than find one word to replace with another I have about 16k words, so as an example; table 1 has two words apple,orange - table 2 has the contents of a book, i want to be able to replace all the words apple and orange that exists in table 2 and replace them with ##, is there a query that does this ? my actual table 1 has about 16k words and table 2 has 8 million words. most of the examples on google suggests i type the words to find but that is not possible when i have 16k words I am using mysql workbench and an absolute beginner

(from comment)  table 1 has 16k names in a column - table 2 has emails and text in columns - i want to be able to censor out all the names in table 2 using the names in table one as a reference

It won't be fast, but this may be part of what you need:

SELECT t2.*
    FROM t1
    JOIN t2  ON t2.email LIKE CONCAT('%', t1.name, '%')

That only identifies the rows that should be censored in the email, and only if the alignment of t1.name, etc, is good.  That is, if t1.name is a substring of some other name, you could get some extra hits by this method.

Stick LIMIT 20 on that query and see what the output feels like.

Another approach might be:

FULLTEXT(email, text)  -- in t2

SELECT t2.* FROM t2 WHERE
    MATCH(email, text) AGAINST("all 16000 words from t1");

I have never seen such a big match-against, but perhaps it will work.

You could get a text copy of the list this way:

SET @@group_concat_max_len = 1234567;
SELECT GROUP_CONCAT(name SEPARATOR ' ') FROM t1;

and then copy the string of names into the above query.


changes on multimaster replication and activate GTID I've been reading a lot about the changes I planned to do to our DB landscape
but I need some confirmation or recommendation to be sure the work I'll do goes without causing a disaster.
Here is what we have on all hosts:

debian8
MySQL server version: 5.7.16-log MySQL Community Server (GPL)

Replication scene all anonymous:
Master               Slave

S3 some DB's       > S4 on channel1
S2 one 150GB DB    > S4 on channel2
S2 one 150GB DB    > S5 one only channel

All replications work well and are max a few sec. behind master. The replicated DBs I use to do backups.
The 150GB DB on S2 is HA and heavily accessed, but it's possible to shut down for let's say < 10min. S3 DB's are not that critical but a < 10min downtime is highly appreciated.
The bigger picture is, S2 is an old machine and needs to go off grid. I like to have the 150GB DB on S4 and S5 in circular replication in order to do load balancing, failover and backups. S3 DB's needs to be replicated to one of S4 or S5 to do backups.
While that changes I like to change all replications to GTID. That change to GTID is the point I'm worried about.
Here is how I think I have to do all that:

on S2 take the services off that access the DB

take all S2 - S5 read only  SET @@global.read_only = ON;

show slave status on S4 and S5
Q: what do I have to watch for before I shutdown the server?

Shutdown all servers

change Mysqld section in config file
S2, S3, S4, S5
gtid-mode = ON
S4, S5
log-bin   = mysql-bin
server-id       = xx

Start S3

Start S4 with --skip-slave-start

Start S5 with --skip-slave-start

try to connect with replication user across all machines as needed

S4 and S5 Change master to ... the corresponding machines with
corresponding user, password, channel and
MASTER_AUTO_POSITION = 1;

Start Slave for channel1  on S4

Start Slave for channel2  on S4

watch logfiles

Start Slave for channel1  on S5

if logfiles doesn't show any errors
restart all servers

make the services that use DB's point to new Servers

I hope I got all together. Any recommendations highly appreciated.

OK, for anybody reading this in future.

I did the first step changing all server to use GTID based replication and it worked perfect.

I did it the way as I wrote in the original post.

Right now I have same landscape as before, just using GTID.

This night I'll go to activate circular replication between S4 and S5
and if all goes well shutdown S2

I keep posting for the world after.


Created user can access all databases in PostgreSQL without any grants I must be missing something with regards to setting up PostgreSQL. What I'd like to do is create multiple databases and users that are isolated from each other so that a specific user only has access to the databases I specify. However, from what I can determine, any created user has access to all databases without any specific grants being given.

Here is what I do on an Ubuntu Server 12.04:

apt-get install postgresql
sudo -u postgres createuser -DRSP mike1
(Specifying the password for the new user)
sudo -u postgres createdb data1
psql -h localhost -U mike1 data1
(Specifying the password for the user mike1 to login)

It seems that new user "mike1" has no problem connecting to database "data1" and creating tables etc. And this without running any GRANT command at all (and the owner of "data1" is "postgres" since I didn't specify an owner in step 3). Is this really how it is supposed to work?

What I'd like to do is grant mike1 full access to data1 and then repeat this for more users and databases, making sure that the users only have access to one (or possibly several) databases of my choice.

At the SQL level, every user can indeed connect to a newly created database, until the following SQL command is issued:

REVOKE connect ON DATABASE database_name FROM PUBLIC;

Once done, each user or role that should be able to connect has to be granted explicitly the connect privilege:

GRANT connect ON DATABASE database_name TO rolename;

Edit:
In a multi-tenant scenario, more than just the connect privilege would be removed. For multi-tenancy tips and best practices, you may want to read on the postgresql public wiki:  Shared Database Hosting and Managing rights in PostgreSQL.


Selecting specific table for each subscriber when using merge replication in SQL Server with multiple subscribers I am using merge replication with SQL Server 2017.

I have one publisher, and two subscribers (all in all 3 database servers).

I need to replicate different tables to each subscriber.

Is this possible?

yes, that is possible - you create 3 different publications - and in each publication you add only the respective tables you want for each server.

for example - only 2 tables for server1

The other tables will be in other publications, not this one.

Alternatively, within the merge replication you can set up filters and each subscriber would see only a specific partition as you define it.

This example below comes from this article:

Merge SQL Server replication parameterized row filter issues

add tables to merge replication with parametrized row filters.

Follow the steps in the referenced article to configure merge publication and adding tables to the publication

At Add filter step add below filter.

SELECT <published_columns> FROM [dbo].[UserDetails] WHERE [UserOrg] = HOST_NAME()

Once the publication setup is completed, generate the snapshot for merge replication. After generating the snapshot navigate to publication, right click and click on Properties -> Data Partitions

Click on Add and enter the partition value i.e. ORG1 and click ok. As of now I am adding only one partition ORG1. We can add more based on subscribers. For example, if I have a subscriber with partition ORG2 then we can add partition for ORG2 here and generate a partitioned snapshot which will be applied on specific subscriber.

Now click on generate the selected snapshot now and click OK.

Right click on publication in merge replication, launch SQL Server replication monitor and make sure the partitioned snapshot is also completed.

Now add subscription. Please follow steps in the following article to add subscriber: Merge Replication – What gets replicated and what doesn’t

While adding the subscriber specify the HOST_NAME() value of the subscriber, so that the rows with UserOrg value ORG1 only will be replicated to the subscriber.


Renaming a column in a PostgreSQL database table This is essentially the same question as How do I rename a column in a database table using SQL?.

So, I want to rename a column in a table. This column is part of a set of tables that lives inside a PostgreSQL schema, which is itself in a PostgreSQL database.

However, I also have constraints like foreign keys involving this column.
It is not clear to me whether this command will also take care of constraints. If not, how should I handle this?

Alternatively, I also have this database available as a dump file. Would it make sense to edit the dump file, and if so, how?

You could try it yourself as well, but no: renaming a column won't break your foreign keys.

Try this:

CREATE TABLE a (a_id serial PRIMARY KEY);

CREATE TABLE b (a_id integer REFERENCES a (a_id));

ALTER TABLE a RENAME COLUMN a_id TO id;

and have a look at b's definition.

Imagine the world if it weren't so: every single column renaming (in my experience happening a lot in the first phases of development) would involve modifying constraints here and there.  Wouldn't make much sense, would it?

Doing this in a dump is rather error-prone, I would not do that.


how to use the keyword `VALUES` in an `IN` statement? I am on sql server 2016
select @@version

Microsoft SQL Server 2016 (SP2-CU15) (KB4577775) - 13.0.5850.14 (X64)
Sep 17 2020 22:12:45    Copyright (c) Microsoft Corporation
Enterprise Edition:  Core-based Licensing (64-bit)  on Windows Server
2012 R2 Datacenter 6.3  (Build 9600: ) (Hypervisor)

Inside a simple insert statement in sql server I have the key word values
for example:
BEGIN TRANSACTION T1
SELECT @@TRANCOUNT

INSERT INTO [APCore].[usr].[userBusinessArea]
           ([businessAreaID]
           ,[userID])
     VALUES
           (21, 368),
           (21, 227419) ,
           (21, 99906),
           (21, 140470)

SELECT [1]= @@TRANCOUNT
--COMMIT TRANSACTION T1
--ROLLBACK
SELECT [2]=@@TRANCOUNT

The problem is that before I run the insert I would like to use the same logic with the values in order to find out if any of these records is already in the table.
otherwise you might end up with this:

the way I have done is:
select top 10 * from
[APCore].[usr].[userBusinessArea]
where businessAreaid = 21
 and Userid in
(368,227419,99906,140470)

this is after the insert:

I saw some examples in here: SQL Server IN Operator
Is there a good and worth way to use values in an in statement?

One option is to reference the VALUES clause just as you would if it were materialised.
INSERT INTO [APCore].[usr].[userBusinessArea]
           ([businessAreaID]
           ,[userID])
SELECT
    businessAreaID,
    userID
FROM (
     VALUES
           (21, 368),
           (21, 227419) ,
           (21, 99906),
           (21, 140470)
) v (businessAreaID, userID)
WHERE v.userID NOT IN (368,227419,99906,140470);

See also the examples in the docs
Try it on db<>fiddle


SQL Server Memory Clerk Description Trying to understand different memory clerks reported by sys.dm_os_memory_clerk  however, not able to find description of the each individual memory clerk either on web (google and binged) or in SQL BOL. Some of the clerk can be self explanatory based on the name e.g. MEMORYCLERK_SQLXP (assuming it is for extended proc) or SQLCLR etc.

However, some of the memory clerk confused me for e.g. MEMORYCLERK_SQLGENERAL vs MEMORYCLERK_SQLBUFFERPOOL?  OR SQLUTILITIES.

Is there an online reference that tells description of these memory clerks in SQL Server?

ps. The question is not how memory management works in SQL Server. Slava Oak has good blog posts regarding how memory management works but no details on different clerks.

There are 4 kinds of memory clerks in SQL Server.

Generic
Cache Store
Object Store
User store

memoryclerk_sqlgeneral is a general memory clerk responsible for a variety of non-specific sql activities whereas the memoryclerk_sqlbufferpool is the clerk dedicated solely to the buffer pool, since the buffer pool is a busy process.

Links:

SQLServer-DBA memory

MSDN Memory Clerks


How to make a table name an attribute in PostgreSQL? I have received hundreds of tables in PostgreSQL with identical structure and each containing similar data of a different day, so that the table name is the date of the data.

I would like to Union them all into a single table but without losing that very critical information that is the date of the data. I guess that I should somehow use information_schema.tables and make use of PL/pgsql which I don't know and therefore should learn, but to be honest this seems to be a big effort for a one-shot operation. So, my question would be:
Is there an easy way to achieve this or what would be the best solution?

This can be considerably simpler and faster:
DO
$do$
BEGIN
EXECUTE (
   SELECT E'CREATE TABLE t_new AS\n'
          || string_agg(format('SELECT tableoid::regclass AS source_table, * FROM %s'
                              , c.oid::regclass::text)
                       ,E'\nUNION ALL\n' ORDER BY c.oid::regclass::text)
   FROM   pg_namespace n
   JOIN   pg_class c ON c.relnamespace = n.oid
   WHERE  n.nspname = 'public'  -- your schema here
   AND    c.relkind = 'r'       -- only tables
   AND    c.relname LIKE 't_%'  -- your table pattern here
   );
END
$do$;

Replace * with your desired columns.
Produces and executes a query of this form once:
CREATE TABLE t_new AS
SELECT tableoid::regclass AS source_table, * FROM t_20140707
UNION ALL
SELECT tableoid::regclass AS source_table, * FROM t_20140708
UNION ALL
SELECT tableoid::regclass AS source_table, * FROM t_20140709
...

So you get one new table containing everything plus the source table name.
Explain

A DO statement is a good choice for a one-time operation. You cannot return values from it, but you can create a new table. You may want to use a TEMP TABLE ...

tableoid::regclass works to retrieve the (properly escaped) table name of the source table and is very handy in various situations. More details:
Get all partition names for a table

I build a query in a single meta-query instead of looping, which is almost always faster and cleaner (if possible). Related answer:
Loop through like tables in a schema

Queries on catalog tables are regularly much faster and offer everything Postgres actually has. For instance the system column oid I am using here. Aside from that, there are pros and cons for using the information schema. Mostly it is useful to achieve some portability across major versions and various RDBMS. Portability doesn't seem relevant for a one-time operation and it hardly ever works properly in my experience anyway. More on that:
How to check if a table exists in a given schema


GeomFromText in trigger has error I have this trigger:

DELIMITER $$
CREATE TRIGGER geometrytrigger BEFORE INSERT ON users
FOR EACH ROW BEGIN
  SET NEW.user_geometry=GeomFromText(NEW.user_geometry);
END$$
DELIMITER ;

when I insert a row with this query:

INSERT INTO `users` (`id`, `name`, `email`, `password`, `user_geometry`) VALUES (NULL, 'arash', 'arash@host.com', 'sssssss', 'POINT(-0.23 169.93)');

this error has occurred:

 #1416 - Cannot get geometry object from data you send to the GEOMETRY field

edit:

show create table users;

result:

Table   Create Table
users   CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(127) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
 `email` varchar(127) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
 `password` binary(40) NOT NULL,
 `user_geometry` geometry DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`),
 UNIQUE KEY `email` (`email`),
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Also I use Percona 5.6.12 or MySQL 5.6.12

The error isn't coming from the trigger.   You could confirm this by dropping the trigger and you will see the same error.

ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

You didn't mention it in the question, but it looks like your user_geometry column is defined with a GEOMETRY data type.  To use a GEOMETRY data type, you have to insert a GEOMETRY object.  You can't coerce it later with a trigger because it's validated very early, before the BEFORE UPDATE trigger.

Remove the quotes and add a comma and you have a query that would work and not need the trigger:

INSERT INTO `users` (`id`, `name`, `email`, `password`, `user_geometry`)
VALUES (NULL, 'arash', 'arash@host.com', 'sssssss', POINT(-0.23,169.93));

The POINT() function takes 2 arguments and returns a geometry object.


You want INDEX(device_id, device_time_ms, id).  First the '=' fields, then, if there is nothing else in WHERE, all the fields of the ORDER BY.

Assuming this is a really large table, and there are a relatively small number of devices, the I recommend you normalize device_id.  That would shrink it from VARCHAR(30) to, say 3 bytes for MEDIUMINT UNSIGNED.  Or maybe it is already a number?  But mistakenly put into a varchar?


Find the total number of rentals for each genre The following are the tables in the database (removed irrelevant tables). I want to query for the total count of rentals for each genre. Note that a movie can have multiple genres.

MOVIE:  Table of movies that are available in the video store.
    id:             Integer identifying the movie instance.
    title:          String with the title of the movie.
    duration:       Integer with the total duration of the movie, in seconds.
    release_year:   Integer with the year of release of the movie.
    rental_rate:    Float with the daily cost of renting the movie.
    rating:         Float in the interval 0-10, measuring how much viewers
                    enjoyed the movie.

GENRE:  Table of movie genres.
    id:             Integer identifying the genre instance.
    name:           String with the name of the genre.

RENTAL:  Table of video rentals.
    id:             Integer identifying the rental instance.
    customer_id:    Integer identifying the customer who performed the rental.
    movie_id:       Integer identifying the movie that was rented.
    checkout_date:  Integer with unix timestamp, in seconds, marking when
                    the video was checked out by the customer.
    return_date:    Integer with unix timestamp, in seconds, marking when the
                    video was returned by the customer.
    cost:           Float with total dollar amount that was charged to the
                    customer for the rental.

MOVIE_GENRE:  Association between a movie and its genre.
    movie_id:       Integer identifying the movie instance.
    genre_id:       Integer identifying the genre instance.

What I have managed to do is simply count all the movies in each genre.

SELECT COUNT(mg.genre_id) AS movieCount, g.name
  FROM MOVIE_GENRE mg, GENRE g
  WHERE mg.genre_id = g.id
  GROUP BY mg.genre_id;

I'm doubting that this query even helps in what I'm trying to do. I know how I can achieve this programmatically, but I'm not sure how to express it in SQL.
We need to map each rental to a movie then to its genres, and count the genres we find here. How do I express this in SQL?

I've reduced a bit your table schema:

create table movie
(
    id int primary key,
    title varchar(20)
);

create table genre
(
    id int primary key,
    name varchar(20)
);

create table movie_genre
(
    movie_id int,
    genre_id int,
    primary key (movie_id, genre_id),
    foreign key (movie_id) references movie (id),
    foreign key (genre_id) references genre (id)
);

create table rental
(
    id int primary key,
    movie_id int
);

And then insert some values:

insert into movie values
(1, 'movie 1'), (2, 'movie 2'), (3, 'movie 3'), (4, 'movie 4');

insert into genre values
(1, 'genre 1'), (2, 'genre 2'), (3, 'genre 3'), (4, 'genre 4');

insert into movie_genre values
(1, 2), (1, 3),
(2, 1), (2, 2), (2, 4),
(3, 1), (3, 2), (3, 3), (3, 4),
(4, 2);

insert into rental values
(1, 1), (2, 2), (3, 1), (4, 3), (5, 1), (6, 3), (7, 4);

Now joining all tables and grouping by genre.name you can get the count of each genre:

select   g.name, count(*) rentals
from     rental r
join     movie  m
on       r.movie_id = m.id
join     movie_genre mg
on       mg.movie_id = m.id
join     genre g
on       mg.genre_id = g.id
group by g.name;

name    | rentals
:------ | ------:
genre 1 |       3
genre 2 |       7
genre 3 |       5
genre 4 |       3

dbfiddle here


Installing Enterprise Edition in a virtual machine Out of curiosity, just wondering if this is possible:

I will download a 180-day trial of Enterprise edition of an Microsoft product (ex: SQL Server 2017).
Install it in a virtual machine.
Once the trial has expired, I will reinstall the virtual machine then re-install the SQL Server in it to have another 180-day trial.

Not sure on the background of Developer Edition and its differences against Enterprise Edition. Just hoped that I could explore all the features of SQL Server 2017 even just for home use only.

I just want to have an environment for self studying with full features of the product as much as possible.

Is it possible? Yes.

Is it legal to use in production? No.

What should I do instead?

If it's for production, and your databases are 10GB or less, check out Express Edition. It's free, and has core and memory limitations, but the price is right.
If it's for production, and you absolutely need to run your own VM, you'll need to buy a production license (like Standard or Enterprise.)
If it's for production, and you don't need to run your own VM, you can rent VMs from Amazon EC2, Google Compute Engine, or Microsoft Azure with the SQL Server licensing included. That way you can pay by the hour and change your licensing based on your needs for that particular day.
If it's for development, consider SQL Server Developer Edition. It's free, and it's available as a download, or as a Docker container.

That'll hopefully help keep the licensing mafia off your back. My friends tell me I shouldn't talk about them anymore, though, because they're probably listening right now. (You might ask, am I referring to the licensing mafia listening, or my friends? Both. They're gunning for me.)


How to design a table that each rows have 5K boolean attributes? I have about 2M rows and each row looks like the following.

244 true false ... true

-> One integer column(V) and about 5K boolean columns(B1, B2, ..., B5K) associated to the integer.

Due to the limitation of the maximum number of columns that I can have for a row, I have separated the boolean columns(attributes) in a separate table.

Table V:

idx_V value_V
--------------
1     244

...

Table B:

idx_V idx_B value_B
--------------------
1     1     true
1     2     false
...
1     5K    true
...

This design works alright when I try to find V's that match one boolean column. For example, finding V's where the 2nd boolean attribute is true:

select value_V
where VT.idx_A = BT.idx_A
    and idx_B = 2
    and value_B = true
from V_Table as VT
    and B_Table as BT

But the query becomes awful when I have to find V's that match a multiple boolean columns, sometimes even for all 5K columns, like finding V's with B1=true, B2=false, B3=true, ... and B5K=false.

My primary use of the tables would be the following 2:

Find V's that x1th, x2th and xnth boolean columns are false/true (n can be anything between 1 and 5K)
Sublists:

Find the sequence of the boolean columns for a specific V: T F T T F F ...
Find other V's that match the sequence found in 2-A

I'm thinking about constructing a varchar[5K] field to store the boolean sequence to do 2 but it seems like there's just too much waste in space since each boolean only requires just 1 bit but I'm allocating a byte.

What would be the best way to go about this?

Combine the binary values into one or more integers and use binary arithmetic when computing the actual values. This will generate some decent cpu overhead, but the complexity of the data is massively reduced. i.e.

True True True True = 8+4+2+1 = 15
True False False True = 8+0+0+1 = 9
False True True False = 0+4+2+0 = 6


Asynchronous database inserts from C# always result in a deadlock I'm hoping this doesn't come off as too amateurish, but I'm converting a synchronous program to run asynchronously. So far, I've gotten everything running asynchronously except database inserts.
Essentially, where it fails is that two of the tasks are trying to insert into the same table at the same time, judging by the fact that the exception returned references the line of my stored procedure with the insert statement.

Here is the relevant C# code:

I attempt to spin the input loading function into a different context, which allows the program to continue processing (and which will eventually lead to calling this same function on another list of files, which almost always occurs before the first document is finished loading into the DB)

await Task.Run(() => context.LoadInput(fileList))

My input files are CSV files. I load them into the database using stored procedures that take a user-defined table type as a parameter. I assemble the UDT on the C# side and send it to the database. In the database, the following stored procedure is called:

CREATE PROCEDURE ImportDocument @documentData dbo.DocumentImportStructure READONLY, @newDocumentId INT OUT AS SET NOCOUNT ON;
BEGIN
    BEGIN TRY
    -- Detect duplicate file
    DECLARE @duplicateId AS INT = (SELECT Id FROM dbo.Documents AS d WHERE EXISTS (SELECT Id FROM @documentData as i WHERE i.[File_Name] = d.[File_Name] AND i.File_Size = d.File_Size));
    If(@duplicateId IS NOT NULL)
        If((SELECT [Status] FROM dbo.Documents WHERE Id = @duplicateId) < 2)
            SET @newDocumentId = @duplicateId
        ELSE
            BEGIN
                DECLARE @ErrorMsg AS NVARCHAR(2048) = 'The document "' + (SELECT [File_Name] FROM @documentData) + '" created ' + CAST((SELECT File_CreationDate FROM @documentData) AS NVARCHAR) + ' already exists in the database under file ID ' + CAST(@duplicateId AS nvarchar) + '.';
                THROW 60001, @ErrorMsg, 1;
            END;
    ELSE
        BEGIN
        -- Add new file
        BEGIN TRANSACTION
            INSERT INTO dbo.Documents([File_Name], File_CreationDate, File_Size, MailingType, StatementDate, DueDate, RecordCount, [Status])
                SELECT [File_Name], File_CreationDate, File_Size, MailingType, StatementDate, DueDate, RecordCount, [Status] FROM @documentData;
        SET @newDocumentId = @@identity
        COMMIT
        END;
    RETURN @newDocumentId
    END TRY
    BEGIN CATCH
        DECLARE @state AS INT = XACT_STATE()
        IF(@state = -1)
        BEGIN
            PRINT N'The transaction cannot be committed in its current state. Rolling back transaction.'
            ROLLBACK TRANSACTION;
        END;
        ELSE IF (@state = 1)
        BEGIN
            PRINT N'The the transaction can be committed in its current state. Committing transaction.'
            COMMIT TRANSACTION;
        END;
        THROW;
    END CATCH
END

Line 18 contains the INSERT statement, and it's also where the following exception is thrown soon after all of the documents start simultaneously being written to the database:

$exception  {"Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The transaction cannot be committed in its current state. Rolling back transaction."}   System.Data.SqlClient.SqlException

I could load the data synchronously and just have everything else be asynchronous, but  I feel like there's a way to do this that I'm missing, or an issue I'm overlooking.

Seems like you have issues with your read statement. What is the isolation level on a database?

You can try to get rid of the second read by adding StatusId <2 to the first read statement

(SELECT Id
   FROM dbo.Documents AS d
  WHERE StatusID < 2
   AND EXISTS (SELECT Id FROM @documentData as I
                WHERE i.[File_Name] = d.[File_Name]
                  AND i.File_Size = d.File_Size)
)


Should you design the database before the application code is written? What is the easiest and most efficient way to design a database?  From my perspective, there are a couple of options for an application's data store design:

Design the database as best as you can initially before writing any application code.  This gives you the advantage of having a base data structure to work off of.  The disadvantage of this, in my opinion, is that you will have a lot of changes as application specifics that affect the what/where/how of data changes throughout the application development cycle.
Design the database as the application comes to fruition.  When you need some database objects as you write the application, you develop the database parallel (chronologically) to the application.  The advantages would be less changes to the database structure as I see it.  The disadvantage would be the division of time and development effort between application code and database development.

In your experience, what do you find to be the most productive and efficient method?

In addition to other answers...

Capturing your conceptual model first should define scope and requirements. From this, you can derive your logical and physical data models.

Once this is mostly static, then you have a stable database to build your application against. This is contrary to your first option.

Your second point will end in a messy, unmaintainable ball of mud. The data model will never be fixed: if you didn't design it up front, you won't have time to fix it before shipping. You'll be too busy hacking things together.

Minor changes to the schema, combining or splitting tables, changing relationships, etc. will happen, but in localised "islands," and your model + basic design will be unchanged.


How to find what SQL Jobs are using a specific account as Proxy? We have identified a few servers that are using Proxy accounts inappropriately. Some of these servers have multiple Credentials and Lots of Jobs.  Manually checking the Job Property GUI for "Run As" on each step is not desired.

How can I quickly identify which if any, jobs have steps that are using Proxy Credentials we have identified as inappropriate?

I want to see, the account related to the Proxy. As well as the Job Name and Step the Proxy is used on.

SQL 2008+

The servers I was looking at had only few credentials. While they had several jobs only a handful were using “Run As” Proxy credentials.  This is the solution I used.

First query shows the account (credential identity) linked to the proxy
Second Query shows what Job and Step is using a Proxy

    -- Search Credentials (shows account for Name)

    use msdb
    select *
    from sys.credentials

    --Search Jobs where there is a 'Run As' proxy and get the name of that proxy

    use msdb

    select  sysjobsteps.job_id
    , sysjobs.name as 'JobName'
    , sysjobsteps.step_id
    , sysjobsteps.step_name
    , sysjobsteps.subsystem
    , sysjobsteps.last_run_date
    , sysjobsteps.proxy_id
    --, sysjobsteps.step_uid
    , sysproxies.name as 'ProxyName'

    from sysjobsteps
    left join dbo.sysproxies
     on sysjobsteps.proxy_id = sysproxies.proxy_id
    left join dbo.sysjobs
     on sysjobsteps.job_id = sysjobs.job_id

    where sysjobsteps.proxy_id > 0

I tried a couple of ways of joining sys.credentials to dbo.sysproxies. If there was more than one credential it did not work well. Separate quires met my needs so I did not dwell on the join.


Is it possible to reduce a group to a row based on some criteria? I'd like to perform a select on a table involving a GROUP BY such that all rows that share the same set of identifiers are grouped together, but I want to reduce the group to one of the grouped rows based on some criteria. For example, the maximum date_added. However, there are other fields of data that could be different among the grouped rows. I want all of those columns to resolve to the row with the max date_added as well.

I realize to get the max date_added I could simply SELECT MAX(date_added), but that is just a column-level aggregate function. Is there any way I can resolve the entire row in a group?

Conceptually, if you imagine each group as a separate table, I want to SELECT * WHERE date_added=(SELECT MAX(date_added)) from that group table.

The traditional solution, the one you may find in books, is to do a self join: first find that "max date per group" of yours, then join to self table on rows with said max date.

However, some hacks allow you to avoid that. Consider the following query:

SELECT
  MAX(date_added) AS date_added,
  SUBSTRING_INDEX(GROUP_CONCAT(some_column ORDER BY date_added DESC), ',', 1) AS some_column,
  SUBSTRING_INDEX(GROUP_CONCAT(another_column ORDER BY date_added DESC), ',', 1) AS another_column
FROM t
GROUP BY whatever

GROUP_CONCAT is an aggregation function which implodes values onto one string. It allows for ORDER BY, which we utilize via ORDER BY date_added DESC so as to implode our desired value first. We then slice up the first token in the string via SUBSTRING_INDEX.

The downside here (apart from making the query quite the frightening appearance) is that your numerical values are transformed into texts. Typically no big deal with SQL, but please be aware.

See also my old post: Selecting a specific non aggregated column data in GROUP BY

There's another option where you do a semi-self-join, a much lighter one; you will have to give up usage of index. It's quite long to describe; it still uses GROUP_CONCAT and SUBSTRING_INDEX, but only for the purpose of creating a derived table with only relevant keys. This derived table is then joined with original table. See an example in SQL: selecting top N records per group, another solution.


MSX Job deployment: Not creating job on certain servers I am deploying a new index maintenance jobs through MSX to about 100 servers, I am in the process of manually checking that each job has been created and I have already discovered that for some currently unknown reason specific servers are not receiving the new MSX job.

Strangely, these servers have been able to successfully disable an older index maintenance job that already exists.

I am wondering if someone can help me identify the reason for this.

On my CMS I have the Server Agent assigned the TargetServerRole in MSDB.

I have noticed in my agent error log, the above information.

Edit: I have since identified that the servers needed resynchronisation, I defected the target (screen capturing existing MSX jobs before doing so), and then rejoined to the master and re-added the MSX jobs - this seemed to resolve the issue.

I solved this issue myself by completing the following tasks:

In some cases, even though the server existed in the list of registered servers, it was giving me an error that the server wasn't registered, if this occurred, I deleted the server and then re-added it.
I defected the server and then enlisted it again as a target, but before doing so I would make a record of the existing MSX jobs on the target. After defecting the target loses its MSX jobs, so it was then necessary to re-add them.
During the enlistment, SQL Server made modifications to the service account that runs the target jobs, and made other changes. This process resolved the issues mentioned above.

There is also the ability to resynchronise jobs on the target servers, but this did not assist in my case.


What is the importance of customized data spaces in SQL Server and what are the quantitative measures that can be used to efficiently design this? I am curious as to what are the advantages of creating custom table spaces for Microsoft SQL Server 2008. As of now, and based on my limited experience all I can really tell/assume are the following

Creating custom table spaces organizes the physical files of the database therefore it might help improve the speed of disk reads
Disk reads might also improve if your table spaces are partitioned.
You will be able to clearly segregate database objects into separate physical locations (tables/clustered indexes/etc) Although I am not sure how this exactly works and how this might help improve the performance of the database

Now I'm not really sure if these assumptions of mine are correct and if so, what are the other advantages aside from what I have mentioned?

On the other hand, are there any quantitative measures that I can use in order to design my custom table spaces? For example, is there any mathematical formulas that I can use in order to set the growth size of table spaces according to the table's number of columns? Or is it more of predicting growth based on a timeline of gathering statistics of the table's usage?

One last thing, Is it much more efficient to segregate table spaces according to database object type? or according to logical design? (e.g. create separate table spaces for indexes and tables or create separate table spaces for each business module/function)

Please take note that I still have little experience with this and feel free to ask for any clarification if my question/s are not understandable

Edit: So I've read a bit about the mdf and ldf files and it seems to slightly address my problem with the growth of the database.

source: http://www.simple-talk.com/content/article.aspx?article=920

but still, Is there any importance of segregating per table space?

Edit 2: Okay based on further reading, if I am not mistaken, this basically falls on the category of segregating into different files/filegroups.. What advantages can I also expect from this aside from having to control the growth of the different mdf/ldf files, instead of maintaining just one big file?

A tablespace creates a layer of abstraction between physical and logical data

By using tablespaces, an administrator can control the disk layout of an installation. A common use of tablespaces is to optimize performance. For example, a heavily used index can be placed on a fast disk. On the other hand, a database table which contains archived data that is rarely accessed could be stored on a less expensive but slower disk.


SQL Server Configuration on Multiple Instance Server I have a 2016 Standard edition server with two instances. The server has 8 logical processors and 40GB memory (it's a testing server on hyper-v so the spec isn't high!) and it doesn't have any existing workload to profile.

Does the advice around configuration of MAXDOP and TempDb files still stand true on a multiple instance server?

My thoughts were to split the server resources and apply the configuration based on those.

e.g. each instance would have

16GB MAX memory
MAXDOP 4
4 TempDB files

Resource governor is your best best if you really need to restrict resources for each instance, although if that is your major concern, I would just avoid stacking multiple instances.

If splitting resources isn't a big concern, I would configure total memory no more then 36GB, so 16GB per instance is fine. I would still configure 8 tempdb files for each instance, and would probably set max dop to around 6, to be tested against the work load.

This makes sure neither instance, or the OS is unable to get the memory it needs, but by configuring the maxdop higher then half the cores, it will allow processes to use more then 4 cores if available, while still not allowing a single process to dominate the entire machine.

I would still allocate 8 tempdb files to the instance, because without something in place to provide a hard restriction, sql server will still be able to access all 8 cores, and could potentially generate 8 simultaneous tempdb requests.

One other thing I would verify, is that if this is running on sql server standard edition, that your VM is configured with no more then 4 sockets regardless of the number of cores.  Sql server will restrict the cpu usuage to 4 sockets, and if you use single core cpus, you could wind up with both instances trying to share the first 4 CPU's, while the other 4 are left idle.


Postgresql update slow on a relative small/medium table I'm trying to run an update on a table with 10 million rows (I just need to update a few), but the query is taking more time than I would like.
This is the table deinifition:
CREATE TABLE det_price_list
    (
      price_list_id     BIGINT NOT NULL
        CONSTRAINT fk_det_list_precio_venta_2
        REFERENCES price_list
        ON UPDATE RESTRICT ON DELETE RESTRICT,

      det_price_list_id BIGINT NOT NULL,
      product_id        VARCHAR(25),
      account_id        BIGINT,
      price             NUMERIC(22, 8),
      currency_id       BIGINT,
      last_updated      TIMESTAMP,

      CONSTRAINT pk_det_price_list
      PRIMARY KEY (price_list_id, det_price_list_id),
      CONSTRAINT fk_det_list_precio_venta_1
      FOREIGN KEY (product_id, account_id) REFERENCES product
      ON UPDATE RESTRICT ON DELETE RESTRICT,
      CONSTRAINT fk_det_price_list_4
      FOREIGN KEY (currency_id, account_id) REFERENCES currency
    );

With this index:
CREATE UNIQUE INDEX indice_det_lista_item
  ON det_price_list (product_id, price_list_id);

CREATE INDEX ix_det_price_list_5
  ON det_price_list (price_list_id, account_id, product_id);

CREATE INDEX ix_det_price_list_1
  ON det_price_list (price_list_id);

CREATE INDEX ix_det_price_list_2
  ON det_price_list (product_id);

CREATE INDEX ix_det_price_list_6
  ON det_price_list (account_id, product_id, price_list_id, last_updated);

CREATE INDEX ix_det_price_list_3
  ON det_price_list (account_id);

CREATE INDEX ix_det_price_list_4
  ON det_price_list (last_updated);

CREATE INDEX det_price_list_account_id_price_list_id_currency_id_i
  ON det_price_list (account_id, price_list_id, currency_id);

And this is the query:
update det_price_list d
    set last_updated = current_timestamp
  where d.account_id = 994
  AND   price_list_id = 4338
  and d.currency_id= 2;

Also here is the query plan using EXPLAIN (ANALYZE, BUFFERS):
QUERY PLAN
Update on det_price_list d  (cost=0.56..219.89 rows=60 width=77) (actual time=1806.823..1806.823 rows=0 loops=1)
  Buffers: shared hit=1040857 read=1704 dirtied=8839
  I/O Timings: read=1.814
  ->  Index Scan using det_price_list_account_id_price_list_id_currency_id_i  on det_price_list d  (cost=0.56..219.89 rows=60 width=77) (actual time=0.034..28.386 rows=26776 loops=1)
        Index Cond: ((account_id  = 994) AND (price_list_id  = 4338) AND (currency_id = 2))
        Buffers: shared hit=2740 dirtied=621
Planning time: 0.157 ms
Execution time: 1806.873 ms

The query is using the index. I've already tried to vacumm, vacumm full, reindex and analyze but i can't reduce that time. I can't copy into another table and drop the old one because it is not a complete update and also the table is used constantly.

Instead of updating hundreds of row at once, which will always take sometime with that much rows
make an UPDATE TRIGGER, which automatically updates the timestamp for every row, whrn an update occurs.
CREATE OR REPLACE FUNCTION update_changetimestamp_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.last_updated = now();
   RETURN NEW;
END;

AND
$$ language 'plpgsql';
CREATE TRIGGER update_det_price_list_timestamp BEFORE UPDATE
ON ab FOR EACH ROW EXECUTE PROCEDURE
update_changetimestamp_column();


Issues Setting Up SQL Server - Multi Server environment I am trying to Configure the Master target server / Multi server environment in Sql Server 2012.
I changed :

MSXENCryptChannnelOptions-->Changed from 2 to 0
AllowDownloadedJobsToMatchProxyName - changed from 0 to 1 on the target

When I run the wizard I am getting below error

MSX Enlist failed for Job Server 'MasterServerName'
The enlist operation Failed(Reason:SQL Server Agent Error: Unable to connect to MSX 'MasterServerName'(Microsoft Sql Server, Error : 22026)

They both servers SQL Agents are running on the same windows service account.
Any Suggestions on how to fix this?
Adding the Log:
Enlist TSX Progress

Create MSXOperator (Success)
Checking for an existing MSXOperator.

Updating existing MSXOperator.
Successfully updated MSXOperator.

Make sure the Agent service for 'Test3' is running (Success)
The service 'SQLSERVERAGENT' is running.

Ensure the agent startup account for 'Test4' has rights to login as a target server (Success)

Checking to see if the startup account for 'Test4' already exists.
Login exists on server.
Checking to see if login has rights to msdb.
Login has rights to msdb.
Checking to see if user is a member of the TargetServersRole.
User is a member of the TargetServersRole.

Enlist 'Test4' into 'Test3' (Error)

Enlisting target server 'Test4' with master server 'Test3'.
Using new enlistment method.
Messages
MSX enlist failed for JobServer 'Test4'.  (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The enlist operation failed (reason: SQLServerAgent Error: Unable to connect to MSX 'TEST3'.) (Microsoft SQL Server, Error: 22026)

It is an old post and I assume you've fixed the issue by now but for people having same problem;

Check the Sql server configuration manager and make sure the TCP/IP protocol is enabled for master and target servers.


Oracle Full Client / Database Client package locations I need to install multiple versions of the Oracle client on an app server for an automation project. The platform I'm looking for in specific is Linux, 64 bit.

Oracle's network of websites are a maze and not helpful at all.

Here is what I've found:

12.1.0.1 : http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-linux-download-1959253.html

11.2.0.1 : http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linx8664soft-100572.html

11.2.0.4 : ??? Who knows, but luckily we have a copy on our repo servers at work.

I need to find the package for 11.2.0.2.

The reason I'm not just using a 12c client for everything (as much as I would like to) is because I can't. This annoying error greets you trying to use expdp a 11.2.0.2 database using the 12c client:

  Export: Release 12.1.0.2.0 - Production on Tue Feb 24 14:14:39 2015

  Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights
  reserved. UDE-00018: Data Pump client is incompatible with database
  version 11.2.0.2.0

So much for backwards compatibility.

P.S., the source database is a production 11.2.0.2 database that will not be upgraded for at least a year.

When it comes to software downloads for production use, the Oracle sites, thankfully, are a little less of a maze - provided that you are familiar with the entrance.

You can find the latest media packs for production use on the Oracle Software Delivery Cloud.

After logging in and accepting terms and export restrictions, select Oracle Database and Linux x86-64. After hitting Go, you should be able to find Oracle Database 11g Release 2 (11.2.0) Media Pack for Linux x86-64 and within; the Oracle Database 11g Release 2 Client (11.2.0.1.0) for Linux x86-64.

You can find Oracle Database 12c Release 1 (12.1.0.2.0) Media Pack for Linux x86-64 and the associated Oracle Database 12c Release 1 Client (12.1.0.2.0) for Linux x86-64 (64-bit) in much the same way.

The patch from 11.2.0.1.0 to 11.2.0.2.0 should be downloaded from Metalink / My Oracle Support.

Starting with 11.2.0.2.0, any subsequent updates can be downloaded from with the installer itself, either as an integrated part of the installation process, or in advance (useful when destined server is restricted from accessing the Internet) by running the installer with the option -downloadUpdates.

I guess you have already considered just running the datapump tools from the actual Oracle home of the database in question?

These days, you may also be offered to try out a new and enhanced version of the software delivery cloud. Look for a big link on the landing page/login page.

On a final note, lots of additional information can be found in the Client Installation Guides available in the Oracle Database Online Documentation 11g Release 2 (11.2) or Oracle Database Online Documentation 12c Release 1 (12.1).


Does MySQL have information_schema.VIEW_COLUMN_USAGE or an equivalent? SQL Server has a an information_schema view, 'VIEW_COLUMN_USAGE', which you can use to determine the actual/base tables that the columns in a view reside in.

I'm looking for the equivalent in MySQL - the only view-related information_schema item i can see is VIEWS which has no column info.

Is the only way to find the tables that the columns in a view belongs to to parse the view definition?  This seems rather barbaric, and maybe not possible if one doesn't control the SQL being used to define views.

Thanks for your time!

Sorry - the answer is: no.
Indeed what you found is the only thing in I_S regarding views.

Not only is this barbaric on the client side, it is also such on the server side: when you:

SELECT ... FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='your_view'

MySQL actually drills down to referenced table definitions and this type of query is very costly if there are many nested views (I have a particular case on my servers).


Why is my MERGE query deadlocking while in a serialized isolation level transaction? I'm trying to avoid deadlock in with my MERGE query, which may be called by different threads and possibly overlap in execution with the same parameters.  My experience with this query is very similar to the scenario described in this question, and I've listed the query below for reference.

CREATE PROCEDURE MergeIt
    @dataToMerge MyTableType READONLY
AS
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION

        MERGE INTO TargetTable WITH(HOLDLOCK) AS [target]
        USING @dataToMerge AS [source]
        ON [source].KeyPart_1 = [target].KeyPart_1 AND
           [source].KeyPart_2 = [target].KeyPart_2
        WHEN NOT MATCHED THEN
            INSERT(Data, KeyPart_1, KeyPart_2)
            VALUES([source].Data, [source].KeyPart_1, [source].KeyPart_2)
        WHEN MATCHED THEN
            UPDATE SET [target].Data = [source].Data,
                       [target].KeyPart_1 = [source].KeyPart_1,
                       [target].KeyPart_2 = [source].KeyPart_2;

    COMMIT TRANSACTION
RETURN 0

TargetTable has an identity column which serves as the primary key, and furthermore has a uniqueness constraint on the [KeyPart_1, KeyPart_2] column-tuple.  MyTableType has a schema similar to TargetTable and also defines a primary key on [KeyPart_1, KeyPart_2] column-tuple.

I am trying to ensure that only one process be allowed to run this MERGE query at any given time, and I thought that the SERIALIZABLE isolation level enforces this.  However, this doesn't appear to be the case.  I've captured these XML log events that show which resources and locks are in play during the deadlock.  One query has an exclusive lock (X), and the other an update lock (U).  As I'm typing this, I see that it is not necessary to update the [KeyPart_1, KeyPart_2] column-tuple in the UPDATE clause, which might very well be contributing to the deadlock since that tuple will trigger an index update.

Are there any other suggestions as to how I can resolve this?  I suppose I can blindly try to use TABLOCKX as the table hint, but I'd like to understand how the SERIALIZABLE isolation level failed here.

Thanks!

If you want to ensure that only one process is allowed to run this MERGE query (stored procedure) at any given time, then
sp_getapplock
is a good fit. It is very straight-forward, easy to understand and maintain, as opposed to obscure query hints. I'm not saying that it is impossible to achieve the same effect with hints. It is easier for me to understand the simple mutex.

This is a template of a stored procedure that I use. Adjust the timeout as needed. The caller should be aware of possible timeouts and retry if needed.

CREATE PROCEDURE MergeIt
    @dataToMerge MyTableType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION
    BEGIN TRY

        DECLARE @VarLockResult int;
        EXEC @VarLockResult = sp_getapplock
            @Resource = 'MergeIt_app_lock',
            @LockMode = 'Exclusive',
            @LockOwner = 'Transaction',
            @LockTimeout = 60000,
            @DbPrincipal = 'public';

        IF @VarLockResult >= 0
        BEGIN
            -- Acquired the lock
            MERGE INTO TargetTable WITH(HOLDLOCK) AS [target]
            USING @dataToMerge AS [source]
            ON [source].KeyPart_1 = [target].KeyPart_1 AND
               [source].KeyPart_2 = [target].KeyPart_2
            WHEN NOT MATCHED THEN
                INSERT(Data, KeyPart_1, KeyPart_2)
                VALUES([source].Data, [source].KeyPart_1, [source].KeyPart_2)
            WHEN MATCHED THEN
                UPDATE SET [target].Data = [source].Data
            ;

        END ELSE BEGIN
            -- timeout waiting for the lock
            -- TODO: handle the problem, e.g. return some error code,
            -- indicating that the caller should retry.
        END;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        -- TODO: handle the problem. Return some error code?
    END CATCH;

    RETURN <the error code>
END


Cassandra cluster monitoring We using Nagios for our servers monitoring.

Each web-environment have 1 cluster as backend - Cassanra 2.0. Each cluster have 3-5 nodes.

Question is: I want write a plugin for Nagios, for Cassandra's nodes monitoring.
Unfortunately - I'm not so good familiar with Cassandra, and don't sure - what parameters need exactly to be checked?

I'm planing use Nodetool utility to grab data from nodes - but it have a lot of commands, and each provides lot of information (e.g. - cfstats, info, status etc).

So - for monitoring I need get some data about memory each node's usage, used disk space, may be - something else?

You can use check Java Heap memory for each node. Like Total Java Heap Memory and Using Java Heap Memory.
One most important is CPU Utilization for each node.
Set alerts for errors. system.log have lots of information about.
You may set alerts for data disk and log disk.
Heartbeat check of servers like if you dont receive in few min it will get alerts to you.
Also dropped mutations and hinted hindoff clear alerts.

Basically you need to start observe system.log and will get more and more error for monitoring.


Is there DMV for seeing view access count? I saw it once during a presentation when someone showed a query to get a count of how many times views have queried in a SQL server. I don't remember if it was from DMV or a combination of some other statistics, but I clearly remember when they ran a query that selects from a view and after it would show that count going up by one. Another interesting fact that I recall about the presentation was selecting from a CTE increased the count by two because SQL Server has to create a "temporary view" and later select from it.

Does anyone know how this can be demonstrated?

No, but what the presenter was probably doing was using Grant Fritchey's technique to search the plan cache for a string. You can search the plan cache for your view name.

That technique has a few drawbacks. It's very slow on a busy/large production server - it doesn't hold folks back by blocking, but it can just take a really long time to search, say, 10-100GB of query plans looking for a string. Also, it only searches the literal plan - if you have a view calling another view, the nested view's name may not show up in the query or the plan.


Can't connect to the DB2 database on localhost, using DBeaver 3.1.1 Solved: I was connecting to the wrong port, according to /etc/services | grep db2

I have a DB2 Express C v.10.5 database running on localhost, but simply I can't connect to it.

Translation of the error message below:
"java.net.ConnectException: An error occured when opening the socket to the server ... on port 50000 with message: Connection refused"

Driver properties:

I am using DBeaver 3.1.1, Ubuntu 14.04 LTS 64 bit.

Can somebody please help?  :|

First make sure db2 is started:

#> db2 connect to <db>

If that's not ok, use db2start. Next what port is db2 listening to?

#> cat /etc/services | grep db2

If it's not 50000 change your connections string in DBeaver. Next try to telnet to the port (assuming it is 50000):

#> telnet localhost 50000

If that's fine (saying something like Escape charater is ^]), check if DB2COMM is set:

#> db2set | grep DB2COMM

It should say DB2COMM=TCPIP

Is it a firewall issue? Check with:

#> sudo iptables -L


Performance impact of including result column in indexed columns I have a database (in MySQL, using InnoDB) that I am using for texture identification in a 3D image. In it, there will be a table of texels(texture pixels) with hue included, in the form of:

texelid INT (PK AI NN)
texelx TINYINT (NN)
texely TINYINT (NN)
texelhue INT (NN)
identifiedtex INT (NN)

Identifiedtex is a foreign key in a 1:N relation of one identifiedtex to many texels(texture elements)

If I create an index(B-tree) using texelx, texely, and texelhue then I can quickly find a row. However, if I add identifiedtex as the last column indexed in the index, will this speed up a lookup of the texture since the result is part of the index? Just as a note, a combination of texelx, texely, and texelhue will result in multiple identifiedtex's, and a few different rows will need to be looked up to identify the texture.

I plan to identify textures by using an inner join against the same table(self join) in order to find rows that have a matching X, matching Y, and matching hue, and grouping by distinct identifiedtex's.

Based on the information given, I will take a shot at an answer...

You stated

  If I add identifiedtex as the last column indexed in the index, will this speed up a lookup of the texture since the result is part of the index?

Let me say that you have the following query:

SELECT identifiedtex FROM texels
WHERE texelx = 210938
AND texely = 378432
AND texelhue = 23;

What would be the effect of having an index (call it Index3)

texelx, texely, texelhue

versus this index (call it Index4)

texelx, texely, texelhue, identifiedtex

When you retrieve identifiedtex using only Index3, it will require an additional table lookup to ascertain the identifiedtex from the table.

When you retrieve identifiedtex using only Index4, it will an index-only lookup because all mentioned columns are in the index. It is known as a covering index.

I mentioned covering indexes in my past posts:

Mar 12, 2012 : How to index this table (a_level, b_level, item_id)
Oct 17, 2012 : Combining columns in index
Nov 13, 2012 : Must an index cover all selected columns for it to be used for ORDER BY? (See My Conclusion)

So to answer your original question, yes adding identifiedtex will index make for a faster query.


Use variables within a function with INSERT INTO I want to use a variable within the function so i can grab systemstate from several clients and put them into a local table.
It looks like this but now i am stuck with making a part variable

    CREATE OR REPLACE FUNCTION schema_test.sync_systemstate(table_name TEXT, dbname TEXT, port int, hostname TEXT, username TEXT, wachtwoord TEXT) returns integer as $$

    DECLARE
    sql varchar;

    BEGIN

    sql :='truncate schema_test.' || table_name || '' ;

    execute sql;

    INSERT INTO schema_test.[variable table name]
    SELECT timestamp, component_name, component_type, status, message ...;

    RETURN port;
    END;
    $$ LANGUAGE plpgsql;

The INSERT INTO schema_test.[variable] is not working, well at least i cant make it working. Any suggestions on how to achieve that behind the schem_test comes the variable table name??

This is a bad idea. You're creating a MATERIALIZED VIEW, just use the native functionality for that.

CREATE MATERIALIZED VIEW schema_test
  AS SELECT timestamp, component_name, component_type, status, message;

Then simply run REFRESH MATERIALIZED VIEWschema_test; it will clear out the old results and insert the new results of the query.


Find MySQL InnoDB index corruption I have large MySQL InnoDB table on my localhost. In this table primary key is id, unique index is set on fields a, b, c.

When I run query:

SELECT * FROM mytable WHERE a = 1 AND b = 2 AND c = 3;

the result is empty set, but such row certainly exists in the table.
When I add " AND id = 1234" at the end (where 1234 is id of expected result):

SELECT * FROM mytable WHERE a = 1 AND b = 2 AND c = 3 AND id = 1234;

it returns 1 row, as expected.

The only explanation is that unique index is corrupted.

But when I tried to check table with:

mysqlcheck -c mydb mytable

or with:

ANALYZE table mydb.mytable;

both checks returned OK with no errors.

So the question is:

How to identify this error in index? Is there a way to find this error with some query?

MySQL version is 5.7.23, in error.log no errrors.

Edit:

It turned out, that after creating empty table mytable1 with same structure and filling it with data by query:

INSERT INTO mytable1 (SELECT * FROM mytable);

this error is present in mytable1, which is impossible in case of index curruption.

After some research I have found, that the trouble is in field c, which has timestamp type.

Actual table structure is:

CREATE TABLE `mytable` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(10) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  `c` timestamp NOT NULL DEFAULT '1999-01-01 00:00:00',
  `d` tinyint(1) NOT NULL DEFAULT '0',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index2` (`a`,`b`,`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

There are screenshots of mysql queries:

Third query differs from first by 'LIKE' instead of '='

I don't know the reason why first query returned empty set, it looks like some bug in MySQL related with timestamp field.

I run query, which fixed this error:

UPDATE mytable SET c = FROM_UNIXTIME(UNIX_TIMESTAMP(c));

After this update the problem query began work fine. But actually, this update must do nothing.

Edit 2

I have found, that the cause of this problem is in clock change in Russia on October 26, 2014.

Because my local setting were:

mysql> select @@global.time_zone, @@session.time_zone;
SYSTEM | SYSTEM

so on my localhost datetime '2014-10-26 01:00:00' had two timestamps:

mysql> select from_unixtime(1414270800);
2014-10-26 01:00:00

mysql> select from_unixtime(1414274400);
2014-10-26 01:00:00

So select by value returned empty set:

mysql> select * from test_db.mytable where c = '2014-10-26 01:00:00';
Empty set

but select by id returned correct data:

mysql> select c from test_db.mytable where id = 316572297;
2014-10-26 01:00:00

I have changed default-time-zone in my.cnf to '+03:00', and everything began work fine.

Note: to reproduce error you must also have index on field c. Without it select by value c = ... works fine.

On 26 October 2014, following another change in the law, the clocks in most of the country were moved back one hour, but summer Daylight Time was not reintroduced; Moscow Time returned to UTC+03:00 permanently.

-- https://en.wikipedia.org/wiki/Time_in_Russia

It sounds like your system (OS and/or MySQL) need an update to the TZ tables.

Down with DST!


Is dbo the default schema when querying if it is not specified? If I query a table which exists in two different schemas but has the same name what determines which table is used?

For example I have table Bar which exists in both dbo and foo, if I execute the statement below which one is returned:

SELECT * FROM Bar

I thought this was determined by the users default schema but doesn't appear to be the case.

I ask because I have found queries written by someone else who is not specifying the schema name yet the database being queried has the same tables in different schemas for ETL.

According to the Books Online:

  The value of DEFAULT_SCHEMA is ignored if the user is a member of the
  sysadmin fixed server role. All members of the sysadmin fixed server
  role have a default schema of dbo.

So it seems the account used where the default schema is not working is a sysadmin role member. The default schema of the dbo user is always dbo and cannot be changed.

The best practice is generally is to schema-qualify object references to avoid ambiguity and improve performance.


How to efficiently swap a portion of a large table's data? I run an application (price comparison website) where I need to update a pricing table with dozens of millions of rows every day.

Basically, every product feed I download from a merchant results in several million rows to delete from the pricing table (WHERE merchantId = x), and several million fresh rows to insert in place of the old ones.

So far, I used to download all the feeds sequentially, insert all of them in a temporary table, then swap the old table with the new one in a single RENAME TABLE query.

This forced me to update all the merchants at once, hence I'd like to move away from this method, and have the ability to process once merchant at a time, deleting all records WHERE merchantId = x and insert all new records in a sub-second query.

My first thought was to use partitioning by list, one partition per merchant. This would solve half of my problem: I can use TRUNCATE PARTITION to quickly remove all existing prices for a given merchant, but the other half of the problem remains: how to quickly insert the millions of new rows, without downtime and unnecessary locks, and without running with the table partially loaded for several minutes?

So I was thinking: is it possible to create a temporary table with the same structure as the partitioned table, fill it with the new rows, and once it is complete, swap one partition of the pricing table with this temporary table?

Any suggestion to solve this problem would be appreciated, including suggestions that do not involve partitioning. I have thought about the MRG_MYISAM engine as well, but I'm not sure whether it fulfils my needs. On a final note, any storage engine is fine for this pricing table, including non-transactional engines.

After a little bit more digging on the MySQL website, I found out about Exchanging Partitions and Subpartitions with Tables, which seems to be a new MySQL 5.6 feature:

ALTER TABLE x EXCHANGE PARTITION y WITH TABLE z;

It seems to be exactly what I was looking for.

After running some benchmarks on my server, I found out that exchanging a partition with a table is not instant, but still very fast: about 1s per 1,700,000 records in the table being integrated in the partitioned table (the number of records in the partition being taken out does not seem to have any influence on the speed), which is very close to the sub-second swapping I was hoping to get for a table with a few million records.

This is to be compared against inserting the rows directly in the partitioned table, where I get about 27,000 inserts / second.

Hence swapping an already filled up table with a partition is roughly 60x faster (for me, YMMV) than inserting directly into the partitioned table.

Sweet!


Join a 'default' value only when there is no fully matching record I need to be able to join to a 'default' row in a table (righttable) only when there is no matching record for both fields in lefttable.  Below is the raw contents of the tables

#lefttable
--------------------
value  program  race
100    gold     1
100    gold     4
100    gold     5

#righttable
-------------------------
commission  program  race
14          GOLD     1
23          GOLD     NULL

In this example, where the race is 1 I need to pick the first row (commission = 14) but if the race is anything other than 1 I need to pick the NULL row that matches the other field (commission = 23)

The problem I'm having is that my query picks the null record and the matching record when joining,  resulting in duplication...

SELECT #lefttable.race,
       totalvalue = SUM(value),
       commission = SUM(value * (commission * 0.01))
  FROM #lefttable
  LEFT JOIN #righttable  ON #lefttable.program = #righttable.program
                        AND #lefttable.race = ISNULL(#righttable.race, #lefttable.race)
 GROUP BY #lefttable.race;

This results in the below

--------------------
race  totalvalue  commission
1     200         37.00
3     100         23.00
4     100         23.00

The totalvalue is double what it should be (and commission incorrect) because the join has created two rows for race 1 (one for each row in righttable)

Ideally I want to be able to solve this problem entirely within the join clause rather than having to add messy cludges to the select clause, or additional joins, or views, or modify righttable to create a row for each race regardless.

I have tried different ways of constructing the join clause but I can't quite wrap my head around how to say "Give me the row with the null race only if there is no row that matches the race in lefttable" using a sql join.

Is this possible?

You should be able to get it by using an OUTER APPLY join plus TOP 1

select
    *
from
    righttable r
where
    (r.race=1 and r.program = 'gold')
    or
    r.race is null
order by
    case when r.race is null then 1 else 0 end

returns:

commission | program | race
---------: | :------ | ---:
        14 | gold    |    1
        23 | gold    | null

but you just need the first row, that you can get by using a TOP 1 in this way:

select
    l.race,
    totalvalue = sum(value),
    commission = SUM(value * (commission * 0.01))
from
    lefttable l
outer apply
    (select top 1
        commission
     from
         righttable r
     where
         (r.race=l.race and r.program = l.program)
         or
         r.race is null
     order by
         case when r.race is null then 1 else 0 end) r
group by
    l.race;

race | totalvalue | commission
---: | ---------: | :---------
   1 |        100 | 14.00
   4 |        100 | 23.00
   5 |        100 | 23.00

db<>fiddle here


MySQL Workbench - Create new server instance error When attempting to create a new server instance I get an error at the 'Test Settings' step of the creation wizard.

The error:

What check_admin_commands/local
Operation failed: /usr/local/mysql/support-files/mysql.server start is invalid

I'm using MAMP and new to OSX so I'm not sure what the problem is here. I can't find the directory /usr on OSX, should I be changing Workbench's configuration to look elsewhere for mysql.server?

http://code-remind.blogspot.com.au/2011/12/on-macbookpro-10.html

NOTE: Where the author suggests using the path '/Applications/MAMP/bin/stoptMysql.sh' at the end... there's a typo.

Should be '/Applications/MAMP/bin/stopMysql.sh'


Search all table, find PrimaryKeys with Id = int/bigint and enable Identity, autoincrement I have a large list of tables and due to change in middleware platform, the middleware is no longer creating the auto-increment on Add New Record at the app layer, as a result, I need to do this in the DB.

How can I Search all tables, find PrimaryKeys with Id = int/bigint ?

To enable/toggle all tables PrimaryKeys with Id = int/bigint  and turn-on Identity with auto-increment
To disable Identity identity & auto-increment (for some tests, which middle ware is still failing)

Edit:

SET IDENTITY_INSERT gettableWithIdentity ON

INSERT gettableWithIdentity (IdentityColumn, col2, col3, ...)
VALUES (SetAnIdentityValue, col2value, col3value, ...)

SET IDENTITY_INSERT gettableWithIdentity OFF

the script below will list all the primary keys, that have at least one int or bigint in their columns. You can filter further or tailor the query more but it should be a good starting point and hope it helps.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
    , OBJECT_NAME(p.object_id) AS [Table]
    , i.name AS [Index]
    , p.partition_number
    , p.rows AS [Row Count]
    , i.type_desc AS [Index Type]
    ,K.increment_value as IncrementValue
    ,K.last_value as LastValue
    ,K.seed_value as SeedValue
    ,k.is_nullable
    ,k.is_identity
    ,k.is_filestream
    ,k.is_replicated
    ,k.is_not_for_replication
FROM sys.partitions p

INNER JOIN sys.indexes i
        ON p.object_id = i.object_id
       AND p.index_id = i.index_id

INNER JOIN SYS.TABLES S
         ON S.object_id = P.object_id

LEFT OUTER JOIN sys.identity_columns K
             ON P.object_id = K.object_id

WHERE 1=1

  AND EXISTS ( SELECT 1
                    FROM SYS.COLUMNS C
              INNER JOIN sys.types AS t
                         ON c.user_type_id=t.user_type_id
                   WHERE i.object_id = c.object_id
                   AND T.user_type_id IN (127,56)  -- ONLY BIGINT AND INT
             )

  AND I.is_primary_key = 1

  -- AND i.index_id < 2  -- GET ONLY THE CLUSTERED INDEXES - IF EXISTS ANY
                      -- get heaps too

  --AND k.is_identity = 1 -- GET ONLY THE IDENTITY COLUMNS

ORDER BY [Schema], [Table], [Index]


Best way to put commas into large numbers I've started a new job and it involves looking at a bunch of big numbers. Is there an easy way to add commas to an int or decimal field to make it readable?

For example, SQL Server outputs the column on the left, but for my own sanity, I need it to look like the one on the right:

2036150 -> 2,036,150

...or would I have to write some heinous

left(right(vandalized_data),6),3) + ',' + right(left(vandalized_data),6),3)

function?

The perfect thing would be commas in the display grid, then plain integers in the output.

TSQL's FORMAT function can do that.  EG

select format(1232131312342132123.2222,'0,###.##')

outputs

1,232,131,312,342,132,123.22

If you want to use a different separator, you still use , which has a special meaning in .Net format string, and then replace the , with something else.  EG:

select replace(format(123445678.2222,'#,###.##'),',','_')

outputs

123_445_678.22


MySQL Replication Stops, But Still Says it is Running I've got a strange replication problem. For reference I am using MySQL 5.5 with Statement Based Replication. We have a Master with Slaves on site A / network A, and slaves on site B / network B.

Everything on network A is fine. The problem occurs with the databases on site B / network B that are connecting to the master at Site A.

On a few occasions now I have seen replication stop on the databases at site B. If I look at the SLAVE_IO_RUNNING and SLAVE_SQL_RUNNING in SHOW SLAVE STATUS they both say yes. To all extents and purposes everything looks fine to me. However, my monitoring is reporting that the heartbeat event we run is falling behind. It's as if the slave was connected to the master, but not receiving any data.

The various pos values (read_master_log_pos, relay_log_pos, exec_master_log_pos) are all static and not moving. I also checked the relay log on one, and the incoming data just stops at that time.

If I look at both the Master and Slave Databases there are no long running queries that would cause this. Everything appears to run as expected, and as mentioned the slaves at site A are all fine and keep up to date.

The databases in question are all running different queries so it's not a specific query upsetting things.

There is nothing in the error logs for MySQL.

We have simulated a short network failure (although from our monitoring we can't see any network outages around these times) and the databases work as expected. As soon as the network is reconnected they resume replication.

This is fixed by running stop slave; start slave; at which point everything carries on as if nothing had happened.

Has anyone else had a similar problem? or could shed some light on what may be happening. My gut feeleing is there is a very brief network outage, too short for the monitoring to catch, but why this would upset MySQL I don't know.

What catches my attention is the word 'NETWORK'.

Communication between Master and Slave is implemented as bidirectional.

According to the MySQL Documentation on Replication

  Binlog dump thread.  The master creates a thread to send the binary log contents to a slave when the slave connects. This thread can be identified in the output of SHOW PROCESSLIST on the master as the Binlog Dump thread.

  The binary log dump thread acquires a lock on the master's binary log for reading each event that is to be sent to the slave. As soon as the event has been read, the lock is released, even before the event is sent to the slave.

  Slave I/O thread.  When a START SLAVE statement is issued on a slave server, the slave creates an I/O thread, which connects to the master and asks it to send the updates recorded in its binary logs.

  The slave I/O thread reads the updates that the master's Binlog Dump thread sends (see previous item) and copies them to local files that comprise the slave's relay log.

  The state of this thread is shown as Slave_IO_running in the output of SHOW SLAVE STATUS or as Slave_running in the output of SHOW STATUS.

Given this description of the I/O aspect of Replication, what could you look for ???

FIREWALL

The connection between Master and Slave requires that the firewall be open. Unfortunately, I have seen occasions where the firewall was open on the Master and a Slave would connect as usual. The Slave would have the I/O thread show up in the processlist like nothing was wrong. The Master would do the same. All of a sudden, 60 seconds later, the I/O thread disappears from the processlist of the Master, but remains visible on the Slave.

Given that scenario (that I eyewitnessed between two Amazon EC2 servers in two different AZs (Availability Zones)), the solution back then was to check the Security Groups and get port 3306 open in the Slave's AZ.

TIMEOUT

MySQL has settings for timing out network connections

From the MySQL Docs:

rpl_semi_sync_master_timeout : A value in milliseconds that controls how long the master waits on a commit for acknowledgment from a slave before timing out and reverting to asynchronous replication. The default value is 10000 (10 seconds). This variable is available only if the master-side semisynchronous replication plugin is installed.
net_read_timeout : The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.
net_write_timeout : The number of seconds to wait for a block to be written to a connection before aborting the write. See also net_read_timeout.
slave_net_timeout : The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement, and the number of reconnection attempts is limited by the --master-retry-count option. Prior to MySQL 5.7.7, the default was 3600 seconds (one hour). In MySQL 5.7.7 and later the default is 60 seconds (one minute).

CONCERNS

Why talk about the network like this ??? You can be victimized in the form of data drift. Back on Jun 17, 2014, I answered the post I have been tasked with Mysql Master-Master replication?. I briefly mentioned the network as an unsung hero in data drift:

Networking

You must make sure the network traffic is not inundated, your switches are fine, any crossover cabling to bypass the network is not defective, there are no dropped packets,.
In terms of MySQL, the Replication I/O thread is dependent on the network. You must make sure mysqld can regularly heartbeat its master. Perhaps semisync replication could be installed and configured for this.

YOUR ACTUAL QUESTION

You running STOP SLAVE; and START SLAVE does not find the root cause but does indeed solve the problem at hand. How ??? All this does is disconnect both the I/O and SQL Threads and then reconnect from scratch.

You could also have done

STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

which would also do just fine, especially if the SQL thread is busy and you do not want interrupt it.

You will need to check the connection between the Master and Slave for dropped packets.

If your monitoring has the same time granularity as the MySQL timeout values, you have nothing to alert you when it does happen. You would have to poll MySQL more frequently. As an alternative, you could probably create some kind of SNMP setup to monitor MySQL so if SNMP info from does not update in a timely manner you could detect MySQL being down or not responding without ever connecting to MySQL.

My answer may not have fully defined the root cause, but I do have two suggestions:

SUGGESTION #1

Look into your max_allowed_packet setting. Many times in the DBA StackExchange I have affectionately called the MySQL Packet the Silent Killer of DB Connections. The I/O thread is just as much as DB Connection as any other. I would make sure max_allowed_packet is always set to 1073741824 (which is 1G).

SUGGESTION #2

You could manually set the I/O Thread Heartbeat. How ?

According to the MySQL 5.5 Documentation for CHANGE MASTER TO

  MASTER_HEARTBEAT_PERIOD sets the interval in seconds between replication heartbeats. Whenever the master's binary log is updated with an event, the waiting period for the next heartbeat is reset. interval is a decimal value having the range 0 to 4294967 seconds and a resolution in milliseconds; the smallest nonzero value is 0.001. Heartbeats are sent by the master only if there are no unsent events in the binary log file for a period longer than interval.

  Setting interval to 0 disables heartbeats altogether. The default value for interval is equal to the value of slave_net_timeout divided by 2.

  Setting @@global.slave_net_timeout to a value less than that of the current heartbeat interval results in a warning being issued. The effect of issuing RESET SLAVE on the heartbeat interval is to reset it to the default value.

Based on these paragraphs and the default value for slave_net_timeout (60 Seconds), it appears that the I/O thread should heartbeat every 30 seconds. You could change the heartbeat period to 10 seconds like this:

STOP SLAVE;
CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD = 10;
START SLAVE;


Add parent_id dynamically based on another field I have a version table using a field to track order:

id, owner_id, order, version
5, 1, 1, 1.1
6, 1, 4, 1.2
7, 1, 7, 1.2.1
8, 1, 10, 1.3

I want to ALTER TABLE version ADD COLUMN parent_id and have each record reference their parent and then drop the order column. The parent_id is the next lowest in order (which may not be sequential) resulting in:

id, owner_id, parent_id, version
5, 1, NULL, 1.1
6, 1, 5, 1.2
7, 1, 6, 1.2.1
8, 1, 7, 1.3

Can I write a query that will update the parent_id for me? Or must it be done programmatically?

CREATE TEMP TABLE v2
    SELECT *
    FROM version;

UPDATE version v1
SET parent_id = (
    SELECT id
    FROM v2
    WHERE v2.order < v1.order
    ORDER BY v2.order DESC LIMIT 1
);


Handle concurrency on sql server for a booking system I've been banging my head against the wall trying to solve this problem and I couldn't come up with a solution that absolutely guarantees two users can't book an appointment for the same time.

The flow and scenario:

1 - The user enters the system and selects a service, say a haircut, and then clicks "Book".

2 - Upon clicking "Book", the server receives the date and time and the staff the user selected to perform the haircut. Based on this information, the available hours the selected staff has are calculated on the fly and sent to the user, who will see something like: 10:00, 12:00, 13:00, 15:00, for example.

3 - The user, then, selects one of the available hours and clicks "Confirm".

The problem:

Two (or more) users can select the same service and staff to perform that service, and when that happens, all the users will be presented the same available hours.

Now, what happens is that when more than one user selects the same available hour, I need to be able to guarantee 100% that only the first will be able to schedule an appointment, and the other will be faced with an error.

I could not come up with a mechanism that ensures no racing condition will occur.
Note that this is a bit different from transport and theather domains, because I do not have a table with all the available hours to simply flag them.

The closest I've come to a solution is to create an extra table, such as "AppointmentsInProgress", and add the starting date and time and the staff to the table, and make both columns unique.
Then, before proceeding to process the user request, check this table to see if nobody else is currently scheduling for the same date, time, and staff.

However, the performance of this solution is a big concern, and I read that the order of sql commands is not guaranteed, which means if two sqls hit the server with a microsecond of difference, there is no guarantee the first will have precedence. Furthermore, something tells me this doesn't really solve my problem.

Any ideas?

However, the performance of this solution is a big concern, and I read that the order of sql commands is not guaranteed, which means if two sqls hit the server with a microsecond of difference, there is no guarantee the first will have precedence.

I don't quite understand how the order of sql commands will affect performance per this sentence.

  Now, what happens is that when more than one user selects the same available hour, I need to be able to guarantee 100% that only the first will be able to schedule an appointment, and the other will be faced with an error.

Well first is relative, I mean really nobody will know if in a nano second if B hit his after A in regards to your previous SQL.  99.999% of the time it won't happen and if it does happen... well someone is going to get kicked, it really doesn't matter who.

  I could not come up with a mechanism that ensures no racing condition will occur.

Here is one way (fairly simple and easy):

Let say you have a table (Appointment) like:

(Int/Guid) (DateTime2)      (DateTime2)      (int/guid/varchar)
ID         AptDatTime      ReservedOn      ReservedBy
-----------------------------------------------------
1          1/1/2016 08:00

The SQL for someone who starts the reservation would be:
(mixing C# and TSQL for brevity)

Update
  Appointment
Set
  ReservedOn = DateAdd('mi', 5, getdate()),
  ReservedBy = <whateverUserSomething>
Where
  (ReservedOn is Null or ReservedOn > DateAdd('mi', 5, getdate())

(There isn't a way that I am aware of to pull this off with entity-framework natively, you'll have to run the command manually through EF)

Then when the user accepts you do another update

Update
  Appointment
Set
  ReservedOn = DateAdd('yyyy', 1000, getdate()), -- or c# DateTime.Max
Where
  (ReservedOn is Null or ReservedOn > DateAdd('mi', 5, getdate())
  AND (ReservedBy == <whateverUserSomething>)

Actual reservations (can be EF-afied):

Select
  (columns)
From
  Appointment
Where
  (ReservedOn > DateAdd('mi', 5, getdate())

Temporary reservations (can be EF-afied):

Select
  (columns)
From
  Appointment
Where
  (ReservedOn > getdate()
    AND ReservedOn < DateAdd('mi', 5, getdate())

  By which I mean if I hit the db with 500 queries at the same time, will it be able to handle it?

SQL Server is extremely robust, 500 queries is very small.  Unless you're using like Compact DB or Local DB maybe or a server with extremely limited cpu/memory/low IO it shouldn't be a problem.


I want to create store procedure of selecting record of last 3 months from 3 tables CREATE OR REPLACE PROCEDURE data.select_data_last_three_month()
LANGUAGE plpgsql
AS $procedure$
declare
v_time1 timestamp;
Begin

    select to_timestamp(to_char(current_date-94,'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss') into v_time1;

    select q1.*,q2.*,q3.* from
    (select * from public.table1 where time >'v_time1' order by time desc) q1, --'2020-05-25 00:00:00'
    (select * from public.table2 >'v_time1' order by time desc)q2,
    (select * from public.table3 time >'v_time1' order by time desc) q3
    ;

 END;
$procedure$
;

call data.delete_interface_tcp_data_three_month_back()

If I call this procedure it gives this error:

SQL Error [22007]: ERROR: invalid input syntax for type timestamp with time zone: "v_time1"

You have several errors in your code:

don't convert timestamps back and forth between strings, dates and timestamps. A simple assignment is enough.
references to variables shouldn't be enclosed in single quotes. 'v_time' is a string, v_time1 is a reference to the variable.
procedures are not intended to return results, you need to use a function declared as returns table (...)

Putting all that together, your function should look like this:
CREATE OR REPLACE function data.select_data_last_three_month()
  returns table (... your column definitions go here ....)
  LANGUAGE plpgsql
AS
$procedure$
declare
  v_time1 timestamp;
Begin

    v_time := current_date - 94;

    select q1.*,q2.*,q3.*
    from
    (select * from public.table1 where time > v_time1 order by time desc) q1, --'2020-05-25 00:00:00'
    (select * from public.table2 > v_time1 order by time desc)q2,
    (select * from public.table3 time > v_time1 order by time desc) q3
    ;

 END;
$procedure$
;

You don't actually need PL/pgSQL for this. A language sql function would be enough.
Then use it like this:
select *
from data.delete_interface_tcp_data_three_month_back();

But I highly doubt, a cross join (cartesian product) between the three tables is what you want. If the three tables have the same structure, then you probably want a UNION ALL.


Get latest timestamp from column in given table dynamically I want to get last timestmap from a table with the column updated in PostgreSQL.
And I want a function to do that. So I can pass the table name as parameter and the function returns the latest timestamp in column updated.
My table:

Query used:
select max(updated) from mytable;

Probably simplest with regclass as input and an OUT parameter:
CREATE OR REPLACE FUNCTION f_last_update(_tbl regclass, OUT _last_update timestamp)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE 'SELECT max(updated_at) FROM ' || _tbl
   INTO _last_update;
END
$func$;

Call:
SELECT f_last_update('mytable');

Optionally schema-qualified and double-quoted where required:
SELECT f_last_update('"My_odd_ScHeMa"."Unwise table name"');

Safe against SQL injection because regclass is automatically quoted and schema-qualified properly when converted to text.
See:

Table name as a PostgreSQL function parameter
Find tables with columns with empty and NULL values in Postgresql
Check whether empty strings are present in character-type columns


Execution plans behavior after a restart in SQL server I have been thinking about how the execution plans act in SQL SERVER after a restart of service. I know that in each query execution in the cache memory exist a process to parse, compile and optimize the requested query applying execution plans based on statistics and other parameters in SQL. All those execution plans are stored in buffer pool area in the storage engine. Actually, after each execution, those plans are updated to let the SQL SEVER learn itself about new improvements.

Here is my doubt: what happens with the execution plans in cache memory each time that I decided to restart my SQL service? All the improvements in the buffer pool area are stored and reloaded to be available in new executions? All execution plans are reloaded as last versions before the restart, or instead, SQL will need to create new execution plans library in order to keep on improving the executions?

Please, guys if you can explain that behavior I will really appreciate. thanks so much

This is what happens when you restart SQL Server. Correct term will SQL Server has to create new execution plans, choose the 'reasonably good one' and save it in the cache to reuse.

  or instead, SQL will need to create new execution plans library in
  order to keep on improving the executions?

You have some misconception about execution plans in your question.

Plans are not updated after each execution. SQL Server keep tracks of how many time a plan was used, last used, and few other metrics. If you have memory pressure the usage metrics is used to decide which plans need to be removed to free up memory.

  Actually, after each execution, those plans are updated to let the SQL
  learn itself about new improvements.

You can manually remove plans from cache, details here.

There are improvements announced for SQL vnext (2017) to adjust/modify query plan on the fly called Adaptive Query Processing . You can watch a video by Microsoft Product Manager Joe Sack.here and read here.


How DML queries execute in MySQL InnoDB I want to know if I fire query like insert into emp ...
What are the steps involved in query ?

It will go through InnoDB buffer pool?

Thanks for your help.

Here's a broad answer to your broad question.

All DMLs and SELECTs always goes through the buffer_pool in InnoDB.  It is a cache of the blocks that compose the BTrees that compose the data (organized by the necessary PRIMARY KEY) and each secondary INDEX.

For DML (broadly):

Fetch a block, if not already in the buffer pool.
Modify whatever.
Mark the block "dirty".
Rinse and repeat.
Eventually (as a background task), the block will be written back to disk.  Meanwhile, it is in the cache so that other operations can avoid having to fetch it from disk.

Modifying a non-unique index involves the "Change buffer".  That is sort of a "delayed write" for such changes.

SELECTs:

Fetch a block (if not already in buffer pool).
Fetch row(s) out of that block.
Rinse and repeat.

Note: in both DML and SELECT, the discovery of where a row usually involves drilling down a BTree, which involves fetching nodes (blocks) of the BTree.  They, too, are cached (and tend to live a long time in the buffer pool).

If you want more details, start another Question with more specifics.


Single Domain Controller for SQL Server Always On Availability Group I was wondering if SQL Server Always On Availability Group supports a single Domain Controller server as opposed to 2 Domain Controllers in every example I've seen. I ask because I suspect that this might be a problem with my HA installation of SQL Server where I use a single Domain Controller.

Azure DSC Extension. HKLM:\SOFTWARE\Microsoft\PowerShell\3\DSC doesn't exist and
Can't create Windows Server Failover Cluster. Static address was not found on any cluster network

It depends on what your goal is.

Technically, SQL Server will work just fine in a Availability Group or FCI installation with a single domain controller. I have this set up in my lab and it works without issue. In this situation, you will be fine.

Generally, the design diagrams you see are splitting things between sites. So you have a DC and a SQL node in each site. If Site 1 fails, you can fail over to Site 2 and still have authentication still work. If you have only one DC, and the site where the DC is offline, then authentication will fail causing issues for your users.


How does an OLAP cube database differ from an RDBMS? Is a cube database, simply an RDBMS—such as PostgreSQL or MySQL—configured for OLAP via a star or snowflake schema? Or is a cube database different from an RDBMS, and if so how?

Cubes are very a different beast from a traditional database.  There are several different kinds of cube storage processes depending on need (OLAP, MOLAP, ROLAP, etc.) which are all done differently depending on how real time the data needs to be.

I actually did a webcast with another Microsoft MVP a couple of weeks ago where we talked a little about the BI process and how it impacts the traditional database, which might be interesting to you.  The recordings (which are free) aren't available yet, but should be soon.


Where can I find the mysql slow log? Just as the title says, where can I see it ?

Are there any config options for it ( like how many ms would determine if a query is slow or not ) ?

In MySQL :  show global variables like '%slow%';

Update :
here's a link to the manual:
link text


privilages system table in postgres Is it possible to know all the tables a user has access to using a single query?

I tried with pg_role and pg_user with no luck. Can anyone tell me about a system table which has the data of all the objects that a user has access to?

I don't think there is a canned view for that purpose, but such a query is possible to write.

You'd have to query each object type independently, forming a big UNION. A number of system information functions are available for checking permissions on objects, see the Access Privilege Inquiry Functions table.

Combine them with queries over information_schema, or over the internal PostgreSQL tables like pg_class, pg_proc, etc, and you'll be able to collect the desired information.

You might find the queries psql uses to implement \dp and the various + commands like \df+ informative. Run psql with -E to see them.