There's no problem in canceling the MOVE operation. Oracle just rolls back.

Progress information can often be gathered from [g]v$session_longops, looking for sessions with time_remaining > 0 (or the specific session you're interested in when you know that).


No, sorry.

PostgreSQL generally indexes NULL fields, so would it would have to add an entry for each row.  You can make an index not index NULLs (using a partial index, or using a non-default type of index which doesn't index NULLs), but it is not implemented for such an index to observe an all-NULL constraint on the column and so take a shortcut of not scanning the table.  It would uselessly scan the table looking for not-NULLs anyway.  Interesting idea though.


Save SQL server job output results to text file

The job might not have completed . The output file will start with what you have received followed by the results.


Insert into select into multiple related tables using INSERT_IDENTITY

A solution that might work for you is using the OUTPUT clause, which spits out all the inserted rows, so you can re-insert them into a different table. However, this puts limitations on foreign key constraints on Table2, if memory serves.

Anyway, the solution would look something like this:

MERGE INTO Table1 AS t1
USING MyTable ON 1=0 -- always generates "not matched by target"

WHEN NOT MATCHED BY TARGET THEN
    -- INSERT into Table1:
    INSERT (A, B, C) VALUES (t1.A, t1.B, t1.C)

--- .. and INSERT into Table2:
OUTPUT inserted.ID, MyTable.D, MyTable.E, MyTable.F
INTO Table2 (ID, D, E, F);

MERGE, as opposed to the other DML statements, can reference other tables than just inserted and deleted, which is useful for you here.

More: http://sqlsunday.com/2013/08/04/cool-merge-features/


selective undoing of commited transactions in PostgreSQL

You have a few options here.  If it is just committed transactions you need to worry about I would use a trigger-based solution. There are already extensions to help with this.  See, for example, e-maj and there is good documentation in how to roll your own, and 2ndQuadrant has released a version of an audit trigger on GitHub.

One of the key issues you may have is in determining how to handle rolling back of selective updates.  Do you need to restore a table to a specific point in time?  Or just a row?  With any solution here you are likely to have to roll your own solution to this part.  Tablelog does indeed appear to be dead, unfortunately.


How to limit maximum number of rows in a table to just 1

These two constraints would do:

CREATE TABLE dbo.Configuration
( ConfigurationID TINYINT NOT NULL DEFAULT 1,
  -- the rest of the columns
  CONSTRAINT Configuration_PK
    PRIMARY KEY (ConfigurationID),
  CONSTRAINT Configuration_OnlyOneRow
    CHECK (ConfigurationID = 1)
) ;

You need both the PRIMARY KEY (or a UNIQUE constraint) so no two rows have the same ID value, and the CHECK constraint so all rows have the same ID value (arbitrarily chosen to 1).
In combination, the two almost opposite constraints restrict the number of rows to either zero or one.

On a fictional DBMS (no current SQL implementation allows this construction) that allows a primary key consisting of 0 columns, this would be a solution, too:

CREATE TABLE dbo.Configuration
( -- no ConfigurationID needed at all
  -- the rest of the columns
  CONSTRAINT Configuration_PK
    PRIMARY KEY ()                -- 0 columns!
) ;


MySQL Triggers Different Database Different Server

There's the FEDERATED storage engine, which makes for a way to create a "proxy" table in your database server, which points to a real table on another server.

FEDERATED is disabled by default. To enable it, add this to your my.cnf configuration file, under the [mysqld] section:

federated = 1

So your trigger on some table T would then INSERT into your FEDERATED table FT, which would connect to the remote server, pass credentials, and perform the operation.

Do note the overhead: triggers are by themselves performance killers. Add to that the connection to the remote machine, and you now also have latency to worry about.


Added a unique index, but cannot find it in TABLE_CONSTRAINTS

Use this syntax, which also allows you to name the constraint:

ALTER TABLE Persons ADD CONSTRAINT UQ_PersonID UNIQUE (P_Id,LastName)


What is the best choice for MySQL high availability (zero downtime)?

There is not such a thing as 100% availability. Even big providers like Google or Amazon only guarantee "three nines". The most critical systems tend to give you high availability "five nines" ("guaranteed" is different from "measured").

Some software solutions, including MySQL-related ones, can provide you something around 99,999%, but with very specific hardware, network, geographical and infrastructure provider redundancy, and probably suffering a lot of efficiency loss.

Almost all solutions will have at least some (mili)seconds of delay when doing geographical failover, making it not 100% available. Unless, of course, you redefine availability as "online schema upgrades", "floodings-do-not-count" or "hot software patches" which is possible with almost any modern MySQL/cluster-like setup (Galera, NDB, which by the way they are very different solutions).


This is all covered in the documentation, but basically:

Enable row movement for each table using ALTER TABLE ... ENABLE ROW MOVEMENT;
Shrink the object (TABLE/MATERIALISED VIEW/etc) with ALTER <object type> <object name> SHRINK SPACE;
Resize the tablespace datafiles with ALTER DATABASE DATAFILE '/path/to/your/datafile.dbf' RESIZE 500M;

Tim Hall covers this in great length, with every conceivable option, in this article - a great resource.


Slow SQL Server database on a specific server

Have you tried running Adam Machanic's sp_WhoIsActive while the query runs on prod?  This will show you other queries that may be taking up resources or blocking your query.

Can you confirm that the isolation level is the same for the database on the server versus your laptop?  Different isolation levels can contribute to various issues related to blocking.  You can check that by running the below in the context of the database in question.

SELECT
[name] AS DBName
, snapshot_isolation_state_desc
, is_read_committed_snapshot_on

FROM sys.databases


Given this solution, is there any way to tune the Secondary/Slave postgres cluster? For instance, since the backup server won't be responding to queries it doesn't need indices. Can I somehow disable these for the Secondary cluster?

  In what ways can I save in disk space and memory on my Secondary/Slave cluster given the knowledge that it's only serving as a 'dumb' backup?

If you do only logical backups (pg_dump), you'll save lots of space - but you'll have much larger data loss windows.

Currently your only other options are WAL archiving and streaming replication, and you're right that they have some big overheads.

You cannot exclude indexes or unwanted tables from the replica stream. Nor can you select only some databases to replicate. It's an all-or-nothing prospect. The only exception is unlogged tables - their contents don't get replicated, and neither do contents of their indexes. So you can use unlogged tables for transient data on the primary to save some I/O, WAL space, and disk space.

There are some logical streaming systems like Londiste and Slony-I, but they're only asynchronous. So you can have a potentially unbounded data loss window if the replica isn't keeping up with the primary.

There's ongoing work into adding synchronous streaming logical replication to PostgreSQL via the BDR project, but it won't be in 9.4 and might not be in 9.5 either, so it's a long-term thing.

Thankfully, a physical replica server has quite low CPU overheads and its disk I/O overhead is usually only moderate. So long as you throw enough spare disk space it it, it won't unduly burden your other application. There isn't really much you can do to tune it, though spreading out checkpoints longer on master can help reduce writes on the replica.

One option you may wish to consider is to sacrifice recovery time for lower overheads. Use WAL archiving with an archive_timeout on the master and monitor log shipping closely. Set up alerts to warn you urgently if logs stop arriving at the destination storage. Then just do a pg_basebackup and use WAL archiving to keep an asynchronous replica. It'll take a lot longer to start up if you ever need to restore it and it needs a ton of disk space, but it has near-zero CPU and RAM overhead and does very efficient sequential disk writes that the I/O subsystem barely notices. You can also have it write to any storage you feel like, not necessarily the spare server. The key thing with this is that you must test restores carefully - make sure you can deal with the long recovery times (and measure them for your data), ensure your base backup procedures are correct, rotate your base backups regularly, etc. I recommend PgBarman, which automates a lot of this for you. You must understand though that WAL archiving is asynchronous - there's always a data loss window of up to archive_timeout at any time, and it can get a lot bigger if your WAL archiving fails due to (eg) running out of disk space, as the master server will not stop or pause if the replication stops.

So for safety, stick with synchronous streaming replication. If you need to, buy a better server.


Importing dbatools module into Azure Automation Account

The issue with importing dbatools into Azure Automation Accounts has been resolved as of release 1.0.66...

https://twitter.com/wsmelton/status/1191370349390303234


Remove particular table from being replicated in a Multi-Master replication

Yes you can, simply add replicate-ignore-table in your my.cnf . please refer following link for mysql documentation :

https://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replicate-ignore-table


generate_series() returns a SETOF dates between the first and second argument, at intervals specified by the third argument. Seems to be what you're looking for.

testdb=# create table customers(customer_id bigint);
CREATE TABLE
testdb=# insert into customers select 1;
INSERT 0 1
testdb=# insert into customers select 2;
INSERT 0 1
testdb=# select customer_id, d from customers, generate_series('2018-11-01'::timestamp, '2018-12-01', '1 week') AS d order by 1, 2;
 customer_id |          d
-------------+---------------------
           1 | 2018-11-01 00:00:00
           1 | 2018-11-08 00:00:00
           1 | 2018-11-15 00:00:00
           1 | 2018-11-22 00:00:00
           1 | 2018-11-29 00:00:00
           2 | 2018-11-01 00:00:00
           2 | 2018-11-08 00:00:00
           2 | 2018-11-15 00:00:00
           2 | 2018-11-22 00:00:00
           2 | 2018-11-29 00:00:00
(10 rows)


Convert column value stripping html tags into sql view with rows and columns

TL;DR Answer
Convert HTML to XML and parse with XMLTABLE()
Long Answer
HTML and XML are both descendants of SGML.
From the sample data you have posted, it looks like you just need to remove the
 tags and wrap desc_data in a root element (eg <row>) for the conversion into XML.
Once in XML format, you can parse the data with XMLTABLE() to give you:

instance of  ( via for ordinality, column name RN )
value of @label
value of

This is done within the CTE named xml_data in my example.
Assuming value always follows label, you can convert the instance of  ( RN ) to have the same value for each pair. (this is also done in xml_data)
From there, you can PIVOT the data into a table that has:

your row identifier (RID)
value for label (LABEL is a key word so I used LABEL_TXT )
value for value (VALUE is a key word so I used VALUE_TXT )

This is done within the CTE labeled key_val in my example.
For the final query, the final columns names MUST BE KNOWN at compile time.  Once you know the columns, you can just do another PIVOT for a proper table.
Notes
As you can see, HTML is not a good data format to use for parsing data and/or using the data within a computer (eg a database).
Unless desc_data is used for display, I recommend that the data is stored in XML or JSON format.  (I'm assuming you can change the source)
Once data is in a parseable format, PL/SQL is no longer needed.
Example Code
with data as (
    select q'[Project Name
 Digital Dept Name
 FinanceLevel
 Level 5]'
        as txt
        ,1 AS RID
    from dual
),xml_data as (
select RID, a.rn - mod(a.rn+1,2) rn,a.label_txt, a.val
from data d, xmltable( '/row/span'
    passing xmltype( '<row>' || replace(d.txt,'
','') || '</row>' )
    columns
        rn for ordinality,
        label_txt varchar2(20)  path '/span/@class',
        val       varchar2(20)  path '/span'
    ) a
), key_val as (
    select rid, label_txt, value_txt
    from xml_data
        pivot (
            min( val )
            for label_txt in ( 'label' as LABEL_TXT, 'value' as VALUE_TXT)
        )
)
select RID, PNAME, LVL, DNAME
from key_val
    pivot (
        min( VALUE_TXT )
        for LABEL_TXT in ('Project Name' as PNAME, 'Level' as LVL, 'Dept Name' as DNAME )
)


postgres: Upgraded RDS Postgres from 9.4 - 9.6, id fields went from SERIAL to INT

While it appears that DMS supports migrating the SERIAL datatype, DMS doesn't support migrating default values like your SET DEFAULT nextval('pageviews_id_seq')

If you are running ongoing replication, then all the data in the read replica target is created in the source database, that is where the nextval from that sequence will be applied.

It kind of makes sense, because DMS is trying to explicity insert a value into that id column, and you shouldn't explicitly set a column that is supposed to have its value generated by a sequence.

If you're using DMS to migrate a database with the intention to switch off the old one then you'll have to

switch off DMS, then on the new database:
create the sequence object pageviews_id_seq, setting the sequence to start at a value higher than the max id in the table, so it's nextval doesn't collide with an existing id
alter the column to apply the SET DEFAULT nextval('pageviews_id_seq')

In your specific case, there is a trigger on the source database (see link below to docs) which looks like this

CREATE OR REPLACE FUNCTION <objects_schema>.awsdms_intercept_ddl()
  RETURNS event_trigger
LANGUAGE plpgsql
SECURITY DEFINER
  AS $$
  declare _qry text;
BEGIN
  if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE') then
         SELECT current_query() into _qry;
         insert into <objects_schema>.awsdms_ddl_audit
         values
         (
         default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
         );
         delete from <objects_schema>.awsdms_ddl_audit;
end if;
END;
$$;

It is trying to insert the default value for the first column in the table awsdms_ddl_audit.

According to the docs the table should look like this

create table <objects_schema>.awsdms_ddl_audit
(
  c_key    bigserial primary key,
  c_time   timestamp,    -- Informational
  c_user   varchar(64),  -- Informational: current_user
  c_txn    varchar(16),  -- Informational: current transaction
  c_tag    varchar(24),  -- Either 'CREATE TABLE' or 'ALTER TABLE' or 'DROP TABLE'
  c_oid    integer,      -- For future use - TG_OBJECTID
  c_name   varchar(64),  -- For future use - TG_OBJECTNAME
  c_schema varchar(64),  -- For future use - TG_SCHEMANAME. For now - holds current_schema
  c_ddlqry  text         -- The DDL query associated with the current DDL event
)

ckey is supposed to be a bigserial, so this should be inserting an incremented bigint automatically for you ( similar to IDENTITY on MSSQL ). The error says that it is failing when trying to insert a NULL, so can you check the datatype for that c_key column, I suspect in your DB the datatype isn't right.

!! Note that in the docs it says you need to do these thing in order
1. create the aws_ddl_audit table
2. create the trigger function
3. attatch the trigger to the ddl_command_end then execute it

The trigger monitors all create , update and drop table statements, which would include creating the aws_ddl_audit table if you create the table after the trigger.

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.Limitations


What locks are taken during statistic operations in SQL Server?

AFAIK, the only real X lock that is taken is on the 'statistics resource'.  The rest of the locks can be read around and don't block.

  That is a resource that only statistics operations care about. It
  exists to limit the number of stats operations in progress on a single
  table to one at a time. Auto-statistics operations do not wait for it
  but just abort if they cannot get it. Manual statistics operations do
  wait for the resource.

https://connect.microsoft.com/SQLServer/feedback/details/174619/update-statistics-holding-exclusive-lock-and-blocking


How to setup a MongoDB replica set for the connector

You can start a single node replica set by starting the mongod with the replSet argument/config option, that is all that is required.  It takes a name argument, which will then function as the name of the replica set in question.  Once you have done that, you simply run rs.initiate() from the shell when connected to the node (this is only needed once).

This is described in detail here:

http://docs.mongodb.org/manual/tutorial/convert-standalone-to-replica-set/

To stay with just a single node, simply do not follow the "Expanding the set" instructions.


Please note that your server seems to have recovered from a crash, according to the init log. Something strange may have happened there related to your problems (a crash, a non-clean stop, a memory leak, etc.)

There are several reasons why MySQL 5.6 may be consuming a lot of memory (specially compared to 5.5).

You have a 128Mb buffer pool (default), that may consume around 150 MB of memory having into account the extra buffers. Aside from other global buffers, you have to add the per-connection memory. You have a huge join_buffer_size on your configuration (128M), but it is commented. Make sure that it is not set to that value by executing:

SHOW GLOBAL VARIABLES like 'join_buffer_size';

Anything bigger that 4MB is normally not recommended.

The first one I would check, however, -which several people have reported memory problems with- is the performance_schema, a new feature that has been enabled by default in 5.6. You can check its memory consumption by doing:

SHOW ENGINE PERFORMANCE_SCHEMA STATUS;

For more information, we would need the runtime statistics of the server, namely:

SHOW ENGINE INNODB STATUS;
SHOW PROCESSLIST;
SHOW GLOBAL STATUS;

Edit to your edit

Do not set the Buffer Pool to 4 MB! That is a global value, and the most important cache buffer if you are using InnoDB.  That is too little even for an embedded system. The 4Mb was just for the join_buffer_size. If you don't know what to put there, and as you seem to be aiming for low memory usage, set it to something around the 50-80% of the total memory you aim to consume.

Your performance_schema consumption is certainly worrying. Set performance_schema = off on your configuration and restart MySQL Server.

Regarding per-connection-memory: simply you must know that there are some variables that reserve memory per action or per session. join_buffer_size, sort_buffer_size, etc. are session variables; innodb_buffer_pool_size is global to the server and is only reserved once. If you are using a single connection to the server, you may not have to worry too much. But please share the complete output of the previous commands, and your hardware specs/memory usage aims.


Two "Connect SQL" permissions in SQL Server 2012

While I can't be certain because you blanked out the Grantor I think you will find that the rows have a different entry in that column.  It is possible to have multiple entries of a permission in that list if the individual who granted the permission is different.


Is there a way, in a single SQL statement to ensure that all items in a list are present in some column?

If I understand what you're trying to achieve, then I think what you want is a CTE with a UNION list of all the possible tags you want to compare against, then RIGHT JOIN  the tag table against the CTE.

Example -

WITH ListOfTags (Tag) AS (SELECT 'Foo' UNION SELECT 'bar' UNION SELECT 'other' UNION SELECT 'thing')
SELECT  *
FROM    item_tag    i
INNER JOIN  tags    t
    ON i.tag_id = t.tag_id
RIGHT JOIN ListOfTags   l
    ON  l.Tag like t.tag
WHERE   i.item_id is null

This might not be the most elegant way of doing it, but it might work for you


Error trying to SET a field when two fields in different tables match

UPDATE ykz2d_maps_integration , ykz2d_crmery_deals
SET catid = '584'
where ykz2d_crmery_deals.company_id = ykz2d_maps_integration.company_id
;

The table must be listed in the update clause to avoi the error message


Use Oracle analytics:

SELECT distinct
  COUNT(userId) over (partition by date_range_start, date_range_end)
, AVG(age)      over (partition by date_range_start, date_range_end)
, STDDEV(age)   over (partition by date_range_start, date_range_end)
, MIN(age)      over (partition by date_range_start, date_range_end)
, MAX(age)      over (partition by date_range_start, date_range_end)
, date_range_start, date_range_end
FROM users
WHERE
(date_range_start >= TO_DATE('01-Dec-2010')) AND (date_range_end <= TO_DATE('30-Nov-2011'))
/

It does the same but most of the times it is surprizingly faster.


How to get the information of the optimizer of PostgreSQL?

You can get some of that information by turning on the configuration parameter log_planner_stats.  Most of that information, however, doesn't really exist, because the planner does not fully compute all alternative plans and their costs.  It only explores an alternative plan until it can determine that it is slower than the current best plan.  So alternative plans are not fully materialized.


Log to record activity against a database

You can enable general log by adding this line in my.cnf/my.ini file:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_general_log

[mysqld]
general_log=1
also You can set General log output by changing your my.cnf/my.ini file (MySQL configuration file) to either TABLE or FILE if TABLE is choosen then server will log all queries in general_log table of mysql schema otherwise in FILE option it will log in a file path mentioned.
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_log_output


Unknown Db file format - best way to find out?

If you are on a *nix system, you can try using the "strings" command on each file to peek at the contents. A header may give you a clue.

e.g.

strings file.dat | head

Here's an example run against a quicky sqlite3 database file.

~$ sqlite3 test.db
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table testtable (id INT);
sqlite> insert into testtable values (1);
sqlite> .quit
~$ strings test.db
SQLite format 3
Ktabletesttabletesttable
CREATE TABLE testtable (id INT)


Select pairs (a,b) excluding (b,a) with same value

SELECT
    p1.Id,p2.Id,p1.Name
FROM Pairs p1
     JOIN Pairs p2
     ON p1.Name = p2.Name
     AND p1.Id < p2.Id


Can't create SSIS package from Import and Export Wizard

We are using SQL Server 2012 SP3 [11.0.6020.0] Enterprise Edition and SSMS 2014 [12.0.4100.1] and have the same issue.

The pictures below are:

SSMS version
Import/Export Wizard complaining about edition
Result of @@version showing Enterprise Edition

Since at work I had SQL Server 2008 R2 SP1 Enterprise Edition [10.50.2500.0] + SSMS RTM [10.50.1600.1] (they just applied SP1 on server only, all clients remained RTM) I know 100% this couple was working fine.
I patched my Studio so it then became 10.50.6000.34 and this couple: SQL Server 2008 SP3 + SSMS SP3 was still working fine.
At home I use both the server and the studio patched, i.e. both of them [10.50.6000.34] and this configuration also has no issue.

Then the Server at work migrated: all the dbs were moved onto SQL Server 2012 SP3 that is shown on the picture [11.0.6020.0], for sure Enterprise Edition, we are using partitioning...And here came a surprise: our clients have the new SSMS 2014 [12.0.4100.1] installed, and the Wizard does not save the packages anymore!
That was the first time I see the bizard complaining about Edition.
So I was wondered if it's a known issue or no and I googled it up. I found no workarounds or explanaitions but I did found the Connect Item

Using Enterprise Edition; Can't Save SSIS Package from IMPORT WIZARD

Status :

Closed
  as Not Reproducible  Not Reproducible

So I just concluded it's a bug that won't be fixed  (ok. because we cannot give any reproducible scenario), but it's just there

I had no chance to test your version [10.50.4000.0] and tis is the first time I read that the issue persisted on 2008 R2. I thought it was SSMS 2014's bug


slow autovacuum on table with binary data

4M rows with each one up to 1MB means that the table is up to 4TB, not counting the obsolete rows themselves.  1.5 hours would be astonishingly fast to vacuum a table of that size.  Especially if you still have the default settings of "autovacuum_vacuum_cost_delay" and kin, which would throttle the autovacuum to about 8MB/S of read throughput, and less of write throughput.

All parts of the table not already having the visibility map set for it will have to be read into memory, but not all at the same time.  All the tables indexes will also have to be read, perhaps multiple times.


Does the SQL Server generate the contents for a derived table contents for every matching record in a join

You would expect the NEWID() to be calculated once, for the derived table t2. This is not the case, as it is calculated for each row of the join resultset of T1 and T2.

Simply put, for each row where T1.C1 = T2.C1 or in other words where T1.C1=1.

Here is a part of a statement from Microsoft regarding this:

  Closing the loop . . . I've discussed this question with the Dev team.
  And eventually we have decided not to change current behavior, for the
  following reasons:

  1) The optimizer does not guarantee timing or number of executions of
  scalar functions. This is a long-established tenet. It's the
  fundamental 'leeway' that allows the optimizer enough freedom to gain
  significant improvements in query-plan execution....

As such the optimizer can choose the timing or number of executions, to get the best performance.

The source of the quote and some more information and examples and the response from microsoft

To explain it we best go through the query plan that is created for the UPDATE statement.

Right of the bat we don't see a join predicate, but simply a  table scan.
A predicate is added to that table scan, to only get the rows where C1 = 1

This  is the 'join' reduced to a predicate.

After this, 2 rows are found that match this predicate, and are processed by the first Compute Scalar operator.

And NEWID() is calculated for each column of the joined table + derived table.

A workaround

The obvious fix is to use a variable to hold the NEWID() value as to calculate it before the join & reuse it.

create table t1 ( c1 int, c2 int, c3 uniqueidentifier null)

go
insert t1(c1,c2) values (1,2),(1,3)
go
DECLARE @test uniqueidentifier = newid()
update t1
set c2 = 5 + c2,
    c3 = t2.c3
from t1 join
( select c1=1, c3=@test)   as t2 on t1.c1 = t2.c1
go
select * from t1
go
drop table t1
go

If you would have more than 1 source columns (T2) you would need to store the NEWID() values in a temporary table or table variable

Another interesting part:

  So, we have a choice: if we want to guarantee a certain behavior in
  the presence of non-deterministic (side-effecting) code – so that
  results of JOINs, for example, follow the semantics of a nested-loop
  execution – then we can use appropriate OPTIONs to force that behavior
  – as UC points out. But the resulting code will run slow – that's the
  cost of, in effect, hobbling the Query Optimizer.

Source

By using functions we could force the optimizer to first calculate the NEWID() on the source table, before the join. But I am not able to do that on the query at hand (probably not the query's fault).


How to prevent concurrency issues in a ssis package?

Since it seems that any concurrent execution of this package is going to cause this problem for you, I'd recommend putting controls into the package that prevent itself from running while another job is running it.

As far as I know, there is no mechanism within SSIS to set a package as 'one execute at a time only', so you would need to have a solution to this implemented in your SQL database. Create a table that records each instance of the package being executed (this is useful to do anyway for auditing/debugging purposes) and record the start/end times of package execution, and maybe a column for status. If there is a job still running, the end time will be null, so your package should check for this and quit if it exists.

The risk with this approach is that a fatal error might cause an unfinished row to remain  in the table, blocking any further execution. You should put something into your package to update it under all error conditions, and perhaps some logic to finish a row for a job if it has gone on too long (figuring out this duration is a business question)


Using MySQL active-passive with RBR DDL without breaking replication?

ALTER will go through RBR, so I don't see it as being much different.

As long as

you don't do SELECT *, the fetches should remain OK.
no queries touch the column you are removing any time near the action, write queries should not be an issue.

If you are trying to avoid the table being blocked a long time, I see some use for the 'failover' you mentioned (but did not adequately detail).

What version do you have?  5.6 rebuilds "in place" and permits concurrent DML.  So, the 'failover' may be overkill.


Why does null and true evaluate to null but null or true evaluates to true?

OR is defined that if either side is true the expression evaluates to true.
AND is defined such that if both sides are true boolean-3VL context, the expression evaluates to true;
null is not true.


Apply the latest Service Pack and Cumulative Update

When patching SQL Server:

Apply the most recent Service Pack, then
Apply the most recent Cumulative Update for that Service Pack.

You never have to go through Service Packs or Cumulative Updates in order. Just use the most recent of each, in the order above.


How can I connect my other table using the mysqli_insert_id?

There is an error in line:

$sql = "INSERT INTO transaction_details (`edi_transaction_id`,`details`) VALUES ($edi_transaction_id,$details) ";

This should be:

$sql = "INSERT INTO transaction_details (`transaction_id`,`details`) VALUES ($edi_transaction_id,$details) ";

Finally in your code you assign the text of the command to the variable $sql, but do not execute it.


Help converting a MySQL query to postgres

This kind of queries do not need variables in Postgres. You can use window functions:

WITH cte AS
( SELECT id,
         1 + (ROW_NUMBER() OVER (ORDER BY id DESC) - 1) / $n  AS new_x
  FROM t
)
UPDATE t
SET x = new_x
FROM cte
WHERE t.id = cte.id ;

You 1 + (ROW_NUMBER() OVER (ORDER BY id DESC) - 1) / $n could also be written similarly to your expression, if you prefer. Assuming that $n is an integer parameter, the result will be the same:

ceiling(ROW_NUMBER() OVER (ORDER BY id DESC) / $n::numeric)


The variable you're looking for is innodb_status_output.  Setting it to OFF should stop the innodb status from being dumped into the error log.


Execute Sql statement and send result to email

Your code is backwards.  You are sending the email, then generating the query results.  The sp_send_dbmail stored procedure will run the query for you, and append the results either in the body of the email or as an attachment.

In case you aren't aware, Microsoft has extensive, comprehensive, and free documentation on their website for SQL Server.  For instance, sp_send_dbmail is available in great detail at:  https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql

The @query parameter is documented as:

  [ @query= ] 'query'
  Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

They also have several examples of calling sp_send_dbmail, including this salient point:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = 'yourfriend@Adventure-Works.com',
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
                  WHERE DueDate > ''2004-04-30''
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

So, you need to pass the query definition into msdb.dbo.sp_send_dbmail like this:

DECLARE @msg VARCHAR(250);
DECLARE @query varchar(2048);

SET @msg = 'Please refer to the attached spread sheet for the report.';

SET @query='
    SELECT
        account.accountID,
        account.name
    FROM
        dbo.account
        LEFT OUTER JOIN dbo.accountfeaturesetting afs
            ON afs.accountid = account.accountid
                AND afs.featureid = ''Schedules''
                AND afs.settingid = ''EditReasons''
    WHERE
        ISNULL(afs.Value, ''0'') = ''1''
        AND NOT EXISTS
            (
                SELECT 1
                FROM dbo.program
                WHERE program.AccountID = account.AccountID
                    AND program.Active = 1
                    AND (
                        program.ScheduleEditReasonFlags <> 0
                        OR program.ScheduleEditReasonFields <> 0
                        )
            )
        AND account.IsMaster = 0;';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'abc'
    , @recipients = 'recipients@company.com'
    , @subject = 'queryresultset'
    , @body= @msg
    , @body_format = 'TEXT'
    , @query = @query
    , @execute_query_database = 'MyDB'
    , @attach_query_result_as_file = 1
    , @query_attachment_filename='warning.csv'
    , @query_result_header = 1
    , @query_result_width = 80
    , @query_result_separator = ' '
    , @exclude_query_output = 0
    , @append_query_error = 1
    , @query_no_truncate = 0
    , @query_result_no_padding = 0;

Also note the extra parameters included, which control various output factors such as how the query is formatted etc.

Note in the @query text above, I've "escaped" the single quotes to prevent SQL Server from considering them as literal delimiters.  Essentially, any single-quotes appearing inside the @query parameter need to be turned into two single-quotes.  Erland Sommarskog has an excellent piece on Dynamic SQL and nested strings.


On Sql Server can you disable an account at the database level?

You can simply deny connect privileges to the specified database. This may be less disruptive than dropping the user (in case you want to give them access again later, you won't have to re-create the user, set up all the permissions again, etc.).

USE database2;
GO
DENY CONNECT TO account_name;

When connect is denied, if they try to establish a new session connected to this database, they will get:

  The database database2 is not accessible.

If they try to select from a table in the denied database, for example, they'll see:

  Msg 916, Level 14, State 1
  The server principal "account_name" is not able to access the database "database2" under the current security context.

To give them access again:

USE database2;
GO
GRANT CONNECT TO account_name;


Where's my missing Sql Server Express 2005 Database space? Size and space available dont add up to 4G?

On the question of the size of things, first of all you said that you shrank a database, so that would reduce the DB size of the space that was discarded during the shrink.  John M also provided some clarification.

Regarding the numbers in the bottom picture, they are:

select (2585832 / 1024.0) /* KB from the Reserved in the bottom picture to MB */
       + 43.96 /* MB free */

SpaceAllocated
---------------
2569.186562

So you can see that 2569.19 MB is essentially the same as 2569.73 MB

The other numbers in the bottom picture are showing you how that database is allocated:

reserved        data         index_size  unused
-----------     ----------   ---------   ---------
2585832 KB   =  2315184 KB + 137216 KB + 133432 KB

So it is pretty straight-forward and actually gives you some useful insight into your space utilization.

Additional Notes: Yes, when you shrink a database, it actually compacts the data and then releases the no longer used disk space.  Of course, SQL Server 2005 Express can have many databases that are 4 GB.  (If you update to a more recent version of SQL Server Express, the limit is 10 GB per database, not including the log space.)

Within a database the unused column in the example, defines how much space currently exists in the database file that could be used for adding more data.  However, the database can be expanded to its maximum size or you can set autogrowth to allow it to grow in selected increments. (Use exact growth values, not percentages.)


Can't grant privileges to "root" when it has no password in MariaDB

"If the NO_AUTO_CREATE_USER SQL_MODE is set, users can only be created with a CREATE USER statement. In this case GRANT will produce an error when the specified user does not exist." -- https://mariadb.com/kb/en/mariadb/grant/

If that is the case, then the solution is probably:

CREATE USER 'root'@'192.168.3.%';
GRANT ... (as you tried)

"Can't find any matching row..." means that there was no row with both user='root' and host='192.168.3.%'.  You were coming in with user='root' and host='localhost' (or something like that).


Why is our 1000 MB database file not uploading completely via phpMyAdmin?

Try this

For Export:

mysqldump -u [user] -p [db_name] | gzip > [filename_to_compress.sql.gz]

For Import:

gunzip < [compressed_filename.sql.gz]  | mysql -u [user] -p[password] [databasename]

Note: There is no space between the keyword '-p' and your password.


SQL Server stored procedure running in infinite loop

Add AND COALESCE(' + @fieldname + ',0) <> 1 to your WHERE clause. You're updating the same 5,000 rows over and over and over and over...


If your 50 connections all want to do SELECTs that need tmp tables, even small ones, they could overflow tmpfs and fail right and left.

I prefer to let tmp tables be in the same filesystem where everything else is.  That way, it is not arbitrarily causing trouble when there is a spike in traffic.

Meanwhile, I keep track of the slowlog and aggressively tackle any slow queries.  Note that queries using big tmp table tend to be slow, so the slowlog generally catches them.

Generally, optimizing a query will provide much more than a 50% improvement.

Optimizing queries decreases the "load".

50 concurrent connections actually active simultaneously running (not just "Sleep" in SHOW PROCESSLIST) is unacceptable in my book.

When a query dies, it should remove the tmp table.  If the server crashes, there is a chance that the tmp table will remain.  This often happens with ALTER.

When a write operation dies in the middle, and the table is Engine=MyISAM, the write will be partially finished, and there is no way to know how much was done or not done.  Examples:  INSERT ... SELECT ... and DELETE ... WHERE ... SELECT ...


Can't create file /var/lib/mysql/user.lower-test

This problems seems to permission issues or change the mysql data direcory to other location then /var/lib/mysql.

  Try to run with sudo

Looks like /var/lib/mysql is owned by the mysql user and the group is set to mysql as well.

$ sudo chown -R mysql /var/lib/mysql
$ sudo chgrp -R mysql /var/lib/mysql
$ sudo chmod 755 /var/lib/mysql

Have you checked that the directory /var/lib/mysql does actually exist and corresponds to your data directory? If it does not, then you probably must specify otherwise the datadir parameter under the mysqld section

[mysqld]
datadir=/var/lib/mysql

Then you can chose between configuring apparmor to allow MySQL to read\execute\modify the new data directory and sub directories or, at your very own risk, you can remove apparmor. You may need to be root to do the following:

/etc/init.d/apparmor stop
/etc/init.d/apparmor teardown
update-rc.d -f apparmor remove
apt-get purge apparmor
reboot


Solved:

ERROR 1030 (HY000) at line 810: Got error -1 from storage engine

The above error means you have innodb_force_recovery set in your my.cnf
I'm not sure why I couldn't find this easily in mysql documentation, but it's simple enough.


Configure MariaDB PAM authentication, and enabling remote login from Toad

I have downloaded HeidiSQL and confirmed that its use of libmysql.dll means that it can use the PAM authentication. It looks like toad for MySQL utilises myodbc.dll which cannot handle PAM authentication


Can I replicate to a pre-existing database?

Yes. After dropping the subscription, you will be able to simply configure replication from the new publisher to the existing subscriber. There is no need to drop the subscriber DB. Please bare in mind however that the subscriber will need to be re-initialized from the new publication. (Assuming transactional or merge publication) this can be achieved either through the snapshot agent or a backup/restore.

Also bare in mind that assuming your new publisher will also be your new distributor, you will need to configure this as well.


I have an instance of RDS of MySQL 5.7.10

If you have the performance_schema enabled, then this will consume roughly 500Mb of RAM, then in addition to that you need to take into account your buffer pool size settings (RDS defaults these to 3/4 of the RAM).

Adding these two together can push the allocated ram size over 1Gb quite quickly - note that you have 182Mb of swap used too.

The 24.5Mb of free RAM is probably the OS file cache, which can be discarded by the OS should other RAM demands become too much.

Try turning off the performance_schema in the RDS parameter group and see if that makes any difference.


Try this

select right(rtrim(alt.Address),2),
   rtrim(left(ltrim(alt.Address),len(ltrim(rtrim(alt.Address))) - 2))

A slight variation on the second expression (city), which works the same but uses fewer ltrim/rtrim calls (which makes it more concise, albeit not necessarily clearer; it depends on how explicit you prefer the logic to be):

select right(rtrim(alt.Address),2),
   ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))

You can play with both options in this live demo at dbfiddle.uk.


First query running in PL/SQL Developer results the error

It seems your question contains the answer.
By default PL/SQL Developer gathers statistical data per session for every query.
In the SQL Window and in the debugger's Test Window you can use the Statistics page to view the resources used by the last executed SQL statement or program unit. This information can be a big help when optimizing your SQL or PL/SQL code.

If the user you have logged in, dose not heve the privilege to query on v$ views (as described in the error description) you will not be able to see the statistics result in Test Window and the message will be shown.
To solve the problem you may choose any of the approaches bellow:

Grant the user the privilege to select on desired v$ views.
GRANT SELECT ON sys.V_$SESSION  TO scott;
GRANT SELECT ON sys.V_$SESSTAT  TO scott;
GRANT SELECT ON sys.V_$STATNAME TO scott;
Or disable automatic statistics in PL/SQL Developer in preferences menu.


Something like this (untested) might be what you're after:

SELECT Month(IH.InvoiceDate) as Month,
    COUNT(*) as Count,
    SUM(CASE WHEN IH.IsBackend = 0 THEN 1 ELSE 0 END) as DirectMobile,
    SUM(CASE WHEN IH.IsBackend = 1 AND PerformaInvoiceNo <> '' AND PerformaInvoiceNo <> '0' THEN 1 ELSE 0 END) as Preselling,
    SUM(CASE WHEN IH.IsBackend = 1 AND (ID.PerformaInvoiceNo = '' OR ID.PerformaInvoiceNo = '0') THEN 1 ELSE 0 END) as DirectWeb
FROM InvoiceDetail ID
INNER JOIN InvoiceHeader IH USING (InvoiceNo)
GROUP BY Month(IH.InvoiceDate);

This emulates the effect of the filter clause that is available in Postgres. See https://modern-sql.com/feature/filter It's optional in the SQL:2003 standard.


How to change a web application that now needs to become OLTP and OLAP application?

OK - I'm going to slightly revise my advice in the previous thread.

If you have only 1  physical server, you're always going to have problems with OLTP conflicting with OLAP - i.e. disk bottleneck. It would be far easier to use PostgreSQL as the server for both than the other way round - see my previous comments on the richer PostgreSQL SQL functionality.

We also don't know what sort of activity your app has, nor do we know your CPU, RAM and disk config (RAID?).

In the light of what you have written above, my advice is to use PostgreSQL for both - maybe two separate schemas on the same instance of PostgreSQL - or possibly two instances of PostgreSQL on the same machine. This is more or less what @Mat and @CraigRinger said. The logic behind separating the schemas is that when and if you get a second server (which you should), parititioning the functionality between the two machines will be trivial.

If most of the functionality of your app to date is of a CRUD nature, then porting it to PostgreSQL should be relatively painless, but, again, YMMV.


How to write correct array string parameter for mysql query?

Use find_in_Set
Schema (MySQL v5.7)
CREATE TABLE group_of_id_s (id_s int ,`date`date);

INSERT INTO group_of_id_s VALUES(1,'2020-08-01'),(2,'2020-07-01');

delimiter //
CREATE PROCEDURE myproc (IN parameter TEXT)
       BEGIN
         SELECT id_s FROM group_of_id_s
         WHERE find_in_set(`date`, parameter) > 0;
       END//
delimiter ;

Query #1
CALL myproc("2020-08-01,2020-08-01,2020-08-02,2020-08-03,2020-08-04");

| id_s |
| ---- |
| 1    |

View on DB Fiddle


Changing MaximumErrorCount when executing maintenance plan

Each task in a Maintenance Plan has a variety of options that can be changed by right-clicking the desired task, then selecting the "Properties" option from the context-menu.  As in:

To allow a task to have multiple errors prior to failing, simply modify the MaximumErrorCount property I've highlighted above to some number greater than 1.

Having said that, Maintenance Plans are difficult to manage.

Options I recommend using instead:

Ola Hallengren has a great replacement solution that is extensible and makes management a breeze.
MinionWare Backup - built by DBAs for DBAs.


MMS is assigning same alias to two data nodes of different shards and replicasets

From the MMS Dashboard; what is the MMS Agent Log saying?

Have you looked in the mms-monitoring agent log locally on the server?

/var/log/mongodb-mms/monitoring-agent.log

Update

Please add a screen shot of how you are configuring the host

Update 14:02

Can you ping S143, S144 and mongo-client2 from the server where the mms-agent is installed?

Can you telnet to those servers from the mms-agent machine?

ex

telnet S143 37007
telnet S144 37007
telnet mongo-client2 37007

If you can't, then perhaps there is a network problem between the server with the MMS agent and the three mongods you listed above.


Faulting application path: \SFSMSSQL\c$\Program Files\Microsoft SQL Server\110\Tools\Binn\OSQL.EXE
Faulting module path: C:\Windows\SYSTEM32\ntdll.dll

You appear to be running osql.exe from another server via the c$ share.  If so, the local ntdll.dll is probably a different version to the osql.exe on the remote server.
Change the path
\\SFSMSSQL\c$\Program Files\Microsoft SQL Server\110\Tools\Binn\OSQL.EXE

to something like:
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\OSQL.EXE


Azure SQL DB doesn't allow ad-hoc 3-part names (database.schema.table) in queries.

Azure SQL DB Managed Instances do - but that's a different product. If you have a lot of existing stored procedures (say, hundreds) referring to a lot of existing objects in different databases (say, thousands), then it may be easier to migrate to Managed Instances than do what I'm about to describe.

To get cross-database queries working in Azure SQL DB, then in each database where you want to run the query, you need to:

Create an external data source - which is kinda like a linked server
Create an external table - which is kinda like a view that points to the table you want to query, going through the external data source

The instructions are fairly straightforward, but here's the painful part: you have to do this for every database you want to query, for every table/view/etc that you want to query. Plus, if the table changes (like new columns are added or a data type changes), then you have to go back and revisit all of the external table references that point to it, and change their structure as well.

This is one of those things that "technically works" in Azure SQL DB, but really only works for new from-scratch applications, not migrating an existing application to the cloud. Like I said - if you've got a lot of these queries referencing a lot of objects, now you understand why Managed Instances might be a better fit for you.


Creator of a MySQL table automatically granted all table permissions?

Seems Silber Schatz doesn't have it fully explained,

Because CREATE doesn't implicitly imply INSERT.

For a database level grant ALL is usually used.


Find rows that have the same values in another column

It's probably best to do this in the application, after all it's just presentation. Something like:

SELECT user, permission
FROM T
ORDER BY permission

then keep track of when permission changes.

However (untested), there seems to be a GROUP_CONCAT function that can do what you want:

SELECT GROUP_CONCAT(user), permission
FROM T
GROUP BY permission
ORDER BY permission


Sudden MySQL error: Unable to connect to any of the MySQL Hosts

ISSUE #1 : Connection Errors

Once mysql has racked up enough connection errors, the host blocks all IPs until you do one of two things:

$ service mysql restart
mysql> FLUSH HOSTS;

According to MySQL Documentation on FLUSH HOSTS;

  HOSTS

  empties the host cache tables. You should flush the host tables
  if some of your hosts change IP address or if you get the error
  message Host 'host_name' is blocked. When more than max_connect_errors
  errors occur successively for a given host while connecting to the
  MySQL server, MySQL assumes that something is wrong and blocks the
  host from further connection requests. Flushing the host tables
  enables further connection attempts from the host. See Section
  C.5.2.6, “Host 'host_name' is blocked”. You can start mysqld with
  --max_connect_errors=999999999 to avoid this error message.

Try bumping up max_connect_errors (default is 10) to some obnoxiously high number, like 999999999 (about 1 billion), in /etc/my.cnf as follows:

[mysqld]
max-connect-errors=999999999

You will then just restar mysql. To put this new setting in place without restarting, just run this command in the mysql client:

mysql> SET GLOBAL max_connect_errors = 999999999;

ISSUE #2 : DNS

If you are connecting to mysql using DNS, this could be one possible source of issues if:

DNS servers go down
DNS servers cannot be reached

To alleviate DNS issues, you need to add the following to /etc/my.cnf (mysql restart required):

[mysqld]
skip-host-cache
skip-name-resolve

You also need to look at the host column of mysql.user. If any of the users have a DNS name, replace it with one of the following choices:

Public IP
Private IP
LoadBalacner-managed VIP
Netblocks (such as 10.1.2.%) of the first three choices

Afterwards, run mysql> FLUSH PRIVILEGES;


Table Design - Storing Mulitple Parameters Required for Data Warehouse Report

In previous jobs where I had to do a similar thing (one report connecting to many parameters), I went for your first option:

store them in a [child] SQL table which has just 2 columns (I used ParameterName and ParameterValue and each parameter is added as a row.

It's nice and normalised, plus things don't break when you have to add/remove/change parameters, since you don't have to change your table structure to add a new parameter.

Try to avoid RBAR (Row-By-Agonising-Row) processing in your function that parses the (variable size) list of parameters into 1 string. Number tables or recursive CTEs come in useful to keep the function runtimes down.


Tracking Progress Within a Job

While the data and metadata are being transferred, a master table is
used to track the progress within a job.
...
The master table is created in the schema of the current user
performing the export or import operation. Therefore, that user must
have the CREATE TABLE system privilege and a sufficient tablespace
quota for creation of the master table.

So alter the user performing the export to have another default tablespace. That way the master table will be created in another tablespace.


Partial local index on partitioned Oracle table

You can create indexes only for certain partitions - of course this is possible only for LOCAL indexes.

The problem is you would have to set DEFAULT ATTRIBUTES INDEXING ON in order to get index created on new partitions. Index partitions older than 5 days you would have to delete (i.e. ALTER TABLE ... MODIFY PARTITION ... UNUSABLE LOCAL INDEXES; or ALTER INDEX ... MODIFY PARTITION ... UNUSABLE;) manually.

Of course you can set DEFAULT ATTRIBUTES INDEXING OFF but then you have to create (i.e. rebuild) the new index partition every day - which should be the same amount of effort.

It is no big deal to run such task (either of mentioned above) in PL/SQL procedure which is executed daily by a scheduler job in the database.

Inital task

-- Turn off Indexing for existing partitions
BEGIN
    FOR aPart IN (SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE TABLE1 MODIFY PARTITION '||aPart.PARTITION_NAME||' INDEXING OFF';
    END LOOP;
END;
/

-- Create the index
CREATE INDEX TABLE1 ON idx (column1, column2) LOCAL INDEXING PARTIAL;

-- Enable indexing, otherwise new partitions will not get indexed.
ALTER TABLE TABLE1 MODIFY DEFAULT ATTRIBUTES INDEXING ON;

-- Rebuild index on most recent partitions
DECLARE
   ts TIMESTAMP;
BEGIN
    FOR aPart IN (SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
        EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
        IF ts > SYSTIMESTAMP - INTERVAL '5' DAY THEN
           EXECUTE IMMEDIATE 'ALTER INDEX idx REBUILD PARTITION '||aPart .PARTITION_NAME;
        END IF;
    END LOOP;
END;
/

Daily task

DECLARE
   ts TIMESTAMP;
BEGIN
    FOR aPart IN (SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
        EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
        IF ts < SYSTIMESTAMP - INTERVAL '5' DAY THEN
           EXECUTE IMMEDIATE 'ALTER TABLE TABLE1 MODIFY PARTITION '||aPart .PARTITION_NAME||' UNUSABLE LOCAL INDEXES';
        END IF;
    END LOOP;
END;
/

However if you don't query data older than 5 days then this approach does not have any effect on performance, you will just save some disc space.


LogShipping does not replicate server level objects such as logins, so your user will be unable to log in to the secondary server when you switch roles.

You can migrate existing logins using the sp_help_revlogin stored procedure, keeping SIDs and passwords for SQL authenticated logins.


GHOST_CLEANUP not freeing up space on SQL Server Availability Group

There is a connect item. It is fixed in SP4.


Open source and commercial tools to build dynamic queries of fact tables in star schema

Mondrian OLAP server
http://mondrian.pentaho.com/

Pentaho Schema Workbench (for creating the cube schema)
http://mondrian.pentaho.com/documentation/workbench.php

Pentaho Aggregation Designer (for finding useful aggregations and building them)
http://infocenter.pentaho.com/help/index.jsp?topic=%2Faggregation_designer_guide%2Fconcept_pad_overview.html

Saiku AJAX pivot tables
http://analytical-labs.com/

Pentaho Report Designer for designing reports.

Pentaho Data Integration (Kettle) for ETL into data warehouse
http://kettle.pentaho.com/

DataCleaner for quality control


What is Required to use Oracle Performance Manager to monitor SQL Server?

I think you are probably looking for Oracle Enterprise Manager or OEM as it is normally referred as, a good starting point for more information is here.

a. Oracle database starting with 10G+ do their own data collection (Automatic workload Repository), however, you need to be licensed to access much of that data. You can still use statspack (which is free). OEM actually uses this collected performance metrics and uses them for display via its pages.
b. Live views are available to use (the V$ and GV$) in Oracle, no experience with SQLServer, but I suspect you have similar views available.
c. OEM can send you alerts on events and conditions that exceed user defined thresholds. This is configurable via templates or custom metrics (aka User Defined Metrics).
d. OEM has some performance reporting built-in, but you can use OEM repository to roll your own reports, it is not  very difficult (have done it in the past). OEM repository is simply a different database, the SYSMAN schema contains tables and views which expose all collected data. You can adjust retention, only constrained by available storage. It can provide your historic information as well as realtime performance for Oracle. You will have to check the plug-in for similar capability for SQLServer.

OEM can (with using optional plugins) monitor SQL Server, check this link for a brief overview. In my shop there is no SS, so I don't have first hand experience in using these plugins.
Licensing information for OEM is here, I'd definitely recommend reading this manual to  you and your customers.
There could be, but once you read through the information linked above, you will get a better idea. Then a quick chat with your Oracle Sales team will yield more information as well.
Some of the extension packs for OEM are listed here.

Hope this gives you enough information to get started on it.


If eventual consistency is acceptable and all your queries are aggregates then perhaps a low-latency OLAP system might work for you.  Your requirement sounds a bit like an algorithmic trading platform.  This type of architecture is often used in trading floor systems that have a requirement to carry out aggregate statistical analysis computations on up to date data.

If you can partition your data by date and old rows don't get updated then you can build a hybrid OLAP system using a conventional OLAP server such as Microsoft Analysis services backed by an ordinary RDBMS platform.  It should be possible to make this cope with ~4TB of data and both SQL Server and SSAS will do shared-disk clusters.  Similar OLAP systems (e.g. Oracle/Hyperion Essbase) are available from other vendors.

OLAP servers work by persisting data in a native store, along with aggregates.  Most will support partitioned data.  In addition, most will also work in a ROLAP mode, where they issue queries against the underlying database.  The important thing to note is that the storage strategy can be managed on a per-partition basis, and you can switch a partition from one to the other programatically,

In this model, historical data is stored in MOLAP partitions with aggregates of the data also persisted.  If a query can be satisfied from the aggregates then the server will use them.  Aggregates can be tuned to suit the queries, and correct aggregates will dramatically reduce the amount of computation needed to resolve the query.  Very responsive aggregate queries are possible with this type of system.

Realtime data can be implemented by maintaining a small leading partition - for the current month, day or even hour if necessary.  The OLAP server will issue queries against the database; if this partition is small enough the DBMS will be able to respond quickly.  A regular process creates new leading partitions and converts closed historical periods to MOLAP.  Older partitions can be merged, allowing the historical data to be managed at any grain desired.

The clients writing to the database just write straight out the the underlying RDBMS.  If historical data remains static they will only be writing to the leading partition.  4TB is a practical volume to use SSDs for if you need extra DBMS performance.  Even mainstream vendors have SSD based offerings with faster SLC units as an option.


Is password complexity policy linked to Active Directory in SQL Server

As far as I know, as long as the users have been created with CHECK_POLICY they will inherit it from the domain.

If not, you'll have to enable the policy use for all of them.
Or use SQL Server 2012 BPA, as it has a check for this part (2).

  The security policy might be set in Windows, or might be received from the domain (1).

https://msdn.microsoft.com/en-us/library/ms161959(v=sql.110).aspx (1)

https://support.microsoft.com/en-us/help/2028712/understanding-password-policy-for-sql-server-logins (2)


Building distinct Column from a common column from two tables

The following code should get you the result for MS SQL or MySQL:

select distinct f.Account_code__C,
case when a.Account_code__C<>'' then 'Y' else 'N' end AS DQ_TOBE,
case when b.Account_code__C<>'' then 'Y' else 'N' end AS DQ_CURRENT
from (
    SELECT Account_code__C FROM GODS.DQ_TOBE
    union
    SELECT Account_code__C FROM GODS.DQ_CURRENT
) f
left join GODS.DQ_TOBE a on a.Account_code__C=f.Account_code__C
left join GODS.DQ_CURRENT b on b.Account_code__C=f.Account_code__C


According to the PostgreSQL documentation on the COPY command the values must be  separated by the delimiter character which is by default a tab (\t). If spaces are used instead I get a similar error on PostgreSQL 9.5. BTW: Which version do you use?

Alternatively you might try to alter the delimiter character:

nd1=> COPY address_types (game_id, device_type) FROM stdin with delimiter '#';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1#Ps3
>> 2#Xbox
>> 3#Other
>> \.
COPY 3


SSMS Error when creating database "Method not found .. StringCollection"

You have several options:

Make sure SSMS 2016 has the latest fixes for that version. Many people forget that tools need patching as well as server components. See Latest Builds of Management Studio for more information.

Consider updating SSMS to the latest major version, which is 17.2 at the time of writing. From that link:

This version of SSMS works with all supported versions of SQL Server 2008 - SQL Server 2017 and provides the greatest level of support for working with the latest cloud features in Azure SQL Database and Azure SQL Data Warehouse.

Perform an uninstall and reinstall. You could reinstall the same version if you like.


How do you create a new database with new sid on Oracle's pre-built 11g virtual machine?

Are you sure that your Developer Days VM 2016-06-02_13 runs Oracle 11g?  I'm using the same, and it reports:

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0  Production                                                                  0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

Reading your questions/explanations, I cannot tell how familiar you are with 12c's "multitenant option", which "allows a single container database (CDB) to host multiple separate pluggable databases (PDB)" (see eg oracle-base.com).  For starters, a new PDB may be just what you need ... an overview of Oracle Multitenant can be found here.

In this article, section "Where to get started", you can see that the SERVICE NAME (not the SID) is used for connecting to a PDB.  More about PDBs (cloning etc), with screen shots, can be found here.  SQLDeveloper (already installed on the Developer Days VM) is probably the best tool for getting started.  I'm not aware of any "wizards" that one could use.

Using Java: When connecting to a PBD, using a JDBC driver, make sure that you use the service name (again: not the SID).

SID (for connections): it seems that the SID is only used for connecting to CDBs (not PDBs) nowadays.  Although - I have also used the "service name" successfully for accessing CDBs.


Create incremental/differential backups in ProgressDB

Look here for the Progress docs: http://communities.progress.com/pcom/docs/DOC-16074

The manual you're looking for is the "Database Administration", and search for "incremental backups"


Inserting a list of emails into multiple rows on the same column

You should provide details about:

The table you want to update
The format of the data of the email list.

The obvious problem is that just a list of email adresses wont't help much, since you don't know who they belong to. So what is the unique key for the table identifying the employee, and is that key also in the list of email adresses?

If the list provides the key, what you can do is load that list into a temp table (using sql loader), and then run a procedure or script that updates the table based on the values and unique key of the employee from the temp table, and then when done and no errors, remove the temp table.


Since the application is running on user workstation, it is likely to be run on user's own account. This, unfortunately, means you are out of luck with the simple solution - setting up user permissions.

What could work is an approach based on stored procedures with EXECUTE AS.

There are lots of tutorials in the net. For a summary:

Create a user account for application usage and grant it appropriate rights to the database objects.
Create stored procedures to handle all the required data operations. Use
EXECUTE AS to run those procedures as the application user.
Grant your users rights to execute the procedures.
Revoke other permissions from the users.


Help with Access many-to-many relationship (beginner)

Your design seems almost correct. I can pinpoint the following problems though:

You're using the table Owners for two purposes:

Have a list of owners (as entities/companies/societies)
Have a list of people with stakes in each entity

You need to break Owners in two, for example:

CREATE TABLE Owners (
  OwnerID int autoinc primar key,
  [Entity name] varchar(200)
);
CREATE TABLE Stakeholders (
  OwnerID int not null references Owners,
  PersonID int not null references "Contact List",
  [Ownership %] float not null,
  unique (OwnerID, PersonID)
);

The above requires you modify Contact List to include a PersonID int autoinc not null primary key, since you could have more than one person with the same name and surname.


solution for non-technical user uploads to SQL Server DB?

There are lots of tools and methods available for loading data. But the easier you want this to be for end users the more complex and sophisticated your dataloading routine will become.

Data validation and business rules need to be defined and implemented. Somebody has to do it. No software is smart enough to do that "auto-magically".

You could build data checking into the spreadsheet. Hopefully this would stop errors before you load. It is possible to import data directly from a db query and there is a variety of lookup and validation tools in excel.

You could build an import routine in ssis  and apply validations, filters, lookups and substitutions as part of the import process. It can even handle errors and redirect invalid records back to the user. But this takes time and effort.

My preferred approach would be to use a db to begin with rather than excel. That way datatypes, dependencies, rules and constraints can be enforced from the start. You could develop an interface in MS-access.

Unfortunately all of these things take time. The more sophisticated you want the app to be more time it will take to develop.


There are basically two ways to do this in a maintainable way:

Use a metadata framework to generate the data warehouse
Use Extended properties
(http://technet.microsoft.com/en-us/library/ms190243(v=sql.105).aspx)

Ad 1) The reason you are having this issue on the first place is that you are not creating metadata first and then using that metadata (with a simple code generator) to generate the tables and views in the database. There are several frameworks available that will allow you to have an almost pure meta driven data warehouse model. Check out TimeExtender (http://www.timextender.com/) and Vulcan (https://vulcan.codeplex.com/) for a few examples. With metadata driven modelling, documentation happens as a natural part of the modeling process

Ad 2) If you already have a model (which it sounds like you do) a great way to document it to end users, is to add Extended properties to the views as you develop/maintain them. These properties be added both to the view itself and to do the columns in the view. With a simple piece of code in your favourite programming languages - you can query the extended properties to auto generate documentation. A good way to expose this documentation to the end user is to render web pages, so they can navigate the data model from there. Another advantage of this model is that the documentation now lives inside the database (you can back it up as part of your routine backup procedure) and the BI developers have it available at their fingertips (nobody likes to document code in Word documents)


The extra predicate wont mess up the indexing. Most likely your index will be evaluated first and any rows that are found will be evaluated against the predicate cityid = 100.

On the other hand, assuming your index is defined as (usergroupid, birthdate). If you remove usergroupid = 54 from your query, the index won't do any good and you will have to evaluate all rows in the table against:  birthdate BETWEEN '1980-01-01' AND '1983-01-01') AND cityid = 100


Most people that perform mysqldumps just use the --all-databases option. That will include the mysql database. There are two schools of thought as to whether one should include the mysql database.

Why not to include mysql ???

When you mysqldump the mysql schema, you should make yourself aware of differences in MySQL versions, particularly the mysql.user table.

In MySQL 5.0, mysql.user has 37 columns
In MySQL 5.1, mysql.user has 39 columns
In MySQL 5.5, mysql.user has 42 columns

Restoring a mysqldump from one version can result in certain privileges disappearing when restored :

Make sure you handle dumping user grants as a special script. There are two methods for this:

METHOD #1 : Use pt-show-grants

This Percona Toolkit program move print out the User Permission in Pure SQL. You could run the result output into a Text File. Then, execute the Text File in MySQL 5.5.24. End of Story.

pt-show-grants ... > MySQLUserGrants.sql

METHOD #2 : Emulate pt-show-grants

I made my own technique for pt-show-grants

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

I have discussed this before

Apr 09, 2012 : Mysql users deleted
Apr 12, 2012 : Cannot GRANT privileges as root
Jun 13, 2012 : Fastest way to move a database from one server to another

Why include mysql ???

The only case for which you can mysqldump the mysql schema is to restore it to the same version of mysql

UPDATE 2014-12-30 17:42 EST

My original answer covered just the MySQL Grants since this particular aspect of the mysql schema is the easiest to break and overlook.

There are other reasons not to just backup the mysql schema unless it is necessary

If you want to retain the timestamps for all Stored Procedures, you can mysqldump the mysql.proc table by itself and load it. You can also just copy the proc.frm, proc.MYD, and proc.MYI then reload it by copying it back into the mysql subsfolder and runninf FLUSH TABLES;
If you use table based logging for the general log (mysql.general_log) and slow log (mysql.slow_log), you do not want to backup these logs and move them to another server where the logs do not semantically apply. It is your choice if you want to restore them.
If you set any timezone info in the mysql, be very careful if you do not want to restore the mysql schema in a data center that is in another timezone.

If you want to be careful about what you want to backup and restore in the mysql schema, you should mysqldump the mysql schema in a separate file.

The number of tables in the mysql schema, as well as the number of columns, will vary from version to version. So, you should login to mysql and run

SHOW TABLES FROM mysql;

The list of tables may not include events (like MySQL 5.0), may not have InnoDB system tables (like MySQL 5.6 has), may not include tablespace grants (like MySQL 5.6), and so forth. Sp, if you feel you must backup the mysql schema, you be very conservative (ok very picky and careful) about what tables you truly need to backup from the mysql schema.


This query will show you any schema which includes objects shipped by Microsoft:

SELECT s.name
FROM sys.schemas s
WHERE EXISTS (
    SELECT 1
    FROM sys.objects o
    WHERE o.is_ms_shipped = 1
        AND o.schema_id = s.schema_id
    )
ORDER BY s.name;

If you run that, you'll see dbo and sys listed.

This query shows the owner for each schema present in the current database:

SELECT [Schema] = s.name
    , [Owner] = dp.name
FROM sys.schemas s
    INNER JOIN sys.database_principals dp ON s.principal_id = dp.principal_id
ORDER BY s.name;

When a schema is created, the "owner" of the schema is either the users default schema or dbo, unless a schema owner is declared in the schema definition.  Take for example:

USE MaxTest;
GO
CREATE USER SchemaOwner WITHOUT LOGIN;
GO
CREATE SCHEMA MaxSchema AUTHORIZATION SchemaOwner;
GO

SELECT [Schema] = s.name
    , [Owner] = dp.name
FROM sys.schemas s
    INNER JOIN sys.database_principals dp ON s.principal_id = dp.principal_id
WHERE s.name = N'MaxSchema';
GO
DROP SCHEMA MaxSchema;
DROP USER SchemaOwner;

The output shows:

╔═══════════╦═════════════╗
║  Schema   ║    Owner    ║
╠═══════════╬═════════════╣
║ MaxSchema ║ SchemaOwner ║
╚═══════════╩═════════════╝

For schemas that have been created recently, you can use the default trace to see who created that schema.

First step is to gather the default trace events into a temp table:

DECLARE @trcfilename nvarchar(260);

SELECT @trcfilename = path
FROM sys.traces
WHERE is_default = 1;

IF OBJECT_ID(N'tempdb..#trctemp', N'U') IS NOT NULL
BEGIN
    DROP TABLE #trctemp;
END

SELECT *
INTO #trctemp
FROM sys.fn_trace_gettable(@trcfilename, default) tt

Once you've got the details in the #trctemp table, use the following query to show schema create events, along with the login that created the schema:

SELECT [Login] = sp.name
    , ObjectName
    , DatabaseName
    , tt.StartTime
    , EventSubClass = CASE tt.EventSubClass
        WHEN 0 THEN 'Begin'
        WHEN 1 THEN 'Commit'
        ELSE 'Unknown'
      END
FROM #trctemp tt
    LEFT JOIN sys.trace_subclass_values tsv ON tt.EventClass = tsv.trace_event_id
        AND tt.EventSubClass = tsv.subclass_value
    LEFT JOIN sys.server_principals sp ON CONVERT(varbinary, tt.LoginSid, 0) = sp.sid
WHERE tt.EventClass = 46 --Object Created
    AND tt.ObjectType = 17235 --Schema
    AND tt.DatabaseID = DB_ID()
ORDER BY tt.EventSequence;

sys.trace_subclass_values is deprecated, and may go away in a future version of SQL Server.  However, it is unlikely that it will go away anytime soon.

Output looks like:

╔═════════════╦════════════╦══════════════╦═════════════════════════╦═══════════════╗
║    Login    ║ ObjectName ║ DatabaseName ║        StartTime        ║ EventSubClass ║
╠═════════════╬════════════╬══════════════╬═════════════════════════╬═══════════════╣
║ Domain\User ║ MaxSchema  ║ MaxTest      ║ 2019-06-20 08:26:01.223 ║ Begin         ║
║ Domain\User ║ MaxSchema  ║ MaxTest      ║ 2019-06-20 08:26:01.223 ║ Commit        ║
║ Domain\User ║ MaxSchema  ║ MaxTest      ║ 2019-06-20 08:26:26.747 ║ Begin         ║
║ Domain\User ║ MaxSchema  ║ MaxTest      ║ 2019-06-20 08:26:26.747 ║ Commit        ║
╚═════════════╩════════════╩══════════════╩═════════════════════════╩═══════════════╝


Database replication with ability to revert to previous states

You can look into MySQL snapshots using the file system. More details in this link. Snapshots are very quick to create, and can be rolled back to.


How to connect from local computer (mac) to remote Oracle DB and execute script and save results to CSV?

SQL*Plus starting with version 12.2 supports printing the output in CSV format.

You can add the below in your script:

set markup csv on

Then execute your script called myscript.sql as:

sqlplus 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))' @myscript.sql

For example:

[oracle@o71 ~]$ cat myscript.sql
set markup csv on
set feedback off echo off termout off
spool my.csv
select username, account_status, created from dba_users where username like 'SYS%';
spool off
exit

[oracle@o71 ~]$ sqlplus -S 'bp/bp@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o71.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SIMPLE)))' @myscript.sql
[oracle@o71 ~]$ cat my.csv

"USERNAME","ACCOUNT_STATUS","CREATED"
"SYS","OPEN","10-JAN-18"
"SYSTEM","OPEN","10-JAN-18"
"SYS$UMF","EXPIRED & LOCKED","10-JAN-18"
"SYSBACKUP","EXPIRED & LOCKED","10-JAN-18"
"SYSRAC","EXPIRED & LOCKED","10-JAN-18"
"SYSKM","EXPIRED & LOCKED","10-JAN-18"
"SYSDG","EXPIRED & LOCKED","10-JAN-18"


How to automatically backup a MySQL/Amazon RDS database?

This is not possible using RDS snapshots.  AWS RDS Snapshots are "hidden" (though almost certainly in S3).  They are not accessible to you, the user, at all.  They can only be restored to RDS.

The only way to get data out from RDS MySQL instances is to export it using mysqldump (http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.SmallExisting.html is a guide to importing, but works equally well for exporting), or by configuring a MySQL replica (http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html).


Is the function PARSENAME() the opposite of QUOTENAME()

Based on the comment by ypercubeᵀᴹ which reads:

  ...can you find a string x that parsename(quotename(x),1) will be different than x? If not, that falls into the definition of "inverse function" ;)  ...

... I created a simple statement to have a look at the different values returned by these functions. The code is as follows:

Code

DECLARE
-- Text that I will be converting using QUOTENAME()
@Text2Quote_1 AS NVARCHAR(20),
@Text2Quote_2 AS NVARCHAR(20),
@Text2Quote_3 AS NVARCHAR(20),
@Text2Quote_4 AS NVARCHAR(20),
-- The characters used for "quotation"
@QuoteChar_1 AS NCHAR(1),
@QuoteChar_2 AS NCHAR(1),
@QuoteChar_3 AS NCHAR(1),
-- The Parsing option as defined in the original MS documnenation for PARSSENAME()
@ParseParam AS INT

SET @Text2Quote_1 = N'Test'
SET @Text2Quote_2 = N'[Test]'
SET @Text2Quote_3 = N'Test.dbo.test'
SET @Text2Quote_4 = N'[Test].[dbo].[test]'

SET @QuoteChar_1 = ''''
SET @QuoteChar_2 = '['
SET @QuoteChar_3 = '"'

SET @ParseParam = 1 -- Parsing level : Object name
-- checking the results for ' single quotes
SELECT  @Text2Quote_1 AS OriginalText_1,
        @Text2Quote_2 AS OriginalText_2,
        @Text2Quote_3 AS OriginalText_3,
        @Text2Quote_4 AS OriginalText_4
SELECT  QUOTENAME(@Text2Quote_1,@QuoteChar_1) AS QuotedText_1,
        QUOTENAME(@Text2Quote_2,@QuoteChar_1) AS QuotedText_2,
        QUOTENAME(@Text2Quote_3,@QuoteChar_1) AS QuotedText_3,
        QUOTENAME(@Text2Quote_4,@QuoteChar_1) AS QuotedText_4
select  PARSENAME(QUOTENAME(@Text2Quote_1,@QuoteChar_1),@ParseParam) AS Parsed_QuotedText_1,
        PARSENAME(QUOTENAME(@Text2Quote_2,@QuoteChar_1),@ParseParam) AS Parsed_QuotedText_2,
        PARSENAME(QUOTENAME(@Text2Quote_3,@QuoteChar_1),@ParseParam) AS Parsed_QuotedText_3,
        PARSENAME(QUOTENAME(@Text2Quote_4,@QuoteChar_1),@ParseParam) AS Parsed_QuotedText_4

Basically, I set a string values, output that value, QUOTENAME() and output the value, then PARSENAME() the QUOTENAME()ed value and output it.

Results

The results were quite interesting:

OriginalText_1           OriginalText_2           OriginalText_3           OriginalText_4
------------------------ ------------------------ ------------------------ ------------------------
Test                     [Test]                   Test.dbo.test            [Test].[dbo].[test]

(1 row(s) affected)

QuotedText_1             QuotedText_2             QuotedText_3             QuotedText_4
------------------------ ------------------------ ------------------------ ------------------------
'Test'                   '[Test]'                 'Test.dbo.test'          '[Test].[dbo].[test]'

(1 row(s) affected)

Parsed_QuotedText_1      Parsed_QuotedText_2      Parsed_QuotedText_3      Parsed_QuotedText_4
------------------------ ------------------------ ------------------------ ------------------------
'Test'                   NULL                     test'                    NULL

(1 row(s) affected)

When the base text Test.dbo.test (OriginalText_3) is passed on to the QUOTENAME() function it gets converted to: 'Test.dbo.test' (QuotedText_3)

When the QuotedText_3 string 'Test.dbo.test' is passed on to the PARSENAME() function it is converted to: test' (Parsed_QuotedText_3)

Conclusion

Seeing as I have proven ypercubeᵀᴹ's thesis wrong, I think it is safe to state that the function PARSENAME() is not the inverse of QUOTENAME().

Based on Aaron Bertrand's feedback here the code for square brackets [ ] (add to original script)

Additional Code for square brackets [ ]

-- checking the results for [ brackets
SELECT  @Text2Quote_1 AS OriginalText_1,
        @Text2Quote_2 AS OriginalText_2,
        @Text2Quote_3 AS OriginalText_3,
        @Text2Quote_4 AS OriginalText_4
SELECT  QUOTENAME(@Text2Quote_1,@QuoteChar_2) AS QuotedText_1,
        QUOTENAME(@Text2Quote_2,@QuoteChar_2) AS QuotedText_2,
        QUOTENAME(@Text2Quote_3,@QuoteChar_2) AS QuotedText_3,
        QUOTENAME(@Text2Quote_4,@QuoteChar_2) AS QuotedText_4
select  PARSENAME(QUOTENAME(@Text2Quote_1,@QuoteChar_2),@ParseParam) AS Parsed_QuotedText_1,
        PARSENAME(QUOTENAME(@Text2Quote_2,@QuoteChar_2),@ParseParam) AS Parsed_QuotedText_2,
        PARSENAME(QUOTENAME(@Text2Quote_3,@QuoteChar_2),@ParseParam) AS Parsed_QuotedText_3,
        PARSENAME(QUOTENAME(@Text2Quote_4,@QuoteChar_2),@ParseParam) AS Parsed_QuotedText_4

Output for square brackets [ ]

OriginalText_1           OriginalText_2           OriginalText_3           OriginalText_4
------------------------ ------------------------ ------------------------ ------------------------
Test                     [Test]                   Test.dbo.test            [Test].[dbo].[test]

(1 row(s) affected)

QuotedText_1             QuotedText_2             QuotedText_3             QuotedText_4
------------------------ ------------------------ ------------------------ ------------------------
[Test]                   [[Test]]]                [Test.dbo.test]          [[Test]].[dbo]].[test]]]

(1 row(s) affected)

Parsed_QuotedText_1      Parsed_QuotedText_2      Parsed_QuotedText_3      Parsed_QuotedText_4
------------------------ ------------------------ ------------------------ ------------------------
Test                     [Test]                   Test.dbo.test            [Test].[dbo].[test]

(1 row(s) affected)

and to complete things the code for the double quotes

Additional code for double quotes "

-- checking the results for [ brackets
SELECT  @Text2Quote_1 AS OriginalText_1,
        @Text2Quote_2 AS OriginalText_2,
        @Text2Quote_3 AS OriginalText_3,
        @Text2Quote_4 AS OriginalText_4
SELECT  QUOTENAME(@Text2Quote_1,@QuoteChar_3) AS QuotedText_1,
        QUOTENAME(@Text2Quote_2,@QuoteChar_3) AS QuotedText_2,
        QUOTENAME(@Text2Quote_3,@QuoteChar_3) AS QuotedText_3,
        QUOTENAME(@Text2Quote_4,@QuoteChar_3) AS QuotedText_4
select  PARSENAME(QUOTENAME(@Text2Quote_1,@QuoteChar_3),@ParseParam) AS Parsed_QuotedText_1,
        PARSENAME(QUOTENAME(@Text2Quote_2,@QuoteChar_3),@ParseParam) AS Parsed_QuotedText_2,
        PARSENAME(QUOTENAME(@Text2Quote_3,@QuoteChar_3),@ParseParam) AS Parsed_QuotedText_3,
        PARSENAME(QUOTENAME(@Text2Quote_4,@QuoteChar_3),@ParseParam) AS Parsed_QuotedText_4

Output for double quotes "

OriginalText_1           OriginalText_2           OriginalText_3           OriginalText_4
------------------------ ------------------------ ------------------------ ------------------------
Test                     [Test]                   Test.dbo.test            [Test].[dbo].[test]

(1 row(s) affected)

QuotedText_1             QuotedText_2             QuotedText_3             QuotedText_4
------------------------ ------------------------ ------------------------ ------------------------
"Test"                   "[Test]"                 "Test.dbo.test"          "[Test].[dbo].[test]"

(1 row(s) affected)

Parsed_QuotedText_1      Parsed_QuotedText_2      Parsed_QuotedText_3      Parsed_QuotedText_4
------------------------ ------------------------ ------------------------ ------------------------
Test                     [Test]                   Test.dbo.test            [Test].[dbo].[test]

(1 row(s) affected)

Based on ypercube™'s feedback and Aaron Bertrand's new feedback we have come to a mutual conclusion

PARSENAME() is the inverse of QUOTENAME() if the following conditions are met:

PARSENAME(x,1)-1(QUOTENAME(x,q)) where q={1,2} and x='{'any_string_value'}

References:

PARSENAME (Transact SQL) (Microsoft Docs)
QUOTENAME (Transact SQL) (Microsoft Docs)


inconsistency between @@global.gtid_executed and mysql.gtid_executed

mysql.gtid_executed is an internal table used to support some specific use cases; it's not meant to be monitored/used like @@global.gtid_executed (and other ways to access it, such as performance_schema.global_variables).

And it's only updated at certain times, which is what you were seeing. You can read more about it here.


every further filter you want to add simply increase the number in the IN Clauses
If you have a new attrubute you have to add another AND in the WHERE clause
As i said in the comment attribute_id in attribute_values is redundant

CREATE TABLE fabric_attribute_value
    (`id` varchar(3), `attribute_value_id` varchar(19), `fabric` varchar(6), `attribute_id` varchar(12))
;

INSERT INTO fabric_attribute_value
    (`id`, `attribute_value_id`, `fabric`, `attribute_id`)
VALUES

    ('1', '6', '1', '2'),
    ('2', '7', '1', '3'),
    ('3', '2', '2', '2'),
    ('4', '7', '2', '3'),
    ('5', '6', '3', '2'),
    ('6', '8', '3', '3'),
    ('7', '2', '4', '2'),
    ('8', '8', '4', '3')
;

CREATE TABLE fabrics
    (`id` int, `title` varchar(8), `code` varchar(4))
;

INSERT INTO fabrics
    (`id`, `title`, `code`)
VALUES
    (1, 'Fabric 1', 'FBR1'),
    (2, 'Fabric 2', 'FBR2'),
    (3, 'Fabric 3', 'FBR3'),
    (4, 'Fabric 4', 'FBR4')
;

SELECT `title`, `code`
FROM fabric_attribute_value av
INNER JOIN fabrics f ON av.`fabric` = f.`id`
WHERE (`attribute_id` = 2 AND (`attribute_value_id` IN ( 2 , 6)))
AND `fabric` IN (SELECT `fabric`
                 FROM fabric_attribute_value
                 WHERE (`attribute_id` = 3 AND (`attribute_value_id` IN (7))))

title    | code
:------- | :---
Fabric 1 | FBR1
Fabric 2 | FBR2

SELECT `title`, `code`
FROM fabric_attribute_value av
INNER JOIN fabrics f ON av.`fabric` = f.`id`
WHERE (`attribute_id` = 2 AND (`attribute_value_id` IN ( 2 , 6)))
AND `fabric` IN (SELECT `fabric`
                 FROM fabric_attribute_value
                 WHERE (`attribute_id` = 3 AND (`attribute_value_id` IN (7, 8))))

title    | code
:------- | :---
Fabric 1 | FBR1
Fabric 2 | FBR2
Fabric 3 | FBR3
Fabric 4 | FBR4

db<>fiddle here


ORA-01017 Invalid Username/Password in SQL Developer

As explained in this answer on SO, if you create a user (or any other identifier) in lowercase, you will have to always quote it when referencing, because by default Oracle (and other SQL databases, except may be Postgres) will convert unquoted identifiers to uppercase.

connect "vbk"/password

or

ALTER USER "vbk" IDENTIFIED BY xxx;


Information schema not showing reliable information?

The root cause is the storage engine. InnoDB does not update the information_schema.tables.table_rows column.

I have old posts that explain this further

Apr 23, 2012 : Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...?
May 16, 2012 : Why doesn't InnoDB store the row count?
Mar 03, 2014 : The most efficient method to obtain the total entries into a mysql table

You can get an approximation using SHOW INDEXES FROM against the table

If you would like to count all the tables in your current database regardless of the storage engine, just run this:

SET group_concat_max_len = 1024*1024*1024;
SELECT GROUP_CONCAT(sqltocount SEPARATOR ' UNION ') sqlcount INTO @sql
FROM (SELECT CONCAT('SELECT ''',table_name,''' tablename,COUNT(1) RowCount
FROM ',table_name) sqltocount FROM (SELECT table_name FROM
information_schema.tables WHERE table_schema=DATABASE()) AA) A;
SELECT @sql\G PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

GIVE IT A TRY !!!


Usage of json to store multiple attributes

This way if you need to filter/order by any part of UserDetails, you cannot use index.

So you can fetch ALL rows to your app and do it there - that means:

reading everything in sql (slow disks)
moving it to app (slow network)
manage in app (lot of memory)

Or you can "walk" all rows in sql using LIKE or something. That will still mean reading entire table but at least it will give you only somehow relevant rows where there is nontrivial chance that it will really match after "deserializing" the JSON so you skip latter two of previous three points.

Linq2Sql is just that - it normally builds queries (hopefully quite efficient ones, but you can sure break that). You probably meant Linq to objects/collections.

Those stand if you need to search "all" (means big enough subset) users by Age/Name etc. In that case having the data in separate columns (and properly indexed) is really needed for anything reasonable (if you will never have more than few hundreds of users, you can probably do it any way and not notice anything, but if you get to tens of thousands or even millions, it will really start to matter).

But - if you know that even with millions of users you will always need to search only specific userId/emailId, or small set of them (say one specific school class in a table of all students of US) and never to it without those limits, then JSON won't hurt you and table will probably be smaller without those indexes you are not going to use.

Same if you never search by those at all, but that does not seem probable (even if you do not now, you probably will in the future - if management asks you for statistics of first names per city, you have a problem with JSON).


When you use the GROUP BY clause, you can only select the fields you group on, or aggregate functions on top of ungrouped fields, like so for example:
Select name, address, dob, sum(scores)
From Emp e
JOIN Emp_Players ep ON e.empID = ep.eid
JOIN Plays p ON p.pid = ep.pid
Group by p.pid, name, address, dob

This will give you the sum of the scores for that player (pid) as you want if they only have one name, address, and dob per pid. But if they have two addresses for example, you'll see two records for that pid, one for each address, with the score summed for just that address. (I assume that's not the case here but just so you're aware. I was unsure since the schema you provided is incomplete and doesn't mention address.)


Where does the mysqldump command store the backed up databases?

By default it does it to the working directory at the time the command was made. You need to redirect the output to a file if you need to save it.

The place you put the backup is up to you. Ideally, it should be on a different machine in a different physical location. This is why you usually do a remote MySQL dump over SSH and get the data on a different machine. You just pipe the console output over ssh into a file on a different machine.


I think you cannot use the result of SHOW commands as if they were tables. You can, however use the Performance Schema Replication Tables.

To get the Slave_IO_State, you have to take into account the following remarK.

  The Slave_IO_State and Slave_SQL_Running_State fields are not preserved. If needed, these values can be obtained from the process list by using the THREAD_ID column of the appropriate replication table and joining it with the ID column in the INFORMATION_SCHEMA PROCESSLIST table to select the STATE column of the latter table.


The problem is that MongoDB does not support file versions natively. So all we can do is to store the different versions and have some sort of metadata indicating the versions, then find the according file.

Adding the complete metadata to fs.files (the wrong way)

GridFS can store custom data in the metadata field of the files collection.

> db.fs.files.find().pretty()
{
    "_id" : ObjectId("571217193380e920f9b57e8a"),
    "chunkSize" : 261120,
    "uploadDate" : ISODate("2016-04-16T10:42:34.109Z"),
    "length" : 358383,
    "md5" : "49ea7daa98666d87e5ca3208855eed0d",
    "filename" : "winstone.jar"
}
> db.fs.files.update(
    {"_id" : ObjectId("571217193380e920f9b57e8a")},
    {$set:{metadata:{Versions: [{id:321, docx:"blob"},{id:444, docx:"blob"}]}}}
  )
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.fs.files.find().pretty()
{
    "_id" : ObjectId("571217193380e920f9b57e8a"),
    "chunkSize" : 261120,
    "uploadDate" : ISODate("2016-04-16T10:42:34.109Z"),
    "length" : 358383,
    "md5" : "49ea7daa98666d87e5ca3208855eed0d",
    "filename" : "winstone.jar",
    "metadata" : {
        "Versions" : [
            {
                "id" : 321,
                "docx" : "blob"
            },
            {
                "id" : 444,
                "docx" : "blob"
            }
        ]
    }
}

Of course, this would not make much sense, since you'd have the versions attached to a single file.

Use the uploadDatefield (the easy way)

Each time a file is saved into GridFS, MongoDB automatically adds the field uploadDate, which can be used to determine specific versions:

> db.fs.files.find().pretty()
{
    "_id" : ObjectId("571217193380e920f9b57e8a"),
    "chunkSize" : 261120,
    "uploadDate" : ISODate("2016-04-16T10:42:34.109Z"),
    "length" : 358383,
    "md5" : "49ea7daa98666d87e5ca3208855eed0d",
    "filename" : "winstone.jar"
}
{
    "_id" : ObjectId("57121ac13380e9212719bc5d"),
    "chunkSize" : 261120,
    "uploadDate" : ISODate("2016-04-16T10:58:09.542Z"),
    "length" : 358383,
    "md5" : "49ea7daa98666d87e5ca3208855eed0d",
    "filename" : "winstone.jar"
}

Note that for both files, the filename is the same, as is the md5 checksum.

Now, getting the latest version is pretty easy:

> db.fs.files.find({filename:"winstone.jar"}).sort({uploadDate:-1}).limit(1).pretty()
{
    "_id" : ObjectId("57121ac13380e9212719bc5d"),
    "chunkSize" : 261120,
    "uploadDate" : ISODate("2016-04-16T10:58:09.542Z"),
    "length" : 358383,
    "md5" : "49ea7daa98666d87e5ca3208855eed0d",
    "filename" : "winstone.jar"
}

Add the version to the fs.files document (the custom way)

When storing the file, you can add the version as an arbitrary field, which, as the documentation states, is perfectly ok:

  Applications may create additional arbitrary fields.

> db.fs.files.find().pretty()
{
    "_id" : ObjectId("571217193380e920f9b57e8a"),
    "chunkSize" : 261120,
    "uploadDate" : ISODate("2016-04-16T10:42:34.109Z"),
    "length" : 358383,
    "md5" : "49ea7daa98666d87e5ca3208855eed0d",
    "filename" : "winstone.jar",
    "version" : 1
}
{
    "_id" : ObjectId("57121ac13380e9212719bc5d"),
    "chunkSize" : 261120,
    "uploadDate" : ISODate("2016-04-16T10:58:09.542Z"),
    "length" : 358383,
    "md5" : "49ea7daa98666d87e5ca3208855eed0d",
    "filename" : "winstone.jar",
    "version" : 2
}

Note the version field.

Getting the latest version of a file is as straightforward as it can be

> db.fs.files.find({filename:"winstone.jar"}).sort({version:-1}).limit(1).pretty()
{
    "_id" : ObjectId("57121ac13380e9212719bc5d"),
    "chunkSize" : 261120,
    "uploadDate" : ISODate("2016-04-16T10:58:09.542Z"),
    "length" : 358383,
    "md5" : "49ea7daa98666d87e5ca3208855eed0d",
    "filename" : "winstone.jar",
    "version" : 2
}

The general problem

But there is a serious drawback with all of these approaches: You do not save the diff in case a document is saved twice, you save the whole document again.

This leads me to the conclusion that MongoDB is not really suited for your use case, unless you can live with the fact that each version will be a full copy of the data.

Personally, I'd probably use Git Large File Storage to store the files and have a metadata collection. Without knowing a lot more about your use cases, it is hard to say if that suits you.


Advantages of POINT over DOUBLE for distance queries?

A point consist of two double precision values. You store the same information, same size on disk and in RAM. But with a slightly different set of constraints.

Two separate columns can be NULL or not independently. A point can only be NULL as a whole:

Why does the behavior of array syntax differ from '(?,?)' syntax when updating a point field and that field is NULL?

You can use geometric functions and operators for points. But you can easily construct a point from two numbers on the fly: point(x, y). In reverse, you can easily access coordinates of a point as double precision numbers: p[0], p[1] (0-based index!)

It's basically a matter of taste and style while you don't use the more sophisticated types geometry or geography provided by PostGis.