Excuse the mess I am about to create, but here it goes ...

STEP 01

Create a file called /tmp/ProcessDate.sql which contains this

create temporary table tmp1(
...
where thedate=@thedate
);

create temporary table tmp2(
...
where thedate=@thedate
);

delete from tbl1;

replace into tbl1(
select *
from tmp1
inner join tmp2
);

STEP 02

Create a Shell Script call /tmp/ProcessDays.sh which contains this

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"

TS_NOW=`date +"%s"`
TS_NDX=1
TS_DAY=0

while [ ${TS_DAY} -lt ${TS_NOW} ]
do
    SQL="SELECT DATE(FROM_UNIXTIME((UNIX_TIMESTAMP(MAKEDATE(YEAR(NOW()),${TS_NDX})))))"
    DT=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
    echo "SET @thedate = '${DT}';" > /tmp/ProcessOneDay.sql
    cat /tmp/ProcessDate.sql >> /tmp/ProcessOneDay.sql
    OUTFILE=Data_${DT}.tsv
    mysql ${MYSQL_CONN} -AN < /tmp/ProcessOneDay.sql
    mysql ${MYSQL_CONN} -AN"SELECT * FROM tbl" > ${OUTFILE}
    (( TS_NDX++ ))
    SQL="SELECT UNIX_TIMESTAMP(MAKEDATE(YEAR(NOW(),1) + INTERVAL ${TS_NDX} DAY)"
    TS_DAY=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
done

STEP 03 : GIVE IT A TRY !!!


Difference between Generate Script option at database level and backup and restore option

With backup you have all data. Of course you can generate script for data, but for a DB with 1-2 GB the script will be very big and will execute long time.

The best way in my point of view is detach/attach method. It will preserve the current state of the DB. Of course you can make backup before that. In that case you also have to take into account downtime for transferring database between servers.


How to remove a table from a FOR ALL TABLES logical replication PUBLICATION in PostgreSQL

For 1, no, I don't think for.  For 2, possibly but it seems fraught.

I naively thought that creating a new publication listing your tables, and then on the subscribing end disabling the old subscription and creating new one using the same slot, all in one transaction, would work.  But doing an experiment, this does not work if the publisher is under load.  It ends up losing some changes. However, it does appear to work if the publisher is quiescent and the subscriber is caught up at the time of the swap.

-- warning: does not work transactionally!
begin;
alter subscription sub disable;
alter subscription sub set (SLOT_NAME = NONE); -- used to be 'sub'
create subscription sub2 CONNECTION '' PUBLICATION pub2 with (
    copy_data = false,
    slot_name='sub',
    create_slot=false) ;
commit;

I can't decide if this is a bug or not.  I don't see anything in the documentation which excuses this non-transactional behavior, but I did not read the entire thing carefully.


If the files are sorted you can just find the id or timestamp of the last inserted document in the database, then only insert the records in the files which have ids that come after that id.

If they are sorted by _id or timestamp then doing this should be trivial.

The solutions you came up with are great as well.

However, be aware that creating entirely new collections will be slower than what i proposed, and upserting will be even slower than that.


This sounds a lot like something I created back in February 2011. In fact, I placed the code for the mysqlservice script in one post : Running multiple instances on the same host (Sep 30, 2011)

The mysqlservice script is a quasi-service I created to help set up multiple mysql instances. The idea is to create

my.cnf for a specific port number
service filename to launch the service

In this case, you should see a file called /etc/init.d/mysql3307. If you do not have one, please create it. It should look like this :

#!/bin/sh
#
# readahead:    Prereads programs required for startup into memory
#
# chkconfig: 2345 4 99
# description:  This service causes the programs used during startup \
#               to be loaded into memory before they are needed,\
#               thus improving startup performance
#
#

# Sanity checks.
[ -x /usr/sbin/readahead ] || exit 0

# Check for > 384 MB
#free -m | gawk '/Mem:/ {exit ($2 >= 384)?0:1}' || exit 0

# Source function library.
#. /etc/rc.d/init.d/functions

WHICH=/usr/bin/which
SERVICE=`${WHICH} service`

MYSQL=`${WHICH} mysql`
PORT_NUMBER=3307

# See how we were called.
case "$1" in
    start)      ${SERVICE} mysqlservice ${PORT_NUMBER} start    ;;
    stop)       ${SERVICE} mysqlservice ${PORT_NUMBER} stop     ;;
    status)     ${SERVICE} mysqlservice ${PORT_NUMBER} status   ;;
    restart)    ${SERVICE} mysqlservice ${PORT_NUMBER} restart  ;;
    mycnf)      ${SERVICE} mysqlservice ${PORT_NUMBER} mycnf    ;;
    edit)       ${SERVICE} mysqlservice ${PORT_NUMBER} edit     ;;
    *)
        echo $"Usage: $0 {start|stop|restart|status|mycnf}"
        ;;
esac

After creating the file, please run

chmod +x /etc/init.d/mysql3307

Now, the service can be summoned with

service mysql3307 start

Under the hood, it will call

service mysqlservice 3307 start

You could call it that way as well. Yet, setting up /etc/init.d/mysql3307 makes the service have a name that's easy to remember.

Give it a Try !!!

CAVEAT : You have to create a my.cnf file for port 3307 called /etc/my3307.cnf

The file /etc/my3307.cnf must include

[mysqld]
port=3307
datadir=<whatever folder you have for 3307's mysql instance>

The fundamentals difference between services are

datadir
port number

You can start and stop the services independent of each other. While mysqld_multi allows you to have multiple MySQL Instances under one my.cnf via setting up mysqld groups, I created the mysqlservice quasi-service to use distinct my.cnf files for each instance. That way, you do not have to worry setting up my.cnf to know  what is common to all MySQL Instances vs. what is unique to each MySQL Instance.

If you setup more than two services in this manner, you become responsible for assigning the proper amount of RAM for buffers, caches, and threads.


Designing an extensible authentication system

First, get the idea of inheritance out of your head and try to think relationally. Keep integrity as a first priority, and ease of use as a second. Saint So-and-so isn't that hot. And, I doubt anyone has heard of the buzz term Concrete table inheritance. PostgreSQL has native inheritance built it and the side effects of it are simply not worth the gain.. Even though PostgreSQL does have a concept of Multiple Inheritance.

You provide three tables...

-- Those tables are middleware artifacts
CREATE TABLE one_time_codes(
    id serial PRIMARY KEY, -- surrogate key
    code integer NOT NULL,
    inserted_at timestamp without time zone DEFAULT now() NOT NULL,
    user_id integer REFERENCES users(id) NOT NULL
);

CREATE TABLE insecure_authentication_handles(
    id serial PRIMARY KEY, -- surrogate key
    passkey character varying (255) NOT NULL,
    inserted_at timestamp without time zone DEFAULT now() NOT NULL,
    user_id integer REFERENCES users(id) NOT NULL
);

-- Final product of successful authentication
CREATE TABLE passphrases(
    id serial PRIMARY KEY, -- surrogate key
    passkey character (5) UNIQUE NOT NULL,
    inserted_at timestamp without time zone DEFAULT now() NOT NULL,
    user_id integer REFERENCES users(id) NOT NULL
);

All of this raises the question of how data relates to other data. In this case, the users table, that we agree must exist.

Do you have 1-to-1 relationship?
Do you have 1-to-many relationship?
Do you have many-to-many relationship?

That should be the principal factor in determining your data schema and not some external theory applied by an organic buzzword generator. That's how databases work.

Aside from how you related to your data, consider the other downsides.

Ease of use. You've got a UNION now and a VIEW other complexities you wouldn't otherwise need.
The alternative is faster. JOINs ain't free they cost index scans and merges -- even the ones on ints. PostgreSQL has a large-row size. The fewer rows you have in memory and have to fetch the better. Same for UNION, without UNION ALL you have to sort and filter distinct. With regular UNION you may not know how many duplicates you're getting back.
You're not using the tools available to you: PostgreSQL provides hstore, (sql-standard) array, jsonb types. You can use these for schema-less design on your user. I suggest it if you're not needing to select on that criteria.

So you could just as easily,

ALTER TABLE users ADD COLUMN (auth jsonb);

And, then put one_time_codes, passkeys, and passphrases in that.

As a side note,

you should consider reviewing pgcrypto for authentication needs.
Don't specify character varying unless you truly need a character limit by specification. Otherwise, use text. It's faster. And, that's the PostgreSQL convention. They're both implemented in the same way, but varchar has a length-check on it.

Testing

200 passcodes, for 2000 users using JSON and PostgreSQL array.

JSON Schema

CREATE TABLE users AS
SELECT t1.userid, md5(t1.userid::text), x AS passcodes
FROM generate_series(1,2000) AS t1(userid)
CROSS JOIN LATERAL (
  SELECT t1.userid, jsonb_agg(floor(random()*1000)::int)
  FROM generate_series(1,200) AS t(x)
  GROUP BY true
) AS t2(userid,x);
CREATE UNIQUE INDEX ON users (userid);
ANALYZE users;

JSON Query

This is 35ms. You should never have to do this.

EXPLAIN ANALYZE SELECT * FROM users
WHERE passcodes @>'5' ;

This is 0.060 ms

EXPLAIN ANALYZE SELECT * FROM users
WHERE passcodes @>'5'
AND userid = 42;

That's likely faster than the index lookup and merge for the other table.

SQL Array Testing

CREATE TABLE users AS
SELECT t1.userid, md5(t1.userid::text), x AS passcodes
FROM generate_series(1,2000) AS t1(userid)
CROSS JOIN LATERAL (
  SELECT t1.userid, array_agg(floor(random()*1000)::int)
  FROM generate_series(1,200) AS t(x)
  GROUP BY true
) AS t2(userid,x);
CREATE UNIQUE INDEX ON users (userid);
ANALYZE users;

SQL Array Query

This is 7ms. You should never have to do this.

EXPLAIN ANALYZE SELECT * FROM users
WHERE passcodes @> ARRAY[5];

This is 0.076 ms

EXPLAIN ANALYZE SELECT * FROM users
WHERE passcodes @> ARRAY[5]
AND userid = 42;

That's likely faster than the index lookup and merge for the other table.


Can FOR XML PATH return NULL for an empty resultset?

Check out the value(method). From MSDN:

  You typically use this method to extract a value from an XML instance stored in an xml type column, parameter, or variable. In this way, you can specify SELECT queries that combine or compare XML data with data in non-XML columns.

Code:

DECLARE @variable_xml XML
DECLARE @variable_varchar VARCHAR(20)
SET @variable_xml = 'select NULL for xml path(''foo'')'
SET @variable_varchar = @variable_xml.value('(/Root)[1]',
                           'varchar')
SELECT  @variable_varchar

Output:
NULL

EDIT:

I may have misunderstood the question. Please comment and I will update or delete my answer if its incorrect.


PostgreSQL; Update different column on condition using CASE statement

It is to be something like (logic only)

update table_A
set column_A = case when     (column_A>table_B.balance) then value else column_A end,
    column_B = case when not (column_A>table_B.balance)
                     and     (column_B>table_B.balance) then value else column_B end,
    column_C = case when not (column_A>table_B.balance)
                     and not (column_B>table_B.balance)
                     and     (column_C>table_B.balance) then value else column_C end
from table_B
on table_A.Id=table_B.id


Arithmetic overflow for Variable in Openrowset

Sorry, don't know how to highlight a comment.

Scott Hodgin postet the solution:

[Size in MB] = CAST( ((SUM(cast(Size as bigint))* 8) / 1024.0)as decimal),

 is the correct line, and solution to my problem:)
Thank you very much:) !


Schema pg_dump failed due to a Lock on a table

From pg_dump manual:

Requesting exclusive locks on database objects while running a parallel dump could cause the dump to fail. The reason is that the pg_dump master process requests shared locks on the objects that the worker processes are going to dump later in order to make sure that nobody deletes them and makes them go away while the dump is running. If another client then requests an exclusive lock on a table, that lock will not be granted but will be queued waiting for the shared lock of the master process to be released. Consequently any other access to the table will not be granted either and will queue after the exclusive lock request. This includes the worker process trying to dump the table. Without any precautions this would be a classic deadlock situation. To detect this conflict, the pg_dump worker process requests another shared lock using the NOWAIT option. If the worker process is not granted this shared lock, somebody else must have requested an exclusive lock in the meantime and there is no way to continue with the dump, so pg_dump has no choice but to abort the dump.

So pg_dump can run when other users use database, but not when tables are being dropped.
Your error message clearly states, that the object was not found and that's what caused locking to fail. It's also hinted in that message that it probably got deleted.
To avoid in the future, verify what could cause the object to be deleted during backup (maintenance? maybe some periodically run scripts/functions delete tables?) and schedule your backup after that.


There's nothing inherently wrong with what you are doing, assuming the strings you're splitting up into pieces to form the IN sets are coming from a safe source, but I would have these suggestions for optimization:

There's not an obvious reason to make the call to a separate procedure, as this could all be accomplished in a single procedure, and everything requires some finite amount of time to be done, so writing it as a single proc would save some small amount of time, which helps when you're doing a large amount of the same thing... though I don't think that's the biggest issue, by far.

I suspect the big time killer here is the repeated creation of the temporary table.  On my unimpressive dual 1GHz Opteron test machine, I see dramatically different creation times for temporary tables:

Query OK, 0 rows affected (0.24 sec) # ENGINE=InnoDB (default)
Query OK, 0 rows affected (0.02 sec) # ENGINE=MyISAM
Query OK, 0 rows affected (0.00 sec) # ENGINE=MEMORY

Since you are not using the temporary table to directly update another table, you should be free to use the most lightweight engine that fits the application, even if that means you're mixing transactional and non-transactional storage engines, because you don't need subsequent queries based on the temporary table to actually replicate correctly to downstream slaves and there's nothing here to roll back.  You're creating, inserting, selecting, dropping, and this should not have any impact on replication (otherwise, it might).

You should also evaluate the EXPLAIN output from the queries in the nested proc to be sure they are optimal.  Using derived tables, e.g., SELECT ... FROM (SELECT...) do not really appear to be necessary and may not be the most optimal way to write those queries.

For example:

insert into areas (id, code, name, shortName, levelid, sortOrder)
select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
    from (select id from GeoArea where Code = _parentArea) as t
    INNER JOIN
    GeoAreaLinkCache c
    ON
    c.ParentAreaID = t.id
    inner join GeoArea a
    on c.ChildAreaID = a.ID
    INNER JOIN
    (select id from GeoAreaLevel where Identifier = _areaType) as l
    ON
    a.LevelID = l.id

...would likely be much better written this way (assuming I've parsed it correctly):

INSERT INTO areas (id, code, name, shortName, levelid, sortOrder)
SELECT a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
  FROM GeoArea t
 INNER JOIN GeoAreaLinkCache c ON c.ParentAreaID = t.id
 INNER JOIN GeoArea a on c.ChildAreaID = a.ID
 INNER JOIN GeoAreaLevel l ON l.id = a.LevelID
 WHERE l.Identifier = _areaType
   AND t.Code = _parentArea;

Avoiding the derived tables potentially gives the optimizer a lot more options when formulating the query plan because derived tables generally limit the usability of indexes.  They may be logically equivalent but aren't equivalent in execution.


Server requested authentication method unknown to the client [mysql_old_password]

I had the same error, trying to debug locally some application that connects to remote DBs.
When using php < 5.3, everything worked.
When using php 5.3 or greater, error shows up.

After hours spent reading and tweaking, i realized this only occurred when connecting as a certain user.
All databases i use have new password length (41).

I changed the password for that user, and everything worked fine, even after changing it again to the previous value.

Hope this helps someone.


Won't start: InnoDB: Corrupted page ... Tablespace was not found

https://dev.mysql.com/doc/refman/5.7/en/repair-table.html says:

  REPAIR TABLE works for MyISAM, ARCHIVE, and CSV tables.

In other words, REPAIR TABLE does nothing for InnoDB. InnoDB has its own automatic crash recovery that it runs on startup.

But InnoDB crash recovery only works for lost changes that were in progress at the time of the crash. It uses the redo log and the doublewrite buffer to reconstruct lost dirty pages. This process cannot reconstruct data that was corrupted at rest.

The error message actually tells you what to do:

  InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.

Read more details at https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html:

  1 (SRV_FORCE_IGNORE_CORRUPT) Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

Start the server with innodb_force_recovery=1 after moving your tablespace files back where they belong.

Then you can dump the table using mysqldump, and it should read the pages it can read, skipping corrupted pages. Then you can make a new table to import from the dump:

CREATE TABLE mytable_new LIKE mytable;
RENAME TABLE mytable TO mytable_bad, mytable_new TO mytable;

Then re-import your dumped data.

I assume you do not need help with dumping a single table and importing the dump file.

Re your comment:

It seems like you have more extensive corruption. I suggest the following steps:

Start the MySQL server with innodb_force_recovery=6
Use mysqldump to dump all of the data.
Shut down MySQL server.
Back up the contents of your datadir just in case you want to get professional help to see if they can recover more of the data. Try https://twindb.com, they are the best experts in MySQL database recovery.
Initialize a new datadir (I would use a new disk device, and take the old one out of service because it now has unknown damage).
Restore the data dump you created with mysqldump.


Copying MySQL Raw Data Files for Master-Slave Replication and GTIDs

OBSERVATION #1

The slave needs log-slave-updates in my.ini

OBSERVATION #2

Both Master and Slave need binlog_gtid_simple_recovery in my.ini

MySQL Restart is required on both Master and Slave to include new options

SUGGESTION

Since DB is 5GB, just mysqldump the database on the Master. No need for raw copying

STEP 01 : On the Master, run this

RESET MASTER;
GRANT REPLICATION CLIENT,REPLICATION_SLAVE ON *.*
TO replicator@'%' IDENTIFIED BY 'repl1cat0r';

STEP 02 : On the Slave, run this

STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='IP Address of Master',
    MASTER_PORT=3306,
    MASTER_USER='replicator',
    MASTER_PASSWORD='repl1cat0r',
    MASTER_HEARTBEAT_PERIOD=1,
    MASTER_AUTO_POSITION=1
;

STEP 03 : Dump the data on the Master

set mysql_user=root
set mysql_conn=-u%mysql_user% -p
set dump_options=--routines --triggers --events -A --apply-slave-statements
mysqldump -uroot -p %dump_options% > C:\mysqldata.sql

STEP 04 : Copy the dump to the Slave (leave dump in C:)

STEP 05 : Load dump into the Slave

mysql %mysql_conn% < C:\mysqldata.sql

STEP 06: Verify replication

Login to mysql on the Slave and run `SHOW SLAVE STATUS\G`

Give it  a Try !!!


MongoDB does not allow to create unique partial index in field that contains null

Please use the below query to create the unique partial index. The reason why your query work is, there may many fields with the value like {rss_id: null}

db.getCollection('noticias').createIndex(
   { rss_id: 1},
   { unique:true, partialFilterExpression: {rss_id: {$ne:null}}}
)

Or you can use sparse index option along with unique index as shown below

db.getCollection('noticias').createIndex( { rss_id: 1}, { sparse: true, unique: true })


What's the meaning of "resources external to the SQL Server computer are needed" in Microsoft's SQL documentation?

This documentation page gives an explanation for those curious enough to scroll down:

A Managed Service Account (MSA) is a type of domain account created and managed by the domain controller. It is assigned to a single member computer for use running a service.

Virtual accounts [...] are managed local accounts. [...] Virtual accounts cannot be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster.

Emphasis above is mine.
In other words, virtual accounts are local to one server and cannot control resources on other servers, while managed service account are domain-scoped and can, therefore, be given access to resources on multiple servers.
For a standalone SQL Server instance there is no difference in functionality. In a cluster, however, the account should have authority to access and/or modify various resources on more than one server, so a domain account would be appropriate.


This limitation relies on the core physical page structure in SQL Server and, no, this hasn't changed in SQL Server 2016 (and won't in SQL Server 2017, either).

There are ways to get around this, though. Top of mind is splitting some larger data out into a separate table. Or just living with the warning message if you know your data won't ever fill all columns on the same row (or you can prevent it).

Or going back to the 3rd party and seeing why they have this and how they prevent actual row problems.


MySQL complains key exists but I can't find it

You likely have an index for a foreign key on some other table in your DB, which has the same constraint name and is thus causing a namespace collision.

If you are using all InnoDB tables (as you should in 2017), try this to locate the offending table:

SELECT t.name
  FROM information_schema.innodb_sys_indexes i
  JOIN information_schema.innodb_sys_tables t
 USING (table_id)
  WHERE i.name = 'BAZ';

Or this may work, too:

SELECT *
FROM information_schema.innodb_sys_foreign
WHERE id REGEXP 'BAZ$';


Are Database Master Key protected with same password, the same?

As it is said here it is a good practice to backup the database master key, because when the database is moved to another instance it will need the original DMK that has been used to protect the certificates in order to skip regeneration of the encrypted stuff.

That answer isn't factually correct. When the database is migrated, the DMK was encrypted with the old SMK and the password. You can open the DMK with the password on the new instance and add decryption by the new SMK. This doesn't require that you decrypt and encrypt the securables protected by the DMK again, since the actual DMK key didn't change - just what is protecting it.

  In the context of database backup encryption, when the DMK is stored in the master database with the rest of the certificates used for backup encryption, do we need to create a backup of the DMK?

Depends but most likely you won't directly need it unless there is something really, really bad that happens. Then you'll need to restore the DMK over the other one but that's not ideal, fun, or easy, either. If you have a backup of the master database then it already holds the DMK, additionally you will need a safe place to store the password. Since master is a special database, the use cases of this are much smaller than a user database.

  Does this mean, that if I used same password to create and protect each DMK in my master databases across all instances, the key will be the same?

No, there is an initialization vector that is used so the keys won't be the same. It's not based off of the password used.

  If not, it is OK to create a DMK on one database, and to restore it on all instances in order to have the same key everywhere and to need make a backup of only one key?

Definitely not ok. Is it ok to have just one password for everything from your email, online banking, and fantasy football account? Probably not. Probably not good to have a single key that opens your car, front door, mailbox, gate, bank deposit box, etc.


Good practices for patching SQL nodes in High Availability solution

Approaching this by thinking about each SQL instance separately.
Patching SQLDR\Inst_B
This is a secondary replica, so it can be patched/rebooted independently.

Switch SQLDR\Inst_B to be an ASYNC replica (if it's not already)
Patch SQLDB\Inst_B and reboot (windows patches, SQL patches, etc.)
Verify SQLDR comes back up and the AG is healthy.
Switch back to SYNC if required.

Notes:  While Inst_B is offline, the T-Logs on Inst_A will be kept around to replay when Inst_B returns.  I wouldn't recommend patching Inst_B while doing index maintenance or large data loads on Inst_A.  If Inst_B is down for a long period of time (think days) then you'll probably want to break the AG replica.
Patching SQLFCIPrimary\Inst_A

Patch passive node and reboot.
Failover to Passive node.  This will cause your (hopefully brief, e.g. < 1 min) outage.
Confirm AG is healthy
Patch new passive node and reboot.
Failback if required.

Notes:  Since you are using MANUAL failover, there is no risk that you'll inadvertently failover to Inst_B.
This is the simplest way to accomplish regular patching, and just needs one or two FCI failovers.  There is no reason to stop WSFC roles.
Whether you do Inst_A or Inst_B first doesn't matter, but I'm risk adverse, so I'd start with Inst_B.


Adding 4th table to join jumps processing time from seconds to over 20 minutes

SQL is not making up rows.
If that join is producing more rows than you expect then figure it out.
Are you sure ts.JobSubTypeId is a task code?
That name does not sound like a task code.

Try this - it will show you were the volume is coming from
In your query each count is a row

  -- this is the raw count from Timesheets
  SELECT ts.JobSubTypeId, count(*) as countRaw
  FROM JobsDB.dbo.Timesheets AS ts
  WHERE ts.WeekEnding >= '2014-01-01'
  GROUP BY ts.JobSubTypeId
  ORDER BY count(*) desc;

  -- this is the count brought in by the join to TaskCode
  SELECT ts.JobSubTypeId, count(*) as countJoin
  FROM JobsDB.dbo.Timesheets AS ts
  LEFT JOIN dbo.TaskCode AS tc
         ON tc.Code = ts.JobSubTypeId
  WHERE ts.WeekEnding >= '2014-01-01'
  GROUP BY ts.JobSubTypeId
  ORDER BY count(*) desc;

  -- this is the raw counts in TaskCode
  SELECT tc.Code, count(*) as countCode
  FROM dbo.TaskCode AS tc
  GROUP BY tc.Code
  ORDER BY count(*) desc;

The fact that when you finally ran the second query and it ran fast is a very strong indication you have a volume and not an index issue.  That count(*) had to touch those rows.

Even if JobSubTypeId is Code that is clearly not a sufficient join

ON tc.Code = ts.JobSubTypeId

TaskCode has a number of very detailed columns
Your are clearly bring in massive data from other projects


What is the meaning of "available RAM" for calculating MySQL max_connections

I'd say 'Available RAM' means max memory mysql can claim. And 'Global buffers' would mean buffers like innodb_buffer_pool, key_buffer_size, query_cache_size, etc.


Availability Group how to determine last Failover time

You can use following PowerShell script to get last failover time

    Get-winEvent -ComputerName ListnerName -filterHashTable @{logname ='Microsoft-Windows-FailoverClustering/Operational'; id=1641}| ft -AutoSize -Wrap


Created RDS db-instance Replica successfully, now how to connect it with server

What you are talking about is called Read/Write Splitting.   This is not a capability directly provided either by MySQL Server itself, or by RDS for MYSQL. It is an advanced technique for which there are no magic solutions, as I have mentioned elsewhere.

If you are not modifying your application (and assuming your framework does not have such functionality built-in, which is a reasonably safe assumption), you will need to deploy one or more read/write splitting proxy servers to divert each query to the correct target as appropriate.

Examples of software with this capability are MariaDB MaxScale and ProxySQL.


How to left join and count two tables where the rows (strings) are different

@ypercube said in question comments:

You don't need to split the strings. If there is always a single space between word, you can replace space with - and then join: ON 'path/' || replace(t1.title, ' ', '-') = t2.path
Or the reverse:  ON t1.title = replace(substring(t2.path, 6), '-', '')

You can use replace() function in the ON clause to compare both strings and get the desired result:

select     title, path
from       t1
inner join t2
on         'path/' || replace(t1.title, ' ', '-') = t2.path;

title             | path
:---------------- | :---------------------
gone wind forever | path/gone-wind-forever

select     title, path
from       t1
inner join t2
on         t1.title = replace(substring(t2.path, 6), '-', ' ');

title             | path
:---------------- | :---------------------
gone wind forever | path/gone-wind-forever

dbfiddle here


From the manual here, you could try experimenting with your my.cnf settings - I know that I had this problem in the past and I eventually got around this by using [client] setting.

# added from http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html
# to allow for abrowse to load data!
loose-local-infile  =   1

local-infile    = 1

Now, it was either local-infile or loose-local-infile which worked eventually. I believe that loose-local-infile is the more permissive setting, so you could try that first - and if you've no security concerns on your server, you could leave it at that.


If I understand you correctly, try this:

with ActionX as (
   select
        ActionID, coalesce(LoginID,'_')) as LoginID, ActionCode
    from ActionTable
)
select
    a.ActionID,
    a.LoginID
    b.ActionCode
from (
   select
        ActionID, max(LoginID) as LoginID
    from ActionX t
    group by ActionID
) a
join ActionX  b
   on b.ActionID  = a.ActionID
  and b.LoginID   = a.LoginID


Identify faulty sub-query (subquery returned more than 1 value)

I think the best way would be to "prove" that your subqueries - that are compared with a value - do always return one or no value.

By making sure they all have either:

TOP (1) ... ORDER BY ... (or the equivalent modern ORDER BY ... FETCH NEXT 1 ROWS ONLY)
WHERE clause that uses the primary or a unique key, e.g.: WHERE table_id = 496
HAVING clause that uses the primary or a unique key
GROUP BY () or an aggregate function in the HAVING or SELECT clause (this ensures that the subquery will return 1 or 0 rows)

This of course is not at all trivial with complex queries (example: nested derived tables or ctes and/or nested correlated subqueries) and even harder when the unique keys are not documented.

It's not trivial because for the most complex queries, you may have to figure out the unique keys of all the derived tables/ctes/subqueries which may involve joins and more nested subqueries (and for the queries that involve joins, the foreign key constraints may be needed as well to find the unique keys.)


Solution for deploying multiple instances of Django Server with Database replication

I'm going to highlight one of the products listed in the wiki link provided by Jack Douglas.
Galera is a product I've been hearing a lot about recently, but have not had a cause to implement it yet into a production environment.
Back in July, Percona wrote a brief blog about implementing it, but mentioned there was no 'production' release at that time.
They've since released the first major version, v1.0. And Vladimir has been shedding more light on it this month. And with some benchmarks
Due to your write-heavy environment, I highlight this use-case:

Distributing writes across the cluster will harness the CPU power in slave nodes for better use to process client write transactions. Due to the row based replication method, only changes made during a client transaction will be replicated and applying such a transaction in slave applier is much faster than the processing of the original transaction. Therefore the cluster can distribute the heavy client transaction processing across many master nodes and this yields in better write transaction throughput overall.

Disclaimer: I'm not affiliate with Galeria or Percona, and have not used it personally...yet.


Unix domain socket "/var/run/postgresql/.s.PGSQL.5432" missing

Try passing the -a option to ls to also show files starting with the period . character (i.e. "hidden" files):

$ ls -a /var/run/postgresql/
.  ..  .s.PGSQL.5432  .s.PGSQL.5432.lock


SQL Server instance running out of worker threads

Increasing MWT can lead to increased SOS_SCHEDULER_YIELD waits. Not the end of the world, but think of it like adding a buncha kids to a teacher's class. All of a sudden, it's gonna be harder for each kid to get attention.

When a process exhausts its 4ms quantum, there will potentially be more threads ahead of it waiting to get on the CPU. It's really difficult to say if performance will be worse with that trade off.

You could try increasing MWT a incrementally.

This is how SQL Server manages it:

Notice how doubling core counts doesn't double MWT, and you get the same number with 1 core as you do with 4 cores?

It's like a sign, or something.

The equation is: 512 + ((logical CPUs - 4) * 16), which means at 10 cores you'd have 608 threads, and at 12 cores you'd have 640 threads.

Those are reasonably safe increments to try, but I wouldn't go over that without a support call with Microsoft.

Hope this helps!


That was fixed. Upgrade to the current version, v5.3 via the Github repository or our site. (Disclaimer: I'm Brent Ozar.)


Does MySQL exectution plan depend on available indexes?

MySQL's optimizer looks only at what indexes are available.

There is an exception in 5.6:  If you have a subqueries such as
FROM ( SELECT ... ) JOIN ( SELECT ... ) ..., there are no indexes on the temporary tables that are created.  This used to lead to terrible performance.  Now, the optimizer will try out various indexes, and create the best one for the tmp table.  I suspect it only looks for single-column indexes, not "compound" indexes

InnoDB is getting the most attention these days.  MyISAM does not have any tricks up its sleeve.

Try out my Cookbook for creating the best index, given a SELECT.  It may save you some false starts.


You can try the following Command and make a left join on rate between both tables to get results as shown in your example

SELECT t1.AccountID, t1.AccountName, t1.Rate, t21.Amount, t1.OldRate, t22.Amount
FROM table1 t1
/* LEFT */ JOIN table2 t21 ON t1.Rate = t21.rate
/* LEFT */ JOIN table2 t22 ON t1.OldRate = t22.rate


Make sure slave has finished reading from master

FLUSH LOGS won't do anything useful for you.

With no log_type option, FLUSH LOGS closes and reopens all log files. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file.
-- http://dev.mysql.com/doc/refman/5.5/en/flush.html

The slave gets events as soon as they're written to the file... not when fsync() or fdatasync() is called and not when the file is closed or rotated... the events are sent across almost instantaneously.
Additionally, it's not only that you need the slave to finish reading everything from the master.  You really need to make sure it has also executed everything from the master.
A better solution would likely be to set the two machines up in master-master where each of them is always a slave to the other.  You'll read that master-master (a.k.a. "circular") replication brings with it some new issues and concerns, but many of these are related to writing to both machines at the same time and potentially causing replication to break due to conflicting writes applied to each machine at almost the same time.
What you'll do, instead, is treat one machine as the master, only allowing your application to write to that one.  When you want to switch... then all you have to do is switch... point your application connections that do the writing to the opposite machine.
An excellent and painless way to do this is with HAProxy.  It can even connect your application only to the preferred server if the preferred server is up, and switch your application to the backup server if the preferred server is down.  You configure your application's MySQL connection to point to HAProxy and configure HAProxy to relay those connections into the appropriate MySQL instance.
But, if you really want to do it manually, here's the way to know the two machines are currently in sync:
On the master:
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (6.53 sec)

mysql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
            File: mysql-bin.002211
        Position: 977403994
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.01 sec)

Keep that connection open, because it holds the read lock, which prevents any thread from writing to the master.  Your application will be able to read, but will block on writes.  Check SHOW MASTER STATUS several times in a row and when the Position stops incrementing (which should be almost immediately since you shouldn't get back the prompt from the FLUSH TABLES WITH READ LOCK until all pending writes are done) your master is frozen and will not write anything else to the binary log until you unlock the tables with UNLOCK TABLES; or you disconnect.  If you shut down the master while still holding the read lock, nothing else on the system should be changed.
On the slave: (extra fields removed for clarity)
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxxxxxxx
                  Master_User: xxxxxxxx
              Master_Log_File: mysql-bin.002211    <<<<<
          Read_Master_Log_Pos: 977403994           <<<<<
        Relay_Master_Log_File: mysql-bin.002211    <<<<<
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 977403994           <<<<<
        Seconds_Behind_Master: 0
             Master_Server_Id: 11
1 row in set (0.00 sec)

mysql>

When the Master_Log_File and Relay_Master_Log_File on the slave match the log file on the master and when Read_Master_Log_Pos (the last position the slave has received from the master) and Exec_Master_Log_Pos (the last position the slave has executed from the master) agree with the information you got from the master, and the *Running states are Yes and Seconds_Behind_Master is 0, then your slave has received and executed everything from the master and the two servers at that moment contain identical data in their tables.
The one caveat here is that if you're going to do this manually, you should probably -- somehow -- disconnect your application from the master before locking the tables, because if there are pending writes, they will happen on the master as soon as you unlock the tables.


Script to Restore/Create SQL Server 2000 Database from Another Database's Backup

RESTORE DATABASE [NewClientDB]
FROM  DISK = N'D:\OldClientDB.bak'
WITH  FILE = 1,
MOVE N'OldClientDB_Data' TO N'C:\Data\MSSQL.1\MSSQL\Data\AdventureWorksNew_Data.mdf',
MOVE N'OldClientDB_Log' TO N'C:\Data\MSSQL.1\MSSQL\Data\AdventureWorksNew_Log.ldf',
NOUNLOAD,  STATS = 10

Source: BACKUP and RESTORE in SQL Server


This sounds like a really simply one-to-many relationship.

For SQL Server, I would write this like:

CREATE TABLE Devices
(
    DeviceID INT
    , DeviceName nvarchar(255)
);

CREATE TABLE Cards
(
    CardID INT
    , CardName nvarchar(255)
    , DeviceID INT
);

CREATE TABLE Ports
(
    PortID INT
    , PortName nvarchar(255)
    , CardID INT
);

INSERT INTO Devices VALUES (1, 'Test Device 1');
INSERT INTO Devices VALUES (2, 'Test Device 2');
INSERT INTO Cards VALUES (1, 'Card 1 in Test Device 1', 1);
INSERT INTO Cards VALUES (2, 'Card 2 in Test Device 1', 1);
INSERT INTO Cards VALUES (3, 'Card 1 in Test Device 2', 2);
INSERT INTO Cards VALUES (4, 'Card 2 in Test Device 2', 2);
INSERT INTO Ports VALUES (1, 'Port in Card 1, Device 1', 1);
INSERT INTO Ports VALUES (2, 'Port in Card 2, Device 2', 4);

SELECT *
FROM Devices;

SELECT *
FROM Cards;

SELECT *
FROM Ports;

This allows a Device to have multiple Cards, which in turn can have multiple Ports.

The results:

The 3 tables can be JOINed together like this:

SELECT DeviceName, CardName, PortName
FROM Devices
    INNER JOIN Cards ON Devices.DeviceID = Cards.DeviceID
    INNER JOIN Ports ON Cards.CardID = Ports.CardID
ORDER BY DeviceName, CardName, PortName;

If you use LEFT JOIN like this:

SELECT DeviceName, CardName, PortName
FROM Devices
    LEFT JOIN Cards ON Devices.DeviceID = Cards.DeviceID
    LEFT JOIN Ports ON Cards.CardID = Ports.CardID
ORDER BY DeviceName, CardName, PortName;

you get these results:

This is an image showing the table relationships:


What is a good auto-generated key for a federated database?

One alternative is to perform a hash on some existing value or combination of values, for instance the user's login name and autogenerated identity (int):

SELECT
    CONVERT(NVARCHAR(40),
        HashBytes('SHA1', UserName + CAST(UserId AS VARCHAR)) ,2)
    AS FederatedIdentity
FROM Users

This way the value would be consistent across databases. You could persist the hash in a table; I usually create a view to work with generated columns like this.


Recovery model from Full to simple and Shrink transact log result on poor performance

Seen this happen a bunch.  Your first mistake was to shrink the transaction log.  When you set the recovery model to Simple the log is automatically truncated after a full backup of the Database.

What you have now is a combination of Index Fragmentation and Statistics needing to be rebuilt.  I would high recommend Ola Hallengren's free utility that can do both for you.

The following works for me after creating the procedures:

EXECUTE dbo.IndexOptimize
 @Databases = 'USER_DATABASES',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL'

The second thing that happened is most likely your query cache got purged and it now has to build new statistics and has cause parameter sniffing on your queries.  Rebuilding your statistics should help.


MySQL Workbench, Windows Server 2019, MySQL not showing Performance, Information of MySQL Schemas!

A similar issue, Information_Schema and mysql databases not shown, was submitted as a bug report to the MySQL Bug System nine years ago. The report was resolved as Not a Bug as the issue turned out to be something controlled by one of MySQL Workbench settings. Specifically, Valeriy Kravchuk suggested this resolution:

  Please, check "Show Metadata Schemata" checkbox in the Query Editor group of the SQL Editor tab in the Workbench Preferences dialog box. Use Edit > Preferences... menu item to open it. Don't forget to click "Refresh all" in the Object Browser's context menu after that.


How to determine if a hyphen (-) exists inside a column

You didn't mention why the code you provided doesn't work.  CONTAINS is for use with SQL Server's full text search feature.  If you're not using this, then you need to use a LIKE clause with wildcards:

CASE WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7') AND al.NEW_ADDRESS LIKE '%-%'
THEN CONCAT(al.ALT_ADDRESS,al.NEW_ADDRESS)

Even if you are using full text search, the matching behavior with dashes can be unexpected and Microsoft recommends using LIKE instead.

You might double check that the character is really a hyphen, something like this should work:

SELECT
    ASCII('-') as RealHypen,
    ASCII(SUBSTRING(NEW_ADDRESS, 1, 1))
FROM YourTable
WHERE ALT_ADDRESS = '2754 Churchill Circle';

As an aside, the LIKE expression above is not able to make use of the seeking abilities of a b-tree index in SQL Server, so performance may suffer on large tables if a large scanning operation is required. The best way to mitigate that (should it apply to you or others in a similar situation) depends heavily on context, but in general the main alternatives are:

Indexing a computed column that evaluates the search condition
Using triggers to persist the search result in advance
Use an external tool that's more suited to searching text (Elasticsearch is popular)
Use n-grams (typically for substrings of 3 characters or more)
Use Full Text Search (this won't help with wildcards per se, or this specific case, but can work for word-based searching).

If you are interested in exploring the performance aspect, please ask a follow up question.


THere are three ways to recover your tables:

http://www.mysqlperformanceblog.com/2011/05/13/connecting-orphaned-ibd-files/
http://www.chriscalender.com/?p=28
Extract records from the ibd files with page_parser/constraints_parser from Data Recovery Toolkit

UPDATE: Data recovery toolkit moved to GitHub


Does a SQL Server bit column really use a whole byte worth of space?

How many bit columns do you have defined in the table? I found this on MSDN, it says 8 or less bit columns are stored as one byte.

http://msdn.microsoft.com/en-us/library/ms177603.aspx


How can i automate the process of log shipping monitoring

The script below will give you most of the information from the standard reports in SSMS when executed on your secondary server.

select ls.primary_server,ls.primary_database,lsd.restore_delay,
DATEDIFF(mi,lms.last_restored_date,getdate()) as time_since_last_restore,
lms.last_copied_date,lms.last_restored_date,lms.last_copied_file,
lms.last_restored_file,
lsd.disconnect_users,ls.backup_source_directory,
ls.backup_destination_directory,ls.monitor_server

from msdb.dbo.log_shipping_secondary ls
join msdb.dbo.log_shipping_secondary_databases lsd
  on lsd.secondary_id=ls.secondary_id
join msdb.dbo.log_shipping_monitor_secondary lms
  on lms.secondary_id=lsd.secondary_id

You can then schedule this to run daily with a SQL Agent job or if you have SSRS you can easily schedule and format the report.

If you go the SQL Agent route you will need to configure Database Mail and use that as your delivery service for the results of the query.


Cassandra querying without full primary key

Yeah, It is possible with 'ALLOW FILTERING' option. BUT NOT ADVISABLE (well explained in this link). Schema design in Cassandra is majorly Query Specific (i.e) design your schema so that it is able to answer a query.
If you want to query on different attributes, go for individual schemas for each one.


What is the best way to filter or otherwise manipulate the results returned by a stored procedure?

One possible solution is to put the result set into a temp table and select from it. Here is an example for sp_who2: SQL Server: Filter output of sp_who2


MySQL still holding memory after I shut it down?

Memory management is a complex beast. If you are on a linux or similar platform, you might be seeing differences because of the page cache. This is a good writeup of what the page cache is.


pg_restore: [custom archiver] could not read from input file: end of file

I would suggest you try to do something along the line of:

SELECT count(*) FROM mytable WHERE my_primary_key < value ;

... and find at which point it fails. [This may be tedious, but you can just cut in half repeatedly, until you find the value that first fails.]

You will most probably need to alter the different settings that affect index usage, because you actually want to force the database to use the index, even if it actually must scan 99% of the table. You want it not to scan the page that booms.

If you can get SELECT to give you most of the data, you can then do something such as:

CREATE TABLE my_table_2 AS
SELECT * FROM my_table WHERE my_primary_key < value;

and later on:

ALTER TABLE my_table RENAME TO my_table_old ;
ALTER TABLE my_table_2 RENAME TO my_table ;

... and you'll have all the data that could be retrieved. I wouldn't drop the old table, in case someone finds later on a better method of retrieving the missing info.

Best of luck.


Function to update column value if the row was updated

It's hard to tell where you're going wrong with the error message but I'll speculate on two things. You want a BEFORE trigger, and this won't work:    where old.id=new.id;

I think what you want is,

create or replace function update_foo_version()
returns trigger as $body$
  begin
    new.version = old.version + 1;
    return new;
  end
$body$
language plpgsql;

create trigger update_version
BEFORE update on foo
for each row execute procedure update_foo_version();


I was able to solve my problem by adding the completion dates for JPM_DESCR90 (which is a field decription/name for certifications) in my query i.e. and then looked for max values of those dates in my query by adding MAX( TO_CHAR(C.JPM_DATE_3,'YYYY-MM-DD')) to the SELECT statement. This gives me the most recently acquired certification for each employee. I have attached a snapshot of my result as a jpeg image above

SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID, MAX( TO_CHAR(C.JPM_DATE_3,'YYYY-MM-DD'))
  FROM PS_NPS_VIEW_ALL A, (PS_PERSON_NAME B LEFT OUTER JOIN  PS_NPS_LIC_VW_RPT C ON  B.EMPLID = C.EMPLID ), PSOPRDEFN E
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_NPS_VIEW_ALL A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPLID = E.EMPLID
     AND A.PER_ORG = 'EMP'
     AND A.EMPL_STATUS='A'
     AND A.PAYGROUP NOT IN ('SUM','CWR'))
  GROUP BY  A.EMPLID,  A.JOBCODE,  A.JOBCODE_DESCR,  A.EMPL_STATUS,  A.LOCATION,  A.LOCATION_DESCR,  A.NAME,  E.EMAILID,  B.EMPLID,  E.OPRID

However, I have one more question. Is there a way to get all the certifications(JPM_DESCR90 aka Jobcodedescr column( in one row rather than in multiple rows for by getting each value of JPM_DESCR90 separated by ':' character for each employee irrespective of certification completion dates (C.JPM_DATE_3) ?


How to make conditional ordering for two or more columns

I'll admit I've never had to do this before so there was a bit of head scratching involved. Simple example table to demonstrate:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
    DROP TABLE [dbo].[MyTable]
GO

CREATE TABLE dbo.MyTable
(
    col1 INT
    , col2 CHAR(1)
)
GO

INSERT dbo.MyTable (col1, col2) VALUES (1, 'A')
INSERT dbo.MyTable (col1, col2) VALUES (1, 'B')
INSERT dbo.MyTable (col1, col2) VALUES (1, 'C')
INSERT dbo.MyTable (col1, col2) VALUES (2, 'A')
INSERT dbo.MyTable (col1, col2) VALUES (2, 'B')
INSERT dbo.MyTable (col1, col2) VALUES (2, 'C')
INSERT dbo.MyTable (col1, col2) VALUES (3, 'A')
INSERT dbo.MyTable (col1, col2) VALUES (3, 'B')
INSERT dbo.MyTable (col1, col2) VALUES (3, 'C')

Using an @SortStyle parameter to differentiate between sort orders, @SortStyle =1 will sort by col1 ASC, col2 DESC and @SortStyle=2 sort by col2 DESC, col1 ASC.

DECLARE @SortStyle INT
SET @SortStyle = 1

SELECT
    col1
    , col2
FROM
    dbo.MyTable
ORDER BY
    CASE
        WHEN @SortStyle = 1 THEN col1
    END ASC,
    CASE
        WHEN @SortStyle = 1 THEN col2
    END DESC,
    CASE
        WHEN @SortStyle = 2 THEN col2
    END DESC,
    CASE
        WHEN @SortStyle = 2 THEN col1
    END ASC

SET @SortStyle = 2

SELECT
    col1
    , col2
FROM
    dbo.MyTable
ORDER BY
    CASE
        WHEN @SortStyle = 1 THEN col1
    END ASC,
    CASE
        WHEN @SortStyle = 1 THEN col2
    END DESC,
    CASE
        WHEN @SortStyle = 2 THEN col2
    END DESC,
    CASE
        WHEN @SortStyle = 2 THEN col1
    END ASC

How do you ORDER BY a parameter covers the simpler case of sorting by just 1 column.


Determining Cardinality in a Logical Model

It should be based on logic. You should be able to express constraints in natural language. ERDs are great only if you already grok how to automatically verbalize diagrams. If not yet, then use plain text editor, it is better and more powerful than ERD.
For this example, starting with two predicates

Course type TYP exists. ctype {TYP}
Course CRS exists. course {CRS}

here are a few options based on constraints:

For each course, that course is of exactly one course type;
for each course type, more than one course may be of that type.
ctype {TYP}
   PK {TYP}

course {CRS, TYP}
    PK {CRS}
    FK {TYP} REFERENCES ctype {TYP}

For each course, that course may be of more than one course type;
for each course type, exactly one course is of that type.
ctype {TYP, CRS}
   PK {TYP}
   FK {CRS} REFERENCES course {CRS}

course {CRS}
    PK {CRS}

For each course, that course is of exactly one course type;
for each course type, exactly one course is of that type.
course {CRS, TYP}
    PK {CRS}
    AK {TYP}

For each course, that course is of at most one course type;
for each course type, more than one course may be of that type.
ctype {TYP}
   PK {TYP}

course {CRS}
    PK {CRS}

course_ctype {CRS, TYP}
          PK {CRS}

         FK1 {CRS} REFERENCES course {CRS}
         FK2 {TYP} REFERENCES ctype  {TYP}

For each course, that course may be of more than one course type;
for each course type, more than one course may be of that type.
ctype {TYP}
   PK {TYP}

course {CRS}
    PK {CRS}

course_ctype {CRS, TYP}
          PK {CRS, TYP}

         FK1 {CRS} REFERENCES course {CRS}
         FK2 {TYP} REFERENCES ctype  {TYP}

For each course, that course may be of more than one course type;
for each course type, at most one course is of that type.
ctype {TYP}
   PK {TYP}

course {CRS}
    PK {CRS}

course_ctype {CRS, TYP}
          PK {TYP}

         FK1 {CRS} REFERENCES course {CRS}
         FK2 {TYP} REFERENCES ctype  {TYP}

For each course, that course is of at most one course type;
for each course type, at most one course is of that type.
ctype {TYP}
   PK {TYP}

course {CRS}
    PK {CRS}

course_ctype {CRS, TYP}
          PK {CRS}
          AK {TYP}

         FK1 {CRS} REFERENCES course {CRS}
         FK2 {TYP} REFERENCES ctype  {TYP}

Note:
All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
FK = Foreign Key

In general, the constraint is verbalized as:
For each A, that A {is | may be} of {exactly one | more than one | at most one} B;
for each B, {exactly one | more than one | at most one} A {is | may be} of that B.
Term                        Range
---------------------------------
is of     exactly one       [1]
is of     at most one       [0,1]
may be of more than one     [0,*]


Does creating View cause an overhead to DB?

Refer the the Concepts Guide - Overview of Views for this sort of question:

  Overview of Views

  A view is a logical representation of one or more tables. In essence, a view is a stored query.
  [...]

  Characteristics of Views

  Unlike a table, a view is not allocated storage space, nor does a view contain data. Rather, a view is defined by a query that extracts or derives data from the base tables referenced by the view. Because a view is based on other objects, it requires no storage other than storage for the query that defines the view in the data dictionary.

Whether creating a view can have an impact on performance or not isn't answerable. If you don't use it, it won't impact anything. If you use it and it happens that the query the view "represents" has poor performance characteristics, then it will have essentially the same bad impact as running the underlying query directly.

Now materialized views are a completely different concept, and do actually contain data. They can also impact the performance of SQL statements that don't explicitly mention them via query rewriting - but again, what sort of impact that would have isn't answerable without specific details.

(Materialized views are more frequently (but not exclusively) used in data warehousing-type scenarios, whereas plain views are simply all over the place.)


Oracle database created whats next? Create Schema or Create Tablespace?

You've missed one place to get an overview of Oracle: the Concepts Guide. It covers all the major topics (including backup and recovery, which is quite important and doesn't appear in the list of links you've posted).

  Whats the next step? Create the Schema or Tablespace?

Both! They're orthogonal. Users are logical entities that access your database. Tablespaces are a storage concept. A user can have access to multiple tablespaces, and a tablespace can store data from multiple schemas. You need both, and you need to grant access to the appropriate tablespace to the users you create. (See e.g. here for the difference between user and schema.)

  Tablespace datafile(s) is where actual data from tables is stored?

Yes, all your database's data and indexes are stored in tablespaces. The main storage structures are:

Ordinary tablespaces store normal, persistent data. That's going to be the largest part of your database, space-usage wise.
Temporary tablespaces store non-persistent data - global temporary tables that get purged at the end of sessions or transactions, temporary storage for things like on-disk sorts, etc.
Undo tablespace(s) and redo log files: that's what Oracle uses to provide ACID guarantees.
Control files: they describe your database (name, files, log sequence and checkpoint information, even some backup info).

(The system tablespace is an ordinary tablespace, except that you shouldn't store anything in it - consider it as Oracle internal and off-limits for ordinary use.)

In addition, your should take great care of your redo log files, the "most crucial structure for database recovery". They are "hot" (lots of writes) and should be on their own disks/luns.

  How many [tablespaces/datafiles] are needed?

As much as you need. There's no general rule here. The number of datafiles will depend on how much data you need to store, operating system limits, Oracle datafile size limits, your storage (hard disks/volumes) constraints, backup/recovery considerations (e.g. having only one humongous Bigfile datafile might not be the best idea), ...

How you structure your tablespaces is up to you too. Having a tablespace per "application" in your tablespace can be good approach to get started. You can always create more tablespaces later if needed (but keep in mind that moving an object from one tablespace to another can be time-consuming, and might require either downtime or pretty complex operations).

  Default or Temporary?

Both! You need space to store your data persistently, and you also need some amount of temporary storage for your database's operation.

  How much space will I need for it?

Anywhere between a few megabytes and several terabytes – only you can know here. To estimate the space you need for a table, create a table with the same structure, fill it up with some sample data (should be more or less statistically representative of what you'll be storing in it) and measure the space usage. Then extrapolate. Don't forget to include the space required indexes (and materialized views)!

  Autoextend?

I'd say yes, use autoextend features, but set limits. You probably shouldn't let Oracle try to autoextend past the actual available space on your filesystems. And monitor space usage. (Keep in mind that datafile extension is relatively costly. Don't set the autoextend size too small.)

For ZFS specifically, Oracle has a whitepaper you might be interested in: Configuring ZFS for an Oracle Database (270k PDF).


How can I check if my user has create index permission?

You can use the sys.fn_my_permissions function to see if your user has ALTER on the table.

IF EXISTS(select * from sys.fn_my_permissions('dbo.thetable', 'OBJECT')
              WHERE permission_name = 'ALTER')
    BEGIN
        PRINT 'I have ALTER'
        --CREATE INDEX STATEMENT HERE
    END


Composite Primary Key efficiency as a Foreign Key

The cost of using a simple synthetic integer PK is small, and the benefit in your case would probably be quite considerable.

As you point out, you'll have a much simpler FK relationship.
A small PK makes for small (and fast) indices.  Your total table space will probably be made less by adding such a column.
If business rules ever change, you won't have to reorder the table.

The only material downside that comes to mind is that you may lose performance on queries which benefited from clustering on the composite PK.  If you think that's likely to be significant, than continue clustering on the composite candidate key, but put the PK on the synthetic key.


The idea of next or previous rows makes sense only in the presence of explicitly specified ORDER BY clause.

Now assuming that you need your resultset ordered by date you can achieve your goal either by using a subquery

SELECT date,
      (
        SELECT date
          FROM table1
         WHERE date > t.date
         ORDER BY date
         LIMIT 1
      ) leadDate,
       comment
  FROM table1 t;

or by leveraging user(session) variables

SELECT date, leadDate, comment
  FROM
(
  SELECT date, CAST(@d AS DATE) leadDate, @d := date, comment
    FROM table1 CROSS JOIN (SELECT @d := NULL) i
   ORDER BY date DESC
) q
 ORDER BY date;

Sample output (in both cases):

+------------+------------+----------+
| date       | leadDate   | comment  |
+------------+------------+----------+
| 2013-01-01 | 2013-03-03 | comment1 |
| 2013-03-03 | NULL       | comment2 |
+------------+------------+----------+

Here is SQLFiddle demo


Select results from table ONLY if rows meet 2 conditions

You can use an aggregate for that:

SELECT OrderId
FROM OrdersTable
WHERE Product in ('ProductA', 'ProductB')
group by orderid
having count(distinct Product) = 2;

This will give you those orders that include at least those two products. The orders might include other products as well.


How should I model a binary-tree like data using Adjacency List?

If you set up an index over the binary-tree related fields, leaving the fields in the table should have more or less the same performance as if you had them into their own table with a full covering index (as PostgreSQL supports index-only scans as of v9.2). It probably isn't a bad idea to set up some tables with filler data and do some test cases, though.

In regards to 2), there is a slightly different way you can represent this kind of data, and it really depends on the way you expect to be querying it. This might not be useful, but might give you some food for thought:

For my organization I had to come up with a way to represent organization structure in such a way that it facilitated very fast queries of the kind "give me every person who reports up to X but has direct reports", or "give me the list of persons who are within Z reporting levels to  this person". The solution is a slightly modified adjacency table of the form:

 h_ID, emp_ID, m_ID, lvlsAbv

where h_ID is an autogenerated key, emp_ID is the employeeID, m_ID is the managerID, and lvlsAbove is the # of reporting lvls difference between the 2 people. This means that each employee has multiple rows (1 for each manager above them).

Example:

h_ID    emp_ID    m_ID   lvlsAbv
42530   211432  254192  1
42531   211432  197829  2
42532   211432  256373  3
42533   211432  255628  4
42534   211432  256978  5
42535   211432  3735    6

The result is a slightly larger table, but is still small enough (size wise) to easily justify a covering index over the whole thing.

The advantage of this kind of structure is the ability to write very simple queries against relational properties of the tree (ex: "select everybody that is downtree of person X"). The downside is that it requires more work to construct and maintain (a lot more).


nstead of dropping and re-creating, why not just keep the same tables and truncate them and re-populate them? Then you don't have to mess with permissions each week.

However, if you want to keep doing this the hard way, this script might help.

SQL Server 2008+:

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH [schemas] AS
(
  SELECT id = [schema_id], name
  FROM sys.schemas
  WHERE name IN (N'Production',N'HResource',N'Sales')
),
[users] AS
(
  SELECT u, [schema] = s FROM
  (
    VALUES
    (N'HR_User1',N'HResource'),
    (N'HR_User2',N'HResource'),
    (N'P_User1', N'Production'),
    (N'P_User2', N'Production'),
    (N'S_User1', N'Sales'),
    (N'S_User2', N'Sales')
  ) AS v(u,s)
)
SELECT @sql += N'
GRANT SELECT, UPDATE, INSERT ON '
  + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
  + ' TO ' + QUOTENAME(u.u) + ';'
FROM [schemas] AS s
INNER JOIN [users] AS u
ON s.name = u.[schema]
INNER JOIN sys.tables AS t
ON s.id = t.[schema_id];

PRINT @sql;
-- EXEC sp_executesql @sql;

If you're stuck on 2005, you'll need to make a couple of changes (and start thinking about your next move):

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

;WITH [schemas] AS
(
  SELECT id = [schema_id], name
  FROM sys.schemas
  WHERE name IN (N'Production',N'HResource',N'Sales')
),
[users] AS
(
  SELECT [user]    = N'HR_User1', [schema] = N'HResource'
    UNION ALL SELECT N'HR_User2',            N'HResource'
    UNION ALL SELECT N'P_User1',             N'Production'
    UNION ALL SELECT N'P_User2',             N'Production'
    UNION ALL SELECT N'S_User1',             N'Sales'
    UNION ALL SELECT N'S_User2',             N'Sales'
)
SELECT @sql = @sql + N'
GRANT SELECT, UPDATE, INSERT ON '
  + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
  + ' TO ' + QUOTENAME(u.u) + ';'
FROM [schemas] AS s
INNER JOIN [users] AS u
ON s.name = u.[schema]
INNER JOIN sys.tables AS t
ON s.id = t.[schema_id];

PRINT @sql;
-- EXEC sp_executesql @sql;


What are the challenges of administrating an International stock trading database?

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

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

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

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

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

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

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

All systems are built by balancing tradeoffs.


Trying to understand SHOW SLAVE STATUS in MySQL

I want to understand further is the Relay_Log_File, Relay_Log_Pos and Relay_Master_Log_File.

From SHOW SLAVE STATUS\G, get two values

Relay_Log_File : Current relay log accepting new entries during replication
Relay_Log_Pos : Current position of the current relay log accepting new entries during replication
Relay_Master_Log_File : Relay log file containing the last successfully executed SQL statement on the Master that was executed on the Slave.
Master_Log_File : The log on the Master that corresponds to the current relay log file and current relay log position

  The files are in binary so not able to view them right

Yes, you can view the relay logs. Since they have the same structure as a regular binary log, just run the mysqlbinlog program. For example to view the SQL within any of you relay logs, such as the last one, do this:

mysqlbinlog mysqld-relay-bin.000056 > /root/SQLForCurrentRelayLog.txt
less /root/SQLForCurrentRelayLog.txt


Count/Statistic for number of SHOW INDEX queries in MySQL/MariaDB 5.5


How to install Oracle's sqlplus on a Mac?

Download Basic Package and SQL*Plus Package zip files from Oracle
Add a local repository to brew from where the Oracle packages will be installed: brew tap InstantClientTap/instantclient
Copy both Oracle's zip files to /usr/local/Homebrew/Library/Taps/instantclienttap/homebrew-instantclient
Install the basic package: brew install instantclient-basic
Install the SQL*Plus package: brew install instantclient-sqlplus
Test sqlplus by connecting to an existing database: sqlplus user/password@server:port/sid

My intention was to improve Joost van Wollingen's article published here.


In your first query the error is produced by the attribute type, which is in the select clause, but not in the group by clause. This is due to the fact that there are different values of type for each group. So, instead of writing:

select maker, type from product group by maker
having count(distinct type) = 6 and count(distinct model) > 1

you should either remove type:

select maker from product group by maker
having count(distinct type) = 6 and count(distinct model) > 1

or use an aggregation function for it, like in the second query:

select maker, min(type) from product group by maker
having count(distinct type) = 6 and count(distinct model) > 1


Reduce your log levels; for example, turn off log_transaction and set log_statement to something above all. Use pg_stat_statements instead of relying on full statement logging. Use Munin/Cacti/collectd/similar to collect system stats rather than relying on tools like pgbadger for log parsing.

Use logrotate if you're on a Linux system and using a system PostgreSQL install. It knows how to compress then age out logs. See /etc/logrotate.d/ .

PostgreSQL's built-in log rotation, as you have observed, is somewhat limited and really expects you'll be archiving and deleting them via a cronjob or similar. But you can do just that and use log_rotation_size.

You can also log to syslog. This gives you severity filtering, etc with a decent syslog like rsyslogd. You can make it keep ERROR level logs for ages, but quickly discard INFO and older, for example, by working alongside logrotate. You can even log to a remote syslog server with abundant disk space if you want to do long term archival.

There are commercial log ingestion products you can use to feed your logs in for archival and analysis too.


Returning a True or False from an IIF statement

This is how TRUE and FALSE work in VBA (and Access): https://stackoverflow.com/questions/4275800/what-are-the-integer-values-of-boolean-false-and-true-in-vb6

If you want a 1 and a 0 like most other programming languages, return 1 and 0 and evaluate that instead of using the keywords TRUE and FALSE. That will make it consistent with other languages, but it won't be internally consistent with Access/VBA.

Something like this:

    IIf([qry_ebs_final_CAN-ENROL-ONLINE].[SP_CAN_ENROL]='Y',1,0)


Handle no more connections or out of shared memory

The number of threads depends on a previous result.

Could you rewrite this to have fewer queries returning more rows each?  If you launch one thread per row to be returned, you are probably introducing more overhead than you are removing.

  If I hardcode the maximum number of connections I will run out of connections when something else is connected.

Unless that "something else" is another golang program doing much the same as the first one, or something equivalent, then you can make max_connections only be slightly larger than the max pool size in golang, and you shouldn't have a problem.  It does require you to have a fairly good idea of what the other things are.

  On the other hand if I hardcode a too low number of allowed connections from the golang program performance will be unnecessary limited.

Have you verified this?  Having 300 connections fighting over 16 CPUs and 4 hard drives is not going to enhance productivity.  There is only so much hardware, and now you have to unproductively fight over spinlocks (for example) as well as do the real work.

  I am thinking of running PgBouncer between the database and the golang program, hopefully accepting all the connections from the golang program, allowing as many as possible through, but blocking the rest until connections become free.

It can do this, but why would it be better than having golang itself do it?  It could make sense if you can push your "other connections" through pgbouncer, but can't push them through golang.  Then you can have one pooler that sees everything.  On the other hand, it introduce another layer of latency on every network round trip.  Plus another layer of complexity.


PostgreSQL 9.3: Dump a database excluding the data of some tables

I think you need to use --exclude-table-data=table option. From the docs:

--exclude-table-data=table
Do not dump data for any tables matching the table pattern. The pattern is interpreted according to the same rules as for -t. --exclude-table-data can be given more than once to exclude tables matching any of several patterns. This option is useful when you need the definition of a particular table even though you do not need the data in it.
To exclude data for all tables in the database, see --schema-only.


This is a bug in SQL Server 2012, and will be fixed in SP2:

http://connect.microsoft.com/SQLServer/feedback/details/779077/output-clause-fails-when-doing-update-using-cte

A workaround is to remove the + 1 from the ROW_NUMBER() calculation; this spurious error message goes away. Rewrite this portion:

  NewLineNumber = row_number() over
    (partition by SalesOrderID order by SalesOrderDetailID)
    ---------------- remove the +1 here --------------------^^^
  from Sales.SalesOrderDetail
  where SalesOrderID <= 44000)
update sod
  set LineNumber = NewLineNumber + 1,
  ------- and put it here -------^^^


MySQL Dump Restoration when FOREIGN_KEY_CHECKS=0

That is a default option in mysqldump. As the name states it only disables the foreign key check, which is necessary. If you don't disable the check, you'll almost certainly run into foreign keys errors, unless the restore, coincidentally, honors the parent/child relationships in the sequence that it restored the tables in. Extremely unlikely. Mysqldump doesn't account for this.

Look at it this way. If your dump restore tries to restore a child table before its parent, it will see the rows as orphan records and the check will give an error.

As ypercube said. The dump file will contain a set command at the end to turn the check back on. It's also a session based variable.

To address your concern. No. The set command does not remove foreign keys from your tables.


You should consider the use of datetime instead but this will do what you want.

group by left(DateTimeSent, 19)


Poor performing subquery with date comparisons

With the index definition that you have for IDX_my_nme, SQL Server will be able to seek using the ActionDate column but not with the Address column. The index contains all of the columns needed to cover the subquery but it likely isn't very selective for that subquery. Suppose that almost all of the data in the table has an ActionDate value of earlier than '2017-05-30'. A seek of ActionDate < '2017-05-30' will return almost all of the rows from the index, which are further filtered down after the row is fetched from the index. If your query returns 200 rows then you would probably doing almost 200 full index scans on IDX_my_nme, which means you will read around 50000 * 200 = 10 million rows from the index.

It's likely that seeking on Address will be far more selective for your subquery, although you haven't given us full statistical information about the query so that's an assumption on my part. However, suppose that you create an index on just Address and your table has 10k unique values for Address. With the new index, SQL Server will only need to seek 5 rows from the index for each execution of the subquery, so you'll read around 200 * 5 = 1000 rows from the index.

I'm testing against SQL Server 2016 so there might be some minor syntax differences. Below is some sample data in which I made similar assumptions to the above for data distribution:

CREATE TABLE #Activity (
    Id int NOT NULL,
    [Address] varchar(25) NULL,
    ActionDate datetime2 NULL,
    FILLER varchar(100),
    PRIMARY KEY (Id)
);

INSERT INTO #Activity WITH (TABLOCK)
SELECT TOP (50000) -- 50k total rows
x.RN
, x.RN % 10000 -- 10k unique addresses
, DATEADD(DAY, x.RN / 100, '20160201') -- 100 rows per day
, REPLICATE('Z', 100)
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) x;

CREATE NONCLUSTERED INDEX [IDX_my_nme] ON #Activity
([ActionDate] ASC) INCLUDE ([Address]);

I've created your index as described in the question. I'm testing against this query which returns the same data as the one in the question:

select
    a.*
    , ( select count(*)
        from #Activity Activity
        where
            Activity.[Address] = a.[Address]
            and Activity.ActionDate < a.ActionDate
    ) as PriorCount
from #Activity a
where a.ActionDate between '2017-05-29' and '2017-05-30'
order by a.ActionDate desc;

I get an index spool. What that means at a basic level is that the query optimizer build a temporary index on-the-fly because none of the existing indexes against the table were suitable.

The query still finishes quickly for me. Perhaps you aren't getting the index spool optimization on your system or there's something different about the table definition or the query. For educational purposes I can use an undocumented feature OPTION (QUERYRULEOFF BuildSpool) to disable the index spool. Here's what the plan looks like:

Don't be fooled by the appearance of a simple index seek. SQL Server reads nearly 10 million rows from the index:

If I'm going to be running the query more than once then it probably doesn't make sense for the query optimizer to create an index each time it runs. I could create an index upfront that would be more selective for this query:

CREATE NONCLUSTERED INDEX [IDX_my_nme_2] ON #Activity
([Address] ASC) INCLUDE (ActionDate);

The plan is similar to before:

However, with the new index SQL Server only reads 1000 rows from the index. 800 of the rows are returned to be counted. The index could be defined to be more selective but this could be good enough depending on your data distribution.

If you aren't able to define any additional indexes on the table I would consider using window functions. The following appears to work:

SELECT t.*
FROM
(
    select
        a.*
        , -1 + ROW_NUMBER() OVER (PARTITION BY [Address] ORDER BY ActionDate) PriorCount
    from #Activity a
) t
where t.ActionDate between '2017-05-29' and '2017-05-30'
order by t.ActionDate desc;

That query does a single scan of the data but does an expensive sort and calculates the ROW_NUMBER() function for every row in the table, so it feels like there's some extra work done here:

However, if you really like that code pattern you could define an index to make it more efficient:

CREATE NONCLUSTERED INDEX [IDX_my_nme] ON #Activity
([Address], [ActionDate]) INCLUDE (FILLER);

That moves the sort towards the end which will be much less expensive:

If none of this helps then you'll need to add more information to the question, preferably including actual execution plans.


SQL1641N error on linux while running db2start using db2 express c on linux (LUW) but the file system is not mounted using nosuid

The files in /opt/ibm/db2/V10.5/ are not to be used for a specific instance.  When you run the db2icrt command, it copies a series of files and directories into ~db2inst1/sqllib, and sets the permissions accordingly.  (Some of the directories in ~db2inst1/sqllib will be soft links back to directories in /opt/ibm/db2/V10.5).

For example, you should find that ~db2inst1/sqllib/adm/db2start has permissions set to 6555 (i.e. -r-sr-sr-x), owned by root, and have the group owner set to the instance owner's primary group.

You should not be executing things from /opt/ibm/db2/V10.5/adm.  That is why the permissions of the files in that directory are set the way they are – /opt/ibm/db2/V10.5/adm/db2start should be 0444.

So, to start your instance, you should be executing ~db2inst1/sqllib/adm/db2start.

Be aware that you also need to initialize the db2inst1 user environment prior to starting the instance, so you should use the -i option for sudo to handle this:

sudo -i -u db2inst1 /home/db2inst1/sqllib/adm/db2start

One last note:  All of this configuration (except perhaps the db2start) can be done using a response file and db2setup.   db2_install has been deprecated, so using response files is the way to handle this going forward.


MySQL optimization - more tables vs more columns/rows

One table.  Period.  Full-stop.
A general rule is not to have multiple "identical" tables.  It is a maintenance problem, and it is likely to be slower.  (The common exception is SaaS, such as WordPress, where they simply live with lots of data bases with the 'same' 20 tables.)
"35 datapoints ... dates"  -- I'm confused.  Either you have information about a stock or you have daily prices going back years.  Are you saving only 5 weeks of data?  Or what do you mean?
5K rows in one table is "tiny" as datasets go.
Does this also have stock prices for each day?  With this, you get to millions or maybe billions of rows of about 6 columns.  Again, I strongly recommend a single table for all prices.  (Plus a table for all the tickers -- again 5K rows of 30 columns.)
I have dealt with such datasets, so ask away.  Or look around; I have answered questions like this in this forum and also in stackoverflow.com
The prices table would have something like
stock_id MEDIUMINT UNSIGNED -- 2 bytes
dy DATE  -- 3 bytes
open, high, low, close   -- see below

The prices get tricky.
FLOAT -- ~7 significant digits, 4 bytes; maybe good enough, might have roundoff
DECIMAL -- but how many decimal places; potentially bulky
INT -- not good enough
DOUBLE -- 8 bytes; overkill

Indexing will be important, but first, figure out what the inserts and selects are like.  These may be optimal:
PRIMARY KEY(stock_id, dy)
INDEX(dy, stock_id)

For one stock_id, there will be many rows.  Do not use 'many' columns for an "array"; that is very clumsy.  Perhaps your dates are "quarterly", as in company reports, not "daily" as in stocks?
5000 companies with 8 years of quarterly reports, at one row each == 160K rows in the single table.  That's still a "small" table.
Plus another table with 5000 rows, one per company, with name, address, etc.  That is, attributes that don't change.
Yes, income, balance, cashflow, etc should be separate columns in the table.  Assuming those values come from a quarterly report, then they would be in the 160K-row table I discussed above.
A table with a few dozen columns and a few million rows is no problem (in general).  If you get to 100 columns or a billion rows, you may encounter non-trivial problems.


You can look in the plan cache to get a pretty good idea of Stored Procedure usage.  Take this query, for instance:

select
    db_name(st.dbid) as database_name,
    object_name(st.objectid) as name,
    p.size_in_bytes / 1024 as size_in_kb,
    p.usecounts,
    st.text
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) st
where p.objtype = 'proc'
and st.dbid = db_id('SomeDatabase')
order by p.usecounts desc

This will give you the usecounts of the stored procedures that are cached pertaining to SomeDB.

Note: the plan cache contains the execution plans.  This retention of these plans has many factors involved.  Whereas this will give you a good idea of what is being used and how often, it's definitely not the running total of stored procedures and how often/when they were executed.

BOL Reference about the Plan Cache


By restarting SQL Server and MobiLink seems to have resolved.But it is taking too long time to sync the 10mb data.


There is one problem with aliases usability in different parts of a query - aliases defined in the SELECT clause are only available in GROUP BY, ORDER BY and HAVING, not in WHERE or JOIN .. ON, so as you do not add the table specificator to the second stat_year, it probably takes the same column and the = is trivially true.

You can check that with EXPLAIN EXTENDED <query>; SHOW WARNINGS; which will tell you how MySQL "sees" your query (canonizing all column references with db and table names).

You need to replace it by the actual expression - ON job.stat_year = YEAR(td.db_date) - the one in the GROUP BY can stay the same as they will be equivalent after the join is done.


You are right, the oplog truncation is killing your cursor.
There are no indexes on the oplog, so dump has to scan from the very beginning to find the documents that match the query.
In that case of this mongodump cursor, it had examined 356596 documents, but still hadn't found any that matched the query.
The failure message reports the last seen record was "RecordId(6850019249918838732)".  That number is a 64-bit value that corresponds to the ts.
You can get the first 32 bits and the corresponding timestamp from bash:
% echo $((6850019249918838732 >> 32))
1594894391

That date is Thu Jul 16 10:13:11 UTC 2020 still over a month prior to the first date of interest.
When those documents were trimmed from the capped collection, the cursor no longer pointed to a valid position, so it was not possible to continue.
When that happens, you'll need to restart the mongodump so it can start scanning from the new beginning of the oplog.


does a read operation have always precede a write operation

Definitely not.

Consider the following transaction:

START TRANSACTION;
UPDATE employee
   SET salary = null;
COMMIT;

No read done by the transaction controlling code:


I cannot restart mysql after mysql_upgrade

The error seems to indicate that the source version is MariaDB-10.3 and the destination version is MySQL-8.0.

There is no supported migration path in-place from MariaDB to MySQL.

Recommend taking a mysqldump of the databases and a copy of the database users and grants to recreate.


You used this predicate: COLUMN_A = 'NA'

The result of COUNT(COLUMN_A) can be 0 only if you have 0 rows where COLUMN_A = 'NA' (as nulls are excluded because of the predicate). If you have 0 rows, the CASE statement is not evaluated at all, and the above query returns no result.

You need a query that returns result even if no rows match your predicate. E.g:

SELECT
  CASE
    WHEN C = 1
      THEN 'PASS'
    WHEN C > 1
      THEN 'FAIL'
    ELSE 'DOES NOT EXIST',
    'NA'
FROM
(
  SELECT COUNT(*) AS C
  FROM SCHEMA.TABLE_A SCH
  WHERE SCH.COLUMN_A = 'NA'
);


Improving slow queries. Slow query log attached

One area that stands out is the way you are accessing the BillDetails table. All queries appear to be using MONTH and YEAR operators on BillDate. For example:

SELECT SUM(GROSSAMOUNT) FROM BILLDETAILS WHERE MONTH(BILLDATE) = 8 AND YEAR(BILLDATE) = 2011;

This approach requires accessing every record in BillDetails. A better approach would be to index BillDate and query as follows:

SELECT SUM(GROSSAMOUNT) FROM BILLDETAILS WHERE BILLDATE BETWEEN '2011-08-01' AND '2011-08-31';

Looks like you don't have much data in there at the moment (1373 rows) so the difference in the above will be minimal at the moment, more significant when the volume grows.


Nvarchar to varchar to nvarchar characters conversion

No, there is no way to "fix" the data because the data is no longer there. When you converted to VARCHAR, the underlying values for each character were changed into the ASCII value for ?. This is not a display issue, those characters are now physically a regular question mark. You will need to do a restore from a backup, unfortunately.

The following example code shows that once a Unicode character is converted to VARCHAR (assuming the Code Page denoted by the Collation does not support that character), that it becomes a regular 'ol question mark, and forever shall it remain as such:

DECLARE @Character NCHAR(1) = NCHAR(0x3525);
SELECT @Character AS [TheCharacter],
       UNICODE(@Character) AS [DecimalCodePoint],
       ASCII(@Character) AS [DecimalValueOfVarchar],
       UNICODE(CONVERT(VARCHAR(5), @Character)) AS [ConvertToVarchar],
       UNICODE(CONVERT(NVARCHAR(5), CONVERT(VARCHAR(5), @Character)))
             AS [ConvertToVarcharAndBack],
       ASCII('?') AS [VarcharQuestionMark],
       UNICODE(N'?') AS [UnicodeQuestionMark];

-- 㔥    13605   63  63  63  63  63

The following example shows an instance of a Unicode character that is highly doubtful (at least at this time) to be supported in most fonts, hence it appears as a square box, but the UNICODE built-n function shows that the underlying code is still the correct Unicode Code Point:

SELECT NCHAR(0xABBF), N'ꮿ', UNICODE(N'ꮿ');

-- ꮿ    ꮿ   43967

The actual character can be seen here: Cherokee Small Letter YA U+ABBF. This is a display issue, and many characters that are not represented in various fonts will display in the same manner without altering the actual value of the character, but they are still distinct characters.


Seperate Address Table for Patient and Emergency Contact Person?

The second approach is much better. One massive advantage is that both patient and emergency contact could have the same address. Typically an emergency contact is a parent or spouse, so you could have a checkbox in your application that says "Emergency contact's address is same as Patient's". This would save on space in the database, add convenience for users of your application without any extra development effort from yourself.


Where is this mystery trace going (SQL Server 2000)?

NULL means it's using the rowset provider for trace I/O. See for reference the below article:
Server-Side Tracing and Collection (section "Querying Server-Side Trace Metadata")

This query returns the trace status, which will be 1 (started) or 0 (stopped); the server-side path to the trace file (or NULL if the trace is using the rowset provider); the maximum file size (or again, NULL in the case of the rowset provider); information about how many buffers of what size are in use for processing the I/O; the number of events captured; and the number of dropped events (in this case, NULL if your trace is using the file provider).
Note For readers migrating from SQL Server 2000, note that the sys.traces view replaces the older fn_trace_getinfo function. This older function returns only a small subset of the data returned by the sys.traces view, so it’s definitely better to use the view going forward.

For details on rowset provider see for example:
SQL Trace Architecture and Terminology

The rowset provider, on the other hand, is not designed to make any data loss guarantees. If data is not being consumed quickly enough and its internal buffers fill, it waits up to 20 seconds before it begins jettisoning events in order to free buffers to get things moving. The SQL Server Profiler client tool will send a special error message if events are getting dropped, but you can also find out if you’re headed in that direction by monitoring SQL Server’s TRACEWRITE wait type, which is incremented as threads are waiting for buffers to free up.


Postgresql script doesn't work as I expect

Where did you find such syntax in the documentation?
You need declare variables in the same block.
DO $$
DECLARE companyNrs text[] = ARRAY[1,2,3];
        companyNr text;
        v_sql text;
BEGIN
  FOREACH companyNr IN ARRAY companyNrs
  LOOP
    v_sql := format('foo bar %L', companyNr);
    raise notice 'notice message %', v_sql;
  END LOOP;
END; $$;

Or it is possible to declare another sub-block:
DO $$
DECLARE companyNrs text[] = ARRAY[1,2,3];
        companyNr text;
BEGIN
  FOREACH companyNr IN ARRAY companyNrs
  LOOP
    --
    -- Create a subblock
    --
    DECLARE
        v_sql text;
    BEGIN
        v_sql := format('foo bar %L', companyNr);
        raise notice 'notice message %', v_sql;
    END;
  END LOOP;
END; $$;

Subblocks with EXCEPTION clause are implicit savepoints and have a performance impact. Not sure about subblocks without EXCEPTION clause. They may not be cheap syntactic sugar either.


Move data with schema change between MariaDB servers

Write a SELECT statement that JOIN the two tables together.  Specify exactly the target columns desired (most coming from one table, one from the other table).
Then do

Something like

INSERT INTO new (a,b,c,d)
    SELECT x.a, x.b, x.c, y.d
        FROM x
        JOIN y ON ...

For reading from one server and writing to another, see FEDERATEDx.


Can I automate the "on" statement from MySQL queries?

If the columns were named the same in "both" tables, I think you could use NATURAL JOIN. Although that won't work for you, your consistency in naming should still let you generate queries--or at least the join clauses--with a scripting language.


(MYSQL) Select union of rows depending on row before?

About 4.5 years ago, I wrote a rather aggressive stored procedure to traverse data stored in a hierarchy and bring back all descendants within that table : Find highest level of a hierarchical field: with vs without CTEs

I took the code from the GetFamilyTree function and wrote it as a procedure for you

STORED PROCEDURE

DELIMITER $$
DROP PROCEDURE IF EXISTS GetDataFromData $$
CREATE PROCEDURE GetDataFromData (GivenData VARCHAR(255))
ThisStoredProcedure:BEGIN

    DECLARE rv,q,queue,queue_children VARCHAR(1024);
    DECLARE queue_length,front_id,pos INT;

    SET rv = '';

    DROP TABLE IF EXISTS DataFromData;
    CREATE TEMPORARY TABLE DataFromData LIKE mytable;

    SELECT COUNT(1) INTO @FoundCount FROM mytable WHERE data = GivenData;

    IF @FoundCount = 1 THEN
        SELECT id INTO @FoundID FROM mytable WHERE data = GivenData;

        SET rv = @FoundID;
        SET queue = @FoundID;
        SET queue_length = 1;

        WHILE queue_length > 0 DO
            SET front_id = FORMAT(queue,0);
            IF queue_length = 1 THEN
                SET queue = '';
            ELSE
                SET pos = LOCATE(',',queue) + 1;
                SET q = SUBSTR(queue,pos);
                SET queue = q;
            END IF;
            SET queue_length = queue_length - 1;

            SELECT IFNULL(qc,'') INTO queue_children FROM
            (SELECT GROUP_CONCAT(otherid) qc FROM mytable WHERE id = front_id) A;

            IF LENGTH(queue_children) = 0 THEN
                IF LENGTH(queue) = 0 THEN
                    SET queue_length = 0;
                END IF;
            ELSE
                IF LENGTH(rv) = 0 THEN
                    SET rv = queue_children;
                ELSE
                    SET rv = CONCAT(rv,',',queue_children);
                END IF;
                IF LENGTH(queue) = 0 THEN
                    SET queue = queue_children;
                ELSE
                    SET queue = CONCAT(queue,',',queue_children);
                END IF;
                SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
            END IF;
        END WHILE;

        SET @sql = CONCAT('INSERT INTO DataFromData SELECT * FROM mytable WHERE id in (',rv,')');
        PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
    END IF;

    SELECT * FROM DataFromData;

END $$
DELIMITER ;

SAMPLE DATA FROM YOUR QUESTION

DROP DATABASE IF EXISTS magistermundus;
CREATE DATABASE magistermundus;
USE magistermundus
CREATE TABLE IF NOT EXISTS `mytable` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `data` VARCHAR(255),
    `otherid` int(11),
    PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO mytable (data,otherid) VALUES ('stuff',2);
INSERT INTO mytable (data,otherid) VALUES ('x',4);
INSERT INTO mytable (data,otherid) VALUES ('y',NULL);
INSERT INTO mytable (data,otherid) VALUES ('z',NULL);
SELECT * FROM mytable;

SAMPLE DATA FROM YOUR QUESTION LOADED

mysql> DROP DATABASE IF EXISTS magistermundus;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE magistermundus;
Query OK, 1 row affected (0.01 sec)

mysql> USE magistermundus
Database changed
mysql> CREATE TABLE IF NOT EXISTS `mytable` (
    ->     `id` int(11) NOT NULL AUTO_INCREMENT,
    ->     `data` VARCHAR(255),
    ->     `otherid` int(11),
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('stuff',2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('x',4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('y',NULL);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('z',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM mytable;
+----+-------+---------+
| id | data  | otherid |
+----+-------+---------+
|  1 | stuff |       2 |
|  2 | x     |       4 |
|  3 | y     |    NULL |
|  4 | z     |    NULL |
+----+-------+---------+
4 rows in set (0.00 sec)

mysql>

STORED PROCEDURE EXECUTED

mysql> CALL GetDataFromData('stuff');
+----+-------+---------+
| id | data  | otherid |
+----+-------+---------+
|  1 | stuff |       2 |
|  2 | x     |       4 |
|  4 | z     |    NULL |
+----+-------+---------+
3 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

CAVEAT # 1

Writing this using UNION SELECT was not practical since the depth of the tree of data is not known. This code will handle any depth of data.

CAVEAT # 2

This code does not handle circular references in data (Where (id,otherid) and (otherid,id) coexist in this table).