Tool for identifying stored procs with deprecated syntax? I have to move a DB running in compatibility mode 80 (SQL Server 2000) on a SQL Server 2005 to a SQL Server 2012. How can I scan it for stored procedures that might break due to deprecated or discontinued syntax? I realize that external (VB or Java) programs might also need some changes. I want to

Run Upgrade advisor or you can run server side trace to Identify deprecated SQL Server code -By Aaron Bertrand.

It will show you - what will break when you upgrade to new version of sql server and change the compatibility mode to the upgraded version.

Also, when upgrading from SQL Server 2000 to 2012, consider using both the older 2008 R2 Upgrade Advisor and the new SQL 2012 Upgrade Advisor, as this will provide a more complete picture of issues you may need to fix.


Querying MySQL and SQL Server from same IDE I am currently working with MySQL and SQL Server.

For querying MySQL I use MySQL Workbench, for SQL Server I use SQL Server Management Studio.

I am mixing up shortcuts all the time. For instance, to execute a query in SSMS it's F5 and in MySQL WB it is CTRL-Enter.

Is it possible to query both databases from the same IDE? A plugin for WB or SMSS? A different IDE that works for both databases?

there is a lot of options but choosing the right one is up to you, this is some of the options :

1) Adminer: http://www.adminer.org/

2) DBeaver: http://dbeaver.jkiss.org/about/

3)DBVisualizer Free: http://www.dbvis.com/

4) sql developer (but you need to install the plugins first) : http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html


Slow Query With Multiple Left Outer Joins I am using the following query which has multiple left joins. it takes around 3.2s for execution. I have added indexes on every column which is taking part in JOIN. is it something regarding DB performance?
What all columns I need to consider for index. is a composite index needed in this case?

SELECT
            COUNT(*) FROM
        (SELECT DISTINCT P.PROFILE_ID
            FROM
            user.PROFILE P
            LEFT OUTER JOIN user.PROFILE_ACCOUNT P_AC ON P_AC.PROFILE_ID = P.PROFILE_ID
            LEFT OUTER JOIN user.ACCOUNT AC ON AC.ACCOUNT_ID = P_AC.ACCOUNT_ID
            LEFT OUTER JOIN user.PROFILE_ADDRESS SA ON SA.ADDRESS_ID = P.SHIPPING_ADDRESS_ID
            LEFT OUTER JOIN user.PROFILE_ADDRESS BA ON BA.ADDRESS_ID = P.BILLING_ADDRESS_ID
            LEFT OUTER JOIN user.PROFILE_ADDRESS AC_BA ON AC_BA.ADDRESS_ID = AC.BILLING_ADDRESS_ID
            LEFT OUTER JOIN user.PROFILE_ADDRESS AC_SA ON AC_SA.ADDRESS_ID = AC.SHIPPING_ADDRESS_ID
            LEFT OUTER JOIN user.PROFILE_CUSTOM_PROPS PCP ON PCP.PROFILE_ID = P.PROFILE_ID

         WHERE P.ORGANIZATION_ID = 1
            AND P.ISANONYMOUS = false
     )  AS T_PROF_ID;

Are there ever two billing addresses for one account?  If not, toss the LEFT JOIN for that address; it has no affect on the COUNT(*).  Ditto for all the other LEFT JOINs.

Even if one of the LEFT JOINs leads to extra rows, get rid of them because of the DISTINCT.

Are there multiple profiles for each user?  If not, then much of the other baggage is wasted.

Then get rid of the nested ("derived") SELECT.

SELECT COUNT(*)   -- or maybe COUNT(DISTINCT profile_id)
    FROM profile AS p
    WHERE P.ORGANIZATION_ID = 1
      AND P.ISANONYMOUS = false

That should give you the same count as your original query.  If not, please explain where there are 1:many mappings that are not obvious.

If you aren't really asking about COUNT(*), but rather fetching multiple columns, then show us the real query; it may need other optimizations.  Also, provide SHOW CREATE TABLE so we don't waste time recommending indexes you may already have.


PostgreSQL update JSONB without jsonb_set Would there be any drawbacks to using a normal update statement to update a json(b) column like so:

update "events" set "properties" = '{"type":"graph"}'

Instead of using the jsonb_set function provided by PostgreSQL, which would turn into this statement:

update "events" set jsonb_set("properties", {'type'}, 'graph')

When using eg. an ORM, and calling .save() on a Model for which you've just updated a JSON field, the first method will be called, but as there is no mention of this way of doing things in the  PostgreSQL documentation, I fear this may have some drawbacks.

Given that I'm not incredibly familiar with anything regarding performance in databases, I thought I'd come and ask a question here.

Thanks in advance!

Update: If the result value of jsonb is the same, then the only difference is jsonb_set would take additional CPU (and ms) to run. In both cases you run SET column = VALUE, but if resulted jsonb value is bifferent,both your statements are very much different, here is example.

sample:

t=# create table so63(j jsonb);
CREATE TABLE
Time: 6.290 ms
t=# insert into so63 select '{"a":0,"b":true}';
INSERT 0 1
Time: 1.137 ms

jsonb_set

t=# update so63 set j = jsonb_set(j,'{a}','[2,3,4]');
UPDATE 1
Time: 1.699 ms
t=# select j from so63;
              j
-----------------------------
 {"a": [2, 3, 4], "b": true}
(1 row)

jsonb value changed at specified path! And now with update .. set:

Time: 0.278 ms
t=# update so63 set j = '{"a":[2,3,4]}';
UPDATE 1
Time: 0.918 ms
t=# select j from so63;
        j
------------------
 {"a": [2, 3, 4]}
(1 row)

Time: 0.241 ms

whole jsonb is overwritten. not just "a" key


Is it best practice to install client tools (like Oracle SQL Developer) on the database server or another machine? A company doing a code review asked for credentials to an Oracle database server. They logged in but soon came back asking where SQL Developer was located, stating that they couldn't find it in the "usual location". My DBA told them we never install client tools on the DB server and that they needed to install it on their own client. Which is best practice?

Best practice would be to install and use it from client machines, since every tool running on the server is consuming some resources which will not be available to the Oracle server

Installing it on the server -- probably no penalty as pointed out in the comments


SSRS: textbox format hide complete row (group header) I get a weird behavior formating a textbox in a group header. If the textbox is set to default shows the data, but so soon how set the format to percentage (or anything else) the complete row (group header) won't show.

With the textbox in default:

Set to percentage:

And will hide the complete row:

Manually changed the height of the row and it's working. I don't know if was problem with the screen high resolution (2560 x 1440), I saw similar issues with some windows in SSMS.


How to export all the security-related information from a SQL Server database As you guys probably know, SQL Server does not provide an out-of-the-box solution to export all the security related statements that are declared for each element in the database (both at the database level and also the object level)

I'm talking about the ability to come up with this information

All the users
All the user-defined roles
All permissions at database level (e.g. GRANT CREATE FUNCTION)
All permissions at the object level (e.g. GRANT SELECT ON OBJECT::xxx)

You would think that SQL Server MUST have something like this but neither the SQL Server Export Wizard or the various scripts that are generated as a result of right clicking the objects do capture this information.

I have seen online many different "possible solutions" using non-curated scripts that people graciously post, but since I have to be 100% sure that all security information is captured I can't fully rely on those scripts.

I have the option of using those as a starting point to write something myself but I hate having to re-invent the wheel for a requirement that you would think many people may have.

Isn't there a tool provided by someone (either as part of the SQL Server product or an 3rd party tool) that could reliably provide you with this information?

Or at least, is there a community supported script that most of people would agree "will do the job"?

Thanks!

I'm sorry you haven't had a response since yesterday; here's at least a starting point for you.

You can try pulling the pieces you need out of.  As always, read the discussion threads (I was, regrettably, unable to find a script endorsed by the big names I recognize, so test thoroughly!  Schema, object, server, and column level permissions are often missing).

I haven't actually used any of these, but they're a set of starting points, in no particular order.

List all permission in all databases

Automated Permissions Auditing With Powershell and T-SQL: Part 1

Permission scripting over all databases

Script DB Level Permissions v2.1

Stored procedure to script out user permissions

Security Queries: Database-level

Security Queries: Server-level


Help optimizing MySQL slow query i'm running a wordpress which needs some mysql optimization, i have a slow query and i would like to get rid of "Using temporary; Using filesort"

query:

EXPLAIN SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (1,3,4,5) )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date
DESC LIMIT 0, 10;

+----+-------------+-----------------------+------+--------------------------+------------------+---------+----------------+------+----------------------------------------------+
| id | select_type | table                 | type | possible_keys            | key              | key_len | ref            | rows | Extra                                        |
+----+-------------+-----------------------+------+--------------------------+------------------+---------+----------------+------+----------------------------------------------+
| 1  | SIMPLE      | wp_posts              | ref  | PRIMARY,type_status_date | type_status_date | 62      | const          | 4    | Using where; Using temporary; Using filesort |
| 1  | SIMPLE      | wp_term_relationships | ref  | PRIMARY,term_taxonomy_id | PRIMARY          | 8       | wp.wp_posts.ID | 1    | Using where; Using index                     |
+----+-------------+-----------------------+------+--------------------------+------------------+---------+----------------+------+----------------------------------------------+

i have uploaded the dump files for the 2 test tables (4 categories and 10 posts)

http://pastebin.com/6zhVGQH7

http://pastebin.com/vUnkKqtP

i have tried a lot of things but nothing worked, tried to add indexes and force them, tried with a subquery and a lot more , from what i saw the major problems are on join line and on the order by post_date, mysql can't use type_status_date index for order by because the date column is not "the leftmost column", there is already a post_date index which is not used, even if i reorder the columns in the type_status_date this in not helping

Thanks

I would like to get rid of "Using temporary; Using filesort"

One of the problems I see is that you're using different GROUP BY and ORDER BY clauses. From the manual on how MySQL uses temporary tables:

  If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

As soon as you create a temporary table, it will need to be sorted according to your ORDER BY clause, indicated by 'using filesort'.

This execution plan at leasts uses the indexes to appropriately limit the number of rows found.

I would also look through the docs on ORDER BY optimization.


How do I check my sp_Blitz/sp_WhoIsActive versions across multiple servers/databases? I have a bunch of SQL Servers registered within my SSMS, lots of different SQL versions, editions and DB compatibilities.

I want to check out if any of those servers have any of the sp_Blitz or sp_WhoIsActive stored procedures installed, and which versions they are.

I know I can right-click on my Registered Servers group and open one query window that connects to all the servers in the group, but is there a reliable way to check which SProcs and versions I have?

For sp_Blitz, there's an enhancement request filed at Github to add outputs for version, and if you'd like to influence how the work is done (or contribute code), you're welcome to leave comments over there.

For sp_WhoIsActive, the author Adam Machanic is considering adding an output to the Messages tab each time it runs.

Until then, Oreo's suggestion above of checking the code is the best one. We won't break that intentionally - we haven't changed those strings in years, and don't intend to start, heh.


Why is a table showing up twice in pg_class? I am attempting to run a PostgreSQL statement to query approximate table size count estimate using the following statement:
SELECT reltuples as approximate_row_count FROM pg_class WHERE relname = 'table_name';

ref: Count estimate (PostgreSQL | Wiki)
However, my query is returning two rows, one with a size and one with zero.
Why?

Turns out I had the same table (same name) within two different schemas.  So each had an entry in pg_class.
Work around from the wiki page is SELECT * FROM pg_class WHERE oid = 'schema_name.table_name'::regclass;


Index Reorgs Blocking Statistics Updates Blocking Inserts After upgrading from SQL Server 2014 to SQL Server 2016 last month, we are seeing statistics updates on a table being blocked for hours while a table’s indexes are being reorganized. Inserts are in turn being blocked by the statistics update.

The database in question initially had Auto Update Statistics enabled and Auto Update Statistics Asynchronously set to disabled. We tried enabling Auto Update Statistics Asynchronously but saw the same problem. Last Friday we disabled both Auto Update Statistics and Auto Update Statistics Asynchronously and have not experienced the blocking chain again.

This has been extremely problematic since we get a large bolus of data in each night and this blocking chain prevents that data from filing prior to the start of business hours. I am discussing running index reorganizing less frequently with the rest of the team but am getting some resistance.

Are reorgs blocking statistics updates blocking inserts something you have seen before? What are we losing by disabling Auto Update Statistics and Auto Update Statistics Asynchronously? We are already using Ola’s scripts to update all statistics on a weekly basis.

Here's what I'd try:

Reschedule my reorgs to occur outside of data loads
Change the threshold that I reorg to a much higher value than the default for Ola's scripts (5%) to something like 50%. Also crank rebuilds up to 70 or 80% if you're using them.
Stop running reorg and rebuilds all together, and just update stats after a data load

Update:
For auto update stats, you could

Turn them off during data loads, and back on afterwards/after stats updates run from your job
Leave them off and run stats updates jobs at regular intervals during the day

Hope this helps


Does full text search works with characters? I have successfully created an FTS index on a column where we have stored the name of medicines e.g:

Amoxicillin 250mg capsules 21 capsule
Verapamil 160mg tablets 100 tablet

When I search with the parameters  Amoxicillin  or tablets it works fine.

But when I search for '%Amoxi%' or blets it doesn't come up with the answer. However the same keywords work with Like operator.

select * from AMPP as a
WHERE Contains(A.[NM] ,'%Amoxici%')

Above query has no results . however below query returns 140 rows in result.

select * from AMPP as a
WHERE Contains(A.[NM] ,'%Amoxicillin%')

select * from AMPP as a
WHERE a.nm like '%Amoxici%'

CONTAINS and LIKE are two completely different beasts.

As it is a full text index search the wildcards (% %) become redundant.

For your query I belive you would need to do something like:

SELECT *
FROM AMPP AS A
WHERE CONTAINS(A.NM, '"Amoxici*"');

Refer to MS Books Online


Convert string to number from xml at server side I am having issues converting numbers stores as character (with . as decimal, sometimes are null) to number from xml on server side. Query is part of package. When I run the query below on my computer, it works. However when I call the procedure from UNIX, it fails and I am getting following error message:

Errors:
ORA-06512: at "DTWH.PKG_GPA", line 246

----- PL/SQL Call Stack ----- object line object handle number name 0x16967d328 390 package body DTWH.PKG_GPA 0x90bf2568 3 anonymous block

ORA-01722: invalid number

SELECT cast(nvl(trim(replace(xmltype(t.sce_msg).extract('//RoofInsulationCSV/Total/text()', 'xmlns="http://INT010.GPA.Schemas.External.GPA_AllFile')
                      .getstringval(),'.',',')),0) AS NUMBER(7,2)) AS amount
FROM        ta_main.serv_cons_event t
INNER JOIN  ta_main.event e
      ON    t.sce_evt_id_s = e.evt_id_s
WHERE       e.evt_typ_id_c IN ('INV', 'ACC')

I tried various approaches like to_number, replace with regex or whatever I found. Always work with me, never from UNIX.

Any idea, what I am missing?

cast relies on your session's NLS settings to convert between datatypes.

You should use to_number with an explicit number format model, something like:

SELECT to_number(nvl(trim(replace(xmltype(t.sce_msg).extract('//RoofInsulationCSV/Total/text()', 'xmlns="http://INT010.GPA.Schemas.External.GPA_AllFile').getstringval(),'.',',')),0), '999999999,99')
FROM        ta_main.serv_cons_event t
INNER JOIN  ta_main.event e
      ON    t.sce_evt_id_s = e.evt_id_s
WHERE       e.evt_typ_id_c IN ('INV', 'ACC');

You'll have to play around with the format model to fit your expected data.


Stored procedures, binlog and SUPER privilege I tried to create this function on Amazon RDS for MySQL:

CREATE FUNCTION GetRootZoneId (zoneId INT) RETURNS INT READS SQL DATA
BEGIN
    DECLARE _id, _parentId INT;
    SELECT zoneId INTO _parentId;

    my_loop: LOOP
        SELECT id, parentId
        INTO _id,_parentId
        FROM Zone
        WHERE id = _parentId;

        IF _parentId IS NULL THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;

    RETURN _id;
END;

This would only be used in a one-off query to update an existing database table.

Running it got me the following error:

  ERROR 1419 (HY000): You do not have the SUPER privilege and
  binary logging is enabled (you might want to use the less safe
  log_bin_trust_function_creators variable)

Does this restriction apply to stored procedures as well, or just functions?

If it does apply to stored procedures as well, is there any alternative to execute an equivalent code without setting log_bin_trust_function_creators=1? Please note that I know how to change this setting on RDS, I'd just like to avoid that if possible.

You can't avoid it. The restriction applies also to stored procedures. The only alternative would be to have the SUPER privilege, but I don't think it's possible in this case.

Moreover, you have to declare your function as DETERMINISTIC, telling explicitly that you're not going to modify data:

CREATE FUNCTION GetRootZoneId (zoneId INT) RETURNS INT DETERMINISTIC READS SQL DATA
BEGIN
    DECLARE _id, _parentId INT;
    SELECT zoneId INTO _parentId;

    my_loop: LOOP
        SELECT id, parentId
        INTO _id,_parentId
        FROM Zone
        WHERE id = _parentId;

        IF _parentId IS NULL THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;

    RETURN _id;
END;


How to find the price based on the given range Here i need to find the price of the qty based on which price range the qty lies. In the first date(2015/01/01) 5 quantities have been sold and we made entries from (1 to 5) ,On second day for 10 quantities, the entry should be made from (6-15) and so on.

Example : Product A will be sold in the price of $ 100 per quantity between 1-3 (qty)

The 4th qty and more will be sold in the price of $ 110.

I've written the query, but is there any way to make it simple ?

# PRODUCT #

PROD    DATE         QTY
 A |   2015-01-01  |  5
 A |   2015-02-01  | 10
 A |   2015-03-01  |  5
 A |   2015-04-01  | 12

 # PRICE #

PROD |  SRANGE |  ERANGE |  PRICE
A    |    1    |  3      |   100
A    |    4    | 15      |   110
A    |   16    | 30      |   120

# RESULT #

PROD    DATE         QTY      PRICE
 A      2015-01-01   5     (300 +220) =520
 A      2015-02-01   10    1100
 A      2015-03-01   5     600
 A      2015-04-01   12    1500

Declare @Ddate as date
Set @Ddate = '2014-01-01'

select DISTINCT Top 1   case when
 (select COUNT (*) from PdtDet where ERange >=
 (select Top 1  Qty +1 from Pdt where Pdate <= DATEADD(d,-1,@Ddate) and Product = 'A' order by Qty  desc)
  and ERange<=T0.Qty and Product ='A') =0

then (select sum(Price) from PdtDet PD where PD.Product = 'A' and
 PD.SRange >= (select Top 1 case when (select COUNT(*) from Pdt where Pdate < @Ddate and Product = 'A')  >0 then
 (select Top 1  Qty +1 from Pdt where Pdate <= DATEADD(d,-1,@Ddate) and Product = 'A' order by Qty  desc) else 1 end) and PD.SRange<=t0.Qty)

 else
 (select Top 1 Price   from PdtDet PD where PD.Product = 'A' and
  PD.ERange >= (select  Qty +1 from Pdt where Pdate <= DATEADD(d,-1,@Ddate) and Product = 'A') and PD.ERange <= T0.Qty)
 end
   'Price'
     from Pdt T0 inner join PdtDet T1 ON T0.Product = T1.Product where t0.PDate = @Ddate and t1.Product = 'A'

I don't get the same output but I think there is an error in your sample with the last row (=1500) and/or a price for QTY>30 is missing.

You can look at this SQL Fiddle and SQL Server query:

With list as (
    Select n = ROW_NUMBER() over(order by (select 1)) From (
        Select n = 1 From (values (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x1(n)
        Cross Join (values (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x2(n)
    ) as x
)
Select pr.PROD, pr.[date], QTY = count(l.n), Price = sum(pi.price) From list as l
Inner Join Price pi on l.n >= pi.srange and l.n <= pi.erange
Inner Join (
    Select p1.PROD, p1.date, previous = coalesce(SUM(p2.QTY), 0), p1.QTY
    From Product as p1
    Left Join Product  as p2 on p1.PROD = p2.PROD and p2.date < p1.date
    Group By p1.PROD, p1.date, p1.QTY
) as pr on pr.PROD = pi.PROD and l.n > pr.previous and l.n <= (pr.previous+pr.QTY)
Group By pr.PROD, pr.date

Output:

PROD | date         | QTY   | Price
A    | 2015-01-01   | 5     | 520
A    | 2015-02-01   | 10    | 1100
A    | 2015-03-01   | 5     | 600
A    | 2015-04-01   | 10    | 1200

With 1 more row in Price such as ('A', 31, 50, 150), it gives the output you want:

PROD | date         | QTY   | Price
A    | 2015-01-01   | 5     | 520
A    | 2015-02-01   | 10    | 1100
A    | 2015-03-01   | 5     | 600
A    | 2015-04-01   | 10    | 1500

SQL Fiddle with Price > 30.


Is this the right attitude from the DBA team? I'm a software developer at a rather large company with a small team of software developers. I am the only remaining developer (.NET developer) in the "reports" team of the company. The rest of the developers (7 in total) are in the transactional part of the software. There are two "report analysts" with me.

This company has three full-time DBAs. But lately I've been receiving what, in my opinion, are too many tickets from the DBAs. Most of these tickets are complaints from the DBAs that our scripts are running "too slowly". They decided that 3 minutes should be enough for any script, and anything that takes more than 3 minutes needs fixing. The other team, the transactional team, basically just does CRUD operations (mostly C, we're into "tens of millions of rows" territory). The company has been bought by a larger company and dozens of people were laid off. There is no money for new servers (production is a '09 Xeon with 24GB RAM, and 10K SAS).

The problem seems to be that the DBA expect any script to run under 3 minutes, except of course, being the reports person, I have to sift through millions of records to get the required statistics (our reports are done nightly because of that reason). The transactional team has also broken down the database to textbook normal form. This means most scripts start off 10 INNER JOINs.

The DBAs messages aren't very helpful. I'm often required to look at query plans and make sure the indices are used. They expect the developers to worry about index creation and preformance. I'm not really sure that, as a software developer, it's my job to worry about indices.

I also have no production credentials, so I have no way of knowing if certain indices are present in production (often I get tickets back that "this table has more indices than data!!!!"). I also have no SP execution permissions (big no, developers have only SELECT permissions in production). And certainly not SHOWPLAN permissions.

The main issue seems that we don't have a copy of production to test our queries against. There is a mockup with a few records, and of course any script runs really quickly against a few records but runs slow on production. So basically I have to make guesses about SQL performance tuning, send the script out to the DBA to deploy (that seems to be their job description), and wait for their ticket next day to tell me in what new way the script has failed or made things worse.

Today I received a ticket with the following description: "X sp taking longer than 3 minutes. Replace CURSOR with WHILE", so this is why I'm asking:

Is this normal procedure for DBA operations? Am I wrong to assume that, if an SP is underperforming, it should be the DBA's job to optimize it?
In case it's not the DBA's job, but mine, should I have more permissions to the production DB for the required traces, query plan, SP execution, etc?
Is it normal not to have a copy of the production DB to test large queries against?

I would like to present my case to the boss here, but I want to be sure I'm not wrong about where my responsibilites as a .NET developer end.

I agree with ypercube - the best course of action would be getting a test environment with a recent backup of production data. You say you have SELECT permissions in production so it doesn't seem as if information is being kept confidential from the dev team. If this is possible, you would be able to quickly test your procedures and find possible improvements.

I disagree with your assumption that it is the DBA's job to optimize an inefficient query. Part of the role of the developer is to write queries and/or code with performance in mind.

However, I also disagree with the DBAs that all queries should run in under 3 minutes with no exceptions. That is an absurd statement if you are writing complex reports against millions of records.

Sounds like there needs to be some give and take from both sides. You may be able to make your queries more efficient, but the DBAs need to give you the tools to assist with that - mainly a test version of current production data. Also, if the queries are indeed performing poorly then the DBAs should provide you with trace logs and other information to help you pinpoint any possible hangups with your query.


Postgres Client - save table sorting Is there a Postgres client (for Windows) that can save customized sorting when viewing tables? Obviously I could write and save a query for this, but perhaps there's a client that can do this for me. I know MS Access can do this (via ODBC), but I'd prefer a better client.

Why not create a VIEW? The upside is that it is client independent.


SQL Server Log Shipping for Server Migration - What's the final step? Working with a server running SQL Server that was the log shipping target for two other servers as part of server consolidation/migration. In other words, server B was log shipping to server A and server C was log shipping to server A and the we failed over both to A and retired B and C.

Server A is happily chugging along. All the old log shipping related jobs were paused and deleted long ago.

Enter a new DB monitoring tool we are trialing (Spotlight Cloud) that throws warnings about our server being a secondary with a high restore latency (2 years! lol). Now, I know this isn't really an issue (afaik) but it does cause me to wonder -- what step did I skip in those failovers?

I still have entries in dbo.log_shipping_secondary. Should I? I have looked through SQL Docs online and don't really see anything covering this scenario as far as final steps.

You need to remove the log shipping metadata. The correct way to do this is via stored procedures, which also removes the jobs (I'm guessing you've deleted those manually).

Execute sp_delete_log_shipping_secondary_primary. This removes the information about the old primaries on the secondary server (Server A). Also removes the jobs if they exist.
Execute sp_delete_log_shipping_secondary_database. This removes the information about the secondary database. NOTE: This does not remove the DB itself, even though the documentation wording makes it sound like it might. Also, this should be called as part of step 1's stored proc, however, I've found that with orphaned log shipping configs, sometimes it doesn't execute so I run it manually afterwards.


Database Mail to Distribution List I have a SQL Server 2005 which I have configured the database mail to setup monitoring alerts and notifications. I also create a email distribution list containing a couple of emails. The thing is that when I send the email from the database to a distribution list, it never gets to the emails inbox, but if I do it individually it does.

I tried to send an email from the outlook to the distribution list and it worked fine. I do not understand what is happening. Please can you help me?

Odds are the the account with which the Database Mail profile is logging into the SMTP server doesn't have rights within Exchange to send to the distribution list.  You need to work with your exchange admin to see what's going on within the Exchange server.  There are tracing tools within Exchange they can use to see exactly what's going on.


How do I modify a column type if the DB has many values I'm currently trying to convert a column in SQL Server from nvarchar(255) to decimal(18,0). However, this is the error I get:

  'member_details' table
  - Unable to modify table.
  Error converting data type nvarchar to numeric.

Before this error, Management Studio gave a warning about changing the datatype of columns with many rows. My DB contains about 1,000,000 rows.

Can anybody help? Any help will be greatly appreciated.

The problem isn't the number of rows, the problem is that you have a row which does not fit into the decimal(18, 0) data type. If you're on SQL 2012 you can identify the rows fairly easily (and can do your own number checking function on other versions).

Create Table dbo.Moo (
    Data Nvarchar(255)
)

Insert dbo.Moo Values ('-1'), ('100'), ('1.1'), ('Cows')

Select  Convert(Decimal(18, 0), Data)
From    dbo.Moo
-- Msg 8114, Level 16, State 5, Line 7
-- Error converting data type nvarchar to numeric.

Select  *
From    dbo.Moo
Where   Data Is Not Null
And     Try_Convert(Decimal(18, 0), Data) Is Null
-- Cows

Once they're identified you can fix the data on those rows and then convert the table over as you originally attempted.


SQL Server Command Line install - Default database directories Is there any way using the Command Line install method, I can specify a custom location for the system databases?

I want them to be in D:\SqlServer\Data

or is this only way to do this to install them in the default directory and then move them after?

Yes you can do this. Best way to do is use the configuration.ini file and customise it as per your use.

For system datafiles you will need to edit this portion of the file :
INSTALLSQLDATADIR="S:"

ABove example is where my system datafiles go into.. (S: )


Sum(JSON_EXTRACT) MariaDB 10.3.11 I'm looking so long, and cannot find anything right concerning - ho to sum the values from JSON column.

My values in the column looks like this:

[{"file":"FILE.doc",
  "POSITION":"3245",
  "NUMBER":"2345",
  "PRICE":"123",
  "PRICE2":"3456",
  "NUMBER":"2019-01-25",
  "date":"2019\/01\/15",
  "added_by":"admin"
 },{
  "file":"FILE.doc",
  "POSITION":"345",
  "numer_faktury":"456",
  "PRICE":"125",
  "PRICE2":"567",
  "DATE":"2019-01-11",
  "DATE":"2019\/01\/15",
  "added_by":"admin"
}]

Table = table
column = prices

My select to extract the values looks as follow:

SELECT JSON_EXTRACT(prices,
    '$[*].PRICE') as quality FROM table
    WHERE employee_id = 10060

And I've got this:

|  QUALITY   |
["123", "125"]

Is there any possible to sum the values and get:

|   QUALITY     |
|   ["248"]     |

Another way to solve this would be by using Recursive Common Table Expressions which is available since MariaDB 10.2.2. This solution does not need a numbers table but recursively iterates the result until the computation of the SUM is complete.

I made a small example which is similar to your data:

CREATE TABLE test_json (
    prices JSON NOT NULL,
    employee_id INT
);
INSERT INTO test_json (prices, employee_id) VALUES (
    '[{"PRICE":"123"},{"PRICE":"125"}]', 10060
), (
    '[{"PRICE":"5"},{"PRICE":"10"},{"PRICE":"15"},{"PRICE":"20"}]', 10070
);

Now the actual query to retrieve the result:

WITH RECURSIVE json_sum (price_sum, prices) AS (
    -- select all entries you need to calculate the sum for, initialize sum as 0
    SELECT 0 AS price_sum, JSON_EXTRACT(prices, '$[*].PRICE') AS prices FROM `test_json`
    WHERE employee_id = 10060 -- this is your original WHERE condition from your question
  UNION
    SELECT
       -- add the value of the first entry in price array to the sum
       price_sum + JSON_UNQUOTE(JSON_EXTRACT(json_sum.prices, '$[0]')) AS price_sum,
       -- remove the first entry from the price array, so the next iteration will not add it again
      JSON_REMOVE(json_sum.prices, '$[0]') AS prices
    FROM json_sum
    WHERE JSON_LENGTH(json_sum.prices) > 0
)
-- select all sums, filtering out the rows where calculation is incomplete
SELECT price_sum AS quality FROM json_sum WHERE JSON_LENGTH(json_sum.prices) = 0;


Oracle pivot on a column with delimited data My data is like:

keycol,col1,col2,col3
1,a;b;c,some data,some other data
2,x,some data,some other data
3,y;z,some data,some other data

Where a column is delimited in the source system with semicolons.

And I want to pivot it to:

1,a,some data,some other data
1,b,some data,some other data
1,c,some data,some other data
2,x,some data,some other data
3,y,some data,some other data
3,z,some data,some other data

I found a technique here, but I can't quite get it to work:

CREATE TABLE yt
    (keycol int, col1 varchar2(5), col2 varchar2(9), col3 varchar2(15))
;

INSERT ALL
    INTO yt (keycol, col1, col2, col3)
         VALUES (1, 'a;b;c', 'some data', 'some other data')
SELECT * FROM dual
;

INSERT ALL
    INTO yt (keycol, col1, col2, col3)
         VALUES (2, 'x', 'some data', 'some other data')
SELECT * FROM dual
;

INSERT ALL
    INTO yt (keycol, col1, col2, col3)
         VALUES (3, 'y;z', 'some data', 'some other data')
SELECT * FROM dual
;

I thought I could include the keycol in the CONNECT BY this way to get parallel recursive chains, but I guess it doesn't work like that.  I'm pretty sure I've done this with recursive CTEs in SQL Server.

SELECT keycol
  ,trim(regexp_substr(col1, '[^;]+', 1, level)) col1
  ,col2
  ,col3
FROM yt t
CONNECT BY keycol = PRIOR keycol AND instr(col1, ';', 1, level - 1) > 0

http://sqlfiddle.com/#!4/3d378

FWIW, I'm on Oracle 10g.

Using the code from this link as a starting point, this should give you the result without the need for a DISTINCT:

select t.keycol,
    regexp_substr ( t.col1, '[^;]+', 1, n) as col1,
    t.col2,
    t.col3
from yt t
cross join
(
    select level n
    from
    (
        select max(length(col1) - length(replace(col1, ';'))) as max_semi
        from yt
    )
    connect by level <= 1 + max_semi
) c
where c.n <= 1 + length(col1) - length(replace(col1, ';'))
order by t.keycol, col1;

See SQL Fiddle with Demo


Foreign Key, on delete set null Am I misunderstanding something here? I have two tables, table1 and table2. Table2 has a foreign key that references a field in table1 with ON DELETE SET NULL associated with the foreign key. Table2 also has a TIMESTAMP field that has ON UPDATE CURRENT_TIMESTAMP set.

Modifying an entry in table2 successfully updates the timestamp field.
Deleting the linked entry in table1 successfully sets the foreign key field in table2 to NULL, but does not update the TIMESTAMP field in table2.

When the field in in table2 gets set to NULL, isn't that considered an UPDATE, and the timestamp should get updated too?

This is documented, although not very clearly, in MySQL docs: InnoDB restrictions:

  Cascaded foreign key actions do not activate triggers.


Oracle 12c backup question with FRA If I wanted to backup to the FRA and another location, how would I configure the channels? Also, would I need to set parallelism?

Thanks

Duplexing Backup Sets

  ... It is not possible to duplex backup sets to the fast recovery
  area.

A similar effect could be achieved by setting the format to the location of FRA, but it will not be the same, as these files will not be accounted in the space usage of FRA.

configure device type disk parallelism 2;
configure channel device type disk format   '/fra/%U',   '/oracle_backup/%U';
configure datafile backup copies for device type disk to 2;
configure archivelog backup copies for device type disk to 2;
configure default device type to disk;

backup database plus archivelog;


Page Life Expectancy sudden drop Looking at performance data I realized sudden drops of Page Life Expectancy on a Sql Server 2016 SP1 running on VMWare consuming 58982 MB of 64 GB RAM. The previous value of PLE was around 133,000 and suddenly it dropped to 7,300 sec.

It looks like there was only one single query a candidate to cause this. I uploadad the execution plan.

That query ran very early in the morning so it looks like there was almost no additional activity on the system. It required 01:27 m:s runtime and caused 600,000 Reads.

Why did this query cause this drop of PLE?

What are the consequences of that drop?

PLE is a bit of a flaky metric. Sure, it's better when it's high and steady, but if you have more data than RAM, that ain't always gonna happen.

Things that can make PLE drop:

Queries that get large memory grants
Queries that displace large amounts of pages in memory with new
ones
Queries that modify many pages and force them to get flushed to
disk
DBCC CHECKDB
Index Rebuilds

PLE is more of a concern if it's constantly low. As with most things, you need to look at your overall wait stats to see if it's an issue.

Since you're looking at My Favorite Monitoring Tool© -- what is it telling you about overall wait stats? If you don't know where to find those, contact support.

The RAM-ifications (wait for it) are that you'll likely need to read those pages back into RAM (there it is) at some point.

Hope this helps!


MySQL "CREATE TABLE LIKE" include triggers I'm using MySQL 5.6 (5.6.29-76.2-56-log Percona XtraDB Cluster)

When I issue a CREATE TABLE LIKE the triggers on the tables are not copied - is it possible to get them on the new table as well?

The long and the short answer is no! This is yet another "non-feature" of MySQL. This construct is known more commonly as CTAS (CREATE TABLE AS.... SELECT) - the CREATE TABLE LIKE syntax is non-standard - like so much of MySQL!

MySQL's documentation argues here - that this lacuna is to ensure "flexibility" - you'd laugh if it wasn't so sad!

  CREATE TABLE ... SELECT does not automatically create any indexes for
  you. This is done intentionally to make the statement as flexible as
  possible.

You can create them (indexes) by declaring them before the SELECT as follows

  If you want to have indexes in the created table, you should specify
  these before the SELECT statement:

> mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

But you're out of luck for TRIGGERs, FUNCTIONs and STORED PROCEDUREs.

If this is important to you, you could try your luck with PostgreSQL - which has a table inheritance mechanism, whereby schema changes are propaged, but I can't see why a WITH INDEXES, WITH FUNCTIONS, WITH STORED PROCEDURES syntax couldn't be introduced.

If you put in a feature request (good luck with that! MySQL doesn't even have CHECK CONSTRAINTs yet), I'd be happy to file a "me-too" if you post the link back here for my attention.

The CREATE TABLE LIKE is a similarly crippled option - from here:

  CREATE TABLE ... LIKE does not preserve any DATA DIRECTORY or INDEX
  DIRECTORY table options that were specified for the original table, or
  any foreign key definitions.


Is there any difference on performance when choosing DISTINCT or GROUP BY to bring distinct values? I would like to know if there is any difference concerning performance when choosing DISTINCT or GROUP BY to bring distinct rows from a query.

I've tried comparing the execution plans, but they seem to be the same for both queries.

Or does it have to do with the complexity of the query? If so, an example would be appreciable.

Example - when I run the following queries against the database it shows exactly the same execution plan for each one:

select distinct table_name
from information_schema.columns
where table_name = 'Customer';

select table_name
from information_schema.columns
where table_name = 'Customer'
group by table_name;

PS.: These simple queries are just to illustrate the question with an actual example.

The two queries are functionally identical so should perform the same and as you can see from the query plans SQL server has indeed spotted that there is no difference between the two.

Of course as soon as you need to perform and aggregate operations you need to use grouping instead, otherwise you'll end up having to use the "distinct" version as a derived table and joining for the other detail which is not going to be the efficient way around.


MySQL root changed password went wrong So basically this is what I did:

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set password=PASSWORD("~)<iXyyj<w!,s!\yZ%]YN#m;d") where User='root';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

And now I can't access using that password. What did I do wrong? maybe the password isn't supposed to have special characters or something?

PS: centos 6.5

You may have to do the following
NEWROOTPASSWORD="Cha@g3m3"
service mysql restart --skip-grant-tables --skip-networking
SQL="update mysql.user SET password=PASSWORD('${NEWROOTPASSWORD}') WHERE user='root';"
mysql -ANe"${SQL}"
service mysql restart
mysql -uroot -p${NEWROOTPASSWORD}

If you get the mysql prompt, CONGRATULATIONS !!!
Then, go change the password properly. Don't use the exclamation point or semicolon for a password as they mean something to Linux.
MySQL Documentation for this situation: http://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html
Give it a Try !!!


Database backup with no frozen I/O Is there a way to make automated  backups of a DB without having the I/O frozen ?

I am using an Amazon EC2 instance .

I/O is frozen on database distribution. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

Shadow backups are taken by VSS backing up all the C: drive  lead into baking up the database and then lead into the I/O frozen message seen in the SQL server log.

The database backup when it is done normally via maintenance plan, via SQL Management Studio or via user  SQL query  does not generate I/O frozen message .


Speeding up a NOT IN subquery Good evening,

I'm currently up to try to improve the performance of some of my queries.
As far as I know, statements like "IN" or "NOT IN" are even faster with a large amount of values if a subquery on an indexed field without conditions is used.

SELECT * FROM table1 WHERE field1 NOT IN (SELECT index_field FROM table2)

When it comes to use conditions at table2, the query becomes realy slow on a large amount of data.

SELECT *
FROM table1
WHERE
    field1 NOT IN (
        SELECT
            index_field
        FROM table2
        WHERE
            user_id = '2'
    )

I was thinking about using LEFT Join but when I need to to the filtering for the user_id, it also becomes slow.

How may I solve this problem? Currently, I've no clue about that.

The original query looked sth. like this

SELECT
    i.*
FROM stream_item si
LEFT JOIN user__publisher_item ui
    ON ui.user_id = 2
    && ui.item_id = si.item_id
INNER JOIN item i
    ON i.id = si.item_id
WHERE
    si.stream_id IN (5,7,8,9,19,24,29,42,43,44)
    && ui.id IS NULL || (
        ui.`read` = 0 || ui.saved = 1
    )
GROUP BY
    si.item_id
ORDER BY
    si.`found` DESC
LIMIT 0,10

id      select_type     table   type        possible_keys                                                                           key                 key_len     ref                 rows        Extra
1       SIMPLE          si      index       PRIMARY,stream_2_item,stream_id_found                                                   stream_2_item       4           \N                  663236      Using temporary; Using filesort
1       SIMPLE          ui      eq_ref      user_id_item_id,user_2_item,user_id_read_saved_hidden,user_id_saved,user_id_hidden      user_id_item_id     8           const,si.item_id    1           Using where
1       SIMPLE          i       eq_ref      PRIMARY                                                                                PRIMARY             4           si.item_id          1

I have a table which represents the n:m relationship between stream and items.
An unique item can be accessible via multiple streams.
Based upon this i have a state-table for user dependent states of an item.
Currently, the user-state-entry will only be generated, if the item was read or saved the first tim. Otherwise, there is no entry in the user_item table.
In the beginning, this query was fine but now, it takes a lot of time. Even if I leave out the "INNER JOIN" it takes 7 seconds on the current tables with around 500k rows each.

The table-structure in the background is as follows:

stream
stream_item //n:m relation between stream and items
item //unique items
stream_group //user-defined groups of multiple streams
stream_group_streams //_n:m relation between stream_groups and streams_
user
user_item //state-table that holds the user-dependent states of an item - if there is no state, no row exists for an item

I already asked this question at Stackoverflow but added my 2 Edits with further information to late so that I haven't got an answer anymore.

In general you can replace a NOT IN (...) subquery with a NOT EXISTS (...) and the latter will be faster. Logically they are the same though some database optimizers handle the NOT EXISTS better as they can stop the subquery check after the first match.

In contrast, if the DB optimizer isn't smart enough to figure out what your doing and the subquery has a lot of rows, then it must buffer all the rows for each subquery before performing each NOT IN check.

Try out something like this:

SELECT *
FROM table1 a
WHERE NOT EXISTS(SELECT 1
                   FROM table2 b
                  WHERE b.user_id = 2
                    AND b.index_field = a.field1)

In this example the optimizer need only check if a single row exists for the matching subquery, including the additional filter on b.user_id = 2. It should use an index on the index_field and user_id if they are available.


In Job Step > Properties, only see two tabs: Package and Configuration I am trying to set a job step that executes a package to use the 32-bit runtime, as it uses the SQL 10 driver against a SQL 2000 database. The job is running on SQL Server 2012. I see all kinds of examples of how to do this, where the job step properties page has 9 tabs (Set Values, Verification, Command Line, General, Configurations, Command Files, Data Sources, Execution Options and Logging). Execution Options has a checkbox for 32-bit mode.

When I look at my job step, logged in to the server as an Admin, running SSMS as administrator, all I see are two tabs: Package and Configuration. I deployed the packages using the Project Deployment method. How do I get those other tabs to show up?

It's on the Configuration which then has 3 subtabs: Parameters, Connection Managers and Advanced

Advanced allows you to specify 32 bit vs 64 bit runtime, change the logging levels, dump on errors and override package properties.


How can I delete a file from the primary filegroup? In dealing with fragmented disk space, I added a second data file to one of my databases.  Most of the data is now in the the second file, and I'd like to drop the first to simplify things.  However, it's the primary data file:

Msg 5020, Level 16, State 1, Line 3
The primary data or log file cannot be removed from a database.

So how do I mark the second file as primary, so I can delete the first file?

In Googling this problem, I see lots of people trying to remove filegroups, but I'm just trying to drop one file from a group.

Same situation, but without the problem regarding "primary data file": https://stackoverflow.com/questions/2183742/remove-merge-sql-servers-database-secondary-data-file.

Similar, but for log files which have different issues: https://stackoverflow.com/questions/10839374/sql-server-database-has-2-log-files-and-i-want-to-remove-one-how.  I am interested in removing a data file.

A similar situation, but there the problem is in emptying the file: http://www.sqlservercentral.com/Forums/Topic464326-5-1.aspx.

The primary database file can never be removed because it contains special page structures that exist only on that file and cannot be relocated.  If you want a single database file, move all data from the secondary file using DBCC SHRINKFILE with the EMPTYFILE option and then remove the secondary file.  Finally, rebuild/reorganize indexes to reduce fragmentation.

An example of unmovable page is the database boot page, which is page 9 of the primary data file of every database.  See Paul Randal's Anatomy of a Page for details.


Grant View Server State - is it possible for a none SA user to have in Azure SQL? I'm trying to set up some scheduled jobs which will collect some server stats, using system DMV's in SQL Server Azure.
If a none SA users uses 'sys.dm_os_schedulers', they get an error (below), query works fine for server admin.

VIEW SERVER STATE permission was denied on object 'server', database 'master'

The user is a  member of DBManager Role in the MasterDB, and they are a member of the db_owner role in User DB, and have added VIEW DATABASE STATE in user db.

I've attempted to 'GRANT VIEW SERVER STATE to [user]' in Master DB, using the server admin account (set up via portal on initial database creation). However, this errors :-

Grantor does not have GRANT permission.

Is it possible to grant this? (which is what every post I've seen says), or how do I do it with the Server Admin account?

GRANT VIEW SERVER STATE is not supported in Azure SQL Database.

GRANT Server Permissions (Transact-SQL)

In order to use sys.dm_os_schedulers you need:

  On SQL Database Premium Tiers, requires the VIEW DATABASE STATE
  permission in the database. On SQL Database Standard and Basic Tiers,
  requires the Server admin or an Azure Active Directory admin account.

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-schedulers-transact-sql

While testing this I also could not grant VIEW DATABASE STATE to a user in master database using SA account.

I grant VIEW DATABASE STATE to the user database using SA account.  Then run SELECT * FROM sys.dm_os_schedulers in the context of user database. I was able to get result set.


Oracle program "lsnrctl" is missing after the installation of 32bit Oracle client I cleaned up my computer for all old Oracle stuff using Uninstall (OUI) and then later used regedit to  delete all references to Oracle and then deleted all remains of Oracle directories.

I have HP ProBook with Windows 7 (64-bit) and I want to install a 32 bit Oracle client on it. I need to be  connected to an Oracle 11gR2 database on a remote server by OCI, ODBC and OLEDB.

I have done this installation tens of times before, but this time I am stuck.

The command lsnrctl isn't present after the installation, and as expected then, the lsnrctl.exe binary is not present under the Oracle home directory (in BIN) as it should be.

This test shows the frightening situation:

C:\Oracle\product\11.2.0\client_1\BIN>lsnrctl status
'lsnrctl' is not recognized as an internal or external command,
operable program or batch file.

I did the same operations and installation on more time, but with the same result.
Why does this happen and what can I do to straight it out, other than re-installing the whole Windows 7?

There doesn't appear to be a problem.

The listener is a process that generally runs on the database server, not on the client machine.  Doing a client-only install will not install a listener.  If you want to have a listener on your machine, you'd need to do a database install (though you can do a software-only install rather than actually creating a database).

If you're just trying to connect to a remote database, however, you'd use the listener that is (presumably) running on the database server.  It's theoretically possible to install a listener on one machine for a database running on another machine but that isn't particularly common.  It would be extremely, extremely unusual to install a listener on the client machine that listens for connections to a database on a remote server.


Is there a TSQL command or another way to rename AOAG (Always On Availability Group)? We have an AOAG named with "space" and I need to rename it to not have "spaces". It was created before my time. The 3rd party vendor tool does not work with AOAG that has spaces. There are about 40 databases in the AOAG (SharePoint).

The property is greyed out in SSMS. Is there another option? As this is production, I am looking for a solution with minimal downtime.

Thanks much,
Debbie

I do not believe that there is a way to rename it (safely). You will need to rebuild the AG.

If you do it during an outage window where you can eliminate or minimize data changes then you should be able to do it without having to perform new backups / restores.


Output another word instead of the word in the table in a query Suppose I have a table where I have a field which can have to values: "enabled" and "disabled".
Suppose want my query to output "unblocked" instead of "enabled" and "blocked" instead of "disabled". How to do that in Oracle?

Either use the SQL standard way of CASE WHEN ... as ypercybe suggested in the comments or for more neat result, DECODE function:

DECODE(fieldref, 'enabled', 'unblocked', 'disabled', 'blocked')


Combining three queries I am having the table structure

id |  name  |   desig    |  age
1  |   John | supervisor |   M/28
2  | Deborah | Manager   |  F/44
3  | Joan   |Area Manager|  M/52

I am trying to get the count of employees by their desig grouped by age .
Here is the query that is working

SELECT sum(case when desig='Manager' then 1 else 0 end ) AS Manager
     , sum(case when desig='Area Manager' then 1 else 0 end ) AS Amanager
     , sum(case when desig='Superwisor' then 1 else 0 end ) AS SW
FROM employee_details
WHERE substring_index(age,'/',-1) < 30

So that i can get the count of employees grouped by designation below 30. I need to get similar counts for the age group between 30-40 , 40-50 and above 50 . Should i go for four different SELECT  queries or is there a different ,more performant way to do this .

I've added a few more rows just to check it.

create table employee_details (id int, name text, desig text, age text);

insert into employee_details values
(1, 'John',    'Supervisor',   'M/28'),
(2, 'Deborah', 'Manager',      'F/44'),
(3, 'Joan',    'Area Manager', 'M/52'),
(4, 'Anna',    'Manager',      'F/35'),
(5, 'Rose',    'Area Manager', 'M/55');

Age group can be computed in this way:

(substring_index(age, '/', -1) DIV 10) * 10 as age_group

Then you can GROUP BY desig and age group:

SELECT   desig,
         (substring_index(age, '/', -1) DIV 10) * 10 as age_group,
         count(*) as count_emp
FROM     employee_details
GROUP BY (substring_index(age, '/', -1) DIV 10) * 10, desig
ORDER BY (substring_index(age, '/', -1) DIV 10) * 10, desig;

desig        | age_group | count_emp
:----------- | --------: | --------:
Supervisor   |        20 |         1
Manager      |        30 |         1
Manager      |        40 |         1
Area Manager |        50 |         2

dbfiddle here

For a custom age groups I'd suggest a tally table:

create table age_group (id int, name text, low int, high int);

insert into age_group values
(1, '< 25',   0,  25),
(2, '25-35', 25,  35),
(3, '35-40', 35,  40),
(4, '40-50', 40,  50),
(5, '>= 50', 50, 150);

Then you can CROSS JOIN this table with all possible designations and use an scalar subquery to get count of employees.

SELECT ed.desig,
       ag.name,
       (SELECT COUNT(*)
        FROM   employee_details
        WHERE  substring_index(age, '/', -1) >= ag.low
        AND    substring_index(age, '/', -1) < ag.high
        AND    desig = ed.desig) count_emp
FROM   age_group ag
CROSS JOIN (SELECT DISTINCT desig
            FROM   employee_details) ed;

desig        | name  | count_emp
:----------- | :---- | --------:
Supervisor   | < 25  |         0
Supervisor   | 25-35 |         1
Supervisor   | 35-40 |         0
Supervisor   | 40-50 |         0
Supervisor   | >= 50 |         0
Manager      | < 25  |         0
Manager      | 25-35 |         0
Manager      | 35-40 |         1
Manager      | 40-50 |         1
Manager      | >= 50 |         0
Area Manager | < 25  |         0
Area Manager | 25-35 |         0
Area Manager | 35-40 |         0
Area Manager | 40-50 |         0
Area Manager | >= 50 |         2

dbfiddle here


Does Sql Server maintain the free space defined by fillfactor? I'm new to concept of what fillfactor is and why it is used.
So far, reading this article, I've learned that by setting the fillfactor configuration of an index, we specify how much space on each page should remain free, so that whenever a new record needs to be added to a page, there will be some free space for it.
My question is that:

Is that free space on each page, intended to remain free? I can guess that, that free space is used when an insert happens, but later the database engine, moves data, so that again we have some free space in each page. Is this what happens in a DBMS?

Ni, it isn't maintained. It is there to postpone page splits. But after so many inserts, the page will split. Then it is up to you whether you fell like doing anything about it (rebuild or reorganize).


Why bother with rman? I've stumped everyone with the previous question about how rman would rather consume infinite disk space than remove obsolete backups. That leads me to this question:

datapump backups work (I have actually tested restoring them), and they are easy to manage unlike rman (if you want to dispute this, answer the previous question!) so why shouldn't I just disable the rman backups and consider that the solution?

It is not the fault of RMAN if the person using it does not know how to configure the database.

With RMAN you can:

restore your database to any point in time in the past that your backups cover, not just the actual state where the backup was created
restore large databases significantly faster (have fun with impdp on multi terabyte databases - especially index building and constraint validation, or tens of thousands of partitions, where Data Pump does nothing for hours because it "processes metadata")
backup directly to tape/network/whatever through using 3rd party backup solutions (IBM TSM, Networker, Netbackup, Data Protector, etc.)
have a central inventory of backups, have automatic deletion based on it
compress backups directly without licensing Advanced Compression option (OK, it is not a true  technological advantage)

What you can not do with RMAN effectively/easily (but still possible combined with Data Pump), and where Data Pump shines:

restore a specific object (table, package, etc.) or a subset of objects


LOCK=NONE when adding NOT NULL constraint fails with "cannot silently convert NULL values" error despite no NULL values being present I'm trying to use MySQL's online DDL feature to add a NOT NULL constraint to an InnoDB table without blocking writes to the table. According to manual, this should be possible - the linked table summarising MySQL 5.6's online DDL capabilities has a 'YES' in the Allows concurrent DML column of the Make column NOT NULL row.

However, when I actually try this, it fails under absolutely all circumstances I've tried. Even if the column I'm trying to add the NOT NULL constraint to does not contain any NULL values, I get the following strange error:

  ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try LOCK=SHARED.

Here's an ultra-simple test case at the MySQL shell:

mysql> CREATE TABLE `Test` (
    ->   `id` int(10) auto_increment PRIMARY KEY,
    ->   `stuff` varchar(100) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)
mysql> ALTER TABLE Test MODIFY stuff varchar(100) NOT NULL, LOCK=none;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try LOCK=SHARED.

How (if at all) can I get around this and add a NOT NULL constraint while permitting concurrent DML?

In case it's relevant: my exact MySQL version is 5.6.16 and my SQL_MODE is NO_ENGINE_SUBSTITUTION.

This seems to be a bug that was introduced in MySQL 5.6.10 and has since been fixed (although there is no bug report on http://bugs.mysql.com that I can find). The behaviour described in the question does not occur in the latest version of MySQL.

Observed behaviour in MySQL 5.7.5-m15:

mysql> CREATE TABLE `Test` (
    ->   `id` int(10) auto_increment PRIMARY KEY,
    ->   `stuff` varchar(100) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.38 sec)

mysql> ALTER TABLE Test MODIFY stuff varchar(100) NOT NULL, LOCK=none;
Query OK, 0 rows affected (0.97 sec)
Records: 0  Duplicates: 0  Warnings: 0


Cannot Login to Fresh Installation of SQL Server 2012 I just deployed a fresh installation of SQL Server 2012 on a Windows Server 2012 Datacenter R2 environment. The installation completed with 0 warnings and 0 failures.

During the installation I specified Integrated Windows Authentication so it did not prompt me for a local SA password. I added the domain security group that should be granted administrative rights as well.

Immediately after the installer completed, I launched SMSS and am greeted by this error when attempting to connect.

After clicking okay and then pressing "Connect" a second time I get this dialog.

The TechNet article relating to this issue says to ensure that Named Pipes is behind TCP/IP in the protocol order, which it is.

Any help would be greatly appreciated.

UPDATE
I tried switching to SQL Authentication mode by launching in Single User Mode, enabling the 'sa' account and setting the password but the errors remain the same.

Yup, that last little comment was it. Even though you can select a domain group for the Instance administrators, it will create this problem. You must add individual user accounts. Thanks for the help and pointers!

In the end I put it into single user mode and ran the following command from an elevated prompt:

sqlcmd -S localhost from the command prompt

EXEC sp_addsrvrolemember
'domain\username', 'sysadmin'; GO

exit

Restarted the SQL services normally and went right in.


PostgreSQL - partial dump of large objects (pg_largeobject table) Context : Our web-app is like a collaboration platform where you can share files, appointments, survey and so on. An instance of this web-app is divided in communities, each one is independent to each other but share the same PostgreSQL database.

Target : actually what i'm trying to do is to write a shell script in order to make regular backups of communities separately. This means that I need to select  only part of the data inside the various Postgre tables and export/dump the community data.

Obstacle :
My big problem is with the pg_largeobjects table, used to store the BLOB of files. This table use OIDs as pk (something I don't relly like by the way). The table files (containing files metadata) , has a reference to the pg_largeobject OID.

Solutions considered :

Select all the OIDs from files table WHERE community_id = something.
And afterwards, I could export the data stream from pg_largeobjects using [lo_export][1] function giving all the OIDs selected in the previous operation:

  int lo_export(PGconn *conn, Oid lobjId, const char *filename);

  The problem with this approach is when i will try to restore pg_largeobject records, I need to keep the same OIDs or the relation will be broken. I can use the function lo_import_with_oid that permit to define the desired OID, but in most cases OIDs will be already occupied from the original data, and for this reason the import will generate a new OID, that will not satisfact the relation with the rest of the database.

I still don't know how I could achieve this, but i try to explain:

during the export I could select and save all the PK of the files table record of the certain community (let's call it filesPKs).
I will then perform a [lo_export] of pg_largeobjects
and during the lo_import I could ignore the fact that new OID are being generated. But in someway i would save the list of these new OIDs inserted.
as final step I could update all the rows(using filesPKs reference) in the files table in order to make them point to the new imported pg_largeobject records.

This second procedure in my opinion is very difficult, because I still don't see how could I update files rows in order to point to the correct largeobject.

An other option, that fancifully would be the best one, would be to modify the pg_largeobject table in order to have an additional column with a (static) reference to files table. But after a test it seems to be impossible:

  testdb=# alter table pg_largeobject add column prova text;  ERROR:
  permission denied: "pg_largeobject" is a system catalog

  Questions :

Any one else faced this issue?
Am I just missing something, or taking the wrong approach?
Out there exist an other way to export/dump pg_largeobjects records, exporting all the fields, not only the data stream?

I managed to resolve this issue as follow (2nd considered solution - revisited):

All in a while cicle, for every file:

select file_id , file_version, oid and put them in 3 variables
perform a [lo_export] of pg_largeobjects that require oid a path+filename to be defined. The filename will be the concatenation of the file_id and file_version.
during lo_import new OID will be generated. But using the filename defined above, I can perform the last stem:
update all the rows in the files table in order to make them point to the new imported pg_largeobject records and mantain the relation consistecy


Checkpoint during Backup Can database process make automatic checkpoint when I'm in process of copying files during

ALTER DATABASE BEGIN BACKUP;

and

ALTER DATABASE END BACKUP;

You can assume that process of copying files will last for very long time.

It somewhat begs the question of why not use rman for your backups instead of user managed backups.  rman has been the robust solution since at least 9i, and here we are with 12c being mature.


SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logs After doing some research I cannot seem to find an answer to this question.

Background
I am attempting to setup a backup plan that fits the following three requirements:

Reliability of backups, having nightly full backups
Transaction log backups that could be restored from
Low amount of disc space used
The backups must be accessible locally for an auditing tool

So to fit those needs I am thinking full backups weekly, differential daily, and transaction hourly. Then each night a copy_only backup would run that can be shipped offsite, this backup is done so that the log chain is not broken, and we have reliable nightly full backups offsite, without having to eat up so much local disc space.

Question
Is it possible to restore from the copy_only backup, and restore the transaction logs after.

Let me just give an example so you know what I am talking about.

Using the below list I am wondering if it is possible to restore FullbackupCOPY_ONLYC.bak followed by TransactionbackupG.trn, TransactionbackupH.trn, finally TransactionbackupI.trn

> ---List of Backups---
FullbackupA.bak 01/01/2013 00:00:00
>  DifferntialbackupA.bak 02/01/2013 00:00:00
FullbackupCOPY_ONLYA.bak 02/01/2013 00:00:00
>     TransactionbackupA.trn 02/01/2013 01:00:00
>     TransactionbackupB.trn 02/01/2013 02:00:00
>     TransactionbackupC.trn 02/01/2013 03:00:00
>  DifferntialbackupB.bak 03/01/2013 00:00:00
FullbackupCOPY_ONLYB.bak 03/01/2013 00:00:00
>     TransactionbackupD.trn 03/01/2013 01:00:00
>     TransactionbackupE.trn 03/01/2013 02:00:00
>     TransactionbackupF.trn 03/01/2013 03:00:00
>  DifferntialbackupC.bak 04/01/2013 00:00:00
FullbackupCOPY_ONLYC.bak 04/01/2013 00:00:00
>     TransactionbackupG.trn 04/01/2013 01:00:00
>     TransactionbackupH.trn 04/01/2013 02:00:00
>     TransactionbackupI.trn 04/01/2013 03:00:00

Maybe this whole setup is not reasonable I am fairly new to SQL Server and am trying to learn as I go. Any advice/help would be appreciated.

A full backup in SQL Server 2008 does not break the log chain. It only resets the differential base-lsn.
You also can restore log backups after restoring from a copy only. The following script demos that:
CREATE DATABASE BakTst13;
GO
ALTER DATABASE BakTst13 SET RECOVERY FULL;
GO
USE BakTst13;
GO
CREATE TABLE dbo.tst(id INT IDENTITY(1,1));
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP LOG BakTst13
TO DISK = 'BakTst13_Log_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_2' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_C' WITH COPY_ONLY,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP LOG BakTst13
TO DISK = 'BakTst13_Log_2' WITH INIT,FORMAT;
GO
USE tempdb;
GO
DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_1' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_1' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_2' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_C' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_2' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;

It creates a database and a table and inserts 50 rows into that table. In between those inserts several backups are taken in this order:

Full
Log
Full
Full Copy_Only
Log

Next the database is dropped and restored like this:

1st Full
1st Log
2nd Log

The following SELECT demonstrates that the restore was successful.
This shows that neither a COP_ONLY nor a normal Full Backup break the log chain.
Then the database is dropped again and restored like this:

Copy_Only Full
2nd Log

Afterwards the SELECT demonstrates success again.
This demonstrates that you can use a COPY_ONLY full backup as the base of your Log Restore.
Differential tests
I created a DIFFERENTIAL version too:
CREATE DATABASE BakTst13;
GO
ALTER DATABASE BakTst13 SET RECOVERY FULL;
GO
USE BakTst13;
GO
CREATE TABLE dbo.tst(id INT IDENTITY(1,1));
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Diff_1' WITH DIFFERENTIAL,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_2' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Diff_2' WITH DIFFERENTIAL,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_C' WITH COPY_ONLY,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Diff_3' WITH DIFFERENTIAL,INIT,FORMAT;
GO
USE tempdb;
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore F1, D1, D2',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_1' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_1' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_2' WITH NORECOVERY;--<--Fails!
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore FC, D3',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_C' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_3' WITH NORECOVERY;--<--Fails!
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore F2, D2, D3',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_2' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_2' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_3' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;

This takes backups in this order:

1st Full
1st Differential
2nd Full
2nd Differential
Copy_Only Diff
3rd Differential

It then tries this restore route:

1st Full
1st Differential
2nd Differential

Step 3 fails with this error:
Msg 3136, Level 16, State 1, Line 4
This differential backup cannot be restored because the database has not been restored to the correct earlier state.

This shows that a normal full backup breaks the differential chain.
Next the database is dropped and this restore flow is attempted:

Copy_Only Full
3rd Differential

Step 2 fails with the same error as step 3 above. This shows that a copy only backup cannot be used as the base for a differential restore.
Then the database is dropped again and the following restore is executed:

2nd Full
2nd Differential
3rd Differential

The following select proves that this restore succeeded. This shows that a COPY_ONLY Full backup does not interrupt the differential chain.


Documenting SSRS and SSIS jobs that are the Job Activity Monitor We are running a SQL Server 2014 database using SSRS as a reporting tool.  We have data coming in via SSIS jobs and linked servers.  We have developers creating various SSRS jobs and scheduling them.

I am wondering if anyone has any recommendations on how to document all the jobs in the Job Activity monitor.  My goal is to identify the SSIS jobs as well as the SSRS scheduled reports.  I'd like to know what tables the SSRS jobs hit so I can make sure they are scheduled after any tables they hit are updated.  Does anyone have a tool or a way they have created this documentation?  I have become the accidental DBA in my group so any guidance would be appreciated.

Thanks!

This simple query on MSDB should get you started:

SELECT s2.subsystem AS [TypeOfJobStep]-- when subsystem = 'SSIS' then 'SSIS job step'
     , s2.command AS [JobStepCommand]
      , s.name AS [JobName]
FROM dbo.sysjobs AS s
JOIN dbo.sysjobsteps AS s2 ON s2.job_id = s.job_id

You can identify SSRS subscriptions because their name will be alternating alphanumeric strings with dashes. I think a SUBSTRING function could determine that.

SSIS job steps should show subsystem = 'SSIS', depending on how you execute your SSIS jobs.

As for identifying which tables your SSRS jobs hit, that will require you to document your SSRS reports. SSRS reports can access data through a stored procedure, or through an embedded query within the report. Once you determine which they are doing, you'll need to determine a way to link them to the jobs you previously queried.

This is the hard part, because SSRS reports can use multiple data sources and those data sources don't have to be on the same SQL server as your scheduled jobs. I don't know if this part is feasible.


What variable(s) to look to maintain MySQL speed of scanning through all database tables while it starts? We have few thousand small databases with total .ibd files of about 800000.

We noticed that MySQL takes couple of minutes to stop but about two hours to start.

For e.g. while MySQL is starting and it's process ID is 26980 and if we look at following command we can see MySQL scanning through all database tables alphabetically.

watch -n 1  'ls -l /proc/26980/fd | grep .ibd'

In first few minutes speed of mysql is about 2 databases per second but after about an hours speed gradually drops to 1 database per 10 seconds, because it keeps getting slower, maybe there is some kind of cache that has exhausted.

My questions is, What variable(s) to look for to maintain MySQL speed of scanning through all database tables while it starts?

We tried playing with table_open_cache, table_definition_cache, tmp_table_size but it does not seems to affect this speed.

Update 1

We copied all data files to an testing server and installed Percona Server 56-5.6.15 in place of Percona Cluster. Server start time is normal, it is starting in few minutes whereas all nodes of our Cluster takes about 2 hours to start.

We tried starting cluster in an isolated mode where it does not connect with other nodes in cluster. Startup time is still 2 hours.

Update 2

We installed Percona Cluster on a testing server with same data. It worked normally and server started in few minutes even though there were same number of databases. Testing server is a small VPS whereas main server is huge. We copied my.cnf from main server to VPS to ensure all variables same.

After Update 2, we have no idea why all our nodes are behaving like this.

SPEEDING UP SHUTDOWN / STARTUP
Since you are dealing with the InnoDB Storage Engine, you need to think about what MySQL does during a startup and during a shutdown.
During a shutdown, InnoDB tends to freeze everything in terms of transactions Dirty pages in the InnoDB Buffer Pool are flushed. During a startup, InnoDB crash recovery rolls forward any leftover changes in the transaction logs. The InnoDB Double Write Buffer may also be scanned during InnoDB crash recovery.
The first variable I think you should set innodb_fast_shutdown to 0.
Please note the MySQL Documentation:

The InnoDB shutdown mode. If the value is 0, InnoDB does a slow
shutdown, a full purge and an insert buffer merge before shutting
down. If the value is 1 (the default), InnoDB skips these operations
at shutdown, a process known as a fast shutdown. If the value is 2,
InnoDB flushes its logs and shuts down cold, as if MySQL had crashed;
no committed transactions are lost, but the crash recovery operation
makes the next startup take longer.
The slow shutdown can take minutes, or even hours in extreme cases
where substantial amounts of data are still buffered. Use the slow
shutdown technique before upgrading or downgrading between MySQL major
releases, so that all data files are fully prepared in case the
upgrade process updates the file format.
Use innodb_fast_shutdown=2 in emergency or troubleshooting situations,
to get the absolute fastest shutdown if data is at risk of corruption.

OK that takes care of a faster startup. The tradeoff is longer shutdown.
Is there anything you can do speed up a shutdown ? Yes !!!
Get dirty pages from the InnoDB Buffer Pool written to disk more frequently. How ?
You need to set innodb_max_dirty_pages_pct = 0. By doing this, the shutdown process takes less time to flush dirty pages to the .ibd files.
Is there anything else to help startup ? Yes, there is one more.
When you startup mysql, usually the InnoDB Buffer Pool is empty after its allocation.
You could make mysql save the block numbers of all 16KB pages that were in the the buffer pool. Just add innodb_buffer_pool_load_at_startup and innodb_buffer_pool_dump_at_shutdown. This will not time a long time since it writes block numbers to a binary files, not the blocks themselves.
LARGER NUMBER OF FILES
InnoDB's default for open files (innodb_open_files) is 300 for MySQL 5.5. For MySQL 5.6, the ideal number is set for you. You may need to increase this value as well. InnoDB may cache the open files once you reach this limit.
You may have to compensate by raising the value to 100000 or 200000. You could also increase the ULIMIT in the OS to allow MySQL to have more file handles and set open_files_limit to that max value.
I would further suggest increasing RAM in the DB Server.
SUMMARY
Here are the options you need to add
[mysqld]
innodb_fast_shutdown=0
innodb_max_dirty_pages_pct=0
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_dump_at_shutdown=1
innodb_open_files=(much higher value)
open_files_limit=(much higher value)

Depending on the environment, you may not need all four options. Perhaps setting innodb_fast_shutdown to 2 is better than 0. Maybe the default setting for innodb_max_dirty_pages_pct is fine if you have low-write activity. In any case, test which options help your shutdown and startup concerns.
Keep in mind that innodb_buffer_pool_load_at_startup and innodb_buffer_pool_dump_at_shutdown are for MySQL 5.6. Therefore, check to see if the version of Percona Server you are using has those two options.
Give it a Try !!!


Setting DataGuardSyncLatency for Oracle Data Guard With Oracle 12.2 there is a new parameter DataGuardSyncLatency when setting up Data Guard. I keep getting the warning:

Warning: ORA-16714: the value of property DataGuardSyncLatency is inconsistent with the member setting

What value should this parameter be for protection mode: MaxPerformance with phsyical standby?

On the primary and standby server I set:

ALTER SYSTEM SET DATA_GUARD_SYNC_LATENCY=0 scope=both sid='*';

Not sure if it should be 0.


How to install keyring_aws plugin in MySQL? I am looking for a way to install keyring_aws plugin in mysql community edition.
Is it possible?

I gone through couple of docs,

  https://dev.mysql.com/doc/refman/5.7/en/keyring-aws-plugin.html
  https://dev.mysql.com/doc/refman/5.7/en/keyring-installation.html

And also tried to findout keyring_aws.so file, but couldn't find it anywhere.

I checked in mysql dir also.

root@server:~# ls -l /usr/lib/mysql/plugin/
total 644
-rw-r--r-- 1 root root  21224 Jan 22 17:26 adt_null.so
-rw-r--r-- 1 root root   6288 Jan 22 17:26 auth_socket.so
-rw-r--r-- 1 root root  44144 Jan 22 17:26 connection_control.so
-rw-r--r-- 1 root root 108696 Jan 22 17:26 innodb_engine.so
-rw-r--r-- 1 root root  88608 Jan 22 17:26 keyring_file.so
-rw-r--r-- 1 root root 154592 Jan 22 17:26 libmemcached.so
-rw-r--r-- 1 root root   9848 Jan 22 17:26 locking_service.so
-rw-r--r-- 1 root root  10840 Jan 22 17:26 mypluglib.so
-rw-r--r-- 1 root root   6288 Jan 22 17:26 mysql_no_login.so
-rw-r--r-- 1 root root  56064 Jan 22 17:26 rewriter.so
-rw-r--r-- 1 root root  56936 Jan 22 17:26 semisync_master.so
-rw-r--r-- 1 root root  14768 Jan 22 17:26 semisync_slave.so
-rw-r--r-- 1 root root  27568 Jan 22 17:26 validate_password.so
-rw-r--r-- 1 root root  31296 Jan 22 17:26 version_token.so

Can we build this .so file from scratch? If yes, where can I find the source?

Unfortunately, the keyring_aws plugin is only available on the Enterprise edition (not available on the community edition).

For community edition, the only encryption method available for tablespace encryption is the standard keyring (keyring_file.so)

To load the plugin, use the --early-plugin-load option to name the plugin library file that contains it. For example, on platforms where the plugin library file suffix is .so, use these lines in the server my.cnf file (adjust the .so suffix for your platform as necessary):

[mysqld]
early-plugin-load=keyring_file.so

If you need the AWS plugin and can't pay for enterprise edition your best option could be MariaDB:
https://mariadb.com/kb/en/library/aws-key-management-encryption-plugin/


Capturing Column Names in Dynamic SQL Statement I'm in the process of updating some procs made for SQL 2008R2 to 2016 that are using a dynamic result set.

Assuming there is a procedure that always has exactly one result set, but the result set has a dynamic column return each time, is there a way to capture the column names at runtime in tsql without doing something janky?
I'm not worried about data types, just column names.

Thanks!

There's no TSQL api to examine the resultset shape of a procedure that returns multiple different resultset shapes.  For normal procs you can use sp_describe_first_result_set.

If you can afford to actually run the procedure and examine the results, the least-janky solution is to use SQL CLR.  In CLR you get a SqlDataReader that you can use to examine the resultset shape.


MySQL Dump/Import progress on Windows I'm going to dump a considerable big datatabase (7gb) from a MySQL 5.1 instance running on Windows into a .sql script and import it into a MySQL 5.5 instance, also on Windows (another machine). I'd like to be able to monitor the process of both operations, since I believe they are going to take some time to execute.

Apparentely, this feature has not yet made into the target MySQL versions (http://stackoverflow.com/questions/8806435/how-to-implement-a-mysql-dump-restore-progress-bar). I also checked the solution suggested here, but it seems a lot of work (and also inserting more variables to this sensitive operation) to do something that should be simple.

By the time, ins't there any simple solution to monitor, somehow, the progress of these operations?

Restoration will be

Per database (alphabetical order)
Per table within each database (alphabetical order)

If you want to see the progress, you can run the following every few minutes:

SHOW DATABASES;

To be sure that restore is not blocked, use SHOW PROCESSLIST. It will show you the restore transactions (create table, insert into...).

You can also followed your disk space usage but this is'nt a really good metric...

Max.


Amount of Data transferred out in mongorestore I'm using MongoDB version 4

Consider I have a database with dataSize 15GB, storageSize 10GB.
After I mongodump --gzip it, the file created is of 5GB.

Now, If I'm restoring it to a remote machine (M2), using mongorestore on my workstation (M1), will the data transferred out of M1 be around 5GB or 10GB or 15GB?

MongoDB 3.4 and newer servers can negotiate network compression if MongoDB clients/drivers have also been updated to support the compressed message format. Network compression was introduced as an optional feature in MongoDB 3.4 and is enabled by default for MongoDB 3.6+ servers.

Unfortunately the mgo driver used by mongodump (as at MongoDB 4.0) does not support network compression and is no longer actively maintained. The expected outcome is that mongorestore or mongodump data transfer will roughly match your dataSize (plus some network overhead).

An officially supported MongoDB Go driver is currently in development and will include full support for the latest MongoDB features. Once the new Go driver is ready, the server command line tools will be updated to be able to take advantage of additional options. A relevant issue to upvote/watch in the MongoDB issue tracker is TOOLS-1668 (Support wire protocol compression in mongodump and mongorestore).

In the interim there are a few possible workarounds if network compression will significantly improve your transfer time to a remote server:

Copy the gzipped mongodump file to your remote server and restore locally
Use an SSH or VPN connection which supports network compression


MSSQL database physical design: RAID or spreading filegroups across different physical drives? I'm going to build a Microsoft SQL server database which could end up being more than 40 TB in size. There are some quite large tables in the DB with more about 1010 .. 11 records. Every table has typical primary key index. Also most of the tables have composite non-clustered indexes too (B-tree). In a few cases we have defined columnstore indexes.

Which of the below physical designs of the server will result in better performance1 and db management2 overtime?

Scenario 1. create one (or more) filegroup per physical drive (we only have SSD NVMe drives), and spread the heavily used tables and indexes across different drives.

Scenario 2. Bundle all drives using RAID 10 and create a single large volume. Data will be striped on all disks by the OS. In this scenario, data file (.mdf), log file (.ldf) and tempdb are all stored on a large RAID 10 volume.

Scenario 3. Partition tables/indexes and store each partition on different physical drive. I should mention that it wold be close to impossible to distribute data uniformly across all partitions because of the type of data we are dealing with.

If you think none of the above are ideal solutions; I'd love to know what would you recommend.

Many Thanks.

[1] by performance I mean lower retrieval time and higher IOPs, for a given indexing architecture

[2] by db management I mean add/remove/update data, keeping the performance, redundancy, availability ..

I believe all 3 options are very much correlated and can be implemented together:
Depending upon the table structure and such a size of database and tables you can have them on individual file groups especially if they are partioned.

Also it's always good to have them on good storage. We have in our scenario most of the data and log files of user dbs and temp dbs on SSD's

There are lot of factors if you are considering performance. I believe you should be on sql server 2016 + to make use of all new improved features like CE's.

Also consider other various factors like fibre cables that SAN is being used followed by good network bandwidth. All I can say per my experience is test the system or hardware with all sorts of load testing and see how well it suites your requirements.


Why is part of transaction committed when stored procedure faults? I have a stored procedure that begins by declaring a few variables then contains begin tran; After this it performs some validations on the supplied arguments (and increments an error count each time a supplied argument fails validation). If there is no error count it then proceeds to carry out 7 inserts. Following this, it has commit tran;
Recently I added an 8th insert to the list. An implicit type conversion meant that some inserted data would be truncated if inserted. This threw an error to the SSMS screen but I found the first 7 inserts had committed while the 8th obviously didn't complete.
I appreciate that I could include a try ... catch block to handle errors but if an explicit begin tran; doesn't make the whole block of work autonomous down to the commit, then what is the point? What have I missed?
I get that I perhaps could have wrapped my procedure call in a transaction at that level - but can someone please explain what is going on and why the begin tran appears disrespected when included within the procedure body? If calling the procedure begins an implicit transaction, then shouldn't a faulting step in the proc roll back all changes being effected by the proc - even without explicitly including begin tran in the proc body?

Transactions don't automatically roll back on error--that's not what they are designed to do. They are designed to give you the ability to rollback. However, you still need to do something to make that happen.
As you mention, you can make that happen through TRY...CATCH, which gives you the most control over if and how you can rollback.
It sounds like you are expecting the behavior of SET XACT_ABORT ON, which you can set in your stored procedure, but is not the default behavior. The description of setting XACT_ABORT on vs off from the docs is:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger.

When SET XACT_ABORT is ON, calling a faulting procedure returns messages in SSMS like:
Msg 8152, Level 16, State 14, Procedure spProcName, Line 298 [Batch Start Line 5]
String or binary data would be truncated.

Completion time: 2021-04-26T10:51:00.8420902+10:00

The first line is in red in SSMS.
When SET XACT_ABORT is OFF, calling a faulting procedure includes one additional message:
Msg 8152, Level 16, State 14, Procedure spProcName, Line 298 [Batch Start Line 5]
String or binary data would be truncated.
The statement has been terminated.

Completion time: 2021-04-26T10:53:52.5951780+10:00

In particular, when you see the following message, it means statements before the faulting statement within the procedure have been committed:
The statement has been terminated.


pg replication connection not returns wal events to java code client , it hangs some times Describe the issue
We are facing issue of pg replication slot not listen database wal events.
Once pg database restarted and we attach

Driver Version: 42.2.6
Java Version: JDK1.8
OS Version: Window 10
PostgreSQL Version: 9.6

Steps to reproduce the behaviour:

Code using the sample from the JDBC driver's documentation

Created replication connection logical with test decode

String url = "jdbc:postgresql://dbhist8:port/" + db;
Properties props = new Properties();
PGProperty.USER.set(props, "postgres");
PGProperty.PASSWORD.set(props, "postgres");
PGProperty.ASSUME_MIN_SERVER_VERSION.set(props, "10.5");
PGProperty.REPLICATION.set(props, "database");
PGProperty.PREFER_QUERY_MODE.set(props, "simple");
Connection slotVerifyConnection = null;
Connection slotConnection = null;
try {
//TODO connection pool
slotVerifyConnection = connect(url, "postgres", "postgres");
slotConnection = DriverManager.getConnection(url, props);
Statement statement = slotVerifyConnection.createStatement();
ResultSet result = statement.executeQuery("select restart_lsn,active,active_pid from pg_replication_slots where slot_type = 'logical' and slot_name ='" + slotName + "'");
while (result.next()) {

            lastLSNNumber = LogSequenceNumber.valueOf(result.getString(1));

        }

        Connection con = DriverManager.getConnection(url, props);
        PGConnection replConnection = con.unwrap(PGConnection.class);

        PGReplicationStream stream = null;

        if (lastLSNNumber != null) {
            stream =
                    replConnection.getReplicationAPI()
                            .replicationStream()
                            .logical()
                            .withSlotName(slotName)
                            //.withSlotOption("include-xids", true)
                             //.withSlotOption("skip-empty-xacts", true)
                            .withStartPosition(lastLSNNumber)

                            // .withStatusInterval(20, TimeUnit.SECONDS)
                            .withStatusInterval(Integer.parseInt("0"), TimeUnit.SECONDS).start();
        } else {

            **ReplicationSlotInfo info=       replConnection.getReplicationAPI()
                    .createReplicationSlot()
                    .logical()
                    .withSlotName(slotName)
                    .withOutputPlugin(wal2json)
                    .make();**
            info.getConsistentPoint();
            stream =
                    replConnection.getReplicationAPI()
                            .replicationStream()
                            .logical()
                            .withSlotName(slotName)
                        //    .withSlotOption("include-xids", false)
                       //     .withSlotOption("skip-empty-xacts", true)
                            .withStatusInterval(0, TimeUnit.SECONDS)
                            .start();
        }
        while (true) {
            //non blocking receive message
            ByteBuffer msg = stream.read();

            if (msg == null) {
                TimeUnit.MILLISECONDS.sleep(10L);
                continue;
            }

            int offset = msg.arrayOffset();
            byte[] source = msg.array();
            int length = source.length - offset;
            System.out.println(new String(source, offset, length));
            String sql=new String(source, offset, length);
            //QueryUtils.getQueries(sql);
            if(stream.getLastReceiveLSN()!=null) {
                stream.setAppliedLSN(stream.getLastReceiveLSN());
                stream.setFlushedLSN(stream.getLastReceiveLSN());
                stream.forceUpdateStatus();
            }else{
                System.out.println("NULL value");
            }
        }

    } catch (Exception ex) {
ex.printStackTrace();
}
}

I have fixed issue using SQL slot creation sql command.
Removed below code which blocks executions
     ReplicationSlotInfo info=       replConnection.getReplicationAPI()
                    .createReplicationSlot()
                    .logical()
                    .withSlotName(slotName)
                    .withOutputPlugin(wal2json)
                    .make();

And my issue was fixed.
Let me know if anyone face same issue I can help.