SELECT in 2 merged 3-Tables relation with IN() clause and a COUNT for each relation In a 3-Tables relation Query, I have this following two Queries for each relation:

http://sqlfiddle.com/#!9/0bcc34/3/0

SELECT d.`id`, COUNT(da.`doc_id`)
FROM `docs` d

LEFT JOIN `docs_scod_a` da ON da.`doc_id` = d.`id`
LEFT JOIN `scod_a` a ON a.id = da.`scod_a_id`

WHERE a.`ver_a` IN ('AA', 'AB')

GROUP BY d.`id`;

| id | COUNT(da.`doc_id`) |
|----|--------------------|
|  1 |                  2 |
|  2 |                  1 |
|  3 |                  2 |

http://sqlfiddle.com/#!9/d89a4e/1/0

SELECT d.`id`, COUNT(db.`doc_id`)
FROM `docs` d

LEFT JOIN `docs_scod_b` db ON db.`doc_id` = d.`id`
LEFT JOIN `scod_b` b ON b.id = db.`scod_b_id`

WHERE b.`ver_b` IN ('BA', 'BB')

GROUP BY d.`id`;

| id | COUNT(db.`doc_id`) |
|----|--------------------|
|  1 |                  2 |
|  2 |                  1 |
|  3 |                  2 |

What I want to Do is to merge both Queries into a Single one That will not only use (3-Tables)x2 only, So I've tried the following Query:

http://sqlfiddle.com/#!9/1d2954/2/0

SELECT d.`id`, COUNT(da.`doc_id`), COUNT(db.`doc_id`)
FROM `docs` d

LEFT JOIN `docs_scod_a` da ON da.`doc_id` = d.`id`
LEFT JOIN `scod_a` a ON a.id = da.`scod_a_id`

LEFT JOIN `docs_scod_b` db ON db.`doc_id` = d.`id`
LEFT JOIN `scod_b` b ON b.`id` = db.`scod_b_id`

WHERE a.ver_a IN ('AC', 'AB') AND b.ver_b IN ('BA', 'BB')

GROUP BY d.`id`;

What I expected to get from this Query was the following results:

| id | COUNT(da.`doc_id`) | COUNT(db.`doc_id`) |
|----|--------------------|--------------------|
|  1 |                  2 |                  2 |
|  2 |                  1 |                  1 |
|  3 |                  2 |                  2 |

But Instead of That I got

| id | COUNT(da.`doc_id`) | COUNT(db.`doc_id`) |
|----|--------------------|--------------------|
|  1 |                  4 |                  4 |
|  3 |                  2 |                  2 |

Is the Problm on the COUNT only or Am I using the wrong method to SELECT data from 3-Tables Relation?

So in Berif: How can I select data from 3-Tables Relation, And merge them together, Then COUNT result of each relation,

In the end I'll SUM the count for each row into one, So the final result expection overall after all of this is supposed to be

| id | SUM(COUNT(da.`doc_id`) + COUNT(db.`doc_id`))|
|----|---------------------------------------------|
|  1 |                                           4 |
|  2 |                                           2 |
|  3 |                                           4 |

You're multiplying the joins, and you get what you ask for.  Scalar selects may be what you want:

select X.id, X.sda + X.sdb sumsummerdesum from (
SELECT d.`id`  id,
 (select COUNT(da.`doc_id`)
  from `docs_scod_a` da,  `scod_a` a
  where da.`doc_id` = d.`id`
    and a.id = da.`doc_id`
    and a.ver_a in ( 'AA', 'AC')
 ) sda,
 (select COUNT(db.`doc_id`)
   from `docs_scod_b` db, `scod_b` b
   where db.`doc_id` = d.`id`
     and b.`id` = db.`doc_id`
     and b.ver_b in ('BA','BB')
 ) sdb
FROM `docs` d
) X

http://sqlfiddle.com/#!9/fee4c6/39

id  sumsummerdesum
1   6
2   1
3   2
4   0


with MySQL, are multiple single column indexes faster than one multi-column index? I have a performance related question, in MySQL, are multiple single column indexes faster than one multi-column index?

SHORT ANSWER

Only as a last resort

LONG ANSWER
Having multiple indexes can be a rather arduous adventure for MySQL Query Optimizer.
I have written about this before

Sep 18, 2012 : How are multiple indexes used in a query by MySQL?
Apr 19, 2014 : Optimizing indexes (Under the Heading ANSWER TO QUESTION #2)

In essence, MySQL will do lookups along multiple indexes and perform an index merge of the results. This would only be done as a last resort if there are no compound indexes that are usable for your query's WHERE, GROUP BY, and ORDER BY clauses.
The order of the columns in the multiple-column index also has a bearing. If you have the following:

Table with indexes (a),(b),(c)
Same table with index (a,b,c)

The three-column index would be better if the WHERE, GROUP BY, and ORDER BY clauses harness the indexed columns in the order inside the index (a,b,c). Anything different combination could possibly result in an index range scan of (a,b,c). Next best choice could be full index scan of (a,b,c) . If these are not possible, the last resort becomes the merge of searching (a),(b),(c) separately.


When trying to connect to an external postgres database using pgAdmin, I get an error I get the following error when trying to connect:

  "FATAL: no pg_hba.conf entry for host "", user "postgres", database "postgres", SSL off"

The db is installed on an Ubuntu machine in Azure. I have allowed inbound connections on port 5432, so there should be no issue there however.

I have edited and added the following lines to the /etc/postgresql/9.5/main/pg_hba.conf file on the server:

host all all 0.0.0.0/0 md5
hostssl all all 0.0.0.0/0 md5

even when I tried to change the mode to trust, I couldn't get a connection established.

I also edited the listen addresses to be: "listen_addresses = '*'" in /etc/postgresql/9.5/main/postgresql.conf.

I have ran the command service postgresql restart after making each of the changes.

The cause of the is the bold part, the host "":

  "FATAL: no pg_hba.conf entry for host "", user "postgres", database "postgres", SSL off"

The database, from which you get the error, see a local connection, and not a networked one.

How can it happen, it depends on your system configuration, what you didn't share here.


TokuDB table stats row count decreases to 1 I have a few Percona 5.6.30 servers with a TokuDB table that has ~120 million rows. On some of these servers, row count in SHOW TABLE STATUS mysteriously decreases until is hits 1. This is accompanied by rather unpleasant performance reduction and happens on both master and slave servers.

I've tried to fix this by running ANALYZE TABLE, running OPTIMIZE TABLE, restarting the affected server, running ALTER TABLE ... FORCE. None of these has had any effect. The only two things that help are ALTER TABLE ... ENGINE=TokuDB and recreating the table from dumped data. These bring row count back to sane values, after which they begin to decrease again.

Any hints about what might have caused this issue and how to fix it would be helpful.

We looked into it for a while and it looks like this is a regression in Percona. I wasn't yet able to reproduce it outside our own environment, but it started when we upgraded our servers from 5.6.22 to 5.6.28, persisted through upgrading to 5.6.30, and disappeared when we returned to 5.6.22.

The stats decreate was present on both master and slave servers and apparently depended on traffic the database served. I was not able to replicate this on any smaller data nor with any specific queries.


What does "index" mean on RDBMSs? I use indexes like most developpers do (mostly on... well ! index), but i'm sure there is a lot of subtle way to optimize a database using index. I'm not sure if it is specific to any implementation of a DBMS.

My question is : what are good examples of how to use index (except for basic, obvious cases), and how does a DBMS optimize its database when you specify an index on a table ?

Think of an index as "table of contents"... that is an ordered list of pointers to positions in a file, aka offsets. Say that you have millions of records stored in a table, rather than search the table for matching criteria, it's much faster to reference an ordered list for matches, then stack the pointers to the specific matching rows. A perfect example of an index is a tables primary key field, most typically its "id" field. If you want row id # 11234566, it's much faster ask the index for a pointer to the data than it is to scan the data source for position 11234566.

Here's a not so obvious use of indexing:

CREATE TABLE activity_log (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
activity_type_id SMALLINT UNSIGNED NOT NULL,
datetime_created DATETIME
KEY(activity_type_id),
PRIMARY KEY(id)
);
CREATE TABLE activity_log_to_date_key (
activity_log_id INT UNSIGNED NOT NULL,
date_created_key  INT UNSIGNED NOT NULL REFERENCES dim_datetime(id),
UNIQUE KEY(activity_log_id),
KEY(date_created_key)
);
CREATE TABLE dim_datetime (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
date_hour DATETIME NOT NULL,
PRIMARY KEY(id),
KEY(date_hour)
);

Your operation can create your log record, but then create a reference to an indexed datetime that is faster to search/sort than your log table. Then join back your log table on its own primary key. If you need me to expand on this, let me know. I hope this makes sense.

Sample query:

SELECT a.activity_log_id, al.activity_type_id, al.datetime_created
FROM activity_log_to_date_key a
INNER JOIN dim_datetime d ON (d.id = a.date_created_key)
LEFT JOIN activity_log al ON (al.id = a.activity_log_id)
WHERE d.date_hour BETWEEN '2009-01-01 00:00:00' AND '2009-06-01 12:00:00';


Missing wsrep variables in mysql / percona / galera cluster I set up a small mysql/percona/galera cluster using the percona cluster docker image, with 3 nodes.

I followed the documentation written here

After starting up the whole cluster, I'm missing some of the wsrep variables.

For instance wsrep_local_state_comment. (among many others)

Does anyone knows why is this happening ?

(I'd imagine it is not, strictly ,percona related, but I don't know)

Thank you

After starting up the whole cluster, I'm missing some of the wsrep variables.

I'm not sure if this is the issue, but
wsrep_local_state_comment is not a wsrep variable, it's a status variable. Which means you have to use SHOW GLOBAL STATUS LIKE ... rather than SHOW GLOBAL VARIABLES LIKE ..., i.e. in this case:

SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';

And more generally:

SHOW GLOBAL STATUS LIKE 'wsrep%';


MySQL Event does not run i am running mysql 5.1.41 that came bundled with xampp on windows. the problem is that the event doesnt run automatically even when the event scheduler is ON. i have a table named ta_table using innodb engine and it has 4 fields one of which is ti_time with a timestamp type with default value of current timestamp. this field ti_time is given the value of timestamp at which the row is inserted. now i want to delete all rows which are 2 hours old from the table ta_table so i created an event
the event looks like this

CREATE EVENT ev ON SCHEDULE EVERY 1 MINUTE STARTS 2011-07-17 14:54:52 ENABLE
    DO
    begin
    delete from ta_table where timestampdiff(minute,ti_time,now())>120;
    end

now this event should delete any rows with ti_time field greater than 2 hours(120 minutes). when i execute this query

delete from ta_table where timestampdiff(minute,ti_time,now())>120;

it works. it deletes the rows older than 2 hours. which means my query is correct but the event is not running. my event scheduler is running which i confirmed by show processlist
and it shows 2 preocesses root and event scheduler.
the state of the event scheduler is waiting for next activation.
when i ran this query

SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE event_name = 'ev'

it gives result as

status = enabled
last executed=2011-07-18 02:36:38

but when i see the table ta_table the records are not deleted? whats wrong with this?

Edit:

As of RolandoMySQLDBA's suggestion i upgraded mysql 5.1.14 to mysql 5.5 but the event still fails

I did some real digging in the bug list for event scheduler issues.

It seems that a certain time calculation for the event scheduler was not portable. This bug was fixed as of MySQL 5.1.48 (Fixed June 2, 2010).

There was a past issue about SHOW EVENTS not getting events from the correct database. This bug was fixed as of MySQL 5.1.57 (Fixed May 5, 2011).

The latest scheduler bug was fixed July 5, 2011 in MySQL 5.1.58

You are using MySQL 5.1.41. You may want to upgrade to the latest version of MySQL 5.1 which is 5.1.58. No scheduler bugs exists as of today.

CAVEAT

On another note, I would change the SQL query to not only do less work

Instead of your DELETE query:

delete from ta_table where timestampdiff(minute,ti_time,now())>120;

Restructure it as follows:

delete from ta_table where ti_time < (now() - interval 2 hour);

Your DELETE will calculate against every row in the table. This new DELETE stops short when comparing ti_time against a time value  (now() - interval 2 hour) instead of computing timestampdiff on every row.

Make sure ti_time is indexed. If not, do this:

ALTER TABLE ta_table ADD INDEX (ti_time);

Assuming the table is MyISAM, you may also want to periodically shrink the table every month like this:

ALTER TABLE ta_table ENGINE=MyISAM;

I hope this information helps !!!

UPDATE 2011-07-19 08:00 EDT

From the last chat room session lovesh and I had, here is the example I ran to create the event on my PC running MySQL 5.5.12:

drop database lovesh;
create database lovesh;
use lovesh
create table mydata (id int not null auto_increment primary key,ti_time timestamp DEFAULT current_timestamp) ENGINE=MyISAM;
DELIMITER $$
DROP PROCEDURE IF EXISTS `lovesh`.`LoadMyData` $$
CREATE PROCEDURE `lovesh`.`LoadMyData` ()
BEGIN
    DECLARE NDX INT;
    SET NDX = 0;
    WHILE NDX < 100 DO
        INSERT INTO mydata (ti_time) VALUES (NOW() - INTERVAL CEILING(14400*RAND()) SECOND);
    SET NDX = NDX + 1;
    END WHILE;
END $$
DELIMITER ;
show create table mydata\G
SHOW CREATE PROCEDURE LoadMyData\G
CALL lovesh.LoadMyData();
CREATE TABLE ta_table LIKE mydata;
ALTER TABLE ta_table DISABLE KEYS;
INSERT INTO ta_table SELECT SQL_NO_CACHE * FROM mydata;
ALTER TABLE ta_table ENABLE KEYS;
CREATE EVENT ev
    ON SCHEDULE
      EVERY 1 MINUTE
      STARTS (NOW() + INTERVAL 1 MINUTE)
    DO
      DELETE FROM ta_table WHERE ti_time > NOW() - INTERVAL 2 HOUR;
SELECT COUNT(1) FROM ta_table;
SELECT SLEEP(62);
SELECT COUNT(1) FROM ta_table;

This worked for me when ta_table was MyISAM. It just kept running using InnoDB. This may be the sticking point.


Oracle db_name, db_unique_name, and ORACLE_HOME I'm studying Oracle and I would like to understand the uniqueness or constrains when assigning these parameters.

Suppose a machine with a single OS (host) and 2 different databases (physical). Are they forced to use different db_name in following cases?

They share ORACLE_HOME
They don't share ORACLE_HOME

How is this related when db_unique_name enters in the game? What's the point?

So far reading in Forums and offical Docs I got the following assumption :

EDITED

This is what created me confusion: http://www.mail-archive.com/oracle-l@fatcity.com/msg10241.html

Then, you could have following situations:

Repeated DB_NAME is not possible within the same ORACLE_HOME (no matter OS and SID) for the DB_NAME is stenciled onto the files for that DB. There are 2 exceptions (Parallel Server/RAC though they are indeed different instances of the same database, and Primary-Standby systems, where need a common DB_NAME and Oracle provides us with the DB_UNIQUE_NAME mechanism)
For different ORACLE_HOMEs, it is possible as each installation does not see the other and they don't conflict when sharing DB_NAME, but is strongly undesirable. Besides, on Windows, every instance is mapped to a service OracleService%SID%, so that makes impossible to have two different SIDs on the same OS, even if an instance ID is defined as ORACLE_HOME + ORACLE_SID. But as by default (and it's an ORACLE recommendation for conventional setups) DB_NAME = SID, even in different ORACLE_HOME, 2 databases in Windows are likely to fail due to a SID default name that is common (so the second service cannot be created).

... but more authoritative answers are still welcome!

You can have 2 databases with the same db_name on the same system on linux in the same ORACLE_HOME. You just have to make sure that the instance name differs. EG different pfile/spfile,... It's just not advised to give them the same name...
When running the runinstaller you just give them the same global name and a different SID.
--> Not sure how Windows reacts on it.
The reason why the db_name has to be the same in a primary-standby setup is because the name is "imprinted" on the datafiles. The db_unique_name is mandatory in order to make a difference between the databases and to setup a syncing. The recovery would be impossible if you use a different db_name


mysql55 spontaneous malfunction MySQL runs fine for weeks or days, and then suddenly and without any user action, all attempts to connect begin failing. This is /var/log/mysqld.log:

160916 14:42:01 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
Version: '5.5.51'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
160930 03:05:51 mysqld_safe Number of processes running now: 0
160930 03:05:52 mysqld_safe mysqld restarted
160930  3:05:53 [Note] /usr/libexec/mysql55/mysqld (mysqld 5.5.51) starting as process 8961 ...
160930  3:05:53 [Note] Plugin 'FEDERATED' is disabled.
160930  3:05:54 InnoDB: The InnoDB memory heap is disabled
160930  3:05:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160930  3:05:54 InnoDB: Compressed tables use zlib 1.2.8
160930  3:05:54 InnoDB: Using Linux native AIO
160930  3:05:54 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
160930  3:05:54 InnoDB: Completed initialization of buffer pool
160930  3:05:54 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160930  3:05:54 [ERROR] Plugin 'InnoDB' init function returned error.
160930  3:05:54 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160930  3:05:54 [ERROR] Unknown/unsupported storage engine: InnoDB
160930  3:05:54 [ERROR] Aborting

160930  3:05:54 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete

160930 03:05:54 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

What causes this, and how can I deal with it?

Did you see this in the log file?

160930  3:05:54 InnoDB: Fatal error: cannot allocate memory for the buffer pool

You do not have enough memory available on your host. Either decrease the amount of memory MySQL is configured to use, or increase the amount of memory the host/VM has.

The database cannot start because there is not enough memory.

Speculating as to why the database restarted in the first place, it may well have been that the operating systems OOM Killer chose the MySQL process as "the one" when the OS was exhausted of memory. See How OOM killer decides which process to kill first? on Unix.SE.

The "Unknown/unsupported storage engine" message appears because MySQL has asked the storage engine (InnoDB) to initialize itself, which it cannot do due to lack of memory.

160930  3:05:54 [ERROR] Plugin 'InnoDB' init function returned error.
160930  3:05:54 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160930  3:05:54 [ERROR] Unknown/unsupported storage engine: InnoDB


Timeout on CPU usage? Is there any way, in SQL Server, to cause a batch to time out if its CPU usage exceeds some value specified in the batch?  I need this to occur at the batch, and not the connection level.

There has been a quick and dirty method in SQL Server for 20 plus years - you set the value of Query governor cost threshold to your arbitrary amount and then if the estimated cost of the execution plan exceeds that the query is rejected - I believe the end user receives some kind of error message but I haven't seen this enabled in the wild for a VERY long time so can't remember any details.
NB: more than just CPU costs is included from the link : CPU time, memory, and disk IO.


How to guard against backup corruption due to spurious bit errors? There's always a very small chance that a backup that was just written to disk is corrupted. Disks are not 100% reliable. Bits can be flipped with very low probability. I have seen that happen on multiple machines on otherwise well-functioning disks.

It is possible to detect such errors using backup checksums. But what do you do when an error is found? If a log backup is corrupted the log backup chain is broken. If it is a full backup depending differential backups are now unusable. A new full backup is needed in order to restart the usual backup schedule.

What can be done to avoid losing backups randomly due to spurious data corruption?

It is possible to detect such errors using backup checksums.

If you use the CHECKSUM option, then yes it should be found but only when using a RESTORE or RESTORE ... WITH VERIFYONLY. If you don't check it, you'll never know.

  But what do you do when an error is found?

That depends on what is wrong and where it happened. The action to be taken first is to make sure your entire restore sequence is still valid and can be actioned. The second is to figure out what caused the corruption and potentially move to different storage, drivers, etc. In this case, chances aren't that great that it will happen very often if at all.

  If a log backup is corrupted the log backup chain is broken.

This may or may not be true depending on your restore strategy. If you have a differential to bridge the gap, that's perfectly valid and you can still use the other log backups that occur after it. If you're extremely worried about single bit flip in a log backup, mirror the log backups to another storage solution.

  If it is a full backup depending differential backups are now
  unusable. A new full backup is needed in order to restart the usual
  backup schedule.

You're correct on the first part and I don't know what you are considering the "usual" backup schedule. If you have the log backups, you can continue to roll forward through the bad full and covering differentials. You're definitely going to want more than a single recovery sequence.

  What can be done to avoid losing backups randomly due to spurious data
  corruption?

Mirror your backups, for starters. Have more than a single recovery sequence. Check the backups and take action when something does go wrong so that you're not exposed to risk. Disks die, things go wrong. While it shouldn't be happening often, it will most likely happen to you at some point or another in your career. Having a good restore and backup strategy to take care of this is what will help.


Where can I find a compatible version of SSMS for SQL Server 2016 CTP? Microsoft seem to have many versions of SQL Server 2016 CTP available at a time to download, at only 2.5GB a piece, but only one version of SSMS 2016 CTP, and if your db engine is the wrong version for SSMS, they don't suggest another version of SSMS, but another bloody version of the actual engine. This is quite ridiculous. I have a trial version of Toad that easily connects to any nearly DB engine version.

My SQL Server engine version is:

Microsoft SQL Server 2016 (CTP3.0) - 13.0.700.242 (X64)
Oct 26 2015 18:46:06
Copyright (c) Microsoft Corporation Express Edition (64-bit)
    on Windows 10 Pro 6.3 <X64> (Build 10586: ) (Hypervisor)

Does anyone know where I can get a version of SSMS 2016 that is compatible with this version?

The version of SSMS I have is the one downloaded from Rob Klimes's link in his comment below. It is version v13.0.800.111. When I try and run it, it shows me this:

The log file is large, and I don't know which parts are relevant enough to post here, but I don't see any glaring errors.

The SSMS November 2015 preview works against the SQL Server 2016 CTP versions. I actually use this version of SSMS (November 2015 preview) against my local 2012 and 2008 R2 instances and don't have any problems.


How to find number of products having SALE price 50% of MRP using MYSQL? Given below table(Product)

id|name|price_type|price|
--|----|----------|-----|
 1|book|MRP       |   10|
 2|book|SALE      |    5|
 3|pen |MRP       |   10|

In this table the answer is 1, since only one product "book" is having sale price 50% of mrp.

I tried doing the following query, but it is giving error

select name from product group by name HAVING  (count(product.name)>1 && product.price_type="MPR" && product.price_type="SALE");

In order to (finally) solve your problem, I did the following

CREATE TABLE product
(
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR (15) NOT NULL,
  price_type VARCHAR (10) NOT NULL,
  price INTEGER NOT NULL
);

Populate the table - I've added extra records, because, as I've found (again), testing with 1 record (or set - i.e. name = 'book') - I added books 1, 2 and 3, because I doubt if you're only selling one book and pens 1 and 2.

INSERT INTO product (name, price_type, price)
VALUES
('book1', 'MRP',  10),
('book1', 'SALE',  5),
('pen1', 'MRP',  10),
('pen1', 'MRP',  5),

('book2', 'MRP',  14),
('book2', 'SALE',  7),
('pen', 'MRP',  16),

('book3', 'MRP',  30),
('book3', 'SALE',  15),
('pen', 'MRP',  8);

The query

SELECT SUM(cnt) AS number_of_50
FROM
(
  SELECT  p1.name, COUNT(p1.name) AS cnt
  FROM product p1
  JOIN product p2
    ON p1.name = p2.name
  WHERE p1.price = 2 * p2.price
  GROUP BY p1.name
) AS tab

Result

number_of_50
5

dbfiddle here!

p.s. Apart from having more than one record, you might consider using a fiddle for your tables and data for any questions in the future? Also, please also always supply your version of MySQL, it can make a real difference to the answer. There are some articles on how to ask questions here on my profile - you might want to take a look? Welcome to the forum!


Amazon RDS freezes on mysqldump My questions is quite similar to Mysqldump from the Amazon RDS. I have the same behavior. Everytime when I do a backup using mysqldump my entire RDS freezes and hence my application is not responding. I am using --single-transaction with the mysqldump command but that doesn't help at all. With this, RDS is quite unusable since I can not dump a backup or data sample during live operation. Is there a way to get the data out of RDS without freezing everything?

I suggest taking a look at processlist while the DB is dumping. There could be locked MyISAM tables causing queue of statements waiting for locks to be released.


Can a SQL Server Subscription Database be unsubscribed and used as standalone? We've got a new server we want to migrate to and I have setup a Publication on the current production server as a Transactional publication. Since the subscribed database is up to date, I want to discontinue the subscription, rename the database, then point our database connections to it.
The new server is SQL Server 2012 where the old server is 2008.

Thanks for any pointers.

Yes .. you can remove replication.

USING GUI :

On the publication server, Right Click on the subscription and delete it. Make sure you select "connect to subscriber" so that all the replication bits are properly cleared.

USING TSQL :

---  removes replication objects from the subscription database.

-- Remove replication objects from the subscription database .
DECLARE @subscriptionDB AS sysname
SET @subscriptionDB = N'AdventureWorks2008R2Replica'

-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @subscriptionDB
GO

----  script disables publishing and distribution on a server that is a Publisher and Distributor and drops the distribution database.

-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables
-- on the command line and in SQL Server Management Studio, see the
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".

-- Disable publishing and distribution.
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB as sysname;
SET @distributionDB = N'distribution';
SET @publisher = $(DistPubServer);
SET @publicationDB = N'AdventureWorks2008R2';

-- Disable the publication database.
USE [AdventureWorks2008R2]
EXEC sp_removedbreplication @publicationDB;

-- Remove the registration of the local Publisher at the Distributor.
USE master
EXEC sp_dropdistpublisher @publisher;

-- Delete the distribution database.
EXEC sp_dropdistributiondb @distributionDB;

-- Remove the local server as a Distributor.
EXEC sp_dropdistributor;
GO

Refer to How to: Disable Publishing and Distribution (Replication Transact-SQL Programming)

MSDN Blog has How to cleanup Replication Bits

EDIT: Based on the comments :

For migration to new server, you are better off

If the database is not big (more than 500GB+) and you have a good maintenance window

Go for backup and restore method.

If the maintenance window is small then

Either you can choose between LogShipping or Database Mirroring.
When you want to cut over to the new server -

With logshipping, just take the final Tail log backup and restore on the new server with recovery.
With Mirroring, you can switch to synchronous mode when you decide to failover to the new server.

Above methods will help you to migrate your database to new server with minimum downtime. Obviously, there are many other pre and post migration steps that you need to be aware.


Why do we need BCP Node in AOAGs I have PROD Site and DR Site. If PROD site is completely down.I have backups of PROD site for Databases which are in SYNC and backups using a Third party tool.So I have copied the latest backups to DR Site and I can restore them.If I have fresh DB copies and if I am able to bring the server online by restoring latest backups ,what exactly is the use of DR BCP server ?

How quickly can you get the databases restored from backups as compared to failing over to the DR replica? Failing over to the DR replica can be done without data loss, provided the primary replica is still accessible, and can be done in just a few seconds.


How to find the length of multiple column index in MySQL Is there a way to find out what is the length in bytes for the unique index (NKU) key created through the following command:

CREATE TABLE test_ (
  testId bigint(20) NOT NULL,
  name varchar(75) DEFAULT NULL,
  key_ varchar(200) DEFAULT NULL,
  user varchar(255) DEFAULT NULL,
  PRIMARY KEY (testId),
  UNIQUE KEY NKU (name,key_,user)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

You'r lucky you have only one secondary index, the PRIMARY KEY is a clustered index so it isn't included in the index statistics.

You can find the answer with the field INDEX_LENGTH of the information_schema.tables view (result in bytes):

SELECT  INDEX_LENGTH
FROM    information_schema.tables
WHERE   table_schema = "myDatabase"
AND     table_name = "test_";

I recommend you to do a ANALYZE TABLE before to get more accurate estimates:

ANALYZE TABLE myDatabase.test_;

If you have more than one secondary index in your table you should have been to drop all but one and show the INDEX_LENGHT:

CREATE TABLE myDatabase.test_ (
  testId bigint(20) NOT NULL,
  name varchar(75) DEFAULT NULL,
  key_ varchar(200) DEFAULT NULL,
  user varchar(255) DEFAULT NULL,
  PRIMARY KEY (testId),
  UNIQUE KEY NKU (name,key_,user)
  UNIQUE NKU2 (user)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE myDatabase.test_ DROP INDEX NKU2;

ANALYZE TABLE myDatabase.test_;

SELECT  INDEX_LENGTH
FROM    information_schema.tables
WHERE   table_schema = "myDatabase"
AND     table_name = "test_";

Max.


Validating Excel Counts against Database Inserts I have to design a SSIS package which uses Excel Source files and inserts the data into SQL Server Database.

The problem is that I've created the packages top transfer data, but now I need to validate whether the Number of Rows in Excel File and the number of Rows in Database are the same. usually a single package has to process 3-4 Excel Files and each file has 5-6 sheets in it.

I've searched over the internet and found various ways one of which was that I use 'Row Count' component in Data Flow Tasks but I think that its a useless step since all the rows which will be read from the excel source will surely be transferred to the Destination. I want to know all the Excel Row Counts BEFORE the Data Flow Tasks are executed.

Is there a way to do this?

Not really.

Since Excel isn't a proper client server database, you can't ask it how many rows are there before bringing them in. You could bring them into a staging area and count rows as they come in. If they don't match and you need to handle that with a business process, the destination is untouched. If they meet your validation criteria, do a second transfer to the real destination. You can use a database table for this, or a raw file connection if you'd rather spill to the filesystem. Cache connection managers are useful if your dataset is reasonably sized.


SQL Server updates missing from WSUS I don't use WSUS for SQL Server patching because we have so many different compatibility issues, requirements and downtime OLA's that it makes it impossible to deploy anything in this way - not sure of many who can to be honest.

However, we have a custom DBA 'app' that I would like to develop a snap-in for that alerts the team to any new updates released by Microsoft for SQL Server (SP's, CU's, Hotfixes etc.)

I thought, hey, let's just hook into the WSUS database, poll for new updates and display them on a web page if they exist...

It works to a fashion, but I noticed that there is a lot of missing updates in WSUS from SQL Server. For example I'm not seeing SQL Server 2014 SP2 CU1 in there which was released recently, 2014 SP1 CU7 is not there, but CU8 is. 2012 SP2 and SP3 are not their either, as another example.

We sync WSUS half-hourly, and all SQL Server products are selected.

Any idea why some SQL Server updates aren't coming through to us via WSUS? IS this normal? Do Microsoft not publish everything through WSUS?

Actually there is a good microsoft article about it. I had the same problem and Microsofts solution fixed it.
https://support.microsoft.com/en-us/help/4047327/non-applicable-sql-server-cus-are-listed-in-wsus-mu-or-confmgr


How to become a DBA...the non-standard route (not beginning as sysadmin or dev)? Background:

I'm a recent BS graduate who's found myself in an extraordinary situation. I've fallen into something of an entry-level Oracle DBA role straight out of the college box. At my small company where the only 2 database people around have each been at it for over half the time I've been breathing, it's difficult to see the path between where I am and where they are.

From what I've read, many successful DBAs emerge from systems admin or developer roles, and I feel as though I've cheated myself - I've jumped the gun and now have no base in which to root new knowledge. Furthermore, I'm pulled in too many different directions as far as what to learn first. (Must understand B before learning A; but must have a grasp of A before diving into B!)

I often find myself wondering how to get where I want to go. What plan of attack might be the simplest for a beginner to begin? Width before depth or depth before width (i.e. an overview of the entire gamut while glossing over details OR an in-depth dive into RMAN or another specific topic). Then what about how Oracle plays with others like Linux, ASM/disk arrays, hardware, business applications, etc.?

Endless thoughts! Stressful implications!

Question:

Essentially, what resources (free or paid) would provide the most value up front, considering virtually no experience and only vague textbook education? Something that may provide exercises, lectures, etc. An ideal source would include more advanced or applied topics than basic SQL and db architecture without too much jargon.

You can add a lot of value to your career at little or no cost except for some of your  time.  You need a desktop computer or server with at least 4 GB of ram. Download Oracle virtual box or pick a pre configured image here.

Install and configure.I learned more by breaking things than by reading in books or videos so don't be afraid to break your database repeatedly as you figure how to use the tools of the trade. With virtual machines you can take a snapshot, break the image and revert to the saved snapshot.

Once you have installed the database you can try adding a schema of your design or play with Oracle APEX to get a quick idea about web applications.

All the Fusion applications are available for download like BEA weblogic and you can play with some Hello world apps in Java.

Then try and duplicate some of the things you need to do at work like backup, tuning, RMAN and anything else you need to practice on.  It won't be easy but it will reward you with hands on experience which will give you confidence for work tasks.


Can I ignore queries about extendedproperty? I want to reuse the structure of a database in a new project. So using SSMS I use Generate Script and in settings I set to generate schema only.

In the result there are too many queries about extendedproperty. Is there any important data or settings in these queries or I can completely igonre them and only reuse queries on database tables and fields to start a similar project?

And if they ar unnecessary how can I use "generate script" without them?

sample query:

EXEC sys.sp_dropextendedproperty @name=N'CollatingOrder' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'billboard', @level2type=N'COLUMN',@level2name=N'url'

You can modify this behaviour by changing the scripting options:

Tools -> Options -> SQL Server explorer -> Scripting -> Script extended properties

  Is there any important data or settings in these queries or I can completely ignore them...

It depends.

DBA's and developers can use extended properties to save information about database objects. If you're scripting other dba's objects you could loose information about them.

Have a look at this articles on MSSQLTips:

Working with SQL Server Extended Properties

Quoted from doc

  Extended Properties can be created for the below database objects, but
  in this tip, we are going to focus on column level Extended
  Properties.

  Database
  Stored Procedures
  User-defined Functions
  Table
  Table Column
  Table Index
  Views
  Rules
  Triggers
  Constraints

  Extended Properties can be used to:

  Specify a caption for a table, view, or column.
  Specify a display mask for a column.
  Display a format of a column, define edit mask for a date column, define number of decimals, etc.
  Specify formatting rules for displaying the data in a column.
  Describe a specific database objects for all users.


Is there an efficient way to use 3rd party packages in PL/Python? I've found examples of people importing 3rd party packages into PL/Python scripts, but it seems to me that re-importing libraries upon every run of a stored procedure is terribly inefficient.

Does Postgres maintain a process-wide python interpreter that's re-used for subsequent PL/Python script executions?

If so, is there any way to have it execute import statements in that context BEFORE a PL/Python procedure runs?

Alternatively, would it be possible/practical to provide a C-compatible dynamically-linked library that hosts a persistent Python interpreter?

I've found examples of people importing 3rd part packages into PL/Python scripts, but it seems to me that re-importing libraries upon every run of a stored procedure is terribly inefficient.

Using strace, I can see that the files are only actually read the first time an import is done in any given session (there may be exceptions, like if the effective user id changes within a session).  There may be some initialization which is re-done every time the import is run, I don't know how to investigate that.

I don't think you can boost the loading to above the session level.


SQL backup file size reduced I'm new to SQL server and need your advice.

I have a database that shows 188GB as the size in the database properties window.

When I take a backup of the database, that backup file is only 65GB.  The database properties window still shows the database itself is 188GB.

Why is there a difference in the size of the backup compared to the actual database?  I'm not using backup compression.

Check to see the amount of space actually used in your database. You probably have a lot of free space available. A quick way to do this would be to connect to your database and use exec sp_spaceused which will give you the database size and unallocated (unused) space.

When SQL Server backs up the database it only needs to backup the pages that contain data (and the log), which means that the backup can easily be smaller than the files themselves.

This question has also been answered in Why is a .bak so much smaller than the database it's a backup of?


Does "use AWE to allocate memory" do anything on a SQL Server 2008 R2 64bit Windows 2003 system? After checking the box I noticed that queries that would timeout after 30 seconds would complete execution.

The TechNet article says "AWE is not required and cannot be configured on 64-bit operating systems."

So did checking the box do anything?

SQL Server 2008 R2 / Version 10.52.4000.0

For 64 bit OS, you dont need AWE and even enabling AWE using sp_configure awe enabled has no meaning.
Refer to Great SQL Server Debates: Lock Pages in Memory by Jonathan Kehayias :

In 64-bit SQL Server, the SQL Server account still requires the Lock Pages in Memory permission in order to be able to allocate locked pages, via AllocateUserPhysicalPages(), but there are a couple of big differences:
The underlying reliance on AWE-mapped memory is removed. You do not need AWE in 64-bit SQL Server; the awe enabled sp_configure option has no meaning. The continued use of the same AWE API function is purely to ensure that the allocated pages are locked.
Memory allocated via AllocateUserPhysicalPages() can be used for both the data cache and plan cache. In 64-bit SQL Server, the plan cache is no longer allocated separately (it now uses stolen pages from the buffer pool)

The memory allocations for AWE in 32bit use the AllocateUserPhysicalPages() API.  The memory allocations for lock pages in 64bit use the same API, but they aren't using AWE because the VAS in a 64-bit process is 8TB for user mode space and it doesn't require special mappings through AWE.
Queries timing out has least to do with AWE. There might be missing indexes, outdated stats, poorly configured memory settings and many other stuff that can lead to queries timing out.
What specific version of sql server are you running (along with patch level) ?


SQL Server Express 2008 installation problem I just installed SQL Server Express 2008 on my Windows 2003 server.

The sql base is started, but I look in config manager and my SQL Server browser is not started. I can't start it. When I try to change properties, I got an error: there's no enable device associate (0x800704).

I will look for reinstalling SQL Server browser manually ( http://msdn.microsoft.com/en-us/library/ms181087.aspx ). I don't know if that will cause an error. Maybe the same thing I got before.

I try to reinstall/repair, but there's an error (my biggest problem is that I'm not the admin of this server, so I need to wait for the tech to be there if I want to do something. Last timed, I didn't have the time to get all information. I need to get all ideas and program before, because I will have not so much time to try to repair it. The server is used by other users (data), and I can't reboot often)

If you have any idea, what should I do, or Test, or hit?

thank you

To install SQL Sever properly -- and have all the services running as expected -- you really should be logged in as an administrator.


How to convert default instance to named instance in SQL Server 2012? How to convert default instance to named instance in SQL Server 2012?

By mistake I have chosen default instance option and installed. Now I have to change to named instance. How to change?

Install the named instance, move the databases, agent jobs, etc over to it, then uninstall the now unused default instance.


What is the locking behavior for Updatable/Insertable Views? Consider I have a View that is merely a simple join between 2 tables, selecting all columns.

The View is Updatable and Insertable.

What would be the locking behavior when executing an UPDATE or INSERT statement in the following situations:

I’m going to insert a record into the view, and only columns of table A have values.
I’m going to update a record in the view, but only columns from table B were modified.

My doubt is basically if on situation 1, only table A is going to be locked, and if on situation 2, only table B is going to be locked.

I’ve searched for the answer in IBM’s documentation, but couldn’t find an appropriate answer. If possible, I'd like to know where IBM is keeping this information.

The short answer is that it depends on the isolation level of your connection and/or statement being run.

If you are taking the default isolation level (which is usually Cursor Stability - CS) in DB2, then I would assume it only puts a lock on the actual rows being modified (as an update lock) and it puts a read lock on the row currently being held by the cursor. All other rows are not technically locked in any sort of way.

Now if you go up higher into Read Stability (RS) or Repeatable Read (RR), then you start to get into where it will lock more rows in the result set (and even the whole table in a join case)....

You may wish to check out the Isolation Level settings in the DB2 Information Center, as this will give you a better picture of how it will act.

Also note, even though there is a default isolation level for the connection, this can be overridden in the session, as well as at the statement level, including the underlying statement that builds the view. So if the default is CS, but the view was built with a SELECT that uses RR, then you are using RR and probably locking on all rows in both the result set and all rows utilized by the engine to create the result set. I'm guessing this is not likely (or at least hopefully someone didn't write the view that way....) and you are using CS, in which what I explained at the beginning is more likely how it behaves.


Natural (numeric) sort on string I've got a table of data like so:

Process_Id  Stage_Id    Step_Id
P1          P1.5        P1.5.1
P1          P1.5        P1.5.10
P1          P1.5        P1.5.11
P1          P1.5        P1.5.12
P1          P1.5        P1.5.13
P1          P1.5        P1.5.14
P1          P1.5        P1.5.15

And as you can see it's not sorting naturally on the last number of the Step (as you or I would imagine it would be sorted in any other situation). This is of course because it's stored as a string.

Is it possible to have these numbers sort naturally? Perhaps ordering by a 'split' pseudo-column consisting of the split of Step_Id by '.'?

Thanks.

Assuming you can trust the last octet will always be a number:

ORDER BY CONVERT(INT, PARSENAME(Step_Id, 1));


Gereralise the query for all user_id I have a mysql query

select sum(duration),app_name,app_id,user_id
from timesheet
where user_id=164
group by app_id
order by sum(duration) desc
limit 5;

The result is

sum(duration), app_name,       app_id,  user_id
'626919371',   'Idle.exe',     '0',     '164'
'38220511',    'LockApp.exe',  '2204',  '164'
'36675000',    '',             '1',     '164'
'27713000',    'LockApp.exe',  '8148',  '164'
'16698661',    'chrome.exe',   '8548',  '164'

However I want the top 5 app for every user_id instead of just 164. Can you pelase guide me as to how this can be achieved in a single query. Or do I need to fire it for every individual user

Test this:

select sum_duration,
       app_name,
       app_id,
       user_id
from ( select get_data.*,
              if( @user_id = get_data.user_id,
                  @rownum := @rownum + 1,
                  @rownum := 1 + least(0, @user_id := get_data.user_id)) row_number
       from ( select sum(duration) sum_duration,
                     app_name,
                     app_id,
                     user_id
              from timesheet
              group by app_id ) get_data,
            ( select @rownum := 1, @user_id := 0 ) init_vars
       order by user_id, sum_duration DESC
     ) subquery
where row_number <= 5


Optimized ways for defragment mysql database table We have a database in MySQL which has more than 500 GB of data, and currently in the process of purging data and count might go beyond 1 million. As we know that purging might lead to high fragmentation and will degrade performance. Also, under few circumstances where the purge data is more, it may make existing index unusable

In case if somebody has come across such scenario, please let me know best technique to handle the same

No, deletions do not significantly slow down what remains.

No, an index never becomes "unusable".

What are you purging?  "Old data"?  If so, then PARTITION BY RANGE(TO_DAYS(...)) is an excellent way to purge old data.  DROP PARTITION is 'instantaneous' and does cause any fragmentation.  My blog goes into the details of how to do the PARTITIONing, and the daily/weekly/whatever maintenance, including sample code.

If you cannot use partitioning in that way, you are stuck with whatever fragmentation happens; OPTIMIZE TABLE on a 500GB table would take a loooong time.  (There is an "online" version coming in 5.7, but perhaps not released yet.)

Are you INSERTing at the "end" of the table only?  Are you doing no UPDATEs or DELETEs other than the purging?  If yes to both, then there is a way to optimize the table or to copy the table to a partitioned table...

It involves walking through the table with the PRIMARY KEY and copying chunks of rows at a time.

To discuss further, please provide SHOW CREATE TABLE and answer the questions I posed.


remove the instance while uninstall a database I have uninstalled the database but it's instance is still available in SQL server 2005.I want to remove that instance.Is any possibilities to remove the instance while uninstall a database?

Actually you delete the database not unistall it.
in SQL Server for one instance there can be many database. i.e. one to many instance.
so there's no possibility to unistall sql server instance while deleting the database. (ofcourse unless you script it)

Uninstall the instance from control panel menu and there after.


Is there some way to store EITHER a specific day (date) OR a year in the same column? I have a column called "date or year of birth". The reason it's not called "date of birth" is that, in some cases, the exact day is not known -- only the year, or only the year and month.
If I try to store "1998" in a date column, I get an error; PostgreSQL doesn't let me store it.
I would have to turn it into "1998-01-01" for it to be allowed to be stored, but now it's probably lying (unless they happen to be born on 1st of January 1998).
Simply having only the year is not the same as 1st of January 1998. This way, I cannot ever be sure if birth dates on that day are "real" or just another case of "we only know the year".
If I turn this column into a text, it will store both "1998" and "1995-04-06", but this makes it a PITA to deal with whenever I need to compare it time-wise. Furthermore, it "feels wrong" to store such information in a "text" column; I only use those as a last resort when PG simply doesn't have "native knowledge" of the kind of information contained within.
What can I do about this?

I would model this thusly:
CREATE TABLE ... (
   birthday date NOT NULL,
   only_year boolean DEFAULT FALSE NOT NULL,
   ...
);

The second column indicates if only the year of the data is relevant or not.


SELECT only the rows with a unique value on certain columns I have a table with multiple records by each employee, i need to find all the records by employee where any of the columns values changes. for example i have the following data for George:

HOME
CURRENT
EFFECTIVEDATE
NOTTOEXCEEDDATE
FLAG
GRADE
POSITION
LASTPAYPERIODPAID

GEORGE
610422
610422
NULL
NULL
N
15
SUPERVISOR  ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
15
SUPERVISOR  ACCOUNTANT

GEORGE
610422
610422
NULL
NULL
N
15
SUPERVISOR  ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
15
SUPERVISOR  ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610124
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610124
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610124
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610124
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
15
SUPERVISOR  ACCOUNTANT

GEORGE
610422
610422
NULL
NULL
N
15
SUPERVISOR  ACCOUNTANT

GEORGE
610124
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610422
610422
NULL
NULL
N
15
SUPERVISOR  ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
15
SUPERVISOR  ACCOUNTANT

How do i get the following result data set ?

HOME
CURRENT
EFFECTIVEDATE
NOTTOEXCEEDDATE
FLAG
GRADE
POSITION
LASTPAYPERIODPAID

GEORGE
610124
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
15
SUPERVISOR  ACCOUNTANT

GEORGE
610422
610422
NULL
NULL
N
15
SUPERVISOR  ACCOUNTANT

so far i can only get the result data set by doing a Select Distinct without the LAST_PAY_PERIOD_PAID column. as soon as i add this column i get the first table all over again.
any help is greatly appreciated

I can't see your problem
Distinct produces exactly what you want
SELECT
DISTINCT
[HOME]
, [CURRENT]
, [EFFECTIVEDATE]
, [NOTTOEXCEEDDATE]
, [FLAG]
, [GRADE]
, [POSITION]
, [LASTPAYPERIODPAID]
FROM Table1

Result
HOME    CURRENT EFFECTIVEDATE   NOTTOEXCEEDDATE     FLAG        GRADE   POSITION    LASTPAYPERIODPAID
GEORGE  610124  630100          11/10/2019          11/6/2021   Y       14          ACCOUNTANT
GEORGE  610422  610422          (null)              (null)      N       15          SUPERVISOR ACCOUNTANT
GEORGE  610422  630100          11/10/2019           11/6/2021  Y       14          ACCOUNTANT
GEORGE  610422  630100          11/10/2019           11/6/2021  Y       15          SUPERVISOR ACCOUNTANT

George has two current numbers, that is why i don't get your result
http://sqlfiddle.com/#!18/f5920/1


Looking for a better way with Reporting Db server I have a "reporting" db server which is basically a clone of production using backup and restore in a stored proc to handle restore , user , permission etc.
Backups/restores happen 1x per day at a scheduled time.

Now they have a new db that they want on the report server and it is a pure OLTP db (right now I backup and restore it daily takes about 45 mins). The would like something a little more up-to-date just not realtime aka approx. with 2-4hours. I am in middle of converting from 2008 r2 to sqlserver 2014. 2008 r2 would give me replication or log shipping anybody have a better idea? Would really like a better idea as I am a single dba shop on call 24/7 365 and enjoy ability to sleep thru the night.
Thanks
kris

I would suggest 2 options ...

If you are using

Enterprise edition then

SQL server 2014 Enterprise edition will give you AlwaysON Availablity groups .. which you can use to offload reporting on secondary.

Standard edition then

You can use logshipping with delaying on transaction logs on secondary in STANDBY mode. The only caveat will be that when the transaction logs are being restored, all the users will be kicked out of the secondary server.

(I am not mentioning database mirroring, but you can use that (even though it is marked as deprecated. You can create database snapshot on secondary and every 2-3 hrs, you can generate a new one since your requirement is - data can be stale 2-4hours)

When using backup/restore method, make sure that you have enabled Instant file initialization and you are using backup compression. That will cut down the backup/restore time.


Add a new collation to a Postgres database I have these collations:

postgres=# select * from pg_collation;
  collname  | collnamespace | collowner | collencoding | collcollate | collctype
------------+---------------+-----------+--------------+-------------+-----------
 default    |            11 |        10 |           -1 |             |
 C          |            11 |        10 |           -1 | C           | C
 POSIX      |            11 |        10 |           -1 | POSIX       | POSIX
 en_US      |            11 |        10 |            6 | en_US.utf8  | en_US.utf8
 en_US.utf8 |            11 |        10 |            6 | en_US.utf8  | en_US.utf8
 ucs_basic  |            11 |        10 |            6 | C           | C
(6 rows)

I installed a new locale on my system and I'd like to use it on postgres. It seems that the only way to install a new collate is using the initdb command, but it requires to make a pg_dumpall, delete the postgres data directory, run initdb and restore all the data from the dump. Isn't there an easier solution?

You don't need to create a new DB cluster. You can use CREATE COLLATION in Postgres 9.1 or later. Example in the manual:

  To create a collation from the operating system locale fr_FR.utf8
  (assuming the current database encoding is UTF8):

CREATE COLLATION french (LOCALE = 'fr_FR.utf8');

Be sure to read the chapter Managing Collations in the manual to understand the details. In particular:

  Within any particular database, only collations that use that
  database's encoding are of interest. Other entries in pg_collation are  ignored.

I.e., the collation has to match your database encoding to be available.
But there's more. Read it.


Sql Server Database Books I was wondering how I would go about learning the functionality of all the different store procs and functions that come out of the box with sql server. Would I be right in saying that a book on sql server internals teach me all this?

Im pretty new to sql server and I have just finished volumes 1-3 out of 1-5 of Joes To Pros T-SQL querying 2012(Personally the writer is very good at putting together a book that enables you to learn and retain knowledge fast, but that's just my opinion).

Anyway, any help on this would be much appreciated. I love getting stuck into a good book as I think having structured learning with examples works best for me.

Thanks,

John

I can't recommend any books in particular but I have several locations of free books on this page: http://sqlstudies.com/study-and-reference-tools/

For example this page has something like 30 free SQL books.

In general though you pick up the various DMOs and system views/functions as you study associated technologies.  For example you will learn about the replication DMOs etc as you study replication.

The Periodic Table of DMOs by Tim Ford is a great place to start on the DMOs however.  It has all of the DMOs broken down by category and functional area.


CMDEXEC The process could not be created for step... (reason: Access is denied). The step failed I have created a job to test executing a CmdExec step.  The job step is simply executing a batch file with dir as the command.

The job is executing as the SQL Server service account.  This account is both sysadmin of SQL Server and Administrator in Windows.  The login also has been  granted exec to xp_cmdshell, in master. In Surface Area Configuration, XPCmdShellEnabled = True.  If I run the command from the query window it executes as expected.

Question: why am I still getting the job step error?  This is plain vanilla install of SQL Server 2008 R2 with SP2 installed.  Nothing fancy going on here.

Note: I know how horribly insecure this is, but this is where it has come to in order for me to chip away at this and try and resolve this error.  Best I can tell, this just can’t be made to work and BOL makes it sound so easy.

ok, so instead of using a CMDEXEC job step, I changed it back to a t-SQL script step replaced the batch file name with the full string:

EXEC xp_cmdshell 'c:\dircommand.bat'

and it works.  So I am not sure what the problem is with Operating System job steps but at this point I am passed the hurdle.  funny thing is, I have another server setup the other way and it works fine.  Thanks for the offers of assistance.


Does LIMIT 1 still have effect on a unique column I know that adding LIMIT 1 when searching for only one record makes a lot of difference when it comes to performance. But does it still have any effect when we search a column with a unique constraint?

Let us say I store email records with a id (int) primary key column and the email field is marked as unique. Do I still need to explicitly call LIMIT 1 in my queries to speed up the returning of a result set? Or does the MySQL engine understand that as soon as one record is found the result can be returned since the field is marked as unique and is LIMIT 1 obsolete?

MySQL table definition:

CREATE TABLE Email
(
    `id` int NOT NULL,
    `email` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE (`email`)
)

Does it in this case make a difference whether I do:

SELECT * WHERE `email`="john.doe@example.com";

Or

SELECT * WHERE `email`="john.doe@example.com" LIMIT 1;

if column has unique index (constraint), You do not need to use LIMIT 1

LIMIT 1 - good tools for prevent potential errors when result MUST be single and You not sure about how many rows You have


SQL Server training materials I'm sorry if this question breaks the rules of DBA StackExchange. I'm not sure if it is allowed in here.

I am planning to lead a basic SQL Server training for my coworkers. The course should include some basic information about relational databases and the SQL querying. This is why I'm looking for some training materials.

Obciously I could prepare PowerPoint presentations, SQL tasks and all the other stuff myself but I think since it is a well researched topic I should not reinvent the wheel.

Do you know of any training materials available that I could use?
I have the training materials for the MS Exam 70-461 - Querying SQL Server but I'm not sure if I would be allowed to use it and also they are pretty big.

Can you recommend something?

Try my free Learn to Query SQL Server with the Stack Overflow Database.

You can either run the queries locally with the Stack Overflow SQL Server data export, or through your browser with Data.StackExchange.com.


Do user defined data types always cause implicit conversion to occur when fields of those UDDTs are used as predicates? I have a database (from a vendor application) that for some unknown reason has a UDDT on almost every column, even when the UDDT is logically the same as the underlying type it implements. E.g. a column in this database might be of UDDT bTinyInt(TinyInt).
Two tables in this database have columns that share the same exact UDDT. E.g. TableA.Field1 is of type bVarchar(VARCHAR(10)) and TableB.Field2 is also bVarchar(VARCHAR(10)).
When I join these two tables together by the aforementioned fields, I notice a warning in the execution plan stating a Cardinality Issue because these fields are being implicitly converted to VARCHAR(MAX).
Is it always the case that UDDTs get implicitly converted when used as predicates, even when comparing the same UDDT?
Also not sure if it matters as well, but the database these two tables belong to is of collation Latin1_General_BIN.

I can't reproduce any warnings or performance differences in this scenario, even when joining bVarchar to varchar(10).  EG
create database tt

alter database tt collate Latin1_General_BIN
use tt
go
create type bVarchar from varchar(10)

create table a(id int primary key identity, b bVarchar, c varchar(10));
create table b(id int primary key identity, b bVarchar, c varchar(10));

with q as
(
  select top 1000000 row_number() over (order by (select null)) i
  from sys.messages, sys.objects
)
insert into a (b,c)
select concat(i%10000,'abc'), concat(i%10000,'abc')
from q;

with q as
(
  select top 1000000 row_number() over (order by (select null)) i
  from sys.messages, sys.objects
)
insert into b (b,c)
select concat(i%10000,'abc'), concat(i%10000,'abc')
from q;

go

set statistics time on
set statistics io on

select count(*)
from a
join b
  on a.b = b.b

select count(*)
from a
join b
  on a.c = b.c

select count(*)
from a
join b
  on a.b = b.c

set statistics time off
set statistics io off

outputs
    SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 360 ms, elapsed time = 360 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(1 row affected)
Table 'a'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 3510, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'b'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 2656, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 268 ms,  elapsed time = 117 ms.

(1 row affected)
Table 'a'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'b'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 297 ms,  elapsed time = 184 ms.

(1 row affected)
Table 'a'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'b'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 280 ms,  elapsed time = 111 ms.

Completion time: 2020-10-17T09:56:35.6734439-05:00


Help with database schema I'm working on a project and I would need help with proper design a database schema. This is the application where users can add appliances in their home. For example, television, washing machine, dishwasher line, fridge, freezer. Each one of these appliances have different parameters that I need to store in a database. I do not think they are effective represent each category of appliances separately, but on the other hand, I do not know how to include them in a single table.

I only have five categories, so five tables it was not a problem, but if they got a hundred, so I do not come quite inefficient. Do you have someone experience something similar or know to help me in my case?

Thank you and sorry for my English.

You could go with a more general structure, such as this:

appliances
----------
  id (PK)
  name (such as "bedroom TV", "main freezer", etc...)

parameter_types
---------------
  id (PK)
  type (such as "STRING", "INT", ...) - You might only be to enforce this in the application code, rather than in the database.

appliance_parameter
-------------------
  id (PK)
  name (such as "Model name", "Max. operating temperature", "IP address", etc...)
  parameter_type_id (FK to parameter_types.id)

appliance_parameter_values
--------------------------
  id
  appliance_id (FK to appliances.id)
  appliance_parameter_id (FK to appliance_parameters.id)
  value

This could be populated as:

appliances
ID  | name
----+------------
1   | Marcel's TV
2   | Main fridge
3   | coffee maker

parameter_types
ID  | type
----+---------
1   | STRING
2   | NUMERIC

appliance_parameter
ID  | name        | parameter_type_id
----+-------------+------------------
1   | model number| 1
2   | colour      | 1
3   | IP addr     | 1
4   | weight (kg) | 2

appliance_parameter_values
ID  | appliance_id  | appliance_parameter_id | value
----+---------------+------------------------+-------------
1   | 1             | 1                      | TV-01A
2   | 1             | 2                      | Magenta
3   | 1             | 3                      | 192.0.0.1
4   | 1             | 4                      | 3.2
5   | 3             | 3                      | 192.0.0.2
6   | 3             | 4                      | 1.2
7   | 3             | 2                      | grey
8   | 2             | 1                      | FRDG-001

In this example, you don't need to create specific columns for appliances. You create date for an appliance parameter in a specific table, then populate values into appliance_parameter_values when you need to. This system can handle any number of appliances with any number of parameters, as long as you can add the appropriate records for them.


MS SQL - Can I find a source of prior log growth? We have a DB in simple recovery mode (which we want). At some point in the past, the LDF grew to be 20ish GB, and I'd like to shrink that.

However, I don't want to shrink it if some process is simply going to grow the file again. Is there a query/table/object that might help return information about transactions that caused the file to grow? Or perhaps is there a table that indicates when the file grew in the past?

Thanks!

Not directly.  Depending on the version of SQL you have, some of the DMVs may give you a little information about hard hitting queries:

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

The first thing I would check is for a database maintenance plan that is doing a DBCC or Index Rebuild.


Are there benefits to using more than 4 CPUs for MySQL? We are running MySQL 5.5 on linux under VMWare, running on 2 CPUs.  We are planning on increasing this to 4, but we have un-used CPUs available, and I'm wondering if there is any benefit to increasing the number to 8?

MySQL Server will only execute each query in a single foreground thread, there is no support for multi-thread execution at SQL side. It also executes maintenance operations like ALTER TABLEs that can rebuild a whole table in a single thread.

However, engines like InnoDB, specially in recent versions, are able to perform its background threads concurrently, meaning that certain IO operations like flushing data, precaching and data purging can happen simultaneously, with some "laboratory" benchmarks by MySQL vendors showing that it can scale up to 64 cores under high stress.

Having said that, please understand that in most cases, a database like MySQL is not CPU-bound, but IO-bound, meaning that it is not the CPU power that is causing the bottleneck in latency, it is the read and write latency and throughput of the secondary storage. There are certain cases where you can be CPU-bound, for example, in some special cases while performing certain checksums in a FusionIO device, if you need SSL/other kind of encryption, if you perform complex mathematical operations at SQL level, or applying row filters that can be done exclusively on-memory.

If you have suffered CPU spikes in the past, or have constant high CPU usage, it may help, but only assuming you can parallelize the query computation: it won't made ALTER TABLES or SQL parsing faster for a single query. That, combined with the "lightweight" approach that many of us recommend when working with MySQL (trying to avoid business logic -stored procedures- at MySQL side) makes the laboratory benchmark that I mentioned before difficult to reproduce in reality. Getting to use the extra concurrency sometimes requires a recent version of MySQL and configuration changes. In general, for MySQL, it is way better to invest in more powerful cores than many of them.


Spliting Datetime into 2 Columns for Restaurant Reservation Schema? Using PostgreSQL.

The primary driver of this is that while no reservation will exist without a date and time, a user can search for and monitor reservations using a time or date or both. With a single column I'm constantly going to have to break this apart and search via date_trunc.

While PostgreSQL allows one to create an index based on a function, explain shows that this is not being used for conditions using date_trunc.

Table:

development2=# \d reservations
                                        Table "public.reservations"
     Column      |            Type             |                         Modifiers
-----------------+-----------------------------+-----------------------------------------------------------
 id              | integer                     | not null default nextval('reservations_id_seq'::regclass)
 tops            | integer                     | not null
 price           | numeric                     |
 state           | integer                     | not null
 restaurant_id   | integer                     | not null
 seller_id       | integer                     |
 customer_id     | integer                     |
 created_at      | timestamp without time zone | not null
 start           | timestamp without time zone |
Indexes:
    "reservations_pkey" PRIMARY KEY, btree (id)
    "start_date_index" btree (date_trunc('day'::text, start))
Foreign-key constraints:
    "reservations_customer_id_fk" FOREIGN KEY (customer_id) REFERENCES users(id)
    "reservations_restaurant_id_fk" FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)
    "reservations_seller_id_fk" FOREIGN KEY (seller_id) REFERENCES users(id)

Query:

development2=# explain select id,start from reservations where date_trunc('day', start) = '2014-03-14';
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Seq Scan on reservations  (cost=0.00..1.01 rows=1 width=12)
   Filter: (date_trunc('day'::text, start) = '2014-03-14 00:00:00'::timestamp without time zone)
(2 rows

)

Not even sure how to approach the time search, to_char + IMMUTABLE I guess...

timestamp vs. date and time
If you are interested in the date part of your timestamp, it is cheaper to just cast it. The result is an actual date, while date_trunc() returns a timestamp! (You could cast that to date in turn, but don't bother.) Your index should be:
CREATE INDEX start_date_index ON reservations (cast(start AS date));

Use the more verbose standard notation cast(column AS type) in the index definition. The Postgres shorthand would require an additional set of parentheses in this context: (column::type). You can still use either notation in queries to match:
SELECT id, start
FROM   reservations
WHERE  start::date = '2014-03-14';

See:

How to create an index on an integer json property in postgres

You can do similar things with start::time if needed.
Either way, I would certainly not split the date and time part. A timestamp is almost always the superior design.
Index tuning
Depending on your actual use cases, you could make that a multi-column index including the id:
CREATE INDEX start_id_date_index ON reservations (cast(start AS date), id);

That would serve the above query perfectly, possibly even with an index-only scan. And since you are now using an actual date in the index (4 bytes), an additional integer column (also 4 bytes) is just perfect for performance. See:

Is a composite index also good for queries on the first field?

What's more, you can also just use a plain index on the timestamp column:
CREATE INDEX start_start_index ON reservations (start);

Just make sure your WHERE conditions are "sargable". Like:
SELECT id, start
FROM   reservations
WHERE  start >= '2014-03-14 0:0'::timestamp
WHERE  start <  '2014-03-15 0:0'::timestamp;

This will use the index.
The best solution depends on the sum of all your requirements. Also, Postgres can use your original, less optimal index nonetheless. How many rows are there in your table? With only few, a sequential scan is always faster and you won't see an index scan.


How expensive is a SELECT that returns an empty result set? I'm analyzing the HTTP logs to one of our web services and I've noticed a lot of request to an endpoint such as /entities/0 which will end up effectively doing something like SELECT x, y, z FROM entities WHERE Id = 0, and I know for a fact there's no entity with Id = 0.

There are approximately 600,000 requests to this endpoint in a 24hr period. Each request opens it's own connection to the SQL Server and executes the query which returns an empty result.

I'm wondering how bad this actually is. What overhead is associated on the SQL Server with having an open connection, and what overhead with repeatedly returning the empty result for the same query (WHERE Id = 0), does SQL Server cache and optimize for this?

We're using SQL Server 2012.

That is about 7 requests per second if the rate is flat over the 24 hour period, which shouldn't have a noticeable impact on a decently specced SQL Server machine. If they are far more bunched then they may be more of a problem.

I would be inclined to investigate where the requests are coming from. If they don't do anything useful then they may indicate a bug in the application.

SELECT <something> FROM <table> WHERE <column>=<value_that_doesn't_exist> is not going to tax the query planner or engine really, the work top bring up and tear down a connection will be more arduous to the SQL infrastructure than the work to lookup a cached query plan and read a couple of pages to see the row does not exist.

Assuming that the Id column is the table's clustering key there will be at most a couple of page reads and they will be from RAM as they are not ever going to leave the buffer cache due to how frequently they are accessed. If that column isn't the clustering key then add another page or two to those read (again, almost certainly from RAM not disk). If the column isn't indexed at all (I'd be surprised if that is the case given it is called "Id") then things are potentially more problematical as there will be a table scan involved.


Using varchar_pattern_ops in a multi-column index in Postgres I'm using postgres 9.5

If I have a table with 2 columns like so:

CREATE TABLE mystuff
(
  somestring character varying(256),
  timestamp_ timestamp without time zone NOT NULL
)

Will this multi-column btree index:

CREATE INDEX mystuff_idx
  ON mystuff
  USING btree
  (timestamp_ , somestring varchar_pattern_ops);

aid in performance of the following query ?

Select count(*)
FROM mystuff
where timestamp_ > '01/01/2012' and somestring like 'foo%'

what about ?

Select count(*)
FROM mystuff
where timestamp_ > '01/01/2012' and somestring like '%foobar'

For extra point please explain how a multi-column btree is used for lookup when there is a like clause in the second (or 3rd etc...) column of the index.

The two-column btree index will help with the like 'foo%' query, but probably not dramatically so.  It helps because it can be executed as an index-only scan, and so it can compute the LIKE portion within in the index without ever having to visit the table.  The index scan will jump to the first entry > '01/01/2012', and then traverse from there to the logical end of the index.  At each entry it will test the LIKE condition.  For the ones that pass that condition, it will check the visibility map to see if the table page holding that tuple is all visible.  If it is all visible, it will increment the counter and move on.  If not, it will have to visit the table page to see if the tuple is visible.

How much of a help this will be is hard to predict, as it depends on the size of the index, the table, your RAM, what mix of queries you are running (which changes which kind of data is likely to be found in the cache), among other things.

It will not help with the `like '%foobar' query.  From the outside looking in, there is no reason it couldn't help in the same way.  But PostgreSQL's index machinery just hasn't been made clever enough (yet) to recognize and implement that possibility.


Update SQL n:n table with multiple associations I have done this before, but before I mindlessly repeat something that I consider as a hack, I'm asking here.

I have 3 tables (details left out for clarity) :

inv_items

id bigserial (PK)
sku character varying(24)
name character varying(32)
...

inv_item_groups

id bigserial (PK)
name cahracter varying(32)
...

inv_item_group_members

item_group_id bigint (FK -> inv_item_groups)
item_id bigint (FK -> inv_items)

Now, in my code, I have an object like so (in pseudo-code)

class ItemGroup
   id:long
   groupName:String
   items:long[]

and these objects can be modified, then needs to be updated. Since I want to preserve the key integrity, I need the inv_item_group_members table (otherwise, I would've used other solutions).

Now, the usual way I was doing this was to

DELETE FROM inv_item_group_members WHERE item_group_id = $1
-- where $1 is the object's id

the, for each items in the object

INSERT INTO inv_item_group_members (item_group_id, item_id) VALUES ($1, $2)

Is there a better solution? What are the alternative? I'm thinking of a SQL function, but not really sure what's the best approach here (I'm not very experienced with PGSQL, yet.) I have read about writable CTE, but it does not address the case when elements are removed from the array (i.e. association removed).

My suggestion is that you only delete from inv_item_group_members the items you deleted from the object and only insert into it the new items you added to the object.

You can do that by creating an itemsToDelete array, which you populate with item_ids each time you call the removeItem method of the class and an itemsToInsert which you populate each time you add an item whith the addItem method.

Then when you call the save method you delete the items whose item_id are present in the itemsToDelete array (obviously restricted also to group_id). Then you proceed to insert items present in the itemsToInsert array.

To make it resilient, you can either use a Map instead of arrays in order to prevent duplicates, or catch and ignore PK exceptions during the insert.


Events-Users Database Design I am creating a web interface for upcoming events in a community. The users can create an event, join an event, and also leave an event. The interface will list the upcoming events with the following fields: Event name, event datatime, event location, event participants.

My question is how should I store the users for each event? I am using Mysql. Since users can create a new event, and they can also join or leave an event by others, is it better for me to create 2 link tables? One to store the creation, the other to store the Join/leave?

I hope this question is specific enough, and any suggestions are gratefully received.

An "Event" has a Creator ("users can create an Event"), Members (those who have "Joined" and not "Left" the event) and a Location.

You should have the entity Event with EventID, Creator, other fields such as Location, description, category, date created (optionally) Represented as an Event table with EventID, CreatorID (foreign key to Users table), maybe other fields as needed.

Then have a table EventUser or similar, which is a "bridge" table between Event and User. Field names EventID, UserID which keeps track of users registered to the particular event.

User table is self explanatory with UserID plus whatever other fields are needed for the user.

Do you need to keep track of the "history" of created/deleted event, joined/left event? Then add appropriate tables accordingly and ensure the event is updated accordingly. e.g. add an entry to a table saying that a User was added or deleted to the particular event. You could do this via the business logic in the application itself, or perhaps with an update/insert trigger.

Presumably only the Creator of an event can change details such as the date and location, so you will need to take that into account in the way your application updates these.


Replace a sequential set of numbers with special character I have a varchar(200) column that contains entries such as,

ABC123124_A12312
ABC123_A1212
ABC123124_B12312
AC123124_AD12312
A12312_123
etc..

I want to replace a sequence of numbers with a single * so that I can group the distinct non-numeric patterns in the table.

The result for this set would be
ABC*_A*
ABC*_B*
AC*_AD*
A*_*

I have written the following primitive query below, it works correctly, but takes a long time to run on a huge table.

I need help with rewriting or editing it to improve it's performance. SQL Server 2014

-- 1. replace all numeric characters with '*'
-- 2. replace multiple consecutive '*' with just a single '*'
SELECT REPLACE
        (REPLACE
             (REPLACE
                  (REPLACE
                       (REPLACE
                            (REPLACE
                                 (REPLACE
                                      (REPLACE
                                           (REPLACE
                                                (REPLACE
                                                     (REPLACE
                                                          (REPLACE
                                                               (REPLACE(SampleID, '0', '*'),
                                                                '1', '*'),
                                                           '2', '*'),
                                                      '3', '*'),
                                                 '4', '*'),
                                            '5', '*'),
                                       '6', '*'),
                                  '7', '*'),
                             '8', '*'),
                        '9', '*')
                  , '*', '~*') -- replace each occurrence of '*' with '~*' (token plus asterisk)
             , '*~', '') -- replace in the result of the previous step each occurrence of '*~' (asterisk plus token) with '' (an empty string)
        , '~*', '*') -- replace in the result of the previous step each occurrence of '~*' (token plus asterisk) with '*' (asterisk)
        AS Pattern
FROM TABLE_X

Data

The column includes letters and numbers [A-Za-z0-9] and may also include the special characters / and _. I want to replace any sequence of numbers with *, but I do not know if the entry has special characters, and if so how many special characters.

I also do not know how many sequences of numbers are in the entry. All I know is that an entry must have a minimum of 1 number sequence.

Two factors are important for performance:

Reduce the number of string operations.
You may find it is possible to implement what you need using e.g. CHARINDEX and PATINDEX to find the start and end of groups, rather than performing very many REPLACE operations on the whole string each time.

Use the cheapest collation that provides correct results.
Binary collations are the cheapest. SQL collations (on non-Unicode data only) are a little more expensive. Windows collations are much more expensive.

For example:
DECLARE @T table
(
    SampleID varchar(200) NOT NULL UNIQUE
);

INSERT @T
    (SampleID)
VALUES
    ('ABC123124_A12312'),
    ('ABC123_A1212'),
    ('ABC123124_B12312'),
    ('AC123124_AD12312'),
    ('A12312_123'),
    ('999ABC888DEF');

SELECT
    T.SampleID,
    Pattern =
    (
        SELECT
            CASE
                WHEN Chars.this NOT LIKE '[0123456789]' THEN Chars.this
                WHEN Chars.prev NOT LIKE '[0123456789]' THEN '*'
                ELSE ''
            END
        FROM dbo.Numbers AS N
        OUTER APPLY
        (
            SELECT
                SUBSTRING(Bin.string, N.n, 1),
                SUBSTRING(Bin.string, N.n + 1, 1)
        ) AS Chars (prev, this)
        WHERE
            N.n BETWEEN 1 AND LEN(Bin.string)
        ORDER BY N.n
        FOR XML PATH ('')
    )
FROM @T AS T
OUTER APPLY (VALUES('$' + T.SampleID COLLATE Latin1_General_100_BIN2)) AS Bin (string);

db<>fiddle demo
That example relies on a permanent table of numbers. If needed, a table sufficient for varchar(200) is:
-- Create a numbers table 1-200 using Itzik Ben-Gan's row generator
WITH
  L0   AS (SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT
    -- Destination column type integer NOT NULL
    ISNULL(CONVERT(integer, N.n), 0) AS n
INTO dbo.Numbers
FROM Nums AS N
WHERE N.n >= 1
AND N.n <= 200
OPTION (MAXDOP 1);

-- Add clustered primary key
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_Numbers_n
PRIMARY KEY CLUSTERED (n)
WITH (SORT_IN_TEMPDB = ON, MAXDOP = 1, FILLFACTOR = 100);

If that isn't faster, you might find that using a binary collation alone would speed up your existing implementation sufficiently. To implement that, change one line of your code to:
(REPLACE(SampleID COLLATE Latin1_General_100_BIN2, '0', '*'),

Users of SQL Server 2017 or later can leverage the built-in TRANSLATE function, which may perform better than the nested REPLACE calls.
You could also use a general regex CLR function, or implement something custom in SQLCLR for this particular task. See for example SQL Server: Replace with wildcards?
Using the SQL# library, a complete solution would be:
SELECT
    T.SampleID,
    SQL#.RegEx_Replace4k(T.SampleID, '\d+', '*', -1, 1, 'CultureInvariant')
FROM @T AS T;

Full regex support is overkill for this task, so if you are able to use SQLCLR, coding a specific function for your needs would probably be the best performing solution of all.


How do I preserve leading zeros for varchar field When saving a number with leading zeros into a string variable or column, leading zeros are truncated.
When I run this query:
declare @varchar as nvarchar(50)
set @varchar = 011
print @varchar

The result I get is 11.
Is there any way to get 011?

In your code sample, 011 is a numeric, not a string. SQL Server simplifies 011 to 11 it's numeric equivalent, then saves 11 as the string you see in your @varchar variable.
If you want the number to be treated as a string, and not a numeric, simply wrap it in quotes. Additionally, since your variable is unicode nvarchar and not simply varchar, you should prefix the quoted string with an N.
This will print the string, preserving leading zeros, using nvarchar as you do above:
DECLARE @nvarchar AS nvarchar(50);
SET @nvarchar = N'011';
PRINT @nvarchar;

But the same holds true with varchar as well:
DECLARE @varchar AS varchar(50);
SET @varchar = '011';
PRINT @varchar;


SSIS - Update Specific Cell in CSV I have a pre-defined csv template.
The header row begins on row 5.

Prior to this I have a date value in cell B2.

How do I update this cell value in SSIS?

CSV would not have the cell ordinals you have specified. This happens only when you try to open your file in Excel. In order to emulate this, you will have to read the content of 2nd row and 2nd column.

This can be done using a Script Task in SSIS.


Postgres Count with different condition on the same query EDIT Postgres 9.3

I'm working on a report which has this following schema: http://sqlfiddle.com/#!15/fd104/2

The current query is working fine which looks like this:

Basically it is a 3 table inner join. I did not make this query but the developer who left it and I want to modify the query. As you can see, TotalApplication just counts the total application based on the a.agent_id. And you can see the totalapplication column in the result. What I want is to remove that and change the totalapplication to a new two column. I want to add a completedsurvey and partitalsurvey column. So basically this part will become

SELECT a.agent_id as agent_id, COUNT(a.id) as CompletedSurvey
FROM forms a WHERE  a.created_at >= '2015-08-01' AND
a.created_at <= '2015-08-31' AND disposition = 'Completed Survey'
GROUP BY a.agent_id

I just added AND disposition = 'Completed Survey' But I need another column for partialsurvey which has the same query with completedsurvey being the only difference is

AND disposition = 'Partial Survey'

and

COUNT(a.id) as PartialSurvey

But I dunno where to put that query or how will be the query look like.So the final output has these columns

agent_id, name, completedsurvey, partialsurvey, loginhours, applicationperhour, rph

Once it is ok then applicationperhour and rph I can fix it myself

If I understand you correctly, you are looking for a filtered (conditional) aggregate:

SELECT a.agent_id as agent_id,
       COUNT(a.id) filter (where disposition = 'Completed Survey') as CompletedSurvey,
       count(a.id) filter (where disposition = 'Partial Survey') as partial_survey
FROM forms a
WHERE a.created_at >= '2015-08-01'
  AND a.created_at <= '2015-08-31'
GROUP BY a.agent_id;

Edit
for older versions (< 9.4) you need to use a case statement:

SELECT a.agent_id as agent_id,
       COUNT(case when disposition = 'Completed Survey' then a.id end) as CompletedSurvey,
       COUNT(case when disposition = 'Partial Survey' then a.id end) as partial_survey
FROM forms a
WHERE a.created_at >= '2015-08-01'
  AND a.created_at <= '2015-08-31'
GROUP BY a.agent_id;


DR For MySQL multi master nodes I’ve planned to build MySQL master-master replication and need DR for this setup. I was  thinking for replication from any of the node as a slave.

Please help, which option is best and how to switch over back if any disasters happen?

For "dual-master", it is best to write to only one of them.

Failover involves (mostly) pointing the clients to the newly-writable master.

MHA automates the process; read about it.

In my opinion, dual-master only handles a limited number of failure scenarios, namely the crash of a server.  The next step is to separate the masters so that a power failure, earthquake, tornado, etc cannot take out both at the same time.  But then you get into the "split-brain" problem, wherein you can't tell which Master is the surviving one because you can't check both of them.  (And both might be surviving!)

Next comes Galera, which needs 3 nodes, and is self-healing.  It comes standard with MariaDB.

If you want the MySQL path, then you need to get beyond 5.6 in order to get a reasonably robust "Group Replication" / "Innodb Cluster".  (8.0 is desired for that.)


Cardinality discrepancy I have two, almost identical, databases and I am trying to figure out what is causing queries to perform that much slower in one of them. While investigating using the information_schema.statistics table I noticed that the cardinality column for one of the indexed fields was triple in size compared to the same column in the other database. e.g.
For the first database the cardinality is

+------------------------+-------------+
| column_name            | cardinality |
+------------------------+-------------+
| date_time              |      46202 |
+------------------------+-------------+

and for the second database:

+------------------------+-------------+
| column_name            | cardinality |
+------------------------+-------------+
| date_time              |      185052 |
+------------------------+-------------+

Since the cardinality is

  An estimate of the number of unique values in the index.

I got wondering about the unique date_time  for this columns. Turns out that they are the same (select distinct date_time from table = 54187).

So if the unique values are exactly the same, how come the cardinality in the second database for the column date_time is almost four times the one on the first database?

The cardinalities are estimates.
They have limited use, so don't worry if the values are as far off as you see.

If you are worried that some queries are being Optimized inadequately, let's see the queries, SHOW CREATE TABLE, and EXPLAIN SELECT ...  There are many things that can be looked at; most do not involve cardinality.

A typical solution involves a "composite" index.  For example:

WHERE a=1 AND b=2

If you have INDEX(a), INDEX(b), the Optimizer will use only one of the indexes and may use cardinality to decide between them.

If you have INDEX(a,b), the decision is obvious, and cardinality is irrelevant, and the query will run faster.