Unable to join secondary replica to the availability group by SQL Server error 47106

I solved the issue. For the error 35237, the hostname that was changed should be of max 15 characters for the node specifier while creating the availability group or else it won't recognize the remaining characters


What would be better long term: Creating many documents in MongoDB or fewer documents with nested arrays

I'm torn between what's better long-term for my application: let each user have just one document and for each quiz he/she creates, it will be added to the "quizData" array of objects...OR...let each user's quiz have its own document in the MongoDB, where I create the above structure for each quiz (obviously, the "quizData" will only contain one object instead of an array of many).

Arrays which potentially grow without bound are an antipattern for performance and usability.

I would recommend having each quiz in a separate document for reasons including:

Better granularity for server memory management and document updates. When you retrieve or update a single quiz, the MongoDB server does not have to fetch/update a large document with all the quizzes for that user.
Easier to manipulate documents in your application code. Arrays of arrays are awkward to work with and have extra overhead if if you want to pull out a subset of elements via server-side manipulation. Similarly, if you wanted to provide a list of active/available quizzes in your application it would be easier to have quiz as a top-level object.
Avoid worrying about hitting the document size limit (16MB as at MongoDB 3.2). If users post many (or large) quizzes, a single document per user makes the document limit a much more likely issue to have to deal with in your application code.

For more information, see: Why shouldn't I embed large arrays in my documents?.


Does updating a partially indexed field add or remove it from an index in Postgres

(Note: I'm not a deep expert in the Pg guts. This is only my understanding. You should really read the mvcc and internals sections of the manual).

Yes, initially the row with id = 1 is still in the index, despite the index's WHERE clause excluding it because it has been updated with archived = true. At some point down the track the index entry will be purged by VACUUM or autovacuum, freeing the space.

The reason for this is PostgreSQL's MVCC design. Other concurrent transactions might still be able to see the old version of the row with id = 1, archived = false - either READ COMMITTED transactions with a long-running statement, or SERIALIZABLE transactions that don't see changes committed after they began. If the index entry were removed when the row was updated those transactions wouldn't find the row in index scans and would produce the wrong result.

Technically what's actually happening is that the old row still exists, it's just had an xmax set so newer transactions ignore it when they see it. After the update the index points to that row, same as before. A new row with archived = true has been inserted with an xmin above the old row's xmax, so any given transaction either sees the old or new version, never both. That new row is not added to the index.

Eventually there will be no open transaction that can still see the old row, so VACUUM will come by and remove the old row and its index entry, freeing the space for re-use.

Note that the index its self has no row visibility information. You can - and often do - have many versions of a row, each with index entries. When a transaction looks for matching index entries it reads all of the versions (unless it can tell from the visibility map that it doesn't need to) and ignores all but the one that has an xmin and xmax that makes it visible to that transaction.

This is one of many good reasons to ensure that autovaccum runs frequently. It reduces the number of dead index entries and dead rows you have lying around wasting space and I/O.


ORA-13506 An attempt was made to perform operation with an invalid Start/End Snapshot Pair during AWR report creation

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => --ANY Integer Number_Min Value,
    end_snap_id   => --ANY Integer Number_Max value, ,
    baseline_name => 'batch baseline');
END;
/

So here min and max value are already in DBA_HIST_BASELINE view. You can't chose random value for snapshot.

Remember value already in oracle system, you can't drop value but not overbound.


Multiple instance of Mysql realtime backup

"My current mysql backup strategy is replication."
 Replication cannot be a backup strategy but you can setup backups on slave! I guess you're talking about HA "if master fails then use slave"...

Anyways... MySQL 5.7 comes with Multi-source replication. You can have single slave machine replicating from two different masters.

You may write your own script to loop around CHANGE-MASTER-TO switching masters and replicate.

Idea for you:

while(true) {

change master to master1 from noted co-ordinates
monitor until sec_behind_master=0
   Note coordinates: relay_master_log_file, exec_master_log_pos

change master to master2 from noted co-ordinates
monitor until sec_behind_master=0
   Note coordinates: relay_master_log_file, exec_master_log_pos

}

Finally you can setup backup on this slave. (This is little complex than wht's written below)

You may also have individual slaves for both masters and setup backups on them. You can later push those backup to remote location / network drive/ tape / upload to s3 to retain longer.

master1--> slave1

master2--> slave2

(Consider backing up binary-logs if you want point in time restores.)

For backups you can use traditional mysqldump or mydumper/loader.
If your data size is large, it'd be better to go /w physical-backups, follow settingup xtrabackup for mysql with Holland framework.


How do I delete a user from the master database when the user has been deleted from the Server Logins using the SSMS Object Explorer

There's two concepts with security at play here, and in general how SQL Server works. There are Logins and Users, which the differences are discussed a little further in this StackOverflow answer. You've deleted the Logins which is server level, but the Users associated to those Logins still exist at the database level, which has its own Security node you can expand and then a Users node below it.
Whenever you map a Login to a database (to grant database permissions) it creates a User in that database.
Example:


That table, us_businessdb.tbl_company may have a broken data dictionary entry. Back in June/July of 2015, I answered 3 questions about a similar situation (where mysqld crashes accessing the table data). My condolences on this table's detachment.

If you have a previous mysqldump of that table's data, you could load it into another database. Then, transfer all the good tables into that other database.

Your current database with the corrupt table is called us_businessdb. Create a database with a similar name (us_business_db) and move the good stuff into it.

CREATE DATABASE us_business_db;
ALTER TABLE us_businessdb.jnct_tbl_user RENAME us_business_db.jnct_tbl_user;
ALTER TABLE us_businessdb.tbl_contact   RENAME us_business_db.tbl_contact;
ALTER TABLE us_businessdb.tbl_data      RENAME us_business_db.tbl_data;
ALTER TABLE us_businessdb.tbl_details   RENAME us_business_db.tbl_details;
ALTER TABLE us_businessdb.tbl_sic       RENAME us_business_db.tbl_sic;
ALTER TABLE us_businessdb.tbl_user      RENAME us_business_db.tbl_user;
ALTER TABLE us_businessdb.tbl_zipcode   RENAME us_business_db.tbl_zipcode;

OK, NOW FOR THE GORY STUFF ...

You mentioned in your question

  EDIT: This corrupt table originated from a table is full error when I was updating a single column. I had just updated about 12 million records so I'm sure it was a setting issue somewhere since my HD has plenty of space.

When you attempted to update too much data in a single UPDATE query and you ran into a table is full error, you did not run out of space on your HD. What ran out of space is the undo log inside ibdata1. I have discussed this back on Jun 16, 2014 (MySQL Index creation failing on table is full) where I personally worked with a 2TB ibdata1/ibdata2 and someone's query failed with that error even with 106GB of unused space in the ibdata files. That unused space is used for undo logs (128 of them).

I discussed this situation even further back:

Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full
Nov 25, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

I cannot definitively say that the "Table is Full" broke your table or not. I just wanted to clarify with that error meant.

OK, NOW FOR THE GORIER STUFF (is gorier a word ?)...

Here is something you will find interesting. Run the following:

USE us_businessdb
SHOW TABLES;

You might see this:

mysql> SHOW TABLES;
+-------------------------+
| Tables_in_us_businessdb |
+-------------------------+
| tbl_company             |
+-------------------------+

Did you ever wonder why mysqld did not crash when doing SHOW TABLES; ?

Doing SHOW TABLES; simply passes through the data folder looking for .frm files.

Running this query

SELECT table_name Tables_in_us_businessdb.
FROM information_schema.tables
WHERE table_schema='us_businessdb';

does the exact same thing. In both cases, it looks for tbl_company.frm.

If you attempt to access the metadata of tbl_company.ibd, by doing

SELECT data_length,index_length,create_time,update_time
FROM information_schema.tables
WHERE table_schema='us_businessdb';

or trying to access the actual data with

SELECT * FROM tbl_company LIMIT 10;

then, mysqld blows up and crashes.

OK, ENOUGH OF THE GORY STUFF. WHAT NOW ???

If you have a mysqldump of that tbl_company table, you could load it into the new database we created (us_business_db).

If you have the original CREATE TABLE tbl_company command, you can execute it in the new database. I will leave the details of reloading it s data to you.

If you do not have the original CREATE TABLE tbl_company command, you are going to need to do is get the table structure from the tbl_company.frm file. How ???

Run this query: SELECT @@global.datadir;

Let's say, for this example, that datadir is /var/lib/mysql

Go to the OS and go to the database folder with the tbl_company.frm

cd /var/lib/mysql/us_businessdb
ls -l

You should see, the tbl_company.frm and tbl_company.ibd files. Copy the .frm to another folder or another machine.

Back on Jun 11, 2013 I answered the question How can extract the table schema from just the .frm file?. In that post, I wrote about something I saw in Chuck's Blog regarding a mysqlfrm utility. I tried it out and it worked. You will have to use it to recreate the empty table in the new database.

Once you have done, you can drop the old database, create the old database back, move the tables back into the old database.

DROP DATABASE us_businessdb;
CREATE DATABASE us_businessdb;
ALTER TABLE us_business_db.jnct_tbl_user RENAME us_businessdb.jnct_tbl_user;
ALTER TABLE us_business_db.tbl_contact   RENAME us_businessdb.tbl_contact;
ALTER TABLE us_business_db.tbl_data      RENAME us_businessdb.tbl_data;
ALTER TABLE us_business_db.tbl_details   RENAME us_businessdb.tbl_details;
ALTER TABLE us_business_db.tbl_sic       RENAME us_businessdb.tbl_sic;
ALTER TABLE us_business_db.tbl_user      RENAME us_businessdb.tbl_user;
ALTER TABLE us_business_db.tbl_zipcode   RENAME us_businessdb.tbl_zipcode;
ALTER TABLE us_business_db.tbl_comnpany  RENAME us_businessdb.tbl_comnpany;

EPILOGUE

Sorry about the corrupt .ibd file. You must see to that. Yet, these steps will help clean up the data dictionary and restore the us_businessdb to some sense of normalcy.


Minimum permission to execute sp_changedbowner SP

Community wiki answer:

From ALTER AUTHORIZATION (Transact-SQL):

  Requires TAKE OWNERSHIP permission on the entity. If the new owner is not the user that is executing this statement, also requires either, 1) IMPERSONATE permission on the new owner if it is a user or login; or 2) if the new owner is a role, membership in the role, or ALTER permission on the role; or 3) if the new owner is an application role, ALTER permission on the application role.

There may be additional considerations in the AWS RDS world.

Note that sp_changedbowner is deprecated in favour of ALTER AUTHORIZATION. The sp_changedbowner documentation says:

  Requires TAKE OWNERSHIP permission on the database. If the new owner has a corresponding user in the database, requires IMPERSONATE permission on the login, otherwise requires CONTROL SERVER permission on the server.


Mongoexport halts when it reaches 101 records

Please check the disk space on target machine


After reading a lot around and receiving a hint about SQL data normalization I came with this schema. The rest will be handled by simple requests:

Table MRindex - All details about a entry will be listed here: key db | user_made_id | description | date | blob_picture | rss_feed | twitter_alias/keyword/hashtag |
Table MRtwitter - All twitter related data will be stored here: key db| index-at-table1| tw_option|tw_user | tw_date | tw_location | tw_name |tw_text
Table MRrss - All rss data will be stored here: key db | index-at-table1| rss_post_author | rss_post_date | rss_post_title | rss_post_text

Any comments & hints would be more than welcomed :-)

EDIT: To clarify a bit...

MRIndex (also referred as table1) holds basic data, filled by the user: Might be user tweets, hashtags or location based tweets. Once this entry is filled up

MRtwitter will hold the tweets and related info. The field index-at-table1 is the "link" to table MRindex.

MRrss it's the the same for blog posts. Same thing applies for 'index-at-table1'.

For MRindex:

description: short user description
date: date of creation
blob_picture: a picture if any (jpg, png, etc)
rss_feed: An RSS_feed URL belonging to this user
twitter_alias/keyword/hashtag: either @user, Greece (for location) or #hashtag


MySQL auto increment column increases after insertion error occurs

Last Sentence of MySQL Documentation on Traditional InnoDB Auto-Increment Locking says

  You may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have generated numbers using the counter.

Therefore, if the second INSERT had failed and rolled back, it makes the gap.

You may have to set innodb_autoinc_lock_mode to 0 or 2. Please read the MySQL Documentation on Configurable InnoDB Auto-Increment Locking to understand what changing innodb_autoinc_lock_mode can do for you.


Improving `GROUP BY` query performance in sqlite3

An index can be used to optimize the GROUP BY, but if the ORDER BY uses different columns, the sorting cannot use an index (because an index would help only if the database would be able to read the rows from the table in the sort order).

A COLLATE NOCASE index does not help if you use a different collation in the query.
Add a 'normal' index, or use GROUP BY seriesName COLLATE NOCASE, if that is allowed.

Using the OFFSET clause for pagination is not very efficient, because the database still has to group and sort all rows before it can begin stepping over them.
Better use a scrolling cursor.

Note: there is no guarantee that the dbId and dlState values come from any specific row; SQLite allows non-aggregated columns in an aggregated query only for bug compatibility with MySQL.


How can I determine if a table exists in the current search_path with PLPGSQL?

Quick and dirty

In Postgres 9.4+ use

SELECT to_regclass('foo');

Returns NULL if the identifier is not found in the search path.
In Postgres 9.3 or older use a cast to regclass:

SELECT 'foo'::regclass;

This raises an exception, if the object is not found!

If 'foo' is found, the oid is returned in its text representation. That's just the table name, schema-qualified according to the current search path and double-quoted where necessary.

If the object is not found you can be sure it does not exist anywhere in the search path - or not at all for a schema-qualified name (schema.foo).

If it's found there are two shortcomings:

The search includes implicit schemas of the search_path, namely pg_catalog and pg_temp. But you may want to exclude temp and system tables for your purpose. (?)
A cast to regclass works for all objects in the system catalog pg_class: indexes, views, sequences etc. Not just tables. You seem to be looking for a regular table exclusively. However, you'll probably have problems with other objects of the same name, too. Details:

How to check if a table exists in a given schema

Slow and sure

We are back to your query, but don't use current_setting('search_path'), which returns the bare setting. Use the dedicated system information function current_schemas(). Per documentation:

  current_schemas(boolean) name[]
  names of schemas in search path, optionally including implicit schemas

"$user" in the search path is resolved smartly. If no schema with the name of SESSION_USER exists, the schema is not returned to begin with. Also, depending on what you want exactly, you can additionally output implicit schemas (pg_catalog and possibly pg_temp) - but I assume you don't want those for the case at hand, so use:

DO
$do$
BEGIN
   IF EXISTS (
      SELECT  -- list can be empty
      FROM   pg_catalog.pg_class c
      JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
      WHERE  n.nspname = ANY(current_schemas(FALSE))
      AND    n.nspname NOT LIKE 'pg_%'  -- exclude system schemas!
      AND    c.relname = 'foo'
      AND    c.relkind = 'r')           -- you probably need this
   THEN
      RAISE 'This application depends on tables created by another application';
   END IF;
END
$do$;

SQL Fiddle, demonstrating all except for the last DO statement.
SQL Fiddle (JDBC) has problems with DO statements containing termination characters.


It'll sound a little stupid, but creating a separate composite non clustered index on the attributes table on product id and each column, resulting in 150+ indexes on the attributes table, will get you the ultimate speed for that query because it will support the select distinct column value by product from the attributes table. Your insert performance will be super painful.


How to apply Static Data Masking With Replication of Database

These are out of the box features of SQL Server so you'll essentially need to follow the Microsoft Books Online and other resources on setting them up. Replication has a lot of small steps and basic prerequisites but it's essentially a wizard, so it's rather simple if you just follow the documentation carefully.
Here's some helpful references for setting up Replication:

Microsoft Books Online - SQL Server Replication

Quick walkthrough on setting up Replication

More in depth walkthrough for setting up Replication

And here's some good resources on implementing Static Data Masking:

Microsoft Books Online - Static Data Masking for Azure SQL Database and SQL Server

SQL Server Static Data Masking Example

Static Data Masking in SSMS 18

Please note that Static Data Masking is only available in SQL Server 2019 (or equivalent Azure SQL Database version). So if you're on an older version of SQL Server, the alternative option available is called Dynamic Data Masking.


For a single row insert you get a narrow/per-row plan

INSERT INTO SomeTable(SomeString1, SomeString2)
SELECT TOP 1 type, type
FROM master..spt_values

If you select the Clustered Index Insert Operator and View the properties window you can see the same information as shown in the XML.

If you try for 1,000 rows

INSERT INTO SomeTable(SomeString1, SomeString2)
SELECT TOP 1000 type, type
FROM master..spt_values

You get a different wide/per-index plan with the operations split out separately

See Wide vs. Narrow Plans or Craig Freedman's blog for more information about the two


Assuming that you have a valid reason to convert the date to a string, and that you cannot change that logic: declare yourself a variable to store the intermediate results of the date arithmetic before you convert it to a string, then you can easily get the difference between the two dates:

DECLARE @eom DATE
      = DATEADD(m, DATEDIFF(m, 0, DATEADD(m, 1, DATEADD(MM, -1, @maxDwdate))), -1);
DECLARE @EndOfMonth AS VARCHAR(10)
    SET @EndOfMonth = (SELECT CONVERT(VARCHAR, @eom, 112))
 SELECT DATEDIFF(DAY, @eom, DUTEDATE) AS DAYS
   FROM t; -- replace with actual table name.


Soft delete to improve performance: all wrong?

In some cases, soft delete is not meant to be permanent. You can just defer the delete to some background job (say, right before you reorganize / rebuild indexes) so that the originating transaction doesn't have to wait for the deletes to occur (especially if you have cascading deletes, triggers, etc).

In other cases, a soft delete is not a performance requirement at all, but rather a way to avoid permanently deleting data, so there is always a trace of it. In that case, you want the soft deletes to be permanent. (But if you "delete" a lot of data at some point the table will be mostly "deleted" data. If that is the pattern and keeping the data permanently is a requirement then you might think about using an archive table to hold the deleted rows, which you can also perform as a deferred background task, still only requiring the originating transaction to update a flag.)

In both cases, you can improve runtime performance by using filtered indexes. Can't guide you on which columns you need to include, but you can say:

CREATE INDEX x ON dbo.table(col) INCLUDE(othercol)
WHERE deleted = 0;

It may sound counterintuitive, but you may want the inverse index as well, to make your delete operations faster, at least in terms of identifying the rows to delete. In that case you probably just want the primary key there:

CREATE INDEX y ON dbo.table(key) WHERE deleted = 1;

Note that there are many limitations:

How filtered indexes could be a more powerful feature
What You Can (and Can’t) Do With Filtered Indexes


How should I model a database structure to store information about words?

I believe your design for a one to many join with base_word in one table and word forms in another is fine. The reasons I make this judgement are:

The base_word, as an entity, is sufficiently different to word_forms as to represent a different entity. For instance, if you were to include the words origin language, this would also be only applicable to the base word entity, not its word forms.
Storing the data in the same table will have implications on the size of the tables due to having sparse columns with attributes that are associated with base_word OR word_form only, meaning they are NULL or empty for the others. You point this out with the is_baseword and inflection columns. Having a bunch of NULL values on a row which will never be changed is usually a reason to use the EAV model or split into different entities.
Performance considerations for queries should be taken into account. With the additional size consideration a self join to a sparsely populated table will likely not be as performant. You will probably find yourself indexing a single table for base_words OR word_forms separately. The indexes will be larger and more difficult to maintain for inserts/deletes etc. Better to have indexes on smaller tables and to have as few as possible on a table.

Don't get too concerned with perfect normalization. While a perfectly normalized system may adhere to strict information science principles, it is often better in the real world to have a slightly denormalized database for other reasons.


Why is my AWS RDS (postgres) at a high percentage of cpu with no active sessions?

I can't guarantee this will always work, but what managed to be effective for me was switching to a different instance, then switching back.

To do so, go up to Modify button in the RDS > Databases > [MY_DB} view, and then change your instance to any other instance type, wait for the update, then switch back.

I suppose I just had a bad ec2.mirco, and the method above got me a different (better) one which does not exhaust more cpu credits at idle than it gains.


Does the order I declare foreign key constraints in matter?

The order of FK Definition does not matter. When a transaction on table with FKs completes, it will release the lock on referenced tables simultaneously.


SQL query to replace attribute value with another attribute value- postgre

Easy with a CTE:

with updata as (
select user_id,attribute_value
from eavtable
where attribute_key='newEmail'
)
UPDATE eavtable
SET attribute_value = updata.attribute_value
FROM updata
WHERE eavtable.user_id = updata.user_id
and eavtable.attribute_key = 'Email';

Then a delete:

delete from eavtable where attribute_key = 'newEmail';

DB Fiddle Link.

Might also be able to do the whole thing in a single merge.


When installing SQL Server 2014 SSIS, what are the meaning of "authentication level"?

Here is a link on SQL Server 2014 for you to Grant Permissions to Integration Services Service

https://msdn.microsoft.com/en-us/library/Hh213130(v=sql.120).aspx

The first step is: Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry.

The following link for DCOM settings were last reviewed in 2006, but the options look unchanged.

https://support.microsoft.com/en-us/kb/176799

This describes Default Authentication Level (Packet Level) which is the screenshot that you posted.

Other subheadings describe several other facets of the DCOM setup.


extract state changes from snapshot-based archive table

I came up with the following query:

    with city_before as (
        select person, city
        from (
            select
                person,
                timestamp,
                first_value(timestamp) over (partition by person order by timestamp desc) as latest_timestamp,
                first_value(city) over (partition by person order by timestamp desc) as city
            from snapshot
            where timestamp < '2006-02-01'
        ) _
        where timestamp = latest_timestamp
    ),
    city_during as (
        select person, city
        from snapshot
        where timestamp between '2006-02-01' and '2006-02-15'
        group by person, city
    )
    select distinct person
    from (
        select s.person, b.city as city_before, s.city as city
        from city_during s
        left outer join city_before b on b.person = s.person
        where b.city <> s.city and s.city = 'NY'
    ) _;


single mysql query that show a particular parent and child value

By the way the same question has been already asked/answered and I would recommand you reading the article of the answer.

The article propose another solution which is less expensive to list parent/children : "The Nested Set Model" method which use these columns : id, left, right.
You will have to manage by yourself (or by your application) the left/right value.


PostgreSQL: how can queries possibly be "immutable" / functional?

I think you are getting confused with functional programming languages.

Anyway, a functional index is used to speed up access to data where queries that are accessing the data are using database functions.

By way of example, a query such as select blah from foo where sqrt(blah)=4 would have to perform the sqrt() operation for each value of blah. You could optimise this by pre-calculating all values of sqrt(blah) in a functional index: create index foo_func on foo (sqrt(blah));

This is where immutability comes in. A function is immutable if and only if the same input will always give the same output. Functional indexes can only use immutable functions. This guarantees that any queries for a given value and related functional index value will always return the same result.


Is any tool for taking MySQL Dump Automatically with a particular time?

I assume you are using Windows (because of you mention VS2010).

Simply create a "scheduled job" either through the Windows UI (e.g. start control schedtasks) or the at command. This taks should run mysqldump at the desired time.

Here are some links that explain how to create a scheduled task in Windows:

Task Scheduler: Management Services | Microsoft Docs
List, add, edit, delete Windows Scheduled Tasks from the command-line | Server Fault
http://support.microsoft.com/kb/814596
http://support.microsoft.com/kb/308569


Yes, you can specify the SQL_ID.  The usage would be something like this:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('gwp663cqh5qbf',0));

To find the SQL IDs for the current session, you'll first need to get the SID for your session, and then search v$session for the related SQL_IDs:

select sys_context('USERENV','SID') from dual;
select SQL_ID from v$session where SID='yoursid';

If the SQL aged out of the cursor cache, you may be able to find it in v$active_session_history:

select SQL_ID from v$active_session_history where SESSION_ID='yoursid';

I would recommend reading the manual page for DBMS_XPLAN to get the full details.


Should I use MongoDB or a relational database management system for this scenario?

To answer your base question, why not. Value lists are easy to make, child - parent can be made with references. You even mix those two to the single key.


Proper way to preserve data for mariadb docker container

Is this still relevant?

You haven't mentioned what your host is (I take it it is Windows). From what you mentioned, I assume also, you used mapping to host system (full path) and not. named (docker managed) volumes.
In addition it would be important to know -- if your host is indeed windows -- how you installed docker (docker desktop in hyper-v, virualbox/vmware)

I go by what I got and guess to start:

The constallation Docker on Windows and host volumes -- especially for databases -- is known to cause problems (see e.g.
https://github.com/docker-library/mariadb/issues/152 or https://stackoverflow.com/questions/41343972/docker-volumes-mariadb-windows)

If my guesses are right, then two things strike me as strange though:
1. How could this mariadb docker ever have worked and
2. where do those old database-files come from?
Is it possible these are an old artefact from a different (possibly local) installation?

You imply that possibly shutting down the mariadb container some time in the past (SIGTERM) lost the connection to the volume and on restart continued to work but that does not seem to match up with other evidence you provide (it obviously did work well up until your last (first?) restart of the container).

Could something have changed with the access-rights to the files or the folder on the host (although that would speak more for a Linux host)? See https://medium.com/@nielssj/docker-volumes-and-file-system-permissions-772c1aee23ca for further information. Again, this does not explani why  mariadb did still run up until the problem you noticed.

Closing (again assuming this is Docker on Windows) your problem might be much smaller now (speaking after Windows 10 2004 was released) using Docker in WSL 2 because you can use named (docker managed) volumes and still easily access these as linux folders in wsl 2 are directly mounted in Windows.

Sorry, this is probably not the answer you expected. If you clarify your situation I (or somebody else) might be able to be more specific.


Is there any particular reason LIMIT 0 is allowed?

Perhaps a matter of convenience

I once answered a post Table JOIN and CREATE new table extremely slow, mentioning how CREATE TABLE AS SELECT will create a table and then perform INSERT INTO SELECT to populate the table

There are ways to use LIMIT 0 to create a temp table without indexes

CREATE TABLE mytable SELECT * FROM othertable LIMIT 0;

In another post, MySQL Locks while CREATE TABLE AS SELECT, I mentioned under OPTION #2 using WHERE 1=2 as a way to create a blank temp table with no indexes

CREATE TABLE 1_temp_foo SELECT * FROM crm_companies WHERE 1=2;
ALTER TABLE 1_temp_foo ENGINE=InnoDB;

In terms of end result, using LIMIT 0 and WHERE 1=2 and even WHERE 0 are synonymous.

I agree with your assumption that it was allowed for no particular reason (because there's no real reason to not allow it, as you say), but it sure comes in handy for such occasions.


Best practices for connecting DBs that are in different geographical regions

VPNs are definitely more secure than web services.  As far as stability goes, as long as the VPN is set up correctly and the network doesn't go down in either location, VPNs are stable.


SQL Server Transaction log Percent Used in Perfmon

My suspicion is that your database, while showing it's in FULL recovery mode, is actually in pseudo-simple mode where the database still behaves like it is still in SIMPLE recovery mode until a full database backup is taken.

Paul Randal, CEO of SQLSkills.com, wrote a blog post about this recovery model called pseudo-simple. This is a behavior of the database that, while it may be configured to be in FULL recovery model, still behaves as if it was still in SIMPLE recovery model. You can easily test this by creating a database and setting it to FULL recovery model. While you can perform a lot of transactions in the database, the transaction log will get truncated, as it is with databases in SIMPLE recovery model.

Paul wrote a TSQL function that can determine if a database is truly in FULL recovery.  Here's the script from the above post.

USE [msdb];
GO

IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'SQLskillsIsReallyInFullRecovery')
    DROP FUNCTION [SQLskillsIsReallyInFullRecovery];
GO

CREATE FUNCTION [SQLskillsIsReallyInFullRecovery] (
    @DBName sysname)
RETURNS BIT
AS
BEGIN
    DECLARE @IsReallyFull BIT;
    DECLARE @LastLogBackupLSN NUMERIC (25,0);
    DECLARE @RecoveryModel TINYINT;

    SELECT
        @LastLogBackupLSN = [last_log_backup_lsn]
    FROM
        sys.database_recovery_status
    WHERE
        [database_id] = DB_ID (@DBName);

    SELECT
        @RecoveryModel = [recovery_model]
    FROM
        sys.databases
    WHERE
        [database_id] = DB_ID (@DBName);

    IF (@RecoveryModel = 1 AND @LastLogBackupLSN IS NOT NULL)
        SELECT @IsReallyFull = 1
    ELSE
        SELECT @IsReallyFull = 0;

    RETURN (@IsReallyFull);
END;
GO

And here is an example of utilizing that function (replacing 'test' with your database name).

select MSDB.DBO.SQLskillsIsReallyInFullRecovery ('test')

There is also a Powershell script written by Edwin Sarmiento that provides the same information:

Check If a SQL Server Database Is In Pseudo-Simple Recovery Model Using Windows PowerShell

My suggestion would be to take a FULL backup of the database and retry your tests.


Where to learn SQL Server Service Broker?

Some online articles:

An Introduction to SQL Server Service Broker
Service Broker Tutorials

I myself host a few articles:

Reusing Conversations
Writing Service Broker Procedures
Troubleshooting Conversations

And there are several introduction level practical examples:

Centralized Asynchronous Auditing with Service Broker
Service Broker Foundations Workbench
Using Service Broker instead of Replication
Introducing Distributed Messaging using Service Broker in SQL Server 2005
Building the MSDN Aggregation System


In MySQL, get number of pregnant women who received 3 ANC checkups

Something like (untested):

select *
from <table>
where if(visit1_date,1,0)+if(visit2_date,1,0)+if(visit3_date,1,0)+if(visit4_date,1,0) >= 3;

should do the trick.


my root account has been deleted for unknown reason, how to recover?

You were supposed to do this:

grant all privileges on *.* to 'john91'@'localhost' with grant option;

You forgot to give 'john91'@'localhost' grant option. Thus, you cannot grant privileges to anyone else.

You can fix that by doing the following

update mysql.user set grant_priv='Y' where user='john91' and host='localhost';
flush privileges;

Then, you should be able to run

grant all privileges on *.* to 'root'@'127.0.0.1';

Give it a Try !!!


MySQL - Joining table with different charset in a view

Try using CONVERT() to change the Chararcter Set first:
ON Convert(t1.f1 USING utf8mb4) COLLATE utf8mb4_unicode_ci = t2.f2


Single value from a list of comma separated values when used in inner join in SQL Server

mdbo and @ are hints it's MS Sql Server. Use a splitter table valued function of choice, Jeff Moden's one http://www.sqlservercentral.com/articles/Tally+Table/72993/ performs very good. So

select *
from Test.dbo.Test1 a
   INNER JOIN  @TestTable T on T.Name in (select item from [DelimitedSplit8K](a.Name, ','))

But may I say the best way to solve the problem is to have no such a problem. Any chance Test.dbo.Test1 could be refactored to have a separate row for every item from Name column? Then take this opportunity.

it's possible to use plain like, but note it prevents optimizer from using any index. In the above version at least index on T.Name can be used.

select *
    from Test.dbo.Test1 a
       INNER JOIN  @TestTable T on ','+a.Name+',' like '%,'+T.name+',%'


Can I run a CLR Stored Procedure on a different server than the database instance?

Can I host the DLL on a different server so it will get run there?

How would this work? If you want to run code on a remote server, you would still need code on the local server to be able to make that remote call.

Although, you could set up an instance of Express Edition to host the SQLCLR code (it would need to be a Stored Procedure(s) and/or Function(s) instead of a Trigger), then create a Linked Server to that Express instance, and then have a T-SQL Trigger on the local table execute the SQLCLR Stored Procedure(s) and/or Functions over the Linked Server. In this manner, the local code calling the remote code is pure T-SQL. You wouldn't even need to enable CLR Integration on the local instance.

  The reason I ask is that we don't want this code slowing down the database server, which it likely would by performing these other tasks within the code.

Whatever work you inside of a Trigger, be it a T-SQL or SQLCLR Trigger, will hold up the completion of that DML statement. Using SQLCLR does not really change anything there.

What are the exact requirements of the Trigger with regards to:

  do things other than database work, such as write a JSON string to a txt file and update a system log of a different type of database.

Do those steps / actions need to be bound by the transaction, such that if the INSERT operation fails, the txt file does not exist and the log entry is not there?

Finding a way to do this asynchronously would be ideal (as also mentioned by Jonathan). However, making a Web Service call removes the transactional aspect so you might get files and log entries for rows that do not actually exist.

Using an NT service would be the least impacting on the INSERT operation, and unless you are using WITH (NOLOCK) (which you shouldn't do), it will only see the results of successful INSERT operations. But, the service is disconnected and will have more latency than the Trigger model, and you will need to find a means of tracking which entries are "new" (the NT service will need to keep track of either a DATETIME or maybe could use a ROWVERSION value and compare to @@DBTS each time). If your table already has a DATETIME or DATETIME2 column being set upon INSERT, then you can leverage that existing column, else you will need to add a DATETIME / DATETIME2 / ROWVERSION column. OR, if you have a PK based upon either an IDENTITY or Sequence, then you can use that value instead of adding a column, and the NT service would just need to keep track of the most recently used ID. Where would you store that value? Text file? Registry? The "Cloud"? (just kidding about that last one ;-)

On the other hand, you could probably use Service Broker to match the two pieces together. When the Trigger fires, it can add a message to the Service Broker queue, including any necessary info from the INSERTED table. This allows it to be asynchronous. Then, on the other end, the queue reader can use a SQLCLR stored procedure as the "activation procedure" to take that info and take the appropriate actions (creating the JSON file, logging the entry on the other system, etc).

This model might not require Service Broker. You can set up your own queue table, have a T-SQL Trigger dump entries into it, and then create a SQL Server Agent job that reads from the queue table and calls the SQLCLR Stored Procedure(s) and/or Function(s) asynchronously (whether they are located on the local server or across a Linked Server), and then removes the records when it has processed them successfully.


How to set up data entry in many-to-many database in Access 2010 with forms/subforms?

The general idea is that you create a subform that's bound to your link table, and make one of the fields in the subform a combo box bound to the child record ID that's in your link table. Fill the combo box using records in the child table.

Then you handle the NotInList event on the combo box to prompt the user to add the record to the child table (or whatever is appropriate for your application).

NotInList Event

Then if you want to actually remove rows from the child table when they are no longer referenced by the link table, handle the appropriate deletion event on your subform, and check for unreferenced records.

I've implemented something like this once, but I think it was in Access 2000 on a machine I don't have with me at the moment. I'll try to check it later out of curiosity.


SQL Server Availability Group does not update DB owner automatically

Yes, you need to make sure that databases have the same owner, schema changes will travel along the availability group. This gets problematic when there are changes in database owner as you need to reapply those changes too to the secondary nodes.

If it is possible, you could change owner to SA on every database, then make this change every night and when fail-over happens. When you capture the fail-over you can run this to make SA owner for every database:

EXEC sp_MSforeachdb 'ALTER AUTHORIZATION ON DATABASE::? TO SA';

EDIT:

If you don't wish to go SA as a owner, you could create table containing the database owners

CREATE TABLE DatabaseInAvailabilityGroup.dbo.dbownersMaintenance (
id INT IDENTITY(1,1),
dbname NVARCHAR(100) NOT NULL,
username NVARCHAR(100) NOT NULL
);

Filling up the table (make this as a job and schedule it once a hour or something)

DELETE FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

INSERT INTO DatabaseInAvailabilityGroup.dbo.dbownersMaintenance
SELECT db.name, sl.name
FROM master.sys.databases db
INNER JOIN master.sys.syslogins sl ON db.owner_sid = sl.sid;

As soon as the fail-over has happened, this needs to be run on the new active node:

DECLARE @maxRow INT, @currentRow INT, @sql NVARCHAR(MAX), @dbname NVARCHAR(100), @owner NVARCHAR(85);
SELECT @maxRow = MAX(id), @currentRow = MIN(id) FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

WHILE @maxRow >= @currentRow
BEGIN
SELECT @dbname = dbname, @owner = username FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance WHERE id = @currentRow;
SET @sql = 'ALTER AUTHORIZATION ON DATABASE::'+@dbname+' TO ['+@owner+']';
EXECUTE (@sql);
SET @currentRow = @currentRow+1;
END


How to query group for increase or decrease in value

Join positions to itself.  In pseudo-sql:

select
    o.position,
    n.position
from positions as o        -- old
inner join positions as n  -- new
    on o.application_id = n.application_id
    and o.country = n.country
    and o.feed_id = n.feed_id
    and o.created = <max value for this app, country and feed that's less than n.created>

There's a discussion on this SO question for other options.


SQL Server Agent Job - Unable to open Step output file. The step succeeded

It was recommended that I review the job name for any characters that would resolve to a bad windows file path since I am using a variablized path. This ended up being due to a '/' in the SQL Server Agent Job name.


I don't think there is a OVERLAPS() function that you could use. You'll have to stick to the usual test:

WHERE a.StartTime < b.EndTime
  AND b.StartTime < a.EndTime

Change < to <=, depending on how you want your Overlap test to function on end cases, when an end time (or date) is exactly the same as the other start time. I guess such behaviour may be wanted for dates.


How to repair backup of .mdf file database?

You're getting this while trying to backup any database?

msdb is used for a bunch of things and you'll want to make sure it's okay. But critically, it sounds like your corruption is in a table which is used for backups, and this is a good thing, because you don't necessarily need that data.

Start by scripting out all your SQL Agent jobs, exporting all your SSIS packages that you've put in msdb, and anything else you can think of that uses msdb.

Now do some investigation to find out what that corrupt page is used for....

...but ultimately, your best bet is likely to be to stop the instance for a moment, pull out the msdb MDF and LDF files (so you have copies), restore from a backup from a different database (and check again for corruption), and then redeploy all your maintenance jobs, agent jobs, SSIS packages, and anything else you can think of.

You might be able to swap your MDF and LDF files onto another machine and extract extra information that you may have missed, but this should at least get you going again.


You should write a logon trigger that will log only sysadmins.
Here you can find more about logon triggers:
Logon Triggers

And here is a practical example of its use:
SQL Server – Login Auditing using Logon Triggers

P.S. To find out if the login is a sysadmin you can use this code:

select is_srvrolemember ('sysadmin',original_login())

UPDATE

If the logon trigger does not use EXECUTE AS clause (and in case of OP this is true), it's executed under the current login. So to check out if the login is sysadmin is sufficient execute

select IS_SRVROLEMEMBER('sysadmin');

without using original_login(). This will work even in case when the AD group is sysadmin, and Windows login is not sysadmin directly but access the server through membership in this AD group


How to know if Instant File Initialization is enabled?

Using ntrights.exe, check if the SeManageVolumePrivilege priviledge is granted. IF you prefer a 'built in' tool, use secedit /export /areas USER_RIGHTS.


Cannot reproduce client SQL 2008R2 error: subqueries are not allowed

Are you 100% sure the (select coalesce(max(foo_id),0) + 1 from foo) gives one row? You said it was simplified code so this may not be the case.

This should be an IDENTITY column because 2 processes can insert the same row and you don't require an aggregate over the current data.

Anyway, how to fix it. Change to this (MAX without GROUP BY always gives one row)

insert into foo values (
select
    coalesce(max(foo_id),0) + 1,
    2,
    'foo two'
from
    foo WITH (ROWLOCK, UPDLOCK, HOLDLOCK)

To test on the client, add this immediately after

SELECT @@ROWCOUNT

Note: the hints reduce (not remove) the chances of the same value being inserted.


Running a task without waiting for the results in Postgres 11 on RDS

You seem to be beating your head against the limitations of RDS.  Is what RDS offers you worth the limitations, over just renting the hardware and running your own database on it?

Triggers and NOTIFY/LISTEN are for event-driven things, not clock-driven things.  They are for solving a different problem than the one you have.

My prefered approach is to combine 'cron' and 'Your language here'.  Write the job in your language, launch it with cron.  For example if I am writing a web-app, I actually write the maintenance logic into the web-app, and then have cron use wget to hit a special URI to invoke it.  Yes, that would require having a non-RDS machine that lets you run cron and your language of choice on it (unless you give up on RDS), but if you have a web-app, you already have an app-server which could be used for that (unless that machine is also AWS-managed with no CLI access).

You could also use pgAgent in the same way.  You would need a separate machine to run the pgAgent software.  You could also run a tiny database on that machine to hold the jobs--pgAgent can read the jobs from one database but execute them against another one.  I think (but I have not tested this) that you can even put the jobs into a schema of your main database.  While you cannot use 'CREATE EXTENSION pgagent', you can run the script manually to create all of the objects.  (And if you do want to use pgAgent, you could bug AWS to whitelist it--since it does not require a superuser there is no reason not to do so other than the effort involved)


Database Size error to restore to SQL Server express 2014

The backup is 7GB? What are the sizes of the MDF files before the backup? That is what Express Edition checks - it doesn't care about the size of the backup file, it cares about how big the data files are that it has to put on the disk.

If this is for a production instance of SQL Server 2014 Express Edition, you're going to have to go back to the source and shrink those data files - it might be that they are larger than 10GB but not full, or it might be that you'll need to actually clear out some of the data.

If this is for local development, I suggest using SQL Server 2016 Developer Edition, which is free - and doesn't have any silly limits on file size.


You can use GROUP BY with the GROUPING SETS () modifier:

select name, city, sum(salary) total_salary
from Employee
group by grouping sets ((city, name), (city)) ;

Example of use from Microsoft Technet: Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS


I'm not sure I understand your motivation behind wanting to Cleanup Error Messages in SQL Server ERRORLOG.  You can certainly close the current file and create a new by either:

Restarting SQL Server

OR

Executing sp_cycle_errorlog

Keep in mind that the SQL Server error log is a file that is full of messages generated by SQL Server. By default this tells you when log backups occurred, other informational events, and even contains pieces and parts of stack dumps. In short, it’s a treasure trove of information. When SQL Server is in trouble, it’s nice to have this available as a source of information during troubleshooting.

SQL Server keeps up to 6 error log files around by default. You can easily change this. Open up your copy of SSMS and:

Expand the “Management” folder.
Right click on “SQL Server Logs”
Select “Configure”
Check the box “Limit the number of error log files before they are
recycled”
Pick some value to put in the “Maximum number of error log failes”
box
Click “OK”

Check out Forgotten Maintenance – Cycling the SQL Server Error Log for more information about configuring the SQL Server ERRORLOG


How to export an Access database and import to MySQL?

You can use ODBC to connect from MSAccess to MySQL.
Here is an example.

What you should actually do is:

Configure a new ODBC DNS connection (this might help)
Inside access - click on the table you would like to export, click on the file menu, then go to export and choose the name for the export
Select the ODBC connection you just created from the list of files of type.

MSAccess will connect to your MySQL server and export the data in that table to your MySQL database.


Is this database fragmentation or something else? What can be done?

A performance knee is pretty common when inserting into an indexed table when the indexed values are not inserted in order.  That is probably the case here, if your uuid values are generated in a way that they do not order chronologically.

Every insertion dirties a random leaf page in the index.  As long as all the leaf pages fit in shared_buffers (and your checkpoints are far enough apart), this is not much of a problem as you can "re-dirty" the same leaf pages over and over, but separate by a substantial amount of time, without much consequence. But once you have enough leaf pages that they exceed shared_buffers, that means every insertion ejects a random dirty page to the kernel, and the kernel then tries to write out random isolated pages to disk, which has horrible performance. In theory you should be able to tune the kernel to maximize the amount of write consolidation, but I've never had good luck with this.

You can put off the performance collapse by making shared_buffers be a large fraction of RAM.  But eventually (if you have enough data) that will still be exceeded.  The solutions that work beyond this point are to leave the primary key off until the table is populated and then add it afterwards, or to make sure you are inserting the rows in approximately the same order as the index order.


Comparing id s of same column and assign value of one to another

WITH cte AS (SELECT *,
                    MIN(name) OVER (PARTITION BY id
                                    ORDER BY name ASC) rn
             FROM test)
UPDATE cte
SET name=rn

fiddle

This will work on the basis of the "lower" letters (IE 'A') will overwrite "higher" letters (IE 'B'), as you have not indicated any preference for how to handle this ordering.


Is it ok to create many logins in SQL Server

SQL Server Security Doesn't Scale Well as a Login Store
While you "can" use many SQL Server logins for a web app doesn't mean you should. At the hospital where I work, we use a legacy application called Fastrack. It uses many SQL logins, one for each user, and it's a nightmare to maintain. Users leave and you don't know they've gone. There's no password table and folks forget their passwords. SQL Server security wasn't really built to be a front end for application security. Additionally, providing web users with an authentic SQL login poses security risks to your DBMS. You'll need to assign object security. There will most likely be more manual maintenance to these logins. No DBA will willing want to manage this. And, I've never seen this work well--ever.
A Simple Approach
A better approach is to build a login table or collection of tables within SQL Server for your app. Then, login through ONE application login to SQL Server to take advantage of connection pooling. Next, branch out with your own application login in the app. You'll want to obfuscate the passwords accordingly and possibly store them in an encrypted column within SQL Server. For example, create a simple login page with ASP.NET using C#. It doesn't have to be complex.
https://www.c-sharpcorner.com/article/how-to-create-login-page-in-asp-net-web-application-using-c-sharp-and-sql-server/
ASP .NET Core Identity
And Microsoft is also pushing Identity framework to build scaffolding for logins that are both internal to your app or external from places like Google and Facebook. This looks way more involved and in depth for what you "might" need at first. Especially if you're just experimenting. But you'd probably be able to find some baked code that you can use/copy to get you started.
https://docs.microsoft.com/en-us/aspnet/core/security/authentication/identity?view=aspnetcore-5.0&tabs=visual-studio#create-a-web-app-with-authentication


Add a mongodb read only replica for reporting purpose

Sharding a Mongo DB should not have any impact on your readable secondaries.  Each shard will have a replica set just as if it wasn't sharded.  The 'read only' methods to access those would be the same way.

https://docs.mongodb.com/manual/core/read-preference-mechanics/
From the link:

Read Preference and Shards

For sharded clusters that have replica set shards, mongos applies the read preference when reading from the shards. Server selection is governed by the read preference and replication.localPingThresholdMs settings.

You're probably going to look for the 'secondary preferred' option:
https://docs.mongodb.com/manual/reference/read-preference/#secondaryPreferred

"In most situations, operations read from secondary members, but in situations where the set consists of a single primary (and no other members), the read operation will use the set’s primary.
When the read preference includes a tag set, the client attempts to find a secondary member that matches the specified tag set and directs reads to a random secondary from among the nearest group. If no secondaries have matching tags, the client ignores tags and reads from the primary.
Read operations using the secondaryPreferred mode may return stale data."


No "Using locked pages for buffer pool" in SQL Server log

As I see, you're using a 32 bit system, so please follow the information from this MSDN article: Do I have to assign the Lock Pages in Memory privilege for Local System?. It's from MS's own CSS team.

Quote from it:
"If you are using 32bit systems, the messages and algorithm are slightly different. The message you should look for in the ERRORLOG is the following:

Address Windowing Extensions is enabled

If the privilege was not set correctly, you will see this message:

Could not use Address Windowing Extensions because the 'lock pages in memory' privilege was not granted.

These messages will NOT show up for 32bit systems unless the configuration value 'awe enabled' is set to 1. The second message can show up if the "Lock Pages in Memory" privilge was changed after successfully configuring AWE and then the privilige was cleared OR you used RECONFIGURE WITH OVERRIDE and the privilige was not set. "

Another detailed article regarding "Lock pages in memory" is from well known sql writer Glenn Berry: SQL Server and the “Lock pages in memory” Right in Windows Server.


What is the quickest way to restore a DB

You can set the database in SINGLE_USER mode. If you also specify ROLLBACK IMMEDIATE it won't wait for open transactions to finish but will just roll them back.

ALTER DATABASE [yourdatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

do your actions

ALTER DATABASE [yourdatabase] SET MULTI_USER


Creating a Primary Key on Existing table

Yes what you want to do is possible. It is impossible, from what you've given, to know how long it will take or how many additional resources will be required in terms of disk space.  Testing will get you those answers.

Some things to take into consideration...

You are going to need additional space for your new constraint/index.  Ensure the TransactionID is as small and unique as possible to maximize on space usage
Is your TransactionID ever growing or can it be changed for a given record?  You want the former to minimize fragmentation leading to wasted disk and performance issues
Assuming you have other indexes, you will want to script those indexes out and drop them prior to creating the PK/clustering key.  Reapply the indexes once completed; this will add additional time against your maintenance window.
You will essentially be ordering your data since you are also making your Primary Key your clustered index as well; you will need to ensure there is plenty of additional disk space to accommodate the sorting operations


How to merge two columns with incomplete information?

You can merge them in a SELECT, no problem. Something like:

SELECT CASE WHEN t1.zip1 in (NULL, 0) then t2.zip2 ELSE t1.zip1 END merged_zip
FROM table1 t1
  INNER JOIN table2 t2 on t1.customersid = t2.customersfid


PostgreSQL synchronized commit performance

Don't do this with only one slave.  If you do you are making your application more subject to failure, not less.  Basically if you do this, and you lose your slave, your master will hang.  You say your backup box is crappy, and I assume that means reliability-wise too, so this is more likely than losing your master.  This is probably not what you want.  Do not use synchronized commit option with replication and only one slave.

Synchronized commits in replication means that the master waits for the slave to acknowledge that the WAL was applied before noting that the commit happened and passing that back to the client.  If you don't get the configured quorum, you wait for it, possibly for.... a..... long..... time......

Do it async.  Use fsync set to on.  You should be golden.


Why doesn't the $1 syntax work for all queries?

Yes, you cannot use parameters with COPY.
Why? Because nobody has implemented it. Feel free to come up with a patch.
Currently only SELECT, INSERT, UPDATE and DELETE support parameters.
You can use the format function with the %L format to comfortably construct an SQL string with a string literal while avoiding the danger of SQL injection.


EXPORT specific columns to INSERT statements

Use Import/Export wizard and instead of exporting the entire table, choose sql to export specific rows and columns.

You can write sql like : select column1, column2 from Table1 where some_condition or if you don't care about specific data then you can use top command to extract any number of rows that you require.

Edit : Below are screenshots for Import export Wizard (Launch it from SSMS):

EDIT:

@Robin IN your situation, you can do 2 things -

Go for something like Redgate's sql data generator that will generate test data -- OR --
You can do a 1 time import of data as I suggested and then backup database and for your rest of testing you can just keep restoring the database from the backup (keeping in mind the data that you imported will not change). I cant think another way of doing what you want to achieve, so will keep this for others to answer ...


You have a few different questions in here:

Q: What is atomicity?

Atomicity means your transaction will completely succeed, or completely fail. At the end of your transaction, when you run COMMIT, all of the work should roll forward together in one atomic unit. If, on the other hand, you run ROLLBACK, it should all roll back together.

Q: Does atomicity mean an insert query locks a table?

No. Atomicity is only about the work involved in your own transaction, not others.

Q: If I want to stop other people from touching tables while I work, how do I do it?

You need to explicitly specify that as part of your query, like by using the SERIALIZABLE isolation level. That's generally not a good idea, though: that's the opposite of high performance. You want to write your code in a way that expects other people to be working in the database at the same time as you are.

That concept - why you should avoid exclusive/serializable locks on objects - is a pretty big one to try to tackle in a single answer, though.


The 80% is just a general rule of thumb for helping people start with a more sensible value than the default.

Buffer pool size calculation can actually be more complicated. Ideally frequently accessed data pages should remain in the buffer pool and you don't want too much eviction to happen because of its impact on performance. This is again generic but a more realistic measurement to have the buffer pool large enough to accommodate hot data + enough space for avoiding unfrequent reads triggering too much eviction.

Just because you have a database 1TB big you don't have to have 1Tb of buffer pool if you only read the same 100MB all the time.

Keep in mind also that inserts and updates also affect buffer pool usage because the operation happens there. You should try to match the amount of data you can write into innodb logs (controlled by innodb_log_file_size) and the space required for this in buffer pool. Even if you have large innodb log files but you don't have enough space for insert than eviction will cause flushing data pages to tablespace which in essence have the same effect as checkpointing.

One empirical way to correctly size buffer pool is to set as high as your datasize (or as large as possible) and run normal production load on it while decreasing the size constantly while measuring transaction rate, response time and threads_running. When you see performance starting to drop that's the size that you need at least.


what is the best choice from those two tables?

Option B is correct.

Quite simply: each row/column stores one and only one item of information

A multiple table design is quite normal: it comes out of the normalisation process which starts with option A as your business data model.


Can I use a script to restore multiple SQL databases from .bak files?

You can use

sqlmigration - Start-SqlServerMigration.ps1

This script provides the ability to migrate databases using detach/copy/attach or backup/restore. SQL Server logins, including passwords, SID and database/server roles can also be migrated. In addition, job server objects can be migrated and server configuration settings can be exported or migrated. This script works with named instances, clusters and SQL Express.

Restore Genie - it has both PowerShell and t-SQL version.

I have used both and they are life savers. Highly recommend to test them first. First one is the one that will help in your situation.


Execution plan push sort operator to avoid too many PK lookups

I think you answered your own question, SQL Server will only use an index for sorting if the columns are in the index definition keys (the sorted part) not in the included column list (as then the values are not sorted). If your users can choose one of multiple sorts you will need multiple supporting indexes, and some carefully written queries.


mysqldump: dump several tables from db. what will be locked?

Given the current way you are doing it

mysqldump -u.. -h.. -p... mydb tbl1 tbl2

each table is locked during the SELECT phase in a specific way

Note the effects during the dump

Each table that is MyISAM should allow SELECTs but block INSERTs, UPDATEs and DELETEs
While dumping tbl1, tbl2 is fully accessible
While dumping tbl2, tbl1 is fully accessible

If those tables (tbl1 and tbl2) are inactive, then such a dump would be harmless to busy tables.


What is the Wait Type "QDS_Shutdown_Queue" referring to?

It's Query Data Store related and you can safely ignore it.
Query store is a new feature in SQL 2016.
Refer to http://www.sqlskills.com/blogs/bobb/more-xevents-in-sql-server-2014-ctp1-dont-wait-any-longer/
Edit:
Below confirms that I was correct - You can safely ignore/filter out this wait type.
Paul Randal has commented in his Wait statistics, or please tell me where it hurts

Dave Mc: Any thoughts on QDS_SHUTDOWN_QUEUE? It’s currently the top reported wait from one of our SQL 2014 prod instances.
Paul Randal : It’s for the Query Store in 2016 – you can filter it out.


MySQL dies when trying to access a specific database

I frequently use innodb force recovery in this case, and it often resolves the issue. On Mac and Windows installs, this is done by editing my.ini

Search for innodb_force in the text, uncomment that line, set the value to 2. Restart the MySQL service.
Undo your edits ( comment out that line in my.ini)
Restart the service and test. You can safely do level 3 in the same manner, but research more about it before attempting level 4 or higher.

Hope this helps.


Can I attach a SQL 2008 R2 database to SQL 2008 that is not R2?

No.

You can't do that from a higher to a lower version. R2 is considered as a new version of sql server. You can go from lower version to higher version with detach/attach.


Sum of all Memory Clerks and Total Server Memory (KB)

Max Server Memory for SQL Server 2019 = Buffer Pool Memory + Non-Buffer Pool Memory.

No, some part of memory allocation for SQL Server 2019 can be done outside of max server memory like ( I would quote from Memory Management Architecture Guide)
Memory for thread stacks1, CLR2, extended procedure .dll files, the OLE DB providers referenced by distributed queries, automation objects referenced in Transact-SQL statements, and any memory allocated by a non SQL Server DLL are not controlled by max server memory.
So you can see some part of memory is allocated outside max server memory so your equation does not always holds true.

Total Server Memory (KB) - The committed memory from the Buffer Pool.

Yes that is the memory committed using memory manager.

Data Cache Memory (KB) performance counter is equivalent to MEMORYCLERK_SQLBUFFERPOOL memory clerk and both represents data cache used size.

No, buffer pool is much bigger, Data cache is subset of it.

If Total Server Memeory (KB) is related to Buffer Pool memory, then why its size (32262 MB) is bigger than Total sum memory clerks altogether (23523 MB) which represents both Buffer Poll Memory and Non-Buffer Pool Memory?

Total server memory includes basically memory used by SQL Server which is almost 31 GB and this will be bigger than sum of the clerks, which is basically 22 GB, because memory clerks do not capture all memory allocations within SQL Server ( They do most of it however).
Let me know if you have further questions.


Because you backed up only the read write files of the database with the command READ_WRITE_FILEGROUPS, you will not be able to restore A or B from the backup. Ideally, you should go perform a full backup of the database and restore all of the files you want from it. This is the recommended method for many reasons

I was thinking of when or why you wouldn't do this. I'm thinking the last ditch effort you would copy and paste ndf files A or B to the location of SQL Server to try and attach with the restore (which may be out of sync and may not work anyway) is if your only backup was the one you posted about. (Only read write databases, all read only lost.) But what would be that last ditch effort?

Corrupted database? SANS blew up? Server completely offline? In all of those circumstances you're not going to be able to grab A and B anyway. Perhaps there's an automated process that copies and clones the SANS to a backup server (but why not the backup files then?) and that piece is operational while the database itself is down, then maybe I can see where you can try to grab the NDF files and attempt a reattachment, but I feel time would be better spent getting the server running and restoring those copies of the files back over.

But here is what I'd suggest as a last ditch effort if you are persistent and intent on doing this for whatever reasons. I will have a few articles at the end related to this.

If you are able to copy and paste files A and B into the folder where SQL can access them, you can attempt to do an attachment on the DB you restored. I suspect that you will need to go to the primary server to find files A and B or possibly find another source. It may be that the files are out of sync and cannot be attached. If there happened to be no activity since the backup, you may have a chance.

Here are some examples of attaching the files:

Stack Overflow on how to attach files.

Stack Overflow on users having problems attaching MDF files.

Aaron Bertrand of Sentry One suggesting to just take a full backup and restore. Bad habits of MDF files.


Update of db yields "Error Code: 1215. Cannot add foreign key constraint"

Discovered the source of error.  There is a third entity Contact that had a foreign key to both Center and County entities. By removing the county property and thus the foreign key from the Contact entity the error is removed.  The trick now, though, will be to get this schema change into production.


How to safely replace the old table with its cleaned copy?

By default, the error message tells you more. Like:

ERROR: cannot drop table tbl because other objects depend on it
SQL state: 2BP01
Detail: view v_tbl depends on table tbl
Hint: Use DROP ... CASCADE to drop the dependent objects too.

General solution
Include the object mentioned in the Detail line in your script.
Also, to do it safely, wrap all of it in a transaction, and write-lock the old table before working on the new to avoid losing work done during the transition:
BEGIN;

LOCK TABLE tbl IN SHARE MODE;

-- prepare tbl_cleaned

DROP VIEW v_tbl;  -- or any other depending object
-- more?

DROP TABLE tbl;
ALTER TABLE tbl_cleaned rename to tbl;

CREATE VIEW v_tbl AS SELECT  ... -- actual definition, see below
-- more?

COMMIT;

There may be more depending objects. Same procedure.
In the example, to get the view definition:
SELECT 'CREATE VIEW v_foo1 AS '
    || pg_get_viewdef('v_foo1'::regclass) AS view_definition;

See:

How to list the columns of a view in Postgres?

And you may want to rename constraints and indices to fit the renamed table - or hold back on those and create with correct names after renaming the new table. (Again, be sure to get the definition before dropping the old table!)
Related:

Best way to populate a new column in a large table?
How do I replace a table in Postgres?

If there are many depending objects and the table is not too big, a good alternative might be to prepare the new table (possibly as temporary table for better performance), then TRUNCATE the original and INSERT from the prepared new table. This way, depending objects like views, FK constraints, rules, function using the table type etc. can just stay in place. See:

Add new column without table lock?

Or you might be able to update the old table in place? This related answer shows code for all three variants:

Compacting a sequence in PostgreSQL

Specific solution
Your particular error message:
DETAIL:  default value for column id of table cals_status_deduplicated depends on sequence cals_status_id_seq

... indicates that you are (ab-)using a sequence to generate default values for multiple tables. Or at least it's owned by the wrong column. The solution depends on how you want to deal with the situation.
Or you created the new table copying the complete structure including default values depending on the same sequence.
Typically, you want a dedicated sequence. So you should probably make the new column own the SEQUENCE:
ALTER SEQUENCE cals_status_id_seq OWNED BY tbl_cleaned.id;  -- column in new table *before* switching

DROP TABLE only cascades to the sequence if it's owned by one of the columns in the table to be dropped.
Related:

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

You may be interested in IDENTITY columns which avoid this kind of complication with serial columns. See:

Auto increment table column


"Duct Tape" option: Remove the index on ID_User and replace it with an index on (ID_User,CREATION_DATE).
Best option: Get rid of the Id and find another suitable primary key such as (ID_User,<timestamp>).  This is preferable as it would: ensure uniqueness, remove the need for one index, and get rid of a useless column.


For recent versions, you can use window functions:
SELECT ID, CustomerID, Volume, Content, runtot
FROM (
    SELECT ID
         , CustomerID
         , Content
         , sum(Volume) over (
                partition by CustomerID
                order by ID
           ) as runtot
    FROM multiqueue
) AS q1
WHERE runtot < 2000;

EDIT:
If you want at least 1 row for each customer, you can add another window function ROW_NUMBER() that enumerates the result, and use that in your select:
SELECT ID, CustomerID, Volume, Content, runtot
FROM (
    SELECT ID
         , CustomerID
         , Content
         , sum(Volume) over (
                partition by CustomerID
                order by ID
           ) as runtot
         , row_number() over (
                partition by CustomerID
                order by ID
           ) as rn

    FROM multiqueue
) AS q1
WHERE runtot < 2000
   OR rn = 1;


One CPU in a 32-core system is getting to 100% usage and hurting performance

What process is using that CPU? You need to monitor the Process object and figure out which instance (ie.process) is driving CPU up. Also is important to distinguish between privileged (kernel) vs. user time. Also important to see if is not Interrupt Time by any chance.

The purpose of the exercise it o establish if is a hardware resource interrupt driving the CPU (I've seen this with some many network cards for instance), a user process (and which process). If you established that is SQL Server process then you can switch investigation to SQL Server specific means, at which moment it turns into SQL performance investigation well suited to Waits and Queues methodology. But if the problem is outside SQL then perhaps the best methodology to deploy is USE. And Kernrate is your friend.


Database deployment using QSR (Quest Script Runner) is slow

For "Schema Updates", look at Edition Based Redefinition.  You might also want to check out 12.2's Application Container.


I would recommend you to read this article:
https://blogs.msdn.microsoft.com/psssql/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set/

It covers all possible questions about AWE and answers to them
Also you might find the post from Paul Randal helpful:
A SQL Server DBA myth a day: (5/30) AWE must be enabled on 64-bit servers


which node will execute events in master-master setup

As far as I know, events in a MariaDB Galera Cluster must be configured to run on only one of the nodes. While CREATE EVENT is replicated to the other nodes, you have to choose which node to use for running the event scheduler: Execute SET GLOBAL event_scheduler = ON; on the node where you want events to run, or set it in your config file if you want it the setting to survive a restart.

If that node is down, then there is no automatic failover for events, so if you'd like to run the events on another node, you'll have to enable the event scheduler on that node.

You could perhaps control the event_scheduler setting through software: a script could poll each node, and make sure the setting is enabled on one and only one node.


Percona xtrabackup transaction log corrupted

DISCLAIMER : Not xtrabackup expert
Your situation is quite similar to a Jan 27, 2016 post on the Percona Forums. The poster, dbaffaleuf, was running percona server 5.6.24-72.2-log, innobackupex 2.3.3 Linux (x86_64).
After encountering your same message, dbaffaleuf wrote

Interestingly the xtrabackup_logfile file has been deleted from the backup path. If I save the xtrabackup_logfile before running the prepare, and restore it after, the first prepare crashes with transaction log corrupted, the next try to prepare works:
Restore the xtrabackup_log file for previous copy after full backup
$ cp /tmp/xtrabackup_logfile /my/path/to/backup/timestamp/

Run the prepare again:
$ /home/products/xtrabackup/bin/innobackupex --use-memory=1G --apply-log /my/path/to/backup/timestamp/ | tee -a /my/path/to/prepare/log/file.log

You may have to do the same

Save the xtrabackup_logfile before running the prepare
Restore the xtrabackup_log file for previous copy after full backup
Run the prepare again


Tuple calculus: unsure if my reasoning is correct

Answer

I'm going to rename your tables a bit:

Store(s) -- store [s.sid] has name [s.sname]
Item(i) -- item [i.iid] has name [i.iname]
Offer(o) -- store [o.sid] offers item [o.iid] for $[o.price]

You want the tuple(s) s of the form < sid > where

-- store s.sid offers every item, ie
-- for all Items i: store s.sid offers item i.iid, ie
-- for all Items i: there exists Offer o: [o.sid = s.sid ∧ o.iid = i.iid], ie
∀ i ∈ Items ∃ o ∈ Offer [o.sid = s.sid ∧ o.iid = i.iid]

The query for such stores--and there can be more than one such store--is:

{ s : < sid > | ∀ i ∈ Items ∃ o ∈ Offer [o.sid = s.sid ∧ o.iid = i.iid] }

Your answer 2

  I am looking for the sid where for all Items there is an entry in the Supplies table that corresponds to the iid.

That has a "for-all for which there-exists", just like my solution. But your proposed answer 2 has a "there-exists for which for-all":

{ P | ∃ S1 ∈ Supplies(
        P.sid = S1.sid
    ∧ ∀ i ∈ Items(S1.iid = i.iid)
    )}

That asks for tuples P where there is an offer S1 where [P's sid is S1's sid and for all Items S1's iid is that item's iid]. I hope you can see that this involves a single iid being the same as all the ones in Item, not what you want.

Your answer 1

{ P | ∃ o ∈ Offer (
        P.sid = o.sid
    ∧ ∀ i ∈ Item (∃ o2 ∈ Offer (o2.iid = i.iid ∧ o2.sid = o.sid))
    )}

Notice from above that the ∀ ... means store o.sid offers every item. So:

{ P | ∃ o ∈ Offer (P.sid = o.sid
    ∧ store o.sid offers every item  )
    )}

If there are no offers and some stores then every store offers every item. But since there are no offers, it's false that ∃ o ∈ Offer (P.sid = o.sid)). No Ps make that true. So this is {}. So this is not the answer.

(Your answer 1 may be modified from a textbook answer expressed by Codd's original relational division operator. But so dividing Offers by Items doesn't actually return tuples s where store s.sid offers every item. It returns rows where store s.sid offers every item AND there is at least one item. So the answer is not expressed by Offers/Items.)

(Indeed 2 has an extra ∃ compared to your natural language description and my answer, but it's the outer ∃. So your solution 1 is in that way reminiscent of the correct answer.)


MySQL: sysbench test - InnoDB vs Memory tables

I have addressed InnoDB vs MEMORY before

Jan 21, 2012 : Is InnoDB Engine up to speed against Memory Engine?
Jan 17, 2012 : Mysql Memory table getting many locks

I also addressed the one headache MEMORY tables have: There is still disk I/O because of the .frm file (See my post I am using the MEMORY storage engine but MySQL still writes to my disk...Why?).

Also worth remembering is the fact that MEMORY tables do table-level locks.

You should just increase the InnoDB buffer pool and leave MEMORY tables behind


What connection do internal sessions belong to in MS SQL Server?

The internal sessions < 50 are background processes, and have no corresponding connection, which is why you won't see them show up in
 sys.dm_exec_connections.

The connections are reffered to as external user processes apart from the internal processes.

Note also, as Dan Guzman added:

  System sessions in newer SQL Server versions may have session_id values > 50. These can be identified by is_user_process = 0 in sys.dm_exec_sessions


SQLOS Resource Monitor and QueryMemoryResourceNotification Win API

This is called external memory pressure assuming that you set the max memory setting to a reasonable value. Generally it happens when processes other than SQL server (ssis, monitoring agents, iis etc.) consumes memory.

You can identify if a memory pressure is internal ör external from ring buffers which is explained in Jonathan's post

An easier way of determining memory pressure is to monitor memory grants pending and memory grants outstanding performance counters.

In both ways either you have to use a third party monitoring software for alerting or you can create your own alerting mechanism.

Note: Cited on both question and answer :). Cheers Jonathan.


How to tell what's failing at making backups?

You can capture backup events using SQL Server Audit.

USE master;
GO

CREATE SERVER AUDIT ServerAudit
  TO FILE (FILEPATH = 'C:\temp\', MAXSIZE = 1 GB)
  WITH (ON_FAILURE = CONTINUE);
GO

ALTER SERVER AUDIT ServerAudit
  WITH (STATE = ON);
GO

CREATE SERVER AUDIT SPECIFICATION BackuAudit
  FOR SERVER AUDIT ServerAudit
  ADD (BACKUP_RESTORE_GROUP);
GO

ALTER SERVER AUDIT SPECIFICATION BackuAudit
  WITH (STATE = ON);
GO

Then you can view the Audit Logs from within SSMS (Security > Audits).

If you can't use audit (e.g. due to edition restrictions), you can use a server-side trace:

declare @rc int, @TraceID int, @maxfilesize bigint = 5;

exec sp_trace_create @TraceID output, 0, N'c:\temp\backup_trc', @maxfilesize, NULL;

exec sp_trace_setevent @TraceID, 115, 1,  1;
exec sp_trace_setevent @TraceID, 115, 9,  1;
exec sp_trace_setevent @TraceID, 115, 3,  1;
exec sp_trace_setevent @TraceID, 115, 4,  1;
exec sp_trace_setevent @TraceID, 115, 5,  1;
exec sp_trace_setevent @TraceID, 115, 6,  1;
exec sp_trace_setevent @TraceID, 115, 7,  1;
exec sp_trace_setevent @TraceID, 115, 8,  1;
exec sp_trace_setevent @TraceID, 115, 10, 1;
exec sp_trace_setevent @TraceID, 115, 11, 1;
exec sp_trace_setevent @TraceID, 115, 12, 1;
exec sp_trace_setevent @TraceID, 115, 14, 1;
exec sp_trace_setevent @TraceID, 115, 21, 1;
exec sp_trace_setevent @TraceID, 115, 23, 1;
exec sp_trace_setevent @TraceID, 115, 26, 1;
exec sp_trace_setevent @TraceID, 115, 28, 1;
exec sp_trace_setevent @TraceID, 115, 29, 1;
exec sp_trace_setevent @TraceID, 115, 34, 1;
exec sp_trace_setevent @TraceID, 115, 35, 1;
exec sp_trace_setevent @TraceID, 115, 37, 1;
exec sp_trace_setevent @TraceID, 115, 40, 1;
exec sp_trace_setevent @TraceID, 115, 41, 1;
exec sp_trace_setevent @TraceID, 115, 49, 1;
exec sp_trace_setevent @TraceID, 115, 50, 1;
exec sp_trace_setevent @TraceID, 115, 51, 1;
exec sp_trace_setevent @TraceID, 115, 60, 1;
exec sp_trace_setevent @TraceID, 115, 64, 1;

exec sp_trace_setstatus @TraceID, 1;

select TraceID = @TraceID;

I've probably captured more columns there than you need...


Is it more efficient to store 0 and 1 for a binary value or just empty and 1 in MySQL

Performance wise

a statement could be made that inserting would be faster when the flag is down as the field doesn't need to be included in the insert clause.

On the other hand, selecting all records where the flag is down could be slower because in the end, somewhere you need to make a conversion from NULL to 0.

As any performance difference that might exist will not be noticable, let's call this a draw.

Storage wise

I'm operating here under the assumption that you are actually using the BOOL datatype. Likely the reasoning holds for other types as well but that should be looked at on a case by case basis.

The BOOL's datatype underlying storage is a TINYINT(1) wich makes up for 1 Byte. The storage requirement is the same for 0, 1 or NULL so you don't gain anything here.

Again a draw.

But that's all moot...

... because in the end, you should use what is most maintainable. Rest assured that being explicit about your values is much more maintainable than having to convert them back and forth everywhere you use that field.


How should permissions for anonymous users be modelled?

I'd go for option 1. With this approach there are no "hacks" in the app for anonymous users. The standard security model continues to apply with the only difference being that if you don't know who the user is (ie, they haven't authenticated) that you pretend they are the anonymous user, which in effect they are.

Option 1 is also the approach used by most (all?) web servers and file servers: they use an "anonymous" or "nobody" user.


sql or hql query to find all parents of a particular record

On Oct 24, 2011, I wrote some Stored Functions to perform Ancestor and Descendant traversals (Find highest level of a hierarchical field: with vs without CTEs ). From my post, you would use GetAncestry. From your data,  the code in GetAncestry will traverse until it sees -1 as a parent id. No changes in the algorithm are needed except to change parent_id to parent.

For the sake of example, let's suppose the table is called mydb.mytable

GetAncestry Code

USE mydb
DELIMITER $$
DROP FUNCTION IF EXISTS `GetAncestry` $$
CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(parent,-1) INTO ch FROM
        (SELECT parent FROM mytable WHERE id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END $$
DELIMITER ;

Once you create this stored procedure, you would call the function as follows:

USE mydb
SELECT GetAncestry(201);

Let's test that out

Sample Data Based On Your Question

drop database if exists mydb;
create database mydb;
use mydb
create table mytable
(id int not null,
name varchar(20),
parent int default -1,
primary key (id));
insert into mytable values
(1,'ROOT',-1),(162,'ROOT',1),(163,'ROOT',1),(164,'ROOT',1),
(165,'ROOT',1),(166,'ROOT',1),(167,'ROOT',1),(168,'ROOT',1),
(169,'ROOT',1),(170,'ROOT',1),(171,'ROOT',1),(172,'ROOT',1),
(173,'ROOT',1),(174,'ROOT',1),(175,'ROOT',1),(176,'ROOT',1),
(177,'ROOT',1),(178,'ROOT',1),(179,'ROOT',1),(180,'ROOT',1),
(181,'ROOT',1),(182,'ROOT',1),(183,'ROOT',1),(184,'ROOT',1),
(185,'ROOT',184),(186,'ROOT',1),(187,'ROOT',1),(188,'ROOT',187),
(201,'ROOT',188),(202,'ROOT',201),(203,'ROOT',202);

Load Sample Data

mysql> drop database if exists mydb;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb
Database changed
mysql> create table mytable
    -> (id int not null,
    -> name varchar(20),
    -> parent int default -1,
    -> primary key (id));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mytable values
    -> (1,'ROOT',-1),(162,'ROOT',1),(163,'ROOT',1),(164,'ROOT',1),
    -> (165,'ROOT',1),(166,'ROOT',1),(167,'ROOT',1),(168,'ROOT',1),
    -> (169,'ROOT',1),(170,'ROOT',1),(171,'ROOT',1),(172,'ROOT',1),
    -> (173,'ROOT',1),(174,'ROOT',1),(175,'ROOT',1),(176,'ROOT',1),
    -> (177,'ROOT',1),(178,'ROOT',1),(179,'ROOT',1),(180,'ROOT',1),
    -> (181,'ROOT',1),(182,'ROOT',1),(183,'ROOT',1),(184,'ROOT',1),
    -> (185,'ROOT',184),(186,'ROOT',1),(187,'ROOT',1),(188,'ROOT',187),
    -> (201,'ROOT',188),(202,'ROOT',201),(203,'ROOT',202);
Query OK, 31 rows affected (0.01 sec)
Records: 31  Duplicates: 0  Warnings: 0

mysql>

Create the Stored Function

mysql> USE mydb
Database changed
mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS `GetAncestry` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
    -> DETERMINISTIC
    -> BEGIN
    ->     DECLARE rv VARCHAR(1024);
    ->     DECLARE cm CHAR(1);
    ->     DECLARE ch INT;
    ->
    ->     SET rv = '';
    ->     SET cm = '';
    ->     SET ch = GivenID;
    ->     WHILE ch > 0 DO
    ->         SELECT IFNULL(parent,-1) INTO ch FROM
    ->         (SELECT parent FROM mytable WHERE id = ch) A;
    ->         IF ch > 0 THEN
    ->             SET rv = CONCAT(rv,cm,ch);
    ->             SET cm = ',';
    ->         END IF;
    ->     END WHILE;
    ->     RETURN rv;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

Call GetAncestry

mysql> SELECT GetAncestry(201);
+------------------+
| GetAncestry(201) |
+------------------+
| 188,187,1        |
+------------------+
1 row in set (0.00 sec)

mysql>

Only one thing left to do...

GIVE IT A TRY !!!

I have discussed using GetAncestry in the DBA StackExchange many times

Please note that this is a MySQL-specific answer. Please read PL/SQL Documentation if you wish to implement the algorithm in a PL/SQL block or a Stored Procedure.


How to improve performance merging multiple table via cursor

Don't use a cursor, use a JOIN to do the entire set of statements all at once.
Here are two examples:
http://mysql.rjweb.org/doc.php/myisam2innodb#generating_alters
http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes
Both of them depend on doing lots of statements, then you manually copy the commands into MySQL commandline tool (or some other tool).  They could be adapted to do prepare/execute.
See also the following for construction a single query to pivot a table:
http://mysql.rjweb.org/doc.php/pivot


SQLCMD Mode in SSMS and @@variable expansion

Unless I'm missing something, doesn't this work, without the apostrophes?:

:setvar dbNotServer @@servername
SELECT $(dbNotServer) AS worked

It returns SERVER\INSTANCE for me in SQLCMD mode through SSMS.

Edit 02/12/2018
After coming across my answer to another question, and reviewing, I can see the issue, I believe it boils down to this:

:setvar dbNotServer @@servername
print '$(dbNotServer)'

This outputs @@servername, and the desired output is the value of @@servername.
The only way I've seen to do this is in the accepted answer here


Updating open orders from a price list within a range of dates

I used you dbfiddler code in the following solution that uses CROSS APPLY

declare @price_list table(product varchar(20), price int, date_price datetime, base int, active tinyint);
insert into @price_list values
('0125',  90, '2017-01-01', 1200, 0),
('0125', 100, '2017-01-25', 1000, 1),
('0125', 110, '2017-02-27', 500,  1),
('1200', 140, '2017-01-01', 2000, 0),
('1200', 150, '2017-02-01', 1500, 1),
('1200', 160, '2017-02-27', 1000, 1);

declare @orders table(product varchar(20), order_date datetime, price int, base int);
insert into @orders values
('0125', '2017-02-19', 100, 1000),
('0125', '2017-02-20', 100, 1000),
('0125', '2017-02-21', 100, 1000),
('0125', '2017-02-22', 100, 1000),
('0125', '2017-02-23', 100, 1000),
('0125', '2017-02-28', 110,  500),
('1200', '2017-02-19', 150, 1500),
('1200', '2017-02-20', 150, 1500),
('1200', '2017-02-21', 150, 1500),
('1200', '2017-02-22', 150, 1500),
('1200', '2017-02-23', 150, 1500),
('1200', '2017-02-28', 160, 1000);

declare @new_date_price datetime = '2017-02-21';

-- add a new price to the list
--
insert into @price_list values ('0125', 105, @new_date_price - 1, 1300, 1);
insert into @price_list values ('1200', 155, @new_date_price - 1, 1400, 1);

-- update orders price and base, according products price list date price
--
-- I'd like to avoid use 2 (or more) subqueries
--
UPDATE o
SET o.Price = ca.price
    ,o.base = ca.base
FROM @Orders o
CROSS APPLY (
    SELECT TOP 1 pl.price AS price
        ,pl.base AS base
    FROM @Price_List pl
    WHERE pl.product = o.product
        AND pl.date_price <= o.order_date
        AND active = 1
    ORDER BY pl.date_price DESC
    ) ca
WHERE o.product IN (
        '0125'
        ,'1200'
        );--<<< select distinct product from inserted
-- final result
--
select * from @price_list order by product, date_price;
select * from @orders order by product, order_date;


Scaling out SQL Server and syncing data across multiple machines

Creating a Scale Out database at Internet scale is pretty huge step. You will face a lot of issues that are not critical on a single big database. From your notes I see that you understand some of the basic issues you face.

Since Microsoft has papers on using SQL Server for scale out, I suggest that you study those first. Your scale out strategy will need to take into account the database server you choose.

For Microsoft SQL Server you should first study: http://msdn.microsoft.com/en-us/library/aa479364.aspx

This paper discusses the decisions that you need to make and why they are important. It offers 5 SQL Server strategies for scaleout:

  • Scalable Shared Databases

  • Peer-to-Peer Replication

  • Linked Servers

  • Distributed Partitioned Views

  • Data-Dependent Routing

As you go down the stack, things get more complicated, but also provide more powerful ways of scaling out.


Can someone please tell me where I went wrong for an exercise submission?

Makes little sense to me. Can you not approach your lecturer/examiner and request further feedback?

Personally I wouldn't be storing distances - store the coordinates and do the calculation on the fly. If you have 100 cities, then you have to store 99 distances with each city - not very pretty.

An example with MySqL is here. If MySQL has this, I'd be fairly sure that all the other major vendors support this functionality. And they do! Found this also which you may find this helpful. You didn't mention the name of your RDBMS but you should always do so.

Take the tour, check out the help centre and the "Help us to help you" blog. p.s welcome to the forums!


I'd consider:

If the table is partitioned - to disable the index partitions on the specific partitions which are going to be changed, and rebuild (rebuild online probably) them after the operation.
Increase the amount of memory in disposal (for the specific session), so that it won't do any I/O operations (on temporary tablespace) at all, by changing workarea_size_policy to 'manual' and  sort_area_size, hash_area_size to the maximum size you can spare for this operation.
Run the entire operation using parallel hint (or parallel_index if you'd decide to use some indexes after all).
Switch all the accessed objects to nologging mode, so that redo logs won't be generated during the operation (and switch back to logging right after it's finished of course).


suppose Orcl service already created  but you can't communicate with them then do follow :
 you need to run your database service like "oracleServiceorcl" as a Administrator so that client server can communicate.

If any doubt then plz put error like ora- Any Digiy


Missing indexes dmv count varies every day

Missing index info will be cleared from the view if the referenced table has changed. Added columns, new indexes would clear out this data. When you remove indexes, any missing index info for that table will also be removed.

Index rebuilds also clears out this info, so if you are doing nightly index maintenance, this may contribute to the change in numbers. (edit: rebuilds only clear missing index info on 2012+)


Choosing Between SQL Server 2008 R2 and SQL Server 2012 - Which to learn about first?

Yes, The certification exams for 2008 R2 are retired, but you can start learning 2008 R2 and once you master the concepts, move to the new 2012 features.

Even if your company (current) has no upgrade plans, your future company may already have upgraded all their servers to SQL Server 2012.

Check out What's new in SQL Server 2012. Also check Top 5 SQL Server 2012 Features by Aaron Bertrand.


How to view the latest entries in all tables in an Oracle DB?

You can identify most recently created users/schemas from dba_users view

select *
from dba_users
order by created desc;

When you know what schemas to look out for, you can list the most recently created tables with

select *
from dba_objects
where owner in ('SCHEMA1','SCHEMA2')
  and object_type = 'TABLE'
order by created desc;

For a single table, you can find a rough date when the data was last modified

select scn_to_timestamp(max(ora_rowscn))
from schema.table;

Or get a list of rows that were (potentially) modified last

select *
from schema.table
where ora_rowscn = (
  select max(ora_rowscn)
  from schema.table
);

Note that ora_rowscn is stored by default per database block, not per row. There is a limit how old scn values can be mapped back to timestamp.

You should also check the audit trail

select *
from dba_audit_trail
order by timestamp desc;


I cannot get SQL Server 2012 to install the database engine

SQL server 2012 installation is not supported through terminal services.  It causes a lot of strange problems, and I experienced this same issue.

Microsoft states this in their prerequisites:

Link is http://msdn.microsoft.com/en-us/library/ms143506.aspx

This document states:

The installation of SQL Server 2012 fails if you launch the setup through Terminal Services Client. Launching SQL Server Setup through Terminal Services Client is not supported.


SQL server Import CSV file - 1'st row is also added as s column name

I used your data and was able to do it without any issues. Seems your first row of data does not have {CR}{LF}. The snap of data looks like there is an auto new line rather than you providing it.And that is why the 3rd column has ' Is_Active""1, so the wizard is looking for a comma now. Below is my test with data of similar type :