Why might a parallel pg_restore command take hours longer to complete its non-parallel equivalent? I used pg_restore to restore a 50 GB db from a directory backup using the following command which used 4 jobs:

pg_restore -d analytics -U postgres  -j 4 -v "D:\Program Files\PostgreSQL\10\backups\Analytics_08_2018__7_53_21.36.compressed"

I ran this from a command line. The restore took about  2 hours longer  than a non-parallel restore. it seemed to hang on creating indexes at the end of the restore job

pg_restore: launching item 2817 INDEX nidx_bigrams_inc_hits
pg_restore: creating INDEX "public.nidx_bigrams_inc_hits"
pg_restore: finished item 2965 TABLE DATA trigrams
pg_restore: launching item 2822 INDEX nidx_trigrams_inc_hits
pg_restore: creating INDEX "public.nidx_trigrams_inc_hits"
pg_restore: finished item 2823 INDEX nidx_unigrams_inc_hits
pg_restore: finished item 2822 INDEX nidx_trigrams_inc_hits
pg_restore: finished item 2817 INDEX nidx_bigrams_inc_hits
pg_restore: finished main parallel loop

Each of these pg_restore "creating index" jobs had a status of "idle" in pg_stat_activity.
Another pg_restore job was "idle" on commit.

I was hoping that the parallel restore would complete much faster than the default restore and it seemed to be doing that until it hung there after about 15 minutes.
There is not much else happening on this server. The indexes seemed complete when I checked them before the job finished i.e. they were the same size as the original database indexes.
Is there any particular reason why a parallel restore job would take longer to finalize/commit as opposed to the non-parallel restore?
I'd want to be prepared for that in a disaster recovery situation and rule out parallel if I cannot rely on the job duration.

By the way I am aware of file system based backups as opposed to logical, but I am only interested in logical for the time being.

It's probably just database contention.

Doing a parallel dump, you postgres can write to many different files at once, so the load can be spread around.

Doing a parallel restore, postgres has to manage all that [heavy-weight] traffic coming in from jobs+1 connections, all filling up data pages in the buffer cache and writing them to disk, all at once.  To create indexes, you need to read data pages back in and scan them; doing this for lots of tables at once puts a lot of pressure on the buffer cache ...   more work for postgres.

Also, you've specified four jobs - does your database [server] have enough CPUs / cores to support that?  If not then the extra jobs will still get spun up, but will suffer contention at the operating system level, as they get switched in an out of memory as the [poor] machine tries to run them all "at the same time".
On smaller servers, it may be that you get [some] benefit from running with two jobs, but more than that and the benefits are rapidly outweighed by the cost.


How to show a column as NULL if it doesn't exist in match? I have a query as shown below.
In this query I am trying to get the result of orders which don't have messages (which I thought as null). But I was expecting that this query would produce a NULL in message_id if someone has an order and doesn't have a message. What should I do do reach my goal?

select *, a.message_id as x
from
  messages a
  FULL OUTER JOIN customers b ON a.customer_id = b.customer_id
  FULL OUTER JOIN orders c ON c.customer_id = b.customer_id
  FULL OUTER JOIN delivery_templates d ON c.billing_template_id = d.delivery_template_id
  FULL OUTER JOIN products e ON a.product_id = e.product_id
  FULL OUTER JOIN order_items f ON f.order_id = c.order_id
where
  a.seller_id in (2, 3, 1, 6)
  AND
  a.message_id ISNULL

You're talking about how you display or project null when the query returns it. Remember, null is a type that represents something. It has no fixed display/rendering. For that use coalesce,

SELECT coalesce(a.message_id, 'NULL') -- or whatever you want the result is `null`

instead of

SELECT a.message_id

null
For more information see the docs on COALESCE,

  The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display.


Virtual Log Files (VLFs) Fragmentation I have a database with 160 VLFs.  How do I determine whether the VLFs are fragmented? Further, how do I find the percentage of fragmentation?

Another thing that I want to confirm: Are the following 3 statements always true for newly added chunks in SQL server Virtual Log files?

chunks less than 64MB and up to 64MB = 4 VLFs
chunks larger than 64MB and up to 1GB = 8 VLFs
chunks larger than 1GB = 16 VLFs

VLF are totally managed internally by SQL server, I am talking about there size. If you have large amount of VLF's for the database its not a good sign.I guess you are referring to large amount of VLF as VLF fragmentation.
High amount of VLF can be attributed to fact that autogrowth settings are not proper for database may be very small and database has to allocate new vlf reach times it grows. Ofcourse VLF size would be small and eventually be many. 160 VLF count does not seems issue to me.Below blog will help you in further understanding

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/


Making production data accessible to developers via masking We want to provide developers in our organization masked data from production to help troubleshoot production issues.
What would be the best way to approach it?
I've read this article https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15

But it seems to take a different approach from what I've envisioned. My idea was to have a separate database server that is replicated from the production database, and somehow mask the data during replication so that the real data will never reach the replicated database server. That way we don't have to have special security considerations about how to store and treat the replicated database server.

Is it a reasonable approach?

Is it a reasonable approach?

Yes.  That's what the (removed) "Static Data Masking" component in SSMS did.  A simple way to implement this is to copy data from production using an identity for which Dynamic Data Masking is enforced on the production database, using the Copy Database Wizard or a custom script.


MySQL accent insensitive word search After spending almost all of my day searching a fix to this I come to you guys.

I need to do an accent insensitive word search on my db(InnoDB), but I cannot get it to work.

I have t1 table, the field description which is an indexed varchar(250), furthermore my table collation is utf8_general_ci.

t1 have a record in which the field description have stored bla bla... instrucción... bla. just in case you are wondering how are the records stored.

I'm trying to fetch the records using the words instrucción and instruccion using this:

SELECT id FROM t1 WHERE description LIKE '%instruccion%'

If I use instrucción I get a non-empty result, but using instruccion I get an empty result.

Please help and thanks.

A Google of "mysql accent insensitive search" produces various links with various "tricks" - which all seem to invalidate any indexes you may have on your table.

I had to implement something similar years ago for Irish names and addresses which may contain accents.

The strategy (if your RDBMS doesn't support accent insensitive searches) is to use a shadow column which keeps track of a "base" word - in our case unaccented, capitalised and spaces removed so that when a record with the name:

name_display
Ronán Ó Sé

is INSERTed (or UPDATEd), what will be stored as

name_shadow
RONANOSE

and then all searches are made against the shadow column.

In the app, we capitalised, and removed accents and spaces from all search strings when sending them to the server, so that the search was comparing like with like - i.e. unaccented, capitalised, space-removed strings.

This had the dual advantage of being always able to use indexes and being portable across RDBMS servers. Being an Irish app, we also had a strategem turning Mac and Mc into MC so that variant spellings were also taken into account - maybe an issue for you also?

We did this using TRIGGERs - but I believe that as of MySQL 5.7, GENERATED (also known as VIRTUAL or COMPUTED) columns are supported which may simplify the logic?


Stripping OIDs from tables in preparation for pg_upgrade I have a postgres database in RDS, file size approaching 1TB.  We started in 2005, using ruby/activerecord/rails, and along the way have upgraded to PG 9.6
Rails migrations create tables using CREATE TABLE, never specified whether to include OIDs or not, but never used them.  So some of our oldest (and largest) tables have OIDs.  At some point postgres stopped defaulting to creating tables with OIDs, so tables created more recently don't have this issue.
We're looking to do an upgrade 9.6 -> 12, ideally using pg_upgrade.  This fails complaining about tables having OIDs.
We can do ALTER TABLE :table_name: SET WITHOUT OIDS but for our larger tables that takes several hours, locking the table, effectively taking down the database.  We would prefer to avoid the downtime if possible.
Is it expected that an ALTER TABLE :table_name: SET WITHOUT OIDS should rewrite all the rows of a table?
Is there a way to avoid this rewriting?  (Ordinary columns can be dropped without rewriting all the rows, for example.)
I tried on a toy database mucking with the metadata tables:
UPDATE pg_catalog.pg_class SET relhasoids = 'f' WHERE oid = (
  SELECT c.oid FROM pg_catalog.pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE n.nspname = 'public' AND c.relname = 'name_of_table'
);

It executed quickly and a cursory examination of the data didn't show any corruption.
But, this doesn't seem to be a documented approach.
Is this a terrible idea? Are there other approaches that don't require rewriting whole tables?

That UPDATE might work, but I am not certain, and I wouldn't want to do that with data I value.  You don't have the option anyway, since you cannot get superuser access in a hosted database.
I can think of a safe, but more painful method:

Briefly suspend data modification activity on the table.

Create a trigger that records all data modification activity in another table.

Create a new table that looks like the old one, and start an INSERT INTO ... SELECT ... that copies the data.

Now normal operation can be resumed.
Once the copy is done, replay the recorded changes. Then

Start a transaction.

LOCK the original table.

Replay all the changes that happened since the last replay (should be few).

DROP the original table.

Rename the copy to the original table name.

Foreign keys will require extra attention.


MySQL replication : Query partially completed on the master (error on master: 1053) I got the below error.

Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: 'optimize table  '

=================

Now, I researched and got something

 Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: 'FLUSH TABLES'
    Here the server panicked on the "FLUSH TABLES" command.
    Research showed the master db was restarted at the time of the error.
    It seems perfectly safe to run "FLUSH TABLES" on the slave
    Connect to slave database
    Run "SHOW SLAVE STATUS;"
    Note "Last Erno" field is 1053 and "Last Error" is above error
    Run "STOP SLAVE;"
    Run "FLUSH TABLES;"
    Run "SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;"
    Run "SHOW SLAVE STATUS;" and verify "Last Erno" is now 0 (zero) and "Last Error" is blank.
    Exit mysql
    Run "cat /var/lib/mysql/relay-log.info" a few times and verify the index values are updating. If a long command is running it may take several minutes to see an update
    Note: It may take a while for the replication db to catch back up to the primary database.
    Continue to monitor occasionally until the mysql-bin.NNNN file in relay-log.info is the same as the latest binlog on the primary database (in /var/lib/mysql). This means the replication has caught back up.

===================================

you can see, they are asking to run "Flush tables" query again on the slave.

while my query was "optimize table  ".

Do I really need to run this 'optimize table' query again on my slave ? because i think its too large table and it will stop again.

======================================

What should I do, Please Help. Should I run Start Slave in Slave machine ?

If the query was OPTIMIZE TABLE table_name, there is no worry about data inconsistency.
You can run SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; to skip the error on the slave, and re-run the optimization on each server by itself if needed. Use LOCAL so the statement is not replicated:

OPTIMIZE LOCAL TABLE table_name


Is there a good way to select a row where the key represents the lower bound of a range? I am designing some tables and a number of the tables have as the key a lower and upper bound which never overlap. I want to query by a value that can fall withing any of those ranges. If I keep both columns I can query it as a range (i.e. WHERE lower_bound <= val AND val < upper_bound) and it is fairly simple.

However, if I can reduce the lower and upper bound columns into a single column then the table will be simplified in terms of the amount of data we need to store.

Here is an example of the data to be stored:

    lower_bound | upper_bound | stuff1_lower | stuff2_lower | stuff1_upper | stuff2_upper
    ------------+-------------+--------------+--------------+--------------+--------------
              0 |          10 |        0.222 |        0.333 |        0.444 |        0.555
             10 |          15 |        0.444 |        0.555 |        0.666 |        0.777

The stuff1_lower and stuff1_upper represent the same data, but the value is at the ends of the range. Anything in between I need to interpolate given the bounds.

For example, I query with 10 and I need the values 0.444 and 0.555 for my application. If I query with a 5 I need the values 0.333 and 0.444, which are the interpolated values.

The data I'm storing though is duplicated both in the key (the bounds) and in the data. By simplifying the table I know that the complexity of the query would increase, but I'm okay with that so long as the speed efficiency isn't significantly impacted.

Is this a good idea? And is there a way to do this (such as through a view) that wont significantly impact efficiency?

UPDATE
Since my data is small I decided to reduce the number of columns as I had been thinking and go with the following query to get the data:

There's no single right answer for any performance question.  The answer is always that query optimizers are very smart, to a point.  So the most efficient design or query will depend on the number of records, what kind of indexes you have, how selective they are, and many, many more factors.

If you want to avoid keeping both bounds of your ranges, you can search using TOP 1 and order by lower_bound but I wouldn't expect this to be efficient necessarily.  I'd suggest profiling it this way to see if the performance is acceptable.

Having an index on lower_bound will probably help, depending on the number of records in your table.


Administration of MySQL Users What is the best practice for keeping and maintaining MySQL users?  Say for example I have 5 programmers and a PHP application connecting to a database.  Should I create 6 different users 1 for each of the users and 1 for the application?

Can you please clarify Users vs Programmers? Typically this is how it is done:

1) Create three identical schemas and name them PROD, QA and DEV. Their names are self explanatory.

2) Create a dbuser for the application (PHP) use on each of them. If your app is MYAPP, name the dbuser myapp. This is the login the application uses on behalf of every application user.

3) Create a dbuser called dbadmin on each of them and give it to your DBA.

4) Create a dbuser called myapp_dev on each of them and give it to your developers.


Storing metadata of various data types in a MySQL database I am currently designing a MySQL database that will store a variety of documents in different digital formats (mostly docs, spreadsheets, and pdfs). Each document will have several metadata values associated with it (a receipt might have metadata fields for date, store, and price, while a recipe might have metadata entries for various types of ingredients, serving size, and meal type). In order to be clear, the metadata I am storing is not about the table or database itself but is information about each type of document being stored and will have a different value associated with the metadata type for each document. I don't want to create a new table for each type of metadata being stored, but rather allow the users of the database to create additional metadata fields for each type of document as needed.

The problem I'm running into is the metadata's data type will be different depending on what the field is (for example, a business name field would be a varchar, while a dollar amount field would be a decimal type). I want to be able to search based on this data (all receipts with a date issued value before January 2nd for example), so I can't just store everything as a varchar. Currently I only see two ways to design this database:

Create a separate table for storing each data type (ex. a text_metadata table, decimal_metadata table, and date_metadata table) and another table keeping track of the field name and what type it is.
Have a single table for storing the values of all metadata, but have a different column for each data type and only use the column specific to that metadata field to store the actual data.

Both of these solutions seem really clunky. Does anyone have a more elegant approach to storing these types of metadata values?

What I have done in the past is to use something like this:

1)  Each document has a logical type associated with it.

2)  Set up tables for metadata for each logical type.  Each row can store all metadata associated with the document.

The other approach is that of key-value-modelling which can actually be ok or not depending on what you are doing with it.  In this case you have a metadata table which stores all metadata, one value per record, for all documents.  This works best if you find a reasonable way to aggregate the data in result sets, and if you aren't doing complex searches across multiple metadata fields.


Users on same server connect to MySQL with different hosts I am aware that when a connection is made to MySQL, MySQL does a reverse lookup on the IP address to determine the host it is coming from.

I have a very odd situation where I have a number of users connecting from the same server and some users are showing as coming from one host (lets say for example "myhost.com") and others are from a different host (lets say "myhost.co.uk")

The config of the connection in both cases is the same. The IP they connect from is the same. The server they connect to is the same. On both client and server PC the reverse lookup of the IP resolves to the same host (e.g. myhost.com). There is only one IP address on the server.

This is causing us problems with authentication because when adding new users (something which is an automated script) we add them using the server IP address as the host, and about 50% of the time, this is not working because when it does the reverse lookup it finds the wrong host name!

I am aware that you can turn off the host lookup in MySQL config, but at this stage I'm reluctant to do that and would rather find an answer to the problem.

Adding the IP and host name to the hosts file on the database server seems to have resolved this problem!


Foreign key subsumed by unique constraint in MariaDB/MySQL I'm using a "migrations" approach to upgrading/downgrading table structure (Laravel). Essentially tables are added and altered as the application grows, but each "upgrade" script has an appropriate "downgrade" script to revert the changes and restore the structure to the previous version.

I want to implement the relationship "All actors are users, but not all users are actors" (typical one-to-zero-or-one relationship). I do this by creating a foreign key from actors to users, then adding a unique constraint to the FK column.

The problem: When dropping the unique constraint (to "downgrade" to the previous version), I get an error that the constraint is needed by the FK. This happens no matter whether there is data in any of the tables or not, and happens in both MySQL and in MariaDB. What am I missing here? As much as I know, this is the correct way to implement one-to-zero-or-one relationships...

Minimum working example:

App version 1 ("We only have users"):

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

App gets upgraded, v.1.5 ("We have users AND actors, but each actor must have a user account to login").

CREATE TABLE `actors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `subsumed_by_unique_later` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

App gets upgraded to version 2 ("Each actor may only have one user account"). A one-to-zero-or-one relationship needs to be enforced:

ALTER TABLE `actors` ADD CONSTRAINT this_subsumes_fk UNIQUE (`user_id`);

During testing, we need to revert to version 1.5, where the unique index was not there yet:

alter table `actors` drop index this_subsumes_fk;

And here's the error from dropping the index:

Cannot drop index 'this_subsumes_fk': needed in a foreign key constraint

The "Show create table.." output at this point is the following; both the unique constraint and the FK are there!!

CREATE TABLE `actors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `this_subsumes_fk` (`user_id`),
  CONSTRAINT `subsumed_by_unique_later` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I may have stumbled on a solution by trial and error -- correct me if I'm wrong please.

Seems that the FK needs some form of index to function. When creating the unique index, the FK's own index is taken over by the unique one, so that when the unique index is dropped later, the FK would remain "index-less". To prevent that from happening, seems like it is enough to add a normal index before deleting the unique:

ALTER TABLE `actors` ADD index (`user_id`);
alter table `actors` drop index this_subsumes_fk;

At least, with this I don't get an error anymore.


Enabling/disabling/changing Oracle auditing without a shutdown? I have a large database that needs auditing on a very detailed level (every select, update, insert, and delete, along with the actual text of the statement) for about half the users.  I know how to do this (here is a related question for anyone interested), but I also realize we cannot do this for any extended amount of time because of how much quickly we would be collective massive amounts of data.  So while there is a scheduled downtime coming up that we can implement the auditing, to change it to fine tune it (as management changes the request of what data they desire) or to disable it once we have enough data would require us having to take the database down to disable this.  While this wouldn't be too horrible to do if we were able to schedule a short downtime late at night, it would be really nice if this could be avoided altogether, but every reference I've seen so far requires the database to be brought down and back up.

So, my question (which I believe to be general enough for the purposes of this site, even though the back story is specific) is if there is a way to enable/disable/change auditing without shutting down the database.

Edit:  Oracle version 11r2.  As for AUD$ vs. FGA, I'm not sure what FGA is, but AUD is the table which will hold the data, so I am assuming that one.

AUDIT_TRAIL is an initialisation parameters and needs a database restart. Anyway by default AUDIT_TRAIL is set to DB. This could be enough.

With this AUDIT_TRAIL you have just to issue:

AUDIT SELECT TABLE

to start the audit on any table for any select. As you stated this activity has an overhead from the database point of view. you have to pay attention to you SYSAUX tablespace growth where by default resides the sys.aud$ table. To move sys.aud$ you have to do:

SQL> BEGIN
 DBMS_AUDIT_MGMT.set_audit_trail_location(
 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
 audit_trail_location_value => 'AUDIT_TBS');
END;
/

FGA is much more than AUDIT and lets you specify the conditions necessary for an audit record to be generated. Furthermore FGA policies are programatically bound to the object (table, view).

Apart from this you should consider a different solution like an third party tool for DAM (Direct Access Monitoring). Oracle has its own solution today called Oracle Firewall. Otherwise you can check for other products like IBM Guardium, DBProtect, etc.


How much space is needed to reclaim space from a table in INNODB I inherited a MySQL InnoDB table that has approximately 200 GB worth of data, as well as several unneeded indices that amount to approximately 500 GB. I've archived approximately 75 percent of the records off site and DELETED the records from my table, and would like to reclaim the space in the table. I'd like to know if I am going to need about 200 GB of free space in order to reclaim the space, or closer to 700 GB.

innodb_file_per_table is on.

MySQL version is 5.5.

And I am considering either running OPTIMIZE TABLE (eg Recover the disk space after deleting rows from table) or using Percona Toolkit's online schema change (eg http://www.percona.com/blog/2013/09/25/how-to-reclaim-space-in-innodb-when-innodb_file_per_table-is-on/). I'd prefer to do the later, because I can avoid write locks, but I'd use OPTIMIZE TABLE if there was a considerable space difference required.

Will I need closer to 200 gb or close to 700 gb of free space in order to reclaim space from InnoDB? If closer to 700GB, would that answer be any different if I first converted the table to MyISAM (eg http://www.percona.com/blog/2014/08/21/the-mysql-ibdata1-disk-space-issue-and-big-tables-part-1/)?

Thank you for your help!

DISKSPACE FOR EVERYTHING INNODB

SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) InnoDB_DiskSpace
FROM information_schema.tables
WHERE engine='InnoDB';

DISKSPACE FOR DATABASE mydb BY TABLE

SELECT
    IFNULL(tbl,'Total') table_name,
    FORMAT(SUM(table_bytes)/POWER(1024,3),2) table_size
FROM
(
    SELECT table_name tbl,SUM(data_length+index_length)table_bytes
    FROM information_schema.tables WHERE table_schema='mydb
    GROUP BY table_name WITH ROLLUP
) A ORDER BY ISNULL(tbl) DESC,table_bytes;

DISKSPACE FOR INDIVIDUAL TABLE (mydb.mytable)

SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) Table_Diskspace
FROM information_schema.tables
WHERE table_schema='mydb;
AND table_name='mytable';

These queries will tell you how much space you need for any table, database, and engine.

SUGGESTION #1

If you would like to simulate OPTIMIZE TABLE just to see diskspace usage, you run the following steps on mydb.mytable

use mydb
CREATE TABLE mytable_new LIKE mytable;
INSERT INTO mytable_new SELECT * FROM mytable;
ANALYZE TABLE mytable_new;
SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) Table_Diskspace
FROM information_schema.tables
WHERE table_schema='mydb;
AND table_name='mytable_new';
#
# Stop Here. If you are happy with the size, do the last two commands
# If you prefer, drop table mytable_new and do do the online schema change
#
RENAME TABLE mytable TO mytable_old,mytable_newTO mytable;
DROP TABLE mytable_old;

Doing this can give an idea who big the .ibd file will be after running OPTIMIZE TABLE.

SUGGESTION #2

Please do not convert the table to MyISAM. It will be building two files (.MYD and .MYI). Then, you try to converting to InnoDB again. It is essentially the same as doing #SUGGESTION #1. MyISAM is also a little more prone to corruption with no recovery processes in place.

EPILOGUE

By all means, you can proceed with pt-online-schema-change if your data is live. My answer simply gives you queries to estimate sizes of the tables and approximate temp table sizes for OPTIMIZE TABLE.

If you are doing this during off hours, try my post Recover the disk space after deleting rows from table

GIVE IT A TRY !!!


You can use your CASE variant with some modifications. First you need to extend the GROUP BY with MONTH(date):

SELECT product_name,
  CASE WHEN MONTH(date) = 1 THEN SUM(quant_ton) END AS JAN,
  CASE WHEN MONTH(date) = 2 THEN SUM(quant_ton) END AS FEV
FROM tb_import
JOIN tb_cost
    ON tb_import.productid = tb_cost.productid
JOIN tb_sites
    ON tb_sites.site_num = tb_import.site_cd
JOIN tb_product
    ON tb_product.prod_id = tb_import.productid
GROUP BY product_name, MONTH(date)

If not MySQL will randomly pick one month for you. There's a lot to be said about this default setting (insane comes to mind:-), but look up sql_mode ONLY_FULL_GROUP_BY for ways around it.

As you can see now you will get null for non-matching months:

product_name    JAN    FEV
gravel          41     (null)
gravel          (null) 22
soil            23     (null)
soil            (null) 10

By picking - for example - max of JAN and FEV you will get rid of those

SELECT product_name, max(JAN), max(FEV)
FROM (
    SELECT product_name,
        CASE WHEN MONTH(date) = 1 THEN SUM(quant_ton) END AS JAN,
        CASE WHEN MONTH(date) = 2 THEN SUM(quant_ton) END AS FEV
    FROM tb_import
    JOIN tb_cost
        ON tb_import.productid = tb_cost.productid
    JOIN tb_sites
        ON tb_sites.site_num = tb_import.site_cd
    JOIN tb_product
        ON tb_product.prod_id = tb_import.productid
    GROUP BY product_name, MONTH(date)
) as t
GROUP BY product_name;

product_name    max(JAN)    max(FEV)
gravel          41          22
soil            23          10

However, the pivoting is only a presentation of the result. I would choose a query like:

SELECT product_name,
       MONTH(date),
       SUM(quant_ton)
FROM tb_import
JOIN tb_cost
    ON tb_import.productid = tb_cost.productid
JOIN tb_sites
    ON tb_sites.site_num = tb_import.site_cd
JOIN tb_product
    ON tb_product.prod_id = tb_import.productid
GROUP BY product_name, MONTH(date)
ORDER BY product_name, MONTH(date);

and then format the result in the presentation layer of the application.


Help with SQL SERVER 2012 Licence I am planning to buy SQL Server 2012 for development and I have gone crazy with Microsofts complicated licencing policies.I had been searching the internet for valid answers from last few days and finally decided to post a question for expert advice.

The Requirement:

I have a small team of developer who will work on different applications. The applications require SQL SERVER as the backend.Following are the things I want to do with SQL Server

The application will interact with the basic tables, SP, functions and views.
Developing basic SSIS packages and SSRS reports.
I do NOT intend to use the database for Mirroring, Replication, Snapshots or any other complicated stuff but just the basic development.

I approached the Microsoft vendors for quotes for SQL SERVER 2012. They told me that I can go with the Standard Edition with 5 CALS (I have no idea what is CAL and why do I need it). I searched the internet and found that there is a BI edition also which is much costlier than the standard edition.

The Question:
The question is that what version should I use considering my requirement?

Thanks

Purchase Microsoft SQL Server Developer Edition for each of your SQL developers in the exact SQL edition that you need. I would personally go for SQL Server 2014 Developer Edition since service pack one is available.

UPDATE: As of Late March 2016 Microsoft SQL Server Developer Edition is now free.

https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/?MC=Vstudio&MC=SQL&MC=IE&MC=HTML5&MC=JavaScript

Microsoft SQL Server Developer Edition, contains all the benefits of Microsoft SQL Server Enterprise Edition, and must be applied to each respective SQL developer within your organization that uses Developer Edition. That being said, once your developers have a SQL Server developer license, they can develop indiscriminately on as many Developer Edition SQL Servers and databases as you like with as many cores as you like.

For example, one developer with license can deploy (n...) databases to (n...) SQL Servers with (n...) cores. Another developer within your organization can access all those installations, databases, and SQL Servers provided that they, too, have a developer license.

Microsoft SQL Server and can literally save your organization thousands to tens of thousands of dollars for development servers--especially since core licensing has gone through the roof. It's a shame that many Microsoft licensing VARs are very misinformed and/or uninformed about Microsoft SQL Server Developer Edition.


How to get best sellers per headquarters Hi i need to get the best seller of all products per headquarters:

I have this query:

SELECT Employees.first_name, Employees.last_name
     , Sold_products.product_type, Headquarters.country
     , Headquarters.city, Headquarters.street
     , COUNT(*) AS TOTAL
FROM Sold_products
   , Employees
   , Headquarters
WHERE Employees.id = Sold_products.employer_id
  AND Employees.headquarters_id = Headquarters.id
GROUP BY Employees.first_name, Employees.last_name
       , Sold_products.product_type, Headquarters.country
       , Headquarters.city, Headquarters.street
ORDER BY Headquarters.country DESC, TOTAL DESC

I want to get result like: | first_name | last_name | Amount of all sold products | country | city | street

And Group it per unique Headquarter - Headquarter in db can have same city and country but not street - streets are unique (e.g. I have many Headquartes in German, Berlin but each have unique street)

To get the best selling item per headquarter, you first need to calculate the total sales per product per HQ and then rank them:

WITH TotalSalesByProductAndHq
AS
(
    SELECT h.id AS hq_id, p.product_type, SUM(p.value) AS TotalSales
      FROM Sold_products AS p
      JOIN Employees AS e
        ON e.id = p.employer_id
      JOIN Headquarters AS h
        ON e.headquarters_id = h.id
  GROUP BY h.id, p.product_type
),
RankedSalesByProductAndHq
AS
(
    SELECT hq_id, product_type
         , RANK() OVER(PARTITION BY hq_id ORDER BY TotalSales DESC) AS rnk
      FROM TotalSalesByProductAndHq
)
SELECT h.country
     , h.city
     , h.street
     , r.product_type
  FROM RankedSalesByProductAndHq AS r
  JOIN Headquarters AS h
    ON e.headquarters_id = r.hq_id
 WHERE r.rnk = 1;

Once you are able to do this, you can then bring back in the employee details to obtain the number of products sold in that category per employee:

WITH TotalSalesByProductAndHq
AS
(
    SELECT h.id AS hq_id, p.product_type, SUM(p.value) AS TotalSales
      FROM Sold_products AS p
      JOIN Employees AS e
        ON e.id = p.employer_id
      JOIN Headquarters AS h
        ON e.headquarters_id = h.id
  GROUP BY h.id, p.product_type
),
RankedSalesByProductAndHq
AS
(
    SELECT hq_id, product_type
         , RANK() OVER(PARTITION BY hq_id ORDER BY TotalSales DESC) AS rnk
      FROM TotalSalesByProductAndHq
)
SELECT e.first_name
     , e.last_name
     , h.country
     , h.city
     , h.street
     , r.product_type
     , COUNT(*) AS TotalNumberOfProductsSold
    FROM Sold_products AS p
    JOIN Employees AS e
      ON e.id = p.employer_id
    JOIN Headquarters AS h
      ON e.headquarters_id = h.id
    JOIN r
      ON r.hq_id = h.id
     AND r.product_type = p.product_type
 WHERE r.rnk = 1;

You should check out the documentation for CTE's and the RANK function for more details.


Scaling out mongodb on the same storage We are planning to have some tests to predict how mongo db operates and functions with 2 terabyte of inforamtion on a single collection.

So the plan is to have 15-20 sharded machines (on the same environment / cabinet).
All are considering to have shared EMC storage

I wanted to ask before we start the test :

IS there any different between:
    * Having physical machines writing to independent disks (on the machine).
    * Having physical machines writing to the same storage.
    * Having virtual  machines writing to same storage.
About memory consumption, What are the hardware considerations about querying this amount of data? If I understand correctly, the advantage when you have massive writes is to do sharding with small-level servers, but is it going to change when you need to have some reads?

Thanks

If you have multiple databases, writing to independent disks (with the directory per db) option will be your best bet for performance.  Even if you just have one big huge sharded database, you can still get some performance boost with multiple disks by putting your journal and local database (database where replication info goes) on separate disks, but your main database data files would all have to be on a single disk.
Not sure what you're after here.  The recommendation is that your working set fit in memory for best performance and that still applies for sharding, its just split across multiple servers and thus your memory requirements are also split up across those servers.  That will of course also depend on how well you pick your sharding key, hopefully the load is distributed evenly across all the nodes.  If you plan on returning lots of data from your queries then you might need extra memory on your mongo-s servers as they have to do some sorting and aggregation of what you're bringing back.


Show the matching record only for the 3 rows I have 2 tables box_data and box_data_logs, both with same fields (exact same field names and a same number of records), I am trying to match only the following 3 records to see if in those rows in both the tables the values are 101, 201, 301. If the record matches show the matching record

I tried the following buy it throws an error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM box_data UNION ALL SELECT box_data_log.id , box_data_log.diIndex, box_data_' at line 2

Here is what I tried

SELECT box_data.id , box_data.diIndex, box_data.diMode, box_data.diStatus,
FROM box_data
UNION ALL
SELECT box_data_log.id , box_data_log.diIndex, box_data_log.diMode, box_data_log.diStatus,
FROM box_data_log

The table structure with the values

CREATE TABLE IF NOT EXISTS `box_data_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `box_name` varchar(255) DEFAULT NULL,
  `diIndex` tinyint(1) NOT NULL,
  `diMode` tinyint(1) NOT NULL,
  `diStatus` tinyint(1) NOT NULL,
  `status` varchar(125) DEFAULT NULL,
  `last_updated` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

INSERT INTO `box_data_logs` (`id`, `box_name`, `diIndex`, `diMode`, `diStatus`, `status`, `last_updated`) VALUES
(7, 'boxa', 0, 0, 0, ' ', '2017-03-02 10:49:35'),
(8, 'boxa', 1, 0, 1, ' ', '2017-03-02 10:49:35'),
(9, 'boxa', 2, 0, 0, ' ', '2017-03-02 10:49:36'),
(10, 'boxa', 3, 0, 0, ' ', '2017-03-02 10:49:36'),
(11, 'boxa', 4, 0, 0, ' ', '2017-03-02 10:49:36'),
(12, 'boxa', 5, 0, 0, ' ', '2017-03-02 10:49:36'),
(13, 'boxa', 6, 0, 0, ' ', '2017-03-02 10:49:36'),
(14, 'boxa', 7, 0, 0, ' ', '2017-03-02 10:49:36');


Cannot connect to Oracle 19 instance running in a container under Ubuntu 20.04 via WSL2 on Docker for Windows Here is my docker-compose.yml file:
version: '3.7'

services:

  database:
    image: scgain.azurecr.io/databases/${IMAGE_NAME}
    environment:
      - ORACLE_SID=${SID}
      - ORACLE_PDB=${PDB}
      - ORACLE_PWD=somepassword
      - ORACLE_CHARACTERSET=WE8MSWIN1252
    volumes:
      - ${ORACLE_HOST_DIR}oradata:/opt/oracle/oradata # persistent oracle database data
      - ${ORACLE_HOST_DIR}data-bridge:/data-bridge    # share data with the running container
    ports:
      - 1521:1521
      - 8080:8080
      - 5500:5500

and here the corresponding .env file:
IMAGE_NAME=oracle-19-e
SID=ORCL19CDB
PDB=ORCL19PDB1
ORACLE_HOST_DIR=D:\src\oracle\

When I run this container in Windows via PowerShell, I can connect to the Oracle instance just fine via SQL Developer (through hostname localhost and port 1521) and with my app using this connection string:
Host=localhost;Port=1521;User ID=someuser;Password=somepassword;Service Name=ORCL19PDB1;Direct=true

When I run it instead in WSL2 (Ubuntu 20,04) I can browse the mapped volumes inside the container. I can connect to the instance inside the container via sqlplus and see all the schemas and tables, but trying to connect to it through SQL Developer gives me a Code 17002 error (unable to establish connection). Also, my app throws an analogous exception that it cannot find the database.
Here is the .env file for Linux:
IMAGE_NAME=oracle-19-e
SID=ORCL19CDB
PDB=ORCL19PDB1
ORACLE_HOST_DIR=/mnt/d/src/oracle/

I checked with lsnrctl status that the TNS listener is running inside the container.
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 03-NOV-2020 20:31:27

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                03-NOV-2020 19:50:01
Uptime                    0 days 0 hr. 41 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/32c1b8c48d42/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=32c1b8c48d42)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCL19CDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ORCL19CDB" has 1 instance(s).
  Instance "ORCL19CDB", status READY, has 1 handler(s) for this service...
Service "ORCL19CDBXDB" has 1 instance(s).
  Instance "ORCL19CDB", status READY, has 1 handler(s) for this service...
Service "ab0e9cc3a7c10befe053020016acb4c7" has 1 instance(s).
  Instance "ORCL19CDB", status READY, has 1 handler(s) for this service...
Service "orcl19pdb1" has 1 instance(s).
  Instance "ORCL19CDB", status READY, has 1 handler(s) for this service...
The command completed successfully

What do I need to do to connect to the db outside of its container when it is running in WSL2?

Well, I found the culprit. The port assignment
      - 8080:8080

is not needed (it was legacy from previous work), and once I take it out, I can connect via SQL Developer and my app is happy as well. Not sure I understand the reason, but for now I am back in business.


index_condition_pushdown is used for Queries with the range, ref, eq_ref, and ref_or_null access methods, i.e when there is a need to access full table rows.

Where as Index Condition Pushdown is enabled by default; it can be controlled with the optimizer_switch system variable by setting the index_condition_pushdown flag.

To Switch:

 SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

for more you could read here:
http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

and http://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html


Postgres constraint function on a single column Is it possible to create a constraint in Postgres that will run a function, but only if a specific column is changed?

Right now, I have this:

ALTER TABLE public.employee
  ADD CONSTRAINT pin_is_unique_in_company CHECK
     (fn_pin_is_unique_in_company(id, company_id, pin));

I only want this to run if the pin column has been modified.

CHECK constraints should always evaluate to the same result. If your function is declared IMMUTABLE it's a perfect candidate, else there is more to discuss here. A NOT VALID constraint might be an option. Consider:

Disable all constraints and table checks while restoring a dump

However, a CHECK constraint is run unconditionally. If you want to run the check only if a specific column has been changed, you need a different tool. One obvious candidate would be a trigger, like @a_vlad commented:

CREATE TRIGGER employee_upbef
BEFORE UPDATE OF pin ON public.employee
FOR EACH ROW EXECUTE PROCEDURE fn_pin_is_unique_in_company();

In Postgres 9.1 or later you can restrict the trigger to the change of specific columns.
You can't pass parameters per row to a trigger function, though. Instead reference the special row variable NEW inside the trigger function ...

Related example:

Non-overlapping rectangles constrained to a boundary

However, What you are trying to do sounds like a case for a multicolumn UNIQUE constraint. There is not enough information to tell.


In PLPGSQL assign a variable based on result of SQL function I have a PLPGSQL function which receives an address in two parts:

num = '123'
location = 'main street, anytown, KS'

To utilize the various portions of location I found there is a SQL function SPLIT_PART() which would work how I want it to, in that, if I execute
SPLIT_PART(location,',',2) it would render the 'city' portion.

I've tested this SPLIT_PART() function in pgAdmin4 to verify that it works how I expect it to, but I am having trouble executing this statement within the PLPGSQL environment and assigning the resulting value to a variable.

Through my searching, it appears as though using 'SELECT ... INTO ... ' is what I want to be using, but I have tried running this directly, using EXECUTE  or EXECUTE QUERY only render errors.  the latest being:

ERROR:  syntax error at or near "SPLIT_PART"

Attempt 1

DECLARE
    cn_rec RECORD;
BEGIN
    SELECT split_part INTO cn_rec FROM SPLIT_PART('''|| i_location ||''', '','',2);
--ERROR:  function split_part(unknown, unknown, unknown, integer) does not exist
--LINE 1: SELECT split_part             FROM SPLIT_PART('''|| i_locati...
--                                           ^
END;

Attempt 2

DECLARE
    cn_rec RECORD;
BEGIN
     EXECUTE QUERY 'SELECT split_part INTO cn_rec FROM SPLIT_PART('''|| i_location ||''', '','',2)';
--ERROR:  type "query" does not exist
--LINE 1: SELECT QUERY 'SELECT split_part INTO cn_rec FROM SPLIT_PART(...
--               ^
END;

Attempt 3

DECLARE
    cn_rec RECORD;
BEGIN
    EXECUTE 'SPLIT_PART('''|| i_location ||''', '','',2)' INTO cn_rec;
--ERROR:  syntax error at or near "SPLIT_PART"
--LINE 1: SPLIT_PART('Main St,Anytown', ',',2)
--        ^
END;

Being fairly new to PLPGSQL I'm not sure if I'm up against syntax errors, or fundamental misunderstanding of how this should work.

While you can use SELECT INTO, it's simpler to use the assignment operator := (or just =) to assign a single value:

_city := split_part(_location, ', ', 2);

EXECUTE would only be needed for dynamic SQL (building and executing SQL strings dynamically). Not for a simple assignment.

Demo

CREATE OR REPLACE FUNCTION f_dummy(_location text, OUT _city text) AS
$func$
BEGIN
   _city := split_part(_location, ', ', 2);
END
$func$  LANGUAGE plpgsql;

SELECT f_dummy('main street, anytown, KS');  -- 'anytown'

db<>fiddle here

Related:

The forgotten assignment operator “=” and the commonplace “:=”


Is it expected behavior that MIN() window function in MariaDB/MySQL can return a non-null value from a window with only null values? A minimal example for MariaDB (reproduced with with 10.2 and latest):

DROP TABLE IF EXISTS t;
CREATE TABLE t
(
    id    int unsigned not null,
    value int unsigned null
);
INSERT INTO t
values (1, null);

CREATE OR REPLACE VIEW v
as
select 1                 as id,
       ifnull(null, 999) as value
from t
;

select *,
       MIN(t.value) over (PARTITION BY t.id) as min_t,
       MIN(v.value) over (PARTITION BY t.id) as min_v
from t
         left join v
                   on t.id > v.id
;

It returns

id value id   value min_t min_v
1  null  null null  null  999

bit I would expect it to return

id value id   value min_t min_v
1  null  null null  null  null <-- null instead of 999

because there is only a single row and it has value = null in the window for MIN function. Sure, it is backed by a ifnull() in view's select, but the resulting set has null from a left join with no corresponding row, so it shouldn't be handled by ifnull() in the view, right?

Can this be considered a bug?

MySQL sqlfiddle example comparing using a view vs using nested selects. Nested selects produce the result I expect, using view doesn't.

Comparing to other RDBMS:

Oracle example: both ways work as I expect.

PostgreSQL example: both ways work as I expect.

Reported this as a bug to MariaDB bug tracker:

https://jira.mariadb.org/browse/MDEV-21874


Query MongoDB collection and find rows from 7 days ago I have a MongoDB collection called messages and this is an example document for that collection:

{
    "_id": {
        "$oid": "551943eee4c6acf40fe5a0f2"
    },
    "sender_id": {
        "$oid": "551943eee4c6acf40fe5a0f3"
    },
    "rep_sender_id": "120",
    "sender_name": "Karen Morris",
    "sender_title": null,
    "sender_photo_id": "https://reptool_rpc.pdone.com/web/bundles/pdone/media/representative/photo/qlv_morris_karen.jpg",
    "sender_company": "",
    "recipient_id": {
        "$oid": "5367e006e4c6ac5b0d1b8e29"
    },
    "recipient_name": "KARIS KNIGHT",
    "recipient_title": "",
    "recipient_photo_id": "webdrProfileIcon.png",
    "message": [
        {
            "message_link": "20150330073847782685415",
            "message_href": "//www.themedicalbag.com/pdone/pharma_brandpage?id=20150330073847782685415&brand_id=11",
            "message_href_text": "Prescribing "
        }
    ],
    "message_subject": "has shared information about ADHD treatment option with you",
    "sender_type": "rep",
    "message_type": "share",
    "supplemental": "",
    "reply_ok": "no",
    "mdate": {
        "$date": "2015-03-30T12:39:10.170Z"
    },
    "date": "Mon March 30, 2015",
    "read": "no",
    "timeago": "2015-03-30T08:39:10-04:00",
    "reference_count": 1,
    "chain": [],
    "rep_header": {
        "paccode": "PFIZER_EMAIL_SEG1",
        "sync": "",
        "_st": "ejR2NGQ0eDVrNWw1ajVqNXc0ZjN2NG4zNDQ0NDAzeDU2NDY0dTVsMjQ0YTVhMms1NjNuNDIzbDJzNG00YTU2MzU0NTNtNW0yMjQyNDIzbjJhMzA1YjNiNHQ1NTVkNGE0cDNsNHozdzIwM2o0",
        "hcp_email_address": "karis@magnoliafamily.com",
        "email_target": "tmb",
        "inactive": "no",
        "share_date": "3-30-2015",
        "share_date_2": {
            "$date": "2015-03-30T12:39:10.000Z"
        },
        "hcp_id": "109270",
        "rep_msg": "Thank you for taking time to speak with me today. Here are some additional materials about Quillivant XR&trade; (methylphenidate HCI) CII. I look forward to following up with you in the near future. Thank you.",
        "session_id": "201503300738361681657321",
        "rep_id": "120",
        "hcp_fname": "KARIS",
        "hcp_lname": "KNIGHT",
        "address": "402 JOHNSTON STREET SE",
        "city": "DECATUR",
        "zip": "35601",
        "program_path": "https://reptool_rpc.pdone.com/web/reptool/set/",
        "rep_fname": "Karen",
        "rep_lname": "Morris",
        "rep_title": null,
        "rep_alias": "Morris,Karen",
        "rep_photo_id": "https://reptool_rpc.pdone.com/web/bundles/pdone/media/representative/photo/qlv_morris_karen.jpg",
        "email_subject": "Karen Morris has shared information about an ADHD treatment option with you.",
        "mongo_id": "5367e006e4c6ac5b0d1b8e29"
    },
    "rep_shares": [
        {
            "media_show_citation": "no",
            "server_path_to_media": "https://reptool_rpc.pdone.com/web/bundles/pdone/media/qlv_veeva/brand/presentation/2014_Quillivant_Downloadable_Letter.pdf",
            "company_id": 6,
            "media_citation": "",
            "media_id": 246,
            "media_size": "11",
            "media_title": "Prescribing%20Quillivant%20XR%E2%84%A2",
            "media_description": "Learn%20how%20to%20individualize%20the%20dose%20of%20Quillivant%20XR%E2%84%A2%20%28methylphenidate%20HCI%29%20CII%20according%20to%20the%20needs%20and%20response%20of%20the%20patient.",
            "media_type": "pdf",
            "media_icon": "https://reptool_rpc.pdone.com/web/bundles/pdone/media/qlv_veeva/brand/presentation/2014_Quillivant_Downloadable_Letter-icon.jpg",
            "session_presentation_id": "20150330073847782685415",
            "media_units": "Pages",
            "brand_id": 11,
            "media_expiration_timestamp": 457187927.50113
        }
    ]
}

I've read oficial docs and also this and this post here at SO looking for something helpful but can't find the proper docs for query the collection and get documents from 7 days ago only by using share_date_2. Can any give me some advice here?

I can't test this at the moment, but that's the basic format. You should change your date data type to ISO so it works easily with the below code:

var lastWeek = new Date();
lastWeek.setDate(lastWeek.getDate() -7);
db.messages.find({ "rep_header.share_date_2.$date": { $gte: lastWeek} } } );


Query notifications and connection options I'm investigating a problem with query notifications. The platform is SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Standard Edition.

.NET errors show a problem reported from SqlNotificationInfo Enumeration (http://msdn.microsoft.com/en-gb/library/system.data.sqlclient.sqlnotificationinfo(v=vs.80).aspx ) as: Event Reason: Isolation.

During tracing I notice that the audit login event immediately preceding the Broker conversation shows:

 -- network protocol: TCP/IP
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read uncommitted

So, my working theory is that the transaction isolation level used for the conversation is not supported. But where is this set and how can I change it?

I've read about a similar problem here:

http://www.roelvanlisdonk.nl/?p=3144
and

http://www.sqlskills.com/blogs/bobb/watch-the-sqlnotificationinfo-for-query-notifications/

The explanation to this problem turned out to be an issue with another part of the application explicitly opening read uncommitted connections. These read uncommitted connections were then left in the connection pool and reused by service broker, which can't operate using this isolation level.

The solution was simple: Make sure service broker is connecting with a different connection string, thereby using a different connection pool.


Uninstalling SQL Server Cumulative Update Issue When I am trying to uninstall a CU on my SQL Server 2017 server getting below issue. Has anyone faced this problem and please guide me on how can I proceed. I don't have access the SQL 2017 image right now.

Thanks..

Why are you uninstalling a Cumulative Update?
If you no longer have the CU, download it again, then (you'll have to stop the current setup process first) run:
> C:\whatever\cu-filename.exe /x c:\temp\cu-files\

Then start the uninstall over again, and when you get that dialog, hit Browse... and point it at c:\temp\cu-files.
It may find what it needs in the extracted CU files, but you may have to wait to perform this change until you have access to the 2017 image. (Any edition will do, I expect, so you could download the evaluation ISO for example.)
But let me ask again: Why are you uninstalling a Cumulative Update?


dbcc checkdb gives error "Incorrect PFS free space information for page" dbcc checkdb

query for database gives errors. What is it, is it dangerous and how to resolve?

Full log file: download file or view on github (errors begin from string 364)

Possible
There was disk d:\ crash before (there are .mdf and .ldf files on d:\Data). Disk was restored from backup. But not sure if these errors are related with this hardware crash.

  DBCC results for 'DocStreams'. Msg 8914, Level 16, State 1, Line 1
  Incorrect PFS free space information for page (1:103151) in object ID
  1993058136, index ID 1, partition ID 72057594434682880, alloc unit ID
  71906736119218176 (type LOB data). Expected value   0_PCT_FULL, actual
  value 100_PCT_FULL. Msg 8914, Level 16, State 1, Line 1 Incorrect PFS
  free space information for page (1:103887) in object ID 1993058136,
  index ID 1, partition ID 72057594434682880, alloc unit ID
  71906736119218176 (type LOB data). Expected value   0_PCT_FULL, actual
  value 100_PCT_FULL. Msg 8914, Level 16, State 1, Line 1 Incorrect PFS
  free space information for page (1:104839) in object ID 1993058136,
  index ID 1, partition ID 72057594434682880, alloc unit ID
  71906736119218176 (type LOB data). Expected value   0_PCT_FULL, actual
  value 100_PCT_FULL.

Thank You for response John!

Tried to backup DB and rebuild clustered index, but didn't help. As I understand there is no patch, but it is and old bug. Or something was really damaged during hardware crash..

This helped:

--dbcc checkdb;
--dbcc checktable(DocStreams) WITH ALL_ERRORMSGS;

ALTER DATABASE WSS_Content
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

dbcc checktable(DocStreams, REPAIR_FAST) WITH ALL_ERRORMSGS;
GO
dbcc checktable(DocStreams, REPAIR_REBUILD) WITH ALL_ERRORMSGS;

-- use this only if above does not work
--DBCC CHECKTABLE(DocStreams, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;

ALTER DATABASE WSS_Content SET MULTI_USER;

But first

Do backup database
I also backuped table using this cmd (press Enter multiple times):

bcp WSS_Content.dbo.DocStreams out C:\backups\DocStreams\DocStreams.dat -t -T

pause

Use in Your own risk)

links:
  incorrect-pfs
  bcp tool

Upd.: or You can try "reorganize with (lob_compaction = on)" first (link)


Limit distinct count of group by Suppose for simplicity that I have a table t with columns id and value. I can find the ids that have more than one distinct value using a query such as

SELECT id, COUNT(DISTINCT(value)) AS count
FROM t
GROUP BY id
HAVING count > 1

Is there a more efficient way to do this? Can it be changed so that the count for each id stops as soon as more than one distinct value is found for it?

You can test this variation. In theory, it would use an (id, value) index to find the min and max and would not have to count the distinct values at all:

SELECT id
FROM t
GROUP BY id
HAVING MIN(value) < MAX(value) ;


Views - Indexes/Keys/Execution Plans I am working with a SQL 2005 database server that contains multiple databases and tables that are replicated from various sources. No one but administrators have access to this server.

There is a SQL 2008 R2 database server that exposes all of the tables from the 2005 databases using views (and only views) within a single database. There is no row-level filtering done on these views, they contain the same data as the original tables.

Regardless of why this architecture was chosen, it is causing three specific problems:

In SQL Server Management Studio, when browsing the views, there is no way to determine the indexes or primary keys on the base table.
When using tool such as SSRS to build a drag-and-drop type report, it does not automatically generate the relationship between the views (probably linked to #1).
Running an execution plan against a query written against the views is virtually useless as many of the nodes end up being "Remote Query"

I have done a little research into Indexed Views. The idea would be to basically replace the existing views with indexed views that have the same unique index as the base table. However, I don't think this is possible due to the following:

  All base tables referenced by the view must be in the same database as the view and have the same owner as the view. http://msdn.microsoft.com/en-us/library/ms191432.aspx

Does anyone have any thoughts on how to alleviate the above three problems?

You are correct you cannot use indexed views. Sadly what you have here is a business problem not a database problem. If you need to know the PKs and indexes from the other database and you don't have even viewing access, then require the DBAs who do have that access to provide the information you need to do your job or require them to write and performance tune the queries. No one should be writing queries against a database they do not have access to. If they won't give you access, then the work belongs to them too. I would have my boss talk to their boss and ask them to solve this set of problems for you. They created the problem it's up to them to fix it.


Wildcard search using parameters in function with dynamic SQL What is the proper way to implement a wildcard search in PostgreSQL when using a parameter in a function that uses dynamic SQL?

As a starting point, here is an example from Erwin Brandstetter answering a different question on stackoverflow:

https://stackoverflow.com/a/12047277/538962

CREATE OR REPLACE FUNCTION report_get_countries_new (starts_with text
                                                   , ends_with   text = NULL)
RETURNS SETOF lookups.countries AS
$func$
DECLARE
   sql text := 'SELECT * FROM lookups.countries WHERE country_name >= $1';
BEGIN
   IF ends_with IS NOT NULL THEN
      sql := sql || ' AND country_name <= $2';
   END IF;

   RETURN QUERY EXECUTE sql
   USING starts_with, ends_with;
END
$func$ LANGUAGE plpgsql;

Let's suppose for country_name you wanted to do a leading and trailing wildcard search.

E.g., without using a parameter, AND country_name LIKE '%ic%'.

What is the best way to implement the wildcard search be in this scenario with respect to negating SQL injection risk?

Let's suppose for country_name you wanted to do a leading and trailing wildcard search.

You don't need dynamic SQL for this. Just:

CREATE OR REPLACE FUNCTION report_get_countries_new (_pattern text)
  RETURNS SETOF lookups.countries AS
$func$
   SELECT *
   FROM   lookups.countries
   WHERE  country_name LIKE '%' || _pattern || '%'
$func$ LANGUAGE sql;

Call:

SELECT * FROM report_get_countries_new ('ic');  -- without wildcards!

This negates SQL injection risk completely, since that comes with dynamic SQL.

The caller can still include wildcards at will (unless you process the parameter to filter wildcards), but there is a hardcoded leading wildcard and also a trailing one (unless the parameter ends with \ removing the special meaning from the trailing %).

Even if you work with dynamic SQL and EXECUTE in PL/pgSQL, there is no risk for SQL injection as long as you pass values as values with the USING clause:

Related:

SQL injection in Postgres functions vs prepared queries
PostgreSQL Stored Procedure Performance
Table name as a PostgreSQL function parameter


Move data from a table to another without losing data I have a log table which is currently holding millions of records. I want to enable partitioning on that table, so what I did for now is:

Created a partition function and a partition scheme.
Created an empty table with the same structure on that partition scheme.
Copied data from the current log table from this point in time (let's call it T1) going backwards to the new partitioned table.

The next steps would be to copy the last remaining records from T1 to Tnow and rename the two tables so that the application starts to write to the new partitioned table.

Of course the log table is frequently accessed, so my question is:

How can I make sure that I don't lose any data during this process? Can i make it so that the users don't notice anything or do I necessarily need to stop the application for this brief time? Or can I just block that table so that the application keeps running? If so, how?

Consider creating a new table with the partitioning strategy you want, and add a view atop both tables that does a union all. Have people use the view, and write instead-of triggers against the underlying tables & views.

Inserts should be sent to the new table, updates should move data to the new table, and deletes should be applied to both tables.

Then do batch moves in the background, moving as many records at a time as you can over to the new table. You can still have concurrency issues while this is going on, and some terrible execution plans, but it lets you stay online while the moves are happening.

Ideally, you start the process on a Friday afternoon to minimize the effect on end users, and try to get it done before Monday morning. Once it's in place, you can change the view to point to just the new table, and the terrible execution plans go away. Ideally.

To avoid the triggers firing when the data is being migrated in batches, look at the number of rows in the deleted/inserted tables in the trigger, and skip the activities if they're near to the number of rows in your batch.

The more transparent you want this to be for your end users, the more work (and testing) it's going to take. This especially holds true if you're using partitioning: very often folks believe it's going to make all their queries faster, and yet some of them end up much slower. Try to test as much of your workload on a development server with the partitioned tables if you can.


Can't access Brew PostgreSQL I've used Brew to install PostgreSQL.  I want to log in with psql. Since I haven't set a password I 'm trying to login without one.  When I try I get this:

psql: fe_sendauth: no password supplied

I have this in pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust

Brew is a clean install.  I believe I've removed all instances of postgres (psql doesn't run if I remove the brewed postgresql).

What am am I missing?

Seems like the file was not read properly.

make sure that you restarted the postgresql-server after editing the file
make really sure that it's restarted (I ran into this twice...)
try again.

I searched one day for one suche error and a simple restart was all that was needed.

Also: with the last homebrew-install I made, my current user was setup automatically without password. so I could just execute psql template0 and was connected to the server.

From there, you can create databases, roles and so on.


Too many WAL files in archive folder PostgreSQL Streaming Replication I have two Postgresql 9.5 servers configured in streaming replication. There are no errors in log files and the servers seem to be in sync.

MASTER postgresl.config

archive_command = 'test ! -f mnt/server/archivedir/%f && cp %p mnt/server/archivedir/%f'
wal_keep_segments = 32

Everything else is pretty much default. And yet I have 1600+ files in the archive directory! Because of space constraints I have dared to delete some of them. I would like to figure out what is causing this. I also see that there are only very old files on the slave for the same directory.

The only thing I can think of is that I recently did a restore on a database. I dropped the database, created it and did a backup restore. I did this several times. The slave seems to have caught up with the changes. But I have a ton of WAL files in the archive folder for each of the backup restores. Would appreciate some insight into what could be happening.

wal_keep_segments tells it how many files to keep in the pg_xlog directory, not the archive directory.

PostgreSQL doesn't manage the archive directory for you, that is up to you.  It doesn't even know where (or what) the archive location is, that is why it asks you for an archive_command, rather than just a directory.  As far as PostgreSQL is concerned, your archive command could pass the files through od, send the output to a line printer with acid-free paper, then put that in a 3 ring binder and store them in your attic.  Although more common would be encrypt them and upload them to Amazon's S3, say.

It is kind of weird to use both wal_keep_segments and archive_command together, at least for relatively simple setups.  If you just want a streaming replica and don't need the ability to do point-in-time recovery, you could turn off archiving altogether.

If you really want to keep using an archive but have it cleaned up when the files are no longer needed, you can use the archive_cleanup_command setting.  But now that we have streaming replication and replication slots, that is almost obsolete.


Can't connect to a Oracle db instance via SQLDeveloper When I set the connecting information on the GUI in SQLDeveloper I get the following error (everytime!)

Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection

Connection information:

user: hr
pass: hr
Conn. type: basic / Role: default
hostname: localhost
port: 1521
SID: xe

I couldn't connect with the sys user either.
Previously I successfully unlocked the HR user through SQLPLUS command line
(as described here:
and set the port=localhost in the tnsnames.ora and listener.ora, the OracleXETNSListener service is up and running as well as the OracleServiceXE)
Sadly I'm still getting this error message...
I'm on a laptop (with wireless wifi connection only), under Windows 7.
What might be the problem?
Thank you for your help.

In the meantime I figured it out myself.

The connection couldn't be established from SQL Developer because I was logged into my company's VPN and so "localhost" was resolved somehow as an IPV6 address.

When I closed the VPN connection and tried to connect with the same connection information it worked, localhost was resolved as normal IPV4 address then.


Error in WSFC Role while applying SQL 2016 CU7 in Multiple SQL instance environment Kindly advise on following error:

Cluster resource 'AG1_NAME' of type 'SQL Server Availability Group' in clustered role 'AG1_NAME' failed
Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it.  Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

Environment:

AG1: (Primary = SRV01\DEV1, Secondary= SRV02\DEV1, SRV03\DEV1)
AG2: (Primary = SRV02\DEV2, Secondary= SRV03\DEV2, SRV01\DEV2)

The error occurred while updating AG2 Replicas with SQL SERVER 2016 SP2 CU7 which was SQL Server 2016 SP2 CU4.
Updating order as follows, and there was no error during the update wizard

Set fail-over to manual on SRV01\DEV2
Updated SRV01\DEV2   -- Noticed WSFC error (above mentioned)
Set fail-over mode to Auto on SRV01\DEV2
Set fail-over mode to manual on SRV03\DEV2
Updated SRV03\DEV2
Set fail-over mode to Auto on SRV03\DEV2
Manually Fail-over from SRV02\DEV2 (Primary) to SRV03\DEV2
Updated SRV02\DEV2
Manually Fail-back to SRV02\DEV2 (Primary) from SRV03\DEV2

Is it normal while updating 2nd instance of SQL Server the 1st instance get interrupted while server involved in Availability Groups, or should we follow any particular method in this case to avoid any error as such.
Fortunately, particular AG1 and all resources of WSFC was working normal when i look back immediately (after the error) into Roles page of WSFC manager. also PowerShell  Get-ClusterResource. but i'm concerned about production update and future updates. Any suggestions would be appreciable. Thanks!

Is it normal while updating 2nd instance of SQL Server the 1st instance get interrupted while server involved in Availability Groups, or should we follow any particular method in this case to avoid any error as such.

No, it's not expected. The error, though, says it tried to bring the resource online and failed at least 3 times (by default, unless you changed this value). This leads me to believe that the instance was patched while it was a primary, which would of course fail while the service is offline for patching. If it wasn't a primary then you'll need to look into the SQL Server errorlog and the cluster log to understand what had happened.


Slow query with where and index on some fields I'm working on a project with the PostgreSQL 12 database.
I have a table of about 500 MB.
The structure of the table is as follows:
create table table_name
(
    id serial not null
        constraint table_name_pkey
            primary key,
    rejected_at timestamp,
    reserved_until timestamp,
    reserved_id integer,
    deleted_at timestamp,
    accepted_at timestamp
);

For the following query:
select *
from table_name
where rejected_at is null and (
        reserved_until is null or
        reserved_until <= '2021-05-14 14:23:16'
    )
  and deleted_at is null

I defined the indexes:
create index table_name_deleted_at_index
    on table_name (deleted_at);

create index table_name_rejected_at_deleted_at_index
    on table_name (rejected_at, deleted_at);

create index table_name_reserved_until_index
    on table_name (reserved_until);

But it has a very low performance. What can I do to improve performance?
The result of the explain analysis:
Index Scan using table_name_deleted_at_index on table_name  (cost=0.42..3827.19 rows=15697 width=936) (actual time=106.780..261.118 rows=1534 loops=1)
  Index Cond: (deleted_at IS NULL)
  Filter: ((rejected_at IS NULL) AND ((reserved_until IS NULL) OR (reserved_until <= '2021-05-14 14:23:16'::timestamp without time zone)))
  Rows Removed by Filter: 21704
Planning Time: 0.130 ms
Execution Time: 261.333 ms

I created the suggested index:
CREATE INDEX ON table_name (COALESCE(reserved_until, TIMESTAMP '-infinity'));

select *
from table_name
where rejected_at is null and COALESCE(reserved_until, TIMESTAMP '-infinity') <= '2021-05-14 14:23:16'
  and deleted_at is null

but the query plan selects deleted_at index for the updated query:
(development database)
Index Scan using table_name_deleted_at_index on table_name (cost=0.42..3277.19 rows=4845 width=938) (actual time=15.144..15.201 rows=48 loops=1)
  Index Cond: (deleted_at IS NULL)
"  Filter: ((rejected_at IS NULL) AND (COALESCE(reserved_until, '-infinity'::timestamp without time zone) <= '2021-05-14 14:23:16'::timestamp without time zone))"
  Rows Removed by Filter: 18633
Planning Time: 0.209 ms
Execution Time: 15.251 ms

Rewrite the condition on reserved_until to
WHERE COALESCE(reserved_until, TIMESTAMP '-infinity') <= '2021-05-14 14:23:16'

and create an index for it:
CREATE INDEX ON table_name (COALESCE(reserved_until, TIMESTAMP '-infinity'))
   WHERE rejected_at IS NULL AND deleted_at IS NULL;

Then you should gather statistics:
ANALYZE table_name;


db2 tables altered, reorg When I alter a table in DB, I need to reorg it:

Call Sysproc.admin_cmd ('reorg Table myTable');

if I had done many modifications, how can I know which tables need a reorg?

The reorg is necessary when the table content has changed (lots of inserts, lots of updates, many deletes, a combination of all of that) and you can query which tables need reorganization via 'REORGCHK'

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0001971.html


Rename Primary Key constraint on Azure I'm trying to change primary key on a table in Azure storage.
The plan is to create a new table, move data over and drop the old table.
The problem is that I need all the constraints names to be the same as the old ones.

So I can't create a new table until I rename old table and all the constraints on that table.
I have tried sp_rename on the constraint, but no luck:

sp_rename 'PK_dbo.ContactSessions', 'PK_dbo.ContactSessions_old', 'OBJECT'

gives me that:

Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.

If I run sp_rename like that:

sp_rename 'PK_dbo.ContactSessions', 'PK_dbo.ContactSessions_old'

I get this error:

No item by the name of 'PK_dbo.ContactSessions' could be found in the current database 'testing_only', given that @itemtype was input as '(null)'.

I have seen this thread: How can I alter an existing Primary Key on SQL Azure?
and tried

create clustered index [NEW_INDEX_NAME] on [ContactSessions] ([ContactSessionId])
    with (drop_existing=on);

and got the error:

Could not find any index named 'NEW_INDEX_NAME' for table 'ContactSessions'.

Are there other options to change the index name??

I know that is possible because I can right click on the key name in SSMS and rename the key, but I need it in the script.

It's a good practice to delimit all identifiers. In this particular case, you said the PK_dbo prefix was actually part of the index name. Therefore, the name has to be delimited appropriately, or it cannot be parsed unambiguously.

Also, what's actually being renamed is an index, not an object.

This should work after filling in the blanks:

sp_rename '[<schema>].[<table>].[PK_dbo.ContactSessions]', 'ContactSessions_old', 'INDEX';

I wouldn't use CREATE INDEX ... WITH(DROP_EXISTING = ON) for this because it will physically recreate the index, which is unnecessary in this scenario. This construct is best used for when the definition of the index needs to change while preserving the name.


22046 "Impersonation error" running SQL Server Agent job (New to SSIS. None of the similar questions mention this particular impersonation error.)

I'm trying to run an SSIS package as an Agent job via a proxy, and I'm getting a non-helpful error message. The credential object is for an AD account. I'm able to log in with the password to other services, but attempting to run the Agent job errors out in the Execute job step. Running the job as SQL Agent service account worked, but it doesn't have access to the filesystem that the SSIS job is supposed to pull data from.

I checked the agent log file and it's this:

[298] SQLServer Error: 22046, Impersonation error. [SQLSTATE 42000]

I checked the Windows security log in Event Viewer:

Failure Information:
Failure Reason:     Unknown user name or bad password.
Status:         0xC000006D
Sub Status:     0xC000006A

I tried it with my (sysadmin) user ID and password in a different credential / proxy and got the same error. I know that account works.

We granted both SQL Agent and this account user rights to logon as batch job, and the agent account permission to impersonate. We also tried making the various accounts Windows admins and sysadmins, but nothing has helped.

This worked right out of the box in our QA domain.

Any suggestions how to get this to work? What domain/local/SQL Server security settings would enable or prevent the agent impersonating a user?

Windows 2016 + SQL Server 2016

Check the account that SQL Agent itself is running as.
See this article on the Redgate "SimpleTalk" website about setting up SQL Agent correctly (section under "What service account to use") for more information.
Key part is this:

The core of the SQL Server Agent infrastructure is the SQL Agent
Service. This is a windows service that is responsible for executing
the different types of job steps supported by SQL Server Agent. The
service account defines the Microsoft Windows account used to execute
the SQL Agent service.
You select an account for the SQL Server Agent service by using SQL
Server Configuration Manager, where you can choose from the following
options:
Local System– This is the NT AUTHORITY\System account on the local
machine. It is a member of the Windows Administrators group on the
local machine, and therefore, is a member of the SQL Server sysadmin
fixed server role. Since the account is a Windows administrator, it
has permissions beyond what is required for running SQL Server Agent,
and it is therefore not recommended to be used as a service account.
Local Service– not recommended. Has minimum privileges on local
machine.
Network Service – not recommended. Has minimum privileges on
local machine.
Windows Domain Account – You can also choose a windows
domain account as the service account for SQL Server Agent. Using a
windows domain account and granting it the necessary permissions is
the best practice when choosing a service account. It is also
recommended that this account not be a member of the Windows
Administrators group. Further, the service account is required to be a
member of the SQL Server sysadmin fixed server role on the SQL Server
instance.

In our case, our Sql Agent was running as Local Service.  Changing it to run as Local System immediately fixed the issue.


Connecting to a local MS SQL Server from a GUI client I am very new to SQL and database servers. I have recently been using SQL Server Express and accessing my database using SQL Server Management Studio on Windows. I am now trying to achieve a similar setup on a Linux machine.

I would like to create a local SQL Server instance on the machine, and then use SQLectron as a GUI client for querying my database. I am using Manjaro Linux  and so install my packages from the AUR.

So far I have installed mssql-server, mssql-tools and sqlectron-gui. I have run the sudo /opt/mssql/bin/mssql-conf setup command in order to (I believe) set up a local server. After doing so, running the command systemctl status mssql-server returns this:

[kev@XPS-Manjaro ~]$ systemctl status mssql-server
● mssql-server.service - Microsoft SQL Server Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2017-08-23 13:43:49 IST; 2h 42min ago
    Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 9130 (sqlservr)
    Tasks: 165
CGroup: /system.slice/mssql-server.service
        ├─9130 /opt/mssql/bin/sqlservr
        └─9144 /opt/mssql/bin/sqlservr

Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [145B blob data]
Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [66B blob data]
Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [96B blob data]
Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [100B blob data]
Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [71B blob data]
Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [124B blob data]
Aug 23 13:49:03 XPS-Manjaro sqlservr[9130]: [156B blob data]
Aug 23 13:49:03 XPS-Manjaro sqlservr[9130]: [194B blob data]
Aug 23 13:52:31 XPS-Manjaro sqlservr[9130]: [74B blob data]
Aug 23 13:52:31 XPS-Manjaro sqlservr[9130]: [199B blob data]

I am now attempting to create a local connection to my server through SQLectron on the following screen:

As I have very little experience with SQL Servers, I am completely lost with where to get all of these credentials to connect to my local server. The only one I know of is the password which I set in the sudo /opt/mssql/bin/mssql-conf setup command.

How do I find the server name, host address (127.0.0.1?), port, domain, Unix socket path and username?

If you are running the GUI on the same machine as the mssql-server service, then you can use localhost for the address (127.0.0.1).  If you are running the GUI from a different workstation, you'll need the IPv4 address of the machine where the mssql-server service is running.  You can obtain that by running ifconfig from a Linux terminal prompt.  Sample output from ifconfig:
eth0: flags=4163  mtu 1500
        ether 00:15:5d:89:45:01  txqueuelen 1000  (Ethernet)
        RX packets 423  bytes 137827 (134.5 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

eth1: flags=4163  mtu 1500
        inet 192.168.200.11  netmask 255.255.255.0  broadcast 192.168.200.255
        inet6 fe80::2f70:9d15:8e7d:16cb  prefixlen 64  scopeid 0x20
        ether 00:15:5d:89:45:04  txqueuelen 1000  (Ethernet)
        RX packets 20138  bytes 2006000 (1.9 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 19756  bytes 30125657 (28.7 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 1  (Local Loopback)
        RX packets 3239  bytes 361340 (352.8 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 3239  bytes 361340 (352.8 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

In the output above, the bit that is important is the "inet" address, which in my case is 192.168.200.11.
The default port for SQL Server is 1433 - unless you chose a different port in mssql-conf use that port number.  Ensure the firewall on the Linux box is allowing outside connections via 1433, if you intend to connect to SQL Server via the network.
Use sa as the login, and the password you specified during SQL Server setup via the sudo /opt/mssql/bin/mssql-conf setup command.
I would leave the domain and unix socket path blank.
Once you have connected to the instance, you may want to configure a non-sa account.  Do that with the CREATE LOGIN statement.
Just an FYI, you can use SQL Server Management Studio to connect to SQL Server on Linux, if that's your desire.  Alternately, you can download Microsoft's native GUI client for Linux (and Windows & Mac), Azure Data Studio, here.


AAG databases not getting backed up I am using the following code with Ola Hallalengren's script to run copy only backups on a 3 node AAG setup. I have configured to run backups using the "prefer secondary" option .   When I triggered the job from the primary replica, only db's outside the AAG are backing up and when I trigger it from one of the secondary only AAG db's are backing up.

The priority for backups has been given like so

Primary - 50%

Secondary - 50%

Secondary1 - 40%

What am I missing?Please suggest. Thanks!

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'\\SQL02\Backups', @CopyOnly = 'Y', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = NULL, @CheckSum = 'Y', @LogToTable = 'Y'" -b

I have configured to run backups using the "prefer secondary" option.

Prefer Secondary - Will backup on secondary with highest priority. If no secondary's are available, it will backup on primary.

  When I triggered the job from the primary replica, only db's outside
  the AAG are backing up and when I trigger it from one of the secondary
  only AAG db's are backing up.

First of all i would like to say that i am try to explain in concise way but due to function logic , elaborate a bit more.

Once you have your backup preferences and priority established, then it is time to set up a SQL Server Agent Job to perform the backups. Thankfully Microsoft has provided us with a system function called sys.fn_hadr_backup_is_preferred_replica. This function will return 0 if the current instance is not the preferred backup location or 1 if it is the preferred replica for backups according to the preference and priority settings.

IF (NOT sys.fn_hadr_backup_is_preferred_replica(@DBNAME))
BEGIN
      Select ‘This is not the preferred replica, exiting with success’;
      RETURN 0 – This is a normal, expected condition, so the script returns success
END
BACKUP DATABASE @DBNAME TO DISK=<disk>
   WITH COPY_ONLY;

As per MSDB BOL Here To take the automated backup preference into account for a given availability group, on each server instance that hosts an availability replica whose backup priority is greater than zero (>0), you need to script backup jobs for the databases in the availability group. To determine whether the current replica is the preferred backup replica, use the sys.fn_hadr_backup_is_preferred_replica function in your backup script. If the availability replica that is hosted by the current server instance is the preferred replica for backups, this function returns 1. If not, the function returns 0. By running a simple script on each availability replica that queries this function, you can determine which replica should run a given backup job.

Scripting a backup job with this logic enables you to schedule the job to run on every availability replica on the same schedule. Each of these jobs looks at the same data to determine which job should run, so only one of the scheduled job actually proceeds to the backup stage.

Note:-There is no enforcement of AutomatedBackupPreference. The interpretation of this preference depends on the logic, if any, that you script into backup jobs for the databases in a given availability group. The automated backup preference setting has no impact on ad-hoc backups.

  As,i would like to say that as when you are configuring auto backup of
  AAG through maintenance Plan in 'Primary Replica' & when your Backup
  preferrence will be 'Preferred Secondary Replica' in that case also
  the Non AAG databases will be backup in primary replica backup
  location, after the execution of Maintenance plan schedule backup. And
  Availability databases you shall not find out in the backup location
  in Primary Replica . But When you shall check the 'Synchronization
  state' through ' Show Dashboard' you shall find out, the both Replica
  in 'Synchronized state'.

For reference i am writing the last script, which had ran in my server (as on dated '15-10-2016') through 'maintenance plan backup'. Which script was as follows:-

DECLARE @preferredReplica int

SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica(''Database1''))

IF (@preferredReplica = 1)
BEGIN
    BACKUP DATABASE [Database1] TO  DISK = N''H:\SQLSRV-N01_Backup_Files\Automatic Backup\Database1_backup_2016_10_15_000001_6209992.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''Database1_backup_2016_10_15_000001_6209992'', SKIP, REWIND, NOUNLOAD,  STATS = 10
END

GO
DECLARE @preferredReplica int

SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica(''Database2''))

IF (@preferredReplica = 1)
BEGIN
    BACKUP DATABASE [Database2] TO  DISK = N''H:\SQLSRV-N01_Backup_Files\Automatic Backup\Database2_backup_2016_10_15_000001_7520058.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''Database2_backup_2016_10_15_000001_7520058'', SKIP, REWIND, NOUNLOAD,  STATS = 10
END

GO
DECLARE @preferredReplica int

SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica(''Database3''))

IF (@preferredReplica = 1)
BEGIN
    BACKUP DATABASE [Database3] TO  DISK = N''H:\SQLSRV-N01_Backup_Files\Automatic Backup\Database3_backup_2016_10_15_000001_7770006.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''Database3_backup_2016_10_15_000001_7770006'', SKIP, REWIND, NOUNLOAD,  STATS = 10
END

GO
BACKUP DATABASE [Database4] TO  DISK = N''H:\SQLSRV-N01_Backup_Files\Automatic Backup\Database4_backup_2016_10_15_000001_8030022.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''Database4_backup_2016_10_15_000001_8030022'', SKIP, REWIND, NOUNLOAD,  STATS = 10

GO
BACKUP DATABASE [Database5] TO  DISK = N''H:\SQLSRV-N01_Backup_Files\Automatic Backup\Database5_backup_2016_10_15_000001_8280125.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''Database5_backup_2016_10_15_000001_8280125'', SKIP, REWIND, NOUNLOAD,  STATS = 10

GO

Here I have only changed the database name otherwise everything is as it's the script.
As you can see the above code Database1,Database2 & Database3 are AAG Databases. That's why
automatically it check through 'Scalar-value Functions' (i,e; sys.fn_hadr_backup_is_preferred_replica).
And For the database4 & database5 , it does not check through that function, because these databases are Non AAG Databases.
So, after execution of my 'primary server replica''maintenance plan schedular backup'' only i have found out the Non AAG Databases in specified backup location.

So, I can say that you are not missing anything in Ola Hallalengren's script.

For your Ref Backing Up SQL Server Databases Participating In AlwaysOn Availability Groups and Active Secondaries: Backup on Secondary Replicas (Always On Availability Groups)


Splitting a result row into multiple rows I have the following query:

SELECT
       vs.MonthName AS Month,
       vs.Region,
       vs.Category,
       CAST (MIN (a.TotalAmount) AS money) AS ActualAmount,
       MIN (a.TotalCases) AS ActualCases,
       CAST (MIN (ve.BudgetAmount) AS money) AS BudgetAmount,
      MIN (ve.BudgetCases) AS BudgetCases
FROM
    dbo.VarianceSetup AS vs
    LEFT OUTER JOIN dbo.Actuals
       AS a ON vs.MonthNum=a.Month
                AND vs.RegionId=a.SalesRegionId
                AND vs.CategoryId=a.ProductCategoryId
                AND a.Year=2015
    LEFT OUTER JOIN dbo.VarianceReportEntries
       AS ve ON a.Month=ve.Month
             AND a.SalesRegionId=ve.SalesRegionId
             AND a.ProductCategoryId=ve.ProductCategoryId
             AND ve.Year=2015
GROUP BY
        vs.MonthName,
        vs.Region,
        vs.Category;

which gives me the following output.

+---------+-------------+----------+--------------+-------------+-------------+--------------+
|  Month  |   Region    | Category | ActualAmount | ActualCases | BudgetCases | BudgetAmount |
+---------+-------------+----------+--------------+-------------+-------------+--------------+
| January | New England | Cola     | 439365.6000  | 31200       | 0           | 0.0000       |
| January | New England | Juice    | 110051.7600  | 8424        | 0           | 0.0000       |
| January | New England | Water    | 800.0000     | 40          | 0           | 0.0000       |
| January | NY / NJ     | Cola     | 552583.0800  | 40692       | 0           | 0.0000       |
| January | NY / NJ     | Juice    | 139824.3600  | 10764       | 0           | 0.0000       |
| January | NY / NJ     | Water    | NULL         | NULL        | NULL        | NULL         |
+---------+-------------+----------+--------------+-------------+-------------+--------------+

But for some code I am working on, I need each row to be split into four rows and add a string identifier to indicate the type of row, like this:

+---------+-------------+----------+--------------+-------------+
|  Month  |   Region    | Category |     Type     |    Value    |
+---------+-------------+----------+--------------+-------------+
| January | New England | Cola     | ActualAmount | 439365.6000 |
| January | New England | Cola     | ActualCases  | 31200       |
| January | New England | Cola     | BudgetAmount | 0.0000      |
| January | New England | Cola     | BudgetCases  | 0           |
| January | New England | Juice    | ActualAmount | 110051.7600 |
| January | New England | Juice    | ActualCases  | 8424        |
| January | New England | Juice    | BudgetAmount | 0.0000      |
| January | New England | Juice    | BudgetCases  | 0           |
+---------+-------------+----------+--------------+-------------+

How can I accomplish that by tweaking my query -- OR by creating a query based on the View of this query?

As Mark mentioned, UNPIVOT would be a good choice here.  I often find it's easier to throw the original query into a temp table then unpivot that.  You could use that approach, CTEs or views depending on performance.  All the datatypes need to line up for it to work, so I'm casting everything to MONEY in my example, something like this:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT
       vs.MonthName AS Month,
       vs.Region,
       vs.Category,
       CAST (MIN (a.TotalAmount) AS MONEY) AS ActualAmount,
       CAST (MIN (a.TotalCases)  AS MONEY) AS ActualCases,
       CAST (MIN (ve.BudgetAmount) AS MONEY) AS BudgetAmount,
       CAST (MIN (ve.BudgetCases)  AS MONEY) AS BudgetCases
INTO #tmp
FROM
    dbo.VarianceSetup AS vs
    LEFT OUTER JOIN dbo.Actuals
       AS a ON vs.MonthNum=a.Month
                AND vs.RegionId=a.SalesRegionId
                AND vs.CategoryId=a.ProductCategoryId
                AND a.Year=2015
    LEFT OUTER JOIN dbo.VarianceReportEntries
       AS ve ON a.Month=ve.Month
             AND a.SalesRegionId=ve.SalesRegionId
             AND a.ProductCategoryId=ve.ProductCategoryId
             AND ve.Year=2015
GROUP BY
        vs.MonthName,
        vs.Region,
        vs.Category;
GO

SELECT [Month], Region, Category, ActualAmount, ActualCases, BudgetAmount, BudgetCases
FROM #tmp
UNPIVOT ( [Type] For [Value] In ( [ActualAmount], [ActualCases], [BudgetCases], [BudgetAmount] ) ) upvt


How to select 3 records before x ID and 5 records after x ID with Laravel Eloquent? How can I select 3 records before x ID and 5 records after x ID with Laravel Eloquent or mysql?

(" or mysql")

( SELECT ... FROM ...
        WHERE id < x ORDER BY id DESC LIMIT 3 )
UNION ALL
( SELECT ... FROM ...
        WHERE id > x ORDER BY id ASC LIMIT 5 )
ORDER BY id   -- optional

If you want to include id=x, then change < to <= or > to >=.

How to turn that into Laravel?  I don't know.  Maybe you will need something clumsier.