Statistical probability of an incomplete transaction committed by Postgres tldr

After spending time with bad databases which I will not mention, I came to question even the point of a database.

I knew that they could serve a purpose to keep permanent data organized, but that was it.

After delving into Postgres with the great help of especially Craig Ringer & Erwin Brandstetter, I've now come to see what a database could and should be:

Convenient syntax
Customizable storage methods for performance relative to persistence
Organized writes and even reads through serialization
Confidence that writes should never be incomplete

...and so on.

The point

The crash course provided has made me just enough knowledgeable on 1) - 3) to be dangerous, but I know no details about 4).

It seems like well-written database software like Postgres are designed to ensure that a transaction will never commit incomplete data.

Speaking out of ignorance, nothing is perfect, so in that vein, how statistically likely is it that Postgres will incompletely commit a transaction and not be able to detect the malfunction?

Speaking out of ignorance, nothing is perfect, so in that vein, how statistically likely is it that Postgres will incompletely commit a transaction and not be able to detect the malfunction?

Barring undetected PostgreSQL or operating system kernel / C library bugs, and in the presence of good quality hardware with ECC memory, nil.

PostgreSQL's write-ahead logging, where it:

Records changes only to to write-ahead log
fsync()s the WAL to ensure it's on disk and persistent
and only then write the changes to the main tables

is extremely robust.

You do need to ensure that your operating system and hardware honours fsync requests properly. The pg_test_fsync program is useful for this, though the results require interpretation. Also, beware of cheap SSDs without proper power-failure protection.

The main risks to data integrity are:

The system administrator
Lack of backups and replication
Operating system and PostgreSQL bugs;
Cheap poor quality memory;
Cheap SSDs without power failure protection;
Faulty/overheating hardware, though on modern x86 you'll usually get MCEs warning you about issues

Anyone who gives you "statistics" or numbers on things like the probability of a future bug causing issues is likely full of it.

There's been lots of work done on transient memory errors; the risk is real but the probability is low. I've never seen a bit-flip error in the wild, but I have colleagues who have. Google has lots of info.

The most important thing is proper backup and admin processes.

I wrote a bit more about this topic a while ago.

BTW, there are a few exceptions to the rule that effects aren't visible until they're committed:

Locking (obviously)
hash indexes (don't use them)
SEQUENCEs (and the SERIAL columns that use them)
... more others I haven't thought of


Filtering on two separate colums The schema

I'm using MySQL
Consider a table with columns

id uuid
type char
is_active Boolean

Here are some facts about the data in this table

type is either 'A' or 'B'
There will be a maximum of 2 records for any id, one for each type
A record with type 'A' will exist for all IDs. A record with type 'B' is optional. That is to say, there will be a minimum of one record for any id, with type 'A'

The scenario

A user can select one or both the types to apply a filter. For example, a user might select type 'A' and filter based on is_active true. Or they might select type 'A' with is_active true AND type 'B' with is_active false.

Our task is to count the number of records which satisfies the is_active condition for selected types.
Here are some scenarios better explanation

If user selects type 'A' and is_active true, count should include all IDs where (type is 'A' and is_active is true) or (type is 'B', regardless of is_active)
If user selects type 'A' with is_active true and type 'B' with is_active false, count should include all IDs where (type is 'A' and is_active is true) or (type is 'B' and is_active is false)

If user restricts is_active for type 'B', IDs without type 'B' should not be included.

If user restricts is_active for type 'A', IDs without type 'B' should be included.

The question

Can this be achieved without using stored procedures? Any help will be greatly appreciated.

As an example

SELECT a.*, b.*
-- SELECT COUNT(*)
FROM      datatable a
LEFT JOIN datatable b ON  a.id   = b.id
                      AND b.type = 'B'
WHERE a.type = 'A'
  AND (CASE @filterA
       WHEN 'A active'     THEN a.is_active
       WHEN 'A not active' THEN NOT a.is_active
       ELSE 1              -- A is any
       END)
  AND (CASE @filterB
       WHEN 'B active'     THEN b.is_active
       WHEN 'B not active' THEN NOT b.is_active
       WHEN 'B absent'     THEN b.id IS NULL
       ELSE 1              -- B is any
       END)

@filterA and @filterB are variables/literals with user filters choices inserted/transferred into the query as a parameter or literally (edit WHEN conditional values for to match).


In mySQL, I shouldn't use semicolon when runing a source command. Why? I'm learning SQL by myself. I've set up a mySQL server, and I was trying to import the sakila example database, and the textbook I'm using states that for that effect I should use source pathway; and end it with a semicolon. This is consistent with the initial warning at the top of the client MySQL command prompt.
However, I get several errors.
So, someone recommended me to run the command without the semicolon. When I run it , everything works just fine.
Why is that?

Semilcolon is use to signalize to MySQL that the command has ended.
But in your case it is interpreted as part of the file name, and there it is forbidden to use semicolons. see https://en.wikipedia.org/wiki/Filename


Prevent row from being inserted with a trigger I'm trying to prevent a row being inserted if a certain condition is met using a trigger. I'm using a user defined exception to do this, however the row is still being inserted, despite the exception being thrown. I'm not sure why this is happening, I though when an exception was raised the transaction was rolled back and nothing would be inserted. The code is below.

create or replace TRIGGER
  CHECK_OPTION
BEFORE INSERT
ON lot_options
FOR EACH ROW

DECLARE
    v_allowed_stage NUMBER;
    v_actual_completion_date DATE;
    option_change_invalid EXCEPTION;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Starting the begin');
    SELECT stage_stage INTO v_allowed_stage
    FROM "TEAM3"."Option" o
    WHERE o.option_number = :new.option_number;
    DBMS_OUTPUT.PUT_LINE(v_allowed_stage);

    DBMS_OUTPUT.PUT('Stage in a cell');

    SELECT actual_completion INTO v_actual_completion_date
    FROM progress
    WHERE stage_stage = v_allowed_stage
    AND Lot_lot_number = :new.lot_lot_number;
    DBMS_OUTPUT.PUT_LINE('completion date in storage');

    IF (v_actual_completion_date IS NOT NULL)
    --DBMS_OUTPUT.PUT('condition holds true')
    THEN RAISE option_change_invalid;
    END IF;

    EXCEPTION
    WHEN option_change_invalid THEN
    DBMS_OUTPUT.PUT_LINE('Invalid Option Change: Option change is not allowed in this stage of progress.');
END;

Remove the Exception block so you no longer catch the error.  Then the error will propagate out and prevent and prevent the insertion from occurring.


How do I use the result of a CASE statement in a WHERE clause? When I try to run this SELECT statement, I get this error: ORA-00904: "GPASCORE": invalid identifier. How do I use the result of the gpaScore CASE statement in the WHERE clause?

SELECT
  (CASE grade
   WHEN 'A+'
     THEN 4
   WHEN 'A'
     THEN 4
   WHEN 'A-'
     THEN 3.7
   WHEN 'B+'
     THEN 3.3
   WHEN 'B'
     THEN 3
   WHEN 'B-'
     THEN 2.7
   WHEN 'C+'
     THEN 2.3
   WHEN 'C'
     THEN 2
   WHEN 'C-'
     THEN 1.7
   WHEN 'D+'
     THEN 1.3
   WHEN 'D'
     THEN 1
   WHEN 'D-'
     THEN 0.7
   WHEN 'F'
     THEN 0
   ELSE NULL END)   gpaScore
FROM PGFINALGRADES
WHERE gpaScore = 3

The problem is that Oracle evaluates the SELECT after the WHERE clause.

You can either put your query in a subselect:

SELECT gpaScore
FROM   (SELECT ( CASE grade
                   WHEN 'A+' THEN 4
                   WHEN 'A' THEN 4
                   WHEN 'A-' THEN 3.7
                   WHEN 'B+' THEN 3.3
                   WHEN 'B' THEN 3
                   WHEN 'B-' THEN 2.7
                   WHEN 'C+' THEN 2.3
                   WHEN 'C' THEN 2
                   WHEN 'C-' THEN 1.7
                   WHEN 'D+' THEN 1.3
                   WHEN 'D' THEN 1
                   WHEN 'D-' THEN 0.7
                   WHEN 'F' THEN 0
                   ELSE NULL
                 END ) gpaScore
        FROM   PGFINALGRADES) b
WHERE  gpaScore = '3';

Or, you could replicate the CASE statement in the WHERE clause:

SELECT ( CASE grade
           WHEN 'A+' THEN 4
           WHEN 'A' THEN 4
           WHEN 'A-' THEN 3.7
           WHEN 'B+' THEN 3.3
           WHEN 'B' THEN 3
           WHEN 'B-' THEN 2.7
           WHEN 'C+' THEN 2.3
           WHEN 'C' THEN 2
           WHEN 'C-' THEN 1.7
           WHEN 'D+' THEN 1.3
           WHEN 'D' THEN 1
           WHEN 'D-' THEN 0.7
           WHEN 'F' THEN 0
           ELSE NULL
         END ) gpaScore
FROM   PGFINALGRADES
WHERE ( CASE grade
           WHEN 'A+' THEN 4
           WHEN 'A' THEN 4
           WHEN 'A-' THEN 3.7
           WHEN 'B+' THEN 3.3
           WHEN 'B' THEN 3
           WHEN 'B-' THEN 2.7
           WHEN 'C+' THEN 2.3
           WHEN 'C' THEN 2
           WHEN 'C-' THEN 1.7
           WHEN 'D+' THEN 1.3
           WHEN 'D' THEN 1
           WHEN 'D-' THEN 0.7
           WHEN 'F' THEN 0
           ELSE NULL
         END ) = '3';

Or, if your real case is about as simple as this, you can put grade in your WHERE clause:

SELECT ( CASE grade
           WHEN 'A+' THEN 4
           WHEN 'A' THEN 4
           WHEN 'A-' THEN 3.7
           WHEN 'B+' THEN 3.3
           WHEN 'B' THEN 3
           WHEN 'B-' THEN 2.7
           WHEN 'C+' THEN 2.3
           WHEN 'C' THEN 2
           WHEN 'C-' THEN 1.7
           WHEN 'D+' THEN 1.3
           WHEN 'D' THEN 1
           WHEN 'D-' THEN 0.7
           WHEN 'F' THEN 0
           ELSE NULL
         END ) gpaScore
FROM   PGFINALGRADES
WHERE grade = 'B'


What is PostGIS doing with ST_DistanceSphere? PostGIS ST_Distance_Sphere and ST_DistanceSphere return the same thing in PostGIS 2.2.

ST_DistanceSphere says

  Uses a spherical earth and radius derived from the spheroid defined by the SRID.

ST_Distance_Sphere says,

  Uses a spherical earth and radius of 6370986 meters

I'm looking into this answer, specifically

SELECT ST_DistanceSphere( ST_MakePoint(0,0), ST_MakePoint(0,1) );
 st_distancesphere
-------------------
   111195.07973463
(1 row)

Currently that returns 111195.07973463, it seems it should return

SELECT ((6370986 * 1 * pi()) / 180) AS distance_on_sphere
 distance_on_sphere
--------------------
   111194.682298463
(1 row)

That's a difference of 0.397436166531406, almost half a meter. Wolfram Apha shows

111194.6822984634581396675695437019275676654860169289329663

Which puts Wolfram at a more precise version of my napkin math and on par with MySQL ((6370986 * 1 * pi()) / 180) and validates that ST_DistanceSphere is borked.

What's going on here?

POSTGIS="2.2.2 r14797" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 2.2.1, released 2017/06/23" LIBXML="2.9.3" LIBJSON="0.12.1" RASTER

There are two problems here.

Though it calculates the distance on a sphere, PostGIS doesn't calculate with the major-axis radius in WGS84 as presumed in the question. The sphere is created with a radius that is the mean radius of the WGS84 ellipsoid. This is by design.
Neither does MySQL, however the radius in MySQL was lifted from a lazy-copy-job from PostGIS that converted a radius in miles to meters from an obscure constant from a random 20-year old PostgreSQL module. Far from by design, this is presumed to be a bug.


Update record column with id found in the table of an associated model Given three tables:

A users table with id's:

╔════╗
║ id ║
╠════╣
║ 1  ║
╟────╢
║ 2  ║
╟────╢
║ 3  ║
╚════╝

A purposes table with user_id's

╔════╤══════════╗
║ id │ user_id  ║
╠════╪══════════╣
║ 1  │ 2        ║
╟────┼──────────╢
║ 2  │ null     ║
╟────┼──────────╢
║ 3  │ null     ║
╚════╧══════════╝

Where the user_id can be null.

And a journeys table with user_id's and purpose_id's.

╔════╤═════════╤════════════╗
║ id │ user_id │ purpose_id ║
╠════╪═════════╪════════════╣
║ 1  │ 2       │ 3          ║
╟────┼─────────┼────────────╢
║ 2  │ 1       │ 2          ║
╟────┼─────────┼────────────╢
║ 3  │ 1       │ 1          ║
╚════╧═════════╧════════════╝

We want to create a PostgreSQL query that will find all of the purposes with null in their user_id column, select their associated journeys (by selecting them based on the id of said purposes), and then update the then empty user_id column of the purpose with the user_id found in the first associated journey.

What is the best way to do this?

I've gotten as far as being able to find all the user_id's from the journeys table who have purposes with an empty user_id column using an exists subselect query but I'm unsure how to update that null user_id column with the matching user_id found in the journeys table...

select
   user_id
from
   journeys
where exists(
    select
       id
    from
       purposes
    where
       user_id is nil
       and purposes.id = journeys.purpose_id
);

Use the FROM clause in the UPDATE. Like:

UPDATE purposes p
SET    user_id = j.user_id
FROM   journeys j
WHERE  j.purpose_id = p.id
AND    p.owner_id IS NULL
AND    j.user_id IS NOT NULL;  --  may be redundant

Ideally, you have some kind of unique index on journeys(purpose_id) to make this unambiguous. Else multiple rows might qualify.

The last predicate AND j.user_id IS NOT NULL is redundant if the column is defined NOT NULL.


Reloading MySQL my.cnf changes Do you have to restart MySQL to edit changes? Or can you edit them on the fly and restart MySQL or make the changes inside MySQL?

This server runs 100s of website that are active and I don't really want to bring mysql down for a restart unless I have to.

If you want to change some of the global configuration setting of MySQL There are two ways.

1. By Changing variable using SET GLOBAL option on running MySQL instance,this does not require server restart we can change variables like Ex : I need to set my key buffer to 128 MB

SET GLOBAL key_buffer_size = 128*1024*1024;

But using this technique we can not change or configure all variables and once we restart MySQL these GLOBAL settings are lost.

2. By Changing variables in config file (my.cnf)

This requires a server restart but changes are permanent.


The SQL server Log folder is expanding because of the SQL Dump files (what to do with it ?) The "Log" folder SIZE in the SQL server root directory (X:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log) becomes too big , ~80 GB.

When I checked it, I see that there are a lot of SQLDumpxxxx.mdmp /SQLDumpxxxx.txt files in that folder.

what to do with them ?
can I delete them and if it is good thing to do ?

10X

First of all, everything that Shanky said in his answer is 100% correct.  I would like to add:

The age of the files - If your log folder has several dumps from two years ago, then no dumps for several months, then a few recent dumps, you can safely delete the two year old dumps.
Duplicate dumps - When dumps occur there are three files created: .txt, .log & .mdmp.  Open the .txt file for several of the dumps.  If they are different, keep the dumps.  If they are the same, delete the old ones.
Your available disk space - If the disk where your dumps are accumulating is also the same disk where your database files or transaction log files also reside, and you are going to run out of disk space if you don't do something, and you have no where to move the dump files to, then by all means, delete them.
Types of dumps - If the .log file indicates "Non-yielding Scheduler",  "Non-yielding IOCP Listener" or "Non-yielding Resource Monitor", these are performance related specific to CPU.  You can troubleshoot those following the steps in this blog:  https://blogs.msdn.microsoft.com/karthick_pk/2012/08/21/non-yielding-iocp-listener-non-yielding-scheduler-and-non-yielding-resource-monitor-known-issues-and-fixes/

If the .log file indicates "Deadlocked Schedulers", this is also performance related specific to CPU.  You can troubleshoot those following the steps in this blog:  https://blogs.msdn.microsoft.com/karthick_pk/2010/06/22/how-to-analyze-deadlocked-schedulers-dumps/

Corruption Dumps - Just as the first answer states, run DBCC CHECKDB.  If the output from DBCC CHECKDB indicates corruption in your indexes, rebuild them.  If it indicates that "repair allow dataloss is the minimum" necessary to repair the database, then restore from last known good backup.
AV Dumps - You can attempt to tackle this yourself:  https://blogs.msdn.microsoft.com/sqlcat/2009/09/11/looking-deeper-into-sql-server-using-minidumps/
If you run into difficulty, open a case with Microsoft, but just as Shanky said, ensure you are on a supported version and you have the latest updates.


Union implemented with Hash Match operator I was reviewing the SQL Server physical operators listed on TechNet (don't judge, you know you've done it) and read that the Hash Match physical operator is sometimes used to implement the UNION logical operator.

I have never seen that done and would like to learn more.  An example query would be great.  When is it used and when is it better than the alternatives?  (Those are usually the same, but not always.)

I cannot recall seeing a hash match (union) operator in the wild so I can't speak authoritatively as to when they are better than the alternatives. It's possible to force one using the { CONCAT | HASH | MERGE } UNION query hint but let's try to create a real example. Quote from the documentation referenced in the question:

  For the union operator, use the first input to build the hash table (removing duplicates). Use the second input (which must have no duplicates) to probe the hash table, returning all rows that have no matches, then scan the hash table and return all entries.

So how can we create a query with a hash match (union) operator as the option with the lowest cost? Well, hash join tends to scale much better with parallelism than merge join so a query running in parallel could help tip the scales towards hash match. We need the second input to have no duplicates so a unique constraint on a table could help, but unique constraints are implemented as indexes so that will help a merge join as well. Perhaps giving the hash table lots of duplicates will favor the hash match over the concatenation option because we'll be doing a smaller effective sort?

After some trial and error, one approach that works on my machine is to insert one million rows with 10000 distinct values into one table and one million distinct values into another table. Sample code:

CREATE TABLE X_NUM_SMALL (ID INT NOT NULL);
GO
INSERT INTO X_NUM_SMALL WITH (TABLOCK)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;
GO 100

CREATE TABLE X_NUM_1000000_UQ (ID INT NOT NULL);

INSERT INTO X_NUM_1000000_UQ WITH (TABLOCK)
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

ALTER TABLE X_NUM_1000000_UQ
ADD CONSTRAINT UC_X_NUM_1000000 UNIQUE (ID);

SET STATISTICS IO, TIME ON;

The following query has a hash match (union) operator with a total estimated cost of 12.3812 units:

SELECT *
FROM X_NUM_SMALL

UNION

SELECT *
FROM X_NUM_1000000_UQ
OPTION (MAXDOP 4);

Adding a MERGE UNION hint to the query increases the cost just barely to 12.6551 optimizer units. Exchanging that hint for a CONCAT UNION hint increases the cost further to 17.2215 optimizer units.

I did a few test runs and usually the hash match (union) operator query was just barely faster than the others. Here are results from the most recent run:

╔════════════╦══════════╦══════════════╗
║ UNION TYPE ║ CPU TIME ║ ELAPSED TIME ║
╠════════════╬══════════╬══════════════╣
║ HASH       ║      657 ║         2279 ║
║ MERGE      ║      312 ║         2375 ║
║ CONCAT     ║      906 ║         2459 ║
╚════════════╩══════════╩══════════════╝


Postgresql 4 table joins I have one doubt in posgresql joins and filters. Please find below sql code snippets.

1)

select count(*)
FROM lfa1 a
LEFT JOIN lfb1 b
    ON a.lifnr = b.lifnr
    and a.hvr_is_deleted = 0
    and b.hvr_is_deleted = 0
    and a.mandt = '100'
    and b.mandt = '100'
LEFT JOIN LFM1 m1
    ON a.lifnr = m1.lifnr
    and m1.mandt = '100'
    and m1.hvr_is_deleted = 0
LEFT JOIN LFM2 m2
    ON a.lifnr = m2.lifnr
    and m1.ekorg= m2.ekorg
    and m2.mandt = '100'
    and m2.hvr_is_deleted = 0;

2)

select count(*)
FROM lfa1 a
LEFT JOIN lfb1 b
    ON a.lifnr = b.lifnr

LEFT JOIN LFM1 m1
    ON a.lifnr = m1.lifnr

LEFT JOIN LFM2 m2
    ON a.lifnr = m2.lifnr
    and m1.ekorg= m2.ekorg

WHERE
    a.hvr_is_deleted = 0
    and b.hvr_is_deleted = 0
    and a.mandt = '100'
    and b.mandt = '100'
    and m1.mandt = '100'
    and m1.hvr_is_deleted = 0
    and m2.mandt = '100'
    and m2.hvr_is_deleted = 0;

Both literally having same meaning but logically it's not working as both of the snippets are giving different counts. I want know how controls will be passed here to execute this code.
Can someone help me to understand would be appreciated.

Thanks

Ravi

As indicated in comments these queries are not equivalent. To understand why the are different, let's examine them:

create table A (x int);
insert into A(x) values (1);

create table B (x int, y int);
insert into B(x,y) values (2,2);

select A.x, B.x, B.y
from A
left join B
    on A.x = B.x
    and B.y = 2;

Since no rows match the join predicate, B.x and B.y are replaced with null in the resulting relation:

A.x B.x     B.y
1   null    null

If we move B.y to the where clause:

select A.x, B.x, B.y
from A
left join B
    on A.x = B.x
where B.y = 2;

you will get 0 rows as a result. To understand why, the WHERE clause is evaluated after the JOIN, so this is the same as:

select Ax, Bx, By
from (
    select A.x as Ax, B.x as Bx, B.y as By
    from A
    left join B
        on A.x = B.x
) as T
where By = 2;

But

    select A.x as Ax, B.x as Bx, B.y as By
    from A
    left join B
        on A.x = B.x

still evaluates to

Ax  Bx      By
1   null    null

so your where clause will be:

where null = 2

i.e.

where null

which isn't TRUE, so the result is empty

Fiddle


Tune Performance by putting large tables into the KEEP Pool We currently have a database with 20GB SGA space. SQL has been optimized but has to statisfy its use cases as well and some queries are very, very slow, specifically when large result sets are returned as the results of a text index search, which are then fetched by db file sequential read. This leads to timeouts in the application and our users complain about them. Our storage is slow and we cannot change that, unfortunatelly (slow as in 15 to 20ms peak for an IO request). The idea is now to put these tables (8GB in total) into the KEEP Pool and providing the database with 16GB additional RAM. Further, we will turn on huge pages on the system.

Do you think this is a good way to tackle the problem? Are there any unforseen pitfalls that we should be aware of? Is it easy to put the tables into the KEEP Pool? Will they stay in the KEEP Pool then or is it possible that Oracle just ignores the storage statement?

Database Version is 11.2.0.3.0

You can put the table in KEEP Pool cache and this may have a positive impact on your queries. I have implemented it in my system for a 16 GB partition and we did get good performance gains.

However putting a table in cache does not mean that its data is loaded into memory automatically.
It only means that since we are using a separate buffer pool for these tables, the data blocks that are loaded into memory are not aged out from the buffer as frequently as they would have if you were using the default buffer pool.

So in case your queries are fetching same data blocks repeatedly there will be lesser aging out and better performance gains. However, in case your queries are fetching different data blocks which are not present in the cache then you will be having some disk reads happening and old blocks may get aged out of bufer.

Also, have you evaluated RESULT_CACHE hint for yor query, in case you are not using GTTs you may be able to get good performance gains from that also.


How to extract certificate attributes from a single column and split into many? In my table I've a field (let say cert_attr) which stores Certificate X.509 Attributes.
Here is the example of 3 rows (each line corresponds to a field):
"CN=User1, OU=Eng, O=Company Ltd, L=D4, S=Dublin, C=IE"
"CN=User2, OU=Eng, O=Company Ltd, L=D2, S=Dublin, C=IE"
"OU=Eng, O=Company Ltd"

And I'm trying to split the value of a field into separate columns using SELECT in the following way:
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "CN=", -1), ", ", 1) as CN,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "OU=", -1), ", ", 1) as OU,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "O=", -1), ", ", 1) as O,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "L=", -1), ", ", 1) as L,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "ST=", -1), ", ", 1) as ST,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "C=", -1), ", ", 1) as C
FROM mytable

which works, however there is an issue for the rows which are missing some attributes.
So in the case where the attribute is missing in the field's string, I expect the column to be empty, but it returns the whole string instead.
The first two row examples are working as expected, which returns the following columns correctly:
| CN    | OU  | O           | L  | S.     | C  |
| ----- | --- | ----------- | -- | ------ | -- |
| User1 | Eng | Company Ltd | D4 | Dublin | IE |
| User2 | Eng | Company Ltd | D2 | Dublin | IE |

The problem is with the 3rd row example, which I expect to return an empty string when the substring pattern is not found:
| CN         | OU  | O           | L          | S.         | C          |
| ---------- | --- | ----------- | ---------- | ---------- | ---------- |
| OU=Eng,... | Eng | Company Ltd | OU=Eng,... | OU=Eng,... | OU=Eng,... |

but instead the whole string is returned.
Question:
Is there any way to return an empty string when SUBSTRING_INDEX() fails to find the substring? Or maybe there is some other function (like a regular expression) or another workaround?
My goal is to extract the data into TSV file by having these attributes in separate columns with valid values:
mysql mytable < query.sql > cert_attributes.tsv

The first part is only to show you that the procedure works

CREATE tABLE tablecertificate  (
stationery_name varchar(500)

INSERT INTO tablecertificate values
("CN=User1, OU=Eng, O=Company Ltd, L=D4, S=Dublin, C=IE"),
("CN=User2, OU=Eng, O=Company Ltd, L=D2, S=Dublin, C=IE"),
("OU=Eng, O=Company Ltd")

CREATE PROCEDURE `splitcertifcate`()
BEGIN
 DECLARE current_pos INT DEFAULT 1;
 DECLARE delim CHAR DEFAULT ',';
 DECLARE current VARCHAR(100) DEFAULT '';
 DECLARE CN VARCHAR(100) DEFAULT '';
 DECLARE OU VARCHAR(100) DEFAULT '';
 DECLARE O VARCHAR(100) DEFAULT '';
 DECLARE L VARCHAR(100) DEFAULT '';
 DECLARE S VARCHAR(100) DEFAULT '';
 DECLARE C VARCHAR(100) DEFAULT '';
 DECLARE rest_cert_part VARCHAR(100) DEFAULT '';
 DECLARE current_cert_part  VARCHAR(100) DEFAULT '';
  DECLARE finished INTEGER DEFAULT 0;
  DECLARE certificate varchar(500) DEFAULT "";
  DEClARE curcertificate
      CURSOR FOR
          SELECT stationery_name FROM tablecertificate;

  -- declare NOT FOUND handler
  DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;

  #Temporary Table that holds the splittet part
   DROP TEMPORARY TABLE IF EXISTS mycertificate;
  CREATE TEMPORARY TABLE mycertificate(CN VARCHAR(100), OU VARCHAR(100), O VARCHAR(100) , L  VARCHAR(100), S VARCHAR(100), C VARCHAR(100));

  OPEN curcertificate;
    getcertificate: LOOP
      # get first row elemnt
      FETCH curcertificate INTO certificate;
      IF finished = 1 THEN
          #Last element reached
          LEAVE getcertificate;
      END IF;
        SET CN = '';
        SET OU = '';
        SET O = '';
        SET L = '';
        SET S = '';
        SET C = '';
      SET current_pos =  LOCATE(delim,certificate);
        SET current_cert_part = SUBSTRING(certificate,1,current_pos-1);
      SET rest_cert_part = SUBSTRING(certificate from current_pos+1);
        IF length(trim(current_cert_part)) = 0   THEN
          SET current_cert_part = rest_cert_part;
      END IF;
      #Examine first element of the string
        CASE
          WHEN INSTR(current_cert_part, "CN=")  > 0 THEN SET CN = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "OU=")  > 0 THEN SET OU = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "O=")  > 0 THEN SET O = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "L=")  > 0 THEN SET L = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "S=")  > 0 THEN SET S = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "C=")  > 0 THEN SET C = TRIM(current_cert_part);
        END CASE;
        WHILE current_pos <> 0 DO
          #loop throuigh the string
          set  current_pos = LOCATE(delim,rest_cert_part);
          set current_cert_part = SUBSTRING(rest_cert_part,1,current_pos-1);
          set rest_cert_part = SUBSTRING(rest_cert_part from current_pos+1);

          if length(trim(current_cert_part)) = 0   then
              #ÖLast elemnt in string
              set current_cert_part = rest_cert_part;
          end if;
          CASE
              WHEN INSTR(current_cert_part, "CN=")  > 0 THEN SET CN = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "OU=")  > 0 THEN SET OU = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "O=")  > 0 THEN SET O = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "L=")  > 0 THEN SET L = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "S=")  > 0 THEN SET S = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "C=")  > 0 THEN SET C = TRIM(current_cert_part);
          END CASE;
      END WHILE;
        #In sert splittet strung  row in temp table
       INSERT INTO mycertificate VALUES (  CN,OU,O,L,S,C);

  END LOOP getcertificate;
  CLOSE curcertificate;
SELECT * FROM mycertificate;
END

cALL splitcertifcate();

CN       | OU     | O             | L    | S        | C
:------- | :----- | :------------ | :--- | :------- | :---
CN=User1 | OU=Eng | O=Company Ltd | L=D4 | S=Dublin | C=IE
CN=User2 | OU=Eng | O=Company Ltd | L=D2 | S=Dublin | C=IE
         | OU=Eng | O=Company Ltd |      |          |

✓

db<>fiddle here
The complete stored procedure including producing the tsv file
DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `splitcertifcate`()
BEGIN
 DECLARE current_pos INT DEFAULT 1;
 DECLARE delim CHAR DEFAULT ',';
 DECLARE current VARCHAR(100) DEFAULT '';
 DECLARE CN VARCHAR(100) DEFAULT '';
 DECLARE OU VARCHAR(100) DEFAULT '';
 DECLARE O VARCHAR(100) DEFAULT '';
 DECLARE L VARCHAR(100) DEFAULT '';
 DECLARE S VARCHAR(100) DEFAULT '';
 DECLARE C VARCHAR(100) DEFAULT '';
 DECLARE rest_cert_part VARCHAR(100) DEFAULT '';
 DECLARE current_cert_part  VARCHAR(100) DEFAULT '';
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE certificate varchar(500) DEFAULT "";
    DEClARE curcertificate
        CURSOR FOR
            SELECT stationery_name FROM tablecertificate;

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;

    #Temporary Table that holds the splittet part
     DROP TEMPORARY TABLE IF EXISTS mycertificate;
    CREATE TEMPORARY TABLE mycertificate(CN VARCHAR(100), OU VARCHAR(100), O VARCHAR(100) , L  VARCHAR(100), S VARCHAR(100), C VARCHAR(100));

    OPEN curcertificate;
    getcertificate: LOOP
        # get first row elemnt
        FETCH curcertificate INTO certificate;
        IF finished = 1 THEN
            #Last element reached
            LEAVE getcertificate;
        END IF;
        SET CN = '';
        SET OU = '';
        SET O = '';
        SET L = '';
        SET S = '';
        SET C = '';
        SET current_pos =  LOCATE(delim,certificate);
        SET current_cert_part = SUBSTRING(certificate,1,current_pos-1);
        SET rest_cert_part = SUBSTRING(certificate from current_pos+1);
        IF length(trim(current_cert_part)) = 0   THEN
            SET current_cert_part = rest_cert_part;
        END IF;
        #Examine first element of the string
        CASE
            WHEN INSTR(current_cert_part, "CN=")  > 0 THEN SET CN = TRIM(current_cert_part);
            WHEN INSTR(current_cert_part, "OU=")  > 0 THEN SET OU = TRIM(current_cert_part);
            WHEN INSTR(current_cert_part, "O=")  > 0 THEN SET O = TRIM(current_cert_part);
            WHEN INSTR(current_cert_part, "L=")  > 0 THEN SET L = TRIM(current_cert_part);
            WHEN INSTR(current_cert_part, "S=")  > 0 THEN SET S = TRIM(current_cert_part);
            WHEN INSTR(current_cert_part, "C=")  > 0 THEN SET C = TRIM(current_cert_part);
        END CASE;
        WHILE current_pos <> 0 DO
            #loop throuigh the string
            set  current_pos = LOCATE(delim,rest_cert_part);
            set current_cert_part = SUBSTRING(rest_cert_part,1,current_pos-1);
            set rest_cert_part = SUBSTRING(rest_cert_part from current_pos+1);

            if length(trim(current_cert_part)) = 0   then
                #ÖLast elemnt in string
                set current_cert_part = rest_cert_part;
            end if;
            CASE
                WHEN INSTR(current_cert_part, "CN=")  > 0 THEN SET CN = TRIM(current_cert_part);
                WHEN INSTR(current_cert_part, "OU=")  > 0 THEN SET OU = TRIM(current_cert_part);
                WHEN INSTR(current_cert_part, "O=")  > 0 THEN SET O = TRIM(current_cert_part);
                WHEN INSTR(current_cert_part, "L=")  > 0 THEN SET L = TRIM(current_cert_part);
                WHEN INSTR(current_cert_part, "S=")  > 0 THEN SET S = TRIM(current_cert_part);
                WHEN INSTR(current_cert_part, "C=")  > 0 THEN SET C = TRIM(current_cert_part);
            END CASE;
        END WHILE;
        #In sert splittet strung  row in temp table
       INSERT INTO mycertificate VALUES (  CN,OU,O,L,S,C);

    END LOOP getcertificate;
    CLOSE curcertificate;

    SELECT * FROM mycertificate INTO OUTFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/cert.tsv"
    FIELDS TERMINATED BY '\t'
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';
END//
DELIMITER ;

Result tsv file
"CN=User1"  "OU=Eng"    "O=Company Ltd" "L=D4"  "S=Dublin"  "C=IE"
"CN=User2"  "OU=Eng"    "O=Company Ltd" "L=D2"  "S=Dublin"  "C=IE"
""          "OU=Eng"    "O=Company Ltd"  ""       ""        ""

You must check the INTO OUTFILE folder, that must correspond with the entry in the my.ini/cnf file
secure-file-priv='C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/'


Language setting for 'TZD' format in TO_CHAR (timestamp with time zone) Which setting controls the language of 'TZD' or 'TZR' in the TO_CHAR(timestamp with time zone) function?
e.g.
select to_char(systimestamp at TIME zone 'AMERICA/EDMONTON', 'TZD') from dual;
returns the English value for TZD "MDT" (Mountain Daylight Time), which is expected as the default value.
However, I can't find which parameter or setting to use for it to return the French value of TZD, i.e. "HNR"

I've tried adding ", 'nls_date_language=FRENCH'" to the TO_CHAR example above, as well as altering the session parameters NLS_LANG and NLS_DATE_LANGUAGE, but to no avail.

You can get the TZD abbreviations with this query:
SELECT tzabbrev, tzname, TZ_OFFSET(tzname)
FROM V$TIMEZONE_NAMES
WHERE tzname = 'America/Edmonton'

TZABBREV    TZNAME              TZ_OFFSET(TZNAME)
----------  ------------------  ------------------
LMT         America/Edmonton    -06:00
MST         America/Edmonton    -06:00
MDT         America/Edmonton    -06:00
MWT         America/Edmonton    -06:00
MPT         America/Edmonton    -06:00

I think they are fix and you cannot change it. Note, for time zone abbreviations there is no common or official standard, for every time zone you will find dozens of abbreviations, see https://www.timeanddate.com/time/zones/na.
If you need a language specific output you could use CASE or DECODE
DECODE(to_char(systimestamp at TIME zone 'AMERICA/EDMONTON', 'TZD'), 'MDT','HNR' ,'HAR')


Shared locks taken under Read Committed Snapshot Isolation (RCSI) I am seeing waits for shared locks (LCK_M_S) when using RCSI. My understanding is that this isn't supposed to happen since SELECTs do not require shared locks when using RCSI.

How can I be seeing shared locks? Is it because of foreign keys?

How can I be seeing shared locks? Is it because of foreign keys?

Yes. SQL Server reverts to the locking implementation of the read committed isolation level when accessing a table for the purpose of validating foreign key constraints. This is required for correctness, and cannot be disabled.

The behaviour applies only to data-modification statements. Shared locks are only taken when checking the foreign-key related data. Other data access in the same execution plan can continue to use row versioning.

If SQL Server did not do this, data-modification statements under RCSI could end up violating the foreign key constraint because the integrity check used out-of-date (versioned) data.

Unfortunately, there is currently no supported way to see this change in locking behaviour in an execution plan. It is possible to see the internal locking hints when trace flag 8607 is active.


Increasing mysql ram usage to decrease disk activity I would like to know if the increasing the following parameters will improve the mysql performance.
| query_cache_size               | 16777216             |
| query_cache_limit              | 1048576              |
| table_open_cache               | 2000                 |
| table_open_cache_instances     | 16                   |
| thread_cache_size              | 8                    |
| host_cache_size                | 279                  |

Right now, the innodb_buffer_pool_size is 2G.
UPDATE:
The output of all variables is available at pastebin. I would like to decrease IO tasks as I have 32GB of memory and it is find to set 16 GB to mysql.

Disk activity is either lots of stuff being read in or lots of stuff being written out because there isn't enough room in memory to hold it.
Increasing memory might reduce your disk activity, but it might not improve your overall performance.
Left to its own devices, your DBMS will not do lots of disk I/O for itself.  It only does so in response to queries that are sent to it.
You need to look at the workload (queries) that the server is running.  The chances are there are some "chunky" ones in there that are trawling through a lot of data.  Find them, tune them, get rid of them.  That will improve your server's overall performance.


SQL Server 2008 server cannot connect to itself with alias name or "localhost" We have a SQL Server 2008 ENT 64bit SP3 server running Server 2008 R2 Standard SP1 64-bit.  This is also a windows/sql clustered environment.  There is the cluster alias for the server, and then we have 2 AD DNS aliases as well for the server

Suddenly I can't connect to the sql instance in Management Studio, or with a Powershell Invoke-SQLcmd IF I am doing it from that actual server, when using one of the dns alias names, or "localhost".  If I use the Cluster alias name it works and if I use the other dns alias name it works.

Using management studio with the non-working dns alias I get "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."

If I use localhost, I get "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)"

Shared Memory and TCPIP are enabled in SQL Server Config manager but named pipes is not.  But we've NEVER had named pipes enabled, and don't have it enabled on any of our other sql servers and they're set up the same (clustering, dns aliases...etc) and are having no issues.

I've Googled around and had a view people suggest listing what other SQL servers it can see (cmd prompt sqlcmd -L)  If it do it from a good server I see all other servers on my network including the "bad" one.  If I do that command from the "bad" server, I get no results at all.

Pretty much checked everything from this blog and it's all ok except the sqlcmd -L issue I just mentioned above.

http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

Windows firewall is not enabled.

This was working just fine for a couple of years.  I'm not sure when this started happening, but I know that at some point, it was not happening.

And the other kicker...it's only happening locally to this server.  If I connect with management studio or run a powershell Invoke-Sqlcmd from another machine, or my local machine connecting to the "bad" server, it works fine no matter what alias I use.

I'm stumped.  I think it sounds like an issue with the AD alias (there are 2 ad aliases, 1 works locally the other doesn't)...but not being able to connect with localhost makes me question that.

any help is greatly appreciated.

Not sure if this will help or not, but i had the same issue with a clustered environment.

localhost will only refer to the same machine it was run on, if the sql server is located on a different machine, localhost will fail, as the server is not on that machine.

I found this issue when i failed over to the second box in the cluster, and from the first machine i ran the localhost and it failed, i jumped over to the second machine and ran the localhost command and it worked....

i failed the second machine back over to the original machine, and issued the localhost command on the newly failed over to machine, and it would work again, but the localhost would fail on the old machine.

long story short, localhost only applies to the machine it is being run from, and the cluster sub-system does not really recognize the localhost command...  best to use the virtual IP address of the cluster/sql server for all access.


Session in "Sql*net message from client", but is active I've Googled around, and my impression is that

  Sql*net message from client

suggests the Oracle DBMS is waiting for the client to send new commands to the DBMS, and therefore any time spent in this event should be client-side time and not consume DB server CPUs. In other words, normally, if a session is in this event, it should be "INACTIVE" rather than "ACTIVE".

What's puzzling to us is that starting from this week (after we started using connection pools [we use dbcp]), we occassionally see sessions in the

  Sql*net message from client

event and showing "ACTIVE" at the same time for extended periods of times. And during all this time, CPU usage on the DB is high.

Can anyone shed some light on what this means?  If the DB session is waiting for the client to send a message, what can it be "ACTIVE" and consuming CPU cycles for?

Looks like we found the culprit. Following the recent app server upgrade, we inadvertently included both ojdbc14 and ojdbc6 jars into our deployment, and evidently, the jvm picked up ojdbc14 for its Oracle DB driver. Since we removed ojdbc14 manually, this problem hasn't come up again in the past 24 hours. I assume ojdbc14 is no longer officially supported, so it could cause all sorts of funny problems.

Our next step is to find out how ojdbc14 sneaked into the deployment in the first place.


Multi-Master Multi-Region MySQL Cluster I am hosting an application (a game) in multiple regions around the globe. Performance is critical to this application since it's a game, and therefore I need to host a database instance in each of these locations that will serve the local application instances.
Due to the nature of my application, I am only able to use MySQL.

My main requirement is that each of these instances will be writable. I understand that in most cases this is a big problem in MySQL clusters when it comes to two servers modifying the same record.
However, in my application this is actually impossible; a player can only be connected to a single region at any given moment, which means only that region's database instance will read or write that player's data. Consistency is also not critical here and Eventual Consistency is okay for my needs. I just need to make sure it gets there eventually.

I haven't worked with replication before and therefore I need some guidance. What would be the right way to implement this, and how would I go about deploying such a cluster?

You probably want a cluster such as MySQL InnoDB Cluster (from Oracle) or MariaDB Galera Cluster or Percona XtraDB Cluster in each region for high availability. You can write to any of the nodes in the cluster and the transaction will be automatically replicated to the other nodes. Note that both Percona XtraDB Cluster and MariaDB Galera Cluster are based on the Galera cluster technology. Galera also provides libraries for regular MySQL.

For an automatic failover solution, you may want to use one of these solutions:

A library in your application that detects whether a node is down, and if so, attempts to use another node in the cluster. This can be e.g. MariaDB's JDBC driver - MariaDB connector/J which I believe also works with Percona and MySQL. You need to configure the connector to use a cluster.
A database proxy for each cluster/region, such as ProxySQL (GPL) or MaxScale (BSL, but older versions automatically become GPL - currently the newest GPL version is 2.1). Database proxies are database-aware: If there's a problem with any of the nodes, it will mark that node as unavailable until the problem has been resolved.

And then you probably want to have master-master asynchronous replication between those clusters. Master-master asynchronous replication simply means that you set up asynchronous master-slave replication from node A to node B and at the same time also asynchronous master-slave replication from node B to node A.

Note that asynchronous master-master replication is notoriously problematic if there is a risk that the two nodes will write to the same rows. Many tutorials on the topic also fail to mention how to avoid many collision problems by setting different values for auto_increment_increment and auto_increment_offset for each node.

Also note that geo-distributed nodes within a cluster is probably not a good idea due to latency and unreliable networks, although Galera doesn't seem to be concerned about this issue in their documentation.

Personally, I wouldn't want to use MySQL 8.0 because of inherent stability issues with the new development and release model: Major new features are now introduced in patch releases (8.0.x). Everyone obviously wants to use the latest patch release because it contains bug & security fixes of previous versions, but since the patch releases also contain major new features, there is a serious risk that new bugs will also be introduced. I'm also not sure what free options you have for non-intrusive backup with MySQL 8.0. (There are paid options, of course, if you go with a MySQL Enterprise license.) You may be able to use Percona Xtrabackup.

It's also possible there is a cloud DBaaS solution for this use-case such as AWS RDS or MariaDB Platform Managed Database Service. I'm unsure how this compares in terms of cost and features, but it might be considerably easier to configure and manage than what I have outlined above.


SSIS 2017 Scale Out functionality and use cases SSIS 2017 has a scale out feature that distributes executions across multiple SSIS worker instances. I think get the big picture, but just wondering how it divides up the work across the worker instances.

For example if I have a for-each container looping on 1000 files to import, does each worker instance import a single file then report back to the master to get another file to process?

What's a good use case for scale-out?

I found theses nice posts that explain when and how work SSIS Scale Out feature. Here are some passages  :

"If your ETL processing looks like one of these scenarios, this could be for you.

You have a workloads that peak at certain times
at a specific time of day (overnight load, during business hours)
at specific times of month or year (month end, black Friday)
You have a variety of unrelated ETL jobs owned by different people or business areas running on the same server and competing for resources at the same time
You have an increasing data volume due to business growth, and you want your ETL solution to grow with you
You have a variety of unrelated ETL jobs owned by different people or business areas installed on different servers making it hard to know what's installed where
"

Steph Martin @ coeo
https://blog.coeo.com/getting-started-with-ssis-scale-out

"If you have lots of disparate processes and packages that are unrelated and independent of each other, then enabling scale out will work for you without having to change anything on your existing jobs.

If you are using master packages to handle execution of a set of packages you will probably have two main drivers; simplicity of management, and parallel execution. You can move directly to using scale out and retain both of these things, but you won't necessarily gain anything. If your workload is a single end to end load, all handled by a single master package (for example a data warehouse ETL load that pulls data from multiple source tables, stages the data, transforms the data and then loads to the destination), then all you are going to achieve is moving the same workload to a different server.

In order to benefit from scale out you would need to execute each package separately. If you do that in a single agent job with multiple steps you will lose your parallelism. You could have multiple jobs all executed at the same time, but that just feels wrong, and will get hard to maintain and monitor, especially if you have lots of agent jobs already."

Steph Martin @ coeo
https://blog.coeo.com/executing-packages-with-ssis-scale-out

Hope this will answer your questions


inconsistent read before/after checkpoint? Let's say the transaction in connection 1 tried to modify record A and then commit.Because the commit only flush log record in buffer to disk, it's checkpoint's job to flush all dirty page to disk (mdf).
And there is a connection B tried to read record A after the connection 1's commit.(since A has committed, there is no lock thing here)

so my questions are:

Q1- if connection B read record A before checkpoint, does it mean it will read the old value?

Q2- if connection B read record A after checkpoint, does it mean it will read the new value?

Q3- isn't that weird that sometimes connection B get new value and sometimes it get old value, is the way to fix this problem is A needs to manually trigger a checkpoint?

To answer your specific questions about this scenario:

  if connection B read record A before checkpoint, does it mean it will read the old value?

No.

  if connection B read record A after checkpoint, does it mean it will read the new value?

It will read the new, committed value regardless of the CHECKPOINT.

  isn't that weird that sometimes connection B get new value and sometimes it get old value, is the way to fix this problem is A needs to manually trigger a checkpoint?

No, issuing a manual CHECKPOINT is not necessary.

When a query needs to read a particular data page, it first tries to read that page from the buffer pool (RAM).  This is called a "logical read."

If that page is not in the buffer pool, it will be read from disk, placed into the buffer pool, and then that in-memory value will be used by the query.  This is called a "physical read."

The point being that rows / data pages that have been updated in RAM (so-called "dirty pages") can be read from memory by multiple queries, and by multiple connections, without first needing to be written to the data file on disk.

In fact, a value in the buffer pool can be updated multiple times before being written to disk.

The CHECKPOINT process is not really related to isolation levels, locking, or correctness of query results.  It's mainly a performance optimization (to prevent lots of small writes from happening all the time) and a recoverability feature (to reduce the time it takes to recover the database to a consistent state from the log).

You can read a lot of in-depth information about these topics in the following Microsoft Docs articles:

Memory Management Architecture Guide - Buffer management
Reading Pages
Writing Pages


Are there better alternatives rather than using a "Full outer join" when it is not possible to use a "left or right join"? I have two tables with below structure :

 Create table Modern_transaction
 (Eeffective_date   Date,
  Customer_Num      Number,
  Branch_Code       Number,
  MTrns_type_one    Number,
  MTrns_type_two    Number)

 Create table Card_transaction
  (Eeffective_date   Date,
   Customer_Num      Number,
   Branch_Code       Number,
   CTrns_type_one    Number,
   CTrns_type_two    Number)

I need to have the data of both table in one table with this structure:

Create table Customer_transaction
  (Eeffective_date   Date,
   Customer_Num      Number,
   Branch_Code       Number,
   MTrns_type_one    Number,
   MTrns_type_two    Number
   CTrns_type_one    Number,
   CTrns_type_two    Number)

some example data :

                                Modern_transaction
 ----------------------------------------------------------------------------------
   Eeffective_date | Customer_Num  | Branch_Code | MTrns_type_one  |  MTrns_type_two
      9/22/2013    |       x       |       12    |       10        |      20
      9/22/2013    |       y       |       13    |       20        |       0
      9/22/2013    |       z       |       18    |       12        |      12

                                Card_transaction
 ----------------------------------------------------------------------------------
   Eeffective_date | Customer_Num  | Branch_Code | CTrns_type_one  |  CTrns_type_two
      9/22/2013    |       x       |       22    |       10        |      20
      9/22/2013    |       y       |       13    |       20        |       0
      9/22/2013    |       y       |       12    |       20        |      20

The point is that I'm using a full join for this :

 select nvl(mt.Eeffective_date,ct.Eeffective_date),
        nvl(mt.Customer_Num ,ct.Customer_Num),
        nvl(mt.Branch_Code ,ct.Branch_Code),
        nvl(mt.CTrns_type_one,0),
        nvl(mt.CTrns_type_two,0),
        nvl(mt.MTrns_type_one,0),
        nvl(mt.MTrns_type_two,0)
 from Modern_transaction mt full join
      Card_transaction  ct
   on mt.Eeffective_date = ct.Eeffective_date and
      mt.Customer_Num    = ct.Customer_Num
      mt.Branch_Code     = ct.Branch_Code

according to many references , full join is harmful and it's not suggested. for example take this for example https://weblogs.sqlteam.com/jeffs/2007/04/19/full-outer-joins/. I wan to know is it always bad and harmful to use  full outer join? I mean do yo consider full join as your last solution ? Are there better alternatives for writing this query? In which circumstances will full join produce false or duplicate result?Using a left join is not possible here cause non of the tables above could be considered as the base table.

Update :
  The primary key of both tables is the combination of Eeffective_date ,Customer_Num,Branch_Code

thanks in advance

If you need to use a full outer join, use a full outer join.  It exists for a reason.  In general, though, it should be an uncommon tool in the toolbox.

When designing systems, you generally try to minimize the number of outer joins that you need to do.  In this case, it would seem to make more sense to have a general transaction table that both modern_transaction and card_transaction are children of.  Then you'd just need to do a left outer join to both children from the parent.

A full outer join isn't going to produce false results.  It will produce exactly the results you ask for.  As with any code, however, that may not be the results you actually want.  In your case, what is the primary key of each table?  Eeffective_date is a date and dates always store time down to the second.  Are you storing the actual date there?  Or are you truncating the date and storing the time as midnight?

If the primary key is Eeffective_date, customer_num, branch_code and you are storing the time as midnight, that implies that each customer can only do one of each transaction at each branch every day.  It seems unlikely that a customer couldn't do two transactions in one day.
If the primary key is Eeffective_date, customer_num, branch_code and you are storing the actual time, then customers can do multiple transactions every day but it would be likely that some transactions that should match wouldn't match in your join because one table was inserted into with a value just before the change of a second and the other was inserted into with a value just after the change.
If the primary key is something else, then a single row from t1 might match multiple rows from t2 and vice versa.  The full outer join will do exactly what you're asking but it is extremely rare that this would be what you want to happen.


Integrated Authentication vs? my coworker and I had a debate and I am looking for Reddit to resolve it! We have PHP web apps running on IIS and are looking to setup the security to the database using one approach:

-What would you call the method where the service account running the application pool authentication to the SQL Server database using its own credentials?

-What would you call the method where the users connecting to the app on the network authenticate to the apps SQL Server database using their own network credentials?

Which is “integrated authentication”?!

They are both "Integrated Authentication", as opposed to "SQL Authentication" which uses a user name and password stored in SQL Server.

The first is "Integrated Authentication With a Shared Servcie Account".

The second one "Integrated Authentication With a End-User Impersonation".


if else loop only iterating once I've discovered that my company's ETL proccess has a major bug in it around a process that checks if the ID in the staging database has already been used in the real database. In theory this processes should iterate through until it find an ID that has not been used, however on study it doesn't do that, only iterating once before stopping.

BEGIN
-- there is only ever 1 row in staging.
declare id1 char(12) default (select id from stg.extraction);

loop_label: Loop

if id1 not in (select id from main.extraction) Then
   leave loop_label;

end if;
   set id1=id1+1;
   leave loop_label;
 End Loop;

update stg.extraction
set id=id1;

END

I've tried several possible solutions but none of them seem to change things. I think I need to convert it to a while loop but I'm not sure.

Don't do it in a loop.  Do it in a single multi-table UPDATE and/or INSERT ... SELECT ... LEFT JOIN ... IS NULL.

More details:  http://mysql.rjweb.org/doc.php/staging_table#normalization


Understanding DB Throughput Calculations I'm trying to understand more about databases and am taking a course that calculates throughput throughout. An example for a DynamoDb table looks like...

  our application requires us to read 10 items of 6KB / second, what should we set the throughput for?

Coming at this from a web dev's perspective I want to understand what is happening here.

Is this saying that if someone requests 10 items from our DB we want them to be able to get 6KB worth of data per second for each item?

So if those 10 items total size is 100 KB and we have our DB configured to read 6 KB of data per second, it will take about 17 seconds to perform that read operation (100/6)?

Am I understanding the requirements correctly or do I have something fundamentally wrong? I want to make sure I have this concept down before moving on.

If we are talking about throughout and the scenario you describe i would consider that your requirement is 10 x 6kb/sec which would be 60kb/sec.

So yes assuming you had 10 concurrent requests and you tried to move 10 x 100kb it would take 17 seconds.

In theory 1000kb / 60kb per sec = 16.66sec.

In the real world if the app required data from the db, the response time would vary depending on whether the db had to parse the query, create an execution plan and then read from disk. This all adds up to delays (latency) before the data starts being returned to the app.

If this is a commonly executed query (for example in a stored proc) the query has already been parsed, execution plan created and data already held in memory this means the initial delay would be much lower.

I would say it would be rare for an app to consistently consume data with 10 simultaneous connections. I guess a video streaming app could set a constant load for long durations. But more likely as other connections start and stop there would be more or less bandwidth available so transfer times could be lower. I would expect to see fluctuations in transfer rates.

Also: watch out for kilobit vs kilobyte,  And expect there will be some overhead. I.e It takes more than 6kb to move 6kb worth of data.


SQL Server Performance Counters Relating to Disk Access and Log Files That Appear Concerning I want to validate the optimal performance of our SQL Server and in doing so, I have gone through all the performance counters listed at the following link and create a trace log to analyze the data.

http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf

I have gone through three days worth of collected data and compared it to the recommended values listed in that chart, and though most of my values look good, there a just a few that are outside of the recommended values listed and I want to get some feedback from others on how their systems compare and if any of my values should raise a red flag.

As a point of clarification, our SQL Server is 2008, running on a virtual machine, with 4 cores and 8 GB of memory.  The SQL VM connects directly, via iSCSI, to our SAN which has three separate disk groups (separate spindles) for the Database files, Log files and TempDB, and all are a RAID 1 configuration.

Here are the counters that I am concerned by:

For the SQLServer:Databases\Log Flush Wait Time counter, it is staying fairly constant between 600 ms and 900 ms with an average of 750ms.  According to the chart listed above, it states that this value should be around 0 ms.  Why would my value be significantly higher?
For SQLServer:Databases\Log Growths, I'm at 325 and for SQLServer:Databases\Log Truncations, I'm at 14200.  I assume that this is since the start of SQL Server, correct?  If so, the chart says that growths should be around 0.  Is this something I should worry about?  The last reboot of the server was a month ago and there are about 100 databases on the server.
For the Memory\Pages Input/sec counter, the values fluctuate dramatically and have an average of 6 but have many spikes well upwards of 100 (some as high as 400).  According to the chart, this value should be less than 10.  Should I be concerned by these spikes?
Finally for the PhysicalDisk\Avg. Disk sec/Write counters:

4a. For the TempDB volume, I get dramatic spikes every few minutes that are upwards of 100 ms.  The guide states that these values should be less than 20 ms. Should I be concerned by these tenth of a second spikes?
4b. For the Log Files volume, it stays almost steady at 8 ms.  Is it normal for the Log Files to have consistent writes with no break?
4c. Regarding these disk counters, in Windows Disk Management > Properties > Policies, the disks don’t have the “Enable write caching on the device” checked nor the “Turn off Windows write-cache buffer flushing on the device” checked.  Should these be enabled for a SAN that has a battery backed cache?  Could this be the cause of 4a and 4b?  If so, can they enabled during production hours without interrupting data flow?

Any input in regards to what others see on their production systems or if any of these values seem abnormal are appreciated.

SQLServer:Databases\Log Flush Wait Time counter, it is staying fairly constant between 600 ms and 900 ms with an average of 750ms

750ms wait time per flush is huge. It means every transaction statement must wait in average 750ms to commit. If you don't use explicit transactions for writes it means each write statement (INSERT/UPDATE/DELETE) must wait 750ms to complete. However this does not corroborate with the other sttaement 'PhysicalDisk\Avg. Disk sec/Write ... For the Log Files volume, it stays almost steady at 8 ms.'. One explanation is that you have log growths (you do, the counters say so) which skew the log flush wait times but that would only explain averages over long periods.

  SQLServer:Databases\Log Growths, I'm at 325

Figure out which database is growing and resize the log appropriately. You should not occur growth events in production, they are extremely expensive. the counter has instances per database.

  PhysicalDisk\Avg. Disk sec/Write ... For the Log Files volume, it stays almost steady at 8 ms. Is it normal for the Log Files to have consistent writes with no break?

Yes. A transaction cannot commit unless the log was written to disk. As long as you have transactions, you will have log disk writes.

  PhysicalDisk\Avg. Disk sec/Write counters: For the TempDB volume, I get dramatic spikes every few minutes that are upwards of 100 ms.

It would be good to know why. Can you correlate it with a specific activity that causes the spike? Can you correlated it with database checkpoints occurring?

  Memory\Pages Input/sec counter, the values fluctuate dramatically and have an average of 6 but have many spikes well upwards of 100 (some as high as 400).

So you're paging. Why? Insufficient memory? Any other offending process on the server host? Virtualized overcommitted environment? Only you can tell. Does swapping IO overlap with any of the IOs above (same physical path, ie. same disks)? Then some of the SQL timers may be influenced by this paging spikes.


Finding "islands" of holiday periods for employees I'm looking for some tips to find a nice solution for my problem:

Let's say I have a table, where days of vacation of employees are stored. e.g.

CREATE TABLE test_dates (
  id             NUMBER        NOT NULL,
  holidaydate    DATE          NOT NULL,
  holidaycomment VARCHAR2(255) NULL
);

INSERT INTO test_dates VALUES (1, To_Date('17.08.2015','dd.mm.rrrr'), 'Trip to Berlin');
INSERT INTO test_dates VALUES (2, To_Date('18.08.2015','dd.mm.rrrr'), 'Trip to Berlin');
INSERT INTO test_dates VALUES (3, To_Date('19.08.2015','dd.mm.rrrr'), 'Trip to Berlin');
INSERT INTO test_dates VALUES (4, To_Date('20.08.2015','dd.mm.rrrr'), 'Trip to Berlin');
INSERT INTO test_dates VALUES (5, To_Date('21.08.2015','dd.mm.rrrr'), 'Stay at home');
INSERT INTO test_dates VALUES (6, To_Date('03.09.2015','dd.mm.rrrr'), 'Trip to London');
INSERT INTO test_dates VALUES (7, To_Date('04.09.2015','dd.mm.rrrr'), 'Trip to London');
INSERT INTO test_dates VALUES (8, To_Date('07.09.2015','dd.mm.rrrr'), 'Trip to London');
INSERT INTO test_dates VALUES (9, To_Date('08.09.2015','dd.mm.rrrr'), 'Trip to London');
INSERT INTO test_dates VALUES (10, To_Date('17.09.2015','dd.mm.rrrr'), NULL);

SELECT * FROM test_dates ORDER BY holidaydate;

Fine. Now I want to query this table to get the whole period of their holidays.

SELECT
  *
FROM (
  SELECT
    connect_by_root(id) AS id,
    connect_by_root(holidaycomment) AS holidaycomment,
    connect_by_root(holidaydate) AS date_from,
    Decode(connect_by_root(holidaydate), holidaydate, NULL, holidaydate) AS date_to,
    CONNECT_BY_ISLEAF AS is_leaf,
    LEVEL AS days
  FROM test_dates
  CONNECT BY PRIOR holidaydate = holidaydate - 1 AND (PRIOR holidaycomment = holidaycomment OR (PRIOR holidaycomment IS NULL AND holidaycomment IS NULL))
)
WHERE is_leaf = 1
ORDER BY date_from;

That query gave me 10 rows, a way too much (August 17th to 20th, August 18th to 20th and so on). So I'm trying to select the starting dates:

SELECT
  id, holidaycomment, date_from, date_to, days
FROM (
  SELECT
    connect_by_root(id) AS id,
    connect_by_root(holidaycomment) AS holidaycomment,
    connect_by_root(holidaydate) AS date_from,
    Decode(connect_by_root(holidaydate), holidaydate, NULL, holidaydate) AS date_to,
    CONNECT_BY_ISLEAF AS is_leaf,
    LEVEL AS days
  FROM test_dates
  -- NEW Code --
  START WITH id IN (
    SELECT t1.id
    FROM test_dates t1
    LEFT JOIN test_dates t2 ON t1.holidaydate - 1 = t2.holidaydate AND (t1.holidaycomment = t2.holidaycomment OR (t1.holidaycomment IS NULL AND t2.holidaycomment IS NULL))
    WHERE t2.id IS NULL
  )
  -- NEW Code --
  CONNECT BY PRIOR holidaydate = holidaydate - 1 AND (PRIOR holidaycomment = holidaycomment OR (PRIOR holidaycomment IS NULL AND holidaycomment IS NULL))
)
WHERE is_leaf = 1
ORDER BY date_from;

ID  HOLIDAYCOMMENT     DATE_FROM     DATE_TO      DAYS
------------------------------------------------------
1   Trip to Berlin     17.08.2015    20.08.2015   4
5   Stay at home       21.08.2015    NULL         1
6   Trip to London     03.09.2015    04.09.2015   2
8   Trip to London     07.09.2015    08.09.2015   2
10  NULL               17.09.2015    NULL         1

It works, but it seems to be too complicated for me. Is there a better way for select the date_from?

And my last problem: My/The Application stores no information on weekend (See Ids 6 and 8). So I get 2 rows for one holiday.

Anyone an idea to join these rows with a query and no stored function? I'm looking for this result:

ID  HOLIDAYCOMMENT     DATE_FROM     DATE_TO      DAYS
------------------------------------------------------
1   Trip to Berlin     17.08.2015    20.08.2015   4
5   Stay at home       21.08.2015    NULL         1
6   Trip to London     03.09.2015    08.09.2015   6
10  NULL               17.09.2015    NULL         1

This is my attempt at solving this "groups and islands" problem, with comments in-line:

WITH GroupStart AS
(
    -- Identify the start of each new group
    SELECT
        TD.id, TD.holidaydate, TD.holidaycomment,
            -- New group if the holidaycomment has changed
            -- (in holidaydate order)
            CASE WHEN holidaycomment =
                    LAG(TD.holidaycomment) OVER (
                        ORDER BY TD.holidaydate)
                THEN 0
                ELSE 1
            END AS gs
    FROM test_dates TD
),
Islands AS
(
    -- The island identifier is the number
    -- of new groups encountered so far
    SELECT
        id, holidaydate, holidaycomment,
        SUM(GS.gs) OVER (ORDER BY GS.holidaydate) AS grp
    FROM GroupStart GS
)
-- Results are now a pretty trivial aggregation
SELECT
    MIN(I.id) AS id -- Lowest id in group,
    MIN(I.holidaycomment) AS holidaycomment -- All the same per group,
    MIN(I.holidaydate) AS date_from -- First date in group,
    NULLIF(MAX(I.holidaydate), MIN(I.holidaydate)) AS date_to,
    1 + MAX(I.holidaydate) - MIN(I.holidaydate) AS days
FROM islands I
GROUP BY
    I.grp
ORDER BY
    I.grp,
    date_from;

SQLFiddle

I wrote this as my first Oracle query for about 20 years. The translation above is from my native SQL Server.


How to union four different tables I have two tables whereby both of them use sub queries. These two tables are combined by using UNION ALL.
I have two other tables that only uses a WHERE clause and both of these are combined using UNION ALL.
I am struggling to join all 4 tables as I keep getting below error when I use UNION ALL and UNION.

  Msg 205, Level 16, State 1, Line 1
  All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

It means the number of columns you select in the top query must be the same as the number of columns in the second. If you don't have the same number, you can work around it. Below I can add a NULL to the second query because it's missing a third column.

SELECT col1,col2,col3
FROM t1
UNION ALL
SELECT col1,col2, NULL
FROM t2


Auto Increment Index Field For Create and Update I'm trying to create an unique auto increment index field that is re-generated on both inserts and updates. I need it so that a client can send a server an integer and get back all outdated (created or updated) records. Something that would allow:

Insert Record A (Record A 'touched' set to '1')
Insert Record B (Record B 'touched' set to '2')
Update Record A (Record A 'touched' set to '3')
Update Record B (Record B 'touched' set to '4')

I'm using both Postgresql and Sqlite (production and development) and would ideally like a solution that will work under both. What is the best way to add something like this?

Thanks!

You need to combine a sequence with some mechanism to trigger the increment for an update. For postgres

create table my_table(id serial primary key, description text, touched serial);

Then either:

create a trigger to set touched = default on an update
or
get all your updates going through an API that also actions the increment

auto-increment is implemented differently for SQLite so you will have to work around that - perhaps by inserting/deleting a new row each time you update a row, and using the rowid of that temporary row to update touched.


Insert into table where boolean value in another table is true I want to insert a value into a table, but only if a boolean value in another table is true.

I currently do this using two separate SQL commands, testing the boolean value in PHP and then deciding to insert the value or not in the other table.
Is it possible to do this in a single SQL statement which should be much quicker than my two statement process. (In the example code below, I have hard coded my values, but they come from php variables in the actual code).

SELECT enabled FROM mydb.sensors WHERE pk=10;

In PHP I run the above query, then I test if enabled is true. If true, then I store the sensor data value into another table.

INSERT INTO mydb.sensordata(sensorkey,sensorvalue) values (10, 25.4);

What I am after is something like the following, but not sure how to implement it.

INSERT INTO mydb.sensordata(sensorkey,sensorvalue) values (10, 25.4) WHERE mydb.sensors.pk = 10 AND mydb.sensors.enabled = TRUE;

Any guidance is appreciated.

One possible solution ...

Setup:

create table sensors(pk int, enabled bit);

insert into sensors values (10,1);

create table sensordata(sensorkey int, sensorvalue float);

Insert if enabled is true:

insert into sensordata (sensorkey,sensorvalue)
select 10,25.4 from dual
where exists(select 1 from sensors where pk = 10 and enabled);

NOTE: from dual required as a dummy table for select to function properly.

Results:

select * from sensors;

 pk      enabled
 ------- -------
      10 true

select * from sensordata;

 sensorkey sensorvalue
 --------- -----------
        10        25.4

sqlfiddle


Add the CASE expression to the GROUP BY clause.

SELECT   PROJECT_ID,
         CASE WHEN SOURCE_NAME = 'A' THEN 'A'
              WHEN SOURCE_NAME = 'B' THEN 'B'
              ELSE 'OTHER'
         END SOURCE_NAME,
         SUM(AMOUNT) AMOUNT,
         YEAR
FROM     FINANCE_SOURCE
GROUP BY PROJECT_ID,
         CASE WHEN SOURCE_NAME = 'A' THEN 'A'
              WHEN SOURCE_NAME = 'B' THEN 'B'
              ELSE 'OTHER'
         END,
         YEAR


Unable to apply patch in Oracle Grid Home I need to set up a RAC environment using two nodes. Details of each server are:

OS : Windows Server 2012 R2 64bit ( certified by Oracle )

RAC: Oracle Grid 11.2.0.4

While installing the Grid Software, I am getting following error:

I came to know from Oracle Metalink, this is bug and had been solved in patch 20502905. I tried to apply this patch using the latest opatch 11.2.0.3.12 (required 11.2.0.3.5 or later) but I am unable to apply this patch because I am getting this error:

Since RAC installation is not completed yet that's why database has not been installed. As I know to use opatch we need to set ORACLE_HOME to database home and need to set OPatch directory path in the PATH variable of OS. But I can not set the path of ORACLE_HOME.

Could you please suggest me how to proceed. Any help is appreciated.

Thanks all for your help. Here is the solution:

Do standalone installation of Grid Software on each server one by one. (Don't configure Grid now.)
Download the required patch.
Unzip the patch in temp folder and then copy and paste it to Oracle GRID_HOME\OPatch directory. Don't delete any file, if asked click on Merge.
Set the ORACLE_HOME=GRID_HOME and then after set the path of OPatch in PATH environment.
Now apply the patch by following the steps provided in README file along with patch.
Once it is done, Start the Grid configuration , hopefully you won't face any issue.


Matching string with LIKE derived from sub-query Here is my query Where I want to match a string with a subquery output. But it is returning wrong result. What I am doing wrong? The concat part is not working as I try to run

Concat('"', (SELECT sec_dep
                                  FROM   admin_users
                                  WHERE  uid = 11), '"')

this part only. it shows error.

SELECT *
FROM   issues
WHERE  ( assigned_to_dept LIKE Concat('"', (SELECT sec_dep
                              FROM   admin_users
                              WHERE  uid = 11), '"')
              OR assigned_to_dept LIKE Concat('"%,', (SELECT sec_dep
                                FROM   admin_users
                                WHERE  uid = 11), '"')
              OR assigned_to_dept LIKE Concat('"', (SELECT sec_dep
                              FROM   admin_users
                              WHERE  uid = 11), ',%"')
              OR assigned_to_dept LIKE Concat('"%,', (SELECT sec_dep
                                FROM   admin_users
                                WHERE  uid = 11), ',%"') )

thanx in advance.

UPDATE

admin_users Table

CREATE TABLE `admin_users` (
 `uid` int(11) NOT NULL AUTO_INCREMENT,
 `pri_dep` int(11) NOT NULL,
 `sec_dep` text NOT NULL,
 PRIMARY KEY (`uid`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

issue table

CREATE TABLE `issues` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `title` varchar(250) NOT NULL,
 `description` text,
 `assigned_by` int(11) NOT NULL,
 `assigned_to_users` text,
 `assigned_to_dept` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8


Elegantly handling 'enum-like' magic numbers in T-SQL code We run Dynamics GP, and anybody that's dealt with GP will be familiar with all of its "magic number" columns representing the various enum values inside the application. For example, a reporting query might look something like this:

...
WHERE sod.SOPTYPE = 2
    AND iv.VCTNMTHD = 3
    AND pod.POLNESTA IN (2, 3)

...which is about as far from self-documenting as possible.

At first I had the bright idea of creating a table called 'Enums', filling it with values, and making a scalar wrapper function so you could query like this:

...
WHERE sod.SOPTYPE = Enum('Sales Doc Type', 'Order')
    AND iv.VCTNMTHD = Enum('Valuation Method', 'Average Perpetual')
    AND pod.POLNESTA IN (Enum('PO Line State', 'Released'), Enum('PO Line State', 'Change Order'))

Of course, that sucked, because the optimizer couldn't see the underlying 'constant' value, and thus made some rather poor choices since it had to make cardinality guesses.

So it needs to be an actual constant value in the code, but I'm not sure if there are any nice features built into SSMS that would make this sort of thing easy. Could I (mis)use code snippets or templates in some way? If we could do some kind of lookup/insertion to make our code look something like this, that would be awesome:

...
WHERE sod.SOPTYPE = /*Order*/2
    AND iv.VCTNMTHD = /*Average Perpetual*/3
    AND pod.POLNESTA IN (/*Released*/2, /*Change Order*/3)

I'm also not opposed to checking out SSMS add-ins.

I would actually recommend against tying this lookup in any way to SSMS / SQLCMD -specific features. Doing so would require that the code could only ever be executed using just those two programs. You wouldn't even be able to put any such logic into a stored procedure, even if it was executed from SSMS or SQLCMD. You also can't use SQLCMD commands in SSRS, etc.

Some options (that are completely independent of the client tool used to execute them):

If SQL Server 2019 is an option, then it's possible that the new Scalar UDF Inlining feature might resolve the issue with your Enum('Sales Doc Type', 'Order') approach.

It's possible that an inline TVF might do the trick. You just might need to restructure those predicates in the WHERE clause to be INNER JOINs:

FROM  SOPxxxxx sod
INNER JOIN dbo.Enum('Sales Doc Type', 'Order') sdt
        ON sod.SOPTYPE = sdt.Value

Of course, the IN list is a little trickier as I think that might require a LEFT JOIN on each option and then a WHERE predicate to ensure that both of those did not result in a NULL:

FROM  dbo.POP10110 pod
LEFT JOIN dbo.Enum('PO Line State', 'Released') pls_r
        ON pod.POLNESTA = pls_r.Value
LEFT JOIN dbo.Enum('PO Line State', 'Change Order') pls_co
        ON pod.POLNESTA = pls_co.Value
WHERE  ( pls_r.Value IS NOT NULL
     OR  pls_co.Value IS NOT NULL)

OR, using this same iTVF, you might be able to do non-correlated subqueries that might be more readable / manageable:

FROM  SOPxxxxx sod
WHERE sod.SOPTYPE = (SELECT sdt.Value FROM dbo.Enum('Sales Doc Type', 'Order') sdt)

And the IN list is at least looks better:

FROM  dbo.POP10110 pod
WHERE pod.POLNESTA IN (
                       SELECT pls_r.Value
                       FROM   dbo.Enum('PO Line State', 'Released') pls_r
                       UNION ALL
                       SELECT pls_co.Value
                       FROM   dbo.Enum('PO Line State', 'Change Order') pls_co
                      )

Or perhaps (not sure what the query optimizer will prefer):

FROM  dbo.POP10110 pod
WHERE pod.POLNESTA IN (
           (SELECT pls_r.Value FROM dbo.Enum('PO Line State', 'Released') pls_r),
           (SELECT pls_co.Value FROM dbo.Enum('PO Line State', 'Change Order') pls_co)
                      )

If SQLCLR is an option, then SQLCLR scalar UDFs that do not do any data access and are marked as [SqlFunction(IsDeterministic = true)] are constant folded into the execution plan (the "Query Processing Architecture Guide" documentation currently states that SQLCLR functions cannot be folded, but that is provably wrong, so I suspect that the documentation was merely not updated when SQL Server 2012 added that ability; I have submitted a documentation update to fix that: Fix "Constant Folding" info in "Query Processing Architecture Guide").

The trick here is to read the data from the table without doing so in the SQLCLR scalar UDF so as to not prevent it from being foldable. In order to accomplish this, do the following:

Create a static readonly dictionary at the class level.
Create a static constructor for the class that uses a regular, external SqlConnection to connect to the local instance. This will select from the table and populate the static dictionary. This constructor will ensure that the dictionary is always populated, even if the assembly gets unloaded due to memory pressure, DBCC FREESYSTEMCACHE, etc. Unfortunately, the internal "context" connection cannot be used here since there is no Sql Context when the static constructor is executed (but would be great if there was!).
Mark the assembly as WITH PERMISSION_SET = EXTERNAL_ACCESS. No need to specify UNSAFE because the dictionary is "readonly". And even though it is "readonly", it can still have items added and/or removed from it. This should not pose any problems as it's a single use collection that is always the same for all users.
Decorate the static method that is exposed to T-SQL to do the lookup with:

[SqlFunction(IsDeterministic = true)]

No need to specify DataAccess or SystemDataAccess properties as those are set to None by default.

This method will allow you to implement the Enum() approach that you had started doing. And, since you are working with a 3rd party app, you can keep things "clean" by installing the assembly into a separate database (and same would go for report procs, etc, I suppose). I have tested and verified that it still does the constant folding.

OR (this is really option 3b): Given that this is a 3rd party app, IF it is safe to assume that the lookup / enum values won't be changing much, if ever, then you don't really need to read them from a table. You can simple have the entire dictionary hard-coded in the .NET code. In this approach, you could then keep the assembly marked as PERMISSION_SET = SAFE as there would be no need for that external SqlConnection back to the current instance.


SSIS Package truncation and possible data loss (error code: 0xC020901C) Good morning all,
It's been 2 days now trying to figure out how to solve this issue, still no answer. Maybe you can help me out.
Here is the thing. I have created an SSIS package to import data from another database server; here are the specifications of both servers:
I am using Sql Server Data Tools 2012

Source: SQL Server 2008 r2
Connection Type: OLE DB

Destination: SQL Server 2012
Connection Type: OLE DB

The package is an Import and Export package, having 2 tasks, a "Data FLow Task" and an "Execute SQL Task". The source is an SQL Command, with a simple select from a couple of joins, nothing that special. the Destination is Table in the destination server. Of course we have a Data Conversion task in between.
The package was working fine until we have added a new column to the Selection, of type varchar(50). I modified the destination Table with same, only i used NVARCHAR(50), and added the data conversion as UNICODE string [DT_WSTR] of length 50.

After this was done, I am receiving the error:

  Error: 0xC020901C at [Package], Source - Query [127]: There was an
  error with Source
  - Query.Outputs[OLE DB Source Output].Columns[COL] on Source
  - Query.Outputs[OLE DB Source Output]. The column status returned was:  "The value could not be converted because of a potential loss of
  data.".

I have tried to increase the sizes of the destination columns from 50 to 100, still the same error is happening. Weird thing is when I replace Col with NULL in the source query, I get the package to run perfectly.

Any suggestions?

Okay I found the solution after searching and testing many things.
The issue was in the Metadata (the connection between the Source Query and the Data Conversion), the length of the field was not matching the one from the source query anymore, which is to me, very odd as I thought that the Metadata would be automatically refreshed when you save and compile, or when you close the solution and reopen it.

I don't know why this occurred, but now the package is running again perfectly.


Unable to take backup of mysql database using Xtrabackup I am trying to take mysql backup without any downtime but unable to take the backup using Xtrabackup .

Actual Database is almost about 40+ GB in size. I am trying to first satisfy myself. Hence, I have created a sample database on a server with 5000 Tables (all InnoDB tables).
As per the blog "there are some cases when you can get table entirely missing from the backup if you use this technique "  "--single-transaction  are not safe to use on production environment.

[root@localhost mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.5.25a-log MySQL Community Server (GPL) by Remi
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| innodbtest         |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)
mysql> use innodbtest;
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> show create table T4000;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| T4000 | CREATE TABLE `T4000` (
  `A` int(11) NOT NULL,
  `B` int(11) DEFAULT NULL,
  `C` char(1) DEFAULT NULL,
  PRIMARY KEY (`A`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table T3040;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| T3040 | CREATE TABLE `T3040` (
  `A` int(11) NOT NULL,
  `B` int(11) DEFAULT NULL,
  `C` char(1) DEFAULT NULL,
  PRIMARY KEY (`A`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

I used tool xtrabackup and followed the steps to create a backup

xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/opt/bkp/

After taking the backup I am not getting the ".ibd" files at the backup location.

[root@localhost mysql]# ls -al /opt/bkp/
total 190676
drwxr-xr-x 2 root root      4096 Nov 27 19:02 .
drwxr-xr-x 5 root root      4096 Nov 27 18:08 ..
-rw-r----- 1 root root 195035136 Nov 27 19:02 ibdata1
-rw-r----- 1 root root        79 Nov 27 19:02 xtrabackup_checkpoints
-rw-r----- 1 root root      2560 Nov 27 19:02 xtrabackup_logfile
[root@localhost mysql]#

After restoring the database using xtrabackup, I am not getting the database folder "innodbtest" on the other server too.

Where am I wrong?

Visit below mansion urls and hope these references will help you.

http://johanandersson.blogspot.in/2012/10/hotbackup-of-galera-cluster-for-mysql.html

http://www.percona.com/doc/percona-xtrabackup/howtos/setting_up_replication.html


Need to find the number of employee who are not in all department i have a table T1 which has two columns empname , department

every emp must be in 37 department , but there are some employee who are in less than 37 department .

I need to find the names of emp who are not in all 37 departments.

i'm using a query :

select empname from T1 having count (department) < 37;

but it is not giving me the desired output.

Unless you are e.g. counting or summing the entire contents of a table, aggregate functions will require a group by statement. For example, if you are aggregating by counting the number of departments by employee, you must tell it to group by employee

select empname, count(*)
from t1
group by empname
having count(*) < 37


Is there any difference between these two version of MySQL - 5.1.73-community-log Vs 5.1.73-community I have installed MySQL 5.1.73 in Oracle LINUX 5 server. Just after RPM installation, I checked system variables and found that version is 5.1.73-community.
When I copid  /usr/share/doc/MySQL-server-community-5.1.73/my-medium.cnf to /etc/my.cnf
I got 5.1.73-community-log as value of version variable.
I am curious to know about what is the difference between
5.1.73-community and 5.1.73-community-log

They are the same software. The -log suffix is simply appended to the version string at runtime.
Here's an explanation (nearly from the last century), by long-time MySQL author Paul DuBois:

I want to know what the -log means and how it got in there.

-log is added if you start the server with logging turned on.
-debug is added if you're running a debugging version of the server with debugging on.
-demo is added if the server is running in demo mode

I verified this in the source, in sql/mysqld.cc, function set_server_version():
. . .
if (opt_log || opt_slow_log || opt_bin_log)
  strmov(end, "-log");                        // This may slow down system
. . .

I think the meaning of that comment is not that appending four extra characters to the version slows down the system, but that logging may slow down the system, so it's worth noting to the DBA that logging is enabled.


Store table names to reference other tables I'm designing a webapp where users can add widgets to their pages. Right now I have only two types of widgets that are different in nature, so they have different attributes, but both of them are used to sell products. I'm expecting to have other widgets implemented in the future. The users have the option of adding one or both of them to their pages, and I have to keep record of what widgets the users add. Also, for each product sold, I have to generate and store a receipt of the financial transaction. Since every sale is the same regardless of which widgets it comes from, I'd rather have only one Sales table for storing them (instead of one table for each widget type, eg: Widget1Sales, Widget2Sales, etc). And since there may be other widgets developed, I prefer not to make a sparse Widget table with columns for the attributes of all widgets.

So far I came up with the following design:

So for each widget the user add to his/her page, I have to make two queries: one to retrieve the specific widget table name and another (possibly a dynamic query) to get the widget info.The same goes for the products: for each product sold for any given widget I have to make a query to retrieve the specific product table name, and another to get the product info. This arrangement seems to work, but storing table names in another table sound just plain wrong to me... Is there any other way I could implement this logic? I've considered implementing some sort of class table inheritance, but I can't seem to envision how I could go about this in that specific use case.

Storing table names in other (user) tables is an instance of storing structural metadata and user data side by side.  This has a variety of interesting effects, and some of those effects make it attractive to designers.  The down side is less apparent.

The down side is this:  you end up doing a lot of data management "by hand" in the SQL that a different design might have allowed the DBMS to do for you.  This makes your code harder to maintain and slower to run.

Having said that, I'll admit to having pulled this stunt several times, and the results were usually good enough so that I didn't regret the choice.

There's a second thing going on in your case.  Types 1 and 2 of both widgets and products are cases of class/subclass modeling (or, if you prefer type/subtype modeling).  This kind of thing is simple and straightforward in object modeling, because inheritance takes care of most of the difficulties for you.  Not so in relational modeling.  Relational modeling, as such, has no mechanism for inheritance.  Some variants of SQL have extensions that make inheritance easier to model and to implement.

There are many case of questions here in the DBA area that boil down to the question of how to implement subclasses (or subtypes) in SQL tables.  Some of those questions are grouped under this tag:  subtypes.  Over in StackOverflow, there are even more such questions, and there are three tags that relate to three design techniques that might help:  single-table-inheritance, class-table-inheritance, and shared-primary-key.

Your design resembles a class table inheritance design, except that you use embedded table names instead of shared primary key to implement the IS-A relationships between subclasses and classes.  You might want to explore using shared primary key, and then creating views that collect all the data for each subclass by joining the superclass table with each subclass.  I'm not sure you want to go this way.  It could get awfully unwieldy once you have hundreds of different product types.


Snapshot Replication where Publisher is SQL Server 2008 and Subscriber is SQL Server 2019 not working When we are performing Snapshot replication where Publisher is SQL Server 2008 and Subscriber is SQL Server 2019. We are receiving below mentioned errors.

Error messages: The process could not bulk copy into table '"dbo"."Car"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037) Get help: http://help/MSSQL_REPL20037 metadata query failed Invalid character value for cast specification (Source: MSSQLServer, Error number: 22018) Get help: http://help/22018 Unspecified error

When we change compatability level of subscriber database to 120 then snapshot replication is working successfully.

Is there anyway by which we can perform snapshot replication successfully without changing the compatability level?.

The replication version compatibility rules are documented here: Replication Backward Compatibility (snapshot falls under Transactional Replication).

SQL Server 2008 is out of support, and replication between SQL 2008 and SQL 2019 is unsupported. So probably not.