There are two separate processes that you are referring to.

mongod is the command that starts the MongoDB server process.  It listens on a port (27017 by default) and you can connect to it with mongo shell or with your driver from an application.
mongo is the mongo interactive shell.  It tries to connect to a port (27017 by default) to talk to the mongod server process.  It will not be able to connect to a port that does not have a running mongod process listening on.

You need to start mongod first.  Whether you start it logging to the console or to a log file (--logpath) it needs to be started and running successfully.  If you provide a --logpath argument then you can also provide a --fork argument which will detach it and run it as a separate process, rather than attached to your console.

Once you have mongod successfully running and waiting for connections, you can now run mongo program to start up the shell and connect to the server.


What does 'nls' in nls_date_format stand for?

National Language Support (because Americans have this weird idea of using MM-DD-YYYY as a date format, and different languages have different words for their months).


First off, gaps in a sequence are to be expected. Ask yourself if you really need to remove them. Your life gets simpler if you just live with it. To get gap-less numbers, the (often better) alternative is to use a VIEW with row_number(). Example in this related answer:

Gap-less sequence where multiple transactions with multiple tables are involved

Here are some recipes to remove gaps.
1. New, pristine table
Avoids complications with unique violations and table bloat and is fast. Only for simple cases where you are not bound by FK references, views on the table or other depending objects, or by concurrent access. Do it in one transaction to avoid accidents:
BEGIN;
LOCK tbl; -- optionally: IN SHARE MODE to allow concurrent reads

CREATE TABLE tbl_new (LIKE tbl INCLUDING ALL);

INSERT INTO tbl_new -- no target list in this case
SELECT row_number() OVER (ORDER BY id), data  -- all columns in default order
FROM   tbl;

ALTER SEQUENCE tbl_id_seq OWNED BY tbl_new.id;  -- make new table own sequence

DROP TABLE tbl;
ALTER TABLE tbl_new RENAME TO tbl;

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

COMMIT;

CREATE TABLE tbl_new (LIKE tbl INCLUDING ALL) copies the structure incl. constraints and defaults from the original table. Then make the new table column own the sequence:

Activerecord-import & serial column in PostgreSQL

And reset it to the new maximum:

How to reset Postgres' primary key sequence when it falls out of sync?

This carries the advantage that the new table is bloat-free and clustered on id.
2. UPDATE in place
This produces a lot of dead rows and requires (auto-) VACUUM later.
If the serial column is also the PRIMARY KEY (like in your case) or has a UNIQUE constraint, you must avoid unique violations in the process. The (cheaper) default for PK / UNIQUE constraints is to be NOT DEFERRABLE, which forces a check after every single single row. All details under this related question on SO:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

You could define your constraint as DEFERRABLE (which makes it more expensive).
Or you can drop the constraint and add it back when you are done:
BEGIN;

LOCK tbl;

ALTER TABLE tbl DROP CONSTRAINT tbl_pkey;  -- remove PK

UPDATE tbl t  -- intermediate unique violations are ignored now
SET    id = t1.new_id
FROM  (SELECT id, row_number() OVER (ORDER BY id) AS new_id FROM tbl) t1
WHERE  t.id = t1.id;

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

ALTER TABLE tbl ADD CONSTRAINT tbl_pkey PRIMARY KEY(id); -- add PK back

COMMIT;

Neither is possible while you have FOREIGN KEY constraints referencing the column(s) because (per documentation):

The referenced columns must be the columns of a non-deferrable unique
or primary key constraint in the referenced table.

You would need to (lock all involved tables and) drop / recreate FK constraints and update all FK values manually (see option 3.). Or you have to move values out of the way with a second UPDATE to avoid conflicts. For instance, assuming you have no negative numbers:
BEGIN;
LOCK tbl;

UPDATE tbl SET id = id * -1;  -- avoid conflicts

UPDATE tbl t
SET    id = t1.new_id
FROM  (SELECT id, row_number() OVER (ORDER BY id DESC) AS new_id FROM tbl) t1
WHERE  t.id = t1.id;

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

COMMIT;

Drawbacks as mentioned above.
3. Temp table, TRUNCATE, INSERT
One more option if you have plenty of RAM. This combines some of the advantages of the first two ways. Almost as fast as option 1. and you get a pristine, new table without bloat but keep all constraints and dependencies in place like in option 2.
However, per documentation:

TRUNCATE cannot be used on a table that has foreign-key references
from other tables, unless all such tables are also truncated in the
same command. Checking validity in such cases would require table
scans, and the whole point is not to do one.

Bold emphasis mine.
You could drop FK constraints temporarily and use data-modifying CTEs to update all FK columns:
SET temp_buffers = 500MB;   -- example value, see 1st link below

BEGIN;

CREATE TEMP TABLE tbl_tmp AS
SELECT row_number() OVER (ORDER BY id) AS new_id, *
FROM   tbl
ORDER  BY id;  -- order here to use index (if one exists)

-- drop FK constraints in other tables referencing this one
-- which takes out an exclusive lock on those tables

TRUNCATE tbl;

INSERT INTO tbl
SELECT new_id, data  -- list all columns in order
FROM tbl_tmp;        -- rely on established order in tbl_tmp
-- ORDER BY id;      -- only to be absolutely sure (not necessary)

--  example for table "fk_tbl" with FK column "fk_id"
UPDATE fk_tbl f
SET    fk_id = t.new_id  -- set to new ID
FROM   tbl_tmp t
WHERE  f.fk_id = t.id;   -- match on old ID

-- add FK constraints in other tables back

COMMIT;

Related, with more details:

How to delete duplicate entries?
How to “merge” two tables efficiently in SQL?


What would be the first things you do to find out about an MYSQL environment you are taking over?

The pt-mysql-summary tool from Percona toolkit gives you a very nice overview of your running server.

It will tell you stuff like what the major config params are set to, what technologies ara available in your server, take a 10 second status sample, analyze your schema etc.

Also worth to run pt-duplicate-key-checker at least once, to detect redundant keys.

I'm authoring the common_schema tool for MySQL, which also offers some diagnostics: the redundant_keys view is parallel to pt-duplicate-key-checker; the data_size_per_schema view will tell you, well, the datasize per schema; the auto_increment_columns view will tell you about "free space" in your AUTO_INCREMENT columns; and more.

Watching the processes as they go, using innotop or the old-timer mytop is also worthwhile: get to know the processes that run most, the longest etc.


FROM keyword not found where expected (Oracle) ORA-00923

You need an alias for both derived tables. And the outer pair around the from clause is useless.

SELECT DISTINCT t1.unique_id AS uid, t1.confidence_is_same
FROM ( --<< only one opening parenthesis
  SELECT unique_id,
         confidence_is_same,
         first_name,
         last_name,
         postal_code
  FROM daniel.unique_physician
  WHERE daniel.unique_physician.first_name = ''
  AND   daniel.unique_physician.last_name = ''
  AND   daniel.unique_physician.is_root_phys = 0
  AND   daniel.unique_physician.postal_code = ''
) t1 ---<< the alias for the derived table is missing
  INNER JOIN (
    SELECT max(confidence_is_same) OVER (PARTITION BY root_id) max_conf
    FROM daniel.unique_physician
    WHERE daniel.unique_physician.first_name = ''
    AND   daniel.unique_physician.last_name = ''
    AND   daniel.unique_physician.is_root_phys = 0
    AND   daniel.unique_physician.postal_code = ''
  ) t2 ON t1.confidence_is_same = t2.max_conf

But the join isn't needed in the first place. Your query can be simplified to:

SELECT DISTINCT t1.unique_id AS uid, t1.confidence_is_same
FROM (
  SELECT unique_id,
         confidence_is_same,
         max(confidence_is_same) OVER (PARTITION BY root_id) max_conf
  FROM daniel.unique_physician unq
  WHERE unq.first_name = ''
  AND   unq.last_name = ''
  AND   unq.is_root_phys = 0
  AND   unq.postal_code = ''
) t1
where confidence_is_same = max_conf;

You don't need to select first_name, last_name and postal_code in the inner select as you don't use them in the outer select. This can make the query potentially more efficient.

Additionally, the condition unq.last_name = '' won't do what you think it does. Oracle does not have an "empty string". A string with length zero ('') will be stored as NULL, so what you really want is probably:

SELECT DISTINCT t1.unique_id AS uid, t1.confidence_is_same
FROM (
  SELECT unique_id,
         confidence_is_same,
         max(confidence_is_same) OVER (PARTITION BY root_id) max_conf
  FROM daniel.unique_physician unq
  WHERE unq.first_name is null
  AND   unq.last_name is null
  AND   unq.is_root_phys = 0
  AND   unq.postal_code is null
) t1
where confidence_is_same = max_conf;


Apache Phoenix: Using MINUS throws an error

I am not sure which SQL is used by Apache Phoenix and I cannot exclude that your code above contains syntax error but what I know is that the MINUS operator can only be used in Oracle SQL.

I suggest trying it with the EXCEPT operator which is used in Transact SQL:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql

If the EXCEPT does also not work, try to imitate the MINUS like it is done in MySQL, which currently does not feature MINUS or EXCEPT at all:
http://www.gokhanatil.com/2010/10/minus-and-intersect-in-mysql.html


Getting errors when I try to group by CTEs

In your first CTE you grouped by Object_Name and all of the other entries were contained within an aggregate.  In your last CTE that isn't the case.

    SELECT c3.Object_name
        ,c1.TimesExecuted/c1.CountExec as New_new_result
        ,c1.MinDate

I've removed the commented lines for clarity, but you'll notice that none of these are contained within an aggregate. (MIN, MAX, AVG, etc).  If you want to group by c3.Object_name you'll need to put something around the other fields.  For example:

    SELECT c3.Object_name
        ,SUM(c1.TimesExecuted/c1.CountExec) as New_new_result
        ,MIN(c1.MinDate)


Normalise an expenditure table in a budget database

Assuming they all have the same three columns you mentioned an no more, have you considered this:

expenditure_budget_item:
id | type_id | amount | frequency | comment
30   6         50       null        "test comment"
31   7         40       null        null

expenditure_budget_types:
id | name   | description
6    "Food"   "Food expenditures"
7    "Fuel"   "Fuel expenditures"

Let me know if there's any details I may have missed.


SQL Server doesn't send intermediate SSL certificates

To configure server side SSL for SQL Server, you will need to place the public CA certificate and private key in the certificate store of the service account running the database engine and place the certificate without the private key in the certificate store of the java VM initiating the connection. Then use SQL Server Configuration Manager to open the connection properties and set force encryption to yes and select the certificate to use, which would be the public CA certificate, for encrypting the connection. When an SSL connection is initiated, the SQL Server will present the certificate to the java VM and the java VM will find it in its store of trusted certificates and validate it directly against the CA without the need for walking the chain of trust for intermediate certificates. Since the server is on the Internet, it wouldn't hurt to configure ipsec so all traffic is encrypted. Also, It's good that you have a certificate from a public CA because if no certificate is configured, then SQL Server will used a self signed certificate and that configuration will leave you vulnerable to a man in the middle attack.


Stored procedure in SSIS Lookup Transformation Editor

I would suggest trying to put the stored procedure logic into a view or table-valued function, which could then be referenced in the lookup.

If that can't be done, then you can use the ugly workaround you already discovered:

SELECT * FROM OPENQUERY(LoopBackServer, N'EXEC db.dbo.procedurename;');


Because Compact Edition is deprecated and doesn't support full-text search (or anything like it), I think the best solution here is to stop using Compact Edition and instead use SQL Server Express with Advanced Services, which includes full-text search capabilities.

Stop moving database files around between developers. This is just dangerous, regardless of whether you are using Compact Edition or proper editions of SQL Server. You should install a single instance of SQL Server, and everybody manage a single copy of the database. Use source control so that rolling back is easier than "use the copy of the file from Jimmy's machine and then add this change and take this table away..."

In order to convert back from Compact Edition to proper SQL Server, see this question and this question.


Mysql: select from information_schema.STATISTICS hanging or slow? never saw a result

20K tables means opening 20K files on the system.  This takes minutes on the typical server.  How long did you wait?

20K tables often means poor schema design.

BTW, the slowness will be solved in 8.0, when the .frm files are moved into InnoDB tables.  Many I_S queries will be a hundred times as fast.


Finding parameter names for binding stored procedure parameters

I did a little bit more research and found the answer to the problem.

Even though the first parameter for DBMS_OUTPUT.PUT_LINE is listed as item in the 10g documentation for DBMS_OUTPUT. The actual name for the parameter is A. You need to bind the name using DBMS_OUTPUT.PUT_LINE(A => 'random text').

The actual parameter names for stored procedures can be discovered using the following query:

SELECT
    argument_name,
    in_out,
    data_type
FROM
    all_arguments
WHERE
    owner = 'SYS' --owner for said procedure
    AND package_name = 'DBMS_OUTPUT' --if procedure is in a package that name, null otherwise
    AND object_name = 'PUT_LINE' --procedure name

The values SYS, DBMS_OUTPUT, and PUT_LINE can be replaced with the values of the the stored procedure that you would like to look up.


You need to find out first what makes the load get too high so that you are able to plan ahead.

If its IO bound then you need faster disks. If its memory bound then you need to add more memory or find ways to reduce its usage.

The best way to find out what the problem is, is to track your system during a peak time. Get a graphing application and look for:

Hard disk usage
CPU usage
Memory usage
Swap usage
Raid card, if you have one

Also keep track of your SHOW STATUS info and see what are the differences. Try to notice patterns, find the bottlenecks and predict scalability issues.

For example, if you system is currently doing 100,000 transactions per minute and your bottlenecks are at 75% capacity, then if your TPM increase it to 140,000 your system might crash/stall/lagg/die-a-horrible-death.


Do long names for jsonb keys use more storage?

Yes key length matters

The bigger the key, the more space to store it. JSONB doesn't do anything special to keys.

Test Cast

Sample Data

# CREATE TABLE foo AS SELECT '{"f":true}'::jsonb FROM generate_series(1,1e6);
SELECT 1000000

# CREATE TABLE bar AS SELECT '{"very_long_key_not_premature_optimization_at_all":true}'::jsonb FROM generate_series(1,1e6);
SELECT 1000000

Table sizes

Now look at the tables

# \dt+ foo;
                   List of relations
 Schema | Name | Type  |  Owner   | Size  | Description
--------+------+-------+----------+-------+-------------
 public | foo  | table | ecarroll | 42 MB |
(1 row)

test=# \dt+ bar;
                   List of relations
 Schema | Name | Type  |  Owner   | Size  | Description
--------+------+-------+----------+-------+-------------
 public | bar  | table | ecarroll | 89 MB |
(1 row)

Source Code

You can see it in the source here

str = TextDatumGetCString(in_datums[i * 2 + 1]);
len = strlen(str);

v.type = jbvString;

v.val.string.len = len;

ZSON Extension

You may consider checking out ZSON extension which provides JSONB cross-row dictionary compression

  ZSON is a PostgreSQL extension for transparent JSONB compression. Compression is based on a shared dictionary of strings most frequently used in specific JSONB documents (not only keys, but also values, array elements, etc).

  In some cases ZSON can save half of your disk space and give you about 10% more TPS. Memory is saved as well. See docs/benchmark.md. Everything depends on your data and workload though. Don't believe any benchmarks, re-check everything on your data, configuration, hardware, workload and PostgreSQL version.


Using hard-coded host name in logon trigger

If you only want users to connect to the standby then check the db_role value from v$database.  The primary will be PRIMARY and the standby wont (it'll be PHYSICAL STANDBY or similar).

Add a check into your trigger to bump them.

...
SELECT database_role INTO db_role FROM v$database;
IF db_role = 'PRIMARY' THEN (deny those users a connection)
..


A customer is a role played by a Party, which is an Individual or Organization (or Automated Agent).

Same with employee.

A party can have zero or more addresses, parties can share the same address, and addresses can exist without parties.

Your data model should look like this:

PARTIES -< ROLES

PARTIES -< PARTY_ADDRESSES >- ADDRESSES

create table parties (
  party_id bigint primary key,
  type text not null, --this should point to a party_type table in real life
  name text not null,
  ...party-type-specific into
);

create table party_roles (
  party_role_id bigint primary key,
  type text not null, --this should point to a party_role_type table in real life
  party_id bigint not null references parties(party_id),
  ...role-type-specific info...
);

create table addresses (
  address_id bigint primary key,
  type text not null, --this should point to a address_type table in real life
  ...address-type-specific info...
);

create table party_addresses (
  party_id bigint references parties(party_id),
  address_id bigint references addresses(address_id),
  role text not null, --this should point to a party_address_role table in real life

  primary key (party_id, address_id, role_id)
);

--demo data:

insert into parties values
(1, 'INDIVIDUAL', 'Neil'),
(2, 'ORGANIZATION', 'Acme, Inc');

insert into party_roles values
(1, 'EMPLOYEE', 1),
(2, 'CUSTOMER', 2);

insert into addresses values
(1, 'MAILING', '123 Main Street'),
(2, 'MAILING', '456 Bay Street');

insert into party_addresses values
(1, 1, 'HOME'),
(2, 2, 'HEADQUARTERS');


SELECT * FROM ALL_SYNONYMS WHERE DB_LINK = 'your_link_name_here';

?


Oracle: deleting data from tables leads to data file fragmentation?

Can you explain exactly what problem you are concerned with?  People have come up with lots of different definitions of "fragmentation" some of which are impossible, some of which are possible but don't create any problems, and a few of which might be worth thinking about.

If you delete at random 1 out of every 2 rows in a table, that will generally mean that you'll have lots of half-empty blocks in the table.  That space will be reused by subsequent inserts into the table, though, so it is generally not create any problems unless you are stating that you are permanently reducing the size of the table (i.e. it will never again grow past 5 GB in size) or you have queries that rely on doing full table scans of the table that you want to tune.  Depending on the Oracle version, you can probably do a SHRINK SPACE on the table to reduce the size of the segment in that case.


Sort Records in Original Order After UNION ALL

How about:
select LOGID, DESCRIPTION, STARTTIME Time from T
union all
select LOGID, DESCRIPTION, ENDTIME from T
order by LOGID, Time

If you want to order the event pairs by STARTTIME instead of LOGID, then
with q as
(
    select LOGID, DESCRIPTION, STARTTIME, STARTTIME Time from T
    union all
    select LOGID, DESCRIPTION, STARTTIME, ENDTIME Time from T
)
select LOGID, DESCRIPTION, Time
from q
order by STARTTIME, Time


AlwaysEncrypted error when inserting via stored proc

So I figured out what I was doing wrong
AlwaysEncrypted requires the variables to be defined in line:

DECLARE @OPERATION_ID int = 4
DECLARE @PARAMETER_NAME varchar(100) = 'algorithm'
DECLARE @PARAMETER_METRIC varchar(100) = 'algorithm'
DECLARE @PARAMETER_VALUE varchar(100) = 'TestValue3'

INSERT INTO [dbo].[TOKEN_PARAMETER] (OPERATION_ID, PARAMETER_NAME,
PARAMETER_METRIC, PARAMETER_VALUE)
SELECT @OPERATION_ID,
       @PARAMETER_NAME,
       @PARAMETER_METRIC,
       @PARAMETER_VALUE

This allows for successful insert into the table.
When using a stored procedure you cannot use the same method to insert.

So in essence, there is nothing wrong with how to stored procedure works.
The application needs to change the way it populates the columns, using the same stored procedure

Please see below link for reference:

https://social.technet.microsoft.com/wiki/contents/articles/37979.working-with-the-always-encrypted-feature-in-sql-server-2016.aspx#Add_Data_to_Always_Encrypted_Column

Still waiting on applications team to come back with results but i'll mark the question as solved if this fixed the issue


Does TRANSACTION/COMMIT/ROLLBACK only work on certain MySQL engines such as InnoDB?

The only storage engine ("table type") that supports transactions with the standard MySQL 5.x distributions is InnoDB.  MyISAM and the others do not support transactions.

http://dev.mysql.com/doc/refman/5.5/en/ansi-diff-transactions.html


How to list all postgres tables in one particular schema


How to update JSON using logic equivalent to WHERE clause

Following is a worked example.

Caveat: the final result is a JSON fragment which is the content of the '$.add' array, rather than the original @data variable containing that array - but the logic is good for solving the problem

declare @data nvarchar(max);
set @data = N'{"add":[{"id":-1,"valA":7,"valB":8,"valC":"text","valD":{"3":"text"}},'
+ '{"id":-2,"valA":5,"valB":8,"valC":"Moretext","valD":{"2":"textB","3":"Moretextagain"}},'
+ '{"id":-1,"valA":9,"valB":8,"valC":"SecondCase","valD":{"2":"textX","3":"MoretextagainSigh"}}'
+ ']}';

select * from openjson(@data);

declare @idToFind int = -1;
declare @newId int = 999;
declare @arrayElement nvarchar(max);
declare @tempSQL nvarchar(max);
declare @add nvarchar(max);
declare @nDoublequote nvarchar(1) = cast(char(39) as nvarchar(1)); -- double quote character as an nvarchar

select @add = json_query(@data, '$.add');

-- Loop through the elements in the '$.add' array that have an id value we are interested in
declare cur_json cursor local fast_forward for select cast([key] as nvarchar(16)) from openjson(@add) where (select json_value([value],'$.id')) = @idToFind;
open cur_json;
fetch next from cur_json into @arrayElement;

    while @@fetch_status = 0
        begin

            -- an element in the '$.add' array has an id that needs updating

            set @tempSQL = concat(N'select @add = json_modify(@add, ' , @nDoublequote , N'$[' , @arrayElement , N'].id' , @nDoublequote , N', ' , @nDoublequote , cast(@newId as nvarchar(16)) , @nDoublequote, N')');
            select ' tempSQL: '+isNull(@tempSQL,'<null>')+'. '; -- just for debugging purposes
            exec sp_executeSQL @tempSQL, N'@add NVARCHAR(MAX) OUTPUT', @add = @add OUTPUT

            fetch next from cur_json INTO @arrayElement; -- check for other array elements having the same id value
        end

close cur_json;
deallocate cur_json;

select * from openjson(@add);

The key to this solution is the cursor which iterates through the array but selects only elements that match on our required id value (ie. the WHERE part of my question). This cursor actually only returns the array's [key] value - which is, in other words, the matching element's array index.

The second part of this solution is to build a string containing the SQL that uses json_modify to modify the value, referencing that value using the array index we just obtained from the cursor, and then using exec sp_executeSQL to effect that.

I'll be honest, I'm not 100% sure why the parameters are required with that procedure - I've taken this part of the solution from yet another question.

This approach -

having the sought and replacement values in parameters
using a cursor to obtain the array indices for matching elements
then building dynamic SQL to effect the changes

is reproducible and actually fairly simple once you strip away the comments and excess select statements that illustrate the solution.


10000 tenants and Muti-Tenant Data Architecture performance?

Design a shared-everything system.  This can be deployed in a shared-nothing way i.e. put each tenant in their own database, should that prove desirable.  The extra development effort to type the additional predicate is very small and easy to do up front.  The reverse - where you re-factor a shared-nothing system to become multi-tenant - will be a significant project and difficult to get right retrospectively.  You will need some scripts or ETL packages to load / unload one tenant from a database.  This will help with size & load balancing, privacy concerns and such like.

The schema-based approach has all the disadvantages of single-database and none of the advantages of multi-tenant.  You will end up with a lot of dynamic code, stored procedures will be difficult to get right and statement recompiles will be ubiquitous, increasing response times.

Invariably some of your tenants will be much larger than others.  My experience is that you will end up with a handful of tenants representing 30%-70% of the total data volume.  These will each have their own DB and, perhaps, infrastructure.  They will consume 60% of your support and maintenance time.  The others will all fit into one or two databases.

I've run development instances with roughly 1,000 databases online sharing about 8GB of memory.  This worked well enough for dev.  I'd never try this in production as the risk of problems snowballing would be too high.

If you're feeling brave you may like to experiment with auto-close on one or more database.  This will save some resources on the instance.  This is definitely not recommended.  I mention it merely as an observation.


Can triggers be used to implement automated polymorphic association?

I was going to write a long post telling you that there are basically 3 ways to implement polymorphic associations, but someone (@Bill Karwin) has already done it better in a more compact way: http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back/32

Your original solution is the first one presented on the slides, the one you propose is the 3rd one (but without using a base, parent table -more on this later), and maybe one can argue that is the one that seems the "cleanest". The actual preferred solution is not always the cleanest, but the best fit for the queries to be done. For example, the single table like_buttons may be much easier to query, but it can grow very tall. Having separate tables, like in solution #2 on the slides can be helpful as we are doing some kind of manual horizontal partitioning. On the other side, having separate tables may be less flexible, as it may force us to change the code if we add another entity part of the polymorphism. Also, there is a more direct relation in the direction like_button -> entity_to_be_liked.

Regarding specifically the trigger, I have nothing against the concept, but I am not sure if it is useful, as on creation time it is completely empty. Can't it be created on the first "like"? Referential integrity will already be secured if you create the extra generic table that I mentioned.


Partition by year and sub-partition by month mysql

I had to do the same thing and solved slightly differently. Basically as far as I understand by reading the docs MySQL subpartitioning does not support partition types besides HASH and KEY.

  In MySQL 5.6, it is possible to subpartition tables that are partitioned by RANGE or LIST. Subpartitions may use either HASH or KEY partitioning. This is also known as composite partitioning.

This means that we can't determine in what subpartition a record will end up. It's up to MySQL. So I don't think it's wise giving such names to your subpartitions (i.e. january) because no way you're going to know if stuff created on that month will end up there. This because you can't subpartition by MONTH(nav_date) but only by HASH(MONTH(nav_date)) or KEY(MONTH(nav_date)).

So to solve the problem I decided to create a new month column in my table and then I added an index to it. Then I subpartioned by KEY(month) without caring about the subpartition names. This way in MySQL 5.6 I could select the main partition from the FROM clause and the subpartition by specifying the month in the WHERE clause.

Follows the full example:

CREATE TABLE `my_example` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `value` double DEFAULT NULL,
    `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
    `timestamp` datetime NOT NULL,
    `last_modified` datetime NOT NULL,
    `month` tinyint(1) NOT NULL,
    PRIMARY KEY (`id`,`timestamp`, `month`),
    KEY `in_is_deleted` (`is_deleted`),
    KEY `in_last_modified` (`last_modified`),
    KEY `in_timestamp` (`timestamp`),
    KEY `in_month` (`month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY RANGE (YEAR(`timestamp`))
SUBPARTITION BY KEY (`month`)
SUBPARTITIONS 12 (
    PARTITION p2011 VALUES LESS THAN (2012),
    PARTITION p2012 VALUES LESS THAN (2013),
    PARTITION p2013 VALUES LESS THAN (2014),
    PARTITION p2014 VALUES LESS THAN (2015),
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

EXPLAIN PARTITIONS SELECT * FROM my_example PARTITION (p2015); -- will go through all twelve subpartitions
EXPLAIN PARTITIONS SELECT * FROM my_example PARTITION (p2015) WHERE month = 9; -- will look only into one subpartition


Table format files (.frm files) that contain a description of table structure. Every
table has its own .frm file, located in the appropriate database directory. This is
true no matter which storage engine manages the table.
The InnoDB storage engine has its own tablespace and log files. The tablespace
contains data and index information for all InnoDB tables, as well as the undo
logs that are needed if a transaction must be rolled back. The log files record
information about committed transactions and are used to ensure that no data loss
occurs. By default, the tablespace and log files are located in the data directory.
The default tablespace file is named ibdata1 and the default log files are named
ib_logfile0 and ib_logfile1. (It is also possible to configure InnoDB to use
one tablespace file per table. In this case, InnoDB creates the tablespace file for a
given table in the table's database directory.)
Each InnoDB table is represented on disk by an .frm format file in the database
directory, as well as data and index storage in the InnoDB tablespace. The InnoDB
tablespace is a logical single storage area that is made up of one or more files or
partitions on disk. By default, InnoDB uses a single tablespace that is shared by
all InnoDB tables. The tablespace is stored in machine-independent format. It is
implemented such that table sizes can exceed the maximum file size allowed by
the filesystem. It is also possible to configure InnoDB to create each table with its
own tablespace.
MySQL represents each MyISAM table using three files: a format file that
stores the definition of the table structure, a data file that stores the contents of
table rows, and an index file that stores any indexes on the table. These files are
distinguished from one another by their suffixes. For example, the format, data,
and index files for a table named mytable are called mytable.frm, mytable.MYD,
and mytable.MYI.


Query specific key fields from x-number of tables?

Understand the problem first, worry about performance later.
This question is too broad and not a good fit for this venue.  However, as someone who works in the property and casualty industry I feel compelled to say... pump the brakes on this for a second.  If this is a hobby project... there are hobbies that are more relaxing.
This is not an easy undertaking, and I can say that I've seen many attempts at this, and they all get something wrong, especially when it comes to the problem you're describing - having multiple types of insurance contracts.
Ultimately that is what you are modeling here - a contract.  Insurance contracts have rules, they can be altered, premium must be calculated and balanced correctly, and they are highly regulated.
So the best practices here are:

Enforce as many contract provisions as possible through the data model
Anything that cannot be enforced through the model must be enforced through transaction logic
Make sure you are aware of the reporting and audit requirements of your jurisdiction

Please read this, then refer to the diagram below.  It's a head start.

This model doesn't even being to address some major concepts you will need address, such as:

Coverages/insurance lines/reporting codes
Governing jurisdictions
Limits/deductibles
Forms/endorsements
Managing customer information across policy types
Premium and policy accounting

As an aside, "fast queries" and GUIDs rarely occur together.  You'll also notice they are absent from the above data model.


Just like  Michael - sqlbot suggested, --quick would help. Official MySQL Doc states:

  --quick, -q

  This option is useful for dumping large tables. It forces mysqldump to
  retrieve rows for a table from the server a row at a time rather than
  retrieving the entire row set and buffering it in memory before
  writing it out.

http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_quick

Regarding the PowerShell issue, there's a note on MySQL Doc:

  Note:

  A dump made using PowerShell on Windows with output redirection
  creates a file that has UTF-16 encoding:

  shell> mysqldump [options] > dump.sql

  However, UTF-16 is not permitted
  as a connection character set (see Section 11.1.4, “Connection
  Character Sets and Collations”), so the dump file will not load
  correctly. To work around this issue, use the --result-file option,
  which creates the output in ASCII format:

shell> mysqldump [options] --result-file=dump.sql

http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html


PostgreSQL alternative to SQL Server’s `try_cast` function

If casting from one specific type to one other specific type is enough, you can do this with a PL/pgSQL function:

create function try_cast_int(p_in text, p_default int default null)
   returns int
as
$$
begin
  begin
    return $1::int;
  exception
    when others then
       return p_default;
  end;
end;
$$
language plpgsql;

Then

select try_cast_int('42'), try_cast_int('foo', -1), try_cast_int('bar')

Returns

try_cast_int | try_cast_int | try_cast_int
-------------+--------------+-------------
          42 |           -1 |

If this is only for numbers, another approach would be to use a regular expression to check if the input string is a valid number. That would probably  be faster than catching exceptions when you expect many incorrect values.


try the following command since the database size is not small:

mysqldump -u USER -p --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > OUTPUT.gz


statistics is used when query execution plan created.

  Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan. For example, the query optimizer could use cardinality estimates to choose the index seek operator instead of the more resource-intensive index scan operator, and in doing so improve query performance.

See http://msdn.microsoft.com/en-us/library/ms190397.aspx for more details.


Oracle - Should processes limit be lower than sessions parameter

The value of sessions can not be lower than processes by definition. Even if you try to set it, the database will just ignore it:
SESSIONS

Default value Derived: (1.5 * PROCESSES) + 22
Oracle uses the default value of this parameter as its minimum. Values
between 1 and the default do not trigger errors, but Oracle ignores
them and uses the default instead.

You simply hit the process limit for that instance. Increase the limit accordingly, or investigate what application opened/opens these sessions.
Also the number of sessions for users should be limited by their profile, not the instance level sessions parameter.
CREATE PROFILE - look for SESSIONS_PER_USER.


What are you looking for? Most things will be in the INFORMATION_SCHEMA like discussed here:

https://dev.mysql.com/doc/refman/5.7/en/information-schema.html


SQL Server 2005 onwards- Capturing Baseline

Check out Rodney Landrum's SQL Server Tacklebox. It's a free book from Red Gate that includes scripts to do that:

http://www.red-gate.com/community/books/sql-server-tacklebox


Importing datetime fields in "m/dd/yyyy" format using PostgreSQL COPY command

I'd recommend always using ISO date format for any kind of data interchange.

Consider Google's Crush Tools convdate for a quick fix:

  convdate --delim ',' --field 1 --preserve_header --input_format '%m/%d/%Y %I:%M:%S' --output_format '%Y-%m-%dT%H:%M:%S' < myData.csv |
  psql -c '\copy foo from stdin with (format csv, header true)'

0:00:00 is probably not a valid time though. Use csvquote if your data has embedded commas or newlines.


Replication : Which subscription changed a row

If the change hasn't yet been replicated you can look in the distribution database to see which publication will change the value.

If the subscriber is in full or bulk_logged recovery you can look in the transaction log to see what user made the change (it might have the application name in there, if it does that'll tell you what SQL Agent job made the change and therefor which publication and subscription made the change).  If you are in simple recovery mode you are hosed unless you are logging this data yourself somewhere within the subscriber.


Add a DL as a login in azure sql managed instace

To create a DL (assuming it is a group in AAD), you need to create without the @domain.. on the database that you want to connect.. example:

CREATE USER [GroupName] FROM EXTERNAL PROVIDER

Here you have a link

https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-23-how-to-add-aad-security-groups-to-azure-sql/ba-p/368866


Microsoft SQL Server says my login is disabled, even in single user/DAC mode

I have had the exact same problem and tried this solution and it works: Using PSExec (a free download from Microsoft) to connect to an instance using the NT AUTHORITY\SYSTEM account. Sample syntax:

PsExec -s -i "C:\...\Tools\Binn\ManagementStudio\Ssms.exe"

This will launch Management Studio as a Windows account that has native sysadmin permissions, and this will allow you to switch to mixed mode, enable the sa account, reset the sa account, add your own Windows account as an administrator, etc.

A lot more information can be found in the following article:

http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/

However bear in mind that you may be breaking some contract, just wanted to note it. :)


I have upgraded from SQL Server 2003 to SQL Server 2008 R2 because my database was 4G and needed extra space.

There is no SQL Server 2003 .. I believe you meant SQL Server 2005.

  I though SQL Server 2008 R2 has a limit of 10Gigs hence I would have 6 Gigs free, however I only got 1Gig free...

Space Available : Displays the amount of available space in the database in megabytes.

SQL server 2008 R2 Express edition has a 10GB limit. SQL Server 2008 R2 Express is still limited to 1 CPU and 1 GB or RAM.

What you are seeing 1GB free is the free space in the database. When you fill up that left 1GB and need more, SQL Server will kick in autogrow algorithm that will grow your database by the amount that you have specified (provided you have autogrowth enabled). Also, have your database autogrowth set to a sensible value instead of default 1MB for data and 10% for log (ignore the values in the below image since its one of my crap database).

Also, enable instant initialization (only applicable to sql server data files).


Issue solved.
Unknown to me, one member of our team deleted the main log file of the database, because it had grown quite a bit. Apparently it was deleted completely instead of being truncated. Then a new log file was created, but for some reason with the root as owner.
Simply changing the owner back to postgres like so

chown postgres:postgres /var/log/postgresql/postgresql-9.4-main.log

solved the issue.


MySQL: Optimize query with a range and distinct count

You have the best index there is.  It is in the right order, and the EXPLAIN says "Using index", which means that it read the index to get the answer, and did not have to reach into the data.

(To further address all the comments...)

Note that it needed to read about 200K rows (of the index) to do the count.  That many rows takes time.

INDEX(offer_id, created_at) versus INDEX(offer_id, created_at, tx_id) -- Apparently you are using InnoDB and tx_id is the PRIMARY KEY.  The PK is included in every secondary key, so these two index specifications are virtually identical.

Order of the columns in an INDEX usually matters.  And it does matter here.  The fields must be in this order:  (1) all the the "=" conditions (offer_id), (2) one range (created_id), and (3) all the other fields to make it "Using index", in any order (tx_id).

If you did not have offer_id = 5, follow the above pattern and get (1) (empty set), (2) (created_id), and (3) (tx_id) -- That is, INDEX(created_at, tx_id).  Note that neither index works well for the other query.

No kind of PARTITIONing would help performance at all.  You don't need a 2-dimensional index (as in two ranges); you have "=" and "range", so a 'compound index' works best.

I suspect that "Using MRR" (Multi-Range Read Optimization) effectively replaces "Using temporary" and "Using filesort" would might normally be used for DISTINCT.


What should I index when joining on a table with conditions?

Given your information about the distribution of the data, the following execution plan will be the best:

Use an index scan to get the 1 to 10 rows from other table.
Perform a nested loop join with main_table as inner table, which will be fast if the join condition is indexed.

So the ideal index on other_table would be

CREATE INDEX ON other_table (date) WHERE deleted_at IS NULL;

If deleted_at is usually NULL, you can omit the WHERE clause without losing much.

If you want to get an index only scan on other_table (which probably isn't necessary, since you only fetch very few rows), you could instead

CREATE INDEX ON other_table (date) INCLUDING (id) WHERE deleted_at IS NULL;

The ideal index on main_table would be

CREATE INDEX ON main_table (user_id, appointment_id);

The order of the columns doesn't matter in this case, because you will scan for both columns with the = operator.

In the real world, you will try to pick indexes that can be useful for as many queries as possible, because having too many indexes hurts performance and wastes space.


How to convert Bytes to GB using sql query

you can use below function :

create or replace function format_size(p_size IN NUMBER)
return VARCHAR2
IS
  v_i number;
  type array_t is varray(3) of varchar2(10);
  v_array array_t := array_t(' Bytes', ' KB', ' MB',' GB');
BEGIN
  v_i := floor(log(1024, p_size));
  return to_char(round(p_size/power(1024, v_i), 2))||v_array(v_i+1);
END;


how to add an extended events tracking session to SSAS?

The target is where the event data is stored.  For example, to write the data to a file use the event_file target type.  To create an extended event and overcome the error on the Data Storage page you're seeing, a target will need to be selected.  There's a wide range of events and exactly what activities you want to track will determine what events you'll want to use.  The script below is an example that creates an extended event that is saved to a local file.  Once this is created you can view the event data by double-clicking on the file, and it will open in SSMS.  This particular extended event tracks the QueryBegin event, which will records queries against the SSAS database.  By default, this will include fields such as the time the query began, the query text, and the NT User Name of of the account that submitted the query.  The default values were left for options such as the max_file_size and maxMemory properties, and of course you'll want to adjust these as necessary.

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ObjectDefinition>
    <Trace>
      <ID>XE_Query_Test</ID>
      <Name>XE_Query_Test</Name>
      <XEvent xmlns="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
        <event_session name="XE_Query_Test" dispatchLatency="0" maxEventSize="0" maxMemory="4" memoryPartition="none" eventRetentionMode="AllowSingleEventLoss" trackCausality="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
          <event package="AS" name="QueryBegin" />
          <target package="package0" name="event_file">
            <parameter name="filename" value="C:\Test\XE_Query_Test.xel" />
            <parameter name="max_file_size" value="4096" />
            <parameter name="max_rollover_files" value="10" />
            <parameter name="increment" value="1024" />
          </target>
        </event_session>
      </XEvent>
    </Trace>
  </ObjectDefinition>
</Create>


Find who will change a record in oracle DB

You can audit updates to the table (assuming that auditing is enabled in your database).  Or you can create a trigger on the table that captures the user and the row being updated and writes the results to an audit table.  Or you can use the DBMS_LOGMNR package to go through the historical redo logs to see who executed an UPDATE in the past.

In any of these cases, you'll see the Oracle user that did the UPDATE.  That may or may not be what you want, however.  Most applications these days are three-tier applications where the user that is connecting to the database is shared by many different human users.  If your middle tier is handling the process of authenticating users and using a single shared Oracle user account in its connection pools, you would need to have the middle tier record who is doing the UPDATE or you would need for the middle tier to identify to the database who the current application user is for any request via a context, via calls to DBMS_APPLICATION_INFO, via calls to a stored procedure, or by just passing the user in with the UPDATE statement.


How to deploy SSRS reports build through Report Builder?

With RB (and sufficient privilege), users can save reports straight to the Report Manager environment. At that point, they're deployed. But if you want to have them save those reports to a Dev/Test box, and want to push them live, there's a few options.

One easy option is to move reports into a known folder 'For Deployment'. You can then have a script which grabs the RDL for reports in here and uploads them to the proper server. You'll probably want to make sure the data sources are mapped appropriately as this method can annoyingly lose references. You can use rs.exe for your scripts, or just use the web references. PowerShell can be very useful here, as it happily talks WebService, and you can schedule PowerShell scripts using SQL Agent jobs.


How to efficiently hit an index in a query that uses IN (mysql)

Reverse the 'subscription' index. As it is, MySQL will probably use the PRIMARY KEY for lookups by a single subscription_id.

Once MySQL hits a RANGE query, which an IN clause is, it doesn't use the rest of the index.

CREATE TABLE `table` (
  `by` varchar(20) NOT NULL,
  `subscription_id` int(11) NOT NULL,
  `subscription_type` int(11) NOT NULL,
  `feed_created_time` datetime NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`subscription_id`,`subscription_type`,`feed_created_time`),
  KEY `subscription` (`subscription_type`,`subscription_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Setup error :Attempted to perform an unauthorized operation SQL Server 2014

In my Case, creating an Administrator Windows Account and installing SQL Server worked!
Also you can simply activate the built-in windows Administrator Account by running the net user administrator /active:yes command in command prompt(Admin Mode), then log in as Administrator. Now You can Install SQL Server without problem.

Afterwards you can use SQL Server from your own normal windows account after installation.

I would recommend deactivating Administrator account by running net user administrator /active:no command in command prompt(Admin Mode)


The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'

First make sure that replication is not causing this, as stated in the connect item the "log_wait_reuse_desc=XTP_CHECKPOINT does not necessarily mean that the XTP checkpoint worker is holding up log truncation." so start by running sp_repltrans and make certain that all data has been distributed.
Then there is this little snippet here:

"it happens on a database, which has memory optimized file group, no matter there has memory optimized tables or not.
The current workaround is set the AutoGrown to be a fixed size. Or, changing the recovery mode to be Simple, and shrinking the log."

So if cleaning up replication does not work try the following:
checkpoint;
dbcc shrinkfile (Logfile, truncateonly)
alter database [database] modify file (filename = 'TRANSACTIONLOG', FILEGROWTH = 5MB)

It is not stated if this is for the log file or the database files but lets start by trying the log files and if not then try setting the database files to fixed growth:


Oracle query that use to run in less than a second now takes 8 minutes

Justin Cave, I think you are right. It's using something other than the explain plan.

I added the hint /*+ LEADING (table_alias, table_alias) */ referencing the first two tables in the explain plan and boom, it's running like lightning again.


What are the differences between Oracle UCM and Oracle Content Server?

found partial answer to this . one is a subset of the other .

" Oracle UCM is a suite of enterprise content management products that include Oracle Content Server and Oracle Site Studio. "

http://download.oracle.com/docs/cd/E13155_01/wlp/docs103/ucm_adapter/intro.html

still not sure the differences .


If you are sure that while starting mongod instance mongo doesn't open all available db files then you should gracefully stop your mongod instance once in a while, while inserting and start it again, for example when opened files counter reaches ulimit on your system. This will close all opened files and open only the ones that you create a collection for.

Another solution is to adjust the number of collections to appropriate level.

Hope this helps.


Unable to connect from remote computer, while local connection works

Rather than using the IP, Use yourdomain\SQLinstance

Also have you allowed the TCP port inbound through the windows firewall on the server side?

Also change server authentication:

Lastly I would change the Pass just incase


Oracle Universal Installer: Install completes, but in (Not responding) state

I wonder if there is a modal window that is under your current installer window that is waiting for you to click finished/done...


How to recover the database with old data?

if you have a base backup AND full WAL archives you can use point in time recovery.  Otherwise you are stuck.  good luck.  I hope you have something you can work with.

Otherwise you are screwed.  There is no way to roll back restoring a backup in this way.  If you didn't drop relations in the process you MIGHT be able to sort things out using min_xid columns on the tables.  In that case you will have a lot of work.....


How do I configure multi master replication on WAMP?

Whether you have Server configured with WAMP or as a Dedicated DB, the concepts for setting up multimaster replication is identical.

Before I go on, let me clarify the phrase multimaster replication.

True multimaster replication would have a Slave with Multiple Masters. It is not possible at present. MySQL 5.6 is approaching this. I think MariaDB as well. What most people call multimaster replication is, in reality, Master/Master (circular) replication.

Master/Master Replication is nothing more that an interleaving of the Master/Slave paradigm between two DB servers. This is what I will answer.

Back on Feb 06, 2012, I wrote a script to create a Slave from a Given Master ( How to setup replication(Master/slave) in MySQL 5.5.20? ). The script is Linux-centric. Since you said WAMP, you should apply the concepts for the Windows environment. I will do my best to translate the concepts:

Scenario

WDB is the WAMP Server (IP 10.1.2.20)
DDB is the Dedicated Windows DB Server (IP 10.1.2.30)

STEP #1

Edit my.ini on WDB and make sure it has

[mysqld]
server-id=1
log-slave-updates
log-bin=mysql-bin
relay-log=relay-bin

If it does not have these, add these to my.ini and then run this

C:\> net stop mysql
C:\> net start mysql

STEP #2

Edit my.ini on DDB and make sure it has

[mysqld]
server-id=2
log-slave-updates
log-bin=mysql-bin
relay-log=relay-bin

If it does not have these, add these to my.ini and then run this

C:\> net stop mysql
C:\> net start mysql

STEP #3

Please open C:\MasterSlaveSetup.bat it in notepad.

set MYSQL_MASTER_HOST=10.1.2.20
set MYSQL_SLAVE_HOST=10.1.2.30
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
set MYSQL_MASTER_CONN=-h%MYSQL_MASTER_HOST} %MYSQL_CONN%
set MYSQL_SLAVE_CONN=-h%MYSQL_SLAVE_HOST} $MYSQL_CONN%

set MYSQLDUMP_OPTIONS=--master-data=1
set MYSQLDUMP_OPTIONS=%MYSQLDUMP_OPTIONS% --single-transaction
set MYSQLDUMP_OPTIONS=%MYSQLDUMP_OPTIONS% --routines
set MYSQLDUMP_OPTIONS=%MYSQLDUMP_OPTIONS% --triggers
set MYSQLDUMP_OPTIONS=%MYSQLDUMP_OPTIONS% --flush-privileges
set MYSQLDUMP_OPTIONS=%MYSQLDUMP_OPTIONS% --all-databases

mysql ${MYSQL_MASTER_CONN} -AN -e"GRANT REPLICATION SLAVE ON *.* TO repluser@'%' IDENTIFIED BY 'replpass'"

set RELOAD_FILE=C:\MySQLData.sql
echo STOP SLAVE; > %RELOAD_FILE%
echo CHANGE MASTER TO master_host='%MYSQL_MASTER_HOST%'," >> %RELOAD_FILE%
echo master_port=3306,             >> %RELOAD_FILE%
echo master_user='repluser',       >> %RELOAD_FILE%
echo master_password='replpass',   >> %RELOAD_FILE%
echo master_log_file='dummy-file', >> %RELOAD_FILE%
echo master_log_pos=1;             >> %RELOAD_FILE%
mysqldump %MYSQL_MASTER_CONN% %MYSQLDUMP_OPTIONS% >> %RELOAD_FILE%
echo "START SLAVE;" >> %RELOAD_FILE%

mysql %MYSQL_SLAVE_CONN% -A < %RELOAD_FILE%

STEP #4

Run C:\MasterSlaveSetup.bat

STEP #5

C:\> copy C:\MasterSlaveSetup.bat C:\MasterSlaveSetup2.bat

STEP #6

Edit C:\MasterSlaveSetup2.bat to reverse the IP Addresses as follows:

set MYSQL_MASTER_HOST=10.1.2.30
set MYSQL_SLAVE_HOST=10.1.2.20

STEP #7

Run C:\MasterSlaveSetup.bat

When done, you should have Circular Replication fully set up. To test it, login to each MySQL instance and run SHOW SLAVE STATUS\G. You should see something like this:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.80.136
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000019
          Read_Master_Log_Pos: 277892198
               Relay_Log_File: relay-bin.000058
                Relay_Log_Pos: 37535484
        Relay_Master_Log_File: mysql-bin.000019
             Slave_IO_Running: Yes  <<------
            Slave_SQL_Running: Yes  <<------
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 277892198
              Relay_Log_Space: 277892637
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

If you see Slave_IO_Running=Yes and Slave_SQL_Running=Yes on both DB Servers, CONGRATULATIONS !!! You did it.

CAVEAT

The Dedicated DB Server does not have to a Windows Server. It could be a Linux server.

To help you understand MySQL Replication more, here are links to my past answers on this. Some of them have answers from others that were accepted. Please check these out and enjoy.

Feb 23, 2011 : MySQL Replication - Introduce new Slave to replication
Dec 17, 2011 : Mysql Master-Master Replication Topologies on >2 machines
Feb 15, 2012 : MySQL replication to slave having VPN connection to Master
Apr 10, 2012 : Slave settings not working
May 07, 2012 : Setting Circular Replication in mysql


How to import a varbinary(max) column with bulk import?

A varbinary value is of the form 0x01020304 for SQL Server to read.

So your CSV would be

col1,col2,varbinary
foo,1,0x01020304
bar,2,0x988776655443

Edit May 2013,

After testing, I can't get this to work with BULK INSERT with or without a format file.

I will post something more when I get it working. I can't delete this answer


Stored Procedure Execute as Owner Close Database Connections Not Working

Using the EXECUTE AS clause of the CREATE {module_type} statement is impersonation, but only at the Database level. The owner is "dbo" (in this case, at least), but that is just a User; a Database-level principal. The KILL command requires an Instance-level permission. By default, when using Database-level impersonation (EXECUTE AS clause or EXECUTE AS USER statement), the process is quarantined to the current Database, even if that User maps to a Login (by having the same SID) that has the appropriate permission. Because of this restriction, the process is not allowed to reach up to the Instance to check Instance-level permissions.

To do this properly:

Get rid of EXECUTE AS
Implement this using Module Signing:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level (blog post of mine; external, but has better explanation)

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service? (one of my answers, here on DBA.SE)

The only permission you should need to grant to the Certificate-based Login is: ALTER ANY CONNECTION (as per the documentation for KILL)

For more info on module signing, please visit: Module Signing Info

Also, in addition to the excellent point made by Aaron Bertrand in a comment on the question, you need to be careful not to kill sessions for legitimate processes, such as SQL Server Agent, etc. You should filter on at least the program_name column of sys.dm_exec_sessions, if not also login_name (and possibly others).

However, if ALTER DATABASE [{db_name}] SET... ends up being the way to proceed, then use the following post as a guide since you won't be concerned with Instance-level permissions in that case:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level


Why are there Victimless Entries on the Deadlock Graph?

ExchangeEvent & e_waitPipeNewRow suggests you've run into what Bart Duncan refers too as Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks".

Most intra-query parallelism deadlocks are considered bugs, although
some of them can be risky bugs to fix so a fix may not be possible.
If you run into one and you're already on the latest SQL service pack,
your best bet may be to investigate workarounds.

So, not much you can do other than:

Ensure you're on the latest service pack and cumulative update.
Try to identify indexes and/or other optimisations to improve the performance of the query. You mention that inputbuf isn't populated but you may be able to identify the query in play via the sqlhandle in the graph XML. If you get nothing from that try running a trace and correlating with the times these deadlocks occur.
Reduce MAXDOP for this query or try MAXDOP(1) to force single-threaded execution. Be aware that you might fix the deadlocks but introduce a different set of performance issues by restricting parallelism.
Open a support call with Microsoft. Possible that a) they have a non-public hotfix for this scenario or b) as these intra-query deadlocks are deemed to be bugs they may want to work with you to find a fix.


am continuously getting the alerts for OS paging High.

Memory is basically two types the physical random access memory (RAM) and virtual memory. A process has to be first mapped to Virtual memory and then to physical memory. All programs use RAM, but when there isn't enough RAM for the program you're trying to run, Windows temporarily moves information that would normally be stored in RAM to a file on your hard disk called a paging file. The amount of information temporarily stored in a paging file is also referred to as virtual memory. Now error page file is less can have multiple meanings

You are running some processes which is heavily utilizing memory and since OS cannot find enough RAM for it it has to use Page file as temporary area for storage.
Since SQL Server is running with Locked pages in memory privilege this memory cannot be paged out to disk and OS cannot directly used this memory by asking SQL Server to release it heavily. So in this case you need to lower max server memory of SQL Server a bit and give more memory to OS so that it can function properly. use how to set optimum value for max server memory to set correct value for max server memory. In your case you have left 10 G for OS which I consider is pretty much good unless you are using SSIS,SSAS and SSRS.

Problem may be not SQL Server memory or memory allocated by AWE API( Locked pages) problem here is windows page file size is less. You need to act on this. Page file is temporary area which is used to hold temporary data which is swapped in and out of physical memory in order to provide a larger virtual memory set. Page file space is reserved when the pages are initially committed, however the page file locations are not chosen until the page is written to disk.

The page file needs of an individual system will vary based on the role of the server, load etc.  There are some performance counters that you can use to monitor private committed memory usage on a systemwide or per-page-file basis.  There is no way to determine how much of a process' private committed memory is resident and how much is paged out to paging files.

Memory: Committed Bytes: Number of bytes of virtual memory that has been committed.  This does not necessarily represent page file usage - it represents the amount of page file space that would be used if the process was completely made nonresident

Memory: Commit Limit:   Number of bytes of virtual memory that can be committed without having to extend the paging files.

Paging File: % Usage    Percentage of the paging file committed

Paging File: % Usage Peak   Highest percentage of the paging file committed

Please use above counters to set proper value for page file. You can read This Link to get more information about page file

You can set page file by Right click on My Computer select properties and then go on Advanced System Settings.In Performance section click on setting and then advanced tab. You would see virtual memory section and then click on change you can set virtual memory here. Please refer to screenshot. Take help of windows team in configuring optimum value. If option automatically set page file for drives is set you can uncheck it and click on SET to set optimum value of page file.

  When i checked using RAMMAP Tool for memory utilization i found that there is AWE which using an approx of 23.8 GB and thus blocking any further memory usage:

This is pretty much normal considering the fact that SQL Server service account is Local system so by default SQL server account will get Locked pages in memory privilege.


JSONB: Finding an element with a null attribute in array of objects


Percona just built a tool to do just that called the Configuration Wizard. I tested it out once just to see what it would return and the results were pretty darn close to what we were using on our servers, whose cnf's were put together by highly trained mysql certified dba's.


Optimizing real-time Database table in Microsoft SQL server 2008

The best strategy for your case is to properly index the table, update statistics and defrag it. That should solve the problem.

There is no inherent limitation on row count in SQL Server. With the right indexes it should not start to become slower.

You need to look at your select and update queries and build useful indexes for them.


I would define levels in the hierarchy:

video_categories
  int     id
  int level
  string  name

I would propagate the parent and child levels into your link table:

video_category_links
  int     parent_id
  int parent_level ((parent_id  , parent_level) foreign_key to video_categories(id, level))
  int     child_id
  int child_level((child_id  , child_level) foreign_key to video_categories(id, level))
  check(child_level > parent_level)


Is there an efficient way to match multiples headers that have the same set of details?

Here is a way using PIVOT and T-SQL that can work IF you have < 255 unique DetailIDs.  I ran into limitation on CONCAT function (2012+) after writing the thing and stress testing it.  It runs pretty well, <5 seconds on 20k headers over 40k rows, with 254 unique detail keys and a lot of matching.  If your set can fit that limitation, might be worth a look.

DECLARE @sql varchar(MAX)
DECLARE @d varchar(MAX)
SET @d = stuff((
        SELECT ',' + QUOTENAME(DetailId)
        FROM (SELECT DetailId FROM DetailTable GROUP BY DetailId) d
        for xml path('')
    ),1,1,'')
DECLARE @tbl TABLE (H int, D varchar(254))
INSERT INTO @tbl
EXEC(
'SELECT HeaderId,CONCAT('+@d+') Details --'+@d+'
FROM
(
    SELECT HeaderId, DetailId, 1 o
    FROM DetailTable
) as s
PIVOT
(
    COUNT(o)
    FOR DetailId IN ('+@d+')
) as pvt'
--Possible subquery and JOIN?
)

SELECT t1.H, t2.H H2  --matches
FROM @tbl t1 JOIN @tbl t2 ON t1.D = t2.D AND t1.H < t2.H

You should be able to get around the CONCAT 254 limitation by replacing the select with just @d, wrapping the PIVOT into a subquery, and stuffing out another @dj for a JOIN.


SSAS Hierachies duplicates when browsing

That behaviour is dependent on your KeyColumns setting.

Given a cube with these 2 named queries in the datasource view

Cities:

SELECT        1 AS id, 'India' AS country, 'Calcutta' AS city
UNION
SELECT        2 AS id, 'India' AS country, 'Bangalore' AS city

Sales:

SELECT        1 AS city, 5 AS salesamount
UNION
SELECT        2 AS city, 5 AS salesamount

A dimension based on cities, linked to the Sales table on the  sales.city->cities.id relation that looks like this, using the id column as KeyColumns:

The result is this:

However, if I change the KeyColumn property to country for that attribute like this:

The result is all sales are reported under one single country:

I agree the documentation is a bit terse:

  Contains the column or columns that represent the key for the
  attribute, which is the column in the underlying relational table in
  the data source view to which the attribute is bound. The value of
  this column for each member is displayed to users unless a value is
  specified for the NameColumn property.

Where you should interpret it as "there is one separate attribute member for each distinct value in the key column, even if the displayed value is the same"

Do note however that changing the key column might require you to change the NameColumn and the OrderBy properties.

Also, KeyColumns can be comprised of multiple columns acting as a unique key instead of a single key like in my example.

Sometimes when it's too risky for existing reports to change the structure already in place it might be a good option to add an extra attribute.


if your data is mission critical and you really cannot move forward without it there are some very specialist tools on the market for recovery. There are only small amount of experts that know how to do this properly. I spent some years working at a consultancy that could do this with varying degrees of success and it was very expensive in terms of $ and time. Take a look on https://recovery.twindb.com/ to see if there's anything there that can be useful.

Don't forget to set your backups up to avoid this situation again in the future. There are plenty of tools out there that make this simple for you.

https://www.percona.com/downloads/XtraBackup/LATEST/

https://www.binlogic.io/


Database Properties Error - sys.spt_values missing but dbo.spt_values works?

Answer compiled from comments by the question author

Following a suggestion by Ben Thul to try connecting using the Dedicated Administrator Connection (DAC), that was it: I was able to connect over the DAC wtih SQLCMD and run

select * from sys.spt_values

From there I ran the u_Tables.sql script and it looks like we're good to go.

I'm not familiar everything that is different when using the DAC, but that connection allowed me to see the sys.spt_values object.  I had not been otherwise able to query against it.  Since the object was now visible over the DAC, I could execute the u_tables.sql rebuild script successfully.

It is still unclear what caused the original issue. I was contacted by a client regarding the error in our Test environment. The issue did not impact any other functionality besides the properties dialog box in SSMS.

Several sources said to run the u_tables.sql script to fix the issues. When I inspected the script, I tried to run just the sql within the view and kept getting the error about an invalid object.

This was true regardless of how I connected through SSMS. It also meant the u_tables.sql would not execute successfully.  The DAC connection allowed me to see the necessary objects.


Under normal operating conditions, shrinking your transaction log file is not recommended. The file grows large enough to handle your current active transactions (with SIMPLE recovery model), or to handle transactions between your transaction log backups (with FULL recovery model).

Unless something very unusual has happened, your shrinking the transaction log file simply means that it will have to grow again.

If your transaction log file is filling up your drive, you should look into the following:

If your database is in the FULL recovery model, do you have regular transaction log backups running? Are they running to a different drive/server? Can you increase the frequency of the transaction log backups? (This last will increase the frequency with which completed transaction activity is released from the log, so the log file should not need as much space)
If your database is in the SIMPLE recovery model, do you have very large transactions running? Can you break those transactions into smaller chunks?
Can you add space to that drive? Alternately, can you add a new drive, so you can add a second transaction log file there?

That said, if you have had an unusual event:

A database in the FULL recovery model where transaction log backups were not being taken;
a log file that grew beyond its normal size due to a rare bulk load of data;
a database that has permanently been switched from FULL to SIMPLE recovery model, where transaction log backups had been done relatively far apart (for instance, once a day).

If you are going to try to shrink a transaction log file, it is easiest to do so immediately after a backup has been taken. If backups are taken regularly, then you don't need to run a backup manually; you can simply wait until a backup completed, then try to shrink the file.

If you don't know what's a good size to shrink to, take your best guess based on the activity that expanded the file - if you have a 350GB transaction log file after running for about a year with no transaction log backups, for example, I'd start with 512 MB. I'd also set the growth factor for the transaction log to a specific value, rather than X% of the current value. In general, I'd aim for about half the starting size, but that's just me.

And, yes, I do believe there are special considerations when the database is involved in mirroring, replication, or always-on; however, I'm not certain what they are.

To expand a bit on what a transaction log backup does:

The transaction log is a record of every transaction that's been committed to your database, as well as any transactions currently running.

Under the SIMPLE recovery model, transaction log data no longer is needed once the transaction is committed. At that point, the space in the log file that's taken up by that transaction can be reused.
Under the FULL recovery model, transaction data is kept in the log file until both the transaction is committed, and the data has been backed up via a transaction log backup.

Transaction log backups allow you to perform "point-in-time" recovery.

If your database crashes at 5PM on Friday, and you take a full backup on Sundays, then you have lost all the data for the past week. Even if you take a nightly differential backup, you've lost all of Friday's data. If you run a transaction log backup every 3 hours, then the most data that you will lose would be three hours' worth.

With transaction log backups, you can also choose not to restore through the point where the last backup was taken, but to a specific time before that (hence, "point-in time"). If someone did something to the database 4 hours ago that basically destroyed it, then you want to restore your full backup, then your transaction logs up to 4 hours and five minutes ago. The transaction log includes information on exactly when each change was applied, so that will get back your database to how it was just before this disastrous change was made.

You mention that after a transaction log backup, you looked at your log file and it had about 20GB of unused space; if you shrink it, that space is indeed released from the file, and available for other uses.

However, I'd try looking at how much space is free in the file right before each backup happens. If there's less than 1 GB left free? Leave the file at 20 GB. You see, if you shrink it to, say 100MB, but it really needs 20 GB every three hours for your transaction load, then the system will simply need to reclaim that20 GB over the course of the next three hours. This is bad for 2 reasons:

SQL Server takes some time to add space to the log file; unlike data files, this cannot be done "instantly" by turning on an option.
If a 20 GB log file has eaten up all the space on your disk, and you've shrunk it to 100 MB, what happens if someone else drops a 10 GB file onto your hard drive a hour after you shrunk the log file? Around 30 minutes later, anyone trying to make a change to the database will get an error, because the log file is full.

Even if the log file has 19 out of 20 GB free at the end of an hour, you still may not want to shrink it down to (say) 2 GB. Something made it grow to 20 GB in the first place. You may have a job that runs overnight, or once a week on the weekend, that creates a lot of transactions, with a lot of data. If that is happening on a weekly basis, you'll need that 20 GB at least once a week. Why shrink the file to the 2 GB it needs Monday to Saturday, when every Sunday it will have to grow back to 20 GB? You've still got the two problems I mentioned above, and if you do run out of log space while some major off-hours process is running, it may take more than just the time to free up space for the log to resolve the problem fully, which might just ruin your weekend. Or, even worse, you don't find out there was a problem until Monday morning, and this system may be functionally down for users for most of the day!

By the way - If your transaction log does record 20 GB of changes in 3 hours, I'd want to be taking my transaction log backups much more frequently: maybe every 5 minutes.

See also: The Transaction Log (SQL Server) in the product documentation.


The WHERE codition has a function that acts on a column. This means that even if an index is used, it will have to do a complete index scan. In such cases, it is good (if possible) to rewrite the condition so the column is used bare, and any function is applied to constants or run-time constants (like NOW()). This way an index can be used with an index seek - and a partial index scan, only rows that are needed will be scanned.

Try rewriting

TIMESTAMPDIFF(MINUTE, tmUpdate, NOW()) <= 30

to:

timestampadd(MINUTE, -30, now()) >= tmUpdate

and add an index on either sat_msgPos (idVeh, idTrigger, tmUpdate) or sat_msgPos (idTrigger, idVeh, tmUpdate).


Simple indexed select query takes 350 seconds (?!) even though Filtered 100.00 "Using index condition"

The optimal index for this query would be a composite index with 4 columns, in any order:

INDEX(contact_id, job_class_name, execute_at, deleted_at)

And, depending on the order you pick, you can get rid of one of the existing KEYs.

Watch out for backslashes.  'App\Jobs\MyJob' may be treated as App[LF]obs[CR]yJob or AppJobsMyJob.

352 seconds is really unreasonable.  Perhaps something else was going on, and blocking access to the rows and/or the table? (The 352s was bogus.)

"Using index condition" is not the same as "Using index".  The latter indicates a "covering index", which is not practical for this query and table.


Shell: How to time a script running in SQLPlus and kill it after x amount of minutes?

In Oracle Database, you can use Database Resource Manager (DRM) to manage memory, CPU, time and other resources.

Usually your workflow is as follows:

Create a resource plan
Create the resource consumer groups
Create the resource consumer group mappings
Create the resource-plan directives

You create a plan with the DBMS_RESOURCE_MANAGER.CREATE_PLAN procedure. Plan name is the only required parameter:

DBMS_RESOURCE_MANAGER.CREATE_PLAN('myplan');

Then you create the resource consumer groups as needed with DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP:

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    consumer_group => 'limit_5_min'
  , comment => 'users with 5 minute execution time limit');

After the necessary resource consumer groups are created, you can define resource consumer group mappings with DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING based on the session attribute so that the new and existing sessions are automatically assigned to appropriate resource consumer groups:

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
    attribute => DBMS_RESOURCE_MANAGER.CLIENT_OS_USER
  , value => 'spongebob'
  , consumer_group => 'limit_5_min');

This mapping will assign all the session made from the OS user spongebob to the resource consumer group created earlier. There are a number of other attributes you can use to map the session to consumer groups, for example, ORACLE_USER. See DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI procedure parameters table for a full list of available attributes.

Now is the most important part. You will create the resource-plan directives that will govern the resource usage by the users. You use the DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE procedure to create the directives. Let's create the directive which limits the session execution time:

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'myplan'
  , group_or_subplan => 'limit_5_min'
  , switch_time => 300
  , switch_group => 'KILL_SESSION');

This directive means that every session created from the OS user spongebob and executes more than 300 seconds (5 minutes) will be automatically killed by DRM.

You will also need to grant the users privileges to switch to the appropriate resource consumer groups with DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP procedure, otherwise the mapping will not work:

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
    grantee_name => 'scott'
  , consumer_group => 'limit_5_min'
  , grant_option => false);


What does TOAD do, when I terminate script execution?

Trace your session. I expect you will find Toad is waiting for Oracle to roll back your transaction - which Oracle will continue to do in the background even if Toad is killed.

Under the covers, when you abort a query, an Oracle client application sends OCIBreak(). However in order to actually process this, the shadow process on the Oracle side has to come back and read that message - it is a request only, there is no way to send a true interrupt (i.e. a signal in Unix terms) across the network. If the shadow process is in a state where it is itself waiting for an operation to complete (such as a big I/O request to the OS) then it is not until that completes that it can advance to the next "tick" and find the break command waiting for it.

If you are really impatient, you could get the same result by finding the shadow process (v$process.spid where addr = v$session.paddr and sid=<your sid>) on the server and just issuing a kill command at the shell.

Incidentally there is some clean-up to do when a long-running SELECT is aborted: the previous versions of the rows are no longer needed.


All you need to do is ORDER BY startDate ASC first, then featured DESC:

SELECT
    a.* --,
    -- c.id as catid,
    -- c.cat_name
FROM eventlisting a
-- LEFT JOIN categories c ON a.cat_num=c.id
WHERE a.startDate >= '2015-08-21'
AND a.endDate <= '2016-08-21'
AND a.status=1
GROUP BY a.id
ORDER BY a.startDate ASC,
         a.featured DESC
LIMIT 0, 50;

Note: I've commented the categories table because you didn't specified which is its structure.

Test:

mysql> SELECT  a.*            FROM eventlisting a   WHERE a.startDate >= '2015-08-21' AND a.endDate <= '2016-08-21'  AND a.status=1  GROUP BY a.id  ORDER BY a.startDate ASC,  a.featured DESC LIMIT 0, 50;
+----+----------+--------+------------+------------+
| id | featured | status | startDate  | endDate    |
+----+----------+--------+------------+------------+
|  3 |        1 |      1 | 2015-08-21 | 2015-08-25 |
|  1 |        0 |      1 | 2015-08-21 | 2015-08-21 |
|  2 |        0 |      1 | 2015-08-21 | 2015-08-22 |
|  5 |        1 |      1 | 2015-08-22 | 2015-08-27 |
|  7 |        1 |      1 | 2015-08-22 | 2015-08-31 |
|  6 |        0 |      1 | 2015-08-22 | 2015-08-31 |
+----+----------+--------+------------+------------+
6 rows in set (0.00 sec)

mysql>

Test it in this Fiddle.


Is it possible to make schema changes on a replicated subscriber database?

Yes, this can be done. It isn't recommended as if you need to re-initialize the subscription the column will be lost and the values would need to be repopulated.  Can the value be stored as a computed column?  If so, then just add it to the publisher which would then replicate the change to the subscriber.


Automatically purge dropped tables in Oracle

Is there any option to tell Oracle to immediately purge dropped tables?

That would be disabling the recycle bin, if only for the current session. Include ALTER SESSION SET recyclebin = OFF in your build process, or, if this is a development system, you can consider turning it off via the instance initialization parameter.

Alternatively, you can choose running the build as a dedicated Oracle user; you will then DROP USER ... CASCADE at the end, which will drop all user's tables and other objects without placing them in the recycle bin.


database design - psychometric testing layout

Based on your information, let have a draft ERD like below:

TEST
+ ID (PK)
+ NAME (UK)
+ ...

TEST_QUESTION
+ TEST_ID (PK)
+ QUESTION_ORDER (PK)
+ QUESTION_CONTENT
+ ...

INDIVIDUAL
+ ID (PK)
+ MEDICAL_ID
+ ...

TEST_LOG
+ ID (PK)
+ INDIVIDUAL_ID (FK to INDIVIDUAL)
+ TEST_ID (FK to TEST)
+ ...

If we're talking about to optimize the reporting, we should denormalize the database structure, that means to accept the data redundancy.
In that case, we can merge TEST and TEST_QUESTION into one table.

Hope this help for an idea.


Moving a SQL Server TempDB of AlwaysOn members

is there a risk that something can get corrupted regrading data and availability group listener

No, tempdb is recreated when SQL server restarts. If your tempdb is large, Make sure to enable instant file initialization for data files only.

The instructions are fine and follow the comment that I put in.(will add it in answer later)


How to find Oracle's installed version and release from a program?

If the database homes were installed properly, the central inventory has a list about them.
The central inventory on Windows is located at C:\Program Files\Oracle\Inventory. On Linux/UNIX platforms, the location of the central inventory can be found in /etc/oraInst.loc. In the inventory, in ContentsXML\inventory.xml, there is a list of installed homes in XML format, e.g:

<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>12.1.0.2.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraGI12Home1" LOC="/opt/oracle/grid12102" TYPE="O" IDX="1"/>
<HOME NAME="OraDB12Home1" LOC="/opt/oracle/base/product/db12102ee" TYPE="O" IDX="2"/>
<HOME NAME="OraDb11g_home1" LOC="/opt/oracle/base/product/db11204ee" TYPE="O" IDX="3"/>
</HOME_LIST>
</INVENTORY>

You can parse this list, and find detailed information with opatch for example:

/opt/oracle/grid12102/OPatch/opatch lsinventory -oh /opt/oracle/grid12102 -details
/opt/oracle/base/product/db12102ee/OPatch/opatch lsinventory -oh /opt/oracle/base/product/db12102ee -details
/opt/oracle/base/product/db11204ee/OPatch/opatch lsinventory -oh /opt/oracle/base/product/db11204ee -details

A Windows example would be:

C:\oracle\base\product\12.1.0\dbhome_1\OPatch\opatch lsinventory -oh C:\oracle\base\product\12.1.0\dbhome_1 -details

The above needs to be run on the server of course.

This is the best-case scenario, with proper installations. Worst-case scenario is, homes use separate inventories, or use no inventory at all, and you have to search all filesystems on the server for possible Oracle installations.


How to concatenate psql variables in tablename?

psql's pattern matching is not strong feature, so your query cannot be performed.

When parsing your string, psql finds the :prefix_ws_ since it cannot match/find :prefix, but that variable isn't defined by -v option, so psql ignores it. Next, psql finds :suffix, then replaces to group since it is defined.

Additionally if you write "select * from :prefix:suffix", psql can replace both variables such as "select * from mygroup".


Oracle Upgrade Standard to Enterprise Edition

You can simply install Enterprise Edition in a different folder, and you can start the databases using that as ORACLE_HOME.

This way you can shutdown the databases running from Standard Edition ORACLE_HOME and startup databases using the Enterprise Edition ORACLE_HOME one at a time, whenever it is feasible.

But you will not be able to skip the install part. There is no official and supported method for converting an existing Oracle software installation from Standard to Enterprise Edition.


You requirement can be translated into a CASE statement expression almost word for word.

SELECT
CASE
    WHEN channel.export is not null then channel.export
    WHEN channel.group_id is null then false
    ELSE "group".export
END as merged_bool
FROM channel LEFT JOIN "group" ON (group_id="group".id);


Unexpected behaviour with InnoDB Transaction retry

When the deadlock occurs at query 3, the deadlock may involve query 1 or 2.  You must start over with the START TRANSACTION and replay all the queries.
If any of those are SELECTs and the result of the SELECT controls what will be subsequently UPDATEd, DELETEd, etc, then do you have FOR UPDATE on the SELECT?
You must check for errors after each and every query in the transaction.  If a deadlock occurs, start over, as mentioned above.
What happened

The error occurred, so it rolled back the transaction as far as it had gone.
The client code ignored the error and plowed ahead.
Queries 4,5,6 were run and committed.

What was needed

The error occurred, so it rolled back the transaction as far as it had gone.
The client code noticed the error.
The client code should have code to ROLLBACK and jump back to the START.

(The explicit ROLLBACK is probably redundant but harmless.  However, branching back to the START is up to the client code.)
A "deadlock" should be handled that way, but any other "error" should be handled with an abort.
And, since a deadlock can occur after virtually any query, you should test everywhere.


Assuming you are doing native backups to disk, and then Veritas backs up to tape:

Do your onsite backups to disk. Setup blob storage on Azure (or S3 with Amazon) and then script copying that file to that storage. You would then have Veritas backup that file as well from the local disk to tape.
If you happen to be on SQL Server 2012 or higher you can backup directly to Azure storage. The Azure storage, you can select geo-redundant service and this will allow your backup to be duplicated between Microsoft data centers. You could consider actually dropping doing backups to Veritas with this option, but an extra copy doesn't hurt to have locally.

Response to your comment

  We do backup to external disk. We no longer use tapes. I want to do a
  full onsite backup each day so if something went wrong then i could
  restore the full database back quickly. I just also want an offsite
  copy. Is this possible? We are using sql server 2008 R2. Does azure
  support incremental/differential backups with sql 2014 or will it only
  keep one copy which is the latest?

Think of Azure storage as an external disk, it just sits in Microsoft Data Centers. Copying your backups to Azure provides you the ability if your building gets wiped away, bring up another server with Internet access and retrieve the last backup you copied up.

Azure storage (blog storage) allows any file type, the only limit is one single file cannot exceed 1TB in size. If in the future your backups grow to that size, you will just need to split the backup into multiple files to keep it under that limit.

SQL Server 2012 and higher allow backing directly to Azure via BackupToURL. You can utilize Microsoft® SQL Server® Backup to Microsoft Azure®Tool to do similar action across any version of SQL Server. There is a write up done for using this tool here.

Azure does not delete or clear out old backup files for you, so in your design of the backup solution using this service you will need to also deal with cleaning files out if needed.


How to configure multi-site SQL Server 2014 Always On Availability Group for DR

Yes the DR nodes need to be joined to the existing cluster. SQL Server 2016 has Distributed Availability Groups, which would a cluster at DR separate from the existing one, but you are on SQL Server 2014. The DR nodes should be able to see the fileshare witness. You can adjust quorum votes so that the DR nodes don't have a say  in if quorum is down. I do add a fileshare in DR for when we failover to it, but it's not part of the cluster until the failover occurs.
The connection string would specify the AG listener and would not specify the two IPs. MultiSubnetFailover=True takes care of it by connecting to the IP that returned success first.
Yes it is very reasonable to swap roles. The last 3 companies I worked for had DR sites that we'd failover to 1-2 times per year, running production at DR for a few days and up to 3 weeks. Failovers are an offline event. They take about 15 seconds on AGs.


Data-wise, tinyint(1), tinyint(2), tinyint(3) etc. are all exactly the same. They are all in the range -128 to 127 for SIGNED or 0-255 for UNSIGNED. As other answers noted the number in parenthesis is merely a display width hint.

You might want to note, though, that application=wise things may look different. Here, tinyint(1) can take a special meaning. For example, the Connector/J (Java connector) treats tinyint(1) as a boolean value, and instead of returning a numerical result to the application, it converts values to true and false. this can be changed via the tinyInt1isBit=false connection parameter.