join table and count them SQL based on segment I have 2 tables and I'm trying to join and count how many people have answer
each questions. I'm trying to find a better way to have only two column. the
first one will be of people who answer "Just Me,My Spouse/Partner" and the
other column who people who answer Children 0-5yr, 6-12yr,13-17yr. I'm
trying to figure out a better way to have a table that looks like this

Question                  Answer               Children     Justme/partner
do you buy milk           weekly                400            20
do you consume cheese     daily                 15             300
how many time you drive   daily                 400            220

here are the 2 tables
This is the table called "household" that is provided:

User    JustMe      Children 0-5      Children 6-12    Myspouse/partner
  5      N              N               N                  N
  5      N              N               N                  N
  4      N              N               N                  N
  4      N              N               N                  N
 12      N              N               N                  N
 12     Just Me         N               N              Myspousepartner
 46     Just Me         N        Children 6-12 years   Mypousepartner
 46     Just Me         N        Children 6-12 years   Mypousepartner

Survey table

 User       questionid   answerid   question                   answer
  4          2             5         how often you buy gas     weekly
  46         3             4         how often you buy milk    monthly
  75         3             4         how often you buy milk     weekly
  13         4             2         how often you use coupon   monthly

I used the

  SELECT id,question, Children_0_5_years, Children_6_12_years,
  Children_13_17_years, count(*) AS NEW_COUNT
  FROM table1 inner join table2 on table1.user=table2.user group by...

unfortunately this query return things that I didn't need.

SELECT
    Question,
    SUM(
        CASE  --Case for each column
            WHEN h."Children 0-5" = 'Children 0-5' THEN 1
            Else 0
        END) as "Children 0-5",
    SUM(
        CASE
           WHEN h."Children 6-12" = 'Children 0-5' THEN 1
           Else 0
        END as "Children 0-5"* from household h
 INNER JOIN survey s
 ON h.user = s.user
 GROUP BY s.question, answer

Ok doing this from my brain rather than testing but here is the idea.  Use case statements to turn your string data into a sum-able number.  Then group by the question to aggregate the answers.

If I were you (and you could change the data) I would get away from verbose string fields and make things like the age groups Y, N or even better 1, 0.


MySQL replication much slower on MySQL 5.7 compared to MySQL 5.5 I have two databases setup for testing (so no live data or connections).

The first is a MySQL 5.5.57 Database acting as the master. The second is a MySQL 5.7.23 Database acting as a slave.
It is running Statement Based Replication on MySQL 5.5 and Row Based Replication on MySQL 5.7.

For some reason larger queries seem to take significantly longer on MySQL 5.7. For example I have the following table:

CREATE TABLE `test1` (
  `col0` int(11) DEFAULT NULL,
  `col1` int(11) DEFAULT NULL,
  `col2` datetime DEFAULT NULL,
  `col3 datetime DEFAULT NULL,
  `col4` int(11) DEFAULT NULL,
  `col5` int(11) DEFAULT NULL,
  `col6` int(11) DEFAULT NULL,
  `col7` int(4) DEFAULT NULL,
  `col8` int(11) DEFAULT NULL,
  `col9` datetime DEFAULT NULL,
  `col10` datetime DEFAULT NULL,
  `UniqRef` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col11` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `col12` tinyint(1) unsigned zerofill DEFAULT NULL,
  `col13` tinyint(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`UniqRef`),
  KEY `col0` (`col0`),
  KEY `col3` (`col3`),
  KEY `col4` (`col4`),
  KEY `col8` (`col8`),
  KEY `col5` (`col5`),
  KEY `col10` (`Deleted`),
  KEY `col2` (`col2`),
  KEY `col12` (`col12`) USING BTREE,
  KEY `col13` (`col13`)
) ENGINE=InnoDB AUTO_INCREMENT=400046649 DEFAULT CHARSET=latin1;

The table is 21GB in size, and holds 83261829 rows (using count(*)).

If I run the query:
UPDATE test.test1 SET col9 = NOW() WHERE UniqRef IN ('397958600','397940686','397940704','397940678','397. . . . .

The query contains 6945 uniqref entries. It runs almost instantly on MySQL 5.5, but then it replicates to MySQL 5.7 and takes 90 seconds to run.

I have changed various settings, but they have so far made no difference:

SET @@global.slave_compressed_protocol = 0;

SET @@global.sync_binlog = 0;

set @@global.range_optimizer_max_mem_size = 0;

I have also tried changing read_io_threads and write_io_threads to various values, as well as changing the number of buffer pool instances, and disabling the binary logs.

Are there any other variables that may make a difference to try and speed this up that I have missed?

UPDATE 2018-10-17

I managed to speed the queries up when run locally (directly on MySQL 5.7) by setting SET @@global.range_optimizer_max_mem_size = 16777216;. After the this the queries went from 90+ seconds to 0 seconds.

However, when the same query was run on MySQL 5.5 and replicated across to MySQL 5.7, it still takes 90+ seconds to run.

I appear to have found the solution. Simply put I set MySQL 5.5 to also use ROW based Replication, using the following process:

SELECT @@GLOBAL.binlog_format;
STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'row';
SET SESSION binlog_format = 'row';
FLUSH LOGS;
UNLOCK TABLES;
START SLAVE;
SELECT @@GLOBAL.binlog_format;

After this, I ran the large query on MySQL 5.5 (ran instantly), and when it replicated to MySQL 5.7, it also ran instantly.

So there appears to be some problem with MySQL 5.7 processing statement based replication queries from a MySQL 5.5 server.

. . . But the plot thickens. Although the MySQL 5.5 database claimed to be using ROW Based Replication:

SELECT @@GLOBAL.binlog_format; = ROW

and the fact that this appears to have helped with replication to MySQL 5.7, when I grabbed the latest binary logs (an hour later or about 10 binary logs later) and ran mysqlbinlog against them, I was surprised to see that they were still populated with STATEMENTs. It was only after a full restart of the database that the binary logs started producing the new ROW based format ! ! !


Is there a way to tell if a GUID column contains sequential or traditional / random GUID's? Have a table that is populated by Entity Framework 3.1.1 code.  The table contains a clustered index / primary key on ID which is defined as of type GUID with no default value.  Is there a way in SQL to look at the row data and tell if the GUID values are a traditional random GUID vs a sequential GUID?


determine maximum advisory locks supported by Postgres According to the Postgres documentation, the maximum number of regular and advisory locks is limited by a shared memory pool:

Both advisory locks and regular locks are stored in a shared memory pool whose size is defined by the configuration variables max_locks_per_transaction and max_connections. Care must be taken not to exhaust this memory or the server will be unable to grant any locks at all. This imposes an upper limit on the number of advisory locks grantable by the server, typically in the tens to hundreds of thousands depending on how the server is configured.

How can I determine the size of this pool? Is this the same thing as shared buffers, which I can see with show shared_buffers; or is it something different? I am trying to determine roughly how many advisory locks my installation would be able to support because I am doing a ton of locking. My shared_buffers size is 5012MB.
I also have a couple more detailed questions:

If the server was unable to grant an advisory lock when I called pg_advisory_xact_lock(), would it hang, error out, or fail silently? As long as it doesn't fail silently I'm good, although ideally it would hang and then continue once memory frees up.
I locking not only with advisory locks but also with SELECT ... FOR UPDATE. If I know the size of the pool, how can I calculate roughly how much space in the pool each advisory lock takes, and each SELECT ... FOR UPDATE takes? I know roughly how many rows will be impacted by each SELECT ... FOR UPDATE.

The documentation is a little confusing because if you look at the documentation for max_locks_per_transaction it says:

The shared lock table tracks locks on max_locks_per_transaction * (max_connections + max_prepared_transactions) objects (e.g., tables); hence, no more than this many distinct objects can be locked at any one time. This parameter controls the average number of object locks allocated for each transaction; individual transactions can lock more objects as long as the locks of all transactions fit in the lock table. This is not the number of rows that can be locked; that value is unlimited.

This seems to track with the idea that the memory pool is equal to max_locks_per_transaction * max_connections described earlier, but here it is saying that the max has more to do with the number of tables and not the number of rows. I'm not really sure how to square this with the first quote, or how this relates to the space taken by advisory locks.
Any tips on calculating would be greatly appreciated!

The lock table is a shared memory area with room for max_locks_per_transaction * (max_connections + max_prepared_transactions) locks. It is allocated at server startup time and never resized.
This is the limit for advisory and all other locks. Like the documentation says, row locks are stored on the row and not (permanently) in the lock table.


How to query SQL Server Memory Manager performance counters in DMV? Is there any system catalog views or DMVs that can be used to query (in SSMS for example) below performance counters from SQL Server:Memory Manager ?

Free Memory (KB)
Target Server Memory
Total Server Memory
Maximum Workspace Memory (KB)
Database Cache Memory (KB)
Granted Workspace Memory (KB)
Lock Memory (KB)
Log Pool Memory (KB)
Optimizer Memory (KB)
Connection Memory (KB)
SQL Cache Memory (KB)
Reserved Server Memory (KB)
Stolen Server Memory (KB)

The information you seek is available in the DMV sys.dm_os_performance_counters.

select object_name,
       counter_name,
       instance_name,
       cntr_value,
       cntr_type
  from sys.dm_os_performance_counters
 where 1=1
   and [object_name] = 'SQLServer:Memory Manager'


Proper way to preserve data for mariadb docker container I am running a single mariadb container as a backend for a webapp. I restarted the container, and the only data remaining was from a long time ago. None of the recent transactions were available. They are seemingly all gone.

I thought that I had prevented this from happening by using volumes for the database files, but after talking to one of my DBA colleagues, I went back and checked, and sure enough, the last modification date for those files was far in the past.

My question is that if shutting down a mariadb docker container wipes all of the transactions from memory without writing them to disk, how am I supposed to preserve data during a database restart?

FWIW, I am using the official mariadb docker container image, which kills the process with a SIGTERM, and my data volume is linked to /var/lib/mysql, which is where I understood all of the data to be stored.

Is this still relevant?

You haven't mentioned what your host is (I take it it is Windows). From what you mentioned, I assume also, you used mapping to host system (full path) and not. named (docker managed) volumes.
In addition it would be important to know -- if your host is indeed windows -- how you installed docker (docker desktop in hyper-v, virualbox/vmware)

I go by what I got and guess to start:

The constallation Docker on Windows and host volumes -- especially for databases -- is known to cause problems (see e.g.
https://github.com/docker-library/mariadb/issues/152 or https://stackoverflow.com/questions/41343972/docker-volumes-mariadb-windows)

If my guesses are right, then two things strike me as strange though:
1. How could this mariadb docker ever have worked and
2. where do those old database-files come from?
Is it possible these are an old artefact from a different (possibly local) installation?

You imply that possibly shutting down the mariadb container some time in the past (SIGTERM) lost the connection to the volume and on restart continued to work but that does not seem to match up with other evidence you provide (it obviously did work well up until your last (first?) restart of the container).

Could something have changed with the access-rights to the files or the folder on the host (although that would speak more for a Linux host)? See https://medium.com/@nielssj/docker-volumes-and-file-system-permissions-772c1aee23ca for further information. Again, this does not explani why  mariadb did still run up until the problem you noticed.

Closing (again assuming this is Docker on Windows) your problem might be much smaller now (speaking after Windows 10 2004 was released) using Docker in WSL 2 because you can use named (docker managed) volumes and still easily access these as linux folders in wsl 2 are directly mounted in Windows.

Sorry, this is probably not the answer you expected. If you clarify your situation I (or somebody else) might be able to be more specific.


How to get full query text from performance_schema.threads? According to documentation:
Access to the threads table does not require a mutex
and has minimal impact on server  performance. The other
sources have negative performance consequences because they require a mutex.

So yes, I want to use threads table to examine running queries. The trouble is, the PROCESSLIST_INFO column does not contain the full query. It just contains a truncated version of the query. There is a flag viz performance_schema_max_sql_text_length which controls how many bytes are allocated for the SQL query. But it seems to affect the events_statements_history_long table. There is no change in the thread table output. Am I missing something?

As I read the manual, ... (In other words, I did not test the following.)
It cannot be set dynamically; the manual is clear on that.  Instead, you should be able to set performance_schema_max_sql_text_length in the config file (my.cnf, etc).  That would require a restart of the server.
Note: The setting is limited to 1MB.


SQL Server Point in Time Restore Scenario Hopefully you can help out on this as I may be up a creek!

I am working with a database using the FULL recovery model. The database has had nightly database backups but their transaction log has never been backed up (according to SQL Server Management Studio).

If I restore from one of the old database backups, would it be possible to take a backup of the current transaction log and bring the restored database forward to a point in time prior to a terrible transaction that got pushed through?

If the log has never been backed up, then it could have been shrunk or truncated at some point. otherwise, it would have just grown. It's also possible that it has changed to SIMPLE and back to FULL.

You have one option: try it...


Is there any reason that metadata should not be a part of my database schema? By metadata I mean things such as:

the owner of an entity
permissions associated with an entity
the date and time of data entry
the surety of entered data
the format of a text attribute
descriptive names for attributes

This is all just data as far as I can tell. I might implement entity ownership by giving an Entity table a foreign key to a User table; I might store all text fields as two fields: content and format. And so on...

Is there any difference between data and metadata as far as practical database design is concerned? Why might I want to store my metadata somewhere other than my database?

Most DBMS already do store metadata in the database.  This data is general stored in what is broadly referred to as "system tables".

The metadata that a DBMS will store already will be what it needs to operate the database.  Some of the types of metadata you've mentioned, e.g. ownership, permissions, and possibly even formats and descriptive names are already stored in your DBMS's system tables.

Other types of metadata (e.g. date/time of data entry) are record oriented, as opposed to the table or column oriented metadata that you can find in your DBMS's system tables.  Record oriented metadata needs to be stored using a mechanism that you design and implement yourself.  Typically, this means storing the record oriented metadata with your records directly.  In some cases, you might find it helpful to normalize out some kinds of record oriented metadata into a common repository.  An event log that tracks events for multiple tables is an example of such a common repository.

How you handle your metadata depends on what you need it for.  As a matter of best practice, let the DBMS do what it is designed to do.  Don't track metadata manually if the DBMS is doing it automatically.  Only manually track the extra metadata.


ALTER TABLE blocking in MySQL 5.6 I have a question with a MySQL 5.6 master-slave setup. If I were to modify the master's schema with an ALTER TABLE SQL query, then when the slave tries to execute that ALTER TABLE statement too, would it just block all reads to just that specific table until that query finishes executing? I assume for sure that not all tables would get blocked but I am concerned if the table being ALTER-ed itself is going to get blocked.

I did some research on my question and am referring to the online ALTER TABLE operation at http://blogs.oracle.com/mysqlinnodb/entry/online_alter_table_in_mysql

I have heard from others that the slave does not block reads in this case if it were running 5.1 MySQL, but does it block for 5.6?

And yes, all the tables in the DB are InnoDB

Thanks!

If your table is InnoDB format, then try pt-online-schema-change (from Percona Toolkit), for example:

pt-online-schema-change
    --charset utf8 \
    --alter 'ADD COLUMN baz INT UNSIGNED' \
    --execute -h db-test -u username -p password D=foo,t=bar


XML vs. Relational databases? I feel like this isn't the best question to ask on a programming site like this, but I haven't really found a satisfying answer online and I would really like to get a different perspective on this.

Currently, we're working on a project at school, but instead of relational databases, we are instructed to use XML databases and related technology (XQuery, XPath and the like). I find that writing the code and infrastructure is quite straightforward, but even after working with it for a while, I can't seem to wrap my head around the advantages of choosing XML databases over a relational model.

My questions, then, are:

Could you describe some advantages that XML databases have over relational?
Could you provide an example where it might be better to use an XML database over a relational one?

I use XML when I need more flexible structure than a database table can offer.

When you have data that you do not know exactly the structure beforehand, it could be profitable to store it as XML. You can store such XML in a relational database field, as many e-learning systems do.

In fact e-learning course structure is a good example because individual courses could be quite different from each other.

So XML is somewhere between a document and a relational-table. Imagine a database table where each record would have a very different structure, that would be good use for XML.

Also XML is very good for data interchange, meanwhile files like .DBF, .XLS etc. are not so good.


Checking Job Statuses As A Daily Task I've been thinking about this a lot recently:
Is there a consensus as to how to check SQL Server Agent jobs have run correctly?

Some advice seems to be alerting the operator on failure and addressing those, but this won't account for jobs that haven't run (say if the agent went down for a time) at all rather than run and failed.
Some advise to use Job Activity Monitor and check the schedules and statuses, which is better but requires you to connect to every server, which can be arduous in a large estate, and maintain a list of job timings.
We currently have a list of jobs per server and have an operator set up that mails to a single DBA account. Daily we go through and check each job in the spreadsheet against the notification emails checking failures or absence of a notification.

I feel like there has to be a better solution, but my googling hasn't brought anything up. Does anyone have a better solution for managing the status of agent jobs?

EDIT -
I should also add that we IDERA SQLDM to monitor some servers, but don't have the budget to look at all our servers. Also our SCOM implementation is in the hands of our server team and currently getting info out of it isn't easy for us. This makes getting reliable information about the status of the agent hard.

Do you have a Central Management Server? If so, you could use this query and run it against all your sql instances on all your servers:

SELECT CONVERT(nvarchar(128), SERVERPROPERTY('Servername')) AS
Server,
msdb.dbo.sysjobs.job_id,
msdb.dbo.sysjobs.name,
msdb.dbo.sysjobs.enabled AS Job_Enabled,
msdb.dbo.sysjobs.description,
msdb.dbo.sysjobs.notify_level_eventlog,
msdb.dbo.sysjobs.notify_level_email,
msdb.dbo.sysjobs.notify_level_netsend,
msdb.dbo.sysjobs.notify_level_page,
msdb.dbo.sysjobs.notify_email_operator_id,
msdb.dbo.sysjobs.date_created,
msdb.dbo.syscategories.name AS Category_Name,
msdb.dbo.sysjobschedules.next_run_date,
msdb.dbo.sysjobschedules.next_run_time,
msdb.dbo.sysjobservers.last_run_outcome,
msdb.dbo.sysjobservers.last_outcome_message,
msdb.dbo.sysjobservers.last_run_date,
msdb.dbo.sysjobservers.last_run_time,
msdb.dbo.sysjobservers.last_run_duration,
msdb.dbo.sysoperators.name AS Notify_Operator,
msdb.dbo.sysoperators.email_address,
msdb.dbo.sysjobs.date_modified,
GETDATE() AS Package_run_date,
msdb.dbo.sysschedules.name AS Schedule_Name,
msdb.dbo.sysschedules.enabled,
msdb.dbo.sysschedules.freq_type,
msdb.dbo.sysschedules.freq_interval,
msdb.dbo.sysschedules.freq_subday_interval,
msdb.dbo.sysschedules.freq_subday_type,
msdb.dbo.sysschedules.freq_relative_interval,
msdb.dbo.sysschedules.freq_recurrence_factor,
msdb.dbo.sysschedules.active_start_date,
msdb.dbo.sysschedules.active_end_date,
msdb.dbo.sysschedules.active_start_time,
msdb.dbo.sysschedules.active_end_time,
msdb.dbo.sysschedules.date_created AS
Date_Sched_Created,
msdb.dbo.sysschedules.date_modified AS
Date_Sched_Modified,
msdb.dbo.sysschedules.version_number,
msdb.dbo.sysjobs.version_number AS Job_Version
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.syscategories ON
msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
LEFT OUTER JOIN msdb.dbo.sysoperators ON
msdb.dbo.sysjobs.notify_page_operator_id = msdb.dbo.sysoperators.id
LEFT OUTER JOIN msdb.dbo.sysjobservers ON
msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobservers.job_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules ON
msdb.dbo.sysjobschedules.job_id = msdb.dbo.sysjobs.job_id
LEFT OUTER JOIN msdb.dbo.sysschedules ON
msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id
WHERE msdb.dbo.sysjobs.enabled = 1
AND msdb.dbo.sysjobservers.last_run_outcome = 0


Trigger Definer Already Same But User Still Denied currently I already have user like this 'app_user'@'10.148.0.0/255.255.240.0'
and I have the trigger with definer like this
CREATE DEFINER=`app_user`@`10.148.0.0/255.255.240.0` TRIGGER usertable_delete_audit

but when apps insert data i got this error
trigger command denied to user 'app_user'@'10.148.0.0/255.255.240.0' for table user table.

i already make sure about the grants this user already full grant.
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON

this impact from user with specific host ? i mean because the user have grant like this 10.148.0.0/255.255.240.0. And the trigger only can give the access from specific ip from server apps ?
my server apps ip 10.148.0.57
i using mariadb version 10.4

Per documentation on create user, netmasks are only valid on the previous class A,B,C addresses. So masks such as 255.255.240.0 are invalid.
I haven't found a feature request for this feature, however obvious it seems, on https://jira.mariadb.org if you'd like to request one.


Can I re-replicate a transactional publication? Should I? We use a cloud-based SAAS provider as one of our line-of-business applications.  An on-site SQL Server 2008 R2 instance in our DMZ is a transactional replication subscriber to a number of publications on the provider's SQL Server.

We are interested in building a reporting server containing much if not all of the data currently being replicated from our provider.  What is the most appropriate mechanism, if any, to support this objective if we wish to accomplish it in-house (as opposed to the possibility of adding the reporting server as a second subscriber to the provider's publications)?

Conceptually, the most appealing prospect would be to establish our current subscriber as a publisher in its own right and re-replicate the data to the reporting server, but that seems fraught with risk -- our publications total more than 250GB of data, and any issue necessitating the re-initialization of every subscription to our provider's publications would result in a 24-to-36-hour outage.  The biggest upside to "re-replicating" would be the ability to optimize indexes on the reporting server, which to my knowledge would not be practical or even possible using log shipping or database mirroring.

Is there a way to accomplish this in-house using the standard SQL Server stack?  If so, what are the pros and cons?  Or would our best option be to look into additional subscriptions to the SAAS publications?

Yes, replication can be setup as per requirement. kindly follow below steps to configure replication :

Configure transactional replication between primary and secondary server. Make sure initialization of objeccts are required to again setup replication.
kindly follow below article to setup replication again between secondary and reporting server.

https://www.mssqltips.com/sqlservertip/2386/initialize-sql-server-replication-using-a-database-backup/
3. Make sure to add appropriate indexes on replicated objects to avoid latency. check procedure_stats for more detail.

I hope this would help to setup.


SQL Server Backups in Parallel Currently I am using ola hallengren  backup and maintenance scripts.

I have a question:

I have 5 big databases in one server. It's taking approximate 10+ hours everyday to complete full backups. Currently it's writing in sequentially into disk.

I want to write all backups parallel to reduce time. Is there any way I can write backups parallel?

I am taking compressed and verify only backups to network location

Create additional jobs to run backups in parallel.  Make sure to specify a single unique database per job.  Configure the SQL Server Agent jobs to start the backup at approximately same time, with a 1 minute delay between each job.

You may find that running backup jobs simultaneously does not significantly decrease backup time.  You should evaluate the I/O capability of the disks where the databases reside, the disks where the backups are being written to, and in the case of network backups, the capacity of the network to transfer data.


pgsql Return Partial Text Field Ending In A Whole Word I found this PHP code that returns a sub-string ending with a whole word (IE separated by a space):

if ( strlen( $body ) < $maxlength ) return $body;
$body = substr( $body , 0 , $maxlength );
$rpos = strrpos( $body , ' ' );
if ( $rpos > 0 ) $body = substr( $body , 0 , $rpos );
return $body;

Is it possible to achieve the same result using pgsql's string manipulation functions?

Something like this should work (here 15 is the equivalent of $maxlength):

SELECT
  CASE WHEN LENGTH(mycolumn) <= 15
       THEN mycolumn
  ELSE SUBSTRING(mycolumn, 0, 15 + 1 -
                 POSITION(' ' IN REVERSE(SUBSTRING(mycolumn, 0, 15 + 1))))
  END
  FROM mytable

Here is a working demo.


Recursive relationship database So I have an university project that has to implement some kind of a recursive relationship like (for example - employee may be managed by other employee(https://www.sqa.org.uk/e-learning/SoftDevRDS02CD/page_49.htm)). Anyway I found it hard to think of an appropriate domain problem. Can you give me some examples? (Examples: categories of an online shop etc...)

As well as management hierarchy, large businesses sometimes have divisional structures (country, area, region, city, branch) of fixed or variable depth. Geographic classification of places more generally could be a useful example too. Any taxonomy (books, living creatures, ...) or most other classification systems, depending on any additional constraints you have been given.

Or family trees. If you ignore complications where parentage is not known or legally changes, both the maternal and paternal lines are potentially infinite tree structures. If you consider them both together rather than as two separate structures connecting the same set of nodes, they form a directed graph which might also be within your spec.

If graphs generally are allowed then that opens up the field even more: for instance places connected by roads, more general relationships between people, or links between articles on something like Wikipedia or the web more generally. Heading back to the more specific (and back to a tree/forest structure) the posts on forums like Reddit of HackerNews.

Recursive structures can appear everywhere that you model things connected or related to each other.

If you are looking for existing data sets to analyse rather than things to model, then there are a great many out there to be found. /r/datasets might be a good place to start, for instance this post links to a dump of HN data.


How do I query the running pg_hba configuration? I want to test if a replication connection is authorized by pg_hba.conf on the provider before issuing the replication-starting command, and don't know how. (I have access to both unix and postgresql shells on both nodes)

For the non-replication connection, I would connect psql using a connstring like 'host=$MASTER_IP port=5432 dbname=$DATABASE user=$DBUSER password=$DBPASSWORD'

Context:
I am writing a script to automate the setup of replication between servers, and configuration of the servers is managed through different systems/repositories (legacy reasons). Therefore, I want to test if settings are all right at each step.

Version 10 and above

There is a very convenient view for this called pg_hba_file_rules.

table pg_hba_file_rules ;

 line_number │   type    │   database    │  user_name  │  address  │                 netmask                 │ auth_method │ options │ error
─────────────┼───────────┼───────────────┼─────────────┼───────────┼─────────────────────────────────────────┼─────────────┼─────────┼───────
           3 │ local     │ {all}         │ {all}       │           │                                         │ trust       │         │
           4 │ hostssl   │ {all}         │ {+users}    │ 127.0.0.1 │ 255.255.255.255                         │ pam         │         │
           5 │ host      │ {all}         │ {all}       │ 127.0.0.1 │ 255.255.255.255                         │ md5         │         │
           6 │ hostssl   │ {all}         │ {+users}    │ ::1       │ ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff │ pam         │         │
           7 │ host      │ {all}         │ {all}       │ ::1       │ ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff │ md5         │         │
           8 │ hostssl   │ {replication} │ {standby}   │ all       │                                         │ md5         │         │
           9 │ hostnossl │ {all}         │ {all}       │ all       │                                         │ reject      │         │

Up to version 9.6

For earlier versions, it is a bit cumbersome and error-prone (and works only for superusers, see the notes).

The only built-in way of reading pg_hba.conf I can currently imagine is using pg_read_file().

SELECT pg_read_file('pg_hba.conf');

will return you the whole file, in a single row, which then contains the newline characters (be them whichever flavour your OS likes:

[...]
 # TYPE  DATABASE        USER            ADDRESS                 METHOD↵
                                                                       ↵
 # "local" is for Unix domain socket connections only                  ↵
 local   all             all                                     md5   ↵
 # IPv4 local connections:                                             ↵
 host    all             all             127.0.0.1/32            md5   ↵
[...]

You can further process this to your liking.

Notes:

The documentation tells us the following:

  The functions shown in Table 9-86 [pg_read_file() included - me] provide native access to files on the machine hosting the server. Only files within the database cluster directory and the log_directory can be accessed. Use a relative path for files in the cluster directory, and a path matching the log_directory configuration setting for log files. Use of these functions is restricted to superusers.

The bit about superusers shouldn't be a problem for you, as you are setting up replication.  If pg_hba.conf is not in the mentioned directories, you can add a symlink to it to trick the system:

postgres@Z22309: cd /var/lib/postgresql/9.5/main
postgres@Z22309:~/9.5/main$ ln -s /etc/postgresql/9.5/main/pg_hba.conf pg_hba.conf

All this won't tell you if the file was changed but the config not reloaded.  For this, you have to compare the results of pg_conf_load_time() and pg_stat_file(), as shown below.  At the same time, you will still don't know exactly which changes were made since the last reload.  Also, as mentioned in a comment, pg_conf_load_time() will advance even when loading the file was not successful, leading to confusion - at least I am not aware how one could tell the reloading was successful or not.

SELECT pg_conf_load_time() > modification FROM pg_stat_file('pg_hba.conf');

In another answer, I show a way how to split the lines returned above.


SQL Server Profiler behavior? I want to monitor only sp's.

Does SQL Server Profiler listen to all events and then filters the desired data (e.g. sp's)?

Or rather the sql emits  only Profiler pre-configured events ?

SQL Trace (what Profiler is built on) will capture all instances of any events that you decide to collect. So if you collect RPC:Completed events, you'll get an event for every RPC completed, trace will be capturing that event. If you don't select that event, trace won't be grabbing it. So it will only capture what it is told to.

You can (and should) then further filter the data returned by adding filters on various columns (say a database name or a duration for a completed event to capture only events with a duration of greater than a certain value, for example). In that case, all of the instances of that event are still captured, but the filter is then applied before the information is dumped to the mechanism you are using to trace.

See the difference? If you don't select the event, that event isn't captured or sent (Emitted as you are saying, if I comprehend you correctly), but if you do select the event - all instances are captured but only those that pass the filter criteria are sent (emitted).

I would also argue against using profiler but consider doing a Server Side trace to a file. Look at this blog post from Jonathan Kehayias to show the difference in performance impact between the methods.


Monitoring Query which executes thousand of run/minute and is generally fast I am looking for some advise here on one of our SQL database server with below behavior-
Queries running for this databases are generally considered good with avg run time of 20 ms.
Suddenly on some weird days it will go from 20 ms to 80 ms and its very hard for our monitoring process to capture which time exactly it shifted its run time and why-
Our current monitoring method include below 2:-

DMV's from cached TOP SQL queries which does not help much as metrics are cumulative so its hard to find the point in time where issue happened and plan or something changed.

Extended events for RPC COMPLETED, SP statement completed, SQL batch completed and SQL statement completed, but only for queries running over 5 secs.

We have not looked into QUER STORE yet due to some issues i am reading online because this DB is SQL2017 with AG setup in 2 DC configuration. Though there are trace flags for some but our Eng team is hesitant still to use QS on this server which is high OLTP with batch req/sec on avg 30-40K/ secs/
Please suggest or advise if i am missing anything except third party monitoring tool?

For the cumulative DMVs, if you're capturing them routinely, then you just need to compare the differential changes between the timeframe of when your query that you're tracing slows down. Then you should be able to pinpoint abnormalities.
You can also look into the sp_Blitz procedures, specifically sp_BlitzFirst during the moment of contention.


Raise error from Stored PROC in SQL Server PDW I need to do some validation inside a stored procedure before I continue processing but SQL Server PDW (SQL Server 2008 R2) does not support RAISEERROR inside stored procedures.

Is there any other way that I can raise an error with a specific error message inside a stored procedure?

I can try and do something illegal, like force a "division by zero" error, but the error would be misleading.

I'd like to be able to raise an error specifying the exact problem that's occurring.

Any other way?

I don't know if this will work with PDW specifically, but I've got an awful, dirty hack I use with user-defined functions, which also don't allow RAISERROR. Just attempt to cast a varchar literal containing your error message to int.

SELECT CAST('Carburetor failure detected.' AS int)

It's not the prettiest error output, but it's better than nothing.


You should use AVG function if you need to get the average
Using MINUTE function will return the minute part from the date. i.e. 10:05:03 and 11:05:33 will give the same result. (It is fine if the interval is less than 60 minutes, however, it is safer to use the following query:)

SELECT left(timestamp, 16) as mtimestamp, avg(vif_2_rx)
FROM md_metrics_status
WHERE vm_name = 'r-23-VM' AND TIMESTAMP > NOW() - INTERVAL 15 MINUTE
GROUP BY mtimestamp


Mysql performance on varchar with ranges I will try to describe my problem as simple as I can.

I have a db with tables that contains columns with phone numbers ranges, e.g. numbertype_id = 7, number_start = '00110000', number_end = '00119999', numbers = 10000 (of course I have an id pk and unique key index as well).

On the site that uses those tables, I have a search functionality, which should find a number within those ranges (if there is any). For example, user searches for '00112233', and it should return true, since there is a hit.

Now the problem is performance. I am searching only in one table per search, with a max of under 200k rows.

Being varchar columns (and need to be, since with int I would loose the leading 0's, plus the leading zeros could be none, up to 6-7), for me the obvious choice was a search like this:

SELECT *
FROM numberdata
WHERE numbertype_id = 7
  AND number_start <= '00112233'
  AND number_end >= '00112233'

Depending on the table, that could take anywhere between 1.5 to 2.5 seconds, and it's not normal to take that long.

Does someone knows a better solution for that?

IMPORTANT UPDATE

I did not explained one vital detail. No matter that the number searched for returns any result or now, I still have to "offer" 45 alternatives, and have to search for those 45 numbers in part as well. So the total queries are 46 actually.

SHOW CREATE TABLE returns this

CREATE TABLE 'numberdata' (
'id' int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'The primary identifier',
'numbertype_id' int(10) unsigned NOT NULL DEFAULT '0',
'prefix' varchar(5) NOT NULL DEFAULT '',
'number_start' varchar(20) NOT NULL DEFAULT '',
'number_end' varchar(20) NOT NULL DEFAULT '',
'number_count' int(10) unsigned NOT NULL DEFAULT '0',
'owner' varchar(100) DEFAULT NULL,
'owner_city' varchar(100) DEFAULT NULL,
PRIMARY KEY ('id'),
UNIQUE KEY number ('numbertype_id','number_start','number_end')
) ENGINE=InnoDB AUTO_INCREMENT=48690 DEFAULT CHARSET=utf8

And EXPLAIN SELECT... returns this

id select_type table      type possible_keys key    key_len ref   rows  Extra
1  SIMPLE      numberdata ref  number        number 4       const 17174 Using index condition

Is it possible for the query to return 2 or more rows? Or will it always return 1 (or 0) rows?

For any number I return only one result, or none (in case it's not present in any of the ranges). No possibilities for duplicates.
Can we assume that all values in number_start and number_end are left-padded with 0s to the same length?

Yes, that's correct.

Assumptions:

the query can only return 1 row (or none) and never 2 or more, i.e. the (number_start, number_end) ranges are not overlapping for the same numbertype_id.
the number_start and number_end values  are all left-padded with 0s to the same length.

Under the above assumptions, we can use a trick to use an index to fetch exactly 1 row (if there is one) that matches the criteria (not all, checking only the low end of the range) - and don't search for more rows. The check on the high end is done before the row is returned to avoid any false positive result:

SELECT n.*
FROM
    ( SELECT id, number_end
      FROM numberdata
      WHERE numbertype_id = 7
        AND number_start <= '00112233'
      ORDER BY number_start DESC
      LIMIT 1
    ) AS ni
  JOIN
    numberdata AS n
  ON  ni.number_end >= '00112233'
  AND ni.id = n.id ;

The query will only need an index on (numbertype_id, number_start) - or keep the one you have on (numbertype_id, number_start, number_end).


Export a SQL Server 2000 database to Server 2012 I have a production environment running Windows Server 2000 and a development environment using SQL Server 2012.  I need to make updates to the DB and implement some new features using .ASP.  Is there a way to export the 2000 database and import it into 2012?  All that I could find is this MS support doc about transferring the DB to 2008.

http://support.microsoft.com/kb/962355

Since your source server is SQL Server 2000, you can't restore directly to SQL Server 2012. You will need to restore to another instance first (2005, 2008, or 2008 R2).

On 2000:

BACKUP DATABASE dbname TO DISK = 'D:\backups\db.bak' WITH INIT;

On 2005 or 2008 or 2008 R2:

RESTORE DATABASE dbname FROM DISK = 'D:\restores\db.bak'
  WITH REPLACE, RECOVERY, MOVE (...you will likely have to specify new file locations...);

Then from there, back it up again, and then restore on the 2012 server.

Once you've restored, you'll probably want to adjust the compatibility level:

ALTER DATABASE dbname SET COMPATIBILITY_LEVEL = 110;

As well as update statistics. But note that 80 compatibility will automatically be upgraded to 90 (and you will have issues if you use syntax that is no longer supported, e.g. *= joins). While it won't catch everything, you may want to run the 2012 Upgrade Advisor while the database is in its temporary home on a 2005, 2008 or 2008 R2 instance to catch any potential issues. Also see this answer for ways the change in compatibility level may affect your existing code (the upgrade advisor won't catch many of those).


Rebuilding partitioned table clustered indexes I need to know how can we rebuild the partition table clustered index with the table size being around 270 GB with 126 Partitions on it.

Also, I want to execute it in the production environment, so what will be the quickest way to do it and how.

This is a very critical change which needs to be done, so any help or suggestion would be highly appreciated.

Partitions can be rebuilt individually.  This allows you to interrupt the rebuild and preserve the work already done. The rebuild can be resumed later after the previously completed partition number.  Below is an example of this technique, where the @PartitonNumber value can be changed in a restart scenario.

DECLARE
      @PartitionNumber int = 1 --specify start partition number
    , @EndPartitionNumber int;

--get last partition number
SELECT @EndPartitionNumber = MAX(p.partition_number)
FROM sys.indexes AS i
JOIN sys.partitions AS p ON
    p.object_id = i.object_id
    AND p.index_id = i.index_id
WHERE
    i.object_id = OBJECT_ID(N'dbo.YourTable', 'U')
    AND i.name = N'YourIndexName';

--rebuild each partition individually
WHILE @PartitionNumber <= @EndPartitionNumber
BEGIN

    RAISERROR('Rebuilding partition %d', 0, 0, @PartitionNumber) WITH NOWAIT;

    ALTER INDEX [YourIndexName]
        ON [dbo].[YourTable]
        REBUILD Partition = @PartitionNumber;
        --WITH(ONLINE=ON); --specify desired online mode in SQL 2014 and later

    RAISERROR('Partition %d rebuild completed', 0, 0, @PartitionNumber) WITH NOWAIT;

    SET @PartitionNumber += 1;

END;
GO


postgres backup / restore: restored database much smaller? I am worried that my restored database is very different from the original:

#check size of postgres database
postgres@db1:/tmp$ psql -c "select pg_size_pretty(pg_database_size('test_db'));"
 pg_size_pretty
----------------
 2105 MB
(1 row)

#backup database
postgres@db1:/tmp$ pg_dump -Fc test_db > test_db_Fc.dump

#rename postgres database (i.e. park it nearby)
postgres@db1:/tmp$ psql -c "alter database test_db rename to test_db_20130322;"
ALTER DATABASE
-------
(1 row)

#restore test_db
postgres@db1:/tmp$ pg_restore -Fc -C -d postgres test_db_Fc.dump

#check size of restored postgres database
postgres@db1:/tmp$ psql -c "select pg_size_pretty(pg_database_size('test_db'));"
 pg_size_pretty
----------------
 257 MB
(1 row)

The original db is many times larger than the restored one. What is going on here?
As far as I can tell, the website that test_db serves is still working perfectly well, after a restore, but I need to know what's up before i use a backup in live context.

FYI if I run vacuumdb on each database there seems to be no change in database size.

[Addendum, added later]
In the tradition of RTFM I have gone hunting in the manual for PostgreSQL.
Here are some more pieces to the puzzle

#is the autovacuum switched on?
postgres@db1:/tmp$ psql -c "SHOW autovacuum;"
 autovacuum
------------
 on
(1 row)

#The "track counts" parameter is needed by autovacuum which uses statistics from database activity to know where to do its job
postgres@db1:/tmp$ psql -c "SHOW track_counts;"
 track_counts
--------------
 on
(1 row)

#is there an autovacuum daemon resident in memory?
postgres@db1:/tmp$ ps  -ef | grep 'autovacuum'
postgres  1261  1021  0 Jan23 ?        00:08:27 postgres: autovacuum launcher process
postgres 18347 18149  0 00:33 pts/0    00:00:00 grep autovacuum

#what's been happening on the live server?
postgres@LIVEdb1:/tmp$ psql -c "SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_all_tables;"
#result is list of 65 tables (out about 300), all empty (no dates at all)

Gratefully following up on @craig-ringer's advice to look into VACUUM FULL I turned to PostgreSQL documentation, (paraphrased) "... In the worst case where your disk is nearly full, VACUUM FULL may be the only workable alternative. ... (but) the usual goal of routine vacuuming is to avoid needing VACUUM FULL. The autovacuum daemon attempts to do standard VACUUMs often enough to maintain steady-state usage of disk space ..."

I will follow @chris-travers advice and map out the number of rows in tables from each version of the database. I think in my case it'd be fair to say that VACUUM FULL will relieve the pressure on disk space, and it'll make original_vs_restored look better but there's still this red flag of excessive bloat. I think autovacuum isn't doing anything and that's a worry! Thanks for the guidance so far, it's fascinating.

Maybe it's just index bloat. VACUUM FULL does not help with index bloat, on the contrary, as said in the doc for 8.4:

  The FULL option is not recommended for routine use, but might be
  useful in special cases. An example is when you have deleted or
  updated most of the rows in a table and would like the table to
  physically shrink to occupy less disk space and allow faster table
  scans. VACUUM FULL will usually shrink the table more than a plain
  VACUUM would. The FULL option does not shrink indexes; a periodic
  REINDEX is still recommended. In fact, it is often faster to drop all
  indexes, VACUUM FULL, and recreate the indexes.

(in more recent versions, this advice has disappeared because VACUUM FULL has been reimplemented differently).

See Routine reindeing and the REINDEX command.

The easiest way to reindex is to connect to the database with the db user that owns it and issue:

REINDEX database test_db;

Ideally, it should be done immediately after VACUUM FULL and the database should shrink to its lowest possible size at this point in time.


Selecting Multiple Values from Same Coulmn I want a query to find out who doesn't have a specific role (user, create or admin)

DB is Structured as Follows:

name_last | name_first | role

Sample Data:

brown | john | user |
brown | john | create |
brown | john | admin |
house | chris | user |
brady | bill | user |
pink | deb | user |
pink | deb | admin |
green | ashley | user |
green | ashley | create

Who doesn't have the create role?

select name_last, name_first, role
where role != ??

You can use a not exists query:

select distinct t1.name_last, t1.name_first
from the_table t1
where not exists (select *
                  from the_table t2
                  where t1.name_last = t2.name_last
                    and t1.name_first = t2.name_first
                    and t2.role = 'create');

The NOT EXISTS operator returns "true" if the sub-select returns nothing. In this case the sub-select looks for any row with the same name as the outer row but with the role create


Add previous column alias to calculate sum/total of last column I have a query which I need to optimize. It is using 3 subqueries 2 times each. Below is the query.

select i.itemid, b.itemname, i.batchno,
        (select sum(qty) from inventtrans
            where itemid = i.itemid
            and batchno = i.batchno
            and inventlocationid = 'locationId'
            and dateinvent < '2018-12-26 00:00:00') as openingBal ,
        (select sum(qty) from inventtrans
            where itemid = i.itemid
            and batchno = i.batchno
            and inventlocationid = 'locationId'
            and dateinvent >= '2018-01-01 00:00:00'
            and dateinvent <= '2019-12-31 00:00:00'
            and qty>0
            ) as inputQty,
        (select sum(qty) from inventtrans
            where itemid = i.itemid
            and batchno = i.batchno
            and inventlocationid = 'locationId'
            and dateinvent >= '2018-01-01 00:00:00'
            and dateinvent <= '2019-12-31 00:00:00'
            and qty<0
            )as outputQty ,
        (
             COALESCE((select sum(qty) from inventtrans
                where itemid = i.itemid
                and batchno = i.batchno
                and inventlocationid = 'locationId'
                and dateinvent < '2018-12-26 00:00:00'),0)
            +
             COALESCE((select sum(qty) from inventtrans
                where itemid = i.itemid
                and batchno = i.batchno
                and inventlocationid = 'locationId'
                and dateinvent >= '2018-01-01 00:00:00'
                and dateinvent <= '2019-12-31 00:00:00'
                and qty>0),0)
            +
             COALESCE((select sum(qty) from inventtrans
                where itemid = i.itemid
                and batchno = i.batchno
                and inventlocationid = 'locationId'
                and dateinvent >= '2018-01-01 00:00:00'
                and dateinvent <= '2019-12-31 00:00:00'
                and qty<0),0)
        ) as bal,
            i.location,
            -- split_part(i.inventsizeid, '/+[0-9]', 2) as size
            i.inventsizeid as size,
            i.configid as color
from inventtrans i, view_inventbatchtrans b
where
i.batchno = b.batchid
group by i.itemid,b.itemname,i.batchno,i.qty,i."location",i.inventsizeid,i.configid;

check the query for openingBal, inputQty, outputQt.
I need to add these 3 columns to get bal column. Here is the query which I optimized.

select i.itemid, b.itemname, i.batchno, i.openingBal, i.inputQty, i.outputQty, i.bal, i.location, i.inventsizeid as size, i.configid as color
from (select i.*,
  (select sum(qty) from inventtrans
    where itemid = i.itemid
    and batchno = i.batchno
    and inventlocationid = 'locationId'
    and dateinvent < '2018-12-26 00:00:00') as openingBal,
    (select sum(qty) from inventtrans
        where itemid = i.itemid
        and batchno = i.batchno
        and inventlocationid = 'locationId'
        and dateinvent >= '2018-01-01 00:00:00'
        and dateinvent <= '2019-12-31 00:00:00'
        and qty>0
      ) as inputQty,
      (select sum(qty) from inventtrans
          where itemid = i.itemid
          and batchno = i.batchno
          and inventlocationid = 'locationId'
          and dateinvent >= '2018-01-01 00:00:00'
          and dateinvent <= '2019-12-31 00:00:00'
          and qty<0
        )as outputQty, (COALESCE(i.openingBal, 0) + COALESCE(i.inputQty, 0) + COALESCE(i.outputQty, 0)) as bal
      from inventtrans i
    ) i, view_inventbatchtrans b
        where
        i.batchno = b.batchid
        group by i.itemid,b.itemname,i.batchno,i.qty,i."location",i.inventsizeid,i.configid;`
 Where I run optimized query it throwing an error as

  SQL Error [42703]: ERROR: column i.openingbal does not exist
        Position: 937 which is at `(COALESCE(i.openingBal, 0) + COALESCE(i.inputQty, 0) + COALESCE(i.outputQty, 0)) as bal

Could you tell what is it that I'm doing wrong. Can we use alias column for this purpose?

Aliases are only available in GROUP BY, ORDER BY or HAVING (aside from direct output).
Found this link as helpful : https://stackoverflow.com/questions/3932205/to-calculate-sum-two-alias-named-columns-in-sql?rq=1

Please check it.


Stored Procedure RAISERROR - Transaction Count Msg Returned I have a stored procedure that should return the below RAISERROR message when called. However, the text is appended to the message when returned:

Transaction count after EXECUTE indicates a mismatching number of
BEGIN and COMMIT statements. Previous count = 1, current count = 0

Why is this? Is the statement order in the CATCH block correct?

SET XACT_ABORT ON; -- ensures the entire transaction is terminated and rolled back. **https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql

BEGIN TRY
BEGIN TRAN --all as one transaction.

    IF EXISTS(
            SELECT
                SupervisorID
            FROM [dbo].[UserDetail]
            WHERE
                SupervisorID IS NOT NULL
            )
    BEGIN
        RAISERROR('Unable to alter a approved data.', 18, 1)
    END
COMMIT TRAN
END TRY
BEGIN CATCH
    -- Determine if an error occurred.
    IF @@TRANCOUNT > 0
        ROLLBACK;

    -- Return the error information.
    DECLARE
        @error_number AS INT,
        @error_message AS NVARCHAR(1000),
        @error_severity AS INT;
    SELECT
        @error_number=ERROR_NUMBER(),
        @error_message=ERROR_MESSAGE(),
        @error_severity=ERROR_SEVERITY();
    RAISERROR(@error_message,@error_severity,1);

END CATCH

You can only get the "Transaction count mismatch" error if you have an open transaction prior to enter this stored procedure. The problem is that BEGIN TRAN will increment @@TRANCOUNT by 1 each time it is called, and COMMIT will decrement by 1 each time it is called, yet ROLLBACK will always reset back to 0 since there truly is no such thing as a nested transaction in SQL Server.

In your case, you have an active transaction before this stored procedure is called, but the ROLLBACK in the CATCH block resets @@TRANCOUNT back to 0, hence the error.

The trick to handling nested stored procedures, each having their own error handling, is to not call BEGIN TRAN if you are already in an active transaction (it doesn't do anything anyway outside of open you up to the possibility of getting this error ;-). The template that I prefer using is found in the following answer of mine, also here on DBA.SE:

Are we required to handle Transaction in C# Code as well as in stored procedure


postgres crosstab with unknown number of categories I have below table

CREATE TABLE bar (project,month,hours)
    AS VALUES
    ('A', 'aug', 1 ),
    ('A', 'sep', 3 ),
    ('B', 'aug', 2 ),
    ('B', 'sep', 5 );

I can crosstab this with month as category as below

select * from crosstab (
'select project, month, hours from bar',
'select distinct month from bar'
) as
c (project text, aug int, sep int);

The problem is month column can be vary. it can be vary from number or names.

Ex: Next time month column in bar table can have oct also which I don't know beforehand.

Then How I create crosstab query for that? In summary what I want is to remove hardcoded aug, sep from my crosstab query.

Seeing as you might be querying all months at some time, you could just add all the months to the crosstab query beforehand.

select * from crosstab
    (
    'select project, month, hours from bar order by 1,2',
    $$SELECT unnest('{jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec}'::text[])$$
    ) as  c
    (
    "project text" text,
    "jan" int, "feb" int, "mar" int, "apr" int,
    "may" int, "jun" int, "jul" int, "aug" int,
    "sep" int, "oct" int, "nov" int, "dec" int
    );

  This solution is based on the answer to the question PostgreSQL Crosstab Query on stackoverflow.com.


Is my ERD valid for airports,cities and airlines database? My objective is to make a database of airports, cities and the airlines which provide coverage to them.

I asked the question before on stackoverflow and the person told me to break up the many-to-many relationship between airport and airline(I  agree with that). Moreover, he/she raised objection on relationship between airline and country.

I thought an airline belongs to a country so i made a relationship between two. Kindly, give your suggestions on the ERD and how i can improve it?

Looks ok.

Some countries span multiple continents, such as Russia and Turkey.

Some airports might not be in cities, like Mirabel (YMX), which is 53k from Montreal.


Migrate servers involved in replication We are planning to migrate two servers with MSSQL 2012 servers with configured transactional replication. New servers will have new names, ip addresses and MSSQL 2014. Subscriber is used for reporting and there are extra tables and columns needed for reporting that does not exist on publisher.
What will be best way to migrate those servers? We will have enough downtime to do all needed works.

Some extra columns on reporting server are calculated on daily basis, and we would like to restore it from backup too, if that is possible.

You don't really migrate a replication topology.  You simply recreate it among the new servers.  Script out your replication configuration and re-create it among the new servers.  So backup, and restore, followed by replication setup with ordinary initialization of the subscriber from a new snapshot.

It's possible, but more complicated, to skip the initialization of the subscriber, if you can ensure that the subscription tables were synchronized at the point-in-time of the restore. See Initializing a subscription with an alternative method.

But if you have the downtime, just clean up and recreate the replication topology from scratch on the new servers.  You should script the new replication setup and test it several times before the cutover.


TokuDB/InnoDB Question What's the faster scheme for insert data to TokuDB or InnoDB Engine? Over 200 Insert per second.

That's okay to answer of one engine.

TABLE: `game`
`gameId` bigint 20 (PK)
`gameMapId` int 4
`gameType` varchar 20
`createDate` datetime (index)

TABLE: `game`
`idx` bigint 20 (autoincrement PK)
`gameId` bigint 20 (UK)
`gameMapId` int 4
`gameType` varchar 20
`createDate` datetime (index)

Percona has already done some benchmarks with TokuDB vs InnoDB

See my post from 10 days ago : TokuDB not much faster than MySQL

Better answers are there as well.


SQL Find Code for Each Separate Column in View We have a SQL View, trying to find the calculation/business logic for each column into a select or table.

create dbo.CustomerVw
as
   FirstName + ' ' LastName as FullName
   datediff(yy,BirthDate,GETDATE()) as Age,
   'New York' as CityReason
from dbo.Customer

Expected Results:

Row 1: FullName: FirstName + ' ' + LastName
Row 2: Age: datediff(yy,BirthDate,GETDATE())
Row 3: City: New York

Code below only displays table code as large text. How would separately display each calculation logic for columns? Each Column row can be a text.

SELECT
    v.TABLE_NAME,
    v.VIEW_DEFINITION
FROM
    INFORMATION_SCHEMA.VIEWS v
WHERE
    v.TABLE_NAME LIKE '%%'

IT will not get you all the way, but you could try string splitting on the new line character (char10):

SELECT
    value
FROM
    INFORMATION_SCHEMA.VIEWS v
    CROSS APPLY string_split(v.VIEW_DEFINITION,CHAR(10))

WHERE
   v.TABLE_SCHEMA = 'dbo' and v.TABLE_NAME = 'CustomerVw';

Result

View definition

create view [dbo].[CustomerVw]
as
 SELECT  FirstName + ' ' +LastName as FullName,
   datediff(yy,BirthDate,GETDATE()) as Age,
   'New York' as CityReason
from dbo.Customer;
GO

some adaptions to get closer to what you want (Not very clean)

SELECT
value
FROM
    INFORMATION_SCHEMA.VIEWS v
    CROSS APPLY string_split(LEFT(SUBSTRING(v.VIEW_DEFINITION,(charindex('SELECT',v.VIEW_DEFINITION) + 6),len(v.VIEW_DEFINITION))
                            ,(CHARINDEX('FROM',SUBSTRING(v.VIEW_DEFINITION,(charindex('SELECT',v.VIEW_DEFINITION) + 6),len(v.VIEW_DEFINITION)))) - 2),CHAR(10))
WHERE
   v.TABLE_SCHEMA = 'dbo' and v.TABLE_NAME = 'CustomerVw';

Result

  FirstName + ' ' +LastName as FullName,
   datediff(yy,BirthDate,GETDATE()) as Age,
   'New York' as CityReason


Extract MIN and MAX values related to datetime values on Postgres 9+ I am trying to query a table that has a collection of student grades recorded over time. I would like to produce a result set that grabs the student id, the first grade, first grade date, last grade, last grade date.

I think I need to use the MIN and MAX functions and some subqueries to achieve this, but I'm just not getting the results I need.

Is there an effective way to achieve the results below on PostgreSQL?

Database Example:

user_id | grade | grade_date
1       | A     | 01/05/2016
1       | B     | 01/15/2016
1       | C     | 01/31/2016
2       | A     | 01/05/2016
2       | B     | 01/15/2016
2       | C     | 01/31/2016
3       | A     | 01/05/2016
3       | B     | 01/15/2016
3       | C     | 01/31/2016
4       | A     | 01/05/2016
4       | B     | 01/15/2016
4       | C     | 01/31/2016

Results I'm aiming for:

user_id | first_grade | first_date | last_grade | last_date
1       | A           | 01/05/2016 | C          | 01/31/2016
2       | A           | 01/05/2016 | C          | 01/31/2016
3       | A           | 01/05/2016 | C          | 01/31/2016
4       | A           | 01/05/2016 | C          | 01/31/2016

There are various ways to do it. An index on columns used in order/filter/join (user_id and grade_date + grade) will play an important role on a large table.
Performances must be tested with real data and table/index design.

Using a window function (ROW_NUMBER()):

SELECT f.user_id, f.grade, f.grade_date, l.grade, l.grade_date
FROM (
    SELECT user_id, grade, grade_date
        , ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY grade_date) as n
    FROM data
) f
INNER JOIN (
    SELECT user_id, grade, grade_date
        , ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY grade_date DESC) as n
    FROM data
) l
ON f.user_id = l.user_id
    AND f.n = 1 AND l.n = 1;

ROW_NUMBER gives each row a number from 1 to N by grade_date up and down and only the first one of each is kept (n=1).

Using subqueries:

SELECT  user_id
    , ( SELECT grade FROM data
        WHERE  user_id = d.user_id
        ORDER BY grade_date LIMIT 1
    )
    , ( SELECT grade_date FROM data
        WHERE  user_id = d.user_id
        ORDER BY grade_date LIMIT 1
    )
    , ( SELECT grade FROM data
        WHERE  user_id = d.user_id
        ORDER BY grade_date DESC LIMIT 1
    )
    , ( SELECT grade_date FROM data
        WHERE  user_id = d.user_id
        ORDER BY grade_date DESC LIMIT 1
    )
FROM (SELECT DISTINCT user_id FROM data) d
;

Each subquery only keep the first row and returns it.

Using MIN and MAX:

SELECT d.user_id, mn.grade, mn.grade_date, mx.grade, mx.grade_date
FROM (
    SELECT user_id, MIN(grade_date) as min_grade_date, MAX(grade_date) as max_grade_date
    FROM data
    GROUP BY user_id
) d
INNER JOIN data mn
    ON mn.grade_date = d.min_grade_date AND mn.user_id = d.user_id
INNER JOIN data mx
    ON mx.grade_date = d.max_grade_date AND mx.user_id = d.user_id
;

It may generate duplicate lines if a user has more than 1 grade on a first or last date.

See SQL Fiddle.


SQL Server 2005 datacube processing in high transaction system We currently have a highly transactional SQL 2005 database which is growning by around 1.2 million rows weekly. On top of this we have a SSAS datacube which is set to process every two hours for reporting.

Over the last couple of months we have been having increasing failures of the datacube processing due to the underlying measures and dimensions changing as the cube processes. Given this we are looking to re-architect the system in order to remove the problem by moving the data transactionally over to a separate reporting database and then processing the datacube on the reporting database.

Can someone with experience of this comment on if it will fix the problem or will the underlying highly transactional database still cause problems in the replication of the measures/dimensions.

Dimensions need to have all the values that the cube has. To avoid having cube value that doesn't exist in the dimensions, I did the following:

have a separate database as a staging database
periodically fill this staging db with data from transaction db
the cubes are generated from this staging db
dimensions are refreshed first, then the cubes
NO data modifications in the staging db between refreshing dimensions and refreshing cubes


created_at vs meaningful db column name , or both? I need often to store the datetime of an action.
For example, for a customer order, "processed_at" would be a right name.

However I need all my tables to have a created_at/updated_at fields for several reasons, among other data export (some ORM generate even the created_at/updated_at by default).

If I use created_at as standard, I wouldn't need the processed_at field as they will be the same, but in such a way I'll lose the "meaning" of the field.

But if use processed_at I'll have to customise all data export based one the relevant "_at" field.

I could create both...storage is not so expensive after all.

Just curious, what have you seen around?

If you're setting a standard that all tables need a column_x, then the standard columns need to be... well... standard. The only way for the columns to be generalisable sensibly in the way you describe is to have created_at be metadata about the record and not the data for the row. processed_at is a different, separate data point that says something about the external world. If you hijack the created_at because it has a reasonably similar name then you've de-standardised the usage of that column - at which point why bother adhering to the standard rigorously? Just name each column what it is.

  TL;DR: use both if you need the standard columns to support your ORM. Don't hijack created_at for non-metadata


Associate a piece of data with a session I think I want to associate a piece of data with each session.

The association must have the following properties:

The data being associated is provided either as a part of connection string (if that is possible at all) or as a parameter for a stored procedure which then must be the first SP called within the session.
The data must be easily available for any stored procedures the session then calls, without having this piece of data passed as a parameter.
This point is essential for the idea: obviously, I could add a parameter into each of my stored procedures and oblige the software to pass the piece of data each time any stored proc is called, but that is exactly what I want to avoid.
The data must automatically expire and be invalid as soon as the session disconnects. No actions from the client must be required to mark the data as expired. If a session disconnects unexpectedly, that still must render its piece of data invalid.

I've considered several ways of doing it, but didn't like any:

Local temporary tables.
Pick a table name (based on a freshly created GUID to avoid any clashes). Have a stored procedure that creates temporary table with this name and inserts a row with the data passed as a parameter. Then all other procs can reference the #table and get the data. The idea is that each session will have it's own #table, while the name of the #table remains the same.
Won't work. The temporary table created in the stored procedure will be dropped upon exiting the procedure. Making the user create the #table directly, without calling a SP, is not an option.
A regular table where data is looked up by @SPID.
Won't work. This requires deleting the data manually upon disconnect.
APP_NAME().
Will probably work, but that's not really app name, and that will not be easily extendable.

Are there better ways of associating data with a session?

Use SET CONTEXT_INFO.

The most common use case for this is to send triggers information about the actor that caused the trigger to fire.

For example:

CREATE PROCEDURE [dbo].[ModifyData]
AS
BEGIN
    DECLARE @ObjectID BINARY(4) = CAST(@@PROCID AS BINARY(4));
    SET CONTEXT_INFO @ObjectID;

    UPDATE [dbo].[Data]
    ...
END;
GO

CREATE TRIGGER [LogModifications]
ON [dbo].[Data]
FOR UPDATE
AS
BEGIN
    DECLARE @ModifiedByObjectID INT = CAST(SUBSTRING(CONTEXT_INFO(), 1, 4) AS INT);

    ...
END;
GO


How can I restrict which computers can connect to an instance of SQL Server? I'm not sure if this is a more a database or sysadmin question, so let me know if it doesn't fit.

Because of some licensing agreements, I've been asked to find a way to restrict which computers on our domain can connect to an instance of SQL Server.

My original idea was to try and create a login trigger to check against a list of computers to see if they matched but I've managed to lock myself out of the server 3 times already. Further to this, apparently using HOST_NAME() to identify the connecting client is not not secure.

How else could I do this from within the SQL Server environment or do I need to look at setting up Firewall rules elsewhere?

Just to be clear. We want it so any user can connect from a specific collection of computers.

If you want to totally restrict certain servers, then the Windows Firewall should be configured to the restriction.

See: Configure the Windows Firewall to Allow SQL Server Access

If you need to prevent a specific login from connecting, then of course you would need to manage that login's rights to the server.

Depending on the granularity of what you need to do, you might need to use both approaches.


How to connect to pluggable database using ipc in Oracle XE 18c? Here is my listener configuration:
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 20-NOV-2020 20:01:25

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=NoteBook)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                20-NOV-2020 20:00:55
Uptime                    0 days 0 hr. 0 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/NoteBook/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=NoteBook)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/product/18c/dbhomeXE/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "b470a0c2708d9acee0530401a8c035ac" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "xepdb1" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully

I can connect to xepdb1 through tcp using sqlplus user/password@NoteBook/xepdb1, however, if I use use ipc (sqlplus user/password@xepdb1) to connect, it fails with an error ORA-12154: TNS:could not resolve the connect identifier specified.
How can I connect to a pluggable database using ipc?

"if I use use ipc (sqlplus user/password@xepdb1) "

That's not IPC, that's tcp.  It tries to find the entry for 'xepdb1' in the client's tnsnames.ora file. Then it tries to construct a tcp packet from the information found there, and send that packet to the specified host and port, where - hopefully - there is a listener.  Your error means that it could not find the specified entry in the client's tnsnames.ora.


Possible to create databases in MongoDB but not have access to databases created by others? I need two user accounts - both able to create and administer databases - but neither account able to access or administer databases created by the other account.

Is this possible?

Answer is NO. If user can create database what don't exists, user must have 'dbAdminAnyDatabase' role and then user can access any DB.

IF database names are know forehand, you can give 'dbAdmin' role to user for that database, even database don't actually exists yet. In this case, if those "prelisted" databases are different names, different users don't have right to those database what other users has created.

As you can create empty databases (without collections) forehand and give rights to users. But, DB names are "selected" forehand and user don't have possibility to choose their own DB name... Collections they can then create, if they have right to DB.


Newly created jobs need to be set for failure notifications automatically Whenever a new / existing job gets failed on an instance of SQL Server, we should recieve an email about failure notification. Please help on how we can automate it?

I'd suggest checking out this post from Greg Larsen - Automating SQL Server Agent Notification.  You could manually create a SQL Agent job that runs periodically and searches for enabled jobs that don't have any notifications defined.

Here's an example of a dynamic SQL query you could put in a SQL Agent job to find SQL agent jobs without notifications and dynamically update them to have notifications.  Make sure you uncomment the EXEC when you put it in a SQL Agent job.  You will also have to modify the variable @Operator to specify the operator you want notified.

USE [msdb]
GO

SET NOCOUNT ON

DECLARE @ExecStmt VARCHAR(4000)
DECLARE @Operator VARCHAR(50) = 'DBA' -- place your operator name here

SELECT @ExecStmt = STUFF((
            SELECT ';' + 'EXEC sp_update_job @job_name = ''' + j.[name] + ''', @notify_email_operator_name = ''' + @Operator + ''', @notify_level_email = 2' -- 1=On Success, 2=On Faulure,3=always
            FROM dbo.sysjobs j
            WHERE j.enabled = 1
                AND j.notify_level_email <> 1
            FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
PRINT @ExecStmt
--exec (@ExecStmt)
GO


How can I automatically trigger a script when my DB fails over? I have some Windows services that I'd like to restart after a database failover but I can't find any details on triggering external actions after a failover. I'm working with SQL Server 2012 database mirroring.

For a cluster failover you can use a startup procedure. Assuming you have xp_cmdshell enabled and the service account has adequate permissions to start services:

USE [master];
GO

CREATE PROCEDURE dbo.StartService
AS
BEGIN
  SET NOCOUNT ON;

  EXEC master..xp_cmdshell 'net start servicename';
END
GO

EXEC [master].dbo.sp_procoption
   'dbo.StartService',
   'startup',
   'true';

I blogged about this for different reasons here:

https://sqlblog.org/2009/09/18/managing-multi-instance-cluster-failovers-with-different-hardware

You could also not use the startup option and instead just call the stored procedure from a job, and schedule the job to run when SQL Server Agent starts:

Of course this depends on the SQL Agent service starting successfully, and also assumes that the Agent or SQL Server services do not depend on the service you're trying to start. :-)


Is there a performance benefit to use tinyint instead of int for Primary Key? Is there a performance benefit to use tinyint instead of int for Primary Key when I know that the number of rows are limited in this table but this key will be used as a foreign key in another huge table? (I will use a lot of joins in queries)
I'm using MySQL.
For example this is a part of my model:

Yes, there is a performance benefit to using TINYINT vs INT.

  Design your tables to minimize their space on the disk. This can
  result in huge improvements by reducing the amount of data written to
  and read from disk. Smaller tables normally require less main memory
  while their contents are being actively processed during query
  execution. Any space reduction for table data also results in smaller
  indexes that can be processed faster.

Read the entire page of the documentation where I pulled that paragraph from for even more information.

For your particular case, the use of TINYINT instead of INT in the company table will mean that the foreign key in the project table will 5 bytes, instead of 8. This is because all secondary keys include the primary key of the table itself - this is so that it can look up the primary key record and get more information. (Which leads into a discussion on clustered indexes, covering indexes, and double lookups.)

So if you will be joining on or filtering by this foreign key to the company table, then you will use 5/8ths as much space for each index entry, which means you'll be able to fit about 8/5ths as much more per page lookup, which means less pages have to be loaded into memory to handle any given query.

In general, smaller is always better. Not for the disk space usage per se, but for when you need to have the data loaded into memory for joins and WHERE clause filters.

That said, TINYINT is quite small. Are you sure you will never need more than 256 companies? My personal rule of thumb is take your best guess on how many records you will have, and then pick a datatype 10 times or more larger. Why? Because people are both bad at estimating and at anticipating future use cases. SMALLINT or MEDIUMINT may avoid you pain in switching in the future.

So, go for small, but big enough to make it unlikely to have to change anytime soon (or preferably, ever.)


Avoiding Multiple Queries when Searching for Records Associated with a Set of Records So, I am sure I have done something really stupid while designing this, and I'm open to schema changes if they'll really help me out.

On to the problem:

I have a custom shopping cart system (backed by a MySQL database) that includes a products table and a price_rules table that's used for computing discounts and applying promo code discounts. Some price rules don't have promo codes attached to them; some are simply "10% off of product X from March 1st through April 1st" or similar. Because a single price rule can apply to many individual products, I also have a join table called price_rule_product.

When showing a set of products (for example, on the main shop page or listing all products in a category) I'm currently running a separate query for each product to look for price rules that apply to that product. Here's what one of those queries looks like:

SELECT DISTINCT
    price_rule.*
FROM
    price_rule
    INNER JOIN price_rule_product ON
        price_rule.id = price_rule_product.price_rule_id
    INNER JOIN product ON
        product.id = price_rule_product.product_id
    WHERE product.id = 10
        AND price_rule.apply_to = 'line_items'
        AND (  price_rule.start_date IS NULL
            OR price_rule.start_date = 0
            OR price_rule.start_date <= 1366063902 )
        AND (  price_rule.end_date IS NULL
            OR price_rule.end_date = 0
            OR price_rule.end_date >= 1366063902 )
        AND (  price_rule.promo_code IS NULL
            OR price_rule.promo_code = '' )
    ORDER BY
         sort ASC

Oh SQL Gods, I pray you have some suggestions/solutions for this. It is causing some significant performance issues, and I'm just not experienced enough with SQL to figure out where to go from here.

EDIT: Here's the output of EXPLAIN SELECT both with and without DISTINCT:

WITH DISTINCT

id  select_type table               type    possible_keys   key key_len ref                         rows    Extra
1   SIMPLE          product             const   PRIMARY         PRIMARY 3   const                           1   Using index; Using temporary; Using filesort
1   SIMPLE          price_rule      ALL         PRIMARY         NULL    NULL    NULL                            14  Using where
1   SIMPLE          price_rule_product  eq_ref  PRIMARY         PRIMARY 8   craft_art.price_rule.id,const   1   Using where; Using index; Distinct

WITHOUT DISTINCT

1   SIMPLE          product             const   PRIMARY         PRIMARY 3   const                           1   Using index; Using filesort
1   SIMPLE          price_rule      ALL         PRIMARY         NULL    NULL    NULL                            14  Using where
1   SIMPLE          price_rule_product  eq_ref  PRIMARY         PRIMARY 8   craft_art.price_rule.id,const   1   Using where; Using index

If I understand you correctly, you want to display the products regardless if they are associated with a price rule, right ?
In my opinion, you'd best achieve this with an OUTER JOIN, which will give you all products and simply return an empty PriceRuleId column for the products without price rules.

The following query will give you a result Matrix with all products and their price rules, if they have them.

select
     p.id as ProductId
    ,pr.id as PriceRuleId
    ,pr.apply_to as PriceRuleApplyTo
    ,pr.start_date as PriceRuleStartDate
    ,pr.end_date as PriceRuleEndDate
    ,pr.promo_code as PriceRulePromoCode
from
    products p
    LEFT OUTER JOIN price_rule_product prp ON prp.product_id = p.id
    LEFT OUTER JOIN
        ( SELECT id, apply_to, start_date, end_date, promo_code
            FROM  price_rule
            WHERE
                apply_to = 'line_items'
                AND ( start_date IS NULL OR start_date = 0 OR start_date <= 1366063902 )
                AND ( end_date IS NULL OR end_date = 0 OR end_date >= 1366063902 )
                AND ( promo_code IS NULL OR promo_code = '' )
            ) pr ON prp.price_rule_id = pr.id

I hope I understood the problem correctly.


How to edit `postgresql.conf` with pgAdmin 3? How does one edit the 'postgresql.conf' file with the pgAdmin app?

With pgAdmin 1.18.1 on Postgres 9.3.x, when I choose File > Open postgresql.conf I get a file picker dialog. And, yes, I'm connected in pgAdmin as the superuser 'postgres'.

I know I've done this in older versions of Postgres.

In pgAdmin…

To edit the postgresql.conf file:Choose Tools > Server Configuration > postgresql.conf
To edit the pg_hba.conf file:Choose Tools > Server Configuration > pg_hba.conf

Avoid the red-herring File menu:

File > Open postgresql.conf
File > Open pg_hba.conf


Improving remake of primary key performance I'm using a MySQL 5.7 hosted on AWS that has a table with about 500,000,000 rows and 124GB in size.
I want to drop the existing primary key (id) and create a new composite primary key of 2 columns:
ALTER TABLE tableName DROP PRIMARY KEY;
ALTER TABLE tableName ADD PRIMARY KEY(column1, column2);

I have already done this query in a testing environment with the same data, and it works fine.
The problem is that running those 2 queries took over 20 hours, and that is unacceptable in a production scenario.
The way I understand it, InnoDB runs these queries by creating a copy of the table (tmp table) and applying the changes to it - and swapping that table.
I've tried scaling it to a db.m5.24xlarge instance, in the hope that the 384GB memory would allow InnoDB to store the entire tmpTable in memory, but it's not working very well - it seems the like the memory is not being utilized.
I have also set the following configuration values:
tmp_table_size = 18446744073709551615;
max_heap_table_size = 1844674407370954752;
innodb_online_alter_log_max_size = 184467440737095;

You should use pt-online-schema-change.
This will allow for no downtime and you can pause it in the middle of a production period
Provided you have twice as much space free (at least 248GB)
STEP 01 : Select Folder
For this example, let's choose /home/whoeveriam/online_change
mkdir -p /home/whoeveriam/online_change
cd /home/whoeveriam/online_change

STEP 02 : Create local my.cnf
vi mysql_creds.cnf

The contents of mysql_creds.cnf should be
[client]
host     = (RDS Endpoint)
user     = blahblahblah
password = blahblahblahblahblahblah

STEP 03 : Create Dry Run Script
Create a file called Dry_Run.sh with this
BASE_FOLDER=(select absolute path)
MYCNF=$BASE_FOLDER}/mysql_creds.cnf
MYSQL_AUTH="--defaults-file=${MYCNF}"
cd ${BASE_FOLDER}

DB=mydb (put your DBName here)
TB=mytable (put your tablename here)
DBTB="${DB}.${TB}"

OPER=Change ; PHASE=Before ; STEP=DryRun ; EXEC_TYPE=--dry-run
RUNLOG=${BASE_FOLDER}/${TB}-${OPER}-${STEP}.log
ERRLOG=${BASE_FOLDER}/${TB}-${OPER}-${STEP}.err
SCTLOG=${BASE_FOLDER}/${TB}-${OPER}-${PHASE}.log
[ -f ${RUNLOG} ] && echo -e "\nAlready Executed" && cat ${RUNLOG} && exit

PAUSE_FILE=${BASE_FOLDER}/pause.txt
PTOSC_OPTS1="--recursion-method=none"
PTOSC_OPTS1="${PTOSC_OPTS1} --pause-file=${PAUSE_FILE}"
PTOSC_OPTS2="${PTOSC_OPTS2} --max-load      Threads_running=100,Threads_connected=500"
PTOSC_OPTS2="${PTOSC_OPTS2} --critical-load Threads_running=200,Threads_connected=1000"

ALTER_TABLE_STMT="DROP PRIMARY KEY,ADD PRIMARY KEY(column1, column2)"

T1=`date +%s`
pt-online-schema-change ${PTOSC_AUTH} D=${DB},t=${TB} ${PTOSC_OPTS1} --alter "${ALTER_TABLE_STMT}" ${PTOSC_OPTS2} ${EXEC_TYPE} >${RUNLOG} 2>${ERRLOG}
T2=`date +%s`
mysql ${MYSQL_AUTH} -ANe"SHOW CREATE TABLE ${DBTB}\G" > ${SCTLOG}

SQL="SET @t1=${T1}; SET @t2=${T2}; SELECT"
SQL="${SQL} FROM_UNIXTIME(@t1) ${STEP}_Initiated"
SQL="${SQL},FROM_UNIXTIME(@t2) ${STEP}_Completed"
SQL="${SQL},SEC_TO_TIME(@t2 - @t1) ${STEP}_RunTime"
mysql ${MYSQL_AUTH} --table -Ae"${SQL}" >> ${RUNLOG}
cat ${RUNLOG}

STEP 04 : Create Live Run Script
cp Dry_Run.sh Live_Run.sh

Change the line that has
OPER=Change ; PHASE=Before ; STEP=DryRun ; EXEC_TYPE=--dry-run

to
OPER=Change ; PHASE=After ; STEP=LiveRun ; EXEC_TYPE=--execute

STEP 05 : Start up Linux Screen Session
screen -S BigChange

STEP 06 : Execute Dry Run
chmod +x Dry_Run.sh
./Dry_Run.sh

STEP 07 : Execute Live Run
chmod +x Live_Run.sh
./Live_Run.sh

FINAL NOTES
If you wish pause the change in the middle of Production Time, run this
cd /home/whoeveriam/online_change
touch pause.txt

It will pause whether it is. Incoming changes will be handled by the triggers
When you want to continue, remove the file
cd /home/whoeveriam/online_change
rm pause.txt

You can monitor progress in the Log and Error Files


MongoDB 3.0 Indexes maximum limitation I have a mongo DB that store user's public messages.
Each message should be categorized by at least one TAG (3 max in an array)

The TAG can be anything up to 32 characters. ([0..9a..z])

the Tag field is not part of the message field, and a search will only be occurring on the Tag field. (i.e. not full text index)

Since there is almost infinite possibilities for the Tag, my though is that at some point the mongoDB index will be huge, with some Tag referencing millions of documents, while some others will only reference a few.

What it the best practice to keep performances high ? Are my worries not founded ?

P.S. :
- The server's RAM is 64GB.
- The message's date is also indexed (for sorting)

On your indices

Ok, first things first. Assuming some structure like this

{
  _id: new ObjectId(),
  date: new ISODate(),
  message: "Hello, Multikey Indices!",
  tags: ["MongoDB","Indices","Multikey"]
}

indexing tags would result in a multikey index. For the document above, the index would have three entries: "MongoDB", "Indices" and "Multikey", all pointing to the same document. The index would have around 200 bytes. Now let's assume the worst case scenario that each document had tags distinct of the tags of each other document, resulting in three new index entries per document. Even if we multiply those 200 bytes with 100,000,000, the result is just 20Gb.

And here comes the good stuff: MongoDB keeps indices in RAM as long as they fit. And for memory access, we are talking of somewhere of 75ns (mind you, a nanosecond is a billionth of a second). So even in our worst case scenario, our index will be searched pretty fast.

As for the sorting: As a rule of thumb, one should assume that MongoDB can use only one index per query. While this is not entirely true as there are index intersections as of MongoDB 2.6, it can be a rather delicate topic. Since the individual fields of compound indices can be used independently, it is pretty reasonable to create a compound index here. But: order matters. You query for tags and then sort by date, so your index should be created like this

db.collection.ensureIndex({tags:1,date:1})

in case you sort in ascending order when doing the query or this

db.collection.ensureIndex({tags:1,date:-1})

for a descending sort order.

That being said, we can now have a look how to ensure performance for a MongoDB cluster.

On the real problem (IMHO)

IMHO, your mistake is to assume that it is a technical question to ensure performance. I strongly disagree with that. There are a few things which can be done from the technical side (such as proper indexing), but at the very bottom, ensuring the performance of a MongoDB cluster is an administrative task. Let me explain this a bit more detailed.

From my experience, you need to adapt the dimensions of your hardware to your needs. If you need to have certain queries to be fast, that's easy to achieve. But if your hardware lacks the necessary resources, your options are either to scale up/out or accept performance impact. (In your scenario, your hardware seems to have sufficient RAM, btw.)

Here is what I do when I expect that I need to scale.

For my initial hardware, I see that I get the most bang for the buck RAM- and IOPs-wise. This way, I am most cost efficient and scaling up does not make any sense any more.
Since I know that my only cost efficient option is to scale out, I make a written plan for it (what to do, who has to make the "go" decision, how to migrate, when to put a maintenance window, etc) and discuss it with the stakeholders. If service interruption is not an option, I start with a sharded cluster with a single shard. That's often viable, since config servers can easily be run on extremely cheapo VMs, which you often can run for years (or even – admittedly theoretical – decades) before this approach becomes more expensive than a service interruption. I strongly suggest the latter approach, since scaling out becomes as easy as adding a shard to the cluster and you don't need to deal with the intricacies of sharding when you already are in sort of a pinch (data migration included for free, too). But make sure you have a written plan for this, too, agreed on by all stakeholders.
I set alerts for disk and RAM usage. As a rule of thumb, an alert when either usage of reaches 85% should give you plenty of time to execute your scaling plan.

From my experience, this is the best practice to keep the performance of MongoDB high.


Active Directory not working through SQL server Management studio I have created a Group in SQL Server and add an Active Directory group to it with which contains users.

When I try to login through Windows authentication, it gives me the following error:

Login failed for user 'UserName'. (Microsoft SQL Server, Error: 18456)

But when I add just an AD user separate from the group, it has no problem logging in.

Why doesn't the login work with only the group added as a login?

I have tried changing the Active Directory settings from global to universal type but that didn't change anything

This on Windows 2016 server machine and SQL Server 2017.

Normally when you receive an error message with the Error: 18456, ... it will contain additional information somewhere.

Detailed SSMS Login Error

In the case of the SSMS Login, the error message will be displayed together with a couple of icons. One of them is page with a white cross on red background. If you click on that icon you will be presented with the detailed error message, which could look a bit like this:

The detailed error message will contain additional information:

Server Name: .
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536

Detailed Login Error in ERRORLOG

Additionally you can find the full error message in the ERRORLOG of your SQL Server instance. The entry will look similar to the following:

2018-08-10 15:37:56.10 Logon       Error: 18456, Severity: 14, State: 5.
2018-08-10 15:37:56.10 Logon       Login failed for user 'bigger'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

Decoding The State Number

With the state number you can then go and check Aaron Bertrand's Blog's article:

Troubleshooting Error 18456 (SQLBlog / Aaron)

Further Analysis

If you can edit additional information into your question, then we might be able to find the root cause together.


Stored procedure support in MySQL Workbench? I've created a lot of stored procedures when I was using HeidiSQL on Windows.  I am now on an iMAC (x64) and it doesn't look like installing HeidiSQL is an option.
I've got MySQL Workbench version 8.0.24, it does list the stored procedures and functions but I cannot edit these in the application.
I'm surprised that this functionality appears to be missing or disabled.  Has anyone got a suggestions?  Tools that will work on iMAC (x64):
macOS Big Sur
Version 11.3.1
iMAC (Retina 5K, 27-inch, Late 2015)
Processor 4 GHz Quad-Core Intel Core i7
Memory 16 GB 1867 MHz DDR3

In  workbench you find all to stored procedures, when you make a right cick on stored procedures.
In your case use Alter Stored procedures
It is basically all the same for view tables columns etc.


In SQL Server, is parallelism per operator, or something else? I work with a really old DBA who says a lot of weird stuff. Dude has an O'Reilly book that only has an amoeba on the cover.

At lunch we were talking about parallelism, because our new server has 24 cores. He says that in a parallel plan, every operator gets DOP threads. So if you have MAXDOP 8 and your query has 4 parallel operators it'll use 32 threads at once.

That doesn't seem right because you'd run out of threads really fast.

I also read that it might just be 8 for the whole query, which seems like too few.

Why Do I see more threads per SPID in sysprocesses than MAXDOP?

Are either of them right?

He says that in a parallel plan, every operator gets DOP threads.

No. This is at best misleading, but closer to being simply wrong.

In a serial plan, every operator 'gets' one thread, but that thread is the same thread for all operators. The principle is similar for parallel plans.

Each parallel operator is run by DOP threads, but those threads are not exclusive to a particular operator, they are shared among operators within the same parallel branch.

Branch boundaries are delimited by Parallelism operators (Demand, Repartition, and Gather Streams). The diagram below shows a parallel plan with three branches:

Reproduced from the article referenced at the end of this answer

  So if you have MAXDOP 8 and your query has 4 parallel operators it'll use 32 threads at once.

No. You can't just multiply DOP by the number of operators to get the number of threads. The number of threads reserved for parallel branches is the number of parallel branches (not operators) multiplied by DOP.

The number of threads that can be active at the same time for a single parallel query is limited to DOP in SQL Server 2005 and later. SQL Server achieves this by allocating threads to DOP schedulers.

  I also read that it might just be 8 for the whole query, which seems like too few.

At DOP = 8 this would be correct for a plan with a single parallel branch. There can be multiple parallel plan branches in a parallel plan. For a plan with n parallel branches, the thread reservation for parallel workers is n * DOP.

See Parallel Execution Plans – Branches and Threads by Paul White.

Note: The number of branches reported in execution plans is normally the number of branches that may possibly execute concurrently (due to blocking operators, threads in one branch may sometimes be safely recycled for a later branch).