pgadmin4 fails to start on fedora 27 (pgadmin4:10053): Gtk-WARNING **: Theme parsing error: gtk.css:8645:2: Junk at end of value for padding-left

(pgadmin4:10053): Gtk-WARNING **: Theme parsing error: gtk.css:13132:11: 'shadowbox' is not a valid property name

Can someone please help figure out why? Also help me with a solution.

Solved it! All, I had to do was enable a shell extension "Topicons plus" from gnome tweak.


FORALL on Nested Collections Suppose I have the following two tables in my Oracle database:

create table a (num number);
create table b (val varchar2(30);

and in my PL/SQL program I have declared a nested collection as follows:

declare
        -- type for inner collection; shadows table b
        type inner_tab_t is table of varchar2(30);

        -- record type for outer table
        type outer_tab_rec is record
        (
            num  number,
            vals inner_tab_t
        );

        -- type for outer table
        type outer_tab_t is table of outer_tab_rec;

        -- actual inner_tab_t
        inner_tab inner_tab_t;

        -- actual outer_tab_t
        outer_tab outer_tab_t;

I am wondering whether it is possible to use a FORALL statement to insert the data from outer_tab into both tables a and b?

In other words, given the following assignments:

begin
        inner_tab := inner_tab_t('one', 'two', 'three');

        outer_tab := outer_tab_t();

        outer_tab.extend;

        outer_tab(outer_tab.last).num := 1;
        outer_tab(outer_tab.last).vals := inner_tab;

I'd like to do something like this (obviously doesn't work):

 forall ind in outer_tab.first .. outer_tab.last
            insert into a (num)
                values(outer_tab(ind).num)
                forall ind2 in outer_tab(ind).vals.first .. outer_tab(ind).vals.last
                    insert into b (val)
                        values (outer_tab(ind).vals(ind2);

Is there a convenient way of handling such a situation?

It is possible to do this in a single FORALL statement, provided you create the nested tables as SQL objects. This allows you to reference them as tables with the SQL. You can then use INSERT ALL to do a multi-table insert:

create or replace type t_inner_tab_t as table of varchar2(30);
/

create or replace type outer_tab_t as object (
  num number,
  vals  t_inner_tab_t
);
/

create or replace type t_outer_tab_t is table of outer_tab_t;
/

declare
  inner_tab t_inner_tab_t;
  outer_tab t_outer_tab_t;
begin
    inner_tab := t_inner_tab_t('one', 'two', 'three');

    outer_tab := t_outer_tab_t();

    outer_tab.extend;

    outer_tab(outer_tab.last) := outer_tab_t(1, inner_tab);

    forall i in outer_tab.first .. outer_tab.last
      insert all
        when r = 1 then
          into a (num) values (o)
        when r  >= 1 then
          into b (val) values (c)
        select column_value c, outer_tab(i).num o,
               row_number() over (partition by outer_tab(i).num order by outer_tab(i).num) r
        from   table(cast(outer_tab(i).vals as t_inner_tab_t));

end;
/

SELECT * FROM a;

NUM
---
  1

SELECT * FROM b;

VAL
------------------------------
one
two
three

The row_number() clause is to enable the conditional insert (when r = 1). Without this, you'll insert into a for every value present in your outer_tab.


How to grant multiple users privileges; MySQL I have the following code:

GRANT SELECT,INSERT,UPDATE ON db1.*
TO ‘user’@example.com;

I am a beginner in mySQL, I was wondering if there is a method in which I can specific the privileges for every user in this domain network without having to type the above code for each user? %wildcard?

Also is there any method such as using a table where I can manage all the privileges of said users

The GRANT syntax allows you to specify multiple users in a single query. You can't do it with a wildcard, but if you already have the list you can do this:

GRANT SELECT,INSERT,UPDATE ON db1.*
TO 'user1', 'user2', 'user3', 'user4', 'user5', 'user6';


Replication has failed; how to get going on again? I'm running Postgres 9.1.6 on Ubuntu and I have streaming replication setup between a master and slave. Everything has been running smoothly until the database crashed and we had to restart both of the boxes.

Now, replication has stopped and when checking the logs on both boxes, I see this message:

CDT FATAL:  requested WAL segment 0000000100000224000000FA has already been removed

It's the same segment over and over again. From my Googling, it would seem that the replication server is trying to retrieve this segment from the master, but it's not there anymore. Ok, but how to get around this? Do I need to make a fresh backup and rsync that over to the slave? Is there an easy way to get the slave back in sync?

Yes you will have to give the slave a new base backup (for streaming replication only steps 1 to 4) of the master.

Your problem has probably occured because the value of wal_keep_segments is to low. The value needs to be high enough that when the slave is down for some time the master won't start recycling segments the slave hasn't processed yet.


create index - invalid memory alloc request size i get this error "invalid memory alloc request size 18446744072635840654. SQL Status:XX000" when i want to create an index.

The Table looks like this:

CREATE TABLE schema.tblname
(
  id bigint NOT NULL DEFAULT nextval(('schema.tblname_id_seq'::text)::regclass),
  column_1 integer,
  column_2 character varying(20),
  ... 8 other columns ...,
  CONSTRAINT tblname_pkey PRIMARY KEY (id),
  CONSTRAINT tlbname_fk FOREIGN KEY (column_1)
      REFERENCES schema.tblname2 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

Server version:

PostgreSQL 8.3.8 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)

Hardware (Virtual Machine):

4 Cores, 16GB RAM, 700GB SAN HDD

ulimit -a:

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 139264
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 139264
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

The History:
We had a crash of the Raid-Controller in the SAN. The redundant Server/SAN prevented a failure of the DB but we had corrupted data in the table.
Then i have searched the table for corrupted data and deleted the rows from the table. Now i want to recreate the index to be sure to have a correct index.

I wanted to create following index:

CREATE INDEX tlbname_column1_column2x
  ON schema.tlbname
  USING btree
  (column_1, column_2);

This was not possible because the following error occurred:

index row requires 2286728 bytes, maximum size is 8191

So i made an index on column_1, this worked fine and deleted it after that.
Then i tried to create an index on column_2 and the error "index row requires 2286728 bytes, maximum size is 8191" occurred.
I searched the table for rows where the octet_length was greater then 20 (because of charvar 20 on column_2) and deletet the lines (approx. 40k rows).

Now when i try to create the index on column_2 it fails with the error "invalid memory alloc request size 18446744072635840654. SQL Status:XX000"

Table info:

rows in the table: aprrox. 1.841.170.000
tablesize: 197GB

The index

CREATE INDEX tlbname_column1_column2x
  ON schema.tlbname
  USING btree
  (column_1, column_2);

already exists since the creation of the table and it worked without any problems. But the recreation does not work.

Any suggestions what i can try or do?
Thanks.

This sort of problem almost certainly means damaged table information, either regarding table headers, page headers, or tuple headers.  Your best option is to dump and restore, or to restore from backup before the crash.

Wish I had a better answer for you but you have to consider that you have fixed some problems and now there are others.  You have to assume that there will be others still.


How can I recover my tables in sql server database I deleted 3 tables from my database with data and I have no backup. How can I restore tables with data in my database ?

How can I restore tables and data with database log file?(Database recovery model is full)

Yes, as Iren and Rahul said, you can use ApexSQL tools. If the tables were dropped, ApexSQL Recover can recover them even from databases in the simple recovery model. ApexSQL Recover can recover both table structure and table records

On the other hand, ApexSQL Log cannot recover records lost when a table was dropped, it can only recover the table structure

In case the records that were lost using DELETE (not DROP TABLE), both ApexSQL Log and ApexSQL Recover can help

The advantages of ApexSQL tools over recovery to a point in time is that ApexSQL will recover just the tables you specify (creates CREATE TABLE and INSERT INTO scripts) , while a point-in-time recovery will roll back all the transactions that happened in the meantime

A DROP TABLE statement marks the MDF file pages used by the dropped table for deallocation. These pages are actually still in the MDF file until overwritten by new operations. To prevent new operations overwrite the data necessary for successful recovery, we recommend creating a copy of the original MDF and LDF files immediately

Disclaimer: I work for ApexSQL as a Support Engineer


When and who shrunk the ldf file? I’m trying to find information about when and who shrunk a ldf file but I can't find the information anywhere.

Can someone point me in the right direction?

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4266.0 (X64)
    Sep 26 2012 17:08:07
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Below is a sample query to return information from the default trace using T-SQL.

SELECT
     trace.DatabaseName
    ,trace.ObjectName
    ,te.name AS EventName
    ,tsv.subclass_name
    ,trace.EventClass
    ,trace.EventSubClass
    ,trace.StartTime
    ,trace.EndTime
    ,trace.NTDomainName
    ,trace.NTUserName
    ,trace.HostName
    ,trace.ApplicationName
    ,trace.Spid
FROM (SELECT REVERSE(STUFF(REVERSE(path), 1, CHARINDEX(N'\', REVERSE(path)), '')) + N'\Log.trc' AS path
    FROM sys.traces WHERE is_default = 1) AS default_trace_path
CROSS APPLY fn_trace_gettable(default_trace_path.path, DEFAULT) AS trace
LEFT JOIN sys.trace_events AS te ON
    trace.EventClass=te.trace_event_id
LEFT JOIN sys.trace_subclass_values AS tsv ON
    tsv.trace_event_id = EventClass
    AND tsv.subclass_value = trace.EventSubClass
ORDER BY trace.StartTime;

By filtering on WHERE TEXTDATA LIKE 'DBCC SHRINK%' you will find all Shrink events.


How to find referencing tables? I'm on SQL Server 2014 and I have a User table:

+----+------+-----+
| ID | Name | ... |
+----+------+-----+
| 11 | John | ... |
| 12 | Jack | ... |
| .. | .... | ... |
+----+------+-----+

And I have many tables which references this one. Can I get a list of all tables which references this table? And, in particular, can I get a list of all tables which references a particular row on this table?

I will then need to update all references to User 12 to User 11.

When you say "a list of tables which reference this table" do you mean a list of tables that have a foreign key that reference your primary key? If so
you can get a list of foreign keys that reference your table 'User' with the following query:

SELECT name as Foreign_Key
,schema_name(schema_id) as Schema_Name
,object_name(parent_object_id) as Table_Name
FROM sys.foreign_keys
WHERE Referenced_object_id = object_id('dbo.user','U');

if your table belongs to a different schema other than dbo then replace the schema name.

This query will give you all the referenced foreign keys to your table User. You will now know what joins you need to make in your update statement to maintain referential integrity.

Edit: woahhhhhh screw my answer,
The best answer by db2 gives more detail in his query.
How to Find the Foreign Key Associated with a Given Primary Key

Add a WHERE clause to his query before the ORDER BY clause for a specific table.

ex. WHERE o2.name = 'User'


MySQL / Fusion IO Configuration Question I need a little configuration optimization. I think what I have is pretty good but feel like there's still room for improvement.

Current Config

[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#innodb
innodb_log_file_size = 256M
innodb_log_buffer_size = 32M

innodb_read_io_threads=8
innodb_write_io_threads=8

innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout=50
innodb_file_per_table
innodb_buffer_pool_size=16G
innodb_buffer_pool_instances=8
innodb_io_capacity=10000
#eliminating double buffering
innodb_flush_method = O_DIRECT
flush_time=86400

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
#tmpdir=/dev/shm
#tmpdir=/usr/tmpfs
skip-name-resolve
#skip-locking
#safe-show-database
query_cache_limit=4M
query_cache_size=256M
sort_buffer_size=8M
read_rnd_buffer_size=1M
max_connections=5000
interactive_timeout=60
wait_timeout=300
connect_timeout=30
thread_cache_size=32
key_buffer=124M
tmp_table_size=4096M
max_heap_table_size=256M
join_buffer=16M
max_connect_errors=2000
table_cache=2048
thread_concurrency=12
long_query_time=5
log-slow-queries=/var/log/mysql-slow.log
#table_definition_cache=384
max_allowed_packet=1024M
#server-id=20
#log-bin=mysql-bin
#expire_logs_days=10

event_scheduler=ON

#master-host =
#master-user =
#master-password =
#master-port = 3306

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Dedicated Database server specs

VMWare VM
Dual Quad Xeon X5680  @ 3.33GHz (8 vCPU)
24gb RAM
Fusion IO mounted on /var/lib/mysql
Rough total of 500 tables between all databases

I know this kind of question really kind of requires eyes on the database server but I will gladly append information to this question as needed in order to get a much better configuration.

Thanks for the help in advance.

This is similar to the question I answered two months ago : MySQL using too much CPU

FusionIO tends to be CPU aggressive. You will have to fight back by

Tuning InnoDB

more read threads (innodb_read_io_threads), perhaps 16
more write threads (innodb_write_io_threads), perhaps 16
raising or lowering innodb_io_capacity to match IOPs of your FusionIO device
raise the Log Buffer (innodb_log_buffer_size) to 1G
raise the Log File (innodb_log_file_size) to 128M

Converting as much of your MyISAM to InnoDB as possible because changes to .MYD are cached in the OS memory rather than any MySQL buffers

I would also lower max_connections to 2000 unless you know for a certainty to will get over 4500 simultaneous DB Connections. If you really need 5000, upgrade the VM's RAM to 48GB.

Give it a Try !!!


Optimise a LATERAL JOIN query on a big table I'm using Postgres 9.5. I have a table that records page hits from several web sites. This table contains about 32 million rows spanning from Jan 1, 2016 to June 30, 2016.

CREATE TABLE event_pg (
   timestamp_        timestamp without time zone NOT NULL,
   person_id         character(24),
   location_host     varchar(256),
   location_path     varchar(256),
   location_query    varchar(256),
   location_fragment varchar(256)
);

I'm trying to tune a query that counts the number of people that performed a given sequence of page hits. The query is meant to answer questions like "how many people viewed the home-page, and then went to the help site and then viewed the thank-you page"? The result looks like this

╔════════════╦════════════╦═════════════╗
║  home-page ║ help site  ║ thankyou    ║
╠════════════╬════════════╬═════════════╣
║ 10000      ║ 9800       ║1500         ║
╚════════════╩════════════╩═════════════╝

Notice the numbers are decreasing which makes sense, because of the 10000 who viewed the home-page 9800 went on to the help site and of those 1500 went on to hit the thank you page.

The SQL for a 3 step sequence uses lateral joins as follows:

SELECT
  sum(view_homepage) AS view_homepage,
  sum(use_help) AS use_help,
  sum(thank_you) AS thank_you
FROM (
  -- Get the first time each user viewed the homepage.
  SELECT X.person_id,
    1 AS view_homepage,
    min(timestamp_) AS view_homepage_time
  FROM event_pg X
  WHERE X.timestamp_ between '2016-04-23 00:00:00.0' and timestamp '2016-04-30 23:59:59.999'
  AND X.location_host like '2015.testonline.ca'
  GROUP BY X.person_id
) e1
LEFT JOIN LATERAL (
  SELECT
    Y.person_id,
    1 AS use_help,
    timestamp_ AS use_help_time
  FROM event_pg Y
  WHERE
    Y.person_id = e1.person_id AND
    location_host = 'helpcentre.testonline.ca' AND
    timestamp_ BETWEEN view_homepage_time AND timestamp '2016-04-30 23:59:59.999'
  ORDER BY timestamp_
  LIMIT 1
) e2 ON true
LEFT JOIN LATERAL (
  SELECT
    1 AS thank_you,
    timestamp_ AS thank_you_time
  FROM event_pg Z
  WHERE Z.person_id = e2.person_id AND
    location_fragment =  '/file/thank-you' AND
    timestamp_ BETWEEN use_help_time AND timestamp '2016-04-30 23:59:59.999'
  ORDER BY timestamp_
  LIMIT 1
) e3 ON true;

I have an index on timestamp_, person_id and the location columns.
Queries on date ranges of a few days or weeks are very fast (1s to 10s). Where it gets slow is when I try to run the query for everything between Jan 1st & July 30. It takes over a minute. If you compare the two explains below you can see it no longer uses the timestamp_ index and instead does a Seq Scan because the index wouldn't buy us anything since we're querying "all time" hence pretty much all the records in the table.

Explain for a few days date range (uses index):
https://explain.depesz.com/s/2tOi
Explain for a 6 months (seq scan):
https://explain.depesz.com/s/c0yq

Now I realise the nested loop nature of the lateral join is going to slow down the more records it has to loop through but is there any way I can speed up this query for huge date ranges so that it scales better?

Preliminary notes

You are using odd data types. character(24)? char(n) is an outdated type and almost always the wrong choice. You have indexes on person_id and join on it repeatedly. integer would be much more efficient for multiple reasons. (Or bigint, if you plan to burn more than 2 billion rows over the lifetime of the table.) Related:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

LIKE is pointless without wildcards. Use = instead. Faster.
x.location_host LIKE '2015.testonline.ca'
x.location_host = '2015.testonline.ca'
Use count(e1.*) or count(*) instead of adding a dummy column with the value 1 for each subquery. (Except for the last (e3), where you don't need any actual data.)
You're inconsistent in sometimes casting the string literal to timestamp and sometimes not (timestamp '2016-04-30 23:59:59.999'). Either it makes sense, then do it all the time, or it doesn't, then don't do it.
It doesn't. When compared to a timestamp column, a string literal is coerced to timestamp anyway. So you don't need an explicit cast.
The Postgres data type timestamp has up to 6 fractional digits. Your BETWEEN expressions leave corner cases. I replaced them with less error-prone expressions.

Indexes

Important: to optimize performance create multicolumn indexes.
For the first subquery hp:

CREATE INDEX event_pg_location_host_timestamp__idx
ON event_pg (location_host, timestamp_);

Or, if you can get index-only scans out of it, append person_id to the index:

CREATE INDEX event_pg_location_host_timestamp__person_id_idx
ON event_pg (location_host, timestamp_, person_id);

For very large time ranges spanning most or all of the table, this index should be preferable - it also supports the hlp subquery, so create it either way:

CREATE INDEX event_pg_location_host_person_id_timestamp__idx
ON event_pg (location_host, person_id, timestamp_);

For tnk:

CREATE INDEX event_pg_location_fragment_timestamp__idx
ON event_pg (location_fragment, person_id, timestamp_);

Optimized with partial indexes

If your predicates on location_host and location_fragment are constants, we can use much cheaper partial indexes instead, especially since your location_* columns seem big:

CREATE INDEX event_pg_hp_person_id_ts_idx ON event_pg (person_id, timestamp_)
WHERE  location_host = '2015.testonline.ca';

CREATE INDEX event_pg_hlp_person_id_ts_idx ON event_pg (person_id, timestamp_)
WHERE  location_host = 'helpcentre.testonline.ca';

CREATE INDEX event_pg_tnk_person_id_ts_idx ON event_pg (person_id, timestamp_)
WHERE  location_fragment = '/file/thank-you';

Consider:

Multicolumn index and performance
Is a composite index also good for queries on the first field?

Again, all of these indexes are substantially smaller and faster with integer or bigint for person_id.

Generally, you need to ANALYZE the table after creating a new index - or wait till autovacuum kicks in to do it for you.

Index that is not used, yet influences query
PostgreSQL partial index unused when created on a table with existing data

To get index-only scans, your table has to be VACUUM'ed enough. Test immediately after VACUUM as proof of concept. Read the linked Postgres Wiki page for details if you are unfamiliar with index-only scans.

Basic Query

Implementing what I discussed. Query for small ranges (few rows per person_id):

SELECT count(*)::int           AS view_homepage
     , count(hlp.hlp_ts)::int AS use_help
     , count(tnk.yes)::int     AS thank_you
FROM  (
   SELECT DISTINCT ON (person_id)
          person_id, timestamp_ AS hp_ts
   FROM   event_pg
   WHERE  timestamp_ >= '2016-04-23'
   AND    timestamp_ <  '2016-05-01'
   AND    location_host = '2015.testonline.ca'
   ORDER  BY person_id, timestamp_
   ) hp
LEFT JOIN LATERAL (
   SELECT timestamp_ AS hlp_ts
   FROM   event_pg y
   WHERE  y.person_id = hp.person_id
   AND    timestamp_ >= hp.hp_ts
   AND    timestamp_ <  '2016-05-01'
   AND    location_host = 'helpcentre.testonline.ca'
   ORDER  BY timestamp_
   LIMIT  1
   ) hlp ON true
LEFT JOIN LATERAL (
   SELECT true AS yes                   -- we only need existence
   FROM   event_pg z
   WHERE  z.person_id = hp.person_id    -- we can use hp here
   AND    location_fragment = '/file/thank-you'
   AND    timestamp_ >= hlp.hlp_ts      -- this introduces dependency on hlp anyways.
   AND    timestamp_ <  '2016-05-01'
   ORDER  BY timestamp_
   LIMIT  1
   ) tnk ON true;

DISTINCT ON is often cheaper for few rows per person_id. Detailed explanation:

Select first row in each GROUP BY group?

If you have many rows per person_id (more likely for bigger time ranges), the recursive CTE discussed in this answer in chapter 1a can be (much) faster:

Optimize GROUP BY query to retrieve latest record per user

See it integrated below.

Optimize & automate best query

It's the old conundrum: one query technique is best for a smaller set, another for a larger set. In your particular case we have a very good indicator from the start - the length of the given time period - which we can use to decide.

We wrap it all in a PL/pgSQL function. My implementation switches from DISTINCT ON to rCTE when the given time period is longer than a set threshold:

CREATE OR REPLACE FUNCTION f_my_counts(_ts_low_inc timestamp, _ts_hi_excl timestamp)
  RETURNS TABLE (view_homepage int, use_help int, thank_you int) AS
$func$
BEGIN

CASE
WHEN _ts_hi_excl <= _ts_low_inc THEN
   RAISE EXCEPTION 'Timestamp _ts_hi_excl (1st param) must be later than _ts_low_inc!';

WHEN _ts_hi_excl - _ts_low_inc < interval '10 days' THEN  -- example value !!!
-- DISTINCT ON for few rows per person_id
   RETURN QUERY
   WITH hp AS (
      SELECT DISTINCT ON (person_id)
             person_id, timestamp_ AS hp_ts
      FROM   event_pg
      WHERE  timestamp_ >= _ts_low_inc
      AND    timestamp_ <  _ts_hi_excl
      AND    location_host = '2015.testonline.ca'
      ORDER  BY person_id, timestamp_
      )
    , hlp AS (
      SELECT hp.person_id, hlp.hlp_ts
      FROM   hp
      CROSS  JOIN LATERAL (
         SELECT timestamp_ AS hlp_ts
         FROM   event_pg
         WHERE  person_id = hp.person_id
         AND    timestamp_ >= hp.hp_ts
         AND    timestamp_ < _ts_hi_excl
         AND    location_host = 'helpcentre.testonline.ca'  -- match partial idx
         ORDER  BY timestamp_
         LIMIT  1
         ) hlp
      )
   SELECT (SELECT count(*)::int FROM hp)   -- AS view_homepage
        , (SELECT count(*)::int FROM hlp)  -- AS use_help
        , (SELECT count(*)::int            -- AS thank_you
           FROM   hlp
           CROSS  JOIN LATERAL (
              SELECT 1                     -- we only care for existence
              FROM   event_pg
              WHERE  person_id = hlp.person_id
              AND    location_fragment = '/file/thank-you'
              AND    timestamp_ >= hlp.hlp_ts
              AND    timestamp_ < _ts_hi_excl
              ORDER  BY timestamp_
              LIMIT  1
              ) tnk
           );

ELSE
-- rCTE for many rows per person_id
   RETURN QUERY
   WITH RECURSIVE hp AS (
      (  -- parentheses required
      SELECT person_id, timestamp_ AS hp_ts
      FROM   event_pg
      WHERE  timestamp_ >= _ts_low_inc
      AND    timestamp_ <  _ts_hi_excl
      AND    location_host = '2015.testonline.ca'  -- match partial idx
      ORDER  BY person_id, timestamp_
      LIMIT  1
      )
      UNION ALL
      SELECT x.*
      FROM   hp, LATERAL (
         SELECT person_id, timestamp_ AS hp_ts
         FROM   event_pg
         WHERE  person_id  > hp.person_id  -- lateral reference
         AND    timestamp_ >= _ts_low_inc  -- repeat conditions
         AND    timestamp_ <  _ts_hi_excl
         AND    location_host = '2015.testonline.ca'  -- match partial idx
         ORDER  BY person_id, timestamp_
         LIMIT  1
         ) x
      )
    , hlp AS (
      SELECT hp.person_id, hlp.hlp_ts
      FROM   hp
      CROSS  JOIN LATERAL (
         SELECT timestamp_ AS hlp_ts
         FROM   event_pg y
         WHERE  y.person_id = hp.person_id
         AND    location_host = 'helpcentre.testonline.ca'  -- match partial idx
         AND    timestamp_ >= hp.hp_ts
         AND    timestamp_ < _ts_hi_excl
         ORDER  BY timestamp_
         LIMIT  1
         ) hlp
      )
   SELECT (SELECT count(*)::int FROM hp)   -- AS view_homepage
        , (SELECT count(*)::int FROM hlp)  -- AS use_help
        , (SELECT count(*)::int            -- AS thank_you
           FROM   hlp
           CROSS  JOIN LATERAL (
              SELECT 1                     -- we only care for existence
              FROM   event_pg
              WHERE  person_id = hlp.person_id
              AND    location_fragment = '/file/thank-you'
              AND    timestamp_ >= hlp.hlp_ts
              AND    timestamp_ < _ts_hi_excl
              ORDER  BY timestamp_
              LIMIT  1
              ) tnk
           );
END CASE;

END
$func$  LANGUAGE plpgsql STABLE STRICT;

Call:

SELECT * FROM f_my_counts('2016-01-23', '2016-05-01');

The rCTE works with a CTE by definition. I also slipped in CTEs for the DISTINCT ON query (like I discussed with @Lennart in the comments), which allows us to use CROSS JOIN instead of LEFT JOIN to reduce the set with each step, since we can count each CTE separately. This has effects working in opposite directions:

On the one had we reduce the number of rows which should make the third join cheaper.
On the other hand we introduce overhead for the CTEs and need considerably more RAM, which may be particularly important for big queries like yours.

You'll have to test which outweighs the other.


Migrating every table to a new linked server has too many prefixes Unfortunately, I can't backup and restore, since the new database is on an older version of SQL.

When I run the following script, I get this error:

    Msg 117, Level 15, State 1, Line 1
    The object name 'eih-dr01.eih.ehs.org.DBA.dbo.fp_monthly' contains more than the maximum number of prefixes. The maximum is 2.

    declare @table varchar(255),
    @sql nvarchar(max)

    declare c cursor local for

    select st.name from sys.tables st

    open c

    fetch next from c into @table

    set @sql = 'select * into [eih-dr01.eih.ehs.org].DBA.dbo.' + @table + ' from ' + @table

    print @sql
    exec sp_executesql @sql

    fetch next from c into @table

    close c
    deallocate c

Question: Which prefix is superfluous?

Actual Question Why aren't my '[',']' charachters showing up in my dynamic sql statement?

According to SELECT - INTO Clause

  You cannot create new_table on a remote server using SELECT INTO

In other words, the superfluous prefix is the remote server

You could probably achieve your goal by creating empty tables on the remote server and alter your dynamic SQL to

INSERT INTO server.database.schema.table select from source


Reclaiming space after delete statement in SQL Server I have some tables used in reporting and some of them grow huge with the daily ETL so I have implemented som jobs that delete rows more that x days old. E.g. everyday 10% new data gets added and I delete 10% of the oldest rows in another job.
Do I need to do something for efficiency? Some tables are heaps with nonclustered indexes and some also have clustered indexes. E.g. to I need to rebuild the tables in case of heaps or rebuild index in case of clustered index on the table? If so how often? Most tables are only used once per day and when all joins and calculations are done they get extracted for visualization in a non-live manner.
I know a little about index fragmentation and heap fragmentation and that one can query fragmentation in percent.
How much do I need to worry about the above scenario?

You can identify and resolve heap / index fragmentation following this guide: How to identify and resolve SQL Server Index Fragmentation
You likely only need to reorganize, and not worry so much about rebuilding as often. There's recommendations for when to use one over the other, but rebuilding is a heavier operation that likely doesn't result in much more benefit than reorganizing.
While 20 million rows is a nice little chunk of data, it isn't anything enormous by any means. I would still potentially recommend always using clustered indexes and you'll see the best benefits during the JOINs and calculations on those tables.
Using clustered indexes in a smart way will also reorganize your data for you in a sense. What I mean by that, is if every day you're loading 20 million records into an empty table, and then add a clustered index on that table after the data has been fully loaded, it'll efficiently organize the data sorted on that clustered index. It can basically take the place of manually rebuilding / reorganizing operations that are pretty heavy operations by themselves. Adding the clustered index to the table after the data has been fully loaded is usually the most efficient way for it to reorganize the data.


SQL Server 2008R2 - Replication - Log Files / Indexes We have 4 SQL Servers engaged in Transactional Peer to Peer Replication. I am doing some work on the Indexes and have noticed 2 things I didn't expect:

The Indexes have different fragmentation levels - I understand that only the Insert/Update/Delete commands are replicated - but wouldn't they therefore create the same level of fragmentation on each server? (They're not wildly different, but enough to catch the eye and change the order of priority (i.e. 99% at the top etc))
The Log Files are wildly different sizes (the smallest is 10Gb the largest around 85Gb)- again, I was expecting them to be similar/spot on the same, surely if the log has recorded and performed the same actions due to the replication they would be similar?

Any thoughts/wisdom?

Thanks

MH

but wouldn't they therefore create the same level of fragmentation on each server?

No. Fragmentation is physical, replication is logical. The same sequence of insert/delete/update may produce different physical indexes, but transnational replication may not even produce the same sequence of insert/deletes/updates. Even with non-filtered publications, the replication agent can fail, rollback and retry when applying the changes.

  surely if the log has recorded and performed the same actions due to the replication they would be similar

Not at all. The database activity may extend well beyond the area covered by replication (in other words, the database may contain more than just the subscription articles). But, more importantly, the log structure is shaped more by backup schedule and log retention than by activity. Read more here.


Different thread states in query execution My MySQL 5.1 based Intranet system is facing very bad performance during high load. When observed through MonYog, I found below thread states:

We can simply understand the different thread state by its name. Can someone provide the

differences among these states?
Why "sending data" is taking 76%?

differences among these states?
The MySQL Documentation provides a list of those states.
From your MONYog graph, here are the states:

Sending data
The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.
Copying to tmp table
The server is copying to a temporary table in memory.
Sorting result
For a SELECT statement, this is similar to Creating sort index, but for nontemporary tables.
freeing items
The thread has executed a command. Some freeing of items done during this state involves the query cache. This state is usually followed by cleaning up.

Why "sending data" is taking 76%?
As the Documentation says, data is being sent to the client.
What would attribute to the high load ?

Queries that return lots of rows
Lots of queries

Queries that return lots of rows
If you have queries returning lots of rows, tune your queries to return less data, perhaps adding effective WHERE and LIMIT clauses to SELECTs.
Lots of queries
If you do not have queries returning lots of rows, then it must be lots of queries. You may find this surprising, but MONYog queries mysqld for the global status variables with either
SHOW GLOBAL VARIABLES;

or
SELECT * FROM information_schema.global_status;

These constitute queries as well.
Perhaps you could configure MONYog to retrieve status information less often.


sql delete records based on combination of multiple fields in other table I need suggestions on deleting records in relation with another table

Main Table:

c1 c2 c3
2  5  3
1  2  8
1  2  NULL
1  4  7
2  4  8
1  3  NULL
2  5  NULL
5  1  3
1  5  1

I need to delete all rows where c1 and c2 are identical to a row where c3 is NULL.

e.g.: for c3 is NULL if record c1=1 and c2=2 delete that record as well as c1=1, c2=2 and  c3=8

The resulting table should be

Main Table:

c1 c2 c3
1  4  7
2  4  8
5  1  3
1  5  1

DELETE t1
FROM Table t
    JOIN Table t1 ON t1.c1 = t.c1
        AND t1.c2 = t.c2
WHERE t.c3 IS null


select newest update record among similar record I have  2 tables segments and summaries.
I want to list the shows in a date interval and just show the latest updated segments (in bold) since it is the freshest copy of the segment for that person.
Here is the query I used to have the result below:

select seg.summ, su.channel, seg.block_id, seg.person, seg.updated_at as segupdate
from segments seg
  left join summaries su on seg.summ = su.id
where su.created_at between '2018-06-06' and '2018-07-13'
order by summ, block_id, seg.updated_at desc;

I want this:

How can I get change my query to show just the bolded rows? to remove the duplicates that are an older version?

I'm not super familiar with Postgres, but based on some Internet searches, it seems that your version of Postgres supports 'window functions'.  It also seems to support 'common table expressions'.  If those statements are true, you should be able to wrap you main query into a common table expression and utilize the ROW_NUMBER window function to assign a row number to each 'grouping' (partition by person) and order by seg.updated desc.  Something like the following

;
With BaseData as
(
SELECT seg.summ
    ,su.channel
    ,seg.block_id
    ,seg.person
    ,seg.updated_at AS segupdate
    ,ROW_NUMBER() OVER(PARTITION BY seg.person ORDER BY seg.updated_at desc) as rn
FROM segments seg
LEFT JOIN summaries su
    ON seg.summ = su.id
WHERE su.created_at BETWEEN '2018-06-06'
        AND '2018-07-13'
ORDER BY summ
    ,block_id
    ,seg.updated_at DESC;
)
SELECT * FROM BaseData where rn = 1


SQL Server - NOLOCK vs. READONLY connections For SQL Server 2008 and above, should (NOLOCK) be used on each table of a SELECT statement even if the connection is configured as READONLY?

Locking is mainly used to help writes and reads write and retrieve data that is ACID compliant.  Thus a 'read only' query can have several layers of locking.  By default, MS SQL Server uses pessimistic locking where as Oracle uses optimistic locking.  That means in SQL Server reads by default will always block writes and vice versa.  In Oracle's default isolation level, reads do not block writes.  Thus unless you are familiar with your isolation level and if you're OK with dirty reads, then technically could.  However that doesn't mean you should.

Read up on some of the issues you'll face with NoLock and alternatives.

Copy/paste:

  NOLOCK Effects

  What are the effects you need to worry about? Well, others, like Andrew Kelly, have described this before, Paul White digs quite deep into it, these search results are quite telling, and Kendra Little even has a video about it… but I'll point them out anyway:

  "Dirty read" – this is the one most people are aware of; you can read data that has not been committed, and could be rolled back some time after you've read it – meaning you've read data that never technically existed.

  Missing rows – because of the way an allocation scan works, other transactions could move data you haven't read yet to an earlier location in the chain that you've already read, or add a new page behind the scan, meaning you won't see it at all.

  Reading rows twice – similarly, data that you've already read could be moved to a later location in the chain, meaning you will read it twice.

  Reading multiple versions of the same row – when using READ UNCOMMITTED, you can get a version of a row that never existed; for example, where you see some columns that have been changed by concurrent users, but you don't see their changes reflected in all columns. This can even happen within a single column (see a great example from Paul White).

  Index corruption – surely you are not using NOLOCK in INSERT/UPDATE/DELETE statements, but if you are, you should be aware that this syntax is deprecated and that it can cause corruption, even in SQL Server 2014 RTM – see this tip for more information. Note that you should check for the hint in any views that you are trying to update, too.

  Read error – because the underlying data could be moved or deleted during your read.


Proxying a Postgres connection I have a client whose Postgres database I need to connect to periodically. This database is currently hosted on their server, let's call it psql.example.com, with standard port 5432. They have it configured so that I can connect to it from my "primary" server, let's say that's on IP 123.45.67.89, using my_username and my_password as credentials.
The thing is, it's not my primary server that need to connect to it, but rather my load-balanced AWS nodes. It seems that I should be able to run something on my primary server with ssh -L or iptables, which will listen for incoming connections (on a different port, say 42042, to avoid potential conflicts later on) from my load-balanced nodes (easy to secure that end of things with standard AWS functionality), and transparently forward it on to their database, passing any response back.
I've tried things like ssh -L 42042:psql.example.com:5432 my_username@123.45.67.89, but this doesn't work; if I debug, I see that it's trying to connect to port 22, not 5432. Seems that I shouldn't be the only one wanting to do something like this, but most of what I've found is about using SSH to secure the connection to the DB server. It doesn't seem like any of the my_username@123.45.67.89 bit should be required on my primary server, that should be more for securing the connection from my end node to my server? Security of the connection to their server would be done through their existing IP limits and then PSQL credentials.
They're running quite an old version of Postgres, 8.1, which I don't think would make any difference for a pure port-forwarding solution, if that exists, but might limit other options.

This works for me.
You say the ssh command is trying to connect to port 22.  That is what it should be doing, in the absence of -p telling it to do otherwise.  It connects to port 22 on host 123.45.67.89 to set up the tunnel.  If 123.45.67.89 runs its sshd on a nonstandard port, then you need to specify it with -p.
Once that is done, then connections to localhost:42042 on the same machine where you ran the ssh command (your aws node) will get routed through 123.45.67.89 and on to psql.example.com on port 5432.


How can we get the table and the column name of known data value? I have a value (like 'IT department'), based on this I want to get the table and column name in which this value exists/present.

Just run this in a database.

As long as its not a text / ntext field..

SET NOCOUNT ON  /* AUTHOR DT 10/19/15 Server: Microsoft SQL Server */

CREATE TABLE #TBL_SEARCH (server_name varchar(100), db_name varchar(100), table_name varchar(40), raw_tablename varchar(max), [schema_nm] varchar(max), matching_field varchar(40), counts int,
A_SAMPLE varchar(100)
)

DECLARE Table_Column_Matches CURSOR
READ_ONLY
FOR (
    select '[' + OBJECT_SCHEMA_NAME(o.id) + '].[' + o.name + ']' as table_name, o.name as raw_table_name,
    OBJECT_SCHEMA_NAME(o.id) as [schema_nm], c.name as field_name, t.name as datatype_field from sysobjects o
    join syscolumns c
    on o.id=c.id
    join systypes t
    on t.type=c.type and t.xtype=c.xtype and c.xusertype=t.xusertype
    where
    o.type='U' and
    t.name IN ('char', 'nchar','nvarchar','varchar' )
)

DECLARE @table_name varchar(300)
DECLARE @field_name varchar(300)
DECLARE @raw_table_name varchar(300)
DECLARE @schema_nm varchar(300)
DECLARE @datatype_field varchar(300)
declare @query nvarchar(3000)

OPEN Table_Column_Matches

FETCH NEXT FROM Table_Column_Matches INTO @table_name, @raw_table_name, @schema_nm, @field_name, @datatype_field
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        set @query = 'SELECT @@SERVERNAME, DB_NAME(), ''' + @table_name + ''' as [table_name], ''[' + @raw_table_name + ']'' as raw_table_name,''[' + @schema_nm + ']'' as schema_nm, '''+ @field_name +''' as matching_field,
            COUNT([' + @field_name + ']) AS COUNTS,
            MAX([' + @field_name + '] ) A_SAMPLE
            FROM  ' + @table_name + ' WITH (NOLOCK)
            WHERE UPPER([' + @field_name + ']) LIKE ''IT DEPT%''
        '

        INSERT INTO #TBL_SEARCH
        exec sp_executesql @query
    END
    FETCH NEXT FROM Table_Column_Matches INTO @table_name, @raw_table_name, @schema_nm, @field_name, @datatype_field
END

CLOSE Table_Column_Matches
DEALLOCATE Table_Column_Matches

select * from #TBL_SEARCH
where counts > 0
drop table #TBL_SEARCH


How do I grant execute permissions to select client_addr from pg_stat_replication; for a regular PostgreSQL user? How do I grant execute permissions to select client_addr from pg_stat_replication; for a regular PostgreSQL user?

Always after creating a function, give execute rights

grant execute on function client_addr_pg_stat_replication() to user;

Make the function

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication() RETURNS text AS $$
BEGIN
  PERFORM client_addr from pg_stat_replication;
END;
$$ LANGUAGE plpgsql security definer;

Result:

select * from client_addr_pg_stat_replication();
ERROR:  control reached end of function without RETURN
CONTEXT:  PL/pgSQL function client_addr_pg_stat_replication()

Make the function

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication() RETURNS SETOF RECORD AS $$
BEGIN
  select client_addr from pg_stat_replication;
END;
$$ LANGUAGE plpgsql security definer;

Result:

select * from client_addr_pg_stat_replication();
ERROR: a column definition list is required for functions returning "record"
LINE 1: select * from client_addr_pg_stat_replication();

Make the function

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication(TEXT)
    RETURNS setof pg_stat_replication
AS
$BODY$
BEGIN
    RETURN QUERY SELECT client_addr FROM pg_stat_replication;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Result:

select * from client_addr_pg_stat_replication(client_addr);
ERROR: column "client_addr" does not exist
LINE 1: select * from client_addr_pg_stat_replication(client_addr);

Make the function

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication() RETURNS TABLE(client_addr varchar) AS
$$
BEGIN
  SELECT client_addr FROM pg_stat_replication;
END;
$$ LANGUAGE plpgsql security definer;

Result:

select * from client_addr_pg_stat_replication();
ERROR:  column reference "client_addr" is ambiguous
LINE 1: SELECT client_addr FROM pg_stat_replication
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT client_addr FROM pg_stat_replication
CONTEXT:  PL/pgSQL function client_addr_pg_stat_replication() line 3 at SQL statement

Make the function

CREATE OR REPLACE FUNCTION public.client_addr_pg_stat_replication(varchar)
    RETURNS setof pg_stat_replication
AS
$BODY$
BEGIN
    RETURN QUERY SELECT * FROM pg_stat_replication;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Result:

select * from client_addr_pg_stat_replication();
ERROR:  function client_addr_pg_stat_replication() does not exist
LINE 1: select * from client_addr_pg_stat_replication();
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
redcheckpg=> select * from client_addr_pg_stat_replication(client_addr);
ERROR:  column "client_addr" does not exist
LINE 1: select * from client_addr_pg_stat_replication(client_addr);

Thank you

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication() RETURNS text AS $$
DECLARE ip text;
BEGIN
select client_addr INTO ip from pg_stat_replication;
RETURN ip;
END;
$$ LANGUAGE plpgsql security definer;

OR

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication()
RETURNS text AS $x$
BEGIN
RETURN client_addr from pg_stat_replication;
END;
$x$ LANGUAGE plpgsql security definer;


how to get the information about sleeping processes that are causing blocking? I have a procedure that allows me to see what is currently running in my sql server.

This procedure does not use sys.sysprocess because it has been deprecated.

However, I am struggling to find needed information about sleeping processes that are causing blocking.

This is because the DMV sys.dm_exec_requests does not hold any request of sleeping processes.

I don't want to use sp_whoisactive or any monitoring tool.
sp_whoisactive uses sys.sysprocesses.

I have my session 81:

  BEGIN TRANSACTION T1

  SELECT @@TRANCOUNT

  update
  [TableBackups].[dbo].[spstats]
  set execution_count  = 1000
  where dbname = 'master'

  select @@spid

and I have my session 51:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT TOP 1000 *
  FROM [TableBackups].[dbo].[spstats]
  WITH (HOLDLOCK)

These 2 sessions above are running at the same time and consequently, session 81 is blocking the session 51.

I have this select below, that get information about the session 81,
however, I could not get teh dbid, and I am not sure about the Open_transactions and the whole waits information.

SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, 'No Info') AS login_name
,COALESCE(es.host_name,'No Info') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
    CASE es.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'Read Uncommitted'
        WHEN 2 THEN 'Read Committed'
        WHEN 3 THEN 'Repeatable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END
,COALESCE(es.cpu_time,0)
    + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0)
    + COALESCE(es.writes,0)
    + COALESCE(er.reads,0)
    + COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
    INNER JOIN sys.dm_exec_requests ec2 ON es.session_id = ec2.blocking_session_id
    LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
    LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
    LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
    CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st
where es.is_user_process = 1
  and es.session_id <> @@spid

The question is,
is there any DMV, other than sys.sysprocesses that I can use to get this info?

This is what I use to monitor sleeping SPIDs causing blocking issues:
SELECT
     s.session_id
    ,s.status
    ,s.login_time
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.original_login_name
    ,s.last_request_end_time
    ,CAST(t.text AS nvarchar(4000)) AS [text]
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c
    ON s.session_id = c.session_id
CROSS APPLY (
    SELECT MAX(DB_NAME(dt.database_id)) AS database_name
    FROM sys.dm_tran_session_transactions AS st
    INNER JOIN sys.dm_tran_database_transactions AS dt
        ON st.transaction_id = dt.transaction_id
    WHERE is_user_transaction = 1
    GROUP BY st.session_id
    HAVING s.session_id = st.session_id
) AS trans
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE s.session_id NOT IN (
        SELECT session_id
        FROM sys.dm_exec_requests
    )
    AND s.session_id IN (
        SELECT request_session_id
        FROM sys.dm_tran_locks
        WHERE request_status = 'GRANT'
    )
    AND STATUS = 'sleeping'
    AND is_user_process = 1;

Hope this helps.
To answer your question specifically, sys.dm_tran_database_transactions holds the information you're looking for.


Mongodb find() not working as expected, PHP Mongodb find() seems Not to work, I have tried all I could and nothing works so far. using PHP7+

I have a Document in the strure like the image below

In SQL I could do something like:SELECT....... where 'common' LIKE '%Burk%'

The simple query is to call the countries collection and produce Burkina Faso as output

  QUERY 1:

$countries_tb= $db->selectCollection('country_city_data');
 $countries = $countries_tb->find([],[
    'common' => new MongoDB\BSON\Regex('Burk')]);
var_dump($countries);

the var_dump prints a list of all the countries in the database instead of getting only Burkina Faso

  QUERY 2:

when I do find(without the empty []), it var_dumps nothing necessary

$countries = $countries_tb->find(/*without the []*/ [
    'common' => new MongoDB\BSON\Regex('Burk')]);

var_dump($countries);

I get this:

I have also tried this format.

  QUERY 3:

 $countries = $countries_tb->find(
                     array('name'=>
 array(  'common'=>new MongoDB\BSON\Regex('Burk'))));

                var_dump($countries);

Still no success.

As suggested by @noobProgrammer from SO below, doing like this:

$countries_tb->find(['name' =>
                  ['common'=>new MongoDB\BSON\Regex('Burk')]],
                  ['typeMap' => ['root' => 'array', 'document' => 'array']]);

Produces this;

I got it working finally!

This is what I did.

$countries = $countries_tb->find(array('name.common' =>   array('$regex' =>  'Burk') ) );

Notice the . in name.common

Here is my foreach loop'

foreach( $countries as $country => $name) {

   foreach ($name as $n){

       if(isset($n->common)){

            var_dump($n->common);

        }

   }
}

//It prints out:    country_cities.php:90:string 'Burkina Faso' (length=12)

Somehow, this does NOT work

$countries = $countries_tb->find(
                         array('name'=>
                                 array('common' =>
                                         array('$regex' =>   'Burk')) ) );

This is the JSON'

{"_id":"55a0f42f20a4d760b5fc306d","altSpellings":["BF"],"area":272967,"borders":["BEN","CIV","GHA","MLI","NER","TGO"],"callingCode":["226"],"capital":"Ouagadougou","cca2":"BF","cca3":"BFA","ccn3":"854","cioc":"BUR","currency":["XOF"],"demonym":"Burkinabe","landlocked":true,"languages":{"fra":"French"},"latlng":[13,-2],"name":{"common":"Burkina Faso","native":{"fra":{"common":"Burkina Faso","official":"Burkina Faso"}},"official":"Burkina Faso"},"region":"Africa","subregion":"Western Africa","tld":[".bf"],"translations":{"cym":{"common":"Burkina Faso","official":"Burkina Faso"},"deu":{"common":"Burkina Faso","official":"Burkina Faso"},"fin":{"common":"Burkina Faso","official":"Burkina Faso"},"fra":{"common":"Burkina Faso","official":"Burkina Faso"},"hrv":{"common":"Burkina Faso","official":"Burkina Faso"},"ita":{"common":"Burkina Faso","official":"Burkina Faso"},"jpn":{"common":"ブルキナファソ","official":"ブルキナファソ"},"nld":{"common":"Burkina Faso","official":"Burkina Faso"},"por":{"common":"Burkina Faso","official":"Burkina Faso"},"rus":{"common":"Буркина-Фасо","official":"Буркина -Фасо"},"spa":{"common":"Burkina Faso","official":"Burkina Faso"}}}


Running MS Access queries for WAN hosted mySQL database I have a front end MS Access running through a backend MySQL database via ODBC, I recently moved MySQL database from a local LAN server to a hosted WAN server on Amazon RDS.  Now all of my queries are running very slow. By searching online I found the best way is to rewrite the queries in pass through mode so stored procedures are not running locally in Access.     I have tried VPN vs ODBC, didn't help.

Problem with this approach is that I have thousands of queries (saved and in code) that run sums, dlookups and other Access procedures, it will take months to rewrite all of them correctly so the queries run on the hosted site.

Is there another quicker fix solution to this problem?

You could run Windows server on AWS EC2 or a Windows Desktop using Amazon Workspaces and try running Access there in AWS's network environment.  That may significantly improve performance.  Another thing to consider is that network performance can be limited on micro instances.

There might be other advantages of hosting your whole application in the cloud in terms of uptime.


MySQL: optimize column size I need a tool/command which will check a table then say something like that:

columnA use tinyint instead of int
columnB use enum instead of varchar

I've seen something like this while ago so I do know it exists, but search gives nothing or I'm asking it in a wrong way.

This is such a quick and dirty question.

This deserves a quick and dirty answer.

Good News : PROCEDURE ANALYSE()

Bad News : It is Deprecated in 5.7.18 and will not be available in MySQL 8.0

I have discussed  PROCEDURE ANALYSE() over the years: See my old posts.

In your case, you would just do this:

SELECT columnA,columnB FROM yourtable PROCEDURE ANALYSE();

and the output will tell you the min value, max value, avg value, and recommended datatype.


What are the downsides of running DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') hourly? I have been seeing some concerning behavior in the SQL Server memory caches on one of our production servers. We are on SQL Server 2014 SP2 Enterprise Edition, 16 cores, 400GB RAM (300 GB dedicated to SQL Server).

Using this query (credits to Glenn Berry):

SELECT TOP(10) mc.[type] AS [Memory Clerk Type],
       CAST((SUM(mc.pages_kb)/1024.0) AS DECIMAL (15,2)) AS [Memory Usage (MB)]
FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
GROUP BY mc.[type]
ORDER BY SUM(mc.pages_kb) DESC OPTION (RECOMPILE);

I have discovered an issue. The USERSTORE_TOKENPERM values grow to a point where they seem to overwhelm the cache, as a result, almost all plans are compiled and none are cached. That is CACHESTORE_SQLCP and CACHESTORE_OBJCP will sit at around 200 MB each, while the USERSTORE_TOKENPERM cache is upwards of 11 GB. With over 168,000 tokens stored, for about 100 concurrent/600 total users with 3000-5000 connections. These numbers seem way off.

Unfortunately, the vendor that provides this application doesn't have crazy things like "System Requirements" documentation, and they won't commit to supporting the application on untested Service Packs/CU's. So my hands are tied to SQL Server 2014 SP2, as other threads I've read suggest upgrading to SQL Server 2014 SP2 CU7 or higher. That is the long-term goal. So I needed another solution for the short term.

After running the command suggested in this thread from 2009.

Specifically:

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

I see our CPU usage dropping from 30%-80% utilization, to under 30%. User complaints of "slowness" in the application have trailed off. Plan caches are now growing to 10 GB or more.

As the day goes on, the token cache grows again. Near the end of the day we start to see a the problematic caching behavior again. I have scheduled the DBCC command to run nightly, outside of business hours.

I understand that I can apply trace flag 4610 and 4618 here to limit the number of stored tokens.

  Increases the size of the hash table that stores the cache entries by
  a factor of 8. When used together with trace flag 4618 increases the
  number of entries in the TokenAndPermUserStore cache store to 8,192.

By the end of the day, we end up with about 40,000 tokens cached.

Does anyone have any experience (or knowledge) with this issue that can say whether using trace flags, or clearing the cache, is a more effective solution?

What are the downsides of clearing the TokenAndPermUserStore cache more frequently than once per day? Say, hourly? Is there a "too often"?

And what would be the downsides of limiting the token cache through trace flags? Will I have end users hit bottlenecks with token limits, or otherwise (thus causing another issue)?

My major concern here is that, this is medical data, and I do not want to lose patient data as a result of a problem associated with a cache flush.

From Comments:

This sounds like a prime candidate for a Microsoft Product Support engineer. - Max Vernon

We experienced the same issue and our final decision was to use DBCC FREESYSTEMCACHE (&#39;TokenAndPermUserStore&#39;) on a daily basis. We consider the solution as more  predictable and managed than trace flags using. Unfortunely, MS wasn't able to find and offer some better solution.  - Denis Rubashkin

This is an old bug, I have a hunch that latest SP/CU should fix it. If not, you may need to contact MS support. As for question I faced this issue a lot in SQL Server 2008 R2 version and selectively clearing cache did not cause any issue. - Shanky


Change length of varchar on live prod table I have a MS SQL Server 2008 R2 DB server currently in use with a production app.

A new enhancement to the app now requires a varchar(100) column in a table to be increased in length.

Can the length of this existing column in the prod DB be increased without affecting the current data?

Does this change have to be completed during off-hours to avoid disruption of service?

If you are increasing it to varchar(100 - 8000) (i.e. anything other than varchar(max)) and you are doing this through TSQL rather than the SSMS GUI

ALTER TABLE YourTable ALTER COLUMN YourCol varchar(200) [NOT] NULL

and not altering column nullability from NULL to NOT NULL (which would lock the table while all rows are validated and potentially written to) or from  NOT NULL to NULL in some circumstances then this is a quick metadata only change. It might need to wait for a SCH-M lock on the table but once it acquires that the change will be pretty much instant.

One caveat to be aware of is that during the wait for a SCH-M lock other queries will be blocked rather than jump the queue ahead of it so you might want to consider adding a SET LOCK_TIMEOUT first.

Also make sure in the ALTER TABLE statement you explicitly specify NOT NULL if that is the original column state as otherwise the column will be changed to allow NULL.


Query to return multiple rows with same UID and entered on the same date I've revised the post per MarkP's suggestions. Thanks for the suggestion as I am new to posting.

I have a table with the following data, UID, Order Number,Date In

I need to select the all of the rows where the Date In is the same for all UIDs , as in rows for UIDs 1 and 4, and ignore rows for UIDs 2 and 3 where the same UID has different dates. (Note that those orders may have duplicate dates as well). So far I've come up with some ways to not achieve the desired results. (I removed all of the samples except one). Since they all returned the same data, all orders with multiple entries on the same date, rather than all orders with only entries on the same date.

The results would look like this:

with uidlist as
(select s.uid,s.ordernumber, count(s.uid) as counter, cast(floor(cast(ot.DateIn as float)) as datetime) as ldate
              from service s join ordertask ot on ot.orderuid = s.uid
              where ot.datein > = '2017-04-01'
              group by s.uid,s.ordernumber, cast(floor(cast(ot.DateIn as float)) as datetime)),
uidfinal as (select count(uid) as counter, uid,ordernumber from uidlist group by uid,ordernumber having count(uid) = 1)
select ul.uid,ul.ordernumber
from uidfinal UL
order by ul.ordernumber;

Thanks for any assistance.

If you need to output rows where the same date appears more than once for a give orderuid, the following should to the job :

SELECT orderuid, ordernumber, datein
FROM
(
  SELECT a.orderuid,a.ordernumber,a.datein,
   count(1) OVER(PARTITION BY a.orderuid,a.datein) as num_with_same_date
        from  ordertask a
)b WHERE num_with_same_date >1;

Updated.

SELECT orderuid, ordernumber, datein
FROM
(
  SELECT a.orderuid,a.ordernumber,a.datein,
   count(1) OVER(PARTITION BY a.orderuid,a.datein) as num_with_same_date,
   count(1) OVER(partition by a.orderuid) as total_num_uid
        from  ordertask a
)b WHERE total_num_uid =num_with_same_date  ;


Piecemeal restore of entire database - fastest way to roll forward and restore multiple files I have a database which has two filegroups (both r/w), each with two files. The backup sequence was:

Full Backup
Several transaction log backups (tlog set1)
Full Backup of file1 of filegroup1
Several transaction log backups (tlog set2)
Full Backup of file2 of filegroup1
Several transaction log backups (tlog set3)
Full Backup of file1 of filegroup2
Several transaction log backups (tlog set4)
Full Backup of file2 of filegroup2
Several transaction log backups (tlog set5)
tail log backup

The guidance I found online was that to restore file1, I will need to restore file1 backup, then roll forward tlog sets 1-5 and the tail log.

However, lets say that I need to restore the entire DB in minimal time, can I use the following sequence :

Restore file1 filegroup1
restore tlog set2 --roll forward to file 2 backup
restore file2 filegroup1
restore tlog set3
restore file1 filegroup2
restore tlog set4
restore file2 filegroup2
restore tlog set5
restore tail

would I have to do

Restore file1 filegroup1
Restore tlog set2 -- roll forward to current time
Restore tlog set3
Restore tlog set4
Restore tlog set5
Restore tail

Restore file2 filegroup1
Restore tlog set2 -- roll forward to current time
Restore tlog set3
Restore tlog set4
Restore tlog set5
Restore tail

...

bringing each file online before moving on the next file (or maybe even just use the full backup then applying all the log backups)? Wasn't able to find a definitive answer.

Additionally, is it necessary for me to restore the initial full backup, since the file backups cover the entire database?

The documentation examples restore just one filegroup, which is restore the full file backup then roll forward through every log bringing the filegroup up to date. The case I'm wondering about is if you are restoring multiple file backups, do you have to roll each file forward to the present, or can you restore the first file backup, roll transaction logs to the second file backup, then roll forward to the present. I want to minimize the number of times I have to apply a log backup.

The first sequence you wrote is correct:

Restore file1 filegroup1
restore tlog set2 --roll forward to file 2 backup
restore file2 filegroup1
restore tlog set3
restore file1 filegroup2
restore tlog set4
restore file2 filegroup2
restore tlog set5
restore tail

You have no need to bring online the first file, then the second file, etc as all you want is to restore the whole database in minimal time.

I've just tried the first sequence as I thought it was right and it worked.

I tried then to restore from full backup + all the log files to find out if it was faster and it was. Certainly my database was small enough, just a test database, but you can try to restore your database to another database or to another server in both ways to compare the time.


MariaDB Galera Cluster without HAProxy, MYSQL Proxy, etc I'm looking to deploy a MariaDB galera cluster across three servers.  However, I've noticed that the documentation often references HAProxy or the like.  Does this not cause a single point of failure situation?

I guess the question I'm asking is - Do I need any sort of load-balancing software to use a MariaDB cluster?  Or could I have support for the cluster built-in to the app?

Apologies for the English.

You should use some kind of load-balancing solution, at least for these main reasons:

To equally balance the load between the nodes.
To check availability of the nodes and make it transparent as possible to your app.

If you use an HAproxy server you should have it replicated, so it's not a single point of failure, e.g. you can have two HAproxies in active/passive with a VIP* managed by keepalived, so if one dies, the other one starts taking the connections.

As you say, there are other options, as having local MySQL proxies in your application servers, or a pool of connections with health checks in your app backend.

If anyway you don't want to deploy any proxy, you can try to have a VIP (or some of them) managed by keepalived directly in your nodes, but with this solution it'd be more difficult to have fair balancing between the nodes and probably you wouldn't benefit so much of the cluster.

The best solution probably depends on the kind of load your cluster is going to have.

* VIP - Virtual IP. In keepalived terms it's an IP that is managed by keepalived. It's initially configured in the "active" host and if it dies, or some of its checks fail, or it's preempted by any other reason, then the keepalived daemons ensure that the IP is down in the failing host and is configured in the "passive" one.


INET6_NTOA output is set as null to varchar(39) variable The following code snippet was working for me in version 10.2.6. But I saw issues in version 10.2.9, where the variable "ip_full_addr" is returning null instead of expected value 2000::

DECLARE ip_full_addr varchar(39) DEFAULT "";
SELECT INET6_NTOA(UNHEX('20000000000000000000000000000000')) into ip_full_addr;
SELECT ip_full_addr;

Does anyone have any clue on why this is happening?

This looks like a genuine bug. I get the expected result in MySQL 5.7.20 (see below), but it returns null in MariaDB 10.2.10. The issue should be reported in the MariaDB Jira.

mysql> DELIMITER ;;
mysql> CREATE PROCEDURE p() CONTAINS SQL BEGIN DECLARE ip_full_addr varchar(39) DEFAULT ""; SELECT INET6_NTOA(UNHEX('20000000000000000000000000000000')) into ip_full_addr; SELECT ip_full_addr; END;;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call p();
+--------------+
| ip_full_addr |
+--------------+
| 2000::       |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


Most efficient way of caching queries / processed data I have doubts on database design.

I'm trying to know if there is a way to increase query performance, and the easiest way for me to explain my doubt is with a small example.

I have a news website with news posts, and those have comments. The Comments table is like this:

Table Comments:
comment_id | text | post_id | user_id | ...

This news website has in its mainpage two rankings, making them heavily read, but with a low number of writes:

One with the most commented news posts (The 10 most "popular" news posts)
Another with the users with most comments (The 10 most "active" users)

The "normal" way to do this is to create the appropriate queries and leave them on that index page:

Top10 of Posts:
SELECT TOP 10 post_id, COUNT(comment_id) AS num_comments
FROM Comments
GROUP BY post_id
ORDER BY num_comments DESC

Which could be heavy to process, since it has to process ALL the news comments rows every time the page is accessed. Well, I'm not sure about this because I don't know how efficient caching is.

That's why I'm thinking in a alternative, that would be to create two tables, that would have the processed data for those two rankings.

Table Comments_PostsTop:
post_id | num_comments

Table Comments_UsersTop:
user_id | num_comments

Would this be a good alternative?

If so, is there an automated way to update these tables based on updates on the Comments table besides triggers? Or are triggers the only good way to achieve this?

Or is caching enough to keep high performance? (SQL Server 2014)

Or is there another model that could be more efficient?

EDIT:
After accepting the answer, I'm posting how I created and queried the materialized view.
It's based on the steps provided by the documentation:
https://msdn.microsoft.com/en-us/library/ms191432.aspx

Creation

--Create view with schemabinding.
IF OBJECT_ID ('dbo.commentsposts', 'view') IS NOT NULL
DROP VIEW dbo.commentsposts;
GO
CREATE VIEW dbo.commentsposts
WITH SCHEMABINDING
AS
    SELECT post_id, COUNT_BIG(*) AS num_comments
    FROM dbo.comments
    GROUP BY post_id;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1
    ON dbo.commentsposts (post_id);
GO

Query

--Normal query
SELECT post_id, COUNT_BIG(*) AS num_comments
FROM comments
GROUP BY post_id;

--Query for the view
SELECT [post_id],[num_comments]
FROM [teste].[dbo].[commentsposts];

--But SQL Server is not using the view, so we need to force its usage
SELECT [post_id],[num_comments]
FROM [teste].[dbo].[commentsposts] WITH (NOEXPAND);

Only on this 3rd query the query selects the data on the view.
The first two are the same according to the estimated execution plan.

Have you looked into using SQL Server's support for Indexed Views?  (Generically this is called a materialized view.)  If your code really needs an assist in getting the answers this is a option, though not without its own difficulties.

An Indexed View is a duplicate set of data taken from the main tables and stored within the Indexed View.  There are rules and limitations, but it basically creates a specialized "table" that should be index-tuned to suite your purpose for using it.

This MSDN topic points you to the details that you need:

https://msdn.microsoft.com/en-us/library/ms191432.aspx

The definition of an indexed view must be deterministic, needs certain settings to be adjusted, and there are actions such as OUTER JOIN and ORDER BY that are forbidden.

EDIT: You should also use the NOEXPAND hint, as explained by Aaron Bertrand, to "ensure the uniqueness guarantee provided by the view indexes is used by the optimizer."  See:  http://sqlperformance.com/2015/12/sql-performance/noexpand-hints


Include fields from a table function in a select I'm running this query on SQL Server 2008 R2:

SELECT
   orr.Rate, orr.ID, orr.UserKey, fn.NetRate
FROM
   OngoingRate orr
CROSS APPLY
   dbo.GetRateNetView(orr.UserKey) as fn
WHERE
   orr.UserKey = 5

I get the following error:

  Incorrect syntax near '.'.

Please help

The syntax you have is correct from SQL Server 2005 and up. You are using SQL Server 2008 so it should work just fine unless you have compatibility level for your database set to "SQL Server 2000 (80)".

Make sure you have compatibility level set to at least "SQL Server 2005 (90)".

Right-click on your database - Properties - Options


How to upgrade PostgreSQL from 9.5 to 9.6.1 without losing data? When I tried to run psql, I got

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

and when I checked Server.log, I saw:

FATAL: database files are incompatible with server
DETAIL: The data directory was initialized by PostgreSQL version 9.5,
        which is not compatible with this version 9.6.1.

I tried to follow this tutorial (from 9.4 to 9.5) for upgrading 9.6.1, but keep getting this error message

cannot write to log file pg_upgrade_internal.log
Failure, exiting

after running

$ pg_upgrade -v \
> -d /usr/local/var/postgres \
> -D /usr/local/var/postgres9.6.1 \
> -b /usr/local/Cellar/postgresql/9.5/bin/ \
> -B /usr/local/Cellar/postgresql/9.6.1/bin/

Does anyone have any idea what I am missing/doing wrong? Thanks!

System:

MacOS 10.12.2
PostgreSQL installed with Homebrew

After 9 months, I updated to PostgreSQL 10 from 9.6.5 with pg_upgrade:

1) Stop postgresql:

brew services stop postgresql

2) Initialize postgresql10 database:

initdb /usr/local/var/postgres10.0 -E utf8

3) Double check the newly created db:

ls /usr/local/Cellar/postgresql/

4) Run pq_upgrade to move data to new database:

pg_upgrade \
  -d /usr/local/var/postgres \
  -D /usr/local/var/postgres10.0 \
  -b /usr/local/Cellar/postgresql/9.6.5/bin/ \
  -B /usr/local/Cellar/postgresql/10.0/bin/ \
  -v

5) Rename old database:

mv /usr/local/var/postgres /usr/local/var/postgres9.6.5

6) Rename new database to postgres:

mv /usr/local/var/postgres10.0 /usr/local/var/postgres

7) Restart postgresql:

brew services start postgresql


How to relate two rows in the same table I have a table where the rows can be related to each other, and logically, the relationship goes both ways (basically, is directionless) between the two rows. (And if you're wondering, yes, this really should be one table. It is two things of the exact same logical entity/type.) I can think of a couple ways to represent this:

Store the relationship and its reverse
Store the relationship one way, constrain the database from storing it the other way, and have two indexes with opposite orders for the FKs (one index being the PK index)
Store the relationship one way with two indexes and allow the second to be inserted anyway (sounds kind of yucky, but hey, completeness)
Create some kind of grouping table and have an FK to it on the original table to it. (Raises lots of question. Grouping table would only have a number; why even have the table? Make FK NULLable or have groups with single row associated?)

What are some major pros and cons of these ways, and of course, is there some way I haven't thought of?

Here's a SQLFiddle to play with: http://sqlfiddle.com/#!12/7ee1a/1/0. (Happens to be PostgreSQL since that's what I'm using, but I don't think this question is very specific to PostgreSQL.) It currently stores both the relationship and its reverse just as an example.

What you have designed is good. What needs to be added is a constraint to make the relationship directionless. So, you cannot have a (1,5) row without a (5,1) row being added as well.

This can be accomplished* with a self referencing constraint on the bridge table.

*: it can be accomplished in Postgres, Oracle, DB2 and all DBMS that have implemented foreign key constraints as the SQL standard describes (deferred, e.g. checked at the end of transaction.) Deferred checking is not really needed anyway, as in SQL-Server that checks them at the end of statement and this construction still works. You cannot do this in MySQL because "InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row".

So, in Postgres the following will match your requirements:

CREATE TABLE x
(
  x_id SERIAL NOT NULL PRIMARY KEY,
  data VARCHAR(10) NOT NULL
);

CREATE TABLE bridge_x
(
  x_id1 INTEGER NOT NULL REFERENCES x (x_id),
  x_id2 INTEGER NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2),
  CONSTRAINT x_x_directionless
    FOREIGN KEY (x_id2, x_id1)
    REFERENCES bridge_x (x_id1, x_id2)
);

Tested at: SQL-Fiddle

If you try to add a row (1,5):

INSERT INTO bridge_x VALUES
(1,5) ;

It fails with:

  ERROR: insert or update on table "bridge_x" violates foreign key constraint "x_x_directionless"
  Detail: Key (x_id2, x_id1)=(5, 1) is not present in table "bridge_x".:
  INSERT INTO bridge_x VALUES (1,5)

Additionally, you can add a CHECK constraint if you want to forbid (y,y) rows:

ALTER TABLE bridge_x
  ADD CONSTRAINT x_x_self_referencing_items_not_allowed
    CHECK (x_id1 <> x_id2) ;

There are other ways to implement this as you mention, like storing only one direction of the relationship (in one row, not two) by forcing the lower id in x_id1 and the higher id in the x_id2 column. It looks easier to implement, but usually leads to more complex queries later:

CREATE TABLE bridge_x
(
  x_id1 INTEGER NOT NULL REFERENCES x (x_id),
  x_id2 INTEGER NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2),
  CONSTRAINT x_x_directionless
    CHECK (x_id1 <= x_id2)                       -- or "<" to forbid `(y,y)` rows
);


Indirect constraints (foreign keys amongst three/four tables) I have the next layout of tables:

table COMPANY:
- primary key pk_company

table USERS:
- primary key pk_users,
- foreign key (fk_company) references COMPANY (pk_company)

table GROUPS: <br>
- primary key pk_group,
- foreign key (fk_company) references COMPANY (pk_company)

table ROLES:
- primary key pk_role

table RELATIO_UGR:
- foreign key (fk_user) references USERS (pk_user)
- foreign key (fk_group) references GROUPS (pk_group)
- foreign key (fk_role) references ROLES (pk_role)
- Also: UNIQUE KEY [fk_user,fk_group]

This is:

USER ---(n:m)--- GROUP ---(m:1)--- COMPANY
USER ------------(n:1)------------ COMPANY
1 company can have N users (workers)
1 company can have M groups
M groups can have N users (each worker can be in more than one group)
1 user can have only 1 role (read / read-write / admin) in each group.

The thing is that all constraints seem to work in the individual tables, however, the RELATIO_UGR table fails to establish constraints.

Let's suppose we have:

USER1 from GROUP1 and COMPANY1
USER2 from GROUP2 and COMPANY2

The RELATIO_UGR shouldn't allow me to establish (for example) the relationship:

USER2-GROUP1-COMPANY2

Yet, I CAN insert it...

How do I restrict it?

Your business rules will dictate your schema modeling. It is important you establish all these rules first.
If a user can belong to multiple Group (group 1 , group 2, group3) than it is the rule. If you don't want a user to be able to be inserted in a group that belongs to another company, you need to program this logic at the application layer. FK itself only checks the key exists in parent on insertion. (it does not verify for you if that user is from group 1 or 2). If you want to enforce this, i would recommend using triggers and using a userGroup table:

USER ------------(n:1)------------ COMPANY
USER ---(n:m)--- UserGROUP --(n:m)---- Group---(m:1)--- COMPANY

UserGROUP (group_id,user_id, roleId)
(PK on group_id, userId to enforce 1 user role per group)

Trigger On insert in UserGROUP:
 if user.company_id = group_company_id, then insert else "error".


What is wrong with this mysql INSERT statement? I have table:
Book:
- id:(int) primary key, autoincrement, not null,
- title:(varchar) not null,
- price: (decimal) not null

and
Artical:
- id:(int) primary key, autoincrement, not null,
- title:(varchar) not null,
- price: (decimal) not null,
- book_id:(int) foreign key referencing book

I already have couple of books in Book table while Artical table is empty, i want to have and insert statement in Artical that will insert data and fill in book_id foreign key properly. I came up to this statement wich is not good but i'm feeling i'm close, here it is:
INSERT INTO Artical (id,title,price,book_id)
VALUES (
    null,
    'Six faces of shadow',
    15,
    (
        SELECT book.id
        FROM book
        JOIN Artical AS ar
        WHERE book.title = ar.title
    )
);

There are two way to insert data:

if you have to insert data in Artical table with specific book title then do following

INSERT
INTO Artical (title, price, book_id)
VALUES (
    'Six faces of shadow',
    15,
    (
        SELECT book_id FROM book
        WHERE book.title = "Six faces of shadow"
    )
);

if you want to add all book title with id then do following

INSERT
INTO Artical (title, price, book_id)
SELECT title, price, book_id FROM book

Hope this Help!!!


How can we sort the words inside a string in PostgreSQL? In my postgresql table one column data is like below

250 TABLETS + PVC + PVDC + ALU + HOSPITAL PACK + Film-coated tablet - BLISTER - [/Tablet] - QUETIAPINE FUMARATE 28.78 mg is present
20 ML + MATERIAL UNKNOWN + Oral suspension - BOTTLE - [/mL] - ALUMINIUM HYDROXIDE 37 mg; MAGNESIUM HYDROXIDE 40 mg...

How can I split this string based on '+' character and sort this alphabetically and put | character and update the same column?

Considering your table has some ID column, you can get it this way:

with ct as
(
select id,
  unnest(regexp_split_to_array(t, '\+')) part
from
  a
),
ct2 as
(
select
  id, string_agg(part, '|' order by part) parts
from
  ct
group by
  id
)
update a
set t = ct2.parts
from ct2
where ct2.id = a.id;

id | t
-: | :----------------------------------------------------------------------------------------------------------------------------------
 1 | 250 TABLETS | ALU | Film-coated tablet - BLISTER - [/Tablet] - QUETIAPINE FUMARATE 28.78 mg is present| HOSPITAL PACK | PVC | PVDC
 2 | 20 ML | MATERIAL UNKNOWN | Oral suspension - BOTTLE - [/mL] - ALUMINIUM HYDROXIDE 37 mg; MAGNESIUM HYDROXIDE 40 mg

db<>fiddle here


Identifying processes that open MySQL connections I need to identify which application process issues a large number of MySQL connections on a Linux system.
The show processlist sql command or tools like innotop do not really help, as they only show db user and host, and i doubt that i catch all statements that way.
I was thinking to use something on the network level, like tcpdump or netstat. Any suggestions?

It seems
ss -p '( dport = :mysql )'

does the trick, listing the process ids of the processes on the local machine that connect to the mysql driver's port.


MySQL/MariaDB data directory backup I am using a solution of backblaze B2 / duplicity / duply to create backups of my databases. I upload full backups and incremental backups to B2 using duplicity via duply. The incrementals are done similar to rsync.

My server is 100GB and my databases are currently 70GB, but I don't suspect them to grow much. I dont have a master/slave setup. I want a backup solution that allows me to backup the 70GB of databases but not require very much space during the backup process.

I decided that I could just backup the whole /var/lib/mysql directory instead of using mysqldump or other backup methods which requires a full backup persist on disk.

I read that in order to do this method I would have to completely turn off the mysql server, do the backup, and turn it back on. I am OK with that because duplicity does incremental backups which should only take a few minutes.

I have ran SET GLOBAL innodb_max_dirty_pages_pct = 0; in order to make shutdown/starup as fast as can be and verified via show global status like '%dirty%'; that Innodb_buffer_pool_bytes_dirty hovers around 0.

I also understand the caveat for InnoDB tables is that they require the exact same mysql version in order to properly restore. Is that still the case?

Is there anything I am missing and will I have a near 100% confidence that if I use the exact mysql version (mariadb in my case) that at least one of my hundreds of incremental backups will restore.

How does duplicity do the "incremental" backups?  By doing something like rsync?  Or using MySQL's binlogs?

One of the least invasive backups involves LVM.

About 1 minute of downtime, regardless of dataset size.
Only a few percent of disk space needed, regardless of dataset size.  (10% might be more than generous.)
And, yet, you always get a full backup.

How, you should ask?  Obviously some kind of sleight of hand.  Hint: a COW is involved.

But, alas, you have to plan ahead and set up a "Logical Volume" for this to work with.  And the OS has to provide such.  Here's how it works:

Set up the LV (one-time task)
when you want to take a dump, do the following steps:
stop mysqld
take a "snapshot" of the entire disk (perhaps 1 minute)
start mysqld
now you get the slow tedious task of copying the entire snapshot to somewhere else.

More info on the LV and the snapshot in the LV:

The snapshot is a "logical" copy of all the disk blocks.
It feels like a separate drive.
It takes very little extra space -- to start with
When either drive writes anything, the COW mooves into help.  COW = Copy On Write.  That is, the block that is being modified now needs to be come two blocks (and takes more real disk space).  One drive keeps the old copy; one gets the new.
During your copy to elsewhere, the backup LV drive still sees only "old" blocks.
During your copy, mysqld continues to modify blocks, oblivious to the COW grazing in the background.
Well, there will be a lot of I/O due to the copy.

Other notes...

Yes, it would be better to have exactly the same version.
You could install a newer version with the backup, but be sure to do mysql_upgrade.  You could even test an upgrade right there in the snapshot -- without copying to elsewhere.  (Note: the LV would need to be big enough for whatever changes in it.)
There is no concept of "incremental" (unless your copy is something like rsync).
Because I suggested actually stopping mysqld, there is no hard need for playing with %dirty%, etc.  After all, InnoDB can recover from a power failure; this is more graceful than that.


What Happens to dirty pages if the system fails before the next checkpoint? Assuming a database using full recovery model, when a record is written in SQL Server (by INSERT / UPDATE etc) write ahead logging will ensure the change is written to the log file before modifying the data page.

Both the log and data page entries are made in RAM and committed to disk later by a Checkpoint.

If there is a system crash (power loss for the sake of argument) what will happen to dirty pages (IE data that is changed in RAM but not committed to disk) as the contents of RAM do not survive system restarts, is this data lost?

EDIT

After some testing, I can see the dirty pages are not lost, but I am not sure why:

using this tutorial

create a test database

CREATE DATABASE DirtyPagesDB
GO
USE DirtyPagesDB
GO

turn off automatic checkpoints

DBCC TRACEON(3505, -1);
DBCC TRACESTATUS();

create a table, insert some data and issue a checkpoint:

CREATE TABLE t1 (Speaker_Bio CHAR(8000))
GO
INSERT INTO t1 VALUES ('SQL'),('Authority')
GO
CHECKPOINT

confirm no dirty pages

-- Get the rows of dirtied pages
SELECT
database_name = d.name,
OBJECT_NAME =
CASE au.TYPE
WHEN 1 THEN o1.name
WHEN 2 THEN o2.name
WHEN 3 THEN o1.name
END,
OBJECT_ID =
CASE au.TYPE
WHEN 1 THEN p1.OBJECT_ID
WHEN 2 THEN p2.OBJECT_ID
WHEN 3 THEN p1.OBJECT_ID
END,
index_id =
CASE au.TYPE
WHEN 1 THEN p1.index_id
WHEN 2 THEN p2.index_id
WHEN 3 THEN p1.index_id
END,
bd.FILE_ID,
bd.page_id,
bd.page_type,
bd.page_level
FROM sys.dm_os_buffer_descriptors bd
INNER JOIN sys.databases d
ON bd.database_id = d.database_id
INNER JOIN sys.allocation_units au
ON bd.allocation_unit_id = au.allocation_unit_id
LEFT JOIN sys.partitions p1
ON au.container_id = p1.hobt_id
LEFT JOIN sys.partitions p2
ON au.container_id = p2.partition_id
LEFT JOIN sys.objects o1
ON p1.OBJECT_ID = o1.OBJECT_ID
LEFT JOIN sys.objects o2
ON p2.OBJECT_ID = o2.OBJECT_ID
WHERE is_modified = 1
AND d.name = 'DirtyPagesDB'
AND
(
o1.name = 't1'
OR o2.name = 't1'
);
GO

confirm time of last checkpoint

SELECT  f1.[Checkpoint Begin], f2.[Checkpoint End]
FROM    fn_dblog(NULL, NULL) f1
        JOIN fn_dblog(NULL, NULL) f2
             On f1.[Current LSN] = f2.[Previous LSN]
WHERE   f2.Operation IN (N'LOP_BEGIN_CKPT', N'LOP_END_CKPT');

Add more rows

INSERT INTO t1 VALUES ('SQL'),('Authority')

Use query above to confirm that there were dirty pages

Kill the SQL Server task from task manager to simulate a power off.

Start the service

Re-run the command above to get the last checkpoint time, it was the same (IE no checkpoints have run other than the one we did manually)

SELECTed from the table t1 and all four records were there

Both the log and data page entries are made in RAM and committed to
  disk later by a Checkpoint.

This statement is not completely true. It is correct that data pages are written to disk by Checkpoint (and Lazy Writer). Log records, however, are physically written to disk when the transaction is committed to guarantee transaction durability. A committed transaction data will never be only memory resident (barring delayed durability).

All data modifications are first written to the log (write-ahead logging) and dirty pages written afterward. Pages and log records may include both committed and uncommitted data on disk.

Regardless of the recovery model, SQL Server scans the log during crash recovery to the last checkpoint, rolls forward all data modifications from that point forward, and finally rolls back uncommitted transactions.


Creating MySQL Trigger I have faced syntax error during creating MySQL Trigger.

Create Trigger Count_McNum
before insert on tension
for each row
declare rowcount int ;
set rowcount = (select count(McNum) from tension where median is null) ;
if (rowcount >1000)
then
SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Warning:  rowcount> 1000!';
    END IF;
End

  1064 - You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use
  near 'declare rowcount int' at line 4

You're missing the small keyword BEGIN. And as you have multiple statements, you have to change the delimiter.

DELIMITER $$
Create Trigger Count_McNum
before insert on tension
for each row
BEGIN
declare rowcount int ;
set rowcount = (select count(McNum) from tension where median is null) ;
if (rowcount >1000)
then
SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Warning:  rowcount> 1000!';
    END IF;
End$$
DELIMITER ;


Tempdb log growth 3x as slow on new server, SP does not perform We are running into the issue that our stored procedure on our new SQL Server performs a lot slower than on the old server. We looked into the problem and noticed the following on our temp db. The incremental growth takes 3 times as long on the new server. The same happens for the log file of our database. What is causing this?

While determining why the growth is taking longer on the new server is a task that can depend a lot o your environment, as noted by David Spillett's comment, it's likely that you might have sized the tempdb in a way that is not optimal for your server, causing auto growth to be triggered frequently.
As Brent Ozar says on his Cheat Sheet: How to Configure TempDB for Microsoft SQL Server:

configure one volume/drive for TempDB. Divide the total space by 9,
and that’s your size number. Create 8 equally sized data files and one
log file, each that size. Presto, the drive is full and your TempDB is
configured for easy performance.

Sizing tempdb properly is also listed on the Optimizing tempdb performance in SQL Server doc as a good practice:

Preallocate space for all tempdb files by setting the file size to a
value large enough to accommodate the typical workload in the
environment. Preallocation prevents tempdb from expanding too often,
which affects performance. The tempdb database should be set to
autogrow to increase disk space for unplanned exceptions.

Finally, if your new server has more than 64 CPUs, the doc on Best Practices for running SQL Server on computers that have more than 64 CPUs says:

Do not rely on autogrow to increase the size of the transaction log
file. Increasing the transaction log must be a serial process.
Extending the log can prevent transaction write operations from
proceeding until the log extension is finished. Instead, preallocate
space for the log files by setting the file size to a value large
enough to support the typical workload in the environment.

Considering mainly the point about preventing transaction write operations, if the SP that is not performing does any write operation, it would be affected by the autogrow.
So, even though this advice does not answer your question about the reason the growth is taking longer to be performed, I thought it was worth mentioning it.


Lock Master Log Position in Master-Master replication I have two servers connected via private IPs. Replication is partially working, however the problem I'm running into is when I change the MASTER_LOG_POS on Server B using Server A's SHOW MASTER STATUS; command, Server A changes the log position immediately after.

Is there any way to stop MySQL from changing this position everytime? I just want them to sit still!

Below is what Server A's master status is currently. It'll increase (seemingly randomly) to something like 1583 if I try to apply that log position on Server B.

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1451 | db1,db2,db3  |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

You can try following method

  Flush tables with read lock on master(It will lock the master)
  show master status
  use this position accordingly in change master to command
  unlock tables(It will unlock master)

Hope it helps.


Reset search_path to the global, cluster default Say I permanently alter my database's search_path like this:

ALTER DATABASE my_db SET search_path TO "$user",public,other_schema;

How can I reset it back to whatever the default that a pristine, unaltered database on this cluster would have?

This is usually "$user",public, but that can be changed from the configuration file. Having a way to read the value of parameters that come from the configuration file would work (so I could build a dynamic query at worst), but I haven't had any luck finding anything of the sort.

Factory defaults

  How can I reset it back to whatever the default that a pristine,
  unaltered database on this cluster would have?

You can read boot_val from pg_settings, but that's actually the compiled-in factory default, not the setting in postgresql.conf.

SELECT boot_val
FROM   pg_settings
WHERE  name LIKE 'search_path';

You aren't overlooking the simple RESET?

To just reset the setting (remove it), you can simply:

ALTER DATABASE my_db RESET search_path;

But that doesn't necessarily give you what "a pristine, unaltered database on this cluster would have". It just removes the custom setting for the database. There are several ways to set the search path:

How does the search_path influence identifier resolution and the “current schema”

And it does not change the search_path of your current session. The effect (if any) is visible in your next session.

Related:

What is the search_path for a given database and user?

Connect to template1

You could get the current setting from a connection to template1, like @Abelisto suggested. But you probably want to stay within your current session. You could use dblink for that:

SELECT * FROM dblink('hostaddr=127.0.0.1 dbname=template1 user=postgres password=you_wish'
                         , $$SELECT boot_val, source FROM pg_settings
                             WHERE name LIKE 'search_path'$$
                          ) AS t(search_path text, source text);

search_path    | source
---------------+---------
"$user",public | default

This has a couple of flaws:

You need the additional module dblink installed. That's simple:

Persistent inserts in a UDF even if the function aborts

You need the privilege to connect to template1. And you need to supply the user pw.
Even in template1 the setting might stem from settings to the role or database (or another instance) as well (even though they probably don't). To make sure check the columns source. If it says 'default', you got it. If it says 'database' or 'user' you are back to square 1 ...

Read actual setting in postgresql.conf

.. usingpg_read_file(), but this is also subject to some important restrictions. The manual:

  Use of these functions is restricted to superusers.

And:

  Only files within the database cluster directory and the log_directory can be accessed.

Bold emphasis mine.
If your config files are somewhere else (like in default installations on Debian and friends) you would have to create a symbolic link in the file system in your data or log directory to your actual config file.

SELECT substring(txt, $$\nsearch_path\s*=\s*'([^']+)'$$) AS search_path
FROM   pg_read_file((SELECT setting FROM pg_settings WHERE name = 'config_file')
                  , 0, 10000000) AS txt;  -- arbitrary 10 MB max.

search_path
------------
public

Note how I fetch the path to the config file dynamically with:

SELECT setting FROM pg_settings WHERE name = 'config_file'

Then I use a regular expression with substring() to extract the setting:

\nsearch_path\s*=\s*'([^']+)'

That should work, I didn't spend much time to make the regexp bullet-proof, though. Might be fooled by a commented setting that matches first or something. You might want to test some more.

Finally, since Postgres 9.4, there is also the ALTER SYSTEM command to override settings in a postgresql.auto.conf file. You'll have to check that, too.