How to normalize a database which uses tables as references to other tables?

As per your other post, if you require such delete requirements, it is surely a poor design.

Secondly, there is no "so called" The Best Design.

Third, do not get struck with heavy normalization though essential. Design in such a way that you arrive at a maximum normalization and put it to use. While Beta testing...you shall refine to an extent.

And finally, even if we review / amend your design details, that might not be a best match for your exact requirement. instead, with your given details, if you would have added any particular issue which you foresee, we shall advise on the same.


How to use IN to compare a single value with a comma separated field in another column

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

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


PostgreSQL text search email parser does not handle + correctly

I don't have good news for you.  If you could convince the PostgreSQL hackers that such a change is desirable, it probably still would not be made in the default parser. Because then a run of pg_upgrade would invalidate all existing FTS indexes, and they would have to be rebuilt.  Even if the change in behavior was acceptable to impose between major versions, the index invalidation probably would not be.

You could fork the default parser to implement your own, but that is unreasonably difficult.  While the CREATE TEXT SEARCH PARSER doc does document the names of the functions you need to implement, it gives no inkling into what those functions need to do, or even what their call signature is.  It just refers you elsewhere, which in turn describes the behavior of the default parser, but in a way which is not useful for trying to implement your own parser. Finally, "contrib" offers no examples from which you could find inspiration, and I am not aware of reasonable examples anywhere else, either.  There is some very old documentation, but it isn't clear how relevant it still is.

If you come up with a more satisfying solution, please do post your own answer about it.

Is your goal to have '+' behave the same as it currently does in every case except where it appears in an email?  Or would you want '+' to be treated in general like a word character, not just in emails?  Is '+' the only thing that is giving you trouble?  Some of the other allegedly-valid examples on wiki also are not processed correctly, but those may be of no consequence to you.

Before starting to implement my parser, I would test to see if I could implement something acceptable at an earlier level, for example by just replacing '+' with '.' in both the documents and queries.


One SQL Server 2014 giving different password hash

This is because since SQL Server 2012 the hash algorithm has changed.

Starting from this version, the hash is twice as long.
You can read up on the three hash versions that are used for the various versions of SQL Server in the sp_help_revlogin microsoft article, under Remarks:

  A password can be hashed in three ways:
  VERSION_LEGACY: This hash is a 16-byte pre-SQL Server 2000 hash.
  VERSION_SHA1: This hash is generated by using the SHA1 algorithm and is used in SQL Server 2000 through SQL Server 2008 R2.
  VERSION_SHA2: This hash is generated by using the SHA2 512 algorithm and is used in SQL Server 2012.

Which means that if you want to move a SQL Server 2012 or higher user to a 2008r2 instance, you'll need to find a different method.

I tested it quite a bit now. Any user created under SHA1 when moved using the password hash to a new version server (2012+) can then be moved back as long as the password isn't changed. However any new user created in 2012+ will not be moveable to 2008r2 or prior.


Using wildcards in a like statement on an unindexed VARCHAR(MAX) column with more than 1 million records

If snapshot isolation is enabled you will not have any blocking issues. If not, you should probably run the query under READ COMMITTED or even READ UNCOMMITTED. It is a common myth that a READ COMMITTED scan locks the table.
You can use Resource Governor for this. Or use a MAXDOP 1 hint. Controlling load of bulk operations is very hard with SQL Server. Depending on the situation you might be 100% fine leaving this running all day, or you might induce timeouts in other parts of the workload. It is not unreasonable to run the query for 10s and cancel it. Then determine whether the application workload was impacted or not.
I like to do progress estimation by dividing the table size (in MB) by the observed disk read rate (in MB/sec). This gives an estimation for the total scan time.

Fulltext search cannot help you because it works on a per-word basis. You'd need to plug in a custom stemmer that knows how to split special characters. Unrealistic. Your query is fine.


I don't have Microsoft Access, but I created a test in MySQL.

My table (freddy) - don't pay attention to the name:

mysql> select * from freddy;
+-------+----------------+
| regno | email          |
+-------+----------------+
|     2 | blah@blah1.com |
|     2 | blah@ah1.com   |
|     2 | blah@ah1.com   |
|     4 | blah@ah1.com   |
+-------+----------------+
4 rows in set (0.00 sec)

Then run the following query:

SELECT regno, email FROM freddy GROUP BY regno, email;

+-------+----------------+
| regno | email          |
+-------+----------------+
|     2 | blah@ah1.com   |
|     2 | blah@blah1.com |
|     4 | blah@ah1.com   |
+-------+----------------+
3 rows in set (0.00 sec)

So, as you can see, the 2 rows 2, blah@ah1.com have been deduplicated. So, you run a similar query on your customer_account_code (GROUP BY - or since you're only selecting on 1 field, you can use DISTINCT) e.g.

mysql> SELECT DISTINCT(regno) FROM freddy;
+-------+
| regno |
+-------+
|     2 |
|     4 |
+-------+
2 rows in set (0.00 sec)

Then set up your form to bring these up and allow your user to add a company_reg and company_email.

However, this is very close to a programming (and not a DBA) question. If you have more trouble with the SQL, then come back here, but otherwise, I think you need an Access and/or Programming forum - StackOverflow perhaps?


How to structure a database to show data for each USA State and Canada Territory on a Per user basis?

Well, I guess I will have to make assumptions about what your user and campaign data look like.

Maybe a schema like this:

Users
-----
  id (PK)

Campaigns
---------
  id (PK)
  user_id (This it the "user owns a campaign" relation, FK to users.ID)
  (other fields)

territories
-----------
  id (PK)
  name
  country

territories_campaigns
---------------------
  id (PK)
  territory_id (FK to territories.id)
  campaign_id (FK campaigns.id)

You use the territories_campaigns table to determine which campaign is happening in which territory. The structure of this table allows a campaign to span multiple territories, and each territory can host multiple campaigns.

Since the campaign table refers to the user who owns it, to build a map for a specific user, make sure you filter on the user_id


Restore from .db folder for Analysis Services

Yes.  Attaching the database files for SSAS is a documented and supported operation.  But unlike SQL Server Database Engine I wouldn't count on it working if the installed version of Analysis Services doesn't match the version that was used to run the database initially.


mysqldump -h source_server... --order-by-primary --where='id > ...' src_db, tbl  |
    mysql -h dest_server ...

But that assumes

id is the PRIMARY KEY
You can get the id 100K (or so) rows from the end:  SELECT id FROM tbl ORDER BY id DESC LIMIT 100000, 1
You can access both servers from wherever you run the pipeline.

Since you need 2 connections, there is no 'simple' way to do it from the mysql commandline tool or from client code (PHP, Java, etc).  Copying one row at a time would quite slow.  Employing LOAD DATA will be no better than mysqldump + mysql.


Index Fragmentation Monitoring: Can I make this query faster?

I would suggest reducing the number of joins in your loop.  You are already hitting sys.indexes and sys.schemas in the your first query to build your temp table.  It would be advisable to just grab whatever additional fields you need and throw them into your temp table as well.

You want to make your query that you are looping through in your cursor as efficient as humanly possible.  You might even want to try and get the data you needed from sys.objects in there too.

Speaking of cursor you should consider using a fast_forward cursor as well.  It has a smaller footprint and is a bit more efficient.


How to create mysql user with CREATE USER grant but without access to new databases?

With the GRANT command, you can use the ON keyword to set table privileges for users.

For example: GRANT ALL ON user_db to 'write_user'@'localhost'


Postgresql Write Only Primary Server Bottleneck for Streaming Replication

This depends almost entirely on the workload - and has little or nothing to do with whether it's a standalone DB server or a streaming replication master.

Ideally you want your DB to fit within RAM. If it doesn't, add RAM until it does or you run out of money.

If you can't sensibly make your DB fit within RAM, make sure there's enough RAM to hold the heavily used indexes and any really hot tables if possible. Then start spending money on fast disk I/O instead.

If you do more than a tiny bit of writing, especially if it's not just inserts, ensure that your storage has fast disk flushes. You need this even if the database fits entirely in RAM. That means battery-backed write-back cache on a RAID controller, or good quality SSDs with proper, reliable power-loss protection.

For CPU, again, it depends on the workload. If you're I/O bound, lots of CPU is generally unnecessary. If you're not I/O bound (i.e. the DB mostly fits in RAM) then lots of CPU will benefit you more. Because PostgreSQL can't use more than one core per query (grr), workloads with few big queries want few fast cores, and workloads with lots of simple queries want lots of small cores.

Does this sound like a long and roundabout way of saying "it depends, benchmark against your workload"? Yep!

(BTW, read http://wiki.postgresql.org/wiki/Number_Of_Database_Connections and consider a connection pooler, no matter what your workload).


Can I separate user and system trace files in Oracle 11g?

I do not think that you can set the user and background dump destinations in 11g onward. One thing that might help is the TRACEFILE_IDENTIFIER parameter. I set this when I do a trace so that it is easy to find in the trace directory.

For example I setup a trace like this:

alter session set max_dump_file_size = unlimited;
ALTER SESSION SET tracefile_identifier = 'bobbydurrett';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

My trace file has my name "bobbydurrett" in it so it is easy to search for it.

Bobby


Can we update multiple record with join of two query

Try merge instead, e.g.

merge into oiu o
  using (select u.usr_key
         from user u
         where u.usr_status = 'Active'
           and u.usr_udf_isexecutiveuser = '1'
        ) x
  on (o.usr_key = x.usr_key)
  when matched then update set
    o.oiu_prov_mechanism = 'RECONCILIATION',
    o.pol.key = null,
    o.oiu_policy_based = null,
    o.oiu_policy_revoke = null
  where o.oiu_prov_mechanism = 'AP HARVESTED';


Does SQL server log when and who enabled / disabled jobs by default?

Simple answer would be no. I don't think there is any system table that captures or logs this change. However you can find the details from last modified date of sql job for any change.

Please refer to some great insights as answered  here by Aaron and Crafty DBA. This should help for future reference.


Replicating some tables from one postgres database to another

On PostgreSQL 9.3, you could use postgres_fdw to transparently query the foreign table on the other machine.

On older versions, dblink can be an option as mentioned by Andrew.

Another option is to use a tool like Londiste or Slony-I to replicate the tables you want. I recommend using Londiste for this, it's going to be much simpler. It creates triggers on the table to detect insert/update/delete, and replicates these using its own client/server and a queueing system to the other database, where it does corresponding insert/update/deletes. I use it in production on several client sites and it works very well.

A future option (hopefully in PostgreSQL 9.5) will be log streaming logical replication, logical changeset extraction and bidirectional replication, which will allow individual databases or tables to be replicated at the SQL level. Part of the work for this was committed to PostgreSQL 9.4, but not enough to make it useful for what you want to do.


The only way I know of is one I can't recommend.

If you convert your EERD into a relational design (with tables, etc.) using some technique like "class table inheritance", you have now disguised the fact that the original EERD contained a generalization/specialization pattern.

If you now turn around and reverse engineer that back to an ERD, you have something that satisfies the requirements of your question.  But that's grotesque.  It conceals the original intent rather than revealing it.  Why would anyone ever want to do that?


MySQL (Percona): Multiple simultaneous long-running connections

There have been only 92 Connections in the 5 hours of Uptime, and no more than 4 at once (Max_used_connections).  So I don't understand your question about struggling with 3K connections.

Observations:

Version: 8.0.15-6
32 GB of RAM
Uptime = 04:54:20; some GLOBAL STATUS values may not be meaningful yet.
Are you sure this was a SHOW GLOBAL STATUS ?
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Extremely high disk activity on the buffer_pool.  Since you have 32GB, and assuming it is mostly for MySQL, change innodb_buffer_pool_size to 22G.

Why so many CHECK, SET, DELETE, SHOW COLUMNS statements?

You seem to have some really big queries.  Turn on the slowlog to catch the worst of them.  For example, maybe a DELETE is missing a useful index?

The table cache is not very effective, increase table_open_cache to 2000.

Max_connections = 15574 is quite high.  Especially since the most used is only 4.  Drop it to 100.

Details and other observations:

( Innodb_buffer_pool_reads ) = 28,825,842 / 17660 = 1632 /sec -- InnoDB buffer_pool I/O read rate
-- check innodb_buffer_pool_size

( Innodb_buffer_pool_pages_flushed ) = 61,160,824 / 17660 = 3463 /sec -- Writes (flushes)
-- check innodb_buffer_pool_size

( innodb_buffer_pool_size / _ram ) = 512M / 32768M = 1.6% -- % of RAM used for InnoDB buffer_pool

( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 979 / (8530 + 979) = 10.3% -- Effectiveness of table_open_cache.
-- Increase table_open_cache and check table_open_cache_instances.

( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests ) = 28,825,842 / 997216830 = 2.9% -- Read requests that had to hit disk
-- Increase innodb_buffer_pool_size if you have enough RAM.

( Innodb_pages_read / Innodb_buffer_pool_read_requests ) = 50,177,127 / 997216830 = 5.0% -- Read requests that had to hit disk
-- Increase innodb_buffer_pool_size if you have enough RAM.

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((28825842 + 61160824) ) / 17660 = 5095 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size?

( innodb_log_buffer_size / innodb_log_file_size ) = 64M / 48M = 133.3% -- Buffer is in RAM; file is on disk.
-- The buffer_size should be smaller and/or the file_size should be larger.

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 248,832 / (17660 / 3600) / 2 / 48M = 0.0005 -- Ratio
-- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 17,660 / 60 * 48M / 248832 = 59,535 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( Innodb_rows_deleted / Innodb_rows_inserted ) = 201 / 0 = INF -- Churn
-- "Don't queue it, just do it." (If MySQL is being used as a queue.)

( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 50177127 + 224 ) / 17660 / 200 = 1420.6% -- If > 100%, need more io_capacity.
-- Increase innodb_io_capacity if the drives can handle it.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.

( expand_fast_index_creation ) = expand_fast_index_creation = OFF -- ALTER and OPTIMIZE may be greatly sped up by using ON.
-- Probably better to be ON.

( innodb_thread_concurrency ) = 0 -- 0 = Let InnoDB decide the best for concurrency_tickets.
-- Set to 0 or 64. This may cut back on CPU.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( max_connections ) = 15,574 -- Maximum number of connections (threads). Impacts various allocations.
-- If max_connections is too high and various memory settings are high, you could run out of RAM.

( (Com_show_create_table + Com_show_fields) / Questions ) = (0 + 436) / 1804 = 24.2% -- Naughty framework -- spending a lot of effort rediscovering the schema.
-- Complain to the 3rd party vendor.

( local_infile ) = local_infile = ON
-- local_infile = ON is a potential security issue

( Select_scan / Com_select ) = 217 / 199 = 109.0% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries.

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( back_log ) = 15,574 -- (Autosized as of 5.6.6; based on max_connections)
-- Raising to min(150, max_connections) may help when doing lots of connections.

( thread_cache_size ) = 16,384 -- How many extra processes to keep around (Not relevant when using thread pooling) (Autosized as of 5.6.8; based on max_connections)
-- Greater than 100 may lead to OOM.

( thread_cache_size / Max_used_connections ) = 16,384 / 4 = 409600.0%
-- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.

Abnormally small:

Bytes_received = 6.2 /sec
Bytes_sent = 145 /sec
Com_insert = 0
Com_select = 41 /HR
Handler_read_key = 0.45 /sec
Handler_write = 0.33 /sec
Innodb_background_log_sync = 0
Innodb_data_writes = 77 /HR
Innodb_data_written = 222 /sec
Innodb_pages0_read = 0
Innodb_rows_inserted = 0
Innodb_secondary_index_triggered_cluster_reads = 0.32 /sec
Max_used_connections = 4
Open_files = 2
Select_range = 0
Table_locks_immediate = 2.7 /HR
Table_open_cache_hits = 0.48 /sec
innodb_default_encryption_key_id = 0

Abnormally large:

( Innodb_pages_read + Innodb_pages_written ) / Uptime = 2,841
Com_check = 22 /HR
Com_create_db = 0.2 /HR
Handler_read_next / Handler_read_key = 51,277
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 33,884
Innodb_buffer_pool_pages_made_not_young = 12596 /sec
Innodb_buffer_pool_read_ahead = 1209 /sec
Innodb_data_pending_reads = 0.82 /HR
Innodb_data_read = 41774567 /sec
Innodb_data_reads = 2841 /sec
Innodb_pages_read = 2841 /sec
Threadpool_idle_threads = 9
Threadpool_threads = 11
gtid_executed_compression_period = 0.057 /sec
host_cache_size = 1,381
innodb_max_dirty_pages_pct_lwm = 10
innodb_undo_tablespaces = 2
max_error_count = 1,024
max_length_for_sort_data = 4,096
slave_pending_jobs_size_max = 128MB

Abnormal strings:

bind_address = 0.0.0.0
default_authentication_plugin = caching_sha2_password
event_scheduler = ON
have_ssl = YES
have_symlink = DISABLED
innodb_fast_shutdown = 1
innodb_undo_log_truncate = ON
optimizer_trace = enabled=off,one_line=off
slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN
thread_handling = pool-of-threads
transaction_write_set_extraction = XXHASH64


No tables found after running Expdp and Impdp in Oracle 12c

I couldn't find what went wrong in your procedure and reproduce the same scenario either. I can help by demonstrating how to export one schema to another or in to the same database. I created the following example for this purpose.

Disclaimer: I don't have 12c instance to test it right now, but same procedure should work for 12c as well.

SQL> create user myschema identified by myschema;

User created.

SQL> grant connect, resource to myschema;

Grant succeeded.

SQL> conn myschema/myschema
Connected.
SQL> create table product(id number, name varchar2(20));

Table created.

SQL> insert into product values(1, 'sample_product_name');

1 row created.

SQL> commit;

Commit complete.

SQL> create directory dp_dir as '/home/oracle/Desktop';

Directory created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@nepal Desktop]$ clear

[oracle@nepal Desktop]$ expdp system/oracle schemas=myschema directory=dp_dir dumpfile=expdp_myschema.dmp logfile=expdp_myschema.log

Export: Release 11.2.0.4.0 - Production on Sat Jul 30 07:02:07 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/password schemas=myschema directory=dp_dir dumpfile=expdp_myschema.dmp logfile=expdp_myschema.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "MYSCHEMA"."PRODUCT"                        5.429 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/Desktop/expdp_myschema.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Jul 30 07:04:51 2016 elapsed 0 00:02:21

[oracle@nepal Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 30 07:10:06 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> drop user myschema cascade;

User dropped.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

   [oracle@nepal Desktop]$ impdp system/oracle schemas=myschema directory=dp_dir dumpfile=expdp_myschema.dmp

Import: Release 11.2.0.4.0 - Production on Sat Jul 30 07:11:44 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/password schemas=myschema directory=dp_dir dumpfile=expdp_myschema.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MYSCHEMA"."PRODUCT"                        5.429 KB       1 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Sat Jul 30 07:12:14 2016 elapsed 0 00:00:19

[oracle@nepal Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 30 07:13:08 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> conn myschema/myschema
Connected.

SQL> select table_name from tabs;

TABLE_NAME
------------------------------
PRODUCT

SQL> select * from product;

    ID NAME
---------- --------------------
     1 sample_product_name

SQL>


How the single SQL statement is isolated?

For SQL Server:

  But what's about single statements?

In SQL Server, there is 4 transaction isolation levels (in pessimistic locking model). Default transaction isolation level is Read Committed, and locks are placed on a statement level. If you have a transaction that has 2 statements inside it that retrieve same data, and in the middle of that transaction (right after 1st statement but before 2nd statement started) some other transaction modifies the data that is retrieved by first transaction, then first transaction will return different data in 1st and 2nd statements.

That is known as non-repeatable read problem. This can be solved by increasing transaction isolation level to REPEATABLE READ where locking happens on transaction level (not on a statement level)

UPDATE table SET value = value + 1 WHERE id = 1;

  Can a parallel transaction change the value ?

No it can't. This is a single statement, and during it Exclusive lock is placed on rows that are being modified.
No other transactions or statements can modify these rows until our update statement is complete

SELECT * FROM table t1 JOIN table t2 USING (id);

  is it possible at some isolation level that t1.* and t2.* columns would differ due to parallel update?

Yes, in READ UNCOMMITED is possible (or when with (NOLOCK) hint is specified), since Shared lock is not applied. In other isolation levels - no, because Shared lock is being placed on rows from t1 and t2, and during it  rows can't be modified (Exclusive lock can't be acquired by other transactions)

SELECT * FROM table1 WHERE id IN (SELECT t_id FROM table2)

  Is it possible that some records from table1 are removed after sub-select has been executed?

In READ UNCOMMITTED is possible. In higher isolation levels - not possible, because during execution of this statement, Shared lock is placed on all selected rows of table1 and table2, and no other transaction is able to modify the rows of table1 and table2 (put Exclusive lock) until our select statement completes and Shared lock released from table1 and table2

Here you can read more information about SQL Server's isolation levels:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15


The usual solution is to authenticate the user within the web app, then issue a SET ROLE or SET SESSION AUTHORIZATION to "become" the user on a JDBC session that's already authenticated with the database using a fixed username.

In both cases the DISCARD ALL command that should be run by any connection pool when returning connections to the pool will automatically reset the authorization. Make sure your connection pool runs DISCARD ALL on returned connections.

These are a lot like the first of the three "proxy authentication" cases given in the linked Oracle doc.

PostgreSQL doesn't support the other two forms, where the client auth is passed through to the DB. It'd be relatively simple to support but nobody's wanted it enough to implement it or fund the work required for someone else to implement it.

SET ROLE

To use SET ROLE you must connect to the DB as a user that has membership of all the user roles, but the user can be otherwise unprivileged. The DB user should have the NOINHERIT option set with ALTER ROLE or at CREATE ROLE time. So you typically create webapp users as db roles that the webapp user is a member of, e.g.

CREATE ROLE newusername
  NOLOGIN
  ROLE mywebappuser;

(The ROLE mywebapp is equivant to running a subsequent GRANT newusername TO mywebappuser;).

SET SESSION AUTHORIZATION

To use SET SESSION AUTHORIZATION you must connect as a superuser, which is generally undesirable as it gives total control to the webapp user.

Unlike SET ROLE there's no requirement for group memberships.


What happens when the lock limit is reached?

max_locks_per_transaction does not apply to rows, but to objects.
The fact that rows do not count is made explicit in the documentation:

From https://www.postgresql.org/docs/current/static/runtime-config-locks.html

  This parameter controls the average number of object locks allocated
  for each transaction; individual transactions can lock more objects as
  long as the locks of all transactions fit in the lock table. This is
  not the number of rows that can be locked; that value is unlimited.

The information necessary to row locking is written in the row itself.


Rate Per Second = RPS
Suggestions to consider for your my.ini [mysqld] section
max_connections=750  # from 1500 to conserve RAM and have 50% more capacity than your expected 500 users
thread_cache_size=100  # from 9 to minimize threads_created overhead
innodb_io_capacity=1900  # from 200 to enable more of your SSD IOPS capacity
read_rnd_buffer_size=64K  # from 1M to reduce handler_read_rnd_next RPS of 39,200

You will find these changes will significantly reduce footprint and CPU busy.
For additional suggestions, view profile, Network profile for contact info and free downloadable Utility Scripts to improve performance.


How to troubleshoot "BCP copy in failed"

Here is how I recommend doing it, which is tailored for character data:

generate format file
edit format file to match the input data structure
upload the data file using the format file generated at step 2

corresponding commands are then as follows:

run bcp [<my_db_name>].<my_schema_name>.<my_table_name> format nul -c -f
<my_format_file_name>.fmt -t\<my_delimiter_char> -S <my_db_server>
-U <my_db_user>
use any text editor if edition is needed
run bcp [<my_db_name>].<my_schema_name>.<my_table_name> in <my_data_file> -f <my_format_file_name>.fmt -t\<my_delimiter_char>  -b <my_batch_size> -m <my_max_errors> -e <my_error_file>  -S <my_db_server> -U <my_db_user>

Then, you can inspect <my_error_file> to see how it went. Execution without any error should produce empty <my_error_file>.


Sure it is saying to use SQLCMD.EXE connecting to the server instance VMLIVE-DATA\SQLSERVER, to run the script that will restore the GIS_ENG from a backup of the DB_ENG database, replacing the existing GIS_ENG if there is one.

The bulk of the text is the script -Q"RESTORE  ...  STATS=10".

The SQLCMD and RESTORE DATABASE parameters are define in the SQL Server documentation.

SQLCMD.EXE - http://technet.microsoft.com/en-us/library/ms162773.aspx

RESTORE DATABASE - http://technet.microsoft.com/en-us/library/ms186858.aspx

By default SQLCMD uses Trusted Connections (describe in the SQLCMD link) so it requires this to be run by a login with rights to restore a database.


How to permanently enable query cache in MySQL?

You can set this parameter in /etc/my.cnf on Red Hat or /etc/mysql/my.cnf on Debian (other systems have the my.cnf file in various locations):

# vi /etc/my.cnf

Append config directives as follows:

query_cache_size = 268435456
query_cache_type = 1
query_cache_limit = 1048576

Change the values as appropriate to your system!


Is this an example of an Enum (Enumerated Type)

Yes, it looks like finance.fund_part_status is an enum type.

Assuming you're using the psql command-line interpreter, try this command:

\dT+ finance.fund_part_status;


Postgres multicolumn vs combined single column indexes

If the P = '...' AND Q = '...' is highly selective, I would just put one index on that pair of columns and leave it at that.  If it is only moderately selective and if I needed individual indexes on A, B, and C anyway for other queries, then I'd probably tack P and Q onto the end of each of those indexes, unless the P and Q columns are wide and so would bloat the size of the indexes.

I wouldn't use 5 separate single column indexes for this, unless I needed each one for some other purpose anyway.  They would have to be combined with a BitmapAnd, which is much less efficient than using a multi-common index with equality conditions.


Best way to create a materialized view in MySQL

Materialized Views Do Not Exist in MySQL.
Flexviews has been recommended in the DBA StackExchange before

Bill Karwin's post : Does MySQL have a version of Change Data Capture?
Redguy's post : Need some support on MySQL Query

Since you have it already, some due diligence and elbow grease on your part may be necessary to get going on using it (if you haven't already done so)
ALTERNATIVE #1
You might look into setting up Summary Tables. You are going to need to index your base tables in order to support aggregation before building Summary Tables.
ALTERNATIVE #2
Here is an old post from http://www.materialized.info/ which has a more down-to-earth strategy that looks intriguing. It involves using triggers. Looks like it could be fun ...
ALTERNATIVE #3
If your base tables are not that big and you can live with querying entire tables once a week, try the User Comments section of the MySQL Documentation on CREATE VIEW for ideas. Search for word materialized on that page.


Use non-printable characters in SQL Server BCP xml format file as TERMINATOR

It seems, from the documentation about BCP - Specify Field and Row Terminators (SQL Server), that non-printable characters are not supported:

  Characters Supported As Terminators

  The bcp command, BULK INSERT statement, and the OPENROWSET bulk rowset
  provider support a variety of characters as field or row terminators
  and always look for the first instance of each terminator. The
  following table lists the supported characters for terminators.

As an option, you could use POWERSHELL to read in the source file and search\replace the 0x1C characters with some other character like a tilda (~) and output to a different file.  Then use that character as your terminator.

(Get-Content c:\test\test.txt) | ForEach-Object {$_ -replace [char]0x1C,'~'} | Set-Content c:\test\testout.txt


Time Difference Between actual total time and execution time in EXPLAIN ANALYZE

The source of EXPLAIN has:

    /* Create textual dump of plan tree */
    ExplainPrintPlan(es, queryDesc);

    if (es->summary && planduration)
    {
            double          plantime = INSTR_TIME_GET_DOUBLE(*planduration);

            ExplainPropertyFloat("Planning Time", "ms", 1000.0 * plantime, 3, es);
    }

    ...

    /*
     * Close down the query and free resources.  Include time for this in the
     * total execution time (although it should be pretty minimal).
     */
    INSTR_TIME_SET_CURRENT(starttime);

    ExecutorEnd(queryDesc);

    FreeQueryDesc(queryDesc);

    PopActiveSnapshot();

    /* We need a CCI just in case query expanded to multiple plans */
    if (es->analyze)
            CommandCounterIncrement();

    totaltime += elapsed_time(&starttime);

    /*
     * We only report execution time if we actually ran the query (that is,
     * the user specified ANALYZE), and if summary reporting is enabled (the
     * user can set SUMMARY OFF to not have the timing information included in
     * the output).  By default, ANALYZE sets SUMMARY to true.
     */
    if (es->summary && es->analyze)
            ExplainPropertyFloat("Execution Time", "ms", 1000.0 * totaltime, 3,
                                                     es);

Perhaps freeing of the query resources (tearing down the executor infrastructure, freeing the snapshot) takes an inordinate time. Hard to say.

Is that a one-time occurrence or do you see that result consistently?

If the latter, is there anything unusual about this query?


Bulk insert top 1000 rows from a big flat file

bcp Import

You can import data from your huge data file using Microsoft SQL Server's bcp utility from the command prompt.

bcp Description

  The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns

Example Syntax

C:\> bcp YOUR_TABLE -d YOUR_DB -i INPUT_FILE -S YOUR_SERVER -F 1 -L 1000 -U YOUR_LOGIN -P YOUR_PASSWORD -f FORMAT_FILE

This translate to:

bcp
bulk copy program
YOUR_TABLE
The table you will be importing your data to
-d YOUR_DB
The name of the database you are connecting to
-i INPUT_FILE
The full pathname and filename to your huge file (e.g. C:\temp\bigfile.txt)
-S YOUR_SERVER
The name of the server you are connecting to
-F number
The first line to import
-L number
The last line to import
-U YOUR_LOGIN
The SQL Server Login (alternatively you can use -T for a trusted connection)
-P YOUR_PASSWORD
The password for the SQL Server Login
-f FORMAT_FILE
Depending on your table definition you will probably require a format file which defines the structure of the table you are importing data to and the delimiters in the INPUT_FILE. Further information for the format file can be found here and here.

Your command should then look like this:

bcp MEGA_TABLE -d Test_DB -i C:\temp\huge_data.txt -S localhost -F 1 -L 1000 -U sa -P secretnottelling -f c:\temp\huge_data.fmt

bcp format file

Depending on the complexity of your table and input file, you might have to create a separate bcp format file to accommodate missing columns in either the input file, target table or both. This can be achieved by exporting your table without a format file:

bcp YOUR_TABLE out c:\temp\sample_data.txt -S localhost -T -d YOUR_TABLE

bcp will ask you some questions about your fields and terminators for the format file and then will ask you to provide a name:

Enter the file storage type of field ID [int-null]:
Enter prefix-length of field ID [1]:
Enter field terminator [none]:

[multiple iterations]

Enter the file storage type of field Fnct_ID [int-null]:
Enter prefix-length of field Fnct_ID [1]:
Enter field terminator [none]: ;

Do you want to save this format information in a file? [Y/n] y
Host filename [bcp.fmt]: C:\temp\huge_data.fmt

Starting copy...

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1

You now have a format file for your target table which can be used in the bcp import.

The format file will look a bit like this one:

12.0
8
1       SQLINT              1       4       ";"    1     ID                   ""
2       SQLNCHAR            2       200     ";"    2     Question             Latin1_General_CS_AS
3       SQLNCHAR            2       200     ";"    3     Link                 Latin1_General_CS_AS
4       SQLNCHAR            2       100     ";"    4     Tbl                  Latin1_General_CS_AS
5       SQLNCHAR            2       2000    ";"    5     Info                 Latin1_General_CS_AS
6       SQLINT              1       4       ";"    6     Tbl_ID               ""
7       SQLINT              1       4       ";"    7     Src_ID               ""
8       SQLINT              1       4       ";"    8     Fnct_ID              ""

This is just a basic primer on how to use the bcp utility to import data from a file.

Answer

Create a format file (*.fmt) for your target table usign bcp
Import the data using the -F and -L parameters
Check the import

References

bcp Utility (Microsoft Technet)
Create a Format File (SQL Server) (Microsoft Technet)
Use a Format File to Map Table Columns to Data-File Fields (SQL Server) (Microsoft Technet)

  In absence of a SQL Server version in your question I referenced the SQL Server 2012 documentation.


Getting ApplicationName (sqlserver.client_app_name) to show up in an xevent

I have seen this issue before, what seems to happen if you have this type of sequence:

create session
start session
stop session
add columnar data (action or field)
start session

The GUI doesn't show the added columnar data. Your options here would be to directly query the target or to clear/rename the old session definition's XEL files.


How to make MySQL log to 2 separate remote servers?

The .mysql_history file is only for commands in the mysql client, and only for the current operating system user. I don't know of any way to log this anywhere but in .mysql_history.

The mysqld.log can be written to the local syslog daemon, so it ends up in /var/log/messages (or wherever you send syslog). You enable this with the following entries in /etc/my.cnf:

[mysqld_safe]
syslog
syslog-tag = mysql

The syslog-tag is optional, but helpful so your mysql log entries are easily separated from other syslog entries. I like to name the tag something specific to the server, if I'm going to merge mysql logs from multiple instances into one remote syslog.

Then you can use rsyslog to send the syslog content (or only the subset of entries with the given tag) to another server. See http://www.rsyslog.com/storing-and-forwarding-remote-messages/

This question might also be helpful: send logs to multiple rsyslog servers


In this instance, if you wanted to use XE you can grab it through "sqlserver.error_reported". The error number for a subquery returning more than one value is 512 - so we can filter on ONLY error numbers of 512.

XE Session Code:

CREATE EVENT SESSION [Error512Tracking] ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_hostname,sqlserver.database_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([error_number]=(512)))
ADD TARGET package0.event_file(SET filename=N'C:\ExtendedEvents\Error512Tracking.xel',metadatafile=N'C:\ExtendedEvents\Error512Tracking.xem')


Using T-SQL, is it possible to a split a result set or table and then insert into two different temp tables?

The feature that you're describing is sometimes called multi-table insert. Oracle is one RDBMS that implements it. It is not implemented in SQL Server.

If you need to split your data doing just a single pass of the data you have a few workarounds but all of them add complexity and have other negative side effects. You could define a partitioned heap and insert into that heap. The partitioning column would be a 0/1 computed value that defines the target table. You can partition switch out of the heap into two separate tables essentially for free. Overall this requires a sort of the data before insert.

You could define a partitioned view, insert your data into the view, and drop the view after. The requirements of a partitioned view don't seem to fit very well with your scenario. Among other things, you'd need primary keys on both target tables that contain the computed column which determines the target table. The query plans for inserting data can look pretty ugly (although every query plan looks astoundingly beautiful in SSMS 17.4):

A third option is to define an INSTEAD OF INSERT trigger on a target table. The trigger code can send rows to the right table. As you might imagine this has some overhead.

If the requirement is to simply insert data in a single transaction you can do that with an explicit transaction and two INSERT queries. I would just negate the WHERE clause instead of joining back to the same table. Just make sure that you handle NULLs correctly. Example code:

BEGIN TRANSACTION;

INSERT INTO #tmp_Declined
select *
from Payments
where NOT (WasOrginallyDeclined = 1 and MostRecentPaymentAccepted = 1);

INSERT INTO #tmp_not_Declined
select *
from Payments
where WasOrginallyDeclined = 1 and MostRecentPaymentAccepted = 1;

COMMIT TRANSACTION;


Should I migrate data using detach/copy/attach or through backup-restore-replay?

Personally, I would avoid the detach/attach mechanisms. Especially in SQL Server 2000, I just don't trust that you will always bring the server back up and be able to attach those files. I've heard plenty of stories where this didn't happen cleanly - just because you have a Plan B doesn't automatically make Plan A sensible.

With backup / restore, you don't risk having to go to Plan B. If the backup fails, your database is still up. If restore fails, your old database is still up. In both cases you can restore the operation of the original database and revisit the plan later. In addition to the extra security here over stopping SQL Server and/or detaching, this also means you can test the hoo-has out of the backup/restore methodology (assuming you currently have the space to perform the backups and another instance to test the restore). You can't really test the detach approach without detaching the databases or stopping SQL Server, and that's tough to do outside of a proper maintenance window. And finally, with the other approaches you can't even start copying the files until you've detached or brought SQL Server down. With backup/restore you can have the .bak file waiting on the new storage long before you take the last log backup and start your maintenance window.

One other benefit over the pull-the-drive-out-from-under-SQL-Server method: with backup/restore you can move various files to different drive letters than they were before. For example when we migrated to a new SAN, we were able to have more volumes, so we could move tempdb to T:\ (which didn't exist before), some of the data and log files to new drive letters, etc. to better utilize all the new I/O capacity we had. If you simply shut down SQL Server and then swap out the disks, you need to have the same drive letters and the same number of volumes.


SET or VARCHAR for list of tags in a MySQL photos table?

Neither

Add another table for tags with one row per tag and photo

either just (photo_id, tag) - tag is varchar (or maybe enum, but then it is harder to add more tags), so if two photos have the same tag, there is some redundancy
or two tables - (tag_id, tag) and (photo_id, tag_id) where each tag is unique, so no duplication of texts, and the second table just links tags to photos, thats more effective but a bit more complex

Do not store multiple values in one column if you want to work with them (search by them etc.) - a database is not just a CSV storage and this is what rows and joins are for. It's called Normalization - learn about it and it will save you from many headaches.


SQl Server 2008 R2 - sa can login but not access database

Fixed this - VSS Writer had fallen over for some reason, and the upshot of that was that a load of stuff (BACKUP amongst them) could not complete (BlkBy column in sp_who2 showed this up). A reboot of the server fixed this issue (services.msc wouldn't let me kill VSS Writer, and neither would the KILL command)...only thing left to do is find out WHY it happened.

And sort myself a disaster recovery plan ;)


A cleaner way to do this would be to get the table object from the database object directly, and then drop it if it returns non-null. This will ONLY run if the table exists.

#drop the Table
$tb = $db.Tables['listeningport']
IF ($tb)
    {$tb.Drop()}

You can use $tb.Tables['tablename', 'schemaname'] to use a non-dbo schema.

When I deal with things like this in SMO my preference is ALWAYS to get the child objects from the parent object directly. It's cleaner, it's more obvious to the next person reading the code what you're doing (you're getting the table directly from that table collection in the db object), and it's very easy to check for existence in a single step.

For your specific issue, it's possible that the table is not created with that name since you're looking at tempdb. Are you sure it's there with that specific name?


Oracle RMAN Back-up files in red hat can be read in windows?

RMAN can be used for cross-platform tasks but you cannot restore your classic full backup from Linux to Windows


Thanks for the clear specifications of the requirements, and the sample input and output.

"Each person must have at least 1 item." -- probably no extra code.

"Each person can have 0 or 1 active items only." -- GROUP BY person_id, then do the test in HAVING SUM(is_active = 'YES') <= 1.

"Get 1 record only for each person" -- The GROUP BY does that.

"Get the row with is_active = "YES" if person has an active item. If person doesn't have any active item, get 1 record (any record) of that person." -- This gets tricky.  If there were only one person, something like this should work: ORDER BY is_active = 'YES' DESC  LIMIT 1.  But that does not work with grouping.  More in a minute.

"Order result by person id DESC" -- This happens at the end.

Now it gets tricky because you need a "groupwise max".  Let's try to get the item.id values rather than just the one id desired.  Then work on getting the details for the 'first' id.  And let's wait until the end to get the person.name; we can easily ignore it until then.

SELECT  id,
        GROUP_CONCAT(id ORDER BY is_active = 'YES' DESC) AS ids
    FROM  items
    GROUP BY  id
    HAVING  SUM(is_active = 'YES') <= 1
    ORDER BY id DESC

See if that gives you the correct set of rows (1 per person, ordered correctly) and the first id of ids is the desired item.  Assuming all that works, let's finish by getting the other desired columns.  (I will eliminate the redundant column in your sample output, and toss the ids.)

SELECT  p.id,
        p.name,
        IF(i.is_active = 'YES', i.name, '') AS item_name
    FROM (
            the-above-select
         ) AS x
    JOIN items AS i  ON i.id = SUBSTRING_INDEX(x.ids, ',', 1)
    JOIN persons AS p ON p.id = x.id
    ORDER BY p.id DESC

(There is, of course, a chance that I goofed.  But that could come close.)


How to automatically grant permissions to application created databases in SQL Server 2008 R2?

You can do this by granting the permission in the Model database.  This database is used to create any new ones.

USE model
GO

CREATE USER UserName FROM LOGIN LoginName
GO

EXEC sp_addrolemember 'db_datareader','UserName'
GO

That way, as the application creates new databases (which are copied from Model), they will already have the User and the role membership.

However be warned.  This will be the case for ANY new database created with the CREATE DATABASE command on this instance.  So if you create a database for a different application it will already have this user and the user will have db_datareader. Databases created with ATTACH DATABASE or RESTORE DATABASE will not use the model database, and will not copy the security from the Model database.


Synchronise data from one table to another & vice-versa using triggers

I concur with @NathanJolly, this is a potential data nightmare and seems ill-advised...  but there is a way to implement this that is, at least conceptually, fairly straightforward.
You need not worry about the possibility of recursion in triggers, because this won't happen... MySQL doesn't support it.
I built this fiddle that creates tables t1 and t2, each of which has an AFTER INSERT trigger that tries to insert the record into the other table... then tries to do an insert.  Theoretically, there's a potential for recursion, but MySQL prevents this:
ERROR 1442 (HY000): Can't update table 't1' in stored function/trigger because it is already
                    used by statement which invoked this stored function/trigger.

So it won't run in a loop, but it still throws an exception.  Technically, it's not trigger recursion causing the exception, because we haven't actually gotten that far... it's the fact that the trigger in t2 tries to modify table t1, which was involved in the currently-executing statement.
Then again, technically, it's not just the recursion of the triggers we need to avoid -- we have to stop one step before that -- if the client modifies t1, the t1 trigger modifies t2, then t2's trigger needs to not even try to do anything to t1 (including, but not limited to, firing the trigger again on t1).
Implementing this is actually fairly straightforward... in each trigger, we toggle the value of a session to tell the other trigger not to run.
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW BEGIN
  IF @__disable_trigger_t1t2 = 1 THEN
    SET @__disable_trigger_t1t2 = NULL;
  ELSE
    SET @__disable_trigger_t1t2 = 1;
    -- trigger logic goes in here
    INSERT INTO t2 (id,things) VALUES (NEW.id,NEW.stuff);
  END IF;
END //

CREATE TRIGGER t2_ai AFTER INSERT ON t2 FOR EACH ROW BEGIN
  IF @__disable_trigger_t1t2 = 1 THEN
    SET @__disable_trigger_t1t2 = NULL;
  ELSE
    SET @__disable_trigger_t1t2 = 1;
    -- trigger logic goes in here
    INSERT INTO t1 (id,stuff) VALUES (NEW.id,NEW.things);
  END IF;
END //

A test case for this logic working can be seen in this fiddle.
Essentially, when a trigger fires, it checks the value of the @__disable_trigger_t1t2 variable (this variable name is, of course, arbitrary).  If '1', it sets it back to NULL (the default for session variables) and does no further processing, because the '1' means this row is being manipulated by the trigger on the other table.  If already NULL, then it sets the value to 1 and executes the trigger logic -- which will update the other table, whose trigger will see the '1', will not execute its own logic, and will reset the variable back to NULL, so that the next execution of either trigger -- whether for the next row in the same statement, or for a subsequent statement -- will see the NULL and execute normally.
In MySQL, triggers are only supported FOR EACH ROW, meaning that we can define a MySQL trigger as "a stored program that is executed before or after each row is inserted, updated, or deleted"... which means that no matter how many rows your query affects, the trigger will run once for each row.  Actions against other tables will cause the triggers on those tables to fire, and the execution of the trigger on t1 for the first row will not complete until all of the triggers it caused to fire have also completed... and this is why we can use this variable like this -- only one row is actually being handled at a time, so we set and clear this variable for each row, always leaving it NULL after each statement terminates.
A significant caveat is that this approach fails if you need to trigger on cascading deletes or updates related to foreign keys, because:

"Currently, cascaded foreign key actions do not activate triggers."
-- http://dev.mysql.com/doc/refman/5.6/en/create-trigger.html

So, while I have misgivings about the long-term viability of maintaining database synchronization with this approach because of the number of things that could go wrong with two applications manipulating each other's data or logical errors in the trigger definitions... it seems technically possible.
You also mentioned:

views cannot have triggers in MySQL

No, but updating an updatable view will fire the triggers on the underlying base table as if the table had been directly manipulated.


First to answer your question, yes that looks like it will work.  However, this approach opens up permissions a bit more than you probably want.  While it's accurate that ALTER permissions are required to Truncate a table, ALTER permissions also grant the following:

  When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope.

If you only require this process to truncate a table, I suggest you create a Stored Procedure that utilizes elevated rights via Impersonation to perform the truncate so you keep your SSIS account at a minimal level of security.

First, you need to create a stored procedure that provides your Truncate Table functionality, as follows:

-- Run this procedure as a user with appropriate Control privileges in the database, and adjust to your liking
CREATE PROCEDURE [dbo].[TRUNCATE_TABLE]
(
    @TableName NVARCHAR(128)
) WITH EXECUTE AS SELF
AS
BEGIN
    -- Only Allow Truncates to dbo schema and user's owned Schema
    IF EXISTS (SELECT * FROM sys.tables t INNER JOIN sys.schemas s
                    ON t.schema_id = s.schema_id
                   WHERE (s.name + N'.' + t.name = @TableName
                        OR s.name + N'.' + t.name = (SELECT TOP 1 default_schema_name FROM sys.database_principals
                                                     WHERE type = 'S' and name = USER_NAME(ISNULL(DATABASE_PRINCIPAL_ID(ORIGINAL_LOGIN()), DATABASE_PRINCIPAL_ID()))) + N'.' + @TableName)
                        AND s.name IN (N'dbo', (SELECT TOP 1 default_schema_name FROM sys.database_principals
                                                     WHERE type = 'S' and name = USER_NAME(ISNULL(DATABASE_PRINCIPAL_ID(ORIGINAL_LOGIN()), DATABASE_PRINCIPAL_ID())))))
    BEGIN
        DECLARE @ReturnCode INT
        DECLARE @DynamicSQL NVARCHAR(272)

        SELECT @TableName = CASE WHEN @TableName LIKE N'%.%' THEN @TableName
                                 ELSE QUOTENAME((SELECT TOP 1 default_schema_name FROM sys.database_principals
                                                 WHERE type = 'S' and name = USER_NAME(ISNULL(DATABASE_PRINCIPAL_ID(ORIGINAL_LOGIN()), DATABASE_PRINCIPAL_ID())))) + N'.' + @TableName
                            END

        SET @DynamicSQL = N'TRUNCATE TABLE ' + @TableName

        -- Initialize @ReturnCode variable
        SET @ReturnCode = -1

        BEGIN TRY
            -- Attempt Truncate Operation
            EXEC @ReturnCode = sp_executeSQL @DynamicSQL
        END TRY
        BEGIN CATCH
            -- If an Error is encountered, trap it
            DECLARE @ErrorMessage NVARCHAR(2048), @ErrorNumber INT, @ErrorSeverity INT,
                @ErrorState INT, @ErrorLine INT, @ErrorProcedure NVARCHAR(126);

            SELECT @ErrorNumber = ERROR_NUMBER(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE(),
                   @ErrorLine = ERROR_LINE(),
                   @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '');

            SELECT @ErrorMessage = N'Error %d, Level %d, State %d, PROCEDURE %s, Line %d, ' +
                N'Message: ' + ERROR_MESSAGE();

            -- Raise an error: msg_str parameter of RAISERROR will contain
            -- the original error information.
            RAISERROR(@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity,
                      @ErrorState, @ErrorProcedure, @ErrorLine)

            RETURN @ReturnCode
        END CATCH
    END

    ELSE
    BEGIN
        -- If user is trying to Truncate a table they are not allowed to, throw and error.
        RAISERROR(N'Cannot find the object because it does not exist or you do not have permissions.'
                ,16, 1, 1088, 16, 7, N'[dbo].[TRUNCATE_TABLE]', 1)
        RETURN -1
    END
END

Now create a database role against which execution of this stored procedure is provided.

-- Create a DB Role
CREATE ROLE TruncatePrivs
GRANT EXECUTE ON TRUNCATE_TABLE TO TruncatePrivs
GO

-- Add Users to the Role
EXEC sp_addrolemember 'TruncatePrivs', 'INT\svc-w-corerefdata-de'
GO

The SSIS user can now truncate whatever table you wish to allow simply by calling the stored procedure from within a Execute SQL Task step within the package, as follows:

EXEC dbo.TRUNCATE_TABLE @Tablename = N'TableName'


How to collapse contiguous single day start/end date ranges in MySQL

After a little more research, I figured out the solution to this "gaps and islands" problem:

SELECT t.tag, begin AS start_date, MAX(end) AS end_date
FROM (
    SELECT
    a.*
    CASE WHEN (DATEDIFF(start_date, @prev_date) = 1 AND DATEDIFF(end_date, start_date) = 1)
        THEN @begin := @begin
        ELSE @begin := start_date
    END AS begin,
    end_date AS end,
    @prev_date := start_date
    FROM (SELECT @prev_date := NULL, @begin := NULL) AS vars
    JOIN my_table a
    ORDER BY start_date
) AS t
GROUP BY begin;


Required values for the performance counters

After searching for "Accidental DBA" as suggested by LowlyDBA you will see that a lot of us got the job because we happened to stand next to the database server as it started going up in flames.

So I want to congratulate you on the promotion and suggest starting here: Accidental DBA Series - SQLskills.com

Regarding your actual question. There is no optimal value for most of the performance counters (besides maybe stuff like 100% CPU all the time might be not good). You need to find out what is normal for YOUR server. This baseline will help you to know where to look when it is not performing as expected (see the "Baseline" section in the "Accidental DBA Series").

That being said I would suggest stepping away from perfmon for a moment and have a look at your servers wait stats first. These stats will tell you if and where it hurts. If your server is not waiting on disk resources it does not matter if some disk specific perfmon counters are "too high". Wait stats analysis is explained in the "Wait Statistics Analysis" section of the sqlskills article.

Again, a baseline will help a lot. Have a look at Brent's solution for monitoring wait stats. It is lightweight, extremely helpful and free.


Setting default value equal to parent's value

"Redundant" information is a no-no in 'proper' schema design.  The "right" way to solve your problem is to leave NULL in the child and reach for the parent's color in the SELECTs.  (See the COALESCE function and LEFT JOIN to avoid stumbling in the root category.)


Could I set my InnoDB MySQL Server session state for SQL_SAFE_UPDATES and INNODB_FLUSH_LOG_AT_TRX_COMMIT?

What version are you running?  5.6 doc gives the details:

innodb_flush_log_at_trx_commit can be set (1) on the commandline, (2) in my.cnf, or (3) via SET GLOBAL.

sql_safe_updates can only be set via SET SESSION or SET GLOBAL.

Note that SET GLOBAL does not impact anyone currently connected (including yourself).  It will effect anyone connecting later, but before the next shutdown of mysqld.

my.cnf affects those who connect after the next restart.

Your question asks how to set them, but fails to say what the ultimate goal is, so I cannot recommend a particular approach.  (Do you care about Workbench?  Your C# connection?  Others currently connected?  Future connections?  What?)

I do not know whether Workbench re-connects between actions.  It probably does.  This implies that SET SESSION may not have any impact.

Update (based on update to Question)

SET GLOBAL INNODB_FLUSH_LOG_AT_TRX_COMMIT = 2;
Note: that SET will affect other users.
Now disconnect and reconnect.
SET SQL_SAFE_UPDATES = 0;
and do the rest of your task.

But...  Neither of those settings impact a mere CREATE TABLE.  I assume you will proceed to do INSERTs and UPDATEs?

Side notes:  These two indexes are redundant and should be removed:

UNIQUE KEY `ASID` (`ASID`),
KEY `IX_ActionSequence_EAID` (`EAID`),

A PRIMARY KEY is a UNIQUE KEY is a KEY.

Why have both ASID and EAID?  Both are UNIQUE; both are VARCHAR(64); either could be the PRIMARY KEY.


Postgres Index scan forward vs backward = speed difference of 357X slower

Since I like replacing aggregate functions by old-fashioned self-joins and NOT EXISTS clauses, here is my attempt:

SET search_path='tmp';

DROP TABLE tmp.changes CASCADE;
CREATE TABLE tmp.changes
        ( id integer NOT NULL PRIMARY KEY
        , fullname varchar
        , issuer varchar
        , rsymbol varchar
        , industry varchar
        , activity INTEGER NOT NULL
        , shareschange FLOAT
        , sharespchange FLOAT
        , mfiled FLOAT
        );

        -- lacking information from the OP
        -- I can only presume a flat distribution.
INSERT INTO tmp.changes(id, activity, shareschange,sharespchange,mfiled )
SELECT nm.*
        , (random() *20)::integer -- mfiled
        , random() *10000
        , random() *100
        , random() *100000
FROM generate_series(1,1000000) nm
        ;

ALTER TABLE tmp.changes
        ALTER shareschange
        SET STATISTICS 1000
        ;
ALTER TABLE tmp.changes
        ALTER mfiled
        SET STATISTICS 1000
        ;

VACUUM ANALYZE tmp.changes
        ;

CREATE INDEX changes_mfiled_shareschange
    ON tmp.changes(mfiled,shareschange)
        ;

EXPLAIN ANALYZE
SELECT initcap(ch.fullname) AS some_name1
     , initcap(ch.issuer) AS some_name2
     , upper(ch.rsymbol) AS some_name3
     , initcap(ch.industry) AS some_name4
     , ch.activity
     , to_char(ch.shareschange,'FM9,999,999,999,999,999') AS some_name5
     , ch.sharespchange || '%' AS some_name6
FROM   changes ch
WHERE  ch.activity IN (4,5)
        -- NOTE: the subquery is *not* correlated.
        -- [I had expected a subselect of nx.activity IN (4,5)
        -- like in the main query. ]
AND    NOT EXISTS (SELECT * FROM changes nx
        WHERE nx.mfiled > ch.mfiled
        )
ORDER  BY ch.shareschange ASC
LIMIT  15
        ;


sql server replication - how to get combination of values from a binary(8) field?

Use the built-in bitwise operators to examine the column.  For example

select
    schema_option & 0x01 AS  GeneratesObjectCreation,
    schema_option & 0x02 AS  GenerateCustomSP,
... etc.


Execute Remote Powershell script from SQL Agent

JNK was correct in the comment above.  If I ran it in the command shell, there was a prompt saying

  Security Warning Run only scripts that you trust

In order to avoid this message, I added -ExecutionPolicy Bypass to the command

Example:
powershell.exe -ExecutionPolicy Bypass -file \Server02\test2\Test.ps1

The job is now able to complete.


About real time statistics oracle 19c only for particular table

First up, you should only change underscore parameters when instructed to do so by support. But more importantly for these parameters, Real-Time Statistics is only available on specific database offerings. See the license guide for details.

Changing these parameters is likely to be a violation of your license agreement!

  LOAD TABLE CONVENTIONAL and OPTIMIZER STATISTICS GATHERING are the new
  parameters that have been added to the explain plan for this new
  feature.

These operations are not specific to Real-Time Statistics. OPTIMIZER STATISTICS GATHERING is for any operation that captures stats during execution. create table as select is an example that has done this for a while now.

LOAD TABLE CONVENTIONAL means the database does a conventional (not direct-path) insert.

You can disable Real-Time Statistics for:

A specific statement with the no_gather_optimizer_statistics hint
A table by locking stats for that table (though this will stop the background job gathering stats too!)

For example, only the first of these three inserts has the OPTIMIZER STATISTICS GATHERING operation:

set long 10000
set serveroutput off
cl scr
create table t as
  select 1 c1 from dual
  connect by level <= 100;

insert /*+ monitor */into t
with rws as (
  select level x from dual
  connect by level <= 10
)
  select * from rws;

select dbms_sqltune.report_sql_monitor (report_level=> 'basic +plan')
from   dual;

SQL Plan Monitoring Details (Plan Hash Value=2403765415)
======================================================================================================================================================
| Id |             Operation             | Name |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity | Activity Detail |
|    |                                   |      | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |   (# samples)   |
======================================================================================================================================================
|  0 | INSERT STATEMENT                  |      |         |      |         1 |     +0 |     1 |        0 |      |       |          |                 |
|  1 |   LOAD TABLE CONVENTIONAL         | T    |         |      |         1 |     +0 |     1 |        0 |    4 | 32768 |          |                 |
|  2 |    OPTIMIZER STATISTICS GATHERING |      |       1 |    2 |         1 |     +0 |     1 |       10 |      |       |          |                 |
|  3 |     VIEW                          |      |       1 |    2 |         1 |     +0 |     1 |       10 |      |       |          |                 |
|  4 |      CONNECT BY WITHOUT FILTERING |      |         |      |         1 |     +0 |     1 |       10 |      |       |          |                 |
|  5 |       FAST DUAL                   |      |       1 |    2 |         1 |     +0 |     1 |        1 |      |       |          |                 |
======================================================================================================================================================

insert /*+ monitor no_gather_optimizer_statistics */ into t
with rws as (
  select level x from dual
  connect by level <= 10
)
  select * from rws;

select dbms_sqltune.report_sql_monitor (report_level=> 'basic +plan')
from   dual;

SQL Plan Monitoring Details (Plan Hash Value=2403765415)
======================================================================================================================================
| Id |            Operation             | Name |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                                  |      | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
======================================================================================================================================
|  0 | INSERT STATEMENT                 |      |         |      |         1 |     +0 |     1 |        0 |          |                 |
|  1 |   LOAD TABLE CONVENTIONAL        | T    |         |      |         1 |     +0 |     1 |        0 |          |                 |
|  2 |    VIEW                          |      |       1 |    2 |         1 |     +0 |     1 |       10 |          |                 |
|  3 |     CONNECT BY WITHOUT FILTERING |      |         |      |         1 |     +0 |     1 |       10 |          |                 |
|  4 |      FAST DUAL                   |      |       1 |    2 |         1 |     +0 |     1 |        1 |          |                 |
======================================================================================================================================

exec dbms_stats.lock_table_stats ( user, 'T' );

insert /*+ monitor */into t
with rws as (
  select level x from dual
  connect by level <= 10
)
  select * from rws;

select dbms_sqltune.report_sql_monitor (report_level=> 'basic +plan')
from   dual;

SQL Plan Monitoring Details (Plan Hash Value=2403765415)
======================================================================================================================================
| Id |            Operation             | Name |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                                  |      | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
======================================================================================================================================
|  0 | INSERT STATEMENT                 |      |         |      |         1 |     +0 |     1 |        0 |          |                 |
|  1 |   LOAD TABLE CONVENTIONAL        | T    |         |      |         1 |     +0 |     1 |        0 |          |                 |
|  2 |    VIEW                          |      |       1 |    2 |         1 |     +0 |     1 |       10 |          |                 |
|  3 |     CONNECT BY WITHOUT FILTERING |      |         |      |         1 |     +0 |     1 |       10 |          |                 |
|  4 |      FAST DUAL                   |      |       1 |    2 |         1 |     +0 |     1 |        1 |          |                 |
======================================================================================================================================

  as we know getting real time statistics for any dml operation thats
  occuring for more that 100k rows is very costly operation

How do you know this?


On Prem SQL Server: Log shipping via Azure Blob Storage: Possible?

Yes, but only manually - Azure Blob Storage isn't integrated into the log shipping wizards. You would need to do your own backups to Azure Blob Storage, and then have the secondary server(s) restore from ABS using the same certificates.

When other shops have done similar projects with AWS's S3 storage (writing their SQL Server backups there using 3rd party tools), I've seen them either log backups to a text file that the other SQL Servers check for backup files, or log entries into a centrally accessible table (like SQL Azure or Amazon RDS) and fetch the backup file names from there.


Will SQL 2008 SSIS package files work with SQL 2016 databases after database upgrades?

At our location, we have SSIS running on SQL Server 2012. Our databases are on SQL Server 2016. We haven't had any issues with any of them, but then again, it really depends on what your packages are doing.

My recommendation is to have some sort of "sandbox" server and restore one of the databases and change the compatibility level to the latest level. Then test the SSIS packages to see if they would work.


Error in PostgreSQL/PostGIS Access to offline bands disabled

Have you set the environment variables to enable rasters ?

  As of PostGIS 2.1.3, out-of-db rasters and all raster drivers are disabled by default. In order to re-enable these, you need to set the following environment variables: POSTGIS_GDAL_ENABLED_DRIVERS and POSTGIS_ENABLE_OUTDB_RASTERS in the server environment.

  If you want to enable offline raster:

  POSTGIS_ENABLE_OUTDB_RASTERS=1
    Any other setting or no setting at all will disable out of db rasters.

  In order to enable all GDAL drivers available in your GDAL install, set this environment variable as follows

  POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

From http://postgis.net/docs/postgis_installation.html#install_short_version


Lock them both (SYS and SYSTEM). You shouldn't need to use them day-to-day, nothing will break.

On a day to day basis you should be using named user accounts that have SYSDBA or SYSOPER.

Tom Kyte recommends doing it, so you can always blame him if something does go wrong :-)


How can I troubleshoot TNS connection issues? Do I need TRCROUTE executable?

ORA-12545 is typically caused by a scenario you described. The client connects to an address (e.g. SCAN address of a RAC) where its request is forwarded to another address (e.g. VIP address of a RAC node) which the client is unable to reach due to something (e.g. missing name resolution).

In this example situation trcroute would have been no help at all. It does not provide information on this even on a successful attempt and it does not care at all about services (just like tnsping), I can give it a non-existent service and it will still report success.

[oracle@o7ca2 ~]$ tnsping "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o7ca-scan.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HYPER_O7CA)))"

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 22-APR-2020 10:48:43

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o7ca-scan.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HYPER_O7CA)))
OK (10 msec)
[oracle@o7ca2 ~]$ trcroute "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o7ca-scan.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HYPER_O7CA)))"

Trace Route Utility for Linux: Version 19.0.0.0.0 - Production on 22-APR-2020 10:48:51

Copyright (c) 1995, 2019, Oracle.  All rights reserved.

Route of TrcRoute:
------------------

Node: Client            Time and address of entry into node:
-------------------------------------------------------------
22-APR-2020 10:48:51 DESCRIPTION =  ADDRESS =  PROTOCOL = TCP  HOST = o7ca-scan.balazs.vm  PORT = 1521    CONNECT_DATA =  SERVER = DEDICATED   SERVICE_NAME = HYPER_O7CA

Node: Server            Time and address of entry into node:
-------------------------------------------------------------
22-APR-2020 10:48:51 ADDRESS= PROTOCOL=TCP  HOST=o7ca-scan.balazs.vm  PORT=1521

[oracle@o7ca2 ~]$ lsnrctl services listener_scan1

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-APR-2020 10:49:06

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
Services Summary...
Service "HYPERXDB" has 4 instance(s).
  Instance "HYPER1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: o7ca1, pid: 4049>
         (ADDRESS=(PROTOCOL=tcp)(HOST=o7ca1.balazs.vm)(PORT=13707))
  Instance "HYPER2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: o7ca2, pid: 6754>
         (ADDRESS=(PROTOCOL=tcp)(HOST=o7ca2.balazs.vm)(PORT=24163))
  Instance "HYPER3", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: o7ca3, pid: 6526>
         (ADDRESS=(PROTOCOL=tcp)(HOST=o7ca3.balazs.vm)(PORT=15639))
  Instance "HYPER4", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: o7ca4, pid: 6593>
         (ADDRESS=(PROTOCOL=tcp)(HOST=o7ca4.balazs.vm)(PORT=22765))
Service "HYPER_O7CA" has 4 instance(s).
  Instance "HYPER1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.25)(PORT=1521))
  Instance "HYPER2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521))
  Instance "HYPER3", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.27)(PORT=1521))
  Instance "HYPER4", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.28)(PORT=1521))
Service "hyper" has 4 instance(s).
  Instance "HYPER1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.25)(PORT=1521))
  Instance "HYPER2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521))
  Instance "HYPER3", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.27)(PORT=1521))
  Instance "HYPER4", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.28)(PORT=1521))
The command completed successfully
[oracle@o7ca2 ~]$ trcroute "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o7ca-scan.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XXX)))"

Trace Route Utility for Linux: Version 19.0.0.0.0 - Production on 22-APR-2020 10:51:39

Copyright (c) 1995, 2019, Oracle.  All rights reserved.

Route of TrcRoute:
------------------

Node: Client            Time and address of entry into node:
-------------------------------------------------------------
22-APR-2020 10:51:39 DESCRIPTION =  ADDRESS =  PROTOCOL = TCP  HOST = o7ca-scan.balazs.vm  PORT = 1521    CONNECT_DATA =  SERVER = DEDICATED   SERVICE_NAME = XXX

Node: Server            Time and address of entry into node:
-------------------------------------------------------------
22-APR-2020 10:51:39 ADDRESS= PROTOCOL=TCP  HOST=o7ca-scan.balazs.vm  PORT=1521

[oracle@o7ca2 ~]$

Actually this is all that happens in the listener on a trcroute attempt:

22-APR-2020 10:45:21 * 12508
TNS-12508: TNS:listener could not resolve the COMMAND given

Using the TRCROUTE Utility to Test Connectivity from the Client

  The listener receives and processes the TRCROUTE connect packet. It
  returns the information to the client by putting it into a refuse
  packet.


Wordpress blog where pages are almost-always served from cache: SSD or more RAM?

If you are going to put MySQL in a wimpy environment (WIMP = Windows, IIS, MySQL, PHP/Perl/Python), your first priority needs to be RAM. Please get more RAM on the box.I suggest 32GB.

As for MySQL, you need to tune InnoDB as much as possible. Here is the InnoDB Architecture

InnoDB Architecture

Recommendation #1

By default, 25% of the Buffer Pool is used to process changes to secondary indexes. Thus, I suggest double the amount of data (you said 3-4 GB) as the Buffer Pool Size

innodb_buffer_pool_size = 8G

Recommendation #2

Since you have a dual core machine, split the Buffer Pool to prevent swapping

innodb_buffer_pool_instances = 2

Recommendation #3

You will need good read and write I/O threading

innodb_read_io_threads = 16
innodb_write_io_threads = 16

Recommendation #4

You need to the Log Buffer to be larger for increased write performance

innodb_log_buffer_size = 64M

Recommendation #5

You need the Log Buffer flushed often since you are in Windows

innodb_flush_log_at_trx_commit = 1

CAVEAT #1: The default for innodb_flush_log_at_trx_commit is 1 to maintain ACID compliance. Do not change this in Windows since Windows has the final say on disk caching.

CAVEAT #2: There is an option for InnoDB to cache data instead of letting the OS do it called innodb_flush_method. Unfortunately, the setting O_DIRECT is not available for Windows. If you switch to Linux, setting this option takes disk caching by the OS out of the equation.

Recommendation #6

Sequential and Random Reads from SSD can be the same or a mixed bag depending on the model SSD you get. If your IOPs exceed 10,000, you can change innodb_io_capacity to 10,000 and innodb_io_capacity_max to the maximum IOPs.

Summary

Get 32GB of RAM for the WIMP machine and use these setting in my.ini

[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 2
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 10000
innodb_io_capacity_max = (Maximum IOPs of the SSD)

Alternative to SSD Only

If you are will to configure it, there is a design that a Facebook Engineer uses to split up InnoDB tables and logs to lengthen the life of the SSD by writing the sequential stuff for InnoDB (Double Write Buffer, Log Files) to a fast HDD.

I have discussed this before

Feb 06, 2014 : MySQL on SSD - what are the disadvantages?
Aug 14, 2013 : How do I determine how much data is being written per day through insert, update and delete operations?
Jun 25, 2013 : Postgres Write Performance on Intel S3700 SSD
Jun 18, 2013 : How big can innodb_doublewrite_file grow?


why locks a table will improve performance during transaction?

Let's say you have a million row table.
One million individual row locks is a lot of memory that can be put to better use.

I'd also read some SQL Server articles on lock escalation:

SQL Server Storage engine team http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx (selected quote, my bold)

  Similarly, if you lock individual rows, you will get higher concurrency but then you will incur the overhead of acquiring/releasing locks on each row and lot more locking resources depending upon the isolation level of your transaction, as you may need to hold the locks on all the rows till the end of transaction.

DBA.SE: https://dba.stackexchange.com/a/6449/630
DBA.SE: https://dba.stackexchange.com/a/6515/630


Recovering from Oracle host OS disk failure (options)?

There is nothing 'magical' about Oracle and its recover ability. As long as you have your ASM metadata somewhere available, you can import it on a spare host and use that in a fresh installation to attach your SAN hosted files/LUNs and use that to continue your run on. Important is to know that all your data is in ASM and all your ASM disks are on the SAN. If you do happen to have some files containing application data are left on the Server that ran your databases before it crashed, it might cause an extra challenge.

Knowing that it is technically possible is one thing, the ability to perform such a recovery is an other one. The only way to gain that ability is to train for it. Each OS has some things that make it more or less easy to do. This is for any failure scenario.


What happens when the same application from different server tries to connect to the always on primary server

From a technical standpoint, there are no problems with multiple applications, running on multiple different machines, connecting to the same SQL Server instance.

If you experience the failover scenario that you described, it's possible that the reporting workload will interfere with your other workloads from a performance standpoint.

In other words, all the long read queries your reporting app does could block queries coming from your other apps.  And in general the reporting app queries will require CPU time and I/O time that is normally solely used by the other app.

It's hard to say without more detail whether this would just slow things down a little, or bring your whole app grinding to a halt due to blocking or resource contention.


MongoDB: Is it possible add SECONDARY member with version 3.0 to a replica set where members have version 2.6?

This is basically the rolling upgrade procedure from the docs, with the only difference being that you will temporarily be going up to a 4 member replica set based on what you have described. Hence it's basically a viable strategy if you have concerns about 3.0.

Couple of things I will note:

First, the priority zero step shows good caution but is not really gaining you anything. There is no realistic way for that node to become primary before it catches up (not considered up to date, and if the other two members fail there will not be enough votes to elect it primary with 2 out of 4). There's no harm in doing it, it just doesn't add much in a 4 member set.

The other thing that I will note is that the addition of a new set member is not actually needed. You can stop your current secondary and simply update its binaries to 3.0, restart it and it is good. Do the same for the arbiter, then step down the primary and repeat. No resync is then necessary because 3.0 is fine as a straight drop-in replacement for 2.6.


Easy way to check connectivity to SQL Server from client

A simple test method for SQL connectivity is to create an empty text file, with a file extension of "UDL".  You can create it in Notepad.  And it can have any name.  I use "TestSQL.UDL"

Save it on the desktop of a windows PC and double click it.

A "Data Link Properties" dialog box will pop up where you can enter the IP address of the SQL server and also a SQL username and password.

Click the "Test Connection" button to see if you can connect.


ORDER BY is the only way to guarantee order. If you have a unique key (primary key, unique index) then order by this, otherwise you'll need to order by all the columns in the table. If you have multiple unique keys and one of them is the clustering key for the table (or the first part of a larger clustering key) then ordering by that would be most efficient. As you don't know ahead of time what table definitions you have you will need to work out what candidate sorting keys you have by checking the contents of sys.indexes and friends.


At some point in the pre-RTM phase, it was used for some Polybase related process. As far as I can tell, it's 10260 is no longer in use for RTM so you can remove. Doesn't actually do anything anymore.


What is the difference between "ALTER DATABASE mydb SET search_path" and "SET search_path"?

ALTER DATABASE will change the search_path for every new connection to that database. The change however does not affect current connections - including the one that ran the ALTER DATABASE. The set search_path then changes the path also for the current connection so that it has the same value as the default path.


Min/ Max dates based on results of query

This should work to get the min and max date for you.

SELECT MIN(RAISED_DATE), MAX(RAISED_DATE), MIN(COM_DATE), MAX(COM_DATE)
FROM   (SELECT SO.COM_DATE, SO.RAISED_DATE, SE.SERV_NO, SO.OR_NO
        FROM S_ORDER SO, S_SERV SE
        WHERE SO.CU_NO='286243' AND SE.SERV_NO='69780'
        AND SUBSTR (SE.ACCT_NO, 1,8)=SO.CU_NO
        AND SO.RAISED_DATE +4/24 BETWEEN TO_DATE('2017-11-01','YYYY-MM-DD')
        AND TO_DATE('2018-07-30','YYYY-MM-DD'))

If you want the other data in the query you will need to add a group by

SELECT SO.COM_DATE, MIN(SO.RAISED_DATE), MAX(SO.RAISED_DATE), SE.SERV_NO, SO.OR_NO
FROM S_ORDER SO, S_SERV SE
WHERE SO.CU_NO='286243' AND SE.SERV_NO='69780'
AND SUBSTR (SE.ACCT_NO, 1,8)=SO.CU_NO
AND SO.RAISED_DATE +4/24 BETWEEN TO_DATE('2017-11-01','YYYY-MM-DD')
AND TO_DATE('2018-07-30','YYYY-MM-DD')
GROUP BY SO.COM_DATE, SE.SERV_NO, SO.OR_NO


SQL Server dbo permissions for specified databases

The code for SQL would be:

USE [DBName] --Insert DBName Here
GO

CREATE USER [SQLServerorWindowsUser]
  FOR LOGIN [SQLServerorWindowsUser] --add the user to the DB
GO

USE [DNBame]
GO

EXEC sp_addrolemember N'db_owner', N'SQLServerorWindowsUser'
GO

If you already have the user in the database you only need to execute:

USE [DNBame]
GO

EXEC sp_addrolemember N'db_owner', N'SQLServerorWindowsUser'
GO


Mongo replica set with no PRIMARY/SECONDARY, members are STARTUP2 and RECOVERING

I was able to resolve this by Breaking the Mirror.  Essentially, I picked one of the members, turned it off, removed the /data/local* files, turned it on, and did a rs.initiate().  At this point, I was a replica set of 1 (myself) and primary (obviously).  Then, for the other two guys, I turned them off, wiped their entire /data/* files and turned them back on.  From the original primary member, I simply added added the two new guys with rs.add("mongo_rs_5_member_1:27018") and rs.add("mongo_rs_5_member_2:27019").  Then the primary synced all the content to the other guys (many hours) and the replica set was health.  No more errors in the relevant application.


How many databases can an Amazon Aurora instance hold?

I'm speaking to the currently available version of Aurora which is MySQL 5.6 compatible. On the surface, it works just like MySQL. I have two databases on my Aurora cluster. My guess it can hold as many databases as MySQL or at least more than you would ever need. Where things differ is in how backups are handled. Aurora, like other RDS offerings, does cluster snapshots, not individual database backups. To restore a cluster snapshot, you actually create a new Aurora cluster, switch DNS to the new cluster, and then delete the source cluster. This means that if you have problems with one of your databases on a cluster, you have to restore all databases at the same time. So you might want to group application databases together. For instance, all our ticketing and code review software (Jira and Fisheye) run on the same Aurora cluster. The two apps are used hand-in-hand so the two would always need to be restored together.


How to differentiate between SQL and PL/SQL?

If it is wrapped in

BEGIN ... END
DECLARE ... END
CREATE OR REPLACE ... END
Is a one-liner prefixed EXECUTE

Then it is PL/SQL. What does this mean under the hood? SQL gets "compiled" to a query plan and executed, immediately returning a result set in the event of SELECT or the number of rows affected in other cases, or an error. PL/SQL however is more involved. Every object referenced in it is checked for existence and necessary grants, then the PL/SQL is compiled down to near-native code, which executes at full speed. The dependencies are tracked and if a table changes, then any PL/SQL referencing it requires recompilation. The reason for this is speed; since everything is done up front at compile time, there is no need to have any runtime checks in PL/SQL, whereas SQL statements must be checked every time (even tho' query plans are cached, the so-called soft parse, privileges must still be checked, and even a soft parse has a cost associated with it).

This is one of the "killer advantages" of stored procedures as Oracle does them; actually executing a PL/SQL stored proc or trigger does the absolute minimum amount of computation to get the result. Application code running outside of the Oracle kernel where it is untrusted has to jump through more hoops in order to get at the data. It's the same argument for strongly-typed, referentially transparent high-level languages such as OCaml or Haskell - do more work once, at compile time, and reap the benefits on the millions of time the code is executed.


restore database using bak file - resorting to old date?

Does your backup file contain multiple backups (known as backup sets), and are you restoring just the earliest backup set from the backup file?

To see the backup sets in a file run the following and note the Position column, which denotes the backup set:

RESTORE HEADERONLY FROM DISK='mybackup.bak'

To restore a specific backup set from a backup:

RESTORE DATABASE ... WITH FILE = <position from previous statement>

Why does this happen?

If you use the same filename every time in your backup script (e.g. BACKUP DATABASE MyDb TO DISK='backup.bak'), and that file is not deleted or changed between backups, then your backup is added to the backup sets in that file. If you then run RESTORE DATABASE ... without a WITH FILE=<position> clause, you will always restore the first backup set in the backup media - usually your earliest one.

The details:

The RESTORE HEADERONLY command is useful for a backup file (if you are looking at it on a different server to the one it was created on). You can also see the backups on the server that created them by running the command:

SELECT backup_set_id, media_set_id, position, backup_finish_date
FROM msdb.dbo.backupset

If you want to check which backup set you just restored from a multi-file media then you can run:

SELECT * FROM msdb.dbo.restorehistory

Note the backup_set_id which corresponds to the msdb backupset table and in turn to the media file and position within that file.

References:

From MSDN: RESTORE Arguments (Transact-SQL):

  Backup Set Options

  FILE ={ backup_set_file_number | @backup_set_file_number }

  ...

  When not specified, the default is 1.


I think your solution (two join tables) is valid. However, I suspect that you are tracking two different things, and wanted to be sure that you were clear on this in terms of design.

The artist(s) associated with a book do not necessarily have anything to do with the artists associated with a song, at the database level. Logically, the artist associated with a book will (almost) certainly also be associated with every song in the book; but, that's irrelevant in terms of the relationship between the artist and the song or the artist and the book.

Some things not to do (not that you necessarily were going to do any of them, but for the sake of someone else looking at this in the future):

Do not use the artists associated with the songs to define the artist for the book. I'm assuming that "artist" = "performer", but let's say it also included the writer of the song. A book of Elton John songs might show that every song not only has Elton John, performing, but Bernie Taupin writing; however, that's not necessarily relevant by default. You need the artist to be associated to the book directly.
Do not ignore the association between the book artist and each song. This is self-evident once a song is included in two different books, based on two different artists, but at an early stage, one may think that with the artist defined at the book level, one could just add additional artists at the dong level and let the book-level association cover that artist being associated with all the songs. Again, if the Brooks/Yearwood duet was going into a Brooks book and a Yearwood book, this would become problematic. You need that association at both levels, book and song.


Since your plan is to re-use schedules I would recommend a change in your thinking as follows:

a) Each service location can have one or more schedules - called location schedules (basically this is an association between a location, a predefined schedule and other data)

b) Each location schedule can have a priority (which allows you to create a custom schedule that overrides the default schedule)

c) Each location schedule has a start date and an end date when its active - which allows you to define a custom schedule which is active for only a specific period of time

d) A schedule has a name and description

e) A schedule has one or more schedule details - day of week, opening time, closing time

I have included an explanatory data model (MySQL)