It seems you are asking if denormalization makes sense.

  Denormalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data. In some cases, denormalization helps cover up the inefficiencies inherent in relational database software. A relational normalized database imposes a heavy access load over physical storage of data even if it is well tuned for high performance.

The answer is always "it depends", so here's my rule of thumb:

If ...

the amount of data is not large
you aren't doing a ton of joins already
and/or database performance is not currently a bottleneck

then stay normalized. Yes, denormalization is faster, but it also means you have redundant data in the system -- data that has to be maintained and kept in sync. There is no longer "one source" for that data, but multiple sources that can deviate. This is risky over time, so you shouldn't do it unless you have very good reasons to do it, backed by some benchmarks.

I would only denormalize when ...

the amount of data is very large
joins are expensive and you have to do a lot of them to get even trivial queries returned
database performance is a bottleneck and/or you want to go as fast as possible

Joins are very fast on modern hardware, but they are never free.


How to get latest date from fees table and compare it to current date in mysql

Try this

select  members.name , fees.todate ,
fees.usercode
from members
INNER JOIN
(
select max(usercode) as usercode
, memberid
, max(todate) as todate
from
fees
WHERE
 fees.usercode ='KMTS472895'
 GROUP BY fees.memberid
) as fees
on members.memberid = fees.memberid
where fees.todate < curdate()
order by members.memberid


SQL Server Group By Very Slow unless using temp tables

Sounds like a bad plan to me - and possibly around statistics.

When you insert the data into a temp table, you can get statistics auto created on it.

If your statistics think there aren't many records for that combination of stock and year, it could choose a really bad plan. Try updating your statistics and seeing how it goes. Or put a better indexing strategy together, to help the join as well as the filter and grouping.

You could also post the sqlplans...


You can try and use data recovery software to recover the blocks as odds are the blocks haven't been overwritten yet.  If they have been overwritten, then the data is gone.

I'm sorry to say, but you've just learned why backups are important.


Is there any sense in using Postgres array of JSONB values instead of only JSONB?

Generally, if you have an array of one thing (statically typed), it's better to store them as jsonb[] (a native SQL array). There are far more functions that operate over a SQL arrays, and indexes. It also tends to less overhead in storage. JSONb only has,

jsonb_array_length(jsonb)
jsonb_array_elements(jsonb)
jsonb_array_elements_text(jsonb)

While Array has a far more, but most of those features are implemented as operators.

Ultimately, in most use cases it won't matter much. You're better off normalizing your data. You really shouldn't be storing multiple json documents in one row. That sounds like a big "con" to begin with.


I want to know which table the value comes from in inner join

Pattern

SELECT COALESCE(table1.value, table2.value, ...) AS value,
       CASE WHEN table1.value IS NOT NULL
            THEN 'table1'
            WHEN table2.value IS NOT NULL
            THEN 'table2'
            ...
            ELSE 'None'
       END AS source
FROM maintable
LEFT JOIN table1 ON maintable.field=table1.field
LEFT JOIN table2 ON maintable.field=table2.field
...


Is it possible to do this same partial restore, but to a different
  database?

Short answer I'm afraid, no.

Best bet is to do a partial restore to a new database then copy the tables and data to your target with:

SSIS Transfer SQL Objects Task. If this is a one off just use the import/export wizard in SSMS.
Use an INSERT SELECT script
If the tables are large, consider BULK INSERT.


Sorry to resurrect an old question

Recently I have been using the 0xDBE DataGrip Database management tool by JetBrains.

It supports multiple database engines, in the excellent Jetbrains IDE, and a key feature I have found useful is the ability to diff 2 tables (DEV and PROD).

Below is a screenshot of the diff in action (in this case there is only one column difference). The screenshot is the result of the "Merge Right" button at the top, generating the SQL required to bring the right table up to scratch.

Hope this new tool helps.


SSAS Dimensions Hierarchy causes deployment errors

I feel you're treating the symptoms and not the cause by suppressing errors.

  I set the attribute Relationships to Date Key -> Day Number Of Month -> English Month Name-> Calendar Quarter -> Calendar Year

If that data looks like:

"16062000"--> 6 --> "June" ---> "Q2" --> 2000

then Analysis Services will indeed get the hump, as for the Q2 it will have a Q2 feeding 2000 and 2001 etc.

You have to make sure the hierarchy consists of unique keys e.g.:

16062000--->062000-->Q12000--->2000

You can use different fields for the Name property of each attribute, but use the keys for the keys. Oh and if you sort by key on the month, this will prevent the classic sorting of months alphabetically.


How to interpret undo statistics in Oracle AWR report?

OK so the three things you need to look at to see if you need to increase your undo are:

The number of failures occuring - the value of STO/OOS
The value of Min/Max TR in comparison to your Undo Retention
The number of undo blocks per second * your block size * your retention guarantee, in comparison to the size of your undo tablespace

If there are any non-zero values in STO/OOS then you have a problem. The values of uS/uR/... will tell you what kind of problems are occurring. You will need to check the alert log for errors to figure out what's going wrong.

If Max TR is close to Undo Retention, let's say within 70%, then you should probably increase the retention to prevent ORA-1555 errors.

If Num Undo Blocks * Block Size is close to the size of your Undo Tbs then you should probably increase the size of the undo to avoid ORA-1652 errors.

But really you should be using the Undo Advisor to choose a good size for the tablespace: http://docs.oracle.com/cd/E11882_01/server.112/e25494/undo.htm#ADMIN11466

So in the example given, you are not having any active problems with undo size. Your undo retention is way above the length of the longest query so that's fine. Your undo tablespace should be a minimum of (UNDO_RETENTION * 12,333 * DB_BLOCK_SIZE ) / Length Of AWR Window In Seconds.

This article is ancient but it explains it well enough: http://www.orafaq.com/node/1888


As Access does not support UPSERT semantics you're stuck with something along the lines:

  insert ... into tbl
  select ... from tmp_tbl
  where not exists (select 1 from tbl where tbl.id = tmp_tbl.id)`

And similar for updates and deletes using outer joins where appropriate.

That means three SQL-statements.

See this question on SO.

Edit:

I would recommend not to delete records, but instead to mark them 'invalid', that way you can decide, what to do with tags in your other table, if necessary.


Trying to get this MySQL Trigger working

The way of declaring VARCHAR is correct
The error is that you have to specify the table, and the action. i.e. ON worker1 BEFORE {INSERT|UPDATE|DELETE}
"if inserting or updating Worker1 then" I think you need two triggers if you want the action to be performed BEFORE INERT and BEFORE UPDATE
Change the delimiter, and end your trigger with it.
If the provided code is not meant to be pseudopod, I recommend reading about MySQL triggers before start coding them.


SQL Server Failover Cluster setup shared drive issue

Found the following on another forum which confirmed my hesitation. Should have just gone with my gut.....:

  The root directory has to be on the local drive on each node. There will be
  options to specify data and log directories later on in the installation process.

This was also confirmed by Krismorte above.


ORA-12514: TNS:listener does not currently know of service requested in connect descriptor on linux

Since you have the following error message that mean either hostname is not correct and/or the domain you are using.

How to know if everything is working fine or not?
You need to execute hostname command, lets say the output is oracle_12C, and execute cat /etc/resolve.con lets say the domain name mydomain.com, then you need to edit /etc/hosts like the following

127.0.0.1 localhost
127.0.0.1 localhost.localdomain
127.0.0.1 localhost4
127.0.0.1 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
::127.0.0.1 localhost.localdomain localhost
172.31.15.86 oracle_12C.mydomin.com oracle_12C

Note if you are not using domain then /etc/hosts/ should be like the following

127.0.0.1 localhost
127.0.0.1 localhost.localdomain
127.0.0.1 localhost4
127.0.0.1 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
::127.0.0.1 localhost.localdomain localhost
172.31.15.86 oracle_12C

Now lets move to tnsnames.ora, inside this file you need to search for your database section (in your question orcl) replace it like the following (note you need to take a backup from this file before doing any changes):

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname as /etc/hosts>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
      )

Note for host if you have doming then HOST = oracle_12C.mydomain.com, else it should be HOST = oracle_12C

Now save the file and close it.

After that lets move to listener.ora,  take a backup from it and edit it like the following:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname as /etc/hosts>)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Now restart your listener by executing lsnrctl stop and lsnrctl start then try to connect to the database by using the following commands

export ORACLE_SID=orcl
sqlplus system/Forest123


move postgresql 9.4 cluster to new server

I solved it like this:

I had to restore the old config too in

/etc/postgresql/9.4/main

Then I could start the old main cluster with

pg_ctlcluster 9.4 main start

Then I had to update redmine:

cd /usr/share/redmine

and start

bundle exec rake db:migrate RAILS_ENV=production

check if redmine is working correctly in your browser, Now you can upgrade postgresql to 11. first dump the database with

sudo -u postgres pg_dumpall > /root/full_dump.sql

remove postgres 9.4 and install postgres 11, then

sudo -u postgres psql -d postgres < /root/full_dump.sql
/etc/init.d/postgresql restart

check /var/log/redmine/default/production.log for any errors, for example check in /etc/redmine/default/database.yml if redmine is still trying to connect on port 5433


Four part naming convention.

Add a linked server that will define the security context for connecting, then refer to PRDSVR.master.sys.databases and the like.


Basic SQL question about setting constraints

A primary key is an object that lives in the meta data of the database.  It will have a name no matter how it is created.  The question is will it have a meaningful name that matches your coding standards and that your team can understand at a glance or will it have a meaningless name the the system generates and everyone will continually have to look up?

The RDBMS does not care what string of characters constitute the object's name.  It's a computer; all strings are equally meaningful/less.

To exaggerate the point to an extreme, you could have all your tables called T1, T2, T3 etc and columns C1, C2, C3.  The application would work, the DBMS wouldn't care, but boy it would be hard to maintain!

You can get problems with replication for unnamed objects.  The source and target systems know the objects by different names internally and replication gets confused trying to map the source to target.


MySQL Workbench SSH to Google Compute Fails with "Authentication error opening SSH tunnel" - but Putty works

Based on mustaccio's comment, the solution was using puttygen 'Conversions'->'Import Key'... Then 'Conversions'->'Export Open SSH Key' to export the key. Open SSH keys begin with "-----BEGIN RSA PRIVATE KEY-----".


Backup Files growing quickly on SQL Server 2016 Always On Cluster

What type of backup are we talking about?

Full? This only include the log records produced while the backup command is running (basically).

Differential? Same as above, as for the log records. As for data, it include the modified extents since last full backup. I.e., it will grow and grow until next full backup.

Log? It includes the log records since last log backup.

Also, what options did you specify for the backup command? If you post the backup command you use, we have some something to go on...


Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of
  database 'xyz' cannot be made at this time. The database is in
  single-user mode, and a user is currently connected to it.

This error does not occur if YOU are the single-user in the database.  It only occurs if someone else is in it AND it is ALREADY in single-user mode.

For example, try this:

create database test
--
use test
--
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Note: there are 4 separate batches, and the 4th one doesn't error.  You're setting the mode while IN the database, and also setting it a 2nd time.  No error.

Assuming you have been given the mandate to restore the DB regardless of what is happening, do an sp_who2 to look for the user who is connected, and KILL the spid.  You can then proceed with the RESTORE.


Delete duplicate records with no change in between

Core feature is the window function lag().
Also pay special attention to avoid deadlocks and race conditions with concurrent deletes and inserts (which can affect which rows to delete!):

CREATE OR REPLACE FUNCTION remove_vendor_price_dupes(_vendor int)
  RETURNS integer AS
$func$
DECLARE
   del_ct int;
BEGIN
   -- this may or may not be necessary:
   -- lock rows to avoid race conditions with concurrent deletes
   PERFORM 1
   FROM   vendor_prices
   WHERE  vendor = _vendor
   ORDER  BY sku, effective_date, id  -- guarantee row locks in consistent order
   FOR    UPDATE;

   -- delete redundant prices
   DELETE FROM vendor_prices v
   USING (
      SELECT id
           , price = lag(price) OVER w  -- same as last row
             AND (lead(id) OVER w) IS NOT NULL AS del  -- not last row
      FROM   vendor_prices
      WHERE  vendor = _vendor
      WINDOW w AS (PARTITION BY sku ORDER BY effective_date, id)
      ) d
   WHERE v.id = d.id
   AND   d.del;

   GET DIAGNOSTICS del_ct = ROW_COUNT;  -- optional:
   RETURN del_ct;  -- return number of deleted rows
END
$func$  LANGUAGE plpgsql;

Call:

SELECT remove_vendor_price_dupes(1);

Notes

The current version of the 9.3 major release is 9.3.6. The project recommends that ...

  all users run the latest available minor release for whatever major version is in use.

A multicolumn index on (vendor, sku, effective_date, id) would be perfect for this - in this particular order. But Postgres can combine indexes rather efficiently, too.
It might pay to add the otherwise irrelevant price as last item ot the index to get index-only scans out of this. You'll have to test.
Since you have concurrent deletes it may be a good idea to run a separate delete per vendor to reduce the potential for race conditions and deadlocks. Since there are only a few vendors, this seems like a reasonable partitioning. (Many tiny calls would be comparatively slow.)
I am running a separate SELECT (PERFORM in plpgsql, since we do not use the result) because the row locking clause FOR UPDATE cannot be used together with window functions. Don't let the keyword mislead you, this is not just for updates. I am locking all rows for the given vendor, since the result depends on all rows. Concurrent reads are not impaired, only concurrent writes have to wait until we are done. That's another reason why deleting rows for one vendor at a time in a separate transaction should be best.
sku is unique per product, so we can PARTITION BY it.
ORDER BY effective_date, id: your first version of the question included code for duplicate rows, so I added id to ORDER BY as additional tie breaker. This way it works for duplicates on (sku, effective_date) as well.
To preserve the last row for each set: AND (lead(id) OVER w) IS NOT NULL. Reusing the same window for lead() is cheap - independent of the added explicit WINDOW clause - that's just syntax shorthand for convenience.
I am locking rows in the same order: ORDER BY sku, effective_date, id. Make sure that concurrent DELETEs operate in the same order to avoid deadlocks. If all other transactions delete no more than a single row within the same transaction, there cannot be deadlocks and you don't need the row locking at all.
If concurrent INSERTs could lead to a different result (make different rows obsolete), you have to lock the whole table in EXCLUSIVE mode instead to avoid race conditions:

LOCK TABLE vendor_prices IN EXCLUSIVE MODE;

Do that only if it's necessary. It blocks all concurrent write access.
I am returning the number of rows deleted, but that's totally optional. You might as well return nothing and declare the function as RETURNS void.


Precautions when designing database schema if tables are going to have billons of rows

Start understanding that the problems you face have nothing to do with the database technology you use. They are caused by physics, and physics does not care about oracle, microsoft or open source. It is the same.

Generally - depending on the queries you may want a HUGH server (albeit "billions of rows" sounds too much for a phone dictionary). Or cluster of servers. Not "I got 8gb memory". I have a sql server here with 48gb memory and guess what - that is small, not to say tiny, when talking big dada.

Also, ad hoc MAY not work. Oracle Exadata is great hardware to make ad hoc work by pure power - but even there are limits.

  There will come a situation where I need to show total count based on category
  or subcategory, or even based on state and city.

There is this concept called a data warehouse and it has a totally different schema than an oltp (transactional) database. I suggest you read up on database concepts and the difference between OLAP and OLTP, also about normalization and - the other side - star schemata (used for reporting.

Generally you may run into a real time problem here, so reglarly updated precalculated tables may be in order for this. The numbers may be off, but who cares if the number by city is 5 minutes late (or whatever update interval you use).

A lot depends on exact business requirements. On what is acceptable, how often certain queries happen, whether they can be offloaded. A data mart / warehouse may be a good idea to isolate reporting / aggregating queries from OLTP queries to lessen burden on servers. Sometimes you can not. But not everything can / should be done ad hoc, especially when mutiple users hit the same (heavy) queries.

Generally - guidance is "read some books or hire someone who knows how to work with databases". Point being - you dont even know enough to ask the right questions. And you focus on Innodb for the totally wrong reason ("good for maintaining relational database" - that is like saying "Big mac makes good food because their hamburgers have meat" - no real sense in the statement at all.


All certificates have an expiry_date, but for most uses of certificates in SQL Server, the expiry is not checked.  A certificate that protects a symmetric key used for data encryption in a database (which is what I think these are), can be used indefinitely.  IE SQL Server doesn't check the expiry_date when you run OPEN SYMMETRIC KEY DECRYPTION BY CERTIFICATE.
From the docs:

EXPIRY_DATE = 'datetime'
Is the date on which the certificate expires.
If not specified, EXPIRY_DATE is set to a date one year after
START_DATE. EXPIRY_DATE is in UTC time and can be specified in any
format that can be converted to a date and time. SQL Server Service
Broker checks the expiration date. Backup with Encryption using
certificates also checks the expiration date and will not allow a new
backup to be created with an expired certificate, but will allow
restores with an expired certificate. However, expiration is not
enforced when the certificate is used for database encryption or
Always Encrypted.

CREATE CERTIFICATE


You can use sp_dropalias do drop the legacy SQL Server 2000 user alias in SQL Server 2008 R2:

 EXEC sp_dropalias N'<name-of-aliased-login-here>';

This stored procedure was removed in SQL Server 2012 later versions.


Selecting a group from groups based on one row condition

I assume you want all rows of the group(s) where at least one row of the group has sLossCode = 'REVSTD'. In this case, you don't need GROUP BY. You can use either a self join (but that would only work under certain UNIQUE constraint requirements) or an EXISTS subquery:

SELECT s.sContractNumber, s.sLossCode, s.iServiceOdom
FROM service_table AS s
WHERE EXISTS
      ( SELECT 1
        FROM service_table AS q
        WHERE q.sLossCode = 'REVSTD'
          AND q.sContractNumber = s.sContractNumber
          AND q.iServiceOdom = s.iServiceOdom
      ) ;


You don't have a correct syntax.

UPDATE table1 set user_id = table2.user_id from table2 where table1.pid = table2.pid

You can also update data from the same table, just join the same table with a new alias

UPDATE table1 set user_id = (table2.json->>'user_id')::bigint from table1 as table2 where table1.pid = table2.pid


If I understand your question well, and you want to build a database by making use of a relational database management system (RDBMS), you should create a table for every entity that you have determined as relevant in your scenario, e.g., one table for Company, one for Person (who are Employees of a given Company), one for Project, and one for Task.

Once you have defined your database structure representing entities by means of tables, you will face the need to store a given ocurrence of an entity, then you should use the corresponding table for doing so. For instance, if you want to store three occurrences of the entity called Company, say “Database Modeling Corporation”, “Application Programming Enterprises” and “User Experience Industries”, then you should INSERT (using DML in SQL) each one of them in the table called Company.

So, no, I would not suggest you creating a new table for storing every particular ocurrence of an entity, since this approach would be very unpractical and inflexible. In this sense, it is best to use the tables that you have already defined for each respective entity.

Every stored row (an occurence of an entity) must be uniquely identified in your tables so that, among other things, you prevent redundancy, therefore you need to use a PRIMARY KEY in this regard.

You also need to set the relationships that exist between the different entities/tables that you are working with, and the concept of FOREIGN KEY is a very powerful relational tool pertaining to this aspect. For example, a specific Task may belong to a particular Project, so yo should add a FOREIGN KEY to the table called Task, and such FOREIGN KEY should reference the PRIMARY KEY that has being set for the table called Project.


You can't get more precision from an INT value than a whole number. You need to make them decimals before calculating:

CREATE TABLE [grades]
([intGradeTransposeValue]      INT,
 [txtCurrentSubjectReportName] NVARCHAR(50)
);

INSERT INTO [grades]
([intGradeTransposeValue],
 [txtCurrentSubjectReportName])
VALUES (2,'latin'), (3, 'latin');

--Returns whole number
SELECT AVG([intGradeTransposeValue])
FROM   [grades];

--Returns 2.5
SELECT AVG(CAST([intGradeTransposeValue] AS DECIMAL(5,2)))
FROM   [grades];

Ideally you'd just store the grades as DECIMAL values to begin with, but the above works in a pinch, too.


Why do SQL Server row estimates change when I add a join hint?

From reading different articles and books, I assumed that the cardinality estimations are performed before the plan is built.

Not exactly. An initial cardinality estimate is derived (after simplifications and other work), which influences the initial join order chosen by the optimizer.

However, subsequent explorations (during cost-based optimization) can, and often do, result in new cardinality estimations being calculated. These later CEs may be more or less 'accurate'. If an under-estimate results, the optimizer may choose a plan that looks cheaper, but in fact runs for much longer.

In general, there is no guarantee that cardinality estimations for semantically identical subtrees will produce the same results. It is a statistical process, after all, and some operations have deeper CE support than others.

In your case, there appears to be another factor - the optimizer introduces (or moves around) a Top, which sets a row goal on the subtree beneath it:

If you were to enable trace flag 4138 (on 2008 R2 or later), you may find the estimates more in-line with expectations, or perhaps even that the optimizer would no longer choose nested loops.

  What I see, however, is that Merge hint causes all estimations to become pretty much perfect.

There is an element of luck involved here. People tend to write queries, or at least the joins, in the order they expect them to be performed physically. Using a join hint comes with an implied FORCE ORDER, thereby fixing the join order to match the textual form, and switching off many optimizer exploration rules that can lead to cardinality re-estimation.

  Besides that, I tried running the query with OPTION (FORCE ORDER) - it runs even faster than using merge join, choosing HASH MATCH for every join.

This is the same as hinting a join, but does not restrict the choice of physical join operator. Again, if you happened to write the query join order in a logical way, it is quite likely you will get a reasonable plan. Of course, you miss out on much of the optimizer's abilities this way, which may not produce optimal results in more general situations.

You will probably not want to use FORCE ORDER very often because it is an extremely powerful hint (directive) that has wider effects than simple forcing the order of joins; for example, it prevents the optimizer moving aggregates around and introducing partial aggregations. I very much advise against using this hint except in very exceptional circumstances, and by truly expert tuners.

A detailed analysis would require more time than I have right now, and access to a statistics-only copy of the database.


mysql replication goes out of sync for some tables

You could be suffering from what is known as data drift.
QUERIES
This can happen if there are queries that are unsafe for replication.
One of the more common types is running UPDATE or DELETE using LIMIT. Using LIMIT on DML can work just fine on a Master. On a Slave, the rows selected (and perhaps certain ORDER BY choices) may not be the same set being updated or deleted as the set on the Master. See the MySQL Documentation for a Comprehensive Description of Unsafe Statements that can affect MySQL Replication.
Baron Schwartz once dealt with this and had to refactor his query to get around this
The following hypothetical scenario illustrates one way to introduce data drift:
BINLOGS
Master

20 DB Connections writing changes (INSERTs,UPDATEs,DELETEs)
The I/O thread from a Slave has to serialize the SQL coming from the 20 DB Connections
The serializing of the queries into the binary logs may be in an order different from when each DB Connection executed its change.
sync_binlog set to 0 (default), which leaves the responsibility of flushing binlogs to disk in the hands of the OS

Slave

I/O Thread reads binlog events in the order the Master wrote them
SQL Thread executes binlog events in the order the Master wrote them

Observation
If binlogs are not flushed to Disk in a timely, predictable manner, any binlog events the Slave needs could easily be bypassed. This could cause data simply not exist on the Slave. Depending on the data recorded or not recorded, Replication's SQL Thread could break because of missing data or data that should be missing.
EPILOGUE
Not every Slave can be affected this way. Masters keep a list of all Slave I/O Threads and transmits binlog events to the Slave in order by ProcessID on the Master. I can see later slaves being victimized first.
If sync_binlog is indeed an issue, perhaps all Slaves have data drift and we just don't know of it yet.
The only way to tell is to download one of the following

mk-table-checksum
mk-table-sync

or

pt-table-checksum
pt-table-sync

and checksum everything on every Slave against the Master. You may find more data drift problems than you think. Just run the sync scripts to correct them.
CAVEAT
You suggested that network latency could be at issue. With binlogs not being flushed by the OS yet, any disconnect and reconnect of MySQL Replication due to latency or dropped packets is worth looking over as well. It could also be a major contributor to data drift.
Also to be noted is what network route the new Slave is communicating with back to the Master. If it is not the same route as the older Slaves (perhaps passing through a different switch, over public IP, etc.) needs to be investigated.


upgrading Oracle forms and reports 6i to 12c

I would recommend consulting with a consultant/company that specialises in migrating Oracle Forms and Reports. Why? Because we recently performed a similar migration and after 2 months we were not finished.

Oracle has changed some things internally and features that previously worked can cease to function with the new version.

Oracle has a special site dedicated to this task:

Fusion Middleware Upgrading Oracle Forms 6i to Oracle Forms 12c

I know this is not a complete answer, but so much depends on the complexity of your current environment, that it would be very hard to give you an estimation.


So I still added the derived table
            (SELECT ProductID, QtyShip_RAW_adjusted,
            (CASE WHEN QtyShip_RAW_adjusted >= 0 and QtyShip_RAW_adjusted < (SELECT
                    MAX(QtyShip_RAW_adjusted)
                  FROM
                    QtyShip_RAW_adjusted_test) THEN QtyShip_RAW_adjusted END) MaxQty
                  FROM
                    QtyShip_RAW_adjusted_test) as Datum

As you can see I included the
                    CASE WHEN QtyShip_RAW_adjusted >= 0 and QtyShip_RAW_adjusted < MAX(QtyShip_RAW_adjusted) THEN QtyShip_RAW_adjusted END
                  )/COUNT(*)

and put the alias in the avg()
                NULLIF(
                  AVG(MaxQty)/COUNT(*),
                  0
                )

Doesn't look good but its working


What are the conditions for a functional dependency to exist?

Community Wiki answer generated from comments on the question by Renzo and ypercubeᵀᴹ

The definition of functional dependencies requires that they must hold for every possible instance.

From an instance, you cannot find the functional dependencies that hold in a certain relation schema.

You also missed A → D.


Significance of ".WORLD" on end of Oracle connection

.WORLD is a type of domain. You need it in tnsnames.ora if this is configured in your sqlnet.ora file (names.default_domain parameter). If this is not present in sqlnet.ora and you have it in tnsnames.ora, you have to append it manually. Sqlnet.ora

  The value of this parameter is appended to the net service name if the net service name doesn't already have a domain.

The .WORLD isn't needed at all if names.default_domain isn't set. In order to get the client working, just make sure the tnsnames.ora file on the server (in the network/admin folder) matches the tnsnames.ora file on the client (in the client network/admin folder).


Since it is a temp table, you are OK to just delete the files.

rm -f *#sql-ib32694.*

Is this harmful to InnoDB ? No. There was a data dictionary entry still inside ibdata1. However, that entry is either missing or invalid (due to the tablespace_id). There is actually a one in 1,099,511,627,766 chance of it causing a problem.

See my older posts on why it is not that harmful and how to deal with

Jun 04, 2014 : temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave
Dec 23, 2014 : #1025 - Error on rename of table errno: 150 : Table was deleted while tried to assign foreign key

Please keep in mind that temp tables (regardless of storage engine) are transient in nature. A temp table will disappear under these circumstances:

When a query is finished using it
When a DB connection terminates normally
When a DB connection terminates abnormally

If a temp table still exists due to a crash, rest assured that the data dictionary no longer acknowledges its existence. Please delete them and keep a good conscience.

If you are worried about, your only recourse when this happens is to perform a fully InnoDB Cleanup. See my post Howto: Clean a mysql InnoDB storage engine? in StackOverflow. The lesson here

Use CREATE TEMPORARY TABLE (...) ENGINE=MyISAM; if you have to use temp tables in your app.
Any system-generated InnoDB temp tables can be deleted after a crash.


MongoDB problems recovering a member of the replica set

You currently have a member running in secondary, because it cannot form a majority.  This is why you should always have an odd number of nodes in a replica set (one can be an arbiter) and I would recommend adding a third node as soon as possible once you get things back to normal.

In terms of how to get the second node up and running, do the following:

Shut down the remaining Secondary server (not strictly necessary, since it is read only as a Secondary, but safer)
Now, copy the whole data directory (everything in dbpath) over to the other host
Restart both nodes

One of the advantages to replica sets (over classic master/slave) is that they are intended to be functionally identical to each other, so you can simply use the data from one "good" node to seed any other "bad" node.


Handle multiple simultaneous requests Mysql

According to your screenshot, it looks like the CPU is spiking before the connections grow. Not sure if you can conclude they are exactly correlated unless this is the only time you see the spiking occur and it's always reving up exactly just a couple minutes before the connections grow (then maybe something just before the connection is registered in your counter). But even your graph shows the lowest point of CPU utilization was at your highest point of connections, so I don't think that's your issue here.
As Michael Lutz mentioned, you should look into Connection Pooling on AWS to see if helps with the Timeouts you're experiencing. But it's also possible your query is not performant when ran concurrently, at high rates of calls, or for whatever varying predicates that could potentially be used.
Without seeing your query, the EXPLAIN AND ANALYZE, and information about your indexes, there's not much more information we can provide. If you can update your post with this information, I'll update my answer accordingly, should any other potential sources of issues become evident.


Getting repetition of same values multiple times while combining two queries

Maybe You need

Select *
from
  (
    (select division.name,
            date(meter_data.reading_date) AS reading_date,
            sum(meter_data.meter_delta) 'Gas Consumption (m3)'
     from meter, meter_data, division
     where meter.meter_id = meter_data.meter_id
       and meter.division_id = division.division_id
       and Date(meter_data.reading_date) between '2018-04-01' and '2018-05-03'
     group by date(meter_data.reading_date)
    ) as table1,

    (Select Date(receipt.receipt_date) AS receipt_date,
            sum(case receipt_item.item_name
                    when 'Gas' then receipt_item.item_amount else 0
                end) 'Gas Purchase'
     from receipt_item, receipt
     where receipt.receipt_id = receipt_item.receipt_id
       and Date(receipt.receipt_date) between '2018-04-01' and '2018-05-03'
     group by Date(receipt.receipt_date)
    ) as table2
  )
WHERE table1.reading_date = table2.receipt_date;

?


Update Mysql records using Same table but Based on same table record

Assuming that all records with wishlist IS NULL must be updated with the value taken from the closest previous (while sorting by id ASC) record with non-NULL wishlist value:

UPDATE test
SET wishlist = (@tmp := COALESCE(wishlist, @tmp))
WHERE (@tmp := -1)
ORDER BY id ASC

fiddle


In sql server replication when stored procedures get replicated, it creates MSins, MSdel and MSupd procedures for all tables in the database

That's the default design for SQL Server subscribers. Those procedures get created and hidden as system stored procedures, so, they don't normally display. If you're doing queries and picking them up then be sure to exclude system objects.

You can modify it to use INSERT/UPDATE/etc which is the default for non-SQL Server subscribers by following the instructions from BOL.

  On the Properties tab of the Article Properties -  dialog
  box, in the Statement Delivery section, specify the propagation method
  for each operation using the INSERT delivery format, UPDATE delivery
  format, and DELETE delivery format menus.

... or ...

execute sp_addarticle
@ins_cmd - controls the replication of INSERT commands.
@upd_cmd - controls the replication of UPDATE commands.
@del_cmd - controls the replication of DELETE commands.

  When specifying a value of SQL for any of the above parameters, commands of that type will be replicated to the Subscriber as the appropriate Transact-SQL command.


If using a function in a case, does it run on each condition and result or is cached?

It runs every time you call it, in your case three times.
So rewrite the function that makes the case superfluous.


Confused about why a particular index is being used

Creating an index on the column involved in the clustering key may seem a little strange.  One asks why create another index, when one already exists?
The clustered index is the table.  That is to say your clustered index for this table is similar to an index like:
CREATE INDEX IX_Panel
ON dbo.Panel(SubId ASC)
INCLUDE (
    LineageId
    , Buck
    , Lot
    , GlassType
    , ETA
);

Pretty clearly, this is not the same as an non-clustered index on the clustering key, which in your case only "includes" the LineageId column:
CREATE UNIQUE NONCLUSTERED INDEX [IX-Panel-SubID-I-LineageID] ON [dbo].[Panel]
(
    [SubId] ASC
)
INCLUDE ([LineageId]);

As a test, I created a mock-up of your table, then insert over 700,000 rows into it:
INSERT INTO dbo.Panel(LineageId, Buck, Lot, GlassType, ETA)
SELECT (ROW_NUMBER() OVER (ORDER BY o1.object_id, o2.object_id) % 35)
    , SUBSTRING(o2.name, 1, 15)
    , 'lot'
    , 'GlassType'
    , o3.name + o2.name
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3;

I then ran the following query to see stats about both indexes:
SELECT o.name
    , ps.index_id
    , ps.index_type_desc
    , ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.Panel'),-1,0, NULL) ps
INNER JOIN sys.objects o ON ps.object_id = o.object_id;

The results of the query above are:

Clearly, the non-clustered index is smaller than the clustered index.  In this case about 1/5th the size.  Being a smaller index means the query optimizer will choose to use it when the index fulfills the requirements of the query, in one way or another.
For instance,
SELECT SubId
FROM dbo.Panel;

will only need to read 1,304 pages from disk by scanning the non-clustered index, instead of having to read 7,105 pages to scan the clustered-index.

On the other hand, for a query that references columns that are not included in the non-clustered index, SQL Server will likely use the clustered index since it fulfills the requirements of the query.
Take for instance, the following query, where you might intuitively think the query optimizer might pick the non-clustered index since it can use that to fulfill the WHERE clause, however the simple act of having to look up the Buck column means it is faster to simply seek the clustered index for the values in the WHERE, then return the 3 columns in the SELECT clause:
SELECT SubId
    , LineageId
    , Buck
FROM dbo.Panel
WHERE LineageId = 6
    AND SubId >= 27
    AND SubId <= 42;

This is a bit of a simplification, I would highly recommend looking at Brent Ozar's Index pages


MySQL query WHERE IN + ORDER BY, which EXPLAIN is better and how to avoid filesort?

Given the structure of multicolumn B-tree index it is not viable to do a sort on position when using IN on category_id. But as the test data suggest the position is not "global" but seems to have a meaning only for given category_id. So as I suggested in comments, it is instead possible to ORDER BY (category_id, position) - that can use two-column index on those columns to get it without filesort. http://sqlfiddle.com/#!9/aabaa/7

Because of the longtext column it is not possible to turn this into an index-only scan (text columns can be only indexed on prefix, not on entire value, as index key length is limited).

Using varchar fields as primary key has some drawbacks, but it is not "wrong" by itself so just a little suggestion - would it make sense in your desing to use some integer ids instead?


ERROR: subquery must return only one column

All elements of an array must have the same type; when constructing an array with a subquery, the simplest way to enforce this is to demand that the query returns exactly one column.

But you can make the subquery return a single column whose type is a composite type by using a row constructor:

ARRAY(SELECT ROW(name, type, description) FROM ...)


(How) does table inheritance interfere with locking in postgres?

Apparently, you want to see constraint exclusion at doing its work.  As in similar setups the parent table has no constraint (and, just to mention it, usually no rows), it will be always visited by queries on the parent table.  Using your example schema, see the output:

test=# EXPLAIN SELECT count(1) FROM p;
                            QUERY PLAN
───────────────────────────────────────────────────────────────────
 Aggregate  (cost=576.00..576.01 rows=1 width=0)
   ->  Append  (cost=0.00..501.00 rows=30001 width=0)
         ->  Seq Scan on p  (cost=0.00..0.00 rows=1 width=0)
         ->  Seq Scan on a  (cost=0.00..164.00 rows=10000 width=0)
         ->  Seq Scan on b  (cost=0.00..164.00 rows=10000 width=0)
         ->  Seq Scan on c  (cost=0.00..173.00 rows=10000 width=0)

test=# EXPLAIN SELECT count(1) FROM p WHERE type_id = 1;
                            QUERY PLAN
───────────────────────────────────────────────────────────────────
 Aggregate  (cost=412.00..412.01 rows=1 width=0)
   ->  Append  (cost=0.00..387.00 rows=10002 width=0)
         ->  Seq Scan on p  (cost=0.00..0.00 rows=1 width=0)
               Filter: (type_id = 1)
         ->  Seq Scan on a  (cost=0.00..189.00 rows=10000 width=0)
               Filter: (type_id = 1)
         ->  Seq Scan on c  (cost=0.00..198.00 rows=1 width=0)
               Filter: (type_id = 1)

In the second case, table b is not visited, because the planner knows for sure that there cannot be any rows matching the condition.  At the same time, p is present in both cases.  The same applies to c, too: it has no easy way (the check) to exclude the table from the plan.  In this case, it has to visit all tables that have the possibility to contain the actual rows.

Let's add some check there, too:

ALTER TABLE c ADD CHECK (type_id = 3);

test=# EXPLAIN SELECT count(1) FROM p WHERE type_id = 1;
                            QUERY PLAN
───────────────────────────────────────────────────────────────────
 Aggregate  (cost=214.00..214.01 rows=1 width=0)
   ->  Append  (cost=0.00..189.00 rows=10001 width=0)
         ->  Seq Scan on p  (cost=0.00..0.00 rows=1 width=0)
               Filter: (type_id = 1)
         ->  Seq Scan on a  (cost=0.00..189.00 rows=10000 width=0)
               Filter: (type_id = 1)

Much better now.

Now let's have a look on the locks that are taken.

BEGIN; -- to be able to see the locks

SELECT count(1) FROM p WHERE type_id = 1;

SELECT relname, locktype, mode
  FROM pg_locks
  JOIN pg_class c ON relation = c.oid
  JOIN pg_namespace n ON c.relnamespace = n.oid
 WHERE nspname = 'dba'
   AND relkind = 'r';

 relname │ locktype │      mode
─────────┼──────────┼─────────────────
 c       │ relation │ AccessShareLock
 b       │ relation │ AccessShareLock
 a       │ relation │ AccessShareLock
 p       │ relation │ AccessShareLock

This is not so nice...  Apparently (and somewhat surprisingly), going to the parent table locks all children, too.

But:

ROLLBACK;
BEGIN; -- to be able to see the locks

SELECT count(1) FROM a WHERE type_id = 1; -- touching only "a"

-- the locks taken:
 a       │ relation │ AccessShareLock

This means that if you can constraint your query to touch only a subset of the tables, it will save you the locks on the other children.  Playing around a bit it shows that this applies to other statements (for example, an UPDATE), too, just the lock modes are different.


Installing Temporal Tables extension (contrib extension) on windows

Since you are on Windows, you also have to copy the compiled DLLs to your PostgreSQL lib directory. Step-by-step instructions are in the manual.


How do I increase the storage available for a SQL Server instance on AWS RDS?

What is the most convenient way to increase the storage space allocated to a SQL Server instance? Is there nothing better than Database Migration Services in this situation?

Native backup and restore through S3 might be an alternative to DMS. However it also has it's cons:

Requires you to stop writing to the existing database while the backup/restore is performed in order to ensure the latest data is included.
Won't work for databases larger than 1TB in size (limit on backup size).
If you've already run out of space on RDS, the backup stored procedure might not work.

AWS explains the procedure in their Import/Export documentation for SQL Server, here are the headlines:

Spin up a new RDS instance with the desired disk capacity
Ensure both source and target databases have an option group that includes SQLSERVER_BACKUP_RESTORE
Stop writes to the source database
Use the stored procedure rds_backup_database on the source instance to backup your database(s) to an S3 bucket you have permissions to write to
Use the stored procedure rds_restore_database on the destination database to restore the backup
Point your clients to the new RDS instance


Is DBSNMP used only by Oracle EM by default?

dbsnmp is used by the monitoring agent to connect on the database and report any issues to OEM which will decide if the issues require attention and announce oracle and customer, otherwise it will just classify and display as warnings.

You can change the password anytime as long as the agent "knows" it.


6th normal form, recomposition query, efficient implementation

Might be preferable to store PERS_INFO as a single table under the
  covers, so that the tables resulting from decomposition can be
  implemented as mappings to that. But current technology doesn’t give
  clean separation of physical storage from logical design.

He's talking about the dbms storing it as a single table, not you storing it as a single table.

The relational model itself says nothing about physical storage. It only requires that "the database be perceived by the user as tables. . . . At the physical level, in fact, the system is free to store the data any way it likes . . ." (An Introduction to Database Systems, 7th ed., CJ Date, p 61)

So Darwen is saying that if the database designer declares a set of 6NF tables, along with their

candidate keys,
distributed keys,
foreign keys,
foreign distributed keys,
and so on,

then the dbms is free to

implement that set of 6NF tables as a single table in a SQL tablespace,
hide that single-table implementation from the user, and
expose to the users a set of updatable views that are indistinguishable from the 6NF tables the database designer declared.

Having said all that, you might be able to work around the lack of dbms support by creating a single table on top of the covers (so to speak), and create a set of updatable views that are indistinguishable from your 6NF design. Revoke permissions on the single table, and require all users to use only the updatable views. What you can't do is have the dbms take care of all those details  based solely on a series of logically correct, 6NF CREATE TABLE statements.


You really do not have much choice because how proportional fill works in SQL Server.

Proportional fill and how to check whether it works

I am assuming you currently have one file in primary filegroup.

You can rebuild objects with clustered index and those will go to
new files totally because of more free space in newly created
tables.  As your current file start freeing up space you might not be able to move to new files any more (depending on free space in each file).
With DBCC SHRINKFILE only way this can work is with EMPTYFILE
option because you cannot shrink smaller than your current used
space. If you try to shrink with smaller size it will not work.
Once you shrink with EMPTYFILE option current file will only hold
some system objects and rest will be split over to your 3 new files.

Next problem will be subsequent data inserts will use the old file (currently emptied) because of proportional fill algorithm.

Here is an article by Paul Randal on the same topic.


In SQL Server, shrinking a database/datafile and compression are not the same.  Shrinking a file (which, by the way, isn't really recommended) is the process of removing unused space from data files in your database.  When files are created, SQL Server "reserves" space by sizing files out (depending on how the file is created), even if it doesn't actually have data to fill that space up.  The idea is that you'll eventually put something in there (but not always).  Shrinking removes this empty space.

Data compression, on the other hand, compresses your objects to reduce the space within the data file they take up.  There are a couple different types of compression, but what they all end up doing is reducing the physical space required by your tables and indexes.

Consider you have a 1 GB data file with 600 MB worth of actual data in it.  The two operations will have the following affects:

Shrink:  You will resize the data file down to 600 MB.  This is the limit, because you still need capacity for the data.
Compress: You will resize your data from 600 MB to some amount less (depending on how good the algorithms can compress it), but your data file will remain 1 GB.

To your second question, compression is more CPU intensive(it has to uncompress the data to make use of it), but uses less disk IO (fewer bits to pull off the disk).  It might affect your query performance if you are CPU constrained.  The only way to properly evaluate is to test.


"Access is denied." for xp_cmdshell for network share

Create a proxy account to run xp_cmdshell and give the proxy access to your share.

Link


Does a non-clustered index make any guarantees about row order?

If you need order in your query results, put in an ORDER BY. It’s that simple.

Check out this article from SQL Server architect Conor Cunningham which pretty much sums this topic up:

No Seatbelt – Expecting Order without ORDER BY


MySQL - Is it good to have millions of tables in a single database?

Absolutely not.

Each table takes at least one file in the directory that represents the database.  The OS overhead is non-trivial.  The OS effort to find a single file (table) in a million-file directory is not pleasant.  Etc.

If it generally poor design to be dynamically creating tables; it is terrible design to be creating millions.

Millions, even billions, of rows in a table -- this is OK.  Having an extra column that replaces your "table" designation -- this is the "right" alternative.

If you would like to discuss your application further (and more specifically than "parent" and "column"), we can discuss other design considerations.

Same arguments apply to having millions of databases.


Anonymous PL/SQL blocks don't start with IF. The above code should be in between a begin and end; at least.

EXISTS is an SQL function, it can't be used in PL/SQL like that.

Try something like this:

set serveroutput on
declare
  c number;
begin
  select count(*) into c from my_table where rownum = 1;
  if c != 0 then
    dbms_output.put_line('has rows');
  else
    dbms_output.put_line('no rows');
  end if;
end;
/

Yes, using EXISTS in the query is also possible:

set serveroutput on
declare
  c varchar2(10);
begin
  select case when exists (select * from my_table) then 'has rows' else 'no rows' end into c from dual;
  dbms_output.put_line(c);
end;
/

Note that both the EXISTS and rownum = 1 version will stop on the first row found, and that's the point, so we don't need to read the whole table/index.


Partitioned views are a (very) old technique for partitioning data that are very rarely used today.  Oracle added the ability to partition tables back in Oracle 8, which provides much more functionality than partitioned views, at which point partitioned views became obsolete.  The only reason to consider using partitioned views would be if you can't afford a license for the partitioning option and you're willing to accept the reduced functionality and extra maintenance required to use partitioned views.  The documentation on partitioned views will pretty much all date back to the Oracle 7 days.

In order to use partitioned views, you would define individual tables for each logical partition, create a CHECK constraint on whatever column you want to partition on so that the optimizer knows which table a particular row must be in, and then create a view that does a UNION ALL on each table to create the partitioned view.  Queries against the partitioned view using the partition key can then be resolved by the optimizer to access only one of the physical tables comprising a particular logical partition.

Of course, if you use this technique, you have to create a new physical table and rebuild the partitioned view every time you add a new partition which is quite a bit of maintenance.  Getting the partitioned view to work correctly and making sure that partition pruning is happening correctly is much, much more challenging than when you're using a partitioned table.  Plus you end up with a ton of separate tables with very similar names polluting your namespace.


MongoDB refuse to start, operation not permitted

A summary of possible solutions are listed below for your convenience:

MongoDB Operation Not Permitted

  MongoDB was working perfectly until one day it generated a lock file and stopped starting with the following error:

The corresponding accepted Answer was:

  Assuming permissions are correct. It could be an selinux issue, try:

setenforce 0

  then try to start the server. If the server starts, you will have to use semanage to set the proper context to the mongo data folder

Windows mounting /data/db

  I'm trying to mount /data/db from my Windows dev PC but everytime I try, Mongo crashes as it seems to not have the right BUT :

  I have changed the security parameters in windows to allow everyone to do everything (full access)
  If I mount in a different directory, it works (the container launchs itself and I can write in my directory)

A possible explanation was:

  Found a link: VirtualBox Shared Folders are not supported by mongodb.

Apparently one user found a workaround and documented it as:

  It works using a volume which is created using docker volume create --name=myvolume
  The volume name is used instead of the local path. It's just a workaround.
  To be more precise the line I used in my case :

docker volume create --name=mongodata
docker run -d -p 27017:27017 -v mongodata:/data/db --name=mymongo mongodb:3.3

Mongodb with volume doesn't work (Operation not permitted)

The initial person writing the issue had already determined that it wouldn't work and was in search of a solution.

  Mongo docs says For example, HGFS and Virtual Box’s shared folders do not support this operation.
  https://docs.mongodb.com/manual/administration/production-notes/#fsync-on-directories

  I wonder if it is possible to make work on current docker for windows.

There was a link in this case that recommeded tuning the mongodb storage engine:

    Doesn't docker plan to implemnt something like this?

  No, not right now. You might be able to tweak the mongodb storage engine to work on an SMB share: https://forums.docker.com/t/issues-with-mongo-with-mounted-volume-for-data/15085/4

Summary

There are multiple issues surrounding MongoDB and virtual servers on Microsoft Windows, so you might want to consider running your MongoDB in a *nix environment.


How to force MySQL/MariaDB to show numbers in scientific notation?

Yes, this seems a known issue, which probably wasn't never acknowledged much because after all, in most cases, a float is displayed/processed by the application code and not represented directly by mysqld/mysql client (and seems that workbench suffers from the same problem).

Sheeri from Mozilla seems to have discovered this problem before when running pt-table-checksum and shows a workaround for its run, but not a general solution. However, on a further discussion, she seems to provide a possible option, although with the opposite format that you want (converted into decimal instead of scientific notation.

I have just arranged a quick calculation to generate the scientific format out of a decimal number, change @n for your column name in your case:

SET @n := 0.00123;

SELECT
   IF(@n=0, 0,
       CONCAT(IF(@n<0, '-', ''),
              FORMAT(abs(@n)/pow(10, floor(log10(abs(@n)))), 6),
              'E',
              FORMAT(floor(log10(abs(@n))), 0)
             )
     );

EDIT: This is the function:

CREATE FUNCTION sci(n DOUBLE)
RETURNS char(12) DETERMINISTIC
RETURN IF(n=0, '0.000000E0',
          CONCAT(IF(n<0, '-', ''),
                 FORMAT(abs(n)/pow(10, floor(log10(abs(n)))), 6),
                 'E',
                 FORMAT(floor(log10(abs(n))), 0)
                )
         )


The position requires a broad spectrum of knowledge ranging from development to system administration and even management.  Not only must a DBA know about backup, recovery, internal operations, memory and security, but also how to communicate with both developers and management.  A DBA could be giving a high level presentation to management, helping a developer tune a query, provisioning disk space for a new system and restoring data from backup all within the same hour.  These responsibilities require a wealth of knowledge with little overlap.

The consequences of failure are usually greater for a DBA than a developer.  DBAs often support dozens, even hundreds of different applications and systems most of which are vital to the success of the company.  A security breach, recovery failure, or performance problem could have far reaching and devastating ramifications.  This requires a level of knowledge and experience that can’t be gained in a short amount of time.

The better a DBA does their job the less visibility they have.  A DBA with a database that is secure, recoverable, available, and performing well will lack recognition.  DBAs get noticed when there are problems.  Not only do they get noticed when their problems are self-inflicted, they also get blamed when the database has problems due to poor coding, improper network setup, or incorrectly configured storage.

I switched from developer to DBA when I was 29.  For me the things that make being a DBA difficult also make it rewarding.  I enjoy absorbing and using a wide spectrum of knowledge, and the greater opportunity for failure makes the avoidance thereof all the more meaningful whether others see that or not.


Ignoring specified tables on replication primary

first of all, the replication variables have effect on the slave only when activated on a replicated "slave" server. you need to understand that the filtering rules on master differ from the ones on slave. on master you can choose only to log a whole db or not. on slave you have more options. here is described http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html
and here: http://dev.mysql.com/doc/refman/5.5/en/replication-rules-table-options.html
I think, you want to skip replicating a set of tables with a given pattern on slave. So, the variables must be configured on the slave. Change the configuration file on the salve and add the db_name instead of % for db part.

  replicate-wild-ignore-table=db_name.%_session line

After that, validate that the variable is activated (is shown in the output) by running:

  show slave status \G

once the filter is activated, slave server will skip all the statements which meet the given pattern. but before doing any change read how MySQL handles replication rules because you can skip important tables if you don't understand the rules.


CHECK constraints are not implemented in MySQL. From CREATE TABLE

  The CHECK clause is parsed but ignored by all storage engines. See Section 12.1.17, “CREATE TABLE Syntax”. The reason for accepting but ignoring syntax clauses is for compatibility, to make it easier to port code from other SQL servers, and to run applications that create tables with references. See Section 1.8.5, “MySQL Differences from Standard SQL”.

It's also been a reported bug for almost 8 years...


Optimizing Slow Changing Dimension (SCD2) joins

I found a somewhat satisfactory solution to this issue. I feel a bit foolish for overlooking this, but you could put it down to a case of 'premature optimization' on my behalf. I had omitted the end_date columns, as per the academic example in Wikipedia, in my SCD tables. I figured this was not particularly necessary since all you need to do to 'rewind' your table and find the correct SCD row is find the first row with a start_date less than or equal to the date you're querying against when the table is sorted. But this requires a table sort to function correctly.

The major bottleneck in my query was caused by sorting a moderately large ( 2500+ row ) table by a timestamp. I managed to resolve this issue by adding an 'end_date' column and changing the query to use ( simplified example ):

LEFT JOIN LATERAL ( SELECT * FROM tax t
  WHERE p.time_created >= t.time_created AND p.time_created <= COALESCE(t.time_ended, '9999-12-30'::date)) current_tax ON true
....

This cuts the query down from ~1sec to <100ms. I tried adding indexes against fields in the 2500+ row table, but this didn't seem to have an enormous effect on the query time, but perhaps I had made some errors there. This seems more optimized at any rate.

An updated DBFiddle can be seen here.

I would love some advice on how to better represent this, or how to better account for null values in the time_ended column.


Reboot the offending client. Simple.

ASYNC_NETWORK_IO causes the server to slow down because the client can't consume data quick enough: the network buffers fill up.

FYI: CXPACKET is expected with parallelism. Not sure of current state but IIRC excess of there can be caused by hyperthreading.

Have you rebooted the client yet...? :-)


Selecting row grouped by an "add-on" indicator

You just need to create a real grouping. Here we use a Window Function count(x) which counts all things NOT NULL. Try it with this simplified example,

SELECT count(a) OVER ()
FROM ( VALUES (true),(false) ) AS t(a);

In order to count just what we want, we use OR which evaluates the rhs if the lhs is not true. So here we test if the row IS FALSE (returning true if the value is false), if the condition does not evaluate to true (meaning the value is true or null) we return null (so count(x) skips it). here is the full query,

SELECT *,
  count((addon IS FALSE) OR NULL) OVER (ORDER BY seq) AS grp
FROM t

  id  | name | seq | addon | kind | grp
------+------+-----+-------+------+-----
 1234 | A    | 345 | f     | c    |   1
 1235 |      | 346 | t     |      |   1
 1236 |      | 347 | t     |      |   1
 2345 | B    | 348 | f     | x    |   2
 2346 |      | 349 | t     |      |   2
 3456 | C    | 350 | f     | c    |   3
 3457 |      | 351 | t     |      |   3
(7 rows)

now you can easily do anything you want..

WITH t AS (
  SELECT *,
    count((addon IS FALSE) OR NULL) OVER (ORDER BY seq) AS grp
  FROM t
)
SELECT *
FROM t AS t1
WHERE EXISTS (
  SELECT 1
  FROM t AS t2
  WHERE addon IS FALSE
    AND kind = 'x'
    AND t1.grp = t2.grp
)
ORDER BY seq;

To see this pattern elsewhere, check out the gaps-and-islands and postgresql, using this search


SQL Server — optimization techniques for rebuilding very large table

Code that relies on the order of columns is asking for trouble anyway. It should never be a problem, since as a DBA, you're profiling and identifying SELECT * statement, and ostracizing the perpetrators, correct?

When I do have to perform such an operation, due to some dumb legacy software, I would follow these steps:

rename existing table and named constraints
create table with new schema
bulk insert from old to new table. if required, break on the id and do it in batches of 100,000* records using a while loop (* - a number that suits the hardware & environment)
drop old table - reclaim space here
create indexes with proper fillfactors
create constraints (after indexes)


How to handle master changed during mongorestore?

Yes and no. You need to check from log file, why primary status was moved to other node, because it's not happened without reason.

You can always change your rs.conf() so, one node have higher priority than other, so it stays primary as long as it can.

But in this situation you still need to check from mongod.log file reason why replica sets nodes voted that move primary status was necessary.


There isn't much you'll be able to do to prevent people from getting access to your code.

If it's encrypted WITH ENCRYPTION it can be decrypted pretty quickly.
If you use CLR procedures the T/SQL statements that are executed against the SQL Server can be captured via SQL Profiler very easily.  Not to mention many shops don't like SQL CLR and wouldn't bring your app in shop.
If you encrypt the procedures via third party code it can still be decrypted pretty easily (if SQL can decrypt the procedures so that it can run them, odds are a user will be able to decrypt it).

Your code should be protected through contracts and NDAs.

Personally when I see an app with encrypted procedures about the first thing I'm going to do is force them to decrypt them.  If they won't, I'll do it myself.  Most databases out there which come from venders aren't tuned very well, and if I'm going to be tuning the database I'm going to need access to the code.


How to create stored procedure that returns query result?

As of a view I suggested in a comment: as you already have a SELECT statement, it is a matter of a few letters to convert it to a view. Here's an example:

SQL> select d.deptno, d.dname, e.empno, e.ename, e.job, e.sal
  2  from dept d join emp e on e.deptno = d.deptno;

    DEPTNO DNAME               EMPNO ENAME      JOB              SAL
---------- -------------- ---------- ---------- --------- ----------
        10 ACCOUNTING           7839 KING       PRESIDENT       5000
        10 ACCOUNTING           7782 CLARK      MANAGER         2450
        10 ACCOUNTING           7934 MILLER     CLERK           1300
        20 RESEARCH             7902 FORD       ANALYST         3000
        20 RESEARCH             7369 SMITH      CLERK            800
        20 RESEARCH             7566 JONES      MANAGER         2975
        30 SALES                7900 JAMES      CLERK            950
        30 SALES                7844 TURNER     SALESMAN        1500
        30 SALES                7654 MARTIN     SALESMAN        1250
        30 SALES                7521 WARD       SALESMAN        1250
        30 SALES                7499 ALLEN      SALESMAN        1600
        30 SALES                7698 BLAKE      MANAGER         2850

12 rows selected.

SQL>

Convert it to a view:

SQL> create or replace view v_emps as
  2  select d.deptno, d.dname, e.empno, e.ename, e.job, e.sal
  3  from dept d join emp e on e.deptno = d.deptno;

View created.

SQL>

See? Lines 2 and 3 are what you already have.

Now, when you want to select something, you'd use a view instead of the same SELECT statement over and over again. For example:

SQL> select dname, sum(sal) from v_emps group by dname;

DNAME            SUM(SAL)
-------------- ----------
ACCOUNTING           8750
RESEARCH             6775
SALES                9400

SQL> select job, count(*) from v_emps group by job;

JOB         COUNT(*)
--------- ----------
CLERK              3
SALESMAN           4
PRESIDENT          1
MANAGER            3
ANALYST            1

SQL> select ename from v_emps where deptno = 20;

ENAME
----------
SMITH
JONES
FORD

SQL>

Quite simple, isn't it?

A view is nothing but a stored query - it doesn't occupy any space, it always returns current data. True - if it is complex and deals with A LOT of data, it may be slow; but then performance tuning takes place.

I'd suggest you to try it; won't cost much, as you've seen - just a simple create or replace view v_emps ...


To find error log location, run the below command

mysql> show global variables like '%log_error%';

As you mentioned, set GLOBAL general_log = 0 - this command used to disable general log. Since it is a space issue, identify, whether it is due to error log or general log. If general log, disable it, if it's due to error log; flush and rename the error log.

Note: If you flush the logs using FLUSH LOGS or FLUSH ERROR LOGS commands, the MySQL will closes and reopens the error log, but it will keep on writing to same error log file.

In order to create a new file, rename the original file before performing the FLUSH operation, so that, when you run FLUSH LOGS or FLUSH ERROR LOGS the MySQL will close and open new file and start writing to new file.

mv /var/log/mysql/error.log /var/log/mysql/error_old.log
mysqladmin flush-logs

If the error log file size keep on increasing, the root cause should be identified and removed. Managing the size is not a solution, just a temporary workaround. Open the error log and start debugging and remove the errors, this will keep the error log size within limit.

If troubleshooting errors not in your hand, just make the FLUSHING operation as scheduled task and take backup of the error log. This can be done as below:

mv /var/log/mysql/error.log /var/log/mysql/error_old.log
mysqladmin flush-logs
mv /var/log/mysql/error_old.log /backupdir

I hope this answer will help you, Thanks!


Syntax error on declaring a variable inside a transaction

Try using a declare block before the main block:

DECLARE
  -- declarations go here
BEGIN
  -- commands go here
END;

If you need this as an anonymous block (not in any function, etc.) also read about DO.


Oracle SCN_TO_TIMESTAMP (ORA_ROWSCN) fails

SCN_TO_TIMESTAMP

  The association between an SCN and a timestamp when the SCN is
  generated is remembered by the database for a limited period of time.
  This period is the maximum of the auto-tuned undo retention period, if
  the database runs in the Automatic Undo Management mode, and the
  retention times of all flashback archives in the database, but no less
  than 120 hours. The time for the association to become obsolete
  elapses only when the database is open. An error is returned if the
  SCN specified for the argument to SCN_TO_TIMESTAMP is too old.

Meaning the databases does not keep track of SCN-timestamp map forever and it is normal for SCN_TO_TIMESTAMP to fail for "too old" values.
With the SCN returned from ORA_ROWSCN, you can still try to find the period of the change from views such as V$LOG_HISTORY (FIRST_CHANGE#, NEXT_CHANGE#, FIRST_TIME, NEXT_TIME).

When the time of the last update is required, the actual value is typically maintained and stored in an extra column in the table. ORA_ROWSCN is not good enough for that.


Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups

The @CleanupTime is always specified for a specific backup job. For example, if you create a Full backup job, a Differential backup job and a Transaction Log backup job, then the @CleanupTime always relates to the extension of the job.

Let's take a look at a Full backup example.

Full Backup

If you create a full backup job, then you will normally add one or more of the following parameters:

@Databases : Which databases get backed up (not really relevant for this example)
@Directory : The directory to store the backups
@BackupType : Full, Differential, TLog
@CleanupTime : How much hours worth of backups to keep
@FileExtensionFull : The extension of your backup.

So you have a backup job in place that will create a full backup according to the schedule you defined for the at job. Let's assume the following:

this job runs at 20:00 (8 p.m.)
@FileExtensionFull has been set to 'BAK'
@Directory has been set to 'F:\SQLBACKUP'
@CleanupTime has been set to 24 (hours)

If we look at the MaintenanceSolution.sql file then you will find the description for the parameter:

SET @CleanupTime         = NULL
-- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.

Well, that's not helping much. Same as in the official documentation on the site. Let's dig further. If you scan through the script you will eventually find a section that looks like this:

The script has been wrapped to increase readability

IF @BackupSoftware IS NULL
    BEGIN
        SET @CurrentCommandType02 = 'xp_delete_file'
        SET @CurrentCommand02 =
            'DECLARE @ReturnCode int
            EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0,
                N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + ''', -- first parameter
                ''' + @CurrentFileExtension + ''', --second parameter
                ''' + CONVERT(nvarchar(19),@CurrentCleanupDate,126) + ''' -- third parameter
        IF @ReturnCode  0 RAISERROR(''Error deleting files.'', 16, 1)'
    END

So Ola is basically using the built-in xp_delete_file function of SQL Server to delete a file at a certain time according to:

@CurrentDirectoryPath
@CurrentFileExtension
@CurrentCleanupDate

But wait what would for example, the @CurrentCleanupDate be? If we go back a bit in the script you can find a section that looks like this:

INSERT INTO @CurrentCleanupDates (CleanupDate, Mirror)
    SELECT DATEADD(hh,-(@CleanupTime),GETDATE()), 0

Ah, so the @CurrentCleanupDate is a date addition which is calculated from the @CleanupTime and the current time GETDATE(). Cool.
(...and we might have just found a typo in the code, because the sections for normal and mirror database both contain Mirror in the code.)

What is the relevant section then for @CurrentFileExtension? Let's search around a bit again. And we find:

SELECT @CurrentFileExtension = CASE
    WHEN @CurrentBackupType = 'FULL' THEN @FileExtensionFull
    WHEN @CurrentBackupType = 'DIFF' THEN @FileExtensionDiff
    WHEN @CurrentBackupType = 'LOG' THEN @FileExtensionLog
    END

So there you have it.

Summary

If the parameters for your Full backup job are set as @FileExtensionFull='BAK' and you have set a @CleanupTime=24 then the procedure will delete all Full backup files that are at least a day old (24 hours).

The @CurrentCommand02 that gets executed is basically:

xp_delete_file 0, 'F:\SQLBACKUP', 'BAK', '2018-08-20 20:00:00.045'

So it doesn't touch on any other backup files. (Unless of course you have defined 'BAK' to be the extension of all backup types, in which case you lose).


Does the existence of a college course depend on the department in which it is studied?

The only sure answer to this question is "it depends".  This is a business rule and the organization will set this business rule.

Here is how you can go about thinking about what the business rule might be and how you can determine the best way to model this relationship.

Think first about cardinality.  Is course related to only a single department?  If a course can be multidisciplinary and offered jointly by two departments then you know right away that course is a strong entity.

Second, think about functional dependencies.  What are your candidate keys for course?  Do you have a candidate key that does not make reference to department?  If so, then you have good reason to presume that course is a strong entity.  Do all of the candidate keys of course reference the department that offers it?  That may be a strong indication that course is a weak entity.

Third, think about the future.  Let's assume that you determine from the above that course seems to be a weak entity.  Is it even remotely conceivable that the factors that led you to this conclusion might change?  If so, you might want to consider promoting course to a strong entity and inventing a candidate key that does not rely on department.  This is a matter of opinion and people may have different philosophies about this.  On the one hand, some people feel that making key changes in the database is a big, painful process to be avoided if at all possible.  On the other hand, some people feel strongly that your data model should reflect your current business rules as closely as possible.


I don't think you can by using PostgreSQL internal authentication.

Check https://www.postgresql.org/message-id/4836F43E.4050607@otg-nc.com:

  I think the best you could do would be to tie authentication into some
  external system that requires a password change after a specified
  interval.  This can be accomplished with LDAP, Kerberos, or PAM.
  However, I don't think that the native mechanism will allow you to do
  what you are trying to accomplish.

This is rather old, but, AFAIK it's still valid.


A replica set can only have one primary at any particular time (one master) with the other nodes being secondaries (slaves) and so they are nothing like master-master (i.e. multi-master) replication.

You definitely should move to a replica set from master/slave configuration because that is the preferred and recommended way to replicate.  From MongoDB docs: "replica sets are a functional superset of master/slave, and newer, more robust code".

When a primary fails, one of the secondaries will be automatically elected as a new primary (you can see the docs to see the details of how that happens).

You cannot get write balancing from having a replica set - you can only distribute writes by sharding in MongoDB.


Question about postgres commits to table in UNLOGGED mode

Your understanding is correct. UNLOGGED tables participate in transactions just like regular, logged tables.
The only difference is the crash-safety, so you may lose data if your server crashes (unexpected power-outage, operating system crash, Postgres bug, ...)


How to condition on one column with different values and print-out paired values in next column in multiple columns?

Here you go...

Data & structure:

create table mytable
(
ids number,
vals varchar(5)
);

insert into mytable values(0, 'A+');
insert into mytable values(1, 'A');
insert into mytable values(1, 'D');
insert into mytable values(2, NULL);
insert into mytable values(0, 'A');
insert into mytable values(2, 'C');
insert into mytable values(1, NULL );
insert into mytable values(3, 'C');

Test:

SQL> select NVL(case when ids=0 then vals else '-' end,'*') as x0IDs,
  2         NVL(case when ids=1 then vals else '-' end,'*') as x1IDs,
  3         NVL(case when ids=2 then vals else '-' end,'*') as x2IDs,
  4         NVL(case when ids=3 then vals else '-' end,'*') as x3IDs
  5  from mytable;

X0IDS X1IDS X2IDS X3IDS
----- ----- ----- -----
A+    -     -     -
-     A     -     -
-     D     -     -
-     -     *     -
A     -     -     -
-     -     C     -
-     *     -     -
-     -     -     C

8 rows selected.

SQL>


Profiling and debugging "Incorrect syntax was encountered while parsing :setvar."

:setvar backuppath = "e:\sql\restore\PROD.bak"

should be

:setvar backuppath "e:\sql\restore\PROD.bak"


Use:

DBCC SHRINKFILE('logicalname', EMPTYFILE);
ALTER DATABASE database_name REMOVE FILE logicalname;


Lock table size issue when deleting on MySQL

Schematically (draft code!):

CREATE PROCEDURE loop_until_done()
BEGIN
DECLARE records_count_before BIGINT;
DECLARE records_count_after BIGINT;
SELECT COUNT(*) FROM myTable INTO records_count_after;
cycle: LOOP
    SET records_count_before = records_count_after;
    DELETE FROM myTable WHERE dataDate IN ('2011-07-12') LIMIT 10000;
    /* SELECT SLEEP(1) INTO records_count_after; */
    SELECT COUNT(*) FROM myTable INTO records_count_after;
    IF records_count_before <= records_count_after THEN
        LEAVE cycle;
    END IF;
END LOOP;
END

It is safe to add SELECT into some service table for some flag value which stops the operation. For example this can be a delay value (SLEEP argument) - its change may change deletion speed and server loading during it, and the value above some predefined value (or maybe zero) is a signal to leave cycle and stop the deletion operation unconditionally.

Simplified code according to danblack's recommendation:

CREATE PROCEDURE loop_until_done()
BEGIN
REPEAT
    /* SELECT SLEEP(1) INTO @dummy; */
    DELETE FROM myTable WHERE dataDate IN ('2011-07-12') LIMIT 10000;
UNTIL NOT ROW_COUNT()
END REPEAT;
END


If you have a system with a large number of tags it's probably more efficient management-wise to use a relational system between products and product-tags.

i.e.:

IF OBJECT_ID(N'dbo.ProductsTags', N'U') IS NOT NULL
DROP TABLE dbo.ProductsTags;
IF OBJECT_ID(N'dbo.Products', N'U') IS NOT NULL
DROP TABLE dbo.Products;
IF OBJECT_ID(N'dbo.Tags', N'U') IS NOT NULL
DROP TABLE dbo.Tags;
CREATE TABLE dbo.Products
(
    ProductID int NOT NULL
        CONSTRAINT PK_Products
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductName varchar(100) NOT NULL
);

CREATE TABLE dbo.Tags
(
    TagID int NOT NULL
        CONSTRAINT PK_Tags
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , TagName varchar(100) NOT NULL
);

CREATE TABLE dbo.ProductsTags
(
    ProductID int NOT NULL
        CONSTRAINT FK_ProductsTags_ProductID
        FOREIGN KEY
        REFERENCES dbo.Products(ProductID)
    , TagID int NOT NULL
        CONSTRAINT FK_ProductsTags_TagID
        FOREIGN KEY
        REFERENCES dbo.Tags(TagID)
    , CONSTRAINT PK_ProductsTags
        PRIMARY KEY CLUSTERED
        (ProductID, TagID)
);

Insert some data into the various tables:

INSERT INTO dbo.Products (ProductName)
VALUES ('wallet')
    , ('Paint');

INSERT INTO dbo.Tags (TagName)
VALUES ('IsLeather')
    , ('IsPlastic')
    , ('IsBlue');

INSERT INTO dbo.ProductsTags(ProductID, TagID)
VALUES (1, 1)
    , (2, 2)
    , (2, 3);

Query the tables:

SELECT p.ProductName
    , t.TagName
FROM dbo.ProductsTags pt
    INNER JOIN dbo.Products p ON pt.ProductID = p.ProductID
    INNER JOIN dbo.Tags t ON pt.TagID = t.TagID

╔═════════════╦═══════════╗
║ ProductName ║  TagName  ║
╠═════════════╬═══════════╣
║ wallet      ║ IsLeather ║
║ Paint       ║ IsPlastic ║
║ Paint       ║ IsBlue    ║
╚═════════════╩═══════════╝

This allows you to extend the tags at will without needing to redesign the database itself.

Using discrete bit columns in the products table is a strategy that will soon get very tedious.  Take for example:

CREATE TABLE dbo.Products
(
    ProductID int NOT NULL
        CONSTRAINT PK_Products
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductName varchar(100) NOT NULL
    , IsLeather bit NOT NULL
    , IsPlastic bit NOT NULL
    , IsBlue bit NOT NULL
);

SELECT p.ProductID
    , p.ProductName
    , p.IsLeather
    , p.IsPlastic
    , p.IsBlue
FROM dbo.Products p

INSERT INTO dbo.Products (ProductName, IsLeather, IsPlastic, IsBlue)
VALUES ('Wallet', 1, 0, 1);

Now, if you want to add products that might be red, you must alter the table via:

ALTER TABLE dbo.Products
ADD IsRed bit NOT NULL;

You must also modify any code that touches the dbo.Products table, to make that code aware of the new IsRed column.


Is UNIQUE(), INDEX() different from UNIQUE INDEX()?

UNIQUE (column1, column2) implies UNIQUE INDEX (column1, column2) because the INDEX keyword is optional. So an index is created. However, the MySQL 5.5 docs show that the INDEX (or KEY) keyword is mandatory so UNIQUE (column1, column2) should give an error

INDEX (column1, column2) does not mean UNIQUE INDEX (column1, column2): it means an index that does allow duplicate (column1, column2) pairs


When installing a service pack on one instance will this hinder the running of the other instances on a standalone SQL Server?

The Service Pack installation process will identify if there are any files in use that will necessitate a restart of the server.

Each instance has it's own independent set of executables, so instance B should not be affected by installation of a new service pack on instance A.

The SQL Server Browser Service will be restarted pretty quickly, however there may be a short period of time where name resolution for named instances may not work as expected.

Best practice for this kind of work on a production server is to schedule it to occur during a period of low or no activity.


Go to the folder of the github
repo.

Download the sys_schema folder using
gitzip or any other available
extensions. I personally used gitzip. Needless to say you can
download the whole mysql-server repo but that would be inefficient
as you do not need everything and it has a heavy download size.

Extract it to a folder and cd into it

Log in to the mysql client
mysql -u root -p

Import the sys_57.sql file. Don't mind the name, it is compatible
with mysql 8. using
source ./sys_57.sql

There you go, you have your sys schema back. Thanks to danblack for giving me a cue.

P.S akina this is
computer, there is always a way


No, deferred trigger execution will not help. It only defers triggers until the end of the transaction.

PostgreSQL doesn't have a built-in task/job scheduler, either, so you'll need to do this in your app.

In the trigger, insert a row into a table that keeps track of which entries should be deleted and when. Scan that table every few minutes and, in a single transaction, records that're past their expiry time and their corresponding entry in the delete queue table.


Using mysql's load infile to only populate certain columns

Check this example:

# cat /tmp/db.txt
id,col1,col2
1,23,"dsafsdf"
2,-1,"ghfdhg"
7,9,"strsdrt"

# mysql test
mysql> CREATE TABLE `test` (
       `id` int(11) NOT NULL,
       `mycol1` int(11) DEFAULT NULL,
       `mycol2` varchar(20) DEFAULT NULL,
       PRIMARY KEY (`id`);

mysql> LOAD DATA INFILE '/tmp/db.txt'
       INTO TABLE test FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"'
       IGNORE 1 LINES (id, mycol1, mycol2);

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+--------+---------+
| id | mycol1 | mycol2  |
+----+--------+---------+
|  1 |     23 | dsafsdf |
|  2 |     -1 | ghfdhg  |
|  7 |      9 | strsdrt |
+----+--------+---------+
3 rows in set (0.00 sec)

If you want to specify certain columns:

mysql> TRUNCATE test;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test;
Empty set (0.00 sec)

mysql> LOAD DATA INFILE '/tmp/db.txt'
       INTO TABLE test FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"'
       IGNORE 1 LINES (id, @ignore, mycol2);

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+--------+---------+
| id | mycol1 | mycol2  |
+----+--------+---------+
|  1 |   NULL | dsafsdf |
|  2 |   NULL | ghfdhg  |
|  7 |   NULL | strsdrt |
+----+--------+---------+
3 rows in set (0.00 sec)

And you can even set them with your own expressions:

mysql> TRUNCATE test;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE '/tmp/db.txt'
       INTO TABLE test FIELDS TERMINATED BY00 ','
       OPTIONALLY ENCLOSED by '"' IGNORE 1 LINES (id, @var1, mycol2)
       SET mycol1 = @var1 + 1;

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+--------+---------+
| id | mycol1 | mycol2  |
+----+--------+---------+
|  1 |     24 | dsafsdf |
|  2 |      0 | ghfdhg  |
|  7 |     10 | strsdrt |
+----+--------+---------+
3 rows in set (0.00 sec)

However, please understand that a primary key cannot be null nor duplicated, so you cannot set it to NULL unless it gets a default value, for example, because it is an auto_increment column.


Index Reorganize During Database Full Backup

When a database backup occurs, it also includes portions of the transaction log.

I'm quoting from Paul Randal's post More on how much transaction log a full backup includes. (Highlighting mine)

  Now, the reason that the transaction log is read is so that the
  restore operation can recover the database so it is transactionally
  consistent as of the point in time when the read data operation
  completed.

  So, (ignoring replication) the minimum LSN of the transaction log
  that’s included in the full backup is MIN (LSN of last checkpoint, LSN
  of oldest active transaction). This ensures that recovery can REDO log
  records to bring pages up-to-date and UNDO log records for
  transactions that had not committed.

Your index maintenance is changing data pages that your backup may have already read and included in your backup.  Once a data page is read by the backup, it is not read again.

In order for SQL Server to recover the database so it is transactionally consistent as of the point in time when the read data operation completed, the log cannot be truncated until those transaction log records have been included in the backup and the log would continue to grow and possibly run out of space.


How do I create a SQL Agent Alert on a custom error message in SQL Server 2012?

Please refer to Create a User-Defined Event

  By default, user-defined messages of severity lower than 19 are not sent to the Microsoft Windows application log when they occur. User-defined messages of severity lower than 19 therefore do not trigger SQL Server Agent alerts.

So your message should be

EXEC sp_addmessage @msgnum = 50005,
      @severity = 1,  -- Informational messages that return status information or report errors that are not severe. The Database Engine does not raise system errors with severities of 0 through 9.
      @msgtext = N'%s';
GO

When you use THROW, it defaults to severity 16 which does not fire the alert.

RAISERROR (50005, -- Message id.
       1, -- Severity,
       1, -- State,
       N'My custom message') with log;

USE [msdb]
GO

/****** Object:  Alert [Alert DBA on custom errors]    Script Date: 1/22/2016 3:17:22 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert DBA on custom errors',
        @message_id=50005,
        @severity=0,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=0,
        @database_name=N'AdventureWorks2012_test',
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

Also, make sure you use @delay_between_responses to a sensible value so that your inbox is not flooded with emails.

Also, refer to my answer for Monitoring free space in SQL Server data files using user defined message and alerts.


How to create a database that is a randomized subset of another db (mysql)?

Add another column to the table.  It could be DOUBLE or INT or BIGINT, depending on size, etc.  Populate it with some function of RAND().  Also, index the column.

Now you can fetch rows that are consecutive in this new column; they will be "random" from the point of view of the original data.  And it will be reproducible.

SELECT * FROM tbl
    WHERE myrand > $x
    ORDER BY myrand  LIMIT 200000;

In this case I am allowing $x to give you different subsets of the table -- each reproducible.  You would need to adjust $x to avoid overlapping between one subset and another (if that matters).

Note:  I did not copy the data over, merely fetched directly from the original data.  This may be an added benefit.  Or it simply gives you the way to 'copy' the rows.


Found it, using this flag:

--skip-lock-tables -f

The schema still can't be exported correctly, except by using phpmyadmin


Copying distinct rows from one table to another

If they are true EXACT duplicates, you can do:

SELECT DISTINCT *
INTO NewTable
FROM Sourcetable

If they aren't exact dupes in every single field, then we need more info.


Using data from other database to populate a new database

This should do it.

update DB2.[dbo].[Categories]
set
    DB2.[dbo].[Categories].Name = DB1.[dbo].[category].catDescription,
    DB2.[dbo].[Categories].URL = DB1.[dbo].[category].catLink
from
    DB2.[dbo].[Categories] inner join
    DB1.[dbo].[category] on DB2.[dbo].[Categories].ID = DB1.[dbo].[category].catParent


Odd execution plan behaviour from variations in WHERE...IN(List)

For the query that you have I don't see a measurable performance advantage or disadvantage to adding the extraneous derived tables. I'm testing against SQL Server 2017 but I can easily reproduce the behavior that you're seeing. I'm going to use a slightly simpler reproduction:

CREATE TABLE Test (FormId INT NOT NULL);

INSERT INTO Test
SELECT TOP (2580) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

-- query 1
SELECT  T.FormId, COUNT(*) AS TheCount
 FROM  Test T
 WHERE  EXISTS (    SELECT NULL
                      FROM (    SELECT 1
                                UNION ALL
                                SELECT 2
                            ) AS X(FormId)
                     WHERE X.FormId = T.FormId
               )
GROUP BY T.FormId;

-- query 2
SELECT  T.FormId, COUNT(*) AS TheCount
  FROM  Test T
 WHERE  EXISTS (    SELECT NULL
                      FROM (    SELECT 1 FROM (SELECT 1 x) AS X1 -- added
                                UNION ALL
                                SELECT 2
                            ) AS X(FormId)
                     WHERE X.FormId = T.FormId
               )
GROUP BY T.FormId;

With 2580 rows in the table I get different plans as you did. However, it takes just five logical reads to read all data from the table. For both queries I wasn't able to get SET STATISTICS IO, TIME ON to output anything other than:

  CPU time = 0 ms,  elapsed time = 0 ms.

The query plans may be different, but the amount of data in the table is so tiny that it doesn't matter.

Now I'm going to truncate the table and insert 10 more rows than before.

TRUNCATE TABLE Test;

INSERT INTO Test
SELECT TOP (2590) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2

OPTION (MAXDOP 1);

With that minor change I get effectively the same plans for both queries. Both queries do one scan of the data and one sort:

As far as I can tell the plan that does multiple scans is not a legal plan option for the first query. I was unable to force it with the USE XML hint, but I could have made a mistake. The second query is able to choose between both plans on a cost basis. The query rule responsible for the multiple scan approach appears to be JNonUNIA. You can see this in action with the undocumented query hint QUERYRULEOFF JNonUNIA. The Google does not give me any results when searching for this optimizer rule, but based on the description it's an abbreviation for "Join Union All -> Union All Join". In other words, take a join to a UNION ALL and switch it to a UNION ALL of joins. That's exactly what we see in the query plan with multiple scans.

I understand that you're using an EF so you don't have complete control over the code. But just because you see a certain plan today doesn't mean that you'll get that plan in all future versions of SQL Server, especially for something as obscure as what you're observing. If for some reason you need to pick one plan over the other for a query I recommend being as direct as possible. For example, in the current version of SQL Server the following query appears to always result in a single scan:

SELECT
  FormId
, COUNT(*) AS TheCount
FROM Test T
WHERE FormId IN (1, 2)
GROUP BY FormId;

And the following query seems to always result in two scans:

SELECT
  1 AS FormId
, COUNT(*) AS TheCount
FROM Test T
WHERE FormId = 1

UNION ALL

SELECT
  2 AS FormId
  , COUNT(*) AS TheCount
FROM Test T
WHERE FormId = 2
OPTION (MAXDOP 1);

Of course, that behavior isn't guaranteed either. It still feels more reliable, if that makes sense.

It is possible to think of modified queries in which using the UNION ALL approach will give a performance boost. Suppose that you have a million rows in the table and you want to put any rows with a FormId between 1 and 600000 into bucket 1 and any rows with a FormId between 600001 and 1000000 into bucket 2.

The following query takes around 200 ms to execute on my machine:

SELECT
  CASE WHEN FormId <= 600000 THEN 1 ELSE 2 END FormBucket
, COUNT(*) AS TheCount
FROM Test T
WHERE FormId >= 1 AND FormId <= 1000000
GROUP BY CASE WHEN FormId <= 600000 THEN 1 ELSE 2 END
OPTION (MAXDOP 1);

Here's the plan:

However, this query executes in around 100 ms:

SELECT
  1 AS FormBucket
, COUNT(*) AS TheCount
FROM Test T
WHERE FormId >= 1 AND FormId <= 600000

UNION ALL

SELECT
  2 AS FormId
, COUNT(*) AS TheCount
FROM Test T
WHERE FormId >= 600001 AND FormId <= 1000000
OPTION (MAXDOP 1);

Here's the plan: