Repmgr : It automatically promotes to new master but other standby stopped I'm trying to setup postgre with repmgr and here is the rsult

The scenario is,

i tried to spinup 1 primary and 2 standby.
then i stop the primary so the postgres-2 got promoted.
Unfortunately the postgres-3 got disconnected due to some reason but here is the error log

It looks like it was able to connect but the postgres restared and didn't come back.
4. I spin up another standby but the master it was pointing is the old one which is postgres-1
That might be the reason why it is saying !running and still primary even if the actual primary is postgres-2.
My question is how can i make other standby not disconnected every time i promoted (automatically due to failure etc.) a new primary?
here is my repmgr.conf
NET_IF=`netstat -rn | awk '/^0.0.0.0/ {thif=substr($0,74,10); print thif;} /^default.*UG/ {thif=substr($0,65,10); print thif;}'`
NET_IP=`ifconfig ${NET_IF} | grep -Eo 'inet (addr:)?([0-9]*\.){3}[0-9]*' | grep -Eo '([0-9]*\.){3}[0-9]*' | grep -v '127.0.0.1'`

HOSTNAME='postgres-'${my_node}

cat<<EOF > /etc/repmgr.conf
    node_id=${my_node}
    node_name=$HOSTNAME
    conninfo='host=${NET_IP} user=repmgr password=repmgr dbname=repmgr connect_timeout=2'
    data_directory='${PGDATA}'

    log_level=INFO
    log_facility=STDERR
    log_status_interval=300

    pg_bindir='/usr/lib/postgresql/10/bin'
    use_replication_slots=1

    failover=automatic
    promote_command='repmgr standby promote'
    follow_command='repmgr standby follow -W'
EOF

Also, I'm running using docker extending the official postgres docker image
FROM postgres:10

RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main 10" \
          >> /etc/apt/sources.list.d/pgdg.list

# RUN ln -s /home/postgres/repmgr.conf /etc/repmgr.conf

RUN apt-get update && apt-get install wget -y
RUN apt-get install net-tools -y
RUN apt-get update; apt-get install -y git make postgresql-server-dev-10 libpq-dev postgresql-10-repmgr repmgr-common

#RUN wget -c https://repmgr.org/download/repmgr-5.1.tar.gz -O - | tar -xz

RUN touch /etc/repmgr.conf; \
    chown postgres:postgres /etc/repmgr.conf

ENV PRIMARY_NAME=localhost
ENV REPMGR_USER=repmgr
ENV REPMGR_DB=repmgr
ENV REPMGR_PASSWORD=repmgr

COPY postgresql.replication.conf /tmp/postgresql.replication.conf

COPY scripts/*.sh /docker-entrypoint-initdb.d/

Lastly, here is how i check if should be register as primary or standby
PGHOST=${PRIMARY_NAME}

installed=$(psql -qAt -h ${PGHOST} repmgr -c "SELECT 1 FROM pg_tables WHERE tablename='nodes'")

if [ "${installed}" != "1" ]; then
    echo "Registering as PRIMARY SERVER"
    repmgr primary register
else
    my_node=$(grep node_id /etc/repmgr.conf | cut -d= -f 2)
    is_reg=$(psql -qAt -h ${PGHOST} repmgr -c "SELECT 1 FROM repmgr.nodes WHERE node_id=${my_node}")

    if [ "${is_reg}" != "1" ] && [ ${my_node} -gt 1 ]; then
        echo "Registering as STANDBY SERVER"

        pg_ctl -D ${PGDATA} stop -m fast
        rm -Rf ${PGDATA}/*
        repmgr -h ${PRIMARY_NAME} -d repmgr standby clone --fast-checkpoint
        pg_ctl -D ${PGDATA} start &
        sleep 1
        repmgr -h ${PRIMARY_NAME} -d repmgr standby register
    fi
fi

here is my update for the postgres.conf

sed -i "s/#*\(shared_preload_libraries\).*/\1='repmgr'/;" ${PGDATA}/postgresql.conf
sed -i "s/#port = 5432/port = 5432/g" ${PGDATA}/postgresql.conf
sed -i "s/#max_wal_senders/max_wal_senders/g"  ${PGDATA}/postgresql.conf
sed -i "s/#wal_level/wal_level/g"  ${PGDATA}/postgresql.conf
sed -i "s/#max_replication_slots/max_replication_slots/g"  ${PGDATA}/postgresql.conf
sed -i "s/#hot_standby/hot_standby/g"  ${PGDATA}/postgresql.conf

sed -i "s/#archive_mode = off/archive_mode = on/g"  ${PGDATA}/postgresql.conf

echo "archive_command = '/bin/true'" >>  ${PGDATA}/postgresql.conf

I'm using postgres:10 and repmgr-5.0
hope someone could help me on this.
Thanks,

I've posted this response on GitHub:
At this point we haven't made any particular provision for repmgr to run in Docker, so it's possible there may be issues of one kind or another.

I also tried adding this
# service_start_command='pg_ctl -D ${PGDATA} start'
# service_stop_command='pg_ctl -D ${PGDATA} stop -m fast'
# service_reload_command='pg_ctl -D ${PGDATA} reload'
#service_restart_command='pg_ctl -D ${PGDATA} restart -m fast'

but same result.

Did you try adding these items without the leading #? I.e.
service_start_command='pg_ctl -D ${PGDATA} start'
service_stop_command='pg_ctl -D ${PGDATA} stop -m fast'
service_reload_command='pg_ctl -D ${PGDATA} reload'
service_restart_command='pg_ctl -D ${PGDATA} restart -m fast'

By default, when restarting a node for a standby follow operation, repmgr will stop then start the server using pg_ctl, as pg_ctl restart has proven to be problematic in some environments. However the opposite might be the case here. Either way we strongly recommend using the OS level service commands where available to avoid issues like this (not sure if those would be available here).


Time stamp of Active and Inactive session by JDBC The following explains how exactly the Middle tier handles the connection pool and connections to the DB (Oracle 10g):

Inactive session in Oracle by JDBC

What I would like to know from DB point of view is at which point in time a particular session from the connection pool has become active and how long is that particular session or session ID is been active and idle?

The following gets me the no.of sessions made to the DB

( select sysdate,
         TRUNC(logon_time,''HH''),
         COUNT(*) NO_OF_SESSIONS,
         username
  from v$session
  where username IS NOT NULL
  GROUP BY TRUNC(logon_time,''HH''),
           USERNAME
)

Unless the application or middle tier supports distinguishing the application sessions from the database sessions (for example setting module, action), this will not happen.

The closest you can get to this is querying V$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY, but this is just sampled data, and it requires Enterprise Edition + Diagnostic Pack licensed.


Slony not replicating changes from a dump file I’m working on a project where we are using pg_dump to get the data out of one database and then using psql to load it into another.  The second database is the master db in a Slony replication cluster.

No matter what options I specify in pg_dump the changes are not being replicated.
I’ve use a standard dump which uses COPY, a --inserts dump which uses INSERT INTO and a --insert-columns dump which adds column names to the INSERT INTO statements.

If I edit the data in pgAdmin III using the view option for the table, that change is replicated just fine.

What is the difference between editing the data that way in pgAdmin III and using psql to load a dump file?

Make sure you are not using the --disable-triggers option of pg_dump since slony replication is based on triggers. Check the dump file for ALTER TABLE <table_name> DISABLE TRIGGER ALL statements.


Query function won't take rows with given default value I have only recently delved into the depths of PostgreSQL and have finally hit a snag. There are 3 tables, Notes, Tags, and Notes_Tags (Many to Many). Some Notes do not contain tags, but still need to be displayed. Maybe there is something that I have a poor understanding of that is not quite apparent to me, but I can't seem to figure out how to get it to display Notes that do not have Tags.

Here is the query I've built:

    select n.id, n.created_at, n.title, n.body,
    case count(t.name) when 0 then '{}' else array_agg(t.name) end as noteTags
    from notes_tags nt
    inner join tags t on t.id = nt.tag_id
    left outer join notes n on n.id = nt.note_id
    where n.user_id = userId and
      case
        when tagFilter = 'all' or tagFilter = '' then t.name != ''
            else t.name = tagFilter
        end
    group by n.created_at, n.title, n.body, n.id
    order by n.created_at DESC
    limit note_limit;

If you want to display the Notes that do not have Tags you have to replace the left outer join for right outer join since you want to keep all the registers from the right table of the join.

So if the rest of your query is correct this one should be what you are looking for

select n.id, n.created_at, n.title, n.body,
case count(t.name) when 0 then '{}' else array_agg(t.name) end as noteTags
from notes_tags nt
inner join tags t on t.id = nt.tag_id
right outer join notes n on n.id = nt.note_id
where n.user_id = userId and
  case
    when tagFilter = 'all' or tagFilter = '' then t.name != ''
        else t.name = tagFilter
    end
group by n.created_at, n.title, n.body, n.id
order by n.created_at DESC
limit note_limit;


Reverse Byte-Order of a postgres bytea field I'm currently working on a table that contains hashes, stored in bytea format. Converting the hashes to hex-strings however yields the wrong order of bytes. Example:

SELECT encode(hash, 'hex') FROM mytable LIMIT 1;

Output: 1a6ee4de86143e81
Expected: 813e1486dee46e1a

Is there a way to reverse the order of bytes for all entries?

Here is one method of doing it, however I would never do this. There is nothing wrong with storing bytes in a database's bytea column. But, I wouldn't bit wrangle in the database, and if I did I would use,

a C language function, or
some fancy procedural language that didn't require me exploding the inputs into a set of bytes.

This is sql-esque and should work -- here is what we're doing,

Generate a set consisting of a series of offsets 0 - (bytelength-1).
Map those offsets to bytes represented as strings of hex.
String aggregate them in reverse order.

Here is an example,

CREATE TABLE foo AS SELECT '\x813e1486dee46e1a'::bytea AS bar;

SELECT bar, string_agg(to_hex(byte), '') AS hash
FROM foo
CROSS JOIN LATERAL (
  SELECT get_byte(bar,"offset") AS byte
  FROM generate_series(0,octet_length(bar)-1) AS x("offset")
  ORDER BY "offset" DESC
) AS x
GROUP BY bar;

Two notes,

We could probably not use offset because it's reserved but you get the point.
This assumes that your hash (bar in the above) is UNIQUE.


How does this derived table with cross apply work? I borrowed some code on how to compact and uncompact a string of diagnosis codes in order to build a bridge table for my data warehouse. It works well. However, I just do not understand how it is doing the uncompacting. Here Is the SQL Fiddle for the below code

create table dimDiagnosisGroup (dxGroupKey int, dxCodeList nvarchar(1024))
insert into dimDiagnosisGroup
values  (1,'042, 070.70, 722.10'),
        (2,'042, 070.70, 780.52, 496, 716.90, 581.9'),
        (3,'042, 070.70, 782.1, V58.69'),
        (4,'042, 070.70, 782.3, V58.69')

 WITH XMLTaggedList AS (
     SELECT dxGroupKey,
        CAST('' + REPLACE(dxCodeList, ', ', '') + '' AS XML)
            AS Diagnosis_Code_List
     FROM dimDiagnosisGroup
 )
     SELECT dxGroupKey,
            ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)') AS dxCodeList2
          FROM XMLTaggedList
        CROSS APPLY Diagnosis_Code_List.nodes('//I') AS ExtractedDiagnosisList(X)

I understand the XMLTaggedList part fine. What I am not understanding is the Cross Apply to the ExtractedDiagnosisList(X), then the ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)').

When I hover over ExtractedDiagnosisList in the select statement, SSMS says it is a derived table. However, it kind of looks like a function to me. I am not understanding how Diagnosis_Code.List gets the .nodes('//I') function. Finally, the ExtractedDiagnosisList.X.value section just looks foreign to me, in SQL. It looks like syntax from a language like C#.

What you`re seeing is the XQuery implementation in SQL Server . Although XQuery uses its own parser and performs its own algebrarization during the query compilation stage, the results are combined and
optimized together with the DML portion of the query, then combined into a single execution plan.

SQL Server supports five different methods.  value , exist , query , and
nodes are used to access and transform the data. Last one, modify , uses XML DML to modify the data.

The value() method returns a scalar value from the XML instance
Lets say you have the xml : `

declare @X xml =
'<Order OrderId="42" OrderTotal="49.96">
<Customer Id="123"/>
<OrderLineItems>
<OrderLineItem>
<ArticleId>250</ArticleId>
<Quantity>3</Quantity>
<Price>9.99</Price>
</OrderLineItem>
</OrderLineItems>
</Order>’

select @X.value('/Order[1]/Customer[1]/@Id','int')`or
select @X.value('(/Order/Customer/@Id)[1]','int')

Would give you ID of the first customer from the first order

In your particular case value('.') means give me all values from the shredded element( that i will talk about in a moment)
Remember in using value function you`re moving through XML. Now to make it more easier ,not to 'move' too much you can use function node which
shreds XML into relational data. It returns a row set with rows representing the nodes identified by the path expression.

Example:

declare @X xml =
'<Order OrderId="42" OrderTotal="49.96">
<Customer Id="123"/>
<OrderLineItems>
<OrderLineItem>
<ArticleId>250</ArticleId>
<Quantity>3</Quantity>
<Price>9.99</Price>
</OrderLineItem>
</OrderLineItems>
</Order>'

select C.t.value('(@Id)[1]','int')
from  @X.nodes('/Order/Customer') as C(t)

When you use the nodes() method with the XML column from the table, you must use the APPLY
operator.

Example:

declare @X as table (xmlCode varchar(1000))
insert into @X values (
'<Order OrderId="42" OrderTotal="49.96">
<Customer Id="123"/>
<OrderLineItems>
<OrderLineItem>
<ArticleId>250</ArticleId>
<Quantity>3</Quantity>
<Price>9.99</Price>
</OrderLineItem>
</OrderLineItems>
</Order>');

WITH CTE AS(
select CAST(xmlCode as XML) as X from @X
)
select C.t.value('(@Id)[1]','int')
from  CTE
CROSS APPLY X.nodes('/Order/Customer') as C(t)

I used examples on value and node, because you provided the code with those two functions only, if you want to read more about it please visit this

Hope this simple examples give you an idea of how to query xml types


Find partial match with textsearch query I have the following query that I did hard looking on this site:
SELECT *
FROM   renns
WHERE
    (
        to_tsvector('english', coalesce(numar_admin, '')) ||
        to_tsvector('english', coalesce(nume_strada, '')) ||
        to_tsvector('english', coalesce(nume_prenume, ''))||
        to_tsvector('english', coalesce(bloc, '')) ||
        to_tsvector('english', coalesce(apartament, ''))
    ) @@ plainto_tsquery('english', '$q')

It works if I write in the variable $q all the text that appears in one of the columns present in the query. For example: cherry
I would like to write only: cher and return all rows fields in which it finds this string: "cher".

Postgres text search supports prefix matching:
SELECT *
FROM   renns
WHERE (
   to_tsvector('english', coalesce(numar_admin, '')) ||
   to_tsvector('english', coalesce(nume_strada, '')) ||
   to_tsvector('english', coalesce(nume_prenume, ''))||
   to_tsvector('english', coalesce(bloc, '')) ||
   to_tsvector('english', coalesce(apartament, ''))
) @@ to_tsquery('simple', $q || ':*');

($q being a single word.)
This can use an index on your expression:
(to_tsvector('english', coalesce(numar_admin , '')) ||
 to_tsvector('english', coalesce(nume_strada , '')) ||
 to_tsvector('english', coalesce(nume_prenume, '')) ||
 to_tsvector('english', coalesce(bloc        , '')) ||
 to_tsvector('english', coalesce(apartament  , '')))

Using the 'simple' dictionary disables stemming. If you want stemming, use the 'english' dictionary like you had before.

"caisului 498:*"  the "CAISULUI" string is from one column and the number from another column of the renns table

To pass multiple lexemes, apply 'english' stemming and do prefix matching for all of them:
@@ (to_tsquery('english', column_a || ':*') &&
    to_tsquery('english', column_b || ':*') -- more?
   )

Detailed explanation:

Get partial match from GIN indexed TSVECTOR column

Prefix matching matches at the start of lexemes. For any partial match, you can use LIKE or regular expression matches (~) in combination with a trigram index. Different approach! Requires a different query and a different index! See:

How to create an index to speed up an aggregate LIKE query on an expression?
Faster query with pattern-matching on multiple text fields
How is LIKE implemented?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

There are smart ways to integrate both. The manual has an example.


How to store real-time data to database Here is my situation:

I have a WebSocket connection that feeds me data real-time
I have an async callback function that gets the data and inserts into a queue
I have another thread that reads from the queue and stores it into the Postgres database, one row at a time (using python's psycopg2 library)

The problem is the real-time data comes in faster than the time it takes to insert into the database, and in a couple of hours, the server runs out of memory. (Is this because psycopg2 library is slow?)

A simple solution is to create more threads that insert into the database; however, this will cause the data to be out of order. Is there a database that sorts the data? Other suggestions would be very much appreciated.

Generally speaking with relational databases, 1000 single-row inserts will be slower than a single 1000-row insert. Batching up your data in memory and loading to the database may be faster.

As you receive data into memory, you build a queue. Then, you pop the top item from your queue and insert it into the database. Instead, pop the top N items and insert them all in a single INSERT statement. The ideal value for N isn't universal, so you'll have to experiment. Try starting at 1000, 10000, and going from there based on the throughput results.

I've also seen (and supported) architectures where high volume data is streamed to a file, then asynchronously loaded to the DB. This helps ensure that your receiving service never runs out of memory and crashes (thus losing data in memory), but it does add some complexity, and doesn't guarantee that you'll always load data faster than it comes in. Rather, it just separates it into a separate problem to solve. In this configuration, your receiving service would write data to files until they are N MB/rows in size. Then, you roll over into the next file. (Personally, I like datetime stamps on file names to preserve file order.) Asynchronously, a separate service picks up the less-than-most-recent files and loads them to the database, (using COPY or whatever Python library you find for that).


What are the consequences of setting varchar(8000)? Since varchar takes disk space proportional to the size of the field, is there any reason why we shouldn't always define varchar as the maximum, e.g. varchar(8000) on SQL Server?

On create table, if I see anyone doing varchar(100) I should tell them no you are wrong you should do varchar(8000)?

Length is a constraint on the data (like CHECK, FK, NULL etc)
Performance when the row exceeds 8060 bytes
Can not have unique constraint or index (key column width must be < 900)
The default is SET ANSI PADDING ON = potential for lots of trailing spaces to be stored
SQL Server will assume average length is 4000 for sort operations, allocating memory based on this (need to find a link to back this up but rust me while I do :-)

Summary: don't do it.


How to optimise very simple query with subquery that only needs to execute once SELECT products_id, products_model, products_iban_no, products_allocation_number FROM products WHERE products_model IN (SELECT products_model FROM products GROUP BY products_model HAVING COUNT(products_model) > 1)

For whatever reason, it would seem that the subquery is being run for each product. I only need it to be run once really - and would have thought that mysql would intelligently realise this. Otherwise I would have written it like

SELECT products_id, products_model, products_iban_no, products_allocation_number FROM products AS a WHERE (SELECT COUNT(products_model) FROM products AS b WHERE a.products_model = b.products_model GROUP BY products_model) > 1

But bizzarely - the second query is actually faster by a factor of precisely 3 (31 seconds as opposed to 1 minute 33 seconds), despite being a "foreach" way of thinking rather than "set theory" way of thinking. I thought the latter was supposed to be much quicker? Clearly I have written the first query incorrectly - but how?

Have you tried with a join?

SELECT a.products_id, a.products_model, a.products_iban_no
     , a.products_allocation_number
FROM products a
JOIN (
    SELECT products_model
    FROM products
    GROUP BY products_model
    HAVING COUNT(products_model) > 1
) as b
    on a.products_model = b.products_model


How to remove blockages in MongoDB when multiple write query comes for same collection at same time I want to use MongoDB in a clustered environment with 3 shards and make each shard replicated. MongoDB is a single threaded so when multiple requests hit shard 1 to insert or edit one collection at a same it causes blockages in the architecture.

How can I remove or prevent these blockages?

From the database perspective you can try using, MongoDB 3.0 with the Wired Tiger storage engine to eliminate these waits. MongoDB 3.0 with the Wired Tiger uses Document level blocking and thus brings down the waits considerably.

In case this is not possible, then you cannot do much on the database layer and will either need to live with these waits or will need to handle it from the Application layer.

A tentative approach, which can be used to  provide control of the waits from the Application layer can be to queue the respective transactions in the application so that the user does not need to make the changes multiple times.


MySQL slow query log: huge query time, no lock time, minuscule number of rows scanned I have a slow query log item with the following:

Query_time: 55  Lock_time: 0  Rows_sent: 739  Rows_examined: 739

No one in my group can explain it besides a guess about packet loss. Is there a common cause or set of causes for result like the above?

I'm going out on a limb here, but did you do an 'EXPLAIN' on this query?

If not, run "EXPLAIN" to see what the execution plan is. This will give you hints on optimization or indexing.

E.g. SOMETHING LIKE

EXPLAIN SELECT a.field1,b.field1 FROM tableA a LEFT JOIN tableB b ON b.a_id = a.id;

The result will be a nicely formatted report.

You may find there's unnecessary complexity in the query that can be pushed to the application or just factored out.


User Permisssions White List vs Black List I have Applications, Roles, Users, Permissions, Application_Permissions, User_Permissions, Role_Permissions, Application_Roles and Role_Permissions tables. So, permission can be defined at application level, role level and user level. Let's say User A belongs to Application X and Application X can have XX Permission. So, User A will get XX Permission. Now, I want to give all users of Application B with XX Permission but except User B. How can I fit this with my current design?
CREATE TABLE APPLICATION_PERMISIONS (APPLICATION_PERMISION_ID number(15) GENERATED AS IDENTITY, APPLICATION_ID number(15), PERMISION_DEFINITION_ID number(15)CONSTRAINT PK_SEC_APPLICATION_PERMISIONS PRIMARY KEY (APPLICATION_PERMISION_ID));
CREATE TABLE APPLICATION_ROLES (APPLICATION_ROLE_ID number(15) GENERATED AS IDENTITY, APPLICATION_ID number(15), ROLE_ID number(15)CONSTRAINT PK_SEC_APPLICATION_ROLES PRIMARY KEY (APPLICATION_ROLE_ID));
CREATE TABLE APPLICATIONS (APPLICATION_ID number(15) GENERATED AS IDENTITY, CODE varchar2(30), NAME_AR varchar2(255), NAME_EN varchar2(255), START_DATE date, END_DATE date CONSTRAINT PK_SEC_APPLICATIONS PRIMARY KEY (APPLICATION_ID));
CREATE TABLE PERMISION_DEFINITIONS (PERMISION_DEFINITION_ID number(15) GENERATED AS IDENTITY, CODE varchar2(30), NAME_AR varchar2(255), NAME_EN varchar2(255), START_DATE date, END_DATE date CONSTRAINT PK_SEC_PERMISION_DEFITIONS PRIMARY KEY (PERMISION_DEFINITION_ID));
CREATE TABLE ROLE_PERMISSIONS (ROLE_PERMISSION_ID number(15) GENERATED AS IDENTITY, ROLE_ID number(15), PERMISION_DEFINITION_ID number(15), START_DATE date, END_DATE date CONSTRAINT PK_SEC_ROLE_PERMISSIONS PRIMARY KEY (ROLE_PERMISSION_ID));
CREATE TABLE ROLES (ROLE_ID number(15) GENERATED AS IDENTITY, CODE varchar2(30) NOT NULL, NAME_AR varchar2(255), NAME_EN varchar2(255), START_DATE date, END_DATE date CONSTRAINT PK_SEC_ROLES PRIMARY KEY (ROLE_ID));
CREATE TABLE USER_PERMISSIONS (USER_PERMISSION_ID number(15) GENERATED AS IDENTITY, USER_ID number(15), PERMISION_DEFINITION_ID number(15)CONSTRAINT PK_SEC_USER_PERMISSIONS PRIMARY KEY (USER_PERMISSION_ID));
CREATE TABLE USER_ROLES (USER_ROLE_ID number(15) GENERATED AS IDENTITY, USER_ID number(15), ROLE_ID number(15)CONSTRAINT PK_SEC_USER_ROLES PRIMARY KEY (USER_ROLE_ID));

You would need two different user roles: one with the permission granted for most users, and one without the permission for User B.
In the world of security, grants or permissions should always be assigned according to the principle of least privilege (only what the user absolutely needs to do their job). As such, privileges should always be denied by default and assigned explicitly, as with a whitelist. They should never be assigned by default and blocked individually (blacklist). Implicit privileges, especially when combined with explicit ones, are more difficult to audit and troubleshoot. Keep it simple.


LEFT JOIN and handling NULL Values I have two tables.

Table 1:

A   B
------
1   'A'
2   'B'
3   'C'

Table 2:

   A   C
----------
   2   15
   3   20

I'm using the following query:

SELECT table1.A, table1.B, table2.C
FROM table1
LEFT JOIN table2
ON table1.A = table2.A

The problem arises in the output which is:

    A   B   C
---------------
    1   'A'  null
    2   'B'  15
    3   'C'  20

Is there a way to get the null as 0, as I have to do an order by on table2.C. Any help is appreciated.

My expected output is:

    A   B   C
---------------
    3  'C'  20
    2  'B'  15
    1  'A'  0

ps: I tried with IFNULL(table2.C, 0). Didn't work.

This may be the most efficient solution:

SELECT A, t1.B, t2.C
FROM   table1 t1
LEFT   JOIN table2 t2 USING (A)
ORDER  BY t2.C NULLS FIRST;

NULL values are sorted last by default. You can reverse that with NULLS FIRST. See:

PostgreSQL sort by datetime asc, null first?


Drop partition and rebuild global indexes "online" I am using Oracle 11g.

I have a requirement to drop a partition and rebuild global indexes. The query below does job well but BLOCKS all DML operations on the table until the indexes are rebuilt.

ALTER TABLE ABC DROP PARTITION PART1 UPDATE GLOBAL INDEXES;

But If I break the query into 2 parts and rebuilt indexes with ONLINE option separately, DML queries DOES NOT get blocked while indexes are being rebuilt

ALTER TABLE ABC DROP PARTITION PART1;
ALTER INDEX XYZ REBUILD ONLINE;

The down side of this approach is the time between the execution of drop partition and rebuilding index, those indexes will be unusable that might create performance problems.

So my question is, is there any option where I can drop partition and rebuild index "online" in one query? Currently I dont think we have following option.

ALTER TABLE ABC DROP PARTITION PART1 UPDATE GLOBAL INDEXES ONLINE;

Not in 11g.

Not even in 12c, but 12c makes life a bit easier, you can delay the "rebuild" part until a more appropriate time.

In 12c there is a new feature, called Asynchronous Global Index Maintenance. You can drop the partition, and that still leaves the global index usable, and marks the no longer needed index entries orphaned. Then later, you can clean up this index (DBMS_PART.CLEANUP_GIDX, or ALTER INDEX COALESCE CLEANUP, or the database can do it automatically for you as well (there is a pre-defined job for it called PMO_DEFERRED_GIDX_MAINT_JOB).


Min(date) in sum case statement I'm trying to write a statement where the settled date is less than the min(duedate) but I'm getting this error:

  Msg 130, Level 15, State 1, Line 32
  Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

This is the query I'm using - can anyone help please?

...
sum(case when
             cast(l.settleddate AS date) < min(cast(f.datedue as DATE))
         then 1
         else 0
    end) as BeforeFirstDue

DECLARE @myDate AS datetime
SELECT @myDate = min(cast(f.datedue as DATE)) FROM tablef f
SELECT
    sum(case when cast(l.settleddate AS date) < @myDate
             Then 1
             else 0
             end) as BeforeFirstDue
FROM tablel l


Processors: E5-4620 v2 vs E5-2620 v3 We're debating between:

R820 - E5-4620 v2 - 2.6GHz - 8 cores, 20MB Cache, 1333MHz max memory speed - max of 768GB ram - 8 core
R730 - E5-2620 v3 - 2.4GHz - 6 cores, 15MB Cache, 1866MHz max memory speed - max of 768GB ram - 6 core

I understand that we'd see better performance in the R820 for parallel processes, but would the R730's actually be better for SQL Server?  Using a benchmark site it appears that the E5-2620 is faster (86.72 vs 70.30) but rarely is CPU the bottleneck on a SQL Server.

How should we decide?  I am not asking for a product recommendation but what way to choose (SQL Server 2014 / rack mounted).

The way to gauge hardware performance is to be take a look at your current workload. So I would begin by quantifying that:

Take a look at Brent Ozar's perf tuning stuff
Identify your bottlenecks (are you seeing bad parallelization, CPU chugging away without finishing, memory, storage)
Determine what differences there will be between your new and old system.

You're right that SQL Server rarely bottlenecks at the CPU, but it's not impossible and you can tell whether you're seeing a CPU bottleneck by looking at some perf counters.

Then you'll know whether or not you'll see a performance improvement simply by updating the CPU to a faster one or one with more cores. If your SQL isn't written to take advantage of parallel processing, it won't matter if you throw more cores at it.

Additionally, there's more things I would want to consider in buying a system:

Upgradability - SQL Server will rapidly test the limits of your hardware if you let it, so try to anticipate that
Affordability - SQL Server licensing in 2014 is core-based, so this should be a consideration.

This is in addition to other IT concerns you'd want to keep in mind like maintainability, support contracts, and your space/power requirements.


How to export dump of some schemas exclude some tables I need to find the way make an quick and light weight dump export. It is the reason why I want to export only specific scheme and to exclude large tables.

I use Enterprise Manager for it. Data Movement > Move Row Data > Export to Export Files > Schemas > add need schemas > Show Advanced Options > Exclude Only Objects Specified Below > Add Another Row > select table and type need table names > next..next..next

EM generate next PL\SQL

declare
  h1 NUMBER;
begin
  h1 := dbms_datapump.open(operation =>'EXPORT', declare h1 NUMBER;
  begin
    h1 := dbms_datapump.open(operation => 'EXPORT',
                             job_mode  => 'SCHEMA',
                             job_name  => 'FAST_TRY_6',
                             version   => 'COMPATIBLE');
    dbms_datapump.set_parallel(handle => h1, degree => 1);
    dbms_datapump.add_file(handle    => h1,
                           filename  => 'efast.LOG',
                           directory => 'backup',
                           filetype  => 3);
    dbms_datapump.set_parameter(handle => h1,
                                name   => 'KEEP_MASTER',
                                value  => 0);
    dbms_datapump.metadata_filter(handle => h1,
                                  name   => 'SCHEMA_EXPR',
                                  value  => 'IN(''MYSCHEMA1'',''MYSCHEMA2'')');
    dbms_datapump.add_file(handle    => h1,
                           filename  => 'efast181015.DMP',
                           directory => 'backup',
                           filetype  => 1);
    dbms_datapump.set_parameter(handle => h1,
                                name   => 'INCLUDE_METADATA',
                                value  => 1);
    dbms_datapump.set_parameter(handle => h1,
                                name   => 'DATA_ACCESS_METHOD',
                                value  => 'AUTOMATIC');
    dbms_datapump.metadata_filter(handle      => h1,
                                  name        => 'EXCLUDE_NAME_EXPR',
                                  value       => 'IN (''"MYSCHEMA1"."MYTABLE1"'',''"MYSCHEMA2"."MYTABLE2"'')',
                                  object_type => 'TABLE');
    dbms_datapump.set_parameter(handle => h1,
                                name   => 'ESTIMATE',
                                value  => 'BLOCKS');
    dbms_datapump.start_job(handle       => h1,
                            skip_current => 0,
                            abort_step   => 0);
    dbms_datapump.detach(handle => h1);
  end;
  /

However, exported whole schemas with MYTABLE1 and MYTABLE2.

Afaik, that syntax never worked like that, I am surprised EM generated this (well, not really...)

grant dba to bp1 identified by bp1;
grant dba to bp2 identified by bp2;
create table bp1.t1 as select * from dba_users;
create table bp1.t2 as select * from dba_users;
create table bp2.t1 as select * from dba_users;
create table bp2.t2 as select * from dba_users;

$ cat exp1.par
schemas=BP1,BP2
exclude=table:" in ('"BP1"."T1"', '"BP2"."T2"')"

This does not work as expected, because the database compares the given strings to table_name and that does not include the owner:

$ expdp \'/ as sysdba\' parfile=exp1.par
...
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "BP1"."T1"                                  19.07 KB      27 rows
. . exported "BP1"."T2"                                  19.07 KB      27 rows
. . exported "BP2"."T1"                                  19.07 KB      27 rows
. . exported "BP2"."T2"                                  19.07 KB      27 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************

If you do this:

$ cat exp2.par
schemas=BP1,BP2
exclude=table:" in ('T1', 'T2')"

This excludes the tables, but from all schemas:

$ expdp \'/ as sysdba\' parfile=exp2.par
...
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************

What you can do is, specify the QUERY parameter with a condition that evaluates to false for the tables to be skipped. This will still export the table, but without any data (0 rows).

$ cat exp3.par
schemas=BP1,BP2
query=BP1.T1:"WHERE 1=0"
query=BP2.T2:"WHERE 1=0"

$ expdp \'/ as sysdba\' parfile=exp3.par
...
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "BP1"."T1"                                  14.99 KB       0 rows
. . exported "BP1"."T2"                                  19.07 KB      27 rows
. . exported "BP2"."T1"                                  19.07 KB      27 rows
. . exported "BP2"."T2"                                  14.99 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************


How to select data in a sub query only once? I have the following query:

SELECT
  ordno
from
  orders
where
  ordno NOT IN (SELECT ordno FROM invoices)

There are about 25000 records in each table. I have indexed the ordno column.

The query hangs without showing any output.

I imagine that the problem is that the select subquery is called 25000 times for each ordno in orders table.

How can I do this?

SELECT
  ordno
from
  orders
WHERE ordno NOT IN (
--  somehow select ordno from invoice table only once
)

If you LEFT JOIN the tables you can then check if the left ordno does not exist. Something like this:

SELECT o.ordno
FROM orders AS o
LEFT JOIN invoices AS i
ON i.ordno = o.ordno
WHERE i.ordno IS NULL;

I hope this helps you.


Using PostGIS to compute graph data I have a query which return me two dimension data:

SELECT POINT(value , extract(EPOCH FROM datetime)) AS pseudo_geom
FROM table

gaves me

pseudo_geom
---------------
(18,1439679940)
(29,1439680000)
(16,1439680060)

What I would like to do is to convert these data into a path type to be able to simplificate through ST_Simplify which accept a geometry type.

My goal is actually to reduce the number of points to display on a graph.

I tried with the path function and playing around with point(), string_agg() and grouping my data, with no result...

What could be the easiest and shortest way to do this?

I finally solved this using this scheme (not as simple as firstly expected):

SELECT ST_X(geom) as value, ST_Y(geom) as datetime from (
    select (ST_DumpPoints(simplifiedLine)).* as g1 from (
        SELECT ST_Simplify(ST_MakeLine(the_geom.resulted), 15) as simplifiedLine FROM (
            SELECT ST_MakePoint(value, extract(EPOCH FROM datetime)) as resulted FROM table
            ORDER BY datetime ASC
        ) as the_geom
    ) as g
) as pt

Hope that will help others.


Generate a file or a script that can be used to reconstruct a database on a different PC I'm trying to copy a database that I created in SQL server 2008 on a new PC without having to do so manually by creating a database on the new computer and recreating all the tables from the scratch. The copy doesn't have to have any of the data stored just the tables and the diagram, Is this possible? I read about restoring  or exporting the database but neither got me anywhere.

Using ssms, Right click the database .. tasks.. generate scripts and export the database as schema only. Schema only does not have data.

See reference : https://www.mssqltips.com/sqlservertip/2500/sql-server-2008-r2-generate-scripts-wizard-with-database-schema-and-data/


Ola Hallengren SQL Server Backup - CleanupTime When the @CleanupTime parameter is used in the [dbo].[DatabaseBackup] procedure, are the targeted old backup files deleted after each successful database backup?  Or are all the backups processed first and then cleanup is performed?

I'm having problems with free disk space and trying to give my network administrator an accurate spec for the size of the backup volume.

I read through Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups before posting.  I couldn't find an answer to my question there. If it is there, I missed it.  Just read through it again, but still don't see anything.

There's a separate @CleanupMode parameter that controls whether cleanup happens before or after. The default behavior is to do the cleanup AFTER. This is the safest option, but requires the most disk space. In the event of a failed backup, nothing is cleaned up, ensuring you have extra files, rather than having deleted a file without making a new one.

This is particularly important if backups were to fail repeatedly (cleaning up, but never making a new one) or when there's a very short retention.

From the documentation:

CleanupMode

Specify if old backup files should be deleted before or after the backup has been performed.

Values:

BEFORE_BACKUP - Delete old backup files before the backup has been performed.
AFTER_BACKUP - Delete old backup files after the backup and verify has been performed. If the backup or verify failed, then no backup files are deleted. This is the default.


Understanding relations Since english is not my first language i find hard to understand this paragraph that i'm reading from "Database Fundamentals" book.

I'm trying to figure out, in practice, real world, what this really means.

English is not the easiest language in the world to describe mathematical concepts and their relationship to the real world.  For a few years, I taught short courses in database design and programming in Spanish, even though Spanish is not my first language.  But there were a few concepts that came across more clearly in Spanish than they did in English.

The first thing you need to understand is that the relational data model was pretty much invented by one person, Ed Codd in 1970, and he based the data model on the mathematics of relations.  A great deal of the terminology is derived from terminology of mathematical relations, although there are subtle differences between mathematical relations and relations in the relational data model.

The second thing you need to understand is that "relation" and "relationship" are two different words with two different underlying concepts, even though there is a huge overlap between the two.  The word "relationship" tends to be used in ER modeling, and refers to an association between two or more entities in the real world.  These entities may be of different types or of the same type.

The third thing you need to understand is that you understand the subject matter through analysis but you come up with a relational model by design.  The difference between analysing the data and designing the database is often not clear even for many English speakers.

When you convert an E-R model into a relational model, both the Entities and the Relationships get expressed as relations.  so the difference between the Es and the Rs becomes almost moot as you make progress towards the eventual construction.

I apologize if this is too vague, but you'll have to clarify your question if you want more specific answers.


Is There a Single-File Based Graph Database Management System? I am considering using a graph database management system (DBMS for brevity) as an application file format for my single-user desktop application.

Is there a single-file based graph DBMS (the "equivalent" of SQLite for graph DBMSs)?

Quick google search showed this result

TinkerPop3
Embedded Neo4j (java)
Arango DB which does graphs


Difference between net start MongoDB and mongod According to MongoDB documentation,when running mongodb community edition, it states you can start mongo service using mongod, or net start MongoDB.

Either is required to access the mongo shell.

Can someone explain the difference between using mongod and net start MongoDB

You can always run mongod with a command. But if you decide to run mongod as a Windows service, in first step you need to set up a service by running mongod with --install option:

"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --config "C:\Program Files\MongoDB\Server\3.4\mongod.cfg" --install

Then, you can use net start/stop to use the installed service:

net start MongoDB


SQL Server : which role grants permission to execute all stored procedures? Which database role membership grants permission to execute all existing stored procedures in SQL Server 2012?

I tried adding a user to each of them and am still unable to execute a stored procedure. I don't want to grant EXECUTE for each stored procedure separately, I want to add the user to a role and he be able to execute any of them.

If you are using schemas other than the default dbo schema, create a database role per schema and grant EXECUTE on the schema to the role.

e.g.
For the default dbo schema:

CREATE ROLE role_exec_dbo
GO
GRANT EXECUTE ON SCHEMA::dbo to role_exec_dbo
GO

For a new schema:

CREATE SCHEMA mySchema
GO
CREATE ROLE role_exec_mySchema
GO
GRANT EXECUTE ON SCHEMA::mySchema to role_exec_mySchema
GO


WHERE clause not preventing return of row but changing values for aggregate function (here: count) I have recently discovered a PostgreSQL behavior which is strange and problematic in my opinion.

Consider this simple query

SELECT
   'something confidential'
WHERE FALSE;

The nonsense clause WHERE FALSE resembles here a very strict permission check.
That works as expected: Nothing is returned.

Now, imagine you add an additional column e.g. a count function.
This gives is this query:

SELECT
   'something confidential'
   ,count(CURRENT_DATE)
WHERE FALSE;

(The parameter of count could be anything. CURRENT_DATE is just a random example.)

Now, we get

  something confidential | 0

I guess, this is somehow on purpose, in the sense that SELECT count(CURRENT_DATE); returns 1 if the condition is truthy and 0 if the condition is falsy.

However, I consider this as problematic in situations where you are not aware of it.
So, my questions are

How come? What is the background of this behavior?
How can I make such a query return zero rows instead of one row with value 0 for count?
Is there a way directly in the query for preventing accidentally returning rows you don't want to return by adding aggregate functions? (I.e. apart from external tests etc.)

I'm using PostgreSQL 12.1, but the behavior is the same for older versions.

This is standard SQL behaviour for pretty much all databases.

There are three ways how rows are returned:

without aggregate functions, the query returns one row per row;
with aggregate functions, but without grouping, the query returns exactly one row;
with aggregate functions and grouping, the query returns one row for each group (i.e., for each unique combination of values in the grouped columns).

In other words, aggregating without grouping behaves as if there is a single group, i.e., as if you had written GROUP BY () (a GROUP BY clause with an empty list results in a single group 'from nowhere', similar to how a query without FROM results in a single row 'from nowhere').

To prevent returning this single-group row, you can

add an explicit GROUP BY, so that you end up in case 3. above (after the WHERE has filtered out all rows, there is no value from which a group could be created):

SELECT
   'something confidential',
   count(CURRENT_DATE)
WHERE FALSE
GROUP BY 1;  -- refers to the first column

move the aggregation into a subquery, so that the permission check affects the output of that:

SELECT *
FROM (SELECT
          'something confidential',
          count(CURRENT_DATE)
     ) AS subquery
WHERE FALSE;

move the permission check into the HAVING clause (if this is possible), so that the output for the single group is thrown away:

SELECT
   'something confidential',
   count(CURRENT_DATE)
HAVING FALSE;


how to reset value of auto incremented id to a specific value in mysql? i am migrating data from one db to another. A table is getting populated from combination of 4 tables. I am using stored procedures to populate new table. When i run it first time with some condition(where clause) it populates the table but when i run it second time with another condition, it skips few values in Id(auto incremented).

The following should do what you want where x is a value greater than the maximum value that appears in the column

ALTER TABLE table_name AUTO_INCREMENT = x

The cause of the skipped values is probably either failed inserts (where the insert fails a unique key check or similar) or insert on duplicate update queries both of which increment the auto_increment without creating a row.

If this is a problem for your migration you may be better off creating two copies of your new table table_name and table_name_temp.  Use your stored procedures to populate table_name_temp ignoring the gaps in the auto increment. Once table_name_temp is fully populated you can populate table_name with an insert select query from the table_name_temp of the table.

INSERT INTO table_name SELECT <all columns except the auto increment> FROM table_name_temp;

This will create a table with a continuous auto_increment


MySQL information_schema doesn't update I have a database, say abc, in mysql server. It has only one table named test. test uses innodb engine and I've set innodb_file_per_table to true.

After I run the query delete from abc.test, I want to calculate the database size of abc. Here is the query I use:

SELECT
    table_schema "name",
    sum( IF(engine = "MyISAM", data_length + index_length -  data_free,
    data_length + index_length)) "size"
FROM information_schema.TABLES
where table_schema like "abc";

The strange thing is that I find the database size doesn't decrease at all, however the data in "test" is gone.

I've done this kind of test many times, this strange behavior happens sometimes.

I'm using percona mysql server 5.5.29-rel29.4.

Can anybody tell me what is wrong?

Update:

Actually, I use another thread to check the database size periodically.

You need to rebuild the table in order to shrink the ibd file. This command will do it:

optimize table abc.test;


replace values of table from values of another table where both tables don't have same no of rows I have two tables at the moment.

Table1
   +-----+----+
     id    val
     1     a
     2     b
     3     c
     4     d
     5     e
   +-----+----+

Table2
 +----+-----+
  id    val
  1     aa
  2     bb
  3     cc
 +----+-----+

I want a resultant table to look like this replacing values in table1 from table2 like so and keeping the renaming intact

Resultant table
   +-----+-----+
     id    val
     1     aa
     2     bb
     3     cc
     4     d
     5     e
   +-----+-----+

I've tried the following code

UPDATE Table1
   SET Table1.val = Table2.val
   where Table1.id = Table2.id

but i get an error that

table2.id cannot be bound

Is there any other solution ? Bear in mind that both tables do not have same length

The error you're receiving is because you haven't included the table in your FROM clause in your update statement.

UPDATE T1
SET T1.value = ISNULL(T2.value,T1.value)
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.id = T2.id

The above code resolves the problem - you could also remove the ISNULL and change the join to an INNER join as below:

UPDATE T1
SET T1.value = T2.value
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.id = T2.id

dbfiddle here

For further details see the docs on update


Does failed task considered to be run to completion in SQL Server maintenance plan terminology? The replies to my previous question: "How to specify a SQL Server maintenance plan subtask to run unconditionally on success or failure of preceding tasks?" answered me that I should change "Evaluation operation:" in my maintenance plan from "Success" to "Completion".

Though, I am still in doubt about the term "Completion" and that the proposed solution will completely address my situation.

All tasks are configured over "All user databases" that:

I have not noticed immediately and added my own user (diagnostics, monitoring, testing) database which, then, took offline;
I am not permitted to change the maintenance plans without previous authorization from company management

So, the nightly databases backup (penultimate) task in screenshot above failed.
There is no diagnostics or logging except the fact that all tasks failed.

I corrected the situation by detaching the culprit offline database. So, after that, maintenance plan job started to complete successfully (with backup task).

But I could not understand:
if I leave a user database offline and all maintenance plan tasks fail to run due to this. Do the tasks considered coming to "completion"?

Some doubts remain because I made other changes which I am sure could not prevent maintenance pla completion. But who knows...

UPDATE:
As I could see by the duration of maintenance plan job in comparison to the successful job runs, the the tasks were not even started because their corresponding previous task did not start and they did not start because probably failed to find "All user databases".

So, the definition of:

  Completion requires only that the precedence executable has completed,
  without regard to outcome, in order for the constrained executable to
  run.

IMO. does not assure the previous task completion because it could have been simply not being started and run at all

Completion is a task state that is true if:

a. The task state is Success.

b. The task state is Failure.

A task will never reach any of these states until a TaskStart event has caused the task to start executing. A disabled task, or a task that is never executed, will not reach a state of Completion.


in what situations the dynamic port number in sql server is used? I have the following script to find out what is the Static and dynamic port numbers:

--===============================
-- sys.dm_exec_connections
--===============================
SELECT
[user]=suser_sname()
,local_tcp_port
,session_id
,connect_time
,net_transport
,protocol_type
,encrypt_option
,auth_scheme
,last_read
,last_write

FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

--===============================
-- Execute below script if SQL Server is configured with dynamic port number
--===============================
DECLARE       @portNo   NVARCHAR(10)

EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpDynamicPorts',
@value      = @portNo OUTPUT

SELECT [PortNumber] = @portNo
GO

--===============================
-- Execute below script if SQL Server is configured with static port number
--===============================
DECLARE       @portNo   NVARCHAR(10)

EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpPort',
@value      = @portNo OUTPUT

SELECT [PortNumber] = @portNo
GO

The following link mentions netstat -ano to check usage from the cmdline.

When is a Dynamic Port “dynamic”?

when is the dynamic port used?

Dynamic ports are used when a SQL Server instance is configured to use dynamic ports on the specified interface(s). By default, only named instances use dynamic ports as described in the documentation. Below is an excerpt from the doc:

  At startup, when an instance of SQL Server is configured to listen on
  dynamic ports, it checks with the operating system for an available
  port, and opens an endpoint for that port. Incoming connections must
  specify that port number to connect. Since the port number can change
  each time SQL Server starts, SQL Server provides the SQL Server
  Browser Service, to monitor the ports, and direct incoming connections
  to the current port for that instance. Using dynamic ports complicates
  connecting SQL Server through a firewall because the port number may
  change when SQL Server is restarted, requiring changes to the firewall
  settings. To avoid connection problems through a firewall, configure
  SQL Server to use a static port.

The SQL Server error log will also include messages indicating the listening interface IP addresses and ports.

EDIT:

As mentioned by @TiborKaraszi, SQL Server remembers that last used dynamic port and will continue to use that as the going forward unless there's a conflict, in which case a new one is used.


Why have SQL databases like PostgresSQL and MySQL added JSON data types? I'm looking for scenarios in which storing a JSON object in a relational database would be useful.

Anywhere you have or need EAV table, it's another way to solve the problem. Or anywhere you accept JSON, especially if the JSON is arbitrary.


I've come to the conclusion that what i wan't is not possible in MDX. But it don't need to be. I also realized MDX is not SQL. SQL returns a dataset. MDX returns a dataset and metadata. Combine the two and I get what I wanted.

Pulling up my original query into an Excel PivotTable it was exactly what I was after. So it came down to SSMS limitations at displying MDX queries, and my own lack of understanding what MDX queries actually return. And what one really needs is some kind of cube analysis client to really benefit from MDX.

So the result of an MDX query is much more then just the dataset it returns but also all the properties you specify to get with it as metadata.


12 fraction digits in SQL Server We are currently migrating from DB2 to SQL Server. DB2 supports TIMESTAMP(6-12) while DATETIME2 only supports up to 7 decimals. Since we have many keys represented as TIMESTAMP(12) what would be the best way of migrating these to SQL Server?

I was thinking we should store them as varchar(32) or char(32). This will allow storing all 12 fraction digits.

I assume order by ascending/descending will work the same with varchar/char as a datetime2.

I assume all SQL in the application can remain the same as datetime2 / timestamp values are represented as strings in SQL.

We will however have issues using time functions in SQL Server unless we cast back and forth (which probably won't perform well).

Any better suggestion?

Storing date/time data in a character-based column is a seriously bad idea.  And, no, datetime2/timestamp values are not represented as strings in SQL Server.  See Aaron Bertrand's excellent bad-habits blog series on the topic.

DB2's timestamp data type1 is:

  a seven-part value representing a date and time by year, month, day, hour, minute, second, and microsecond, in the range of 0001-01-01-00.00.00.000000000 to 9999-12-31-24.00.00.000000000 with nanosecond precision. Timestamps can also hold timezone information

This equates nearly perfectly to a datetime2(7) data type in SQL Server2.

  Time portion:  0 to 7 digits, with an accuracy of 100ns. The default precision is 7 digits.

Unless you are measuring exceptionally accurate particle accelerator physics, you likely don't need precision beyond 1 ns.

What time source are you using?

You mention in your question you are using these high-precision timestamp values as keys.  I would investigate the appropriateness of storing those values in a numeric(32,12) column in SQL Server.

This is a sample table with that type of key column:

IF OBJECT_ID(N'dbo.TimeTesT', N'U') IS NOT NULL
DROP TABLE dbo.TimeTest;
CREATE TABLE dbo.TimeTest
(
    ItemKey numeric(32,12) NOT NULL
        CONSTRAINT PK_TimeTest
        PRIMARY KEY CLUSTERED
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

Here, I insert a very large and precise value into the column:

INSERT INTO dbo.TimeTest (ItemKey)
VALUES (99999999999999999999.123456789012);

What that looks like:

SELECT *
FROM dbo.TimeTest;

+-----------------------------------+
|              ItemKey              |
+-----------------------------------+
| 99999999999999999999.123456789012 |
+-----------------------------------+

In order to successfully import your DB2 timestamp(12) values into SQL Server you'll need to export them as numeric values with the appropriate magnitude and precision.  If you can do that, you won't lose any data, and will gain the ability for SQL Server to properly validate data used in the key columns.   A numeric column is also extremely efficient for sorting, since it is not locale or collation sensitive.

Using a varchar(45) would allow keys to be created like this is a very long, non-numeric key; which would clearly be a bad idea.


MySQL stuck on INSERT INTO ... SELECT I have a problem with my database. I have a production server running MySQL 5.5.33 and a local test server running MySQL 5.5.5. The only other difference I can think of is that the prod DB is being replicated
They both run the same exact SQL databases (local is a replica of the prod environment).
I need to create a materialized view in MySQL which will contain a subset of the record_status_events table. Below you will find the query that does this. This same exact query works in under a second on local, but halts on prod. It gets stuck in the "Sending data" status. If I export the data and import it manually it works fine. It is just the INSERT INTO ... SELECT combo causing the problems.
The underlying query I am trying to insert runs under 150ms and has 50k entries.
I tried:

Changing the DB engine
Wrapping it in a transaction
Removing all indexes
Removing the primary key
Using the auto increment and ignoring the key from the source table

DROP TABLE IF EXISTS `hlh_record_status_submission`;
CREATE TABLE `hlh_record_status_submission` (
  `id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `record_id` int(10) unsigned NOT NULL,
  `status` tinyint(3) unsigned NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Created At` (`created_at`),
  KEY `status` (`status`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `hlh_record_status_submission`
SELECT * FROM `hlh_record_status_events`
WHERE id IN (SELECT MIN(id) FROM `hlh_record_status_events`
                  WHERE status IN (21,24,31,40) GROUP BY record_id);

Give this formulation a try:
INSERT INTO `hlh_record_status_submission`
SELECT rse.* FROM `hlh_record_status_events` AS rse
    JOIN ( SELECT MIN(id) AS min_id
              FROM `hlh_record_status_events`
              WHERE status IN (21,24,31,40)
              GROUP BY record_id
         ) AS x  ON min_Id = rse.id;

and replace INDEX(status) with
INDEX(status, record_id, id)

Also, you should really change all your tables to InnoDB.
The subquery (a "derived" table) will be faster because of the index starting with what is in its WHERE clause and because that index is "covering".  The JOINing to the outer part is (I assume) using the PRIMARY KEY(id) making that efficient.
Historically, the construct IN ( SELECT ... ) has been notoriously inefficient, so I try to avoid it.  JOIN and EXISTS ( SELECT ... ) are sometimes viable alternatives.
More discussion of building optimal indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql


What Is The Best Way To Clone A Database From Production To Development Environment? We use SQL Server at our company as DBMS. I have a scheduled job working monthly. The job restores every databases on production server to development server at the end of the month but there is insufficient disk issue on the development server. I truncate some big tables which are not important for development teams and shrink all databases after the restore completed. Shrinking databases takes too long time to increase free disk space on development server. Of course, this is not good solution. I need a better or the best solution for the situation. I need your advices. How can i use more efficient method? What would be the best solution?

Omer, It sounds like you are saying that you don't have enough space on the Dev server to restore all of the databases from prod without truncating some tables and shrinking the database to recover space. I'm unsure if you mean above that increasing the size of the drives on the Dev server is an option or not. That part is unclear.
I would push to increase the size of the drives and explaining to your storage team or Windows Admins that you need the space in order to avoid doing the damaging shrink operation as part of your restore process.


What is the name of this type of query, and what is an efficient example? The purpose is to find a parent, given it's children. For example, say you have a marketing package (aka a "combo"), and want to match it based on the products in it. Example table/data:

create table marketing_package_product (
  package_id int not null references marketing_package(id),
  product_id int not null references product(id),
  primary key (package_id, product_id)
);

insert into marketing_package_product values
(1,1),
(1,2),
(1,3),
(2,1),
(2,5);

Given products 1,2,3, I want to get marketing_package 1. But given products 1,2 only I do not want marketing_package 1.

Is there a name for this type of query, and what is the most efficient way to go about it?

The problem you are solving is called Relational Division with two variations, the "Division with Remainder" and "Exact division" (which fits your description).

See also this article: Divided We Stand: The SQL of Relational Division

This question at StackOVerflow: How to filter SQL results in a has-many-through relation has a few ways to solve it and benchmarks for Postgres but it's not for the exact variation, only for the "Division with Remainder". The queries will be similar but an extra check/condition has to be added for the exact variation.

One way (of the too many) to solve this would be to self-join the table as many times as the "products" and then an extra check so only packages with these exactly products are kept):

SELECT m1.package_id
FROM marketing_package_product AS m1
  JOIN marketing_package_product AS m2
    ON m2.package_id = m1.package_id
WHERE m1.product_id = 1
  AND m2.product_id = 2
  AND NOT EXISTS
      ( SELECT *
        FROM marketing_package_product AS x
        WHERE x.package_id = m1.package_id
          AND x.product_id NOT IN (1,2)
      ) ;

Another would be to GROUP BY and then two HAVING conditions, one for the division and the other for the "exactness":

SELECT m.package_id
FROM marketing_package_product AS m
GROUP BY m.package_id
HAVING COUNT(CASE WHEN m.product_id IN (1,2) THEN 1 END) = @n
   AND COUNT(*) = @n ;

where @n would be 2 in this case, the number of products in your check.

Test at SQL-Fiddle.


Retrieving the value of $1, $2 etc. from a Postgres Query I am currently trying to update/modify a number of SQL queries on a system that does not have any internal Documentation (Developer left on short notice and on bad terms).

I have enabled Query logging in Postgres and have tracked down the Queries in question to be in a number of approximately 30 queries. From there, I plan on running them and seeing what the output is and then determining which ones need the updates.

The problem is that all them in the log are in this format (Simplified)

Select t.Col1, t2.Col2, t3.Col3 from Table t
join table2 t2 on t2.Col1 = t.Col1
join table3 t3 on t3.Col1 = t2.Col3
where t.Col1 = $1
and t2.Col3 = $2
...
and t3.Col20 = $15

etc.

For some of the parameters, I've been able to guesstimate what the most likely values are (based on the info in the column it looks up), however there are a few that I've got no idea on what they could be.

I'm not super-familiar with Postgres (more of an MSSQL man myself) nor am I familiar with using parameters/variables in queries. It seems to me these values must be either generated somewhere or held somewhere and most be evaluated at some point in order to run a valid query. As such, either logging the values or logging the input or something similar should be possible so I can then finish what is required.

Hoepfully someone can advise how to get the 'real' values of the variables $1,$2, $3 etc.

If you set log_min_duration_statement = 0 in postgresql.conf and reload, all statements and the parameter values will be written to the log file.

Warning: on a busy system, logging all statements can be a bottleneck.


Non-English characters are not imported using SSIS or Visual Studio Deploy I'm having problems with importing data into a SQL Server 2008 database.

If I execute the following statement in SSMS, it works fine.

INSERT INTO
    [dbo].[ObjectiveRatingTranslation]
(
    [ObjectiveRatingId]
,   [TranslationId]
,   [Abbreviation]
,   [RateName]
,   [Description]
,   [CreatedBy]
,   [CreatedOn]
,   [ModifiedBy]
,   [ModifiedOn]
)
VALUES
(
    3
,   29
,   N'O / T'
,   N'På målet'
,   N'Målet har slutförts i enlighet med detta förväntningar. Uppfyller de slutprodukter som definieras. Det förväntas att de flesta mål bör vara Topplista på denna nivå.'
,   0
,   '2012-03-27T11:49:43.2300000'
,   0
,   '2012-03-27T11:49:43.2300000'
)

If I have this statement in  a script file that is being executed by VS 2010, Database, Deploy.  The data gets imported as

P� m�let | M�let har slutf�rts i enlighet med detta f�rv�ntningar. Uppfyller de slutprodukter som definieras. Det f�rv�ntas att de flesta m�l b�r vara Topplista p� denna niv�.

Same type of problem happens with SSIS on import to. I'm sure there must be some kind of setting that can be changed, but I"m not sure what.

The data types of the columns you are inserting the text into should be set to nvarchar.


Index planning for many columns with different usage I've a table with 80 columns and that is a base table for most of the application. Daily load inserts almost 8,000 records and update upto 2,000 records. This table is now having more than 5 million records. Unfortunately, I can't change the architecture and for next few months I have to continue with this.
Now, as I said there are multiple application connected to the table which are fetching data from it and almost all of them using different columns for their purpose. Example of few of those queries are:

SELECT Col1, Col2
FROM Table
WHERE Col3 = 'something'

SELECT Col4, Col5, Col6
FROM Table
WHERE Col7 IN ('A','B') AND Col1 = 'something'

SELECT Col1, Col2, Col7, Col28, ....
FROM Table
WHERE Col1 = 'Something' AND Col2 = 12 AND (Col2 > 2 OR Col7 <20)

As you can see that one column is being used in where clause and some query it is in select clause. I have created indexes and now I realized that I need more but that doesn't seems to be feasible to me as I will end up in having so many indexes.

  How can I implement index strategy in this kind of scenario?

Also,

  How to design index when those columns are coming up in different
  combinations?

Ex.:

SELECT Col1, Col2, Col3
FROM Table
WHERE Col3 = 'something' AND Col4 = 'something'

SELECT Col4,Col1, Col5
FROM table
WHERE Col3='Something'AND Col7 = 'something' AND Col67 = 'something'

out of 80 columns, application is using > 50 columns in where clause.

If the only thing that inserts / updates data in this table is an out-of-hours process I'd be tempted to apply as many indexes as you can.  999 are allowed for a SQL Server 2012 table.  You can determine how many you need using the Database Tuning Advisor.

Obviously this will slow down the load process and consume a lot more disk space -  both from the indexes and the additional log data - but you can create a script to Disable the indexes prior to the load & Enable them afterwards to speed things up.

If disabling indexes isn't available to you you can create a script to drop & re-create them.


How to restore several databases with binlogs? I need to restore 3 databases but I don't have the backups of the, I investigate that it can be done with the binlog files, but how does that work when you have several databases that need to be restored?

You can use the tool mysqlbinlog  to parse your binlogs, then you can use mysql client to run the parsed binlogs directly.

Maybe you should add some parameter to the tool mysqlbinlog just like '--start-datetime','--start-position'.It will filter out the binlogs which you do not need.


is my postgresql-server thread-safe? before 9.0 i had to compile my postgresql-server with "--enable-thread-safety" for http://www.bacula.org

since 9.0 the config-paramater is renamed to "--disable-thread-safety".

The "PostgreSQL Development Group"-Package for CentOS 6.2 doesn't use this parameter.
http://yum.postgresql.org/9.1/redhat/rhel-6-i386/

# pg_config --configure
'--disable-rpath' '--prefix=/usr/pgsql-9.1' '--includedir=/usr/pgsql-9.1/include'
'--mandir=/usr/pgsql-9.1/share/man' '--datadir=/usr/pgsql-9.1/share' '--with-perl'
'--with-python' '--with-tcl' '--with-tclconfig=/usr/lib64' '--with-openssl' '--with-pam'
'--with-krb5' '--with-gssapi' '--with-includes=/usr/include' '--with-libraries=/usr/lib64'
'--enable-nls' '--with-ossp-uuid' '--with-libxml' '--with-libxslt' '--with-ldap'
'--with-system-tzdata=/usr/share/zoneinfo' '--sysconfdir=/etc/sysconfig/pgsql'
'--docdir=/usr/share/doc'
'CFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -I/usr/include/et'
'CPPFLAGS= -I/usr/include/et'

I don't know, how bacula is testing the thread-safety.
http://www.bacula.org/de/dev-manual/Installi_Configur_PostgreS.html#tex2html63

after yum install postgresql91-devel
i have the file libpq.a and the check is successful:

nm /usr/pgsql-9.1/lib/libpq.a | grep pthread_mutex_lock
  U pthread_mutex_lock
  U pthread_mutex_lock

how can i check, that my database-server is thread-safety?

The thread-safety parameter is only relevant for the client library libpq it doesn't influence how the backend is build. The backend actually doesn't even use threads, it uses a process per connection.

edit
just noticed I didn't address the renaming of the configure parameter. The point with configure is that the build scripts can support optional features. If you have a feature X you can pass either --enable-X or --disable-X. However it's help output will only list one of the two based on the current default (enabled is the default since 9.0 for thread-safety).


Getting InnoDb internal Errors on every query run I am having following errors in error.log at the run of each query due to which I am not able to use error.log properly.

2016-05-30 09:39:58 7f7c7e3cb700 InnoDB: Error: Column last_update in table "mysql"."innodb_table_stats" is INT UNSIGNED NOT NULL but should be BINARY(4) NOT NULL (type mismatch).
2016-05-30 09:39:58 7f7c7e3cb700 InnoDB: Error: Fetch of persistent statistics requested for table "mysql"."innodb_table_stats" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

I have checked the  "mysql"."innodb_table_stats" and mysql.innodb_index_stats tables are present but still getting these errors.

The Structures for both tables are given below:

CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) UNSIGNED NOT NULL,
  `sample_size` bigint(20) UNSIGNED DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) UNSIGNED NOT NULL,
  `clustered_index_size` bigint(20) UNSIGNED NOT NULL,
  `sum_of_other_index_sizes` bigint(20) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

MySql Server details:
Server type: MySQL
Server version: 5.6.19-1~dotdeb.1 - (Debian)

I would like add more details about same, some weeks ago I was facing the some issues with default location of Mysql, So; I changed the location of Mysql by using following steps.

Shutdown Mysql
Move All Mysql file inside old folder to new folder
Change the datadir variable inside config file my.cnf
Restart the MySql Server

The problem is related with the internal representation of "timestamp" fields, probably caused by a bug in the database conversion procedure, occurred while upgrading from a previous version.

Exporting and importing the database "mysql" has fixed the problem in my debian server.

During the repair procedure, it's preferable to stop Apache and any other software that make use of the database, just in case.

Export the database:

mysqldump --events --quick --single-transaction mysql > mysql-dump.sql

Import back immediately after the export:

mysql mysql < mysql-dump.sql

Important: You must restart mysql service at this point to reload privileges.

service mysql restart

Of course I recommend to repair all your databases, because they could be affected.
You can do it one by one by hand, or all at once with a simple script:

mysqldump="mysqldump --events --quick --single-transaction"
exclude="information_schema|performance_schema"

cd /tmp

# Backup and re-import each database on the system
for db in $(mysql -e "show databases;" -s --skip-column-names | grep -vE "($exclude)")
do
        echo "Repairing database $db"
        $mysqldump $db > $db.sql
        mysql $db < $db.sql
done


Ownership of SQL Server Files, Register Entries & Executables I'm working on a database server hardening project for a client of mine.  I'm getting into ownership issues when it comes to the following:

  .mdf & .ldf
  executables
  HKEY--> tree from Microsoft SQL Server on down

So my question is.....who should own the above items?  I do know that sa/dbo would be the easy answer, but I'm trying to give the least amount of privileges to these items.

Please see Security SQL Server on MSDN for a great resource on how to properly secure SQL Server.
Security is a very large topic, and needs a lot of thought to be done well.  The page linked above contains a large amount of resources from Microsoft about this topic.
From the above page, this is just a sample of topics:

Physical Security
Best practices for physical security strictly limit access to the physical server and hardware components. For example, use locked rooms with restricted access for the database server hardware and networking devices. In addition, limit access to backup media by storing it at a secure offsite location.
Implementing physical network security starts with keeping unauthorized users off the network. The following table contains more information about networking security information.
Operating System Security
Operating system service packs and upgrades include important security enhancements. Apply all updates and upgrades to the operating system after you test them with the database applications.
Firewalls also provide effective ways to implement security. Logically, a firewall is a separator or restrictor of network traffic, which can be configured to enforce your organization's data security policy.
Principals and Database Object Security
Principals are the individuals, groups, and processes granted access to SQL Server. “Securables” are the server, database, and objects the database contains. Each has a set of permissions that can be configured to help reduce the SQL Server surface area.

Database files, including data and log files, as well as backup files, should be owned by the SQL Server Service Account, or in the very least that account should have full control over them.  See this MSDN page for more details about that, including for SQL Server executables, and the Registry.


PostgreSQL vs SQLite I was wondering because recently I installed PostgreSQL, and I have previously installed DB Browser for SQLite on my Windows 10 computer. If it PostgreSQL has a DB Browser GUI for the tables, because I was using it and I was using SQL Query language to create a table, and insert values into the table. I thought this question would be on topic for the site, if its not please let me know.

pgAdmin is the most recommended GUI tool for querying PostgreSQL: https://www.pgadmin.org/
Here's a list of other recommendations too: https://scalegrid.io/blog/which-is-the-best-postgresql-gui-2019-comparison/#:~:text=pgAdmin%20is%20the%20de%20facto,and%20developers%20for%20database%20administration.


sort a varchar field numerically in mysql I have the varchar field named _tid, normally which contains numerical field, but it is also possible that may be contain string field. Now i wanted to to sort the column by numerically rather than lexicographically.
Here the query i've used,

mysql> select _tid,_name from teacher order by _tid;

which returns the following row set.

 +------+----------------------+
    | _tid | _name                |
    +------+----------------------+
    | 1    | A.MANIVANNAN         |
    | 10   | M.ELUMALAI           |
    | 100  | SAMPATH.R            |
    | 101  | S.PAULRAJ            |
    | 102  | A.ASHOK KUMAR        |
    | 103  | S.JAYAKUMAR          |
    | 104  | S.CINRAS             |
    | 105  | P.MURUGAN            |
    | 106  | S.VIJAY              |
    | 107  | N.KARTHIKEYAN        |
    | 108  | G.BALAKRISHNAN       |
    | 109  | C.THARANI            |
    | 11   | M.PONNUSAMY          |
    | 110  | J.KANNAN             |
    | 111  | V.MAHENDRAN          |

But i want row set like the following,

+------+----------------------+
| _tid | _name                |
+------+----------------------+
| 1    | A.MANIVANNAN         |
| 2    | A.PONNIVALAVAN       |
| 3    | B.MUTHU              |
| 6    | R.Kumar              |
| 4    | P.RAJAKALI           |
| 5    | N.Shaunmuga Sundram  |
| 8    | M.Balaji             |
| 7    | V.PALANI             |
| 9    | J.RANJITH            |
| 10   | M.ELUMALAI           |
| 11   | M.PONNUSAMY          |

Two possibilities:
Either pad your values with zeros from the left and sort based on that:

SELECT _tid, _name FROM teacher ORDER BY lpad(_tid, 3, '0');
-- choose an appropriate number instead of 3

or cast your values to a number (similar to Aaron W's solution, apart from here I cast explicitly, and that is a clearer solution):

SELECT _tid,_name FROM teacher ORDER BY cast(_tid as decimal);

Be careful if you have non-number strings in your field:

SELECT cast('something' as decimal);
0
SELECT 'something' + 0;
0


The operating system returned error 21 (The device is not ready.) Every time I reboot Windows, for some databases I get this error:

  The operating system returned error 21(The device is not ready.)

I checked the disk with chkdsk /r - no bad sectors.
I executed DBCC CHECKDB with no errors:

*(CHECKDB found 0 allocation errors and 0 consistency errors in database)*

If I restart SQL Server the errors disappear.

Windows 10 and SQL Server 2016 Express.

Every time I reboot windows, for some databases comes out this error. (OS Error 21 - Device not ready)

This is due to a disk being offline or not being online either at the time SQL Server started, or had transitioned states after SQL Server has been online.

  3.If I restart SQL Server the errors disappears

Yes, because the databases have been remounted inside of SQL Server. You could also offline->online the database as well and it would work, assuming the disk device has been fixed.

This can easily be reproduced in a test environment by putting a database on a disk, disabling the disk, running a select query (to get the error), bringing the disk back online and noticing that the select still fails with the same error. The database will need to be remounted in order to work again and not get OS Error 21.

What should you do?

Have someone do some windows tracing to figure out why it isn't coming online initially or why it is going offline (any state transition) or why it is showing ready to windows but really isn't (maybe other drivers need to be loaded for it).

Additionally check any disk filter drivers are up to date for things like anti-virus, host intrusion protections, etc., as those may also be blocking the service/startup/state.


Is there any performance benefit to minifying MySQL queries? I recently ran across some tools that help you minify queries, and I'm curious to know if that's actually something that could help performance.

I'm going to post an answer just to put an end to this bizarre debate.

TLDR: No, "minification" will not provide any performance benefit that you would notice.

"Minification" refers to a process of removing any optional whitespace from a program source code and giving program identifiers (variables, functions, etc.) shortest possible names, all while retaining the original semantics of the program. The original purpose of it was to reduce the download time of JavaScript code embedded in (or linked from) HTML pages, with the aim of improving user experience on many web sites. As a side effect, minified JavaScript code is practically unreadable by humans, which brought about the secondary usage of the practice, that of obfuscating the source code, often for malicious purposes.

SQL statement syntax differs from other programming languages in that it rarely has any variables1; it contains mostly keywords and SQL object identifiers, which obviously cannot be replaced by arbitrary shorter tokens without stripping a statement entirely of its meaning. That leaves only two minification targets: optional whitespace and, possibly, correlation names when such are used, which makes minification "improvement", in terms of the overall statement length, significantly less impressive.

Further, unlike transferring sources of HTML pages over long range internet links from web servers to residential clients, the part of the overall SQL statement processing cycle that can be theoretically improved by reducing the length of the statement string is negligible, compared to other steps, such as optimizing the statement, executing it, and sending statement results back to the client, if applicable. Besides, SQL statements normally travel over backbone networks, which are much faster and experience much less contention than public internet.

Finally, if you decide to apply minification to dynamically composed SQL statements, you might even see overall performance degradation, because a poorly written minification code2 will probably have overhead that  outweighs whatever "benefits" it produces.

As a result, you3 are unlikely to perceive any difference in effective statement processing time (from submission to receiving first results) between minified and non-minified statements.

1 - PSM and compound SQL statement code can, of course, contain variable names, but in the cases when such code is used, potential network throughput improvements from minification are even less significant.
2 - I would find it doubtful that people engaging in such practice can produce high quality code.
3 - Yes, even if you are developing a High Frequency Trading application.


Mysql join table order by index First Master Table

CREATE TABLE `edipostingmaster` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `applied` int(11) DEFAULT '0',
  `fileName` varchar(255) DEFAULT NULL,
  `checkNo` varchar(100) DEFAULT NULL,
  `checkDate` date DEFAULT NULL,
  `checkCount` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `unqiue` (`checkNo`,`fileName`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8

First Detail Table

CREATE TABLE `edipostingclaims` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `applied` int(11) DEFAULT '0',
  `fileName` varchar(255) DEFAULT NULL,
  `checkNo` varchar(100) DEFAULT NULL,
  `claimSno` int(11) NOT NULL,
  `claimNo` varchar(100) DEFAULT NULL,
  `claimExists` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UNIQUE` (`fileName`,`checkNo`,`claimSno`)
) ENGINE=InnoDB AUTO_INCREMENT=385 DEFAULT CHARSET=utf8

Second detail table

CREATE TABLE `edipostingcodes` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `fileName` varchar(255) DEFAULT NULL,
  `checkNo` varchar(100) DEFAULT NULL,
  `claimSno` int(11) DEFAULT NULL,
  `claimNo` varchar(100) DEFAULT NULL,
  `lineItemNo` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `unique` (`checkNo`,`claimSno`,`fileName`,`lineItemNo`)
) ENGINE=InnoDB AUTO_INCREMENT=1289 DEFAULT CHARSET=utf8

Here is the query

EXPLAIN
SELECT * FROM edipostingmaster a  , edipostingclaims b, edipostingclaims c
WHERE a.fileName = b.fileName AND a.checkNo = b.checkNo
AND b.fileName = c.fileName AND b.checkNo = c.checkNo AND b.claimSno = c.claimSno
AND a.checkNo ='893881996'
ORDER BY b.claimSno

Now explain showing Using where; Using temporary; Using filesort for table a.
Without order by, it is fine.

In this case, while an index could be used (not in the current state, but if the index changed its column order a bit) to speed up the ordering, the optimizer has to decide between favoring the filtering or the ordering.

As it probably starts by filtering by the constant a.checkNo ='893881996', and accessing first the table a, it would be impossible to get the records in b.claimSno order (even with different indexes).

As such, a sorting process is required at the end of the row retrieval, and that requires a temporary table. Do not be too worried unless the difference in time between each execution is very high.

If the table ends up being created on disk, you can try tuning tmp_table_size and max_heap_table_size for that session to try to avoid that - although with the couple of varchar(255) that you have, it probably will be worse, not better. If the number of sort_merge_passes is very high, you can try tuning the sort_buffer_size for that session.

On the other hand, if the query is running fast enough for you, you do not need to worry as it is something normal.


SQL Server impersonation This question is about impersonation in Microsoft SQL Server. I want to know that if I have impersonation permissions on a specific login then I can easily impersonate that login whenever I want and I do not need to have a stored procedure with EXECUTE AS OWNER? If that is so then what is the purpose of a stored procedure with EXECUTE AS?

Excellent question. Yes, if LoginA (or UserA) is granted IMPERSONATE on LoginB (or UserB), then LoginA (or UserA) can execute the EXECUTE AS statement whenever they want, without restriction:

EXECUTE AS LOGIN = 'LoginB';

or

EXECUTE AS USER = 'UserB';

  then what is the purpose of a stored procedure with EXECUTE AS?

The purpose of the EXECUTE AS clause (part of the CREATE {module} statement) is to temporarily grant that IMPERSONATE permission only in the context of that module. This prevents you from needing to explicitly grant IMPERSONATE. The difference is, outside of the context of that module with EXECUTE AS {SomeUser}, whoever is executing that module shouldn't have the ability to impersonate that User. You don't want Logins / Users being able to impersonate others whenever they want. That is a security risk. The EXECUTE AS clause gives you the ability to let a principal impersonate another only when you want, not when they try to on their own (where you can't restrict what they do with those elevated permissions). So, you actually do want the EXECUTE AS clause, and do not want to grant IMPERSONATE to anyone ever (if it can be avoided, and most likely it can be).

For example: There is no TRUNCATE TABLE permission. If you want someone to be able to truncate a particular table (that they do not own, since they do have permission to do that) you could grant them IMPERSONATE on dbo. However, there is then no way to restrict their actions to only issuing TRUNCATE TABLE, and only on that particular table. That User can execute EXECUTE AS USER = 'dbo'; whenever they want, and then do whatever they want. However, by adding EXECUTE AS 'dbo' to the CREATE PROCEDURE .... AS TRUNCATE TABLE {TableName}; statement, whoever executes that module will still impersonate dbo, but only for the actions in that module, which here is just truncating a particular table.

Of course, the EXECUTE AS clause of a CREATE statement can only impersonate another User (database-level only). It cannot impersonate another Login (instance-level). This limitation is what leads too many people to enable TRUSTWORTHY so that a Login matching the same SID of the User being impersonated will be able to temporarily grant additional permissions. But this is quite dangerous, which is why the ideal (and far better) mechanism for temporarily elevating permissions is Module Signing.

For more details on why impersonation, as well as TRUSTWORTHY ON and Cross-DB Ownership Chaining, are bad, and how / why Module Signing is better, please see:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining


How to use IN to compare a single value with a comma separated field in another column I have a table that looks like this. let's call it table1:

|attributeMask |
|--------------|
| ,300,302,400 |
| ,301,500     |
| ,900,876     |

These are varchar values.

Then I have another table2:

 columnNumber  |
|------|
| 300  |
| 500  |
| 900  |

These ones are int.

What I need is a query to compare columnNumber with values inside attributeMask.

For example, I need to find inside attributeMask, rows that has 300 (for each row. the second columnNumber is 500, and I will compare this 500 with the respective row from it's own attributeMask that can be ,299,34,500). I'm trying to use FOR XML PATH or other functions but I have no idea what to do.

This is the entire query that i could create until now:

select  top 3
    t1.[Name]
   ,t1.ColumnNumber
   ,t3.AttributeMask
   ,t3.changeData
   ,t3.CreatedOn
   ,t3.ObjectTypeCode
   ,t3.UserId
from metadataschema.attribute     t1
inner join  metadataschema.Entity t2
    on t1.EntityId=t2.EntityId
inner join AuditBase              t3
    on t2.ObjectTypeCode=t3.ObjectTypeCode
where t2.ObjectTypeCode=1
//this is wrong. but is this part that I need to fix
and convert(varchar,t1.ColumnNumber) in (select t3.AttributeMask from
AuditBase)
//until here
and t3.CreatedOn BETWEEN '20190902' AND '20190913'
and t1.[Name] in
(
'address1_postalcode','smart_caixapostal','smart_tipologradouro','smart_complementoprincipal','smart_estado','smart_logradouroprincipal','smart_bairroprincipal',
'new_regiao','smart_numero','address1_city','smart_pais','smart_validarendereco','smart_cepcobranca','smart_caixacobranca','smart_validarendereco_cob',
'smart_tipologradourocobranca','smart_logradourocobranca','smart_numerocobranca','smart_complementocobranca','smart_bairrocobranca','smart_municipiocobranca',
'smart_estadocobranca','territoryid','smart_paiscobranca','smart_cepmkt','smart_logradouromkt','smart_complementomkt','smart_ufmkt','smart_tipodelogradouromkt',
'smart_bairromkt','smart_ddidddmkt','smart_caixapostalmkt','smart_numeromkt','smart_municipiomkt','smart_telefonemkt'
)

There is a way to do this. However, I highly recommend that you reconsider using comma-separated values within a column. If you ABSOLUTELY must do it this way, you can use the LIKE operator with wildcards. This will enable you to search the attribute mask string for values. Expect terrible performance when looking up many values.

inner join AuditBase              t3
    on t2.ObjectTypeCode=t3.ObjectTypeCode
    and t3.AttributeMask LIKE '%' + convert(varchar,t1.ColumnNumber) + '%'
AuditBase)


Teradata view with "With Clause" syntax I am trying to a view with "With" clause in Teradata but it is not allowing to create a view. Please suggest with any options.

Given below is the query which I have tried:

create view derived_table(derived_column)
AS
(
  With temp_table(a,b,c)
  As
  (select a,b,c from table_a where column1=column2)
select (a||'-'||b) as derived_column  from  temp_table
union all
select (a||'-'||b||'-'||C) as derived_column from  temp_table
)

It's a pain, but Teradata doesn't support CTE in views (as of 15.00), see SQL Data Manipulation Language > The SELECT Statement > WITH and WITH RECURSIVE Statement Modifiers.

In your case you can create another view with the contents of the CTE, but you probably know that already.


Improve speed of COPY COMMAND in postgres I would like to understand if increasing work_mem does help improving the speed of the COPY command.

Does COPY use work_mem or maint_work_mem extensively?

No, COPY does not need a lot of memory.

There are two things to speed up COPY:

Remove any indexes and constraints on the table before you COPY into it.
Increase max_wal_size so that you don't get more checkpoints than necessary.

Of course, if you COPY into an UNLOGGED table, it will be even faster.


Postgresql Function Trying to Insert into 1 row instead of 2 Postgresql 9.3, Mac OS X 10.6.7

I created this function and I would like to insert both results into the same row. Obviously the way I have it inserts into 2 rows.

First, here is the Table T1:

 test_app_development=# select * from T1;
   name  | age | id
  -------+-----+----
   jim   |  50 |  1
  bill   |  55 |  2
   Ed    |  35 |  3
   joe   |  30 |  4
  dave   |  45 |  5
  steve  |  38 |  6
   (6 rows)

Now the Function:

  create or replace function stats_test() returns numeric as $$
    begin
     insert into stats(stdv) select stddev(age) from T1;
     insert into stats(ave_age) select avg(age) from T1;
     return 1;
    end;
 $$ language plpgsql;

And here are the results to the function:

 select stats_test();

 test_app_development=# select * from stats;
    ave_age | id | stdv
   ---------+----+-------
            |  1 | 9.496
    42.167  |  2 |
   (2 rows)

I have tried variations but with no luck:

  insert into stats(ave_age,stdv) select avg(age), stddev(age) from T1;

I have tried it with this function, it works fine for me.

create or replace function stats_test() returns numeric as $$
  begin
    insert into stats(ave_age, stdv) select avg(age), stddev(age) from "T1";
    return 1;
  end;
$$ language plpgsql


When do you add your foreign keys? I'm scripting out a fairly simple database so I can simply hand off a script to someone else for an application to use.  I'm curious about where best practice/the community stand on this: When should you create your primary/foreign key constraints?  Should you do it while you are creating the tables because of the continuity, or should you do it after the tables are created since it will be easier to script that way since you can create the tables in any order you wish?  Are there any potential fatal flaws down the road with either pattern?

I typically create the primary key constraint directly with the table:

CREATE TABLE dbo.MyTable
(MyTableID INT IDENTITY(1,1) NOT NULL
     CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED,
 .......
)

I don't see any point in deferring that PK constraint to later (same also applies to DEFAULT and CHECK constraints which I typically also define "inline" with the table creation script).

However, the FK constraint, I typically define those as separate ALTER TABLE .. ADD CONSTRAINT ... statements, that can be run right after the table creation, or after all tables have been created - doesn't really matter:

ALTER TABLE dbo.MyTable WITH CHECK
ADD CONSTRAINT FK_MyTable_OtherTable
FOREIGN KEY (OtherTableID) REFERENCES dbo.OtherTable(OtherTableID)

ALTER TABLE dbo.MyTable CHECK CONSTRAINT FK_MyTable_OtherTable


ERD Table and Field Questions !ERD

My question is two-fold.

First, this is my first design and I am using the Mere Mortals book to help me.  He states, "...each table represents a single, specific subject."  So should I combine the Statistics table and Designer tables into the Project table since the subject is Project?

Secondly, the two fields of WhyClientChoseUs and ClientLossReason are opposed to one another and only one of these two fields would be filled out. Is that OK?  And should this go in the Project table and not the Client table?

Thanks in advance.

According to the annotations in your ERD diagram, I understand that:

A Client may have many Projects
A Project may have many Designers

If that is true, then these 2 tables should not be consolidated.

Likewise, depending on your definition of WhyClientChoseUs and ClientLossReason, there are reasons to keep both.  (E.g. you may have a reason WhyClientChoseUs and later need to add the ClientLossReason.  Not uncommon on big projects.)

But, because the client may continue with one project but abandon another, the Project may be a better place to keep that information.

But answers to these kinds of question do depend on your definitions.