Primary key with randomly varying increments (so it cannot be guessed easily)

I suggest a function taking a regclass parameter that runs ALTER SEQUENCE with a new randomly generated increment before it returns the next value from a given sequence.
Can be used as drop-in replacement for nextval().

Per documentation on ALTER SEQUENCE:

  increment

  The clause INCREMENT BY increment is optional. A positive value will
  make an ascending sequence, a negative one a descending sequence. If
  unspecified, the old increment value will be maintained.

However:

  You must own the sequence to use ALTER SEQUENCE.

So we need to take care of privileges. You could make the function SECURITY DEFINER and owned by a superuser. If you don't REVOKE privileges from public it works for anyone on any sequence. There are two basic strategies to restrict usage:

To allow for selected sequences only, change the owner of those sequences to some dedicated role, say randseq and make randseq own the function (still with SECURITY DEFINER).
To allow for selected roles only, REVOKE all privileges on the function from public and GRANT EXECUTE to said roles. You might use a group role to simplify privilege management.

Or combine both:

CREATE OR REPLACE FUNCTION nextval_rand(regclass)
  RETURNS int AS
$func$
BEGIN
EXECUTE format('ALTER SEQUENCE %s INCREMENT %s'
               , $1                          -- regclass automatically sanitized
               , (random() * 100)::int + 1); -- values between 1 and 100
RETURN nextval($1)::int;
END
$func$ LANGUAGE plpgsql SECURITY DEFINER;

-- to restrict usage:
ALTER FUNCTION nextval_rand(regclass) OWNER TO randseq;
REVOKE ALL ON FUNCTION nextval_rand(regclass) FROM public;
GRANT EXECUTE ON FUNCTION nextval_rand(regclass) TO randseq;
GRANT randseq TO ???;

Call:

SELECT nextval_rand('tbl_tbl_id_seq'::regclass);

All you have to do now is replace nextval() with nextval_rand() in the column default of any serial column. And possibly change the owner of the sequence.

ALTER SEQUENCE tbl_tbl_id_seq OWNER TO randseq;
ALTER TABLE tbl ALTER COLUMN tbl_id SET DEFAULT nextval_rand('tbl_tbl_id_seq'::regclass);

Notes

ALTER SEQUENCE is designed not to block concurrent transactions. It takes effect immediately and cannot be rolled back. It should work reliably in a multi-user environment. Read the Notes section of the manual page for the fine print of ALTER SEQUENCE behavior.

There is a very slim chance for a race condition, where two concurrent operations each run ALTER SEQUENCE before calling nextval(). Since we are operating with random numbers anyway, this really doesn't matter.

Since we are running dynamic SQL I would normally SET search_path = public, pg_temp for the function. But since the parameter is regclass, only valid sequence names can be passed and are automatically schema-qualified and escaped unambiguously.


Mirror / replicate only 1 table of a database

You can use snapshot replication (it allows you to update the table every 5-10 minutes). If you would like instant changes, go for transactional replication.

Note that you cannot publish a replication on SQL Server Express, you need at least Standard edition.


One possible reason:

Bug 18162779 : ORA-01428: ARGUMENT '000000000000000000' IS OUT OF RANGE

Try running the query after setting the below:

alter session set  "_disable_function_based_index" = TRUE;


efficient primary key that does not expose business internals

First make sure that it is important that the user doesn't know this information. Is it really a problem if they know that 12 other tasks were recorded between they last two? What could they do with this information that would cause problems?

If you are concerned that they could potentially access data they should by tweaking client-side requests (asking for getdata.php?recordid=1233, someone else's record, instead of getdata.php?recordid=1234, one of their records) then the problem doesn't lie in the knowledge that other records exist but in that the application isn't properly validating requests and/or performing checking security. If that is the case then you can solve the issue in the application layer by request forgery checking and/or privilege checks on every request (I'll not cover these in detail here as it is stepping away from the core topic of the question).

If there is a legitimate concern with using incrementing numbers, is the extra storage required for UUIDs really that expensive compared to the problem you are trying to combat?

You don't state which database engine you are using, but parts of the question strongly suggest mySQL. Other databases can handle UUIDs better by dealing with them as a native binary type, taking 16 bytes instead of 32 characters. It is a while since I've used mySQL, do modern versions support this or similar? That is still four times the size of an integer though.

You could translate the keys in the application layer by bit swapping: every time you send an ID client-side switch the bits around (swap bit 1 with bit 3, bit 4 with bit 0, ...) and translate back when receiving IDs before using them to query the database. This means you are obfuscating the IDs without altering the storage efficiency of the data but you are adding extra complication and potential bugs and inefficiency into your application(s). A seasoned hacker might see through this obfuscation, especially given a sizeable amount of data to analyse, but someone less determined will just see a random/arbitrary set of numbers. There are other such translations you could use: shift the number of 8 bits and fill in the bottom 8 with a simple hash of the original number perhaps.

Pushing the "scale and add some random bits to the bottom" idea into the database you could have a combined key that is the auto-increment value plus another integer or smallint whose value defaults to something arbitrary/random. In SQL Server you could do this in one column by using a user defined sequence to populate a BIGINT column, other DBs might offer something similar, though for many inserts there would be a performance consideration here. In fact even without the bigger type and randomness a sequence shared by all the increments in your data model may have the obfuscating effect you are looking for.

There are many imaginative ways you could make your keys more arbitrary, but they all add complexity somewhere in either the DB or the application so I'd go back to my initial response: do you really need to? If the need is real then I'd be inclined to bite the bullet and use UUIDs, type 4 UUIDs that are fully arbitrary rather the type 1s that include the MAC and so have some order, and if not I'd stick with increment integers for efficiency.


First, I would recommend you go get Ola Hallengren's backup scripts.  They are full featured and well documented, supporting exactly the type of operations you are looking to complete.  This saves you a lot of effort of writing your own scripts from scratch and the solution is recommended by many notables in the community.

Second, I would create SQL Agent jobs to call the backup stored procedures, with the following jobs:

Full backup job that runs weekly, creating full backups for all databases.  For argument's sake, this is at 2 AM Sunday.
Differential backup job that runs on all days other than the day of the full.  This would be 2 AM Monday-Saturday.
Log backup job that executes every 5 minutes.

This is a very basic and general schedule and you will probably need to tweak and adjust it based on your environment.  The largest area of adjustment will probably be how often you run your log backups.  Depending on the size, activity, the number of databases in FULL recovery mode, and the fact that this approach backs up your databases serially (one after the other), all log backups on your instance may not complete in 5 minutes.  There are several ways to handle this, such as increasing the window between log backups and creating different backup jobs for different databases (so you can have them execute in parallel).


select from database with special characters

Use the following query :

select * from outstanding where lcocode LIKE 'PN007\\\\00128'

Backslash is an escape prefix for both strings and LIKE patterns. So you need to double it once for LIKE, and again for string literal syntax.


Query to find Maximum friends not giving correct resuts

Your query is correct and works fine in other DBMS (SQL-Server, Postgres, Oracle, MySQL). In SQLite, it appears there is a bug, possibly due to the correlated subquery and/or the grouping in both the main and the sub query. Here is another way to write the query:

SELECT id, COUNT(*) AS count1
FROM Likes
GROUP BY id
HAVING COUNT(*) =
      ( SELECT COUNT(*) AS cnt
        FROM Likes
        GROUP BY id
        ORDER BY cnt DESC
          LIMIT 1
      ) ;

Tested in SQL-Fiddle


How do you load a data file into a specific field?

Since you are looking to INSERT an entire file into a single column. You need the LOAD_FILE function:
UPDATE jobs SET Log = LOAD_FILE('/root/copy.log')
WHERE TaskName = 'LouPrMgt004.Backup';

So that you can see just how LOAD_FILE function works, try this:
SET @mycnf = LOAD_FILE('/etc/my.cnf');
SELECT @mycnf;

You should be able to see your my.cnf loaded into a variable
Here is a sample run
mysql> SET @mycnf = LOAD_FILE('/etc/my.cnf');
Query OK, 0 rows affected (0.00 sec)

mysql> select @mycnf\G
*************************** 1. row ***************************
@mycnf: [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_password=1

key_buffer_size=1G
max_allowed_packet=128M

# These two values must be the same
tmp_table_size=16M
max_heap_table_size=16M

# Sort_buffer is too large, so try default to see if it helps
sort_buffer_size = 2M

read_buffer_size=1M
read_rnd_buffer_size=2M
myisam_sort_buffer_size=16M
max_length_for_sort_data=2048
max_sort_length=2048
long-query-time=5
skip-name-resolve
table_cache = 6144
open_files_limit = 32768
interactive_timeout=3600
wait_timeout=3600
thread_cache = 100
max_connections=1000
query_cache_size = 0
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
ft_min_word_len=2
group_concat_max_len=5000000
query_cache_size=8M
query_cache_limit=32M

#log-output=TABLE
#slow-query-log
#slow-query-log_file=slow-query.log
#expire-logs-days=7

server-id=180
#log-bin=mysql-bin
#log-slave-updates
#relay-log=relay-bin

innodb_file_per_table
innodb_log_file_size=128M
innodb_buffer_pool_size=512M
innodb_log_buffer_size=8M
innodb_flush_method=O_DIRECT

innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_io_capacity=5000
innodb_thread_concurrency=0

#
#       XtraDB Cluster Options
#

user=mysql
binlog_format=ROW
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://
wsrep_slave_threads=2
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2

wsrep_sst_method=xtrabackup
wsrep_cluster_name=oxygen_xtradb_cluster_test
wsrep_node_name=oxygen_xtradb_cluster_test_node180

[mysql.server]
user=mysql
#basedir=/var/lib

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

1 row in set (0.00 sec)

mysql>

Give it a Try !!!
Be careful with this because some have complained that it does not work

http://forums.mysql.com/read.php?20,218333,218333#msg-218333
http://bugs.mysql.com/bug.php?id=38403


MySQL User with Host set to a Range of IP Addresses

you can use the grant assign to a netmask like:

GRANT ALL PRIVILEGES ON database.* TO 'user'@'81.10.20.1/255.255.255.240';

It will give you access from 81.10.20.1 -> 81.10.20.14, just change the subnet part to fit the range you need.

You can see more info at mysql doc account names


Here you can find the explnation of this error by Paul Randal:
A little-known sign of impending doom: error 825

From SQL Server 2005 onwards, if you ever see an 823 or 824, SQL
Server has actually tried that I/O a total of 4 times before it
finally declares a lost cause and surfaces the high-severity I/O error
to the connection’s console, killing the connection into the
bargain. The idea behind this read-retry logic came from Exchange,
where adding the logic reduced the amount of immediate downtime that
customers experienced. While in concept this was something I agreed
with at the time, I didn’t agree with the way it was implemented.
If the I/O continues to fail, then the 823/824 is surfaced – that’s fine. But what if the I/O succeeds on one of the retries?
No high-severity error is raised, and the query completes, blissfully
unaware that anything untoward happened. However, something did go
badly wrong – the I/O subsystem failed to read 8KB of data correctly
until the read was attempted again. Basically, the I/O subsystem had a
problem, which luckily wasn’t fatal this time. And that’s what I
don’t like – the I/O subsystem went wrong but there are no flashing
lights and alarm bells that fire for the DBA, as with an 823 or 824.
If read-retry is required to get a read to complete, the only notification of this is a severity-10 informational message in the
error log – error 825.
Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens
database integrity and must be corrected. Complete a full database
consistency check (DBCC CHECKDB). This error can be caused by many
factors; for more information, see SQL Server Books Online.
What this message is really saying is that your I/O subsystem is going wrong and you must do something about it. And unless you’re
regularly scanning the error log looking for these, you’ll be
none-the-wiser.


Does leaving a connection open for a matter of seconds cause deadlocks?

I can only speak toward SQL Server.

A database connection itself is not going to cause deadlocks. If it did connection pooling would be useless.

How they are handling and managing transactions against the database can have an impact on deadlocks. There are plenty of scripts online that can be found on how to force a deadlock using SSMS, most are with two query windows that are separate sessions over one connection.


Postgres read from master after slave failure

Usually the replica server is used for HA (that's why everyone is talking about promoting it) and for read scaling.
From your question it seems you're interested in the later.
As you use the replica for read scaling, you can use any load balancer to spread the read load between the master and slave.
If the slave goes down, most load balancers will stop sending traffic to it.


Merge Only Events that Start immediately after the Previous Event without any Gap

It's a bit convoluted, but I think this gets the result you're after:

;WITH src AS
(
SELECT et.M_ID
    , et.Y_ID
    , et.T_ID
    , et.E_ID
    , et.BeginDate
    , Marker = CASE WHEN et.BeginDate <= DATEADD(DAY, 1, LAG(et.EndDate, 1, et.BeginDate) OVER (PARTITION BY et.M_ID
            , et.Y_ID
            , et.T_ID
            , et.E_ID
             ORDER BY et.BeginDate))
        THEN 1
        ELSE 0
        END
    , et.EndDate
FROM #EventTable et
)
, src2 AS
(
SELECT src.*
    , rn1 = ROW_NUMBER() OVER (PARTITION BY src.M_ID
            , src.Y_ID
            , src.T_ID
            , src.E_ID
            , src.Marker
             ORDER BY src.BeginDate)
    , rn2 = ROW_NUMBER() OVER (PARTITION BY src.M_ID
            , src.Y_ID
            , src.T_ID
            , src.E_ID
            , src.Marker
             ORDER BY src.BeginDate DESC)
FROM src
)
SELECT src2.M_ID
    , src2.Y_ID
    , src2.T_ID
    , src2.E_ID
    , StartDate = MIN(src2.BeginDate)
    , EndDate = MAX(src2.EndDate)
FROM src2
WHERE src2.rn1 = 1
    OR src2.rn2 = 1
GROUP BY src2.M_ID
    , src2.Y_ID
    , src2.T_ID
    , src2.E_ID
    , src2.Marker
ORDER BY src2.M_ID
    , src2.Y_ID
    , src2.T_ID
    , src2.E_ID
    , StartDate
    , EndDate;

Output:

╔════════╦══════╦══════╦══════╦════════════╦════════════╗
║  M_ID  ║ Y_ID ║ T_ID ║ E_ID ║ StartDate  ║  EndDate   ║
╠════════╬══════╬══════╬══════╬════════════╬════════════╣
║ 134560 ║    5 ║    8 ║    3 ║ 2017-04-01 ║ 2017-08-31 ║
║ 135709 ║    2 ║    7 ║    1 ║ 2017-01-01 ║ 2017-06-30 ║
║ 135709 ║    2 ║    7 ║    3 ║ 2017-01-01 ║ 2017-05-31 ║
║ 135709 ║    2 ║    7 ║    3 ║ 2017-07-01 ║ 2017-08-31 ║
║ 135709 ║    5 ║    8 ║    1 ║ 2017-02-01 ║ 2017-07-31 ║
╚════════╩══════╩══════╩══════╩════════════╩════════════╝

The code uses the LAG() aggregate to inspect the next logical row to see if the start date is prior to or adjacent-by-one-day to the end date in the current row.  It then uses the two ROW_NUMBER() aggregates to get the first-and-last rows of each group.


How to compare xmin and txid_current() after transactions ID wraparound?

You can strip the added epoch to match the value in xmin, i.e. extract the 4-byte integer from the bigint. Since xmin is type xid and not (signed!) integer, we compare the text representation instead:

SELECT * FROM test
WHERE  xmin::text = (txid_current() % (2^32)::bigint)::text;

Detailed explanation:

How to view tuples changed in a PostgreSQL transaction?


Basically, you need to test to see if the insert succeeded.  It will either succeed and everything will be inserted, or it fails and nothing is inserted.  Check @@ROWCOUNT like this:

create table SomeTable (someVale nvarchar(10));
create table SomeOtherTable (someVal nvarchar(11));

insert SomeOtherTable values
(N'1234567890'),
(N'12345678910');

select * from SomeOtherTable;

INSERT INTO SomeTable
SELECT * FROM SomeOtherTable;

if @@ROWCOUNT > 0
    DROP table SomeOtherTable;

select * from SomeOtherTable;

You could wrap it in a transaction if you want or use a GOTO with a label.  It all depends on how you want to terminate.

For a full list of options and considerations, see Erland Sommarskog's reference articles:

Error and Transaction Handling in SQL Server


Mysql shutting down during a mysqldump of a specific database

You will have to mysqldump around moments_production_wp

I have old posts to bypass databases and tables

Dec 16, 2011 : How do you mysqldump specific table(s)?
Feb 20, 2013 : Any option for mysqldump to ignore databases for backup?

Using the same concepts, here is how to mysqldump all except moments_production_wp

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DATABASES_TO_EXCLUDE="moments_production_wp"
EXCLUSION_LIST="'information_schema','mysql','performance_schema'"
for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done
SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYSQLDUMP_DATABASES="--databases"
for DB in `mysql -ANe"${SQLSTMT}"`
do
    MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done
MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > GoodDatabases.sql

After dumping all the other databases, mysqldump the one database

mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} moments_production_wp > OneDatabase.sql

It will mysqldump tables in alphabetical order. It should break near the bad table.

You can then mysqldump that database one table at a time

Let's say you know tables tb1 and tb2 makes mysqld crash

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DBTODUMP=moments_production_wp
SQL="SELECT table_name FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DBTODUMP}'"
SQL="${SQL} AND table_name NOT IN ('tb1','tb2')"
TBLIST=`mysql ${MYSQL_CONN} -AN -e"${SQL}"`
MYSQLDUMP_OPTIONS="--hex-blob --max-allowed-packet=1073741824"
for TB in `echo "${TBLIST}"`
do
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DBTODUMP} ${TB} > ${DBTODUMP}-${TB}.sql
done

This should help narrow the beam as to which table is causing the issue.

I do have one further thing to share. I have a 4-year-old post on the silent killer of mysqldumps : MySQL server has gone away obstructing import of large dumps. You will need to set your max_allowed_packet to 1G in my.cnf and restart mysqld. If you cannot change the max_allowed_packet at this time, you should mysqldump one row at a time using --skip-extended-insert to throttle the amount of data passing into your current MySQL Packet.


Are these two queries technically equal to each other?

No. But almost.

There are four things that joins do. I wrote about this at: JOIN simplification in SQL Server

Your "NOT IN" (which you should be careful of, regarding NULLs - try using NOT EXISTS) won't duplicate any rows. A LEFT JOIN can. But consider the use of Unique Indexes/Constraints/PKs, which can help the Query Optimizer treat these as identical.

On the whole, use NOT EXISTS rather than LEFT JOIN, because NOT EXISTS will get treated as an Anti Semi-Join, which is slightly better than what a LEFT JOIN / NOT NULL does. I show this at: Joins without JOIN

So... based on my assumption of what your queries were meant to be, the answer is:

Almost. (And how 'almost' depends on the metadata involved)


Error Msg 823, Level 24, State 2, Line 1

I have heard of this occurring during a restore of multiple databases, non-sequentially (i.e.: multiple restores occurring on different SPIDs at once), after a drive failure, on a hyper-V VM.

(I'm sorry to include all of those caveats, but that was the situation... and I'd hate to find out that any one factor changes the results.)

Sequential restores (only one at a time) seemed to have resolved the issue.


It seems to me that in a case like this you need a linking table that will allow for that many to many relationship.  Something like

Create Table tblBookWriter
(tblBookWriter_ID int
, Book_id --(FK to Book table-Book_Id)
, Writer_id --(FK to Writer table-Writer_Id)
);

With this you can add have a book with as many writers as it needs and a writer can have authored as many books as is needed.  Something like that should get it for you anyway.

If you do a web search for "database many to many linking table" you will see all kinds of results.  The first is a wiki link for a Junction Table


CASE is an expression (not a statement) and cannot be used for control-of-flow like that - not to call commands, not to return more than one column/value, not to be used as a command on its own.

It seems to me you can just use IF to raise the error when the dates are equal, otherwise run the inserts.

IF @dateA = @dateB
BEGIN
  raiseerror('dates equal',20,-1) with log;
END
ELSE -- maybe you don't need a batch-aborting, logging error level
BEGIN
  INSERT ...
END

You could also do it the other way. Run the inserts only if the dates are not equal, otherwise raise the error:

IF @dateA <> @dateB
BEGIN
  INSERT ...
END
ELSE
BEGIN
  raiserror ...
END

If you thought to use the error only for the purpose of getting out of running the inserts, then you can just remove everything from ELSE down, since the only way the inserts will run is when @dateA and @dateB are not equal:

IF @dateA <> @dateB
BEGIN
  INSERT ...
END

I have scaled way back on being pedantic about things like rows (vs. "records"), and columns (vs. "fields"), but the whole expression vs. statement thing is a very important distinction, for exactly this reason. See "Dirty Secrets of the CASE Expression."


innodb_log_file_size determines the size of the redo log. In short, this is a temporary storage zone on disk where data changes are buffered before being inserted in the actual table data files. It also stores temporary data that may be generated during the course of a transaction.

The first, most obvious advantage of this buffer is that it mitigates the problem of random writes, which is what would happen otherwise in a typical real-life situation. Writes in the redo log are done sequentially, which is quicker. Randomly located changes may be committed to the actual data tables at a later time, when, hopefully, the activity decreases.

The second advantage is it provides protection against data corruption in case of crash. Since a coherent version of the data (almost) always exists, either in the data table, or in the redo log, the risk of data corruption is lowered, since it should alwys be possible to restore the database to a coherent state. Other mechanisms exist on top of this one for the same purpose (eg. double-buffering)

As the manual puts it:

  Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. (...) The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. Larger log files also make crash recovery slower.

innodb_log_buffer_size defines the size of the memory buffer where data is stored before being flushed to the redo log.

The larger the better, but make sure to leave enough memory for other buffers. Also, I do not think there is a point in allocating more than the total redo log size (ie. innodb_log_file_size x innodb_log_files_in_group.

Books the size of a dictionary can be written about database tuning, this cannot be addressed here. This manual page is a good starting point. If you have identified a specific bottleneck, please come back with relevant information and I (we) will be happy to help you.


Entity Framework SaveChanges fails due to a trigger

Most likely there is a problem with the JOIN to sometable that is filtering out any rows from matching, hence the @biInbndEquip_ID variable never gets populated.

HOWEVER, even if you fix that, you still have a bigger problem to deal with: your trigger logic is written to handle a single row. If a multi-row INSERT is done, then the trigger will only ever grab one of the values that was inserted to place into the Message214Status table. Get rid of the @biInbndEquip_ID, @iCust_ID, etc local variables in the trigger, and rewrite it to be a simple INSERT...SELECT. For example:

INSERT INTO dbo.Message214Status (InbndEquip_ID, Cust_ID, ...)
  SELECT ins.[InboundEquipmentID], ins.[CustID], ...
  FROM   INSERTED ins
  INNER JOIN sometable c
          ON c.[join_column] = ins.[join_column];


Typically, 0x80070005 is an Access Denied error.  Perhaps the account Idera SQL Diagnostic Manager is using no longer has access to the Service Control Manager on the Windows nodes where the errors are occurring.


How to use a table when the user identifier can be one of two fields

When doing this sort of design, I think first about the "things", and then about the "relationships".
The things are:

the people
the credit cards and
the organisation.

The relationships are:

Who owns the organisation.
Who is a member of the organisation.
Which credit card is the owner using to pay the bill

You need an entity for each "thing".
Depending on whether it is a "to one" relationship or a "to many" relationship, you can either define the relationship within an entity or you will need a new entity for the relatonship.

Who owns the organisation: If only one owner, you can put the owner in the organsation entity, otherwise you would need a separate entity.
Who is a member of the organisation: You can have many members of an organisation, therefore you will need a separate entity.
Which credit card is the owner using to pay the bill: Only one credit card would be used therefore you can put the reference to the credit card card in the organisation entity.

So you would have four entities:
Person (a "Thing")
Credit Card (a "Thing")
Organisation (a "Thing", with two relationships)
Organisation_Member (a relationship)

Here are some example columns you could use for the entities

Person Columns:

person_id (primary key, identity)
name
email

Credit Card Columns

person_id
billing_id (start from 1, don't make it an identity)
+credit card information columns (I expect you would encrypt the data, so not store the actual card numbers as text)

(The combination of person_id and billing_id in the above forms the primary key)

Organisation Columns:

org_id
org name
person_id (of the (one) owner - defining one relationship mentioned)
billing_id (of the (responsible) owner - defining another relationship mentioned)

Organisation_Member:

org_id
person_id (of the member)

One more point about email addresses. If you want there to be a separate email address for the organisation, it can be included in the organisation table. I'm not sure I understand why you would need this. Perhaps my answer would be different if I understood what it is for.


SP returns positive RecordsAffected value even when TSQL statement rolled back

on the C# side, the dbResponse.RecordsAffected value is always incremented.

That's just a total of the row count messages returned from SQL Server.  These are sent as queries are run, before the transaction has been committed or rolled back.

  how can I get this SP to return the correct rows effected value?

Keep track of the row count in a variable, and return that to the client with an output parameter or a result set.

  Is it possible to reset this value in the CATCH statement?

As @Kin suggested, you can SET NOCOUNT ON and no rowcount messages will be sent to the client.  Then after you COMMIT you can turn NOCOUNT OFF and run a query to that generates the desired rowcount message. Perhaps something cheezy like:

set nocount on
--do stuff
declare @rows int = 25

set nocount off
select top (@rows) 1 a
into #bitbucket
from sys.objects o, sys.columns c


How to rewrite slow CTE construction to match speed of temp tables

Sometimes CTE has got the wrong estimation. The temp table is good at it.
So, the CTE uses those indexes because they think fewer rows are there. The reason for the slowness of the first one is RID Lookup. If you drop your indexes or add your output column as include on your index. It will faster.
There is an awesome blog post here.
I think there is no win between them. You should use them to depends on the situation. And try them both in the same situation. In this way you can see the costs.


How would I run DELETE queries on a Master Without Executing Same DELETEs on a Slave?

There is a much, much simpler way
A Replication Slave relies on the Master's binary logs so as to ship SQL from them into the Slave's local relay logs. Just tell the Master not to record the SQL
SQLSTMT="SET SQL_LOG_BIN=0;"
SQLSTMT="${SQLSTMT} DELETE FROM tablename WHERE column='our filters'"
mysql -u... -p... -Ae"${SQLSTMT}"

The key here is to disable binary logging in its session. The Slave won't know what hit the Master because the Master will execute the DELETE and not record the DELETE.
Give it a Try !!!


The problem explained

As per your comment, your shard key is the _id field of the document. This field is monotonically increasing, basically like an incremented integer.

Put simply, sharding works this way: documents are stored in chunks. Those chunks are spread over the cluster based on ranges of the shard key. Let's look at a simple example:

s1: chunks holding id 0-50
s2: chunks holding id 51-100
s3: chunks above 101-?

And here is where the problem occurs: there is one shard were all new documents go to. What happens then is that new chunks are created all the time (as chunks have fixed size). So not only are all writes focussed on one server, this server also has to do some overhead work. After a certain threshold is met, the cluster will start to move chunks to the other shards, which only will balance out disk space, though. In out example, it might look like this:

s1: chunks holding id 0-100
s2: chunks holding id 101-200
s3: chunks holding id 201-?

Obviously, all write operations will still go to s3.

What went wrong?

You chose the wrong shard key. Monotonically increasing shard keys lead to the problem explained above. Altough ObjectIds look like hash sums, but they aren't. They are monotonically increasing.

What can be done?

You need a better shard key. Since a shard key can not be changed after a collection is sharded, migrating to a new shard key is not a simple thing to do. However, there is a quite detailed explanation in mongoDB's ticket system. Basically, it works like this:

Remove all but one shard from the cluster
Wait for the chunk migrations to be finished
Shut down the remaining shard
Change the shard key through a mongos instance
Restart the last shard
Add the other shards to the cluster


Table Partitioning and updates to partition subset

due to partitioning limitation of 15,000 I will create a partition on
  say 10,000 values using ranges.

If by this you mean that you plan to create a partition function with 10000 ranges, then I strongly recommend against doing that. The limit of 15000 is not a goal. The product supports going over 1000 now but the engine support for that isn't great. You should expect to see query performance and other issues if you work with a table with so many partitions.

  If I was able to partition by Asset ID then I could simply swap the
  partition, but since I am partitioning by asset range it gets a bit
  more complicated.

Partitioning a table isn't something to take lightly, especially if you need to convert a table that you consider to be large to the new partition scheme. It is atypical to partition a table on an integer value like you described. Most of the time people partition by date or by a column that represents a date column. Here, it seems as if you are partitioning the table simply to allow switching out a partition, truncating it, and reinserting all of the data.

  What is a recommended approach to updating a subset of data in the
  partition? Say that I have an update for an asset in patition 10.

Issue a T-SQL UPDATE to change your data. If you know that all of the data to be updated resides in a single partition you can filter by the partition function of that function (using syntax similar to $PARTITION.{{partition_function}}(AssetID) = 10) to avoid an unnecessary DML request sort.

  Do I have to switch out the partition 10 to a new staging table and
  perform updates there and then switch it back in? What would the
  advantage of this be as compared to updating data directly?

No, you do not need to switch out a partition to a new table just to perform updates. However, there are some advantages in performing DML against single partitions of a properly partitioned table:

If you'd like to perform an operation that requires a table lock on the target, such as parallel inserts in SQL Server 2016, you can do so against many partitions concurrently if you switch the partitions out first into their own tables.
You can avoid blocking issues without needing partition level lock escalation.
The UPDATE operator in a query cannot run in parallel. However, if you switch out partitions then you can safely and easily issue many concurrent UPDATE queries at once.

It's really unclear what you hope to accomplish by partitioning your table. I recommend reading through some of the resources here and defining your goal to be a bit better. There's nothing wrong with partitioning a table to, in addition to other goals, make an ETL type of process more efficient. however, the partition scheme that you pick can have big impacts on maintenance and query performance against the table.


How to use COALESCE with multiple rows and without preceding comma?

This might be the cleaner approach you're after. Basically, check if the variable has been initialized yet. If it hasn't, set it to the empty string, and append the first city (no leading comma). If it has, then append a comma, then append the city.

DECLARE @col nvarchar(MAX);
SELECT @col = COALESCE(@col + ',', '') + city
  FROM dbo.tbl WHERE state = 'California';

Of course, that only works for populating a variable per state. If you are pulling the list for each state one at a time, there is a better solution in one shot:

SELECT [state], cities = STUFF((
    SELECT N', ' + city FROM dbo.tbl
    WHERE [state] = x.[state]
    FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
FROM dbo.tbl AS x
GROUP BY [state]
ORDER BY [state];

Results:

state       cities
----------  --------------------------------------
California  San Francisco, Los Angeles, Sacramento
Florida     Miami, Jacksonville

To order by city name within each state:

SELECT [state], cities = STUFF((
    SELECT N', ' + city FROM dbo.tbl
    WHERE [state] = x.[state]
    ORDER BY city
    FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
FROM dbo.tbl AS x
GROUP BY [state]
ORDER BY [state];

In Azure SQL Database or SQL Server 2017+, you can use the new STRING_AGG() function:

SELECT [state], cities = STRING_AGG(city, N', ')
  FROM dbo.tbl
  GROUP BY [state]
  ORDER BY [state];

And ordered by city name:

SELECT [state], cities = STRING_AGG(city, N', ')
                         WITHIN GROUP (ORDER BY city)
  FROM dbo.tbl
  GROUP BY [state]
  ORDER BY [state];


MySqlDump's DISABLE KEYS have no effect on import

You cannot rely on DISABLE KEYS; and ENABLE KEYS; for InnoDB because it is not implemented in the InnoDB Storage Engine. Running ALTER TABLE ... DISABLE KEYS; and ALTER TABLE ... ENABLE KEYS; were designed for MyISAM. As it says in the MySQL Documentation for ALTER TABLE:

  If you use ALTER TABLE on a MyISAM table, all nonunique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes.

  For MyISAM tables, key updating can be controlled explicitly. Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. Then use ALTER TABLE ... ENABLE KEYS to re-create missing indexes. MyISAM does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.

  While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.

No mention is ever made of InnoDB in context with ALTER TABLE ... DISABLE/ENABLE KEYS;

Even if you run ALTER TABLE ... DISABLE KEYS; against an InnoDB table, it generates a warning:

mysql> show create table mytimes\G
*************************** 1. row ***************************
       Table: mytimes
Create Table: CREATE TABLE `mytimes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `totalTime` int(11) NOT NULL,
  `totalTimeDesc` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table mytimes disable keys;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------+
| Level | Code | Message                                                     |
+-------+------+-------------------------------------------------------------+
| Note  | 1031 | Table storage engine for 'mytimes' doesn't have this option |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

That's why there is no affect. Please recall that @jynus mentioned the same thing in his answer in bulletpoint 7.

Keep in mind also that MyISAM keeps data and indexes in two separate files (.MYD for data, .MYI for indexes), so it would be trivial to disable and enable indexes. InnoDB keeps the PRIMARY KEY and row data in the same InnoDB pages (via the Clustered Index). Secondary indexes will carry the PRIMARY KEY as an attachment to every secondary index leaf entry. Since the data and indexes are intertwined via the Clustered Index, no one has, as of yet, attempted to implement DISABLE KEYS and ENABLE KEYS in InnoDB.


If there is a particular plan you want it to use, regardless of the statistics, then try using a plan guide for that query. It seems pointless to try to persuade the optimiser when there's a known behaviour you want to use. It's better to update your stats AFTER your ETL, not just to persuade your ETL to run well.


MySQL Workbench Synchronization False Positives

Probably a bug. Some of the change detection is not catching this specific case (or interpreting it wrongly). Best is to file a bug report (http://bugs.mysql.com).


How to determine if Oracle Java classes are being used

Doc https://docs.oracle.com/database/121/SQLRF/statements_4007.htm#SQLRF01107 for AUDIT statement says "Java schema objects (sources, classes, and resources) are considered the same as procedures for purposes of auditing SQL statements." So I assume that you can audit these Java objects with AUDIT statement (but I have not tested this).


Performance in replicating table between servers

if you want the records that have been changed you could use the OUTPUT Clause (Transact-SQL)

you can also see an example of this here

Regarding your update statement, you definitely need an index on the ID column in both tables. I would even consider creating it as clustered, but I don't know your tables structure and your application.

you could for example, use the output clause to save the changed records into an specific table(s) and replicate that/those table(s).


What benefit is there from enabling Query Store on msdb?

Microsoft enabling a feature does not mean it will be useful for everyone. For systems using some of the features can mean relying on information stored in MSDB.  In those cases Query Store can be useful.

Here are few articles about the usage and tuning  of MSDB database objects.

msdb Database from books online.

MSDB Performance Tuning by Geoff N. Hiten

The Importance of Maintenance on MSDB by Tim Radney where he mentioned following:

  Optimizing indexes in msdb is just as important as your user
  databases. Many times I have found clients who are optimizing user
  databases but not the system databases. Since the msdb database is
  heavily used by SQL Server Agent, Log Shipping, Service Broker, SSIS,
  backup and restore, and other processes, the indexes can get highly
  fragmented. Ensure that your index optimization jobs also include your
  system databases, or at least msdb. I’ve seen index optimizations free
  up several gigabytes of space from highly fragmented indexes within
  msdb.

I can see how query store can help in optimizing your indexing strategy and optimally querying/aggrgating/purging some of the information stored in MSDB.


How to trim a string by finding multiple words

Replacing multiple substrings is fairly easy to do with REPLACE commands inside of a CASE block.
I successfully tested the following command on MySQL 5.6:
select
    case
        when my_string like '% (RES)%' then replace(my_string, ' (RES)', '')
        when my_string like '% (R)%' then replace(my_string, ' (R)', '')
        else my_string
    end as my_string_without_characters
from
    sample_table;

Shameless plug: I tested this command across multiple databases and posted about it on my blog here: How to replace multiple substrings with SQL.


Imported database file size different with original database file

Well, you created new empty tables and inserted all of the data into those tables in one shot. This means that the new tables do not have any of the fragmentation and page splits that would have happened as the original database was populated much more gradually and probably was subjected to a lot of updates and deletes. Hence, the same data will fit on a potentially much smaller number of pages, since the data is much "cleaner."

You can use a tool like Red Gate SQL Data Compare, if it still supports SQL Server 2000. There are alternatives if you search, of course, and you could write your own scripts to do it if you're really budget-conscious, but please read this first. Any of these tools (again, that support 2000) can help ensure that all of the data made it. Assuming, of course, that the source data hasn't changed substantially since you moved the data.

In all honesty, though, I would much rather perform a backup and restore than rely on all this stuff - even if it means I have some wasted space in the copy.


How can I configure SQL Server Aliases on a SQL Server Client Machine

Try using cliconfg.exe instead.  This is shipped with Windows.

c:\windows\system32\cliconfg.exe - 64Bit aliases
c:\windows\syswow64\cliconfg.exe - 32Bit aliases


Performance first low, then high after adding index, then keep high after drop index

Most likely the statistics on the field were out of date. Adding the index created/updated statistics with a full table scan. Or creating the index pulled all the data you need into memory, like any scan would.

Adding an index will only perform better if it is being used. Add the index and then check the query plan. If the stats are up to date and the query optimizer elects to use it there is a big chance it will improve performance. Removing statistics has no use as it will result most likely in a suboptimal query plan.

More info on statistics https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/engine-independent-table-statistics/


Syntax on Changing return String by Concatenation?

It looks like you're expecting the integer to get implicitly converted to string. What actually happens is the other way around - the string gets implicitly converted to integer.
The simplest version of what you want is:
Select '$' + Cast(dbo.fn_TotalBill('IBM') As Varchar)

But:

You shouldn't use Varchar without a length, it's bad practice. In this case maybe Varchar(16) would do?

You're missing cents. Normal practice would be to use Decimal(16, 2) to store it or perhaps even smaller. Also as per the other answer some people prefer the Money data type because of additional decimal places for rounding purposes that might be useful if compounding interest was involved.

T-SQL isn't going to do proper regional formatting for you (though it turns out 2012 added a way in the other answer).

But above all this generally, and almost certainly, isn't/shouldn't be done in T-SQL. You should always pass raw numerical values to the caller and let them do regional currency conversion for you in the presentation layer. That's where it belongs.
The only exception I've seen is when I've included the figure in an internal error message, for example to give me a figure where the balance is wrong for debugging purposes and this isn't going to flow through to the caller.


How to store hierarchical dimension for timeseries data

SQL Server has a hierarchyid type defined to deal with this kind of system.

Consider this example:

IF OBJECT_ID(N'dbo.Categories', N'U') IS NOT NULL
DROP TABLE dbo.Categories;
GO
CREATE TABLE dbo.Categories
(
    CatID int NOT NULL
        CONSTRAINT PK_Categories
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , hid hierarchyid NOT NULL
    , CategoryName varchar(30) NOT NULL
);

INSERT INTO dbo.Categories (hid, CategoryName)
VALUES ('/', 'Root')
    , ('/1/', 'A')
    , ('/1/2/', 'B')
    , ('/1/2/3/', 'C')
    , ('/1/2/4/', 'D')
    , ('/1/2/5/', 'E');

A sample query against the table:

SELECT c.CatID
    , Hierarchy = c.hid.ToString()
    , Parent = c.hid.GetAncestor(1).ToString()
    , c.CategoryName
    , ParentCategories = STUFF((
        SELECT ', ' + c1.CategoryName
        FROM dbo.Categories c1
        WHERE c.hid.IsDescendantOf(c1.hid) = CONVERT(bit, 1)
            AND c1.hid <> c.hid
            AND c1.CategoryName <> 'Root'
        FOR XML PATH ('')), 1, 2, '')
FROM dbo.Categories c;

The results:

╔═══════╦═══════════╦════════╦══════════════╦══════════════════╗
║ CatID ║ Hierarchy ║ Parent ║ CategoryName ║ ParentCategories ║
╠═══════╬═══════════╬════════╬══════════════╬══════════════════╣
║     1 ║ /         ║ NULL   ║ Root         ║ NULL             ║
║     2 ║ /1/       ║ /      ║ A            ║ NULL             ║
║     3 ║ /1/2/     ║ /1/    ║ B            ║ A                ║
║     4 ║ /1/2/3/   ║ /1/2/  ║ C            ║ A, B             ║
║     5 ║ /1/2/4/   ║ /1/2/  ║ D            ║ A, B             ║
║     6 ║ /1/2/5/   ║ /1/2/  ║ E            ║ A, B             ║
╚═══════╩═══════════╩════════╩══════════════╩══════════════════╝

To implement this into your system, you'd reference the above table in your table of searches.  Something like:

CREATE TABLE dbo.Searches (
    id int NOT NULL
        CONSTRAINT PK_Searches
        PRIMARY KEY
        IDENTITY(1,1)
    , searchString nvarchar(100)
    , timestamp datetimeoffset
        CONSTRAINT DF_Sarches_timestamp
        DEFAULT (SYSDATETIMEOFFSET())
    , CatID int NOT NULL
        CONSTRAINT FK_Searches_Category
        FOREIGN KEY
        REFERENCES dbo.Categories (CatID)
);

To insert a search in Category C:

INSERT INTO dbo.Searches (searchString, CatID)
VALUES ('This is a search', 4);

Query:

SELECT s.searchString
    , s.timestamp
    , Category = c.CategoryName
    , CategoryParents = (
        SELECT '/' + c1.CategoryName
        FROM dbo.Categories c1
        WHERE c.hid.IsDescendantOf(c1.hid) = CONVERT(bit, 1)
            AND c1.CategoryName <> 'Root'
            AND c1.hid <> c.hid
        FOR XML PATH (''))
FROM dbo.Searches s
    INNER JOIN dbo.Categories c ON s.CatID = c.CatID;

Results:

╔══════════════════╦══════════╦═════════════════╦════════════════════════════════════╗
║   searchString   ║ Category ║ CategoryParents ║             timestamp              ║
╠══════════════════╬══════════╬═════════════════╬════════════════════════════════════╣
║ This is a search ║ C        ║ /A/B            ║ 2018-03-08 15:29:25.2267243 -06:00 ║
╚══════════════════╩══════════╩═════════════════╩════════════════════════════════════╝

If you subsequently want to reparent C, and retain the old version of C, you do this:

INSERT INTO dbo.Categories (hid, CategoryName)
VALUES ('/1/2/', 'C');

Then, when you insert into the newly re-parented category, like this:

INSERT INTO dbo.Searches (searchString, CatID)
VALUES ('This is a search, but with a different parent', 7);

You get:

SELECT s.searchString
    , s.timestamp
    , Category = c.CategoryName
    , CategoryParents = (
        SELECT '/' + c1.CategoryName
        FROM dbo.Categories c1
        WHERE c.hid.IsDescendantOf(c1.hid) = CONVERT(bit, 1)
            AND c1.CategoryName <> 'Root'
            AND c1.hid <> c.hid
        FOR XML PATH (''))
FROM dbo.Searches s
    INNER JOIN dbo.Categories c ON s.CatID = c.CatID;

Results:

╔═══════════════════════════════════════════════╦══════════╦═════════════════╦════════════════════════════════════╗
║                 searchString                  ║ Category ║ CategoryParents ║             timestamp              ║
╠═══════════════════════════════════════════════╬══════════╬═════════════════╬════════════════════════════════════╣
║ This is a search                              ║ C        ║ /A/B/C          ║ 2018-03-08 15:29:25.2267243 -06:00 ║
║ This is a search, but with a different parent ║ C        ║ /A              ║ 2018-03-08 15:29:25.2546739 -06:00 ║
╚═══════════════════════════════════════════════╩══════════╩═════════════════╩════════════════════════════════════╝


What relationship type to use for this messaging system?

Assuming you're not trying to keep track of threads of messages, I.E. you send a message to N people, and they can't reply:

1) user needs no references to other tables
2) a message should have an ID (obviously) and a reference to the user who sent it originally
3) you need a table that connects a single message and its recipients (users) that looks like this: message_recipients{user_id, message_id} where the primary key is the combination of the two fields. This ensures that one message can have many recipients, and that users can be the recipients of many different messages.

  One user can send a message to one or many users

This is accomplished via the message_recipients table

  Messages can be sent only to registered users (i.e. to an existing 'idUser' record)

You'll need to handle this in your application logic as well, but if you add (as you should) a foreign key and (depending on your DBMS) a NOT NULL restriction on each of the columns in message_recipients table, it will be possible to create a record in message_recipients without the recipient existing.

  There should be tracking of sent messages (who sent the message and to whom)

This data model supports that. Example:

Find the name of all recipients of messages sent by user Mike Johnson

(edited to give better aliases to the two different uses of the user table)

SELECT recipient.firstname, recipient.lastname
FROM user recipient
JOIN message_recipient mr ON recipient.id = mr.user_id
JOIN message m ON m.id = mr.message_id
JOIN user sender ON m.user_id = sender.id
WHERE sender.firstname = 'Mike' and sender.lastname = 'Johnson'


Why do I get The transaction log for database 'X' is full due to 'ACTIVE_TRANSACTION' w/ Simple Recovery and auto grow log?

When I see this error it's usually because there was a long running transaction that has not been committed yet. Note that the size of the long running transaction isn't necessarily the problem. If the transaction changes any amount of data it will prevent the transaction log from clearing past that VLF. So if your transaction log is 1 GB and you do activity in the database that requires more than 1 GB of transaction log since your oldest active transaction you can run into an issue.
For a simple demo I have a database with a recovery model of SIMPLE and a log limited to 100 MB. In one session I insert and delete data in a loop:
create table dbo.X_DUMMY_TABLE (COL VARCHAR(4000));

SET NOCOUNT ON;

-- truncate and insert data in a loop
WHILE 1 = 1
BEGIN
    TRUNCATE TABLE X_DUMMY_TABLE

    BEGIN TRANSACTION
    INSERT INTO dbo.X_DUMMY_TABLE
    SELECT REPLICATE('Z', 4000)
    FROM dbo.getNums(100);
    COMMIT TRANSACTION;

    CHECKPOINT;
END;

With a recovery model of SIMPLE this doesn't cause any issues. Using DBCC SQLPERF(LOGSPACE) I can see that the log is between 1 - 7% full. Note that the transaction log file itself stays at 100 MB.
Now I'll start a transaction in another session that inserts just a single row. I won't commit the transaction:
create table dbo.X_DUMMY_TABLE_2 (COL VARCHAR(4000))

BEGIN TRANSACTION

INSERT INTO dbo.X_DUMMY_TABLE_2
SELECT 'Z';
-- note the lack of a COMMIT

After about 3 seconds the first session throws this error:

Msg 9002, Level 17, State 4, Line 11
The transaction log for database 'SE_DB3' is full due to 'ACTIVE_TRANSACTION'.


I think I understand what you are getting at. Could you try the following query:

SELECT startshift, endshift, starttime, endtime, `NAME`
from shifts
JOIN people on shifts.PPLID=people.id
INNER JOIN shift_breaks on shifts.PPLID=BREAKID
    and (date(startshift) = date(starttime))
    and (date(startshift) = date(endtime))
where PPLID="0"
ORDER BY STARTSHIFT

This ensures that your join between shifts and shift_breaks are on Person ID AND the corresponding date.

I tested this in a database locally and it seemed to give the desired result.

This approach would have an issue if the start of the shift and end of the shift were on DIFFERENT dates (i.e.: overnight swing shift.)

Overnight shift wont work because we are comparing the DATE of the break begin/end to the DATE of the shift start. To handle spanning dates, you may need a SHIFT table with SHIFT ID, SHIFTSTART (datetime), SHIFTEND (datetime)... then Breaks would have a FK relationship to a SHIFT and EMPLOYEE. Shifts would have an FK relationship with EMPLOYEE. Would then join on IDs.

There are a few ways to restrict the records to a date range within the where clause. You can add where PPLID="0" and date(startdate) = '2017-03-30' for a single day or BETWEEN for a range (between '2012-03-11 00:00:00' and '2012-05-11 23:59:00').


How to populate another column with a sequence value generated for an identity column?

If you have a condition that specifies 1005 whether root_id has to get the same value as unique_id, you could use a TRIGGER. Something like this:

CREATE OR REPLACE TRIGGER root_unique_trg
    BEFORE INSERT
    ON UNIQUE_PHYSICIAN
    FOR EACH ROW
    WHEN (NEW.is_root_phys = 1)
BEGIN
    :NEW.root_id := :NEW.unique_id ;
END ;

Tested in SQLfiddle


To add another functionality for -v... If you're trying to add the quotation, add it in the command line:

psql -v action="'drop'"

and this will run the code for:

select * where :action;

The same as

select * where 'drop';


I gather that Oracle XE only gives you a single database

Correct - as long as you "speak" Oracle-ese.
The Container Database is the only "real" Oracle Database instance that you have.
The "Pluggable Databases" within it are essentially "Tablespaces on Steroids", but with much better security that now guarantees the isolation of each one from every other.
For most normal operations, you should ignore the Container Database completely.
Just use the/a Pluggable Database inside it.

How do I create and use a schema, or whatever is required to contain a collection of tables?
CREATE USER fred IDENTIFIED BY '********';

Connect to the Pluggable Database and your statement will work exactly as you expect it to.


Short answer: your workaround is fine, other than that: no.
Long answer:
It is not possible to create a user for a non existing domain, though the error message is somewhat strange:
CREATE USER [INVALIDNAME\SOMEUSER] FOR LOGIN [VALIDNAME\SOMEUSER] WITH DEFAULT_SCHEMA=[dbo]
GO
Msg 15006, Level 16, State 1, Line 5
'INVALIDNAME\SOMEUSER' is not a valid name because it contains invalid characters.

Running the same with "VALIDNAME" domain on both user and login side
works fine.

even if it would work, it would create a bunch of other issues for you (SID of INVALIDNAME\SOMEUSER not valid, mapped to wrong login, etc)

Your Workaround
With your workaround, on the other hand, it will be okay as the users are not touched when you restore the database on any server.
Another Workaround
Create a staging instance in the same domain as server B is, create all logins you need and create a database that contains all the users mapped to the logins you need.
Then, restore both, the master and your database, in a new instance running outside your domain.
I haven't tested this, but I am quite sure it should work.


Get multiple columns from a select subquery

Assuming combination product_special.id, product_special.priority is unique

 SELECT p.*, special_price,special_date
 FROM product p
 LEFT JOIN
 (
     SELECT ps.id, ps.price as special_price, ps.`date` as special_date
     FROM product_special ps
     INNER JOIN
     (
       SELECT id, MIN(priority) as min_priority
       FROM product_special
       GROUP BY id
     ) ps2
     ON (ps2.id = ps.id)
 )a ON (a.id=p.id)


Last accessed timestamp of a Netezza table?

You could setup a history database (using nzhistcreatedb) which acts as a detailed access log and then consult the views and tables that it provides (e.g. $v_hist_table_access_stats).

If you haven't already got this in place then I'm not aware of any way to find out who accessed a table unless you trawl through the logs in /nz/kit/log/postgres/.


How to measure Key Lookups per Second on SQL Server

I don't know of a fully accurate and reliable way to track this.

One way to get at least something potentially useful is to keep snapshots of sys.dm_db_index_usage_stats, specifically the user_lookups column for index_id 0 (RID Lookup) and 1 (Key Lookup). This DMV can be reset for many reasons, including database or instance restarts. Rebuilding (but not reorganizing) and index also clears the related DMV entry on SQL Server 2012 and later, which can make things more difficult. You will need to capture information fairly regularly and use a heuristic to decide if the DMV reset between captures.

The index usage stats DMV also only returns a count of the number of times a plan containing a Lookup is executed. A plan that contains a single Key Lookup will increment the counter by 1, regardless of the number of lookups actually performed. It will also increment even if the Lookup is not executed at all.

The sys.dm_db_index_operational_stats DMV records the number of singleton lookups actually performed, but does not distinguish between singleton seeks on the index directly, and those that result from a Key or RID Lookup, so it is not useful for your purpose.

Singleton seeks are another name for Probe Scans as reported by the Access Methods Object. There is no way to distinguish between a 'normal' singleton seek on a unique index, and a singleton seek resulting from a Lookup. This means the Access Methods Probe Scans counter is not useful to you. The AM counters are very noisy anyway, and there's no way to correlate the counters with a particular index.


Oracle ODBC connection failure (using oracle's stock ODBC drivers)

I have not used Visual Studion in the last 7 years and I have never ever used SSIS, but this is your 2nd frustrated post about this in 2 weeks, so here we go:

Oracle ODBC driver installation BEGIN

Download 32-bit 18.3 Oracle instant client (instantclient-basic-nt-18.3.0.0.0dbru.zip) + ODBC (instantclient-odbc-nt-18.3.0.0.0dbru.zip), extract it (C:\instantclient_18_3_32).

Actual ODBC driver installation, takes about 1 second:

C:\instantclient_18_3_32>odbc_install.exe
Oracle ODBC Driver is installed successfully.

C:\instantclient_18_3_32>

Set up tnsnames.ora in C:\instantclient_18_3_32\network\admin, set TNS_ADMIN to this directory in system variables, create DSN with C:\Windows\SysWOW64\odbcad32.exe, test it.

Oracle ODBC driver installation END

Create a new BI/Integration Services project, add a new ODBC connection, select DSN from list, enter user + password, test:

There we are. Close VS, add C:\instantclient_18_3_32 to PATH in System Variables.

Repeat the test, and:

Guess it's time to start learning Mandarin Chinese.


Can graph databases be easily distributed?

even that the way to distribute data in graph databases is not that easy as in key-value stores, where the keys are distributed by ranges, there are techniques to distribute a graph.

InfiniteGraph (made by Objectivity) for instance is a highly distributed graph database. It uses a P2P technology to increase read and write performance by growing the hardware. It allows the user to cluster a certain part of the graph or a certain kind of information as its needed and to distribute it over some machines as its needed too.

As I heard this process of clustering/distribution will be eased for the user in release 3.0. A parallel query mechanism is also planned, that allows to run one query concurrently on the machines.


Ensure you've set the max server memory (mb) and min server memory (mb) options via sys.sp_configure appropriately for your server.  See my question to determine what those numbers should be.

Check Page Life Expectancy to see if your buffer pool is too small.  Depending on the size of your buffer pool, a commonly mentioned number is 300 seconds (5 minutes), but this will vary depending on your needs.  This code should work well enough:

DECLARE @Version NVARCHAR(255);
DECLARE @VersionINT INT;
SET @Version = CONVERT(NVARCHAR(255),SERVERPROPERTY('ProductVersion'));
SET @VersionINT = CONVERT(INT, SUBSTRING(@Version,1 ,CHARINDEX('.',@Version)-1));
DECLARE @cmd NVARCHAR(4000);
SET @cmd = '';
IF @VersionINT >= 9
BEGIN
    SET @cmd =
'SET NOCOUNT ON;

DECLARE @SecondsSinceRestart INT;
DECLARE @cpu_count INT;
DECLARE @physical_memory_kb INT;
DECLARE @committed_target_kb INT;
DECLARE @committed_kb INT;

DECLARE @BootTime DATETIME;
DECLARE @output TABLE
(
    RowNum INT NOT NULL IDENTITY(1,1)
    , [output] VARCHAR(1000) NULL
);
INSERT INTO @output ([output])
EXEC sys.xp_cmdshell @command = ''wmic os get lastbootuptime'';

;WITH c AS (
    SELECT Year = SUBSTRING(o.output, 1, 4)
        , Month = SUBSTRING(o.output, 5, 2)
        , Day = SUBSTRING(o.output, 7, 2)
        , Hour = SUBSTRING(o.output, 9, 2)
        , Minute = SUBSTRING(o.output, 11, 2)
        , Second = SUBSTRING(o.output, 13, 2)
    FROM @output o
    WHERE o.RowNum = 2
)
SELECT @BootTime = CONVERT(DATETIME, c.Year + ''-'' + c.Month + ''-'' + c.Day + ''T'' + c.Hour + '':'' + c.Minute + '':'' + c.Second, 126)
FROM c;

SET @SecondsSinceRestart = DATEDIFF(SECOND, @BootTime, GETDATE());

';
IF @VersionINT = 11
SET @cmd = @cmd + '
SELECT @cpu_count = cpu_count
        , @physical_memory_kb = physical_memory_kb
        , @committed_target_kb = committed_target_kb
        , @committed_kb = committed_kb
FROM sys.dm_os_sys_info dosi
'
ELSE
SET @cmd = @cmd + '
SELECT @cpu_count = cpu_count
        , @physical_memory_kb = physical_memory_in_bytes / 1024
        , @committed_target_kb = bpool_commit_target * 8
        , @committed_kb = bpool_committed * 8
FROM sys.dm_os_sys_info dosi
';

SET @cmd = @cmd + '
;WITH c AS (
    SELECT ServerName = @@SERVERNAME
        , pc.object_name
        , pc.counter_name
        , pc.instance_name
        , pc.cntr_value
        , BootTime = @BootTime
        , SecondsSinceRestart = @SecondsSinceRestart
        , cpu_count = @cpu_count
        , physical_memory_kb = @physical_memory_kb
        , committed_target_kb = @committed_target_kb
        , committed_kb = @committed_kb
    FROM sys.dm_os_performance_counters pc
    WHERE pc.object_name = ''MSSQL$'' + CONVERT(VARCHAR(255), SERVERPROPERTY(''InstanceName'')) + '':Buffer Node                                                                                ''
    AND pc.counter_name = ''Page life expectancy                                                                                ''
    AND pc.instance_name IS NOT NULL
)
SELECT RunNum = 7
    , c.ServerName
    , c.counter_name
    , c.object_name
    , c.instance_name
    , c.cntr_value
    , c.SecondsSinceRestart
    , c.BootTime
    , Ratio = c.cntr_value / CONVERT(DECIMAL(10,0), c.SecondsSinceRestart)
    , cpu_count
    , physical_memory_kb
    , committed_target_kb
    , committed_kb
    , ServerVersion = SERVERPROPERTY(''ProductVersion'')
FROM c;';
    EXEC sys.sp_executesql @cmd;
END

You can see what objects are occupying memory using the code shown in a recent blog post I wrote over at SQLServerScience.com.


SELECT *
FROM my_table a
JOIN my_table b ON a.my_column < b.my_column


How can I restore a MySQL database after formatting my hard drive

After putting the data files, remove ib_logfile0 and ib_logfile1. Dont delete ibdata1 file. And then restart MySQL service.

Before deleting take the backup of ib_logfiles and then delete.

Just give a try in this way.


MySql - selecting into file and re-loading it with special characters

I would suggest remove all those REPLACE(text,',','****') stuff, and use FIELDS ENCLOSED BY...:

SELECT
  id,
  status_twitter_id,
  twitter_id,
  creator_twitter_identity_id,
  text,
  in_reply_to_status_id,
  in_reply_to_user_id,
  urls,
  hashtags,
  created_at,
  updated_at,
  sent_at,
  user_mentions
FROM
  twitter_statuses
into outfile '/tmp/twitter_statuses.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
;

You then LOAD DATA INFILE like this:

LOAD DATA LOCAL INFILE '/srv/$SPLIT_FOLDER/$f'
INTO TABLE my_db.twitter_statuses_new
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
...

This should do the work.


You can use ORDER BY with LIMIT 1 for this:

SELECT t.*                           -- the columns you want
FROM table_name AS t
WHERE t.column_name <= 95            -- your parameter
ORDER BY column_name DESC
LIMIT 1 ;


How to reverse-engineer SQL statements issued by a legacy application

You can setup a test database and trace the application activity using the SQL Server profiler. By tracing the event "Security Audit\Audit Schema Object Access Event" you can effectively trace all the SQL the client is sending to the server.  But that will not necessarily help you in discover the application logic.


Can SQL Server WebAdmin degrade the performance of my site?

Any application that runs queries against a SQL Server (or any database engine) instance will decrease the performance because it's extra load the server has to deal with. I assume the warning is just to cover themselves, legally.

What it comes down to is the scripts you run, and how much activity the application generates on its own. There may be options to disable the latter, which is usually a good idea when working against a production instance.

I'm not familiar with the particular product you mentioned (Azure?), so for example from Management Studio, I always disable IntelliSense by default on a production management box, because this runs queries behind the scenes to do its work. Activity Monitor is also a big consumer of resources, and sometimes people leave that tab open and forget about it.

So again, their warning is a standard thing; I would probably just ignore it.


create more logs in Mysql, pros and cons?

The mysqld process will rotate to the next log by itself when the current binary log reaches 1G (default is 1G, but you can change that by setting max_binlog_size).

Unless you set the expire_logs_days option (By default, it is 0), no logs will disappear.

Keep in mind that restarting mysql also causes log rotations.

If you want to delete logs manually, DO NOT DELETE THEM IN THE OS.

Please use this command : PURGE BINARY LOGS TO 'mysql-bin.000200';

All logs before mysql-bin.000200 will be deleted.

You could also delete logs by timestamp: PURGE BINARY LOGS BEFORE '2012-04-19 00:00:00';

There are no other drawbacks other than wasted diskspace, especiall if the logs have outlived their usefulness.

Storage Engine usage in independent of Binary Logging. DDL for MyISAM and InnoDB can both recorded in the binary logs.


Sync tables from Host1 to Host2 with over 50 million rows?

Master-Slave (and various other forms of replication) provide keeping two (or more) hosts in sync continually.  No ETL process needed.

Splitting the client processes and MySQL into separate servers is a good first step.

Putting different customers on different "shards" provides 'infinite' scalability in that dimension.  You would need some form of proxy or home-grown directing to get from a client to the necessary server.

Ingesting 60K rows is pretty trivial unless, of course, the data format is always something different.  At that point, I might contract a programmer who knows Perl (or other tool) to crank out ingestion scripts.  (Years ago, I did such with news feeds.)

Does 50M rows at 60K per customer mean you have 1000 customers?

I would suggest you use sharding do deal with excessive write traffic and disk space.  And use Slaves for excessive read traffic.

It is a never-ending pain to try to perfect "sync" scripts.


The closest match to what you're looking for seems to be the SQL Server Audit feature, which was added in SQL Server 2008 as a replacement/successor for SQL Trace.

I recommend following the link to read up on it, since its setup/use is too big a topic to describe here in any meaningful way.


Snapshot for transactional replication taking too long for large database

Yes, initializing a large database through a snapshot can be very slow and time consuming.  If it is appropriate to your case, you should start by restoring a copy of the database to the target machine.

In setting up the subscription choose "allow initialization from backup files".

Because of limitations in the user interface for setting up replication, you will need to create a script instead of relying on the UI tools.  There is a blog post on this at:

http://blogs.msdn.com/b/repltalk/archive/2010/03/16/deep-dive-on-initialize-from-backup-for-transactional-replication.aspx

The post from msdn includes the code sample:

exec sp_addsubscription @publication = N'Repl2000', …..
    @sync_type = N'initialize with backup', @backupdevicetype='Disk',
    @backupdevicename='C:\Repl2000_RestoreThis.bak'--the last backup used to restore on the subscriber
go
exec sp_addpushsubscription_agent …….
go

The article also includes some troubleshooting tips.


Check what virtual NIC the VMs are using? if you are using E1000 NIC, change it to the VMXNET3.  This resolved my issue when doing large transfers using VMWare distributed switches.


MariaDB 5.2 is really old.  Upgrade.
MariaDB 10.2 (and MySQL 8.0) have RANK and other "windowing functions" to assist with your query.
Setting @variables inside SELECTs should not be trusted.  Rewrite the query to avoid them.
Updating everyone's rank all the time will be a performance drag when you have a lot of users.


Is it possible to set SQLite DB on server and connect to it in an Android app?

No, it's not possible. SQLite is an embedded database engine, which means it runs as part of your program and uses a file (usually) in the application sandbox on Android (so this is not part of your source code).

  SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files.

It's probably possible to have an application run on a server which is able to function as a backend for your app and stores its data in a SQLite file, but I'd rather choose a database which is intended for this kind of purposes like PostgreSQL or MySQL. You can talk SQL to those databases too. Of course, in a production-grade application you'll need an application as a back end, not just a database, which is able to authenticate users, validate business logic etc.


Oracle 19c GI Install on OL7 : ASM disks stamped via AsmLib not getting discovered via GridSetup.sh

Execute the below command to change owner (from root to oracle) on the given directory:

chown -R oracle:oinstall  /u01/app/19.0.0/grid/log

and run the installer again. It worked for me.


row locking within ACID transaction innodb

According to Page 418 Paragraph 3 of MySQL 5.0 Certification Study Guide

the following commands can break a transaction

ALTER TABLE
BEGIN
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
LOCK TABLES
UNLOCK TABLES
SET AUTOCOMMIT = 1
START TRANSACTION

As long as you do not any of these commands inside the transaction, the transaction should be stable. In fact, SELECT FOR UPDATE is so strong that row locks invade the indexes as well as the data. It could even set off possible InnoDB deadlocks. I once wrote about that in this post : select for update gives error on indexed column
As long as you keep transactions short and sweet, SELECT FOR UPDATE should not be a problem. If you want to protect your transaction from intermittent or unexpected deadlocks, MySQL 5.6 now has the following:

START TRANSACTION READ WRITE;
START TRANSACTION READ ONLY;

You should start the transaction with options like these. Why ?
According to that MySQL Documentation:

The READ WRITE and READ ONLY modifiers set the transaction access mode. They permit or prohibit changes to tables used in the transaction. The READ ONLY restriction prevents the transaction from modifying or locking both transactional and nontransactional tables that are visible to other transactions; the transaction can still modify or lock temporary tables. These modifiers are available as of MySQL 5.6.5.

Your last comment was

so can i lock rows within a transaction another way other then for update

You can also run SELECT ... LOCK IN SHARE MODE. The Documentation says

SELECT ... LOCK IN SHARE MODE sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.
For index records the search encounters, SELECT ... FOR UPDATE locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... LOCK IN SHARE MODE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.)

Although possible, keep transactions short. Otherwise, you can increase the likelihood of deadlocks.
UPDATE 2014-02-13 21:57 EST
To clear up any misconceptions, please note this from the MySQL Documentation:

All locks set by LOCK IN SHARE MODE and FOR UPDATE queries are released when the transaction is committed or rolled back.

Therefore, if you execute SELECT ... LOCK IN SHARE MODE inside a transaction, the rows stay locked until the transaction commits or rolls back.


DB2 shouldn't have issues at all with storage of large files. Some of how you store them will depend on what exactly you are storing. Are you storing XML files? Large text files? Word documents or PDFs? Video? Audio?

You have several options available to you. Graphic strings, binary strings, your large objects (CLOB, BLOB, and DBCLOB), and just plain XML.

If you are using just character data I would side with a CLOB. But check your sizes, if you are consistently lower than 32K it might just be better to use a VARCHAR. (Reason being is that large objects in DB2 are stored outside the table by default (unless you use the INLINE option, but even then it only works to certain sizes of LOBs), and they are also not cached in bufferpools. DB2 always retreives LOB's straight from disk (unless they are INLINE'd as mentioned above). If you are storing XML documents, you might want to look into the XML type as DB2 has a more optimal way to store and retrieve XML. You can set up special XML indexes on XML types too. And you can query the XML using SQL/XML or XQuery using XPath.

If you are storing binary objects, then I'd go with BLOB.

I think you may be right about the compression piece. (Although it wouldn't hurt to test it. There are about four or five different compression levels in DB2 and I know I can do at least backup compression with 9.7 Express-C).

Here is a 10.1 Edition comparison chart. And here is a article on the different editions and what they all offer.

Also take a look at space requirements for tables to help you better pick what you need.


Unique Identifier with Extra Characters Still Matching in Select

The implicit conversion also works if the value is enclosed in curly brackets {...}.

If you add those in the query the implicit conversion will fail if the original value is too long because the last } ends up in the wrong place.

select *
from some_table
where uuid = '{'+'7DA26ECB-D599-4469-91D4-F9136EC0B4E8'+'}'

If you try the convert

SELECT CONVERT(UNIQUEIDENTIFIER, '{'+'7DA26ECB-D599-4469-91D4-F9136EC0B4E8EXTRACHARS'+'}');

you get

Msg 8169, Level 16, State 2, Line 1
Conversion failed when converting from a character string to uniqueidentifier.


I am not able to get the result when joining two tables with where clause with OR condition

The join happens - logically - before the WHERE clause is applied. You can use an EXISTS, moving the join to a subquery:

SELECT p.*
FROM projects  p
WHERE p.project_manager_id = 1
   OR EXISTS
      ( SELECT 1
        FROM project_user_cross  c
        WHERE c.project_id = p.id
          AND c.user_id = 1
      ) ;


There is a CREATE TABLE syntax where you can declare the data folder and index folder under the partition_definition of a partition_option:

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir'] <<<<<---------
        [INDEX DIRECTORY [=] 'index_dir'] <<<<<---------
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]

CAVEATS

I know it works for MyISAM because I wrote about it in September 2011 : Dynamically changeable database. For an already existing MyISAM table, it can done from the OS by moving the .MYD and .MYI to another disk and setting up two symlinks. I wrote about that in May 2012 : Can I have one table be an "alias", or "symlink", to another?. When done correctly, SHOW CREATE TABLE will display DATA DIRECTORY and INDEX DIRECTORY and their respective folders.
I personally implemented this in production for a client when I did the following

Converted mysql.general_log from CSV to MyISAM
Moved /var/lib/mysql/mysql/general_log.MYD to a large 3TB disk
Moved /var/lib/mysql/mysql/general_log.MYI to a large 3TB disk
Setup /var/lib/mysql/mysql/general_log.MYD as a symlink to the real .MYD
Setup /var/lib/mysql/mysql/general_log.MYI as a symlink to the real .MYI
enabled the general log
Watched the client's general log growth to 1.1TB
I wrote up how to set this up back in June 2011 : Mail alert from MySQL when databases are edited (under Option 3 : You Could Monitor the general log)

Using hard links, it can be done to MyISAM tables in Windows, although the effort to do so may not be worth it. I wrote about that back in January 2012 : Can I specify a different disk for a specific MySQL table?
I do not believe [DATA DIRECTORY [=] 'data_dir'] and [INDEX DIRECTORY [=] 'index_dir'] were implemented for InnoDB. Even if the it were implemented for InnoDB, I would adamently advised against it and I wrote about why you should not back in August 2011 : MySQL InnoDB data file directly on a disk slice (fixed-size)?

Your Original Question

MySQL 5.5 Only : With regard to your question in the heading, Where are partitions defined in mysql, you can run this query to locate all tables that have partitions:

select count(1) partition_count,table_schema,table_name
from information_schema.partitions
group by table_schema,table_name
having count(1) > 1;

Here is a sample:

mysql> select count(1) partition_count,table_schema,table_name
    -> from information_schema.partitions
    -> group by table_schema,table_name
    -> having count(1) > 1;
+-----------------+--------------+-------------------------+
| partition_count | table_schema | table_name              |
+-----------------+--------------+-------------------------+
|              31 | clickit      | t_keyword_conversion_ga |
|               2 | part         | myoldpart               |
|               3 | test         | messages                |
|             256 | test         | ngram_key               |
|               2 | test         | rc1                     |
+-----------------+--------------+-------------------------+
5 rows in set (1.60 sec)

mysql>

You can get the complete definition by running SHOW CREATE TABLE db.tbl\G from any table from the result of that query.


Update one mysql column when another is edited

The code doesn't look like valid Mysql code:

ALTER is not a valid option
No AS in Mysql
IF UPDATE(column)

UPDATE(column) tests whether column updated in SQLServer, not in Mysql
IF syntax is invalid (check https://dev.mysql.com/doc/refman/5.5/en/if.html)

Finally, you can change value of the record which is being modified in BEFORE trigger, not in AFTER.

You need something like

drop trigger if exists last_progress_date;
delimiter //
CREATE TRIGGER last_progress_date BEFORE UPDATE ON wp_task_mgr
FOR EACH ROW
BEGIN
   --assuming progress_percentage is not nullable; if it is, the condition
   -- needs to be modified to properly handle NULL values
   IF (NEW.progress_percentage != OLD.progress_percentage)
   THEN
       SET NEW.last_progress_date = GETDATE();
   END IF;
END;
//
DELIMITER ;


You can't shrink SQL Anywhere databases online.

The "correct" way to do it is to unload/reload it in a new database.

It's easy to do it via Sybase Central. Via command line it is the dbunload command with the -an command line switch. See Unload utility (dbunload) in the documentation.

A shrunk database is not by default faster than an "unshrinked" database.


How can I reduce time spent acquiring the mongodb schema lock?

This is a general architectural problem with the WiredTiger storage engine.
It is now being discussed here: https://jira.mongodb.org/browse/WT-5479

Long story short, your number of open files is too high.
If you can, consider removing unnecessary indexes, e.g. indexes on small collections.
You could also explore the new wildcard indexes introduced in MongoDB 4.2


SQL Server: allocated vs reserved vs commited

What exactly is the memory that is allocated to the a memory component in SQL Server?

Memory allocated is memory given to SQL Server for doing various processing. Allocation is wide terms which means providing memory you should not attach any specific meaning to it.
You are correct committed memory is physical memory used because committed memory is backed by physical memory while reserved memory is memory reserved by process which it thinks it might need it may necessarily not be committed.

When a memory component requests memory from a Memory Manager, what is the nature of this memory being granted? Real/Physical or VAS?

VAS: Virtual address space is total amount of virtual memory a process can see in the system when created.
When SQL Server process requests memory it is first mapped to VAS address in the region. VAS is 8TB for 64 bit system and 4 GB for 32 Bit system. Every process which requests memory sees virtual memory which equal to 4GB or 8 TB as per architecture(32 or 64 bit) it is running on. Now after process is mapped to VAS memory manager finds out physical memory to which it can be mapped and finally commit memory to this process when this is eventually done the memory is allocated and is called physical memory allocated to process.
Virtual_memory_committed: This is virtual memory committed as reported by the  clerk.
Virtual_memory_reserved: This is the VM reserved as tracked by the clerk..
Pages_kb: Amount of page memory allocated to process.
I hope this clears few things for you


How should I configure the RAID array of SSD drives on my SQL Server?

Conventional wisdom about RAID doesn't apply well to SSDs. They don't really need striping (RAID0). They are prone to failures by-design, but RAID-1 is usually not the right answer for SSD for two reasons: a) is wasteful, halves the capacity of the SSD array (and they are pricey) and 2) SSDs failure characteristics leads towards both drives in the mirror to fail at very close intervals (ie. correlated failures) and thus render the entire array useless. See Differential RAID: Rethinking RAID for SSD Reliability for a lengthier discussion. Some have recommended using Raid-6 for SSDs.

Additionally, the conventional wisdom of SQL Server file layout doesn't apply to SSDs. I would recommend you watch SQL on SSDs: Hot and Crazy Love and go over the benchmark links in this answer.


How to use a recursive CTE to get ancestors in a hierarchy

I would stick with one model. If you store both nested set information and parent information, you will have to maintain both, and you may end up with inconsistent information about your tree. If your main focus is on reporting both for div1 and div1, div2, you may consider GROUPING SETS. They are a UNION in disguise:

select sum(somevar_1) as somevar_1
     , sum(somevar_2) as somevar_2
     , div1.name as d1name
     , div1.id as d1id
     , COALESCE(div2.name, 'TOTAL') as d2name
     , div2.id as d2id
from report
join location as div3
    on report.location_id = div3.id
join location as div2
    on div3.parent_id = div2.id
join location as div1
    on div2.parent_id = div1.id
-- for example, all reports this year
where date_trunc('year', tyme) = '2019-01-01'
group by
    GROUPING SETS((div1.id, div1.name, div2.id, div2.name)
                 ,(div1.id, div1.name))

For the second grouping set (div1.id, div1.name) you will get null instead of div2.id, div2.name. That's why I used COALESCE to map the name to TOTAL.

You may also add a level of a GRAND TOTAL like:

    GROUPING SETS((div1.id, div1.name, div2.id, div2.name)
                 ,(div1.id, div1.name)
                 ,())

Grouping sets are the most general construction, but under certain situations (not yours), you can use GROUP BY ROLLUP and GROUP BY CUBE which are a bit shorter. See:

Grouping sets, rollup, and cube

If I get it right, you will always traverse div1, div2 and div3 levels in your queries and I therefor strongly doubt that a recursive CTE will perform better than 3 static JOINS. The power of RCTE is that it can traverse a variable number of joins. For a fixed number of levels, there is no performance gain.


The easiest way would be to add a computed column to the table with your mod calculation, and then create an index or add it to an existing index.

For example:

-- Test table and values
CREATE TABLE #Test (col integer PRIMARY KEY);

INSERT #Test (col)
VALUES (1), (2), (3), (4), (5);

-- Original query (scan)
SELECT T.col
FROM #Test AS T
WHERE col % 5 = 4;

-- Add computed column (no need for persisted)
ALTER TABLE #Test
ADD col_mod5 AS col % 5;

-- Index computed column
CREATE INDEX i
ON #Test (col_mod5)
INCLUDE (col);

-- Index seek
SELECT T.col
FROM #Test AS T
WHERE col % 5 = 4;

-- Tidy up
DROP TABLE #Test;

Another alternative is to create an indexed view selecting what you need from the table, plus a column with the mod calculation, and refer to that in your queries. You do this by declaring the view as schemabinding and then creating a clustered index on it as if it were a table.

If you are using Enterprise Edition, you may not need to change your query to use the view. Otherwise, you will need to reference the view explicitly and use the NOEXPAND hint. The indexed computed column is the lighter weight solution.


Postgres tablespace size is bigger than database size

A tablespace can contain tables from more than one database.

The extra space must be from another database.


Query is slow with JDBC parameters, fast with concatenated SQL

Not a complete answer, but here's some diagnostics and suggestions from looking at the plans.

You're running SQL 2016, so there's a ton of useful info in the plan.

First is that you're running a GDR patch for SQL 2016 SP1 (13.0.4224.16).  SQL 2016 SP2 was released on April 24, 2108, almost a year ago, and after 1 year applying the service pack is required for support.  So you can test this on SQL 2016 SP2, or SQL 2017 to see if it behaves differently.

When optimizing a parameterized query, SQL Server has to pick a plan that will be used for all future values of fooId and likeParameter.  Apparently it's guessing you will often have a selective LIKE predicate, and so using the Name index will, on average, be cheaper.  With the hard-coded values, SQL Server doesn't need to build a plan that works for multiple different LIKE predicates, and so picks a plan that doesn't use a non-clustered index.

To evaluate the predicate NAME_ LIKE @p SQL Server is range scanning a non-clustustered index on NAME_.  This works great if you happen to pass a LIKE predicate that starts with a few literal characters, but when it starts with a wildcard, will require a complete scan of the index.  Here scanning, and evaluating the LIKE predicate for each of 14M rows.

In the fast plan SQL seeks 21,518 separate components using the clustered index and checks each one's name against the LIKE predicate.

From the plans:

The difference between the plans is a single operator, which accounts for all of the increased cost: a scan of 14,675,300 rows from the index [PDL_COMPONENT].[IDX_COMPONENT_NAME], taking 37sec of CPU time:

  <RunTimeInformation>
    <RunTimeCountersPerThread
      Thread="0"
      ActualRows="741"
      Batches="0"
      ActualEndOfScans="1"
      ActualExecutions="1"
      ActualExecutionMode="Row"
      ActualElapsedms="37038"
      ActualCPUms="37028" />
  </RunTimeInformation>

Not parameter sniffing, as the ParameterCompiledValue is the same as ParemeterRuntimeValue:

    <ParameterList>
      <ColumnReference Column="@P1" ParameterDataType="nvarchar(127)" ParameterCompiledValue="N'%geronimo%'" ParameterRuntimeValue="N'%geronimo%'" />
      <ColumnReference Column="@P0" ParameterDataType="int" ParameterCompiledValue="(9)" ParameterRuntimeValue="(9)" />
    </ParameterList>

Using the query with the hard-coded values isn't the worst idea here.  Or you could try a RECOMPILE and turn off plan caching without giving up parameterizing your queries.

And I don't know what the indexes and foreign keys in the database are, but using DISTINCT has really bad code smell, as it often indicates that one or more JOIN is not correct, and it affects the cardinality estimation of the query.


Most efficient way to structure segmented data

One possibility, stated simply, is the events are all in a calendar table, along with any non-category-specific attributes... then tables for the separate types of events to store the category-specific attributes.

tbl_calendar
------------------
event_id (PK)
user_id (FK -> user.id)
start_time
end_time

tbl_cat1_event_info
------------------
event_id (FK -> tbl_calendar.event_id)
cat1_foo1
cat1_foo2

tbl_cat2_event_info
------------------
event_id (FK -> tbl_calendar.event_id)
cat2_foo3
cat2_foo4

This query would return essentially the same result as a union query of the two original tables.

SELECT *
  FROM tbl_calendar tc
  LEFT JOIN tbl_cat1_event_info c1 ON c1.event_id = tc.event_id
  LEFT JOIN tbl_cat1_event_info c2 ON c2.event_id = tc.event_id
 WHERE c1.event_id IS NOT NULL OR c2.event_id IS NOT NULL;
-- see the expanded version of this query below

That last line isn't technically necessary but it assures that no events that aren't either in c1 or c2 don't get returned.

Getting just the cat1 events:

SELECT *
  FROM tbl_calendar tc
  JOIN tbl_cat1_event_info c1 ON c1.event_id = tc.event_id;

Follow-up: Avoiding events inappropriately being assocated with more than one category

The business rules say that an event is always either a cat1 event or a cat2 event, never both... but the structure I suggested above does not contain any mechanism for avoiding the inconsistency of the same event_id being inserted in both the cat1 and cat2 event info tables, which would then indicate that one event was both types of events... which can't be true.

If we add an event_type_id column to tbl_calendar, we can also modify the joins so that only the appropriate *event_info table is joined.

-- assumes tbl_calendar has an event_type_id column
SELECT *
  FROM tbl_calendar tc
  LEFT JOIN tbl_cat1_event_info c1 ON tc.event_type_id = 1 AND c1.event_id = tc.event_id
  LEFT JOIN tbl_cat1_event_info c2 ON tc.event_type_id = 2 AND c2.event_id = tc.event_id
 WHERE c1.event_id IS NOT NULL OR c2.event_id IS NOT NULL;

This would not actually prevent the event from being inserted but it would result in it not being evaluated if it appears in the wrong table; only the correct table according to event_type_id would be joined for each event.

A more aggressive (but perhaps also more correct) approach in addition to the above would be before-insert and before-update triggers on the *event_info tables that checked the other *event_info table and used SIGNAL (introduced in MySQL 5.5) to throw an exception to prevent the incorrect data from being inserted or updated.  One example:

DELIMITER //
CREATE TRIGGER tbl_cat1_event_info_bi BEFORE INSERT ON tbl_cat1_event_info FOR EACH ROW
BEGIN
    IF EXISTS (SELECT c2.id FROM cat2_event_info c2 WHERE c2.id = NEW.id) THEN
        SIGNAL SQLSTATE '45000'
               SET MESSAGE_TEXT = 'Conflicting value found in cat2_event_info';
    END IF;
END;
//
DELIMITER ;


what do vertical lines refer to in database diagram view

The answer for to your question is further down in the article:

  Endpoints The endpoints of the line indicate whether the relationship is one-to-one or one-to-many. If a relationship has a key at one endpoint and a figure-eight at the other, it is a one-to-many relationship. If a relationship has a key at each endpoint, it is a one-to-one relationship.

The key represents the "one" and the figure-eight (or infinity symbol, I presume) represents the "many".

The relationships aren't tied to the visually closest column name in the diagram, but to the column with the same name in each table. For instance, the one-to-many relationship between publishers and titles is tied to the pub_id column.


Does mysqldump -all-databases include all objects

No mysqldump -all-databases does not include all objects

mysqldump --help

  -A, --all-databases Dump all the databases. This will be same as --databases
                      with all databases selected.

So mysqldump with --all-databases only dumps all the databases.

In order to migrate all the databases to a new server, you should take a complete backup :

mysqldump an entire mysql instance

mysqldump -h... -u... -p... --events --routines --triggers --all-databases > MySQLData.sql

The drawback is that backups created this way can only be reloaded into the same major release version of mysql that the mysqldump was generated with. In other words, a mysqldump --all-databases from a MySQL 5.0 database cannot be loaded in 5.1 or 5.5. The reason ? The mysql schema is totally different between major releases.

Here is the generic way to dump the SQL Grants for users that is readble and more portable

mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -h... -u... -p... --skip-column-names -A | sed 's/$/;/g' > MySQLGrants.sql

Have a look at answer from RolandoMySQLDBA on How can I optimize a mysqldump of a large database ?


How postgres deals with XIDs and wraparounds?

Imagine the transaction IDs from 0 to 4294967296 in a circle. AS some point in that circle is your current transaction ID.

Let's say your current transaction ID is 1000000. Then the 2^31 transaction IDs after it are in the future: 1000001 to 2148483648. And the 2^31 transactions before that are the past: 2148483649 to 4294967296 and 0 to 999999 (remember we have a circle, so right after 4294967296 comes 0).

Once your current transaction ID crosses 4294967296, it becomes 0 again (to be precise, it will become 3, because the transaction IDs 0 to 2 have a special meaning).