trigger can't access column loop SSCCE:
The following script:

$ cat test.sql
CREATE TABLE public.foo (
loop INTEGER
);

CREATE OR REPLACE FUNCTION public.foo_fun(loop INTEGER) RETURNS BOOLEAN AS $$
    SELECT TRUE;
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION public.foo_tr_fun() RETURNS TRIGGER AS $$
DECLARE
    fRV BOOLEAN;
BEGIN
    SELECT public.foo_fun(NEW.loop) INTO fRV;
IF fRV THEN
    RETURN NEW;
ELSE
    RAISE EXCEPTION 'bar';
END IF;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER foo_tr AFTER INSERT OR UPDATE ON public.foo
FOR EACH ROW
EXECUTE PROCEDURE public.foo_tr_fun();

... which I run with:

psql -v ON_ERROR_STOP=1 --quiet -X -U some-user -d some-db -f test.sql

... when I then try to insert a row into foo (from psql):

$ psql -U some-user some-db
psql (9.1.14)
Type "help" for help.

RegTAP=> SELECT * FROM public.foo;
 loop
------
(0 rows)

RegTAP=> INSERT INTO public.foo(loop) VALUES(0);
ERROR:  record "new" has no field "loop"
LINE 1: SELECT public.foo_fun(NEW.loop)
                              ^
QUERY:  SELECT public.foo_fun(NEW.loop)
CONTEXT:  PL/pgSQL function "foo_tr_fun" line 5 at SQL statement
RegTAP=>

As it turns out, the key word loop needs to be double-quoted inside PL/pgSQL or the parser is confused:

SELECT public.foo_fun(NEW."loop") INTO fRV;

The situation in Postgres 9.3 is unchanged:
SQL fiddle with quotes (works).
SQL fiddle without quotes (doesn't work).

It's generally a good idea to avoid typical key words like loop as identifiers. There are some plpgsql key words in addition to SQL key words (like for procedural elements or exception handling), but I don't know of a comprehensive list of additional plpgsql key words. The source code would be my best idea.

I found a related discussion on pgsql-hackers from 2007. It seems the matter is not fully resolved, yet.
Didn't see any changes here for Postgres 9.4 either ...


How to UPDATE by COUNT from JOINed tables I have a schema as

CREATE TABLE companies
(
company_id int(11) NOT NULL AUTO_INCREMENT,
company varchar(255),
projects int(11),
PRIMARY KEY(company_id)
) ENGINE=InnoDB

CREATE TABLE employees
(
employee_id int(11) NOT NULL AUTO_INCREMENT,
company_id int(11),
name varchar(255),
PRIMARY KEY(employee_id)
) ENGINE=InnoDB

CREATE TABLE project_map
(
employee_id int(11),
project_id int(11),
PRIMARY KEY(employee_id,project_id)
) ENGINE=InnoDB

I need to UPDATE column projects in table company, by counting all projects done by employees of each company.

UPDATE companies
SET project = count of project_map by employees of each company

I did manage to do this through SQL. However, I think that this is a task that is far better better suited for TRIGGERs - i.e. if a record insertion/deletion is made on the project_map table, then a TRIGGER should fire adding +/- 1 to the relevant company's project field. I gave this a +1 because it was trickier than it seems initially.

Giving the DDL was good, but it would have been even better if you had also provided DML (which I had to create myself for testing). I've put my DDL + DML at the bottom of this post.

The SQL is:

UPDATE companies c
JOIN
(
  SELECT cc.company_id AS cid, SUM(cc.pcount) AS pcount
  FROM
  (
    SELECT e.company_id, ep.eid, ep.pcount
    FROM employees e
    INNER JOIN
    (
      SELECT e.employee_id AS eid, COUNT(p.employee_id) AS pcount
      FROM project_map p
      INNER JOIN
        employees e
        ON p.employee_id = e.employee_id
      GROUP BY eid
      ORDER BY eid
    ) AS ep
    ON e.employee_id = ep.eid
  ) AS cc
  GROUP BY cid
  ORDER BY cid
) AS b
ON c.company_id = b.cid
SET c.projects = b.pcount;

The many derived tables in the above query are not necessary but they can serve as an illustration of the step by step process of how the query was developed. The actual query can be as simple as this:

UPDATE companies c
JOIN
(
  SELECT e.company_id AS cid, COUNT(p.employee_id) AS pcount
  FROM project_map p
  INNER JOIN
    employees e
    ON p.employee_id = e.employee_id
  GROUP BY cid
) AS b
ON c.company_id = b.cid
SET c.projects = b.pcount;

And the result in either case is:

company_id, company,  projects
1,          companyA,        4
2,          companyB,        4
3,          companyC,        1
4,          companyD,        0

It is relatively easy to see from the DML (below) that this is the correct result.

-------------------- DDL and DML ----------------------

DML

This is a minimal "implementation" - of course, one would have to create PRIMARY KEYs, FOREIGN KEYs, NOT NULLs, UNIQUE KEYs and just plain old KEYs.

CREATE TABLE companies
(
company_id INTEGER,
company VARCHAR(255),
projects INTEGER
);

CREATE TABLE employees
(
employee_id INTEGER,
company_id INTEGER,
name VARCHAR(255)
);

CREATE TABLE project_map
(
employee_id INTEGER,
project_id INTEGER
);

And the DML for the test data:

INSERT INTO  companies (company_id, company, projects) VALUES (1, 'companyA', 0);
INSERT INTO  companies (company_id, company, projects) VALUES (2, 'companyB', 0);
INSERT INTO  companies (company_id, company, projects) VALUES (3, 'companyC', 0);
INSERT INTO  companies (company_id, company, projects) VALUES (4, 'companyD', 0);

INSERT INTO employees (employee_id, company_id, name) VALUES (1, 1, 'emp1_1');
INSERT INTO employees (employee_id, company_id, name) VALUES (2, 1, 'emp1_2');
INSERT INTO employees (employee_id, company_id, name) VALUES (3, 2, 'emp2_1');
INSERT INTO employees (employee_id, company_id, name) VALUES (4, 2, 'emp2_2');
INSERT INTO employees (employee_id, company_id, name) VALUES (5, 3, 'emp3_1');
INSERT INTO employees (employee_id, company_id, name) VALUES (6, 3, 'emp3_2');
INSERT INTO employees (employee_id, company_id, name) VALUES (7, 5, 'emp5_1');

INSERT INTO project_map VALUES(1, 15);
INSERT INTO project_map VALUES(1, 16);
INSERT INTO project_map VALUES(1, 17);
INSERT INTO project_map VALUES(2, 26);
INSERT INTO project_map VALUES(3, 35);
INSERT INTO project_map VALUES(3, 36);
INSERT INTO project_map VALUES(3, 37);
INSERT INTO project_map VALUES(3, 38);
INSERT INTO project_map VALUES(5, 55);


Partitioning in PostgreSQL 10 Currently we are using pg_partman for partitioning our tables in PostgreSQL 9.4. We are planning on upgrading to PostgreSQL 10. I read that PostgreSQL 10 has better support for partitioning.

While I read the documentation on it, is there a standard process to automate creation of the partitions? Do we still need to use pg_partman to automate creation of the partitions or is there another way?

I am not a PostgreSQL DBA but I need to maintain the database so I am looking for any standard practices for PostgreSQL partitioning.

I just wanted to share how we dealt with this - in case this helps others. Based on suggestions from some DBAs in Postgres User Groups, I created a cron job that executes twice a month. We use daily partitions. So the cron job executes a bash script. The bash script connects to the database using psql and calls a function which takes in the table that needs to be partitioned and the date interval to create the partitions for. Here is the function I used to create partitions. This has worked without any issues for us.

CREATE OR REPLACE FUNCTION public.create_partition_tables(p_parent_table text, p_partition_start_date date, p_partition_end_date date)
RETURNS void AS
    $body$
    DECLARE
        partition_date date;
        v_tablename text;
    BEGIN
        for partition_date in
            select date(generate_series(p_partition_start_date, p_partition_end_date, interval '1 day'))
        loop

            SELECT tablename INTO v_tablename FROM pg_catalog.pg_tables WHERE schemaname='public' and tablename = p_parent_table || '_p' ||to_char(partition_date, 'yyyy_mm_dd');
            IF v_tablename IS NOT NULL THEN
                CONTINUE;
            END IF;

            execute format('create table public.' || p_parent_table || '_p' ||to_char(partition_date, 'yyyy_mm_dd')|| ' partition of public.'
            || p_parent_table || ' for values from (%L) to (%L)', partition_date, date(partition_date + interval '1 day') );
        end loop;
    END;
    $body$
LANGUAGE 'plpgsql' VOLATILE STRICT


What is a base64 RAW? How do I use it? Oracle has a function for encoding a binary value, specifically a RAW type, into base64: BASE64_ENCODE. This makes sense. I want to turn binary into something I can more easily port across systems.

However, in addition to accepting a binary RAW for its input, it also returns a binary RAW for its output. I find this extremely confusing, as the purpose of base64 is to encode the bytes in a text format.

So what does this output RAW actually contain? What is the proper way to convert it into an actual text type (e.g., VARCHAR2)?

I additionally assume that BASE64_DECODE expects its input to be in the same format as the output of BASE64_ENCODE, but please note if that's not the case.

I'm specifically using Oracle 11.2, but I doubt this has changed since it was introduced. Feel free to correct my if I'm wrong on that.

It order to understand your problem and to simulate it I have generated one scenario. First of all I would like to tell how the Base64 works as general.
1) It takes the ASCII text.
2) Converts it to Hexadecimal values(Decimal to Hex conversion).
3) Represents the text in bits(groups as byte).
4) Since the Base64 takes three bytes of data. That is, it takes 24 bits of data and returns as 32 bit encoded characters. So bit should be represented as 6 bits string.
5) Then these 6 bits string will be converted to decimal number.
6) Then it selects the ascii characters(A-Z,a-z,0-1,+ and /) according to the decimal number calculated in step 4.

Now i want to show this practically and also want to shows how the Oracle's Base64 encoding returns RAW value.
Lets say we want to encode the text 'ABCD'.
1) Lets convert this to RAW data which is hexadecimal values.

SQL> SELECT UTL_RAW.CAST_TO_RAW('ABCD') FROM dual;
41424344

We have got the hex values of 'ABCD' that is '41424344'.
2) Lets encode this hex using Base64. In Oracle we have got UTL_ENCODE package to encode it, which takes RAW and returns encoded value as RAW.

SQL> SELECT UTL_ENCODE.BASE64_ENCODE('41424344') FROM dual;
51554A4452413D3D
Let me explain how we have got this values according the encoding steps that i have mentioned above.
1) Our text is 'ABCD'.
2) Hex representation is '41424344'.
3) Binary representation of text grouped by bytes.

41->01000001 42->01000010 43->01000011 44->01000100

4) Bit representation of text grouped by 6 bits.

010000 010100  001001  000011  010001  000000

(Adds '=' sign to output text as we added 0s to make it 6bits)
5) Decimal representation of 6 bits representation of text.

010000->16  010100->20  001001->9  000011->3  010001->17  000000->0

6) Now lets select from base64 characters(A-Z,a-z,0-9,+,/  altogether 64 characters:0-63) according to the decimal values.

16->Q, 20->U, 9->J,3->D,17->R,0->A

Output:QUJDRA==
But Oracle Base64 encoding returns this text as RAW(Hex Values). In order to get encoded string as text instead of RAW we can cast it to VARCHAR2.

SQL>SELECT UTL_RAW.CAST_TO_VARCHAR2('51554A4452413D3D') FROM dual;
QUJDRA==

If we convert the ASCII value of these text into hex we get the encoded string returned by Base64 encoding.

Character  ASCII  Hex
Q          81       51
U          85       55
J          74       4A
D          68       44
R          82       52
A          65       41
=          61       3D
=          61       3D

Now lets convert this text to hex.

SQL>SELECT UTL_RAW.CAST_TO_RAW('QUJDRA==') FROM dual;
51554A4452413D3D

We get the original encoded string

51554A4452413D3D

Now in order to get my text back to original text 'ABCD', lets decode it.

SQL>SELECT UTL_ENCODE.BASE64_DECODE('51554A4452413D3D') FROM dual;
41424344
SQL>SELECT UTL_RAW.CAST_TO_VARCHAR2('41424344') FROM dual;
ABCD

Conclusion: So if we want to get encoded value as text instead of Hex we can use UTL_RAW.CAST_TO_VARCHAR2 which simply converts Hex to varchar2.


The following approach will only work as expected if we can safely assume that rows with the same housename will have the same houseno where houseno is populated.

As the first step, you need to get the list of all distinct housename/houseno pairs where houseno has a value. If my assumption above is correct, you will get a result set where all housename values are unique, and each will have a corresponding houseno – a kind of reference table:

SELECT DISTINCT
  housename,
  houseno
FROM
  YourTable
WHERE
  houseno <> ''

Use the above as a derived table to join it back to the original table in the UPDATE statement, so that you can populate the missing values from the derived table:

UPDATE
  YourTable AS t
  INNER JOIN
  (
    SELECT DISTINCT
      housename,
      houseno
    FROM
      YourTable
    WHERE
      houseno <> ''
  ) AS ref ON t.housename = ref.housename
SET
  t.houseno = ref.houseno
WHERE
  t.houseno = '' OR t.houseno IS NULL
;

Note that rather than "fixing" your table like this, it would be better to normalise it by creating an actual reference table with columns housename and houseno that you would then join back to your data table whenever you need to retrieve houseno. Consequently, the houseno column in the original table would no longer be needed.


Should I duplicate columns between tables to speed-up aggregations like SUM? I have two tables in PostgreSQL 10.12 database:

waste_card (id (PK), user_id, manufacture_date, transfer_date, address_id, card_type, .. (and other card_specific_firelds)
wastes (user_id, waste_card_id(FK), amount, ... (other wastes_specific_fields)

I need to frequently list and count user wastes (2 separated API endpoints) by address and manufacture_date or transfer_date
Now to list items I fire 2 queries: 1- load user cards then 2- load wastes
SELECT waste_cards.*
FROM   waste_cards
WHERE  waste_cards.user_id = $1
       AND (waste_cards.manufacture_date < '$2')
       AND (waste_cards.address_id = $3)
LIMIT $4

SELECT wastes.*
FROM   wastes
WHERE  wastes.waste_card_id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)

To just counting amount I fire:
SELECT SUM(wastes.amount)
FROM   waste_cards
LEFT   OUTER JOIN wastes
       ON wastes.waste_card_id = waste_cards.id
WHERE  waste_cards.user_id = $1
       AND (waste_cards.manufacture_date < '$2')
       AND (waste_cards.address_id = $3)

Should I add [manufacture_date, transfer_date, address_id] fields to wastes to speed up queries to be like:
SELECT SUM(amount)
FROM   wastes
WHERE  user_id = $1
       AND address_id = $2
       AND manufacture_date < '$3'

I'm on very beginning with this system. Queries are less than 150 ms. Today its about 1K users with 100-200 cards each and 2-5 wastes on each card. Not much yet but we want to add other 10-15K users next month. So I want to ask if this db schema is correct or should I change it before go live on production.

DB design
Seems like a 1:n relationship between waste_card and wastes. Actual table definitions (CREATE TABLE statements) would clarify.
Do not repeat (duplicate) the columns user_id, manufacture_date, and address_id in the many-table wastes. That would bloat your table, introduce problems with maintenance, raise the question which is the true source of information, blatantly defy normalization etc.
Queries
While computing a total sum, just use a plain [INNER] JOIN. Wastecards with no related entries in waste don't change the result and can be excluded:
SELECT SUM(w.amount) AS total_waste
FROM   waste_card wc
JOIN   wastes     w  ON w.waste_card_id = wc.id
WHERE  wc.user_id = $1
AND    wc.manufacture_date < $2
AND    wc.address_id = $3;

LEFT JOIN makes sense to ...

list the sum per entry in waste_card
and include cards with no entries in waste

But then it's typically better to use a LATERAL subquery computing the sum per card. The aggregate function guarantees exactly one row from the subquery so we can just as well switch to CROSS JOIN LATERAL (like Andriy pointed out):
SELECT wc.*  -- or better just the columns you need
     , w.sum_waste
FROM   waste_card wc
CROSS  JOIN LATERAL (
   SELECT SUM(w.amount) AS sum_waste
   FROM   wastes w
   WHERE  w.waste_card_id = wc.id
   ) w
WHERE  wc.user_id = $1
AND    wc.manufacture_date < $2
AND    wc.address_id = $3;

See:

JOIN (SELECT … ) ue ON 1=1?

Fastest way to count how many dates' ranges cover each date from series

Convert right side of join of many to many into array

Covering index
To speed up either query, an index "covering" wastes.amount might pay (requires Postgres 11 or later):
CREATE INDEX your_idx_name ON wastes(waste_card_id) INCLUDE (amount);

For Postgres 10 or older, fall back to a multicolumn index:
CREATE INDEX your_idx_name ON wastes(waste_card_id, amount);

See:

Does a query with a primary key and foreign keys run faster than a query with just primary keys?

Do covering indexes in PostgreSQL help JOIN columns?

Aside:  LIMIT without ORDER BY (like in your first query) produces arbitrary results. Typically you want to add ORDER BY to determine results.


Insert JSONL with two columns of comma separated fields into separate rows in mysql I have json datas in the format as shown below.
**the number of objects under 'images'(VIEWS, ROOMS, etc.) varied for each JSON object. Not all objects have VIEWS...POOL, it may only have ROOMS.
    "propertyId":{"ea":"12345678","h":"","vo":""},
    "thumbnail":{"width":1000,"height":500,"link":"xxx.jpg"},
    "hero":{"title":"Featured Image","width":1000,"height":669,"link":"yyy.jpg"},
    "images":{
       "VIEWS":[
        {"title":"View from Property","width":1000,"height":666,"link":"a1.jpg"},
        {"title":"View from Property","width":1000,"height":666,"link":"a2.jpg"}
       ],
        "ROOMS":[
            {"title":"Room","width":1000,"height":669,"link":"b1.jpg"},
            {"title":"Room","width":1000,"height":662,"link":"b2.jpg"}
       ],
        "EXTERIOR":[
            {"title":"Terrace","width":1000,"height":666,"link":"c1.jpg"},
            {"title":"Terrace","width":1000,"height":666,"link":"c2.jpg"}
       ],
        "AMENITIES":[
             {"title":"Property Amenity","width":1000,"height":666,"link":"d1.jpg"}
       ],
        "POOL":[
            {"title":"Outdoor Pool","width":1000,"height":666,"link":"e1"},
            {"title":"Outdoor Pool","width":1000,"height":666,"link":"e2.jpg"}
    ]}
    }

I wanted to insert the following data into the table with the following format in MySQL Ver 8.0.23 database.

JSON -> Table Reference
    HotelId = propertyId.ea
    hero_url = hero.link
    hero_title = hero.title
    media_title = images.(VIEWS/ROOMS/EXTERIOR/...).title
    media_url = images.(VIEWS/ROOMS/EXTERIOR/...).link
    >> ...other objects may have different category (VIEWS/ROOMS...)
    created_at and updated_at = date

After searching through the old topics, I was able to use the select statement below to display the format above. However, I'm confused as to it how works when using INSERT.
  SELECT t.HotelID, t.hero_url, t.hero_title,SUBSTRING_INDEX(SUBSTRING_INDEX(t.media_title, ',', n.n), ',', -1) media_title, SUBSTRING_INDEX(SUBSTRING_INDEX(t.images, ',', n.n), ',', -1) images
  FROM hotel_sample.property_photos t CROSS JOIN
  (
   SELECT a.N + b.N * 10 + 1 n
     FROM
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
   ) n
 WHERE n.n <= 1 + (LENGTH(t.media_title) - LENGTH(REPLACE(t.media_title, ',', '')))
 ORDER BY HotelID

Would appreciate if anyone could show me how I could insert the above JSON data into the table. (MySQL Ver 8.0.23)
CREATE TABLE `property_photos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `HotelID` varchar(10) DEFAULT NULL,
  `hero_url` varchar(255) DEFAULT NULL,
  `hero_title` text,
  `category` varchar(30) DEFAULT NULL,
  `media_url` varchar(255) DEFAULT NULL,
  `media_title` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
  PRIMARY KEY (`id`)

The parsing consists from 2 stages/queries.
First stage parses root object properties.
WITH
cte1 AS (SELECT @json AS val)
SELECT val->>"$.propertyId.ea" + 0 AS ea,
       val->>"$.propertyId.h" AS h,
       val->>"$.propertyId.vo" AS vo,
       val->>"$.thumbnail.width" + 0 AS t_width,
       val->>"$.thumbnail.height" + 0 AS t_height,
       val->>"$.thumbnail.link" AS t_link,
       val->>"$.hero.title" AS h_title,
       val->>"$.hero.width" + 0 AS h_width,
       val->>"$.hero.height" + 0 AS h_height,
       val->>"$.hero.link" AS h_link
FROM cte1

Output:

ea
h
vo
t_width
t_height
t_link
h_title
h_width
h_height
h_link

12345678

1000
500
xxx.jpg
Featured Image
1000
669
yyy.jpg

Second step parses its subobjects.
WITH
cte1 AS (SELECT @json AS val),
cte2 AS (SELECT val->>"$.propertyId.ea" + 0 AS ea,
                val->"$.images.VIEWS" AS views,
                val->"$.images.ROOMS" AS rooms,
                val->"$.images.EXTERIOR" AS exterior,
                val->"$.images.AMENITIES" AS amenities,
                val->"$.images.POOL" AS pool
         FROM cte1),
cte3 AS (SELECT cte2.ea, 'views' type, jsontable.*
         FROM cte2
         CROSS JOIN JSON_TABLE(CAST(cte2.views AS JSON),
                               "$[*]" COLUMNS (title VARCHAR(255) PATH "$.title",
                                               width INT PATH "$.width",
                                               height INT PATH "$.height",
                                               link VARCHAR(255) PATH "$.link")) jsontable
         WHERE cte2.views IS NOT NULL
         UNION ALL
         SELECT cte2.ea, 'rooms' type, jsontable.*
         FROM cte2
         CROSS JOIN JSON_TABLE(CAST(cte2.rooms AS JSON),
                               "$[*]" COLUMNS (title VARCHAR(255) PATH "$.title",
                                               width INT PATH "$.width",
                                               height INT PATH "$.height",
                                               link VARCHAR(255) PATH "$.link")) jsontable
         WHERE cte2.rooms IS NOT NULL
         UNION ALL
         SELECT cte2.ea, 'exterior' type, jsontable.*
         FROM cte2
         CROSS JOIN JSON_TABLE(CAST(cte2.exterior AS JSON),
                               "$[*]" COLUMNS (title VARCHAR(255) PATH "$.title",
                                               width INT PATH "$.width",
                                               height INT PATH "$.height",
                                               link VARCHAR(255) PATH "$.link")) jsontable
         WHERE cte2.exterior IS NOT NULL
         UNION ALL
         SELECT cte2.ea, 'amenities' type, jsontable.*
         FROM cte2
         CROSS JOIN JSON_TABLE(CAST(cte2.amenities AS JSON),
                               "$[*]" COLUMNS (title VARCHAR(255) PATH "$.title",
                                               width INT PATH "$.width",
                                               height INT PATH "$.height",
                                               link VARCHAR(255) PATH "$.link")) jsontable
         WHERE cte2.amenities IS NOT NULL
         UNION ALL
         SELECT cte2.ea, 'pool' type, jsontable.*
         FROM cte2
         CROSS JOIN JSON_TABLE(CAST(cte2.pool AS JSON),
                               "$[*]" COLUMNS (title VARCHAR(255) PATH "$.title",
                                               width INT PATH "$.width",
                                               height INT PATH "$.height",
                                               link VARCHAR(255) PATH "$.link")) jsontable
         WHERE cte2.pool IS NOT NULL)
SELECT * FROM cte3;

Output:

ea
type
title
width
height
link

12345678
views
View from Property
1000
666
a1.jpg

12345678
views
View from Property
1000
666
a2.jpg

12345678
rooms
Room
1000
669
b1.jpg

12345678
rooms
Room
1000
662
b2.jpg

12345678
exterior
Terrace
1000
666
c1.jpg

12345678
exterior
Terrace
1000
666
c2.jpg

12345678
amenities
Property Amenity
1000
666
d1.jpg

12345678
pool
Outdoor Pool
1000
666
e1.jpg

12345678
pool
Outdoor Pool
1000
666
e2.jpg

You may use these queries as a rowsource for INSERT statement, combine these queries if needed.
If "$.propertyId.ea" is not enough for unique root object identifying then add necessary columns into the last step query.
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=81ebab30922db5f99abd90c8b39622e5
PS. + 0 converts extracted string value to numeric datatype.


MySQL: Foreign key constraint on composite key with the referenced table a function of a constituent column of the key I have two MySQL tables customFieldsText and customFieldsMultipleChoice. Both tables have an auto-incremental unsigned int primary key.

I'd like to create a new table called customFields with the following two columns:

customFields
 - id [int] [unsigned] [auto-increment]
 - type [string] [not nullable]
 - foreignId [int] [unsigned] [not nullable]

I'd like to enforce that [type, foreignId] is a unique key on this table. I would like to have "something like" a foreign key constraint that works this way:

if type == 'text' then
   enforce that foreignId can be found in customFieldsText table
if type == 'multiple_choice' then
   enforce that the foreignId can be found in the customFieldsMultipleChoice table

I'm not too experienced with SQL and I don't know if this is possible at all. I think it's probably clear what I want to achieve here: I want to be able to uniquely identify all my customFields by a single unsigned integer key. I inherited the two tables mentioned above so I'd be disinclined to change their schema. How would you solve this issue? Is there a way to create such a foreign key constraint?

Maybe it's not exactly what you expected but you can try such table structure:

CREATE TABLE customFields (
    id INT UNSIGNED NOT NULL
    ,type VARCHAR(15) NOT NULL -- it would be better to use bit
    ,foreignIdMultipleChoice INT NULL
    ,foreignIdText INT NULL
    ,foreignId INT AS (IFNULL(foreignIdMultipleChoice, foreignIdText))
    ,CONSTRAINT FK_foreignIdMultipleChoice FOREIGN KEY (foreignIdMultipleChoice)
        REFERENCES customFieldsMultipleChoice(id)
    ,CONSTRAINT FK_foreignIdText FOREIGN KEY (foreignIdText)
        REFERENCES customFieldsText(id)
    )

You will need to insert foreignId value into one of these columns (foreignIdMultipleChoice or foreignIdText) depending on the field source. If you are on latest version of MySQL you can add check constraint to avoid issues when both of these columns are nulls or both of them are not null simultaneously. Something like this:

,CONSTRAINT `foreignId_check` CHECK ((foreignIdMultipleChoice > 0 AND foreignIdText IS NULL)
                                      OR (foreignIdMultipleChoice IS NULL AND foreignIdText > 0))


Call to sysmail_delete_mailitems_sp keeps deadlocking I run SQL Server 2012.
I have a maintenance job that runs every night. At the end of the maintenance, I do some cleanup and call this:

Begin try
Exec msdb.dbo.sysmail_delete_log_sp @logged_before = '20160613';
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp  @sent_before = '20160613';
End try
Begin catch
-- Do some log error
End catch

I put the transaction in a Try/Catch but it doesn't seem to be working. I keep getting this error :
"Error 1205, Severity 13, level 18 : Transaction (Process ID 112) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

If I run the transaction manually, it runs fast and I don't get any errors.
Any ideas why I keep getting this error or how I can catch it so it doesn't fail my job?

So I didn't got any luck with the deadlock graph. Doesn't seem to appear in my SQL Logs. But, I did find out that another job has been scheduled to run at the same time than my job, and it was taking backups and sending e-mails at the same time that my job was trying to cleanup the mail history.
So changing the schedule of the other job got my problem solved!


DB2 : Load from cursor Dynamic query We need to move multiple tables using  Load from cursor. But the number columns in the source and destination tables are not the same.

Also the  tables structures can change so we need the same to be dynamic.

the statement would be something like below.

 declare cur1 cursor for select col1,col2, col3  from test1;
 load from cur1 of cursor insert into test2 (col1,col2, col3);

I need to call this from a procedure using execute immediate so that we can append the columns in the query. The list of columns is in a variable V_COL_LIST so the query i need to execute for procedure is like below.

 execute  immediate ('declare cur1 cursor for select '||V_COL_LIST ||'  from test1');
 execute  immediate ('load from cur1 of cursor insert into test2 ('||V_COL_LIST ||')');

But Execute Immediate does not work for the above queries.
Is there any work around to this ?

Figured it out used ADMIN_CMD

set v_sql ='LOAD FROM (SELECT '|| v_colquery || ' FROM ' || srctablename  || ') OF CURSOR INSERT INTO ' || destablename || '_LOG  ( ' || v_colquery || ')';
call admin_cmd(v_sql);


Is there a way to create a re-usable base table definition in InfoSphere Data Architect? Our company has chose to use IBM's InfoSphere Data Architect (IDA) for data architecting regarding our data warehouse and data marts.

The data architects originally designed several "re-usable" tables for our marts. In other words a table like CUSTOMER_DIMENSION may reside structure-wise in more than one mart. Each mart will have their own copy of their data.

I believe to make things easy, they defined all the tables (dimensions and facts) within the same "schema" that they called DB2ADMIN within IDA.

Since a mart can only have one fact table, I have to look at the diagrams of the table relationships to the fact tables to determine which tables should get generated into a DDL file for a particular mart. As of right now each mart physically resides within its own schema within one database. So not only do I have to manually pick the tables for DDL generation, but I have to manually tweak each DDL file to override the schema. This is not ideal as it is an error-prone process.

Is there a way to have a "base" definition of a table and then inherit it or whatever into the real table within IDA? What I'm looking to do is have schema definitions within the tool same as what we have on our server. I would like only the applicable tables to show up within each schema (as a mart should be - one fact and only its dimension tables it cares about). But to save on work, I'd prefer if the table definitions were in one place. So the architects could make one change, but we could more easily generate our tables.

Is this possible? Are there any gurus with IDA out there?

Sadly, it appears that at least in the version we are currently using (7.5.3.0), that no such functionality exists. Marking this the answer for now, unless someone else discovers otherwise (including within a new version).


Migrate unknown Oracle database user's encrypted password - 11g hash to 12c database I have tried to search extensively but could not find.Oracle has changed the encryption (spare4) in 12c.

I am trying to see if its possible to migrate / copy the encrypted password hash of a user in 11g database to 12c.

I have done this before on 11g to 11g, but not able to replicate / find how to do this on 11g to 12c.

Thanks!

This isn't really a database administration question. To "migrate" a hashed password to a different hash algorithm you will need to "unhash" it first, that is, find the original password from its salted SHA1 hash, which is not practical.

You'll need to reset that user's password.


Duplicate Data from two column i have a table which has only two column one is pid(Photo ID) and another one is tid(Tag ID)

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| pid   | int(10) | NO   |     | NULL    |       |
| tid   | int(10) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

this table will store a photo id with a tag id, but i am facing a problem and i guess which is against normalization as well, here is my table data.

+-----+-----+
| pid | tid |
+-----+-----+
|  10 |   1 |
|  10 |   2 |
|  10 |   3 |
|  10 |   4 |
|   0 |   6 |
|   0 |  10 |
|   0 |  10 |
|  10 |   1 |
|  10 |   2 |
|  10 |   3 |
|  10 |   4 |
+-----+-----+

it store many duplicate values, how to get rid of this?I mean, how i can store this types of row only for one time: |  10 |   1 |
I have tried with UNIQE but this will going to store a photo id or a tag id only for one time?I'm using PHP
Thanks

You need a compound unique index.

Suppose your table is called photos. You can do this:

CREATE TABLE photos_new LIKE photos;
ALTER TABLE photos_new ADD UNIQUE INDEX pid_tid_index (pid,tid);
INSERT IGNORE INTO photos_new SELECT * FROM photos;
ALTER TABLE photos RENAME photos_old;
ALTER TABLE photos_new RENAME photos;

If it works out, then run

DROP TABLE photos_old;

Give it a Try !!!


SELECT Statements only please I have a user that wants to use SQL Server Management Studio (SSMS) to run SELECT statements.  I don't want him to be able to modify data.

I can't regulate his permissions through Windows Authentication because his user account needs modify permissions for programs that we run.

I could create a separate Windows account for him with less permissions, however if he does not use this new account, he will be able to change data.

Does anyone else have this problem? Is there a simple solution?

Grant the user read-only access and have your programs authenticate as a different user (one that's got modify rights).


PowerShell Subsystem max_worker_threads reverts back to default after applying cumulative update for SQL Server 2016 We change the PowerShell subsystem max_worker threads to 10, but after we install the latest cumulative update, it reverts back to 2.  Is there a way to keep that from happening?

SELECT  s.subsystem_id,
        s.subsystem,
        m.[text] AS [description],
        s.max_worker_threads
FROM msdb.dbo.syssubsystems s
INNER JOIN msdb.sys.messages m ON s.description_id = m.message_id
AND m.language_id = 1033

Create an agent job that runs on start up to set the number of threads. Set the schedule option to Start Automatically when SQL server agent starts.


Are idx_scan statistics reset automatically (default)? I was looking at the tables (pg_stat_user_indexes and pg_stat_user_tables) and discovered many indices that are not being used.

But before I think about doing any operations to remove these indices, I need to understand what period was the analysis of this data (idx_scan), has it been since the database was created?

In the pg_stat_database (stats_reset) table is there a date that normally is today or up to 15 days ago, but does this process interfere with the tables I mentioned above?

No command of% reset () was executed.

Do the% reset () commands clear the tables (pg_stat_user_indexes and pg_stat_user_tables)?

My goal is to understand the period of data collected so that I can make a decision.

Quote from the manual

  When recovery is performed at server start (e.g. after immediate shutdown, server crash, and point-in-time recovery), all statistics counters are reset

In other words, the statistics are not reset on a clean shutdown and restart.

So if your server never crashed, and you never issued a "shutdown immediate" (using `pg_ctl stop -m immediate) then the statistics show the cumulated values since the instance was created (for "global" statistics") or since the database was created (for database specific statistics).

If you want to detect if there are unused indexes, I would reset the statistics, then take snapshots of them in regular intervals (e.g. every day or every hour depending on your system) and then compare the values over time after e.g. a week of normal operation.

Note that you can't (shouldn't) remove unique indexes (e.g. a primary key index) that have never been used for reading!


SQL Server backup to Azure Blob with different backup plan than local backup We have a production SQL Server with a number of critical bases. At this time, we have a maintenance plan which performs the following:

creates a FULL backup every Saturday with a TTL of 4 weeks
creates a DIFF backup every night (except Saturday) with a TTL of 2 weeks
creates a LOG backup every hour with a TTL of 1 week

As such, we're able to restore the databases to any Saturday of the past 4 weeks, any day (night) of the past 2 weeks and almost any specific point in time for the past week. Also, we're using the backup scripts by Ola Hallengren, and the databases are for our SharePoint and TFS.

We recognize that 4 weeks is not that long AND those backups are, ultimately, local backups, so if something serious happens to the physical server they are on, we'll be in trouble.

I wanted to utilize Azure Blobs to store online backups (for FULL and DIFF as LOG can remain local), however I have a bit of a problem coming up with a good plan on how to do that. The main problem is that our internet connection isn't very fast (we only have a symmetrical 30/30 at the office) and, well, Azure costs money (Blobs are cheap, but still). But make no mistake, network speed is the primary problem.

I wanted to use a different backup plan for Azure than for local backups, but quickly ran into a problem - DIFF backups become useless if the FULL backups aren't the same in both backup locations and I can't have the same FULL backups in both locations if I want a different schedule for Blobs.

To put that into a more practical example: say I want to save a FULL backup into Azure on the first weekend of every month... if my local backup was the same, that wouldn't be a problem. But now, if I do copy-only FULL backup to Azure, then daily DIFF backups stored in Azure won't work. If I do a non-copy-only FULL backup to Azure, then the LOCAL backups get broken. Besides, the first FULL local backup after the first Azure backup will break the subsequent DIFF backups on Azure anyway.

I think the only thing I can do is take FULL backups less often (say, 1 FULL per month?), increase the TTL accordingly, and then have the same backup plan for Azure (using the MIRROR options in the script). But then I worry that this is not often enough for FULL backups. Alternatively, the script by Ola has a ModificationLevel parameter, but I'm not sure how to use it for optimal storage space utilization.

Alternatively I could utilize a file-sync-to-Azure solution (rather than an SQL-backup-to-Azure solution), but this is really the same problem.

Is there a way to have a different backup plan for Azure than for local SQL backups?

As @LowlyDBA pointed out "having a separate job to upload the files to Azure BLOB" is the right approach here.  You simply can't have two separate jobs managing differential or log backups.  And you don't really want to have any BACKUP command that runs for a really long time.

Just have a single local backup plan, and a secondary job that uses AZCopy to push some or all of the resulting backup files into Azure Blob storage.

Here's an old blog post on this topic Copying SQL Server Backups to Windows Azure Storage using AzCopy.  You can also try the new Azure File Sync  service to automatically move files to Azure.


User has no password set I see these when running MySQLTuner.pl:

-------- Security Recommendations  -------------------------------------------
[!!] User '@debian' has no password set.
[!!] User '@localhost' has no password set.
------------------------------------------------------------------------------

Is it a security hole?
How to fix it?

Thanks

That is definite a security hole. That's because mysql was installed that way.

To remove those entries, please run these lines

DELETE FROM mysql.user WHERE user='' or password='';
FLUSH PRIVILEGES;

Here are three(3) past posts I wrote on how and why to do such cleanup of mysql.user and mysql.db

Secure method beside phpMyAdmin for remote mysql
MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
MySQL : Why are there "test" entries in mysql.db?


How to enable remote access to wamp mysql I have wamp server which is installed in windows server. Since database is becoming large want to move code to other machine from there I want logon to wamp mysql.

I have disabled bind-to-address and created new user with all permission, but unfortunately not able to connect mysql remotely.

Steps I followed:

CREATE USER 'user'@'x.x.x.x' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'user'@'x.x.x.x';

Please let me what is steps do I need to follow.

When you grant access to the MySQL instance to a user account you have to ensure you are allowing access from the remote host.

GRANT ALL PRIVILEGES ON *.* TO 'user'@'x.x.x.x';

In your example the x.x.x.x should be the IP address of the application server or if the server has DNS resolution it can be the hostname (A record or CName) of the application server.

E.g.

Given the following values:

APP Server ....: 192.168.10.12
APP Host Name .: myappserver.domain.com

DB Server .....: 192.168.10.25
DB Host Name ..: mydbserver.domain.com

...your statements should be:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'192.168.10.12';

or:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'myappserver.domain.com';

You are granting access to a user from a host/ip_address (myappserver) to the MySQL instance (mydbserver).

Then you have to ensure that you can TELNET into the MySQL server from the application server on port 3306 (default; or the port you have configured MySQL to listen on). On App Server:

bash> telnet mydbserver 3306

You should be greeted by the following if the firewall is open:

Connected to mydbserver.
Escape character is '^]'.
[

If not you may have a firewall issue. Ensure that the firewall is open from myappserver to mydbserver and port 3306 (default).


IBM i Access ODBC driver returning only first letter of column names I'm new to DB2 and am connecting to a DB2 for i (V7R1) database using unixODBC and the IBM i Access ODBC driver for Linux.

When I query the database, the results only include the first letter of the column names. For example, a typical query run with the isql utility will give me something that looks like this:

SQL> SELECT column1, column2 FROM schema.table WHERE column1 = 12345
+--------+---------------------------------+
| C      | C                               |
+--------+---------------------------------+
| 12345  | Lorem ipsum dolar sit           |
+--------+---------------------------------+
SQLRowCount returns -1
1 rows fetched

This is a problem for me because when I try to query the database in applications (e.g. using the pyodbc library for Python), I can't access the results by column name; both columns are named "C" in the result set.

My questions are:

Why is this happening?
Is it possible to change this behavior? (How?)

EDIT:

This happens even if I try to give the columns aliases:

SQL> SELECT column1 as foo, column2 as bar FROM schema.table WHERE column1 = 12345
+--------+---------------------------------+
| F      | B                               |
+--------+---------------------------------+
| 12345  | Lorem ipsum dolar sit           |
+--------+---------------------------------+
SQLRowCount returns -1
1 rows fetched

I believe this is a bug with the specific version of IBM's driver I was using. I was using the ODBC driver from IBM i Access Client Solutions Linux AP v07.03.00.

I replaced that driver with the ODBC driver from IBM i Access for Linux v7.1.0-1.0 and now everything works as expected.


MySQL with slow queries I have a website running on Wordpress Multisite + Woocommerce with over 5700 users on a Nginx + Apache server. I recently experienced some down time but didn’t get any errors, just a blank page.

I contacted my hosting company (WP Engine) and they said the following:

  your site is suffering from slow queries being run on tables with too
  many rows.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Name                           Rows
wp_users                       6046
wp_groups_user_group           6867
wp_rg_form_view                7657
wp_posts                       12574
wp_rg_lead_detail              12925
wp_woocommerce_order_items     13510
wp_term_relationships          14581
wp_options                     17169
wp_comments                    57730
wp_commentmeta                 65568
wp_usermeta                    176243
wp_woocommerce_order_itemmeta  278636
wp_postmeta                    491916
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

  The reason the errors are happening is because the queries on the
  above tables are not completing fast enough. This is due to excessive
  table rows. Here is an example query that is having the issue:

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Time: 140219 13:00:28
# User@Host: Websitename @ localhost [127.0.0.1]
# Thread_id: 3948879  Schema: wp_dbname  Last_errno: 0  Killed: 0
# Query_time: 2.394413  Lock_time: 0.000171  Rows_sent: 1  Rows_examined: 510794  Rows_affected: 0  Rows_read: 510794
# Bytes_sent: 89
SET timestamp=1392814828;
SELECT COUNT( order_items.order_item_id )
        FROM wp_woocommerce_order_items as order_items
        LEFT JOIN wp_woocommerce_order_itemmeta AS itemmeta ON order_items.order_item_id = itemmeta.order_item_id
        LEFT JOIN wp_postmeta AS postmeta ON order_items.order_id = postmeta.post_id
        LEFT JOIN wp_term_relationships AS rel ON postmeta.post_id = rel.object_ID
        LEFT JOIN wp_term_taxonomy AS tax USING( term_taxonomy_id )
        LEFT JOIN wp_terms AS term USING( term_id )
        WHERE   term.slug IN ('completed','processing','on-hold')
        AND     tax.taxonomy        = 'shop_order_status'
        AND     (
                    (
                        itemmeta.meta_key = '_variation_id'
                        AND itemmeta.meta_value = '1261'
                    ) OR (
                        itemmeta.meta_key = '_product_id'
                        AND itemmeta.meta_value = '1261'
                    )
        )
        AND     (
                    (
                        postmeta.meta_key = '_billing_email'
                        AND postmeta.meta_value IN ( 'john@email.com' )
                    ) OR (
                        postmeta.meta_key = '_customer_user'
                        AND postmeta.meta_value = '2936' AND postmeta.meta_value > 0
                    )
                )
     /* From [example.com/product/example_product/] in [example.com/wp-content/plugins/woocommerce/woocommerce-core-functions.php:1862] */;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

And finally they suggested for me to work with my database/web developer to slim the database and this will speed the queries up and allow them to work properly.

As I am not database expert, I just wanted to ask here - is what WP Engine recommended to me reasonable? I ask because I know there are many websites running on much bigger databases that are larger than mine and they're working properly! That being said, Could it possibly be that WP Engine just has slow server processing or should I do what they recommended?

Thanks

If you are using MySQL v5.6 because Performance_Schema has lot of metadata about your query/table and how the processing of a query happened (almost everything that is helpful for you to debug a problem/query internals)

You can have a look at my existing answer here:
Is there a way to get slow-query-log-like profiling information on a single query?

Otherwise, You can try Show Profiling functionality of MySQL to find out where most of the time has been taken by a query execution and optimize based on result:

set profiling=1;

Run your slow query (eg SELECT * FROM table WHERE name='xxx';

SHOW PROFILES;

http://dev.mysql.com/doc/refman/5.0/en/show-profile.html

Analyze EXPLAIN EXTENED output and optimize query accordingly and profile the query again to see gain in performance.


Why shouldn't I create a new user on Oracle SYSTEM Tabespace? For example:

CREATE USER c##mybear IDENTIFIED BY pretty
DEFAULT TABLESPACE USERS
QUOTA 500M ON SYSTEM;

So, Why shouldn't I create a new user on Oracle SYSTEM Tabespace?

The Oracle Documentation states:

  The default setting for the default tablespaces of all users is the
  SYSTEM tablespace. If a user does not create objects, and has no
  privileges to do so, then this default setting is fine. However, if a
  user is likely to create any type of object, then you should
  specifically assign the user a default tablespace, such as the USERS
  tablespace. Using a tablespace other than SYSTEM reduces contention
  between data dictionary objects and user objects for the same data
  files. In general, do not store user data in the SYSTEM tablespace.

The documentation also explains how to create an alternative tablespace etc.


Storing dart throws in relation database (JSON vs multiple tables) I'm trying to model database for storing dart throw practices.
Data sent from frontend will look something like this
[
  {"target_name":"S13","singles":1,"doubles":1, "triples":1, "misses":1}
  {"target_name":"S14","singles":5,"doubles":3, "triples":3, "misses":4}
  {"target_name":"S15","singles":5,"doubles":3, "triples":3, "misses":4}
   ... \\ from 1 to 100 rows
]

And i want to store in table/tables in SQL database.
On tables I want to make operations  (in brackets I placed how this selects would look like)
Requirement listed ordered by importance

Get short game summary,
(Select all_singles,all_doubles,all_triples,all_misses, points, datetime from SDT_GAME)
Get Game data, so i will send back data as was given (Select gamesheet_JSON from SDT_GAME)
Get Merged all games data for current user, for every target return
(SELECT COUNT(singles), COUNT(doubles), COUNT(triples), COUNT(misses) from sdt_row INNER JOIN ... GROUP BY target)

I had 2 ideas like below (SDT_GAME2 vs SDT_GAME)

I like SDT_GAME_2 option more because of db normalization. But I'm worried that storing on average 50 rows for every game will clog db fast and make selecting(requirements 2) much slower.
Are any of these options clearly better considering my requirements?

When I data plan for a table or system, I think in terms of how much data do I realistically see being stored in 1 year, 5 years, 10 years and 20 years. I don't know how many games you expect in a given day and over a year's time, but accumulating millions of records in a single table in a year is not uncommon and definitely reasonable to support in most RDBMS. Obviously there's no concrete answer to exactly how many rows a table can hold at a given time and still be performant, because it's depend on the server hardware and database design, but again millions of rows in one table is not something to be scared of by any means. I've worked with tables with 10s of billions of rows that were performant running on servers with pretty standard hardware (8 Core CPU, 32 GB of RAM, SSD).
That being said, I think your database design is pretty good and I would definitely recommend going with SDT_GAME_2 because of the better normalization. The aggregations for all games data by user is something you should easily be able to calculate on the fly as needed (e.g. with a view or procedure) instead of storing the aggregation in a table.


SSMS and Registered Server Results Background:

We are trying to create an AG "is primary" test harness for one of our support teams.  Not knowing which servers at any given time will be the primary they have been instructed to execute the TSQL against a registered server group.  The registered server group consists of all servers in the AG.  The goal is to only execute TSQL on the current primary server:

Current Test Harness:

IF EXISTS (SELECT *
FROM sys.dm_hadr_availability_replica_states AS HARS
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS HACS ON HACS.replica_id = HARS.replica_id
    WHERE (HARS.role_desc = 'PRIMARY') AND (HACS.replica_server_name LIKE @@SERVERNAME))
BEGIN
<<SOME CODE TO EXECUTE>>
END

Problem:

If the first server that responds back to the multi server query doesn't return any results, SSMS will assume the right result set is no result set, even if other servers later come back with a result set.  So in this scenario, no results get returned...this is not correct and is not the expected functionality.

Can anyone think of a way, with SSMS (this is the most familiar tool to the CS team), to force execution only on current primary server?

I have run into this** before, and if I recall correctly, to ensure always getting results with multi-server queries you need to force an empty result set when no rows would otherwise be returned. Meaning, you need an ELSE branch on that IF and within the ELSE you would do something like the following:

SELECT CONVERT(DATETIME, NULL) AS [Col1name],
       CONVERT(DECIMAL(12, 5), NULL) AS [Col2name],
       ...{additional fields}...
WHERE  1 = 0;

This produces an empty result set that has the proper names and datatypes.

OR, and I have not tried this in the past (just thought of it as I was typing this), but you might be able to get away with simply pausing in that ELSE branch such that the primary / intended server is allowed to always return its result set first (which is the real issue here: the first server to respond defines the structure that all other responses must adhere to). Hence the following might work as the only thing in the ELSE:

WAITFOR DELAY '00:00:10'; -- 10 seconds (just needs to be longer than the real query takes)

But I don't remember if having other servers return no results at all caused an error message to be displayed in the "Messages" tab. If that does happen, then the empty result set is definitely the way to go. But if this does work, then this might work better in a general template (like your case seems to be) as it wouldn't require adjusting the forced, empty result set each time it is used.

UPDATE:

The O.P. verified that:

the WAITFOR DELAY did indeed work, and
the replicas did report the error message, but it did not present a problem for the O.P.'s usage

** The situation I ran into was similar, but had nothing to do with either Availability Groups or wanting results from only one server. Our situation was that we had 18 servers of same schema different data and needed to do various maintenance tasks, aggregations across all 18 nodes. There were some stored procedures that, for whatever reason, occasionally didn't return any result set, and whatever that reason was it couldn't be fixed within the stored procedure. So, depending on which node returned first, most of the time everything was fine, but every once in a while the node that sometimes returned no result set returned first. So, I had to do something like dump the results into a temp table and if @@ROWCOUNT of that INSERT...EXEC was 0, then I would select the forced, empty result set.


Having md5(NULL) return a non-NULL value in postgresql I have a table with the structure (content_md5 UUID, content TEXT), where I'd like to use content_md5 (which is the value of md5(content)) as the primary key, and use it as a foreign key in other tables.

This is a "static" table, where the content (some largish documents) would be referred to by their md5 value for simplicity, and to prevent duplication in the table (which wouldn't be a given with a simple SERIAL PKEY).

However, content can be NULL, which is different from an empty value to declare a non-existing content field in the referencing table.
Since md5(NULL) returns NULL, and NULL is not allowed in a primary key constraint, I'd like to have a way of having md5(NULL) return all zeros instead of NULL.

Example:

-- setup
CREATE TABLE example (content_md5 UUID PRIMARY KEY, content TEXT);
CREATE TABLE test (id SERIAL PRIMARY KEY, tags TEXT, content_md5 UUID REFERENCES example(content_md5) ON DELETE RESTRICT);
INSERT INTO  example VALUES ('00000000000000000000000000000000'::uuid, NULL);

-- usage
INSERT INTO example VALUES (md5('some text')::uuid, 'some text');
INSERT INTO test (tags, content_md5) VALUES ('some content defining tags', md5('some text')::uuid);

SELECT tags, content FROM test LEFT JOIN example USING (content_md5);

-- QUESTION: Having an md5-like function to return zero-filled "md5"/uuid?
INSERT INTO example VALUES (md5(NULL)::uuid, NULL); -- ignored, because already existing record
INSERT INTO test (tags, content_md5) VALUES ('non-existing-document', md5(NULL)::uuid);

Is it possible to somehow cast the returned value to a zero-filled string, create a custom function based on md5() which replaces NULL with 00000000000000000000000000000000, or some other way to achieve this result?

/edit: Or perhaps I don't need any NULL values in this table, and can just set the referencing foreign key column to NULL to achieve the same result?

I suggest this alternative design:

-- setup
CREATE TABLE example (content_id serial PRIMARY KEY, content text);
CREATE TABLE test (id serial PRIMARY KEY, tags TEXT, content_id int REFERENCES example);

CREATE UNIQUE INDEX ON example ((md5(content)::uuid)) INCLUDE (content_id); -- !

-- usage
INSERT INTO example(content) VALUES (NULL);        -- allowed multiple times
INSERT INTO example(content) VALUES ('some text');

INSERT INTO test (tags, content_id)
SELECT 'some content defining tags', content_id
FROM   example
WHERE  md5(content)::uuid = md5('some text')::uuid;

db<>fiddle here

Major points

Use a serial column (content_id) as surrogate PK of table example - and as FK reference everywhere. 4 bytes instead of 16.

Enforce uniqueness with a unique index on the expression md5(example)::uuid. Be aware that hash collisions are possible (even if very unlikely while your table isn't huge).

While being at it, add the serial PK column to the index with an INCLUDE clause (Postgres 11 or later) to make it a covering index for fast index-only lookup.

As opposed to a PK column, this allows NULL, and NULL is not considered to be a duplicate of NULL, which should cover your use case. See:

Allow null in unique column

In Postgres 10 or older don't add content_id to the index. Then you don't get index-only scans, of course:

CREATE UNIQUE INDEX ON example ((md5(content)::uuid));

Unless you want to allow only a single instance of NULL, which could be enforced with a function like you posted (introducing the risk of a collision - even if unlikely) or a tiny partial index in addition to the one above:

CREATE UNIQUE INDEX ON example (content_id)
WHERE md5(content)::uuid IS NULL;

See:

Create unique constraint with null columns

Do not store the md5 value as table column (redundantly) at all.

If you want to keep using the function you posted in your answer, consider optimizing it:

CREATE OR REPLACE FUNCTION pg_temp.md5zero(data text)
  RETURNS uuid PARALLEL SAFE IMMUTABLE LANGUAGE sql AS
$func$
SELECT COALESCE(md5(data)::uuid, '00000000000000000000000000000000')
$func$

Faster, and can be inlined. See:

Query slow when using function in the WHERE clause


Logical Reads vs. Records Returned I have a very basic query against a large table that roughly follows this form:

SELECT
    [Extent1].[A] AS [A],
    [Extent1].[B] AS [B]
FROM [dbo].[SomeTable] AS [Extent1]
WHERE
    ([Extent1].[A] <= (SysUtcDateTime()))
    AND (2 = [Extent1].[B])

This query spends 100% of its time using the clustered index. In different environments I have observed these results:

DEV: 391 rows, 12 logical reads
QA: 341502 rows, 6030 logical reads
UAT: 1273656 rows, 52536 logical reads

It seems to me that this query is about as efficient as it can be given the size of the result set.

Is there a generally accepted correlation between logical reads and the amount of data returned by a query? If so, is it safe to say that these results fall within that acceptance range for a well-performing query?

Is there a generally accepted correlation between logical reads and the amount of data returned by a query?

The missing piece is the

1) The number of logical reads of pages with no relevant data

2) The number of relevant result rows on each page

And these two are determined by your indexes.  If this table has no relevant indexes, then you will have to scan each page, and you will have to read a lot of completely or mostly irrelevant pages.  Even if your index supports a query plan where you read only pages (mostly) full of relevant rows, if those rows are very wide, then there may not be very many (A,B) pairs per page.

If you had an index on (B,A) on this table then SQL could seek to the first row where B=1 and start scanning rows until it hits a row with A>SysUtcDateTime().  This would only require ~4 Logical IOs to get to the first page, and then every Logical IO would result in ~700 (A,B) pairs.


selecting complete rows grouped by one column based on the maximum of another I'm trying to find a query selecting all columns of my table, where the result set contains only distinct values of a certain column. The row being selected for a specific distinct value should be the one with the maximum value of another column (within the group of rows having that value for the former column).

I tried

SELECT column1, column2, MAX(column3) FROM table GROUP BY column1;

which doesn't work because in the results column2 and MAX(column3) are not from the same row. How is this achieved in mysql?

Example

For a table like

CREATE TABLE table (column1 VARCHAR(10), column2 VARCHAR(10), column3 INT);

and data like

INSERT INTO table VALUES ("a", "x", 1);
INSERT INTO table VALUES ("a", "y", 2);
INSERT INTO table VALUES ("a", "z", 3);
INSERT INTO table VALUES ("b", "x", 1);
INSERT INTO table VALUES ("b", "y", 2);

I want to get the rows ("a", "z", 3) and ("b", "y", 2). With the above query I get ("a", "x", 3) and ("b", "x", 2) i.e. MAX(column3) and the rest of the result do not come from the same original row in my data. I see why that is not possible with aggregation functions in general but I can't think of another simple way to achieve it.

general answer

SELECT t1.* from [table] t1
JOIN (SELECT column1, MAX(column3) as max_c3 from [table] group by column1) t2
ON t1.column1 = t2.column1 AND t1.column3 = t2.max_c3

how is it work -

SELECT column1, MAX(column3) as max_c3 from [table] group by column1) t2

prepare all rows with correct column1 and MAX(column3)

JOIN ON t1.column1 = t2.column1 AND t1.column3 = t2.max_c3

correctly select other columns corrected for result,

if table have more than 1 row with same MAX(column3) - query return 1 row for each MAX(column3) result.

Example data:

id; column1; column2; column3;column4
1   10  50  1   1
2   10  60  2   2
3   12  50  3   3
4   12  60  4   4
5   11  70  5   5
6   10  99  6   6
7   11  55  7   7
8   10  60  6   8

result:

id; column1; column2; column3;column4
8   10  60  6   8   10  6
6   10  99  6   6   10  6
7   11  55  7   7   11  7
4   12  60  4   4   12  4

if delete from table row with id=8 (remove duplicates for max(column3)), result will be:

id; column1; column2; column3;column4
6   10  99  6   6   10  6
7   11  55  7   7   11  7
4   12  60  4   4   12  4


Single query approach to split and copy string values in a column This is a part of my previous question.

I have the below logic to split a string value from a column in table tblProjects as below:

SELECT  @Docs = Documents FROM tblProjects WHERE ID = @ID
SET @Docs = @Docs + '$'
SET @pos = 0
SET @len = 0

WHILE CHARINDEX('$', @Docs, @pos + 1)>0
BEGIN
  SET @len = CHARINDEX('$', @Docs, @pos + 1) - @pos
  SET @value = SUBSTRING(@Docs, @pos, @len)

  IF CHARINDEX('|', @value) >0
  BEGIN
    SELECT  @type = SUBSTRING(@value, 1, CHARINDEX('|', @value) - 1),
            @name = SUBSTRING(@value, CHARINDEX('|', @value) + 1, 200)
    INSERT INTO tblDocuments VALUES(@ID, @value, @type, @name)
  END

  SET @pos = CHARINDEX('$', @Docs, @pos + @len) +1
END

A sample input would be

insert into tblProjects(ID, Documents)
values('P_1', 'gdgfg|jkkgk$bkgkkj|kjgkgk$vjffj|Khkgjhg$jkgkvghv|kbkghhkgk');

I need to split the Documents string and insert the values into a separate table. Values delimited by | should go into separate columns of the same row, whereas values for a particular row are delimited by $.

Is there a single query method to complete this operation, for all rows in the table, in the minimum possible time?

You should make use of a split string function.

select left(S.Item, charindex('|', S.Item) - 1),
       stuff(S.Item, 1, charindex('|', S.Item), '')
from dbo.tblProjects as P
  cross apply dbo.SplitString(P.Documents, '$') as S;

Aaron Bertrand blogged about some of the alternatives you have in Split strings the right way – or the next best way


Group rows by running aggregate I have a set of transactions that can either be payments or purchases. The purchases can be of different types (e.g. 'ApplePurchase', 'OrangePurchase').

I will select some payments by their coupon code. For each payment, I wish to discard all the purchases up to the last time, prior to that payment, that the balance reached zero. I then wish to aggregate the remaining purchases. (Thanks to @Andriy M for that phrasing and apologies for butchering it).

Any advice on how to approach writing (part of) the solution in SQL or would be much appreciated.

Here is some example data:

create table users
     (id integer primary key);

create table transactions
     (user_id integer references users (id),
     type enum('ApplePurchase', 'OrangePurchase', 'Payment'),
     coupon varchar(10),
     date_paid date,
     amount integer);

insert into users
    (id)
    values (1), (2), (3), (4), (5);

insert into transactions
    (user_id, type, coupon, date_paid, amount)
    values (1, 'ApplePurchase', NULL, '2003-01-02', 5),
           (2, 'ApplePurchase', NULL, '2003-01-02', 15),
           (1, 'Payment', 'aCoupon', '2003-01-02', 5), # here user 1's balance reaches 0
           (2, 'OrangePurchase', NULL, '2003-01-03', 10),
           (2, 'Payment', 'bCoupon', '2003-01-03', 23),
           (1, 'ApplePurchase', NULL, '2003-01-05', 15),
           (1, 'Payment', 'bCoupon', '2003-01-05', 15),
           (1, 'ApplePurchase', NULL, '2003-01-07', 15),
           (1, 'Payment', 'bCoupon', '2003-01-07', 15);

Perhaps I am looking to perform this aggregation for payments of coupon 'bCoupon'. Member 2 has one payment of that type and 3 purchases before it. As there are no intervening payments, we only need to aggregate the purchases (as in the first row). Member two has two payments of that type. The first has two purchases before it, but the very first purchase is cancelled by an earlier payment. In that case, I am looking for a result of the form

Date       | Member |  Apple | Orange | Payment | Balance
2003-03-01 | 2      |  15    | 10     | 23      | 2
2003-05-01 | 1      |  15    | 0      | 15      | 0
2003-07-01 | 1      |  15    | 0      | 15      | 0

One option is to leave some of this functionality to the application code. It's possible to query for the relevant data with

select * from transactions t
    where exists(
        select 1 from transactions t2
            where t2.coupon = 'bCoupon'
                and t.user_id = t2.user_id
                and t2.date_paid > t.date_paid
    )
    order by date_paid

And then fold over that.


Advisable to restart a SQL Server running Windows when disk space is low? We've had a sudden drop in hard drive space on the OS partition of our SQL Server (as databases are being stored there). It's causing various issues, and we're unable to move one of the databases, as the OS now seems to be placing pre-emptive locks on the database to ensure that the machine at least stays running.

We've tried everything we can think of. Is it advisable to now stop the SQL Server instance then restart Windows?

We're thinking this is the only way to clear things up, however our infrastructure guy is concerned that Windows will first try to write its state to disk before it restarts which could make the issue worse.

Any advice/experience in this situation?

The short answer is that we eventually literally pulled the plug on the SQL server and got away with it, (although I wouldn't necessarily recommend the approach). If you're in a situation like we were with this though, here's the full scenario:

We couldn't log onto the box at all, and all services dependant on the database were running very slowly. The OS seemed to be partially shutting down.
Eventually got lucky and managed to allocate an extra 6Gb to the server (from a low of 4Mb free), by connecting to it using computer management (after several attempts).
After this, all attempts to detach the database we wanted to move failed (we tried setting the database into single user mode with immediate rollback, also setting it into simple recovery mode, manually killing off SPIDs, nothing worked).
We were seeing pre-emptive database locks placed by the OS (presumably for the OS to ensure it could keep running), which seems to have been what was preventing us detaching the database in order to move it to a different drive.
We tried to stop the entire database instance, however this didn't work and eventually we literally pulled the plug on the server.
When it restarted we were able to immediately detach the database as the restart straightened out the OS. Moved the database, re-attached it and we're back in business :)
The database we moved is (initially) stuck in 'read only' state, however we do only read from it.


Run DELETE on each INSERT to enforce row limit Using PostgreSQL 11.
Consider a table like
CREATE TABLE "logs"
    (
      "id" INTEGER NOT NULL,
      "userId" INTEGER NOT NULL,
      "timestamp" TIMESTAMP NOT NULL,
      CONSTRAINT "PK_8d33b9f1a33b412e4865d1e5465" PRIMARY KEY ("id")
     )

Now, the requirement is that only 100 rows are stored per userId. If more data comes in, the oldest logs have to be deleted. If, for a short time, 101 rows are stored, it's not the end of the world, however. It's fine if the superfluous row gets deleted with a few seconds delay.
I cannot create a database TRIGGER. So, I need to write a query which is triggered on a log creation event on application layer.
Pure SQL is preferred over plpgsql.
This is the solution I came up with:
WITH "userLogs" AS (SELECT id, timestamp FROM "logs"
                    WHERE "userId" = $1
                ),
"countLogs" AS (SELECT count(id) FROM "userLogs")

DELETE FROM "logs" WHERE id = ANY
                (
                    SELECT id FROM "userLogs"
                    ORDER BY "timestamp" ASC
                    LIMIT GREATEST( (SELECT count FROM "countLogs") - 100, 0)
                );

Idea is: Always run a DELETE and base the decision if actually something has to be deleted on the LIMIT of a sub-query. If there are more than 100 logs, the sub-query will return the ids of the oldest ones to drop. Otherwise, LIMIT will be 0, the sub-query won't return anything and nothing gets deleted.
My questions are now:

Is it sensitive to run a DELETE query on each INSERT - even if it
doesn't delete anything?
Are there any performance implications here? (Or other pitfalls I might not be aware of?)
I am not quite sure if I need a LOCK. In my tests I could not
produce any unexpected behavior when running INSERTs in parallel,
however could it be that there are edge cases where I'd need a LOCK?

Edit: It's hard to predict how many times an INSERT will be run against that table. If all goes well (business-wise), it could be a few thousand times a day in sum - and a few dozens times per user each day.
Edit 2: timestamp values are not necessarily unique per user: there can be multiple log entries with the same timestamp and the same userId. It is expected that the table will get more columns containing what actually happened.

If you have an index on user_id, you can drop it and replace it with an index on (user_id,timestamp). This will also save a sort when displaying the latest log entries (WHERE user_id=... ORDER BY timestamp DESC LIMIT n).
Then:
SELECT timestamp FROM logs WHERE userid=1 ORDER BY timestamp DESC LIMIT 1 OFFSET 100

If there are more than 100 rows, this will return the timestamp of the 100th row. Otherwise it will return nothing. To delete the old logs for one user:
DELETE FROM logs WHERE userid=1 AND timestamp <=
(SELECT timestamp FROM logs WHERE userid=1 ORDER BY timestamp DESC LIMIT 1 OFFSET 100);

This is a very fast query. If the select doesn't find any rows to delete, it will be well under 1ms.
To delete all the old logs:
DELETE FROM logs
USING (SELECT userid, f.timestamp FROM users CROSS JOIN LATERAL (SELECT timestamp FROM logs WHERE logs.userid=users.userid ORDER BY timestamp DESC LIMIT 1 OFFSET 100) f) oldlogs
WHERE logs.userid=oldlogs.userid AND logs.timestamp<=oldlogs.timestamp;

This will probably seq-scan logs, so it could be slow. Here's a better one which will exploit the index on (userid,timestamp) and be fast if there is nothing to do:
DELETE FROM logs USING
( SELECT userid,timestamp FROM users
  CROSS JOIN LATERAL (SELECT timestamp FROM logs WHERE logs.userid=users.userid ORDER BY timestamp DESC OFFSET 100) oldlogs ) o
WHERE logs.userid=o.userid AND logs.timestamp=o.timestamp;

To answer your comment "what if many logs all have the same timestamp?"... Well this should never happen since if you want your logs to be useful, they should be ordered by something unique, otherwise you don't know in what order they were logged. But... you can simply use the primary key:
-- one user
DELETE FROM logs USING
( SELECT id FROM logs WHERE logs.userid=123 ORDER BY timestamp DESC, id DESC OFFSET 100 ) o
WHERE logs.id=o.id;

-- all users
DELETE FROM logs USING
( SELECT oldlogs.id FROM users
  CROSS JOIN LATERAL (SELECT id FROM logs WHERE logs.userid=users.userid ORDER BY timestamp DESC, id DESC OFFSET 100) oldlogs ) o
WHERE logs.id=o.id;

So if they have the same timestamp, the ORDER BY will keep the highest ids which should have been inserted last.


SQL Service Using Group Managed Service Account does not start after reboot We are using group managed service accounts for our SQL Server 2016 servers.  For some reason, when we reboot the server, the service does not start and we see this in the event viewer:

  The MSSQLSERVER service was unable to log on as ds\gsaNQSQLRSNSVC$ with the currently configured password due to the following error:
  The specified domain either does not exist or could not be contacted.

I then go to SSCM and start up the service and it starts up just fine.  The workaround for this is to start it with Automatic (Delayed Start).  I can't figure out why it won't just start up on reboot.  Does anyone know why this is happening?

The DCs are Win 2016 servers. The servers are in AWS and they are in the same VPC, the SQL Server and DC.

The information in Using a gMSA with SQL Server by Wayne Sheffield worked for me with the service issue.

  The pitfalls of using a gMSA with SQL Server

  As with almost all things, there is inevitably something that doesn’t work correctly. One thing that I found is that when the server is rebooted, the SQL Server services are not restarted. And I’m not the only person that has had this problem. After digging through the Windows event logs, we were able to figure out the issues. The service was not restarting because it was trying to do things before those services had been started. We were able to overcome this issue by creating service dependencies. A service dependency prevents the service (SQL Server) from starting up before other specified services are running.

See the linked article for full details.


SQL Server 2012 Edition Upgrade Error I know I can't have a direct answer to this previously written somewhere, because this is somehow an special situation.

I installed a SQL Server 2012 Business Intelligence last month. I have found some issues, and happly noticed there's a service pack 1 already.

On the other hand, I was asked to perform an Edition Upgrade from SQL Server 2012 BI to the Enterprise version.

Well, I'm not sure if the provided Edition Upgrade option that comes with the SQL Server 2012 installation will work after I applied the path.

So, seems like I should run the upgrade to Enterprise first, then install the patch, but I'm getting an error while trying to do this:

  SQL Server Setup has encountered the following error:

  Attributes do not match. Present attributes (Directory, Compressed) ,
  included attributes (0), excluded attributes (Compressed, Encrypted).

  Error code 0x84B40000.

If I go to the link I get:

  We're sorry
  There is no additional information about this issue in
  the Error and Event Log Messages or Knowledge Base databases at this
  time. You can use the links in the Support area to determine whether
  any additional information might be available elsewhere.

Well, I would be glad to get the Edition Upgrade first, but I can't find a lot of information about this error.

The error sounded very cryptic, but it resulted to be a simple solution:

http://databasebestpractices.com/attributes-match-present-attributes-directory-compressed-included-attributes-0-excluded-attributes-compressed-encrypted/

It meant that the target folder was compressed, I didn't notice before, so I uncompressed "c:\Program Files\Microsoft SQL Server" and the Edition Upgrade ended in 5 minutes succesfully.


How to change disk for "There is insufficient free space on disk volume"? Restore of database 'Configurations_6.2.2.25' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)

  ------------------------------ ADDITIONAL INFORMATION:

  System.Data.SqlClient.SqlError: There is insufficient free space on
  disk volume 'E:\' to create the database. The database requires
  22758293504 additional free bytes, while only 10663624704 bytes are
  available. (Microsoft.SqlServer.SmoExtended)

I am getting the above error when trying to restore a bak file I was given. It is not an option for the database to be shrunk before creating the bak.

I have 9.99GB free on E:\ and I have 59.7GB free on D:\

How can I change this to point to D:\ instead of E:\? and will changing the disk solve my problem? Thanks

EDIT: Solved my problem. In Restore Database -> File -> I changed the Log file folder path to D:\, then under Options I checked Override

Use RESTORE ... WITH MOVE instead of pointing and clicking in the GUI.

First, run RESTORE FILELISTONLY FROM DISK = 'D:\path\file.bak';

You will get output, like:

LogicalName    PhysicalName      ...
-----------    ---------------   ---------
SomeName1      E:\whatever\...
SomeName2      E:\whatever\...

For each LogicalName, add a MOVE clause.

RESTORE DATABASE [Configurations_6.2.2.25] -- terrible name, btw, for multiple reasons
  FROM DISK = 'D:\path\file.bak'
  WITH REPLACE, RECOVERY,
    MOVE 'SomeName1' TO 'D:\wherever\file.mdf',
    MOVE 'SomeName2' TO 'D:\wherever\file.ldf';


What are the challenges of administrating an International stock trading database? I am a student, and have a question for an assignment:

What problems and challenges do database administrators face when designing systems to trade international stocks in real time and 24 hours a day, 6 days a week? Are cloud based database systems as a service a viable option?

Because it's a system, you will have a LOT of interacting subsystems, including power, environmental controls, communications, hardware, and then on top of that the data management and everything else.

There are facets which cut across all these layers, like security, redundancy, business continuity, etc.

Just from the database side, you would need to consider the transactional load, the architecture for balancing the load, the storage requirements, the response time requirements.

Everything depends upon the requirements - of which there will be many from regulatory bodies, clients, users, business partners.

As to whether cloud-based databases, I don't think any of those can be viable if your organization has to be responsible for the system.

Any time you outsource anything, you limit your ability to be responsive to requirements.  Some things you delegate are easier to mitigate than others.  For instance, you decide to use a software library as a component in your system - you can always modify the library if you have the source, or rewrite it yourself or swap it out.  Or picking a RDBMS like Oracle or SQL Server where you can get a vendor to do a proof of concept and have a high level of support because the sale is large.

That's not to say by relaxing your requirements, you couldn't use Google Apps BigTable or Amazon or Azure or whatever, but the simple fact is that even with a contract, you have a limited amount of control over another organization's system and you are relying on them.

All systems are built by balancing tradeoffs.


How to restore a database from a newer version of SQL Server I have SQL Server 2008 and I want to restore a database from SQL Server 2008 R2. When I try to restore, SQL Server produces an error message that the database came from a newer version of SQL Server than the one I have.

Sorry, but backup and restore won't allow you to restore that way - you can only restore to a higher version (backwards compatibility) rather than a lower version (forwards compatibility?). You'll need to export and import the data, or use scripts, or some other way of transferring everything.


How to grant permissions to users based on table information? Say I have a table:

+------------------------+
|       Employees        |
+------+--------+--------+
| Dept |  Name  | Salary |
+------+--------+--------+
|    1 | John   |  10000 |
|    1 | Mary   |  50000 |
|    2 | Alfred |  30000 |
+------+--------+--------+

Now I create two views:

create view dept1_memberview as select Dept, Name from Employees where Dept=1;
create view dept2_memberview as select Dept, Name from Employees where Dept=2;

What I want to do is give access to each employee in the table (their usernames match their entry under the 'Name' column) to the view that corresponds to the department they're in. I know I could just do:

grant select on db.dept1_memberview to John, Mary;
grant select on db.dept2_memberview to Alfred;

But mostly out of curious, and for larger databases with hundreds of employees, I would like to know how I'd do that.

Thanks in advance!

Something like that could be achieved through dynamic sql, or via using stored procedures instead of views (for selecting) or triggers (for controlling inserts/updates).alter

But you can also create the memberview to return values according to the currently logged on user, which is probably more practical:

create view memberview as
  select Dept, Name
  from Employees
  where Dept= (
    select Dept
    from Employees
    where Name = coalesce(nullif(mid(current_user, 1, locate('@', current_user)-1), ''), current_user)
    );


Mongo doesn't restore user password I have following scenario:

Dump Mongo database (cmd mongodump)
Change password from one of admin users
Restore Mongo database (cmd mongorestore)

EDIT:

I use following command for db dump:

mongodump -h $HOST:$PORT --ssl --sslAllowInvalidCertificates --authenticationDatabase $authdb -u $user -p $pass --gzip --archive=$destination

As a result password was not restored. Is there any defect plan that can be followed in mongodb for this specific case?

So I found solution, --drop shoud be added in the command:

mongodump -h $HOST:$PORT --ssl --sslAllowInvalidCertificates --authenticationDatabase $authdb -u $user -p $pass --drop --gzip --archive=$destination


SSMS Error when creating database "Method not found .. StringCollection" I'm using SQL Server Management Studio 2016.
When I try to create a local database by right clicking on Databases and selecting "New Database" I get the following error:

I've tried restarting SSMS and the machine.

I can create the database through SQL, however I get the same error when trying to launch any GUI dialogs for the database. So creating it thru SQL doesn't work around my issue as I need to configure it also (and I don't want to write scripts for everything).

You have several options:

Make sure SSMS 2016 has the latest fixes for that version. Many people forget that tools need patching as well as server components. See Latest Builds of Management Studio for more information.

Consider updating SSMS to the latest major version, which is 17.2 at the time of writing. From that link:

This version of SSMS works with all supported versions of SQL Server 2008 - SQL Server 2017 and provides the greatest level of support for working with the latest cloud features in Azure SQL Database and Azure SQL Data Warehouse.

Perform an uninstall and reinstall. You could reinstall the same version if you like.


Compare words in a string without considering their positions? In Postgres 9.6 I want to test whether two strings like these are considered the same:

'this is a test number 01', 'number this is 01 a test'

So I have created this function:

CREATE OR REPLACE FUNCTION sort_text(a text) RETURNS text AS $$
    declare t1 text;
    BEGIN
      select(array_to_string (
        array(
          select * from unnest(string_to_array(a, ' ')) order by 1), ' ')) into t1;
      RETURN t1;
    END;

$$ LANGUAGE plpgsql;

select (sort_text('this is a test number 01') = sort_text('number this is 01 a test'));

which actually looks to be working correctly.

I was wondering, is there any better way to do this?

'this and this' and 'and this' are considered to be different.

All the strings are already stripped out (spaces and punctuation) and duplication is not a problem. String length 50 characters as max estimation.

I suggest a single SELECT in a plain SQL function:

CREATE OR REPLACE FUNCTION strings_equivalent(a text, b text)
  RETURNS bool AS
$func$
   SELECT a1 = b1
   FROM  (
      SELECT string_agg(w, ' ') AS a1
      FROM  (
         SELECT w
         FROM   unnest(string_to_array(a, ' ')) w
         ORDER  BY w
         ) a1
      ) a2
   , (
      SELECT string_agg(w, ' ') AS b1
      FROM  (
         SELECT w
         FROM   unnest(string_to_array(b, ' ')) w
         ORDER  BY w
         ) b1
      ) b2
   WHERE  length(a) = length(b)
   UNION ALL
   SELECT FALSE
   LIMIT 1;  -- for clarity, not needed
$func$ LANGUAGE sql IMMUTABLE;

Call:

SELECT strings_equivalent('this is a test number 01', 'number this is 01 a test');

This is assuming:

Duplicate words count like any other words
Separator is a single space
No leading or trailing spaces

The function returns NULL for any empty string or NULL input.

The UNION ALL construct is a shortcut to return FALSE immediately if the input strings don't have the same length and avoid more expensive processing. Related:

Return a value if no record is found
SQL order by query results in any arbitrary (but reproducible) manner

LIMIT 1 is not needed because the function only returns the first column of the first row anyway and ignores the rest if there are more rows.

IMMUTABLE (since the result never changes for the same input) helps performance with repeated evaluation and allows indexes on functional expressions.

You could use regexp_split_to_table(a, ' ') instead of unnest(string_to_array(a, ' ')), but regular expression functions are typically more expensive. (You can cover more sophisticated separator characters with the regex, though, like '\s+' for any white space). Related:

How to preserve the original order of elements in an unnested array?

BTW, your simple function sort_text() looks good. But use string_agg() instead of array_to_string(ARRAY(...)) in a simple SQL function. No variable assignment needed:

CREATE OR REPLACE FUNCTION sort_text(a text)
  RETURNS text AS
$$
   SELECT string_agg(w, ' ')
   FROM  (
      SELECT w
      FROM   unnest(string_to_array(a, ' ')) w
      ORDER  BY 1
      ) sub
$$ LANGUAGE sql IMMUTABLE;


Best practice for when the ideal number of columns is unknown I'm currently making a MySQL database to list products for an e-commerce site. Each product will have several sizes available which will each have their own dimensions and measurements (e.g. length, width, thickness) and pricing. However, different products, even within the same category, have different numbers of dimensions (some will have 2 widths at different sections, etc.) and there isn't necessarily a maximum number of measurements required to uniquely ID a product size. The options I can see are:

Create a table every time a product is created to account for how many size measurements it needs. Obviously a poor way to handle this.
Create a table with an arbitrarily large number of "measurement" columns. Leaves a lot of nulls if it's too large, and requires that I manually edit the table structure later if it's too small.
Create a table with two CSV varchars: one to identify pseudo-column names, the other for values. Sorting and searching by any value but the first would be largely impossible to my knowledge, but the information is all there.
Create two tables: one for the individual product size code (linking to the product of course), and another with some kind of "key-value" setup: one column for the product code, another for the name of the measurement (e.g. length), and a third for the value of that measurement. Necessitates more joins to get the same information as the other solutions, plus an extra column for an "index" if I want to keep sizing attributes in a particular order (or the size key could be an enum, I suppose).

The last solution seems most reasonable, but is there a better way to go about this?

Will you ever search by those parameters accross different products? It does not seem very probable to me.

If you do not need to manipulate those in sql, only retrieve them for any given product, then store it as xml/json/anything in one blob column (or dynamic column on MariaDB). You then can get one product with all variants and maybe "filter" them by user input in application code (there won't probably be hundreds of them).

But if you have to search/aggregate etc, then do the second table for them and use join or another query to retrieve them as needed. Join is fast, it is a core function of relational DB.


Auto update a column every 24 hours in Oracle database I want to reset the values of a column every 24 hours. Is it possible? The column contains numeric values.

It's a ticket reservation system. I need to reset the number of tickets.

Sure, just create a job. For example, update column every midnight:

begin
  dbms_scheduler.create_job
  (
   job_name             => 'UPDATE_TABLE',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'begin update table set column = value; commit; end;',
   start_date           => trunc(sysdate+1),
   repeat_interval      => 'FREQ=DAILY;BYHOUR=0;BYMINUTE=0',
   enabled              => TRUE,
   auto_drop            => FALSE
  );
end;
/


Read from partition in local cassandra node only I'm aware that I can read from any cassandra node and it acts as coordinator to read from the node containing a specific partition, but can I read data only from the partition which is on the node I'm connecting to?

In other words, when a cassandra cluster has 10 nodes, it contains 10 partitions on each node (and maybe replicas on other nodes when RF is set). When I send a SELECT * FROM TABLE I would like to get only 1/10th of the total data, which is really stored on that specific node without any traffic to other nodes.

Thank you so much!

You can do it as following (classes names, etc. are for driver 3.x, could be slightly different in 4.x):

Identify what token ranges are handled by specific node - you can obtain this information from Metadata class as cluster.getMetadata().getTokenRanges(keyspace, host) (see doc);
for every token range repeat:

generate the query like select * from table where token(part_keys) > rangeStart  AND token(part_keys) <= rangeEnd (but this may not work all the time, as you need to handle cases when node's token range is split between end of token ring and begin of token ring - see linked code)
create an instance of Statement for each query (for example, as SimpleStatement), and set consistency level  to LOCAL_ONE and host to which query should be sent (via setHost function)
perform query using the execute or executeAsync functions and process data

Source code that performs a full cluster scan could be found here - you can reuse pieces of it regarding generation of queries on token ranges, etc.


intermittent TNS-12533: TNS:illegal ADDRESS parameters I am running a windows XP (SP3) VM in virtualbox, on which I host Oracle XE.

It has been running fine like this for a couple of years, but recently I am seeing some strange problems.

My client software fails, and the log files say :
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

So I log on to the DB, and try running sqlplus / as sysdba;

which gives me the following:
ora-12560 tns protocol adapter error

So following the debugging steps from several websites, I try this:
tnsping db
and the result is :
TNS-12533: TNS:illegal ADDRESS parameters

So I decide to test another SID:
tnsping xe
which works fine.

As you can see from the tnsnames.ora file, those two SIDs are configured identically.

I've not touched that file in a couple of years and don't intend to do so now.

So I restart the windows machine, and now everything works.

Why did the error message indicate a problem with the file, when upon restart it doesn't complain?

Why would this suddenly stop working?

I checked the alert log and found this:

ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process

I've tried wiping some files from the hard disk (there was 1GB free space, which seems like plenty) but it hasn't worked.

Any ideas?

The contents of my tnsnames.ora file include:

XE =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = windows-virtual)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = XE)
  )
)

DB=
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = windows-virtual)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = DB)
   )
 )

Try any one or both

Try using IP for HOST
Also remove (SERVER = DEDICATED) row from tnsname.ora file.


my azure sql database is not connecting with SSMS 2014 The firewall contain my IP in valid list but I am still getting error in connection. I tried the command

C:\Users\MianAsbat>SQLCMD -U username -P password -S

and got the following response. I am trying from windows 8 virtual machine on mac. Please help. Thanks.

  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server:
  Named Pipes Provider: Could not open a connection to SQL Server [67].
  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server:
  Login timeout expired.
  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server:
  A network-related or in stance-specific error has occurred while establishing
  a connection to SQL Server. Server is not found or not accessible.
  Check if instance name is correct and if SQL Server is configured to allow
  remote connections. For more information see  SQL Server Books Online..

You should insert the database name:

sqlcmd -U ProvideLogin@Server -P ProvidePassword -S ProvideServerName -d ProvideDatabaseName

Client quick-start code samples to SQL Database


Significant performance differences executing alter and grant statements between users with same permissions I am experiencing significant performances differences between users with the same permissions when executing alter and grant statements.
I have an automated build system that runs a series of SQL scripts for database changes - mostly alter table, alter procedure, with relevant grant statements.
While trying to change some things in my build process (including running under a different user account), I noticed that the current account running the scripts executes them up to 100x faster than any other user - even users with exactly the same permissions, and belonging to the same Active Directory group. I've tested SQL logins and Windows Authentication, with both results being basically identical.
The build process is run through a C# script in all cases. The script properly opens the connection only once, executes all the statements, then closes. This happens the same for all users. The script itself is called by a batch file, which in turn is called by a CI tool.
The average execution time for the current build user running this script is 12ms per statement, while the average execution time for any other user is 275ms per statement.
What possible causes could be contributing to this performance difference?

TL;DR; Check database triggers
It turns our this was a very narrow, unique issue - but one that others might experience in a similar setup.
The performance issue was being caused by a database trigger that was called on grant, create, and alter statements, and based on the user, executed additional (sometimes heavy) logic.
These triggers were running extra logic for the accounts I was testing with, but not the original account.


Assuming this is SQL Server, you can truncate datetime values to hours using this technique:

DATEADD(HOUR, DATEDIFF(HOUR, 0, @YourDateTimeValue), 0)

Using that as a grouping criterion, you can then use the FIRST_VALUE analytic function (assuming you are on SQL Server 2012 or later version) to get the first DATEDIFF(MINUTE, Created, Updated) per group:

SELECT DISTINCT
  x.CreatedHour,
  DiffMinutes = FIRST_VALUE(DATEDIFF(MINUTE, t.Created, t.Updated))
                OVER (PARTITION BY x.CreatedHour
                      ORDER BY t.Created ASC
                      ROWS UNBOUNDED PRECEDING)
FROM
  dbo.YourTable AS t
  CROSS APPLY
  (
    SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, t.Created), 0)
  ) AS x (CreatedHour)
;

Since the FIRST_VALUE function would return the result for every row of your table, it would return identical values for entries created in the same hour, thus giving you a lot of duplicates. It is to suppress those duplicates that the above query is using DISTINCT.